18307996893
/
tihao-ai


			
				
					
						
						
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221
							import type { NormalizedCandidate } from './recommendation.service.ts';

function escapeHtml(value: string): string {
  return value
    .replaceAll('&', '&amp;')
    .replaceAll('<', '&lt;')
    .replaceAll('>', '&gt;')
    .replaceAll('"', '&quot;');
}

/**
 * 生成媒体资源推荐表 Excel (HTML table format compatible with MS Excel)
 * 按模板生成：账号名称、ID、账号类型、地区、链接、粉丝数、赞藏数、
 * 图文/视频报价、推荐等级、推荐理由等字段
 */
export function generateExcelBuffer(candidates: NormalizedCandidate[], taskFileName?: string): Buffer {
  const title = taskFileName ? taskFileName.replace(/\.[^.]+$/, '') : '达人推荐表';
  const now = new Date().toLocaleDateString('zh-CN');
  const evaluationHeaders = buildEvaluationHeaders(candidates);
  const colCount = 28 + evaluationHeaders.length;

  const headerRow = `
    <tr>
      <th>序号</th>
      <th>平台</th>
      <th>账号名称</th>
      <th>账号ID</th>
      <th>地区</th>
      <th>主页链接</th>
      <th>粉丝数</th>
      <th>图文报价</th>
      <th>视频报价</th>
      <th>最低报价</th>
      <th>内容标签</th>
      <th>综合评分</th>
      <th>风格匹配</th>
      <th>推荐等级</th>
      <th>推荐理由</th>
      <th>风险提示</th>
      <th>数据来源</th>
      <th>昵称</th>
      <th>性别</th>
      <th>粉丝数（万）</th>
      <th>赞藏数（万）</th>
      <th>内容类型</th>
      <th>城市</th>
      <th>地理位置</th>
      <th>小红书主页</th>
      <th>合作方式</th>
      <th>图文笔记报价（含平台服务费）</th>
      <th>视频笔记报价（含平台服务费）</th>
      ${evaluationHeaders.map((header) => `<th>${escapeHtml(header)}</th>`).join('')}
    </tr>`;

  const rows = candidates.map((c, index) => `
    <tr>
      <td>${index + 1}</td>
      <td>${escapeHtml(mapPlatformDisplay(c.platform))}</td>
      <td>${escapeHtml(c.displayName)}</td>
      <td>${escapeHtml(c.platformUserId)}</td>
      <td>${escapeHtml(c.location || '-')}</td>
      <td>${escapeHtml(c.profileUrl)}</td>
      <td>${formatFans(c.fansCount)}</td>
      <td>${c.imagePrice > 0 ? `¥${c.imagePrice}` : '-'}</td>
      <td>${c.videoPrice > 0 ? `¥${c.videoPrice}` : '-'}</td>
      <td>${c.minPrice > 0 ? `¥${c.minPrice}` : '-'}</td>
      <td>${escapeHtml(c.contentTags.join('、') || '-')}</td>
      <td>${c.score}</td>
      <td>${c.styleMatch}%</td>
      <td>${escapeHtml(c.recommendStatus)}</td>
      <td>${escapeHtml(c.recommendReason || '-')}</td>
      <td>${escapeHtml(c.riskNote || '-')}</td>
      <td>${escapeHtml(c.sourceProvider)}</td>
      <td>${escapeHtml(c.displayName)}</td>
      <td>${escapeHtml(c.gender || '-')}</td>
      <td>${formatWan(c.fansCount)}</td>
      <td>${formatWan(c.likedCollectCount || 0)}</td>
      <td>${escapeHtml(c.contentType || c.contentTags.join('、') || '-')}</td>
      <td>${escapeHtml(c.city || c.location || '-')}</td>
      <td>${escapeHtml(c.geoLocation || c.location || '-')}</td>
      <td>${escapeHtml(c.xiaohongshuUrl || (c.platform === 'xiaohongshu' ? c.profileUrl : '-'))}</td>
      <td>${escapeHtml(c.cooperationMethod || '-')}</td>
      <td>${c.imagePrice > 0 ? `¥${c.imagePrice}` : '-'}</td>
      <td>${c.videoPrice > 0 ? `¥${c.videoPrice}` : '-'}</td>
      ${buildEvaluationCells(c, evaluationHeaders)}
    </tr>`
  ).join('');

  const html = `
<html xmlns:o="urn:schemas-microsoft-com:office:office"
      xmlns:x="urn:schemas-microsoft-com:office:excel"
      xmlns="http://www.w3.org/TR/REC-html40">
<head>
  <meta charset="UTF-8">
  <meta name="ProgId" content="Excel.Sheet">
  <!--[if gte mso 9]>
  <xml>
    <x:ExcelWorkbook>
      <x:ExcelWorksheets>
        <x:ExcelWorksheet>
          <x:Name>推荐名单</x:Name>
          <x:WorksheetOptions>
            <x:DisplayGridlines/>
          </x:WorksheetOptions>
        </x:ExcelWorksheet>
      </x:ExcelWorksheets>
    </x:ExcelWorkbook>
  </xml>
  <![endif]-->
  <style>
    table { border-collapse: collapse; font-family: "Microsoft YaHei", Arial, sans-serif; font-size: 11pt; }
    th { background: #1f8a70; color: #fff; font-weight: bold; text-align: center; }
    th, td { border: 1px solid #cfd7d1; padding: 6px 10px; white-space: nowrap; }
    .title-row td { font-size: 14pt; font-weight: bold; border: none; padding: 10px 0; }
    .meta-row td { font-size: 9pt; color: #666; border: none; padding: 2px 0; }
    td:nth-child(12), td:nth-child(13) { background: #eef7f3; font-weight: 700; }
  </style>
</head>
<body>
  <table>
    <tr class="title-row"><td colspan="${colCount}">${escapeHtml(title)} - 媒体资源推荐表</td></tr>
    <tr class="meta-row"><td colspan="${colCount}">生成时间：${now} | 候选总数：${candidates.length} | 强推荐：${candidates.filter(c => c.recommendStatus === '强推荐').length} | 备选：${candidates.filter(c => c.recommendStatus === '备选').length}</td></tr>
    <tr><td colspan="${colCount}"></td></tr>
    ${headerRow}
    ${rows}
  </table>
</body>
</html>`;

  return Buffer.from(html, 'utf-8');
}

function buildEvaluationHeaders(candidates: NormalizedCandidate[]): string[] {
  const headers: string[] = [];
  const has = (predicate: (candidate: NormalizedCandidate) => boolean) => candidates.some(predicate);
  if (has((candidate) => Boolean(candidate.evaluation?.cpmCpe))) headers.push('CPM/CPE判断', '图文CPM', '视频CPM', '图文CPE', '视频CPE');
  if (has((candidate) => Boolean(candidate.evaluation?.commercialStability))) headers.push('商单稳定性判断', '商单数', '近30天商单数', '商单阅读中位数', '商单互动中位数');
  if (has((candidate) => Boolean(candidate.evaluation?.recentPerformance))) headers.push('近期数据判断', '近样本互动中位数', '近期/前期互动比', '互动波动倍数');
  if (has((candidate) => Boolean(candidate.evaluation?.updateFrequency))) headers.push('更新频率判断', '近30天样本数');
  if (has((candidate) => Boolean(candidate.evaluation?.commentQuality))) headers.push('评论区判断', '评论中位数');
  if (has((candidate) => Boolean(candidate.evaluation?.publicSentiment))) headers.push('舆情风险词', '舆情判断');
  if (has((candidate) => Boolean(candidate.evaluation?.audienceGender))) headers.push('女性粉丝占比', '粉丝性别判断');
  if (has((candidate) => Boolean(candidate.evaluation?.notes?.length))) headers.push('按需评估摘要');
  return headers;
}

function buildEvaluationCells(candidate: NormalizedCandidate, headers: string[]): string {
  const evaluation = candidate.evaluation;
  return headers.map((header) => {
    const value = evaluationCellValue(candidate, header, evaluation);
    return `<td>${escapeHtml(value)}</td>`;
  }).join('');
}

function evaluationCellValue(
  _candidate: NormalizedCandidate,
  header: string,
  evaluation?: NormalizedCandidate['evaluation'],
): string {
  if (!evaluation) return '-';
  const cpm = evaluation.cpmCpe;
  const commercial = evaluation.commercialStability;
  const recent = evaluation.recentPerformance;
  const update = evaluation.updateFrequency;
  const comment = evaluation.commentQuality;
  const sentiment = evaluation.publicSentiment;
  const audience = evaluation.audienceGender;
  const values: Record<string, string> = {
    'CPM/CPE判断': cpm?.verdict || '-',
    '图文CPM': formatOptionalNumber(cpm?.pictureCpm),
    '视频CPM': formatOptionalNumber(cpm?.videoCpm),
    '图文CPE': formatOptionalNumber(cpm?.pictureCpe),
    '视频CPE': formatOptionalNumber(cpm?.videoCpe),
    '商单稳定性判断': commercial?.verdict || '-',
    '商单数': formatOptionalNumber(commercial?.businessNoteCount),
    '近30天商单数': formatOptionalNumber(commercial?.coopNoteNum30d),
    '商单阅读中位数': formatOptionalNumber(commercial?.readMidCoop30),
    '商单互动中位数': formatOptionalNumber(commercial?.interMidCoop30),
    '近期数据判断': recent?.verdict || '-',
    '近样本互动中位数': formatOptionalNumber(recent?.recentMedianInteraction || recent?.medianInteraction),
    '近期/前期互动比': formatOptionalNumber(recent?.trendRatio),
    '互动波动倍数': formatOptionalNumber(recent?.volatility),
    '更新频率判断': update?.verdict || '-',
    '近30天样本数': formatOptionalNumber(update?.recent30DayNotes),
    '评论区判断': comment?.verdict || '-',
    '评论中位数': formatOptionalNumber(comment?.commentMedian),
    '舆情风险词': sentiment?.riskKeywordHits?.join('、') || '-',
    '舆情判断': sentiment?.verdict || '-',
    '女性粉丝占比': audience?.femaleRatio !== undefined ? `${Math.round(audience.femaleRatio * 100)}%` : '-',
    '粉丝性别判断': audience?.verdict || '-',
    '按需评估摘要': evaluation.notes?.join('；') || '-',
  };
  return values[header] || '-';
}

function formatOptionalNumber(value?: number): string {
  return value === undefined || value === null || Number.isNaN(value) ? '-' : String(value);
}

function mapPlatformDisplay(platform: string): string {
  const map: Record<string, string> = {
    xiaohongshu: '小红书',
    douyin: '抖音',
    bilibili: 'B站',
    weibo: '微博',
    weixin: '微信/公众号',
  };
  return map[platform] || platform;
}

function formatFans(count: number): string {
  if (count === 0) return '-';
  if (count >= 10000) return `${(count / 10000).toFixed(1)}万`;
  return String(count);
}

function formatWan(count: number): string {
  if (!count) return '-';
  const value = count >= 10000 ? count / 10000 : count;
  return Number.isInteger(value) ? String(value) : value.toFixed(2).replace(/0+$/, '').replace(/\.$/, '');
}