From f48a72bfc3eb8a2fff13cb602e53e252bea6777d Mon Sep 17 00:00:00 2001
From: Michael Ritter <mikejritter@users.noreply.github.com>
Date: Fri, 12 Jan 2024 15:04:44 -0700
Subject: [PATCH] DRYD-1370: Join on most recent media (#385)

---
 .../jasper-cs-report/src/main/resources/accessions.jrxml   | 5 ++++-
 .../jasper-cs-report/src/main/resources/condition.jrxml    | 4 +++-
 .../jasper-cs-report/src/main/resources/deaccessions.jrxml | 5 ++++-
 .../src/main/resources/obj_computed_location.jrxml         | 7 ++++++-
 4 files changed, 17 insertions(+), 4 deletions(-)

diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/accessions.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/accessions.jrxml
index 58950125c..3a15b150a 100644
--- a/services/report/3rdparty/jasper-cs-report/src/main/resources/accessions.jrxml
+++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/accessions.jrxml
@@ -83,12 +83,15 @@
   LEFT JOIN hierarchy opdg_hier ON opdg_hier.parentid = object.id AND opdg_hier.name = 'collectionobjects_common:objectProductionDateGroupList' AND opdg_hier.pos = 0
   LEFT JOIN structureddategroup sdg ON sdg.id = opdg_hier.id
 ), related_media AS (
-  SELECT
+  SELECT DISTINCT ON (object.csid)
     object.csid AS objcsid,
     media.objectcsid AS mediacsid
   FROM related_objects object
   INNER JOIN relations_common media ON media.subjectcsid = object.csid AND media.objectdocumenttype = 'Media'
   INNER JOIN misc ON misc.id = media.id AND misc.lifecyclestate != 'deleted'
+  INNER JOIN hierarchy hier ON hier.name = media.objectcsid
+  INNER JOIN collectionspace_core core ON core.id = hier.id
+  ORDER BY object.csid, core.updatedat DESC
 )
 SELECT
   acq.acquisitionreferencenumber,
diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/condition.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/condition.jrxml
index 60792aaca..75d7d34ff 100644
--- a/services/report/3rdparty/jasper-cs-report/src/main/resources/condition.jrxml
+++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/condition.jrxml
@@ -24,7 +24,7 @@
 	</parameter>
 	<queryString language="SQL">
 		<![CDATA[WITH media AS (
-  SELECT
+  SELECT DISTINCT ON (relation.subjectcsid)
     relation.subjectcsid,
     relation.objectcsid AS mediacsid
   FROM media_common media
@@ -32,6 +32,8 @@
   INNER JOIN misc ON misc.id = media.id AND misc.lifecyclestate != 'deleted'
   INNER JOIN relations_common relation ON relation.objectcsid = hier.name
     AND (relation.subjectdocumenttype = 'Conditioncheck' OR relation.subjectdocumenttype = 'CollectionObject')
+  INNER JOIN collectionspace_core core ON core.id = media.id
+  ORDER BY relation.subjectcsid, core.updatedat DESC
 )
 SELECT
   obj.objectnumber,
diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/deaccessions.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/deaccessions.jrxml
index b79d0c980..0f3528e0d 100644
--- a/services/report/3rdparty/jasper-cs-report/src/main/resources/deaccessions.jrxml
+++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/deaccessions.jrxml
@@ -106,12 +106,15 @@
   LEFT JOIN hierarchy opdg_hier ON opdg_hier.parentid = object.id AND opdg_hier.name = 'collectionobjects_common:objectProductionDateGroupList' AND opdg_hier.pos = 0
   LEFT JOIN structureddategroup sdg ON sdg.id = opdg_hier.id
 ), related_object_media AS (
-  SELECT
+  SELECT DISTINCT ON (object.csid)
     object.csid AS objcsid,
     media.objectcsid as mediacsid
   FROM related_objects object
   INNER JOIN relations_common media ON media.subjectcsid = object.csid AND media.objectdocumenttype = 'Media'
   INNER JOIN misc ON misc.id = media.id AND misc.lifecyclestate != 'deleted'
+  INNER JOIN hierarchy hier ON hier.name = media.objectcsid
+  INNER JOIN collectionspace_core core ON core.id = hier.id
+  ORDER BY object.csid, core.updatedat DESC
 ), related_acquisitions AS (
   SELECT
     acq.acquisitionreferencenumber AS acquisition,
diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/obj_computed_location.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/obj_computed_location.jrxml
index d3ed625f0..9c31ce07d 100644
--- a/services/report/3rdparty/jasper-cs-report/src/main/resources/obj_computed_location.jrxml
+++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/obj_computed_location.jrxml
@@ -57,10 +57,15 @@ LEFT JOIN hierarchy place_hier ON place_hier.parentid = place.id AND place_hier.
 LEFT JOIN placetermgroup place_term ON place_term.id = place_hier.id
 -- related media
 LEFT JOIN (
-  SELECT relation.*
+  SELECT DISTINCT ON (relation.subjectcsid)
+    relation.objectcsid,
+    relation.subjectcsid
   FROM relations_common relation
   INNER JOIN misc ON misc.id = relation.id AND misc.lifecyclestate != 'deleted'
+  INNER JOIN hierarchy hier ON hier.name = relation.objectcsid
+  INNER JOIN collectionspace_core core ON core.id = hier.id
   WHERE relation.objectdocumenttype = 'Media' AND relation.subjectdocumenttype = 'CollectionObject'
+  ORDER BY relation.subjectcsid, core.updatedat DESC
 ) related_media ON related_media.subjectcsid = hier.name
 $P!{whereclause}]]>
 	</queryString>
-- 
2.47.3