USE [CRM0000_CONFIG]
GO

/****** Object:  StoredProcedure [dbo].[synchronizePayments_metalbud]    Script Date: 08/06/2018 16:11:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Adam Cieślakowski
-- Create date: 2017-03-15
-- Description:	Uzupełnianie informacji o płatnościach z TETY oraz potem próba powiązania poprzez wywołanie standardowej procedury. 
-- Oryginalne zapytanie przygotował Krzysztof Czupryniak
-- =============================================
ALTER PROCEDURE [dbo].[synchronizePayments_metalbud]
	-- Add the parameters for the stored procedure here
@startDate      VARCHAR(8)  = 'NULL', --  'NULL' ALBO DATA W FORMACIE YY/MM/DD
@endDate        VARCHAR(8)  = 'NULL', --  'NULL' ALBO DATA W FORMACIE YY/MM/DD
@unpaid          VARCHAR(4)  = 'NULL'       , --  'NULL' - BEZ FILTRA 0 - ROZLICZONE   1 - NIE ROZLICZONE
@firmNR          VARCHAR(50) = 'NULL'    , --  'NULL'  ALBO FIRM NR
@tetaFirmID     VARCHAR(50) = 'NULL'    , --  'NULL'  ALBO EXTERNAL FIRM ID
@invoiceSymbol  VARCHAR(50) = 'NULL'      --  'NULL'  ALBO EXTERNAL INVOCE FULL NUMBER
, @externalPaymentID varchar(50) = NULL --Możliwość wyfiltrowania konkretnej płatności
, @comparePayments bit = 0 /*Porównanie płatności - eksperymentalne*/
, @specialTestFilters bit = 0 /*Tymczasowo do testowania róznych rzeczy*/
, @deletePayments bit = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT @startDate = ISNULL(@startDate, 'NULL'), @endDate = ISNULL(@endDate, 'NULL')
	, @unpaid = ISNULL(@unpaid, 'NULL'), @firmNR = ISNULL(@firmNR, 'NULL')
	, @tetaFirmID = ISNULL(@tetaFirmID, 'NULL'), @invoiceSymbol = ISNULL(@invoiceSymbol, 'NULL')

	declare @invoiceCorrectedValueInFKDescription nvarchar(max) 
	= N'Płatność symulująca różnice między wartością faktury a wartością faktury po korekcie w systemie FK.'

	declare @invoicePayedMoreValueInFkDescription nvarchar(max)
	= N'Płatność symulująca różnice między wartością zapłaconą po korekcie w systemie FK a sumą płatności jakie udalo się znaleźć do tej faktury w systemie FK'

 
create  table #tab
(
    remoteFullNR	   varchar(500),
    firmNR		       varchar(500),
    externalSystemID   varchar(500),
    invoiceIssueDate   datetime null,
    paymentDate		   datetime null,
    currency           varchar(500),
    invoiceGrossValue  decimal(18,2),
    invoiceCorrectedGrossValue  decimal(18,2),
	grossValueIncludingCorrections decimal(18,2),
    grossValue         decimal(18,2),
    paymentSettlementDate  datetime  null,
	paymentDescription nvarchar(max),
	paymentExternalID  nvarchar(max),
	sourceSystemType   nvarchar(20)
);  

create table #tabOracle
(
    remoteFullNR	   varchar(500)  ,
    firmNR		       varchar(500)  ,
    externalSystemID   varchar(500)  ,
    invoiceIssueDate   varchar(500)  ,
    paymentDate		   varchar(500)  ,
    currency           varchar(500)  ,
    invoiceGrossValue  varchar(500)  ,
	invoiceCorrectedGrossValue varchar(500),
	grossValueIncludingCorrections varchar(500),
    grossValue         varchar(500)  ,
    paymentSettlementDate  varchar(500)  null,
	paymentDescription nvarchar(max),
	paymentExternalID  nvarchar(max),
	sourceSystemType   nvarchar(20)
);  

/*Faktyczne uruchomienie na serwerze Metalbud z połączeniem do TETY*/

DECLARE 
@X               NVARCHAR(MAX);
 
SET @X =   
'
SELECT * FROM
OPENQUERY (TETA_CONS_PROD, 
''
SELECT trim(SUBSTR(DEKRET.SYMBOL_DOKUMENTU,1,12))		SYMBOL_DOKUMENTU,
       KK.SYMBOL                                        KLIENT_SYMBOL   , 
       DEKRET.KONR_ID                                   TETA_KLIENTID   ,
       DEKRET.DATA_DOKUMENTU                            DATA_DOKUMENTU  ,
       DEKRET.DATA_PLATNOSCI                            DATA_PLATNOSCI  ,
       WAL.KOD                                          WALUTA          ,
       CASE WHEN WAL.KOD = ''''PLN'''' THEN DEKRET.OBROTY_WN
       ELSE DEKRET.OBROTY_WN_WALUTA END                 WARTOSC_FK      ,
	   RN.WARTOSC_Z_KOR_DOK,
	   RN.zaplaty_z_kor_dok,
       RP.PRZYCHOD_POZ                                  WPLATA          ,
       RP.DATA_UZGODNIENIA_POZ                          DATA_WPLATY     ,
	   RP.IDENTYFIKATOR_KG_DOK,
	   RP.GUID_UZGODNIENIA,
	   RP.SYSTEM_POCHODZENIA_DOK
FROM TETA_ADMIN.RK_DOKUMENTY  DEKRET
INNER JOIN TETA_ADMIN.AP_KONTRAHENCI KK ON KK.ID = DEKRET.KONR_ID
INNER JOIN TETA_ADMIN.RK_WALUTY WAL ON WAL.ID = DEKRET.WALU_ID
INNER JOIN TETA_ADMIN.API_RK_ROZR_NALEZNOSCI RN ON RN.GUID_DOK = DEKRET.GUID
LEFT JOIN TETA_ADMIN.API_RK_ROZR_PLATNOSCI  RP ON  RP.ID_WP_NALEZNOSCI = RN.ID_WP_NALEZNOSCI 
WHERE     DEKRET.KOD_SYSTEMU = ''''OS''''
AND    DEKRET.TYP_DOKUMENTU  IN (''''PKR'''' ,  ''''FVS'''', ''''FKS''''    )   
--AND    (RP.PRZYCHOD_POZ IS NULL OR RP.PRZYCHOD_POZ != 0)
AND    (
            '''''+@UNPAID+''''' = ''''NULL''''
        OR 
          ( '''''+@UNPAID+''''' = ''''1'''' AND  RP.ID_KONTRAHENTA IS NULL    )
        OR 
          ( '''''+@UNPAID+''''' = ''''0'''' AND  RP.ID_KONTRAHENTA IS NOT NULL) 
       )
AND    (
           '''''+@FIRMNR+''''' = ''''NULL''''
        OR KK.SYMBOL = '''''+@FIRMNR+'''''
       )
AND    (
           '''''+@tetaFirmID+''''' = ''''NULL''''
        OR KK.SYMBOL = '''''+@tetaFirmID+'''''
       )
ORDER BY DEKRET.DATA_DOKUMENTU
'') f;'
SET @X = 'insert into #tabOracle ' + @x;
--- Zakomentowany filtr: 
/*RP.SYSTEM_POCHODZENIA_DOK IN   (''''OS'''', ''''BANK'''', ''''FK'''', ''''KOMP'''', ''''KASA'''')
and */

/*ZOBACZ KOMPLETNE ZAPYTANIE*/
--SELECT   @X 

/*ODPAL   ZAPYTANIE PRZEZ SERWER*/
EXEC   (@x)  

--select * from #tabOracle

--Przekonwertowanie do odpowiednich typów danych
insert into #tab
select remoteFullNr, firmNR, externalSystemID,
convert(datetime, left(invoiceIssueDate, 19), 121), convert(datetime, left(paymentDate, 19), 121),
currency, 
TRY_PARSE(invoiceGrossValue  as decimal(18,2)), --  cast(replace(invoiceGrossValue,',','.') as decimal(18,2)), 
TRY_PARSE(invoiceCorrectedGrossValue  as decimal(18,2) ), --    cast(replace(invoiceCorrectedGrossValue,',','.') as decimal(18,2)), 
TRY_PARSE(grossValueIncludingCorrections as decimal(18,2)) ,--   cast(replace(grossValueIncludingCorrections,',','.') as decimal(18,2)),
TRY_PARSE( grossValue as decimal(18,2)), --cast(replace(grossValue,',','.') as decimal(18,2)),
convert(datetime, left(paymentSettlementDate, 19), 121),
paymentDescription, paymentExternalID, sourceSystemType
from #tabOracle

--Usuwam dziwne wpisy, one i tak są dla faktur spoza systemu
--delete from #tab where invoiceGrossValue = 0 or grossValue = 0 or grossValue is null

if @invoiceSymbol is not null and @invoiceSymbol != 'NULL' begin
	delete from #tab where remoteFullNR != @invoiceSymbol
end

if @externalPaymentID is not null begin
	/*Tryb diagnostyczny, który tylko zwraca dane*/
	select * from #tab where paymentExternalID = @externalPaymentID
end
else if @specialTestFilters = 1 begin
	/*Tryb diagnostyczny*/
	select t.* from #tab t 
	inner join crm_metalbud_2016.document.invoice i on i.invoiceFullNR = t.remoteFullNR COLLATE DATABASE_DEFAULT
	inner join crm_metalbud_2016.document.payment p on p.remoteID = i.invoiceID and t.paymentExternalID = p.externalPaymentID COLLATE DATABASE_DEFAULT

	select count(*) from #tab t
	inner join crm_metalbud_2016.document.invoice i on i.invoiceFullNR = t.remoteFullNR COLLATE DATABASE_DEFAULT
	left join  crm_metalbud_2016.document.payment p on p.remoteFullNR = i.invoiceFullNR /*łączenie nie może tu być po id bo jeśli jeszcze id nie jest uzupełnione to płatność by się wstawiła podwójnie*/
		and t.paymentExternalID = p.externalPaymentID COLLATE DATABASE_DEFAULT
	where p.paymentID is null

	select count(*) from #tab t
	inner join crm_metalbud_2016.document.invoice i on i.invoiceFullNR = t.remoteFullNR COLLATE DATABASE_DEFAULT

	select * from #tab where invoiceGrossValue = 0

	select * from #tab where paymentSettlementDate is null

end
else if @comparePayments = 1 begin

	/*Porównanie płatności*/
	select '/*Płatności, które nie mają opisów*/'
	select p.remoteFullNR, p.[description], * 
	from crm_metalbud_2016.document.payment p inner join crm_metalbud_2016.document.invoice i on p.remoteID = i.invoiceID
	where p.[description] is null
	order by i.invoiceIssueDate desc

	/*Płatności, które mają external id ale już nie wskazuje na id w tecie - id się zmieniło?*/
	select '/*Płatności, które mają external id ale już nie wskazuje na id w tecie - id się zmieniło?*/'
	select p.remoteFullNR, p.[description], * 
	from crm_metalbud_2016.document.payment p inner join crm_metalbud_2016.document.invoice i on p.remoteID = i.invoiceID
	left join #tab t on t.paymentExternalID = p.externalPaymentID COLLATE DATABASE_DEFAULT
	where p.externalPaymentID is not null and t.paymentExternalID is null
	order by i.invoiceIssueDate desc

	/*Płatności, które zostały zwrócone z tety a nie ma ich w naszej bazie*/
	select '/*Płatności, które zostały zwrócone z tety a nie ma ich w naszej bazie ale da się znaleźć w naszej bazie fakturę*/'
	select t.* from #tab t 
	inner join crm_metalbud_2016.document.invoice i on i.invoiceFullNR = t.remoteFullNR COLLATE DATABASE_DEFAULT
	left join crm_metalbud_2016.document.payment p on t.paymentExternalID = p.externalPaymentID COLLATE DATABASE_DEFAULT
	where p.paymentID is null and t.grossValue is not null and t.grossValue != 0

	/*Płatności w których nie zgadza się suma*/
	select '/*Płatności w których nie zgadza się suma*/'
	select t.* from #tab t inner join crm_metalbud_2016.document.payment p on t.paymentExternalID = p.externalPaymentID COLLATE DATABASE_DEFAULT
	where p.grossValue != t.grossValue

	/*Płatności wyrównujące stan płatności dla faktur, dla których została w systemie FK skorygowana ręcznie wartość do zapłaty 
	(poprzez rozliczenie korektą do wcześniejszej faktury)*/
	select 'Płatności wyrównujące stan płatności dla faktur, dla których została w systemie FK skorygowana ręcznie wartość do zapłaty a nie są w platformie wyrównane'

	select i.invoiceFullNR, i.invoiceIssueDate, invoiceCorrectedGrossValue, t.invoiceGrossValue fkInvoiceGrossValue, sum(t.grossValue) fkPayemntsGrossValue
	, invoiceGrossValue - invoiceCorrectedGrossValue fkGrossDifference, p.grossValue platformaSimulatedPayments
	from #tab t inner join crm_metalbud_2016.document.invoice i on i.invoiceFullNR = t.remoteFullNR COLLATE DATABASE_DEFAULT
	left join (select remoteID, sum(grossValue) grossValue from crm_metalbud_2016.document.payment where [description] = @invoiceCorrectedValueInFKDescription group by [remoteID]) p on p.remoteID = i.invoiceID
	where invoiceCorrectedGrossValue != invoiceGrossValue and (p.remoteID is null or invoiceGrossValue - invoiceCorrectedGrossValue != p.grossValue)
	group by i.invoiceFullNR, i.invoiceIssueDate, invoiceCorrectedGrossValue, t.invoiceGrossValue, p.grossValue
	having t.invoiceGrossValue > isnull(sum(t.grossValue), 0)
	order by i.invoiceIssueDate

	select 'Wylistowanie tych płatności gdzie różni się wartość wynikająca z płatności z tą która jest zapisana na fakturze'
	/*Wylistowanie tych płatności gdzie różni się wartość wynikająca z płatności w FK z tą która jest zapisana na fakturze w FK*/
	;with groupedFkInvoices as (
		select remoteFullNR, grossValueIncludingCorrections, sum(t.grossValue) paymentsGrossValue
		from #tab t group by remoteFullNR, grossValueIncludingCorrections
	)
	, finalGroupsFkInvoices as (
		select remoteFullNR, sum(grossValueIncludingCorrections) grossValueIncludingCorrections, sum(paymentsGrossValue) paymentsGrossValue
		from groupedFkInvoices group by remoteFullNR
	)
	select i.invoiceFullNR, i.invoiceID, i.currency, grossValueIncludingCorrections - paymentsGrossValue difference
	, p.paymentID, p.grossValue, p.[description]
	from finalGroupsFkInvoices t
	inner join crm_metalbud_2016.document.invoice i on i.invoiceFullNR = t.remoteFullNR COLLATE DATABASE_DEFAULT
	left join crm_metalbud_2016.document.payment p on p.remoteID = i.invoiceID and p.[description] = @invoicePayedMoreValueInFkDescription
	where grossValueIncludingCorrections > t.paymentsGrossValue 
	and (p.remoteID is null or grossValueIncludingCorrections - t.paymentsGrossValue != p.grossValue)
	order by i.invoiceIssueDate

end
else if @invoiceSymbol is not null and @invoiceSymbol != 'NULL' begin
	select * from #tab where remoteFullNR = @invoiceSymbol

		;with groupedFkInvoices as (
		select remoteFullNR, grossValueIncludingCorrections, sum(t.grossValue) paymentsGrossValue
		from #tab t group by remoteFullNR, grossValueIncludingCorrections
	)
	, finalGroupsFkInvoices as (
		select remoteFullNR, sum(grossValueIncludingCorrections) grossValueIncludingCorrections, sum(paymentsGrossValue) paymentsGrossValue
		from groupedFkInvoices group by remoteFullNR
	)
	select * from finalGroupsFkInvoices

	select remoteFullNR, grossValueIncludingCorrections, sum(t.grossValue) paymentsGrossValue
	from #tab t group by remoteFullNR, grossValueIncludingCorrections


end
else begin


	/*Faktyczna synchronizacja następuje w tym bloku kodu*/

	if @deletePayments = 1 begin
		/*Ewentualne usunięcie płatności - potrzebne przy przełączaniu się na zmieniony mechanizm*/
		delete from crm_metalbud_2016.document.payment
	end

	/*Płatności, które mają external id ale już nie wskazuje na id w tecie - id się zmieniło?*/
	select '/*Płatności, które mają external id ale już nie wskazuje na id w tecie - id się zmieniło?*/'
	delete from crm_metalbud_2016.document.payment where paymentID in (
	select p.paymentID 
	from crm_metalbud_2016.document.payment p inner join crm_metalbud_2016.document.invoice i on p.remoteID = i.invoiceID
	left join #tab t on t.paymentExternalID = p.externalPaymentID COLLATE DATABASE_DEFAULT
	where p.externalPaymentID is not null and t.paymentExternalID is null)

	/*Wstawienie płatności, które jeszcze nie są wstawione, identyfikuje je po id uzgodnienia, opis nie we wszystkich przypadkach jest uzupełniony*/
	insert into crm_metalbud_2016.document.payment(paymentSettlementDate, remoteFullNR, grossValue, currency, [description], externalPaymentID
	, addDate, modDate, addUsername, modUsername)
	select t.paymentSettlementDate, t.remoteFullNR, t.grossValue, t.currency, t.paymentDescription, t.paymentExternalID
	, getutcdate(), getutcdate(), 'sys', 'sys'
	from #tab t
	inner join crm_metalbud_2016.document.invoice i on i.invoiceFullNR = t.remoteFullNR COLLATE DATABASE_DEFAULT
	left join  crm_metalbud_2016.document.payment p on p.remoteFullNR = i.invoiceFullNR and t.paymentExternalID = p.externalPaymentID COLLATE DATABASE_DEFAULT
	where p.paymentID is null and t.grossValue is not null and t.grossValue != 0

	/*WARTOSC_Z_KOR_DOK START*/
	/*Wstawienie płatności wyrównujących stan płatności dla faktur, dla których została w systemie FK skorygowana ręcznie wartość do zapłaty*/
	declare @correctedInFk table(invoiceFullNR varchar(50), invoiceID uniqueidentifier, currency varchar(3), paymentDate smalldatetime
	, fkInvoiceCorrectedGrossValue decimal(18,2)
		, fkInvoiceGrossValue decimal(18,2), fkPaymentsGrossValue decimal(18,2), fkGrossDifference decimal(18,2) /*różnica do uwzględnienia*/
		/*dane płatności już wstawionej w platformie*/, paymentID uniqueidentifier, paymentGrossValue decimal(18,2), paymentDescription nvarchar(max))

	insert into @correctedInFk
	select i.invoiceFullNR, i.invoiceID, i.currency, i.paymentDate, invoiceCorrectedGrossValue, t.invoiceGrossValue fkInvoiceGrossValue, sum(t.grossValue) fkPaymentsGrossValue
	, invoiceGrossValue - invoiceCorrectedGrossValue grossDifference, p.paymentID, p.grossValue, p.[description]
	from #tab t inner join crm_metalbud_2016.document.invoice i on i.invoiceFullNR = t.remoteFullNR COLLATE DATABASE_DEFAULT
	left join crm_metalbud_2016.document.payment p on p.remoteID = i.invoiceID and p.[description] = @invoiceCorrectedValueInFKDescription
	where invoiceCorrectedGrossValue != invoiceGrossValue
	group by i.invoiceFullNR, i.invoiceID, i.currency, i.paymentDate, invoiceCorrectedGrossValue, t.invoiceGrossValue, p.paymentID, p.grossValue, p.[description]
	having t.invoiceGrossValue > isnull(sum(t.grossValue), 0)

	/*Aktualizacja płatności już wstawionych jeśli się różnią*/
	update crm_metalbud_2016.document.payment
	set grossValue = dfk.fkGrossDifference
	from crm_metalbud_2016.document.payment
	inner join @correctedInFk dfk on dfk.paymentID = payment.paymentID and dfk.fkGrossDifference != payment.grossValue

	/*Wstawienie płatności brakujących jeśli nie ma ich jeszcze w bazie danych*/
	insert into crm_metalbud_2016.document.payment(paymentSettlementDate, remoteFullNR, grossValue, currency, [description], externalPaymentID
	, addDate, modDate, addUsername, modUsername)
	select case when getdate() > dfk.paymentDate then dfk.paymentDate else GETDATE() end, dfk.invoiceFullNR, dfk.fkGrossDifference, dfk.currency, @invoiceCorrectedValueInFKDescription, NULL
	, getutcdate(), getutcdate(), 'sys', 'sys'
	from @correctedInFk dfk
	where dfk.paymentID is null
	/*WARTOSC_Z_KOR_DOK END*/

	/*ZAPLATY_Z_KOR_DOK*/

	declare @paymentsDiffInFK table(invoiceFullNR varchar(50), invoiceID uniqueidentifier, currency varchar(3), paymentDate smalldatetime, fkGrossDifference decimal(18,2)
	/*dane płatności już wstawionej w platformie*/, paymentID uniqueidentifier, paymentGrossValue decimal(18,2), paymentDescription nvarchar(max))

	/*Wylistowanie tych płatności gdzie różni się wartość wynikająca z płatności w FK z tą która jest zapisana na fakturze w FK*/
	;with groupedFkInvoices as (
		select remoteFullNR, grossValueIncludingCorrections, sum(t.grossValue) paymentsGrossValue
		from #tab t group by remoteFullNR, grossValueIncludingCorrections
	)
	, finalGroupsFkInvoices as (
		select remoteFullNR, sum(grossValueIncludingCorrections) grossValueIncludingCorrections, sum(paymentsGrossValue) paymentsGrossValue
		from groupedFkInvoices group by remoteFullNR
	)
	insert into @paymentsDiffInFK(invoiceFullNR, invoiceID, currency, paymentDate, fkGrossDifference
	, paymentID, paymentGrossValue, paymentDescription)
	select i.invoiceFullNR, i.invoiceID, i.currency, i.paymentDate, grossValueIncludingCorrections - paymentsGrossValue difference
	, p.paymentID, p.grossValue, p.[description]
	from finalGroupsFkInvoices t
	inner join crm_metalbud_2016.document.invoice i on i.invoiceFullNR = t.remoteFullNR COLLATE DATABASE_DEFAULT
	left join crm_metalbud_2016.document.payment p on p.remoteID = i.invoiceID and p.[description] = @invoicePayedMoreValueInFkDescription
	where grossValueIncludingCorrections > t.paymentsGrossValue
	order by i.invoiceIssueDate
	
	/*Aktualizacja płatności już wstawionych jeśli się różnią*/
	update crm_metalbud_2016.document.payment
	set grossValue = dfk.fkGrossDifference
	from crm_metalbud_2016.document.payment
	inner join @paymentsDiffInFK dfk on dfk.paymentID = payment.paymentID and dfk.fkGrossDifference != payment.grossValue


	/*Wstawienie płatności brakujących jeśli nie ma ich jeszcze w bazie danych*/
	insert into crm_metalbud_2016.document.payment(paymentSettlementDate, remoteFullNR, grossValue, currency, [description], externalPaymentID
	, addDate, modDate, addUsername, modUsername)
	select case when getdate() > dfk.paymentDate then dfk.paymentDate else GETDATE() end
	, dfk.invoiceFullNR, dfk.fkGrossDifference, dfk.currency, @invoicePayedMoreValueInFkDescription, NULL
	, getutcdate(), getutcdate(), 'sys', 'sys'
	from @paymentsDiffInFK dfk
	where dfk.paymentID is null
	/*ZAPLATY_Z_KOR_DOK*/



	/*Uzupełnianie faktycznych powiązań poprzez uniwersalną procedurę uzupełniającą brakujące dane z systemu platformacrm*/
	exec crm_metalbud_2016.[document].[payment_processExternalPayments] @username = 'sys'

	/*Aktualizacja daty ostatniej udanej automatycznej synchronizacji*/
	insert into crm_metalbud_2016.interface.import([description], importType, [status], xmlFile)
	values ('payments', 'paymentsAutoSync', 1, '')


	exec crm_metalbud_2016.document.invoiceComputed_Calculate


end


END

GO


