-- 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 = ID
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 traitees a 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 = ID
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 = ID
and coregistration_champ = 6
and coregistration_eligible = 1
and substring(coregistration_valeur from 1 for 2) not in ('69','75');
-- on passe les lignes traitees a 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 = ID
and coregistration_champ = 6
and coregistration_eligible = 1
and substring(coregistration_valeur from 1 for 2) not in ('69','75')
);
-- fin requete cp


-- requete sur sous question en fonction question_id / reponse_id et campagne_annonceur_id
insert into coregistration.export_auto_non_eligible (export_auto_non_eligible_affichage)
select coregistration_reponse_affichage
from coregistration.coregistration_reponse
inner join coregistration.coregistration on coregistration_coregistration_affichage = coregistration_reponse_affichage and coregistration_champ = 1 -- jointure sur coreg
inner join coregistration.association_coregistration_question on association_coregistration_question_id_question = coregistration_reponse_id_question
where coregistration_reponse_id_question = QUESTION_ID
and substring(coregistration_reponse_id_reponse from 1 for 2) not in ('06','07')
and coregistration_campagne_annonceur = CAMPAGNE_ID
and coregistration_eligible = 1;
-- 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_reponse_affichage
from coregistration.coregistration_reponse
inner join coregistration.coregistration on coregistration_coregistration_affichage = coregistration_reponse_affichage and coregistration_champ = 1 -- jointure sur coreg
inner join coregistration.association_coregistration_question on association_coregistration_question_id_question = coregistration_reponse_id_question
where coregistration_reponse_id_question = QUESTION_ID
and substring(coregistration_reponse_id_reponse from 1 for 2) not in ('06','07')
and coregistration_campagne_annonceur = CAMPAGNE_ID
and coregistration_eligible = 1
);
-- fin requete sous question

-- retraitement des reponses en attente pour forcer le bon format telephone
update coregistration.coregistration_reponse
set coregistration_reponse_id_reponse = REGEXP_REPLACE(REPLACE(coregistation_reponse_id_reponse, '+33', ''), '[^0-9]', '', 'g')
from coregistration.coregistration
where coregistration_coregistration_affichage = coregistration_reponse_affichage
and coregistration_reponse_id_question = ID_QUESTION
and coregistration_campagne_annonceur = ID_CAMPAGNE
and coregistration_champ = 1
and coregistration_eligible = 1;