0337d704 |
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; |