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

(-)a/circ/reserveratios.pl (-160 / +21 lines)
Lines 21-27 Link Here
21
use Modern::Perl;
21
use Modern::Perl;
22
22
23
use CGI qw ( -utf8 );
23
use CGI qw ( -utf8 );
24
use Date::Calc qw/Today Add_Delta_YM/;
25
24
26
use C4::Context;
25
use C4::Context;
27
use C4::Output;
26
use C4::Output;
Lines 30-36 use C4::Debug; Link Here
30
use C4::Biblio qw/GetMarcBiblio GetRecordValue GetFrameworkCode/;
29
use C4::Biblio qw/GetMarcBiblio GetRecordValue GetFrameworkCode/;
31
use C4::Acquisition qw/GetOrdersByBiblionumber/;
30
use C4::Acquisition qw/GetOrdersByBiblionumber/;
32
use Koha::DateUtils;
31
use Koha::DateUtils;
33
use Koha::Acquisition::Baskets;
32
use Koha::Util::HoldsRatios;
34
33
35
my $input = new CGI;
34
my $input = new CGI;
36
my $startdate       = $input->param('from');
35
my $startdate       = $input->param('from');
Lines 38-43 my $enddate = $input->param('to'); Link Here
38
my $ratio           = $input->param('ratio');
37
my $ratio           = $input->param('ratio');
39
my $include_ordered = $input->param('include_ordered');
38
my $include_ordered = $input->param('include_ordered');
40
39
40
# If coming from acq we may be getting ratios for titles in a single basket
41
my $booksellerid    = $input->param('booksellerid') // '';
42
my $basketno        = $input->param('basketno') // '';
43
$debug and warn output_pref({ dt => $startdate, dateformat => 'iso', dateonly => 1 }) . "\n" . output_pref({ dt => $enddate, dateformat => 'iso', dateonly => 1 });
44
41
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
45
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
42
    {
46
    {
43
        template_name   => "circ/reserveratios.tt",
47
        template_name   => "circ/reserveratios.tt",
Lines 49-218 my ( $template, $loggedinuser, $cookie ) = get_template_and_user( Link Here
49
    }
53
    }
50
);
54
);
51
55
52
my $booksellerid = $input->param('booksellerid') // '';
56
my ($reservedata,$sql) = Koha::Util::HoldsRatios::get_ratios({
53
my $basketno = $input->param('basketno') // '';
57
    from            => $startdate,
54
if ($booksellerid && $basketno) {
58
    to              => $enddate,
55
     $template->param( booksellerid => $booksellerid, basketno => $basketno );
59
    ratio           => $ratio,
56
}
60
    include_ordered => $include_ordered,
57
61
    basketno        => $basketno
58
my $effective_create_items = q{};
62
});
59
if ( $basketno ){
60
    my $basket = Koha::Acquisition::Baskets->find( $basketno );
61
    if ($basket){
62
        $effective_create_items = $basket->effective_create_items;
63
    } else {
64
        $effective_create_items = C4::Context->preference('AcqCreateItem');
65
    }
66
}
67
68
$startdate = eval { dt_from_string( $startdate ) } if $startdate;
69
$enddate = eval { dt_from_string( $enddate ) } if $enddate;
70
71
my $todaysdate = dt_from_string;
72
73
#    A default of the prior years's holds is a reasonable way to pull holds
74
$enddate = $todaysdate unless $enddate;
75
$startdate = $todaysdate->clone->subtract( years => 1 ) unless $startdate;
76
77
if (!defined($ratio)) {
78
    $ratio = 3;
79
}
80
# Force to be a number
81
$ratio += 0;
82
if ($ratio <= 0) {
83
    $ratio = 1; # prevent division by zero
84
}
85
86
my $dbh    = C4::Context->dbh;
87
my $sqldatewhere = "";
88
$debug and warn output_pref({ dt => $startdate, dateformat => 'iso', dateonly => 1 }) . "\n" . output_pref({ dt => $enddate, dateformat => 'iso', dateonly => 1 });
89
my @query_params = ();
90
91
$sqldatewhere .= " AND reservedate >= ?";
92
push @query_params, output_pref({ dt => $startdate, dateformat => 'iso' }) ;
93
$sqldatewhere .= " AND reservedate <= ?";
94
push @query_params, output_pref({ dt => $enddate, dateformat => 'iso' });
95
96
my $include_aqorders_qty =
97
  $effective_create_items eq 'receiving'
98
  ? '+ COALESCE(aqorders.quantity, 0) - COALESCE(aqorders.quantityreceived, 0)'
99
  : q{};
100
101
my $include_aqorders_qty_join =
102
  $effective_create_items eq 'receiving'
103
  ? 'LEFT JOIN aqorders ON reserves.biblionumber=aqorders.biblionumber'
104
  : q{};
105
106
my $nfl_comparison = $include_ordered ? '<=' : '=';
107
my $strsth =
108
"SELECT reservedate,
109
        reserves.borrowernumber as borrowernumber,
110
        reserves.biblionumber,
111
        reserves.branchcode as branch,
112
        items.holdingbranch,
113
        items.itemcallnumber,
114
        items.itemnumber,
115
        GROUP_CONCAT(DISTINCT items.itemcallnumber 
116
            ORDER BY items.itemnumber SEPARATOR '|') as listcall,
117
        GROUP_CONCAT(DISTINCT homebranch
118
            ORDER BY items.itemnumber SEPARATOR '|') as homebranch_list,
119
        GROUP_CONCAT(DISTINCT holdingbranch 
120
            ORDER BY items.itemnumber SEPARATOR '|') as holdingbranch_list,
121
        GROUP_CONCAT(DISTINCT items.location 
122
            ORDER BY items.itemnumber SEPARATOR '|') as l_location,
123
        GROUP_CONCAT(DISTINCT items.itype 
124
            ORDER BY items.itemnumber SEPARATOR '|') as l_itype,
125
126
        reserves.found,
127
        biblio.title,
128
        biblio.author,
129
        count(DISTINCT reserves.borrowernumber) as reservecount, 
130
        count(DISTINCT items.itemnumber) $include_aqorders_qty as itemcount
131
 FROM  reserves
132
 LEFT JOIN items ON items.biblionumber=reserves.biblionumber 
133
 LEFT JOIN biblio ON reserves.biblionumber=biblio.biblionumber
134
 $include_aqorders_qty_join
135
 WHERE
136
 notforloan $nfl_comparison 0 AND damaged = 0 AND itemlost = 0 AND withdrawn = 0
137
 $sqldatewhere
138
";
139
140
if (C4::Context->preference('IndependentBranches')){
141
    $strsth .= " AND items.holdingbranch=? ";
142
    push @query_params, C4::Context->userenv->{'branch'};
143
}
144
145
$strsth .= " GROUP BY reserves.biblionumber ORDER BY reservecount DESC";
146
147
$template->param(sql => $strsth);
148
my $sth = $dbh->prepare($strsth);
149
$sth->execute(@query_params);
150
151
my $ratio_atleast1 = ($ratio >= 1) ? 1 : 0;
152
my @reservedata;
153
while ( my $data = $sth->fetchrow_hashref ) {
154
    my $thisratio = $data->{reservecount} / $data->{itemcount};
155
    my $ratiocalc = ($thisratio / $ratio);
156
    ($thisratio / $ratio) >= 1 or next;  # TODO: tighter targeting -- get ratio limit into SQL using HAVING clause
157
    my $record = GetMarcBiblio({ biblionumber => $data->{biblionumber} });
158
    $data->{subtitle} = GetRecordValue('subtitle', $record, GetFrameworkCode($data->{biblionumber}));
159
    push(
160
        @reservedata,
161
        {
162
            reservedate        => $data->{reservedate},
163
            priority           => $data->{priority},
164
            name               => $data->{borrower},
165
            title              => $data->{title},
166
            subtitle           => $data->{subtitle},
167
            author             => $data->{author},
168
            itemnum            => $data->{itemnumber},
169
            biblionumber       => $data->{biblionumber},
170
            holdingbranch      => $data->{holdingbranch},
171
            homebranch_list    => [split('\|', $data->{homebranch_list})],
172
            holdingbranch_list => [split('\|', $data->{holdingbranch_list})],
173
            branch             => $data->{branch},
174
            itemcallnumber     => $data->{itemcallnumber},
175
            location           => [split('\|', $data->{l_location})],
176
            itype              => [split('\|', $data->{l_itype})],
177
            reservecount       => $data->{reservecount},
178
            itemcount          => $data->{itemcount},
179
            ratiocalc          => sprintf( "%.0d", $ratio_atleast1 ? ( $thisratio / $ratio ) : $thisratio ),
180
            thisratio => sprintf( "%.2f", $thisratio ),
181
            thisratio_atleast1 => ( $thisratio >= 1 ) ? 1 : 0,
182
            listcall           => [split('\|', $data->{listcall})]
183
        }
184
    );
185
}
186
187
for my $rd ( @reservedata ) {
188
    next unless $rd->{biblionumber};
189
    $rd->{pendingorders} = CountPendingOrdersByBiblionumber( $rd->{biblionumber} );
190
}
191
63
192
$template->param(
64
$template->param(
193
    ratio_atleast1  => $ratio_atleast1,
65
    todaysdate      => dt_from_string,
194
    todaysdate      => $todaysdate,
195
    from            => $startdate,
66
    from            => $startdate,
196
    to              => $enddate,
67
    to              => $enddate,
197
    ratio           => $ratio,
68
    ratio           => $ratio,
198
    include_ordered => $include_ordered,
69
    include_ordered => $include_ordered,
199
    reserveloop     => \@reservedata,
70
    reserveloop     => $reservedata,
200
);
71
);
72
if ($booksellerid && $basketno) {
73
    $template->param( 
74
        booksellerid => $booksellerid, 
75
        basketno => $basketno 
76
    );
77
}
201
78
202
output_html_with_http_headers $input, $cookie, $template->output;
79
output_html_with_http_headers $input, $cookie, $template->output;
203
80
204
sub CountPendingOrdersByBiblionumber {
205
    my $biblionumber = shift;
206
    my @orders = GetOrdersByBiblionumber( $biblionumber );
207
    my $cnt = 0;
208
    if (scalar(@orders)) {
209
        for my $order ( @orders ) {
210
            next if $order->{datecancellationprinted};
211
            my $onum = $order->{quantity} // 0;
212
            my $rnum = $order->{quantityreceived} // 0;
213
            next if $rnum >= $onum;
214
            $cnt += ($onum - $rnum);
215
        }
216
    }
217
    return $cnt;
218
}
219
- 

Return to bug 23360