set client_encoding to latin1;

begin; 

-- Pouvez vous migrer les contacts BVA de
-- Fréderique Bou : http://zen.indicateurbertrand.com/ZEN/118/586347/1358350
-- pour les département 39,71,03,70 et 68 sur le compte de
-- Jerome de Kermadec : http://zen.indicateurbertrand.com/ZEN/118/586344/1396054

update prospection set 
  fin=now(), 
  codemodif='Bou BVA vers Kermadec'
where 
  ref_cible in (select ci.uid  from cible ci, contact co, travaille_pour t, societe s 
				where ci.ref_departement=(select uid from departement where code='BVA') 
					and ci.ref_contact=co.uid and t.ref_personne_physique=co.uid and t.ref_societe=s.uid 
					and (s.cp like '39%' or s.cp like '71%' or s.cp like '03%' or s.cp like '70%' or s.cp like '68%'))
  and ref_employe=(select uid from employe where prenom='Frédérique' and nom='BOU')
  and fin is null;

insert into prospection (ztitle, ref_cible, ref_employe, codecrea)
	(select distinct 'indefini', ref_cible, (select uid from employe where prenom='Jérome' and nom='DE KERMADEC'), 'Bou BVA vers Kermadec' 
	from prospection 
	where codemodif='Bou BVA vers Kermadec');

update action set ref_employe=(select uid from employe where prenom='Jérome' and nom='DE KERMADEC')
where ref_statut=3 and ref_cible in (select ref_cible from prospection where codemodif='Bou BVA vers Kermadec');

-- Si possible meme requete pour BML.
-- Fréderique Bou : http://zen.indicateurbertrand.com/ZEN/586522/586523/1358352
-- Jerome de Kermadec : http://zen.indicateurbertrand.com/ZEN/586522/651000/1396052

update prospection set 
  fin=now(), 
  codemodif='Bou BML vers Kermadec'
where 
  ref_cible in (select ci.uid  from cible ci, contact co, travaille_pour t, societe s 
				where ci.ref_departement=(select uid from departement where code='BML') 
					and ci.ref_contact=co.uid and t.ref_personne_physique=co.uid and t.ref_societe=s.uid 
					and (s.cp like '39%' or s.cp like '71%' or s.cp like '03%' or s.cp like '70%' or s.cp like '68%'))
  and ref_employe=(select uid from employe where prenom='Frédérique' and nom='BOU')
  and fin is null;

insert into prospection (ztitle, ref_cible, ref_employe, codecrea)
	(select distinct 'indefini', ref_cible, (select uid from employe where prenom='Jérome' and nom='DE KERMADEC'), 'Bou BML vers Kermadec' 
	from prospection 
	where codemodif='Bou BML vers Kermadec');

update action set ref_employe=(select uid from employe where prenom='Jérome' and nom='DE KERMADEC')
where ref_statut=3 and ref_cible in (select ref_cible from prospection where codemodif='Bou BML vers Kermadec');

-- commit;