+
+ # loop cols.
+ if ($column eq "Day") {
+ #Display by day
+ $column = "old_issues.timestamp";
+ $colfield .="dayname($column)";
+ $colorder .="weekday($column)";
+ } elsif ($column eq "Month") {
+ #Display by Month
+ $column = "old_issues.timestamp";
+ $colfield .="monthname($column)";
+ $colorder .="month($column)";
+ } elsif ($column eq "Year") {
+ #Display by Year
+ $column = "old_issues.timestamp";
+ $colfield .="Year($column)";
+ $colorder .= $column;
+ } else {
+ $colfield .= $column;
+ $colorder .= $column;
+ }
+
+ my $strsth2;
+ $strsth2 .= "SELECT DISTINCTROW $colfield
+ FROM `old_issues`
+ LEFT JOIN borrowers ON old_issues.borrowernumber=borrowers.borrowernumber
+ LEFT JOIN items ON old_issues.itemnumber=items.itemnumber
+ LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber)
+ WHERE 1";
+ if (($column=~/timestamp/) or ($column=~/returndate/)){
+ if ($colfilter[1] and $colfilter[0]){
+ $strsth2 .= " AND $column between '$colfilter[0]' AND '$colfilter[1]' " ;
+ } elsif ($colfilter[1]) {
+ $strsth2 .= " AND $column < '$colfilter[1]' " ;
+ } elsif ($colfilter[0]) {
+ $strsth2 .= " AND $column > '$colfilter[0]' " ;
+ }
+ } elsif ($colfilter[0]) {
+ $colfilter[0] =~ s/\*/%/g;
+ $strsth2 .= " AND $column LIKE '$colfilter[0]' " ;
+ }
+ $strsth2 .=" GROUP BY $colfield";
+ $strsth2 .=" ORDER BY $colorder";
+
+ $debug and print DEBUG "bor_issues_top (old_issues) SQL: $strsth2\n";
+ my $sth2 = $dbh->prepare($strsth2);
+ $sth2->execute;
+ print DEBUG "rows: ", $sth2->rows, "\n";
+ while (my @row = $sth2->fetchrow) {
+ $columns{($row[0] ||'NULL')}++;
+ push @loopcol, { coltitle => $row[0] || 'NULL' };
+ }
+
+ $strsth2 =~ s/old_issues/issues/g;
+ $debug and print DEBUG "bor_issues_top (issues) SQL: $strsth2\n";
+ $sth2 = $dbh->prepare($strsth2);
+ $sth2->execute;
+ $debug and print DEBUG "rows: ", $sth2->rows, "\n";
+ while (my @row = $sth2->fetchrow) {
+ $columns{($row[0] ||'NULL')}++;
+ push @loopcol, { coltitle => $row[0] || 'NULL' };
+ }
+ $debug and print DEBUG "full array: ", Dumper(\%columns), "\n";
+ }else{
+ $columns{''} = 1;
+ }
+
+ my $strcalc ;
+
+# Processing average loanperiods
+ $strcalc .= "SELECT CONCAT(borrowers.surname , \",\\t\",borrowers.firstname), COUNT(*) AS RANK, borrowers.borrowernumber AS ID";
+ $strcalc .= " , $colfield " if ($colfield);
+ $strcalc .= " FROM `old_issues`
+ LEFT JOIN borrowers USING(borrowernumber)
+ LEFT JOIN items USING(itemnumber)
+ LEFT JOIN biblioitems USING(biblioitemnumber)
+ WHERE old_issues.borrowernumber IS NOT NULL
+ ";
+ my @filterterms = (
+ 'old_issues.issuedate >',
+ 'old_issues.issuedate <',
+ 'old_issues.returndate >',
+ 'old_issues.returndate <',
+ 'old_issues.branchcode like',
+ 'biblioitems.itemtype like',
+ 'borrowers.categorycode like',
+ );
+ foreach ((@$filters)[0..9]) {
+ my $term = shift @filterterms; # go through both arrays in step
+ ($_) or next;
+ s/\*/%/g;
+ $strcalc .= " AND $term '$_' ";