-- 2024-05-13
ALTER TABLE t_b_crm
    ADD COLUMN lead_type VARCHAR(45) NULL COMMENT '大区经理指定类型（BY_PERSON(\"按人员指定\"), BY_AREA(\"按地区指定\");）' AFTER lead_id;

-- 2024-05-14
ALTER TABLE t_b_user_archives_allocation
    DROP COLUMN role_id,
    DROP COLUMN region_user_id,
    DROP COLUMN sop_no,
    ADD COLUMN crm_detail_id BIGINT NOT NULL COMMENT '派单详情表id' AFTER id,
    ADD COLUMN sop_role_type VARCHAR(45) NOT NULL COMMENT 'sop角色类型\nREGION_COORDINATOR(\"区域协调人\"),PROJECT_MANAGER(\"项目经理\"),ENGINEER(\"工程师\");' AFTER crm_no,
    CHANGE COLUMN service_id service_id BIGINT NOT NULL COMMENT '服务单元id' ,
    CHANGE COLUMN user_id user_id BIGINT NOT NULL COMMENT '用户id' ;
ALTER TABLE t_b_user_archives_allocation
    DROP INDEX t_b_user_archives_allocation_un ,
    ADD UNIQUE INDEX t_b_user_archives_allocation_un (crm_detail_id ASC, sop_role_type ASC, archives_id ASC) VISIBLE;
;

CREATE TABLE t_b_user_archives_allocation_log (
    id bigint NOT NULL COMMENT '主键',
    crm_detail_id bigint NOT NULL COMMENT '派单详情表id',
    sop_role_type varchar(45) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'sop角色类型\nREGION_COORDINATOR("区域协调人"),PROJECT_MANAGER("项目经理"),ENGINEER("工程师");',
    user_id bigint NOT NULL COMMENT '用户id',
    archives_id bigint NOT NULL COMMENT '人员档案id',
    archives_name varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '档案名称',
    supplier_name varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '供应商名称',
    city varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城市',
    create_id bigint DEFAULT NULL COMMENT '创建人',
    create_time bigint DEFAULT NULL COMMENT '创建时间',
    update_id bigint DEFAULT NULL COMMENT '更新人',
    update_time bigint DEFAULT NULL COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='人员调配历史记录';

ALTER TABLE t_b_ding
    ADD COLUMN sop_role_type VARCHAR(45) NOT NULL COMMENT 'sop角色' AFTER role_type,
    CHANGE COLUMN role_type role_type VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '项目角色' ;

-- 2024-05-15
ALTER TABLE sys_ding_obj
    ADD COLUMN sop_role_type VARCHAR(45) NOT NULL COMMENT 'sop角色' AFTER type;

-- 旧数据临时处理
update t_b_ding set sop_role_type = 'ENGINEER' where sop_role_type = '';
update t_b_user_archives_allocation set sop_role_type = 'ENGINEER' where sop_role_type = '';
update sys_ding_obj set sop_role_type = 'ENGINEER' where sop_role_type = '';

INSERT INTO sequence (name, current_value, increment) VALUES ('sysCustomCode', '1', '1');

-- 2024-05-16
INSERT INTO sys_config (id, config_key, config_name, config_value, enable, sort, create_id) VALUES ('39', 'sms.custom.info.missing.remind.code', '客户信息缺失通知', 'SMS_467015006', '1', '1', '1');

-- 2024-05-17
UPDATE sys_privilege SET name = '服务范围管理-划定派单和服务单元关系', url = '/api/service/service/scope/bind' WHERE (id = '2009');

-- 2024-05-20
ALTER TABLE t_b_user_archives_allocation
    ADD COLUMN remark VARCHAR(45) NULL COMMENT '备注' AFTER archives_id;

ALTER TABLE t_b_user_archives_allocation
    ADD COLUMN create_id BIGINT NULL COMMENT '创建人' AFTER remark,
    ADD COLUMN create_time BIGINT NULL COMMENT '创建时间' AFTER create_id,
    ADD COLUMN update_id BIGINT NULL COMMENT '更新人' AFTER create_time,
    ADD COLUMN update_time BIGINT NULL COMMENT '更新时间' AFTER update_id;

-- 2024-05-21
UPDATE sys_privilege SET parent_id = '51' WHERE (id = '1032');
UPDATE sys_privilege SET parent_id = '51' WHERE (id = '1034');
UPDATE sys_privilege SET parent_id = '51' WHERE (id = '1035');
UPDATE sys_privilege SET parent_id = '51' WHERE (id = '1036');
UPDATE sys_privilege SET parent_id = '51' WHERE (id = '1037');
UPDATE sys_privilege SET parent_id = '51' WHERE (id = '1038');
UPDATE sys_privilege SET related = NULL WHERE (id = '51');
UPDATE sys_privilege SET name = '批量禁用' WHERE (id = '1032');
UPDATE sys_privilege SET name = '统计待划定数量' WHERE (id = '1034');
UPDATE sys_privilege SET name = '派单信息表删除' WHERE (id = '1035');
UPDATE sys_privilege SET name = '获取单个派单' WHERE (id = '1036');
UPDATE sys_privilege SET name = '分页查询' WHERE (id = '1037');
UPDATE sys_privilege SET name = '新增修改' WHERE (id = '1038');
UPDATE sys_privilege SET related = '1034,1037' WHERE (id = '209');
UPDATE sys_privilege SET related = '1034,1037' WHERE (id = '210');
UPDATE sys_privilege SET related = '1036,1038' WHERE (id = '211');
UPDATE sys_privilege SET related = '1032' WHERE (id = '212');
UPDATE sys_privilege SET related = '2009' WHERE (id = '213');
UPDATE sys_privilege SET related = '1034,1037' WHERE (id = '214');
UPDATE sys_privilege SET related = '2009' WHERE (id = '215');
UPDATE sys_privilege SET related = '2009' WHERE (id = '217');
UPDATE sys_privilege SET related = '1036,1038' WHERE (id = '216');

INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('2060', '查询同服务单元客户绑定数据', '/api/admin/tb/crm/crm_bind_info', 'URL', '51', '8', 'AUTH', '1', '1', '0');
UPDATE sys_privilege SET related = '2009,2060' WHERE (id = '215');
UPDATE sys_privilege SET related = '2009,2060' WHERE (id = '217');

UPDATE sys_privilege SET parent_id = '3079', sequence = '8' WHERE (id = '2024');
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('2061', '派单详情调配信息查询', '/api/admin/user/archives/allocation/info', 'URL', '3079', '9', 'AUTH', '1', '1', '0');
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('2062', '人员调配可选人员查询', '/api/admin/user/archives/allocation/can_choose', 'URL', '3079', '10', 'AUTH', '1', '1', '0');

ALTER TABLE t_b_ding
    ADD COLUMN ding_exception TINYINT NOT NULL DEFAULT 0 COMMENT '打卡异常,0:正常,1:异常' AFTER face_out_pass;

-- 2024-05-22
DROP TABLE IF EXISTS `t_b_ding_statistic`;
CREATE TABLE `t_b_ding_statistic` (
    `id` bigint NOT NULL COMMENT '主键',
    `service_id` bigint NOT NULL COMMENT '服务单元id',
    `sop_no` varchar(100) CHARACTER SET utf8mb4 NOT NULL COMMENT 'sop单号',
    `crm_no` varchar(100) CHARACTER SET utf8mb4 NOT NULL COMMENT 'crm单号',
    `user_archives_id` bigint NOT NULL COMMENT '人员档案id',
    `scan_start_time` bigint DEFAULT NULL COMMENT '扫描开始时间',
    `scan_end_time` bigint DEFAULT NULL COMMENT '扫描结束时间',
    `mark_paper_start_time` bigint DEFAULT NULL COMMENT '阅卷开始时间',
    `mark_paper_end_time` bigint DEFAULT NULL COMMENT '阅卷开始时间',
    `role_type` varchar(20) CHARACTER SET utf8mb4 NOT NULL COMMENT '项目角色类型',
    `actual_days` int DEFAULT NULL COMMENT '实际出勤（天）',
    `weekdays` int DEFAULT NULL COMMENT '工作日（天）',
    `weekends` int DEFAULT NULL COMMENT '周末（天）',
    `legal_holidays` int DEFAULT NULL COMMENT '法定节假日（天）',
    `work_hours` decimal(8,2) DEFAULT NULL COMMENT '累计工时（小时）',
    `violation_days` decimal(8,2) DEFAULT NULL COMMENT '违规工时',
    `submit_user_id` bigint NOT NULL COMMENT '提交人id',
    `submit_time` bigint NOT NULL COMMENT '提交时间',
    `submit_status` varchar(20) CHARACTER SET utf8mb4 NOT NULL COMMENT '提交状态',
    `create_id` bigint DEFAULT NULL COMMENT '创建人id',
    `create_time` bigint DEFAULT NULL COMMENT '创建时间',
    `update_id` bigint DEFAULT NULL COMMENT '更新人id',
    `update_time` bigint DEFAULT NULL COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `t_b_ding_statistic_un` (`sop_no`,`user_archives_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考勤提交统计表';