kavehhn
bf5e66fa95
Support uploading structured JSON to create courses, classes, and students without wiping existing data, and merge user name fields while adding gender and registration details from source sheets.
390 lines
12 KiB
JavaScript
390 lines
12 KiB
JavaScript
#!/usr/bin/env node
|
||
'use strict';
|
||
|
||
/**
|
||
* Converts class-folder xlsx files under raw-data into a structured import JSON.
|
||
* Student photo folders are ignored.
|
||
*
|
||
* Usage: node scripts/convert-raw-data.js [raw-data-root] [output.json]
|
||
*/
|
||
|
||
const fs = require('fs');
|
||
const path = require('path');
|
||
const XLSX = require('xlsx');
|
||
|
||
const MONTHS = {
|
||
فروردین: 1,
|
||
اردیبهشت: 2,
|
||
خرداد: 3,
|
||
تیر: 4,
|
||
مرداد: 5,
|
||
شهریور: 6,
|
||
مهر: 7,
|
||
آبان: 8,
|
||
آذر: 9,
|
||
دی: 10,
|
||
بهمن: 11,
|
||
اسفند: 12
|
||
};
|
||
|
||
const INFO_SHEET_HINTS = ['اطلاعات کلی', 'مشخصات کلی', 'اطلاعات'];
|
||
const FALLBACK_SHEET_HINTS = ['شهریه'];
|
||
|
||
const DEFAULT_RAW = path.join(
|
||
__dirname,
|
||
'..',
|
||
'..',
|
||
'raw-data',
|
||
'برنامه آموزشی 1405-20260814T213206Z-1-001',
|
||
'برنامه آموزشی 1405'
|
||
);
|
||
const DEFAULT_OUT = path.join(__dirname, '..', 'data', 'import-data.json');
|
||
|
||
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, ' ')
|
||
.replace(/\s+/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];
|
||
const mm = String(gm).padStart(2, '0');
|
||
const dd = String(gd).padStart(2, '0');
|
||
return `${gyOut}-${mm}-${dd}`;
|
||
};
|
||
|
||
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 normalizePhone = (raw) => {
|
||
if (raw == null || raw === '') return '';
|
||
let digits = toEnglishDigits(raw).replace(/[^\d+]/g, '');
|
||
if (digits.includes('-') || String(raw).includes('-')) {
|
||
// Prefer first Iranian mobile-looking segment
|
||
const parts = toEnglishDigits(raw)
|
||
.split(/[-–—,\/|\s]+/)
|
||
.map((p) => p.replace(/\D/g, ''))
|
||
.filter(Boolean);
|
||
const candidate =
|
||
parts.find((p) => /^(0?9\d{9})$/.test(p)) ||
|
||
parts.find((p) => p.length >= 10) ||
|
||
parts[0] ||
|
||
'';
|
||
digits = candidate;
|
||
}
|
||
digits = digits.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}`;
|
||
if (digits.length === 11 && digits.startsWith('09')) return digits;
|
||
return digits || '';
|
||
};
|
||
|
||
const normalizeNationalId = (raw) => {
|
||
if (raw == null || raw === '') return '';
|
||
const digits = toEnglishDigits(raw).replace(/\D/g, '');
|
||
return digits;
|
||
};
|
||
|
||
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 jalaliYear = null;
|
||
let jalaliMonth = null;
|
||
let startDate = null;
|
||
|
||
if (monthMatch) {
|
||
const monthName = monthMatch[1];
|
||
jalaliYear = Number(monthMatch[2]);
|
||
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',
|
||
jalaliYear,
|
||
jalaliMonth,
|
||
startDate
|
||
};
|
||
};
|
||
|
||
const findHeaderMap = (headerRow) => {
|
||
const map = {};
|
||
(headerRow || []).forEach((cell, idx) => {
|
||
const h = normalizeHeader(cell);
|
||
if (!h) return;
|
||
if (h === 'نام' || h === 'نام ') map.firstName = idx;
|
||
else if (h.includes('نام خانوادگی')) map.lastName = idx;
|
||
else if (h.includes('نام و نام خانوادگی')) map.fullName = idx;
|
||
else if (h.includes('تلفن همراه هنرجو') || h.includes('شماره تماس هنرجو')) map.phone = idx;
|
||
else if (h.includes('تلفن همراه والد')) map.parentPhone = idx;
|
||
else if (h.includes('تلفن همراه') || h.includes('شماره تماس') || h.includes('شماره همراه')) {
|
||
if (map.phone == null) map.phone = idx;
|
||
} else if (h.includes('کد ملی')) map.nationalId = idx;
|
||
else if (h.includes('شماره شناسنامه')) map.birthCertificateNumber = idx;
|
||
else if (h.includes('کد پستی')) map.postalCode = idx;
|
||
else if (h.includes('محل صدور')) map.placeOfIssue = idx;
|
||
else if (h.includes('نام پدر')) map.fatherName = idx;
|
||
else if (h.includes('تاریخ تولد')) map.birthDate = idx;
|
||
else if (h.includes('تحصیلات')) map.education = idx;
|
||
else if (h === 'آدرس' || h.includes('آدرس')) map.address = idx;
|
||
});
|
||
return map;
|
||
};
|
||
|
||
const pickSheet = (wb, hints) => {
|
||
for (const hint of hints) {
|
||
const found = wb.SheetNames.find((n) => normalizeHeader(n) === hint || normalizeHeader(n).includes(hint));
|
||
if (found) return found;
|
||
}
|
||
return null;
|
||
};
|
||
|
||
const rowToStudent = (row, map) => {
|
||
const get = (key) => (map[key] == null ? '' : cleanText(row[map[key]]));
|
||
|
||
let name = '';
|
||
if (map.fullName != null) {
|
||
name = get('fullName');
|
||
} else {
|
||
name = cleanText(`${get('firstName')} ${get('lastName')}`);
|
||
}
|
||
|
||
const phone = normalizePhone(get('phone') || get('parentPhone'));
|
||
const parentPhone = map.parentPhone != null ? normalizePhone(get('parentPhone')) : '';
|
||
const nationalIdCode = normalizeNationalId(get('nationalId'));
|
||
const birthDateRaw = get('birthDate');
|
||
const birthDate = parseJalaliDate(birthDateRaw);
|
||
|
||
if (!name && !phone && !nationalIdCode) return null;
|
||
|
||
const student = {
|
||
name: name || (nationalIdCode ? `کاربر ${nationalIdCode}` : `کاربر ${phone}`),
|
||
phoneNumber: phone || undefined,
|
||
nationalIdCode: nationalIdCode || undefined,
|
||
birthCertificateNumber: get('birthCertificateNumber') || undefined,
|
||
postalCode: get('postalCode') || undefined,
|
||
placeOfIssue: get('placeOfIssue') || undefined,
|
||
fatherName: get('fatherName') || undefined,
|
||
birthDate: birthDate || undefined,
|
||
birthDateJalali: birthDateRaw || undefined,
|
||
education: get('education') || undefined,
|
||
address: get('address') || undefined
|
||
};
|
||
|
||
if (parentPhone && parentPhone !== phone) {
|
||
student.parentPhoneNumber = parentPhone;
|
||
}
|
||
|
||
// Drop undefined keys for cleaner JSON
|
||
Object.keys(student).forEach((k) => {
|
||
if (student[k] === undefined || student[k] === '') delete student[k];
|
||
});
|
||
|
||
return student;
|
||
};
|
||
|
||
const extractStudentsFromSheet = (sheet) => {
|
||
const rows = XLSX.utils.sheet_to_json(sheet, { header: 1, defval: null, raw: false });
|
||
let headerIdx = -1;
|
||
let map = {};
|
||
|
||
for (let i = 0; i < Math.min(rows.length, 15); i += 1) {
|
||
const candidate = findHeaderMap(rows[i]);
|
||
const hasIdentity =
|
||
candidate.firstName != null ||
|
||
candidate.fullName != null ||
|
||
candidate.lastName != null;
|
||
const hasContact = candidate.phone != null || candidate.nationalId != null;
|
||
if (hasIdentity && (hasContact || candidate.lastName != null)) {
|
||
headerIdx = i;
|
||
map = candidate;
|
||
break;
|
||
}
|
||
}
|
||
|
||
if (headerIdx < 0) return [];
|
||
|
||
const students = [];
|
||
for (let i = headerIdx + 1; i < rows.length; i += 1) {
|
||
const row = rows[i];
|
||
if (!row || row.every((c) => c == null || String(c).trim() === '')) continue;
|
||
const student = rowToStudent(row, map);
|
||
if (student) students.push(student);
|
||
}
|
||
return students;
|
||
};
|
||
|
||
const findClassXlsx = (dirPath) => {
|
||
const files = fs.readdirSync(dirPath).filter((f) => f.endsWith('.xlsx') && !f.startsWith('~$'));
|
||
if (files.length === 0) return null;
|
||
// Prefer file whose name looks like the class (not "لیست شرکت کنندگان...")
|
||
const preferred = files.find((f) => !f.includes('لیست شرکت کنندگان')) || files[0];
|
||
return path.join(dirPath, preferred);
|
||
};
|
||
|
||
const convertClassFolder = (dirPath, folderName) => {
|
||
const meta = parseFolderMeta(folderName);
|
||
const xlsxPath = findClassXlsx(dirPath);
|
||
let students = [];
|
||
let sourceFile = null;
|
||
|
||
if (xlsxPath) {
|
||
sourceFile = path.basename(xlsxPath);
|
||
const wb = XLSX.readFile(xlsxPath, { cellDates: false, raw: false });
|
||
const infoSheetName = pickSheet(wb, INFO_SHEET_HINTS);
|
||
if (infoSheetName) {
|
||
students = extractStudentsFromSheet(wb.Sheets[infoSheetName]);
|
||
}
|
||
if (students.length === 0) {
|
||
const fallback = pickSheet(wb, FALLBACK_SHEET_HINTS);
|
||
if (fallback) students = extractStudentsFromSheet(wb.Sheets[fallback]);
|
||
}
|
||
}
|
||
|
||
return {
|
||
...meta,
|
||
sourceFile,
|
||
students
|
||
};
|
||
};
|
||
|
||
const buildImportDocument = (rawRoot) => {
|
||
const entries = fs
|
||
.readdirSync(rawRoot, { withFileTypes: true })
|
||
.filter((d) => d.isDirectory())
|
||
.map((d) => d.name)
|
||
.sort((a, b) => a.localeCompare(b, 'fa'));
|
||
|
||
const classes = entries.map((name) => convertClassFolder(path.join(rawRoot, name), name));
|
||
|
||
const coursesMap = new Map();
|
||
for (const cls of classes) {
|
||
const key = cls.courseTitle;
|
||
if (!coursesMap.has(key)) {
|
||
coursesMap.set(key, {
|
||
title: cls.courseTitle,
|
||
type: cls.type,
|
||
price: 0,
|
||
classes: []
|
||
});
|
||
}
|
||
const course = coursesMap.get(key);
|
||
if (cls.type === 'Private') course.type = 'Private';
|
||
course.classes.push({
|
||
name: cls.className,
|
||
folderName: cls.folderName,
|
||
startDate: cls.startDate,
|
||
jalaliYear: cls.jalaliYear,
|
||
jalaliMonth: cls.jalaliMonth,
|
||
sourceFile: cls.sourceFile,
|
||
students: cls.students
|
||
});
|
||
}
|
||
|
||
const courses = [...coursesMap.values()];
|
||
const userCount = classes.reduce((n, c) => n + c.students.length, 0);
|
||
|
||
return {
|
||
version: 1,
|
||
generatedAt: new Date().toISOString(),
|
||
source: path.basename(rawRoot),
|
||
summary: {
|
||
courses: courses.length,
|
||
classes: classes.length,
|
||
students: userCount
|
||
},
|
||
courses
|
||
};
|
||
};
|
||
|
||
const main = () => {
|
||
const rawRoot = path.resolve(process.argv[2] || DEFAULT_RAW);
|
||
const outPath = path.resolve(process.argv[3] || DEFAULT_OUT);
|
||
|
||
if (!fs.existsSync(rawRoot)) {
|
||
console.error(`Raw data folder not found: ${rawRoot}`);
|
||
process.exit(1);
|
||
}
|
||
|
||
const doc = buildImportDocument(rawRoot);
|
||
fs.writeFileSync(outPath, `${JSON.stringify(doc, null, 2)}\n`, 'utf8');
|
||
console.log(`Wrote ${outPath}`);
|
||
console.log(JSON.stringify(doc.summary, null, 2));
|
||
};
|
||
|
||
main();
|