set client_encoding to latin1;

begin; 

update prospection set 
  fin=now(), 
  codemodif='david_dupuid_vers_guillaume_brouck-hublet-res'
where 
  ref_cible in (select uid  from cible where ref_departement=145) 
  and ref_employe=194 --from
  and fin is null;

insert into prospection (ztitle, ref_cible, ref_employe,codecrea)
(select distinct 'indefini', ref_cible, 1358070, 'david_dupuid_vers_guillaume_brouck-hublet-res' 
from prospection 
where codemodif='david_dupuid_vers_guillaume_brouck-hublet-res');

update action set ref_employe=1358070 --to
where ref_statut=3 and ref_cible in (select ref_cible from prospection where codemodif='david_dupuid_vers_guillaume_brouck-hublet-res');

end;

begin;
-- pglist creee a la main puis:
insert into list_element (ref_list, ref_object, codecrea) 
  (select 1378929, ref_object, 'listes_david_dupuid_vers_guillaume_brouck-hublet' from list_element where ref_list=1316721 and ref_statut=0);
insert into list_element (ref_list, ref_object, codecrea) 
  (select 1379142, ref_object, 'listes_david_dupuid_vers_guillaume_brouck-hublet' from list_element where ref_list=1296613 and ref_statut=0);
insert into list_element (ref_list, ref_object, codecrea) 
  (select 1379211, ref_object, 'listes_david_dupuid_vers_guillaume_brouck-hublet' from list_element where ref_list=1285568 and ref_statut=0);
end;

begin;

update prospection set 
  fin=now(), 
  codemodif='stephane_aventurier_vers_frederic-trumeau-res'
where 
  ref_cible in (select uid  from cible where ref_departement=145) 
  and ref_employe=222  --from
  and fin is null;

insert into prospection (ztitle, ref_cible, ref_employe,codecrea)
(select distinct 'indefini', ref_cible, 1311158  , 'stephane_aventurier_vers_frederic-trumeau-res'
from prospection 
where codemodif='stephane_aventurier_vers_frederic-trumeau-res');

update action set ref_employe=1311158  --to
where ref_statut=3 and ref_cible in (select ref_cible from prospection where codemodif='stephane_aventurier_vers_frederic-trumeau-res');

end;

begin;

update prospection set 
  fin = now(), 
  codemodif='yann_finot_vers_frederique_bou-bva'
where ref_cible in 
  (select ci.uid from cible ci, contact co, travaille_pour t, societe s 
	where ci.ref_departement=118 
	and ci.ref_contact=co.uid 
	and t.ref_personne_physique=co.uid 
	and t.ref_societe=s.uid 
	and t.fin is null 
	and (s.cp not like '17%' or length(s.cp)!=5 or s.cp is null))
and ref_employe = 586329  --from
and fin is null;

insert into prospection (ztitle,debut,ref_cible,ref_employe,codecrea) 
(select distinct 'indefini', now(), ref_cible, 1311165 , 'yann_finot_vers_frederique_bou-bva' 
from prospection 
where codemodif = 'yann_finot_vers_frederique_bou-bva');

update action set ref_employe=1311165  --to
where ref_statut=3 and ref_cible in (select ref_cible from prospection where codemodif='yann_finot_vers_frederique_bou-bva');

update prospection set 
  fin = now(), 
  codemodif='yann_finot_vers_jeannie_calloch-bva'
where ref_cible in 
  (select ci.uid from cible ci, contact co, travaille_pour t, societe s 
	where ci.ref_departement=118 --dep
	and ci.ref_contact=co.uid 
	and t.ref_personne_physique=co.uid 
	and t.ref_societe=s.uid 
	and t.fin is null 
	and s.cp like '17%'
	and length(s.cp)=5)
and ref_employe = 586329  --from
and fin is null;

insert into prospection (ztitle,debut,ref_cible,ref_employe,codecrea) 
(select distinct 'indefini', now(), ref_cible, 586339, 'yann_finot_vers_jeannie_calloch-bva' 
from prospection 
where codemodif = 'yann_finot_vers_jeannie_calloch-bva');

update action set ref_employe=586339  --to
where ref_statut=3 and ref_cible in (select ref_cible from prospection where codemodif='yann_finot_vers_jeannie_calloch-bva');


update prospection set 
  fin = now(), 
  codemodif='yann_finot_vers_frederique_bou-bml'
where ref_cible in 
  (select ci.uid from cible ci, contact co, travaille_pour t, societe s 
	where ci.ref_departement=586522  --dep
	and ci.ref_contact=co.uid 
	and t.ref_personne_physique=co.uid 
	and t.ref_societe=s.uid 
	and t.fin is null 
	and (s.cp not like '17%' or length(s.cp)!=5 or s.cp is null))
and ref_employe = 586329  --from
and fin is null;

insert into prospection (ztitle,debut,ref_cible,ref_employe,codecrea) 
(select distinct 'indefini', now(), ref_cible, 1311165, 'yann_finot_vers_frederique_bou-bml' 
from prospection 
where codemodif = 'yann_finot_vers_frederique_bou-bml');

update action set ref_employe=1311165 --to
where ref_statut=3 and ref_cible in (select ref_cible from prospection where codemodif='yann_finot_vers_frederique_bou-bml');


update prospection set 
  fin = now(), 
  codemodif='yann_finot_vers_jeannie_calloch-bml'
where ref_cible in 
  (select ci.uid from cible ci, contact co, travaille_pour t, societe s 
	where ci.ref_departement=586522 --dep
	and ci.ref_contact=co.uid 
	and t.ref_personne_physique=co.uid 
	and t.ref_societe=s.uid 
	and t.fin is null 
	and s.cp like '17%'
	and length(s.cp)=5)
and ref_employe = 586329  --from
and fin is null;

insert into prospection (ztitle,debut,ref_cible,ref_employe,codecrea) 
(select distinct 'indefini', now(), ref_cible, 586339, 'yann_finot_vers_jeannie_calloch-bml' 
from prospection 
where codemodif = 'yann_finot_vers_jeannie_calloch-bml');

update action set ref_employe=586339 --to
where ref_statut=3 and ref_cible in (select ref_cible from prospection where codemodif='yann_finot_vers_jeannie_calloch-bml');


end;


-- dispatch marrach
begin;

update prospection set 
  fin = now(), 
  codemodif='mathieu_marracq_vers_dominique_albospeyre-pdf'
where ref_cible in 
  (select ci.uid from cible ci, contact co, travaille_pour t, societe s 
	where ci.ref_departement=8 --dep
	and ci.ref_contact=co.uid 
	and t.ref_personne_physique=co.uid 
	and t.ref_societe=s.uid 
	and t.fin is null 
	and s.cp like '84%'
	and length(s.cp)=5)
and ref_employe = 27  --from
and fin is null;

insert into prospection (ztitle,debut,ref_cible,ref_employe,codecrea) 
(select distinct 'indefini', now(), ref_cible, 18, 'mathieu_marracq_vers_dominique_albospeyre-pdf' 
from prospection 
where codemodif = 'mathieu_marracq_vers_dominique_albospeyre-pdf');

update action set ref_employe=18 , codemodif='mathieu_marracq_vers_dominique_albospeyre-pdf' --to
where ref_statut=3 and ref_cible in (select ref_cible from prospection where codemodif='mathieu_marracq_vers_dominique_albospeyre-pdf');



update prospection set 
  fin = now(), 
  codemodif='mathieu_marracq_vers_gregory_faye-pdf'
where ref_cible in 
  (select ci.uid from cible ci, contact co, travaille_pour t, societe s 
	where ci.ref_departement=8 --dep
	and ci.ref_contact=co.uid 
	and t.ref_personne_physique=co.uid 
	and t.ref_societe=s.uid 
	and t.fin is null 
	and (s.cp like '13%' or s.cp like '26%')
	and length(s.cp)=5)
and ref_employe = 27  --from
and fin is null;

insert into prospection (ztitle,debut,ref_cible,ref_employe,codecrea) 
(select distinct 'indefini', now(), ref_cible, 30, 'mathieu_marracq_vers_gregory_faye-pdf' 
from prospection 
where codemodif = 'mathieu_marracq_vers_gregory_faye-pdf');

update action set ref_employe=30, codemodif='mathieu_marracq_vers_gregory_faye-pdf'  --to
where ref_statut=3 and ref_cible in (select ref_cible from prospection where codemodif='mathieu_marracq_vers_gregory_faye-pdf');




update prospection set 
  fin = now(), 
  codemodif='mathieu_marracq_vers_olivier_herbin-pdf'
where ref_cible in 
  (select ci.uid from cible ci, contact co, travaille_pour t, societe s 
	where ci.ref_departement=8 --dep
	and ci.ref_contact=co.uid 
	and t.ref_personne_physique=co.uid 
	and t.ref_societe=s.uid 
	and t.fin is null 
	and (s.cp like '07%' or s.cp like '30%' or s.cp like '34%')
	and length(s.cp)=5)
and ref_employe = 27  --from
and fin is null;

insert into prospection (ztitle,debut,ref_cible,ref_employe,codecrea) 
(select distinct 'indefini', now(), ref_cible, 21, 'mathieu_marracq_vers_olivier_herbin-pdf' 
from prospection 
where codemodif = 'mathieu_marracq_vers_olivier_herbin-pdf');

update action set ref_employe=21 , codemodif='mathieu_marracq_vers_olivier_herbin-pdf' --to
where ref_statut=3 and ref_cible in (select ref_cible from prospection where codemodif='mathieu_marracq_vers_olivier_herbin-pdf');

end;