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