View | Details | Raw Unified | Return to bug 15675
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_15334.sql (-2 / +11 lines)
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
- 

Return to bug 15675