职工-工程数据库课程设计

以下GUI界面均为Navicat

1) E-R总体图&&关系模式

打?为主码
部门 department（部门号did?，部门名dname）
职位 job（职位号jid?，职位名jname，小时工资率srate）
职工 staff（职工号sid?，职工名 sname，部门号did,职位 jid）
工程 project（工程号pid?，工程名pname，工程所在地pl，开始日期pst，结束日期pet）
打卡 clockio（打卡类型punchTheClock（0上班1下班），时间timestamp?，职工号sid?，工程号pid?）
职员变更（记录号logid?，日期ltime，职工号sid，变更类型ctype，原先部门pd，现在部门cd，经手人handlerid）

2)DDL语句 建表/约束、存储过程或函数

建表、约束：

• CREATE TABLE `clockio` (
    `punchTheClock` tinyint(4) NOT NULL COMMENT ‘打卡类型（0上班1下班）‘,
    `time_stamp` timestamp NOT NULL COMMENT ‘时间‘,
    `sid` int(11) NOT NULL COMMENT ‘职工号‘,
    `pid` int(11) NOT NULL COMMENT ‘工程号‘,
    PRIMARY KEY (`time_stamp`,`sid`,`pid`) USING BTREE,
    KEY `sid` (`sid`),
    KEY `pid` (`pid`),
    CONSTRAINT `pid` FOREIGN KEY (`pid`) REFERENCES `project` (`pid`),
    CONSTRAINT `sid` FOREIGN KEY (`sid`) REFERENCES `staff` (`sid`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  

• CREATE TABLE `department` (
    `did` int(255) NOT NULL COMMENT ‘部门号‘,
    `dname` varchar(255) DEFAULT NULL COMMENT ‘部门名‘,
    KEY `did` (`did`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  

• CREATE TABLE `job` (
    `jid` int(11) NOT NULL COMMENT ‘职位号‘,
    `jname` varchar(255) DEFAULT NULL COMMENT ‘职位名‘,
    `srate` float(255,0) DEFAULT NULL COMMENT ‘小时工资率‘,
    PRIMARY KEY (`jid`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  

• CREATE TABLE `jobchanged` (
    `logid` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘记录号‘,
    `ltime` timestamp NOT NULL COMMENT ‘记录发生时间‘,
    `sid` int(11) NOT NULL COMMENT ‘职工号‘,
    `ctype` varchar(255) NOT NULL COMMENT ‘变更类型‘,
    `pd` int(11) DEFAULT NULL COMMENT ‘原先部门号‘,
    `cd` int(11) DEFAULT NULL COMMENT ‘现在部门号‘,
    `handlerid` int(11) DEFAULT NULL COMMENT ‘经手人编号‘,
    PRIMARY KEY (`logid`),
    KEY `sid1` (`sid`),
    KEY `pd` (`pd`),
    KEY `cd` (`cd`),
    KEY `handlerid` (`handlerid`),
    CONSTRAINT `cd` FOREIGN KEY (`cd`) REFERENCES `department` (`did`),
    CONSTRAINT `handlerid` FOREIGN KEY (`handlerid`) REFERENCES `staff` (`sid`),
    CONSTRAINT `pd` FOREIGN KEY (`pd`) REFERENCES `department` (`did`),
    CONSTRAINT `sid1` FOREIGN KEY (`sid`) REFERENCES `staff` (`sid`)
  ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  

• CREATE TABLE `project` (
    `pid` int(11) NOT NULL COMMENT ‘工程号‘,
    `pname` varchar(255) DEFAULT NULL COMMENT ‘工程名‘,
    `pl` varchar(255) DEFAULT NULL COMMENT ‘工程所在地‘,
    `pst` date DEFAULT NULL COMMENT ‘工程开始时间‘,
    `pet` date DEFAULT NULL COMMENT ‘工程结束时间‘,
    PRIMARY KEY (`pid`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  

• CREATE TABLE `staff` (
    `sid` int(11) NOT NULL COMMENT ‘职工号\r\n‘,
    `sname` varchar(255) DEFAULT NULL COMMENT ‘职工名‘,
    `did` int(11) DEFAULT NULL COMMENT ‘部门号‘,
    `jid` int(11) DEFAULT NULL COMMENT ‘职位号‘,
    PRIMARY KEY (`sid`),
    KEY `did` (`did`),
    KEY `jid` (`jid`),
    CONSTRAINT `did` FOREIGN KEY (`did`) REFERENCES `department` (`did`),
    CONSTRAINT `jid` FOREIGN KEY (`jid`) REFERENCES `job` (`jid`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  

存储过程/函数：

计算工程需要支付的工资：

CREATE DEFINER=`root`@`%` PROCEDURE `calculate_salary`(IN num INT,OUT result INT)
BEGIN
DECLARE exist INT;
DECLARE start_time TIMESTAMP;
DECLARE end_time TIMESTAMP;
SELECT count(*) INTO exist FROM project WHERE pid = num;
IF exist = 0 THEN
	SET result = -1;
ELSE
	SELECT DATE_FORMAT(pst,‘%Y-%m-%d‘) INTO start_time FROM project WHERE pid = num;
	SELECT DATE_FORMAT(pet,‘%Y-%m-%d‘) INTO end_time FROM project WHERE pid = num;
	SELECT SUM(TIMESTAMPDIFF(HOUR,stime,etime)*srate) INTO result 
	FROM 
	(SELECT a.sid,a.day1,stime,etime FROM
	((SELECT sid,DATE_FORMAT(time_stamp,‘%Y-%m-%d‘) AS day1,min(DATE_FORMAT(time_stamp,‘%Y-%m-%d %H:%i:%s‘)) stime	
	FROM clockio	
	WHERE punchTheClock = 0 AND pid = num AND 
	DATE_FORMAT(time_stamp,‘%Y-%m-%d‘) between start_time and end_time group by sid,day1) a,
	(SELECT sid,DATE_FORMAT(time_stamp,‘%Y-%m-%d‘) AS day1,max(DATE_FORMAT(time_stamp,‘%Y-%m-%d %H:%i:%s‘)) etime	
	FROM clockio	
	WHERE punchTheClock = 1 AND pid = num AND 
	DATE_FORMAT(time_stamp,‘%Y-%m-%d‘) between start_time and end_time group by sid,day1) b)
	WHERE a.sid = b.sid AND a.day1 = b.day1) AS job_time,staff,job 
	WHERE staff.sid = job_time.sid AND staff.jid = job.jid;
END IF;
END

职工职位变更：

CREATE DEFINER=`root`@`%` PROCEDURE `change_job`(IN `staff_id` int,IN `staff_name` varchar(255),IN `staff_job` int,IN `change_type` varchar(255),IN `new_department` int,IN `dealer` int,OUT `result` int)
BEGIN
	declare old_department int;
	IF change_type = ‘enter‘ THEN
		INSERT staff VALUES(staff_id,staff_name,new_department,staff_job);
		INSERT jobchanged(ltime,sid,ctype,pd,cd,handlerid) values(NOW(),staff_id,change_type,null,new_department,		dealer);
		SET result=1;
	ELSEIF change_type = ‘leave‘ THEN
		SELECT did INTO old_department FROM staff WHERE sid=staff_id;
		INSERT jobchanged(ltime,sid,ctype,pd,cd,handlerid) values(NOW(),staff_id,change_type,old_department,null,		dealer);
		DELETE from staff WHERE sid = staff_id;
		SET result=1;
	ELSE
		SELECT did INTO old_department FROM staff WHERE sid=staff_id;
		IF old_department = new_department THEN
			SET result=0;
		ELSE
			UPDATE staff SET did = new_department WHERE sid=staff_id;
			INSERT jobchanged(ltime,sid,ctype,pd,cd,handlerid) values(NOW(),staff_id,change_type,old_department,			new_department,dealer);
			SET result=1;
		END IF;
	END IF;
END

打卡：

CREATE DEFINER=`root`@`%` PROCEDURE `punch_in`(IN ptype tinyint,IN snum int,IN pnum int,OUT result INT)
BEGIN
DECLARE now_time TIMESTAMP;
DECLARE cnt INT;
DECLARE judge INT;
SELECT current_timestamp INTO now_time;
SELECT count(*) INTO cnt FROM clockio 
WHERE sid = snum AND punchTheClock = 0 
AND DATE_FORMAT(time_stamp,‘%Y-%m-%d‘) = DATE_FORMAT(now_time,‘%Y-%m-%d‘);
SELECT count(*) INTO judge FROM clockio 
WHERE sid = snum AND punchTheClock = ptype 
AND DATE_FORMAT(time_stamp,‘%Y-%m-%d‘) = DATE_FORMAT(now_time,‘%Y-%m-%d‘);
IF DATE_FORMAT(CURRENT_TIMESTAMP,‘%H‘) NOT BETWEEN 0 AND 24 THEN
	SET result = 0;
ELSEIF judge >= 1 THEN
	SET result = 0;
ELSEIF cnt = 0 and ptype = 1 THEN
	SET result = 0;
ELSE
	INSERT INTO clockio VALUES(ptype,now_time,snum,pnum);
	SET result = 1;
END IF;
END

3)数据初始化

department表：

job表：

project表：

staff表：

clockio表：

jobchanged：

4)测试存储过程/函数

测试工程2所需支付的所有工人酬金：

同理查询工程1：

根据目前已有的打卡记录可以验证存储过程函数运算正确：

测试员工职位变更：

1. 

2. 

3. 

4. 

   进行入职操作：

   

   

   

   员工离职操作：

   

   

   

   

测试员工打卡：

当前时间为凌晨 不在允许打卡的范围内 所以返回零

对其中判断部分进行修改后重新运行 就成功了 多了一条新的数据

在本日没有上班打卡的前提下进行下班打卡操作 也返回零 规避了打卡记录错乱的风险

职工-工程数据库课程设计