-- Group-PersonsWithTextDates.sql

/* 
2013-09-01 Tom Holden ve3meo
rev 2018-07-07 bug fixed when no other existing group
rev 2024-10-08 update to work with RM10 
Creates and updates a named group of persons
having events with dates in an invalid format, 
i.e., a yellow background in the Date field on the Edit Person screen.
*/
-- Create Named Group if it does not exist 'SQL: Text Dates'
INSERT OR IGNORE INTO TagTable 
VALUES
(
  (SELECT TagID FROM TagTable WHERE TagName LIKE 'SQL: Text Dates')
  ,0
  ,(SELECT IFNULL(MAX(TagValue),0)+1 FROM TagTable)
  ,'SQL: Text Dates'
  ,'SQLite query'
  ,(SELECT julianday('now') - 2415018.5 AS UTCModDate)
)
;

-- Delete all members of the named group
DELETE FROM GroupTable 
WHERE GroupID = 
(SELECT TagValue 
  FROM TagTable 
  WHERE TagName 
  LIKE 'SQL: Text Dates'
)
;

-- Add members to the named group
INSERT INTO GroupTable
SELECT
 null
 ,(SELECT TagValue 
  FROM TagTable 
  WHERE TagName 
  LIKE 'SQL: Text Dates'
   )
 ,PersonID AS StartID
 ,PersonID AS EndID
 ,(SELECT julianday('now') - 2415018.5 AS UTCModDate)
FROM
(
 SELECT DISTINCT OwnerID AS PersonID FROM 
(
-- Individual events
SELECT OwnerID, Date FROM EventTable E
JOIN FactTypeTable F ON  E.EventType = F.FactTypeID
WHERE Date LIKE 'T%' AND F.OwnerType = 0

UNION

-- Family events - husband
SELECT FatherID, Date FROM EventTable E
JOIN FactTypeTable F ON  E.EventType = F.FactTypeID
JOIN FamilyTable Fam ON E.OwnerID = Fam.FamilyID
WHERE Date LIKE 'T%' AND F.OwnerType = 1

UNION

-- Family events - wife
SELECT MotherID, Date FROM EventTable E
JOIN FactTypeTable F ON  E.EventType = F.FactTypeID
JOIN FamilyTable Fam ON E.OwnerID = Fam.FamilyID
WHERE Date LIKE 'T%' AND F.OwnerType = 1

UNION

-- Alternate name events
SELECT OwnerID, Date FROM NameTable N
WHERE Date LIKE 'T%' AND NOT +IsPrimary

)
);