123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159 |
- 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;
|