培训学校网站模板,58同城百姓网,德国红点设计奖官网,免费的ai写作网站前置知识
如果项目为小程序的开发项目中#xff1a;
我们确定数据库中有的字段有: 预约人姓名、手机号、家人名称、预约时间
根据我们的经定一表必须要有的6个字段#xff1a;
主键、创建时间、修改时间、创建人、修改人、备注
使用我们现在有的字段为#xff1a;
主键…前置知识
如果项目为小程序的开发项目中
我们确定数据库中有的字段有: 预约人姓名、手机号、家人名称、预约时间
根据我们的经定一表必须要有的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
) ENGINEInnoDB AUTO_INCREMENT93 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci ROW_FORMATDYNAMIC 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 idinsert parameterTypecom.zzyl.entity.Reservationinsert 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页码intpageSize每页数量intstatus状态int
实现思路 2.1 controller
GetMapping(/page)public ResponseResultPageResponseReservationVo 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){PageResponseReservationVo 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 PageResponseReservationVo 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();PageReservation page reservationMapper.findByPage(pageNum,pageSize,name, phone, status, type, userId, startTime, endTime);return PageResponse.of(page,ReservationVo.class);}2.3 mapper.xml 实现 用动态Sql进行实现 select idfindByPage parameterTypejava.util.Map resultTypecom.zzyl.entity.Reservationselect 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_bywhereif testname ! null and name ! and r.name like concat(%,#{name},%)/ifif testmobile ! null and mobole ! and r.mobile #{mobile}/ifif teststatus ! nulland r.status #{status,jdbcTypeINTEGER}/ifif testcreateBy ! nulland r.create_by #{createBy}/ifif teststartTime ! null and endTime ! nulland r.time BETWEEN #{startTime} and #{endTime}/if/whereorder 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 idupdateupdate 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 idgetcancelledCount resultTypejava.lang.Longselect 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 ListCountVo getCountByTime(LocalDateTime time) {LocalDateTime endTime time.plusHours(24);ListCountVo list reservationMapper.getCountByTime(time, endTime);return reservationMapper.getCountByTime(time,endTime);} 5.2 mapper select idgetCountByTime resultTypecom.zzyl.vo.CountVoSELECT 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){ListCountVo 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 ResponseResultPageResponseReservationVo 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){PageResponseReservationVo 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 ListCountVo getCountByTime(LocalDateTime time);void addReservation(ReservationDto reservationDto);PageResponseReservationVo 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);ListReservationVo 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 ListCountVo getCountByTime(LocalDateTime time) {LocalDateTime endTime time.plusHours(24);ListCountVo list reservationMapper.getCountByTime(time, endTime);return reservationMapper.getCountByTime(time,endTime);}/*** 查找所有预约* param mobile 预约人手机号* param time 预约时间*/Overridepublic ListReservationVo findAll(String mobile, LocalDateTime time) {LocalDateTime endTime time.plusHours(24); // 计算24小时后的时间Long userId UserThreadLocal.getUserId();ListReservation reservations reservationMapper.findAll(userId, mobile, time, endTime); // 根据mobile和时间范围查询预约return convertToVOList(reservations);}private ListReservationVo convertToVOList(ListReservation 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 PageResponseReservationVo 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();PageReservation 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);ListCountVo getCountByTime(Param(startTime) LocalDateTime startTime, Param(endTime) LocalDateTime endTime);ListReservation 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);PageReservation 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 version1.0 encodingUTF-8?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.zzyl.mapper.ReservationMapperresultMap idreservationMap typecom.zzyl.entity.Reservationid columnid propertyid/result columnname propertyname/result columnmobile propertymobile/result columntime propertytime/result columnvisitor propertyvisitor/result columntype propertytype/result columnstatus propertystatus/result columncreate_by propertycreateBy/result columnupdate_by propertyupdateBy/result columnremark propertyremark/result columncreate_time propertycreateTime/result columnupdate_time propertyupdateTime/result columncreator propertycreator//resultMapinsert idinsert parameterTypecom.zzyl.entity.Reservationinsert into reservation(name, mobile, time, visitor, type, status, create_by, remark, create_time)values (#{name}, #{mobile}, #{time}, #{visitor}, #{type}, #{status}, #{createBy}, #{remark}, #{createTime})/insertupdate idupdateupdate reservationset name #{name},mobile #{mobile},time #{time},visitor #{visitor},type #{type},status #{status},update_time #{updateTime},update_by #{updateBy}where id #{id}/updatedelete iddeleteById parameterTypejava.lang.Longdeletefrom reservationwhere id #{id}/deleteselect idfindByPage parameterTypejava.util.Map resultTypecom.zzyl.entity.Reservationselect 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_bywhereif testname ! null and name ! and r.name like concat(%,#{name},%)/ifif testmobile ! null and mobole ! and r.mobile #{mobile}/ifif teststatus ! nulland r.status #{status,jdbcTypeINTEGER}/ifif testcreateBy ! nulland r.create_by #{createBy}/ifif teststartTime ! null and endTime ! nulland r.time BETWEEN #{startTime} and #{endTime}/if/whereorder by create_time desc/selectselect idgetCountByTime resultTypecom.zzyl.vo.CountVoSELECT time, 6 - COUNT(*) AS countFROM reservationWHERE time BETWEEN #{startTime}AND #{endTime}and status ! 2GROUP BY time/selectselect idfindAll resultMapreservationMapselect * from reservationWHERE time BETWEEN #{startTime} AND #{endTime}if testmobile ! null and mobile ! and mobile #{mobile}/ifif testcreateBy ! nullAND create_by #{createBy}/iforder by create_time desc/selectselect idgetcancelledCount resultTypejava.lang.Longselect count(*) from reservationwhere update_time between #{startTime} and #{endTime}and status 2and update_by #{userId}/selectselect idfindById resultTypecom.zzyl.entity.Reservationselect * from reservationwhere id #{id}/selectselect idcountReservationsWithinTimeRange resultTypejava.lang.IntegerELECT COUNT(*) FROM reservationWHERE time BETWEEN #{startTime} AND #{endTime}if testcreateBy ! nullAND create_by #{createBy}/ifif teststatus ! nulland status #{status, jdbcTypeINTEGER}/if/select/mapper
