D-Net

-- add column

#altered in beta & production

#ALTER TABLE claim ADD COLUMN claimed_in_dashboard varchar(50);

-- update column with specific value:

#run in production 25-11-2019select

#UPDATE claim SET claimed_in_dashboard = 'production_explore';

-- update claims linked to a community

#run in beta 25-11-2019

#UPDATE claim SET claimed_in_dashboard = concat('beta_connect_',split_part(source_id,'::',1)) where source_type = 'context' and claim_date between '2018-03-01' and '2019-12-31' ;

--which users claims since connect release

select claimedby,claimed_in_dashboard, source_id, target_id  from claim where claim_date between '2018-03-01' and '2019-12-31';

-- update claims made by a community user/manager

#run in beta 25-11-2019

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_ni' where  ( claimedby = 'sorina.pop@creatis.insa-lyon.fr' or claimedby = 'xavier.rolland@inria.fr'  or claimedby = 'micheL.dojat@inserm.fr' or claimedby = 'camille.maumet@inria.fr') and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_elexir-gr' where  ( claimedby = 'schatz@imis.athena-innovation.gr' or claimedby = 'vergoulis@imis.athena-innovation.gr' ) and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_fam' where  ( claimedby = 'taha.imzilen@ird.fr' or claimedby = 'bastien.merigot@umontpellier.fr'  or claimedby = 'paul.taconet@ird.fr' or claimedby = 'julien.barde@ird.fr') and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_mes' where  ( claimedby = 'spesant@marum.de' or claimedby = 'javier.moreno@gm.uca.es') and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_ni' where  ( claimedby = 'sorina.pop@creatis.insa-lyon.fr' or claimedby = 'xavier.rolland@inria.fr'  or claimedby = 'micheL.dojat@inserm.fr' or claimedby = 'camille.maumet@inria.fr') and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_rda' where  ( claimedby = 'ilaria.fava@gmail.com') and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_dh-ch' where  ( claimedby = 'achille.felicetti@gmail.com' or claimedby = 'francesca.murano@unifi.it' or claimedby = 'sokhi.huda@uinsby.ac.id') and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_ee' where  ( claimedby = 'evita.mailli@icre8.eu' or claimedby = 'avassilopoulos.aua@gmail.com' or claimedby = 'ebun.akinsete@icre8.eu') and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_dariah' where  ( claimedby = 'erzsebet.toth-czifra@dariah.eu') and claim_date between '2018-03-01' and '2019-12-31' ;

# init rest values with explore

select claimedby,claimed_in_dashboard, source_id, target_id  from claim where claimed_in_dashboard is null;

#run in beta 25-11-2019

#UPDATE claim SET claimed_in_dashboard = 'beta_explore' where claimed_in_dashboard is null;

--

#if the properties file is cached and there is no value for  dashboard, the claimed_in_dashboard value may be 'production_undefined'. Correct with:

# select * from claim where claimed_in_dashboard = 'production_undefined' ;

#UPDATE claim SET claimed_in_dashboard = 'production_explore' where claimed_in_dashboard = 'production_undefined' ;

--

select * from claim where claim_date between '2018-03-01' and '2019-12-31' and  ( claimed_in_dashboard ='beta_connect_mes'  or  claimed_in_dashboard ='beta_connect_ee'  or claimed_in_dashboard ='beta_connect_ni' or claimed_in_dashboard ='beta_connect_dh-ch' or claimed_in_dashboard ='beta_connect_fam');