View | Details | Raw Unified | Return to bug 5304
Collapse All | Expand All

(-)a/C4/Items.pm (-110 / +100 lines)
Lines 2-7 package C4::Items; Link Here
2
2
3
# Copyright 2007 LibLime, Inc.
3
# Copyright 2007 LibLime, Inc.
4
#
4
#
5
# Parts copyright Catalyst IT 2010
6
#
5
# This file is part of Koha.
7
# This file is part of Koha.
6
#
8
#
7
# Koha is free software; you can redistribute it and/or modify it under the
9
# Koha is free software; you can redistribute it and/or modify it under the
Lines 1168-1297 If this is set, it is set to C<One Order>. Link Here
1168
sub GetItemsInfo {
1170
sub GetItemsInfo {
1169
    my ( $biblionumber, $type ) = @_;
1171
    my ( $biblionumber, $type ) = @_;
1170
    my $dbh   = C4::Context->dbh;
1172
    my $dbh   = C4::Context->dbh;
1173
1174
    # get notforloan complete status if applicable
1175
    my $sthnflstatus = $dbh->prepare(
1176
        "SELECT authorised_value
1177
        FROM   marc_subfield_structure
1178
        WHERE  kohafield='items.notforloan'
1179
    "
1180
    );
1181
    $sthnflstatus->execute;
1182
    my ($authorised_value_nfl) = $sthnflstatus->fetchrow;
1183
1184
    # my stack procedures
1185
    my $stackstatus = $dbh->prepare(
1186
        'SELECT authorised_value
1187
            FROM   marc_subfield_structure
1188
            WHERE  kohafield="items.stack"
1189
    '
1190
    );
1191
    $stackstatus->execute;
1192
    my ($authorised_value_stack) = $stackstatus->fetchrow;
1193
1194
    my $user_branch;
1195
    if (C4::Context->preference("IndependantBranches")){
1196
        my $userenv = C4::Context->userenv;
1197
        $user_branch = $userenv->{branch} if $userenv && ( $userenv->{flags} % 2 != 1 );
1198
    }
1199
1171
    # note biblioitems.* must be avoided to prevent large marc and marcxml fields from killing performance.
1200
    # note biblioitems.* must be avoided to prevent large marc and marcxml fields from killing performance.
1172
    my $query = "
1201
    my $fields = <<EOS;
1173
    SELECT items.*,
1202
        items.*,
1174
           biblio.*,
1203
        biblio.*,
1175
           biblioitems.volume,
1204
        issues.*,
1176
           biblioitems.number,
1205
        biblioitems.volume,
1177
           biblioitems.itemtype,
1206
        biblioitems.number,
1178
           biblioitems.isbn,
1207
        biblioitems.itemtype,
1179
           biblioitems.issn,
1208
        biblioitems.isbn,
1180
           biblioitems.publicationyear,
1209
        biblioitems.issn,
1181
           biblioitems.publishercode,
1210
        biblioitems.publicationyear,
1182
           biblioitems.volumedate,
1211
        biblioitems.publishercode,
1183
           biblioitems.volumedesc,
1212
        biblioitems.volumedate,
1184
           biblioitems.lccn,
1213
        biblioitems.volumedesc,
1185
           biblioitems.url,
1214
        biblioitems.lccn,
1186
           items.notforloan as itemnotforloan,
1215
        biblioitems.url,
1187
           itemtypes.description,
1216
        items.notforloan as itemnotforloan,
1188
           itemtypes.notforloan as notforloan_per_itemtype,
1217
        itemtypes.description,
1189
           branchurl
1218
        itemtypes.notforloan as notforloan_per_itemtype,
1190
     FROM items
1219
        homebranch.branchurl,
1191
     LEFT JOIN branches ON items.homebranch = branches.branchcode
1220
        COALESCE(holdingbranch.branchname, homebranch.branchname) AS branchname,
1192
     LEFT JOIN biblio      ON      biblio.biblionumber     = items.biblionumber
1221
        serial.serialseq,
1193
     LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
1222
        serial.publisheddate
1194
     LEFT JOIN itemtypes   ON   itemtypes.itemtype         = "
1223
EOS
1195
     . (C4::Context->preference('item-level_itypes') ? 'items.itype' : 'biblioitems.itemtype');
1224
    my $itemtypes_join_field = C4::Context->preference('item-level_itypes') ? 'items.itype'
1196
    $query .= " WHERE items.biblionumber = ? ORDER BY branches.branchname,items.dateaccessioned desc" ;
1225
                                                                            : 'biblioitems.itemtype';
1226
    my $from = <<EOS;
1227
items
1228
LEFT JOIN biblio      USING(biblionumber)
1229
LEFT JOIN biblioitems USING(biblioitemnumber)
1230
LEFT OUTER JOIN (SELECT itemnumber, serialseq, publisheddate
1231
                 FROM serialitems LEFT JOIN serial USING(serialid)) serial
1232
                      USING(itemnumber)
1233
LEFT OUTER JOIN (SELECT itemnumber, borrowernumber, cardnumber, surname, firstname, date_due,
1234
                        borrowers.branchcode AS borrower_branchcode
1235
                 FROM issues LEFT JOIN borrowers USING(borrowernumber)) issues
1236
                      USING(itemnumber)
1237
LEFT JOIN itemtypes   ON   itemtypes.itemtype         = $itemtypes_join_field
1238
LEFT       JOIN branches homebranch    ON items.homebranch    = homebranch.branchcode
1239
LEFT OUTER JOIN branches holdingbranch ON items.holdingbranch = holdingbranch.branchcode
1240
EOS
1241
    if ($authorised_value_nfl) {
1242
        $from .= <<EOS;
1243
LEFT OUTER JOIN authorised_values authorised_values_nfl
1244
    ON authorised_values_nfl.category = '$authorised_value_nfl'
1245
    AND authorised_values_nfl.authorised_value = items.notforloan
1246
EOS
1247
        $fields .= ", authorised_values_nfl.lib AS notforloanvalue";
1248
    }
1249
1250
    if ($authorised_value_stack) {
1251
        $from .= <<EOS;
1252
LEFT OUTER JOIN authorised_values authorised_values_stack
1253
    ON authorised_values_nfl.category = '$authorised_value_stack'
1254
    AND authorised_values_nfl.authorised_value = items.stack
1255
EOS
1256
        $fields .= ", authorised_values_stack.lib AS stack";
1257
    }
1258
1259
    my $query = "SELECT $fields FROM $from WHERE items.biblionumber = ?
1260
                 ORDER BY homebranch.branchname,items.dateaccessioned desc" ;
1197
    my $sth = $dbh->prepare($query);
1261
    my $sth = $dbh->prepare($query);
1198
    $sth->execute($biblionumber);
1262
    $sth->execute($biblionumber);
1199
    my $i = 0;
1263
1200
    my @results;
1264
    my @results;
1201
    my $serial;
1265
    my $serial;
1202
1203
    my $isth    = $dbh->prepare(
1204
        "SELECT issues.*,borrowers.cardnumber,borrowers.surname,borrowers.firstname,borrowers.branchcode as bcode
1205
        FROM   issues LEFT JOIN borrowers ON issues.borrowernumber=borrowers.borrowernumber
1206
        WHERE  itemnumber = ?"
1207
       );
1208
	my $ssth = $dbh->prepare("SELECT serialseq,publisheddate from serialitems left join serial on serialitems.serialid=serial.serialid where serialitems.itemnumber=? "); 
1209
	while ( my $data = $sth->fetchrow_hashref ) {
1266
	while ( my $data = $sth->fetchrow_hashref ) {
1210
        my $datedue = '';
1267
        my $datedue = $data->{'date_due'};
1211
        my $count_reserves;
1268
        my $count_reserves;
1212
        $isth->execute( $data->{'itemnumber'} );
1269
1213
        if ( my $idata = $isth->fetchrow_hashref ) {
1270
        $data->{'NOTSAMEBRANCH'} = $data->{'borrower_branchcode'} ne $user_branch
1214
            $data->{borrowernumber} = $idata->{borrowernumber};
1271
          if $data->{'borrower_branchcode'} && $user_branch;
1215
            $data->{cardnumber}     = $idata->{cardnumber};
1272
1216
            $data->{surname}     = $idata->{surname};
1273
		$serial = 1 if $data->{'serial'};
1217
            $data->{firstname}     = $idata->{firstname};
1274
1218
            $datedue                = $idata->{'date_due'};
1275
		unless ( $datedue ) {
1219
        if (C4::Context->preference("IndependantBranches")){
1220
        my $userenv = C4::Context->userenv;
1221
        if ( ($userenv) && ( $userenv->{flags} % 2 != 1 ) ) { 
1222
            $data->{'NOTSAMEBRANCH'} = 1 if ($idata->{'bcode'} ne $userenv->{branch});
1223
        }
1224
        }
1225
        }
1226
		if ( $data->{'serial'}) {	
1227
			$ssth->execute($data->{'itemnumber'}) ;
1228
			($data->{'serialseq'} , $data->{'publisheddate'}) = $ssth->fetchrow_array();
1229
			$serial = 1;
1230
        }
1231
		if ( $datedue eq '' ) {
1232
            my ( $restype, $reserves ) =
1276
            my ( $restype, $reserves ) =
1233
              C4::Reserves::CheckReserves( $data->{'itemnumber'} );
1277
              C4::Reserves::MostImportantReserve( $data->{'biblioitemnumber'}, $data->{'biblionumber'}, $data->{'itemnumber'} );
1234
# Previous conditional check with if ($restype) is not needed because a true
1278
# Previous conditional check with if ($restype) is not needed because a true
1235
# result for one item will result in subsequent items defaulting to this true
1279
# result for one item will result in subsequent items defaulting to this true
1236
# value.
1280
# value.
1237
            $count_reserves = $restype;
1281
            $count_reserves = $restype;
1238
        }
1282
        }
1239
        #get branch information.....
1240
        my $bsth = $dbh->prepare(
1241
            "SELECT * FROM branches WHERE branchcode = ?
1242
        "
1243
        );
1244
        $bsth->execute( $data->{'holdingbranch'} );
1245
        if ( my $bdata = $bsth->fetchrow_hashref ) {
1246
            $data->{'branchname'} = $bdata->{'branchname'};
1247
        }
1248
        $data->{'datedue'}        = $datedue;
1283
        $data->{'datedue'}        = $datedue;
1249
        $data->{'count_reserves'} = $count_reserves;
1284
        $data->{'count_reserves'} = $count_reserves;
1250
1285
1251
        # get notforloan complete status if applicable
1252
        my $sthnflstatus = $dbh->prepare(
1253
            'SELECT authorised_value
1254
            FROM   marc_subfield_structure
1255
            WHERE  kohafield="items.notforloan"
1256
        '
1257
        );
1258
1259
        $sthnflstatus->execute;
1260
        my ($authorised_valuecode) = $sthnflstatus->fetchrow;
1261
        if ($authorised_valuecode) {
1262
            $sthnflstatus = $dbh->prepare(
1263
                "SELECT lib FROM authorised_values
1264
                 WHERE  category=?
1265
                 AND authorised_value=?"
1266
            );
1267
            $sthnflstatus->execute( $authorised_valuecode,
1268
                $data->{itemnotforloan} );
1269
            my ($lib) = $sthnflstatus->fetchrow;
1270
            $data->{notforloanvalue} = $lib;
1271
        }
1272
1273
        # my stack procedures
1274
        my $stackstatus = $dbh->prepare(
1275
            'SELECT authorised_value
1276
             FROM   marc_subfield_structure
1277
             WHERE  kohafield="items.stack"
1278
        '
1279
        );
1280
        $stackstatus->execute;
1281
1282
        ($authorised_valuecode) = $stackstatus->fetchrow;
1283
        if ($authorised_valuecode) {
1284
            $stackstatus = $dbh->prepare(
1285
                "SELECT lib
1286
                 FROM   authorised_values
1287
                 WHERE  category=?
1288
                 AND    authorised_value=?
1289
            "
1290
            );
1291
            $stackstatus->execute( $authorised_valuecode, $data->{stack} );
1292
            my ($lib) = $stackstatus->fetchrow;
1293
            $data->{stack} = $lib;
1294
        }
1295
        # Find the last 3 people who borrowed this item.
1286
        # Find the last 3 people who borrowed this item.
1296
        my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
1287
        my $sth2 = $dbh->prepare("SELECT * FROM old_issues,borrowers
1297
                                    WHERE itemnumber = ?
1288
                                    WHERE itemnumber = ?
Lines 1307-1314 sub GetItemsInfo { Link Here
1307
            $ii++;
1298
            $ii++;
1308
        }
1299
        }
1309
1300
1310
        $results[$i] = $data;
1301
        push @results, $data;
1311
        $i++;
1312
    }
1302
    }
1313
	if($serial) {
1303
	if($serial) {
1314
		return( sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results );
1304
		return( sort { ($b->{'publisheddate'} || $b->{'enumchron'}) cmp ($a->{'publisheddate'} || $a->{'enumchron'}) } @results );
(-)a/C4/Reserves.pm (-19 / +36 lines)
Lines 797-822 sub GetReserveStatus { Link Here
797
  ($status, $reserve) = &CheckReserves($itemnumber);
797
  ($status, $reserve) = &CheckReserves($itemnumber);
798
  ($status, $reserve) = &CheckReserves(undef, $barcode);
798
  ($status, $reserve) = &CheckReserves(undef, $barcode);
799
799
800
Find a book in the reserves.
800
Find the book by its itemnumber or barcode, and call C<MostImportantReserve>
801
801
802
C<$itemnumber> is the book's item number.
802
C<$itemnumber> is the book's item number.
803
803
C<$barcode> is the book's barcode
804
As I understand it, C<&CheckReserves> looks for the given item in the
805
reserves. If it is found, that's a match, and C<$status> is set to
806
C<Waiting>.
807
808
Otherwise, it finds the most important item in the reserves with the
809
same biblio number as this book (I'm not clear on this) and returns it
810
with C<$status> set to C<Reserved>.
811
812
C<&CheckReserves> returns a two-element list:
813
814
C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
815
816
C<$reserve> is the reserve item that matched. It is a
817
reference-to-hash whose keys are mostly the fields of the reserves
818
table in the Koha database.
819
820
=cut
804
=cut
821
805
822
sub CheckReserves {
806
sub CheckReserves {
Lines 851-856 sub CheckReserves { Link Here
851
    #    execpt where items.notforloan < 0 :  This indicates the item is holdable. 
835
    #    execpt where items.notforloan < 0 :  This indicates the item is holdable. 
852
    return ( 0, 0 ) if  ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
836
    return ( 0, 0 ) if  ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
853
837
838
    return MostImportantReserve($bibitem, $biblio, $itemnumber);
839
}
840
841
=head2 MostImportantReserve
842
843
  ($status, $reserve) = &MostImportantReserve($itemnumber);
844
845
Find the most important reserve for a book.
846
847
C<$bibitem> is the book's biblioitem number.
848
C<$biblio> is the book's biblio number.
849
C<$itemnumber> is the book's item number.
850
851
As I understand it, C<&MostImportantReserve> looks for the given item in the
852
reserves. If it is found, that's a match, and C<$status> is set to
853
C<Waiting>.
854
855
Otherwise, it finds the most important item in the reserves with the
856
same biblio number as this book (I'm not clear on this) and returns it
857
with C<$status> set to C<Reserved>.
858
859
C<&MostImportantReserve> returns a two-element list:
860
861
C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
862
863
C<$reserve> is the reserve item that matched. It is a
864
reference-to-hash whose keys are mostly the fields of the reserves
865
table in the Koha database.
866
867
=cut
868
869
sub MostImportantReserve {
870
    my ( $bibitem, $biblio, $itemnumber ) = @_;
854
    # Find this item in the reserves
871
    # Find this item in the reserves
855
    my @reserves = _Findgroupreserve( $bibitem, $biblio, $itemnumber );
872
    my @reserves = _Findgroupreserve( $bibitem, $biblio, $itemnumber );
856
873
Lines 877-883 sub CheckReserves { Link Here
877
    # If we get this far, then no exact match was found.
894
    # If we get this far, then no exact match was found.
878
    # We return the most important (i.e. next) reservation.
895
    # We return the most important (i.e. next) reservation.
879
    if ($highest) {
896
    if ($highest) {
880
        $highest->{'itemnumber'} = $item;
897
        $highest->{'itemnumber'} = $itemnumber;
881
        return ( "Reserved", $highest );
898
        return ( "Reserved", $highest );
882
    }
899
    }
883
    else {
900
    else {
(-)a/installer/data/mysql/kohastructure.sql (-1 / +2 lines)
Lines 1517-1523 CREATE TABLE `reserveconstraints` ( Link Here
1517
  `reservedate` date default NULL,
1517
  `reservedate` date default NULL,
1518
  `biblionumber` int(11) NOT NULL default 0,
1518
  `biblionumber` int(11) NOT NULL default 0,
1519
  `biblioitemnumber` int(11) default NULL,
1519
  `biblioitemnumber` int(11) default NULL,
1520
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1520
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1521
  KEY `biblio` (`biblionumber`, `biblioitemnumber`)
1521
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1522
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1522
1523
1523
--
1524
--
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +7 lines)
Lines 3744-3749 if (C4::Context->preference("Version") < TransformToNum($DBversion)) { Link Here
3744
    SetVersion ($DBversion);
3744
    SetVersion ($DBversion);
3745
}
3745
}
3746
3746
3747
$DBversion = "3.01.00.XXX";
3748
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3749
    $dbh->do("CREATE INDEX `reserveconstraints_biblio_idx` ON reserveconstraints (`biblionumber`, `biblioitemnumber`) ");
3750
    print "Upgrade to $DBversion done (reserveconstraints_biblio_idx index added)\n";
3751
    SetVersion ($DBversion);
3752
}
3753
3747
3754
3748
=item DropAllForeignKeys($table)
3755
=item DropAllForeignKeys($table)
3749
3756
3750
- 

Return to bug 5304