cnbg-be/db/v2.0/0205字段.sql

0205字段整理

--wm_score_package  积分包表
--新增字段
ALTER TABLE `wm_score_package` ADD `package_finish_time` datetime DEFAULT NULL  COMMENT '完成时间';
ALTER TABLE `wm_score_package` ADD `relation_score_id` varchar(11) DEFAULT NULL  COMMENT '关联积分包id';
ALTER TABLE `wm_score_package` ADD `package_type1` varchar(11) DEFAULT NULL  COMMENT '积分包任务类型1';
ALTER TABLE `wm_score_package` ADD `package_type2` varchar(11) DEFAULT NULL  COMMENT '积分包任务类型2';
ALTER TABLE `wm_score_package` ADD `user_num` int(11) DEFAULT NULL  COMMENT '需求人数';
ALTER TABLE `wm_score_package` ADD `task_num` int(11) DEFAULT NULL  COMMENT '任务个数';
ALTER TABLE `wm_score_package` ADD `package_user_scope` varchar(11) DEFAULT NULL  COMMENT '接单对象范围';
ALTER TABLE `wm_score_package` ADD `kfpjf` int(30)  COMMENT '可分配积分';
ALTER TABLE `wm_score_package` ADD   `belong_date` varchar(20)    DEFAULT NULL COMMENT '所属月份';
ALTER TABLE `wm_score_package` ADD   `allocation_date` datetime(0)  DEFAULT NULL COMMENT '分配日期';
ALTER TABLE `wm_score_package` ADD   `pay_rate` decimal(20, 2)  DEFAULT NULL COMMENT '已支付百分比';
ALTER TABLE `wm_score_package` ADD   `finish_rate` decimal(20, 2)  DEFAULT NULL COMMENT '已完成百分比';
ALTER TABLE `wm_score_package` ADD   `pay_score` varchar(11)   DEFAULT NULL COMMENT '已支付积分值';
ALTER TABLE `wm_score_package` ADD   `end_label_date` varchar(64)    DEFAULT NULL COMMENT '查询结束日期';
ALTER TABLE `wm_score_package` ADD `score_package_name` varchar(255) DEFAULT NULL  COMMENT '积分包名称';
ALTER TABLE `wm_score_package` ADD `related_service` varchar(11) DEFAULT NULL  COMMENT '关联服务企业';
ALTER TABLE `wm_score_package` ADD   `package_is_conduct` varchar(1)  DEFAULT NULL COMMENT '积分包是否进行中';
ALTER TABLE `wm_score_package` ADD `description` varchar(255) DEFAULT NULL  COMMENT '描述';
ALTER TABLE `wm_score_package` ADD `user_score` varchar(11) DEFAULT NULL  COMMENT '个人预计包值';
ALTER TABLE `wm_score_package` ADD `enable_type` char(1) DEFAULT 0  COMMENT '终止方式';
ALTER TABLE `wm_score_package` ADD `is_receive` varchar(1) DEFAULT NULL  COMMENT '是否允许接单';
ALTER TABLE `wm_score_package` ADD `rule_id` varchar(50) DEFAULT NULL  COMMENT '规则id';
ALTER TABLE `wm_score_package` ADD   `accept_sug` varchar(20)  DEFAULT '' COMMENT '接包对象';
ALTER TABLE `wm_score_package` ADD   `task_add_flag` varchar(1)  DEFAULT '1' COMMENT '是否可以新增任务1-可以  0-不可以';
ALTER TABLE `wm_score_package` ADD   `settle_flag` char(1)  DEFAULT '0' COMMENT '结算状态';
ALTER TABLE `wm_score_package` ADD   `score_package_status` varchar(11)  DEFAULT NULL COMMENT '积分包状态';

--更新字段属性
ALTER TABLE `wm_score_package` MODIFY `score` VARCHAR(11) DEFAULT NULL;
ALTER TABLE `wm_score_package` MODIFY `drug_ent_id` VARCHAR(11) DEFAULT NULL;
ALTER TABLE `wm_score_package` MODIFY `score_id` VARCHAR(50) DEFAULT NULL;
ALTER TABLE `wm_score_package` MODIFY `agent_type_id` VARCHAR(1) DEFAULT NULL;


--字段值
package_type1--积分包任务类型2,值--1定额包 2定量包 3通用包
package_type2--积分包任务类型2,值--1任务包 2结算包
package_user_scope--接单对象范围，值--1仅辖内可见，2全部可见
enable_type--终止方式（积分包的终止），值--0未终止，1全部收回 2部分收回
is_receive--是否允许接单，值--0允许接单 1不允许接单
task_add_flag--是否可以新增任务,值--1可以  0不可以
settle_flag--结算状态，是否发起结算请求 值--1已经发起  0没有
score_package_status--积分包状态，值--1已分派待申领 2已申领待审批 3进行中 4已完成待结算 5已提交结算申请  6已终止

--更新sql
--更新score_package_name 将原字段score_id，score_name的值更新到score_package_name
update wm_score_package set score_package_name=score_id where score_id!='';
update wm_score_package set score_package_name=score_name where score_name!='';




--wm_score_package_status  接单审核表
ALTER TABLE `wm_score_package_status` ADD `user_score` varchar(11) DEFAULT NULL  COMMENT '个人预计包值';
ALTER TABLE `wm_score_package_status` ADD `enable_type` char(1) DEFAULT 0  COMMENT '终止方式';
ALTER TABLE `wm_score_package_status` ADD `task_num` int(11)  DEFAULT NULL COMMENT '个人包任务个数';
ALTER TABLE `wm_score_package_status` ADD `task_add_flag` varchar(1)  DEFAULT '1' COMMENT '是否可以新增任务1-可以  0-不可以';

--字段值
enable_type--终止方式（接单人的终止），值--0未终止，1全部收回 2部分收回
task_add_flag--是否可以新增任务,值--1可以  0不可以




--wm_task 任务表
ALTER TABLE `wm_task` ADD `report_one_id` int(11)  DEFAULT NULL COMMENT '任务提交到一级cso的报告ID' ;
ALTER TABLE `wm_task` ADD `report_one_approval_status` VARCHAR (32)  DEFAULT '0' COMMENT '任务提交到一级cso的报告审核状态' ;
ALTER TABLE `wm_task` ADD `report_one_approval_opinion` varchar(2) DEFAULT NULL COMMENT '任务提交到一级cso的报告审批意见';
ALTER TABLE `wm_task` ADD `report_one_approval_info` varchar(50) DEFAULT NULL COMMENT '任务提交到一级cso的报告审批说明';

ALTER TABLE `wm_task` ADD `report_second_id` int(11)  DEFAULT NULL COMMENT '任务提交到二级cso的报告ID' ;
ALTER TABLE `wm_task` ADD `report_second_approval_status` VARCHAR (32)  DEFAULT '0' COMMENT '任务提交到二级cso的报告审核状态' ;
ALTER TABLE `wm_task` ADD `report_second_approval_opinion` varchar(2) DEFAULT NULL COMMENT '任务提交到二级cso的报告审批意见';
ALTER TABLE `wm_task` ADD `report_second_approval_info` varchar(50) DEFAULT NULL COMMENT '任务提交到二级cso的报告审批说明';

ALTER TABLE `wm_task` ADD `report_drug_id` int(11)  DEFAULT NULL COMMENT '任务提交到药企的报告ID' ;
ALTER TABLE `wm_task` ADD `report_drug_approval_status` VARCHAR (32)  DEFAULT '0' COMMENT '任务提交到药企的报告审核状态' ;
ALTER TABLE `wm_task` ADD `report_drug_approval_opinion` varchar(2) DEFAULT NULL COMMENT '任务提交到药企的报告审批意见';
ALTER TABLE `wm_task` ADD `report_drug_approval_info` varchar(50) DEFAULT NULL COMMENT '任务提交到药企的报告审批说明';
ALTER TABLE `wm_task` ADD  `task_status_info` varchar(500)  DEFAULT NULL COMMENT 'cso审核审核意见';

--更新score_package_id，二期只用score_package_id ,所以将score_package_level1_id，score_package_level1_id的值都更新到score_package_id
update wm_task set score_package_id=score_package_level1_id where score_package_id=null and score_package_level1_id!=null;
update wm_task set score_package_id=score_package_drug_id where score_package_id=null  and score_package_level1_id=null and score_package_drug_id!=null;



--wm_task_type
ALTER TABLE `wm_task_type` ADD `parent_id` varchar(11) DEFAULT NULL  COMMENT '父级ID';


--新增表
--结算报告表
CREATE TABLE `hnqzx`.`wm_report`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `report_no` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '报告单号',
  `drug_ent_id` int(11) NULL DEFAULT NULL COMMENT '结算企业ID',
  `score_id` int(11) NOT NULL COMMENT '积分包ID',
  `apply_drug_ent_id` int(11) NULL DEFAULT NULL COMMENT '申请结算企业ID',
  `package_score` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '积分包值',
  `task_number` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '任务数量',
  `total_score` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '合计分值',
  `report_status` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '报告状态',
  `create_people` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `approval_people` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '审核人',
  `approval_time` datetime(0) NULL DEFAULT NULL COMMENT '审核时间',
  `update_people` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `del_flag` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '是否删除',
  `export1` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备用1',
  `export2` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备用2',
  `export3` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备用3',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '报告审核表' ROW_FORMAT = Dynamic;

--积分包任务类型表
CREATE TABLE `hnqzx`.`wm_score_task_type`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `score_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '关联积分包id',
  `task_type_id` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '有效任务类型id',
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '积分包类型，1:定额，2定量',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '积分包任务类型表' ROW_FORMAT = Dynamic;


--导入任务成员表
CREATE TABLE `hnqzx`.`wm_import_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `score_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '积分包ID',
  `import_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `import_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '手机号',
  `import_id_card_number` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '身份证号',
  `user_sign` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '签名',
  `cert_status` int(2) NULL DEFAULT NULL COMMENT '认证状态',
  `cert_remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '认证结果描述',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `create_user` int(11) NULL DEFAULT NULL COMMENT '创建人',
  `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '是否删除',
  `real_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '是否禁用',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '导入任务成员表' ROW_FORMAT = Dynamic;



	update wm_task set report_drug_approval_status=6 where task_status=3 and (report_drug_approval_status is NULL or report_drug_approval_status=0);
	update wm_task set report_drug_approval_opinion=1 where task_status=3 and report_drug_approval_opinion is NULL;

	update wm_task set report_one_approval_status=6 where task_status=3 and (report_one_approval_status is NULL or report_one_approval_status=0);
	update wm_task set report_one_approval_opinion=1 where task_status=3 and report_one_approval_opinion is NULL;

	update wm_task set report_second_approval_status=6 where task_status=3 and (report_second_approval_status is NULL or report_second_approval_status=0);
	update wm_task set report_one_approval_opinion=1 where task_status=3 and report_one_approval_opinion is NULL;