From afa01bb448fcd89f341ed6eacdceb7a338946b13 Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Mon, 1 Feb 2010 17:25:56 +0000
Subject: [PATCH] Backporting r15146: Patch from Doug Kyle to address
 transit-related deficiency in the reshelving-complete query

git-svn-id: svn://svn.open-ils.org/ILS/branches/rel_1_6_0@15418 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 .../Application/Storage/Publisher/action.pm        | 54 +++++++++++++---------
 1 file changed, 31 insertions(+), 23 deletions(-)

diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/action.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/action.pm
index 2663a2acdc..bb5b28a4f1 100644
--- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/action.pm
+++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/action.pm
@@ -131,35 +131,43 @@ sub complete_reshelving {
 	my $setting = actor::org_unit_setting->table;
 	my $circ = action::circulation->table;
 	my $cp = asset::copy->table;
+	my $atc = action::transit_copy->table;
 
 	my $sql = <<"	SQL";
 		UPDATE	$cp
 		  SET	status = 0
 		  WHERE	id IN (
-            SELECT  id
-              FROM  (SELECT cp.id, MAX(circ.checkin_time)
-                      FROM  $cp cp
-                            JOIN $circ circ ON (circ.target_copy = cp.id)
-                            LEFT JOIN $setting setting
-                                ON (cp.circ_lib = setting.org_unit AND setting.name = 'circ.reshelving_complete.interval')
-                      WHERE circ.checkin_time IS NOT NULL
-                            AND cp.status = 7
-                      GROUP BY 1
-                      HAVING MAX(circ.checkin_time) < NOW() - CAST( COALESCE( BTRIM( FIRST(setting.value),'"' ), ? )  AS INTERVAL)
-                    ) AS foo
-                                UNION ALL
-            SELECT  cp.id
-			   FROM $cp cp 
-                    LEFT JOIN $setting setting
-                        ON (cp.circ_lib = setting.org_unit AND setting.name = 'circ.reshelving_complete.interval')
-                    LEFT JOIN $circ circ ON (circ.target_copy = cp.id)
-              WHERE cp.status = 7 AND circ.id IS NULL
-                    AND cp.create_date < NOW() - CAST( COALESCE( BTRIM( setting.value,'"' ), ? )  AS INTERVAL)
-          )
-	SQL
-
+			SELECT  id
+			  FROM  (SELECT cp.id, MAX(circ.checkin_time), MAX(trans.dest_recv_time)
+					  FROM  $cp cp
+							JOIN $circ circ ON (circ.target_copy = cp.id)
+							LEFT JOIN $atc trans ON (trans.target_copy = cp.id)
+							LEFT JOIN $setting setting
+								ON (cp.circ_lib = setting.org_unit AND setting.name = 'circ.reshelving_complete.interval')
+					  WHERE circ.checkin_time IS NOT NULL
+							AND cp.status = 7
+					  GROUP BY 1
+					  HAVING (
+						( ( MAX(circ.checkin_time) > MAX(trans.dest_recv_time) or MAX(trans.dest_recv_time) IS NULL )
+						  AND MAX(circ.checkin_time) < NOW() - CAST( COALESCE( BTRIM( FIRST(setting.value),'"' ), ? )  AS INTERVAL) )
+						OR
+						( MAX(trans.dest_recv_time) > MAX(circ.checkin_time)
+						  AND MAX(trans.dest_recv_time) < NOW() - CAST( COALESCE( BTRIM( FIRST(setting.value),'"' ), ? )  AS INTERVAL) )
+					  )
+					) AS foo
+								UNION ALL
+			SELECT  cp.id
+			  FROM  $cp cp 
+					LEFT JOIN $setting setting
+						ON (cp.circ_lib = setting.org_unit AND setting.name = 'circ.reshelving_complete.interval')
+					LEFT JOIN $circ circ ON (circ.target_copy = cp.id)
+			  WHERE cp.status = 7
+					AND circ.id IS NULL
+					AND cp.create_date < NOW() - CAST( COALESCE( BTRIM( setting.value,'"' ), ? )  AS INTERVAL)
+		  )
+ 	SQL
 	my $sth = action::circulation->db_Main->prepare_cached($sql);
-	$sth->execute($window, $window);
+	$sth->execute($window, $window, $window);
 
 	return $sth->rows;
 
-- 
2.11.0