From ff9ab18d9629c2c8dde86c7f26676ddc2073a136 Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Thu, 5 Aug 2010 15:30:51 +0000
Subject: [PATCH] speed up open circ counting and listing

git-svn-id: svn://svn.open-ils.org/ILS/trunk@17100 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 Open-ILS/examples/fm_IDL.xml                       | 84 ++++++++++++++++++++++
 Open-ILS/src/perlmods/OpenILS/Application/Actor.pm | 40 ++++++++++-
 2 files changed, 121 insertions(+), 3 deletions(-)

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 546667d83..0fb4d103b 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -2455,6 +2455,90 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
             </actions>
         </permacrud>
 	</class>
+	<class id="ocirccount" controller="open-ils.cstore" oils_obj:fieldmapper="action::open_circ_count" reporter:label="Open Circulation Count" oils_persist:readonly="true">
+        <oils_persist:source_definition>
+SELECT  usr,
+        SUM(
+            CASE
+                WHEN (
+                    ((fine_interval >= '1 day' AND due_date >= 'today') OR (fine_interval < '1 day'  AND due_date > 'now'))
+                    AND (stop_fines IS NULL OR stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE'))
+                ) THEN 1
+                ELSE 0
+            END
+        ) AS out,
+
+        SUM(
+            CASE
+                WHEN (
+                    ((fine_interval >= '1 day' AND due_date < 'today') OR (fine_interval < '1 day'  AND due_date < 'now'))
+                    AND (stop_fines IS NULL OR stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE'))
+                ) THEN 1
+                ELSE 0
+            END
+        ) AS overdue,
+
+        SUM( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LOST') THEN id ELSE 0 END) AS lost,
+        SUM( CASE WHEN (xact_finish IS NULL AND stop_fines = 'CLAIMSRETURNED') THEN id ELSE 0 END) AS claims_returned,
+        SUM( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LONGOVERDUE') THEN id ELSE 0 END) AS long_overdue
+  FROM  action.circulation
+  WHERE checkin_time IS NULL
+  GROUP BY 1;
+        </oils_persist:source_definition>
+        <fields oils_persist:primary="id">
+            <field reporter:label="User ID" name="usr" reporter:datatype="link"/>
+            <field reporter:label="Out" name="out" reporter:datatype="text"/>
+            <field reporter:label="Overdue" name="overdue" reporter:datatype="text"/>
+            <field reporter:label="Lost" name="lost" reporter:datatype="text"/>
+            <field reporter:label="Claims Returned" name="claims_returned" reporter:datatype="text"/>
+            <field reporter:label="Long Overdue" name="long_overdue" reporter:datatype="text"/>
+        </fields>
+        <links>
+            <link field="usr" reltype="has_a" key="id" map="" class="au"/>
+        </links>
+	</class>
+	<class id="ocirclist" controller="open-ils.cstore" oils_obj:fieldmapper="action::open_circ_list" reporter:label="Open Circulation List" oils_persist:readonly="true">
+        <oils_persist:source_definition>
+SELECT  usr,
+        ARRAY_TO_STRING(ARRAY_ACCUM(
+            CASE
+                WHEN (
+                    ((fine_interval >= '1 day' AND due_date >= 'today') OR (fine_interval &lt; '1 day'  AND due_date > 'now'))
+                    AND (stop_fines IS NULL OR stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE'))
+                ) THEN id
+                ELSE 0
+            END
+        ),',') AS out,
+
+        ARRAY_TO_STRING(ARRAY_ACCUM(
+            CASE
+                WHEN (
+                    ((fine_interval >= '1 day' AND due_date &lt; 'today') OR (fine_interval &lt; '1 day'  AND due_date &lt; 'now'))
+                    AND (stop_fines IS NULL OR stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE'))
+                ) THEN id
+                ELSE 0
+            END
+        ),',') AS overdue,
+
+        ARRAY_TO_STRING(ARRAY_ACCUM( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LOST') THEN id ELSE 0 END),',') AS lost,
+        ARRAY_TO_STRING(ARRAY_ACCUM( CASE WHEN (xact_finish IS NULL AND stop_fines = 'CLAIMSRETURNED') THEN id ELSE 0 END),',') AS claims_returned,
+        ARRAY_TO_STRING(ARRAY_ACCUM( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LONGOVERDUE') THEN id ELSE 0 END),',') AS long_overdue
+  FROM  action.circulation
+  WHERE checkin_time IS NULL
+  GROUP BY 1;
+        </oils_persist:source_definition>
+        <fields oils_persist:primary="id">
+            <field reporter:label="User ID" name="usr" reporter:datatype="link"/>
+            <field reporter:label="Out" name="out" reporter:datatype="text"/>
+            <field reporter:label="Overdue" name="overdue" reporter:datatype="text"/>
+            <field reporter:label="Lost" name="lost" reporter:datatype="text"/>
+            <field reporter:label="Claims Returned" name="claims_returned" reporter:datatype="text"/>
+            <field reporter:label="Long Overdue" name="long_overdue" reporter:datatype="text"/>
+        </fields>
+        <links>
+            <link field="usr" reltype="has_a" key="id" map="" class="au"/>
+        </links>
+	</class>
 	<class id="circ" controller="open-ils.cstore" oils_obj:fieldmapper="action::circulation" oils_persist:tablename="action.circulation" reporter:core="true" reporter:label="Circulation">
 		<fields oils_persist:primary="id" oils_persist:sequence="money.billable_xact_id_seq">
 			<field reporter:label="Check In Library" name="checkin_lib" reporter:datatype="org_unit"/>
diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Actor.pm b/Open-ILS/src/perlmods/OpenILS/Application/Actor.pm
index 00574e5ba..9d6c0b508 100644
--- a/Open-ILS/src/perlmods/OpenILS/Application/Actor.pm
+++ b/Open-ILS/src/perlmods/OpenILS/Application/Actor.pm
@@ -1840,9 +1840,43 @@ sub checked_out {
 
 sub _checked_out {
 	my( $iscount, $e, $userid ) = @_;
-	my $meth = 'open-ils.storage.actor.user.checked_out';
-	$meth = "$meth.count" if $iscount;
-	return $U->storagereq($meth, $userid);
+
+    my %result = (
+        out => [],
+        overdue => [],
+        lost => [],
+        claims_returned => [],
+        long_overdue => []
+    );
+	my $meth = 'retrieve_action_open_circ_';
+
+    if ($iscount) {
+	    $meth .= 'count';
+        %result = (
+            out => 0,
+            overdue => 0,
+            lost => 0,
+            claims_returned => 0,
+            long_overdue => 0
+        );
+    } else {
+	    $meth .= 'list';
+    }
+
+    my $data = $e->$meth($userid);
+
+    if ($data) {
+        if ($iscount) {
+            $result{$_} += $data->$_() for (keys %result);
+            $result{total} += $data->$_() for (keys %result);
+        } else {
+            for my $k (keys %result) {
+                $result{$k} = [ grep { $_ > 0 } split( ',', $data->$k()) ];
+            }
+        }
+    }
+
+    return \%result;
 }
 
 
-- 
2.11.0