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::Biblio qw(GetBiblioFromItemNumber);
  25 use C4::Circulation qw(GetOpenIssue);
  26
  27 use vars qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS $DEBUG @FIELDS);
  28
  29 BEGIN {
  30     require Exporter;
  31     @ISA       = qw(Exporter);
  32     @EXPORT_OK = qw(
  33       &GetCourse
  34       &ModCourse
  35       &GetCourses
  36       &DelCourse
  37
  38       &GetCourseInstructors
  39       &ModCourseInstructors
  40
  41       &GetCourseItem
  42       &ModCourseItem
  43
  44       &GetCourseReserve
  45       &ModCourseReserve
  46       &GetCourseReserves
  47       &DelCourseReserve
  48
  49       &SearchCourses
  50
  51       &GetItemCourseReservesInfo
  52     );
  53     %EXPORT_TAGS = ( 'all' => \@EXPORT_OK );
  54
  55     $DEBUG = 0;
  56     @FIELDS = ( 'itype', 'ccode', 'holdingbranch', 'location' );
  57 }
  58
  59 =head1 NAME
  60
  61 C4::CourseReserves - Koha course reserves module
  62
  63 =head1 SYNOPSIS
  64
  65 use C4::CourseReserves;
  66
  67 =head1 DESCRIPTION
  68
  69 This module deals with course reserves.
  70
  71 =head1 FUNCTIONS
  72
  73 =head2 GetCourse
  74
  75     $course = GetCourse( $course_id );
  76
  77 =cut
  78
  79 sub GetCourse {
  80     my ($course_id) = @_;
  81     warn whoami() . "( $course_id )" if $DEBUG;
  82
  83     my $query = "SELECT * FROM courses WHERE course_id = ?";
  84     my $dbh   = C4::Context->dbh;
  85     my $sth   = $dbh->prepare($query);
  86     $sth->execute($course_id);
  87
  88     my $course = $sth->fetchrow_hashref();
  89
  90     $query = "
  91         SELECT b.* FROM course_instructors ci
  92         LEFT JOIN borrowers b ON ( ci.borrowernumber = b.borrowernumber )
  93         WHERE course_id =  ?
  94     ";
  95     $sth = $dbh->prepare($query);
  96     $sth->execute($course_id);
  97     $course->{'instructors'} = $sth->fetchall_arrayref( {} );
  98
  99     return $course;
 100 }
 101
 102 =head2 ModCourse
 103
 104     ModCourse( [ course_id => $id ] [, course_name => $course_name ] [etc...] );
 105
 106 =cut
 107
 108 sub ModCourse {
 109     my (%params) = @_;
 110     warn identify_myself(%params) if $DEBUG;
 111
 112     my $dbh = C4::Context->dbh;
 113
 114     my $course_id;
 115     if ( defined $params{'course_id'} ) {
 116         $course_id = $params{'course_id'};
 117         delete $params{'course_id'};
 118     }
 119
 120     my @query_keys;
 121     my @query_values;
 122
 123     my $query;
 124
 125     $query .= ($course_id) ? ' UPDATE ' : ' INSERT ';
 126     $query .= ' courses SET ';
 127
 128     foreach my $key ( keys %params ) {
 129         push( @query_keys,   "$key=?" );
 130         push( @query_values, $params{$key} );
 131     }
 132     $query .= join( ',', @query_keys );
 133
 134     if ($course_id) {
 135         $query .= " WHERE course_id = ?";
 136         push( @query_values, $course_id );
 137     }
 138
 139     $dbh->do( $query, undef, @query_values );
 140
 141     $course_id = $course_id
 142       || $dbh->last_insert_id( undef, undef, 'courses', 'course_id' );
 143
 144     EnableOrDisableCourseItems(
 145         course_id => $course_id,
 146         enabled   => $params{'enabled'}
 147     );
 148
 149     return $course_id;
 150 }
 151
 152 =head2 GetCourses
 153
 154   @courses = GetCourses( [ fieldname => $value ] [, fieldname2 => $value2 ] [etc...] );
 155
 156 =cut
 157
 158 sub GetCourses {
 159     my (%params) = @_;
 160     warn identify_myself(%params) if $DEBUG;
 161
 162     my @query_keys;
 163     my @query_values;
 164
 165     my $query = "
 166         SELECT courses.*
 167         FROM courses
 168         LEFT JOIN course_reserves ON course_reserves.course_id = courses.course_id
 169         LEFT JOIN course_items ON course_items.ci_id = course_reserves.ci_id
 170     ";
 171
 172     if ( keys %params ) {
 173
 174         $query .= " WHERE ";
 175
 176         foreach my $key ( keys %params ) {
 177             push( @query_keys,   " $key LIKE ? " );
 178             push( @query_values, $params{$key} );
 179         }
 180
 181         $query .= join( ' AND ', @query_keys );
 182     }
 183
 184     $query .= " GROUP BY courses.course_id ";
 185
 186     my $dbh = C4::Context->dbh;
 187     my $sth = $dbh->prepare($query);
 188     $sth->execute(@query_values);
 189
 190     my $courses = $sth->fetchall_arrayref( {} );
 191
 192     foreach my $c (@$courses) {
 193         $c->{'instructors'} = GetCourseInstructors( $c->{'course_id'} );
 194     }
 195
 196     return $courses;
 197 }
 198
 199 =head2 DelCourse
 200
 201   DelCourse( $course_id );
 202
 203 =cut
 204
 205 sub DelCourse {
 206     my ($course_id) = @_;
 207
 208     my $course_reserves = GetCourseReserves( course_id => $course_id );
 209
 210     foreach my $res (@$course_reserves) {
 211         DelCourseReserve( cr_id => $res->{'cr_id'} );
 212     }
 213
 214     my $query = "
 215         DELETE FROM course_instructors
 216         WHERE course_id = ?
 217     ";
 218     C4::Context->dbh->do( $query, undef, $course_id );
 219
 220     $query = "
 221         DELETE FROM courses
 222         WHERE course_id = ?
 223     ";
 224     C4::Context->dbh->do( $query, undef, $course_id );
 225 }
 226
 227 =head2 EnableOrDisableCourseItems
 228
 229   EnableOrDisableCourseItems( course_id => $course_id, enabled => $enabled );
 230
 231   For each item on reserve for this course,
 232   if the course item has no active course reserves,
 233   swap the fields for the item to make it 'normal'
 234   again.
 235
 236   enabled => 'yes' to enable course items
 237   enabled => 'no' to disable course items
 238
 239 =cut
 240
 241 sub EnableOrDisableCourseItems {
 242     my (%params) = @_;
 243     warn identify_myself(%params) if $DEBUG;
 244
 245     my $course_id = $params{'course_id'};
 246     my $enabled = $params{'enabled'} || 0;
 247
 248     my $lookfor = ( $enabled eq 'yes' ) ? 'no' : 'yes';
 249
 250     return unless ( $course_id && $enabled );
 251     return unless ( $enabled eq 'yes' || $enabled eq 'no' );
 252
 253     my $course_reserves = GetCourseReserves( course_id => $course_id );
 254
 255     if ( $enabled eq 'yes' ) {
 256         foreach my $course_reserve (@$course_reserves) {
 257             if (CountCourseReservesForItem(
 258                     ci_id   => $course_reserve->{'ci_id'},
 259                     enabled => 'yes'
 260                 )
 261               ) {
 262                 EnableOrDisableCourseItem(
 263                     ci_id   => $course_reserve->{'ci_id'},
 264                 );
 265             }
 266         }
 267     }
 268     if ( $enabled eq 'no' ) {
 269         foreach my $course_reserve (@$course_reserves) {
 270             unless (
 271                 CountCourseReservesForItem(
 272                     ci_id   => $course_reserve->{'ci_id'},
 273                     enabled => 'yes'
 274                 )
 275               ) {
 276                 EnableOrDisableCourseItem(
 277                     ci_id   => $course_reserve->{'ci_id'},
 278                 );
 279             }
 280         }
 281     }
 282 }
 283
 284 =head2 EnableOrDisableCourseItem
 285
 286     EnableOrDisableCourseItem( ci_id => $ci_id );
 287
 288 =cut
 289
 290 sub EnableOrDisableCourseItem {
 291     my (%params) = @_;
 292     warn identify_myself(%params) if $DEBUG;
 293
 294     my $ci_id   = $params{'ci_id'};
 295
 296     return unless ( $ci_id );
 297
 298     my $course_item = GetCourseItem( ci_id => $ci_id );
 299
 300     my $info = GetItemCourseReservesInfo( itemnumber => $course_item->{itemnumber} );
 301
 302     my $enabled = any { $_->{course}->{enabled} eq 'yes' } @$info;
 303     $enabled = $enabled ? 'yes' : 'no';
 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     EnableOrDisableCourseItem(
 832         ci_id   => $params{'ci_id'},
 833     );
 834
 835     return $cr_id;
 836 }
 837
 838 =head2 GetCourseReserves {
 839
 840   $course_reserves = GetCourseReserves( %params );
 841
 842   Required:
 843       course_id OR ci_id
 844   Optional:
 845       include_items   => 1,
 846       include_count   => 1,
 847       include_courses => 1,
 848
 849 =cut
 850
 851 sub GetCourseReserves {
 852     my (%params) = @_;
 853     warn identify_myself(%params) if $DEBUG;
 854
 855     my $course_id       = $params{'course_id'};
 856     my $ci_id           = $params{'ci_id'};
 857     my $include_items   = $params{'include_items'};
 858     my $include_count   = $params{'include_count'};
 859     my $include_courses = $params{'include_courses'};
 860
 861     return unless ( $course_id || $ci_id );
 862
 863     my $field = ($course_id) ? 'course_id' : 'ci_id';
 864     my $value = ($course_id) ? $course_id  : $ci_id;
 865
 866     my $query = "
 867         SELECT cr.*, ci.itemnumber
 868         FROM course_reserves cr, course_items ci
 869         WHERE cr.$field = ?
 870         AND cr.ci_id = ci.ci_id
 871     ";
 872     my $dbh = C4::Context->dbh;
 873     my $sth = $dbh->prepare($query);
 874     $sth->execute($value);
 875
 876     my $course_reserves = $sth->fetchall_arrayref( {} );
 877
 878     if ($include_items) {
 879         foreach my $cr (@$course_reserves) {
 880             $cr->{'course_item'} = GetCourseItem( ci_id => $cr->{'ci_id'} );
 881             $cr->{'item'}        = GetBiblioFromItemNumber( $cr->{'itemnumber'} );
 882             $cr->{'issue'}       = GetOpenIssue( $cr->{'itemnumber'} );
 883         }
 884     }
 885
 886     if ($include_count) {
 887         foreach my $cr (@$course_reserves) {
 888             $cr->{'reserves_count'} = CountCourseReservesForItem( ci_id => $cr->{'ci_id'} );
 889         }
 890     }
 891
 892     if ($include_courses) {
 893         foreach my $cr (@$course_reserves) {
 894             $cr->{'courses'} = GetCourses( itemnumber => $cr->{'itemnumber'} );
 895         }
 896     }
 897
 898     return $course_reserves;
 899 }
 900
 901 =head2 DelCourseReserve {
 902
 903   DelCourseReserve( cr_id => $cr_id );
 904
 905 =cut
 906
 907 sub DelCourseReserve {
 908     my (%params) = @_;
 909     warn identify_myself(%params) if $DEBUG;
 910
 911     my $cr_id = $params{'cr_id'};
 912
 913     return unless ($cr_id);
 914
 915     my $dbh = C4::Context->dbh;
 916
 917     my $course_reserve = GetCourseReserve( cr_id => $cr_id );
 918
 919     my $query = "
 920         DELETE FROM course_reserves
 921         WHERE cr_id = ?
 922     ";
 923     $dbh->do( $query, undef, $cr_id );
 924
 925     ## If there are no other course reserves for this item
 926     ## delete the course_item as well
 927     unless ( CountCourseReservesForItem( ci_id => $course_reserve->{'ci_id'} ) ) {
 928         DelCourseItem( ci_id => $course_reserve->{'ci_id'} );
 929     }
 930
 931 }
 932
 933 =head2 GetItemCourseReservesInfo
 934
 935     my $arrayref = GetItemCourseReservesInfo( itemnumber => $itemnumber );
 936
 937     For a given item, returns an arrayref of reserves hashrefs,
 938     with a course hashref under the key 'course'
 939
 940 =cut
 941
 942 sub GetItemCourseReservesInfo {
 943     my (%params) = @_;
 944     warn identify_myself(%params) if $DEBUG;
 945
 946     my $itemnumber = $params{'itemnumber'};
 947
 948     return unless ($itemnumber);
 949
 950     my $course_item = GetCourseItem( itemnumber => $itemnumber );
 951
 952     return unless ( keys %$course_item );
 953
 954     my $course_reserves = GetCourseReserves( ci_id => $course_item->{'ci_id'} );
 955
 956     foreach my $cr (@$course_reserves) {
 957         $cr->{'course'} = GetCourse( $cr->{'course_id'} );
 958     }
 959
 960     return $course_reserves;
 961 }
 962
 963 =head2 CountCourseReservesForItem
 964
 965     $bool = CountCourseReservesForItem( %params );
 966
 967     ci_id - course_item id
 968     OR
 969     itemnumber - course_item itemnumber
 970
 971     enabled = 'yes' or 'no'
 972     Optional, if not supplied, counts reserves
 973     for both enabled and disabled courses
 974
 975 =cut
 976
 977 sub CountCourseReservesForItem {
 978     my (%params) = @_;
 979     warn identify_myself(%params) if $DEBUG;
 980
 981     my $ci_id      = $params{'ci_id'};
 982     my $itemnumber = $params{'itemnumber'};
 983     my $enabled    = $params{'enabled'};
 984
 985     return unless ( $ci_id || $itemnumber );
 986
 987     my $course_item = GetCourseItem( ci_id => $ci_id, itemnumber => $itemnumber );
 988
 989     my @params = ( $course_item->{'ci_id'} );
 990     push( @params, $enabled ) if ($enabled);
 991
 992     my $query = "
 993         SELECT COUNT(*) AS count
 994         FROM course_reserves cr
 995         LEFT JOIN courses c ON ( c.course_id = cr.course_id )
 996         WHERE ci_id = ?
 997     ";
 998     $query .= "AND c.enabled = ?" if ($enabled);
 999
1000     my $dbh = C4::Context->dbh;
1001     my $sth = $dbh->prepare($query);
1002     $sth->execute(@params);
1003
1004     my $row = $sth->fetchrow_hashref();
1005
1006     return $row->{'count'};
1007 }
1008
1009 =head2 SearchCourses
1010
1011     my $courses = SearchCourses( term => $search_term, enabled => 'yes' );
1012
1013 =cut
1014
1015 sub SearchCourses {
1016     my (%params) = @_;
1017     warn identify_myself(%params) if $DEBUG;
1018
1019     my $term = $params{'term'};
1020
1021     my $enabled = $params{'enabled'} || '%';
1022
1023     my @params;
1024     my $query = "SELECT c.* FROM courses c";
1025
1026     $query .= "
1027         LEFT JOIN course_instructors ci
1028             ON ( c.course_id = ci.course_id )
1029         LEFT JOIN borrowers b
1030             ON ( ci.borrowernumber = b.borrowernumber )
1031         LEFT JOIN authorised_values av
1032             ON ( c.department = av.authorised_value )
1033         WHERE
1034             ( av.category = 'DEPARTMENT' OR av.category = 'TERM' )
1035             AND
1036             (
1037                 department LIKE ? OR
1038                 course_number LIKE ? OR
1039                 section LIKE ? OR
1040                 course_name LIKE ? OR
1041                 term LIKE ? OR
1042                 public_note LIKE ? OR
1043                 CONCAT(surname,' ',firstname) LIKE ? OR
1044                 CONCAT(firstname,' ',surname) LIKE ? OR
1045                 lib LIKE ? OR
1046                 lib_opac LIKE ?
1047            )
1048            AND
1049            c.enabled LIKE ?
1050         GROUP BY c.course_id
1051     ";
1052
1053     $term //= '';
1054     $term   = "%$term%";
1055     @params = ($term) x 10;
1056
1057     $query .= " ORDER BY department, course_number, section, term, course_name ";
1058
1059     my $dbh = C4::Context->dbh;
1060     my $sth = $dbh->prepare($query);
1061
1062     $sth->execute( @params, $enabled );
1063
1064     my $courses = $sth->fetchall_arrayref( {} );
1065
1066     foreach my $c (@$courses) {
1067         $c->{'instructors'} = GetCourseInstructors( $c->{'course_id'} );
1068     }
1069
1070     return $courses;
1071 }
1072
1073 sub whoami  { ( caller(1) )[3] }
1074 sub whowasi { ( caller(2) )[3] }
1075
1076 sub stringify_params {
1077     my (%params) = @_;
1078
1079     my $string = "\n";
1080
1081     foreach my $key ( keys %params ) {
1082         $string .= "    $key => " . $params{$key} . "\n";
1083     }
1084
1085     return "( $string )";
1086 }
1087
1088 sub identify_myself {
1089     my (%params) = @_;
1090
1091     return whowasi() . stringify_params(%params);
1092 }
1093
1094 1;
1095
1096 =head1 AUTHOR
1097
1098 Kyle M Hall <kyle@bywatersolutions.com>
1099
1100 =cut