-- AllCitations+Dupes.sql
-- 2010-07-07 by ve3meo
-- based on AllCitations.sql, using a similar procedure to build a temporary
--  table of all citations from which a subsequent query in the script
--  can display specific results - in this case a summary report
--  showing all sources cited against a person sorted by the numbers
--  of duplicate citations.
-- N.B. some SQLite managers may fail to display any results; it will be necessary to 
--  execute the last SELECT as a sub-query using whatever technique the manager
--  provides, e.g., highlighting the command to be executed while the temporary 
--  table still exists.
-- To Do - maybe use eventtable.SortDate as a sorting criterion instead of Date
-- 2011-11-04 ve3meo corrections for spouse, and family fact citations and multiples due Alt Name
-- 2012-02-23 ve3meo corrected Couple Event Citations for 200:1 speed improvement in that query
--
-- BEGIN
-- all Personal citations for Individual
DROP TABLE IF EXISTS tmpCitations;
CREATE TEMP TABLE tmpCitations AS
SELECT  c.CITATIONID AS CitID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq, n.surname COLLATE NOCASE AS Surname, n.suffix COLLATE NOCASE AS Sfx, n.prefix COLLATE NOCASE AS Pfx, n.given COLLATE NOCASE AS Givens, n.birthyear AS Born, 
  n.deathyear AS Died, Null AS EvtID, 'Personal' AS Citer, Null AS EvtDate,
  s.NAME COLLATE NOCASE AS Source, s.refnumber AS SrcREFN, s.actualtext AS SrcTxt, s.comments AS SrcComment, s.fields AS SrcFlds,
  c.refnumber AS CitREFN, QUOTE(c.actualtext) AS CitTxt, QUOTE(c.comments) AS CitComment, c.fields AS CitFlds
FROM  citationtable c 
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid 
  LEFT OUTER JOIN persontable p ON c.ownerid=p.personid 
  LEFT OUTER JOIN  nametable n ON p.personid=n.ownerid
WHERE  c.ownertype=0 AND +n.IsPrimary=1
;
INSERT INTO tmpCitations
-- all Fact citations for Individual
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname COLLATE NOCASE , n.suffix COLLATE NOCASE , n.prefix COLLATE NOCASE , n.given COLLATE NOCASE , n.birthyear, 
  n.deathyear, e.EventID AS EvtID, f.NAME AS Citer, e.Date AS EvtDate, 
  s.NAME COLLATE NOCASE , s.refnumber, s.actualtext, s.comments, s.fields AS SrcFlds,
  c.refnumber, c.actualtext, c.comments, c.fields AS CitFlds
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
  LEFT OUTER JOIN eventtable e ON c.ownerid=e.eventid
  LEFT OUTER JOIN persontable p ON e.ownerid=p.personid
  LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
  LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
WHERE c.ownertype=2 AND e.ownertype=0 AND f.ownertype=0 AND +n.IsPrimary=1 
 ;
INSERT INTO tmpCitations
-- all Spouse citations for Father|Husband|Partner 1
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear, 
  n.deathyear, NULL AS EvtID, '(Spouse)' AS 'Name', NULL AS EvtDate,
  s.NAME, s.refnumber, s.actualtext, s.comments, s.fields AS SrcFlds,
  c.refnumber, c.actualtext, c.comments, c.fields AS CitFlds
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid 
  LEFT OUTER JOIN familytable fm ON c.ownerid=fm.FamilyID
  LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
  LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
--  LEFT OUTER JOIN eventtable e ON e.ownerid=fm.familyid
--  LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
WHERE c.ownertype=1 -- AND e.ownertype=1 AND f.ownertype=1 
AND +n.IsPrimary=1
;

INSERT INTO tmpCitations
-- all Couple Event citations for Father|Husband|Partner 1
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear, 
  n.deathyear, e.EventID AS EvtID, f.NAME, e.Date AS EvtDate,
  s.NAME, s.refnumber, s.actualtext, s.comments, s.fields AS SrcFlds,
  c.refnumber, c.actualtext, c.comments, c.fields AS CitFlds
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid 
  LEFT OUTER JOIN eventtable e ON c.ownerid=e.eventid  
  LEFT OUTER JOIN familytable fm ON e.ownerid=fm.familyid
  LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
  LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
  LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
WHERE c.ownertype=2 AND e.ownertype=1 AND f.ownertype=1 AND +n.IsPrimary=1
;

INSERT INTO tmpCitations
-- Citations for Alternate Names 
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, NOT n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear, 
  n.deathyear, Null AS EvtID, 'Alternate Name' AS Citer, Null AS EvtDate, 
  s.NAME AS Source, s.refnumber, s.actualtext, s.comments, s.fields AS SrcFlds,
  c.refnumber, c.actualtext, c.comments, c.fields AS CitFlds 
FROM  citationtable c 
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid 
  LEFT OUTER JOIN  nametable n ON n.nameid=c.ownerid
WHERE  c.ownertype=7 AND +n.IsPrimary=0
;

-- Display summary results, in descending order of duplications
SELECT RIN, Surname || ' ' || Sfx || ', ' || Pfx || ' ' || Givens || ' (b' || Born || '-d' || Died || ')' AS Person, Citer, EvtDate, COUNT(1)-1 AS Dupes, Source FROM tmpcitations
WHERE uniq=1 OR uniq ISNULL 
GROUP BY RIN, EvtID, Citer , SOURCE, SrcREFN, SrcTxt, SrcComment, SrcFlds, CitREFN, CitTxt, CitComment, CitFlds 
ORDER BY Dupes DESC, RIN, EvtDate
; 
-- END