1 delimiter $$
2 CREATE PROCEDURE `proc_Ranking`(IN sku VARCHAR(20),IN skuname VARCHAR(20),IN status VARCHAR(20),IN sales VARCHAR(20),IN today VARCHAR(20),IN old_time VARCHAR(20))
3 BEGIN
4 SET @sku = sku;
5 SET @skuname = skuname;
6 SET @status = status;
7 SET @sales = sales;
8 SET @today = today;
9 SET @old_time = old_time;
10 SET @sql_header = "SELECT
11 c.`sku`,c.`amt`,c.`avgs` avg,s.`skuname`,s.`skupic`,s.`edittime`,st.`statusname`
12 FROM(
13 SELECT
14 `sku` , ROUND(SUM( `amt` ) , 2) 'amt' , ROUND(SUM( `avg` ) /7 , 2) 'avgs'
15 FROM `skusales`
16 WHERE
17 `sku` <> ''
18 AND `paytime` > @old_time
19 AND `paytime` < @today
20 GROUP BY `sku`) c
21 LEFT JOIN
22 `skustatus` s ON c.`sku` = s.`sku`
23 LEFT JOIN
24 `statustype` st ON s.`sale_status` = st.`pid`
25 WHERE
26 1";
27 IF @sku <> '' THEN
28 SET @sql_header = CONCAT(@sql_header," AND c.`sku` = @sku");
29 END IF;
30
31 IF @status <> '' THEN
32 SET @sql_header = CONCAT(@sql_header," AND s.`sale_status` = @status");
33 END IF;
34
35 IF @skuname <> '' THEN
36 SET @sql_header = CONCAT(@sql_header," AND s.`skuname` like @skuname");
37 END IF;
38
39 IF @sales <> '' THEN
40 SET @sql_header = CONCAT(@sql_header," AND c.avgs");
41 SET @sql_header = CONCAT(@sql_header,@sales);
42 END IF;
43 SET @sql_header = CONCAT(@sql_header," ORDER BY c.amt DESC");
44
45 SET @sql_bodys = @sql_header;
46
47 PREPARE stmt FROM @sql_bodys;
48 EXECUTE stmt;
49 DEALLOCATE PREPARE stmt ;
50
51 END$$
