shebao-server/docs/sql/db/shebao_admin_data20151005_new.sql

/*
SQLyog Ultimate v11.11 (64 bit)
MySQL - 5.6.26 : Database - shebao_admin
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`shebao_admin` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `shebao_admin`;

/*Table structure for table `sys_privilege` */

DROP TABLE IF EXISTS `sys_privilege`;

CREATE TABLE `sys_privilege` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '权限名称',
  `url` varchar(255) DEFAULT NULL COMMENT 'url地址',
  `description` varchar(255) DEFAULT '' COMMENT '描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系统权限表';

/*Data for the table `sys_privilege` */

/*Table structure for table `sys_role` */

DROP TABLE IF EXISTS `sys_role`;

CREATE TABLE `sys_role` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
  `enabled` varchar(1) NOT NULL DEFAULT '' COMMENT '状态',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='系统角色表';

/*Data for the table `sys_role` */

insert  into `sys_role`(`id`,`name`,`enabled`) values (1,'ROLE_USER','1'),(2,'ROLE_SUPER_ADMIN ','1');

/*Table structure for table `sys_role_privilege` */

DROP TABLE IF EXISTS `sys_role_privilege`;

CREATE TABLE `sys_role_privilege` (
  `role_id` varchar(20) NOT NULL DEFAULT '' COMMENT '角色ID',
  `privilege_id` varchar(20) NOT NULL DEFAULT '' COMMENT '权限ID',
  PRIMARY KEY (`role_id`,`privilege_id`),
  KEY `fk_role_privilege` (`privilege_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系统权限和系统角色关联表';

/*Data for the table `sys_role_privilege` */

/*Table structure for table `sys_user` */

DROP TABLE IF EXISTS `sys_user`;

CREATE TABLE `sys_user` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `name` varchar(30) NOT NULL DEFAULT '' COMMENT '名称',
  `password` varchar(50) NOT NULL DEFAULT '' COMMENT '密码',
  `enabled` varchar(1) NOT NULL DEFAULT '' COMMENT '状态',
  PRIMARY KEY (`id`),
  UNIQUE KEY `INDEX_USER_NAME` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='系统用户表';

/*Data for the table `sys_user` */

insert  into `sys_user`(`id`,`name`,`password`,`enabled`) values (1,'admin','d2f69715e1d2f5be2dd331099875fdfd','1'),(2,'shanpin','c4a6fd8a749cf1fcf6be3638477b7d62','1');

/*Table structure for table `sys_user_detail` */

DROP TABLE IF EXISTS `sys_user_detail`;

CREATE TABLE `sys_user_detail` (
  `user_id` varchar(20) NOT NULL DEFAULT '' COMMENT 'ID',
  `user_name` varchar(50) NOT NULL COMMENT '用户名称',
  `nick_name` varchar(50) DEFAULT NULL COMMENT '昵称',
  `real_name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `user_mail` varchar(100) DEFAULT '' COMMENT '邮件',
  `user_mobile` varchar(50) DEFAULT NULL COMMENT '手机',
  `modify_time` datetime NOT NULL DEFAULT '2014-12-12 00:00:00' COMMENT '修改时间',
  `user_lasttime` datetime DEFAULT NULL COMMENT '最后登录时间',
  `user_lastip` varchar(50) DEFAULT NULL COMMENT '最后登录IP',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_unique_name` (`user_name`),
  UNIQUE KEY `user_mail` (`user_mail`),
  UNIQUE KEY `user_unique_mobile` (`user_mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系统用户详细表';

/*Data for the table `sys_user_detail` */

/*Table structure for table `sys_user_role` */

DROP TABLE IF EXISTS `sys_user_role`;

CREATE TABLE `sys_user_role` (
  `user_id` varchar(20) NOT NULL DEFAULT '',
  `role_id` varchar(20) NOT NULL DEFAULT '' COMMENT '角色ID',
  PRIMARY KEY (`role_id`,`user_id`),
  KEY `fk_usr_role_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系统用户和系统角色关联表';

/*Data for the table `sys_user_role` */

insert  into `sys_user_role`(`user_id`,`role_id`) values ('1','1'),('1','2'),('2','1');

/*Table structure for table `t_sb_buy_order_ext` */

DROP TABLE IF EXISTS `t_sb_buy_order_ext`;

CREATE TABLE `t_sb_buy_order_ext` (
  `order_no` varchar(32) NOT NULL COMMENT '订单号',
  `note` varchar(200) DEFAULT NULL COMMENT '备注'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_sb_buy_order_ext` */

insert  into `t_sb_buy_order_ext`(`order_no`,`note`) values ('030000000106201602','有待审核'),('020000000106201512','有待审核'),('030000000104201512','因政策调整已退款'),('020000000099201512','暂不提交'),('030000000110201601','haha');

/*Table structure for table `t_sb_employee` */

DROP TABLE IF EXISTS `t_sb_employee`;

CREATE TABLE `t_sb_employee` (
  `order_no` varchar(32) NOT NULL COMMENT '此次的支付订单号t_sb_buy',
  `pay_uid` int(10) unsigned NOT NULL COMMENT '此次支付的用户标识',
  `id_card` varchar(20) NOT NULL COMMENT '身份证号码',
  `hukou_type` tinyint(3) unsigned NOT NULL COMMENT '户籍性质',
  `city_code` smallint(5) unsigned NOT NULL COMMENT '城市代号',
  `pay_for` tinyint(4) NOT NULL COMMENT '社保、公积金',
  `pay_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0首次、其它',
  `for_month` int(11) NOT NULL COMMENT '参保、缴存的月份',
  `gen_time` datetime DEFAULT NULL,
  `any_reason` text COMMENT '失败原因',
  `recon_result` tinyint(4) DEFAULT '0' COMMENT '核对结果',
  PRIMARY KEY (`order_no`),
  KEY `employee_month_index` (`for_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='代理员工，所有成功付款的参保、缴存用户';

/*Data for the table `t_sb_employee` */

insert  into `t_sb_employee`(`order_no`,`pay_uid`,`id_card`,`hukou_type`,`city_code`,`pay_for`,`pay_type`,`for_month`,`gen_time`,`any_reason`,`recon_result`) values ('010000000099201510163287',1000001,'330322197709013638',1,1001,1,0,201510,'2015-10-04 13:57:37',NULL,0),('020000000100201510',1000001,'441900199112130735',1,1001,2,0,201510,'2015-10-04 13:57:37',NULL,0),('020000000101201510',1000001,'372324199209294438',1,1001,2,0,201510,'2015-10-04 13:57:37',NULL,0),('020000000102201510',1000001,'440682199001018888',1,1001,2,0,201510,'2015-10-04 13:57:37',NULL,0),('020000000104201510',1000001,'440682199001018889',1,1001,2,0,201510,'2015-10-04 13:57:37',NULL,0),('020000000106201511',1000001,'111111111188888888',1,1001,2,0,201511,'2015-10-04 13:57:37',NULL,0),('030000000100201510',1000001,'441900199112130735',1,1001,3,0,201510,'2015-10-04 13:57:37',NULL,0),('030000000100201603',1000001,'441900199112130735',1,1001,3,0,201603,'2015-10-04 13:57:37',NULL,0),('030000000101201601',1000001,'372324199209294438',1,1001,1,0,201601,'2015-10-04 13:57:37',NULL,0),('030000000102201510',1000001,'440682199001018888',3,1001,3,0,201510,'2015-10-04 13:57:37',NULL,0),('030000000104201510',1000001,'440682199001018889',2,1001,3,0,201510,'2015-10-04 13:57:37',NULL,0),('030000000105201510',1000001,'444444444455555555',1,1001,3,0,201510,'2015-10-04 13:57:37',NULL,0),('030000000105201601',1000001,'444444444455555555',3,1001,3,0,201601,'2015-10-04 13:57:37',NULL,0),('030000000106201602',1000001,'111111111188888888',2,1001,1,0,201602,'2015-10-04 13:57:37',NULL,0),('B010000000109201512',1000001,'44068219910103258x',2,1001,1,0,201512,'2015-10-04 13:57:37',NULL,0),('B010000000110201510',1000001,'44444444444444444',3,1001,1,0,201510,'2015-10-04 13:57:37',NULL,0),('B010000000110201602',1000001,'44444444444444444',1,1001,1,0,201602,'2015-10-04 13:57:37',NULL,0),('B010000000111201510',1000001,'444444444444444444',1,1001,1,0,201510,'2015-10-04 13:57:37',NULL,0),('B010000000113201510',1000004,'440682199001015678',1,1001,1,0,201510,'2015-10-04 13:57:37',NULL,0),('B010000000113201602',1000004,'440682199001015678',2,1008,1,0,201602,'2015-10-04 13:57:37',NULL,0),('B010000000117201510',1000001,'123456789012345678',2,1003,1,0,201510,'2015-10-04 13:57:37',NULL,0),('B010000000122201510',1000001,'222222222222222222',2,1002,1,0,201510,'2015-10-04 13:57:37',NULL,0),('B010000000128201510',1000001,'5555555555555555',1,1003,1,0,201510,'2015-10-05 01:00:00',NULL,0),('B010000000129201510',1000001,'130425199306283453',1,1011,1,0,201510,'2015-10-05 01:00:00',NULL,0),('B020000000099201512',1000001,'330322197709013638',1,1001,2,0,201512,'2015-10-04 13:57:37',NULL,0),('B020000000101201512',1000001,'372324199209294438',1,1001,2,0,201512,'2015-10-04 13:57:37',NULL,0),('B020000000109201512',1000001,'44068219910103258x',1,1001,2,0,201512,'2015-10-04 13:57:37',NULL,0),('B020000000113201601',1000004,'440682199001015678',1,1007,2,0,201601,'2015-10-04 13:57:37',NULL,0),('B020000000113201603',1000004,'440682199001015678',1,1008,2,0,201603,'2015-10-04 13:57:37',NULL,0),('B020000000114201511',1000001,'334235796320642254',1,1002,2,0,201511,'2015-10-04 13:57:37',NULL,0),('B020000000115201510',1000001,'46996346794673467',1,1001,2,0,201510,'2015-10-04 13:57:37',NULL,0),('B020000000116201512',1000001,'123456789123456789',1,1001,2,0,201512,'2015-10-04 13:57:37',NULL,0),('B020000000116201602',1000001,'123456789123456789',1,1001,2,0,201602,'2015-10-04 13:57:37',NULL,0),('B020000000117201601',1000001,'123456789012345678',1,1004,2,0,201601,'2015-10-04 13:57:37',NULL,0),('B020000000117201603',1000001,'123456789012345678',1,1004,2,0,201603,'2015-10-04 13:57:37',NULL,0),('B020000000122201511',1000001,'222222222222222222',1,1002,2,0,201511,'2015-10-04 13:57:37',NULL,0),('B020000000123201510',1000001,'123456789011111122',1,1001,2,0,201510,'2015-10-05 01:00:00',NULL,0),('B020000000124201510',1000002,'342546425542453385',1,1001,2,0,201510,'2015-10-05 01:00:00',NULL,0),('B020000000128201510',1000001,'5555555555555555',1,1003,2,0,201510,'2015-10-05 01:00:00',NULL,0),('B020000000129201510',1000001,'130425199306283453',1,1301,2,0,201510,'2015-10-05 01:00:00',NULL,0),('B030000000099201511',1000001,'330322197709013638',1,1001,2,0,201511,'2015-10-04 13:57:37',NULL,0),('B030000000108201512',1000001,'440682199001015678',1,1001,3,0,201512,'2015-10-04 13:57:37',NULL,0),('B030000000109201603',1000001,'44068219910103258x',1,1001,3,0,201603,'2015-10-05 01:00:00',NULL,0),('B030000000110201601',1000001,'44444444444444444',2,1001,3,0,201601,'2015-10-04 13:57:37',NULL,0),('B030000000112201510',1000001,'555555555555555555',2,1004,3,0,201510,'2015-10-04 13:57:37',NULL,0),('B030000000113201511',1000004,'440682199001015678',1,1003,2,0,201511,'2015-10-04 13:57:37',NULL,0),('B030000000114201510',1000001,'334235796320642254',1,1002,3,0,201510,'2015-10-04 13:57:37',NULL,0),('B030000000114201602',1000001,'334235796320642254',1,1008,3,0,201602,'2015-10-04 13:57:37',NULL,0),('B030000000117201511',1000001,'123456789012345678',3,1002,2,0,201511,'2015-10-04 13:57:37',NULL,0),('B030000000117201602',1000001,'123456789012345678',1,1004,1,0,201602,'2015-10-04 13:57:37',NULL,0),('B030000000118201510',1000001,'123456789011111111',1,1004,3,0,201510,'2015-10-04 13:57:37',NULL,0),('B030000000118201603',1000001,'123456789011111111',1,1001,3,0,201603,'2015-10-05 01:00:00',NULL,0),('B030000000122201601',1000001,'222222222222222222',1,1004,3,0,201601,'2015-10-04 13:57:37',NULL,0),('B030000000122201604',1000001,'222222222222222222',1,1004,3,0,201604,'2015-10-04 13:57:37',NULL,0),('B030000000123201512',1000001,'123456789011111122',1,1003,1,0,201512,'2015-10-05 01:00:00',NULL,0),('B030000000125201510',1000002,'342564286438524583',1,1001,3,0,201510,'2015-10-05 01:00:00',NULL,0),('B030000000125201512',1000002,'342564286438524583',1,1001,3,0,201512,'2015-10-05 01:00:00',NULL,0),('B030000000126201510',1000002,'134254679976438645',1,1001,3,0,201510,'2015-10-05 01:00:00',NULL,0),('B030000000128201601',1000001,'5555555555555555',1,1001,1,0,201601,'2015-10-05 01:00:00',NULL,0);

/*Table structure for table `t_sb_stop` */

DROP TABLE IF EXISTS `t_sb_stop`;

CREATE TABLE `t_sb_stop` (
  `order_no` varchar(32) NOT NULL COMMENT '订单号',
  `buy_no` varchar(32) NOT NULL COMMENT '方便查找停保用户资料',
  `pay_uid` int(10) unsigned NOT NULL COMMENT '支付人ID',
  `id_card` varchar(20) NOT NULL COMMENT '身份证',
  `hukou_type` tinyint(3) unsigned NOT NULL COMMENT '户籍性质',
  `city_code` smallint(5) unsigned NOT NULL COMMENT '城市代码',
  `stop_for` tinyint(3) unsigned NOT NULL COMMENT '停的类型',
  `stop_type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '到期停保、其它',
  `stop_month` mediumint(8) unsigned NOT NULL COMMENT '停的月份',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态',
  `gen_time` datetime NOT NULL,
  `any_reason` text COMMENT '失败原因',
  PRIMARY KEY (`order_no`),
  KEY `stop_uid_idx` (`pay_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='减员表';

/*Data for the table `t_sb_stop` */

insert  into `t_sb_stop`(`order_no`,`buy_no`,`pay_uid`,`id_card`,`hukou_type`,`city_code`,`stop_for`,`stop_type`,`stop_month`,`status`,`gen_time`,`any_reason`) values ('020000000100201510','020000000100201510',1000001,'441900199112130735',2,1001,1,1,201510,2,'2015-10-02 10:07:51',NULL),('020000000101201512','B020000000101201512',1000001,'372324199209294438',1,1001,2,2,201510,1,'2015-10-02 10:10:35',NULL),('030000000101201601','030000000101201601',1000001,'372324199209294438',3,1001,1,1,201509,1,'2015-10-02 17:42:24',NULL);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;