From 1ad56082788211d91030f47147efbbfd3e4be77e Mon Sep 17 00:00:00 2001 From: Steven Callender Date: Wed, 19 Oct 2011 18:51:14 +0000 Subject: [PATCH] Reshelving update. Update to the reshelving code to make it faster and utilize the new status change time field on asset.copy. Signed-off-by: Steven Callender Signed-off-by: Mike Rylander --- .../Application/Storage/Publisher/action.pm | 35 ++++--------------- 1 file changed, 6 insertions(+), 29 deletions(-) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm index 6e3ea0116a..2069f58ca9 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm @@ -162,41 +162,18 @@ sub complete_reshelving { unless (interval_to_seconds( $window )); 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), 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) + SELECT cp.id + FROM $cp cp + LEFT JOIN $setting setting + ON (cp.circ_lib = setting.org_unit AND setting.name = 'circ.reshelving_complete.interval') + WHERE cp.status = 7 + AND cp.status_changed_time < NOW() - CAST( COALESCE( BTRIM( setting.value,'"' ), ? ) AS INTERVAL) ) SQL my $sth = action::circulation->db_Main->prepare_cached($sql); -- 2.43.2