/****** Object:  Trigger [dbo].[tbcPrejetiRacuniTrigger]    Script Date: 12/03/2014 09:52:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tbcPrejetiRacuniTrigger]
ON [dbo].[tbcPrejetiRacuni]
FOR INSERT
NOT FOR REPLICATION
AS
Begin

IF @@RowCount = 0 Return

Begin Transaction
SET NoCount ON
SET ARITHABORT ON


DECLARE @sAction VarChar(10)

IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) SET @sAction = 'UPDATE'
ELSE IF EXISTS(SELECT * FROM inserted) SET @sAction = 'INSERT'
ELSE IF EXISTS(SELECT * FROM deleted)  SET @sAction = 'DELETE'
ELSE                                   SET @sAction = ''

IF @sAction = 'INSERT' Begin

DECLARE @OdbitniDelez money
DECLARE @gPID nVarChar(3)

SET @gPID='01'
SELECT @OdbitniDelez=ROUND(OdbitniDelez/100,4) FROM tgkNastavitev WHERE PID=@gPID 
-- Če spodnjega komentarja ne rabiš, ga raje zbriši, ker takšen nikoli ne sme biti v triggerju. Je pa sintaktično pravilen.
--SELECT convert(varchar(8), getdate(), 112) Sandi, pazi: takšen stavek, ki je zakomentiran najbrž zato, ker si hotel videt kako gre, je lahko ZELO nevaren, če se odkomentira. SELECT, ki ni del INSERTA, ampak kar tako, da nekaj pokaže, zmede trigger.
INSERT INTO tgkBoleRac (PID, PoslLeto, KontoIDP, KontoID, PartnerIDP, PartnerID,
                        VdIDP, VdID, BCDocumentId, StDok, Veza,  
                        DatBilanca,DatDok, DatValute, DatDur,DatPrejema,DatDDV, 
                        SmIDP, SmID, NosilIDP, NosilID, Protikonto,
                        DobavStevilka, OpisDok, DatKnjiz,
                        Program, BankaNasaID, BankaPartnerID,
                        Zr1, Zr2, Zr3,  
                        DavekIDP, DavekID, InventarnaStevilka,
                        MeniRacuna, LikvidatorID, VrNaroc,  
                        ZnesekOsnova, ZnesekDDV,
                        ZnesekOdbitniDelez,
                        ZnesekPfax,
                        VdIDPfax,KontoDDV, PrenosVGk
                        )

SELECT '01' AS PID, year(CAST(ServiceDate as DateTime)) as PoslLeto,'01' AS KontoIDP, IsNull(P.KontoIDDob,'') as KontoID, '01' AS PartnerIDP, ISNULL(E.PartnerID,'') AS PartnerID,
       '01' AS VdIDP, CASE WHEN IsNull(D.Procent,0)=0 THEN LEFT(IsNull(E.VdID,'PFA'),3) + 'o' ELSE E.VdID END as VdID, ISNULL(BCDocumentId,'') AS BCDocumentId, ISNULL(StDok,'') AS StDok, ISNULL(StDok,'') AS Veza,
       CAST(ServiceDate as DateTime) AS DatBilanca, CAST(InvoiceDate as DateTime) AS DatDok, CAST(DateDue as DateTime) AS DatValute, CAST(ServiceDate as DateTime) AS DatDur, CAST(CreatedDate as DateTime) AS DatPrejema, CAST(CreatedDate as DateTime) AS DatDDV,
       '01' AS SmIDP, ISNULL(CostCenter,'') AS SmID, '01' AS NosilIDP, ISNULL(CostUnit,'000') AS NosilID, ISNULL(Account,'') AS ProtiKonto, 
       DobavStevilka as DobavStevilka, ISNULL(Description,'') AS OpisDok, convert(varchar(8), getdate(), 112) AS DatKnjiz, 
       'Gk' AS Program, '01' as BankaNasaID,B.BankaPartnerID,
       CASE WHEN left(IBAN,2)='SI' THEN SUBSTRING(iban,5,35) ELSE Iban END AS Zr1, left(ISNULL(Reference,''),4) AS Zr2, ltrim(substring(ISNULL(Reference,''),5,35)) AS Zr3, 
 
      '01' AS DavekIDP, ISNULL(E.DavekID,'') AS DavekID, CASE WHEN ISNULL(InventoryNumber,'')<>'' THEN 'OS'+ISNULL(InventoryNumber,'') ELSE '' END AS InventarnaStevilka,

       CASE WHEN LEFT(LikvidatorID,4)='2819' THEN 'LX1-LEKARNA' ELSE
          CASE WHEN LEFT(LikvidatorID,4)='7500' THEN 'LX2-NABAVA' ELSE     
             CASE WHEN LEFT(LikvidatorID,4)='3100' THEN 'LX3-SPV' ELSE
                CASE WHEN LEFT(LikvidatorID,4)='5000' THEN 'LX4-KADROVSKA' ELSE     
                   CASE WHEN LEFT(LikvidatorID,4)='2965' THEN 'LX5-OSTALO' ELSE     
                      CASE WHEN LEFT(LikvidatorID,4)='6000' THEN 'LX5-OSTALO' ELSE     
                         CASE WHEN LEFT(LikvidatorID,4)='8000' THEN 'LX5-OSTALO' ELSE     
                            CASE WHEN LEFT(LikvidatorID,4)='7000' THEN 'LX5-OSTALO' ELSE     
                               CASE WHEN LEFT(LikvidatorID,4)='7100' THEN 'LX6-LAB.STORITVE' ELSE 'LX5-OSTALO' END 
                            END 
                         END 
                      END 
                   END 
                END 
             END 
          END 
       END as MeniRacuna, ISNULL(LikvidatorID,'') AS LikvidatorID,  ISNULL(OrderType,'') AS VrNaroc, 
       NetAmount AS ZnesekOsnova, Round(NetAmount*ISNULL(D.Procent,0)/100,2) as ZnesekDDV, 
       CASE WHEN IsNull(D.BrezOdbitnegaDeleza,0)=1 THEN 0 ELSE Round(Round(NetAmount*ISNULL(D.Procent,0)/100,2)*@OdbitniDelez,2) END as ZnesekOdbitniDelez,
       Round(NetAmount*ISNULL(D.Procent,0)/100,2) - CASE WHEN IsNull(D.BrezOdbitnegaDeleza,0)=1 THEN 0 ELSE Round(Round(NetAmount*ISNULL(D.Procent,0)/100,2)*@OdbitniDelez,2) END as ZnesekPfax,
       LEFT(IsNull(E.VdID,'PFA'),3) + 'x' as VdIDPFax, ISNULL(D.KontoID,'') as KontoDDV,0 as PrenosVGk
FROM (SELECT
--* glava
              i.PodatkiRacun.value('(Invoice/InvoiceHeader/BCDocumentId)[1]', 'nVarChar(33)') AS BCDocumentId,
              i.PodatkiRacun.value('(Invoice/InvoiceHeader/SBCInvoiceNumber)[1]', 'nVarChar(15)') AS StDok,
              i.PodatkiRacun.value('(Invoice/InvoiceHeader/InvoiceNumber)[1]', 'nVarChar(15)') AS DobavStevilka,
              i.PodatkiRacun.value('(Invoice/InvoiceHeader/PartnerID)[1]', 'nVarChar(35)') AS PartnerID,
              i.PodatkiRacun.value('(Invoice/InvoiceHeader/Reference)[1]', 'nVarChar(35)') AS Reference,
              i.PodatkiRacun.value('(Invoice/InvoiceHeader/ServiceDate)[1]', 'nVarChar(19)') AS ServiceDate,
              i.PodatkiRacun.value('(Invoice/InvoiceHeader/CreatedDate)[1]', 'nVarChar(19)') AS CreatedDate,
              i.PodatkiRacun.value('(Invoice/InvoiceHeader/InvoiceDate)[1]', 'nVarChar(19)') AS InvoiceDate,
              i.PodatkiRacun.value('(Invoice/InvoiceHeader/DateDue)[1]', 'nVarChar(19)') AS DateDue,
              i.PodatkiRacun.value('(Invoice/InvoiceHeader/Signer)[1]', 'nVarChar(35)') AS LikvidatorID,
              i.PodatkiRacun.value('(Invoice/InvoiceHeader/IBAN)[1]', 'nVarChar(35)') AS IBAN,
              i.PodatkiRacun.value('(Invoice/InvoiceHeader/InvoiceType)[1]', 'nVarChar(35)') AS VdID,
--* telo
              x.value('(CostCenter)[1]', 'nVarChar(35)') AS CostCenter,
              x.value('(Account)[1]', 'nVarChar(35)') AS Account,
              x.value('(NetAmount)[1]', 'money') AS NetAmount,
              x.value('(Tax)[1]', 'nVarChar(35)') AS DavekID,
              x.value('(Description)[1]', 'nVarChar(35)') AS [Description],
              x.value('(CostUnit)[1]', 'nVarChar(35)') AS CostUnit,
              x.value('(InventoryNumber)[1]', 'nVarChar(35)') AS InventoryNumber,
              x.value('(OrderType)[1]', 'nVarChar(35)') AS OrderType,
              x.value('(InvestmentNumber)[1]', 'nVarChar(35)') AS InvestmentNumber
FROM inserted i
CROSS APPLY PodatkiRacun.nodes('Invoice/InvoiceLines/InvoiceLine') AS p(x)

) E LEFT OUTER JOIN tsiPartner P ON '01'=P.PartnerIDP AND E.PartnerID=P.PartnerID
                   LEFT OUTER JOIN tsiDavek D ON '01'=D.DavekIDP AND E.DavekID=D.DavekID
                   LEFT JOIN tsiBankaPartner B ON '01'=B.PartnerIDP AND E.PartnerID=B.PartnerID AND E.IBAN=B.PartnerZR1

UPDATE tbcPrejetiRacuni 
SET PrenosVGk=1, DatumPrenosaVGk=GetDate()
FROM tbcPrejetiRacuni INNER JOIN inserted ON tbcPrejetiRacuni.Zaporedje=inserted.Zaporedje

Commit Transaction
End--*IF INSERT
End--*Trigger