-- 2024-09-04
ALTER TABLE t_b_ding
    CHANGE COLUMN sop_no sop_no VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT 'sop单号' ,
    CHANGE COLUMN ding_sop_no ding_sop_no VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '实际打卡对应的sop单号' ;

INSERT INTO sys_config (id, config_key, config_name, config_value, enable, sort, create_id) VALUES ('41', 'region.coordinator.ding.time', '区域协调人打卡时间段', '{\"startTime\": 1725292800000,\"endTime\": 1725897600000}', '1', '1', '1');

-- 2024-09-05
ALTER TABLE t_b_ding_apply
    ADD COLUMN service_id BIGINT NULL COMMENT '服务单元id' AFTER id;
ALTER TABLE t_b_ding_apply
    CHANGE COLUMN sop_no sop_no VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT 'sop编号' ,
    CHANGE COLUMN crm_no crm_no VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '派单编号' ;

UPDATE t_b_ding_apply tbda
SET
    tbda.service_id = (SELECT
            tbc.service_id
        FROM
            t_b_crm tbc
        WHERE
            tbda.crm_no = tbc.crm_no)
WHERE
    service_id IS NULL;

ALTER TABLE t_b_crm_detail
    ADD COLUMN scan_actual_start_time BIGINT NULL COMMENT '扫描开始实际时间\n研究生:现场环境检测提交\n教务处:扫描准备提交' AFTER publish_time,
    ADD COLUMN scan_actual_end_time BIGINT NULL COMMENT '扫描结束实际时间\n研究生:收尾校验提交\n教务处:扫描收尾检查提交' AFTER scan_actual_start_time,
    ADD COLUMN mark_paper_actual_start_time BIGINT NULL COMMENT '阅卷开始实际时间\n研究生:评卷准备提交\n教务处:阅卷参数提交' AFTER scan_actual_end_time,
    ADD COLUMN mark_paper_actual_end_time BIGINT NULL COMMENT '阅卷结束实际时间\n研究生:设备入库提交\n教务处:评卷收尾检查提交' AFTER mark_paper_actual_start_time;

ALTER TABLE t_b_ding_submit
    CHANGE COLUMN sop_no sop_no VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT 'sop单号' ;

ALTER TABLE t_b_ding_statistic
    CHANGE COLUMN sop_no sop_no VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT 'sop单号' ,
    CHANGE COLUMN crm_no crm_no VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT 'crm单号' ;

-- 2024-09-06
ALTER TABLE t_b_ding_statistic 
ADD COLUMN custom_id BIGINT NULL COMMENT '客户id' AFTER crm_no;

UPDATE t_b_ding_statistic tbds
SET
    tbds.custom_id = (SELECT
            tbc.custom_id
        FROM
            t_b_crm tbc
        WHERE
            tbds.crm_no = tbc.crm_no)
WHERE
    custom_id IS NULL;
    
ALTER TABLE t_b_ding_statistic
ADD COLUMN service_name VARCHAR(100) NULL COMMENT '服务单元名称' AFTER service_id;

UPDATE t_b_ding_statistic tbds
SET
    tbds.service_name = (SELECT
            tbs.name
        FROM
            t_b_service tbs
        WHERE
            tbds.service_id = tbs.id)
WHERE
    service_name IS NULL;

-- 2024-09-09
CREATE TABLE t_b_ding_statistic_by_person (
  id BIGINT NOT NULL,
  service_id BIGINT NOT NULL COMMENT '服务单元id',
  service_name VARCHAR(100) NULL COMMENT '服务单元名称',
  user_info VARCHAR(100) NULL COMMENT '姓名(人员档案号)',
  supplier_name VARCHAR(100) NULL COMMENT '供应商',
  actual_days INT NULL COMMENT '实际出勤（天）',
  weekdays INT NULL COMMENT '工作日（天）',
  weekends INT NULL COMMENT '周末（天）',
  legal_holidays INT NULL COMMENT '法定节假日（天）',
  work_hours DECIMAL(8,2) NULL COMMENT '累计工时（小时）',
  violation_days INT NULL COMMENT '违规工时（天）',
  create_id BIGINT NULL COMMENT '创建人id',
  create_time BIGINT NULL COMMENT '创建时间',
  update_id BIGINT NULL COMMENT '更新人id',
  update_time BIGINT NULL COMMENT '更新时间',
  PRIMARY KEY (id))
COMMENT = '工时统计-按人员统计';

CREATE TABLE t_b_ding_statistic_by_sop (
  id BIGINT NOT NULL,
  service_id BIGINT NOT NULL COMMENT '服务单元id',
  service_name VARCHAR(100) NULL COMMENT '服务单元名称',
  sop_no VARCHAR(100) NULL COMMENT 'sop流水号',
  course_name VARCHAR(100) NULL COMMENT '科目名称',
  actual_days INT NULL COMMENT '实际出勤（天）',
  weekdays INT NULL COMMENT '工作日（天）',
  weekends INT NULL COMMENT '周末（天）',
  legal_holidays INT NULL COMMENT '法定节假日（天）',
  scan_period INT NULL COMMENT '扫描周期（天）',
  mark_paper_period INT NULL COMMENT '阅卷周期（天）',
  work_hours DECIMAL(8,2) NULL COMMENT '累计工时（小时）',
  violation_days INT NULL COMMENT '违规工时（天）',
  create_id BIGINT NULL COMMENT '创建人id',
  create_time BIGINT NULL COMMENT '创建时间',
  update_id BIGINT NULL COMMENT '更新人id',
  update_time BIGINT NULL COMMENT '更新时间',
  PRIMARY KEY (id))
COMMENT = '工时统计-按sop统计';

CREATE TABLE t_b_ding_statistic_by_crm (
  id BIGINT NOT NULL,
  service_id BIGINT NOT NULL COMMENT '服务单元id',
  service_name VARCHAR(100) NULL COMMENT '服务单元名称',
  crm_no VARCHAR(100) NULL COMMENT '派单号',
  crm_name VARCHAR(100) NULL COMMENT '派单名称',
  actual_days INT NULL COMMENT '实际出勤（天）',
  weekdays INT NULL COMMENT '工作日（天）',
  weekends INT NULL COMMENT '周末（天）',
  legal_holidays INT NULL COMMENT '法定节假日（天）',
  scan_period INT NULL COMMENT '扫描周期（天）',
  mark_paper_period INT NULL COMMENT '阅卷周期（天）',
  work_hours DECIMAL(8,2) NULL COMMENT '累计工时（小时）',
  violation_days INT NULL COMMENT '违规工时（天）',
  create_id BIGINT NULL COMMENT '创建人id',
  create_time BIGINT NULL COMMENT '创建时间',
  update_id BIGINT NULL COMMENT '更新人id',
  update_time BIGINT NULL COMMENT '更新时间',
  PRIMARY KEY (id))
COMMENT = '工时统计-按派单统计';

CREATE TABLE t_b_ding_statistic_by_custom (
  id BIGINT NOT NULL,
  service_id BIGINT NOT NULL COMMENT '服务单元id',
  service_name VARCHAR(100) NULL COMMENT '服务单元名称',
  custom_id BIGINT NULL COMMENT '客户id',
  custom_name VARCHAR(100) NULL COMMENT '客户名称',
  actual_days INT NULL COMMENT '实际出勤（天）',
  weekdays INT NULL COMMENT '工作日（天）',
  weekends INT NULL COMMENT '周末（天）',
  legal_holidays INT NULL COMMENT '法定节假日（天）',
  scan_period INT NULL COMMENT '扫描周期（天）',
  mark_paper_period INT NULL COMMENT '阅卷周期（天）',
  work_hours DECIMAL(8,2) NULL COMMENT '累计工时（小时）',
  violation_days INT NULL COMMENT '违规工时（天）',
  create_id BIGINT NULL COMMENT '创建人id',
  create_time BIGINT NULL COMMENT '创建时间',
  update_id BIGINT NULL COMMENT '更新人id',
  update_time BIGINT NULL COMMENT '更新时间',
  PRIMARY KEY (id))
COMMENT = '工时统计-按客户统计';

CREATE TABLE t_b_ding_statistic_by_supplier (
  id BIGINT NOT NULL,
  service_id BIGINT NOT NULL COMMENT '服务单元id',
  service_name VARCHAR(100) NULL COMMENT '服务单元名称',
  supplier_id BIGINT NULL COMMENT '供应商id',
  supplier_name VARCHAR(100) NULL COMMENT '供应商名称',
  actual_days INT NULL COMMENT '实际出勤（天）',
  weekdays INT NULL COMMENT '工作日（天）',
  weekends INT NULL COMMENT '周末（天）',
  legal_holidays INT NULL COMMENT '法定节假日（天）',
  work_hours DECIMAL(8,2) NULL COMMENT '累计工时（小时）',
  violation_days INT NULL COMMENT '违规工时（天）',
  create_id BIGINT NULL COMMENT '创建人id',
  create_time BIGINT NULL COMMENT '创建时间',
  update_id BIGINT NULL COMMENT '更新人id',
  update_time BIGINT NULL COMMENT '更新时间',
  PRIMARY KEY (id))
COMMENT = '工时统计-按供应商统计';


INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('2100', '工时统计-分页查询', '/api/admin/tb/ding/statistic/page', 'URL', '29', '1', 'AUTH', '1', '1', '0');
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('2101', '工时统计-批量导出', '/api/admin/tb/ding/statistic/export', 'URL', '29', '2', 'AUTH', '1', '1', '0');
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, related, enable, default_auth, front_display) VALUES ('2102', '搜索', 'Select', 'BUTTON', '29', '1', 'AUTH', '2100', '1', '0', '1');
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, related, enable, default_auth, front_display) VALUES ('2103', '导出统计结果', 'Export', 'BUTTON', '29', '2', 'AUTH', '2101', '1', '0', '1');

-- 2024-09-10
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, related, enable, default_auth, front_display) VALUES ('2104', '补卡申请-区协查询审核人', '/api/admin/ding/exception/apply/find_region_coordinator_auditor', 'URL', '26', '2', 'SYS', NULL, '1', '1', '0');
