-- 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-01 rev by ve3meo to concatenate names for easier viewing   SourceTemplateListWithCitationDetails2.sql
-- 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 'Template',
         CASE WHEN SourceTemplateTable.TemplateID < 10000 
           THEN 'OEM' 
           ELSE 'USR'
         END AS 'Type',
         SourceTable.Name COLLATE NOCASE AS 'Source Name',
         'Person' AS 'Citation Type',
         NULL AS 'Fact Type',
         NULL AS MRIN,
         NameTable.OwnerID AS 'RIN 1',
         UPPER(NameTable.Surname) COLLATE Nocase ||
          CASE NameTable.Suffix COLLATE NoCase
           WHEN '' THEN ''
           ELSE ' ' || NameTable.Suffix COLLATE Nocase
          END
          ||', '||  
          NameTable.Prefix COLLATE Nocase ||' '||  
          NameTable.Given COLLATE Nocase ||
          CASE
           WHEN NameTable.Nickname NOT LIKE ''
            THEN ' "'|| NameTable.Nickname ||'"'
           ELSE ''
          END 
          AS 'Person 1', 
         NULL AS 'RIN 2',
         NULL AS 'Person 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 "Template", "Type", "Source Name", "Citation Type", "Fact Type", "MRIN", "RIN 1", "Person 1", "RIN 2", "Person 2"
 
UNION ALL
 
-- Family Citations
SELECT   SourceTemplateTable.Name COLLATE NOCASE AS 'Template',
         CASE WHEN SourceTemplateTable.TemplateID < 10000 
           THEN 'OEM' 
           ELSE 'USR'
         END AS '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',
         UPPER(NameTable1.Surname) COLLATE Nocase ||
          CASE NameTable1.Suffix COLLATE NoCase
           WHEN '' THEN ''
           ELSE ' ' || NameTable1.Suffix COLLATE Nocase
          END
          ||', '||  
          NameTable1.Prefix COLLATE Nocase ||' '||  
          NameTable1.Given COLLATE Nocase ||
          CASE
           WHEN NameTable1.Nickname NOT LIKE ''
            THEN ' "'|| NameTable1.Nickname ||'"'
           ELSE ''
          END 
          AS 'Person 1', 
         NameTable2.OwnerID AS 'RIN 2',
         UPPER(NameTable2.Surname) COLLATE Nocase ||
          CASE NameTable2.Suffix COLLATE NoCase
           WHEN '' THEN ''
           ELSE ' ' || NameTable2.Suffix COLLATE Nocase
          END
          ||', '||  
          NameTable2.Prefix COLLATE Nocase ||' '||  
          NameTable2.Given COLLATE Nocase ||
          CASE
           WHEN NameTable2.Nickname NOT LIKE ''
            THEN ' "'|| NameTable2.Nickname ||'"'
           ELSE ''
          END 
          AS 'Person 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 "Template", "Type", "Source Name", "Citation Type", "Fact Type", "MRIN", "RIN 1", "Person 1", "RIN 2", "Person 2"
 
UNION ALL
 
-- Person Fact Citations
SELECT   SourceTemplateTable.Name COLLATE NOCASE AS 'Template',
         CASE WHEN SourceTemplateTable.TemplateID < 10000 
           THEN 'OEM' 
           ELSE 'USR'
         END AS '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',
         UPPER(NameTable.Surname) COLLATE Nocase ||
          CASE NameTable.Suffix COLLATE NoCase
           WHEN '' THEN ''
           ELSE ' ' || NameTable.Suffix COLLATE Nocase
          END
          ||', '||  
          NameTable.Prefix COLLATE Nocase ||' '||  
          NameTable.Given COLLATE Nocase ||
          CASE
           WHEN NameTable.Nickname NOT LIKE ''
            THEN ' "'|| NameTable.Nickname ||'"'
           ELSE ''
          END 
          AS 'Person 1', 
         NULL AS 'RIN 2',
         NULL AS 'Person 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 "Template", "Type", "Source Name", "Citation Type", "Fact Type", "MRIN", "RIN 1", "Person 1", "RIN 2", "Person 2"
 
UNION ALL
 
-- Family Fact Citations
SELECT   SourceTemplateTable.Name COLLATE NOCASE AS 'Template',
         CASE WHEN SourceTemplateTable.TemplateID < 10000 
           THEN 'OEM' 
           ELSE 'USR'
         END AS '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',
         UPPER(NameTable1.Surname) COLLATE Nocase ||
          CASE NameTable1.Suffix COLLATE NoCase
           WHEN '' THEN ''
           ELSE ' ' || NameTable1.Suffix COLLATE Nocase
          END
          ||', '||  
          NameTable1.Prefix COLLATE Nocase ||' '||  
          NameTable1.Given COLLATE Nocase ||
          CASE
           WHEN NameTable1.Nickname NOT LIKE ''
            THEN ' "'|| NameTable1.Nickname ||'"'
           ELSE ''
          END 
          AS 'Person 1', 
         NameTable2.OwnerID AS 'RIN 2',
         UPPER(NameTable2.Surname) COLLATE Nocase ||
          CASE NameTable2.Suffix COLLATE NoCase
           WHEN '' THEN ''
           ELSE ' ' || NameTable2.Suffix COLLATE Nocase
          END
          ||', '||  
          NameTable2.Prefix COLLATE Nocase ||' '||  
          NameTable2.Given COLLATE Nocase ||
          CASE
           WHEN NameTable2.Nickname NOT LIKE ''
            THEN ' "'|| NameTable2.Nickname ||'"'
           ELSE ''
          END 
          AS 'Person 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 "Template", "Type", "Source Name", "Citation Type", "Fact Type", "MRIN", "RIN 1", "Person 1", "RIN 2", "Person 2"
 
UNION ALL
 
-- Alternate Name Fact Citations
SELECT   SourceTemplateTable.Name COLLATE NOCASE AS 'Template',
         CASE WHEN SourceTemplateTable.TemplateID < 10000 
           THEN 'OEM' 
           ELSE 'USR'
         END AS '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',
         UPPER(NameTable2.Surname) COLLATE Nocase ||
          CASE NameTable2.Suffix COLLATE NoCase
           WHEN '' THEN ''
           ELSE ' ' || NameTable2.Suffix COLLATE Nocase
          END
          ||', '||  
          NameTable2.Prefix COLLATE Nocase ||' '||  
          NameTable2.Given COLLATE Nocase ||
          CASE
           WHEN NameTable2.Nickname NOT LIKE ''
            THEN ' "'|| NameTable2.Nickname ||'"'
           ELSE ''
          END 
          AS 'Person 1', 
         NULL AS 'RIN 2',
         NULL AS 'Person 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 "Template", "Type", "Source Name", "Citation Type", "Fact Type", "MRIN", "RIN 1", "Person 1", "RIN 2", "Person 2"
 
ORDER BY "Template", "Source Name", "RIN 1", "RIN 2", "Fact Type"