MrbQYa5c

1def self.up
2    execute "CREATE TEMPORARY TABLE IF NOT EXISTS contacts_with_inactive_users (id integer)"
3    execute "TRUNCATE contacts_with_inactive_users"
4    
5    execute <<-SQL
6      INSERT INTO contacts_with_inactive_users 
7        SELECT distinct c.id 
8        FROM contacts c INNER JOIN users u ON c.id = u.contact_id 
9        WHERE NOT EXISTS (SELECT 1 FROM users WHERE contact_id = c.id AND active = 1) 
10          AND EXISTS (SELECT 1 FROM users WHERE contact_id = c.id AND active = 0)
11    SQL
12
13    roles_in_order = [
14                      'fsp_admin', 
15                      'ove_admin', 
16                      'ove_support', 
17                      'ove_customer_relations', 
18                      'ove_employee',
19                      'national_account_corp',
20                      'national_account',
21                      'fsp_employee', 
22                      'fleet_manager',
23                      'employee_sales_fsp_manager',
24                      'dealer'
25                     ]
26                         
27    roles_in_order.reverse.each do |role|  
28      execute <<-SQL
29       UPDATE contacts c INNER JOIN users u ON u.contact_id = c.id 
30       SET c.role = u.role
31       WHERE u.role = '#{role}'
32       AND u.active = 1
33      SQL
34
35      execute <<-SQL
36       UPDATE contacts c 
37       INNER JOIN users u ON u.contact_id = c.id 
38       INNER JOIN contacts_with_inactive_users ciu ON u.contact_id = ciu.id
39       SET c.role = u.role
40       WHERE u.role = '#{role}'
41      SQL
42    end
43    
44    drop_table :contacts_with_inactive_users
45
46  end