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

(-)a/C4/Items.pm (-8 / +33 lines)
Lines 1024-1030 sub GetLostItems { Link Here
1024
1024
1025
=head2 GetItemsForInventory
1025
=head2 GetItemsForInventory
1026
1026
1027
($itemlist, $iTotalRecords)  = GetItemsForInventory($minlocation, $maxlocation, $location, $itemtype, $ignoreissued, $datelastseen, $branchcode, $offset, $size, $statushash);
1027
($itemlist, $iTotalRecords) = GetItemsForInventory( {
1028
  minlocation  => $minlocation,
1029
  maxlocation  => $maxlocation,
1030
  location     => $location,
1031
  itemtype     => $itemtype,
1032
  ignoreissued => $ignoreissued,
1033
  datelastseen => $datelastseen,
1034
  branchcode   => $branchcode,
1035
  branch       => $branch,
1036
  offset       => $offset,
1037
  size         => $size,
1038
  stautshash   => $statushash
1039
  interface    => $interface,
1040
} );
1028
1041
1029
Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
1042
Retrieve a list of title/authors/barcode/callnumber, for biblio inventory.
1030
1043
Lines 1042-1048 $iTotalRecords is the number of rows that would have been returned without the $ Link Here
1042
=cut
1055
=cut
1043
1056
1044
sub GetItemsForInventory {
1057
sub GetItemsForInventory {
1045
    my ( $minlocation, $maxlocation,$location, $itemtype, $ignoreissued, $datelastseen, $branchcode, $branch, $offset, $size, $statushash ) = @_;
1058
    my ( $parameters ) = @_;
1059
    my $minlocation  = $parameters->{'minlocation'}  // '';
1060
    my $maxlocation  = $parameters->{'maxlocation'}  // '';
1061
    my $location     = $parameters->{'location'}     // '';
1062
    my $itemtype     = $parameters->{'itemtype'}     // '';
1063
    my $ignoreissued = $parameters->{'ignoreissued'} // '';
1064
    my $datelastseen = $parameters->{'datelastseen'} // '';
1065
    my $branchcode   = $parameters->{'branchcode'}   // '';
1066
    my $branch       = $parameters->{'branch'}       // '';
1067
    my $offset       = $parameters->{'offset'}       // '';
1068
    my $size         = $parameters->{'size'}         // '';
1069
    my $statushash   = $parameters->{'statushash'}   // '';
1070
    my $interface    = $parameters->{'interface'}    // '';
1071
1046
    my $dbh = C4::Context->dbh;
1072
    my $dbh = C4::Context->dbh;
1047
    my ( @bind_params, @where_strings );
1073
    my ( @bind_params, @where_strings );
1048
1074
Lines 1121-1136 sub GetItemsForInventory { Link Here
1121
    $sth->execute( @bind_params );
1147
    $sth->execute( @bind_params );
1122
    my ($iTotalRecords) = $sth->fetchrow_array();
1148
    my ($iTotalRecords) = $sth->fetchrow_array();
1123
1149
1150
    my $avmapping = C4::Koha::GetKohaAuthorisedValuesMapping( {
1151
                      interface => $interface
1152
                    } );
1124
    foreach my $row (@$tmpresults) {
1153
    foreach my $row (@$tmpresults) {
1125
1154
1126
        # Auth values
1155
        # Auth values
1127
        foreach (keys %$row) {
1156
        foreach (keys %$row) {
1128
            # If the koha field is mapped to a marc field
1157
            if (defined($avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}})) {
1129
            my ($f, $sf) = GetMarcFromKohaField("items.$_", $row->{'frameworkcode'});
1158
                $row->{$_} = $avmapping->{"items.$_,".$row->{'frameworkcode'}.",".$row->{$_}};
1130
            if ($f and $sf) {
1131
                # We replace the code with it's description
1132
                my $authvals = C4::Koha::GetKohaAuthorisedValuesFromField($f, $sf, $row->{'frameworkcode'});
1133
                $row->{$_} = $authvals->{$row->{$_}} if defined $authvals->{$row->{$_}};
1134
            }
1159
            }
1135
        }
1160
        }
1136
        push @results, $row;
1161
        push @results, $row;
(-)a/C4/Koha.pm (+45 lines)
Lines 62-67 BEGIN { Link Here
62
                &IsAuthorisedValueCategory
62
                &IsAuthorisedValueCategory
63
		&GetKohaAuthorisedValues
63
		&GetKohaAuthorisedValues
64
		&GetKohaAuthorisedValuesFromField
64
		&GetKohaAuthorisedValuesFromField
65
    &GetKohaAuthorisedValuesMapping
65
    &GetKohaAuthorisedValueLib
66
    &GetKohaAuthorisedValueLib
66
    &GetAuthorisedValueByCode
67
    &GetAuthorisedValueByCode
67
    &GetKohaImageurlFromAuthorisedValues
68
    &GetKohaImageurlFromAuthorisedValues
Lines 1327-1332 sub GetKohaAuthorisedValuesFromField { Link Here
1327
  }
1328
  }
1328
}
1329
}
1329
1330
1331
=head2 GetKohaAuthorisedValuesMapping
1332
1333
Takes a hash as a parameter. The interface key indicates the
1334
description to use in the mapping.
1335
1336
Returns hashref of:
1337
 "{kohafield},{frameworkcode},{authorised_value}" => "{description}"
1338
for all the kohafields, frameworkcodes, and authorised values.
1339
1340
Returns undef if nothing is found.
1341
1342
=cut
1343
1344
sub GetKohaAuthorisedValuesMapping {
1345
    my ($parameter) = @_;
1346
    my $interface = $parameter->{'interface'} // '';
1347
1348
    my $query_mapping = q{
1349
SELECT TA.kohafield,TA.authorised_value AS category,
1350
       TA.frameworkcode,TB.authorised_value,
1351
       IF(TB.lib_opac>'',TB.lib_opac,TB.lib) AS OPAC,
1352
       TB.lib AS Intranet,TB.lib_opac
1353
FROM marc_subfield_structure AS TA JOIN
1354
     authorised_values as TB ON
1355
     TA.authorised_value=TB.category
1356
WHERE TA.kohafield>'' AND TA.authorised_value>'';
1357
    };
1358
    my $dbh = C4::Context->dbh;
1359
    my $sth = $dbh->prepare($query_mapping);
1360
    $sth->execute();
1361
    my $avmapping;
1362
    if ($interface eq 'opac') {
1363
        while (my $row = $sth->fetchrow_hashref) {
1364
            $avmapping->{$row->{kohafield}.",".$row->{frameworkcode}.",".$row->{authorised_value}} = $row->{OPAC};
1365
        }
1366
    }
1367
    else {
1368
        while (my $row = $sth->fetchrow_hashref) {
1369
            $avmapping->{$row->{kohafield}.",".$row->{frameworkcode}.",".$row->{authorised_value}} = $row->{Intranet};
1370
        }
1371
    }
1372
    return $avmapping;
1373
}
1374
1330
=head2 xml_escape
1375
=head2 xml_escape
1331
1376
1332
  my $escaped_string = C4::Koha::xml_escape($string);
1377
  my $escaped_string = C4::Koha::xml_escape($string);
(-)a/t/db_dependent/Items/GetItemsForInventory.t (+156 lines)
Line 0 Link Here
1
#!/usr/bin/perl
2
#
3
# This file is part of Koha.
4
#
5
# Copyright (c) 2015   Mark Tompsett
6
#
7
# Koha is free software; you can redistribute it and/or modify it
8
# under the terms of the GNU General Public License as published by
9
# the Free Software Foundation; either version 3 of the License, or
10
# (at your option) any later version.
11
#
12
# Koha is distributed in the hope that it will be useful, but
13
# WITHOUT ANY WARRANTY; without even the implied warranty of
14
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15
# GNU General Public License for more details.
16
#
17
# You should have received a copy of the GNU General Public License
18
# along with Koha; if not, see <http://www.gnu.org/licenses>.
19
20
use Modern::Perl;
21
use Test::More tests => 6;
22
23
$| = 1;
24
25
BEGIN {
26
    use_ok('C4::Context');
27
    use_ok('C4::Items');
28
    use_ok('C4::Biblio');
29
    use_ok('C4::Koha');
30
}
31
32
can_ok('C4::Items','GetItemsForInventory');
33
34
my $dbh = C4::Context->dbh;
35
$dbh->{AutoCommit} = 0;
36
$dbh->{RaiseError} = 1;
37
38
diag("This could take a while for large datasets...");
39
40
my ($oldResults, $oldCount) = OldWay($dbh);
41
my ($newResults, $newCount) = GetItemsForInventory( { interface => 'staff' } );
42
43
is_deeply($newResults,$oldResults,"Inventory results unchanged.");
44
45
$dbh->rollback;
46
47
sub OldWay {
48
    my ($tdbh)       = @_;
49
    my $ldbh         = $tdbh;
50
    my $minlocation  = '';
51
    my $maxlocation  = '';
52
    my $location     = '';
53
    my $itemtype     = '';
54
    my $ignoreissued = '';
55
    my $datelastseen = '';
56
    my $branchcode   = '';
57
    my $branch       = '';
58
    my $offset       = '';
59
    my $size         = '';
60
    my $statushash   = '';
61
    my $interface    = '';
62
63
    my ( @bind_params, @where_strings );
64
65
    my $select_columns = q{
66
        SELECT items.itemnumber, barcode, itemcallnumber, title, author, biblio.biblionumber, biblio.frameworkcode, datelastseen, homebranch, location, notforloan, damaged, itemlost, withdrawn, stocknumber
67
    };
68
    my $select_count = q{SELECT COUNT(*)};
69
    my $query = q{
70
        FROM items
71
        LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
72
        LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber
73
    };
74
    if ($statushash){
75
        for my $authvfield (keys %$statushash){
76
            if ( scalar @{$statushash->{$authvfield}} > 0 ){
77
                my $joinedvals = join ',', @{$statushash->{$authvfield}};
78
                push @where_strings, "$authvfield in (" . $joinedvals . ")";
79
            }
80
        }
81
    }
82
83
    if ($minlocation) {
84
        push @where_strings, 'itemcallnumber >= ?';
85
        push @bind_params, $minlocation;
86
    }
87
88
    if ($maxlocation) {
89
        push @where_strings, 'itemcallnumber <= ?';
90
        push @bind_params, $maxlocation;
91
    }
92
93
    if ($datelastseen) {
94
        $datelastseen = format_date_in_iso($datelastseen);
95
        push @where_strings, '(datelastseen < ? OR datelastseen IS NULL)';
96
        push @bind_params, $datelastseen;
97
    }
98
99
    if ( $location ) {
100
        push @where_strings, 'items.location = ?';
101
        push @bind_params, $location;
102
    }
103
104
    if ( $branchcode ) {
105
        if($branch eq "homebranch"){
106
        push @where_strings, 'items.homebranch = ?';
107
        }else{
108
            push @where_strings, 'items.holdingbranch = ?';
109
        }
110
        push @bind_params, $branchcode;
111
    }
112
113
    if ( $itemtype ) {
114
        push @where_strings, 'biblioitems.itemtype = ?';
115
        push @bind_params, $itemtype;
116
    }
117
118
    if ( $ignoreissued) {
119
        $query .= "LEFT JOIN issues ON items.itemnumber = issues.itemnumber ";
120
        push @where_strings, 'issues.date_due IS NULL';
121
    }
122
123
    if ( @where_strings ) {
124
        $query .= 'WHERE ';
125
        $query .= join ' AND ', @where_strings;
126
    }
127
    $query .= ' ORDER BY items.cn_sort, itemcallnumber, title';
128
    my $count_query = $select_count . $query;
129
    $query .= " LIMIT $offset, $size" if ($offset and $size);
130
    $query = $select_columns . $query;
131
    my $sth = $ldbh->prepare($query);
132
    $sth->execute( @bind_params );
133
134
    my @results = ();
135
    my $tmpresults = $sth->fetchall_arrayref({});
136
    $sth = $ldbh->prepare( $count_query );
137
    $sth->execute( @bind_params );
138
    my ($iTotalRecords) = $sth->fetchrow_array();
139
140
    foreach my $row (@$tmpresults) {
141
142
        # Auth values
143
        foreach my $field (sort keys %$row) {
144
            # If the koha field is mapped to a marc field
145
            my ($f, $sf) = C4::Biblio::GetMarcFromKohaField("items.$field", $row->{'frameworkcode'});
146
            if (defined($f) and defined($sf)) {
147
                # We replace the code with it's description
148
                my $authvals = C4::Koha::GetKohaAuthorisedValuesFromField($f, $sf, $row->{'frameworkcode'});
149
                $row->{$field} = $authvals->{$row->{$field}} if defined $authvals && defined $row->{$field} && defined $authvals->{$row->{$field}};
150
            }
151
        }
152
        push @results, $row;
153
    }
154
155
    return (\@results, $iTotalRecords);
156
}
(-)a/t/db_dependent/Koha/GetKohaAuthorisedValuesMapping.t (+54 lines)
Line 0 Link Here
1
#!/usr/bin/perl
2
#
3
# This file is part of Koha.
4
#
5
# Copyright (c) 2015   Mark Tompsett
6
#
7
# Koha is free software; you can redistribute it and/or modify it
8
# under the terms of the GNU General Public License as published by
9
# the Free Software Foundation; either version 3 of the License, or
10
# (at your option) any later version.
11
#
12
# Koha is distributed in the hope that it will be useful, but
13
# WITHOUT ANY WARRANTY; without even the implied warranty of
14
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15
# GNU General Public License for more details.
16
#
17
# You should have received a copy of the GNU General Public License
18
# along with Koha; if not, see <http://www.gnu.org/licenses>.
19
20
use Modern::Perl;
21
22
use C4::Context;
23
24
use Test::More tests => 8;
25
26
BEGIN {
27
    use_ok('C4::Context');
28
    use_ok('C4::Koha');
29
}
30
31
can_ok('C4::Koha','GetKohaAuthorisedValuesMapping');
32
33
my $avMappingOPAC;
34
my $avMappingStaff;
35
my $avMappingUndef1;
36
my $avMappingUndef2;
37
my $avMappingUndef3;
38
SKIP: {
39
    my $dbh = C4::Context->dbh;
40
    my $count = $dbh->selectrow_arrayref("SELECT COUNT(*) FROM marc_subfield_structure WHERE kohafield LIKE 'item%';");
41
    skip "Lacking item mappings in marc_subfield_structure",5 unless ($count && $count->[0]>0);
42
    $count = $dbh->selectrow_arrayref("SELECT COUNT(*) FROM authorised_values;");
43
    skip "Lacking authorised_values",5 unless ($count && $count->[0]>0);
44
    $avMappingOPAC   = GetKohaAuthorisedValuesMapping( { interface => 'opac' });
45
    $avMappingStaff  = GetKohaAuthorisedValuesMapping( { interface => 'staff' });
46
    $avMappingUndef1 = GetKohaAuthorisedValuesMapping( { interface => undef });
47
    $avMappingUndef2 = GetKohaAuthorisedValuesMapping( { } );
48
    $avMappingUndef3 = GetKohaAuthorisedValuesMapping();
49
    is_deeply($avMappingUndef1,$avMappingStaff,"Undefined interface = Staff test 1");
50
    is_deeply($avMappingUndef2,$avMappingStaff,"Undefined interface = Staff test 2");
51
    is_deeply($avMappingUndef3,$avMappingStaff,"Undefined interface = Staff test 3");
52
    isnt($avMappingOPAC ,undef,"OPAC has a mapping");
53
    isnt($avMappingStaff,undef,"Staff has a mapping");
54
}
(-)a/tools/inventory.pl (-3 / +28 lines)
Lines 234-243 if ( $markseen or $op ) { Link Here
234
234
235
    # We use datelastseen only when comparing the results to the barcode file.
235
    # We use datelastseen only when comparing the results to the barcode file.
236
    my $paramdatelastseen = ($compareinv2barcd) ? $datelastseen : '';
236
    my $paramdatelastseen = ($compareinv2barcd) ? $datelastseen : '';
237
    ($inventorylist, $totalrecords) = GetItemsForInventory($minlocation, $maxlocation, $location, $itemtype, $ignoreissued, $paramdatelastseen, $branchcode, $branch, 0, undef, $staton);
237
    ($inventorylist, $totalrecords) = GetItemsForInventory( {
238
      minlocation  => $minlocation,
239
      maxlocation  => $maxlocation,
240
      location     => $location,
241
      itemtype     => $itemtype,
242
      ignoreissued => $ignoreissued,
243
      datelastseen => $paramdatelastseen,
244
      branchcode   => $branchcode,
245
      branch       => $branch,
246
      offset       => 0,
247
      size         => undef,
248
      statushash   => $staton,
249
      interface    => 'staff',
250
    } );
238
251
239
    # For the items that may be marked as "wrong place", we only check the location (callnumbers, location and branch)
252
    # For the items that may be marked as "wrong place", we only check the location (callnumbers, location and branch)
240
    ($wrongplacelist, $totalrecords) = GetItemsForInventory($minlocation, $maxlocation, $location, undef, undef, undef, $branchcode, $branch, 0, undef, undef);
253
    ($wrongplacelist, $totalrecords) = GetItemsForInventory( {
254
      minlocation  => $minlocation,
255
      maxlocation  => $maxlocation,
256
      location     => $location,
257
      itemtype     => undef,
258
      ignoreissued => undef,
259
      datelastseen => undef,
260
      branchcode   => $branchcode,
261
      branch       => $branch,
262
      offset       => 0,
263
      size         => undef,
264
      statushash   => undef,
265
      interface    => 'staff',
266
    } );
241
267
242
}
268
}
243
269
244
- 

Return to bug 14057