/*
 Navicat Premium Data Transfer

 Source Server         : linux-server
 Source Server Type    : MySQL
 Source Server Version : 80020
 Source Host           : 121.89.207.103:3306
 Source Schema         : book_management

 Target Server Type    : MySQL
 Target Server Version : 80020
 File Encoding         : 65001

 Date: 18/09/2020 08:55:04
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for book_type
-- ----------------------------
DROP TABLE IF EXISTS `book_type`;
CREATE TABLE `book_type`  (
  `bt_id` int(0) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '类别名称',
  PRIMARY KEY (`bt_id`) USING BTREE,
  UNIQUE INDEX `name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of book_type
-- ----------------------------
INSERT INTO `book_type` VALUES (4, '文学');
INSERT INTO `book_type` VALUES (1, '暂定');
INSERT INTO `book_type` VALUES (2, '物理学');
INSERT INTO `book_type` VALUES (8, '羊肉串');
INSERT INTO `book_type` VALUES (11, '龙凤和');

-- ----------------------------
-- Table structure for books
-- ----------------------------
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books`  (
  `b_id` int(0) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `bt_id` int(0) NOT NULL COMMENT '图书类别ID',
  `type_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '类别名名',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '图书名称',
  `author` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '作者',
  `publisher` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '出版社',
  `total` int(0) NOT NULL COMMENT '总数量',
  `rental_unit` int(0) NOT NULL COMMENT '租金/天 多少分钱',
  `over_due_unit` int(0) NOT NULL COMMENT '逾期租金/天 多少分钱',
  `in_library_total` int(0) NOT NULL COMMENT '在馆图书总数',
  `reserve_num` int(0) NOT NULL COMMENT '预约人数',
  `status` tinyint(0) NOT NULL DEFAULT 1 COMMENT '图书的状态（true/false）',
  PRIMARY KEY (`b_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 51 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of books
-- ----------------------------
INSERT INTO `books` VALUES (1, 1, '暂定', '数据结构', '李锡辉', '湖南信息学院', 10, 100, 150, 10, 0, 0);
INSERT INTO `books` VALUES (2, 1, '暂定', 'javaEE框架技术1', '毛杰辉', '湖南信息学院', 10, 110, 150, 9, 0, 1);
INSERT INTO `books` VALUES (49, 1, '暂定', '哑舍', '玄色', '1212', 10, 200, 200, 10, 0, 0);
INSERT INTO `books` VALUES (50, 1, '暂定', '1234', '3', '3', 10, 200, 200, 10, 0, 1);
INSERT INTO `books` VALUES (51, 1, '暂定', '3', '2', '23', 10, 200, 300, 10, 0, 1);

-- ----------------------------
-- Table structure for borrow_books
-- ----------------------------
DROP TABLE IF EXISTS `borrow_books`;
CREATE TABLE `borrow_books`  (
  `bb_id` int(0) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `r_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '读者姓名',
  `r_id` int(0) NOT NULL COMMENT '读者ID',
  `b_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '图书名',
  `b_id` int(0) NOT NULL COMMENT '图书ID',
  `remaining_days` int(0) NOT NULL COMMENT '剩余借书天数',
  `bb_time` datetime(0) NOT NULL COMMENT '借阅时间',
  `due_time` datetime(0) NOT NULL COMMENT '应还书日期',
  `rental_unit` int(0) NOT NULL COMMENT '租金单价',
  `is_overdue` tinyint unsigned NOT NULL COMMENT '是否逾期',
  `overdue_rental_unit` int(0) NOT NULL COMMENT '逾期扣除单价/分',
  `rental` int unsigned NOT NULL COMMENT '用户扣除的租金/分',
  PRIMARY KEY (`bb_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 41 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of borrow_books
-- ----------------------------
INSERT INTO `borrow_books` VALUES (40, '羊肉串', 3, 'javaEE框架技术1', 2, -67, '2020-07-10 00:00:00', '2020-07-13 00:00:00', 110, 1, 150, 10380);

-- ----------------------------
-- Table structure for give_back_books
-- ----------------------------
DROP TABLE IF EXISTS `give_back_books`;
CREATE TABLE `give_back_books`  (
  `gbb_id` int(0) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `r_id` int(0) NOT NULL COMMENT '读者ID',
  `r_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '读者名',
  `b_id` int(0) NOT NULL COMMENT '图书ID',
  `b_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '图书名',
  `bb_time` date NOT NULL COMMENT '借阅时间',
  `due_time` date NOT NULL COMMENT '应还书日期',
  `reality_time` datetime(0) NOT NULL COMMENT '实际还书时间',
  `rental_unit` int(0) NOT NULL COMMENT '租金单价',
  `is_overdue` tinyint(0) NOT NULL COMMENT '是否逾期',
  `overdue_rental_unit` int(0) NOT NULL COMMENT '逾期扣除单价/分',
  `rental` int(0) NOT NULL COMMENT '用户扣除的租金/分',
  PRIMARY KEY (`gbb_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for mail_set
-- ----------------------------
DROP TABLE IF EXISTS `mail_set`;
CREATE TABLE `mail_set`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `account` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '邮箱账号',
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '邮箱密码',
  `overdue_before_day` int(0) NOT NULL COMMENT '逾期前多少天发送邮箱提醒',
  `overdue_after_day` int(0) NOT NULL COMMENT '逾期后多少天发送邮箱提醒',
  `overdue_interval` tinyint(0) NOT NULL COMMENT '逾期后是否间隔overdue_afrter_day再继续发送',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of mail_set
-- ----------------------------
INSERT INTO `mail_set` VALUES (1, 'zpf_0827@163.com', 'DINODMVPZVSBPYKA', 2, 3, 0);

-- ----------------------------
-- Table structure for mail_template
-- ----------------------------
DROP TABLE IF EXISTS `mail_template`;
CREATE TABLE `mail_template`  (
  `mt_id` int(0) NOT NULL AUTO_INCREMENT,
  `template` varchar(3000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '邮箱模板',
  `tt_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '模板类型',
  `tt_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '模板类型名',
  PRIMARY KEY (`mt_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of mail_template
-- ----------------------------
INSERT INTO `mail_template` VALUES (1, '3213', '1', '未逾期');

-- ----------------------------
-- Table structure for mail_template_type
-- ----------------------------
DROP TABLE IF EXISTS `mail_template_type`;
CREATE TABLE `mail_template_type`  (
  `tt_id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '模板类型名',
  `default` varchar(3000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '默认模板',
  PRIMARY KEY (`tt_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of mail_template_type
-- ----------------------------
INSERT INTO `mail_template_type` VALUES (1, '逾期', 'rw');
INSERT INTO `mail_template_type` VALUES (2, '未逾期', '123');

-- ----------------------------
-- Table structure for manager
-- ----------------------------
DROP TABLE IF EXISTS `manager`;
CREATE TABLE `manager`  (
  `m_id` int(0) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `gender` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '性别',
  `password` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
  `birthday` date NOT NULL COMMENT '出生日期',
  `eamil` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '邮箱',
  `phone` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '手机号',
  `mtId` int(0) NOT NULL COMMENT '类型id',
  PRIMARY KEY (`m_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of manager
-- ----------------------------
INSERT INTO `manager` VALUES (1, '周攀峰', '男', 'zpf123', '2020-07-04', 'zpf_0827@163.com', '18711272916', 500);

-- ----------------------------
-- Table structure for manager_type
-- ----------------------------
DROP TABLE IF EXISTS `manager_type`;
CREATE TABLE `manager_type`  (
  `mt_id` int(0) NOT NULL COMMENT '编号',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '管理人员类别名称',
  PRIMARY KEY (`mt_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of manager_type
-- ----------------------------
INSERT INTO `manager_type` VALUES (1, '管理员');
INSERT INTO `manager_type` VALUES (2, '操作员');

-- ----------------------------
-- Table structure for reader
-- ----------------------------
DROP TABLE IF EXISTS `reader`;
CREATE TABLE `reader`  (
  `r_id` int(0) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `account` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '账户名称',
  `password` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `gender` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '性别',
  `birthday` date NOT NULL COMMENT '出生日期',
  `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '邮箱',
  `phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '手机号',
  `balance` int(0) NOT NULL COMMENT '余额/分',
  `credit_score` int(0) NULL DEFAULT NULL COMMENT '信誉积分',
  `last_login_time` datetime(0) NULL DEFAULT NULL COMMENT '最后一次登录的时间',
  PRIMARY KEY (`r_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of reader
-- ----------------------------


-- ----------------------------
-- Table structure for renewal _book
-- ----------------------------
DROP TABLE IF EXISTS `renewal _book`;
CREATE TABLE `renewal _book`  (
  `rbb_id` int(0) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `r_id` int(0) NOT NULL COMMENT '读者ID',
  `b_id` int(0) NOT NULL COMMENT '图书ID',
  `r_day` int(0) NOT NULL COMMENT '续约天数',
  PRIMARY KEY (`rbb_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for reserve_borrow_books
-- ----------------------------
DROP TABLE IF EXISTS `reserve_borrow_books`;
CREATE TABLE `reserve_borrow_books`  (
  `rbb_id` int(0) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `r_id` int(0) NOT NULL COMMENT '读者ID',
  `r_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '读者姓名',
  `b_id` int(0) NOT NULL COMMENT '图书ID',
  `b_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '图书名',
  `order_time` datetime(0) NOT NULL COMMENT '订单时间',
  `remaining_days` int(0) NOT NULL COMMENT '借阅的天数',
  PRIMARY KEY (`rbb_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 82 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Procedure structure for computerRent
-- ----------------------------
DROP PROCEDURE IF EXISTS `computerRent`;
delimiter ;;
CREATE DEFINER=`root`@`%` PROCEDURE `computerRent`()
begin
     -- 游标所使用变量需要在定义游标之前申明
     declare bb_id_t int(11);
		 declare r_id_t int(11);
		 declare remaining_days_t int(11);
     declare rental_unit_t int(11);
		 declare rental_t int(11);
		 declare overdue_rental_unit_t int(11);	#逾期单位/分
     -- 遍历数据结束标志 注意位置顺序
     DECLARE done INT DEFAULT FALSE;
     -- 注意用别名 因为id在上面已经有定义所以需要使用表的别名区别
     declare cur_test CURSOR for select t.bb_id, t.r_id, t.remaining_days, t.rental_unit, t.rental, t.overdue_rental_unit from borrow_books t;
     -- 将结束标志绑定到游标 
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     open cur_test;
          #repeat
					lp : LOOP
             fetch cur_test into bb_id_t, r_id_t, remaining_days_t, rental_unit_t, rental_t, overdue_rental_unit_t;
						 set remaining_days_t = (remaining_days_t-1);	#剩余天数-1
						 IF done THEN
							 LEAVE lp;
							#判断是否逾期
							ELSEIF remaining_days_t<0 THEN #逾期
								#计算用户逾期所产生的租金( 租金（t.rental） = 原来的租金（rental） +  逾期的1天的租金(over_due_rental_unit) )
								set rental_t = rental_t + overdue_rental_unit_t;
								#用户剩余天数 -1
								#修改 t.is_over_due 为1（表示用户逾期）
								update borrow_books
									set is_overdue=1,
										rental=rental_t,
										remaining_days = remaining_days_t
									where bb_id=bb_id_t;
								update reader 	#用户的账号减去 租金
									set balance = balance - overdue_rental_unit_t
									where r_id=r_id_t;
							ELSE   							
							#未逾期
								#计算租金 租金费用 = 原来的租金费用 + 基础租金费用(set t.rental = rental + rental_unit),
								set rental_t = (rental_t + rental_unit_t);
								#剩余借阅的天数 -1 （set remaining_days = remaining_days-1;）
								update borrow_books
									set rental = rental_t,			
									remaining_days = remaining_days_t 
									where bb_id = bb_id_t;
								update reader 	#用户的账号减去 租金
									set balance = balance - rental_unit_t
									where r_id=r_id_t;
							END IF;
							#用户的账户金额
				end LOOP;
       # until done
				#end repeat;
		-- 注意关闭游标
      close cur_test;  
 end
;;
delimiter ;

-- ----------------------------
-- Event structure for computerRentEvent
-- ----------------------------
DROP EVENT IF EXISTS `computerRentEvent`;
delimiter ;;
CREATE DEFINER = `root`@`%` EVENT `computerRentEvent`
ON SCHEDULE
EVERY '1' DAY STARTS '2020-07-10 00:00:00'
DO call computerRent()
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;