-- WitnessOrderByRelationship+Name.sql
/*
2016-07-29 Tom Holden ve3meo

The WitnessOrder field in WitnessTable controls the order of output in the display
of witnesses and in narrative reports but RootsMagic 7 provides no control for it.
This script modifies the WitnessOrder values so that they are sorted:
1. Primary: Consanguinity of witness to reference person used by the RootsMagic 
SET RELATIONSHIPS function
2. Secondary: Alphabetical order of the primary name of each person, 
including those not in a tree.

Clearing the relationships prior to execution results in a purely alphabetical sort.

It sets negative values in WitnessOrder so inspection of the WitnessTable readily
reveals those set by the script. 
*/

-- count the number of witnesses for each shared event
DROP VIEW IF EXISTS vWitnessCount
;
CREATE TEMP VIEW vWitnessCount AS
SELECT EventID, COUNT(EventID) AS Ctr FROM WitnessTable GROUP BY EventID
;

-- for shared events with multiple witnesses, list them in sorted order, including 
-- those named as not in the database
DROP VIEW IF EXISTS vWitnessSort
;
CREATE TEMP VIEW vWitnessSort AS
SELECT
 W.EventID
 , W.WitnessID
 , IFNULL(P.Relate1,9999) + IFNULL(P.Relate2,0) AS Consanguinity
 , IFNULL(N.Surname, W.Surname) AS Surname -- includes names not in database
 , IFNULL(N.Given, W.Given) AS Given
FROM WitnessTable W
LEFT JOIN PersonTable P USING(PersonID)
LEFT JOIN NameTable N ON W.PersonID = N.OwnerID AND N.IsPrimary
WHERE W.EventID IN
(SELECT EventID FROM vWitnessCount WHERE Ctr > 1) -- must have multiple witnesses
ORDER BY EventID, Consanguinity, Surname, Given
; 

--SELECT * FROM vWitnessSort
;

-- put the ordered list into a table to establish record numbers to be used for WitnessOrder
DROP TABLE IF EXISTS xWitnessOrder
;
CREATE TEMP TABLE xWitnessOrder
(WitnessOrderID INTEGER PRIMARY KEY, EventID INTEGER, WitnessID INTEGER, Consanguinity INTEGER, Surname TEXT, Given TEXT, WitnessOrder INTEGER)
;
INSERT INTO xWitnessOrder SELECT null,*, null FROM vWitnessSort
;

-- get the highest record number for each event from the ordered list as  reference
DROP VIEW IF EXISTS vWitnessRef
;
CREATE TEMP VIEW vWitnessRef AS
SELECT EventID, WitnessOrderID FROM xWitnessOrder GROUP BY EventID
;

-- calculate the WitnessOrder from the ordered list record numbers and reference numbers
DROP VIEW IF EXISTS vWitnessOrder
;
CREATE TEMP VIEW vWitnessOrder AS
SELECT WO.WitnessID, WO.WitnessOrderID - WR.WitnessOrderID - 1 AS WitnessOrder
FROM xWitnessOrder WO
JOIN vWitnessRef WR USING(EventID)
;

-- revise the WitnessOrder in the WitnessTable for those witnesses 
UPDATE WitnessTable 
SET WitnessOrder =
 (SELECT WitnessOrder FROM vWitnessOrder WO WHERE WitnessTable.WitnessID = WO.WitnessID)
WHERE WitnessID IN (SELECT WitnessID FROM vWitnessOrder ORDER BY WitnessID)
; 

-- display the affected results
SELECT * FROM WitnessTable WHERE WitnessOrder < 0
ORDER BY EventID, WitnessOrder
;