-- BMD_private_multiples2.sql
/* 
2020-02-15 Tom Holden ve3meo
2020-02-16 rev to add color code to list of mults to fix

Makes private for a person or couple all 
the vital events (BMD) that are multiples of the same type, 
other than the one marked Primary.

Closes by listing the View BMDmultstofix which inccludes
all those persons with multiple BMD-type
events having no Primary event. The user should edit each
person to set one multiple as Primary and either set the others
Private or rerun the script.
*/

-- BMD events
DROP VIEW IF EXISTS BMDevents
;
CREATE TEMP VIEW BMDevents AS
SELECT * FROM EventTable
WHERE EventType IN (1,2,3,4,5,300)
;

-- Count of BMD events per person, couple
DROP VIEW IF EXISTS BMDcount
;
CREATE TEMP VIEW BMDcount AS
SELECT EventType, OwnerID, OwnerType, COUNT() AS Count
FROM BMDevents 
--AND NOT IsPrivate AND NOT IsPrimary
GROUP BY EventType, OwnerID, OwnerType 
;

-- BMD multiples
DROP VIEW IF EXISTS BMDmults
;
CREATE TEMP VIEW BMDmults AS
SELECT * FROM BMDcount
WHERE Count > 1
;

-- BMD primary events
DROP VIEW IF EXISTS BMDprimary
;
CREATE TEMP VIEW BMDprimary AS
SELECT EventID, BMD.EventType, BMD.OwnerID, BMD.OwnerType, COUNT
FROM BMDmults BMD NATURAL JOIN EventTable
WHERE IsPrimary
;

-- BMD mults Not Primary but having a Primary
DROP VIEW IF EXISTS BMDnotprimary
;
CREATE TEMP VIEW BMDnotprimary AS
SELECT E.EventID FROM EventTable E
JOIN BMDprimary USING(EventType, OwnerID, OwnerType)
WHERE NOT IsPrimary
ORDER BY 1
;

--set these non-Primary mults to private 
UPDATE EventTable SET IsPrivate=1 
WHERE EventID IN (SELECT EventID FROM BMDnotprimary) --sets all to private TH2020-02-16 
;

-- at this point there remain BMDmults having no Primary so find them
-- BMDmultsnoprimary
DROP VIEW IF EXISTS BMDmultsnoprimary
;
CREATE TEMP VIEW BMDmultsnoprimary AS
SELECT M.EventType, M.OwnerID, M.OwnerType
FROM BMDmults M
EXCEPT
SELECT P.EventType, P.OwnerID, P.OwnerType
FROM BMDprimary P
;

-- List of mults needing follow up
DROP VIEW IF EXISTS BMDmultstofix
;
CREATE TEMP VIEW BMDmultstofix AS
SELECT BMD.OwnerID AS RIN, N.Given, N.Surname, FT.Name
FROM BMDmultsnoprimary BMD
JOIN NameTable N USING(OwnerID)
JOIN FactTypeTable FT ON BMD.EventType = FT.FactTypeID
WHERE BMD.OwnerType=0 AND N.IsPrimary
UNION
SELECT FAM.FatherID AS RIN, N.Given, N.Surname, FT.Name
FROM BMDmultsnoprimary BMD
JOIN FamilyTable FAM ON BMD.OwnerID=FAM.FamilyID
JOIN NameTable N ON FAM.FatherID = N.OwnerID
JOIN FactTypeTable FT ON BMD.EventType = FT.FactTypeID
WHERE BMD.OwnerType=1 AND N.IsPrimary
UNION
SELECT FAM.MotherID AS RIN, N.Given, N.Surname, FT.Name
FROM BMDmultsnoprimary BMD
JOIN FamilyTable FAM ON BMD.OwnerID=FAM.FamilyID
JOIN NameTable N ON FAM.MotherID = N.OwnerID
JOIN FactTypeTable FT ON BMD.EventType = FT.FactTypeID
WHERE BMD.OwnerType=1 AND N.IsPrimary
;

-- add color code TH2020-02-16
DROP TABLE IF EXISTS BMDmultsToFixClr
;
CREATE TEMP VIEW BMDmultsToFixClr AS
SELECT RIN, Surname, Given, Name, Color
FROM BMDmultstofix
JOIN PersonTable ON RIN=PersonID
;
 
-- Display persons needing attention
SELECT * FROM BMDmultsToFixClr
-- ORDER BY RIN -- already sorted
;
-- END of script