From 15aaf0345bbdc413b02bf5cfcf4fd54282682f9a Mon Sep 17 00:00:00 2001
From: Chris Sharp <csharp@georgialibraries.org>
Date: Fri, 18 Mar 2022 10:05:25 -0400
Subject: [PATCH] add faster circulation view

Signed-off-by: Chris Sharp <csharp@georgialibraries.org>
---
 Open-ILS/examples/fm_IDL.xml                       | 43 ++++++++++++++++++++
 Open-ILS/src/sql/Pg/example.reporter-extension.sql | 47 ++++++++++++++++++++++
 2 files changed, 90 insertions(+)

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 089c68b430..10dd9c2fa6 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -12993,6 +12993,49 @@ SELECT  usr,
 		</links>
 	</class>
 
+	<class id="rcv" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::circulation_view" oils_persist:tablename="reporter.circulation_view" reporter:core="true" reporter:label="Circulation View">
+		<fields oils_persist:primary="id">
+			<field reporter:label="Circulation" name="id" reporter:datatype="id" />
+			<field reporter:label="Library Circulation Location Short (Policy) Name" name="circ_lib" reporter:datatype="text"/>
+			<field reporter:label="Library Circulation Location Link" name="circ_lib_id" reporter:datatype="org_unit"/>
+			<field reporter:label="Circulation Date/Time" name="xact_start" reporter:datatype="timestamp"/>
+			<field reporter:label="Circulation Type" name="circ_type" reporter:datatype="text"/>
+			<field reporter:label="Copy Link" name="copy_id" reporter:datatype="link"/>
+			<field reporter:label="Circ Modifier" name="circ_modifier" reporter:datatype="text"/>
+			<field reporter:label="Owning Library Short (Policy) Name" name="owning_lib_name" reporter:datatype="text"/>
+			<field reporter:label="Shelving Location" name="shelving_location" reporter:datatype="text"/>
+			<field reporter:label="Patron Profile Group" name="profile_group" reporter:datatype="text"/>
+			<field reporter:label="Patron Age Demographic" name="demographic_general_division" reporter:datatype="text"/>
+			<field reporter:label="Call Number Link" name="call_number" reporter:datatype="link"/>
+			<field reporter:label="Call Number Label" name="call_number_label" reporter:datatype="text"/>
+			<field reporter:label="Call Number Dewey/Prefix" name="dewey" reporter:datatype="text"/>
+			<field reporter:label="Patron Link" name="patron_id" reporter:datatype="link"/>
+			<field reporter:label="Patron Home Library Link" name="patron_home_lib" reporter:datatype="link"/>
+			<field reporter:label="Patron Home Library Short (Policy) Name" name="patron_home_lib_shortname" reporter:datatype="text"/>
+			<field reporter:label="Patron County" name="patron_county" reporter:datatype="text"/>
+			<field reporter:label="Patron City" name="patron_city" reporter:datatype="text"/>
+			<field reporter:label="Patron ZIP Code" name="patron_zip" reporter:datatype="text"/>
+			<field reporter:label="Legacy CAT1 Link" name="stat_cat_1" reporter:datatype="link"/>
+			<field reporter:label="Legacy CAT2 Link" name="stat_cat_2" reporter:datatype="link"/>
+			<field reporter:label="Dewey Range - Tens" name="dewey_range_tens" reporter:datatype="text"/>
+			<field reporter:label="Dewey Range - Hundreds" name="dewey_range_hundreds" reporter:datatype="text"/>
+			<field reporter:label="Dewey Block - Tens" name="dewey_block_tens" reporter:datatype="text"/>
+			<field reporter:label="Dewey Block - Hundreds" name="dewey_block_hundreds" reporter:datatype="text"/>
+			<field reporter:label="Legacy CAT1 Value" name="stat_cat_1_value" reporter:datatype="text"/>
+			<field reporter:label="Legacy CAT2 Value" name="stat_cat_2_value" reporter:datatype="text"/>
+		</fields>
+		<links>
+			<link field="id" reltype="has_a" key="id" map="" class="circ"/>
+			<link field="copy_id" reltype="has_a" key="id" map="" class="acp"/>
+			<link field="patron_id" reltype="has_a" key="id" map="" class="au"/>
+			<link field="circ_lib_id" reltype="has_a" key="id" map="" class="aou"/>
+			<link field="call_number" reltype="has_a" key="id" map="" class="acn"/>
+			<link field="patron_home_lib" reltype="has_a" key="id" map="" class="aou"/>
+			<link field="stat_cat_1" reltype="has_a" key="id" map="" class="rsce1"/>
+			<link field="stat_cat_2" reltype="has_a" key="id" map="" class="rsce2"/>
+		</links>
+	</class>
+
 	<class id="rsce1" controller="open-ils.reporter-store open-ils.cstore" oils_obj:fieldmapper="reporter::legacy_cat1" oils_persist:tablename="reporter.legacy_cat1" reporter:label="CAT1 Entry">
 		<fields oils_persist:primary="id">
 			<field reporter:label="Entry ID" name="id" reporter:selector="value" reporter:datatype="id" />
diff --git a/Open-ILS/src/sql/Pg/example.reporter-extension.sql b/Open-ILS/src/sql/Pg/example.reporter-extension.sql
index 15fe4c5a7c..6deebd9b1a 100644
--- a/Open-ILS/src/sql/Pg/example.reporter-extension.sql
+++ b/Open-ILS/src/sql/Pg/example.reporter-extension.sql
@@ -120,6 +120,53 @@ SELECT	cl.shortname AS circ_lib,
 	LEFT JOIN asset.stat_cat_entry_copy_map sc2 ON (sc2.owning_copy = cp.id AND sc2.stat_cat = 2)
 	LEFT JOIN asset.stat_cat_entry sce2 ON (sce2.id = sc2.stat_cat_entry);
 
+CREATE OR REPLACE VIEW reporter.circulation_view AS
+SELECT cl.shortname AS circ_lib,
+    cl.id AS circ_lib_id,
+    circ.xact_start,
+    circ_type.type AS circ_type,
+    cp.id AS copy_id,
+    cp.circ_modifier,
+    ol.shortname AS owning_lib_name,
+    sl.name AS shelving_location,
+    p.id AS patron_id,
+    g.name AS profile_group,
+    dem.general_division AS demographic_general_division,
+    circ.id,
+    cn.id AS call_number,
+    cn.label AS call_number_label,
+    call_number_dewey(cn.label) AS dewey,
+    racnd.dewey_block_tens,
+    racnd.dewey_block_hundreds,
+    racnd.dewey_range_tens,
+    racnd.dewey_range_hundreds,
+    hl.id AS patron_home_lib,
+    hl.shortname AS patron_home_lib_shortname,
+    paddr.county AS patron_county,
+    paddr.city AS patron_city,
+    paddr.post_code AS patron_zip,
+    sc1.stat_cat_entry AS stat_cat_1,
+    sc2.stat_cat_entry AS stat_cat_2,
+    sce1.value AS stat_cat_1_value,
+    sce2.value AS stat_cat_2_value
+   FROM action.circulation circ
+     JOIN reporter.circ_type circ_type ON circ.id = circ_type.id
+     JOIN asset.copy cp ON cp.id = circ.target_copy
+     JOIN asset.copy_location sl ON cp.location = sl.id
+     JOIN asset.call_number cn ON cp.call_number = cn.id
+     JOIN reporter.asset_call_number_dewey racnd ON racnd.call_number = cn.id
+     JOIN actor.org_unit ol ON cn.owning_lib = ol.id
+     JOIN actor.org_unit cl ON circ.circ_lib = cl.id
+     JOIN actor.usr p ON p.id = circ.usr
+     JOIN actor.org_unit hl ON p.home_ou = hl.id
+     JOIN permission.grp_tree g ON p.profile = g.id
+     JOIN reporter.demographic dem ON dem.id = p.id
+     LEFT JOIN actor.usr_address paddr ON paddr.id = p.billing_address
+     LEFT JOIN asset.stat_cat_entry_copy_map sc1 ON sc1.owning_copy = cp.id AND sc1.stat_cat = 1
+     LEFT JOIN asset.stat_cat_entry sce1 ON sce1.id = sc1.stat_cat_entry
+     LEFT JOIN asset.stat_cat_entry_copy_map sc2 ON sc2.owning_copy = cp.id AND sc2.stat_cat = 2
+     LEFT JOIN asset.stat_cat_entry sce2 ON sce2.id = sc2.stat_cat_entry;
+
 CREATE OR REPLACE VIEW reporter.legacy_cat1 AS
 SELECT	id,
 	owner,
-- 
2.11.0