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