| 1 | CREATE TABLE IF NOT EXISTS profile_directory ( |
| 2 | uid INT NOT NULL, |
| 3 | email_directory VARCHAR(255) DEFAULT NULL, |
| 4 | PRIMARY KEY (uid) |
| 5 | ) CHARSET=utf8; |
| 6 | |
| 7 | INSERT INTO profile_directory (uid, email_directory) |
| 8 | SELECT user_id, Mel_usage |
| 9 | FROM fusionax_anciens AS ax |
| 10 | INNER JOIN auth_user_md5 AS u ON (ax.id_ancien = CONVERT(u.matricule_ax, BINARY)) |
| 11 | WHERE Mel_publiable != '0' AND Mel_usage != ''; |
| 12 | |
| 13 | |
| 14 | ALTER TABLE register_marketing MODIFY COLUMN type ENUM('user', 'staff', 'ax'); |
| 15 | |
| 16 | INSERT IGNORE INTO register_marketing (uid, email, type) |
| 17 | SELECT user_id, Mel_usage, 'ax' |
| 18 | FROM fusionax_anciens AS ax |
| 19 | INNER JOIN auth_user_md5 AS u ON (ax.id_ancien = CONVERT(u.matricule_ax, BINARY)) |
| 20 | LEFT JOIN emails AS e ON (e.uid = u.user_id AND e.flags = 'active') |
| 21 | WHERE Mel_usage != '' AND |
| 22 | Mel_usage NOT LIKE '%@polytechnique.edu' AND |
| 23 | Mel_usage NOT LIKE '%@polytechnique.org' AND |
| 24 | Mel_usage NOT LIKE '%@m4x.org' AND |
| 25 | Mel_usage NOT LIKE '%@melix.%' AND |
| 26 | e.email IS NULL; |
| 27 | |