From 0d57ca4a57461af31e649de8f9b68b4ab34cd76d Mon Sep 17 00:00:00 2001
From: Bill Erickson <berickxx@gmail.com>
Date: Mon, 6 Feb 2017 17:11:53 -0500
Subject: [PATCH] JBAS-1405 Revert custom hold queue stats code

Return to using the custom hold queue position calculation code.
This merely moves the logic from the DB back into the Perl code.  The
query logic is unchanged.

Signed-off-by: Bill Erickson <berickxx@gmail.com>
---
 KCLS/sql/schema/deploy/revert-custom-qstats.sql    |  8 ++++++
 KCLS/sql/schema/revert/revert-custom-qstats.sql    | 19 ++++++++++++++
 KCLS/sql/schema/sqitch.plan                        |  1 +
 KCLS/sql/schema/verify/revert-custom-qstats.sql    |  7 +++++
 .../perlmods/lib/OpenILS/Application/Circ/Holds.pm | 30 +++++++++++++++++++++-
 5 files changed, 64 insertions(+), 1 deletion(-)
 create mode 100644 KCLS/sql/schema/deploy/revert-custom-qstats.sql
 create mode 100644 KCLS/sql/schema/revert/revert-custom-qstats.sql
 create mode 100644 KCLS/sql/schema/verify/revert-custom-qstats.sql

diff --git a/KCLS/sql/schema/deploy/revert-custom-qstats.sql b/KCLS/sql/schema/deploy/revert-custom-qstats.sql
new file mode 100644
index 0000000000..275298bb0a
--- /dev/null
+++ b/KCLS/sql/schema/deploy/revert-custom-qstats.sql
@@ -0,0 +1,8 @@
+-- Deploy kcls-evergreen:revert-custom-qstats to pg
+-- requires: backstage-exports-continued
+
+BEGIN;
+
+DROP FUNCTION IF EXISTS action.get_hold_queue_status_by_id(INTEGER);
+
+COMMIT;
diff --git a/KCLS/sql/schema/revert/revert-custom-qstats.sql b/KCLS/sql/schema/revert/revert-custom-qstats.sql
new file mode 100644
index 0000000000..a73ad48adc
--- /dev/null
+++ b/KCLS/sql/schema/revert/revert-custom-qstats.sql
@@ -0,0 +1,19 @@
+-- Revert kcls-evergreen:revert-custom-qstats from pg
+
+BEGIN;
+
+CREATE OR REPLACE FUNCTION get_hold_queue_status_by_id(integer) 
+    RETURNS TABLE(id integer, cut_in_line boolean, request_time timestamp with time zone)
+    LANGUAGE sql STABLE
+    AS $_$
+WITH holds(holdid) AS 
+( SELECT acm.hold FROM action.hold_copy_map acm 
+  JOIN action.hold_copy_map acm2 USING(target_copy) 
+  WHERE acm2.hold=$1) 
+  SELECT id, cut_in_line, request_time FROM action.hold_request 
+  WHERE id IN (SELECT holdid FROM holds) 
+  ORDER BY coalesce(cut_in_line, false ) DESC, request_time; 
+$_$;
+
+COMMIT;
+
diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan
index 34cfffc2e3..3e3b1524b9 100644
--- a/KCLS/sql/schema/sqitch.plan
+++ b/KCLS/sql/schema/sqitch.plan
@@ -42,3 +42,4 @@ purge-holds [audit-table-maint] 2016-09-21T16:01:57Z Bill Erickson <berickxx@gma
 2.7-to-2.9-upgrade [backstage-exports-continued] 2016-12-09T20:27:17Z Bill Erickson <berickxx@gmail.com> # 2.7 to 2.9 Upgrade
 2.7-to-2.9-upgrade-part-2 [2.7-to-2.9-upgrade] 2016-12-12T15:19:07Z Bill Erickson <berickxx@gmail.com> # 2.7 to 2.9 Upgrade Secondary Updates
 pg-9.6-prep [2.7-to-2.9-upgrade-part-2] 2017-03-20T15:59:10Z Bill Erickson,,, <berick@cylinders> # Prep for PG 9.6
+revert-custom-qstats [backstage-exports-continued] 2017-02-06T22:09:42Z Bill Erickson <berickxx@gmail.com> # Remove custom hold queue position func
diff --git a/KCLS/sql/schema/verify/revert-custom-qstats.sql b/KCLS/sql/schema/verify/revert-custom-qstats.sql
new file mode 100644
index 0000000000..78d1abdb67
--- /dev/null
+++ b/KCLS/sql/schema/verify/revert-custom-qstats.sql
@@ -0,0 +1,7 @@
+-- Verify kcls-evergreen:revert-custom-qstats on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm
index 8d18aa9052..82c0b2ab6a 100644
--- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm
+++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm
@@ -1387,7 +1387,35 @@ sub retrieve_hold_queue_status_impl {
 
         # fetch cut_in_line and request_time since they're in the order_by
         # and we're asking for distinct values
-        from => [ "action.get_hold_queue_status_by_id", $hold->id ]
+        select => {ahr => ['id', 'cut_in_line', 'request_time']},
+        from   => 'ahr',
+        where => {
+            id => { in => {
+                select => { ahcm => ['hold'] },
+                from   => {
+                    'ahcm' => {
+                        'ahcm2' => {
+                            'class' => 'ahcm',
+                            'field' => 'target_copy',
+                            'fkey'  => 'target_copy'
+                        }
+                    }
+                },
+                where => { '+ahcm2' => { hold => $hold->id } },
+                distinct => 1
+            }}
+        },
+        order_by => [
+            {
+                "class" => "ahr",
+                "field" => "cut_in_line",
+                "transform" => "coalesce",
+                "params" => [ 0 ],
+                "direction" => "desc"
+            },
+            { "class" => "ahr", "field" => "request_time" }
+        ],
+        distinct => 1
     });
 
     if (!@$q_holds) { # none? maybe we don't have a map ...
-- 
2.11.0