CMS-CSE/core/class/lottery.class.php

<?php

class lottery
{
    public static function getLotterys()
    {
        db::query("SELECT "
            . "" . DB_T_LOTTERY . ".id, "
            . "" . DB_T_LOTTERY . ".md5, "
            . "" . DB_T_LOTTERY . ".titre, "
            . "" . DB_T_LOTTERY . ".description, "
            . "" . DB_T_LOTTERY . ".sortDate, "
            . "COUNT(" . DB_T_LOTTERY_INSCRITS . ".login) AS m_inscrits, "
            . "SUM(IF(" . DB_T_LOTTERY_INSCRITS . ".valide = 1, 1, 0)) AS m_eligible, "
            . "SUM(IF(" . DB_T_LOTTERY_INSCRITS . ".valide = 0, 1, 0)) AS m_ineligible, "
            . "COUNT(" . DB_T_LOTTERY_INSCRITS . ".selected) AS m_selected, "
            . "" . DB_T_LOTTERY . ".cree, "
            . "" . DB_T_LOTTERY . ".maj, "
            . "CONCAT (" . DB_T_USER . ".prenom, ' ', " . DB_T_USER . ".nom) AS 'user' "
            . "FROM " . DB_T_LOTTERY . " "
            . "LEFT JOIN " . DB_T_LOTTERY_INSCRITS . " ON " . DB_T_LOTTERY_INSCRITS . ".id_lottery = " . DB_T_LOTTERY . ".id "
            . "INNER JOIN " . DB_T_USER . " ON " . DB_T_LOTTERY . ".id_user = " . DB_T_USER . ".id "
            . "GROUP BY 1");
        return db::resultset();
    }

    public static function getInscription(int $_id){
        db::query("SELECT "
                . "" . DB_T_LOTTERY_INSCRITS . ".id_lottery, "
                . "" . DB_T_LOTTERY_INSCRITS . ".id_salarie, "
                . "" . DB_T_LOTTERY_INSCRITS . ".id_presta, "
                . "" . DB_T_LOTTERY_INSCRITS . ".id_dossier, "
                . "IF(" . DB_T_SALARIES . ".actif = 1, 'ACTIF', IF(" . DB_T_SALARIES . ".actif = 0, 'INACTIF', 'ABSENT')) AS actif , "
                . "UCASE(" . DB_T_LOTTERY_INSCRITS . ".login) AS login, "
                . "UCASE(" . DB_T_LOTTERY_INSCRITS . ".prenom) AS prenom, "
                . "UCASE(" . DB_T_LOTTERY_INSCRITS . ".nom) AS nom, "
                . "IF(" . DB_T_LOTTERY_INSCRITS . ".valide = 1, 'OUI', 'NON') AS valide, "
                . "" . DB_T_LOTTERY_INSCRITS . ".selected, "
                . "" . DB_T_LOTTERY_INSCRITS . ".cree, "
                . "" . DB_T_LOTTERY_INSCRITS . ".id_user, "
                . "CONCAT(" . DB_T_USER . ".prenom, ' ', " . DB_T_USER . ".nom ) AS admin, "
                . "" . DB_T_LOTTERY_INSCRITS . ".cree "
                . "FROM " . DB_T_LOTTERY_INSCRITS . " "
                . "INNER JOIN " . DB_T_USER . " ON " . DB_T_LOTTERY_INSCRITS . ".id_user = " . DB_T_USER . ".id "
                . "LEFT JOIN " . DB_T_SALARIES . " ON " . DB_T_LOTTERY_INSCRITS . ".id_salarie = " . DB_T_SALARIES . ".id "
                . "WHERE " . DB_T_LOTTERY_INSCRITS . ".id_lottery = :id_lottery");
        db::bind(':id_lottery', $_id);
        return db::resultset();
    }

    public static function getWinners(int $_id){
        db::query("SELECT "
                . "" . DB_T_LOTTERY_INSCRITS . ".id_salarie, "
                . "" . DB_T_LOTTERY_INSCRITS . ".id_dossier, "
                . "UCASE(" . DB_T_LOTTERY_INSCRITS . ".login) AS login, "
                . "UCASE(" . DB_T_LOTTERY_INSCRITS . ".prenom) AS prenom, "
                . "UCASE(" . DB_T_LOTTERY_INSCRITS . ".nom) AS nom, "
                . "" . DB_T_LOTTERY_INSCRITS . ".selected "
                . "FROM " . DB_T_LOTTERY_INSCRITS . " "
                . "WHERE " . DB_T_LOTTERY_INSCRITS . ".id_lottery = :id_lottery AND " . DB_T_LOTTERY_INSCRITS . ".selected IS NOT NULL");
        db::bind(':id_lottery', $_id);
        return db::resultset();
    }

    public static function getCloture(int $_id){
        db::query("SELECT "
                . DB_T_LOTTERY . ".sortDate, "
                . DB_T_LOTTERY . ".sortNb, "
                . "CONCAT (" . DB_T_USER . ".prenom, ' ', " . DB_T_USER . ".nom) AS 'sortBy' "
                . "FROM " . DB_T_LOTTERY . " "
                . "INNER JOIN " . DB_T_USER . " ON " . DB_T_LOTTERY . ".id_user = " . DB_T_USER . ".id "
                . "WHERE " . DB_T_LOTTERY . ".id = :id");
        db::bind(':id', $_id);
        return db::single();
    }

    public static function getSortInscription(int $_id, int $_sort = 0)
    {
        if($_sort == 0){
            $sqlSort = "";
        } else {
            $sqlSort = " ORDER BY RAND() LIMIT " . $_sort;
        }
        
        db::query("SELECT "
                . "" . DB_T_LOTTERY_INSCRITS . ".id_lottery, "
                . "" . DB_T_LOTTERY_INSCRITS . ".id_salarie, "
                . "" . DB_T_LOTTERY_INSCRITS . ".id_dossier, "
                . "UCASE(" . DB_T_LOTTERY_INSCRITS . ".login) AS login, "
                . "UCASE(" . DB_T_LOTTERY_INSCRITS . ".prenom) AS prenom, "
                . "UCASE(" . DB_T_LOTTERY_INSCRITS . ".nom) AS nom "
                . "FROM " . DB_T_LOTTERY_INSCRITS . " "
                . "INNER JOIN " . DB_T_SALARIES . " ON " . DB_T_LOTTERY_INSCRITS . ".id_salarie = " . DB_T_SALARIES . ".id "
                . "WHERE " . DB_T_LOTTERY_INSCRITS . ".id_lottery = :id_lottery AND " . DB_T_SALARIES . ".actif = 1" . $sqlSort);
        db::bind(':id_lottery', $_id);
        return db::resultset();
    }

    public static function setSortLottery(int $_idDossier)
    {
        db::query("UPDATE " . DB_T_LOTTERY_INSCRITS . " "
                . "SET " . DB_T_LOTTERY_INSCRITS . ".selected = CURRENT_TIMESTAMP() "
                . "WHERE " . DB_T_LOTTERY_INSCRITS . ".id_dossier = :id_dossier");
        db::bind(':id_dossier', $_idDossier);
        try {
            db::execute();
            return TRUE;
        } catch (Exception $ex) {
            alert::recError("ERREUR TECHNIQUE : Au niveau de l'écriture en base de données lors du tirage au sort");
            return FALSE;
        }
    }

    public static function deleteLottery(int $_id)
    {
        db::query("DELETE FROM " . DB_T_LOTTERY . " WHERE id = :id");
        db::bind(':id', $_id);
        return db::execute();
    }

    public static function deleteInscriptionLottery(int $_id)
    {
        db::query("DELETE FROM " . DB_T_LOTTERY_INSCRITS . " WHERE id_lottery = :id_lottery");
        db::bind(':id_lottery', $_id);
        return db::execute();
    }

    public static function setDateLottery(int $_id, int $_nb)
    {
        db::query("UPDATE " . DB_T_LOTTERY . " "
                . "SET " . DB_T_LOTTERY . ".sortDate = CURRENT_TIMESTAMP(), "
                . DB_T_LOTTERY . ".sortNb = :sortNb, "
                . DB_T_LOTTERY . ".sortBy = :sortBy "
                . "WHERE " . DB_T_LOTTERY . ".id = :id");
        db::bind(':sortNb', $_nb);
        db::bind(':sortBy', session::getId());
        db::bind(':id', $_id);
        try {
            db::execute();
            return TRUE;
        } catch (Exception $ex) {
            alert::recError("ERREUR TECHNIQUE : Au niveau de l'écriture en base de données lors de la datation");
            return FALSE;
        }
    }

    public static function getInscriptionData(int $_id)
    {
        db::query("SELECT "
        . "COUNT(" . DB_T_LOTTERY_INSCRITS . ".login) AS inscrits, "
        . "SUM(IF(" . DB_T_LOTTERY_INSCRITS . ".valide = 1, 1, 0)) AS eligible, "
        . "SUM(IF(" . DB_T_LOTTERY_INSCRITS . ".valide = 0, 1, 0)) AS ineligible "
        . "FROM " . DB_T_LOTTERY_INSCRITS . " "
        . "WHERE " . DB_T_LOTTERY_INSCRITS . ".id_lottery = :id_lottery");
        db::bind(':id_lottery', $_id);
        return db::single();
    }

    public static function getFiche(int $_id)
    {
        db::query("SELECT * FROM " . DB_T_LOTTERY . " WHERE id = :id");
        db::bind(':id', $_id);
        return db::single();
    }

    public static function lastLottery()
    {
        db::query("SELECT MAX(id) AS id FROM " . DB_T_LOTTERY);
        return db::single()["id"];
    }
}