/**
* Internal method to fetch the next row from the result set
*
* @return array|null
*/
protected function getNextRow()
{
# If the result resource is invalid then don't bother trying to fetch
if (!$this->result) {
return;
}
switch ($this->mode) {
case "mysql":
$row = $this->result->fetch_assoc();
break;
case "postgres":
case "redshift":
$row = pg_fetch_assoc($this->result);
break;
case "odbc":
$row = odbc_fetch_array($this->result, $this->position + 1);
break;
case "sqlite":
$row = $this->result->fetchArray(SQLITE3_ASSOC);
break;
case "mssql":
$row = mssql_fetch_assoc($this->result);
break;
}
# If the fetch fails then there are no rows left to retrieve
if (!$row) {
return;
}
$this->position++;
return $row;
}
function findFlights($flight)
{
//Connects to database
require 'connect_db.php';
$query = mssql_query('SELECT * FROM FLIGHT');
if (!mssql_num_rows($query)) {
echo 'No records found';
} else {
//Creates tables and fills it with flight numbers and their delays
echo '<br><br><br><br><table border = 1>';
echo '<th>Flight Number</th><th>Delayed</th><th>Depature Time</th>';
while ($row = mssql_fetch_assoc($query)) {
$i = 0;
//Check if flight is what is looking for
if (strcmp($row['Flight_number'], $flight) == 0) {
$i = $i + 1;
echo '<tr><td>' . $row['Flight_number'] . '</td>';
if (strcmp($row['Delayed'], '1') != 0) {
echo '<td>' . 'On Time' . '</td>';
} else {
echo '<td>' . 'Delayed' . '</td>';
}
echo '<td>' . $row['Depature_time'] . '</td></tr>';
//^End else
}
}
//^ends while
echo '</table>';
}
}
public function getAll()
{
$obj_ids = $this->getObjIds();
$retsult = array();
$sql = <<<SQL
\t\t\tSELECT
\t\t\t\ttemp.OBJID as id,
\t\t\t\tCONVERT(VARCHAR, (DB1.GMT+'02:00'), 21) as dt,
\t\t\t\tDB1.LAT as lat,
\t\t\t\tDB1.LON as lon,
\t\t\t\tDB1.AVTO_NO as avto_no,
\t\t\t\tDB1.SPEED as speed
\t\t\tFROM [monitoring_new].[dbo].[OD_LTE_OBJLASTPOS] DB1
\t\t\tINNER JOIN (
\t\t\t\tSELECT
\t\t\t\t\tOBJID,
\t\t\t\t\tMAX(GMT) AS DAT
\t\t\t\tFROM [monitoring_new].[dbo].[OD_LTE_OBJLASTPOS]
\t\t\t\tWHERE GMT>=cast(getutcdate() as date) and OBJID in ({$obj_ids})
\t\t\t\tGROUP BY OBJID) as temp
\t\t\tON (temp.OBJID=DB1.OBJID and temp.DAT=DB1.GMT)
\t\t\tORDER BY temp.OBJID
SQL;
$cur = mssql_query($sql, $this->conm);
while ($data = mssql_fetch_assoc($cur)) {
$result[$data['id']] = array('obj_id' => $data['id'], 'dt' => $data['dt'], 'lat' => $data['lat'], 'lon' => $data['lon'], 'name' => $data['avto_no']);
}
return $result;
}
public function update()
{
$this->LogInfo("Start");
$this->latestUserId = $this->lastInsertedUserIdInMongoDb();
$newUsersInDatabase = new UserFind($this->latestUserId, new RelaySQLConnection());
$query = $newUsersInDatabase->findNewUsersInDatabase();
$this->LogInfo("Found " . mssql_num_rows($query) . " new users");
if ($this->queryContainsNewUsers($query)) {
while ($result = mssql_fetch_assoc($query)) {
$criteria = array(UsersSchema::USERNAME => $result[UserRelaySchema::USERNAME]);
if ($this->foundNewUser($criteria)) {
// $this->LogInfo("Found new user " . $result[UserRelaySchema::USERNAME]);
$user = (new UserCreate())->create($result);
if (is_null($user)) {
$this->LogError("Could not create the user with username: "******"Tried to insert user: "******", but user is already in database");
}
}
}
if ($this->usersInserted > 0) {
$this->updateLargestInsertedUserIdInMongoDb();
$this->LogInfo("Inserted " . $this->usersInserted . " new users");
}
}
/**
* @param $sql
*
* @return array
*/
public function query($sql)
{
//
$this->connection = $this->getConnection();
// Run query
$query = mssql_query($sql, $this->connection);
// On error
if ($query === false) {
Response::error(500, $_SERVER["SERVER_PROTOCOL"] . ' DB query failed (SQL): ' . mssql_get_last_message());
}
// E.g. boolean is returned if no rows (e.g. no resource found or on UPDATE)
if ($query === true) {
$response = $query;
} else {
// Response
$response = array();
//
// Loop rows and add to response array
if (mssql_num_rows($query) > 0) {
while ($row = mssql_fetch_assoc($query)) {
$response[] = $row;
}
}
// Free the query result
mssql_free_result($query);
}
// Close link
$this->closeConnection();
//
return $response;
}
public function query($sql)
{
$resource = mssql_query($sql, $this->link);
if ($resource) {
if (is_resource($resource)) {
$i = 0;
$data = array();
while ($result = mssql_fetch_assoc($resource)) {
$data[$i] = $result;
$i++;
}
mssql_free_result($resource);
$query = new Object();
$row = isset(Arrays::first($data)) ? Arrays::first($data) : array();
$query->setRow($row)->setRows($data)->setNumRows($i);
unset($data);
return $query;
} else {
return true;
}
} else {
trigger_error('Error: ' . mssql_get_last_message($this->link) . '<br />' . $sql);
exit;
}
}
function table_copy($xsql, $table)
{
global $old_gp;
global $new_gp;
$name = "";
$names = "";
$feilds = "";
$sql = "truncate table {$table};";
$inc = 0;
$rset = mssql_query($xsql, $old_gp);
while ($line = mssql_fetch_assoc($rset)) {
$names = "";
$feilds = "";
for ($inc = 0; $inc < mssql_num_fields($rset); $inc++) {
$name = trim(mssql_field_name($rset, $inc));
if ($names == "") {
$names = $name;
} else {
$names .= ", {$name}";
}
if ($feilds == "") {
$feilds = "'" . str_replace("'", "''", trim($line[$name])) . "'";
} else {
$feilds .= ", '" . str_replace("'", "''", trim($line[$name])) . "'";
}
}
$sql .= "insert into {$table} ({$names}) values ({$feilds});\n";
echo "{$sql}";
mssql_query($sql, $new_gp);
$sql = "";
}
//echo $sql;
}
/**
* Iterator impl.
*/
public function rewind()
{
$this->cindex = 0;
mssql_data_seek($this->result, 0);
$this->last = mssql_fetch_assoc($this->result);
return $this->last;
}
private function fetch_next()
{
if ($row = mssql_fetch_assoc($this->rsrc)) {
$row = array_change_key_case($row, CASE_LOWER);
}
return $row;
}
/**
* Iterator function. Returns a rowset if called without parameter,
* the fields contents if a field is specified or FALSE to indicate
* no more rows are available.
*
* @param string field default NULL
* @return [:var]
*/
public function next($field = null)
{
if (!is_resource($this->handle) || false === ($row = mssql_fetch_assoc($this->handle))) {
return null;
}
foreach ($row as $key => $value) {
if (null === $value || !isset($this->fields[$key])) {
continue;
}
switch ($this->fields[$key]) {
case 'datetime':
$row[$key] = new \util\Date($value, $this->tz);
break;
case 'numeric':
if (false !== strpos($value, '.')) {
settype($row[$key], 'double');
break;
}
// Fallthrough intentional
case 'int':
if ($value <= PHP_INT_MAX && $value >= -PHP_INT_MAX - 1) {
settype($row[$key], 'integer');
} else {
settype($row[$key], 'double');
}
break;
}
}
if ($field) {
return $row[$field];
} else {
return $row;
}
}
public function query($sql)
{
$resource = mssql_query($sql, $this->link);
if ($resource) {
if (is_resource($resource)) {
$i = 0;
$data = array();
while ($result = mssql_fetch_assoc($resource)) {
$data[$i] = $result;
$i++;
}
mssql_free_result($resource);
$query = new stdClass();
$query->row = isset($data[0]) ? $data[0] : array();
$query->rows = $data;
$query->num_rows = $i;
unset($data);
return $query;
} else {
return true;
}
} else {
trigger_error('Error: ' . mssql_get_last_message($this->link) . '<br />' . $sql);
exit;
}
}
public function query($sql, $start = null, $perpage = null, $nolimit = false)
{
$start and !$perpage and $perpage = 10000;
$query = mssql_query($sql, $this->dbConnection());
if ($start) {
$qcount = mssql_num_rows($query);
if ($qcount < $start) {
return array();
} else {
mssql_data_seek($query, $start);
}
}
if ($query) {
$result = array();
while ($row = mssql_fetch_assoc($query)) {
if (DBCHARSET == 'gbk' && CHARSET != 'gbk') {
$row = Base_Class::gbktoutf($row);
}
$result[] = $row;
if ($perpage && count($result) >= $perpage) {
break;
}
}
return $result;
} else {
$this->halt("数据库查询错误", $sql);
}
}
/**
* Iterator function. Returns a rowset if called without parameter,
* the fields contents if a field is specified or FALSE to indicate
* no more rows are available.
*
* @param string field default NULL
* @return var
*/
public function next($field = NULL)
{
if (!is_resource($this->handle) || FALSE === ($row = mssql_fetch_assoc($this->handle))) {
return FALSE;
}
foreach ($row as $key => $value) {
if (NULL === $value || !isset($this->fields[$key])) {
continue;
}
switch ($this->fields[$key]) {
case 'datetime':
$row[$key] = Date::fromString($value, $this->tz);
break;
case 'numeric':
if (FALSE !== strpos($value, '.')) {
settype($row[$key], 'double');
break;
}
// Fallthrough intentional
case 'int':
if ($value <= LONG_MAX && $value >= LONG_MIN) {
settype($row[$key], 'integer');
} else {
settype($row[$key], 'double');
}
break;
}
}
if ($field) {
return $row[$field];
} else {
return $row;
}
}
/**
* Fetches a row from the current result set.
*
* @access public
* @return array
*/
function fetchRow()
{
$row = @mssql_fetch_assoc($this->result);
if (is_array($row)) {
return $row;
}
return false;
}
function fetchArray($r = 0)
{
if (!$r) {
$r = $this->lastResult;
}
$row = mssql_fetch_assoc($r);
return $row;
}
function fetcha($query = null)
{
if ($query == null) {
return $this->convert(mssql_fetch_assoc($this->query));
} else {
return $this->convert(mssql_fetch_assoc($query));
}
}
public function fetchAssoc($result)
{
if (!is_resource($result)) {
throw new DbControlException("Ilegal parameter result. Must be valid result resource.");
} else {
return @mssql_fetch_assoc($result);
}
}
public function result($type = 'object')
{
$result = [];
while ($row = mssql_fetch_assoc($this->result)) {
$result[] = $type == 'object' ? (object) $row : $row;
}
return $result;
}
function query($query)
{
$res = DB::execute($query);
$data = array();
while ($line = mssql_fetch_assoc($res)) {
$data[] = $line;
}
return $data;
}
function fetch()
{
// Attention! Due to a bug in the sybase driver, a boolean 'true' is
// returned when the query was succesful but did not return any rows. We
// work around this problem by checking for this 'true' value.
if ($this->rs === true) {
return false;
}
return mssql_fetch_assoc($this->rs);
}
function getOneRecord()
{
$record = new lmbMssqlRecord();
$queryId = $this->connection->execute($this->getSQL());
$values = mssql_fetch_assoc($queryId);
$record->import($values);
mssql_free_result($queryId);
if (is_array($values)) {
return $record;
}
}
public function getNamaBulanById($id)
{
$sql = 'SELECT Bulan FROM ' . $this->table . ' WHERE BulanId=' . $id;
$result = mssql_query($sql);
if (mssql_num_rows($result) > 0) {
while ($val = mssql_fetch_assoc($result)) {
$namaBulan = $val['Bulan'];
}
}
mssql_free_result($result);
return $bulan;
}
public function getAlasanTidakKbById($id)
{
$sql = 'SELECT * FROM ' . $this->table . ' WHERE ID=' . $id;
$result = mssql_query($sql);
$data = array();
if (mssql_num_rows($result) > 0) {
while ($val = mssql_fetch_assoc($result)) {
$data[] = array('ID' => $val['ID'], 'Alasan' => $val['Alasan'], 'SortNumber' => $val['SortNumber'], 'IsActive' => $val['IsActive']);
}
}
mssql_free_result($result);
return $data;
}
public function getRwByKelurahan($id)
{
$sql = 'SELECT * FROM ' . $this->table . ' WHERE KelurahanId=' . $id;
$result = mssql_query($sql);
$data = array();
if (mssql_num_rows($result) > 0) {
while ($val = mssql_fetch_assoc($result)) {
$data[] = array('RWId' => $val['RWId'], 'Nama' => $val['Nama'], 'KelurahanId' => $val['KelurahanId']);
}
}
mssql_free_result($result);
return $data;
}
public function getAllRecord()
{
$sql = 'SELECT * FROM ' . $this->table . ' ORDER BY TempatPelayananKBId';
$result = mssql_query($sql);
$data = array();
if (mssql_num_rows($result) > 0) {
while ($val = mssql_fetch_assoc($result)) {
$data[] = array('TempatPelayananKBId' => $val['TempatPelayananKBId'], 'Nama' => $val['Nama']);
}
}
mssql_free_result($result);
return $data;
}
private function fetch_next()
{
if (!$this->rsrc) {
return false;
}
if (!($row = mssql_fetch_assoc($this->rsrc))) {
mssql_free_result($this->rsrc);
$this->rsrc = null;
return false;
}
$row = array_change_key_case($row, CASE_LOWER);
return $row;
}
public function getKecamatanByKelurahan($id)
{
$sql = 'SELECT a.Nama, a.KecamatanId, a.KodeDepdagri FROM ' . $this->table . ' a INNER JOIN M_Kelurahan b on a.KecamatanId=b.KecamatanId WHERE b.KelurahanId=' . $id;
$result = mssql_query($sql);
$data = array();
if (mssql_num_rows($result) > 0) {
while ($val = mssql_fetch_assoc($result)) {
$data[] = array('KecamatanId' => $val['KecamatanId'], 'Nama' => $val['Nama'], 'KodeDedagri' => $val['KodeDepdagri']);
}
}
mssql_free_result($result);
return $data;
}
public function __construct()
{
$link = mssql_connect($servername, $username, $password);
if (!$link) {
echo "Can not connect<br/>\n";
} else {
mssql_select_db("robot", $link);
$res = mssql_query("select * from result_tbl", $link);
while ($out = mssql_fetch_assoc($res)) {
$this->results[] = $out['Result'];
}
mssql_close($link);
}
}
function selectQuery($row, $table, $limit)
{
parent::selectQuery($row, $table, $limit);
if (DEMO === true) {
return __CLASS__ . __METHOD__ . $this->query;
} else {
$res = mssql_query($this->query);
$arr = array();
$row = mssql_fetch_assoc($res);
foreach ($row as $arr) {
$query = $arr;
}
return $this->query = $query;
}
}
function get_document($id, $type)
{
//Don't forget to header("Content-Type: pdf");
$value = false;
$query_getall = "SELECT `DocumentKey`,`DocumentTitle` FROM `dmDocument` WHERE `UserDefined9` = {$id} AND `UserDefined8` = '{$type}'";
$result = mssql_query($query_getall, $this->mssql);
if ($result->num_rows() > 0) {
while ($row = mssql_fetch_assoc($result)) {
$document_key = $row['DocumentKey'];
}
$handle = fopen($webddocs_dir . '/' . $document_key . $doc_trail, 'rb');
$value = stream_get_contents($handle);
fclose($handle);
}
return $value;
}
