From 4c16a0269763434dff2cd4347b49e9170c3fc8e9 Mon Sep 17 00:00:00 2001 From: Michael Ritter Date: Fri, 19 Jul 2024 12:38:21 -0600 Subject: [PATCH] DRYD-1438: Workflow Report Updates (#417) --- .../src/main/resources/deed_of_gift.jrxml | 35 ++++++++++ .../src/main/resources/incoming_loan.jrxml | 64 ++++++++++++++++++- .../resources/obj_computed_location.jrxml | 13 ++++ .../src/main/resources/outgoing_loan.jrxml | 64 ++++++++++++++++++- 4 files changed, 172 insertions(+), 4 deletions(-) diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/deed_of_gift.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/deed_of_gift.jrxml index 1f14d974c..f66853b6f 100644 --- a/services/report/3rdparty/jasper-cs-report/src/main/resources/deed_of_gift.jrxml +++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/deed_of_gift.jrxml @@ -4,6 +4,19 @@ xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="deedofgift" pageWidth="1500" pageHeight="800" orientation="Landscape" columnWidth="1460" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isIgnorePagination="true" uuid="202f91ff-d0dd-4eda-90de-a15938303d79"> + + + + + + + + + + + + + @@ -42,6 +55,7 @@ ), person_owners AS ( SELECT ag.*, string_agg(primaryterm.termdisplayname, '|' ORDER BY term_hier.pos) as primarydisplayname, + string_agg(regexp_replace(primaryterm.termflag, '^.*\)''(.*)''$', '\1'), '|' ORDER BY term_hier.pos) as primarytermflag, intake.csid as intakecsid FROM intakes intake INNER JOIN intakes_common_currentowners co ON co.id = intake.id AND co.pos = 0 @@ -79,6 +93,7 @@ INNER JOIN misc on misc.id = media.id AND misc.lifecyclestate != 'deleted' ) select owner.primarydisplayname as primarydisplayname, + owner.primarytermflag as primarytermflag, owner.addressplace1 as addressplace1, owner.addressplace2 as addressplace2, owner.addresstype as addresstype, @@ -101,6 +116,11 @@ left join related_object_media media on media.objcsid = obj.csid]]> + + + + + @@ -186,6 +206,14 @@ left join related_object_media media on media.objcsid = obj.csid]]> + + + + + + + @@ -294,6 +322,13 @@ left join related_object_media media on media.objcsid = obj.csid]]> + + + + + + diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/incoming_loan.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/incoming_loan.jrxml index e74f913c2..fce432ecb 100644 --- a/services/report/3rdparty/jasper-cs-report/src/main/resources/incoming_loan.jrxml +++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/incoming_loan.jrxml @@ -1,6 +1,6 @@ - + @@ -46,8 +46,11 @@ SELECT address.addressstateorprovince, loanin.loanindate, loanin.loanrenewalapplicationdate, + loanin.loanpurpose, + loanin.loaninconditions, objects.objectnumber, objects.objectname, + objects.briefdescription, loanvaluation.valueamount AS loanvalueamount, loanvaluation.valuecurrency AS loanvaluecurrency, objvaluation.valueamount AS objvalueamount, @@ -73,15 +76,18 @@ LEFT JOIN ( hier.name AS csid, relation.subjectcsid, obj.objectnumber, - ong.objectname + ong.objectname, + string_agg(bd.item, '; ') AS briefdescription FROM collectionobjects_common obj INNER JOIN hierarchy hier ON hier.id = obj.id INNER JOIN misc on misc.id = obj.id AND misc.lifecyclestate != 'deleted' INNER JOIN relations_common relation ON relation.objectcsid = hier.name AND relation.subjectdocumenttype = 'Loanin' AND relation.objectdocumenttype = 'CollectionObject' + LEFT JOIN collectionobjects_common_briefdescriptions bd ON bd.id = obj.id LEFT JOIN hierarchy ong_hier ON ong_hier.parentid = obj.id AND ong_hier.primarytype = 'objectNameGroup' AND ong_hier.pos = 0 LEFT JOIN objectnamegroup ong ON ong.id = ong_hier.id + GROUP BY relation.subjectcsid, obj.objectnumber, ong.objectname, hier.name ) objects ON objects.subjectcsid = hier.name LEFT JOIN valuations loanvaluation ON loanvaluation.subjectcsid = hier.name LEFT JOIN valuations objvaluation ON objvaluation.subjectcsid = objects.csid @@ -187,6 +193,21 @@ $P!{whereclause}]]> + + + + + + + + + + + + + + + @@ -344,6 +365,27 @@ $P!{whereclause}]]> + + + + + + + + + + + + + + + + + + + + + @@ -469,6 +511,24 @@ $P!{whereclause}]]> + + + + + + + + + + + + + + + + + + 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 9c31ce07d..4e3bf4f37 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 @@ -1,6 +1,19 @@ + + + + + + + + + + + + + diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/outgoing_loan.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/outgoing_loan.jrxml index d6b2d0544..0ba5b60d5 100644 --- a/services/report/3rdparty/jasper-cs-report/src/main/resources/outgoing_loan.jrxml +++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/outgoing_loan.jrxml @@ -1,6 +1,6 @@ - + @@ -37,6 +37,8 @@ SELECT loan.loanoutnumber, loan.borrower, loan.borrowerscontact, + loan.loanpurpose, + loan.specialconditionsofloan, COALESCE(person_addr.addressplace1, org_addr.addressplace1, '') AS addressplace1, COALESCE(person_addr.addressplace2, org_addr.addressplace2, '') AS addressplace2, COALESCE(person_addr.addresstype, org_addr.addresstype, '') AS addresstype, @@ -48,6 +50,7 @@ SELECT loan.loanrenewalapplicationdate, object.objectnumber, object.objectname, + object.briefdescription, loan_valuation.valueamount AS loanvalueamount, loan_valuation.valuecurrency AS loanvaluecurrency, obj_valuation.valueamount AS objvalueamount, @@ -79,15 +82,18 @@ LEFT JOIN ( hier.name AS csid, relation.subjectcsid, obj.objectnumber, - ong.objectname + ong.objectname, + string_agg(bd.item, '; ') AS briefdescription FROM collectionobjects_common obj INNER JOIN hierarchy hier ON hier.id = obj.id INNER JOIN misc on misc.id = obj.id AND misc.lifecyclestate != 'deleted' INNER JOIN relations_common relation ON relation.objectcsid = hier.name AND relation.subjectdocumenttype = 'Loanout' AND relation.objectdocumenttype = 'CollectionObject' + LEFT JOIN collectionobjects_common_briefdescriptions bd ON bd.id = obj.id LEFT JOIN hierarchy ong_hier ON ong_hier.parentid = obj.id AND ong_hier.primarytype = 'objectNameGroup' AND ong_hier.pos = 0 LEFT JOIN objectnamegroup ong ON ong.id = ong_hier.id + GROUP BY relation.subjectcsid, obj.objectnumber, ong.objectname, hier.name ) object ON object.subjectcsid = hier.name -- valuations LEFT JOIN valuations loan_valuation ON loan_valuation.subjectcsid = hier.name @@ -200,6 +206,21 @@ $P!{whereclause} + + + + + + + + + + + + + + + @@ -364,6 +385,27 @@ $P!{whereclause} + + + + + + + + + + + + + + + + + + + + + @@ -495,6 +537,24 @@ $P!{whereclause} + + + + + + + + + + + + + + + + + + -- 2.47.3