3 # Test C4::HoldsQueue::CreateQueue() for both transport cost matrix
4 # and StaticHoldsQueueWeight array (no RandomizeHoldsQueueWeight, no point)
5 # Wraps tests in transaction that's rolled back, so no data is destroyed
6 # MySQL WARNING: This makes sense only if your tables are InnoDB, otherwise
7 # transactions are not supported and mess is left behind
11 use Test::More tests => 51;
21 use Koha::CirculationRules;
23 use t::lib::TestBuilder;
28 use lib $FindBin::Bin;
29 use_ok('C4::Reserves');
30 use_ok('C4::HoldsQueue');
33 my $schema = Koha::Database->schema;
34 $schema->storage->txn_begin;
35 my $dbh = C4::Context->dbh;
36 $dbh->do("DELETE FROM circulation_rules");
38 my $builder = t::lib::TestBuilder->new;
40 t::lib::Mocks::mock_preference( 'UseBranchTransferLimits', '0' );
41 t::lib::Mocks::mock_preference( 'BranchTransferLimitsType', 'itemtype' );
43 my $library1 = $builder->build({
46 my $library2 = $builder->build({
49 my $library3 = $builder->build({
53 my $TITLE = "Test Holds Queue XXX";
55 my $borrower = $builder->build({
58 branchcode => $library1->{branchcode},
62 my $borrowernumber = $borrower->{borrowernumber};
63 # Set special (for this test) branches
64 my $borrower_branchcode = $borrower->{branchcode};
65 my @branchcodes = ( $library1->{branchcode}, $library2->{branchcode}, $library3->{branchcode} );
66 my @other_branches = ( $library2->{branchcode}, $library3->{branchcode} );
67 my $least_cost_branch_code = pop @other_branches;
68 my $itemtype = $builder->build({ source => 'Itemtype', value => { notforloan => 0 } })->{itemtype};
71 # Sysprefs and cost matrix
72 t::lib::Mocks::mock_preference('HoldsQueueSkipClosed', 0);
73 t::lib::Mocks::mock_preference('LocalHoldsPriority', 0);
74 $dbh->do("UPDATE systempreferences SET value = ? WHERE variable = 'StaticHoldsQueueWeight'", undef,
75 join( ',', @other_branches, $borrower_branchcode, $least_cost_branch_code));
76 $dbh->do("UPDATE systempreferences SET value = '0' WHERE variable = 'RandomizeHoldsQueueWeight'");
78 $dbh->do("DELETE FROM transport_cost");
79 my $transport_cost_insert_sth = $dbh->prepare("insert into transport_cost (frombranch, tobranch, cost) values (?, ?, ?)");
80 # Favour $least_cost_branch_code
81 $transport_cost_insert_sth->execute($borrower_branchcode, $least_cost_branch_code, 0.2);
82 $transport_cost_insert_sth->execute($least_cost_branch_code, $borrower_branchcode, 0.2);
83 my @b = @other_branches;
84 while ( my $b1 = shift @b ) {
85 foreach my $b2 ($borrower_branchcode, $least_cost_branch_code, @b) {
86 $transport_cost_insert_sth->execute($b1, $b2, 0.5);
87 $transport_cost_insert_sth->execute($b2, $b1, 0.5);
92 # Loanable items - all possible combinations of homebranch and holdingbranch
93 $dbh->do("INSERT INTO biblio (frameworkcode, author, title, datecreated)
94 VALUES ('SER', 'Koha test', '$TITLE', '2011-02-01')");
95 my $biblionumber = $dbh->selectrow_array("SELECT biblionumber FROM biblio WHERE title = '$TITLE'")
96 or BAIL_OUT("Cannot find newly created biblio record");
97 $dbh->do("INSERT INTO biblioitems (biblionumber, itemtype)
98 VALUES ($biblionumber, '$itemtype')");
99 my $biblioitemnumber = $dbh->selectrow_array("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber = $biblionumber")
100 or BAIL_OUT("Cannot find newly created biblioitems record");
102 my $items_insert_sth = $dbh->prepare("INSERT INTO items (biblionumber, biblioitemnumber, barcode, homebranch, holdingbranch, notforloan, damaged, itemlost, withdrawn, onloan, itype)
103 VALUES ($biblionumber, $biblioitemnumber, ?, ?, ?, 0, 0, 0, 0, NULL, '$itemtype')"); # CURRENT_DATE - 3)");
104 my $first_barcode = int(rand(1000000000000)); # XXX
105 my $barcode = $first_barcode;
106 foreach ( $borrower_branchcode, $least_cost_branch_code, @other_branches ) {
107 $items_insert_sth->execute($barcode++, $borrower_branchcode, $_);
108 $items_insert_sth->execute($barcode++, $_, $_);
109 $items_insert_sth->execute($barcode++, $_, $borrower_branchcode);
112 # Remove existing reserves, makes debugging easier
113 $dbh->do("DELETE FROM reserves");
114 my $bibitems = undef;
119 branchcode => $borrower_branchcode,
120 borrowernumber => $borrowernumber,
121 biblionumber => $biblionumber,
122 priority => $priority,
125 # $resdate, $expdate, $notes, $title, $checkitem, $found
126 $dbh->do("UPDATE reserves SET reservedate = DATE_SUB( reservedate, INTERVAL 1 DAY )");
129 my $use_cost_matrix_sth = $dbh->prepare("UPDATE systempreferences SET value = ? WHERE variable = 'UseTransportCostMatrix'");
130 my $test_sth = $dbh->prepare("SELECT * FROM hold_fill_targets
131 JOIN tmp_holdsqueue USING (borrowernumber, biblionumber, itemnumber)
132 JOIN items USING (itemnumber)
133 WHERE borrowernumber = $borrowernumber");
135 # We have a book available homed in borrower branch, no point fiddling with AutomaticItemReturn
136 t::lib::Mocks::mock_preference('AutomaticItemReturn', 0);
137 test_queue ('take from homebranch', 0, $borrower_branchcode, $borrower_branchcode);
138 test_queue ('take from homebranch', 1, $borrower_branchcode, $borrower_branchcode);
140 $dbh->do("DELETE FROM tmp_holdsqueue");
141 $dbh->do("DELETE FROM hold_fill_targets");
142 $dbh->do("DELETE FROM issues WHERE itemnumber IN (SELECT itemnumber FROM items WHERE homebranch = '$borrower_branchcode' AND holdingbranch = '$borrower_branchcode')");
143 $dbh->do("DELETE FROM items WHERE homebranch = '$borrower_branchcode' AND holdingbranch = '$borrower_branchcode'");
144 # test_queue will flush
145 t::lib::Mocks::mock_preference('AutomaticItemReturn', 1);
146 # Not sure how to make this test more difficult - holding branch does not matter
148 $dbh->do("DELETE FROM tmp_holdsqueue");
149 $dbh->do("DELETE FROM hold_fill_targets");
150 $dbh->do("DELETE FROM issues WHERE itemnumber IN (SELECT itemnumber FROM items WHERE homebranch = '$borrower_branchcode')");
151 $dbh->do("DELETE FROM items WHERE homebranch = '$borrower_branchcode'");
152 t::lib::Mocks::mock_preference('AutomaticItemReturn', 0);
153 # We have a book available held in borrower branch
154 test_queue ('take from holdingbranch', 0, $borrower_branchcode, $borrower_branchcode);
155 test_queue ('take from holdingbranch', 1, $borrower_branchcode, $borrower_branchcode);
157 $dbh->do("DELETE FROM tmp_holdsqueue");
158 $dbh->do("DELETE FROM hold_fill_targets");
159 $dbh->do("DELETE FROM issues WHERE itemnumber IN (SELECT itemnumber FROM items WHERE holdingbranch = '$borrower_branchcode')");
160 $dbh->do("DELETE FROM items WHERE holdingbranch = '$borrower_branchcode'");
161 # No book available in borrower branch, pick according to the rules
162 # Frst branch from StaticHoldsQueueWeight
163 test_queue ('take from lowest cost branch', 0, $borrower_branchcode, $other_branches[0]);
164 test_queue ('take from lowest cost branch', 1, $borrower_branchcode, $least_cost_branch_code);
165 my $queue = C4::HoldsQueue::GetHoldsQueueItems($least_cost_branch_code) || [];
166 my $queue_item = $queue->[0];
168 && $queue_item->{pickbranch} eq $borrower_branchcode
169 && $queue_item->{holdingbranch} eq $least_cost_branch_code, "GetHoldsQueueItems" )
170 or diag( "Expected item for pick $borrower_branchcode, hold $least_cost_branch_code, got ".Dumper($queue_item) );
171 ok( exists($queue_item->{itype}), 'item type included in queued items list (bug 5825)' );
174 C4::HoldsQueue::least_cost_branch( 'B', [ 'A', 'B', 'C' ] ) eq 'B',
175 'C4::HoldsQueue::least_cost_branch returns the local branch if it is in the list of branches to pull from'
178 # XXX All this tests are for borrower branch pick-up.
179 # Maybe needs expanding to homebranch or holdingbranch pick-up.
181 $schema->txn_rollback;
184 ### Test holds queue builder does not violate holds policy ###
186 # Clear out existing rules relating to holdallowed
187 $dbh->do("DELETE FROM circulation_rules");
189 t::lib::Mocks::mock_preference('UseTransportCostMatrix', 0);
191 $itemtype = $builder->build({ source => 'Itemtype', value => { notforloan => 0 } })->{itemtype};
193 $library1 = $builder->build({
196 $library2 = $builder->build({
199 $library3 = $builder->build({
202 @branchcodes = ( $library1->{branchcode}, $library2->{branchcode}, $library3->{branchcode} );
204 my $borrower1 = $builder->build({
205 source => 'Borrower',
207 branchcode => $branchcodes[0],
210 my $borrower2 = $builder->build({
211 source => 'Borrower',
213 branchcode => $branchcodes[1],
216 my $borrower3 = $builder->build({
217 source => 'Borrower',
219 branchcode => $branchcodes[2],
236 $biblionumber = $dbh->selectrow_array("SELECT biblionumber FROM biblio WHERE title = '$TITLE'")
237 or BAIL_OUT("Cannot find newly created biblio record");
240 INSERT INTO biblioitems (
248 $biblioitemnumber = $dbh->selectrow_array("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber = $biblionumber")
249 or BAIL_OUT("Cannot find newly created biblioitems record");
251 $items_insert_sth = $dbh->prepare(qq{
278 # Create 3 items from 2 branches ( branches are for borrowers 1 and 2 respectively )
279 $barcode = int( rand(1000000000000) );
280 $items_insert_sth->execute( $barcode + 0, $branchcodes[0], $branchcodes[0] );
281 $items_insert_sth->execute( $barcode + 1, $branchcodes[1], $branchcodes[1] );
282 $items_insert_sth->execute( $barcode + 2, $branchcodes[1], $branchcodes[1] );
284 $dbh->do("DELETE FROM reserves");
285 my $sth = $dbh->prepare(q{
286 INSERT INTO reserves (
292 ) VALUES ( ?,?,?,?, CURRENT_DATE() )
294 $sth->execute( $borrower1->{borrowernumber}, $biblionumber, $branchcodes[0], 1 );
295 $sth->execute( $borrower2->{borrowernumber}, $biblionumber, $branchcodes[0], 2 );
296 $sth->execute( $borrower3->{borrowernumber}, $biblionumber, $branchcodes[0], 3 );
300 $dbh->do("DELETE FROM circulation_rules");
301 Koha::CirculationRules->set_rule(
303 rule_name => 'holdallowed',
309 C4::HoldsQueue::CreateQueue();
310 $holds_queue = $dbh->selectall_arrayref("SELECT * FROM tmp_holdsqueue", { Slice => {} });
311 is( @$holds_queue, 2, "Holds queue filling correct number for default holds policy 'from home library'" );
312 is( $holds_queue->[0]->{cardnumber}, $borrower1->{cardnumber}, "Holds queue filling 1st correct hold for default holds policy 'from home library'");
313 is( $holds_queue->[1]->{cardnumber}, $borrower2->{cardnumber}, "Holds queue filling 2nd correct hold for default holds policy 'from home library'");
315 # Test skipping hold picks for closed libraries.
316 # At this point in the test, we have 2 rows in the holds queue
317 # 1 of which is coming from MPL. Let's enable HoldsQueueSkipClosed
318 # and make today a holiday for MPL. When we run it again we should only
319 # have 1 row in the holds queue
320 t::lib::Mocks::mock_preference('HoldsQueueSkipClosed', 1);
321 my $today = dt_from_string();
322 C4::Calendar->new( branchcode => $branchcodes[0] )->insert_single_holiday(
323 day => $today->day(),
324 month => $today->month(),
325 year => $today->year(),
327 description => "$today",
329 # If the test below is removed, aother tests using the holiday will fail. For some reason if we call is_holiday now
330 # the holiday will get set in cache correctly, but not if we let C4::HoldsQueue call is_holiday instead.
331 is( Koha::Calendar->new( branchcode => $branchcodes[0] )->is_holiday( $today ), 1, 'Is today a holiday for pickup branch' );
332 C4::HoldsQueue::CreateQueue();
333 $holds_queue = $dbh->selectall_arrayref("SELECT * FROM tmp_holdsqueue", { Slice => {} });
334 is( scalar( @$holds_queue ), 1, "Holds not filled with items from closed libraries" );
335 t::lib::Mocks::mock_preference('HoldsQueueSkipClosed', 0);
337 $dbh->do("DELETE FROM circulation_rules");
338 Koha::CirculationRules->set_rule(
340 rule_name => 'holdallowed',
346 C4::HoldsQueue::CreateQueue();
347 $holds_queue = $dbh->selectall_arrayref("SELECT * FROM tmp_holdsqueue", { Slice => {} });
348 is( @$holds_queue, 3, "Holds queue filling correct number for holds for default holds policy 'from any library'" );
350 # Test skipping hold picks for closed libraries without transport cost matrix
351 # At this point in the test, we have 3 rows in the holds queue
352 # one of which is coming from MPL. Let's enable HoldsQueueSkipClosed
353 # and use our previously created holiday for MPL
354 # When we run it again we should only have 2 rows in the holds queue
355 t::lib::Mocks::mock_preference( 'HoldsQueueSkipClosed', 1 );
356 C4::HoldsQueue::CreateQueue();
357 $holds_queue = $dbh->selectall_arrayref("SELECT * FROM tmp_holdsqueue", { Slice => {} });
358 is( scalar( @$holds_queue ), 2, "Holds not filled with items from closed libraries" );
359 t::lib::Mocks::mock_preference( 'HoldsQueueSkipClosed', 0 );
361 ## Test LocalHoldsPriority
362 t::lib::Mocks::mock_preference('LocalHoldsPriority', 1);
364 $dbh->do("DELETE FROM circulation_rules");
365 Koha::CirculationRules->set_rule(
367 rule_name => 'holdallowed',
373 $dbh->do("DELETE FROM issues");
375 # Test homebranch = patron branch
376 t::lib::Mocks::mock_preference('LocalHoldsPriorityPatronControl', 'HomeLibrary');
377 t::lib::Mocks::mock_preference('LocalHoldsPriorityItemControl', 'homebranch');
378 C4::Context->clear_syspref_cache();
379 $dbh->do("DELETE FROM reserves");
380 $sth->execute( $borrower1->{borrowernumber}, $biblionumber, $branchcodes[0], 1 );
381 $sth->execute( $borrower2->{borrowernumber}, $biblionumber, $branchcodes[0], 2 );
382 $sth->execute( $borrower3->{borrowernumber}, $biblionumber, $branchcodes[0], 3 );
384 $dbh->do("DELETE FROM items");
385 # barcode, homebranch, holdingbranch, itemtype
386 $items_insert_sth->execute( $barcode + 4, $branchcodes[2], $branchcodes[0] );
388 C4::HoldsQueue::CreateQueue();
389 $holds_queue = $dbh->selectall_arrayref("SELECT * FROM tmp_holdsqueue", { Slice => {} });
390 is( $holds_queue->[0]->{cardnumber}, $borrower3->{cardnumber}, "Holds queue giving priority to patron who's home library matches item's home library");
392 ### Test branch transfer limits ###
393 t::lib::Mocks::mock_preference('LocalHoldsPriorityPatronControl', 'HomeLibrary');
394 t::lib::Mocks::mock_preference('LocalHoldsPriorityItemControl', 'holdingbranch');
395 t::lib::Mocks::mock_preference( 'UseBranchTransferLimits', '1' );
396 C4::Context->clear_syspref_cache();
397 $dbh->do("DELETE FROM reserves");
398 $sth->execute( $borrower1->{borrowernumber}, $biblionumber, $branchcodes[0], 1 );
399 $sth->execute( $borrower2->{borrowernumber}, $biblionumber, $branchcodes[1], 2 );
401 $dbh->do("DELETE FROM items");
402 # barcode, homebranch, holdingbranch, itemtype
403 $items_insert_sth->execute( $barcode, $branchcodes[2], $branchcodes[2] );
404 my $item = Koha::Items->find( { barcode => $barcode } );
406 my $limit1 = Koha::Item::Transfer::Limit->new(
408 toBranch => $branchcodes[0],
409 fromBranch => $branchcodes[2],
410 itemtype => $item->effective_itemtype,
414 C4::HoldsQueue::CreateQueue();
415 $holds_queue = $dbh->selectall_arrayref("SELECT * FROM tmp_holdsqueue", { Slice => {} });
416 is( $holds_queue->[0]->{cardnumber}, $borrower2->{cardnumber}, "Holds queue skips hold transfer that would violate branch transfer limits");
418 my $limit2 = Koha::Item::Transfer::Limit->new(
420 toBranch => $branchcodes[1],
421 fromBranch => $branchcodes[2],
422 itemtype => $item->effective_itemtype,
426 C4::HoldsQueue::CreateQueue();
427 $holds_queue = $dbh->selectall_arrayref("SELECT * FROM tmp_holdsqueue", { Slice => {} });
428 is( $holds_queue->[0]->{cardnumber}, undef, "Holds queue doesn't fill hold where all available items would violate branch transfer limits");
432 t::lib::Mocks::mock_preference( 'UseBranchTransferLimits', '0' );
433 ### END Test branch transfer limits ###
435 # Test holdingbranch = patron branch
436 t::lib::Mocks::mock_preference('LocalHoldsPriorityPatronControl', 'HomeLibrary');
437 t::lib::Mocks::mock_preference('LocalHoldsPriorityItemControl', 'holdingbranch');
438 C4::Context->clear_syspref_cache();
439 $dbh->do("DELETE FROM reserves");
440 $sth->execute( $borrower1->{borrowernumber}, $biblionumber, $branchcodes[0], 1 );
441 $sth->execute( $borrower2->{borrowernumber}, $biblionumber, $branchcodes[0], 2 );
442 $sth->execute( $borrower3->{borrowernumber}, $biblionumber, $branchcodes[0], 3 );
444 $dbh->do("DELETE FROM items");
445 # barcode, homebranch, holdingbranch, itemtype
446 $items_insert_sth->execute( $barcode + 4, $branchcodes[0], $branchcodes[2] );
448 C4::HoldsQueue::CreateQueue();
449 $holds_queue = $dbh->selectall_arrayref("SELECT * FROM tmp_holdsqueue", { Slice => {} });
450 is( $holds_queue->[0]->{cardnumber}, $borrower3->{cardnumber}, "Holds queue giving priority to patron who's home library matches item's holding library");
452 # Test holdingbranch = pickup branch
453 t::lib::Mocks::mock_preference('LocalHoldsPriorityPatronControl', 'PickupLibrary');
454 t::lib::Mocks::mock_preference('LocalHoldsPriorityItemControl', 'holdingbranch');
455 C4::Context->clear_syspref_cache();
456 $dbh->do("DELETE FROM reserves");
457 $sth->execute( $borrower1->{borrowernumber}, $biblionumber, $branchcodes[0], 1 );
458 $sth->execute( $borrower2->{borrowernumber}, $biblionumber, $branchcodes[0], 2 );
459 $sth->execute( $borrower3->{borrowernumber}, $biblionumber, $branchcodes[2], 3 );
461 $dbh->do("DELETE FROM items");
462 # barcode, homebranch, holdingbranch, itemtype
463 $items_insert_sth->execute( $barcode + 4, $branchcodes[0], $branchcodes[2] );
465 C4::HoldsQueue::CreateQueue();
466 $holds_queue = $dbh->selectall_arrayref("SELECT * FROM tmp_holdsqueue", { Slice => {} });
467 is( $holds_queue->[0]->{cardnumber}, $borrower3->{cardnumber}, "Holds queue giving priority to patron who's home library matches item's holding library");
469 # Test homebranch = pickup branch
470 t::lib::Mocks::mock_preference('LocalHoldsPriorityPatronControl', 'PickupLibrary');
471 t::lib::Mocks::mock_preference('LocalHoldsPriorityItemControl', 'homebranch');
472 C4::Context->clear_syspref_cache();
473 $dbh->do("DELETE FROM reserves");
474 $sth->execute( $borrower1->{borrowernumber}, $biblionumber, $branchcodes[0], 1 );
475 $sth->execute( $borrower2->{borrowernumber}, $biblionumber, $branchcodes[0], 2 );
476 $sth->execute( $borrower3->{borrowernumber}, $biblionumber, $branchcodes[2], 3 );
478 $dbh->do("DELETE FROM items");
479 # barcode, homebranch, holdingbranch, itemtype
480 $items_insert_sth->execute( $barcode + 4, $branchcodes[2], $branchcodes[0] );
482 C4::HoldsQueue::CreateQueue();
483 $holds_queue = $dbh->selectall_arrayref("SELECT * FROM tmp_holdsqueue", { Slice => {} });
484 is( $holds_queue->[0]->{cardnumber}, $borrower3->{cardnumber}, "Holds queue giving priority to patron who's home library matches item's holding library");
486 t::lib::Mocks::mock_preference('LocalHoldsPriority', 0);
487 ## End testing of LocalHoldsPriority
491 $itemtype = $builder->build({ source => 'Itemtype', value => { notforloan => 0 } })->{itemtype};
492 $borrowernumber = $borrower3->{borrowernumber};
493 my $library_A = $library1->{branchcode};
494 my $library_B = $library2->{branchcode};
495 my $library_C = $borrower3->{branchcode};
496 $dbh->do("DELETE FROM reserves");
497 $dbh->do("DELETE FROM issues");
498 $dbh->do("DELETE FROM items");
499 $dbh->do("DELETE FROM biblio");
500 $dbh->do("DELETE FROM biblioitems");
501 $dbh->do("DELETE FROM transport_cost");
502 $dbh->do("DELETE FROM tmp_holdsqueue");
503 $dbh->do("DELETE FROM hold_fill_targets");
506 INSERT INTO biblio (frameworkcode, author, title, datecreated) VALUES ('', 'Koha test', '$TITLE', '2011-02-01')
509 $biblionumber = $dbh->selectrow_array("SELECT biblionumber FROM biblio WHERE title = '$TITLE'")
510 or BAIL_OUT("Cannot find newly created biblio record");
512 $dbh->do("INSERT INTO biblioitems (biblionumber, itemtype) VALUES ($biblionumber, '$itemtype')");
515 $dbh->selectrow_array("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber = $biblionumber")
516 or BAIL_OUT("Cannot find newly created biblioitems record");
519 INSERT INTO items (biblionumber, biblioitemnumber, homebranch, holdingbranch, notforloan, damaged, itemlost, withdrawn, onloan, itype)
520 VALUES ($biblionumber, $biblioitemnumber, '$library_A', '$library_A', 0, 0, 0, 0, NULL, '$itemtype')
524 INSERT INTO items (biblionumber, biblioitemnumber, homebranch, holdingbranch, notforloan, damaged, itemlost, withdrawn, onloan, itype)
525 VALUES ($biblionumber, $biblioitemnumber, '$library_B', '$library_B', 0, 0, 0, 0, NULL, '$itemtype')
528 Koha::CirculationRules->set_rules(
530 branchcode => $library_A,
531 itemtype => $itemtype,
534 returnbranch => 'homebranch',
539 $dbh->do( "UPDATE systempreferences SET value = ? WHERE variable = 'StaticHoldsQueueWeight'",
540 undef, join( ',', $library_B, $library_A, $library_C ) );
541 $dbh->do( "UPDATE systempreferences SET value = 0 WHERE variable = 'RandomizeHoldsQueueWeight'" );
543 my $reserve_id = AddReserve(
545 branchcode => $library_C,
546 borrowernumber => $borrowernumber,
547 biblionumber => $biblionumber,
551 C4::HoldsQueue::CreateQueue();
552 $holds_queue = $dbh->selectall_arrayref("SELECT * FROM tmp_holdsqueue", { Slice => {} });
553 is( @$holds_queue, 1, "Bug 14297 - Holds Queue building ignoring holds where pickup & home branch don't match and item is not from le");
557 $itemtype = $builder->build({ source => 'Itemtype', value => { notforloan => 0 } })->{itemtype};
558 $borrowernumber = $borrower2->{borrowernumber};
559 $library_A = $library1->{branchcode};
560 $library_B = $library2->{branchcode};
561 $dbh->do("DELETE FROM reserves");
562 $dbh->do("DELETE FROM issues");
563 $dbh->do("DELETE FROM items");
564 $dbh->do("DELETE FROM biblio");
565 $dbh->do("DELETE FROM biblioitems");
566 $dbh->do("DELETE FROM transport_cost");
567 $dbh->do("DELETE FROM tmp_holdsqueue");
568 $dbh->do("DELETE FROM hold_fill_targets");
570 t::lib::Mocks::mock_preference("UseTransportCostMatrix",1);
572 my $tc_rs = $schema->resultset('TransportCost');
573 $tc_rs->create({ frombranch => $library_A, tobranch => $library_B, cost => 0, disable_transfer => 1 });
574 $tc_rs->create({ frombranch => $library_B, tobranch => $library_A, cost => 0, disable_transfer => 1 });
577 INSERT INTO biblio (frameworkcode, author, title, datecreated) VALUES ('', 'Koha test', '$TITLE', '2011-02-01')
580 $biblionumber = $dbh->selectrow_array("SELECT biblionumber FROM biblio WHERE title = '$TITLE'")
581 or BAIL_OUT("Cannot find newly created biblio record");
583 $dbh->do("INSERT INTO biblioitems (biblionumber, itemtype) VALUES ($biblionumber, '$itemtype')");
586 $dbh->selectrow_array("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber = $biblionumber")
587 or BAIL_OUT("Cannot find newly created biblioitems record");
590 INSERT INTO items (biblionumber, biblioitemnumber, homebranch, holdingbranch, notforloan, damaged, itemlost, withdrawn, onloan, itype)
591 VALUES ($biblionumber, $biblioitemnumber, '$library_A', '$library_A', 0, 0, 0, 0, NULL, '$itemtype')
594 $reserve_id = AddReserve(
596 branchcode => $library_B,
597 borrowernumber => $borrowernumber,
598 biblionumber => $biblionumber,
603 C4::HoldsQueue::CreateQueue();
604 $holds_queue = $dbh->selectall_arrayref("SELECT * FROM tmp_holdsqueue", { Slice => {} });
605 is( @$holds_queue, 0, "Bug 15062 - Holds queue with Transport Cost Matrix will transfer item even if transfers disabled");
608 # Test hold_fulfillment_policy
609 t::lib::Mocks::mock_preference( "UseTransportCostMatrix", 0 );
610 $borrowernumber = $borrower3->{borrowernumber};
611 $library_A = $library1->{branchcode};
612 $library_B = $library2->{branchcode};
613 $library_C = $library3->{branchcode};
614 $dbh->do("DELETE FROM reserves");
615 $dbh->do("DELETE FROM issues");
616 $dbh->do("DELETE FROM items");
617 $dbh->do("DELETE FROM biblio");
618 $dbh->do("DELETE FROM biblioitems");
619 $dbh->do("DELETE FROM transport_cost");
620 $dbh->do("DELETE FROM tmp_holdsqueue");
621 $dbh->do("DELETE FROM hold_fill_targets");
623 $dbh->do("INSERT INTO biblio (frameworkcode, author, title, datecreated) VALUES ('', 'Koha test', '$TITLE', '2011-02-01')");
625 $biblionumber = $dbh->selectrow_array("SELECT biblionumber FROM biblio WHERE title = '$TITLE'")
626 or BAIL_OUT("Cannot find newly created biblio record");
628 $dbh->do("INSERT INTO biblioitems (biblionumber, itemtype) VALUES ($biblionumber, '$itemtype')");
631 $dbh->selectrow_array("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber = $biblionumber")
632 or BAIL_OUT("Cannot find newly created biblioitems record");
635 INSERT INTO items (biblionumber, biblioitemnumber, homebranch, holdingbranch, notforloan, damaged, itemlost, withdrawn, onloan, itype)
636 VALUES ($biblionumber, $biblioitemnumber, '$library_A', '$library_B', 0, 0, 0, 0, NULL, '$itemtype')
639 # With hold_fulfillment_policy = homebranch, hold should only be picked up if pickup branch = homebranch
640 $dbh->do("DELETE FROM circulation_rules");
641 Koha::CirculationRules->set_rules(
647 hold_fulfillment_policy => 'homebranch',
652 # Home branch matches pickup branch
653 $reserve_id = AddReserve(
655 branchcode => $library_A,
656 borrowernumber => $borrowernumber,
657 biblionumber => $biblionumber,
662 C4::HoldsQueue::CreateQueue();
663 $holds_queue = $dbh->selectall_arrayref( "SELECT * FROM tmp_holdsqueue", { Slice => {} } );
664 is( @$holds_queue, 1, "Hold where pickup branch matches home branch targeted" );
665 Koha::Holds->find( $reserve_id )->cancel;
667 # Holding branch matches pickup branch
668 $reserve_id = AddReserve(
670 branchcode => $library_B,
671 borrowernumber => $borrowernumber,
672 biblionumber => $biblionumber,
678 C4::HoldsQueue::CreateQueue();
679 $holds_queue = $dbh->selectall_arrayref( "SELECT * FROM tmp_holdsqueue", { Slice => {} } );
680 is( @$holds_queue, 0, "Hold where pickup ne home, pickup eq home not targeted" );
681 Koha::Holds->find( $reserve_id )->cancel;
683 # Neither branch matches pickup branch
684 $reserve_id = AddReserve(
686 branchcode => $library_C,
687 borrowernumber => $borrowernumber,
688 biblionumber => $biblionumber,
693 C4::HoldsQueue::CreateQueue();
694 $holds_queue = $dbh->selectall_arrayref( "SELECT * FROM tmp_holdsqueue", { Slice => {} } );
695 is( @$holds_queue, 0, "Hold where pickup ne home, pickup ne holding not targeted" );
696 Koha::Holds->find( $reserve_id )->cancel;
698 # With hold_fulfillment_policy = holdingbranch, hold should only be picked up if pickup branch = holdingbranch
699 $dbh->do("DELETE FROM circulation_rules");
700 Koha::CirculationRules->set_rules(
706 hold_fulfillment_policy => 'holdingbranch',
711 # Home branch matches pickup branch
712 $reserve_id = AddReserve(
714 branchcode => $library_A,
715 borrowernumber => $borrowernumber,
716 biblionumber => $biblionumber,
721 C4::HoldsQueue::CreateQueue();
722 $holds_queue = $dbh->selectall_arrayref( "SELECT * FROM tmp_holdsqueue", { Slice => {} } );
723 is( @$holds_queue, 0, "Hold where pickup eq home, pickup ne holding not targeted" );
724 Koha::Holds->find( $reserve_id )->cancel;
726 # Holding branch matches pickup branch
727 $reserve_id = AddReserve(
729 branchcode => $library_B,
730 borrowernumber => $borrowernumber,
731 biblionumber => $biblionumber,
736 C4::HoldsQueue::CreateQueue();
737 $holds_queue = $dbh->selectall_arrayref( "SELECT * FROM tmp_holdsqueue", { Slice => {} } );
738 is( @$holds_queue, 1, "Hold where pickup ne home, pickup eq holding targeted" );
739 Koha::Holds->find( $reserve_id )->cancel;
741 # Neither branch matches pickup branch
742 $reserve_id = AddReserve(
744 branchcode => $library_C,
745 borrowernumber => $borrowernumber,
746 biblionumber => $biblionumber,
751 C4::HoldsQueue::CreateQueue();
752 $holds_queue = $dbh->selectall_arrayref( "SELECT * FROM tmp_holdsqueue", { Slice => {} } );
753 is( @$holds_queue, 0, "Hold where pickup ne home, pickup ne holding not targeted" );
754 Koha::Holds->find( $reserve_id )->cancel;
756 # With hold_fulfillment_policy = any, hold should be pikcup up reguardless of matching home or holding branch
757 $dbh->do("DELETE FROM circulation_rules");
758 Koha::CirculationRules->set_rules(
764 hold_fulfillment_policy => 'any',
769 # Home branch matches pickup branch
770 $reserve_id = AddReserve(
772 branchcode => $library_A,
773 borrowernumber => $borrowernumber,
774 biblionumber => $biblionumber,
779 C4::HoldsQueue::CreateQueue();
780 $holds_queue = $dbh->selectall_arrayref( "SELECT * FROM tmp_holdsqueue", { Slice => {} } );
781 is( @$holds_queue, 1, "Hold where pickup eq home, pickup ne holding targeted" );
782 Koha::Holds->find( $reserve_id )->cancel;
784 # Holding branch matches pickup branch
785 $reserve_id = AddReserve(
787 branchcode => $library_B,
788 borrowernumber => $borrowernumber,
789 biblionumber => $biblionumber,
794 C4::HoldsQueue::CreateQueue();
795 $holds_queue = $dbh->selectall_arrayref( "SELECT * FROM tmp_holdsqueue", { Slice => {} } );
796 is( @$holds_queue, 1, "Hold where pickup ne home, pickup eq holding targeted" );
797 Koha::Holds->find( $reserve_id )->cancel;
799 # Neither branch matches pickup branch
800 $reserve_id = AddReserve(
802 branchcode => $library_C,
803 borrowernumber => $borrowernumber,
804 biblionumber => $biblionumber,
809 C4::HoldsQueue::CreateQueue();
810 $holds_queue = $dbh->selectall_arrayref( "SELECT * FROM tmp_holdsqueue", { Slice => {} } );
811 is( @$holds_queue, 1, "Hold where pickup ne home, pickup ne holding targeted" );
812 Koha::Holds->find( $reserve_id )->cancel;
814 # End testing hold_fulfillment_policy
816 # Test hold itemtype limit
817 t::lib::Mocks::mock_preference( "UseTransportCostMatrix", 0 );
818 my $wrong_itemtype = $builder->build({ source => 'Itemtype', value => { notforloan => 0 } })->{itemtype};
819 my $right_itemtype = $builder->build({ source => 'Itemtype', value => { notforloan => 0 } })->{itemtype};
820 $borrowernumber = $borrower3->{borrowernumber};
821 my $branchcode = $library1->{branchcode};
822 $dbh->do("DELETE FROM reserves");
823 $dbh->do("DELETE FROM issues");
824 $dbh->do("DELETE FROM items");
825 $dbh->do("DELETE FROM biblio");
826 $dbh->do("DELETE FROM biblioitems");
827 $dbh->do("DELETE FROM transport_cost");
828 $dbh->do("DELETE FROM tmp_holdsqueue");
829 $dbh->do("DELETE FROM hold_fill_targets");
831 $dbh->do("INSERT INTO biblio (frameworkcode, author, title, datecreated) VALUES ('', 'Koha test', '$TITLE', '2011-02-01')");
833 $biblionumber = $dbh->selectrow_array("SELECT biblionumber FROM biblio WHERE title = '$TITLE'")
834 or BAIL_OUT("Cannot find newly created biblio record");
836 $dbh->do("INSERT INTO biblioitems (biblionumber, itemtype) VALUES ($biblionumber, '$itemtype')");
839 $dbh->selectrow_array("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber = $biblionumber")
840 or BAIL_OUT("Cannot find newly created biblioitems record");
843 INSERT INTO items (biblionumber, biblioitemnumber, homebranch, holdingbranch, notforloan, damaged, itemlost, withdrawn, onloan, itype)
844 VALUES ($biblionumber, $biblioitemnumber, '$library_A', '$library_B', 0, 0, 0, 0, NULL, '$right_itemtype')
847 # With hold_fulfillment_policy = homebranch, hold should only be picked up if pickup branch = homebranch
848 $dbh->do("DELETE FROM circulation_rules");
849 Koha::CirculationRules->set_rules(
855 hold_fulfillment_policy => 'any',
860 # Home branch matches pickup branch
861 $reserve_id = AddReserve(
863 branchcode => $library_A,
864 borrowernumber => $borrowernumber,
865 biblionumber => $biblionumber,
867 itemtype => $wrong_itemtype,
871 C4::HoldsQueue::CreateQueue();
872 $holds_queue = $dbh->selectall_arrayref( "SELECT * FROM tmp_holdsqueue", { Slice => {} } );
873 is( @$holds_queue, 0, "Item with incorrect itemtype not targeted" );
874 Koha::Holds->find( $reserve_id )->cancel;
876 # Holding branch matches pickup branch
877 $reserve_id = AddReserve(
879 branchcode => $library_A,
880 borrowernumber => $borrowernumber,
881 biblionumber => $biblionumber,
883 itemtype => $right_itemtype,
887 C4::HoldsQueue::CreateQueue();
888 $holds_queue = $dbh->selectall_arrayref( "SELECT * FROM tmp_holdsqueue", { Slice => {} } );
889 is( @$holds_queue, 1, "Item with matching itemtype is targeted" );
890 Koha::Holds->find( $reserve_id )->cancel;
892 # Neither branch matches pickup branch
893 $reserve_id = AddReserve(
895 branchcode => $library_A,
896 borrowernumber => $borrowernumber,
897 biblionumber => $biblionumber,
902 C4::HoldsQueue::CreateQueue();
903 $holds_queue = $dbh->selectall_arrayref( "SELECT * FROM tmp_holdsqueue", { Slice => {} } );
904 is( @$holds_queue, 1, "Item targeted when hold itemtype is not set" );
905 Koha::Holds->find( $reserve_id )->cancel;
907 # End testing hold itemtype limit
910 subtest "Test Local Holds Priority - Bib level" => sub {
913 Koha::Biblios->delete();
914 t::lib::Mocks::mock_preference( 'LocalHoldsPriority', 1 );
915 t::lib::Mocks::mock_preference( 'LocalHoldsPriorityPatronControl', 'PickupLibrary' );
916 t::lib::Mocks::mock_preference( 'LocalHoldsPriorityItemControl', 'homebranch' );
917 my $branch = $builder->build_object( { class => 'Koha::Libraries' } );
918 my $branch2 = $builder->build_object( { class => 'Koha::Libraries' } );
919 my $local_patron = $builder->build_object(
921 class => "Koha::Patrons",
923 branchcode => $branch->branchcode
927 my $other_patron = $builder->build_object(
929 class => "Koha::Patrons",
931 branchcode => $branch2->branchcode
935 my $biblio = $builder->build_sample_biblio();
936 my $item = $builder->build_sample_item(
938 biblionumber => $biblio->biblionumber,
939 library => $branch->branchcode,
943 my $reserve_id = AddReserve(
945 branchcode => $branch2->branchcode,
946 borrowernumber => $other_patron->borrowernumber,
947 biblionumber => $biblio->biblionumber,
951 my $reserve_id2 = AddReserve(
953 branchcode => $item->homebranch,
954 borrowernumber => $local_patron->borrowernumber,
955 biblionumber => $biblio->biblionumber,
960 C4::HoldsQueue::CreateQueue();
962 my $queue_rs = $schema->resultset('TmpHoldsqueue');
963 is( $queue_rs->count(), 1,
964 "Hold queue contains one hold" );
966 $queue_rs->next->borrowernumber,
967 $local_patron->borrowernumber,
968 "We should pick the local hold over the next available"
972 subtest "Test Local Holds Priority - Item level" => sub {
975 Koha::Biblios->delete();
976 t::lib::Mocks::mock_preference( 'LocalHoldsPriority', 1 );
977 t::lib::Mocks::mock_preference( 'LocalHoldsPriorityPatronControl', 'PickupLibrary' );
978 t::lib::Mocks::mock_preference( 'LocalHoldsPriorityItemControl', 'homebranch' );
979 my $branch = $builder->build_object( { class => 'Koha::Libraries' } );
980 my $branch2 = $builder->build_object( { class => 'Koha::Libraries' } );
981 my $local_patron = $builder->build_object(
983 class => "Koha::Patrons",
985 branchcode => $branch->branchcode
989 my $other_patron = $builder->build_object(
991 class => "Koha::Patrons",
993 branchcode => $branch2->branchcode
997 my $biblio = $builder->build_sample_biblio();
998 my $item = $builder->build_sample_item(
1000 biblionumber => $biblio->biblionumber,
1001 library => $branch->branchcode,
1005 my $reserve_id = AddReserve(
1007 branchcode => $branch2->branchcode,
1008 borrowernumber => $other_patron->borrowernumber,
1009 biblionumber => $biblio->biblionumber,
1011 itemnumber => $item->id,
1014 my $reserve_id2 = AddReserve(
1016 branchcode => $item->homebranch,
1017 borrowernumber => $local_patron->borrowernumber,
1018 biblionumber => $biblio->biblionumber,
1020 itemnumber => $item->id,
1024 C4::HoldsQueue::CreateQueue();
1026 my $queue_rs = $schema->resultset('TmpHoldsqueue');
1027 my $q = $queue_rs->next;
1028 is( $queue_rs->count(), 1,
1029 "Hold queue contains one hold" );
1032 $local_patron->borrowernumber,
1033 "We should pick the local hold over the next available"
1037 subtest "Test Local Holds Priority - Item level hold over Record level hold (Bug 23934)" => sub {
1040 Koha::Biblios->delete();
1041 t::lib::Mocks::mock_preference( 'LocalHoldsPriority', 1 );
1042 t::lib::Mocks::mock_preference( 'LocalHoldsPriorityPatronControl', 'PickupLibrary' );
1043 t::lib::Mocks::mock_preference( 'LocalHoldsPriorityItemControl', 'homebranch' );
1044 my $branch = $builder->build_object( { class => 'Koha::Libraries' } );
1045 my $branch2 = $builder->build_object( { class => 'Koha::Libraries' } );
1046 my $local_patron = $builder->build_object(
1048 class => "Koha::Patrons",
1050 branchcode => $branch->branchcode
1054 my $other_patron = $builder->build_object(
1056 class => "Koha::Patrons",
1058 branchcode => $branch2->branchcode
1062 my $biblio = $builder->build_sample_biblio();
1063 my $item = $builder->build_sample_item(
1065 biblionumber => $biblio->biblionumber,
1066 library => $branch->branchcode,
1070 my $reserve_id = AddReserve(
1072 branchcode => $branch2->branchcode,
1073 borrowernumber => $other_patron->borrowernumber,
1074 biblionumber => $biblio->biblionumber,
1078 my $reserve_id2 = AddReserve(
1080 branchcode => $item->homebranch,
1081 borrowernumber => $local_patron->borrowernumber,
1082 biblionumber => $biblio->biblionumber,
1084 itemnumber => $item->id,
1088 C4::HoldsQueue::CreateQueue();
1090 my $queue_rs = $schema->resultset('TmpHoldsqueue');
1091 my $q = $queue_rs->next;
1092 is( $queue_rs->count(), 1,
1093 "Hold queue contains one hold" );
1096 $local_patron->borrowernumber,
1097 "We should pick the local hold over the next available"
1101 subtest "Test Local Holds Priority - Ensure no duplicate requests in holds queue (Bug 18001)" => sub {
1104 $dbh->do("DELETE FROM tmp_holdsqueue");
1105 $dbh->do("DELETE FROM hold_fill_targets");
1106 $dbh->do("DELETE FROM reserves");
1107 $dbh->do("DELETE FROM circulation_rules");
1108 Koha::Biblios->delete();
1110 t::lib::Mocks::mock_preference( 'LocalHoldsPriority', 1 );
1111 t::lib::Mocks::mock_preference( 'LocalHoldsPriorityPatronControl', 'PickupLibrary' );
1112 t::lib::Mocks::mock_preference( 'LocalHoldsPriorityItemControl', 'homebranch' );
1113 my $branch = $builder->build_object( { class => 'Koha::Libraries' } );
1114 my $branch2 = $builder->build_object( { class => 'Koha::Libraries' } );
1115 my $patron = $builder->build_object(
1117 class => "Koha::Patrons",
1119 branchcode => $branch->branchcode
1123 my $biblio = $builder->build_sample_biblio();
1124 my $item1 = $builder->build_sample_item(
1126 biblionumber => $biblio->biblionumber,
1127 library => $branch->branchcode,
1130 my $item2 = $builder->build_sample_item(
1132 biblionumber => $biblio->biblionumber,
1133 library => $branch->branchcode,
1137 my $item3 = $builder->build_sample_item(
1139 biblionumber => $biblio->biblionumber,
1140 library => $branch->branchcode,
1144 $reserve_id = AddReserve(
1146 branchcode => $item1->homebranch,
1147 borrowernumber => $patron->borrowernumber,
1148 biblionumber => $biblio->id,
1153 C4::HoldsQueue::CreateQueue();
1155 my $queue_rs = $schema->resultset('TmpHoldsqueue');
1157 is( $queue_rs->count(), 1,
1158 "Hold queue contains one hold from chosen from three possible items" );
1162 subtest 'Trivial test for UpdateTransportCostMatrix' => sub {
1165 { frombranch => $library1->{branchcode}, tobranch => $library2->{branchcode}, cost => 1, disable_transfer => 0 },
1166 { frombranch => $library2->{branchcode}, tobranch => $library3->{branchcode}, cost => 0, disable_transfer => 1 },
1168 C4::HoldsQueue::UpdateTransportCostMatrix( $recs );
1169 is( $schema->resultset('TransportCost')->count, 2, 'UpdateTransportCostMatrix added two records' );
1173 $schema->storage->txn_rollback;
1175 ### END Test holds queue builder does not violate holds policy ###
1178 my ($test_name, $use_cost_matrix, $pick_branch, $hold_branch) = @_;
1180 $test_name = "$test_name (".($use_cost_matrix ? "" : "don't ")."use cost matrix)";
1182 $use_cost_matrix_sth->execute($use_cost_matrix);
1183 C4::Context->clear_syspref_cache();
1184 C4::HoldsQueue::CreateQueue();
1186 my $results = $dbh->selectall_arrayref($test_sth, { Slice => {} }); # should be only one
1187 my $r = $results->[0];
1189 my $ok = is( $r->{pickbranch}, $pick_branch, "$test_name pick up branch");
1190 $ok &&= is( $r->{holdingbranch}, $hold_branch, "$test_name holding branch")
1193 diag( "Wrong pick-up/hold for first target (pick_branch, hold_branch, reserves, hold_fill_targets, tmp_holdsqueue): "
1194 . Dumper ($pick_branch, $hold_branch, map dump_records($_), qw(reserves hold_fill_targets tmp_holdsqueue)) )
1197 # Test enforcement of branch transfer limit
1198 if ( $r->{pickbranch} ne $r->{holdingbranch} ) {
1199 t::lib::Mocks::mock_preference( 'UseBranchTransferLimits', '1' );
1200 my $limit = Koha::Item::Transfer::Limit->new(
1202 toBranch => $r->{pickbranch},
1203 fromBranch => $r->{holdingbranch},
1204 itemtype => $r->{itype},
1207 C4::Context->clear_syspref_cache();
1208 C4::HoldsQueue::CreateQueue();
1209 $results = $dbh->selectall_arrayref( $test_sth, { Slice => {} } )
1210 ; # should be only one
1211 my $s = $results->[0];
1212 isnt( $r->{holdingbranch}, $s->{holdingbranch}, 'Hold is not trapped for pickup at a branch that cannot be transferred to');
1215 t::lib::Mocks::mock_preference( 'UseBranchTransferLimits', '0' );
1216 C4::Context->clear_syspref_cache();
1217 C4::HoldsQueue::CreateQueue();
1223 my ($tablename) = @_;
1224 return $dbh->selectall_arrayref("SELECT * from $tablename where borrowernumber = ?", { Slice => {} }, $borrowernumber);