public function Query($Procedimiento, $RetornaDatos, $arrayValores = "")
{
$parametros = array();
$NombreServidor = constant("sqlHost");
$InfoConexion = array("UID" => constant("sqlUsuario"), "PWD" => constant("sqlContrasena"), "Database" => $this->NombreBaseDatos);
$conn = sqlsrv_connect($NombreServidor, $InfoConexion);
if (is_array($arrayValores)) {
$stringInterrogacion = "(?";
for ($i = 1; $i < count($arrayValores); $i++) {
$stringInterrogacion .= ",?";
}
$stringInterrogacion .= ")";
$Procedimiento = "{call " . $Procedimiento . " " . $stringInterrogacion . "}";
for ($i = 0; $i < count($arrayValores); $i++) {
array_push($parametros, array($arrayValores[$i], SQLSRV_PARAM_IN));
}
} else {
$Procedimiento = "{call " . $Procedimiento . "}";
}
$stmt3 = sqlsrv_query($conn, $Procedimiento, $parametros);
if ($RetornaDatos) {
$array = array();
while ($obj = sqlsrv_fetch_array($stmt3, SQLSRV_FETCH_ASSOC)) {
$array[] = $obj;
}
return $array;
sqlsrv_free_stmt($stmt3);
}
sqlsrv_close($conn);
}
/** Disconnects from database * */
function disconnect()
{
if (DB_TYPE == 'sqlsrv') {
if (@sqlsrv_close($this->_dbHandle) != 0) {
return 1;
} else {
return 0;
}
} else {
if (DB_TYPE == 'mssql') {
if (@mssql_close($this->_dbHandle) != 0) {
return 1;
} else {
return 0;
}
} else {
if (DB_TYPE == 'mysql') {
if (@mysql_close($this->_dbHandle) != 0) {
return 1;
} else {
return 0;
}
}
}
}
}
function GetScheduleWithQuestionsOLD($groupid)
{
include_once 'db_Connection.php';
$conn = sqlsrv_connect($serverName, $connectionInfo);
$questionvalues = array();
$questions = array();
$schedule = array();
$schedulequestions = array();
if ($conn) {
//----get acadyear-------------------
$acid = 0;
$sqlstr = " SELECT [AcadYearID] FROM [dbo].[Groups] where [groupid]=? ";
$params = array($groupid);
$sqlquery = sqlsrv_query($conn, $sqlstr, $params);
if ($sqlquery) {
while ($row = sqlsrv_fetch_array($sqlquery, SQLSRV_FETCH_ASSOC)) {
$acid = $row['AcadYearID'];
}
sqlsrv_free_stmt($sqlquery);
}
$sqlstr = " SELECT [questionTypeId],[value],[text] " . " FROM [dbo].[QuestionValues] " . " order by questionTypeId ";
// $params = array ($acid);
$sqlquery = sqlsrv_query($conn, $sqlstr);
if ($sqlquery) {
while ($row = sqlsrv_fetch_object($sqlquery)) {
$questionvalues[] = $row;
}
sqlsrv_free_stmt($sqlquery);
}
//----get question array-------------------
$sqlstr = " SELECT [QuestionID],[QueastionText],[questionType],[maxmark],[NeedDescription],-1 as mark,'' as description " . " FROM [dbo].[Questions] " . " where [QuestionLecturer]=1 " . " and [Acadyear]=? ";
$params = array($acid);
$sqlquery = sqlsrv_query($conn, $sqlstr, $params);
if ($sqlquery) {
while ($row = sqlsrv_fetch_object($sqlquery)) {
$row->questionValues = array();
foreach ($questionvalues as &$questValue) {
if ($questValue->questionTypeId === $row->questionType) {
array_push($row->questionValues, $questValue);
}
}
$questions[] = $row;
}
sqlsrv_free_stmt($sqlquery);
}
//----get schadule array-------------------
$sqlstr = " SELECT distinct [SubjID],[SubjName],[STypeID],[STypeName], [LectID], [LectName] " . " FROM [dbo].[Schedule] " . " WHERE groupID=? " . " order by [SubjID],[STypeID] ";
$params = array($groupid);
$sqlquery = sqlsrv_query($conn, $sqlstr, $params);
if ($sqlquery) {
while ($row = sqlsrv_fetch_object($sqlquery)) {
$row->quests = $questions;
$schedule[] = $row;
}
$schedulequestions = array("result" => 0, "data" => $schedule);
}
sqlsrv_close($conn);
}
return $schedulequestions;
}
function GetFacultyQuestions($groupid)
{
include_once 'db_Connection.php';
$conn = sqlsrv_connect($serverName, $connectionInfo);
$questionvalues = array();
$questions = array();
$faculty = array();
$facultyquestions = array();
if ($conn) {
//----get acadyear-------------------
$acid = 0;
$sqlstr = " SELECT [AcadYearID] FROM [dbo].[Groups] where [groupid]=? ";
$params = array($groupid);
$sqlquery = sqlsrv_query($conn, $sqlstr, $params);
if ($sqlquery) {
while ($row = sqlsrv_fetch_array($sqlquery, SQLSRV_FETCH_ASSOC)) {
$acid = $row['AcadYearID'];
}
sqlsrv_free_stmt($sqlquery);
}
$sqlstr = " SELECT [questionTypeId],[value],[text] " . " FROM [dbo].[QuestionValues] " . " order by questionTypeId ";
// $params = array ($acid);
$sqlquery = sqlsrv_query($conn, $sqlstr);
if ($sqlquery) {
while ($row = sqlsrv_fetch_object($sqlquery)) {
$questionvalues[] = $row;
}
sqlsrv_free_stmt($sqlquery);
}
//----get question array-------------------
$sqlstr = " SELECT [QuestionID],[QueastionText],[questionType],[maxmark],-1 as mark,'' as description " . " FROM [dbo].[Questions] " . " where [QuestionLecturer]=0 " . " and [Acadyear]=? ";
$params = array($acid);
$sqlquery = sqlsrv_query($conn, $sqlstr, $params);
if ($sqlquery) {
while ($row = sqlsrv_fetch_object($sqlquery)) {
$row->questionValues = array();
foreach ($questionvalues as &$questValue) {
if ($questValue->questionTypeId === $row->questionType) {
array_push($row->questionValues, $questValue);
}
}
$questions[] = $row;
}
sqlsrv_free_stmt($sqlquery);
}
//----get faculty-------------------
$sqlstr = " SELECT [FacultyID],[FacultyName] FROM [dbo].[Groups] " . " where [groupid]=? ";
$params = array($groupid);
$sqlquery = sqlsrv_query($conn, $sqlstr, $params);
if ($sqlquery) {
while ($row = sqlsrv_fetch_object($sqlquery)) {
$row->quests = $questions;
$faculty[] = $row;
}
$facultyquestions = array("result" => 0, "data" => $faculty);
}
sqlsrv_close($conn);
}
return $facultyquestions;
}
function dbcall()
{
session_start();
$seubid = (string) session_id();
$server = "bamsql2";
$options = array("UID" => "genes", "PWD" => "Genes12", "Database" => "genes");
$conn = sqlsrv_connect($server, $options);
if ($conn === false) {
die("<pre>" . print_r(sqlsrv_errors(), true));
}
$rno = $_POST['Rnumber'];
$name = $_POST['name'];
$email = $_POST['email'];
$gender = $_POST['gender'];
$sql = "insert INTO dbo.contactinfo values('{$rno}','{$name}','{$email}','{$gender}')";
$query = sqlsrv_query($conn, $sql);
if ($query === false) {
exit("<pre>" . print_r(sqlsrv_errors(), true));
}
#while ($row = sqlsrv_fetch_array($query))
# { echo "<p>Hello, $row[ascore]!</p>";
#}
sqlsrv_free_stmt($query);
sqlsrv_close($conn);
}
function SaveLecturerMarks($marks)
{
$status = array();
include_once 'db_Connection.php';
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn) {
$login = "******";
$subjectid = 0;
$subjtype = 0;
$lectid = 0;
$qid = 0;
$mark = 0;
$ldescription = "0";
$sqlstr = " insert into [dbo].[MarkLecturer] ([Login],[subjid],[subjtype],[lectid],[qid],[mark],[ldescription]) " . " values (?,?,?,?,?,?,?) ";
$params = array(&$login, &$subjectid, &$subjtype, &$lectid, &$qid, &$mark, &$ldescription);
$stmt = sqlsrv_prepare($conn, $sqlstr, $params);
if ($stmt) {
foreach ($marks as &$data) {
$login = $data->login;
$subjectid = $data->subjectid;
$subjtype = $data->subjtype;
$lectid = $data->lectid;
$qid = $data->qid;
$mark = $data->mark;
$ldescription = $data->description;
if (!sqlsrv_execute($stmt)) {
array_push($status, "not inserted-L" . $login . "-S" . $subjectid . "-T" . $subjtype . "-L" . $lectid . "-Q" . $qid);
}
}
}
sqlsrv_close($conn);
}
return $status;
}
/**
* Close the connection.
*/
public function disconnect()
{
if ($this->connected()) {
sqlsrv_close($this->connection);
}
$this->connected = false;
}
public function __destruct()
{
/*
| About:
| -> Close the connection and returns the elapsed time.
*/
if (!empty($this->connection['debugMode'])) {
$this->errorHandling();
}
switch ($this->connection['db_type']) {
case 'mysql':
$this->conn->close();
break;
case 'mssql':
mssql_close($this->conn);
break;
case 'sqlserv':
sqlsrv_close($this->conn);
break;
case 'pgsql':
pg_close($this->conn);
break;
}
return $this->elapsedTime();
}
/**
* @see MooMsSQL::close()
*
*/
public function close()
{
if ($this->linkId) {
sqlsrv_close($this->linkId);
$this->linkId = false;
}
}
/**
* Disconnect
*
* @return bool success
*/
public function close()
{
if ($this->handle && ($r = sqlsrv_close($this->handle))) {
$this->handle = NULL;
return $r;
}
return FALSE;
}
/**
* Disconnects from the database.
* Does nothing if there was no connection established.
*
* @return void
*/
protected function disconnectInternal()
{
if (!$this->isConnected) {
return;
}
$this->isConnected = false;
sqlsrv_close($this->resource);
}
function CloseConnection()
{
if ($this->databaseType == 'mySql' || $this->databaseType == 'both') {
mysql_close($this->mysqlConnection);
}
if ($this->databaseType == 'sqlServer' || $this->databaseType == 'both') {
sqlsrv_close($this->sqlserverConnection);
}
}
protected function close()
{
if (isset($this->db)) {
$r = sqlsrv_close($this->db);
if ($r) {
$this->db = null;
}
}
}
/**
* This function closes an open connection.
*
* @access public
* @override
* @return boolean whether an open connection was closed
*/
public function close()
{
if ($this->is_connected()) {
if (!@sqlsrv_close($this->resource)) {
return FALSE;
}
$this->resource = NULL;
}
return TRUE;
}
public function close()
{
$this->conn_open = false;
if (!$this->reuse_connection) {
if ($this->handle && !$this->data_result) {
return sqlsrv_close($this->handle);
}
}
return true;
}
public function disconnect()
{
if ($this->con) {
if (sqlsrv_close($this->connection)) {
$this->con = false;
return true;
} else {
return false;
}
}
}
public function getStations()
{
require_once "database_aqo.php";
$stations = array();
$sql = "select Stn_ID, Stn_Name, Stn_Index, Stn_Lat, Stn_Lng\n\t\t\t\t\tfrom Stn_Info\n\t\t\t\t\torder by Stn_Index";
$result = sqlsrv_query($conn, $sql) or die("Invalid Query");
while ($record = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
$stations[] = $record;
}
sqlsrv_close($conn);
return $stations;
}
function WriteData()
{
try {
echo "622";
$conn = OpenConnection();
$sql = "INSERT dbo.Dog OUTPUT INSERTED.Name VALUES (Rosco Jenkins,10)";
$stmt = sqlsrv_query($conn, $sql);
if (!$stmt) {
echo "Error: Doesn't Query";
}
sqlsrv_close($conn);
} catch (Exception $e) {
echo "Error!";
}
}
public function getReadings($stn_ID)
{
require_once "database_aqo.php";
$readings = array();
$sql = "select Reading as value\n\t\t\t\t\tfrom AQI_Data\n\t\t\t\t\twhere Stn_ID = {$stn_ID} and AQI_DateTime between '2011-03-01 00:00:00' and '2011-03-31 23:00:00'\n\t\t\t\t\torder by AQI_DateTime";
$result = sqlsrv_query($conn, $sql) or die("Invalid Query");
while ($record = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
if ($record['value'] == -999 || $record['value'] == 9999) {
$record['value'] = 20;
}
$readings[] = $record;
}
sqlsrv_close($conn);
return $readings;
}
function disconnect()
{
if (function_exists("mysql_connect")) {
mysql_close($this->connection);
} else {
if (function_exists("mssql_connect")) {
mssql_close($this->connection);
} else {
if (function_exists("sqlsrv_connect")) {
sqlsrv_close($this->connection);
} else {
die("ERROR: Unable to find a valid connection driver configured on PH");
}
}
}
}
public function disconnect()
{
try {
// Database is assumed disconnected
$status = TRUE;
if (is_resource($this->_connection)) {
if ($status = sqlsrv_close($this->_connection)) {
// Clear the connection
$this->_connection = NULL;
}
}
} catch (Exception $e) {
// Database is probably not disconnected
$status = !is_resource($this->_connection);
}
return $status;
}
function loginQuery($user, $psw)
{
global $serverName, $connectionInfo;
$conn = sqlsrv_connect($serverName, $connectionInfo);
$query = "Select TOP 1 * from usuario \n\tWHERE usuario='{$user}' OR correo='{$user}' AND contrasena='{$psw}'";
$params = array();
$options = array("Scrollable" => SQLSRV_CURSOR_KEYSET);
$stmt = sqlsrv_query($conn, $query, $params, $options);
$row_count = sqlsrv_num_rows($stmt);
$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
if ($row_count === 1) {
$keys = array('result' => "true", 'id' => $row['id'], 'nombre' => $row['nombre'], 'correo' => $row['correo'], 'usuario' => $row['usuario'], 'pass' => $row['contrasena'], 'puesto' => $row['puesto']);
return $keys;
}
if ($stmt === false) {
die(print_r(sqlsrv_errors(), true));
}
sqlsrv_close($conn);
}
function InsertData($conn)
{
echo "Inside The Insert Function <br>";
try {
$tsql = "INSERT INTO TextQueue (PhoneNumber, TextContent) VALUES ('+17047856193', 'This is a test insert' )";
echo "TSQL Query: " . $tsql . "<br>";
$insertReview = sqlsrv_query($conn, $tsql);
echo "Insert Review: " . $insertReview . "<br>";
if ($insertReview == FALSE) {
echo "Dying <br>";
die(FormatErrors(sqlsrv_errors()));
}
while ($row = sqlsrv_fetch_array($insertReview, SQLSRV_FETCH_ASSOC)) {
echo $row['ID'];
}
sqlsrv_close($conn);
} catch (Exception $e) {
echo "Error!";
}
}
function MSSQL($query)
{
$start = microtime_float();
//Connect to MSSQL
$serverName = "sqlserver.cfv2m251hguu.us-east-1.rds.amazonaws.com,1433";
$connectionInfo = array("Database" => "Cap", "UID" => "admin", "PWD" => "password");
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn === false) {
die(print_r(sqlsrv_errors(), true));
}
$stmt = sqlsrv_query($conn, $query);
if ($stmt === false) {
die(print_r(sqlsrv_errors(), true));
}
$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
$end = microtime_float();
$time = $end - $start;
return number_format($time, 3);
}
function signUp()
{
if (!empty($_POST['username'])) {
$checkUser = $_POST['username'];
unset($query);
$query = "select * from users where username = '******'";
$stmt = sqlsrv_query($GLOBALS['conn'], $query);
$record = sqlsrv_has_rows($stmt);
if (!$record) {
$this->regUser();
} else {
//echo "Sorry! You are already registered.";
sqlsrv_close($GLOBALS['conn']);
die(print_r(sqlsrv_errors(), true));
}
}
if (isset($_POST['submit'])) {
echo "Submit call";
signUp();
}
}
/**
* Closes the open database connection
*
* @internal
*
* @return void
*/
public function __destruct()
{
if (!$this->connection) {
return;
}
fCore::debug('Total query time: ' . $this->query_time . ' seconds', $this->debug);
if ($this->extension == 'ibm_db2') {
db2_close($this->connection);
} elseif ($this->extension == 'mssql') {
mssql_close($this->connection);
} elseif ($this->extension == 'mysql') {
mysql_close($this->connection);
} elseif ($this->extension == 'mysqli') {
mysqli_close($this->connection);
} elseif ($this->extension == 'oci8') {
oci_close($this->connection);
} elseif ($this->extension == 'pgsql') {
pg_close($this->connection);
} elseif ($this->extension == 'sqlite') {
sqlite_close($this->connection);
} elseif ($this->extension == 'sqlsrv') {
sqlsrv_close($this->connection);
} elseif ($this->extension == 'pdo') {
// PDO objects close their own connections when destroyed
}
}
/**
* 关闭数据库
* @access public
*/
public function close()
{
if ($this->_linkID) {
sqlsrv_close($this->_linkID);
}
$this->_linkID = null;
}
/**
* Force the connection to close.
*
* @return void
*/
public function closeConnection()
{
if ($this->isConnected()) {
sqlsrv_close($this->_connection);
}
$this->_connection = null;
}
function _close()
{
if ($this->transCnt) {
$this->RollbackTrans();
}
$rez = @sqlsrv_close($this->_connectionID);
$this->_connectionID = false;
return $rez;
}
/**
* execute a query as DBA
*
* @param string $query the SQL query
* @param mixed $types array that contains the types of the columns in
* the result set
* @param boolean $is_manip if the query is a manipulation query
* @return mixed MDB2_OK on success, a MDB2 error on failure
* @access public
*/
function &standaloneQuery($query, $types = null, $is_manip = false)
{
$user = $this->options['DBA_username'] ? $this->options['DBA_username'] : $this->dsn['username'];
$pass = $this->options['DBA_password'] ? $this->options['DBA_password'] : $this->dsn['password'];
$connection = $this->_doConnect($user, $pass, $this->database_name, $this->options['persistent']);
if (MDB2::isError($connection)) {
return $connection;
}
$query = $this->_modifyQuery($query, $is_manip, $this->limit, $this->offset);
$this->offset = $this->limit = 0;
$result = $this->_doQuery($query, $is_manip, $connection);
if (!MDB2::isError($result)) {
$result = $this->_affectedRows($connection, $result);
}
@sqlsrv_close($connection);
return $result;
}
