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(GetItem 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 courses.*
 166         FROM courses
 167         LEFT JOIN course_reserves ON course_reserves.course_id = courses.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 courses.course_id ";
 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     my $itype         = $params{'itype'};
 464     my $ccode         = $params{'ccode'};
 465     my $holdingbranch = $params{'holdingbranch'};
 466     my $location      = $params{'location'};
 467
 468     return unless ($itemnumber);
 469
 470     my $course_item = GetCourseItem( itemnumber => $itemnumber );
 471
 472     my $ci_id;
 473
 474     if ($course_item) {
 475         $ci_id = $course_item->{'ci_id'};
 476
 477         _UpdateCourseItem(
 478             ci_id       => $ci_id,
 479             course_item => $course_item,
 480             %params
 481         );
 482     } else {
 483         $ci_id = _AddCourseItem(%params);
 484     }
 485
 486     return $ci_id;
 487
 488 }
 489
 490 =head2 _AddCourseItem
 491
 492     my $ci_id = _AddCourseItem( %params );
 493
 494 =cut
 495
 496 sub _AddCourseItem {
 497     my (%params) = @_;
 498     warn identify_myself(%params) if $DEBUG;
 499
 500     my ( @fields, @values );
 501
 502     push( @fields, 'itemnumber = ?' );
 503     push( @values, $params{'itemnumber'} );
 504
 505     foreach (@FIELDS) {
 506         if ( $params{$_} ) {
 507             push( @fields, "$_ = ?" );
 508             push( @values, $params{$_} );
 509         }
 510     }
 511
 512     my $query = "INSERT INTO course_items SET " . join( ',', @fields );
 513     my $dbh = C4::Context->dbh;
 514     $dbh->do( $query, undef, @values );
 515
 516     my $ci_id = $dbh->last_insert_id( undef, undef, 'course_items', 'ci_id' );
 517
 518     return $ci_id;
 519 }
 520
 521 =head2 _UpdateCourseItem
 522
 523   _UpdateCourseItem( %params );
 524
 525 =cut
 526
 527 sub _UpdateCourseItem {
 528     my (%params) = @_;
 529     warn identify_myself(%params) if $DEBUG;
 530
 531     my $ci_id         = $params{'ci_id'};
 532     my $course_item   = $params{'course_item'};
 533     my $itype         = $params{'itype'};
 534     my $ccode         = $params{'ccode'};
 535     my $holdingbranch = $params{'holdingbranch'};
 536     my $location      = $params{'location'};
 537
 538     return unless ( $ci_id || $course_item );
 539
 540     $course_item = GetCourseItem( ci_id => $ci_id )
 541       unless ($course_item);
 542     $ci_id = $course_item->{'ci_id'} unless ($ci_id);
 543
 544     ## Revert fields that had an 'original' value, but now don't
 545     ## Update the item fields to the stored values from course_items
 546     ## and then set those fields in course_items to NULL
 547     my @fields_to_revert;
 548     foreach (@FIELDS) {
 549         if ( !$params{$_} && $course_item->{$_} ) {
 550             push( @fields_to_revert, $_ );
 551         }
 552     }
 553     _RevertFields(
 554         ci_id       => $ci_id,
 555         fields      => \@fields_to_revert,
 556         course_item => $course_item
 557     ) if (@fields_to_revert);
 558
 559     ## Update fields that still have an original value, but it has changed
 560     ## This necessitates only changing the current item values, as we still
 561     ## have the original values stored in course_items
 562     my %mod_params;
 563     foreach (@FIELDS) {
 564         if (   $params{$_}
 565             && $course_item->{$_}
 566             && $params{$_} ne $course_item->{$_} ) {
 567             $mod_params{$_} = $params{$_};
 568         }
 569     }
 570     ModItem( \%mod_params, undef, $course_item->{'itemnumber'} ) if %mod_params;
 571
 572     ## Update fields that didn't have an original value, but now do
 573     ## We must save the original value in course_items, and also
 574     ## update the item fields to the new value
 575     my $item = GetItem( $course_item->{'itemnumber'} );
 576     my %mod_params_new;
 577     my %mod_params_old;
 578     foreach (@FIELDS) {
 579         if ( $params{$_} && !$course_item->{$_} ) {
 580             $mod_params_new{$_} = $params{$_};
 581             $mod_params_old{$_} = $item->{$_};
 582         }
 583     }
 584     _ModStoredFields( 'ci_id' => $params{'ci_id'}, %mod_params_old );
 585     ModItem( \%mod_params_new, undef, $course_item->{'itemnumber'} ) if %mod_params_new;
 586
 587 }
 588
 589 =head2 _ModStoredFields
 590
 591     _ModStoredFields( %params );
 592
 593     Updates the values for the 'original' fields in course_items
 594     for a given ci_id
 595
 596 =cut
 597
 598 sub _ModStoredFields {
 599     my (%params) = @_;
 600     warn identify_myself(%params) if $DEBUG;
 601
 602     return unless ( $params{'ci_id'} );
 603
 604     my ( @fields_to_update, @values_to_update );
 605
 606     foreach (@FIELDS) {
 607         if ( $params{$_} ) {
 608             push( @fields_to_update, $_ );
 609             push( @values_to_update, $params{$_} );
 610         }
 611     }
 612
 613     my $query = "UPDATE course_items SET " . join( ',', map { "$_=?" } @fields_to_update ) . " WHERE ci_id = ?";
 614
 615     C4::Context->dbh->do( $query, undef, @values_to_update, $params{'ci_id'} )
 616       if (@values_to_update);
 617
 618 }
 619
 620 =head2 _RevertFields
 621
 622     _RevertFields( ci_id => $ci_id, fields => \@fields_to_revert );
 623
 624 =cut
 625
 626 sub _RevertFields {
 627     my (%params) = @_;
 628     warn identify_myself(%params) if $DEBUG;
 629
 630     my $ci_id       = $params{'ci_id'};
 631     my $course_item = $params{'course_item'};
 632     my $fields      = $params{'fields'};
 633     my @fields      = @$fields;
 634
 635     return unless ($ci_id);
 636
 637     $course_item = GetCourseItem( ci_id => $params{'ci_id'} )
 638       unless ($course_item);
 639
 640     my $mod_item_params;
 641     my @fields_to_null;
 642     foreach my $field (@fields) {
 643         foreach (@FIELDS) {
 644             if ( $field eq $_ && $course_item->{$_} ) {
 645                 $mod_item_params->{$_} = $course_item->{$_};
 646                 push( @fields_to_null, $_ );
 647             }
 648         }
 649     }
 650     ModItem( $mod_item_params, undef, $course_item->{'itemnumber'} ) if $mod_item_params && %$mod_item_params;
 651
 652     my $query = "UPDATE course_items SET " . join( ',', map { "$_=NULL" } @fields_to_null ) . " WHERE ci_id = ?";
 653
 654     C4::Context->dbh->do( $query, undef, $ci_id ) if (@fields_to_null);
 655 }
 656
 657 =head2 _SwapAllFields
 658
 659     _SwapAllFields( $ci_id );
 660
 661 =cut
 662
 663 sub _SwapAllFields {
 664     my ($ci_id) = @_;
 665     warn "C4::CourseReserves::_SwapFields( $ci_id )" if $DEBUG;
 666
 667     my $course_item = GetCourseItem( ci_id => $ci_id );
 668     my $item = GetItem( $course_item->{'itemnumber'} );
 669
 670     my %course_item_fields;
 671     my %item_fields;
 672     foreach (@FIELDS) {
 673         if ( $course_item->{$_} ) {
 674             $course_item_fields{$_} = $course_item->{$_};
 675             $item_fields{$_}        = $item->{$_};
 676         }
 677     }
 678
 679     ModItem( \%course_item_fields, undef, $course_item->{'itemnumber'} ) if %course_item_fields;
 680     _ModStoredFields( %item_fields, ci_id => $ci_id );
 681 }
 682
 683 =head2 GetCourseItems {
 684
 685   $course_items = GetCourseItems(
 686       [course_id => $course_id]
 687       [, itemnumber => $itemnumber ]
 688   );
 689
 690 =cut
 691
 692 sub GetCourseItems {
 693     my (%params) = @_;
 694     warn identify_myself(%params) if $DEBUG;
 695
 696     my $course_id  = $params{'course_id'};
 697     my $itemnumber = $params{'itemnumber'};
 698
 699     return unless ($course_id);
 700
 701     my @query_keys;
 702     my @query_values;
 703
 704     my $query = "SELECT * FROM course_items";
 705
 706     if ( keys %params ) {
 707
 708         $query .= " WHERE ";
 709
 710         foreach my $key ( keys %params ) {
 711             push( @query_keys,   " $key LIKE ? " );
 712             push( @query_values, $params{$key} );
 713         }
 714
 715         $query .= join( ' AND ', @query_keys );
 716     }
 717
 718     my $dbh = C4::Context->dbh;
 719     my $sth = $dbh->prepare($query);
 720     $sth->execute(@query_values);
 721
 722     return $sth->fetchall_arrayref( {} );
 723 }
 724
 725 =head2 DelCourseItem {
 726
 727   DelCourseItem( ci_id => $cr_id );
 728
 729 =cut
 730
 731 sub DelCourseItem {
 732     my (%params) = @_;
 733     warn identify_myself(%params) if $DEBUG;
 734
 735     my $ci_id = $params{'ci_id'};
 736
 737     return unless ($ci_id);
 738
 739     _RevertFields( ci_id => $ci_id, fields => \@FIELDS );
 740
 741     my $query = "
 742         DELETE FROM course_items
 743         WHERE ci_id = ?
 744     ";
 745     C4::Context->dbh->do( $query, undef, $ci_id );
 746 }
 747
 748 =head2 GetCourseReserve {
 749
 750   $course_item = GetCourseReserve( %params );
 751
 752 =cut
 753
 754 sub GetCourseReserve {
 755     my (%params) = @_;
 756     warn identify_myself(%params) if $DEBUG;
 757
 758     my $cr_id     = $params{'cr_id'};
 759     my $course_id = $params{'course_id'};
 760     my $ci_id     = $params{'ci_id'};
 761
 762     return unless ( $cr_id || ( $course_id && $ci_id ) );
 763
 764     my $dbh = C4::Context->dbh;
 765     my $sth;
 766
 767     if ($cr_id) {
 768         my $query = "
 769             SELECT * FROM course_reserves
 770             WHERE cr_id = ?
 771         ";
 772         $sth = $dbh->prepare($query);
 773         $sth->execute($cr_id);
 774     } else {
 775         my $query = "
 776             SELECT * FROM course_reserves
 777             WHERE course_id = ? AND ci_id = ?
 778         ";
 779         $sth = $dbh->prepare($query);
 780         $sth->execute( $course_id, $ci_id );
 781     }
 782
 783     my $course_reserve = $sth->fetchrow_hashref();
 784     return $course_reserve;
 785 }
 786
 787 =head2 ModCourseReserve
 788
 789     $id = ModCourseReserve( %params );
 790
 791 =cut
 792
 793 sub ModCourseReserve {
 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 $staff_note  = $params{'staff_note'};
 800     my $public_note = $params{'public_note'};
 801
 802     return unless ( $course_id && $ci_id );
 803
 804     my $course_reserve = GetCourseReserve( course_id => $course_id, ci_id => $ci_id );
 805     my $cr_id;
 806
 807     my $dbh = C4::Context->dbh;
 808
 809     if ($course_reserve) {
 810         $cr_id = $course_reserve->{'cr_id'};
 811
 812         my $query = "
 813             UPDATE course_reserves
 814             SET staff_note = ?, public_note = ?
 815             WHERE cr_id = ?
 816         ";
 817         $dbh->do( $query, undef, $staff_note, $public_note, $cr_id );
 818     } else {
 819         my $query = "
 820             INSERT INTO course_reserves SET
 821             course_id = ?,
 822             ci_id = ?,
 823             staff_note = ?,
 824             public_note = ?
 825         ";
 826         $dbh->do( $query, undef, $course_id, $ci_id, $staff_note, $public_note );
 827         $cr_id = $dbh->last_insert_id( undef, undef, 'course_reserves', 'cr_id' );
 828     }
 829
 830     EnableOrDisableCourseItem(
 831         ci_id   => $params{'ci_id'},
 832     );
 833
 834     return $cr_id;
 835 }
 836
 837 =head2 GetCourseReserves {
 838
 839   $course_reserves = GetCourseReserves( %params );
 840
 841   Required:
 842       course_id OR ci_id
 843   Optional:
 844       include_items   => 1,
 845       include_count   => 1,
 846       include_courses => 1,
 847
 848 =cut
 849
 850 sub GetCourseReserves {
 851     my (%params) = @_;
 852     warn identify_myself(%params) if $DEBUG;
 853
 854     my $course_id       = $params{'course_id'};
 855     my $ci_id           = $params{'ci_id'};
 856     my $include_items   = $params{'include_items'};
 857     my $include_count   = $params{'include_count'};
 858     my $include_courses = $params{'include_courses'};
 859
 860     return unless ( $course_id || $ci_id );
 861
 862     my $field = ($course_id) ? 'course_id' : 'ci_id';
 863     my $value = ($course_id) ? $course_id  : $ci_id;
 864
 865     my $query = "
 866         SELECT cr.*, ci.itemnumber
 867         FROM course_reserves cr, course_items ci
 868         WHERE cr.$field = ?
 869         AND cr.ci_id = ci.ci_id
 870     ";
 871     my $dbh = C4::Context->dbh;
 872     my $sth = $dbh->prepare($query);
 873     $sth->execute($value);
 874
 875     my $course_reserves = $sth->fetchall_arrayref( {} );
 876
 877     if ($include_items) {
 878         foreach my $cr (@$course_reserves) {
 879             my $item = Koha::Items->find( $cr->{itemnumber} );
 880             my $biblio = $item->biblio;
 881             my $biblioitem = $biblio->biblioitem;
 882             $cr->{'course_item'} = GetCourseItem( ci_id => $cr->{'ci_id'} );
 883             $cr->{'item'}        = $item;
 884             $cr->{'biblio'}      = $biblio;
 885             $cr->{'biblioitem'}  = $biblioitem;
 886             $cr->{'issue'}       = GetOpenIssue( $cr->{'itemnumber'} );
 887         }
 888     }
 889
 890     if ($include_count) {
 891         foreach my $cr (@$course_reserves) {
 892             $cr->{'reserves_count'} = CountCourseReservesForItem( ci_id => $cr->{'ci_id'} );
 893         }
 894     }
 895
 896     if ($include_courses) {
 897         foreach my $cr (@$course_reserves) {
 898             $cr->{'courses'} = GetCourses( itemnumber => $cr->{'itemnumber'} );
 899         }
 900     }
 901
 902     return $course_reserves;
 903 }
 904
 905 =head2 DelCourseReserve {
 906
 907   DelCourseReserve( cr_id => $cr_id );
 908
 909 =cut
 910
 911 sub DelCourseReserve {
 912     my (%params) = @_;
 913     warn identify_myself(%params) if $DEBUG;
 914
 915     my $cr_id = $params{'cr_id'};
 916
 917     return unless ($cr_id);
 918
 919     my $dbh = C4::Context->dbh;
 920
 921     my $course_reserve = GetCourseReserve( cr_id => $cr_id );
 922
 923     my $query = "
 924         DELETE FROM course_reserves
 925         WHERE cr_id = ?
 926     ";
 927     $dbh->do( $query, undef, $cr_id );
 928
 929     ## If there are no other course reserves for this item
 930     ## delete the course_item as well
 931     unless ( CountCourseReservesForItem( ci_id => $course_reserve->{'ci_id'} ) ) {
 932         DelCourseItem( ci_id => $course_reserve->{'ci_id'} );
 933     }
 934
 935 }
 936
 937 =head2 GetItemCourseReservesInfo
 938
 939     my $arrayref = GetItemCourseReservesInfo( itemnumber => $itemnumber );
 940
 941     For a given item, returns an arrayref of reserves hashrefs,
 942     with a course hashref under the key 'course'
 943
 944 =cut
 945
 946 sub GetItemCourseReservesInfo {
 947     my (%params) = @_;
 948     warn identify_myself(%params) if $DEBUG;
 949
 950     my $itemnumber = $params{'itemnumber'};
 951
 952     return unless ($itemnumber);
 953
 954     my $course_item = GetCourseItem( itemnumber => $itemnumber );
 955
 956     return unless ( keys %$course_item );
 957
 958     my $course_reserves = GetCourseReserves( ci_id => $course_item->{'ci_id'} );
 959
 960     foreach my $cr (@$course_reserves) {
 961         $cr->{'course'} = GetCourse( $cr->{'course_id'} );
 962     }
 963
 964     return $course_reserves;
 965 }
 966
 967 =head2 CountCourseReservesForItem
 968
 969     $bool = CountCourseReservesForItem( %params );
 970
 971     ci_id - course_item id
 972     OR
 973     itemnumber - course_item itemnumber
 974
 975     enabled = 'yes' or 'no'
 976     Optional, if not supplied, counts reserves
 977     for both enabled and disabled courses
 978
 979 =cut
 980
 981 sub CountCourseReservesForItem {
 982     my (%params) = @_;
 983     warn identify_myself(%params) if $DEBUG;
 984
 985     my $ci_id      = $params{'ci_id'};
 986     my $itemnumber = $params{'itemnumber'};
 987     my $enabled    = $params{'enabled'};
 988
 989     return unless ( $ci_id || $itemnumber );
 990
 991     my $course_item = GetCourseItem( ci_id => $ci_id, itemnumber => $itemnumber );
 992
 993     my @params = ( $course_item->{'ci_id'} );
 994     push( @params, $enabled ) if ($enabled);
 995
 996     my $query = "
 997         SELECT COUNT(*) AS count
 998         FROM course_reserves cr
 999         LEFT JOIN courses c ON ( c.course_id = cr.course_id )
1000         WHERE ci_id = ?
1001     ";
1002     $query .= "AND c.enabled = ?" if ($enabled);
1003
1004     my $dbh = C4::Context->dbh;
1005     my $sth = $dbh->prepare($query);
1006     $sth->execute(@params);
1007
1008     my $row = $sth->fetchrow_hashref();
1009
1010     return $row->{'count'};
1011 }
1012
1013 =head2 SearchCourses
1014
1015     my $courses = SearchCourses( term => $search_term, enabled => 'yes' );
1016
1017 =cut
1018
1019 sub SearchCourses {
1020     my (%params) = @_;
1021     warn identify_myself(%params) if $DEBUG;
1022
1023     my $term = $params{'term'};
1024
1025     my $enabled = $params{'enabled'} || '%';
1026
1027     my @params;
1028     my $query = "SELECT c.* FROM courses c";
1029
1030     $query .= "
1031         LEFT JOIN course_instructors ci
1032             ON ( c.course_id = ci.course_id )
1033         LEFT JOIN borrowers b
1034             ON ( ci.borrowernumber = b.borrowernumber )
1035         LEFT JOIN authorised_values av
1036             ON ( c.department = av.authorised_value )
1037         WHERE
1038             ( av.category = 'DEPARTMENT' OR av.category = 'TERM' )
1039             AND
1040             (
1041                 department LIKE ? OR
1042                 course_number LIKE ? OR
1043                 section LIKE ? OR
1044                 course_name LIKE ? OR
1045                 term LIKE ? OR
1046                 public_note LIKE ? OR
1047                 CONCAT(surname,' ',firstname) LIKE ? OR
1048                 CONCAT(firstname,' ',surname) LIKE ? OR
1049                 lib LIKE ? OR
1050                 lib_opac LIKE ?
1051            )
1052            AND
1053            c.enabled LIKE ?
1054         GROUP BY c.course_id
1055     ";
1056
1057     $term //= '';
1058     $term   = "%$term%";
1059     @params = ($term) x 10;
1060
1061     $query .= " ORDER BY department, course_number, section, term, course_name ";
1062
1063     my $dbh = C4::Context->dbh;
1064     my $sth = $dbh->prepare($query);
1065
1066     $sth->execute( @params, $enabled );
1067
1068     my $courses = $sth->fetchall_arrayref( {} );
1069
1070     foreach my $c (@$courses) {
1071         $c->{'instructors'} = GetCourseInstructors( $c->{'course_id'} );
1072     }
1073
1074     return $courses;
1075 }
1076
1077 sub whoami  { ( caller(1) )[3] }
1078 sub whowasi { ( caller(2) )[3] }
1079
1080 sub stringify_params {
1081     my (%params) = @_;
1082
1083     my $string = "\n";
1084
1085     foreach my $key ( keys %params ) {
1086         $string .= "    $key => " . $params{$key} . "\n";
1087     }
1088
1089     return "( $string )";
1090 }
1091
1092 sub identify_myself {
1093     my (%params) = @_;
1094
1095     return whowasi() . stringify_params(%params);
1096 }
1097
1098 1;
1099
1100 =head1 AUTHOR
1101
1102 Kyle M Hall <kyle@bywatersolutions.com>
1103
1104 =cut