客运公司运输管理系统（简易版）

概述

  本系统主要实现车型、路线、驾驶员等信息的管理; 实现线路车次、线路站点及票价管理; 实现售票管理; 使用的数据库为免费开源的MYsql数据库，可以在一定程度上减少客运的运营成本,并且系统完全围绕信息在组织内部的流动设计的，因此，数据表不仅业务信息过程，又充分表达了与组织的紧密关系。同时，系统的开发思维是自顶向下的,严格遵守“生命开发周期”的过程。

一、信息系统规划

• 规划的基本问题：客运公司，为适应新时代的发展需求，需要提高业务效率，降低运营成本，更好服务于大众？
• 内部和外部环境：业务过程慢,运营成本高;高铁、滴滴等快速发展竞争激烈；
• 现状约束：运营成本过高，服务效率低下；
• 愿景和目标：服务环境优越，业务效率快速，利润有所提升；
• 解决方案：引进信息管理系统；

二、信息系统分析

  主要解决业务效率问题？业务的过程涉及客户、用户、车辆、司机、路线、票价等信息，大致可以概括为公司预售上线到用户购买车票，再到乘车的业务过程。

图 1-1 客运运输管理流程图

三、信息系统设计

1.构建ER图

  公司的组织内部信息流大致包括：车辆表、司机表、班次表、用户表、乘客表、车票表等。

图 1-2 客运运输管理ER图

2.转换为关系模型

车辆表(车牌号、类型、座位数);
司机表(司机编号、姓名、性别、年龄、出生日期、驾照、联系电话);
班次表(班次编号、出发地、目的地、发车时间、到站时间、车牌号、司机编号、状态);
乘客表(乘客编号、姓名、性别、出生日期、联系电话);
用户表(用户编号、姓名、性别、出生日期、联系电话);
车票表(车票编号、班次编号、乘客编号、用户编号、购票时间、状态、座位号);

3.数据字典

车票表

乘客表

用户表

班次表

司机表

车辆表

座位表

四、信息系统实施

1.创建函数和事件

（1）生成编号函数

DELIMITER $$

USE `transport`$$

DROP FUNCTION IF EXISTS `fun_sequence`$$
-- @编号前缀，@是否选取到秒
CREATE DEFINER=`root`@`localhost` FUNCTION `fun_sequence`(pre VARCHAR(12),flag INT) RETURNS VARCHAR(30) CHARSET utf8mb4
BEGIN
DECLARE str VARCHAR(30);
IF flag=1 THEN
SET str=CONCAT(pre,TRIM(DATE_FORMAT(NOW(),'%y%m%d%H%i%s')));
ELSE
SET str=CONCAT(pre,TRIM(DATE_FORMAT(NOW(),'%y%m%d%H%i')));
END IF;
RETURN str;
END$$

DELIMITER ;

（2）自动过期事件

DELIMITER $$

ALTER DEFINER=`root`@`localhost` EVENT `e_ticket_status` 
ON SCHEDULE EVERY 24 HOUR STARTS '2018-10-15 01:22:33' 
ON COMPLETION PRESERVE ENABLE DO CALL pro_ticket_status()$$ -- 调用过期存储

DELIMITER ;

2.创建触发器

（1）添加班次触发器–生成对应的座位表数据

DELIMITER $$

USE `transport`$$

DROP TRIGGER /*!50032 IF EXISTS */ `trg_trainN_insert`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `trg_trainN_insert` AFTER INSERT ON `班次表` 
    FOR EACH ROW BEGIN
DECLARE num INT;
DECLARE trainN_no VARCHAR(12);
SET trainN_no=new.班次编号;
SELECT 座位数 INTO num FROM 班次表 WHERE 班次编号=new.班次编号;
WHILE num>0 DO
INSERT INTO 座位表 VALUES(trainN_no,num,'未售');
SET num=num-1;
END WHILE;
END;
$$

DELIMITER ;

（2）取消班次触发器–删除相关对应表数据

DELIMITER $$

USE `transport`$$

DROP TRIGGER /*!50032 IF EXISTS */ `trg_trainN_delete`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `trg_trainN_delete` BEFORE DELETE ON `班次表` 
    FOR EACH ROW BEGIN
DELETE  FROM 座位表 WHERE 班次编号=old.班次编号;
DELETE  FROM  车票表 WHERE 班次编号=old.班次编号;
DELETE  FROM   发车表 WHERE 班次编号=old.班次编号;

END;
$$

DELIMITER ;

（3）购票触发器 --修改相应班次表座位数，座位号状态;

DELIMITER $$

USE `transport`$$

DROP TRIGGER /*!50032 IF EXISTS */ `trg_ticket_insert`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `trg_ticket_insert` AFTER INSERT ON `车票表` 
    FOR EACH ROW BEGIN
DECLARE num INT;
SELECT 座位数 INTO num FROM 班次表 WHERE 班次编号=new.班次编号;
IF num>=1 THEN
UPDATE 班次表 SET 座位数=座位数-1 WHERE 班次编号=new.班次编号;
UPDATE 座位表 SET 状态='已售' WHERE 班次编号=new.班次编号 AND 座位号=new.座位号 ;
END IF;
END;
$$

DELIMITER ;

（4）退票触发器–修改相应班次表座位数，座位号状态;

DELIMITER $$

USE `transport`$$

DROP TRIGGER /*!50032 IF EXISTS */ `trg_ticket_update`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `trg_ticket_update` AFTER UPDATE ON `车票表` 
    FOR EACH ROW BEGIN
DECLARE startT DATETIME;
DECLARE statusT VARCHAR(30);
SELECT 发车时间 INTO startT FROM 班次表 WHERE 班次编号=new.班次编号;
SELECT 状态 INTO statusT FROM 车票表 WHERE 车票编号=new.车票编号;
IF statusT='退票' THEN
IF NOW()<startT THEN
UPDATE 班次表 SET 座位数=座位数+1 WHERE 班次编号=new.班次编号;
UPDATE 座位表 SET 状态='未售' WHERE 班次编号=new.班次编号 AND 座位号=new.座位号 ;
END IF;
END IF;
END;
$$

DELIMITER ;

3.创建存储过程

（1）创建班次存储

DELIMITER $$

USE `transport`$$

DROP PROCEDURE IF EXISTS `pro_trainN_insert`$$
-- @出发地，@目的地 ，@发车时间，@到站时间，@票价，@车辆编号，@司机编号
CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_trainN_insert`(IN departure VARCHAR(30),IN destination VARCHAR(30),
IN startT DATETIME,IN endT DATETIME,IN price DOUBLE,IN vehicle_no VARCHAR(12),IN driver_no VARCHAR(12))
BEGIN
DECLARE num INT;
DECLARE flag_dv INT DEFAULT 0;
DECLARE flag_vh INT DEFAULT 0;
DECLARE startTT DATETIME;
DECLARE endTT DATETIME;
DECLARE  not_found INT DEFAULT FALSE;
DECLARE traintN_curDV CURSOR FOR SELECT 发车时间,到站时间 FROM 班次表 WHERE 发车时间>NOW() AND 司机编号=driver_no;
DECLARE traintN_curVH CURSOR FOR SELECT 发车时间,到站时间 FROM 班次表 WHERE 发车时间>NOW() AND 车牌号=vehicle_no;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found=TRUE;

OPEN traintN_curDV;
FETCH traintN_curDV INTO startTT,endTT;
WHILE ! not_found DO
IF (startT BETWEEN startTT AND endTT )=1 THEN
    SET flag_dv=1;
END IF;
FETCH traintN_curDV INTO startTT,endTT;
END WHILE;

OPEN traintN_curVH;
FETCH traintN_curVH INTO startTT,endTT;
WHILE ! not_found DO
IF (startT BETWEEN startTT AND endTT )=1 THEN
    SET flag_vh=1;
END IF;
FETCH traintN_curVH INTO startTT,endTT;
END WHILE;

SELECT 座位数 INTO num FROM 车辆表 WHERE 车牌号=vehicle_no;
IF startT>NOW() AND flag_dv=0 AND flag_vh=0 THEN
INSERT INTO 班次表 VALUES(fun_sequence('dv',0),departure,destination,startT,endT,price,num,vehicle_no,driver_no,'未发车');
ELSE
ROLLBACK;
END IF;
END$$

DELIMITER ;

(2)取消班次存储

DELIMITER $$

USE `transport`$$
-- @班次编号
DROP PROCEDURE IF EXISTS `pro_trainN_delete`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_trainN_delete`(IN traiN_no VARCHAR(30))
BEGIN
DELETE FROM 班次表 WHERE 班次编号=traiN_no;
END$$

DELIMITER ;

(3)购票存储

ELIMITER $$

USE `transport`$$

DROP PROCEDURE IF EXISTS `pro_ticket_insert`$$
-- @用户编号，@乘客编号，@班次编号
CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_ticket_insert`(IN passenger_no VARCHAR(12),IN user_no VARCHAR(12),IN trainN_no VARCHAR(12))
BEGIN
DECLARE num INT;
DECLARE startT DATETIME;
DECLARE seatNumber INT;
DECLARE seat_cur CURSOR FOR SELECT 座位号 FROM 座位表 WHERE 状态='未售' AND 班次编号=trainN_no ORDER BY 座位号 ASC;
OPEN seat_cur;
FETCH seat_cur INTO seatNumber;
CLOSE seat_cur;
SELECT 座位数 INTO num FROM 班次表 WHERE 班次编号=trainN_no;
SELECT 发车时间 INTO startT FROM 班次表 WHERE 班次编号=trainN_no;
IF startT>NOW() AND num>=0 THEN
INSERT INTO 车票表 VALUES(fun_sequence('tk',1),passenger_no,user_no,trainN_no,NOW(),'未乘',seatNumber);
ELSE
ROLLBACK;
END IF;
END$$

DELIMITER ;

(4)退票存储

DELIMITER $$

USE `transport`$$

DROP PROCEDURE IF EXISTS `pro_ticket_update`$$
-- @车票编号
CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_ticket_update`(IN ticket_no VARCHAR(30))
BEGIN
DECLARE startT DATETIME;
DECLARE statusT VARCHAR(30);
SELECT 发车时间 INTO startT FROM 班次表 WHERE 班次编号=(SELECT 班次编号 FROM 车票表 WHERE 车票编号=ticket_no);
SELECT 状态 INTO statusT FROM 车票表 WHERE 车票编号=ticket_no;
IF statusT='未乘' THEN
IF NOW()<startT THEN
UPDATE 车票表 SET 状态='退票' WHERE  车票编号=ticket_no;
ELSE 
ROLLBACK;
END IF;
ELSE 
ROLLBACK;
END IF;
END$$

DELIMITER ;

(5)乘车存储

DELIMITER $$

USE `transport`$$

DROP PROCEDURE IF EXISTS `pro_riding_update`$$
-- @车票编号
CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_riding_update`(IN ticket_no VARCHAR(30))
BEGIN
DECLARE statusT VARCHAR(30);
DECLARE startT DATETIME;
SELECT 状态 INTO statusT FROM 车票表 WHERE 车票编号=ticket_no;
SELECT 发车时间 INTO startT FROM 班次表 WHERE 班次编号=(SELECT 班次编号 FROM 车票表 WHERE 车票编号=ticket_no);
IF NOW()>=SUBDATE(startT,INTERVAL  30 MINUTE) AND statusT='未乘' THEN
UPDATE 车票表 SET 状态='已乘' WHERE 车票编号=ticket_no;
ELSE
ROLLBACK;
END IF;
END$$

DELIMITER ;

(6)过期存储

DELIMITER $$

USE `transport`$$

DROP PROCEDURE IF EXISTS `pro_ticket_status`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_ticket_status`()
BEGIN
DECLARE trainT_no VARCHAR(30);
DECLARE startT DATETIME;
DECLARE  not_found INT DEFAULT FALSE;
DECLARE trainN_startT_cur CURSOR FOR SELECT 班次编号,发车时间  FROM 班次表;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found=TRUE;
OPEN trainN_startT_cur;
FETCH trainN_startT_cur INTO trainT_no,startT;
WHILE ! not_found DO
IF NOW()>ADDDATE(startT,INTERVAL 30 MINUTE) THEN
UPDATE 车票表 SET 状态='过期' WHERE 状态='未乘' AND 班次编号=trainT_no;
END IF;
FETCH trainN_startT_cur INTO trainT_no,startT;
END WHILE;
END$$

DELIMITER ;

五、信息系统评价和维护

  基本可以实现从预售到购票再到乘车的业务信息过程。但某些车辆、司机等信息仍然不完整。并且预售管理 – 定时间、定班次、定价格等等需要运营商根据实际情况（既满足客运需求又最大的降低运营成本等）进行设定。
  对于车辆、司机、用户、乘客等信息需要进行定期更新。