-- Wartości rozliczeń Netto, Brutto, Vat uwzględniając kurs walut
-- @whichExchangeRate
--       gdy 1 - po kursie faktury rozliczajacej
--           0 - po kursie zaliczek
CREATE OR ALTER FUNCTION HM.ft_AdvanceInvoiceSettlementSaleDocument(@advanceInvoiceDpId int, @saleDocumentDpId int, @whichExchangeRate BIT)
RETURNS TABLE AS RETURN
(
	WITH sett_CTE(ValueGross, ValueGrossPLN, VatFactor, cvmSum)
	AS
	(
		SELECT
			CAST(sett.ValueGross as float) as ValueGross, 
			CAST(ROUND(sett.ValueGross * IIF(@whichExchangeRate = 1, si.kurs, adv.kurs), 2) as float) as ValueGrossPLN,
			IIF(adv.subtyp = 46, 0, xt.wartosc) as VatFactor,
            CAST(IIF(adv.flag & 0x8000 = 0, 0, 1) as bit) as cvmSum      -- calculate vat method as sum
		  FROM HM.AdvanceInvoiceSettlementSaleDocument sett 
		  INNER JOIN HM.DP advp on advp.id = sett.AdvanceInvoiceDpId
		  INNER JOIN HM.DK adv on adv.id = advp.super
		  INNER JOIN HM.XT ON XT.id = advp.stvat
		  INNER JOIN HM.DP AS sip ON sip.id = sett.SaleDocumentDpId
		  INNER JOIN HM.DK AS si ON si.id = sip.super
		  WHERE sett.SaleDocumentDpId = @saleDocumentDpId and sett.AdvanceInvoiceDpId = @advanceInvoiceDpId
	)
	SELECT CAST(ValueGross AS decimal(15,2)) AS ValueGross, CAST(ValueGrossPLN AS decimal(15,2)) AS ValueGrossPLN,
        -- metoda obliczania VAT / zaokraglenie po przecinku :
        -- iloczynowo / 6
        -- sumacyjnie / 2
		CAST(ROUND(ValueGross / (1 + VatFactor), IIF(cvmSum = 0, 6, 2)) AS decimal(15,6)) as ValueNet,
		CAST(ROUND(ValueGrossPLN / (1 + VatFactor), IIF(cvmSum = 0, 6, 2)) AS decimal(15,6)) as ValueNetPLN,
		CAST(ROUND(ValueGross - (ValueGross / (1 + VatFactor)), IIF(cvmSum = 0, 6, 2)) AS decimal(15,6)) as Vat,
		CAST(ROUND(ValueGrossPLN - (ValueGrossPLN / (1 + VatFactor)), IIF(cvmSum = 0, 6, 2)) AS decimal(15,6)) as VatPLN
	FROM sett_CTE
)

