DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Auto_Delete`(IN `input_val` DATE)
    NO SQL
BEGIN
DELETE FROM `tbl_exit_ticket` WHERE DATE(exit_time) = input_val; 
DELETE FROM `tbl_entrance_ticket` WHERE DATE(entrance_time) = input_val;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `CashCollectATS`(IN `shiftId` INT)
    NO SQL
BEGIN 

DECLARE icId INT;
DECLARE startTime DateTime;
DECLARE endTime DateTime;

SELECT tbl_shift.ic_id,tbl_shift.shift_start,tbl_shift.shift_end INTO icId,startTime,endTime FROM tbl_shift WHERE id = shiftId;


SELECT icId ;

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `DeleteDuplicateEntranceTickets`()
    NO SQL
BEGIN
DECLARE duplicateId INT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur1 CURSOR FOR SELECT id FROM tbl_entrance_ticket_duplicate ORDER BY `tbl_entrance_ticket_duplicate`.`id` ASC;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur1;

    read_loop : LOOP

		FETCH Cur1 INTO duplicateId;
        
        IF done THEN
        	LEAVE read_loop;
        END IF;
        
        DELETE FROM tbl_entrance_ticket WHERE id = duplicateId;

    END LOOP;

    CLOSE cur1;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetExitTicketSummaryByCatAndIc`(IN `startDate` DATETIME, IN `endDate` DATETIME)
    NO SQL
BEGIN
	INSERT INTO rpt_hourly_traffic_exit(booth_id,cat1_count,cat2_count,cat3_count,cat4_count,catS_count,catPS_count,catPMS_count,catAmbulance_count,catNotAssigned_count,day,hour) SELECT
	tb.booth_id,
	IFNULL(SUM(case when tb.vehicle_category = 1 THEN tb.count END),0) Cat01,
	IFNULL(SUM(case when tb.vehicle_category = 2 THEN tb.count END),0) Cat02,
	IFNULL(SUM(case when tb.vehicle_category = 3 THEN tb.count END),0) Cat03,
	IFNULL(SUM(case when tb.vehicle_category = 4 THEN tb.count END),0) Cat04,
	IFNULL(SUM(case when tb.vehicle_category = 20 THEN tb.count END),0) CatS,
	IFNULL(SUM(case when tb.vehicle_category = 21 THEN tb.count END),0) CatPS,
	IFNULL(SUM(case when tb.vehicle_category = 22 THEN tb.count END),0) CatPMS,
	IFNULL(SUM(case when tb.vehicle_category = 30 THEN tb.count END),0) CatAmbulance,
	IFNULL(SUM(case when tb.vehicle_category = 99 THEN tb.count END),0) CatNotAssigned,
	DATE(startDate) AS day,
	HOUR(endDate) AS hour
	FROM
	(SELECT booth_id, t.vehicle_category, SUM(t.count) as count FROM 
		(SELECT booth_id, vehicle_category, COUNT(*) as count 
			FROM tbl_exit_ticket_2019_11 
			WHERE exit_time >= startDate AND exit_time < endDate
			GROUP BY booth_id, vehicle_category 
		UNION SELECT booth_id, vehicle_category, COUNT(*) as count 
			FROM tbl_exit_ticket_2019_12 
			WHERE exit_time >= startDate AND exit_time < endDate 
			GROUP BY booth_id, vehicle_category
		UNION SELECT booth_id, vehicle_category, COUNT(*) as count 
			FROM tbl_all_exit_ticket 
			WHERE exit_time >= startDate AND exit_time < endDate
			GROUP BY booth_id, vehicle_category 
		UNION SELECT booth_id, vehicle_category, COUNT(*) as count 
			FROM tbl_exit_ticket 
			WHERE exit_time >= startDate AND exit_time < endDate 
			GROUP BY booth_id, vehicle_category 
		) t 
	GROUP BY t.booth_id,t.vehicle_category ORDER BY t.booth_id ) tb GROUP BY tb.booth_id;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `DeleteDuplicateExitTickets`()
    NO SQL
BEGIN
DECLARE duplicateId INT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur1 CURSOR FOR SELECT id FROM tbl_exit_ticket_duplicate;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur1;

    read_loop : LOOP

		FETCH Cur1 INTO duplicateId;
        
        IF done THEN
        	LEAVE read_loop;
        END IF;
        
        DELETE FROM tbl_exit_ticket WHERE id = duplicateId;

    END LOOP;

    CLOSE cur1;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetDailyDirectionalTrafficExit`(IN `startDate` DATE, IN `endDate` DATE)
    NO SQL
BEGIN
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

INSERT INTO rpt_daily_traffic_Directional_exit(exit_ic,day,direction,cat1_count,cat2_count,cat3_count,cat4_count,catS_count,catPS_count,catPMS_count,catAmbulance_count,catNotAssigned_count)
SELECT 
	tb.exit_ic,
	tb.day,
	tb.direction,
    
	IFNULL(SUM(case when tb.vehicle_category = 1 THEN tb.count END),0) Cat01,
	IFNULL(SUM(case when tb.vehicle_category = 2 THEN tb.count END),0) Cat02,
	IFNULL(SUM(case when tb.vehicle_category = 3 THEN tb.count END),0) Cat03,
	IFNULL(SUM(case when tb.vehicle_category = 4 THEN tb.count END),0) Cat04,
	IFNULL(SUM(case when tb.vehicle_category = 20 THEN tb.count END),0) CatS,
	IFNULL(SUM(case when tb.vehicle_category = 21 THEN tb.count END),0) CatPS,
	IFNULL(SUM(case when tb.vehicle_category = 22 THEN tb.count END),0) CatPMS,
	IFNULL(SUM(case when tb.vehicle_category = 30 THEN tb.count END),0) CatAmbulance,
	IFNULL(SUM(case when tb.vehicle_category = 99 THEN tb.count END),0) CatNotAssigned
FROM 
(

SELECT t.day,d.name AS direction, t.exit_ic,t.vehicle_category,t.entrance_ic ,COUNT(t.vehicle_category) AS count FROM 
(SELECT date(exit_time) AS day, exit_ic,vehicle_category,entrance_ic FROM `tbl_all_exit_ticket` where date(exit_time) >= startDate and date(exit_time) <= endDate) t
LEFT JOIN tbl_route r ON r.source = t.entrance_ic AND r.destination = t.exit_ic
LEFT JOIN tbl_direction d ON r.direction = d.id
GROUP BY t.day,d.name, t.exit_ic,t.vehicle_category
UNION
SELECT t.day,d.name AS direction, t.exit_ic,t.vehicle_category,t.entrance_ic ,COUNT(t.vehicle_category) AS count FROM 
(SELECT date(exit_time) AS day, exit_ic,vehicle_category,entrance_ic FROM `tbl_exit_ticket` where date(exit_time) >= startDate and date(exit_time) <= endDate) t
LEFT JOIN tbl_route r ON r.source = t.entrance_ic AND r.destination = t.exit_ic
LEFT JOIN tbl_direction d ON r.direction = d.id
GROUP BY t.day,d.name, t.exit_ic,t.vehicle_category
UNION
SELECT t.day,d.name AS direction, t.exit_ic,t.vehicle_category,t.entrance_ic ,COUNT(t.vehicle_category) AS count FROM 
(SELECT date(exit_time) AS day, exit_ic,vehicle_category,entrance_ic FROM `tbl_exit_ticket_2019_11` where date(exit_time) >= startDate and date(exit_time) <= endDate) t
LEFT JOIN tbl_route r ON r.source = t.entrance_ic AND r.destination = t.exit_ic
LEFT JOIN tbl_direction d ON r.direction = d.id
GROUP BY t.day,d.name, t.exit_ic,t.vehicle_category
UNION
SELECT t.day,d.name AS direction, t.exit_ic,t.entrance_ic,t.vehicle_category ,COUNT(t.vehicle_category) AS count FROM 
(SELECT date(exit_time) AS day, exit_ic,vehicle_category,entrance_ic FROM `tbl_exit_ticket_2019_12` where date(exit_time) >= startDate and date(exit_time) <= endDate) t
LEFT JOIN tbl_route r ON r.source = t.entrance_ic AND r.destination = t.exit_ic
LEFT JOIN tbl_direction d ON r.direction = d.id
GROUP BY t.day,d.name, t.exit_ic,t.vehicle_category


) tb
GROUP BY tb.day, tb.direction,tb.exit_ic,tb.vehicle_category; 

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetDailyDirectionalTraffic`(IN `startDate` DATE, IN `endDate` DATE)
    NO SQL
BEGIN

INSERT INTO rpt_daily_traffic_Directional_entrance(entrance_ic,day,direction,cat1_count,cat2_count,cat3_count,cat4_count,catS_count,catPS_count,catPMS_count,catAmbulance_count,catNotAssigned_count)
SELECT 
	tb.entrance_ic,
	tb.day,
	tb.direction,
	IFNULL(SUM(case when tb.vehicle_category = 1 THEN tb.count END),0) Cat01,
	IFNULL(SUM(case when tb.vehicle_category = 2 THEN tb.count END),0) Cat02,
	IFNULL(SUM(case when tb.vehicle_category = 3 THEN tb.count END),0) Cat03,
	IFNULL(SUM(case when tb.vehicle_category = 4 THEN tb.count END),0) Cat04,
	IFNULL(SUM(case when tb.vehicle_category = 20 THEN tb.count END),0) CatS,
	IFNULL(SUM(case when tb.vehicle_category = 21 THEN tb.count END),0) CatPS,
	IFNULL(SUM(case when tb.vehicle_category = 22 THEN tb.count END),0) CatPMS,
	IFNULL(SUM(case when tb.vehicle_category = 30 THEN tb.count END),0) CatAmbulance,
	IFNULL(SUM(case when tb.vehicle_category = 99 THEN tb.count END),0) CatNotAssigned
FROM 
(

SELECT t.day,d.name AS direction, t.entrance_ic,t.vehicle_category ,COUNT(t.vehicle_category) AS count FROM 
(SELECT date(entrance_time) AS day, entrance_ic,vehicle_category,exit_ic FROM `tbl_all_exit_ticket` where date(entrance_time) >= startDate and date(entrance_time) <= endDate) t
LEFT JOIN tbl_route r ON r.source = t.entrance_ic AND r.destination = t.exit_ic
LEFT JOIN tbl_direction d ON r.direction = d.id
GROUP BY t.day,d.name, t.entrance_ic,t.vehicle_category
UNION
SELECT t.day,d.name AS direction, t.entrance_ic,t.vehicle_category ,COUNT(t.vehicle_category) AS count FROM 
(SELECT date(entrance_time) AS day, entrance_ic,vehicle_category,exit_ic FROM `tbl_exit_ticket` where date(entrance_time) >= startDate and date(entrance_time) <= endDate) t
LEFT JOIN tbl_route r ON r.source = t.entrance_ic AND r.destination = t.exit_ic
LEFT JOIN tbl_direction d ON r.direction = d.id
GROUP BY t.day,d.name, t.entrance_ic,t.vehicle_category
UNION
SELECT t.day,d.name AS direction, t.entrance_ic,t.vehicle_category ,COUNT(t.vehicle_category) AS count FROM 
(SELECT date(entrance_time) AS day, entrance_ic,vehicle_category,exit_ic FROM `tbl_exit_ticket_2019_11` where date(entrance_time) >= startDate and date(entrance_time) <= endDate) t
LEFT JOIN tbl_route r ON r.source = t.entrance_ic AND r.destination = t.exit_ic
LEFT JOIN tbl_direction d ON r.direction = d.id
GROUP BY t.day,d.name, t.entrance_ic,t.vehicle_category
UNION
SELECT t.day,d.name AS direction, t.entrance_ic,t.vehicle_category ,COUNT(t.vehicle_category) AS count FROM 
(SELECT date(entrance_time) AS day, entrance_ic,vehicle_category,exit_ic FROM `tbl_exit_ticket_2019_12` where date(entrance_time) >= startDate and date(entrance_time) <= endDate) t
LEFT JOIN tbl_route r ON r.source = t.entrance_ic AND r.destination = t.exit_ic
LEFT JOIN tbl_direction d ON r.direction = d.id
GROUP BY t.day,d.name, t.entrance_ic,t.vehicle_category

) tb
GROUP BY tb.day, tb.direction,tb.entrance_ic,tb.vehicle_category; 

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `GetExitTicketSummaryByCatAndIcAmount`(IN `startDate` DATETIME, IN `endDate` DATETIME)
    NO SQL
BEGIN
	INSERT INTO rpt_hourly_traffic_exit_amount(booth_id,cat1_amount,                                        cat2_amount,cat3_amount,cat4_amount,catS_amount,catPS_amount,catPMS_amount,catAmbulance_amount,catNotAssigned_amount,day,hour,cat1_count,cat2_count,cat3_count,cat4_count,catS_count,catPS_count,catPMS_count,catAmbulance_count,catNotAssigned_count) SELECT
	tb.booth_id,
	IFNULL(SUM(case when tb.vehicle_category = 1 THEN tb.amount END),0) Cat01,
	IFNULL(SUM(case when tb.vehicle_category = 2 THEN tb.amount END),0) Cat02,
	IFNULL(SUM(case when tb.vehicle_category = 3 THEN tb.amount END),0) Cat03,
	IFNULL(SUM(case when tb.vehicle_category = 4 THEN tb.amount END),0) Cat04,
	IFNULL(SUM(case when tb.vehicle_category = 20 THEN tb.amount END),0) CatS,
	IFNULL(SUM(case when tb.vehicle_category = 21 THEN tb.amount END),0) CatPS,
	IFNULL(SUM(case when tb.vehicle_category = 22 THEN tb.amount END),0) CatPMS,
	IFNULL(SUM(case when tb.vehicle_category = 30 THEN tb.amount END),0) CatAmbulance,
	IFNULL(SUM(case when tb.vehicle_category = 99 THEN tb.amount END),0) CatNotAssigned,
	DATE(startDate) AS day,
	HOUR(endDate) AS hour,
    
	IFNULL(SUM(case when tb.vehicle_category = 1 THEN tb.count END),0) Cat01,
	IFNULL(SUM(case when tb.vehicle_category = 2 THEN tb.count END),0) Cat02,
	IFNULL(SUM(case when tb.vehicle_category = 3 THEN tb.count END),0) Cat03,
	IFNULL(SUM(case when tb.vehicle_category = 4 THEN tb.count END),0) Cat04,
	IFNULL(SUM(case when tb.vehicle_category = 20 THEN tb.count END),0) CatS,
	IFNULL(SUM(case when tb.vehicle_category = 21 THEN tb.count END),0) CatPS,
	IFNULL(SUM(case when tb.vehicle_category = 22 THEN tb.count END),0) CatPMS,
	IFNULL(SUM(case when tb.vehicle_category = 30 THEN tb.count END),0) CatAmbulance,
	IFNULL(SUM(case when tb.vehicle_category = 99 THEN tb.count END),0) CatNotAssigned
	FROM
	(SELECT booth_id, t.vehicle_category, SUM(t.amount) as amount,SUM(t.count) as count FROM 
		(SELECT booth_id, vehicle_category,SUM(amount) as amount,COUNT(*) as count 
			FROM tbl_exit_ticket_2019_11 
			WHERE exit_time >= startDate AND exit_time < endDate
			GROUP BY booth_id, vehicle_category 
		UNION SELECT booth_id, vehicle_category, SUM(amount) as amount,COUNT(*) as count  
			FROM tbl_exit_ticket_2019_12 
			WHERE exit_time >= startDate AND exit_time < endDate 
			GROUP BY booth_id, vehicle_category
		UNION SELECT booth_id, vehicle_category, SUM(amount) as amount ,COUNT(*) as count 
			FROM tbl_all_exit_ticket 
			WHERE exit_time >= startDate AND exit_time < endDate
			GROUP BY booth_id, vehicle_category 
		UNION SELECT booth_id, vehicle_category, SUM(amount) as amount ,COUNT(*) as count 
			FROM tbl_exit_ticket 
			WHERE exit_time >= startDate AND exit_time < endDate 
			GROUP BY booth_id, vehicle_category 
		) t 
	GROUP BY t.booth_id,t.vehicle_category ORDER BY t.booth_id ) tb GROUP BY tb.booth_id;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetFareCollectionSummaryByCatAndIc`(IN `startDate` DATETIME, IN `endDate` DATETIME)
    NO SQL
INSERT INTO rpt_hourly_traffic_entrance(ic_id,cat1_count,cat2_count,cat3_count,cat4_count,catS_count,catPS_count,catPMS_count,catAmbulance_count,catNotAssigned_count,day,hour)
SELECT
	tb.entrance_ic,
	IFNULL(SUM(case when tb.vehicle_category = 1 THEN tb.count END),0) Cat01,
	IFNULL(SUM(case when tb.vehicle_category = 2 THEN tb.count END),0) Cat02,
	IFNULL(SUM(case when tb.vehicle_category = 3 THEN tb.count END),0) Cat03,
	IFNULL(SUM(case when tb.vehicle_category = 4 THEN tb.count END),0) Cat04,
	IFNULL(SUM(case when tb.vehicle_category = 20 THEN tb.count END),0) CatS,
	IFNULL(SUM(case when tb.vehicle_category = 21 THEN tb.count END),0) CatPS,
	IFNULL(SUM(case when tb.vehicle_category = 22 THEN tb.count END),0) CatPMS,
	IFNULL(SUM(case when tb.vehicle_category = 30 THEN tb.count END),0) CatAmbulance,
	IFNULL(SUM(case when tb.vehicle_category = 30 THEN tb.count END),0) CatNotAssigned,
	DATE(startDate) AS day,
	HOUR(startDate) AS hour
	FROM
	(	
	
SELECT t.entrance_ic, t.vehicle_category,SUM(t.count) as count FROM 
(SELECT entrance_ic,vehicle_category,COUNT(*) as count 
FROM tbl_exit_ticket_2019_12 
WHERE entrance_time >= startDate AND entrance_time < endDate
GROUP BY vehicle_category, entrance_ic
) t
GROUP BY t.entrance_ic,t.vehicle_category
) tb GROUP BY tb.entrance_ic$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InitDashbordCounts`()
    NO SQL
BEGIN

DECLARE user_count INT;
DECLARE region_count INT;
DECLARE ic_count INT;
DECLARE booth_count INT;
DECLARE device_count INT;
DECLARE entrance_ticket_count INT;
DECLARE exit_ticket_count INT;
DECLARE nfc_count INT;

SELECT COUNT(id) INTO user_count  FROM user;
SELECT COUNT(id) INTO region_count FROM tbl_region;
SELECT COUNT(id) INTO ic_count FROM tbl_ic;
SELECT COUNT(id) INTO booth_count FROM tbl_booth;
SELECT COUNT(id) INTO device_count FROM tbl_devices;
SELECT COUNT(id) INTO entrance_ticket_count FROM tbl_entrance_ticket;
SELECT COUNT(id) INTO exit_ticket_count  FROM tbl_exit_ticket;
SELECT COUNT(id) INTO nfc_count FROM tbl_nfc_card;

DELETE FROM dashboard_counts;

INSERT INTO dashboard_counts (user_count,region_count,ic_count,booth_count,device_count,entrance_ticket_count,exit_ticket_count,nfc_count) VALUES(user_count,region_count,ic_count,booth_count,device_count,entrance_ticket_count,exit_ticket_count,nfc_count);

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InitDashboardExitTicketsSummary`()
    NO SQL
BEGIN

DELETE FROM dashboard_exit_summary;

INSERT INTO dashboard_exit_summary(exit_ic,count,amount) SELECT (SELECT name FROM tbl_ic WHERE id = exit_ic) as exit_ic,count(id) as count,SUM(amount) as amount FROM tbl_exit_ticket GROUP BY exit_ic;

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `GetExitTicketSummaryByCatAndIcTblExit`(IN `startDate` DATETIME, IN `endDate` DATETIME)
    NO SQL
BEGIN
	INSERT INTO rpt_hourly_traffic_exit(booth_id,cat1_count,cat2_count,cat3_count,cat4_count,catS_count,catPS_count,catPMS_count,catAmbulance_count,catNotAssigned_count,day,hour) SELECT
	tb.booth_id,
	IFNULL(SUM(case when tb.vehicle_category = 1 THEN tb.count END),0) Cat01,
	IFNULL(SUM(case when tb.vehicle_category = 2 THEN tb.count END),0) Cat02,
	IFNULL(SUM(case when tb.vehicle_category = 3 THEN tb.count END),0) Cat03,
	IFNULL(SUM(case when tb.vehicle_category = 4 THEN tb.count END),0) Cat04,
	IFNULL(SUM(case when tb.vehicle_category = 20 THEN tb.count END),0) CatS,
	IFNULL(SUM(case when tb.vehicle_category = 21 THEN tb.count END),0) CatPS,
	IFNULL(SUM(case when tb.vehicle_category = 22 THEN tb.count END),0) CatPMS,
	IFNULL(SUM(case when tb.vehicle_category = 30 THEN tb.count END),0) CatAmbulance,
	IFNULL(SUM(case when tb.vehicle_category = 99 THEN tb.count END),0) CatNotAssigned,
	DATE(startDate) AS day,
	HOUR(startDate) AS hour
	FROM
	(SELECT booth_id, t.vehicle_category, SUM(t.count) as count FROM 
		(SELECT booth_id, vehicle_category, COUNT(*) as count 
			FROM tbl_exit_ticket 
			WHERE exit_time >= startDate AND exit_time < endDate 
			GROUP BY booth_id, vehicle_category 
		) t 
	GROUP BY t.booth_id,t.vehicle_category ORDER BY t.booth_id ) tb GROUP BY tb.booth_id;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `InsertDailyDirectionalTrafficExitRevenue`(IN `startDate` DATETIME, IN `endDate` DATETIME)
    NO SQL
BEGIN
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

INSERT INTO rpt_daily_traffic_Directional_exit_revenue(exit_ic,day,direction,cat1_revenue,cat2_revenue,cat3_revenue,
cat4_revenue,catS_revenue,catPS_revenue,catPMS_revenue,catAmbulance_revenue,catNotAssigned_revenue)
SELECT 
	tb.exit_ic,
	tb.day,
	tb.direction,
    
	IFNULL(SUM(case when tb.vehicle_category = 1 THEN tb.revenue END),0) Cat01,
	IFNULL(SUM(case when tb.vehicle_category = 2 THEN tb.revenue END),0) Cat02,
	IFNULL(SUM(case when tb.vehicle_category = 3 THEN tb.revenue END),0) Cat03,
	IFNULL(SUM(case when tb.vehicle_category = 4 THEN tb.revenue END),0) Cat04,
	IFNULL(SUM(case when tb.vehicle_category = 20 THEN tb.revenue END),0) CatS,
	IFNULL(SUM(case when tb.vehicle_category = 21 THEN tb.revenue END),0) CatPS,
	IFNULL(SUM(case when tb.vehicle_category = 22 THEN tb.revenue END),0) CatPMS,
	IFNULL(SUM(case when tb.vehicle_category = 30 THEN tb.revenue END),0) CatAmbulance,
	IFNULL(SUM(case when tb.vehicle_category = 99 THEN tb.revenue END),0) CatNotAssigned
FROM 
(

SELECT t.day,d.name AS direction, t.exit_ic,t.vehicle_category,t.entrance_ic ,SUM(t.amount) AS revenue FROM 
(SELECT date(exit_time) AS day, exit_ic,vehicle_category,entrance_ic,amount FROM `tbl_all_exit_ticket` where date(exit_time) >= startDate and date(exit_time) <= endDate) t
LEFT JOIN tbl_route r ON r.source = t.entrance_ic AND r.destination = t.exit_ic
LEFT JOIN tbl_direction d ON r.direction = d.id
GROUP BY t.day,d.name, t.exit_ic,t.vehicle_category
UNION
SELECT t.day,d.name AS direction, t.exit_ic,t.vehicle_category,t.entrance_ic ,SUM(t.amount) AS revenue  FROM 
(SELECT date(exit_time) AS day, exit_ic,vehicle_category,entrance_ic,amount FROM `tbl_exit_ticket` where date(exit_time) >= startDate and date(exit_time) <= endDate) t
LEFT JOIN tbl_route r ON r.source = t.entrance_ic AND r.destination = t.exit_ic
LEFT JOIN tbl_direction d ON r.direction = d.id
GROUP BY t.day,d.name, t.exit_ic,t.vehicle_category
UNION
SELECT t.day,d.name AS direction, t.exit_ic,t.vehicle_category,t.entrance_ic ,SUM(t.amount) AS revenue  FROM 
(SELECT date(exit_time) AS day, exit_ic,vehicle_category,entrance_ic,amount FROM `tbl_exit_ticket_2019_11` where date(exit_time) >= startDate and date(exit_time) <= endDate) t
LEFT JOIN tbl_route r ON r.source = t.entrance_ic AND r.destination = t.exit_ic
LEFT JOIN tbl_direction d ON r.direction = d.id
GROUP BY t.day,d.name, t.exit_ic,t.vehicle_category
UNION
SELECT t.day,d.name AS direction, t.exit_ic,t.entrance_ic,t.vehicle_category ,SUM(t.amount) AS revenue  FROM 
(SELECT date(exit_time) AS day, exit_ic,vehicle_category,entrance_ic,amount FROM `tbl_exit_ticket_2019_12` where date(exit_time) >= startDate and date(exit_time) <= endDate) t
LEFT JOIN tbl_route r ON r.source = t.entrance_ic AND r.destination = t.exit_ic
LEFT JOIN tbl_direction d ON r.direction = d.id
GROUP BY t.day,d.name, t.exit_ic,t.vehicle_category


) tb
GROUP BY tb.day, tb.direction,tb.exit_ic,tb.vehicle_category; 

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `InsertDirectionalHourlyExitRevenue`(IN `startDate` DATETIME, IN `endDate` DATETIME)
    NO SQL
INSERT INTO rpt_hourly_traffic_directional_exit_revenue(exit_ic,day,direction,hour,cat1_count,cat2_count,cat3_count,cat4_count,catS_count,catPS_count,catPMS_count,catAmbulance_count,catNotAssigned_count, cat1_amount,cat2_amount,cat3_amount,cat4_amount,catS_amount,catPS_amount,catPMS_amount,catAmbulance_amount,catNotAssigned_amount)
SELECT 
	tb.exit_ic,
	date(startDate) as day,
	tb.direction,
    hour(startDate) as hour,
	IFNULL(SUM(case when tb.vehicle_category = 1 THEN tb.count END),0) Cat01,
	IFNULL(SUM(case when tb.vehicle_category = 2 THEN tb.count END),0) Cat02,
	IFNULL(SUM(case when tb.vehicle_category = 3 THEN tb.count END),0) Cat03,
	IFNULL(SUM(case when tb.vehicle_category = 4 THEN tb.count END),0) Cat04,
	IFNULL(SUM(case when tb.vehicle_category = 20 THEN tb.count END),0) CatS,
	IFNULL(SUM(case when tb.vehicle_category = 21 THEN tb.count END),0) CatPS,
	IFNULL(SUM(case when tb.vehicle_category = 22 THEN tb.count END),0) CatPMS,
	IFNULL(SUM(case when tb.vehicle_category = 30 THEN tb.count END),0) CatAmbulance,
	IFNULL(SUM(case when tb.vehicle_category = 99 THEN tb.count END),0) CatNotAssigned,
    IFNULL(SUM(case when tb.vehicle_category = 1 THEN tb.amount END),0) Cat01Amount,
	IFNULL(SUM(case when tb.vehicle_category = 2 THEN tb.amount END),0) Cat02Amount,
	IFNULL(SUM(case when tb.vehicle_category = 3 THEN tb.amount END),0) Cat03Amount,
	IFNULL(SUM(case when tb.vehicle_category = 4 THEN tb.amount END),0) Cat04Amount,
	IFNULL(SUM(case when tb.vehicle_category = 20 THEN tb.amount END),0) CatSAmount,
	IFNULL(SUM(case when tb.vehicle_category = 21 THEN tb.amount END),0) CatPSAmount,
	IFNULL(SUM(case when tb.vehicle_category = 22 THEN tb.amount END),0) CatPMSAmount,
	IFNULL(SUM(case when tb.vehicle_category = 30 THEN tb.amount END),0) CatAmbulanceAmount,
	IFNULL(SUM(case when tb.vehicle_category = 99 THEN tb.amount END),0) CatNotAssignedAmount
    
FROM 
(

SELECT d.name AS direction, t.exit_ic,t.vehicle_category ,COUNT(t.vehicle_category) AS count, SUM(t.amount) AS amount FROM 
(
SELECT date(exit_time) AS day, exit_ic,vehicle_category,entrance_ic ,amount FROM `tbl_all_exit_ticket` where exit_time >= startDate and exit_time < endDate
UNION ALL SELECT date(exit_time) AS day, exit_ic,vehicle_category,entrance_ic ,amount FROM `tbl_exit_ticket` where exit_time >= startDate and exit_time < endDate
UNION ALL SELECT date(exit_time) AS day, exit_ic,vehicle_category,entrance_ic,amount FROM `tbl_exit_ticket_2019_11` where exit_time >= startDate and exit_time < endDate
UNION ALL SELECT date(exit_time) AS day, exit_ic,vehicle_category,entrance_ic,amount FROM `tbl_exit_ticket_2019_12` where exit_time >= startDate and exit_time < endDate
) t
INNER JOIN tbl_route r ON r.source = t.entrance_ic AND r.destination = t.exit_ic
INNER JOIN tbl_direction d ON r.direction = d.id
GROUP BY d.name, t.exit_ic,t.vehicle_category

) tb
GROUP BY tb.direction,tb.exit_ic$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `InsertDirectionalHourlyExitRevenueTBLExit`(IN `startDate` DATETIME, IN `endDate` DATETIME)
    NO SQL
INSERT INTO rpt_hourly_traffic_directional_exit_revenue(exit_ic,day,direction,hour,cat1_count,cat2_count,cat3_count,cat4_count,catS_count,catPS_count,catPMS_count,catAmbulance_count,catNotAssigned_count, cat1_amount,cat2_amount,cat3_amount,cat4_amount,catS_amount,catPS_amount,catPMS_amount,catAmbulance_amount,catNotAssigned_amount)
SELECT 
	tb.exit_ic,
	date(startDate) as day,
	tb.direction,
    hour(startDate) as hour,
	IFNULL(SUM(case when tb.vehicle_category = 1 THEN tb.count END),0) Cat01,
	IFNULL(SUM(case when tb.vehicle_category = 2 THEN tb.count END),0) Cat02,
	IFNULL(SUM(case when tb.vehicle_category = 3 THEN tb.count END),0) Cat03,
	IFNULL(SUM(case when tb.vehicle_category = 4 THEN tb.count END),0) Cat04,
	IFNULL(SUM(case when tb.vehicle_category = 20 THEN tb.count END),0) CatS,
	IFNULL(SUM(case when tb.vehicle_category = 21 THEN tb.count END),0) CatPS,
	IFNULL(SUM(case when tb.vehicle_category = 22 THEN tb.count END),0) CatPMS,
	IFNULL(SUM(case when tb.vehicle_category = 30 THEN tb.count END),0) CatAmbulance,
	IFNULL(SUM(case when tb.vehicle_category = 99 THEN tb.count END),0) CatNotAssigned,
    IFNULL(SUM(case when tb.vehicle_category = 1 THEN tb.amount END),0) Cat01Amount,
	IFNULL(SUM(case when tb.vehicle_category = 2 THEN tb.amount END),0) Cat02Amount,
	IFNULL(SUM(case when tb.vehicle_category = 3 THEN tb.amount END),0) Cat03Amount,
	IFNULL(SUM(case when tb.vehicle_category = 4 THEN tb.amount END),0) Cat04Amount,
	IFNULL(SUM(case when tb.vehicle_category = 20 THEN tb.amount END),0) CatSAmount,
	IFNULL(SUM(case when tb.vehicle_category = 21 THEN tb.amount END),0) CatPSAmount,
	IFNULL(SUM(case when tb.vehicle_category = 22 THEN tb.amount END),0) CatPMSAmount,
	IFNULL(SUM(case when tb.vehicle_category = 30 THEN tb.amount END),0) CatAmbulanceAmount,
	IFNULL(SUM(case when tb.vehicle_category = 99 THEN tb.amount END),0) CatNotAssignedAmount
    
FROM 
(

SELECT d.name AS direction, t.exit_ic,t.vehicle_category ,COUNT(t.vehicle_category) AS count, SUM(t.amount) AS amount FROM 
(
SELECT date(exit_time) AS day, exit_ic,vehicle_category,entrance_ic ,amount FROM `tbl_exit_ticket` 
    where 
     ticket_type_status_id NOT IN (2,12) and
    exit_time >= startDate and exit_time < endDate


    
) t
INNER JOIN tbl_route r ON r.source = t.entrance_ic AND r.destination = t.exit_ic
INNER JOIN tbl_direction d ON r.direction = d.id
GROUP BY d.name, t.exit_ic,t.vehicle_category

) tb
GROUP BY tb.direction,tb.exit_ic$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptADTEntrance`(IN `year` INT, IN `direction` VARCHAR(500), IN `month` INT, IN `ic` INT)
    NO SQL
SELECT 
MONTH(rpt_daily_traffic_Directional_entrance.day) as month_no,
YEAR(rpt_daily_traffic_Directional_entrance.day) as year_no,
rpt_daily_traffic_Directional_entrance.direction,
rpt_daily_traffic_Directional_entrance.entrance_ic,
(
    SUM(rpt_daily_traffic_Directional_entrance.cat1_count)+
    SUM(rpt_daily_traffic_Directional_entrance.cat2_count)+
    SUM(rpt_daily_traffic_Directional_entrance.cat3_count)+
    SUM(rpt_daily_traffic_Directional_entrance.cat4_count)+
    SUM(rpt_daily_traffic_Directional_entrance.catS_count)+
    SUM(rpt_daily_traffic_Directional_entrance.catPS_count)+
    SUM(rpt_daily_traffic_Directional_entrance.catPMS_count)
) as total


    FROM `rpt_daily_traffic_Directional_entrance` 
   
    WHERE 
    YEAR(rpt_daily_traffic_Directional_entrance.day) = year 
    AND MONTH(rpt_daily_traffic_Directional_entrance.day) = month 
    AND rpt_daily_traffic_Directional_entrance.direction = direction 
    AND rpt_daily_traffic_Directional_entrance.entrance_ic = ic 
    GROUP BY direction,entrance_ic,month_no,year_no$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InitDashbordEntranceTicketSummary`()
    NO SQL
BEGIN

DELETE FROM dashboard_entrance_summary;

INSERT INTO dashboard_entrance_summary(entrance_ic,count) (SELECT (SELECT name FROM tbl_ic WHERE id = entrance_ic) as entrance_ic,count(id) as count FROM tbl_entrance_ticket GROUP BY entrance_ic);

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Insert_Ticket_Summary`(IN `input_val` DATE)
    NO SQL
BEGIN
INSERT INTO `rpt_book_receipt_ticket_summary` SELECT * FROM `tbl_exit_ticket` WHERE DATE(exit_time) = input_val AND ticket_type_status_id =7;
INSERT INTO `rpt_category_changed_ticket_summary` SELECT * FROM `tbl_exit_ticket` WHERE DATE(exit_time) = input_val AND ticket_type_status_id =9;
INSERT INTO `rpt_expired_ticket_summary` SELECT * FROM `tbl_exit_ticket` WHERE DATE(exit_time) = input_val AND ticket_type_status_id =4;
INSERT INTO `rpt_lost_ticket_summary` SELECT * FROM `tbl_exit_ticket` WHERE DATE(exit_time) = input_val AND ticket_type_status_id =3;
INSERT INTO `rpt_mannual_ticket_summary` SELECT * FROM `tbl_exit_ticket` WHERE DATE(exit_time) = input_val AND ticket_type_status_id =10;
INSERT INTO `rpt_mississued_ticket_summary_exit` SELECT * FROM `tbl_exit_ticket` WHERE DATE(exit_time) = input_val AND ticket_type_status_id =12;
INSERT INTO `rpt_re_printed_ticket_changed_ticket_summary` SELECT * FROM `tbl_exit_ticket` WHERE DATE(exit_time) = input_val AND ticket_type_status_id =2;
INSERT INTO `rpt_mississued_ticket_summary_entrance` SELECT * FROM `tbl_entrance_ticket` WHERE DATE(entrance_time) = input_val AND ticket_type_status_id =12;
INSERT INTO rda_ticket_data_archive.`tbl_all_exit_ticket` SELECT * FROM `tbl_exit_ticket` WHERE DATE(exit_time) = input_val;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptDailyRevenueDirectionalExitAny`(IN `year` INT, IN `monthNumber` INT)
    NO SQL
SELECT day,direction,
SUM(cat1_amount) as cat1,
SUM(cat2_amount) as cat2,
SUM(cat3_amount) as cat3,
SUM(cat4_amount) as cat4,
SUM(catS_amount) as s,
SUM(catPS_amount) as ps,
SUM(catPMS_amount) as pms,
(
    SUM(cat1_amount)+
    SUM(cat2_amount)+
    SUM(cat3_amount)+
    SUM(cat4_amount)+
    SUM(catS_amount)+
    SUM(catPS_amount)+
    SUM(catPMS_amount)
   
) as total

    FROM `rpt_hourly_traffic_directional_exit_revenue` 
    WHERE  YEAR(day) = year AND MONTH(day) = monthNumber
    GROUP BY day,direction ORDER BY day$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `InsertHourlyTrafficEntranceWithDirections`(IN `startDate` DATETIME, IN `endDate` DATETIME)
    NO SQL
INSERT INTO rpt_hourly_directional_traffic_entrance(day,hour,ic_id,direction,cat1_count,cat2_count,cat3_count,cat4_count,catS_count,catPS_count,catPMS_count,catAmbulance_count,catNotAssigned_count)
SELECT
	tb.day,
    tb.hour,
	tb.entrance_ic,
	tb.direction,
	IFNULL(SUM(case when tb.vehicle_category = 1 THEN tb.count END),0) Cat01,
	IFNULL(SUM(case when tb.vehicle_category = 2 THEN tb.count END),0) Cat02,
	IFNULL(SUM(case when tb.vehicle_category = 3 THEN tb.count END),0) Cat03,
	IFNULL(SUM(case when tb.vehicle_category = 4 THEN tb.count END),0) Cat04,
	IFNULL(SUM(case when tb.vehicle_category = 20 THEN tb.count END),0) CatS,
	IFNULL(SUM(case when tb.vehicle_category = 21 THEN tb.count END),0) CatPS,
	IFNULL(SUM(case when tb.vehicle_category = 22 THEN tb.count END),0) CatPMS,
	IFNULL(SUM(case when tb.vehicle_category = 30 THEN tb.count END),0) CatAmbulance,
	IFNULL(SUM(case when tb.vehicle_category = 99 THEN tb.count END),0) CatNotAssigned
	FROM (
		SELECT 
		tbl.day,
        tbl.hour,
		tbl.booth_id,
		tbl.entrance_ic,
		tbl.vehicle_category,
		tbl.direction,
		tbl.count
		 FROM
			(SELECT 
			date(t.entrance_time) AS day,
            hour(t.entrance_time) AS hour,
			t.booth_id,
			t.entrance_ic,
			t.vehicle_category,
			d.name AS direction, 
			COUNT(t.vehicle_category) AS count FROM 
			(
			SELECT * FROM `tbl_exit_ticket` where entrance_time >= startDate and entrance_time < endDate
			) t
			INNER JOIN tbl_route r ON r.source = t.entrance_ic AND r.destination = t.exit_ic
			INNER JOIN tbl_direction d ON r.direction = d.id
			GROUP BY date(t.entrance_time),hour(t.entrance_time),t.entrance_ic,t.booth_id,t.vehicle_category,d.name ) tbl
			
		ORDER BY tbl.day ) tb GROUP BY tb.day, tb.hour,tb.booth_id,
	tb.entrance_ic,
	tb.vehicle_category,
	tb.direction$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptDailyRevenueDirectionalExit`(IN `year` INT, IN `monthNumber` INT, IN `ic` INT)
    NO SQL
SELECT day,direction,
SUM(cat1_amount) as cat1,
SUM(cat2_amount) as cat2,
SUM(cat3_amount) as cat3,
SUM(cat4_amount) as cat4,
SUM(catS_amount) as s,
SUM(catPS_amount) as ps,
SUM(catPMS_amount) as pms,
(
    SUM(cat1_amount)+
    SUM(cat2_amount)+
    SUM(cat3_amount)+
    SUM(cat4_amount)+
    SUM(catS_amount)+
    SUM(catPS_amount)+
    SUM(catPMS_amount)
   
) as total
    FROM `rpt_hourly_traffic_directional_exit_revenue` 
    WHERE exit_ic=ic AND YEAR(day) = year AND MONTH(day) = monthNumber
    GROUP BY day,direction ORDER BY day$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `RptDailyTrafficDirectionalEntrance`(IN `year` INT, IN `monthNumber` INT, IN `ic` INT)
    NO SQL
SELECT day,direction,
SUM(cat1_count) as cat1,
SUM(cat2_count) as cat2,
SUM(cat3_count) as cat3,
SUM(cat4_count) as cat4,
SUM(catS_count) as s,
SUM(catPS_count) as ps,
SUM(catPMS_count) as pms,
(
    SUM(cat1_count)+
    SUM(cat2_count)+
    SUM(cat3_count)+
    SUM(cat4_count)+
    SUM(catS_count)+
    SUM(catPS_count)+
    SUM(catPMS_count)
) as total

    FROM `rpt_hourly_directional_traffic_entrance` 
    WHERE ic_id=ic AND YEAR(day) = year AND MONTH(day) = monthNumber
    GROUP BY day,direction ORDER BY day$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `RptDailyTrafficDirectionalExitAny`(IN `year` INT, IN `monthNumber` INT)
    NO SQL
SELECT day,direction,
SUM(cat1_count) as cat1,
SUM(cat2_count) as cat2,
SUM(cat3_count) as cat3,
SUM(cat4_count) as cat4,
SUM(catS_count) as s,
SUM(catPS_count) as ps,
SUM(catPMS_count) as pms,
(
    SUM(cat1_count)+
    SUM(cat2_count)+
    SUM(cat3_count)+
    SUM(cat4_count)+
    SUM(catS_count)+
    SUM(catPS_count)+
    SUM(catPMS_count)+
    SUM(catAmbulance_count)+
	SUM(catNotAssigned_count)
) as total

    FROM `rpt_hourly_traffic_directional_exit_revenue` 
    WHERE  YEAR(day) = year AND MONTH(day) = monthNumber
    GROUP BY day,direction ORDER BY day$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptMonthlyRevenueDirectionalIc`(IN `year` INT, IN `ic` INT)
    NO SQL
SELECT direction,MONTH(day) as month,
SUM(cat1_amount) as cat1,
SUM(cat2_amount) as cat2,
SUM(cat3_amount) as cat3,
SUM(cat4_amount) as cat4,
SUM(catS_amount) as s,
SUM(catPS_amount) as ps,
SUM(catPMS_amount) as pms,
(
    SUM(cat1_amount)+
    SUM(cat2_amount)+
    SUM(cat3_amount)+
    SUM(cat4_amount)+
    SUM(catS_amount)+
    SUM(catPS_amount)+
    SUM(catPMS_amount)
) as total

    FROM `rpt_hourly_traffic_directional_exit_revenue` 
    WHERE  exit_ic=ic AND YEAR(day) = year
    GROUP BY direction,month
    ORDER BY month,direction$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptMonthlyRevenueDirectionalIcAny`(IN `year` INT)
    NO SQL
SELECT direction,MONTH(day) as month,
SUM(cat1_amount) as cat1,
SUM(cat2_amount) as cat2,
SUM(cat3_amount) as cat3,
SUM(cat4_amount) as cat4,
SUM(catS_amount) as s,
SUM(catPS_amount) as ps,
SUM(catPMS_amount) as pms,
(
    SUM(cat1_amount)+
    SUM(cat2_amount)+
    SUM(cat3_amount)+
    SUM(cat4_amount)+
    SUM(catS_amount)+
    SUM(catPS_amount)+
    SUM(catPMS_amount)
) as total

    FROM `rpt_hourly_traffic_directional_exit_revenue` 
    WHERE  YEAR(day) = year
    GROUP BY direction,month$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptMonthlyTrafficDirectionalEntranceIc`(IN `year` INT, IN `ic` INT)
    NO SQL
SELECT 
direction,
MONTH(day) as date,


SUM(cat1_count) as cat1,
SUM(cat2_count) as cat2,
SUM(cat3_count) as cat3,
SUM(cat4_count) as cat4,
SUM(catS_count) as s,
SUM(catPS_count) as ps,
SUM(catPMS_count) as pms,
SUM(catAmbulance_count) as ambulance,
SUM(catNotAssigned_count) as notassigned,
(
    SUM(cat1_count)+
    SUM(cat2_count)+
    SUM(cat3_count)+
    SUM(cat4_count)+
    SUM(catS_count)+
    SUM(catPS_count)+
    SUM(catPMS_count)+
    SUM(catAmbulance_count)+
	SUM(catNotAssigned_count)

) as total

    FROM `rpt_hourly_directional_traffic_entrance` t 
    WHERE entrance_ic=ic AND YEAR(day) = year
    GROUP BY direction,date$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptMonthlyTrafficDirectionalEntranceIcAny`(IN `year` INT)
    NO SQL
SELECT 
direction,
MONTH(day) as date,
SUM(cat1_count) as cat1,
SUM(cat2_count) as cat2,
SUM(cat3_count) as cat3,
SUM(cat4_count) as cat4,
SUM(catS_count) as s,
SUM(catPS_count) as ps,
SUM(catPMS_count) as pms,
SUM(catAmbulance_count) as ambulance,
SUM(catNotAssigned_count) as notassigned,
(
    SUM(cat1_count)+
    SUM(cat2_count)+
    SUM(cat3_count)+
    SUM(cat4_count)+
    SUM(catS_count)+
    SUM(catPS_count)+
    SUM(catPMS_count)+
    SUM(catAmbulance_count)+
	SUM(catNotAssigned_count)

) as total

    FROM `rpt_hourly_directional_traffic_entrance` t 
    WHERE  YEAR(day) = year
    GROUP BY direction,date
    ORDER BY MONTH(day),direction$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `RptDailyTrafficDirectionalEntranceAny`(IN `year` INT, IN `monthNumber` INT)
    NO SQL
SELECT day,direction,
SUM(cat1_count) as cat1,
SUM(cat2_count) as cat2,
SUM(cat3_count) as cat3,
SUM(cat4_count) as cat4,
SUM(catS_count) as s,
SUM(catPS_count) as ps,
SUM(catPMS_count) as pms,
(
    SUM(cat1_count)+
    SUM(cat2_count)+
    SUM(cat3_count)+
    SUM(cat4_count)+
    SUM(catS_count)+
    SUM(catPS_count)+
    SUM(catPMS_count)
) as total

    FROM `rpt_hourly_directional_traffic_entrance` 
    WHERE  YEAR(day) = year AND MONTH(day) = monthNumber
    GROUP BY day,direction ORDER BY day$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptMonthlyTrafficDirectionalExitIcAny`(IN `year` INT)
    NO SQL
SELECT 
direction,
MONTH(day) as date,
SUM(cat1_count) as cat1,
SUM(cat2_count) as cat2,
SUM(cat3_count) as cat3,
SUM(cat4_count) as cat4,
SUM(catS_count) as s,
SUM(catPS_count) as ps,
SUM(catPMS_count) as pms,
SUM(catAmbulance_count) as ambulance,
SUM(catNotAssigned_count) as notassigned,
(
    SUM(cat1_count)+
    SUM(cat2_count)+
    SUM(cat3_count)+
    SUM(cat4_count)+
    SUM(catS_count)+
    SUM(catPS_count)+
    SUM(catPMS_count)+
    SUM(catAmbulance_count)+
	SUM(catNotAssigned_count)

) as total


    FROM `rpt_hourly_traffic_directional_exit_revenue` 
   WHERE  YEAR(day) = year
    GROUP BY direction,date
    ORDER BY MONTH(day),direction$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptYearlyRevenueDirectionalIc`(IN `from_year` INT, IN `to_year` INT, IN `ic` INT)
    NO SQL
SELECT 
direction,
MONTH(day) as year_number,
SUM(cat1_amount) as cat1,
SUM(cat2_amount) as cat2,
SUM(cat3_amount) as cat3,
SUM(cat4_amount) as cat4,
SUM(catS_amount) as s,
SUM(catPS_amount) as ps,
SUM(catPMS_amount) as pms,
(
    SUM(cat1_amount)+
    SUM(cat2_amount)+
    SUM(cat3_amount)+
    SUM(cat4_amount)+
    SUM(catS_amount)+
    SUM(catPS_amount)+
    SUM(catPMS_amount)
   
) as total

    FROM `rpt_hourly_traffic_directional_exit_revenue` 
   
 where  exit_ic=ic AND YEAR(day) >= from_year and YEAR(day) <= to_year
    GROUP BY direction,year_number
    ORDER BY year_number,direction$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptMonthlyTrafficDirectionalExitIc`(IN `year` INT, IN `ic` INT)
    NO SQL
SELECT 
direction,
MONTH(day) as date,
SUM(cat1_count) as cat1,
SUM(cat2_count) as cat2,
SUM(cat3_count) as cat3,
SUM(cat4_count) as cat4,
SUM(catS_count) as s,
SUM(catPS_count) as ps,
SUM(catPMS_count) as pms,
SUM(catAmbulance_count) as ambulance,
SUM(catNotAssigned_count) as notassigned,
(
    SUM(cat1_count)+
    SUM(cat2_count)+
    SUM(cat3_count)+
    SUM(cat4_count)+
    SUM(catS_count)+
    SUM(catPS_count)+
    SUM(catPMS_count)+
    SUM(catAmbulance_count)+
	SUM(catNotAssigned_count)

) as total

    FROM `rpt_hourly_traffic_directional_exit_revenue` 
    WHERE exit_ic=ic AND YEAR(day) = year 
    GROUP BY direction,date$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptYearlyTrafficDirectionalEntranceIc`(IN `from_year` INT, IN `to_year` INT, IN `ic` INT)
    NO SQL
SELECT 
direction,
MONTH(day) as year_number,
SUM(cat1_count) as cat1,
SUM(cat2_count) as cat2,
SUM(cat3_count) as cat3,
SUM(cat4_count) as cat4,
SUM(catS_count) as s,
SUM(catPS_count) as ps,
SUM(catPMS_count) as pms,
(
    SUM(cat1_count)+
    SUM(cat2_count)+
    SUM(cat3_count)+
    SUM(cat4_count)+
    SUM(catS_count)+
    SUM(catPS_count)+
    SUM(catPMS_count)
) as total

    FROM `rpt_daily_traffic_Directional_entrance` t 
   where YEAR(day) >= from_year and YEAR(day) <= to_year 
   AND entrance_ic = ic 
    GROUP BY direction,year_number
    ORDER BY year_number,direction$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptYearlyTrafficDirectionalEntranceIcAny`(IN `from_year` VARCHAR(20), IN `to_year` VARCHAR(20))
    NO SQL
SELECT 
direction,
MONTH(day) as year_number,
SUM(cat1_count) as cat1,
SUM(cat2_count) as cat2,
SUM(cat3_count) as cat3,
SUM(cat4_count) as cat4,
SUM(catS_count) as s,
SUM(catPS_count) as ps,
SUM(catPMS_count) as pms,
(
    SUM(cat1_count)+
    SUM(cat2_count)+
    SUM(cat3_count)+
    SUM(cat4_count)+
    SUM(catS_count)+
    SUM(catPS_count)+
    SUM(catPMS_count)
) as total

    FROM `rpt_daily_traffic_Directional_entrance` t 
   where YEAR(day) >= from_year and YEAR(day) <= to_year
    GROUP BY direction,year_number
    ORDER BY year_number,direction$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptYearlyTrafficDirectionalExitIc`(IN `from_year` INT, IN `to_year` INT, IN `ic` INT)
    NO SQL
SELECT 
direction,
MONTH(day) as year_number,
SUM(cat1_count) as cat1,
SUM(cat2_count) as cat2,
SUM(cat3_count) as cat3,
SUM(cat4_count) as cat4,
SUM(catS_count) as s,
SUM(catPS_count) as ps,
SUM(catPMS_count) as pms,
(
    SUM(cat1_count)+
    SUM(cat2_count)+
    SUM(cat3_count)+
    SUM(cat4_count)+
    SUM(catS_count)+
    SUM(catPS_count)+
    SUM(catPMS_count)+
    SUM(catAmbulance_count)+
	SUM(catNotAssigned_count)
) as total

    FROM `rpt_hourly_traffic_directional_exit_revenue` 
  	where YEAR(day) >= from_year and YEAR(day) <= to_year
     AND exit_ic = ic 
    GROUP BY direction,year_number
    ORDER BY year_number,direction$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptYearlyTrafficDirectionalExitIcAny`(IN `from_year` INT, IN `to_year` INT)
    NO SQL
SELECT 
direction,
MONTH(day) as year_number,
SUM(cat1_count) as cat1,
SUM(cat2_count) as cat2,
SUM(cat3_count) as cat3,
SUM(cat4_count) as cat4,
SUM(catS_count) as s,
SUM(catPS_count) as ps,
SUM(catPMS_count) as pms,
(
    SUM(cat1_count)+
    SUM(cat2_count)+
    SUM(cat3_count)+
    SUM(cat4_count)+
    SUM(catS_count)+
    SUM(catPS_count)+
    SUM(catPMS_count)+
    SUM(catAmbulance_count)+
	SUM(catNotAssigned_count)
) as total


    FROM `rpt_hourly_traffic_directional_exit_revenue` 
 where YEAR(day) >= from_year and YEAR(day) <= to_year
    GROUP BY direction,year_number
    ORDER BY year_number,direction$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptADTExit`(IN `year` INT, IN `direction` VARCHAR(500), IN `month` INT, IN `ic` INT)
    NO SQL
SELECT 
MONTH(rpt_daily_traffic_Directional_exit.day) as month_no,
YEAR(rpt_daily_traffic_Directional_exit.day) as year_no,
rpt_daily_traffic_Directional_exit.direction,
rpt_daily_traffic_Directional_exit.exit_ic,
(
    SUM(rpt_daily_traffic_Directional_exit.cat1_count)+
    SUM(rpt_daily_traffic_Directional_exit.cat2_count)+
    SUM(rpt_daily_traffic_Directional_exit.cat3_count)+
    SUM(rpt_daily_traffic_Directional_exit.cat4_count)+
    SUM(rpt_daily_traffic_Directional_exit.catS_count)+
    SUM(rpt_daily_traffic_Directional_exit.catPS_count)+
    SUM(rpt_daily_traffic_Directional_exit.catPMS_count)
) as total


    FROM `rpt_daily_traffic_Directional_exit` 
   
    WHERE 
    YEAR(rpt_daily_traffic_Directional_exit.day) = year 
    AND MONTH(rpt_daily_traffic_Directional_exit.day) = month 
    AND rpt_daily_traffic_Directional_exit.direction = direction 
    AND rpt_daily_traffic_Directional_exit.exit_ic = ic 
    GROUP BY direction,exit_ic,month_no,year_no$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `RptYearlyRevenueDirectionalIcAny`(IN `from_year` INT, IN `to_year` INT)
    NO SQL
SELECT 
direction,
MONTH(day) as year_number,
SUM(cat1_amount) as cat1,
SUM(cat2_amount) as cat2,
SUM(cat3_amount) as cat3,
SUM(cat4_amount) as cat4,
SUM(catS_amount) as s,
SUM(catPS_amount) as ps,
SUM(catPMS_amount) as pms,
(
    SUM(cat1_amount)+
    SUM(cat2_amount)+
    SUM(cat3_amount)+
    SUM(cat4_amount)+
    SUM(catS_amount)+
    SUM(catPS_amount)+
    SUM(catPMS_amount)
   
) as total

    FROM `rpt_hourly_traffic_directional_exit_revenue` 
   
 where YEAR(day) >= from_year and YEAR(day) <= to_year
    GROUP BY direction,year_number
    ORDER BY year_number,direction$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Rpt_AllICEntranceSummary`(IN `startDate` DATETIME, IN `endDate` DATETIME)
    NO SQL
SELECT 
ic.name AS Interchange, 
SUM(t.cat1_count) AS Cat1,
SUM(t.cat2_count) AS Cat2,
SUM(t.cat3_count) AS Cat3,
SUM(t.cat4_count) AS Cat4,
SUM(t.catS_count) AS CatS,
SUM(t.catPS_count) AS CatPS,
SUM(t.catPMS_count) AS CatPMS,
SUM(t.catAmbulance_count) AS CatAmbulance,
SUM(t.catNotAssigned_count) AS CatNotAssigned,
(SUM(t.cat1_count) + SUM(t.cat2_count)+ SUM(t.cat3_count)+ SUM(t.cat4_count)+ SUM(t.catS_count)+ SUM(t.catPS_count)+ SUM(t.catPMS_count)+ SUM(t.catAmbulance_count)+ SUM(t.catNotAssigned_count)) AS Total
FROM 
(SELECT * FROM `rpt_hourly_traffic_entrance` where  DATE_ADD(day, interval (`hour`-1) hour) >= startDate AND DATE_ADD(day, interval `hour` hour) < endDate) t
INNER JOIn tbl_ic ic ON ic.id = t.ic_id
GROUP BY ic.name$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_AllICExitSummaryAmount`(IN `startDate` DATETIME, IN `endDate` DATETIME)
    NO SQL
SELECT tbl_ic.name as Interchange, 
SUM(cat1_amount) as Cat1, 
SUM(cat2_amount) as Cat2,
SUM(cat3_amount) as Cat3,
SUM(cat4_amount) as Cat4,
SUM(catS_amount) as CatS,
SUM(catPS_amount) as CatPS,
SUM(catPMS_amount) as CatPMS,
SUM(catAmbulance_amount) as CatAmbulance,
SUM(catNotAssigned_amount) as CatNotAssigned,
(SUM(cat1_amount) + SUM(cat2_amount)+ SUM(cat3_amount)+ SUM(cat4_amount)+ SUM(catS_amount)+ SUM(catPS_amount)+ SUM(catPMS_amount)+ SUM(catAmbulance_amount)+ SUM(catNotAssigned_amount)) AS Total
FROM `rpt_hourly_traffic_directional_exit_revenue` 
INNER JOIn tbl_ic ON tbl_ic.id = rpt_hourly_traffic_directional_exit_revenue.exit_ic
WHERE DATE_ADD(day, interval (`hour`) hour) >= startDate AND DATE_ADD(day, interval (`hour`) hour) < endDate
GROUP BY exit_ic$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_BookReceiptTicketIc`(IN `start_date` DATETIME, IN `end_date` DATETIME, IN `ic` INT)
    NO SQL
SELECT ET.exit_serial,IFNULL(ET.entrance_serial,'-') entrance_serial,IFNULL(ET.entrance_time,'-') entrance_time,
IFNULL(ET.exit_time,'-') exit_time,
IC2.name ic_entrance,IC.name ic_exit,V.name vc,ET.amount,B.name booth,U.username,ET.exit_time
                FROM rpt_book_receipt_ticket_summary ET
                LEFT JOIN tbl_ic IC ON IC.id = ET.exit_ic
                LEFT JOIN tbl_ic IC2 ON IC2.id = ET.entrance_ic
                LEFT JOIN tbl_booth B ON B.id = ET.booth_id
                LEFT JOIN user U ON U.id = ET.teller_id
                LEFT JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date AND ET.exit_ic = ic$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `RptDailyTrafficDirectionalExit`(IN `year` INT, IN `monthNumber` INT, IN `ic` INT)
    NO SQL
SELECT day,direction,
SUM(cat1_count) as cat1,
SUM(cat2_count) as cat2,
SUM(cat3_count) as cat3,
SUM(cat4_count) as cat4,
SUM(catS_count) as s,
SUM(catPS_count) as ps,
SUM(catPMS_count) as pms,
SUM(catAmbulance_count) as ambulance,
SUM(catNotAssigned_count) as notassigned,

(
    SUM(cat1_count)+
    SUM(cat2_count)+
    SUM(cat3_count)+
    SUM(cat4_count)+
    SUM(catS_count)+
    SUM(catPS_count)+
    SUM(catPMS_count)+
    SUM(catAmbulance_count)+
	SUM(catNotAssigned_count)
) as total

    FROM `rpt_hourly_traffic_directional_exit_revenue` 
    WHERE exit_ic=ic AND YEAR(day) = year AND MONTH(day) = monthNumber
    GROUP BY day,direction ORDER BY day$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_CategoryChangedTicketAnyIcTeller`(IN `start_date` DATETIME, IN `end_date` DATETIME, IN `teller` INT)
    NO SQL
SELECT ET.entrance_serial,ET.entrance_time,ET.exit_serial,ET.exit_time,IC.name ic,B.name booth,ET.amount,V.name vc,U.username
                    FROM rpt_category_changed_ticket_summary ET
                    INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                    INNER JOIN tbl_booth B ON B.id = ET.booth_id
                    INNER JOIN user U ON U.id = ET.teller_id
                    LEFT JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                    WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date AND  ET.teller_id = teller$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_CategoryChangedTicketAnyIcAnyTeller`(IN `start_date` DATETIME, IN `end_date` DATETIME)
    NO SQL
SELECT ET.entrance_serial,ET.entrance_time,ET.exit_serial,ET.exit_time,IC.name ic,B.name booth,ET.amount,V.name vc,U.username
                    FROM rpt_category_changed_ticket_summary ET
                    INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                    INNER JOIN tbl_booth B ON B.id = ET.booth_id
                    INNER JOIN user U ON U.id = ET.teller_id
                    LEFT JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                    WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_CategoryChangedTicketIcTeller`(IN `start_date` DATETIME, IN `end_date` DATETIME, IN `ic` INT, IN `teller` INT)
    NO SQL
SELECT ET.entrance_serial,ET.entrance_time,ET.exit_serial,ET.exit_time,IC.name ic,B.name booth,ET.amount,V.name vc,U.username
                    FROM rpt_category_changed_ticket_summary ET
                    INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                    INNER JOIN tbl_booth B ON B.id = ET.booth_id
                    INNER JOIN user U ON U.id = ET.teller_id
                    LEFT JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                    WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date  AND ET.teller_id = teller AND ET.exit_ic = ic$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_AllICExitSummary`(IN `startDate` DATETIME, IN `endDate` DATETIME)
    NO SQL
SELECT tbl_ic.name as Interchange, 
SUM(cat1_count) as Cat1, 
SUM(cat2_count) as Cat2,
SUM(cat3_count) as Cat3,
SUM(cat4_count) as Cat4,
SUM(catS_count) as CatS,
SUM(catPS_count) as CatPS,
SUM(catPMS_count) as CatPMS,
SUM(catAmbulance_count) as CatAmbulance,
SUM(catNotAssigned_count) as CatNotAssigned,
(SUM(cat1_count) + SUM(cat2_count)+ SUM(cat3_count)+ SUM(cat4_count)+ SUM(catS_count)+ SUM(catPS_count)+ SUM(catPMS_count)+ SUM(catAmbulance_count)+ SUM(catNotAssigned_count)) AS Total
FROM `rpt_hourly_traffic_directional_exit_revenue` 
INNER JOIn tbl_ic ON tbl_ic.id = rpt_hourly_traffic_directional_exit_revenue.exit_ic
WHERE DATE_ADD(day, interval (`hour`) hour) >= startDate AND DATE_ADD(day, interval (`hour`) hour) < endDate
GROUP BY exit_ic$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_ExpiredTicketIc`(IN `start_date` DATETIME, IN `end_date` DATETIME, IN `ic` INT)
    NO SQL
SELECT ET.exit_serial,ET.entrance_time,ET.exit_time,R.max_time,V.name vc,ET.amount,U.username,IC.name ic,B.name booth,D.name dname
                FROM rpt_expired_ticket_summary ET
                INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                INNER JOIN tbl_booth B ON B.id = ET.booth_id
                INNER JOIN tbl_route R ON R.source = ET.entrance_ic AND R.destination = ET.exit_ic
                INNER JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                INNER JOIN user U ON U.id = ET.teller_id
                INNER JOIN tbl_devices D ON D.id = ET.device_id
                WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date AND ET.exit_ic = ic$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_BookReceiptTicketAnyIc`(IN `start_date` DATETIME, IN `end_date` DATETIME)
    NO SQL
SELECT ET.exit_serial,IFNULL(ET.entrance_serial,'-') entrance_serial,IFNULL(ET.entrance_time,'-') entrance_time,IC2.name ic_entrance,IC.name ic_exit,V.name vc,ET.amount,B.name booth,U.username,ET.exit_time
                FROM rpt_book_receipt_ticket_summary ET
                LEFT JOIN tbl_ic IC ON IC.id = ET.exit_ic
                LEFT JOIN tbl_ic IC2 ON IC2.id = ET.entrance_ic
                LEFT JOIN tbl_booth B ON B.id = ET.booth_id
                LEFT JOIN user U ON U.id = ET.teller_id
                LEFT JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_HourlyRevenueDirectionalExitIcAny`(IN `startDate` DATETIME)
    NO SQL
SELECT 
ti.start_time,
ti.end_time,
t.hour,
SUM(t.cat1_amount) AS cat1,
SUM(t.cat2_amount) AS cat2,
SUM(t.cat3_amount) AS cat3,
SUM(t.cat4_amount) AS cat4,
SUM(t.catS_amount) AS s,
SUM(t.catPS_amount) AS ps,
SUM(t.catPMS_amount) AS pms,
SUM(t.catAmbulance_amount) AS catAmbulance,
SUM(t.catNotAssigned_amount) AS catNotAssigned,
(
    SUM(t.cat1_amount) + 
    SUM(t.cat2_amount)+
    SUM(t.cat3_amount)+
SUM(t.cat4_amount)+
SUM(t.catS_amount) +
SUM(t.catPS_amount)+
SUM(t.catPMS_amount) +
SUM(t.catAmbulance_amount) +
SUM(t.catNotAssigned_amount) 

) AS total,
t.direction 
FROM `rpt_hourly_traffic_directional_exit_revenue` t
INNER JOIn tbl_ic ic ON ic.id = t.exit_ic
INNER JOIN time_intervals_for_report ti ON ti.hour_number = t.hour
WHERE t.day = startDate
GROUP BY t.hour,t.direction,ti.start_time,
ti.end_time$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_HourlyRevenueDirectionalExitIc`(IN `ic_id` INT, IN `startDate` DATETIME)
    NO SQL
SELECT 
ti.start_time,
ti.end_time,
ic.id,
t.hour,
cat1_amount as cat1,
cat2_amount as cat2,
cat3_amount as cat3,
cat4_amount as cat4,
catS_amount as s,
catPS_amount as ps,
catPMS_amount as pms,
(cat1_amount+cat2_amount+cat3_amount+cat4_amount+catS_amount+catPS_amount+catPMS_amount) as total,
t.direction 
FROM `rpt_hourly_traffic_directional_exit_revenue` t
INNER JOIn tbl_ic ic ON ic.id = t.exit_ic
INNER JOIN time_intervals_for_report ti ON ti.hour_number = t.hour
WHERE t.day = startDate AND ic.id = ic_id$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_ExpiredTicketAnyIc`(IN `start_date` DATETIME, IN `end_date` DATETIME)
    NO SQL
SELECT ET.exit_serial,ET.entrance_time,ET.exit_time,R.max_time,V.name vc,ET.amount,U.username,IC.name ic,B.name booth,D.name dname
                FROM rpt_expired_ticket_summary ET
                INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                INNER JOIN tbl_booth B ON B.id = ET.booth_id
                INNER JOIN tbl_route R ON R.source = ET.entrance_ic AND R.destination = ET.exit_ic
                INNER JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                INNER JOIN user U ON U.id = ET.teller_id
                INNER JOIN tbl_devices D ON D.id = ET.device_id
                WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_CategoryChangedTicketIcAnyTeller`(IN `start_date` DATETIME, IN `end_date` DATETIME, IN `ic` INT)
    NO SQL
SELECT ET.entrance_serial,ET.entrance_time,ET.exit_serial,ET.exit_time,IC.name ic,B.name booth,ET.amount,V.name vc,U.username
                    FROM rpt_category_changed_ticket_summary ET
                    INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                    INNER JOIN tbl_booth B ON B.id = ET.booth_id
                    INNER JOIN user U ON U.id = ET.teller_id
                    LEFT JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                    WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date AND ET.exit_ic = ic$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_LostTicketAny`(IN `start_date` DATETIME, IN `end_date` DATETIME)
    NO SQL
SELECT ET.exit_serial,ET.exit_time,ET.amount,IC.name ic,B.name booth
                    FROM rpt_lost_ticket_summary ET
                    INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                    INNER JOIN tbl_booth B ON B.id = ET.booth_id
                    WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_LostTicketIc`(IN `start_date` DATETIME, IN `end_date` DATETIME, IN `ic` INT)
    NO SQL
SELECT ET.exit_serial,ET.exit_time,ET.amount,IC.name ic,B.name booth
                    FROM rpt_lost_ticket_summary ET
                    INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                    INNER JOIN tbl_booth B ON B.id = ET.booth_id
                    WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date AND ET.exit_ic = ic$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_ManualTicketAnyIcTeller`(IN `start_date` DATETIME, IN `end_date` DATETIME, IN `teller` INT)
    NO SQL
SELECT ET.entrance_serial,ET.entrance_time,ET.exit_serial,ET.exit_time,IC.name ic,B.name booth,ET.amount,V.name vc,U.username
                    FROM rpt_mannual_ticket_summary ET
                    INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                    INNER JOIN tbl_booth B ON B.id = ET.booth_id
                    INNER JOIN user U ON U.id = ET.teller_id
                    LEFT JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                    WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date  AND ET.teller_id = teller$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_ManualTicketIcAnyTeller`(IN `start_date` DATETIME, IN `end_date` DATETIME, IN `ic` INT)
    NO SQL
SELECT ET.entrance_serial,ET.entrance_time,ET.exit_serial,ET.exit_time,IC.name ic,B.name booth,ET.amount,V.name vc,U.username
                    FROM rpt_mannual_ticket_summary ET
                    INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                    INNER JOIN tbl_booth B ON B.id = ET.booth_id
                    INNER JOIN user U ON U.id = ET.teller_id
                    LEFT JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                    WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date  AND ET.exit_ic = ic$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_HourlyTrafficDirectionalExit`(IN `ic_id` INT, IN `startDate` DATETIME)
    NO SQL
SELECT 
ti.start_time,
ti.end_time,
ic.id,
t.hour,
cat1_count as cat1,
cat2_count as cat2,
cat3_count as cat3,
cat4_count as cat4,
catS_count as s,
catPS_count as ps,
catPMS_count as pms,
(cat1_count+cat2_count+cat3_count+cat4_count+catS_count+catPS_count+catPMS_count) as total,
t.direction 
FROM `rpt_hourly_traffic_directional_exit_revenue` t
INNER JOIn tbl_ic ic ON ic.id = t.exit_ic
INNER JOIN time_intervals_for_report ti ON ti.hour_number = t.hour
WHERE t.day = startDate AND ic.id = ic_id
ORDER BY t.hour,t.direction$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_ManualTicketAnyIcAnyTeller`(IN `start_date` DATETIME, IN `end_date` DATETIME)
    NO SQL
SELECT ET.entrance_serial,ET.entrance_time,ET.exit_serial,ET.exit_time,IC.name ic,B.name booth,ET.amount,V.name vc,U.username
                    FROM rpt_mannual_ticket_summary ET
                    INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                    INNER JOIN tbl_booth B ON B.id = ET.booth_id
                    INNER JOIN user U ON U.id = ET.teller_id
                    LEFT JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                    WHERE ET.exit_time >=start_date AND ET.exit_time <= end_date$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_ManualTicketIcTeller`(IN `start_date` DATETIME, IN `end_date` DATETIME, IN `ic` INT, IN `teller` INT)
    NO SQL
SELECT ET.entrance_serial,ET.entrance_time,ET.exit_serial,ET.exit_time,IC.name ic,B.name booth,ET.amount,V.name vc,U.username
                    FROM rpt_mannual_ticket_summary ET
                    INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                    INNER JOIN tbl_booth B ON B.id = ET.booth_id
                    INNER JOIN user U ON U.id = ET.teller_id
                    LEFT JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                    WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date  AND ET.teller_id = teller AND ET.exit_ic = ic$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_RePrintedTicketAnyIcTeller`(IN `start_date` DATETIME, IN `end_date` DATETIME, IN `teller` INT)
    NO SQL
BEGIN
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SELECT ET.entrance_serial,ET.entrance_time,ET.exit_serial,ET.exit_time,IC.name ic,B.name booth,ET.amount,V.name vc,U.username,COUNT(ET.id) as count
                    FROM rpt_re_printed_ticket_changed_ticket_summary  ET
                    INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                    INNER JOIN tbl_booth B ON B.id = ET.booth_id
                    INNER JOIN user U ON U.id = ET.teller_id
                    LEFT JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                    WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date AND ET.teller_id = teller
                    GROUP BY ET.exit_serial,ET.exit_time;
                    END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_RePrintedTicketIcAnyTeller`(IN `start_date` DATETIME, IN `end_date` DATETIME, IN `ic` INT)
    NO SQL
BEGIN
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SELECT ET.entrance_serial,ET.entrance_time,ET.exit_serial,ET.exit_time,IC.name ic,B.name booth,ET.amount,V.name vc,U.username,COUNT(ET.id) as count
                    FROM rpt_re_printed_ticket_changed_ticket_summary  ET
                    INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                    INNER JOIN tbl_booth B ON B.id = ET.booth_id
                    INNER JOIN user U ON U.id = ET.teller_id
                    LEFT JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                    WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date AND ET.exit_ic = ic
                    GROUP BY ET.exit_serial,ET.exit_time;
                    END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_RePrintedTicketAnyIcAnyTeller`(IN `start_date` DATETIME, IN `end_date` DATETIME)
    NO SQL
BEGIN
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

SELECT ET.entrance_serial,ET.entrance_time,ET.exit_serial,ET.exit_time,IC.name ic,B.name booth,ET.amount,V.name vc,U.username,COUNT(ET.id) as count
                    FROM rpt_re_printed_ticket_changed_ticket_summary ET
                    INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                    INNER JOIN tbl_booth B ON B.id = ET.booth_id
                    INNER JOIN user U ON U.id = ET.teller_id
                    LEFT JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                    WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date
                    GROUP BY ET.exit_serial,ET.exit_time;
 
 END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_Session_cash`(IN `year` INT, IN `monthNumber` INT)
    NO SQL
SELECT 
SUM(b.excess) as excess,
SUM(b.short) as short,
SUM(b.total_cash_amount) as total_cash_amount ,
SUM(b.total_system_amount) as total_system_amount 


FROM tbl_cash_collect as b 
WHERE b.status = 1 
AND b.actual_session_id 

IN (
    SELECT id as actual_session_id 
    FROM tbl_login_logout_session as a 
    WHERE YEAR(a.created_on) =year 
    AND  MONTH(a.created_on) =monthNumber )$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_TimePeriodDirectionalEntrance`(IN `startDate` DATE, IN `endDate` DATE)
    NO SQL
SELECT 
tbl_ic.name,
t.ic_id,
SUM(cat1_count) as cat1,
SUM(cat2_count) as cat2,
SUM(cat3_count) as cat3,
SUM(cat4_count) as cat4,
SUM(catS_count) as s,
SUM(catPS_count) as ps,
SUM(catPMS_count) as pms,
SUM(catAmbulance_count) as ambulance,
SUM(catNotAssigned_count) as notassigned,
t.direction ,
(
SUM(cat1_count) +
SUM(cat2_count)+
SUM(cat3_count) +
SUM(cat4_count)+
SUM(catS_count) +
SUM(catPS_count)+
SUM(catPMS_count)+
SUM(catAmbulance_count)+
SUM(catNotAssigned_count)
 
) as total

FROM `rpt_hourly_directional_traffic_entrance` t
INNER JOIN tbl_ic ON t.ic_id = tbl_ic.id
WHERE t.day >= startDate AND  t.day < endDate
GROUP BY t.ic_id,t.direction
ORDER BY t.ic_id$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_TimePeriodDirectionalExit`(IN `startDate` DATE, IN `endDate` DATE)
    NO SQL
SELECT 
tbl_ic.name,
t.exit_ic,
SUM(cat1_count) as cat1,
SUM(cat2_count) as cat2,
SUM(cat3_count) as cat3,
SUM(cat4_count) as cat4,
SUM(catS_count) as s,
SUM(catPS_count) as ps,
SUM(catPMS_count) as pms,
SUM(catAmbulance_count) as ambulance,
SUM(catNotAssigned_count) as notassigned,
t.direction ,
(
SUM(cat1_count) +
SUM(cat2_count)+
SUM(cat3_count) +
SUM(cat4_count)+
SUM(catS_count) +
SUM(catPS_count)+
SUM(catPMS_count) + SUM(catAmbulance_count)+ SUM(catNotAssigned_count)
) as total

FROM `rpt_hourly_traffic_directional_exit_revenue` t
LEFT JOIN tbl_ic ON t.exit_ic = tbl_ic.id
WHERE DATE_ADD(t.day, interval (t.`hour`) hour) >= startDate AND  DATE_ADD(t.day, interval (t.`hour`) hour) < endDate
GROUP BY t.exit_ic,t.direction
ORDER BY t.exit_ic$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_HourlyTrafficDirectionalEntrance`(IN `ic_id` INT, IN `startDate` DATE)
    NO SQL
SELECT 
ti.start_time,
ti.end_time,
t.ic_id,
t.hour,
SUM(cat1_count) as cat1,
SUM(cat2_count) as cat2,
SUM(cat3_count) as cat3,
SUM(cat4_count) as cat4,
SUM(catS_count) as s,
SUM(catPS_count) as ps,
SUM(catPMS_count) as pms,
t.direction ,
(
SUM(cat1_count) +
SUM(cat2_count)+
SUM(cat3_count) +
SUM(cat4_count)+
SUM(catS_count) +
SUM(catPS_count)+
SUM(catPMS_count) 
) as total

FROM `rpt_hourly_directional_traffic_entrance` t
INNER JOIn time_intervals_for_report ti ON t.hour = ti.hour_number
WHERE t.day = startDate AND t.ic_id = ic_id
GROUP BY t.ic_id,t.hour,t.direction,ti.start_time,
ti.end_time
ORDER BY t.hour,t.direction$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_TimePeriodDirectionalExitRevenue`(IN `startDate` DATE, IN `endDate` DATE)
    NO SQL
SELECT 
tbl_ic.name,
t.exit_ic,
t.direction,
SUM(t.cat1_amount) AS cat1,
SUM(t.cat2_amount) AS cat2,
SUM(t.cat3_amount) AS cat3,
SUM(t.cat4_amount) AS cat4,
SUM(t.catS_amount) AS s,
SUM(t.catPS_amount) AS ps,
SUM(t.catPMS_amount) AS pms,
SUM(catAmbulance_amount) as ambulance,
SUM(catNotAssigned_amount) as notassigned,
(
    SUM(t.cat1_amount) + 
    SUM(t.cat2_amount)+
    SUM(t.cat3_amount)+
SUM(t.cat4_amount)+
SUM(t.catS_amount) +
SUM(t.catPS_amount)+
SUM(t.catPMS_amount) +
SUM(t.catAmbulance_amount) +
SUM(t.catNotAssigned_amount) 

) AS total

FROM `rpt_hourly_traffic_directional_exit_revenue` t
LEFT JOIN tbl_ic ON t.exit_ic = tbl_ic.id
WHERE DATE_ADD(t.day, interval (`hour`) hour) >= startDate AND DATE_ADD(t.day, interval (`hour`) hour) < endDate
GROUP BY t.exit_ic,t.direction
ORDER BY t.exit_ic$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `fareCollectionIcExitSummary`(IN `startDate` DATETIME, IN `endDate` DATETIME)
BEGIN
    DECLARE bDone INT DEFAULT FALSE;
    DECLARE icId INT;
	DECLARE icName VARCHAR(50);
    

    DECLARE curs CURSOR FOR  (
		SELECT id,name
		FROM `tbl_ic`
		ORDER by id
		);
                
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = TRUE;

    DROP TABLE IF EXISTS IcExitSummary;
    CREATE TABLE IF NOT EXISTS IcExitSummary (
        ID int NOT NULL AUTO_INCREMENT,
        ic varchar(50),
        ticket_type varchar(50),
        ticket_count varchar(50),
        ticket_amount varchar(50),
        PRIMARY KEY (ID)

        
    );

    OPEN curs;

    
    loopEmp: LOOP
        FETCH curs INTO icID,icName;

        IF bDone THEN
            LEAVE loopEmp;
        END IF;

		INSERT INTO IcExitSummary (ic,ticket_type,ticket_count,ticket_amount)
        SELECT icName,VC.name,COUNT(ET.id),IFNULL(SUM(ET.amount),0)
		FROM tbl_vehicle_category VC
		LEFT JOIN tbl_exit_ticket ET ON ET.vehicle_category = VC.id AND ET.exit_time >= startDate AND ET.exit_time <= endDate AND ET.exit_ic = icID
		GROUP BY VC.id;
        
            
    END LOOP;

    CLOSE curs;
    
    
    
    END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `hourlySectionalTraffic`(IN `startDate` DATETIME, IN `endDate` DATETIME, IN `fIc` TIME, IN `sIc` TIME)
BEGIN
    DECLARE bDone INT DEFAULT FALSE;
    DECLARE sTime VARCHAR(50);
    DECLARE eTime VARCHAR(50);
    

    DECLARE curs CURSOR FOR  (
		SELECT start_time,end_time
		FROM `time_intervals_for_report`
		);
                
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = TRUE;

    DROP TABLE IF EXISTS hourly_sectional_traffic_report;
    CREATE TABLE IF NOT EXISTS hourly_sectional_traffic_report (
        ID int NOT NULL AUTO_INCREMENT,
        time varchar(50),
        direction varchar(50),
        c1 varchar(50),
        c2 varchar(50),
        c3 varchar(50),
        c4 varchar(50),
        c5 varchar(50),
        c6 varchar(50),
        c7 varchar(50),
        c8 varchar(50),
        c9 varchar(50),
        c10 varchar(50),
        c11 varchar(50),
        c12 varchar(50),
        PRIMARY KEY (ID)

        
    );

    OPEN curs;

    
    loopEmp: LOOP
        FETCH curs INTO sTime,eTime;

        IF bDone THEN
            LEAVE loopEmp;
        END IF;

		INSERT INTO hourly_sectional_traffic_report (time,direction,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12)
        SELECT  concat(sTime,'-',eTime),k.direction,IFNULL(sum(CASE WHEN VC.category_num_for_report = 1 THEN 1 ELSE 0 END),0) as C1,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 2 THEN 1 ELSE 0 END),0) as C2,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 3 THEN 1 ELSE 0 END),0) as C3,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 4 THEN 1 ELSE 0 END),0) as C4,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 5 THEN 1 ELSE 0 END),0) as C5,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 6 THEN 1 ELSE 0 END),0) as C6,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 7 THEN 1 ELSE 0 END),0) as C7,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 8 THEN 1 ELSE 0 END),0) as C8,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 9 THEN 1 ELSE 0 END),0) as C9,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 10 THEN 1 ELSE 0 END),0) as C10,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 11 THEN 1 ELSE 0 END),0) as C11,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 12 THEN 1 ELSE 0 END),0) as C12
        FROM(SELECT ENT.id,ENT.vehicle_category,ENT.entrance_time,ENT.entrance_ic,R.direction
            FROM tbl_entrance_ticket ENT
            INNER JOIN tbl_exit_ticket EXT ON EXT.entrance_barcode = ENT.serial_no
            INNER JOIN tbl_route R ON R.source = ENT.entrance_ic AND R.destination = EXT.exit_ic AND R.direction = 1
            WHERE ENT.entrance_time >= startDate AND ENT.entrance_time <= endDate AND time(ENT.entrance_time) > sTime AND time(ENT.entrance_time) <= eTime AND  (ENT.entrance_ic = fIc OR ENT.entrance_ic = sIc)
            UNION
            SELECT EXT1.id,EXT1.vehicle_category,EXT1.exit_time,EXT1.exit_ic,R.direction
            FROM tbl_exit_ticket EXT1
            INNER JOIN tbl_route R ON R.source = EXT1.entrance_ic AND R.destination = EXT1.exit_ic AND R.direction = 1
            WHERE EXT1.exit_time >= startDate AND EXT1.exit_time <= endDate AND time(EXT1.exit_time) > sTime AND time(EXT1.exit_time) <= eTime AND  (EXT1.exit_ic = fIc OR EXT1.exit_ic = sIc)) as k
        INNER JOIN tbl_vehicle_category VC ON VC.id = k.vehicle_category

        UNION

        SELECT  '',k.direction,IFNULL(sum(CASE WHEN VC.category_num_for_report = 1 THEN 1 ELSE 0 END),0) as C1,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 2 THEN 1 ELSE 0 END),0) as C2,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 3 THEN 1 ELSE 0 END),0) as C3,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 4 THEN 1 ELSE 0 END),0) as C4,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 5 THEN 1 ELSE 0 END),0) as C5,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 6 THEN 1 ELSE 0 END),0) as C6,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 7 THEN 1 ELSE 0 END),0) as C7,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 8 THEN 1 ELSE 0 END),0) as C8,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 9 THEN 1 ELSE 0 END),0) as C9,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 10 THEN 1 ELSE 0 END),0) as C10,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 11 THEN 1 ELSE 0 END),0) as C11,
                IFNULL(sum(CASE WHEN VC.category_num_for_report = 12 THEN 1 ELSE 0 END),0) as C12
        FROM(SELECT ENT.id,ENT.vehicle_category,ENT.entrance_time,ENT.entrance_ic,R.direction
            FROM tbl_entrance_ticket ENT
            INNER JOIN tbl_exit_ticket EXT ON EXT.entrance_barcode = ENT.serial_no
            INNER JOIN tbl_route R ON R.source = ENT.entrance_ic AND R.destination = EXT.exit_ic AND R.direction = 2
            WHERE ENT.entrance_time >= startDate AND ENT.entrance_time <= endDate AND time(ENT.entrance_time) > sTime AND time(ENT.entrance_time) <= eTime AND (ENT.entrance_ic = fIc OR ENT.entrance_ic = sIc)
            UNION
            SELECT EXT1.id,EXT1.vehicle_category,EXT1.exit_time,EXT1.exit_ic,R.direction
            FROM tbl_exit_ticket EXT1
            INNER JOIN tbl_route R ON R.source = EXT1.entrance_ic AND R.destination = EXT1.exit_ic AND R.direction = 2
            WHERE EXT1.exit_time >= startDate AND EXT1.exit_time <= endDate AND time(EXT1.exit_time) > sTime AND time(EXT1.exit_time) <= eTime AND (EXT1.exit_ic = fIc OR EXT1.exit_ic = sIc)) as k
        INNER JOIN tbl_vehicle_category VC ON VC.id = k.vehicle_category;

        
            
    END LOOP;

    CLOSE curs;
    
    
    END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `timePeriodRevenue`(IN `sDate` DATETIME, IN `eDate` DATETIME, IN `sTime` TIME, IN `eTime` TIME)
BEGIN
    DECLARE bDone INT DEFAULT FALSE;
    DECLARE ic_id VARCHAR(50);
    DECLARE ic_name VARCHAR(50);
    

    DECLARE curs CURSOR FOR  (
		SELECT IC.id,IC.name 
        FROM tbl_ic IC 
    );
                
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = TRUE;

    DROP TABLE IF EXISTS time_period_revenue_report;
    CREATE TABLE IF NOT EXISTS time_period_revenue_report (
        ID int NOT NULL AUTO_INCREMENT,
        ic varchar(50),
        direction varchar(50),
        c1 varchar(50),
        c2 varchar(50),
        c3 varchar(50),
        c4 varchar(50),
        c5 varchar(50),
        c6 varchar(50),
        c7 varchar(50),
        c8 varchar(50),
        c9 varchar(50),
        c10 varchar(50),
        c11 varchar(50),
        c12 varchar(50),
        PRIMARY KEY (ID)

        
    );

    OPEN curs;

    
    loopEmp: LOOP
        FETCH curs INTO ic_id,ic_name;

        IF bDone THEN
            LEAVE loopEmp;
        END IF;

		INSERT INTO time_period_revenue_report (ic,direction,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12)
        SELECT ic_name,R.direction,IFNULL(sum(CASE WHEN VC.category_num_for_report = 1 THEN ET.amount ELSE 0 END),0) as C1,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 2 THEN ET.amount ELSE 0 END),0) as C2,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 3 THEN ET.amount ELSE 0 END),0) as C3,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 4 THEN ET.amount ELSE 0 END),0) as C4,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 5 THEN ET.amount ELSE 0 END),0) as C5,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 6 THEN ET.amount ELSE 0 END),0) as C6,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 7 THEN ET.amount ELSE 0 END),0) as C7,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 8 THEN ET.amount ELSE 0 END),0) as C8,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 9 THEN ET.amount ELSE 0 END),0) as C9,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 10 THEN ET.amount ELSE 0 END),0) as C10,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 11 THEN ET.amount ELSE 0 END),0) as C11,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 12 THEN ET.amount ELSE 0 END),0) as C12
        FROM tbl_vehicle_category VC
        INNER JOIN tbl_exit_ticket ET ON ET.vehicle_category = VC.id AND ET.exit_time >= sDate AND ET.exit_time <= eDate AND time(ET.exit_time) >= sTime AND time(ET.exit_time) < eTime AND ET.exit_ic = ic_id
        INNER JOIN tbl_route R ON R.source = ET.exit_ic AND R.destination = ET.entrance_ic AND R.direction = 1


        UNION

        SELECT '',R.direction,IFNULL(sum(CASE WHEN VC.category_num_for_report = 1 THEN ET.amount ELSE 0 END),0) as C1,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 2 THEN ET.amount ELSE 0 END),0) as C2,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 3 THEN ET.amount ELSE 0 END),0) as C3,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 4 THEN ET.amount ELSE 0 END),0) as C4,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 5 THEN ET.amount ELSE 0 END),0) as C5,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 6 THEN ET.amount ELSE 0 END),0) as C6,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 7 THEN ET.amount ELSE 0 END),0) as C7,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 8 THEN ET.amount ELSE 0 END),0) as C8,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 9 THEN ET.amount ELSE 0 END),0) as C9,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 10 THEN ET.amount ELSE 0 END),0) as C10,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 11 THEN ET.amount ELSE 0 END),0) as C11,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 12 THEN ET.amount ELSE 0 END),0) as C12
        FROM tbl_vehicle_category VC
        INNER JOIN tbl_exit_ticket ET ON ET.vehicle_category = VC.id AND ET.exit_time >= sDate AND ET.exit_time <= eDate AND time(ET.exit_time) >= sTime AND time(ET.exit_time) < eTime AND ET.exit_ic = ic_id
        INNER JOIN tbl_route R ON R.source = ET.exit_ic AND R.destination = ET.entrance_ic AND R.direction = 2;

        
            
    END LOOP;

    CLOSE curs;
    
    
    END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `hourlyTraffic`(IN `date` DATE, IN `ic` INT)
BEGIN
    DECLARE bDone INT DEFAULT FALSE;
    DECLARE sTime VARCHAR(50);
    DECLARE eTime VARCHAR(50);
    

    DECLARE curs CURSOR FOR  (
		SELECT start_time,end_time
		FROM `time_intervals_for_report`
		);
                
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = TRUE;

    DROP TABLE IF EXISTS hourly_traffic_report;
    CREATE TABLE IF NOT EXISTS hourly_traffic_report (
        ID int NOT NULL AUTO_INCREMENT,
        time varchar(50),
        direction varchar(50),
        c1 varchar(50),
        c2 varchar(50),
        c3 varchar(50),
        c4 varchar(50),
        c5 varchar(50),
        c6 varchar(50),
        c7 varchar(50),
        c8 varchar(50),
        c9 varchar(50),
        c10 varchar(50),
        c11 varchar(50),
        c12 varchar(50),
        PRIMARY KEY (ID)

        
    );

    OPEN curs;

    
    loopEmp: LOOP
        FETCH curs INTO sTime,eTime;

        IF bDone THEN
            LEAVE loopEmp;
        END IF;

		INSERT INTO hourly_traffic_report (time,direction,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12)
        SELECT concat(sTime,'-',eTime),R.direction,IFNULL(sum(CASE WHEN VC.category_num_for_report = 1 THEN 1 ELSE 0 END),0) as C1,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 2 THEN 1 ELSE 0 END),0) as C2,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 3 THEN 1 ELSE 0 END),0) as C3,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 4 THEN 1 ELSE 0 END),0) as C4,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 5 THEN 1 ELSE 0 END),0) as C5,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 6 THEN 1 ELSE 0 END),0) as C6,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 7 THEN 1 ELSE 0 END),0) as C7,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 8 THEN 1 ELSE 0 END),0) as C8,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 9 THEN 1 ELSE 0 END),0) as C9,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 10 THEN 1 ELSE 0 END),0) as C10,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 11 THEN 1 ELSE 0 END),0) as C11,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 12 THEN 1 ELSE 0 END),0) as C12
        FROM tbl_vehicle_category VC
        INNER JOIN tbl_exit_ticket ET ON ET.vehicle_category = VC.id AND date(ET.exit_time) = date AND time(ET.exit_time) >= sTime AND time(ET.exit_time) < eTime AND ET.exit_ic = ic
        INNER JOIN tbl_route R ON R.source = ET.exit_ic AND R.destination = ET.entrance_ic AND R.direction = 1


        UNION

        SELECT '',R.direction,IFNULL(sum(CASE WHEN VC.category_num_for_report = 1 THEN 1 ELSE 0 END),0) as C1,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 2 THEN 1 ELSE 0 END),0) as C2,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 3 THEN 1 ELSE 0 END),0) as C3,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 4 THEN 1 ELSE 0 END),0) as C4,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 5 THEN 1 ELSE 0 END),0) as C5,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 6 THEN 1 ELSE 0 END),0) as C6,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 7 THEN 1 ELSE 0 END),0) as C7,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 8 THEN 1 ELSE 0 END),0) as C8,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 9 THEN 1 ELSE 0 END),0) as C9,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 10 THEN 1 ELSE 0 END),0) as C10,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 11 THEN 1 ELSE 0 END),0) as C11,
        IFNULL(sum(CASE WHEN VC.category_num_for_report = 12 THEN 1 ELSE 0 END),0) as C12
        FROM tbl_vehicle_category VC
        INNER JOIN tbl_exit_ticket ET ON ET.vehicle_category = VC.id AND date(ET.exit_time) = date AND time(ET.exit_time) >= sTime AND time(ET.exit_time) < eTime AND ET.exit_ic = ic
        INNER JOIN tbl_route R ON R.source = ET.exit_ic AND R.destination = ET.entrance_ic AND R.direction = 2;

        
            
    END LOOP;

    CLOSE curs;
    
    
    END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `timePeriodTrafficExit`(IN `sDate` DATETIME, IN `eDate` DATETIME, IN `sTime` TIME, IN `eTime` TIME)
BEGIN
    DECLARE bDone INT DEFAULT FALSE;
    DECLARE ic_id VARCHAR(50);
    DECLARE ic_name VARCHAR(50);
    

    DECLARE curs CURSOR FOR  (
		SELECT IC.id,IC.name 
        FROM tbl_ic IC 
    );
                
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = TRUE;

    DROP TABLE IF EXISTS time_period_traffic_report;
    CREATE TABLE IF NOT EXISTS time_period_traffic_report (
        ID int NOT NULL AUTO_INCREMENT,
        ic varchar(50),
        direction varchar(50),
        c1 varchar(50),
        c2 varchar(50),
        c3 varchar(50),
        c4 varchar(50),
        c5 varchar(50),
        c6 varchar(50),
        c7 varchar(50),
        c8 varchar(50),
        c9 varchar(50),
        c10 varchar(50),
        c11 varchar(50),
        c12 varchar(50),
        PRIMARY KEY (ID)

        
    );

    OPEN curs;

    
    loopEmp: LOOP
        FETCH curs INTO ic_id,ic_name;

        IF bDone THEN
            LEAVE loopEmp;
        END IF;

		INSERT INTO time_period_traffic_report (ic,direction,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12)
        SELECT ic_name,R.direction,IFNULL(sum(CASE WHEN VC.category = 1 THEN 1 ELSE 0 END),0) as C1,
        IFNULL(sum(CASE WHEN VC.category = 2 THEN 1 ELSE 0 END),0) as C2,
        IFNULL(sum(CASE WHEN VC.category = 3 THEN 1 ELSE 0 END),0) as C3,
        IFNULL(sum(CASE WHEN VC.category = 4 THEN 1 ELSE 0 END),0) as C4,
        IFNULL(sum(CASE WHEN VC.category = 5 THEN 1 ELSE 0 END),0) as C5,
        IFNULL(sum(CASE WHEN VC.category = 6 THEN 1 ELSE 0 END),0) as C6,
        IFNULL(sum(CASE WHEN VC.category = 7 THEN 1 ELSE 0 END),0) as C7,
        IFNULL(sum(CASE WHEN VC.category = 8 THEN 1 ELSE 0 END),0) as C8,
        IFNULL(sum(CASE WHEN VC.category = 9 THEN 1 ELSE 0 END),0) as C9,
        IFNULL(sum(CASE WHEN VC.category = 10 THEN 1 ELSE 0 END),0) as C10,
        IFNULL(sum(CASE WHEN VC.category = 11 THEN 1 ELSE 0 END),0) as C11,
        IFNULL(sum(CASE WHEN VC.category = 12 THEN 1 ELSE 0 END),0) as C12
        FROM tbl_vehicle_category VC
        INNER JOIN tbl_exit_ticket ET ON ET.vehicle_category = VC.id AND ET.exit_time >= sDate AND ET.exit_time <= eDate AND time(ET.exit_time) >= sTime AND time(ET.exit_time) < eTime AND ET.exit_ic = ic_id
        INNER JOIN tbl_route R ON R.source = ET.exit_ic AND R.destination = ET.entrance_ic AND R.direction = 1


        UNION

        SELECT '',R.direction,IFNULL(sum(CASE WHEN VC.category = 1 THEN 1 ELSE 0 END),0) as C1,
        IFNULL(sum(CASE WHEN VC.category = 2 THEN 1 ELSE 0 END),0) as C2,
        IFNULL(sum(CASE WHEN VC.category = 3 THEN 1 ELSE 0 END),0) as C3,
        IFNULL(sum(CASE WHEN VC.category = 4 THEN 1 ELSE 0 END),0) as C4,
        IFNULL(sum(CASE WHEN VC.category = 5 THEN 1 ELSE 0 END),0) as C5,
        IFNULL(sum(CASE WHEN VC.category = 6 THEN 1 ELSE 0 END),0) as C6,
        IFNULL(sum(CASE WHEN VC.category = 7 THEN 1 ELSE 0 END),0) as C7,
        IFNULL(sum(CASE WHEN VC.category = 8 THEN 1 ELSE 0 END),0) as C8,
        IFNULL(sum(CASE WHEN VC.category = 9 THEN 1 ELSE 0 END),0) as C9,
        IFNULL(sum(CASE WHEN VC.category = 10 THEN 1 ELSE 0 END),0) as C10,
        IFNULL(sum(CASE WHEN VC.category = 11 THEN 1 ELSE 0 END),0) as C11,
        IFNULL(sum(CASE WHEN VC.category = 12 THEN 1 ELSE 0 END),0) as C12
        FROM tbl_vehicle_category VC
        INNER JOIN tbl_exit_ticket ET ON ET.vehicle_category = VC.id AND ET.exit_time >= sDate AND ET.exit_time <= eDate AND time(ET.exit_time) >= sTime AND time(ET.exit_time) < eTime AND ET.exit_ic = ic_id
        INNER JOIN tbl_route R ON R.source = ET.exit_ic AND R.destination = ET.entrance_ic AND R.direction = 2;

        
            
    END LOOP;

    CLOSE curs;
    
    
    END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_RePrintedTicketIcTeller`(IN `start_date` DATETIME, IN `end_date` DATETIME, IN `ic` INT)
    NO SQL
BEGIN
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SELECT ET.entrance_serial,ET.entrance_time,ET.exit_serial,ET.exit_time,IC.name ic,B.name booth,ET.amount,V.name vc,U.username,COUNT(ET.id) as count
                    FROM tbl_all_exit_ticket ET
                    INNER JOIN tbl_ic IC ON IC.id = ET.exit_ic
                    INNER JOIN tbl_booth B ON B.id = ET.booth_id
                    INNER JOIN user U ON U.id = ET.teller_id
                    LEFT JOIN tbl_vehicle_category V ON V.id = ET.vehicle_category
                    WHERE ET.exit_time >= start_date AND ET.exit_time <= end_date AND ET.teller_id = teller AND ET.exit_ic = ic
                    GROUP BY ET.exit_serial,ET.exit_time;
                    END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`eimuser`@`localhost` PROCEDURE `Rpt_TimePeriodDirectionalExitRevenueHighway`(IN `startDate` DATE, IN `endDate` DATE, IN `reg_id` INT)
    NO SQL
SELECT 
tbl_ic.name,
t.exit_ic,
t.direction,
SUM(t.cat1_amount) AS cat1,
SUM(t.cat2_amount) AS cat2,
SUM(t.cat3_amount) AS cat3,
SUM(t.cat4_amount) AS cat4,
SUM(t.catS_amount) AS s,
SUM(t.catPS_amount) AS ps,
SUM(t.catPMS_amount) AS pms,
SUM(catAmbulance_amount) as ambulance,
SUM(catNotAssigned_amount) as notassigned,
(
    SUM(t.cat1_amount) + 
    SUM(t.cat2_amount)+
    SUM(t.cat3_amount)+
SUM(t.cat4_amount)+
SUM(t.catS_amount) +
SUM(t.catPS_amount)+
SUM(t.catPMS_amount) +
SUM(t.catAmbulance_amount) +
SUM(t.catNotAssigned_amount) 

) AS total

FROM `rpt_hourly_traffic_directional_exit_revenue` t
LEFT JOIN tbl_ic ON t.exit_ic = tbl_ic.id
WHERE tbl_ic.region_id = reg_id 
AND DATE_ADD(t.day, interval (`hour`) hour) >= startDate AND DATE_ADD(t.day, interval (`hour`) hour) < endDate
GROUP BY t.exit_ic,t.direction
ORDER BY t.exit_ic$$
DELIMITER ;