tocc-sq/sql/20211103修改.sql

delete
from tbl_paln;
delete
from tbl_paln_task;
delete
from tbl_incident;
delete
from tbl_incident_process;

create table b_plan
(
    id          bigint not null
        primary key,
    name        varchar(255),
    type        integer,
    create_by   varchar(64),
    create_time timestamp(0),
    create_dept varchar(64),
    update_by   varchar(64),
    update_time timestamp(0),
    del_flag    char default '0'
);
comment
on table  b_plan is '预案管理';
comment
on column b_plan.id is '主键id';
comment
on column b_plan.name is '预案名称';
comment
on column b_plan.type is '预案类型';
comment
on column b_plan.create_by is '创建人';
comment
on column b_plan.create_time is '创建时间';
comment
on column b_plan.create_dept is '创建部门';


create table b_plan_task
(
    id          bigint not null
        primary key,
    plan_id     bigint,
    level       int,
    task_name   varchar(255),
    task_des    varchar(2550),
    create_by   varchar(64),
    create_time timestamp(0),
    update_by   varchar(64),
    update_time timestamp(0),
    del_flag    char default '0'
);

comment
on table  b_plan_task is '预案任务管理';
comment
on column b_plan_task.id is '任务id';
comment
on column b_plan_task.plan_id is '所属预案id';
comment
on column b_plan_task.level is '预案等级';
comment
on column b_plan_task.task_name is '任务名称';
comment
on column b_plan_task.task_des is '任务描述';
comment
on column b_plan_task.create_by is '创建用户';
comment
on column b_plan_task.create_time is '创建时间';

create table b_plan_file
(
    id          bigint not null primary key,
    plan_id     bigint,
    file_name   varchar(255),
    file_url    varchar(255),
    create_by   varchar(64),
    create_time timestamp(0),
    update_by   varchar(64),
    update_time timestamp(0),
    del_flag    char default '0'
);

comment
on table  b_plan_file is '预案文件管理';
comment
on column b_plan_file.id is '=id';
comment
on column b_plan_file.plan_id is '任务id';
comment
on column b_plan_file.file_name is '文件名称';
comment
on column b_plan_file.file_url is '文件路径';


create table b_incident
(
    id          bigint not null primary key,
    type        smallint,
    level       smallint,
    addr        varchar(255),
    locations   varchar(255),
    create_dept varchar(64),
    create_by   varchar(64),
    create_time timestamp(0),
    update_by   varchar(64),
    update_time timestamp(0),
    del_flag    char default '0',
    source      varchar(16),
    des         varchar(2550),
    pic         varchar(2550),
    video       varchar(2550),
    expr1       varchar(255),
    expr2       varchar(255),
    expr_json   text,
    name        varchar(255),
    status      smallint,
    madin_dept  integer,
    assist_dept varchar(255)
);

comment on table  b_incident is '事件基础表';
comment on column b_incident.id is '主键id';
comment on column b_incident.type is '事件类型';
comment on column b_incident.level is '事件等级';
comment on column b_incident.addr is '事件地址';
comment on column b_incident.locations is '经纬度';
comment on column b_incident.create_dept is '上报部门';
comment on column b_incident.create_by is '上报人';
comment on column b_incident.create_time is '上报时间';
comment on column b_incident.update_by is '修改人';
comment on column b_incident.update_time is '修改时间';
comment on column b_incident.source is '事件来源';
comment on column b_incident.des is '事件描述';
comment on column b_incident.pic is '上传图片地址';
comment on column b_incident.video is '上传视频地址';
comment on column b_incident.expr1 is '拓展1';
comment on column b_incident.expr2 is '拓展2';
comment on column b_incident.expr_json is 'json拓展，用于存取多出的字段';
comment on column b_incident.name is '事件标题';
comment on column b_incident.status is '事件状态 1：预警，2：事件 3：归档';
comment on column b_incident.madin_dept is '主办单位';
comment on column b_incident.assist_dept is '协办单位 多个数据用，分割';


create table b_incident_process
(
    id          bigint not null primary key,
    incident_id        bigint,
    des         varchar(2550),
    create_time timestamp(0),
    create_by   varchar(64),
    status      smallint
);
comment on table  b_incident_process is '事件处置流程';
comment on column b_incident_process.id is '主键id';
comment on column b_incident_process.incident_id is '所属事件id';
comment on column b_incident_process.des is '处置过程';
comment on column b_incident_process.create_time is '过程创建时间';
comment on column b_incident_process.create_by is '过程创建人';
comment on column b_incident_process.status is '流程进度当前状态';


create table b_incident_task
(
    id          bigint not null primary key,
    incident_id        bigint,
    task_name   varchar(255),
    create_time timestamp(0),
    create_by   varchar(64),
    del_flag    char default '0'
);

comment on table  b_incident_task is '事件处置任务';
comment on column b_incident_task.id is '主键id';
comment on column b_incident_task.incident_id is '所属事件id';
comment on column b_incident_task.task_name is '事件任务名称';
comment on column b_incident_task.create_time is '过程创建时间';
comment on column b_incident_task.create_by is '过程创建人';

INSERT INTO sys_dict_type (dict_id, dict_name, dict_type, status, create_by, create_time, update_by, update_time, remark) VALUES (18, '应急资源', 'zhdd_resource', '0', 'admin', '2021-09-28 15:55:09', 'admin', '2021-09-28 15:55:09', null);
INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (127, 0, '应急仓库', '1', 'zhdd_resource', null, 'default', 'N', '0', 'admin', '2021-09-28 15:57:23', 'admin', '2021-09-28 15:57:23', null);
INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (128, 0, '应急队伍', '2', 'zhdd_resource', null, 'default', 'N', '0', 'admin', '2021-09-28 15:57:23', 'admin', '2021-09-28 15:57:23', null);
INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (129, 0, '应急车辆', '3', 'zhdd_resource', null, 'default', 'N', '0', 'admin', '2021-09-28 15:57:23', 'admin', '2021-09-28 15:57:23', null);

INSERT INTO sys_dict_type (dict_id, dict_name, dict_type, status, create_by, create_time, update_by, update_time, remark) VALUES (19, '首页类型筛选', 'zhdd_screen_type', '0', 'admin', '2021-09-28 15:55:09', 'admin', '2021-09-28 15:55:09', null);
INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (130, 0, '预警事件', '预警事件', 'zhdd_screen_type', null, 'default', 'N', '0', 'admin', '2021-09-28 15:57:23', 'admin', '2021-09-28 15:57:23', null);
INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (131, 0, '待派发事件', '待派发事件', 'zhdd_screen_type', null, 'default', 'N', '0', 'admin', '2021-09-28 15:57:23', 'admin', '2021-09-28 15:57:23', null);
INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (132, 0, '待处置事件', '待处置事件', 'zhdd_screen_type', null, 'default', 'N', '0', 'admin', '2021-09-28 15:57:23', 'admin', '2021-09-28 15:57:23', null);
INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (133, 0, '路况信息', '路况信息', 'zhdd_screen_type', null, 'default', 'N', '0', 'admin', '2021-09-28 15:57:23', 'admin', '2021-09-28 15:57:23', null);
INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (134, 0, '视频监控', '视频监控', 'zhdd_screen_type', null, 'default', 'N', '0', 'admin', '2021-09-28 15:57:23', 'admin', '2021-09-28 15:57:23', null);
INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (135, 0, '应急车辆', '应急车辆', 'zhdd_screen_type', null, 'default', 'N', '0', 'admin', '2021-09-28 15:57:23', 'admin', '2021-09-28 15:57:23', null);
INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (136, 0, '应急队伍', '应急队伍', 'zhdd_screen_type', null, 'default', 'N', '0', 'admin', '2021-09-28 15:57:23', 'admin', '2021-09-28 15:57:23', null);
INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (137, 0, '应急仓库', '应急仓库', 'zhdd_screen_type', null, 'default', 'N', '0', 'admin', '2021-09-28 15:57:23', 'admin', '2021-09-28 15:57:23', null);

alter table b_resource
    add car_type varchar(20);
comment on column b_resource.car_type is '车辆类型';

alter table b_duty_emp alter column id type varchar(36) using id::varchar(36);
alter table b_duty_report alter column id type varchar(36) using id::varchar(36);
alter table b_incident alter column id type varchar(36) using id::varchar(36);
alter table b_incident_process alter column id type varchar(36) using id::varchar(36);
alter table b_incident_task alter column id type varchar(36) using id::varchar(36);
alter table b_plan alter column id type varchar(36) using id::varchar(36);
alter table b_plan_file alter column id type varchar(36) using id::varchar(36);
alter table b_plan_task alter column id type varchar(36) using id::varchar(36);
alter table b_resource alter column id type varchar(36) using id::varchar(36);
alter table b_resource_detail alter column id type varchar(36) using id::varchar(36);
alter table b_incident_process alter column incident_id type varchar(36) using incident_id::varchar(36);
alter table b_incident_task alter column incident_id type varchar(36) using incident_id::varchar(36);
alter table b_plan_file alter column plan_id type varchar(36) using plan_id::varchar(36);
alter table b_plan_task alter column plan_id type varchar(36) using plan_id::varchar(36);
alter table b_resource_detail alter column resource_id type varchar(36) using resource_id::varchar(36);
alter table b_incident alter column madin_dept type varchar(36) using madin_dept::varchar(36);


INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time) VALUES (138, 0, '皮卡', '皮卡', 'zhdd_car_type', null, 'default', 'N', '0', 'admin', '2021-09-28 15:57:23', 'admin', '2021-09-28 15:57:23');
INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time) VALUES (139, 0, '轿车', '轿车', 'zhdd_car_type', null, 'default', 'N', '0', 'admin', '2021-09-28 15:57:23', 'admin', '2021-09-28 15:57:23');
INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time) VALUES (140, 0, '越野车', '越野车', 'zhdd_car_type', null, 'default', 'N', '0', 'admin', '2021-09-28 15:57:23', 'admin', '2021-09-28 15:57:23');
INSERT INTO sys_dict_type (dict_id, dict_name, dict_type, status, create_by, create_time, update_by, update_time) VALUES (20, '车辆类型', 'zhdd_car_type', '0', 'admin', '2021-09-28 15:55:09', 'admin', '2021-09-28 15:55:09');

create table b_incident_task_person
(
    id varchar(36),
    incident_task_id varchar(36),
    position varchar(20),
    person varchar(10),
    create_time timestamp(0),
    create_by varchar(64)
);
comment on table b_incident_task_person is '事件处置方案人员';
comment on column b_incident_task_person.id is '主键';
comment on column b_incident_task_person.incident_task_id is '事件id';
comment on column b_incident_task_person.position is '人员职位';
comment on column b_incident_task_person.person is '人员姓名';

create table b_incident_task_command
(
    id          varchar(36),
    incident_task_id varchar(36),
    command    varchar(300),
    create_time timestamp(0),
    create_by   varchar(64)
);
comment on table  b_incident_task_command is '事件处置方案指令';
comment on column b_incident_task_command.id is '主键';
comment on column b_incident_task_command.incident_task_id is '事件方案id';
comment on column b_incident_task_command.command is '指令';