inc/Subjects.php

<?php
namespace DAFME\Covid;

class Subjects {
  public static function getAll() {
    global $con;
    $query = $con->prepare('SELECT * FROM subjects');

    if (!$query->execute())
      return false;

    return $query->fetchAll(\PDO::FETCH_ASSOC);
  }

  public static function addIsUserSelected(&$entries, $doIfSignedOut = true) {
    $isSignedIn = Users::isSignedIn();

    if (!$doIfSignedOut && !$isSignedIn) return;

    foreach ($entries as &$entry) {
      if (!$isSignedIn)
        $entry['user_subject_id'] = null;

      $entry['user_selected'] = $entry['user_subject_id'] !== null;
    }
  }

  public static function getStartupSubjects() {
    global $con;
    $isSignedIn = Users::isSignedIn();

    $query = $con->prepare('SELECT s.id, s.friendly_name'.($isSignedIn ? ', us.id user_subject_id' : '').'
      FROM subjects s'.($isSignedIn ? '
      LEFT JOIN user_subjects us
        ON s.id = us.subject_id
      WHERE
        us.user_id = :user_id OR
        us.user_id IS NULL' : '').'
      ORDER BY s.friendly_name ASC');

    $query_params = [
      "user_id" => Users::getUserId()
    ];

    if (!$query->execute($query_params)) return false;
    $subjects = $query->fetchAll(\PDO::FETCH_ASSOC);

    self::addIsUserSelected($subjects, false);
    return $subjects;
  }

  public static function exists(int $subject): bool {
    global $con;
    $query = $con->prepare('SELECT id FROM subjects WHERE id = ?');
    if (!$query->execute([$subject]))
      return false;

    return $query->rowCount() > 0;
  }

  public static function isNewUserSubject(int $subjectId): bool {
    global $con;
    $query = $con->prepare('SELECT id FROM user_subjects WHERE user_id = :user_id AND subject_id = :subject_id');

    $userId = Users::getUserId();
    if ($userId == -1 || !$query->execute([
      'user_id' => $userId,
      'subject_id' => $subjectId
    ]))
      return false;

    return $query->rowCount() == 0;
  }

  public static function getUserSubjects() {
    global $con;
    $query = $con->prepare('SELECT
        us.id, us.subject_id, s.friendly_name, s.calendar_name
        FROM user_subjects us
        INNER JOIN subjects s
          ON us.subject_id = s.id
        WHERE us.user_id = ?');

    $userId = Users::getUserId();
    if ($userId == -1 || !$query->execute([$userId]))
      return false;

    return $query->fetchAll(\PDO::FETCH_ASSOC);
  }

  public static function addUserSubject(int $subjectId): bool {
    global $con;

    if (!self::exists($subjectId) || !self::isNewUserSubject($subjectId))
      return false;

    $query = $con->prepare('INSERT INTO user_subjects (user_id, subject_id) VALUES (:user_id, :subject_id)');

    $userId = Users::getUserId();

    return $userId != -1 && $query->execute([
      'user_id' => $userId,
      'subject_id' => $subjectId
    ]);
  }

  public static function removeUserSubject(int $subjectId): bool {
    global $con;

    $query = $con->prepare('DELETE FROM user_subjects WHERE user_id = :user_id AND subject_id = :subject_id LIMIT 1');

    $userId = Users::getUserId();
    return $userId != -1 && $query->execute([
      'user_id' => $userId,
      'subject_id' => $subjectId
    ]);
  }
}