/**
 * 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;
}

 public function getDetalle()
 {
     $sql = "SELECT trigger_name, trigger_type, triggering_event, table_name, status, description, trigger_body FROM user_triggers WHERE trigger_name = UPPER(:v_trigger_name)";
     $stmt = oci_parse($this->getConnection(), $sql);
     oci_bind_by_name($stmt, ":v_trigger_name", $this->objectName);
     if (!@oci_execute($stmt)) {
         $e = oci_error($stmt);
         $this->setMensaje("Error al obtener los datos del trigger '{$this->objectName}' de la tabla user_triggers - {$e['message']}");
         return false;
     }
     $row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS);
     if (empty($row)) {
         $this->setMensaje("No se pudo encontrar el trigger especificado en la tabla user_triggers");
         return false;
     }
     $this->triggerType = $row['TRIGGER_TYPE'];
     $this->triggeringEvent = $row['TRIGGERING_EVENT'];
     $this->affectedTable = $row['TABLE_NAME'];
     $this->triggerStatus = $row['STATUS'];
     $this->description = $row['DESCRIPTION'];
     $this->triggerSql = $row['TRIGGER_BODY'];
     if ($this->triggerStatus != 'ENABLED') {
         $this->setMensaje("El trigger se encuentra inhabilitado");
         return false;
     }
     return true;
 }

 /**
  * @see ResultSet::next()
  */
 function next()
 {
     // no specific result position available
     // Returns an array, which corresponds to the next result row or FALSE
     // in case of error or there is no more rows in the result.
     $this->fields = oci_fetch_array($this->result, $this->fetchmode + OCI_RETURN_NULLS + OCI_RETURN_LOBS);
     if (!$this->fields) {
         // grab error via array
         $error = oci_error($this->result);
         if (!$error) {
             // end of recordset
             $this->afterLast();
             return false;
         } else {
             throw new SQLException('Error fetching result', $error['code'] . ': ' . $error['message']);
         }
     }
     // Oracle returns all field names in uppercase and associative indices
     // in the result array will be uppercased too.
     if ($this->fetchmode === ResultSet::FETCHMODE_ASSOC && $this->lowerAssocCase) {
         $this->fields = array_change_key_case($this->fields, CASE_LOWER);
     }
     // Advance cursor position
     $this->cursorPos++;
     return true;
 }

 public function testConnection($db_info)
 {
     if (!$this->isValid($db_info)) {
         return false;
     }
     foreach ($db_info as $key => $value) {
         if (empty($db_info[$key])) {
             unset($db_info[$key]);
         }
     }
     if ($db_info['db_oracle_type'] == 'tns') {
         $connect = @oci_connect($db_info['db_user'], $db_info['db_password'], $db_info['db_net_service_name']);
         if (!$connect) {
             $error = oci_error();
             throw new \Exception($error['message'], $error['code']);
         }
     } else {
         $dsn = $db_info['db_host'];
         $dsn .= ':' . $db_info['db_port'];
         $dsn .= '/' . $db_info['db_service_name'];
         $connect = @oci_connect($db_info['db_user'], $db_info['db_password'], $dsn);
         if (!$connect) {
             $error = oci_error();
             throw new \Exception($error['message'], $error['code']);
         }
     }
     return true;
 }

 /**
  * Obtiene el SQL de la funcion especificada
  * @return String or false
  */
 protected function getObjectSql()
 {
     if ($this->remote) {
         $sql = "select line, text from all_source where name = UPPER(:v_function_name) and type = :v_object_type order by name, type, line";
     } else {
         $sql = "select line, text from user_source where name = UPPER(:v_function_name) and type = :v_object_type order by name, type, line";
     }
     $stmt = oci_parse($this->getConnection(), $sql);
     oci_bind_by_name($stmt, ":v_function_name", $this->objectName);
     oci_bind_by_name($stmt, ":v_object_type", $this->objectType);
     if (!@oci_execute($stmt)) {
         $e = oci_error($stmt);
         $this->setMensaje("Error al obtener el SQL del objeto {$this->objectType} '{$this->objectName}' - {$e['message']}");
         return false;
     }
     $sqlResult = '';
     while ($row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS)) {
         $sqlResult .= $row['TEXT'];
     }
     $this->sourceSql = $sqlResult;
     if (empty($sqlResult)) {
         $this->setMensaje("No se pudo obtener el SQL del objeto {$this->objectType} '{$this->objectName}'");
         return false;
     }
     return $this->sourceSql;
 }

 function executePlainSQL($cmdstr)
 {
     //takes a plain (no bound variables) SQL command and executes it
     //echo "<br>running ".$cmdstr."<br>";
     global $db_conn, $success;
     $statement = OCIParse($db_conn, $cmdstr);
     //There is a set of comments at the end of the file that describe some of the OCI specific functions and how they work
     if (!$statement) {
         $e = OCI_Error($db_conn);
         // For OCIParse errors pass the
         // connection handle
         echo "<div class='alert alert-danger' role='alert'>";
         echo "<span class='glyphicon glyphicon-exclamation-sign' aria-hidden='true'></span>";
         echo "<span class='sr-only'>Error:</span>";
         echo htmlentities($e['message']);
         echo "<br>Cannot parse the following command: " . $cmdstr . "<br>";
         echo "</div>";
         $success = False;
     }
     $r = OCIExecute($statement, OCI_DEFAULT);
     if (!$r) {
         #echo "<br>Cannot execute the following command: " . $cmdstr . "<br>";
         $e = oci_error($statement);
         // For OCIExecute errors pass the statementhandle
         echo "<div class='alert alert-danger' role='alert'>";
         echo "<span class='glyphicon glyphicon-exclamation-sign' aria-hidden='true'></span>";
         echo "<span class='sr-only'>Error:</span>";
         echo htmlentities($e['message']);
         echo "<br>Cannot execute the following command: " . $cmdstr . "<br>";
         echo "</div>";
         $success = False;
     } else {
     }
     return $statement;
 }

 public function getError()
 {
     set_error_handler($this->getErrorHandler());
     $error = oci_error($this->resource);
     restore_error_handler();
     return $error;
 }

 /**
  * Excecutes a statement
  *
  * @return boolean
  */
 public function query($sql, array $params = array())
 {
     $this->numRows = 0;
     $this->numFields = 0;
     $this->rowsAffected = 0;
     $this->arrayResult = null;
     $this->result = $stid = oci_parse($this->dbconn, $sql);
     # Bound variables
     if (count($params)) {
         foreach ($params as $var => $value) {
             oci_bind_by_name($stid, $var, $value);
         }
     }
     $r = $this->transac_mode ? @oci_execute($stid, OCI_NO_AUTO_COMMIT) : @oci_execute($stid, OCI_COMMIT_ON_SUCCESS);
     if (!$r) {
         $error = oci_error($this->result);
         $this->error($error["code"], $error["message"]);
         if (count($this->errors)) {
             throw new Exception($error["message"], $error["code"]);
         } else {
             throw new Exception("Unknown error!");
         }
     }
     # This should be before of getArrayResult() because oci_fetch() is incremental.
     $this->rowsAffected = oci_num_rows($stid);
     $rows = $this->getArrayResult();
     $this->numRows = count($rows);
     $this->numFields = oci_num_fields($stid);
     if ($this->transac_mode) {
         $this->transac_result = is_null($this->transac_result) ? $this->result : $this->transac_result && $this->result;
     }
     return $this->result;
 }

 function executePlainSQL($cmdstr)
 {
     //takes a plain (no bound variables) SQL command and executes it
     //echo "<br>running ".$cmdstr."<br>";
     global $db_conn, $success;
     $statement = OCIParse($db_conn, $cmdstr);
     //There is a set of comments at the end of the file that describe some of the OCI specific functions and how they work
     if (!$statement) {
         echo "<br>Cannot parse the following command: " . $cmdstr . "<br>";
         $e = OCI_Error($db_conn);
         // For OCIParse errors pass the
         // connection handle
         echo htmlentities($e['message']);
         $success = False;
     }
     $r = OCIExecute($statement, OCI_DEFAULT);
     if (!$r) {
         echo "<br>Cannot execute the following command: " . $cmdstr . "<br>";
         $e = oci_error($statement);
         // For OCIExecute errors pass the statementhandle
         echo htmlentities($e['message']);
         $success = False;
     } else {
     }
     return $statement;
 }

 protected function query($query, $returnresult = false)
 {
     if (!$this->isConnected()) {
         $this->connect();
     }
     if ($this->isConnected()) {
         $this->myLog->log(LOG_DEBUG, 'DB query is: ' . $query);
         # OCI mode
         $result = oci_parse($this->dbh, $query);
         if (!oci_execute($result)) {
             $this->myLog->log(LOG_INFO, 'Database query error: ' . preg_replace('/\\n/', ' ', print_r(oci_error($result), true)));
             $this->dbh = Null;
             return false;
         }
         $this->result = $result;
         if ($returnresult) {
             return $this->result;
         } else {
             return true;
         }
     } else {
         $this->myLog->log(LOG_CRIT, 'No database connection');
         return false;
     }
 }

 public function buscarPorProcedimiento()
 {
     $sql = "SELECT job, to_char(last_date, 'DD/MM/YYYY') last_date, last_sec, to_char(next_date, 'DD/MM/YYYY') next_date, next_sec, interval, failures, what from user_jobs WHERE UPPER(what) LIKE UPPER('%' || :v_procedure_name || '%')";
     $stmt = oci_parse($this->getConnection(), $sql);
     oci_bind_by_name($stmt, ":v_procedure_name", $this->objectName);
     if (!@oci_execute($stmt)) {
         $e = oci_error($stmt);
         $this->setMensaje("Error al obtener los datos del job que ejecuta el proceso '{$this->objectName}' de la tabla user_jobs - {$e['message']}");
         $this->setEstado(false);
     }
     $row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS);
     if (empty($row)) {
         $this->setEstado(false);
     }
     $this->jobId = $row['JOB'];
     $this->lastDate = $row['LAST_DATE'];
     $this->lastSec = $row['LAST_SEC'];
     $this->nextDate = $row['NEXT_DATE'];
     $this->nextSec = $row['NEXT_SEC'];
     $this->interval = $row['INTERVAL'];
     $this->failures = $row['FAILURES'];
     $this->jobSql = $row['WHAT'];
     $this->setEstado(true);
     return $this->getEstado();
 }

 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;
 }

 public function getUserObjectData()
 {
     $sql = "SELECT object_name, object_type, TO_CHAR(created, 'DD/MM/YYYY') AS created, TO_CHAR(last_ddl_time, 'DD/MM/YYYY') AS last_ddl_time, status FROM user_objects WHERE object_name = UPPER(:v_obj_name) AND object_type = UPPER(:v_obj_type)";
     $stmt = oci_parse($this->getConnection(), $sql);
     oci_bind_by_name($stmt, ":v_obj_name", $this->objectName);
     oci_bind_by_name($stmt, ":v_obj_type", $this->objectType);
     if (!@oci_execute($stmt)) {
         $e = oci_error($stmt);
         $this->setMensaje("Error al obtener los datos del objeto '{$this->objectName}' de la tabla user_objects - {$e['message']}");
         return false;
     }
     $row = @oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS);
     if (empty($row)) {
         $this->setMensaje("No se pudo encontrar el objeto '{$this->objectName}' en la tabla user_objects");
         return false;
     }
     $this->fechaCreacion = $row['CREATED'];
     $this->fechaModificacion = $row['LAST_DDL_TIME'];
     $this->oracleStatus = $row['STATUS'];
     if ($this->oracleStatus != 'VALID') {
         $this->setMensaje("El objeto '{$this->objectName}' tiene el estado '{$this->oracleStatus}' en la tabla user_objects");
         return false;
     }
     return true;
 }

 public function open_connection()
 {
     $connection_string = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)\n            (HOST = " . DB_SERVER . ")(PORT = " . DB_PORT . ")))(CONNECT_DATA=(SID=" . DB_NAME . ")))";
     $this->connection = oci_connect(DB_USER, DB_PASS, $connection_string);
     if (!$this->connection) {
         die("Database connection failed: " . oci_error());
     }
 }

function log_if_error($resource)
{
    if (!$resource) {
        $error = oci_error();
        echo $error['message'];
        exit;
    }
}

    protected static function oci_error($resource = null) {
        $error = isset($resource) ? oci_error($resource) : oci_error();
        if ($error === FALSE) {
            $error = array('message' => 'No error is found');
        }

        return $error;
    }

 public function __construct(array $config)
 {
     $this->_connection = oci_connect($config['username'], $config['password'], $config['host'] . ":" . $config['port'] . "/" . $config['instance_name'], $config['charset']);
     if (!$this->_connection) {
         $e = oci_error();
         trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
     }
 }

 public function get_ams_student($student_id = NULL)
 {
     $conn = oci_connect('AMS_QUERIES', 'Oo_Hecha1_rohm3', '//192.168.170.171:1522/ACADEMIC');
     if (!$conn) {
         $e = oci_error();
         trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
     } else {
         if ($student_id == NULL) {
             $sql = "SELECT * FROM GAOWNER.VIEW_STUDENT_DETAILS";
         } else {
             $sql = 'SELECT * FROM GAOWNER.VIEW_STUDENT_DETAILS WHERE STUDENT_NO LIKE \'%' . $student_id . '%\'';
         }
         $rs4 = oci_parse($conn, $sql);
         oci_execute($rs4);
         $rows = oci_num_rows($rs4);
         $t = 0;
         while (OCIFetch($rs4)) {
             $t++;
             $name1 = ociresult($rs4, "SURNAME");
             $dob = ociresult($rs4, "DOB");
             $gender = ociresult($rs4, "GENDER");
             $oname1 = ociresult($rs4, "OTHER_NAMES");
             $STUDENT_NO = ociresult($rs4, "STUDENT_NO");
             $COURSES = ociresult($rs4, "COURSES");
             $GUARDIAN_NAME1 = ociresult($rs4, "GUARDIAN_NAME");
             $MOBILE_NO = ociresult($rs4, "MOBILE_NO");
             $EMAIL = ociresult($rs4, "EMAIL");
             $FACULTIES = ociresult($rs4, "FACULTIES");
             //  details to be saved
             $name = str_replace("'", "", "{$name1}");
             $oname = str_replace("'", "", "{$oname1}");
             $GUARDIAN_NAME = str_replace("'", "", "{$GUARDIAN_NAME1}");
             if (!empty($STUDENT_NO)) {
                 $exists = $this->student_exists($STUDENT_NO);
                 $data = array('title' => '', 'Surname' => $name, 'Other_names' => $oname, 'DOB' => $dob, 'contact' => $MOBILE_NO, 'gender' => $gender, 'student_Number' => $STUDENT_NO, 'courses' => $FACULTIES, 'GUARDIAN_NAME' => $GUARDIAN_NAME, 'faculty' => $FACULTIES);
                 if (!$exists) {
                     $this->db->insert('student', $data);
                 } else {
                     $this->db->where('student_Number', $STUDENT_NO);
                     $this->db->update('student', $data);
                 }
                 $date = date("Y-m-d H:i:s");
                 //  data for patients patient date, visit type, strath number created by and modified by fields
                 if ($student_id != NULL) {
                     $patient_data = array('patient_number' => $this->create_patient_number(), 'patient_date' => $date, 'visit_type_id' => 1, 'strath_no' => $STUDENT_NO, 'created_by' => $this->session->userdata('personnel_id'), 'modified_by' => $this->session->userdata('personnel_id'));
                     $this->db->insert('patients', $patient_data);
                     return $this->db->insert_id();
                 }
             } else {
                 $this->session->set_userdata("error_message", "Student could not be found");
                 return FALSE;
             }
         }
         if ($student_id != NULL) {
             return TRUE;
         }
     }
 }

function connect()
{
    $conn = oci_connect('olexson', 'yohatdog4');
    if (!$conn) {
        $e = oci_error();
        trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
    }
    return $conn;
}

 /**
  * Construct method where configuration is applied
  * @param string $dbIdent  Identifier of connection parameters section in config file
  */
 public function __construct($dbIdent)
 {
     $connData = Pokelio_Global::getConfig('DATABASES', 'Pokelio')->{$dbIdent};
     $this->connection = oci_connect($connData->USER, $connData->PASSWORD, $connData->DSN);
     if (!$this->connection) {
         $e = oci_error();
         trigger_error("Error connecting to database." . NL . htmlentities($e['message'], ENT_QUOTES) . NL);
     }
 }