-- Uwaga: Gdy dodasz nowy parametr, użuj go równiez przy wywołaniu na końcu procedury (szukaj: EXEC [HM].[ReadSaleDocumentAsXML]), sugeruje się pozostawić parametr @removeInvalidXMLCharacters na końcu
CREATE
	OR

ALTER PROCEDURE [HM].[ReadSaleDocumentAsXML] (
	@docId INT,
	@userId INT,
	@moduleCode NVARCHAR(255),
	@removeInvalidXMLCharacters BIT = 0
	)
AS
BEGIN
	BEGIN TRY
		DECLARE @correction BIT = 0 -- korekta zwykla lub zbiorcza
		DECLARE @correctedDocId INT

		SELECT 
			@correction = IIF(iddokkoryg IS NOT NULL
				OR flag & 0x20 = 0x20, 1, 0),
			@correctedDocId = iddokkoryg
		FROM HM.DK
		WHERE id = @docId

		-- Pola wlasne naglowka dokumentu
		CREATE TABLE #customFields (xmlData XML)

		EXECUTE HM.CustomFieldsDocumentPrepareTemporaryDataBeforeRead @docId,
			@moduleCode,
			'DK',
			'dokumenty sprzedaży'

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

		EXECUTE HM.CustomFieldsPositionsPrepareTemporaryDataBeforeRead @docId,
			@moduleCode,
			'DP',
			'pozycje dokumentów sprzedaży',
			@correctedDocId,
			'DK'

		-- Dokumenty powiazane
		DECLARE @relatedDocuments AS HM.RelatedDocuments

		INSERT @relatedDocuments
		EXEC [HM].[GetRelatedDocumentsToFVS] @Id_fak = @docId

		-- Inne powiazania
		DECLARE @relatedElements AS HM.RelatedElements

		INSERT @relatedElements
		EXEC [HM].[GetRelatedElementsForDocument] @docId = @docId,
			@docBase = 16 -- sprzedaż

		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
				)
		DECLARE @resultXml XML = (
				SELECT dk.[ExchangeRatePIT],
					dk.[FiscalizationState],
					dk.ReceiptNumber,
					dk.UniqueCashBoxNumber,
					dk.[JPK_V7],
					dk.[JPK],
					dk.[SaleRegister],
					dk.[IsGroupCorrection],
					dk.[DigitalInvoice],
					dk.RelatedForeignOrderDocumentId,
					dk.RelatedToForeignOrderVersion,
					dk.AdvanceInvoiceAmount,
					dk.PaymentsSum,
					(
						ISNULL((
								SELECT *
								FROM HM.ft_Contractor(dk.khadid)
								FOR XML PATH(''),
									ELEMENTS XSINIL,
									TYPE
								), '')
						) AS ContractorData,
					(
						ISNULL((
								SELECT *
								FROM HM.ft_Contractor(dk.odadid)
								FOR XML PATH(''),
									ELEMENTS XSINIL,
									TYPE
								), '')
						) AS ReceiverData,
					(
						ISNULL((
								SELECT dp.[Description],
									dp.[CorrectedDocumentId],
									dp.[CorrectedDocumentShortcut],
									dp.[CorrectedPositionId],
									dp.[Compensation],
									dp.[RelatedForeignOrderPositionId],
									dp.[RelatedForeignOrderPositionIdForSalePosition],
									dp.[RelatedForeignOrderIdForSalePosition],
									dp.[RelatedPositionIdForDropdown],
									dp.[IsDocumentPosition],
									dp.[IsCorrectedPosition],
									dp.[IsCorrectionPosition],
									dp.[IsNewCorrectPosition],
									dp.[IsGroupCorrection],
									dp.[IsCreatedFromForeignOrder],
									dp.[ServiceExport],
									dp.[VatUE],
									dp.[DomesticVatRateId],
									dp.[JPK_V7] AS [JPK_V7],
									dp.[cn] AS [CN],
									dp.[VatRegister],
									dp.[HasSettelmentDeliveryReservation],
									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(zz.ilosc)
															FROM [HM].[ZZ] zz
															WHERE zz.typ = 96
																AND zz.baza1 = 152
																AND zz.id1 = rz.id
																AND zz.baza1 = zz.baza2
															), 0) AS DECIMAL(24, 4)) AS [RealizedReservedQuantity],
												ISNULL((
														SELECT rzo.id AS [ReservationId],
															ISNULL(IIF(rzo.subtyp = 0, 'true', 'false'), 'false') AS [IsDeliveryReservation],
															CAST(ISNULL((
																		SELECT SUM(Quantity)
																		FROM [HM].[dbo_ForeignOrderPositionSettlementsView]
																		WHERE OrderPositionId = zp.id
																			AND DocumentId != @docId
																		), 0) AS DECIMAL(24, 4)) AS [RealizedSettelmentReservedQuantity],
															CAST(ISNULL(pwo.ilosc, 0) AS DECIMAL(24, 4)) AS [Quantity],
															CAST(ISNULL((
																		SELECT SUM(zzz.ilosc)
																		FROM HM.ZZ zzz
																		WHERE zzz.typ = 96 and zzz.baza1 = 36 and zzz.id1 = pwo.id
																		), 0) AS DECIMAL(24, 4)) AS [RealizationQuantity],
															ISNULL(pwo.id, 0) AS [DeliverySettlementId],
															ISNULL(CAST(ISNULL(dw.iloscdosp, 0) AS DECIMAL(24, 4)), 0) AS [DeliveryMaxQuantity],
															ISNULL(zp.id, 0) AS [OrderId],
															ISNULL(CAST(ISNULL(zp.ilosc, 0) AS DECIMAL(24, 4)), 0) AS [MaxQuantity]
														FROM HM.RZ rzo
														LEFT JOIN HM.PW pwo ON pwo.idmg = rzo.id
														LEFT JOIN HM.DW dw ON pwo.iddw = dw.id
														LEFT JOIN HM.ZZ zz ON zz.typ = 96 and zz.baza1 = 152 and zz.id1 = rzo.id
														LEFT JOIN HM.ZP zp ON rzo.idpozrz = zp.id
														LEFT JOIN HM.ZO zo ON zp.super = zo.id
														WHERE pwo.typ = 26
															AND zz.id2 = rz.id
															
														ORDER BY zo.datasp DESC
														FOR XML PATH('DeliverySettlement'),
															ELEMENTS XSINIL,
															TYPE
														), '') AS [DeliverySettlements]
											FROM [HM].[RZ] rz
											JOIN [HM].[KH] kh ON rz.[magazyn] = kh.[id]
											WHERE rz.[bazadokrz] = 16
												AND rz.[bazapozrz] = 18
												AND rz.[idpozrz] = dp.[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) AS DECIMAL(24, 4)) + CAST(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(zz.ilosc)
															FROM [HM].[ZZ] zz
															WHERE zz.typ = 96
																AND zz.baza1 = 36
																AND zz.id1 = pw.id
																AND zz.baza1 = zz.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] pw
											JOIN [HM].[DW] dw ON dw.[id] = pw.[iddw]
											WHERE pw.[idmg] IN (
													SELECT id
													FROM hm.rz
													WHERE idpozrz = dp.[id]
														AND bazapozrz = 18
														AND subtyp = 0
													)
												AND pw.[typ] = 26
											FOR XML PATH('ReservationDelivery'),
												ELEMENTS XSINIL,
												TYPE
											), '') AS ReservationsDelivery,
									ISNULL((
											SELECT rel.ZpId
											FROM [HM].[AdvanceInvoiceSettlementForeignOrder] rel
											WHERE rel.DpId = ISNULL(dp.idpozkoryg, dp.id)
											FOR XML PATH('RelationZp'),
												ELEMENTS XSINIL,
												TYPE
											), '') AS RelationsZp,
									ISNULL((
											select * from hm.ft_AdvanceInvoiceSettlementPosition(dp.Id)
											order by DocumentDate, DkzId
											FOR XML PATH('AdvanceInvoiceSettlementPosition'),
												ELEMENTS XSINIL,
												TYPE
											), '') AS AdvanceInvoiceSettlementPositions,
									-- pola własne
									(
										SELECT pcf.customFields
										FROM #positionsCustomFields pcf
										WHERE pcf.positionId = dp.id
										),
									dp.[PriceOfSetCalculatedBasedOfElements],
									dp.[NoEditOfSet],
									dp.[VatRateId],
									dp.[PricePLN],
									dp.[PriceGross],
									dp.[PriceGrossPLN],
									dp.[PriceListPrice],
									dp.[PriceListPriceGross],
									dp.[OriginalPriceListPrice],
									dp.[OriginalPriceListPriceCurrency],
									dp.[OriginalBasePrice],
									dp.[OriginalBasePriceCurrency],
									dp.[BasePricePLN],
									dp.[ValueGross],
									dp.[ValueVat],
									dp.[ValuePLN],
									dp.[ValueGrossPLN],
									dp.[ValueVatPLN],
									dp.[PriceTypeId],
									dp.[OO],
									dp.[CodePKWIU],
									dp.[Discount],
									dp.[ReservationStockCode],
									dp.[StockStatus],
									dp.[ServiceExpense],
									dp.[Id],
									dp.[NodeId],
									dp.[ParentId],
									dp.[UserOrdinalNumber],
									dp.[OrdinalNumber],
									dp.[DocumentId],
									dp.[OriginalDocumentId],
									dp.[BaseType],
									dp.[Guid],
									dp.[ProductId],
									dp.[ProductShortcut],
									dp.[IsElementOfSet],
									dp.[IsSet],
									dp.[UnitOfMeasurement],
									dp.[EvidenceUnitOfMeasurement],
									dp.[ConversionFactorUoM],
									dp.[Quantity],
									dp.[UnitQuantity],
									dp.[ReservationQuantity],
									dp.[EvidenceQuantity],
									dp.[ReservationType],
									dp.[AccountingPattern],
									dp.[Price],
									dp.[Value],
									dp.[PriceRounding],
									dp.[IsService],
									dp.[CompletedQuantity],
									dp.ForSettlement AS ForSettlement
								FROM [HM].[ft_SalePositions](dk.id, 16, dk.flag, dk.subtyp, @removeInvalidXMLCharacters, @correction) dp
								WHERE dp.[subtyp] = 0
								-- pozycje sprzedaży
								ORDER BY dp.NodeId
								-- istotne w przypadku korekt do poprawnego wyliczania _Index
								FOR XML PATH('SalePosition'),
									ELEMENTS XSINIL,
									TYPE
								), '')
						) AS DocumentPositions,
					-- Stawki VAT
					(
						ISNULL((
								SELECT *
								FROM HM.ft_VatRates(16, @docId, @NpRateId, @ZeroRateId)
								ORDER BY (
										CASE 
											WHEN Code LIKE '[a-z]%'
												THEN 1
											ELSE 0
											END
										),
									Code DESC, Id ASC
								FOR XML PATH('VatRate'),
									ELEMENTS XSINIL,
									TYPE
								), '')
						) AS VatRates,
					-- Płatności
					(
						ISNULL((
								SELECT *
								FROM HM.ft_Payments(@docId)
								FOR XML PATH('Payment'),
									ELEMENTS XSINIL,
									TYPE
								), '')
						) AS Payments,
					-- Dokumenty powiązane
					(
						ISNULL((
								SELECT *
								FROM HM.ft_RelatedDocuments(@relatedDocuments)
								FOR XML PATH('RelatedDocument'),
									ELEMENTS XSINIL,
									TYPE
								), '')
						) AS RelatedDocuments,
					-- Inne powiązania
					(
						ISNULL((
								SELECT *
								FROM HM.ft_RelatedElements(@relatedElements)
								FOR XML PATH('RelatedElement'),
									ELEMENTS XSINIL,
									TYPE
								), '')
						) AS RelatedElements,
					ISNULL(dk.[statusKsef], 0) AS [StatusKsef],
					-- pola własne
					(
						SELECT *
						FROM #customFields
						),
					dk.[Id],
					dk.[Guid],
					dk.[DocumentNumber],
					dk.[SeriesNo],
					dk.[Name],
					dk.[CorrectedDocumentId],
					dk.[IssueDate],
					dk.[OperationDate],
					dk.[IsCorrection],
					dk.[Description],
					dk.[RecipientPerson],
					dk.[BaseType],
					dk.[DocumentTypeShortcut],
					dk.[DocumentNumerationShortcut],
					dk.[IsIssued],
					dk.[Value],
					dk.[DocumentStatus],
					dk.[DocumentStatusDescription],
					dk.[HmImported],
					dk.[Archived],
					dk.[FkExported],
					dk.[IsCreatedFromForeignOrder],
					dk.[Corrected],
					dk.[Canceled],
					dk.[IsActive],
					dk.[MigrationStatus],
					dk.[ContractorId],
					dk.[DepartmentId],
					dk.[AccountingPattern],
					dk.[IssuerId],
					dk.[IssuerLogin],
					dk.[DepartmentInSeries],
					dk.[Period],
					dk.[RDFStatus],
					dk.[ReceiverId],
					dk.[ReceiverAddressName],
					dk.[PayRegistryId],
					dk.[PaymentFormClientId],
					dk.[PaymentDeadline],
					dk.[ValueGross],
					dk.[ValueVat],
					dk.[ValuePLN],
					dk.[ValueGrossPLN],
					dk.[ValueVatPLN],
					dk.[Currency],
					dk.[ExchangeRate],
					dk.[PriceTypeId],
					dk.[Discount],
					dk.[SplitPayment],
					dk.[InRegisterDate],
					dk.[CorrectionCause],
					dk.[PriceKind],
					dk.[InventorySettlementStatus],
					dk.[NoticeText],
					dk.[PublishInventoryDocumentMode],
					dk.[CashSettled],
					dk.[RegisterMethod],
					dk.[ContractorLimitFlag],
					dk.[ContractorLimitValue],
					dk.[ContractorLimitCurrency],
					dk.[CalculateVatMethod],
					dk.[KindId],
					dk.[CatalogId],
					dk.[Marker],
					dk.[ReservationType],
					IIF(LTRIM(RTRIM(ISNULL(ZO.waluta,'')))=LTRIM(RTRIM(dk.Currency)),'true','false') AS CompatibleCurrencies,
					dk.IsSettlementAdvances,
					dk.SettlementAdvancesNet,
					dk.SettlementAdvancesVat,
					dk.SettlementAdvancesGross
				FROM [HM].[ft_SaleDocument](@docId) dk
				     LEFT OUTER JOIN HM.TR AS tdk ON ISNULL(dk.iddokkoryg, dk.id) = tdk.owner_id AND tdk.owner_base = 16
					 OUTER APPLY (SELECT top 1 id1 FROM HM.PO WHERE id2 = tdk.id) PO
					 LEFT OUTER JOIN HM.TR AS tzo ON PO.id1 = tzo.id AND tzo.owner_base = 45
				     LEFT OUTER JOIN HM.ZO ON tzo.owner_id = ZO.id
				FOR XML PATH(''),
					ROOT('SaleDocument')
				)

		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].[ReadSaleDocumentRIAsXML] @docId,
			@resultXml,
			@userId,
			@moduleCode
	END TRY

	BEGIN CATCH
		DECLARE @ErrorNumber INT = ERROR_NUMBER();

		IF (
				@removeInvalidXMLCharacters = 0
				AND (@ErrorNumber IN (9420, 6841))
				)
		BEGIN
			EXEC [HM].[ReadSaleDocumentAsXML] @docId,
				@userId,
				@moduleCode,
				@removeInvalidXMLCharacters = 1;

			GOTO END_CATCH;
		END;

		THROW;

		END_CATCH:
	END CATCH
END
