shebao-server/docs/sql/db/shebao_admin_data20151012_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 AUTO_INCREMENT=53 DEFAULT CHARSET=utf8 COMMENT='系统权限表';

/*Data for the table `sys_privilege` */

insert  into `sys_privilege`(`id`,`name`,`url`,`description`) values (1,'SBA','/sba/','后台账号登录'),(2,'SB_LIST','/sba/sb/list','社保参数查询列表'),(3,'FUND_LIST','/sba/fund/list','公积金参数查询列表'),(4,'CHARGE_QUERYLIST','/sba/charge/querylist','服务费查询列表'),(5,'SYSTEM_USER_QUERYPAGEBY','/sba/system/user/querypageby','查询系统账号'),(6,'SYSTEM_ROLE_QUERYPAGEBY','/sba/system/role/querypageby','查询系统角色'),(7,'SYSTEM_PRIVILEGE_QUERYPAGEBY','/sba/system/privilege/querypageby','查询系统权限'),(9,'SYSTEM_USER_DELETE','/sba/system/user/delete','删除系统账号'),(10,'SYSTEM_USER_ADD','/sba/system/user/add','添加系统账号'),(11,'SYSTEM_USER_SHOWPASS','/sba/system/user/showpass','修改账号密码显示'),(12,'SYSTEM_USER_UPDATEPASS','/sba/system/user/updatepass','修改账号密码'),(13,'SYSTEM_USER_QUERYROLELIST','/sba/system/user/queryrolelist','授权账号角色查询页面'),(14,'SYSTEM_USER_GRANTROLE','/sba/system/user/grantrole','授权账号角色提交'),(15,'SYSTEM_ROLE_ADD','/sba/system/role/add','添加系统角色'),(16,'SYSTEM_ROLE_DELETE','/sba/system/role/delete','删除系统角色'),(17,'SYSTEM_ROLE_QUERYPRIVILEGELIST','/sba/system/role/queryprivilegelist','角色授权查询'),(18,'SYSTEM_ROLE_GRANTPRIVILEGE','/sba/system/role/grantprivilege','修改角色授权提交'),(19,'SYSTEM_PRIVILEGE_ADD','/sba/system/privilege/add','添加系统权限'),(20,'SYSTEM_PRIVILEGE_DELETE','/sba/system/privilege/delete','删除系统权限'),(22,'USER_QUERYPAGEBY','/sba/user/querypageby','用户查询列表'),(23,'EXCEL_EXPORTUSER','/sba/excel/exportuser','用户批量导出EXCEL'),(24,'EMPLOYEE_QUERYAGENCYPAGE','/sba/employee/queryagencypage','代理员工查询列表'),(25,'EMPLOYEE_UPDATERECON','/sba/employee/updaterecon','代理员工状态修改'),(26,'EMPLOYEE_BATCHRECON','/sba/employee/batchrecon','代理员工批量状态修改'),(27,'EMPLOYEE_QUERYAGENCYDETAILPAGE','/sba/employee/queryagencydetailpage','代理员工按月查询列表'),(28,'EXCEL_EXPORTAGENCYDETAIL','/sba/excel/exportagencydetail','代理员工批量导出EXCEL'),(29,'EXCEL_IMPORTEMPLOYEEEXCEL','/sba/excel/importEmployeeExcel','代理员工导入核对EXCEL'),(30,'EMPLOYEE_QUERYCHANGEDETAILPAGE','/sba/employee/querychangedetailpage','增减员查询增员列表'),(31,'EMPLOYEE_EXCEL_EXPORTCHANGECOMING','/sba/employee/excel/exportchangecoming','增员批量导出EXCEL'),(32,'EXCEL_IMPORTEMPLOYEEADDEXCEL','/sba/excel/importEmployeeAddExcel','导入增员卡号EXCEL'),(33,'EMPLOYEE_UPDATESBCARD','/sba/employee/updatesbcard','编辑增员卡号'),(34,'EMPLOYEE_UPDATEREASON','/sba/employee/updatereason','编辑增员失败原因'),(35,'EMPLOYEE_CHECKIDCARD','/sba/employee/checkidcard','查看增员身份证'),(36,'EMPLOYEE_DOWNLOADFILE','/sba/employee/downloadfile','增减员打包下载身份证ZIP'),(37,'EXCEL_EXPORTCHANGEGOING','/sba/excel/exportchangegoing','减员批量导出EXCEL'),(38,'ORDER_QUERYPAGEBY','/sba/order/querypageby','订单查询列表'),(39,'EXCEL_EXPORTORDER','/sba/excel/exportorder','订单批量导出EXCEL'),(40,'ORDER_QUERYDETAIL','/sba/order/querydetail','订单详情修改显示'),(41,'ORDER_MODIFY','/sba/order/modify','订单详情修改'),(42,'SB_2MODIFY','/sba/sb/2modify','社保参数增加'),(43,'SB_MOD_MAIN','/sba/sb/mod/main','社保参数提交修改'),(44,'FUND_2MODIFY','/sba/fund/2modify','公积金参数修改'),(45,'FUND_MODIFY','/sba/fund/modify','公积金参数提交修改'),(46,'NEWS_QUERYLIST','/sba/news/querylist','群发消息查询消息列表'),(47,'NEWS_ADD','/sba/news/add','群发消息保存'),(48,'NEWS_DELETE','/sba/news/delete','群发消息删除'),(49,'NEWS_BATCHDELETE','/sba/news/batchdelete','群发消息批量删除消息'),(50,'USER_QUERYPAGE','/sba/user/querypage','用户查询列表'),(51,'EMPLOYEE_QUERYCHANGEPAGE','/sba/employee/querychangepage','增减员查询列表'),(52,'NEWS_LIST','/sba/news/list','群发消息查询消息列表');

/*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=14 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'),(3,'ROLE_ADMIN ','1'),(4,'ROLE_ADMIN_SB','1'),(5,'ROLE_ADMIN_FUND','1'),(6,'ROLE_ADMIN_CHARGE','1'),(7,'ROLE_ADMIN_ORDER','1'),(8,'ROLE_ADMIN_EMPLOYEE','1'),(9,'ROLE_ADMIN_USER','1'),(10,'ROLE_ADMIN_SYSTEM_USER','1'),(11,'ROLE_ADMIN_SYSTEM_ROLE','1'),(12,'ROLE_ADMIN_SYSTEM_PRIVILEGE','1'),(13,'ROLE_ADMIN_NEWS','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` */

insert  into `sys_role_privilege`(`role_id`,`privilege_id`) values ('1','1'),('10','1'),('11','1'),('12','1'),('13','1'),('2','1'),('3','1'),('4','1'),('5','1'),('6','1'),('7','1'),('8','1'),('9','1'),('10','10'),('10','11'),('10','12'),('10','13'),('10','14'),('11','15'),('11','16'),('11','17'),('11','18'),('12','19'),('2','2'),('4','2'),('12','20'),('3','22'),('9','22'),('3','23'),('9','23'),('3','24'),('8','24'),('3','25'),('8','25'),('3','26'),('8','26'),('3','27'),('8','27'),('3','28'),('8','28'),('3','29'),('8','29'),('2','3'),('5','3'),('3','30'),('8','30'),('3','31'),('8','31'),('3','32'),('8','32'),('3','33'),('8','33'),('3','34'),('8','34'),('3','35'),('8','35'),('3','36'),('8','36'),('3','37'),('8','37'),('3','38'),('7','38'),('3','39'),('7','39'),('2','4'),('6','4'),('3','40'),('7','40'),('3','41'),('7','41'),('4','42'),('4','43'),('5','44'),('5','45'),('13','46'),('3','46'),('13','47'),('3','47'),('13','48'),('3','48'),('13','49'),('3','49'),('10','5'),('3','50'),('9','50'),('3','51'),('8','51'),('13','52'),('3','52'),('11','6'),('12','7'),('10','9');

/*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=4 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','d432eeff84a49601a87eaf1d80cf2d14','1'),(3,'super','af8dae1f3ae051b9388aa66f8b1f469c','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','3'),('1','4'),('1','5'),('1','6'),('2','1'),('2','13'),('2','7'),('2','8'),('2','9'),('3','1'),('3','2'),('3','3');

/*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'),('030000000121201510','1'),('010000000130201512','1');

/*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 ('010000000107201510',1000004,'440682199001016789',1,1008,1,0,201510,'2015-10-12 01:00:00',NULL,0),('010000000127201511',1000006,'440222198404060315',1,1001,1,0,201511,'2015-10-10 01:00:00',NULL,0),('010000000134201608',1000004,'440682198503083561',1,1001,1,0,201608,'2015-10-09 14:48:31',NULL,1),('010000000134201609',1000004,'440682198503083561',1,1001,1,2,201609,'2015-10-09 14:50:44',NULL,1),('010000000135201609',1000004,'440682198503083522',1,1001,1,0,201609,'2015-10-09 15:02:40',NULL,0),('010000000135201610',1000004,'440682198503083522',1,1001,1,2,201610,'2015-10-09 15:03:18',NULL,0),('010000000136201609',1000004,'440682198503083555',1,1001,1,0,201609,'2015-10-09 15:16:43',NULL,0),('010000000136201610',1000004,'440682198503083555',1,1001,1,2,201610,'2015-10-09 15:17:55',NULL,0),('010000000137201609',1000004,'440682198503083566',1,1001,1,0,201609,'2015-10-09 15:23:58',NULL,0),('010000000137201611',1000004,'440682198503083566',1,1001,1,0,201611,'2015-10-09 15:41:47',NULL,0),('010000000142201510',1000007,'43072319910929641X',1,1001,1,0,201510,'2015-10-10 01:00:00',NULL,0),('010000000144201510',1000004,'360124198707251511',4,1005,1,0,201510,'2015-10-11 01:00:00',NULL,0),('020000000107201510',1000004,'440682199001016789',1,1301,2,0,201510,'2015-10-11 01:00:00',NULL,0),('020000000134201609',1000004,'440682198503083561',1,1001,2,0,201609,'2015-10-09 14:56:45',NULL,0),('020000000134201610',1000004,'440682198503083561',1,1001,2,2,201610,'2015-10-09 14:57:02',NULL,0),('020000000135201609',1000004,'440682198503083522',1,1001,2,0,201609,'2015-10-09 15:02:40',NULL,0),('020000000135201610',1000004,'440682198503083522',1,1001,2,2,201610,'2015-10-09 15:03:18',NULL,0),('020000000136201609',1000004,'440682198503083555',1,1001,2,0,201609,'2015-10-09 15:16:43',NULL,0),('020000000136201611',1000004,'440682198503083555',1,1001,2,0,201611,'2015-10-09 15:19:34',NULL,0),('020000000137201609',1000004,'440682198503083566',1,1001,2,0,201609,'2015-10-09 15:23:58',NULL,0),('020000000137201610',1000004,'440682198503083566',1,1001,2,2,201610,'2015-10-09 15:26:08',NULL,0),('020000000142201511',1000007,'43072319910929641X',1,1001,2,0,201511,'2015-10-10 01:00:00',NULL,0),('020000000144201512',1000004,'360124198707251511',2,1008,2,0,201512,'2015-10-11 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 ('STOP010000000134201610','B010000000134201609',1000004,'440682198503083561',1,1001,1,0,201610,0,'2015-10-09 14:52:13',NULL),('STOP010000000135201611','B030000000135201610',1000004,'440682198503083522',1,1001,1,0,201611,0,'2015-10-09 15:03:42',NULL),('STOP010000000136201611','B010000000136201610',1000004,'440682198503083555',1,1001,1,0,201611,0,'2015-10-09 15:20:03',NULL),('STOP010000000137201610','B030000000137201609',1000004,'440682198503083566',1,1001,1,0,201610,0,'2015-10-09 15:26:17',NULL),('STOP010000000137201612','B010000000137201611',1000004,'440682198503083566',1,1001,1,0,201612,0,'2015-10-09 15:44:44',NULL),('STOP020000000134201611','B020000000134201610',1000004,'440682198503083561',1,1001,2,0,201611,0,'2015-10-09 14:57:26',NULL),('STOP020000000135201611','B030000000135201610',1000004,'440682198503083522',1,1001,2,0,201611,0,'2015-10-09 15:03:42',NULL),('STOP020000000136201610','B030000000136201609',1000004,'440682198503083555',1,1001,2,0,201610,0,'2015-10-09 15:18:30',NULL),('STOP020000000136201612','B020000000136201611',1000004,'440682198503083555',1,1001,2,0,201612,0,'2015-10-09 15:20:54',NULL),('STOP020000000137201611','B020000000137201610',1000004,'440682198503083566',1,1001,2,0,201611,0,'2015-10-09 15:27:16',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 */;