conn = new PDO('pgsql:host=localhost;port=5432;dbname=apisub', $db_user, $db_pass); } catch (PDOException $e) { die('Connection failed: ' . $e->getMessage()); } } public function subList($utclogin) { $sql = 'SELECT * FROM vsubscription WHERE utclogin=:utclogin AND (validation OR validation IS NULL)'; $st = $this->conn->prepare($sql); $st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR); $st->execute(); $res = $st->fetchAll(PDO::FETCH_ASSOC); return $res; } public function apiList($admin, $utclogin) { $sql = 'SELECT a.*, is_available(:utclogin, week, year) AS is_available, CASE WHEN s.api IS NULL THEN 0 /* student has not yet subscribed */ WHEN s.validation IS NULL THEN 0.5 /* student has subscribed and wait for valdiation */ WHEN s.validation THEN 1 /* student subscription has been validated */ ELSE -1 END AS validation /* student subscription has been declined or other subscription has been accepted the same week */ FROM vapi a LEFT JOIN subscribe s ON a.id = s.api WHERE semester=:semester AND year=:year'; $st = $this->conn->prepare($sql); $st->bindValue(':semester',$admin->activeSemester(),PDO::PARAM_STR); $st->bindValue(':year',$admin->activeYear(),PDO::PARAM_INT); $st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR); $st->execute(); $res = $st->fetchAll(PDO::FETCH_ASSOC); return $res; } public function subToApi($admin, $utclogin, $api) { // Case 1 : Student had not yet subscribe to this Api and no other Api is validated same week, insertion expected to work // Case 2 : Student had already subscribe to this Api, key constraint will prevent from adding new record, so, if subscription has been unvalidated it will remain so AND is_available test will block inserting before previous rule anyway // Case 3 : Student had not subscribe to this Api, but another Api has been validated same week, insertion expected to fail (test with is_avaiblable) $sql = 'SELECT is_available(:utclogin, week, year) AS is_available FROM vapi WHERE semester=:semester AND year=:year AND id=:api' ; $st = $this->conn->prepare($sql); $st->bindValue(':api',$api,PDO::PARAM_INT); $st->bindValue(':semester',$admin->activeSemester(),PDO::PARAM_STR); $st->bindValue(':year',$admin->activeYear(),PDO::PARAM_INT); $st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR); $st->execute(); $res = $st->fetch(PDO::FETCH_ASSOC); if ($res['is_available']) { $today = date('Ymd'); $sql = 'INSERT INTO subscribe(utclogin, api, subdate) VALUES (:utclogin, :api, :today)'; $st = $this->conn->prepare($sql); $st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR); $st->bindValue(':api',$api,PDO::PARAM_INT); $st->bindValue(':today',$today,PDO::PARAM_STR); $res = $st->execute(); return $res; } else { return null; } } public function unsubToApi($utclogin, $api) { #TODO add superpower to unsubscribe if admin /** Testing validation IS NULL prevent from unsubscribing to validated Api **/ $sql = 'DELETE FROM subscribe WHERE utclogin=:utclogin AND api=:api AND validation IS NULL'; $st = $this->conn->prepare($sql); $st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR); $st->bindValue(':api',$api,PDO::PARAM_INT); $res = $st->execute(); return $res; } public function copyUser($utclogin, $surname, $firstname, $email) { // Function used to create a local copy of each user, in order to use data directly in database $sql = 'SELECT utclogin FROM localuser WHERE utclogin=:utclogin'; $st1 = $this->conn->prepare($sql); $st1->bindValue(':utclogin',$utclogin,PDO::PARAM_STR); $st1->execute(); if (!$st1->fetch(PDO::FETCH_ASSOC)) { // If user has never logged in yet, he is added to local copy $sql = 'INSERT INTO localuser(utclogin) VALUES (:utclogin)'; $st2 = $this->conn->prepare($sql); $st2->bindValue(':utclogin',$utclogin,PDO::PARAM_STR); $res = $st2->execute(); } // Update data in any case $sql = 'UPDATE localuser SET firstname=:firstname, surname=:surname, email=:email WHERE utclogin=:utclogin'; $st2 = $this->conn->prepare($sql); $st2->bindValue(':utclogin',$utclogin,PDO::PARAM_STR); $st2->bindValue(':firstname',$firstname,PDO::PARAM_STR); $st2->bindValue(':surname',$surname,PDO::PARAM_STR); $st2->bindValue(':email',$email,PDO::PARAM_STR); $res = $st2->execute(); return $res; } /** Returns configuration parameters as: ** openbegin | openend | isactive ** ------------------------+--------------------------+---------- ** mercredi 10 avril 2019 | mercredi 31 juillet 2019 | t **/ public function config() { $sql = 'SELECT * FROM vconfig'; $st = $this->conn->prepare($sql); $st->execute(); $res = $st->fetch(PDO::FETCH_ASSOC); return $res; } public function isResp ($resplogin) { $sql = 'SELECT resplogin FROM api WHERE resplogin=:resplogin'; $st = $this->conn->prepare($sql); $st->bindValue(':resplogin',$resplogin,PDO::PARAM_STR); $st->execute(); $res = $st->fetch(PDO::FETCH_ASSOC); return $res['resplogin']; } public function isAdmin ($utclogin) { $sql = 'SELECT utclogin FROM admins WHERE utclogin=:utclogin'; $st = $this->conn->prepare($sql); $st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR); $st->execute(); $res = $st->fetch(PDO::FETCH_ASSOC); return $res['utclogin']; } public function apiListResp($admin, $utclogin) { $sql = 'SELECT *, indicator(utclogin, week, year) AS indicator FROM vsubscription WHERE semester=:semester AND year=:year AND resplogin=:resp' ; $st = $this->conn->prepare($sql); $st->bindValue(':semester',$admin->activeSemester(),PDO::PARAM_STR); $st->bindValue(':year',$admin->activeYear(),PDO::PARAM_INT); $st->bindValue(':resp',$utclogin,PDO::PARAM_STR); $st->execute(); $res = $st->fetchAll(PDO::FETCH_ASSOC); return $res; } // Validation that $resp is resp of $api private function validateResp($resp, $api) { $sql = 'SELECT COUNT(*) AS c FROM vapi WHERE resplogin=:resp AND id=:api'; $st = $this->conn->prepare($sql); $st->bindValue(':resp',$resp,PDO::PARAM_STR); $st->bindValue(':api',$api,PDO::PARAM_STR); $st->execute(); $res = $st->fetch(PDO::FETCH_ASSOC); if ($res['c']==1) return true; else return false; } /** This function either validate or decline a student participation to an Api. ** If $validation is true : student participation is unvalidated ** Else : student participation is declined **/ public function validate($resp, $api, $student, $validation) { $today = date('Ymd'); // Check user is Api responsible if ($this->validateResp($resp, $api) == false) { echo "

Erreur : le responsable $resp n'est pas autorisé à valider l'inscription de $student à l'Api $api

" ; return -1; } // Year and week calculation for Api $sql = "SELECT year, week FROM vapi WHERE id=:api"; $st = $this->conn->prepare($sql); $st->bindValue(':api',$api,PDO::PARAM_STR); $st->execute(); $res = $st->fetch(PDO::FETCH_ASSOC); $year = $res['year']; $week = $res['week']; // Validate subscription $st = $this->conn->prepare($sql); $sql = "BEGIN"; $st = $this->conn->prepare($sql); $st->execute(); $sql = "UPDATE subscribe SET validation=:validation, validationdate=:today WHERE api=:api AND utclogin=:utclogin"; $st = $this->conn->prepare($sql); $st->bindValue(':utclogin',$student,PDO::PARAM_STR); $st->bindValue(':api',$api,PDO::PARAM_STR); $st->bindValue(':today',$today,PDO::PARAM_STR); $st->bindValue(':validation',$validation,PDO::PARAM_STR); $res = $st->execute(); // Cancel other subscriptions the same week (if validation only) if ($validation == 'TRUE') { $sql = "UPDATE subscribe SET validation='FALSE', validationdate=:today WHERE api<>:api AND utclogin=:utclogin AND api IN (SELECT id FROM vapi WHERE week=:week AND year=:year)"; $st = $this->conn->prepare($sql); $st->bindValue(':utclogin',$student,PDO::PARAM_STR); $st->bindValue(':api',$api,PDO::PARAM_STR); $st->bindValue(':today',$today,PDO::PARAM_STR); $st->bindValue(':year',$year,PDO::PARAM_STR); $st->bindValue(':week',$week,PDO::PARAM_STR); $res = $st->execute(); } $sql = "COMMIT"; $st = $this->conn->prepare($sql); $st->execute(); return true; } public function apiStudents($utclogin, $api) { $sql = 'SELECT * FROM vsubscription WHERE id=:api AND resplogin=:utclogin AND validation ORDER BY surname, firstname'; $st = $this->conn->prepare($sql); $st->bindValue(':api',$api,PDO::PARAM_STR); $st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR); $st->execute(); $res = $st->fetchAll(PDO::FETCH_ASSOC); return $res; } public function apiStudentsAll() { $sql = 'SELECT * FROM vsubscription WHERE validation ORDER BY week, code, surname, firstname'; $st = $this->conn->prepare($sql); $st->execute(); $res = $st->fetchAll(PDO::FETCH_ASSOC); return $res; } public function mailResp() { $sql = "SELECT DISTINCT u.email AS email FROM vapi a JOIN localuser u ON a.resplogin=u.utclogin ORDER BY u.email"; $st = $this->conn->prepare($sql); $st->execute(); $res = $st->fetchAll(PDO::FETCH_ASSOC); return $res; } }