You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
92 lines
5.3 KiB
92 lines
5.3 KiB
-- ====================================
|
|
-- 数据初始化脚本
|
|
-- ====================================
|
|
|
|
-- 创建员工表
|
|
-- 表结构请根据实际需求定义
|
|
-- 示例员工表的插入数据
|
|
-- ====================================
|
|
-- 员工表 (Employee)
|
|
-- ====================================
|
|
INSERT INTO Employee (EmployeeID, Name, Gender, DateOfBirth, Position, DepartmentID, ContactInfo, HireDate, EmploymentStatus)
|
|
VALUES
|
|
('E001', '张伟', '男', '1990-01-15', '软件工程师', 'D001', '13812345678', '2020-03-01', '在职'),
|
|
('E002', '李娜', '女', '1985-05-20', '人事经理', 'D002', '13923456789', '2018-06-15', '在职'),
|
|
('E003', '王强', '男', '1992-09-10', '测试工程师', 'D001', '13734567890', '2021-02-20', '在职'),
|
|
('E004', '赵丽', '女', '1988-03-25', '财务主管', 'D003', '13645678901', '2017-08-10', '在职'),
|
|
('E005', '孙晓', '男', '1995-12-05', '产品经理', 'D001', '13556789012', '2022-04-05', '在职'),
|
|
('E006', '陈刚', '男', '1990-07-13', '前端开发', 'D001', '13467890123', '2019-09-15', '休假'),
|
|
('E007', '杨洋', '女', '1983-06-18', 'HR专员', 'D002', '13378901234', '2020-01-01', '离职'),
|
|
('E008', '刘晨', '男', '1994-11-11', 'Java开发', 'D001', '13289012345', '2021-11-20', '在职'),
|
|
('E009', '吴霞', '女', '1991-02-23', '市场经理', 'D004', '13190123456', '2018-07-10', '在职'),
|
|
('E010', '郑涛', '男', '1989-05-17', '技术总监', 'D001', '13001234567', '2015-03-23', '在职');
|
|
|
|
-- ====================================
|
|
-- 部门表 (Department)
|
|
-- ====================================
|
|
INSERT INTO Department (DepartmentID, ManagerID, DepartmentName)
|
|
VALUES
|
|
('D001', 'E010', '研发部'),
|
|
('D002', 'E002', '人事部'),
|
|
('D003', 'E004', '财务部'),
|
|
('D004', 'E009', '市场部');
|
|
|
|
-- ====================================
|
|
-- 考勤记录表 (Attendance)
|
|
-- ====================================
|
|
INSERT INTO Attendance (AttendanceID, EmployeeID, AttendanceDate, CheckInTime, CheckOutTime, WorkingHours, OvertimeHours, AttendanceStatus)
|
|
VALUES
|
|
('A001', 'E001', '2024-12-01', '2024-12-25 09:00:00', '2024-12-25 18:00:00', 8, 1, '正常'),
|
|
('A002', 'E002', '2024-12-01', '2024-12-25 09:15:00', '2024-12-25 18:00:00', 7.75, 0, '迟到'),
|
|
('A003', 'E003', '2024-12-01', '2024-12-25 09:00:00', '2024-12-25 18:00:00', 8, 0, '正常'),
|
|
('A004', 'E004', '2024-12-01', '2024-12-25 09:00:00', '2024-12-25 18:00:00', 8, 2, '正常'),
|
|
('A005', 'E005', '2024-12-01', '2024-12-25 09:10:00', '2024-12-25 18:00:00', 7.83, 0, '迟到'),
|
|
('A006', 'E006', '2024-12-01', '2024-12-25 09:00:00', '2024-12-25 18:00:00', 8, 0, '正常'),
|
|
('A007', 'E007', '2024-12-01', '2024-12-25 09:00:00', '2024-12-25 18:00:00', 8, 0, '正常'),
|
|
('A008', 'E008', '2024-12-01', '2024-12-25 09:05:00', '2024-12-25 18:00:00', 7.92, 0, '迟到'),
|
|
('A009', 'E009', '2024-12-01', '2024-12-25 09:00:00', '2024-12-25 18:00:00', 8, 0, '正常'),
|
|
('A010', 'E010', '2024-12-01', '2024-12-25 09:00:00', '2024-12-25 18:00:00', 8, 0, '正常');
|
|
|
|
-- ====================================
|
|
-- 假期申请表 (LeaveRequest)
|
|
-- ====================================
|
|
INSERT INTO LeaveRequest (RequestID, EmployeeID, LeaveType, StartDate, EndDate, LeaveDays, RequestStatus, ApproverID, Remarks)
|
|
VALUES
|
|
('L001', 'E001', '年假', '2024-12-10', '2024-12-12', 3, '待审批', 'E002', '年终假期'),
|
|
('L002', 'E002', '病假', '2024-12-03', '2024-12-04', 2, '已批准', 'E004', '因病请假'),
|
|
('L003', 'E003', '年假', '2024-12-05', '2024-12-06', 2, '已批准', 'E010', '年假'),
|
|
('L004', 'E004', '事假', '2024-12-02', '2024-12-02', 1, '已批准', 'E002', '处理家务事'),
|
|
('L005', 'E005', '年假', '2024-12-08', '2024-12-10', 3, '已批准', 'E010', '年终休假'),
|
|
('L006', 'E006', '病假', '2024-12-01', '2024-12-02', 2, '已拒绝', 'E003', '生病未能批准'),
|
|
('L007', 'E007', '事假', '2024-12-15', '2024-12-16', 2, '待审批', 'E004', '家里有事'),
|
|
('L008', 'E008', '年假', '2024-12-07', '2024-12-09', 3, '已批准', 'E010', '休假'),
|
|
('L009', 'E009', '病假', '2024-12-06', '2024-12-07', 2, '已批准', 'E003', '生病'),
|
|
('L010', 'E010', '年假', '2024-12-12', '2024-12-14', 3, '已批准', 'E002', '年假');
|
|
|
|
-- ====================================
|
|
-- 用户角色表 (UserRoles)
|
|
-- ====================================
|
|
INSERT INTO UserRoles (RoleID, RoleName, RoleDescription)
|
|
VALUES
|
|
('R001', '管理员', '系统管理员，拥有最高权限'),
|
|
('R002', '普通员工', '普通员工，拥有基本的操作权限'),
|
|
('R003', 'HR', '人事部员工，负责员工假期审批'),
|
|
('R004', '财务', '财务部员工，负责财务相关操作'),
|
|
('R005', '经理', '部门经理，负责审批部门内员工的假期');
|
|
|
|
-- ====================================
|
|
-- 考勤统计表 (AttendanceSummary)
|
|
-- ====================================
|
|
INSERT INTO AttendanceSummary (SummaryID, EmployeeID, Month, PresentDays, LateCount, EarlyLeaveCount, AbsentDays, TotalOvertimeHours)
|
|
VALUES
|
|
('S001', 'E001', '2024-12-01', 22, 1, 0, 0, 10),
|
|
('S002', 'E002', '2024-12-01', 20, 2, 1, 0, 5),
|
|
('S003', 'E003', '2024-12-01', 23, 0, 0, 0, 8),
|
|
('S004', 'E004', '2024-12-01', 21, 1, 0, 0, 12),
|
|
('S005', 'E005', '2024-12-01', 20, 1, 1, 1, 15),
|
|
('S006', 'E006', '2024-12-01', 19, 3, 2, 0, 20),
|
|
('S007', 'E007', '2024-12-01', 18, 2, 1, 0, 3),
|
|
('S008', 'E008', '2024-12-01', 21, 0, 0, 0, 6),
|
|
('S009', 'E009', '2024-12-01', 22, 1, 0, 0, 7),
|
|
('S010', 'E010', '2024-12-01', 23, 0, 0, 0, 10);
|