|
Line 1
Link Here
|
|
|
1 |
-- Add issue_id to accountlines table |
| 1 |
ALTER TABLE accountlines ADD issue_id INT(11) NULL DEFAULT NULL AFTER accountlines_id; |
2 |
ALTER TABLE accountlines ADD issue_id INT(11) NULL DEFAULT NULL AFTER accountlines_id; |
| 2 |
- |
3 |
|
|
|
4 |
-- Close out any accruing fines with no current issue |
| 5 |
UPDATE accountlines LEFT JOIN issues USING ( itemnumber, borrowernumber ) SET accounttype = 'F' WHERE accounttype = 'FU' and issues.issue_id IS NULL; |
| 6 |
|
| 7 |
-- Close out any extra not really accruing fines, keep only the latest accring fine |
| 8 |
UPDATE accountlines SET accounttype = 'F' WHERE accountlines_id NOT IN ( SELECT accountlines_id FROM ( SELECT * FROM accountlines WHERE accounttype = 'FU' ORDER BY accountlines_id DESC ) a2 GROUP BY borrowernumber, itemnumber ); |
| 9 |
|
| 10 |
-- Update the unclosed fines to add the current issue_id to them |
| 11 |
UPDATE accountlines LEFT JOIN issues USING ( itemnumber ) SET accountlines.issue_id = issues.issue_id WHERE accounttype = 'FU'; |