shebao-server/docs/sql/shebao20150912.sql

/*
Navicat MySQL Data Transfer

Source Server         : 192.168.2.199
Source Server Version : 50626
Source Host           : 192.168.2.199:3306
Source Database       : shebao

Target Server Type    : MYSQL
Target Server Version : 50626
File Encoding         : 65001

Date: 2015-09-12 15:18:16
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `t_city_news`
-- ----------------------------
DROP TABLE IF EXISTS `t_city_news`;
CREATE TABLE `t_city_news` (
  `c_n_id` int(30) unsigned NOT NULL AUTO_INCREMENT,
  `cid` int(30) DEFAULT NULL,
  `nid` int(30) DEFAULT NULL,
  PRIMARY KEY (`c_n_id`)
) ENGINE=InnoDB AUTO_INCREMENT=304 DEFAULT CHARSET=utf8;


-- ----------------------------
-- Table structure for `t_news`
-- ----------------------------
DROP TABLE IF EXISTS `t_news`;
CREATE TABLE `t_news` (
  `nid` int(30) unsigned NOT NULL AUTO_INCREMENT COMMENT '标识ID',
  `title` varchar(255) DEFAULT NULL COMMENT '标题',
  `content` text COMMENT '内容',
  `publish_time` datetime DEFAULT NULL,
  PRIMARY KEY (`nid`)
) ENGINE=InnoDB AUTO_INCREMENT=220 DEFAULT CHARSET=utf8;


-- ----------------------------
-- Table structure for `t_sb_buy`
-- ----------------------------
DROP TABLE IF EXISTS `t_sb_buy`;
CREATE TABLE `t_sb_buy` (
  `buy_no` varchar(32) NOT NULL DEFAULT '0' COMMENT '用于支付的订单号',
  `pay_uid` int(10) unsigned NOT NULL COMMENT '支付人ID',
  `id_card` varchar(20) NOT NULL COMMENT '身份证',
  `start_month` mediumint(8) unsigned NOT NULL COMMENT '购买的月份',
  `month_num` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '月份数',
  `pay_for` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `cid` int(10) unsigned NOT NULL COMMENT '社保数据ID',
  `hukou` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '户籍性质',
  `total_fee` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '本单总金额',
  `shebaos` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '单月社保金额',
  `funds` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '单月公积金金额',
  `charges` decimal(6,2) NOT NULL DEFAULT '0.00' COMMENT '单月手续费',
  `card_charges` decimal(6,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '新办卡服务费',
  `pay_no` varchar(128) NOT NULL DEFAULT '' COMMENT '第三方支付订单号',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '支付状态',
  `gen_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`buy_no`),
  KEY `buy_month_idx` (`start_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='支付表';


-- ----------------------------
-- Table structure for `t_sb_buy_order`
-- ----------------------------
DROP TABLE IF EXISTS `t_sb_buy_order`;
CREATE TABLE `t_sb_buy_order` (
  `order_no` varchar(32) NOT NULL COMMENT '子订单号',
  `buy_no` varchar(32) NOT NULL COMMENT '购买订单号',
  `pay_uid` int(10) unsigned NOT NULL COMMENT '支付人',
  `id_card` varchar(20) NOT NULL COMMENT '身份证',
  `city_code` smallint(6) NOT NULL COMMENT '城市代码',
  `pay_for` tinyint(1) NOT NULL COMMENT '对象(社保、公积金)',
  `for_month` int(10) unsigned NOT NULL COMMENT '月份201506',
  `shebaos` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '本月参保社保',
  `funds` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '本月缴存公积金',
  `charges` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '服务费',
  `new_card` tinyint(1) NOT NULL DEFAULT '0' COMMENT '新办卡',
  `isAuto` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否自动代扣生成',
  `status` tinyint(4) NOT NULL DEFAULT '2' COMMENT '状态(已支付成功)',
  `gen_time` datetime NOT NULL COMMENT '生成时间',
  PRIMARY KEY (`order_no`),
  KEY `buy_order_uid_idx` (`pay_uid`),
  KEY `buy_order_month_idx` (`for_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='支付订单成功后的子订单';

-- ----------------------------
-- Records of t_sb_buy_order
-- ----------------------------

-- ----------------------------
-- Table structure for `t_sb_buy_payer`
-- ----------------------------
DROP TABLE IF EXISTS `t_sb_buy_payer`;
CREATE TABLE `t_sb_buy_payer` (
  `buy_no` varchar(32) NOT NULL,
  `pay_uid` int(10) unsigned NOT NULL,
  `pay_channel` varchar(32) NOT NULL,
  `buyer_id` varchar(32) DEFAULT NULL COMMENT '支付宝用户号/腾讯openID',
  `buyer_acc` varchar(32) DEFAULT NULL COMMENT '帐号、邮箱等',
  PRIMARY KEY (`buy_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='支付成功后的相关信息';

-- ----------------------------
-- Records of t_sb_buy_payer
-- ----------------------------

-- ----------------------------
-- Table structure for `t_sb_buy_user`
-- ----------------------------
DROP TABLE IF EXISTS `t_sb_buy_user`;
CREATE TABLE `t_sb_buy_user` (
  `buy_no` varchar(32) NOT NULL COMMENT '购买订单号',
  `id_card` varchar(20) NOT NULL COMMENT '身份证',
  `user_name` varchar(32) NOT NULL COMMENT '订单受益人姓名',
  `nation` varchar(20) NOT NULL COMMENT '民族',
  `gender` tinyint(1) NOT NULL COMMENT '性别',
  `phone` varchar(16) NOT NULL COMMENT '联系方式',
  `modify_time` datetime DEFAULT NULL COMMENT '修改时间',
  `recon_result` tinyint(4) NOT NULL DEFAULT '0' COMMENT '核对结果',
  PRIMARY KEY (`buy_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='参保人资料';

-- ----------------------------
-- Records of t_sb_buy_user
-- ----------------------------

-- ----------------------------
-- Table structure for `t_sb_city`
-- ----------------------------
DROP TABLE IF EXISTS `t_sb_city`;
CREATE TABLE `t_sb_city` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `city_code` smallint(6) NOT NULL COMMENT '城市编码',
  `shebao_type` varchar(32) DEFAULT '' COMMENT '社保类型(标准、豪华等)',
  `proxy_type` tinyint(3) unsigned DEFAULT NULL COMMENT '代理员工增减员方式1~8',
  `shebao_charge` decimal(6,2) DEFAULT NULL COMMENT '本城市的社保服务费',
  `shebao_card_charge` decimal(6,2) DEFAULT NULL COMMENT '本城市的代办社保卡服务费',
  `fund_charge` decimal(6,2) DEFAULT NULL COMMENT '本城市的公积金服务费',
  `package_charge` decimal(6,2) DEFAULT NULL COMMENT '社保、公积金打包服务费',
  `end_date` tinyint(4) DEFAULT NULL COMMENT '月购买日期区分',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='各城市、户藉的社保数据--主表';

-- ----------------------------
-- Records of t_sb_city
-- ----------------------------
INSERT INTO `t_sb_city` VALUES ('1', '1001', '标准', '1', '59.00', '20.00', '59.00', '99.00', '0', '2015-09-08 16:28:40');
INSERT INTO `t_sb_city` VALUES ('4', '1002', '标准', '1', '59.00', '10.00', '59.00', '90.00', '15', '2015-08-21 14:43:27');
INSERT INTO `t_sb_city` VALUES ('5', '1201', '标准', '1', '59.00', '20.00', '59.00', '90.00', '15', '2015-08-21 15:05:42');
INSERT INTO `t_sb_city` VALUES ('6', '1003', '标准', '0', '2.00', '2.00', '2.00', '2.00', '0', '2015-09-08 19:55:24');

-- ----------------------------
-- Table structure for `t_sb_city_data`
-- ----------------------------
DROP TABLE IF EXISTS `t_sb_city_data`;
CREATE TABLE `t_sb_city_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cid` int(10) unsigned NOT NULL COMMENT '见T_SB_CITY',
  `hukou_type` tinyint(4) NOT NULL COMMENT '户籍性质',
  `pension` varchar(64) DEFAULT NULL COMMENT '养老',
  `medical` varchar(64) DEFAULT NULL COMMENT '医疗',
  `lose_job` varchar(64) DEFAULT NULL COMMENT '失业保险',
  `birth` varchar(64) DEFAULT NULL COMMENT '生育保险',
  `injury` varchar(64) DEFAULT NULL COMMENT '工伤',
  `major` varchar(64) DEFAULT NULL COMMENT '住院/重大',
  `village` varchar(64) DEFAULT NULL COMMENT '农民工医疗',
  `supplement` decimal(8,2) DEFAULT NULL COMMENT '补充医疗',
  `disabled` decimal(8,2) DEFAULT NULL COMMENT '残保金',
  `fund` varchar(64) DEFAULT NULL COMMENT '公积金最低额度',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COMMENT='各城市、户藉的社保数据(ID与各户籍性质的社保数据)';

-- ----------------------------
-- Records of t_sb_city_data
-- ----------------------------
INSERT INTO `t_sb_city_data` VALUES ('8', '1', '1', '{\"min\":2408.0,\"max\":24080.0,\"e\":14.0,\"p\":8.0}', '{\"min\":3712.0,\"max\":37120.0,\"e\":8.0,\"p\":2.0}', '{\"min\":1895.0,\"max\":18950.0,\"e\":1.2,\"p\":0.5}', '{\"min\":3712.0,\"max\":37120.0,\"e\":0.85,\"p\":0.0}', '{\"min\":1895.0,\"max\":18950.0,\"e\":0.5,\"p\":0.0}', '{\"min\":6187.0,\"max\":9000.0,\"e\":0.26,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '0.00', '0.00', '{\"min\":200.0,\"max\":5331.0,\"e\":5.0,\"p\":5.0}');
INSERT INTO `t_sb_city_data` VALUES ('9', '1', '2', '{\"min\":2408.0,\"max\":24080.0,\"e\":14.0,\"p\":8.0}', '{\"min\":3712.0,\"max\":37120.0,\"e\":8.0,\"p\":2.0}', '{\"min\":1895.0,\"max\":18950.0,\"e\":1.2,\"p\":0.5}', '{\"min\":3712.0,\"max\":37120.0,\"e\":0.85,\"p\":0.0}', '{\"min\":1895.0,\"max\":18950.0,\"e\":0.5,\"p\":0.0}', '{\"min\":1234.0,\"max\":5000.0,\"e\":0.26,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '0.00', '0.00', '{\"min\":300.0,\"max\":5300.0,\"e\":5.0,\"p\":5.0}');
INSERT INTO `t_sb_city_data` VALUES ('10', '1', '3', '{\"min\":2408.0,\"max\":24080.0,\"e\":14.0,\"p\":8.0}', '{\"min\":3712.0,\"max\":37120.0,\"e\":8.0,\"p\":2.0}', '{\"min\":1895.0,\"max\":18950.0,\"e\":1.2,\"p\":0.5}', '{\"min\":3712.0,\"max\":37120.0,\"e\":0.85,\"p\":0.0}', '{\"min\":1895.0,\"max\":18950.0,\"e\":0.5,\"p\":0.0}', '{\"min\":6187.0,\"max\":9000.0,\"e\":0.26,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '0.00', '0.00', '{\"min\":303.0,\"max\":5303.0,\"e\":5.0,\"p\":5.0}');
INSERT INTO `t_sb_city_data` VALUES ('11', '1', '4', '{\"min\":2408.0,\"max\":24080.0,\"e\":14.0,\"p\":8.0}', '{\"min\":3712.0,\"max\":37120.0,\"e\":8.0,\"p\":2.0}', '{\"min\":1895.0,\"max\":18950.0,\"e\":1.2,\"p\":0.5}', '{\"min\":3712.0,\"max\":37120.0,\"e\":0.85,\"p\":0.0}', '{\"min\":1895.0,\"max\":18950.0,\"e\":0.5,\"p\":0.0}', '{\"min\":6187.0,\"max\":9000.0,\"e\":0.26,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '0.00', '0.00', '{\"min\":308.0,\"max\":5308.0,\"e\":5.0,\"p\":5.0}');
INSERT INTO `t_sb_city_data` VALUES ('12', '4', '1', '{\"min\":2408.0,\"max\":14958.0,\"e\":13.0,\"p\":8.0}', '{\"min\":3712.0,\"max\":3712.0,\"e\":5.0,\"p\":2.0}', '{\"min\":1510.0,\"max\":13920.0,\"e\":0.5,\"p\":0.5}', '{\"min\":3480.0,\"max\":3920.0,\"e\":0.9,\"p\":0.0}', '{\"min\":2784.0,\"max\":13920.0,\"e\":0.45,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '0.00', '37.02', null);
INSERT INTO `t_sb_city_data` VALUES ('13', '4', '2', '{\"min\":2408.0,\"max\":14958.0,\"e\":13.0,\"p\":8.0}', '{\"min\":3712.0,\"max\":3712.0,\"e\":5.0,\"p\":2.0}', '{\"min\":1510.0,\"max\":13920.0,\"e\":0.5,\"p\":0.5}', '{\"min\":3480.0,\"max\":3920.0,\"e\":0.9,\"p\":0.0}', '{\"min\":2784.0,\"max\":13920.0,\"e\":0.45,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '0.00', '37.02', null);
INSERT INTO `t_sb_city_data` VALUES ('14', '4', '3', '{\"min\":2408.0,\"max\":14958.0,\"e\":13.0,\"p\":8.0}', '{\"min\":3712.0,\"max\":3712.0,\"e\":5.0,\"p\":2.0}', '{\"min\":1510.0,\"max\":13920.0,\"e\":0.5,\"p\":0.5}', '{\"min\":3480.0,\"max\":3920.0,\"e\":0.9,\"p\":0.0}', '{\"min\":2784.0,\"max\":13920.0,\"e\":0.45,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '0.00', '37.02', null);
INSERT INTO `t_sb_city_data` VALUES ('15', '4', '4', '{\"min\":2408.0,\"max\":14958.0,\"e\":13.0,\"p\":8.0}', '{\"min\":3712.0,\"max\":3712.0,\"e\":5.0,\"p\":2.0}', '{\"min\":1510.0,\"max\":13920.0,\"e\":0.5,\"p\":0.5}', '{\"min\":3480.0,\"max\":3920.0,\"e\":0.9,\"p\":0.0}', '{\"min\":2784.0,\"max\":13920.0,\"e\":0.45,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '0.00', '37.02', null);
INSERT INTO `t_sb_city_data` VALUES ('16', '5', '1', '{\"min\":2415.0,\"max\":12075.0,\"e\":20.0,\"p\":8.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":8.0,\"p\":2.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":2.0,\"p\":1.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":0.7,\"p\":0.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":0.5,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '90.00', '0.00', null);
INSERT INTO `t_sb_city_data` VALUES ('17', '5', '2', '{\"min\":2415.0,\"max\":12075.0,\"e\":20.0,\"p\":8.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":8.0,\"p\":2.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":2.0,\"p\":1.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":0.7,\"p\":0.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":0.5,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '90.00', '0.00', null);
INSERT INTO `t_sb_city_data` VALUES ('18', '5', '3', '{\"min\":2415.0,\"max\":12075.0,\"e\":20.0,\"p\":8.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":8.0,\"p\":2.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":2.0,\"p\":1.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":0.7,\"p\":0.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":0.5,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '90.00', '0.00', null);
INSERT INTO `t_sb_city_data` VALUES ('19', '5', '4', '{\"min\":2415.0,\"max\":12075.0,\"e\":20.0,\"p\":8.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":8.0,\"p\":2.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":2.0,\"p\":1.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":0.7,\"p\":0.0}', '{\"min\":2415.0,\"max\":12075.0,\"e\":0.5,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '{\"min\":0.0,\"max\":0.0,\"e\":0.0,\"p\":0.0}', '90.00', '0.00', null);


-- ----------------------------
-- Table structure for `t_sb_user`
-- ----------------------------
DROP TABLE IF EXISTS `t_sb_user`;
CREATE TABLE `t_sb_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `u_id` int(10) unsigned NOT NULL COMMENT '用户标识',
  `id_card` varchar(20) NOT NULL COMMENT '身份证号码',
  `user_name` varchar(12) NOT NULL COMMENT '姓名',
  `nation` varchar(16) DEFAULT NULL COMMENT '民族',
  `gender` tinyint(4) DEFAULT NULL COMMENT '性别',
  `phone` varchar(12) DEFAULT NULL COMMENT '联系电话',
  `shebao_card` varchar(24) DEFAULT NULL COMMENT '社保卡号',
  `id_card_img` varchar(256) DEFAULT NULL COMMENT '正反面身份证图片链接',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sb_user_uniq` (`u_id`,`id_card`),
  KEY `sb_user_uid` (`u_id`)
) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8 COMMENT='参保人身份数据(一个用户标识可对应多个参保人信息)';


-- ----------------------------
-- Table structure for `t_sys_log`
-- ----------------------------
DROP TABLE IF EXISTS `t_sys_log`;
CREATE TABLE `t_sys_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL,
  `error_msg` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系统执行过程中的异常';

-- ----------------------------
-- Records of t_sys_log
-- ----------------------------

-- ----------------------------
-- Table structure for `t_user`
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `u_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '本系统的用户标识',
  `user_id` varchar(32) DEFAULT NULL COMMENT '兼容性用户ID',
  `phone` varchar(13) DEFAULT NULL COMMENT '手机号码',
  `password` varchar(32) DEFAULT NULL COMMENT '密码',
  `nick_name` varchar(20) DEFAULT NULL COMMENT '昵称',
  `head_img` varchar(64) NOT NULL DEFAULT '' COMMENT '头像图片链接',
  `reg_channel` varchar(12) NOT NULL DEFAULT '0' COMMENT '注册来源(手机注册、闪聘帐号、微信账号)',
  `reg_time` datetime DEFAULT NULL COMMENT '注册时间',
  `login_time` datetime DEFAULT NULL COMMENT '登录时间',
  PRIMARY KEY (`u_id`),
  UNIQUE KEY `user_phone_idx` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 COMMENT='用户基本信息表';