|
Lines 5-11
ALTER TABLE accountlines ADD issue_id INT(11) NULL DEFAULT NULL AFTER accountlin
Link Here
|
| 5 |
UPDATE accountlines LEFT JOIN issues USING ( itemnumber, borrowernumber ) SET accounttype = 'F' WHERE accounttype = 'FU' and issues.issue_id IS NULL; |
5 |
UPDATE accountlines LEFT JOIN issues USING ( itemnumber, borrowernumber ) SET accounttype = 'F' WHERE accounttype = 'FU' and issues.issue_id IS NULL; |
| 6 |
|
6 |
|
| 7 |
-- Close out any extra not really accruing fines, keep only the latest accring fine |
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 ); |
8 |
UPDATE accountlines a1 |
|
|
9 |
LEFT JOIN (SELECT MAX(accountlines_id) AS keeper, |
| 10 |
borrowernumber, |
| 11 |
itemnumber |
| 12 |
FROM accountlines |
| 13 |
WHERE accounttype = 'FU' |
| 14 |
GROUP BY borrowernumber, itemnumber |
| 15 |
) a2 USING ( borrowernumber, itemnumber ) |
| 16 |
SET a1.accounttype = 'F' |
| 17 |
WHERE a1.accounttype = 'FU' |
| 18 |
AND a1.accountlines_id != a2.keeper; |
| 9 |
|
19 |
|
| 10 |
-- Update the unclosed fines to add the current issue_id to them |
20 |
-- 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'; |
21 |
UPDATE accountlines LEFT JOIN issues USING ( itemnumber ) SET accountlines.issue_id = issues.issue_id WHERE accounttype = 'FU'; |
| 12 |
- |
|
|