PAHMA Collection Objects data mapping

SELECT
 o.objectID objectID,
 o.ObjectNumber objectNumber,
 NULL isComponent,
 CatRais pahmaCollection,
 d.Department pahmaTmsLegacyDepartment,
 HistAttributions objectHistoryNote,
 (CASE WHEN lower(LoanClass) not in ('', 'catalog card', 'inventory', 'ledger') THEN LoanClass END) pahmaTms2003DataSource,
 Markings distinguishingFeatures,
 Medium material,
 MTB_PlaceTermID fieldCollectionPlace,
 Notes comments,
 s.ObjectStatus pahmaObjectStatus,
 t.ObjectType collection,
 i.CSpaceID nagpraInventoryName,
 MTB_Portfolio portfolioSeries,
 Provenance fieldLocVerbatim,
 (CASE WHEN MTB_ResponsibleDept = 1 THEN 'Natasha Johnson'
       WHEN MTB_ResponsibleDept = 2 THEN 'Leslie Freund'
       WHEN MTB_ResponsibleDept = 3 THEN 'Victoria Bradshaw'
       WHEN MTB_ResponsibleDept = 4 THEN 'Alicja Egbert'
       WHEN MTB_ResponsibleDept = 5 THEN 'No collection manager (Registration)'
       WHEN MTB_ResponsibleDept = 8 THEN 'uncertain' END) responsibleDepartment,
 Signed inscriptionContent,
 b.optionID pahmaNagpraCodeLegacy,
 Title title,
 c.integer3 inventoryCount
FROM
 Objects o
 LEFT OUTER JOIN Departments d ON o.departmentID = d.departmentID
 LEFT OUTER JOIN ObjectStatuses s ON o.objectStatusID = s.objectStatusID
 LEFT OUTER JOIN ObjectTypes t ON o.objectTypeID = t.objectTypeID
 LEFT OUTER JOIN MTB_NagpraInvNames i ON o.paperFileRef = i.LongTMSname
 LEFT OUTER JOIN MTB_NagpraBurialCodes b ON o.state = b.code
 LEFT OUTER JOIN ObjContext c ON o.objectID = c.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.componentNumber objectNumber,
 'Component' isComponent,
 NULL pahmaCollection,
 NULL pahmaTmsLegacyDepartment,
 NULL objectHistoryNote,
 NULL pahmaTms2003DataSource,
 NULL distinguishingFeatures,
 NULL material,
 NULL fieldCollectionPlace,
 NULL comments,
 NULL pahmaObjectStatus,
 NULL collection,
 NULL nagpraInventoryName,
 NULL portfolioSeries,
 NULL fieldLocVerbatim,
 NULL responsibleDepartment,
 NULL inscriptionContent,
 NULL pahmaNagpraCodeLegacy,
 NULL title,
 NULL inventoryCount
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
;

SELECT
 c.objectID parentObjectID,
  'c' + CAST(c.componentID AS VARCHAR) childObjectID
FROM
  ObjComponents c
WHERE
  c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
;

SELECT
 o.objectID,
 o.objectCount numberOfObjects
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.objectID NOT IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
UNION ALL
SELECT
 o.objectID,
 (CASE WHEN c.compCount > o.objectCount THEN c.compCount ELSE o.objectCount END) numberOfObjects
FROM
 Objects o
 JOIN  ObjComponents c ON o.objectID = c.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.compCount numberOfObjects
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
;

SELECT
  n.ID objectID,
  n.altnum pahmaAltNum,
  n.description pahmaAltNumType,
  n.remarks pahmaAltNumNote
FROM
  AltNums n
WHERE
  n.tableID = 108
UNION ALL
SELECT
 c.objectID,
 c.componentNumber pahmaAltNum,
 NULL pahmaAltNumType,
 NULL pahmaAltNumNote
FROM
 ObjComponents c
 JOIN Objects o ON c.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.componentNumber IS NOT NULL
 AND c.componentNumber != o.objectNumber
;

SELECT
 o.objectID,
 o.objectName,
 NULL objectNameLanguage,
 NULL objectNameNote
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.componentName objectName,
 NULL objectNameLanguage,
 NULL objectNameNote
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
 AND c.componentName IS NOT NULL
UNION ALL
SELECT
 c.objectID,
 c.componentName objectName,
 NULL objectNameLanguage,
 'Component name' objectNameNote
FROM
 ObjComponents c
 JOIN Objects o ON c.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.componentName IS NOT NULL
 AND coalesce(o.objectName,'') != c.componentName
UNION ALL
SELECT
 n.objectID,
 n.objectName,
 (CASE WHEN n.languageID != '0' THEN l.MTB_ISOcode END) objectNameLanguage,
 (CASE WHEN n.objectNameTypeID != '0' THEN t.objectNameType ELSE '' END) + (CASE WHEN n.objectNameTypeID != '0' AND n.remarks is not null THEN '; ' ELSE '' END) + coalesce(n.remarks, '') objectNameNote
FROM
 objectNames n
 LEFT OUTER JOIN objectNameTypes t ON n.objectNameTypeID = t.objectNameTypeID
 LEFT OUTER JOIN languages l ON n.languageID = l.languageID
WHERE
 n.objectName IS NOT NULL
;

SELECT
 o.objectID,
 o.description briefDescription
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.physDesc briefDescription
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
 AND c.physDesc IS NOT NULL
UNION ALL
SELECT
 c.objectID,
 c.physDesc briefDescription
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.physDesc IS NOT NULL
;

SELECT
 objectID,
 title,
 (CASE WHEN n.languageID != '0' THEN l.MTB_ISOcode END) titleLanguage,
 (CASE WHEN n.titleTypeID != '0' THEN t.titleType END) titleType
FROM
 objTitles n
 LEFT OUTER JOIN titleTypes t ON n.titleTypeID = t.titleTypeID
 LEFT OUTER JOIN languages l ON n.languageID = l.languageID
;

SELECT
 u.objectID,
 c.CSoptionID pahmaEthnographicFileCode
FROM
 ObjUserFields u
 JOIN MTB_EthnographicUseCodes c ON u.FieldValue = c.FieldValue
WHERE
 u.FieldTypeID = 17
;

SELECT
  objectID,
  lower(eventType) assocDateType,
  dateText assocDate_dateDisplayDate,
  (CASE WHEN dateBegSearch != '0' THEN dateBegSearch END) assocDate_dateEarliestSingleYear,
  (CASE WHEN dateEndSearch != '0' THEN dateEndSearch END) assocDate_dateLatestYear,
  dayBegSearch assocDate_dateEarliestSingleDay,
  dayEndSearch assocDate_dateLatestDay,
  monthBegSearch assocDate_dateEarliestSingleMonth,
  monthEndSearch assocDate_dateLatestMonth,
  remarks assocDateNote
FROM
  ObjDates
WHERE
  lower(eventType) not in ('', 'accessioned', 'date collected', 'secondary collection date', 'tertiary collection date', 'date made')
  AND (dateText is not null OR dateBegSearch != 0)
;

SELECT
  objectID,
  dateText fieldCollectionDate_dateDisplayDate,
  (CASE WHEN dateBegSearch != '0' THEN dateBegSearch END) fieldCollectionDate_dateEarliestSingleYear,
  (CASE WHEN dateEndSearch != '0' THEN dateEndSearch END) fieldCollectionDate_dateLatestYear,
  dayBegSearch fieldCollectionDate_dateEarliestSingleDay,
  dayEndSearch fieldCollectionDate_dateLatestDay,
  monthBegSearch fieldCollectionDate_dateEarliestSingleMonth,
  monthEndSearch fieldCollectionDate_dateLatestMonth,
  remarks fieldCollectionNote
FROM
  ObjDates
WHERE
  lower(eventType) in ('date collected', 'secondary collection date', 'tertiary collection date')
UNION
SELECT
  objectID,
  dated fieldCollectionDate_dateDisplayDate,
  (CASE WHEN DateBegin != '0' THEN DateBegin END) fieldCollectionDate_dateEarliestSingleYear,
  (CASE WHEN DateEnd != '0' THEN DateEnd END) fieldCollectionDate_dateLatestYear,
  null fieldCollectionDate_dateEarliestSingleDay,
  null fieldCollectionDate_dateLatestDay,
  null fieldCollectionDate_dateEarliestSingleMonth,
  null fieldCollectionDate_dateLatestMonth,
  null fieldCollectionNote
FROM
  Objects
WHERE
  dated is not null OR dateBegin != '0' OR dateEnd != '0'
;

SELECT
  objectID,
  dateText objectProductionDate_dateDisplayDate,
  (CASE WHEN dateBegSearch != '0' THEN dateBegSearch END) objectProductionDate_dateEarliestSingleYear,
  (CASE WHEN dateEndSearch != '0' THEN dateEndSearch END) objectProductionDate_dateLatestYear,
  dayBegSearch objectProductionDate_dateEarliestSingleDay,
  dayEndSearch objectProductionDate_dateLatestDay,
  monthBegSearch objectProductionDate_dateEarliestSingleMonth,
  monthEndSearch objectProductionDate_dateLatestMonth,
  remarks objectProductionNote
FROM
  ObjDates
WHERE
  lower(eventType) = 'date made'
;

SELECT
  objectID,
  bibliography referenceNote
FROM
  Objects
WHERE
  bibliography is not null
UNION ALL
SELECT
  objectID,
  PubReferences referenceNote
FROM
  Objects
WHERE
  PubReferences is not null
;

SELECT
 objectid,
 edition annotationNote,
 'TMS Edition field' annotationType
FROM
 Objects
WHERE
 edition IS NOT NULL
UNION ALL
SELECT
 objectid,
 curatorialRemarks annotationNote,
 'TMS Curatorial remarks' annotationType
FROM
 Objects
WHERE
 curatorialRemarks IS NOT NULL
UNION ALL
SELECT
 c.objectID objectID,
 c.prepComments annotationNote,
 'Component Prep comments' annotationType
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.prepComments IS NOT NULL
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.prepComments annotationNote,
 'Component Prep comments' annotationType
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
 AND c.prepComments IS NOT NULL
UNION ALL
SELECT
 u.objectID,
 u.fieldValue annotationNote,
 t.userFieldType annotationType
FROM
 ObjUserFields u
 JOIN UserFieldTypes t ON u.fieldTypeID = t.userFieldTypeID
WHERE
 u.FieldTypeID != 17
;

SELECT
 objectPackageID groupID,
 name title,
 notes scopeNote,
 (CASE WHEN u.MTB_ConstituentID = -1 THEN NULL ELSE u.MTB_ConstituentID END) owner
FROM
 ObjectPackages p
 LEFT OUTER JOIN Users u ON p.owner = u.login
;

SELECT
 objectID,
 objectPackageID groupID
FROM
 objPkgList
;