PAHMA Collection Objects data mapping

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.ObjectNumber objectNumber,
 'no' 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,
 'yes' 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
 CAST(c.objectID AS VARCHAR) 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
 CAST(o.objectID AS VARCHAR) 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
 CAST(o.objectID AS VARCHAR) 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
  CAST(n.ID AS VARCHAR) objectID,
  n.altnum pahmaAltNum,
  n.description pahmaAltNumType,
  n.remarks pahmaAltNumNote
FROM
  AltNums n
WHERE
  n.tableID = 108
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) 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
 CAST(o.objectID AS VARCHAR) 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
 CAST(c.objectID AS VARCHAR) 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
 CAST(n.objectID AS VARCHAR) 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
 CAST(o.objectID AS VARCHAR) 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
 CAST(c.objectID 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
;

SELECT
 CAST(n.objectID AS VARCHAR) objectID,
 n.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
 CAST(u.objectID AS VARCHAR) objectID,
 c.CSoptionID pahmaEthnographicFileCode
FROM
 ObjUserFields u
 JOIN MTB_EthnographicUseCodes c ON u.FieldValue = c.FieldValue
WHERE
 u.FieldTypeID = 17
;

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

SELECT
 CAST(d.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,
 d.remarks fieldCollectionNote
FROM
 ObjDates d
WHERE
 lower(d.eventType) in ('date collected', 'secondary collection date', 'tertiary collection date')
UNION
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,
 null fieldCollectionNote
FROM
 Objects o
WHERE
 o.dated IS NOT NULL OR o.dateBegin != '0' OR o.dateEnd != '0'
;

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

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.bibliography referenceNote
FROM
 Objects o
WHERE
 o.bibliography is not null
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.PubReferences referenceNote
FROM
 Objects o
WHERE
 o.PubReferences is not null
;

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.edition annotationNote,
 'TMS Edition field' annotationType
FROM
 Objects o
WHERE
 o.edition IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.curatorialRemarks annotationNote,
 'TMS Curatorial remarks' annotationType
FROM
 Objects o
WHERE
 o.curatorialRemarks IS NOT NULL
UNION ALL
SELECT
 CAST(c.objectID 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
 '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
 CAST(u.objectID AS VARCHAR) objectID,
 u.fieldValue annotationNote,
 t.userFieldType annotationType
FROM
 ObjUserFields u
 JOIN UserFieldTypes t ON u.fieldTypeID = t.userFieldTypeID
WHERE
 u.FieldTypeID != 17
;

SELECT
 p.objectPackageID groupID,
 p.name title,
 p.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
 CAST(o.objectID AS VARCHAR) objectID,
 o.objectPackageID groupID
FROM
 objPkgList o
;