571 lines
20 KiB
JavaScript
571 lines
20 KiB
JavaScript
import express from 'express';
|
||
import cors from 'cors';
|
||
import sql from 'mssql';
|
||
import axios from 'axios';
|
||
|
||
const app = express();
|
||
const PORT = 3000;
|
||
|
||
app.use(cors({ origin: '*' }));
|
||
app.use(express.json());
|
||
|
||
// Configuration Azure AD
|
||
const AZURE_CONFIG = {
|
||
tenantId: '9840a2a0-6ae1-4688-b03d-d2ec291be0f9',
|
||
clientId: '4bb4cc24-bac3-427c-b02c-5d14fc67b561',
|
||
clientSecret: 'gvf8Q~545Bafn8yYsgjW~QG_P1lpzaRe6gJNgb2t',
|
||
groupId: 'c1ea877c-6bca-4f47-bfad-f223640813a0'
|
||
};
|
||
|
||
// Configuration SQL Server
|
||
const dbConfig = {
|
||
server: '192.168.0.3',
|
||
user: 'gta_app',
|
||
password: 'GTA2025!Secure',
|
||
database: 'GTA',
|
||
port: 1433,
|
||
options: {
|
||
encrypt: true,
|
||
trustServerCertificate: true,
|
||
enableArithAbort: true,
|
||
connectTimeout: 60000,
|
||
requestTimeout: 60000
|
||
},
|
||
pool: {
|
||
max: 10,
|
||
min: 0,
|
||
idleTimeoutMillis: 30000
|
||
}
|
||
};
|
||
|
||
// Créer le pool de connexions
|
||
const pool = new sql.ConnectionPool(dbConfig);
|
||
|
||
// Connexion au démarrage
|
||
pool.connect()
|
||
.then(() => {
|
||
console.log('✅ Connecté à SQL Server');
|
||
console.log(` Base: ${dbConfig.database}@${dbConfig.server}`);
|
||
})
|
||
.catch(err => {
|
||
console.error('❌ Erreur connexion SQL Server:', err.message);
|
||
});
|
||
|
||
// ========================================
|
||
// WRAPPER POUR COMPATIBILITÉ (style MySQL)
|
||
// ========================================
|
||
pool.query = async function (queryText, params = []) {
|
||
if (!pool.connected) {
|
||
await pool.connect();
|
||
}
|
||
|
||
const request = pool.request();
|
||
|
||
// Ajouter les paramètres
|
||
params.forEach((value, index) => {
|
||
request.input(`param${index}`, value);
|
||
});
|
||
|
||
// Remplacer ? par @param0, @param1, etc.
|
||
let parameterizedQuery = queryText;
|
||
let paramIndex = 0;
|
||
parameterizedQuery = parameterizedQuery.replace(/\?/g, () => `@param${paramIndex++}`);
|
||
|
||
// Conversion LIMIT → TOP
|
||
parameterizedQuery = parameterizedQuery.replace(
|
||
/LIMIT\s+(\d+)/gi,
|
||
(match, limit) => {
|
||
return parameterizedQuery.includes('SELECT')
|
||
? parameterizedQuery.replace(/SELECT/i, `SELECT TOP ${limit}`)
|
||
: '';
|
||
}
|
||
);
|
||
|
||
const result = await request.query(parameterizedQuery);
|
||
return result.recordset || [];
|
||
};
|
||
|
||
// ========================================
|
||
// 🔑 FONCTION TOKEN MICROSOFT GRAPH
|
||
// ========================================
|
||
async function getGraphToken() {
|
||
try {
|
||
const params = new URLSearchParams({
|
||
grant_type: 'client_credentials',
|
||
client_id: AZURE_CONFIG.clientId,
|
||
client_secret: AZURE_CONFIG.clientSecret,
|
||
scope: 'https://graph.microsoft.com/.default'
|
||
});
|
||
|
||
const response = await axios.post(
|
||
`https://login.microsoftonline.com/${AZURE_CONFIG.tenantId}/oauth2/v2.0/token`,
|
||
params.toString(),
|
||
{ headers: { 'Content-Type': 'application/x-www-form-urlencoded' } }
|
||
);
|
||
|
||
return response.data.access_token;
|
||
} catch (error) {
|
||
console.error('❌ Erreur obtention token:', error.message);
|
||
return null;
|
||
}
|
||
}
|
||
|
||
// ========================================
|
||
// 🔄 FONCTION SYNCHRONISATION ENTRA ID
|
||
// ========================================
|
||
async function syncEntraIdUsers() {
|
||
const syncResults = {
|
||
processed: 0,
|
||
inserted: 0,
|
||
updated: 0,
|
||
deactivated: 0,
|
||
errors: []
|
||
};
|
||
|
||
try {
|
||
console.log('\n🔄 === DÉBUT SYNCHRONISATION ENTRA ID ===');
|
||
|
||
// 1️⃣ Obtenir le token
|
||
const accessToken = await getGraphToken();
|
||
if (!accessToken) {
|
||
console.error('❌ Impossible d\'obtenir le token');
|
||
return syncResults;
|
||
}
|
||
console.log('✅ Token obtenu');
|
||
|
||
// 2️⃣ Récupérer le groupe
|
||
const groupResponse = await axios.get(
|
||
`https://graph.microsoft.com/v1.0/groups/${AZURE_CONFIG.groupId}?$select=id,displayName`,
|
||
{ headers: { Authorization: `Bearer ${accessToken}` } }
|
||
);
|
||
const groupName = groupResponse.data.displayName;
|
||
console.log(`📋 Groupe : ${groupName}`);
|
||
|
||
// 3️⃣ Récupérer tous les membres avec pagination
|
||
let allAzureMembers = [];
|
||
let nextLink = `https://graph.microsoft.com/v1.0/groups/${AZURE_CONFIG.groupId}/members?$select=id,givenName,surname,mail,department,jobTitle,officeLocation,accountEnabled&$top=999`;
|
||
|
||
console.log('📥 Récupération des membres...');
|
||
while (nextLink) {
|
||
const membersResponse = await axios.get(nextLink, {
|
||
headers: { Authorization: `Bearer ${accessToken}` }
|
||
});
|
||
allAzureMembers = allAzureMembers.concat(membersResponse.data.value);
|
||
nextLink = membersResponse.data['@odata.nextLink'];
|
||
|
||
if (nextLink) {
|
||
console.log(` 📄 ${allAzureMembers.length} membres récupérés...`);
|
||
}
|
||
}
|
||
|
||
console.log(`✅ ${allAzureMembers.length} membres trouvés`);
|
||
|
||
// 4️⃣ Filtrer les membres valides
|
||
const validMembers = allAzureMembers.filter(m => {
|
||
if (!m.mail || m.mail.trim() === '') return false;
|
||
if (m.accountEnabled === false) return false;
|
||
|
||
const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
|
||
return emailRegex.test(m.mail);
|
||
});
|
||
|
||
console.log(`✅ ${validMembers.length} membres valides`);
|
||
|
||
// 5️⃣ Traitement avec transaction
|
||
const transaction = new sql.Transaction(pool);
|
||
await transaction.begin();
|
||
|
||
try {
|
||
const azureEmails = new Set();
|
||
validMembers.forEach(m => {
|
||
azureEmails.add(m.mail.toLowerCase().trim());
|
||
});
|
||
|
||
console.log('\n📝 Traitement des utilisateurs...');
|
||
|
||
// 6️⃣ Pour chaque membre
|
||
for (const m of validMembers) {
|
||
try {
|
||
const emailClean = m.mail.toLowerCase().trim();
|
||
syncResults.processed++;
|
||
|
||
// Vérifier existence
|
||
const request = new sql.Request(transaction);
|
||
request.input('email', sql.NVarChar, emailClean);
|
||
|
||
const result = await request.query(`
|
||
SELECT id, email, entraUserId, actif
|
||
FROM CollaborateurAD
|
||
WHERE LOWER(email) = LOWER(@email)
|
||
`);
|
||
|
||
if (result.recordset.length > 0) {
|
||
// MISE À JOUR
|
||
const updateRequest = new sql.Request(transaction);
|
||
updateRequest.input('entraUserId', sql.NVarChar, m.id);
|
||
updateRequest.input('prenom', sql.NVarChar, m.givenName || '');
|
||
updateRequest.input('nom', sql.NVarChar, m.surname || '');
|
||
updateRequest.input('departement', sql.NVarChar, m.department || '');
|
||
updateRequest.input('fonction', sql.NVarChar, m.jobTitle || '');
|
||
updateRequest.input('campus', sql.NVarChar, m.officeLocation || '');
|
||
updateRequest.input('email', sql.NVarChar, emailClean);
|
||
|
||
await updateRequest.query(`
|
||
UPDATE CollaborateurAD
|
||
SET
|
||
entraUserId = @entraUserId,
|
||
prenom = @prenom,
|
||
nom = @nom,
|
||
departement = @departement,
|
||
fonction = @fonction,
|
||
campus = @campus,
|
||
actif = 1
|
||
WHERE LOWER(email) = LOWER(@email)
|
||
`);
|
||
|
||
syncResults.updated++;
|
||
console.log(` ✓ Mis à jour : ${emailClean}`);
|
||
|
||
} else {
|
||
// INSERTION
|
||
const insertRequest = new sql.Request(transaction);
|
||
insertRequest.input('entraUserId', sql.NVarChar, m.id);
|
||
insertRequest.input('prenom', sql.NVarChar, m.givenName || '');
|
||
insertRequest.input('nom', sql.NVarChar, m.surname || '');
|
||
insertRequest.input('email', sql.NVarChar, emailClean);
|
||
insertRequest.input('departement', sql.NVarChar, m.department || '');
|
||
insertRequest.input('fonction', sql.NVarChar, m.jobTitle || '');
|
||
insertRequest.input('campus', sql.NVarChar, m.officeLocation || '');
|
||
|
||
await insertRequest.query(`
|
||
INSERT INTO CollaborateurAD
|
||
(entraUserId, prenom, nom, email, departement, fonction, campus, role, SocieteId, actif, dateCreation, TypeContrat)
|
||
VALUES (@entraUserId, @prenom, @nom, @email, @departement, @fonction, @campus, 'Collaborateur', 1, 1, GETDATE(), '37h')
|
||
`);
|
||
|
||
syncResults.inserted++;
|
||
console.log(` ✓ Créé : ${emailClean}`);
|
||
}
|
||
|
||
} catch (userError) {
|
||
syncResults.errors.push({
|
||
email: m.mail,
|
||
error: userError.message
|
||
});
|
||
console.error(` ❌ Erreur ${m.mail}:`, userError.message);
|
||
}
|
||
}
|
||
|
||
// 7️⃣ DÉSACTIVATION des comptes absents
|
||
console.log('\n🔍 Désactivation des comptes obsolètes...');
|
||
|
||
if (azureEmails.size > 0) {
|
||
const activeEmailsList = Array.from(azureEmails).map(e => `'${e}'`).join(',');
|
||
|
||
const deactivateRequest = new sql.Request(transaction);
|
||
const deactivateResult = await deactivateRequest.query(`
|
||
UPDATE CollaborateurAD
|
||
SET actif = 0
|
||
WHERE
|
||
email IS NOT NULL
|
||
AND email != ''
|
||
AND LOWER(email) NOT IN (${activeEmailsList})
|
||
AND actif = 1
|
||
`);
|
||
|
||
syncResults.deactivated = deactivateResult.rowsAffected[0];
|
||
console.log(` ✓ ${syncResults.deactivated} compte(s) désactivé(s)`);
|
||
}
|
||
|
||
await transaction.commit();
|
||
|
||
console.log('\n📊 === RÉSUMÉ ===');
|
||
console.log(` Groupe: ${groupName}`);
|
||
console.log(` Total Entra: ${allAzureMembers.length}`);
|
||
console.log(` Valides: ${validMembers.length}`);
|
||
console.log(` Traités: ${syncResults.processed}`);
|
||
console.log(` Créés: ${syncResults.inserted}`);
|
||
console.log(` Mis à jour: ${syncResults.updated}`);
|
||
console.log(` Désactivés: ${syncResults.deactivated}`);
|
||
console.log(` Erreurs: ${syncResults.errors.length}`);
|
||
|
||
} catch (error) {
|
||
await transaction.rollback();
|
||
throw error;
|
||
}
|
||
|
||
} catch (error) {
|
||
console.error('\n❌ ERREUR SYNCHRONISATION:', error.message);
|
||
}
|
||
|
||
return syncResults;
|
||
}
|
||
|
||
// ========================================
|
||
// 📡 ROUTES API
|
||
// ========================================
|
||
|
||
// Route test connexion
|
||
app.get('/api/db-status', async (req, res) => {
|
||
try {
|
||
const result = await pool.query('SELECT COUNT(*) AS count FROM CollaborateurAD', []);
|
||
const collaboratorCount = result[0]?.count || 0;
|
||
|
||
res.json({
|
||
success: true,
|
||
message: 'Connexion SQL Server OK',
|
||
collaboratorCount,
|
||
});
|
||
} catch (error) {
|
||
console.error('Erreur connexion:', error);
|
||
res.status(500).json({
|
||
success: false,
|
||
message: 'Erreur connexion base',
|
||
error: error.message,
|
||
});
|
||
}
|
||
});
|
||
|
||
// Route sync unitaire
|
||
app.post('/api/initial-sync', async (req, res) => {
|
||
try {
|
||
const email = (req.body.mail || req.body.userPrincipalName)?.toLowerCase().trim();
|
||
const entraUserId = req.body.id;
|
||
|
||
if (!email) {
|
||
return res.json({ success: false, message: 'Email manquant' });
|
||
}
|
||
|
||
console.log(`\n🔄 Sync utilisateur : ${email}`);
|
||
|
||
const transaction = new sql.Transaction(pool);
|
||
await transaction.begin();
|
||
|
||
try {
|
||
// Vérifier existence
|
||
const checkRequest = new sql.Request(transaction);
|
||
checkRequest.input('email', sql.NVarChar, email);
|
||
|
||
const existing = await checkRequest.query(`
|
||
SELECT id, email, actif
|
||
FROM CollaborateurAD
|
||
WHERE LOWER(email) = LOWER(@email)
|
||
`);
|
||
|
||
if (existing.recordset.length > 0) {
|
||
// UPDATE
|
||
const updateRequest = new sql.Request(transaction);
|
||
updateRequest.input('collaborateurADId', sql.NVarChar, entraUserId);
|
||
updateRequest.input('prenom', sql.NVarChar, req.body.givenName || '');
|
||
updateRequest.input('nom', sql.NVarChar, req.body.surname || '');
|
||
updateRequest.input('departement', sql.NVarChar, req.body.department || '');
|
||
updateRequest.input('fonction', sql.NVarChar, req.body.jobTitle || '');
|
||
updateRequest.input('campus', sql.NVarChar, req.body.officeLocation || '');
|
||
updateRequest.input('email', sql.NVarChar, email);
|
||
updateRequest.input('dateMaj', sql.DateTime, new Date());
|
||
|
||
await updateRequest.query(`
|
||
UPDATE CollaborateurAD
|
||
SET
|
||
CollaborateurADId = @collaborateurADId,
|
||
prenom = @prenom,
|
||
nom = @nom,
|
||
departement = @departement,
|
||
fonction = @fonction,
|
||
campus = @campus,
|
||
actif = 1,
|
||
dateMiseAJour = @dateMaj
|
||
WHERE LOWER(email) = LOWER(@email)
|
||
`);
|
||
|
||
console.log(` ✅ Mis à jour : ${email}`);
|
||
} else {
|
||
// INSERT
|
||
const insertRequest = new sql.Request(transaction);
|
||
insertRequest.input('collaborateurADId', sql.NVarChar, entraUserId);
|
||
insertRequest.input('prenom', sql.NVarChar, req.body.givenName || '');
|
||
insertRequest.input('nom', sql.NVarChar, req.body.surname || '');
|
||
insertRequest.input('email', sql.NVarChar, email);
|
||
insertRequest.input('departement', sql.NVarChar, req.body.department || '');
|
||
insertRequest.input('fonction', sql.NVarChar, req.body.jobTitle || '');
|
||
insertRequest.input('campus', sql.NVarChar, req.body.officeLocation || '');
|
||
insertRequest.input('dateCreation', sql.DateTime, new Date());
|
||
insertRequest.input('dateMaj', sql.DateTime, new Date());
|
||
|
||
await insertRequest.query(`
|
||
INSERT INTO CollaborateurAD
|
||
(CollaborateurADId, prenom, nom, email, departement, fonction, campus, service, societe, actif, dateCreation, dateMiseAJour)
|
||
VALUES (@collaborateurADId, @prenom, @nom, @email, @departement, @fonction, @campus, NULL, NULL, 1, @dateCreation, @dateMaj)
|
||
`);
|
||
|
||
console.log(` ✅ Créé : ${email}`);
|
||
}
|
||
|
||
// Récupérer données
|
||
const getUserRequest = new sql.Request(transaction);
|
||
getUserRequest.input('email', sql.NVarChar, email);
|
||
|
||
const userData = await getUserRequest.query(`
|
||
SELECT id as localUserId, email, prenom, nom, fonction, departement
|
||
FROM CollaborateurAD
|
||
WHERE LOWER(email) = LOWER(@email)
|
||
`);
|
||
|
||
await transaction.commit();
|
||
|
||
if (userData.recordset.length === 0) {
|
||
throw new Error('Utilisateur introuvable après sync');
|
||
}
|
||
|
||
res.json({
|
||
success: true,
|
||
message: 'Sync réussie',
|
||
localUserId: userData.recordset[0].localUserId,
|
||
user: userData.recordset[0]
|
||
});
|
||
|
||
} catch (error) {
|
||
await transaction.rollback();
|
||
throw error;
|
||
}
|
||
|
||
} catch (error) {
|
||
console.error('❌ Erreur sync:', error);
|
||
res.json({
|
||
success: false,
|
||
message: error.message
|
||
});
|
||
}
|
||
});
|
||
|
||
// Route check groups
|
||
app.post('/api/check-user-groups', async (req, res) => {
|
||
try {
|
||
const { userPrincipalName } = req.body;
|
||
|
||
if (!userPrincipalName) {
|
||
return res.json({ authorized: false, message: 'Email manquant' });
|
||
}
|
||
|
||
const users = await pool.query(
|
||
'SELECT id, email, prenom, nom, actif FROM CollaborateurAD WHERE email = ?',
|
||
[userPrincipalName]
|
||
);
|
||
|
||
if (users.length > 0) {
|
||
const user = users[0];
|
||
|
||
if (user.actif === 0) {
|
||
return res.json({ authorized: false, message: 'Compte désactivé' });
|
||
}
|
||
|
||
return res.json({
|
||
authorized: true,
|
||
localUserId: user.id,
|
||
user: user
|
||
});
|
||
}
|
||
|
||
res.json({
|
||
authorized: true,
|
||
message: 'Sera créé au login'
|
||
});
|
||
|
||
} catch (error) {
|
||
console.error('❌ Erreur check:', error);
|
||
res.json({ authorized: false, error: error.message });
|
||
}
|
||
});
|
||
|
||
// Route sync complète manuelle
|
||
app.post('/api/sync-all', async (req, res) => {
|
||
try {
|
||
console.log('🚀 Sync complète manuelle...');
|
||
const results = await
|
||
IdUsers();
|
||
|
||
res.json({
|
||
success: true,
|
||
message: 'Sync terminée',
|
||
stats: results
|
||
});
|
||
} catch (error) {
|
||
res.status(500).json({
|
||
success: false,
|
||
message: error.message
|
||
});
|
||
}
|
||
});
|
||
|
||
// Route diagnostic
|
||
app.get('/api/diagnostic-sync', async (req, res) => {
|
||
try {
|
||
const totalDB = await pool.query(
|
||
'SELECT COUNT(*) as total, SUM(CASE WHEN actif = 1 THEN 1 ELSE 0 END) as actifs FROM CollaborateurAD',
|
||
[]
|
||
);
|
||
|
||
const sansEmail = await pool.query(
|
||
'SELECT COUNT(*) as total FROM CollaborateurAD WHERE email IS NULL OR email = \'\'',
|
||
[]
|
||
);
|
||
|
||
const derniers = await pool.query(
|
||
'SELECT TOP 10 id, prenom, nom, email, CollaborateurADId, actif FROM CollaborateurAD ORDER BY id DESC',
|
||
[]
|
||
);
|
||
|
||
// Test Entra
|
||
let entraStatus = { connected: false };
|
||
try {
|
||
const token = await getGraphToken();
|
||
if (token) {
|
||
const groupResponse = await axios.get(
|
||
`https://graph.microsoft.com/v1.0/groups/${AZURE_CONFIG.groupId}?$select=id,displayName`,
|
||
{ headers: { Authorization: `Bearer ${token}` } }
|
||
);
|
||
entraStatus = {
|
||
connected: true,
|
||
groupName: groupResponse.data.displayName
|
||
};
|
||
}
|
||
} catch (err) {
|
||
entraStatus.error = err.message;
|
||
}
|
||
|
||
res.json({
|
||
success: true,
|
||
database: {
|
||
total: totalDB[0]?.total || 0,
|
||
actifs: totalDB[0]?.actifs || 0,
|
||
sansEmail: sansEmail[0]?.total || 0
|
||
},
|
||
entraId: entraStatus,
|
||
derniers_utilisateurs: derniers
|
||
});
|
||
|
||
} catch (error) {
|
||
res.status(500).json({
|
||
success: false,
|
||
error: error.message
|
||
});
|
||
}
|
||
});
|
||
|
||
// ========================================
|
||
// 🚀 DÉMARRAGE
|
||
// ========================================
|
||
app.listen(PORT, "0.0.0.0", async () => {
|
||
console.log("✅ ==========================================");
|
||
console.log(" SERVEUR TEST DÉMARRÉ");
|
||
console.log(" Port:", PORT);
|
||
console.log(` Base SQL Server: ${dbConfig.database}@${dbConfig.server}`);
|
||
console.log("==========================================");
|
||
|
||
// Sync auto après 5 secondes
|
||
setTimeout(async () => {
|
||
console.log("\n🚀 Sync Entra ID automatique...");
|
||
await syncEntraIdUsers();
|
||
}, 5000);
|
||
});
|