-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE procedure [C6MartPeriodico].[GESTIONE_SELECT_S80_PARZIALE_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 [dbo].[REP_S80_EVOL_STAMPA_C6_PARZIALE]
SELECT 
	A.ChiaveClientePB,
	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
		 WHEN AREA = 'Self' THEN 8
		 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 ('Self') THEN CONTROVALORE ELSE 0 END AS LineaSelf
FROM
	(SELECT
		VC.ChiaveClientePB,
		VC.COD_AGENTE,
		PBF.COD_FISCALE,
		PBF.RETE,
		CASE WHEN PBF.ID_AREA = 'Self' THEN PBF.ID_AREA 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
		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,
		CASE WHEN PBF.ID_AREA = 'Self' THEN PBF.ID_AREA ELSE LEFT(PBF.ID_AREA,3) END
	) 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
	WHERE COD_AGGREG = 'COMPLESSIVO|BF'
END