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