-- =============================================  
-- Author:  Paolo Giovanetti  / TRINGALI PER FIDUCIARIE  
-- Create date: <Create Date,,>  
-- Description: <Description,,>  
-- =============================================  
CREATE   procedure [C6Mart].[RP_DM_W6CLIESG]  
AS  
BEGIN  
 SET NOCOUNT ON;  
 -- Log esecuzione  
 INSERT INTO LOG_ESECUZIONE  
  (Nome, Inizio, Nota)  
 VALUES (  
  'RP_DM_W6CLIESG',  
  GETDATE(),  
  'Elaborazione Lettere di Benvenuto'  
 )  
 TRUNCATE TABLE C6Mart.SPB_W6CLIESG
 TRUNCATE TABLE C6Mart.W6CLIESG
 INSERT INTO  C6Mart.SPB_W6CLIESG
       ( 
	   [Rete]
      ,[Codice_Agente]
      ,[Codice_Fiscale]
      ,[Piva_Fiduciaria]
      ,[Codman]
      ,[BeneficioDiversificazione]
      ,[ValoreVaR]
      ,[Riserva]
      ,[PesoSostESG]
      ,[CSP]
      ,[ConcLungoPeriodo]
      ,[Liquidita]
      ,[NoSostESG]
      ,[SostPercE]
      ,[SostPercS]
      ,[SostPercG]
      ,[SostESGInLinea]
      ,[SostPercEInLinea]
      ,[SostPercSInLinea]
      ,[SostPercGInLinea]
      ,[NoSostSFDR]
      ,[SostSFDR]
      ,[SostSFDRInLinea]
      ,[NoSostTaxonomy]
      ,[SostTaxonomy]
      ,[SostTaxonomyInLinea]
      ,[NoSostPaiE]
      ,[SostPaiE]
      ,[SostPaiEInLinea]
      ,[NoSostPaiS]
      ,[SostPaiS]
      ,[SostPaiSInLinea]
      ,[Sost_E]
      ,[Sost_S]
      ,[Sost_G]
      ,[percESG]
      ,[UlterioriPrefSost]
      ,[PercSFDR]
      ,[PercTaxonomy]
      ,[PAI_E]
      ,[PAI_S]
      ,[ListPAI_E]
      ,[ListPAI_S])  
 SELECT distinct 
	   [Rete]
      ,[Codice_Agente]
      ,case when ISNULL(CODMAN,'')<>'' then 'FF@'+CODMAN
	   else Codice_Fiscale end as Codice_Fiscale
      ,[Piva_Fiduciaria]
      ,[Codman]
      ,[BeneficioDiversificazione]
      ,[ValoreVaR]
      ,[Riserva]
      ,[PesoSostESG]
      ,[CSP]
      ,[ConcLungoPeriodo]
      ,[Liquidita]
      ,[NoSostESG]
      ,[SostPercE]
      ,[SostPercS]
      ,[SostPercG]
      ,[SostESGInLinea]
      ,[SostPercEInLinea]
      ,[SostPercSInLinea]
      ,[SostPercGInLinea]
      ,[NoSostSFDR]
      ,[SostSFDR]
      ,[SostSFDRInLinea]
      ,[NoSostTaxonomy]
      ,[SostTaxonomy]
      ,[SostTaxonomyInLinea]
      ,[NoSostPaiE]
      ,[SostPaiE]
      ,[SostPaiEInLinea]
      ,[NoSostPaiS]
      ,[SostPaiS]
      ,[SostPaiSInLinea]
      ,[Sost_E]
      ,[Sost_S]
      ,[Sost_G]
      ,[percESG]
      ,[UlterioriPrefSost]
      ,[PercSFDR]
      ,[PercTaxonomy]
      ,isnull([PAI_E],'')
      ,isnull([PAI_S],'')
      ,[ListPAI_E]
      ,[ListPAI_S]
FROM [C6StampeCentralizzate].[C6Staging].[SPB_W6CliESG] 
drop table if exists #st_split_E
drop table if exists #st_split_S
drop table if exists #st_split_ES 
-- Step 1: Creazione delle tabelle temporanee
SELECT 
    value AS ListPAI_E,
    [Rete],
    [Codice_Agente],
    CASE 
        WHEN CODMAN <> '' THEN 'FF@' + CODMAN
        ELSE Codice_Fiscale 
    END AS COD_FISCALE
INTO #st_split_E
FROM  C6Mart.SPB_W6CLIESG
CROSS APPLY STRING_SPLIT(ListPAI_E, '|')
SELECT 
    value AS ListPAI_S,
    [Rete],
    [Codice_Agente],
    CASE 
        WHEN CODMAN <> '' THEN 'FF@' + CODMAN
        ELSE Codice_Fiscale 
    END AS COD_FISCALE
INTO #st_split_S
FROM C6Mart.SPB_W6CLIESG
CROSS APPLY STRING_SPLIT(ListPAI_S, '|')
--select * from  C6Mart.SPB_W6CLIESG
--select * from #st_split_E
--select * from #st_split_S
-- Step 2: Aggregare le descrizioni in un'unica query
SELECT 
    [Rete], 
    [COD_FISCALE],
    STRING_AGG(CASE 
        WHEN ListPAI_E = 'PAI_A01' THEN 'Emissioni gas serra'
        WHEN ListPAI_E = 'PAI_A02' THEN 'Biodiversità'
        WHEN ListPAI_E = 'PAI_A03' THEN 'Acqua'
        WHEN ListPAI_E = 'PAI_A04' THEN 'Rifiuti'
        WHEN ListPAI_E = 'PAI_A05' THEN 'Governativi di tipo Ambientale'
        WHEN ListPAI_E = 'PAI_A07' THEN 'Efficienza energetica'
        WHEN ListPAI_E = 'PAI_A08' THEN 'Settore Immobiliare'
        ELSE NULL 
    END, ', ') AS PAI_E,
    STRING_AGG(CASE 
        WHEN ListPAI_S = 'PAI_S01' THEN 'Questioni Sociali e dei Dipendenti'
        WHEN ListPAI_S = 'PAI_S02' THEN 'Governativi di tipo Sociale'
        ELSE NULL 
    END, ', ') AS PAI_S
into #st_split_ES 
FROM (
    SELECT Rete, COD_FISCALE, ListPAI_E, NULL AS ListPAI_S 
    FROM #st_split_E
    UNION ALL
    SELECT Rete, COD_FISCALE, NULL AS ListPAI_E, ListPAI_S 
    FROM #st_split_S
) AS Combined
GROUP BY 
    [Rete],
    [COD_FISCALE]
update  b
set b.PAI_E=es.PAI_E, b.PAI_S=es.PAI_S
from C6Mart.SPB_W6CLIESG b
inner join #st_split_ES es
on   b.Rete =  es.Rete
and  b.Codice_Fiscale = es.COD_FISCALE
insert into [C6Mart].[W6CLIESG] 
SELECT CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS DECIMAL(8, 0)) AS DTTRIM,
RETE,
codice_fiscale as CODFIS,
'' as CODMAN,
UlterioriPrefSost as U_PREFSOST,
case when isnull(PercSFDR,0.00)=0.00 then 'N' else 'S' end as FLAGSFDR,
case when isnull(PercTaxonomy,0.00)=0.00 then 'N' else 'S' end as FLAGTAXO,
case when ListPAI_E='' and ListPAI_S='' then 'N' else 'S' end as FLAGPAI,
PercSFDR as SFDR_PERC,
PercTaxonomy as TAXO_PERC,
case when ListPAI_E like '%PAI_A01%' then 'S' else 'N' end as PAI_A01,
case when ListPAI_E like '%PAI_A02%' then 'S' else 'N' end as PAI_A02,
case when ListPAI_E like '%PAI_A03%' then 'S' else 'N' end as PAI_A03,
case when ListPAI_E like '%PAI_A04%' then 'S' else 'N' end as PAI_A04,
case when ListPAI_E like '%PAI_A05%' then 'S' else 'N' end as PAI_A05,
case when ListPAI_E like '%PAI_A07%' then 'S' else 'N' end as PAI_A07,
case when ListPAI_E like '%PAI_A08%' then 'S' else 'N' end as PAI_A08,
case when ListPAI_S like '%PAI_S01%' then 'S' else 'N' end as PAI_S01,
case when ListPAI_S like '%PAI_S02%' then 'S' else 'N' end as PAI_S02,
CASE WHEN ListPAI_E like '%PAI_A01%' THEN 'Emissioni gas serra'				   else '' end as PAI_A1,
CASE WHEN ListPAI_E like '%PAI_A02%' THEN 'Biodiversità'					   else '' end as PAI_A2,
CASE WHEN ListPAI_E like '%PAI_A03%' THEN 'Acqua'							   else '' end as PAI_A3,
CASE WHEN ListPAI_E like '%PAI_A04%' THEN 'Rifiuti'							   else '' end as PAI_A4,
CASE WHEN ListPAI_E like '%PAI_A05%' THEN 'Governativi di tipo Ambientale'	   else '' end as PAI_A5,
CASE WHEN ListPAI_E like '%PAI_A07%' THEN 'Efficienza energetica'			   else '' end as PAI_A7,
CASE WHEN ListPAI_E like '%PAI_A08%' THEN 'Settore Immobiliare'				   else '' end as PAI_A8,
CASE WHEN ListPAI_S like '%PAI_S01%' THEN 'Questioni Sociali e dei Dipendenti' else '' end as PAI_S1,
CASE WHEN ListPAI_S like '%PAI_S02%' THEN 'Governativi di tipo Sociale'		   else '' end as PAI_S2,
PAI_E as PAI_A,
PAI_S,
'N' as PAI_NO_DETT,
PesoSostESG as PERC1ESG,
SostPercE as PERCAMB,
SostPercS as PERCSOC,
SostPercG as PERCGOV,
SostPercEInLinea as PERCAMB_OK,
SostPercsInLinea as PERCSOC_OK,
SostPercgInLinea as PERCGOV_OK,
SostSFDR as SFDR_PERCT,
SostSFDRInLinea as SFDR_PERC_OK,
SostTaxonomy as TAXO_PERCT,
SostTaxonomyInLinea as TAXO_PERC_OK,
SostPaiE as PAIA_PERCT,
SostPaiEInLinea as PAIA_PERC_OK,
SostPaiS as PAIS_PERCT,
SostPaiSInLinea as PAIS_PERC_OK
from [c6mart].[SPB_W6CliESG]
 UPDATE  
  LOG_ESECUZIONE  
 SET  
  Fine = GETDATE(),  
  Tipo = 'INSERT',  
  Righe = @@ROWCOUNT  
 WHERE  
  Nome = 'RP_DM_W6CliESG' AND  
  Inizio = (  
   SELECT  
    MAX(Inizio)  
   FROM  
    LOG_ESECUZIONE  
   WHERE  
    Nome = 'RP_DM_W6CliESG'  
  )  
END