From 2b2d910201be324fcaed315a56acb0328e7152a7 Mon Sep 17 00:00:00 2001
From: Chris Sharp <csharp@georgialibraries.org>
Date: Wed, 28 Sep 2016 14:36:11 -0400
Subject: [PATCH] LP#1628639 - Reporter Classic Item List source improvements

This changes the following:

1) Capitalize the first letters of all words in title and author.

2) Display the copy status name (previously only available through
	the "Status" link)

3) Add a link to the last editing user.

No DB upgrade script.  To apply re-run Open-ILS/src/sql/Pg/example.reporter-extension.sql

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

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 7126f16779..8a729b2930 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -11215,7 +11215,7 @@ SELECT  usr,
             <field reporter:label="Title" name="title" reporter:datatype="text"/>
             <field reporter:label="Author" name="author" reporter:datatype="text"/>
             <field reporter:label="Pubdate" name="pubdate" reporter:datatype="text"/>
-            <field reporter:label="Copy ID" name="id" reporter:datatype="id"/>
+            <field reporter:label="Copy ID" name="copy_id" reporter:datatype="id"/>
             <field reporter:label="Price" name="price" reporter:datatype="money"/>
             <field reporter:label="Barcode" name="barcode" reporter:datatype="text"/>
             <field reporter:label="Callnumber Label" name="call_number_label" reporter:datatype="text"/>
@@ -11234,14 +11234,16 @@ SELECT  usr,
             <field reporter:label="Circ Lib Name" name="circ_lib_name" reporter:datatype="text"/>
             <field reporter:label="Owning Lib" name="owning_lib" reporter:datatype="link"/>
             <field reporter:label="Circ Lib" name="circ_lib" reporter:datatype="link"/>
-            <field reporter:label="Creator" name="creator" reporter:datatype="link"/>
+            <field reporter:label="Creator" name="creator_id" reporter:datatype="link"/>
+            <field reporter:label="Last Editor" name="editor_id" reporter:datatype="link"/>
             <field reporter:label="Age Protection" name="age_protect" reporter:datatype="link"/>
             <field reporter:label="OPAC Visible" name="opac_visible" reporter:datatype="bool"/>
             <field reporter:label="Reference" name="ref" reporter:datatype="bool"/>
             <field reporter:label="Deposit Amount" name="deposit_amount" reporter:datatype="text"/>
             <field reporter:label="Deleted" name="deleted" reporter:datatype="bool"/>
             <field reporter:label="TCN" name="tcn_value" reporter:datatype="text"/>
-            <field reporter:label="Status" name="status" reporter:datatype="link"/>
+            <field reporter:label="Status ID" name="status_id" reporter:datatype="link"/>
+            <field reporter:label="Status Name" name="status_name" reporter:datatype="text"/>
             <field reporter:label="Stop Fines Reason" name="stop_fines" reporter:datatype="text"/>
             <field reporter:label="Due Date" name="due_date" reporter:datatype="timestamp"/>
             <field reporter:label="Patron Barcode" name="patron_barcode" reporter:datatype="text"/>
@@ -11249,13 +11251,14 @@ SELECT  usr,
         </fields>
         <links>
             <link field="owning_lib" reltype="has_a" key="id" map="" class="aou"/>
-            <link field="id" reltype="has_a" key="id" map="" class="acp"/>
+            <link field="copy_id" reltype="has_a" key="id" map="" class="acp"/>
             <link field="circ_lib" reltype="has_a" key="id" map="" class="aou"/>
-            <link field="creator" reltype="has_a" key="id" map="" class="au"/>
+            <link field="creator_id" reltype="has_a" key="id" map="" class="au"/>
+            <link field="editor_id" reltype="has_a" key="id" map="" class="au"/>
             <link field="age_protect" reltype="has_a" key="id" map="" class="crahp"/>
-            <link field="status" reltype="has_a" key="id" map="" class="ccs"/>
+            <link field="status_id" reltype="has_a" key="id" map="" class="ccs"/>
             <link field="stat_cat_1" reltype="might_have" key="id" map="" class="rsce1"/>
-	    <link field="stat_cat_2" reltype="might_have" key="id" map="" class="rsce2"/>
+            <link field="stat_cat_2" reltype="might_have" key="id" map="" class="rsce2"/>
         </links>
     </class>
 
diff --git a/Open-ILS/src/sql/Pg/example.reporter-extension.sql b/Open-ILS/src/sql/Pg/example.reporter-extension.sql
index 5095aeecf6..5bb754a2f3 100644
--- a/Open-ILS/src/sql/Pg/example.reporter-extension.sql
+++ b/Open-ILS/src/sql/Pg/example.reporter-extension.sql
@@ -172,10 +172,10 @@ SELECT	x.id AS id,
 	JOIN actor.usr_address paddr ON (paddr.id = u.billing_address);
 
 CREATE OR REPLACE VIEW reporter.classic_item_list AS
-SELECT rmsr.title,
-    rmsr.author,
+SELECT initcap(rmsr.title) AS title,
+    initcap(rmsr.author) AS author,
     rmsr.pubdate,
-    cp.id,
+    cp.id AS copy_id,
     cp.price,
     cp.barcode,
     cn.label as call_number_label,
@@ -212,18 +212,20 @@ SELECT rmsr.title,
     cn.owning_lib,
     cl.shortname AS circ_lib_name,
     cl.id AS circ_lib,
-    cp.creator,
+    creator.id AS creator_id,
+    editor.id AS editor_id,
     cp.age_protect,
     cp.opac_visible,
     cp.ref,
     cp.deposit_amount,
     cp.deleted,
     rmsr.tcn_value,
-    cp.status,
+    status.id AS status_id,
+    status.name AS status_name,
     circ.stop_fines,
     circ.due_date,
-    circ_card.barcode as patron_barcode,
-    circ_u.first_given_name || ' ' || circ_u.family_name as patron_name
+    circ_card.barcode AS patron_barcode,
+    circ_u.first_given_name || ' ' || circ_u.family_name AS patron_name
   FROM  asset.copy cp
     JOIN extend_reporter.full_circ_count erfcc ON (cp.id = erfcc.id)
     JOIN asset.copy_location sl ON (cp.location = sl.id)
@@ -231,8 +233,11 @@ SELECT rmsr.title,
     JOIN actor.org_unit ol ON (cn.owning_lib = ol.id)
     JOIN actor.org_unit cl ON (cp.circ_lib = cl.id)
     JOIN reporter.materialized_simple_record rmsr ON (cn.record = rmsr.id)
+    JOIN config.copy_status status ON (cp.status = status.id)
     LEFT JOIN action.circulation circ ON (circ.target_copy = cp .id AND circ.checkin_time IS NULL)
     LEFT JOIN actor.usr circ_u ON (circ_u.id = circ.usr)
+    LEFT JOIN actor.usr creator ON (cp.creator = creator.id)
+    LEFT JOIN actor.usr editor ON (cp.editor = editor.id)
     LEFT JOIN actor.card circ_card ON (circ_u.card = circ_card.id)
     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)
-- 
2.11.0