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;