-- @withExtendedInformation
--       gdy 0 - rozliczone zaliczki po kursie faktury rozliczajacej z ujemnym Id
--           1 - DODATKOWO TE SAME
--               rozliczone zaliczki po kursie zaliczek z zerowym Id
CREATE OR ALTER FUNCTION HM.ft_VatRatesExtended (@documentBase INT, @documentId INT, @NpRateId INT, @ZeroRateId INT, @withExtendedInformation BIT)
RETURNS TABLE AS RETURN
(
	-- dokumenty sprzedazy
	SELECT XT.id                                                          AS Id, 
	       XT.kod                                                         AS Code, 
	       SUM(CAST(DP.walNetto AS DECIMAL(24,2)))						  AS Value,
	       SUM(CAST(DP.wartNetto AS DECIMAL(24,2)))						  AS ValuePLN,
	       SUM(CAST(DP.walBrutto AS DECIMAL(24,2)))						  AS ValueGross,
	       SUM(CAST(DP.wartNetto + DP.wartVat AS DECIMAL(24,2)))          AS ValueGrossPLN,
	       SUM(CAST(DP.walBrutto - DP.walNetto AS DECIMAL(24,2)))		  AS ValueVat,
	       SUM(CAST(DP.wartVat AS DECIMAL(24,2)))						  AS ValueVatPLN,
		   'false'														  AS IsVatModifiedManually
	  FROM HM.DP
	       INNER JOIN HM.DK ON DK.id = DP.super
					 --jesli NP to NP, jesli nie, ale dokument to DEX (46) to zerowa w przeciwnym wypadku stawka VAT z pozycji
	       INNER JOIN HM.XT ON XT.id = IIF(DP.stvat = @NpRateId, DP.stvat, IIF(DK.subtyp = 46, @ZeroRateId, DP.stvat))
	 WHERE @documentBase = 16 AND DP.super = @documentId AND (DP.[flag] & 0x4000) <> 0x4000
	GROUP BY XT.kod, XT.id, DK.kurs

	UNION ALL

	-- rozliczone zaliczki po kursie faktury rozliczajacej z ujemnym Id
	SELECT -XT.id                                                         AS Id, 
	       XT.kod                                                         AS Code, 
           -- najpierw sumujemy stosownie zaokraglone zaleznie od metody obliczania VAT
           -- potem zaokraglamy sumy do 2 miejsc po przecinku
	       -CAST(SUM(s.ValueNet) AS DECIMAL(24,2))						  AS Value,
	       -CAST(SUM(s.ValueNetPLN) AS DECIMAL(24,2))					  AS ValuePLN,
	       -CAST(SUM(s.ValueGross) AS DECIMAL(24,2))					  AS ValueGross,
	       -CAST(SUM(s.ValueGrossPLN) AS DECIMAL(24,2))					  AS ValueGrossPLN,
	       -CAST(SUM(s.Vat) AS DECIMAL(24,2))							  AS ValueVat,
	       -CAST(SUM(s.VatPLN) AS DECIMAL(24,2))						  AS ValueVatPLN,
		   'false'														  AS IsVatModifiedManually
	  FROM HM.DP
	  INNER JOIN HM.DK ON DK.id = DP.super
	  INNER JOIN HM.XT ON XT.id = IIF(DP.stvat = @NpRateId, DP.stvat, IIF(DK.subtyp = 46, @ZeroRateId, DP.stvat))
	  INNER JOIN HM.AdvanceInvoiceSettlementSaleDocument sett on sett.SaleDocumentDpId = DP.id
	  CROSS APPLY HM.ft_AdvanceInvoiceSettlementSaleDocument(sett.AdvanceInvoiceDpId, sett.SaleDocumentDpId,1) s
	 WHERE @documentBase = 16 AND DP.super = @documentId AND (DP.[flag] & 0x4000) <> 0x4000 and s.ValueGross <> 0
	GROUP BY XT.kod, XT.id, DK.kurs

	UNION ALL

	-- rozliczone zaliczki po kursie zaliczek z zerowym Id (dla potrzeb wydruku repx)
	SELECT 0												              AS Id, 
	       XT.kod                                                         AS Code, 
           -- najpierw sumujemy stosownie zaokraglone zaleznie od metody obliczania VAT
           -- potem zaokraglamy sumy do 2 miejsc po przecinku
	       -CAST(SUM(s.ValueNet) AS DECIMAL(24,2))						  AS Value,
	       -CAST(SUM(s.ValueNetPLN) AS DECIMAL(24,2))					  AS ValuePLN,
	       -CAST(SUM(s.ValueGross) AS DECIMAL(24,2))					  AS ValueGross,
	       -CAST(SUM(s.ValueGrossPLN) AS DECIMAL(24,2))					  AS ValueGrossPLN,
	       -CAST(SUM(s.Vat) AS DECIMAL(24,2))							  AS ValueVat,
	       -CAST(SUM(s.VatPLN) AS DECIMAL(24,2))						  AS ValueVatPLN,
		   'false'														  AS IsVatModifiedManually
	  FROM HM.DP
	  INNER JOIN HM.DK ON DK.id = DP.super
	  INNER JOIN HM.XT ON XT.id = IIF(DP.stvat = @NpRateId, DP.stvat, IIF(DK.subtyp = 46, @ZeroRateId, DP.stvat))
	  INNER JOIN HM.AdvanceInvoiceSettlementSaleDocument sett on sett.SaleDocumentDpId = DP.id
	  CROSS APPLY HM.ft_AdvanceInvoiceSettlementSaleDocument(sett.AdvanceInvoiceDpId, sett.SaleDocumentDpId,0) s
	 WHERE @documentBase = 16 AND DP.super = @documentId AND (DP.[flag] & 0x4000) <> 0x4000 and s.ValueGross <> 0
			AND @withExtendedInformation = 1
	GROUP BY XT.kod, XT.id, DK.kurs

	UNION ALL

	-- dokumenty zakupu
	SELECT XT.id                                                          AS Id, 
	       XT.kod                                                         AS Code, 
	       -SUM(CAST(DP.walNetto AS DECIMAL(24,2)))						  AS Value,
	       -SUM(CAST(DP.wartNetto AS DECIMAL(24,2)))					  AS ValuePLN,
	       -SUM(CAST(DP.walBrutto AS DECIMAL(24,2)))					  AS ValueGross,
	       -SUM(CAST(DP.wartNetto + DP.wartVat AS DECIMAL(24,2)))         AS ValueGrossPLN,
	       -SUM(CAST(DP.walBrutto - DP.walNetto AS DECIMAL(24,2)))		  AS ValueVat,
	       -SUM(CAST(DP.wartVat AS DECIMAL(24,2)))						  AS ValueVatPLN,
	       IIF(EXISTS((SELECT TOP 1 1 
			FROM HM.DP 
			WHERE DP.super = @documentId AND DP.stvat = XT.id AND (DP.flag & 0x4400) = 0x0400)), 'true', 'false') AS IsVatModifiedManually
	  FROM HM.DP
	       INNER JOIN HM.DK ON DK.id = DP.super
	       INNER JOIN HM.XT ON XT.id = DP.stvat
	 WHERE @documentBase = 34 AND DP.super = @documentId AND (DP.[flag] & 0x4000) <> 0x4000
	GROUP BY XT.kod, XT.id, DK.kurs

	UNION ALL

	-- zamowienia obce
	SELECT XT.id                                                          AS Id, 
	       XT.kod                                                         AS Code, 
	       SUM(CAST(ZP.walNetto AS DECIMAL(24,2)))						  AS Value,
	       SUM(CAST(ZP.wartNetto AS DECIMAL(24,2)))			              AS ValuePLN,
	       SUM(CAST(ZP.walBrutto AS DECIMAL(24,2)))						  AS ValueGross,
	       SUM(CAST(ZP.wartNetto + ZP.wartVat AS DECIMAL(24,2)))          AS ValueGrossPLN,
	       SUM(CAST(ZP.walBrutto - ZP.walNetto AS DECIMAL(24,2)))		  AS ValueVat,
	       SUM(CAST(ZP.wartVat AS DECIMAL(24,2)))						  AS ValueVatPLN,
		   'false'														  AS IsVatModifiedManually
	  FROM HM.ZP
	       INNER JOIN HM.ZO ON ZO.id = ZP.super
	       INNER JOIN HM.XT ON XT.id = ZP.stvat
	 WHERE @documentBase = 45 AND ZP.super = @documentId AND (ZP.[flag] & 0x4000) <> 0x4000
	GROUP BY XT.kod, XT.id, ZO.kurs

	UNION ALL

	-- oferty
	SELECT XT.id																											AS Id, 
	       XT.kod																											AS Code, 
	       SUM(CAST(OA.ValueNet AS DECIMAL(24,2)))																			AS Value,
	       SUM(CAST(OA.ValueNet * ISNULL(O.CurrencyRate, 1) AS DECIMAL(24,2)))												AS ValuePLN,
	       SUM(CAST(OA.ValueGross AS DECIMAL(24,2)))																		AS ValueGross,
	       SUM(CAST(OA.ValueNet * ISNULL(O.CurrencyRate, 1) + OA.ValueVAT AS DECIMAL(24,2)))								AS ValueGrossPLN,
	       SUM(CAST(OA.ValueVAT / IIF(O.CurrencyRate IS NULL OR O.CurrencyRate <= 0, 1, O.CurrencyRate) AS DECIMAL(24,2)))	AS ValueVat,
	       SUM(CAST(OA.ValueVAT AS DECIMAL(24,2)))																			AS ValueVatPLN,
		   'false'																											AS IsVatModifiedManually
	  FROM CM.OfferArticles OA
	       INNER JOIN CM.Offers O ON OA.OfferId = O.Id
	       INNER JOIN HM.XT ON XT.id = OA.VatRateId
	 WHERE @documentBase = 667 AND OA.OfferId = @documentId
	GROUP BY XT.kod, XT.id, O.CurrencyRate
)