From 5adbbc5faca61ccd073ee3342e20818fc0cf1360 Mon Sep 17 00:00:00 2001 From: Michael Ritter Date: Fri, 13 Dec 2024 15:26:49 -0700 Subject: [PATCH] NAGPRA Reports QA (#442) * Notice of Inventory - Fix join for object counts * Notice of Inventory - Fix join for acquisition date * Notice of Inventory - Coalesce for better null handling * Notice of Inventory - Switch to JavaScript templating * Notice of Inventory - Adjust spacing and increase header height * Notice of Inventory - Add noData section when result set is empty * Notice of Repatriation - Increase header height --- .../notice_of_intent_to_repatriate.jrxml | 48 +- .../notice_of_inventory_completion.jrxml | 795 +++++++++--------- 2 files changed, 426 insertions(+), 417 deletions(-) diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/notice_of_intent_to_repatriate.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/notice_of_intent_to_repatriate.jrxml index 2e24dd553..65c777192 100644 --- a/services/report/3rdparty/jasper-cs-report/src/main/resources/notice_of_intent_to_repatriate.jrxml +++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/notice_of_intent_to_repatriate.jrxml @@ -331,151 +331,151 @@ WHERE summary_hierarchy.primarytype = 'SummaryDocumentation' $P!{whereclause}]]> - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + @@ -483,7 +483,7 @@ WHERE summary_hierarchy.primarytype = 'SummaryDocumentation' $P!{whereclause}]]> - + @@ -491,7 +491,7 @@ WHERE summary_hierarchy.primarytype = 'SummaryDocumentation' $P!{whereclause}]]> - + diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/notice_of_inventory_completion.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/notice_of_inventory_completion.jrxml index bd41864d0..db55543b8 100644 --- a/services/report/3rdparty/jasper-cs-report/src/main/resources/notice_of_inventory_completion.jrxml +++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/notice_of_inventory_completion.jrxml @@ -1,6 +1,6 @@ - + @@ -15,160 +15,159 @@ - + - + - + object count with type of minimum number of individuals) - LEFT JOIN LATERAL ( - SELECT ocg_hierarchy.parentid, - sum(ocg.objectcount) AS object_count_mni - FROM hierarchy ocg_hierarchy - INNER JOIN objectcountgroup ocg ON ocg.id = ocg_hierarchy.id AND ocg.objectcounttype LIKE '%minimum%' - WHERE ocg_hierarchy.name = 'collectionobjects_common:objectCountGroupList' - GROUP BY ocg_hierarchy.parentid - ) object_count_mni ON object_count_mni.parentid = relation.objectcsid - -- Object Count AFO (related object > object count with type of associated funerary objects) - LEFT JOIN LATERAL ( - SELECT ocg_hierarchy.parentid, - sum(ocg.objectcount) AS object_count_afo - FROM hierarchy ocg_hierarchy - INNER JOIN objectcountgroup ocg ON ocg.id = ocg_hierarchy.id AND ocg.objectcounttype ~ '\yassociated' - WHERE ocg_hierarchy.name = 'collectionobjects_common:objectCountGroupList' - GROUP BY ocg_hierarchy.parentid - ) object_count_afo ON object_count_afo.parentid = relation.objectcsid - LEFT JOIN ( - SELECT related_acquisitions.objectcsid, - array_agg(acquisition.acquisitionreferencenumber) AS acquisition_numbers, - array_agg(acquisition.acquisitionmethod) AS acquisition_methods - FROM acquisitions_common acquisition - INNER JOIN related_acquisitions ON related_acquisitions.acquisition_id = acquisition.id - GROUP BY related_acquisitions.objectcsid - ) acquisition_data ON acquisition_data.objectcsid = relation.objectcsid - LEFT JOIN ( - SELECT related_acquisitions.objectcsid, - array_agg(sources.item) AS acquisition_sources - FROM acquisitions_common_acquisitionsources sources - INNER JOIN related_acquisitions ON related_acquisitions.acquisition_id = sources.id - GROUP BY related_acquisitions.objectcsid - ) acquisition_sources ON acquisition_sources.objectcsid = relation.objectcsid - LEFT JOIN LATERAL ( - SELECT related_acquisitions.objectcsid, - array_agg(sdg.datedisplaydate) AS acquisition_dates - FROM hierarchy acquisition_date_hierarchy - INNER JOIN related_acquisitions ON related_acquisitions.acquisition_id = acquisition_date_hierarchy.parentid - INNER JOIN structureddategroup sdg ON sdg.id = acquisition_date_hierarchy.id - WHERE acquisition_date_hierarchy.name = 'acquisitions_common:acquisitionDateGroup' - GROUP BY related_acquisitions.objectcsid - ) acquisition_dates ON acquisition_dates.objectcsid = relation.objectcsid + INNER JOIN misc ON misc.id = hierarchy.id and misc.lifecyclestate != 'deleted' + INNER JOIN collectionspace_core core ON core.id = hierarchy.id AND core.tenantid = $P{tenantid} + INNER JOIN nagprainventories_common inventory ON inventory.id = hierarchy.id + INNER JOIN related_objects relation ON relation.subjectcsid = hierarchy.name + INNER JOIN collectionobjects_common obj ON obj.id = relation.object_id + LEFT JOIN nagprainventories_common_noticetypes nt ON nt.id = inventory.id AND nt.pos = 0 + LEFT JOIN collectionobjects_common_briefdescriptions bd ON bd.id = relation.object_id AND bd.pos = 0 + LEFT JOIN collectionobjects_nagpra_nagpranotes nagpra_note ON nagpra_note.id = relation.object_id AND nagpra_note.pos = 0 + LEFT JOIN ( + SELECT fcp.id, + array_agg(fcp.item) AS places + FROM collectionobjects_common_fieldcollectionplaces fcp + GROUP BY fcp.id + ) field_collection_places ON field_collection_places.id = relation.object_id + LEFT JOIN ( + SELECT fcs.id, + array_agg(fcs.item) AS sites + FROM collectionobjects_common_fieldcollectionsites fcs + GROUP BY fcs.id + ) field_collection_sites ON field_collection_sites.id = relation.object_id + LEFT JOIN ( + SELECT collectors.id, + array_agg(collectors.item) AS collectors + FROM collectionobjects_common_fieldcollectors collectors + GROUP BY collectors.id + ) field_collectors ON field_collectors.id = relation.object_id + LEFT JOIN ( + SELECT categories.id, + array_agg(categories.item) AS category_determinations + FROM collectionobjects_nagpra_nagpracategories categories + GROUP BY categories.id + ) nagpra_category_determinations ON nagpra_category_determinations.id = relation.object_id + LEFT JOIN ( + SELECT id, + array_agg(item) AS object_functions + FROM collectionobjects_anthro_ethnofilecodes + GROUP BY id + ) object_functions ON object_functions.id = relation.object_id + LEFT JOIN LATERAL ( + SELECT collection_date_hierarchy.parentid, + array_agg(sdg.datedisplaydate) AS dates + FROM hierarchy collection_date_hierarchy + INNER JOIN structureddategroup sdg ON sdg.id = collection_date_hierarchy.id + WHERE collection_date_hierarchy.name = 'collectionobjects_common:fieldCollectionDateGroup' + GROUP BY collection_date_hierarchy.parentid + ) field_collection_dates ON field_collection_dates.parentid = relation.object_id + LEFT JOIN LATERAL ( + SELECT ong_hierarchy.parentid, + array_agg(ong.objectname) AS object_names, + array_agg(ong.objectnamecontrolled) AS controlled_object_names + FROM hierarchy ong_hierarchy + INNER JOIN objectnamegroup ong ON ong.id = ong_hierarchy.id + WHERE ong_hierarchy.name = 'collectionobjects_common:objectNameList' + GROUP BY ong_hierarchy.parentid + ) object_names ON object_names.parentid = relation.object_id + LEFT JOIN LATERAL ( + SELECT category_hierarchy.parentid, + array_agg(category.categorycountunit) AS object_category_units, + array_agg(category.categorycount) AS object_category_counts + FROM hierarchy category_hierarchy + INNER JOIN objectcategorygroup category ON category.id = category_hierarchy.id + WHERE category_hierarchy.name = 'collectionobjects_objectcategory_extension:objectCategoryGroupList' + GROUP BY category_hierarchy.parentid + ) object_category ON object_category.parentid = relation.object_id + -- Object Count MNI (related object > object count with type of minimum number of individuals) + LEFT JOIN LATERAL ( + SELECT ocg_hierarchy.parentid, + sum(ocg.objectcount) AS object_count_mni + FROM hierarchy ocg_hierarchy + INNER JOIN objectcountgroup ocg ON ocg.id = ocg_hierarchy.id AND ocg.objectcounttype LIKE '%minimum%' + WHERE ocg_hierarchy.name = 'collectionobjects_common:objectCountGroupList' + GROUP BY ocg_hierarchy.parentid + ) object_count_mni ON object_count_mni.parentid = relation.object_id + -- Object Count AFO (related object > object count with type of associated funerary objects) + LEFT JOIN LATERAL ( + SELECT ocg_hierarchy.parentid, + sum(ocg.objectcount) AS object_count_afo + FROM hierarchy ocg_hierarchy + INNER JOIN objectcountgroup ocg ON ocg.id = ocg_hierarchy.id AND ocg.objectcounttype ~ '\yassociated' + WHERE ocg_hierarchy.name = 'collectionobjects_common:objectCountGroupList' + GROUP BY ocg_hierarchy.parentid + ) object_count_afo ON object_count_afo.parentid = relation.object_id + LEFT JOIN ( + SELECT related_acquisitions.objectcsid, + array_agg(acquisition.acquisitionreferencenumber) AS acquisition_numbers, + array_agg(acquisition.acquisitionmethod) AS acquisition_methods + FROM acquisitions_common acquisition + INNER JOIN related_acquisitions ON related_acquisitions.acquisition_id = acquisition.id + GROUP BY related_acquisitions.objectcsid + ) acquisition_data ON acquisition_data.objectcsid = relation.objectcsid + LEFT JOIN ( + SELECT related_acquisitions.objectcsid, + array_agg(sources.item) AS acquisition_sources + FROM acquisitions_common_acquisitionsources sources + INNER JOIN related_acquisitions ON related_acquisitions.acquisition_id = sources.id + GROUP BY related_acquisitions.objectcsid + ) acquisition_sources ON acquisition_sources.objectcsid = relation.objectcsid + LEFT JOIN LATERAL ( + SELECT related_acquisitions.objectcsid, + array_agg(sdg.datedisplaydate) AS acquisition_dates + FROM hierarchy acquisition_date_hierarchy + INNER JOIN related_acquisitions ON related_acquisitions.acquisition_id = acquisition_date_hierarchy.parentid + INNER JOIN structureddategroup sdg ON sdg.id = acquisition_date_hierarchy.id + WHERE acquisition_date_hierarchy.name = 'acquisitions_common:acquisitionDateGroupList' + GROUP BY related_acquisitions.objectcsid + ) acquisition_dates ON acquisition_dates.objectcsid = relation.objectcsid $P!{whereclause}]]> @@ -227,7 +226,7 @@ $P!{whereclause}]]> - + @@ -262,259 +261,269 @@ $P!{whereclause}]]> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + !!value).join('; ') + ]]> + + + + + + + + + + + + + + !!name) + .join('; ')]]> + + + + + + + + + + + + + !!num).join('; ')]]> + + + + + + !!category).join('; ')]]> + + + + + + + + + + + + + + + + + + + + + + + + !!site).join('; ')]]> + + + + + + !!place).join('; ')]]> + + + + + + !!collector).join('; ')]]> + + + + + + !!date).join('; ')]]> + + + + + + !!source).join('; ')]]> + + + + + + !!method).join('; ')]]> + + + + + + !!date).join('; ')]]> + + + + + + + + + + + + + + + + + + -- 2.47.3