145 lines
7.0 KiB
SQL
145 lines
7.0 KiB
SQL
-- 嘉恒智能报工小程序 MySQL schema
|
||
-- 建议 MySQL 8.0+，字符集统一 utf8mb4。
|
||
|
||
CREATE TABLE IF NOT EXISTS personnel (
|
||
phone VARCHAR(20) NOT NULL COMMENT '电话号',
|
||
name VARCHAR(64) NOT NULL COMMENT '姓名',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (phone)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='人员表';
|
||
|
||
CREATE TABLE IF NOT EXISTS person_roles (
|
||
phone VARCHAR(20) NOT NULL COMMENT '电话号',
|
||
role ENUM('worker', 'admin', 'manager') NOT NULL COMMENT '角色',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (phone, role),
|
||
CONSTRAINT fk_person_roles_person FOREIGN KEY (phone) REFERENCES personnel(phone)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='人员角色表';
|
||
|
||
CREATE TABLE IF NOT EXISTS products (
|
||
project_no VARCHAR(64) NOT NULL COMMENT '项目号',
|
||
product_name VARCHAR(255) NOT NULL COMMENT '产品名称',
|
||
profile_no VARCHAR(128) NULL COMMENT '型材号，来自现有产品清单',
|
||
material_code VARCHAR(128) NULL COMMENT '物料编码',
|
||
material_name VARCHAR(255) NULL COMMENT '物料名称',
|
||
supplier VARCHAR(255) NULL COMMENT '供应商',
|
||
device_no VARCHAR(64) NOT NULL COMMENT '使用设备号',
|
||
process_name VARCHAR(128) NOT NULL COMMENT '工序',
|
||
stamping_method VARCHAR(128) NULL COMMENT '冲压方式',
|
||
operator_count DECIMAL(10,2) NOT NULL DEFAULT 1 COMMENT '操作人数',
|
||
standard_beat DECIMAL(12,3) NOT NULL COMMENT '标准节拍，秒/件',
|
||
standard_workload DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '标准工作量，建议按8小时折算',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (project_no, product_name, device_no, process_name),
|
||
KEY idx_products_device_no (device_no),
|
||
KEY idx_products_material_code (material_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品表';
|
||
|
||
CREATE TABLE IF NOT EXISTS work_sessions (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
employee_phone VARCHAR(20) NOT NULL,
|
||
start_at DATETIME NOT NULL,
|
||
end_at DATETIME NULL,
|
||
status ENUM('active', 'reporting', 'submitted', 'cancelled') NOT NULL DEFAULT 'active',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
KEY idx_sessions_employee_status (employee_phone, status),
|
||
CONSTRAINT fk_sessions_employee FOREIGN KEY (employee_phone) REFERENCES personnel(phone)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='扫码上班流程';
|
||
|
||
CREATE TABLE IF NOT EXISTS work_session_devices (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
session_id BIGINT UNSIGNED NOT NULL,
|
||
device_no VARCHAR(64) NOT NULL,
|
||
scanned_at DATETIME NOT NULL,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_session_device (session_id, device_no),
|
||
KEY idx_session_devices_device (device_no),
|
||
CONSTRAINT fk_session_devices_session FOREIGN KEY (session_id) REFERENCES work_sessions(id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='一次报工流程中的设备序列';
|
||
|
||
CREATE TABLE IF NOT EXISTS production_reports (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
session_id BIGINT UNSIGNED NOT NULL,
|
||
employee_phone VARCHAR(20) NOT NULL,
|
||
report_date DATE NOT NULL COMMENT '以员工确认提交日期为准',
|
||
start_at DATETIME NOT NULL,
|
||
end_at DATETIME NOT NULL,
|
||
duration_minutes DECIMAL(10,2) NOT NULL DEFAULT 0,
|
||
break_minutes DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '休息间隔，仅经理可改',
|
||
effective_minutes DECIMAL(10,2) NOT NULL DEFAULT 0,
|
||
total_good_qty DECIMAL(12,2) NOT NULL DEFAULT 0,
|
||
total_output_qty DECIMAL(12,2) NOT NULL DEFAULT 0,
|
||
actual_beat DECIMAL(12,3) NOT NULL DEFAULT 0,
|
||
standard_beat DECIMAL(12,3) NOT NULL DEFAULT 0,
|
||
expected_workload DECIMAL(12,2) NOT NULL DEFAULT 0,
|
||
pace_rate DECIMAL(10,2) NOT NULL DEFAULT 0,
|
||
workload_rate DECIMAL(10,2) NOT NULL DEFAULT 0,
|
||
status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
|
||
reviewer_phone VARCHAR(20) NULL,
|
||
reviewed_at DATETIME NULL,
|
||
reject_reason VARCHAR(500) NULL,
|
||
submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_report_session (session_id),
|
||
KEY idx_reports_employee_date (employee_phone, report_date),
|
||
KEY idx_reports_status_date (status, report_date),
|
||
KEY idx_reports_reviewer (reviewer_phone, reviewed_at),
|
||
CONSTRAINT fk_reports_session FOREIGN KEY (session_id) REFERENCES work_sessions(id),
|
||
CONSTRAINT fk_reports_employee FOREIGN KEY (employee_phone) REFERENCES personnel(phone),
|
||
CONSTRAINT fk_reports_reviewer FOREIGN KEY (reviewer_phone) REFERENCES personnel(phone)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报工主表';
|
||
|
||
CREATE TABLE IF NOT EXISTS production_report_items (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
report_id BIGINT UNSIGNED NOT NULL,
|
||
device_no VARCHAR(64) NOT NULL,
|
||
project_no VARCHAR(64) NOT NULL,
|
||
product_name VARCHAR(255) NOT NULL,
|
||
material_code VARCHAR(128) NULL,
|
||
material_name VARCHAR(255) NULL,
|
||
process_name VARCHAR(128) NULL,
|
||
standard_beat DECIMAL(12,3) NOT NULL DEFAULT 0,
|
||
standard_workload DECIMAL(12,2) NOT NULL DEFAULT 0,
|
||
good_qty DECIMAL(12,2) NOT NULL DEFAULT 0,
|
||
defect_qty DECIMAL(12,2) NOT NULL DEFAULT 0,
|
||
scrap_qty DECIMAL(12,2) NOT NULL DEFAULT 0,
|
||
allocated_minutes DECIMAL(10,2) NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
KEY idx_report_items_report (report_id),
|
||
KEY idx_report_items_product (project_no, product_name),
|
||
CONSTRAINT fk_report_items_report FOREIGN KEY (report_id) REFERENCES production_reports(id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报工明细';
|
||
|
||
CREATE TABLE IF NOT EXISTS report_audit_logs (
|
||
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
report_id BIGINT UNSIGNED NOT NULL,
|
||
reviewer_phone VARCHAR(20) NOT NULL,
|
||
action ENUM('approve', 'approve_with_correction', 'reject', 'manager_break_update') NOT NULL,
|
||
before_json JSON NULL,
|
||
after_json JSON NULL,
|
||
remark VARCHAR(500) NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
KEY idx_audit_report (report_id),
|
||
KEY idx_audit_reviewer (reviewer_phone, created_at),
|
||
CONSTRAINT fk_audit_report FOREIGN KEY (report_id) REFERENCES production_reports(id),
|
||
CONSTRAINT fk_audit_reviewer FOREIGN KEY (reviewer_phone) REFERENCES personnel(phone)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='审核记录';
|
||
|
||
CREATE TABLE IF NOT EXISTS device_qrcodes (
|
||
device_no VARCHAR(64) NOT NULL,
|
||
qr_scene VARCHAR(128) NOT NULL,
|
||
qr_url VARCHAR(500) NULL,
|
||
created_by VARCHAR(20) NOT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (device_no),
|
||
CONSTRAINT fk_qrcode_creator FOREIGN KEY (created_by) REFERENCES personnel(phone)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备二维码';
|