预约功能的知识整理

前置知识

如果项目为小程序的开发项目中：

我们确定数据库中有的字段有: 预约人姓名、手机号、家人名称、预约时间

根据我们的经定一表必须要有的6个字段：

主键、创建时间、修改时间、创建人、修改人、备注

使用我们现在有的字段为：

主键、 预约人姓名、手机号、家人姓名、预约时间、创建时间、修改时间、创建人、修改人、备注

预约的状态也有4种,具体如下:

•  待报道 预约成功后
•  已完成 
• 取消
• 过期

所以，预约表中最终的表字段为：

主键、 预约人姓名、手机号、家人姓名、类型、状态、预约时间、创建时间、修改时间、创建人、修改人、备注

由于一个手机号在同一个时间段内只能预约一次，我们也可以在表结构中设置唯一约束（手机号+时间）

我们可以得到如下的预约表 reservation：

CREATE TABLE `reservation` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '预约人姓名',`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '预约人手机号',`time` datetime NOT NULL COMMENT '预约时间',`visitor` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '探访人',`type` int NOT NULL COMMENT '预约类型，0：参观预约，1：探访预约',`status` int NOT NULL COMMENT '预约状态，0：待报道，1：已完成，2：取消，3：过期',`create_time` datetime NOT NULL COMMENT '创建时间',`update_time` datetime DEFAULT NULL COMMENT '更新时间',`create_by` bigint DEFAULT NULL COMMENT '创建人id',`update_by` bigint DEFAULT NULL COMMENT '更新人id',`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '备注',PRIMARY KEY (`id`) USING BTREE,UNIQUE KEY `name_mobile_time_visitor` (`mobile`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=93 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='预约信息表';

接口的常见实现与操作

1.  新增预约  /customer/reservation/

        请求的方法：post

         请求参数:

{"mobile": "",  //手机号"name": "",    //预约人"time": "",    //时间"type":0,      //预约类型  0为参观预约"visitor": ""  //家人
}

我们可以通过一个dto对象传递

    实现思路：

        1.1 Controller：

/*** 新增预约* @param reservationDto* @return*/@PostMappingpublic ResponseResult addReservation(@RequestBody ReservationDto reservationDto){reservationService.addReservation(reservationDto);return ResponseResult.success();}

       1.2 impl:

        （1）根据userid 判断今天的取消的次数 

        （2）进行预约，插入状态

 /*** 新增预约信息* @param reservationDto*/@Overridepublic void addReservation(ReservationDto reservationDto) {// 1. 查看数据库的表// 获取当前的useridLong userId = UserThreadLocal.getUserId();// 判断今天取消的次数是否大于3次Long cancelledCount = cancelledCount(userId);// 如果大于3次则不进行预约if(cancelledCount >=3 ){throw new RuntimeException(String.valueOf(BasicEnum.RESERVATION_CANCEL_COUNT_UPPER_LIMIT));}// 进行预约Reservation reservation = new Reservation();BeanUtils.copyProperties(reservationDto,reservation);// 预约状态，0：待报道reservation.setStatus(ReservationStatus.PENDING.getOrdinal());reservation.setCreateBy(userId);try {reservationMapper.insert(reservation);}catch (Exception e){log.info(e + "");throw new RuntimeException(String.valueOf(BasicEnum.TIME_ALREADY_RESERVATED_BY_PHONE));}}

        1.3 mapper.xml 进行新增操作

<insert id="insert" parameterType="com.zzyl.entity.Reservation">insert into reservation(name, mobile, time, visitor, type, status, create_by, remark, create_time)values (#{name}, #{mobile}, #{time}, #{visitor}, #{type}, #{status}, #{createBy}, #{remark}, #{createTime})</insert>

2. 分页查询 /customer/reservation/page

        请求方式: GET

        请求参数:

参数名称	参数说明	数据类型
pageNum	页码	int
pageSize	每页数量	int
status	状态	int

实现思路：

        2.1 controller

@GetMapping("/page")public ResponseResult<PageResponse<ReservationVo>> getPage(@RequestParam(defaultValue = "1") int pageNum,@RequestParam(defaultValue = "10") int pageSize,@RequestParam(required = false) String name,@RequestParam(required = false) String phone,@RequestParam(required = false) Integer status,@RequestParam(required = false) Integer type,@RequestParam(required = false) Long startTime,@RequestParam(required = false) Long endTime){PageResponse<ReservationVo> byPage = reservationService.findByPage(pageNum, pageSize, name, phone, status, type,ObjectUtil.isEmpty(startTime)? null : LocalDateTimeUtil.of(startTime),ObjectUtil.isEmpty(endTime)? null : LocalDateTimeUtil.of(endTime));return success(byPage);}

         2.2 impl  根据分页插件 与 userid进行查询

 /***  分页查询预约* @param pageNum* @param pageSize* @param name* @param phone* @param status* @param type* @param startTime* @param endTime* @return*/@Overridepublic PageResponse<ReservationVo> findByPage(int pageNum, int pageSize, String name, String phone, Integer status, Integer type, LocalDateTime startTime, LocalDateTime endTime) {PageHelper.startPage(pageNum,pageSize);Long userId = UserThreadLocal.getUserId();Page<Reservation> page = reservationMapper.findByPage(pageNum,pageSize,name, phone, status, type, userId, startTime, endTime);return PageResponse.of(page,ReservationVo.class);}

        2.3 mapper.xml 实现 用动态Sql进行实现

 <select id="findByPage" parameterType="java.util.Map" resultType="com.zzyl.entity.Reservation">select r.id, r.name, r.mobile, r.time, r.visitor, r.type, r.status, r.create_by, r.remark, r.create_time, s.nameas creator from reservation rLEFT JOIN member s ON s.id = r.create_by<where><if test="name != null and name != ''">and r.name like concat('%',#{name},'%')</if><if test="mobile != null and mobole != ''">and r.mobile = #{mobile}</if><if test="status != null">and r.status = #{status,jdbcType=INTEGER}</if><if test="createBy != null">and r.create_by = #{createBy}</if><if test="startTime != null and endTime != null">and r.time BETWEEN #{startTime} and #{endTime}</if></where>order by create_time desc</select>

3. 取消预约 

• 请求方式:PUT    /customer/reservation/{id}/cancel

• 请求参数:

参数名称	参数说明	数据类型
id	主键	long

 

实现思路：

        3.1 Impl  

/*** 取消预约* @param id*/@Overridepublic void cancelledById(Long id) {Reservation reservation = reservationMapper.findById(id);if(ObjectUtil.isNotEmpty(reservation)){// 2：取消reservation.setStatus(ReservationStatus.CANCELED.getOrdinal());reservationMapper.update(reservation);}}

     3.2 xml

 <update id="update">update reservationset name        = #{name},mobile      = #{mobile},time        = #{time},visitor     = #{visitor},type        = #{type},status      = #{status},update_time = #{updateTime},update_by   = #{updateBy}where id = #{id}</update>

  4.  查询取消预约数量

• 请求方式:GET

• 请求参数: 无，我们需要获取线程中的用户即可，在后端获取，不需要前端传任何参数

实现思路：

        4.1 Impl  获取一天的时间 与 userId

/*** 取消的次数* @param userId* @return*/@Overridepublic Long cancelledCount(Long userId) {// 1.查看今天内取消的次数// 需要给时间范围LocalDateTime startTime = LocalDateTime.now().withHour(0).withMinute(0).withSecond(0);LocalDateTime endTime = LocalDateTime.now().withHour(23).withMinute(59).withSecond(59);Long count = reservationMapper.getcancelledCount(startTime,endTime,userId);return count;}

        4.2 xml

<select id="getcancelledCount" resultType="java.lang.Long">select count(*) from reservationwhere update_time between #{startTime} and #{endTime}and status = 2and update_by = #{userId}</select>

5. 查询每个时间段剩余预约次数

• 接口地址:/customer/reservation/countByTime

• 请求方式:GET

• 请求参数:

参数名称	参数说明	数据类型
time	时间	long(时间戳)

• 返回示例：

{ "code": 200, "msg": "操作成功","data": [ { "time": "2023-08-21 09:30:00", "count": 5 },{ "time": "2023-08-21 10:00:00", "count": 5 } ],"operationTime": null
}

实现思路：

        5.1 impl 

 /*** 查询每个时间剩余的预约次数* @param time* @return*/@Overridepublic List<CountVo> getCountByTime(LocalDateTime time) {LocalDateTime endTime = time.plusHours(24);List<CountVo> list = reservationMapper.getCountByTime(time, endTime);return reservationMapper.getCountByTime(time,endTime);}

        5.2 mapper

  <select id="getCountByTime" resultType="com.zzyl.vo.CountVo">SELECT time, 6 - COUNT(*) AS countFROM reservationWHERE `time` BETWEEN #{startTime}AND #{endTime}and status != 2GROUP BY time</select>

代码实例：

package com.zzyl.controller.customer;import cn.hutool.core.date.LocalDateTimeUtil;
import com.zzyl.base.PageResponse;
import com.zzyl.base.ResponseResult;
import com.zzyl.controller.BaseController;
import com.zzyl.dto.ReservationDto;
import com.zzyl.service.ReservationService;
import com.zzyl.utils.ObjectUtil;
import com.zzyl.utils.UserThreadLocal;
import com.zzyl.vo.CountVo;
import com.zzyl.vo.ReservationVo;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.catalina.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;@RestController
@RequestMapping("/customer/reservation")
@Api(tags = "客户预约管理")
public class CustomerReservationController extends BaseController {@Autowiredprivate ReservationService reservationService;/*** 查询每个时间段剩余的预约次数* @param time* @return*/@ApiOperation("查询每个时间段剩余的预约次数")@GetMapping("/countByTime")public ResponseResult getCountByTime(@RequestParam(required = false) Long time){List<CountVo> list = reservationService.getCountByTime(LocalDateTimeUtil.of(time));System.out.println("time --------------------------+ " + time);System.out.println("LocalDateTimeUtil.of(time) --------------+"+LocalDateTimeUtil.of(time));System.out.println("list ----+"+list);return success(list);}/*** 查询预约的次数* @return*/@GetMapping("/cancelled-count")public ResponseResult cancelledCount(){// 1. 获取ThreadLocal中的用户的idLong userId = UserThreadLocal.getUserId();Long count = reservationService.cancelledCount(userId);return ResponseResult.success(count);}/*** 新增预约* @param reservationDto* @return*/@PostMappingpublic ResponseResult addReservation(@RequestBody ReservationDto reservationDto){reservationService.addReservation(reservationDto);return ResponseResult.success();}/*** 分页查询* @param pageNum* @param pageSize* @param name* @param phone* @param status* @param type* @param startTime* @param endTime* @return*/@GetMapping("/page")public ResponseResult<PageResponse<ReservationVo>> getPage(@RequestParam(defaultValue = "1") int pageNum,@RequestParam(defaultValue = "10") int pageSize,@RequestParam(required = false) String name,@RequestParam(required = false) String phone,@RequestParam(required = false) Integer status,@RequestParam(required = false) Integer type,@RequestParam(required = false) Long startTime,@RequestParam(required = false) Long endTime){PageResponse<ReservationVo> byPage = reservationService.findByPage(pageNum, pageSize, name, phone, status, type,ObjectUtil.isEmpty(startTime)? null : LocalDateTimeUtil.of(startTime),ObjectUtil.isEmpty(endTime)? null : LocalDateTimeUtil.of(endTime));return success(byPage);}@PutMapping("/{id}/cancel")public ResponseResult cancelById(@PathVariable Long id){reservationService.cancelledById(id);return success();}
}

public interface ReservationService {Long cancelledCount(Long userId);public List<CountVo> getCountByTime(LocalDateTime time);void addReservation(ReservationDto reservationDto);PageResponse<ReservationVo> findByPage(int pageNum, int pageSize, String name, String phone, Integer status, Integer type, LocalDateTime startTime, LocalDateTime endTime);void cancelledById(Long id);void update(Long id, ReservationDto dto);void updateVisitReservationStatusToExpiredIfNotCompleted(Long id);ReservationVo findById(Long id);void visit(Long id, Long time);void updateReservationStatus(LocalDateTime now);List<ReservationVo> findAll(String mobile, LocalDateTime time);
}

package com.zzyl.service.impl;import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.LocalDateTimeUtil;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.zzyl.base.PageResponse;
import com.zzyl.dto.ReservationDto;
import com.zzyl.dto.VisitDto;
import com.zzyl.entity.Reservation;
import com.zzyl.enums.BasicEnum;
import com.zzyl.enums.ReservationStatus;
import com.zzyl.mapper.ReservationMapper;
import com.zzyl.service.ReservationService;
import com.zzyl.service.VisitService;
import com.zzyl.utils.ObjectUtil;
import com.zzyl.utils.UserThreadLocal;
import com.zzyl.vo.CountVo;
import com.zzyl.vo.ReservationVo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;@Service
@Slf4j
public class ResercationServiceImpl implements ReservationService {@Autowiredprivate ReservationMapper reservationMapper;@Autowiredprivate VisitService visitService;/*** 取消的次数* @param userId* @return*/@Overridepublic Long cancelledCount(Long userId) {// 1.查看今天内取消的次数// 需要给时间范围LocalDateTime startTime = LocalDateTime.now().withHour(0).withMinute(0).withSecond(0);LocalDateTime endTime = LocalDateTime.now().withHour(23).withMinute(59).withSecond(59);Long count = reservationMapper.getcancelledCount(startTime,endTime,userId);return count;}/*** 查询每个时间剩余的预约次数* @param time* @return*/@Overridepublic List<CountVo> getCountByTime(LocalDateTime time) {LocalDateTime endTime = time.plusHours(24);List<CountVo> list = reservationMapper.getCountByTime(time, endTime);return reservationMapper.getCountByTime(time,endTime);}/*** 查找所有预约* @param mobile 预约人手机号* @param time 预约时间*/@Overridepublic List<ReservationVo> findAll(String mobile, LocalDateTime time) {LocalDateTime endTime = time.plusHours(24); // 计算24小时后的时间Long userId = UserThreadLocal.getUserId();List<Reservation> reservations = reservationMapper.findAll(userId, mobile, time, endTime); // 根据mobile和时间范围查询预约return convertToVOList(reservations);}private List<ReservationVo> convertToVOList(List<Reservation> reservations){return reservations.stream().map(this::convertToVO).collect(Collectors.toList());}/*** 新增预约信息* @param reservationDto*/@Overridepublic void addReservation(ReservationDto reservationDto) {// 1. 查看数据库的表// 获取当前的useridLong userId = UserThreadLocal.getUserId();// 判断今天取消的次数是否大于3次Long cancelledCount = cancelledCount(userId);// 如果大于3次则不进行预约if(cancelledCount >=3 ){throw new RuntimeException(String.valueOf(BasicEnum.RESERVATION_CANCEL_COUNT_UPPER_LIMIT));}// 进行预约Reservation reservation = new Reservation();BeanUtils.copyProperties(reservationDto,reservation);// 预约状态，0：待报道reservation.setStatus(ReservationStatus.PENDING.getOrdinal());reservation.setCreateBy(userId);try {reservationMapper.insert(reservation);}catch (Exception e){log.info(e + "");throw new RuntimeException(String.valueOf(BasicEnum.TIME_ALREADY_RESERVATED_BY_PHONE));}}/***  分页查询预约* @param pageNum* @param pageSize* @param name* @param phone* @param status* @param type* @param startTime* @param endTime* @return*/@Overridepublic PageResponse<ReservationVo> findByPage(int pageNum, int pageSize, String name, String phone, Integer status, Integer type, LocalDateTime startTime, LocalDateTime endTime) {PageHelper.startPage(pageNum,pageSize);Long userId = UserThreadLocal.getUserId();Page<Reservation> page = reservationMapper.findByPage(pageNum,pageSize,name, phone, status, type, userId, startTime, endTime);return PageResponse.of(page,ReservationVo.class);}/*** 取消预约* @param id*/@Overridepublic void cancelledById(Long id) {Reservation reservation = reservationMapper.findById(id);if(ObjectUtil.isNotEmpty(reservation)){// 2：取消reservation.setStatus(ReservationStatus.CANCELED.getOrdinal());reservationMapper.update(reservation);}}/*** 更新预约*/@Overridepublic void update(Long id, ReservationDto dto) {Reservation reservation = reservationMapper.findById(id);if (reservation != null) {BeanUtils.copyProperties(dto, reservation);reservation.setId(id);reservation.setUpdateTime(LocalDateTime.now());reservationMapper.update(reservation);}}/*** 将Reservation转换为ReservationVO*/private ReservationVo convertToVO(Reservation reservation) {return BeanUtil.toBean(reservation, ReservationVo.class);}/*** 将时间戳转换为字符串*/private String convertTimeToStr(Long time) {return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date(time));}/*** 如果预约未完成，则将预约状态更新为过期*/@Overridepublic void updateVisitReservationStatusToExpiredIfNotCompleted(Long id) {Reservation visitReservation = reservationMapper.findById(id);// 判断是否 已经完成 || 已取消  -- > 是就直接返回if (visitReservation.getStatus().equals(ReservationStatus.COMPLETED.getOrdinal())|| visitReservation.getStatus().equals(ReservationStatus.CANCELED.getOrdinal()) ) {return;}LocalDateTime reservationTime = visitReservation.getTime();LocalDateTime currentTime = LocalDateTime.now();if (currentTime.isAfter(reservationTime.plusHours(1))) {visitReservation.setStatus(ReservationStatus.EXPIRED.getOrdinal());reservationMapper.update(visitReservation);}}/*** 根据id查找预约*/@Overridepublic ReservationVo findById(Long id) {Reservation reservation = reservationMapper.findById(id);if (reservation != null) {return convertToVO(reservation);}return null;}/*** 来访* @param id ID* @param time 时间*/@Overridepublic void visit(Long id, Long time) {Reservation reservation = reservationMapper.findById(id);if (reservation != null) {reservation.setStatus(ReservationStatus.COMPLETED.getOrdinal());reservationMapper.update(reservation);VisitDto visitDto = BeanUtil.toBean(reservation, VisitDto.class);visitDto.setTime(LocalDateTimeUtil.of(time));visitService.add(visitDto);}}@Overridepublic void updateReservationStatus(LocalDateTime now) {reservationMapper.updateReservationStatus(now);}}

package com.zzyl.mapper;import com.github.pagehelper.Page;
import com.zzyl.entity.Reservation;
import com.zzyl.vo.CountVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Update;import java.time.LocalDateTime;
import java.util.List;@Mapper
public interface ReservationMapper {void update(Reservation reservation);void insert(Reservation reservation);int deleteById(Long id);Reservation findById(@Param("id") Long id);List<CountVo> getCountByTime(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);List<Reservation> findAll(@Param("createBy") Long userId, @Param("mobile") String mobile, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);int countReservationsWithinTimeRange(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("createBy") Long createBy, @Param("status") Integer status);Page<Reservation> findByPage(@Param("page") int startIndex, @Param("pageSize") int pageSize, @Param("name") String name, @Param("mobile") String mobile, @Param("status") Integer status, @Param("type") Integer type, @Param("createBy") Long userId, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);Long getcancelledCount(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, @Param("userId") Long userId);@Update("update reservation set status = 3 where status = 0 and time <= #{minusDays}")void updateReservationStatus(LocalDateTime minusDays);}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zzyl.mapper.ReservationMapper"><resultMap id="reservationMap" type="com.zzyl.entity.Reservation"><id column="id" property="id"/><result column="name" property="name"/><result column="mobile" property="mobile"/><result column="time" property="time"/><result column="visitor" property="visitor"/><result column="type" property="type"/><result column="status" property="status"/><result column="create_by" property="createBy"/><result column="update_by" property="updateBy"/><result column="remark" property="remark"/><result column="create_time" property="createTime"/><result column="update_time" property="updateTime"/><result column="creator" property="creator"/></resultMap><insert id="insert" parameterType="com.zzyl.entity.Reservation">insert into reservation(name, mobile, time, visitor, type, status, create_by, remark, create_time)values (#{name}, #{mobile}, #{time}, #{visitor}, #{type}, #{status}, #{createBy}, #{remark}, #{createTime})</insert><update id="update">update reservationset name        = #{name},mobile      = #{mobile},time        = #{time},visitor     = #{visitor},type        = #{type},status      = #{status},update_time = #{updateTime},update_by   = #{updateBy}where id = #{id}</update><delete id="deleteById" parameterType="java.lang.Long">deletefrom reservationwhere id = #{id}</delete><select id="findByPage" parameterType="java.util.Map" resultType="com.zzyl.entity.Reservation">select r.id, r.name, r.mobile, r.time, r.visitor, r.type, r.status, r.create_by, r.remark, r.create_time, s.nameas creator from reservation rLEFT JOIN member s ON s.id = r.create_by<where><if test="name != null and name != ''">and r.name like concat('%',#{name},'%')</if><if test="mobile != null and mobole != ''">and r.mobile = #{mobile}</if><if test="status != null">and r.status = #{status,jdbcType=INTEGER}</if><if test="createBy != null">and r.create_by = #{createBy}</if><if test="startTime != null and endTime != null">and r.time BETWEEN #{startTime} and #{endTime}</if></where>order by create_time desc</select><select id="getCountByTime" resultType="com.zzyl.vo.CountVo">SELECT time, 6 - COUNT(*) AS countFROM reservationWHERE `time` BETWEEN #{startTime}AND #{endTime}and status != 2GROUP BY time</select><select id="findAll" resultMap="reservationMap">select * from reservationWHERE time BETWEEN #{startTime} AND #{endTime}<if test="mobile != null and mobile != ''">and mobile = #{mobile}</if><if test="createBy != null">AND create_by = #{createBy}</if>order by create_time desc</select><select id="getcancelledCount" resultType="java.lang.Long">select count(*) from reservationwhere update_time between #{startTime} and #{endTime}and status = 2and update_by = #{userId}</select><select id="findById" resultType="com.zzyl.entity.Reservation">select * from reservationwhere id = #{id}</select><select id="countReservationsWithinTimeRange" resultType="java.lang.Integer">ELECT COUNT(*) FROM reservationWHERE time BETWEEN #{startTime} AND #{endTime}<if test="createBy != null">AND create_by = #{createBy}</if><if test="status != null">and status = #{status, jdbcType=INTEGER}</if></select></mapper>