| 
      
            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';  |