-- CopyFact2Group.sql
-- Tom Holden 5 Apr 2011
-- ALWAYS BACK UP YOUR DATABASE BEFORE RUNNING A QUERY THAT MODIFIES IT
-- Copies a fact from one person to all persons in a named group
--  - Media is NOT copied but Sources and Note are. 
--  - Edit date is not modified from that of the original fact.
-- Requires GroupID and EventID values to be found and entered, each into
--  two queries. The GroupID is the LabelID of the LabelName corresponding
--  to the name of the Group in the LabelTable table. 
--  The EventID is easily found if it is the last fact entered - the EventID
--  of the last row in the table EventTable.
--  
-- The first query adds a record to the EventTable for each person with
--  the same fact values as the record copied.
-- The second query adds an identical record to the CitationTable for each source for the 
--  copied fact for each added fact. If n sources for the original fact, then the same n
--  sources for the fact are added to each person in the group.
--
-- This query copies the fact/event but not the Sources to the persons in the Named Group
-- CHANGE values of GroupID and EventID to your values
INSERT OR ROLLBACK INTO EventTable (EventType, OwnerType, OwnerID, FamilyID, PlaceID, SiteID, Date, SortDate, 
       IsPrimary, IsPrivate, Proof, Status, EditDate, Sentence, Details, Note)
  SELECT EventType, OwnerType, N.OwnerID, FamilyID, PlaceID, SiteID, Date, SortDate, 
       IsPrimary, IsPrivate, Proof, Status, EditDate, Sentence, Details, Note
   FROM (SELECT N.OwnerID FROM NameTable N, GroupTable WHERE N.OwnerID >=StartID AND N.OwnerID <= EndID AND GroupID=???) N,
       EventTable E WHERE EventID=???;

-- This query copies the Sources for the newly created facts. 
-- CHANGE value of C.OwnerID to your EventID value and GroupID to your GroupID       
INSERT OR ROLLBACK INTO CitationTable (OwnerType, SourceID, OwnerID, Quality, IsPrivate, Comments, ActualText, RefNumber, Flags, Fields)
  SELECT C.OwnerType, C.SourceID, E.EventID, C.Quality, C.IsPrivate, C.Comments, C.ActualText, C.RefNumber, C.Flags, C.Fields
    FROM CitationTable C,
     (SELECT EventID FROM EventTable 
        WHERE EventID >
         (SELECT MAX(EventID) FROM EventTable) - (SELECT SUM(EndID-StartID+1) FROM GroupTable
           WHERE GroupID=???)) E
    WHERE C.OwnerID=??? AND C.OwnerType=2;
