-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE procedure [C6MartPeriodico].[GESTIONE_SELECT_S80_FASE2_CU]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DataFase datetime
DECLARE @DataFineAvanzato datetime
SET @DataFase = CONVERT(DATETIME, C6MARTPERIODICO.GETTRIMESTRE2(getdate(),0), 103) --'20130930'
SET @DataFineAvanzato = getdate() --'20130731'
insert into [CONSUNI].ConsulenzaUnica.dbo.REP_S80_EVOL_STAMPA_C6
SELECT 
	A.ChiaveClientePB,
		 -- 20190206
	 CASE
		 WHEN AREA IN ('SELFXY', 'SELFFO') THEN 'SELF'
		ELSE UPPER(AREA)
	 END as AREA,
	--UPPER(AREA) as AREA,
	 --/20190206
	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
		 -- 20190206 nella subquery c'è stato bisogno di differenziare l'Area Self tra XY e FO, quindi adesso bisogna gestire le aree come SELFXY e SELFFO
		 WHEN AREA = 'SelfXY' THEN 8
		 WHEN AREA = 'SelfFO' THEN 9
		 --WHEN AREA = 'Self' THEN 8
		 --/20190206
		 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,
	CASE WHEN ISNULL(W.flgprof,'A')='P' THEN 0.00 ELSE MAX_VAR END AS 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,
	-- 20190206 in base al cod_interno (XY/FO) l'Area è restituita dalla subquery su Patrimonio_BF come SELFXY e SELFFO, una restituisce il controvalore della LineaSelf e l'altra il controvalore della LineaSelfFogli
	CASE WHEN AREA IN ('SelfXY') THEN CONTROVALORE ELSE 0 END AS LineaSelf,
	CASE WHEN AREA IN ('SelfFO') THEN CONTROVALORE ELSE 0 END AS LineaSelfFoglio
	--CASE WHEN AREA IN ('Self') THEN CONTROVALORE ELSE 0 END AS LineaSelf
	--/20190206
FROM
	(SELECT
		VC.ChiaveClientePB,
		VC.COD_AGENTE,
		PBF.COD_FISCALE,
		PBF.RETE,
		-- 20190206
		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,
		--CASE WHEN PBF.ID_AREA = 'Self' THEN PBF.ID_AREA ELSE LEFT(PBF.ID_AREA,3) END AS AREA,
		--/20190206
		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
		C6MART.VCONTRATTI VC
	INNER JOIN
		C6MARTPERIODICO.PATRIMONIO_BF PBF
	ON VC.rete = PBF.rete
		AND VC.cod_Fiscale = PBF.cod_fiscale
    LEFT OUTER JOIN 
		C6MARTPERIODICO.PARTITE_VIAGGIANTI PVIA
    ON
		PBF.COD_FISCALE=PVIA.COD_FISCALE 
		AND PBF.RETE=PVIA.RETE
	INNER JOIN
		C6MARTPERIODICO.GESTIONE_PDF_FTP PDFFTP
	ON
		VC.COD_FISCALE=PDFFTP.CODICEFISCALE
		AND VC.RETE=PDFFTP.RETE
	WHERE 1=1
		AND TIPOREPORT = 'M'
		AND FLAGINVIO='S'
--		AND DEFINITIVO=1
		AND ISNULL(VC.DATA_FINEAVANZATO, '99991231') > @DataFineAvanzato
	GROUP BY
		VC.ChiaveClientePB,
		VC.COD_AGENTE,
		PBF.COD_FISCALE,
		PBF.RETE,
		-- 20190206
		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
		--CASE WHEN PBF.ID_AREA = 'Self' THEN PBF.ID_AREA ELSE LEFT(PBF.ID_AREA,3) END
		--/20190206
	) A
	LEFT OUTER JOIN C6MartPeriodico.RISCHIO_AGGREGATO AS RA
		ON A.RETE = RA.RETE
		AND A.COD_FISCALE = RA.COD_FISCALE
	INNER JOIN C6MartPeriodico.MIFID AS MFD
		ON A.RETE = MFD.RETE
		AND A.COD_FISCALE = MFD.COD_FISCALE
	INNER JOIN C6MartPeriodico.CODIFICA_RISCHIO AS CR
		ON MFD.PROFILO_ASS = CR.PROFILO
		LEFT JOIN C6MartPeriodico.WSEIRPF W
		ON W.rete=A.RETE AND W.chiave_acn=A.COD_FISCALE
	WHERE COD_AGGREG = 'COMPLESSIVO|BF'
---------------------------------------------------------------------------------------
--SELECT 
--	H.ChiaveClientePB,
----	ltrim(rtrim(H.COD_FISCALE)) as COD_FISCALE,
----	H.RETE,
--
--	UPPER(AREA) 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
--		 ELSE 0
--	END AS ORDINE,
--	FASE,
--    --Cast('20110930' as datetime) AS DATA_FASE,
--	--'20091231' AS DATA_FASE,
--	--CONVERT(DATETIME, C6MARTPERIODICO.GETPREVIOUSTRIMESTRE(getdate(),0), 103) AS DATA_FASE,
--	--CONVERT(varchar(8), C6MARTPERIODICO.GETPREVIOUSTRIMESTRE(getdate(),0), 112) AS DATA_FASE,
--	CONVERT(DATETIME, C6MARTPERIODICO.GETTRIMESTRE2(getdate(),0), 103) AS DATA_FASE,
--	CONTROVALORE,
--	SUM(RISORSEALLOCATE) OVER (PARTITION BY H.COD_FISCALE,H.RETE) AS RISORSEALLOCATE,
--	SUM(RISORSENONALLOCATE) OVER (PARTITION BY H.COD_FISCALE,H.RETE) AS RISORSENONALLOCATE,
--	SUM(CONTOCORRENTE) OVER (PARTITION BY H.COD_FISCALE,H.RETE) AS CONTOCORRENTE,
--	sum(PVDISINV) over (partition by h.cod_fiscale,H.RETE) + isnull(PVINV,0) AS INVESTIMENTIINCORSO,
--	VAR_PERC_PTF,
--	MAX_VAR,
--    RA.COPERTURA,
--    M.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,
--    M.RISKCLASS AS RISKCLASSMAX
--FROM
--	(SELECT 
--		VC.ChiaveClientePB,
--		A.COD_FISCALE,
--		A.RETE,
--		LEFT(A.ID_AREA,3) AS AREA,
--		2 AS FASE,
--		SUM(CTV) AS CONTROVALORE,
--		sum(isnull(PARTVIA_DISINV,0)) AS PVDISINV,
--		max(isnull(C.IMPORTO,0)) AS PVINV,
--		SUM(CASE WHEN ID_AREA IN ('Na','Cc') THEN 0 ELSE A.CTV END) AS  RISORSEALLOCATE,
--		SUM(CASE WHEN ID_AREA IN ('Na') THEN  A.CTV ELSE 0 END) AS  RISORSENONALLOCATE,
--		SUM(CASE WHEN ID_AREA IN ('Cc') THEN A.CTV ELSE 0 END) AS  CONTOCORRENTE
--	FROM
--		C6MART.VCONTRATTI VC
--		INNER JOIN
--		C6MARTPERIODICO.PATRIMONIO_BF A
--		ON VC.rete = A.rete
--		AND VC.cod_Fiscale = A.cod_fiscale
--        LEFT OUTER JOIN 
--		C6MARTPERIODICO.PARTITE_VIAGGIANTI C
--        ON
--		A.COD_FISCALE=C.COD_FISCALE 
--		AND A.RETE=C.RETE
--		WHERE  A.COD_FISCALE IN (
--			SELECT DISTINCT
--				CODICEFISCALE
--			FROM
--				C6MARTPERIODICO.GESTIONE_PDF_FTP B
--			WHERE 
--				FLAGINVIO='S'
--				--AND DEFINITIVO=1 -- DA SCOMMENTARE IN PRODUZIONE
--		)
--	GROUP BY
--		VC.ChiaveClientePB,
--		A.COD_FISCALE,
--		A.RETE,
--		LEFT(A.ID_AREA,3)
--	) H
--	LEFT OUTER JOIN C6MartPeriodico.RISCHIO_AGGREGATO AS RA
--		ON H.RETE = RA.RETE
--		AND H.COD_FISCALE = RA.COD_FISCALE
--	INNER JOIN C6MartPeriodico.MIFID AS M
--		ON H.RETE = M.RETE
--		AND H.COD_FISCALE = M.COD_FISCALE
--    INNER JOIN C6MART.CONTRATTOSEI C6
--    ON H.COD_FISCALE=C6.COD_FISCALE 
--    AND H.RETE=C6.RETE
---- MODIFICARE ATRINGALI     
--   AND ISNULL(C6.DATA_FINEAVANZATO, '99991231') > getdate() --'20091231'
--	INNER JOIN C6MartPeriodico.CODIFICA_RISCHIO AS CR
--		ON M.PROFILO_ASS = CR.PROFILO
--	WHERE COD_AGGREG = 'COMPLESSIVO|BF'
--	--and H.cod_fiscale='PCTGNN45A30G276G'
---------------------------------------------------------------------------------------
END