using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace RMtrix.Class
{
    class dBQueries
    {
        public static string dBPropertiesQuery
        {
            get
            {
                string commandText = ""
+ "ATTACH '" //+ System.IO.Path.GetFullPath("Empty.rmgc") + "' AS 'CTL'; "
+ System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + "\\Empty.rmgc"
+ "' AS 'CTL'; " 
+ "  "
+ "DROP TABLE IF EXISTS VariableTable; "
+ " "
+ "CREATE TABLE IF NOT EXISTS VariableTable "
+ "( "
+ "Variable TEXT, "
+ "Value INTEGER, "
+ "Remark TEXT "
+ "); "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT 'Version', "
+ " (SELECT CAST(substr(datarec,58,4) AS TEXT) FROM ConfigTable WHERE RecType=1), "
+ " 'vs Control version ' || (SELECT substr(datarec,58,4) FROM CTL.ConfigTable WHERE RecType=1) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT 'People', COUNT(1), 'all records in PersonTable' "
+ "FROM PersonTable; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT '- Nameless People', COUNT(1), 'no record in NameTable for that RIN' "
+ "FROM PersonTable P  "
+ "LEFT JOIN nametable N "
+ "ON P.PersonID = N.OwnerID  AND n.ISPRIMARY "
+ "WHERE N.nameid ISNULL "
+ "; "
+ " "
+ "DROP TABLE IF EXISTS namesort; "
+ "CREATE TABLE IF NOT EXISTS namesort AS "
+ "SELECT ownerid, surname  || given  AS name FROM nametable ORDER BY surname , given ; "
+ " "
+ "CREATE INDEX IF NOT EXISTS idxNameSortName ON Namesort (NAME); "
+ "CREATE INDEX IF NOT EXISTS idxNameSortOwnerID ON Namesort (OwnerID); "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- Unresolved Duplicate Name Pairs',COUNT(1), 'pairs of Given and Surnames, not flagged as \"Not a Problem\"'  "
+ "FROM "
+ "( SELECT n.ownerid AS ID1, n2.ownerid AS ID2 from namesort n INNER JOIN namesort n2 USING (name) where ID1<ID2 "
+ "  EXCEPT SELECT ID1,ID2 FROM EXCLUSIONTABLE WHERE exclusiontype=1); "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- Resolved* Duplicate Name Pairs',COUNT(1), 'flagged as \"Not a Problem\" - flags lost on transfer'  "
+ "FROM EXCLUSIONTABLE WHERE exclusiontype=1; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT '- Unresolved Duplicates with Media Links',COUNT(1), \"secondary persons' links lost on merge\"  "
+ "FROM "
+ "( SELECT n.ownerid AS ID1, n2.ownerid as ID2 FROM namesort n INNER JOIN namesort n2 USING (name)  "
+ "  INNER JOIN medialinktable ml ON n2.ownerid=ml.ownerid   "
+ "  where ID1<ID2 AND ml.ownertype=0 "
+ "  EXCEPT SELECT ID1,ID2 FROM EXCLUSIONTABLE WHERE exclusiontype=1 "
+ "  ) "
+ "   ; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT 'Alternate names', COUNT(1), 'all records in NameTable where IsPrimary=0' "
+ "FROM NameTable "
+ "WHERE IsPrimary = 0; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT '- Orphaned Alternate names*', COUNT(1), 'no Primary name record found' "
+ "FROM NameTable na "
+ "LEFT JOIN nametable n "
+ "ON na.ownerid=n.ownerid "
+ "WHERE NOT na.IsPrimary AND +n.IsPrimary AND n.nameid ISNULL; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT 'Families', COUNT(1), 'all records in FamilyTable' "
+ "FROM FamilyTable; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT 'Fact Types', COUNT(1), 'no. of records from FactTypeTable' FROM FactTypeTable "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- Custom Fact Types', COUNT(1), 'no. of custom Fact Types' FROM main.FactTypeTable  "
+ " WHERE FactTypeID > (SELECT max (FactTypeID) FROM CTL.FactTypeTable) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- Customised Built-in Fact Types', COUNT(1), 'no. of built-in Fact Types modified' FROM  "
+ " ( "

+ "  SELECT FactTypeID, Name , Abbrev, GedcomTag, usevalue, usedate, UsePlace, Sentence FROM main.FactTypeTable  "
+ "    WHERE FactTypeID <= (SELECT max (FactTypeID) FROM CTL.FactTypeTable)     "
+ "    EXCEPT SELECT FactTypeID, Name , Abbrev, GedcomTag, usevalue, usedate, UsePlace, Sentence FROM CTL.FactTypeTable "
+ "  ) "
+ ";  "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- Unused Fact Types', COUNT(1), 'no. of Fact Types not used' FROM  "
+ " ( "

+ "  SELECT FactTypeID, Name , Sentence FROM main.FactTypeTable WHERE FactTypeID IN  "
+ "   ( "
+ "    SELECT FactTypeID FROM main.FactTypeTable EXCEPT SELECT EventType FROM main.EventTable "
+ "    )     "
+ "  ) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- Blank Fact Type Names', COUNT(1), 'FactTypes must be named' FROM  "
+ " ( "

+ "  SELECT FactTypeID, Name , Sentence FROM FactTypeTable WHERE Name  LIKE '' "
+ "  ) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- Blank FactType Sentences', COUNT(1), 'FactType needing definition' FROM  "
+ " ( "

+ "  SELECT FactTypeID, Name , Sentence FROM FactTypeTable WHERE Sentence LIKE '' "
+ "  ) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT 'Events', COUNT(1), 'all records of EventTable' "
+ "FROM EventTable; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT '- Orphaned Events', SUM(Ctr), 'events for which no person or family match in respective tables'  "
+ "FROM "
+ "( "
+ "SELECT '- Orphaned Personal Events', COUNT(1) AS Ctr "
+ "FROM EventTable E "
+ "LEFT JOIN persontable p "
+ "ON e.OWNERID=p.PersonID  "
+ "LEFT JOIN nametable n "
+ "ON p.PERSONID=N.OwnerID  "
+ "WHERE e.OWNERTYPE=0  AND n.ISPRIMARY AND n.nameid ISNULL "
+ "UNION ALL "
+ "SELECT '- Orphaned Family Events', COUNT(1) "
+ "FROM EventTable E "
+ "LEFT JOIN familytable fm "
+ "ON e.OWNERID=fm.FamilyID  "
+ "WHERE e.OWNERTYPE=1 AND fm.FamilyID ISNULL "
+ ") "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- Event Witnesses', COUNT(1), 'All records in WitnessTable of persons sharing events' FROM Witnesstable "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '-- Nominal Witnesses', COUNT(1), 'not Persons from database, but named in WitnessTable as sharing an event' FROM Witnesstable WHERE PersonID = 0  "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '-- Headless Witnesses', COUNT(1), 'PersonID (RIN) in WitnessTable missing from PersonTable' FROM "
+ " ( "

+ "  SELECT PersonID FROM WitnessTable WHERE WitnessTable.PersonID > 0 "
+ "   EXCEPT SELECT PersonID FROM PersonTable    "
+ "  ) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '-- Witnesses to Lost Events', COUNT(1), 'EventID in WitnessTable cannot be found in EventTable' FROM "
+ " ( "

+ "  SELECT [WitnessID],[EventID],[PersonID],[WitnessOrder],[Role],[Sentence],[Note],[Given] ,[Surname] ,[Prefix] ,[Suffix]  FROM WitnessTable WHERE EventID IN   "
+ "   ( "

+ "    SELECT EventID FROM WitnessTable  "
+ "     EXCEPT SELECT EventID FROM EventTable "
+ "    )     "
+ "  ) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '-- Witnesses with blank Role', count(1), 'no role has been assigned from RoleTable or the RoleTable role is empty' FROM  "
+ " (  "

+ "  SELECT [WitnessID],[EventID],[PersonID],[WitnessOrder],[Role],W.[Sentence],[Note],[Given] ,[Surname] ,[Prefix] ,[Suffix]   FROM WitnessTable W, RoleTable WHERE Role LIKE RoleID AND RoleName  LIKE '' "
+ "  )  "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '-- Witnesses with Custom Sentence', count(1), 'a custom sentence has been assigned, unique to this witness' FROM  "
+ " (  "

+ "  SELECT [WitnessID],[EventID],[PersonID],[WitnessOrder],[Role],W.[Sentence],[Note],[Given] ,[Surname] ,[Prefix] ,[Suffix]   FROM WitnessTable W, RoleTable WHERE Role LIKE RoleID AND W.Sentence NOT LIKE '' "
+ "  )  "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '-- Witnesses with Note', count(1), 'a note has been entered for this witness to an event' FROM  "
+ " (  "

+ "  SELECT [WitnessID],[EventID],[PersonID],[WitnessOrder],[Role],W.[Sentence],[Note],[Given] ,[Surname] ,[Prefix] ,[Suffix]   FROM WitnessTable W, RoleTable WHERE Role LIKE RoleID AND Note NOT LIKE '' "
+ "  )  "
+ "; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT '-- Roles', COUNT(1), 'no. of records from RoleTable' FROM roletable "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '--- Custom Roles', COUNT(1), 'no. of custom roles' FROM main.RoleTable  "
+ " WHERE RoleID > (SELECT max (RoleID) FROM CTL.roletable) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '--- Customised Built-in Roles', COUNT(1), 'no. of built-in roles modified' FROM  "
+ " ( "

+ "  SELECT RoleID, RoleName , EventType, RoleType, Sentence FROM main.RoleTable  "
+ "    WHERE RoleID <= (SELECT max (RoleID) FROM CTL.roletable)     "
+ "    EXCEPT SELECT RoleID, RoleName , EventType, RoleType, Sentence FROM CTL.RoleTable "
+ "  ) "
+ ";  "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '--- Unused Roles', COUNT(1), 'no. of roles not used' FROM  "
+ " ( "

+ "  SELECT RoleID, RoleName , EventType, RoleType, Sentence FROM main.RoleTable WHERE RoleID IN  "
+ "   ( "
+ "    SELECT RoleID FROM main.RoleTable EXCEPT SELECT Role FROM main.WitnessTable "
+ "    )     "
+ "  ) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '--- Blank Role Names', COUNT(1), 'Roles needing definition' FROM  "
+ " ( "

+ "  SELECT RoleID, RoleName , EventType, RoleType, Sentence FROM RoleTable WHERE rolename  LIKE '' "
+ "  ) "
+ "; "
+ " "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '--- Blank Role Sentences', COUNT(1), 'Roles needing definition' FROM  "
+ " ( "

+ "  SELECT RoleID, RoleName , EventType, RoleType, Sentence FROM RoleTable WHERE Sentence LIKE '' "
+ "  ) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT 'Total Places', COUNT(1), 'all records in PlaceTable incl Places and Place Details (Sites)' "
+ "FROM PlaceTable "
+ "; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT '- System Places', COUNT(1), 'system supplied Places: LDS Temples' "
+ "FROM PlaceTable "
+ "WHERE PlaceType = 1 "
+ "; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT '- User Places', COUNT(1), 'user defined Places excl Sites' "
+ "FROM PlaceTable "
+ "WHERE PlaceType = 0 "
+ "; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT '-- Used, having Geo-coordinates', COUNT(1), 'non-empty Lat or Long' "
+ "FROM (SELECT P.PlaceID  "
+ "     FROM PlaceTable P, eventtable e "
+ "     WHERE P.PlaceType = 0 AND P.Latitude +P.Longitude <> 0  "
+ "     AND P.PlaceID=E.PlaceID "
+ "     GROUP BY P.PlaceID) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '-- Unused User Places*', COUNT(1), 'not used by EventTable, will be dropped in a transfer' "
+ " FROM PlaceTable,        "
+ " (SELECT PlaceTable.PlaceID AS unusedPlaceID           "
+ "  FROM PlaceTable  "
+ "  WHERE placetable.PlaceType = 0          "
+ "  EXCEPT SELECT eventtable.PlaceID            "
+ "         FROM eventtable    "
+ ")       "
+ "  WHERE PlaceTable.PlaceID = unusedPlaceID; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '-- User Place Details', COUNT(1), 'user defined Sites' "
+ "FROM PlaceTable "
+ "WHERE PlaceType = 2; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '--- Used, having Place Detail Notes*', COUNT(1), 'Site Notes will be lost in a transfer' "
+ "FROM (SELECT PD.PlaceID  "
+ "     FROM PlaceTable PD, PlaceTable P, eventtable e "
+ "     WHERE PD.PlaceType = 2 AND PD.Note <> '' AND PD.MasterID=P.PlaceID  "
+ "     AND PD.MasterID=E.PlaceID AND PD.PlaceID=E.SiteID "
+ "     GROUP BY PD.PlaceID) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '--- Used, having Geo-coordinates', COUNT(1), 'non-empty Lat or Long' "
+ "FROM (SELECT PD.PlaceID  "
+ "     FROM PlaceTable PD, PlaceTable P, eventtable e "
+ "     WHERE PD.PlaceType = 2 AND PD.Latitude +PD.Longitude <> 0 AND PD.MasterID=P.PlaceID  "
+ "     AND PD.MasterID=E.PlaceID AND PD.PlaceID=E.SiteID "
+ "     GROUP BY PD.PlaceID) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '--- Unused Place Details*', COUNT(1), 'Sites will be lost in a transfer' "
+ "FROM PlaceTable PD  "
+ "LEFT JOIN PlaceTable P ON PD.MasterID=P.PlaceID "
+ "LEFT JOIN eventtable e ON (PD.MasterID=E.PlaceID AND PD.PlaceID=E.SiteID) "
+ "WHERE PD.PlaceType = 2 AND E.EVENTID ISNULL "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT 'Source Templates', COUNT(1), '# of records from SourceTemplateTable' FROM SourceTemplateTable "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- Custom Source Templates', COUNT(1), '# of custom Source Templates' FROM main.SourceTemplateTable  "
+ " WHERE TemplateID > 9999 "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- Unupdated Built-in SourceTemplates', COUNT(1), '# not matching reference database' FROM  "
+ " ( "

+ "  SELECT TemplateID, Name , [Description],[Footnote],[ShortFootnote],[Bibliography],[FieldDefs] FROM main.SourceTemplateTable  "
+ "    WHERE TemplateID <= 9999     "
+ "    EXCEPT SELECT TemplateID, Name , [Description],[Footnote],[ShortFootnote],[Bibliography],[FieldDefs] FROM CTL.SourceTemplateTable "
+ "  ) "
+ ";  "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- Unused Custom SourceTemplates*', COUNT(1), 'lost on transfer' FROM  "
+ " ( "

+ "  SELECT TemplateID, Name  FROM main.SourceTemplateTable WHERE TemplateID>9999 AND TemplateID IN  "
+ "   ( "
+ "    SELECT TemplateID FROM main.SourceTemplateTable EXCEPT SELECT TemplateID FROM main.SourceTable "
+ "    )     "
+ "  ) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- Incomplete Source Templates', COUNT(1), 'missing part of definition' FROM  "
+ " ( "

+ "  SELECT TemplateID, Name , [Description],[Footnote],[ShortFootnote],[Bibliography],[FieldDefs] FROM main.SourceTemplateTable "
+ "  WHERE Name  LIKE '' OR    "
+ "        [Description] LIKE '' OR    "
+ "        [Footnote] LIKE '' OR    "
+ "        [ShortFootnote] LIKE '' OR    "
+ "        [Bibliography] LIKE '' OR    "
+ "        [FieldDefs] LIKE '%<FIELDS/>%' "
+ "  ) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT 'Total Sources', COUNT(1), 'all records from SourceTable' "
+ "FROM SourceTable; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT '- Unused Sources*', COUNT(1), 'SourceTable records unused by CitationTable' "
+ "FROM sourcetable s LEFT OUTER JOIN citationtable c "
+ "ON s.SOURCEID = c.SOURCEID  "
+ "WHERE c.SourceID ISNULL; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT 'Total Citations', COUNT(1), 'all records from CitationTable' "
+ "FROM CitationTable; "
+ " "
+ " "
+ "DROP TABLE IF EXISTS tmpCitations "
+ "; "
+ "CREATE TABLE tmpCitations (CITID INTEGER, SrcID INTEGER, RIN INTEGER, Dupes INTEGER, RinCitation TEXT); "
+ " "
+ "INSERT INTO tmpCitations  "
+ "SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, COUNT(1)-1 AS Dupes,  "
+ "  QUOTE(n.OwnerID) || 'Personal' || QUOTE(s.NAME) || QUOTE(s.refnumber) || QUOTE(s.actualtext) || QUOTE(s.comments) || QUOTE(CAST(s.Fields AS TEXT)) || QUOTE(mm1.mediafile)  "
+ "  || QUOTE(c.refnumber) || QUOTE(c.actualtext) || QUOTE(c.comments) || QUOTE(CAST(c.Fields AS TEXT)) || QUOTE(mm2.mediafile)  "
+ "  AS RinCitation "
+ "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 "
+ "  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3  "
+ "  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID     "
+ "  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4  "
+ "  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID     "
+ "WHERE  c.ownertype=0 AND +n.IsPrimary=1 "
+ "GROUP BY RinCitation "
+ " "
+ "UNION ALL "
+ " "
+ "SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, COUNT(1)-1 AS Dupes,  "
+ "  QUOTE(n.OwnerID) || QUOTE(f.NAME) || QUOTE(e.EventID) || QUOTE(s.NAME) || QUOTE(s.refnumber) || QUOTE(s.actualtext) || QUOTE(s.comments) || QUOTE(CAST(s.Fields AS TEXT)) || QUOTE(mm1.mediafile)  "
+ "  || QUOTE(c.refnumber) || QUOTE(c.actualtext) || QUOTE(c.comments) || QUOTE(CAST(c.Fields AS TEXT)) || QUOTE(mm2.mediafile)  "
+ "  AS RinCitation "
+ "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   "
+ "  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3  "
+ "  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID     "
+ "  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4  "
+ "  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID     "
+ "WHERE c.ownertype=2 AND e.ownertype=0 AND f.ownertype=0 AND +n.IsPrimary=1  "
+ "GROUP BY RinCitation "
+ " "
+ "UNION ALL "
+ " "
+ "SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, COUNT(1)-1 AS Dupes,  "
+ "  QUOTE(n.OwnerID) || QUOTE(f.NAME) || QUOTE(e.EventID) || s.NAME || s.refnumber || QUOTE(s.actualtext) || QUOTE(s.comments) || (s.Fields) || QUOTE(mm1.mediafile) ||  "
+ "  c.refnumber || QUOTE(c.actualtext) || QUOTE(c.comments) || (c.Fields) || QUOTE(mm2.mediafile) AS RinCitation "
+ "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 "
+ "  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3  "
+ "  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID     "
+ "  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4  "
+ "  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID     "
+ "WHERE c.ownertype=1 AND e.ownertype=1 AND f.ownertype=1 AND +n.IsPrimary=1 "
+ "GROUP BY RinCitation "
+ " "
+ "UNION ALL "
+ " "
+ "SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, COUNT(1)-1 AS Dupes,  "
+ "  n.OwnerID || 'Alternate Name' || s.NAME || s.refnumber || QUOTE(s.actualtext) || QUOTE(s.comments) || (s.Fields) || QUOTE(mm1.mediafile) ||  "
+ "  c.refnumber || QUOTE(c.actualtext) || QUOTE(c.comments) || (c.Fields) || QUOTE(mm2.mediafile) AS RinCitation "
+ "FROM  citationtable c  "
+ "  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid  "
+ "  LEFT OUTER JOIN  nametable n ON n.nameid=c.ownerid "
+ "  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3  "
+ "  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID     "
+ "  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4  "
+ "  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID     "
+ "WHERE  c.ownertype=7 AND +n.IsPrimary=0 "
+ "GROUP BY RinCitation "
+ " "
+ "; "
+ " "
+ " "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- Duplicate Citations', SUM(Dupes), 'identical in most respects, cluttering reports'  "
+ "  FROM tmpcitations "
+ "; "
+ " "
+ " "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- Sourceless Citations*', COUNT(1), 'no SourceTable record for this CitationTable record' "
+ "FROM CitationTable c LEFT OUTER JOIN sourcetable s "
+ "ON s.SOURCEID = c.SOURCEID "
+ "WHERE s.SourceID ISNULL; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- Headless Citations*', SUM(ctr), 'CitationTable records for which no Person, Event, Family, AltName found; cleaned on transfer'  "
+ "FROM "
+ "( "
+ "SELECT '- Headless Personal Citations', COUNT(1) AS ctr "
+ "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.ownerid ISNULL "
+ "UNION ALL "
+ "SELECT '- Headless Fact Citations', COUNT(1) "
+ "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 n.ownerid ISNULL "
+ "UNION ALL "
+ "SELECT '- Headless Family Citations', COUNT(1) "
+ "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.ownerid ISNULL "
+ "UNION ALL "
+ "SELECT '- Headless Alt Name Citations', COUNT(1) "
+ "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.nameid ISNULL "
+ "); "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT 'Repositories', COUNT(1), 'all records from AddressTable of type Repository' "
+ "FROM AddressTable "
+ "WHERE AddressType = 1; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT 'To- do tasks', COUNT(1), 'all records from ResearchTable' "
+ "FROM ResearchTable "
+ "WHERE TaskType = 0; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT 'Multimedia items', COUNT(1), 'all records from MultimediaTable' "
+ "FROM MultimediaTable; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT '- lacking thumbnail', COUNT(1), 'probably an imported reference to an image file that has yet to be found' "
+ "FROM MultimediaTable  "
+ "WHERE mediatype=1 AND thumbnail ISNULL; "
+ " "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- duplicate multimedia filenames', SUM(DUPES), 'probably having different paths' "
+ " FROM "
+ " ( "
+ "  SELECT MediaFile , COUNT(*)-1 AS DUPES FROM MultimediaTable  "
+ "    GROUP BY MediaFile  "
+ "  ) "
+ "; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT 'Multimedia links', COUNT(1), 'all records from MediaLinkTable' "
+ "FROM MediaLinkTable; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT '- with Date & Description*', COUNT(1), 'if a record has both, the Description is lost in a transfer' "
+ "FROM MultimediaTable m "
+ "INNER JOIN MediaLinkTable ml ON m.MediaID=ml.MediaID "
+ "WHERE ml.Date <>'.' AND ml.Description LIKE '%_'  ; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT '- duplicate multimedia links', SUM(DUPES), 'image appears multiple times for person, fact' "
+ " FROM "
+ " ( "
+ "  SELECT COUNT(*)-1 AS DUPES FROM MediaLinkTable "
+ "  GROUP BY OwnerType, OwnerID, IsPrimary, Include1, Include2, Include3, Include4, SortOrder, RectLeft, RectTop, RectRight, RectBottom, Note, Caption , RefNumber , Date, SortDate, Description "
+ "  ) "
+ "; "
+ " "
+ "INSERT INTO VariableTable "
+ "SELECT 'Addresses', COUNT(1), 'all records from AddressTable of type Address' "
+ "FROM AddressTable "
+ "WHERE AddressType = 0; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT '- blank names', COUNT(1), 'Name field of AddressTable record is blank'  "
+ "FROM AddressTable   "
+ "WHERE AddressType = 0 AND Name  LIKE ''; "
+ "  "
+ "INSERT INTO VariableTable "
+ "SELECT 'Correspondence', COUNT(1), 'all records from ResearchTable of type Correspondence' "
+ "FROM ResearchTable "
+ "WHERE TaskType = 1; "
+ " "
+ "INSERT INTO VARIABLETABLE "
+ "SELECT '* NOT TRANSFERABLE', NULL , 'via GEDCOM or Drag&Drop to another RM database'; "
+ " "
+ " "
+ "DETACH CTL "
+ "; "
+ " "
+ " "
+ " "
+ " "
+ "SELECT Value, Variable, Remark FROM variabletable ;"

                    ;
                return commandText;
            }
        }

        public static string dBAddressBlankNameQuery
        {
            get
            {
                string commandText = "SELECT Surname||', '||Given  AS Person, Adr.Name  AS AddressName, Adr.Street1, Adr.Street2, Adr.City, Adr.State, Adr.Country, Adr.Phone1, Adr.Fax, Adr.Email FROM NameTable AS Nam, AddressLinkTable AS Lnk, AddressTable AS Adr WHERE Nam.OwnerID = Lnk.OwnerID AND Lnk.AddressID = Adr.AddressID AND Adr.AddressType = 0 AND Adr.Name NOT LIKE '%_';";
                return commandText;
            }
        }

        public static string dBEventsSharedMissingWitnesses 
        {
            get
            {
                string commandText = ""
+ "SELECT PersonID AS LostRIN,  "
+ "       'shared as',  "
+ "       RoleName,  "
+ "       FactTypeTable.Name AS Event,  "
+ "       'of', "
+ "       CASE EventTable.OwnerType "
+ "        WHEN 0 THEN NameTable.Surname || ', ' || NameTable.Given || '-' || NameTable.OwnerID  "
+ "        WHEN 1 THEN Husb.Surname || ', ' || Husb.Given || '-' || Husb.OwnerID "
+ "       END "
+ "        AS Principal  "
+ "FROM WitnessTable  "
+ "LEFT JOIN EventTable USING (EventID)  "
+ "LEFT JOIN FactTypeTable ON EventTable.EventType=FactTypeID "
+ "LEFT JOIN RoleTable ON Role=RoleID "
+ "LEFT JOIN NameTable USING (OwnerID) "
+ "LEFT JOIN FamilyTable ON EventTable.OwnerID = FamilyTable.FamilyID "
+ "LEFT JOIN NameTable AS Husb ON FamilyTable.FatherID = NameTable.OwnerID "
+ "WHERE PersonID IN "
+ "( "
+ "SELECT PersonID FROM WitnessTable WHERE WitnessTable.PersonID > 0 "
+ "EXCEPT SELECT PersonID FROM PersonTable  "
+ ") "
+ "AND EventTable.OwnerType = 0 "
+ "AND +NameTable.IsPrimary "
+ "; "                    
                    ;
                return commandText;
            }
        }

        public static string dBPeopleMultipleSpouses 
        { 
            get
                {
                string commandText = ""
+ "SELECT * FROM  "
+ "( "
+ " SELECT FatherID AS RIN, Surname || ' ' || Suffix || ', ' || Prefix || ' ' || Given AS Name, COUNT(*) AS 'Spouses' "
+ "  FROM FamilyTable JOIN NameTable WHERE OwnerID=RIN AND +IsPrimary=1 GROUP BY RIN "
+ " "
+ " UNION ALL "
+ " "
+ " SELECT MotherID AS RIN, Surname || ' ' || Suffix || ', ' || Prefix || ' ' || Given AS Name, COUNT(*) AS 'Spouses' "
+ "  FROM FamilyTable JOIN NameTable WHERE OwnerID=RIN AND +IsPrimary=1 GROUP BY RIN "
+ ") "
+ "WHERE  Spouses > 1 "
+ "ORDER BY Spouses DESC, Name; ";
                return commandText;
                }
        }

        public static string dBPlacesUnusedPlaces
        {
            get
            {
                string commandText = ""
+ "	SELECT PlaceTable.PlaceID, PlaceTable.Name     "
+ "		FROM PlaceTable,        "
+ "		(SELECT PlaceTable.PlaceID AS unusedPlaceID           "
+ "			FROM PlaceTable           "
+ "			EXCEPT SELECT eventtable.PlaceID            "
+ "				FROM eventtable)    "
+ "	WHERE placetable.PlaceType = 0       "
+ "	AND PlaceTable.PlaceID = unusedPlaceID; ";                   
                return commandText;
                    
            }
        }

        public static string dBPlaceshavingPlaceDetails 
        {
            get
            {
                string commandText = ""
+ "SELECT PlaceTable2.Name  AS Place, PlaceTable1.Name  AS PlaceDetails "
+ "	FROM PlaceTable AS PlaceTable1, PlaceTable AS PlaceTable2 "
+ "	WHERE PlaceTable1.MasterID = PlaceTable2.PlaceID "
+ "	ORDER by Place||PlaceDetails ; ";
                return commandText;
            }
            
        }

        public static string dBPeopleDuplicateNameSearch 
        { 
            get
            {
                string commandText = ""
// DuplicateNameSearch.sql
// Attempt to provide faster tool than RM4's DuplicateSearchMerge

+ "DROP TABLE IF EXISTS namesort; "
+ "CREATE temp TABLE IF NOT EXISTS namesort AS "
+ "SELECT ownerid, LOWER(surname  || given ) AS NAME FROM nametable ORDER BY NAME ; "

+ "CREATE INDEX IF NOT EXISTS idxNameSortName ON Namesort (NAME); "
+ "CREATE INDEX IF NOT EXISTS idxNameSortOwnerID ON Namesort (OwnerID); "
+ "  "

+ "SELECT  "
+ "  100*( "
+ "  1  "
+ "  + CASE P.birthyear<>0 AND S.birthyear<>0 WHEN 0 THEN 0 ELSE (3-ABS(P.BIRTHYEAR-S.BIRTHYEAR)) END "
+ "  + CASE P.Deathyear<>0 AND S.DeathYear<>0 WHEN 0 THEN 0 ELSE (3-ABS(P.Deathyear-S.Deathyear)) END "
+ "  + CASE LOWER(NF1.Surname || NF1.Given) WHEN LOWER(NF2.Surname || NF2.Given)  THEN 3 ELSE 0 END     "
+ "  + CASE LOWER(NM1.Surname || NM1.Given) WHEN LOWER(NM2.Surname || NM2.Given)  THEN 3 ELSE 0 END     "
+ "  )/13 "
+ "  AS Score,    "
// Score 1 for name match
// Score 3 for matching BirthYear less 1 for each year difference
// Score 3 for matching DeathYear less 1 for each year difference
// Score 3 for matching Father names
// Score 3 for matching Mother names
// Divide by max possible score and multiply by 100
+ "  P.SurName ||', '|| P.Given ||'-'||ID1 AS \"Primary\", P.BirthYear AS Born, P.DeathYear AS Died,  "
+ "    NF1.Surname ||', '|| NF1.Given ||'-'||NF1.OwnerID AS PrimesFather,  "
+ "    NM1.Surname ||', '|| NM1.Given ||'-'||NM1.OwnerID AS PrimesMother,  "
+ "  S.SurName ||', '|| S.Given ||'-'||ID2 AS Duplicate, S.BirthYear AS Born, S.DeathYear AS Died,  "
+ "    NF2.Surname ||', '|| NF2.Given ||'-'||NF2.OwnerID AS DupesFather, "
+ "    NM2.Surname ||', '|| NM2.Given ||'-'||NM2.OwnerID AS DupesMother "
+ "FROM "
+ "( SELECT ns1.ownerid AS ID1, ns2.ownerid AS ID2 FROM namesort ns1  "
+ "  INNER JOIN namesort ns2 USING (NAME)  "
+ "  WHERE ID1<ID2 "
+ "  EXCEPT SELECT ID1,ID2 FROM EXCLUSIONTABLE WHERE ExclusionType=1) "
+ "INNER JOIN nametable P ON P.OwnerID=ID1  "
+ "INNER JOIN nametable S ON S.OwnerID=ID2 "
+ "LEFT JOIN CHILDTABLE C1 ON P.OWNERID=C1.CHILDID "
+ "LEFT JOIN ChildTable C2 ON S.OWNERID=C2.CHILDID "
+ "LEFT JOIN FamilyTable F1 ON C1.FamilyID=F1.FamilyID "
+ "LEFT JOIN FamilyTable F2 ON C2.FamilyID=F2.FamilyID "
+ "LEFT JOIN NameTable NF1 ON F1.FatherID=NF1.OwnerID "
+ "LEFT JOIN NameTable NF2 ON F2.FatherID=NF2.OwnerID "
+ "LEFT JOIN NameTable NM1 ON F1.MotherID=NM1.OwnerID "
+ "LEFT JOIN NameTable NM2 ON F2.MotherID=NM2.OwnerID "

+ "WHERE +P.IsPrimary AND +S.IsPrimary  "
+ "  AND CASE +NF1.IsPrimary WHEN 0 THEN 0 ELSE 1 END  "
+ "  AND CASE +NF2.IsPrimary WHEN 0 THEN 0 ELSE 1 END "
+ "  AND CASE +NM1.IsPrimary WHEN 0 THEN 0 ELSE 1 END "
+ "  AND CASE +NM2.IsPrimary WHEN 0 THEN 0 ELSE 1 END "
+ "  AND NOT MIN(P.BIRTHYEAR, S.BIRTHYEAR, ABS(P.BIRTHYEAR-S.BIRTHYEAR)>2) "
+ "  AND NOT MIN(P.Deathyear, S.Deathyear, ABS(P.Deathyear-S.Deathyear)>2) "
+ "  AND CASE F1.FatherID WHEN ID2 THEN 0 ELSE 1 END "
+ "  AND CASE F1.MotherID WHEN ID2 THEN 0 ELSE 1 END "
+ "  AND CASE F2.FatherID WHEN ID1 THEN 0 ELSE 1 END "
+ "  AND CASE F2.MotherID WHEN ID1 THEN 0 ELSE 1 END "
+ "ORDER BY Score DESC, \"Primary\" ASC "
+ "LIMIT 100 "
+ "; ";
                return commandText;
            }
        }

        public static string dBSourcesSourceList 
        { 
            get
            {
                string commandText = ""
+ "" // SourceList.sql "
+ "" // by ve3meo 2011-11-03 "
+ "" // A powerful query emulating the RM4 Source List Report that can be sorted and filtered in many ways. "
+ "" // Reports every citation, showing the Source Name, Citation Fields, Quality, Text and Comment, the citing fact and event date and the person  "
+ "" // with whom it is associated, the Source Template and key ID's for finding the corresponding records in the database tables. "
+ "" // "
+ "" // Based on prior work: "
+ "" //  Source Template List with Citation Texts "
+ "" //  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 ve3meo - to concatenate names for easier viewing        SourceTemplateListWithCitationDetails2.sql "
+ "" // 2011-11-02 ve3meo - added Citation Details and Comments            SourceTemplateListWithCitationDetails3.sql "
+ "" //            added Source Details Fields (Cit Fields), Citation Quality, Event Date, rearranged table order "
+ "" //                                                                    SourceTemplateListWithCitationDetails4.sql "
+ "" // 2011-11-03 ve3meo corrected misreporting of RIN and Person having an Alternate Name fact source "
+ "" //                                                         renamed    SourceList.sql "
+ "" // 2011-11-04 ve3meo further corrected reporting of false citations from non-existing Alternate Name facts "
+ "" // 2011-11-06 now reports Free Form and orphaned citations; count of media items linked to citation; "
+ "" //            improved format of CitFields; CAST CitText and CitComments AS TEXT for wider compatability with SQLite managers "
+ "" // 2011-11-18 ve3meo added SourceTable FIELDS, Event Place and Site to output "
+ "  "
+ "" // This complex SELECT processes data from the UNION of 5 SELECTs that it queries, setting the order of display  "
+ "SELECT  "
+ "       \"Source Name\", " // "
+ "" // SourceTable FIELDS  "
+ "       REPLACE(                                                                                                 "
+ "        REPLACE( "
+ "         REPLACE( "
+ "          REPLACE( "
+ "           REPLACE( "
+ "            REPLACE(SUBSTR(CAST(SrcFIELDS AS TEXT),55, LENGTH(CAST(SrcFIELDS AS TEXT))-87), '</Name>', ':'||CAST (X'09' AS TEXT)), "
+ "           '<Field><Name>', ''), "
+ "          '</Value>',''), "
+ "         '<Value>',''), "
+ "        '<Value/>',''), "
+ "       '</Field>',CAST (X'0D' AS TEXT)) "
+ "       AS 'Src Fields', "
+ "" // "
+ "" // CitationTable FIELDS parsed out from XML in a blob "
+ "       REPLACE(                                                                                                 "
+ "        REPLACE( "
+ "         REPLACE( "
+ "          REPLACE( "
+ "           REPLACE( "
+ "            REPLACE(SUBSTR(CAST(CitFIELDS AS TEXT),55, LENGTH(CAST(CitFIELDS AS TEXT))-87), '</Name>', ':'||CAST (X'09' AS TEXT)), "
+ "           '<Field><Name>', ''), "
+ "          '</Value>',''), "
+ "         '<Value>',''), "
+ "        '<Value/>',''), "
+ "       '</Field>',CAST (X'0D' AS TEXT)) "
+ "       AS 'Cit Fields', "
+ "" // "
+ "       \"Quality\" AS 'Qual', "
+ "       CAST(\"Citation Text\" AS TEXT) AS 'Cit Text', "
+ "       CAST(\"Citation Comment\" AS TEXT) AS 'Cit Comment', "
+ "       Media, "
+ "       \"Fact Type\" AS 'Cited by', "
+ "" // Convert Fact dates to readable form "
+ "        CASE Substr( rawDate , 1 , 1 ) "
+ "      	WHEN 'Q' THEN rawDate  "
+ "      	WHEN 'T' THEN Substr( rawDate , 2 , 20 )  "
+ "      	WHEN 'D' THEN  "
+ "      		CASE Substr( rawDate , 2 , 1 )  "
+ "      			WHEN 'A' THEN 'aft '  "
+ "      			WHEN 'B' THEN 'bef '  "
+ "      			WHEN 'F' THEN 'from '  "
+ "      			WHEN 'I' THEN 'since '  "
+ "      			WHEN 'R' THEN 'bet '  "
+ "      			WHEN 'S' THEN 'from '  "
+ "      			WHEN 'T' THEN 'to '  "
+ "      			WHEN 'U' THEN 'until '  "
+ "      			WHEN 'Y' THEN 'by '  "
+ "      		ELSE ''  "
+ "      		END  "
+ "      		||  "
+ "      		CASE Substr( rawDate , 13 , 1 )  "
+ "      			WHEN 'A' THEN 'abt '  "
+ "      			WHEN 'C' THEN 'ca '  "
+ "      			WHEN 'E' THEN 'est '  "
+ "      			WHEN 'L' THEN 'calc '  "
+ "      			WHEN 'S' THEN 'say '  "
+ "      			WHEN '6' THEN 'cert '  "
+ "      			WHEN '5' THEN 'prob '  "
+ "      			WHEN '4' THEN 'poss '  "
+ "      			WHEN '3' THEN 'lkly '  "
+ "      			WHEN '2' THEN 'appar '  "
+ "      			WHEN '1' THEN 'prhps '  "
+ "      			WHEN '?' THEN 'maybe '  "
+ "      		ELSE ''  "
+ "      		END  "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 3 , 1 ) = '-' THEN 'BC' ELSE '' END   "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 4 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 4 , 4 ) END  "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 3 , 5 ) ) ELSE '' END  "
+ "      		||  "
+ "      		CASE  "
+ "      			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) <> '00' ) THEN '-??'  "
+ "      			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) == '00' ) THEN ''  "
+ "      			WHEN Substr( rawDate , 8 , 2 ) = '00' THEN ''  "
+ "      		ELSE '-' || Substr( rawDate , 8 , 2 )  "
+ "      		END  "
+ "      		||  "
+ "      		Coalesce( Nullif( '-' || Substr( rawDate , 10 , 2 ) , '-00' ) , '' )  "
+ "      		||  "
+ "      		CASE Substr( rawDate , 2 , 1 )  "
+ "      			WHEN 'R' THEN ' and '  "
+ "      			WHEN 'S' THEN ' to '  "
+ "      			WHEN 'O' THEN ' or '  "
+ "      			WHEN '-' THEN ' - '  "
+ "      		ELSE ''  "
+ "      		END  "
+ "      		||  "
+ "      		CASE Substr( rawDate , 24 , 1 )  "
+ "      			WHEN 'A' THEN 'abt '  "
+ "      			WHEN 'E' THEN 'est '  "
+ "      			WHEN 'L' THEN 'calc '  "
+ "      			WHEN 'C' THEN 'ca '  "
+ "      			WHEN 'S' THEN 'say '  "
+ "      			WHEN '6' THEN 'cert '  "
+ "      			WHEN '5' THEN 'prob '  "
+ "      			WHEN '4' THEN 'poss '  "
+ "      			WHEN '3' THEN 'lkly '  "
+ "      			WHEN '2' THEN 'appar '  "
+ "      			WHEN '1' THEN 'prhps '  "
+ "      			WHEN '?' THEN 'maybe '  "
+ "    		  ELSE ''  "
+ "    	   	END  "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 14 , 1 ) = '-' THEN 'BC' ELSE '' END  "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 15 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 15 , 4 ) END  "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 14 , 5 ) ) ELSE '' END  "
+ "      		||  "
+ "      		CASE  "
+ "      			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) <> '00' ) THEN '-??'  "
+ "      			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) == '00' ) THEN ''  "
+ "      			WHEN Substr( rawDate , 19 , 2 ) = '00' THEN ''  "
+ "      		ELSE '-' || Substr( rawDate , 19 , 2 )  "
+ "      		END  "
+ "      		||  "
+ "      		Coalesce( Nullif( '-' || Substr( rawDate , 21 , 2 ) , '-00' ) , '' )  "
+ "      	ELSE ''  "
+ "        END AS 'EventDate' , "
+ "" // "
+ "        \"Event Place\", "
+ "        \"Event Site\", "
+ "       \"RIN 1\", "
+ "" // Concatenate Name fields "
+ "       UPPER(\"Surname 1\") || "
+ "          CASE \"Suffix 1\" "
+ "           WHEN '' THEN '' "
+ "           ELSE ' ' || \"Suffix 1\" "
+ "          END "
+ "          ||', '||   "
+ "          \"Prefix 1\" ||' '||   "
+ "          \"Given 1\" || "
+ "          CASE "
+ "           WHEN \"Nickname 1\" NOT LIKE '' "
+ "            THEN ' \"'|| \"Nickname 1\" ||'\"' "
+ "           ELSE '' "
+ "          END  "
+ "       AS 'Person 1', "
+ "" // "
+ "       \"MRIN\", "
+ "       \"RIN 2\",  "
+ "" // Concatenate Spouse Name fields "
+ "       UPPER(\"Surname 2\") || "
+ "          CASE \"Suffix 2\" "
+ "           WHEN '' THEN '' "
+ "           ELSE ' ' || \"Suffix 2\" "
+ "          END "
+ "          ||', '||   "
+ "          \"Prefix 2\" ||' '||   "
+ "          \"Given 2\" || "
+ "          CASE "
+ "           WHEN \"Nickname 2\" NOT LIKE '' "
+ "            THEN ' \"'|| \"Nickname 2\" ||'\"' "
+ "           ELSE '' "
+ "          END  "
+ "       AS 'Person 2', "
+ "" //        "
+ "" //       \"Records\", "
+ "       \"CitID\", "
+ "       \"SrcID\", "
+ "       \"Template\", "
+ "       \"TpltID\", "
+ "       CASE WHEN \"TpltID\" < 10000  "
+ "           THEN 'OEM'  "
+ "           ELSE 'USR' "
+ "       END AS 'Tplt Type' "
+ " "
+ "FROM  "
+ "( "
+ " (  " // Start of UNION of 5 SELECT queries of the database "
+ "" // Person Citations "
+ "SELECT   CASE "
+ "          WHEN SourceTable.TemplateID=0 THEN 'Free Form' "
+ "          WHEN SourceTemplateTable.TemplateID THEN SourceTemplateTable.Name COLLATE NOCASE "
+ "          ELSE 'ERROR: source template does not exist' "
+ "         END AS 'Template', "
+ "         SourceTable.TemplateID AS 'TpltID', "
+ "         SourceTable.SourceID AS 'SrcID', "
+ "         SourceTable.Name COLLATE NOCASE AS 'Source Name', "
+ "         SourceTable.Fields AS 'SrcFields', "
+ "         CitationTable.CitationID AS 'CitID', "
+ "         CitationTable.Fields AS 'CitFields', "
+ "         CitationTable.Quality AS 'Quality', "
+ "         CitationTable.ActualText AS 'Citation Text', "
+ "         CitationTable.Comments AS 'Citation Comment', "
+ "         'Person' AS 'Citation Type', "
+ "         '(Person)' AS 'Fact Type', "
+ "         NULL AS 'rawDate',          "
+ "         NULL AS 'Event Place',          "
+ "         NULL AS 'Event Site', "
+ "         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     CitationTable  "
+ "         LEFT JOIN SourceTable USING (SourceID) "
+ "         LEFT JOIN SourceTemplateTable USING (TemplateID) "
+ "         INNER JOIN NameTable ON "
+ "         CitationTable.OwnerID = NameTable.OwnerID "
+ "WHERE    CitationTable.OwnerType = 0 AND +NameTable.IsPrimary = 1 "
+ "GROUP BY \"CitID\" "
+ "  "
+ "UNION ALL "
+ "  "
+ "" // Family Citations "
+ "SELECT   CASE "
+ "          WHEN SourceTable.TemplateID=0 THEN 'Free Form' "
+ "          WHEN SourceTemplateTable.TemplateID THEN SourceTemplateTable.Name COLLATE NOCASE "
+ "          ELSE 'ERROR: source template does not exist' "
+ "         END AS 'Template', "
+ "         SourceTable.TemplateID AS 'TpltID', "
+ "         SourceTable.SourceID AS 'SrcID', "
+ "         SourceTable.Name COLLATE NOCASE AS 'Source Name', "
+ "         SourceTable.Fields AS 'SrcFields', "
+ "         CitationTable.CitationID AS 'CitID', "
+ "         CitationTable.Fields AS 'CitFields', "
+ "         CitationTable.Quality AS 'Quality', "
+ "         CitationTable.ActualText AS 'Citation Text', "
+ "         CitationTable.Comments AS 'Citation Comment', "
+ "         'Family' AS 'Citation Type', "
+ "         '(Family)' AS 'Fact Type', "
+ "         NULL AS 'rawDate', "
+ "         NULL AS 'Event Place',          "
+ "         NULL AS 'Event Site', "
+ "         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     CitationTable  "
+ "         LEFT JOIN SourceTable USING (SourceID) "
+ "         LEFT JOIN SourceTemplateTable USING (TemplateID) "
+ "         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 \"CitID\" "
+ "  "
+ "UNION ALL "
+ "  "
+ "" // Person Fact Citations "
+ "SELECT   CASE "
+ "          WHEN SourceTable.TemplateID=0 THEN 'Free Form' "
+ "          WHEN SourceTemplateTable.TemplateID THEN SourceTemplateTable.Name COLLATE NOCASE "
+ "          ELSE 'ERROR: source template does not exist' "
+ "         END AS 'Template', "
+ "         SourceTable.TemplateID AS 'TpltID', "
+ "         SourceTable.SourceID AS 'SrcID', "
+ "         SourceTable.Name COLLATE NOCASE AS 'Source Name', "
+ "         SourceTable.Fields AS 'SrcFields', "
+ "         CitationTable.CitationID AS 'CitID', "
+ "         CitationTable.Fields AS 'CitFields', "
+ "         CitationTable.Quality AS 'Quality', "
+ "         CitationTable.ActualText AS 'Citation Text', "
+ "         CitationTable.Comments AS 'Citation Comment', "
+ "         'Fact - Person' AS 'Citation Type', "
+ "         FactTypeTable.Name COLLATE NOCASE AS 'Fact Type', "
+ "         EventTable.Date AS 'rawDate', "
+ "         PlaceTable.Name AS 'Event Place',          "
+ "         SiteTable.Name AS 'Event Site', "
+ "         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     CitationTable  "
+ "         LEFT JOIN SourceTable USING (SourceID) "
+ "         LEFT JOIN SourceTemplateTable USING (TemplateID) "
+ "         INNER JOIN EventTable ON "
+ "         CitationTable.OwnerID = EventTable.EventID "
+ "         INNER JOIN FactTypeTable ON "
+ "         EventTable.EventType = FactTypeTable.FactTypeID "
+ "         INNER JOIN NameTable ON "
+ "         EventTable.OwnerID = NameTable.OwnerID          "
+ "         LEFT JOIN PlaceTable USING (PlaceID)          "
+ "         LEFT JOIN PlaceTable AS SiteTable ON          "
+ "         EventTable.SiteID=SiteTable.PlaceID "
+ "WHERE    CitationTable.OwnerType = 2 AND +NameTable.IsPrimary = 1 AND "
+ "         EventTable.OwnerType = 0 "
+ "GROUP BY \"CitID\" "
+ "  "
+ "UNION ALL "
+ "  "
+ "" // Family Fact Citations "
+ "SELECT   CASE "
+ "          WHEN SourceTable.TemplateID=0 THEN 'Free Form' "
+ "          WHEN SourceTemplateTable.TemplateID THEN SourceTemplateTable.Name COLLATE NOCASE "
+ "          ELSE 'ERROR: source template does not exist' "
+ "         END AS 'Template', "
+ "         SourceTable.TemplateID AS 'TpltID', "
+ "         SourceTable.SourceID AS 'SrcID', "
+ "         SourceTable.Name COLLATE NOCASE AS 'Source Name', "
+ "         SourceTable.Fields AS 'SrcFields', "
+ "         CitationTable.CitationID AS 'CitID', "
+ "         CitationTable.Fields AS 'CitFields', "
+ "         CitationTable.Quality AS 'Quality', "
+ "         CitationTable.ActualText AS 'Citation Text', "
+ "         CitationTable.Comments AS 'Citation Comment', "
+ "         'Fact - Family' AS 'Citation Type', "
+ "         FactTypeTable.Name COLLATE NOCASE AS 'Fact Type', "
+ "         EventTable.Date AS 'rawDate', "
+ "         PlaceTable.Name AS 'Event Place',          "
+ "         SiteTable.Name AS 'Event Site', "
+ "         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     CitationTable  "
+ "         LEFT JOIN SourceTable USING (SourceID) "
+ "         LEFT JOIN SourceTemplateTable USING (TemplateID) "
+ "         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 "
+ "         LEFT JOIN PlaceTable USING (PlaceID)          "
+ "         LEFT JOIN PlaceTable AS SiteTable ON          "
+ "         EventTable.SiteID=SiteTable.PlaceID "
+ "WHERE    CitationTable.OwnerType = 2 AND +NameTable1.IsPrimary = 1 AND "
+ "         +NameTable2.IsPrimary = 1 AND EventTable.OwnerType = 1 "
+ "GROUP BY \"CitID\" "
+ "  "
+ "UNION ALL "
+ "  "
+ "" // Alternate Name Fact Citations "
+ "SELECT   CASE "
+ "          WHEN SourceTable.TemplateID=0 THEN 'Free Form' "
+ "          WHEN SourceTemplateTable.TemplateID THEN SourceTemplateTable.Name COLLATE NOCASE "
+ "          ELSE 'ERROR: source template does not exist' "
+ "         END AS 'Template', "
+ "         SourceTable.TemplateID AS 'TpltID', "
+ "         SourceTable.SourceID AS 'SrcID', "
+ "         SourceTable.Name COLLATE NOCASE AS 'Source Name', "
+ "         SourceTable.Fields AS 'SrcFields', "
+ "         CitationTable.CitationID AS 'CitID', "
+ "         CitationTable.Fields AS 'CitFields', "
+ "         CitationTable.Quality AS 'Quality', "
+ "         CitationTable.ActualText AS 'Citation Text', "
+ "         CitationTable.Comments AS 'Citation Comment', "
+ "         'Fact - Alt Name' AS 'Citation Type', "
+ "         'Alt Name' AS 'Fact Type', "
+ "         NameTable1.Date AS 'rawDate', "
+ "         NULL AS 'Event Place',          "
+ "         NULL AS 'Event Site', "
+ "         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     CitationTable  "
+ "         LEFT JOIN SourceTable USING (SourceID) "
+ "         LEFT JOIN SourceTemplateTable USING (TemplateID) "
+ "         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 \"CitID\" "
+ "" // end of the core UNION of 5 SELECT queries of the database that is then queried by the first SELECT "
+ " ) "
+ "LEFT JOIN "
+ "" // count media items linkd to citation "
+ "(SELECT MediaLinkTable.OwnerID AS CitID, COUNT() AS Media "
+ "FROM multimediatable "
+ "LEFT JOIN MediaLinkTable USING(MediaID) "
+ "WHERE MediaLinkTable.OwnerType=4 "
+ "GROUP BY CitID) "
+ "USING (CitID) "
+ ")   "
+ "" // Sort order - this one clusters identical Source Details Text for a given Source "
+ "ORDER BY \"Source Name\", \"Citation Text\", \"Citation Comment\", \"Person 1\", \"Fact Type\", \"EventDate\", \"Person 2\" "
+ "" // Sort order - this one clusters a person's citations for a given Source "
+ "" // ORDER BY \"Source Name\", \"Person 1\", \"Fact Type\", \"EventDate\", \"Person 2\", \"Citation Text\", \"Citation Comment\" "
+ "; " // END OF FILE 
;                                                           
                return commandText;
            }
        }

        public static string dBSourcesMasterSources 
        { get
            {
                string commandText = ""
+ "" // MasterSources.sql "
+ "" // ve3meo 2011-11-04 "
+ "" // Lists Master Sources and fields from SourceTable with  "
+ "" // name/id of Source Template and number of citations "
+ "" // 2011-11-06 now reports Free Form and orphaned citations; count of media items linked to master source; improved format of SrcFields "
+ " "
+ "" // BEGIN "
+ "SELECT   SourceTable.SourceID AS 'SrcID', "
+ "         SourceTable.Name COLLATE NOCASE AS 'Source Name', "
+ "         SourceTable.RefNumber, "
+ "" // SourceTable FIELDS parsed out from XML in a blob "
+ "         REPLACE(                                                                                                 "
+ "          REPLACE( "
+ "           REPLACE( "
+ "            REPLACE( "
+ "             REPLACE( "
+ "              REPLACE(SUBSTR(CAST(SourceTable.FIELDS AS TEXT),55, LENGTH(CAST(SourceTable.FIELDS AS TEXT))-87), '</Name>', ':'||CAST (X'09' AS TEXT)), "
+ "             '<Field><Name>', ''), "
+ "            '</Value>',''), "
+ "           '<Value>',''), "
+ "          '<Value/>',''), "
+ "         '</Field>',CAST (X'0D' AS TEXT)) "
+ "         AS 'Src Fields', "
+ "" // "
+ "         SourceTable.ActualText, "
+ "         SourceTable.Comments, "
+ "         MediaCtr, "
+ "         SourceTable.IsPrivate, "
+ "         COUNT(1) AS Citations, "
+ "         CASE "
+ "          WHEN SourceTable.TemplateID=0 THEN 'Free Form' "
+ "          WHEN SourceTemplateTable.TemplateID THEN SourceTemplateTable.Name COLLATE NOCASE "
+ "          ELSE 'ERROR: source template does not exist' "
+ "         END AS 'Template', "
+ "         SourceTable.TemplateID AS 'TpltID' "
+ "FROM     CitationTable  "
+ "         LEFT JOIN SourceTable USING (SourceID) "
+ "         LEFT JOIN SourceTemplateTable USING (TemplateID) "
+ "         LEFT JOIN "
+ "" // count media items linkd to master source "
+ "(SELECT MediaLinkTable.OwnerID AS SourceID, COUNT() AS MediaCtr "
+ "FROM multimediatable "
+ "LEFT JOIN MediaLinkTable USING(MediaID) "
+ "WHERE MediaLinkTable.OwnerType=3 "
+ "GROUP BY SourceID  "
+ ") "
+ "USING (SourceID) "
+ "GROUP BY SourceTable.SourceID "
+ "ORDER BY \"Source Name\" "
+ "; " // END "
;
                return commandText;
            }
        }

        public static string dBToDoToDoList
        { 
            get
                {
                string commandText = ""
+ "" // To-Do.sql "
+ "" // 1st SELECT gets tasks for Individuals and General tasks  "
+ "SELECT  "
+ "  R.Ownerid AS RIN ,  "
+ "  COALESCE(  "
+ "   UPPER(N.Surname) COLLATE Nocase ||' '||  "
+ "   N.Suffix COLLATE Nocase ||', '||  "
+ "   N.Prefix COLLATE Nocase ||' '|| "
+ "   N.Given COLLATE Nocase,   'General') AS Subject,  "
+ "  N.Birthyear AS Born ,  "
+ "  N.Deathyear AS Died ,  "
+ "  R.Refnumber AS File ,  "
+ "  R.NAME COLLATE Nocase AS Task,  "
+ "  Substr( 'OPEN     ,COMPLETED,PENDING  ,PROBLEM', R.Status*10+1,9) AS 'Status' ,  "
+ "  R.Priority AS Pty ,  "
+ " (Substr(R.Date1,4,4)||'-'||Substr(R.Date1,8,2)||'-'||Substr(R.Date1,10,2))AS'Started',  "
+ " (Substr(R.Date2,4,4)||'-'||Substr(R.Date2,8,2)||'-'||Substr(R.Date2,10,2))AS'LastWorked', "
+ " (Substr(R.Date3,4,4)||'-'||Substr(R.Date3,8,2)||'-'||Substr(R.Date3,10,2))AS'Completed', "
+ "  Quote( R.Details ) AS Details,  "
+ "  A.NAME COLLATE NOCASE AS Repository ,  "
+ "  A.Street1 AS Adr1 ,  "
+ "  A.Street2 AS Adr2 ,  "
+ "  A.City AS 'City',  "
+ "  A.State AS 'State' "
+ "FROM  "
+ "  Researchtable R   LEFT JOIN Addresslinktable Al ON ( R.Taskid = Al.Ownerid AND Al.Ownertype = 6 ) "
+ "  LEFT JOIN Addresstable A ON Al.Addressid = A.Addressid  "
+ "  LEFT JOIN Nametable N ON R.Ownerid = N.Ownerid  AND N.Isprimary = 1 " // 0=Alternate Name, 1=Primary Name "
+ "  LEFT JOIN FamilyTable F ON R.OwnerID = F.FamilyID AND F.FatherID=N.Ownerid AND R.OwnerType=1 AND N.Isprimary = 1 "
+ "WHERE  "
+ "  Status NOT LIKE 1 " // 0=OPEN, 1=COMPLETED, 2=PENDING, 3=PROBLEM  AND R.OwnerType <>1 " // suppress Family tasks because wrong name linked   "
+ "  " // Add other filter criteria, e.g. AND Subject LIKE 'surname%' "
+ "  AND R.OwnerType<>1 " // suppress Family "
+ "UNION ALL "
+ "" // 2nd SELECT gets Family related tasks with name and RIN of Father "
+ "SELECT  "
+ "  R.Ownerid AS RIN ,  "
+ "  'Family of '||UPPER(N.Surname) COLLATE Nocase ||' '||  "
+ "   N.Suffix COLLATE Nocase ||', '||  "
+ "   N.Prefix COLLATE Nocase ||' '|| "
+ "   N.Given COLLATE Nocase AS Subject,  "
+ "  N.Birthyear AS Born ,  "
+ "  N.Deathyear AS Died ,  "
+ "  R.Refnumber AS File ,  "
+ "  R.NAME COLLATE Nocase AS Task,  "
+ "  Substr( 'OPEN     ,COMPLETED,PENDING  ,PROBLEM', R.Status*10+1,9) AS 'Status' ,  "
+ "  R.Priority AS Pty ,  "
+ " (Substr(R.Date1,4,4)||'-'||Substr(R.Date1,8,2)||'-'||Substr(R.Date1,10,2))AS'Started',  "
+ " (Substr(R.Date2,4,4)||'-'||Substr(R.Date2,8,2)||'-'||Substr(R.Date2,10,2))AS'LastWorked', "
+ " (Substr(R.Date3,4,4)||'-'||Substr(R.Date3,8,2)||'-'||Substr(R.Date3,10,2))AS'Completed', "
+ "  Quote( R.Details ) AS Details,  "
+ "  A.NAME COLLATE NOCASE AS Repository ,  "
+ "  A.Street1 AS Adr1 ,  "
+ "  A.Street2 AS Adr2 ,  "
+ "  A.City ,  "
+ "  A.State  "
+ "FROM  "
+ "  Researchtable R   LEFT JOIN Addresslinktable Al ON ( R.Taskid = Al.Ownerid AND Al.Ownertype = 6 ) "
+ "  LEFT JOIN Addresstable A ON Al.Addressid = A.Addressid  "
+ "  INNER JOIN FamilyTable F ON R.OwnerID = F.FamilyID AND R.OwnerType=1  "
+ "  INNER JOIN Nametable N ON F.FatherID = N.Ownerid AND N.Isprimary = 1 " // 0=Alternate Name, 1=Primary Name "
+ "WHERE  "
+ "  Status NOT LIKE 1 " // 0=OPEN, 1=COMPLETED, 2=PENDING, 3=PROBLEM  AND R.OwnerType <>1 " // suppress Family tasks because wrong name linked   "
+ "  " // Add other filter criteria, e.g. AND Subject LIKE '%surname%' "
+ "ORDER BY  "
+ "  Subject " // or BY RIN, Task, Status, Repository or combination separated by comma "
+ "" // END OF QUERY - DO NOT DELETE trailing semi-colon "
+ "  ; "
+ " "
+ " ";
                return commandText;
                }
        }

        public static string dBEventsSameDateSort 
        { 
            get
                {
                string commandText = ""
+ "" //  SortDateSameDayOrderCustom.sql "
+ "" //   2011-12-20 ve3meo "
+ "" //   Alters SortDates of any set of Fact types  "
+ "" //   to a natural order when any pair or more occur on the same date.  "
+ "" //   Could be extended to order other facts also. SortDates are effectively assigned  "
+ "" //   (by arithmetical offsets) an absolute suffix -10, -20, ... related to the rank of the FactType. "
+ "" //   Affects only those events whose SortDates correspond to the Fact Date, as computed  "
+ "" //   by a Date encoding algorithm. The algorithm does not handle Date modifiers so not all  "
+ "" //   Event dates are handled, e.g. \"Bef 1960\". "
+ "" // rev 2012-02-14 ve3meo changed gap to 10 between events;   "
+ " "
+ "DROP TABLE IF EXISTS TmpFactOrder "
+ "; "
+ "CREATE TEMP TABLE IF NOT EXISTS TmpFactOrder "
+ "(Rank INTEGER PRIMARY KEY, FactName TEXT) "
+ "; "
+ " "
+ "/* list of Fact Names, standard and custom, to be sorted, in rank order. "
+ "   Revise the list to suit your needs */ "
+ "INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Stillborn'); "
+ "INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Birth'); "
+ "INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Christen'); "
+ "INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Baptism'); "
+ "INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Divorce'); " // family events not yet handled "
+ "INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Marriage'); " // family events not yet handled "
+ "INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Death'); "
+ "INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Funeral'); "
+ "INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Cremation'); "
+ "INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Burial'); "
+ "INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Obituary'); "
+ "INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Memorial'); "
+ "/* revise SortDates */ "
+ "UPDATE EventTable  "
+ "SET SortDate = SortDate "
+ "  -6692012023  " // this offset goes to Date-1 if the event is a ranked event "
+ "  *( ( "
+ "      SELECT Rank  "
+ "      FROM TmpFactOrder,  "
+ "           FactTypeTable  "
+ "      WHERE FactName LIKE Name  "
+ "      AND FactTypeID = EventType "
+ "      )>0 "
+ "    )   "
+ "  +1048576  " // this offset adds steps of 10 to Date-1 multiplied by (rank) "
+ "  *(10 * ( "
+ "      SELECT Rank  "
+ "      FROM TmpFactOrder,  "
+ "           FactTypeTable  "
+ "      WHERE FactName LIKE Name  "
+ "      AND FactTypeID = EventType "
+ "      ) "
+ "      -1 "
+ "    ) " // maps the FactType to its order "
+ "WHERE EventID  "
+ "IN (SELECT EventID FROM EventTable "
+ "    INNER JOIN  "
+ "    (SELECT     " // matching dates "
+ "     SortDate, OwnerID, COUNT()-1 AS Matches  "
+ "     FROM EventTable "
+ "     INNER JOIN FactTypeTable "
+ "     ON EventType = FactTypeID   "
+ "     WHERE EventTable.OwnerType = 0  "
+ "     AND Name IN (SELECT FactName FROM TmpFactOrder)  "
+ "     AND  "
+ "     SortDate =   " // equals encoded event Date (if not a match, suggests that user has modified SortDate so don't touch it) "
+ "       (CASE  "
+ "        WHEN Date LIKE '.%'  "
+ "        THEN 1  "
+ "        ELSE Substr(Date,3,5) END  "
+ "        +10000 "
+ "        )*562949953421312  "
+ "        + Substr(Date,8,2)*35184372088832  "
+ "        + Substr(Date,10,2)*549755813888  "
+ "        + 17178820620  "
+ "      GROUP BY SortDate, OwnerID, EventTable.OwnerType "
+ "     ) "
+ "     USING (OwnerID, SortDate) "
+ "     INNER JOIN FactTypeTable "
+ "     ON EventType = FactTypeID   "
+ "     WHERE Matches  "
+ "     AND EventTable.OwnerType = 0  "
+ "     AND Name IN (SELECT FactName FROM TmpFactOrder)  "
+ "    ) "
+ "; "
+ "SELECT 'Completed the natural ordering of events on the same date.' AS Status "
+ "; ";
                return commandText;
                }
        }

                public static string dBReportsParagraphStrip 
                { 
                    get
                        {
                            string commandText = ""
    + "" // Paragraph-strip.sql "
    + "" //   2011-12-22 by ve3meo "
    + "" //   Strips CR/LF and spaces from around Name and Event custom sentence templates and Person, Family, Name and Event Notes. "
    + "" //   2012-02-14 ve3meo: removed block comments and added an endong SELECT for use with RMtrix "
    + " "
    + " "
    + "" // Reiterate the following query until no further results to remove leading CRLF from the custom sentence "
    + "UPDATE EventTable SET Sentence = SUBSTR (Sentence,3,LENGTH(Sentence)) WHERE Sentence LIKE CAST (X'0D0A' AS TEXT)||'%' "
    + "; "
    + "" // Reiterate the following query until no further results to remove leading CRLF from the Note "
    + "UPDATE EventTable SET Note = SUBSTR (Note,3,LENGTH(Note)) WHERE Note LIKE CAST (X'0D0A' AS TEXT)||'%' "
    + "; "
    + "" // Reiterate the following query until no further results to remove trailing CRLF from the Note "
    + "UPDATE EventTable SET Note = SUBSTR (Note,1,LENGTH(Note)-2) WHERE Note LIKE '%' || CAST (X'0D0A' AS TEXT) "
    + "; "
    + "" // Reiterate the following query until no further results to remove leading CRLF from the Note "
    + "UPDATE PersonTable SET Note = SUBSTR (Note,3,LENGTH(Note)) WHERE Note LIKE CAST (X'0D0A' AS TEXT)||'%' "
    + "; "
    + "" // Reiterate the following query until no further results to remove trailing CRLF from the Note "
    + "UPDATE PersonTable SET Note = SUBSTR (Note,1,LENGTH(Note)-2) WHERE Note LIKE '%' || CAST (X'0D0A' AS TEXT) "
    + "; "
    + "" // Reiterate the following query until no further results to remove leading CRLF from the Note "
    + "UPDATE FamilyTable SET Note = SUBSTR (Note,3,LENGTH(Note)) WHERE Note LIKE CAST (X'0D0A' AS TEXT)||'%' "
    + "; "
    + "" // Reiterate the following query until no further results to remove trailing CRLF from the Note "
    + "UPDATE FamilyTable SET Note = SUBSTR (Note,1,LENGTH(Note)-2) WHERE Note LIKE '%' || CAST (X'0D0A' AS TEXT) "
    + "; "
    + "" // Reiterate the following query until no further results to remove leading CRLF from the custom sentence "
    + "UPDATE NameTable SET Sentence = SUBSTR (Sentence,3,LENGTH(Sentence)) WHERE Sentence LIKE CAST (X'0D0A' AS TEXT)||'%' "
    + "; "
    + "" // Reiterate the following query until no further results to remove leading CRLF from the Note "
    + "UPDATE NameTable SET Note = SUBSTR (Note,3,LENGTH(Note)) WHERE Note LIKE CAST (X'0D0A' AS TEXT)||'%' "
    + "; "
    + "" // Reiterate the following query until no further results to remove trailing CRLF from the Note "
    + "UPDATE NameTable SET Note = SUBSTR (Note,1,LENGTH(Note)-2) WHERE Note LIKE '%' || CAST (X'0D0A' AS TEXT) "
    + "; "
    + "" // Remove leading and trailing spaces "
    + "UPDATE EventTable SET Sentence = TRIM(Sentence) "
    + "; "
    + "UPDATE EventTable SET NOTE = TRIM(Note) "
    + "; "
    + "UPDATE PersonTable SET NOTE = TRIM(Note) "
    + "; "
    + "UPDATE FamilyTable SET NOTE = TRIM(Note) "
    + "; "
    + "UPDATE NameTable SET Sentence = TRIM(Sentence) "
    + "; "
    + "UPDATE NameTable SET NOTE = TRIM(Note) "
    + "; "
    + "SELECT 'Completed one pass at stripping leading and trailing white' || X'0D0A' ||  "
    + "       'space and line feeds from custom sentences and notes.'  || X'0D0A' || "
    + "       'Repeat as needed.' AS Status "
    + "; "; 
                        return commandText;
                        }
                }

                public static string dBReportsParagraphAdd
                {
                    get
                    {
                        string commandText = ""
+ "" // Paragraph-add.sql "
+ "" //   2011-12-22 ve3meo "
+ "" //   Adds trailing double CR/LF to Notes of Family and Individual Events. "
+ "" //   Removes last pair of CR/LF from the last Person Fact or Name Note unless  "
+ "" //   followed by a Person General note. "
+ "" //   Does not yet work on Family notes.  "
+ "" // 2012-02-14 ve3meo removed block comments; added SELECT status at end for RMtrix "
+ " "
+ "UPDATE EventTable SET Note = Note || CAST (X'0D0A0D0A' AS TEXT) WHERE LENGTH (Note) > 0 "
+ "; "
+ "" //UPDATE PersonTable SET Note = Note || CAST (X'0D0A0D0A' AS TEXT) WHERE LENGTH (Note) > 0 "
+ "; "
+ "UPDATE FamilyTable SET Note = Note || CAST (X'0D0A0D0A' AS TEXT) WHERE LENGTH (Note) > 0 "
+ "; "
+ "UPDATE NameTable SET Note = Note || CAST (X'0D0A0D0A' AS TEXT) WHERE LENGTH (Note) > 0 "
+ "; "
+ " "
+ "DROP TABLE IF EXISTS LastNoteLog  "
+ "; "
+ " "
+ "CREATE TEMP TABLE IF NOT EXISTS LastNoteLog "
+ "AS "
+ "" // Person general note exists "
+ "SELECT PersonID, Null AS NameID, Null AS EventID, Null AS SortDate, 'PG' AS Type FROM PersonTable WHERE Note NOT LIKE '' "
+ "; "
+ " "
+ "INSERT INTO LastNoteLog "
+ "SELECT * " // Last Indiv Event ID "
+ "FROM "
+ "  ( "
+ "   SELECT OwnerID AS PersonID, Null AS NameID, EventID, SortDate, 'PF' AS Type FROM EventTable WHERE OwnerType=0 "
+ "   ORDER BY SortDate "
+ "   ) "
+ "GROUP BY PersonID "
+ ";  "
+ " "
+ "INSERT INTO LastNoteLog  "
+ "SELECT * " // Last Alt Name "
+ "FROM "
+ "  ( "
+ "   SELECT OwnerID AS PersonID, NameID, Null AS EventID, SortDate, 'AN' AS Type FROM NameTable WHERE NOT +IsPrimary "
+ "   ORDER BY SortDate "
+ "   ) "
+ "GROUP BY PersonID "
+ ";  "
+ " "
+ "" // Delete trailing CR/LF from last Alt Name Note if there is NOT a Person general note AND no later Person Fact "
+ "UPDATE NameTable  "
+ "SET Note = SUBSTR (Note, 1, LENGTH(Note)-4)  "
+ "" //SELECT * FROM NameTable "
+ "WHERE Note NOT LIKE ''  "
+ "AND NameID IN "
+ "  ( "
+ "     SELECT NameID FROM LastNoteLog "
+ "    ) "
+ "AND OwnerID  "
+ "NOT IN "
+ "   ( "
+ "    SELECT PersonID FROM LastNoteLog WHERE Type LIKE 'PG' "
+ "    ) "
+ "AND SortDate > (SELECT SortDate FROM LastNoteLog WHERE Type LIKE 'PF' AND OwnerID = PersonID) "
+ "; "
+ " "
+ "" // Delete trailing CR/LF from last Person Fact Note if there is NOT a Person general note AND no later Alt Name Fact "
+ "UPDATE EventTable  "
+ "SET Note = SUBSTR (Note, 1, LENGTH(Note)-4)  "
+ "" //SELECT * FROM EventTable "
+ "WHERE Note NOT LIKE ''  "
+ "AND EventID IN "
+ "  ( "
+ "     SELECT EventID FROM LastNoteLog "
+ "    ) "
+ "AND OwnerID  "
+ "NOT IN "
+ "   ( "
+ "    SELECT PersonID FROM LastNoteLog WHERE Type LIKE 'PG' "
+ "    ) "
+ "AND SortDate > (SELECT SortDate FROM LastNoteLog WHERE Type LIKE 'AN' AND OwnerID = PersonID) "
+ "; "
+ "SELECT 'Completed best efforts paragraphing after fact notes for Person.' || X'0D0A' || 'Family and children notes unaffected' AS Status "
+ "; ";
                        return commandText;
                    }
                }

        public static string dBReportsDummyFamilyAdd 
        { 
            get
                {
                string commandText = ""
 + "" // DummyFamily-Add.sql "
+ "" // Based on trick developed by Jerry Bryan to cause Narrative Descendant reports "
+ "" // to output childless, spouseless children in their own generation, along with their  "
+ "" // married or parental siblings. "
+ "" //  "
+ "" // Adds dummy family(ies) for this(all such) child(ren), based on the runtime parameter, "
+ "" // to the FamilyTable. Requires SQLite Export Personal or equiv to support selection of a single "
+ "" // child. "
+ "" //  "
+ "" // After completing the report, run DummyFamily-Delete.sql; it is probably less risky, however, "
+ "" // to run the first query and report on a copy of the database and then delete the copy. "
+ "" // 2012-01-15 ve3meo "
+ "" //  "
+ "  "
+ "INSERT INTO FamilyTable  "
+ " (FatherID) "
+ "SELECT ChildTable.ChildID FROM ChildTable "
+ "WHERE "
//+ " CASE  "
//+ "  WHEN @RIN NOT LIKE '' THEN ChildTable.ChildID = @RIN "
//+ "  ELSE 1 "
//+ " END  "
//+ "AND "
+ " ChildTable.ChildID NOT IN (SELECT FatherID FROM FamilyTable)  "
+ "AND "
+ " ChildTable.ChildID NOT IN (SELECT MotherID FROM FamilyTable) "
+ ";  "
+ "SELECT 'Completed adding a dummy family to each childless, spouseless child.' || X'0D0A' || "
+ "       'Run Dummy Family - Delete after completing the RootsMagic reports.' AS Status "
+ "; "
+ " ";
                return commandText;
                }
        }

          public static string dBReportsDummyFamilyDelete
        { 
            get
                {
                string commandText = ""
+ "" // DummyFamily-Delete.sql "
+ "" // Reverses the changes made by DummyFamily-Add.sql to the FamilyTable. "
+ "" //  "
+ "" // After completing the report, run DummyFamily-Delete.sql; it is probably less risky, however, "
+ "" // to run the first query and report on a copy of the database and then delete the copy. "
+ "" // 2012-01-15 ve3meo "
+ "" // 2012-02-14 rev Remove navigation aid for Family View "
+ "  "
+ "DELETE FROM FamilyTable "
+ "WHERE "
+ " FatherID  "
+ " IN "
+ "  ( "
+ "   SELECT FatherID FROM FamilyTable "
+ "   WHERE "
// + "    CASE  "
// + "     WHEN @RIN NOT LIKE '' THEN FatherID = @RIN "
// + "     ELSE 1 "
// + "    END   "
// + "   AND "
+ "    MotherID IS NULL  "
+ "   AND "
+ "    ChildID IS NULL "
+ "  )   "
+ "; "
+ "" // Remove Navigation aid for Family View "
+ "UPDATE PersonTable  "
+ "SET SpouseID=0  "
+ "WHERE SpouseID  "
+ "NOT IN  "
+ " ( "
+ "  SELECT FamilyID  "
+ "  FROM FamilyTable "
+ "  ) "
+ "; "
+ "SELECT 'Completed deleting the dummy family from each childless, spouseless child.' AS Status "
+ "; ";
                return commandText;
                }
        }

          public static string dBPeopleNamelessPeopleDelete
                { 
                    get
                        {
                            string commandText = ""
    + "" // Nameless People i.e. no record in NameTable for that RIN in PersonTable "
    + "DELETE FROM PersonTable  "
    + "WHERE PersonID "
    + "IN "
    + "( "
    + "SELECT PersonID "
    + "FROM PersonTable P  "
    + "LEFT JOIN nametable N "
    + "ON P.PersonID = N.OwnerID  AND n.ISPRIMARY "
    + "WHERE N.nameid ISNULL "
    + "); "
    + "SELECT 'Completed deleting nameless persons from PersonTable' AS Status; "; 
                        return commandText;
                        }
                }

          public static string dBPeopleResolvedDuplicateNames 
                { 
                    get
                        {
                        string commandText = ""
+ "" // Resolved* Duplicate Name Pairs,  "
+ "" // flagged as \"Not a Problem\".  "
+ "" // flags lost on transfer  "
+ "SELECT  "
+ "N1.Surname || ', ' || N1.Given || ' - ' || X.ID1 ||  "
+ " CASE WHEN N1.BirthYear != 0 THEN ' b' || N1.BirthYear ELSE '' END ||  "
+ " CASE WHEN N1.DeathYear != 0 THEN ' d' || N1.DeathYear ELSE '' END  "
+ " AS Name1, "
+ "N2.Surname || ', ' || N2.Given || ' - ' || X.ID2 ||  "
+ " CASE WHEN N2.BirthYear != 0 THEN ' b' || N2.BirthYear ELSE '' END ||  "
+ " CASE WHEN N2.DeathYear != 0 THEN ' d' || N2.DeathYear ELSE '' END  "
+ " AS Name2 "
+ "FROM ExclusionTable AS X  "
+ "INNER JOIN NameTable AS N1  "
+ "ON X.ID1 = N1.OwnerID "
+ "INNER JOIN NameTable AS N2  "
+ "ON X.ID2 = N2.OwnerID "
+ "WHERE X.ExclusionType = 1 AND +N1.IsPrimary AND +N2.IsPrimary "
+ "ORDER BY Name1, Name2 "
+ "; ";
                        return commandText;
                        }
                }


          public static string dBPeopleAltNamesList
                { 
                    get
                        {
                        string commandText = ""
+ "" // Alternate Names List "
+ "SELECT  "
+ " N.Surname || CASE WHEN N.Suffix NOT LIKE '' THEN ' ' || N.Suffix ELSE '' END  "
+ " || ', '  "
+ " || CASE WHEN N.Prefix NOT LIKE '' THEN N.Prefix || ' ' ELSE '' END || N.Given  "
+ " || CASE WHEN N.Nickname NOT LIKE '' THEN ' \"' || N.Nickname || '\"' ELSE '' END "
+ " AS Name, "
+ " N.OwnerID AS RIN, "
+ " Nalt.Surname || CASE WHEN Nalt.Suffix NOT LIKE '' THEN ' ' || Nalt.Suffix ELSE '' END  "
+ " || ', '  "
+ " || CASE WHEN Nalt.Prefix NOT LIKE '' THEN Nalt.Prefix || ' ' ELSE '' END || Nalt.Given  "
+ " || CASE WHEN Nalt.Nickname NOT LIKE '' THEN ' \"' || Nalt.Nickname || '\"' ELSE '' END "
+ " AS AltName, "
+ " CASE Nalt.NameType "
+ " WHEN 0 THEN '' "
+ " WHEN 1 THEN 'AKA' "
+ " WHEN 2 THEN 'Birth' "
+ " WHEN 3 THEN 'Immigrant' "
+ " WHEN 4 THEN 'Maiden' "
+ " WHEN 5 THEN 'Married' "
+ " WHEN 6 THEN 'Nickname' "
+ " WHEN 7 THEN 'Other spelling' "
+ " ELSE '' "
+ " END "
+ " AS NameType "
+ " From NameTable AS Nalt "
+ " LEFT JOIN "
+ " NameTable AS N "
+ " ON Nalt.OwnerID = N.OwnerID "
+ " WHERE NOT +Nalt.IsPrimary AND +N.IsPrimary "
+ " ORDER BY Name, AltName "
+ "; ";
                        return commandText;
                        }
                }

          public static string dBPeopleAltNamesDeleteOrphans 
                { 
                    get
                        {
                        string commandText = ""
+ "" // Orphaned Alternate names DELETE "
+ "DELETE FROM NameTable "
+ "WHERE NameID IN "
+ "( "
+ " SELECT Nalt.NameID "
+ " FROM NameTable Nalt "
+ " LEFT JOIN NameTable N "
+ " ON Nalt.ownerid = N.ownerid "
+ " WHERE NOT +Nalt.IsPrimary  "
+ " AND +N.IsPrimary "
+ " AND N.Nameid ISNULL "
+ ") "
+ "; "
+ " "
+ "SELECT 'Completed deleting Orphaned Alternate Names,  "
+ "i.e., those with no Primary name record found.' AS Status"
+ "; ";
                        return commandText;
                        }
                }

          public static string dBPlacesOrphanedPlaceDetailsList
                { 
                    get
                        {
                        string commandText = ""
+ "" // OrphanedPlaceDetails.sql "
+ "" // 2012-02-22 ve3meo "
+ "" // Orphaned Place Details are Place Details for which the parent Place  "
+ "" // does not exist in the PlaceTable. These may arise because RM5021 does "
+ "" // not preclude the entry of such in the Edit Person screen or possibly "
+ "" // from its failure to clean up after mergers and deletions. Some are  "
+ "" // actually used by Facts/Events and come out in some reports (but not "
+ "" // the Place List). Others are no longer accessible from the program. "
+ "" // 2012-03-04 rev added SiteID and MasterID fields "
+ " "
+ "SELECT PlaceTable.Name AS [Orphaned Place Detail], "
+ " CASE EventTable.OwnerType "
+ " WHEN 0 "
+ " THEN "
+ "  NameTable.Surname || ', ' || "
+ "  NameTable.Given || ' - ' || "
+ "  NameTable.OwnerID  "
+ " WHEN 1 "
+ " THEN "
+ "  Husband.Surname || ', ' || "
+ "  Husband.Given || ' - ' || "
+ "  Husband.OwnerID "
+ " END "
+ " AS Person, "
+ " FactTypeTable.Name AS Fact, "
+ " PlaceTable.PlaceID AS SiteID, "
+ " PlaceTable.MasterID "
+ "FROM PlaceTable "
+ "LEFT JOIN EventTable "
+ "ON PlaceTable.PlaceID = SiteID  "
+ "LEFT JOIN FactTypeTable "
+ "ON EventType = FactTypeID "
+ "LEFT JOIN NameTable "
+ "ON EventTable.OwnerID = NameTable.OwnerID AND +NameTable.IsPrimary AND EventTable.OwnerType=0 "
+ "LEFT JOIN FamilyTable "
+ "ON EventTable.OwnerID = FamilyTable.FamilyID AND EventTable.OwnerType=1 "
+ "LEFT JOIN NameTable AS Husband "
+ "ON FamilyTable.FatherID = Husband.OwnerID AND +Husband.IsPrimary "
+ "WHERE PlaceTable.PlaceID IN "
+ "( "
+ "SELECT PlaceID "
+ "FROM PlaceTable "
+ "WHERE PlaceType = 2 "
+ "AND "
+ "( "
+ " MasterID = 0 "
+ " OR "
+ " MasterID NOT IN "
+ " ( "
+ "  SELECT PlaceID "
+ "  FROM PlaceTable "
+ "  WHERE PlaceType = 0 "
+ " ) "
+ ") "
+ ") "
+ "ORDER BY Person "
+ "; ";
                        return commandText;
                        }
                }

          public static string dBPlacesOrphanedPlaceDetailsDeleteUnused 
                { 
                    get
                        {
                        string commandText = ""
+ "" // OrphanedPlaceDetails_DeleteUnused.sql "
+ "" // 2012-02-22 ve3meo "
+ "" // Deletes orphaned Place Details (having no parent Place) "
+ "" // that are NOT used by any event in the EventTable. "
+ " "
+ "DELETE FROM PlaceTable "
+ "WHERE PlaceID "
+ "IN "
+ "( "
+ " SELECT PlaceID "
+ " FROM PlaceTable "
+ " WHERE PlaceType = 2 "
+ " AND "
+ " ( "
+ "  MasterID = 0 "
+ "  OR "
+ "  MasterID NOT IN "
+ " ( "
+ "  SELECT PlaceID "
+ "  FROM PlaceTable "
+ "  WHERE PlaceType = 0 "
+ " ) "
+ ") "
+ "AND PlaceID  "
+ "NOT IN "
+ "(Select SiteID  "
+ " FROM EventTable "
+ " ) "
+ ") "
+ "; "
+ " "
+ "SELECT 'Completed deleting Unused Orphaned Place Details'  "
+ "AS Status "
+ "; ";
                        return commandText;
                        }
                }

          public static string dBMediaListRM5 
                { 
                    get
                        {
                        string commandText = ""
+ "" // MediaList-RM5.sql "
+ "" // 2011-11-06 by ve3meo "
+ "" // Lists all media and the key link ID to where used. Some SQLite managers will display thumbnail image (e.g. SQLite Expert). "
+ "" // The results displayed by some can be copied and pasted (or exported) to Excel which supports the synthesized Hyperlink column "
+ "" // for opening the media file. "
+ "" // 2012-03-02 rev for RM5 "
+ " "
+ "SELECT OwnerID, "
+ "       CASE OwnerType "
+ "              WHEN 0 "
+ "              THEN OwnerID "
+ "       END AS PersonID, "
+ "       CASE OwnerType "
+ "              WHEN 1 "
+ "              THEN OwnerID "
+ "       END AS FamilyID, "
+ "       CASE OwnerType "
+ "              WHEN 2 "
+ "              THEN OwnerID "
+ "       END AS EventID, "
+ "       CASE OwnerType "
+ "              WHEN 3 "
+ "              THEN OwnerID "
+ "       END AS SourceID, "
+ "       CASE OwnerType "
+ "              WHEN 4 "
+ "              THEN OwnerID "
+ "       END AS CitationID, "
+ "       CASE OwnerType "
+ "              WHEN 5 "
+ "              THEN OwnerID "
+ "       END AS PlaceID, "
+ "       MediaID       , "
+ "" //       Thumbnail, "
+ "       MediaPath     , "
+ "       MediaFile     , "
+ "       '=HYPERLINK(\"' "
+ "              ||MediaPath "
+ "              ||MediaFile "
+ "              ||'\",\"Open\")' AS HyperLink, "
+ "       Caption                          , "
+ "       Description                      , "
+ "       " // Convert Fact dates to readable form "
+ "       CASE SUBSTR( rawDate , 1 , 1 ) "
+ "      	WHEN 'Q' THEN rawDate  "
+ "      	WHEN 'T' THEN Substr( rawDate , 2 , 20 )  "
+ "      	WHEN 'D' THEN  "
+ "      		CASE Substr( rawDate , 2 , 1 )  "
+ "      			WHEN 'A' THEN 'aft '  "
+ "      			WHEN 'B' THEN 'bef '  "
+ "      			WHEN 'F' THEN 'from '  "
+ "      			WHEN 'I' THEN 'since '  "
+ "      			WHEN 'R' THEN 'bet '  "
+ "      			WHEN 'S' THEN 'from '  "
+ "      			WHEN 'T' THEN 'to '  "
+ "      			WHEN 'U' THEN 'until '  "
+ "      			WHEN 'Y' THEN 'by '  "
+ "      		ELSE ''  "
+ "      		END  "
+ "      		||  "
+ "      		CASE Substr( rawDate , 13 , 1 )  "
+ "      			WHEN 'A' THEN 'abt '  "
+ "      			WHEN 'C' THEN 'ca '  "
+ "      			WHEN 'E' THEN 'est '  "
+ "      			WHEN 'L' THEN 'calc '  "
+ "      			WHEN 'S' THEN 'say '  "
+ "      			WHEN '6' THEN 'cert '  "
+ "      			WHEN '5' THEN 'prob '  "
+ "      			WHEN '4' THEN 'poss '  "
+ "      			WHEN '3' THEN 'lkly '  "
+ "      			WHEN '2' THEN 'appar '  "
+ "      			WHEN '1' THEN 'prhps '  "
+ "      			WHEN '?' THEN 'maybe '  "
+ "      		ELSE ''  "
+ "      		END  "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 3 , 1 ) = '-' THEN 'BC' ELSE '' END   "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 4 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 4 , 4 ) END  "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 3 , 5 ) ) ELSE '' END  "
+ "      		||  "
+ "      		CASE  "
+ "      			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) <> '00' ) THEN '-??'  "
+ "      			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) == '00' ) THEN ''  "
+ "      			WHEN Substr( rawDate , 8 , 2 ) = '00' THEN ''  "
+ "      		ELSE '-' || Substr( rawDate , 8 , 2 )  "
+ "      		END  "
+ "      		||  "
+ "      		Coalesce( Nullif( '-' || Substr( rawDate , 10 , 2 ) , '-00' ) , '' )  "
+ "      		||  "
+ "      		CASE Substr( rawDate , 2 , 1 )  "
+ "      			WHEN 'R' THEN ' and '  "
+ "      			WHEN 'S' THEN ' to '  "
+ "      			WHEN 'O' THEN ' or '  "
+ "      			WHEN '-' THEN ' - '  "
+ "      		ELSE ''  "
+ "      		END  "
+ "      		||  "
+ "      		CASE Substr( rawDate , 24 , 1 )  "
+ "      			WHEN 'A' THEN 'abt '  "
+ "      			WHEN 'E' THEN 'est '  "
+ "      			WHEN 'L' THEN 'calc '  "
+ "      			WHEN 'C' THEN 'ca '  "
+ "      			WHEN 'S' THEN 'say '  "
+ "      			WHEN '6' THEN 'cert '  "
+ "      			WHEN '5' THEN 'prob '  "
+ "      			WHEN '4' THEN 'poss '  "
+ "      			WHEN '3' THEN 'lkly '  "
+ "      			WHEN '2' THEN 'appar '  "
+ "      			WHEN '1' THEN 'prhps '  "
+ "      			WHEN '?' THEN 'maybe '  "
+ "    		  ELSE ''  "
+ "    	   	END  "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 14 , 1 ) = '-' THEN 'BC' ELSE '' END  "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 15 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 15 , 4 ) END  "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 14 , 5 ) ) ELSE '' END  "
+ "      		||  "
+ "      		CASE  "
+ "      			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) <> '00' ) THEN '-??'  "
+ "      			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) == '00' ) THEN ''  "
+ "      			WHEN Substr( rawDate , 19 , 2 ) = '00' THEN ''  "
+ "      		ELSE '-' || Substr( rawDate , 19 , 2 )  "
+ "      		END  "
+ "      		||  "
+ "      		Coalesce( Nullif( '-' || Substr( rawDate , 21 , 2 ) , '-00' ) , '' )  "
+ "      	ELSE ''  "
+ "        END AS 'MediaDate' , "
+ "" // "
+ "       RefNumber         , "
+ "       Include1 AS Scrpbk, "
+ "       IsPrimary "
+ "FROM   ( "
+ "       " // All fields and values from MediaLinkTable and MultiMediaTable "
+ "       SELECT  MM.MediaID                 , "
+ "               MM.MediaType               , "
+ "               MM.MediaPath               , "
+ "               MM.MediaFile COLLATE NOCASE, "
+ "               MM.URL                     , "
+ "               MM.Thumbnail               , "
+ "               ML.LinkID                  , "
+ "               ML.MediaID                 , "
+ "               ML.OwnerType               , "
+ "               ML.OwnerID                 , "
+ "               ML.IsPrimary               , "
+ "               ML.Include1                , "
+ "               ML.Include2                , "
+ "               ML.Include3                , "
+ "               ML.Include4                , "
+ "               ML.SortOrder               , "
+ "               ML.RectLeft                , "
+ "               ML.RectTop                 , "
+ "               ML.RectRight               , "
+ "               ML.RectBottom              , "
+ "               ML.Note                    , "
+ "               MM.Caption COLLATE   NOCASE  , "
+ "               MM.RefNumber COLLATE NOCASE  , "
+ "               MM.Date AS rawDate           , "
+ "               ML.SortDate                  , "
+ "               CAST(MM.Description AS TEXT) AS Description "
+ "       FROM    MultiMediaTable              AS MM "
+ "               LEFT JOIN MediaLinkTable     AS ML "
+ "       USING   (MediaID) "
+ "       ) "
+ "ORDER BY OwnerID ; ";
                        return commandText;
                        }
                }

          public static string dBMediaListRM4 
                { 
                    get
                        {
                        string commandText = ""
+ "" // MediaList.sql "
+ "" // 2011-11-06 by ve3meo "
+ "" // Lists all media and the key link ID to where used. Some SQLite managers will display thumbnail image (e.g. SQLite Expert). "
+ "" // The results displayed by some can be copied and pasted (or exported) to Excel which supports the synthesized Hyperlink column "
+ "" // for opening the media file. "
+ " "
+ "SELECT OwnerID, "
+ "       CASE OwnerType "
+ "              WHEN 0 "
+ "              THEN OwnerID "
+ "       END AS PersonID, "
+ "       CASE OwnerType "
+ "              WHEN 1 "
+ "              THEN OwnerID "
+ "       END AS FamilyID, "
+ "       CASE OwnerType "
+ "              WHEN 2 "
+ "              THEN OwnerID "
+ "       END AS EventID, "
+ "       CASE OwnerType "
+ "              WHEN 3 "
+ "              THEN OwnerID "
+ "       END AS SourceID, "
+ "       CASE OwnerType "
+ "              WHEN 4 "
+ "              THEN OwnerID "
+ "       END AS CitationID, "
+ "       CASE OwnerType "
+ "              WHEN 5 "
+ "              THEN OwnerID "
+ "       END AS PlaceID, "
+ "       MediaID       , "
+ "" //       Thumbnail, "
+ "       MediaPath     , "
+ "       MediaFile     , "
+ "       '=HYPERLINK(\"' "
+ "              ||MediaPath "
+ "              ||MediaFile "
+ "              ||'\",\"Open\")' AS HyperLink, "
+ "       Caption                          , "
+ "       Description                      , "
+ "       " // Convert Fact dates to readable form "
+ "       CASE SUBSTR( rawDate , 1 , 1 ) "
+ "      	WHEN 'Q' THEN rawDate  "
+ "      	WHEN 'T' THEN Substr( rawDate , 2 , 20 )  "
+ "      	WHEN 'D' THEN  "
+ "      		CASE Substr( rawDate , 2 , 1 )  "
+ "      			WHEN 'A' THEN 'aft '  "
+ "      			WHEN 'B' THEN 'bef '  "
+ "      			WHEN 'F' THEN 'from '  "
+ "      			WHEN 'I' THEN 'since '  "
+ "      			WHEN 'R' THEN 'bet '  "
+ "      			WHEN 'S' THEN 'from '  "
+ "      			WHEN 'T' THEN 'to '  "
+ "      			WHEN 'U' THEN 'until '  "
+ "      			WHEN 'Y' THEN 'by '  "
+ "      		ELSE ''  "
+ "      		END  "
+ "      		||  "
+ "      		CASE Substr( rawDate , 13 , 1 )  "
+ "      			WHEN 'A' THEN 'abt '  "
+ "      			WHEN 'C' THEN 'ca '  "
+ "      			WHEN 'E' THEN 'est '  "
+ "      			WHEN 'L' THEN 'calc '  "
+ "      			WHEN 'S' THEN 'say '  "
+ "      			WHEN '6' THEN 'cert '  "
+ "      			WHEN '5' THEN 'prob '  "
+ "      			WHEN '4' THEN 'poss '  "
+ "      			WHEN '3' THEN 'lkly '  "
+ "      			WHEN '2' THEN 'appar '  "
+ "      			WHEN '1' THEN 'prhps '  "
+ "      			WHEN '?' THEN 'maybe '  "
+ "      		ELSE ''  "
+ "      		END  "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 3 , 1 ) = '-' THEN 'BC' ELSE '' END   "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 4 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 4 , 4 ) END  "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 3 , 5 ) ) ELSE '' END  "
+ "      		||  "
+ "      		CASE  "
+ "      			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) <> '00' ) THEN '-??'  "
+ "      			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) == '00' ) THEN ''  "
+ "      			WHEN Substr( rawDate , 8 , 2 ) = '00' THEN ''  "
+ "      		ELSE '-' || Substr( rawDate , 8 , 2 )  "
+ "      		END  "
+ "      		||  "
+ "      		Coalesce( Nullif( '-' || Substr( rawDate , 10 , 2 ) , '-00' ) , '' )  "
+ "      		||  "
+ "      		CASE Substr( rawDate , 2 , 1 )  "
+ "      			WHEN 'R' THEN ' and '  "
+ "      			WHEN 'S' THEN ' to '  "
+ "      			WHEN 'O' THEN ' or '  "
+ "      			WHEN '-' THEN ' - '  "
+ "      		ELSE ''  "
+ "      		END  "
+ "      		||  "
+ "      		CASE Substr( rawDate , 24 , 1 )  "
+ "      			WHEN 'A' THEN 'abt '  "
+ "      			WHEN 'E' THEN 'est '  "
+ "      			WHEN 'L' THEN 'calc '  "
+ "      			WHEN 'C' THEN 'ca '  "
+ "      			WHEN 'S' THEN 'say '  "
+ "      			WHEN '6' THEN 'cert '  "
+ "      			WHEN '5' THEN 'prob '  "
+ "      			WHEN '4' THEN 'poss '  "
+ "      			WHEN '3' THEN 'lkly '  "
+ "      			WHEN '2' THEN 'appar '  "
+ "      			WHEN '1' THEN 'prhps '  "
+ "      			WHEN '?' THEN 'maybe '  "
+ "    		  ELSE ''  "
+ "    	   	END  "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 14 , 1 ) = '-' THEN 'BC' ELSE '' END  "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 15 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 15 , 4 ) END  "
+ "      		||  "
+ "      		CASE WHEN Substr( rawDate , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 14 , 5 ) ) ELSE '' END  "
+ "      		||  "
+ "      		CASE  "
+ "      			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) <> '00' ) THEN '-??'  "
+ "      			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) == '00' ) THEN ''  "
+ "      			WHEN Substr( rawDate , 19 , 2 ) = '00' THEN ''  "
+ "      		ELSE '-' || Substr( rawDate , 19 , 2 )  "
+ "      		END  "
+ "      		||  "
+ "      		Coalesce( Nullif( '-' || Substr( rawDate , 21 , 2 ) , '-00' ) , '' )  "
+ "      	ELSE ''  "
+ "        END AS 'MediaDate' , "
+ "" // "
+ "       RefNumber         , "
+ "       Include1 AS Scrpbk, "
+ "       IsPrimary "
+ "FROM   ( "
+ "       " // All fields and values from MediaLinkTable and MultiMediaTable "
+ "       SELECT  MM.MediaID                 , "
+ "               MM.MediaType               , "
+ "               MM.MediaPath               , "
+ "               MM.MediaFile COLLATE NOCASE, "
+ "               MM.URL                     , "
+ "               MM.Thumbnail               , "
+ "               ML.LinkID                  , "
+ "               ML.MediaID                 , "
+ "               ML.OwnerType               , "
+ "               ML.OwnerID                 , "
+ "               ML.IsPrimary               , "
+ "               ML.Include1                , "
+ "               ML.Include2                , "
+ "               ML.Include3                , "
+ "               ML.Include4                , "
+ "               ML.SortOrder               , "
+ "               ML.RectLeft                , "
+ "               ML.RectTop                 , "
+ "               ML.RectRight               , "
+ "               ML.RectBottom              , "
+ "               ML.Note                    , "
+ "               ML.Caption COLLATE   NOCASE  , "
+ "               ML.RefNumber COLLATE NOCASE  , "
+ "               ML.Date AS rawDate           , "
+ "               ML.SortDate                  , "
+ "               CAST(ML.Description AS TEXT) AS Description "
+ "       FROM    MultiMediaTable              AS MM "
+ "               LEFT JOIN MediaLinkTable     AS ML "
+ "       USING   (MediaID) "
+ "       ) "
+ "ORDER BY OwnerID ; ";
                        return commandText;
                        }
                }

          public static string dBPlacesErrors 
                { 
                    get
                        {
                        string commandText = ""
+ "" // PlaceErrors.sql "
+ "" // 2012-03-06 ve3meo "
+ "" // Combines two queries OrphanedPlaceDetails and PlacesLost "
+ "" // into one with error described. "
+ " "
+ "SELECT Error, Person, Fact, \"Place Detail\", \"PlaceID(EventTbl)\", \"SiteID(EventTbl)\", \"PlaceID(PlaceTbl)\", \"MasterID(PlaceTbl)\" "
+ "FROM "
+ "( "
+ "" // PlacesLost.sql "
+ "" // 2012-03-06 ve3meo "
+ "" // Lost Places are Places for Events which  "
+ "" // do not exist in the PlaceTable thus resulting in a blank Place "
+ "" // indistinguishable from no Place having been assigned.  "
+ "" // These may arise from mergers and deletions, splitting Places  "
+ "" // into Place + Place Detail, the latter a suspected bug in RM5.0.2.1.  "
+ " "
+ "SELECT "
+ " 'Place Lost' AS Error,  "
+ " CASE EventTable.OwnerType "
+ " WHEN 0 "
+ " THEN "
+ "  NameTable.Surname || ', ' || "
+ "  NameTable.Given || ' - ' || "
+ "  NameTable.OwnerID  "
+ " WHEN 1 "
+ " THEN "
+ "  Husband.Surname || ', ' || "
+ "  Husband.Given || ' - ' || "
+ "  Husband.OwnerID "
+ " END "
+ " AS Person, "
+ " FactTypeTable.Name AS Fact, "
+ " SiteTable.Name AS \"Place Detail\", "
+ " EventTable.PlaceID AS \"PlaceID(EventTbl)\", "
+ " EventTable.SiteID AS \"SiteID(EventTbl)\",  "
+ " PlaceTable.PlaceID AS \"PlaceID(PlaceTbl)\", "
+ " PlaceTable.MasterID AS \"MasterID(PlaceTbl)\" "
+ " "
+ "FROM EventTable "
+ "LEFT JOIN PlaceTable "
+ "ON EventTable.PlaceID = PlaceTable.PlaceID "
+ "LEFT JOIN PlaceTable AS SiteTable "
+ "ON EventTable.SiteID = SiteTable.PlaceID AND SiteTable.PlaceType = 2  "
+ "LEFT JOIN FactTypeTable "
+ "ON EventType = FactTypeID "
+ "LEFT JOIN NameTable "
+ "ON EventTable.OwnerID = NameTable.OwnerID AND +NameTable.IsPrimary AND EventTable.OwnerType=0 "
+ "LEFT JOIN FamilyTable "
+ "ON EventTable.OwnerID = FamilyTable.FamilyID AND EventTable.OwnerType=1 "
+ "LEFT JOIN NameTable AS Husband "
+ "ON FamilyTable.FatherID = Husband.OwnerID AND +Husband.IsPrimary "
+ "WHERE EventTable.PlaceID <>0 " // No Place was assigned "
+ "AND EventTable.PlaceID "
+ "NOT IN "
+ "(SELECT PlaceID FROM PlaceTable WHERE PlaceType = 0) " // Existing Places PlaceID's "
+ "AND EventTable.SiteID " // Event's Place Detail PlaceID "
+ "NOT IN "
+ "(SELECT PlaceID FROM PlaceTable WHERE PlaceType = 2) " // Existing Place Details' PlaceID's "
+ " "
+ "UNION ALL "
+ " "
+ "" // OrphanedPlaceDetails.sql "
+ "" // 2012-02-22 ve3meo "
+ "" // Orphaned Place Details are Place Details for which the parent Place  "
+ "" // does not exist in the PlaceTable. These may arise because RM5021 does "
+ "" // not preclude the entry of such in the Edit Person screen or possibly "
+ "" // from its failure to clean up after mergers and deletions. Some are  "
+ "" // actually used by Facts/Events and come out in some reports (but not "
+ "" // the Place List). Others are no longer accessible from the program. "
+ "" // 2012-03-04 rev added Error and various PlaceID fields "
+ " "
+ "SELECT  "
+ " CASE  "
+ " WHEN EventTable.SiteID ISNULL THEN 'Unused Place Detail w/o Place' "
+ " ELSE  'Place Detail w/o Place'  "
+ " END AS Error, "
+ " CASE EventTable.OwnerType "
+ " WHEN 0 "
+ " THEN "
+ "  NameTable.Surname || ', ' || "
+ "  NameTable.Given || ' - ' || "
+ "  NameTable.OwnerID  "
+ " WHEN 1 "
+ " THEN "
+ "  Husband.Surname || ', ' || "
+ "  Husband.Given || ' - ' || "
+ "  Husband.OwnerID "
+ " END "
+ " AS Person, "
+ " FactTypeTable.Name AS Fact, "
+ " PlaceTable.Name AS \"Place Detail\", "
+ " EventTable.PlaceID AS \"Event.PlaceID\", "
+ " EventTable.SiteID AS \"Event.SiteID\",  "
+ " PlaceTable.PlaceID AS \"Place.PlaceID\", "
+ " PlaceTable.MasterID AS \"Place.MasterID\" "
+ "FROM PlaceTable "
+ "LEFT JOIN EventTable "
+ "ON PlaceTable.PlaceID = SiteID  "
+ "LEFT JOIN FactTypeTable "
+ "ON EventType = FactTypeID "
+ "LEFT JOIN NameTable "
+ "ON EventTable.OwnerID = NameTable.OwnerID AND +NameTable.IsPrimary AND EventTable.OwnerType=0 "
+ "LEFT JOIN FamilyTable "
+ "ON EventTable.OwnerID = FamilyTable.FamilyID AND EventTable.OwnerType=1 "
+ "LEFT JOIN NameTable AS Husband "
+ "ON FamilyTable.FatherID = Husband.OwnerID AND +Husband.IsPrimary "
+ "WHERE PlaceTable.PlaceID IN "
+ "( "
+ "SELECT PlaceID "
+ "FROM PlaceTable "
+ "WHERE PlaceType = 2 "
+ "AND "
+ "( "
+ " MasterID = 0 "
+ " OR "
+ " MasterID NOT IN "
+ " ( "
+ "  SELECT PlaceID "
+ "  FROM PlaceTable "
+ "  WHERE PlaceType = 0 "
+ " ) "
+ ") "
+ ") "
+ ") "
+ "ORDER BY Person ASC "
+ "; ";
                        return commandText;
                        }
                }

          public static string dBEventsFactTypeOutputsView 
        { 
            get
                {
                string commandText = ""
+ "" // List FactType Include Settings "
+ "SELECT "
+ " Name, "
+ " Flags & 1 AS GEDCOM, "
+ " Flags & 2 <> 0 AS WEB, "
+ " Flags & 4 <> 0 AS FGS, "
+ " Flags & 8 <> 0 AS NARR, "
+ " Flags & 16 <> 0 AS INDIV, "
+ " Flags & 32 <> 0 AS LISTS "
+ "FROM FactTypeTable "
+ "ORDER BY Name "
+ "; ";
                return commandText;
                }
        }

          public static string dBEventsFactTypeSettingsSnapshot 
                  { 
                      get
                          {
                          string commandText = ""
+ "" // Save current FactType settings for subsequent restoration "
+ "DROP TABLE IF EXISTS zFactTypeTable; "
+ "CREATE TABLE IF NOT EXISTS zFactTypeTable  "
+ "AS SELECT * FROM FactTypeTable; "
+ "SELECT 'Snapshot of current settings saved to zFactTypeTable' AS Status"
+ "; ";
                          return commandText;
                          }
                  }

          public static string dBEventsFactTypeSettingsAllGEDCOM 
                { 
                    get
                        {
                        string commandText = ""
+ "" // Set all FactTypes to be included in GEDCOM and Drag and Drop "
+ "UPDATE FactTypeTable  "
+ " SET Flags = Flags +1 "
+ "WHERE NOT Flags & 1 "
+ "; "
+ "SELECT 'All Fact Types are set to be included in GEDCOM export and Drag and Drop.' AS Status "
+ "; ";
                        return commandText;
                        }
                }

          public static string dBEventsFactTypeSettingsRestore 
                { 
                    get
                        {
                        string commandText = ""
+ "" // Restore saved FactTypeTable settings "
+ "INSERT OR REPLACE INTO FactTypeTable  "
+ " SELECT * FROM zFactTypeTable "
+ "; "
+ "SELECT 'Fact Type settings have been restored from the Snapshot.' AS Status "
+ "; ";
                        return commandText;
                        }
                }

          public static string dBEventsFactTypeSettingsDeleteSnapshot 
                { 
                    get
                        {
                        string commandText = ""
+ "DROP TABLE IF EXISTS zFactTypeTable; "
+ "SELECT 'Snapshot of Fact Type settings has been deleted (if it existed at all).' AS Status "
+ "; ";
                        return commandText;
                        }
                }

          public static string dBEventsFactTypeSnapOutputsView
          {
              get
              {
                  string commandText = ""
  + "" // List FactType Include Settings "
  + "SELECT "
  + " Name, "
  + " Flags & 1 AS GEDCOM, "
  + " Flags & 2 <> 0 AS WEB, "
  + " Flags & 4 <> 0 AS FGS, "
  + " Flags & 8 <> 0 AS NARR, "
  + " Flags & 16 <> 0 AS INDIV, "
  + " Flags & 32 <> 0 AS LISTS "
  + "FROM zFactTypeTable "
  + "ORDER BY Name "
  + "; ";
                  return commandText;
              }
          }

          public static string dBReportsLifeLines 
                { 
                    get
                        {
                        string commandText = ""
+ "SELECT  "
+ "" // LifeLines-OO.sql 15 Mar 2010 "
+ "" // originally initiated by romermb "
+ "" // further developed almost beyond recognition by ve3meo "
+ "" // Lists a summary of all events and facts for each person including "
+ "" //  personal, couple, parental, child, shared, alternate name. "
+ "" // Reports persons not in a database tree who shared an event. "
+ "" // Lists RIN, Person, Role, Fact, Date (decoded), Detail, Site, Place, MRIN, "
+ "" //  Sharer(s) and RIN(s), Duplicate count, Sort Date (undecoded), and IsPrivate flag. "
+ "" // Works with SQLite Developer, SQLiteSpy, DBManager, SQLiteman, Open Office Base "
+ "" // rev 2010-12-29  added IsPrivate "
+ " "
+ "  RIN,  "
+ "  Person,  "
+ "  Role,  "
+ "  Fact,  "
+ "  CASE Substr( rawDate , 1 , 1 ) "
+ "	WHEN 'Q' THEN rawDate  "
+ "	WHEN 'T' THEN Substr( rawDate , 2 , 20 )  "
+ "	WHEN 'D' THEN  "
+ "		CASE Substr( rawDate , 2 , 1 )  "
+ "			WHEN 'A' THEN 'aft '  "
+ "			WHEN 'B' THEN 'bef '  "
+ "			WHEN 'F' THEN 'from '  "
+ "			WHEN 'I' THEN 'since '  "
+ "			WHEN 'R' THEN 'bet '  "
+ "			WHEN 'S' THEN 'from '  "
+ "			WHEN 'T' THEN 'to '  "
+ "			WHEN 'U' THEN 'until '  "
+ "			WHEN 'Y' THEN 'by '  "
+ "		ELSE ''  "
+ "		END  "
+ "		||  "
+ "		CASE Substr( rawDate , 13 , 1 )  "
+ "			WHEN 'A' THEN 'abt '  "
+ "			WHEN 'C' THEN 'ca '  "
+ "			WHEN 'E' THEN 'est '  "
+ "			WHEN 'L' THEN 'calc '  "
+ "			WHEN 'S' THEN 'say '  "
+ "			WHEN '6' THEN 'cert '  "
+ "			WHEN '5' THEN 'prob '  "
+ "			WHEN '4' THEN 'poss '  "
+ "			WHEN '3' THEN 'lkly '  "
+ "			WHEN '2' THEN 'appar '  "
+ "			WHEN '1' THEN 'prhps '  "
+ "			WHEN '?' THEN 'maybe '  "
+ "		ELSE ''  "
+ "		END  "
+ "		||  "
+ "		CASE WHEN Substr( rawDate , 3 , 1 ) = '-' THEN 'BC' ELSE '' END   "
+ "		||  "
+ "		CASE WHEN Substr( rawDate , 4 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 4 , 4 ) END  "
+ "		||  "
+ "		CASE WHEN Substr( rawDate , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 3 , 5 ) ) ELSE '' END  "
+ "		||  "
+ "		CASE  "
+ "			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) <> '00' ) THEN '-??'  "
+ "			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) == '00' ) THEN ''  "
+ "			WHEN Substr( rawDate , 8 , 2 ) = '00' THEN ''  "
+ "		ELSE '-' || Substr( rawDate , 8 , 2 )  "
+ "		END  "
+ "		||  "
+ "		Coalesce( Nullif( '-' || Substr( rawDate , 10 , 2 ) , '-00' ) , '' )  "
+ "		||  "
+ "		CASE Substr( rawDate , 2 , 1 )  "
+ "			WHEN 'R' THEN ' and '  "
+ "			WHEN 'S' THEN ' to '  "
+ "			WHEN 'O' THEN ' or '  "
+ "			WHEN '-' THEN ' - '  "
+ "		ELSE ''  "
+ "		END  "
+ "		||  "
+ "		CASE Substr( rawDate , 24 , 1 )  "
+ "			WHEN 'A' THEN 'abt '  "
+ "			WHEN 'E' THEN 'est '  "
+ "			WHEN 'L' THEN 'calc '  "
+ "			WHEN 'C' THEN 'ca '  "
+ "			WHEN 'S' THEN 'say '  "
+ "			WHEN '6' THEN 'cert '  "
+ "			WHEN '5' THEN 'prob '  "
+ "			WHEN '4' THEN 'poss '  "
+ "			WHEN '3' THEN 'lkly '  "
+ "			WHEN '2' THEN 'appar '  "
+ "			WHEN '1' THEN 'prhps '  "
+ "			WHEN '?' THEN 'maybe '  "
+ "		ELSE ''  "
+ "		END  "
+ "		||  "
+ "		CASE WHEN Substr( rawDate , 14 , 1 ) = '-' THEN 'BC' ELSE '' END  "
+ "		||  "
+ "		CASE WHEN Substr( rawDate , 15 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 15 , 4 ) END  "
+ "		||  "
+ "		CASE WHEN Substr( rawDate , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 14 , 5 ) ) ELSE '' END  "
+ "		||  "
+ "		CASE  "
+ "			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) <> '00' ) THEN '-??'  "
+ "			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) == '00' ) THEN ''  "
+ "			WHEN Substr( rawDate , 19 , 2 ) = '00' THEN ''  "
+ "		ELSE '-' || Substr( rawDate , 19 , 2 )  "
+ "		END  "
+ "		||  "
+ "		Coalesce( Nullif( '-' || Substr( rawDate , 21 , 2 ) , '-00' ) , '' )  "
+ "	ELSE ''  "
+ "  END AS Date , "
+ "  Detail, "
+ "  Site, "
+ "  Place, "
+ "  MRIN,   "
+ "  \"Sharer Name\", "
+ "  Dup, "
+ "  SortDate, "
+ "  IsPrivate "
+ "FROM "
+ "( "
+ "SELECT  "
+ "" // Invented Parents fact to show Birth parents regardless of existence of Birth Fact "
+ "  Ch.ChildID AS RIN ,  "
+ "  UPPER(Npers.Surname) COLLATE Nocase ||' '||  "
+ "  Npers.Suffix COLLATE Nocase ||', '||  "
+ "  Npers.Prefix COLLATE Nocase ||' '||  "
+ "  Npers.Given COLLATE Nocase AS Person,  "
+ "  'Child' AS Role ,  "
+ "  'Parents' AS Fact ,  "
+ "  NULL AS rawDate , "
+ "  NULL AS Detail,   "
+ "  NULL AS Site, "
+ "  NULL AS Place, "
+ "  'M'|| parent.FamilyID AS MRIN,  "
+ "  IFNULL( UPPER(Nfath.Surname) COLLATE Nocase ||' '||  "
+ "    Nfath.Suffix COLLATE Nocase ||', '||  "
+ "    Nfath.Prefix COLLATE Nocase ||' '||  "
+ "    Nfath.Given COLLATE Nocase ||'-'||       "
+ "    Nfath.OwnerID , '' )           "
+ "  || "
+ "  IFNULL( ' & '|| UPPER(Nmoth.Surname) COLLATE Nocase ||' '||  "
+ "    Nmoth.Suffix COLLATE Nocase ||', '||  "
+ "    Nmoth.Prefix COLLATE Nocase ||' '||  "
+ "    Nmoth.Given COLLATE Nocase ||'-'||       "
+ "    Nmoth.OwnerID , '' ) "
+ "   AS 'Sharer Name' ,  "
+ "  Count( 1 ) -1 AS Dup, "
+ "  NULL AS SortDate , "
+ "  Ch.IsPrivate AS IsPrivate "
+ "FROM  "
+ "  Childtable Ch  "
+ "  LEFT JOIN Nametable Npers ON Ch.ChildID = Npers.Ownerid AND +Npers.Isprimary = 1 "
+ "  INNER JOIN familytable AS parent ON parent.Familyid = Ch.Familyid AND Ch.RelMother IN (0, NULL) AND Ch.RelFather IN( 0 , NULL)   "
+ "  LEFT JOIN NameTable AS Nfath ON parent.FATHERID = Nfath.OwnerID AND +Nfath.IsPrimary = 1  "
+ "  LEFT JOIN NameTable AS Nmoth ON parent.MotherID = Nmoth.OwnerID AND +Nmoth.IsPrimary = 1 "
+ "    "
+ "GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 "
+ " "
+ "UNION ALL "
+ " "
+ "SELECT  "
+ "" // Facts for Wives in Couple events "
+ "  Nwife.OwnerID AS RIN,  "
+ "  UPPER(Nwife.Surname) COLLATE Nocase ||' '||  "
+ "  Nwife.Suffix COLLATE Nocase ||', '||   "
+ "  Nwife.Prefix COLLATE Nocase ||' '||   "
+ "  Nwife.Given COLLATE Nocase AS Person,   "
+ "  'Partner' AS Role,  "
+ "  Fact.Name COLLATE Nocase AS Event,  "
+ "  E.Date AS rawDate , "
+ "  CAST( E.Details AS TEXT ) AS Detail,   "
+ "  Site.Name COLLATE Nocase AS Site, "
+ "  Place.Name COLLATE Nocase AS Place,   "
+ "  'M'|| Fam.FamilyID AS MRIN,  "
+ "  UPPER(Nhusb.Surname) COLLATE Nocase ||' '||  "
+ "  Nhusb.Suffix COLLATE Nocase ||', '||  "
+ "  Nhusb.Prefix COLLATE Nocase ||' '||  "
+ "  Nhusb.Given COLLATE Nocase ||'-'||       "
+ "  Nhusb.OwnerID AS 'Sharer Name', "
+ "  Count( 1 )-1 AS Dup, "
+ "  E.SortDate , "
+ "  Fam.IsPrivate AS IsPrivate "
+ "FROM  "
+ "  Eventtable AS E   "
+ "  LEFT JOIN Facttypetable AS Fact ON E.Eventtype = Fact.Facttypeid     "
+ "  LEFT JOIN NameTable AS Npers ON E.OwnerID = Npers.Ownerid AND +Npers.IsPrimary = 1 "
+ "  LEFT JOIN FamilyTable AS Fam ON E.OwnerID = Fam.FamilyID "
+ "  LEFT JOIN NameTable AS Nhusb ON Fam.FatherID = Nhusb.OwnerID AND +Nhusb.Isprimary =1  "
+ "  LEFT JOIN NameTable AS Nwife ON Fam.MotherID = Nwife.OwnerID AND +Nwife.Isprimary = 1 "
+ "  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID "
+ "  LEFT JOIN PlaceTable AS Site ON E.SITEID = Site.PlaceID   "
+ "  LEFT JOIN PersonTable AS Person ON E.OWNERID = person.PersonID   "
+ "WHERE E.OwnerType = 1  "
+ " "
+ "GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 "
+ " "
+ "UNION ALL "
+ " "
+ "SELECT  "
+ "" // Facts for Individuals and Husbands "
+ "  CASE E.OwnerType    "
+ "    WHEN 0 THEN E.OwnerID  "
+ "    WHEN 1 THEN Nhusb.OwnerID "
+ "  END "
+ "    AS RIN,  "
+ "  CASE E.OwnerType    "
+ "    WHEN 0 THEN "
+ "      UPPER(Npers.Surname) COLLATE Nocase ||' '||  "
+ "      Npers.Suffix COLLATE Nocase ||', '||   "
+ "      Npers.Prefix COLLATE Nocase ||' '||   "
+ "      Npers.Given COLLATE Nocase   "
+ "    WHEN 1 THEN "
+ "      UPPER(Nhusb.Surname) COLLATE Nocase ||' '||  "
+ "      Nhusb.Suffix COLLATE Nocase ||', '||   "
+ "      Nhusb.Prefix COLLATE Nocase ||' '||   "
+ "      Nhusb.Given COLLATE Nocase "
+ "  END AS Person,   "
+ "  CASE E.OwnerType    "
+ "    WHEN 0 THEN 'Principal'     "
+ "    ELSE 'Partner'     "
+ "  END "
+ "    AS Role,  "
+ "  Fact.Name COLLATE Nocase AS Event,  "
+ "  E.Date AS rawDate , "
+ "  CAST( E.Details AS TEXT ) AS Detail,   "
+ "  Site.Name COLLATE Nocase AS Site, "
+ "  Place.Name COLLATE Nocase AS Place,   "
+ "  CASE E.OwnerType   "
+ "    WHEN 0 THEN      "
+ "      CASE  "
+ "        WHEN E.EventType IN (1,33) THEN "
+ "          'M'|| Parent.FamilyID "
+ "        WHEN E.EventType = 6 THEN "
+ "          'M'|| AdoptParent.FamilyID "
+ "      END       "
+ "    WHEN 1 THEN "
+ "      'M'|| Fam.FamilyID       "
+ "    ELSE NULL "
+ "  END "
+ "     AS MRIN,  "
+ "  CASE E.OwnerType   "
+ "    WHEN 0 THEN "
+ "      CASE  "
+ "        WHEN E.EventType IN(1,33) THEN "
+ "          IFNULL( UPPER(Nfath.Surname) COLLATE Nocase ||' '||  "
+ "          Nfath.Suffix COLLATE Nocase ||', '||  "
+ "          Nfath.Prefix COLLATE Nocase ||' '||  "
+ "          Nfath.Given COLLATE Nocase ||'-'||       "
+ "          Nfath.OwnerID , '' )           "
+ "          || "
+ "          IFNULL( ' & '|| UPPER(Nmoth.Surname) COLLATE Nocase ||' '||  "
+ "          Nmoth.Suffix COLLATE Nocase ||', '||  "
+ "          Nmoth.Prefix COLLATE Nocase ||' '||  "
+ "          Nmoth.Given COLLATE Nocase ||'-'||       "
+ "          Nmoth.OwnerID , '' ) "
+ "        WHEN E.EventType = 6 THEN "
+ "          IFNULL( UPPER(Nafath.Surname) COLLATE Nocase ||' '||  "
+ "          Nafath.Suffix COLLATE Nocase ||', '||  "
+ "          Nafath.Prefix COLLATE Nocase ||' '||  "
+ "          Nafath.Given COLLATE Nocase ||'-'||       "
+ "          Nafath.OwnerID , '')           "
+ "          || "
+ "          IFNULL(' & '|| UPPER(Namoth.Surname) COLLATE Nocase ||' '||  "
+ "          Namoth.Suffix COLLATE Nocase ||', '||  "
+ "          Namoth.Prefix COLLATE Nocase ||' '||  "
+ "          Namoth.Given COLLATE Nocase ||'-'||       "
+ "          Namoth.OwnerID , '') "
+ "        ELSE NULL         "
+ "      END      "
+ "    WHEN 1 THEN "
+ "      UPPER(Nwife.Surname) COLLATE Nocase ||' '||  "
+ "      Nwife.Suffix COLLATE Nocase ||', '||  "
+ "      Nwife.Prefix COLLATE Nocase ||' '||  "
+ "      Nwife.Given COLLATE Nocase ||'-'||       "
+ "      Nwife.OwnerID "
+ "  END     "
+ "    AS 'Sharer Name', "
+ "  Count( 1 )-1 AS Dup, "
+ "  E.SortDate , "
+ "  E.IsPrivate AS IsPrivate "
+ "FROM  "
+ "  Eventtable AS E   "
+ "  LEFT JOIN Facttypetable AS Fact ON E.Eventtype = Fact.Facttypeid     "
+ "  LEFT JOIN NameTable AS Npers ON E.OwnerID = Npers.Ownerid AND +Npers.IsPrimary = 1 AND E.OwnerType = 0 "
+ "  LEFT JOIN FamilyTable AS Fam ON E.OwnerID = Fam.FamilyID AND E.OwnerType = 1 "
+ "  LEFT JOIN NameTable AS Nhusb ON Fam.FatherID = Nhusb.OwnerID AND +Nhusb.Isprimary =1  "
+ "  LEFT JOIN NameTable AS Nwife ON Fam.MotherID = Nwife.OwnerID AND +Nwife.Isprimary = 1 "
+ "  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID "
+ "  LEFT JOIN PlaceTable AS Site ON E.SITEID = Site.PlaceID   "
+ "  LEFT JOIN ChildTable AS Child ON E.OwnerID = Child.ChildID AND Child.RelMother = 0 AND Child.RelFather = 0  "
+ "  LEFT JOIN familytable AS Parent ON Child.Familyid = Parent.Familyid  "
+ "  LEFT JOIN NameTable AS Nfath ON parent.FATHERID = Nfath.OwnerID AND +Nfath.IsPrimary = 1  "
+ "  LEFT JOIN NameTable AS Nmoth ON parent.MotherID = Nmoth.OwnerID AND +Nmoth.IsPrimary = 1 "
+ "  LEFT JOIN familytable AS AdoptParent ON E.FamilyID = AdoptParent.FamilyID   "
+ "  LEFT JOIN NameTable AS Nafath ON AdoptParent.FATHERID = Nafath.OwnerID AND +Nafath.IsPrimary = 1  "
+ "  LEFT JOIN NameTable AS Namoth ON AdoptParent.MotherID = Namoth.OwnerID AND +Namoth.IsPrimary = 1   "
+ " "
+ "GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 "
+ " "
+ "UNION ALL "
+ " "
+ "SELECT   "
+ "" // Invented Fact for Father having child "
+ "  Npers.Ownerid AS RIN ,  "
+ "  UPPER(Npers.Surname) COLLATE Nocase ||' '||  "
+ "  Npers.Suffix COLLATE Nocase ||', '||  "
+ "  Npers.Prefix COLLATE Nocase ||' '||  "
+ "  Npers.Given COLLATE Nocase AS Person,  "
+ "  'Parent' AS Role ,  "
+ "  'Fathered' AS Fact ,  "
+ "  E.Date AS rawDate , "
+ "  CAST( E.Details AS TEXT ) AS Detail,   "
+ "  Site.Name COLLATE Nocase AS Site, "
+ "  Place.Name COLLATE Nocase AS Place, "
+ "  'M'|| Fam.FamilyID AS MRIN,  "
+ "  UPPER(Nch.Surname) COLLATE Nocase ||' '||  "
+ "  Nch.Suffix COLLATE Nocase ||', '||  "
+ "  Nch.Prefix COLLATE Nocase ||' '||  "
+ "  Nch.Given COLLATE Nocase ||'-'|| "
+ "  Ch.ChildID "
+ "   AS 'Sharer Name' ,  "
+ "  Count( 1 ) -1 AS Dup, "
+ "  E.SortDate AS SortDate , "
+ "  Ch.IsPrivate AS IsPrivate "
+ "FROM  "
+ "  Familytable Fam   "
+ "  LEFT JOIN Nametable Npers ON Fam.Fatherid = Npers.Ownerid AND +Npers.Isprimary = 1  "
+ "  LEFT JOIN Childtable Ch ON Fam.Familyid = Ch.Familyid "
+ "  LEFT JOIN Nametable Nch ON Ch.Childid = Nch.Ownerid  AND +Nch.Isprimary = 1 "
+ "  LEFT JOIN Eventtable E ON Ch.Childid = E.Ownerid AND E.EVENTTYPE = 1 "
+ "  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID "
+ "  LEFT JOIN PlaceTable AS Site ON E.SiteID = Site.PlaceID   "
+ "WHERE  "
+ "  Ch.RelFather = 0  "
+ "GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 "
+ " "
+ "UNION ALL "
+ " "
+ "SELECT  "
+ "" // Invented fact for Mother having child "
+ "  Npers.Ownerid AS RIN ,  "
+ "  UPPER(Npers.Surname) COLLATE Nocase ||' '||  "
+ "  Npers.Suffix COLLATE Nocase ||', '||  "
+ "  Npers.Prefix COLLATE Nocase ||' '||  "
+ "  Npers.Given COLLATE Nocase AS Person,  "
+ "  'Parent' AS Role ,  "
+ "  'Mothered' AS Fact ,  "
+ "  E.Date AS rawDate , "
+ "  CAST( E.Details AS TEXT ) AS Detail,   "
+ "  Site.Name COLLATE Nocase AS Site, "
+ "  Place.Name COLLATE Nocase AS Place, "
+ "  'M'|| Fam.FamilyID AS MRIN,  "
+ "  UPPER(Nch.Surname) COLLATE Nocase ||' '||  "
+ "  Nch.Suffix COLLATE Nocase ||', '||  "
+ "  Nch.Prefix COLLATE Nocase ||' '||  "
+ "  Nch.Given COLLATE Nocase ||'-'|| "
+ "  Ch.ChildID "
+ "   AS 'Sharer Name' ,  "
+ "  Count( 1 ) -1 AS Dup, "
+ "  E.SortDate AS SortDate , "
+ "  Ch.IsPrivate AS IsPrivate "
+ "FROM  "
+ "  Familytable Fam   "
+ "  LEFT JOIN Nametable Npers ON Fam.Motherid = Npers.Ownerid AND +Npers.Isprimary = 1  "
+ "  LEFT JOIN Childtable Ch ON Fam.Familyid = Ch.Familyid "
+ "  LEFT JOIN Nametable Nch ON Ch.Childid = Nch.Ownerid AND +Nch.Isprimary = 1 "
+ "  LEFT JOIN Eventtable E ON Ch.Childid = E.Ownerid AND E.EventType = 1 "
+ "  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID "
+ "  LEFT JOIN PlaceTable AS Site ON E.SiteID = Site.PlaceID   "
+ "WHERE  "
+ "  Ch.RelMother = 0  "
+ "GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 "
+ " "
+ "UNION ALL "
+ " "
+ "SELECT "
+ "" // Alternate Name as a Fact "
+ "  Npers.Ownerid AS RIN,  "
+ "  UPPER(Npers.Surname) COLLATE Nocase ||' '||  "
+ "  Npers.Suffix COLLATE Nocase ||', '||   "
+ "  Npers.Prefix COLLATE Nocase ||' '||   "
+ "  Npers.Given COLLATE Nocase AS Person ,  "
+ "  'Principal' AS Role,  "
+ "  'Alternate name' AS Event, "
+ "  Nalt.Date AS rawDate , "
+ "  CASE Nalt.NameType "
+ "    WHEN 1 THEN 'AKA' "
+ "    WHEN 2 THEN 'Birth' "
+ "    WHEN 3 THEN 'Immigrant' "
+ "    WHEN 4 THEN 'Maiden' "
+ "    WHEN 5 THEN 'Married' "
+ "    WHEN 6 THEN 'Nickname' "
+ "    WHEN 7 THEN 'Other spelling' "
+ "    ELSE NULL "
+ "  END AS Detail,  "
+ "  NULL AS Site,  "
+ "  NULL AS Place,  "
+ "  NULL AS MRIN, "
+ "  UPPER(Nalt.Surname) COLLATE Nocase ||' '||  "
+ "    Nalt.Suffix COLLATE Nocase ||', '||  "
+ "    Nalt.Prefix COLLATE Nocase ||' '||  "
+ "    Nalt.Given COLLATE Nocase  "
+ "    AS 'Alternate Name' ,           "
+ "  Count( 1 ) - 1 AS Dup, "
+ "  Nalt.SortDate AS SortDate , "
+ "  Nalt.IsPrivate AS IsPrivate "
+ "  "
+ "FROM Nametable AS Nalt "
+ "  LEFT JOIN NameTable AS Npers ON Nalt.OwnerID = Npers.OwnerID AND +Npers.IsPrimary = 1 "
+ "WHERE +Nalt.Isprimary = 0  "
+ "GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 "
+ " "
+ "UNION ALL "
+ " "
+ "SELECT  "
+ "" // shared events from WitnessTable as Facts for Sharers  "
+ "  W.PersonID AS RIN,  "
+ "" // to bring out Treeless witnesses "
+ "  CASE W.PersonID    "
+ "  WHEN 0 THEN "
+ "  UPPER(W.Surname) COLLATE Nocase ||' '||  "
+ "    W.Suffix COLLATE Nocase ||', '||   "
+ "    W.Prefix COLLATE Nocase ||' '||   "
+ "    W.Given COLLATE Nocase   "
+ "  ELSE "
+ "  UPPER(Nwitn.Surname) COLLATE Nocase ||' '||  "
+ "    Nwitn.Suffix COLLATE Nocase ||', '||   "
+ "    Nwitn.Prefix COLLATE Nocase ||' '||   "
+ "    Nwitn.Given COLLATE Nocase "
+ "  END AS Person,   "
+ "  Rolename COLLATE Nocase AS Role,  "
+ "  Fact.Name COLLATE Nocase AS Event,  "
+ "  E.Date AS rawDate , "
+ "  CAST( E.Details AS TEXT ) AS Detail,   "
+ "  Site.Name COLLATE Nocase AS Site, "
+ "  Place.Name COLLATE Nocase AS Place,   "
+ "  CASE E.OwnerType   "
+ "    WHEN 1 THEN "
+ "      'M'|| Fam.FamilyID       "
+ "    ELSE NULL "
+ "  END "
+ "     AS MRIN,  "
+ "  CASE E.OwnerType   "
+ "    WHEN 0 THEN     "
+ "      UPPER(Npers.Surname) COLLATE Nocase ||' '||  "
+ "      Npers.Suffix COLLATE Nocase ||', '||   "
+ "      Npers.Prefix COLLATE Nocase ||' '||   "
+ "      Npers.Given COLLATE Nocase  ||'-'||       "
+ "      E.OwnerID "
+ "    WHEN 1 THEN "
+ "      IFNULL( UPPER(Nhusb.Surname) COLLATE Nocase ||' '||  "
+ "      Nhusb.Suffix COLLATE Nocase ||', '||   "
+ "      Nhusb.Prefix COLLATE Nocase ||' '||   "
+ "      Nhusb.Given COLLATE Nocase ||'-'||       "
+ "      Nhusb.OwnerID , '' ) "
+ "      || "
+ "      IFNULL( ' & '|| UPPER(Nwife.Surname) COLLATE Nocase ||' '||  "
+ "      Nwife.Suffix COLLATE Nocase ||', '||  "
+ "      Nwife.Prefix COLLATE Nocase ||' '||  "
+ "      Nwife.Given COLLATE Nocase ||'-'||       "
+ "      Nwife.OwnerID , '' ) "
+ "  END     "
+ "    AS 'Sharer Name', "
+ "  Count( 1 )-1 AS Dup, "
+ "  E.SortDate , "
+ "  E.IsPrivate AS IsPrivate "
+ "FROM  "
+ "  Witnesstable AS W "
+ "  LEFT JOIN Eventtable AS E ON W.EventID = E.EventID  "
+ "  LEFT JOIN Facttypetable AS Fact ON E.Eventtype = Fact.Facttypeid     "
+ "  LEFT JOIN Roletable AS Role ON   W.Role = Role.Roleid     "
+ "  LEFT JOIN NameTable AS Nwitn ON W.Personid = Nwitn.Ownerid AND +Nwitn.IsPrimary = 1 "
+ "  LEFT JOIN NameTable AS Npers ON E.OwnerID = Npers.Ownerid AND +Npers.IsPrimary = 1 "
+ "  LEFT JOIN FamilyTable AS Fam ON E.OwnerID = Fam.FamilyID "
+ "  LEFT JOIN NameTable AS Nhusb ON Fam.FatherID = Nhusb.OwnerID AND +Nhusb.Isprimary =1  "
+ "  LEFT JOIN NameTable AS Nwife ON Fam.MotherID = Nwife.OwnerID AND +Nwife.Isprimary = 1 "
+ "  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID "
+ "  LEFT JOIN PlaceTable AS Site ON E.SITEID = Site.PlaceID "
+ "GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 "
+ " "
+ "ORDER BY RIN, SortDate "
+ ") "
+ "; ";
                        return commandText;
                        }
                }

          public static string dBEventsSharableFacts
                { 
                    get
                        {
                            string commandText = ""
    + "" // SharableFacts2.sql "
    + "" // Counts events having citations "
    + "" // grouped by EventDate, \"Cited by\", \"Description\", \"Event Place\", \"Event Site\", \"Source Name\", \"Cit Fields\" "
    + "" //   "
    + "" // SourceList.sql "
    + "" // by ve3meo 2011-11-03 "
    + "" // A powerful query emulating the RM4 Source List Report that can be sorted and filtered in many ways. "
    + "" // Reports every citation, showing the Source Name, Citation Fields, Quality, Text and Comment, the citing fact and event date and the person  "
    + "" // with whom it is associated, the Source Template and key ID's for finding the corresponding records in the database tables. "
    + "" // "
    + "" // Based on prior work: "
    + "" //  Source Template List with Citation Texts "
    + "" //  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 ve3meo - to concatenate names for easier viewing        SourceTemplateListWithCitationDetails2.sql "
    + "" // 2011-11-02 ve3meo - added Citation Details and Comments            SourceTemplateListWithCitationDetails3.sql "
    + "" //            added Source Details Fields (Cit Fields), Citation Quality, Event Date, rearranged table order "
    + "" //                                                                    SourceTemplateListWithCitationDetails4.sql "
    + "" // 2011-11-03 ve3meo corrected misreporting of RIN and Person having an Alternate Name fact source "
    + "" //                                                         renamed    SourceList.sql "
    + "" // 2011-11-04 ve3meo further corrected reporting of false citations from non-existing Alternate Name facts "
    + "" // 2011-11-06 now reports Free Form and orphaned citations; count of media items linked to citation; "
    + "" //            improved format of CitFields; CAST CitText and CitComments AS TEXT for wider compatability with SQLite managers "
    + "" // 2011-11-18 ve3meo added SourceTable FIELDS, Event Place and Site to output "
    + "  "
    + "" // This complex SELECT processes data from the UNION of 5 SELECTs that it queries, setting the order of display  "
    + "SELECT * FROM "
    + "( "
    + "SELECT  "
    + "       \"Source Name\", " // "
    + "" // SourceTable FIELDS  "
    + "       REPLACE(                                                                                                 "
    + "        REPLACE( "
    + "         REPLACE( "
    + "          REPLACE( "
    + "           REPLACE( "
    + "            REPLACE(SUBSTR(CAST(SrcFIELDS AS TEXT),55, LENGTH(CAST(SrcFIELDS AS TEXT))-87), '</Name>', ':'||CAST (X'09' AS TEXT)), "
    + "           '<Field><Name>', ''), "
    + "          '</Value>',''), "
    + "         '<Value>',''), "
    + "        '<Value/>',''), "
    + "       '</Field>',CAST (X'0D' AS TEXT)) "
    + "       AS 'Src Fields', "
    + "" // "
    + "" // CitationTable FIELDS parsed out from XML in a blob "
    + "       REPLACE(                                                                                                 "
    + "        REPLACE( "
    + "         REPLACE( "
    + "          REPLACE( "
    + "           REPLACE( "
    + "            REPLACE(SUBSTR(CAST(CitFIELDS AS TEXT),55, LENGTH(CAST(CitFIELDS AS TEXT))-87), '</Name>', ':'||CAST (X'09' AS TEXT)), "
    + "           '<Field><Name>', ''), "
    + "          '</Value>',''), "
    + "         '<Value>',''), "
    + "        '<Value/>',''), "
    + "       '</Field>',CAST (X'0D' AS TEXT)) "
    + "       AS 'Cit Fields', "
    + "" // "
    + "       \"Quality\" AS 'Qual', "
    + "       CAST(\"Citation Text\" AS TEXT) AS 'Cit Text', "
    + "       CAST(\"Citation Comment\" AS TEXT) AS 'Cit Comment', "
    + "       Media, "
    + "       COUNT() -1 AS Sharables, "
    + "       \"Fact Type\" AS 'Cited by', "
    + "" // Convert Fact dates to readable form "
    + "        CASE Substr( rawDate , 1 , 1 ) "
    + "      	WHEN 'Q' THEN rawDate  "
    + "      	WHEN 'T' THEN Substr( rawDate , 2 , 20 )  "
    + "      	WHEN 'D' THEN  "
    + "      		CASE Substr( rawDate , 2 , 1 )  "
    + "      			WHEN 'A' THEN 'aft '  "
    + "      			WHEN 'B' THEN 'bef '  "
    + "      			WHEN 'F' THEN 'from '  "
    + "      			WHEN 'I' THEN 'since '  "
    + "      			WHEN 'R' THEN 'bet '  "
    + "      			WHEN 'S' THEN 'from '  "
    + "      			WHEN 'T' THEN 'to '  "
    + "      			WHEN 'U' THEN 'until '  "
    + "      			WHEN 'Y' THEN 'by '  "
    + "      		ELSE ''  "
    + "      		END  "
    + "      		||  "
    + "      		CASE Substr( rawDate , 13 , 1 )  "
    + "      			WHEN 'A' THEN 'abt '  "
    + "      			WHEN 'C' THEN 'ca '  "
    + "      			WHEN 'E' THEN 'est '  "
    + "      			WHEN 'L' THEN 'calc '  "
    + "      			WHEN 'S' THEN 'say '  "
    + "      			WHEN '6' THEN 'cert '  "
    + "      			WHEN '5' THEN 'prob '  "
    + "      			WHEN '4' THEN 'poss '  "
    + "      			WHEN '3' THEN 'lkly '  "
    + "      			WHEN '2' THEN 'appar '  "
    + "      			WHEN '1' THEN 'prhps '  "
    + "      			WHEN '?' THEN 'maybe '  "
    + "      		ELSE ''  "
    + "      		END  "
    + "      		||  "
    + "      		CASE WHEN Substr( rawDate , 3 , 1 ) = '-' THEN 'BC' ELSE '' END   "
    + "      		||  "
    + "      		CASE WHEN Substr( rawDate , 4 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 4 , 4 ) END  "
    + "      		||  "
    + "      		CASE WHEN Substr( rawDate , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 3 , 5 ) ) ELSE '' END  "
    + "      		||  "
    + "      		CASE  "
    + "      			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) <> '00' ) THEN '-??'  "
    + "      			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) == '00' ) THEN ''  "
    + "      			WHEN Substr( rawDate , 8 , 2 ) = '00' THEN ''  "
    + "      		ELSE '-' || Substr( rawDate , 8 , 2 )  "
    + "      		END  "
    + "      		||  "
    + "      		Coalesce( Nullif( '-' || Substr( rawDate , 10 , 2 ) , '-00' ) , '' )  "
    + "      		||  "
    + "      		CASE Substr( rawDate , 2 , 1 )  "
    + "      			WHEN 'R' THEN ' and '  "
    + "      			WHEN 'S' THEN ' to '  "
    + "      			WHEN 'O' THEN ' or '  "
    + "      			WHEN '-' THEN ' - '  "
    + "      		ELSE ''  "
    + "      		END  "
    + "      		||  "
    + "      		CASE Substr( rawDate , 24 , 1 )  "
    + "      			WHEN 'A' THEN 'abt '  "
    + "      			WHEN 'E' THEN 'est '  "
    + "      			WHEN 'L' THEN 'calc '  "
    + "      			WHEN 'C' THEN 'ca '  "
    + "      			WHEN 'S' THEN 'say '  "
    + "      			WHEN '6' THEN 'cert '  "
    + "      			WHEN '5' THEN 'prob '  "
    + "      			WHEN '4' THEN 'poss '  "
    + "      			WHEN '3' THEN 'lkly '  "
    + "      			WHEN '2' THEN 'appar '  "
    + "      			WHEN '1' THEN 'prhps '  "
    + "      			WHEN '?' THEN 'maybe '  "
    + "    		  ELSE ''  "
    + "    	   	END  "
    + "      		||  "
    + "      		CASE WHEN Substr( rawDate , 14 , 1 ) = '-' THEN 'BC' ELSE '' END  "
    + "      		||  "
    + "      		CASE WHEN Substr( rawDate , 15 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 15 , 4 ) END  "
    + "      		||  "
    + "      		CASE WHEN Substr( rawDate , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 14 , 5 ) ) ELSE '' END  "
    + "      		||  "
    + "      		CASE  "
    + "      			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) <> '00' ) THEN '-??'  "
    + "      			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) == '00' ) THEN ''  "
    + "      			WHEN Substr( rawDate , 19 , 2 ) = '00' THEN ''  "
    + "      		ELSE '-' || Substr( rawDate , 19 , 2 )  "
    + "      		END  "
    + "      		||  "
    + "      		Coalesce( Nullif( '-' || Substr( rawDate , 21 , 2 ) , '-00' ) , '' )  "
    + "      	ELSE ''  "
    + "        END AS 'EventDate' , "
    + "" // "
    + "        CAST(\"EventDetail\" AS TEXT) AS Description, "
    + "        \"Event Place\", "
    + "        \"Event Site\", "
    + "       \"RIN 1\", "
    + "" // Concatenate Name fields "
    + "       UPPER(\"Surname 1\") || "
    + "          CASE \"Suffix 1\" "
    + "           WHEN '' THEN '' "
    + "           ELSE ' ' || \"Suffix 1\" "
    + "          END "
    + "          ||', '||   "
    + "          \"Prefix 1\" ||' '||   "
    + "          \"Given 1\" || "
    + "          CASE "
    + "           WHEN \"Nickname 1\" NOT LIKE '' "
    + "            THEN ' \"'|| \"Nickname 1\" ||'\"' "
    + "           ELSE '' "
    + "          END  "
    + "       AS 'Person 1', "
    + "" // "
    + "       \"MRIN\", "
    + "       \"RIN 2\",  "
    + "" // Concatenate Spouse Name fields "
    + "       UPPER(\"Surname 2\") || "
    + "          CASE \"Suffix 2\" "
    + "           WHEN '' THEN '' "
    + "           ELSE ' ' || \"Suffix 2\" "
    + "          END "
    + "          ||', '||   "
    + "          \"Prefix 2\" ||' '||   "
    + "          \"Given 2\" || "
    + "          CASE "
    + "           WHEN \"Nickname 2\" NOT LIKE '' "
    + "            THEN ' \"'|| \"Nickname 2\" ||'\"' "
    + "           ELSE '' "
    + "          END  "
    + "       AS 'Person 2', "
    + "" //        "
    + "" //       \"Records\", "
    + "       \"CitID\", "
    + "       \"SrcID\", "
    + "       \"Template\", "
    + "       \"TpltID\", "
    + "       CASE WHEN \"TpltID\" < 10000  "
    + "           THEN 'OEM'  "
    + "           ELSE 'USR' "
    + "       END AS 'Tplt Type' "
    + " "
    + "FROM  "
    + "( "
    + " (  " // Start of UNION of X SELECT queries of the database "
    + "" // Person Fact Citations "
    + "SELECT   CASE "
    + "          WHEN SourceTable.TemplateID=0 THEN 'Free Form' "
    + "          WHEN SourceTemplateTable.TemplateID THEN SourceTemplateTable.Name COLLATE NOCASE "
    + "          ELSE 'ERROR: source template does not exist' "
    + "         END AS 'Template', "
    + "         SourceTable.TemplateID AS 'TpltID', "
    + "         SourceTable.SourceID AS 'SrcID', "
    + "         SourceTable.Name COLLATE NOCASE AS 'Source Name', "
    + "         SourceTable.Fields AS 'SrcFields', "
    + "         CitationTable.CitationID AS 'CitID', "
    + "         CitationTable.Fields AS 'CitFields', "
    + "         CitationTable.Quality AS 'Quality', "
    + "         CitationTable.ActualText AS 'Citation Text', "
    + "         CitationTable.Comments AS 'Citation Comment', "
    + "         'Fact - Person' AS 'Citation Type', "
    + "         FactTypeTable.Name COLLATE NOCASE AS 'Fact Type', "
    + "         EventTable.Date AS 'rawDate',          "
    + "         EventTable.Details AS 'EventDetail',          "
    + "         PlaceTable.Name COLLATE NOCASE AS 'Event Place',          "
    + "         SiteTable.Name COLLATE NOCASE AS 'Event Site', "
    + "         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     CitationTable  "
    + "         LEFT JOIN SourceTable USING (SourceID) "
    + "         LEFT JOIN SourceTemplateTable USING (TemplateID) "
    + "         INNER JOIN EventTable ON "
    + "         CitationTable.OwnerID = EventTable.EventID "
    + "         INNER JOIN FactTypeTable ON "
    + "         EventTable.EventType = FactTypeTable.FactTypeID "
    + "         INNER JOIN NameTable ON "
    + "         EventTable.OwnerID = NameTable.OwnerID          "
    + "         LEFT JOIN PlaceTable USING (PlaceID)          "
    + "         LEFT JOIN PlaceTable AS SiteTable ON          "
    + "         EventTable.SiteID=SiteTable.PlaceID "
    + "WHERE    CitationTable.OwnerType = 2 AND +NameTable.IsPrimary = 1 AND "
    + "         EventTable.OwnerType = 0 "
    + "GROUP BY \"CitID\" "
    + "  "
    + "UNION ALL "
    + "  "
    + "" // Family Fact Citations "
    + "SELECT   CASE "
    + "          WHEN SourceTable.TemplateID=0 THEN 'Free Form' "
    + "          WHEN SourceTemplateTable.TemplateID THEN SourceTemplateTable.Name COLLATE NOCASE "
    + "          ELSE 'ERROR: source template does not exist' "
    + "         END AS 'Template', "
    + "         SourceTable.TemplateID AS 'TpltID', "
    + "         SourceTable.SourceID AS 'SrcID', "
    + "         SourceTable.Name COLLATE NOCASE AS 'Source Name', "
    + "         SourceTable.Fields AS 'SrcFields', "
    + "         CitationTable.CitationID AS 'CitID', "
    + "         CitationTable.Fields AS 'CitFields', "
    + "         CitationTable.Quality AS 'Quality', "
    + "         CitationTable.ActualText AS 'Citation Text', "
    + "         CitationTable.Comments AS 'Citation Comment', "
    + "         'Fact - Family' AS 'Citation Type', "
    + "         FactTypeTable.Name COLLATE NOCASE AS 'Fact Type', "
    + "         EventTable.Date AS 'rawDate', "
    + "         EventTable.Details AS 'EventDetail',          "
    + "         PlaceTable.Name AS 'Event Place',          "
    + "         SiteTable.Name AS 'Event Site', "
    + "         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     CitationTable  "
    + "         LEFT JOIN SourceTable USING (SourceID) "
    + "         LEFT JOIN SourceTemplateTable USING (TemplateID) "
    + "         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 "
    + "         LEFT JOIN PlaceTable USING (PlaceID)          "
    + "         LEFT JOIN PlaceTable AS SiteTable ON          "
    + "         EventTable.SiteID=SiteTable.PlaceID "
    + "WHERE    CitationTable.OwnerType = 2 AND +NameTable1.IsPrimary = 1 AND "
    + "         +NameTable2.IsPrimary = 1 AND EventTable.OwnerType = 1 "
    + "GROUP BY \"CitID\" "
    + " ) "
    + "LEFT JOIN "
    + "" // count media items linkd to citation "
    + "(SELECT MediaLinkTable.OwnerID AS CitID, COUNT() AS Media "
    + "FROM multimediatable "
    + "LEFT JOIN MediaLinkTable USING(MediaID) "
    + "WHERE MediaLinkTable.OwnerType=4 "
    + "GROUP BY CitID) "
    + "USING (CitID) "
    + ")   "
    + "GROUP BY EventDate, \"Cited by\", \"Description\", \"Event Place\", \"Event Site\", \"Source Name\", \"Cit Fields\" "
    + ") "
    + "WHERE Sharables > 0 "
    + "" // Sort order - this one clusters identical Source Details Text for a given Source "
    + "" // ORDER BY \"Source Name\", \"Citation Text\", \"Citation Comment\", \"Person 1\", \"Fact Type\", \"EventDate\", \"Person 2\" "
    + "" // Sort order - this one clusters a person's citations for a given Source "
    + "" // ORDER BY \"Source Name\", \"Person 1\", \"Fact Type\", \"EventDate\", \"Person 2\", \"Citation Text\", \"Citation Comment\" "
    + "; ";
                        return commandText;
                        }
                }


          public static string dBAddressHowUsedQuery 
                { 
                    get
                        {
                            string commandText = ""
    + "" // QueryID 25"
    + "" // AddressHowUsed.sql "
    + "" // ve3meo "
    + "" // 17 Aug 2012 "
    + "/* "
    + "Lists a subset of the info of all addresses in the AddressTable and  "
    + "reports how they are used in two columns: [User type] and [Used by] "
    + "*/  "
    + " SELECT DISTINCT "
    + "   AddressTable.Name, "
    + "   Street1 || ', ' || Street2 AS Street, "
    + "   Country || ', ' || State || ', ' || City AS Place, "
    + "   AddressTable.URL, "
    + "   CASE AddressLinkTable.AddressNum "
    + "    WHEN 1 "
    + "     THEN 'Y' "
    + "    WHEN 2 "
    + "     THEN 'N' "
    + "    ELSE '?' "
    + "   END "
    + "    AS [Primary], "
    + "   CASE  "
    + "    WHEN AddressLinkTable.OwnerType IS NULL THEN 'Contact' "
    + "    WHEN AddressLinkTable.OwnerType = 0 THEN 'Person' "
    + "    WHEN AddressLinkTable.OwnerType = 1 THEN 'Family' "
    + "    WHEN AddressLinkTable.OwnerType = 3 THEN 'Source' "
    + "    WHEN AddressLinkTable.OwnerType = 6  "
    + "     THEN "
    + "     CASE ResearchTable.TaskType "
    + "      WHEN 0 THEN 'To Do Task' "
    + "      WHEN 1 THEN 'Correspondence' "
    + "      WHEN 2 THEN 'Research' " // Task moved to Research Log but RL merely copies the Address so this value may never appear "
    + "      ELSE 'Unknown ResearchTable.TaskType' "
    + "     END "
    + "    ELSE 'Unknown AddressLinkTable.OwnerType' "
    + "   END "
    + "    AS [User type],  "
    + "   CASE AddressLinkTable.OwnerType "
    + "    WHEN 0 "
    + "     THEN NameTable.Surname || ', ' || NameTable.Given || '-' || NameTable.OwnerID "
    + "    WHEN 1 "
    + "     THEN Husband.Surname || ', ' || Husband.Given || '-' || Husband.OwnerID || ' & ' "
    + "             || Wife.Surname || ', ' || Wife.Given || '-' || Wife.OwnerID "
    + "    WHEN 3 "
    + "     THEN SourceTable.Name  "
    + "          || ' ('  "
    + "          || (SELECT COUNT() FROM CitationTable C2 WHERE C2.SourceID = SourceTable.SourceID) "
    + "          || ' citations)'   "
    + "    WHEN 6 "
    + "     THEN ResearchTable.Name "
    + "    ELSE 'ERROR - Unknown AddressLinkTable.OwnerType' "
    + "    END "
    + "    AS [Used by] "
    + " FROM AddressTable  "
    + " LEFT JOIN AddressLinkTable  "
    + "  USING (AddressID) "
    + " LEFT JOIN NameTable "
    + "  ON AddressLinkTable.OwnerID = NameTable.OwnerID AND +NameTable.IsPrimary "
    + " LEFT JOIN FamilyTable "
    + "  ON AddressLinkTable.OwnerID = FamilyTable.FamilyID "
    + " LEFT JOIN NameTable AS Husband "
    + "  ON FamilyTable.FatherID = Husband.OwnerID AND +Husband.IsPrimary "
    + " LEFT JOIN NameTable AS Wife "
    + "  ON FamilyTable.MotherID = Wife.OwnerID AND +Wife.IsPrimary "
    + " LEFT JOIN SourceTable "
    + "  ON AddressLinkTable.OwnerID = SourceTable.SourceID "
    + " LEFT JOIN CitationTable "
    + "  USING (SourceID) "
    + " LEFT JOIN ResearchTable "
    + "  ON AddressLinkTable.OwnerID = ResearchTable.TaskID "
    + " ; "
    + " "
    + "/* "
    + "   AddressTable.AddressID - unique key, Address Identification Number "
    + "   AddressTable.AddressType - (0,1) , Address Type (0 = Address, 1 = Repository) "
    + "   AddressLinkTable.LinkID - unique key, Link Identification Number, sequentially ordered "
    + "   AddressLinkTable.OwnerType - (0,1,3,6), Owner Type Code  "
    + "     0 = Personal Address,  "
    + "     1 = Family Address,  "
    + "     3 = Source Repository Address,  "
    + "     6 = Correspondence or To-Do Address or Repository Address,  "
    + "     Others?)  "
    + "       "
    + "   AddressLinkTable.AddressID - Address Identification Number, linking to AddressID of AddressTable "
    + "   AddressLinkTable.OwnerID - Owner Identification Number, linking to  "
    + "     PersonID of PersonTable (OwnerType = 0),  "
    + "     FamilyID of FamilyTable (OwnerType = 1),  "
    + "     SourceID of SourceTable (OwnerType = 3),  "
    + "     TaskID of ResearchTable (OwnerType = 6),  "
    + "     Others?  "
    + "   AddressLinkTable.AddressNum -  "
    + "    Address Number (1 = Address [Primary], from Edit Address screen (OwnerType = 0 or 1);  "
    + "    Repository [Primary], from Edit Repository screen (OwnerType = 3);  "
    + "    Address or Repository, from Edit Address or Edit Repository screen (OwnerType = 6);  "
    + "    2 = Address [Secondary], from Edit Address screen (OwnerType = 0 or 1);  "
    + "    Repository [Secondary], from Edit Repository screen (OwnerType = 3)) "
    + "*/ "; 
                        return commandText;
                        }
                }


        public static string dBSourceInvisibleNameCitationsList 
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 26"
+ "" // Citations, Invisible - List.sql "
+ "/* "
+ "2012-09-10 by Tom Holden "
+ " "
+ "Lists citations for the Primary Name of a person which are invisible on  "
+ "key user screens and reports. RM4 and 5 generate these citations on import  "
+ "of GEDCOM from GenBox and Ancestry.com and, possibly, from others.  "
+ "  "
+ "Citations with CitationTable.OwnerType=7 that point to a Primary Name are invisible in  "
+ "the Edit Person and RM Explorer screens in version 5.0.4.1. They are also suppressed  "
+ "in Individual Summary and at least one Narrative Descendant report and probably others.  "
+ "Nonetheless, they are shown in the Source List report and survive Drag and Drop transfers.  "
+ " "
+ "The list produced by this query contains just the key data with which one can review "
+ "a database through RootsMagic to confirm the invisible citations and to find the  "
+ "corresponding records using SQLite to inspect the database tables. The listed data are: "
+ "CitationID - the record in the CitationTable "
+ "RIN - the PersonID in the PersonTable and the OwnerID in the NameTable; use for "
+ "  fast lookup in RootsMagic Explorer "
+ "Surname, Given names - the names from the NameTable pointed to by the OwnerID from  "
+ "  the CitationTable "
+ "Source Name - the user defined name assigned to the cited source, as seen in the  "
+ "  Source List report "
+ "Details - the field values for the Source Details for the citation are squeezed  "
+ "  between the XML tags and are what are listed under the citation in the  "
+ "  Source List report   "
+ "*/ "
+ " "
+ " "
+ "SELECT CitationID, NameTable.OwnerID AS RIN, Surname, Given,  "
+ "SourceTable.Name AS \"Source Name\", CAST(CitationTable.Fields AS TEXT) AS  "
+ "Details "
+ "FROM CitationTable "
+ "LEFT JOIN NameTable "
+ "ON CitationTable.OwnerID = NameTable.NameID "
+ "LEFT JOIN SourceTable "
+ "ON CitationTable.SourceID = SourceTable.SourceID "
+ "WHERE CitationTable.OwnerType = 7 AND +NameTable.IsPrimary "
+ "; "
+ " ";
                return commandText;
                }
        }

        public static string dBSourceInvisibleNameCitationsConvert 
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 27"
+ "" // Citations, Invisible - Convert to Personals.sql "
+ "/* "
+ "2012-09-10 by Tom Holden "
+ " "
+ "Citations with CitationTable.OwnerType=7 that point to a Primary Name are invisible in  "
+ "the Edit Person and RM Explorer screens in version 5.0.4.1. They are also suppressed  "
+ "in Individual Summary and at least one Narrative Descendant report and probably others.  "
+ "Nonetheless, they are shown in the Source List report and survive Drag and Drop transfers.  "
+ " "
+ "RM4 and 5 generate these citations on import of GEDCOM from GenBox and Ancestry.com  "
+ "and, possibly, from others.  "
+ " "
+ "This query converts all such citations to Personal citatations, making them visible  "
+ "and useful again. It does so by changing the OwnerType to 0 and the OwnerID to the PersonID "
+ "that 'owns' the person's Primary Name.  "
+ "*/ "
+ " "
+ "UPDATE OR ROLLBACK CitationTable "
+ "SET OwnerID =  "
+ "( "
+ " SELECT NameTable.OwnerID  "
+ " FROM CitationTable AS Old "
+ " LEFT JOIN NameTable "
+ " ON Old.OwnerID = NameTable.NameID "
+ " WHERE Old.CitationID = CitationTable.CitationID "
+ "), "
+ "OwnerType = 0 "
+ "WHERE CitationID IN "
+ "( "
+ " SELECT CitationID "
+ " FROM CitationTable "
+ " LEFT JOIN NameTable "
+ " ON CitationTable.OwnerID = NameTable.NameID "
+ " WHERE CitationTable.OwnerType = 7 AND +NameTable.IsPrimary "
+ ") "
+ "; ";
                return commandText;
                }
        }

        public static string dBPeopleDeleteManyColorRED 
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 28"
+ "" // DeleteByColorCode.sql "
+ "/* "
+ "2012-10-27 Tom Holden ve3meo "
+ " "
+ "Deletes Persons and Families from PersonTable and FamilyTable respectively for  "
+ "persons with a specified color code.  "
+ " "
+ "As written, the key color is RED (=1). Edit all instances of \"= 1\" to the desired code. "
+ " 0 = None,   "
+ " 1 = Red,   "
+ " 2 = Lime,   "
+ " 3 = Blue,   "
+ " 4 = Fuschia,   "
+ " 5 = Yellow,   "
+ " 6 = Aqua,   "
+ " 7 = Silver,   "
+ " 8 = Maroon,   "
+ " 9 = Green,  "
+ "10 = Navy,  "
+ "11 = Purple,  "
+ "12 = Brown,  "
+ "13 = Teal,  "
+ "14 = Gray "
+ " "
+ "N.B.: This procedure results in many types of 'phantoms' and should be followed by "
+ "the procedure DeletePhantoms.sql "
+ "*/ "
+ " "
+ "" // Delete Family where one of the spouses has a colorcode match "
+ "DELETE FROM FamilyTable WHERE (SELECT Color FROM PersonTable Where PersonID = FatherID) = 1; "
+ "DELETE FROM FamilyTable WHERE (SELECT Color FROM PersonTable Where PersonID = MotherID) = 1; "
+ "" // Delete Person from PersonTable if matched on colorcode "
+ "DELETE FROM PersonTable WHERE Color = 1; ";
                return commandText;
                }
        }

        public static string dBPeopleDeleteManyGroupNamedDELETE 
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 29"
+ "" // DeleteByNamedGroup2.sql "
+ "/* "
+ "2012-10-27 Tom Holden ve3meo "
+ "2013-01-21 v2 - deletes groups with name beginning '#DELETE#' "
+ " "
+ "Deletes Persons and Families from PersonTable and FamilyTable respectively for  "
+ "all persons in specific Named Groups having a name starting with #DELETE# . "
+ " "
+ "N.B.: This procedure results in many types of 'phantoms' and should be followed by "
+ "the procedure DeletePhantoms.sql  "
+ "*/ "
+ "" // Delete Family where one of the spouses is in the Group "
+ "" // Fathers or Husbands "
+ "DELETE "
+ "FROM FamilyTable "
+ "WHERE FatherID IN ( "
+ "		SELECT PersonID "
+ "		FROM PersonTable "
+ "			,GroupTable "
+ "		WHERE PersonID BETWEEN StartID "
+ "				AND EndID "
+ "			AND GroupID IN ( "
+ "				SELECT LabelValue "
+ "				FROM LabelTable "
+ "				WHERE UPPER(LabelName) LIKE '#DELETE#%' "
+ "				) "
+ "		); "
+ " "
+ "" // Mothers or Wives "
+ "DELETE "
+ "FROM FamilyTable "
+ "WHERE MotherID IN ( "
+ "		SELECT PersonID "
+ "		FROM PersonTable "
+ "			,GroupTable "
+ "		WHERE PersonID BETWEEN StartID "
+ "				AND EndID "
+ "			AND GroupID IN ( "
+ "				SELECT LabelValue "
+ "				FROM LabelTable "
+ "				WHERE UPPER(LabelName) LIKE '#DELETE#%' "
+ "				) "
+ "		); "
+ " "
+ "" // Delete Person from PersonTable if in the Group "
+ "DELETE "
+ "FROM PersonTable "
+ "WHERE PersonID IN ( "
+ "		SELECT PersonID "
+ "		FROM PersonTable "
+ "			,GroupTable "
+ "		WHERE PersonID BETWEEN StartID "
+ "				AND EndID "
+ "			AND GroupID IN ( "
+ "				SELECT LabelValue "
+ "				FROM LabelTable "
+ "				WHERE UPPER(LabelName) LIKE '#DELETE#%' "
+ "				) "
+ "		); ";
                return commandText;
                }
        }

        public static string dBToolsDeletePhantoms 
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 30"
+ "" // DeletePhantoms2.sql "
+ "/* "
+ "2012-10-27 Tom Holden ve3meo "
+ "2012-11-13 added query to correct PersonTable.SpouseID to 0 for those whose family (spouse)  "
+ "was deleted. "
+ "2013-01-21 v2 removed scripts that delete unused Media, Places... "
+ " "
+ "Cleans database of unused records in many tables, "
+ "some of which may give rise to phantoms in displays and reports. "
+ "These arise from incomplete cleanup by RootsMagic when persons, families, places, sources  "
+ "are deleted directly or by merging and, most certainly, by simple SQLite deletions "
+ "such as DeleteByColorCode. "
+ " "
+ "There is an as-yet-unaddressed problem with shared events arising from the deletion of the Principal; "
+ "the event is deleted so there is nothing to share. It would be desirable to convert shared events to  "
+ "unshared singular events for every sharee. "
+ " "
+ "*/ "
+ "" // Set SpouseID=0 for persons with invalid FamilyID (TAH 2012-11-13) "
+ "UPDATE PersonTable "
+ "SET SpouseID = 0 "
+ "WHERE SpouseID NOT IN ( "
+ "		SELECT FamilyID "
+ "		FROM FamilyTable "
+ "		); "
+ " "
+ "" // Delete Child from ChildTable if Child does not exist in PersonTable "
+ "DELETE "
+ "FROM ChildTable "
+ "WHERE ChildID NOT IN ( "
+ "		SELECT PersonID "
+ "		FROM PersonTable "
+ "		); "
+ " "
+ "" // Delete Child from ChildTable if FamilyID gone from FamilyTable "
+ "DELETE "
+ "FROM ChildTable "
+ "WHERE FamilyID NOT IN ( "
+ "		SELECT FamilyID "
+ "		FROM FamilyTable "
+ "		); "
+ " "
+ "" // Delete Names from NameTable if OwnerID is not a PersonID in PersonTable "
+ "DELETE "
+ "FROM NameTable "
+ "WHERE OwnerID NOT IN ( "
+ "		SELECT PersonID "
+ "		FROM PersonTable "
+ "		); "
+ " "
+ "" // Delete Individual's Events from EventTable if OwnerID not in PersonTable "
+ "DELETE "
+ "FROM EventTable "
+ "WHERE OwnerType = 0 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT PersonID "
+ "		FROM PersonTable "
+ "		); "
+ " "
+ "" // Delete Family Events from EventTable if OwnerID not in FamilyTable "
+ "DELETE "
+ "FROM EventTable "
+ "WHERE OwnerType = 1 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT FamilyID "
+ "		FROM FamilyTable "
+ "		); "
+ " "
+ "" // Clean out Citations that no do not link to an active record  "
+ "" // Personal "
+ "DELETE "
+ "FROM CitationTable "
+ "WHERE OwnerType = 0 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT PersonID "
+ "		FROM PersonTable "
+ "		); "
+ " "
+ "" // Family "
+ "DELETE "
+ "FROM CitationTable "
+ "WHERE OwnerType = 1 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT FamilyID "
+ "		FROM FamilyTable "
+ "		); "
+ " "
+ "" // Event citations "
+ "DELETE "
+ "FROM CitationTable "
+ "WHERE OwnerType = 2 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT EventID "
+ "		FROM EventTable "
+ "		); "
+ " "
+ "" // Alternate Name citatoins "
+ "DELETE "
+ "FROM CitationTable "
+ "WHERE OwnerType = 7 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT NameID "
+ "		FROM NameTable "
+ "		WHERE NOT IsPrimary "
+ "		); "
+ " "
+ "" // Delete Citations having lost their Source "
+ "" // DeleteUnsourcedCitations.sql "
+ "" // 2010-01-29 ve3meo "
+ "DELETE "
+ "FROM CitationTable "
+ "WHERE CitationID IN ( "
+ "		SELECT CitationID "
+ "		FROM CitationTable c "
+ "		LEFT JOIN SourceTable s ON c.SourceID = s.SourceID "
+ "		WHERE s.SourceID ISNULL "
+ "		); "
+ " "
+ "" // Clean up addresses "
+ "" // Addresses that are linked to non-existant persons or families "
+ "DELETE "
+ "FROM AddressTable "
+ "WHERE AddressID NOT IN ( "
+ "		" // Addresses that are linked to existing persons "
+ "		SELECT AddressID "
+ "		FROM AddressLinkTable "
+ "		WHERE OwnerType = 0 "
+ "			AND OwnerID IN ( "
+ "				SELECT PersonID "
+ "				FROM PersonTable "
+ "				) "
+ "		 "
+ "		UNION "
+ "		 "
+ "		" // Addresses that are linked to existing families "
+ "		SELECT AddressID "
+ "		FROM AddressLinkTable "
+ "		WHERE OwnerType = 1 "
+ "			AND OwnerID IN ( "
+ "				SELECT FamilyID "
+ "				FROM FamilyTable "
+ "				) "
+ "		) "
+ "	AND AddressType = 0; "
+ " "
+ "" // Remove orphaned records from AddressLinkTable "
+ "" // Links to non-existing persons "
+ "DELETE "
+ "FROM AddressLinkTable "
+ "WHERE OwnerType = 0 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT PersonID "
+ "		FROM PersonTable "
+ "		); "
+ " "
+ "" // Links to non-existing families "
+ "DELETE "
+ "FROM AddressLinkTable "
+ "WHERE OwnerType = 1 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT FamilyID "
+ "		FROM FamilyTable "
+ "		); "
+ " "
+ "" // Links to non-existing addresses "
+ "DELETE "
+ "FROM AddressLinkTable "
+ "WHERE AddressID NOT IN ( "
+ "		SELECT AddressID "
+ "		FROM AddressTable "
+ "		); "
+ " "
+ "" // DELETE Orphaned MediaLinks "
+ "" // Having a non-existant MediaID "
+ "DELETE "
+ "FROM MediaLinkTable "
+ "WHERE MediaID NOT IN ( "
+ "		SELECT MediaID "
+ "		FROM MultiMediaTable "
+ "		); "
+ " "
+ "" // Having a link to non-exsiting "
+ "" // Person media "
+ "DELETE "
+ "FROM MediaLinkTable "
+ "WHERE OwnerType = 0 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT PersonID "
+ "		FROM PersonTable "
+ "		); "
+ " "
+ "" // Family media "
+ "DELETE "
+ "FROM MediaLinkTable "
+ "WHERE OwnerType = 1 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT FamilyID "
+ "		FROM FamilyTable "
+ "		); "
+ " "
+ "" // Event Media "
+ "DELETE "
+ "FROM MediaLinkTable "
+ "WHERE OwnerType = 2 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT EventID "
+ "		FROM EventTable "
+ "		); "
+ " "
+ "" // Master Source Media "
+ "DELETE "
+ "FROM MediaLinkTable "
+ "WHERE OwnerType = 3 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT SourceID "
+ "		FROM SourceTable "
+ "		); "
+ " "
+ "" // Citation Media "
+ "DELETE "
+ "FROM MediaLinkTable "
+ "WHERE OwnerType = 4 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT CitationID "
+ "		FROM CitationTable "
+ "		); "
+ " "
+ "" // Place Media "
+ "DELETE "
+ "FROM MediaLinkTable "
+ "WHERE OwnerType = 5 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT PlaceID "
+ "		FROM PlaceTable "
+ "		); "
+ " "
+ "" // Delete orphaned Tasks "
+ "" // individuals "
+ "DELETE "
+ "FROM ResearchTable "
+ "WHERE OwnerType = 0 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT OwnerID "
+ "		FROM PersonTable "
+ "		); "
+ " "
+ "" // families "
+ "DELETE "
+ "FROM ResearchTable "
+ "WHERE OwnerType = 1 "
+ "	AND OwnerID NOT IN ( "
+ "		SELECT FamilyID "
+ "		FROM FamilyTable "
+ "		); "
+ " "
+ "" // Delete Headless Witnesses "
+ "DELETE "
+ "FROM WitnessTable "
+ "WHERE WitnessTable.PersonID > 0 "
+ "	AND WitnessTable.PersonID NOT IN ( "
+ "		SELECT PersonID "
+ "		FROM PersonTable "
+ "		); ";
                return commandText;
                }
        }

        public static string dBMediaDeleteUnused 
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 31"
+ "" // MediaDeleteUnused.sql "
+ "/*  "
+ "2013-01-21 Tom Holden ve3meo "
+ " "
+ "Deletes records from MultiMediaTable untagged by  "
+ "any Person, Family, Fact, Place, Source or Citation. "
+ " "
+ "Extracted from DeletePhantoms.sql "
+ "*/ "
+ "DELETE "
+ "FROM MultimediaTable "
+ "WHERE MediaID NOT IN ( "
+ "		" // Person media "
+ "		SELECT MediaID "
+ "		FROM MediaLinkTable "
+ "		WHERE OwnerType = 0 "
+ "			AND OwnerID IN ( "
+ "				SELECT PersonID "
+ "				FROM PersonTable "
+ "				) "
+ "		 "
+ "		UNION "
+ "		 "
+ "		" // Family media "
+ "		SELECT MediaID "
+ "		FROM MediaLinkTable "
+ "		WHERE OwnerType = 1 "
+ "			AND OwnerID IN ( "
+ "				SELECT FamilyID "
+ "				FROM FamilyTable "
+ "				) "
+ "		 "
+ "		UNION "
+ "		 "
+ "		" // Event Media "
+ "		SELECT MediaID "
+ "		FROM MediaLinkTable "
+ "		WHERE OwnerType = 2 "
+ "			AND OwnerID IN ( "
+ "				SELECT EventID "
+ "				FROM EventTable "
+ "				) "
+ "		 "
+ "		UNION "
+ "		 "
+ "		" // Master Source Media "
+ "		SELECT MediaID "
+ "		FROM MediaLinkTable "
+ "		WHERE OwnerType = 3 "
+ "			AND OwnerID IN ( "
+ "				SELECT SourceID "
+ "				FROM SourceTable "
+ "				) "
+ "		 "
+ "		UNION "
+ "		 "
+ "		" // Citation Media "
+ "		SELECT MediaID "
+ "		FROM MediaLinkTable "
+ "		WHERE OwnerType = 4 "
+ "			AND OwnerID IN ( "
+ "				SELECT CitationID "
+ "				FROM CitationTable "
+ "				) "
+ "		 "
+ "		UNION "
+ "		 "
+ "		" // Place Media "
+ "		SELECT MediaID "
+ "		FROM MediaLinkTable "
+ "		WHERE OwnerType = 5 "
+ "			AND OwnerID IN ( "
+ "				SELECT PlaceID "
+ "				FROM PlaceTable "
+ "				) "
+ "		); ";
                return commandText;
                }
        }

        public static string dBPlacesDeleteUnused 
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 32"
+ "" // PlacesDeleteUnused.sql "
+ "/*  "
+ "2013-01-21 Tom Holden ve3meo "
+ " "
+ "Deletes Places and Place Details records from PlaceTable "
+ "if unused by any Event or a Place Detail with no master Place "
+ " "
+ "Extracted from DeletePhantoms.sql "
+ "*/ "
+ "" // Delete Unused Places and Place Details "
+ "" // Places "
+ "DELETE "
+ "FROM PlaceTable "
+ "WHERE PlaceID IN ( "
+ "		SELECT PlaceID "
+ "		FROM PlaceTable "
+ "		WHERE PlaceTable.PlaceType = 0 "
+ "		 "
+ "		EXCEPT "
+ "		 "
+ "		SELECT PlaceID "
+ "		FROM EventTable "
+ "		); "
+ " "
+ "" // Place Details "
+ "DELETE "
+ "FROM PlaceTable "
+ "WHERE PlaceID IN ( "
+ "		SELECT PlaceID "
+ "		FROM PlaceTable "
+ "		WHERE PlaceTable.PlaceType = 2 "
+ "		 "
+ "		EXCEPT "
+ "		 "
+ "		SELECT SiteID "
+ "		FROM EventTable "
+ "		); "
+ " "
+ "" // Orphaned Place Details having no Master Place "
+ "DELETE "
+ "FROM PlaceTable "
+ "WHERE PlaceType = 2 "
+ "	AND MasterID IN ( "
+ "		SELECT MasterID "
+ "		FROM PlaceTable "
+ "		WHERE PlaceTable.PlaceType = 2 "
+ "		 "
+ "		EXCEPT "
+ "		 "
+ "		SELECT PlaceID "
+ "		FROM PlaceTable "
+ "		); ";
                return commandText;
                }
        }

        public static string dBEventsBirthYearMismatchList 
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 33"
+ "" // BirthYearMisMatch.sql "
+ "" // Lists individuals whose Birth Year is missing from the sidebar (and  "
+ "" // other views and reports where just the YEAR IS outputted) or  "
+ "" // mismatches the value that has been stored in the Date field of the Birth fact. "
+ "" // #1 2010-01-16 ve3meo "
+ "" // #2 2010-01-17 ve3meo rev to compare years <1000 "
+ "" // #3 2010-01-17 ve3meo rev to count multiple Birth Facts "
+ "" // #4 2012-01-26 ve3meo corrected overcount of Birth Facts due to alternate names. "
+ " "
+ "SELECT    "
+ "  RIN ,  "
+ "  Surname ,  "
+ "  Suffix ,  "
+ "  Prefix ,  "
+ "  Given AS 'Given Name(s)',  "
+ "  BirthYear AS 'Birth Year',  "
+ "  Date AS 'Birth Fact Date',  "
+ "  IsPrimary AS 'Primary?',  "
+ "  BirthCount AS 'Birth Facts'  "
+ "FROM  "
+ "  (  "
+ "    SELECT  "
+ "      N.Ownerid AS Rin ,  "
+ "      N.Surname COLLATE Nocase ,  "
+ "      N.Suffix COLLATE Nocase ,  "
+ "      N.Prefix COLLATE Nocase ,  "
+ "      N.Given COLLATE Nocase ,  "
+ "      N.Birthyear ,  "
+ "      E.Date ,  "
+ "      E.Isprimary ,  "
+ "      Length( N.Birthyear ) AS Bystrlen , " // Birth year string length "
+ "      Count( 1 ) AS Birthcount            " // count up multiple Birth facts "
+ "    FROM  "
+ "      Nametable N ,  "
+ "      Eventtable E  "
+ "    WHERE  "
+ "      N.Ownerid = E.Ownerid AND E.Eventtype = 1 AND E.Ownertype = 0 AND +N.IsPrimary "
+ "    GROUP BY  "
+ "      1  "
+ "  )  "
+ "WHERE  "
+ "  NOT Like( Birthyear , Substr( Date , 8 - Bystrlen , Bystrlen ) )   " // the mis-match test,  "
+ "  " // cannot find Birth Year where it should be in the Date field "
+ "  AND Date NOT LIKE '.' " // no date entered "
+ "  AND Date NOT LIKE 'TUNKNOWN' " // example of suppressing a not bothersome Date value    "
+ "ORDER BY  "
+ "  RIN ; ";
                return commandText;
                }
        }

        public static string dBEventsDeathYearMismatchList 
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 34"
+ "" // DeathYearMisMatch.sql "
+ "" // Lists individuals whose Death Year is missing from the NameTable (and thus  "
+ "" // missing from views and reports where just the YEAR is outputted) or  "
+ "" // mismatches the value that has been stored in the Date field of the Death fact. "
+ "" // #1 2010-06-03 created by ve3meo, based on BirthYearMismatch.sql "
+ "" // #2 2012-01-27 corrected overcount of Death fact due to alternate names "
+ " "
+ "SELECT    "
+ "  RIN ,  "
+ "  Surname ,  "
+ "  Suffix ,  "
+ "  Prefix ,  "
+ "  Given AS 'Given Name(s)',  "
+ "  DeathYear AS 'Death Year',  "
+ "  Date AS 'Death Fact Date',  "
+ "  IsPrimary AS 'Primary?',  "
+ "  DeathCount AS 'Death Facts'  "
+ "FROM  "
+ "  (  "
+ "    SELECT  "
+ "      N.Ownerid AS Rin ,  "
+ "      N.Surname COLLATE Nocase ,  "
+ "      N.Suffix COLLATE Nocase ,  "
+ "      N.Prefix COLLATE Nocase ,  "
+ "      N.Given COLLATE Nocase ,  "
+ "      N.Deathyear ,  "
+ "      E.Date ,  "
+ "      E.Isprimary ,  "
+ "      Length( N.Deathyear ) AS Bystrlen , " // Death year string length "
+ "      Count( 1 ) AS Deathcount            " // count up multiple Death facts "
+ "    FROM  "
+ "      Nametable N ,  "
+ "      Eventtable E  "
+ "    WHERE  "
+ "      N.Ownerid = E.Ownerid AND E.Eventtype = 2 AND E.Ownertype = 0 AND +N.IsPrimary "
+ "    GROUP BY  "
+ "      1  "
+ "  )  "
+ "WHERE  "
+ "  NOT Like( Deathyear , Substr( Date , 8 - Bystrlen , Bystrlen ) )   " // the mis-match test,  "
+ "  " // cannot find Death Year where it should be in the Date field "
+ "  AND Date NOT LIKE '.' " // no date entered "
+ "  AND Date NOT LIKE 'TUNKNOWN' " // example of suppressing a not bothersome Date value    "
+ "ORDER BY  "
+ "  RIN ; "
+ " ";
                return commandText;
                }
        }

        public static string dBUpdateBirthDeathYears 
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 35"
+ "" // UpdateBirthDeathYears.sql "
+ "/* "
+ "2012-11-14 Tom Holden ve3meo "
+ "2012-12-06 revA: priority to first record set to Primary, else first record "
+ " when multiple Birth or Death facts. "
+ "           revB: incorporated Christen, Baptism as alternate Birth facts; Burial, Cremation "
+ " as alternate Death facts - in that order. Same priority for Primary facts in same type. "
+ " Now supports BC dates. "
+ " "
+ "Sets Birth and Death years as seen in the sidebar index and various other "
+ "reports and displays to match the corresponding facts. "
+ " "
+ "Close and reopen RootsMagic Explorer to see the results. "
+ " "
+ "*/ "
+ "UPDATE NameTable  "
+ "  SET  "
+ "    BirthYear= "
+ "    ( "
+ "      SELECT BirthYear "
+ "      FROM "
+ "      ( "
+ "       SELECT "
+ "        E.OwnerID, "
+ "        CASE  "
+ "          WHEN E.DATE REGEXP '[DR]..\\d\\d\\d\\d.+' "
+ "          THEN CAST(MAX(SUBSTR(E.Date,3,5),0) AS NUMERIC) "
+ "          ELSE 0 "
+ "        END AS BirthYear, E.IsPrimary "
+ "       FROM  "
+ "         Nametable N ,  "
+ "         Eventtable E  "
+ "       WHERE  "
+ "         N.Ownerid = E.Ownerid AND E.Eventtype IN (1,3,7) AND E.Ownertype = 0 AND +N.IsPrimary  "
+ "       ORDER BY E.OwnerID, E.EventType, +E.IsPrimary DESC "
+ "       ) AS Births  "
+ "      WHERE NameTable.OwnerID = Births.OwnerID "
+ "     ), "
+ "    DeathYear= "
+ "    ( "
+ "      SELECT DeathYear "
+ "      FROM "
+ "      ( "
+ "       SELECT "
+ "        E.OwnerID, "
+ "        CASE  "
+ "          WHEN E.DATE REGEXP '[DR]..\\d\\d\\d\\d.+' "
+ "          THEN CAST(MAX(SUBSTR(E.Date,3,5),0) AS NUMERIC) "
+ "          ELSE 0 "
+ "        END AS DeathYear, E.IsPrimary "
+ "       FROM  "
+ "         Nametable N ,  "
+ "         Eventtable E  "
+ "       WHERE  "
+ "         N.Ownerid = E.Ownerid AND E.Eventtype IN (2,4,5) AND E.Ownertype = 0 AND +N.IsPrimary  "
+ "       ORDER BY E.OwnerID, E.EventType, +E.IsPrimary DESC "
+ "       ) AS Deaths     "
+ "      WHERE NameTable.OwnerID = Deaths.OwnerID "
+ "    ) "
+ "; "
+ " "
+ " ";
                return commandText;
                }
        }

        public static string dBPeopleNamesADDMarried 
        { 
            get
                {
                    string commandText = ""
    + "" // QueryID 36"
    + "" // Name-Add_Married.sql "
    + "/* "
    + "2012-11-24 Tom Holden ve3meo "
    + " "
    + "Inserts an Alternate Name of type \"Married\" for female \"Wife\" spouses "
    + "in a \"Family\" where no Alternate Surname matches the male \"Husband's\" and "
    + "a (family) Marriage fact exists.  "
    + " "
    + "Prefixes with \"Mrs.\". "
    + "Suffixes the birth name in parentheses (). "
    + " "
    + "Assigns the Marriage date and sortdate to the Alternate Name. "
    + " "
    + "Sets the fact to Not Private and the fact sentence to ' ', assuming the user wishes to have  "
    + "the Married Name show on the Publish Online website Name Index but not in narrative reports. It will "
    + "show in other reports but alternate names do not come out in report Name Indexes. "
    + " "
    + "Because the NameTable uses the RMNOCASE collation, the query first REINDEXes "
    + "the database against the fake RMNOCASE collation. Following the query, "
    + "it is essential to use RootsMagic's File > Database tools > Rebuild Indexes, i.e., "
    + "do not use this procedure on databases prior to RootsMagic 5. "
    + "*/ "
    + " "
    + "" // Following deletes all Alternate Names of type Married "
    + "" // DELETE FROM NameTable WHERE NameType = 5 AND NOT +IsPrimary; "
    + " "
    + "" // Name-Add_Married "
    + "REINDEX " // because following procedure does string matches on columns indexed using RMNOCASE "
    + "; "
    + " "
    + "INSERT OR REPLACE INTO NameTable "
    + "SELECT "
    + " NULL AS NameID,  "
    + " Wife.OwnerID AS OwnerID,  "
    + " Husband.Surname AS Surname,  "
    + " Husband.Given AS Given, "
    + " 'Mrs.' AS Prefix, "
    + " '(' || Wife.Surname || ', ' || Wife.Given || ')' AS Suffix,  "
    + " Wife.Nickname AS Nickname, "
    + " 5 AS NameType,  "
    + " Event.Date AS Date, " // set to ',' for undated, Event.Date to match Marriage date. "
    + " CASE Event.SortDate & 1023 "
    + "  WHEN 1023 THEN Event.SortDate " // "
    + "  ELSE Event.SortDate + 1 "
    + " END AS SortDate, "
    + "" // Event.SortDate +1 AS SortDate, " // set to 9223372036854775807 to sort with undated Alt Names, Event.SortDate to match Marriage "
    + " 0 AS IsPrimary, "
    + " 0 AS IsPrivate, " // set to 1 to make Private "
    + " 0 AS Proof, "
    + " 0.0 AS EditDate, "
    + "" // 'After [person:hisher] marriage, [person] was also known as [Desc].' AS Sentence, "
    + " ' ' AS Sentence, " // a blank space to prevent the default sentence from being outputted "
    + " '' AS Note, "
    + " Wife.BirthYear AS BirthYear, "
    + " Wife.DeathYear AS DeathYear "
    + "FROM NameTable Wife   "
    + "INNER JOIN FamilyTable Family ON Wife.OwnerID=MotherID AND +Wife.IsPrimary "
    + "INNER JOIN NameTable Husband ON FatherID = Husband.OwnerID AND +Husband.IsPrimary "
    + "INNER JOIN EventTable Event ON Family.FamilyID = Event.OwnerID AND Event.EventType = 300 "
    + "INNER JOIN PersonTable Person2 ON Wife.OwnerID = Person2.PersonID AND Person2.Sex=1 "
    + "INNER JOIN PersonTable Person1 ON Husband.OwnerID = Person1.PersonID AND Person1.Sex=0 "
    + "WHERE  "
    + " Wife.Surname NOT LIKE Husband.Surname AND "
    + " Wife.OwnerID NOT IN "
    + "( "
    + "" // Wives with Alternate Surnames matching Husband's Surname "
    + "SELECT Wife.OwnerID " //, Wife.IsPrimary, Wife.Given, Wife.Surname, Husband.Surname  "
    + "FROM NameTable Wife   "
    + "INNER JOIN FamilyTable ON Wife.OwnerID=MotherID AND NOT +Wife.IsPrimary "
    + "INNER JOIN NameTable Husband ON FatherID = Husband.OwnerID AND +Husband.IsPrimary "
    + "WHERE Wife.Surname LIKE Husband.Surname "
    + "   OR Wife.Surname LIKE Wife.Surname || '-' || Husband.Surname "
    + ") "
    + "; "
    + " ";
//+ "" //  BE SURE TO REBUILD INDEXES IN ROOTSMAGIC ;";
                return commandText;
                }
        }

        public static string dBPeopleNamesSETMarried 
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 37"
+ "" // AltName-SetTypeMarried.sql "
+ "/* 2012-09-22 Tom Holden "
+ "Sets the NameType to 5=Married for the Alt Name of all wives  "
+ "that is the same surname as the husband's. "
+ "*/  "
+ " "
+ "UPDATE NameTable SET NameType = 5 "
+ "WHERE NameID IN "
+ "( "
+ "SELECT Wife.NameID  "
+ "FROM NameTable AS Wife "
+ "INNER JOIN FamilyTable AS Family  "
+ "ON Wife.OwnerID = Family.MotherID  "
+ "AND NOT +Wife.IsPrimary "
+ "INNER JOIN NameTable AS Husband  "
+ "ON Family.FatherID = Husband.OwnerID  "
+ "AND +Husband.IsPrimary  "
+ "AND Wife.Surname LIKE Husband.Surname "
+ ") "
+ "; ";
                return commandText;
                }
        }

        public static string dBMediaResetType
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 38"
+ "" // MediaTypeReset.sql "
+ "/* "
+ "2012-12-04 Tom Holden ve3meo "
+ "2012-12-09 restricted Image file types to those rendered by Viewer/Reporter "
+ " "
+ "Users can mistakenly add image files to the Media Gallery with the  "
+ "wrong MediaType which prevents the images from being used in reports. "
+ "MediaType is normally set by the user selection in the Add Media Item dialog "
+ "and cannot be changed from within RootsMagic 6.0.0.2 and earlier. "
+ "This script sets the MediaType according to the file extension of the media file. "
+ "*/ "
+ " "
+ "" // Clear all previous settings "
+ "UPDATE MultimediaTable "
+ " SET MediaType=0 "
+ "; "
+ "" // Set Image files (only RM Add Media filters + .targ, .tiff because of viewer/reporter limitations) "
+ "UPDATE MultimediaTable "
+ " SET MediaType=1 "
+ "  WHERE LOWER(SUBSTR(MediaFile,-4))  "
+ "  IN ('.bmp','.gif','.jpg','jpeg','.png','.tga','targ','.tif','tiff') "
+ ";  "
+ "" // Set Sound files (RM Add Media filters on just .wav, .mid, .mp3 but no player to set constraints) "
+ "UPDATE MultimediaTable "
+ " SET MediaType=3 "
+ "  WHERE LOWER(SUBSTR(MediaFile,-4))  "
+ "  IN ('.wav','.mid','.mp3','.ogg','.gsm','.dct','flac','aiff','.vox','.raw','.wma','.aac') "
+ "  OR LOWER(SUBSTR(MediaFile,-3))  "
+ "  IN ('.au','.ra','ram','dss','msv','dvf','ape') "
+ "; "
+ "" // Set Video files (RM filters on Add Media only for .avi, .mov, .mpg, .mpeg, .wmv but no player to set constraints) "
+ "UPDATE MultimediaTable "
+ " SET MediaType=4 "
+ "  WHERE LOWER(SUBSTR(MediaFile,-4))  "
+ "  IN ('.3gp','.asf','.avi','.dat','.flv','.m4v','.mkv','.mov','mpeg','.mpg','.mpe','.swf','.wmv') "
+ "; "
+ "" // Set files, other than previously set Image, Sound, Video types, to File type "
+ "UPDATE MultimediaTable "
+ " SET MediaType=2 "
+ "  WHERE MediaType=0 "
+ "; "
+ " ";
                return commandText;
                }
        }

        public static string dBMediaTagsCopyShared2Personal
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 39"
+ "" // MediaTags-CopySharedFactsMediaToShareePersonal.sql "
+ "/* "
+ "2012-12-27 Tom Holden ve3meo "
+ "2012-12-28 V1 complete "
+ "2013-01-05 1.1 outputs media tag for sharees having no defined role in the event.  "
+ "           Sentence in tag description reads \"Shared in the ...\". "
+ " "
+ "Creates a media tag, for media already tagged to a shared event,  "
+ " to the Persons sharing the event. The Description contains "
+ " the original Description appended with the Person's role name,  "
+ " the event the Person shared in and the name(s) of the Principal(s) "
+ " in the event. "
+ " "
+ "Version 1 deals with tags for shared non-family and family events. "
+ " "
+ "Use MediaTags-DeletePersonalHavingFactDupes.sql to delete tags created by this script. "
+ "*/ "
+ " "
+ " "
+ "INSERT OR REPLACE INTO MediaLinkTable "
+ " "
+ "" // tags for shared individual (non-family) events "
+ "SELECT "
+ " NULL AS LinkID,  "
+ " MediaID, "
+ " 0 AS OwnerType, "
+ " OwnerID, "
+ " 0 AS IsPrimary, "
+ " 0 AS Include1, "
+ " 0 AS Include2, "
+ " 0 AS Include3, "
+ " 0 AS Include4, "
+ " 0 AS SortOrder, "
+ " 0 AS RectLeft, "
+ " 0 AS RectTop, "
+ " 0 AS RectRight, "
+ " 0 AS RectBottom, "
+ " '' AS Note, "
+ " Caption, "
+ " RefNumber, "
+ " Date, "
+ " SortDate, "
+ " Description "
+ "FROM "
+ "(  "
+ "SELECT "
+ " ML.MediaID AS MediaID,  "
+ " W.PersonID AS OwnerID, "
+ " ML.Caption AS Caption, "
+ " ML.RefNumber AS RefNumber, "
+ " ML.Date AS Date, "
+ " ML.SortDate AS SortDate, "
+ " ML.Description "
+ "  || ' ' "
+ "  || ifnull(R.RoleName, 'Shared') "
+ "  || ' in the '  "
+ "  || LOWER(F.Name)  "
+ "  ||  ' of '  "
+ "  || N.Given  "
+ "  || ' '  "
+ "  || N.Surname  "
+ "  || '-'  "
+ "  || N.OwnerID "
+ "  AS Description "
+ "FROM MediaLinkTable ML "
+ "INNER JOIN EventTable E "
+ " ON ML.OwnerID = E.EventID "
+ " AND ML.OwnerType = 2 " // Event "
+ " AND E.OwnerType = 0 " // Person "
+ "INNER JOIN WitnessTable W "
+ " USING(EventID) "
+ "INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID "
+ "LEFT JOIN RoleTable R ON W.Role = R.RoleID "
+ "INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary "
+ "WHERE W.PersonID > 0 "
+ "AND ML.MediaID || '.' || W.PersonID "
+ "NOT IN  "
+ "( "
+ " SELECT DISTINCT MediaID || '.' || OwnerID   "
+ " FROM MediaLinkTable "
+ " WHERE OwnerType = 0 "
+ " ORDER BY MediaID, OwnerID "
+ " ) "
+ " "
+ "UNION "
+ "" // tags for shared family events "
+ "SELECT "
+ " ML.MediaID AS MediaID,  "
+ " W.PersonID AS OwnerID, "
+ " ML.Caption AS Caption, "
+ " ML.RefNumber AS RefNumber, "
+ " ML.Date AS Date, "
+ " ML.SortDate AS SortDate, "
+ " ML.Description "
+ "  || ' ' "
+ "  || ifnull(R.RoleName, 'Shared') "
+ "  || ' in the '  "
+ "  || LOWER(F.Name)  "
+ "  ||  ' of '  "
+ "  || Husb.Given  "
+ "  || ' '  "
+ "  || Husb.Surname  "
+ "  || '-'  "
+ "  || Husb.OwnerID "
+ "  || ' & ' "
+ "  || Wife.Given "
+ "  || ' '  "
+ "  || Wife.Surname  "
+ "  || '-'  "
+ "  || Wife.OwnerID "
+ "   "
+ "  AS Description "
+ "FROM MediaLinkTable ML "
+ "INNER JOIN EventTable E "
+ " ON ML.OwnerID = E.EventID "
+ " AND ML.OwnerType = 2 " // Event "
+ " AND E.OwnerType = 1 " // FamilyPerson "
+ "INNER JOIN FamilyTable Fam "
+ " ON E.OwnerID = Fam.FamilyID "
+ "LEFT JOIN NameTable Husb "
+ " ON Fam.FatherID = Husb.OwnerID AND +Husb.IsPrimary "
+ "LEFT JOIN NameTable Wife "
+ " ON Fam.MotherID = Wife.OwnerID AND +Wife.IsPrimary "
+ "INNER JOIN WitnessTable W "
+ " USING(EventID) "
+ "INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID "
+ "LEFT JOIN RoleTable R ON W.Role = R.RoleID "
+ "" //INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary "
+ "WHERE W.PersonID > 0 "
+ "AND W.PersonID || '.' || ML.MediaID "
+ "NOT IN  "
+ "( "
+ " SELECT DISTINCT MediaID || '.' || OwnerID   "
+ " FROM MediaLinkTable "
+ " WHERE OwnerType = 0 "
+ " ORDER BY MediaID, OwnerID "
+ " ) "
+ ") "
+ " "
+ ";   "
+ " "
+ "" // USE MediaTags-DeletePersonalHavingFactDupes.sql to delete tags created by above "
+ "; ";
                return commandText;
                }
        }

        public static string dBMediaTagsDeletePersonalsDuplicatingEvents 
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 40"
+ "" // MediaTags-DeletePersonalHavingFactDupes.sql "
+ "/* "
+ "2012-12-28 Tom Holden ve3meo "
+ " "
+ "Deletes Personal MediaTags (Person-General) for media "
+ "having duplicate tags to an event for the same person. "
+ "*/  "
+ " "
+ "" // SELECT LinkID, MediaID, OwnerID " // testing  "
+ "DELETE "
+ "FROM MediaLinkTable  "
+ "WHERE OwnerType=0  "
+ "AND MediaID || '.' || OwnerID "
+ "IN "
+ "( "
+ "" // build ordered list of event mediatags in form MediaID.PersonID "
+ "SELECT DISTINCT MediaID || '.' || PersonID AS MediaPerson "
+ "FROM "
+ "( "
+ "" // LinkIDs for PersonID's having Indiv Event MediaTags "
+ "SELECT DISTINCT LinkID, MediaID, E.OwnerID AS PersonID FROM MediaLinkTable ML "
+ "INNER JOIN EventTable E "
+ "ON ML.OwnerID = E.EventID "
+ " AND ML.OwnerType=2 "
+ " AND E.OwnerType = 0 "
+ " "
+ "UNION "
+ " "
+ "" // LinkIDs for Husband PersonID's having Family Event MediaTags "
+ "SELECT DISTINCT LinkID, MediaID, F.FatherID AS HusbandID FROM MediaLinkTable ML "
+ "INNER JOIN EventTable E "
+ " ON ML.OwnerID = E.EventID "
+ " AND ML.OwnerType=2 "
+ " AND E.OwnerType = 1 "
+ "INNER JOIN FamilyTable F "
+ " ON E.OwnerID = F.FamilyID "
+ " "
+ "UNION "
+ " "
+ "" // LinkIDs for Wife PersonID's having Family Event MediaTags "
+ "SELECT DISTINCT LinkID, MediaID, F.MotherID AS WifeID  "
+ "FROM MediaLinkTable ML "
+ "INNER JOIN EventTable E "
+ " ON ML.OwnerID = E.EventID "
+ " AND ML.OwnerType=2 "
+ " AND E.OwnerType = 1 "
+ "INNER JOIN FamilyTable F "
+ " ON E.OwnerID = F.FamilyID "
+ ") "
+ "ORDER BY MediaPerson "
+ "); ";
                return commandText;
                }
        }

        public static string dBEventsSplitShared2Individual
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 41"
+ "" // Facts-SplitSharedToIndiv.sql "
+ "" // 2012-12-19 Tom Holden ve3meo "
+ "/*  "
+ "2012-12-21 rev. divided script into separate files.  "
+ "   "
+ "Run this query to convert shared events to Individual 'Share' events, "
+ "except Census, Residence fact types whose Fact Name is preserved.  "
+ " "
+ "Creates a new Individual Fact Type named and abbreviated 'Share', without the quotes, "
+ "if none exists. "
+ " "
+ "Witness not in File and their note is appended to the shared event note "
+ "and enclosed in privacy brackets, thus {** ... **}. "
+ " "
+ "See end of script for steps to UNDO the changes "
+ " "
+ "*/ "
+ " "
+ "" // DELETE FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share'; "
+ "" // Create Share facttype if it does not exist "
+ "INSERT OR IGNORE INTO FactTypeTable "
+ " "
+ "SELECT  "
+ "  ( "
+ "   SELECT FactTypeID FROM FactTypeTable "
+ "   WHERE LOWER(Abbrev) LIKE 'share' "
+ "   ) AS FactTypeID, "
+ "  0 AS OwnerType, "
+ "  'Share' AS Name, "
+ "  'Share' AS Abbrev, "
+ "  'EVEN' AS GedcomTag, "
+ "  1 AS UseValue, "
+ "  1 AS UseDate, "
+ "  1 AS UsePlace, "
+ "  '[person] was involved as a< [Desc]>< [Date]>< [PlaceDetails]>< [Place]>.' AS Sentence, "
+ "  -1 AS Flags "
+ "; "
+ " "
+ " "
+ "" // keep track of the current last key rowid's before the adding of the Share events "
+ "DROP TABLE IF Exists zTmpShareSplit "
+ "; "
+ " "
+ "CREATE TABLE IF NOT EXISTS zTmpShareSplit "
+ "(TableName TEXT, Maxrowid INTEGER) "
+ "; "
+ "INSERT OR REPLACE INTO zTmpShareSplit "
+ "SELECT 'FactTypeTable',  "
+ "  (SELECT FactTypeID FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share') "
+ ";  "
+ " "
+ "INSERT OR REPLACE INTO zTmpShareSplit "
+ "SELECT 'EventTable', MAX(EventID) FROM EventTable "
+ "; "
+ "INSERT OR REPLACE INTO zTmpShareSplit "
+ "SELECT 'CitationTable', MAX(CitationID) FROM CitationTable "
+ "; "
+ "INSERT OR REPLACE INTO zTmpShareSplit "
+ "SELECT 'MediaLinkTable', MAX(LinkID) FROM MediaLinkTable "
+ "; "
+ "INSERT OR REPLACE INTO zTmpShareSplit "
+ "SELECT 'URLTable', MAX(LinkID) FROM URLTable "
+ "; "
+ " "
+ " "
+ "" // Split Shared non-family facts "
+ "INSERT INTO EventTable "
+ " "
+ "SELECT "
+ "  NULL AS EventID,  "
+ "  CASE E.EventType "
+ "    WHEN 18 THEN 18  " // Census to Census "
+ "    WHEN 311 THEN 18 " // Census (family) to Census "
+ "    WHEN 29 THEN 29  " // Residence to Residence "
+ "    WHEN 310 THEN 29 " // Residence (family) to Residence "
+ "    ELSE (SELECT FactTypeID FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share') " // all others to Share "
+ "  END "
+ "    AS EventType,  "
+ "  E.OwnerType, W.PersonID, E.FamilyID, E.PlaceID, E.SiteID,  "
+ "  E.Date, E.SortDate,  "
+ "  E.EventID AS IsPrimary, " // temp store for Principal's EventID for citations, later set to 0  "
+ "  E.IsPrivate, E.Proof, E.Status, E.EditDate,  "
+ "  '' AS Sentence, "
+ "  LOWER(R.RoleName) || ' in the ' || LOWER(F.Name) ||  ' of ' || N.Given || ' ' || N.Surname || '-' || N.OwnerID AS Details, "
+ "  W.Note AS Note "
+ "FROM "
+ "  WitnessTable W  "
+ "  INNER JOIN EventTable E USING(EventID) "
+ "  INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID "
+ "  INNER JOIN RoleTable R ON W.Role = R.RoleID "
+ "  INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary "
+ "WHERE E.OwnerType = 0 " // Person "
+ "AND W.PersonID > 0 " // Person is not just a name in the WitnessTable but a person in the database tree "
+ ";  "
+ " "
+ "" // Split Shared family facts "
+ "INSERT INTO EventTable "
+ " "
+ "SELECT "
+ "  NULL AS EventID,  "
+ "  CASE E.EventType "
+ "    WHEN 18 THEN 18  " // Census to Census "
+ "    WHEN 311 THEN 18 " // Census (family) to Census "
+ "    WHEN 29 THEN 29  " // Residence to Residence "
+ "    WHEN 310 THEN 29 " // Residence (family) to Residence "
+ "    ELSE (SELECT FactTypeID FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share') " // all others to Share "
+ "  END "
+ "    AS EventType,  "
+ "  0 AS OwnerType, W.PersonID, E.FamilyID, E.PlaceID, E.SiteID,  "
+ "  E.Date, E.SortDate,  "
+ "  E.EventID AS IsPrimary, " // temp store for Principal's EventID for citations, later set to 0  "
+ "  E.IsPrivate, E.Proof, E.Status, E.EditDate,  "
+ "  '' AS Sentence, "
+ "  LOWER(R.RoleName) || ' in the ' || LOWER(F.Name) ||  ' of ' || N1.Given || ' ' || N1.Surname || '-' || N1.OwnerID || ' & ' || N2.Given || ' ' || N2.Surname || '-' || N2.OwnerID AS Details, "
+ "  W.Note AS Note "
+ "FROM "
+ "  WitnessTable W  "
+ "  INNER JOIN EventTable E USING(EventID) "
+ "  INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID "
+ "  INNER JOIN RoleTable R ON W.Role = R.RoleID "
+ "  INNER JOIN FamilyTable F ON E.OwnerID = F.FamilyID "
+ "  LEFT JOIN NameTable N1 ON F.FatherID = N1.OwnerID AND +N1.IsPrimary " // to get Husband Names "
+ "  LEFT JOIN NameTable N2 ON F.MotherID = N2.OwnerID AND +N2.IsPrimary " // to get Wife Names "
+ "WHERE E.OwnerType = 1 " // Family "
+ "AND W.PersonID > 0 " // Person is not just a name in the WitnessTable but a person in the database tree "
+ ";  "
+ " "
+ "" // Copy citation from shared event to Share events "
+ "INSERT INTO CitationTable "
+ "SELECT "
+ "  NULL AS CitationID,  "
+ "  C.OwnerType,  "
+ "  C.SourceID,  "
+ "  E.EventID AS OwnerID,  "
+ "  C.Quality, C.IsPrivate, C.Comments, C.ActualText, C.RefNumber,  "
+ "  C.CitationID AS Flags, " // temp use of Flags to link media and webtags to new citations, set to 0 later  "
+ "  C.Fields "
+ "FROM CitationTable C "
+ "INNER JOIN EventTable E ON C.OwnerID = E.IsPrimary  "
+ "AND C.OwnerType = 2 "
+ "AND E.EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable') "
+ "; "
+ " "
+ "" // Copy Media tag from shared event to Share events "
+ " "
+ "INSERT INTO MediaLinkTable "
+ " "
+ "SELECT "
+ "  NULL AS LinkID, ML.MediaID, ML.OwnerType,  "
+ "  E.EventID AS OwnerID,  "
+ "  ML.IsPrimary, ML.Include1, ML.Include2, ML.Include3, ML.Include4, ML.SortOrder,  "
+ "  ML.RectLeft, ML.RectTop, ML.RectRight, ML.RectBottom,  "
+ "  ML.Note, ML.Caption, ML.RefNumber, ML.Date, ML.SortDate, ML.Description "
+ "FROM MediaLinkTable ML "
+ "INNER JOIN EventTable E ON ML.OwnerID = E.IsPrimary " // remember IsPrimary temp holds the shared EventID  "
+ "AND ML.OwnerType =2 "
+ "AND E.EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable') "
+ "; "
+ " "
+ "" // Copy Media tag from citation of shared event to citation of Share events "
+ " "
+ "INSERT INTO MediaLinkTable "
+ " "
+ "SELECT "
+ "  NULL AS LinkID, ML.MediaID, ML.OwnerType,  "
+ "  C.CitationID AS OwnerID,  "
+ "  ML.IsPrimary, ML.Include1, ML.Include2, ML.Include3, ML.Include4, ML.SortOrder,  "
+ "  ML.RectLeft, ML.RectTop, ML.RectRight, ML.RectBottom,  "
+ "  ML.Note, ML.Caption, ML.RefNumber, ML.Date, ML.SortDate, ML.Description "
+ "FROM MediaLinkTable ML "
+ "INNER JOIN CitationTable C ON ML.OwnerID = C.Flags " // remember Flags temp holds the CitationID of the shared Event  "
+ "AND ML.OwnerType =4 "
+ "AND C.CitationID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationTable') "
+ " "
+ "; "
+ " "
+ "" // Copy WebTag from citation of shared event to citation of Share events "
+ " "
+ "INSERT INTO URLTable "
+ " "
+ "SELECT "
+ "  NULL AS LinkID, 4 AS OwnerType, C.CitationID, U.LinkType, U.Name, U.URL, U.Note "
+ "FROM URLTable U "
+ "INNER JOIN CitationTable C ON U.OwnerID = C.Flags " // remember Flags temp holds the CitationID of the shared Event   "
+ "AND U.OwnerType = 4 "
+ "AND C.CitationID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationTable') "
+ "; "
+ " "
+ "" // Append Name and Note from witness not in database to the original shared event note "
+ " "
+ "UPDATE EventTable "
+ "SET Note = "
+ "" // SELECT  "
+ "  Note ||  "
+ "" // SELECT   "
+ "  '{** ' || CAST(X'0A0D' AS TEXT) || "
+ "  (SELECT W.Given || ' ' || W.Surname || ' was ' || LOWER(R.RoleName) || '. ' "
+ "    || W.Note  "
+ "   FROM WitnessTable W "
+ "   INNER JOIN RoleTable R ON W.Role = R.RoleID "
+ "   WHERE EventTable.EventID = W.EventID "
+ "   AND W.PersonID = 0 "
+ "  ) || CAST(X'0A0D' AS TEXT) || '**}' "
+ "" //  FROM EventTable  "
+ "  WHERE EventTable.EventID  "
+ "  IN (SELECT EventID FROM WitnessTable WHERE PersonID = 0) "
+ "; "
+ " "
+ "" // Reset EventTable.IsPrimary temp used for Shared EventID to 0 "
+ "UPDATE EventTable SET IsPrimary = 0 "
+ "WHERE EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable') "
+ "; "
+ " "
+ "" // Reset CitationTable.Flags temp used for Shared Event CitationID to 0 "
+ "UPDATE CitationTable SET Flags = 0 "
+ "WHERE CitationID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationTable') "
+ "; "
+ " "
+ "" // Make a backup of the WitnessTable before wiping it "
+ "DROP TABLE IF EXISTS WitnessTableSafe "
+ "; "
+ "CREATE TABLE IF NOT EXISTS WitnessTableSafe "
+ "AS "
+ "SELECT * FROM WitnessTable "
+ "; "
+ " "
+ "/* "
+ " DONE! Shared events still shared but now the sharers also have individual unshared events "
+ " with their particular note.   "
+ " When SQLiteSpy closes the database, any temp tables in memory will be gone "
+ " but WitnessTableSafe will remain in the database and could be used to restore the Sharings "
+ " before running the script again. So, too, remains zTmpShareSplit. Facts-Split-Undo.sql "
+ " depends on their presence to reverse the splits. "
+ " "
+ " Facts-Split-HideTracks.sql drops these tables from the database, no going back. "
+ " "
+ "*/ "
+ "; "
+ " ";
                return commandText;
                }
        }

        public static string dBEventsUNSHARE
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 42"
+ "" // Facts-Unshare.sql "
+ "/* "
+ "2012-12-21 Tom Holden ve3meo "
+ " "
+ "This simple script unshares all shared facts by deleting  "
+ "all rows from the WitnessTable. "
+ " "
+ "*/ "
+ " "
+ "" // Delete Sharings "
+ "DELETE FROM WitnessTable "
+ "; "
+ " ";
                return commandText;
                }
        }

        public static string dBEventsUNDOsplitsRESHARE
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 43"
+ "" // Facts-Split-Undo.sql "
+ "/*  "
+ "2012-12-21 Tom Holden ve3meo "
+ "Reverses the changes made by Facts-SplitSharedToIndiv.sql "
+ "Requires tables zTmpShareSplit and WitnessTableSafe from the Split query "
+ " "
+ "Deletes the new Indiv Share, Census and Residence events and  "
+ "related citations, media tags and webtags "
+ "and restores the Sharings. Sort of an UNDO... but not complete; "
+ "the appends to the original fact note remain. "
+ "*/ "
+ "BEGIN TRANSACTION; "
+ "DELETE FROM EventTable WHERE EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable'); "
+ "COMMIT TRANSACTION; "
+ "BEGIN TRANSACTION; "
+ "DELETE FROM CitationTable WHERE CitationID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationTable');  "
+ "COMMIT TRANSACTION; "
+ "BEGIN TRANSACTION; "
+ "DELETE FROM MediaLinkTable WHERE LinkID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'MediaLinkTable');  "
+ "COMMIT TRANSACTION; "
+ "BEGIN TRANSACTION; "
+ "DELETE FROM URLTable WHERE LinkID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'URLTable');  "
+ "COMMIT TRANSACTION; "
+ "BEGIN TRANSACTION; "
+ "" // To restore the Sharings: "
+ "INSERT INTO WitnessTable "
+ "SELECT * FROM WitnessTableSafe ;"
+ "COMMIT TRANSACTION; ";
                return commandText;
                }
        }

        public static string dBEventsConvertSharedHideTracks
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 44"
+ "" // Facts-Split-HideTracks.sql "
+ "/*  "
+ "2012-12-21 Tom Holden ve3meo "
+ " "
+ "Drops the tables created by Facts-SplitSharedToIndiv.sql "
+ "It does not hide all traces as the Share events themselves attest and  "
+ "THERE IS NO GOING BACK: Facts-Split-Undo.sql will fail. "
+ "*/ "
+ " "
+ "DROP TABLE WitnessTableSafe "
+ "; "
+ "DROP TABLE zTmpShareSplit "
+ "; ";
                return commandText;
                }
        }

        public static string dBWebTagsMAKEfromcitationComments
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 45"
+ "" // WebTags-MakeFromAncestryComments.sql "
+ "/*  "
+ "2012-12-10 Tom Holden ve3meo "
+ " "
+ "Inspects for Citation Comments beginning with \"http://\" and generates  "
+ "a corresponding WebTag. GEDCOMs downloaded from Ancestry.com have this  "
+ "characteristic, typically followed by two linefeed-carriage returns that  "
+ "are stripped by this query. "
+ " "
+ "Due to limitations of SQLite, should the Comment contain more than the trailing "
+ "white space, the complete Comment is placed in the URL field and will  "
+ "almost certainly fail to open the web page. "
+ "*/ "
+ "" // Make Citation webtags for Citation Comments starting with \"http://\" "
+ "INSERT OR REPLACE INTO URLTable "
+ "SELECT "
+ "  (SELECT LinkID FROM URLTable WHERE OwnerType=4 AND OwnerID=Cit.CitationID) AS LinkID,  "
+ "  4 AS OwnerType,  "
+ "  Cit.CitationID AS OwnerID,  "
+ "  0 AS LinkType, "
+ "  Src.Name AS Name, "
+ "  SUBSTR(Cit.Comments,1, LENGTH(Cit.Comments)-4) AS URL, " // strip four bytes off the end, typ two pairs of CR-LF "
+ "  Cit.ActualText AS Note  "
+ "  FROM CitationTable AS Cit  "
+ "  INNER JOIN SourceTable AS Src "
+ "  USING (SourceID) "
+ "   "
+ "WHERE Cit.Comments LIKE 'http://%' "
+ "; ";
                return commandText;
                }
        }

        public static string dBWebTagsCOPYcitationtoPerson
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 46"
+ "" // WebTags-Consolidate.sql "
+ "/* "
+ "2012-12-10 Tom Holden ve3meo "
+ " "
+ "Generates a WebTag for the Individual from all WebTags attached to citations "
+ "of that Individual, the Family in which he/she is a spouse, all his/her events "
+ "and alternate names. This results in ready access to all the person's WebTags "
+ "from the button on the Edit Person screen. "
+ "URL Owner Type (0 = Individual, 3 = Source, 4 = Citation, 5 = Place, 15 = Research Item) "
+ "Citation Owner Type (0 = Personal, 1 = Family, 2 = Event, 7 = Alternate Name) "
+ "*/ "
+ " "
+ "INSERT OR REPLACE INTO URLTable "
+ "" // Citation WebTags  "
+ "" // Person for citations of Persons "
+ "SELECT "
+ "  NULL AS LinkID, "
+ "  0 AS OwnerType,  "
+ "  Cit.OwnerID AS OwnerID,  "
+ "  0 AS LinkType, "
+ "  URL.Name AS Name, "
+ "  URL.URL AS URL,  "
+ "  URL.Note AS Note  "
+ "  FROM URLTable URL "
+ "  INNER JOIN "
+ "    CitationTable AS Cit "
+ "  ON URL.OwnerID = Cit.CitationID   "
+ "  WHERE URL.OwnerType = 4 " // Citation webtag "
+ "    AND Cit.OwnerType = 0 "
+ "UNION "
+ "" // Fathers for citations of families "
+ "SELECT "
+ "  NULL AS LinkID, "
+ "  0 AS OwnerType,  "
+ "  Fam.FatherID AS OwnerID,  "
+ "  0 AS LinkType, "
+ "  URL.Name AS Name, "
+ "  URL.URL AS URL,  "
+ "  URL.Note AS Note  "
+ "  FROM URLTable URL "
+ "  INNER JOIN CitationTable AS Cit "
+ "    ON URL.OwnerID = Cit.CitationID  "
+ "  INNER JOIN FamilyTable AS Fam "
+ "    ON Cit.OwnerID = Fam.FamilyID   "
+ "  WHERE URL.OwnerType = 4 " // Citation webtag "
+ "    AND Cit.OwnerType = 1 "
+ " "
+ "UNION "
+ "" // Mothers for citations of families "
+ "SELECT "
+ "  NULL AS LinkID, "
+ "  0 AS OwnerType,  "
+ "  Fam.MotherID AS OwnerID,  "
+ "  0 AS LinkType, "
+ "  URL.Name AS Name, "
+ "  URL.URL AS URL,  "
+ "  URL.Note AS Note  "
+ "  FROM URLTable URL "
+ "  INNER JOIN CitationTable AS Cit "
+ "    ON URL.OwnerID = Cit.CitationID  "
+ "  INNER JOIN FamilyTable AS Fam "
+ "    ON Cit.OwnerID = Fam.FamilyID   "
+ "  WHERE URL.OwnerType = 4 " // Citation webtag "
+ "    AND Cit.OwnerType = 1 "
+ " "
+ "UNION "
+ "" // Person for citations of individual events "
+ "SELECT "
+ "  NULL AS LinkID, "
+ "  0 AS OwnerType,  "
+ "  Evt.OwnerID AS OwnerID,  "
+ "  0 AS LinkType, "
+ "  URL.Name AS Name, "
+ "  URL.URL AS URL,  "
+ "  URL.Note AS Note  "
+ "  FROM URLTable URL "
+ "  INNER JOIN CitationTable AS Cit "
+ "    ON URL.OwnerID = Cit.CitationID  "
+ "  INNER JOIN EventTable Evt "
+ "    ON Cit.OwnerID = Evt.EventID "
+ "  WHERE URL.OwnerType = 4 " // Citation webtag "
+ "    AND Cit.OwnerType = 2 " // Event citation "
+ "    AND Evt.OwnerType = 0 " // Person "
+ " "
+ "UNION "
+ "" // Husband for citations of Family events "
+ "SELECT "
+ "  NULL AS LinkID, "
+ "  0 AS OwnerType,  "
+ "  Fam.FatherID AS OwnerID,  "
+ "  0 AS LinkType, "
+ "  URL.Name AS Name, "
+ "  URL.URL AS URL,  "
+ "  URL.Note AS Note  "
+ "  FROM URLTable URL "
+ "  INNER JOIN CitationTable AS Cit "
+ "    ON URL.OwnerID = Cit.CitationID  "
+ "  INNER JOIN EventTable Evt "
+ "    ON Cit.OwnerID = Evt.EventID "
+ "  INNER JOIN FamilyTable Fam "
+ "    ON Evt.OwnerID = Fam.FamilyID "
+ "  WHERE URL.OwnerType = 4 " // Citation webtag "
+ "    AND Cit.OwnerType = 2 " // Event citation "
+ "    AND Evt.OwnerType = 1 " // Family "
+ " "
+ "UNION "
+ "" // Wife for citations of Family events "
+ "SELECT "
+ "  NULL AS LinkID, "
+ "  0 AS OwnerType,  "
+ "  Fam.MotherID AS OwnerID,  "
+ "  0 AS LinkType, "
+ "  URL.Name AS Name, "
+ "  URL.URL AS URL,  "
+ "  URL.Note AS Note  "
+ "  FROM URLTable URL "
+ "  INNER JOIN CitationTable AS Cit "
+ "    ON URL.OwnerID = Cit.CitationID  "
+ "  INNER JOIN EventTable Evt "
+ "    ON Cit.OwnerID = Evt.EventID "
+ "  INNER JOIN FamilyTable Fam "
+ "    ON Evt.OwnerID = Fam.FamilyID "
+ "  WHERE URL.OwnerType = 4 " // Citation webtag "
+ "    AND Cit.OwnerType = 2 " // Event citation "
+ "    AND Evt.OwnerType = 1 " // Family "
+ " "
+ "UNION "
+ "" // Person for citations of Alternate Names "
+ "SELECT "
+ "  NULL AS LinkID, "
+ "  0 AS OwnerType,  "
+ "  Nam.OwnerID AS OwnerID,  "
+ "  0 AS LinkType, "
+ "  URL.Name AS Name, "
+ "  URL.URL AS URL,  "
+ "  URL.Note AS Note  "
+ "  FROM URLTable URL "
+ "  INNER JOIN "
+ "    CitationTable AS Cit "
+ "    ON URL.OwnerID = Cit.CitationID "
+ "  INNER JOIN NameTable Nam "
+ "    ON Cit.OwnerID = Nam.NameID   "
+ "  WHERE URL.OwnerType = 4 " // Citation webtag "
+ "    AND Cit.OwnerType = 7 " // Alt Name citation "
+ "" // End of Citation WebTags  "
+ "; ";
                return commandText;
                }
        }

        public static string dBWebTagsDELETEduplicates 
        { 
            get
                {
                string commandText = ""
+ "" // QueryID 47"
+ "" // WebTags-DeleteDuplicates.sql "
+ "/* "
+ "2012-12-10 Tom Holden ve3meo "
+ " "
+ "Repeated use of WebTags-Consolidate.sql will result in duplicate WebTags "
+ "as may some other operations. This query eliminates the duplicates "
+ "by copying the unique WebTags to a temporary table, deleting all records  "
+ "from URLTable, and copying the records from the temporary table back "
+ "into URLTable. "
+ "*/ "
+ " "
+ "DROP TABLE IF EXISTS tmpURLTable "
+ "; "
+ " "
+ "CREATE TEMP TABLE IF NOT EXISTS tmpURLTable AS "
+ " "
+ "SELECT DISTINCT "
+ "  NULL AS LinkID, "
+ "  OwnerType, "
+ "  OwnerID, "
+ "  LinkType, "
+ "  Name, "
+ "  URL, "
+ "  Note "
+ "  FROM URLTable "
+ "; "
+ " "
+ "DELETE FROM URLTable "
+ "; "
+ " "
+ "INSERT INTO URLTable "
+ "SELECT * FROM tmpURLTable "
+ "; "
+ " "
+ "DROP TABLE IF EXISTS tmpURLTable "
+ "; "
+ " ";
                return commandText;
                }
        }

        

        // shell for SQLite query
        //        public static string dBReportsParagraphStrip 
        //        { 
        //            get
        //                {
        //                string commandText = ""
        // + "SQL parsed by lines and concatenated " 
        // + "last SQL line - don't forget to end with the C# semi-colon outside the quotes ;";
        //                return commandText;
        //                }
        //        }





    }
}
