From ad767716dc19e37013b9870fc96a94a3961aa505 Mon Sep 17 00:00:00 2001 From: Henri-Damien LAURENT Date: Wed, 19 Mar 2008 10:11:09 -0500 Subject: [PATCH] Bug Fix : 1898 Replaces the previous commit : More fixes. A) Fixing queries which were mysql old style B) Fixing output. + Fixing queries the same way for bor_issues_top catalogue_out and issues_avg_stats Signed-off-by: Galen Charlton Signed-off-by: Joshua Ferraro --- .../en/modules/reports/cat_issues_top.tmpl | 11 +++++----- reports/bor_issues_top.pl | 13 +++++++++-- reports/cat_issues_top.pl | 16 ++++++++++++-- reports/catalogue_out.pl | 6 ++++- reports/issues_avg_stats.pl | 22 +++++++++++++++++-- 5 files changed, 56 insertions(+), 12 deletions(-) diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/cat_issues_top.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/cat_issues_top.tmpl index b95851f6dc..568f286208 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/cat_issues_top.tmpl +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/cat_issues_top.tmpl @@ -38,17 +38,18 @@ function Dopop(link) { - - +   Item Count of Checkouts - + - + + + @@ -266,4 +267,4 @@ function Dopop(link) { - \ No newline at end of file + diff --git a/reports/bor_issues_top.pl b/reports/bor_issues_top.pl index f27a0db3bf..d4b9cbf8fd 100755 --- a/reports/bor_issues_top.pl +++ b/reports/bor_issues_top.pl @@ -268,7 +268,12 @@ sub calculate { } my $strsth2; - $strsth2 .= "select distinctrow $colfield FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber and returndate is not null"; + $strsth2 .= "SELECT DISTINCTROW $colfield + FROM `issues` + LEFT JOIN borrowers ON issues.borrowernumber=borrowers.borrowernumber + LEFT JOIN items ON issues.itemnumber=items.itemnumber + LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber) + WHERE returndate is not null"; if (($column=~/timestamp/) or ($column=~/returndate/)){ if ($colfilter[1] and ($colfilter[0])){ $strsth2 .= " and $column between '$colfilter[0]' and '$colfilter[1]' " ; @@ -318,7 +323,11 @@ sub calculate { # Processing average loanperiods $strcalc .= "SELECT CONCAT(borrowers.surname , \"\\t\",borrowers.firstname), COUNT(*) AS RANK, borrowers.borrowernumber AS ID"; $strcalc .= " , $colfield " if ($colfield); - $strcalc .= " FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber and returndate is not null"; + $strcalc .= " FROM `issues` + LEFT JOIN borrowers ON borrowers.borrowernumber + LEFT JOIN items ON items.itemnumber=issues.itemnumber + LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber) + WHERE returndate is not null"; @$filters[0]=~ s/\*/%/g if (@$filters[0]); $strcalc .= " AND issues.timestamp > '" . @$filters[0] ."'" if ( @$filters[0] ); diff --git a/reports/cat_issues_top.pl b/reports/cat_issues_top.pl index 9d89bfb2c2..3235bbe348 100755 --- a/reports/cat_issues_top.pl +++ b/reports/cat_issues_top.pl @@ -268,7 +268,12 @@ sub calculate { } my $strsth2; - $strsth2 .= "select distinctrow $colfield FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber and returndate is not null"; + $strsth2 .= "SELECT distinctrow $colfield + FROM `issues` + LEFT JOIN borrowers ON borrowers.borrowernumber=issues.borrowernumber + LEFT JOIN items ON issues.itemnumber=items.itemnumber + LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber + WHERE returndate is not null"; if (($column=~/timestamp/) or ($column=~/returndate/)){ if ($colfilter[1] and ($colfilter[0])){ $strsth2 .= " and $column between '$colfilter[0]' and '$colfilter[1]' " ; @@ -325,7 +330,13 @@ sub calculate { # Processing average loanperiods $strcalc .= "SELECT DISTINCT biblio.title, COUNT(biblio.biblionumber) AS RANK, biblio.biblionumber AS ID"; $strcalc .= " , $colfield " if ($colfield); - $strcalc .= " FROM `issues`,borrowers,(items LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber and returndate is not null"; + $strcalc .= " FROM `issues` + LEFT JOIN borrowers ON issues.borrowernumber=borrowers.borrowernumber + LEFT JOIN (items + LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber) + ON items.itemnumber=issues.itemnumber + LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber) + WHERE returndate is not null"; @$filters[0]=~ s/\*/%/g if (@$filters[0]); $strcalc .= " AND issues.timestamp > '" . @$filters[0] ."'" if ( @$filters[0] ); @@ -352,6 +363,7 @@ sub calculate { $strcalc .= ", $colfield" if ($column); $strcalc .= " order by RANK DESC"; $strcalc .= ", $colfield " if ($colfield); + # my $max; # if (@loopcol) { # $max = $line*@loopcol; diff --git a/reports/catalogue_out.pl b/reports/catalogue_out.pl index e1d48148dd..4424aed88c 100755 --- a/reports/catalogue_out.pl +++ b/reports/catalogue_out.pl @@ -256,7 +256,11 @@ sub calculate { # Processing average loanperiods $strcalc .= "SELECT items.barcode, biblio.title, biblio.biblionumber, biblio.author"; $strcalc .= " , $colfield " if ($colfield); - $strcalc .= " FROM (items LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber LEFT JOIN biblio ON biblio.biblionumber=items.biblionumber) LEFT JOIN issues ON issues.itemnumber=items.itemnumber WHERE issues.itemnumber is null"; + $strcalc .= " FROM (items + LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber + LEFT JOIN biblio ON biblio.biblionumber=items.biblionumber) + LEFT JOIN issues ON issues.itemnumber=items.itemnumber + WHERE issues.itemnumber is null"; @$filters[0]=~ s/\*/%/g if (@$filters[0]); $strcalc .= " AND items.homebranch like '" . @$filters[0] ."'" if ( @$filters[0] ); @$filters[1]=~ s/\*/%/g if (@$filters[1]); diff --git a/reports/issues_avg_stats.pl b/reports/issues_avg_stats.pl index 8d573f1de5..71e0b3064d 100755 --- a/reports/issues_avg_stats.pl +++ b/reports/issues_avg_stats.pl @@ -354,7 +354,16 @@ sub calculate { } my $strsth; - $strsth .= "select distinctrow $linefield FROM `old_issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=categorycode) WHERE old_issues.itemnumber=items.itemnumber AND old_issues.borrowernumber=borrowers.borrowernumber"; + $strsth .= "select distinctrow $linefield + 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) + LEFT JOIN issuingrules ON + (issuingrules.branchcode=old_issues.branchcode + AND issuingrules.itemtype=biblioitems.itemtype + AND issuingrules.categorycode=borrowers.categorycode) + WHERE returndate is not null"; if (($line=~/timestamp/) or ($line=~/returndate/)){ if ($linefilter[1] and ($linefilter[0])){ @@ -416,7 +425,16 @@ sub calculate { } my $strsth2; - $strsth2 .= "select distinctrow $colfield FROM `old_issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=categorycode) WHERE old_issues.itemnumber=items.itemnumber AND old_issues.borrowernumber=borrowers.borrowernumber"; + $strsth2 .= "SELECT distinctrow $colfield + FROM `old_issues` + LEFT JOIN borrowers ON borrowers.borrowernumber=old_issues.borrowernumber + LEFT JOIN items ON items.itemnumber=old_issues.itemnumber + LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber) + LEFT JOIN issuingrules ON + (issuingrules.branchcode=old_issues.branchcode + AND issuingrules.itemtype=biblioitems.itemtype + AND issuingrules.categorycode=borrowers.categorycode) + WHERE returndate is not null"; if (($column=~/timestamp/) or ($column=~/returndate/)){ if ($colfilter[1] and ($colfilter[0])){ -- 2.20.1