-- 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 = 180
and coregistration_champ = 12
and coregistration_eligible = 1
and EXTRACT(YEAR FROM age(now()::date, general.cast_to_date(coregistration_valeur))) < 18;
-- 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 = 180
and coregistration_champ = 12
and coregistration_eligible = 1
and EXTRACT(YEAR FROM age(now()::date, general.cast_to_date(coregistration_valeur))) < 18
);
-- fin requete date naissance

-- requete sur ip
insert into coregistration.export_auto_non_eligible (export_auto_non_eligible_affichage)
select coregistration_coregistration_affichage
from coregistration
where coregistration_campagne_annonceur = 180
and coregistration_champ = 13
and coregistration_eligible = 1
and (select name from general.geoip_country(general.cast_to_inet(coregistration_valeur))) != 'France';
-- 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 = 180
and coregistration_champ = 13
and coregistration_eligible = 1
and (select name from general.geoip_country(general.cast_to_inet(coregistration_valeur))) != 'France'
);
-- fin requete ip

-- requete sur cp
insert into coregistration.export_auto_non_eligible (export_auto_non_eligible_affichage)
select coregistration_coregistration_affichage
from coregistration
where coregistration_campagne_annonceur = 180
and coregistration_champ = 6
and coregistration_eligible = 1
and substring(coregistration_valeur from 1 for 2) not in
('01','02','04','05','06','07','08','09','10','11','12','21','25','26','27','30',
'31','32','34','38','39','41','42','43','51','52','54','55','57','58','59','60',
'62','63','64','65','66','69','70','71','73','74','76','77','80','81','82','84','88','89','94');
-- 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 = 180
and coregistration_champ = 6
and coregistration_eligible = 1
and substring(coregistration_valeur from 1 for 2) not in
('01','02','04','05','06','07','08','09','10','11','12','21','25','26','27','30',
'31','32','34','38','39','41','42','43','51','52','54','55','57','58','59','60',
'62','63','64','65','66','69','70','71','73','74','76','77','80','81','82','84','88','89','94')
);
-- fin requete cp

-- exclusion des profils ayant répondu non a la question propriétaire id 116
insert into coregistration.export_auto_non_eligible (export_auto_non_eligible_affichage)
select distinct(coregistration_coregistration_affichage)
from coregistration c
join coregistration_reponse cr on cr.coregistration_reponse_affichage = c.coregistration_coregistration_affichage
join question q on q.question_id = cr.coregistration_reponse_id_question and q.question_id = 116
where coregistration_eligible = 1
and coregistration_reponse_id_reponse = '556'
group by c.coregistration_coregistration_affichage;
-- 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 distinct(coregistration_coregistration_affichage)
from coregistration c
join coregistration_reponse cr on cr.coregistration_reponse_affichage = c.coregistration_coregistration_affichage
join question q on q.question_id = cr.coregistration_reponse_id_question and q.question_id = 116
where coregistration_eligible = 1
and coregistration_reponse_id_reponse = '556'
group by c.coregistration_coregistration_affichage
)
-- fin traitement sous question