xiaoshu-app/scripts/cloud/support-requests.js

// Both mobile roles submit category 22 to Guestbook. Keep legacy, untyped requests visible.
async function supportRequestsPage(context, input) {
  requireRole(context, ['ops-manager', 'ops-staff', 'ops-auditor']);
  const company = companyIdOf(context, input);
  if (!company) fail(400, '请先选择帐套');
  const kind = text(input.kind), role = text(input.role), search = text(input.search);
  if (kind && !['feedback', 'leave', 'legacy'].includes(kind)) fail(400, '申请类型无效');
  if (role && !['student', 'teacher', 'other'].includes(role)) fail(400, '提交身份无效');
  const from = text(input.dateFrom), to = text(input.dateTo);
  for (const day of [from, to]) {
    if (day && (!/^\d{4}-\d{2}-\d{2}$/.test(day) || !Number.isFinite(Date.parse(day)) || new Date(day).toISOString().slice(0,10) !== day)) fail(400, '日期无效');
  }
  if (from && to && from > to) fail(400, '开始日期不能晚于结束日期');
  const page = Math.max(1, Math.floor(number(input.page, 1)));
  const pageSize = Math.min(100, Math.max(1, Math.floor(number(input.pageSize, 20))));
  const result = await Psql.one(`WITH requests AS (
    SELECT g."objectId" AS id, g.userid::text AS "userId", COALESCE(g.title,'') AS title,
      COALESCE(g.tcontent,'') AS content, COALESCE(g.gdate,g."createdAt") AS "submittedAt",
      COALESCE(NULLIF(u."realName",''),NULLIF(u.nickname,''),NULLIF(u.username,''),'账号未匹配') AS "accountName",
      CASE WHEN g.title LIKE '【休假申请】%' THEN 'leave'
        WHEN g.title LIKE '【意见反馈】%' THEN 'feedback' ELSE 'legacy' END AS kind,
      CASE WHEN COALESCE(to_jsonb(u)->>'identityType','')='coach' OR COALESCE(u."legacyGroupId"::text,u."legacyUserData"->>'GroupID')='3' THEN 'teacher'
        WHEN COALESCE(to_jsonb(u)->>'identityType','')='member' OR COALESCE(u."legacyGroupId"::text,u."legacyUserData"->>'GroupID')='1' THEN 'student'
        ELSE 'other' END AS role
    FROM "Guestbook" g
    LEFT JOIN LATERAL (SELECT u.* FROM "_User" u WHERE u.company=g.company
      AND COALESCE(u."legacyUserId"::text,u."legacyUserData"->>'UserID')=g.userid::text
      ORDER BY u."createdAt" DESC,u."objectId" LIMIT 1) u ON TRUE
    WHERE g.company=$1 AND COALESCE(g.parentid,0)=0 AND COALESCE(g.status,0)<>-2
      AND (g.cateid=22 OR g."sourceKey" LIKE 'cloud:guestbook_add:%')
  ), filtered AS (
    SELECT * FROM requests WHERE ($2='' OR kind=$2) AND ($3='' OR role=$3)
      AND ($4='' OR strpos(lower(title||' '||content||' '||"accountName"||' '||COALESCE("userId",'')),lower($4))>0)
      AND ($5='' OR "submittedAt">=NULLIF($5,'')::date::timestamp AT TIME ZONE 'Asia/Shanghai')
      AND ($6='' OR "submittedAt"<(NULLIF($6,'')::date+1)::timestamp AT TIME ZONE 'Asia/Shanghai')
  ), paged AS (SELECT * FROM filtered ORDER BY "submittedAt" DESC NULLS LAST,id DESC LIMIT $7 OFFSET $8)
  SELECT COUNT(*)::int AS total,
    COUNT(*) FILTER(WHERE kind='feedback')::int AS feedback,
    COUNT(*) FILTER(WHERE kind='leave')::int AS leave,
    COUNT(*) FILTER(WHERE kind='legacy')::int AS legacy,
    COALESCE((SELECT jsonb_agg(p ORDER BY p."submittedAt" DESC NULLS LAST,p.id DESC) FROM paged p),'[]'::jsonb) AS items
  FROM filtered`, [company,kind,role,search.slice(0,200),from,to,pageSize,(page-1)*pageSize]);
  return {items:result.items, total:result.total, page, pageSize,
    summary:{feedback:result.feedback,leave:result.leave,legacy:result.legacy},refreshedAt:new Date().toISOString()};
}