C4/CourseReserves.pm

   1 package C4::CourseReserves;
   2
   3 # This file is part of Koha.
   4 #
   5 # Koha is free software; you can redistribute it and/or modify it
   6 # under the terms of the GNU General Public License as published by
   7 # the Free Software Foundation; either version 3 of the License, or
   8 # (at your option) any later version.
   9 #
  10 # Koha is distributed in the hope that it will be useful, but
  11 # WITHOUT ANY WARRANTY; without even the implied warranty of
  12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  13 # GNU General Public License for more details.
  14 #
  15 # You should have received a copy of the GNU General Public License
  16 # along with Koha; if not, see <http://www.gnu.org/licenses>.
  17
  18 use Modern::Perl;
  19
  20 use List::MoreUtils qw(any);
  21
  22 use C4::Context;
  23 use C4::Items qw(ModItem);
  24 use C4::Circulation qw(GetOpenIssue);
  25
  26 use vars qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS $DEBUG @FIELDS);
  27
  28 BEGIN {
  29     require Exporter;
  30     @ISA       = qw(Exporter);
  31     @EXPORT_OK = qw(
  32       &GetCourse
  33       &ModCourse
  34       &GetCourses
  35       &DelCourse
  36
  37       &GetCourseInstructors
  38       &ModCourseInstructors
  39
  40       &GetCourseItem
  41       &ModCourseItem
  42
  43       &GetCourseReserve
  44       &ModCourseReserve
  45       &GetCourseReserves
  46       &DelCourseReserve
  47
  48       &SearchCourses
  49
  50       &GetItemCourseReservesInfo
  51     );
  52     %EXPORT_TAGS = ( 'all' => \@EXPORT_OK );
  53
  54     $DEBUG = 0;
  55     @FIELDS = ( 'itype', 'ccode', 'holdingbranch', 'location' );
  56 }
  57
  58 =head1 NAME
  59
  60 C4::CourseReserves - Koha course reserves module
  61
  62 =head1 SYNOPSIS
  63
  64 use C4::CourseReserves;
  65
  66 =head1 DESCRIPTION
  67
  68 This module deals with course reserves.
  69
  70 =head1 FUNCTIONS
  71
  72 =head2 GetCourse
  73
  74     $course = GetCourse( $course_id );
  75
  76 =cut
  77
  78 sub GetCourse {
  79     my ($course_id) = @_;
  80     warn whoami() . "( $course_id )" if $DEBUG;
  81
  82     my $query = "SELECT * FROM courses WHERE course_id = ?";
  83     my $dbh   = C4::Context->dbh;
  84     my $sth   = $dbh->prepare($query);
  85     $sth->execute($course_id);
  86
  87     my $course = $sth->fetchrow_hashref();
  88
  89     $query = "
  90         SELECT b.* FROM course_instructors ci
  91         LEFT JOIN borrowers b ON ( ci.borrowernumber = b.borrowernumber )
  92         WHERE course_id =  ?
  93     ";
  94     $sth = $dbh->prepare($query);
  95     $sth->execute($course_id);
  96     $course->{'instructors'} = $sth->fetchall_arrayref( {} );
  97
  98     return $course;
  99 }
 100
 101 =head2 ModCourse
 102
 103     ModCourse( [ course_id => $id ] [, course_name => $course_name ] [etc...] );
 104
 105 =cut
 106
 107 sub ModCourse {
 108     my (%params) = @_;
 109     warn identify_myself(%params) if $DEBUG;
 110
 111     my $dbh = C4::Context->dbh;
 112
 113     my $course_id;
 114     if ( defined $params{'course_id'} ) {
 115         $course_id = $params{'course_id'};
 116         delete $params{'course_id'};
 117     }
 118
 119     my @query_keys;
 120     my @query_values;
 121
 122     my $query;
 123
 124     $query .= ($course_id) ? ' UPDATE ' : ' INSERT ';
 125     $query .= ' courses SET ';
 126
 127     foreach my $key ( keys %params ) {
 128         push( @query_keys,   "$key=?" );
 129         push( @query_values, $params{$key} );
 130     }
 131     $query .= join( ',', @query_keys );
 132
 133     if ($course_id) {
 134         $query .= " WHERE course_id = ?";
 135         push( @query_values, $course_id );
 136     }
 137
 138     $dbh->do( $query, undef, @query_values );
 139
 140     $course_id = $course_id
 141       || $dbh->last_insert_id( undef, undef, 'courses', 'course_id' );
 142
 143     EnableOrDisableCourseItems(
 144         course_id => $course_id,
 145         enabled   => $params{'enabled'}
 146     );
 147
 148     return $course_id;
 149 }
 150
 151 =head2 GetCourses
 152
 153   @courses = GetCourses( [ fieldname => $value ] [, fieldname2 => $value2 ] [etc...] );
 154
 155 =cut
 156
 157 sub GetCourses {
 158     my (%params) = @_;
 159     warn identify_myself(%params) if $DEBUG;
 160
 161     my @query_keys;
 162     my @query_values;
 163
 164     my $query = "
 165         SELECT c.course_id, c.department, c.course_number, c.section, c.course_name, c.term, c.staff_note, c.public_note, c.students_count, c.enabled, c.timestamp
 166         FROM courses c
 167         LEFT JOIN course_reserves ON course_reserves.course_id = c.course_id
 168         LEFT JOIN course_items ON course_items.ci_id = course_reserves.ci_id
 169     ";
 170
 171     if ( keys %params ) {
 172
 173         $query .= " WHERE ";
 174
 175         foreach my $key ( keys %params ) {
 176             push( @query_keys,   " $key LIKE ? " );
 177             push( @query_values, $params{$key} );
 178         }
 179
 180         $query .= join( ' AND ', @query_keys );
 181     }
 182
 183     $query .= " GROUP BY c.course_id, c.department, c.course_number, c.section, c.course_name, c.term, c.staff_note, c.public_note, c.students_count, c.enabled, c.timestamp ";
 184
 185     my $dbh = C4::Context->dbh;
 186     my $sth = $dbh->prepare($query);
 187     $sth->execute(@query_values);
 188
 189     my $courses = $sth->fetchall_arrayref( {} );
 190
 191     foreach my $c (@$courses) {
 192         $c->{'instructors'} = GetCourseInstructors( $c->{'course_id'} );
 193     }
 194
 195     return $courses;
 196 }
 197
 198 =head2 DelCourse
 199
 200   DelCourse( $course_id );
 201
 202 =cut
 203
 204 sub DelCourse {
 205     my ($course_id) = @_;
 206
 207     my $course_reserves = GetCourseReserves( course_id => $course_id );
 208
 209     foreach my $res (@$course_reserves) {
 210         DelCourseReserve( cr_id => $res->{'cr_id'} );
 211     }
 212
 213     my $query = "
 214         DELETE FROM course_instructors
 215         WHERE course_id = ?
 216     ";
 217     C4::Context->dbh->do( $query, undef, $course_id );
 218
 219     $query = "
 220         DELETE FROM courses
 221         WHERE course_id = ?
 222     ";
 223     C4::Context->dbh->do( $query, undef, $course_id );
 224 }
 225
 226 =head2 EnableOrDisableCourseItems
 227
 228   EnableOrDisableCourseItems( course_id => $course_id, enabled => $enabled );
 229
 230   For each item on reserve for this course,
 231   if the course item has no active course reserves,
 232   swap the fields for the item to make it 'normal'
 233   again.
 234
 235   enabled => 'yes' to enable course items
 236   enabled => 'no' to disable course items
 237
 238 =cut
 239
 240 sub EnableOrDisableCourseItems {
 241     my (%params) = @_;
 242     warn identify_myself(%params) if $DEBUG;
 243
 244     my $course_id = $params{'course_id'};
 245     my $enabled = $params{'enabled'} || 0;
 246
 247     my $lookfor = ( $enabled eq 'yes' ) ? 'no' : 'yes';
 248
 249     return unless ( $course_id && $enabled );
 250     return unless ( $enabled eq 'yes' || $enabled eq 'no' );
 251
 252     my $course_reserves = GetCourseReserves( course_id => $course_id );
 253
 254     if ( $enabled eq 'yes' ) {
 255         foreach my $course_reserve (@$course_reserves) {
 256             if (CountCourseReservesForItem(
 257                     ci_id   => $course_reserve->{'ci_id'},
 258                     enabled => 'yes'
 259                 )
 260               ) {
 261                 EnableOrDisableCourseItem(
 262                     ci_id   => $course_reserve->{'ci_id'},
 263                 );
 264             }
 265         }
 266     }
 267     if ( $enabled eq 'no' ) {
 268         foreach my $course_reserve (@$course_reserves) {
 269             unless (
 270                 CountCourseReservesForItem(
 271                     ci_id   => $course_reserve->{'ci_id'},
 272                     enabled => 'yes'
 273                 )
 274               ) {
 275                 EnableOrDisableCourseItem(
 276                     ci_id   => $course_reserve->{'ci_id'},
 277                 );
 278             }
 279         }
 280     }
 281 }
 282
 283 =head2 EnableOrDisableCourseItem
 284
 285     EnableOrDisableCourseItem( ci_id => $ci_id );
 286
 287 =cut
 288
 289 sub EnableOrDisableCourseItem {
 290     my (%params) = @_;
 291     warn identify_myself(%params) if $DEBUG;
 292
 293     my $ci_id   = $params{'ci_id'};
 294
 295     return unless ( $ci_id );
 296
 297     my $course_item = GetCourseItem( ci_id => $ci_id );
 298
 299     my $info = GetItemCourseReservesInfo( itemnumber => $course_item->{itemnumber} );
 300
 301     my $enabled = any { $_->{course}->{enabled} eq 'yes' } @$info;
 302     $enabled = $enabled ? 'yes' : 'no';
 303
 304     ## We don't want to 'enable' an already enabled item,
 305     ## or disable and already disabled item,
 306     ## as that would cause the fields to swap
 307     if ( $course_item->{'enabled'} ne $enabled ) {
 308         _SwapAllFields($ci_id);
 309
 310         my $query = "
 311             UPDATE course_items
 312             SET enabled = ?
 313             WHERE ci_id = ?
 314         ";
 315
 316         C4::Context->dbh->do( $query, undef, $enabled, $ci_id );
 317
 318     }
 319
 320 }
 321
 322 =head2 GetCourseInstructors
 323
 324     @$borrowers = GetCourseInstructors( $course_id );
 325
 326 =cut
 327
 328 sub GetCourseInstructors {
 329     my ($course_id) = @_;
 330     warn "C4::CourseReserves::GetCourseInstructors( $course_id )"
 331       if $DEBUG;
 332
 333     my $query = "
 334         SELECT * FROM borrowers
 335         RIGHT JOIN course_instructors ON ( course_instructors.borrowernumber = borrowers.borrowernumber )
 336         WHERE course_instructors.course_id = ?
 337     ";
 338
 339     my $dbh = C4::Context->dbh;
 340     my $sth = $dbh->prepare($query);
 341     $sth->execute($course_id);
 342
 343     return $sth->fetchall_arrayref( {} );
 344 }
 345
 346 =head2 ModCourseInstructors
 347
 348     ModCourseInstructors( mode => $mode, course_id => $course_id, [ cardnumbers => $cardnumbers ] OR [ borrowernumbers => $borrowernumbers  );
 349
 350     $mode can be 'replace', 'add', or 'delete'
 351
 352     $cardnumbers and $borrowernumbers are both references to arrays
 353
 354     Use either cardnumbers or borrowernumber, but not both.
 355
 356 =cut
 357
 358 sub ModCourseInstructors {
 359     my (%params) = @_;
 360     warn identify_myself(%params) if $DEBUG;
 361
 362     my $course_id       = $params{'course_id'};
 363     my $mode            = $params{'mode'};
 364     my $cardnumbers     = $params{'cardnumbers'};
 365     my $borrowernumbers = $params{'borrowernumbers'};
 366
 367     return unless ($course_id);
 368     return
 369       unless ( $mode eq 'replace'
 370         || $mode eq 'add'
 371         || $mode eq 'delete' );
 372     return unless ( $cardnumbers || $borrowernumbers );
 373     return if ( $cardnumbers && $borrowernumbers );
 374
 375     my ( @cardnumbers, @borrowernumbers );
 376     @cardnumbers = @$cardnumbers if ( ref($cardnumbers) eq 'ARRAY' );
 377     @borrowernumbers = @$borrowernumbers
 378       if ( ref($borrowernumbers) eq 'ARRAY' );
 379
 380     my $field  = (@cardnumbers) ? 'cardnumber' : 'borrowernumber';
 381     my @fields = (@cardnumbers) ? @cardnumbers : @borrowernumbers;
 382     my $placeholders = join( ',', ('?') x scalar @fields );
 383
 384     my $dbh = C4::Context->dbh;
 385
 386     $dbh->do( "DELETE FROM course_instructors WHERE course_id = ?", undef, $course_id )
 387       if ( $mode eq 'replace' );
 388
 389     my $query;
 390
 391     if ( $mode eq 'add' || $mode eq 'replace' ) {
 392         $query = "
 393             INSERT INTO course_instructors ( course_id, borrowernumber )
 394             SELECT ?, borrowernumber
 395             FROM borrowers
 396             WHERE $field IN ( $placeholders )
 397         ";
 398     } else {
 399         $query = "
 400             DELETE FROM course_instructors
 401             WHERE course_id = ?
 402             AND borrowernumber IN (
 403                 SELECT borrowernumber FROM borrowers WHERE $field IN ( $placeholders )
 404             )
 405         ";
 406     }
 407
 408     my $sth = $dbh->prepare($query);
 409
 410     $sth->execute( $course_id, @fields ) if (@fields);
 411 }
 412
 413 =head2 GetCourseItem {
 414
 415   $course_item = GetCourseItem( itemnumber => $itemnumber [, ci_id => $ci_id );
 416
 417 =cut
 418
 419 sub GetCourseItem {
 420     my (%params) = @_;
 421     warn identify_myself(%params) if $DEBUG;
 422
 423     my $ci_id      = $params{'ci_id'};
 424     my $itemnumber = $params{'itemnumber'};
 425
 426     return unless ( $itemnumber || $ci_id );
 427
 428     my $field = ($itemnumber) ? 'itemnumber' : 'ci_id';
 429     my $value = ($itemnumber) ? $itemnumber  : $ci_id;
 430
 431     my $query = "SELECT * FROM course_items WHERE $field = ?";
 432     my $dbh   = C4::Context->dbh;
 433     my $sth   = $dbh->prepare($query);
 434     $sth->execute($value);
 435
 436     my $course_item = $sth->fetchrow_hashref();
 437
 438     if ($course_item) {
 439         $query = "SELECT * FROM course_reserves WHERE ci_id = ?";
 440         $sth   = $dbh->prepare($query);
 441         $sth->execute( $course_item->{'ci_id'} );
 442         my $course_reserves = $sth->fetchall_arrayref( {} );
 443
 444         $course_item->{'course_reserves'} = $course_reserves
 445           if ($course_reserves);
 446     }
 447     return $course_item;
 448 }
 449
 450 =head2 ModCourseItem {
 451
 452   ModCourseItem( %params );
 453
 454   Creates or modifies an existing course item.
 455
 456 =cut
 457
 458 sub ModCourseItem {
 459     my (%params) = @_;
 460     warn identify_myself(%params) if $DEBUG;
 461
 462     my $itemnumber = $params{'itemnumber'};
 463
 464     return unless ($itemnumber);
 465
 466     my $course_item = GetCourseItem( itemnumber => $itemnumber );
 467
 468     my $ci_id;
 469
 470     if ($course_item) {
 471         $ci_id = $course_item->{'ci_id'};
 472
 473         _UpdateCourseItem(
 474             ci_id       => $ci_id,
 475             course_item => $course_item,
 476             %params
 477         );
 478     } else {
 479         $ci_id = _AddCourseItem(%params);
 480     }
 481
 482     return $ci_id;
 483
 484 }
 485
 486 =head2 _AddCourseItem
 487
 488     my $ci_id = _AddCourseItem( %params );
 489
 490 =cut
 491
 492 sub _AddCourseItem {
 493     my (%params) = @_;
 494     warn identify_myself(%params) if $DEBUG;
 495
 496     my ( @fields, @values );
 497
 498     push( @fields, 'itemnumber = ?' );
 499     push( @values, $params{'itemnumber'} );
 500
 501     foreach (@FIELDS) {
 502         push( @fields, "$_ = ?" );
 503         push( @values, $params{$_} || undef );
 504     }
 505
 506     my $query = "INSERT INTO course_items SET " . join( ',', @fields );
 507     my $dbh = C4::Context->dbh;
 508     $dbh->do( $query, undef, @values );
 509
 510     my $ci_id = $dbh->last_insert_id( undef, undef, 'course_items', 'ci_id' );
 511
 512     return $ci_id;
 513 }
 514
 515 =head2 _UpdateCourseItem
 516
 517   _UpdateCourseItem( %params );
 518
 519 =cut
 520
 521 sub _UpdateCourseItem {
 522     my (%params) = @_;
 523     warn identify_myself(%params) if $DEBUG;
 524
 525     my $ci_id         = $params{'ci_id'};
 526     my $course_item   = $params{'course_item'};
 527
 528     return unless ( $ci_id || $course_item );
 529
 530     $course_item = GetCourseItem( ci_id => $ci_id )
 531       unless ($course_item);
 532     $ci_id = $course_item->{'ci_id'} unless ($ci_id);
 533
 534     my %mod_params =
 535       map {
 536         defined $params{$_} && $params{$_} ne ''
 537           ? ( $_ => $params{$_} )
 538           : ()
 539       } @FIELDS;
 540
 541     ModItem( \%mod_params, undef, $course_item->{'itemnumber'} );
 542 }
 543
 544 =head2 _ModStoredFields
 545
 546     _ModStoredFields( %params );
 547
 548     Updates the values for the 'original' fields in course_items
 549     for a given ci_id
 550
 551 =cut
 552
 553 sub _ModStoredFields {
 554     my (%params) = @_;
 555     warn identify_myself(%params) if $DEBUG;
 556
 557     return unless ( $params{'ci_id'} );
 558
 559     my ( @fields_to_update, @values_to_update );
 560
 561     foreach (@FIELDS) {
 562         if ( defined($params{$_}) ) {
 563             push( @fields_to_update, $_ );
 564             push( @values_to_update, $params{$_} );
 565         }
 566     }
 567
 568     my $query = "UPDATE course_items SET " . join( ',', map { "$_=?" } @fields_to_update ) . " WHERE ci_id = ?";
 569
 570     C4::Context->dbh->do( $query, undef, @values_to_update, $params{'ci_id'} )
 571       if (@values_to_update);
 572
 573 }
 574
 575 =head2 _RevertFields
 576
 577     _RevertFields( ci_id => $ci_id, fields => \@fields_to_revert );
 578
 579 =cut
 580
 581 sub _RevertFields {
 582     my (%params) = @_;
 583     warn identify_myself(%params) if $DEBUG;
 584
 585     my $ci_id = $params{'ci_id'};
 586
 587     return unless ($ci_id);
 588
 589     my $course_item = GetCourseItem( ci_id => $params{'ci_id'} );
 590
 591     my $mod_item_params;
 592     foreach my $field ( @FIELDS ) {
 593         next unless defined $course_item->{$field};
 594         $mod_item_params->{$field} = $course_item->{$field};
 595     }
 596
 597     ModItem( $mod_item_params, undef, $course_item->{'itemnumber'} ) if $mod_item_params && %$mod_item_params;
 598 }
 599
 600 =head2 _SwapAllFields
 601
 602     _SwapAllFields( $ci_id );
 603
 604 =cut
 605
 606 sub _SwapAllFields {
 607     my ($ci_id) = @_;
 608     warn "C4::CourseReserves::_SwapFields( $ci_id )" if $DEBUG;
 609
 610     my $course_item = GetCourseItem( ci_id => $ci_id );
 611     my $item = Koha::Items->find($course_item->{'itemnumber'});
 612
 613     my %course_item_fields;
 614     my %item_fields;
 615     foreach (@FIELDS) {
 616         if ( defined( $course_item->{$_} ) ) {
 617             $course_item_fields{$_} = $course_item->{$_};
 618             $item_fields{$_}        = $item->$_ || q{};
 619         }
 620     }
 621
 622     ModItem( \%course_item_fields, undef, $course_item->{'itemnumber'} ) if %course_item_fields;
 623     _ModStoredFields( %item_fields, ci_id => $ci_id );
 624 }
 625
 626 =head2 GetCourseItems {
 627
 628   $course_items = GetCourseItems(
 629       [course_id => $course_id]
 630       [, itemnumber => $itemnumber ]
 631   );
 632
 633 =cut
 634
 635 sub GetCourseItems {
 636     my (%params) = @_;
 637     warn identify_myself(%params) if $DEBUG;
 638
 639     my $course_id  = $params{'course_id'};
 640     my $itemnumber = $params{'itemnumber'};
 641
 642     return unless ($course_id);
 643
 644     my @query_keys;
 645     my @query_values;
 646
 647     my $query = "SELECT * FROM course_items";
 648
 649     if ( keys %params ) {
 650
 651         $query .= " WHERE ";
 652
 653         foreach my $key ( keys %params ) {
 654             push( @query_keys,   " $key LIKE ? " );
 655             push( @query_values, $params{$key} );
 656         }
 657
 658         $query .= join( ' AND ', @query_keys );
 659     }
 660
 661     my $dbh = C4::Context->dbh;
 662     my $sth = $dbh->prepare($query);
 663     $sth->execute(@query_values);
 664
 665     return $sth->fetchall_arrayref( {} );
 666 }
 667
 668 =head2 DelCourseItem {
 669
 670   DelCourseItem( ci_id => $cr_id );
 671
 672 =cut
 673
 674 sub DelCourseItem {
 675     my (%params) = @_;
 676     warn identify_myself(%params) if $DEBUG;
 677
 678     my $ci_id = $params{'ci_id'};
 679
 680     return unless ($ci_id);
 681
 682     _RevertFields( ci_id => $ci_id );
 683
 684     my $query = "
 685         DELETE FROM course_items
 686         WHERE ci_id = ?
 687     ";
 688     C4::Context->dbh->do( $query, undef, $ci_id );
 689 }
 690
 691 =head2 GetCourseReserve {
 692
 693   $course_item = GetCourseReserve( %params );
 694
 695 =cut
 696
 697 sub GetCourseReserve {
 698     my (%params) = @_;
 699     warn identify_myself(%params) if $DEBUG;
 700
 701     my $cr_id     = $params{'cr_id'};
 702     my $course_id = $params{'course_id'};
 703     my $ci_id     = $params{'ci_id'};
 704
 705     return unless ( $cr_id || ( $course_id && $ci_id ) );
 706
 707     my $dbh = C4::Context->dbh;
 708     my $sth;
 709
 710     if ($cr_id) {
 711         my $query = "
 712             SELECT * FROM course_reserves
 713             WHERE cr_id = ?
 714         ";
 715         $sth = $dbh->prepare($query);
 716         $sth->execute($cr_id);
 717     } else {
 718         my $query = "
 719             SELECT * FROM course_reserves
 720             WHERE course_id = ? AND ci_id = ?
 721         ";
 722         $sth = $dbh->prepare($query);
 723         $sth->execute( $course_id, $ci_id );
 724     }
 725
 726     my $course_reserve = $sth->fetchrow_hashref();
 727     return $course_reserve;
 728 }
 729
 730 =head2 ModCourseReserve
 731
 732     $id = ModCourseReserve( %params );
 733
 734 =cut
 735
 736 sub ModCourseReserve {
 737     my (%params) = @_;
 738     warn identify_myself(%params) if $DEBUG;
 739
 740     my $course_id   = $params{'course_id'};
 741     my $ci_id       = $params{'ci_id'};
 742     my $staff_note  = $params{'staff_note'};
 743     my $public_note = $params{'public_note'};
 744
 745     return unless ( $course_id && $ci_id );
 746
 747     my $course_reserve = GetCourseReserve( course_id => $course_id, ci_id => $ci_id );
 748     my $cr_id;
 749
 750     my $dbh = C4::Context->dbh;
 751
 752     if ($course_reserve) {
 753         $cr_id = $course_reserve->{'cr_id'};
 754
 755         my $query = "
 756             UPDATE course_reserves
 757             SET staff_note = ?, public_note = ?
 758             WHERE cr_id = ?
 759         ";
 760         $dbh->do( $query, undef, $staff_note, $public_note, $cr_id );
 761     } else {
 762         my $query = "
 763             INSERT INTO course_reserves SET
 764             course_id = ?,
 765             ci_id = ?,
 766             staff_note = ?,
 767             public_note = ?
 768         ";
 769         $dbh->do( $query, undef, $course_id, $ci_id, $staff_note, $public_note );
 770         $cr_id = $dbh->last_insert_id( undef, undef, 'course_reserves', 'cr_id' );
 771     }
 772
 773     EnableOrDisableCourseItem(
 774         ci_id   => $params{'ci_id'},
 775     );
 776
 777     return $cr_id;
 778 }
 779
 780 =head2 GetCourseReserves {
 781
 782   $course_reserves = GetCourseReserves( %params );
 783
 784   Required:
 785       course_id OR ci_id
 786   Optional:
 787       include_items   => 1,
 788       include_count   => 1,
 789       include_courses => 1,
 790
 791 =cut
 792
 793 sub GetCourseReserves {
 794     my (%params) = @_;
 795     warn identify_myself(%params) if $DEBUG;
 796
 797     my $course_id       = $params{'course_id'};
 798     my $ci_id           = $params{'ci_id'};
 799     my $include_items   = $params{'include_items'};
 800     my $include_count   = $params{'include_count'};
 801     my $include_courses = $params{'include_courses'};
 802
 803     return unless ( $course_id || $ci_id );
 804
 805     my $field = ($course_id) ? 'course_id' : 'ci_id';
 806     my $value = ($course_id) ? $course_id  : $ci_id;
 807
 808     my $query = "
 809         SELECT cr.*, ci.itemnumber
 810         FROM course_reserves cr, course_items ci
 811         WHERE cr.$field = ?
 812         AND cr.ci_id = ci.ci_id
 813     ";
 814     my $dbh = C4::Context->dbh;
 815     my $sth = $dbh->prepare($query);
 816     $sth->execute($value);
 817
 818     my $course_reserves = $sth->fetchall_arrayref( {} );
 819
 820     if ($include_items) {
 821         foreach my $cr (@$course_reserves) {
 822             my $item = Koha::Items->find( $cr->{itemnumber} );
 823             my $biblio = $item->biblio;
 824             my $biblioitem = $biblio->biblioitem;
 825             $cr->{'course_item'} = GetCourseItem( ci_id => $cr->{'ci_id'} );
 826             $cr->{'item'}        = $item;
 827             $cr->{'biblio'}      = $biblio;
 828             $cr->{'biblioitem'}  = $biblioitem;
 829             $cr->{'issue'}       = GetOpenIssue( $cr->{'itemnumber'} );
 830         }
 831     }
 832
 833     if ($include_count) {
 834         foreach my $cr (@$course_reserves) {
 835             $cr->{'reserves_count'} = CountCourseReservesForItem( ci_id => $cr->{'ci_id'} );
 836         }
 837     }
 838
 839     if ($include_courses) {
 840         foreach my $cr (@$course_reserves) {
 841             $cr->{'courses'} = GetCourses( itemnumber => $cr->{'itemnumber'} );
 842         }
 843     }
 844
 845     return $course_reserves;
 846 }
 847
 848 =head2 DelCourseReserve {
 849
 850   DelCourseReserve( cr_id => $cr_id );
 851
 852 =cut
 853
 854 sub DelCourseReserve {
 855     my (%params) = @_;
 856     warn identify_myself(%params) if $DEBUG;
 857
 858     my $cr_id = $params{'cr_id'};
 859
 860     return unless ($cr_id);
 861
 862     my $dbh = C4::Context->dbh;
 863
 864     my $course_reserve = GetCourseReserve( cr_id => $cr_id );
 865
 866     my $query = "
 867         DELETE FROM course_reserves
 868         WHERE cr_id = ?
 869     ";
 870     $dbh->do( $query, undef, $cr_id );
 871
 872     ## If there are no other course reserves for this item
 873     ## delete the course_item as well
 874     unless ( CountCourseReservesForItem( ci_id => $course_reserve->{'ci_id'} ) ) {
 875         DelCourseItem( ci_id => $course_reserve->{'ci_id'} );
 876     }
 877
 878 }
 879
 880 =head2 GetItemCourseReservesInfo
 881
 882     my $arrayref = GetItemCourseReservesInfo( itemnumber => $itemnumber );
 883
 884     For a given item, returns an arrayref of reserves hashrefs,
 885     with a course hashref under the key 'course'
 886
 887 =cut
 888
 889 sub GetItemCourseReservesInfo {
 890     my (%params) = @_;
 891     warn identify_myself(%params) if $DEBUG;
 892
 893     my $itemnumber = $params{'itemnumber'};
 894
 895     return unless ($itemnumber);
 896
 897     my $course_item = GetCourseItem( itemnumber => $itemnumber );
 898
 899     return unless ( keys %$course_item );
 900
 901     my $course_reserves = GetCourseReserves( ci_id => $course_item->{'ci_id'} );
 902
 903     foreach my $cr (@$course_reserves) {
 904         $cr->{'course'} = GetCourse( $cr->{'course_id'} );
 905     }
 906
 907     return $course_reserves;
 908 }
 909
 910 =head2 CountCourseReservesForItem
 911
 912     $bool = CountCourseReservesForItem( %params );
 913
 914     ci_id - course_item id
 915     OR
 916     itemnumber - course_item itemnumber
 917
 918     enabled = 'yes' or 'no'
 919     Optional, if not supplied, counts reserves
 920     for both enabled and disabled courses
 921
 922 =cut
 923
 924 sub CountCourseReservesForItem {
 925     my (%params) = @_;
 926     warn identify_myself(%params) if $DEBUG;
 927
 928     my $ci_id      = $params{'ci_id'};
 929     my $itemnumber = $params{'itemnumber'};
 930     my $enabled    = $params{'enabled'};
 931
 932     return unless ( $ci_id || $itemnumber );
 933
 934     my $course_item = GetCourseItem( ci_id => $ci_id, itemnumber => $itemnumber );
 935
 936     my @params = ( $course_item->{'ci_id'} );
 937     push( @params, $enabled ) if ($enabled);
 938
 939     my $query = "
 940         SELECT COUNT(*) AS count
 941         FROM course_reserves cr
 942         LEFT JOIN courses c ON ( c.course_id = cr.course_id )
 943         WHERE ci_id = ?
 944     ";
 945     $query .= "AND c.enabled = ?" if ($enabled);
 946
 947     my $dbh = C4::Context->dbh;
 948     my $sth = $dbh->prepare($query);
 949     $sth->execute(@params);
 950
 951     my $row = $sth->fetchrow_hashref();
 952
 953     return $row->{'count'};
 954 }
 955
 956 =head2 SearchCourses
 957
 958     my $courses = SearchCourses( term => $search_term, enabled => 'yes' );
 959
 960 =cut
 961
 962 sub SearchCourses {
 963     my (%params) = @_;
 964     warn identify_myself(%params) if $DEBUG;
 965
 966     my $term = $params{'term'};
 967
 968     my $enabled = $params{'enabled'} || '%';
 969
 970     my @params;
 971     my $query = "
 972         SELECT c.course_id, c.department, c.course_number, c.section, c.course_name, c.term, c.staff_note, c.public_note, c.students_count, c.enabled, c.timestamp
 973         FROM courses c
 974         LEFT JOIN course_instructors ci
 975             ON ( c.course_id = ci.course_id )
 976         LEFT JOIN borrowers b
 977             ON ( ci.borrowernumber = b.borrowernumber )
 978         LEFT JOIN authorised_values av
 979             ON ( c.department = av.authorised_value )
 980         WHERE
 981             ( av.category = 'DEPARTMENT' OR av.category = 'TERM' )
 982             AND
 983             (
 984                 department LIKE ? OR
 985                 course_number LIKE ? OR
 986                 section LIKE ? OR
 987                 course_name LIKE ? OR
 988                 term LIKE ? OR
 989                 public_note LIKE ? OR
 990                 CONCAT(surname,' ',firstname) LIKE ? OR
 991                 CONCAT(firstname,' ',surname) LIKE ? OR
 992                 lib LIKE ? OR
 993                 lib_opac LIKE ?
 994            )
 995            AND
 996            c.enabled LIKE ?
 997         GROUP BY c.course_id, c.department, c.course_number, c.section, c.course_name, c.term, c.staff_note, c.public_note, c.students_count, c.enabled, c.timestamp
 998     ";
 999
1000     $term //= '';
1001     $term   = "%$term%";
1002     @params = ($term) x 10;
1003
1004     $query .= " ORDER BY department, course_number, section, term, course_name ";
1005
1006     my $dbh = C4::Context->dbh;
1007     my $sth = $dbh->prepare($query);
1008
1009     $sth->execute( @params, $enabled );
1010
1011     my $courses = $sth->fetchall_arrayref( {} );
1012
1013     foreach my $c (@$courses) {
1014         $c->{'instructors'} = GetCourseInstructors( $c->{'course_id'} );
1015     }
1016
1017     return $courses;
1018 }
1019
1020 sub whoami  { ( caller(1) )[3] }
1021 sub whowasi { ( caller(2) )[3] }
1022
1023 sub stringify_params {
1024     my (%params) = @_;
1025
1026     my $string = "\n";
1027
1028     foreach my $key ( keys %params ) {
1029         $string .= "    $key => " . $params{$key} . "\n";
1030     }
1031
1032     return "( $string )";
1033 }
1034
1035 sub identify_myself {
1036     my (%params) = @_;
1037
1038     return whowasi() . stringify_params(%params);
1039 }
1040
1041 1;
1042
1043 =head1 AUTHOR
1044
1045 Kyle M Hall <kyle@bywatersolutions.com>
1046
1047 =cut