import { AppDataSource, WorkDayStatistics, Work, PlatformAccount } from '../models/index.js'; import { Between, In } from 'typeorm'; import { logger } from '../utils/logger.js'; interface StatisticsItem { workId: number; fansCount?: number; playCount?: number; likeCount?: number; commentCount?: number; shareCount?: number; collectCount?: number; } interface SaveResult { inserted: number; updated: number; } interface TrendData { dates: string[]; fans: number[]; views: number[]; likes: number[]; comments: number[]; shares: number[]; collects: number[]; } interface PlatformStatItem { platform: string; fansCount: number; fansIncrease: number; viewsCount: number; likesCount: number; commentsCount: number; collectsCount: number; } interface WorkStatisticsItem { recordDate: string; fansCount: number; playCount: number; likeCount: number; commentCount: number; shareCount: number; collectCount: number; } export class WorkDayStatisticsService { private statisticsRepository = AppDataSource.getRepository(WorkDayStatistics); private workRepository = AppDataSource.getRepository(Work); private accountRepository = AppDataSource.getRepository(PlatformAccount); /** * 保存作品日统计数据 * 当天的数据走更新流,日期变化走新增流 */ async saveStatistics(statistics: StatisticsItem[]): Promise { const today = new Date(); today.setHours(0, 0, 0, 0); let insertedCount = 0; let updatedCount = 0; for (const stat of statistics) { if (!stat.workId) continue; // 检查当天是否已有记录 const existing = await this.statisticsRepository.findOne({ where: { workId: stat.workId, recordDate: today, }, }); if (existing) { // 更新已有记录 await this.statisticsRepository.update(existing.id, { fansCount: stat.fansCount ?? existing.fansCount, playCount: stat.playCount ?? existing.playCount, likeCount: stat.likeCount ?? existing.likeCount, commentCount: stat.commentCount ?? existing.commentCount, shareCount: stat.shareCount ?? existing.shareCount, collectCount: stat.collectCount ?? existing.collectCount, }); updatedCount++; } else { // 插入新记录 const newStat = this.statisticsRepository.create({ workId: stat.workId, recordDate: today, fansCount: stat.fansCount ?? 0, playCount: stat.playCount ?? 0, likeCount: stat.likeCount ?? 0, commentCount: stat.commentCount ?? 0, shareCount: stat.shareCount ?? 0, collectCount: stat.collectCount ?? 0, }); await this.statisticsRepository.save(newStat); insertedCount++; } } return { inserted: insertedCount, updated: updatedCount }; } /** * 获取数据趋势 */ async getTrend( userId: number, options: { days?: number; startDate?: string; endDate?: string; accountId?: number; } ): Promise { const { days = 7, startDate, endDate, accountId } = options; // 计算日期范围 let dateStart: Date; let dateEnd: Date; if (startDate && endDate) { dateStart = new Date(startDate); dateEnd = new Date(endDate); } else { dateEnd = new Date(); dateStart = new Date(); dateStart.setDate(dateStart.getDate() - Math.min(days, 30) + 1); } // 构建查询 const queryBuilder = this.statisticsRepository .createQueryBuilder('wds') .innerJoin(Work, 'w', 'wds.work_id = w.id') .select('wds.record_date', 'recordDate') .addSelect('w.accountId', 'accountId') .addSelect('MAX(wds.fans_count)', 'accountFans') .addSelect('SUM(wds.play_count)', 'accountViews') .addSelect('SUM(wds.like_count)', 'accountLikes') .addSelect('SUM(wds.comment_count)', 'accountComments') .addSelect('SUM(wds.share_count)', 'accountShares') .addSelect('SUM(wds.collect_count)', 'accountCollects') .where('w.userId = :userId', { userId }) .andWhere('wds.record_date >= :dateStart', { dateStart }) .andWhere('wds.record_date <= :dateEnd', { dateEnd }) .groupBy('wds.record_date') .addGroupBy('w.accountId') .orderBy('wds.record_date', 'ASC'); if (accountId) { queryBuilder.andWhere('w.accountId = :accountId', { accountId }); } const accountResults = await queryBuilder.getRawMany(); // 按日期汇总所有账号的数据 const dateMap = new Map(); for (const row of accountResults) { const dateKey = row.recordDate instanceof Date ? row.recordDate.toISOString().split('T')[0] : String(row.recordDate).split('T')[0]; if (!dateMap.has(dateKey)) { dateMap.set(dateKey, { fans: 0, views: 0, likes: 0, comments: 0, shares: 0, collects: 0, }); } const current = dateMap.get(dateKey)!; current.fans += parseInt(row.accountFans) || 0; current.views += parseInt(row.accountViews) || 0; current.likes += parseInt(row.accountLikes) || 0; current.comments += parseInt(row.accountComments) || 0; current.shares += parseInt(row.accountShares) || 0; current.collects += parseInt(row.accountCollects) || 0; } // 构建响应数据 const dates: string[] = []; const fans: number[] = []; const views: number[] = []; const likes: number[] = []; const comments: number[] = []; const shares: number[] = []; const collects: number[] = []; // 按日期排序 const sortedDates = Array.from(dateMap.keys()).sort(); for (const dateKey of sortedDates) { dates.push(dateKey.slice(5)); // "YYYY-MM-DD" -> "MM-DD" const data = dateMap.get(dateKey)!; fans.push(data.fans); views.push(data.views); likes.push(data.likes); comments.push(data.comments); shares.push(data.shares); collects.push(data.collects); } // 如果没有数据,生成空的日期范围 if (dates.length === 0) { const d = new Date(dateStart); while (d <= dateEnd) { dates.push(`${String(d.getMonth() + 1).padStart(2, '0')}-${String(d.getDate()).padStart(2, '0')}`); fans.push(0); views.push(0); likes.push(0); comments.push(0); shares.push(0); collects.push(0); d.setDate(d.getDate() + 1); } } return { dates, fans, views, likes, comments, shares, collects }; } /** * 按平台分组获取统计数据 */ async getStatisticsByPlatform( userId: number, options: { days?: number; startDate?: string; endDate?: string; } ): Promise { const { days = 30, startDate, endDate } = options; // 计算日期范围 let dateStart: Date; let dateEnd: Date; if (startDate && endDate) { dateStart = new Date(startDate); dateEnd = new Date(endDate); } else { dateEnd = new Date(); dateStart = new Date(); dateStart.setDate(dateStart.getDate() - Math.min(days, 30) + 1); } // 获取用户的所有账号 const accounts = await this.accountRepository.find({ where: { userId }, }); const platformData: PlatformStatItem[] = []; for (const account of accounts) { // 获取该账号在区间内第一天和最后一天的数据 const firstDayQuery = this.statisticsRepository .createQueryBuilder('wds') .innerJoin(Work, 'w', 'wds.work_id = w.id') .select('MAX(wds.fans_count)', 'fans') .addSelect('SUM(wds.play_count)', 'views') .addSelect('SUM(wds.like_count)', 'likes') .addSelect('SUM(wds.comment_count)', 'comments') .addSelect('SUM(wds.collect_count)', 'collects') .where('w.accountId = :accountId', { accountId: account.id }) .andWhere('wds.record_date = (SELECT MIN(record_date) FROM work_day_statistics wds2 INNER JOIN works w2 ON wds2.work_id = w2.id WHERE w2.accountId = :accountId2 AND wds2.record_date >= :dateStart AND wds2.record_date <= :dateEnd)', { accountId2: account.id, dateStart, dateEnd, }); const lastDayQuery = this.statisticsRepository .createQueryBuilder('wds') .innerJoin(Work, 'w', 'wds.work_id = w.id') .select('MAX(wds.fans_count)', 'fans') .addSelect('SUM(wds.play_count)', 'views') .addSelect('SUM(wds.like_count)', 'likes') .addSelect('SUM(wds.comment_count)', 'comments') .addSelect('SUM(wds.collect_count)', 'collects') .where('w.accountId = :accountId', { accountId: account.id }) .andWhere('wds.record_date = (SELECT MAX(record_date) FROM work_day_statistics wds2 INNER JOIN works w2 ON wds2.work_id = w2.id WHERE w2.accountId = :accountId2 AND wds2.record_date >= :dateStart AND wds2.record_date <= :dateEnd)', { accountId2: account.id, dateStart, dateEnd, }); const [firstDay, lastDay] = await Promise.all([ firstDayQuery.getRawOne(), lastDayQuery.getRawOne(), ]); const currentFans = account.fansCount ?? 0; const earliestFans = parseInt(firstDay?.fans) || currentFans; const fansIncrease = currentFans - earliestFans; const viewsIncrease = (parseInt(lastDay?.views) || 0) - (parseInt(firstDay?.views) || 0); const likesIncrease = (parseInt(lastDay?.likes) || 0) - (parseInt(firstDay?.likes) || 0); const commentsIncrease = (parseInt(lastDay?.comments) || 0) - (parseInt(firstDay?.comments) || 0); const collectsIncrease = (parseInt(lastDay?.collects) || 0) - (parseInt(firstDay?.collects) || 0); platformData.push({ platform: account.platform, fansCount: currentFans, fansIncrease, viewsCount: Math.max(0, viewsIncrease), likesCount: Math.max(0, likesIncrease), commentsCount: Math.max(0, commentsIncrease), collectsCount: Math.max(0, collectsIncrease), }); } // 按粉丝数降序排序 platformData.sort((a, b) => b.fansCount - a.fansCount); return platformData; } /** * 批量获取作品的历史统计数据 */ async getWorkStatisticsHistory( workIds: number[], options: { startDate?: string; endDate?: string; } ): Promise> { const { startDate, endDate } = options; const queryBuilder = this.statisticsRepository .createQueryBuilder('wds') .select('wds.work_id', 'workId') .addSelect('wds.record_date', 'recordDate') .addSelect('wds.fans_count', 'fansCount') .addSelect('wds.play_count', 'playCount') .addSelect('wds.like_count', 'likeCount') .addSelect('wds.comment_count', 'commentCount') .addSelect('wds.share_count', 'shareCount') .addSelect('wds.collect_count', 'collectCount') .where('wds.work_id IN (:...workIds)', { workIds }) .orderBy('wds.work_id', 'ASC') .addOrderBy('wds.record_date', 'ASC'); if (startDate) { queryBuilder.andWhere('wds.record_date >= :startDate', { startDate }); } if (endDate) { queryBuilder.andWhere('wds.record_date <= :endDate', { endDate }); } const results = await queryBuilder.getRawMany(); // 按 workId 分组 const groupedData: Record = {}; for (const row of results) { const workId = String(row.workId); if (!groupedData[workId]) { groupedData[workId] = []; } const recordDate = row.recordDate instanceof Date ? row.recordDate.toISOString().split('T')[0] : String(row.recordDate).split('T')[0]; groupedData[workId].push({ recordDate, fansCount: parseInt(row.fansCount) || 0, playCount: parseInt(row.playCount) || 0, likeCount: parseInt(row.likeCount) || 0, commentCount: parseInt(row.commentCount) || 0, shareCount: parseInt(row.shareCount) || 0, collectCount: parseInt(row.collectCount) || 0, }); } return groupedData; } /** * 获取数据总览 * 返回账号列表和汇总统计数据 */ async getOverview(userId: number): Promise<{ accounts: Array<{ id: number; nickname: string; username: string; avatarUrl: string | null; platform: string; groupId: number | null; fansCount: number; totalIncome: number | null; yesterdayIncome: number | null; totalViews: number | null; yesterdayViews: number | null; yesterdayComments: number; yesterdayLikes: number; yesterdayFansIncrease: number; updateTime: string; status: string; }>; summary: { totalAccounts: number; totalIncome: number; yesterdayIncome: number; totalViews: number; yesterdayViews: number; totalFans: number; yesterdayComments: number; yesterdayLikes: number; yesterdayFansIncrease: number; }; }> { // 只查询支持的平台:抖音、百家号、视频号、小红书 const allowedPlatforms = ['douyin', 'baijiahao', 'weixin_video', 'xiaohongshu']; // 获取用户的所有账号(只包含支持的平台) const accounts = await this.accountRepository.find({ where: { userId, platform: In(allowedPlatforms), }, }); // 使用中国时区(UTC+8)计算“今天/昨天”的业务日期 // 思路:在当前 UTC 时间基础上 +8 小时,再取 ISO 日期部分,即为中国日历日期 const now = new Date(); const chinaNow = new Date(now.getTime() + 8 * 60 * 60 * 1000); const chinaYesterday = new Date(chinaNow.getTime() - 24 * 60 * 60 * 1000); // 格式化为 YYYY-MM-DD,与 MySQL DATE 字段匹配 const todayStr = chinaNow.toISOString().split('T')[0]; const yesterdayStr = chinaYesterday.toISOString().split('T')[0]; logger.info(`[WorkDayStatistics] getOverview - userId: ${userId}, today: ${todayStr}, yesterday: ${yesterdayStr}`); const accountList: Array<{ id: number; nickname: string; username: string; avatarUrl: string | null; platform: string; groupId: number | null; fansCount: number; totalIncome: number | null; yesterdayIncome: number | null; totalViews: number | null; yesterdayViews: number | null; yesterdayComments: number; yesterdayLikes: number; yesterdayFansIncrease: number; updateTime: string; status: string; }> = []; // 汇总统计数据 let totalAccounts = 0; let totalIncome = 0; let yesterdayIncome = 0; let totalViews = 0; let yesterdayViews = 0; let totalFans = 0; let yesterdayComments = 0; let yesterdayLikes = 0; let yesterdayFansIncrease = 0; for (const account of accounts) { // 获取该账号的所有作品ID const works = await this.workRepository.find({ where: { accountId: account.id }, select: ['id'], }); if (works.length === 0) { // 如果没有作品,只返回账号基本信息 const accountFansCount = account.fansCount || 0; accountList.push({ id: account.id, nickname: account.accountName || '', username: account.accountId || '', avatarUrl: account.avatarUrl, platform: account.platform, groupId: account.groupId, fansCount: accountFansCount, totalIncome: null, yesterdayIncome: null, totalViews: null, yesterdayViews: null, yesterdayComments: 0, yesterdayLikes: 0, yesterdayFansIncrease: 0, updateTime: account.updatedAt.toISOString(), status: account.status, }); // 即使没有作品,也要累加账号的粉丝数到总粉丝数 totalAccounts++; totalFans += accountFansCount; continue; } const workIds = works.map(w => w.id); // 获取每个作品的最新日期统计数据(总播放量等) const latestStatsQuery = this.statisticsRepository .createQueryBuilder('wds') .select('wds.work_id', 'workId') .addSelect('MAX(wds.record_date)', 'latestDate') .addSelect('MAX(wds.play_count)', 'playCount') .addSelect('MAX(wds.like_count)', 'likeCount') .addSelect('MAX(wds.comment_count)', 'commentCount') .addSelect('MAX(wds.fans_count)', 'fansCount') .where('wds.work_id IN (:...workIds)', { workIds }) .groupBy('wds.work_id'); const latestStats = await latestStatsQuery.getRawMany(); // 计算总播放量(所有作品最新日期的play_count总和) let accountTotalViews = 0; const latestDateMap = new Map(); for (const stat of latestStats) { accountTotalViews += parseInt(stat.playCount) || 0; latestDateMap.set(stat.workId, stat.latestDate); } // 获取昨天和今天的数据来计算增量 // 使用日期字符串直接比较(DATE 类型会自动转换) const yesterdayStatsQuery = this.statisticsRepository .createQueryBuilder('wds') .select('wds.work_id', 'workId') .addSelect('SUM(wds.play_count)', 'playCount') .addSelect('SUM(wds.like_count)', 'likeCount') .addSelect('SUM(wds.comment_count)', 'commentCount') .addSelect('MAX(wds.fans_count)', 'fansCount') .where('wds.work_id IN (:...workIds)', { workIds }) .andWhere('wds.record_date = :yesterday', { yesterday: yesterdayStr }) .groupBy('wds.work_id'); const todayStatsQuery = this.statisticsRepository .createQueryBuilder('wds') .select('wds.work_id', 'workId') .addSelect('SUM(wds.play_count)', 'playCount') .addSelect('SUM(wds.like_count)', 'likeCount') .addSelect('SUM(wds.comment_count)', 'commentCount') .addSelect('MAX(wds.fans_count)', 'fansCount') .where('wds.work_id IN (:...workIds)', { workIds }) .andWhere('wds.record_date = :today', { today: todayStr }) .groupBy('wds.work_id'); const [yesterdayStats, todayStats] = await Promise.all([ yesterdayStatsQuery.getRawMany(), todayStatsQuery.getRawMany(), ]); logger.info(`[WorkDayStatistics] Account ${account.id} (${account.accountName}) - workIds: ${workIds.length}, yesterdayStats: ${yesterdayStats.length}, todayStats: ${todayStats.length}`); if (yesterdayStats.length > 0 || todayStats.length > 0) { logger.debug(`[WorkDayStatistics] yesterdayStats:`, JSON.stringify(yesterdayStats.slice(0, 3))); logger.debug(`[WorkDayStatistics] todayStats:`, JSON.stringify(todayStats.slice(0, 3))); } // 计算昨日增量 let accountYesterdayViews = 0; let accountYesterdayComments = 0; let accountYesterdayLikes = 0; let accountYesterdayFansIncrease = 0; // 按作品ID汇总 const yesterdayMap = new Map(); const todayMap = new Map(); for (const stat of yesterdayStats) { const workId = parseInt(String(stat.workId)) || 0; yesterdayMap.set(workId, { play: Number(stat.playCount) || 0, like: Number(stat.likeCount) || 0, comment: Number(stat.commentCount) || 0, fans: Number(stat.fansCount) || 0, }); } for (const stat of todayStats) { const workId = parseInt(String(stat.workId)) || 0; todayMap.set(workId, { play: Number(stat.playCount) || 0, like: Number(stat.likeCount) || 0, comment: Number(stat.commentCount) || 0, fans: Number(stat.fansCount) || 0, }); } logger.debug(`[WorkDayStatistics] Account ${account.id} - yesterdayMap size: ${yesterdayMap.size}, todayMap size: ${todayMap.size}`); // 计算增量(今天 - 昨天) for (const workId of workIds) { const todayData = todayMap.get(workId) || { play: 0, like: 0, comment: 0, fans: 0 }; const yesterdayData = yesterdayMap.get(workId) || { play: 0, like: 0, comment: 0, fans: 0 }; const viewsDiff = todayData.play - yesterdayData.play; const commentsDiff = todayData.comment - yesterdayData.comment; const likesDiff = todayData.like - yesterdayData.like; accountYesterdayViews += Math.max(0, viewsDiff); accountYesterdayComments += Math.max(0, commentsDiff); accountYesterdayLikes += Math.max(0, likesDiff); } logger.info(`[WorkDayStatistics] Account ${account.id} - Calculated: views=${accountYesterdayViews}, comments=${accountYesterdayComments}, likes=${accountYesterdayLikes}`); // 获取账号的最新粉丝数(从最新日期的统计数据中取最大值) let accountFansCount = account.fansCount || 0; if (latestStats.length > 0) { const maxFans = Math.max(...latestStats.map(s => parseInt(s.fansCount) || 0)); if (maxFans > 0) { accountFansCount = maxFans; } } // 计算昨日涨粉(今天最新粉丝数 - 昨天最新粉丝数) // 如果今天有统计数据,用今天数据中的最大粉丝数;否则用账号表的当前粉丝数 const todayMaxFans = todayStats.length > 0 ? Math.max(...todayStats.map(s => parseInt(s.fansCount) || 0)) : accountFansCount; // 如果昨天有统计数据,用昨天数据中的最大粉丝数;否则需要找最近一天的数据作为基准 let yesterdayMaxFans: number; if (yesterdayStats.length > 0) { // 昨天有数据,直接用昨天的最大粉丝数 yesterdayMaxFans = Math.max(...yesterdayStats.map(s => parseInt(s.fansCount) || 0)); } else { // 昨天没有数据,需要找最近一天的数据作为基准 // 查询该账号最近一天(早于今天)的统计数据 const recentStatsQuery = this.statisticsRepository .createQueryBuilder('wds') .select('MAX(wds.fans_count)', 'fansCount') .where('wds.work_id IN (:...workIds)', { workIds }) .andWhere('wds.record_date < :today', { today: todayStr }); const recentStat = await recentStatsQuery.getRawOne(); if (recentStat && recentStat.fansCount) { // 找到了最近一天的数据,用它作为基准 yesterdayMaxFans = parseInt(recentStat.fansCount) || accountFansCount; } else { // 完全没有历史数据,用账号表的当前粉丝数作为基准(但这样计算出来的增量可能不准确) yesterdayMaxFans = accountFansCount; } } accountYesterdayFansIncrease = todayMaxFans - yesterdayMaxFans; accountList.push({ id: account.id, nickname: account.accountName || '', username: account.accountId || '', avatarUrl: account.avatarUrl, platform: account.platform, groupId: account.groupId, fansCount: accountFansCount, totalIncome: null, // 收益数据需要从其他表获取,暂时为null yesterdayIncome: null, totalViews: accountTotalViews > 0 ? accountTotalViews : null, yesterdayViews: accountYesterdayViews > 0 ? accountYesterdayViews : null, yesterdayComments: accountYesterdayComments, yesterdayLikes: accountYesterdayLikes, yesterdayFansIncrease: accountYesterdayFansIncrease, updateTime: account.updatedAt.toISOString(), status: account.status, }); // 累加汇总数据 totalAccounts++; totalViews += accountTotalViews; yesterdayViews += accountYesterdayViews; totalFans += accountFansCount; yesterdayComments += accountYesterdayComments; yesterdayLikes += accountYesterdayLikes; yesterdayFansIncrease += accountYesterdayFansIncrease; } return { accounts: accountList, summary: { totalAccounts, totalIncome, yesterdayIncome, totalViews, yesterdayViews, totalFans, yesterdayComments, yesterdayLikes, yesterdayFansIncrease, }, }; } }