CREATE procedure [C6MartPeriodico].[Blacklist_Blocco_Tabellone]
@param1 varchar(20)
AS  
BEGIN  
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.  
-- [C6MartPeriodico].[Blacklist_Blocco_Tabellone] Blocco
-- [C6MartPeriodico].[Blacklist_Blocco_Tabellone] SBlocco
--select * from c6martperiodico.tabellone where blacklist is not null
--select * from C6MartPeriodico.ExcludedList 
--select * from C6MartPeriodico.ExcludedList_testing
--
--select * from c6martperiodico.tabellone where codice_fiscale='BBBMRA56T09L736R'
--select * from C6MartPeriodico.ExcludedList where codicefiscale='FRILXA72T04H501R'
--select codicefiscale,COUNT(*) from C6MartPeriodico.ExcludedList group by codicefiscale having COUNT(*)>1
--insert into C6MartPeriodico.ExcludedList
--values ('F','BBBMRA56T09L736R','TestF',GETDATE(),1),
--('F','BBBMRA56T09L736R','TestB',GETDATE(),1),
--('F','BBBMRA56T09L736R','TestC',GETDATE(),1),
--('F','BBBMRA56T09L736R','TestF',GETDATE(),1)
----delete from C6MartPeriodico.ExcludedList  where DescrErr='TestC'
if (@param1='BLOCCO')
begin
drop table if exists #riunisco
drop table if exists #rimuovoduplicati
update C6MartPeriodico.tabellone
set blacklist=NULL
select distinct rete,codicefiscale,DescrErr
into #rimuovoduplicati
from  C6MartPeriodico.ExcludedList
SELECT rete,codicefiscale,STRING_AGG( ISNULL(DescrErr, ' '), ',') As descrerr 
	into #riunisco
       From #rimuovoduplicati group by rete,codicefiscale
update C6MartPeriodico.tabellone 
set blacklist=r.descrerr
from #riunisco r inner join C6MartPeriodico.tabellone t 
on t.chiave_acn=r.Rete+r.codicefiscale
update C6MartPeriodico.tabellone 
set lettera_sost='S'
where blacklist like '%sostitutiva%'
end
if (@param1='SBLOCCO') 
begin
update C6MartPeriodico.tabellone 
set blacklist=NULL
drop  table if exists #riunisco1
drop table if exists #rimuovoduplicati1
select distinct rete,codicefiscale,DescrErr
into #rimuovoduplicati1
from  C6MartPeriodico.ExcludedList
SELECT rete,codicefiscale,STRING_AGG( ISNULL(DescrErr, ' '), ',') As descrerr 
	into #riunisco1
       From #rimuovoduplicati1 group by rete,codicefiscale
update C6MartPeriodico.tabellone 
set blacklist=r.descrerr
from #riunisco1 r inner join C6MartPeriodico.tabellone t 
on t.chiave_acn=r.Rete+r.codicefiscale
update C6MartPeriodico.tabellone 
set lettera_sost='S'
where blacklist like '%sostitutiva%'
end
--update C6MartPeriodico.tabellone 
----set inizio_milestone= case when inizio_milestone like '%1900%' then GETDATE() else inizio_milestone end,
--set blacklist=case when blacklist = exc.descrErr then REPLACE(blacklist,exc.DescrErr,'') else blacklist end
--from C6MartPeriodico.ExcludedList_testing exc
--where chiave_acn=exc.rete+exc.codicefiscale and exc.DescrErr<>'SEGNALAZIONE_VP'
--end
--select chiave_acn,value from C6MartPeriodico.tabellone 
--CROSS APPLY STRING_SPLIT(blacklist, ',')
--select * from C6MartPeriodico.motivazioni_blacklist
end