ghalusa / PHP-Skeleton-App

   * @param       string $user_account_email     The data value

   * @return      false|string                   The query result

   */

  public function account_email_exists( $user_account_email )

  {

      $statement = $this->db->prepare("SELECT 

            user_account_id

            ,user_account_email

          FROM user_account

          WHERE user_account_email = :user_account_email");

      $statement->bindValue(":user_account_email", $user_account_email, PDO::PARAM_INT);

      $statement->execute();

      return $statement->fetch(PDO::FETCH_ASSOC);

  }

  /**

   * Is Registered

   * @param       int $user_account_id           The data value

   * @return      array|bool                     The query result

   */

  public function is_registered( $user_account_id )

  {

      $statement = $this->db->prepare("SELECT user_account.acceptable_use_policy

          ,GROUP_CONCAT(user_account_groups.group_id SEPARATOR ', ') AS groups

          FROM user_account

          LEFT JOIN user_account_groups ON user_account.user_account_id = user_account_groups.user_account_id

          WHERE user_account.user_account_id = :user_account_id

          AND user_account.acceptable_use_policy = 1

          GROUP BY user_account.user_account_id

          HAVING groups != ''");

      $statement->bindValue(":user_account_id", $user_account_id, PDO::PARAM_INT);

      $statement->execute();

      return $statement->fetch(PDO::FETCH_ASSOC);

  }

  /**

   * Update Emailed Hash

   * @param       int $user_account_id    The data value

   * @return      array|bool              The query result

   */

  public function get_user_account_groups($user_account_id)

  {

      $statement = $this->db->prepare("SELECT `group`.group_id

          ,`group`.name AS group_name

          FROM user_account_groups

          LEFT JOIN `group` ON `group`.group_id = user_account_groups.group_id

          WHERE user_account_groups.user_account_id = :user_account_id

          GROUP BY `group`.group_id");

      $statement->bindValue(":user_account_id", $user_account_id, PDO::PARAM_INT);

      $statement->execute();

      return $statement->fetchAll(PDO::FETCH_ASSOC);

  }

  /**

   * Get User Group Roles

   * @param       int $group_id           The data value

   * @return      array|bool              The query result

   */

  public function get_user_group_roles($user_account_id, $group_id)

  {

      $statement = $this->db->prepare("SELECT user_account_roles.role_id

          ,user_account_roles.label AS role_label

          FROM user_account_groups

          LEFT JOIN user_account_roles ON user_account_roles.role_id = user_account_groups.role_id

          WHERE user_account_groups.user_account_id = :user_account_id

          AND user_account_groups.group_id = :group_id");

      $statement->bindValue(":user_account_id", $user_account_id, PDO::PARAM_INT);

      $statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);

      $statement->execute();

      return $statement->fetchAll(PDO::FETCH_ASSOC);

  }

  /**

   * Get Roles

   * @param       int $user_account_id    The data value

   * @return      array|bool              The query result

   */

  public function get_user_account_info($user_account_id = false)

  {

      $statement = $this->db->prepare("SELECT user_account_email

          ,first_name

          ,last_name

          ,user_account_id

          FROM user_account

          WHERE user_account_id = :user_account_id");

      $statement->bindValue(":user_account_id", $user_account_id, PDO::PARAM_INT);

      $statement->execute();

      return $statement->fetch(PDO::FETCH_ASSOC);

  }

  /**

   * Get Addresses

   * @param       int $user_account_id    The data value

   * @return      array|bool              The query result

   */

  public function get_addresses($user_account_id = false)

  {

      $statement = $this->db->prepare("SELECT *

          FROM user_account_addresses

          WHERE user_account_id = :user_account_id");

      $statement->bindValue(":user_account_id", $user_account_id, PDO::PARAM_INT);

      $statement->execute();

      return $statement->fetchAll(PDO::FETCH_ASSOC);

  }

  /**

   * Insert Addresses

   * @param       string $search                 The data value

   * @return      array|bool                     The query result

   */

  public function find_user_account( $search )

  {

      $statement = $this->db->prepare("

          SELECT CONCAT(first_name, ' ', last_name) AS displayname

              ,first_name

              ,last_name

              ,user_account_id

          FROM user_account

          WHERE first_name LIKE :search

          OR last_name LIKE :search

          LIMIT 20");

      $statement->bindValue(":search", "%".$search ."%", PDO::PARAM_STR);

      $statement->execute();

      return $statement->fetchAll(PDO::FETCH_ASSOC);

  }

  /**

   * Delete User Account

   * @param       int $user_account_id           The data value

   * @return      void

   */

  public function delete_user_account( $user_account_id )

  {

      // Delete the user from the user_account table.

      $statement = $this->db->prepare("DELETE FROM user_account

          WHERE user_account_id = :user_account_id");

      $statement->bindValue(":user_account_id", $user_account_id, PDO::PARAM_INT);

      $statement->execute();

      // Delete the user from the user_account_groups table.

      $this->delete_user_groups( $user_account_id );

  }

  /**

   * Delete User Groups

   * @param       int $user_account_id           The data value

   * @return      array|bool                     The query result

   */

  public function get_user_roles_list( $user_account_id )

  {

      $statement = $this->db->prepare("SELECT DISTINCT role_id

          FROM user_account_groups

          WHERE user_account_id = :user_account_id");

      $statement->bindValue(":user_account_id", $user_account_id, PDO::PARAM_INT);

      $statement->execute();

      return $statement->fetchAll(PDO::FETCH_ASSOC);

  }

  /**

   * Update Acceptable Use Policy

   * @param       int $group_id           The data value

   * @return      array|bool              The query result

   */

  public function get_users_proxies_for_group( $user_account_id, $group_id )

  {

      $statement = $this->db->prepare("SELECT 

              CONCAT(user_account.first_name, ' ', user_account.last_name) AS displayname

              ,user_account.user_account_id

          FROM user_account_groups

          RIGHT JOIN user_account_proxy ON user_account_proxy.user_account_groups_id = user_account_groups.user_account_groups_id

          LEFT JOIN user_account ON user_account.user_account_id = user_account_proxy.proxy_user_account_id

          WHERE user_account_groups.user_account_id = :user_account_id

          AND user_account_groups.group_id = :group_id");

      $statement->bindValue(":user_account_id", $user_account_id, PDO::PARAM_INT);

      $statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);

      $statement->execute();

      return $statement->fetchAll(PDO::FETCH_ASSOC);

  }

  /**

   * Get User Group Roles Map