CREATE
	OR

ALTER PROCEDURE [HM].[ReadForeignOrderDocumentAsXML] (
	@foreignOrderId INT,
	@userId INT,
	@moduleCode NVARCHAR(255),
	@removeInvalidXMLCharacters BIT = 0
	)
AS
BEGIN
	BEGIN TRY
		-- Pola wlasne naglowka dokumentu
		CREATE TABLE #customFields (xmlData XML)

		EXECUTE HM.CustomFieldsDocumentPrepareTemporaryDataBeforeRead @foreignOrderId,
			@moduleCode,
			'ZO',
			'zamówienia obce'

		-- Pola wlasne pozycji dokumentu
		CREATE TABLE #positionsCustomFields (
			positionId INT,
			customFields XML
			)

		EXECUTE HM.CustomFieldsPositionsPrepareTemporaryDataBeforeRead @foreignOrderId,
			@moduleCode,
			'ZP',
			'pozycje zamówień obcych' -- Dokumenty powiazane

		DECLARE @relatedDocuments AS HM.RelatedDocuments

		INSERT @relatedDocuments
        EXEC HM.GetRelatedDocumentsToZMO @ID_zam = @foreignOrderId -- Obecnie nie można wykorzystać funkcji ponieważ niezbędne jest odwołanie do schemy CM które powoduje błąd przy tworzeniu funkcji.
		
		
		DECLARE @relatedToInventoryDocument VARCHAR(5) = IIF(EXISTS(SELECT TOP 1 id FROM @relatedDocuments rd WHERE rd.GroupDescription = 'Dokumenty magazynowe'), 'true', 'false')

		-- Inne powiazania
		DECLARE @relatedElements AS HM.RelatedElements

		INSERT @relatedElements
		EXEC HM.GetRelatedElementsForDocument @docId = @foreignOrderId,
			@docBase = 45

		DECLARE @NpRateId INT = (
				SELECT TOP (1) Id
				FROM HM.XT
				WHERE super = 10000
					AND subtyp = 7
					AND flag & 0x200 <> 0
				)
		DECLARE @ZeroRateId INT = (
				SELECT TOP (1) Id
				FROM HM.XT
				WHERE super = 10000
					AND subtyp = 3
				)
		-- czy istnieje zaliczka do zamówienia (TODO: do weryfikacji jak będzię można wystawiać zaliczkę do zamówienia)
		DECLARE @AdvanceInvoiceExists BIT = HM.AdvanceInvoiceExists(@foreignOrderId)
		-- czy istnieje dokument sprzedazy do zamówienia
		DECLARE @SaleDocumentExists BIT = ISNULL((
				SELECT TOP 1 1
				FROM HM.TR AS tzo
				INNER JOIN HM.PO ON tzo.id = PO.id1
				INNER JOIN HM.TR AS tdk ON PO.id2 = tdk.id AND tdk.owner_base = 16
				WHERE tzo.owner_id = @foreignOrderId AND tzo.owner_base = 45
				), 0)
		-- Czy istnieje wersja do zamówienia
		DECLARE @ForeignOrderVersionExists BIT = (
				SELECT TOP 1 1
				FROM HM.ForeignOrdersVersions v
				WHERE v.ZoId = @foreignOrderId
				)
		DECLARE @resultXml XML = (
				SELECT ZO.ManualSettlement,
					ISNULL((
							SELECT *
							FROM HM.ft_Contractor(ZO.khadid)
							FOR XML PATH(''),
								ELEMENTS XSINIL,
								TYPE
							), '') AS ContractorData,
					ISNULL((
							SELECT *
							FROM HM.ft_Contractor(ZO.odadid)
							FOR XML PATH(''),
								ELEMENTS XSINIL,
								TYPE
							), '') AS ReceiverData,
					ISNULL((
							SELECT ISNULL((
										SELECT RZ.id AS Id,
											RZ.idpozrz AS PositionId,
											KH.id AS InventoryId,
											KH.kod AS InventoryName,
											CAST(ISNULL(RZ.iloscrz, 0) AS DECIMAL(24, 4)) AS ReservedQuantity,
											CAST(ISNULL(RZ.iloscrz, 0) AS DECIMAL(24, 4)) AS ReservedQuantityAuxiliary,
											CAST(ISNULL((
														SELECT SUM(Quantity)
														FROM [HM].[dbo_ForeignOrderPositionSettlementsView]
														WHERE OrderPositionId = zp.id
														), 0) AS DECIMAL(24, 4)) AS RealizedReservedQuantity
										FROM HM.RZ
										INNER JOIN HM.KH ON RZ.magazyn = KH.id
										WHERE RZ.bazadokrz = 45
											AND RZ.bazapozrz = 30
											AND RZ.idpozrz = ZP.id
										FOR XML PATH('Reservation'),
											ELEMENTS XSINIL,
											TYPE
										), '') AS Reservations,
								ISNULL((
										SELECT PW.id AS Id,
											CAST(ISNULL(PW.ilosc, 0) AS DECIMAL(24, 4)) AS ReservedQuantity,
											CAST(ISNULL(DW.iloscdosp, 0) + ISNULL(PW.ilosc, 0) AS DECIMAL(24, 4)) AS AvailableQuantity,
											CAST(ISNULL(PW.ilosc, 0) AS DECIMAL(24, 4)) AS ReservedQuantityAuxiliary,
											CAST(ISNULL((
														SELECT SUM(ilosc)
														FROM HM.ZZ
														WHERE typ = 96
															AND baza1 = 36
															AND id1 = PW.id
															AND baza1 = baza2
														), 0) AS DECIMAL(24, 4)) AS RealizedReservedQuantity,
											(
												SELECT kod
												FROM hm.kh
												WHERE typ = 105
													AND id = DW.magazyn
												) AS InventoryName,
											ISNULL((
													SELECT DW.id AS Id,
														CAST(DW.cena AS DECIMAL(24, 4)) AS Price,
														DW.[data] AS [Date],
														DW.kod AS [Name],
														DW.krajPochodzenia AS CountryId,
														DW.magazyn AS InventoryId
													FOR XML PATH('Delivery'),
														ELEMENTS XSINIL,
														TYPE
													), '')
										FROM HM.PW
										INNER JOIN HM.DW ON DW.id = PW.iddw
										WHERE PW.idmg IN (
												SELECT id
												FROM hm.rz
												WHERE idpozrz = ZP.id
													AND bazapozrz = 30
													AND subtyp & 0x01 = 0
												)
											AND PW.typ = 26
										FOR XML PATH('ReservationDelivery'),
											ELEMENTS XSINIL,
											TYPE
										), '') AS ReservationsDelivery,
								zp.IsDocumentPosition,
								zp.Description,
								-- pola własne
								(
									SELECT pcf.customFields
									FROM #positionsCustomFields pcf
									WHERE pcf.positionId = ZP.id
									),
								zp.[PriceOfSetCalculatedBasedOfElements],
								zp.[NoEditOfSet],
								zp.[VatRateId],
								zp.[PricePLN],
								zp.[PriceGross],
								zp.[PriceGrossPLN],
								zp.[PriceListPrice],
								zp.[PriceListPriceGross],
								zp.[OriginalPriceListPrice],
								zp.[OriginalPriceListPriceCurrency],
								zp.[OriginalBasePrice],
								zp.[OriginalBasePriceCurrency],
								zp.[BasePricePLN],
								zp.[ValueGross],
								zp.[ValueVat],
								zp.[ValuePLN],
								zp.[ValueGrossPLN],
								zp.[ValueVatPLN],
								zp.[PriceTypeId],
								zp.[OO],
								zp.[CodePKWIU],
								zp.[Discount],
                                zp.[IsProductDiscountAllowed],
								zp.[ReservationStockCode],
								zp.[StockStatus],
								zp.[ServiceExpense],
								zp.[Id],
								zp.[NodeId],
								zp.[ParentId],
								zp.[UserOrdinalNumber],
								zp.[OrdinalNumber],
								zp.[DocumentId],
								zp.[OriginalDocumentId],
								zp.[BaseType],
								zp.[Guid],
								zp.[ProductId],
								zp.[ProductShortcut],
								zp.[IsElementOfSet],
								zp.[IsSet],
								zp.[UnitOfMeasurement],
								zp.[EvidenceUnitOfMeasurement],
								zp.[ConversionFactorUoM],
								zp.[Quantity],
								zp.[UnitQuantity],
								zp.[ReservationQuantity],
								zp.[EvidenceQuantity],
								zp.[ReservationType],
								zp.[AccountingPattern],
								zp.[Price],
								zp.[Value],
								zp.[PriceRounding],
								zp.[IsService],
								zp.[CompletedQuantity],
								zp.ForSettlement,
								zp.ForSettlementPLN,
								zp.UsedInAdvanceInvoice,
								zp.AdvanceInvoiceValueGross,
								'' as [AdvanceInvoiceDivisionPositions],
								'false' AS IsVatModifiedManually
							FROM HM.ft_ForeignOrderPositions(@foreignOrderId, @removeInvalidXMLCharacters) zp
							ORDER BY ZP.lp
							FOR XML PATH('ForeignOrderPosition'),
								ELEMENTS XSINIL,
								TYPE
							), '') AS DocumentPositions,
					-- Stawki VAT
					ISNULL((
							SELECT [Id],
								[Code],
								[Value],
								[ValuePLN],
								[ValueGross],
								[ValueGrossPLN],
								[ValueVat],
								[ValueVatPLN]
							FROM HM.ft_VatRates(45, @foreignOrderId, @NpRateId, @ZeroRateId)
							ORDER BY (CASE WHEN Code LIKE '[a-z]%' THEN 1 ELSE 0 END),
								Code DESC
							FOR XML PATH('VatRate'),
								ELEMENTS XSINIL,
								TYPE
							), '') AS VatRates, -- Dokumenty powiązane
					ISNULL((
							SELECT [Id],
                                [DocumentTypeId],
								[DocumentType],
								[Number],
								[Date],
								[Person],
								[CharacterId],
								[InBuffer]
							FROM HM.ft_RelatedDocuments(@relatedDocuments)
							FOR XML PATH('RelatedDocument'),
								ELEMENTS XSINIL,
								TYPE
							), '') AS RelatedDocuments, -- Inne powiązania
					ISNULL((
							SELECT [Id],
								[TypeName],
								[Type],
								[Details],
								[InBuffer]
							FROM HM.ft_RelatedElements(@relatedElements)
							FOR XML PATH('RelatedElement'),
								ELEMENTS XSINIL,
								TYPE
							), '') AS RelatedElements, -- pola własne
					(
						SELECT *
						FROM #customFields
						),
					zo.[Id],
					zo.[Guid],
					zo.[DocumentNumber],
					zo.[SeriesNo],
					zo.[Name],
					zo.[CorrectedDocumentId],
					zo.[IssueDate],
					zo.[OperationDate],
					zo.[IsCorrection],
					zo.[Description],
					zo.[RecipientPerson],
					zo.[BaseType],
					zo.[DocumentTypeShortcut],
					zo.[DocumentNumerationShortcut],
					zo.[IsIssued],
					zo.[Value],
					zo.[DocumentStatus],
					zo.[DocumentStatusDescription],
					zo.[HmImported],
					zo.[Archived],
					zo.[FkExported],
					zo.[IsCreatedFromForeignOrder],
					zo.[Corrected],
					zo.[Canceled],
					zo.[IsActive],
					zo.[MigrationStatus],
					zo.[ContractorId],
					zo.[DepartmentId],
					zo.[AccountingPattern],
					zo.[IssuerId],
					zo.[IssuerLogin],
					zo.[DepartmentInSeries],
					zo.[Period],
					zo.[RDFStatus],
					zo.[ReceiverId],
					zo.[ReceiverAddressName],
					zo.[PayRegistryId],
					zo.[PaymentFormClientId],
					zo.[PaymentDeadline],
					zo.[ValueGross],
					zo.[ValueVat],
					zo.[ValuePLN],
					zo.[ValueGrossPLN],
					zo.[ValueVatPLN],
					zo.[Currency],
					zo.[ExchangeRate],
					zo.[PriceTypeId],
					zo.[Discount],
					zo.[SplitPayment],
					zo.[InRegisterDate],
					zo.[CorrectionCause],
					zo.[PriceKind],
					zo.[InventorySettlementStatus],
					zo.[PublishInventoryDocumentMode],
					zo.[CashSettled],
					zo.[RegisterMethod],
					zo.[CalculateVatMethod],
					CASE WHEN [HM].[fnMayCreateSaleDocument](ZO.id) = 1 THEN 'true' ELSE 'false' END AS MayCreateSaleDocument,
					CASE WHEN [HM].[fnMayCreateInventoryDocument](ZO.id) = 1 THEN 'true' ELSE 'false' END AS MayCreateInventoryDocument,
					CASE WHEN [HM].[fnMayCreateAdvanceInvoice](ZO.id) = 1 THEN 'true' ELSE 'false' END AS MayCreateAdvanceInvoice,
					zo.UseAdvanceInvoiceIsReadOnly,
					zo.UseAdvanceInvoice,
					zo.IsForeignOrderVersion,
					zo.ForeignOrderVersionExists,
					zo.[KindId],
					zo.[CatalogId],
					zo.[Marker],
					zo.[ReservationType],
					IIF(@AdvanceInvoiceExists = 1, 'true', 'false') AS AdvanceInvoiceExists,
					IIF(@SaleDocumentExists = 1, 'true', 'false') AS SaleDocumentExists,
					@relatedToInventoryDocument AS RelatedToInventoryDocument,
					'false' AS IsVatModifiedManually,
                    zo.[ManualSettlementStatus],
                    0 AS ToPay,
					zo.TypeExternal,
                    zo.IdExternal,
                    zo.Id2External
				FROM HM.ft_ForeignOrderDocument(@foreignOrderId, @removeInvalidXMLCharacters) zo
				FOR XML PATH(''), ELEMENTS XSINIL,
					ROOT('ForeignOrderDocument')
				)

		IF (OBJECT_ID('tempdb..#customFieldsDefinitions') IS NOT NULL)
			DROP TABLE #customFieldsDefinitions

		IF (OBJECT_ID('tempdb..#customFields') IS NOT NULL)
			DROP TABLE #customFields

		IF (OBJECT_ID('tempdb..#positionsCustomFields') IS NOT NULL)
			DROP TABLE #positionsCustomFields

		IF (OBJECT_ID('tempdb..#positionsCustomFieldsDefinitions') IS NOT NULL)
			DROP TABLE #positionsCustomFieldsDefinitions

		EXEC HM.ReadForeignOrderDocumentRIAsXML @foreignOrderId,
			@resultXml,
			@userId,
			@moduleCode
	END TRY

	BEGIN CATCH
		DECLARE @ErrorNumber INT = ERROR_NUMBER();

		IF (
				@removeInvalidXMLCharacters = 0
				AND (@ErrorNumber IN (9420, 6841))
				)
		BEGIN
			EXEC HM.ReadForeignOrderDocumentAsXML @foreignOrderId,
				@userId,
				@moduleCode,
				@removeInvalidXMLCharacters = 1;

			GOTO END_CATCH;
		END;

		THROW;

		END_CATCH:
	END CATCH
END
