-- requete sur civilite
insert into coregistration.export_auto_non_eligible (export_auto_non_eligible_affichage)
select coregistration_coregistration_affichage 
from coregistration 
where coregistration_campagne_annonceur = 127
and coregistration_champ = 4
and coregistration_eligible = 1
and coregistration_valeur = 'monsieur';
-- on passe les lignes traités à exportable 3 pour ne plus les selectionner
update coregistration set coregistration_eligible = 3
where coregistration_coregistration_affichage in
(
select coregistration_coregistration_affichage
from coregistration
where coregistration_campagne_annonceur = 127
and coregistration_champ = 4
and coregistration_eligible = 1
and coregistration_valeur = 'monsieur'
);
-- fin requete civilite

-- requete sur date naissance
insert into coregistration.export_auto_non_eligible (export_auto_non_eligible_affichage)
select coregistration_coregistration_affichage 
from coregistration 
where coregistration_campagne_annonceur = 127
and coregistration_champ = 12
and coregistration_eligible = 1
and EXTRACT(YEAR FROM age(now()::date, general.cast_to_date(coregistration_valeur))) < 20;
-- on passe les lignes traités à exportable 3 pour ne plus les selectionner
update coregistration set coregistration_eligible = 3
where coregistration_coregistration_affichage in
(
select coregistration_coregistration_affichage
from coregistration
where coregistration_campagne_annonceur = 127
and coregistration_champ = 12
and coregistration_eligible = 1
and EXTRACT(YEAR FROM age(now()::date, general.cast_to_date(coregistration_valeur))) < 20
);
-- fin requete date naissance

-- requete hardbounce
insert into coregistration.export_auto_non_eligible (export_auto_non_eligible_affichage)
select coregistration_coregistration_affichage
from coregistration c
inner join general.hardbounce
	on hardbounce_email = coregistration_valeur
left outer join export_auto_hardbounces eah on eah.export_auto_hardbounces_affichage = c.coregistration_coregistration_affichage
where coregistration_campagne_annonceur = 127
and coregistration_champ = 1
and coregistration_eligible = 1
and eah.export_auto_hardbounces_affichage is null;
-- exportable 3
update coregistration set coregistration_eligible = 3
where coregistration_coregistration_affichage
in (
select coregistration_coregistration_affichage
from coregistration c
inner join general.hardbounce
	on hardbounce_email = coregistration_valeur
left outer join export_auto_hardbounces eah on eah.export_auto_hardbounces_affichage = c.coregistration_coregistration_affichage
where coregistration_campagne_annonceur = 127
and coregistration_champ = 1
and coregistration_eligible = 1
and eah.export_auto_hardbounces_affichage is null
);