|
|
/**
|
|
|
* 统计分析数据模型
|
|
|
*
|
|
|
* 功能：封装各类统计分析的数据库操作
|
|
|
*/
|
|
|
|
|
|
const { pool } = require('../config/database');
|
|
|
|
|
|
/**
|
|
|
* 统计分析模型类
|
|
|
*/
|
|
|
class Analytics {
|
|
|
/**
|
|
|
* 获取综合仪表盘数据
|
|
|
* @param {string} startDate - 开始日期
|
|
|
* @param {string} endDate - 结束日期
|
|
|
* @returns {Promise<object>} 仪表盘数据
|
|
|
*/
|
|
|
static async getDashboard(startDate = null, endDate = null) {
|
|
|
try {
|
|
|
const today = new Date().toISOString().split('T')[0];
|
|
|
const start = startDate || today;
|
|
|
const end = endDate || today;
|
|
|
|
|
|
// 订单统计
|
|
|
const [orderStats] = await pool.query(
|
|
|
`SELECT
|
|
|
COUNT(*) as total_orders,
|
|
|
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_orders,
|
|
|
SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) as confirmed_orders,
|
|
|
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_orders,
|
|
|
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) as paid_orders,
|
|
|
SUM(final_amount) as total_revenue,
|
|
|
AVG(final_amount) as avg_order_amount
|
|
|
FROM orders
|
|
|
WHERE DATE(created_at) BETWEEN ? AND ?`,
|
|
|
[start, end]
|
|
|
);
|
|
|
|
|
|
// 支付统计
|
|
|
const [paymentStats] = await pool.query(
|
|
|
`SELECT
|
|
|
COUNT(*) as total_payments,
|
|
|
SUM(amount) as total_payment_amount,
|
|
|
SUM(CASE WHEN payment_method = 'cash' THEN amount ELSE 0 END) as cash_amount,
|
|
|
SUM(CASE WHEN payment_method = 'card' THEN amount ELSE 0 END) as card_amount,
|
|
|
SUM(CASE WHEN payment_method = 'wechat' THEN amount ELSE 0 END) as wechat_amount,
|
|
|
SUM(CASE WHEN payment_method = 'alipay' THEN amount ELSE 0 END) as alipay_amount
|
|
|
FROM payments
|
|
|
WHERE status = 'paid' AND DATE(paid_at) BETWEEN ? AND ?`,
|
|
|
[start, end]
|
|
|
);
|
|
|
|
|
|
// 会员统计
|
|
|
const [memberStats] = await pool.query(
|
|
|
`SELECT
|
|
|
COUNT(*) as total_members,
|
|
|
SUM(CASE WHEN level = 'bronze' THEN 1 ELSE 0 END) as bronze_members,
|
|
|
SUM(CASE WHEN level = 'silver' THEN 1 ELSE 0 END) as silver_members,
|
|
|
SUM(CASE WHEN level = 'gold' THEN 1 ELSE 0 END) as gold_members,
|
|
|
SUM(CASE WHEN level = 'diamond' THEN 1 ELSE 0 END) as diamond_members
|
|
|
FROM members`
|
|
|
);
|
|
|
|
|
|
// 预订统计
|
|
|
const [reservationStats] = await pool.query(
|
|
|
`SELECT
|
|
|
COUNT(*) as total_reservations,
|
|
|
SUM(CASE WHEN type = 'dine_in' THEN 1 ELSE 0 END) as dine_in_count,
|
|
|
SUM(CASE WHEN type = 'takeaway' THEN 1 ELSE 0 END) as takeaway_count,
|
|
|
SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) as confirmed_count
|
|
|
FROM reservations
|
|
|
WHERE DATE(reservation_time) BETWEEN ? AND ?`,
|
|
|
[start, end]
|
|
|
);
|
|
|
|
|
|
// 库存预警
|
|
|
const [inventoryAlerts] = await pool.query(
|
|
|
`SELECT COUNT(*) as low_stock_count
|
|
|
FROM inventory
|
|
|
WHERE quantity <= alert_quantity`
|
|
|
);
|
|
|
|
|
|
return {
|
|
|
period: { start_date: start, end_date: end },
|
|
|
orders: orderStats[0],
|
|
|
payments: paymentStats[0],
|
|
|
members: memberStats[0],
|
|
|
reservations: reservationStats[0],
|
|
|
inventory: inventoryAlerts[0]
|
|
|
};
|
|
|
} catch (error) {
|
|
|
throw new Error('获取仪表盘数据失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取销售趋势（按日期）
|
|
|
* @param {string} startDate - 开始日期
|
|
|
* @param {string} endDate - 结束日期
|
|
|
* @returns {Promise<array>} 每日销售数据
|
|
|
*/
|
|
|
static async getSalesTrend(startDate, endDate) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
DATE(paid_at) as date,
|
|
|
COUNT(*) as order_count,
|
|
|
SUM(amount) as revenue,
|
|
|
AVG(amount) as avg_amount
|
|
|
FROM payments
|
|
|
WHERE status = 'paid'
|
|
|
AND DATE(paid_at) BETWEEN ? AND ?
|
|
|
GROUP BY DATE(paid_at)
|
|
|
ORDER BY date ASC`,
|
|
|
[startDate, endDate]
|
|
|
);
|
|
|
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('获取销售趋势失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取热门菜品排行
|
|
|
* @param {number} limit - 限制数量
|
|
|
* @param {string} startDate - 开始日期（可选）
|
|
|
* @param {string} endDate - 结束日期（可选）
|
|
|
* @returns {Promise<array>} 热门菜品列表
|
|
|
*/
|
|
|
static async getTopDishes(limit = 10, startDate = null, endDate = null) {
|
|
|
try {
|
|
|
let query = `
|
|
|
SELECT
|
|
|
oi.dish_id,
|
|
|
oi.dish_name,
|
|
|
COUNT(*) as order_count,
|
|
|
SUM(oi.quantity) as total_quantity,
|
|
|
SUM(oi.subtotal) as total_revenue,
|
|
|
AVG(oi.price) as avg_price
|
|
|
FROM order_items oi
|
|
|
JOIN orders o ON oi.order_id = o.id
|
|
|
WHERE o.status IN ('completed', 'paid')
|
|
|
`;
|
|
|
|
|
|
const params = [];
|
|
|
|
|
|
if (startDate && endDate) {
|
|
|
query += ' AND DATE(o.created_at) BETWEEN ? AND ?';
|
|
|
params.push(startDate, endDate);
|
|
|
}
|
|
|
|
|
|
query += `
|
|
|
GROUP BY oi.dish_id, oi.dish_name
|
|
|
ORDER BY total_quantity DESC
|
|
|
LIMIT ?
|
|
|
`;
|
|
|
params.push(limit);
|
|
|
|
|
|
const [rows] = await pool.query(query, params);
|
|
|
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('获取热门菜品失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取分类销售统计
|
|
|
* @param {string} startDate - 开始日期（可选）
|
|
|
* @param {string} endDate - 结束日期（可选）
|
|
|
* @returns {Promise<array>} 分类销售数据
|
|
|
*/
|
|
|
static async getCategorySales(startDate = null, endDate = null) {
|
|
|
try {
|
|
|
let query = `
|
|
|
SELECT
|
|
|
dc.id as category_id,
|
|
|
dc.name as category_name,
|
|
|
COUNT(DISTINCT oi.order_id) as order_count,
|
|
|
SUM(oi.quantity) as total_quantity,
|
|
|
SUM(oi.subtotal) as total_revenue
|
|
|
FROM order_items oi
|
|
|
JOIN dishes d ON oi.dish_id = d.id
|
|
|
JOIN dish_categories dc ON d.category_id = dc.id
|
|
|
JOIN orders o ON oi.order_id = o.id
|
|
|
WHERE o.status IN ('completed', 'paid')
|
|
|
`;
|
|
|
|
|
|
const params = [];
|
|
|
|
|
|
if (startDate && endDate) {
|
|
|
query += ' AND DATE(o.created_at) BETWEEN ? AND ?';
|
|
|
params.push(startDate, endDate);
|
|
|
}
|
|
|
|
|
|
query += `
|
|
|
GROUP BY dc.id, dc.name
|
|
|
ORDER BY total_revenue DESC
|
|
|
`;
|
|
|
|
|
|
const [rows] = await pool.query(query, params);
|
|
|
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('获取分类销售统计失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取营业时段分析
|
|
|
* @param {string} startDate - 开始日期（可选）
|
|
|
* @param {string} endDate - 结束日期（可选）
|
|
|
* @returns {Promise<array>} 时段销售数据
|
|
|
*/
|
|
|
static async getHourlyAnalysis(startDate = null, endDate = null) {
|
|
|
try {
|
|
|
let query = `
|
|
|
SELECT
|
|
|
HOUR(created_at) as hour,
|
|
|
COUNT(*) as order_count,
|
|
|
SUM(final_amount) as revenue,
|
|
|
AVG(final_amount) as avg_amount
|
|
|
FROM orders
|
|
|
WHERE status IN ('completed', 'paid')
|
|
|
`;
|
|
|
|
|
|
const params = [];
|
|
|
|
|
|
if (startDate && endDate) {
|
|
|
query += ' AND DATE(created_at) BETWEEN ? AND ?';
|
|
|
params.push(startDate, endDate);
|
|
|
}
|
|
|
|
|
|
query += `
|
|
|
GROUP BY HOUR(created_at)
|
|
|
ORDER BY hour ASC
|
|
|
`;
|
|
|
|
|
|
const [rows] = await pool.query(query, params);
|
|
|
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('获取时段分析失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取会员消费排行
|
|
|
* @param {number} limit - 限制数量
|
|
|
* @param {string} startDate - 开始日期（可选）
|
|
|
* @param {string} endDate - 结束日期（可选）
|
|
|
* @returns {Promise<array>} 会员消费排行
|
|
|
*/
|
|
|
static async getTopMembers(limit = 10, startDate = null, endDate = null) {
|
|
|
try {
|
|
|
let query = `
|
|
|
SELECT
|
|
|
m.user_id,
|
|
|
u.username,
|
|
|
m.level,
|
|
|
COUNT(o.id) as order_count,
|
|
|
SUM(o.final_amount) as total_spent,
|
|
|
AVG(o.final_amount) as avg_spent,
|
|
|
m.points as current_points
|
|
|
FROM members m
|
|
|
JOIN users u ON m.user_id = u.id
|
|
|
LEFT JOIN orders o ON m.user_id = o.customer_id
|
|
|
`;
|
|
|
|
|
|
const params = [];
|
|
|
const conditions = ["o.status IN ('completed', 'paid')"];
|
|
|
|
|
|
if (startDate && endDate) {
|
|
|
conditions.push('DATE(o.created_at) BETWEEN ? AND ?');
|
|
|
params.push(startDate, endDate);
|
|
|
}
|
|
|
|
|
|
if (conditions.length > 0) {
|
|
|
query += ' WHERE ' + conditions.join(' AND ');
|
|
|
}
|
|
|
|
|
|
query += `
|
|
|
GROUP BY m.user_id, u.username, m.level, m.points
|
|
|
ORDER BY total_spent DESC
|
|
|
LIMIT ?
|
|
|
`;
|
|
|
params.push(limit);
|
|
|
|
|
|
const [rows] = await pool.query(query, params);
|
|
|
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('获取会员排行失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取订单类型统计
|
|
|
* @param {string} startDate - 开始日期（可选）
|
|
|
* @param {string} endDate - 结束日期（可选）
|
|
|
* @returns {Promise<array>} 订单类型数据
|
|
|
*/
|
|
|
static async getOrderTypeStats(startDate = null, endDate = null) {
|
|
|
try {
|
|
|
let query = `
|
|
|
SELECT
|
|
|
order_type,
|
|
|
COUNT(*) as count,
|
|
|
SUM(final_amount) as revenue,
|
|
|
AVG(final_amount) as avg_amount
|
|
|
FROM orders
|
|
|
WHERE status IN ('completed', 'paid')
|
|
|
`;
|
|
|
|
|
|
const params = [];
|
|
|
|
|
|
if (startDate && endDate) {
|
|
|
query += ' AND DATE(created_at) BETWEEN ? AND ?';
|
|
|
params.push(startDate, endDate);
|
|
|
}
|
|
|
|
|
|
query += ' GROUP BY order_type';
|
|
|
|
|
|
const [rows] = await pool.query(query, params);
|
|
|
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('获取订单类型统计失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取支付方式统计
|
|
|
* @param {string} startDate - 开始日期（可选）
|
|
|
* @param {string} endDate - 结束日期（可选）
|
|
|
* @returns {Promise<array>} 支付方式数据
|
|
|
*/
|
|
|
static async getPaymentMethodStats(startDate = null, endDate = null) {
|
|
|
try {
|
|
|
let query = `
|
|
|
SELECT
|
|
|
payment_method,
|
|
|
COUNT(*) as count,
|
|
|
SUM(amount) as total_amount,
|
|
|
AVG(amount) as avg_amount
|
|
|
FROM payments
|
|
|
WHERE status = 'paid'
|
|
|
`;
|
|
|
|
|
|
const params = [];
|
|
|
|
|
|
if (startDate && endDate) {
|
|
|
query += ' AND DATE(paid_at) BETWEEN ? AND ?';
|
|
|
params.push(startDate, endDate);
|
|
|
}
|
|
|
|
|
|
query += ' GROUP BY payment_method ORDER BY total_amount DESC';
|
|
|
|
|
|
const [rows] = await pool.query(query, params);
|
|
|
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('获取支付方式统计失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取月度对比报表
|
|
|
* @param {number} months - 对比月份数（默认6个月）
|
|
|
* @returns {Promise<array>} 月度数据
|
|
|
*/
|
|
|
static async getMonthlyComparison(months = 6) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
DATE_FORMAT(paid_at, '%Y-%m') as month,
|
|
|
COUNT(*) as order_count,
|
|
|
SUM(amount) as revenue,
|
|
|
AVG(amount) as avg_amount
|
|
|
FROM payments
|
|
|
WHERE status = 'paid'
|
|
|
AND paid_at >= DATE_SUB(NOW(), INTERVAL ? MONTH)
|
|
|
GROUP BY DATE_FORMAT(paid_at, '%Y-%m')
|
|
|
ORDER BY month ASC`,
|
|
|
[months]
|
|
|
);
|
|
|
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('获取月度对比失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取库存价值报表
|
|
|
* @returns {Promise<object>} 库存价值数据
|
|
|
*/
|
|
|
static async getInventoryValue() {
|
|
|
try {
|
|
|
const [categoryStats] = await pool.query(
|
|
|
`SELECT
|
|
|
category,
|
|
|
COUNT(*) as item_count,
|
|
|
SUM(quantity * purchase_price) as total_value,
|
|
|
SUM(CASE WHEN quantity <= alert_quantity THEN 1 ELSE 0 END) as low_stock_count
|
|
|
FROM inventory
|
|
|
GROUP BY category
|
|
|
ORDER BY total_value DESC`
|
|
|
);
|
|
|
|
|
|
const [totalStats] = await pool.query(
|
|
|
`SELECT
|
|
|
COUNT(*) as total_items,
|
|
|
SUM(quantity * purchase_price) as total_value,
|
|
|
SUM(CASE WHEN quantity <= alert_quantity THEN 1 ELSE 0 END) as total_low_stock
|
|
|
FROM inventory`
|
|
|
);
|
|
|
|
|
|
return {
|
|
|
summary: totalStats[0],
|
|
|
by_category: categoryStats
|
|
|
};
|
|
|
} catch (error) {
|
|
|
throw new Error('获取库存价值报表失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取优惠券使用统计
|
|
|
* @param {string} startDate - 开始日期（可选）
|
|
|
* @param {string} endDate - 结束日期（可选）
|
|
|
* @returns {Promise<object>} 优惠券统计
|
|
|
*/
|
|
|
static async getCouponStats(startDate = null, endDate = null) {
|
|
|
try {
|
|
|
let whereClause = "WHERE uc.status = 'used'";
|
|
|
const params = [];
|
|
|
|
|
|
if (startDate && endDate) {
|
|
|
whereClause += ' AND DATE(uc.used_at) BETWEEN ? AND ?';
|
|
|
params.push(startDate, endDate);
|
|
|
}
|
|
|
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
c.id as coupon_id,
|
|
|
c.name as coupon_name,
|
|
|
c.type,
|
|
|
COUNT(*) as used_count,
|
|
|
c.total_quantity,
|
|
|
c.remaining_quantity
|
|
|
FROM user_coupons uc
|
|
|
JOIN coupons c ON uc.coupon_id = c.id
|
|
|
${whereClause}
|
|
|
GROUP BY c.id, c.name, c.type, c.total_quantity, c.remaining_quantity
|
|
|
ORDER BY used_count DESC`,
|
|
|
params
|
|
|
);
|
|
|
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('获取优惠券统计失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取预订转化率
|
|
|
* @param {string} startDate - 开始日期（可选）
|
|
|
* @param {string} endDate - 结束日期（可选）
|
|
|
* @returns {Promise<object>} 预订转化数据
|
|
|
*/
|
|
|
static async getReservationConversion(startDate = null, endDate = null) {
|
|
|
try {
|
|
|
let whereClause = '';
|
|
|
const params = [];
|
|
|
|
|
|
if (startDate && endDate) {
|
|
|
whereClause = 'WHERE DATE(reservation_time) BETWEEN ? AND ?';
|
|
|
params.push(startDate, endDate);
|
|
|
}
|
|
|
|
|
|
const [stats] = await pool.query(
|
|
|
`SELECT
|
|
|
COUNT(*) as total_reservations,
|
|
|
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 result = stats[0];
|
|
|
result.completion_rate = result.total_reservations > 0
|
|
|
? (result.completed_count / result.total_reservations * 100).toFixed(2)
|
|
|
: 0;
|
|
|
|
|
|
return result;
|
|
|
} catch (error) {
|
|
|
throw new Error('获取预订转化率失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
module.exports = Analytics;
|
