UPDATE `utilisateurs` SET `date_dernier_acces`= █► '2025-10-21 17:09:15' ◄█ ,
        `commentaire`= █► '/src/pages/admin/admin_temps.php?f=mois&dialog&debug' ◄█ ,
        `ip`= █► '193.56.80.188' ◄█ ,
        `session`= █► '508ff2befb02f2a6841fb71dd873fe77' ◄█
WHERE `id`= █► 2 ◄█ ;

SELECT
COALESCE(SUM(xp_total),
        0) AS xp_total
FROM ( -- XP provenant des défis
    SELECT SUM(`xp_total`) AS xp_total
    FROM `defis_scores`
    WHERE `id_utilisateur` = █► 2 ◄█
    UNION ALL -- XP provenant des résultats (avec catégorie vide)
SELECT SUM(`xp_total`) AS xp_total
FROM `resultats`
WHERE `categorie` = ''
        AND `id_utilisateur` = █► 2 ◄█
UNION ALL -- XP provenant des récompenses (seules les récompenses d'ID >= 100 sont prises en compte)
SELECT SUM(`valeur2`) AS xp_total
FROM `recompenses`
WHERE `id` >;= 100
        AND `id_utilisateur` = █► 2 ◄█ ) AS R1;

/* fonctions.php 03 */
SELECT
`id`,
        `id_utilisateur`,
        `id_participants`,
        IF((R2.`nbParticipants` = R1.`nb_Participants`)
        OR ( █► 172800 ◄█ -TIMESTAMPDIFF(SECOND,
        R1.`date_creation`,
        CAST( █► '2025-10-21 17:09:15' ◄█ AS DATETIME)) <;= 0),
        1,
        0) AS `defis_a_cloturer`
FROM `defis` AS R1
LEFT JOIN (
    SELECT `id_defi`,
        IFNULL(COUNT(*),
        0) AS `nbParticipants`
    FROM `defis_scores`
    GROUP BY `id_defi`) AS R2 ON R1.`id`=R2.`id_defi`
WHERE `etat`=1
HAVING `defis_a_cloturer`=1

/* 002a */
SELECT
'' AS `date_creation`,
        `id_utilisateur`,
        ROUND(SUM(`temps` / 60),
        0) AS `temps`,
        SUM(`NbR`) AS `NbR`,
        SUM(`NbV`) AS `NbV`,
        SUM(`NbQ`) AS `NbQ`,
        U.`nom`,
        U.`pseudo`,
        U.`photo`,
        IFNULL(GG.liste_groupes,
        '') AS `liste_groupes`,
        IFNULL(GG.liste_groupes_ids,
        '') AS `liste_groupes_ids`
FROM ( /* defis */
    SELECT DS.`id_utilisateur`,
        G.`id` AS iDG,
        G.`id_auteur` AS idG_Auteur,
        -1 AS idQ,
        -1 AS adQ_Auteur,
        SUM(`temps`) AS `temps`,
        COUNT(*) AS `NbR`,
        0 AS `NbV`,
        SUM(`nb_justes`)+SUM(`nb_faux`) AS `NbQ`
    FROM `defis_scores` AS DS
    LEFT JOIN `defis` AS D ON D.`id` = DS.`id_defi`
    LEFT JOIN `groupes_utilisateurs` AS GU ON GU.`id_utilisateur`=DS.`id_utilisateur`
    LEFT JOIN `groupes` AS G ON G.`id`=GU.`id_groupe`
    WHERE MONTH(DS.`date_creation`) = MONTH(CURDATE())
        AND DATE(DS.`date_creation`) BETWEEN DATE('2025-09-01')
        AND DATE('2026-08-01')
    GROUP BY DS.`id_utilisateur`
    UNION /* resultat -- */
SELECT R.`id_utilisateur`,
        G.`id` AS iDG,
        G.`id_auteur` AS idG_Auteur,
        Q.`id` AS idQ,
        Q.`id_auteur` AS adQ_Auteur,
        SUM(R.`temps`) AS `temps`,
        COUNT(*) AS `NbR`,
        0 AS `NbV`,
        SUM(R.`total`) AS `NbQ`
FROM `resultats` AS R
LEFT JOIN `questionnaires` AS Q ON Q.`id` = R.`id_questionnaire`
LEFT JOIN `groupes_utilisateurs` AS GU ON GU.`id_utilisateur`=R.`id_utilisateur`
LEFT JOIN `groupes` AS G ON G.`id`=GU.`id_groupe`
WHERE MONTH(R.`date_creation`) = MONTH(CURDATE())
        AND R.`categorie`=''
        AND DATE(R.`date_creation`) BETWEEN DATE('2025-09-01')
        AND DATE('2026-08-01')
GROUP BY R.`id_utilisateur`
UNION /* vote */
SELECT V.`id_utilisateur`,
        G.`id` AS iDG,
        G.`id_auteur` AS idG_Auteur,
        Q.`id` AS idQ,
        Q.`id_auteur` AS adQ_Auteur,
        SUM(V.`temps`) AS `temps`,
        0 AS `NbR`,
        COUNT(*) AS `NbV`,
        COUNT(*) AS `NbQ`
FROM `votes` AS V
LEFT JOIN `questionnaires` AS Q ON Q.`id` = V.`id_questionnaire`
LEFT JOIN `groupes_utilisateurs` AS GU ON GU.`id_utilisateur`=V.`id_utilisateur`
LEFT JOIN `groupes` AS G ON G.`id`=GU.`id_groupe`
WHERE MONTH(V.`date_creation`) = MONTH(CURDATE())
        AND DATE(V.`date_creation`) BETWEEN DATE('2025-09-01')
        AND DATE('2026-08-01')
GROUP BY V.`id_utilisateur` ) AS F
LEFT JOIN `utilisateurs` AS U ON F.`id_utilisateur`=U.`id` /* Recupere la liste des groupes associé a ce questionnaire de l'utilisateur */
LEFT JOIN (
    SELECT U.`id`,
        group_concat(cast(GU.`nom` AS char) separator ',
        ') AS `liste_groupes`,
        group_concat(cast(GU.`id_utilisateur` AS char) separator ',
        ') AS `liste_groupes_ids`
    FROM `utilisateurs` AS U
    LEFT JOIN (
        SELECT `id_utilisateur`,
        `nom`
        FROM `groupes_utilisateurs` AS GU
        LEFT JOIN `groupes` AS G ON G.`id`=GU.`id_groupe`
        WHERE IFNULL(G.`date_inactif`,
        -1)=-1 ) AS GU ON GU.`id_utilisateur`=U.`id`
    GROUP BY U.`id` ) AS GG ON GG.`id`=U.`id`
WHERE U.`id_compte`= █► 'LOMI' ◄█
        AND (NOT `acces` <; █► 3 ◄█ )
        AND U.`actif`=1
        AND U.`archiver`=0
        AND ( idG_Auteur= █► 2 ◄█
        OR idG IN (
        SELECT `id`
        FROM `groupes` AS G
        WHERE ( G.`id_auteur`= █► 2 ◄█
        OR G.`id` IN (
                SELECT `id_groupe`
                FROM `equipes_groupes`
                WHERE `id_equipe` IN (
                    SELECT `id_equipe`
                    FROM `equipes_auteurs`
                    WHERE `id_auteur`= █► 2 ◄█ ) ) ) ) )
GROUP BY `id_utilisateur`
ORDER BY U.`nom` ASC

/* 002c */
SELECT
`annee`,
        `date_creation_origine`,
        `date_creation2`,
        ROUND(SUM(`temps` / 60),
        0) AS `temps`,
        SUM(`NbR`) AS `NbR`,
        SUM(`NbV`) AS `NbV`,
        SUM(`NbQ`) AS `NbQ`,
        IFNULL(GG.liste_groupes,
        '') AS `liste_groupes`,
        IFNULL(GG.liste_groupes_ids,
        '') AS `liste_groupes_ids`
FROM ( /* defis */
    SELECT YEAR(DS.`date_creation`) AS `annee`,
        DS.`id_utilisateur` AS `idu`,
        DS.`date_creation` AS `date_creation_origine`,
        MONTH(DS.`date_creation`) AS `date_creation2`,
        DS.`id_utilisateur`,
        G.`id` AS iDG,
        G.`id_auteur` AS idG_Auteur,
        -1 AS idQ,
        -1 AS adQ_Auteur,
        SUM(`temps`) AS `temps`,
        COUNT(*) AS `NbR`,
        0 AS `NbV`,
        SUM(`nb_justes`)+SUM(`nb_faux`) AS `NbQ`
    FROM `defis_scores` AS DS
    LEFT JOIN `defis` AS D ON D.`id` = DS.`id_defi`
    LEFT JOIN `groupes_utilisateurs` AS GU ON GU.`id_utilisateur`=DS.`id_utilisateur`
    LEFT JOIN `groupes` AS G ON G.`id`=GU.`id_groupe`
    WHERE DATE(DS.`date_creation`) BETWEEN DATE('2025-09-01')
        AND DATE('2026-08-01')
    GROUP BY `id_utilisateur`,
        MONTH(DS.`date_creation`)
    UNION /* resultat -- */
SELECT YEAR(R.`date_creation`) AS `annee`,
        R.`id_utilisateur` AS `idu`,
        R.`date_creation` AS `date_creation_origine`,
        MONTH(R.`date_creation`) AS `date_creation2`,
        R.`id_utilisateur`,
        G.`id` AS iDG,
        G.`id_auteur` AS idG_Auteur,
        Q.`id` AS idQ,
        Q.`id_auteur` AS adQ_Auteur,
        SUM(R.`temps`) AS `temps`,
        COUNT(*) AS `NbR`,
        0 AS `NbV`,
        SUM(R.`total`) AS `NbQ`
FROM `resultats` AS R
LEFT JOIN `questionnaires` AS Q ON Q.`id` = R.`id_questionnaire`
LEFT JOIN `groupes_utilisateurs` AS GU ON GU.`id_utilisateur`=R.`id_utilisateur`
LEFT JOIN `groupes` AS G ON G.`id`=GU.`id_groupe`
WHERE DATE(R.`date_creation`) BETWEEN DATE('2025-09-01')
        AND DATE('2026-08-01')
        AND R.`categorie`=''
GROUP BY `id_utilisateur`,
        MONTH(R.`date_creation`)
UNION /* vote */
SELECT YEAR(V.`date_creation`) AS `annee`,
        V.`id_utilisateur` AS `idu`,
        V.`date_creation` AS `date_creation_origine`,
        MONTH(V.`date_creation`) AS `date_creation2`,
        V.`id_utilisateur`,
        G.`id` AS iDG,
        G.`id_auteur` AS idG_Auteur,
        Q.`id` AS idQ,
        Q.`id_auteur` AS adQ_Auteur,
        SUM(V.`temps`) AS `temps`,
        0 AS `NbR`,
        COUNT(*) AS `NbV`,
        COUNT(*) AS `NbQ`
FROM `votes` AS V
LEFT JOIN `questionnaires` AS Q ON Q.`id` = V.`id_questionnaire`
LEFT JOIN `groupes_utilisateurs` AS GU ON GU.`id_utilisateur`=V.`id_utilisateur`
LEFT JOIN `groupes` AS G ON G.`id`=GU.`id_groupe`
WHERE DATE(V.`date_creation`) BETWEEN DATE('2025-09-01')
        AND DATE('2026-08-01')
GROUP BY `id_utilisateur`,
        MONTH(V.`date_creation`) ) AS F
LEFT JOIN `utilisateurs` AS U ON F.`id_utilisateur`=U.`id` /* Recupere la liste des groupes associé a ce questionnaire de l'utilisateur */
LEFT JOIN (
    SELECT U.`id`,
        group_concat(cast(GU.`nom` AS char) separator ',
        ') AS `liste_groupes`,
        group_concat(cast(GU.`id_utilisateur` AS char) separator ',
        ') AS `liste_groupes_ids`
    FROM `utilisateurs` AS U
    LEFT JOIN (
        SELECT `id_utilisateur`,
        `nom`
        FROM `groupes_utilisateurs` AS GU
        LEFT JOIN `groupes` AS G ON G.`id`=GU.`id_groupe`
        WHERE IFNULL(G.`date_inactif`,
        -1)=-1 ) AS GU ON GU.`id_utilisateur`=U.`id`
    GROUP BY U.`id` ) AS GG ON GG.`id`=U.`id`
WHERE U.`id_compte`= █► 'LOMI' ◄█
        AND (NOT `acces` <; █► 3 ◄█ )
        AND U.`actif`=1
        AND U.`archiver`=0
        AND ( idG_Auteur= █► 2 ◄█
        OR idG IN (
        SELECT `id`
        FROM `groupes` AS G
        WHERE ( G.`id_auteur`= █► 2 ◄█
        OR G.`id` IN (
                SELECT `id_groupe`
                FROM `equipes_groupes`
                WHERE `id_equipe` IN (
                    SELECT `id_equipe`
                    FROM `equipes_auteurs`
                    WHERE `id_auteur`= █► 2 ◄█ ) ) ) ) )
GROUP BY `date_creation2`
ORDER BY `annee` DESC,
        `date_creation2` DESC

SELECT
`valeur1`
FROM `recompenses`
WHERE `id`=0
        AND `id_utilisateur`= █► 2 ◄█ ;

SELECT
N.`id`,
        N.`date_creation`,
        N.`id_utilisateur`,
        N.`id_auteur`,
        N.`icon_id`,
        N.`message`,
        N.`valeur`,
        U.`photo`,
        U.`nom`,
        U.`pseudo`
FROM `notifications` AS N
LEFT JOIN `utilisateurs` AS U ON N.`id_auteur`=U.`id`
WHERE N.`id_utilisateur`= █► 2 ◄█
        AND N.`etat`=1
ORDER BY N.`id` ASC;

SELECT
IFNULL(COUNT(*),
        0) AS `nbDefi`
FROM (
    SELECT TIMESTAMPDIFF(SECOND,
        D.`date_creation`,
        CAST( █► '2025-10-21 17:09:15' ◄█ AS DATETIME)) AS `temps`,
        D.`etat`
    FROM `defis` AS D
    LEFT JOIN `utilisateurs` AS U ON U.`id`=D.`id_auteur`
    LEFT JOIN (
        SELECT `id_defi`,
        COUNT(*) AS `deja_joue`
        FROM `defis_scores`
        WHERE `id_utilisateur`= █► 2 ◄█
        GROUP BY `id_defi` ) AS DJ ON DJ.`id_defi`=D.`id`
    WHERE (FIND_IN_SET( █► 2 ◄█ ,
        `id_participants`)
        OR `id_utilisateur`= █► 2 ◄█
        OR `id_participants` = '** TOUS **')
        AND (U.`actif`=1
        AND U.`actif_compte`=1
        AND `id_participants` <;>; '** seul **'
        AND U.`id_compte`= █► 'LOMI' ◄█ )
        AND D.`etat` = 1
        AND DJ.`deja_joue` IS NULL
    HAVING `temps` <; █► 345600 ◄█ ) AS DEFI

SELECT
IFNULL(COUNT(*),
        0) AS `nbMessages`
FROM (
    SELECT R1.`id` ,
        R1.`id_compte`,
        `id_groupe` ,
        R1.`id_auteur`,
        R2.`photo`,
        `id_destinataire` ,
        `message`,
        R1.`date_creation`,
        `lu`,
        R1.`actif`,
        IFNULL(`utilisateurs`.`nom`,
        '') AS `nom_destinataire`,
        IFNULL(R2.`nom`,
        '') AS `nom_auteur`,
        IFNULL(`groupes`.`nom`,
        '') AS `nom_groupe`
    FROM (
        SELECT `id` ,
        `id_compte`,
        IFNULL(`id_groupe`,
        -1) AS `id_groupe` ,
        `id_auteur`,
        IFNULL(`id_destinataire`,
        -1) AS `id_destinataire` ,
        `message`,
        `date_creation`,
        `lu`,
        `actif`
        FROM `messages` ) AS R1
    LEFT JOIN `utilisateurs` ON (R1.`id_destinataire`=`utilisateurs`.`id`)
    LEFT JOIN `utilisateurs` AS R2 ON (R1.`id_auteur`= R2.`id`)
    LEFT JOIN `groupes` ON (R1.`id_groupe`=`groupes`.`id`)
    WHERE R1.`id_compte`= █► 'LOMI' ◄█
        AND ( R1.`id_destinataire`= █► 2 ◄█
        OR ( R1.`id_groupe` IN (
                SELECT `id_groupe`
                FROM `groupes_utilisateurs`
                WHERE `id_utilisateur`= █► 2 ◄█
                UNION SELECT -1 )
        AND R1.`id_destinataire` IN (-1,
        █► 2 ◄█ ) ) )
ORDER BY R1.`date_creation` DESC,
        R1.`id` DESC
LIMIT 20 ) AS R1

SELECT
COUNT(*) AS `nbPartieEnCours`
FROM (
    SELECT COUNT(*) AS `nbQ`
    FROM `votes`
    WHERE `id_utilisateur`= █► 2 ◄█
    GROUP BY `id_questionnaire` ) AS nbQ

SELECT
`id_compte`,
        COUNT(*) AS nbConnectes
FROM `utilisateurs`
WHERE `date_dernier_acces` >; █► '2025-10-21 17:04:15' ◄█
GROUP BY `id_compte` ;