| Line 0
          
      
      
        Link Here | 
            
              |  |  | 1 | package Koha::CourseReserves; | 
            
              | 2 |  | 
            
              | 3 | # This file is part of Koha. | 
            
              | 4 | # | 
            
              | 5 | # Koha is free software; you can redistribute it and/or modify it under the | 
            
              | 6 | # terms of the GNU General Public License as published by the Free Software | 
            
              | 7 | # Foundation; either version 2 of the License, or (at your option) any later | 
            
              | 8 | # version. | 
            
              | 9 | # | 
            
              | 10 | # Koha is distributed in the hope that it will be useful, but WITHOUT ANY | 
            
              | 11 | # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR | 
            
              | 12 | # A PARTICULAR PURPOSE.  See the GNU General Public License for more details. | 
            
              | 13 | # | 
            
              | 14 | # You should have received a copy of the GNU General Public License along with | 
            
              | 15 | # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place, | 
            
              | 16 | # Suite 330, Boston, MA  02111-1307 USA | 
            
              | 17 |  | 
            
              | 18 | use Modern::Perl; | 
            
              | 19 |  | 
            
              | 20 | require Exporter; | 
            
              | 21 |  | 
            
              | 22 | use C4::Context; | 
            
              | 23 | use C4::Items qw(GetItem ModItem); | 
            
              | 24 | use C4::Biblio qw(GetBiblioFromItemNumber); | 
            
              | 25 | use C4::Circulation qw(GetOpenIssue); | 
            
              | 26 |  | 
            
              | 27 | use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS $DEBUG @FIELDS); | 
            
              | 28 |  | 
            
              | 29 | BEGIN { | 
            
              | 30 |     @ISA    = qw(Exporter); | 
            
              | 31 |     @EXPORT = qw( | 
            
              | 32 |       &GetCourse | 
            
              | 33 |       &ModCourse | 
            
              | 34 |       &GetCourses | 
            
              | 35 |       &DelCourse | 
            
              | 36 |  | 
            
              | 37 |       &GetCourseInstructors | 
            
              | 38 |       &ModCourseInstructors | 
            
              | 39 |  | 
            
              | 40 |       &GetCourseItem | 
            
              | 41 |       &ModCourseItem | 
            
              | 42 |  | 
            
              | 43 |       &GetCourseReserve | 
            
              | 44 |       &ModCourseReserve | 
            
              | 45 |       &GetCourseReserves | 
            
              | 46 |       &DelCourseReserve | 
            
              | 47 |  | 
            
              | 48 |       &SearchCourses | 
            
              | 49 |        | 
            
              | 50 |       &GetItemReservesInfo | 
            
              | 51 |     ); | 
            
              | 52 |  | 
            
              | 53 |     $DEBUG = 0; | 
            
              | 54 |     @FIELDS = ( 'itype', 'ccode', 'holdingbranch', 'location' ); | 
            
              | 55 | } | 
            
              | 56 |  | 
            
              | 57 | =head1 NAME | 
            
              | 58 |  | 
            
              | 59 | Koha::CourseReserves - Koha course reserves module | 
            
              | 60 |  | 
            
              | 61 | =head1 SYNOPSIS | 
            
              | 62 |  | 
            
              | 63 | use Koha::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 courses.* | 
            
              | 165 |         FROM courses | 
            
              | 166 |         LEFT JOIN course_reserves ON course_reserves.course_id = courses.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 courses.course_id "; | 
            
              | 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 courses | 
            
              | 214 |         WHERE course_id = ? | 
            
              | 215 |     "; | 
            
              | 216 |     C4::Context->dbh->do( $query, undef, $course_id ); | 
            
              | 217 | } | 
            
              | 218 |  | 
            
              | 219 | =head2 EnableOrDisableCourseItems | 
            
              | 220 |  | 
            
              | 221 |   EnableOrDisableCourseItems( course_id => $course_id, enabled => $enabled ); | 
            
              | 222 |  | 
            
              | 223 |   For each item on reserve for this course, | 
            
              | 224 |   if the course item has no active course reserves, | 
            
              | 225 |   swap the fields for the item to make it 'normal' | 
            
              | 226 |   again. | 
            
              | 227 |  | 
            
              | 228 |   enabled => 'yes' to enable course items | 
            
              | 229 |   enabled => 'no' to disable course items | 
            
              | 230 |  | 
            
              | 231 | =cut | 
            
              | 232 |  | 
            
              | 233 | sub EnableOrDisableCourseItems { | 
            
              | 234 |     my (%params) = @_; | 
            
              | 235 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 236 |  | 
            
              | 237 |     my $course_id = $params{'course_id'}; | 
            
              | 238 |     my $enabled   = $params{'enabled'}; | 
            
              | 239 |  | 
            
              | 240 |     my $lookfor = ( $enabled eq 'yes' ) ? 'no' : 'yes'; | 
            
              | 241 |  | 
            
              | 242 |     return unless ( $course_id && $enabled ); | 
            
              | 243 |     return unless ( $enabled eq 'yes' || $enabled eq 'no' ); | 
            
              | 244 |  | 
            
              | 245 |     my $course_reserves = GetCourseReserves( course_id => $course_id ); | 
            
              | 246 |  | 
            
              | 247 |     if ( $enabled eq 'yes' ) { | 
            
              | 248 |         foreach my $course_reserve (@$course_reserves) { | 
            
              | 249 |             if ( | 
            
              | 250 |                 CountCourseReservesForItem( | 
            
              | 251 |                     ci_id   => $course_reserve->{'ci_id'}, | 
            
              | 252 |                     enabled => 'yes' | 
            
              | 253 |                 ) | 
            
              | 254 |               ) | 
            
              | 255 |             { | 
            
              | 256 |                 EnableOrDisableCourseItem( | 
            
              | 257 |                     ci_id   => $course_reserve->{'ci_id'}, | 
            
              | 258 |                     enabled => 'yes', | 
            
              | 259 |                 ); | 
            
              | 260 |             } | 
            
              | 261 |         } | 
            
              | 262 |     } | 
            
              | 263 |     if ( $enabled eq 'no' ) { | 
            
              | 264 |         foreach my $course_reserve (@$course_reserves) { | 
            
              | 265 |             unless ( | 
            
              | 266 |                 CountCourseReservesForItem( | 
            
              | 267 |                     ci_id   => $course_reserve->{'ci_id'}, | 
            
              | 268 |                     enabled => 'yes' | 
            
              | 269 |                 ) | 
            
              | 270 |               ) | 
            
              | 271 |             { | 
            
              | 272 |                 EnableOrDisableCourseItem( | 
            
              | 273 |                     ci_id   => $course_reserve->{'ci_id'}, | 
            
              | 274 |                     enabled => 'no', | 
            
              | 275 |                 ); | 
            
              | 276 |             } | 
            
              | 277 |         } | 
            
              | 278 |     } | 
            
              | 279 | } | 
            
              | 280 |  | 
            
              | 281 | =head2 EnableOrDisableCourseItem | 
            
              | 282 |  | 
            
              | 283 |     EnableOrDisableCourseItem( ci_id => $ci_id, enabled => $enabled ); | 
            
              | 284 |  | 
            
              | 285 |     enabled => 'yes' to enable course items | 
            
              | 286 |     enabled => 'no' to disable course items | 
            
              | 287 |  | 
            
              | 288 | =cut | 
            
              | 289 |  | 
            
              | 290 | sub EnableOrDisableCourseItem { | 
            
              | 291 |     my (%params) = @_; | 
            
              | 292 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 293 |  | 
            
              | 294 |     my $ci_id   = $params{'ci_id'}; | 
            
              | 295 |     my $enabled = $params{'enabled'}; | 
            
              | 296 |  | 
            
              | 297 |     return unless ( $ci_id && $enabled ); | 
            
              | 298 |     return unless ( $enabled eq 'yes' || $enabled eq 'no' ); | 
            
              | 299 |  | 
            
              | 300 |     my $course_item = GetCourseItem( ci_id => $ci_id ); | 
            
              | 301 |  | 
            
              | 302 |     ## We don't want to 'enable' an already enabled item, | 
            
              | 303 |     ## or disable and already disabled item, | 
            
              | 304 |     ## as that would cause the fields to swap | 
            
              | 305 |     if ( $course_item->{'enabled'} ne $enabled ) { | 
            
              | 306 |         _SwapAllFields($ci_id); | 
            
              | 307 |  | 
            
              | 308 |         my $query = " | 
            
              | 309 |             UPDATE course_items | 
            
              | 310 |             SET enabled = ? | 
            
              | 311 |             WHERE ci_id = ? | 
            
              | 312 |         "; | 
            
              | 313 |  | 
            
              | 314 |         C4::Context->dbh->do( $query, undef, $enabled, $ci_id ); | 
            
              | 315 |  | 
            
              | 316 |     } | 
            
              | 317 |  | 
            
              | 318 | } | 
            
              | 319 |  | 
            
              | 320 | =head2 GetCourseInstructors | 
            
              | 321 |  | 
            
              | 322 |     @borrowernumbers = GetCourseInstructors( $course_id ); | 
            
              | 323 |  | 
            
              | 324 | =cut | 
            
              | 325 |  | 
            
              | 326 | sub GetCourseInstructors { | 
            
              | 327 |     my ($course_id) = @_; | 
            
              | 328 |     warn "Koha::CourseReserves::GetCourseInstructors( $course_id )" | 
            
              | 329 |       if $DEBUG; | 
            
              | 330 |  | 
            
              | 331 |     my $query = " | 
            
              | 332 |         SELECT * FROM borrowers | 
            
              | 333 |         RIGHT JOIN course_instructors ON ( course_instructors.borrowernumber = borrowers.borrowernumber ) | 
            
              | 334 |         WHERE course_instructors.course_id = ? | 
            
              | 335 |     "; | 
            
              | 336 |  | 
            
              | 337 |     my $dbh = C4::Context->dbh; | 
            
              | 338 |     my $sth = $dbh->prepare($query); | 
            
              | 339 |     $sth->execute($course_id); | 
            
              | 340 |  | 
            
              | 341 |     return $sth->fetchall_arrayref( {} ); | 
            
              | 342 | } | 
            
              | 343 |  | 
            
              | 344 | =head2 ModCourseInstructors | 
            
              | 345 |  | 
            
              | 346 |     ModCourseInstructors( mode => $mode, course_id => $course_id, [ cardnumbers => $cardnumbers ] OR [ borrowernumbers => $borrowernumbers  ); | 
            
              | 347 |  | 
            
              | 348 |     $mode can be 'replace', 'add', or 'delete' | 
            
              | 349 |  | 
            
              | 350 |     $cardnumbers and $borrowernumbers are both references to arrays | 
            
              | 351 |  | 
            
              | 352 |     Use either cardnumbers or borrowernumber, but not both. | 
            
              | 353 |  | 
            
              | 354 | =cut | 
            
              | 355 |  | 
            
              | 356 | sub ModCourseInstructors { | 
            
              | 357 |     my (%params) = @_; | 
            
              | 358 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 359 |  | 
            
              | 360 |     my $course_id       = $params{'course_id'}; | 
            
              | 361 |     my $mode            = $params{'mode'}; | 
            
              | 362 |     my $cardnumbers     = $params{'cardnumbers'}; | 
            
              | 363 |     my $borrowernumbers = $params{'borrowernumbers'}; | 
            
              | 364 |  | 
            
              | 365 |     return unless ($course_id); | 
            
              | 366 |     return | 
            
              | 367 |       unless ( $mode eq 'replace' | 
            
              | 368 |         || $mode eq 'add' | 
            
              | 369 |         || $mode eq 'delete' ); | 
            
              | 370 |     return unless ( $cardnumbers || $borrowernumbers ); | 
            
              | 371 |     return if ( $cardnumbers && $borrowernumbers ); | 
            
              | 372 |  | 
            
              | 373 |     my @cardnumbers = @$cardnumbers if ( ref($cardnumbers) eq 'ARRAY' ); | 
            
              | 374 |     my @borrowernumbers = @$borrowernumbers | 
            
              | 375 |       if ( ref($borrowernumbers) eq 'ARRAY' ); | 
            
              | 376 |  | 
            
              | 377 |     my $field  = (@cardnumbers) ? 'cardnumber' : 'borrowernumber'; | 
            
              | 378 |     my @fields = (@cardnumbers) ? @cardnumbers : @borrowernumbers; | 
            
              | 379 |     my $placeholders = join( ',', ('?') x scalar @fields ); | 
            
              | 380 |  | 
            
              | 381 |     my $dbh = C4::Context->dbh; | 
            
              | 382 |  | 
            
              | 383 |     $dbh->do( "DELETE FROM course_instructors WHERE course_id = ?", | 
            
              | 384 |         undef, $course_id ) | 
            
              | 385 |       if ( $mode eq 'replace' ); | 
            
              | 386 |  | 
            
              | 387 |     my $query; | 
            
              | 388 |  | 
            
              | 389 |     if ( $mode eq 'add' || $mode eq 'replace' ) { | 
            
              | 390 |         $query = " | 
            
              | 391 |             INSERT INTO course_instructors ( course_id, borrowernumber ) | 
            
              | 392 |             SELECT ?, borrowernumber | 
            
              | 393 |             FROM borrowers | 
            
              | 394 |             WHERE $field IN ( $placeholders ) | 
            
              | 395 |         "; | 
            
              | 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 |     my $itype         = $params{'itype'}; | 
            
              | 463 |     my $ccode         = $params{'ccode'}; | 
            
              | 464 |     my $holdingbranch = $params{'holdingbranch'}; | 
            
              | 465 |     my $location      = $params{'location'}; | 
            
              | 466 |  | 
            
              | 467 |     return unless ($itemnumber); | 
            
              | 468 |  | 
            
              | 469 |     my $course_item = GetCourseItem( itemnumber => $itemnumber ); | 
            
              | 470 |  | 
            
              | 471 |     my $ci_id; | 
            
              | 472 |  | 
            
              | 473 |     if ($course_item) { | 
            
              | 474 |         $ci_id = $course_item->{'ci_id'}; | 
            
              | 475 |  | 
            
              | 476 |         _UpdateCourseItem( | 
            
              | 477 |             ci_id       => $ci_id, | 
            
              | 478 |             course_item => $course_item, | 
            
              | 479 |             %params | 
            
              | 480 |         ); | 
            
              | 481 |     } | 
            
              | 482 |     else { | 
            
              | 483 |         $ci_id = _AddCourseItem(%params); | 
            
              | 484 |     } | 
            
              | 485 |  | 
            
              | 486 |     return $ci_id; | 
            
              | 487 |  | 
            
              | 488 | } | 
            
              | 489 |  | 
            
              | 490 | =head2 _AddCourseItem | 
            
              | 491 |  | 
            
              | 492 |     my $ci_id = _AddCourseItem( %params ); | 
            
              | 493 |  | 
            
              | 494 | =cut | 
            
              | 495 |  | 
            
              | 496 | sub _AddCourseItem { | 
            
              | 497 |     my (%params) = @_; | 
            
              | 498 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 499 |  | 
            
              | 500 |     my ( @fields, @values ); | 
            
              | 501 |  | 
            
              | 502 |     push( @fields, 'itemnumber = ?' ); | 
            
              | 503 |     push( @values, $params{'itemnumber'} ); | 
            
              | 504 |  | 
            
              | 505 |     foreach (@FIELDS) { | 
            
              | 506 |         if ( $params{$_} ) { | 
            
              | 507 |             push( @fields, "$_ = ?" ); | 
            
              | 508 |             push( @values, $params{$_} ); | 
            
              | 509 |         } | 
            
              | 510 |     } | 
            
              | 511 |  | 
            
              | 512 |     my $query = "INSERT INTO course_items SET " . join( ',', @fields ); | 
            
              | 513 |     my $dbh = C4::Context->dbh; | 
            
              | 514 |     $dbh->do( $query, undef, @values ); | 
            
              | 515 |  | 
            
              | 516 |     my $ci_id = $dbh->last_insert_id( undef, undef, 'course_items', 'ci_id' ); | 
            
              | 517 |  | 
            
              | 518 |     return $ci_id; | 
            
              | 519 | } | 
            
              | 520 |  | 
            
              | 521 | =head2 _UpdateCourseItem | 
            
              | 522 |  | 
            
              | 523 |   _UpdateCourseItem( %params ); | 
            
              | 524 |  | 
            
              | 525 | =cut | 
            
              | 526 |  | 
            
              | 527 | sub _UpdateCourseItem { | 
            
              | 528 |     my (%params) = @_; | 
            
              | 529 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 530 |  | 
            
              | 531 |     my $ci_id         = $params{'ci_id'}; | 
            
              | 532 |     my $course_item   = $params{'course_item'}; | 
            
              | 533 |     my $itype         = $params{'itype'}; | 
            
              | 534 |     my $ccode         = $params{'ccode'}; | 
            
              | 535 |     my $holdingbranch = $params{'holdingbranch'}; | 
            
              | 536 |     my $location      = $params{'location'}; | 
            
              | 537 |  | 
            
              | 538 |     return unless ( $ci_id || $course_item ); | 
            
              | 539 |  | 
            
              | 540 |     $course_item = GetCourseItem( ci_id => $ci_id ) | 
            
              | 541 |       unless ($course_item); | 
            
              | 542 |     $ci_id = $course_item->{'ci_id'} unless ($ci_id); | 
            
              | 543 |  | 
            
              | 544 |     ## Revert fields that had an 'original' value, but now don't | 
            
              | 545 |     ## Update the item fields to the stored values from course_items | 
            
              | 546 |     ## and then set those fields in course_items to NULL | 
            
              | 547 |     my @fields_to_revert; | 
            
              | 548 |     foreach (@FIELDS) { | 
            
              | 549 |         if ( !$params{$_} && $course_item->{$_} ) { | 
            
              | 550 |             push( @fields_to_revert, $_ ); | 
            
              | 551 |         } | 
            
              | 552 |     } | 
            
              | 553 |     _RevertFields( | 
            
              | 554 |         ci_id       => $ci_id, | 
            
              | 555 |         fields      => \@fields_to_revert, | 
            
              | 556 |         course_item => $course_item | 
            
              | 557 |     ) if (@fields_to_revert); | 
            
              | 558 |  | 
            
              | 559 |     ## Update fields that still have an original value, but it has changed | 
            
              | 560 |     ## This necessitates only changing the current item values, as we still | 
            
              | 561 |     ## have the original values stored in course_items | 
            
              | 562 |     my %mod_params; | 
            
              | 563 |     foreach (@FIELDS) { | 
            
              | 564 |         if (   $params{$_} | 
            
              | 565 |             && $course_item->{$_} | 
            
              | 566 |             && $params{$_} ne $course_item->{$_} ) | 
            
              | 567 |         { | 
            
              | 568 |             $mod_params{$_} = $params{$_}; | 
            
              | 569 |         } | 
            
              | 570 |     } | 
            
              | 571 |     ModItem( \%mod_params, undef, $course_item->{'itemnumber'} ); | 
            
              | 572 |  | 
            
              | 573 |     ## Update fields that didn't have an original value, but now do | 
            
              | 574 |     ## We must save the original value in course_items, and also | 
            
              | 575 |     ## update the item fields to the new value | 
            
              | 576 |     my $item = GetItem( $course_item->{'itemnumber'} ); | 
            
              | 577 |     my %mod_params_new; | 
            
              | 578 |     my %mod_params_old; | 
            
              | 579 |     foreach (@FIELDS) { | 
            
              | 580 |         if ( $params{$_} && !$course_item->{$_} ) { | 
            
              | 581 |             $mod_params_new{$_} = $params{$_}; | 
            
              | 582 |             $mod_params_old{$_} = $item->{$_}; | 
            
              | 583 |         } | 
            
              | 584 |     } | 
            
              | 585 |     _ModStoredFields( 'ci_id' => $params{'ci_id'}, %mod_params_old ); | 
            
              | 586 |     ModItem( \%mod_params_new, undef, $course_item->{'itemnumber'} ); | 
            
              | 587 |  | 
            
              | 588 | } | 
            
              | 589 |  | 
            
              | 590 | =head2 _ModStoredFields | 
            
              | 591 |  | 
            
              | 592 |     _ModStoredFields( %params ); | 
            
              | 593 |  | 
            
              | 594 |     Updates the values for the 'original' fields in course_items | 
            
              | 595 |     for a given ci_id | 
            
              | 596 |  | 
            
              | 597 | =cut | 
            
              | 598 |  | 
            
              | 599 | sub _ModStoredFields { | 
            
              | 600 |     my (%params) = @_; | 
            
              | 601 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 602 |  | 
            
              | 603 |     return unless ( $params{'ci_id'} ); | 
            
              | 604 |  | 
            
              | 605 |     my ( @fields_to_update, @values_to_update ); | 
            
              | 606 |  | 
            
              | 607 |     foreach (@FIELDS) { | 
            
              | 608 |         if ( $params{$_} ) { | 
            
              | 609 |             push( @fields_to_update, $_ ); | 
            
              | 610 |             push( @values_to_update, $params{$_} ); | 
            
              | 611 |         } | 
            
              | 612 |     } | 
            
              | 613 |  | 
            
              | 614 |     my $query = | 
            
              | 615 |         "UPDATE course_items SET " | 
            
              | 616 |       . join( ',', map { "$_=?" } @fields_to_update ) | 
            
              | 617 |       . " WHERE ci_id = ?"; | 
            
              | 618 |  | 
            
              | 619 |     C4::Context->dbh->do( $query, undef, @values_to_update, $params{'ci_id'} ) | 
            
              | 620 |       if (@values_to_update); | 
            
              | 621 |  | 
            
              | 622 | } | 
            
              | 623 |  | 
            
              | 624 | =head2 _RevertFields | 
            
              | 625 |  | 
            
              | 626 |     _RevertFields( ci_id => $ci_id, fields => \@fields_to_revert ); | 
            
              | 627 |  | 
            
              | 628 | =cut | 
            
              | 629 |  | 
            
              | 630 | sub _RevertFields { | 
            
              | 631 |     my (%params) = @_; | 
            
              | 632 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 633 |  | 
            
              | 634 |     my $ci_id       = $params{'ci_id'}; | 
            
              | 635 |     my $course_item = $params{'course_item'}; | 
            
              | 636 |     my $fields      = $params{'fields'}; | 
            
              | 637 |     my @fields      = @$fields; | 
            
              | 638 |  | 
            
              | 639 |     return unless ($ci_id); | 
            
              | 640 |  | 
            
              | 641 |     $course_item = GetCourseItem( ci_id => $params{'ci_id'} ) | 
            
              | 642 |       unless ($course_item); | 
            
              | 643 |  | 
            
              | 644 |     my $mod_item_params; | 
            
              | 645 |     my @fields_to_null; | 
            
              | 646 |     foreach my $field (@fields) { | 
            
              | 647 |         foreach (@FIELDS) { | 
            
              | 648 |             if ( $field eq $_ && $course_item->{$_} ) { | 
            
              | 649 |                 $mod_item_params->{$_} = $course_item->{$_}; | 
            
              | 650 |                 push( @fields_to_null, $_ ); | 
            
              | 651 |             } | 
            
              | 652 |         } | 
            
              | 653 |     } | 
            
              | 654 |     ModItem( $mod_item_params, undef, $course_item->{'itemnumber'} ); | 
            
              | 655 |  | 
            
              | 656 |     my $query = | 
            
              | 657 |         "UPDATE course_items SET " | 
            
              | 658 |       . join( ',', map { "$_=NULL" } @fields_to_null ) | 
            
              | 659 |       . " WHERE ci_id = ?"; | 
            
              | 660 |  | 
            
              | 661 |     C4::Context->dbh->do( $query, undef, $ci_id ) if (@fields_to_null); | 
            
              | 662 | } | 
            
              | 663 |  | 
            
              | 664 | =head2 _SwapAllFields | 
            
              | 665 |  | 
            
              | 666 |     _SwapAllFields( $ci_id ); | 
            
              | 667 |  | 
            
              | 668 | =cut | 
            
              | 669 |  | 
            
              | 670 | sub _SwapAllFields { | 
            
              | 671 |     my ($ci_id) = @_; | 
            
              | 672 |     warn "Koha::CourseReserves::_SwapFields( $ci_id )" if $DEBUG; | 
            
              | 673 |  | 
            
              | 674 |     my $course_item = GetCourseItem( ci_id => $ci_id ); | 
            
              | 675 |     my $item = GetItem( $course_item->{'itemnumber'} ); | 
            
              | 676 |  | 
            
              | 677 |     my %course_item_fields; | 
            
              | 678 |     my %item_fields; | 
            
              | 679 |     foreach (@FIELDS) { | 
            
              | 680 |         if ( $course_item->{$_} ) { | 
            
              | 681 |             $course_item_fields{$_} = $course_item->{$_}; | 
            
              | 682 |             $item_fields{$_}        = $item->{$_}; | 
            
              | 683 |         } | 
            
              | 684 |     } | 
            
              | 685 |  | 
            
              | 686 |     ModItem( \%course_item_fields, undef, $course_item->{'itemnumber'} ); | 
            
              | 687 |     _ModStoredFields( %item_fields, ci_id => $ci_id ); | 
            
              | 688 | } | 
            
              | 689 |  | 
            
              | 690 | =head2 GetCourseItems { | 
            
              | 691 |  | 
            
              | 692 |   $course_items = GetCourseItems( | 
            
              | 693 |       [course_id => $course_id] | 
            
              | 694 |       [, itemnumber => $itemnumber ] | 
            
              | 695 |   ); | 
            
              | 696 |  | 
            
              | 697 | =cut | 
            
              | 698 |  | 
            
              | 699 | sub GetCourseItems { | 
            
              | 700 |     my (%params) = @_; | 
            
              | 701 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 702 |  | 
            
              | 703 |     my $course_id  = $params{'course_id'}; | 
            
              | 704 |     my $itemnumber = $params{'itemnumber'}; | 
            
              | 705 |  | 
            
              | 706 |     return unless ($course_id); | 
            
              | 707 |  | 
            
              | 708 |     my @query_keys; | 
            
              | 709 |     my @query_values; | 
            
              | 710 |  | 
            
              | 711 |     my $query = "SELECT * FROM course_items"; | 
            
              | 712 |  | 
            
              | 713 |     if ( keys %params ) { | 
            
              | 714 |  | 
            
              | 715 |         $query .= " WHERE "; | 
            
              | 716 |  | 
            
              | 717 |         foreach my $key ( keys %params ) { | 
            
              | 718 |             push( @query_keys,   " $key LIKE ? " ); | 
            
              | 719 |             push( @query_values, $params{$key} ); | 
            
              | 720 |         } | 
            
              | 721 |  | 
            
              | 722 |         $query .= join( ' AND ', @query_keys ); | 
            
              | 723 |     } | 
            
              | 724 |  | 
            
              | 725 |     my $dbh = C4::Context->dbh; | 
            
              | 726 |     my $sth = $dbh->prepare($query); | 
            
              | 727 |     $sth->execute(@query_values); | 
            
              | 728 |  | 
            
              | 729 |     return $sth->fetchall_arrayref( {} ); | 
            
              | 730 | } | 
            
              | 731 |  | 
            
              | 732 | =head2 DelCourseItem { | 
            
              | 733 |  | 
            
              | 734 |   DelCourseItem( ci_id => $cr_id ); | 
            
              | 735 |  | 
            
              | 736 | =cut | 
            
              | 737 |  | 
            
              | 738 | sub DelCourseItem { | 
            
              | 739 |     my (%params) = @_; | 
            
              | 740 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 741 |  | 
            
              | 742 |     my $ci_id = $params{'ci_id'}; | 
            
              | 743 |  | 
            
              | 744 |     return unless ($ci_id); | 
            
              | 745 |  | 
            
              | 746 |     _RevertFields( ci_id => $ci_id, fields => \@FIELDS ); | 
            
              | 747 |  | 
            
              | 748 |     my $query = " | 
            
              | 749 |         DELETE FROM course_items | 
            
              | 750 |         WHERE ci_id = ? | 
            
              | 751 |     "; | 
            
              | 752 |     C4::Context->dbh->do( $query, undef, $ci_id ); | 
            
              | 753 | } | 
            
              | 754 |  | 
            
              | 755 | =head2 GetCourseReserve { | 
            
              | 756 |  | 
            
              | 757 |   $course_item = GetCourseReserve( %params ); | 
            
              | 758 |  | 
            
              | 759 | =cut | 
            
              | 760 |  | 
            
              | 761 | sub GetCourseReserve { | 
            
              | 762 |     my (%params) = @_; | 
            
              | 763 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 764 |  | 
            
              | 765 |     my $cr_id     = $params{'cr_id'}; | 
            
              | 766 |     my $course_id = $params{'course_id'}; | 
            
              | 767 |     my $ci_id     = $params{'ci_id'}; | 
            
              | 768 |  | 
            
              | 769 |     return unless ( $cr_id || ( $course_id && $ci_id ) ); | 
            
              | 770 |  | 
            
              | 771 |     my $dbh = C4::Context->dbh; | 
            
              | 772 |     my $sth; | 
            
              | 773 |  | 
            
              | 774 |     if ($cr_id) { | 
            
              | 775 |         my $query = " | 
            
              | 776 |             SELECT * FROM course_reserves | 
            
              | 777 |             WHERE cr_id = ? | 
            
              | 778 |         "; | 
            
              | 779 |         $sth = $dbh->prepare($query); | 
            
              | 780 |         $sth->execute($cr_id); | 
            
              | 781 |     } | 
            
              | 782 |     else { | 
            
              | 783 |         my $query = " | 
            
              | 784 |             SELECT * FROM course_reserves | 
            
              | 785 |             WHERE course_id = ? AND ci_id = ? | 
            
              | 786 |         "; | 
            
              | 787 |         $sth = $dbh->prepare($query); | 
            
              | 788 |         $sth->execute( $course_id, $ci_id ); | 
            
              | 789 |     } | 
            
              | 790 |  | 
            
              | 791 |     my $course_reserve = $sth->fetchrow_hashref(); | 
            
              | 792 |     return $course_reserve; | 
            
              | 793 | } | 
            
              | 794 |  | 
            
              | 795 | =head2 ModCourseReserve | 
            
              | 796 |  | 
            
              | 797 |     $id = ModCourseReserve( %params ); | 
            
              | 798 |  | 
            
              | 799 | =cut | 
            
              | 800 |  | 
            
              | 801 | sub ModCourseReserve { | 
            
              | 802 |     my (%params) = @_; | 
            
              | 803 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 804 |  | 
            
              | 805 |     my $course_id   = $params{'course_id'}; | 
            
              | 806 |     my $ci_id       = $params{'ci_id'}; | 
            
              | 807 |     my $staff_note  = $params{'staff_note'}; | 
            
              | 808 |     my $public_note = $params{'public_note'}; | 
            
              | 809 |  | 
            
              | 810 |     return unless ( $course_id && $ci_id ); | 
            
              | 811 |  | 
            
              | 812 |     my $course_reserve = | 
            
              | 813 |       GetCourseReserve( course_id => $course_id, ci_id => $ci_id ); | 
            
              | 814 |     my $cr_id; | 
            
              | 815 |  | 
            
              | 816 |     my $dbh = C4::Context->dbh; | 
            
              | 817 |  | 
            
              | 818 |     if ($course_reserve) { | 
            
              | 819 |         $cr_id = $course_reserve->{'cr_id'}; | 
            
              | 820 |  | 
            
              | 821 |         my $query = " | 
            
              | 822 |             UPDATE course_reserves | 
            
              | 823 |             SET staff_note = ?, public_note = ? | 
            
              | 824 |             WHERE cr_id = ? | 
            
              | 825 |         "; | 
            
              | 826 |         $dbh->do( $query, undef, $staff_note, $public_note, $cr_id ); | 
            
              | 827 |     } | 
            
              | 828 |     else { | 
            
              | 829 |         my $query = " | 
            
              | 830 |             INSERT INTO course_reserves SET | 
            
              | 831 |             course_id = ?, | 
            
              | 832 |             ci_id = ?, | 
            
              | 833 |             staff_note = ?, | 
            
              | 834 |             public_note = ? | 
            
              | 835 |         "; | 
            
              | 836 |         $dbh->do( $query, undef, $course_id, $ci_id, $staff_note, | 
            
              | 837 |             $public_note ); | 
            
              | 838 |         $cr_id = | 
            
              | 839 |           $dbh->last_insert_id( undef, undef, 'course_reserves', 'cr_id' ); | 
            
              | 840 |     } | 
            
              | 841 |  | 
            
              | 842 |     my $course = GetCourse($course_id); | 
            
              | 843 |     EnableOrDisableCourseItem( | 
            
              | 844 |         ci_id   => $params{'ci_id'}, | 
            
              | 845 |         enabled => $course->{'enabled'} | 
            
              | 846 |     ); | 
            
              | 847 |  | 
            
              | 848 |     return $cr_id; | 
            
              | 849 | } | 
            
              | 850 |  | 
            
              | 851 | =head2 GetCourseReserves { | 
            
              | 852 |  | 
            
              | 853 |   $course_reserves = GetCourseReserves( %params ); | 
            
              | 854 |    | 
            
              | 855 |   Required: | 
            
              | 856 |       course_id OR ci_id | 
            
              | 857 |   Optional: | 
            
              | 858 |       include_items   => 1, | 
            
              | 859 |       include_count   => 1, | 
            
              | 860 |       include_courses => 1, | 
            
              | 861 |  | 
            
              | 862 | =cut | 
            
              | 863 |  | 
            
              | 864 | sub GetCourseReserves { | 
            
              | 865 |     my (%params) = @_; | 
            
              | 866 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 867 |  | 
            
              | 868 |     my $course_id     = $params{'course_id'}; | 
            
              | 869 |     my $ci_id         = $params{'ci_id'}; | 
            
              | 870 |     my $include_items = $params{'include_items'}; | 
            
              | 871 |     my $include_count = $params{'include_count'}; | 
            
              | 872 |     my $include_courses = $params{'include_courses'}; | 
            
              | 873 |      | 
            
              | 874 |     return unless ( $course_id || $ci_id ); | 
            
              | 875 |  | 
            
              | 876 |     my $field = ($course_id) ? 'course_id' : 'ci_id'; | 
            
              | 877 |     my $value = ($course_id) ? $course_id  : $ci_id; | 
            
              | 878 |  | 
            
              | 879 |     my $query = " | 
            
              | 880 |         SELECT cr.*, ci.itemnumber | 
            
              | 881 |         FROM course_reserves cr, course_items ci | 
            
              | 882 |         WHERE cr.$field = ? | 
            
              | 883 |         AND cr.ci_id = ci.ci_id | 
            
              | 884 |     "; | 
            
              | 885 |     my $dbh = C4::Context->dbh; | 
            
              | 886 |     my $sth = $dbh->prepare($query); | 
            
              | 887 |     $sth->execute($value); | 
            
              | 888 |  | 
            
              | 889 |     my $course_reserves = $sth->fetchall_arrayref( {} ); | 
            
              | 890 |  | 
            
              | 891 |     if ($include_items) { | 
            
              | 892 |         foreach my $cr (@$course_reserves) { | 
            
              | 893 |             $cr->{'course_item'} = GetCourseItem( ci_id => $cr->{'ci_id'} ); | 
            
              | 894 |             $cr->{'item'} = GetBiblioFromItemNumber( $cr->{'itemnumber'} ); | 
            
              | 895 |             $cr->{'issue'} = GetOpenIssue( $cr->{'itemnumber'} ); | 
            
              | 896 |         } | 
            
              | 897 |     } | 
            
              | 898 |  | 
            
              | 899 |     if ($include_count) { | 
            
              | 900 |         foreach my $cr (@$course_reserves) { | 
            
              | 901 |             $cr->{'reserves_count'} = | 
            
              | 902 |               CountCourseReservesForItem( ci_id => $cr->{'ci_id'} ); | 
            
              | 903 |         } | 
            
              | 904 |     } | 
            
              | 905 |  | 
            
              | 906 |     if ($include_courses) { | 
            
              | 907 |         foreach my $cr (@$course_reserves) { | 
            
              | 908 |             $cr->{'courses'} = | 
            
              | 909 |               GetCourses( itemnumber => $cr->{'itemnumber'} ); | 
            
              | 910 |         } | 
            
              | 911 |     } | 
            
              | 912 |  | 
            
              | 913 |     return $course_reserves; | 
            
              | 914 | } | 
            
              | 915 |  | 
            
              | 916 | =head2 DelCourseReserve { | 
            
              | 917 |  | 
            
              | 918 |   DelCourseReserve( cr_id => $cr_id ); | 
            
              | 919 |  | 
            
              | 920 | =cut | 
            
              | 921 |  | 
            
              | 922 | sub DelCourseReserve { | 
            
              | 923 |     my (%params) = @_; | 
            
              | 924 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 925 |  | 
            
              | 926 |     my $cr_id = $params{'cr_id'}; | 
            
              | 927 |  | 
            
              | 928 |     return unless ($cr_id); | 
            
              | 929 |  | 
            
              | 930 |     my $dbh = C4::Context->dbh; | 
            
              | 931 |  | 
            
              | 932 |     my $course_reserve = GetCourseReserve( cr_id => $cr_id ); | 
            
              | 933 |  | 
            
              | 934 |     my $query = " | 
            
              | 935 |         DELETE FROM course_reserves | 
            
              | 936 |         WHERE cr_id = ? | 
            
              | 937 |     "; | 
            
              | 938 |     $dbh->do( $query, undef, $cr_id ); | 
            
              | 939 |  | 
            
              | 940 |     ## If there are no other course reserves for this item | 
            
              | 941 |     ## delete the course_item as well | 
            
              | 942 |     unless ( CountCourseReservesForItem( ci_id => $course_reserve->{'ci_id'} ) ) | 
            
              | 943 |     { | 
            
              | 944 |         DelCourseItem( ci_id => $course_reserve->{'ci_id'} ); | 
            
              | 945 |     } | 
            
              | 946 |  | 
            
              | 947 | } | 
            
              | 948 |  | 
            
              | 949 | =head2 GetReservesInfo | 
            
              | 950 |  | 
            
              | 951 |     my $arrayref = GetItemReservesInfo( itemnumber => $itemnumber ); | 
            
              | 952 |  | 
            
              | 953 |     For a given item, returns an arrayref of reserves hashrefs, | 
            
              | 954 |     with a course hashref under the key 'course' | 
            
              | 955 |  | 
            
              | 956 | =cut | 
            
              | 957 |  | 
            
              | 958 | sub GetItemReservesInfo { | 
            
              | 959 |     my (%params) = @_; | 
            
              | 960 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 961 |  | 
            
              | 962 |     my $itemnumber = $params{'itemnumber'}; | 
            
              | 963 |  | 
            
              | 964 |     return unless ($itemnumber); | 
            
              | 965 |  | 
            
              | 966 |     my $course_item = GetCourseItem( itemnumber => $itemnumber ); | 
            
              | 967 |  | 
            
              | 968 |     return unless ( keys %$course_item ); | 
            
              | 969 |  | 
            
              | 970 |     my $course_reserves = GetCourseReserves( ci_id => $course_item->{'ci_id'} ); | 
            
              | 971 |  | 
            
              | 972 |     foreach my $cr (@$course_reserves) { | 
            
              | 973 |         $cr->{'course'} = GetCourse( $cr->{'course_id'} ); | 
            
              | 974 |     } | 
            
              | 975 |  | 
            
              | 976 |     return $course_reserves; | 
            
              | 977 | } | 
            
              | 978 |  | 
            
              | 979 | =head2 CountCourseReservesForItem | 
            
              | 980 |  | 
            
              | 981 |     $bool = CountCourseReservesForItem( %params ); | 
            
              | 982 |  | 
            
              | 983 |     ci_id - course_item id | 
            
              | 984 |     OR | 
            
              | 985 |     itemnumber - course_item itemnumber | 
            
              | 986 |  | 
            
              | 987 |     enabled = 'yes' or 'no' | 
            
              | 988 |     Optional, if not supplied, counts reserves | 
            
              | 989 |     for both enabled and disabled courses | 
            
              | 990 |  | 
            
              | 991 | =cut | 
            
              | 992 |  | 
            
              | 993 | sub CountCourseReservesForItem { | 
            
              | 994 |     my (%params) = @_; | 
            
              | 995 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 996 |  | 
            
              | 997 |     my $ci_id      = $params{'ci_id'}; | 
            
              | 998 |     my $itemnumber = $params{'itemnumber'}; | 
            
              | 999 |     my $enabled    = $params{'enabled'}; | 
            
              | 1000 |  | 
            
              | 1001 |     return unless ( $ci_id || $itemnumber ); | 
            
              | 1002 |  | 
            
              | 1003 |     my $course_item = | 
            
              | 1004 |       GetCourseItem( ci_id => $ci_id, itemnumber => $itemnumber ); | 
            
              | 1005 |  | 
            
              | 1006 |     my @params = ( $course_item->{'ci_id'} ); | 
            
              | 1007 |     push( @params, $enabled ) if ($enabled); | 
            
              | 1008 |  | 
            
              | 1009 |     my $query = " | 
            
              | 1010 |         SELECT COUNT(*) AS count | 
            
              | 1011 |         FROM course_reserves cr | 
            
              | 1012 |         LEFT JOIN courses c ON ( c.course_id = cr.course_id ) | 
            
              | 1013 |         WHERE ci_id = ? | 
            
              | 1014 |     "; | 
            
              | 1015 |     $query .= "AND c.enabled = ?" if ($enabled); | 
            
              | 1016 |  | 
            
              | 1017 |     my $dbh = C4::Context->dbh; | 
            
              | 1018 |     my $sth = $dbh->prepare($query); | 
            
              | 1019 |     $sth->execute(@params); | 
            
              | 1020 |  | 
            
              | 1021 |     my $row = $sth->fetchrow_hashref(); | 
            
              | 1022 |  | 
            
              | 1023 |     return $row->{'count'}; | 
            
              | 1024 | } | 
            
              | 1025 |  | 
            
              | 1026 | =head2 SearchCourses | 
            
              | 1027 |  | 
            
              | 1028 |     my $courses = SearchCourses( term => $search_term, enabled => 'yes' ); | 
            
              | 1029 |  | 
            
              | 1030 | =cut | 
            
              | 1031 |  | 
            
              | 1032 | sub SearchCourses { | 
            
              | 1033 |     my (%params) = @_; | 
            
              | 1034 |     warn identify_myself(%params) if $DEBUG; | 
            
              | 1035 |  | 
            
              | 1036 |     my $term = $params{'term'}; | 
            
              | 1037 |      | 
            
              | 1038 |     my $enabled = $params{'enabled'} || '%'; | 
            
              | 1039 |  | 
            
              | 1040 |     my @params; | 
            
              | 1041 |     my $query = "SELECT c.* FROM courses c"; | 
            
              | 1042 |      | 
            
              | 1043 |     $query .= " | 
            
              | 1044 |         LEFT JOIN course_instructors ci | 
            
              | 1045 |             ON ( c.course_id = ci.course_id ) | 
            
              | 1046 |         LEFT JOIN borrowers b | 
            
              | 1047 |             ON ( ci.borrowernumber = b.borrowernumber ) | 
            
              | 1048 |         LEFT JOIN authorised_values av | 
            
              | 1049 |             ON ( c.department = av.authorised_value ) | 
            
              | 1050 |         WHERE | 
            
              | 1051 |             ( av.category = 'DEPARTMENT' OR av.category = 'TERM' ) | 
            
              | 1052 |             AND | 
            
              | 1053 |             ( | 
            
              | 1054 |                 department LIKE ? OR | 
            
              | 1055 |                 course_number LIKE ? OR | 
            
              | 1056 |                 section LIKE ? OR | 
            
              | 1057 |                 course_name LIKE ? OR | 
            
              | 1058 |                 term LIKE ? OR | 
            
              | 1059 |                 public_note LIKE ? OR | 
            
              | 1060 |                 CONCAT(surname,' ',firstname) LIKE ? OR | 
            
              | 1061 |                 CONCAT(firstname,' ',surname) LIKE ? OR | 
            
              | 1062 |                 lib LIKE ? OR | 
            
              | 1063 |                 lib_opac LIKE ? | 
            
              | 1064 |            ) | 
            
              | 1065 |            AND | 
            
              | 1066 |            c.enabled LIKE ? | 
            
              | 1067 |         GROUP BY c.course_id | 
            
              | 1068 |     "; | 
            
              | 1069 |  | 
            
              | 1070 |     $term   = "%$term%"; | 
            
              | 1071 |     @params = ($term) x 10; | 
            
              | 1072 |  | 
            
              | 1073 |     $query .= " ORDER BY department, course_number, section, term, course_name "; | 
            
              | 1074 |  | 
            
              | 1075 |     my $dbh = C4::Context->dbh; | 
            
              | 1076 |     my $sth = $dbh->prepare($query); | 
            
              | 1077 |  | 
            
              | 1078 |     $sth->execute(@params, $enabled); | 
            
              | 1079 |  | 
            
              | 1080 |     my $courses = $sth->fetchall_arrayref( {} ); | 
            
              | 1081 |  | 
            
              | 1082 |     foreach my $c (@$courses) { | 
            
              | 1083 |         $c->{'instructors'} = GetCourseInstructors( $c->{'course_id'} ); | 
            
              | 1084 |     } | 
            
              | 1085 |  | 
            
              | 1086 |     return $courses; | 
            
              | 1087 | } | 
            
              | 1088 |  | 
            
              | 1089 | sub whoami  { ( caller(1) )[3] } | 
            
              | 1090 | sub whowasi { ( caller(2) )[3] } | 
            
              | 1091 |  | 
            
              | 1092 | sub stringify_params { | 
            
              | 1093 |     my (%params) = @_; | 
            
              | 1094 |  | 
            
              | 1095 |     my $string = "\n"; | 
            
              | 1096 |  | 
            
              | 1097 |     foreach my $key ( keys %params ) { | 
            
              | 1098 |         $string .= "    $key => " . $params{$key} . "\n"; | 
            
              | 1099 |     } | 
            
              | 1100 |  | 
            
              | 1101 |     return "( $string )"; | 
            
              | 1102 | } | 
            
              | 1103 |  | 
            
              | 1104 | sub identify_myself { | 
            
              | 1105 |     my (%params) = @_; | 
            
              | 1106 |  | 
            
              | 1107 |     return whowasi() . stringify_params(%params); | 
            
              | 1108 | } | 
            
              | 1109 |  | 
            
              | 1110 | 1; | 
            
              | 1111 |  | 
            
              | 1112 | =head1 AUTHOR | 
            
              | 1113 |  | 
            
              | 1114 | Kyle M Hall <kyle@bywatersolutions.com> | 
            
              | 1115 |  | 
            
              | 1116 | =cut |