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

<?php

class event
{

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

    public static function getFicheByMd5(string $_md5)
    {
        db::query("SELECT * FROM " . DB_T_EVENTS . " WHERE md5 = :md5");
        db::bind(':md5', $_md5);
        return db::single();
    }

    public static function getQRCode(int $_id, $_width = 50, string $_link = NULL)
    {
        db::query("SELECT md5 FROM " . DB_T_EVENTS . " WHERE id = :id");
        db::bind(':id', $_id);
        $md5 = db::single()["md5"];
        $link = core::base64_url_encode("https://" . DOMAIN_EVENTS . "/?e=" . $md5);

        if ($_link == NULL) {
            echo '<img src="/qrcode.php?q= ' . $link . '" width="' . $_width . '" >';
        } else {
            echo '<a href="https://' . DOMAIN_EVENTS . '/qrcode.php?q=' . $link . '" target="_blank"><img src="/qrcode.php?q= ' . $link . '" width="' . $_width . '" ></a>';
        }
    }

    public static function add()
    {
        db::query("INSERT INTO " . DB_T_EVENTS . " (md5, titre, description, startDate, endDate, type_emargement, type_inscription, actif, id_user) VALUES (:md5, :titre, :description, :startDate, :endDate, :type_emargement, :type_inscription, :actif, :id_user)");

        db::bind(':md5', md5(time().rand(1000000000, 9999999999)));
        db::bind(':titre', core::getPost("titre"));
        db::bind(':description', core::getPost("description"));
        db::bind(':startDate', core::getPost("startDate"));
        db::bind(':endDate', core::getPost("endDate"));
        db::bind(':type_emargement', core::getPost("type_emargement"));
        db::bind(':type_inscription', core::getPost("type_inscription"));
        db::bind(':actif', core::getPost("actif"));
        db::bind(':id_user', session::getId());
        
        try {
                db::execute();
                return TRUE;
            } catch (Exception $ex) {
                return FALSE;
            }
    }

    public static function update()
    {
        db::query("UPDATE " . DB_T_EVENTS . " SET "
                . "titre = :titre, "
                . "description = :description, "
                . "startDate = :startDate, "
                . "endDate = :endDate, "
                . "type_emargement = :type_emargement, "
                . "type_inscription = :type_inscription, "
                . "actif = :actif, "
                . "id_user = :id_user "
                . "WHERE id = :id");
        
        db::bind(':titre', core::getPost("titre"));
        db::bind(':description', core::getPost("description"));
        db::bind(':startDate', core::getPost("startDate"));
        db::bind(':endDate', core::getPost("endDate"));
        db::bind(':type_emargement', core::getPost("type_emargement"));
        db::bind(':type_inscription', core::getPost("type_inscription"));
        db::bind(':actif', core::getPost("actif"));
        db::bind(':id_user', session::getId());
        db::bind(':id', core::getPost("id"));
        
        try {
                db::execute();
                return TRUE;
            } catch (Exception $ex) {
                return FALSE;
            }
    }

    public static function subscribe(int $_salarie = NULL)
    {
        if($_salarie == NULL){
            $salarie = core::getPost("salarie");
        } else {
            $salarie = $_salarie;
        }
        
        db::query("INSERT INTO " . DB_T_EVENTS_INSCRITS . " (id_evenement, id_salarie, sel, id_user) VALUES (:id_evenement, :id_salarie, :sel, :id_user)");
        db::bind(':sel', md5(core::getPost("event")."-".time().rand(100000000000000, 999999999999999)));
        db::bind(':id_evenement', core::getPost("event"));
        db::bind(':id_salarie', $salarie);
        db::bind(':id_user', session::getId());

        try {
            db::execute();
            return TRUE;
        } catch (Exception $ex) {
            return FALSE;
        }
    }

    public static function unsubscribe()
    {
        db::query("DELETE FROM " . DB_T_EVENTS_INSCRITS . " WHERE id_evenement = :id_evenement AND id_salarie = :id_salarie");
        db::bind(':id_evenement', core::getPost("event"));
        db::bind(':id_salarie', core::getPost("salarie"));

        try {
            db::execute();
            return TRUE;
        } catch (Exception $ex) {
            return FALSE;
        }
    }

    public static function getEvents()
    {
        db::query("SELECT "
            . "" . DB_T_EVENTS . ".id, "
            . "" . DB_T_EVENTS . ".md5, "
            . "" . DB_T_EVENTS . ".titre, "
            . "" . DB_T_EVENTS . ".description, "
            . "" . DB_T_EVENTS . ".startDate, "
            . "COUNT(" . DB_T_EVENTS_INSCRITS . ".id_salarie) AS m_global, "
            . "COUNT(" . DB_T_EVENTS_INSCRITS . ".present) AS m_inscrit, "
            . "" . DB_T_EVENTS . ".endDate, "
            . "TIMESTAMPDIFF(HOUR, " . DB_T_EVENTS . ".startDate, " . DB_T_EVENTS . ".endDate) AS pastHours, "
            . "IF(" . DB_T_EVENTS . ".type_emargement = 1, 'Contôle', IF(" . DB_T_EVENTS . ".type_emargement = 2, 'Autonome', 'Contrôle & Autonome')) AS type_emargement, "
            . "IF(" . DB_T_EVENTS . ".type_inscription = 1, 'Incription', 'Libre') AS type_inscription, "
            . "" . DB_T_EVENTS . ".actif, "
            . "" . DB_T_EVENTS . ".cree, "
            . "" . DB_T_EVENTS . ".maj, "
            . "CONCAT (" . DB_T_USER . ".prenom, ' ', " . DB_T_USER . ".nom) AS 'user' "
            . "FROM " . DB_T_EVENTS . " "
            . "LEFT JOIN " . DB_T_EVENTS_INSCRITS . " ON " . DB_T_EVENTS_INSCRITS . ".id_evenement = " . DB_T_EVENTS . ".id "
            . "INNER JOIN " . DB_T_USER . " ON " . DB_T_EVENTS . ".id_user = " . DB_T_USER . ".id "
            . "GROUP BY 1");
        return db::resultset();
    }

    public static function getInscriptions()
    {
        db::query("SELECT "
            . "" . DB_T_SALARIES . ".id, "
            . "" . DB_T_SALARIES . ".loginId, "
            . "" . DB_T_SALARIES . ".nom, "
            . "" . DB_T_SALARIES . ".prenom, "
            . "" . DB_T_SALARIES . ".sexe, "
            . "" . DB_T_SALARIES . ".contrat, "
            . "" . DB_T_SALARIES_PROWEB . ".dateNaissance, "
            . "" . DB_T_SALARIES . ".lieu, "
            . "" . DB_T_SALARIES . ".actif, "
            . "IF(" . DB_T_EVENTS_INSCRITS . ".id_salarie IS NOT NULL, 1, 0) AS inscrit, "
            . "" . DB_T_EVENTS_INSCRITS . ".present "
            . "FROM " . DB_T_SALARIES . " "
            . "LEFT JOIN " . DB_T_EVENTS_INSCRITS . " ON " . DB_T_SALARIES . ".id = " . DB_T_EVENTS_INSCRITS . ".id_salarie "
            . "LEFT JOIN " . DB_T_SALARIES_PROWEB . " ON " . DB_T_SALARIES . ".loginId = " . DB_T_SALARIES_PROWEB . ".loginId "
            . "AND " . DB_T_EVENTS_INSCRITS . ".id_evenement = " . core::getGet("id"));
            return db::resultset();
    }

    public static function connectSalarie(array $_post)
    {

        if (empty($_post["ident"])) {
            return FALSE;
        } elseif (empty($_post["date"])) {
            return FALSE;
        }
        if (is_int($_post["ident"])) {
            $login = intval($_post["ident"]);
        } else {
            $login = $_post["ident"];
        }

        $date = new DateTime($_post["date"]);
        $date = $date->format('Y-m-d H:i:s');

        db::query("SELECT "
            . "" . DB_T_SALARIES . ".id, "
            . "" . DB_T_SALARIES . ".sel, "
            . "" . DB_T_SALARIES . ".nom, "
            . "" . DB_T_SALARIES_PROWEB . ".dateNaissance, "
            . "" . DB_T_SALARIES . ".prenom, "
            . "" . DB_T_SALARIES . ".actif, "
            . "" . DB_T_SALARIES . ".contrat, "
            . "" . DB_T_SALARIES . ".jourEntree "
            . "FROM " . DB_T_SALARIES . " "
            . "INNER JOIN " . DB_T_SALARIES_PROWEB . " ON " . DB_T_SALARIES . ".loginId = " . DB_T_SALARIES_PROWEB . ".loginId "
            . "WHERE " . DB_T_SALARIES . ".loginId = :login OR " . DB_T_SALARIES . ".idLocal = :login");
        db::bind(':login', $login);
        $salarie = db::single();

        if (isset($salarie["id"])) {
            if ($salarie["actif"] == 0) {
                return FALSE;
            } elseif (core::dateWhithoutHours($salarie["dateNaissance"]) != core::dateWhithoutHours($date)) {
                return FALSE;
            } else {
                $_SESSION["salarie"] = array(
                    "id" => $salarie["id"],
                    "sel" => $salarie["sel"],
                    "nom" => $salarie["nom"],
                    "prenom" => $salarie["prenom"]
                );
                return TRUE;
            }
        } else {
            return FALSE;
        }
        return FALSE;
    }

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

    public static function getIdEvenementByMd5(string $_md5)
    {
        db::query("SELECT id FROM " . DB_T_EVENTS . " WHERE md5 = :md5");
        db::bind(':md5', $_md5);
        return db::single()["id"];
    }

    public static function presentEvenement(int $_idSalarie)
    {
        db::query("SELECT "
            . "" . DB_T_EVENTS . ".id, "
            . "" . DB_T_EVENTS . ".md5, "
            . "" . DB_T_EVENTS . ".titre, "
            . "" . DB_T_EVENTS . ".startDate, "
            . "" . DB_T_EVENTS . ".endDate, "
            . "" . DB_T_EVENTS . ".type_emargement, "
            . "" . DB_T_EVENTS_INSCRITS . ".sel, "
            . "" . DB_T_EVENTS_INSCRITS . ".present "
            . "FROM " . DB_T_EVENTS . " "
            . "LEFT JOIN " . DB_T_EVENTS_INSCRITS . " ON " . DB_T_EVENTS_INSCRITS . ".id_evenement = " . DB_T_EVENTS . ".id AND " . DB_T_EVENTS_INSCRITS . ".id_salarie = :id_salarie "
            . "WHERE " . DB_T_EVENTS . ".startDate <= CURRENT_TIMESTAMP() "
            . "AND " . DB_T_EVENTS . ".endDate >= CURRENT_TIMESTAMP() "
            . "AND ((" . DB_T_EVENTS . ".type_inscription = 1 AND " . DB_T_EVENTS_INSCRITS . ".id_salarie = :id_salarie) OR (" . DB_T_EVENTS . ".type_inscription = 0))"
            . "AND " . DB_T_EVENTS . ".actif = 1");
        db::bind(':id_salarie', $_idSalarie);
        $row = db::resultset();
        return $row;
    }

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

    public static function deleteInscriptionEvenement(int $_idEvenement)
    {
        db::query("DELETE FROM " . DB_T_EVENTS_INSCRITS . " WHERE id_evenement = :id_evenement");
        db::bind(':id_evenement', $_idEvenement);
        return db::execute();
    }

    public static function checkUrl(string $_url)
    {
        return parse_url($_url)["host"];
    }

    public static function getEvenementbyQRCode(string $_QRCode)
    {
        $query = parse_url($_QRCode)["query"];
        parse_str($query, $params);
        return $params;
    }

    public static function checkEmargement(int $_id_evenement)
    {
        db::query("SELECT "
            . "COUNT(present) AS nb "
            . "FROM " . DB_T_EVENTS_INSCRITS . " "
            . "WHERE " . DB_T_EVENTS_INSCRITS . ".id_evenement = :id_evenement");
        db::bind(':id_evenement', $_id_evenement);
        $nb = db::single()["nb"];
        return $nb;
    }

    public static function getInscriptionEvenementById(int $_id_evenement, int $_id_salarie)
    {
        db::query("SELECT "
            . "" . DB_T_EVENTS_INSCRITS . ".id_salarie, "
            . "" . DB_T_EVENTS_INSCRITS . ".present, "
            . "" . DB_T_EVENTS . ".titre "
            . "FROM " . DB_T_EVENTS_INSCRITS . " "
            . "INNER JOIN " . DB_T_EVENTS . " ON " . DB_T_EVENTS_INSCRITS . ".id_evenement = " . DB_T_EVENTS . ".id "
            . "WHERE " . DB_T_EVENTS_INSCRITS . ".id_salarie = :id_salarie AND " . DB_T_EVENTS_INSCRITS . ".id_evenement = :id_evenement");
        db::bind(':id_salarie', $_id_salarie);
        db::bind(':id_evenement', $_id_evenement);
        $inscription = db::single();
        return $inscription;
    }

    public static function getInscription(int $_id_event){
        db::query("SELECT "
                . "" . DB_T_SALARIES . ".id, "
                . "" . DB_T_SALARIES . ".loginId, "
                . "" . DB_T_SALARIES . ".idLocal, "
                . "" . DB_T_SALARIES_PROWEB . ".dateNaissance, "
                . "" . DB_T_SALARIES . ".jourEntree, "
                . "" . DB_T_SALARIES . ".nom, "
                . "" . DB_T_SALARIES . ".prenom, "
                . "" . DB_T_SALARIES . ".sexe, "
                . "" . DB_T_SALARIES . ".contrat, "
                . "" . DB_T_SALARIES . ".lieu, "
                . "" . DB_T_SALARIES . ".actif, "
                . "" . DB_T_EVENTS_INSCRITS . ".id_evenement, "
                . "" . DB_T_EVENTS_INSCRITS . ".present "
                . "FROM " . DB_T_SALARIES . " "
                . "INNER JOIN " . DB_T_EVENTS_INSCRITS . " ON " . DB_T_EVENTS_INSCRITS . ".id_salarie = " . DB_T_SALARIES . ".id "
                . "LEFT JOIN " . DB_T_SALARIES_PROWEB . " ON " . DB_T_SALARIES . ".loginId = " . DB_T_SALARIES_PROWEB . ".loginId "
                . "WHERE " . DB_T_EVENTS_INSCRITS . ".id_evenement = " . $_id_event);
        return db::resultset();
    }

    public static function checkEvenementBySalarie(string $_md5)
    {

        $return = array();
        $now = time();

        db::query("SELECT "
            . "" . DB_T_SALARIES . ".id, "
            . "" . DB_T_SALARIES . ".actif, "
            . "" . DB_T_SALARIES . ".contrat "
            . "FROM " . DB_T_SALARIES . " "
            . "WHERE " . DB_T_SALARIES . ".id = :id");
        db::bind(':id', session::getId("salarie"));
        $salarie = db::single();

        db::query("SELECT "
            . "" . DB_T_EVENTS_INSCRITS . ".id_salarie, "
            . "" . DB_T_EVENTS_INSCRITS . ".present "
            . "FROM " . DB_T_EVENTS . " "
            . "LEFT JOIN " . DB_T_EVENTS_INSCRITS . " ON " . DB_T_EVENTS_INSCRITS . ".id_evenement = " . DB_T_EVENTS . ".id "
            . "WHERE " . DB_T_EVENTS_INSCRITS . ".id_salarie = :id_salarie AND " . DB_T_EVENTS . ".md5 = :md5");
        db::bind(':id_salarie', session::getId("salarie"));
        db::bind(':md5', $_md5);
        $inscription = db::single();

        db::query("SELECT "
            . "" . DB_T_EVENTS . ".id, "
            . "" . DB_T_EVENTS . ".md5, "
            . "" . DB_T_EVENTS . ".titre, "
            . "" . DB_T_EVENTS . ".startDate, "
            . "" . DB_T_EVENTS . ".endDate, "
            . "" . DB_T_EVENTS . ".type_emargement, "
            . "" . DB_T_EVENTS . ".type_inscription, "
            . "" . DB_T_EVENTS . ".actif "
            . "FROM " . DB_T_EVENTS . " "
            . "WHERE " . DB_T_EVENTS . ".md5 = :md5");
        db::bind(':md5', $_md5);
        $evenement = db::single();

        if ($salarie["id"] == NULL) {
            $return["result"] = FALSE;
            $return["description"] = "Vous n'êtes pas un salarié rattaché à notre CSE";
        } elseif ($salarie["actif"] == 0) {
            $return["result"] = FALSE;
            $return["description"] = "Vous n'êtes plus un salarié rattaché à notre CSE";
        } elseif ($evenement["actif"] == 0) {
            $return["result"] = FALSE;
            $return["description"] = "Cet évènement n'est pas activée";
        } elseif ($evenement["type_inscription"] == 1 and $inscription["id_salarie"] == NULL) {
            $return["result"] = FALSE;
            $return["description"] = "Vous n'êtes pas inscrit à cet évènement";
        } elseif ($now < strtotime($evenement["startDate"])) {
            $return["result"] = FALSE;
            $return["description"] = "Cet évènement n'a pas encore commencé";
        } elseif ($now > (strtotime($evenement["endDate"]) + EMEMARGEMENT_END)) {
            $return["result"] = FALSE;
            $return["description"] = "Cet évènement est terminé";
        } elseif (isset($inscription["present"])) {
            $return["result"] = FALSE;
            $return["description"] = "Votre émargement a déjà été pris en compte<br />le " . core::convertDate($inscription["present"]);
        } else {
            $return["result"] = TRUE;
            $return["description"] = "Votre émargement a été pris en compte";
        }
        $return["evenement"] = $evenement["titre"];
        return $return;
    }

    public static function emargementEvenement(int $_idEvenement, int $_idSalarie)
    {

        $inscription = self::getInscriptionEvenementById($_idEvenement, $_idSalarie);
        $evenement = self::getFiche($_idEvenement);

        if (!isset($inscription["present"]) or empty($inscription["present"])) {
            if (isset($inscription["id_salarie"])) {
                db::query("UPDATE " . DB_T_EVENTS_INSCRITS . " SET present = CURRENT_TIMESTAMP() WHERE id_evenement = :id_evenement AND id_salarie = :id_salarie");
                db::bind(':id_evenement', $_idEvenement);
                db::bind(':id_salarie', $_idSalarie);
                db::execute();
                $return["result"] = TRUE;
                $return["evenement"] = $evenement["titre"];
                $return["description"] = "L'émargement a été pris en compte";
            } else {
                db::query("INSERT INTO " . DB_T_EVENTS_INSCRITS . " (id_evenement, id_salarie, present) "
                    . "VALUES (:id_evenement, :id_salarie, CURRENT_TIMESTAMP())");
                db::bind(':id_evenement', $_idEvenement);
                db::bind(':id_salarie', $_idSalarie);
                db::execute();
                $return["result"] = TRUE;
                $return["evenement"] = $evenement["titre"];
                $return["description"] = "L'émargement a été pris en compte";
            }
        } else {
            $return["result"] = FALSE;
            $return["description"] = "Emargement déjà pris en compte le " . core::convertDate($inscription["present"]);
        }
        return $return;
    }

    public static function emargementEvenementByQRCode()
    {
        $idSalarie = core::getGet("s");
        $md5Evenement = core::getGet("e");
        $key = core::getGet("k");

        $salarie = salaries::getSalarieById($idSalarie);

        $constructKey = md5($md5Evenement . $salarie["sel"]);

        if ($constructKey == $key) {
            $checkEmarge = self::emargementEvenement(self::getIdEvenementByMd5($md5Evenement), $salarie["id"]);
            return $checkEmarge;
        } else {
            $return["result"] = FALSE;
            $return["description"] = "Le QR-Code n'est pas valide";
            return $return;
        }
    }

    public static function printEventBouton(array $_array)
    {
        switch ($_array["nb"]) {
            case 0:
                $class_fadeIn = "first";
                break;
            case 1:
                $class_fadeIn = "second";
                break;
            case 2:
                $class_fadeIn = "third";
                break;
            case 3:
                $class_fadeIn = "fourth";
                break;
            default:
                $class_fadeIn = "fifth";
                break;
        }

        if ($_array["type_emargement"] == 1) {
            $explication = '<p class="card-text">
                                <div>[ QRCode à présenter à votre arrivée ]</div>
                                <br />
                                <div><span data-feather="maximize"></span></div>
                            </p>';
            $meta = 'data-toggle="modal" data-target="#QRCodeModal" data-qrr-qrcode-regexp="^https?:\/\/" data-sel="' . $_array["url_qrcode"] . '" data-titre="' . $_array["titre"] . '"';
            $class_card = 'showQRCode';
            $txt["footer"] = "Cliquez ici pour afficher votre QRCode";
        } else {
            $explication = '<p class="card-text">
                                <div>[ QRCode à flasher vous même ]</div>
                                <br />
                                <div><span data-feather="camera"></span></div>
                            </p>';
            $meta = 'data-event="' . $_array["md5"] . '" data-qrr-target="#QRCodeRead" ';
            $class_card = 'qrcodeReader';
            $txt["footer"] = "Cliquez ici pour scanner le QRCode";
        }

        if (isset($_array["present"])) {
            $meta = "";
            $explication = '<p class="card-text">Votre émargement a bien été pris en compte</p>';
            $class["card-text"] = "text-success";
            $class["card"] = "";
            $txt["footer"] = "Emargement réalisé le " . core::convertDate($_array["present"]);
        } else {
            $class["card-text"] = "text-dark";
            $class["card"] = $class_card;
        }

        echo '  <div ' . $meta . ' class="card mb-3 text-center ' . $class["card"] . ' ' . $class["card-text"] . ' fadeIn ' . $class_fadeIn . '">
                    <div class="card-header"><small>Du ' . core::convertDate($_array["startDate"]) . ' au ' . core::convertDate($_array["endDate"]) . '</small></div>
                    <div class="card-body">
                        <h5 class="card-title">' . $_array["titre"] . '</h5>
                        ' . $explication . '
                    </div>
                    <div class="card-footer"><small>' . $txt["footer"] . '</small></div>
                </div>';
    }

    public static function printIconeResultFlash(string $_result)
    {
        if ($_result == 1) {
            echo '  <div class="swal2-icon swal2-success swal2-animate-success-icon" style="display: flex;">
                        <div class="swal2-success-circular-line-left" style="background-color: rgb(255, 255, 255);"></div>
                        <span class="swal2-success-line-tip"></span>
                        <span class="swal2-success-line-long"></span>
                        <div class="swal2-success-ring"></div> 
                        <div class="swal2-success-fix" style="background-color: rgb(255, 255, 255);"></div>
                        <div class="swal2-success-circular-line-right" style="background-color: rgb(255, 255, 255);"></div>
                    </div>';
        } else {
            echo '  <div class="swal2-icon swal2-error swal2-animate-error-icon" style="display: flex;">
                        <span class="swal2-x-mark"><span class="swal2-x-mark-line-left"></span><span class="swal2-x-mark-line-right"></span></span>
                    </div>';
        }
    }
}