server/sop-api/install/mysql/upgrade/1.1.3.sql

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 ;

-- 2024-11-12
UPDATE sys_privilege SET enable = '0' WHERE (id = '2056');
UPDATE sys_privilege SET enable = '0' WHERE (id = '1028');
UPDATE sys_privilege SET enable = '0' WHERE (id = '1027');
UPDATE sys_privilege SET enable = '0' WHERE (id = '1026');
UPDATE sys_privilege SET enable = '0' WHERE (id = '149');
UPDATE sys_privilege SET enable = '0' WHERE (id = '151');
UPDATE sys_privilege SET enable = '0' WHERE (id = '152');
UPDATE sys_privilege SET enable = '0' WHERE (id = '2057');
UPDATE sys_privilege SET related = '2100' WHERE (id = '148');
UPDATE sys_privilege SET related = '2100' WHERE (id = '150');
UPDATE sys_privilege SET name = '追加工时' WHERE (id = '2122');

INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(50, 2024, 10, 6, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(51, 2025, 1, 1, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(52, 2025, 1, 26, 'WEEKDAY');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(53, 2025, 1, 28, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(54, 2025, 1, 29, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(55, 2025, 1, 30, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(56, 2025, 1, 31, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(57, 2025, 2, 1, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(58, 2025, 2, 2, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(59, 2025, 2, 3, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(60, 2025, 2, 4, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(61, 2025, 2, 8, 'WEEKDAY');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(62, 2025, 4, 4, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(63, 2025, 4, 5, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(64, 2025, 4, 6, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(65, 2025, 4, 27, 'WEEKDAY');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(66, 2025, 5, 1, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(67, 2025, 5, 2, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(68, 2025, 5, 3, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(69, 2025, 5, 4, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(70, 2025, 5, 5, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(71, 2025, 5, 31, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(72, 2025, 6, 1, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(73, 2025, 6, 2, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(74, 2025, 9, 28, 'WEEKDAY');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(75, 2025, 10, 1, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(76, 2025, 10, 2, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(77, 2025, 10, 3, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(78, 2025, 10, 4, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(79, 2025, 10, 5, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(80, 2025, 10, 6, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(81, 2025, 10, 7, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(82, 2025, 10, 8, 'LEGAL_HOLIDAYS');
INSERT INTO sys_ding_date
(id, `year`, `month`, `day`, `type`)
VALUES(83, 2025, 10, 11, 'WEEKDAY');

INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(291, '客户表查询', '/api/sys/custom/list/by/serviceUnit', 'URL', 64, 34, 'SYS', NULL, 1, 1, 0);