-- PlaceErrors3.sql
-- 2012-03-07 ve3meo
-- Combines the cores of three queries PlacesLost, PlaceDetailsLost and OrphanedPlaceDetails
-- into one with error described and makes common the Person, Year, Fact and Place Detail name lookup.
 
SELECT 
 Error,
 CASE "Event.OwnerType"
 WHEN 0
 THEN
  NameTable.Surname || ', ' ||
  NameTable.Given || ' - ' ||
  NameTable.OwnerID 
 WHEN 1
 THEN
  Husband.Surname || ', ' ||
  Husband.Given || ' - ' ||
  Husband.OwnerID
 END
 AS Person,
 SUBSTR(EventDate,4,4) AS Year,
 FactTypeTable.Name AS Fact,
 SiteTable.Name AS "Place Detail",
 "Event.PlaceID",
 "Event.SiteID",
 SiteTable.PlaceID AS "PlaceID(PlaceTbl)",
 SiteTable.MasterID AS "MasterID(PlaceTbl)"

FROM
(
-- 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,
 EventTable.OwnerID AS "Event.OwnerID", 
 EventTable.OwnerType AS "Event.OwnerType",
 EventTable.Date AS EventDate,
 EventTable.EventType AS EventType,
 EventTable.PlaceID AS "Event.PlaceID",
 EventTable.SiteID AS "Event.SiteID", 
 Null AS "Place.PlaceID",
 Null AS "Place.MasterID"
FROM EventTable
WHERE EventTable.PlaceID <>0
AND EventTable.PlaceID
NOT IN
(
  SELECT PlaceID FROM PlaceTable WHERE PlaceType = 0
 )
 
UNION

-- Lost Place Details are Place Details 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 Detail Lost' AS Error, 
 EventTable.OwnerID AS "Event.OwnerID", 
 EventTable.OwnerType AS "Event.OwnerType",
 EventTable.Date,
 EventTable.EventType AS EventType,
 EventTable.PlaceID AS "Event.PlaceID",
 EventTable.SiteID AS "Event.SiteID", 
 PlaceTable.PlaceID AS "Place.PlaceID",
 PlaceTable.MasterID AS "Place.MasterID"

FROM EventTable
LEFT JOIN PlaceTable
ON EventTable.PlaceID = PlaceTable.PlaceID
LEFT JOIN PlaceTable AS SiteTable
ON EventTable.SiteID = SiteTable.PlaceID
WHERE EventTable.SiteID <>0 -- No Place Detail was assigned
AND EventTable.SiteID
NOT IN
(
 SELECT PlaceID FROM PlaceTable WHERE PlaceType = 2
 ) -- Existing Place Details' PlaceID's
 
UNION

-- 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.
SELECT 
 CASE 
 WHEN EventTable.SiteID ISNULL 
 THEN 'Unused Place Detail w/o Place'
 ELSE 'Place Detail w/o Place' 
 END AS Error,
 EventTable.OwnerID AS "Event.OwnerID", 
 EventTable.OwnerType AS "Event.OwnerType",
 EventTable.Date,
 EventTable.EventType AS EventType,
 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 
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
     )
   )
)
-- end of UNIONs
)
LEFT JOIN NameTable
ON "Event.OwnerID" = NameTable.OwnerID AND +NameTable.IsPrimary AND "Event.OwnerType"=0
LEFT JOIN FamilyTable
ON "Event.OwnerID" = FamilyTable.FamilyID AND "Event.OwnerType"=1
LEFT JOIN NameTable AS Husband
ON FamilyTable.FatherID = Husband.OwnerID AND +Husband.IsPrimary
LEFT JOIN FactTypeTable 
ON "EventType" = FactTypeTable.FactTypeID
LEFT JOIN PlaceTable
ON "Event.PlaceID" = PlaceTable.PlaceID
LEFT JOIN PlaceTable AS SiteTable
ON "Event.SiteID" = SiteTable.PlaceID AND SiteTable.PlaceType = 2
ORDER BY Person, Fact 
;