-- =============================================
-- Author:		Marian Zaki
-- Create date: 31 ottobre 2010
-- Description:	Compare the counter value calculated per need area and client between SP of section S57 and section S43
-- =============================================
CREATE procedure [C6MartPeriodico].[CHECK_CTV_PER_NEEDAREA_PER_CLIENT]
AS
BEGIN
DECLARE @reteLoop   varchar(1)
DECLARE @cod_FiscaleLoop varchar(16)
Create Table #tempDifferenceInCTVPerAreaPerClient (
RETE varchar(1),
Cod_Fiscale varchar(16),
NeedArea VARCHAR(20),
S57CTV decimal(38,3),
S43CTV decimal(38,3)
)
CREATE TABLE #tempS57Output (
	NeedArea VARCHAR(20), 
	AreaName VARCHAR(30),
	PercentualeModello decimal(38,3),
	PercentualeAttuale decimal(38,3),
	ControvaloreAttuale decimal(38,3), 
	ControvaloreModello decimal(38,3),
	Totale_Percentuale decimal (15,3),
	DifferenzaEuro decimal (15,3),
	DifferenzaPercentuale decimal (15,3),
	var_needarea decimal (15,3),
	var_needareaString varchar(5),
	copertura_needarea decimal(38,3),
	copertura_needareaString varchar(5),
	Nota int,
	somma_controvalAttuale decimal(38,3),
	somma_controvalModello decimal(38,3),
	somma_percentualeAttuale decimal(38,3),
	somma_percentualeModello decimal(38,3),
	var_tot decimal(38,3),
	var_totString varchar(5),
	var_tot_pir decimal(38,3),
	var_tot_pirString varchar(5),
	copertura_tot decimal(38,3),
	copertura_totString varchar(5),
	copertura_tot_pir decimal(38,3),
	copertura_tot_pirString varchar(5),
	VAR_RISFIN  decimal(38,3),
	VAR_RISFINSTRING varchar(5),
	copertura_risfin decimal(38,3),
	copertura_risfinString varchar(5),
	ordinamento int)
CREATE TABLE #tempS43Output (
	NeedArea VARCHAR(20), 
	AreaName VARCHAR(30),
	areasortorder int,
	nome_progetto varchar(50),
	somma_controval_needarea decimal(38,3),
	versato_needarea decimal(38,3),
	versato_needareaString varchar(5),
	MinusPlusValenza_needarea decimal(38,3),
	MinusPlusValenza_needareaString varchar(5),
	somma_controval_need_prog decimal(38,3),
	versato_need_prog decimal(38,3),
	versato_need_progString varchar(5),
	MinusPlusValenza_need_prog decimal(38,3),
	MinusPlusValenza_need_progString varchar(5),
	somma_var_need_prog decimal(38,3),
	somma_copertura_need_prog decimal(38,3),
	var_needarea decimal (15,3),
	var_needareaString varchar(5),
	copertura_needarea decimal (15,3),
	copertura_needareaString varchar(30),
	var_need_prog decimal(38,3),
	var_need_progString varchar(5),
	copertura_need_prog decimal(38,3),
	dataSottoscrizione varchar(10),
	ContrattoDossier varchar(11),
	Descrizione varchar(128),
	Controvalore decimal(38,3),
	VersatoNetto decimal(38,3),
	VersatoNettoString varchar(5),
	MinusPlusValenza decimal(38,3),
	MinusPlusValenzaString varchar(5),
	VaRprodotto decimal(38,3),
	varProdottoString varchar(5),
	Coperturaprodotto decimal(38,3),
	PerformanceYTD decimal(38,3),
	performanceDS decimal(38,3),
	partitaViaggiante decimal(38,3),
	nota_needarea  varchar(1),
	creditrisk int,
	creditriskstring varchar(5))
DECLARE allCleints_cursor CURSOR FOR  
--SELECT  distinct [Rete]
--      ,[Cod_Fiscale]
--      
--  FROM C6MartPeriodico.PATRIMONIO_BF
--where cod_fiscale in (select cod_fiscale from c6mart.contrattosei where cod_Esito=1)
--and cod_fiscale in ( select cod_fiscale from campionereport)
SELECT  distinct  [Rete]   ,[Cod_Fiscale]      
FROM C6MartPeriodico.PATRIMONIO_BF
--where cod_fiscale in (select cod_fiscale from c6mart.contrattosei where cod_Esito=1)
 Where [Cod_Fiscale] <> 'DNTTMS48E03F205P'
--and [Cod_Fiscale] = 'BRGNNI46T05H555X'
OPEN allCleints_cursor   
FETCH NEXT FROM allCleints_cursor INTO @reteLoop,   @cod_FiscaleLoop
WHILE @@FETCH_STATUS = 0   
BEGIN   
		print @reteLoop
		print @cod_FiscaleLoop
		INSERT INTO #tempS57Output 
		EXEC C6MartPeriodico.PL_S57ConfrontoPiramidi 		
		@Rete = @reteLoop,
		@CodiceFiscale = @cod_FiscaleLoop
		INSERT INTO #tempS43Output
		EXEC C6MartPeriodico.PL_S43DettaglioProdottiBF 		
		@Rete = @reteLoop,
		@CodiceFiscale = @cod_FiscaleLoop
--		select * from #tempS43Output
		insert into #tempDifferenceInCTVPerAreaPerClient
		select  @reteLoop  ,@cod_FiscaleLoop  ,
		S57.NeedArea as NeedArea, 
		SUM(S57.ControvaloreAttuale) as S57TotalCounterValuePerCleint,
		case when SUM(tempS43.Controvalore) is null then 0 else SUM(tempS43.Controvalore) end  as S43TotalCounterValuePerCleint
			from #tempS57Output as S57 
			left outer join ( select  S43.NeedArea,  SUM(S43.Controvalore)   as Controvalore
							  from #tempS43Output as S43 
							  where S43.NeedArea not in ('Cc','Na') 
							  group by S43.NeedArea) tempS43
			on S57.NeedArea = tempS43.NeedArea
			where S57.NeedArea not in ('Cc', 'Na')
			group by S57.NeedArea   
--select * from #tempDifferenceInCTVPerAreaPerClient
		--select * from #tempDifferenceInCTVPerAreaPerClient
		delete from #tempS43Output
		delete from #tempS57Output
       FETCH NEXT FROM allCleints_cursor INTO @reteLoop,   @cod_FiscaleLoop     
END   
CLOSE allCleints_cursor   
DEALLOCATE allCleints_cursor 
select * from #tempDifferenceInCTVPerAreaPerClient as Diff
where Diff.S43CTV <> Diff.S57CTV
--group by   Diff.Rete, Diff.Cod_Fiscale, Diff.NeedArea
DROP TABLE #tempS57output
DROP TABLE #tempS43output
DROP TABLE #tempDifferenceInCTVPerAreaPerClient
end