|
|
/**
|
|
|
* 菜单数据模型
|
|
|
*
|
|
|
* 功能：封装菜单表和菜单-菜品关联表的数据库操作
|
|
|
*/
|
|
|
|
|
|
const { pool } = require('../config/database');
|
|
|
|
|
|
/**
|
|
|
* 菜单模型类
|
|
|
*/
|
|
|
class Menu {
|
|
|
/**
|
|
|
* 根据ID查找菜单
|
|
|
* @param {number} id - 菜单ID
|
|
|
* @returns {Promise<object|null>} 菜单对象或null
|
|
|
*/
|
|
|
static async findById(id) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
'SELECT * FROM menus WHERE 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,
|
|
|
type,
|
|
|
is_active
|
|
|
} = options;
|
|
|
|
|
|
const offset = (page - 1) * pageSize;
|
|
|
|
|
|
// 构建WHERE条件
|
|
|
const conditions = [];
|
|
|
const params = [];
|
|
|
|
|
|
if (type) {
|
|
|
conditions.push('type = ?');
|
|
|
params.push(type);
|
|
|
}
|
|
|
if (is_active !== undefined) {
|
|
|
conditions.push('is_active = ?');
|
|
|
params.push(is_active);
|
|
|
}
|
|
|
|
|
|
const whereClause = conditions.length > 0
|
|
|
? 'WHERE ' + conditions.join(' AND ')
|
|
|
: '';
|
|
|
|
|
|
// 查询总数
|
|
|
const [countResult] = await pool.query(
|
|
|
`SELECT COUNT(*) as total FROM menus ${whereClause}`,
|
|
|
params
|
|
|
);
|
|
|
const total = countResult[0].total;
|
|
|
|
|
|
// 查询列表
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT * FROM menus ${whereClause}
|
|
|
ORDER BY created_at 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} menuData - 菜单数据
|
|
|
* @returns {Promise<object>} 创建的菜单
|
|
|
*/
|
|
|
static async create(menuData) {
|
|
|
try {
|
|
|
const {
|
|
|
name,
|
|
|
type,
|
|
|
start_date,
|
|
|
end_date,
|
|
|
is_active = 1
|
|
|
} = menuData;
|
|
|
|
|
|
const [result] = await pool.query(
|
|
|
`INSERT INTO menus (name, type, start_date, end_date, is_active)
|
|
|
VALUES (?, ?, ?, ?, ?)`,
|
|
|
[name, type, start_date, end_date, is_active]
|
|
|
);
|
|
|
|
|
|
return await this.findById(result.insertId);
|
|
|
} catch (error) {
|
|
|
throw new Error('创建菜单失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 更新菜单
|
|
|
* @param {number} id - 菜单ID
|
|
|
* @param {object} menuData - 要更新的数据
|
|
|
* @returns {Promise<object>} 更新后的菜单
|
|
|
*/
|
|
|
static async update(id, menuData) {
|
|
|
try {
|
|
|
const { name, type, start_date, end_date, is_active } = menuData;
|
|
|
|
|
|
const updates = [];
|
|
|
const values = [];
|
|
|
|
|
|
if (name !== undefined) {
|
|
|
updates.push('name = ?');
|
|
|
values.push(name);
|
|
|
}
|
|
|
if (type !== undefined) {
|
|
|
updates.push('type = ?');
|
|
|
values.push(type);
|
|
|
}
|
|
|
if (start_date !== undefined) {
|
|
|
updates.push('start_date = ?');
|
|
|
values.push(start_date);
|
|
|
}
|
|
|
if (end_date !== undefined) {
|
|
|
updates.push('end_date = ?');
|
|
|
values.push(end_date);
|
|
|
}
|
|
|
if (is_active !== undefined) {
|
|
|
updates.push('is_active = ?');
|
|
|
values.push(is_active);
|
|
|
}
|
|
|
|
|
|
if (updates.length === 0) {
|
|
|
throw new Error('没有要更新的数据');
|
|
|
}
|
|
|
|
|
|
values.push(id);
|
|
|
|
|
|
await pool.query(
|
|
|
`UPDATE menus SET ${updates.join(', ')} WHERE id = ?`,
|
|
|
values
|
|
|
);
|
|
|
|
|
|
return await this.findById(id);
|
|
|
} catch (error) {
|
|
|
throw new Error('更新菜单失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 删除菜单
|
|
|
* @param {number} id - 菜单ID
|
|
|
* @returns {Promise<boolean>} 是否成功
|
|
|
*/
|
|
|
static async delete(id) {
|
|
|
try {
|
|
|
// 删除菜单会自动删除关联的菜品（CASCADE）
|
|
|
await pool.query('DELETE FROM menus WHERE id = ?', [id]);
|
|
|
return true;
|
|
|
} catch (error) {
|
|
|
throw new Error('删除菜单失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取菜单中的菜品列表
|
|
|
* @param {number} menuId - 菜单ID
|
|
|
* @returns {Promise<array>} 菜品列表
|
|
|
*/
|
|
|
static async getMenuDishes(menuId) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
md.id as menu_dish_id,
|
|
|
md.special_price,
|
|
|
d.id as dish_id,
|
|
|
d.name,
|
|
|
d.price as original_price,
|
|
|
d.image,
|
|
|
d.description,
|
|
|
d.taste,
|
|
|
d.cooking_time,
|
|
|
d.is_available,
|
|
|
dc.name as category_name
|
|
|
FROM menu_dishes md
|
|
|
JOIN dishes d ON md.dish_id = d.id
|
|
|
LEFT JOIN dish_categories dc ON d.category_id = dc.id
|
|
|
WHERE md.menu_id = ?
|
|
|
ORDER BY d.category_id, d.id`,
|
|
|
[menuId]
|
|
|
);
|
|
|
|
|
|
// 计算实际价格
|
|
|
return rows.map(row => ({
|
|
|
...row,
|
|
|
effective_price: row.special_price || row.original_price
|
|
|
}));
|
|
|
} catch (error) {
|
|
|
throw new Error('查询菜单菜品失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 为菜单添加菜品
|
|
|
* @param {number} menuId - 菜单ID
|
|
|
* @param {number} dishId - 菜品ID
|
|
|
* @param {number} specialPrice - 特价（可选）
|
|
|
* @returns {Promise<object>} 添加的记录
|
|
|
*/
|
|
|
static async addDish(menuId, dishId, specialPrice = null) {
|
|
|
try {
|
|
|
// 检查是否已存在
|
|
|
const [existing] = await pool.query(
|
|
|
'SELECT id FROM menu_dishes WHERE menu_id = ? AND dish_id = ?',
|
|
|
[menuId, dishId]
|
|
|
);
|
|
|
|
|
|
if (existing.length > 0) {
|
|
|
throw new Error('该菜品已在菜单中');
|
|
|
}
|
|
|
|
|
|
const [result] = await pool.query(
|
|
|
'INSERT INTO menu_dishes (menu_id, dish_id, special_price) VALUES (?, ?, ?)',
|
|
|
[menuId, dishId, specialPrice]
|
|
|
);
|
|
|
|
|
|
// 返回添加的菜品信息
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
md.id,
|
|
|
md.special_price,
|
|
|
d.id as dish_id,
|
|
|
d.name,
|
|
|
d.price as original_price
|
|
|
FROM menu_dishes md
|
|
|
JOIN dishes d ON md.dish_id = d.id
|
|
|
WHERE md.id = ?`,
|
|
|
[result.insertId]
|
|
|
);
|
|
|
|
|
|
return rows[0];
|
|
|
} catch (error) {
|
|
|
throw new Error('添加菜品到菜单失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 从菜单移除菜品
|
|
|
* @param {number} menuId - 菜单ID
|
|
|
* @param {number} dishId - 菜品ID
|
|
|
* @returns {Promise<boolean>} 是否成功
|
|
|
*/
|
|
|
static async removeDish(menuId, dishId) {
|
|
|
try {
|
|
|
const [result] = await pool.query(
|
|
|
'DELETE FROM menu_dishes WHERE menu_id = ? AND dish_id = ?',
|
|
|
[menuId, dishId]
|
|
|
);
|
|
|
|
|
|
if (result.affectedRows === 0) {
|
|
|
throw new Error('菜品不在该菜单中');
|
|
|
}
|
|
|
|
|
|
return true;
|
|
|
} catch (error) {
|
|
|
throw new Error('从菜单移除菜品失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 更新菜单中菜品的特价
|
|
|
* @param {number} menuId - 菜单ID
|
|
|
* @param {number} dishId - 菜品ID
|
|
|
* @param {number} specialPrice - 特价
|
|
|
* @returns {Promise<boolean>} 是否成功
|
|
|
*/
|
|
|
static async updateDishPrice(menuId, dishId, specialPrice) {
|
|
|
try {
|
|
|
const [result] = await pool.query(
|
|
|
'UPDATE menu_dishes SET special_price = ? WHERE menu_id = ? AND dish_id = ?',
|
|
|
[specialPrice, menuId, dishId]
|
|
|
);
|
|
|
|
|
|
if (result.affectedRows === 0) {
|
|
|
throw new Error('菜品不在该菜单中');
|
|
|
}
|
|
|
|
|
|
return true;
|
|
|
} catch (error) {
|
|
|
throw new Error('更新菜品特价失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 批量添加菜品到菜单
|
|
|
* @param {number} menuId - 菜单ID
|
|
|
* @param {array} dishes - 菜品数组 [{dish_id, special_price}]
|
|
|
* @returns {Promise<number>} 成功添加的数量
|
|
|
*/
|
|
|
static async addDishes(menuId, dishes) {
|
|
|
const connection = await pool.getConnection();
|
|
|
|
|
|
try {
|
|
|
await connection.beginTransaction();
|
|
|
|
|
|
let successCount = 0;
|
|
|
|
|
|
for (const dish of dishes) {
|
|
|
try {
|
|
|
await connection.query(
|
|
|
'INSERT INTO menu_dishes (menu_id, dish_id, special_price) VALUES (?, ?, ?)',
|
|
|
[menuId, dish.dish_id, dish.special_price || null]
|
|
|
);
|
|
|
successCount++;
|
|
|
} catch (err) {
|
|
|
// 忽略已存在的记录
|
|
|
if (err.code !== 'ER_DUP_ENTRY') {
|
|
|
throw err;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
await connection.commit();
|
|
|
return successCount;
|
|
|
} catch (error) {
|
|
|
await connection.rollback();
|
|
|
throw new Error('批量添加菜品失败: ' + error.message);
|
|
|
} finally {
|
|
|
connection.release();
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取当前有效的菜单
|
|
|
* @param {string} type - 菜单类型
|
|
|
* @returns {Promise<array>} 菜单列表
|
|
|
*/
|
|
|
static async getActiveMenus(type = null) {
|
|
|
try {
|
|
|
const today = new Date().toISOString().split('T')[0];
|
|
|
|
|
|
let query = `
|
|
|
SELECT * FROM menus
|
|
|
WHERE is_active = 1
|
|
|
AND (start_date IS NULL OR start_date <= ?)
|
|
|
AND (end_date IS NULL OR end_date >= ?)
|
|
|
`;
|
|
|
|
|
|
const params = [today, today];
|
|
|
|
|
|
if (type) {
|
|
|
query += ' AND type = ?';
|
|
|
params.push(type);
|
|
|
}
|
|
|
|
|
|
query += ' ORDER BY created_at DESC';
|
|
|
|
|
|
const [rows] = await pool.query(query, params);
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询有效菜单失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
module.exports = Menu;
|
