-- 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 --------
;