USE sop_db;

-- 2024-11-04
UPDATE t_d_form_widget
SET code='TABLE', `type`='FORM', form_id='project_contacts_table', form_name='project_contacts_table', title='项目联系人表格', input_type='STRING', required=0, readable=0, writable=1, visable=1, `scale`=NULL, `length`=NULL, binding=NULL, data_grid=NULL, tips=NULL, format=NULL, span=12, sub_title=NULL, `options`=NULL, flow_type='CLOUD_MARK_SOP_FLOW', form_setup=1, handle=0, form_group=NULL, role_type=NULL, create_id=1, create_time=24
WHERE id=112;
UPDATE t_d_form_widget
SET code='TEXT', `type`='TABLE', form_id='school_college', form_name='school_college', title='项目联系人表格_机构输入框', input_type='STRING', required=0, readable=0, writable=1, visable=1, `scale`=NULL, `length`=100, binding=NULL, data_grid=NULL, tips=NULL, format=NULL, span=3, sub_title=NULL, `options`=NULL, flow_type='CLOUD_MARK_SOP_FLOW', form_setup=1, handle=0, form_group=NULL, role_type=NULL, create_id=1, create_time=25
WHERE id=113;
UPDATE t_d_form_widget
SET code='TEXT', `type`='TABLE', form_id='school_name', form_name='school_name', title='项目联系人表格_姓名输入框', input_type='STRING', required=0, readable=0, writable=1, visable=1, `scale`=NULL, `length`=100, binding=NULL, data_grid=NULL, tips=NULL, format=NULL, span=3, sub_title=NULL, `options`=NULL, flow_type='CLOUD_MARK_SOP_FLOW', form_setup=1, handle=0, form_group=NULL, role_type=NULL, create_id=1, create_time=26
WHERE id=114;
UPDATE t_d_form_widget
SET code='TEXT', `type`='TABLE', form_id='school_job', form_name='school_job', title='项目联系人表格_职务输入框', input_type='STRING', required=0, readable=0, writable=1, visable=1, `scale`=NULL, `length`=100, binding=NULL, data_grid=NULL, tips=NULL, format=NULL, span=3, sub_title=NULL, `options`=NULL, flow_type='CLOUD_MARK_SOP_FLOW', form_setup=1, handle=0, form_group=NULL, role_type=NULL, create_id=1, create_time=27
WHERE id=115;
UPDATE t_d_form_widget
SET code='TEXT', `type`='TABLE', form_id='school_tel', form_name='school_tel', title='项目联系人表格_电话输入框', input_type='STRING', required=0, readable=0, writable=1, visable=1, `scale`=NULL, `length`=100, binding=NULL, data_grid=NULL, tips=NULL, format=NULL, span=3, sub_title=NULL, `options`=NULL, flow_type='CLOUD_MARK_SOP_FLOW', form_setup=1, handle=0, form_group=NULL, role_type=NULL, create_id=1, create_time=28
WHERE id=116;

UPDATE t_d_form_widget
SET code='FILE', `type`='FORM', form_id='upload_device_version_photos', form_name='upload_device_version_photos', title='上传每台扫描仪序列号拍照', input_type='ARRAY', required=1, readable=0, writable=1, visable=1, `scale`=NULL, `length`=NULL, binding=NULL, data_grid=NULL, tips=NULL, format=NULL, span=12, sub_title=NULL, `options`=NULL, flow_type='CLOUD_MARK_SOP_FLOW', form_setup=4, handle=0, form_group=NULL, role_type=NULL, create_id=1, create_time=1
WHERE id=188;

UPDATE t_d_form_widget
SET code='CHECKBOX', `type`='FORM', form_id='missing_list_cb', form_name='missing_list_cb', title='缺考名单比对', input_type='STRING', required=1, readable=0, writable=1, visable=1, `scale`=NULL, `length`=NULL, binding=NULL, data_grid=NULL, tips=NULL, format=NULL, span=12, sub_title=NULL, `options`='[{"value":"1","label":"核对“无答题卡考生”的考生是否与学校提供的缺考名单吻合"},{"value":"2","label":"人工指定为缺考（缺考一致性>导入缺考>导入缺考缺页）"},{"value":"3","label":"需核对平台“考生管理”中状态为“未上传”的考生是否全部为缺考、是否全部变为“人工指定”"}]', flow_type='CLOUD_MARK_SOP_FLOW', form_setup=6, handle=0, form_group=NULL, role_type=NULL, create_id=1, create_time=1
WHERE id=162;

UPDATE t_d_form_widget
SET code='NUMBER', `type`='FORM', form_id='scan_page_count', form_name='scan_page_count', title='扫描张数', input_type='INT', required=1, readable=0, writable=1, visable=1, `scale`=NULL, `length`=NULL, binding=NULL, data_grid=NULL, tips=NULL, format=NULL, span=3, sub_title=NULL, `options`=NULL, flow_type='CLOUD_MARK_SOP_FLOW', form_setup=9, handle=0, form_group=NULL, role_type=NULL, create_id=1, create_time=8
WHERE id=195;
UPDATE t_d_form_widget
SET code='LABEL', `type`='FORM', form_id='scan_page_count_label', form_name='scan_page_count_label', title='[{"label":"操作说明","color":"black"},{"label":"1、考试管理>选择对应的考试点击“详情”","color":"black"},{"label":"2、查看“扫描张数” 或者点击验收报告查看扫描总张数","color":"black"}]', input_type='STRING', required=0, readable=1, writable=0, visable=1, `scale`=NULL, `length`=NULL, binding=NULL, data_grid=NULL, tips=NULL, format=NULL, span=6, sub_title=NULL, `options`=NULL, flow_type='CLOUD_MARK_SOP_FLOW', form_setup=9, handle=0, form_group=NULL, role_type=NULL, create_id=1, create_time=8
WHERE id=196;
UPDATE t_d_form_widget
SET code='FILE', `type`='FORM', form_id='upload_check_report_photos', form_name='upload_check_report_photos', title='上传验收报告(纸质)拍照', input_type='ARRAY', required=1, readable=0, writable=1, visable=1, `scale`=NULL, `length`=5, binding=NULL, data_grid=NULL, tips=NULL, format=NULL, span=12, sub_title=NULL, `options`=NULL, flow_type='CLOUD_MARK_SOP_FLOW', form_setup=9, handle=0, form_group=NULL, role_type=NULL, create_id=1, create_time=9
WHERE id=184;

-- 2024-11-05
INSERT INTO t_d_form_widget
(id, code, `type`, form_id, form_name, title, input_type, required, readable, writable, visable, `scale`, `length`, binding, data_grid, tips, format, span, sub_title, `options`, flow_type, form_setup, handle, form_group, role_type, create_id, create_time)
VALUES(201, 'FILE', 'FORM', 'example_photos', 'example_photos', '示例', 'ARRAY', 0, 1, 0, 1, NULL, 2, NULL, 'https://sop-file.qmth.com.cn/logo/logo.png,https://sop-file.qmth.com.cn/logo/operation.png', NULL, NULL, 12, NULL, NULL, 'CLOUD_MARK_SOP_FLOW', 4, 0, NULL, NULL, 1, 2);


-- 2024-11-04
ALTER TABLE t_b_ding
    ADD COLUMN sign_in_custom_id BIGINT NULL COMMENT '签到客户id' AFTER sign_in_address,
    ADD COLUMN sign_out_custom_id BIGINT NULL COMMENT '签退客户id' AFTER sign_out_address;

CREATE TABLE `t_b_ding_add` (
                                `id` BIGINT NOT NULL,
                                `service_id` BIGINT NOT NULL COMMENT '服务单元id',
                                `sop_no` VARCHAR(100) DEFAULT NULL COMMENT 'sop号',
                                `user_id` BIGINT NOT NULL COMMENT '用户id',
                                `add_days` DECIMAL(8 , 2 ) DEFAULT NULL COMMENT '追加人天（天）',
                                `add_hours` DECIMAL(8 , 2 ) DEFAULT 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`) USING BTREE
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COMMENT='工时追加表';

ALTER TABLE t_b_ding_apply
    ADD COLUMN apply_custom_id BIGINT NULL COMMENT '区协补卡选择的客户id' AFTER reason;

-- 2024-11-05
ALTER TABLE t_b_ding_add
    ADD UNIQUE INDEX ding_add_unique (service_id ASC, user_id ASC);

ALTER TABLE t_b_ding_statistic_by_crm
    ADD COLUMN add_days DECIMAL(8,2) NULL COMMENT '增加人天（天）' AFTER mark_paper_period,
    ADD COLUMN add_hours DECIMAL(8,2) NULL COMMENT '增加小时（小时）' AFTER add_days,
    ADD COLUMN work_days DECIMAL(8,2) NULL COMMENT '累计人天（天）' AFTER add_hours,
    CHANGE COLUMN work_hours work_hours DECIMAL(8,2) NULL COMMENT '累计工时（小时）' ;

ALTER TABLE t_b_ding_statistic_by_custom
    ADD COLUMN add_days DECIMAL(8,2) NULL COMMENT '增加人天（天）' AFTER mark_paper_period,
    ADD COLUMN add_hours DECIMAL(8,2) NULL COMMENT '增加小时（小时）' AFTER add_days,
    ADD COLUMN work_days DECIMAL(8,2) NULL COMMENT '累计人天（天）' AFTER add_hours,
    CHANGE COLUMN work_hours work_hours DECIMAL(8,2) NULL COMMENT '累计工时（小时）' ;

ALTER TABLE t_b_ding_statistic_by_person
    ADD COLUMN add_days DECIMAL(8,2) NULL COMMENT '增加人天（天）' AFTER legal_holidays,
    ADD COLUMN add_hours DECIMAL(8,2) NULL COMMENT '增加小时（小时）' AFTER add_days,
    ADD COLUMN work_days DECIMAL(8,2) NULL COMMENT '累计人天（天）' AFTER add_hours,
    CHANGE COLUMN work_hours work_hours DECIMAL(8,2) NULL COMMENT '累计工时（小时）' ;

ALTER TABLE t_b_ding_statistic_by_sop
    ADD COLUMN add_days DECIMAL(8,2) NULL COMMENT '增加人天（天）' AFTER legal_holidays,
    ADD COLUMN add_hours DECIMAL(8,2) NULL COMMENT '增加小时（小时）' AFTER add_days,
    ADD COLUMN work_days DECIMAL(8,2) NULL COMMENT '累计人天（天）' AFTER add_hours,
    CHANGE COLUMN work_hours work_hours DECIMAL(8,2) NULL COMMENT '累计工时（小时）' ;

ALTER TABLE t_b_ding_statistic_by_supplier
    ADD COLUMN add_days DECIMAL(8,2) NULL COMMENT '增加人天（天）' AFTER legal_holidays,
    ADD COLUMN add_hours DECIMAL(8,2) NULL COMMENT '增加小时（小时）' AFTER add_days,
    ADD COLUMN work_days DECIMAL(8,2) NULL COMMENT '累计人天（天）' AFTER add_hours,
    CHANGE COLUMN work_hours work_hours DECIMAL(8,2) NULL COMMENT '累计工时（小时）' ;

-- 2024-11-06
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('2110', '查询区协可选择的客户', '/api/admin/tb/ding/find_custom', 'URL', '26', '6', 'SYS', '1', '1', '0');
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('2120', '增加工时-查询可选sop', '/api/admin/tb/ding/add/find_sop_list', 'URL', '29', '3', 'AUTH', '1', '1', '0');
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('2121', '增加工时-提交', '/api/admin/tb/ding/add/submit', 'URL', '29', '4', 'AUTH', '1', '1', '0');
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, related, enable, default_auth, front_display) VALUES ('2122', '添加工时', 'AddDing', 'LINK', '29', '3', 'AUTH', '2120,2121', '1', '0', '1');

ALTER TABLE t_b_ding_statistic_by_person
    ADD COLUMN user_id BIGINT NULL COMMENT '用户id' AFTER service_name;

INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, enable, default_auth, front_display) VALUES ('2123', '工时统计-统计计算', '/api/admin/tb/ding/statistic/calculate', 'URL', '29', '4', 'AUTH', '1', '1', '0');
INSERT INTO sys_privilege (id, name, url, type, parent_id, sequence, property, related, enable, default_auth, front_display) VALUES ('2124', '统计计算', 'Calculate', 'BUTTON', '29', '5', 'AUTH', '2123', '1', '0', '1');

DELETE FROM sys_privilege WHERE (id = '2123');
DELETE FROM sys_privilege WHERE (id = '2124');

UPDATE t_d_form_widget
SET code='FILE', `type`='FORM', form_id='example_photos', form_name='example_photos', title='示例', input_type='ARRAY', required=0, readable=1, writable=0, visable=1, `scale`=NULL, `length`=2, binding=NULL, data_grid='https://sop-file.qmth.com.cn/logo/1.png,https://sop-file.qmth.com.cn/logo/2.png', tips=NULL, format=NULL, span=12, sub_title=NULL, `options`=NULL, flow_type='CLOUD_MARK_SOP_FLOW', form_setup=4, handle=0, form_group=NULL, role_type=NULL, create_id=1, create_time=2
WHERE id=201;

-- 2024-11-11
ALTER TABLE t_b_ding_statistic_by_person
    ADD COLUMN add_days_detail TEXT NULL COMMENT '追加详情（天）' AFTER add_days,
    ADD COLUMN add_hours_detail TEXT NULL COMMENT '追加详情（时）' AFTER add_hours;

ALTER TABLE t_b_ding_add
    DROP INDEX ding_add_unique ;
;