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 undef 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     $course_item->update( { %data, %enabled } );
 539     if ( $course_item->is_enabled ) {
 540         my $item_fields = {};
 541         $item_fields->{itype}         = $course_item->itype         if $course_item->itype_enabled;
 542         $item_fields->{ccode}         = $course_item->ccode         if $course_item->ccode_enabled;
 543         $item_fields->{location}      = $course_item->location      if $course_item->location_enabled;
 544         $item_fields->{homebranch}    = $course_item->homebranch    if $course_item->homebranch_enabled;
 545         $item_fields->{holdingbranch} = $course_item->holdingbranch if $course_item->holdingbranch_enabled;
 546
 547         Koha::Items->find( $course_item->itemnumber )
 548                    ->set( $item_fields )
 549                    ->store
 550             if keys %$item_fields;
 551
 552     }
 553
 554 }
 555
 556 =head2 _RevertFields
 557
 558     _RevertFields( ci_id => $ci_id, fields => \@fields_to_revert );
 559
 560     Copies fields from course item storage back to the actual item
 561
 562 =cut
 563
 564 sub _RevertFields {
 565     my (%params) = @_;
 566     warn identify_myself(%params) if $DEBUG;
 567
 568     my $ci_id = $params{'ci_id'};
 569
 570     return unless $ci_id;
 571
 572     my $course_item = Koha::Course::Items->find( $ci_id );
 573
 574     my $item_fields = {};
 575     $item_fields->{itype}         = $course_item->itype_storage         if $course_item->itype_enabled;
 576     $item_fields->{ccode}         = $course_item->ccode_storage         if $course_item->ccode_enabled;
 577     $item_fields->{location}      = $course_item->location_storage      if $course_item->location_enabled;
 578     $item_fields->{homebranch} = $course_item->homebranch_storage if $course_item->homebranch_enabled;
 579     $item_fields->{holdingbranch} = $course_item->holdingbranch_storage if $course_item->holdingbranch_enabled;
 580
 581     Koha::Items->find( $course_item->itemnumber )
 582                ->set( $item_fields )
 583                ->store
 584         if keys %$item_fields;
 585
 586     $course_item->itype_storage(undef);
 587     $course_item->ccode_storage(undef);
 588     $course_item->location_storage(undef);
 589     $course_item->homebranch_storage(undef);
 590     $course_item->holdingbranch_storage(undef);
 591     $course_item->store();
 592 }
 593
 594 =head2 _SwapAllFields
 595
 596     _SwapAllFields( $ci_id );
 597
 598 =cut
 599
 600 sub _SwapAllFields {
 601     my ( $ci_id, $enabled ) = @_;
 602     warn "C4::CourseReserves::_SwapFields( $ci_id )" if $DEBUG;
 603
 604     my $course_item = Koha::Course::Items->find( $ci_id );
 605     my $item = Koha::Items->find( $course_item->itemnumber );
 606
 607     if ( $enabled eq 'yes' ) { # Copy item fields to course item storage, course item fields to item
 608         $course_item->itype_storage( $item->effective_itemtype )    if $course_item->itype_enabled;
 609         $course_item->ccode_storage( $item->ccode )                 if $course_item->ccode_enabled;
 610         $course_item->location_storage( $item->location )           if $course_item->location_enabled;
 611         $course_item->homebranch_storage( $item->homebranch )       if $course_item->homebranch_enabled;
 612         $course_item->holdingbranch_storage( $item->holdingbranch ) if $course_item->holdingbranch_enabled;
 613         $course_item->store();
 614
 615         my $item_fields = {};
 616         $item_fields->{itype}         = $course_item->itype         if $course_item->itype_enabled;
 617         $item_fields->{ccode}         = $course_item->ccode         if $course_item->ccode_enabled;
 618         $item_fields->{location}      = $course_item->location      if $course_item->location_enabled;
 619         $item_fields->{homebranch}    = $course_item->homebranch    if $course_item->homebranch_enabled;
 620         $item_fields->{holdingbranch} = $course_item->holdingbranch if $course_item->holdingbranch_enabled;
 621
 622         Koha::Items->find( $course_item->itemnumber )
 623                    ->set( $item_fields )
 624                    ->store
 625             if keys %$item_fields;
 626
 627     } else { # Copy course item storage to item
 628         my $item_fields = {};
 629         $item_fields->{itype}         = $course_item->itype_storage         if $course_item->itype_enabled;
 630         $item_fields->{ccode}         = $course_item->ccode_storage         if $course_item->ccode_enabled;
 631         $item_fields->{location}      = $course_item->location_storage      if $course_item->location_enabled;
 632         $item_fields->{homebranch}    = $course_item->homebranch_storage    if $course_item->homebranch_enabled;
 633         $item_fields->{holdingbranch} = $course_item->holdingbranch_storage if $course_item->holdingbranch_enabled;
 634
 635         Koha::Items->find( $course_item->itemnumber )
 636                    ->set( $item_fields )
 637                    ->store
 638             if keys %$item_fields;
 639
 640         $course_item->itype_storage(undef);
 641         $course_item->ccode_storage(undef);
 642         $course_item->location_storage(undef);
 643         $course_item->homebranch_storage(undef);
 644         $course_item->holdingbranch_storage(undef);
 645         $course_item->store();
 646     }
 647 }
 648
 649 =head2 GetCourseItems {
 650
 651   $course_items = GetCourseItems(
 652       [course_id => $course_id]
 653       [, itemnumber => $itemnumber ]
 654   );
 655
 656 =cut
 657
 658 sub GetCourseItems {
 659     my (%params) = @_;
 660     warn identify_myself(%params) if $DEBUG;
 661
 662     my $course_id  = $params{'course_id'};
 663     my $itemnumber = $params{'itemnumber'};
 664
 665     return unless ($course_id);
 666
 667     my @query_keys;
 668     my @query_values;
 669
 670     my $query = "SELECT * FROM course_items";
 671
 672     if ( keys %params ) {
 673
 674         $query .= " WHERE ";
 675
 676         foreach my $key ( keys %params ) {
 677             push( @query_keys,   " $key LIKE ? " );
 678             push( @query_values, $params{$key} );
 679         }
 680
 681         $query .= join( ' AND ', @query_keys );
 682     }
 683
 684     my $dbh = C4::Context->dbh;
 685     my $sth = $dbh->prepare($query);
 686     $sth->execute(@query_values);
 687
 688     return $sth->fetchall_arrayref( {} );
 689 }
 690
 691 =head2 DelCourseItem {
 692
 693   DelCourseItem( ci_id => $cr_id );
 694
 695 =cut
 696
 697 sub DelCourseItem {
 698     my (%params) = @_;
 699     warn identify_myself(%params) if $DEBUG;
 700
 701     my $ci_id = $params{'ci_id'};
 702
 703     return unless ($ci_id);
 704
 705     my $course_item = Koha::Course::Items->find( $ci_id );
 706     return unless $course_item;
 707
 708     _RevertFields( ci_id => $ci_id ) if $course_item->enabled eq 'yes';
 709
 710     my $query = "
 711         DELETE FROM course_items
 712         WHERE ci_id = ?
 713     ";
 714     C4::Context->dbh->do( $query, undef, $ci_id );
 715 }
 716
 717 =head2 GetCourseReserve {
 718
 719   $course_item = GetCourseReserve( %params );
 720
 721 =cut
 722
 723 sub GetCourseReserve {
 724     my (%params) = @_;
 725     warn identify_myself(%params) if $DEBUG;
 726
 727     my $cr_id     = $params{'cr_id'};
 728     my $course_id = $params{'course_id'};
 729     my $ci_id     = $params{'ci_id'};
 730
 731     return unless ( $cr_id || ( $course_id && $ci_id ) );
 732
 733     my $dbh = C4::Context->dbh;
 734     my $sth;
 735
 736     if ($cr_id) {
 737         my $query = "
 738             SELECT * FROM course_reserves
 739             WHERE cr_id = ?
 740         ";
 741         $sth = $dbh->prepare($query);
 742         $sth->execute($cr_id);
 743     } else {
 744         my $query = "
 745             SELECT * FROM course_reserves
 746             WHERE course_id = ? AND ci_id = ?
 747         ";
 748         $sth = $dbh->prepare($query);
 749         $sth->execute( $course_id, $ci_id );
 750     }
 751
 752     my $course_reserve = $sth->fetchrow_hashref();
 753     return $course_reserve;
 754 }
 755
 756 =head2 ModCourseReserve
 757
 758     $id = ModCourseReserve( %params );
 759
 760 =cut
 761
 762 sub ModCourseReserve {
 763     my (%params) = @_;
 764     warn identify_myself(%params) if $DEBUG;
 765
 766     my $course_id   = $params{'course_id'};
 767     my $ci_id       = $params{'ci_id'};
 768     my $staff_note  = $params{'staff_note'};
 769     my $public_note = $params{'public_note'};
 770
 771     return unless ( $course_id && $ci_id );
 772
 773     my $course_reserve = GetCourseReserve( course_id => $course_id, ci_id => $ci_id );
 774     my $cr_id;
 775
 776     my $dbh = C4::Context->dbh;
 777
 778     if ($course_reserve) {
 779         $cr_id = $course_reserve->{'cr_id'};
 780
 781         my $query = "
 782             UPDATE course_reserves
 783             SET staff_note = ?, public_note = ?
 784             WHERE cr_id = ?
 785         ";
 786         $dbh->do( $query, undef, $staff_note, $public_note, $cr_id );
 787     } else {
 788         my $query = "
 789             INSERT INTO course_reserves SET
 790             course_id = ?,
 791             ci_id = ?,
 792             staff_note = ?,
 793             public_note = ?
 794         ";
 795         $dbh->do( $query, undef, $course_id, $ci_id, $staff_note, $public_note );
 796         $cr_id = $dbh->last_insert_id( undef, undef, 'course_reserves', 'cr_id' );
 797     }
 798
 799     EnableOrDisableCourseItem(
 800         ci_id   => $params{'ci_id'},
 801     );
 802
 803     return $cr_id;
 804 }
 805
 806 =head2 GetCourseReserves {
 807
 808   $course_reserves = GetCourseReserves( %params );
 809
 810   Required:
 811       course_id OR ci_id
 812   Optional:
 813       include_items   => 1,
 814       include_count   => 1,
 815       include_courses => 1,
 816
 817 =cut
 818
 819 sub GetCourseReserves {
 820     my (%params) = @_;
 821     warn identify_myself(%params) if $DEBUG;
 822
 823     my $course_id       = $params{'course_id'};
 824     my $ci_id           = $params{'ci_id'};
 825     my $include_items   = $params{'include_items'};
 826     my $include_count   = $params{'include_count'};
 827     my $include_courses = $params{'include_courses'};
 828
 829     return unless ( $course_id || $ci_id );
 830
 831     my $field = ($course_id) ? 'course_id' : 'ci_id';
 832     my $value = ($course_id) ? $course_id  : $ci_id;
 833
 834     my $query = "
 835         SELECT cr.*, ci.itemnumber
 836         FROM course_reserves cr, course_items ci
 837         WHERE cr.$field = ?
 838         AND cr.ci_id = ci.ci_id
 839     ";
 840     my $dbh = C4::Context->dbh;
 841     my $sth = $dbh->prepare($query);
 842     $sth->execute($value);
 843
 844     my $course_reserves = $sth->fetchall_arrayref( {} );
 845
 846     if ($include_items) {
 847         foreach my $cr (@$course_reserves) {
 848             my $item = Koha::Items->find( $cr->{itemnumber} );
 849             my $biblio = $item->biblio;
 850             my $biblioitem = $biblio->biblioitem;
 851             $cr->{'course_item'} = GetCourseItem( ci_id => $cr->{'ci_id'} );
 852             $cr->{'item'}        = $item;
 853             $cr->{'biblio'}      = $biblio;
 854             $cr->{'biblioitem'}  = $biblioitem;
 855             $cr->{'issue'}       = GetOpenIssue( $cr->{'itemnumber'} );
 856         }
 857     }
 858
 859     if ($include_count) {
 860         foreach my $cr (@$course_reserves) {
 861             $cr->{'reserves_count'} = CountCourseReservesForItem( ci_id => $cr->{'ci_id'} );
 862         }
 863     }
 864
 865     if ($include_courses) {
 866         foreach my $cr (@$course_reserves) {
 867             $cr->{'courses'} = GetCourses( itemnumber => $cr->{'itemnumber'} );
 868         }
 869     }
 870
 871     return $course_reserves;
 872 }
 873
 874 =head2 DelCourseReserve {
 875
 876   DelCourseReserve( cr_id => $cr_id );
 877
 878 =cut
 879
 880 sub DelCourseReserve {
 881     my (%params) = @_;
 882     warn identify_myself(%params) if $DEBUG;
 883
 884     my $cr_id = $params{'cr_id'};
 885
 886     return unless ($cr_id);
 887
 888     my $dbh = C4::Context->dbh;
 889
 890     my $course_reserve = GetCourseReserve( cr_id => $cr_id );
 891
 892     my $query = "
 893         DELETE FROM course_reserves
 894         WHERE cr_id = ?
 895     ";
 896     $dbh->do( $query, undef, $cr_id );
 897
 898     ## If there are no other course reserves for this item
 899     ## delete the course_item as well
 900     unless ( CountCourseReservesForItem( ci_id => $course_reserve->{'ci_id'} ) ) {
 901         DelCourseItem( ci_id => $course_reserve->{'ci_id'} );
 902     }
 903
 904 }
 905
 906 =head2 GetItemCourseReservesInfo
 907
 908     my $arrayref = GetItemCourseReservesInfo( itemnumber => $itemnumber );
 909
 910     For a given item, returns an arrayref of reserves hashrefs,
 911     with a course hashref under the key 'course'
 912
 913 =cut
 914
 915 sub GetItemCourseReservesInfo {
 916     my (%params) = @_;
 917     warn identify_myself(%params) if $DEBUG;
 918
 919     my $itemnumber = $params{'itemnumber'};
 920
 921     return unless ($itemnumber);
 922
 923     my $course_item = GetCourseItem( itemnumber => $itemnumber );
 924
 925     return unless ( keys %$course_item );
 926
 927     my $course_reserves = GetCourseReserves( ci_id => $course_item->{'ci_id'} );
 928
 929     foreach my $cr (@$course_reserves) {
 930         $cr->{'course'} = GetCourse( $cr->{'course_id'} );
 931     }
 932
 933     return $course_reserves;
 934 }
 935
 936 =head2 CountCourseReservesForItem
 937
 938     $bool = CountCourseReservesForItem( %params );
 939
 940     ci_id - course_item id
 941     OR
 942     itemnumber - course_item itemnumber
 943
 944     enabled = 'yes' or 'no'
 945     Optional, if not supplied, counts reserves
 946     for both enabled and disabled courses
 947
 948 =cut
 949
 950 sub CountCourseReservesForItem {
 951     my (%params) = @_;
 952     warn identify_myself(%params) if $DEBUG;
 953
 954     my $ci_id      = $params{'ci_id'};
 955     my $itemnumber = $params{'itemnumber'};
 956     my $enabled    = $params{'enabled'};
 957
 958     return unless ( $ci_id || $itemnumber );
 959
 960     my $course_item = GetCourseItem( ci_id => $ci_id, itemnumber => $itemnumber );
 961
 962     my @params = ( $course_item->{'ci_id'} );
 963     push( @params, $enabled ) if ($enabled);
 964
 965     my $query = "
 966         SELECT COUNT(*) AS count
 967         FROM course_reserves cr
 968         LEFT JOIN courses c ON ( c.course_id = cr.course_id )
 969         WHERE ci_id = ?
 970     ";
 971     $query .= "AND c.enabled = ?" if ($enabled);
 972
 973     my $dbh = C4::Context->dbh;
 974     my $sth = $dbh->prepare($query);
 975     $sth->execute(@params);
 976
 977     my $row = $sth->fetchrow_hashref();
 978
 979     return $row->{'count'};
 980 }
 981
 982 =head2 SearchCourses
 983
 984     my $courses = SearchCourses( term => $search_term, enabled => 'yes' );
 985
 986 =cut
 987
 988 sub SearchCourses {
 989     my (%params) = @_;
 990     warn identify_myself(%params) if $DEBUG;
 991
 992     my $term = $params{'term'};
 993
 994     my $enabled = $params{'enabled'} || '%';
 995
 996     my @params;
 997     my $query = "
 998         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
 999         FROM courses c
1000         LEFT JOIN course_instructors ci
1001             ON ( c.course_id = ci.course_id )
1002         LEFT JOIN borrowers b
1003             ON ( ci.borrowernumber = b.borrowernumber )
1004         LEFT JOIN authorised_values av
1005             ON ( c.department = av.authorised_value )
1006         WHERE
1007             ( av.category = 'DEPARTMENT' OR av.category = 'TERM' )
1008             AND
1009             (
1010                 department LIKE ? OR
1011                 course_number LIKE ? OR
1012                 section LIKE ? OR
1013                 course_name LIKE ? OR
1014                 term LIKE ? OR
1015                 public_note LIKE ? OR
1016                 CONCAT(surname,' ',firstname) LIKE ? OR
1017                 CONCAT(firstname,' ',surname) LIKE ? OR
1018                 lib LIKE ? OR
1019                 lib_opac LIKE ?
1020            )
1021            AND
1022            c.enabled LIKE ?
1023         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
1024     ";
1025
1026     $term //= '';
1027     $term   = "%$term%";
1028     @params = ($term) x 10;
1029
1030     $query .= " ORDER BY department, course_number, section, term, course_name ";
1031
1032     my $dbh = C4::Context->dbh;
1033     my $sth = $dbh->prepare($query);
1034
1035     $sth->execute( @params, $enabled );
1036
1037     my $courses = $sth->fetchall_arrayref( {} );
1038
1039     foreach my $c (@$courses) {
1040         $c->{'instructors'} = GetCourseInstructors( $c->{'course_id'} );
1041     }
1042
1043     return $courses;
1044 }
1045
1046 sub whoami  { ( caller(1) )[3] }
1047 sub whowasi { ( caller(2) )[3] }
1048
1049 sub stringify_params {
1050     my (%params) = @_;
1051
1052     my $string = "\n";
1053
1054     foreach my $key ( keys %params ) {
1055         $string .= "    $key => " . $params{$key} . "\n";
1056     }
1057
1058     return "( $string )";
1059 }
1060
1061 sub identify_myself {
1062     my (%params) = @_;
1063
1064     return whowasi() . stringify_params(%params);
1065 }
1066
1067 1;
1068
1069 =head1 AUTHOR
1070
1071 Kyle M Hall <kyle@bywatersolutions.com>
1072
1073 =cut