-- Tree-HourglassMembers.sql
-- 2016-05-08 Tom Holden ve3meo
/*
Lists the record numbers and sex of all the members in the hourglass
of the same tree as that of a starting member specified at runtime.
N.B. This is a subset of all the members of the same tree.
Uses AncestorsRecursive.sql to create a temp table xAncestors listing the
RINs of the ancestors of the reference person.
 Then it uses a minor variant of
DescendantsRecursive.sql to list the the RINs and sex of all the descendants 
of the reference person's ancestors.
*/

-- AncestorsRecursive.sql
/*
2014-04-14 Tom Holden ve3meo

Generates the list of RINs for the ancestors of a person.

Requires support not only for SQLite 3.8.3 or later but also
for named parameters for user input of the RIN of the starting person
and choice of birth only or all relationships. 

Developed and tested with current SQLite Expert Personal 3.5.36.2456

Uses the WITH RECURSIVE syntax introduced in SQLite 3.8.3 2014-02-03
modelled on http://www.sqlite.org/lang_with.html example
and complement of DescendantsRecursive.sql
*/

DROP TABLE IF EXISTS xAncestors
;
CREATE TEMP TABLE xAncestors
AS
WITH RECURSIVE
  parent_of(ChildID, ParentID) AS
    (SELECT PersonID, FatherID AS ParentID FROM PersonTable 
       LEFT JOIN ChildTable ON PersonID=ChildTable.ChildID 
       LEFT JOIN FamilyTable USING(FamilyID) 
       WHERE 
         CASE $BirthOnly(YN)       
         WHEN 'Y' OR 'y' THEN RelFather=0         
         ELSE 1         
         END       
         --RelFather=0 --birth father (ELSE WHERE 1 to include all relationships)
     UNION
     SELECT PersonID, MotherID AS ParentID FROM PersonTable 
       LEFT JOIN ChildTable ON PersonID=ChildTable.ChildID 
       LEFT JOIN FamilyTable USING(FamilyID) 
       WHERE 
         CASE $BirthOnly(YN)       
         WHEN 'Y' OR 'y' THEN RelMother=0         
         ELSE 1         
         END
         --RelMother=0 --birth mother (ELSE WHERE 1 to include all relationships)
     ),
  ancestor_of_person(AncestorID) AS
    (SELECT ParentID FROM parent_of 
       WHERE ChildID=$Person(RIN) --enter RIN of starting person at runtime
     UNION --ALL
     SELECT ParentID FROM parent_of 
       INNER JOIN ancestor_of_person ON ChildID = AncestorID)
SELECT AncestorID, Sex FROM ancestor_of_person, PersonTable
 WHERE ancestor_of_person.AncestorID=PersonTable.PersonID
UNION
SELECT PersonID, Sex FROM PersonTable WHERE PersonID = $Person(RIN) -- include starting person
;

-- DescendantsRecursive.sql
/*
2014-04-14 Tom Holden ve3meo
rev 2015-11-24 maternal or paternal lines added

Generates the list of RINs for the descendants of a person,
optionally through through their maternal or paternal lines.

Requires support not only for SQLite 3.8.3 or later but also
for named parameters for user input of the RIN of the starting person,
choice of birth only or all relationships, and choice of the gender
of the persons (for paternal Y-STR or maternal mtDNA lines). 

Developed and tested with current SQLite Expert Personal 3.5.36.2456

Uses the WITH RECURSIVE syntax introduced in SQLite 3.8.3 2014-02-03
modelled on http://www.sqlite.org/lang_with.html example 
and complement of AncestorsRecursive.sql 
*/

WITH RECURSIVE
  child_of(ParentID, ChildID) AS
    (SELECT PersonID, ChildTable.ChildID FROM PersonTable 
       LEFT JOIN FamilyTable ON PersonID=FatherID        
       LEFT JOIN ChildTable USING(FamilyID) 
       WHERE 
         CASE $BirthOnly(YN)       
         WHEN 'Y' OR 'y' THEN RelFather=0         
         ELSE 1         
         END       
         --RelFather=0 --birth father (ELSE WHERE 1 to include all relationships)
         AND 
         CASE UPPER($Sex(MF?))         
         WHEN 'M' THEN Sex = 0         
         WHEN 'F' THEN Sex = 1         
         ELSE 1         
         END
     UNION 
     SELECT PersonID, ChildTable.ChildID FROM PersonTable 
       LEFT JOIN FamilyTable ON PersonID=MotherID        
       LEFT JOIN ChildTable USING(FamilyID) 
       WHERE 
         CASE $BirthOnly(YN)       
         WHEN 'Y' OR 'y' THEN RelMother=0         
         ELSE 1         
         END
         --RelMother=0 --birth mother (ELSE WHERE 1 to include all relationships)         
         AND
         CASE UPPER($Sex(MF?))         
         WHEN 'M' THEN Sex = 0         
         WHEN 'F' THEN Sex = 1         
         ELSE 1         
         END
     ),
  descendant_of_person(DescendantID) AS
    (SELECT ChildID FROM child_of 
       WHERE ParentID IN (SELECT DISTINCT AncestorID FROM xAncestors) --=$Person(RIN) --enter RIN of starting person at runtime
     UNION --ALL
     SELECT ChildID FROM child_of 
       INNER JOIN descendant_of_person ON ParentID = DescendantID)
SELECT DescendantID, Sex FROM descendant_of_person, PersonTable
 WHERE descendant_of_person.DescendantID=PersonTable.PersonID 
 AND
         CASE UPPER($Sex(MF?))         
         WHEN 'M' THEN Sex = 0         
         WHEN 'F' THEN Sex = 1         
         ELSE 1         
         END
UNION
SELECT * FROM xAncestors -- include the starting person's end-of-line ancestors
;