Bugzilla – Attachment 29959 Details for
Bug 10188
Top Most-circulated items doesn't count current loans
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
add union
cat_issues_top.pl.2.diff (text/plain), 6.89 KB, created by
Micah Bucy
on 2014-07-22 20:59:57 UTC
(
hide
)
Description:
add union
Filename:
MIME Type:
Creator:
Micah Bucy
Created:
2014-07-22 20:59:57 UTC
Size:
6.89 KB
patch
obsolete
>diff --git a/reports/cat_issues_top.pl b/reports/cat_issues_top.pl >index 3f9ea0e..6309ad1 100755 >--- a/reports/cat_issues_top.pl >+++ b/reports/cat_issues_top.pl >@@ -204,7 +204,7 @@ sub calculate { > my $colfield; > my $colorder; > if ($column){ >- $column = "old_issues.".$column if (($column=~/branchcode/) or ($column=~/timestamp/)); >+ $column = "circulated.".$column if (($column=~/branchcode/) or ($column=~/timestamp/)); > if($column=~/itemtype/){ > $column = C4::Context->preference('item-level_itypes') ? "items.itype": "biblioitems.itemtype"; > } >@@ -226,17 +226,17 @@ sub calculate { > # loop cols. > if ($column eq "Day") { > #Display by day >- $column = "old_issues.timestamp"; >+ $column = "circulated.timestamp"; > $colfield .="dayname($column)"; > $colorder .="weekday($column)"; > } elsif ($column eq "Month") { > #Display by Month >- $column = "old_issues.timestamp"; >+ $column = "circulated.timestamp"; > $colfield .="monthname($column)"; > $colorder .="month($column)"; > } elsif ($column eq "Year") { > #Display by Year >- $column = "old_issues.timestamp"; >+ $column = "circulated.timestamp"; > $colfield .="Year($column)"; > $colorder .= $column; > } else { >@@ -245,24 +245,27 @@ sub calculate { > } > > my $strsth2; >- $strsth2 .= "SELECT distinctrow $colfield >- FROM `old_issues` >- LEFT JOIN borrowers ON borrowers.borrowernumber=old_issues.borrowernumber >- LEFT JOIN items ON old_issues.itemnumber=items.itemnumber >- LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber >- WHERE 1"; >+ my $strsth2_filter; >+ $strsth2_filter = ""; > if (($column=~/issuedate/) or ($column=~/returndate/)){ > if ($colfilter[1] and ($colfilter[0])){ >- $strsth2 .= " and $column between '$colfilter[0]' and '$colfilter[1]' " ; >+ $strsth2_filter .= " and $column between '$colfilter[0]' and '$colfilter[1]' " ; > } elsif ($colfilter[1]) { >- $strsth2 .= " and $column < '$colfilter[1]' " ; >+ $strsth2_filter .= " and $column < '$colfilter[1]' " ; > } elsif ($colfilter[0]) { >- $strsth2 .= " and $column > '$colfilter[0]' " ; >+ $strsth2_filter .= " and $column > '$colfilter[0]' " ; > } > } elsif ($colfilter[0]) { > $colfilter[0] =~ s/\*/%/g; >- $strsth2 .= " and $column LIKE '$colfilter[0]' " ; >+ $strsth2_filter .= " and $column LIKE '$colfilter[0]' " ; > } >+ $strsth2 .= "SELECT distinctrow $colfield >+ FROM ( SELECT * FROM `old_issues` WHERE 1 $strsth2_filter >+ UNION ALL >+ SELECT * FROM `issues` WHERE 1 $strsth2_filter ) AS circulated >+ LEFT JOIN borrowers ON borrowers.borrowernumber=circulated.borrowernumber >+ LEFT JOIN items ON circulated.itemnumber=items.itemnumber >+ LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber"; > $strsth2 .=" group by $colfield"; > $strsth2 .=" order by $colorder"; > >@@ -302,27 +305,37 @@ sub calculate { > > # preparing calculation > my $strcalc ; >+ my $strcalc_filter; > >+ $strcalc_filter = ""; >+ @$filters[0]=~ s/\*/%/g if (@$filters[0]); >+ $strcalc_filter .= " AND issuedate > '" . @$filters[0] ."'" if ( @$filters[0] ); >+ @$filters[1]=~ s/\*/%/g if (@$filters[1]); >+ $strcalc_filter .= " AND issuedate < '" . @$filters[1] ."'" if ( @$filters[1] ); >+ @$filters[2]=~ s/\*/%/g if (@$filters[2]); >+ $strcalc_filter .= " AND returndate > '" . @$filters[2] ."'" if ( @$filters[2] ); >+ @$filters[3]=~ s/\*/%/g if (@$filters[3]); >+ $strcalc_filter .= " AND returndate < '" . @$filters[3] ."'" if ( @$filters[3] ); >+ @$filters[4]=~ s/\*/%/g if (@$filters[4]); >+ $strcalc_filter .= " AND branchcode like '" . @$filters[4] ."'" if ( @$filters[4] ); >+ @$filters[7]=~ s/\*/%/g if (@$filters[7]); >+ $strcalc_filter .= " AND dayname(timestamp) like '" . @$filters[7]."'" if (@$filters[7]); >+ @$filters[8]=~ s/\*/%/g if (@$filters[8]); >+ $strcalc_filter .= " AND monthname(timestamp) like '" . @$filters[8]."'" if (@$filters[8]); >+ @$filters[9]=~ s/\*/%/g if (@$filters[9]); >+ $strcalc_filter .= " AND year(timestamp) like '" . @$filters[9] ."'" if ( @$filters[9] ); > # Processing average loanperiods > $strcalc .= "SELECT DISTINCT biblio.title, COUNT(biblio.biblionumber) AS RANK, biblio.biblionumber AS ID"; > $strcalc .= " , $colfield " if ($colfield); >- $strcalc .= " FROM `old_issues` >- LEFT JOIN items USING(itemnumber) >+ $strcalc .= " FROM ( SELECT * FROM `old_issues` WHERE 1 $strcalc_filter "; >+ $strcalc .= " UNION ALL "; >+ $strcalc .= " SELECT * FROM `issues` WHERE 1 $strcalc_filter ) AS circulated"; >+ $strcalc .= " LEFT JOIN items USING(itemnumber) > LEFT JOIN biblio USING(biblionumber) > LEFT JOIN biblioitems USING(biblionumber) > LEFT JOIN borrowers USING(borrowernumber) > WHERE 1"; > >- @$filters[0]=~ s/\*/%/g if (@$filters[0]); >- $strcalc .= " AND old_issues.issuedate > '" . @$filters[0] ."'" if ( @$filters[0] ); >- @$filters[1]=~ s/\*/%/g if (@$filters[1]); >- $strcalc .= " AND old_issues.issuedate < '" . @$filters[1] ."'" if ( @$filters[1] ); >- @$filters[2]=~ s/\*/%/g if (@$filters[2]); >- $strcalc .= " AND old_issues.returndate > '" . @$filters[2] ."'" if ( @$filters[2] ); >- @$filters[3]=~ s/\*/%/g if (@$filters[3]); >- $strcalc .= " AND old_issues.returndate < '" . @$filters[3] ."'" if ( @$filters[3] ); >- @$filters[4]=~ s/\*/%/g if (@$filters[4]); >- $strcalc .= " AND old_issues.branchcode like '" . @$filters[4] ."'" if ( @$filters[4] ); > @$filters[5]=~ s/\*/%/g if (@$filters[5]); > if ( @$filters[5] ){ > if(C4::Context->preference('item-level_itypes') ){ >@@ -334,12 +347,6 @@ sub calculate { > } > @$filters[6]=~ s/\*/%/g if (@$filters[6]); > $strcalc .= " AND borrowers.categorycode like '" . @$filters[6] ."'" if ( @$filters[6] ); >- @$filters[7]=~ s/\*/%/g if (@$filters[7]); >- $strcalc .= " AND dayname(old_issues.timestamp) like '" . @$filters[7]."'" if (@$filters[7]); >- @$filters[8]=~ s/\*/%/g if (@$filters[8]); >- $strcalc .= " AND monthname(old_issues.timestamp) like '" . @$filters[8]."'" if (@$filters[8]); >- @$filters[9]=~ s/\*/%/g if (@$filters[9]); >- $strcalc .= " AND year(old_issues.timestamp) like '" . @$filters[9] ."'" if ( @$filters[9] ); > > $strcalc .= " group by biblio.biblionumber"; > $strcalc .= ", $colfield" if ($column);
<b>You cannot view the attachment while viewing its details because your browser does not support IFRAMEs. <a href="attachment.cgi?id=29959">View the attachment on a separate page</a>.</b>
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 10188
: 29959