-- Source Template List with Citation Details
-- created  by romermb 14 Feb 2010
-- modified by romermb 15 Feb 2010 to override SQLite query optimization routine treatment of 
--             IsPrimary field in order to produce faster run time,
--             to add Template Type field
-- 2010-06-01 rev by ve3meo to replace IF construct (not supported without a load extension) by CASE
-- 2011-11-03 rev by ve3meo corrected misreporting of RIN and Person having an Alternate Name fact source
-- 2011-11-04 ve3meo further corrected reporting of false citations from non-existing Alternate Name facts
 
-- Person Citations
SELECT   SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name',
         CASE WHEN SourceTemplateTable.TemplateID < 10000 
           THEN 'Built-In' 
           ELSE 'User-Defined'
         END AS 'Template Type',
         SourceTable.Name COLLATE NOCASE AS 'Source Name',
         'Person' AS 'Citation Type',
         NULL AS 'Fact Type',
         NULL AS MRIN,
         NameTable.OwnerID AS 'RIN 1',
         NameTable.Surname COLLATE NOCASE AS 'Surname 1',
         NameTable.Suffix COLLATE NOCASE AS 'Suffix 1',
         NameTable.Prefix COLLATE NOCASE AS 'Prefix 1',
         NameTable.Given COLLATE NOCASE AS 'Given 1',
         NameTable.Nickname COLLATE NOCASE AS 'Nickname 1',
         NULL AS 'RIN 2',
         NULL AS 'Surname 2',
         NULL AS 'Suffix 2',
         NULL AS 'Prefix 2',
         NULL AS 'Given 2',
         NULL AS 'Nickname 2',
         COUNT(1) AS Records
FROM     SourceTemplateTable
         INNER JOIN SourceTable ON
         SourceTemplateTable.TemplateID = SourceTable.TemplateID
         INNER JOIN CitationTable ON
         SourceTable.SourceID = CitationTable.SourceID
         INNER JOIN NameTable ON
         CitationTable.OwnerID = NameTable.OwnerID
WHERE    CitationTable.OwnerType = 0 AND +NameTable.IsPrimary = 1
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
 
UNION ALL
 
-- Family Citations
SELECT   SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name',
         CASE WHEN SourceTemplateTable.TemplateID < 10000 
           THEN 'Built-In' 
           ELSE 'User-Defined'
         END AS 'Template Type',
         SourceTable.Name COLLATE NOCASE AS 'Source Name',
         'Family' AS 'Citation Type',
         NULL AS 'Fact Type',
         FamilyTable.FamilyID AS MRIN,
         NameTable1.OwnerID AS 'RIN 1',
         NameTable1.Surname COLLATE NOCASE AS 'Surname 1',
         NameTable1.Suffix COLLATE NOCASE AS 'Suffix 1',
         NameTable1.Prefix COLLATE NOCASE AS 'Prefix 1',
         NameTable1.Given COLLATE NOCASE AS 'Given 1',
         NameTable1.Nickname COLLATE NOCASE AS 'Nickname 1',
         NameTable2.OwnerID AS 'RIN 2',
         NameTable2.Surname COLLATE NOCASE AS 'Surname 2',
         NameTable2.Suffix COLLATE NOCASE AS 'Suffix 2',
         NameTable2.Prefix COLLATE NOCASE AS 'Prefix 2',
         NameTable2.Given COLLATE NOCASE AS 'Given 2',
         NameTable2.Nickname COLLATE NOCASE AS 'Nickname 2',
         COUNT(1) AS Records
FROM     SourceTemplateTable
         INNER JOIN SourceTable ON
         SourceTemplateTable.TemplateID = SourceTable.TemplateID
         INNER JOIN CitationTable ON
         SourceTable.SourceID = CitationTable.SourceID
         INNER JOIN FamilyTable ON
         CitationTable.OwnerID = FamilyTable.FamilyID
         INNER JOIN NameTable AS NameTable1 ON
         FamilyTable.FatherID = NameTable1.OwnerID
         INNER JOIN NameTable AS NameTable2 ON
         FamilyTable.MotherID = NameTable2.OwnerID
WHERE    CitationTable.OwnerType = 1 AND +NameTable1.IsPrimary = 1 AND
         +NameTable2.IsPrimary = 1
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
 
UNION ALL
 
-- Person Fact Citations
SELECT   SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name',
         CASE WHEN SourceTemplateTable.TemplateID < 10000 
           THEN 'Built-In' 
           ELSE 'User-Defined'
         END AS 'Template Type',
         SourceTable.Name COLLATE NOCASE AS 'Source Name',
         'Fact - Person' AS 'Citation Type',
         FactTypeTable.Name COLLATE NOCASE AS 'Fact Type',
         NULL AS MRIN,
         NameTable.OwnerID AS 'RIN 1',
         NameTable.Surname COLLATE NOCASE AS 'Surname 1',
         NameTable.Suffix COLLATE NOCASE AS 'Suffix 1',
         NameTable.Prefix COLLATE NOCASE AS 'Prefix 1',
         NameTable.Given COLLATE NOCASE AS 'Given 1',
         NameTable.Nickname COLLATE NOCASE AS 'Nickname 1',
         NULL AS 'RIN 2',
         NULL AS 'Surname 2',
         NULL AS 'Suffix 2',
         NULL AS 'Prefix 2',
         NULL AS 'Given 2',
         NULL AS 'Nickname 2',
         COUNT(1) AS Records
FROM     SourceTemplateTable
         INNER JOIN SourceTable ON
         SourceTemplateTable.TemplateID = SourceTable.TemplateID
         INNER JOIN CitationTable ON
         SourceTable.SourceID = CitationTable.SourceID
         INNER JOIN EventTable ON
         CitationTable.OwnerID = EventTable.EventID
         INNER JOIN FactTypeTable ON
         EventTable.EventType = FactTypeTable.FactTypeID
         INNER JOIN NameTable ON
         EventTable.OwnerID = NameTable.OwnerID
WHERE    CitationTable.OwnerType = 2 AND +NameTable.IsPrimary = 1 AND
         EventTable.OwnerType = 0
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
 
UNION ALL
 
-- Family Fact Citations
SELECT   SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name',
         CASE WHEN SourceTemplateTable.TemplateID < 10000 
           THEN 'Built-In' 
           ELSE 'User-Defined'
         END AS 'Template Type',
         SourceTable.Name COLLATE NOCASE AS 'Source Name',
         'Fact - Family' AS 'Citation Type',
         FactTypeTable.Name COLLATE NOCASE AS 'Fact Type',
         FamilyTable.FamilyID AS MRIN,
         NameTable1.OwnerID AS 'RIN 1',
         NameTable1.Surname COLLATE NOCASE AS 'Surname 1',
         NameTable1.Suffix COLLATE NOCASE AS 'Suffix 1',
         NameTable1.Prefix COLLATE NOCASE AS 'Prefix 1',
         NameTable1.Given COLLATE NOCASE AS 'Given 1',
         NameTable1.Nickname COLLATE NOCASE AS 'Nickname 1',
         NameTable2.OwnerID AS 'RIN 2',
         NameTable2.Surname COLLATE NOCASE AS 'Surname 2',
         NameTable2.Suffix COLLATE NOCASE AS 'Suffix 2',
         NameTable2.Prefix COLLATE NOCASE AS 'Prefix 2',
         NameTable2.Given COLLATE NOCASE AS 'Given 2',
         NameTable2.Nickname COLLATE NOCASE AS 'Nickname 2',
         COUNT(1) AS Records
FROM     SourceTemplateTable
         INNER JOIN SourceTable ON
         SourceTemplateTable.TemplateID = SourceTable.TemplateID
         INNER JOIN CitationTable ON
         SourceTable.SourceID = CitationTable.SourceID
         INNER JOIN EventTable ON
         CitationTable.OwnerID = EventTable.EventID
         INNER JOIN FactTypeTable ON
         EventTable.EventType = FactTypeTable.FactTypeID
         INNER JOIN FamilyTable ON
         EventTable.OwnerID = FamilyTable.FamilyID
         INNER JOIN NameTable AS NameTable1 ON
         FamilyTable.FatherID = NameTable1.OwnerID
         INNER JOIN NameTable AS NameTable2 ON
         FamilyTable.MotherID = NameTable2.OwnerID
WHERE    CitationTable.OwnerType = 2 AND +NameTable1.IsPrimary = 1 AND
         +NameTable2.IsPrimary = 1 AND EventTable.OwnerType = 1
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
 
UNION ALL
 
-- Alternate Name Fact Citations
SELECT   SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name',
         CASE WHEN SourceTemplateTable.TemplateID < 10000 
           THEN 'Built-In' 
           ELSE 'User-Defined'
         END AS 'Template Type',
         SourceTable.Name COLLATE NOCASE AS 'Source Name',
         'Fact - Alt Name' AS 'Citation Type',
         'Alt Name' AS 'Fact Type',
         NULL AS MRIN,
         NameTable2.OwnerID AS 'RIN 1',
         NameTable2.Surname COLLATE NOCASE AS 'Surname 1',
         NameTable2.Suffix COLLATE NOCASE AS 'Suffix 1',
         NameTable2.Prefix COLLATE NOCASE AS 'Prefix 1',
         NameTable2.Given COLLATE NOCASE AS 'Given 1',
         NameTable2.Nickname COLLATE NOCASE AS 'Nickname 1',
         NULL AS 'RIN 2',
         NULL AS 'Surname 2',
         NULL AS 'Suffix 2',
         NULL AS 'Prefix 2',
         NULL AS 'Given 2',
         NULL AS 'Nickname 2',
         COUNT(1) AS Records
FROM     SourceTemplateTable
         INNER JOIN SourceTable ON
         SourceTemplateTable.TemplateID = SourceTable.TemplateID
         INNER JOIN CitationTable ON
         SourceTable.SourceID = CitationTable.SourceID
         INNER JOIN NameTable AS NameTable1 ON
         CitationTable.OwnerID = NameTable1.NameID
         INNER JOIN NameTable AS NameTable2 ON
         NameTable1.OwnerID = NameTable2.OwnerID
WHERE    CitationTable.OwnerType = 7 AND +NameTable1.IsPrimary = 0 AND +NameTable2.IsPrimary = 1
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
 
ORDER BY 1, 3, 7, 13, 5
