-- AddressHowUsed-RM7.sql
/*
-- ve3meo
-- 17 Aug 2012
rev 3 Jul 2021 - corrected the UsedBy result for Contacts to provide 
    blank instead of error; likewise for Primary as Contacts have no Primary.
    Confirmed incompatible with RM8.
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 
    WHEN AddressLinkTable.AddressNum = 1
     THEN 'Y'
    WHEN AddressLinkTable.AddressNum = 2
     THEN 'N'
    WHEN AddressLinkTable.AddressNum IS NULL
     THEN ''
    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 
    WHEN AddressLinkTable.OwnerType IS NULL
     THEN ''
    WHEN AddressLinkTable.OwnerType=0
     THEN NameTable.Surname || ', ' || NameTable.Given || '-' || NameTable.OwnerID
    WHEN AddressLinkTable.OwnerType=1
     THEN Husband.Surname || ', ' || Husband.Given || '-' || Husband.OwnerID || ' & '
             || Wife.Surname || ', ' || Wife.Given || '-' || Wife.OwnerID
    WHEN AddressLinkTable.OwnerType=3
     THEN SourceTable.Name 
          || ' (' 
          || (SELECT COUNT() FROM CitationTable C2 WHERE C2.SourceID = SourceTable.SourceID)
          || ' citations)'  
    WHEN AddressLinkTable.OwnerType=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))
*/