1 DROP PROCEDURE if exists p_report_xxsjtjb$$
2 CREATE PROCEDURE p_report_xxsjtjb(IN pi_branchid INT(11), -- 分店id
3 IN pi_sb SMALLINT, -- 市别，0:午市；1:晚市；-1:全天
4 IN pi_ksrq DATETIME, -- 开始日期，
5 IN pi_jsrq DATETIME, -- 结束日期
6 IN pi_cxlx SMALLINT, -- 查询类型 1:应收金额 2:实收金额 3:结算人数 4:开台数
7 IN pi_qy VARCHAR(50), -- 餐台所属区域 -1:全部 非-1:区域id
8 OUT po_errmsg VARCHAR(100)
9 -- 错误信息
10 )
11 SQL SECURITY INVOKER
12 COMMENT '详细数据统计表'
13 label_main:
14 BEGIN
15 -- 返回字段说明如下(2个固定字段+不固定字段)：
16 -- 区域 桌号 时间列1 时间列2 时间列3.....
17 --
18 -- 返回数据举例（返回值包含多条数据）：
19 -- 大堂 1 22 33 44
20
21
22 DECLARE v_date_start DATETIME;
23 DECLARE v_date_end DATETIME;
24 DECLARE v_date_interval DATETIME; #时间间隔
25 DECLARE v_loop_num INT DEFAULT 0; #根据开始结束时间和显示类型，来设置循环次数
26 DECLARE v_table_index INT;
27 DECLARE v_table_id VARCHAR(50);
28 DECLARE v_sql VARCHAR(5000) DEFAULT '';
29
30 -- 异常处理模块，出现异常返回null
31 DECLARE EXIT HANDLER FOR SQLEXCEPTION
32 BEGIN
33 SELECT NULL;
34 GET DIAGNOSTICS CONDITION 1 po_errmsg = MESSAGE_TEXT;
35 END;
36
37 IF pi_branchid IS NULL THEN
38 SELECT NULL;
39 SET po_errmsg = '分店ID输入不能为空';
40 LEAVE label_main;
41 END IF;
42
43
44 SET @@max_heap_table_size = 1024 * 1024 * 300;
45 SET @@tmp_table_size = 1024 * 1024 * 300;
46
47 #设置循环次数,处理开始结算时间
48 SET v_date_start = str_to_date(concat(date_format(pi_ksrq, '%Y-%m-%d'), '00:00:00'), '%Y-%m-%d %H:%i:%s');
49 SET v_date_end = str_to_date(concat(date_format(pi_jsrq, '%Y-%m-%d'), '23:59:59'), '%Y-%m-%d %H:%i:%s');
50 SET v_date_interval = date_sub(date_add(v_date_start, INTERVAL 1 DAY), INTERVAL 1 SECOND);
51 SET v_loop_num = timestampdiff(DAY, v_date_start, v_date_end) + 1;
52
53 #创建订单临时内存表
54 DROP TEMPORARY TABLE IF EXISTS t_temp_order;
55 CREATE TEMPORARY TABLE t_temp_order
56 (
57 orderid VARCHAR(50),
58 begintime DATETIME,
59 tableid VARCHAR(50),
60 womanNum INT,
61 childNum INT,
62 mannum INT
63 ) ENGINE = MEMORY DEFAULT CHARSET = utf8;
64
65 IF pi_sb > -1 THEN
66 INSERT INTO t_temp_order
67 SELECT orderid
68 , begintime
69 , currenttableid
70 , womanNum
71 , childNum
72 , mannum
73 FROM
74 t_order USE INDEX (IX_t_order_begintime)
75 WHERE
76 branchid = pi_branchid
77 AND begintime BETWEEN v_date_start AND v_date_end -- 需要创建索引IX_t_order_begintime
78 AND shiftid = pi_sb
79 AND orderstatus = 3;
80 ELSE
81 INSERT INTO t_temp_order
82 SELECT orderid
83 , begintime
84 , currenttableid
85 , womanNum
86 , childNum
87 , mannum
88 FROM
89 t_order USE INDEX (IX_t_order_begintime)
90 WHERE
91 branchid = pi_branchid
92 AND begintime BETWEEN v_date_start AND v_date_end -- 需要创建索引IX_t_order_begintime
93 AND orderstatus = 3;
94 END IF;
95
96 #为订单内存表创建索引
97 CREATE UNIQUE INDEX ix_t_temp_order_orderid ON t_temp_order (orderid);
98
99
100 IF pi_cxlx = 1 THEN
101 DROP TEMPORARY TABLE IF EXISTS t_temp_order_detail;
102 CREATE TEMPORARY TABLE t_temp_order_detail
103 (
104 orderid VARCHAR(50),
105 dishnum DOUBLE(13, 2),
106 orignalprice DOUBLE(13, 2),
107 tableid VARCHAR(50),
108 begintime DATETIME,
109 childdishtype TINYINT,
110 primarykey VARCHAR(50),
111 superkey VARCHAR(50)
112 ) ENGINE = MEMORY DEFAULT CHARSET = utf8 MAX_ROWS = 1000000;
113
114 # 向临时订单详情内存表中放值
115 INSERT INTO t_temp_order_detail
116 SELECT b.orderid
117 , b.dishnum
118 , b.orignalprice
119 , a.tableid
120 , a.begintime
121 , b.childdishtype
122 , b.primarykey
123 , b.superkey
124 FROM
125 t_temp_order a, t_order_detail b
126 WHERE
127 a.orderid = b.orderid
128 AND b.orignalprice > 0;
129
130 # 删除套餐明细
131 DROP TEMPORARY TABLE IF EXISTS t_temp_keys;
132 CREATE TEMPORARY TABLE t_temp_keys
133 (
134 primarykey VARCHAR(50)
135 ) ENGINE = MEMORY DEFAULT CHARSET = utf8 MAX_ROWS = 1000000;
136 INSERT INTO t_temp_keys
137 SELECT primarykey
138 FROM
139 t_temp_order_detail
140 WHERE
141 childdishtype = 2;
142
143 DELETE
144 FROM
145 t_temp_order_detail
146 USING
147 t_temp_order_detail, t_temp_keys
148 WHERE
149 t_temp_order_detail.superkey = t_temp_keys.primarykey
150 AND t_temp_order_detail.primarykey != t_temp_keys.primarykey;
151
152 CREATE INDEX ix_t_temp_order_detail_begintime ON t_temp_order_detail (begintime);
153
154 #创建结算明细内存表
155 ELSEIF pi_cxlx = 2 THEN
156 DROP TEMPORARY TABLE IF EXISTS t_temp_settlement_detail;
157 CREATE TEMPORARY TABLE t_temp_settlement_detail
158 (
159 orderid VARCHAR(50),
160 payway INT,
161 payamount DOUBLE(13, 2),
162 begintime DATETIME,
163 tableid VARCHAR(50)
164 ) ENGINE = MEMORY DEFAULT CHARSET = utf8 MAX_ROWS = 1000000;
165
166 #生产临时结算明细表数据
167 INSERT INTO t_temp_settlement_detail
168 SELECT b.orderid
169 , b.payway
170 , b.payamount
171 , a.begintime
172 , a.tableid
173 FROM
174 t_temp_order a, t_settlement_detail b
175 WHERE
176 a.orderid = b.orderid
177 AND b.payway IN (0, 1, 5, 8, 13, 17, 18) -- shangwenchao 2015/12/21 22:46:49 增加结算方式13, 17 ,18
178 AND b.payamount > 0;
179
180 CREATE INDEX ix_t_temp_settlement_detail_begintime ON t_temp_settlement_detail (begintime);
181
182 #创建会员消费内存表
183 DROP TEMPORARY TABLE IF EXISTS t_temp_order_member;
184 CREATE TEMPORARY TABLE t_temp_order_member
185 (
186 orderid VARCHAR(50),
187 Inflated DOUBLE(13, 2),
188 begintime DATETIME,
189 tableid VARCHAR(50)
190 ) ENGINE = MEMORY DEFAULT CHARSET = utf8 MAX_ROWS = 1000000;
191
192 #生成临时会员结算数据
193 INSERT INTO t_temp_order_member
194 SELECT b.orderid
195 , b.Inflated
196 , a.begintime
197 , a.tableid
198 FROM
199 t_temp_order a, t_order_member b
200 WHERE
201 a.orderid = b.orderid;
202 CREATE INDEX ix_t_temp_order_member_begintime ON t_temp_order_member (begintime);
203 END IF;
204
205
206 #筛选桌子
207 DROP TEMPORARY TABLE IF EXISTS t_temp_table;
208 CREATE TEMPORARY TABLE t_temp_table
209 (
210 areaname VARCHAR(10),
211 tableid VARCHAR(50),
212 tableNo VARCHAR(50)
213 ) ENGINE = MEMORY DEFAULT CHARSET = utf8;
214
215 IF pi_qy = '-1' THEN
216 INSERT INTO t_temp_table (areaname, tableid, tableNo)
217 SELECT b.areaname
218 , a.tableid
219 , a.tableNo
220 FROM
221 t_table a, t_tablearea b
222 WHERE
223 a.areaid = b.areaid
224 AND b.branchid = pi_branchid;
225 ELSE
226 INSERT INTO t_temp_table (areaname, tableid, tableNo)
227 SELECT b.areaname
228 , a.tableid
229 , a.tableNo
230 FROM
231 t_table a, t_tablearea b
232 WHERE
233 a.areaid = b.areaid
234 AND b.areaid = pi_qy
235 AND b.branchid = pi_branchid;
236 END IF;
237
238
239 #创建结果集
240 DROP TEMPORARY TABLE IF EXISTS t_temp_res;
241 CREATE TEMPORARY TABLE t_temp_res
242 (
243 areaname VARCHAR(10),
244 tableid VARCHAR(50),
245 stime VARCHAR(10),
246 svalue DOUBLE(13, 2)
247 ) ENGINE = MEMORY DEFAULT CHARSET = utf8;
248
249
250 #创建虚增结果集
251 DROP TEMPORARY TABLE IF EXISTS t_temp_inflated;
252 CREATE TEMPORARY TABLE t_temp_inflated
253 (
254 tableid VARCHAR(50),
255 stime VARCHAR(10),
256 svalue DOUBLE(13, 2)
257 ) ENGINE = MEMORY DEFAULT CHARSET = utf8;
258
259 #####################################遍历桌子及统计天数，生成结果数据##########################################
260 WHILE v_loop_num > 0
261 DO
262
263 #统计应收
264 IF pi_cxlx = 1 THEN
265 INSERT INTO t_temp_res (tableid, stime, svalue)
266 SELECT tableid
267 , date_format(v_date_start, '%Y/%m/%d')
268 , sum(dishnum * orignalprice)
269 FROM
270 t_temp_order_detail
271 WHERE
272 begintime BETWEEN v_date_start AND v_date_interval
273 GROUP BY
274 tableid;
275
276 #统计实收
277 ELSEIF pi_cxlx = 2 THEN
278 INSERT INTO t_temp_res (tableid, stime, svalue)
279 SELECT tableid
280 , date_format(v_date_start, '%Y/%m/%d')
281 , ifnull(sum(payamount), 0)
282 FROM
283 t_temp_settlement_detail
284 WHERE
285 begintime BETWEEN v_date_start AND v_date_interval
286 GROUP BY
287 tableid;
288
289 INSERT INTO t_temp_inflated
290 SELECT tableid
291 , date_format(v_date_start, '%Y/%m/%d')
292 , ifnull(sum(Inflated), 0)
293 FROM
294 t_temp_order_member
295 WHERE
296 begintime BETWEEN v_date_start AND v_date_interval
297 GROUP BY
298 tableid;
299
300 #INSERT INTO t_temp_res (tableid, stime, svalue) VALUES (v_table_id, date_format(v_date_start, '%Y/%m/%d'), @shishou - @xuzeng);
301
302 #统计结账人数
303 ELSEIF pi_cxlx = 3 THEN
304 INSERT INTO t_temp_res (tableid, stime, svalue)
305 SELECT tableid
306 , date_format(v_date_start, '%Y/%m/%d')
307 , ifnull(sum(mannum + womanNum + childNum), 0)
308 FROM
309 t_temp_order
310 WHERE
311 begintime BETWEEN v_date_start AND v_date_interval
312 GROUP BY
313 tableid;
314
315 #统计开台数据
316 ELSEIF pi_cxlx = 4 THEN
317 INSERT INTO t_temp_res (tableid, stime, svalue)
318 SELECT tableid
319 , date_format(v_date_start, '%Y/%m/%d')
320 , count(1)
321 FROM
322 t_temp_order
323 WHERE
324 begintime BETWEEN v_date_start AND v_date_interval
325 GROUP BY
326 tableid;
327 #退出程序
328 ELSE
329 SELECT NULL;
330 SET po_errmsg = '显示类型输入有误 1:应收金额 2:实收金额 3:结算人数 4:开台数';
331 LEAVE label_main;
332 END IF;
333
334 #动态拼装sql，为后面的返回数据做准备
335 SET @date_start = date_format(v_date_start, '%Y/%m/%d');
336 SET v_sql = concat(v_sql, ',max(case stime when \'', @date_start, '\' then svalue else 0 end) \'', @date_start, '\'');
337
338 SET v_date_start = date_add(v_date_start, INTERVAL 1 DAY);
339 SET v_date_interval = date_add(v_date_interval, INTERVAL 1 DAY);
340 SET v_loop_num = v_loop_num - 1;
341 END WHILE;
342
343
344 #删除不需要统计的桌号
345 DELETE t_temp_res
346 FROM
347 t_temp_res
348 LEFT JOIN t_temp_table
349 ON t_temp_res.tableid = t_temp_table.tableid
350 WHERE
351 t_temp_table.tableid IS NULL;
352
353 #更新实收数据
354 UPDATE t_temp_res a, t_temp_inflated b
355 SET
356 a.svalue = a.svalue - b.svalue
357 WHERE
358 a.tableid = b.tableid
359 AND a.stime = b.stime;
360
361 #更新桌号及区域名称
362 UPDATE t_temp_res a, t_temp_table b
363 SET
364 a.areaname = b.areaname, a.tableid = b.tableNo
365 WHERE
366 a.tableid = b.tableid;
367
368 #返回数据
369 SET v_sql = concat('SELECT areaname, tableid', v_sql);
370 SET v_sql = concat(v_sql, ' FROM t_temp_res GROUP BY areaname, tableid order by tableid-0,areaname');
371 SET @sql_xxsj = v_sql;
372 PREPARE s1 FROM @sql_xxsj;
373 EXECUTE s1;
374 DEALLOCATE PREPARE s1;
375
376 #清空内存表
377 -- DROP TEMPORARY TABLE IF EXISTS t_temp_order;
378 -- DROP TEMPORARY TABLE IF EXISTS t_temp_order_detail;
379 -- DROP TEMPORARY TABLE IF EXISTS t_temp_settlement_detail;
380 -- DROP TEMPORARY TABLE IF EXISTS t_temp_order_member;
381 -- DROP TEMPORARY TABLE IF EXISTS t_temp_table;
382 -- DROP TEMPORARY TABLE IF EXISTS t_temp_res;
383 -- DROP TEMPORARY TABLE IF EXISTS t_temp_inflated;
384 -- DROP TEMPORARY TABLE IF EXISTS t_temp_keys;
385 END
386 $$
