CREATE FUNCTION [dbo].[Replace_Testo_Email_Periodico_LC_REP_GEN]
(
	@TESTO as varchar(max) = ''
)
RETURNS varchar(max)
AS
BEGIN
	DECLARE @TESTO_DI_RITORNO1 as varchar(max)
	SET @TESTO_DI_RITORNO1 = ''
    --CLIENTI CON REPORT DI DIAGNOSI
    DECLARE @TABLE_DIAGNOSI AS TABLE(rete VARCHAR(1),codicefiscale VARCHAR(16))
 	INSERT INTO @TABLE_DIAGNOSI(rete,CODICEFISCALE)
    SELECT RETE,CODICEFISCALE
    FROM C6MartPeriodico.[GESTIONE_PDF_FTP] REPGEN
    WHERE ISNULL(REPGEN.FLAGINVIO,'N') = 'N'
    AND REPGEN.TIPOREPORT = 'D'
    --CLIENTI CON REPORT DI DIAGNOSI E MONITORAGGIO
    DECLARE @TABLE_MONITORAGGIO_DIAGNOSI AS TABLE(rete VARCHAR(1),codicefiscale VARCHAR(16))
    INSERT INTO @TABLE_MONITORAGGIO_DIAGNOSI(rete,CODICEFISCALE)
    SELECT REPGEN.RETE,REPGEN.CODICEFISCALE
    FROM C6MartPeriodico.[GESTIONE_PDF_FTP] REPGEN
    INNER JOIN @TABLE_DIAGNOSI TABLE_DIAGNOSI
    ON TABLE_DIAGNOSI.rete = REPGEN.rete
    AND TABLE_DIAGNOSI.codicefiscale = REPGEN.codicefiscale
    WHERE ISNULL(REPGEN.FLAGINVIO,'N') = 'N'
    AND REPGEN.TIPOREPORT = 'M'
    --CONCATENO LA STRINGA DI TESTO PER LE EMAIL DI REPORT MONITORAGGIO E DIAGNOSI
    DECLARE @PB AS VARCHAR(6)
    SET @PB = ''
    DECLARE @RETE AS VARCHAR(1)
    SET @RETE = ''
	SELECT @TESTO_DI_RITORNO1 = @TESTO_DI_RITORNO1 + 
		  (CASE  WHEN @PB <> C6.COD_AGENTE OR @RETE <> C6.RETE THEN '<tr><td>PB: ' + C6.COD_AGENTE + ' (RETE ' + C6.RETE + ')</td></tr>' ELSE '' END)
		  + '<tr><td>Cliente: ' + ISNULL(ANAG_CLIENTI.NOME,'') + ' ' + ISNULL(ANAG_CLIENTI.COGNOME,'') + ' (' + CASE WHEN RISCHIO_AGGREGATO.VAR_PERC_PTF <= CODIFICA_RISCHIO.MAX_VAR THEN 'profilo adeguato' else 'profilo non adeguato' end + ')' + '</td></tr>'
		  , @PB = C6.COD_AGENTE
		  , @RETE = C6.RETE
	FROM @TABLE_MONITORAGGIO_DIAGNOSI REP_GEN
	INNER JOIN C6MartPeriodico.MIFID MIFID
	ON 	REP_GEN.RETE = MIFID.RETE
	AND REP_GEN.codicefiscale = MIFID.COD_FISCALE
	INNER JOIN C6MartPeriodico.CODIFICA_RISCHIO CODIFICA_RISCHIO
	ON MIFID.PROFILO_ASS = CODIFICA_RISCHIO.PROFILO
	INNER JOIN C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_AGGREGATO
	ON 	RISCHIO_AGGREGATO.RETE = REP_GEN.RETE
	AND RISCHIO_AGGREGATO.COD_FISCALE = REP_GEN.codicefiscale
	AND RISCHIO_AGGREGATO.COD_AGGREG = 'COMPLESSIVO|BF'
	INNER JOIN C6Mart.[CONTRATTOSEI] C6
	ON 	REP_GEN.RETE = C6.RETE
	AND REP_GEN.codicefiscale = C6.cod_fiscale
	INNER JOIN [C6MartPeriodico].[ANAG_CLIENTI] ANAG_CLIENTI
	ON 	REP_GEN.RETE = ANAG_CLIENTI.RETE
	AND REP_GEN.codicefiscale = ANAG_CLIENTI.cod_fiscale
	ORDER BY C6.RETE , C6.COD_AGENTE, ANAG_CLIENTI.cod_fiscale
-- mifid -- profilo
-- codifica_rischio varmax
-- riscio_aggregato con COMPLESSIVO|BF var
--var<=varmax adeguato else non adeguato
	IF @TESTO_DI_RITORNO1 <> ''
		SET @TESTO_DI_RITORNO1 = '<TABLE><TR><TD>Report: <B>Report Monitoraggio e Diagnosi trimestrale</B></TD></TR>' + @TESTO_DI_RITORNO1 + '</TABLE></BR>'
    --CLIENTI CON REPORT DI SOLO MONITORAGGIO
    DECLARE @TESTO_DI_RITORNO2 as varchar(max)
	SET @TESTO_DI_RITORNO2 = ''
    SET @PB = ''
    SET @RETE = ''
    SELECT @TESTO_DI_RITORNO2 = @TESTO_DI_RITORNO2 + 
		  (CASE  WHEN @PB <> C6.COD_AGENTE OR @RETE <> C6.RETE THEN '<tr><td>PB: ' + C6.COD_AGENTE + ' (RETE ' + C6.RETE + ')</td></tr>' ELSE '' END)
		  + '<tr><td>Cliente: ' + ISNULL(ANAG_CLIENTI.NOME,'') + ' ' + ISNULL(ANAG_CLIENTI.COGNOME,'') + '</td></tr>'
		  , @PB = C6.COD_AGENTE
		  , @RETE = C6.RETE
	FROM (
			SELECT REPGEN.RETE,REPGEN.CODICEFISCALE
			FROM C6MartPeriodico.[GESTIONE_PDF_FTP] REPGEN
			LEFT OUTER JOIN @TABLE_DIAGNOSI TABLE_DIAGNOSI
			ON TABLE_DIAGNOSI.rete = REPGEN.rete
			AND TABLE_DIAGNOSI.codicefiscale = REPGEN.codicefiscale
			WHERE ISNULL(REPGEN.FLAGINVIO,'N') = 'N'
			AND REPGEN.TIPOREPORT = 'M'
			AND TABLE_DIAGNOSI.rete IS NULL
			) REP_GEN
	INNER JOIN C6Mart.[CONTRATTOSEI] C6
	ON 	REP_GEN.RETE = C6.RETE
	AND REP_GEN.codicefiscale = C6.cod_fiscale
	INNER JOIN [C6MartPeriodico].[ANAG_CLIENTI] ANAG_CLIENTI
	ON 	REP_GEN.RETE = ANAG_CLIENTI.RETE
	AND REP_GEN.codicefiscale = ANAG_CLIENTI.cod_fiscale
	ORDER BY C6.RETE , C6.COD_AGENTE, ANAG_CLIENTI.cod_fiscale	  
	IF @TESTO_DI_RITORNO2 <> ''
		SET @TESTO_DI_RITORNO2 = '<TABLE><TR><TD>Report: <B>Report Monitoraggio trimestrale</B></TD></TR>' + @TESTO_DI_RITORNO2 + '</TABLE></BR>'
	DECLARE @TESTO_DI_RITORNO as varchar(max)
	SET @TESTO_DI_RITORNO = 'EMAIL ERRATA CONTATTARE HELP DESK'
	IF @TESTO_DI_RITORNO1 <> '' OR @TESTO_DI_RITORNO2 <> ''
	BEGIN
		SET @TESTO_DI_RITORNO = isnull(@TESTO,'/$TESTO NON VALORIZZATO$/');
		SET @TESTO_DI_RITORNO = REPLACE(@TESTO_DI_RITORNO,'/$ACAPO$/',char(10));
		SET @TESTO_DI_RITORNO = REPLACE(@TESTO_DI_RITORNO,'/$num_gg_gen_rep_periodico$/','15');
		SET @TESTO_DI_RITORNO = REPLACE(@TESTO_DI_RITORNO,'/$Mon_Diagn_Per$/',@TESTO_DI_RITORNO1 + ' ' + @TESTO_DI_RITORNO2); 
	END 
    RETURN @TESTO_DI_RITORNO
END