const mysql = require('mysql2/promise');
|
|
// 数据库配置
|
const dbConfig = {
|
host: '139.155.104.10',
|
port: 3306,
|
user: 'ryc',
|
password: 'KiYap3E8X8RLcM6T',
|
database: 'ryc'
|
};
|
|
async function testActivityStatesAndMapping() {
|
let connection;
|
|
try {
|
console.log('=== 小程序活动状态映射修复测试 ===\n');
|
|
// 连接数据库
|
connection = await mysql.createConnection(dbConfig);
|
console.log('✅ 数据库连接成功');
|
|
// 1. 检查数据库中的活动状态分布
|
console.log('\n📊 数据库活动状态分布:');
|
const [stateStats] = await connection.execute(`
|
SELECT
|
state,
|
COUNT(*) as count,
|
CASE
|
WHEN state = 0 THEN '未发布(删除)'
|
WHEN state = 1 THEN '发布'
|
WHEN state = 2 THEN '关闭'
|
ELSE '未知状态'
|
END as state_name
|
FROM t_activity
|
WHERE pid = 0
|
GROUP BY state
|
ORDER BY state
|
`);
|
|
stateStats.forEach(row => {
|
console.log(` State ${row.state} (${row.state_name}): ${row.count} 个活动`);
|
});
|
|
// 2. 验证修复后的状态映射逻辑
|
console.log('\n🔧 修复后的状态映射逻辑:');
|
console.log(' upcoming (即将开始) -> state=1 (发布)');
|
console.log(' ongoing (进行中) -> state=1 (发布)');
|
console.log(' ended (已结束) -> state=2 (关闭)');
|
console.log(' all (全部) -> state=null (后端过滤 state!=0)');
|
|
// 3. 模拟不同筛选条件的查询结果
|
console.log('\n🧪 模拟小程序筛选查询:');
|
|
// 模拟 filterStatus = 'all' (state = null)
|
const [allActivities] = await connection.execute(`
|
SELECT id, name, state,
|
CASE
|
WHEN state = 0 THEN '未发布(删除)'
|
WHEN state = 1 THEN '发布'
|
WHEN state = 2 THEN '关闭'
|
ELSE '未知状态'
|
END as state_name
|
FROM t_activity
|
WHERE pid = 0 AND state != 0
|
ORDER BY create_time DESC
|
LIMIT 5
|
`);
|
console.log(`\n 筛选条件: 全部 (all) - 应该排除已删除活动:`);
|
console.log(` 查询结果: ${allActivities.length} 个活动`);
|
allActivities.forEach(activity => {
|
console.log(` - ${activity.name} (state=${activity.state}, ${activity.state_name})`);
|
});
|
|
// 模拟 filterStatus = 'upcoming' 或 'ongoing' (state = 1)
|
const [publishedActivities] = await connection.execute(`
|
SELECT id, name, state,
|
CASE
|
WHEN state = 0 THEN '未发布(删除)'
|
WHEN state = 1 THEN '发布'
|
WHEN state = 2 THEN '关闭'
|
ELSE '未知状态'
|
END as state_name
|
FROM t_activity
|
WHERE pid = 0 AND state = 1
|
ORDER BY create_time DESC
|
LIMIT 5
|
`);
|
console.log(`\n 筛选条件: 即将开始/进行中 (upcoming/ongoing) - state=1:`);
|
console.log(` 查询结果: ${publishedActivities.length} 个活动`);
|
publishedActivities.forEach(activity => {
|
console.log(` - ${activity.name} (state=${activity.state}, ${activity.state_name})`);
|
});
|
|
// 模拟 filterStatus = 'ended' (state = 2)
|
const [closedActivities] = await connection.execute(`
|
SELECT id, name, state,
|
CASE
|
WHEN state = 0 THEN '未发布(删除)'
|
WHEN state = 1 THEN '发布'
|
WHEN state = 2 THEN '关闭'
|
ELSE '未知状态'
|
END as state_name
|
FROM t_activity
|
WHERE pid = 0 AND state = 2
|
ORDER BY create_time DESC
|
LIMIT 5
|
`);
|
console.log(`\n 筛选条件: 已结束 (ended) - state=2:`);
|
console.log(` 查询结果: ${closedActivities.length} 个活动`);
|
closedActivities.forEach(activity => {
|
console.log(` - ${activity.name} (state=${activity.state}, ${activity.state_name})`);
|
});
|
|
// 4. 验证是否还有state=0的活动会被显示
|
const [deletedActivities] = await connection.execute(`
|
SELECT id, name, state
|
FROM t_activity
|
WHERE pid = 0 AND state = 0
|
`);
|
|
console.log(`\n✅ 验证结果:`);
|
console.log(` - 数据库中有 ${deletedActivities.length} 个已删除活动 (state=0)`);
|
console.log(` - 修复后的映射确保这些活动不会在任何筛选条件下显示`);
|
console.log(` - 'all' 筛选会排除 state=0 的活动`);
|
console.log(` - 其他筛选条件只会查询 state=1 或 state=2 的活动`);
|
|
if (deletedActivities.length === 0) {
|
console.log(` - 当前数据库中没有已删除的活动，修复主要解决了状态映射问题`);
|
}
|
|
} catch (error) {
|
console.error('❌ 测试失败:', error.message);
|
} finally {
|
if (connection) {
|
await connection.end();
|
console.log('\n🔌 数据库连接已关闭');
|
}
|
}
|
}
|
|
// 运行测试
|
testActivityStatesAndMapping();
|
