MySQL数据库基础练习系列40、报销审批系统

CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    name VARCHAR(100) NOT NULL COMMENT '用户名',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    department VARCHAR(100) NOT NULL COMMENT '部门'
);

CREATE TABLE ExpenseTypes (
    type_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '费用类型ID',
    type_name VARCHAR(100) NOT NULL COMMENT '费用类型名称'
);

CREATE TABLE ExpenseClaims (
    claim_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '报销申请ID',
    user_id INT NOT NULL COMMENT '用户ID',
    expense_date DATE NOT NULL COMMENT '费用日期',
    total_amount DECIMAL(10, 2) NOT NULL COMMENT '总金额',
    claim_description TEXT COMMENT '报销描述',
    status ENUM('待审批', '已审批', '已驳回') NOT NULL DEFAULT '待审批' COMMENT '报销状态',
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

CREATE TABLE ExpenseDetails (
    detail_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '费用明细ID',
    claim_id INT NOT NULL COMMENT '报销申请ID',
    type_id INT NOT NULL COMMENT '费用类型ID',
    amount DECIMAL(10, 2) NOT NULL COMMENT '金额',
    expense_description TEXT COMMENT '费用描述',
    FOREIGN KEY (claim_id) REFERENCES ExpenseClaims(claim_id),
    FOREIGN KEY (type_id) REFERENCES ExpenseTypes(type_id)
);

CREATE TABLE ApprovalProcesses (
    process_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '审批流程ID',
    claim_id INT NOT NULL COMMENT '报销申请ID',
    approver_id INT NOT NULL COMMENT '审批人ID',
    status ENUM('待审批', '已审批', '已驳回') NOT NULL DEFAULT '待审批' COMMENT '审批状态',
    FOREIGN KEY (claim_id) REFERENCES ExpenseClaims(claim_id),
    FOREIGN KEY (approver_id) REFERENCES Users(user_id)
);

CREATE TABLE ApprovalRecords (
    record_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '审批记录ID',
    process_id INT NOT NULL COMMENT '审批流程ID',
    approver_id INT NOT NULL COMMENT '审批人ID',
    approval_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '审批日期',
    approval_result ENUM('同意', '驳回') NOT NULL COMMENT '审批结果',
    remarks TEXT COMMENT '备注',
    FOREIGN KEY (process_id) REFERENCES ApprovalProcesses(process_id),
    FOREIGN KEY (approver_id) REFERENCES Users(user_id)
);

INSERT INTO Users (name, gender, department) VALUES
('孙悟空', '男', '天庭技术部'),
('白骨精', '女', '妖界人事部'),
('猪八戒', '男', '天庭财务部');
INSERT INTO ExpenseTypes (type_name) VALUES ('交通费');
INSERT INTO ExpenseTypes (type_name) VALUES ('餐饮费');
INSERT INTO ExpenseTypes (type_name) VALUES ('住宿费');
INSERT INTO ExpenseClaims (user_id, expense_date, total_amount, claim_description, status) VALUES
(1, '2023-04-01', 1500.00, '出差北京期间的费用', '待审批'),
(2, '2023-04-05', 1200.00, '参加会议相关费用', '待审批'),
(3, '2023-04-10', 1800.00, '前往上海培训费用', '待审批');
INSERT INTO ExpenseDetails (claim_id, type_id, amount, expense_description) VALUES
(1, 1, 800.00, '北京至上海的火车票'),
(1, 2, 500.00, '出差期间的餐饮费'),
(1, 3, 200.00, '住宿一晚的费用'),
(2, 1, 400.00, '参加会议的交通费'),
(2, 2, 800.00, '会议期间的餐饮费'),
(3, 2, 1000.00, '培训期间的餐饮费'),
(3, 3, 800.00, '培训期间的住宿费');

-- 插入ApprovalProcesses数据，为每个报销申请创建一个审批流程
INSERT INTO ApprovalProcesses (claim_id, approver_id) VALUES
(1, 1),
(2, 1),
(3, 1);


-- 插入ApprovalRecords数据，为每个审批流程添加审批记录

-- 假设第一个报销申请被同意，第二个被驳回，第三个待审批
INSERT INTO ApprovalRecords (process_id, approver_id, approval_result, remarks) VALUES
(1, 1, '同意', '报销申请已通过'),
(2, 1, '驳回', '部分费用不符合报销规定'),
(3, 1, '同意', NULL);