public function readCursor($storedProcedure, $binds)
{
//
// This function needs two parameters:
//
// $storedProcedure - the name of the stored procedure to call a chamar. Ex:
// my_schema.my_package.my_proc(:param)
//
// $binds - receives an array of associative arrays with: parameter names,
// values and sizes
//
// WARNING: The first parameter must be consistent with the second one
$conn = oci_connect('SECMAN', 'SECMAN', '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.10.24)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = cisqa)))');
if ($conn) {
// Create the statement and bind the variables (parameter, value, size)
$stid = oci_parse($conn, 'begin :cursor := ' . $storedProcedure . '; end;');
foreach ($binds as $variable) {
oci_bind_by_name($stid, $variable["parameter"], $variable["value"], $variable["size"]);
}
// Create the cursor and bind it
$p_cursor = oci_new_cursor($conn);
oci_bind_by_name($stid, ':cursor', $p_cursor, -1, OCI_B_CURSOR);
// Execute the Statement and fetch the data
oci_execute($stid);
oci_execute($p_cursor, OCI_DEFAULT);
oci_fetch_all($p_cursor, $data, null, null, OCI_FETCHSTATEMENT_BY_ROW);
// Return the data
return $data;
}
}
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;
}
/**
* Executes a prepared statement.
*
* @param array $args an array of values with as many elements as there are bound parameters in the statement
* @return boolean returns true on success or false on failure
*/
public function execute($args = array())
{
foreach ($args as $key => $value) {
oci_bind_by_name($this->statement, ":" . $key, $args[$key]);
}
return oci_execute($this->statement, OCI_DEFAULT);
}
/**
* 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 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);
}
}
protected function getDetalle()
{
$sql = "SELECT synonym_name, table_owner, table_name, db_link FROM user_synonyms WHERE synonym_name = UPPER(:v_synonym_name)";
$stmt = oci_parse($this->getConnection(), $sql);
oci_bind_by_name($stmt, ":v_synonym_name", $this->objectName);
if (!@oci_execute($stmt)) {
$e = oci_error($stmt);
$this->setMensaje("Error al obtener los datos del sinónimo '{$this->objectName}' de la tabla user_synonyms - {$e['message']}");
$this->setEstado(false);
return false;
}
$row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS);
if (empty($row)) {
$sqlPublic = "SELECT * FROM all_synonyms WHERE synonym_name = UPPER(:v_synonym_name) AND owner = 'PUBLIC'";
$stmt2 = oci_parse($this->getConnection(), $sqlPublic);
oci_bind_by_name($stmt2, ":v_synonym_name", $this->objectName);
if (!@oci_execute($stmt2)) {
$e = oci_error($stmt2);
$this->setMensaje("Error al obtener los datos del sinónimo '{$this->objectName}' de la tabla all_synonyms - {$e['message']}");
$this->setEstado(false);
return false;
}
$row = oci_fetch_array($stmt2, OCI_ASSOC | OCI_RETURN_NULLS);
if (empty($row)) {
$this->setMensaje("No se encontró el sinónimo '{$this->objectName}' en la tabla user_synonyms");
$this->setEstado(false);
return false;
}
}
$this->tableOwner = $row['TABLE_OWNER'];
$this->tableName = $row['TABLE_NAME'];
$this->dbLinkName = $row['DB_LINK'];
$this->setEstado(true);
return true;
}
function insertImage($conn, $photo_id, $owner_name, $descriptive_info, $thumbnail, $photo)
{
$photo_blob = oci_new_descriptor($conn, OCI_D_LOB);
$thumbnail_blob = oci_new_descriptor($conn, OCI_D_LOB);
$subject = $descriptive_info[0];
$place = $descriptive_info[1];
$date_time = $descriptive_info[2];
$description = $descriptive_info[3];
$permitted = $descriptive_info[4];
$sql = 'INSERT INTO images (photo_id, owner_name, permitted, subject, place,
timing, description, thumbnail, photo) VALUES (:photoid, :ownername,
:permitted, :subject, :place, TO_DATE(:datetime, \'MM/DD/YYYY\'), :description, empty_blob(),
empty_blob()) returning thumbnail, photo into :thumbnail, :photo';
$stid = oci_parse($conn, $sql);
oci_bind_by_name($stid, ':photoid', $photo_id);
oci_bind_by_name($stid, ':ownername', $owner_name);
oci_bind_by_name($stid, ':permitted', $permitted);
oci_bind_by_name($stid, ':subject', $subject);
oci_bind_by_name($stid, ':place', $place);
oci_bind_by_name($stid, ':datetime', $date_time);
oci_bind_by_name($stid, ':description', $description);
oci_bind_by_name($stid, ':thumbnail', $thumbnail_blob, -1, OCI_B_BLOB);
oci_bind_by_name($stid, ':photo', $photo_blob, -1, OCI_B_BLOB);
$res = oci_execute($stid, OCI_DEFAULT);
if ($thumbnail_blob->save($thumbnail) && $photo_blob->save($photo)) {
oci_commit($conn);
} else {
oci_rollback($conn);
}
oci_free_statement($stid);
$photo_blob->free();
$thumbnail_blob->free();
}
function dispCart()
{
$cartLen = count($_SESSION['cart']);
if ($cartLen < 1) {
echo 'You have no items in your cart.<br><a href="main.php">Keep shopping</a><br>';
die;
}
//for loop to iterate through cart items
for ($i = 0; $i < $cartLen; $i++) {
if ($cartLen > 0) {
$newconn = conndb();
//sql
$s = oci_parse($newconn, "select * from PRODUCT where PRODUCTID=:pid_prefix");
$plook = $_SESSION['cart'][$i]['productid'];
oci_bind_by_name($s, ':pid_prefix', $plook);
oci_execute($s);
//fetch a single row depending on product id
$res = oci_fetch_assoc($s);
echo "Product name: ", $res['PRODUCTNAME'], " Price: ", $res['PRODUCTPRICE'];
echo '<a href="cart.php?del=' . $i . '"> Remove item</a><br>';
}
}
if ($cartLen > 0) {
echo '<a href="checkout.php">Proceed to checkout</a><br>';
}
}
public static function bindValues($stmt, $binds, $types)
{
foreach ($binds as $key => $field) {
switch ($types[$key]) {
case 'numeric':
case 'string':
case 'date':
case 'time':
case 'datetime':
oci_bind_by_name($stmt, ":" . ($key + 1), $binds[$key], -1);
break;
case 'int':
oci_bind_by_name($stmt, ":" . ($key + 1), $binds[$key], -1, SQLT_INT);
break;
case 'boolean':
oci_bind_by_name($stmt, ":" . ($key + 1), $binds[$key], -1);
break;
case 'blob':
oci_bind_by_name($stmt, ":" . ($key + 1), $binds[$key], -1, SQLT_BLOB);
break;
case 'custom':
oci_bind_by_name($stmt, ":" . ($key + 1), $binds[$key], -1);
break;
}
}
return true;
}
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();
}
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;
}
public function bindByName($bvName, &$variable, $maxLength = -1, $type = SQLT_CHR)
{
set_error_handler(static::getErrorHandler());
$isSuccess = oci_bind_by_name($this->resource, $bvName, $variable, $maxLength, $type);
restore_error_handler();
return $isSuccess;
}
/**
* 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;
}
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 prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
{
/* init */
$this->hasAutoincrement = false;
/* parent prep */
$result = parent::prepareStatement($adapter, $statementContainer);
/* oci8 with autoincrement */
if ($statementContainer instanceof \Zend\Db\Adapter\Driver\Oci8\Statement && $this->autoincrement !== null) {
/* get sequence */
if ($this->sequence === null) {
$this->sequence = 'SEQ_' . $this->table;
}
/* replace ai field with sequence & move ai field binding to the end with returning */
$count = 0;
$sql = preg_replace('/:' . $this->autoincrement . '\\s*/', $this->sequence . '.NEXTVAL', $statementContainer->getSql(), 1, $count) . ' RETURNING "' . $this->autoincrement . '" INTO :' . $this->autoincrement;
/* anything replaced? */
if ($count > 0) {
/* prep statement to prep resource */
$statementContainer->setSql($sql);
$statementContainer->prepare();
/* unset ai field */
$statementContainer->getParameterContainer()->offsetUnset($this->autoincrement);
/* get ai field position on values */
$position = array_search($this->autoincrement, $this->columns);
$this->values[$position] = 0;
$this->hasAutoincrement = true;
oci_bind_by_name($statementContainer->getResource(), $this->autoincrement, $this->values[$position], -1, SQLT_INT);
}
}
//oci8 AI
return $result;
}
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;
}
/**
* 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>";
}
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);
}
function query($query)
{
if (!$this->link) {
$this->connect();
}
$matches = array();
preg_match_all('/\' ?<<::(.*?)::>> *\'/', $query, $matches, PREG_SET_ORDER);
$replacements = array();
foreach ($matches as $idx => $match) {
$query = str_replace($match[0], ':' . $idx, $query);
$replacements[':' . $idx] = str_replace(array('<|<::', '::>|>'), array('<<::', '::>>'), $match[1]);
}
$result = oci_parse($this->link, $query);
foreach ($replacements as $key => $replacement) {
oci_bind_by_name($result, $key, $replacements[$key]);
}
if (!$result) {
return false;
}
$success = @oci_execute($result);
if (!$success) {
return false;
}
return $result;
}
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;
}
}
function actualizarQuejas($quejas)
{
//$ids = transformarArray($denuncias,",");
$conex = DataBase::getInstance();
foreach ($quejas as $key => $value) {
$conex = DataBase::getInstance();
$stid = oci_parse($conex, "UPDATE FISC_DENUNCIAS_JURIDICAS SET \n\t\t\t\tASIGNADA=1 , ASIGNADOPOR=:sesion_cod\n\t\t\t\tWHERE ID_DENUNCIA=:ID");
if (!$stid) {
oci_free_statement($stid);
oci_close($conex);
return false;
}
// Realizar la lógica de la consulta
oci_bind_by_name($stid, ':ID', $value);
oci_bind_by_name($stid, ':sesion_cod', $_SESSION['USUARIO']['codigo_usuario']);
$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;
}
/**
Does the oracle-dependent work to bind the parameters to the statement.
@param $aParameters The parameters to bind.
*/
protected function doBind($aParameters)
{
foreach ($aParameters as $sName => $mValue) {
if (isset($this->aParameters[$sName])) {
// Don't use $mValue here because oci_bind_by_name binds by reference.
oci_bind_by_name($this->rStatement, ':' . $sName, $aParameters[$sName]) or burn('DatabaseException', sprintf(_WT("Failed to bind the parameter \"%s\" with the following error:\n%s"), $sName, array_value(oci_error($this->rStatement), 'message')));
}
}
}
function getUltimaRevisioVehicle($codi_vehicle)
{
$oci = new Oci($_SESSION['user']['usuari'], $_SESSION['user']['passwd']);
$req = "SELECT \r\n\t\t\t\t\t\tdata_l, \r\n\t\t\t\t\t\tkms \r\n\t\t\t\t\tFROM \r\n\t\t\t\t\t\trevisio \r\n\t\t\t\t\tWHERE codi IN ( \r\n\t\t\t\t\t\tselect max(rev.codi) \r\n\t\t\t\t\t\tfrom revisio rev \r\n\t\t\t\t\t\twhere rev.codi_vehicle = :codi_vehicle \r\n\t\t\t\t\t\tgroup by rev.codi_vehicle \r\n\t\t\t\t\t)";
$stid = oci_parse($oci->getConnection(), $req);
oci_bind_by_name($stid, ":codi_vehicle", $codi_vehicle);
oci_execute($stid);
$ultima = oci_fetch_object($stid);
return $ultima;
}
function HakAksesUser($username, $nm_field, $conn)
{
$sql = "SELECT {$nm_field} FROM WELTES_SEC_ADMIN.WELTES_AUTHENTICATION AUT LEFT OUTER JOIN WELTES_SEC_ADMIN.WELTES_AUTH_LEVEL LEV ON LEV.APP_USR_CODE=AUT.APP_USR_CODE WHERE APP_USERNAME = :UN_BV ";
$sqlSqry = oci_parse($conn, $sql);
oci_bind_by_name($sqlSqry, ":UN_BV", $username);
oci_execute($sqlSqry);
$rowSqry = oci_fetch_array($sqlSqry);
// echo $rowSqry[0];
return $rowSqry[0];
}
function show_foto($id)
{
$conn = oci_connect('mmAdmin', 'mmAdmin', '//localhost/MATCHMEDB');
$stid = oci_parse($conn, "begin :result := get_foto(person_id => :codigo); end;");
oci_bind_by_name($stid, ":result", $resul, 200);
oci_bind_by_name($stid, ":codigo", $id);
oci_execute($stid);
oci_close($conn);
return $resul;
}
/**
* Run a SQL or PL/SQL statement
*
* Call like:
* Db::execute("insert into mytab values (:c1, :c2)",
* [[":c1", $c1, -1],
* [":c2", $c2, -1]])
*
* For returned bind values:
* Db::execute("begin :r := myfunc(:p); end",
* [[":r", &$r, 20],
* [":p", $p, -1]])
*
* Note: this performs a commit.
*
* @param string $sql The statement to run
* @param array $bindvars Binds. An array of (bv_name, php_variable, length)
*/
public function execute($sql, $bindvars = [])
{
$this->stid = oci_parse($this->_connection, $sql);
foreach ($bindvars as $bv) {
// oci_bind_by_name(resource, bv_name, php_variable, length)
oci_bind_by_name($this->stid, $bv[0], $bv[1], $bv[2]);
}
//oci_set_action($this->_connection, $action);
oci_execute($this->stid);
// will auto commit
}
private function buscarMesJove()
{
$codi_delegacio = $this->_infos->CODI_DELEGACIO;
$oci = new Oci($_SESSION['user']['usuari'], $_SESSION['user']['passwd']);
$req = "SELECT codi_delegacio, min(data_alta), max(codi) as CODI FROM venedor WHERE codi_delegacio = :codi_delegacio group by codi_delegacio";
$stid = oci_parse($oci->getConnection(), $req);
oci_bind_by_name($stid, ":codi_delegacio", $codi_delegacio);
oci_execute($stid);
$jove = oci_fetch_object($stid);
return $jove->CODI;
}
/**
* @param $query
* @param $params
* @param $action
*/
public function execute($query, $action, $params = array())
{
$this->stid = oci_parse($this->conn, $query);
if ($this->prefetch >= 0) {
oci_set_prefetch($this->stid, $this->prefetch);
}
foreach ($params as $bv) {
oci_bind_by_name($this->stid, $bv[0], $bv[1], $bv[2]);
}
oci_set_action($this->conn, $action);
oci_execute($this->stid);
}
function db_execute($sql, $bind = null) {
$c = db_connect();
$res = array();
$s = oci_parse($c, $sql);
if ($bind != null) {
foreach ($bind as $key => $value) {
oci_bind_by_name($s, ":".$key, htmlentities($value,ENT_QUOTES));
}
}
$res = oci_execute($s);
return $res;
}
public function exec_proc($proc, $param, $exportReq)
{
$cursor = oci_new_cursor($this->conn);
for ($i = 0; $i < count($param); $i++) {
$this->proc_param_list .= "p{$i}=>:param{$i}, ";
}
$sql = "begin\n\t\t\t\t{$proc} ({$this->proc_param_list} ref_cur_out=>:result); \n\t\t\t\tend;";
$parse = oci_parse($this->conn, $sql);
oci_bind_by_name($parse, ':result', $cursor, -1, OCI_B_CURSOR);
# Bind input parameters to stored procedure call ($param)
for ($i = 0; $i < count($param); $i++) {
oci_bind_by_name($parse, ':param' . $i, $param[$i]);
}
oci_execute($parse);
oci_execute($cursor);
if ($exportReq) {
// CSV export
oci_fetch_all($cursor, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_NUM);
foreach ($res as $key => $value) {
// Remove 'ORDERING' from resultset
array_pop($res[$key]);
}
return $res;
} else {
oci_fetch_all($cursor, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
array_shift($res);
// Remove 'header' row foreach entry on json req
foreach ($res as $key => $value) {
// Remove 'ORDERING' from resultset
unset($res[$key]['ORDERING']);
}
return json_encode($res, JSON_PRETTY_PRINT);
}
}
