/**
  * 按SQL语句获取记录结果，返回数组
  * 
  * @param sql  执行的SQL语句
  */
 public function getArray($sql)
 {
     $result = $this->exec($sql);
     oci_fetch_all($result, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW);
     oci_free_statement($result);
     return $res;
 }

/**
 * Function takes in an sql statement and returns data as an array. When calling, always alias
 * resuts returned from stored function as 'dataArray'
 */
function executeQuery($SQLstatement, $parserFunction = "defaultFunction")
{
    $conn = $GLOBALS['oracle_connection'];
    if (!$conn) {
        return array('error' => oci_error());
    }
    $preparedStatement = oci_parse($conn, $SQLstatement);
    //Prepare statement
    $success = oci_execute($preparedStatement);
    //execute preparedStatement
    if (!$success) {
        return array('error' => oci_error($preparedStatement));
    }
    $arrayOfDataReturned = array();
    //Array containing all data returned from result set
    $currentRecord;
    //temp user for each result set
    while ($functionResults = oci_fetch_array($preparedStatement, OCI_ASSOC)) {
        //Get first class in result set
        /**Calls variable function; SEE: http://www.php.net/manual/en/functions.variable-functions.php**/
        $currentRecord = $parserFunction($functionResults);
        //Convert information array to class
        array_push($arrayOfDataReturned, $currentRecord);
        //push created object into all classes array
        //echo($allStudentClasses[0]->term + "<br />");
    }
    oci_free_statement($preparedStatement);
    return $arrayOfDataReturned;
}

 /**
  * Destructor - Checks for an oci statment resource and frees the resource if needed.
  */
 public function __destruct()
 {
     if (strtolower(get_resource_type($this->stmt)) == 'oci8 statement') {
         oci_free_statement($this->stmt);
     }
     //Also test for descriptors
 }

function q5()
{
    $dao = new DAO();
    $q = "SELECT USERID FROM Q5 WHERE NUM = (SELECT MAX(NUM) FROM Q5)";
    $stmt = oci_parse($dao->con, $q);
    oci_execute($stmt, OCI_DEFAULT);
    oci_fetch_all($stmt, $rs);
    foreach ($rs as $rows) {
        echo "";
        foreach ($rows as $col_values) {
            echo "User contributed most reviews: " . $dao->fetchUserName($col_values) . '<br>';
        }
    }
    oci_free_statement($stmt);
    $q = "SELECT USERID FROM Q5 WHERE NUM = (SELECT MAX(NUM) FROM Q5)";
    $stmt = oci_parse($dao->con, $q);
    oci_execute($stmt, OCI_DEFAULT);
    oci_fetch_all($stmt, $rs);
    foreach ($rs as $rows) {
        echo "";
        foreach ($rows as $col_values) {
            echo "User contributed most comments on threads: " . $dao->fetchUserName($col_values) . '<br>';
        }
    }
}

function loginChk($conn)
{
    isset($_POST["email"]);
    isset($_POST["pass1"]);
    global $username, $password, $password_enc;
    /* *** A1 - Injection attacks, converted all SQL statments to include binding/placeholders to prevent injection attacks.
     *
     */
    //check password in database
    $s = oci_parse($conn, "SELECT username FROM tblusers WHERE username=:username_prefix AND password=:pw");
    oci_bind_by_name($s, ':username_prefix', $username);
    oci_bind_by_name($s, ':pw', $password_enc);
    oci_execute($s);
    //evaluate based on db information
    $res = oci_fetch_row($s);
    if ($res) {
        oci_free_statement($s);
        oci_close($conn);
        return true;
    } else {
        oci_free_statement($s);
        oci_close($conn);
        echo "Username or password were incorrect.</br> Please try to login again, <a href='login.html'>click to return to login page</a>.";
        return false;
    }
}

 public function endQuery($sqlQuery)
 {
     if (!oci_free_statement($this->queries[$sqlQuery])) {
         echo "******** Unable to clean oci resource " . $this->queries[$sqlQuery];
     }
     unset($this->queries[$sqlQuery]);
 }

function get_list($data)
{
    $results = array();
    $games = array();
    $conn = oci_connect('malz', '1Qaz2wsx', '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=db1.chpc.ndsu.nodak.edu)(Port=1521)))(CONNECT_DATA=(SID=cs)))');
    //Select customer with last name from field
    $userQuery = 'select userName from Account where listId = :data';
    $listQuery = 'select * from ListGame, Game where listId = :data and ListGame.gameId = Game.gameId';
    $stid = oci_parse($conn, $userQuery);
    $stid2 = oci_parse($conn, $listQuery);
    oci_bind_by_name($stid, ':data', $data);
    oci_bind_by_name($stid2, ':data', $data);
    oci_execute($stid, OCI_DEFAULT);
    //iterate through each row
    while ($row = oci_fetch_array($stid, OCI_ASSOC)) {
        $results[] = $row;
    }
    oci_execute($stid2, OCI_DEFAULT);
    while ($row = oci_fetch_array($stid2, OCI_ASSOC)) {
        $games[] = $row;
    }
    $results[] = $games;
    echo json_encode($results);
    oci_free_statement($stid);
    oci_free_statement($stid2);
    oci_close($conn);
}

 public function execute($query)
 {
     $q_result = oci_parse($this->connection, $query);
     oci_execute($q_result);
     //Gibt den Speicher wieder frei
     oci_free_statement($q_result);
 }

 public function getById($id)
 {
     $this->conex = DataBase::getInstance();
     $stid = oci_parse($this->conex, "SELECT *\n\t\t\tFROM FISC_CIUDADANO WHERE ID_CIUDADANO=:id");
     if (!$stid) {
         $e = oci_error($this->conex);
         trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
     }
     // Realizar la lógica de la consulta
     oci_bind_by_name($stid, ':id', $id);
     $r = oci_execute($stid);
     if (!$r) {
         $e = oci_error($stid);
         trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
     }
     // Obtener los resultados de la consulta
     $alm = new FiscCiudadano();
     while ($fila = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) {
         $it = new ArrayIterator($fila);
         while ($it->valid()) {
             $alm->__SET(strtolower($it->key()), $it->current());
             $it->next();
         }
     }
     //Libera los recursos
     oci_free_statement($stid);
     // Cierra la conexión Oracle
     oci_close($this->conex);
     //retorna el resultado de la consulta
     return $alm;
 }

function actualizarPassword($newpassword, $token)
{
    $conex = DataBase::getInstance();
    $stid = oci_parse($conex, "UPDATE FISC_USERS SET \n\t\t\t\t\t\tpassword=:newpassword\n\t\t\t\t    WHERE token=:token");
    if (!$stid) {
        oci_free_statement($stid);
        oci_close($conex);
        return false;
    }
    // Realizar la lógica de la consulta
    oci_bind_by_name($stid, ':token', $token);
    oci_bind_by_name($stid, ':newpassword', $newpassword);
    $r = oci_execute($stid, OCI_NO_AUTO_COMMIT);
    if (!$r) {
        oci_free_statement($stid);
        oci_close($conex);
        return false;
    }
    $r = oci_commit($conex);
    if (!$r) {
        oci_free_statement($stid);
        oci_close($conex);
        return false;
    }
    oci_free_statement($stid);
    // Cierra la conexión Oracle
    oci_close($conex);
    return true;
}

/**
 * Insert image data to database (recoreded_data and thumbnail).
 * First generate an unique image id, then insert image to recoreded_data and resized image to thubnail along with 
 *   other given data
 */
function uploadImage($conn, $sensor_id, $date_created, $description)
{
    $image_id = generateId($conn, "images");
    if ($image_id == 0) {
        return;
    }
    $image2 = file_get_contents($_FILES['file_image']['tmp_name']);
    $image2tmp = resizeImage($_FILES['file_image']);
    $image2Thumbnail = file_get_contents($image2tmp['tmp_name']);
    // encode the stream
    $image = base64_encode($image2);
    $imageThumbnail = base64_encode($image2Thumbnail);
    $sql = "INSERT INTO images (image_id, sensor_id, date_created, description, thumbnail, recoreded_data)\n                      VALUES(" . $image_id . ", " . $sensor_id . ", TO_DATE('" . $date_created . "', 'DD/MM/YYYY hh24:mi:ss'), '" . $description . "', empty_blob(), empty_blob())\n                       RETURNING thumbnail, recoreded_data INTO :thumbnail, :recoreded_data";
    $result = oci_parse($conn, $sql);
    $recoreded_dataBlob = oci_new_descriptor($conn, OCI_D_LOB);
    $thumbnailBlob = oci_new_descriptor($conn, OCI_D_LOB);
    oci_bind_by_name($result, ":recoreded_data", $recoreded_dataBlob, -1, OCI_B_BLOB);
    oci_bind_by_name($result, ":thumbnail", $thumbnailBlob, -1, OCI_B_BLOB);
    $res = oci_execute($result, OCI_DEFAULT) or die("Unable to execute query");
    if ($recoreded_dataBlob->save($image) && $thumbnailBlob->save($imageThumbnail)) {
        oci_commit($conn);
    } else {
        oci_rollback($conn);
    }
    oci_free_statement($result);
    $recoreded_dataBlob->free();
    $thumbnailBlob->free();
    echo "New image is added with image_id ->" . $image_id . "<br>";
}

 public function __destruct()
 {
     if (!is_null($this->_cursor)) {
         oci_free_statement($this->_cursor);
         $this->_cursor = null;
     }
 }

function db_closequery($qhandle)
{
	if(function_exists("oci_free_statement"))
		oci_free_statement($qhandle);
	else
		ocifreestatement($qhandle);
}

function get_filteredGames($data)
{
    // The connection string is loooooooong. It's easiest to copy/paste this line. Remember to replace 'username' and 'password'!
    $conn = oci_connect('malz', '1Qaz2wsx', '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=db1.chpc.ndsu.nodak.edu)(Port=1521)))(CONNECT_DATA=(SID=cs)))');
    if ($data === 'all') {
        $results = array();
        $query = 'select * from Game';
        $stid = oci_parse($conn, $query);
        oci_bind_by_name($stid, ':data', $data);
        oci_execute($stid);
        //iterate through each row
        while ($row = oci_fetch_array($stid, OCI_ASSOC)) {
            $results[] = $row;
        }
        echo json_encode($results);
        oci_free_statement($stid);
        oci_close($conn);
    } else {
        $results = array();
        $data = $data . '%';
        $query = 'select * from Game where gameName like :data';
        $stid = oci_parse($conn, $query);
        oci_bind_by_name($stid, ':data', $data);
        oci_execute($stid);
        //iterate through each row
        while ($row = oci_fetch_array($stid, OCI_ASSOC)) {
            $results[] = $row;
        }
        echo json_encode($results);
        oci_free_statement($stid);
        oci_close($conn);
    }
}

 public function free_result()
 {
     if (is_resource($this->result_id)) {
         oci_free_statement($this->result_id);
         $this->result_id = FALSE;
     }
 }

function cargarArray($sentencia)
{
    include dirname(__FILE__) . '/conectar_ORACLE.php';
    $array = array();
    $sentenciaExec = oci_parse($c, $sentencia);
    oci_execute($sentenciaExec);
    $error = 0;
    $k = 0;
    $ncols = oci_num_fields($sentenciaExec);
    for ($i = 1; $i <= $ncols; ++$i) {
        $colname = oci_field_name($sentenciaExec, $i);
        $array[0][$k] = $colname;
        $k++;
    }
    $cont = 0;
    $j = 1;
    $k = 0;
    while ($row = oci_fetch_array($sentenciaExec, OCI_BOTH + OCI_RETURN_NULLS)) {
        while ($cont < $ncols) {
            $array[$j][$cont] = $row[$cont];
            $cont++;
        }
        $cont = 0;
        $k = 0;
        $j++;
    }
    if (oci_num_rows($sentenciaExec) == 0) {
        oci_free_statement($sentenciaExec);
        return false;
    } else {
        oci_free_statement($sentenciaExec);
        return $array;
    }
}

 /**
  * closes the cursor, allowing the statement to be executed again
  */
 public function closeCursor()
 {
     if (!$this->_stmt) {
         return false;
     }
     oci_free_statement($this->_stmt);
     $this->_stmt = false;
 }

 public function close()
 {
     if ($this->stmt) {
         oci_free_statement($this->stmt);
         $this->stmt = null;
     }
     $this->current = null;
 }

 public function closeCursor()
 {
     if (!$this->_cursorOpen) {
         return true;
     }
     $this->_cursorOpen = false;
     return oci_free_statement($this->_sth);
 }

 /**
  * This function frees the command reference.
  *
  * @access public
  * @override
  */
 public function free()
 {
     if ($this->command !== NULL) {
         @oci_free_statement($this->command);
         $this->command = NULL;
         $this->record = FALSE;
     }
 }