public function query($sql)
{
$rs = odbc_exec($this->dbConnection(), $sql);
if (is_resource($rs)) {
while ($result[] = odbc_fetch_array($rs)) {
}
odbc_free_result($rs);
$this->close();
return $result;
} else {
$this->halt('Database query error', $sql);
}
}
/**
* Execute a query and return the results
* @param string $query
* @param array $params
* @param bool $fetchResult Return a ressource or a an array
* @return resource|array
* @throws Exception
*/
public function query($query, $params = null, $fetchResult = false)
{
$this->checkConnection(true);
$this->log('Query: ' . $query);
if (!empty($params)) {
$this->log('Params: ' . print_r($params, true));
}
$start = microtime(true);
if (empty($params)) {
$res = $this->executeQuery($query);
} else {
$res = $this->executePreparedStatement($query, $params);
}
$end = microtime(true);
$this->log("Execution time: " . ($end - $start) . " seconds");
if ($fetchResult) {
$this->log('Num Rows: ' . odbc_num_rows($res));
$resutlSet = $this->getRows($res);
odbc_free_result($res);
$res = $resutlSet;
$resultSet = null;
$fetch = microtime(true);
$this->log("Fetch time: " . ($fetch - $end) . " seconds");
}
return $res;
}
function _import_db()
{
//
$sql = 'SELECT *
FROM provedor
ORDER BY nit';
$result = $this->query($sql);
while ($row = odbc_fetch_array($result)) {
$this->p[] = $row;
}
odbc_free_result($result);
unset($row);
//
$sql = 'SELECT *
FROM constancia
ORDER BY exencion';
$result = $this->query($sql);
while ($row = odbc_fetch_array($result)) {
$this->e[] = $row;
}
odbc_free_result($result);
unset($row);
//
$sql = 'SELECT *
FROM factura
ORDER BY exencion, factura';
$result = $this->query($sql);
while ($row = odbc_fetch_array($result)) {
$this->f[] = $row;
}
odbc_free_result($result);
//
return;
}
function free_result($res)
{
if (odbc_free_result($res)) {
return true;
} else {
return false;
}
}
/**
* @see DatabaseInfo::initTables()
*/
protected function initTables()
{
include_once 'creole/drivers/odbc/metadata/ODBCTableInfo.php';
$result = @odbc_tables($this->conn->getResource());
if (!$result) {
throw new SQLException('Could not list tables', $this->conn->nativeError());
}
while (odbc_fetch_row($result)) {
$tablename = strtoupper(odbc_result($result, 'TABLE_NAME'));
$this->tables[$tablename] = new ODBCTableInfo($this, $tablename);
}
@odbc_free_result($result);
}
public function index()
{
//test the dynamic connection with other databases seems successfull
$systems = System::all();
$res = [];
foreach ($systems as $system) {
$query = "SELECT [states].eq_id, [states].time, [states].[state_OK],[states].[state_MaintRQ], [states].[state_InMaint], [states].[state_Fault], [equipment].eq_id, CAST(CAST([equipment].eq_name AS VARBINARY) AS VARCHAR) as eq_name FROM [states] LEFT JOIN [equipment] on states.eq_id = [equipment].eq_id";
if ($system['dbversion'] == '2000') {
$port = '1434';
try {
$connection = odbc_connect("Driver={SQL Server Native Client 10.0};Server=" . $system['host'] . "," . $port . ";Database=" . $system['dbname'], $system['dbuser'], Crypt::decrypt($system['dbuserpass']));
} catch (ErrorException $e) {
$system->status = 'default';
$system->save();
$res[$system['name']] = ['error' => $e];
}
if ($conn) {
$results = odbc_exec($connection, $query);
$realData = [];
$i = 0;
while ($row = json_decode(json_encode(odbc_fetch_object($results)), true)) {
foreach ($row as $key => $item) {
if ($key == "eq_name" && is_string($item)) {
$row[$key] = iconv('UCS-2LE', 'UTF-8', $item);
}
}
$realData[$i] = $row;
$i++;
}
$res[$system['name']] = $realData;
odbc_free_result($results);
odbc_close($connection);
}
} else {
try {
$conn = new PDO("sqlsrv:Server=" . $system['host'] . ";Database=" . $system['dbname'], $system['dbuser'], Crypt::decrypt($system['dbuserpass']));
} catch (PDOException $e) {
$system->status = 'default';
$system->save();
$res[$system['name']] = ['error' => $e];
}
if ($conn) {
$sql = $conn->prepare($query);
$sql->execute();
$res[$system['name']] = $sql->fetchAll();
$conn = null;
}
}
}
return $res;
}
function odbc_fila($sql, $fila)
{
$cnn_odbc = odbc_connect("ctw", "", "");
$rs = odbc_exec($cnn_odbc, $sql);
$value = "NO_EXISTE";
$value = odbc_fetch_array($rs, 1);
odbc_free_result($rs);
odbc_close($cnn_odbc);
//if(!$rs){
// return odbc_error($rs);
//} else {
return $value[$fila];
//}
}
/**
* Internal function to call native ODBC prepare/execute functions.
*/
protected function _execute($sql, $params, $fetchmode, $isupdate)
{
// Set any params passed directly
if ($params) {
for ($i = 0, $cnt = count($params); $i < $cnt; $i++) {
$this->set($i + 1, $params[$i]);
}
}
// Trim surrounding quotes added from default set methods.
// Exception: for LOB-based parameters, odbc_execute() will
// accept a filename surrounded by single-quotes.
foreach ($this->boundInVars as $idx => $var) {
if ($var instanceof Lob) {
$file = $isupdate ? $var->getInputFile() : $var->getOutputFile();
$this->boundInVars[$idx] = "'{$file}'";
} else {
if (is_string($var)) {
$this->boundInVars[$idx] = trim($var, "\"\\'");
}
}
}
if ($this->resultSet) {
$this->resultSet->close();
$this->resultSet = null;
}
$this->updateCount = null;
$stmt = @odbc_prepare($this->conn->getResource(), $sql);
if ($stmt === FALSE) {
throw new SQLException('Could not prepare query', $this->conn->nativeError(), $sql);
}
$ret = @odbc_execute($stmt, $this->boundInVars);
if ($ret === FALSE) {
@odbc_free_result($stmt);
throw new SQLException('Could not execute query', $this->conn->nativeError(), $sql);
}
return $this->conn->createResultSet(new ODBCResultResource($stmt), $fetchmode);
}
/**
* Get the rows returned from a SELECT query.
*
* @param resource The query result pointer
* @param ?integer Whether to start reading from (NULL: irrelevant for this forum driver)
* @return array A list of row maps
*/
function db_get_query_rows($results, $start = NULL)
{
$out = array();
$i = 0;
$num_fields = odbc_num_fields($results);
$types = array();
$names = array();
for ($x = 1; $x <= $num_fields; $x++) {
$types[$x] = odbc_field_type($results, $x);
$names[$x] = odbc_field_name($results, $x);
}
while (odbc_fetch_row($results)) {
if (is_null($start) || $i >= $start) {
$newrow = array();
for ($j = 1; $j <= $num_fields; $j++) {
$v = odbc_result($results, $j);
$type = $types[$j];
$name = strtolower($names[$j]);
if ($type == 'INTEGER' || $type == 'SMALLINT' || $type == 'UINTEGER') {
if (!is_null($v)) {
$newrow[$name] = intval($v);
} else {
$newrow[$name] = NULL;
}
} else {
$newrow[$name] = $v;
}
}
$out[] = $newrow;
}
$i++;
}
odbc_free_result($results);
// echo '<p>End '.microtime(false);
return $out;
}
function freeResult($result)
{
if (is_resource($result)) {
// Always return true
return odbc_free_result($result);
}
if (!isset($this->prepare_tokens[(int) $result])) {
return false;
}
unset($this->prepare_tokens[(int) $result]);
unset($this->prepare_types[(int) $result]);
return true;
}
function db_free($oStmt)
{
return odbc_free_result($oStmt);
}
function freeResult($result)
{
unset($this->row[(int) $result]);
return @odbc_free_result($result);
}
/**
* Free the result
*
* @return void
*/
public function free_result()
{
if (is_resource($this->result_id)) {
odbc_free_result($this->result_id);
$this->result_id = FALSE;
}
}
/**
* This will not return anything, and will free resources correctly.
*/
function execute($sql, $ignore_message = null)
{
$query = @odbc_exec($this->con, $sql);
if ($query === false) {
if (odbc_errormsg() !== $ignore_message) {
throw new Exception("Query error: " . odbc_error() . ": " . odbc_errormsg() . "\nSQL:\n" . $sql);
}
} else {
odbc_free_result($query);
}
}
/**
* Will grab the auto incremented value from the last query (if one exists)
*
* @param fResult $result The result object for the query
* @return void
*/
private function handleAutoIncrementedValue($result)
{
if (!preg_match('#^\\s*INSERT\\s+INTO\\s+(?:`|"|\\[)?(\\w+)(?:`|"|\\])?#i', $result->getSQL(), $table_match)) {
$result->setAutoIncrementedValue(NULL);
return;
}
$table = strtolower($table_match[1]);
$insert_id = NULL;
if ($this->type == 'oracle') {
if (!isset($this->schema_info['sequences'])) {
$sql = "SELECT\r\n\t\t\t\t\t\t\t\tTABLE_NAME,\r\n\t\t\t\t\t\t\t\tTRIGGER_BODY\r\n\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\tUSER_TRIGGERS\r\n\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tTRIGGERING_EVENT = 'INSERT' AND\r\n\t\t\t\t\t\t\t\tSTATUS = 'ENABLED' AND\r\n\t\t\t\t\t\t\t\tTRIGGER_NAME NOT LIKE 'BIN\$%'";
$this->schema_info['sequences'] = array();
foreach ($this->query($sql) as $row) {
if (preg_match('#SELECT\\s+(\\w+).nextval\\s+INTO\\s+:new\\.(\\w+)\\s+FROM\\s+dual#i', $row['trigger_body'], $matches)) {
$this->schema_info['sequences'][strtolower($row['table_name'])] = array('sequence' => $matches[1], 'column' => $matches[2]);
}
}
if ($this->cache) {
$this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info);
}
}
if (!isset($this->schema_info['sequences'][$table]) || preg_match('#INSERT\\s+INTO\\s+' . preg_quote($table, '#') . '\\s+\\([^\\)]*?\\b' . preg_quote($this->schema_info['sequences'][$table]['column'], '#') . '\\b#i', $result->getSQL())) {
return;
}
$insert_id_sql = "SELECT " . $this->schema_info['sequences'][$table]['sequence'] . ".currval AS INSERT_ID FROM dual";
}
if ($this->type == 'postgresql') {
if (!isset($this->schema_info['sequences'])) {
$sql = "SELECT\r\n\t\t\t\t\t\t\t\tpg_class.relname AS table_name,\r\n\t\t\t\t\t\t\t\tpg_attribute.attname AS column\r\n\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\tpg_attribute INNER JOIN\r\n\t\t\t\t\t\t\t\tpg_class ON pg_attribute.attrelid = pg_class.oid INNER JOIN\r\n\t\t\t\t\t\t\t\tpg_attrdef ON pg_class.oid = pg_attrdef.adrelid AND pg_attribute.attnum = pg_attrdef.adnum\r\n\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tNOT pg_attribute.attisdropped AND\r\n\t\t\t\t\t\t\t\tpg_attrdef.adsrc LIKE 'nextval(%'";
$this->schema_info['sequences'] = array();
foreach ($this->query($sql) as $row) {
$this->schema_info['sequences'][strtolower($row['table_name'])] = $row['column'];
}
if ($this->cache) {
$this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info);
}
}
if (!isset($this->schema_info['sequences'][$table]) || preg_match('#INSERT\\s+INTO\\s+' . preg_quote($table, '#') . '\\s+\\([^\\)]*?\\b' . preg_quote($this->schema_info['sequences'][$table], '#') . '\\b#i', $result->getSQL())) {
return;
}
}
if ($this->extension == 'mssql') {
$insert_id_res = mssql_query("SELECT @@IDENTITY AS insert_id", $this->connection);
$insert_id = mssql_result($insert_id_res, 0, 'insert_id');
mssql_free_result($insert_id_res);
} elseif ($this->extension == 'mysql') {
$insert_id = mysql_insert_id($this->connection);
} elseif ($this->extension == 'mysqli') {
$insert_id = mysqli_insert_id($this->connection);
} elseif ($this->extension == 'oci8') {
$oci_statement = oci_parse($this->connection, $insert_id_sql);
oci_execute($oci_statement);
$insert_id_row = oci_fetch_array($oci_statement, OCI_ASSOC);
$insert_id = $insert_id_row['INSERT_ID'];
oci_free_statement($oci_statement);
} elseif ($this->extension == 'odbc' && $this->type == 'mssql') {
$insert_id_res = odbc_exec($this->connection, "SELECT @@IDENTITY AS insert_id");
$insert_id = odbc_result($insert_id_res, 'insert_id');
odbc_free_result($insert_id_res);
} elseif ($this->extension == 'odbc' && $this->type == 'oracle') {
$insert_id_res = odbc_exec($this->connection, $insert_id_sql);
$insert_id = odbc_result($insert_id_res, 'insert_id');
odbc_free_result($insert_id_res);
} elseif ($this->extension == 'pgsql') {
$insert_id_res = pg_query($this->connection, "SELECT lastval()");
$insert_id_row = pg_fetch_assoc($insert_id_res);
$insert_id = array_shift($insert_id_row);
pg_free_result($insert_id_res);
} elseif ($this->extension == 'sqlite') {
$insert_id = sqlite_last_insert_rowid($this->connection);
} elseif ($this->extension == 'sqlsrv') {
$insert_id_res = sqlsrv_query($this->connection, "SELECT @@IDENTITY AS insert_id");
$insert_id_row = sqlsrv_fetch_array($insert_id_res, SQLSRV_FETCH_ASSOC);
$insert_id = $insert_id_row['insert_id'];
sqlsrv_free_stmt($insert_id_res);
} elseif ($this->extension == 'pdo') {
switch ($this->type) {
case 'mssql':
try {
$insert_id_statement = $this->connection->query("SELECT @@IDENTITY AS insert_id");
if (!$insert_id_statement) {
throw new Exception();
}
$insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC);
$insert_id = array_shift($insert_id_row);
} catch (Exception $e) {
// If there was an error we don't have an insert id
}
break;
case 'oracle':
try {
$insert_id_statement = $this->connection->query($insert_id_sql);
if (!$insert_id_statement) {
throw new Exception();
}
$insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC);
$insert_id = array_shift($insert_id_row);
} catch (Exception $e) {
// If there was an error we don't have an insert id
}
break;
case 'postgresql':
$insert_id_statement = $this->connection->query("SELECT lastval()");
$insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC);
$insert_id = array_shift($insert_id_row);
$insert_id_statement->closeCursor();
unset($insert_id_statement);
break;
case 'mysql':
$insert_id = $this->connection->lastInsertId();
break;
case 'sqlite':
$insert_id = $this->connection->lastInsertId();
break;
}
}
$result->setAutoIncrementedValue($insert_id);
}
/**
* Free the memory used by the result resource
*
* @return void
*/
public function free()
{
switch ($this->mode) {
case "mysql":
$this->result->free();
break;
case "postgres":
case "redshift":
pg_free_result($this->result);
break;
case "odbc":
odbc_free_result($this->result);
break;
case "sqlite":
$this->result->finalize();
break;
case "mssql":
mssql_free_result($this->result);
break;
}
}
/**
* @return bool
*/
function FreeResult()
{
if ($this->_resultId) {
if (!@odbc_free_result($this->_resultId)) {
$this->_setSqlError();
return false;
} else {
$this->_resultId = null;
}
return true;
} else {
return true;
}
}
function sql_free_result($res)
{
global $dbtype;
switch ($dbtype) {
case "MySQL":
$row = mysql_free_result($res);
return $row;
break;
case "mSQL":
$row = msql_free_result($res);
return $row;
break;
case "postgres":
case "postgres_local":
$rows = pg_FreeResult($res->get_result());
return $rows;
break;
case "ODBC":
case "ODBC_Adabas":
$rows = odbc_free_result($res);
return $rows;
break;
case "Interbase":
echo "<BR>Error! PHP dosen't support ibase_numrows!<BR>";
return $rows;
break;
case "Sybase":
$rows = sybase_free_result($res);
return $rows;
break;
}
}
function sql_freeresult($query_id = 0)
{
if (!$query_id) {
$query_id = $this->query_result;
}
if ($query_id) {
$result = @odbc_free_result($query_id);
return $result;
} else {
return false;
}
}
/**
* Build db-specific report
* @access private
*/
function _sql_report($mode, $query = '')
{
switch ($mode) {
case 'start':
break;
case 'fromcache':
$endtime = explode(' ', microtime());
$endtime = $endtime[0] + $endtime[1];
$result = @odbc_exec($this->db_connect_id, $query);
while ($void = @odbc_fetch_array($result)) {
// Take the time spent on parsing rows into account
}
@odbc_free_result($result);
$splittime = explode(' ', microtime());
$splittime = $splittime[0] + $splittime[1];
$this->sql_report('record_fromcache', $query, $endtime, $splittime);
break;
}
}
function sql_query($query = "", $transaction = FALSE)
{
if ($query != "") {
$this->num_queries++;
if ($transaction == BEGIN_TRANSACTION && !$this->in_transaction) {
if (!odbc_autocommit($this->db_connect_id, false)) {
return false;
}
$this->in_transaction = TRUE;
}
if (preg_match("/^SELECT(.*?)(LIMIT ([0-9]+)[, ]*([0-9]+)*)?\$/s", $query, $limits)) {
$query = $limits[1];
if (!empty($limits[2])) {
$row_offset = $limits[4] ? $limits[3] : "";
$num_rows = $limits[4] ? $limits[4] : $limits[3];
$query = "TOP " . ($row_offset + $num_rows) . $query;
}
$this->result = odbc_exec($this->db_connect_id, "SELECT {$query}");
if ($this->result) {
if (empty($this->field_names[$this->result])) {
for ($i = 1; $i < odbc_num_fields($this->result) + 1; $i++) {
$this->field_names[$this->result][] = odbc_field_name($this->result, $i);
$this->field_types[$this->result][] = odbc_field_type($this->result, $i);
}
}
$this->current_row[$this->result] = 0;
$this->result_rowset[$this->result] = array();
$row_outer = isset($row_offset) ? $row_offset + 1 : 1;
$row_outer_max = isset($num_rows) ? $row_offset + $num_rows + 1 : 1000000000.0;
$row_inner = 0;
while (odbc_fetch_row($this->result, $row_outer) && $row_outer < $row_outer_max) {
for ($j = 0; $j < count($this->field_names[$this->result]); $j++) {
$this->result_rowset[$this->result][$row_inner][$this->field_names[$this->result][$j]] = stripslashes(odbc_result($this->result, $j + 1));
}
$row_outer++;
$row_inner++;
}
$this->num_rows[$this->result] = count($this->result_rowset[$this->result]);
}
} else {
if (eregi("^INSERT ", $query)) {
$this->result = odbc_exec($this->db_connect_id, $query);
if ($this->result) {
$result_id = odbc_exec($this->db_connect_id, "SELECT @@IDENTITY");
if ($result_id) {
if (odbc_fetch_row($result_id)) {
$this->next_id[$this->db_connect_id] = odbc_result($result_id, 1);
$this->affected_rows[$this->db_connect_id] = odbc_num_rows($this->result);
}
}
}
} else {
$this->result = odbc_exec($this->db_connect_id, $query);
if ($this->result) {
$this->affected_rows[$this->db_connect_id] = odbc_num_rows($this->result);
}
}
}
if (!$this->result) {
if ($this->in_transaction) {
odbc_rollback($this->db_connect_id);
odbc_autocommit($this->db_connect_id, true);
$this->in_transaction = FALSE;
}
return false;
}
if ($transaction == END_TRANSACTION && $this->in_transaction) {
$this->in_transaction = FALSE;
if (!odbc_commit($this->db_connect_id)) {
odbc_rollback($this->db_connect_id);
odbc_autocommit($this->db_connect_id, true);
return false;
}
odbc_autocommit($this->db_connect_id, true);
}
odbc_free_result($this->result);
return $this->result;
} else {
if ($transaction == END_TRANSACTION && $this->in_transaction) {
$this->in_transaction = FALSE;
if (!@odbc_commit($this->db_connect_id)) {
odbc_rollback($this->db_connect_id);
odbc_autocommit($this->db_connect_id, true);
return false;
}
odbc_autocommit($this->db_connect_id, true);
}
return true;
}
}
/**
* @see TableInfo::initForeignKeys()
*/
protected function initForeignKeys()
{
// columns have to be loaded first
if (!$this->colsLoaded) {
$this->initColumns();
}
$result = @odbc_foreignkeys($this->dblink, '', '', '', $this->dbname, '', $this->name);
while (odbc_fetch_row($result)) {
$name = odbc_result($result, 'COLUMN_NAME');
$ftbl = odbc_result($result, 'FKTABLE_NAME');
$fcol = odbc_result($result, 'FKCOLUMN_NAME');
if (!isset($this->foreignKeys[$name])) {
$this->foreignKeys[$name] = new ForeignKeyInfo($name);
if (($foreignTable = $this->database->getTable($ftbl)) === null) {
$foreignTable = new TableInfo($ltbl);
$this->database->addTable($foreignTable);
}
if (($foreignCol = $foreignTable->getColumn($name)) === null) {
$foreignCol = new ColumnInfo($foreignTable, $name);
$foreignTable->addColumn($foreignCol);
}
$this->foreignKeys[$name]->addReference($this->columns[$name], $foreignCol);
}
}
@odbc_free_result($result);
$this->fksLoaded = true;
}
function _close()
{
return @odbc_free_result($this->_queryID);
}
private function getSytemIinfo($id)
{
$system = System::find($id);
$queryMeas = "SELECT [meas].param_id, [meas].time, [meas].value, [param].param_name, [param].param_unit, [limit].ad_limit FROM [meas] LEFT JOIN [param] on meas.param_id = [param].param_id LEFT JOIN [limit] on meas.param_id = [limit].param_id";
$queryEquipment2000 = "SELECT [time] ,st.[eq_id],[state_OK],[state_MaintRQ],[state_InMaint],[state_Fault] ,CAST(CAST([eq_name] AS VARBINARY) AS VARCHAR) as eq_name FROM [states] st JOIN [equipment] eq ON st.eq_id = eq.eq_id";
$queryEquipment = "SELECT [time] ,st.[eq_id],[state_OK],[state_MaintRQ],[state_InMaint],[state_Fault], [eq_name] FROM [states] st JOIN [equipment] eq ON st.eq_id = eq.eq_id";
$res;
$meas;
$eq;
$conn = false;
if ($system['status'] != 'default') {
if ($system['dbversion'] == '2000') {
$port = '1434';
try {
$conn = odbc_connect("Driver={SQL Server Native Client 10.0};Server=" . $system['host'] . "," . $port . ";Database=" . $system['dbname'], $system['dbuser'], Crypt::decrypt($system['dbuserpass']));
} catch (ErrorException $e) {
$res[$system['name']] = ['error' => 'Connection Error'];
$res[$system['name']]['meas'] = [];
$res[$system['name']]['equipment'] = [];
}
if ($conn) {
$results = odbc_exec($conn, $queryMeas);
$realDataMeas = [];
$i = 0;
while ($row = odbc_fetch_object($results)) {
$row->status = $row->param_name != "O2" && $row->ad_limit && $row->value > $row->ad_limit ? 'warning' : 'success';
$realDataMeas[$i] = $row;
$i++;
}
$meas = json_decode(json_encode($realDataMeas), true);
odbc_free_result($results);
$results = odbc_exec($conn, $queryEquipment2000);
$realDataEq = [];
$i = 0;
$state;
while ($row = json_decode(json_encode(odbc_fetch_object($results)), true)) {
foreach ($row as $key => $item) {
if ($key == "eq_name" && is_string($item)) {
$row[$key] = iconv('UCS-2LE', 'UTF-8', $item);
}
}
$realDataEq[$i] = $row;
$i++;
}
$eq = $realDataEq;
odbc_free_result($results);
odbc_close($conn);
$res[$system['name']] = ['meas' => $meas, 'equipment' => $eq];
}
} else {
try {
$conn = new PDO("sqlsrv:Server=" . $system['host'] . ";Database=" . $system['dbname'], $system['dbuser'], Crypt::decrypt($system['dbuserpass']));
} catch (PDOException $e) {
$res[$system['name']] = ['error' => 'Connection Error'];
$res[$system['name']]['meas'] = [];
$res[$system['name']]['equipment'] = [];
}
if ($conn) {
$sql = $conn->prepare($queryMeas);
$sql->execute();
$meas = $sql->fetchAll();
foreach ($meas as $key => $result) {
$result['status'] = $result['param_name'] != 'O2' && $result['ad_limit'] && $result['value'] > $result['ad_limit'] ? 'warning' : 'success';
$meas[$key] = $result;
}
$sql = $conn->prepare($queryEquipment);
$sql->execute();
$eq = $sql->fetchAll();
$conn = null;
$res[$system['name']] = ['meas' => $meas, 'equipment' => $eq];
}
}
} else {
$res[$system['name']] = ['error' => 'Connection Error'];
$res[$system['name']]['meas'] = [];
$res[$system['name']]['equipment'] = [];
}
return $res;
}
/**
* Frees a result set.
*
* @param resource $res The database query resource returned from
* the {@link dbi_query()} function.
*
* @return bool True on success
*/
function dbi_free_result($res)
{
if (strcmp($GLOBALS["db_type"], "mysql") == 0) {
return mysql_free_result($res);
} else {
if (strcmp($GLOBALS["db_type"], "mysqli") == 0) {
return mysqli_free_result($res);
} else {
if (strcmp($GLOBALS["db_type"], "mssql") == 0) {
return mssql_free_result($res);
} else {
if (strcmp($GLOBALS["db_type"], "oracle") == 0) {
// Not supported. Ingore.
if ($GLOBALS["oracle_statement"] >= 0) {
OCIFreeStatement($GLOBALS["oracle_statement"]);
$GLOBALS["oracle_statement"] = -1;
}
} else {
if (strcmp($GLOBALS["db_type"], "postgresql") == 0) {
return pg_freeresult($res);
} else {
if (strcmp($GLOBALS["db_type"], "odbc") == 0) {
return odbc_free_result($res);
} else {
if (strcmp($GLOBALS["db_type"], "ibm_db2") == 0) {
return db2_free_result($res);
} else {
if (strcmp($GLOBALS["db_type"], "ibase") == 0) {
return ibase_free_result($res);
} else {
dbi_fatal_error("dbi_free_result(): db_type not defined.");
}
}
}
}
}
}
}
}
}
/**
* Loads the map of ODBC data types to Creole (JDBC) types.
*
* NOTE: This function cannot map DBMS-specific datatypes. If you use a
* driver which implements DBMS-specific datatypes, you will need
* to modify/extend this class to add the correct mapping.
*/
public static function loadTypeMap($conn = null)
{
if (self::$typeMap !== null && count(self::$typeMap) > 0) {
return;
}
if ($conn == null) {
throw new SQLException('No connection specified when loading ODBC type map.');
}
self::$typeMap = array();
$result = @odbc_gettypeinfo($conn->getResource());
if ($result === false) {
throw new SQLException('Failed to retrieve type info.', $conn->nativeError());
}
$rowNum = 1;
while (odbc_fetch_row($result, $rowNum++)) {
$odbctypeid = odbc_result($result, 'DATA_TYPE');
$odbctypename = odbc_result($result, 'TYPE_NAME');
switch ($odbctypeid) {
case SQL_CHAR:
self::$typeMap[$odbctypename] = CreoleTypes::CHAR;
break;
case SQL_VARCHAR:
self::$typeMap[$odbctypename] = CreoleTypes::VARCHAR;
break;
case SQL_LONGVARCHAR:
self::$typeMap[$odbctypename] = CreoleTypes::LONGVARCHAR;
break;
case SQL_DECIMAL:
self::$typeMap[$odbctypename] = CreoleTypes::DECIMAL;
break;
case SQL_NUMERIC:
self::$typeMap[$odbctypename] = CreoleTypes::NUMERIC;
break;
case SQL_BIT:
self::$typeMap[$odbctypename] = CreoleTypes::BOOLEAN;
break;
case SQL_TINYINT:
self::$typeMap[$odbctypename] = CreoleTypes::TINYINT;
break;
case SQL_SMALLINT:
self::$typeMap[$odbctypename] = CreoleTypes::SMALLINT;
break;
case SQL_INTEGER:
self::$typeMap[$odbctypename] = CreoleTypes::INTEGER;
break;
case SQL_BIGINT:
self::$typeMap[$odbctypename] = CreoleTypes::BIGINT;
break;
case SQL_REAL:
self::$typeMap[$odbctypename] = CreoleTypes::REAL;
break;
case SQL_FLOAT:
self::$typeMap[$odbctypename] = CreoleTypes::FLOAT;
break;
case SQL_DOUBLE:
self::$typeMap[$odbctypename] = CreoleTypes::DOUBLE;
break;
case SQL_BINARY:
self::$typeMap[$odbctypename] = CreoleTypes::BINARY;
break;
case SQL_VARBINARY:
self::$typeMap[$odbctypename] = CreoleTypes::VARBINARY;
break;
case SQL_LONGVARBINARY:
self::$typeMap[$odbctypename] = CreoleTypes::LONGVARBINARY;
break;
case SQL_DATE:
self::$typeMap[$odbctypename] = CreoleTypes::DATE;
break;
case SQL_TIME:
self::$typeMap[$odbctypename] = CreoleTypes::TIME;
break;
case SQL_TIMESTAMP:
self::$typeMap[$odbctypename] = CreoleTypes::TIMESTAMP;
break;
case SQL_TYPE_DATE:
self::$typeMap[$odbctypename] = CreoleTypes::DATE;
break;
case SQL_TYPE_TIME:
self::$typeMap[$odbctypename] = CreoleTypes::TIME;
break;
case SQL_TYPE_TIMESTAMP:
self::$typeMap[$odbctypename] = CreoleTypes::TIMESTAMP;
break;
default:
self::$typeMap[$odbctypename] = CreoleTypes::OTHER;
break;
}
}
@odbc_free_result($result);
}
/**
* Returns information about a table or a result set
*
* @param object|string $result DB_result object from a query or a
* string containing the name of a table.
* While this also accepts a query result
* resource identifier, this behavior is
* deprecated.
* @param int $mode a valid tableInfo mode
*
* @return array an associative array with the information requested.
* A DB_Error object on failure.
*
* @see DB_common::tableInfo()
* @since Method available since Release 1.7.0
*/
function tableInfo($result, $mode = null)
{
if (is_string($result)) {
/*
* Probably received a table name.
* Create a result resource identifier.
*/
$id = @odbc_exec($this->connection, "SELECT * FROM {$result}");
if (!$id) {
return $this->odbcRaiseError();
}
$got_string = true;
} elseif (isset($result->result)) {
/*
* Probably received a result object.
* Extract the result resource identifier.
*/
$id = $result->result;
$got_string = false;
} else {
/*
* Probably received a result resource identifier.
* Copy it.
* Deprecated. Here for compatibility only.
*/
$id = $result;
$got_string = false;
}
if (!is_resource($id)) {
return $this->odbcRaiseError(DB_ERROR_NEED_MORE_DATA);
}
if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
$case_func = 'strtolower';
} else {
$case_func = 'strval';
}
$count = @odbc_num_fields($id);
$res = array();
if ($mode) {
$res['num_fields'] = $count;
}
for ($i = 0; $i < $count; $i++) {
$col = $i + 1;
$res[$i] = array('table' => $got_string ? $case_func($result) : '', 'name' => $case_func(@odbc_field_name($id, $col)), 'type' => @odbc_field_type($id, $col), 'len' => @odbc_field_len($id, $col), 'flags' => '');
if ($mode & DB_TABLEINFO_ORDER) {
$res['order'][$res[$i]['name']] = $i;
}
if ($mode & DB_TABLEINFO_ORDERTABLE) {
$res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
}
}
// free the result only if we were called on a table
if ($got_string) {
@odbc_free_result($id);
}
return $res;
}
$rsSQLS = odbc_exec($conn, "SELECT Count(*) AS contador_abm FROM ThirdParties WHERE DocumentNumber = '{$DocumentNumber}'");
$result = odbc_fetch_array($rsSQLS);
odbc_free_result($rsSQLS);
if ($result['contador_abm'] >= 1) {
//manejamos una variable para enviar los mensajes
$mensaje = "";
$rs_datos_abm = odbc_exec($conn, "SELECT \n\t\t\t\t\t\t\t\t\t\tPhoneNumber,\n\t\t\t\t\t\t\t\t\t\tFaxNumber,\n\t\t\t\t\t\t\t\t\t\tAddress,\n\t\t\t\t\t\t\t\t\t\tMobilePhoneNumber,\n\t\t\t\t\t\t\t\t\t\tEmail,\n\t\t\t\t\t\t\t\t\t\tPOBox,\n\t\t\t\t\t\t\t\t\t\tCityIataCode\n\t\t\t\t\t\t\t\t\tFROM ThirdParties WHERE DocumentNumber = '{$DocumentNumber}'");
$result_abm = odbc_fetch_array($rs_datos_abm);
$telefono_abm = $result_abm['PhoneNumber'];
$fax_abm = $result_abm['FaxNumber'];
$celular_abm = $result_abm['MobilePhoneNumber'];
$email_abm = $result_abm['Email'];
$direccion_abm = $result_abm['Address'];
$cod_postal_abm = $result_abm['POBox'];
$city_iata_abm = $result_abm['CityIataCode'];
odbc_free_result($rs_datos_abm);
echo "<br> <b> Ya existe el registro en el ABM :..</b><br>";
//Actualizacion de datos si son diferentes, en vtiger al ABM...
if ($telefono_vtiger != $telefono_abm) {
// Actualizacion de Tlf
$update_telefono = odbc_exec($conn, "UPDATE ThirdParties SET [PhoneNumber] = '{$telefono_vtiger}'\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE [DocumentNumber] = '{$DocumentNumber}'");
$mensaje = "<br>- Se actualizó el Nro. de Teléfono del Contacto. <br>";
}
if ($fax_vtiger != $fax_abm) {
// Actualizacion de fax
$update_fax = odbc_exec($conn, "UPDATE ThirdParties SET \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t[FaxNumber] = '{$fax_vtiger}'\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE [DocumentNumber] = '{$DocumentNumber}'");
$mensaje .= "<br>- Se actualizó el Nro. de Fax del Contacto. <br>";
}
if ($celular_vtiger != $celular_abm) {
// Actualizacion de celular
$update_celular = odbc_exec($conn, "UPDATE ThirdParties SET \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t[MobilePhoneNumber] = '{$celular_vtiger}'\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE [DocumentNumber] = '{$DocumentNumber}'");
/**
* Returns metadata for all columns in a table.
* @param string
* @return array
*/
public function getColumns($table)
{
$result = odbc_columns($this->connection);
$res = array();
while ($row = odbc_fetch_array($result)) {
if ($row['TABLE_NAME'] === $table) {
$res[] = array('name' => $row['COLUMN_NAME'], 'table' => $table, 'nativetype' => $row['TYPE_NAME'], 'size' => $row['COLUMN_SIZE'], 'nullable' => (bool) $row['NULLABLE'], 'default' => $row['COLUMN_DEF']);
}
}
odbc_free_result($result);
return $res;
}
/**
* Define the application's command schedule.
*
* @param \Illuminate\Console\Scheduling\Schedule $schedule
* @return void
*/
protected function schedule(Schedule $schedule)
{
$schedule->call(function () {
$systems = System::all();
foreach ($systems as $key => $system) {
$queryMeas = "SELECT [meas].param_id, [meas].time, [meas].value, [param].param_name, [param].param_unit, [limit].ad_limit FROM [meas] LEFT JOIN [param] on meas.param_id = [param].param_id LEFT JOIN [limit] on meas.param_id = [limit].param_id";
$queryEquipment2000 = "SELECT [time] ,st.[eq_id],[state_OK],[state_MaintRQ],[state_InMaint],[state_Fault] ,CAST(CAST([eq_name] AS VARBINARY) AS VARCHAR) as eq_name FROM [states] st JOIN [equipment] eq ON st.eq_id = eq.eq_id";
$queryEquipment = "SELECT [time] ,st.[eq_id],[state_OK],[state_MaintRQ],[state_InMaint],[state_Fault], [eq_name] FROM [states] st JOIN [equipment] eq ON st.eq_id = eq.eq_id";
$meas;
$eq;
$conn = false;
if ($system['dbversion'] == '2000') {
$port = '1434';
try {
$conn = odbc_connect("Driver={SQL Server Native Client 10.0};Server=" . $system['host'] . "," . $port . ";Database=" . $system['dbname'], $system['dbuser'], Crypt::decrypt($system['dbuserpass']));
} catch (ErrorException $e) {
$system->status = 'default';
$system->save();
}
if ($conn) {
$sysStatus = 'success';
$results = odbc_exec($conn, $queryMeas);
$realDataMeas = [];
$i = 0;
while ($row = odbc_fetch_object($results)) {
$row->status = $row->param_name != "O2" && $row->ad_limit && $row->value > $row->ad_limit ? 'warning' : 'success';
$sysStatus = $sysStatus != $row->status && $row->status == 'warning' ? $row->status : $sysStatus;
$realDataMeas[$i] = $row;
$i++;
}
$meas = json_decode(json_encode($realDataMeas), true);
odbc_free_result($results);
$results = odbc_exec($conn, $queryEquipment2000);
$realDataEq = [];
$i = 0;
while ($row = json_decode(json_encode(odbc_fetch_object($results)), true)) {
if ($row['state_Fault'] == 1 && $sysStatus != 'error') {
$sysStatus = 'error';
}
if ($sysStatus != 'error' && ($row['state_InMaint'] == 1 || $row['state_MaintRQ'] == 1)) {
$sysStatus = 'warning';
}
if ($sysStatus != 'warning' && $sysStatus != 'error' && $row['state_OK'] == 1) {
$sysStatus = 'success';
}
foreach ($row as $key => $item) {
if ($key == "eq_name" && is_string($item)) {
$row[$key] = iconv('UCS-2LE', 'UTF-8', $item);
}
}
$realDataEq[$i] = $row;
$i++;
}
$system->status = $sysStatus;
$system->save();
odbc_free_result($results);
odbc_close($conn);
}
} else {
try {
$conn = new PDO("sqlsrv:Server=" . $system['host'] . ";Database=" . $system['dbname'], $system['dbuser'], Crypt::decrypt($system['dbuserpass']));
} catch (PDOException $e) {
$system->status = 'default';
$system->save();
}
if ($conn) {
$sysStatus = 'success';
$sql = $conn->prepare($queryMeas);
$sql->execute();
$meas = $sql->fetchAll();
foreach ($meas as $key => $row) {
$row['status'] = $row['param_name'] != 'O2' && $row['ad_limit'] && $row['value'] > $row['ad_limit'] ? 'warning' : 'success';
$sysStatus = $sysStatus != $row['status'] && $row['status'] == 'warning' ? $row['status'] : $sysStatus;
$meas[$key] = $row;
}
$sql = $conn->prepare($queryEquipment);
$sql->execute();
$eq = $sql->fetchAll();
foreach ($eq as $key => $row) {
if ($row['state_Fault'] == 1 && $sysStatus != 'error') {
$sysStatus = 'error';
}
if ($sysStatus != 'error' && ($row['state_InMaint'] == 1 || $row['state_MaintRQ'] == 1)) {
$sysStatus = 'warning';
}
if ($sysStatus != 'warning' && $sysStatus != 'error' && $row['state_OK'] == 1) {
$sysStatus = 'success';
}
$eq[$key] = $row;
}
$system->status = $sysStatus;
$system->save();
$conn = null;
}
}
}
})->everyMinute();
}
