-- retraitement des reponses en attente pour forcer le bon format date
update coregistration.coregistration_reponse
set coregistration_reponse_id_reponse = to_char(general.cast_to_date(coregistration_reponse_id_reponse), 'YYYY-MM-DD')
from coregistration.coregistration
where coregistration_coregistration_affichage = coregistration_reponse_affichage
and coregistration_reponse_id_question = 581
and coregistration_campagne_annonceur = 846
and coregistration_champ = 1
and coregistration_eligible = 1;
-- requete sur sous question en fonction de la valeur de la reponse
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
where coregistration_reponse_id_question = 581
and coregistration_campagne_annonceur = 846
and coregistration_reponse_id_reponse = '01/01/1900'
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
where coregistration_reponse_id_question = 581
and coregistration_campagne_annonceur = 846
and coregistration_reponse_id_reponse = '01/01/1900'
and coregistration_eligible = 1
);
-- fin requete sous question

-- 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 co1.coregistration_coregistration_affichage
from coregistration co1
inner join coregistration.coregistration co2 on co1.coregistration_valeur = co2.coregistration_valeur
inner join coregistration.coregistration_reponse on co1.coregistration_coregistration_affichage = coregistration_reponse_affichage
where co1.coregistration_campagne_annonceur = 846
AND co2.coregistration_campagne_annonceur = 847
AND co1.coregistration_champ = 1
AND co2.coregistration_champ = 1
AND (coregistration_reponse_id_question = 581 OR coregistration_reponse_id_question = 582)
AND general.cast_to_date(coregistration_reponse_id_reponse) < current_date - INTERVAL '3 MONTH'
AND general.cast_to_date(coregistration_reponse_id_reponse) >=  current_date - INTERVAL '6 MONTH'
AND co1.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 co1.coregistration_coregistration_affichage
from coregistration co1
inner join coregistration.coregistration co2 on co1.coregistration_valeur = co2.coregistration_valeur
inner join coregistration.coregistration_reponse on co1.coregistration_coregistration_affichage = coregistration_reponse_affichage
where co1.coregistration_campagne_annonceur = 846
AND co2.coregistration_campagne_annonceur = 847
AND co1.coregistration_champ = 1
AND co2.coregistration_champ = 1
AND (coregistration_reponse_id_question = 581 OR coregistration_reponse_id_question = 582)
AND general.cast_to_date(coregistration_reponse_id_reponse) < current_date - INTERVAL '3 MONTH'
AND general.cast_to_date(coregistration_reponse_id_reponse) >=  current_date - INTERVAL '6 MONTH'
AND co1.coregistration_eligible = 1
);
-- fin requete sous question
--en fonction de la date changer la campagne (4 a 6 mois)
update coregistration_affichage set coregistration_affichage_campagne_annonceur = 847
where coregistration_affichage_id 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
where coregistration_reponse_id_question = 581
and coregistration_campagne_annonceur = 846
and general.cast_to_date(coregistration_reponse_id_reponse) < current_date - INTERVAL '3 MONTH'
and general.cast_to_date(coregistration_reponse_id_reponse) >=  current_date - INTERVAL '6 MONTH'
and coregistration_eligible = 1
);
--mis a jour de la question_id
update coregistration.coregistration_reponse set coregistration_reponse_id_question = 582
where coregistration_reponse_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
where coregistration_reponse_id_question = 581
and coregistration_campagne_annonceur = 846
and general.cast_to_date(coregistration_reponse_id_reponse) < current_date - INTERVAL '3 MONTH'
and general.cast_to_date(coregistration_reponse_id_reponse) >=  current_date - INTERVAL '6 MONTH'
and coregistration_eligible = 1
);
-- on vérifie si le lead n'est pas compatible avec les campagnes assoscie
update coregistration set coregistration_campagne_annonceur = 847
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
where coregistration_reponse_id_question = 582
and coregistration_campagne_annonceur = 846
and general.cast_to_date(coregistration_reponse_id_reponse) < current_date - INTERVAL '3 MONTH'
and general.cast_to_date(coregistration_reponse_id_reponse) >=  current_date - INTERVAL '6 MONTH'
and coregistration_eligible = 1
);
-- fin requete sous question

-- 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 co1.coregistration_coregistration_affichage
from coregistration co1
inner join coregistration.coregistration co2 on co1.coregistration_valeur = co2.coregistration_valeur
inner join coregistration.coregistration_reponse on co1.coregistration_coregistration_affichage = coregistration_reponse_affichage
where co1.coregistration_campagne_annonceur = 846
AND co2.coregistration_campagne_annonceur = 865
AND co1.coregistration_champ = 1
AND co2.coregistration_champ = 1
AND (coregistration_reponse_id_question = 581 OR coregistration_reponse_id_question = 601)
AND general.cast_to_date(coregistration_reponse_id_reponse) < current_date - INTERVAL '6 MONTH'
AND general.cast_to_date(coregistration_reponse_id_reponse) >=  current_date - INTERVAL '12 MONTH'
AND co1.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 co1.coregistration_coregistration_affichage
from coregistration co1
inner join coregistration.coregistration co2 on co1.coregistration_valeur = co2.coregistration_valeur
inner join coregistration.coregistration_reponse on co1.coregistration_coregistration_affichage = coregistration_reponse_affichage
where co1.coregistration_campagne_annonceur = 846
AND co2.coregistration_campagne_annonceur = 865
AND co1.coregistration_champ = 1
AND co2.coregistration_champ = 1
AND (coregistration_reponse_id_question = 581 OR coregistration_reponse_id_question = 601)
AND general.cast_to_date(coregistration_reponse_id_reponse) < current_date - INTERVAL '6 MONTH'
AND general.cast_to_date(coregistration_reponse_id_reponse) >=  current_date - INTERVAL '12 MONTH'
AND co1.coregistration_eligible = 1
);
-- fin requete sous question
--en fonction de la date changer la campagne (7 a 12 mois)
update coregistration_affichage set coregistration_affichage_campagne_annonceur = 865
where coregistration_affichage_id 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
where coregistration_reponse_id_question = 581
and coregistration_campagne_annonceur = 846
and general.cast_to_date(coregistration_reponse_id_reponse) < current_date - INTERVAL '6 MONTH'
and general.cast_to_date(coregistration_reponse_id_reponse) >=  current_date - INTERVAL '12 MONTH'
and coregistration_eligible = 1
);
--mis a jour de la question_id
update coregistration.coregistration_reponse set coregistration_reponse_id_question = 601
where coregistration_reponse_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
where coregistration_reponse_id_question = 581
and coregistration_campagne_annonceur = 846
and general.cast_to_date(coregistration_reponse_id_reponse) < current_date - INTERVAL '6 MONTH'
and general.cast_to_date(coregistration_reponse_id_reponse) >=  current_date - INTERVAL '12 MONTH'
and coregistration_eligible = 1
);
-- on vérifie si le lead n'est pas compatible avec les campagnes assoscie
update coregistration set coregistration_campagne_annonceur = 865
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
where coregistration_reponse_id_question = 601
and coregistration_campagne_annonceur = 846
and general.cast_to_date(coregistration_reponse_id_reponse) < current_date - INTERVAL '6 MONTH'
and general.cast_to_date(coregistration_reponse_id_reponse) >=  current_date - INTERVAL '12 MONTH'
and coregistration_eligible = 1
);
-- fin requete sous question


-- requete sur sous question en fonction de la valeur de la reponse
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
where coregistration_reponse_id_question = 581
and coregistration_campagne_annonceur = 846
and (general.cast_to_date(coregistration_reponse_id_reponse) > current_date OR general.cast_to_date(coregistration_reponse_id_reponse) < current_date - INTERVAL '12 MONTH')
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
where coregistration_reponse_id_question = 581
and coregistration_campagne_annonceur = 846
and (general.cast_to_date(coregistration_reponse_id_reponse) > current_date OR general.cast_to_date(coregistration_reponse_id_reponse) < current_date - INTERVAL '12 MONTH')
and coregistration_eligible = 1
);