- my $strcalc ;
- if ($process ==2) {
- # Processing average loanperiods
- $strcalc .= "SELECT $linefield, $colfield, ";
- $strcalc .= " DATE_SUB(date_due, INTERVAL CAST(issues.renewals AS SIGNED INTEGER) * (CAST(issues.renewals AS SIGNED INTEGER)+1) DAY) AS issuedate, returndate, COUNT(*), date_due, issues.renewals, issuelength 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 ";
-
- @$filters[0]=~ s/\*/%/g if (@$filters[0]);
- $strcalc .= " AND issues.returndate > '" . @$filters[0] ."'" if ( @$filters[0] );
- @$filters[1]=~ s/\*/%/g if (@$filters[1]);
- $strcalc .= " AND issues.returndate < '" . @$filters[1] ."'" if ( @$filters[1] );
- @$filters[2]=~ s/\*/%/g if (@$filters[2]);
- $strcalc .= " AND borrowers.categorycode like '" . @$filters[2] ."'" if ( @$filters[2] );
- @$filters[3]=~ s/\*/%/g if (@$filters[3]);
- $strcalc .= " AND biblioitems.itemtype like '" . @$filters[3] ."'" if ( @$filters[3] );
- @$filters[4]=~ s/\*/%/g if (@$filters[4]);
- $strcalc .= " AND issues.branchcode like '" . @$filters[4] ."'" if ( @$filters[4] );
- @$filters[5]=~ s/\*/%/g if (@$filters[5]);
- $strcalc .= " AND borrowers.sort1 like '" . @$filters[5] ."'" if ( @$filters[5] );
- @$filters[6]=~ s/\*/%/g if (@$filters[6]);
- $strcalc .= " AND borrowers.sort2 like '" . @$filters[6] ."'" if ( @$filters[6] );
- $strcalc .= " AND dayname(timestamp) like '" . $daysel ."'" if ( $daysel );
- $strcalc .= " AND monthname(timestamp) like '" . $monthsel ."'" if ( $monthsel );
-
- $strcalc .= " group by issuedate, returndate, $linefield, $colfield order by $linefield,$colfield";
-
- my $dbcalc = $dbh->prepare($strcalc);
- $dbcalc->execute;
- # warn "filling table";
- my $emptycol;
- my $issues_count;
- while (my ($row, $col, $issuedate, $returndate) = $dbcalc->fetchrow) {
- # warn "filling table $row / $col / $value ";
- $emptycol = 1 if ($col eq undef);
- $col = "zzEMPTY" if ($col eq undef);
- $row = "zzEMPTY" if ($row eq undef);
-
- $table{$row}->{$col}+=$value;
- $table{$row}->{totalrow}+=$value;
- $grantotal += $value;
- }
+ my $strcalc = "SELECT $linefield, $colfield, ";
+ $strcalc .= ($process == 1) ? " COUNT(*) " :
+ ($process == 2) ? "(COUNT(DISTINCT borrowers.borrowernumber))" :
+ ($process == 3) ? "(COUNT(DISTINCT statistics.itemnumber))" : '';
+ if ($process == 4) {
+ my $rqbookcount = $dbh->prepare("SELECT count(*) FROM items");
+ $rqbookcount->execute;
+ my ($bookcount) = $rqbookcount->fetchrow;
+ $strcalc .= "100*(COUNT(DISTINCT statistics.itemnumber))/ $bookcount " ;