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);
|
|
});
|