540 lines
14 KiB
JavaScript
540 lines
14 KiB
JavaScript
const express = require('express');
|
||
const router = express.Router();
|
||
const { getDB } = require('../database');
|
||
const { agentAuth } = require('../middleware/agentAuth');
|
||
const { logger } = require('../config/logger');
|
||
|
||
/**
|
||
* 获取代理佣金记录列表
|
||
* GET /api/commissions
|
||
*/
|
||
router.get('/', agentAuth, async (req, res) => {
|
||
try {
|
||
const agentId = req.agent.id;
|
||
const {
|
||
page = 1,
|
||
limit = 20,
|
||
search,
|
||
status,
|
||
commission_type,
|
||
start_date,
|
||
end_date,
|
||
min_amount,
|
||
max_amount,
|
||
sort_by = 'created_at',
|
||
sort_order = 'desc'
|
||
} = req.query;
|
||
|
||
const pageNum = parseInt(page) || 1;
|
||
const limitNum = parseInt(limit) || 20;
|
||
const offset = (pageNum - 1) * limitNum;
|
||
|
||
// 构建查询条件
|
||
let whereConditions = ['acr.agent_id = ?'];
|
||
let queryParams = [agentId];
|
||
|
||
if (search) {
|
||
whereConditions.push('(u.username LIKE ? OR u.real_name LIKE ? OR u.phone LIKE ?)');
|
||
queryParams.push(`%${search}%`, `%${search}%`, `%${search}%`);
|
||
}
|
||
|
||
if (status) {
|
||
whereConditions.push('acr.status = ?');
|
||
queryParams.push(status);
|
||
}
|
||
|
||
if (commission_type) {
|
||
whereConditions.push('acr.commission_type = ?');
|
||
queryParams.push(commission_type);
|
||
}
|
||
|
||
if (start_date) {
|
||
whereConditions.push('DATE(acr.created_at) >= ?');
|
||
queryParams.push(start_date);
|
||
}
|
||
|
||
if (end_date) {
|
||
whereConditions.push('DATE(acr.created_at) <= ?');
|
||
queryParams.push(end_date);
|
||
}
|
||
|
||
if (min_amount) {
|
||
whereConditions.push('acr.commission_amount >= ?');
|
||
queryParams.push(parseFloat(min_amount));
|
||
}
|
||
|
||
if (max_amount) {
|
||
whereConditions.push('acr.commission_amount <= ?');
|
||
queryParams.push(parseFloat(max_amount));
|
||
}
|
||
|
||
const whereClause = whereConditions.join(' AND ');
|
||
|
||
// 验证排序字段
|
||
const allowedSortFields = ['created_at', 'commission_amount', 'status', 'commission_type'];
|
||
const sortBy = allowedSortFields.includes(sort_by) ? sort_by : 'created_at';
|
||
const sortOrder = sort_order.toLowerCase() === 'asc' ? 'ASC' : 'DESC';
|
||
|
||
// 查询佣金记录列表
|
||
const commissionsQuery = `
|
||
SELECT
|
||
acr.id,
|
||
acr.agent_id,
|
||
acr.merchant_id,
|
||
acr.commission_type,
|
||
acr.commission_amount,
|
||
acr.status,
|
||
acr.description,
|
||
acr.reference_id,
|
||
acr.created_at,
|
||
acr.updated_at,
|
||
acr.paid_at,
|
||
u.username,
|
||
u.real_name,
|
||
u.phone,
|
||
u.avatar
|
||
FROM agent_commission_records acr
|
||
LEFT JOIN users u ON acr.merchant_id = u.id
|
||
WHERE ${whereClause}
|
||
ORDER BY acr.${sortBy} ${sortOrder}
|
||
LIMIT ${limitNum} OFFSET ${offset}
|
||
`;
|
||
|
||
const [commissions] = await getDB().execute(commissionsQuery, queryParams);
|
||
|
||
// 查询总数
|
||
const countQuery = `
|
||
SELECT COUNT(*) as total
|
||
FROM agent_commission_records acr
|
||
LEFT JOIN users u ON acr.merchant_id = u.id
|
||
WHERE ${whereClause}
|
||
`;
|
||
|
||
const [countResult] = await getDB().execute(countQuery, queryParams);
|
||
const total = countResult[0]?.total || 0;
|
||
|
||
// 查询统计信息
|
||
const [statsResult] = await getDB().execute(`
|
||
SELECT
|
||
COUNT(*) as total_commissions,
|
||
COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_commissions,
|
||
COUNT(CASE WHEN status = 'paid' THEN 1 END) as paid_commissions,
|
||
CAST(COALESCE(SUM(commission_amount), 0) AS DECIMAL(10,2)) as total_amount,
|
||
CAST(COALESCE(SUM(CASE WHEN status = 'pending' THEN commission_amount ELSE 0 END), 0) AS DECIMAL(10,2)) as pending_amount,
|
||
CAST(COALESCE(SUM(CASE WHEN status = 'paid' THEN commission_amount ELSE 0 END), 0) AS DECIMAL(10,2)) as paid_amount,
|
||
CAST(COALESCE(SUM(CASE WHEN DATE(created_at) = CURDATE() THEN commission_amount ELSE 0 END), 0) AS DECIMAL(10,2)) as today_amount
|
||
FROM agent_commission_records
|
||
WHERE agent_id = ?
|
||
`, [agentId]);
|
||
|
||
const stats = statsResult[0] || {
|
||
total_commissions: 0,
|
||
pending_commissions: 0,
|
||
paid_commissions: 0,
|
||
total_amount: '0.00',
|
||
pending_amount: '0.00',
|
||
paid_amount: '0.00',
|
||
today_amount: '0.00'
|
||
};
|
||
|
||
res.json({
|
||
success: true,
|
||
data: {
|
||
commissions,
|
||
pagination: {
|
||
current_page: pageNum,
|
||
per_page: limitNum,
|
||
total,
|
||
total_pages: Math.ceil(total / limitNum)
|
||
},
|
||
stats
|
||
}
|
||
});
|
||
|
||
} catch (error) {
|
||
logger.error('获取佣金记录失败', {
|
||
error: error.message,
|
||
stack: error.stack,
|
||
agentId: req.agent?.id
|
||
});
|
||
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '获取佣金记录失败'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 获取单个佣金记录详情
|
||
* GET /api/commissions/:id
|
||
*/
|
||
router.get('/:id', agentAuth, async (req, res) => {
|
||
try {
|
||
const agentId = req.agent.id;
|
||
const commissionId = req.params.id;
|
||
|
||
// 查询佣金记录详情
|
||
const [commissions] = await getDB().execute(`
|
||
SELECT
|
||
acr.id,
|
||
acr.agent_id,
|
||
acr.merchant_id,
|
||
acr.commission_type,
|
||
acr.commission_amount,
|
||
acr.status,
|
||
acr.description,
|
||
acr.reference_id,
|
||
acr.created_at,
|
||
acr.updated_at,
|
||
acr.paid_at,
|
||
u.username,
|
||
u.real_name,
|
||
u.phone,
|
||
u.avatar,
|
||
u.city,
|
||
u.district
|
||
FROM agent_commission_records acr
|
||
LEFT JOIN users u ON acr.merchant_id = u.id
|
||
WHERE acr.id = ? AND acr.agent_id = ?
|
||
`, [commissionId, agentId]);
|
||
|
||
if (commissions.length === 0) {
|
||
return res.status(404).json({
|
||
success: false,
|
||
message: '佣金记录不存在或无权限查看'
|
||
});
|
||
}
|
||
|
||
const commission = commissions[0];
|
||
|
||
res.json({
|
||
success: true,
|
||
data: commission
|
||
});
|
||
|
||
} catch (error) {
|
||
logger.error('获取佣金记录详情失败', {
|
||
error: error.message,
|
||
stack: error.stack,
|
||
agentId: req.agent?.id,
|
||
commissionId: req.params.id
|
||
});
|
||
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '获取佣金记录详情失败'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 申请佣金发放（单个）
|
||
* POST /api/commissions/:id/request-payment
|
||
*/
|
||
router.post('/:id/request-payment', agentAuth, async (req, res) => {
|
||
try {
|
||
const agentId = req.agent.id;
|
||
const commissionId = req.params.id;
|
||
|
||
// 检查佣金记录是否存在且属于当前代理
|
||
const [commissions] = await getDB().execute(`
|
||
SELECT id, status, commission_amount
|
||
FROM agent_commission_records
|
||
WHERE id = ? AND agent_id = ?
|
||
`, [commissionId, agentId]);
|
||
|
||
if (commissions.length === 0) {
|
||
return res.status(404).json({
|
||
success: false,
|
||
message: '佣金记录不存在或无权限操作'
|
||
});
|
||
}
|
||
|
||
const commission = commissions[0];
|
||
|
||
if (commission.status !== 'pending') {
|
||
return res.status(400).json({
|
||
success: false,
|
||
message: '只能申请待发放状态的佣金'
|
||
});
|
||
}
|
||
|
||
// 更新佣金状态为申请中
|
||
await getDB().execute(`
|
||
UPDATE agent_commission_records
|
||
SET status = 'requested', updated_at = NOW()
|
||
WHERE id = ?
|
||
`, [commissionId]);
|
||
|
||
logger.info('代理申请佣金发放', {
|
||
agentId,
|
||
commissionId,
|
||
amount: commission.commission_amount
|
||
});
|
||
|
||
res.json({
|
||
success: true,
|
||
message: '佣金发放申请已提交，请等待审核'
|
||
});
|
||
|
||
} catch (error) {
|
||
logger.error('申请佣金发放失败', {
|
||
error: error.message,
|
||
stack: error.stack,
|
||
agentId: req.agent?.id,
|
||
commissionId: req.params.id
|
||
});
|
||
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '申请佣金发放失败'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 批量申请佣金发放
|
||
* POST /api/commissions/batch-request-payment
|
||
*/
|
||
router.post('/batch-request-payment', agentAuth, async (req, res) => {
|
||
try {
|
||
const agentId = req.agent.id;
|
||
const { commission_ids } = req.body;
|
||
|
||
if (!commission_ids || !Array.isArray(commission_ids) || commission_ids.length === 0) {
|
||
return res.status(400).json({
|
||
success: false,
|
||
message: '请选择要申请发放的佣金记录'
|
||
});
|
||
}
|
||
|
||
// 检查所有佣金记录是否存在且属于当前代理
|
||
const placeholders = commission_ids.map(() => '?').join(',');
|
||
const [commissions] = await getDB().execute(`
|
||
SELECT id, status, commission_amount
|
||
FROM agent_commission_records
|
||
WHERE id IN (${placeholders}) AND agent_id = ?
|
||
`, [...commission_ids, agentId]);
|
||
|
||
if (commissions.length !== commission_ids.length) {
|
||
return res.status(400).json({
|
||
success: false,
|
||
message: '部分佣金记录不存在或无权限操作'
|
||
});
|
||
}
|
||
|
||
// 检查状态
|
||
const invalidCommissions = commissions.filter(c => c.status !== 'pending');
|
||
if (invalidCommissions.length > 0) {
|
||
return res.status(400).json({
|
||
success: false,
|
||
message: '只能申请待发放状态的佣金'
|
||
});
|
||
}
|
||
|
||
// 批量更新状态
|
||
await getDB().execute(`
|
||
UPDATE agent_commission_records
|
||
SET status = 'requested', updated_at = NOW()
|
||
WHERE id IN (${placeholders}) AND agent_id = ?
|
||
`, [...commission_ids, agentId]);
|
||
|
||
const totalAmount = commissions.reduce((sum, c) => sum + parseFloat(c.commission_amount), 0);
|
||
|
||
logger.info('代理批量申请佣金发放', {
|
||
agentId,
|
||
commissionIds: commission_ids,
|
||
count: commission_ids.length,
|
||
totalAmount
|
||
});
|
||
|
||
res.json({
|
||
success: true,
|
||
message: `已提交${commission_ids.length}条佣金发放申请，总金额${totalAmount.toFixed(2)}元`
|
||
});
|
||
|
||
} catch (error) {
|
||
logger.error('批量申请佣金发放失败', {
|
||
error: error.message,
|
||
stack: error.stack,
|
||
agentId: req.agent?.id
|
||
});
|
||
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '批量申请佣金发放失败'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 获取佣金趋势数据
|
||
* GET /api/commissions/trend
|
||
*/
|
||
router.get('/trend/data', agentAuth, async (req, res) => {
|
||
try {
|
||
const agentId = req.agent.id;
|
||
const { days = 7, type = 'amount' } = req.query;
|
||
|
||
let selectField = 'CAST(COALESCE(SUM(commission_amount), 0) AS DECIMAL(10,2)) as value';
|
||
if (type === 'count') {
|
||
selectField = 'COUNT(*) as value';
|
||
}
|
||
|
||
const [trendData] = await getDB().execute(`
|
||
SELECT
|
||
DATE(created_at) as date,
|
||
${selectField}
|
||
FROM agent_commission_records
|
||
WHERE agent_id = ?
|
||
AND created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
|
||
GROUP BY DATE(created_at)
|
||
ORDER BY date ASC
|
||
`, [agentId, parseInt(days)]);
|
||
|
||
res.json({
|
||
success: true,
|
||
data: trendData
|
||
});
|
||
|
||
} catch (error) {
|
||
logger.error('获取佣金趋势失败', {
|
||
error: error.message,
|
||
stack: error.stack,
|
||
agentId: req.agent?.id
|
||
});
|
||
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '获取佣金趋势失败'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 导出佣金记录
|
||
* GET /api/commissions/export
|
||
*/
|
||
router.get('/export/data', agentAuth, async (req, res) => {
|
||
try {
|
||
const agentId = req.agent.id;
|
||
const {
|
||
format = 'json',
|
||
search,
|
||
status,
|
||
commission_type,
|
||
start_date,
|
||
end_date,
|
||
min_amount,
|
||
max_amount
|
||
} = req.query;
|
||
|
||
// 构建查询条件
|
||
let whereConditions = ['acr.agent_id = ?'];
|
||
let queryParams = [agentId];
|
||
|
||
if (search) {
|
||
whereConditions.push('(u.username LIKE ? OR u.real_name LIKE ? OR u.phone LIKE ?)');
|
||
queryParams.push(`%${search}%`, `%${search}%`, `%${search}%`);
|
||
}
|
||
|
||
if (status) {
|
||
whereConditions.push('acr.status = ?');
|
||
queryParams.push(status);
|
||
}
|
||
|
||
if (commission_type) {
|
||
whereConditions.push('acr.commission_type = ?');
|
||
queryParams.push(commission_type);
|
||
}
|
||
|
||
if (start_date) {
|
||
whereConditions.push('DATE(acr.created_at) >= ?');
|
||
queryParams.push(start_date);
|
||
}
|
||
|
||
if (end_date) {
|
||
whereConditions.push('DATE(acr.created_at) <= ?');
|
||
queryParams.push(end_date);
|
||
}
|
||
|
||
if (min_amount) {
|
||
whereConditions.push('acr.commission_amount >= ?');
|
||
queryParams.push(parseFloat(min_amount));
|
||
}
|
||
|
||
if (max_amount) {
|
||
whereConditions.push('acr.commission_amount <= ?');
|
||
queryParams.push(parseFloat(max_amount));
|
||
}
|
||
|
||
const whereClause = whereConditions.join(' AND ');
|
||
|
||
// 查询佣金记录
|
||
const [commissions] = await getDB().execute(`
|
||
SELECT
|
||
acr.id,
|
||
acr.commission_type,
|
||
acr.commission_amount,
|
||
acr.status,
|
||
acr.description,
|
||
acr.reference_id,
|
||
acr.created_at,
|
||
acr.paid_at,
|
||
u.username,
|
||
u.real_name,
|
||
u.phone
|
||
FROM agent_commission_records acr
|
||
LEFT JOIN users u ON acr.merchant_id = u.id
|
||
WHERE ${whereClause}
|
||
ORDER BY acr.created_at DESC
|
||
`, queryParams);
|
||
|
||
if (format === 'csv') {
|
||
// 生成CSV格式
|
||
const csvHeader = 'ID,佣金类型,佣金金额,状态,描述,关联ID,用户名,真实姓名,手机号,创建时间,发放时间\n';
|
||
const csvData = commissions.map(commission => {
|
||
return [
|
||
commission.id,
|
||
commission.commission_type || '',
|
||
commission.commission_amount,
|
||
commission.status || '',
|
||
(commission.description || '').replace(/,/g, '，'), // 替换逗号避免CSV格式问题
|
||
commission.reference_id || '',
|
||
commission.username || '',
|
||
commission.real_name || '',
|
||
commission.phone || '',
|
||
commission.created_at || '',
|
||
commission.paid_at || ''
|
||
].join(',');
|
||
}).join('\n');
|
||
|
||
res.setHeader('Content-Type', 'text/csv; charset=utf-8');
|
||
res.setHeader('Content-Disposition', `attachment; filename="commissions_${Date.now()}.csv"`);
|
||
res.send(csvHeader + csvData);
|
||
} else {
|
||
// 默认JSON格式
|
||
res.json({
|
||
success: true,
|
||
data: commissions,
|
||
exported_at: new Date().toISOString(),
|
||
total: commissions.length
|
||
});
|
||
}
|
||
|
||
} catch (error) {
|
||
logger.error('导出佣金记录失败', {
|
||
error: error.message,
|
||
stack: error.stack,
|
||
agentId: req.agent?.id
|
||
});
|
||
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '导出佣金记录失败'
|
||
});
|
||
}
|
||
});
|
||
|
||
module.exports = router; |