You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

690 lines
17 KiB

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

/**
* 会员数据模型
*
* 功能:封装会员表、优惠券表和用户优惠券关联表的数据库操作
*/
const { pool } = require('../config/database');
/**
* 会员模型类
*/
class Member {
/**
* 根据用户ID查找会员信息
* @param {number} userId - 用户ID
* @returns {Promise<object|null>} 会员对象或null
*/
static async findByUserId(userId) {
try {
const [rows] = await pool.query(
`SELECT
m.*,
u.username,
u.email,
u.phone
FROM members m
JOIN users u ON m.user_id = u.id
WHERE m.user_id = ?`,
[userId]
);
return rows[0] || null;
} catch (error) {
throw new Error('查询会员信息失败: ' + error.message);
}
}
/**
* 根据会员ID查找会员信息
* @param {number} id - 会员ID
* @returns {Promise<object|null>} 会员对象或null
*/
static async findById(id) {
try {
const [rows] = await pool.query(
`SELECT
m.*,
u.username,
u.email,
u.phone
FROM members m
JOIN users u ON m.user_id = u.id
WHERE m.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,
level,
search
} = options;
const offset = (page - 1) * pageSize;
// 构建WHERE条件
const conditions = [];
const params = [];
if (level) {
conditions.push('m.level = ?');
params.push(level);
}
if (search) {
conditions.push('(u.username LIKE ? OR u.phone LIKE ? OR u.email LIKE ?)');
params.push(`%${search}%`, `%${search}%`, `%${search}%`);
}
const whereClause = conditions.length > 0
? 'WHERE ' + conditions.join(' AND ')
: '';
// 查询总数
const [countResult] = await pool.query(
`SELECT COUNT(*) as total
FROM members m
JOIN users u ON m.user_id = u.id
${whereClause}`,
params
);
const total = countResult[0].total;
// 查询列表
const [rows] = await pool.query(
`SELECT
m.*,
u.username,
u.email,
u.phone
FROM members m
JOIN users u ON m.user_id = u.id
${whereClause}
ORDER BY m.total_points DESC
LIMIT ? OFFSET ?`,
[...params, pageSize, offset]
);
return {
list: rows,
total,
page: parseInt(page),
pageSize: parseInt(pageSize)
};
} catch (error) {
throw new Error('查询会员列表失败: ' + error.message);
}
}
/**
* 创建会员
* @param {number} userId - 用户ID
* @returns {Promise<object>} 创建的会员
*/
static async create(userId) {
try {
const [result] = await pool.query(
`INSERT INTO members (user_id, level, points, total_points, total_spent)
VALUES (?, 'bronze', 0, 0, 0)`,
[userId]
);
return await this.findById(result.insertId);
} catch (error) {
throw new Error('创建会员失败: ' + error.message);
}
}
/**
* 更新会员等级
* @param {number} userId - 用户ID
* @param {string} level - 新等级
* @returns {Promise<object>} 更新后的会员
*/
static async updateLevel(userId, level) {
try {
await pool.query(
'UPDATE members SET level = ?, updated_at = NOW() WHERE user_id = ?',
[level, userId]
);
return await this.findByUserId(userId);
} catch (error) {
throw new Error('更新会员等级失败: ' + error.message);
}
}
/**
* 增加积分
* @param {number} userId - 用户ID
* @param {number} points - 积分数量
* @param {string} reason - 积分来源
* @returns {Promise<object>} 更新后的会员
*/
static async addPoints(userId, points, reason = '消费获得') {
const connection = await pool.getConnection();
try {
await connection.beginTransaction();
// 更新会员积分
await connection.query(
`UPDATE members
SET points = points + ?,
total_points = total_points + ?,
updated_at = NOW()
WHERE user_id = ?`,
[points, points, userId]
);
// 记录积分历史(如果需要可以创建积分历史表)
// 检查是否需要升级会员等级
const [member] = await connection.query(
'SELECT * FROM members WHERE user_id = ?',
[userId]
);
if (member.length > 0) {
const currentMember = member[0];
const newLevel = this.calculateLevel(currentMember.total_points);
if (newLevel !== currentMember.level) {
await connection.query(
'UPDATE members SET level = ? WHERE user_id = ?',
[newLevel, userId]
);
}
}
await connection.commit();
return await this.findByUserId(userId);
} catch (error) {
await connection.rollback();
throw new Error('增加积分失败: ' + error.message);
} finally {
connection.release();
}
}
/**
* 扣减积分
* @param {number} userId - 用户ID
* @param {number} points - 积分数量
* @param {string} reason - 扣减原因
* @returns {Promise<object>} 更新后的会员
*/
static async deductPoints(userId, points, reason = '兑换使用') {
try {
// 检查积分是否足够
const member = await this.findByUserId(userId);
if (!member) {
throw new Error('会员不存在');
}
if (member.points < points) {
throw new Error('积分不足');
}
await pool.query(
`UPDATE members
SET points = points - ?,
updated_at = NOW()
WHERE user_id = ?`,
[points, userId]
);
return await this.findByUserId(userId);
} catch (error) {
throw new Error('扣减积分失败: ' + error.message);
}
}
/**
* 更新消费金额
* @param {number} userId - 用户ID
* @param {number} amount - 消费金额
* @returns {Promise<object>} 更新后的会员
*/
static async updateSpent(userId, amount) {
try {
await pool.query(
`UPDATE members
SET total_spent = total_spent + ?,
updated_at = NOW()
WHERE user_id = ?`,
[amount, userId]
);
return await this.findByUserId(userId);
} catch (error) {
throw new Error('更新消费金额失败: ' + error.message);
}
}
/**
* 根据累计积分计算会员等级
* @param {number} totalPoints - 累计积分
* @returns {string} 会员等级
*/
static calculateLevel(totalPoints) {
if (totalPoints >= 10000) {
return 'diamond';
} else if (totalPoints >= 5000) {
return 'gold';
} else if (totalPoints >= 1000) {
return 'silver';
} else {
return 'bronze';
}
}
/**
* 获取会员统计信息
* @returns {Promise<object>} 统计信息
*/
static async getStatistics() {
try {
// 总会员数和各等级数量
const [levelStats] = await pool.query(
`SELECT
COUNT(*) as total_members,
SUM(CASE WHEN level = 'bronze' THEN 1 ELSE 0 END) as bronze_count,
SUM(CASE WHEN level = 'silver' THEN 1 ELSE 0 END) as silver_count,
SUM(CASE WHEN level = 'gold' THEN 1 ELSE 0 END) as gold_count,
SUM(CASE WHEN level = 'diamond' THEN 1 ELSE 0 END) as diamond_count,
SUM(total_spent) as total_revenue,
AVG(total_spent) as avg_spent_per_member
FROM members`
);
return levelStats[0];
} catch (error) {
throw new Error('查询会员统计失败: ' + error.message);
}
}
/**
* 检查用户是否已是会员
* @param {number} userId - 用户ID
* @returns {Promise<boolean>} 是否是会员
*/
static async isMember(userId) {
try {
const [rows] = await pool.query(
'SELECT id FROM members WHERE user_id = ?',
[userId]
);
return rows.length > 0;
} catch (error) {
throw new Error('检查会员状态失败: ' + error.message);
}
}
/**
* 获取会员优惠折扣
* @param {string} level - 会员等级
* @returns {number} 折扣率0.95表示95折
*/
static getDiscount(level) {
const discounts = {
bronze: 1.0, // 无折扣
silver: 0.95, // 95折
gold: 0.9, // 9折
diamond: 0.85 // 85折
};
return discounts[level] || 1.0;
}
}
/**
* 优惠券模型类
*/
class Coupon {
/**
* 根据ID查找优惠券
* @param {number} id - 优惠券ID
* @returns {Promise<object|null>} 优惠券对象或null
*/
static async findById(id) {
try {
const [rows] = await pool.query(
'SELECT * FROM coupons 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;
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 coupons ${whereClause}`,
params
);
const total = countResult[0].total;
const [rows] = await pool.query(
`SELECT * FROM coupons ${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} couponData - 优惠券数据
* @returns {Promise<object>} 创建的优惠券
*/
static async create(couponData) {
try {
const {
name,
type,
discount_value,
min_amount,
max_discount,
valid_from,
valid_to,
total_quantity,
points_required,
is_active
} = couponData;
const [result] = await pool.query(
`INSERT INTO coupons
(name, type, discount_value, min_amount, max_discount, valid_from,
valid_to, total_quantity, remaining_quantity, points_required, is_active)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
name,
type,
discount_value,
min_amount || null,
max_discount || null,
valid_from,
valid_to,
total_quantity,
total_quantity, // remaining_quantity初始等于total_quantity
points_required || 0,
is_active !== undefined ? is_active : 1
]
);
return await this.findById(result.insertId);
} catch (error) {
throw new Error('创建优惠券失败: ' + error.message);
}
}
/**
* 更新优惠券
* @param {number} id - 优惠券ID
* @param {object} couponData - 要更新的数据
* @returns {Promise<object>} 更新后的优惠券
*/
static async update(id, couponData) {
try {
const updates = [];
const values = [];
const fields = ['name', 'discount_value', 'min_amount', 'max_discount',
'valid_from', 'valid_to', 'total_quantity', 'points_required', 'is_active'];
fields.forEach(field => {
if (couponData[field] !== undefined) {
updates.push(`${field} = ?`);
values.push(couponData[field]);
}
});
if (updates.length === 0) {
throw new Error('没有要更新的数据');
}
values.push(id);
await pool.query(
`UPDATE coupons 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 {
await pool.query('DELETE FROM coupons WHERE id = ?', [id]);
return true;
} catch (error) {
throw new Error('删除优惠券失败: ' + error.message);
}
}
/**
* 用户领取优惠券
* @param {number} userId - 用户ID
* @param {number} couponId - 优惠券ID
* @returns {Promise<object>} 用户优惠券记录
*/
static async claimByUser(userId, couponId) {
const connection = await pool.getConnection();
try {
await connection.beginTransaction();
// 检查优惠券是否存在且有效
const [coupons] = await connection.query(
'SELECT * FROM coupons WHERE id = ? AND is_active = 1',
[couponId]
);
if (coupons.length === 0) {
throw new Error('优惠券不存在或已失效');
}
const coupon = coupons[0];
// 检查剩余数量
if (coupon.remaining_quantity <= 0) {
throw new Error('优惠券已被领完');
}
// 检查用户是否已领取
const [existing] = await connection.query(
'SELECT id FROM user_coupons WHERE user_id = ? AND coupon_id = ?',
[userId, couponId]
);
if (existing.length > 0) {
throw new Error('您已领取过该优惠券');
}
// 检查积分是否足够
if (coupon.points_required > 0) {
const [members] = await connection.query(
'SELECT points FROM members WHERE user_id = ?',
[userId]
);
if (members.length === 0 || members[0].points < coupon.points_required) {
throw new Error('积分不足,无法领取');
}
// 扣减积分
await connection.query(
'UPDATE members SET points = points - ? WHERE user_id = ?',
[coupon.points_required, userId]
);
}
// 创建用户优惠券记录
const [result] = await connection.query(
`INSERT INTO user_coupons (user_id, coupon_id, status)
VALUES (?, ?, 'unused')`,
[userId, couponId]
);
// 减少优惠券剩余数量
await connection.query(
'UPDATE coupons SET remaining_quantity = remaining_quantity - 1 WHERE id = ?',
[couponId]
);
await connection.commit();
// 返回用户优惠券记录
const [userCoupon] = await connection.query(
`SELECT
uc.*,
c.name,
c.type,
c.discount_value,
c.min_amount,
c.max_discount,
c.valid_from,
c.valid_to
FROM user_coupons uc
JOIN coupons c ON uc.coupon_id = c.id
WHERE uc.id = ?`,
[result.insertId]
);
return userCoupon[0];
} catch (error) {
await connection.rollback();
throw new Error('领取优惠券失败: ' + error.message);
} finally {
connection.release();
}
}
/**
* 获取用户的优惠券列表
* @param {number} userId - 用户ID
* @param {string} status - 状态筛选(可选)
* @returns {Promise<array>} 用户优惠券列表
*/
static async getUserCoupons(userId, status = null) {
try {
let query = `
SELECT
uc.*,
c.name,
c.type,
c.discount_value,
c.min_amount,
c.max_discount,
c.valid_from,
c.valid_to
FROM user_coupons uc
JOIN coupons c ON uc.coupon_id = c.id
WHERE uc.user_id = ?
`;
const params = [userId];
if (status) {
query += ' AND uc.status = ?';
params.push(status);
}
query += ' ORDER BY uc.created_at DESC';
const [rows] = await pool.query(query, params);
return rows;
} catch (error) {
throw new Error('查询用户优惠券失败: ' + error.message);
}
}
/**
* 使用优惠券
* @param {number} userCouponId - 用户优惠券ID
* @param {number} orderId - 订单ID
* @returns {Promise<boolean>} 是否成功
*/
static async useCoupon(userCouponId, orderId) {
try {
await pool.query(
`UPDATE user_coupons
SET status = 'used', used_at = NOW(), order_id = ?
WHERE id = ? AND status = 'unused'`,
[orderId, userCouponId]
);
return true;
} catch (error) {
throw new Error('使用优惠券失败: ' + error.message);
}
}
}
module.exports = { Member, Coupon };