Prodtrace/source_code/jetplasma-service/sql/online_sql/upgrade.sql

alter table jetplasma_demo_execute add princ_dept bigint null comment '执行负责人部门' after principals;
alter table jetplasma_eco add solution varchar(200) null comment '解决方案' after report;

alter table jetplasma_alteration_apply add enable_eco smallint null comment '是否验证: 1 - 是,0 - 否' after suggest;
update jetplasma_alteration_apply set enable_eco=1 where enable_eco is null;

drop table if exists flow_add_sign_hist;
CREATE TABLE `flow_add_sign_hist`  (
    `id`  bigint(20)      not null auto_increment primary key comment '主键ID',
    `instance_id` bigint(20) COMMENT '流程实例ID',
    `business_id` bigint(20) COMMENT '业务记录编号',
    `task_id` bigint(20) COMMENT '任务节点ID',
    `user_id` bigint(20) COMMENT '指定审批人',
    `user_dept_id` bigint(20) COMMENT '指定审批人部门号',
    `create_by` bigint(20) COMMENT '加签人编号',
    `create_dept` bigint(20) COMMENT '加签人部门',
    `flow_status` tinyint(1) comment '审批结果',
    `message` varchar(500) comment '备注信息',
    `approve_at` datetime default null comment '审批时间',
    `create_at`   datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
)  engine=innodb comment = '加签历史记录表';


alter table jetplasma_alteration_apply add cur_approve varchar(200) null comment '当前审批人' after approve_status;
alter table jetplasma_eco add cur_approve varchar(200) null comment '当前审批人' after approve_status;

delete from sys_job;
delete from sys_job_log;
INSERT INTO jetplasma.sys_job (job_name, job_group, invoke_target, cron_expression, misfire_policy, concurrent, status, create_by, create_time, update_by, update_time, remark) VALUES ('定时发送审批提醒', 'DEFAULT', 'flowTask.sendEmail()', '0 0 12 * * ?', '1', '1', '0', 'admin', '2024-11-02 06:02:28', '', '2024-11-02 06:02:31', '');

alter table jetplasma_device_problem_check add handler varchar(200) null comment '问题处理人';
alter table jetplasma_device_problem_sale add handler varchar(200) null comment '问题处理人';

alter table jetplasma_device_problem_check add handler_dept varchar(200) null comment '问题处理人部门';
alter table jetplasma_device_problem_sale add handler_dept varchar(200) null comment '问题处理人部门';

alter table flow_definition
    add auto_pass tinyint null comment '相同审批人是否自动通过, 1 - 是 ， 0 - 否' after from_path;

update flow_definition set flow_definition.auto_pass=0;

alter table jetplasma_alteration_apply add create_user bigint null comment '提交人';
alter table jetplasma_eco add create_user bigint null comment '提交人';
alter table jetplasma_eco_meta add create_user bigint null comment '提交人';
alter table jetplasma_ecn add create_user bigint null comment '提交人';
alter table jetplasma_ecn_meta add create_user bigint null comment '提交人';
alter table jetplasma_ecn_spread add create_user bigint null comment '提交人';
alter table jetplasma_ecn_progress add create_user bigint null comment '提交人';
alter table jetplasma_demo add create_user bigint null comment '提交人';
alter table jetplasma_demo_execute add create_user bigint null comment '提交人';
alter table jetplasma_demo_execute_progress add create_user bigint null comment '提交人';

alter table jetplasma_eco
    modify report longtext null comment '验证报告';

alter table jetplasma_eco
    modify solution longtext null comment '解决方案';

alter table jetplasma_demo
    modify attachment longtext null comment '附件';

alter table jetplasma_demo_execute
    modify attachment longtext null comment '附件';

alter table jetplasma_alteration_apply
    modify attachment longtext null comment '附件';
alter table jetplasma_demo
    add submit_at datetime null comment '审批提交时间';
alter table jetplasma_demo
    add submit_user bigint null comment '审批提交人';

alter table jetplasma_demo_execute
    add submit_at datetime null comment '审批提交时间';
alter table jetplasma_demo_execute
    add submit_user bigint null comment '审批提交人';

alter table jetplasma_demo_execute_progress
    add submit_at datetime null comment '审批提交时间';
alter table jetplasma_demo_execute_progress
    add submit_user bigint null comment '审批提交人';

alter table jetplasma_alteration_apply
    add submit_at datetime null comment '审批提交时间';
alter table jetplasma_alteration_apply
    add submit_user bigint null comment '审批提交人';

alter table jetplasma_eco
    add submit_at datetime null comment '审批提交时间';
alter table jetplasma_eco
    add submit_user bigint null comment '审批提交人';

alter table jetplasma_ecn
    add submit_at datetime null comment '审批提交时间';
alter table jetplasma_ecn
    add submit_user bigint null comment '审批提交人';


drop view if exists jetplasma_demo_execute_view;
create view jetplasma_demo_execute_view as
select e.*, d.customer, d.device_type, d.saler, d.requirements from jetplasma_demo d, jetplasma_demo_execute e where d.id = e.demo_id;


drop view if exists jetplasma_eco_view;
CREATE VIEW jetplasma_eco_view AS
select e.*, jaa.apply_user, jaa.apply_date, jaa.apply_depart from jetplasma_eco e left join jetplasma_alteration_apply jaa on e.alteration_id = jaa.id;

drop view if exists jetplasma_ecn_view;
CREATE VIEW jetplasma_ecn_view AS
select e.*, jaa.apply_user, jaa.apply_depart from jetplasma_ecn e left join jetplasma_alteration_apply jaa on e.alteration_id = jaa.id;


alter table jetplasma_demo add cur_approve varchar(200) null comment '当前审批人' after approve_status;
alter table jetplasma_demo_execute add cur_approve varchar(200) null comment '当前审批人' after approve_status;

drop view if exists jetplasma_demo_execute_view;
create view jetplasma_demo_execute_view as
select e.*, d.customer, d.device_type, d.saler, d.requirements from jetplasma_demo d, jetplasma_demo_execute e where d.id = e.demo_id;