/* CensusNeededGroup.sql
2011-11-26 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 "Census needed" and "#CensusYear" and "Jurisdiction" (without the quotes),
where CensusYear is the year of the census, Jurisdiction is the country, or province/state, or municipality. 
On running the query, it prompts for the CensusYear (w/o the #) and Jurisdiction
in order to find the Group it is to operate on. It then finds the RINs of persons with no individual census fact
but with birth and death years spanning the CensusYear and some individual fact in a place containing the jurisdiction
and replaces the existing members of the group with this list.

Needed enhancement is the incorporation of the family census and family facts.

The script uses a series of queries which use parameters such as the CensusYear, Jurisdiction and the GroupID
stored in a temporary table established at the beginning.
2011-11-27 corrected error 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 @CensusYear AS CensusYear, @Jurisdiction AS Jurisdiction, LabelValue AS GroupID, LabelName COLLATE NOCASE FROM LabelTable 
WHERE LabelName LIKE '%Census needed%#' || @CensusYear || '%' || @Jurisdiction || '%'
;

-- delete all persons from the named group whose id is stored in the temp table set up at the start
DELETE FROM GroupTable WHERE GroupID =
(
SELECT GroupID FROM GroupParmTable
); 

-- Build list of persons in group
INSERT INTO GroupTable (GroupID, StartID)
SELECT GroupID, PersonID
FROM
(
SELECT GroupID FROM GroupParmTable
)
LEFT JOIN
--SELECT * FROM 
(
-- Persons with any individual fact in Jursidiction with BirthYear and DeathYear spanning the CensusYear
SELECT DISTINCT OwnerID AS PersonID FROM EventTable
INNER JOIN NameTable USING (OwnerID)
INNER JOIN PlaceTable USING (PlaceID)
WHERE EventTable.OwnerType=0 
AND PlaceTable.Name COLLATE NOCASE LIKE '%' || (SELECT Jurisdiction FROM GroupParmTable) || '%'  
AND +NameTable.IsPrimary 
AND CASE 
    WHEN NameTable.BirthYear > 0 AND NameTable.DeathYear > 0 
      THEN NameTable.BirthYear <= (SELECT CensusYear FROM GroupParmTable) AND NameTable.DeathYear > (SELECT CensusYear FROM GroupParmTable)
    WHEN NameTable.BirthYear = 0 AND NameTable.DeathYear > 0     
      THEN NameTable.DeathYear > (SELECT CensusYear FROM GroupParmTable) AND NameTable.DeathYear < (SELECT CensusYear FROM GroupParmTable) + 75
    WHEN NameTable.BirthYear > 0 AND NameTable.DeathYear = 0 
      THEN NameTable.BirthYear <= (SELECT CensusYear FROM GroupParmTable) AND NameTable.BirthYear > (SELECT CensusYear FROM GroupParmTable) - 75      
    ELSE 0    
    END


EXCEPT

-- Persons with individual census in the target CensusYear
SELECT DISTINCT PersonID FROM PersonTable, EventTable 
WHERE EventType = 18 
AND PersonID=EventTable.OwnerID
AND EventTable.Date LIKE 'D__' || (SELECT CensusYear FROM GroupParmTable) || '%'
)
;

-- Add EndID matching StartID - easy way out instead of ranges
UPDATE GroupTable SET EndID = StartID 
WHERE GroupID LIKE
(
SELECT GroupID FROM GroupParmTable
)
;
/*
-- Unmark (list of manual unmarks, not advisable for a parameterised query)
DELETE FROM GroupTable 
WHERE GroupID LIKE
(
SELECT GroupID FROM GroupParmTable
)
AND StartID IN (78,829) -- list of RINs to unmark, separated by commas
;

-- Mark (list of manual marks, not advisable for a parameterised query)
INSERT INTO GroupTable (GroupID, StartID, EndID)
SELECT GroupID, MarkList.*, MarkList.*  FROM GroupParmTable
LEFT JOIN 
(
SELECT 78       -- RIN to be marked
UNION           -- required for each successive RIN to be marked
SELECT 829      -- RIN to be marked
--UNION         -- uncomment for next RIN to be added
)
AS MarkList
;
*/
-- END OF SCRIPT