<?php

class tgSqliteDB {

  var $db;
  var $debug;
  var $conf;

  function __construct ( &$conf ) {
    $this->conf =& $conf;
    $this->db = new SQLite3($conf['sqlite']['path']) 
      or die('SQLite DB not found');
  }
  
  function query ($db_query){
    if ($this->debug) echo $db_query."<br/>"; 
    $res = $this->db->query($db_query);
    return $res; 
  }
  
  /**
   * Methods dealing with UserRequests
   */
  function getUserRequests($filter="open", $order = "timestamp DESC") {
    
    $where = '';

    switch($filter) {
      case "open":
        $where .= "WHERE at_status=0";
        break;
      case "my":
        $where .= "WHERE at_assignee='".$this->conf['login']['user']."'";
        break;
      case "closed":
        $where .= "WHERE at_status=1 OR at_status = 2";
        break;    
      case "assigned":
        $where .= "WHERE at_status=3";
        break;              
      case "all":
        break;
    }
    
    $result = $this->query("SELECT * FROM user_request " . $where . " ORDER BY " . $order);
    
    if ($result) while($row = $result->fetchArray(SQLITE3_ASSOC)){
        $requests[]= $row;
      }
    return $requests;
  }
  
  function getUserRequest($id) {
    $result = $this->query("SELECT * FROM user_request WHERE id=".$id);
    
    if ($result) return $result->fetchArray(SQLITE3_ASSOC);
    else return false;
  }
  
  function insertUserRequest ($data) {

     foreach ($data as $key => $val) {
        $data[$key] = $this->db->escapeString($val);
     }
     
     $query = "INSERT INTO user_request (name, surname, pref_uid, email, institution, newsletter, lang, ldap_uid, at_status, at_assignee) 
               VALUES ('".$data['name']."','".$data['surname']."','".$data['userid']."','"
               .$data['email']."','".$data['institution']."','".$data['newsletter']."', '".$data['lang']."', '', '0', '')";
     $this->query($query);
  }
  
  function assignRequest($id) {
     $query = "UPDATE user_request SET at_assignee = '" . $this->conf['login']['user'] . "', at_status = 3 WHERE id=".$id;
     $this->query($query);
  }
  
  function closeRequest($id, $ldap_uid) {
    $query = "UPDATE user_request SET at_status = 1, ldap_uid = '".$ldap_uid."' WHERE id = ". $id;
    $this->query($query);
  }
  
  function rejectRequest($id) {
    $query = "UPDATE user_request SET at_status = 2 WHERE id = ". $id;
    $this->query($query);
  }
  
  /**
   * Methods dealing with Mailtemplates
   */  
  function getMailTemplates() {
    $result = $this->query("SELECT id,title,lang FROM mail_template");
    
    if ($result) while($row = $result->fetchArray(SQLITE3_ASSOC)){
        $mails[]= $row;
      }
    return $mails;
  }
  
  function getMailTemplate($id) {
    $result = $this->query("SELECT * FROM mail_template WHERE id=".$id);
    
    if ($result) return $result->fetchArray(SQLITE3_ASSOC);
    else return false;
  }  

}

?>