625 lines
22 KiB
JavaScript
625 lines
22 KiB
JavaScript
const express = require('express');
|
||
const router = express.Router();
|
||
const {getDB} = require('../database');
|
||
const {agentAuth} = require('../middleware/agentAuth');
|
||
const {logger} = require('../config/logger');
|
||
const dayjs = require('dayjs');
|
||
|
||
/**
|
||
* 获取代理统计数据
|
||
* GET /api/agent/stats
|
||
*/
|
||
router.get('/stats', agentAuth, async (req, res) => {
|
||
try {
|
||
const {id: agentId, userId} = req.agent;
|
||
|
||
|
||
// 获取下级用户统计
|
||
const [userStats] = await getDB().execute(`
|
||
SELECT COUNT(*) as total_users,
|
||
COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as today_new_users
|
||
FROM users
|
||
WHERE inviter = ?
|
||
`, [userId]);
|
||
//获取直推用户的分销用户数量
|
||
const [userIds] = await getDB().execute(`
|
||
SELECT id
|
||
FROM users
|
||
WHERE inviter = ?
|
||
`, [userId]);
|
||
let active_users = 0
|
||
for (const id of userIds) {
|
||
const [userTotal] = await getDB().execute(`
|
||
SELECT COUNT(*) as total_users
|
||
FROM users
|
||
WHERE inviter = ?
|
||
`, [id])
|
||
active_users += userTotal[0].total_users
|
||
}
|
||
userStats[0].active_users = active_users;
|
||
// 获取佣金统计
|
||
const [commissionStats] = await getDB().execute(`
|
||
SELECT COALESCE(SUM(amount), 0) AS total_commission,
|
||
COALESCE(SUM(CASE
|
||
WHEN created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
|
||
AND created_at < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
|
||
THEN amount
|
||
END), 0) AS monthly_commission
|
||
FROM transfers
|
||
WHERE to_user_id = ?
|
||
AND transfer_type = 'user_to_agent'
|
||
`, [userId]);
|
||
|
||
// 获取转账统计
|
||
const [transferStats] = await getDB().execute(`
|
||
SELECT COUNT(*) as total_transfers,
|
||
COUNT(CASE WHEN DATE(t.created_at) = CURDATE() THEN 1 END) as today_transfers,
|
||
CAST(COALESCE(SUM(t.amount), 0) AS DECIMAL(10, 2)) as total_amount,
|
||
CAST(COALESCE(SUM(CASE WHEN DATE(t.created_at) = CURDATE() THEN t.amount ELSE 0 END),
|
||
0) AS DECIMAL(10, 2)) as today_amount
|
||
FROM transfers t
|
||
INNER JOIN agent_merchants am ON (t.from_user_id = am.merchant_id OR t.to_user_id = am.merchant_id)
|
||
WHERE am.agent_id = ?
|
||
`, [agentId]);
|
||
|
||
const stats = {
|
||
users: userStats[0] || {
|
||
total_users: 0,
|
||
today_new_users: 0,
|
||
active_users: 0,
|
||
agent_share_users: 0,
|
||
operated_share_users: 0,
|
||
},
|
||
commissions: commissionStats[0] || {
|
||
total_commission: '0.00',
|
||
today_commission: '0.00',
|
||
paid_commission: '0.00',
|
||
monthly_commission: '0.00'
|
||
},
|
||
transfers: transferStats[0] || {
|
||
total_transfers: 0,
|
||
today_transfers: 0,
|
||
total_amount: '0.00',
|
||
today_amount: '0.00'
|
||
}
|
||
};
|
||
|
||
res.json({
|
||
success: true,
|
||
data: stats
|
||
});
|
||
|
||
} catch (error) {
|
||
logger.error('获取代理统计数据失败', {
|
||
error: error.message,
|
||
stack: error.stack,
|
||
agentId: req.agent?.id
|
||
});
|
||
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '获取统计数据失败'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 获取代理统计数据-直营代理
|
||
* GET /api/agent/stats
|
||
*/
|
||
router.get('/stats_agent_directly', agentAuth, async (req, res) => {
|
||
try {
|
||
const {id: agentId, userId} = req.agent;
|
||
|
||
// 获取直营用户统计
|
||
const [userStats] = await getDB().execute(`
|
||
SELECT COUNT(*) as total_directly_agents,
|
||
COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as today_new_users
|
||
FROM users
|
||
WHERE inviter = ?
|
||
AND user_type = 'directly_operated'
|
||
`, [userId]);
|
||
//获取直营用户的分销用户数量
|
||
const [userIds] = await getDB().execute(`
|
||
SELECT id
|
||
FROM users
|
||
WHERE inviter = ?
|
||
AND user_type = 'directly_operated'
|
||
`, [userId])
|
||
|
||
//直营商户人数
|
||
let active_users = 0
|
||
let operated_commission_sql = []
|
||
for (const id of userIds) {
|
||
const [userTotal] = await getDB().execute(`
|
||
SELECT COUNT(*) as total_users
|
||
FROM users
|
||
WHERE inviter = ?
|
||
`, [id.id])
|
||
active_users += userTotal[0].total_users
|
||
operated_commission_sql.push(`to_user_id=${id.id}`)
|
||
}
|
||
userStats[0].active_users = active_users;
|
||
userStats[0].total_operated = userIds.length;
|
||
//直销人员分享人数
|
||
let [agent_share_users] = await getDB().execute(`
|
||
SELECT id FROM users WHERE inviter=? AND user_type='user'
|
||
`, [userId])
|
||
let total_share_users = 0
|
||
for (const id of agent_share_users) {
|
||
const [userTotal] = await getDB().execute(`
|
||
SELECT COUNT(*) as total_users
|
||
FROM users
|
||
WHERE inviter = ?
|
||
`, [id])
|
||
total_share_users += userTotal[0].total_users
|
||
}
|
||
userStats[0].agent_share_users = total_share_users;
|
||
//直营商户分享人数
|
||
let [operated_share_users] = await getDB().execute(`
|
||
SELECT id FROM users WHERE inviter=? AND user_type='directly_operated'
|
||
`, [userId])
|
||
let total_operated_share_users = 0
|
||
let operated_user_ids = []
|
||
let operated_user_sql = []
|
||
for (const id of operated_share_users) {
|
||
const [userTotal] = await getDB().execute(`
|
||
SELECT COUNT(*) as total_users
|
||
FROM users
|
||
WHERE inviter = ?
|
||
`, [id])
|
||
total_operated_share_users += userTotal[0].total_users
|
||
operated_user_sql.push(`to_user_id=?`)
|
||
operated_user_ids.push(userTotal[0].total_users)
|
||
}
|
||
userStats[0].operated_share_users = total_operated_share_users;
|
||
|
||
// 获取佣金统计
|
||
const [commissionStats] = await getDB().execute(`
|
||
SELECT COALESCE(SUM(amount), 0) AS total_commission,
|
||
COALESCE(SUM(CASE
|
||
WHEN created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
|
||
AND created_at < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
|
||
THEN amount
|
||
END), 0) AS monthly_commission
|
||
FROM transfers
|
||
WHERE to_user_id = ?
|
||
AND transfer_type = 'user_to_agent'
|
||
`, [userId]);
|
||
// 获取直营佣金
|
||
let [operated_commission] = operated_commission_sql.length === 0 ?
|
||
await getDB().execute(`
|
||
SELECT SUM(amount) as amount FROM transfers
|
||
`)
|
||
:
|
||
await getDB().execute(`
|
||
SELECT SUM(amount) as amount FROM transfers WHERE ${operated_commission_sql.join(' or ')}
|
||
`)
|
||
|
||
// 获取直销商户利润
|
||
let [directly_agents_income] = await getDB().execute(`
|
||
SELECT SUM(amount) as amount FROM transfers WHERE to_user_id=? AND transfer_type='user_to_agent' AND source_type='agent_operated'
|
||
`,[userId])
|
||
|
||
commissionStats[0].directly_agents_income = directly_agents_income[0].amount || 0
|
||
|
||
// 获取直销商户分享利润
|
||
let [active_users_income] = operated_user_sql.length === 0 ?
|
||
await getDB().execute(`
|
||
SELECT SUM(amount) as amount
|
||
FROM transfers
|
||
WHERE transfer_type = 'user_to_agent'
|
||
AND source_type = 'agent_operated'
|
||
`,[...operated_user_ids])
|
||
:
|
||
await getDB().execute(`
|
||
SELECT SUM(amount) as amount
|
||
FROM transfers
|
||
WHERE (${operated_user_sql.join(' or ')})
|
||
AND transfer_type = 'user_to_agent'
|
||
AND source_type = 'agent_operated'
|
||
`,[...operated_user_ids])
|
||
commissionStats[0].active_users_income = active_users_income[0].amount || 0
|
||
|
||
|
||
commissionStats[0].operated_commission = operated_commission[0].amount || 0
|
||
|
||
// 获取已提现佣金
|
||
let [get_commission] = await getDB().execute(`
|
||
SELECT SUM(amount) as amount FROM transfers WHERE from_user_id=${userId} AND source_type='withdraw' AND created_at >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
|
||
`)
|
||
commissionStats[0].get_commission = get_commission[0].amount || 0
|
||
|
||
// 获取待提现佣金
|
||
let [loading_commission] = await getDB().execute(`
|
||
SELECT SUM(balance) as balance FROM users WHERE inviter=${userId}
|
||
`)
|
||
commissionStats[0].loading_commission = Math.abs(loading_commission[0].balance) || 0
|
||
|
||
// 获取总收入
|
||
let [total_commission] = await getDB().execute(`
|
||
SELECT SUM(balance) as balance FROM users WHERE id=${userId}
|
||
`)
|
||
commissionStats[0].total_commission =Math.abs(total_commission[0].balance) || 0
|
||
|
||
// 获取转账统计
|
||
const [transferStats] = await getDB().execute(`
|
||
SELECT COUNT(*) as total_transfers,
|
||
COUNT(CASE WHEN DATE(t.created_at) = CURDATE() THEN 1 END) as today_transfers,
|
||
CAST(COALESCE(SUM(t.amount), 0) AS DECIMAL(10, 2)) as total_amount,
|
||
CAST(COALESCE(SUM(CASE WHEN DATE(t.created_at) = CURDATE() THEN t.amount ELSE 0 END),
|
||
0) AS DECIMAL(10, 2)) as today_amount
|
||
FROM transfers t
|
||
INNER JOIN agent_merchants am ON (t.from_user_id = am.merchant_id OR t.to_user_id = am.merchant_id)
|
||
WHERE am.agent_id = ?
|
||
`, [agentId]);
|
||
|
||
const stats = {
|
||
users: userStats[0] || {
|
||
total_directly_agents: 0,
|
||
today_new_users: 0,
|
||
active_users: 0,
|
||
total_operated: userIds.length
|
||
},
|
||
commissions: commissionStats[0] || {
|
||
total_commission: '0.00',
|
||
today_commission: '0.00',
|
||
paid_commission: '0.00',
|
||
monthly_commission: '0.00'
|
||
},
|
||
transfers: transferStats[0] || {
|
||
total_transfers: 0,
|
||
today_transfers: 0,
|
||
total_amount: '0.00',
|
||
today_amount: '0.00'
|
||
}
|
||
};
|
||
|
||
res.json({
|
||
success: true,
|
||
data: stats
|
||
});
|
||
|
||
} catch (error) {
|
||
console.log('获取统计数据失败',error)
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '获取统计数据失败'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 获取用户增长趋势数据
|
||
* GET /api/agent/user-growth-trend
|
||
*/
|
||
router.get('/user-growth-trend', agentAuth, async (req, res) => {
|
||
try {
|
||
const {id: agentId, userId} = req.agent;
|
||
const {days = 7} = req.query;
|
||
|
||
const [trendData] = await getDB().execute(`
|
||
SELECT DATE(created_at) as date,
|
||
COUNT(*) as count
|
||
FROM users
|
||
WHERE inviter = ?
|
||
AND created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
|
||
GROUP BY DATE(created_at)
|
||
ORDER BY date ASC
|
||
`, [userId, parseInt(days)]);
|
||
// 填充缺失的日期（注册数为0）
|
||
const result = [];
|
||
|
||
for (let i = days - 1; i >= 0; i--) {
|
||
const date = dayjs().subtract(i, 'day');
|
||
const dateStr = date.format('YYYY-MM-DD');
|
||
|
||
// 修复日期比较：将数据库返回的Date对象转换为字符串进行比较
|
||
const existingData = trendData.find(item => {
|
||
const itemDateStr = dayjs(item.date).format('YYYY-MM-DD');
|
||
return itemDateStr === dateStr;
|
||
});
|
||
|
||
result.push({
|
||
date: date.format('MM-DD'),
|
||
count: existingData ? existingData.count : 0
|
||
});
|
||
}
|
||
res.json({
|
||
success: true,
|
||
data: result
|
||
});
|
||
|
||
} catch (error) {
|
||
logger.error('获取用户增长趋势失败', {
|
||
error: error.message,
|
||
stack: error.stack,
|
||
agentId: req.agent?.id
|
||
});
|
||
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '获取趋势数据失败'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 获取营收收入趋势数据
|
||
* GET /api/agent/commission-trend
|
||
*/
|
||
router.get('/commission-trend', agentAuth, async (req, res) => {
|
||
try {
|
||
const {id: agentId, userId} = req.agent;
|
||
const {days = 7} = req.query;
|
||
|
||
const [trendData] = await getDB().execute(`
|
||
SELECT DATE(created_at) as date,
|
||
CAST(COALESCE(SUM(amount), 0) AS DECIMAL(10, 2)) as amount
|
||
FROM transfers
|
||
WHERE to_user_id = ?
|
||
AND created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
|
||
AND source_type = 'user_to_agent'
|
||
GROUP BY DATE(created_at)
|
||
ORDER BY date ASC
|
||
`, [userId, parseInt(days)]);
|
||
|
||
// 填充缺失的日期（佣金为0）
|
||
const result = [];
|
||
|
||
for (let i = days - 1; i >= 0; i--) {
|
||
const date = dayjs().subtract(i, 'day');
|
||
const dateStr = date.format('YYYY-MM-DD');
|
||
|
||
const existingData = trendData.find(item => {
|
||
const itemDateStr = dayjs(item.date).format('YYYY-MM-DD');
|
||
return itemDateStr === dateStr;
|
||
});
|
||
|
||
result.push({
|
||
date: date.format('MM-DD'),
|
||
amount: existingData ? parseFloat(existingData.amount) : 0
|
||
});
|
||
}
|
||
|
||
res.json({
|
||
success: true,
|
||
data: result
|
||
});
|
||
|
||
} catch (error) {
|
||
logger.error('获取佣金趋势失败', {
|
||
error: error.message,
|
||
stack: error.stack,
|
||
agentId: req.agent?.id
|
||
});
|
||
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '获取趋势数据失败'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 获取用户活跃度数据
|
||
* GET /api/agent/user-activity
|
||
*/
|
||
router.get('/user-activity', agentAuth, async (req, res) => {
|
||
try {
|
||
const agentId = req.agent.id;
|
||
const {days = 7} = req.query;
|
||
|
||
// 获取活跃用户趋势（基于转账活动）
|
||
const [activityTrend] = await getDB().execute(`
|
||
SELECT DATE(t.created_at) as date,
|
||
COUNT(DISTINCT CASE WHEN t.from_user_id = am.merchant_id THEN t.from_user_id END) as active_senders,
|
||
COUNT(DISTINCT CASE
|
||
WHEN t.to_user_id = am.merchant_id
|
||
THEN t.to_user_id END) as active_receivers,
|
||
COUNT(DISTINCT am.merchant_id) as total_active_users
|
||
FROM transfers t
|
||
INNER JOIN agent_merchants am ON (t.from_user_id = am.merchant_id OR t.to_user_id = am.merchant_id)
|
||
WHERE am.agent_id = ?
|
||
AND t.created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
|
||
AND t.status = 'completed'
|
||
GROUP BY DATE(t.created_at)
|
||
ORDER BY date ASC
|
||
`, [agentId, parseInt(days)]);
|
||
|
||
// 获取用户活跃度统计
|
||
const [activityStats] = await getDB().execute(`
|
||
SELECT COUNT(DISTINCT am.merchant_id) as total_users,
|
||
COUNT(DISTINCT CASE
|
||
WHEN t.created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)
|
||
THEN am.merchant_id END) as daily_active_users,
|
||
COUNT(DISTINCT CASE
|
||
WHEN t.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
|
||
THEN am.merchant_id END) as weekly_active_users,
|
||
COUNT(DISTINCT CASE
|
||
WHEN t.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
|
||
THEN am.merchant_id END) as monthly_active_users,
|
||
ROUND(
|
||
COUNT(DISTINCT
|
||
CASE WHEN t.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN am.merchant_id END) *
|
||
100.0 /
|
||
NULLIF(COUNT(DISTINCT am.merchant_id), 0), 2
|
||
) as weekly_activity_rate
|
||
FROM agent_merchants am
|
||
LEFT JOIN transfers t ON (t.from_user_id = am.merchant_id OR t.to_user_id = am.merchant_id)
|
||
AND t.status = 'completed'
|
||
WHERE am.agent_id = ?
|
||
`, [agentId]);
|
||
|
||
// 填充缺失的日期
|
||
const trendResult = [];
|
||
for (let i = days - 1; i >= 0; i--) {
|
||
const date = dayjs().subtract(i, 'day');
|
||
const dateStr = date.format('YYYY-MM-DD');
|
||
|
||
const existingData = activityTrend.find(item => {
|
||
const itemDateStr = dayjs(item.date).format('YYYY-MM-DD');
|
||
return itemDateStr === dateStr;
|
||
});
|
||
|
||
trendResult.push({
|
||
date: date.format('MM-DD'),
|
||
active_users: existingData ? existingData.total_active_users : 0,
|
||
active_senders: existingData ? existingData.active_senders : 0,
|
||
active_receivers: existingData ? existingData.active_receivers : 0
|
||
});
|
||
}
|
||
|
||
res.json({
|
||
success: true,
|
||
data: {
|
||
trend: trendResult,
|
||
stats: activityStats[0] || {
|
||
total_users: 0,
|
||
daily_active_users: 0,
|
||
weekly_active_users: 0,
|
||
monthly_active_users: 0,
|
||
weekly_activity_rate: 0
|
||
}
|
||
}
|
||
});
|
||
|
||
} catch (error) {
|
||
logger.error('获取用户活跃度失败', {
|
||
error: error.message,
|
||
stack: error.stack,
|
||
agentId: req.agent?.id
|
||
});
|
||
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '获取用户活跃度数据失败'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 获取佣金类型分布数据
|
||
* GET /api/agent/commission-distribution
|
||
*/
|
||
router.get('/commission-distribution', agentAuth, async (req, res) => {
|
||
try {
|
||
const agentId = req.agent.id;
|
||
|
||
const [distributionData] = await getDB().execute(`
|
||
SELECT commission_type as type,
|
||
COUNT(*) as count,
|
||
CAST(COALESCE(SUM(commission_amount), 0) AS DECIMAL(10, 2)) as amount
|
||
FROM agent_commission_records
|
||
WHERE agent_id = ?
|
||
GROUP BY commission_type
|
||
ORDER BY amount DESC
|
||
`, [agentId]);
|
||
|
||
res.json({
|
||
success: true,
|
||
data: distributionData
|
||
});
|
||
|
||
} catch (error) {
|
||
logger.error('获取佣金分布失败', {
|
||
error: error.message,
|
||
stack: error.stack,
|
||
agentId: req.agent?.id
|
||
});
|
||
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '获取分布数据失败'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 获取最新下级用户
|
||
* GET /api/agent/recent-users
|
||
*/
|
||
router.get('/recent-users', agentAuth, async (req, res) => {
|
||
try {
|
||
const {id: agentId, userId} = req.agent;
|
||
const {limit = 10} = req.query;
|
||
|
||
const limitValue = Math.max(1, Math.min(100, parseInt(limit))); // 限制在1-100之间
|
||
const [recentUsers] = await getDB().execute(`
|
||
SELECT u.id,
|
||
u.username,
|
||
u.real_name,
|
||
u.phone,
|
||
u.avatar,
|
||
u.balance,
|
||
u.created_at,
|
||
u.created_at as join_date
|
||
FROM users u
|
||
WHERE u.inviter = ?
|
||
ORDER BY u.created_at DESC
|
||
LIMIT ${limitValue}
|
||
`, [userId]);
|
||
|
||
res.json({
|
||
success: true,
|
||
data: recentUsers
|
||
});
|
||
|
||
} catch (error) {
|
||
logger.error('获取最新用户失败', {
|
||
error: error.message,
|
||
stack: error.stack,
|
||
agentId: req.agent?.id
|
||
});
|
||
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '获取最新用户失败'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 获取最新佣金记录
|
||
* GET /api/agent/recent-commissions
|
||
*/
|
||
router.get('/recent-commissions', agentAuth, async (req, res) => {
|
||
try {
|
||
const {id: agentId, userId} = req.agent;
|
||
const {limit = 10} = req.query;
|
||
|
||
const limitValue = Math.max(1, Math.min(100, parseInt(limit))); // 限制在1-100之间
|
||
const [recentCommissions] = await getDB().execute(`
|
||
SELECT acr.id,
|
||
acr.amount as commission_amount,
|
||
acr.created_at,
|
||
u.username,
|
||
u.real_name
|
||
FROM transfers acr
|
||
LEFT JOIN users u ON acr.from_user_id = u.id
|
||
WHERE acr.to_user_id = ?
|
||
AND source_type = 'user_to_agent'
|
||
ORDER BY acr.created_at DESC
|
||
LIMIT ${limitValue}
|
||
`, [agentId]);
|
||
|
||
res.json({
|
||
success: true,
|
||
data: recentCommissions
|
||
});
|
||
|
||
} catch (error) {
|
||
logger.error('获取最新佣金失败', {
|
||
error: error.message,
|
||
stack: error.stack,
|
||
agentId: req.agent?.id
|
||
});
|
||
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '获取最新佣金失败'
|
||
});
|
||
}
|
||
});
|
||
|
||
module.exports = router; |