generated from gitea_admin/default
387 lines
11 KiB
JavaScript
387 lines
11 KiB
JavaScript
import fs from "node:fs"
|
|
import path from "node:path"
|
|
import process from "node:process"
|
|
import ExcelJS from "exceljs"
|
|
import mysql from "mysql2/promise"
|
|
import nodemailer from "nodemailer"
|
|
|
|
const ROOT_DIR = process.cwd()
|
|
const DEFAULT_OUTPUT_PATH = "exports/inscriptions-lycee-{date}.xlsx"
|
|
|
|
const columns = [
|
|
["id", "ID demande"],
|
|
["created_at", "Date demande"],
|
|
["nom_etablissement", "Nom établissement"],
|
|
["departement", "Département"],
|
|
["ville", "Ville"],
|
|
["coordinateur_nom", "Coordinateur"],
|
|
["email", "Email"],
|
|
["telephone", "Téléphone"],
|
|
["discipline", "Discipline"],
|
|
["niveau_classe", "Niveau classe"],
|
|
["nombre_eleves", "Nombre élèves"],
|
|
["priorite_concert_rencontre", "Priorité concert-rencontre"],
|
|
["priorite_immersion", "Priorité immersion"],
|
|
["priorite_eac", "Priorité EAC"],
|
|
["priorite_concert_commente", "Priorité concert commenté"],
|
|
["periode_souhaitee", "Période souhaitée"],
|
|
]
|
|
|
|
function loadDotEnv(filePath = path.join(ROOT_DIR, ".env")) {
|
|
if (!fs.existsSync(filePath)) {
|
|
return
|
|
}
|
|
|
|
const content = fs.readFileSync(filePath, "utf8")
|
|
|
|
for (const line of content.split(/\r?\n/)) {
|
|
const trimmed = line.trim()
|
|
|
|
if (!trimmed || trimmed.startsWith("#") || !trimmed.includes("=")) {
|
|
continue
|
|
}
|
|
|
|
const index = trimmed.indexOf("=")
|
|
const key = trimmed.slice(0, index).trim()
|
|
let value = trimmed.slice(index + 1).trim()
|
|
|
|
if ((value.startsWith('"') && value.endsWith('"')) || (value.startsWith("'") && value.endsWith("'"))) {
|
|
value = value.slice(1, -1)
|
|
}
|
|
|
|
if (!process.env[key]) {
|
|
process.env[key] = value
|
|
}
|
|
}
|
|
}
|
|
|
|
function requireEnv(name) {
|
|
const value = process.env[name]
|
|
|
|
if (!value) {
|
|
throw new Error(`Variable d'environnement manquante : ${name}`)
|
|
}
|
|
|
|
return value
|
|
}
|
|
|
|
function optionalEnv(name, fallback = "") {
|
|
return process.env[name] || fallback
|
|
}
|
|
|
|
function formatLocalDate(date = new Date()) {
|
|
const year = date.getFullYear()
|
|
const month = String(date.getMonth() + 1).padStart(2, "0")
|
|
const day = String(date.getDate()).padStart(2, "0")
|
|
|
|
return `${year}-${month}-${day}`
|
|
}
|
|
|
|
function resolveDatedPath(filePath) {
|
|
return filePath.replaceAll("{date}", formatLocalDate())
|
|
}
|
|
|
|
function getSmtpConfig() {
|
|
return {
|
|
host: optionalEnv("NUXT_SMTP_HOST"),
|
|
port: Number(optionalEnv("NUXT_SMTP_PORT", "587")),
|
|
secure: optionalEnv("NUXT_SMTP_SECURE", "false").toLowerCase() === "true",
|
|
user: optionalEnv("NUXT_SMTP_USER"),
|
|
password: optionalEnv("NUXT_SMTP_PASSWORD"),
|
|
fromEmail: optionalEnv("NUXT_SMTP_FROM_EMAIL"),
|
|
fromName: optionalEnv("NUXT_SMTP_FROM_NAME"),
|
|
reportEmail: optionalEnv("ACADEMIE_EXCEL_REPORT_EMAIL", optionalEnv("NUXT_SMTP_FROM_EMAIL")),
|
|
}
|
|
}
|
|
|
|
function isSmtpConfigured(config) {
|
|
return Boolean(config.host && config.port && config.user && config.password && config.fromEmail && config.reportEmail)
|
|
}
|
|
|
|
async function sendExportReport({ ok, mode, rowsCount = 0, localOutputPath = "", uploadPath = "", uploadedName = "", skippedUpload = false, error = null }) {
|
|
const config = getSmtpConfig()
|
|
|
|
if (!isSmtpConfigured(config)) {
|
|
console.warn("Email de rapport non envoyé : configuration SMTP incomplète.")
|
|
return false
|
|
}
|
|
|
|
const transporter = nodemailer.createTransport({
|
|
host: config.host,
|
|
port: config.port,
|
|
secure: config.secure,
|
|
connectionTimeout: 10000,
|
|
greetingTimeout: 10000,
|
|
socketTimeout: 15000,
|
|
auth: {
|
|
user: config.user,
|
|
pass: config.password,
|
|
},
|
|
})
|
|
const from = config.fromName ? `"${config.fromName}" <${config.fromEmail}>` : config.fromEmail
|
|
const subject = ok
|
|
? `Export Lycée OK - ${formatLocalDate()}`
|
|
: `Export Lycée ERREUR - ${formatLocalDate()}`
|
|
const text = ok
|
|
? [
|
|
"L'export Excel des demandes projet lycée s'est terminé correctement.",
|
|
"",
|
|
`Mode : ${mode}`,
|
|
`Demandes exportées : ${rowsCount}`,
|
|
localOutputPath ? `Fichier local : ${localOutputPath}` : "Fichier local : non généré",
|
|
skippedUpload ? "Upload SharePoint : ignoré" : `Fichier SharePoint : ${uploadPath || uploadedName}`,
|
|
uploadedName && uploadedName !== uploadPath ? `Nom retourné par SharePoint : ${uploadedName}` : null,
|
|
].filter(Boolean).join("\n")
|
|
: [
|
|
"L'export Excel des demandes projet lycée a échoué.",
|
|
"",
|
|
`Mode : ${mode || "non déterminé"}`,
|
|
`Erreur : ${error?.message || String(error)}`,
|
|
].join("\n")
|
|
|
|
await transporter.sendMail({
|
|
from,
|
|
to: config.reportEmail,
|
|
subject,
|
|
text,
|
|
})
|
|
|
|
return true
|
|
}
|
|
|
|
async function trySendExportReport(report) {
|
|
try {
|
|
return await sendExportReport(report)
|
|
} catch (error) {
|
|
console.error("Erreur envoi email de rapport :", error)
|
|
return false
|
|
}
|
|
}
|
|
|
|
async function createXlsxBuffer(rows) {
|
|
const workbook = new ExcelJS.Workbook()
|
|
workbook.creator = "wondif_vue"
|
|
workbook.lastModifiedBy = "wondif_vue"
|
|
workbook.created = new Date()
|
|
workbook.modified = new Date()
|
|
const worksheet = workbook.addWorksheet("Demandes", {
|
|
views: [{ state: "frozen", ySplit: 1 }],
|
|
})
|
|
const tableRows = rows.map((row) =>
|
|
columns.map(([key]) => {
|
|
const value = row[key]
|
|
if (value === null || value === undefined) {
|
|
return ""
|
|
}
|
|
if (value instanceof Date) {
|
|
return value
|
|
}
|
|
return value
|
|
})
|
|
)
|
|
worksheet.addTable({
|
|
name: "TableDemandesLycee",
|
|
ref: "A1",
|
|
headerRow: true,
|
|
totalsRow: false,
|
|
style: {
|
|
theme: "TableStyleMedium2",
|
|
showRowStripes: true,
|
|
},
|
|
columns: columns.map(([, label]) => ({
|
|
name: label,
|
|
filterButton: true,
|
|
})),
|
|
rows: tableRows,
|
|
})
|
|
worksheet.columns.forEach((column) => {
|
|
column.width = 22
|
|
})
|
|
worksheet.getRow(1).font = {
|
|
bold: true,
|
|
}
|
|
return Buffer.from(await workbook.xlsx.writeBuffer())
|
|
}
|
|
|
|
async function getRows(db, mode) {
|
|
const where = mode === "pending" ? "WHERE exported_to_excel = 0" : ""
|
|
const selectColumns = columns.map(([key]) => key).join(", ")
|
|
const [rows] = await db.execute(`
|
|
SELECT ${selectColumns}
|
|
FROM projet_lycee_demandes
|
|
${where}
|
|
ORDER BY created_at ASC, id ASC
|
|
`)
|
|
|
|
return rows
|
|
}
|
|
|
|
async function getAccessToken() {
|
|
const tenantId = requireEnv("MICROSOFT_TENANT_ID")
|
|
const clientId = requireEnv("MICROSOFT_CLIENT_ID")
|
|
const clientSecret = requireEnv("MICROSOFT_CLIENT_SECRET")
|
|
const body = new URLSearchParams({
|
|
client_id: clientId,
|
|
client_secret: clientSecret,
|
|
scope: "https://graph.microsoft.com/.default",
|
|
grant_type: "client_credentials",
|
|
})
|
|
const response = await fetch(`https://login.microsoftonline.com/${tenantId}/oauth2/v2.0/token`, {
|
|
method: "POST",
|
|
headers: { "Content-Type": "application/x-www-form-urlencoded" },
|
|
body,
|
|
})
|
|
|
|
if (!response.ok) {
|
|
throw new Error(`Erreur token Microsoft Graph (${response.status}) : ${await response.text()}`)
|
|
}
|
|
|
|
const data = await response.json()
|
|
return data.access_token
|
|
}
|
|
|
|
async function uploadToMicrosoft365(buffer) {
|
|
const driveId = requireEnv("GRAPH_DRIVE_ID")
|
|
const itemId = optionalEnv("LYCEE_GRAPH_EXCEL_ITEM_ID")
|
|
const uploadPath = resolveDatedPath(optionalEnv("LYCEE_GRAPH_EXCEL_UPLOAD_PATH", DEFAULT_OUTPUT_PATH)).replace(/^\/+/, "")
|
|
const token = await getAccessToken()
|
|
const url = itemId
|
|
? `https://graph.microsoft.com/v1.0/drives/${encodeURIComponent(driveId)}/items/${encodeURIComponent(itemId)}/content`
|
|
: `https://graph.microsoft.com/v1.0/drives/${encodeURIComponent(driveId)}/root:/${uploadPath.split("/").map(encodeURIComponent).join("/")}:/content`
|
|
const response = await fetch(url, {
|
|
method: "PUT",
|
|
headers: {
|
|
Authorization: `Bearer ${token}`,
|
|
"Content-Type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
|
|
},
|
|
body: buffer,
|
|
})
|
|
|
|
if (!response.ok) {
|
|
throw new Error(`Erreur upload Microsoft Graph (${response.status}) : ${await response.text()}`)
|
|
}
|
|
|
|
const uploaded = await response.json()
|
|
|
|
return { uploaded, uploadPath }
|
|
}
|
|
|
|
async function markRowsExportSucceeded(db, rows, { markAsExported = false } = {}) {
|
|
if (!rows.length) {
|
|
return
|
|
}
|
|
|
|
const exportedColumnUpdate = markAsExported ? "exported_to_excel = 1," : ""
|
|
|
|
await db.query(
|
|
`
|
|
UPDATE projet_lycee_demandes
|
|
SET ${exportedColumnUpdate}
|
|
exported_to_excel_at = NOW(),
|
|
export_error = NULL
|
|
WHERE id IN (?)
|
|
`,
|
|
[rows.map((row) => row.id)]
|
|
)
|
|
}
|
|
|
|
async function markRowsExportFailed(db, rows, error) {
|
|
if (!rows.length) {
|
|
return
|
|
}
|
|
|
|
await db.query(
|
|
`
|
|
UPDATE projet_lycee_demandes
|
|
SET exported_to_excel_at = NOW(),
|
|
export_error = ?
|
|
WHERE id IN (?)
|
|
`,
|
|
[String(error?.message || error).slice(0, 5000), rows.map((row) => row.id)]
|
|
)
|
|
}
|
|
|
|
async function main() {
|
|
loadDotEnv()
|
|
|
|
const mode = optionalEnv("LYCEE_EXCEL_EXPORT_MODE", "all")
|
|
|
|
if (!["all", "pending"].includes(mode)) {
|
|
throw new Error("LYCEE_EXCEL_EXPORT_MODE doit valoir 'all' ou 'pending'.")
|
|
}
|
|
|
|
const db = await mysql.createConnection({
|
|
host: requireEnv("NUXT_MYSQL_HOST"),
|
|
port: Number(optionalEnv("NUXT_MYSQL_PORT", "3306")),
|
|
database: requireEnv("NUXT_MYSQL_DATABASE"),
|
|
user: requireEnv("NUXT_MYSQL_USER"),
|
|
password: requireEnv("NUXT_MYSQL_PASSWORD"),
|
|
charset: "utf8mb4",
|
|
})
|
|
|
|
let rows = []
|
|
|
|
try {
|
|
rows = await getRows(db, mode)
|
|
const buffer = await createXlsxBuffer(rows)
|
|
const localOutputPath = resolveDatedPath(optionalEnv("LYCEE_EXCEL_LOCAL_OUTPUT", ""))
|
|
let absoluteOutputPath = ""
|
|
|
|
if (localOutputPath) {
|
|
absoluteOutputPath = path.resolve(ROOT_DIR, localOutputPath)
|
|
fs.mkdirSync(path.dirname(absoluteOutputPath), { recursive: true })
|
|
fs.writeFileSync(absoluteOutputPath, buffer)
|
|
console.log(`Fichier local généré : ${absoluteOutputPath}`)
|
|
}
|
|
|
|
if (optionalEnv("LYCEE_EXCEL_SKIP_UPLOAD", "") === "1") {
|
|
console.log(`Upload Microsoft 365 ignoré. ${rows.length} ligne(s) générée(s).`)
|
|
await trySendExportReport({
|
|
ok: true,
|
|
mode,
|
|
rowsCount: rows.length,
|
|
localOutputPath: absoluteOutputPath,
|
|
skippedUpload: true,
|
|
})
|
|
return
|
|
}
|
|
|
|
const { uploaded, uploadPath } = await uploadToMicrosoft365(buffer)
|
|
|
|
await markRowsExportSucceeded(db, rows, { markAsExported: mode === "pending" })
|
|
|
|
await trySendExportReport({
|
|
ok: true,
|
|
mode,
|
|
rowsCount: rows.length,
|
|
localOutputPath: absoluteOutputPath,
|
|
uploadPath,
|
|
uploadedName: uploaded.name || uploaded.id,
|
|
})
|
|
|
|
console.log(`${rows.length} demande(s) exportée(s) vers Microsoft 365 : ${uploaded.name || uploaded.id}`)
|
|
} catch (error) {
|
|
await markRowsExportFailed(db, rows, error)
|
|
throw error
|
|
} finally {
|
|
await db.end()
|
|
}
|
|
}
|
|
|
|
main().catch(async (error) => {
|
|
console.error(error)
|
|
|
|
try {
|
|
loadDotEnv()
|
|
await sendExportReport({
|
|
ok: false,
|
|
mode: optionalEnv("LYCEE_EXCEL_EXPORT_MODE", "all"),
|
|
error,
|
|
})
|
|
} catch (mailError) {
|
|
console.error("Erreur envoi email de rapport :", mailError)
|
|
}
|
|
|
|
process.exit(1)
|
|
})
|