Lines 174-190
sub calculate {
Link Here
|
174 |
$colorder .= $column; |
174 |
$colorder .= $column; |
175 |
|
175 |
|
176 |
my $strsth2; |
176 |
my $strsth2; |
177 |
$strsth2 .= "select distinct $colfield FROM borrowers WHERE 1"; |
177 |
$strsth2 .= "select distinct " . $dbh->quote($colfield) . " FROM borrowers WHERE 1"; |
178 |
if ($colfilter[0]) { |
178 |
my @query_args; |
|
|
179 |
if ( $colfilter[0] ) { |
179 |
$colfilter[0] =~ s/\*/%/g; |
180 |
$colfilter[0] =~ s/\*/%/g; |
180 |
$strsth2 .= " and $column LIKE '$colfilter[0]' " ; |
181 |
$strsth2 .= " and " . $dbh->quote($column) . "LIKE ?" ; |
|
|
182 |
push @query_args, $colfilter[0]; |
181 |
} |
183 |
} |
182 |
$strsth2 .=" group by $colfield"; |
184 |
$strsth2 .=" group by " . $dbh->quote($colfield); |
183 |
$strsth2 .=" order by $colorder"; |
185 |
$strsth2 .=" order by " . $dbh->quote($colorder); |
184 |
# warn "". $strsth2; |
186 |
# warn "". $strsth2; |
185 |
|
187 |
|
186 |
my $sth2 = $dbh->prepare( $strsth2 ); |
188 |
my $sth2 = $dbh->prepare( $strsth2 ); |
187 |
$sth2->execute; |
189 |
$sth2->execute( @query_args ); |
188 |
while (my ($celvalue) = $sth2->fetchrow) { |
190 |
while (my ($celvalue) = $sth2->fetchrow) { |
189 |
my %cell; |
191 |
my %cell; |
190 |
# my %ft; |
192 |
# my %ft; |
Lines 217-237
sub calculate {
Link Here
|
217 |
|
219 |
|
218 |
# Processing calculation |
220 |
# Processing calculation |
219 |
$strcalc .= "SELECT CONCAT( borrowers.surname , \"\\t\",borrowers.firstname, \"\\t\", borrowers.cardnumber)"; |
221 |
$strcalc .= "SELECT CONCAT( borrowers.surname , \"\\t\",borrowers.firstname, \"\\t\", borrowers.cardnumber)"; |
220 |
$strcalc .= " , $colfield " if ($colfield); |
222 |
$strcalc .= " , " . $dbh->quote($colfield) if ($colfield); |
221 |
$strcalc .= " FROM borrowers "; |
223 |
$strcalc .= " FROM borrowers "; |
222 |
$strcalc .= "WHERE 1 "; |
224 |
$strcalc .= "WHERE 1 "; |
223 |
@$filters[0]=~ s/\*/%/g if (@$filters[0]); |
225 |
my @query_args; |
224 |
$strcalc .= " AND borrowers.categorycode like '" . @$filters[0] ."'" if ( @$filters[0] ); |
226 |
if ( @$filters[0] ) { |
225 |
|
227 |
@$filters[0]=~ s/\*/%/g; |
|
|
228 |
$strcalc .= " AND borrowers.categorycode like ?"; |
229 |
push @query_args, @$filters[0]; |
230 |
} |
226 |
$strcalc .= " AND NOT EXISTS (SELECT * FROM issues WHERE issues.borrowernumber=borrowers.borrowernumber "; |
231 |
$strcalc .= " AND NOT EXISTS (SELECT * FROM issues WHERE issues.borrowernumber=borrowers.borrowernumber "; |
227 |
$strcalc .= " AND issues.timestamp> '" . @$filters[1] . "'" if (@$filters[1]); |
232 |
if ( @$filters[1] ) { |
|
|
233 |
$strcalc .= " AND issues.timestamap > ?"; |
234 |
push @query_args, @$filters[1]; |
235 |
} |
228 |
$strcalc .= ") "; |
236 |
$strcalc .= ") "; |
229 |
$strcalc .= " AND NOT EXISTS (SELECT * FROM old_issues WHERE old_issues.borrowernumber=borrowers.borrowernumber "; |
237 |
$strcalc .= " AND NOT EXISTS (SELECT * FROM old_issues WHERE old_issues.borrowernumber=borrowers.borrowernumber "; |
230 |
$strcalc .= " AND old_issues.timestamp> '" . @$filters[1] . "'" if (@$filters[1]); |
238 |
if ( @$filters[1] ) { |
|
|
239 |
$strcalc .= " AND old_issues.timestamp > ?"; |
240 |
push @query_args, @$filters[1]; |
241 |
} |
231 |
$strcalc .= ") "; |
242 |
$strcalc .= ") "; |
232 |
$strcalc .= " group by borrowers.borrowernumber"; |
243 |
$strcalc .= " group by borrowers.borrowernumber"; |
233 |
$strcalc .= ", $colfield" if ($column); |
244 |
$strcalc .= ", " . $dbh->quote($colfield) if ($column); |
234 |
$strcalc .= " order by $colfield " if ($colfield); |
245 |
$strcalc .= " order by " . $dbh->quote($colfield) if ($colfield); |
235 |
my $max; |
246 |
my $max; |
236 |
if ($line) { |
247 |
if ($line) { |
237 |
if (@loopcol) { |
248 |
if (@loopcol) { |
Lines 241-247
sub calculate {
Link Here
|
241 |
} |
252 |
} |
242 |
|
253 |
|
243 |
my $dbcalc = $dbh->prepare($strcalc); |
254 |
my $dbcalc = $dbh->prepare($strcalc); |
244 |
$dbcalc->execute; |
255 |
$dbcalc->execute( @query_args ); |
245 |
# warn "filling table"; |
256 |
# warn "filling table"; |
246 |
my $previous_col; |
257 |
my $previous_col; |
247 |
$i=1; |
258 |
$i=1; |
248 |
- |
|
|