/* AncestorsGroup.sql
2011-11-23 by ve3meo
Updates the membership in a pre-Named Group in a selected RM4 database.
Group must be named with the inclusion of the terms "ancestors" and "#RIN" (without the quotes),
where RIN is the number of the base or start person. On running the query, it prompts for the RIN (w/o the #)
in order to find the Group it is to operate on. It then finds the RINs of up to 12 generations of
ancestors and replaces the existing members of the group with this list.

The script uses a series of queries which use parameters such as the starting RIN and the GroupID
stored in a temporary table established at the beginning.
2011-11-27 error corrected using LabelID where LabelValue should have been
*/

/* 
GroupParmTable stores parameters used by this script, including the RIN of the starting person, the ID
and name of the named group having a name containing 'Ancestors' and #StartRIN , e.g., "Tom's ancestors #269".
Only the GroupID and StartRIN are used subsequently.
*/
DROP TABLE IF EXISTS GroupParmTable
;
CREATE TEMP TABLE IF NOT EXISTS GroupParmTable
AS
SELECT @StartRIN AS StartRIN, LabelValue AS GroupID, LabelName COLLATE NOCASE FROM LabelTable WHERE LabelName LIKE '%Ancestors%#' || @StartRIN || '%'
;

-- AncestorsTable is a temp table storing results from the Ancestors query
DROP TABLE IF EXISTS AncestorsTable
;
CREATE TEMP TABLE IF NOT EXISTS AncestorsTable
AS
SELECT Fam1.PersonID AS Gen1, Fam2.PersonID AS Gen2, Fam3.PersonID AS Gen3, Fam4.PersonID AS Gen4, Fam5.PersonID AS Gen5, Fam6.PersonID AS Gen6, Fam7.PersonID AS Gen7, Fam8.PersonID AS Gen8, Fam9.PersonID AS Gen9, Fam10.PersonID AS Gen10, Fam11.PersonID AS Gen11, Fam12.PersonID AS Gen12, Fam12.FatherID AS Male13, Fam12.MotherID AS Female13
FROM
(
SELECT PersonID, Ch1.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch1 ON PersonID=Ch1.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch1.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch1 ON PersonID=Ch1.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam1
LEFT JOIN
(
SELECT PersonID, Ch2.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch2 ON PersonID=Ch2.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch2.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch2 ON PersonID=Ch2.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam2 
ON Fam1.ParentID=Fam2.PersonID
LEFT JOIN
(
SELECT PersonID, Ch3.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch3 ON PersonID=Ch3.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch3.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch3 ON PersonID=Ch3.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam3
ON Fam2.ParentID=Fam3.PersonID
LEFT JOIN
(
SELECT PersonID, Ch4.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch4 ON PersonID=Ch4.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch4.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch4 ON PersonID=Ch4.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam4
ON Fam3.ParentID=Fam4.PersonID
LEFT JOIN
(
SELECT PersonID, Ch5.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch5 ON PersonID=Ch5.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch5.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch5 ON PersonID=Ch5.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam5
ON Fam4.ParentID=Fam5.PersonID
LEFT JOIN
(
SELECT PersonID, Ch6.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch6 ON PersonID=Ch6.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch6.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch6 ON PersonID=Ch6.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam6
ON Fam5.ParentID=Fam6.PersonID
LEFT JOIN
(
SELECT PersonID, Ch7.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch7 ON PersonID=Ch7.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch7.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch7 ON PersonID=Ch7.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam7
ON Fam6.ParentID=Fam7.PersonID
LEFT JOIN
(
SELECT PersonID, Ch8.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch8 ON PersonID=Ch8.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch8.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch8 ON PersonID=Ch8.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam8
ON Fam7.ParentID=Fam8.PersonID
LEFT JOIN
(
SELECT PersonID, Ch9.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch9 ON PersonID=Ch9.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch9.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch9 ON PersonID=Ch9.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam9
ON Fam8.ParentID=Fam9.PersonID
LEFT JOIN
(
SELECT PersonID, Ch10.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch10 ON PersonID=Ch10.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch10.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch10 ON PersonID=Ch10.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam10
ON Fam9.ParentID=Fam10.PersonID
LEFT JOIN
(
SELECT PersonID, Ch11.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch11 ON PersonID=Ch11.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch11.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch11 ON PersonID=Ch11.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam11
ON Fam10.ParentID=Fam11.PersonID
LEFT JOIN
(
SELECT PersonID, Ch12.ChildID, FatherID, MotherID FROM PersonTable LEFT JOIN ChildTable AS Ch12 ON PersonID=Ch12.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam12
ON Fam11.ParentID=Fam12.PersonID

WHERE
CASE 
WHEN (SELECT GroupID FROM GroupParmTable) NOT LIKE '' THEN Gen1 = (SELECT StartRIN FROM GroupParmTable)
ELSE 1
END
;

-- delete all persons from the named group whose id is store in the temp table set up at the start
DELETE FROM GroupTable WHERE GroupID =
(
SELECT GroupID FROM GroupParmTable
); 
-- Build list of ancestors in group
INSERT INTO GroupTable (GroupID, StartID)
SELECT GroupID, PersonID
FROM
(
SELECT GroupID FROM GroupParmTable
)
LEFT JOIN
-- SELECT * FROM AncestorsTable
(
SELECT DISTINCT Gen1 AS PersonID FROM AncestorsTable WHERE PersonID>0
UNION
SELECT DISTINCT Gen2 AS PersonID FROM AncestorsTable WHERE PersonID>0
UNION
SELECT DISTINCT Gen3 AS PersonID FROM AncestorsTable WHERE PersonID>0
UNION
SELECT DISTINCT Gen4 AS PersonID FROM AncestorsTable WHERE PersonID>0
UNION
SELECT DISTINCT Gen5 AS PersonID FROM AncestorsTable WHERE PersonID>0
UNION
SELECT DISTINCT Gen6 AS PersonID FROM AncestorsTable WHERE PersonID>0
UNION
SELECT DISTINCT Gen7 AS PersonID FROM AncestorsTable WHERE PersonID>0
UNION
SELECT DISTINCT Gen8 AS PersonID FROM AncestorsTable WHERE PersonID>0
UNION
SELECT DISTINCT Gen9 AS PersonID FROM AncestorsTable WHERE PersonID>0
UNION
SELECT DISTINCT Gen10 AS PersonID FROM AncestorsTable WHERE PersonID>0
UNION
SELECT DISTINCT Gen11 AS PersonID FROM AncestorsTable WHERE PersonID>0
UNION
SELECT DISTINCT Gen12 AS PersonID FROM AncestorsTable WHERE PersonID>0
UNION
SELECT DISTINCT Male13 AS PersonID FROM AncestorsTable WHERE PersonID>0
UNION
SELECT DISTINCT Female13 AS PersonID FROM AncestorsTable WHERE PersonID>0
)
;
-- Add EndID matching StartID - easy way out instead of ranges
UPDATE GroupTable SET EndID = StartID 
WHERE GroupID LIKE
(
SELECT GroupID FROM GroupParmTable
)
;
-- END OF SCRIPT