Nombre d'emails envoyés dans les dernières 24 heures : {{ $countEmailsLast24Hours }}
Doctors CNOM list in users table : {{ $doctorsCONPTListInUsersTable }}
Visualiser :
SELECT * FROM doctors WHERE email IN (SELECT email FROM users);
SELECT * FROM doctors WHERE phone IN (SELECT phone FROM users);
Supprimer :
DELETE FROM doctors
WHERE email IN (SELECT email FROM users);
Participation without existing event : {{ $particpationsWithoutEventCount }}
Visualiser :
SELECT COUNT(*) as count
FROM participants p
LEFT JOIN events e ON p.event_id = e.id
WHERE e.id IS NULL OR p.event_id IS NULL;
Participation without existing user : {{ $particpationsWithoutUserCount }}
Visualiser :
SELECT p.*
FROM participants p
LEFT JOIN users u ON p.user_id = u.id
WHERE u.id IS NULL AND p.user_id IS NOT NULL;
Supprimer :
DELETE p
FROM participants p
LEFT JOIN users u ON p.user_id = u.id
WHERE u.id IS NULL AND p.user_id IS NOT NULL;
Participation Workshop without existing participation : {{ $particpationWorkshopWithoutPartipationCount }}
Visualiser :
SELECT pw.*
FROM participant_workshops pw
LEFT JOIN participants p ON pw.participant_id = p.id
WHERE p.id IS NULL AND pw.participant_id IS NOT NULL;
Supprimer :
DELETE pw
FROM participant_workshops pw
LEFT JOIN participants p ON pw.participant_id = p.id
WHERE p.id IS NULL AND pw.participant_id IS NOT NULL;
Participation Workshop without existing Workshop : {{ $particpationWorkshopWithoutWorkshopCount }}
Visualiser :
SELECT pw.*
FROM participant_workshops pw
LEFT JOIN workshops w ON pw.workshop_id = w.id
WHERE w.id IS NULL AND pw.workshop_id IS NOT NULL;
Participation duplicated : {{ $particpationDuplicateCount }}
Visualiser :
SELECT user_id, event_id, COUNT(*) as count FROM participants GROUP BY user_id, event_id
HAVING COUNT(*) > 1;
Cleaning :
UPDATE users SET phone = REPLACE(phone, ' ', '');
SELECT phone, HEX(phone) FROM users WHERE phone LIKE '% %';
UPDATE users SET phone = REPLACE(phone, '00216', '') WHERE phone like '00216%';
UPDATE users SET phone = REPLACE(phone, '+216', '') WHERE phone like '+216%';
UPDATE users SET firstname = UPPER(firstname);
UPDATE users SET name = UPPER(name);
UPDATE users SET email = LOWER(email);
UPDATE users SET phone = LOWER(phone);
Users with the same email : {{ $particpationDuplicateEmailCount }}
Visualiser :
SELECT email, COUNT(*) AS user_count FROM users GROUP BY email HAVING COUNT(*) > 1;
Users with the same phone (not exposants and not fake) : {{ $particpationDuplicatePhoneCount }}
Visualiser les doublons :
SELECT phone, COUNT(*) AS user_count FROM users GROUP BY phone HAVING COUNT(*) > 1;
Visualiser les doublons, not fake, not empty and not exposant :
SELECT phone, COUNT(*) AS user_count FROM users WHERE phone IS NOT NULL AND phone != ''
AND phone NOT LIKE '%fake%' AND is_exposant = 0 GROUP BY phone HAVING COUNT(*) > 1 ORDER
BY `user_count` DESC
Set is_exposant :
Set :
UPDATE users
SET is_exposant = 1
WHERE id IN (
SELECT user_id
FROM participants
WHERE category = 'Exposant'
);
Users with the same firstname and lastname (not exposants) : {{ $particpationDuplicateFirstnameLastnameCount }}
Visualiser :
SELECT firstname, name, COUNT(*) AS user_count FROM users WHERE is_exposant = 0
GROUP BY firstname, name
HAVING COUNT(*) > 1 ORDER BY user_count DESC;
Documents without user :
Documents without participation :
Documents without event :
Documents without files :
Files without documents :