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