前置知识
如果项目为小程序的开发项目中:
我们确定数据库中有的字段有: 预约人姓名、手机号、家人名称、预约时间
根据我们的经定一表必须要有的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
*/
@PostMapping
public ResponseResult addReservation(@RequestBody ReservationDto reservationDto){
reservationService.addReservation(reservationDto);
return ResponseResult.success();
}
1.2 impl:
(1)根据userid 判断今天的取消的次数
(2)进行预约,插入状态
/**
* 新增预约信息
* @param reservationDto
*/
@Override
public void addReservation(ReservationDto reservationDto) {
// 1. 查看数据库的表
// 获取当前的userid
Long 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
*/
@Override
public 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.name
as creator from reservation r
LEFT 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
*/
@Override
public 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 reservation
set 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
*/
@Override
public 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 reservation
where update_time between #{startTime} and #{endTime}
and status = 2
and 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
*/
@Override
public 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 count
FROM reservation
WHERE `time` BETWEEN #{startTime}
AND #{endTime}
and status != 2
GROUP 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 {
@Autowired
private 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中的用户的id
Long userId = UserThreadLocal.getUserId();
Long count = reservationService.cancelledCount(userId);
return ResponseResult.success(count);
}
/**
* 新增预约
* @param reservationDto
* @return
*/
@PostMapping
public 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 {
@Autowired
private ReservationMapper reservationMapper;
@Autowired
private VisitService visitService;
/**
* 取消的次数
* @param userId
* @return
*/
@Override
public 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
*/
@Override
public 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 预约时间
*/
@Override
public 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
*/
@Override
public void addReservation(ReservationDto reservationDto) {
// 1. 查看数据库的表
// 获取当前的userid
Long 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
*/
@Override
public 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
*/
@Override
public void cancelledById(Long id) {
Reservation reservation = reservationMapper.findById(id);
if(ObjectUtil.isNotEmpty(reservation)){
// 2:取消
reservation.setStatus(ReservationStatus.CANCELED.getOrdinal());
reservationMapper.update(reservation);
}
}
/**
* 更新预约
*/
@Override
public 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));
}
/**
* 如果预约未完成,则将预约状态更新为过期
*/
@Override
public 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查找预约
*/
@Override
public ReservationVo findById(Long id) {
Reservation reservation = reservationMapper.findById(id);
if (reservation != null) {
return convertToVO(reservation);
}
return null;
}
/**
* 来访
* @param id ID
* @param time 时间
*/
@Override
public 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);
}
}
@Override
public 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 reservation
set 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">
delete
from reservation
where 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.name
as creator from reservation r
LEFT 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 count
FROM reservation
WHERE `time` BETWEEN #{startTime}
AND #{endTime}
and status != 2
GROUP BY time
</select>
<select id="findAll" resultMap="reservationMap">
select * from reservation
WHERE 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 reservation
where update_time between #{startTime} and #{endTime}
and status = 2
and update_by = #{userId}
</select>
<select id="findById" resultType="com.zzyl.entity.Reservation">
select * from reservation
where id = #{id}
</select>
<select id="countReservationsWithinTimeRange" resultType="java.lang.Integer">
ELECT COUNT(*) FROM reservation
WHERE 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>