· 8 years ago · Dec 04, 2017, 08:18 PM
1/*!50003 SET sql_mode = @saved_sql_mode */ ;
2/*!50003 SET character_set_client = @saved_cs_client */ ;
3/*!50003 SET character_set_results = @saved_cs_results */ ;
4/*!50003 SET collation_connection = @saved_col_connection */ ;
5/*!50003 DROP PROCEDURE IF EXISTS `autocancel` */;
6/*!50003 SET @saved_cs_client = @@character_set_client */ ;
7/*!50003 SET @saved_cs_results = @@character_set_results */ ;
8/*!50003 SET @saved_col_connection = @@collation_connection */ ;
9/*!50003 SET character_set_client = utf8 */ ;
10/*!50003 SET character_set_results = utf8 */ ;
11/*!50003 SET collation_connection = utf8_general_ci */ ;
12/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
13/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
14DELIMITER ;;
15CREATE DEFINER=`root`@`%` PROCEDURE `autocancel`()
16BEGIN
17DROP temporary TABLE IF EXISTS tmpIds;
18CREATE temporary TABLE tmpIds (id varchar(20));
19
20INSERT INTO tmpIds
21SELECT res.id
22 FROM reservation res
23 WHERE
24 (
25 res.timestamp < subdate(NOW(), INTERVAL 1 day)
26 -- OR ( res.timestamp < subtime(NOW(), '4:0:0:0') ) -- order vexere-online qua 4 tieng
27
28 OR ( res.timestamp < subtime(NOW(), '2:0:0:0') ) -- order 3 qua 3 tieng AND (res.reservee <> 'vexere-online')
29
30
31 OR ( res.timestamp < subtime(NOW(), '1:0:0:0')
32 AND (res.reservee = 'dlvexere-hcm' OR res.reservee = 'dlvexere-hn') ) -- order vexere qua 1 tieng
33
34 OR ( res.timestamp < subtime(NOW(), '0:10:0:0')
35 AND (res.reservee = 'online') AND
36 (SELECT COUNT(id)
37 FROM Seats
38 WHERE res.id = Seats.reservation
39 AND seats.Agent = '') > 0) -- Seat online thi agent phai = ''
40 ) -- order online qua 10 ph
41
42 -- limit for order xuat ben tu hom qua cho den 60 ngay toi
43 AND (res.gioxuatben between subdate(NOW(), INTERVAL 1 day)
44 AND ADDDATE(NOW(), INTERVAL 60 day))
45 AND res.reservee <> 'giucho' AND coalesce(res.giucho,0) <> '1' AND res.ticketing IS NULL
46 AND (SELECT COUNT(id)
47 FROM Seats
48 WHERE res.id = Seats.reservation AND Seat <> 0) -- Seat != 0
49 AND id <> 'G72BGD';
50
51SELECT * FROM tmpIds;
52
53IF( (SELECT COUNT(id) FROM tmpIds) > 0) THEN
54 -- update ticket
55 UPDATE Seats
56 SET Seat = 0
57 WHERE reservation IN
58 (SELECT id FROM tmpIds);
59
60 -- insert log
61 INSERT INTO eticket_logs
62 SET user_done = 'auto cancel',
63 date_done=NOW(),
64 table_done = 'reservation',
65 actions = 'Auto Cancel.',
66 note =
67 (SELECT group_concat(ID SEPARATOR ',') FROM tmpIds);
68END IF;
69END ;;
70DELIMITER ;
71/*!50003 SET sql_mode = @saved_sql_mode */ ;
72/*!50003 SET character_set_client = @saved_cs_client */ ;
73/*!50003 SET character_set_results = @saved_cs_results */ ;
74/*!50003 SET collation_connection = @saved_col_connection */ ;
75/*!50003 DROP PROCEDURE IF EXISTS `spChartShiftAndBusSummary` */;
76/*!50003 SET @saved_cs_client = @@character_set_client */ ;
77/*!50003 SET @saved_cs_results = @@character_set_results */ ;
78/*!50003 SET @saved_col_connection = @@collation_connection */ ;
79/*!50003 SET character_set_client = utf8 */ ;
80/*!50003 SET character_set_results = utf8 */ ;
81/*!50003 SET collation_connection = utf8_general_ci */ ;
82/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
83/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
84DELIMITER ;;
85CREATE DEFINER=`root`@`%` PROCEDURE `spChartShiftAndBusSummary`(in groupby nvarchar(15),in selecteddate datetime, in routeID int)
86BEGIN
87set @mysqlQuery=concat(
88 'select date, ',groupby,', sum(totalTicket) as totalTicket, sum(totalmoneyReceived) as totalmoneyReceived
89 from vViewShiftAndBusSummary where router_id = ',routeID,' and date =''',selecteddate,'''
90 Group by ', groupby);
91
92 prepare stmt from @mysqlQuery;
93 execute stmt;
94
95 DEALLOCATE PREPARE stmt;
96
97END ;;
98DELIMITER ;
99/*!50003 SET sql_mode = @saved_sql_mode */ ;
100/*!50003 SET character_set_client = @saved_cs_client */ ;
101/*!50003 SET character_set_results = @saved_cs_results */ ;
102/*!50003 SET collation_connection = @saved_col_connection */ ;
103/*!50003 DROP PROCEDURE IF EXISTS `spDuplicationSeats` */;
104/*!50003 SET @saved_cs_client = @@character_set_client */ ;
105/*!50003 SET @saved_cs_results = @@character_set_results */ ;
106/*!50003 SET @saved_col_connection = @@collation_connection */ ;
107/*!50003 SET character_set_client = utf8 */ ;
108/*!50003 SET character_set_results = utf8 */ ;
109/*!50003 SET collation_connection = utf8_general_ci */ ;
110/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
111/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
112DELIMITER ;;
113CREATE DEFINER=`root`@`%` PROCEDURE `spDuplicationSeats`(in ldate varchar(100))
114select s.* from seats s
115inner join(
116
117SELECT * , COUNT(*) AS number_record
118FROM seats where date >= ldate and seat >0 and res_order = 0 GROUP BY shift,date,seat HAVING number_record > 1
119) q
120on s.date = q.date and s.shift = q.shift and s.seat = q.seat
121
122 order by s.shift, s.seat ;;
123DELIMITER ;
124/*!50003 SET sql_mode = @saved_sql_mode */ ;
125/*!50003 SET character_set_client = @saved_cs_client */ ;
126/*!50003 SET character_set_results = @saved_cs_results */ ;
127/*!50003 SET collation_connection = @saved_col_connection */ ;
128/*!50003 DROP PROCEDURE IF EXISTS `spEticketLogs` */;
129/*!50003 SET @saved_cs_client = @@character_set_client */ ;
130/*!50003 SET @saved_cs_results = @@character_set_results */ ;
131/*!50003 SET @saved_col_connection = @@collation_connection */ ;
132/*!50003 SET character_set_client = utf8mb4 */ ;
133/*!50003 SET character_set_results = utf8mb4 */ ;
134/*!50003 SET collation_connection = utf8mb4_unicode_ci */ ;
135/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
136/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
137DELIMITER ;;
138CREATE DEFINER=`root`@`%` PROCEDURE `spEticketLogs`()
139Begin
140
141
142
143INSERT INTO hlong_archives.eticket_logs(id,user_done,date_done,table_done,session,actions,note,url)
144
145SELECT id,user_done,date_done,table_done,session,actions,note,url FROM hlong_eticket.eticket_logs WHERE date_done < SubDate(NOW(), INTERVAL 6 MONTH);
146
147
148
149DELETE FROM hlong_eticket.eticket_logs WHERE date_done < SubDate(NOW(), INTERVAL 6 MONTH);
150
151
152
153
154
155end ;;
156DELIMITER ;
157/*!50003 SET sql_mode = @saved_sql_mode */ ;
158/*!50003 SET character_set_client = @saved_cs_client */ ;
159/*!50003 SET character_set_results = @saved_cs_results */ ;
160/*!50003 SET collation_connection = @saved_col_connection */ ;
161/*!50003 DROP PROCEDURE IF EXISTS `spFindCancelledTicket` */;
162/*!50003 SET @saved_cs_client = @@character_set_client */ ;
163/*!50003 SET @saved_cs_results = @@character_set_results */ ;
164/*!50003 SET @saved_col_connection = @@collation_connection */ ;
165/*!50003 SET character_set_client = utf8 */ ;
166/*!50003 SET character_set_results = utf8 */ ;
167/*!50003 SET collation_connection = utf8_general_ci */ ;
168/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
169/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
170DELIMITER ;;
171CREATE DEFINER=`root`@`%` PROCEDURE `spFindCancelledTicket`(in fromDate nvarchar(30))
172BEGIN
173
174
175set @mysqlQuery=concat('SELECT r.id,r.date,r.shift,r.nocustomer,r.departure,r.destination,sh.start_time,sh.start_city,sh.stop_city,sh.type , seats
176
177 FROM reservation r
178
179 INNER JOIN shift sh ON r.shift = sh.id
180
181 LEFT JOIN seats s on r.shift = s.shift and r.date = s.date and r.seats = s.seat
182
183 WHERE r.nocustomer <> ''0'' AND r.date >= ''',STR_TO_DATE(fromDate,'%Y-%m-%d'),''' AND r.ticketing <> '''' and s.seat_id is null
184
185 ORDER BY r.date ASC');
186 prepare stmt from @mysqlQuery;
187 execute stmt;
188 DEALLOCATE PREPARE stmt;
189
190END ;;
191DELIMITER ;
192/*!50003 SET sql_mode = @saved_sql_mode */ ;
193/*!50003 SET character_set_client = @saved_cs_client */ ;
194/*!50003 SET character_set_results = @saved_cs_results */ ;
195/*!50003 SET collation_connection = @saved_col_connection */ ;
196/*!50003 DROP PROCEDURE IF EXISTS `spGetExposureCode` */;
197/*!50003 SET @saved_cs_client = @@character_set_client */ ;
198/*!50003 SET @saved_cs_results = @@character_set_results */ ;
199/*!50003 SET @saved_col_connection = @@collation_connection */ ;
200/*!50003 SET character_set_client = utf8 */ ;
201/*!50003 SET character_set_results = utf8 */ ;
202/*!50003 SET collation_connection = utf8_general_ci */ ;
203/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
204/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
205DELIMITER ;;
206CREATE DEFINER=`root`@`%` PROCEDURE `spGetExposureCode`(in sShiftID int)
207BEGIN
208Declare routerID int;
209declare seed int;
210
211set routerID = (select router_ID from ss_shifts where ID= sShiftID);
212
213select exposureseed, exposurecode from ss_routers where id = routerID;
214
215update ss_routers set exposureseed = exposureseed+1 where id = routerID;
216
217END ;;
218DELIMITER ;
219/*!50003 SET sql_mode = @saved_sql_mode */ ;
220/*!50003 SET character_set_client = @saved_cs_client */ ;
221/*!50003 SET character_set_results = @saved_cs_results */ ;
222/*!50003 SET collation_connection = @saved_col_connection */ ;
223/*!50003 DROP PROCEDURE IF EXISTS `spGetLimousineUsers` */;
224/*!50003 SET @saved_cs_client = @@character_set_client */ ;
225/*!50003 SET @saved_cs_results = @@character_set_results */ ;
226/*!50003 SET @saved_col_connection = @@collation_connection */ ;
227/*!50003 SET character_set_client = utf8 */ ;
228/*!50003 SET character_set_results = utf8 */ ;
229/*!50003 SET collation_connection = utf8_general_ci */ ;
230/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
231/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
232DELIMITER ;;
233CREATE DEFINER=`root`@`%` PROCEDURE `spGetLimousineUsers`()
234BEGIN
235 SELECT
236 u.id,
237 u.station_id,
238 u.username,
239 IFNULL(Limousine.LimousineDebit, 0) AS LimousineDebit,
240 u.deposits
241 FROM
242 office_users u
243 LEFT JOIN
244 (SELECT
245 userid, SUM(price) AS LimousineDebit
246 FROM
247 ss_reservations
248 WHERE
249 (user_confirm IS NULL
250 OR user_confirm = '')
251 AND type = 5
252 AND (isOfficerBooking IS NULL
253 OR isOfficerBooking = 0)
254 GROUP BY userid) AS Limousine ON Limousine.userid = u.id
255 WHERE
256 u.block = 0
257 AND EXISTS( SELECT
258 1
259 FROM
260 pm_stationpermissions p
261 JOIN
262 pm_permissions pp ON p.permissionid = pp.id
263 WHERE
264 p.stationid = u.station_id
265 AND pp.permissionName = 'ExpDatVeLimousine' UNION ALL SELECT
266 1
267 FROM
268 pm_userpermissions p
269 JOIN
270 pm_permissions pp ON p.permissionid = pp.id
271 WHERE
272 p.userid = u.id
273 AND pp.permissionName = 'ExpDatVeLimousine');
274END ;;
275DELIMITER ;
276/*!50003 SET sql_mode = @saved_sql_mode */ ;
277/*!50003 SET character_set_client = @saved_cs_client */ ;
278/*!50003 SET character_set_results = @saved_cs_results */ ;
279/*!50003 SET collation_connection = @saved_col_connection */ ;
280/*!50003 DROP PROCEDURE IF EXISTS `spGetOfficeShift` */;
281/*!50003 SET @saved_cs_client = @@character_set_client */ ;
282/*!50003 SET @saved_cs_results = @@character_set_results */ ;
283/*!50003 SET @saved_col_connection = @@collation_connection */ ;
284/*!50003 SET character_set_client = utf8 */ ;
285/*!50003 SET character_set_results = utf8 */ ;
286/*!50003 SET collation_connection = utf8_general_ci */ ;
287/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
288/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
289DELIMITER ;;
290CREATE DEFINER=`root`@`%` PROCEDURE `spGetOfficeShift`(in conditions nvarchar(100),in dTime nvarchar(20))
291BEGIN
292 set @mysqlQuery=concat( 'SELECT shift.id as id, dep.viet_name as departure, shift.start_time as start_time, des.viet_name as destination,
293 shift.type AS type, 0 as discount, ''addc'' as func, 0 as ischeck
294 FROM shift INNER JOIN stops dep ON shift.start_city=dep.id INNER JOIN stops des ON shift.stop_city=des.id
295 WHERE (',conditions,') AND shift.type=1
296
297 UNION
298
299 SELECT shift.id as id, dep.viet_name as departure, shift.start_time AS start_time, des.viet_name as destination,
300 shift.type AS type, shift_info.discount as discount, shift_info.func AS func, shift_info.ischeck AS ischeck
301 FROM shift INNER JOIN stops dep ON shift.start_city=dep.id INNER JOIN stops des ON shift.stop_city=des.id
302 INNER JOIN shift_info ON shift.id = shift_info.shift
303 WHERE (',conditions,') AND shift.type<>1 AND shift_info.date=''',STR_TO_DATE(dtime,'%Y-%m-%d'),''' AND shift_info.ischeck=1
304 ORDER BY start_time');
305
306 prepare stmt from @mysqlQuery;
307 execute stmt;
308 DEALLOCATE PREPARE stmt;
309END ;;
310DELIMITER ;
311/*!50003 SET sql_mode = @saved_sql_mode */ ;
312/*!50003 SET character_set_client = @saved_cs_client */ ;
313/*!50003 SET character_set_results = @saved_cs_results */ ;
314/*!50003 SET collation_connection = @saved_col_connection */ ;
315/*!50003 DROP PROCEDURE IF EXISTS `spGetOrdercode` */;
316/*!50003 SET @saved_cs_client = @@character_set_client */ ;
317/*!50003 SET @saved_cs_results = @@character_set_results */ ;
318/*!50003 SET @saved_col_connection = @@collation_connection */ ;
319/*!50003 SET character_set_client = utf8 */ ;
320/*!50003 SET character_set_results = utf8 */ ;
321/*!50003 SET collation_connection = utf8_general_ci */ ;
322/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
323/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
324DELIMITER ;;
325CREATE DEFINER=`root`@`%` PROCEDURE `spGetOrdercode`(in sShiftID int)
326BEGIN
327Declare routerID int;
328declare seed int;
329
330set routerID = (select router_ID from ss_shifts where ID= sShiftID);
331
332select orderseed, ordercode from ss_routers where id = routerID;
333
334update ss_routers set orderseed = orderseed+1 where id = routerID;
335
336END ;;
337DELIMITER ;
338/*!50003 SET sql_mode = @saved_sql_mode */ ;
339/*!50003 SET character_set_client = @saved_cs_client */ ;
340/*!50003 SET character_set_results = @saved_cs_results */ ;
341/*!50003 SET collation_connection = @saved_col_connection */ ;
342/*!50003 DROP PROCEDURE IF EXISTS `spGetShift` */;
343/*!50003 SET @saved_cs_client = @@character_set_client */ ;
344/*!50003 SET @saved_cs_results = @@character_set_results */ ;
345/*!50003 SET @saved_col_connection = @@collation_connection */ ;
346/*!50003 SET character_set_client = utf8 */ ;
347/*!50003 SET character_set_results = utf8 */ ;
348/*!50003 SET collation_connection = utf8_general_ci */ ;
349/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
350/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
351DELIMITER ;;
352CREATE DEFINER=`root`@`%` PROCEDURE `spGetShift`(in start_stop nvarchar(200), in end_stop nvarchar(200), in dtime nvarchar(20))
353BEGIN
354 set @mysqlQuery=concat( 'SELECT shift.*, 0 as resultType
355 FROM shift
356 WHERE (',start_stop,') AND type=1
357 UNION
358 SELECT shift.*,1 as resultType FROM shift,shift_info WHERE shift_info.shift = shift.id AND shift_info.date=''',STR_TO_DATE(dtime,'%Y-%m-%d'),''' AND (',end_stop,') AND shift.type >= 2
359
360 UNION
361 SELECT shift.*,2 as resultType FROM shift,shift_info WHERE shift_info.shift = shift.id AND shift_info.date=''',STR_TO_DATE(dtime,'%Y-%m-%d')-INTERVAL 1 DAY,''' AND (',end_stop,') AND shift.type >= 2
362
363 UNION
364 SELECT shift.*,3 as resultType FROM shift,shift_info WHERE shift_info.shift = shift.id AND shift_info.date=''',STR_TO_DATE(dtime,'%Y-%m-%d')-INTERVAL 2 DAY,''' AND (',end_stop,') AND shift.type >= 2
365 ORDER BY start_time');
366 prepare stmt from @mysqlQuery;
367 execute stmt;
368 DEALLOCATE PREPARE stmt;
369
370END ;;
371DELIMITER ;
372/*!50003 SET sql_mode = @saved_sql_mode */ ;
373/*!50003 SET character_set_client = @saved_cs_client */ ;
374/*!50003 SET character_set_results = @saved_cs_results */ ;
375/*!50003 SET collation_connection = @saved_col_connection */ ;
376/*!50003 DROP PROCEDURE IF EXISTS `spGetShiftNew` */;
377/*!50003 SET @saved_cs_client = @@character_set_client */ ;
378/*!50003 SET @saved_cs_results = @@character_set_results */ ;
379/*!50003 SET @saved_col_connection = @@collation_connection */ ;
380/*!50003 SET character_set_client = utf8 */ ;
381/*!50003 SET character_set_results = utf8 */ ;
382/*!50003 SET collation_connection = utf8_general_ci */ ;
383/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
384/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
385DELIMITER ;;
386CREATE DEFINER=`root`@`%` PROCEDURE `spGetShiftNew`(in start_stop nvarchar(200), in end_stop nvarchar(200), in dtime nvarchar(20))
387BEGIN
388 set @mysqlQuery=concat( 'SELECT shift.*, 0 as resultType,case when shift.type = 3 then COALESCE(45 - COALESCE(TotalTicket,0),0) else COALESCE(38 - COALESCE(TotalTicket,0),0) end as FreeSeat
389 FROM shift left outer join (select shift, count(id) as TotalTicket from reservation where date = ''',STR_TO_DATE(dtime,'%Y-%m-%d'),''' and ticketing is not null
390group by shift) as ticket on shift.id = ticket.shift
391 WHERE (',start_stop,') AND type=1
392 UNION
393 SELECT shift.*,1 as resultType,case when shift.type = 3 then COALESCE(45 - COALESCE(TotalTicket,0),0) else COALESCE(38 - COALESCE(TotalTicket,0),0) end as FreeSeat FROM shift shift2,shift_info
394 left outer join (select shift, count(id) as TotalTicket from reservation where date = ''',STR_TO_DATE(dtime,'%Y-%m-%d'),''' and ticketing is not null
395group by shift) as ticket on shift2.id = ticket.shift
396 WHERE shift_info.shift = shift.id AND shift_info.date=''',STR_TO_DATE(dtime,'%Y-%m-%d'),''' AND (',end_stop,') AND shift.type >= 2
397
398 UNION
399 SELECT shift.*,2 as resultType,case when shift.type = 3 then COALESCE(45 - COALESCE(TotalTicket,0),0) else COALESCE(38 - COALESCE(TotalTicket,0),0) end as FreeSeat FROM shift shift3,shift_info
400 left outer join (select shift, count(id) as TotalTicket from reservation where date = ''',STR_TO_DATE(dtime,'%Y-%m-%d'),''' and ticketing is not null
401group by shift) as ticket on shift3.id = ticket.shift
402WHERE shift_info.shift = shift.id AND shift_info.date=''',STR_TO_DATE(dtime,'%Y-%m-%d')-INTERVAL 1 DAY,''' AND (',end_stop,') AND shift.type >= 2
403
404 UNION
405 SELECT shift.*,3 as resultType,case when shift.type = 3 then COALESCE(45 - COALESCE(TotalTicket,0),0) else COALESCE(38 - COALESCE(TotalTicket,0),0) end as FreeSeat FROM shift shift4,shift_info
406 left outer join (select shift, count(id) as TotalTicket from reservation where date = ''',STR_TO_DATE(dtime,'%Y-%m-%d'),''' and ticketing is not null
407group by shift) as ticket on shift4.id = ticket.shift
408WHERE shift_info.shift = shift.id AND shift_info.date=''',STR_TO_DATE(dtime,'%Y-%m-%d')-INTERVAL 2 DAY,''' AND (',end_stop,') AND shift.type >= 2
409 ORDER BY start_time');
410 prepare stmt from @mysqlQuery;
411 execute stmt;
412 DEALLOCATE PREPARE stmt;
413
414END ;;
415DELIMITER ;
416/*!50003 SET sql_mode = @saved_sql_mode */ ;
417/*!50003 SET character_set_client = @saved_cs_client */ ;
418/*!50003 SET character_set_results = @saved_cs_results */ ;
419/*!50003 SET collation_connection = @saved_col_connection */ ;
420/*!50003 DROP PROCEDURE IF EXISTS `spGetShortShift` */;
421/*!50003 SET @saved_cs_client = @@character_set_client */ ;
422/*!50003 SET @saved_cs_results = @@character_set_results */ ;
423/*!50003 SET @saved_col_connection = @@collation_connection */ ;
424/*!50003 SET character_set_client = utf8 */ ;
425/*!50003 SET character_set_results = utf8 */ ;
426/*!50003 SET collation_connection = utf8_general_ci */ ;
427/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
428/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
429DELIMITER ;;
430CREATE DEFINER=`root`@`%` PROCEDURE `spGetShortShift`(in start_stop int, in end_stop int, in dtime nvarchar(20))
431BEGIN
432set @mysqlQuery=concat( 'SELECT id, start_time, type, Duration, Note, seat_num,departure, destination
433 FROM ss_shifts
434 WHERE (type=1 OR type=3) and active = 1 and departure = ',start_stop, ' and destination = ',end_stop,' AND router_id = (SELECT id from ss_routers WHERE departure=',start_stop, ' and destination = ',end_stop,' AND (isred is null or isred<>1))
435 UNION
436 SELECT ss_shifts.id, ss_shifts.start_time, ss_shifts.type, Duration, Note,seat_num, departure, destination FROM ss_shifts ,ss_shift_extra WHERE ss_shift_extra.shiftid = ss_shifts.id AND ss_shift_extra.date=''',STR_TO_DATE(dtime,'%Y-%m-%d'),''' AND ss_shifts.type >= 2 and active = 1
437 and ss_shifts.departure = ',start_stop, ' and ss_shifts.destination = ',end_stop,' AND ss_shifts.router_id = (SELECT id from ss_routers WHERE departure=',start_stop, ' and destination = ',end_stop,' AND (isred is null or isred<>1))
438
439 ORDER BY start_time');
440 prepare stmt from @mysqlQuery;
441 execute stmt;
442 DEALLOCATE PREPARE stmt;
443END ;;
444DELIMITER ;
445/*!50003 SET sql_mode = @saved_sql_mode */ ;
446/*!50003 SET character_set_client = @saved_cs_client */ ;
447/*!50003 SET character_set_results = @saved_cs_results */ ;
448/*!50003 SET collation_connection = @saved_col_connection */ ;
449/*!50003 DROP PROCEDURE IF EXISTS `spGetShortShift2` */;
450/*!50003 SET @saved_cs_client = @@character_set_client */ ;
451/*!50003 SET @saved_cs_results = @@character_set_results */ ;
452/*!50003 SET @saved_col_connection = @@collation_connection */ ;
453/*!50003 SET character_set_client = utf8 */ ;
454/*!50003 SET character_set_results = utf8 */ ;
455/*!50003 SET collation_connection = utf8_general_ci */ ;
456/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
457/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
458DELIMITER ;;
459CREATE DEFINER=`root`@`%` PROCEDURE `spGetShortShift2`(in dtime nvarchar(20),in route int)
460BEGIN
461set @mysqlQuery=concat( 'SELECT id, start_time, type, Duration, Note, seat_num,departure, destination
462 FROM ss_shifts
463 WHERE (type = 1 OR type = 3) and active = 1 AND router_id = ',route, '
464 UNION
465 SELECT ss_shifts.id, ss_shifts.start_time, ss_shifts.type, Duration, Note,seat_num, departure, destination FROM ss_shifts,ss_shift_extra WHERE ss_shift_extra.shiftid = ss_shifts.id AND ss_shift_extra.date=''',STR_TO_DATE(dtime,'%Y-%m-%d'),''' AND ss_shifts.type >= 2 and active = 1 AND ss_shifts.router_id = ',route,'
466
467 ORDER BY start_time' );
468 prepare stmt from @mysqlQuery;
469 execute stmt;
470 DEALLOCATE PREPARE stmt;
471END ;;
472DELIMITER ;
473/*!50003 SET sql_mode = @saved_sql_mode */ ;
474/*!50003 SET character_set_client = @saved_cs_client */ ;
475/*!50003 SET character_set_results = @saved_cs_results */ ;
476/*!50003 SET collation_connection = @saved_col_connection */ ;
477/*!50003 DROP PROCEDURE IF EXISTS `spGetShortShiftNew` */;
478/*!50003 SET @saved_cs_client = @@character_set_client */ ;
479/*!50003 SET @saved_cs_results = @@character_set_results */ ;
480/*!50003 SET @saved_col_connection = @@collation_connection */ ;
481/*!50003 SET character_set_client = utf8 */ ;
482/*!50003 SET character_set_results = utf8 */ ;
483/*!50003 SET collation_connection = utf8_general_ci */ ;
484/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
485/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
486DELIMITER ;;
487CREATE DEFINER=`root`@`%` PROCEDURE `spGetShortShiftNew`(in startdate varchar(20), in dep int(2),in des int(2),in routetype int(2))
488BEGIN
489set @mysqlQuery=concat( 'select shift.id as ShiftId,shift.type as ShiftType, start_time as StartTime,seat_num as SeatNum, case when ls.id is null then 0 else 1 end as locked,COALESCE(seat_num - COALESCE(TotalTicket,0),0) as FreeSeat from ss_shifts shift
490 left outer join (select shift, count(id) as TotalTicket from ss_reservations where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and status <> 4
491group by shift) as ticket on shift.id = ticket.shift
492 left join ss_lockshifts ls on shift.id = ls.ssShiftID and ls.Date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),'''
493 where (shift.type = 1 OR shift.type = 3) and active = 1 AND router_id = (SELECT id FROM ss_routers WHERE departure = ',dep,' and destination = ',des,' and routetype = ',routetype,')
494 UNION
495 SELECT shift.id as ShiftId,shift.type as ShiftType,start_time as StartTime,seat_num as SeatNum , 0 as locked,COALESCE(seat_num - COALESCE(TotalTicket,0),0) as FreeSeat from ss_shifts shift left outer join
496 (select shift, count(id) as TotalTicket from ss_reservations where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and status <> 4
497group by shift) as ticket on shift.id = ticket.shift
498inner join ss_routers rt ON shift.router_id = rt.id,ss_shift_extra WHERE ss_shift_extra.shiftid = shift.id AND ss_shift_extra.date=''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' AND shift.type = 2 and active = 1
499AND shift.router_id = (SELECT id FROM ss_routers WHERE departure = ',dep,' and destination = ',des,' and routetype = ',routetype,')
500ORDER BY StartTime');
501
502
503 prepare stmt from @mysqlQuery;
504 execute stmt;
505
506 DEALLOCATE PREPARE stmt;
507END ;;
508DELIMITER ;
509/*!50003 SET sql_mode = @saved_sql_mode */ ;
510/*!50003 SET character_set_client = @saved_cs_client */ ;
511/*!50003 SET character_set_results = @saved_cs_results */ ;
512/*!50003 SET collation_connection = @saved_col_connection */ ;
513/*!50003 DROP PROCEDURE IF EXISTS `spGetTransactionAPI` */;
514/*!50003 SET @saved_cs_client = @@character_set_client */ ;
515/*!50003 SET @saved_cs_results = @@character_set_results */ ;
516/*!50003 SET @saved_col_connection = @@collation_connection */ ;
517/*!50003 SET character_set_client = utf8 */ ;
518/*!50003 SET character_set_results = utf8 */ ;
519/*!50003 SET collation_connection = utf8_general_ci */ ;
520/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
521/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
522DELIMITER ;;
523CREATE DEFINER=`root`@`%` PROCEDURE `spGetTransactionAPI`()
524BEGIN
525-- Generate order ref number
526
527 SET @result = '';
528 SET @codeFound = 1;
529 SET @try = 0;
530
531 WHILE(@codeFound > 0) DO
532
533 SET @result = concat(
534 substring('OABCDEFGHIJKLMNPQRSTUVWXYZ1234567890', format(rand()*35,0)+1, 1),
535 substring('OABCDEFGHIJKLMNPQRSTUVWXYZ1234567890', format(rand()*35,0)+1, 1),
536 substring('OABCDEFGHIJKLMNPQRSTUVWXYZ1234567890', format(rand()*35,0)+1, 1),
537 substring('OABCDEFGHIJKLMNPQRSTUVWXYZ1234567890', format(rand()*35,0)+1, 1),
538 substring('OABCDEFGHIJKLMNPQRSTUVWXYZ1234567890', format(rand()*35,0)+1, 1),
539 substring('OABCDEFGHIJKLMNPQRSTUVWXYZ1234567890', format(rand()*35,0)+1, 1),
540 substring('OABCDEFGHIJKLMNPQRSTUVWXYZ1234567890', format(rand()*35,0)+1, 1),
541 substring('OABCDEFGHIJKLMNPQRSTUVWXYZ1234567890', format(rand()*35,0)+1, 1)
542 ) COLLATE utf8_unicode_ci;
543
544 -- Any of those table has this code already then we don't use this code and try something else
545 SET @codeFound = 0;
546 SET @codeFound = @codeFound + (SELECT COUNT(id) FROM api_reserve WHERE id = @result);
547 SET @try = @try + 1;
548 END WHILE;
549
550 SELECT @result;
551END ;;
552DELIMITER ;
553/*!50003 SET sql_mode = @saved_sql_mode */ ;
554/*!50003 SET character_set_client = @saved_cs_client */ ;
555/*!50003 SET character_set_results = @saved_cs_results */ ;
556/*!50003 SET collation_connection = @saved_col_connection */ ;
557/*!50003 DROP PROCEDURE IF EXISTS `spGetViewShift` */;
558/*!50003 SET @saved_cs_client = @@character_set_client */ ;
559/*!50003 SET @saved_cs_results = @@character_set_results */ ;
560/*!50003 SET @saved_col_connection = @@collation_connection */ ;
561/*!50003 SET character_set_client = utf8 */ ;
562/*!50003 SET character_set_results = utf8 */ ;
563/*!50003 SET collation_connection = utf8_general_ci */ ;
564/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
565/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
566DELIMITER ;;
567CREATE DEFINER=`root`@`%` PROCEDURE `spGetViewShift`(in date varchar(40),in shift int)
568BEGIN
569SELECT
570 res.id as id, res.checked as checked, res.shift as shift, res.user_check as kiemtra, res.date as date, seats.departure, seats.destination, seats.seat as seat, seats.first_name,
571 res.phone, res.agency as daily, res.serri as serri, res.information as information, res.ticketing, res.agent_ticketing, res.price AS total, res.discounts, res.eat,
572 seats.last_name, reservation,res.address, seats.price as price, seats.age,
573 dep.viet_name AS dep, des.viet_name AS des,
574 user.station AS agency, user.firstname AS agent,
575 additions.cargo as cargo, additions.cargo_price as cargo_price, additions.child as child_add,
576 additions.child_price as child_add_price, res.nocustomer,
577 pck.name AS pickPoint,drp.name AS dropPoint,seats.pickAdd,seats.dropAdd
578 FROM seats
579 LEFT JOIN stops AS dep ON seats.departure = dep.id
580 LEFT JOIN stops AS des ON seats.destination = des.id
581 LEFT JOIN reservation AS res ON seats.reservation = res.id
582 LEFT JOIN office_users AS user ON res.reservee = user.username
583 LEFT JOIN additions ON res.id = additions.resnum
584 LEFT JOIN tbl_droppoints pck ON seats.pickpoint = pck.id
585 LEFT JOIN tbl_droppoints drp ON seats.droppoint = drp.id
586 WHERE seats.shift = shift
587 AND seats.date = date
588 AND seats.seat IS NOT NULL
589 AND (seats.seat <> 0 OR (seats.seat = 0 AND (seats.age='adt' OR seats.age='child')
590 AND ticketing IS NOT NULL));
591END ;;
592DELIMITER ;
593/*!50003 SET sql_mode = @saved_sql_mode */ ;
594/*!50003 SET character_set_client = @saved_cs_client */ ;
595/*!50003 SET character_set_results = @saved_cs_results */ ;
596/*!50003 SET collation_connection = @saved_col_connection */ ;
597/*!50003 DROP PROCEDURE IF EXISTS `spLoadAllShift` */;
598/*!50003 SET @saved_cs_client = @@character_set_client */ ;
599/*!50003 SET @saved_cs_results = @@character_set_results */ ;
600/*!50003 SET @saved_col_connection = @@collation_connection */ ;
601/*!50003 SET character_set_client = utf8 */ ;
602/*!50003 SET character_set_results = utf8 */ ;
603/*!50003 SET collation_connection = utf8_general_ci */ ;
604/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
605/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
606DELIMITER ;;
607CREATE DEFINER=`root`@`%` PROCEDURE `spLoadAllShift`(in direction varchar(200))
608BEGIN
609set @mysqlQuery=concat( 'SELECT sh.type,sh.id as shid,sh.start_time, sh.price_2016_2 as price1,sh.price_2016_3 as price2,st1.viet_name as start,st2.viet_name as stop
610 FROM shift sh
611 LEFT JOIN stops st1 ON sh.start_city = st1.id
612 LEFT JOIN stops st2 ON sh.stop_city = st2.id
613 WHERE 1=1 ',direction,'
614 ORDER BY sh.type,sh.start_time;');
615
616 prepare stmt from @mysqlQuery;
617 execute stmt;
618 DEALLOCATE PREPARE stmt;
619
620END ;;
621DELIMITER ;
622/*!50003 SET sql_mode = @saved_sql_mode */ ;
623/*!50003 SET character_set_client = @saved_cs_client */ ;
624/*!50003 SET character_set_results = @saved_cs_results */ ;
625/*!50003 SET collation_connection = @saved_col_connection */ ;
626/*!50003 DROP PROCEDURE IF EXISTS `spLoadShiftLock` */;
627/*!50003 SET @saved_cs_client = @@character_set_client */ ;
628/*!50003 SET @saved_cs_results = @@character_set_results */ ;
629/*!50003 SET @saved_col_connection = @@collation_connection */ ;
630/*!50003 SET character_set_client = utf8 */ ;
631/*!50003 SET character_set_results = utf8 */ ;
632/*!50003 SET collation_connection = utf8_general_ci */ ;
633/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
634/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
635DELIMITER ;;
636CREATE DEFINER=`root`@`%` PROCEDURE `spLoadShiftLock`(in date varchar(20),in direction varchar(200))
637BEGIN
638set @mysqlQuery=concat( 'SELECT rp.shift,rp.date,sh.type,sh.id AS shid,sh.start_time, sh.price_2016_2 as price1,sh.price_2016_3 as price2,st1.viet_name AS start,st2.viet_name AS stop
639 FROM reports rp
640 LEFT JOIN shift sh ON rp.shift = sh.id
641 INNER JOIN stops st1 ON sh.start_city = st1.id
642 INNER JOIN stops st2 ON sh.stop_city = st2.id
643 WHERE rp.date = ''',STR_TO_DATE(date,'%Y-%m-%d'),''' ',direction,'
644 ORDER BY (sh.start_time);');
645
646 prepare stmt from @mysqlQuery;
647 execute stmt;
648 DEALLOCATE PREPARE stmt;
649
650END ;;
651DELIMITER ;
652/*!50003 SET sql_mode = @saved_sql_mode */ ;
653/*!50003 SET character_set_client = @saved_cs_client */ ;
654/*!50003 SET character_set_results = @saved_cs_results */ ;
655/*!50003 SET collation_connection = @saved_col_connection */ ;
656/*!50003 DROP PROCEDURE IF EXISTS `spLoadShiftNormal` */;
657/*!50003 SET @saved_cs_client = @@character_set_client */ ;
658/*!50003 SET @saved_cs_results = @@character_set_results */ ;
659/*!50003 SET @saved_col_connection = @@collation_connection */ ;
660/*!50003 SET character_set_client = utf8 */ ;
661/*!50003 SET character_set_results = utf8 */ ;
662/*!50003 SET collation_connection = utf8_general_ci */ ;
663/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
664/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
665DELIMITER ;;
666CREATE DEFINER=`root`@`%` PROCEDURE `spLoadShiftNormal`(in date varchar(20),in direction varchar(200))
667BEGIN
668set @mysqlQuery=concat( 'SELECT sh.type as type,sh.id as shid,sh.start_time, sh.price_2016_2 as price1,sh.price_2016_3 as price2,st1.viet_name as start,st2.viet_name stop
669 FROM shift sh
670 LEFT JOIN stops st1 ON sh.start_city = st1.id
671 LEFT JOIN stops st2 ON sh.stop_city = st2.id
672 LEFT JOIN reports rp ON (sh.id = rp.shift AND rp.date = ''',STR_TO_DATE(date,'%Y-%m-%d'),''')
673 WHERE sh.type = 1 AND ISNULL(rp.date)
674 ',direction,'
675 -- ORDER BY (sh.start_time)
676 UNION
677 SELECT sh.type as type,sh.id as shid,sh.start_time, sh.price_2016_2 as price1,sh.price_2016_3 as price2,st1.viet_name as start,st2.viet_name as stop
678 FROM shift sh
679 LEFT JOIN stops st1 ON sh.start_city = st1.id
680 LEFT JOIN stops st2 ON sh.stop_city = st2.id
681 LEFT JOIN shift_info shi ON (sh.id = shi.shift AND shi.date = ''',STR_TO_DATE(date,'%Y-%m-%d'),''')
682 LEFT JOIN reports rp ON (sh.id = rp.shift AND rp.date = ''',STR_TO_DATE(date,'%Y-%m-%d'),''')
683 WHERE (sh.type = 2 OR sh.type = 3 OR sh.type = 4) AND shi.ischeck = 1 AND ISNULL(rp.date) AND ISNULL(rp.shift) ',direction,'
684 ORDER BY (start_time);');
685
686 prepare stmt from @mysqlQuery;
687 execute stmt;
688 DEALLOCATE PREPARE stmt;
689
690END ;;
691DELIMITER ;
692/*!50003 SET sql_mode = @saved_sql_mode */ ;
693/*!50003 SET character_set_client = @saved_cs_client */ ;
694/*!50003 SET character_set_results = @saved_cs_results */ ;
695/*!50003 SET collation_connection = @saved_col_connection */ ;
696/*!50003 DROP PROCEDURE IF EXISTS `spMirageReport` */;
697/*!50003 SET @saved_cs_client = @@character_set_client */ ;
698/*!50003 SET @saved_cs_results = @@character_set_results */ ;
699/*!50003 SET @saved_col_connection = @@collation_connection */ ;
700/*!50003 SET character_set_client = utf8 */ ;
701/*!50003 SET character_set_results = utf8 */ ;
702/*!50003 SET collation_connection = utf8_general_ci */ ;
703/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
704/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
705DELIMITER ;;
706CREATE DEFINER=`root`@`%` PROCEDURE `spMirageReport`(in startdate varchar(20),in enddate varchar(20))
707BEGIN
708select o.OperationId,o.Date,o.Time,o.TotalAmount,o.TypeAgentName,o.AgentName,
709o.DeviceId,o.DeviceName,o.VehicleName,o.AgentDuty,o.VehicleDutyName,o.NetworkName,o.RouteName,o.RouteFromName,
710o.RouteToName,o.TripName,o.TripFromTime,o.TripToTime,o.PointName,o.AgencyName,s.ProductName,s.ProductType,s.CUID,
711s.QtySold,s.QtyCancelled,s.Amount,s.EndPointName from mirage_operation o join mirage_selling s on o.Id = s.OperationId
712left join mirage_payment p on o.Id = p.OperationId
713where o.Date between STR_TO_DATE(startdate,'%Y-%m-%d') and STR_TO_DATE(enddate,'%Y-%m-%d')
714order by o.Id desc;
715END ;;
716DELIMITER ;
717/*!50003 SET sql_mode = @saved_sql_mode */ ;
718/*!50003 SET character_set_client = @saved_cs_client */ ;
719/*!50003 SET character_set_results = @saved_cs_results */ ;
720/*!50003 SET collation_connection = @saved_col_connection */ ;
721/*!50003 DROP PROCEDURE IF EXISTS `spMirageReportByDay` */;
722/*!50003 SET @saved_cs_client = @@character_set_client */ ;
723/*!50003 SET @saved_cs_results = @@character_set_results */ ;
724/*!50003 SET @saved_col_connection = @@collation_connection */ ;
725/*!50003 SET character_set_client = utf8 */ ;
726/*!50003 SET character_set_results = utf8 */ ;
727/*!50003 SET collation_connection = utf8_general_ci */ ;
728/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
729/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
730DELIMITER ;;
731CREATE DEFINER=`root`@`%` PROCEDURE `spMirageReportByDay`(in startdate varchar(20),in enddate varchar(20))
732BEGIN
733SELECT o.Id, o.OperationId,o.RouteName,o.TripId,o.TripName,o.Date,o.DeviceName,o.VehicleName,o.AgentDuty,o.AgentName,COALESCE(ticket.TotalTicket,0) as TotalTicket,COALESCE(card.TotalCard,0) as TotalCard,COALESCE(cb.TotalCB,0) as TotalCB,
734COALESCE(cargo.TotalCargo,0) as TotalCargo,COALESCE(amount.TotalValueTicket,0) as TotalValueTicket,COALESCE(paymentcard.TotalValueCard,0) as TotalValueCard,COALESCE(charge.TotalCharge,0) as TotalCharge,
735COALESCE(amountcargo.TotalValueCargo,0) as TotalValueCargo
736 FROM hlong_eticket.mirage_operation o
737 left outer join (select COALESCE(COUNT(id),0) as TotalTicket,OperationId FROM mirage_selling WHERE ProductId = 1 GROUP BY OperationId) as ticket on o.Id = ticket.operationId
738 left outer join (select COALESCE(COUNT(id),0) as TotalCargo,OperationId FROM mirage_selling WHERE ProductId = 5 GROUP BY OperationId) as cargo on o.Id = cargo.operationId
739 left outer join (select COALESCE(COUNT(id),0) as TotalCard,OperationId FROM mirage_selling WHERE ProductId = 4 GROUP BY OperationId) as card on o.Id = card.operationId
740 left outer join (select COALESCE(COUNT(id),0) as TotalCB,OperationId FROM mirage_selling WHERE ProductId = 6 GROUP BY OperationId) as cb on o.Id = cb.operationId
741 left outer join (select COALESCE(SUM(amount),0) as TotalValueTicket,OperationId FROM mirage_selling WHERE ProductId = 1 GROUP BY OperationId) as amount on o.Id = amount.operationId
742 left outer join (select COALESCE(SUM(amount),0) as TotalValueCard,OperationId FROM mirage_selling WHERE ProductId = 4 GROUP BY OperationId) as paymentcard on o.Id = paymentcard.operationId
743 left outer join (select COALESCE(SUM(amount),0) as TotalCharge,OperationId FROM mirage_selling WHERE ProductId = 3 GROUP BY OperationId) as charge on o.Id = charge.operationId
744 left outer join (select COALESCE(SUM(amount),0) as TotalValueCargo,OperationId FROM mirage_selling WHERE ProductId = 5 GROUP BY OperationId) as amountcargo on o.Id = amountcargo.operationId
745 where o.Date between STR_TO_DATE(startdate,'%Y-%m-%d') and STR_TO_DATE(enddate,'%Y-%m-%d')
746 order by o.Date;
747END ;;
748DELIMITER ;
749/*!50003 SET sql_mode = @saved_sql_mode */ ;
750/*!50003 SET character_set_client = @saved_cs_client */ ;
751/*!50003 SET character_set_results = @saved_cs_results */ ;
752/*!50003 SET collation_connection = @saved_col_connection */ ;
753/*!50003 DROP PROCEDURE IF EXISTS `spMirageReportByShift` */;
754/*!50003 SET @saved_cs_client = @@character_set_client */ ;
755/*!50003 SET @saved_cs_results = @@character_set_results */ ;
756/*!50003 SET @saved_col_connection = @@collation_connection */ ;
757/*!50003 SET character_set_client = utf8 */ ;
758/*!50003 SET character_set_results = utf8 */ ;
759/*!50003 SET collation_connection = utf8_general_ci */ ;
760/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
761/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
762DELIMITER ;;
763CREATE DEFINER=`root`@`%` PROCEDURE `spMirageReportByShift`(in ShiftId int,in VehicleId int,in AgentId int,in startdate varchar(20),in enddate varchar(20))
764BEGIN
765-- neu co filter theo shift, thi gan filter vao
766set @shiftwhere =concat(' and o.TripId = ',ShiftId,' ');
767
768-- neu co filter theo Vehicle, thi gan filter vao
769set @vehiclewhere =concat(' and o.VehicleId = ',VehicleId,' ');
770
771-- neu co filter theo AgentId, thi gan filter vao
772set @agentwhere =concat(' and o.AgentId = ',AgentId,' ');
773
774set @wherecondition = if(ShiftId = 0,'', @shiftwhere);
775
776set @wherecondition = if(VehicleId = 0,@wherecondition, concat(@wherecondition, @vehiclewhere));
777
778set @wherecondition = if(AgentId = 0,@wherecondition, concat(@wherecondition, @agentwhere));
779
780set @mysqlQuery = concat('SELECT SellingId,o.OperationId,ProductId,CUID,Amount,o.RouteName,o.TripId,o.TripName,o.Date,o.Time,o.DeviceId,o.DeviceName,o.AgentDuty,o.VehicleId,o.VehicleName,
781o.AgentName,o.CompanyId,o.CompanyName,o.PointId,o.PointName,o.RouteFromId,o.RouteFromName,o.RouteToId,o.RouteToName
782 FROM hlong_eticket.mirage_selling s join mirage_operation o on s.OperationId = o.Id WHERE o.Date between ''',startdate,''' AND ''',enddate,''' ',@wherecondition,'
783 ');
784
785
786prepare stmt from @mysqlQuery;
787execute stmt;
788deallocate prepare stmt;
789END ;;
790DELIMITER ;
791/*!50003 SET sql_mode = @saved_sql_mode */ ;
792/*!50003 SET character_set_client = @saved_cs_client */ ;
793/*!50003 SET character_set_results = @saved_cs_results */ ;
794/*!50003 SET collation_connection = @saved_col_connection */ ;
795/*!50003 DROP PROCEDURE IF EXISTS `spMirageReportCard` */;
796/*!50003 SET @saved_cs_client = @@character_set_client */ ;
797/*!50003 SET @saved_cs_results = @@character_set_results */ ;
798/*!50003 SET @saved_col_connection = @@collation_connection */ ;
799/*!50003 SET character_set_client = utf8 */ ;
800/*!50003 SET character_set_results = utf8 */ ;
801/*!50003 SET collation_connection = utf8_general_ci */ ;
802/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
803/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
804DELIMITER ;;
805CREATE DEFINER=`root`@`%` PROCEDURE `spMirageReportCard`(in startdate varchar(20),in enddate varchar(20))
806BEGIN
807SELECT s.CUID,SellingId,o.Date,o.Time,o.PointId,o.PointName,o.DeviceId,o.DeviceName,s.Amount,o.AgentId,
808o.AgentName,o.CompanyId,o.CompanyName,o.AgentDuty FROM hlong_eticket.mirage_selling s join mirage_operation o on s.OperationId = o.Id
809where s.ProductId = 3 AND o.Date between @startdate and @enddate;
810END ;;
811DELIMITER ;
812/*!50003 SET sql_mode = @saved_sql_mode */ ;
813/*!50003 SET character_set_client = @saved_cs_client */ ;
814/*!50003 SET character_set_results = @saved_cs_results */ ;
815/*!50003 SET collation_connection = @saved_col_connection */ ;
816/*!50003 DROP PROCEDURE IF EXISTS `spNewStops` */;
817/*!50003 SET @saved_cs_client = @@character_set_client */ ;
818/*!50003 SET @saved_cs_results = @@character_set_results */ ;
819/*!50003 SET @saved_col_connection = @@collation_connection */ ;
820/*!50003 SET character_set_client = utf8 */ ;
821/*!50003 SET character_set_results = utf8 */ ;
822/*!50003 SET collation_connection = utf8_general_ci */ ;
823/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
824/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
825DELIMITER ;;
826CREATE DEFINER=`root`@`%` PROCEDURE `spNewStops`(IN city varchar(100),
827 IN station varchar(100),
828 IN co nvarchar(200),
829 IN viet varchar(300),
830 IN id int)
831INSERT INTO `hlong_eticket`.`stops`(`city`,
832 `station`,
833 `coordinates`,
834 `viet_name`,
835 id)
836 VALUES (city,
837 station,
838 co,
839 viet,
840 id) ;;
841DELIMITER ;
842/*!50003 SET sql_mode = @saved_sql_mode */ ;
843/*!50003 SET character_set_client = @saved_cs_client */ ;
844/*!50003 SET character_set_results = @saved_cs_results */ ;
845/*!50003 SET collation_connection = @saved_col_connection */ ;
846/*!50003 DROP PROCEDURE IF EXISTS `spResnum` */;
847/*!50003 SET @saved_cs_client = @@character_set_client */ ;
848/*!50003 SET @saved_cs_results = @@character_set_results */ ;
849/*!50003 SET @saved_col_connection = @@collation_connection */ ;
850/*!50003 SET character_set_client = utf8 */ ;
851/*!50003 SET character_set_results = utf8 */ ;
852/*!50003 SET collation_connection = utf8_general_ci */ ;
853/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
854/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
855DELIMITER ;;
856CREATE DEFINER=`root`@`%` PROCEDURE `spResnum`()
857BEGIN
858-- Generate order ref number
859
860 SET @result = '';
861 SET @codeFound = 1;
862 SET @try = 0;
863
864 WHILE(@codeFound > 0) DO
865
866 SET @result = concat(
867 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
868 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
869 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
870 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
871 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
872 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1)
873 ) COLLATE utf8_unicode_ci;
874
875 -- Any of those table has this code already then we don't use this code and try something else
876 SET @codeFound = 0;
877 SET @codeFound = @codeFound + (SELECT COUNT(id) FROM reservation WHERE id = @result);
878 SET @codeFound = @codeFound + (SELECT COUNT(resnum) as id FROM refund WHERE resnum = @result);
879 SET @codeFound = @codeFound + (SELECT COUNT(resnum) as id FROM refund_30 WHERE resnum = @result);
880 SET @codeFound = @codeFound + (SELECT COUNT(id) FROM reservation_backup WHERE id = @result);
881 SET @codeFound = @codeFound + (SELECT COUNT(id) FROM tax_haiphong WHERE id = @result);
882 SET @codeFound = @codeFound + (SELECT COUNT(id) FROM tax_hochiminh_2014 WHERE id = @result);
883 SET @try = @try + 1;
884 END WHILE;
885
886 SELECT @result;
887END ;;
888DELIMITER ;
889/*!50003 SET sql_mode = @saved_sql_mode */ ;
890/*!50003 SET character_set_client = @saved_cs_client */ ;
891/*!50003 SET character_set_results = @saved_cs_results */ ;
892/*!50003 SET collation_connection = @saved_col_connection */ ;
893/*!50003 DROP PROCEDURE IF EXISTS `spResnum2` */;
894/*!50003 SET @saved_cs_client = @@character_set_client */ ;
895/*!50003 SET @saved_cs_results = @@character_set_results */ ;
896/*!50003 SET @saved_col_connection = @@collation_connection */ ;
897/*!50003 SET character_set_client = utf8 */ ;
898/*!50003 SET character_set_results = utf8 */ ;
899/*!50003 SET collation_connection = utf8_general_ci */ ;
900/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
901/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
902DELIMITER ;;
903CREATE DEFINER=`root`@`%` PROCEDURE `spResnum2`()
904BEGIN
905-- Generate order ref number
906
907 SET @result = '';
908 SET @codeFound = 1;
909 SET @try = 0;
910
911 WHILE(@codeFound > 0) DO
912
913 SET @result = concat(
914 substring('OABCDEFGHIJKLMNPQRSTUVWXYZ1234567890', format(rand()*35,0)+1, 1),
915 substring('OABCDEFGHIJKLMNPQRSTUVWXYZ1234567890', format(rand()*35,0)+1, 1),
916 substring('OABCDEFGHIJKLMNPQRSTUVWXYZ1234567890', format(rand()*35,0)+1, 1),
917 substring('OABCDEFGHIJKLMNPQRSTUVWXYZ1234567890', format(rand()*35,0)+1, 1),
918 substring('OABCDEFGHIJKLMNPQRSTUVWXYZ1234567890', format(rand()*35,0)+1, 1),
919 substring('OABCDEFGHIJKLMNPQRSTUVWXYZ1234567890', format(rand()*35,0)+1, 1)
920 ) COLLATE utf8_unicode_ci;
921
922 -- Any of those table has this code already then we don't use this code and try something else
923 SET @codeFound = 0;
924 SET @codeFound = @codeFound + (SELECT COUNT(id) FROM reservation WHERE id = @result);
925 SET @codeFound = @codeFound + (SELECT COUNT(resnum) as id FROM refund WHERE resnum = @result);
926 SET @codeFound = @codeFound + (SELECT COUNT(resnum) as id FROM refund_30 WHERE resnum = @result);
927 SET @codeFound = @codeFound + (SELECT COUNT(id) FROM reservation_backup WHERE id = @result);
928 SET @codeFound = @codeFound + (SELECT COUNT(id) FROM tax_haiphong WHERE id = @result);
929 SET @codeFound = @codeFound + (SELECT COUNT(id) FROM tax_hochiminh_2014 WHERE id = @result);
930 SET @try = @try + 1;
931 END WHILE;
932
933 SELECT @result;
934END ;;
935DELIMITER ;
936/*!50003 SET sql_mode = @saved_sql_mode */ ;
937/*!50003 SET character_set_client = @saved_cs_client */ ;
938/*!50003 SET character_set_results = @saved_cs_results */ ;
939/*!50003 SET collation_connection = @saved_col_connection */ ;
940/*!50003 DROP PROCEDURE IF EXISTS `sp_CbGetListCustomers` */;
941/*!50003 SET @saved_cs_client = @@character_set_client */ ;
942/*!50003 SET @saved_cs_results = @@character_set_results */ ;
943/*!50003 SET @saved_col_connection = @@collation_connection */ ;
944/*!50003 SET character_set_client = utf8 */ ;
945/*!50003 SET character_set_results = utf8 */ ;
946/*!50003 SET collation_connection = utf8_general_ci */ ;
947/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
948/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
949DELIMITER ;;
950CREATE DEFINER=`root`@`%` PROCEDURE `sp_CbGetListCustomers`(IN groups INT, IN date varchar(20))
951BEGIN
952 SET @mysqlQuery=concat('SELECT res.id,res.seat, res.fullname, res.ticketing, res.user_check,
953 res.child, res.age_type, res.phone, res.shift, res.date, res.price, shi.start_time, st1.vietnam as dep,
954 st2.vietnam as des, res.contractNum,res.yearbirth, res.gender,res.address,res.national
955 FROM cb_reservations res
956 INNER JOIN cb_shifts shi ON res.shift = shi.id
957 INNER JOIN cb_routers rou ON shi.router_id = rou.id
958 INNER JOIN cb_stops st1 ON rou.departure = st1.id
959 INNER JOIN cb_stops st2 ON rou.destination = st2.id
960 WHERE res.deleted = 0 AND shi.groups = ', groups, ' AND date =''', STR_TO_DATE(date,'%Y-%m-%d'), '''
961 AND res.ticketing IS NOT NULL
962 ORDER BY id DESC');
963 prepare stmt from @mysqlQuery;
964 execute stmt;
965 DEALLOCATE PREPARE stmt;
966END ;;
967DELIMITER ;
968/*!50003 SET sql_mode = @saved_sql_mode */ ;
969/*!50003 SET character_set_client = @saved_cs_client */ ;
970/*!50003 SET character_set_results = @saved_cs_results */ ;
971/*!50003 SET collation_connection = @saved_col_connection */ ;
972/*!50003 DROP PROCEDURE IF EXISTS `sp_CbGetReservations` */;
973/*!50003 SET @saved_cs_client = @@character_set_client */ ;
974/*!50003 SET @saved_cs_results = @@character_set_results */ ;
975/*!50003 SET @saved_col_connection = @@collation_connection */ ;
976/*!50003 SET character_set_client = utf8 */ ;
977/*!50003 SET character_set_results = utf8 */ ;
978/*!50003 SET collation_connection = utf8_general_ci */ ;
979/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
980/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
981DELIMITER ;;
982CREATE DEFINER=`root`@`%` PROCEDURE `sp_CbGetReservations`(IN shift INT, IN date varchar(20))
983BEGIN
984 SET @mysqlQuery=concat('SELECT res.id,res.seat, res.fullname, res.age_type, res.nocustomer, res.reservee,
985 res.print, res.ticketing, res.child, res.phone, res.shift, res.date,
986 res.price, shi.start_time, st1.vietnam as dep, st2.vietnam as des,
987 res.contractNum,res.email,res.taxi_price,gr.groupname,res.ssticketid,
988 ss.ordernumber as limoCode, ss.price as limoPrice, ss.limousineChangeShift,res.user_check,
989 se.seat as limoSeat, ssshi.start_time as limoStartTime, sto.viet_name AS limoDeparture, sto1.viet_name AS limoDestination
990 FROM cb_reservations res
991 INNER JOIN cb_shifts shi ON res.shift = shi.id
992 INNER JOIN cb_groups gr ON shi.groups = gr.id
993 INNER JOIN cb_routers rou ON shi.router_id = rou.id
994 INNER JOIN cb_stops st1 ON rou.departure = st1.id
995 INNER JOIN cb_stops st2 ON rou.destination = st2.id
996 LEFT JOIN ss_reservations ss ON ss.id = res.ssticketid
997 LEFT JOIN ss_seats se ON se.reservation = ss.id
998 LEFT JOIN ss_shifts ssshi ON ssshi.id = ss.shift
999 LEFT JOIN stops sto ON sto.id = ssshi.departure
1000 LEFT JOIN stops sto1 ON sto1.id = ssshi.destination
1001 WHERE (res.deleted IS NULL OR res.deleted = 0) AND res.shift = ', shift, ' AND res.date =''', STR_TO_DATE(date,'%Y-%m-%d'), '''
1002 ORDER BY id DESC');
1003 prepare stmt from @mysqlQuery;
1004 execute stmt;
1005 DEALLOCATE PREPARE stmt;
1006END ;;
1007DELIMITER ;
1008/*!50003 SET sql_mode = @saved_sql_mode */ ;
1009/*!50003 SET character_set_client = @saved_cs_client */ ;
1010/*!50003 SET character_set_results = @saved_cs_results */ ;
1011/*!50003 SET collation_connection = @saved_col_connection */ ;
1012/*!50003 DROP PROCEDURE IF EXISTS `sp_CbGetReservationsByGroup` */;
1013/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1014/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1015/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1016/*!50003 SET character_set_client = utf8 */ ;
1017/*!50003 SET character_set_results = utf8 */ ;
1018/*!50003 SET collation_connection = utf8_general_ci */ ;
1019/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1020/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1021DELIMITER ;;
1022CREATE DEFINER=`root`@`%` PROCEDURE `sp_CbGetReservationsByGroup`(IN groups INT, IN date varchar(20))
1023BEGIN
1024 SET @mysqlQuery=concat('SELECT res.id,res.seat, res.fullname, res.age_type, res.nocustomer, res.reservee,
1025 res.print, res.ticketing, res.child, res.phone, res.shift, res.date,
1026 res.price, shi.start_time, st1.vietnam as dep, st2.vietnam as des,
1027 res.contractNum,res.email,res.taxi_price,gr.groupname,res.ssticketid,res.user_check,
1028 ss.ordernumber as limoCode, ss.price as limoPrice, ss.limousineChangeShift,
1029 se.seat as limoSeat, ssshi.start_time as limoStartTime, sto.viet_name AS limoDeparture, sto1.viet_name AS limoDestination
1030 FROM cb_reservations res
1031 INNER JOIN cb_shifts shi ON res.shift = shi.id
1032 INNER JOIN cb_groups gr ON shi.groups = gr.id
1033 INNER JOIN cb_routers rou ON shi.router_id = rou.id
1034 INNER JOIN cb_stops st1 ON rou.departure = st1.id
1035 INNER JOIN cb_stops st2 ON rou.destination = st2.id
1036 LEFT JOIN ss_reservations ss ON ss.id = res.ssticketid
1037 LEFT JOIN ss_seats se ON se.reservation = ss.id
1038 LEFT JOIN ss_shifts ssshi ON ssshi.id = ss.shift
1039 LEFT JOIN stops sto ON sto.id = ssshi.departure
1040 LEFT JOIN stops sto1 ON sto1.id = ssshi.destination
1041 WHERE (res.deleted IS NULL OR res.deleted = 0) AND shi.groups = ', groups, ' AND res.date =''', STR_TO_DATE(date,'%Y-%m-%d'), '''
1042 ORDER BY id DESC');
1043 prepare stmt from @mysqlQuery;
1044 execute stmt;
1045 DEALLOCATE PREPARE stmt;
1046END ;;
1047DELIMITER ;
1048/*!50003 SET sql_mode = @saved_sql_mode */ ;
1049/*!50003 SET character_set_client = @saved_cs_client */ ;
1050/*!50003 SET character_set_results = @saved_cs_results */ ;
1051/*!50003 SET collation_connection = @saved_col_connection */ ;
1052/*!50003 DROP PROCEDURE IF EXISTS `sp_CbGetShiftRouter` */;
1053/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1054/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1055/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1056/*!50003 SET character_set_client = utf8 */ ;
1057/*!50003 SET character_set_results = utf8 */ ;
1058/*!50003 SET collation_connection = utf8_general_ci */ ;
1059/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1060/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1061DELIMITER ;;
1062CREATE DEFINER=`root`@`%` PROCEDURE `sp_CbGetShiftRouter`(IN route INT)
1063BEGIN
1064 SET @mysqlQuery=concat('SELECT sh.id, sh.start_time, sh.groups,gr.groupname
1065 FROM cb_shifts sh LEFT JOIN cb_groups gr ON sh.groups = gr.id
1066 WHERE sh.router_id = ',route,'
1067 ORDER BY sh.start_time ASC');
1068 prepare stmt from @mysqlQuery;
1069 execute stmt;
1070 DEALLOCATE PREPARE stmt;
1071END ;;
1072DELIMITER ;
1073/*!50003 SET sql_mode = @saved_sql_mode */ ;
1074/*!50003 SET character_set_client = @saved_cs_client */ ;
1075/*!50003 SET character_set_results = @saved_cs_results */ ;
1076/*!50003 SET collation_connection = @saved_col_connection */ ;
1077/*!50003 DROP PROCEDURE IF EXISTS `sp_GetCbReport` */;
1078/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1079/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1080/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1081/*!50003 SET character_set_client = utf8 */ ;
1082/*!50003 SET character_set_results = utf8 */ ;
1083/*!50003 SET collation_connection = utf8_general_ci */ ;
1084/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1085/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1086DELIMITER ;;
1087CREATE DEFINER=`root`@`%` PROCEDURE `sp_GetCbReport`(IN date varchar(20))
1088BEGIN
1089 SET @mysqlQuery=concat('SELECT
1090 count(case when res.ticketing <> '''' and ticket_type <> 2 then ''Äã Xuất'' end )as exported,
1091 count(case when res.ticketing <> '''' and ticket_type = 2 then ''Vé HÄ'' end )as unexport,
1092 count(case when res.ticketing <> '''' and ticket_type <> 2 then ''Äã Xuất'' end ) + count(case when res.ticketing <> '''' and ticket_type = 2 then ''Vé HÄ'' end ) as sum,
1093 res.date, sh.start_time, CONCAT(st1.vietnam, '' - '', st2.vietnam) AS shift, sh.id, gr.groupname
1094 FROM cb_reservations res
1095 INNER JOIN cb_shifts sh ON res.shift = sh.id
1096 INNER JOIN cb_groups gr ON sh.groups = gr.id
1097 INNER JOIN cb_routers rt ON sh.router_id = rt.id
1098 INNER JOIN cb_stops st1 ON rt.departure = st1.id
1099 INNER JOIN cb_stops st2 ON rt.destination = st2.id where res.date = ''', STR_TO_DATE(date,'%Y-%m-%d'), ''' and res.deleted = 0
1100 GROUP BY sh.id, res.date, sh.start_time, CONCAT(st1.vietnam, ''-'', st2.vietnam)
1101 having count(case when res.ticketing <> '''' and ticket_type <> 2 then ''Äã Xuất'' end) > 0
1102 or count(case when res.ticketing <> '''' and ticket_type = 2 then ''Äã Xuất'' end) > 0
1103 ORDER BY sh.groups');
1104 prepare stmt from @mysqlQuery;
1105 execute stmt;
1106 DEALLOCATE PREPARE stmt;
1107END ;;
1108DELIMITER ;
1109/*!50003 SET sql_mode = @saved_sql_mode */ ;
1110/*!50003 SET character_set_client = @saved_cs_client */ ;
1111/*!50003 SET character_set_results = @saved_cs_results */ ;
1112/*!50003 SET collation_connection = @saved_col_connection */ ;
1113/*!50003 DROP PROCEDURE IF EXISTS `sp_GetMenuByUser` */;
1114/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1115/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1116/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1117/*!50003 SET character_set_client = utf8 */ ;
1118/*!50003 SET character_set_results = utf8 */ ;
1119/*!50003 SET collation_connection = utf8_general_ci */ ;
1120/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1121/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1122DELIMITER ;;
1123CREATE DEFINER=`root`@`%` PROCEDURE `sp_GetMenuByUser`( in user_ID int)
1124BEGIN
1125 select u.id as UserId,p.MenuId,Name, Image,Position,Link,Description,ParentId,Archive, 1 as fromStation from office_Users u inner join stations s on u.station_id = s.station_id
1126 inner join expandomenustation p on s.station_id=p.StationId
1127 inner join expandomenu menu on p.MenuId=menu.Id
1128 where u.id = user_ID
1129 UNION
1130 select u.id as UserId,p.MenuId,Name, Image,Position,Link,Description,ParentId,Archive, 0 as fromStation from office_Users u
1131 inner join expandomenuusers p on u.id =p.userid
1132 inner join expandomenu menu on p.MenuId=menu.Id
1133 where u.id = user_ID ;
1134
1135END ;;
1136DELIMITER ;
1137/*!50003 SET sql_mode = @saved_sql_mode */ ;
1138/*!50003 SET character_set_client = @saved_cs_client */ ;
1139/*!50003 SET character_set_results = @saved_cs_results */ ;
1140/*!50003 SET collation_connection = @saved_col_connection */ ;
1141/*!50003 DROP PROCEDURE IF EXISTS `sp_GetOnlineTickets` */;
1142/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1143/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1144/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1145/*!50003 SET character_set_client = utf8 */ ;
1146/*!50003 SET character_set_results = utf8 */ ;
1147/*!50003 SET collation_connection = utf8_general_ci */ ;
1148/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1149/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1150DELIMITER ;;
1151CREATE DEFINER=`root`@`%` PROCEDURE `sp_GetOnlineTickets`()
1152BEGIN
1153SELECT * FROM reservation WHERE (reservee ='online' OR reservee IS NULL) AND ticketing IS NULL AND date >= NOW() ORDER BY date ASC;
1154END ;;
1155DELIMITER ;
1156/*!50003 SET sql_mode = @saved_sql_mode */ ;
1157/*!50003 SET character_set_client = @saved_cs_client */ ;
1158/*!50003 SET character_set_results = @saved_cs_results */ ;
1159/*!50003 SET collation_connection = @saved_col_connection */ ;
1160/*!50003 DROP PROCEDURE IF EXISTS `sp_GetPriceHcm` */;
1161/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1162/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1163/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1164/*!50003 SET character_set_client = utf8 */ ;
1165/*!50003 SET character_set_results = utf8 */ ;
1166/*!50003 SET collation_connection = utf8_general_ci */ ;
1167/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1168/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1169DELIMITER ;;
1170CREATE DEFINER=`root`@`%` PROCEDURE `sp_GetPriceHcm`(in startdate datetime, in departure int(2))
1171BEGIN
1172
1173set @price = if(startdate >'2017-01-01', 'price_2017_khuyenmai', 'price_2017_01_01');
1174set @priceeat = if(startdate >'2017-01-01', 'price_2017_khuyenmai_eat', 'price_2017_01_01_eat');
1175
1176set @mysqlQuery=concat('SELECT
1177 `cn`.`departure` AS `departure`,
1178 `cn`.`conn_id` AS `conn_id`,
1179 `st1`.`viet_name` AS `dep`,
1180 `st1`.`city` AS `depEng`,
1181 `cn`.`duration` AS `duration`,
1182 `cn`.`destination` AS `destination`,
1183 `st2`.`viet_name` AS `des`,
1184 `st2`.`city` AS `desEng`,
1185 `cn`.`',@price,'` AS `price`,
1186 `cn`.`',@priceeat,'` AS `price_eat`,
1187 `cn`.`price_ghe_02072011` AS `price_ghe`
1188 FROM
1189 ((`connections_240110` `cn`
1190 JOIN `stops` `st1` ON ((`st1`.`id` = `cn`.`departure`)))
1191 JOIN `stops` `st2` ON ((`st2`.`id` = `cn`.`destination`)))
1192 where st1.id=',departure,'
1193 ORDER BY `cn`.`departure` , `cn`.`destination`');
1194
1195PREPARE stmt FROM @mysqlQuery;
1196EXECUTE stmt;
1197DEALLOCATE PREPARE stmt;
1198
1199END ;;
1200DELIMITER ;
1201/*!50003 SET sql_mode = @saved_sql_mode */ ;
1202/*!50003 SET character_set_client = @saved_cs_client */ ;
1203/*!50003 SET character_set_results = @saved_cs_results */ ;
1204/*!50003 SET collation_connection = @saved_col_connection */ ;
1205/*!50003 DROP PROCEDURE IF EXISTS `sp_GetSeatBooking` */;
1206/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1207/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1208/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1209/*!50003 SET character_set_client = utf8 */ ;
1210/*!50003 SET character_set_results = utf8 */ ;
1211/*!50003 SET collation_connection = utf8_general_ci */ ;
1212/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1213/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1214DELIMITER ;;
1215CREATE DEFINER=`root`@`%` PROCEDURE `sp_GetSeatBooking`(in date datetime,in shift int,in seat int)
1216BEGIN
1217SELECT s.departure,s.destination,drp.location AS pickLocation,drp1.location AS dropLocation
1218 FROM seats s
1219 LEFT JOIN tbl_droppoints drp ON s.pickpoint = drp.id
1220 LEFT JOIN tbl_droppoints drp1 ON s.droppoint = drp1.id
1221 WHERE s.shift = shift
1222 AND s.date = date AND s.seat = seat AND s.seat <> 0 AND (s.age = 'adt' OR s.age = 'child') ;
1223END ;;
1224DELIMITER ;
1225/*!50003 SET sql_mode = @saved_sql_mode */ ;
1226/*!50003 SET character_set_client = @saved_cs_client */ ;
1227/*!50003 SET character_set_results = @saved_cs_results */ ;
1228/*!50003 SET collation_connection = @saved_col_connection */ ;
1229/*!50003 DROP PROCEDURE IF EXISTS `sp_pmGetUserPermission` */;
1230/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1231/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1232/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1233/*!50003 SET character_set_client = utf8 */ ;
1234/*!50003 SET character_set_results = utf8 */ ;
1235/*!50003 SET collation_connection = utf8_general_ci */ ;
1236/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1237/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1238DELIMITER ;;
1239CREATE DEFINER=`root`@`%` PROCEDURE `sp_pmGetUserPermission`( in user_ID int)
1240BEGIN
1241 select u.id,p.permissionid,description, permissionName, 1 as fromStation from office_Users u inner join stations s on u.station_id = s.station_id
1242 inner join pm_stationpermissions p on s.station_id =p.stationid
1243 inner join pm_permissions pp on p.permissionid = pp.id
1244 where u.id = user_ID
1245 UNION
1246 select u.id,p.permissionid,description, permissionName, 0 as fromStation from office_Users u
1247 inner join pm_userpermissions p on u.id =p.userid
1248 inner join pm_permissions pp on p.permissionid = pp.id
1249 where u.id = user_ID ;
1250
1251END ;;
1252DELIMITER ;
1253/*!50003 SET sql_mode = @saved_sql_mode */ ;
1254/*!50003 SET character_set_client = @saved_cs_client */ ;
1255/*!50003 SET character_set_results = @saved_cs_results */ ;
1256/*!50003 SET collation_connection = @saved_col_connection */ ;
1257/*!50003 DROP PROCEDURE IF EXISTS `sp_ShiftManager` */;
1258/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1259/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1260/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1261/*!50003 SET character_set_client = utf8 */ ;
1262/*!50003 SET character_set_results = utf8 */ ;
1263/*!50003 SET collation_connection = utf8_general_ci */ ;
1264/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1265/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1266DELIMITER ;;
1267CREATE DEFINER=`root`@`%` PROCEDURE `sp_ShiftManager`(in date varchar(20),in direction varchar(200))
1268BEGIN
1269set @mysqlQuery=concat( 'SELECT sh.id as shid,sh.start_time, sh.price_2016_2 as price1, sh.price_2016_3 as price2,st1.viet_name as start,st2.viet_name as stop, sh.type,
1270 1 as isChay,case when rp.rep_id is null then 0 else 1 end as isKhoa
1271
1272 FROM shift sh
1273 LEFT JOIN stops st1 ON sh.start_city = st1.id
1274 LEFT JOIN stops st2 ON sh.stop_city = st2.id
1275 LEFT JOIN (select * from reports where date(date) = ''',STR_TO_DATE(date,'%Y-%m-%d'),''') as rp ON (sh.id = rp.shift )
1276 WHERE sh.type = 1 ',direction,'
1277 -- AND ISNULL(rp.date) AND ISNULL(rp.shift)
1278
1279 UNION
1280 SELECT sh2.id as shid,sh2.start_time, sh2.price_2016_2 as price1, sh2.price_2016_3 as price2,st1.viet_name,st2.viet_name,sh2.type,
1281 case when shi.id is null then 0 else 1 end as isChay,case when rp.rep_id is null then 0 else 1 end as isKhoa
1282
1283 FROM shift sh2
1284 LEFT JOIN stops st1 ON sh2.start_city = st1.id
1285 LEFT JOIN stops st2 ON sh2.stop_city = st2.id
1286 LEFT JOIN (select * from shift_info where date(date) = ''',STR_TO_DATE(date,'%Y-%m-%d'),''' AND ischeck = 1) shi ON sh2.id = shi.shift
1287 LEFT JOIN (select * from reports where date(date) = ''',STR_TO_DATE(date,'%Y-%m-%d'),''') rp ON sh2.id = rp.shift
1288 WHERE (sh2.type in(2,3,4)) ',direction,';');
1289
1290 prepare stmt from @mysqlQuery;
1291 execute stmt;
1292 DEALLOCATE PREPARE stmt;
1293
1294END ;;
1295DELIMITER ;
1296/*!50003 SET sql_mode = @saved_sql_mode */ ;
1297/*!50003 SET character_set_client = @saved_cs_client */ ;
1298/*!50003 SET character_set_results = @saved_cs_results */ ;
1299/*!50003 SET collation_connection = @saved_col_connection */ ;
1300/*!50003 DROP PROCEDURE IF EXISTS `sp_ssCancelFailedTicket` */;
1301/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1302/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1303/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1304/*!50003 SET character_set_client = utf8 */ ;
1305/*!50003 SET character_set_results = utf8 */ ;
1306/*!50003 SET collation_connection = utf8_general_ci */ ;
1307/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1308/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1309DELIMITER ;;
1310CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssCancelFailedTicket`()
1311BEGIN
1312/* status =4: Fail
1313status 1,2: Take too long on payment page
1314*/
1315
1316DECLARE done INT DEFAULT 0;
1317DECLARE resid INT;
1318DECLARE seatid INT;
1319DECLARE note varchar(2000);
1320DECLARE result varchar(4000);
1321DECLARE cur1 CURSOR FOR SELECT * FROM tmpTicket;
1322DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
1323DROP temporary TABLE IF EXISTS tmpTicket;
1324CREATE temporary TABLE tmpTicket (resid int, seatid int, note varchar(2000));
1325
1326INSERT INTO tmpTicket
1327select r.id, s.seat_id, concat('update ordernumber ', r.ordernumber) as note from ss_reservations r inner join ss_seats s on r.id= s.reservation
1328 where (r.status in (1,2) and addtime(r.timestamp,'00:20:00') < current_timestamp);
1329
1330
1331
1332select * from tmpTicket;
1333 OPEN cur1;
1334 REPEAT
1335 FETCH cur1 INTO resid,seatid, note;
1336 IF NOT done THEN
1337 insert into ss_logs(`user_done`,`userid_done`,`date_done`,`table_done`,`actions`,`note`,`url`) values('system',1095, current_timestamp, 'ss_reservations','autocancel',note,'');
1338 update ss_reservations set status =4 where id = resid;
1339 update ss_seats set status =4 where seat_id =seatid;
1340 END IF;
1341 UNTIL done END REPEAT;
1342
1343 CLOSE cur1;
1344
1345END ;;
1346DELIMITER ;
1347/*!50003 SET sql_mode = @saved_sql_mode */ ;
1348/*!50003 SET character_set_client = @saved_cs_client */ ;
1349/*!50003 SET character_set_results = @saved_cs_results */ ;
1350/*!50003 SET collation_connection = @saved_col_connection */ ;
1351/*!50003 DROP PROCEDURE IF EXISTS `sp_ssCheckTax` */;
1352/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1353/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1354/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1355/*!50003 SET character_set_client = utf8 */ ;
1356/*!50003 SET character_set_results = utf8 */ ;
1357/*!50003 SET collation_connection = utf8_general_ci */ ;
1358/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1359/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1360DELIMITER ;;
1361CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssCheckTax`()
1362BEGIN
1363 set @tax = (select count(id) from ss_tax ) + (select count(id) from ss_red_tax );
1364 set @reservation = (select count(tax) from ss_reservations where tax =1);
1365
1366 set @result = concat('Tax: ',@tax + 9118,' , Reservations with tax: ',@reservation);
1367
1368 select @result;
1369
1370END ;;
1371DELIMITER ;
1372/*!50003 SET sql_mode = @saved_sql_mode */ ;
1373/*!50003 SET character_set_client = @saved_cs_client */ ;
1374/*!50003 SET character_set_results = @saved_cs_results */ ;
1375/*!50003 SET collation_connection = @saved_col_connection */ ;
1376/*!50003 DROP PROCEDURE IF EXISTS `sp_ssExpressRevenueByBus` */;
1377/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1378/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1379/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1380/*!50003 SET character_set_client = utf8 */ ;
1381/*!50003 SET character_set_results = utf8 */ ;
1382/*!50003 SET collation_connection = utf8_general_ci */ ;
1383/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1384/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1385DELIMITER ;;
1386CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssExpressRevenueByBus`(in startdate datetime, in enddate datetime)
1387BEGIN
1388SET @sql = NULL;
1389set session group_concat_max_len = 3000;
1390SELECT
1391 GROUP_CONCAT(DISTINCT
1392 CONCAT(
1393 'max(if(number=''',
1394 number,
1395 ''',concat(totalShift,''_'',totalmoneyreceived), 0)) AS ''',
1396 replace(number, ' ', ''),''''
1397 )
1398
1399 ) INTO @sql
1400from vssshiftsummary where date between startdate and enddate ;
1401
1402-- return 1 if there is no data.
1403
1404SET @sql = case when @sql is not null then CONCAT('SELECT pt.date, sum(totalShift) as totalShift, sum(totalmoneyreceived) as totalmoneyreceived,', @sql, ' from vssshiftsummary pt where date between ''',startdate,''' and ''',enddate, '''
1405
1406group by pt.date') else 'select 1' end;
1407
1408PREPARE stmt FROM @sql;
1409EXECUTE stmt;
1410DEALLOCATE PREPARE stmt;
1411END ;;
1412DELIMITER ;
1413/*!50003 SET sql_mode = @saved_sql_mode */ ;
1414/*!50003 SET character_set_client = @saved_cs_client */ ;
1415/*!50003 SET character_set_results = @saved_cs_results */ ;
1416/*!50003 SET collation_connection = @saved_col_connection */ ;
1417/*!50003 DROP PROCEDURE IF EXISTS `sp_ssGetCargo` */;
1418/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1419/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1420/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1421/*!50003 SET character_set_client = utf8 */ ;
1422/*!50003 SET character_set_results = utf8 */ ;
1423/*!50003 SET collation_connection = utf8_general_ci */ ;
1424/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1425/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1426DELIMITER ;;
1427CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssGetCargo`(in date datetime)
1428BEGIN
1429 SELECT s.station_id,s.station_name,s.shortname,sum(coalesce(ha.tongthu,0)) as tongthu FROM stations s left outer join
1430 (SELECT distinct h.guivanphong,sum(h.tiencuochang) as tongthu,h.station_send FROM hlong_eticket.haidv_hanghoa h where taikhoanchuyenxe <> '' and date(thoigianchuyenxe) = date
1431 group by guivanphong,station_send) as ha on s.station_id=ha.station_send
1432 where s.station_id in(SELECT station_id FROM stations where can_cargo_short = '1' and city in (30,10) and active = '1')
1433 group by s.station_id,s.station_name,s.shortname;
1434END ;;
1435DELIMITER ;
1436/*!50003 SET sql_mode = @saved_sql_mode */ ;
1437/*!50003 SET character_set_client = @saved_cs_client */ ;
1438/*!50003 SET character_set_results = @saved_cs_results */ ;
1439/*!50003 SET collation_connection = @saved_col_connection */ ;
1440/*!50003 DROP PROCEDURE IF EXISTS `sp_ssGetOrderCode_New` */;
1441/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1442/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1443/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1444/*!50003 SET character_set_client = utf8 */ ;
1445/*!50003 SET character_set_results = utf8 */ ;
1446/*!50003 SET collation_connection = utf8_general_ci */ ;
1447/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1448/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1449DELIMITER ;;
1450CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssGetOrderCode_New`(in sShiftID int)
1451BEGIN
1452
1453declare orderseed nvarchar(15) ;
1454declare ordercode nvarchar(15);
1455
1456select number COLLATE utf8_unicode_ci, r.ordercode into orderseed, ordercode from ss_routers r inner join ss_shifts s on r.id= s.router_id
1457 inner join ss_ticketnumber t on r.id= t.route where (t.used =0 or t.used is null) and s.id = sShiftID limit 1;
1458
1459update ss_ticketnumber set used =1 where number = orderseed COLLATE utf8_unicode_ci;
1460
1461select orderseed, ordercode;
1462
1463END ;;
1464DELIMITER ;
1465/*!50003 SET sql_mode = @saved_sql_mode */ ;
1466/*!50003 SET character_set_client = @saved_cs_client */ ;
1467/*!50003 SET character_set_results = @saved_cs_results */ ;
1468/*!50003 SET collation_connection = @saved_col_connection */ ;
1469/*!50003 DROP PROCEDURE IF EXISTS `sp_ssMoneyReceivedByUser` */;
1470/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1471/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1472/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1473/*!50003 SET character_set_client = utf8 */ ;
1474/*!50003 SET character_set_results = utf8 */ ;
1475/*!50003 SET collation_connection = utf8_general_ci */ ;
1476/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1477/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1478DELIMITER ;;
1479CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssMoneyReceivedByUser`(in startdate varchar(20), in enddate varchar(20),in shiftid varchar (200), in stationid nvarchar(200))
1480BEGIN
1481set @mysqlQuery=concat( 'select o.id, o.username,coalesce(moneyreceived,0) as moneyreceived ,coalesce(surcharge,0) as surcharge,coalesce(totalticket,0) as totalticket,coalesce(price,0) as ticketprice,st.station_name, o.station_id from office_users o left outer join
1482 (select moneyreceivedby as user_id, sum(moneyreceived) as moneyreceived, sum(deliverysurcharge) as surcharge
1483 from ss_shift_info where shift in (',shiftid,') and date between ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and ''',STR_TO_DATE(enddate,'%Y-%m-%d'),''' group by moneyreceivedby) as lookedShift on o.id = lookedShift.user_id
1484 left outer join (select userid, sum(price) as price, count(ticketid) as totalTicket, station_name from vgetshortshift
1485 where status = 3 and shift in (',shiftid,') and date(date_ticket) between ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and ''',STR_TO_DATE(enddate,'%Y-%m-%d'),''' group by userid, station_name
1486 ) as ticket on o.id = ticket.userid
1487 inner join stations st ON o.station_id = st.station_id
1488 where (coalesce(moneyreceived,0) <>0 or coalesce(surcharge,0) <>0 or coalesce(price,0) <> 0)');
1489
1490
1491 set @mysqlQuery = if (stationid <> '', concat(@mysqlQuery, ' and o.station_id in (',stationid,')'),@mysqlQuery);
1492
1493
1494 /*Chỉ group theo user*/
1495
1496 set @mysqlQuery = concat('select sum(moneyreceived) as moneyreceived, sum(surcharge) as surcharge, sum(totalticket) as totalticket, sum(ticketprice) as ticketprice,station_name, station_id from (',@mysqlQuery,') as query group by station_name, station_id');
1497
1498
1499 prepare stmt from @mysqlQuery;
1500 execute stmt;
1501
1502
1503 DEALLOCATE PREPARE stmt;
1504
1505END ;;
1506DELIMITER ;
1507/*!50003 SET sql_mode = @saved_sql_mode */ ;
1508/*!50003 SET character_set_client = @saved_cs_client */ ;
1509/*!50003 SET character_set_results = @saved_cs_results */ ;
1510/*!50003 SET collation_connection = @saved_col_connection */ ;
1511/*!50003 DROP PROCEDURE IF EXISTS `sp_ssRedRevenueByBus` */;
1512/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1513/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1514/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1515/*!50003 SET character_set_client = utf8 */ ;
1516/*!50003 SET character_set_results = utf8 */ ;
1517/*!50003 SET collation_connection = utf8_general_ci */ ;
1518/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1519/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1520DELIMITER ;;
1521CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssRedRevenueByBus`(in startdate datetime, in enddate datetime)
1522BEGIN
1523SET @sql = NULL;
1524set session group_concat_max_len = 3000;
1525SELECT
1526 GROUP_CONCAT(DISTINCT
1527 CONCAT(
1528 'max(if(number=''',
1529 number,
1530 ''',concat(totalShift,''_'',totalmoneyreceived), 0)) AS ''',
1531 replace(number, ' ', ''),''''
1532 )
1533
1534 ) INTO @sql
1535from vRedShiftSummary where date between startdate and enddate;
1536
1537-- return 1 if there is no data.
1538
1539SET @sql = case when @sql is not null then CONCAT('SELECT pt.date, sum(totalShift) as totalShift, sum(totalmoneyreceived) as totalmoneyreceived,', @sql, ' from vRedShiftSummary pt where date between ''',startdate,''' and ''',enddate, '''
1540group by pt.date') else 'select 1' end;
1541
1542PREPARE stmt FROM @sql;
1543EXECUTE stmt;
1544DEALLOCATE PREPARE stmt;
1545END ;;
1546DELIMITER ;
1547/*!50003 SET sql_mode = @saved_sql_mode */ ;
1548/*!50003 SET character_set_client = @saved_cs_client */ ;
1549/*!50003 SET character_set_results = @saved_cs_results */ ;
1550/*!50003 SET collation_connection = @saved_col_connection */ ;
1551/*!50003 DROP PROCEDURE IF EXISTS `sp_ssRedRevenueByBus_tax` */;
1552/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1553/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1554/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1555/*!50003 SET character_set_client = utf8 */ ;
1556/*!50003 SET character_set_results = utf8 */ ;
1557/*!50003 SET collation_connection = utf8_general_ci */ ;
1558/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1559/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1560DELIMITER ;;
1561CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssRedRevenueByBus_tax`(in startdate datetime, in enddate datetime)
1562BEGIN
1563SET @sql = NULL;
1564set session group_concat_max_len = 3000;
1565SELECT
1566 GROUP_CONCAT(DISTINCT
1567 CONCAT(
1568 'max(if(number=''',
1569 number,
1570 ''',concat(totalShift,''_'',totalmoneyreceived), 0)) AS ''',
1571 replace(number, ' ', ''),''''
1572 )
1573
1574 ) INTO @sql
1575from vRedShiftSummary_Red where date between startdate and enddate;
1576
1577-- return 1 if there is no data.
1578
1579SET @sql = case when @sql is not null then CONCAT('SELECT pt.date, sum(totalShift) as totalShift, sum(totalmoneyreceived) as totalmoneyreceived,', @sql, ' from vRedShiftSummary_Red pt where date between ''',startdate,''' and ''',enddate, '''
1580group by pt.date') else 'select 1' end;
1581
1582PREPARE stmt FROM @sql;
1583EXECUTE stmt;
1584DEALLOCATE PREPARE stmt;
1585END ;;
1586DELIMITER ;
1587/*!50003 SET sql_mode = @saved_sql_mode */ ;
1588/*!50003 SET character_set_client = @saved_cs_client */ ;
1589/*!50003 SET character_set_results = @saved_cs_results */ ;
1590/*!50003 SET collation_connection = @saved_col_connection */ ;
1591/*!50003 DROP PROCEDURE IF EXISTS `sp_ssRedShiftInfo` */;
1592/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1593/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1594/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1595/*!50003 SET character_set_client = utf8 */ ;
1596/*!50003 SET character_set_results = utf8 */ ;
1597/*!50003 SET collation_connection = utf8_general_ci */ ;
1598/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1599/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1600DELIMITER ;;
1601CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssRedShiftInfo`(in startdate datetime, in routeid int)
1602BEGIN
1603select st.start_time, coalesce(info.id,0) as id, st.id as shift, startdate as date ,info.driver1,
1604 info.driver2,
1605 info.bus_number,
1606 info.number,
1607 info.hodem,
1608 info.ten,
1609 info.hodem2,
1610 info.ten2,
1611 info.completed,
1612 info.issleep,
1613 info.customerstation,
1614 info.customerroad,
1615 info.moneycustomer,
1616 info.deliverysurcharge,
1617 info.contract,
1618 info.totalmoneyreceived,
1619 info.paystation,
1620 info.paycustomer,
1621 info.moneyeat,
1622 info.payother,
1623 info.totalpaymoney,
1624 info.moneyCB,
1625 info.totalcustomer,
1626 info.realmoney from ss_shifts st left outer join
1627(select ri.* from vgetredshiftinfo ri inner join ss_shifts ss on ri.shift =ss.id where ri.date = startdate and ss.router_id = routeid) as info
1628 on st.id = info.shift
1629 where st.router_id= routeid
1630 ;
1631END ;;
1632DELIMITER ;
1633/*!50003 SET sql_mode = @saved_sql_mode */ ;
1634/*!50003 SET character_set_client = @saved_cs_client */ ;
1635/*!50003 SET character_set_results = @saved_cs_results */ ;
1636/*!50003 SET collation_connection = @saved_col_connection */ ;
1637/*!50003 DROP PROCEDURE IF EXISTS `sp_ssRedShiftInfo2` */;
1638/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1639/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1640/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1641/*!50003 SET character_set_client = utf8 */ ;
1642/*!50003 SET character_set_results = utf8 */ ;
1643/*!50003 SET collation_connection = utf8_general_ci */ ;
1644/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1645/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1646DELIMITER ;;
1647CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssRedShiftInfo2`(in startdate datetime, in routeid int)
1648BEGIN
1649
1650select st.start_time, coalesce(info.id,0) as id, st.id as shift, startdate as date ,info.driver1,
1651 info.driver2,
1652 info.bus_number,
1653 info.number,
1654 info.hodem,
1655 info.ten,
1656 info.hodem2,
1657 info.ten2,
1658 info.completed,
1659 info.issleep,
1660 st.type,
1661 info.customerstation,
1662 info.customerroad,
1663 info.moneycustomer,
1664 info.deliverysurcharge,
1665 info.contract,
1666 info.totalmoneyreceived,
1667 info.paystation,
1668 info.paycustomer,
1669 info.moneyeat,
1670 info.payother,
1671 info.totalpaymoney,
1672 info.moneyCB,
1673 info.totalcustomer,
1674 info.realmoney from ss_shifts st left outer join
1675(select ri.* from vgetredshiftinfo2 ri inner join ss_shifts ss on ri.shift =ss.id where ri.date = startdate and ss.router_id = routeid AND ri.type=1) as info
1676 on st.id = info.shift
1677 where st.router_id= routeid AND st.type=1
1678 UNION
1679 SELECT st.start_time, coalesce(info.id,0) as id, st.id as shift, startdate as date ,info.driver1,
1680 info.driver2,
1681 info.bus_number,
1682 info.number,
1683 info.hodem,
1684 info.ten,
1685 info.hodem2,
1686 info.ten2,
1687 info.completed,
1688 info.issleep,
1689 st.type,
1690 info.customerstation,
1691 info.customerroad,
1692 info.moneycustomer,
1693 info.deliverysurcharge,
1694 info.contract,
1695 info.totalmoneyreceived,
1696 info.paystation,
1697 info.paycustomer,
1698 info.moneyeat,
1699 info.payother,
1700 info.totalpaymoney,
1701 info.moneyCB,
1702 info.totalcustomer,
1703 info.realmoney from ss_shifts st left outer join
1704(select ri.* from vgetredshiftinfo2 ri inner join ss_shifts ss on ri.shift =ss.id where ri.date = startdate and ss.router_id = routeid) as info
1705 on st.id = info.shift,ss_shift_extra WHERE ss_shift_extra.shiftid = st.id AND ss_shift_extra.date=STR_TO_DATE(startdate,'%Y-%m-%d') AND st.type >= 2 AND st.router_id = routeid
1706 ORDER BY start_time
1707 ;
1708
1709
1710END ;;
1711DELIMITER ;
1712/*!50003 SET sql_mode = @saved_sql_mode */ ;
1713/*!50003 SET character_set_client = @saved_cs_client */ ;
1714/*!50003 SET character_set_results = @saved_cs_results */ ;
1715/*!50003 SET collation_connection = @saved_col_connection */ ;
1716/*!50003 DROP PROCEDURE IF EXISTS `sp_ssReportLimousineRevenueByBus` */;
1717/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1718/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1719/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1720/*!50003 SET character_set_client = utf8 */ ;
1721/*!50003 SET character_set_results = utf8 */ ;
1722/*!50003 SET collation_connection = utf8_general_ci */ ;
1723/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1724/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1725DELIMITER ;;
1726CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssReportLimousineRevenueByBus`(in startdate datetime, in enddate datetime, in bus VARCHAR(50), in dep INT(2))
1727BEGIN
1728SET @sql = NULL;
1729set session group_concat_max_len = 4000;
1730SELECT
1731 GROUP_CONCAT(DISTINCT CONCAT('max(if(number=\'',
1732 number,
1733 '\',concat(totalShift,\'_\',totalmoneyreceived), 0)) AS \'',
1734 REPLACE(number, ' ', ''),
1735 '\''))
1736INTO @sql FROM
1737 vssReportByBus
1738WHERE
1739 date BETWEEN startdate AND enddate
1740 AND Type = 5
1741 AND CASE
1742 WHEN bus <> '' THEN number = bus
1743 ELSE 1 = 1
1744 END
1745 AND CASE
1746 WHEN dep <> 0 THEN departure = dep
1747 ELSE 1 = 1
1748 END
1749;
1750
1751set @where = CASE WHEN bus <> '' THEN CONCAT(' AND number = ''', bus, '''')
1752 ELSE ''
1753 END;
1754
1755set @where2 = CASE WHEN dep <> 0 THEN CONCAT(' AND departure = ', dep)
1756 ELSE ''
1757 END;
1758
1759SET @sql = case when @sql is not null then CONCAT('SELECT DATA.date, sum(totalShift) as totalShift, sum(totalmoneyreceived) as totalmoneyreceived,', @sql,
1760' FROM (SELECT pt.date, pt.number, sum(totalShift) as totalShift, sum(totalmoneyreceived) as totalmoneyreceived
1761from vssReportByBus pt where date between ''',startdate,''' and ''',enddate, ''' and Type = 5 ', @where, ' ', @where2,
1762' group by pt.date, pt.number) AS DATA GROUP by DATA.date') else 'select 1' end;
1763
1764PREPARE stmt FROM @sql;
1765EXECUTE stmt;
1766DEALLOCATE PREPARE stmt;
1767END ;;
1768DELIMITER ;
1769/*!50003 SET sql_mode = @saved_sql_mode */ ;
1770/*!50003 SET character_set_client = @saved_cs_client */ ;
1771/*!50003 SET character_set_results = @saved_cs_results */ ;
1772/*!50003 SET collation_connection = @saved_col_connection */ ;
1773/*!50003 DROP PROCEDURE IF EXISTS `sp_ssReportRevenueByBus` */;
1774/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1775/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1776/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1777/*!50003 SET character_set_client = utf8 */ ;
1778/*!50003 SET character_set_results = utf8 */ ;
1779/*!50003 SET collation_connection = utf8_general_ci */ ;
1780/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1781/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1782DELIMITER ;;
1783CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssReportRevenueByBus`(in startdate datetime, in enddate datetime, in routeType int)
1784BEGIN
1785SET @sql = NULL;
1786set session group_concat_max_len = 4000;
1787SELECT
1788 GROUP_CONCAT(DISTINCT
1789 CONCAT(
1790 'max(if(number=''',
1791 number,
1792 ''',concat(totalShift,''_'',totalmoneyreceived), 0)) AS ''',
1793 replace(number, ' ', ''),''''
1794 )
1795
1796 ) INTO @sql
1797from vssReportByBus where date between startdate and enddate and
1798CASE when routeType >0 then Type = routeType
1799 when routeType =0 then Type in (1,2)
1800 when routeType =-1 then Type in (3,4)
1801 END
1802 ;
1803
1804-- neu khong chá»n type, phải xá» lý trÆ°á»›c view thà nh subquery để gom nhóm liệu theo type
1805
1806set @fromobject = CASE WHEN routeType>0 then'vssReportByBus'
1807 WHEN routeType=0 then '(select date, number,sum(totalShift) totalShift , sum(totalmoneyreceived) totalmoneyreceived from vssReportByBus where Type in (1,2) group by date, number)'
1808 WHEN routeType=-1 then '(select date, number,sum(totalShift) totalShift , sum(totalmoneyreceived) totalmoneyreceived from vssReportByBus where Type in (3,4) group by date, number)'
1809 END;
1810
1811
1812-- neu chon type, day type vao dieu kien where
1813set @wherecondition = if(routeType<=0,'', concat(' and Type =',routetype));
1814
1815
1816SET @sql = case when @sql is not null then CONCAT('SELECT pt.date, sum(totalShift) as totalShift, sum(totalmoneyreceived) as totalmoneyreceived,', @sql, ' from ',@fromobject,' pt where date between ''',startdate,''' and ''',enddate, '''',@wherecondition,'
1817
1818
1819group by pt.date') else 'select 1' end;
1820
1821PREPARE stmt FROM @sql;
1822EXECUTE stmt;
1823DEALLOCATE PREPARE stmt;
1824END ;;
1825DELIMITER ;
1826/*!50003 SET sql_mode = @saved_sql_mode */ ;
1827/*!50003 SET character_set_client = @saved_cs_client */ ;
1828/*!50003 SET character_set_results = @saved_cs_results */ ;
1829/*!50003 SET collation_connection = @saved_col_connection */ ;
1830/*!50003 DROP PROCEDURE IF EXISTS `sp_ssReportRevenueByShift` */;
1831/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1832/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1833/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1834/*!50003 SET character_set_client = utf8 */ ;
1835/*!50003 SET character_set_results = utf8 */ ;
1836/*!50003 SET collation_connection = utf8_general_ci */ ;
1837/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1838/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1839DELIMITER ;;
1840CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssReportRevenueByShift`(in startdate datetime, in enddate datetime, in routeType int)
1841BEGIN
1842
1843DECLARE done INT DEFAULT 0;
1844DECLARE thisDate varchar(10);
1845
1846DECLARE cur1 CURSOR FOR SELECT * FROM tmpDate;
1847DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
1848DROP temporary TABLE IF EXISTS tmpDate;
1849CREATE temporary TABLE tmpDate (date varchar(20));
1850
1851DROP temporary TABLE IF EXISTS tmpShift;
1852CREATE temporary TABLE tmpShift (shiftid int, infoID int, shiftDate varchar(10), starttime time,info varchar(1000) CHARSET utf8, routeID int, busnumber varchar(50), drivername varchar(50) CHARSET utf8, departure int(2), deliverysurcharge decimal(10,0) ,customernoticket int(11) ,moneynoticket decimal(10,0), moneyreceived decimal(10,0), paystation decimal(10,0) ,moneyeat decimal(10,0) ,paycustomer decimal(10,0) ,payother decimal(10,0) ,moneyCB decimal(10,0) ,totalticketroundtrip int(11) ,moneyroundtrip decimal(10,0) ,exposurecode varchar(45) ,totalticket int(11) ,moneyticket decimal(10,0) ,totalpaymoney decimal(10,0) ,realtotalmoney decimal(10,0) ,totalticketsoftware int(11) ,ticketfree int(11) ,ticketCB int(11),payhightwaytax decimal(10,0), correctedCustomerNo int, reason varchar(1000), routeType int, officegoods decimal(10,0), officeexposure decimal(10,0), completed int, note varchar(1024));
1853
1854
1855-- Lay ra danh sach ngay trong khoang 2 ngay truyen vao
1856INSERT INTO tmpDate
1857select * from
1858(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
1859 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
1860 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
1861 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
1862 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
1863 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
1864where selected_date between startDate and endDate;
1865
1866
1867-- Duyet qua danh sach ngay, insert và o bảng tạm, cuối cùng sẽ query lấy hết dữ liệu trong đó ra
1868
1869 OPEN cur1;
1870 REPEAT
1871 FETCH cur1 INTO thisDate;
1872 IF NOT done THEN
1873 insert into tmpShift -- values(1,2,thisDate,'2016-2-21',1,22,3000,3000,3000,9000);
1874
1875 select s.id, coalesce(si.id,0) ,thisDate, s.start_Time,info, router_id,si.number, CONCAT(vic_hoso.hodem, ' ', vic_hoso.ten), s.departure, deliverysurcharge/1000,customernoticket,moneynoticket/1000, moneyreceived/1000, paystation/1000,moneyeat/1000,paycustomer/1000,payother/1000,moneyCB/1000,totalticketroundtrip,moneyroundtrip/1000,si.exposurecode,totalticket,moneyticket/1000,totalpaymoney/1000,realtotalmoney/1000,totalticketsoftware,ticketfree,ticketCB,payhightwaytax/1000,correctedCustomerNo,reason, rt.RouteType, officegoods/1000 officegoods, officeexposure/1000 officeexposure, completed, s.note
1876 from ss_shifts s join (select ss_shift_info.*,bus.number from ss_shift_info left outer join bus on ss_shift_info.bus_number=bus.id
1877
1878 where date =thisDate) si on s.id = si.shift
1879 inner join ss_routers rt on s.router_id = rt.id
1880 left join vic_hoso on si.driver1 = vic_hoso.id
1881 ;
1882
1883 END IF;
1884 UNTIL done END REPEAT;
1885
1886 CLOSE cur1;
1887
1888-- neu co filter theo shift, thi gan filter vao
1889set @tempwhere =concat(' and routeType =',routetype,'');
1890
1891-- neu co filter theo shift, thi gan filter vao
1892
1893set @wherecondition = case when routeType =0 then ' and routeType in(1,2) '
1894 when routeType =-1 then ' and routeType in(3,4) '
1895 else @tempwhere end;
1896
1897set @mysqlQuery = concat('select * from tmpShift where 1=1 ',@wherecondition,' order by shiftDate, routeID, starttime ');
1898
1899
1900prepare stmt from @mysqlQuery;
1901execute stmt;
1902deallocate prepare stmt;
1903
1904END ;;
1905DELIMITER ;
1906/*!50003 SET sql_mode = @saved_sql_mode */ ;
1907/*!50003 SET character_set_client = @saved_cs_client */ ;
1908/*!50003 SET character_set_results = @saved_cs_results */ ;
1909/*!50003 SET collation_connection = @saved_col_connection */ ;
1910/*!50003 DROP PROCEDURE IF EXISTS `sp_ssReportRevenueByStations` */;
1911/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1912/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1913/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1914/*!50003 SET character_set_client = utf8 */ ;
1915/*!50003 SET character_set_results = utf8 */ ;
1916/*!50003 SET collation_connection = utf8_general_ci */ ;
1917/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1918/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1919DELIMITER ;;
1920CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssReportRevenueByStations`(in startdate varchar(20), in enddate varchar(20), in stationid nvarchar(200), in vehicletype int)
1921BEGIN
1922set @mysqlQuery=concat( 'select o.id, o.username,0 as moneyreceived ,0 as surcharge,coalesce(totalticket,0) as totalticket,coalesce(price,0) as ticketprice,st.station_name, o.station_id, ticket.type from office_users o inner join
1923 (select userid, sum(price) as price, coalesce(ss_reservations.type,1) type, count(id) as totalTicket from ss_reservations
1924 where status = 3 and date(date_ticket) between ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and ''',STR_TO_DATE(enddate,'%Y-%m-%d'),''' group by userid, coalesce(ss_reservations.type,1)
1925 ) as ticket on o.id = ticket.userid
1926 inner join stations st ON o.station_id = st.station_id
1927 where coalesce(price,0) <> 0');
1928
1929 set @wherecondition = case when vehicletype =-1 then ' and ticket.Type in(3,4)'
1930 when vehicletype = 0 then ' and ticket.Type in(1,2)'
1931 else concat(' and ticket.Type = ',vehicletype)
1932 end;
1933
1934 set @mysqlQuery = if (stationid <> '', concat(@mysqlQuery, ' and o.station_id in (',stationid,')'),@mysqlQuery);
1935 set @mysqlQuery = concat(@mysqlQuery, @wherecondition);
1936
1937
1938 /*Chỉ group theo user*/
1939
1940 set @mysqlQuery = concat('select sum(moneyreceived) as moneyreceived, sum(surcharge) as surcharge, sum(totalticket) as totalticket, sum(ticketprice) as ticketprice,station_name, station_id from (',@mysqlQuery,') as query group by station_name, station_id');
1941
1942
1943 prepare stmt from @mysqlQuery;
1944 execute stmt;
1945
1946
1947 DEALLOCATE PREPARE stmt;
1948
1949END ;;
1950DELIMITER ;
1951/*!50003 SET sql_mode = @saved_sql_mode */ ;
1952/*!50003 SET character_set_client = @saved_cs_client */ ;
1953/*!50003 SET character_set_results = @saved_cs_results */ ;
1954/*!50003 SET collation_connection = @saved_col_connection */ ;
1955/*!50003 DROP PROCEDURE IF EXISTS `sp_ssReportRevenueByUsers` */;
1956/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1957/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1958/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1959/*!50003 SET character_set_client = utf8 */ ;
1960/*!50003 SET character_set_results = utf8 */ ;
1961/*!50003 SET collation_connection = utf8_general_ci */ ;
1962/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1963/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
1964DELIMITER ;;
1965CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssReportRevenueByUsers`(in startdate varchar(20), in enddate varchar(20), in stationid nvarchar(200), in userid nvarchar(100), in vehicletype int)
1966BEGIN
1967set @mysqlQuery=concat( 'select o.id, o.username,0 as moneyreceived ,0 as surcharge,coalesce(totalticket,0) as totalticket,coalesce(price,0) as ticketprice,st.station_name, o.station_id, ticket.type from office_users o inner join
1968 (select userid, sum(price) as price, coalesce(ss_reservations.type,1) type, count(id) as totalTicket from ss_reservations
1969 where status = 3 and date(date_ticket) between ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and ''',STR_TO_DATE(enddate,'%Y-%m-%d'),''' group by userid,coalesce(ss_reservations.type,1)
1970 ) as ticket on o.id = ticket.userid
1971 inner join stations st ON o.station_id = st.station_id
1972 where coalesce(price,0) <> 0 ');
1973
1974 set @wherecondition = case when vehicletype =-1 then ' and ticket.Type in(3,4)'
1975 when vehicletype = 0 then ' and ticket.Type in(1,2)'
1976 else concat(' and ticket.Type = ',vehicletype)
1977 end;
1978
1979 set @mysqlQuery = if (stationid <> '', concat(@mysqlQuery, ' and o.station_id in (',stationid,')'),@mysqlQuery);
1980 set @mysqlQuery = if (userid <> '', concat(@mysqlQuery, ' and o.id in (',userid,')'),@mysqlQuery);
1981 set @mysqlQuery = concat(@mysqlQuery, @wherecondition);
1982
1983
1984 /*Chỉ group theo user*/
1985
1986 set @mysqlQuery = concat('select id,username, sum(moneyreceived) as moneyreceived, sum(surcharge) as surcharge, sum(totalticket) as totalticket, sum(ticketprice) as ticketprice,station_name, station_id from (',@mysqlQuery,') as query group by id,username,station_name, station_id');
1987
1988
1989 prepare stmt from @mysqlQuery;
1990 execute stmt;
1991
1992
1993 DEALLOCATE PREPARE stmt;
1994
1995END ;;
1996DELIMITER ;
1997/*!50003 SET sql_mode = @saved_sql_mode */ ;
1998/*!50003 SET character_set_client = @saved_cs_client */ ;
1999/*!50003 SET character_set_results = @saved_cs_results */ ;
2000/*!50003 SET collation_connection = @saved_col_connection */ ;
2001/*!50003 DROP PROCEDURE IF EXISTS `sp_ssReportTaxRevenue` */;
2002/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2003/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2004/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2005/*!50003 SET character_set_client = utf8 */ ;
2006/*!50003 SET character_set_results = utf8 */ ;
2007/*!50003 SET collation_connection = utf8_general_ci */ ;
2008/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2009/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2010DELIMITER ;;
2011CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssReportTaxRevenue`(in startdate datetime, in enddate datetime, in route nvarchar(10))
2012BEGIN
2013
2014set @taxObject = if(route='(1,2)', 'ss_tax', 'ss_tax_tb');
2015
2016-- kiểm tra nếu route = HN HP và ngà y cần lấy bé hơn 11/2, chỉ lấy trong bảng backup
2017
2018set @taxObject= if(route ='(1,2)' and enddate<='2017-2-8','ss_tax_20170211',@taxObject);
2019
2020set @mysqlQuery=concat('select v.orderref, v.name,v.date, t.serri, t.id as taxid, v.taxcode, v.Phone, v.Price/1.1 as Price, v.Price - v.Price/1.1 as VAT, v.Price as Total, t.date_invoice, t.reservee, t.ticketing, v.start_time, v.destination, v.departure from vgetshortshift_forreport v inner join ',@taxObject,' t on v.ticketid = t.reservation
2021 where date(t.date_ticket) between ''', startdate,''' and ''',enddate,''' and coalesce(stype,1) in ',route);
2022
2023
2024-- Vì có backup cho dữ liệu (HN-HP) từ 9/2/2017 trở vá» trÆ°á»›c, nên phải kiểm tra trÆ°á»ng hợp ngÆ°á»i dùng cần lấy dữ liệu cÅ©. Xấu nhất là khi
2025-- ngÆ°á»i dùng xem dữ liệu ở cả 2 bảng, phải Union dể lấy hết dữ liệu ra
2026
2027
2028 set @mysqlQuery = if(route='(1,2)' and enddate>='2017-2-9' and startdate<='2017-2-9',
2029 concat('select v.orderref, v.name,v.date, t.serri, t.id as taxid, v.taxcode, v.Phone, v.Price/1.1 as Price, v.Price - v.Price/1.1 as VAT, v.Price as Total, t.date_invoice, t.reservee, t.ticketing, v.start_time, v.destination, v.departure from vgetshortshift_forreport v inner join ss_tax_20170211 t on v.ticketid = t.reservation
2030 where date(t.date_ticket) between ''', startdate,''' and ','''2017-2-12''',' and coalesce(stype,1) in (1,2)
2031 UNION ALL
2032 select v.orderref, v.name,v.date, t.serri, t.id as taxid, v.taxcode, v.Phone, v.Price/1.1 as Price, v.Price - v.Price/1.1 as VAT, v.Price as Total, t.date_invoice, t.reservee, t.ticketing, v.start_time, v.destination, v.departure from vgetshortshift_forreport v inner join ss_tax t on v.ticketid = t.reservation
2033 where date(t.date_ticket) between ', '''2017-2-9''',' and ''',enddate,''' and coalesce(stype,1) in (1,2)
2034 '),@mysqlQuery);
2035
2036PREPARE stmt FROM @mysqlQuery;
2037EXECUTE stmt;
2038DEALLOCATE PREPARE stmt;
2039
2040END ;;
2041DELIMITER ;
2042/*!50003 SET sql_mode = @saved_sql_mode */ ;
2043/*!50003 SET character_set_client = @saved_cs_client */ ;
2044/*!50003 SET character_set_results = @saved_cs_results */ ;
2045/*!50003 SET collation_connection = @saved_col_connection */ ;
2046/*!50003 DROP PROCEDURE IF EXISTS `sp_ssReportTaxRevenueBackUp20170314` */;
2047/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2048/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2049/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2050/*!50003 SET character_set_client = utf8 */ ;
2051/*!50003 SET character_set_results = utf8 */ ;
2052/*!50003 SET collation_connection = utf8_general_ci */ ;
2053/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2054/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2055DELIMITER ;;
2056CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssReportTaxRevenueBackUp20170314`(in startdate datetime, in enddate datetime, in route nvarchar(10))
2057BEGIN
2058
2059set @taxObject = if(route='(1,2)', 'ss_tax', 'ss_tax_tb');
2060
2061set @mysqlQuery=concat('select v.orderref, v.name,v.date, t.serri, t.id as taxid, v.taxcode, v.Phone, v.Price/1.1 as Price, v.Price - v.Price/1.1 as VAT, v.Price as Total, t.timestamp, t.reservee, t.ticketing, v.start_time, v.destination, v.departure from vgetshortshift_forreport v inner join ',@taxObject,' t on v.ticketid = t.reservation
2062 where date(t.date_ticket) between ''', startdate,''' and ''',enddate,''' and coalesce(stype,1) in ',route);
2063
2064PREPARE stmt FROM @mysqlQuery;
2065EXECUTE stmt;
2066DEALLOCATE PREPARE stmt;
2067
2068END ;;
2069DELIMITER ;
2070/*!50003 SET sql_mode = @saved_sql_mode */ ;
2071/*!50003 SET character_set_client = @saved_cs_client */ ;
2072/*!50003 SET character_set_results = @saved_cs_results */ ;
2073/*!50003 SET collation_connection = @saved_col_connection */ ;
2074/*!50003 DROP PROCEDURE IF EXISTS `sp_ssReportTaxRevenueNew` */;
2075/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2076/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2077/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2078/*!50003 SET character_set_client = utf8 */ ;
2079/*!50003 SET character_set_results = utf8 */ ;
2080/*!50003 SET collation_connection = utf8_general_ci */ ;
2081/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2082/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2083DELIMITER ;;
2084CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssReportTaxRevenueNew`(in startdate datetime, in enddate datetime, in route nvarchar(10))
2085BEGIN
2086
2087set @taxObject = if(route='(1,2)', 'ss_tax', 'ss_tax_tb');
2088
2089-- kiểm tra nếu route = HN HP và ngà y cần lấy bé hơn 11/2, chỉ lấy trong bảng backup
2090
2091set @taxObject= if(route ='(1,2)' and enddate<='2017-2-10','ss_tax_20170211',@taxObject);
2092
2093set @mysqlQuery=concat('select v.orderref, v.name,v.date, t.serri, t.id as taxid, v.taxcode, v.Phone, v.Price/1.1 as Price, v.Price - v.Price/1.1 as VAT, v.Price as Total, t.timestamp, t.reservee, t.ticketing, v.start_time, v.destination, v.departure from vgetshortshift_forreport v inner join ',@taxObject,' t on v.ticketid = t.reservation
2094 where date(t.date_ticket) between ''', startdate,''' and ''',enddate,''' and coalesce(stype,1) in ',route);
2095
2096
2097-- Vì có backup cho dữ liệu (HN-HP) từ 11/2/2017 trở vá» trÆ°á»›c, nên phải kiểm tra trÆ°á»ng hợp ngÆ°á»i dùng cần lấy dữ liệu cÅ©. Xấu nhất là khi
2098-- ngÆ°á»i dùng xem dữ liệu ở cả 2 bảng, phải Union dể lấy hết dữ liệu ra
2099
2100
2101 set @mysqlQuery = if(route='(1,2)' and enddate>='2017-2-11' and startdate<='2017-2-11',
2102 concat('select v.orderref, v.name,v.date, t.serri, t.id as taxid, v.taxcode, v.Phone, v.Price/1.1 as Price, v.Price - v.Price/1.1 as VAT, v.Price as Total, t.timestamp, t.reservee, t.ticketing, v.start_time, v.destination, v.departure from vgetshortshift_forreport v inner join ss_tax_20170211 t on v.ticketid = t.reservation
2103 where date(t.date_ticket) between ''', startdate,''' and ','''2017-2-12''',' and coalesce(stype,1) in (1,2)
2104 UNION ALL
2105 select v.orderref, v.name,v.date, t.serri, t.id as taxid, v.taxcode, v.Phone, v.Price/1.1 as Price, v.Price - v.Price/1.1 as VAT, v.Price as Total, t.timestamp, t.reservee, t.ticketing, v.start_time, v.destination, v.departure from vgetshortshift_forreport v inner join ss_tax t on v.ticketid = t.reservation
2106 where date(t.date_ticket) between ', '''2017-2-9''',' and ''',enddate,''' and coalesce(stype,1) in (1,2)
2107 '),@mysqlQuery);
2108
2109PREPARE stmt FROM @mysqlQuery;
2110EXECUTE stmt;
2111DEALLOCATE PREPARE stmt;
2112
2113END ;;
2114DELIMITER ;
2115/*!50003 SET sql_mode = @saved_sql_mode */ ;
2116/*!50003 SET character_set_client = @saved_cs_client */ ;
2117/*!50003 SET character_set_results = @saved_cs_results */ ;
2118/*!50003 SET collation_connection = @saved_col_connection */ ;
2119/*!50003 DROP PROCEDURE IF EXISTS `sp_ssRevenueByStations` */;
2120/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2121/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2122/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2123/*!50003 SET character_set_client = utf8 */ ;
2124/*!50003 SET character_set_results = utf8 */ ;
2125/*!50003 SET collation_connection = utf8_general_ci */ ;
2126/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2127/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2128DELIMITER ;;
2129CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssRevenueByStations`(in startdate varchar(20), in enddate varchar(20),in shiftid varchar (2000), in stationid nvarchar(200))
2130BEGIN
2131set @mysqlQuery=concat( 'select o.id, o.username,0 as moneyreceived ,0 as surcharge,coalesce(totalticket,0) as totalticket,coalesce(price,0) as ticketprice,st.station_name, o.station_id from office_users o inner join
2132 (select userid, sum(price) as price, count(id) as totalTicket from ss_reservations
2133 where status = 3 and shift in (',shiftid,') and coalesce(ss_reservations.type,1) =1 and date(date_ticket) between ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and ''',STR_TO_DATE(enddate,'%Y-%m-%d'),''' group by userid
2134 ) as ticket on o.id = ticket.userid
2135 inner join stations st ON o.station_id = st.station_id
2136 where coalesce(price,0) <> 0');
2137
2138
2139 set @mysqlQuery = if (stationid <> '', concat(@mysqlQuery, ' and o.station_id in (',stationid,')'),@mysqlQuery);
2140
2141
2142 /*Chỉ group theo user*/
2143
2144 set @mysqlQuery = concat('select sum(moneyreceived) as moneyreceived, sum(surcharge) as surcharge, sum(totalticket) as totalticket, sum(ticketprice) as ticketprice,station_name, station_id from (',@mysqlQuery,') as query group by station_name, station_id');
2145
2146
2147 prepare stmt from @mysqlQuery;
2148 execute stmt;
2149
2150
2151 DEALLOCATE PREPARE stmt;
2152
2153END ;;
2154DELIMITER ;
2155/*!50003 SET sql_mode = @saved_sql_mode */ ;
2156/*!50003 SET character_set_client = @saved_cs_client */ ;
2157/*!50003 SET character_set_results = @saved_cs_results */ ;
2158/*!50003 SET collation_connection = @saved_col_connection */ ;
2159/*!50003 DROP PROCEDURE IF EXISTS `sp_ssRevenueByUsers` */;
2160/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2161/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2162/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2163/*!50003 SET character_set_client = utf8 */ ;
2164/*!50003 SET character_set_results = utf8 */ ;
2165/*!50003 SET collation_connection = utf8_general_ci */ ;
2166/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2167/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2168DELIMITER ;;
2169CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssRevenueByUsers`(in startdate varchar(20), in enddate varchar(20),in shiftid varchar (2000), in stationid nvarchar(200), in userid nvarchar(100))
2170BEGIN
2171set @mysqlQuery=concat( 'select o.id, o.username,0 as moneyreceived ,0 as surcharge,coalesce(totalticket,0) as totalticket,coalesce(price,0) as ticketprice,st.station_name, o.station_id from office_users o inner join
2172 (select userid, sum(price) as price, count(id) as totalTicket from ss_reservations
2173 where status = 3 and shift in (',shiftid,') and coalesce(ss_reservations.type,1) =1 and date(date_ticket) between ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and ''',STR_TO_DATE(enddate,'%Y-%m-%d'),''' group by userid
2174 ) as ticket on o.id = ticket.userid
2175 inner join stations st ON o.station_id = st.station_id
2176 where coalesce(price,0) <> 0 ');
2177
2178
2179 set @mysqlQuery = if (stationid <> '', concat(@mysqlQuery, ' and o.station_id in (',stationid,')'),@mysqlQuery);
2180 set @mysqlQuery = if (userid <> '', concat(@mysqlQuery, ' and o.id in (',userid,')'),@mysqlQuery);
2181
2182 /*Chỉ group theo user*/
2183
2184 set @mysqlQuery = concat('select id,username, sum(moneyreceived) as moneyreceived, sum(surcharge) as surcharge, sum(totalticket) as totalticket, sum(ticketprice) as ticketprice,station_name, station_id from (',@mysqlQuery,') as query group by id,username,station_name, station_id');
2185
2186
2187 prepare stmt from @mysqlQuery;
2188 execute stmt;
2189
2190
2191 DEALLOCATE PREPARE stmt;
2192
2193END ;;
2194DELIMITER ;
2195/*!50003 SET sql_mode = @saved_sql_mode */ ;
2196/*!50003 SET character_set_client = @saved_cs_client */ ;
2197/*!50003 SET character_set_results = @saved_cs_results */ ;
2198/*!50003 SET collation_connection = @saved_col_connection */ ;
2199/*!50003 DROP PROCEDURE IF EXISTS `sp_ssRevenueByUsers1` */;
2200/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2201/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2202/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2203/*!50003 SET character_set_client = utf8 */ ;
2204/*!50003 SET character_set_results = utf8 */ ;
2205/*!50003 SET collation_connection = utf8_general_ci */ ;
2206/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2207/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2208DELIMITER ;;
2209CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssRevenueByUsers1`(in startdate varchar(20), in enddate varchar(20),in shiftid varchar (2000), in stationid nvarchar(200), in userid nvarchar(100))
2210BEGIN
2211set @mysqlQuery=concat( 'select o.id, o.username,0 as moneyreceived ,0 as surcharge,coalesce(totalticket,0) as totalticket,coalesce(price,0) as ticketprice,st.station_name, o.station_id from office_users o inner join
2212 (select userid, sum(price) as price, count(id) as totalTicket from ss_reservations
2213 where status = 3 and shift in (',shiftid,') and date(date_ticket) between ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and ''',STR_TO_DATE(enddate,'%Y-%m-%d'),''' group by userid
2214 ) as ticket on o.id = ticket.userid
2215 inner join stations st ON o.station_id = st.station_id
2216 where coalesce(price,0) <> 0');
2217
2218
2219 set @mysqlQuery = if (stationid <> '', concat(@mysqlQuery, ' and o.station_id in (',stationid,')'),@mysqlQuery);
2220 set @mysqlQuery = if (userid <> '', concat(@mysqlQuery, ' and o.id in (',userid,')'),@mysqlQuery);
2221
2222 /*Chỉ group theo user*/
2223
2224 set @mysqlQuery = concat('select id,username, sum(moneyreceived) as moneyreceived, sum(surcharge) as surcharge, sum(totalticket) as totalticket, sum(ticketprice) as ticketprice,station_name, station_id from (',@mysqlQuery,') as query group by id,username,station_name, station_id');
2225
2226
2227 prepare stmt from @mysqlQuery;
2228 execute stmt;
2229
2230
2231 DEALLOCATE PREPARE stmt;
2232
2233END ;;
2234DELIMITER ;
2235/*!50003 SET sql_mode = @saved_sql_mode */ ;
2236/*!50003 SET character_set_client = @saved_cs_client */ ;
2237/*!50003 SET character_set_results = @saved_cs_results */ ;
2238/*!50003 SET collation_connection = @saved_col_connection */ ;
2239/*!50003 DROP PROCEDURE IF EXISTS `sp_ssShiftInfoByDate` */;
2240/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2241/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2242/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2243/*!50003 SET character_set_client = utf8 */ ;
2244/*!50003 SET character_set_results = utf8 */ ;
2245/*!50003 SET collation_connection = utf8_general_ci */ ;
2246/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2247/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2248DELIMITER ;;
2249CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssShiftInfoByDate`(in datet date, in shiftid int)
2250BEGIN
2251 select s.*, b.number from ss_shift_info s inner join bus b on s.bus_number = b.id where shift = shiftid and date = datet;
2252END ;;
2253DELIMITER ;
2254/*!50003 SET sql_mode = @saved_sql_mode */ ;
2255/*!50003 SET character_set_client = @saved_cs_client */ ;
2256/*!50003 SET character_set_results = @saved_cs_results */ ;
2257/*!50003 SET collation_connection = @saved_col_connection */ ;
2258/*!50003 DROP PROCEDURE IF EXISTS `sp_ssTaxAdjustment` */;
2259/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2260/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2261/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2262/*!50003 SET character_set_client = utf8 */ ;
2263/*!50003 SET character_set_results = utf8 */ ;
2264/*!50003 SET collation_connection = utf8_general_ci */ ;
2265/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2266/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2267DELIMITER ;;
2268CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssTaxAdjustment`(in startdate datetime, in enddate datetime)
2269BEGIN
2270select s.station_id, s.station_name, coalesce(totalTicket,0) as totalTicket, coalesce(price,0) as totalPrice, coalesce(taxtotalTicket,0) as totalTaxTicket, coalesce(taxprice,0) as totalTaxPrice,
2271 case when s.express_tax=1 then 'Xuất ngay(All)' when s.express_tax=2 then 'Xuất sau(All)' when s.express_tax=3 then 'Xuất ngay' else 'Xuất sau' end as TaxType,
2272 case when coalesce(price,0) = 0 then 100 else 100* coalesce(taxprice,0)/ coalesce(price,0) end as Percentage,
2273 case when coalesce(price,0) = 0 then 'Äã cân' when coalesce(taxprice,0)/ coalesce(price,0) >=0.7 then 'Äã cân' else 'ChÆ°a cân' end as AdjustmentStatus
2274
2275
2276 from stations s left outer join
2277
2278 ( select sum(price) as price, count(ticketid) as totalTicket, station_id from vgetshortshift_basic
2279where status = 3 and coalesce(stype,1) = 1 and date(date_ticket) between startdate and enddate group by station_id) as ticket on s.station_id = ticket.station_id
2280
2281left outer join
2282
2283 ( select sum(price) as taxprice, count(ticketid) as taxtotalTicket, station_id from vgetshortshift_basic
2284where status = 3 and coalesce(stype,1) = 1 and tax =1 and date(date_ticket) between startdate and enddate group by station_id) as taxticket on s.station_id = taxticket.station_id
2285
2286inner join pm_stationpermissions p on s.station_id = p.stationid and permissionid =2;
2287
2288END ;;
2289DELIMITER ;
2290/*!50003 SET sql_mode = @saved_sql_mode */ ;
2291/*!50003 SET character_set_client = @saved_cs_client */ ;
2292/*!50003 SET character_set_results = @saved_cs_results */ ;
2293/*!50003 SET collation_connection = @saved_col_connection */ ;
2294/*!50003 DROP PROCEDURE IF EXISTS `sp_ssTaxAdjustmentNew` */;
2295/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2296/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2297/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2298/*!50003 SET character_set_client = utf8 */ ;
2299/*!50003 SET character_set_results = utf8 */ ;
2300/*!50003 SET collation_connection = utf8_general_ci */ ;
2301/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2302/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2303DELIMITER ;;
2304CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssTaxAdjustmentNew`(in startdate datetime, in enddate datetime,in routetype nvarchar(5))
2305BEGIN
2306set @mysqlQuery=concat('select s.station_id, s.station_name, coalesce(totalTicket,0) as totalTicket, coalesce(price,0) as totalPrice, coalesce(taxtotalTicket,0) as totalTaxTicket, coalesce(taxprice,0) as totalTaxPrice,
2307 case when s.express_tax=1 then ''Xuất ngay(All)'' when s.express_tax=2 then ''Xuất sau(All)'' when s.express_tax=3 then ''Xuất ngay'' else ''Xuất sau'' end as TaxType,
2308 case when coalesce(price,0) = 0 then 100 else 100* coalesce(taxprice,0)/ coalesce(price,0) end as Percentage,
2309 case when coalesce(price,0) = 0 then ''Äã cân'' when coalesce(taxprice,0)/ coalesce(price,0) >=0.7 then ''Äã cân'' else ''ChÆ°a cân'' end as AdjustmentStatus
2310
2311
2312 from stations s left outer join
2313
2314 ( select sum(price) as price, count(ticketid) as totalTicket, station_id from vgetshortshift_basic
2315where status = 3 and coalesce(stype,1) in ',routetype,' and date(date_ticket) between ''',startdate,''' and ''',enddate,''' group by station_id) as ticket on s.station_id = ticket.station_id
2316
2317left outer join
2318
2319 ( select sum(price) as taxprice, count(ticketid) as taxtotalTicket, station_id from vgetshortshift_basic
2320where status = 3 and coalesce(stype,1) in ',routetype,' and tax = 1 and date(date_ticket) between ''',startdate,''' and ''',enddate,''' group by station_id) as taxticket on s.station_id = taxticket.station_id
2321
2322inner join pm_stationpermissions p on s.station_id = p.stationid and permissionid =2');
2323
2324
2325
2326PREPARE stmt FROM @mysqlQuery;
2327EXECUTE stmt;
2328DEALLOCATE PREPARE stmt;
2329
2330
2331END ;;
2332DELIMITER ;
2333/*!50003 SET sql_mode = @saved_sql_mode */ ;
2334/*!50003 SET character_set_client = @saved_cs_client */ ;
2335/*!50003 SET character_set_results = @saved_cs_results */ ;
2336/*!50003 SET collation_connection = @saved_col_connection */ ;
2337/*!50003 DROP PROCEDURE IF EXISTS `sp_ssTaxReportRevenueByBus` */;
2338/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2339/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2340/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2341/*!50003 SET character_set_client = utf8 */ ;
2342/*!50003 SET character_set_results = utf8 */ ;
2343/*!50003 SET collation_connection = utf8_general_ci */ ;
2344/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2345/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2346DELIMITER ;;
2347CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssTaxReportRevenueByBus`(in startdate datetime, in enddate datetime, in routeType int)
2348BEGIN
2349SET @sql = NULL;
2350set session group_concat_max_len = 4000;
2351SELECT
2352 GROUP_CONCAT(DISTINCT
2353 CONCAT(
2354 'max(if(number=''',
2355 number,
2356 ''',concat(totalShift,''_'',totalmoneyreceived), 0)) AS ''',
2357 replace(number, ' ', ''),''''
2358 )
2359
2360 ) INTO @sql
2361from vsstaxreportbybus where date between startdate and enddate and
2362CASE when routeType >0 then Type = routeType
2363 when routeType =0 then Type in (1,2)
2364 when routeType =-1 then Type in (3,4)
2365 END
2366 ;
2367
2368-- neu khong chá»n type, phải xá» lý trÆ°á»›c view thà nh subquery để gom nhóm liệu theo type
2369
2370set @fromobject = CASE WHEN routeType>0 then'vsstaxreportbybus'
2371 WHEN routeType=0 then '(select date, number,sum(totalShift) totalShift , sum(totalmoneyreceived) totalmoneyreceived from vsstaxreportbybus where Type in (1,2) group by date, number)'
2372 WHEN routeType=-1 then '(select date, number,sum(totalShift) totalShift , sum(totalmoneyreceived) totalmoneyreceived from vsstaxreportbybus where Type in (3,4) group by date, number)'
2373 END;
2374
2375-- neu chon type, day type vao dieu kien where
2376set @wherecondition = if(routeType<=0,'', concat(' and Type =',routetype));
2377
2378SET @sql = case when @sql is not null then CONCAT('SELECT pt.date, sum(totalShift) as totalShift, sum(totalmoneyreceived) as totalmoneyreceived,', @sql, ' from ',@fromobject,' pt where date between ''',startdate,''' and ''',enddate, '''',@wherecondition,'
2379
2380
2381group by pt.date') else 'select 1' end;
2382
2383
2384-- coalesce(TicketPrice, 0)/1000 + coalesce( moneynoticket,0)/1000*0.8 + moneyroundtrip/1000 -totalpaymoney/1000
2385
2386PREPARE stmt FROM @sql;
2387EXECUTE stmt;
2388DEALLOCATE PREPARE stmt;
2389END ;;
2390DELIMITER ;
2391/*!50003 SET sql_mode = @saved_sql_mode */ ;
2392/*!50003 SET character_set_client = @saved_cs_client */ ;
2393/*!50003 SET character_set_results = @saved_cs_results */ ;
2394/*!50003 SET collation_connection = @saved_col_connection */ ;
2395/*!50003 DROP PROCEDURE IF EXISTS `sp_ssTaxReportRevenueByShift` */;
2396/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2397/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2398/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2399/*!50003 SET character_set_client = utf8 */ ;
2400/*!50003 SET character_set_results = utf8 */ ;
2401/*!50003 SET collation_connection = utf8_general_ci */ ;
2402/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2403/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2404DELIMITER ;;
2405CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssTaxReportRevenueByShift`(in startdate datetime, in enddate datetime, in routeType int)
2406BEGIN
2407
2408DECLARE done INT DEFAULT 0;
2409DECLARE thisDate varchar(10);
2410
2411DECLARE cur1 CURSOR FOR SELECT * FROM tmpDate;
2412DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
2413DROP temporary TABLE IF EXISTS tmpDate;
2414CREATE temporary TABLE tmpDate (date varchar(20));
2415
2416DROP temporary TABLE IF EXISTS tmpShift;
2417CREATE temporary TABLE tmpShift (shiftid int, infoID int, shiftDate varchar(10), starttime time,info varchar(1000) CHARSET utf8, routeID int, busnumber varchar(50) , deliverysurcharge decimal(10,0) ,customernoticket int(11) ,moneynoticket decimal(10,0), moneyreceived decimal(10,0), paystation decimal(10,0) ,moneyeat decimal(10,0) ,paycustomer decimal(10,0) ,payother decimal(10,0) ,moneyCB decimal(10,0) ,totalticketroundtrip int(11) ,moneyroundtrip decimal(10,0) ,exposurecode varchar(45) ,totalticket int(11) ,moneyticket decimal(10,0) ,totalpaymoney decimal(10,0) ,realtotalmoney decimal(10,0) ,totalticketsoftware int(11) ,ticketfree int(11) ,ticketCB int(11),payhightwaytax decimal(10,0), correctedCustomerNo int, reason varchar(1000), routeType int, completed int );
2418
2419
2420-- Lay ra danh sach ngay trong khoang 2 ngay truyen vao
2421INSERT INTO tmpDate
2422select * from
2423(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
2424 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
2425 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
2426 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
2427 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
2428 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
2429where selected_date between startDate and endDate;
2430
2431
2432-- Duyet qua danh sach ngay, insert và o bảng tạm, cuối cùng sẽ query lấy hết dữ liệu trong đó ra
2433
2434 OPEN cur1;
2435 REPEAT
2436 FETCH cur1 INTO thisDate;
2437 IF NOT done THEN
2438 insert into tmpShift -- values(1,2,thisDate,'2016-2-21',1,22,3000,3000,3000,9000);
2439
2440 select s.id, coalesce(si.id,0) ,thisDate, s.start_Time,info, router_id,si.number, 0 as deliverysurcharge, customernoticket*0.8 as customernoticket ,
2441 (CASE WHEN date >= '2017-6-1'
2442 THEN coalesce(moneynoticket,0)/1000*0.6 ELSE coalesce(moneynoticket,0)/1000*0.8
2443 END) as moneynoticket,
2444 (CASE WHEN date >= '2017-6-1'
2445 THEN coalesce(TicketPrice,0)/1000 + coalesce(moneynoticket,0)/1000*0.6 ELSE coalesce(TicketPrice,0)/1000 + coalesce( moneynoticket,0)/1000*0.8
2446 END) as moneyreceived,
2447 paystation/1000,moneyeat/1000,paycustomer/1000,payother/1000,moneyCB/1000,totalticketroundtrip,moneyroundtrip/1000,si.exposurecode,coalesce(Tickets,0)+customernoticket*0.8 as totalticket,coalesce(TicketPrice, 0)/1000 moneyticket, totalpaymoney/1000,
2448 (CASE WHEN date >= '2017-6-1'
2449 THEN coalesce(TicketPrice,0)/1000 + coalesce(moneynoticket,0)/1000*0.6 + moneyroundtrip/1000 - totalpaymoney/1000 ELSE coalesce(TicketPrice,0)/1000 + coalesce(moneynoticket,0)/1000*0.8 + moneyroundtrip/1000 - totalpaymoney/1000
2450 END) as realtotalmoney,
2451 Tickets as totalticketsoftware,ticketfree,ticketCB,payhightwaytax/1000,correctedCustomerNo,reason, rt.RouteType, completed
2452 from ss_shifts s join (select ss_shift_info.*,bus.number from ss_shift_info left outer join bus on ss_shift_info.bus_number=bus.id
2453
2454 where date = thisDate) si on s.id = si.shift
2455 inner join ss_routers rt on s.router_id = rt.id
2456 left join (select count(id) as Tickets, sum(price) as TicketPrice, shift from ss_reservations where date = thisDate and tax =1 and parent_ticket is null Group by shift) as taxTicket on s.id = taxTicket.Shift
2457
2458
2459 ;
2460
2461 END IF;
2462 UNTIL done END REPEAT;
2463
2464 CLOSE cur1;
2465
2466-- neu co filter theo shift, thi gan filter vao
2467set @tempwhere =concat(' and routeType =',routetype,'');
2468
2469-- neu co filter theo shift, thi gan filter vao
2470set @wherecondition = case when routeType =0 then ' and routeType in(1,2) '
2471 when routeType =-1 then ' and routeType in(3,4) '
2472 else @tempwhere end;
2473
2474
2475
2476set @mysqlQuery = concat('select * from tmpShift where 1=1 ',@wherecondition,' order by shiftDate, routeID, starttime ');
2477
2478
2479prepare stmt from @mysqlQuery;
2480execute stmt;
2481deallocate prepare stmt;
2482
2483END ;;
2484DELIMITER ;
2485/*!50003 SET sql_mode = @saved_sql_mode */ ;
2486/*!50003 SET character_set_client = @saved_cs_client */ ;
2487/*!50003 SET character_set_results = @saved_cs_results */ ;
2488/*!50003 SET collation_connection = @saved_col_connection */ ;
2489/*!50003 DROP PROCEDURE IF EXISTS `sp_ssTaxRevenue` */;
2490/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2491/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2492/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2493/*!50003 SET character_set_client = utf8 */ ;
2494/*!50003 SET character_set_results = utf8 */ ;
2495/*!50003 SET collation_connection = utf8_general_ci */ ;
2496/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2497/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2498DELIMITER ;;
2499CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssTaxRevenue`(in startdate datetime, in enddate datetime)
2500BEGIN
2501
2502 select v.orderref, v.name,v.date, t.serri, t.id as taxid, v.taxcode, v.Phone, v.Price/1.1 as Price, v.Price - v.Price/1.1 as VAT, v.Price as Total, v.seat, t.timestamp, t.reservee, t.ticketing, v.start_time, v.destination, v.departure from vgetshortshift_basic v inner join ss_tax t on v.ticketid = t.reservation
2503 where date(t.date_ticket) between startdate and enddate and coalesce(stype,1)=1;
2504
2505
2506END ;;
2507DELIMITER ;
2508/*!50003 SET sql_mode = @saved_sql_mode */ ;
2509/*!50003 SET character_set_client = @saved_cs_client */ ;
2510/*!50003 SET character_set_results = @saved_cs_results */ ;
2511/*!50003 SET collation_connection = @saved_col_connection */ ;
2512/*!50003 DROP PROCEDURE IF EXISTS `sp_ssTaxRevenue20160731` */;
2513/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2514/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2515/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2516/*!50003 SET character_set_client = utf8 */ ;
2517/*!50003 SET character_set_results = utf8 */ ;
2518/*!50003 SET collation_connection = utf8_general_ci */ ;
2519/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2520/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2521DELIMITER ;;
2522CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssTaxRevenue20160731`(in startdate datetime, in enddate datetime)
2523BEGIN
2524
2525 select v.orderref, v.name,v.date, t.serri, t.id as taxid, v.taxcode, v.Phone, v.Price/1.1 as Price, v.Price - v.Price/1.1 as VAT, v.Price as Total, v.seat, t.timestamp, t.reservee, t.ticketing, v.start_time, v.destination, v.departure from vgetshortshift_basic v inner join ss_tax_20170211 t on v.ticketid = t.reservation
2526 where date(t.timestamp) between startdate and enddate;
2527
2528
2529END ;;
2530DELIMITER ;
2531/*!50003 SET sql_mode = @saved_sql_mode */ ;
2532/*!50003 SET character_set_client = @saved_cs_client */ ;
2533/*!50003 SET character_set_results = @saved_cs_results */ ;
2534/*!50003 SET collation_connection = @saved_col_connection */ ;
2535/*!50003 DROP PROCEDURE IF EXISTS `sp_ssTaxRevenue20160731_2` */;
2536/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2537/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2538/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2539/*!50003 SET character_set_client = utf8 */ ;
2540/*!50003 SET character_set_results = utf8 */ ;
2541/*!50003 SET collation_connection = utf8_general_ci */ ;
2542/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2543/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2544DELIMITER ;;
2545CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssTaxRevenue20160731_2`(in startdate datetime, in enddate datetime)
2546BEGIN
2547
2548 select v.orderref, v.name,v.date, t.serri, t.id as taxid, v.taxcode, v.Phone, v.Price/1.1 as Price, v.Price - v.Price/1.1 as VAT, v.Price as Total, t.timestamp, t.reservee, t.ticketing, v.start_time, v.destination, v.departure from vgetshortshift_forreport v inner join ss_tax_20170211 t on v.ticketid = t.reservation
2549 where date(t.timestamp) between startdate and enddate;
2550
2551
2552END ;;
2553DELIMITER ;
2554/*!50003 SET sql_mode = @saved_sql_mode */ ;
2555/*!50003 SET character_set_client = @saved_cs_client */ ;
2556/*!50003 SET character_set_results = @saved_cs_results */ ;
2557/*!50003 SET collation_connection = @saved_col_connection */ ;
2558/*!50003 DROP PROCEDURE IF EXISTS `sp_ssViewRedRevenueByDay` */;
2559/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2560/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2561/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2562/*!50003 SET character_set_client = utf8 */ ;
2563/*!50003 SET character_set_results = utf8 */ ;
2564/*!50003 SET collation_connection = utf8_general_ci */ ;
2565/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2566/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2567DELIMITER ;;
2568CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssViewRedRevenueByDay`(in startdate datetime, in enddate datetime)
2569BEGIN
2570
2571DECLARE done INT DEFAULT 0;
2572DECLARE thisDate varchar(10);
2573
2574DECLARE cur1 CURSOR FOR SELECT * FROM tempDate;
2575DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
2576DROP temporary TABLE IF EXISTS tempDate;
2577CREATE temporary TABLE tempDate (date varchar(20));
2578
2579DROP temporary TABLE IF EXISTS tmpRedRevenue;
2580CREATE temporary TABLE tmpRedRevenue (revenueDate varchar(10), totalShift int, totalCustomer int, totalTicketHN int,totalTicketHP int, ticketPriceHN decimal(11), ticketPriceHP decimal(11), deliverysurcharge decimal(11), deliverysurchargeVP decimal(11), CB decimal (11), otherrevenue decimal(11), totalRevenue decimal(11), outHN decimal(11), outHP decimal(11), discount decimal(11), otherOut decimal(11), totalOut decimal(11), margin decimal(11));
2581
2582
2583-- Lay ra danh sach ngay trong khoang 2 ngay truyen vao
2584INSERT INTO tempDate
2585select * from
2586(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
2587 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
2588 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
2589 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
2590 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
2591 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
2592where selected_date between startDate and endDate;
2593
2594
2595-- Duyet qua danh sach ngay, insert và o bảng tạm, cuối cùng sẽ query lấy hết dữ liệu trong đó ra
2596
2597 OPEN cur1;
2598 REPEAT
2599 FETCH cur1 INTO thisDate;
2600 IF NOT done THEN
2601 insert into tmpRedRevenue -- values(1,2,thisDate,'2016-2-21',1,22,3000,3000,3000,9000);
2602
2603
2604select main.Date as revenueDate, main.totalshift, main.totalCustomer, coalesce(totalTicketHN,0) as totalTicketHN, coalesce(totalTicketHP,0) as totalTicketHP, coalesce(ticketPriceHN,0) as ticketPriceHN, coalesce(ticketPriceHP,0) as ticketPriceHP, coalesce(deliverysurcharge,0) as deliverysurcharge, coalesce(cargo,0) as deliverysurchargeVP, coalesce(CB,0) as CB, coalesce(extra,0) +contract as otherRevenue, totalRevenue + coalesce(CB,0) +coalesce(extra,0) as totalRevenue ,coalesce(OutStationHN,0) as OutStationHN, coalesce(OutStationHP,0) as OutStationHP, coalesce(Discount,0) as Discount, otherOut, totalOut, margin+coalesce(extra,0) as margin
2605
2606 from (select date, count(id) as totalShift,sum(customerstation +customerroad) as totalCustomer, sum(deliverysurcharge) as deliverysurcharge, sum(coalesce(contract,0)) as contract, sum(moneyCB) as CB, sum(totalmoneyReceived) as totalRevenue, sum(paycustomer) as Discount, sum(payother+ moneyeat) as otherOut, sum(totalPaymoney) as totalOut,sum(RealMoney) as Margin from ss_red_shift_info info where date = thisDate and totalMoneyReceived is not null group by date)
2607as main left outer join (select date, sum(customerstation +customerroad) as totalTicketHN, sum(moneycustomer) as ticketPriceHN, sum(paystation) as OutStationHN from ss_red_shift_info info inner join ss_shifts ss on info.shift = ss.id where date = thisDate and ss.router_id = 6 and totalMoneyReceived is not null group by date)
2608as HN on main.date = HN.date left outer join (select date, sum(customerstation +customerroad) as totalTicketHP, sum(moneycustomer) as ticketPriceHP, sum(paystation) as OutStationHP from ss_red_shift_info info inner join ss_shifts ss on info.shift = ss.id where date = thisDate and ss.router_id = 5 and totalMoneyReceived is not null group by date)
2609as HP on main.date=HP.date left join (select date(updated) as date, sum(totalmoney) as extra from ss_ref_red_shiftinfo where date(updated) = thisDate)
2610as extra on main.date = extra.date left join (select sum(coalesce(real_money,0))as cargo, date from ss_cargo where date = thisDate group by date) as sscargo on main.date = sscargo.date ;
2611
2612 END IF;
2613 UNTIL done END REPEAT;
2614
2615 CLOSE cur1;
2616
2617
2618select * from tmpRedRevenue;
2619
2620END ;;
2621DELIMITER ;
2622/*!50003 SET sql_mode = @saved_sql_mode */ ;
2623/*!50003 SET character_set_client = @saved_cs_client */ ;
2624/*!50003 SET character_set_results = @saved_cs_results */ ;
2625/*!50003 SET collation_connection = @saved_col_connection */ ;
2626/*!50003 DROP PROCEDURE IF EXISTS `sp_ssViewRedRevenueByDay_tax` */;
2627/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2628/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2629/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2630/*!50003 SET character_set_client = utf8 */ ;
2631/*!50003 SET character_set_results = utf8 */ ;
2632/*!50003 SET collation_connection = utf8_general_ci */ ;
2633/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2634/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2635DELIMITER ;;
2636CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssViewRedRevenueByDay_tax`(in startdate datetime, in enddate datetime)
2637BEGIN
2638
2639DECLARE done INT DEFAULT 0;
2640DECLARE thisDate varchar(10);
2641
2642DECLARE cur1 CURSOR FOR SELECT * FROM tempDate;
2643DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
2644DROP temporary TABLE IF EXISTS tempDate;
2645CREATE temporary TABLE tempDate (date varchar(20));
2646
2647DROP temporary TABLE IF EXISTS tmpRedRevenue;
2648CREATE temporary TABLE tmpRedRevenue (revenueDate varchar(10), totalShift int, totalCustomer int, totalTicketHN int,totalTicketHP int, ticketPriceHN decimal(11), ticketPriceHP decimal(11), deliverysurcharge decimal(11), deliverysurchargeVP decimal(11), CB decimal (11), otherrevenue decimal(11), totalRevenue decimal(11), outHN decimal(11), outHP decimal(11), discount decimal(11), otherOut decimal(11), totalOut decimal(11), margin decimal(11));
2649
2650
2651-- Lay ra danh sach ngay trong khoang 2 ngay truyen vao
2652INSERT INTO tempDate
2653select * from
2654(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
2655 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
2656 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
2657 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
2658 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
2659 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
2660where selected_date between startDate and endDate;
2661
2662
2663-- Duyet qua danh sach ngay, insert và o bảng tạm, cuối cùng sẽ query lấy hết dữ liệu trong đó ra
2664
2665 OPEN cur1;
2666 REPEAT
2667 FETCH cur1 INTO thisDate;
2668 IF NOT done THEN
2669 insert into tmpRedRevenue -- values(1,2,thisDate,'2016-2-21',1,22,3000,3000,3000,9000);
2670
2671
2672select main.Date as revenueDate, main.totalshift, main.totalCustomer, coalesce(totalTicketHN,0) as totalTicketHN, coalesce(totalTicketHP,0) as totalTicketHP, coalesce(ticketPriceHN,0)*0.7 as ticketPriceHN, coalesce(ticketPriceHP,0) *0.7 as ticketPriceHP, coalesce(deliverysurcharge,0) as deliverysurcharge, coalesce(cargo,0) as deliverysurchargeVP, coalesce(CB,0) as CB, coalesce(extra,0) +contract as otherRevenue, totalRevenue + coalesce(CB,0) +coalesce(extra,0) - coalesce( ticketPriceHN,0) *0.3 - coalesce(ticketPriceHP,0) *0.3 as totalRevenue ,coalesce( OutStationHN,0) as OutStationHN,coalesce( OutStationHP,0) as OutStationHP, coalesce(Discount,0) as Discount, otherOut, totalOut, margin + coalesce(extra,0) - coalesce( ticketPriceHN,0) *0.3 - coalesce(ticketPriceHP,0) *0.3 as Margin
2673
2674 from (select date, count(id) as totalShift,sum(customerstation +customerroad) as totalCustomer, sum(deliverysurcharge) as deliverysurcharge, sum(coalesce(contract,0)) as contract, sum(moneyCB) as CB, sum(totalmoneyReceived) as totalRevenue, sum(paycustomer) as Discount, sum(payother+ moneyeat) as otherOut, sum(totalPaymoney) as totalOut,sum(RealMoney) as Margin from ss_red_shift_info info where date = thisDate and totalMoneyReceived is not null group by date)
2675as main left join (select date, sum(customerstation +customerroad) as totalTicketHN, sum(moneycustomer) as ticketPriceHN, sum(paystation) as OutStationHN from ss_red_shift_info info inner join ss_shifts ss on info.shift = ss.id where date = thisDate and ss.router_id = 6 and totalMoneyReceived is not null group by date)
2676as HN on main.date = HN.date left join (select date, sum(customerstation +customerroad) as totalTicketHP, sum(moneycustomer) as ticketPriceHP, sum(paystation) as OutStationHP from ss_red_shift_info info inner join ss_shifts ss on info.shift = ss.id where date = thisDate and ss.router_id = 5 and totalMoneyReceived is not null group by date)
2677as HP on main.date=HP .date left join (select date(updated) as date, sum(totalmoney) as extra from ss_ref_red_shiftinfo where date(updated) = thisDate)
2678as extra on main.date = extra.date left join (select sum(coalesce(real_money,0))as cargo, date from ss_cargo where date = thisDate group by date) as sscargo on main.date = sscargo.date ;
2679
2680 END IF;
2681 UNTIL done END REPEAT;
2682
2683 CLOSE cur1;
2684
2685
2686select * from tmpRedRevenue;
2687
2688END ;;
2689DELIMITER ;
2690/*!50003 SET sql_mode = @saved_sql_mode */ ;
2691/*!50003 SET character_set_client = @saved_cs_client */ ;
2692/*!50003 SET character_set_results = @saved_cs_results */ ;
2693/*!50003 SET collation_connection = @saved_col_connection */ ;
2694/*!50003 DROP PROCEDURE IF EXISTS `sp_ssViewRedShiftDetails` */;
2695/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2696/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2697/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2698/*!50003 SET character_set_client = utf8 */ ;
2699/*!50003 SET character_set_results = utf8 */ ;
2700/*!50003 SET collation_connection = utf8_general_ci */ ;
2701/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2702/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2703DELIMITER ;;
2704CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssViewRedShiftDetails`(in startdate varchar(20), in routerID int)
2705BEGIN
2706set @mysqlQuery=concat( 'select shift.id,shift.type, shift.router_id,start_time,seat_num ,TotalTicket, TotalTichke,TotalValue, st.number,hodem, ten, hodem2, ten2 from ss_shifts shift left outer join
2707 (select shift, count(seat_id) as TotalTichke from ss_seats where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and reservation =-1 and status =3
2708group by shift) as tichke on shift.id = tichke.shift
2709 left outer join (select shift, count(id) as TotalTicket, sum(price) as TotalValue from ss_reservations where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and status =3
2710group by shift) as ticket on shift.id = ticket.shift
2711 left outer join(select ss.shift, bus.number, v.hodem,v.ten, v2.hodem as hodem2,v2.ten as ten2 from ss_red_shift_info ss left outer join bus on ss.bus_number = bus.id
2712 left outer join vic_hoso v on ss.driver1=v.id
2713 left outer join vic_hoso v2 on ss.driver2=v2.id where date =''',STR_TO_DATE(startdate,'%Y-%m-%d'),''') as st ON shift.id = st.shift
2714 where (type = 1 OR type = 3) and active = 1 AND router_id = ',routerID,'
2715 UNION select shift.id,shift.type, shift.router_id,start_time,seat_num ,TotalTicket, TotalTichke,TotalValue, st.number,hodem, ten, hodem2, ten2 from ss_shifts shift left outer join
2716 (select shift, count(seat_id) as TotalTichke from ss_seats where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and reservation =-1 and status =3
2717group by shift) as tichke on shift.id = tichke.shift
2718 left outer join (select shift, count(id) as TotalTicket, sum(price) as TotalValue from ss_reservations where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and status =3
2719group by shift) as ticket on shift.id = ticket.shift
2720 left outer join(select ss.shift, bus.number, v.hodem,v.ten, v2.hodem as hodem2,v2.ten as ten2 from ss_red_shift_info ss left outer join bus on ss.bus_number = bus.id
2721 left outer join vic_hoso v on ss.driver1=v.id
2722 left outer join vic_hoso v2 on ss.driver2=v2.id where date =''',STR_TO_DATE(startdate,'%Y-%m-%d'),''') as st ON shift.id = st.shift,ss_shift_extra WHERE ss_shift_extra.shiftid = shift.id AND ss_shift_extra.date=''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' AND shift.type >= 2 and active = 1 AND shift.router_id = ',routerID,''
2723 );
2724
2725
2726
2727 prepare stmt from @mysqlQuery;
2728 execute stmt;
2729
2730 DEALLOCATE PREPARE stmt;
2731END ;;
2732DELIMITER ;
2733/*!50003 SET sql_mode = @saved_sql_mode */ ;
2734/*!50003 SET character_set_client = @saved_cs_client */ ;
2735/*!50003 SET character_set_results = @saved_cs_results */ ;
2736/*!50003 SET collation_connection = @saved_col_connection */ ;
2737/*!50003 DROP PROCEDURE IF EXISTS `sp_ssViewRevenueByShift` */;
2738/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2739/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2740/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2741/*!50003 SET character_set_client = utf8 */ ;
2742/*!50003 SET character_set_results = utf8 */ ;
2743/*!50003 SET collation_connection = utf8_general_ci */ ;
2744/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2745/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2746DELIMITER ;;
2747CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssViewRevenueByShift`(in startdate datetime, in enddate datetime, in shiftIDs varchar(2000), in busnumber varchar(20))
2748BEGIN
2749
2750DECLARE done INT DEFAULT 0;
2751DECLARE thisDate varchar(10);
2752
2753DECLARE cur1 CURSOR FOR SELECT * FROM tmpDate;
2754DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
2755DROP temporary TABLE IF EXISTS tmpDate;
2756CREATE temporary TABLE tmpDate (date varchar(20));
2757
2758DROP temporary TABLE IF EXISTS tmpShift;
2759CREATE temporary TABLE tmpShift (shiftid int, infoID int, shiftDate varchar(10), starttime time,info varchar(1000) CHARSET utf8, routeID int, busnumber varchar(50) , deliverysurcharge decimal(10,0) ,customernoticket int(11) ,moneynoticket decimal(10,0), moneyreceived decimal(10,0), paystation decimal(10,0) ,moneyeat decimal(10,0) ,paycustomer decimal(10,0) ,payother decimal(10,0) ,moneyCB decimal(10,0) ,totalticketroundtrip int(11) ,moneyroundtrip decimal(10,0) ,exposurecode varchar(45) ,totalticket int(11) ,moneyticket decimal(10,0) ,totalpaymoney decimal(10,0) ,realtotalmoney decimal(10,0) ,totalticketsoftware int(11) ,ticketfree int(11) ,ticketCB int(11),payhightwaytax decimal(10,0), correctedCustomerNo int, reason varchar(1000) ,officegoods decimal(10,0),officeexposure decimal(10,0));
2760
2761
2762-- Lay ra danh sach ngay trong khoang 2 ngay truyen vao
2763INSERT INTO tmpDate
2764select * from
2765(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
2766 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
2767 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
2768 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
2769 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
2770 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
2771where selected_date between startDate and endDate;
2772
2773
2774-- Duyet qua danh sach ngay, insert và o bảng tạm, cuối cùng sẽ query lấy hết dữ liệu trong đó ra
2775
2776 OPEN cur1;
2777 REPEAT
2778 FETCH cur1 INTO thisDate;
2779 IF NOT done THEN
2780 insert into tmpShift -- values(1,2,thisDate,'2016-2-21',1,22,3000,3000,3000,9000);
2781
2782 select s.id, coalesce(si.id,0) ,thisDate, s.start_Time,info, router_id,si.number, deliverysurcharge/1000,customernoticket,moneynoticket/1000, moneyreceived/1000, paystation/1000,moneyeat/1000,paycustomer/1000,payother/1000,moneyCB/1000,totalticketroundtrip,moneyroundtrip/1000,si.exposurecode,totalticket,moneyticket/1000,totalpaymoney/1000,realtotalmoney/1000,totalticketsoftware,ticketfree,ticketCB,payhightwaytax/1000,correctedCustomerNo,reason,officegoods/1000,officeexposure/1000
2783 from ss_shifts s left outer join (select ss_shift_info.*,bus.number from ss_shift_info left outer join bus on ss_shift_info.bus_number=bus.id
2784
2785 where date =thisDate) si on s.id = si.shift
2786 inner join ss_routers rt on s.router_id = rt.id
2787 where rt.ID in (1,2)
2788
2789 ;
2790
2791 END IF;
2792 UNTIL done END REPEAT;
2793
2794 CLOSE cur1;
2795
2796-- neu co filter theo shift, thi gan filter vao
2797set @tempwhere =concat(' and shiftid in (',shiftIDs,')');
2798
2799-- neu co filter theo shift, thi gan filter vao
2800set @busnumber =concat(' and busnumber =''',busnumber,'''');
2801
2802set @wherecondition = if(shiftIDs ='','', @tempwhere);
2803
2804set @wherecondition = if(busnumber ='',@wherecondition, concat(@wherecondition, @busnumber));
2805
2806set @mysqlQuery = concat('select * from tmpShift where 1=1 ',@wherecondition,' order by shiftDate, routeID, starttime ');
2807
2808
2809prepare stmt from @mysqlQuery;
2810execute stmt;
2811deallocate prepare stmt;
2812
2813END ;;
2814DELIMITER ;
2815/*!50003 SET sql_mode = @saved_sql_mode */ ;
2816/*!50003 SET character_set_client = @saved_cs_client */ ;
2817/*!50003 SET character_set_results = @saved_cs_results */ ;
2818/*!50003 SET collation_connection = @saved_col_connection */ ;
2819/*!50003 DROP PROCEDURE IF EXISTS `sp_ssViewRevenueByShift1` */;
2820/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2821/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2822/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2823/*!50003 SET character_set_client = utf8 */ ;
2824/*!50003 SET character_set_results = utf8 */ ;
2825/*!50003 SET collation_connection = utf8_general_ci */ ;
2826/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2827/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2828DELIMITER ;;
2829CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssViewRevenueByShift1`(in startdate datetime, in enddate datetime, in shiftIDs varchar(200), in busnumber varchar(20))
2830BEGIN
2831
2832DECLARE done INT DEFAULT 0;
2833DECLARE thisDate varchar(10);
2834
2835DECLARE cur1 CURSOR FOR SELECT * FROM tmpDate;
2836DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
2837DROP temporary TABLE IF EXISTS tmpDate;
2838CREATE temporary TABLE tmpDate (date varchar(20));
2839
2840DROP temporary TABLE IF EXISTS tmpShift;
2841CREATE temporary TABLE tmpShift (shiftid int, infoID int, shiftDate varchar(10), starttime time, routeID int, busnumber varchar(50) , totalTicket int, ticketPrice decimal(11), noticketPrice decimal(11), deliverysurcharge decimal(11), totalrevenue decimal(11));
2842
2843
2844-- Lay ra danh sach ngay trong khoang 2 ngay truyen vao
2845INSERT INTO tmpDate
2846select * from
2847(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
2848 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
2849 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
2850 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
2851 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
2852 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
2853where selected_date between startDate and endDate;
2854
2855
2856-- Duyet qua danh sach ngay, insert và o bảng tạm, cuối cùng sẽ query lấy hết dữ liệu trong đó ra
2857
2858 OPEN cur1;
2859 REPEAT
2860 FETCH cur1 INTO thisDate;
2861 IF NOT done THEN
2862 insert into tmpShift -- values(1,2,thisDate,'2016-2-21',1,22,3000,3000,3000,9000);
2863
2864 select s.id, coalesce(si.id,0) ,thisDate, s.start_Time, router_id,si.number, coalesce(totalTicket,0), coalesce(price,0), coalesce( si.moneyreceived,0), coalesce( si.deliverysurcharge,0),coalesce(price,0) + coalesce( si.moneyreceived,0)+ coalesce( si.deliverysurcharge,0)
2865 from ss_shifts s left outer join (select ss_shift_info.*,bus.number from ss_shift_info left outer join bus on ss_shift_info.bus_number=bus.id where date =thisDate) si on s.id = si.shift
2866 left outer join (select shift, sum(price) as price, count(ticketid) as totalTicket from vgetshortshift
2867 where status = 3 and date(date) =thisDate group by shift) ticket on s.id = ticket.shift
2868 ;
2869
2870 END IF;
2871 UNTIL done END REPEAT;
2872
2873 CLOSE cur1;
2874
2875-- neu co filter theo shift, thi gan filter vao
2876set @tempwhere =concat(' and shiftid in (',shiftIDs,')');
2877
2878-- neu co filter theo shift, thi gan filter vao
2879set @busnumber =concat(' and busnumber =''',busnumber,'''');
2880
2881set @wherecondition = if(shiftIDs ='','', @tempwhere);
2882
2883set @wherecondition = if(busnumber ='',@wherecondition, concat(@wherecondition, @busnumber));
2884
2885set @mysqlQuery = concat('select * from tmpShift where 1=1 ',@wherecondition,' order by shiftDate, routeID, starttime ');
2886
2887
2888prepare stmt from @mysqlQuery;
2889execute stmt;
2890deallocate prepare stmt;
2891
2892END ;;
2893DELIMITER ;
2894/*!50003 SET sql_mode = @saved_sql_mode */ ;
2895/*!50003 SET character_set_client = @saved_cs_client */ ;
2896/*!50003 SET character_set_results = @saved_cs_results */ ;
2897/*!50003 SET collation_connection = @saved_col_connection */ ;
2898/*!50003 DROP PROCEDURE IF EXISTS `sp_ssViewShiftDetails` */;
2899/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2900/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2901/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2902/*!50003 SET character_set_client = utf8 */ ;
2903/*!50003 SET character_set_results = utf8 */ ;
2904/*!50003 SET collation_connection = utf8_general_ci */ ;
2905/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2906/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2907DELIMITER ;;
2908CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssViewShiftDetails`(in startdate varchar(20), in routerID varchar(20))
2909BEGIN
2910set @mysqlQuery=concat( 'select shift.id,shift.type, shift.router_id,rt.isred,rt.routetype,start_time,seat_num ,TotalTicket, null as TotalTichke,TotalValue, st.number,hodem, ten, hodem2, ten2, completed, case when ls.id is null then 0 else 1 end as locked, bus_numbers, st.moneyreceived from ss_shifts shift
2911 left outer join (select shift, count(id) as TotalTicket, sum(price) as TotalValue from ss_reservations where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and status =3 and (deleted is null or deleted = 0)
2912group by shift) as ticket on shift.id = ticket.shift
2913 left outer join(select ss.moneyreceived, ss.shift, bus.number, completed, v.hodem,v.ten, v2.hodem as hodem2,v2.ten as ten2, ss.bus_numbers from ss_shift_info ss left outer join bus on ss.bus_number = bus.id
2914 left outer join vic_hoso v on ss.driver1=v.id
2915 left outer join vic_hoso v2 on ss.driver2=v2.id where date =''',STR_TO_DATE(startdate,'%Y-%m-%d'),''') as st ON shift.id = st.shift
2916 inner join ss_routers rt ON shift.router_id = rt.id
2917 left join ss_lockshifts ls on shift.id = ls.ssShiftID and ls.Date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),'''
2918
2919 where (shift.type = 1 OR (rt.routetype <> 5 AND shift.type = 3)) and active = 1 AND router_id in (',routerID,' )
2920 UNION select shift.id,shift.type, shift.router_id,rt.isred,rt.routetype,start_time,seat_num ,TotalTicket,null as TotalTichke,TotalValue, st.number,hodem, ten, hodem2, ten2, completed, case when ls.id is null then 0 else 1 end as locked, bus_numbers, st.moneyreceived from ss_shifts shift left outer join
2921 (select shift, count(id) as TotalTicket, sum(price) as TotalValue from ss_reservations where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and status =3 and (deleted is null or deleted = 0)
2922group by shift) as ticket on shift.id = ticket.shift
2923 left outer join(select ss.moneyreceived, ss.shift, completed, bus.number, v.hodem,v.ten, v2.hodem as hodem2,v2.ten as ten2, ss.bus_numbers from ss_shift_info ss left outer join bus on ss.bus_number = bus.id
2924 left outer join vic_hoso v on ss.driver1=v.id
2925 left outer join vic_hoso v2 on ss.driver2=v2.id where date =''',STR_TO_DATE(startdate,'%Y-%m-%d'),''') as st ON shift.id = st.shift
2926 left join ss_lockshifts ls on shift.id = ls.ssShiftID and ls.Date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),'''
2927inner join ss_routers rt ON shift.router_id = rt.id,ss_shift_extra WHERE ss_shift_extra.shiftid = shift.id AND ss_shift_extra.date=''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' AND (shift.type = 2 OR (rt.routetype = 5 AND shift.type = 3)) and active = 1 AND shift.router_id in (',routerID,')');
2928
2929
2930
2931 prepare stmt from @mysqlQuery;
2932 execute stmt;
2933
2934 DEALLOCATE PREPARE stmt;
2935END ;;
2936DELIMITER ;
2937/*!50003 SET sql_mode = @saved_sql_mode */ ;
2938/*!50003 SET character_set_client = @saved_cs_client */ ;
2939/*!50003 SET character_set_results = @saved_cs_results */ ;
2940/*!50003 SET collation_connection = @saved_col_connection */ ;
2941/*!50003 DROP PROCEDURE IF EXISTS `sp_ssViewShiftDetails2` */;
2942/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2943/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2944/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2945/*!50003 SET character_set_client = utf8 */ ;
2946/*!50003 SET character_set_results = utf8 */ ;
2947/*!50003 SET collation_connection = utf8_general_ci */ ;
2948/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2949/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2950DELIMITER ;;
2951CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssViewShiftDetails2`(in startdate varchar(20), in routerID int)
2952BEGIN
2953set @mysqlQuery=concat( 'select shift.id,shift.type, shift.router_id,start_time,seat_num ,TotalTicket, TotalTichke,TotalValue, st.number,hodem, ten, hodem2, ten2 from ss_shifts shift left outer join
2954 (select shift, count(seat_id) as TotalTichke from ss_seats where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and reservation =-1 and status =3
2955group by shift) as tichke on shift.id = tichke.shift
2956 left outer join (select shift, count(id) as TotalTicket, sum(price) as TotalValue from ss_reservations where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and status =3
2957group by shift) as ticket on shift.id = ticket.shift
2958 left outer join(select ss.shift, bus.number, v.hodem,v.ten, v2.hodem as hodem2,v2.ten as ten2 from ss_shift_info ss left outer join bus on ss.bus_number = bus.id
2959 left outer join vic_hoso v on ss.driver1=v.id
2960 left outer join vic_hoso v2 on ss.driver2=v2.id where date =''',STR_TO_DATE(startdate,'%Y-%m-%d'),''') as st ON shift.id = st.shift
2961 where (type = 1 OR type = 3) and active = 1 AND router_id = ',routerID,'
2962 UNION select shift.id,shift.type, shift.router_id,start_time,seat_num ,TotalTicket, TotalTichke,TotalValue, st.number,hodem, ten, hodem2, ten2 from ss_shifts shift left outer join
2963 (select shift, count(seat_id) as TotalTichke from ss_seats where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and reservation =-1 and status =3
2964group by shift) as tichke on shift.id = tichke.shift
2965 left outer join (select shift, count(id) as TotalTicket, sum(price) as TotalValue from ss_reservations where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and status =3
2966group by shift) as ticket on shift.id = ticket.shift
2967 left outer join(select ss.shift, bus.number, v.hodem,v.ten, v2.hodem as hodem2,v2.ten as ten2 from ss_shift_info ss left outer join bus on ss.bus_number = bus.id
2968 left outer join vic_hoso v on ss.driver1=v.id
2969 left outer join vic_hoso v2 on ss.driver2=v2.id where date =''',STR_TO_DATE(startdate,'%Y-%m-%d'),''') as st ON shift.id = st.shift,ss_shift_extra WHERE ss_shift_extra.shiftid = shift.id AND ss_shift_extra.date=''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' AND shift.type >= 2 and active = 1 AND shift.router_id = ',routerID,''
2970 );
2971
2972
2973
2974 prepare stmt from @mysqlQuery;
2975 execute stmt;
2976
2977 DEALLOCATE PREPARE stmt;
2978END ;;
2979DELIMITER ;
2980/*!50003 SET sql_mode = @saved_sql_mode */ ;
2981/*!50003 SET character_set_client = @saved_cs_client */ ;
2982/*!50003 SET character_set_results = @saved_cs_results */ ;
2983/*!50003 SET collation_connection = @saved_col_connection */ ;
2984/*!50003 DROP PROCEDURE IF EXISTS `sp_ssViewShiftDetails20170106` */;
2985/*!50003 SET @saved_cs_client = @@character_set_client */ ;
2986/*!50003 SET @saved_cs_results = @@character_set_results */ ;
2987/*!50003 SET @saved_col_connection = @@collation_connection */ ;
2988/*!50003 SET character_set_client = utf8 */ ;
2989/*!50003 SET character_set_results = utf8 */ ;
2990/*!50003 SET collation_connection = utf8_general_ci */ ;
2991/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
2992/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
2993DELIMITER ;;
2994CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssViewShiftDetails20170106`(in startdate varchar(20), in routerID varchar(20))
2995BEGIN
2996set @mysqlQuery=concat( 'select shift.id,shift.type, shift.router_id,rt.isred,rt.routetype,start_time,seat_num ,TotalTicket, TotalTichke,TotalValue, st.number,hodem, ten, hodem2, ten2 from ss_shifts shift left outer join
2997 (select shift, count(seat_id) as TotalTichke from ss_seats where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and reservation =-1 and status =3
2998group by shift) as tichke on shift.id = tichke.shift
2999 left outer join (select shift, count(id) as TotalTicket, sum(price) as TotalValue from ss_reservations where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and status =3
3000group by shift) as ticket on shift.id = ticket.shift
3001 left outer join(select ss.shift, bus.number, v.hodem,v.ten, v2.hodem as hodem2,v2.ten as ten2 from ss_shift_info ss left outer join bus on ss.bus_number = bus.id
3002 left outer join vic_hoso v on ss.driver1=v.id
3003 left outer join vic_hoso v2 on ss.driver2=v2.id where date =''',STR_TO_DATE(startdate,'%Y-%m-%d'),''') as st ON shift.id = st.shift
3004 inner join ss_routers rt ON shift.router_id = rt.id
3005 where (shift.type = 1 OR shift.type = 3) and active = 1 AND router_id in (',routerID,' )
3006 UNION select shift.id,shift.type, shift.router_id,rt.isred,rt.routetype,start_time,seat_num ,TotalTicket, TotalTichke,TotalValue, st.number,hodem, ten, hodem2, ten2 from ss_shifts shift left outer join
3007 (select shift, count(seat_id) as TotalTichke from ss_seats where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and reservation =-1 and status =3
3008group by shift) as tichke on shift.id = tichke.shift
3009 left outer join (select shift, count(id) as TotalTicket, sum(price) as TotalValue from ss_reservations where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and status =3
3010group by shift) as ticket on shift.id = ticket.shift
3011 left outer join(select ss.shift, bus.number, v.hodem,v.ten, v2.hodem as hodem2,v2.ten as ten2 from ss_shift_info ss left outer join bus on ss.bus_number = bus.id
3012 left outer join vic_hoso v on ss.driver1=v.id
3013 left outer join vic_hoso v2 on ss.driver2=v2.id where date =''',STR_TO_DATE(startdate,'%Y-%m-%d'),''') as st ON shift.id = st.shift
3014inner join ss_routers rt ON shift.router_id = rt.id,ss_shift_extra WHERE ss_shift_extra.shiftid = shift.id AND ss_shift_extra.date=''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' AND shift.type = 2 and active = 1 AND shift.router_id in (',routerID,')');
3015
3016
3017
3018 prepare stmt from @mysqlQuery;
3019 execute stmt;
3020
3021 DEALLOCATE PREPARE stmt;
3022END ;;
3023DELIMITER ;
3024/*!50003 SET sql_mode = @saved_sql_mode */ ;
3025/*!50003 SET character_set_client = @saved_cs_client */ ;
3026/*!50003 SET character_set_results = @saved_cs_results */ ;
3027/*!50003 SET collation_connection = @saved_col_connection */ ;
3028/*!50003 DROP PROCEDURE IF EXISTS `sp_ssViewShiftDetails20170107` */;
3029/*!50003 SET @saved_cs_client = @@character_set_client */ ;
3030/*!50003 SET @saved_cs_results = @@character_set_results */ ;
3031/*!50003 SET @saved_col_connection = @@collation_connection */ ;
3032/*!50003 SET character_set_client = utf8 */ ;
3033/*!50003 SET character_set_results = utf8 */ ;
3034/*!50003 SET collation_connection = utf8_general_ci */ ;
3035/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
3036/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
3037DELIMITER ;;
3038CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssViewShiftDetails20170107`(in startdate varchar(20), in routerID varchar(20))
3039BEGIN
3040set @mysqlQuery=concat( 'select shift.id,shift.type, shift.router_id,rt.isred,rt.routetype,start_time,seat_num ,TotalTicket, null as TotalTichke,TotalValue, st.number,hodem, ten, hodem2, ten2 from ss_shifts shift
3041 left outer join (select shift, count(id) as TotalTicket, sum(price) as TotalValue from ss_reservations where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and status =3
3042group by shift) as ticket on shift.id = ticket.shift
3043 left outer join(select ss.shift, bus.number, v.hodem,v.ten, v2.hodem as hodem2,v2.ten as ten2 from ss_shift_info ss left outer join bus on ss.bus_number = bus.id
3044 left outer join vic_hoso v on ss.driver1=v.id
3045 left outer join vic_hoso v2 on ss.driver2=v2.id where date =''',STR_TO_DATE(startdate,'%Y-%m-%d'),''') as st ON shift.id = st.shift
3046 inner join ss_routers rt ON shift.router_id = rt.id
3047 where (shift.type = 1 OR shift.type = 3) and active = 1 AND router_id in (',routerID,' )
3048 UNION select shift.id,shift.type, shift.router_id,rt.isred,rt.routetype,start_time,seat_num ,TotalTicket,null as TotalTichke,TotalValue, st.number,hodem, ten, hodem2, ten2 from ss_shifts shift left outer join
3049 (select shift, count(id) as TotalTicket, sum(price) as TotalValue from ss_reservations where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and status =3
3050group by shift) as ticket on shift.id = ticket.shift
3051 left outer join(select ss.shift, bus.number, v.hodem,v.ten, v2.hodem as hodem2,v2.ten as ten2 from ss_shift_info ss left outer join bus on ss.bus_number = bus.id
3052 left outer join vic_hoso v on ss.driver1=v.id
3053 left outer join vic_hoso v2 on ss.driver2=v2.id where date =''',STR_TO_DATE(startdate,'%Y-%m-%d'),''') as st ON shift.id = st.shift
3054inner join ss_routers rt ON shift.router_id = rt.id,ss_shift_extra WHERE ss_shift_extra.shiftid = shift.id AND ss_shift_extra.date=''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' AND shift.type = 2 and active = 1 AND shift.router_id in (',routerID,')');
3055
3056
3057
3058 prepare stmt from @mysqlQuery;
3059 execute stmt;
3060
3061 DEALLOCATE PREPARE stmt;
3062END ;;
3063DELIMITER ;
3064/*!50003 SET sql_mode = @saved_sql_mode */ ;
3065/*!50003 SET character_set_client = @saved_cs_client */ ;
3066/*!50003 SET character_set_results = @saved_cs_results */ ;
3067/*!50003 SET collation_connection = @saved_col_connection */ ;
3068/*!50003 DROP PROCEDURE IF EXISTS `sp_ssViewShiftDetailsNew` */;
3069/*!50003 SET @saved_cs_client = @@character_set_client */ ;
3070/*!50003 SET @saved_cs_results = @@character_set_results */ ;
3071/*!50003 SET @saved_col_connection = @@collation_connection */ ;
3072/*!50003 SET character_set_client = utf8 */ ;
3073/*!50003 SET character_set_results = utf8 */ ;
3074/*!50003 SET collation_connection = utf8_general_ci */ ;
3075/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
3076/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
3077DELIMITER ;;
3078CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssViewShiftDetailsNew`(in startdate varchar(20), in routerID varchar(20))
3079BEGIN
3080set @mysqlQuery=concat( 'select shift.id,shift.type, shift.router_id,rt.isred,rt.routetype,start_time,seat_num ,TotalTicket, null as TotalTichke,TotalValue, st.number,hodem, ten, hodem2, ten2, completed, case when ls.id is null then 0 else 1 end as locked from ss_shifts shift
3081 left outer join (select shift, count(id) as TotalTicket, sum(price) as TotalValue from ss_reservations where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and status =3
3082group by shift) as ticket on shift.id = ticket.shift
3083 left outer join(select ss.shift, bus.number, completed, v.hodem,v.ten, v2.hodem as hodem2,v2.ten as ten2 from ss_shift_info ss left outer join bus on ss.bus_number = bus.id
3084 left outer join vic_hoso v on ss.driver1=v.id
3085 left outer join vic_hoso v2 on ss.driver2=v2.id where date =''',STR_TO_DATE(startdate,'%Y-%m-%d'),''') as st ON shift.id = st.shift
3086 inner join ss_routers rt ON shift.router_id = rt.id
3087 left join ss_lockshifts ls on shift.id = ls.ssShiftID and ls.Date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),'''
3088
3089 where (shift.type = 1 OR shift.type = 3) and active = 1 AND router_id in (',routerID,' )
3090 UNION select shift.id,shift.type, shift.router_id,rt.isred,rt.routetype,start_time,seat_num ,TotalTicket,null as TotalTichke,TotalValue, st.number,hodem, ten, hodem2, ten2, completed, 0 as locked from ss_shifts shift left outer join
3091 (select shift, count(id) as TotalTicket, sum(price) as TotalValue from ss_reservations where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and status =3
3092group by shift) as ticket on shift.id = ticket.shift
3093 left outer join(select ss.shift, completed, bus.number, v.hodem,v.ten, v2.hodem as hodem2,v2.ten as ten2 from ss_shift_info ss left outer join bus on ss.bus_number = bus.id
3094 left outer join vic_hoso v on ss.driver1=v.id
3095 left outer join vic_hoso v2 on ss.driver2=v2.id where date =''',STR_TO_DATE(startdate,'%Y-%m-%d'),''') as st ON shift.id = st.shift
3096inner join ss_routers rt ON shift.router_id = rt.id,ss_shift_extra WHERE ss_shift_extra.shiftid = shift.id AND ss_shift_extra.date=''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' AND shift.type = 2 and active = 1 AND shift.router_id in (',routerID,')');
3097
3098
3099
3100 prepare stmt from @mysqlQuery;
3101 execute stmt;
3102
3103 DEALLOCATE PREPARE stmt;
3104END ;;
3105DELIMITER ;
3106/*!50003 SET sql_mode = @saved_sql_mode */ ;
3107/*!50003 SET character_set_client = @saved_cs_client */ ;
3108/*!50003 SET character_set_results = @saved_cs_results */ ;
3109/*!50003 SET collation_connection = @saved_col_connection */ ;
3110/*!50003 DROP PROCEDURE IF EXISTS `sp_ssViewShiftDetails_backup` */;
3111/*!50003 SET @saved_cs_client = @@character_set_client */ ;
3112/*!50003 SET @saved_cs_results = @@character_set_results */ ;
3113/*!50003 SET @saved_col_connection = @@collation_connection */ ;
3114/*!50003 SET character_set_client = utf8 */ ;
3115/*!50003 SET character_set_results = utf8 */ ;
3116/*!50003 SET collation_connection = utf8_general_ci */ ;
3117/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
3118/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
3119DELIMITER ;;
3120CREATE DEFINER=`root`@`%` PROCEDURE `sp_ssViewShiftDetails_backup`(in startdate varchar(20), in routerID int)
3121BEGIN
3122set @mysqlQuery=concat( 'select shift.id, shift.router_id,start_time,seat_num ,TotalTicket, TotalTichke,TotalValue, st.number,hodem, ten, hodem2, ten2 from ss_shifts shift left outer join
3123 (select shift, count(seat_id) as TotalTichke from ss_seats where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and reservation =-1 and status =3
3124group by shift) as tichke on shift.id = tichke.shift
3125 left outer join (select shift, count(id) as TotalTicket, sum(price) as TotalValue from ss_reservations where date = ''',STR_TO_DATE(startdate,'%Y-%m-%d'),''' and status =3
3126group by shift) as ticket on shift.id = ticket.shift
3127 left outer join(select ss.shift, bus.number, v.hodem,v.ten, v2.hodem as hodem2,v2.ten as ten2 from ss_shift_info ss left outer join bus on ss.bus_number = bus.id
3128 left outer join vic_hoso v on ss.driver1=v.id
3129 left outer join vic_hoso v2 on ss.driver2=v2.id where date =''',STR_TO_DATE(startdate,'%Y-%m-%d'),''') as st ON shift.id = st.shift
3130 where router_id = ',routerID);
3131
3132
3133
3134 prepare stmt from @mysqlQuery;
3135 execute stmt;
3136
3137 DEALLOCATE PREPARE stmt;
3138END ;;
3139DELIMITER ;