Home Explore Help
Sign In
cnbg
/
cnbg-be
4
0
Fork 0
Files Issues 0 Pull Requests 0 Wiki
Tree: 804568b04b
Branches Tags
cnbg-be
/
db
/
v2.0
/
12-01.sql

12-01.sql 14 KB
History Raw

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273 
  1. 

  2. 

  3. SET NAMES utf8mb4;
    4. 

  4. SET FOREIGN_KEY_CHECKS = 0;
    5. 

  5. 

  6. -- ----------------------------
    7. 

  7. -- Table structure for wm_payoff
    8. 

  8. -- ----------------------------
    9. 

  9. DROP TABLE IF EXISTS `wm_payoff`;
    10. 

  10. CREATE TABLE `wm_payoff` (
    11. 

  11.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
    12. 

  12.   `score_name` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '积分包名称',
    13. 

  13.   `id_number` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '身份证号',
    14. 

  14.   `settle_status` int(2) DEFAULT NULL COMMENT '结算状态',
    15. 

  15.   `cause_failure` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '失败原因',
    16. 

  16.   `cert_status` int(2) DEFAULT NULL COMMENT '实名认证状态',
    17. 

  17.   `submitted_value` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '已提交积分值',
    18. 

  18.   `settle_amount` decimal(30,8) DEFAULT NULL COMMENT '实际结算金额',
    19. 

  19.   `discount` decimal(11,4) DEFAULT NULL COMMENT '折扣',
    20. 

  20.   `score_id` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL  COMMENT '关联积分包id',
    21. 

  21.   `settle_people` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '结算人',
    22. 

  22.   `settle_time` datetime DEFAULT NULL COMMENT '结算时间',
    23. 

  23.   `statement_no` varchar(64) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '结算单号',
    24. 

  24.   `export1` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '备用1',
    25. 

  25.   `export2` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '备用2',
    26. 

  26.   `export3` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '备用3',
    27. 

  27.   PRIMARY KEY (`id`)
    28. 

  28. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='结算表';
    29. 

  29. 

  30. SET FOREIGN_KEY_CHECKS = 1;
    31. 

  31. 

  32. 

  33. ALTER TABLE `wm_score_package` MODIFY `invoice_category` int(11) ;
    34. 

  34. 

  35. ALTER TABLE `sys_dept` ADD `app_id` VARCHAR (32)  COMMENT '' ;
    36. 

  36. ALTER TABLE `sys_dept` ADD `app_secret` VARCHAR (32)  COMMENT '' ;
    37. 

  37. ALTER TABLE `sys_dept` ADD `rsa_public_key` VARCHAR(256) DEFAULT NULL  COMMENT '';
    38. 

  38. ALTER TABLE `sys_dept` ADD `query_url` VARCHAR(32) DEFAULT NULL  COMMENT '';
    39. 

  39. 

  40. 

  41. 

  42. create or replace view view_wm_socre_package_total as select `ccc`.`id` AS `id`,
    43. 

  43. `ccc`.`invoice_category` AS `invoice_category`,
    44. 

  44. `ccc`.`settle_status` AS `settle_status`,
    45. 

  45. `ccc`.`settle_amount` AS `settle_amount`,
    46. 

  46. `ccc`.`discount` AS `discount`,
    47. 

  47. `ccc`.`task_rule_id` AS `task_rule_id`,`ccc`.`drug_ent_id` AS `drug_ent_id`,`ccc`.`score_id` AS `score_id`,`ccc`.`score_name` AS `score_name`,`ccc`.`score` AS `score`,`ccc`.`agent_type_id` AS `agent_type_id`,`ccc`.`level1_id` AS `level1_id`,`ccc`.`level2_id` AS `level2_id`,`ccc`.`zb_id` AS `zb_id`,`ccc`.`xxdb_id` AS `xxdb_id`,`ccc`.`typeid` AS `typeid`,`ccc`.`package_status` AS `package_status`,`ccc`.`package_finish_status` AS `package_finish_status`,`ccc`.`package_audit_status` AS `package_audit_status`,`ccc`.`send_package_dept_id` AS `send_package_dept_id`,`ccc`.`dept_id` AS `dept_id`,`ccc`.`del_flag` AS `del_flag`,`ccc`.`enable_flag` AS `enable_flag`,`ccc`.`tenant_id` AS `tenant_id`,`ccc`.`create_time` AS `create_time`,`ccc`.`create_user` AS `create_user`,`ccc`.`update_time` AS `update_time`,`ccc`.`update_user` AS `update_user`,`ccc`.`receive_date` AS `receive_date`,`ccc`.`ytj` AS `ytj`,`ccc`.`ysh` AS `ysh`,`ccc`.`dsh` AS `dsh`,`ccc`.`t_type` AS `t_type` from (select `hnqzx`.`wm_score_package`.`id` AS `id`,
    48. 

  48. `hnqzx`.`wm_score_package`.`invoice_category` AS `invoice_category`,
    49. 

  49. `hnqzx`.`wm_score_package`.`settle_status` AS `settle_status`,
    50. 

  50. `hnqzx`.`wm_score_package`.`settle_amount` AS `settle_amount`,
    51. 

  51. `hnqzx`.`wm_score_package`.`discount` AS `discount`,
    52. 

  52. `hnqzx`.`wm_score_package`.`task_rule_id` AS `task_rule_id`,`hnqzx`.`wm_score_package`.`drug_ent_id` AS `drug_ent_id`,`hnqzx`.`wm_score_package`.`score_id` AS `score_id`,`hnqzx`.`wm_score_package`.`score_name` AS `score_name`,`hnqzx`.`wm_score_package`.`score` AS `score`,`hnqzx`.`wm_score_package`.`agent_type_id` AS `agent_type_id`,`hnqzx`.`wm_score_package`.`level1_id` AS `level1_id`,`hnqzx`.`wm_score_package`.`level2_id` AS `level2_id`,`hnqzx`.`wm_score_package`.`zb_id` AS `zb_id`,`hnqzx`.`wm_score_package`.`xxdb_id` AS `xxdb_id`,`hnqzx`.`wm_score_package`.`typeid` AS `typeid`,`hnqzx`.`wm_score_package`.`package_status` AS `package_status`,`hnqzx`.`wm_score_package`.`package_finish_status` AS `package_finish_status`,`hnqzx`.`wm_score_package`.`package_audit_status` AS `package_audit_status`,`hnqzx`.`wm_score_package`.`send_package_dept_id` AS `send_package_dept_id`,`hnqzx`.`wm_score_package`.`dept_id` AS `dept_id`,`hnqzx`.`wm_score_package`.`del_flag` AS `del_flag`,`hnqzx`.`wm_score_package`.`enable_flag` AS `enable_flag`,`hnqzx`.`wm_score_package`.`tenant_id` AS `tenant_id`,`hnqzx`.`wm_score_package`.`create_time` AS `create_time`,`hnqzx`.`wm_score_package`.`create_user` AS `create_user`,`hnqzx`.`wm_score_package`.`update_time` AS `update_time`,`hnqzx`.`wm_score_package`.`update_user` AS `update_user`,`hnqzx`.`wm_score_package`.`receive_date` AS `receive_date`,`hnqzx`.`wm_score_package`.`is_conduct` AS `is_conduct`,`bb`.`ytj` AS `ytj`,`bb`.`ysh` AS `ysh`,`bb`.`dsh` AS `dsh`,1 AS `t_type` from (`hnqzx`.`wm_score_package` left join (select `hnqzx`.`wm_task`.`score_package_id` AS `score_package_id`,sum(if((`hnqzx`.`wm_task`.`task_status` > 0),`hnqzx`.`wm_task`.`score`,0)) AS `ytj`,sum(if((`hnqzx`.`wm_task`.`task_status` = 3),`hnqzx`.`wm_task`.`score`,0)) AS `ysh`,sum(if((`hnqzx`.`wm_task`.`task_status` = 2),`hnqzx`.`wm_task`.`score`,0)) AS `dsh` from `hnqzx`.`wm_task` where (`hnqzx`.`wm_task`.`score_package_id` is not null) group by `hnqzx`.`wm_task`.`score_package_id`) `bb` on((`hnqzx`.`wm_score_package`.`id` = `bb`.`score_package_id`))) where ((`hnqzx`.`wm_score_package`.`del_flag` = '0') and (`hnqzx`.`wm_score_package`.`enable_flag` = '0') and (`hnqzx`.`wm_score_package`.`agent_type_id` = 2) and (`hnqzx`.`wm_score_package`.`typeid` in (3,4))) union select `hnqzx`.`wm_score_package`.`id` AS `id`,`hnqzx`.`wm_score_package`.`invoice_category` AS `invoice_category`,`hnqzx`.`wm_score_package`.`settle_status` AS `settle_status`,`hnqzx`.`wm_score_package`.`settle_amount` AS `settle_amount`,`hnqzx`.`wm_score_package`.`discount` AS `discount`,`hnqzx`.`wm_score_package`.`task_rule_id` AS `task_rule_id`,`hnqzx`.`wm_score_package`.`drug_ent_id` AS `drug_ent_id`,`hnqzx`.`wm_score_package`.`score_id` AS `score_id`,`hnqzx`.`wm_score_package`.`score_name` AS `score_name`,`hnqzx`.`wm_score_package`.`score` AS `score`,`hnqzx`.`wm_score_package`.`agent_type_id` AS `agent_type_id`,`hnqzx`.`wm_score_package`.`level1_id` AS `level1_id`,`hnqzx`.`wm_score_package`.`level2_id` AS `level2_id`,`hnqzx`.`wm_score_package`.`zb_id` AS `zb_id`,`hnqzx`.`wm_score_package`.`xxdb_id` AS `xxdb_id`,`hnqzx`.`wm_score_package`.`typeid` AS `typeid`,`hnqzx`.`wm_score_package`.`package_status` AS `package_status`,`hnqzx`.`wm_score_package`.`package_finish_status` AS `package_finish_status`,`hnqzx`.`wm_score_package`.`package_audit_status` AS `package_audit_status`,`hnqzx`.`wm_score_package`.`send_package_dept_id` AS `send_package_dept_id`,`hnqzx`.`wm_score_package`.`dept_id` AS `dept_id`,`hnqzx`.`wm_score_package`.`del_flag` AS `del_flag`,`hnqzx`.`wm_score_package`.`enable_flag` AS `enable_flag`,`hnqzx`.`wm_score_package`.`tenant_id` AS `tenant_id`,`hnqzx`.`wm_score_package`.`create_time` AS `create_time`,`hnqzx`.`wm_score_package`.`create_user` AS `create_user`,`hnqzx`.`wm_score_package`.`update_time` AS `update_time`,`hnqzx`.`wm_score_package`.`update_user` AS `update_user`,`hnqzx`.`wm_score_package`.`receive_date` AS `receive_date`,`hnqzx`.`wm_score_package`.`is_conduct` AS `is_conduct`,`bb`.`ytj` AS `ytj`,`bb`.`ysh` AS `ysh`,`bb`.`dsh` AS `dsh`,2 AS `t_type` from (`hnqzx`.`wm_score_package` left join (select `hnqzx`.`wm_task`.`score_package_level1_id` AS `score_package_id`,sum(if((`hnqzx`.`wm_task`.`task_status` > 0),`hnqzx`.`wm_task`.`score`,0)) AS `ytj`,sum(if((`hnqzx`.`wm_task`.`task_status` = 3),`hnqzx`.`wm_task`.`score`,0)) AS `ysh`,sum(if((`hnqzx`.`wm_task`.`task_status` = 2),`hnqzx`.`wm_task`.`score`,0)) AS `dsh` from `hnqzx`.`wm_task` where (`hnqzx`.`wm_task`.`score_package_id` is not null) group by `hnqzx`.`wm_task`.`score_package_level1_id`) `bb` on((`hnqzx`.`wm_score_package`.`id` = `bb`.`score_package_id`))) where ((`hnqzx`.`wm_score_package`.`del_flag` = '0') and (`hnqzx`.`wm_score_package`.`enable_flag` = '0') and (`hnqzx`.`wm_score_package`.`agent_type_id` = 2) and (`hnqzx`.`wm_score_package`.`typeid` = 2)) union select `hnqzx`.`wm_score_package`.`id` AS `id`,`hnqzx`.`wm_score_package`.`invoice_category` AS `invoice_category`,`hnqzx`.`wm_score_package`.`settle_status` AS `settle_status`,`hnqzx`.`wm_score_package`.`settle_amount` AS `settle_amount`,`hnqzx`.`wm_score_package`.`discount` AS `discount`,`hnqzx`.`wm_score_package`.`task_rule_id` AS `task_rule_id`,`hnqzx`.`wm_score_package`.`drug_ent_id` AS `drug_ent_id`,`hnqzx`.`wm_score_package`.`score_id` AS `score_id`,`hnqzx`.`wm_score_package`.`score_name` AS `score_name`,`hnqzx`.`wm_score_package`.`score` AS `score`,`hnqzx`.`wm_score_package`.`agent_type_id` AS `agent_type_id`,`hnqzx`.`wm_score_package`.`level1_id` AS `level1_id`,`hnqzx`.`wm_score_package`.`level2_id` AS `level2_id`,`hnqzx`.`wm_score_package`.`zb_id` AS `zb_id`,`hnqzx`.`wm_score_package`.`xxdb_id` AS `xxdb_id`,`hnqzx`.`wm_score_package`.`typeid` AS `typeid`,`hnqzx`.`wm_score_package`.`package_status` AS `package_status`,`hnqzx`.`wm_score_package`.`package_finish_status` AS `package_finish_status`,`hnqzx`.`wm_score_package`.`package_audit_status` AS `package_audit_status`,`hnqzx`.`wm_score_package`.`send_package_dept_id` AS `send_package_dept_id`,`hnqzx`.`wm_score_package`.`dept_id` AS `dept_id`,`hnqzx`.`wm_score_package`.`del_flag` AS `del_flag`,`hnqzx`.`wm_score_package`.`enable_flag` AS `enable_flag`,`hnqzx`.`wm_score_package`.`tenant_id` AS `tenant_id`,`hnqzx`.`wm_score_package`.`create_time` AS `create_time`,`hnqzx`.`wm_score_package`.`create_user` AS `create_user`,`hnqzx`.`wm_score_package`.`update_time` AS `update_time`,`hnqzx`.`wm_score_package`.`update_user` AS `update_user`,`hnqzx`.`wm_score_package`.`receive_date` AS `receive_date`,`hnqzx`.`wm_score_package`.`is_conduct` AS `is_conduct`,`bb`.`ytj` AS `ytj`,`bb`.`ysh` AS `ysh`,`bb`.`dsh` AS `dsh`,3 AS `t_type` from (`hnqzx`.`wm_score_package` left join (select `hnqzx`.`wm_task`.`score_package_drug_id` AS `score_package_id`,sum(if((`hnqzx`.`wm_task`.`task_status` > 0),`hnqzx`.`wm_task`.`score`,0)) AS `ytj`,sum(if((`hnqzx`.`wm_task`.`task_status` = 3),`hnqzx`.`wm_task`.`score`,0)) AS `ysh`,sum(if((`hnqzx`.`wm_task`.`task_status` = 2),`hnqzx`.`wm_task`.`score`,0)) AS `dsh` from `hnqzx`.`wm_task` where (`hnqzx`.`wm_task`.`score_package_id` is not null) group by `hnqzx`.`wm_task`.`score_package_drug_id`) `bb` on((`hnqzx`.`wm_score_package`.`id` = `bb`.`score_package_id`))) where ((`hnqzx`.`wm_score_package`.`del_flag` = '0') and (`hnqzx`.`wm_score_package`.`enable_flag` = '0') and (`hnqzx`.`wm_score_package`.`agent_type_id` = 1))) `ccc`
    53. 

  53. order by `ccc`.`agent_type_id`,`ccc`.`create_time` desc
    54. 

  54. 

  55. 

  56. INSERT INTO `hnqzx`.`sys_dict`(`type`, `description`, `create_time`, `update_time`, `remarks`, `system`, `del_flag`, `tenant_id`) VALUES ('invoice_category', '发票类目', '2020-12-03 13:59:26', '2020-12-03 13:59:26', '技术咨询划服务  市场推⼴服务  其他咨询服务 现场辅助服务', '1', '0', 1);
    57. 

  57. 

  58. INSERT INTO `hnqzx`.`sys_dict_item`( `dict_id`, `value`, `label`, `type`, `description`, `sort`, `create_time`, `update_time`, `remarks`, `del_flag`, `tenant_id`) VALUES ( 50, '77', '技术咨询划服务', 'invoice_category', '技术咨询划服务', 0, '2020-12-03 14:01:38', '2020-12-03 14:01:38', '', '0', 1);
    59. 

  59. INSERT INTO `hnqzx`.`sys_dict_item`( `dict_id`, `value`, `label`, `type`, `description`, `sort`, `create_time`, `update_time`, `remarks`, `del_flag`, `tenant_id`) VALUES ( 50, '30', '市场推⼴服务', 'invoice_category', '市场推⼴服务', 1, '2020-12-03 14:02:46', '2020-12-03 14:02:46', '', '0', 1);
    60. 

  60. INSERT INTO `hnqzx`.`sys_dict_item`( `dict_id`, `value`, `label`, `type`, `description`, `sort`, `create_time`, `update_time`, `remarks`, `del_flag`, `tenant_id`) VALUES ( 50, '70', '其他咨询服务', 'invoice_category', '其他咨询服务', 2, '2020-12-03 14:03:02', '2020-12-03 14:03:02', '', '0', 1);
    61. 

  61. INSERT INTO `hnqzx`.`sys_dict_item`( `dict_id`, `value`, `label`, `type`, `description`, `sort`, `create_time`, `update_time`, `remarks`, `del_flag`, `tenant_id`) VALUES ( 50, '203', '现场辅助服务', 'invoice_category', '现场辅助服务', 3, '2020-12-03 14:03:14', '2020-12-03 14:03:14', '', '0', 1);
    62. 

  62. 

  63. 

  64. 

  65. INSERT INTO `hnqzx`.`sys_dict`( `type`, `description`, `create_time`, `update_time`, `remarks`, `system`, `del_flag`, `tenant_id`) VALUES ('settle_status', '结算状态', '2020-12-04 14:52:03', '2020-12-04 14:52:03', '1结算成功，2结算失败，3已提交，4未结算', '1', '0', 1);
    66. 

  66. 

  67. INSERT INTO `hnqzx`.`sys_dict_item`( `dict_id`, `value`, `label`, `type`, `description`, `sort`, `create_time`, `update_time`, `remarks`, `del_flag`, `tenant_id`) VALUES ( 51, '1', '结算成功', 'settle_status', '结算成功', 0, '2020-12-04 14:52:49', '2020-12-04 14:52:49', '', '0', 1);
    68. 

  68. INSERT INTO `hnqzx`.`sys_dict_item`( `dict_id`, `value`, `label`, `type`, `description`, `sort`, `create_time`, `update_time`, `remarks`, `del_flag`, `tenant_id`) VALUES ( 51, '2', '结算失败', 'settle_status', '结算失败', 1, '2020-12-04 14:53:04', '2020-12-04 14:53:04', '', '0', 1);
    69. 

  69. INSERT INTO `hnqzx`.`sys_dict_item`( `dict_id`, `value`, `label`, `type`, `description`, `sort`, `create_time`, `update_time`, `remarks`, `del_flag`, `tenant_id`) VALUES ( 51, '3', '已提交', 'settle_status', '已提交', 2, '2020-12-04 14:53:20', '2020-12-04 14:53:20', '', '0', 1);
    70. 

  70. INSERT INTO `hnqzx`.`sys_dict_item`( `dict_id`, `value`, `label`, `type`, `description`, `sort`, `create_time`, `update_time`, `remarks`, `del_flag`, `tenant_id`) VALUES ( 51, '4', '未结算', 'settle_status', '未结算', 3, '2020-12-04 14:53:34', '2020-12-04 14:53:34', '', '0', 1);
    71. 

  71. 

  72. 

  73.