[OPEN-ILS-DEV] Reshelving Complete

[Doug Kyle]

Paul Waak wrote:
>
> On Sep 18, 2009, at 9:13 AM, Mike Rylander wrote:
>
>> [big snip]
>> So, are their any circulation-manager-type folks out there that would
>> be willing to weigh in on this?  To summarize, the options on the
>> table are:
>>
>> 1) Tie reshelving-complete to the last edit time (effectively the
>> "last time we touched the copy") for copies in the Reshelving status
>> [non-circulation edits would extend the reshelving delay]
>> 2) Extend the current behavior (checkin time OR create time for new,
>> uncirculated copies) to make it aware of transits and apply the delay
>> only after a post-circulation transit is complete [more complicated,
>> and possibly slower, reshelving query]
>>
> Option 1 gets my vote. This is what (we said) we wanted when I was 
> working in circulation. It compensates for people taking a book from a 
> shelving cart, making changes, then putting it on a random cart in the 
> checkin room.
>
>> -- 
>> Mike Rylander
>> | VP, Research and Design
>> | Equinox Software, Inc. / The Evergreen Experts
>> | phone:  1-877-OPEN-ILS (673-6457)
>> | email:  miker at esilibrary.com
>> | web:  http://www.esilibrary.com
>
> Paul Waak
> pwaak at yahoo.com
>
I'm not a circ manager type, but I believe option 1 would work better 
for my library, although why not write it so libraries can choose either 
option?  I was looking at the following partial query modified from sub 
complete_reshelving in Storage/Publisher/action.pm to account for transits.

SELECT cp.id, MAX(circ.checkin_time), MAX(trans.dest_recv_time)
                      FROM  asset.copy cp
                            JOIN action.circulation circ ON 
(circ.target_copy = cp.id)
                            Join action.transit_copy trans ON 
(trans.target_copy = cp.id)
                            LEFT JOIN actor.org_unit_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) and
                            MAX(circ.checkin_time) < NOW() - CAST( 
COALESCE( BTRIM( FIRST(setting.value),'"' ), '24h'::text )  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),'"' ), '24h'::text )  AS 
INTERVAL)
                       )


but I'm confused as to why the query plan refers to 
action.hold_transit_copy as well as action.transit_copy?

Sort  (cost=43947.95..43952.98 rows=2010 width=34)
   Sort Key: max(trans.dest_recv_time)
   ->  HashAggregate  (cost=43707.02..43837.67 rows=2010 width=34)
         Filter: (((max(checkin_time) > max(dest_recv_time)) AND 
(max(checkin_time) < (now() - (COALESCE(btrim("first"(value), 
'"'::text), '24h'::text))::interval))) OR ((max(dest_recv_time) > 
max(checkin_time)) AND (max(dest_recv_time) < (now() - 
(COALESCE(btrim("first"(value), '"'::text), '24h'::text))::interval))))
         ->  Nested Loop  (cost=0.00..43651.75 rows=2010 width=34)
               ->  Nested Loop Left Join  (cost=0.00..9052.25 rows=602 
width=34)
                     ->  Nested Loop  (cost=0.00..8879.32 rows=602 width=28)
                           Join Filter: (trans.target_copy = cp.id)
                           ->  Index Scan using copy_status_idx on 
"copy" cp  (cost=0.00..2667.67 rows=2715 width=12)
                                 Index Cond: (status = 7)
                           ->  Append  (cost=0.00..2.24 rows=4 width=16)
                                 ->  Index Scan using 
active_transit_cp_idx on transit_copy trans  (cost=0.00..1.52 rows=2 
width=16)
                                       Index Cond: (trans.target_copy = 
cp.id)
                                 ->  Index Scan using 
active_hold_transit_cp_idx on hold_transit_copy trans  (cost=0.00..0.72 
rows=2 width=16)
                                       Index Cond: (trans.target_copy = 
cp.id)
                     ->  Index Scan using ou_once_per_key on 
org_unit_setting setting  (cost=0.00..0.27 rows=1 width=14)
                           Index Cond: ((cp.circ_lib = setting.org_unit) 
AND (setting.name = 'circ.reshelving_complete.interval'::text))
               ->  Index Scan using circ_copy_idx on circulation circ  
(cost=0.00..57.10 rows=30 width=16)
                     Index Cond: (circ.target_copy = cp.id)
                     Filter: (checkin_time IS NOT NULL)