1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
| DROP PROCEDURE IF EXISTS export;
CREATE PROCEDURE export()
general: BEGIN
DECLARE exit_proc INT DEFAULT 0;
DECLARE userid INT;
DECLARE useremail CHAR(250);
DECLARE titlech CHAR(250);
DECLARE firstname CHAR(250);
DECLARE lastname CHAR(250);
DECLARE companych CHAR(250);
DECLARE address1 CHAR(250);
DECLARE address2 CHAR(250);
DECLARE zipch INT;
DECLARE citych CHAR(250);
DECLARE countrych CHAR(250);
DECLARE phone1 CHAR(250);
DECLARE phone2 CHAR(250);
DECLARE bdaych CHAR(250);
DECLARE newsletterch CHAR(250);
DECLARE lastvisitDatech CHAR(250);
DECLARE formatmsgid INT DEFAULT 2;
DECLARE date_bday CHAR(10);
DECLARE date_lastvisite CHAR(10);
DECLARE maxcouponval INT DEFAULT 0;
DECLARE maxcouponcode CHAR(15) DEFAULT "RAZ-DOLIST";
DECLARE maxcoupondate CHAR(10) DEFAULT "01/01/1900";
DECLARE nbcmd INT DEFAULT 0;
DECLARE date_cmd CHAR(10);
DECLARE totalmontantcmd int DEFAULT 0;
DECLARE orderid INT;
DECLARE lastdatech CHAR(10);
DECLARE ordertotal INT DEFAULT 0;
DECLARE couponcode CHAR(10);
DECLARE couponvalue INT default 0;
DECLARE datefincoup CHAR(10);
DECLARE title INT DEFAULT 2;
DECLARE countryid INT DEFAULT 1;
DECLARE familial CHAR(1) default '';
DECLARE ambiance CHAR(1) default '';
DECLARE geeks CHAR(1) default '';
DECLARE duo CHAR(1) default '';
DECLARE enfants CHAR(1) default '';
DECLARE cursorallclients CURSOR FOR SELECT distinct inf.user_id,user_email,title,first_name,last_name,company,address_1,address_2,zip,city,country,phone_1,phone_2,date_format(bday, '%Y-%m-%d') bday,newsletter,date_format(lastvisitDate, '%Y-%m-%d') lastvisitDate From jos_vm_user_info inf,jos_users us Where inf.user_id=us.id and inf.address_type = 'BT' and inf.address_1 <> '' and inf.user_id not in (62,63) and user_email not like '%@espritjeu.com' order by inf.user_id desc;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exit_proc = 1;
Truncate exportdolist;
OPEN cursorallclients;
REPEAT
SET date_bday = '1900-01-01';
SET formatmsgid = 2;
SET date_lastvisite = '1900-01-01';
FETCH cursorallclients into userid,useremail,titlech,firstname,lastname,companych,address1,address2,zipch,citych,countrych,phone1,phone2,bdaych,newsletterch,lastvisitDatech;
/*formatage titre */
if(titlech = 'Mr')then
set title = '3';
elseif(titlech = 'Mme')then
set title = '1';
end if;
/*Formatage Adresse*/
Set address1 = "";
set address1 = replace(address1,"\'", "'");
set address1 = replace(address1,"\r", "");
set address1 = replace(address1,"\n", "");
set address1 = replace(address1,'\"', '"');
set address2 = replace(address2,"\'", "'");
set address2 = replace(address2,"\r", "");
set address2 = replace(address2,"\n", "");
set address2 = replace(address2,'\"', '"');
/*Formatage Pays*/
if(countrych = "BEL")then
set countryid = "2";
elseif(countrych = "SUI")then
set countryid = "36";
elseif(countrych = "FIN")then
set countryid = "32";
elseif(countrych = "ROY")then
set countryid = "61";
end if;
/*format des mails par défaut (2 = html; 1 = texte ; 3 = AOL)*/
SET formatmsgid = 2;
IF(LOCATE('aol.', useremail) <> 0)then
SET formatmsgid = 3;
end if;
/*formatage date anniversaire*/
IF(bdaych = '0000-00-00' || bdaych = '1930-01-01')then
SET date_bday = '1900-01-01';
else
SET date_bday = bdaych;
end if;
/*formatage date dernière visite*/
if(lastvisitDatech = '0000-00-00')then
SET date_lastvisite = '1900-01-01';
else
SET date_lastvisite = lastvisitDatech;
end if;
/* recup des commandes validées ou réglées ou expédiées pour chaque client*/
cmd: BEGIN
DECLARE nborder int default 0;
DECLARE cursorcmd CURSOR FOR SELECT distinct order_id,from_unixtime(cdate, '%Y-%m-%d') lastdate, order_total from jos_vm_orders where user_id = userid and order_status not in ('p','x','r','l') order by cdate asc;
OPEN cursorcmd;
SELECT count(order_id) into nborder from jos_vm_orders where user_id = userid and order_status not in ('p','x','r','l') order by cdate asc;
SET maxcouponval = 0;
SET maxcouponcode = "RAZ-DOLIST";
SET maxcoupondate = "1900-01-01";
SET nbcmd = 0;
SET totalmontantcmd = 0;
SET date_cmd = '1900-01-01';
REPEAT
FETCH cursorcmd into orderid,lastdatech,ordertotal;
SET nbcmd = nbcmd +1;
SET date_cmd = lastdatech;
SET totalmontantcmd = totalmontantcmd + ordertotal;
/*recherche d'un coupon valide correspondant à la commande en cours*/
SELECT coupon_code,coupon_value,DATE_FORMAT(coupon_datef, '%Y-%m-%d') datef into couponcode,couponvalue,datefincoup FROM jos_vm_coupons
WHERE (coupon_code like concat(orderid,'D%') or coupon_code like concat(orderid,'B%') or coupon_code like concat(orderid,'A%')) and coupon_used = 0 and CURDATE() BETWEEN coupon_dated and coupon_datef;
If(couponvalue >= maxcouponval)then
SET maxcouponval = round(couponvalue,0);
SET maxcouponcode = couponcode;
SET maxcoupondate = datefincoup;
End If;
UNTIL nbcmd >= nborder END REPEAT;
close cursorcmd;
END cmd;
/*classement par type de joueur*/
/*Familial*/
select distinct "F" into familial from jos_vm_orders where order_id in (
select order_id from jos_vm_order_item where product_id in (
select product_id from jos_vm_product_category_xref where category_id in (36,19,52,2))) and user_id = userid;
/*Ambiance*/
select distinct "A" into ambiance from jos_vm_orders where order_id in (
select order_id from jos_vm_order_item where product_id in (
select product_id from jos_vm_product_category_xref where category_id in (40,17,7,8,18,51))) and user_id = userid;
/*Geeks*/
select distinct "G" into geeks from jos_vm_orders where order_id in (
select order_id from jos_vm_order_item where product_id in (
select product_id from jos_vm_product_category_xref where category_id in (21,3,53))) and user_id = userid;
/*Duo*/
select distinct "D" into duo from jos_vm_orders where order_id in (
select order_id from jos_vm_order_item where product_id in (
select product_id from jos_vm_product_category_xref where category_id in (13,14,15))) and user_id = userid;
/*Enfants*/
select distinct "E" into enfants from jos_vm_orders where order_id in (
select order_id from jos_vm_order_item where product_id in (
select product_id from jos_vm_product_category_xref where category_id = "12") ) and user_id = userid;
/*Tramage de la ligne*/
/*set ligne ="Email\tSalutationId\tFirstName\tLastName\tCompany\tAddress 1\tAddress 2\tAddress 3\tZipCode\tCity\tCountryID\tPhone\tFax\tMobile Phone\tMsgFormatId\tSaleMgt\tBirthdate\tCustomDate1\tCustomDate2\tCustomDate3\tCustomStr1\tCustomStr2\tCustomStr3\tCustomStr4\tCustomStr5\tCustomStr6\tCustomStr7\tCustomStr8\tCustomStr9\tCustomStr10\tCustomStr11\tCustomStr12\tCustomStr13\tCustomStr14\tCustomStr15\tCustomStr16\tCustomStr17\tCustomStr18\tCustomStr19\tCustomStr20\tCustomStr21\tCustomStr22\tCustomStr23\tCustomStr24\tCustomStr25\tCustomStr26\tCustomStr27\tCustomStr28\tCustomStr29\tCustomStr30\tCustomInt1\tCustomInt2\tCustomInt3\tCustomInt4\tCustomInt5\tCustomInt6\tCustomInt7\tCustomInt8\tCustomInt9\tCustomInt10\n" ;
/*set ligne = $row['user_email'] ."\t". title ."\t". ucfirst($row['first_name']) ."\t". ucfirst($row['last_name']) ."\t".$row['company']."\t".$address1."\t".$address2."\t\t".$row['zip']."\t".ucfirst($row['city'])."\t".$countryid."\t\t\t\t".$formatmsgid."\t1\t".$date_bday."\t".$date_lastvisite."\t".$date_cmd."\t".$maxcoupondate."\t".$row['newsletter']."\t". $maxcouponcode ."\t". $familial ."\t". $ambiance ."\t". $geeks ."\t".$duo."\t".$enfants."\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t". round($maxcouponval,0) ."\t".$nbcmd."\t".round($totalmontantcmd,0)."\t\t\t\t\t\t\t\n";
/*sortie en table EsportDolist*/
Insert into exportdolist values(userid,useremail,title,firstname,lastname,companych,address1,address2,'',zipch,citych,countryid,null,null,null,formatmsgid,1,date_bday,date_lastvisite,date_cmd,maxcoupondate,newsletterch,maxcouponcode,familial,ambiance,geeks,duo,enfants,'','','','','','','','','','','','','','','','','','','','','','','',round(maxcouponval,0),nbcmd,round(totalmontantcmd,0),null,null,null,null,null,null,null);
UNTIL exit_proc END REPEAT;
CLOSE cursorallclients;
END general |
Boucle repeat qui s'arrête au premier enregistrement
Répondre avec citation
Partager