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