MSCIS.org

Level 1 Views

These views reference tables only.

viewContactNames

Use this view to see Contact Names in pre-defined formats. You can, for example, manually set phpMyAdmin (via the "pma_relations" table) to show the DisplayName field, and link all foreign keys to this ContactID field.

CREATE VIEW viewContactNames AS
 SELECT
  ContactID,
  CONCAT(
   LastName,
   _utf8', ',
   IF(
    PrefName = 1,
    IF(
     MiddleName <> _utf8'',
     CONCAT(
      FirstName,
      _utf8' ',
      MiddleName
     ),
     FirstName
    ),
    MiddleName
   ),
   IF(
    Suffix <> _utf8'',
    CONCAT(
     _utf8' ',
     Suffix
    ),
    _utf8''
   )
  ) AS DisplayName,
  LastName AS SirName,
  IF(
   PrefName = 1,
   CONCAT_WS(
    _utf8' ',
    FirstName,
    MiddleName
   ),
   CONCAT(
    _utf8'(',
    FirstName,
    _utf8') ',
    MiddleName
   )
  ) AS GivenName,
  MiddleName,
  MaidenName,
  PreName,
  Suffix,
  PrefName
 FROM contacts;

viewFullAddress

Use this to view Address data in pre-defined formats.

CREATE VIEW viewFullAddress AS
 SELECT
  CONCAT(
   CONCAT_WS(
    _utf8' ',
    Home,
    StreetOrPObox,
    ApptOrSuite
   ),
   _utf8'\r\n',
   City,
   _utf8', ',
   State,
   _utf8'  ',
   Zip,
   if(
    Country <> 'USA',
    concat(
     _utf8'\r\n',
     Country
    ),
    _utf8''
   )
  ) AS Address,
  CONCAT_WS(
   _utf8' ',
   Home,
   StreetOrPObox,
   ApptOrSuite
  ) AS Street,
  a.*
 FROM addresses a

viewGroupDepths

Use this to view Addressbook categorization schema as a pseudo-outline.

CREATE VIEW viewGroupDepths AS
 SELECT
  node.GroupName,
  COUNT(parent.GroupName) -1 AS Depth
 FROM  groups AS node,
  groups AS parent
 WHERE node.lft BETWEEN parent.lft AND parent.rgt
 GROUP BY node.GroupID
 ORDER BY node.lft

viewGroupLeaves

Use this to quickly view leaves of the Addressbook's categorization schema tree

CREATE VIEW viewGroupLeaves AS
 SELECT GroupName
 FROM  groups
 WHERE rgt = lft + 1;

viewMarriages

Use this to view current marriages, with names pre-formatted

CREATE VIEW viewMarriages AS
 SELECT
  MarriageID,
  CONCAT(
   m.FirstName,
   _utf8' and ',
   f.FirstName,
   _utf8' ',
   m.LastName
  ) AS Family,
  MarriageDate
 FROM
  marriages
 JOIN contacts m
  ON m.ContactID = marriages.MaleID
 JOIN contacts f
  ON f.ContactID = marriages.FemaleID
 ORDER BY
  m.LastName,
  m.FirstName,
  f.FirstName;

viewOrgNames

Use this to view Organization names in a pre-defined format.

CREATE VIEW viewOrgNames AS
 SELECT
  CONCAT_WS(
   ' ',
   OrgPrefix,
   OrgName
  ) AS Company,
  o.*
 FROM
  organizations o;