SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for wm_payoff
-- ----------------------------
DROP TABLE IF EXISTS `wm_payoff`;
CREATE TABLE `wm_payoff` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `score_name` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '积分包名称',
  `id_number` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '身份证号',
  `settle_status` int(2) DEFAULT NULL COMMENT '结算状态',
  `cause_failure` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '失败原因',
  `cert_status` int(2) DEFAULT NULL COMMENT '实名认证状态',
  `submitted_value` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '已提交积分值',
  `settle_amount` decimal(30,8) DEFAULT NULL COMMENT '实际结算金额',
  `discount` decimal(11,4) DEFAULT NULL COMMENT '折扣',
  `score_id` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL  COMMENT '关联积分包id',
  `settle_people` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '结算人',
  `settle_time` datetime DEFAULT NULL COMMENT '结算时间',
  `statement_no` varchar(64) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '结算单号',
  `export1` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '备用1',
  `export2` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '备用2',
  `export3` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '备用3',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='结算表';

SET FOREIGN_KEY_CHECKS = 1;


ALTER TABLE `wm_score_package` MODIFY `invoice_category` int(11) ;

ALTER TABLE `sys_dept` ADD `app_id` VARCHAR (32)  COMMENT '' ;
ALTER TABLE `sys_dept` ADD `app_secret` VARCHAR (32)  COMMENT '' ;
ALTER TABLE `sys_dept` ADD `rsa_public_key` VARCHAR(256) DEFAULT NULL  COMMENT '';
ALTER TABLE `sys_dept` ADD `query_url` VARCHAR(32) DEFAULT NULL  COMMENT '';



create or replace view view_wm_socre_package_total as select `ccc`.`id` AS `id`,
`ccc`.`invoice_category` AS `invoice_category`,
`ccc`.`settle_status` AS `settle_status`,
`ccc`.`settle_amount` AS `settle_amount`,
`ccc`.`discount` AS `discount`,
`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`,
`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`,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`
order by `ccc`.`agent_type_id`,`ccc`.`create_time` desc


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

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



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

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