|
|
/**
|
|
|
* 支付数据模型
|
|
|
*
|
|
|
* 功能：封装支付表和发票表的数据库操作
|
|
|
*/
|
|
|
|
|
|
const { pool } = require('../config/database');
|
|
|
|
|
|
/**
|
|
|
* 支付模型类
|
|
|
*/
|
|
|
class Payment {
|
|
|
/**
|
|
|
* 根据ID查找支付记录
|
|
|
* @param {number} id - 支付ID
|
|
|
* @returns {Promise<object|null>} 支付对象或null
|
|
|
*/
|
|
|
static async findById(id) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
p.*,
|
|
|
o.order_no,
|
|
|
o.customer_id,
|
|
|
u.username as customer_name
|
|
|
FROM payments p
|
|
|
LEFT JOIN orders o ON p.order_id = o.id
|
|
|
LEFT JOIN users u ON o.customer_id = u.id
|
|
|
WHERE p.id = ?`,
|
|
|
[id]
|
|
|
);
|
|
|
return rows[0] || null;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询支付记录失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 根据订单ID查找支付记录
|
|
|
* @param {number} orderId - 订单ID
|
|
|
* @returns {Promise<object|null>} 支付对象或null
|
|
|
*/
|
|
|
static async findByOrderId(orderId) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
p.*,
|
|
|
o.order_no,
|
|
|
o.customer_id,
|
|
|
u.username as customer_name
|
|
|
FROM payments p
|
|
|
LEFT JOIN orders o ON p.order_id = o.id
|
|
|
LEFT JOIN users u ON o.customer_id = u.id
|
|
|
WHERE p.order_id = ?`,
|
|
|
[orderId]
|
|
|
);
|
|
|
return rows[0] || null;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询支付记录失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 根据交易号查找支付记录
|
|
|
* @param {string} transactionNo - 交易号
|
|
|
* @returns {Promise<object|null>} 支付对象或null
|
|
|
*/
|
|
|
static async findByTransactionNo(transactionNo) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
p.*,
|
|
|
o.order_no,
|
|
|
o.customer_id,
|
|
|
u.username as customer_name
|
|
|
FROM payments p
|
|
|
LEFT JOIN orders o ON p.order_id = o.id
|
|
|
LEFT JOIN users u ON o.customer_id = u.id
|
|
|
WHERE p.transaction_no = ?`,
|
|
|
[transactionNo]
|
|
|
);
|
|
|
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,
|
|
|
payment_method,
|
|
|
status,
|
|
|
start_date,
|
|
|
end_date
|
|
|
} = options;
|
|
|
|
|
|
const offset = (page - 1) * pageSize;
|
|
|
|
|
|
// 构建WHERE条件
|
|
|
const conditions = [];
|
|
|
const params = [];
|
|
|
|
|
|
if (payment_method) {
|
|
|
conditions.push('p.payment_method = ?');
|
|
|
params.push(payment_method);
|
|
|
}
|
|
|
|
|
|
if (status) {
|
|
|
conditions.push('p.status = ?');
|
|
|
params.push(status);
|
|
|
}
|
|
|
|
|
|
if (start_date && end_date) {
|
|
|
conditions.push('DATE(p.created_at) BETWEEN ? AND ?');
|
|
|
params.push(start_date, end_date);
|
|
|
}
|
|
|
|
|
|
const whereClause = conditions.length > 0
|
|
|
? 'WHERE ' + conditions.join(' AND ')
|
|
|
: '';
|
|
|
|
|
|
// 查询总数
|
|
|
const [countResult] = await pool.query(
|
|
|
`SELECT COUNT(*) as total
|
|
|
FROM payments p
|
|
|
LEFT JOIN orders o ON p.order_id = o.id
|
|
|
${whereClause}`,
|
|
|
params
|
|
|
);
|
|
|
const total = countResult[0].total;
|
|
|
|
|
|
// 查询列表
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
p.*,
|
|
|
o.order_no,
|
|
|
o.customer_id,
|
|
|
u.username as customer_name
|
|
|
FROM payments p
|
|
|
LEFT JOIN orders o ON p.order_id = o.id
|
|
|
LEFT JOIN users u ON o.customer_id = u.id
|
|
|
${whereClause}
|
|
|
ORDER BY p.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);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 生成交易号
|
|
|
* @returns {string} 交易号
|
|
|
*/
|
|
|
static generateTransactionNo() {
|
|
|
const timestamp = Date.now();
|
|
|
const random = Math.floor(Math.random() * 10000).toString().padStart(4, '0');
|
|
|
return `PAY${timestamp}${random}`;
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 创建支付记录
|
|
|
* @param {object} paymentData - 支付数据
|
|
|
* @returns {Promise<object>} 创建的支付记录
|
|
|
*/
|
|
|
static async create(paymentData) {
|
|
|
const connection = await pool.getConnection();
|
|
|
|
|
|
try {
|
|
|
await connection.beginTransaction();
|
|
|
|
|
|
const {
|
|
|
order_id,
|
|
|
payment_method,
|
|
|
amount
|
|
|
} = paymentData;
|
|
|
|
|
|
const transaction_no = this.generateTransactionNo();
|
|
|
|
|
|
// 创建支付记录
|
|
|
const [result] = await connection.query(
|
|
|
`INSERT INTO payments
|
|
|
(order_id, transaction_no, payment_method, amount, status)
|
|
|
VALUES (?, ?, ?, ?, 'pending')`,
|
|
|
[order_id, transaction_no, payment_method, amount]
|
|
|
);
|
|
|
|
|
|
await connection.commit();
|
|
|
|
|
|
return await this.findById(result.insertId);
|
|
|
} catch (error) {
|
|
|
await connection.rollback();
|
|
|
throw new Error('创建支付记录失败: ' + error.message);
|
|
|
} finally {
|
|
|
connection.release();
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 更新支付状态
|
|
|
* @param {number} id - 支付ID
|
|
|
* @param {string} status - 新状态
|
|
|
* @param {string} thirdPartyNo - 第三方交易号（可选）
|
|
|
* @returns {Promise<object>} 更新后的支付记录
|
|
|
*/
|
|
|
static async updateStatus(id, status, thirdPartyNo = null) {
|
|
|
const connection = await pool.getConnection();
|
|
|
|
|
|
try {
|
|
|
await connection.beginTransaction();
|
|
|
|
|
|
// 更新支付状态
|
|
|
if (status === 'paid') {
|
|
|
await connection.query(
|
|
|
`UPDATE payments
|
|
|
SET status = ?, paid_at = NOW(), third_party_transaction_no = ?
|
|
|
WHERE id = ?`,
|
|
|
[status, thirdPartyNo, id]
|
|
|
);
|
|
|
} else {
|
|
|
await connection.query(
|
|
|
'UPDATE payments SET status = ? WHERE id = ?',
|
|
|
[status, id]
|
|
|
);
|
|
|
}
|
|
|
|
|
|
// 如果支付成功，更新订单状态
|
|
|
if (status === 'paid') {
|
|
|
const payment = await this.findById(id);
|
|
|
if (payment && payment.order_id) {
|
|
|
await connection.query(
|
|
|
'UPDATE orders SET status = ? WHERE id = ?',
|
|
|
['paid', payment.order_id]
|
|
|
);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
await connection.commit();
|
|
|
|
|
|
return await this.findById(id);
|
|
|
} catch (error) {
|
|
|
await connection.rollback();
|
|
|
throw new Error('更新支付状态失败: ' + error.message);
|
|
|
} finally {
|
|
|
connection.release();
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 支付成功处理（包含积分奖励）
|
|
|
* @param {number} id - 支付ID
|
|
|
* @param {string} thirdPartyNo - 第三方交易号
|
|
|
* @returns {Promise<object>} 更新后的支付记录
|
|
|
*/
|
|
|
static async handlePaymentSuccess(id, thirdPartyNo = null) {
|
|
|
const connection = await pool.getConnection();
|
|
|
|
|
|
try {
|
|
|
await connection.beginTransaction();
|
|
|
|
|
|
const payment = await this.findById(id);
|
|
|
if (!payment) {
|
|
|
throw new Error('支付记录不存在');
|
|
|
}
|
|
|
|
|
|
// 更新支付状态
|
|
|
await connection.query(
|
|
|
`UPDATE payments
|
|
|
SET status = 'paid', paid_at = NOW(), third_party_transaction_no = ?
|
|
|
WHERE id = ?`,
|
|
|
[thirdPartyNo, id]
|
|
|
);
|
|
|
|
|
|
// 更新订单状态为已支付
|
|
|
await connection.query(
|
|
|
'UPDATE orders SET status = ? WHERE id = ?',
|
|
|
['paid', payment.order_id]
|
|
|
);
|
|
|
|
|
|
// 更新会员消费金额
|
|
|
await connection.query(
|
|
|
`UPDATE members
|
|
|
SET total_spent = total_spent + ?
|
|
|
WHERE user_id = ?`,
|
|
|
[payment.amount, payment.customer_id]
|
|
|
);
|
|
|
|
|
|
// 计算并增加积分（消费1元 = 1积分）
|
|
|
const points = Math.floor(payment.amount);
|
|
|
if (points > 0) {
|
|
|
await connection.query(
|
|
|
`UPDATE members
|
|
|
SET points = points + ?,
|
|
|
total_points = total_points + ?
|
|
|
WHERE user_id = ?`,
|
|
|
[points, points, payment.customer_id]
|
|
|
);
|
|
|
|
|
|
// 检查是否需要升级会员等级
|
|
|
const [members] = await connection.query(
|
|
|
'SELECT * FROM members WHERE user_id = ?',
|
|
|
[payment.customer_id]
|
|
|
);
|
|
|
|
|
|
if (members.length > 0) {
|
|
|
const member = members[0];
|
|
|
let newLevel = 'bronze';
|
|
|
|
|
|
if (member.total_points >= 10000) {
|
|
|
newLevel = 'diamond';
|
|
|
} else if (member.total_points >= 5000) {
|
|
|
newLevel = 'gold';
|
|
|
} else if (member.total_points >= 1000) {
|
|
|
newLevel = 'silver';
|
|
|
}
|
|
|
|
|
|
if (newLevel !== member.level) {
|
|
|
await connection.query(
|
|
|
'UPDATE members SET level = ? WHERE user_id = ?',
|
|
|
[newLevel, payment.customer_id]
|
|
|
);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
await connection.commit();
|
|
|
|
|
|
return await this.findById(id);
|
|
|
} catch (error) {
|
|
|
await connection.rollback();
|
|
|
throw new Error('处理支付成功失败: ' + error.message);
|
|
|
} finally {
|
|
|
connection.release();
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取支付统计信息
|
|
|
* @param {string} startDate - 开始日期（可选）
|
|
|
* @param {string} endDate - 结束日期（可选）
|
|
|
* @returns {Promise<object>} 统计信息
|
|
|
*/
|
|
|
static async getStatistics(startDate = null, endDate = null) {
|
|
|
try {
|
|
|
let whereClause = "WHERE status = 'paid'";
|
|
|
const params = [];
|
|
|
|
|
|
if (startDate && endDate) {
|
|
|
whereClause += ' AND DATE(paid_at) BETWEEN ? AND ?';
|
|
|
params.push(startDate, endDate);
|
|
|
}
|
|
|
|
|
|
// 总支付金额和笔数
|
|
|
const [totalStats] = await pool.query(
|
|
|
`SELECT
|
|
|
COUNT(*) as total_transactions,
|
|
|
SUM(amount) as total_amount,
|
|
|
AVG(amount) as avg_amount
|
|
|
FROM payments ${whereClause}`,
|
|
|
params
|
|
|
);
|
|
|
|
|
|
// 按支付方式统计
|
|
|
const [methodStats] = await pool.query(
|
|
|
`SELECT
|
|
|
payment_method,
|
|
|
COUNT(*) as count,
|
|
|
SUM(amount) as total_amount
|
|
|
FROM payments ${whereClause}
|
|
|
GROUP BY payment_method`,
|
|
|
params
|
|
|
);
|
|
|
|
|
|
return {
|
|
|
...totalStats[0],
|
|
|
by_method: methodStats
|
|
|
};
|
|
|
} catch (error) {
|
|
|
throw new Error('查询支付统计失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 申请退款
|
|
|
* @param {number} id - 支付ID
|
|
|
* @param {string} reason - 退款原因
|
|
|
* @returns {Promise<object>} 更新后的支付记录
|
|
|
*/
|
|
|
static async requestRefund(id, reason) {
|
|
|
try {
|
|
|
await pool.query(
|
|
|
`UPDATE payments
|
|
|
SET status = 'refunding', refund_reason = ?
|
|
|
WHERE id = ? AND status = 'paid'`,
|
|
|
[reason, id]
|
|
|
);
|
|
|
|
|
|
return await this.findById(id);
|
|
|
} catch (error) {
|
|
|
throw new Error('申请退款失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 完成退款
|
|
|
* @param {number} id - 支付ID
|
|
|
* @returns {Promise<object>} 更新后的支付记录
|
|
|
*/
|
|
|
static async completeRefund(id) {
|
|
|
const connection = await pool.getConnection();
|
|
|
|
|
|
try {
|
|
|
await connection.beginTransaction();
|
|
|
|
|
|
const payment = await this.findById(id);
|
|
|
if (!payment) {
|
|
|
throw new Error('支付记录不存在');
|
|
|
}
|
|
|
|
|
|
// 更新支付状态
|
|
|
await connection.query(
|
|
|
`UPDATE payments
|
|
|
SET status = 'refunded', refunded_at = NOW()
|
|
|
WHERE id = ?`,
|
|
|
[id]
|
|
|
);
|
|
|
|
|
|
// 更新订单状态
|
|
|
await connection.query(
|
|
|
'UPDATE orders SET status = ? WHERE id = ?',
|
|
|
['refunded', payment.order_id]
|
|
|
);
|
|
|
|
|
|
// 扣减会员消费金额和积分
|
|
|
const points = Math.floor(payment.amount);
|
|
|
await connection.query(
|
|
|
`UPDATE members
|
|
|
SET total_spent = GREATEST(total_spent - ?, 0),
|
|
|
points = GREATEST(points - ?, 0)
|
|
|
WHERE user_id = ?`,
|
|
|
[payment.amount, points, payment.customer_id]
|
|
|
);
|
|
|
|
|
|
await connection.commit();
|
|
|
|
|
|
return await this.findById(id);
|
|
|
} catch (error) {
|
|
|
await connection.rollback();
|
|
|
throw new Error('完成退款失败: ' + error.message);
|
|
|
} finally {
|
|
|
connection.release();
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 发票模型类
|
|
|
*/
|
|
|
class Invoice {
|
|
|
/**
|
|
|
* 根据ID查找发票
|
|
|
* @param {number} id - 发票ID
|
|
|
* @returns {Promise<object|null>} 发票对象或null
|
|
|
*/
|
|
|
static async findById(id) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
i.*,
|
|
|
p.transaction_no,
|
|
|
p.amount,
|
|
|
o.order_no
|
|
|
FROM invoices i
|
|
|
LEFT JOIN payments p ON i.payment_id = p.id
|
|
|
LEFT JOIN orders o ON p.order_id = o.id
|
|
|
WHERE i.id = ?`,
|
|
|
[id]
|
|
|
);
|
|
|
return rows[0] || null;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询发票失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 根据支付ID查找发票
|
|
|
* @param {number} paymentId - 支付ID
|
|
|
* @returns {Promise<object|null>} 发票对象或null
|
|
|
*/
|
|
|
static async findByPaymentId(paymentId) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
i.*,
|
|
|
p.transaction_no,
|
|
|
p.amount,
|
|
|
o.order_no
|
|
|
FROM invoices i
|
|
|
LEFT JOIN payments p ON i.payment_id = p.id
|
|
|
LEFT JOIN orders o ON p.order_id = o.id
|
|
|
WHERE i.payment_id = ?`,
|
|
|
[paymentId]
|
|
|
);
|
|
|
return rows[0] || null;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询发票失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 生成发票号
|
|
|
* @returns {string} 发票号
|
|
|
*/
|
|
|
static generateInvoiceNo() {
|
|
|
const timestamp = Date.now();
|
|
|
const random = Math.floor(Math.random() * 10000).toString().padStart(4, '0');
|
|
|
return `INV${timestamp}${random}`;
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 创建发票
|
|
|
* @param {object} invoiceData - 发票数据
|
|
|
* @returns {Promise<object>} 创建的发票
|
|
|
*/
|
|
|
static async create(invoiceData) {
|
|
|
try {
|
|
|
const {
|
|
|
payment_id,
|
|
|
invoice_type,
|
|
|
invoice_title,
|
|
|
tax_no,
|
|
|
email
|
|
|
} = invoiceData;
|
|
|
|
|
|
const invoice_no = this.generateInvoiceNo();
|
|
|
|
|
|
const [result] = await pool.query(
|
|
|
`INSERT INTO invoices
|
|
|
(payment_id, invoice_no, invoice_type, invoice_title, tax_no, email, status)
|
|
|
VALUES (?, ?, ?, ?, ?, ?, 'pending')`,
|
|
|
[payment_id, invoice_no, invoice_type, invoice_title, tax_no || null, email]
|
|
|
);
|
|
|
|
|
|
return await this.findById(result.insertId);
|
|
|
} catch (error) {
|
|
|
throw new Error('创建发票失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 更新发票状态
|
|
|
* @param {number} id - 发票ID
|
|
|
* @param {string} status - 新状态
|
|
|
* @returns {Promise<object>} 更新后的发票
|
|
|
*/
|
|
|
static async updateStatus(id, status) {
|
|
|
try {
|
|
|
const updateField = status === 'issued' ? ', issued_at = NOW()' : '';
|
|
|
|
|
|
await pool.query(
|
|
|
`UPDATE invoices SET status = ? ${updateField} WHERE id = ?`,
|
|
|
[status, id]
|
|
|
);
|
|
|
|
|
|
return await this.findById(id);
|
|
|
} catch (error) {
|
|
|
throw new Error('更新发票状态失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取用户的发票列表
|
|
|
* @param {number} userId - 用户ID
|
|
|
* @returns {Promise<array>} 发票列表
|
|
|
*/
|
|
|
static async getUserInvoices(userId) {
|
|
|
try {
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
i.*,
|
|
|
p.transaction_no,
|
|
|
p.amount,
|
|
|
o.order_no
|
|
|
FROM invoices i
|
|
|
JOIN payments p ON i.payment_id = p.id
|
|
|
JOIN orders o ON p.order_id = o.id
|
|
|
WHERE o.customer_id = ?
|
|
|
ORDER BY i.created_at DESC`,
|
|
|
[userId]
|
|
|
);
|
|
|
return rows;
|
|
|
} catch (error) {
|
|
|
throw new Error('查询用户发票列表失败: ' + error.message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取所有发票列表（分页）
|
|
|
* @param {object} options - 查询选项
|
|
|
* @returns {Promise<object>} 发票列表
|
|
|
*/
|
|
|
static async findAll(options = {}) {
|
|
|
try {
|
|
|
const {
|
|
|
page = 1,
|
|
|
pageSize = 10,
|
|
|
status
|
|
|
} = options;
|
|
|
|
|
|
const offset = (page - 1) * pageSize;
|
|
|
|
|
|
const whereClause = status ? 'WHERE i.status = ?' : '';
|
|
|
const params = status ? [status] : [];
|
|
|
|
|
|
// 查询总数
|
|
|
const [countResult] = await pool.query(
|
|
|
`SELECT COUNT(*) as total
|
|
|
FROM invoices i ${whereClause}`,
|
|
|
params
|
|
|
);
|
|
|
const total = countResult[0].total;
|
|
|
|
|
|
// 查询列表
|
|
|
const [rows] = await pool.query(
|
|
|
`SELECT
|
|
|
i.*,
|
|
|
p.transaction_no,
|
|
|
p.amount,
|
|
|
o.order_no,
|
|
|
o.customer_id,
|
|
|
u.username as customer_name
|
|
|
FROM invoices i
|
|
|
LEFT JOIN payments p ON i.payment_id = p.id
|
|
|
LEFT JOIN orders o ON p.order_id = o.id
|
|
|
LEFT JOIN users u ON o.customer_id = u.id
|
|
|
${whereClause}
|
|
|
ORDER BY i.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);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
module.exports = { Payment, Invoice };
|
