- my $colfield;
- my $colorder;
- if ((($column =~/timestamp/) and ($podsp == 1)) or (($column =~/returndate/) and ($rodsp == 1))) {
- #Display by day
- $colfield .="dayname($column)";
- $colorder .="weekday($column)";
- } elsif ((($column =~/timestamp/) and ($podsp == 2)) or (($column =~/returndate/) and ($rodsp == 2))) {
- #Display by Month
- $colfield .="monthname($column)";
- $colorder .="month($column)";
- } elsif ((($column =~/timestamp/) and ($podsp == 3)) or (($column =~/returndate/) and ($rodsp == 3))) {
- #Display by Year
- $colfield .="Year($column)";
- $colorder .= $column;
- } elsif (($column=~/timestamp/) or ($column=~/returndate/)){
- $colfield .= 'date_format( '."'".$column."'". ', "%Y-%m-%d")';
- $colorder .= $column;
- } else {
- $colfield .= $column;
- $colorder .= $column;
- }
-
- my $strsth2;
- $strsth2 .= "select distinctrow $colfield FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=issues.branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=borrowers.categorycode) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber and 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]' " ;
- } elsif ($colfilter[1]) {
- $strsth2 .= " and $column < '$colfilter[1]' " ;
- } elsif ($colfilter[0]) {
- $strsth2 .= " and $column > '$colfilter[0]' " ;
- }
- if ($colfilter[2]){
- $strsth2 .= " and dayname($column) = '$colfilter[2]' " ;
- }
- if ($colfilter[3]){
- $strsth2 .= " and monthname($column) = '$colfilter[3]' " ;
- }
- } elsif ($colfilter[0]) {
- $colfilter[0] =~ s/\*/%/g;
- $strsth2 .= " and $column LIKE '$colfilter[0]' " ;
- }
- $strsth2 .=" group by $colfield";
- $strsth2 .=" order by $colorder";
- warn "". $strsth2;
-
- my $sth2 = $dbh->prepare( $strsth2 );
- if (( @colfilter ) and ($colfilter[1])){
- $sth2->execute("'".$colfilter[0]."'","'".$colfilter[1]."'");
- } elsif ($colfilter[0]) {
- $sth2->execute($colfilter[0]);
- } else {
- $sth2->execute;
- }
-
+ my $colfield;
+ my $colorder;
+ if ((($column =~/timestamp/) and ($podsp == 1)) or (($column =~/returndate/) and ($rodsp == 1))) {
+ #Display by day
+ $colfield .="dayname($column)";
+ $colorder .="weekday($column)";
+ } elsif ((($column =~/timestamp/) and ($podsp == 2)) or (($column =~/returndate/) and ($rodsp == 2))) {
+ #Display by Month
+ $colfield .="monthname($column)";
+ $colorder .="month($column)";
+ } elsif ((($column =~/timestamp/) and ($podsp == 3)) or (($column =~/returndate/) and ($rodsp == 3))) {
+ #Display by Year
+ $colfield .="Year($column)";
+ $colorder .= $column;
+ } elsif (($column=~/timestamp/) or ($column=~/returndate/)){
+ $colfield .= 'date_format( '."'".$column."'". ', "%Y-%m-%d")';
+ $colorder .= $column;
+ } else {
+ $colfield .= $column;
+ $colorder .= $column;
+ }
+
+ my $strsth2;
+ $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 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]' " ;
+ }
+ if ($colfilter[2]){
+ $strsth2 .= " AND dayname($column) = '$colfilter[2]' " ;
+ }
+ if ($colfilter[3]){
+ $strsth2 .= " AND monthname($column) = '$colfilter[3]' " ;
+ }
+ } elsif ($colfilter[0]) {
+ $colfilter[0] =~ s/\*/%/g;
+ $strsth2 .= " AND $column LIKE '$colfilter[0]' " ;
+ }
+ $strsth2 .=" GROUP BY $colfield";
+ $strsth2 .=" ORDER BY $colorder";
+ warn "". $strsth2;
+
+ my $sth2 = $dbh->prepare( $strsth2 );
+ if (( @colfilter ) and ($colfilter[1])){
+ $sth2->execute("'".$colfilter[0]."'","'".$colfilter[1]."'");
+ } elsif ($colfilter[0]) {
+ $sth2->execute($colfilter[0]);
+ } else {
+ $sth2->execute;
+ }
+