/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50155
Source Host           : localhost:3306
Source Database       : njbx

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

Date: 2017-08-11 16:45:50
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `telephone` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES ('1', '张三', '13566666666');
INSERT INTO `customer` VALUES ('2', '李四', '13666666666');
INSERT INTO `customer` VALUES ('3', '王五', '15133333333');
INSERT INTO `customer` VALUES ('4', '赵六', '15588888888');
INSERT INTO `customer` VALUES ('5', '王麻子', '15899999999');

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `departmentname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

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

-- ----------------------------
-- Table structure for insurance
-- ----------------------------
DROP TABLE IF EXISTS `insurance`;
CREATE TABLE `insurance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

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

-- ----------------------------
-- Table structure for pay
-- ----------------------------
DROP TABLE IF EXISTS `pay`;
CREATE TABLE `pay` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `warrantyid` int(11) DEFAULT NULL,
  `money` float(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of pay
-- ----------------------------
INSERT INTO `pay` VALUES ('1', '1', '80000.00');
INSERT INTO `pay` VALUES ('2', '3', '90000.00');
INSERT INTO `pay` VALUES ('3', '5', '10000.00');

-- ----------------------------
-- Table structure for salesmen
-- ----------------------------
DROP TABLE IF EXISTS `salesmen`;
CREATE TABLE `salesmen` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `departmentid` varchar(255) DEFAULT NULL,
  `salesname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of salesmen
-- ----------------------------
INSERT INTO `salesmen` VALUES ('1', '1', '小王');
INSERT INTO `salesmen` VALUES ('2', '1', '小白');
INSERT INTO `salesmen` VALUES ('3', '2', '小李');
INSERT INTO `salesmen` VALUES ('4', '2', '小明');

-- ----------------------------
-- Table structure for unpayreason
-- ----------------------------
DROP TABLE IF EXISTS `unpayreason`;
CREATE TABLE `unpayreason` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reason` text,
  `payid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of unpayreason
-- ----------------------------
INSERT INTO `unpayreason` VALUES ('1', '酒驾', '1');
INSERT INTO `unpayreason` VALUES ('2', '汽车进水', '3');

-- ----------------------------
-- Table structure for warranty
-- ----------------------------
DROP TABLE IF EXISTS `warranty`;
CREATE TABLE `warranty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `insuranceid` int(11) DEFAULT NULL,
  `customerid` int(11) DEFAULT NULL,
  `coverage` float(10,2) DEFAULT NULL,
  `salesmenid` int(11) DEFAULT NULL,
  `shishou` float(10,2) DEFAULT NULL,
  `ruzhang` float(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of warranty
-- ----------------------------
INSERT INTO `warranty` VALUES ('1', '1', '1', '150000.00', '1', '100000.00', '100000.00');
INSERT INTO `warranty` VALUES ('2', '1', '2', '200000.00', '1', '130000.00', '140000.00');
INSERT INTO `warranty` VALUES ('3', '2', '2', '90000.00', '3', '10000.00', '10000.00');
INSERT INTO `warranty` VALUES ('4', '3', '3', '150000.00', '2', '100000.00', '120000.00');
INSERT INTO `warranty` VALUES ('5', '2', '4', '120000.00', '4', '50000.00', '50000.00');
INSERT INTO `warranty` VALUES ('6', '2', '5', '280000.00', '4', '180000.00', '180000.00');
INSERT INTO `warranty` VALUES ('7', '1', '4', '300000.00', '2', '220000.00', '230000.00');

-- ----------------------------
-- View structure for jjq_bx
-- ----------------------------
DROP VIEW IF EXISTS `jjq_bx`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`  VIEW `jjq_bx` AS SELECT a.*,b.`name` customername,d.type,c.salesname,g.departmentname,e.money,(a.ruzhang-a.shishou)/a.ruzhang yinshou,e.money/a.ruzhang peifu,(a.coverage-e.money)unpay,f.reason
FROM warranty a
LEFT JOIN customer b ON a.customerid=b.id
LEFT JOIN salesmen c ON c.id=a.salesmenid
LEFT JOIN insurance d ON d.id=a.insuranceid
LEFT JOIN pay e ON a.id=e.warrantyid
LEFT JOIN unpayreason f ON f.payid=e.id
LEFT JOIN department g on g.id=c.departmentid
WHERE 1=1 ;