<?php  


class DbDatabase  {
  const QUESTION_TABLE='Questions';
  const TOURNAMENT_TABLE='Tournaments';
  const PEOPLE_TABLE = 'People';
  const P2T_TABLE = 'P2T';
  public function getTournament($id) {
      if (is_numeric($id)) {
          return $this->getTournamentByDatabaseId($id);
      } else {
          return $this->getTournamentByTextId($id);
      }
  }

  public function getAllEditorsRes() {
      $res = db_query("SELECT People.* from  P2T LEFT JOIN People ON (P2T.Author=People.CharId)
            GROUP BY CharId ORDER BY TNumber DESC");
      return $res;

  }
  
  public function getEditors($id) {
    $sql = "SELECT People.* from  P2T LEFT JOIN People ON (P2T.Author=People.CharId)
    WHERE P2T.Tour='$id'";
    $res = db_query($sql);
    $result = array();
    while ($e = db_fetch_object($res)) {
      $result[] = $e;
    }
    return $result;
  }

  public function getTournamentByDatabaseId($id){
    $sql = sprintf ("SELECT * FROM %s t
      WHERE t.Id = '%d'", self::TOURNAMENT_TABLE, $id);
    $res = db_query($sql);
    return db_fetch_object($res);
  }
  public function getTournamentByTextId($id){
/*    if (!preg_match('/\./', $id)) {
      $id .= '.txt';
    }*/

    $sql = sprintf ("SELECT * FROM %s t
      WHERE t.FileName = '%s.txt' OR t.FileName = '%s'", 
      self::TOURNAMENT_TABLE, $id, $id);
    $res = db_query($sql);
    return db_fetch_object($res);
  }

  public function getPersonById($id){
    $sql = sprintf ("SELECT * FROM %s a
      WHERE a.CharId = '%s'",
      self::PEOPLE_TABLE, $id);
    $res = db_query($sql);
    return db_fetch_object($res);
  }

  public function editorToursRes($id) {
    $res = db_query("SELECT t.* FROM %s t
                LEFT JOIN %s p2t ON
                (p2t.Tour = t.Id)
                WHERE p2t.Author = '%s'
                ORDER BY PlayedAt DESC, Title ",
            self::TOURNAMENT_TABLE,
            self::P2T_TABLE,
            $id);
    return $res;
  }

  public function getQuestionsRes($id) {
    $sql = sprintf("SELECT * FROM {%s} WHERE ParentId=%d ORDER BY Number", self::QUESTION_TABLE, $id);
    return db_query($sql);
  }

  public function getChildrenRes($id) {
    $sql = sprintf("SELECT * FROM {%s} WHERE ParentId=%d ORDER BY Number, Id", self::TOURNAMENT_TABLE, $id);
    return db_query($sql);
  }
  
  public function fetch_row($res) {
    return db_fetch_object($res);
  }

  public function getFulltextSearchRes( $sstr, $options = array()) {
    $sql="SELECT
      t.FileName as tourFileName,
      t1.FileName as tournamentFileName,
      q.*,
      t.Id as tourId,
      t1.id as tournamentId,
      t.Title as tourTitle,
      t1.Title as tournamentTitle,
      t.Type as tourType,
      t1.Type as tournamentType

    FROM %1\$s q
    LEFT JOIN %2\$s t ON (q.ParentId=t.Id)
    LEFT JOIN  %2\$s t1 ON (t.ParentId=t1.Id)
    WHERE
     MATCH (Question,Answer,PassCriteria,Comments) AGAINST ('%3\$s' IN BOOLEAN MODE)
    ORDER BY MATCH (Question,Answer,PassCriteria,Comments) AGAINST
     ('%3\$s' IN BOOLEAN MODE) DESC LIMIT %4\$s";
    $limit = 20;
    $sql = sprintf($sql,
            self::QUESTION_TABLE,
            self::TOURNAMENT_TABLE,
            $sstr,
            $limit);
      return db_query($sql);    
  }

}