671 lines
23 KiB
JavaScript
671 lines
23 KiB
JavaScript
#!/usr/bin/env node
|
||
'use strict';
|
||
|
||
/**
|
||
* Extract attendance + payment sheets from class xlsx files into dashboard import JSON.
|
||
* Amounts in spreadsheets are Rials; output uses Toman (one zero dropped).
|
||
*
|
||
* Usage:
|
||
* node scripts/convert-attendance-payments.js [raw-root] [output.json] [--exclude=pattern]
|
||
*/
|
||
|
||
const fs = require('fs');
|
||
const path = require('path');
|
||
const XLSX = require('xlsx');
|
||
const { projectSessionDates } = require('../components/dataImport/importHelpers');
|
||
const { rialsToToman } = require('../utils/paymentAmount');
|
||
|
||
const MONTHS = {
|
||
فروردین: 1,
|
||
اردیبهشت: 2,
|
||
خرداد: 3,
|
||
تیر: 4,
|
||
مرداد: 5,
|
||
شهریور: 6,
|
||
مهر: 7,
|
||
آبان: 8,
|
||
آذر: 9,
|
||
دی: 10,
|
||
بهمن: 11,
|
||
اسفند: 12
|
||
};
|
||
|
||
const ATTENDANCE_HINTS = ['حضور و غیاب', 'حضور غیاب'];
|
||
const PAYMENT_HINTS = ['شهریه'];
|
||
const INFO_HINTS = ['اطلاعات کلی', 'مشخصات کلی', 'اطلاعات'];
|
||
|
||
const DEFAULT_RAW = path.join(
|
||
__dirname,
|
||
'..',
|
||
'..',
|
||
'raw-data',
|
||
'برنامه آموزشی 1405-20260814T213206Z-1-001',
|
||
'برنامه آموزشی 1405'
|
||
);
|
||
const DEFAULT_OUT = path.join(__dirname, '..', '..', 'raw-data', '1405-attendance-payments-import.json');
|
||
const REF_DATA = path.join(__dirname, '..', '..', 'raw-data', 'data');
|
||
|
||
const toEnglishDigits = (value) =>
|
||
String(value ?? '')
|
||
.replace(/[۰-۹]/g, (d) => '۰۱۲۳۴۵۶۷۸۹'.indexOf(d))
|
||
.replace(/[٠-٩]/g, (d) => '٠١٢٣٤٥٦٧٨٩'.indexOf(d));
|
||
|
||
const cleanText = (value) => {
|
||
if (value == null) return '';
|
||
return String(value).replace(/\s+/g, ' ').trim();
|
||
};
|
||
|
||
const normalizeHeader = (value) => cleanText(value).replace(/\n/g, ' ');
|
||
|
||
const jalaliToGregorian = (jy, jm, jd) => {
|
||
const gy = jy <= 979 ? 621 : 1600;
|
||
jy -= jy <= 979 ? 0 : 979;
|
||
let days =
|
||
365 * jy +
|
||
Math.floor(jy / 33) * 8 +
|
||
Math.floor(((jy % 33) + 3) / 4) +
|
||
78 +
|
||
jd +
|
||
(jm < 7 ? (jm - 1) * 31 : (jm - 7) * 30 + 186);
|
||
let gyOut = gy + 400 * Math.floor(days / 146097);
|
||
days %= 146097;
|
||
if (days > 36524) {
|
||
gyOut += 100 * Math.floor(--days / 36524);
|
||
days %= 36524;
|
||
if (days >= 365) days += 1;
|
||
}
|
||
gyOut += 4 * Math.floor(days / 1461);
|
||
days %= 1461;
|
||
if (days > 365) {
|
||
gyOut += Math.floor((days - 1) / 365);
|
||
days = (days - 1) % 365;
|
||
}
|
||
let gd = days + 1;
|
||
const sal_a = [
|
||
0, 31,
|
||
(gyOut % 4 === 0 && gyOut % 100 !== 0) || gyOut % 400 === 0 ? 29 : 28,
|
||
31, 30, 31, 30, 31, 31, 30, 31, 30, 31
|
||
];
|
||
let gm = 0;
|
||
for (gm = 1; gm <= 12 && gd > sal_a[gm]; gm += 1) gd -= sal_a[gm];
|
||
return `${gyOut}-${String(gm).padStart(2, '0')}-${String(gd).padStart(2, '0')}`;
|
||
};
|
||
|
||
const parseJalaliDate = (raw) => {
|
||
if (!raw) return null;
|
||
const text = toEnglishDigits(raw).replace(/[./\-]/g, '/').trim();
|
||
const match = text.match(/^(\d{3,4})\/(\d{1,2})\/(\d{1,2})$/);
|
||
if (!match) return null;
|
||
const jy = Number(match[1]);
|
||
const jm = Number(match[2]);
|
||
const jd = Number(match[3]);
|
||
if (!jy || !jm || !jd || jm > 12 || jd > 31) return null;
|
||
try {
|
||
return jalaliToGregorian(jy, jm, jd);
|
||
} catch {
|
||
return null;
|
||
}
|
||
};
|
||
|
||
const formatJalaliSlash = (raw) => {
|
||
if (!raw) return null;
|
||
const text = toEnglishDigits(raw).replace(/[./\-]/g, '/').trim();
|
||
const match = text.match(/^(\d{3,4})\/(\d{1,2})\/(\d{1,2})$/);
|
||
if (!match) return null;
|
||
return `${match[1]}/${Number(match[2])}/${Number(match[3])}`;
|
||
};
|
||
|
||
const normalizePhone = (raw) => {
|
||
if (raw == null || raw === '') return '';
|
||
let digits = toEnglishDigits(raw).replace(/\D/g, '');
|
||
if (digits.startsWith('98') && digits.length === 12) digits = `0${digits.slice(2)}`;
|
||
if (digits.length === 10 && digits.startsWith('9')) digits = `0${digits}`;
|
||
return digits.length === 11 && digits.startsWith('09') ? digits : digits || '';
|
||
};
|
||
|
||
const normalizeNationalId = (raw) => {
|
||
if (raw == null || raw === '') return '';
|
||
return toEnglishDigits(raw).replace(/\D/g, '');
|
||
};
|
||
|
||
const normalizePersonName = (first, last) => {
|
||
const name = cleanText(`${first || ''} ${last || ''}`)
|
||
.replace(/ي/g, 'ی')
|
||
.replace(/ك/g, 'ک')
|
||
.replace(/[\u200c\u200d]/g, ' ')
|
||
.replace(/\s+/g, ' ')
|
||
.trim();
|
||
return name;
|
||
};
|
||
|
||
const parseMoney = (raw) => {
|
||
if (raw == null || raw === '') return 0;
|
||
const text = cleanText(raw);
|
||
if (!text) return 0;
|
||
if (/^[\d,.\s]+$/.test(toEnglishDigits(text).replace(/,/g, ''))) {
|
||
const digits = toEnglishDigits(text).replace(/[^\d]/g, '');
|
||
return digits ? Number(digits) : 0;
|
||
}
|
||
if (/e\+/i.test(text)) {
|
||
const n = Number(toEnglishDigits(text));
|
||
return Number.isFinite(n) ? Math.round(n) : 0;
|
||
}
|
||
return 0;
|
||
};
|
||
|
||
const mapAttendanceStatus = (raw) => {
|
||
const value = cleanText(raw);
|
||
if (!value) return null;
|
||
if (value === '*' || value === 'ح' || value === 'حاضر') return 'present';
|
||
if (value === 'غ' || value === 'غایب') return 'absent';
|
||
if (value === 'ت' || value === 'تأخیر' || value === 'تاخیر') return 'late';
|
||
if (value === 'م' || value === 'موجه') return 'excused';
|
||
return null;
|
||
};
|
||
|
||
const pickSheet = (wb, hints) => {
|
||
for (const hint of hints) {
|
||
const found = wb.SheetNames.find((n) => {
|
||
const norm = normalizeHeader(n);
|
||
return norm === hint || norm.includes(hint);
|
||
});
|
||
if (found) return found;
|
||
}
|
||
return null;
|
||
};
|
||
|
||
const sheetRows = (sheet) =>
|
||
XLSX.utils.sheet_to_json(sheet, { header: 1, defval: null, raw: false });
|
||
|
||
const parseFolderMeta = (folderName) => {
|
||
const cleaned = cleanText(folderName).replace(/^\d+\s*[-–—.]?\s*/, '');
|
||
const monthNames = Object.keys(MONTHS).join('|');
|
||
const monthMatch = cleaned.match(new RegExp(`(${monthNames})\\s+(\\d{4})$`));
|
||
let className = cleaned;
|
||
let courseTitle = cleaned;
|
||
let startDate = null;
|
||
|
||
if (monthMatch) {
|
||
const monthName = monthMatch[1];
|
||
const jalaliYear = Number(monthMatch[2]);
|
||
const jalaliMonth = MONTHS[monthName];
|
||
className = cleaned;
|
||
courseTitle = cleanText(cleaned.slice(0, monthMatch.index));
|
||
startDate = jalaliToGregorian(jalaliYear, jalaliMonth, 1);
|
||
}
|
||
|
||
const isPrivate = /خصوصی/.test(courseTitle) || /خصوصی/.test(className);
|
||
if (isPrivate) courseTitle = cleanText(courseTitle.replace(/خصوصی/g, ''));
|
||
|
||
return {
|
||
folderName,
|
||
courseTitle: courseTitle || className,
|
||
className,
|
||
type: isPrivate ? 'Private' : 'General',
|
||
startDate
|
||
};
|
||
};
|
||
|
||
const findClassXlsx = (dirPath) => {
|
||
const files = fs.readdirSync(dirPath).filter((f) => f.endsWith('.xlsx') && !f.startsWith('~$'));
|
||
if (!files.length) return null;
|
||
return path.join(dirPath, files.find((f) => !f.includes('لیست شرکت کنندگان')) || files[0]);
|
||
};
|
||
|
||
const findHeaderRow = (rows, predicate, max = 8) => {
|
||
for (let i = 0; i < Math.min(rows.length, max); i += 1) {
|
||
if (predicate(rows[i] || [])) return i;
|
||
}
|
||
return -1;
|
||
};
|
||
|
||
const loadReferenceData = () => {
|
||
const classesPath = path.join(REF_DATA, 'test.classes.json');
|
||
const coursesPath = path.join(REF_DATA, 'test.courses.json');
|
||
const usersPath = path.join(REF_DATA, 'test.users.json');
|
||
|
||
const classes = fs.existsSync(classesPath) ? JSON.parse(fs.readFileSync(classesPath, 'utf8')) : [];
|
||
const courses = fs.existsSync(coursesPath) ? JSON.parse(fs.readFileSync(coursesPath, 'utf8')) : [];
|
||
const users = fs.existsSync(usersPath) ? JSON.parse(fs.readFileSync(usersPath, 'utf8')) : [];
|
||
|
||
const courseById = new Map(courses.map((c) => [String(c._id.$oid || c._id), c.title]));
|
||
const classByName = new Map(classes.map((c) => [c.name, { ...c, courseTitle: courseById.get(String(c.course.$oid || c.course)) }]));
|
||
const usersByPhone = new Map();
|
||
const usersByNationalId = new Map();
|
||
for (const user of users) {
|
||
if (user.phoneNumber) usersByPhone.set(normalizePhone(user.phoneNumber), user);
|
||
if (user.nationalIdCode) usersByNationalId.set(normalizeNationalId(user.nationalIdCode), user);
|
||
}
|
||
|
||
return { classByName, usersByPhone, usersByNationalId };
|
||
};
|
||
|
||
const resolveClassMeta = (meta, refs) => {
|
||
const candidates = [
|
||
meta.className,
|
||
meta.folderName.replace(/^\d+\s*[-–—.]?\s*/, ''),
|
||
meta.folderName
|
||
];
|
||
for (const name of candidates) {
|
||
const hit = refs.classByName.get(name);
|
||
if (hit) {
|
||
return {
|
||
...meta,
|
||
className: hit.name,
|
||
courseTitle: hit.courseTitle || meta.courseTitle,
|
||
dbStartDate: hit.startDate?.$date?.slice(0, 10) || null,
|
||
tuitionFee: hit.tuitionFee || 0
|
||
};
|
||
}
|
||
}
|
||
return meta;
|
||
};
|
||
|
||
const extractInfoLookup = (sheet) => {
|
||
const rows = sheetRows(sheet);
|
||
const headerIdx = findHeaderRow(rows, (row) => {
|
||
const text = row.map(normalizeHeader).join('|');
|
||
return text.includes('نام') && (text.includes('تلفن') || text.includes('کد ملی'));
|
||
});
|
||
if (headerIdx < 0) return new Map();
|
||
|
||
const header = rows[headerIdx].map(normalizeHeader);
|
||
const idx = {
|
||
firstName: header.findIndex((h) => h === 'نام'),
|
||
lastName: header.findIndex((h) => h.includes('نام خانوادگی')),
|
||
fullName: header.findIndex((h) => h.includes('نام و نام خانوادگی')),
|
||
phone: header.findIndex((h) => h.includes('تلفن') || h.includes('شماره تماس')),
|
||
nationalId: header.findIndex((h) => h.includes('کد ملی'))
|
||
};
|
||
|
||
const lookup = new Map();
|
||
for (let i = headerIdx + 1; i < rows.length; i += 1) {
|
||
const row = rows[i];
|
||
if (!row) continue;
|
||
const name = idx.fullName >= 0
|
||
? cleanText(row[idx.fullName])
|
||
: normalizePersonName(row[idx.firstName], row[idx.lastName]);
|
||
if (!name) continue;
|
||
const phone = idx.phone >= 0 ? normalizePhone(row[idx.phone]) : '';
|
||
const nationalIdCode = idx.nationalId >= 0 ? normalizeNationalId(row[idx.nationalId]) : '';
|
||
lookup.set(name, { name, phoneNumber: phone || undefined, nationalIdCode: nationalIdCode || undefined });
|
||
}
|
||
return lookup;
|
||
};
|
||
|
||
const extractAttendance = (sheet) => {
|
||
const rows = sheetRows(sheet);
|
||
const headerRowIdx = findHeaderRow(rows, (row) => {
|
||
const text = row.map(normalizeHeader).join('|');
|
||
return text.includes('نام') && text.includes('نام خانوادگی');
|
||
});
|
||
if (headerRowIdx < 0) return { sessions: [], sessionCount: 0, weekdays: [], startDate: null };
|
||
|
||
const headerRow = rows[headerRowIdx];
|
||
const numberRow = headerRowIdx > 0 ? rows[headerRowIdx - 1] : null;
|
||
const firstNameIdx = headerRow.findIndex((h) => normalizeHeader(h) === 'نام');
|
||
const lastNameIdx = headerRow.findIndex((h) => normalizeHeader(h).includes('نام خانوادگی'));
|
||
const identityEndCol = Math.max(firstNameIdx, lastNameIdx) + 1;
|
||
|
||
const sessionCols = [];
|
||
for (let col = identityEndCol; col < headerRow.length; col += 1) {
|
||
const jalaliRaw = headerRow[col] || (numberRow ? numberRow[col] : null);
|
||
const isoDate = parseJalaliDate(jalaliRaw);
|
||
if (!isoDate) continue;
|
||
sessionCols.push({
|
||
col,
|
||
sessionNo: sessionCols.length + 1,
|
||
jalaliRaw,
|
||
isoDate
|
||
});
|
||
}
|
||
|
||
const totalCount = sessionCols.length;
|
||
if (!totalCount) {
|
||
return { sessions: [], sessionCount: 0, weekdays: [], startDate: null };
|
||
}
|
||
|
||
const knownDates = sessionCols.map((s) => s.isoDate);
|
||
const projectedDates = projectSessionDates(knownDates, totalCount);
|
||
const sessions = sessionCols.map((colMeta, index) => ({
|
||
topic: `جلسه ${colMeta.sessionNo}`,
|
||
day: projectedDates[index] || colMeta.isoDate,
|
||
dayJalali: colMeta.jalaliRaw ? formatJalaliSlash(colMeta.jalaliRaw) : undefined,
|
||
startTime: '19:00',
|
||
endTime: '20:30',
|
||
status: 'scheduled',
|
||
attendance: []
|
||
}));
|
||
|
||
for (let i = headerRowIdx + 1; i < rows.length; i += 1) {
|
||
const row = rows[i];
|
||
if (!row) continue;
|
||
const name = normalizePersonName(row[firstNameIdx], row[lastNameIdx]);
|
||
if (!name) continue;
|
||
|
||
for (const colMeta of sessionCols) {
|
||
const status = mapAttendanceStatus(row[colMeta.col]);
|
||
if (!status) continue;
|
||
const session = sessions.find((s) => s.topic === `جلسه ${colMeta.sessionNo}`);
|
||
if (!session) continue;
|
||
session.attendance.push({ name, status });
|
||
}
|
||
}
|
||
|
||
const today = new Date().toISOString().slice(0, 10);
|
||
for (const session of sessions) {
|
||
const hasAttendance = session.attendance.length > 0;
|
||
if (hasAttendance || (session.day && session.day <= today)) {
|
||
session.status = 'held';
|
||
}
|
||
}
|
||
|
||
const weekdays = [...new Set(
|
||
sessions.filter((s) => s.day).map((s) => new Date(`${s.day}T12:00:00.000Z`).getUTCDay())
|
||
)].sort();
|
||
|
||
return {
|
||
sessions,
|
||
sessionCount: totalCount,
|
||
weekdays,
|
||
startDate: sessions.find((s) => s.day)?.day || null
|
||
};
|
||
};
|
||
|
||
const findInstallmentGroups = (headerRow) => {
|
||
const groups = [];
|
||
for (let i = 0; i < headerRow.length; i += 1) {
|
||
const h = normalizeHeader(headerRow[i]);
|
||
const match = h.match(/^قسط (اول|دوم|سوم|چهارم|پنجم)$/);
|
||
if (!match) continue;
|
||
groups.push({ label: match[1], amountCol: i, dateCol: i + 1, receiptCol: i + 2 });
|
||
}
|
||
return groups;
|
||
};
|
||
|
||
const extractPayments = (sheet, infoLookup, refs) => {
|
||
const rows = sheetRows(sheet);
|
||
const headerIdx = findHeaderRow(rows, (row) => {
|
||
const text = row.map(normalizeHeader).join('|');
|
||
return text.includes('نام') && text.includes('هزینه کل دوره');
|
||
});
|
||
if (headerIdx < 0) return [];
|
||
|
||
const headerRow = rows[headerIdx].map(normalizeHeader);
|
||
const firstNameIdx = headerRow.findIndex((h) => h === 'نام');
|
||
const lastNameIdx = headerRow.findIndex((h) => h.includes('نام خانوادگی'));
|
||
const phoneIdx = headerRow.findIndex((h) => h.includes('شماره تماس') || h.includes('تلفن'));
|
||
const discountIdx = headerRow.findIndex((h) => h.includes('تخفیف'));
|
||
const totalIdx = headerRow.findIndex((h) => h === 'هزینه کل دوره');
|
||
const installmentTotalIdx = headerRow.findIndex((h) => h.includes('هزینه کل دوره') && h !== 'هزینه کل دوره');
|
||
const installmentGroups = findInstallmentGroups(headerRow);
|
||
|
||
let remainderCol = -1;
|
||
for (let i = headerRow.length - 1; i >= 0; i -= 1) {
|
||
const h = headerRow[i];
|
||
if (!h) continue;
|
||
if (h.includes('پرتال') || h.includes('هزینه های')) continue;
|
||
remainderCol = i;
|
||
break;
|
||
}
|
||
|
||
const payments = [];
|
||
for (let i = headerIdx + 1; i < rows.length; i += 1) {
|
||
const row = rows[i];
|
||
if (!row) continue;
|
||
const name = normalizePersonName(row[firstNameIdx], row[lastNameIdx]);
|
||
if (!name) continue;
|
||
|
||
const phoneNumber = phoneIdx >= 0 ? normalizePhone(row[phoneIdx]) : '';
|
||
const info = infoLookup.get(name) || {};
|
||
const userByPhone = phoneNumber ? refs.usersByPhone.get(phoneNumber) : null;
|
||
const nationalIdCode =
|
||
info.nationalIdCode ||
|
||
(userByPhone ? normalizeNationalId(userByPhone.nationalIdCode) : '');
|
||
|
||
const discountRials = discountIdx >= 0 ? parseMoney(row[discountIdx]) : 0;
|
||
let amountRials = totalIdx >= 0 ? parseMoney(row[totalIdx]) : 0;
|
||
const installmentTotalRials = installmentTotalIdx >= 0 ? parseMoney(row[installmentTotalIdx]) : 0;
|
||
if (!amountRials && installmentTotalRials) {
|
||
amountRials = installmentTotalRials + discountRials;
|
||
}
|
||
if (!amountRials && installmentTotalRials) amountRials = installmentTotalRials;
|
||
if (!amountRials) continue;
|
||
|
||
const amount = rialsToToman(amountRials);
|
||
const discount = rialsToToman(discountRials);
|
||
|
||
const transactions = [];
|
||
for (const group of installmentGroups) {
|
||
const amountPart = parseMoney(row[group.amountCol]);
|
||
if (!amountPart) continue;
|
||
const trxAmount = rialsToToman(amountPart);
|
||
const jalaliRaw = row[group.dateCol];
|
||
const isoDate = parseJalaliDate(jalaliRaw);
|
||
const receiptRaw = row[group.receiptCol];
|
||
let receiptNumber;
|
||
if (receiptRaw != null && receiptRaw !== '') {
|
||
const receiptText = cleanText(receiptRaw);
|
||
if (/^\d+(?:\.\d+)?(?:e\+?\d+)?$/i.test(toEnglishDigits(receiptText))) {
|
||
receiptNumber = toEnglishDigits(receiptText).replace(/\.\d+$/, '').replace(/e\+?\d+/i, '');
|
||
if (/e/i.test(receiptText)) {
|
||
receiptNumber = String(Math.round(Number(toEnglishDigits(receiptText))));
|
||
}
|
||
} else if (/^\d+$/.test(toEnglishDigits(receiptText))) {
|
||
receiptNumber = toEnglishDigits(receiptText);
|
||
}
|
||
}
|
||
|
||
const trx = {
|
||
amount: trxAmount,
|
||
method: 'card',
|
||
status: 'paid'
|
||
};
|
||
if (isoDate) {
|
||
trx.date = isoDate;
|
||
trx.dueDate = isoDate;
|
||
}
|
||
if (jalaliRaw) trx.dateJalali = formatJalaliSlash(jalaliRaw);
|
||
if (receiptNumber) trx.receiptNumber = receiptNumber;
|
||
transactions.push(trx);
|
||
}
|
||
|
||
const remainderRials = remainderCol >= 0 ? parseMoney(row[remainderCol]) : 0;
|
||
const remainder = rialsToToman(remainderRials);
|
||
const paidTotal = transactions.reduce((sum, trx) => sum + trx.amount, 0);
|
||
const payable = Math.max(0, amount - discount);
|
||
const inferredRemainder = Math.max(0, payable - paidTotal);
|
||
|
||
if (remainder > 0) {
|
||
transactions.push({
|
||
amount: remainder,
|
||
status: 'pending',
|
||
notes: 'مانده شهریه ثبتشده در اکسل'
|
||
});
|
||
} else if (inferredRemainder > 0 && paidTotal > 0) {
|
||
transactions.push({
|
||
amount: inferredRemainder,
|
||
status: 'pending',
|
||
notes: 'مانده محاسبهشده از اکسل'
|
||
});
|
||
}
|
||
|
||
const payment = {
|
||
name,
|
||
phoneNumber: phoneNumber || info.phoneNumber || undefined,
|
||
nationalIdCode: nationalIdCode || undefined,
|
||
amount,
|
||
discount,
|
||
transactions
|
||
};
|
||
|
||
Object.keys(payment).forEach((k) => {
|
||
if (payment[k] === undefined) delete payment[k];
|
||
});
|
||
payments.push(payment);
|
||
}
|
||
|
||
return payments;
|
||
};
|
||
|
||
const enrichIdentity = (sessions, payments, infoLookup, refs) => {
|
||
const enrichPerson = (person) => {
|
||
const info = infoLookup.get(person.name) || {};
|
||
if (!person.phoneNumber && info.phoneNumber) person.phoneNumber = info.phoneNumber;
|
||
if (!person.nationalIdCode && info.nationalIdCode) person.nationalIdCode = info.nationalIdCode;
|
||
if (person.phoneNumber) {
|
||
const user = refs.usersByPhone.get(normalizePhone(person.phoneNumber));
|
||
if (user && !person.nationalIdCode) person.nationalIdCode = normalizeNationalId(user.nationalIdCode);
|
||
}
|
||
if (person.nationalIdCode) {
|
||
const user = refs.usersByNationalId.get(normalizeNationalId(person.nationalIdCode));
|
||
if (user && !person.phoneNumber) person.phoneNumber = normalizePhone(user.phoneNumber);
|
||
}
|
||
};
|
||
|
||
for (const session of sessions) {
|
||
for (const record of session.attendance) enrichPerson(record);
|
||
}
|
||
for (const payment of payments) enrichPerson(payment);
|
||
};
|
||
|
||
const convertClassFolder = (dirPath, folderName, refs) => {
|
||
const meta = resolveClassMeta(parseFolderMeta(folderName), refs);
|
||
const xlsxPath = findClassXlsx(dirPath);
|
||
if (!xlsxPath) {
|
||
return { ...meta, sourceFile: null, sessions: [], payments: [], sessionCount: 0, warnings: ['missing_xlsx'] };
|
||
}
|
||
|
||
const wb = XLSX.readFile(xlsxPath, { cellDates: false, raw: false });
|
||
const infoSheet = pickSheet(wb, INFO_HINTS);
|
||
const attendanceSheetName = pickSheet(wb, ATTENDANCE_HINTS);
|
||
const paymentSheetName = pickSheet(wb, PAYMENT_HINTS);
|
||
|
||
const infoLookup = infoSheet ? extractInfoLookup(wb.Sheets[infoSheet]) : new Map();
|
||
const attendance = attendanceSheetName
|
||
? extractAttendance(wb.Sheets[attendanceSheetName])
|
||
: { sessions: [], sessionCount: 0, weekdays: [], startDate: null };
|
||
const payments = paymentSheetName
|
||
? extractPayments(wb.Sheets[paymentSheetName], infoLookup, refs)
|
||
: [];
|
||
|
||
enrichIdentity(attendance.sessions, payments, infoLookup, refs);
|
||
|
||
const warnings = [];
|
||
if (!attendanceSheetName) warnings.push('missing_attendance_sheet');
|
||
if (!paymentSheetName) warnings.push('missing_payment_sheet');
|
||
|
||
return {
|
||
...meta,
|
||
sourceFile: path.basename(xlsxPath),
|
||
sheets: {
|
||
info: infoSheet,
|
||
attendance: attendanceSheetName,
|
||
payments: paymentSheetName
|
||
},
|
||
sessionCount: attendance.sessionCount,
|
||
sessions: attendance.sessions,
|
||
weekdays: attendance.weekdays,
|
||
startDate: attendance.startDate || meta.dbStartDate || meta.startDate,
|
||
payments,
|
||
warnings
|
||
};
|
||
};
|
||
|
||
const buildImportDocument = (rawRoot, excludePatterns = [/تکسا\s+تیر/i]) => {
|
||
const refs = loadReferenceData();
|
||
const entries = fs
|
||
.readdirSync(rawRoot, { withFileTypes: true })
|
||
.filter((d) => d.isDirectory())
|
||
.map((d) => d.name)
|
||
.filter((name) => !excludePatterns.some((re) => re.test(name)))
|
||
.sort((a, b) => a.localeCompare(b, 'fa'));
|
||
|
||
const classes = entries.map((name) => convertClassFolder(path.join(rawRoot, name), name, refs));
|
||
const coursesMap = new Map();
|
||
|
||
for (const cls of classes) {
|
||
if (!coursesMap.has(cls.courseTitle)) {
|
||
coursesMap.set(cls.courseTitle, {
|
||
title: cls.courseTitle,
|
||
type: cls.type,
|
||
classes: []
|
||
});
|
||
}
|
||
const course = coursesMap.get(cls.courseTitle);
|
||
if (cls.type === 'Private') course.type = 'Private';
|
||
|
||
course.classes.push({
|
||
name: cls.className,
|
||
folderName: cls.folderName,
|
||
sourceFile: cls.sourceFile,
|
||
startDate: cls.startDate,
|
||
days: cls.weekdays,
|
||
startTime: '19:00',
|
||
endTime: '20:30',
|
||
numberOfSessions: cls.sessionCount || undefined,
|
||
sessions: cls.sessions,
|
||
payments: cls.payments
|
||
});
|
||
}
|
||
|
||
const courses = [...coursesMap.values()];
|
||
const summary = {
|
||
courses: courses.length,
|
||
classes: classes.length,
|
||
sessions: classes.reduce((n, c) => n + (c.sessionCount || 0), 0),
|
||
attendanceRecords: classes.reduce(
|
||
(n, c) => n + c.sessions.reduce((m, s) => m + s.attendance.length, 0),
|
||
0
|
||
),
|
||
payments: classes.reduce((n, c) => n + c.payments.length, 0),
|
||
transactions: classes.reduce(
|
||
(n, c) => n + c.payments.reduce((m, p) => m + (p.transactions?.length || 0), 0),
|
||
0
|
||
),
|
||
excluded: excludePatterns.map(String),
|
||
classesDetail: classes.map((c) => ({
|
||
className: c.className,
|
||
courseTitle: c.courseTitle,
|
||
sourceFile: c.sourceFile,
|
||
sessionCount: c.sessionCount,
|
||
attendanceRecords: c.sessions.reduce((m, s) => m + s.attendance.length, 0),
|
||
payments: c.payments.length,
|
||
transactions: c.payments.reduce((m, p) => m + (p.transactions?.length || 0), 0),
|
||
warnings: c.warnings
|
||
}))
|
||
};
|
||
|
||
return {
|
||
version: 1,
|
||
generatedAt: new Date().toISOString(),
|
||
source: path.basename(rawRoot),
|
||
summary,
|
||
courses
|
||
};
|
||
};
|
||
|
||
const main = () => {
|
||
const args = process.argv.slice(2);
|
||
const positional = args.filter((a) => !a.startsWith('--'));
|
||
const excludeArg = args.find((a) => a.startsWith('--exclude='));
|
||
const excludePatterns = excludeArg
|
||
? [new RegExp(excludeArg.slice('--exclude='.length), 'i')]
|
||
: [/تکسا\s+تیر/i];
|
||
|
||
const rawRoot = path.resolve(positional[0] || DEFAULT_RAW);
|
||
const outPath = path.resolve(positional[1] || DEFAULT_OUT);
|
||
|
||
if (!fs.existsSync(rawRoot)) {
|
||
console.error(`Raw data folder not found: ${rawRoot}`);
|
||
process.exit(1);
|
||
}
|
||
|
||
const doc = buildImportDocument(rawRoot, excludePatterns);
|
||
fs.writeFileSync(outPath, `${JSON.stringify(doc, null, 2)}\n`, 'utf8');
|
||
console.log(`Wrote ${outPath}`);
|
||
console.log(JSON.stringify(doc.summary, null, 2));
|
||
};
|
||
|
||
main();
|