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), status = text(input.status);
  if (status && !['pending','processed','approved','rejected'].includes(status)) fail(400, '处理状态无效');
  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(r.status,'pending') AS status, COALESCE(r.comment,'') AS response, r."handledAt", r."actorName", r."readAt",
      md5(COALESCE(g.title,'')||chr(10)||COALESCE(g.tcontent,'')||chr(10)||COALESCE(g.userid::text,'')) AS version,
      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 "SupportRequestResolution" r ON r.company=g.company AND r."requestId"=g."objectId"
    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 ($9='' OR status=$9) AND ($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,status]);
  return {canProcess:['ops-manager','ops-staff'].includes(roleOf(context)),items:result.items, total:result.total, page, pageSize,
    summary:{feedback:result.feedback,leave:result.leave,legacy:result.legacy},refreshedAt:new Date().toISOString()};
}

async function supportRequestProcess(context,input) {
  requireRole(context,['ops-manager','ops-staff']);
  const company=companyIdOf(context,input), id=text(input.targetId), status=text(input.status), note=text(input.comment);
  if(!company || !id) fail(400,'缺少帐套或申请编号');
  if(!['processed','approved','rejected'].includes(status)||!note||note.length>1000)fail(400,'请填写处理结果和 1 至 1000 字的处理意见');
  const actorName=text(context.current.get('realName')||context.current.get('nickname')||context.current.get('username')||'管理员');
  try {return (await Psql.one('SELECT xs_support_request_process($1,$2,$3,$4,$5,$6,$7) AS result',[company,id,context.current.id,actorName,status,note,text(input.version)])).result;}
  catch(error){if(error.code==='P0001')fail(409,error.message);throw error;}
}