From 85689a10cda641a70db60423115d904767ffd82a Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Mon, 1 Feb 2010 17:24:21 +0000
Subject: [PATCH] Patch from Doug Kyle to address transit-related deficiency in
the reshelving-complete query.
Previously, items that transited for reshelving at their circulating library would be flipped from Reshelving to Available almost immediately in most cases. Doug taught the "find copies to flip to Available" query about transit receive times, allowing them to stay in Reshelving status for as long as copies returned directly to the circulating library.
git-svn-id: svn://svn.open-ils.org/ILS/trunk@15416 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 5762dd2f0e..9b3590c82c 100644
--- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/action.pm
+++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/action.pm
@@ -147,35 +147,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