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