
set heading on;
set feedback off;
set verify off;
Set termout on;
Set trimspool on;
set pagesize 2000;
set linesize 2500;

set serveroutput on
--accept rep prompt "Taper Retour Chariot pour continuer ou CTRL C pour arreter" ;
--prompt "Recherche entre deux dates. ";
accept deb prompt "Date de debut. Saisie sous la forme aaaammjj :";
accept fin prompt "Date de fin de saisie aaaammjj :";

spool %chemin%'&deb'-'&fin'.csv

DECLARE
	inter	varchar2(200);
	nom	varchar2(200);
	surnom	varchar2(200);
	prenom	varchar2(200);
	sexe	varchar2(1);
	domicile	varchar2(200);
	cp	varchar2(30);
	ville	varchar2(200);
	insee	varchar2(30);
	codedep	varchar2(30);
	dep	varchar2(200);
	mere_nbr number;
	Pinter	varchar2(200);
	Pnom	varchar2(200);
	Psurnom	varchar2(200);
	pprenom	varchar2(200);
	Psexe	varchar2(1);
	Pdomicile	varchar2(200);
	Pcp	varchar2(30);
	Pville	varchar2(200);
	Pinsee	varchar2(30);
	Pcodedep	varchar2(30);
	Pdep	varchar2(200);
	pere_nbr number;
	an varchar2(6);
	mois varchar2(4);
	jour varchar2(4);
	datenaiss varchar2(15);

BEGIN
  for zt in (
select	a.actid,
	f.prlnom,
	b.PRID,
	b.PRNOM,
	b.prsnom,
	b.PRPRE,
	b.prsex,
	b.prdob,
	b.prcob,
	b.prleg,
	b.prrec,
	b.prdoma,
	d.cmcode,
	d.cmname,
	d.cminse,
	e.dpcode,
	e.dpname
from ACT a,PERSON b,ACTPER c,comn d,dept e,prrole f
where a.actdat >= '&deb'
AND a.actdat <= '&fin'
AND c.ACTID = a.ACTID 
AND b.PRID = c.PRID 
and c.prlid=f.prlid
AND c.PRLID = 1000
AND a.ACTBAS = 'NAI'
AND b.prdomid=d.cmid(+)
and d.dpid=e.dpid(+)
AND a.ACTNUM is not null
	
) loop
-- Recherche de la mere
-- Test si existe
select count(*) 
into mere_nbr
from ACT a,PERSON b,ACTPER c,comn d,dept e,prrole f
where c.ACTID = a.ACTID 
AND b.PRID = c.PRID 
and c.prlid=f.prlid
AND c.PRLID = 1004
AND b.prdomid=d.cmid(+)
and d.dpid=e.dpid(+)
and a.actid=zt.actid;

-- On trouve, on effectue la requete mere
if mere_nbr>0 then
select 	f.prlnom,b.PRNOM,b.prsnom,b.PRPRE,b.prsex,b.prdoma,d.cmcode,d.cmname,d.cminse,e.dpcode,e.dpname
into inter,nom,surnom,prenom,sexe,domicile,cp,ville,insee,codedep,dep
from ACT a,PERSON b,ACTPER c,comn d,dept e,prrole f
where c.ACTID = a.ACTID 
AND b.PRID = c.PRID 
and c.prlid=f.prlid
AND c.PRLID = 1004
AND b.prdomid=d.cmid(+)
and d.dpid=e.dpid(+)
and a.actid=zt.actid;
end if;

-- Recherche du pere
-- Test si existe
select count(*) 
into pere_nbr
from ACT a,PERSON b,ACTPER c,comn d,dept e,prrole f
where c.ACTID = a.ACTID 
AND b.PRID = c.PRID 
and c.prlid=f.prlid
AND c.PRLID = 1005
AND b.prdomid=d.cmid(+)
and d.dpid=e.dpid(+)
and a.actid=zt.actid;

-- On trouve, on effectue la requete pere
if pere_nbr>0 then
select 	f.prlnom,b.PRNOM,b.prsnom,b.PRPRE,b.prsex,b.prdoma,d.cmcode,d.cmname,d.cminse,e.dpcode,e.dpname
into pinter,pnom,psurnom,pprenom,psexe,pdomicile,pcp,pville,pinsee,pcodedep,pdep
from ACT a,PERSON b,ACTPER c,comn d,dept e,prrole f
where c.ACTID = a.ACTID 
AND b.PRID = c.PRID 
and c.prlid=f.prlid
AND c.PRLID = 1005
AND b.prdomid=d.cmid(+)
and d.dpid=e.dpid(+)
and a.actid=zt.actid;
end if;


if mere_nbr >0 and pere_nbr>0 then
an:=substr(zt.prdob,1,4);
mois:=substr(zt.prdob,5,2);
jour:=substr(zt.prdob,7,2);
datenaiss:=an||'-'||mois||'-'||jour;
--dbms_output.put_line (zt.PRNOM||','||zt.PRPRE||','||datenaiss||','||zt.PRSEX||','||zt.PRDOMA||','||zt.cminse||','||zt.cmcode||','||zt.cmname||','||zt.dpcode||','||zt.dpname||','||''||','||''||','||''||','||''||','||''||','||''||','||''||','||''||','||''||','||pnom||','||psurnom||','||pprenom||','||psexe||','||pdomicile||','||pcp||','||pville||','||pinsee||','||pcodedep||','||pdep||','||'-');
--dbms_output.put_line ('Mere et pere');
dbms_output.put_line (zt.PRNOM||';'||zt.PRPRE||';'||datenaiss||';'||zt.PRSEX||';'||zt.PRDOMA||';'||zt.cminse||';'||zt.cmcode||';'||zt.cmname||';'||nom||';'||prenom||';'||pnom||';'||pprenom||chr(10));
end if;

if mere_nbr >0 and pere_nbr=0 then
an:=substr(zt.prdob,1,4);
mois:=substr(zt.prdob,5,2);
jour:=substr(zt.prdob,7,2);
datenaiss:=an||'-'||mois||'-'||jour;
--dbms_output.put_line ('Mere');
dbms_output.put_line (zt.PRNOM||';'||zt.PRPRE||';'||datenaiss||';'||zt.PRSEX||';'||zt.PRDOMA||';'||zt.cminse||';'||zt.cmcode||';'||zt.cmname||';'||nom||';'||prenom||';'||''||';'||''||chr(10));
end if;

if mere_nbr =0 and pere_nbr>0 then
an:=substr(zt.prdob,1,4);
mois:=substr(zt.prdob,5,2);
jour:=substr(zt.prdob,7,2);
datenaiss:=an||'-'||mois||'-'||jour;
--dbms_output.put_line ('pere');
dbms_output.put_line (zt.PRNOM||';'||zt.PRPRE||';'||datenaiss||';'||zt.PRSEX||';'||zt.PRDOMA||';'||zt.cminse||';'||zt.cmcode||';'||zt.cmname||';'||''||';'||''||';'||pnom||';'||pprenom||chr(10));
end if;

End loop;


END;
/
spool off;
exit
