JL9LytJ0

1class MigrateRolesFromUsersToContacts < ActiveRecord::Migration
2  def self.up
3    execute "CREATE TEMPORARY TABLE IF NOT EXISTS contacts_with_only_inactive_users (id integer)"
4    execute "TRUNCATE contacts_with_only_inactive_users"
5    
6    execute <<-SQL
7      INSERT INTO contacts_with_only_inactive_users 
8        SELECT distinct c.id 
9        FROM contacts c INNER JOIN users u ON c.id = u.contact_id 
10        WHERE NOT EXISTS (SELECT 1 FROM users WHERE contact_id = c.id AND active = 1) 
11          AND EXISTS (SELECT 1 FROM users WHERE contact_id = c.id AND active = 0)
12    SQL
13
14    roles_in_order = [
15                      'fsp_admin', 
16                      'ove_admin', 
17                      'ove_support', 
18                      'ove_customer_relations', 
19                      'ove_employee',
20                      'national_account_corp',
21                      'national_account',
22                      'fsp_employee', 
23                      'fleet_manager',
24                      'employee_sales_fsp_manager',
25                      'dealer'
26                     ]
27                         
28    #  Loop through the roles in order of increasing power so the 
29    #  contact ends up with the most powerful role of it's users.   
30                          
31    roles_in_order.reverse.each do |role|  
32      #  The first update only users the roles of the contact's active users.
33      execute <<-SQL
34       UPDATE contacts c INNER JOIN users u ON u.contact_id = c.id 
35       SET c.role = u.role
36       WHERE u.role = '#{role}'
37       AND u.active = 1
38      SQL
39
40      #  The second update only applies to users who have only inactive contacts.
41      execute <<-SQL
42       UPDATE contacts c 
43       INNER JOIN users u ON u.contact_id = c.id 
44       INNER JOIN contacts_with_only_inactive_users ciu ON u.contact_id = ciu.id
45       SET c.role = u.role
46       WHERE u.role = '#{role}'
47      SQL
48    end
49    
50    drop_table :contacts_with_only_inactive_users
51
52  end