CREATE procedure [C6MartPeriodico].[INSERISCI_FASE2_PER_CAMBIO_RETE]
AS
--Creo un cursore per ciclare sui cf per i quali devo ri-creare la fase2
DECLARE Cur_CF CURSOR FOR 
	SELECT [RETE_C],[RETE_R],[CODFIS],[FLAG_ELAB],DT_ELAB FROM [C6StampeCentralizzate].[C6MartPeriodico].[ANAG_CAMBIO_RETE]
--creo la tabella temporanea dove inserire la riga letta
declare	@t_DTCHRETE as datetime
declare	@t_COGNOME as varchar(50)
declare	@t_RETE_C [varchar](1) 
declare	@t_RETE_R [varchar](1) 
declare	@t_CODFIS [varchar](16) 
declare	@t_CODAGE_C [varchar](6)
declare	@t_CODAGE_R [varchar](6)
declare	@t_FLAG_ELAB [varchar](1)
declare	@t_DT_ELAB [datetime]  
----------------------------------------------------------
DECLARE @DataFase datetime
DECLARE @DataFineAvanzato datetime
SET @DataFase = CONVERT(DATETIME, [c6newcu].C6MARTPERIODICO.GETTRIMESTRE2('2019-02-14',0), 103) 
SET @DataFineAvanzato = '2019-02-14' --IMPOSTO LA DATA DEL BACKUP DEL DB TRIMESTRALE
----------------------------------------------------------
--ATTIVARE il CURSORE
OPEN Cur_CF
--PRELEVO la prossima riga 
FETCH NEXT FROM Cur_CF into @t_RETE_C,@t_RETE_R,@t_CODFIS,@t_FLAG_ELAB,@t_DT_ELAB
--Finchè trovo nuove righe...
WHILE @@FETCH_STATUS = 0
begin
		--print @t_RETE_R + '/' + @t_CODFIS + ' <' + @t_FLAG_ELAB + '>'
		if @t_FLAG_ELAB = 'S'
			begin
			-- inserisco nella tabella di log
			INSERT INTO [C6MartPeriodico].[LOG_CAMBIO_RETE] VALUES (getdate(),@t_RETE_C,@t_RETE_R,@t_CODFIS, 'GIA'' ELABORATO IN DATA '+LEFT(CONVERT(VARCHAR, @t_DT_ELAB, 120), 10));
			end
		ELSE
			begin
			--**********************************************************************************************
			--                   inserimento in [consulenzaUnica].dbo.REP_S80_EVOL_STAMPA_C6
			--               query estratta dalla sp di ribaltamento dei dati in post trimestrale
			--
			--**********************************************************************************************
			insert into [CONSUNI].[consulenzaUnica].dbo.REP_S80_EVOL_STAMPA_C6
			SELECT 
				A.ChiaveClientePB,
				 CASE
					 WHEN AREA IN ('SELFXY', 'SELFFO') THEN 'SELF'
					ELSE UPPER(AREA)
				 END as AREA,
				CASE
					 WHEN AREA = 'Inv' THEN 2
					 WHEN AREA = 'Pre' THEN 3
					 WHEN AREA = 'Ris' THEN 4
					 WHEN AREA = 'Ext' THEN 1
					 WHEN AREA = 'Liq' THEN 5
					 WHEN AREA = 'Na' THEN 6
					 WHEN AREA = 'Cc' THEN 7
					 WHEN AREA = 'SelfXY' THEN 8
					 WHEN AREA = 'SelfFO' THEN 9
					 ELSE 0
				END AS ORDINE,
				FASE,
				@DataFase AS DATA_FASE,
				CONTROVALORE,
				SUM(RISORSEALLOCATE) OVER (PARTITION BY A.COD_FISCALE,A.RETE) AS RISORSEALLOCATE,
				SUM(RISORSENONALLOCATE) OVER (PARTITION BY A.COD_FISCALE,A.RETE) AS RISORSENONALLOCATE,
				SUM(CONTOCORRENTE) OVER (PARTITION BY A.COD_FISCALE,A.RETE) AS CONTOCORRENTE,
				SUM(PVDISINV) over (partition by A.cod_fiscale,A.RETE) + isnull(PVINV,0) AS INVESTIMENTIINCORSO,
				VAR_PERC_PTF,
				MAX_VAR,
				RA.COPERTURA,
				MFD.PROFILO_ASS AS PROFILO,
				COD_AGENTE AS AGENTE,
				case 
					when isnull(ra.CREDITRISK,0) > 0 
						then cast(ra.CREDITRISK  as varchar(5))
						--else 'n.a.'
						else '5'
				end AS RISKCLASS,
				MFD.RISKCLASS AS RISKCLASSMAX,
				CASE WHEN AREA IN ('SelfXY') THEN CONTROVALORE ELSE 0 END AS LineaSelf,
				CASE WHEN AREA IN ('SelfFO') THEN CONTROVALORE ELSE 0 END AS LineaSelfFoglio
			FROM
				(SELECT
					VC.ChiaveClientePB,
					VC.COD_AGENTE,
					PBF.COD_FISCALE,
					PBF.RETE,
					CASE
						WHEN PBF.ID_AREA = 'Self' and PBF.COD_INTERNO = 'XY' THEN 'SELFXY'
						WHEN PBF.ID_AREA = 'Self' and PBF.COD_INTERNO = 'FO' THEN 'SELFFO'
						ELSE LEFT(PBF.ID_AREA,3)
					END as AREA,
					2 AS FASE,
					SUM(CTV) AS CONTROVALORE,
					sum(isnull(PARTVIA_DISINV,0)) AS PVDISINV,
					max(isnull(PVIA.IMPORTO,0)) AS PVINV,
					SUM(CASE WHEN ID_AREA IN ('Na','Cc') THEN 0 ELSE PBF.CTV END) AS  RISORSEALLOCATE,
					SUM(CASE WHEN ID_AREA IN ('Na') THEN  PBF.CTV ELSE 0 END) AS  RISORSENONALLOCATE,
					SUM(CASE WHEN ID_AREA IN ('Cc') THEN PBF.CTV ELSE 0 END) AS  CONTOCORRENTE
				FROM
					[c6stampecentralizzate].C6MART.VCONTRATTI VC
				INNER JOIN
					[c6newcu].C6MARTPERIODICO.PATRIMONIO_BF PBF
				ON PBF.rete = @t_RETE_C
					AND VC.cod_Fiscale = PBF.cod_fiscale
				LEFT OUTER JOIN 
					[c6newcu].C6MARTPERIODICO.PARTITE_VIAGGIANTI PVIA
				ON
					PBF.COD_FISCALE=PVIA.COD_FISCALE 
					AND PBF.RETE=PVIA.RETE
				INNER JOIN
					[c6newcu].C6MARTPERIODICO.GESTIONE_PDF_FTP PDFFTP
				ON
					VC.COD_FISCALE=PDFFTP.CODICEFISCALE
					AND PDFFTP.RETE = @t_RETE_C
				WHERE 1=1
					AND TIPOREPORT = 'M'
					AND FLAGINVIO='S'
					AND ISNULL(VC.DATA_FINEAVANZATO, '99991231') > @DataFineAvanzato
					and vc.cod_fiscale = @t_CODFIS 
				GROUP BY
					VC.ChiaveClientePB,
					VC.COD_AGENTE,
					PBF.COD_FISCALE,
					PBF.RETE,
					CASE
						WHEN PBF.ID_AREA = 'Self' and PBF.COD_INTERNO = 'XY' THEN 'SELFXY'
						WHEN PBF.ID_AREA = 'Self' and PBF.COD_INTERNO = 'FO' THEN 'SELFFO'
						ELSE LEFT(PBF.ID_AREA,3)
					END
				) A
				LEFT OUTER JOIN [c6newcu].C6MartPeriodico.RISCHIO_AGGREGATO AS RA
					ON A.RETE = RA.RETE
					AND A.COD_FISCALE = RA.COD_FISCALE
				INNER JOIN [c6newcu].C6MartPeriodico.MIFID AS MFD
					ON A.RETE = MFD.RETE
					AND A.COD_FISCALE = MFD.COD_FISCALE
				INNER JOIN [c6newcu].C6MartPeriodico.CODIFICA_RISCHIO AS CR
					ON MFD.PROFILO_ASS = CR.PROFILO
				WHERE COD_AGGREG = 'COMPLESSIVO|BF'
			--**********************************************************************************************
			--                   FINE inserimento in [consulenzaUnica].dbo.REP_S80_EVOL_STAMPA_C6
			--               
			--**********************************************************************************************
			-- inserisco nella tabella di log
			INSERT INTO [C6MartPeriodico].[LOG_CAMBIO_RETE] VALUES (getdate(),@t_RETE_C,@t_RETE_R,@t_CODFIS, 'Inserite ' +  Convert(Varchar(12),@@rowCount) + ' righe' );
			end
		--Continuo 
		FETCH NEXT FROM Cur_CF into @t_RETE_C,@t_RETE_R,@t_CODFIS,@t_FLAG_ELAB,@t_DT_ELAB
end
--CHIUDO il CURSORE
CLOSE Cur_CF
--RIMUOVO il CURSORE dalla memoria
DEALLOCATE Cur_CF