| 1 | -- MySQL dump 9.11 |
| 2 | |
| 3 | -- |
| 4 | -- Table structure for table register_pending |
| 5 | -- |
| 6 | |
| 7 | CREATE TABLE register_pending ( |
| 8 | uid int NOT NULL default '0', |
| 9 | forlife varchar(255) NOT NULL default '', |
| 10 | bestalias varchar(255) NOT NULL default '', |
| 11 | mailorg2 varchar(255) default null, |
| 12 | password varchar(32) NOT NULL default '', |
| 13 | email varchar(255) NOT NULL default '', |
| 14 | date date NOT NULL default '0000-00-00', |
| 15 | relance date NOT NULL default '0000-00-00', |
| 16 | naissance date NOT NULL default '0000-00-00', |
| 17 | hash varchar(12) NOT NULL default '', |
| 18 | PRIMARY KEY (uid), |
| 19 | UNIQUE KEY (forlife), |
| 20 | UNIQUE KEY (bestalias), |
| 21 | INDEX (hash) |
| 22 | ); |
| 23 | |
| 24 | create table register_marketing (/*{{{*/ |
| 25 | uid int not null default 0, |
| 26 | sender int not null default 0, |
| 27 | email varchar(255) not null default '', |
| 28 | date date not null default '0000-00-00', |
| 29 | last date not null default '0000-00-00', |
| 30 | nb tinyint not null default 0, |
| 31 | type enum('user', 'staff') not null default 'user', |
| 32 | hash char(32) not null, |
| 33 | PRIMARY KEY (uid, email), |
| 34 | INDEX (date), |
| 35 | INDEX (last), |
| 36 | INDEX (nb) |
| 37 | ); |
| 38 | |
| 39 | create table register_mstats ( |
| 40 | uid int not null default 0, |
| 41 | sender int not null default 0, |
| 42 | success date not null default '0000-00-00', |
| 43 | PRIMARY KEY (uid) |
| 44 | ); |
| 45 | |
| 46 | |
| 47 | -- |
| 48 | -- envoidirect -> register_mstats |
| 49 | -- |
| 50 | |
| 51 | insert into register_mstats (uid, sender, success) |
| 52 | select distinct u.user_id, e.sender, u.date_ins |
| 53 | from envoidirect as e |
| 54 | inner join auth_user_md5 as u using(matricule) |
| 55 | where u.date_ins != 0 group by user_id; |
| 56 | |
| 57 | insert into register_marketing (uid, sender, email, date, last, nb, type, hash) |
| 58 | select u.user_id, m.expe, m.email, 0, 0, FIND_IN_SET('envoye', m.flags), IF(FIND_IN_SET('mail_perso', m.flags), 'user', 'staff'), '' |
| 59 | from marketing as m |
| 60 | inner join auth_user_md5 as u on u.matricule = m.dest |
| 61 | where date_ins = 0 and deces = 0 |
| 62 | group by user_id, m.email; |
| 63 | |
| 64 | replace into register_marketing (uid, sender, email, date, last, nb, type, hash) |
| 65 | select u.user_id, e.sender, e.email, date_envoi, date_envoi, 1, 'staff', e.uid |
| 66 | from envoidirect as e |
| 67 | inner join auth_user_md5 as u using(matricule) |
| 68 | where date_ins = 0 and deces = 0; |
| 69 | |
| 70 | drop table envoidirect; |
| 71 | drop table marketing;/*}}}*/ |
| 72 | |
| 73 | insert into register_pending (uid, forlife, bestalias, mailorg2, password, email, date, relance, naissance, hash) |
| 74 | select u.user_id, e.username, |
| 75 | IF(e.loginbis, e.loginbis, REPLACE(REPLACE(e.username, '.19', ''), '.20', '')), |
| 76 | IF(e.loginbis, REPLACE(REPLACE(e.username, '.19', ''), '.20', ''), NULL), |
| 77 | e.password, email, e.date, relance, e.naissance, ins_id |
| 78 | from en_cours AS e |
| 79 | inner join auth_user_md5 AS u USING( matricule ) |
| 80 | where u.perms = 'pending'; |
| 81 | |
| 82 | drop table en_cours; |