xiaoshu-app/scripts/cloud/student-lesson-statistics.js

// Display-only statistics from completed lesson facts. Never transition an appointment
// or infer a balance from attendance; reservations and financial ledgers remain independent.
function studentLessonStatisticsSql() {
 return `WITH facts AS (
 SELECT l.*,COALESCE(NULLIF(NULLIF(l.yyds,'0'),''),'lesson:'||l."objectId") AS "classKey",
 COALESCE(r."actualStartAt",l."lessonAt",xiaoshu_safe_shanghai_timestamp(l.plpjsj),c."createdAt",l."sourceCreatedAt",l."createdAt") AS "actualAt",
 COALESCE(NULLIF(CASE WHEN r."durationMinutes">0 AND r."durationMinutes"::text NOT IN ('NaN','Infinity','-Infinity') THEN r."durationMinutes" END,0),
 CASE WHEN a."durationMinutes">0 AND a."durationMinutes"::text NOT IN ('NaN','Infinity','-Infinity') THEN a."durationMinutes" END,
 CASE WHEN to_jsonb(l)->>'durationMinutes' ~ '^\\d+(\\.\\d+)?$' THEN NULLIF((to_jsonb(l)->>'durationMinutes')::numeric,0) END,
 CASE l.kclx WHEN '1' THEN 30 WHEN '2' THEN 60 WHEN '3' THEN 60 END) AS minutes,
 COALESCE(NULLIF(l."courseCategoryKey",''),NULLIF(r."courseCategoryKey",''),a."courseCategoryKey",'') AS category
 FROM "LessonRecord" l
 LEFT JOIN LATERAL (SELECT * FROM "CommonModel" c WHERE c.company=l.company AND c."modelId"=59 AND c."itemId"=l.id ORDER BY c."objectId" LIMIT 1) c ON true
 LEFT JOIN LATERAL (SELECT r.* FROM "LessonCompletionReport" r WHERE r.company=l.company AND r."studentId"::text=l.xymz
 AND (r."lessonId"=l."objectId" OR r."appointmentGeneralId"::numeric=CASE WHEN l.yyds~'^\\d+$' THEN l.yyds::numeric END)
 ORDER BY r."submittedAt" DESC NULLS LAST,r."objectId" DESC LIMIT 1) r ON true
 LEFT JOIN LATERAL (SELECT a.* FROM "CommonModel" ac JOIN "CourseAppointment" a ON a.company=ac.company AND a.id=ac."itemId" AND a.szyh=l.xymz
 WHERE ac.company=l.company AND ac."modelId"=54 AND ac."generalId"=CASE WHEN l.yyds~'^\\d+$' THEN l.yyds::numeric END ORDER BY ac."objectId" LIMIT 1) a ON true
 WHERE l.company=$1 AND ($2='' OR l.xymz=$2) AND c.status IS DISTINCT FROM -2
 ), ranked AS (SELECT facts.*,row_number() OVER(PARTITION BY company,xymz,"classKey" ORDER BY "actualAt" DESC NULLS LAST,"objectId") rn FROM facts),
 lessons AS (SELECT * FROM ranked WHERE rn=1)
 SELECT xymz AS "studentId",COUNT(*)::int total,
 COUNT(*) FILTER(WHERE minutes=30 AND kclx IS DISTINCT FROM '3' AND category<>'trial')::int t30,
 COUNT(*) FILTER(WHERE minutes=60 AND kclx IS DISTINCT FROM '3' AND category<>'trial')::int t60,
 COUNT(*) FILTER(WHERE kclx='3' OR category='trial')::int tiyan,
 COUNT(*) FILTER(WHERE minutes IS NULL)::int "unknownDurationCount",
 CASE WHEN COUNT(*) FILTER(WHERE minutes IS NULL)>0 THEN NULL ELSE SUM(minutes) END AS "totalMinutes"
 FROM lessons GROUP BY xymz`;
}
function studentUnmatchedLessonsSql() {
 return `SELECT a.szyh AS "studentId",COUNT(*)::int total FROM "CourseAppointment" a
 LEFT JOIN LATERAL (SELECT * FROM "CommonModel" c WHERE c.company=a.company AND c."modelId"=54 AND c."itemId"=a.id ORDER BY c."objectId" LIMIT 1) c ON true
 WHERE a.company=$1 AND ($2='' OR a.szyh=$2) AND a.dszt ~ '^[0-9]+$' AND a.dszt::numeric>10 AND c.status IS DISTINCT FROM -2
 AND NOT EXISTS(SELECT 1 FROM "LessonRecord" l WHERE l.company=a.company AND l.xymz=a.szyh AND l.yyds=c."generalId"::text) GROUP BY a.szyh`;
}
async function studentLessonStatistics(companyId,studentId) {
 const [rows,missing]=await Promise.all([Psql.query(studentLessonStatisticsSql(),[companyId,String(studentId)]),Psql.query(studentUnmatchedLessonsSql(),[companyId,String(studentId)])]);
 const result=rows[0]||{total:0,t30:0,t60:0,tiyan:0,totalMinutes:0,unknownDurationCount:0};
 const unresolved=Number(missing[0]?.total)||0;
 return {...result,unknownDurationCount:Number(result.unknownDurationCount)+unresolved,totalMinutes:unresolved?null:result.totalMinutes};
}