miaoguo_system_server/scripts/scan_2months.mjs

import Miaoguo from '../src/model/miaoguo.js';

const startDate = new Date(Date.now() - 60 * 86400000).toISOString().slice(0, 10);
console.log('统计区间: >=', startDate);

// 1) DayNumber 异常（全时段，限定近两个月有任务的用户）
const bad = await Miaoguo.RunSql([startDate], `
    SELECT UserID, COUNT(DISTINCT DATE(CreateTime)) days, MAX(DayNumber) maxDay,
           SUM(CASE WHEN DayNumber=0 THEN 1 ELSE 0 END) zeroCnt
    FROM MiaoguoTask
    WHERE CardTask>0 AND UserID IN (SELECT DISTINCT UserID FROM MiaoguoTask WHERE CardTask>0 AND CreateTime>=?)
    GROUP BY UserID
    HAVING zeroCnt>0 OR maxDay<>days
    ORDER BY UserID;`);
console.log('\n[1] DayNumber 异常用户数:', bad.length);
if (bad.length) console.log('    ', bad.map(r => `U${r.UserID}(z=${r.zeroCnt},max=${r.maxDay},days=${r.days})`).join(', '));

// 2) 近两个月：有学习记录但没有 MiaoguoTask 记录的天（需要补齐）
const missing = await Miaoguo.RunSql([startDate], `
    SELECT mc.UserID, DATE_FORMAT(r.CreateTime,'%Y-%m-%d') d,
           COUNT(DISTINCT r.CardID) CardCount, SUM(r.Duration) Duration
    FROM MiaoguoLearningRecord r
    INNER JOIN MiaoguoCard mc ON mc.MiaoguoCardID = r.CardID
    LEFT JOIN MiaoguoTask t ON t.UserID = mc.UserID AND DATE(t.CreateTime) = DATE(r.CreateTime)
    WHERE r.Flag=0 AND r.CreateTime >= ? AND t.ID IS NULL
    GROUP BY mc.UserID, DATE_FORMAT(r.CreateTime,'%Y-%m-%d')
    ORDER BY mc.UserID, d;`);
const mUsers = [...new Set(missing.map(r => r.UserID))];
console.log('\n[2] 缺 MiaoguoTask 记录: 受影响用户数:', mUsers.length, ' 缺失天数合计:', missing.length);
console.log('    用户:', mUsers.join(','));
if (missing.length) {
    console.log('    明细(前30):');
    for (const r of missing.slice(0, 30))
        console.log(`      U${r.UserID} ${r.d} Card=${r.CardCount} Dur=${r.Duration}`);
}

// 3) 近两个月同一天多条 MiaoguoTask 记录
const dup = await Miaoguo.RunSql([startDate], `
    SELECT UserID, DATE_FORMAT(CreateTime,'%Y-%m-%d') d, COUNT(*) c
    FROM MiaoguoTask
    WHERE CardTask>0 AND CreateTime>=?
    GROUP BY UserID, DATE_FORMAT(CreateTime,'%Y-%m-%d')
    HAVING c>1
    ORDER BY UserID, d;`);
console.log('\n[3] 同一天多条记录: 天数:', dup.length);
if (dup.length) {
    const dUsers = [...new Set(dup.map(r => r.UserID))];
    console.log('    用户:', dUsers.join(','));
    console.log('    明细:', dup.map(r => `U${r.UserID}@${r.d}x${r.c}`).join(', '));
}

process.exit(0);