/*
Navicat MySQL Data Transfer

Source Server         : 南航专用
Source Server Version : 50626
Source Host           : localhost:3306
Source Database       : njbx

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

Date: 2017-08-11 16:29:05
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES ('1', '业务一部');
INSERT INTO `department` VALUES ('2', '业务二部');

-- ----------------------------
-- Table structure for ipolicy
-- ----------------------------
DROP TABLE IF EXISTS `ipolicy`;
CREATE TABLE `ipolicy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `infee` int(11) DEFAULT NULL,
  `realfee` int(11) DEFAULT NULL,
  `maxfee` int(11) DEFAULT NULL,
  `tid` int(11) DEFAULT NULL,
  `itime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of ipolicy
-- ----------------------------
INSERT INTO `ipolicy` VALUES ('1', '10000', '8000', '500000', '1', '2016-07-01 15:46:00');
INSERT INTO `ipolicy` VALUES ('2', '10000', '6000', '500000', '2', '2016-01-01 15:46:11');
INSERT INTO `ipolicy` VALUES ('3', '10000', '7000', '500000', '3', '2016-01-01 15:46:11');
INSERT INTO `ipolicy` VALUES ('4', '10000', '5000', '500000', '1', '2016-01-01 15:46:11');
INSERT INTO `ipolicy` VALUES ('5', '10000', '4000', '500000', '2', '2016-01-01 15:46:11');
INSERT INTO `ipolicy` VALUES ('6', '10000', '3000', '500000', '3', '2016-01-01 15:46:11');
INSERT INTO `ipolicy` VALUES ('7', '10000', '3500', '500000', '1', '2016-01-01 15:46:11');
INSERT INTO `ipolicy` VALUES ('8', '10000', '5500', '500000', '2', '2016-01-01 15:46:11');
INSERT INTO `ipolicy` VALUES ('9', '10000', '7500', '500000', '3', '2016-01-01 15:46:11');
INSERT INTO `ipolicy` VALUES ('10', '10000', '7900', '500000', '1', '2016-01-01 15:46:11');

-- ----------------------------
-- Table structure for itype
-- ----------------------------
DROP TABLE IF EXISTS `itype`;
CREATE TABLE `itype` (
  `tid` int(11) DEFAULT NULL,
  `tname` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of itype
-- ----------------------------
INSERT INTO `itype` VALUES ('1', '人寿保险');
INSERT INTO `itype` VALUES ('2', '车险');
INSERT INTO `itype` VALUES ('3', '非车险');

-- ----------------------------
-- Table structure for ppolicy
-- ----------------------------
DROP TABLE IF EXISTS `ppolicy`;
CREATE TABLE `ppolicy` (
  `id` int(11) DEFAULT NULL,
  `apay` int(255) DEFAULT NULL,
  `nopay` int(255) DEFAULT NULL,
  `iid` int(11) DEFAULT NULL,
  `rid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of ppolicy
-- ----------------------------
INSERT INTO `ppolicy` VALUES ('1', '0', '100000', '1', '1');
INSERT INTO `ppolicy` VALUES ('2', '0', '80000', '2', '2');
INSERT INTO `ppolicy` VALUES ('3', '50000', '50000', '3', '3');
INSERT INTO `ppolicy` VALUES ('4', null, null, '4', null);
INSERT INTO `ppolicy` VALUES ('5', null, null, '5', null);
INSERT INTO `ppolicy` VALUES ('6', null, null, '6', null);
INSERT INTO `ppolicy` VALUES ('7', null, null, '7', null);
INSERT INTO `ppolicy` VALUES ('8', null, null, '8', null);
INSERT INTO `ppolicy` VALUES ('9', '10000', '0', '9', null);
INSERT INTO `ppolicy` VALUES ('10', '20000', '0', '10', null);

-- ----------------------------
-- Table structure for reason
-- ----------------------------
DROP TABLE IF EXISTS `reason`;
CREATE TABLE `reason` (
  `id` int(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `pid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of reason
-- ----------------------------
INSERT INTO `reason` VALUES ('1', '违法', '1');
INSERT INTO `reason` VALUES ('2', '做假', '2');
INSERT INTO `reason` VALUES ('3', '自身原因', '3');

-- ----------------------------
-- Table structure for saleman
-- ----------------------------
DROP TABLE IF EXISTS `saleman`;
CREATE TABLE `saleman` (
  `id` int(11) DEFAULT NULL,
  `ipid` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `did` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of saleman
-- ----------------------------
INSERT INTO `saleman` VALUES ('1', '1', '南航', '1');
INSERT INTO `saleman` VALUES ('2', '2', '丁云', '2');
INSERT INTO `saleman` VALUES ('3', '3', '丁云', '2');
INSERT INTO `saleman` VALUES ('4', '4', '蔡振东', '3');
INSERT INTO `saleman` VALUES ('5', '5', '蔡振东', '3');
INSERT INTO `saleman` VALUES ('6', '6', '蔡振东', '3');
INSERT INTO `saleman` VALUES ('7', '7', '孙畅', '1');
INSERT INTO `saleman` VALUES ('8', '8', '孙畅', '1');
INSERT INTO `saleman` VALUES ('9', '9', '孙畅', '1');
INSERT INTO `saleman` VALUES ('10', '10', '孙畅', '1');

-- ----------------------------
-- View structure for diyiti
-- ----------------------------
DROP VIEW IF EXISTS `diyiti`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%`  VIEW `diyiti` AS select (sum(ipolicy.infee) -sum(ipolicy.realfee) )/sum(ipolicy.infee)  from ipolicy where ipolicy.tid=1 ;

-- ----------------------------
-- View structure for shiji
-- ----------------------------
DROP VIEW IF EXISTS `shiji`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER  VIEW `shiji` AS select SUM(i.infee) r from ipolicy i where i.tid=1 ;

-- ----------------------------
-- View structure for yingshou
-- ----------------------------
DROP VIEW IF EXISTS `yingshou`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER  VIEW `yingshou` AS select SUM(i.realfee) y from ipolicy i where i.tid=1 ;