338 lines
14 KiB
JavaScript
338 lines
14 KiB
JavaScript
/////////////////////////////////////////////////
|
||
// APPELS DES MODULES
|
||
/////////////////////////////////////////////////
|
||
const path = require("path");
|
||
// CALCUL DE DATE
|
||
const moment = require('moment');
|
||
// uuid
|
||
const { v4: uuidv4 } = require('uuid');
|
||
const mysql = require('mysql2');
|
||
const mysql_promise = require('mysql2/promise');
|
||
const fetch = require('node-fetch');
|
||
const fs = require('fs/promises');
|
||
const nodemailer = require('nodemailer');
|
||
|
||
/////////////////////////////////////////////////
|
||
// APPELS DES MODULES PERSOS
|
||
/////////////////////////////////////////////////
|
||
// loggings
|
||
const logger = require("../public/js/app/logger_adhesion");
|
||
// DB
|
||
const { connect_DB_promise } = require('../modules/db/databaseconnect');
|
||
|
||
////////////////////////////////////////////////////
|
||
// FILE NAME
|
||
////////////////////////////////////////////////////
|
||
const filename = path.parse(__filename).base;
|
||
|
||
|
||
|
||
/////////////////////////////////////////////////
|
||
// FONCTIONS
|
||
/////////////////////////////////////////////////
|
||
|
||
const runSync_prof_strapi = async function(req, res) {
|
||
|
||
logger.info("======================================", { label: filename });
|
||
logger.info("======== FUNCTION sync_prof_strapi ==========", { label: filename });
|
||
logger.info("======================================", { label: filename });
|
||
|
||
// en mode CRON il n'y a pas de session web, donc pas de req.session
|
||
const sid = req?.session?.id || "cron";
|
||
|
||
try {
|
||
|
||
// ENV requis : STRAPI_URL, STRAPI_TOKEN, DB_HOST, DB_USER, DB_PASSWORD, DB_NAME
|
||
const {
|
||
DOMAIN_STRAPI,
|
||
strapi_token,
|
||
db_connection_host,
|
||
db_connection_user,
|
||
db_connection_password,
|
||
db_connection_database
|
||
} = process.env;
|
||
|
||
logger.info(`DOMAIN_STRAPI=${DOMAIN_STRAPI}, strapi_token=${strapi_token}`, { numsession: sid });
|
||
|
||
|
||
const db_connection_promise = await mysql_promise.createConnection({
|
||
host: process.env.db_connection_host,
|
||
port: process.env.db_connection_port,
|
||
user: process.env.db_connection_user,
|
||
password: process.env.db_connection_password,
|
||
database: process.env.db_connection_database
|
||
});
|
||
|
||
// ==== FONCTION D'AJOUT DES PROFS DANS STRAPI ====
|
||
const upsert = async (payload) => {
|
||
// 1) chercher par legacy_id
|
||
const q = new URLSearchParams({ "filters[adherent_mail][$eq]": payload.data.adherent_mail || "" });
|
||
const getUrl = `${DOMAIN_STRAPI}/api/adherent-professeurs?${q.toString()}`;
|
||
const r0 = await fetch(getUrl, { headers: { Authorization: `Bearer ${strapi_token}` }});
|
||
const j0 = await r0.json();
|
||
const found = j0?.data?.[0];
|
||
|
||
if (found?.id) {
|
||
// déjà présent → on NE LÈVE PAS d’erreur, on signale juste un skip
|
||
return { action: "skip_existing", id: found.id };
|
||
} else {
|
||
const postUrl = `${DOMAIN_STRAPI}/api/adherent-professeurs`;
|
||
const r = await fetch(postUrl, {
|
||
method: "POST",
|
||
headers: {
|
||
"Content-Type": "application/json",
|
||
Authorization: `Bearer ${strapi_token}`
|
||
},
|
||
body: JSON.stringify(payload)
|
||
});
|
||
if (!r.ok) {
|
||
const txt = await r.text();
|
||
throw new Error(`POST ${postUrl} -> ${r.status} ${txt}`);
|
||
}
|
||
const j = await r.json();
|
||
return { action: "create", id: j?.data?.id };
|
||
}
|
||
};
|
||
|
||
// ==== RÉCUPÉRATION DES DONNÉES SUR LES PROFS DANS LA DB ====
|
||
const [rows] = await db_connection_promise.query(`
|
||
SELECT
|
||
ID,
|
||
association_suzuki,
|
||
adherent_nom,
|
||
adherent_prenom,
|
||
adherent_mail,
|
||
adherent_telephone_mobile,
|
||
adherent_prof_instrument1,
|
||
adherent_prof_instrument1_esa,
|
||
adherent_prof_instrument2,
|
||
adherent_prof_instrument2_esa,
|
||
adherent_prof_sece,
|
||
adherent_prof_ecole1,
|
||
adherent_prof_ecole1_mail,
|
||
adherent_prof_ecole1_web,
|
||
adherent_prof_ecole1_cp,
|
||
adherent_prof_ecole1_ville,
|
||
adherent_prof_ecole2,
|
||
adherent_prof_ecole2_mail,
|
||
adherent_prof_ecole2_web,
|
||
adherent_prof_ecole2_cp,
|
||
adherent_prof_ecole2_ville,
|
||
adherent_prof_ecole3,
|
||
adherent_prof_ecole3_mail,
|
||
adherent_prof_ecole3_web,
|
||
adherent_prof_ecole3_adresse,
|
||
adherent_prof_ecole3_cp,
|
||
adherent_prof_ecole3_ville
|
||
FROM adherent_professeur
|
||
`);
|
||
|
||
console.log('rows = ', rows);
|
||
|
||
|
||
// ==== GEO helpers ====
|
||
// pour l'ajout de la lat, long et region dans les données de strapi pour le prof
|
||
let CP_MAP = null;
|
||
|
||
async function loadCpMap(filePath = "src/data/cp_region_lat_long.json") {
|
||
if (CP_MAP) return CP_MAP;
|
||
const buf = await fs.readFile(filePath, "utf8");
|
||
CP_MAP = JSON.parse(buf);
|
||
return CP_MAP;
|
||
}
|
||
function normalizeCp(cp) {
|
||
if (!cp) return null;
|
||
const s = String(cp).trim().replace(/\s+/g, "");
|
||
return /^\d{5}$/.test(s) ? s : null;
|
||
}
|
||
|
||
function pickProfPostalCode(cp) {
|
||
return (
|
||
normalizeCp(cp) || null
|
||
);
|
||
}
|
||
|
||
// helper pour convertir en number sûr (gère "48,85" et strings)
|
||
const toNum = (v) => {
|
||
if (v == null) return null;
|
||
const n = (typeof v === "number") ? v : parseFloat(String(v).replace(",", "."));
|
||
return Number.isFinite(n) ? n : null;
|
||
};
|
||
|
||
const cpMap = await loadCpMap("src/data/cp_region_lat_long.json");
|
||
|
||
// ==== AJOUT DES PROFS DANS STRAPI ====
|
||
let creates = 0, errors = 0;
|
||
let attempted = 0, skipped_existing = 0;
|
||
|
||
for (const r of rows) {
|
||
// 3.1 choisir le CP prioritaire
|
||
const cp_1 = pickProfPostalCode(r.adherent_prof_ecole1_cp);
|
||
const cp_2 = pickProfPostalCode(r.adherent_prof_ecole2_cp);
|
||
const cp_3 = pickProfPostalCode(r.adherent_prof_ecole3_cp);
|
||
let geo_lat_1 = null, geo_lng_1 = null, geo_region_1 = null;
|
||
let geo_lat_2 = null, geo_lng_2 = null, geo_region_2 = null;
|
||
let geo_lat_3 = null, geo_lng_3 = null, geo_region_3 = null;
|
||
|
||
if (cp_1 && cpMap[cp_1]) {
|
||
const meta = cpMap[cp_1];
|
||
// tolérance: on ne renseigne que si lat/lng sont présents
|
||
geo_lat_1 = toNum(meta.lat);
|
||
geo_lng_1 = toNum(meta.lng);
|
||
geo_region_1 = meta.region ? String(meta.region) : null;
|
||
}
|
||
if (cp_2 && cpMap[cp_2]) {
|
||
const meta = cpMap[cp_2];
|
||
// tolérance: on ne renseigne que si lat/lng sont présents
|
||
geo_lat_2 = toNum(meta.lat);
|
||
geo_lng_2 = toNum(meta.lng);
|
||
geo_region_2 = meta.region ? String(meta.region) : null;
|
||
}
|
||
if (cp_3 && cpMap[cp_2]) {
|
||
const meta = cpMap[cp_3];
|
||
// tolérance: on ne renseigne que si lat/lng sont présents
|
||
geo_lat_3 = toNum(meta.lat);
|
||
geo_lng_3 = toNum(meta.lng);
|
||
geo_region_3 = meta.region ? String(meta.region) : null;
|
||
}
|
||
// 3.2 payload de base
|
||
const data = {
|
||
legacy_id: r.ID.toString().trim(),
|
||
association_suzuki: r.association_suzuki?.trim() || null,
|
||
adherent_nom: r.adherent_nom?.trim() || null,
|
||
adherent_prenom: r.adherent_prenom?.trim() || null,
|
||
adherent_mail: r.adherent_mail?.toString().trim().toLowerCase() || null,
|
||
adherent_telephone_mobile: r.adherent_telephone_mobile?.trim() || null,
|
||
adherent_prof_instrument1: r.adherent_prof_instrument1?.trim() || null,
|
||
adherent_prof_instrument1_niveau: r.adherent_prof_instrument1_esa?.trim() || null,
|
||
adherent_prof_instrument2: r.adherent_prof_instrument2?.trim() || null,
|
||
adherent_prof_instrument2_niveau: r.adherent_prof_instrument2_esa?.trim() || null,
|
||
adherent_prof_sece: r.adherent_prof_sece?.trim() || null,
|
||
adherent_prof_ecole1: r.adherent_prof_ecole1?.trim() || null,
|
||
adherent_prof_ecole1_mail: r.adherent_prof_ecole1_mail?.trim() || null,
|
||
adherent_prof_ecole1_web: r.adherent_prof_ecole1_web?.trim() || null,
|
||
adherent_prof_ecole1_cp: r.adherent_prof_ecole1_cp?.trim() || null,
|
||
adherent_prof_ecole1_ville: r.adherent_prof_ecole1_ville?.trim() || null,
|
||
adherent_prof_ecole2: r.adherent_prof_ecole2?.trim() || null,
|
||
adherent_prof_ecole2_mail: r.adherent_prof_ecole2_mail?.trim() || null,
|
||
adherent_prof_ecole2_web: r.adherent_prof_ecole2_web?.trim() || null,
|
||
adherent_prof_ecole2_cp: r.adherent_prof_ecole2_cp?.trim() || null,
|
||
adherent_prof_ecole2_ville: r.adherent_prof_ecole2_ville?.trim() || null,
|
||
adherent_prof_ecole3: r.adherent_prof_ecole3?.trim() || null,
|
||
adherent_prof_ecole3_mail: r.adherent_prof_ecole3_mail?.trim() || null,
|
||
adherent_prof_ecole3_web: r.adherent_prof_ecole3_web?.trim() || null,
|
||
adherent_prof_ecole3_adresse: r.adherent_prof_ecole3_adresse?.trim() || null,
|
||
adherent_prof_ecole3_cp: r.adherent_prof_ecole3_cp?.trim() || null,
|
||
adherent_prof_ecole3_ville: r.adherent_prof_ecole3_ville?.trim() || null,
|
||
// >>> Injecte UNIQUEMENT des scalaires, si valides
|
||
...(geo_region_1 ? { adherent_prof_ecole1_region: geo_region_1 } : {}),
|
||
...(geo_lat_1 != null ? { adherent_prof_ecole1_lat: geo_lat_1 } : {}),
|
||
...(geo_lng_1 != null ? { adherent_prof_ecole1_long: geo_lng_1 } : {}),
|
||
...(geo_region_2 ? { adherent_prof_ecole1_region: geo_region_2 } : {}),
|
||
...(geo_lat_2 != null ? { adherent_prof_ecole1_lat: geo_lat_2 } : {}),
|
||
...(geo_lng_2 != null ? { adherent_prof_ecole1_long: geo_lng_2 } : {}),
|
||
...(geo_region_3 ? { adherent_prof_ecole1_region: geo_region_3 } : {}),
|
||
...(geo_lat_3 != null ? { adherent_prof_ecole1_lat: geo_lat_3 } : {}),
|
||
...(geo_lng_3 != null ? { adherent_prof_ecole1_long: geo_lng_3 } : {})
|
||
};
|
||
|
||
// champs obligatoires minimum
|
||
if (!data.adherent_mail || !data.adherent_nom || !data.adherent_prenom) {
|
||
console.warn(`skip: mail/nom/prenom manquants`);
|
||
continue;
|
||
}
|
||
|
||
try {
|
||
attempted++;
|
||
const res = await upsert({ data });
|
||
if (res.action === "create") creates++;
|
||
else if (res.action === "skip_existing") skipped_existing++;
|
||
} catch (e) {
|
||
errors++;
|
||
console.error(`ETL error ->`, e.message);
|
||
}
|
||
}
|
||
|
||
await db_connection_promise.end();
|
||
|
||
console.log(`Done. attempted=${attempted} skipped_existing=${skipped_existing} created=${creates} errors=${errors}`);
|
||
|
||
|
||
///////////////////////////////////////////////////////////////////////
|
||
// ENVOIE MAIL APRES EXECUTION
|
||
logger.info("MAIL MAJ ANNUAIRE PROF ENVOI AVANT", { numsession: sid });
|
||
var transporter = nodemailer.createTransport({
|
||
service: 'gmail',
|
||
auth: {
|
||
user: process.env.nodemailer_user,
|
||
pass: process.env.nodemailer_pass
|
||
}
|
||
});
|
||
var mail_objet = "ADUZUKI - CRON MAJ ANNUAIRE PROF";
|
||
mail_html = "<p>Pour l'administrateur de l'application ADUZUKI,</p><br><p>Ceci est le mail d'envoi pour confirmer l'exécution du Webhook de mise à jour de l'annuaire des profs qui se fait la nuit à 4h.</p>";
|
||
mail_html += "<p>- nombre de <strong>professeurs à ajouter</strong> : ";
|
||
mail_html += attempted;
|
||
mail_html += "</p>";
|
||
mail_html += "<p>- nombre de <strong>professeurs créés</strong> : ";
|
||
mail_html += creates;
|
||
mail_html += "</p>";
|
||
mail_html += "<p>- nombre de <strong>professeurs non ajoutés</strong> pour cause probable de doublon : ";
|
||
mail_html += skipped_existing;
|
||
mail_html += "</p>";
|
||
mail_html += "<p>- nombre d'<strong>erreurs</strong> : ";
|
||
mail_html += errors;
|
||
mail_html += "</p>";
|
||
|
||
mail_html += "<br><p>Julie Chaumard</p><p>Développeuse web de l'agence Parisweb.art</p>";
|
||
|
||
var mailOptions = {
|
||
from: 'afps.inscription@gmail.com',
|
||
to: "projet@parisweb.art",
|
||
subject: mail_objet,
|
||
html: mail_html
|
||
};
|
||
|
||
transporter.sendMail(mailOptions, function(error, info){
|
||
if (error) {
|
||
logger.info("error" + error, { numsession: sid });
|
||
} else {
|
||
logger.info("Email envoyé: " + info.response, { numsession: sid });
|
||
}
|
||
});
|
||
logger.info("MAIL MAJ ANNUAIRE PROF ENVOI APRES", { numsession: sid });
|
||
|
||
|
||
|
||
// --- Réponse HTTP IMMÉDIATE
|
||
const retour = {
|
||
ok: true,
|
||
attempted,
|
||
creates,
|
||
skipped_existing,
|
||
errors
|
||
};
|
||
|
||
logger.info("sync_prof_strapi terminé", { label: filename, numsession: sid });
|
||
res.status(200).json(retour);
|
||
|
||
} catch (e) {
|
||
logger.error("sync_prof_strapi FAILED", {
|
||
label: filename,
|
||
numsession: sid,
|
||
message: e.message,
|
||
stack: e.stack
|
||
});
|
||
// Réponse d’erreur JSON claire
|
||
return res.status(500).json({ ok: false, error: e.message });
|
||
}
|
||
//return { attempted, creates, skipped_existing, errors };
|
||
|
||
}
|
||
|
||
/////////////////////////////////////////////////
|
||
// EXPORT DE MODULE POUR QU'IL SOIT LU DANS LES AUTRES FICHIERS JS
|
||
/////////////////////////////////////////////////
|
||
|
||
module.exports = {
|
||
runSync_prof_strapi
|
||
} |