From 8334454215055a217f15dcb966ad5d899080799f Mon Sep 17 00:00:00 2001
From: Liam Whalen <liam.whalen@bc.libraries.coop>
Date: Fri, 6 Nov 2015 14:46:45 -0800
Subject: [PATCH] LP#978095 Fixed SQL query

The JSON SQL query was no where close to correct.  This makes numerous
adjustments to it.

Signed-off-by: Liam Whalen <liam.whalen@bc.libraries.coop>
---
 .../perlmods/lib/OpenILS/Application/Acq/Order.pm  | 73 +++++++++++-----------
 1 file changed, 36 insertions(+), 37 deletions(-)

diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/Order.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/Order.pm
index 051637ffce..3e606ba561 100644
--- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/Order.pm
+++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Acq/Order.pm
@@ -3257,7 +3257,7 @@ sub cancel_lineitem_detail_api {
     my $result = cancel_lineitem_detail($mgr, $lid_id, $cancel_reason) or
         return $e->die_event;
 
-    no_lid_cancel_lineitem($e, $lid_id);
+    no_lid_cancel_lineitem($e, $mgr, $lid_id);
 
     if (not_cancelable($result)) {
         $e->rollback;
@@ -3338,13 +3338,13 @@ sub cancel_lineitem_detail {
 }
 
 sub no_lid_cancel_lineitem {
-    my ($e, $lid_id) = @_;
+    my ($e, $mgr, $lid_id) = @_;
     my $lid = $mgr->editor->retrieve_acq_lineitem_detail([
         $lid_id, {
             "flesh" => 2,
             "flesh_fields" => {
                 "acqlid" => ["lineitem","cancel_reason"],
-                "jub" => ["purchase_order"]
+                "jub" => ["purchase_order", "eg_bib_id"]
             }
         }
     ]) or return 0;
@@ -3352,42 +3352,41 @@ sub no_lid_cancel_lineitem {
     #Get a count of lineitem details that exist on the lineitem
     #that are not cancelled, or lineitem details that are
     #cancelled but having keep_debits = true.
-    my $query = { "select" => {
-        "acqlid" => [
-            "lineitem", {
-                "column" => "lineitem",
-                "transform" => "count",
-                "alias" => "lineitem_count",
+
+    my $query = { "select" => { 
+        "acqlid" => [ 
+            "lineitem", { 
+                "column" => "lineitem", 
+                "transform" => "count", 
+                "alias" => "lineitem_count", 
                 "aggregate" => "true"
             }]
-        },
-        "from" => {
-            "jub" => {
-                "acqlid" => {
-                    "join" => { "acqcr" => {} }
-                }
-            }
-        },
-        "where" => [{
-                "+acqlid" => {
-                    "lineitem" => $lid->lineitem->id,
-                    "cancel_reason" => null
-                }
-            }, {
-                "purchase_order" => $lid->lineitem->purchase_order
-            }, {
-                "-or" => [{
-                        "+acqlid" => {
-                            "lineitem" => $lid->lineitem->id,
-                            "cancel_reason" => { "!=" => null }
-                        }
-                    }, {
-                        "+acqcr" => "keep_debits"
-                    }, {
-                        "purchase_order" => $lid->lineitem->purchase_order
-                    }]
-            }
-        ]
+        }, "from" => { 
+            "jub" => { 
+                "acqlid" => { 
+                    "join" => { 
+                        "acqcr" => { 
+                            "type" => "left" 
+                        } 
+                    } 
+                } 
+            } 
+        }, 
+        "where" => {
+            "-or" => [{ 
+                    "-and" => { 
+                        "+acqlid" => { "cancel_reason" => undef }, 
+                        "eg_bib_id" => $lid->lineitem->eg_bib_id, 
+                        "purchase_order" => $lid->lineitem->purchase_order 
+                    }}, {
+                    "-and" => { 
+                        "+acqlid" => { "cancel_reason" => { "!=" => undef }}, 
+                        "+acqcr" => "keep_debits", 
+                        "eg_bib_id" => $lid->lineitem->eg_bib_id, 
+                        "purchase_order" => $lid->lineitem->purchase_order 
+                    }
+            }]
+        }
     };
 
     my $lineitem_query = $e->json_query($query)
-- 
2.11.0