-----------------------------------------
-- Liste des familles
select
e.id,
e.date_begin,
e.name,
p1.displayname as 'Mere',
p2.displayname as 'Pere'
from event e
left outer join person p1 on p1.id = e.person_1_id
left outer join person p2 on p2.id = e.person_2_id
where e.event_type_id in (2) -- Mariages, unions

----------------------------
-- Liste des enfants de #1 
select 
e.person_1_id,
p1.displayname,
y.name as event_type_name,
e.date_begin as event_date,
e.person_2_id,
p2.displayname as Mère,
e.person_3_id,
p3.displayname as Père,
pl.name as place_name
from 
event e,
person p1,
event_type y
left outer join person p2 on e.person_2_id = p2.id 
left outer join person p3 on e.person_3_id = p3.id 
left outer join place pl on pl.id = e.place_id 
where y.id = e.event_type_id
and e.person_1_id = p1.id 
and e.event_type_id = 10 -- Type naissance
and ( e.person_3_id = 2 or e.person_2_id = 2) -- Chercher père ou mère
order by e.date_begin


----------------------------------------
-- Liste des parents individu #4
select 
e.person_1_id,
p1.displayname,
y.name as event_type_name,
e.date_begin as event_date,
e.person_2_id,
p2.displayname as Mère,
e.person_3_id,
p3.displayname as Père,
pl.name as place_name
from 
event e,
person p1,
event_type y
left outer join person p2 on e.person_2_id = p2.id 
left outer join person p3 on e.person_3_id = p3.id 
left outer join place pl on pl.id = e.place_id 
where y.id = e.event_type_id
and e.person_1_id = p1.id 
and e.event_type_id = 10 -- Type naissance
and e.person_1_id = 4 
order by e.date_begin

-----------------------------------------
-- Liste des evenements d'un individu #1
select 
e.person_1_id,
p1.displayname,
e.person_2_id,
p2.displayname,
e.person_3_id,
p3.displayname,
y.name as event_type_name,
e.date_begin as event_date,
pl.name as place_name
from 
event e,
person p1,
event_type y
left outer join person p2 on e.person_2_id = p2.id 
left outer join person p3 on e.person_3_id = p3.id 
left outer join place pl on pl.id = e.place_id 
where y.id = e.event_type_id
and e.person_1_id = p1.id 
and e.person_1_id =  1
order by e.date_begin

-----------------------------------------
-----------------------------------------
-- Liste des sources des evenements d'un individu 

select 
s.id,
e.id,
e.date_begin,
e.name,
s.name,
t.name,
d.name,
p1.displayname,
p2.displayname
--p3.displayname
from Event_source es , source s, event e,
person p1,
source_type t,
depot d
left outer join person p2 on p2.id = e.person_2_id
WHERE
	es.source_id = s.id
and es.event_id = e.id
and p1.id = e.person_1_id
and t.id = s.source_type_id
and d.id = s.depot_id
and e.person_1_id =  1 -- Individu 
order by e.date_begin

--------------------------------------------
-- Gedcom
-- http://joel.q.free.fr/gedcom.html
-- http://homepages.rootsweb.com/~pmcbride/gedcom/55gcappb.htm#S1
-- http://gencom.org.nz/GEDCOM_tags.html

select *
from (
	select 1 as 'Order', 0 as 'Level', NULL as 'DbId', NULL as 'Id', 'HEAD' as 'Code', NULL as 'Data'
	UNION
	select 2, 1, NULL, NULL, 'DATE', '10 Sep 1998'
	UNION
	select 3, 1, NULL, NULL, 'CHAR', 'UTF-8'
 	UNION
	select 4, 1, NULL, NULL, 'FILE', 'myfile.ged'
	UNION
	select 5, 1, NULL, NULL, 'GEDC', NULL
	UNION
	select 6, 2, NULL, NULL, 'VERS', '5.5'
	UNION 
	select 50 as 'Order', 0 as 'Level', NULL as 'DbId', '@S0@' as 'Id', 'SUBM' as 'Code', NULL as 'Data'
	union
	select 51, 1, NULL, NULL, 'NAME', 'Laurent Vardon'	
	union
	select 100, 0, p.id, '@I'||p.id||'@', 'INDI', NULL   from person p
	UNION
	select 101, 0, NULL, '@F' ||id|| '@', 'FAM', NULL  from (
										select * from (
											select
											e.id,
											e.date_begin,
											e.name,
											p1.displayname as 'Mere',
											p2.displayname as 'Pere'
											from event e
											left outer join person p1 on p1.id = e.person_1_id
											left outer join person p2 on p2.id = e.person_2_id
											where e.event_type_id in (select et.id from Event_type et where et.code in( 'MARI' , 'UNION')) -- Mariages, unions
										)
									)
	UNION
	select 102, 0, s.id, '@S'||s.id||'@', 'SOUR', NULL from source s
	UNION
	select 103, 0, d.id, '@R'||d.id||'@', 'REPO', NULL from depot d
	UNION
	select 999, 0, NULL, NULL, 'TRLR', NULL
)
order by "Order", "Level"


----
-- Liste des individus et propriétés

select *
from (
	select 1 as 'Order', 1 as 'SubOrder', 1 as 'Level', p.id as 'DbId', NULL as 'Id', 'NAME' as 'Code' , p.firstname || ',' || p.lastname as 'Data' from person p
	union
	select 1, 2, 1, p.id, NULL, 'SEX' , p.sex from person p
	union
	select 1, 3, 1, p.id, NULL, 'BIRT' , NULL from person p -- +
	union
	select 1, 4, 1, p.id, NULL, 'DEAT' , NULL from person p -- +
	union
	select 1, 5, 1, p.id, NULL, 'FAMS' , '@@' from person p -- famille de son couple (pointeur vers famille @F3@)
	union
	select 1, 6, 1, p.id, NULL, 'FAMC' , '@@' from person p -- famille de ses parents (pointeur vers famille @F1@)
	union
	select 1, 7, 1, p.id, NULL, 'OCCU' , NULL from person p -- 
	union
	select 1, 8, 1, p.id, NULL, 'NOTE' , '@@' from person p
)
order by "Order", "SubOrder", "DbId"


----------
-- Date evenement naissance , individu #x
-- Lieu evenement naissance , individu #x

select * from (
	select 1 as 'Order', 2 as 'Level',  NULL as 'Dbid', NULL as 'Id', 'DATE' as 'Code', e.date_begin as 'Data' 
	from event e 
	where 
	e.person_1_id = 4 
	and e.event_type_id = ( select et.id from Event_type  et where et.code = 'BIRTH' )
	union
	select 2 as 'Order', 2 as 'Level',  NULL as 'Dbid', NULL as 'Id', 'PLAC' as 'Code', p.name || ifnull(', ' || p.zipcode,'') as 'Data'
	from event e, place p
	where 
	e.place_id = p.id
	and e.person_1_id = 4 
	and e.event_type_id = ( select et.id from Event_type  et where et.code = 'BIRTH' )
)
order by "Order", "DbId"

--------
-- Identifiant source evenement #x

select 2 as 'Level','SOUR' as 'Code', '@' || s.id || '@'
from event_source es, source s
where 
es.source_id = s.id
and es.event_id = 2