0205字段.sql 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159
  1. 0205字段整理
  2. --wm_score_package 积分包表
  3. --新增字段
  4. ALTER TABLE `wm_score_package` ADD `package_finish_time` datetime DEFAULT NULL COMMENT '完成时间';
  5. ALTER TABLE `wm_score_package` ADD `relation_score_id` varchar(11) DEFAULT NULL COMMENT '关联积分包id';
  6. ALTER TABLE `wm_score_package` ADD `package_type1` varchar(11) DEFAULT NULL COMMENT '积分包任务类型1';
  7. ALTER TABLE `wm_score_package` ADD `package_type2` varchar(11) DEFAULT NULL COMMENT '积分包任务类型2';
  8. ALTER TABLE `wm_score_package` ADD `user_num` int(11) DEFAULT NULL COMMENT '需求人数';
  9. ALTER TABLE `wm_score_package` ADD `task_num` int(11) DEFAULT NULL COMMENT '任务个数';
  10. ALTER TABLE `wm_score_package` ADD `package_user_scope` varchar(11) DEFAULT NULL COMMENT '接单对象范围';
  11. ALTER TABLE `wm_score_package` ADD `kfpjf` int(30) COMMENT '可分配积分';
  12. ALTER TABLE `wm_score_package` ADD `belong_date` varchar(20) DEFAULT NULL COMMENT '所属月份';
  13. ALTER TABLE `wm_score_package` ADD `allocation_date` datetime(0) DEFAULT NULL COMMENT '分配日期';
  14. ALTER TABLE `wm_score_package` ADD `pay_rate` decimal(20, 2) DEFAULT NULL COMMENT '已支付百分比';
  15. ALTER TABLE `wm_score_package` ADD `finish_rate` decimal(20, 2) DEFAULT NULL COMMENT '已完成百分比';
  16. ALTER TABLE `wm_score_package` ADD `pay_score` varchar(11) DEFAULT NULL COMMENT '已支付积分值';
  17. ALTER TABLE `wm_score_package` ADD `end_label_date` varchar(64) DEFAULT NULL COMMENT '查询结束日期';
  18. ALTER TABLE `wm_score_package` ADD `score_package_name` varchar(255) DEFAULT NULL COMMENT '积分包名称';
  19. ALTER TABLE `wm_score_package` ADD `related_service` varchar(11) DEFAULT NULL COMMENT '关联服务企业';
  20. ALTER TABLE `wm_score_package` ADD `package_is_conduct` varchar(1) DEFAULT NULL COMMENT '积分包是否进行中';
  21. ALTER TABLE `wm_score_package` ADD `description` varchar(255) DEFAULT NULL COMMENT '描述';
  22. ALTER TABLE `wm_score_package` ADD `user_score` varchar(11) DEFAULT NULL COMMENT '个人预计包值';
  23. ALTER TABLE `wm_score_package` ADD `enable_type` char(1) DEFAULT 0 COMMENT '终止方式';
  24. ALTER TABLE `wm_score_package` ADD `is_receive` varchar(1) DEFAULT NULL COMMENT '是否允许接单';
  25. ALTER TABLE `wm_score_package` ADD `rule_id` varchar(50) DEFAULT NULL COMMENT '规则id';
  26. ALTER TABLE `wm_score_package` ADD `accept_sug` varchar(20) DEFAULT '' COMMENT '接包对象';
  27. ALTER TABLE `wm_score_package` ADD `task_add_flag` varchar(1) DEFAULT '1' COMMENT '是否可以新增任务1-可以 0-不可以';
  28. ALTER TABLE `wm_score_package` ADD `settle_flag` char(1) DEFAULT '0' COMMENT '结算状态';
  29. ALTER TABLE `wm_score_package` ADD `score_package_status` varchar(11) DEFAULT NULL COMMENT '积分包状态';
  30. --更新字段属性
  31. ALTER TABLE `wm_score_package` MODIFY `score` VARCHAR(11) DEFAULT NULL;
  32. ALTER TABLE `wm_score_package` MODIFY `drug_ent_id` VARCHAR(11) DEFAULT NULL;
  33. ALTER TABLE `wm_score_package` MODIFY `score_id` VARCHAR(50) DEFAULT NULL;
  34. ALTER TABLE `wm_score_package` MODIFY `agent_type_id` VARCHAR(1) DEFAULT NULL;
  35. --字段值
  36. package_type1--积分包任务类型2,值--1定额包 2定量包 3通用包
  37. package_type2--积分包任务类型2,值--1任务包 2结算包
  38. package_user_scope--接单对象范围,值--1仅辖内可见,2全部可见
  39. enable_type--终止方式(积分包的终止),值--0未终止,1全部收回 2部分收回
  40. is_receive--是否允许接单,值--0允许接单 1不允许接单
  41. task_add_flag--是否可以新增任务,值--1可以 0不可以
  42. settle_flag--结算状态,是否发起结算请求 值--1已经发起 0没有
  43. score_package_status--积分包状态,值--1已分派待申领 2已申领待审批 3进行中 4已完成待结算 5已提交结算申请 6已终止
  44. --更新sql
  45. --更新score_package_name 将原字段score_id,score_name的值更新到score_package_name
  46. update wm_score_package set score_package_name=score_id where score_id!='';
  47. update wm_score_package set score_package_name=score_name where score_name!='';
  48. --wm_score_package_status 接单审核表
  49. ALTER TABLE `wm_score_package_status` ADD `user_score` varchar(11) DEFAULT NULL COMMENT '个人预计包值';
  50. ALTER TABLE `wm_score_package_status` ADD `enable_type` char(1) DEFAULT 0 COMMENT '终止方式';
  51. ALTER TABLE `wm_score_package_status` ADD `task_num` int(11) DEFAULT NULL COMMENT '个人包任务个数';
  52. ALTER TABLE `wm_score_package_status` ADD `task_add_flag` varchar(1) DEFAULT '1' COMMENT '是否可以新增任务1-可以 0-不可以';
  53. --字段值
  54. enable_type--终止方式(接单人的终止),值--0未终止,1全部收回 2部分收回
  55. task_add_flag--是否可以新增任务,值--1可以 0不可以
  56. --wm_task 任务表
  57. ALTER TABLE `wm_task` ADD `report_one_id` int(11) DEFAULT NULL COMMENT '任务提交到一级cso的报告ID' ;
  58. ALTER TABLE `wm_task` ADD `report_one_approval_status` VARCHAR (32) DEFAULT '0' COMMENT '任务提交到一级cso的报告审核状态' ;
  59. ALTER TABLE `wm_task` ADD `report_one_approval_opinion` varchar(2) DEFAULT NULL COMMENT '任务提交到一级cso的报告审批意见';
  60. ALTER TABLE `wm_task` ADD `report_one_approval_info` varchar(50) DEFAULT NULL COMMENT '任务提交到一级cso的报告审批说明';
  61. ALTER TABLE `wm_task` ADD `report_second_id` int(11) DEFAULT NULL COMMENT '任务提交到二级cso的报告ID' ;
  62. ALTER TABLE `wm_task` ADD `report_second_approval_status` VARCHAR (32) DEFAULT '0' COMMENT '任务提交到二级cso的报告审核状态' ;
  63. ALTER TABLE `wm_task` ADD `report_second_approval_opinion` varchar(2) DEFAULT NULL COMMENT '任务提交到二级cso的报告审批意见';
  64. ALTER TABLE `wm_task` ADD `report_second_approval_info` varchar(50) DEFAULT NULL COMMENT '任务提交到二级cso的报告审批说明';
  65. ALTER TABLE `wm_task` ADD `report_drug_id` int(11) DEFAULT NULL COMMENT '任务提交到药企的报告ID' ;
  66. ALTER TABLE `wm_task` ADD `report_drug_approval_status` VARCHAR (32) DEFAULT '0' COMMENT '任务提交到药企的报告审核状态' ;
  67. ALTER TABLE `wm_task` ADD `report_drug_approval_opinion` varchar(2) DEFAULT NULL COMMENT '任务提交到药企的报告审批意见';
  68. ALTER TABLE `wm_task` ADD `report_drug_approval_info` varchar(50) DEFAULT NULL COMMENT '任务提交到药企的报告审批说明';
  69. ALTER TABLE `wm_task` ADD `task_status_info` varchar(500) DEFAULT NULL COMMENT 'cso审核审核意见';
  70. --更新score_package_id,二期只用score_package_id ,所以将score_package_level1_id,score_package_level1_id的值都更新到score_package_id
  71. update wm_task set score_package_id=score_package_level1_id where score_package_id=null and score_package_level1_id!=null;
  72. 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;
  73. --wm_task_type
  74. ALTER TABLE `wm_task_type` ADD `parent_id` varchar(11) DEFAULT NULL COMMENT '父级ID';
  75. --新增表
  76. --结算报告表
  77. CREATE TABLE `hnqzx`.`wm_report` (
  78. `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  79. `report_no` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '报告单号',
  80. `drug_ent_id` int(11) NULL DEFAULT NULL COMMENT '结算企业ID',
  81. `score_id` int(11) NOT NULL COMMENT '积分包ID',
  82. `apply_drug_ent_id` int(11) NULL DEFAULT NULL COMMENT '申请结算企业ID',
  83. `package_score` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '积分包值',
  84. `task_number` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '任务数量',
  85. `total_score` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '合计分值',
  86. `report_status` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '报告状态',
  87. `create_people` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
  88. `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  89. `approval_people` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '审核人',
  90. `approval_time` datetime(0) NULL DEFAULT NULL COMMENT '审核时间',
  91. `update_people` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人',
  92. `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  93. `del_flag` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '是否删除',
  94. `export1` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备用1',
  95. `export2` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备用2',
  96. `export3` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备用3',
  97. PRIMARY KEY (`id`) USING BTREE
  98. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '报告审核表' ROW_FORMAT = Dynamic;
  99. --积分包任务类型表
  100. CREATE TABLE `hnqzx`.`wm_score_task_type` (
  101. `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  102. `score_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '关联积分包id',
  103. `task_type_id` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '有效任务类型id',
  104. `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '积分包类型,1:定额,2定量',
  105. PRIMARY KEY (`id`) USING BTREE
  106. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '积分包任务类型表' ROW_FORMAT = Dynamic;
  107. --导入任务成员表
  108. CREATE TABLE `hnqzx`.`wm_import_user` (
  109. `id` int(11) NOT NULL AUTO_INCREMENT,
  110. `score_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '积分包ID',
  111. `import_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  112. `import_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '手机号',
  113. `import_id_card_number` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '身份证号',
  114. `user_sign` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '签名',
  115. `cert_status` int(2) NULL DEFAULT NULL COMMENT '认证状态',
  116. `cert_remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '认证结果描述',
  117. `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  118. `create_user` int(11) NULL DEFAULT NULL COMMENT '创建人',
  119. `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '是否删除',
  120. `real_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '是否禁用',
  121. PRIMARY KEY (`id`) USING BTREE
  122. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '导入任务成员表' ROW_FORMAT = Dynamic;
  123. 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);
  124. update wm_task set report_drug_approval_opinion=1 where task_status=3 and report_drug_approval_opinion is NULL;
  125. 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);
  126. update wm_task set report_one_approval_opinion=1 where task_status=3 and report_one_approval_opinion is NULL;
  127. 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);
  128. update wm_task set report_one_approval_opinion=1 where task_status=3 and report_one_approval_opinion is NULL;