...
| Code Block | ||||
|---|---|---|---|---|
| ||||
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 | ||||
|---|---|---|---|---|
| ||||
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 | ||||
|---|---|---|---|---|
| ||||
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
;
|
...