xiaoshu-app/scripts/tests/learning-report-query.test.mjs

import { after, before, test } from 'node:test';
import assert from 'node:assert/strict';
import { readFileSync } from 'node:fs';
import vm from 'node:vm';
import { PGlite } from '@electric-sql/pglite';

const db = new PGlite();
const source = readFileSync(new URL('../deploy-admin-functions.mjs', import.meta.url), 'utf8');
const gateway = {};
vm.createContext(gateway);
vm.runInContext(source.match(/const operationsGatewayCode = String.raw`([\s\S]*?)\n`;/)[1], gateway);
gateway.companyIdOf = () => 'co';
before(async () => {
  await db.exec(`
    CREATE TABLE "DailyStudyRecord"("objectId" text,"company" text,"id" int,"userId" int,"pl" text,"dsid" text,"dqrq" text,"xxqs" text,"createdAt" timestamptz);
    CREATE TABLE "CommonModel"("objectId" text,"company" text,"modelId" int,"itemId" int,"generalId" int,"createTime" timestamptz,"createdAt" timestamptz);
    CREATE TABLE "_User"("objectId" text,"company" text,"legacyUserId" int,"legacyUserData" jsonb,"realName" text,"nickname" text,"username" text,"mobile" text,"identityType" text,"isDeleted" boolean);
    CREATE TABLE "CourseAppointment"("objectId" text,"company" text,"id" int,"kcid" text,"dszt" text,"courseCategoryKey" text,"courseCategoryName" text,szyh text);
    CREATE TABLE "Node"("company" text,"nodeId" int,"nodeName" text);
    INSERT INTO "DailyStudyRecord" VALUES
      ('compact','co',1,20,'30','','20261004','[]','2026-10-04T00:00:00Z'),
      ('dashed','co',2,20,'30','','2026-10-04','[]','2026-10-04T00:00:00Z'),
      ('slash','co',3,20,'30','','2026/10/03','[]','2026-10-03T00:00:00Z'),
      ('fallback','co',4,20,'30','',NULL,'[]','2026-09-30T16:01:00Z'),
      ('history','co',5,20,'30','','20260930','[]','2026-09-30T00:00:00Z'),
      ('foreign','other',6,20,'30','','20261004','[]','2026-10-04T00:00:00Z');
  `);
});
after(() => db.close());
async function rows(filters) {
  const query = gateway.learningReportQueryParts({}, { page: 1, pageSize: 20, filters });
  return (await db.query('SELECT d."objectId"' + query.from + query.where + ' ORDER BY ' + query.studyDateSql + ' DESC,d."objectId"', query.values)).rows.map(row => row.objectId);
}
test('月份筛选兼容三种日期格式，并按北京时间补齐缺失学习日期', async () => {
  assert.deepEqual(await rows({ month: '2026-10' }), ['compact','dashed','slash','fallback']);
});
test('全部月份包含历史记录，保持帐套隔离', async () => {
  assert.deepEqual(await rows({ month: '' }), ['compact','dashed','slash','fallback','history']);
});
test('日期范围包含首尾当天，兼容不同保存格式', async () => {
  assert.deepEqual(await rows({ dateFrom: '2026-10-03', dateTo: '2026-10-04' }), ['compact','dashed','slash']);
  assert.deepEqual(await rows({ month: '2026-09' }), ['history']);
});

test('报表展示与筛选使用同一个学习日期，斜线日期与 UTC 时间不会少一天', () => {
  for(const raw of ['20261004','2026-10-04','2026/10/04'])assert.equal(gateway.learningReportProjection({dqrq:raw}).studyDate,'2026-10-04');
  assert.equal(gateway.learningReportProjection({recordCreatedAt:'2026-09-30T16:01:00Z'}).studyDate,'2026-10-01');
});

test('门店报表按当前会员归属过滤后再计数，无法通过过滤器读取其他门店',async()=>{
 await db.exec(`INSERT INTO "_User" ("objectId",company,"legacyUserId","legacyUserData","identityType") VALUES ('own','co',20,'{"ParentUserID":57}','member'),('other','co',21,'{"ParentUserID":73}','member');
 INSERT INTO "DailyStudyRecord" ("objectId",company,"userId",dqrq) VALUES ('other-store','co',21,'20261004');`);
 const query=gateway.learningReportQueryParts({storeId:57},{page:1,pageSize:20,filters:{month:'2026-10',storeId:73}});
 const count=(await db.query('SELECT COUNT(*)::int total'+query.from+query.where,query.values)).rows[0].total;
 assert.equal(count,4);
 const foreign=gateway.learningReportQueryParts({storeId:73},{page:1,pageSize:20,filters:{month:'2026-10'}});
 assert.deepEqual((await db.query('SELECT d."objectId"'+foreign.from+foreign.where,foreign.values)).rows.map(r=>r.objectId),['other-store']);
});

test('无教材的语法写作课按预约分类显示并支持课程名称搜索',async()=>{
 await db.exec(`INSERT INTO "CourseAppointment" VALUES
   ('writing','co',201,'0','30','high_writing','高中语法写作课','59'),
   ('word','co',202,'13','30','word','单词课','59'),
   ('legacy-writing','co',203,'398','30','high_writing','高中语法写作课','59'),
   ('legacy-unclassified','co',204,'398','30',NULL,NULL,'59'),
   ('unrelated-appointment','co',205,'398','30',NULL,NULL,'60');
  INSERT INTO "CommonModel" VALUES
   ('writing-main','co',54,201,201,NULL,NULL),
   ('word-main','co',54,202,202,NULL,NULL),
   ('legacy-writing-main','co',54,203,203,NULL,NULL),
   ('legacy-unclassified-main','co',54,204,204,NULL,NULL),
   ('unrelated-main','co',54,205,205,NULL,NULL);
  INSERT INTO "Node" VALUES ('co',13,'人教版词表'),('co',398,'阅读写作');
  INSERT INTO "DailyStudyRecord" VALUES
   ('writing-report','co',21,59,'88','201','20261111','[]',NULL),
   ('word-report','co',22,59,'88','202','20261111','[]',NULL),
   ('legacy-writing-report','co',23,59,'88','203','20261111','[]',NULL),
   ('legacy-unclassified-report','co',24,59,'88','204','20261111','[]',NULL),
   ('unrelated-report','co',25,59,'88','205','20261111','[]',NULL);`);
 const q=gateway.learningReportQueryParts({}, {filters:{dateFrom:'2026-11-11',dateTo:'2026-11-11',studentId:59}});
 const sql='SELECT d."objectId",'+gateway.learningReportCourseTitleSql()+' AS title'+q.from+q.where+' ORDER BY d.id';
 assert.deepEqual((await db.query(sql,q.values)).rows.map(row=>row.title),['高中语法写作课','人教版词表','阅读写作','阅读写作','阅读写作']);
 assert.deepEqual(await rows({courseSearch:'高中语法写作课'}),['writing-report']);
 assert.deepEqual(await rows({courseSearch:'人教版'}),['word-report']);
});

test('写作课不进入单词明细缺失或单词报表筛选', async () => {
  const dates = { dateFrom: '2026-11-11', dateTo: '2026-11-11' };
  assert.deepEqual(await rows({ ...dates, status: 'writing' }), [
    'legacy-unclassified-report', 'legacy-writing-report', 'writing-report',
  ]);
  assert.deepEqual(await rows({ ...dates, status: 'missing' }), ['unrelated-report', 'word-report']);
  assert.deepEqual(await rows({ ...dates, status: 'available' }), []);
});

test('写作报表的词汇掌握率不适用，普通单词报表仍计算掌握率', () => {
  const wordData = { xxqs: '[{"Title":"apple","check":1}]', appointmentObjectId: 'word', appointmentStatus: '30' };
  const writing = gateway.learningReportProjection({ ...wordData, isWriting: true });
  assert.equal(writing.status, 'writing');
  assert.equal(writing.masteryRate, null);
  assert.equal(writing.canExport, false);
  assert.equal(writing.learnedCount, 0);
  const word = gateway.learningReportProjection(wordData);
  assert.equal(word.status, 'available');
  assert.equal(word.masteryRate, 100);
  assert.equal(word.canExport, true);
});