-- =============================================
-- Author:		<Nobili,,Andrea>
-- Create date: <Create Date,,>
-- Description:	<Email Log Giornaliero>
-- =============================================
CREATE procedure [C6Mart].[SRV_CHECK_C6_GIORNALIERO_AND]
AS
BEGIN
	SET NOCOUNT ON;
SELECT DISTINCT * INTO #TEST_QUALITY FROM (
SELECT *,12 AS ISLOG FROM C6MART.TEST_QUALITY
UNION
SELECT DISTINCT VC.RETE,VC.COD_FISCALE,'Cliente con VaR discordante '+ convert(varchar,RA.VAR_PERC_PTF) + ' ' + convert(varchar,VC.VAR_COMPLESSIVO_BF) AS MOTIVO,0
FROM
	C6Mart.RISCHIO_AGGREGATO AS RA
	INNER JOIN C6staging.VAR_ADEGUATEZZA AS VC
		ON RA.RETE = VC.RETE
		AND RA.COD_FISCALE = VC.COD_FISCALE
WHERE
	RA.COD_AGGREG = 'COMPLESSIVO|BF'
	AND ABS(RA.VAR_PERC_PTF - VC.VAR_COMPLESSIVO_BF) > 0.02
	AND (VC.FiduMulitMand = 'N')
UNION
SELECT DISTINCT RETE,COD_FISCALE,'Indirizzo più lunghi di 36 caratteri' AS MOTIVO,6	 
FROM C6Mart.vcontrattisei 
WHERE	data_fineavanzato > GETDATE() 
		AND isnull(numrevoche,0) = 0
		AND inviobenvenuto = 'N'
		AND LEN(Indirizzo) > 36 		
UNION
 SELECT DISTINCT RETE,CODICEFISCALE			    
	,'Prodotto non a catalogo ' + isnull(codiceContratto,'') +', '+  ltrim(rtrim(isnull(codiceinterno,''))) + '/'+ ltrim(rtrim(isnull(codicesottoprodotto,''))) + '/'+ ltrim(rtrim(isnull(codicemaf,''))) + '/'+ ltrim(rtrim(isnull(isin,'')))  As motivo  ,7 
FROM C6Staging.SPB_CONTR_SINTESI WHERE POSITIONID IS NULL
UNION
SELECT DISTINCT CM.Rete,CM.Cod_Fiscale,'Cliente senza righe Contratto Sintesi' AS MOTIVO,8 
FROM  C6Mart.RP_VContrattiMigrati CM
LEFT JOIN C6Staging.SPB_CONTR_SINTESI CS
ON CM.Cod_Fiscale = CS.CODICEFISCALE AND CM.RETE = CS.RETE
WHERE CS.CODICEFISCALE IS NULL
UNION
SELECT DISTINCT PB.RETE,PB.COD_FISCALE,'Cliente senza data sottoscrizione ' + dbo.toShortDateString(PB.ID_CONTRATTO)  AS MOTIVO,9		 
	FROM
		C6Mart.vcontratti AS C
		INNER JOIN C6Mart.PATRIMONIO_BF AS PB
			ON C.Rete = PB.RETE
			AND C.Cod_Fiscale = PB.COD_FISCALE
		INNER JOIN C6Mart.RISCHIO_AGGREGATO AS RA
			ON PB.RETE = RA.RETE
			AND PB.COD_FISCALE = RA.COD_FISCALE
	WHERE
		PB.DATA_SOTTOSCRIZIONE IS NULL
		AND PB.TIPO_PRODOTTO = 'DT'
UNION
SELECT DISTINCT VC.Rete,VC.Cod_Fiscale,'Cliente senza piramide modello' AS MOTIVO,10
FROM C6Mart.vcontrattiSEI VC
	 LEFT JOIN C6Mart.PIRAMIDE_MOD PM
	 ON VC.Cod_Fiscale=PM.Cod_Fiscale AND VC.RETE=PM.RETE
WHERE	 
	VC.Data_FineAvanzato > getDate()
	AND VC.inviobenvenuto = 'N'
	AND VC.clientemigrato = 'S'
	AND VC.risorseNA = 'N'
	AND VC.profiloAttivo = 1
	AND DATEDIFF(d, VC.Data_Perf, GETDATE()) >= CAST(dbo.getParametro('NUMGIORNIDALPERF_REP') AS INT)
	AND ISNULL(VC.NUMREVOCHE,0) = 0
	AND PM.Cod_Fiscale IS NULL
UNION
SELECT DISTINCT CM.RETE,CM.Cod_Fiscale,'Cliente senza righe profilo MIFID' AS MOTIVO,11
FROM  C6Mart.RP_VContrattiMigrati CM
LEFT JOIN  C6Staging.SPB_MIFID MD
ON CM.Cod_Fiscale = MD.CODICEFISCALE AND CM.RETE = MD.RETE
WHERE MD.CODICEFISCALE IS NULL
) T
INSERT INTO #TEST_QUALITY
SELECT DISTINCT VC.RETE,VC.COD_FISCALE,'Cliente con VaR discordante '+ convert(varchar,RA.VAR_PERC_PTF) + ' ' + convert(varchar,VC.VAR_COMPLESSIVO_BF) + CASE  WHEN VC.FiduMulitMand = 'S' THEN ' FiduMulitMand' ELSE ' ' END AS MOTIVO,5
FROM
	C6Mart.RISCHIO_AGGREGATO AS RA
	INNER JOIN C6staging.VAR_ADEGUATEZZA AS VC
		ON RA.RETE = VC.RETE
		AND RA.COD_FISCALE = VC.COD_FISCALE
WHERE
	RA.COD_AGGREG = 'COMPLESSIVO|BF'
	AND ABS(RA.VAR_PERC_PTF - VC.VAR_COMPLESSIVO_BF) <> 0.00	 
INSERT INTO #TEST_QUALITY
SELECT DISTINCT RETE,Cod_Fiscale,'Caratteri non validi in EMAIL' AS MOTIVO,12
FROM C6Mart.vcontratti WHERE  [C6Mart].[CARATTERINONVALIDI](email) = 'S' and data_fineavanzato > getdate()  
INSERT INTO #TEST_QUALITY
SELECT DISTINCT RETE,Cod_Fiscale,'Caratteri non validi in INDIRIZZO' AS MOTIVO,13
FROM C6Mart.vcontratti	WHERE [C6Mart].[CARATTERINONVALIDI](Indirizzo) = 'S' and data_fineavanzato > getdate()
INSERT INTO #TEST_QUALITY
SELECT DISTINCT RETE,Cod_Fiscale,'Caratteri non validi in CAP' AS MOTIVO,13
FROM C6Mart.vcontratti	WHERE [C6Mart].[CARATTERINONVALIDI](CAP) = 'S' and data_fineavanzato > getdate()
INSERT INTO #TEST_QUALITY
SELECT DISTINCT RETE,Cod_Fiscale,'Caratteri non validi in Citta' AS MOTIVO,13
FROM C6Mart.vcontratti	WHERE [C6Mart].[CARATTERINONVALIDI](Citta) = 'S' and data_fineavanzato > getdate()
INSERT INTO #TEST_QUALITY
SELECT DISTINCT RETE,Cod_Fiscale,'Caratteri non validi in Provincia' AS MOTIVO,13
FROM C6Mart.vcontratti	WHERE [C6Mart].[CARATTERINONVALIDI](Provincia) = 'S' and data_fineavanzato > getdate()
INSERT INTO #TEST_QUALITY
SELECT DISTINCT RETE,Cod_Fiscale,'Caratteri non validi in Nazione' AS MOTIVO,13
FROM C6Mart.vcontratti	WHERE [C6Mart].[CARATTERINONVALIDI](Nazione) = 'S' and data_fineavanzato > getdate()
INSERT INTO #TEST_QUALITY
SELECT DISTINCT RETE,Cod_Fiscale,'Caratteri non validi in Presso' AS MOTIVO,13
FROM C6Mart.vcontratti	WHERE [C6Mart].[CARATTERINONVALIDI](Presso) = 'S' and data_fineavanzato > getdate()
	SELECT
	    Tipo
		,CASE Tipo
			WHEN 'PERF' THEN 'Perfezionamento'
			WHEN '1_SOLL_NOASS' THEN '1&ordm sollecito risorse non associate'
			WHEN '2_SOLL_NOASS' THEN '2&ordm sollecito risorse non associate'
			WHEN '3_SOLL_NOASS' THEN '3&ordm sollecito risorse non associate'
			WHEN 'S_SOLL_NOASS' THEN 'pi&ugrave di 3 solleciti risorse non associate'
			WHEN 'REVOCA_NOASS' THEN 'revoca per risorse non associate'
			WHEN '1_SOLL_NOMIG' THEN '1&ordm sollecito cliente non migrato'
			WHEN '2_SOLL_NOMIG' THEN '2&ordm sollecito cliente non migrato'
			WHEN '3_SOLL_NOMIG' THEN '3&ordm sollecito cliente non migrato'
			WHEN 'S_SOLL_NOMIG' THEN 'pi&ugrave di 3 solleciti cliente non migrato'
			WHEN 'REVOCA_NOMIG' THEN 'revoca per cliente non migrato'
			WHEN '1_SOLL_NOPRO' THEN '1&ordm sollecito profilo non attivo'
			WHEN '2_SOLL_NOPRO' THEN '2&ordm sollecito profilo non attivo'
			WHEN '3_SOLL_NOPRO' THEN '3&ordm sollecito profilo non attivo'
			WHEN 'S_SOLL_NOPRO' THEN 'pi&ugrave di 3 solleciti profilo non attivo'
			WHEN 'REVOCA_NOPRO' THEN 'revoca per profilo non attivo'
		END AS DescTipo
		,CASE Tipo
			WHEN 'PERF' THEN 1
			WHEN '1_SOLL_NOMIG' THEN 2
			WHEN '2_SOLL_NOMIG' THEN 3
			WHEN '3_SOLL_NOMIG' THEN 4
			WHEN 'S_SOLL_NOMIG' THEN 5
			WHEN 'REVOCA_NOMIG' THEN 6
			WHEN '1_SOLL_NOASS' THEN 7
			WHEN '2_SOLL_NOASS' THEN 8
			WHEN '3_SOLL_NOASS' THEN 9
			WHEN 'S_SOLL_NOASS' THEN 10
			WHEN 'REVOCA_NOASS' THEN 11			
			WHEN '1_SOLL_NOPRO' THEN 12
			WHEN '2_SOLL_NOPRO' THEN 13
			WHEN '3_SOLL_NOPRO' THEN 14
			WHEN 'S_SOLL_NOPRO' THEN 15
			WHEN 'REVOCA_NOPRO' THEN 16
		END AS Ordine
		,COUNT(Tipo) AS Totale
	INTO #EmailInviate
	FROM C6Mart.GESTIONE_EMAIL
	WHERE  dbo.toShortDateString(Data_generazione) = dbo.toShortDateString(GETDATE())
	GROUP BY Tipo	
	SELECT   C.rete
			,C.Cod_fiscale
			,C.cod_Agente
			,case when C.Cod_Fiscale  like '%@%' then c.piva_fiduciaria + '  ' + substring(C.cod_fiscale,4,6) else C.Cod_Fiscale end as cliente
			,C.data_perf
			,Tipo
			,C.ClienteMIGRATO
			,C.RISORSENA
			,C.PROFILOATTIVO
			,case when dbo.toShortDateString(Data_generazione) = dbo.toShortDateString(GETDATE()) then 'S' else 'N' end as FLAGG
			,case when dbo.toShortDateString(Data_invio) = dbo.toShortDateString(GETDATE()) then 'S' else 'N' end as FLAGI
			,inviata
			,row_number() over (partition by  C.rete,C.Cod_fiscale ORDER BY CASE Tipo
																				WHEN 'PERF' THEN 1
																				WHEN '1_SOLL_NOMIG' THEN 2
																				WHEN '2_SOLL_NOMIG' THEN 3
																				WHEN '3_SOLL_NOMIG' THEN 4
																				WHEN 'S_SOLL_NOMIG' THEN 5
																				WHEN 'REVOCA_NOMIG' THEN 6
																				WHEN '1_SOLL_NOASS' THEN 7
																				WHEN '2_SOLL_NOASS' THEN 8
																				WHEN '3_SOLL_NOASS' THEN 9
																				WHEN 'S_SOLL_NOASS' THEN 10
																				WHEN 'REVOCA_NOASS' THEN 11			
																				WHEN '1_SOLL_NOPRO' THEN 12
																				WHEN '2_SOLL_NOPRO' THEN 13
																				WHEN '3_SOLL_NOPRO' THEN 14
																				WHEN 'S_SOLL_NOPRO' THEN 15
																				WHEN 'REVOCA_NOPRO' THEN 16
																				END)  as duplicato
	   INTO #EmailInviateDett
	 FROM C6Mart.GESTIONE_EMAIL E INNER JOIN C6MART.VCONTRATTISEI C
	 ON E.COD_FISCALE = C.COD_FISCALE AND E.RETE=C.RETE 
	 WHERE dbo.toShortDateString(Data_generazione) = dbo.toShortDateString(GETDATE())
	   OR  dbo.toShortDateString(Data_invio) = dbo.toShortDateString(GETDATE())
SELECT
		C.Rete,
		C.Cod_fiscale,
		C.Cod_Agente,
		case when C.Cod_Fiscale  like '%@%' then c.piva_fiduciaria + '  ' + substring(C.cod_fiscale,4,6) else C.Cod_Fiscale end as cliente,
		C.Data_Perf
	INTO #ReportGenerati
	FROM
		C6Mart.GESTIONE_PDF_FTP AS GPF
		INNER JOIN C6Mart.VCONTRATTI AS C             
			ON GPF.RETE = C.Rete
			AND GPF.CODICEFISCALE = C.Cod_Fiscale
        INNER JOIN C6Mart.ContrattoSEI_metadati AS M
            on  C.chiaveclientepb = M.chiaveclientepb
	WHERE
		dbo.toShortDateString(GPF.DataGenerazione) = dbo.toShortDateString(GETDATE())
		AND Data_FineAvanzato > getDate()
SElECT DISTINCT  
	 V.COD_FISCALE
	,V.RETE
	,V.COD_AGENTE
	,V.piva_fiduciaria 
	,V.DATA_PERF
	,V.ClienteMigrato
	,V.RisorseNA
	,V.profiloAttivo
	,V.Data_UltimaRevoca 
	,CASE  WHEN Q.COD_FISCALE IS NULL THEN 'N' ELSE 'S' END AS TestQuality
	,CASE  WHEN DATEDIFF(d, DATA_PERF, GETDATE()) >= CAST(dbo.getParametro('NUMGIORNIDALPERF_REP') AS INT)  THEN 'S' ELSE 'N' END AS FinePerf
	,InvioBenvenuto as Generato
INTO #TuttiReport		 
FROM C6MART.VCONTRATTISEI V
LEFT JOIN #TEST_QUALITY Q
	ON V.COD_FISCALE = Q.COD_FISCALE AND V.RETE = Q.RETE
WHERE 1=1
AND V.INVIOBENVENUTO = 'N'  
AND DATA_FINEAVANZATO > getDate()
SELECT *
, 'n.ro giorni trascorsi dal perfezionamento: ' + CONVERT(VARCHAR,DATEDIFF(d,DATA_PERF, GETDATE())) AS Motivo
,CASE when Cod_Fiscale  like '%@%' then  piva_fiduciaria + '  ' + substring( cod_fiscale,4,6) else  Cod_Fiscale end as CLIENTE	 
INTO #ReportNonGenerati 
FROM  #TuttiReport WHERE(FINEPERF = 'S') OR (FINEPERF = 'N' AND TestQuality = 'S')
SELECT 	 
	  Q.RETE	
	 ,Q.COD_FISCALE	 	
	 ,Q.MOTIVO 
	 ,CASE  WHEN Q.COD_FISCALE IS NULL THEN 'N' ELSE 'S' END AS TestQuality
	 ,CASE  WHEN DATEDIFF(d, DATA_PERF, GETDATE()) >= CAST(dbo.getParametro('NUMGIORNIDALPERF_REP') AS INT)  THEN 'S' ELSE 'N' END AS FinePerf
	 ,InvioBenvenuto as Generato
	 ,Q.ISLOG		 
INTO #DettReport
FROM #TEST_QUALITY  Q   
INNER JOIN C6MART.VCONTRATTISEI V
	ON V.COD_FISCALE = Q.COD_FISCALE AND V.RETE = Q.RETE
	WHERE ISLOG > 0
UNION
SELECT  rete,COD_FISCALE,'In attesa di gestione Revoca 'AS MOTIVO,TestQuality,FinePerf, Generato,1 as ISLOG from #ReportNonGenerati WHERE Data_UltimaRevoca IS NOT NULL	
UNION	
SELECT  rete,COD_FISCALE,'Cliente non migrato ' AS MOTIVO,TestQuality,FinePerf,Generato,2 as ISLOG from #ReportNonGenerati WHERE isnull(ClienteMigrato,'N') = 'N'
UNION
SELECT  rete,COD_FISCALE,'Risorse non associate ' AS MOTIVO,TestQuality,FinePerf,Generato,3 as ISLOG  from #ReportNonGenerati WHERE isnull(RisorseNA,'S') = 'S'
UNION
SELECT  rete,COD_FISCALE,'Profilo non attivo ' AS MOTIVO,TestQuality,FinePerf,Generato,4 as ISLOG  from #ReportNonGenerati WHERE isnull(profiloAttivo,0) = 0
--  SELECT * FROM  #EmailInviateDett
--  SELECT * FROM   #EmailInviate 
--	SELECT * FROM #TEST_QUALITY
--	SELECT * FROM #TuttiReport where cod_fiscale = 'VCCGPP46D06F205X'
--	SELECT * FROM #ReportGenerati where cod_fiscale = 'VCCGPP46D06F205X'
--	SELECT * FROM #ReportNonGenerati  where cod_fiscale = 'VCCGPP46D06F205X'  
--	SELECT * FROM #DettReport where cod_fiscale = 'VCCGPP46D06F205X'  
--	SELECT * FROM #EmailInviate
DECLARE @testoMail0 VARCHAR(MAX)
DECLARE @testoMail1 VARCHAR(MAX)
DECLARE @testoMail2 VARCHAR(MAX)
DECLARE @testoMail3 VARCHAR(MAX)
DECLARE @testoMail4 VARCHAR(MAX)
DECLARE @rete CHAR(1)
DECLARE @cod_fiscale CHAR(16)
DECLARE @codiceAgente VARCHAR(6)
DECLARE @cliente VARCHAR(25)
DECLARE @data DATETIME
DECLARE @Motivo VARCHAR(100)
DECLARE @testQuality CHAR(1)
DECLARE @FinePerf CHAR(1)	
DECLARE @totale INT
 -- [C6Mart].[SRV_CHECK_C6_GIORNALIERO_AND]
set @testoMail0 = '<HTML><HEAD></HEAD><TITLE></TITLE><BODY><table><tr><td width=300><h2><b>Rapporto giornaliero</b></h2></td></tr></table>'
set @testoMail1 = '<HTML><HEAD></HEAD><TITLE></TITLE><SCRIPT type="text/javascript">var _email="chiuso";var _rngn="chiuso";var _rngs="chiuso";var cmd="";var menu="";function ac(item){var menu=item;if(item == "22N" && _email == "chiuso"){cmd=_email;_email="aperto";}else if(item=="22N" && _email=="aperto"){cmd=_email;_email="tutto";}else if(item=="22N" && _email=="tutto"){cmd=_email;_email="chiuso";}else if(item=="00N" && _rngn=="chiuso"){cmd=_rngn;_rngn="aperto";}else if(item=="00N" && _rngn=="aperto"){cmd=_rngn;_rngn="tutto";}else if(item == "00N" && _rngn == "tutto"){cmd=_rngn;_rngn="chiuso";}else if(item == "00S" && _rngs == "chiuso"){cmd=_rngs;_rngs="aperto";}else if(item == "00S" && _rngs == "aperto"){cmd=_rngs;_rngs="tutto";}else if(item == "00S" && _rngs == "tutto"){cmd=_rngs;_rngs="chiuso";}else{var padre=document.getElementById(item);if(padre.style.display=="block"){cmd="chiuditutto";}else{cmd="apritutto";}}var divs=document.getElementsByTagName("table");for(i=0;i<divs.length;i++)'
				+ 'if(cmd=="apritutto" && item==divs[i].id.substr(0,item.length)) divs[i].style.display="block";else if(cmd=="chiuditutto" && item==divs[i].id.substr(0,item.length))divs[i].style.display="none";else{if(menu==divs[i].id.substr(0,3))divs[i].style.display="none";if(cmd=="chiuso" && menu == divs[i].id)divs[i].style.display="block";if(cmd=="aperto" && menu==divs[i].id.substr(0,3))divs[i].style.display="block";}}'
				+ 'function carica(){divs=document.getElementsByTagName("table");for(i=0;i<divs.length;i++){if(divs[i].id!="111"){divs[i].style.display="none";}if(divs[i].id.length==3 || divs[i].style.color!=""){divs[i].onmouseout=function(){document.body.style.cursor="default";};divs[i].onmouseover=function(){document.body.style.cursor="pointer";};}else{divs[i].style.color="#778899";}if(divs[i].id.substr(0,3)== "00G" && divs[i].style.color=="red"){document.getElementById("warning00G").style.display="block";}if(divs[i].id.substr(0,3)== "22N" && divs[i].style.color=="red"){document.getElementById("warning22N").style.display="block";}}}</SCRIPT><body onload="carica();">';
SET @testoMail2 = ''
SET @testoMail3 = ''
SET @testoMail4 = '' 
SET @totale = 0  
--*********************************************************************************
--*********************************************************************************
--*********************************************************************************
    SET @totale = 0
	SELECT @totale = isnull(sum(totale),0) FROM #EmailInviate
	SET @testoMail1 = @testoMail1 + '<br><br><table id="111"><tr onclick="ac(''22N'')"><td><b>E-mail inviate</b></td><td><div id= "warning22N" STYLE="vertical-align:middle;font-size:9px;color:red;display:none">warning</div></td></tr>'
	DECLARE @tipoEmail VARCHAR(100)
	DECLARE @DescTipo VARCHAR(100)
	DECLARE @totaleEmail VARCHAR(100)	
	DECLARE @ClienteMIGRATO VARCHAR(1)
	DECLARE @RISORSENA  VARCHAR(1)
	DECLARE @FLAGG  VARCHAR(1)
	DECLARE @FLAGI  VARCHAR(1)	 
	DECLARE @PROFILOATTIVO INT
	DECLARE @DUPLICATO INT
	DECLARE reader CURSOR FOR SELECT DescTipo,convert(varchar,totale),tipo FROM #EmailInviate ORDER BY Ordine
	OPEN reader
	FETCH NEXT FROM reader INTO   @DescTipo, @totaleEmail,@tipoEmail
	WHILE @@FETCH_STATUS = 0
	BEGIN 
	    SET @testoMail1 = @testoMail1 +'<tr><table id="22N"><tr onclick="ac(''22N'+@tipoEmail+''')"><td width=300>'+@DescTipo+'</td><td width=100>'+@totaleEmail+'</td></tr></table></tr><!--[TY'+@tipoEmail+']-->'		 
		FETCH NEXT FROM reader INTO  @DescTipo, @totaleEmail,@tipoEmail
	END	
	SET @testoMail1 = @testoMail1 + '<tr><td><b>Totale: ' + CAST(@totale AS VARCHAR) + '</b></td></tr></table>'
	CLOSE reader
	DEALLOCATE reader
	DECLARE reader CURSOR FOR SELECT  rete,Cod_fiscale,  cod_Agente, cliente, data_perf,tipo,ClienteMIGRATO,RISORSENA ,PROFILOATTIVO,DUPLICATO,FLAGG,FLAGI FROM #EmailInviateDett  order by  DUPLICATO asc
	OPEN reader
	FETCH NEXT FROM reader INTO @rete,  @Cod_Fiscale,  @codiceAgente , @cliente, @data,@tipoEmail,@ClienteMIGRATO,@RISORSENA ,@PROFILOATTIVO,@DUPLICATO,@FLAGG,@FLAGI
	WHILE @@FETCH_STATUS = 0
	BEGIN
	    SET @testoMail1 =  REPLACE(@testoMail1,'<!--[TY'+@tipoEmail+']-->','<!--[TY'+@tipoEmail+']-->'+'<tr><table style="color:black;" ID="22N'+@tipoEmail+'" ><tr onclick="ac(''22N'+@tipoEmail+@cod_fiscale+@rete +''')"><td width=80></td><td width=80>'+@rete+'</td><td width=100>'+@codiceAgente+'</td><td width=200>'+@cliente+'</td><td width=100>'+dbo.toShortDateString(@data)+'</td></tr></table></tr><!--[TQ'+@tipoEmail+@cod_fiscale+@rete+']-->');
	    IF  @PROFILOATTIVO  =  1 AND @RISORSENA = 'N' AND @ClienteMIGRATO = 'S' AND @tipoEmail <> 'PERF'  AND @tipoEmail<>'REVOCA_NOMIG' AND @tipoEmail<>'REVOCA_NOASS'  AND @tipoEmail<>'REVOCA_NOPRO' 
			SET @testoMail1 =  REPLACE(@testoMail1,'<!--[TQ'+@tipoEmail+@Cod_Fiscale+@rete+']-->','<!--[TQ'+@tipoEmail+@Cod_Fiscale+@rete+']-->'+'<tr><table ID="22N'+@tipoEmail+@cod_fiscale+@rete+'" style="color:red;"><tr><td width=150></td><td>Tipo Email Errata</td></table></tr>');
	    IF @DUPLICATO <> 1
	    	SET @testoMail1 =  REPLACE(@testoMail1,'<!--[TQ'+@tipoEmail+@Cod_Fiscale+@rete+']-->','<!--[TQ'+@tipoEmail+@Cod_Fiscale+@rete+']-->'+'<tr><table ID="22N'+@tipoEmail+@cod_fiscale+@rete+'" style="color:red;"><tr><td width=150></td><td>' + CONVERT(VARCHAR,@DUPLICATO) + '&ordm  email inviata</td></table></tr>');
	    IF  @FLAGG = 'S' AND @FLAGI = 'N'
	    		SET @testoMail1 =  REPLACE(@testoMail1,'<!--[TQ'+@tipoEmail+@Cod_Fiscale+@rete+']-->','<!--[TQ'+@tipoEmail+@Cod_Fiscale+@rete+']-->'+'<tr><table ID="22N'+@tipoEmail+@cod_fiscale+@rete+'" style="color:red;"><tr><td width=150></td><td>Email non inviata</td></table></tr>');
	    IF  @FLAGG = 'N' AND @FLAGI = 'S' AND @tipoEmail<>'REVOCA_NOMIG' AND @tipoEmail<>'REVOCA_NOASS'  AND @tipoEmail<>'REVOCA_NOPRO'
				SET @testoMail1 =  REPLACE(@testoMail1,'<!--[TQ'+@tipoEmail+@Cod_Fiscale+@rete+']-->','<!--[TQ'+@tipoEmail+@Cod_Fiscale+@rete+']-->'+'<tr><table ID="22N'+@tipoEmail+@cod_fiscale+@rete+'" style="color:red;"><tr><td width=150></td><td>Invio Email Errata</td></table></tr>');
		IF @PROFILOATTIVO <> 1  
			SET @testoMail1 =  REPLACE(@testoMail1,'<!--[TQ'+@tipoEmail+@Cod_Fiscale+@rete+']-->','<!--[TQ'+@tipoEmail+@Cod_Fiscale+@rete+']-->'+'<tr><table ID="22N'+@tipoEmail+@cod_fiscale+@rete+'"><tr><td width=150></td><td>Profilo non attivo </td></table></tr>');
		IF @RISORSENA <> 'N'  
			SET @testoMail1 =  REPLACE(@testoMail1,'<!--[TQ'+@tipoEmail+@Cod_Fiscale+@rete+']-->','<!--[TQ'+@tipoEmail+@Cod_Fiscale+@rete+']-->'+'<tr><table ID="22N'+@tipoEmail+@cod_fiscale+@rete+'"><tr><td width=150></td><td>Risorse non associate </td></table></tr>');
		IF @ClienteMIGRATO <> 'S'  
			SET @testoMail1 =  REPLACE(@testoMail1,'<!--[TQ'+@tipoEmail+@Cod_Fiscale+@rete+']-->','<!--[TQ'+@tipoEmail+@Cod_Fiscale+@rete+']-->'+'<tr><table ID="22N'+@tipoEmail+@cod_fiscale+@rete+'"><tr><td width=150></td><td>Cliente non migrato </td></table></tr>');
		FETCH NEXT FROM reader INTO @rete,  @Cod_Fiscale,  @codiceAgente , @cliente, @data,@tipoEmail,@ClienteMIGRATO,@RISORSENA ,@PROFILOATTIVO,@DUPLICATO,@FLAGG,@FLAGI
	END	
	CLOSE reader
	DEALLOCATE reader
--*********************************************************************************
--*********************************************************************************
--*********************************************************************************
	SET @totale = 0
	SELECT @totale = COUNT(*)  FROM #ReportGenerati
	SET @testoMail2 = @testoMail2 + '<br><br><table id="111"><tr onclick="ac(''00G'')"><td><b>Report generati</b></td><td><div id= "warning00G" STYLE="vertical-align:middle;font-size:9px;color:red;display:none">warning</div></td></tr>'
	DECLARE reader CURSOR FOR SELECT  rete,Cod_fiscale,  cod_Agente, cliente, data_perf FROM #ReportGenerati ORDER BY DATA_PERF 	 		 
	OPEN reader
	FETCH NEXT FROM reader INTO  @rete,@Cod_fiscale,  @codiceAgente, @cliente, @data
	WHILE @@FETCH_STATUS = 0
	BEGIN 
		SET @testoMail2 = @testoMail2 +'<tr><table id="00G"><tr><td width=80>'+@rete+'</td><td width=100>'+@codiceAgente+'</td><td width=200>'+@cliente+'</td><td width=100>'+dbo.toShortDateString(@data)+'</td><td width=300></td></tr></table></tr><!--[TQ'+@cod_fiscale+@rete+']-->'		 
		FETCH NEXT FROM reader INTO  @rete,@Cod_fiscale,  @codiceAgente, @cliente, @data
	END	
	SET @testoMail2 = @testoMail2 + '<tr><td><b>Totale: ' + CAST(@totale AS VARCHAR) + '</b></td></tr></table>'
	SET @testoMail0 = @testoMail0 + '<table><tr><td width=300>Report generati</td><td>'+CAST(@totale AS VARCHAR)+'</td></tr></table>'   
	CLOSE reader            
	DEALLOCATE reader
--*********************************************************************************
--*********************************************************************************
--*********************************************************************************
	SET @totale = 0
	SELECT @totale = COUNT(*) FROM #ReportNonGenerati WHERE FINEPERF = 'S'
	SET @testoMail3 = @testoMail3 + '<br><br><table id="111"><tr onclick="ac(''00S'')"><td><b>Report non generati</b></td></tr>'
	DECLARE reader CURSOR FOR SELECT   rete, cod_fiscale, cod_Agente, cliente, data_perf, Motivo,testQuality  FROM #ReportNonGenerati WHERE FINEPERF = 'S' ORDER BY DATA_PERF 	 		 
	OPEN reader
	FETCH NEXT FROM reader INTO @rete, @cod_fiscale, @codiceAgente, @cliente, @data, @Motivo,@testQuality
	WHILE @@FETCH_STATUS = 0
	BEGIN 
		SET @testoMail3 = @testoMail3 +'<tr><table id="00S" onclick="ac(''00S'+ @cod_fiscale+@rete +''')"><tr><td width=80>'+@rete+'</td><td width=100>'+@codiceAgente+'</td><td width=200>'+@cliente+'</td><td width=100>'+dbo.toShortDateString(@data)+'</td><td width=300>'+@Motivo+'</td></tr></table></tr><!--[TQ'+@cod_fiscale+@rete+']-->'		 
		FETCH NEXT FROM reader INTO @rete, @cod_fiscale, @codiceAgente, @cliente, @data, @Motivo,@testQuality
	END	
	SET @testoMail3 = @testoMail3 + '<tr><td><b>Totale: ' + CAST(@totale AS VARCHAR) + '</b></td></tr></table>'
	SET @testoMail0 = @testoMail0 +  '<table><tr><td width=300>Report non generati</td><td>'+CAST(@totale AS VARCHAR)+'</td></tr></table>'
	CLOSE reader   
	DEALLOCATE reader   
----*********************************************************************************
----*********************************************************************************
----*********************************************************************************
	SET @totale = 0
	DECLARE @totale2 INT 
	SELECT @totale = COUNT(*) FROM #ReportNonGenerati WHERE FINEPERF = 'N' AND TestQuality = 'S'
	SELECT @totale2 = COUNT(*) FROM #TuttiReport WHERE FINEPERF = 'N' 
	SET @testoMail4 = @testoMail4 + '<br><br><table id="111"><tr onclick="ac(''00N'')"><td><b>Report errati in Perfezionamento</b></td></tr>'
	DECLARE reader CURSOR FOR SELECT  rete, cod_fiscale, cod_Agente, cliente, data_perf, Motivo,testQuality FROM #ReportNonGenerati WHERE FINEPERF = 'N' AND TestQuality = 'S' ORDER BY DATA_PERF 	 		 
	OPEN reader
	FETCH NEXT FROM reader INTO @rete, @cod_fiscale, @codiceAgente, @cliente, @data, @Motivo,@testQuality
	WHILE @@FETCH_STATUS = 0
	BEGIN 
		SET @testoMail4 = @testoMail4 +'<tr><table id="00N" onclick="ac(''00N'+ @cod_fiscale+@rete +''')"><tr><td width=80>'+@rete+'</td><td width=100>'+@codiceAgente+'</td><td width=200>'+@cliente+'</td><td width=100>'+dbo.toShortDateString(@data)+'</td><td width=300>'+@Motivo+'</td></tr></table></tr><!--[TQ'+@cod_fiscale+@rete+']-->'		 
		FETCH NEXT FROM reader INTO @rete, @cod_fiscale, @codiceAgente, @cliente, @data, @Motivo,@testQuality
	END	
	SET @testoMail4 = @testoMail4 + '<tr><td><b>Totale: ' + CAST(@totale AS VARCHAR) + ' su ' + CAST(@totale2 AS VARCHAR) + '</b></td></tr></table>'
	SET @testoMail0 = @testoMail0 + '<table><tr><td width=300>Report errati in Perfezionamento</td><td>'+ CAST(@totale AS VARCHAR) + ' su ' + CAST(@totale2 AS VARCHAR) +'</td></tr></table>'
	CLOSE reader          
	DEALLOCATE reader
----*********************************************************************************
----*********************************************************************************
----*********************************************************************************
	DECLARE reader CURSOR FOR SELECT Rete,Cod_Fiscale,Motivo,TestQuality,CASE WHEN generato ='S' THEN 'G' ELSE FinePerf END FinePerf FROM #DettReport order by ISLOG desc 
	OPEN reader
	FETCH NEXT FROM reader INTO @rete,  @Cod_Fiscale,  @Motivo , @testQuality, @FinePerf
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @testoMail2 =  REPLACE(@testoMail2,'<!--[TQ'+@Cod_Fiscale+@rete+']-->','<!--[TQ'+@Cod_Fiscale+@rete+']-->'+'<tr><table ID="00'+@FinePerf+@cod_fiscale+@rete+'" style="color:red;"><tr><td width=100></td><td>'+@Motivo+'</td></table></tr>');
		SET @testoMail3 =  REPLACE(@testoMail3,'<!--[TQ'+@Cod_Fiscale+@rete+']-->','<!--[TQ'+@Cod_Fiscale+@rete+']-->'+'<tr><table ID="00'+@FinePerf+@cod_fiscale+@rete+'"><tr><td width=100></td><td>'+@Motivo+'</td></table></tr>');
		SET @testoMail4 =  REPLACE(@testoMail4,'<!--[TQ'+@Cod_Fiscale+@rete+']-->','<!--[TQ'+@Cod_Fiscale+@rete+']-->'+'<tr><table ID="00'+@FinePerf+@cod_fiscale+@rete+'"><tr><td width=100></td><td>'+@Motivo+'</td></table></tr>');
		FETCH NEXT FROM reader INTO @rete, @Cod_Fiscale, @Motivo, @testQuality, @FinePerf
	END	
	CLOSE reader
	DEALLOCATE reader	
----*********************************************************************************
----*********************************************************************************
----*********************************************************************************
SET @testoMail0 = @testoMail0 + '<br><br>In allegato dettaglio del Giornaliero</BODY></HTML>'
DROP TABLE #TEST_QUALITY
DROP TABLE #EmailInviate
DROP TABLE #EmailInviateDett
DROP TABLE #TuttiReport
DROP TABLE #ReportNonGenerati
DROP TABLE #DettReport
DROP TABLE #ReportGenerati
SELECT @testoMail0,@testoMail1,@testoMail2,@testoMail3,@testoMail4,'</body></html>'
END