Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
sql
sql
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 d.dateText fieldCollectionDate_dateDisplayDate,
 (CASE WHEN d.dateBegSearch != '0' THEN d.dateBegSearch END) fieldCollectionDate_dateEarliestSingleYear,
 (CASE WHEN d.dateEndSearch != '0' THEN d.dateEndSearch END) fieldCollectionDate_dateLatestYear,
 d.dayBegSearch fieldCollectionDate_dateEarliestSingleDay,
 d.dayEndSearch fieldCollectionDate_dateLatestDay,
 d.monthBegSearch fieldCollectionDate_dateEarliestSingleMonth,
 d.monthEndSearch fieldCollectionDate_dateLatestMonth
FROM
 ObjDates d
 JOIN Objects o on d.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(d.eventType) in ('date collected', 'secondary collection date', 'tertiary collection date')
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.dated fieldCollectionDate_dateDisplayDate,
 (CASE WHEN o.dateBegin != '0' THEN o.dateBegin END) fieldCollectionDate_dateEarliestSingleYear,
 (CASE WHEN o.dateEnd != '0' THEN o.dateEnd END) fieldCollectionDate_dateLatestYear,
 null fieldCollectionDate_dateEarliestSingleDay,
 null fieldCollectionDate_dateLatestDay,
 null fieldCollectionDate_dateEarliestSingleMonth,
 null fieldCollectionDate_dateLatestMonth
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.dated IS NOT NULL OR o.dateBegin != '0' OR o.dateEnd != '0'
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 (CASE WHEN LEN(x.displayDate) > 0 THEN x.displayDate END) fieldCollectionDate_dateDisplayDate,
 (CASE WHEN x.dateBegin != '0' THEN x.dateBegin END) fieldCollectionDate_dateEarliestSingleYear,
 (CASE WHEN x.dateEnd != '0' THEN x.dateEnd END) fieldCollectionDate_dateLatestYear,
 NULL fieldCollectionDate_dateEarliestSingleDay,
 NULL fieldCollectionDate_dateLatestDay,
 NULL fieldCollectionDate_dateEarliestSingleMonth,
 NULL fieldCollectionDate_dateLatestMonth
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects o ON x.ID = o.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
  AND lower(r.ROLE) IN ('collector', 'secondary collector', 'tertiary collector')
 AND (x.dateBegin != 0 OR (x.displayDate IS NOT NULL AND len(x.displayDate) > 0))
;

...

TMS table name

TMS field name

CSpace field name

notes

ConXrefs

constituentID

fieldCollector

reference to Person or Organization

sql query

view:  cs_fieldcollectors

Code Block
sql
sql
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.constituentID fieldCollector
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects o ON x.ID = o.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND  o.IsVirtual = 0 AND o.IsTemplate = 0
  AND lower(r.ROLE) IN ('collector', 'secondary collector', 'tertiary collector')
;

...

Code Block
sql
sql
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 'Field collection date: ' + d.remarks fieldCollectionNote
FROM
 ObjDates d
 JOIN Objects o on d.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(d.eventType) IN ('date collected', 'secondary collection date', 'tertiary collection date')
 AND d.remarks IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 'Field collector: ' + x.remarks fieldCollectionNote
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects o ON x.ID = o.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0 
 AND lower(r.ROLE) IN ('collector', 'secondary collector', 'tertiary collector')
 AND x.remarks IS NOT NULL
ORDER BY objectID
;

...