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

(-)a/reports/cat_issues_top.pl (-32 / +39 lines)
Lines 204-210 sub calculate { Link Here
204
    my $colfield;
204
    my $colfield;
205
    my $colorder;
205
    my $colorder;
206
    if ($column){
206
    if ($column){
207
        $column = "old_issues.".$column if (($column=~/branchcode/) or ($column=~/timestamp/));
207
        $column = "circulated.".$column if (($column=~/branchcode/) or ($column=~/timestamp/));
208
        if($column=~/itemtype/){
208
        if($column=~/itemtype/){
209
            $column = C4::Context->preference('item-level_itypes') ? "items.itype": "biblioitems.itemtype";
209
            $column = C4::Context->preference('item-level_itypes') ? "items.itype": "biblioitems.itemtype";
210
        }
210
        }
Lines 226-242 sub calculate { Link Here
226
    # loop cols.
226
    # loop cols.
227
        if ($column eq "Day") {
227
        if ($column eq "Day") {
228
            #Display by day
228
            #Display by day
229
            $column = "old_issues.timestamp";
229
            $column = "circulated.timestamp";
230
            $colfield .="dayname($column)";  
230
            $colfield .="dayname($column)";  
231
            $colorder .="weekday($column)";
231
            $colorder .="weekday($column)";
232
        } elsif ($column eq "Month") {
232
        } elsif ($column eq "Month") {
233
            #Display by Month
233
            #Display by Month
234
            $column = "old_issues.timestamp";
234
            $column = "circulated.timestamp";
235
            $colfield .="monthname($column)";  
235
            $colfield .="monthname($column)";  
236
            $colorder .="month($column)";  
236
            $colorder .="month($column)";  
237
        } elsif ($column eq "Year") {
237
        } elsif ($column eq "Year") {
238
            #Display by Year
238
            #Display by Year
239
            $column = "old_issues.timestamp";
239
            $column = "circulated.timestamp";
240
            $colfield .="Year($column)";
240
            $colfield .="Year($column)";
241
            $colorder .= $column;
241
            $colorder .= $column;
242
        } else {
242
        } else {
Lines 245-268 sub calculate { Link Here
245
        }  
245
        }  
246
        
246
        
247
        my $strsth2;
247
        my $strsth2;
248
        $strsth2 .= "SELECT distinctrow $colfield 
248
        my $strsth2_filter;
249
                     FROM `old_issues` 
249
        $strsth2_filter = "";
250
                     LEFT JOIN borrowers ON borrowers.borrowernumber=old_issues.borrowernumber 
251
                     LEFT JOIN items ON old_issues.itemnumber=items.itemnumber 
252
                     LEFT JOIN biblioitems  ON biblioitems.biblioitemnumber=items.biblioitemnumber 
253
                     WHERE 1";
254
        if (($column=~/issuedate/) or ($column=~/returndate/)){
250
        if (($column=~/issuedate/) or ($column=~/returndate/)){
255
            if ($colfilter[1] and ($colfilter[0])){
251
            if ($colfilter[1] and ($colfilter[0])){
256
                $strsth2 .= " and $column between '$colfilter[0]' and '$colfilter[1]' " ;
252
                $strsth2_filter .= " and $column between '$colfilter[0]' and '$colfilter[1]' " ;
257
            } elsif ($colfilter[1]) {
253
            } elsif ($colfilter[1]) {
258
                    $strsth2 .= " and $column < '$colfilter[1]' " ;
254
                    $strsth2_filter .= " and $column < '$colfilter[1]' " ;
259
            } elsif ($colfilter[0]) {
255
            } elsif ($colfilter[0]) {
260
                $strsth2 .= " and $column > '$colfilter[0]' " ;
256
                $strsth2_filter .= " and $column > '$colfilter[0]' " ;
261
            }
257
            }
262
        } elsif ($colfilter[0]) {
258
        } elsif ($colfilter[0]) {
263
            $colfilter[0] =~ s/\*/%/g;
259
            $colfilter[0] =~ s/\*/%/g;
264
            $strsth2 .= " and $column LIKE '$colfilter[0]' " ;
260
            $strsth2_filter .= " and $column LIKE '$colfilter[0]' " ;
265
        }
261
        }
262
        $strsth2 .= "SELECT distinctrow $colfield 
263
                     FROM ( SELECT * FROM `old_issues` WHERE 1 $strsth2_filter
264
                     UNION ALL
265
                     SELECT * FROM `issues` WHERE 1 $strsth2_filter ) AS circulated 
266
                     LEFT JOIN borrowers ON borrowers.borrowernumber=circulated.borrowernumber 
267
                     LEFT JOIN items ON circulated.itemnumber=items.itemnumber 
268
                     LEFT JOIN biblioitems  ON biblioitems.biblioitemnumber=items.biblioitemnumber";
266
        $strsth2 .=" group by $colfield";
269
        $strsth2 .=" group by $colfield";
267
        $strsth2 .=" order by $colorder";
270
        $strsth2 .=" order by $colorder";
268
        
271
        
Lines 302-328 sub calculate { Link Here
302
305
303
# preparing calculation
306
# preparing calculation
304
    my $strcalc ;
307
    my $strcalc ;
308
    my $strcalc_filter;
305
    
309
    
310
    $strcalc_filter = "";
311
    @$filters[0]=~ s/\*/%/g if (@$filters[0]);
312
    $strcalc_filter .= " AND issuedate > '" . @$filters[0] ."'" if ( @$filters[0] );
313
    @$filters[1]=~ s/\*/%/g if (@$filters[1]);
314
    $strcalc_filter .= " AND issuedate < '" . @$filters[1] ."'" if ( @$filters[1] );
315
    @$filters[2]=~ s/\*/%/g if (@$filters[2]);
316
    $strcalc_filter .= " AND returndate > '" . @$filters[2] ."'" if ( @$filters[2] );
317
    @$filters[3]=~ s/\*/%/g if (@$filters[3]);
318
    $strcalc_filter .= " AND returndate < '" . @$filters[3] ."'" if ( @$filters[3] );
319
    @$filters[4]=~ s/\*/%/g if (@$filters[4]);
320
    $strcalc_filter .= " AND branchcode like '" . @$filters[4] ."'" if ( @$filters[4] );
321
    @$filters[7]=~ s/\*/%/g if (@$filters[7]);
322
    $strcalc_filter .= " AND dayname(timestamp) like '" . @$filters[7]."'" if (@$filters[7]);
323
    @$filters[8]=~ s/\*/%/g if (@$filters[8]);
324
    $strcalc_filter .= " AND monthname(timestamp) like '" . @$filters[8]."'" if (@$filters[8]);
325
    @$filters[9]=~ s/\*/%/g if (@$filters[9]);
326
    $strcalc_filter .= " AND year(timestamp) like '" . @$filters[9] ."'" if ( @$filters[9] );
306
# Processing average loanperiods
327
# Processing average loanperiods
307
    $strcalc .= "SELECT DISTINCT biblio.title, COUNT(biblio.biblionumber) AS RANK, biblio.biblionumber AS ID";
328
    $strcalc .= "SELECT DISTINCT biblio.title, COUNT(biblio.biblionumber) AS RANK, biblio.biblionumber AS ID";
308
    $strcalc .= " , $colfield " if ($colfield);
329
    $strcalc .= " , $colfield " if ($colfield);
309
    $strcalc .= " FROM `old_issues` 
330
    $strcalc .= " FROM ( SELECT * FROM `old_issues` WHERE 1 $strcalc_filter ";
310
                  LEFT JOIN items USING(itemnumber) 
331
    $strcalc .= " UNION ALL ";
332
    $strcalc .= " SELECT * FROM `issues` WHERE 1 $strcalc_filter ) AS circulated";
333
    $strcalc .= " LEFT JOIN items USING(itemnumber) 
311
                  LEFT JOIN biblio USING(biblionumber) 
334
                  LEFT JOIN biblio USING(biblionumber) 
312
                  LEFT JOIN biblioitems USING(biblionumber)
335
                  LEFT JOIN biblioitems USING(biblionumber)
313
                  LEFT JOIN borrowers USING(borrowernumber)
336
                  LEFT JOIN borrowers USING(borrowernumber)
314
                  WHERE 1";
337
                  WHERE 1";
315
338
316
    @$filters[0]=~ s/\*/%/g if (@$filters[0]);
317
    $strcalc .= " AND old_issues.issuedate > '" . @$filters[0] ."'" if ( @$filters[0] );
318
    @$filters[1]=~ s/\*/%/g if (@$filters[1]);
319
    $strcalc .= " AND old_issues.issuedate < '" . @$filters[1] ."'" if ( @$filters[1] );
320
    @$filters[2]=~ s/\*/%/g if (@$filters[2]);
321
    $strcalc .= " AND old_issues.returndate > '" . @$filters[2] ."'" if ( @$filters[2] );
322
    @$filters[3]=~ s/\*/%/g if (@$filters[3]);
323
    $strcalc .= " AND old_issues.returndate < '" . @$filters[3] ."'" if ( @$filters[3] );
324
    @$filters[4]=~ s/\*/%/g if (@$filters[4]);
325
    $strcalc .= " AND old_issues.branchcode like '" . @$filters[4] ."'" if ( @$filters[4] );
326
    @$filters[5]=~ s/\*/%/g if (@$filters[5]);
339
    @$filters[5]=~ s/\*/%/g if (@$filters[5]);
327
    if ( @$filters[5] ){
340
    if ( @$filters[5] ){
328
        if(C4::Context->preference('item-level_itypes') ){
341
        if(C4::Context->preference('item-level_itypes') ){
Lines 334-345 sub calculate { Link Here
334
    }
347
    }
335
    @$filters[6]=~ s/\*/%/g if (@$filters[6]);
348
    @$filters[6]=~ s/\*/%/g if (@$filters[6]);
336
    $strcalc .= " AND borrowers.categorycode like '" . @$filters[6] ."'" if ( @$filters[6] );
349
    $strcalc .= " AND borrowers.categorycode like '" . @$filters[6] ."'" if ( @$filters[6] );
337
    @$filters[7]=~ s/\*/%/g if (@$filters[7]);
338
    $strcalc .= " AND dayname(old_issues.timestamp) like '" . @$filters[7]."'" if (@$filters[7]);
339
    @$filters[8]=~ s/\*/%/g if (@$filters[8]);
340
    $strcalc .= " AND monthname(old_issues.timestamp) like '" . @$filters[8]."'" if (@$filters[8]);
341
    @$filters[9]=~ s/\*/%/g if (@$filters[9]);
342
    $strcalc .= " AND year(old_issues.timestamp) like '" . @$filters[9] ."'" if ( @$filters[9] );
343
    
350
    
344
    $strcalc .= " group by biblio.biblionumber";
351
    $strcalc .= " group by biblio.biblionumber";
345
    $strcalc .= ", $colfield" if ($column);
352
    $strcalc .= ", $colfield" if ($column);

Return to bug 10188