Index: trunk/SQL/StoredProcedures/sp_ImportCDNPayments.txt
===================================================================
--- trunk/SQL/StoredProcedures/sp_ImportCDNPayments.txt (revision 491)
+++ trunk/SQL/StoredProcedures/sp_ImportCDNPayments.txt (revision 491)
@@ -0,0 +1,150 @@
+
+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
+
Index: trunk/SQL/Jobs/Wplaty_CDN_BazaReklam.txt
===================================================================
--- trunk/SQL/Jobs/Wplaty_CDN_BazaReklam.txt (revision 491)
+++ trunk/SQL/Jobs/Wplaty_CDN_BazaReklam.txt (revision 491)
@@ -0,0 +1,125 @@
+
+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'