|
Lines 256-265
SELECT borrowernumber,
Link Here
|
| 256 |
city, |
256 |
city, |
| 257 |
phone, |
257 |
phone, |
| 258 |
dateofbirth, |
258 |
dateofbirth, |
| 259 |
sum( accountlines.amountoutstanding ) as total_fines |
259 |
account_balance as total_fines |
| 260 |
FROM borrowers |
260 |
FROM borrowers |
| 261 |
LEFT JOIN accountlines USING (borrowernumber) |
|
|
| 262 |
GROUP BY borrowernumber; |
| 263 |
END_SQL |
261 |
END_SQL |
| 264 |
|
262 |
|
| 265 |
my $fields_count = $sth_mysql->execute(); |
263 |
my $fields_count = $sth_mysql->execute(); |
|
Lines 278-320
END_SQL
Link Here
|
| 278 |
} |
276 |
} |
| 279 |
$dbh_sqlite->commit(); |
277 |
$dbh_sqlite->commit(); |
| 280 |
print "inserted $count borrowers\n" if $verbose; |
278 |
print "inserted $count borrowers\n" if $verbose; |
| 281 |
# add_fines_to_borrowers_table(); |
|
|
| 282 |
} |
| 283 |
|
| 284 |
=head2 add_fines_to_borrowers_table |
| 285 |
|
| 286 |
Import the fines from koha.accountlines into the sqlite db |
| 287 |
|
| 288 |
=cut |
| 289 |
|
| 290 |
sub add_fines_to_borrowers_table { |
| 291 |
|
| 292 |
print "preparing to update borrowers\n" if $verbose; |
| 293 |
my $sth_mysql = $dbh_mysql->prepare( |
| 294 |
"SELECT DISTINCT borrowernumber, SUM( amountoutstanding ) AS total_fines |
| 295 |
FROM accountlines |
| 296 |
GROUP BY borrowernumber" |
| 297 |
); |
| 298 |
$sth_mysql->execute(); |
| 299 |
my $count; |
| 300 |
while ( my $result = $sth_mysql->fetchrow_hashref() ) { |
| 301 |
$count++; |
| 302 |
if ( $verbose ) { |
| 303 |
print '.' unless ( $count % 10 ); |
| 304 |
print "$count\n" unless ( $count % 1000 ); |
| 305 |
} |
| 306 |
|
| 307 |
my $borrowernumber = $result->{'borrowernumber'}; |
| 308 |
my $total_fines = $result->{'total_fines'}; |
| 309 |
|
| 310 |
# warn "Fines for Borrower # $borrowernumber are \$ $total_fines \n" if $verbose; |
| 311 |
my $sql = "UPDATE borrowers SET total_fines = ? WHERE borrowernumber = ?"; |
| 312 |
|
| 313 |
my $sth_sqlite = $dbh_sqlite->prepare($sql); |
| 314 |
$sth_sqlite->execute( $total_fines, $borrowernumber ); |
| 315 |
$sth_sqlite->finish(); |
| 316 |
} |
| 317 |
print "updated $count borrowers\n" if ( $verbose && $count ); |
| 318 |
} |
279 |
} |
| 319 |
|
280 |
|
| 320 |
=head2 create_issue_table |
281 |
=head2 create_issue_table |