From 0b8955e11c3583b9afd02273e35e891224c7c367 Mon Sep 17 00:00:00 2001 From: Michael Ritter Date: Thu, 27 Jul 2023 12:51:36 -0600 Subject: [PATCH] Publicart Report Updates (#360) --- .../resources/tombstone_with_budget.jrxml | 6 +++++- .../resources/tombstone_with_creator.jrxml | 19 +++++++++++++++---- 2 files changed, 20 insertions(+), 5 deletions(-) diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_budget.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_budget.jrxml index 5f770a6da..405eb4b95 100644 --- a/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_budget.jrxml +++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_budget.jrxml @@ -151,10 +151,14 @@ LEFT JOIN materials material on material.objcsid = obj.objcsid LEFT JOIN dimensions dimension on dimension.objcsid = obj.objcsid LEFT JOIN dates date on date.objcsid = obj.objcsid LEFT JOIN ( - SELECT relation.* + SELECT relation.subjectcsid, relation.objectcsid 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 core.updatedat DESC + LIMIT 1 ) media ON media.subjectcsid = obj.objcsid LEFT JOIN ( SELECT diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_creator.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_creator.jrxml index e9e1077af..909c8f9cd 100644 --- a/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_creator.jrxml +++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_creator.jrxml @@ -36,7 +36,7 @@ dimension.dimension, dimension.dimensionvalue, dimension.dimensionunit, - sd.datedisplaydate AS artworkdate, + sd.artworkdate, media.objectcsid AS mediacsid FROM collectionobjects_common obj INNER JOIN hierarchy hier on hier.id = obj.id @@ -48,9 +48,16 @@ LEFT JOIN collectionobjects_publicart_publicartcollections coll ON coll.id = obj LEFT JOIN collectionobjects_common_briefdescriptions bd ON bd.id = obj.id AND bd.pos = 0 LEFT JOIN collectionobjects_common_owners owner ON owner.id = obj.id AND owner.pos = 0 LEFT JOIN collectionobjects_common_inventorystatuslist inventory ON inventory.id = obj.id AND inventory.pos = 0 -LEFT JOIN hierarchy pdg_hier on pdg_hier.parentid = obj.id AND pdg_hier.primarytype = 'publicartProductionDateGroup' and pdg_hier.pos = 0 -LEFT JOIN hierarchy sdg_hier on sdg_hier.parentid = pdg_hier.id AND sdg_hier.primarytype = 'structuredDateGroup' -LEFT JOIN structureddategroup sd on sd.id = sdg_hier.id +LEFT JOIN ( + SELECT + hier.parentid, + string_agg(sd.datedisplaydate, '|') as artworkdate + FROM hierarchy hier + LEFT JOIN hierarchy sdg_hier on sdg_hier.parentid = hier.id AND sdg_hier.primarytype = 'structuredDateGroup' + LEFT JOIN structureddategroup sd on sd.id = sdg_hier.id + WHERE hier.primarytype = 'publicartProductionDateGroup' + GROUP BY hier.parentid +) sd ON sd.parentid = obj.id LEFT JOIN ( SELECT measured_hier.parentid, @@ -97,7 +104,11 @@ LEFT JOIN ( SELECT relation.subjectcsid, relation.objectcsid 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 core.updatedat DESC + LIMIT 1 ) media ON media.subjectcsid = hier.name $P!{whereclause}]]> -- 2.47.3