-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
--select * from C6MartPeriodico.assetclass
CREATE   procedure [C6MartPeriodico].[RP_DM_ESG_ANAG_METRICHE_bkp_20241210]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @ID_ELAB INT
	DECLARE @TIPO_ELAB VARCHAR(1)
	SET @ID_ELAB = C6MartPeriodico.GETIDELAB()
	SET @TIPO_ELAB = C6MartPeriodico.GETTIPOELAB()
	-- Log esecuzione
	INSERT INTO LOG_ESECUZIONE
		(Nome, Inizio, Nota)
	VALUES (
		'RP_DM_ESG_ANAG_METRICHE',
		GETDATE(),
		'Elaborazione giornaliera'
	)
TRUNCATE TABLE c6StagingPeriodico.ESG_ANAG_METRICHE  
 INSERT INTO [c6StagingPeriodico].ESG_ANAG_METRICHE  
     ( [COD_PRODOTTO]
      ,[CodSottoprodotto]
      ,[CodInterno]
      ,[CodMaf]
      ,[CodAdeguatezza]
      ,[CodIsin]
      ,[ESG_E]
      ,[ESG_S]
      ,[ESG_G]
      ,[CatSFDR]
      ,[ESGRating]
      ,[TrendESGRating]
	   ,[LABEL_Green_Bond]
      ,[LABEL_Social_Bond]
      ,[LABEL_Sustainability_Bond]
      ,[LABEL_Transition_Bond]
      ,[LABEL_Sust_linked_bond]
	  ,LABEL_EU_GREEN_BOND_STANDARD
	   ,[PERC_INVESTIMENTO_TAXONOMY]
      ,[PERC_INVESTIMENTO_SFDR]
	  ,[PERC_INVEST_SFDR_MIN_SUST_E]
	  ,[PERC_INVEST_SFDR_MIN_SUST_S]
	  ,[PAI_LAB_A_Emissioni_Gas_Serra]
      ,[PAI_LAB_A_Biodiversita]
      ,[PAI_LAB_A_Acqua]
      ,[PAI_LAB_A_Rifiuti]
      ,[PAI_LAB_A_Gov_Ambientale]
      ,[PAI_LAB_A_Comb_Fossili]
      ,[PAI_LAB_A_Eff_energetica]
	  ,[PAI_LAB_A_Settore_Immobiliare]
      ,[PAI_LAB_S_Questioni_Soc_Dip]
      ,[PAI_LAB_S_Gov_Sociali]
      ,[U_INFO_ESG]
     ,[ID_ELAB]
      ,[TIPO_ELAB]
      )  
 SELECT   
  --ChiaveProdotto,   
  CatalogueID AS COD_PRODOTTO,  
  C.CodSottoprodotto AS COD_SOTTOPRODOTTO, 
  ISNULL(C.CodInterno, '') AS COD_INTERNO, 
  C.CodMaf AS COD_MAF, 
  C.CodAdeguatezza AS COD_ADEGUATEZZA,  
  ISNULL(C.CodIsin, '') AS COD_ISIN,  
  case when Ltrim(rtrim(ESG_E)) = 'Y' then 'SI' 
  when Ltrim(rtrim(ESG_E)) = 'N' then 'NO'
  else 'NO' end  as ESG_E,
  case when Ltrim(rtrim(ESG_S)) = 'Y' then 'SI' 
  when Ltrim(rtrim(ESG_S)) = 'N' then 'NO'
  else 'NO' end  as ESG_S,
  case when Ltrim(rtrim(ESG_G)) = 'Y' then 'SI' 
  when Ltrim(rtrim(ESG_G)) = 'N' then 'NO'
  else 'NO' end  as ESG_G,
  case when CatSFDR='-' then '' else CatSFDR end as CatSFDR,
  Ltrim(rtrim(ESGRating)) as ESGRating ,
  Ltrim(rtrim(TrendESGRating)) as TrendESGRating,
  case when LABEL_Green_Bond<> '-' then 'Green Bond' else '' end as LABEL_Green_Bond,
  case when LABEL_Social_Bond<> '-' then 'Social Bond' else '' end as LABEL_Social_Bond,
  case when LABEL_Sustainability_Bond<> '-' then 'Sustainability Bond' else '' end as LABEL_Sustainability_Bond,
  case when LABEL_Transition_Bond<> '-' then 'Transition Bond' else '' end as LABEL_Transition_Bond,
  case when LABEL_Sust_linked_bond<> '-' then 'Sustainability Linked Bond' else '' end as LABEL_Sust_linked_bond,
  case when LABEL_EU_GREEN_BOND_STANDARD<> '-' then 'EU Green Bond Standard' else '' end as LABEL_EU_GREEN_BOND_STANDARD,
  PERC_INVESTIMENTO_TAXONOMY *100 as PERC_INVESTIMENTO_TAXONOMY,
  PERC_INVESTIMENTO_SFDR * 100 as PERC_INVESTIMENTO_SFDR,
  [PERC_INVEST_SFDR_MIN_SUST_E] * 100 as PERC_INVEST_SFDR_MIN_SUST_E ,
  [PERC_INVEST_SFDR_MIN_SUST_S]  * 100 as PERC_INVEST_SFDR_MIN_SUST_S,
  case when PAI_LAB_A_Emissioni_Gas_Serra<>'-' then 'Emissioni Gas Serra' else '' end as PAI_LAB_A_Emissioni_Gas_Serra,
  case when PAI_LAB_A_Biodiversita<>'-' then 'Biodiversità' else '' end as PAI_LAB_A_Biodiversita,
  case when PAI_LAB_A_Acqua<>'-' then 'Acqua' else '' end as PAI_LAB_A_Acqua,
  case when PAI_LAB_A_Rifiuti<>'-' then 'Rifiuti' else '' end as PAI_LAB_A_Rifiuti,
  case when PAI_LAB_A_Gov_Ambientale<>'-' then 'Governativi Ambientali' else '' end as PAI_LAB_A_Gov_Ambientale,
  case when PAI_LAB_A_Comb_Fossili<>'-' then 'Combustibili Fossili' else '' end as PAI_LAB_A_Comb_Fossili,
  case when PAI_LAB_A_Eff_energetica<>'-' then 'Efficienza Energetica' else '' end as PAI_LAB_A_Eff_energetica,
  case when PAI_LAB_A_Settore_Immobiliare <>'-' then 'Settore Immobiliare' else '' end as PAI_LAB_A_Settore_Immobiliare,
  case when PAI_LAB_S_Questioni_Soc_Dip<>'-' then 'Questioni Sociali e dei Dipendenti' else '' end as PAI_LAB_S_Questioni_Soc_Dip,
  case when PAI_LAB_S_Gov_Sociali<>'-' then 'Governativi Sociali' else '' end as PAI_LAB_S_Gov_Sociali,
  case when U_INFO_ESG='TOOL_01' then 'Nota1' 
	   when U_INFO_ESG='TOOL_02' then 'Nota2'
	   else '' end as U_INFO_ESG,
  @ID_ELAB AS ID_ELAB,  
  @TIPO_ELAB AS TIPO_ELAB
 FROM c6StagingPeriodico.RP_CatalogoProdotti  c  
 Inner Join c6StagingPeriodico.RP_TB_ESG_ANAG_STRUM  a  
 ON c.ChiaveProdotto = a.ChiaveProdotto  
UPDATE m1
SET m1.label_pai_a = NULLIF(CONCAT_WS(', ',
        NULLIF(m2.PAI_LAB_A_Emissioni_Gas_Serra, ''),
        NULLIF(m2.PAI_LAB_A_Biodiversita, ''),
        NULLIF(m2.PAI_LAB_A_Acqua, ''),
        NULLIF(m2.PAI_LAB_A_Rifiuti, ''),
        NULLIF(m2.PAI_LAB_A_Gov_Ambientale, ''),
        NULLIF(m2.PAI_LAB_A_Comb_Fossili, ''),
        NULLIF(m2.PAI_LAB_A_Eff_energetica, ''),
		NULLIF(m2.PAI_LAB_A_Settore_Immobiliare, '')
        -- NULLIF(m2.PAI_LAB_S_Questioni_Soc_Dip, ''),
        -- NULLIF(m2.PAI_LAB_S_Gov_Sociali, '')
    ), '')
FROM c6StagingPeriodico.ESG_ANAG_METRICHE m1
INNER JOIN c6StagingPeriodico.ESG_ANAG_METRICHE m2 ON 
    ISNULL(m1.COD_PRODOTTO, '') = ISNULL(m2.COD_PRODOTTO, '')
    AND ISNULL(m1.CodSottoprodotto, '') = ISNULL(m2.CodSottoprodotto, '')
    AND ISNULL(m1.CodInterno, '') = ISNULL(m2.CodInterno, '')
    AND ISNULL(m1.CodMaf, '') = ISNULL(m2.CodMaf, '')
    AND ISNULL(m1.CodAdeguatezza, '') = ISNULL(m2.CodAdeguatezza, '')
    AND ISNULL(m1.CodIsin, '') = ISNULL(m2.CodIsin, '')
UPDATE m1
SET m1.label_pai_s = NULLIF(CONCAT_WS(', ', 
       NULLIF(m2.PAI_LAB_S_Questioni_Soc_Dip, ''),
       NULLIF(m2.PAI_LAB_S_Gov_Sociali, '')
    ), '')
FROM c6StagingPeriodico.ESG_ANAG_METRICHE m1
INNER JOIN c6StagingPeriodico.ESG_ANAG_METRICHE m2 ON 
    ISNULL(m1.COD_PRODOTTO, '') = ISNULL(m2.COD_PRODOTTO, '')
    AND ISNULL(m1.CodSottoprodotto, '') = ISNULL(m2.CodSottoprodotto, '')
    AND ISNULL(m1.CodInterno, '') = ISNULL(m2.CodInterno, '')
    AND ISNULL(m1.CodMaf, '') = ISNULL(m2.CodMaf, '')
    AND ISNULL(m1.CodAdeguatezza, '') = ISNULL(m2.CodAdeguatezza, '')
    AND ISNULL(m1.CodIsin, '') = ISNULL(m2.CodIsin, '')
	--select * from c6StagingPeriodico.ESG_ANAG_METRICHE
UPDATE m1
SET m1.Label_U_INFO_ESG = NULLIF(CONCAT_WS(', ', 
       NULLIF(m2.LABEL_Green_Bond, ''),
       NULLIF(m2.Label_Social_Bond, ''),
	   NULLIF(m2.Label_Sustainability_Bond, ''),
	   NULLIF(m2.Label_Transition_Bond, ''),
	   NULLIF(m2.Label_Sust_linked_Bond, ''),
	   NULLIF(m2.Label_EU_Green_Bond_Standard, ''),
	   NULLIF(m2.CatSFDR, ''),
	   NULLIF(m2.U_INFO_ESG, '')
	   ), '')
FROM c6StagingPeriodico.ESG_ANAG_METRICHE m1
INNER JOIN c6StagingPeriodico.ESG_ANAG_METRICHE m2 ON 
    ISNULL(m1.COD_PRODOTTO, '') = ISNULL(m2.COD_PRODOTTO, '')
    AND ISNULL(m1.CodSottoprodotto, '') = ISNULL(m2.CodSottoprodotto, '')
    AND ISNULL(m1.CodInterno, '') = ISNULL(m2.CodInterno, '')
    AND ISNULL(m1.CodMaf, '') = ISNULL(m2.CodMaf, '')
    AND ISNULL(m1.CodAdeguatezza, '') = ISNULL(m2.CodAdeguatezza, '')
    AND ISNULL(m1.CodIsin, '') = ISNULL(m2.CodIsin, '')
	-- Log esecuzione
	UPDATE
		LOG_ESECUZIONE
	SET
		Fine = GETDATE(),
		Tipo = 'INSERT',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'RP_DM_ESG_ANAG_METRICHE' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE
			WHERE
				Nome = 'RP_DM_ESG_ANAG_METRICHE'
		)
END