root/trunk/SQL/StoredProcedures/sp_ImportCDNPayments.txt @ 499

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:              marek
-- Create date: 2009/03/25
-- Description: Importuje wplaty z CDN do Bazy Reklam
-- =============================================
ALTER PROCEDURE [dbo].[sp_ImportCDNPayments] 
AS
BEGIN


DECLARE @lastJobDate datetime
SELECT @lastJobDate=[date] FROM Run_Job WHERE job_name='WplatyCDN' 

PRINT @lastJobDate

DECLARE @CDN TABLE
(
        NumerFaktury nvarchar(25),
        DataWplaty datetime,
        SymbolPotwierdzenia nvarchar(25),
        Brutto decimal(9,2),
        WalutaBrutto decimal(9,2),
        Waluta nvarchar(3)
)


INSERT INTO @CDN
SELECT 
Z.BZd_NumerPelny AS NumerFaktury, 
Z.BZd_DataRoz AS DataWplaty,
K.Brk_LNumer AS SymbolPotwierdzenia, 
Z.BZd_KwotaRozSys AS Brutto, 
Z.BZd_KwotaRoz AS WalutaBrutto,
Waluta = 
CASE
WHEN Z.BZd_Waluta IS NULL OR Z.BZd_Waluta='' THEN 'PLN'
ELSE Z.BZd_Waluta
END

FROM REPLICATOR.CDN_SAM_PRESS.CDN.BnkZdarzenia AS Z
INNER JOIN 
(
SELECT K1.* FROM REPLICATOR.CDN_SAM_PRESS.CDN.BnkRozKwoty AS K1
INNER JOIN 
( 
SELECT  BRK_PDokID, MAX(BRK_TS_Mod) AS BRK_TS_Mod FROM REPLICATOR.CDN_SAM_PRESS.CDN.BnkRozKwoty
WHERE BRK_TS_Mod >= @lastJobDate
GROUP BY BRK_PDokID 
) K2 ON K2.BRK_PDokID=K1.BRK_PDokID AND K2.BRK_TS_Mod=K1.BRK_TS_Mod

) K ON Z.BZd_BZdID=K.BRK_PDokID
WHERE Z.BZd_KatId=1


SELECT * FROM @CDN


DECLARE @Wplaty TABLE
(
        RowNumber INT IDENTITY (1, 1),
        IdFaktury INT,
        Numer INT,
        Roz NVARCHAR(8),
        Rok INT,
        Data DATETIME,
        SymbolPotwierdzenia nvarchar(25),
        Brutto DECIMAL(9,2),
        BruttoWaluta DECIMAL(9,2),
        Waluta NVARCHAR(10)
)

INSERT INTO @Wplaty
SELECT F.Id_Faktury AS IdFaktury,
CAST(dbo.SplitAsScalar(CDN.NumerFaktury, 1, '/') AS INT) AS Numer, 
dbo.SplitAsScalar(CDN.NumerFaktury, 2, '/') AS Roz,
CAST(dbo.SplitAsScalar(CDN.NumerFaktury, 3, '/') AS INT) AS Rok,
CDN.DataWplaty,
CDN.SymbolPotwierdzenia,
CDN.Brutto,
CDN.WalutaBrutto,
CDN.Waluta 
FROM @CDN CDN
LEFT OUTER JOIN Faktury F ON 
CAST(dbo.SplitAsScalar(CDN.NumerFaktury, 1, '/') AS INT)=F.Numer
AND CAST(dbo.SplitAsScalar(CDN.NumerFaktury, 2, '/') AS NVARCHAR(8))=F.Numer_Roz
AND CAST(dbo.SplitAsScalar(CDN.NumerFaktury, 3, '/') AS INT)=F.Numer_Rok
WHERE F.SystemKsiegowyId=2
ORDER BY CDN.DataWplaty


SELECT * FROM @Wplaty


DECLARE @MaxRows int
DECLARE @RowCnt int
SET @RowCnt=1
SELECT @MaxRows=count(*) from @Wplaty
DECLARE @idFaktury INT

DECLARE @Suma decimal(9,2)
WHILE @RowCnt <= @MaxRows
BEGIN
        SELECT @idFaktury=IdFaktury FROM @Wplaty WHERE RowNumber=@RowCnt
        IF (SELECT COUNT(*) FROM Wplaty WHERE idFaktury=@idFaktury)>0
                BEGIN
                        PRINT 'UPDATE dla faktury: ' + CAST(@idFaktury AS NVARCHAR)
                        UPDATE W
                        SET W.dataWplaty=C.Data, W.symbolPotwierdzenia=C.SymbolPotwierdzenia, W.rodzajPotwierdzenia='PRZ', W.brutto=C.Brutto, W.waluta_brutto=C.BruttoWaluta, W.waluta_miano=C.Waluta
                        FROM Wplaty W, @Wplaty C
                        WHERE W.idFaktury=C.idFaktury
                        AND C.idFaktury=@idFaktury
                        
                END
        ELSE
                BEGIN
                        PRINT 'INSERT dla faktury: ' + CAST(@idFaktury AS NVARCHAR)
                        INSERT INTO Wplaty(idFaktury, dataWplaty, symbolPotwierdzenia, rodzajPotwierdzenia, brutto, waluta_brutto, waluta_miano)
                        SELECT IdFaktury, Data, SymbolPotwierdzenia, 'PRZ', Brutto, BruttoWaluta, Waluta
                        FROM @Wplaty
                        WHERE IdFaktury=@idFaktury
                END

        UPDATE F
        SET F.suma_zaplat=C.Brutto, F.zaplata_data=C.Data, F.zaplata_opis=C.SymbolPotwierdzenia
        FROM Faktury F, @Wplaty C
        WHERE F.Id_Faktury=C.idFaktury
        AND C.idFaktury=@idFaktury

        SET @RowCnt=@RowCnt+1
END

UPDATE Run_Job
SET [date]=GETDATE(), job_status='OK'
WHERE job_name='WplatyCDN' 


END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO