programs_info/三资项目/删除前备份/流程不使用直接上级前的版本备份/doc/sql/2023/02/10/cf.sql

create table idea_oa.oa_demand_pool
(
    id               varchar(100) not null comment '主键'
        primary key,
    pro_id           varchar(100) null comment '商机id',
    pro_name         varchar(255) null comment '关联项目',
    demand_source    varchar(100) null comment '需求来源',
    demand_sketch    varchar(255) null comment '需求简述',
    demand_files     varchar(255) null comment '附件ids',
    sub_user         varchar(100) null comment '提交人',
    sub_user_name    varchar(100) null comment '提交人姓名',
    sub_date         datetime     null comment '提交日期',
    demand_state     varchar(100) null comment '状态',
    handle_user      varchar(100) null comment '处理人',
    handle_user_name varchar(100) null comment '处理人姓名',
    receipt_time     datetime     null comment '回执时间',
    handle_type      varchar(100) null comment '处理方式',
    handle_id        varchar(100) null comment '处理方式回填id',
    cost_hour        varchar(100) null comment '所耗工时（小时）',
    create_user      varchar(100) null comment '创建人',
    create_time      datetime     null comment '创建时间',
    update_user      varchar(100) null comment '更新人',
    update_time      datetime     null comment '更新时间',
    group_id         varchar(100) null comment '集团id'
)
    comment '需求池';

create index oa_demand_pool__index
    on idea_oa.oa_demand_pool (pro_id);

create index oa_demand_pool_group_id_index
    on idea_oa.oa_demand_pool (group_id);

INSERT INTO idea_oa.sys_dict(id, dict_name, dict_code, dict_value, sys_id) VALUES ('1074632361754230784', '需求来源', 'DEMAND_SOURCE', '[{"id":1676253845350,"label":"销售","value":"0","isSearch":false},{"id":1676253898445,"label":"用户反馈","value":"1","isSearch":false},{"id":1676253905265,"label":"市场","value":"2","isSearch":false},{"id":1676253911321,"label":"运营","value":"3","isSearch":false}]', 'BIZ_OA');

create table idea_oa.oa_budget_template
(
    id               varchar(100) not null comment '主键'
        primary key,
    pro_type           varchar(100) null comment '项目类型',
    pro_type_name         varchar(100) null comment '项目类型名称',
    budget_type         varchar(100) null comment '预算类型（成本类、费用类）',
    sort_no    integer null comment '排序',
    budget_child_type    varchar(255) null comment '预算子项',
    need_detail     varchar(10) null comment '需明细（0是1否）',
    upper_limit         varchar(100) null comment '上限设定（定额、占比销售额）',
    upper_limit_value    decimal(18,6) null comment '上限值',
    effect_date    datetime null comment '生效日期',
    create_user      varchar(100) null comment '创建人',
    create_time      datetime     null comment '创建时间',
    update_user      varchar(100) null comment '更新人',
    update_time      datetime     null comment '更新时间',
    group_id         varchar(100) null comment '集团id'
)
    comment '预算模板设定';

create index oa_budget_template_group_id_index
    on idea_oa.oa_budget_template (group_id);

INSERT INTO idea_oa.sys_dict (id, dict_name, dict_code, dict_value, sys_id) VALUES ('1074713187305127936', '预算模板-成本类子项', 'BUDGET_CB', '[{"id":1676273146808,"label":"人力成本","value":"人力成本","isSearch":false},{"id":1676273156020,"label":"设备","value":"设备","isSearch":false},{"id":1676273164932,"label":"材料","value":"材料","isSearch":false},{"id":1676273176969,"label":"外包研发","value":"外包研发","isSearch":false}]', 'BIZ_OA');
INSERT INTO idea_oa.sys_dict (id, dict_name, dict_code, dict_value, sys_id) VALUES ('1074713523256295424', '预算模板-费用类子项', 'BUDGET_FY', '[{"id":1676273223102,"label":"差旅费","value":"差旅费","isSearch":false},{"id":1676273233698,"label":"餐费","value":"餐费","isSearch":false},{"id":1676273233882,"label":"商务费","value":"商务费","isSearch":false},{"id":1676273234040,"label":"交通费","value":"交通费","isSearch":false},{"id":1676273253886,"label":"办公费","value":"办公费","isSearch":false},{"id":1676273254053,"label":"其他","value":"其他","isSearch":false}]', 'BIZ_OA');

create table idea_oa.oa_user_cost
(
    id               varchar(100) not null comment '主键'
        primary key,
    post_id           varchar(100) null comment '岗位id',
    post_name         varchar(100) null comment '岗位名称',
    post_level         varchar(100) null comment '岗位层级',
    post_cost    decimal(18,2) null comment '人力成本',
    effect_date    datetime null comment '生效日期',
    create_user      varchar(100) null comment '创建人',
    create_time      datetime     null comment '创建时间',
    update_user      varchar(100) null comment '更新人',
    update_time      datetime     null comment '更新时间',
    group_id         varchar(100) null comment '集团id'
)
    comment '人员成本设定';

create index oa_user_cost_group_id_index
    on idea_oa.oa_user_cost (group_id);

INSERT INTO idea_oa.sys_dict (id, dict_name, dict_code, dict_value, sys_id) VALUES ('1075346069581201408', '人员成本-岗位层级', 'POST_LEVEL', '[{"id":1676424043485,"label":"初级","value":"初级","isSearch":false},{"id":1676424043840,"label":"中级","value":"中级","isSearch":false},{"id":1676424060954,"label":"高级","value":"高级","isSearch":false},{"id":1676424064869,"label":"资深","value":"资深","isSearch":false}]', 'BIZ_OA');

create table idea_oa.oa_budget_making
(
    id               varchar(100) not null comment '主键' primary key,
    pro_id           varchar(100) null comment '项目类型',
    budget_type      varchar(100) null comment '预算类型（成本类、费用类）',
    sort_no    integer null comment '排序',
    budget_child_type    varchar(255) null comment '预算子项',
    need_detail     varchar(10) null comment '需明细（0是1否）',
    budget_amount    decimal(18,6) null comment '预算金额',
    budget_file    varchar(255) null comment '附件',
    budget_status    integer null comment '状态（0暂存 1保存 2变更申请中 3变更申请通过）',
    create_user      varchar(100) null comment '创建人',
    create_time      datetime     null comment '创建时间',
    update_user      varchar(100) null comment '更新人',
    update_time      datetime     null comment '更新时间',
    group_id         varchar(100) null comment '集团id'
)
    comment '预算编制';

create index oa_budget_making_group_id_index
    on idea_oa.oa_budget_making (group_id);

create index oa_budget_making_pro_id_index
    on idea_oa.oa_budget_making (pro_id);

alter table oa_pro_task_enclosure
    add pro_id varchar(100) null comment '项目id' after task_id;