|
|
/**
|
|
|
* 预订数据模型
|
|
|
*
|
|
|
* 功能：封装预订表的数据库操作
|
|
|
*/
|
|
|
|
|
|
const { pool } = require('../config/database');
|
|
|
|
|
|
/**
|
|
|
* 预订模型类
|
|
|
*/
|
|
|
class Reservation {
|
|
|
/**
|
|
|
* 根据ID查找预订
|
|
|
* @param {number} id - 预订ID
|
|
|
* @returns {Promise<object|null>} 预订对象或null
|
|
|
*/
|
|
|
static async findById(id) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
r.*,
|
|
|
u.username as customer_name,
|
|
|
u.phone as customer_phone
|
|
|
FROM reservations r
|
|
|
LEFT JOIN users u ON r.customer_id = u.id
|
|
|
WHERE r.id = ?`,
|
|
|
[id]
|
|
|
);
|
|
|
return rows[0] || null;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询预订失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取预订列表（分页、筛选）
|
|
|
* @param {object} options - 查询选项
|
|
|
* @returns {Promise<object>} 预订列表和总数
|
|
|
*/
|
|
|
static async findAll(options = {}) {
|
|
|
try {
|
|
|
const {
|
|
|
page = 1,
|
|
|
pageSize = 10,
|
|
|
customer_id,
|
|
|
type,
|
|
|
status,
|
|
|
date,
|
|
|
table_number
|
|
|
} = options;
|
|
|
|
|
|
const offset = (page - 1) * pageSize;
|
|
|
|
|
|
// 构建WHERE条件
|
|
|
const conditions = [];
|
|
|
const params = [];
|
|
|
|
|
|
if (customer_id) {
|
|
|
conditions.push('r.customer_id = ?');
|
|
|
params.push(customer_id);
|
|
|
}
|
|
|
|
|
|
if (type) {
|
|
|
conditions.push('r.type = ?');
|
|
|
params.push(type);
|
|
|
}
|
|
|
|
|
|
if (status) {
|
|
|
conditions.push('r.status = ?');
|
|
|
params.push(status);
|
|
|
}
|
|
|
|
|
|
if (date) {
|
|
|
conditions.push('DATE(r.reservation_time) = ?');
|
|
|
params.push(date);
|
|
|
}
|
|
|
|
|
|
if (table_number) {
|
|
|
conditions.push('r.table_number = ?');
|
|
|
params.push(table_number);
|
|
|
}
|
|
|
|
|
|
const whereClause = conditions.length > 0
|
|
|
? 'WHERE ' + conditions.join(' AND ')
|
|
|
: '';
|
|
|
|
|
|
// 查询总数
|
|
|
const [countResult] = await pool.query(
|
|
|
`SELECT COUNT(*) as total FROM reservations r ${whereClause}`,
|
|
|
params
|
|
|
);
|
|
|
const total = countResult[0].total;
|
|
|
|
|
|
// 查询列表
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
r.*,
|
|
|
u.username as customer_name,
|
|
|
u.phone as customer_phone
|
|
|
FROM reservations r
|
|
|
LEFT JOIN users u ON r.customer_id = u.id
|
|
|
${whereClause}
|
|
|
ORDER BY r.reservation_time DESC
|
|
|
LIMIT ? OFFSET ?`,
|
|
|
[...params, pageSize, offset]
|
|
|
);
|
|
|
|
|
|
return {
|
|
|
list: rows,
|
|
|
total,
|
|
|
page: parseInt(page),
|
|
|
pageSize: parseInt(pageSize)
|
|
|
};
|
|
|
} catch (error) {
|
|
|
throw new Error('查询预订列表失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 创建新预订
|
|
|
* @param {object} reservationData - 预订数据
|
|
|
* @returns {Promise<object>} 创建的预订
|
|
|
*/
|
|
|
static async create(reservationData) {
|
|
|
try {
|
|
|
const {
|
|
|
customer_id,
|
|
|
type,
|
|
|
reservation_time,
|
|
|
guest_count,
|
|
|
table_number,
|
|
|
contact_name,
|
|
|
contact_phone,
|
|
|
special_request
|
|
|
} = reservationData;
|
|
|
|
|
|
const [result] = await pool.query(
|
|
|
`INSERT INTO reservations
|
|
|
(customer_id, type, reservation_time, guest_count, table_number,
|
|
|
contact_name, contact_phone, special_request, status)
|
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'pending')`,
|
|
|
[
|
|
|
customer_id,
|
|
|
type,
|
|
|
reservation_time,
|
|
|
guest_count,
|
|
|
table_number || null,
|
|
|
contact_name,
|
|
|
contact_phone,
|
|
|
special_request || null
|
|
|
]
|
|
|
);
|
|
|
|
|
|
return await this.findById(result.insertId);
|
|
|
} catch (error) {
|
|
|
throw new Error('创建预订失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 更新预订
|
|
|
* @param {number} id - 预订ID
|
|
|
* @param {object} reservationData - 要更新的数据
|
|
|
* @returns {Promise<object>} 更新后的预订
|
|
|
*/
|
|
|
static async update(id, reservationData) {
|
|
|
try {
|
|
|
const {
|
|
|
reservation_time,
|
|
|
guest_count,
|
|
|
table_number,
|
|
|
contact_name,
|
|
|
contact_phone,
|
|
|
special_request
|
|
|
} = reservationData;
|
|
|
|
|
|
const updates = [];
|
|
|
const values = [];
|
|
|
|
|
|
if (reservation_time !== undefined) {
|
|
|
updates.push('reservation_time = ?');
|
|
|
values.push(reservation_time);
|
|
|
}
|
|
|
if (guest_count !== undefined) {
|
|
|
updates.push('guest_count = ?');
|
|
|
values.push(guest_count);
|
|
|
}
|
|
|
if (table_number !== undefined) {
|
|
|
updates.push('table_number = ?');
|
|
|
values.push(table_number);
|
|
|
}
|
|
|
if (contact_name !== undefined) {
|
|
|
updates.push('contact_name = ?');
|
|
|
values.push(contact_name);
|
|
|
}
|
|
|
if (contact_phone !== undefined) {
|
|
|
updates.push('contact_phone = ?');
|
|
|
values.push(contact_phone);
|
|
|
}
|
|
|
if (special_request !== undefined) {
|
|
|
updates.push('special_request = ?');
|
|
|
values.push(special_request);
|
|
|
}
|
|
|
|
|
|
if (updates.length === 0) {
|
|
|
throw new Error('没有要更新的数据');
|
|
|
}
|
|
|
|
|
|
values.push(id);
|
|
|
|
|
|
await pool.query(
|
|
|
`UPDATE reservations SET ${updates.join(', ')} WHERE id = ?`,
|
|
|
values
|
|
|
);
|
|
|
|
|
|
return await this.findById(id);
|
|
|
} catch (error) {
|
|
|
throw new Error('更新预订失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 更新预订状态
|
|
|
* @param {number} id - 预订ID
|
|
|
* @param {string} status - 新状态
|
|
|
* @returns {Promise<object>} 更新后的预订
|
|
|
*/
|
|
|
static async updateStatus(id, status) {
|
|
|
try {
|
|
|
await pool.query(
|
|
|
'UPDATE reservations SET status = ?, updated_at = NOW() WHERE id = ?',
|
|
|
[status, id]
|
|
|
);
|
|
|
|
|
|
return await this.findById(id);
|
|
|
} catch (error) {
|
|
|
throw new Error('更新预订状态失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 取消预订
|
|
|
* @param {number} id - 预订ID
|
|
|
* @param {string} cancelReason - 取消原因
|
|
|
* @returns {Promise<object>} 更新后的预订
|
|
|
*/
|
|
|
static async cancel(id, cancelReason = null) {
|
|
|
try {
|
|
|
await pool.query(
|
|
|
`UPDATE reservations
|
|
|
SET status = 'cancelled', cancel_reason = ?, updated_at = NOW()
|
|
|
WHERE id = ?`,
|
|
|
[cancelReason, id]
|
|
|
);
|
|
|
|
|
|
return await this.findById(id);
|
|
|
} catch (error) {
|
|
|
throw new Error('取消预订失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 删除预订
|
|
|
* @param {number} id - 预订ID
|
|
|
* @returns {Promise<boolean>} 是否成功
|
|
|
*/
|
|
|
static async delete(id) {
|
|
|
try {
|
|
|
await pool.query('DELETE FROM reservations WHERE id = ?', [id]);
|
|
|
return true;
|
|
|
} catch (error) {
|
|
|
throw new Error('删除预订失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 检查桌位是否可用
|
|
|
* @param {number} tableNumber - 桌号
|
|
|
* @param {string} reservationTime - 预订时间
|
|
|
* @param {number} excludeId - 排除的预订ID（用于更新时）
|
|
|
* @returns {Promise<boolean>} 是否可用
|
|
|
*/
|
|
|
static async isTableAvailable(tableNumber, reservationTime, excludeId = null) {
|
|
|
try {
|
|
|
// 检查前后2小时内是否有冲突的预订
|
|
|
const query = excludeId
|
|
|
? `SELECT id FROM reservations
|
|
|
WHERE table_number = ?
|
|
|
AND type = 'dine_in'
|
|
|
AND status IN ('pending', 'confirmed')
|
|
|
AND id != ?
|
|
|
AND ABS(TIMESTAMPDIFF(MINUTE, reservation_time, ?)) < 120`
|
|
|
: `SELECT id FROM reservations
|
|
|
WHERE table_number = ?
|
|
|
AND type = 'dine_in'
|
|
|
AND status IN ('pending', 'confirmed')
|
|
|
AND ABS(TIMESTAMPDIFF(MINUTE, reservation_time, ?)) < 120`;
|
|
|
|
|
|
const params = excludeId
|
|
|
? [tableNumber, excludeId, reservationTime]
|
|
|
: [tableNumber, reservationTime];
|
|
|
|
|
|
const [rows] = await pool.query(query, params);
|
|
|
|
|
|
return rows.length === 0;
|
|
|
} catch (error) {
|
|
|
throw new Error('检查桌位可用性失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取指定时间段内的预订列表
|
|
|
* @param {string} startTime - 开始时间
|
|
|
* @param {string} endTime - 结束时间
|
|
|
* @param {string} type - 预订类型（可选）
|
|
|
* @returns {Promise<array>} 预订列表
|
|
|
*/
|
|
|
static async findByTimeRange(startTime, endTime, type = null) {
|
|
|
try {
|
|
|
let query = `
|
|
|
SELECT
|
|
|
r.*,
|
|
|
u.username as customer_name,
|
|
|
u.phone as customer_phone
|
|
|
FROM reservations r
|
|
|
LEFT JOIN users u ON r.customer_id = u.id
|
|
|
WHERE r.reservation_time >= ?
|
|
|
AND r.reservation_time <= ?
|
|
|
AND r.status IN ('pending', 'confirmed')
|
|
|
`;
|
|
|
|
|
|
const params = [startTime, endTime];
|
|
|
|
|
|
if (type) {
|
|
|
query += ' AND r.type = ?';
|
|
|
params.push(type);
|
|
|
}
|
|
|
|
|
|
query += ' ORDER BY r.reservation_time ASC';
|
|
|
|
|
|
const [rows] = await pool.query(query, params);
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询时间段预订失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取今日预订列表
|
|
|
* @param {string} type - 预订类型（可选）
|
|
|
* @returns {Promise<array>} 今日预订列表
|
|
|
*/
|
|
|
static async getTodayReservations(type = null) {
|
|
|
try {
|
|
|
const today = new Date().toISOString().split('T')[0];
|
|
|
|
|
|
let query = `
|
|
|
SELECT
|
|
|
r.*,
|
|
|
u.username as customer_name,
|
|
|
u.phone as customer_phone
|
|
|
FROM reservations r
|
|
|
LEFT JOIN users u ON r.customer_id = u.id
|
|
|
WHERE DATE(r.reservation_time) = ?
|
|
|
AND r.status IN ('pending', 'confirmed')
|
|
|
`;
|
|
|
|
|
|
const params = [today];
|
|
|
|
|
|
if (type) {
|
|
|
query += ' AND r.type = ?';
|
|
|
params.push(type);
|
|
|
}
|
|
|
|
|
|
query += ' ORDER BY r.reservation_time ASC';
|
|
|
|
|
|
const [rows] = await pool.query(query, params);
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询今日预订失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取即将到来的预订（未来24小时内）
|
|
|
* @param {number} customerId - 顾客ID（可选）
|
|
|
* @returns {Promise<array>} 预订列表
|
|
|
*/
|
|
|
static async getUpcomingReservations(customerId = null) {
|
|
|
try {
|
|
|
let query = `
|
|
|
SELECT
|
|
|
r.*,
|
|
|
u.username as customer_name,
|
|
|
u.phone as customer_phone
|
|
|
FROM reservations r
|
|
|
LEFT JOIN users u ON r.customer_id = u.id
|
|
|
WHERE r.reservation_time >= NOW()
|
|
|
AND r.reservation_time <= DATE_ADD(NOW(), INTERVAL 24 HOUR)
|
|
|
AND r.status IN ('pending', 'confirmed')
|
|
|
`;
|
|
|
|
|
|
const params = [];
|
|
|
|
|
|
if (customerId) {
|
|
|
query += ' AND r.customer_id = ?';
|
|
|
params.push(customerId);
|
|
|
}
|
|
|
|
|
|
query += ' ORDER BY r.reservation_time ASC';
|
|
|
|
|
|
const [rows] = await pool.query(query, params);
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询即将到来的预订失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取预订统计信息
|
|
|
* @param {string} startDate - 开始日期（可选）
|
|
|
* @param {string} endDate - 结束日期（可选）
|
|
|
* @returns {Promise<object>} 统计信息
|
|
|
*/
|
|
|
static async getStatistics(startDate = null, endDate = null) {
|
|
|
try {
|
|
|
let whereClause = '';
|
|
|
const params = [];
|
|
|
|
|
|
if (startDate && endDate) {
|
|
|
whereClause = 'WHERE DATE(reservation_time) BETWEEN ? AND ?';
|
|
|
params.push(startDate, endDate);
|
|
|
}
|
|
|
|
|
|
// 总预订数和各状态数量
|
|
|
const [statusStats] = await pool.query(
|
|
|
`SELECT
|
|
|
COUNT(*) as total_reservations,
|
|
|
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_count,
|
|
|
SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) as confirmed_count,
|
|
|
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_count,
|
|
|
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_count,
|
|
|
SUM(CASE WHEN status = 'no_show' THEN 1 ELSE 0 END) as no_show_count
|
|
|
FROM reservations ${whereClause}`,
|
|
|
params
|
|
|
);
|
|
|
|
|
|
// 按类型统计
|
|
|
const [typeStats] = await pool.query(
|
|
|
`SELECT
|
|
|
type,
|
|
|
COUNT(*) as count,
|
|
|
SUM(guest_count) as total_guests
|
|
|
FROM reservations ${whereClause}
|
|
|
GROUP BY type`,
|
|
|
params
|
|
|
);
|
|
|
|
|
|
return {
|
|
|
...statusStats[0],
|
|
|
by_type: typeStats
|
|
|
};
|
|
|
} catch (error) {
|
|
|
throw new Error('查询预订统计失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取可用桌位列表
|
|
|
* @param {string} reservationTime - 预订时间
|
|
|
* @param {number} guestCount - 客人数量（可选，用于推荐合适的桌位）
|
|
|
* @returns {Promise<array>} 可用桌位列表
|
|
|
*/
|
|
|
static async getAvailableTables(reservationTime, guestCount = null) {
|
|
|
try {
|
|
|
// 获取所有已被预订的桌位（前后2小时内）
|
|
|
const [bookedTables] = await pool.query(
|
|
|
`SELECT DISTINCT table_number
|
|
|
FROM reservations
|
|
|
WHERE table_number IS NOT NULL
|
|
|
AND type = 'dine_in'
|
|
|
AND status IN ('pending', 'confirmed')
|
|
|
AND ABS(TIMESTAMPDIFF(MINUTE, reservation_time, ?)) < 120`,
|
|
|
[reservationTime]
|
|
|
);
|
|
|
|
|
|
const bookedTableNumbers = bookedTables.map(t => t.table_number);
|
|
|
|
|
|
// 简化版：返回1-20号桌中未被预订的桌位
|
|
|
const allTables = Array.from({ length: 20 }, (_, i) => ({
|
|
|
table_number: i + 1,
|
|
|
capacity: i < 10 ? 4 : 8, // 前10桌4人桌，后10桌8人桌
|
|
|
is_available: !bookedTableNumbers.includes(i + 1)
|
|
|
}));
|
|
|
|
|
|
// 如果提供了客人数量，优先推荐合适容量的桌位
|
|
|
if (guestCount) {
|
|
|
return allTables
|
|
|
.filter(t => t.is_available)
|
|
|
.sort((a, b) => {
|
|
|
const aDiff = Math.abs(a.capacity - guestCount);
|
|
|
const bDiff = Math.abs(b.capacity - guestCount);
|
|
|
return aDiff - bDiff;
|
|
|
});
|
|
|
}
|
|
|
|
|
|
return allTables.filter(t => t.is_available);
|
|
|
} catch (error) {
|
|
|
throw new Error('查询可用桌位失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
module.exports = Reservation;
|
