function RetrieveEmployeesByPosition($employeePosition1 = null, $employeePosition2 = null, $employeePosition3 = null)
{
$dtoArray = array();
$subQuery = "SELECT posId FROM OHPS";
if (!empty($employeePosition1)) {
$subQuery = $subQuery . " WHERE name LIKE '%" . $employeePosition1 . "%'";
}
if (!empty($employeePosition2)) {
$subQuery = $subQuery . " OR name LIKE '%" . $employeePosition2 . "%'";
}
if (!empty($employeePosition3)) {
$subQuery = $subQuery . " OR name LIKE '%" . $employeePosition3 . "%'";
}
$query = "SELECT empID, firstName, middleName, lastName FROM OHEM WHERE position IN (" . $subQuery . ") ORDER BY firstName DESC";
$recordSet = sqlsrv_query($this->sqlserverConnection, $query);
$index = 0;
while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
$dto = new EmployeeDTO();
$dto->empID = $record["empID"];
$dto->firstName = $record["firstName"];
$dto->middleName = $record["middleName"];
$dto->lastName = $record["lastName"];
$dtoArray[$index] = $dto;
$index++;
}
sqlsrv_free_stmt($recordSet);
return $dtoArray;
}
function RetrieveRecordArray($filter = null)
{
$dtoArray = array();
// Procura na tabela OCRD (Partner Card)
$query = "SELECT CardCode, CardName, CardFName, frozenFor, CntctPrsn, Phone1, IndustryC FROM OCRD WHERE " . $filter;
if (empty($filter)) {
$query = "SELECT CardCode, CardName, CardFName, frozenFor, CntctPrsn, Phone1, IndustryC FROM OCRD WHERE CardName IS NOT NULL ORDER BY cardName";
}
$recordSet = sqlsrv_query($this->sqlserverConnection, $query);
$index = 0;
while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
$dto = new BusinessPartnerDTO();
$dto->cardCode = $record['CardCode'];
$dto->cardName = $record['CardName'];
$dto->cardFName = $record['CardFName'];
$dto->inactive = $record['frozenFor'];
$dto->contactPerson = $record['CntctPrsn'];
$dto->telephoneNumber = $record['Phone1'];
$dto->industry = $record['IndustryC'];
$dtoArray[$index] = $dto;
$index++;
}
sqlsrv_free_stmt($recordSet);
return $dtoArray;
}
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);
}
public function getOne($sql)
{
$res = $this->query($sql);
$row = sqlsrv_fetch_array($res, $this->rsType);
sqlsrv_free_stmt($res);
return $row;
}
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 RetrieveRecordArray($filter = null)
{
$dtoArray = array();
$query = "SELECT Address, AddrType, Street, StreetNo, Building, ZipCode, Block, City, State, Country, U_Secretaria FROM CRD1 WHERE " . $filter;
if (empty($filter)) {
$query = "SELECT Address, AddrType, Street, StreetNo, Building, ZipCode, Block, City, State, Country, U_Secretaria FROM CRD1 WHERE Address IS NOT NULL";
}
$recordSet = sqlsrv_query($this->sqlserverConnection, $query);
$index = 0;
while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
$dto = new PartnerAddressDTO();
$dto->addressLabel = $record["Address"];
$dto->addrType = $record["AddrType"];
$dto->street = $record["Street"];
$dto->streetNo = $record["StreetNo"];
$dto->building = $record["Building"];
$dto->zipCode = $record["ZipCode"];
$dto->block = $record["Block"];
$dto->city = $record["City"];
$dto->state = $record["State"];
$dto->country = $record["Country"];
$dto->locationRef = $record["U_Secretaria"];
$dtoArray[$index] = $dto;
$index++;
}
sqlsrv_free_stmt($recordSet);
return $dtoArray;
}
function RetrieveOther($filter = null)
{
$dtoArray = array();
$query = "SELECT OINV.Serial, INV1.Usage, OINV.CardCode, OINV.CardName, OINV.DocTotal, ORCT.CashSum, ORCT.CheckSum, ORCT.TrsfrSum, ORCT.DocDueDate, OSLP.SlpCode, OSLP.SlpName, OINV.U_demFaturamento FROM ORCT ";
$query .= "JOIN RCT2 ON RCT2.DocNum = ORCT.DocEntry ";
$query .= "JOIN OINV ON OINV.DocEntry = RCT2.DocEntry ";
$query .= "JOIN INV1 ON INV1.DocEntry = OINV.DocEntry ";
$query .= "JOIN OSLP ON OSLP.SlpCode = OINV.SlpCode ";
if (!empty($filter)) {
$query .= " WHERE " . $filter;
}
$recordSet = sqlsrv_query($this->sqlserverConnection, $query);
$index = 0;
while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
$dto = new InvoicePaymentDTO();
$dto->serial = $record['Serial'];
$dto->tipo = $record['Usage'];
$dto->cardCode = $record['CardCode'];
$dto->cardName = $record['CardName'];
$dto->valorNotaFiscal = $record['DocTotal'];
$dto->valorDinheiro = $record['CashSum'];
$dto->valorCheque = $record['CheckSum'];
$dto->valorDeposito = $record['TrsfrSum'];
$dto->date = $record['DocDueDate'];
$dto->slpCode = $record['SlpCode'];
$dto->slpName = $record['SlpName'];
$dto->demFaturamento = $record['U_demFaturamento'];
$dtoArray[$index] = $dto;
$index++;
}
sqlsrv_free_stmt($recordSet);
return $dtoArray;
}
function RetrieveRecordArray($filter = null)
{
$dtoArray = array();
$query = "SELECT * FROM ( ";
$query .= "SELECT EQP.customer AS codigoCliente, CLI.cardName + ' (' + CLI.cardCode + ')' AS nomeCliente, EQP.insID AS codigoEquipamento, EQP.manufSN AS serieEquipamento, MDL.id AS codigoModelo, MDL.modelo AS tagModelo, ";
$query .= "FAB.FirmName AS fabricante, CHAM.id AS numeroChamado, CHAM.tempoAtendimento, MONTH(CHAM.dataAtendimento) AS mesReferencia, YEAR(CHAM.dataAtendimento) AS anoReferencia ";
$query .= "FROM MYSQL...chamadoServico CHAM ";
$query .= "JOIN OINS EQP ON CHAM.cartaoEquipamento = EQP.insID ";
$query .= "JOIN OCRD CLI ON EQP.customer = CLI.cardCode ";
$query .= "JOIN MYSQL...modeloEquipamento MDL ON EQP.U_Model = MDL.id ";
$query .= "JOIN OMRC FAB ON MDL.fabricante = FAB.FirmCode ";
$query .= " ) LABOREXPENSES ";
if (isset($filter) && !empty($filter)) {
$query = $query . " WHERE " . $filter;
}
$recordSet = sqlsrv_query($this->sqlserverConnection, $query . " ORDER BY nomeCliente, serieEquipamento");
$index = 0;
while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
$dto = new LaborExpenseDTO();
$dto->codigoCliente = $record["codigoCliente"];
$dto->nomeCliente = $record["nomeCliente"];
$dto->codigoEquipamento = $record["codigoEquipamento"];
$dto->serieEquipamento = $record["serieEquipamento"];
$dto->codigoModelo = $record["codigoModelo"];
$dto->tagModelo = $record["tagModelo"];
$dto->fabricante = $record["fabricante"];
$dto->numeroChamado = $record["numeroChamado"];
$dto->tempoAtendimento = $record["tempoAtendimento"];
$dtoArray[$index] = $dto;
$index++;
}
sqlsrv_free_stmt($recordSet);
return $dtoArray;
}
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 free_result()
{
if (is_resource($this->result_id)) {
sqlsrv_free_stmt($this->result_id);
$this->result_id = FALSE;
}
}
public function query($sql)
{
LogMaster::log($sql);
if ($this->start_from) {
$res = sqlsrv_query($this->connection, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_STATIC));
} else {
$res = sqlsrv_query($this->connection, $sql);
}
if ($res === false) {
$errors = sqlsrv_errors();
$message = array();
foreach ($errors as $error) {
$message[] = $error["SQLSTATE"] . $error["code"] . $error["message"];
}
throw new Exception("SQLSrv operation failed\n" . implode("\n\n", $message));
}
if ($this->insert_operation) {
sqlsrv_next_result($res);
$last = sqlsrv_fetch_array($res);
$this->last_id = $last["dhx_id"];
sqlsrv_free_stmt($res);
}
if ($this->start_from) {
$data = sqlsrv_fetch($res, SQLSRV_SCROLL_ABSOLUTE, $this->start_from - 1);
}
return $res;
}
public function close()
{
if ($this->rsrc) {
sqlsrv_free_stmt($this->rsrc);
$this->rsrc = null;
}
$this->current = null;
}
/**
* This function frees the command reference.
*
* @access public
* @override
*
* @see http://php.net/manual/en/function.sqlsrv-free-stmt.php
*/
public function free()
{
if ($this->command !== NULL) {
@sqlsrv_free_stmt($this->command);
$this->command = NULL;
$this->record = FALSE;
}
}
function RetrieveRecordArray($filter = null)
{
$dtoArray = array();
$query = "SELECT * FROM ( ";
$query .= "SELECT EQP.customer AS codigoCliente, CLI.cardName + ' (' + CLI.cardCode + ')' AS nomeCliente, EQP.insID AS codigoEquipamento, EQP.manufSN AS serieEquipamento, MDL.id AS codigoModelo, MDL.modelo AS tagModelo, FAB.FirmName AS fabricante, ";
$query .= "CHAM.dataAtendimento AS dataDespesa, CASE WHEN DESP.codigoInsumo IS NULL THEN CAST(DESP.quantidade AS VARCHAR) + 'UN ' + DESP.nomeItem ELSE (TINS.tipoInsumo + '( Número do Chamado: ' + CAST(CHAM.id AS VARCHAR) + ' )') END AS descricaoDespesa, ";
$query .= "PROD.AvgPrice AS precoMedio, PROD.U_Durability AS durabilidade, DESP.totalDespesa ";
$query .= "FROM MYSQL...chamadoServico CHAM ";
$query .= "JOIN MYSQL...despesaChamado DESP ON CHAM.id = DESP.codigoChamado ";
$query .= "LEFT JOIN MYSQL...insumo INS ON INS.id = DESP.codigoInsumo ";
$query .= "LEFT JOIN MYSQL...tipoInsumo TINS ON INS.tipoInsumo = TINS.id ";
$query .= "JOIN OINS EQP ON CHAM.cartaoEquipamento = EQP.insID ";
$query .= "LEFT JOIN OITM PROD ON PROD.ItemCode = DESP.codigoItem COLLATE database_default ";
$query .= "JOIN OCRD CLI ON EQP.customer = CLI.cardCode ";
$query .= "JOIN MYSQL...modeloEquipamento MDL ON EQP.U_Model = MDL.id ";
$query .= "JOIN OMRC FAB ON MDL.fabricante = FAB.FirmCode ";
$query .= " UNION ALL ";
$query .= "SELECT EQP.customer AS codigoCliente, CLI.cardName + ' (' + CLI.cardCode + ')' AS nomeCliente, EQP.insID AS codigoEquipamento, EQP.manufSN AS serieEquipamento, MDL.id AS codigoModelo, MDL.modelo AS tagModelo, FAB.FirmName AS fabricante, ";
$query .= "PED.data AS dataDespesa, CAST(ITM.quantidade AS VARCHAR) + 'UN ' + ITM.nomeItem AS descricaoDespesa, PROD.AvgPrice AS precoMedio, PROD.U_Durability AS durabilidade, ITM.total AS totalDespesa ";
$query .= "FROM MYSQL...pedidoConsumivel PED ";
$query .= "JOIN MYSQL...solicitacaoItem ITM ON ITM.pedidoConsumivel_id = PED.id ";
$query .= "JOIN OINS EQP ON PED.codigoCartaoEquipamento = EQP.insID ";
$query .= "LEFT JOIN OITM PROD ON PROD.ItemCode = ITM.codigoItem COLLATE database_default ";
$query .= "JOIN OCRD CLI ON EQP.customer = CLI.cardCode ";
$query .= "JOIN MYSQL...modeloEquipamento MDL ON EQP.U_Model = MDL.id ";
$query .= "JOIN OMRC FAB ON MDL.fabricante = FAB.FirmCode ";
$query .= " ) DESPESAS ";
if (isset($filter) && !empty($filter)) {
$query = $query . " WHERE " . $filter;
}
$recordSet = sqlsrv_query($this->sqlserverConnection, $query . " ORDER BY nomeCliente, dataDespesa, serieEquipamento");
$index = 0;
while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
$dto = new EquipmentExpenseDTO();
$dto->codigoCliente = $record["codigoCliente"];
$dto->nomeCliente = $record["nomeCliente"];
$dto->codigoEquipamento = $record["codigoEquipamento"];
$dto->serieEquipamento = $record["serieEquipamento"];
$dto->codigoModelo = $record["codigoModelo"];
$dto->tagModelo = $record["tagModelo"];
$dto->fabricante = $record["fabricante"];
$dto->dataDespesa = $record["dataDespesa"];
$dto->descricaoDespesa = $record["descricaoDespesa"];
$dto->precoMedioUnitario = $record["precoMedio"];
$dto->vidaUtil = $record["durabilidade"];
$dto->totalDespesa = $record["totalDespesa"];
$dtoArray[$index] = $dto;
$index++;
}
sqlsrv_free_stmt($recordSet);
return $dtoArray;
}
function RetrieveRecordArray($filter = null)
{
$dtoArray = array();
$query = "SELECT * FROM ( ";
$query .= "SELECT EQP.customer AS codigoCliente, CLI.cardName + ' (' + CLI.cardCode + ')' AS nomeCliente, EQP.insID AS codigoEquipamento, EQP.manufSN AS serieEquipamento, MDL.id AS codigoModelo, MDL.modelo AS tagModelo, FAB.FirmName AS fabricante, ";
$query .= "LEITURA.data AS dataLeitura, CONTADOR.nome AS tipoMedidor, LEITURA.contagem AS medicao, LEITURA.ajusteContagem AS ajusteLeitura, CASE WHEN LEITURA.reset = 0 THEN 'N' ELSE 'S' END AS reset, FUNCIONARIO.firstName + ' ' + FUNCIONARIO.lastName AS assinaturaDatacopy, ";
$query .= "LEITURA.assinaturaCliente AS assinaturaCliente, LEITURA.obs AS observacao, FORMA.nome AS formaLeitura, ORIGEM.nome AS origemLeitura, ORIGEM.id AS idOrigemLeitura ";
$query .= "FROM MYSQL...leitura LEITURA ";
$query .= "JOIN OINS EQP ON LEITURA.codigoCartaoEquipamento = EQP.insID ";
$query .= "JOIN OCRD CLI ON EQP.customer = CLI.cardCode ";
$query .= "JOIN MYSQL...modeloEquipamento MDL ON EQP.U_Model = MDL.id ";
$query .= "JOIN OMRC FAB ON MDL.fabricante = FAB.FirmCode ";
$query .= "JOIN MYSQL...contador CONTADOR ON LEITURA.contador_id = CONTADOR.id ";
$query .= "JOIN OHEM FUNCIONARIO ON LEITURA.assinaturaDatacopy = FUNCIONARIO.empId ";
$query .= "JOIN MYSQL...formaLeitura FORMA ON LEITURA.formaLeitura_id = FORMA.id ";
$query .= "JOIN MYSQL...origemLeitura ORIGEM ON LEITURA.origemLeitura_id = ORIGEM.id ";
$query .= " ) LEITURAS ";
if (isset($filter) && !empty($filter)) {
$query = $query . " WHERE " . $filter;
}
$recordSet = sqlsrv_query($this->sqlserverConnection, $query . " ORDER BY dataLeitura");
$index = 0;
while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
$dto = new EquipmentReadingDTO();
$dto->codigoCliente = $record["codigoCliente"];
$dto->nomeCliente = $record["nomeCliente"];
$dto->codigoEquipamento = $record["codigoEquipamento"];
$dto->serieEquipamento = $record["serieEquipamento"];
$dto->codigoModelo = $record["codigoModelo"];
$dto->tagModelo = $record["tagModelo"];
$dto->fabricante = $record["fabricante"];
$dto->dataLeitura = $record["dataLeitura"];
$dto->tipoMedidor = $record["tipoMedidor"];
$dto->medicao = $record["medicao"];
$dto->ajusteLeitura = $record["ajusteLeitura"];
$dto->reset = $record["reset"];
$dto->assinaturaDatacopy = $record["assinaturaDatacopy"];
$dto->assinaturaCliente = $record["assinaturaCliente"];
$dto->observacao = $record["observacao"];
$dto->formaLeitura = $record["formaLeitura"];
$dto->origemLeitura = $record["origemLeitura"];
$dtoArray[$index] = $dto;
$index++;
}
sqlsrv_free_stmt($recordSet);
return $dtoArray;
}
function search()
{
include "dbitcon.php";
if ($conn === false) {
die(print_r(sqlsrv_errors(), true));
}
/* Passed Keywords */
$q = str_replace("'", "''", $_GET['q']);
$tsql = "SELECT Description FROM LookUp WHERE Description LIKE '%{$q}%' AND LookUp_Type = 25";
$stat = sqlsrv_query($conn, $tsql);
if ($stat === false) {
die(print_r(sqlsrv_errors(), true));
}
while ($row = sqlsrv_fetch_array($stat, SQLSRV_FETCH_ASSOC)) {
echo $row['Description'] . "<br />";
}
sqlsrv_free_stmt($stat);
}
public function query($sql)
{
LogMaster::log($sql);
if ($this->start_from) {
$res = sqlsrv_query($this->connection, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_STATIC));
} else {
$res = sqlsrv_query($this->connection, $sql);
}
if ($this->insert_operation) {
sqlsrv_next_result($res);
$last = sqlsrv_fetch_array($res);
$this->last_id = $last["dhx_id"];
sqlsrv_free_stmt($res);
}
if ($this->start_from) {
$data = sqlsrv_fetch($res, SQLSRV_SCROLL_ABSOLUTE, $this->start_from - 1);
}
return $res;
}
function RetrieveRecordArray($filter = null)
{
$dtoArray = array();
$query = "SELECT FirmCode, FirmName FROM OMRC WHERE " . $filter;
if (empty($filter)) {
$query = "SELECT FirmCode, FirmName FROM OMRC ORDER BY FirmCode ASC";
}
$recordSet = sqlsrv_query($this->sqlserverConnection, $query);
$index = 0;
while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
$dto = new ManufacturerDTO();
$dto->FirmCode = $record["FirmCode"];
$dto->FirmName = $record["FirmName"];
$dtoArray[$index] = $dto;
$index++;
}
sqlsrv_free_stmt($recordSet);
return $dtoArray;
}
function RetrieveRecordArray($filter = null)
{
$dtoArray = array();
$query = "SELECT IndCode, IndName, IndDesc FROM OOND WHERE " . $filter;
if (empty($filter)) {
$query = "SELECT IndCode, IndName, IndDesc FROM OOND ORDER BY IndCode ASC";
}
$recordSet = sqlsrv_query($this->sqlserverConnection, $query);
$index = 0;
while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
$dto = new IndustryDTO();
$dto->id = $record["IndCode"];
$dto->name = $record["IndName"];
$dto->description = $record["IndDesc"];
$dtoArray[$index] = $dto;
$index++;
}
sqlsrv_free_stmt($recordSet);
return $dtoArray;
}
public function nextRecord()
{
if (!is_resource($this->handle)) {
return false;
}
if ($data = sqlsrv_fetch_array($this->handle, SQLSRV_FETCH_ASSOC)) {
// special case for sqlsrv - date values are DateTime coming out of the sqlsrv drivers,
// so we convert to the usual Y-m-d H:i:s value!
foreach ($data as $name => $value) {
if ($value instanceof DateTime) {
$data[$name] = $value->format('Y-m-d H:i:s');
}
}
return $data;
} else {
// Free the handle if there are no more results - sqlsrv crashes if there are too many handles
sqlsrv_free_stmt($this->handle);
$this->handle = null;
}
return false;
}
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 RetrieveRecordArray($filter = null)
{
$dtoArray = array();
// Procura na tabela OSLP (Sales Person)
$query = "SELECT SlpCode, SlpName, Commission, U_SerializedData FROM OSLP WHERE " . $filter;
if (empty($filter)) {
$query = "SELECT SlpCode, SlpName, Commission, U_SerializedData FROM OSLP";
}
$recordSet = sqlsrv_query($this->sqlserverConnection, $query);
$index = 0;
while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
$dto = new SalesPersonDTO();
$dto->slpCode = $record['SlpCode'];
$dto->slpName = $record['SlpName'];
$dto->commission = $record['Commission'];
$dto->serializedData = $record['U_SerializedData'];
$dtoArray[$index] = $dto;
$index++;
}
sqlsrv_free_stmt($recordSet);
return $dtoArray;
}
function RetrieveRecordArray($filter = null)
{
$dtoArray = array();
$query = "SELECT Code, U_InsId, U_ItemCode, U_ItemName, U_Amount FROM [@ACCESSORIES] WHERE " . $filter;
if (empty($filter)) {
$query = "SELECT Code, U_InsId, U_ItemCode, U_ItemName, U_Amount FROM [@ACCESSORIES]";
}
$recordSet = sqlsrv_query($this->sqlserverConnection, $query);
$index = 0;
while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
$dto = new AccessoriesDTO();
$dto->id = $record["Code"];
$dto->equipmentCode = $record["U_InsId"];
$dto->itemCode = $record["U_ItemCode"];
$dto->itemName = $record["U_ItemName"];
$dto->amount = $record["U_Amount"];
$dtoArray[$index] = $dto;
$index++;
}
sqlsrv_free_stmt($recordSet);
return $dtoArray;
}
function RetrieveRecordArray($filter = null)
{
$dtoArray = array();
// Procura na tabela OCPR (Contact Person)
$query = "SELECT CntctCode, Name, Tel1, Cellolar, E_MailL FROM OCPR WHERE " . $filter . " ORDER BY Name";
if (empty($filter)) {
$query = "SELECT CntctCode, Name, Tel1, Cellolar, E_MailL FROM OCPR ORDER BY Name";
}
$recordSet = sqlsrv_query($this->sqlserverConnection, $query);
$index = 0;
while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
$dto = new ContactPersonDTO();
$dto->cntctCode = $record["CntctCode"];
$dto->name = $record["Name"];
$dto->phoneNumber = $record["Tel1"];
$dto->cellNumber = $record["Cellolar"];
$dto->email = $record["E_MailL"];
$dtoArray[$index] = $dto;
$index++;
}
sqlsrv_free_stmt($recordSet);
return $dtoArray;
}
/**
* 执行存储过程，多结果集
* @param $sql
* @param $params
* @return array
*/
function sp_execute_multi($sql, $params)
{
$conn = get_sqlsrv_conn();
if ($conn === false) {
die(print_r(sqlsrv_errors(), true));
}
$stmt = sqlsrv_query($conn, $sql, $params);
if ($stmt === false) {
die(print_r(sqlsrv_errors(), true));
}
//
$rss = array();
do {
$rs = array();
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
array_push($rs, $row);
}
array_push($rss, $rs);
} while (sqlsrv_next_result($stmt));
sqlsrv_free_stmt($stmt);
free_sqlsrv_conn($conn);
return $rss;
}
/**
* Closes the cursor, allowing the statement to be executed again.
*
* @return bool
*/
public function closeCursor()
{
if (!$this->_stmt) {
return false;
}
sqlsrv_free_stmt($this->_stmt);
$this->_stmt = false;
return true;
}
/**
* Executes an SQL query
*
* @param string|fStatement $statement The statement to perform
* @param fResult $result The result object for the query
* @param array $params The parameters for prepared statements
* @return void
*/
private function performQuery($statement, $result, $params)
{
fCore::startErrorCapture();
$extra = NULL;
if (is_object($statement)) {
$statement->executeQuery($result, $params, $extra, $statement != $this->statement);
} elseif ($this->extension == 'ibm_db2') {
$extra = db2_exec($this->connection, $statement, array('cursor' => DB2_FORWARD_ONLY));
if (is_resource($extra)) {
$rows = array();
while ($row = db2_fetch_assoc($extra)) {
$rows[] = $row;
}
$result->setResult($rows);
unset($rows);
} else {
$result->setResult($extra);
}
} elseif ($this->extension == 'mssql') {
$result->setResult(mssql_query($result->getSQL(), $this->connection));
} elseif ($this->extension == 'mysql') {
$result->setResult(mysql_query($result->getSQL(), $this->connection));
} elseif ($this->extension == 'mysqli') {
$result->setResult(mysqli_query($this->connection, $result->getSQL()));
} elseif ($this->extension == 'oci8') {
$extra = oci_parse($this->connection, $result->getSQL());
if (oci_execute($extra, $this->inside_transaction ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS)) {
oci_fetch_all($extra, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
$result->setResult($rows);
unset($rows);
} else {
$result->setResult(FALSE);
}
} elseif ($this->extension == 'pgsql') {
$result->setResult(pg_query($this->connection, $result->getSQL()));
} elseif ($this->extension == 'sqlite') {
$result->setResult(sqlite_query($this->connection, $result->getSQL(), SQLITE_ASSOC, $extra));
} elseif ($this->extension == 'sqlsrv') {
$extra = sqlsrv_query($this->connection, $result->getSQL());
if (is_resource($extra)) {
$rows = array();
while ($row = sqlsrv_fetch_array($extra, SQLSRV_FETCH_ASSOC)) {
$rows[] = $row;
}
$result->setResult($rows);
unset($rows);
} else {
$result->setResult($extra);
}
} elseif ($this->extension == 'pdo') {
if (preg_match('#^\\s*CREATE(\\s+OR\\s+REPLACE)?\\s+TRIGGER#i', $result->getSQL())) {
$this->connection->exec($result->getSQL());
$extra = FALSE;
$returned_rows = array();
} else {
$extra = $this->connection->query($result->getSQL());
if (is_object($extra)) {
// This fixes a segfault issue with blobs and fetchAll() for pdo_ibm
if ($this->type == 'db2') {
$returned_rows = array();
$scanned_for_blobs = FALSE;
$blob_columns = array();
while (($row = $extra->fetch(PDO::FETCH_ASSOC)) !== FALSE) {
if (!$scanned_for_blobs) {
foreach ($row as $key => $value) {
if (is_resource($value)) {
$blob_columns[] = $key;
}
}
}
foreach ($blob_columns as $blob_column) {
$row[$blob_column] = stream_get_contents($row[$blob_column]);
}
$returned_rows[] = $row;
}
} else {
$returned_rows = $extra->fetchAll(PDO::FETCH_ASSOC);
}
} else {
$returned_rows = $extra;
}
// The pdo_pgsql driver likes to return empty rows equal to the number of affected rows for insert and deletes
if ($this->type == 'postgresql' && $returned_rows && $returned_rows[0] == array()) {
$returned_rows = array();
}
}
$result->setResult($returned_rows);
}
$this->statement = $statement;
$this->handleErrors(fCore::stopErrorCapture());
$this->checkForError($result, $extra);
if ($this->extension == 'ibm_db2') {
$this->setAffectedRows($result, $extra);
if ($extra && !is_object($statement)) {
db2_free_result($extra);
}
} elseif ($this->extension == 'pdo') {
$this->setAffectedRows($result, $extra);
if ($extra && !is_object($statement)) {
$extra->closeCursor();
}
} elseif ($this->extension == 'oci8') {
$this->setAffectedRows($result, $extra);
if ($extra && !is_object($statement)) {
oci_free_statement($extra);
}
} elseif ($this->extension == 'sqlsrv') {
$this->setAffectedRows($result, $extra);
if ($extra && !is_object($statement)) {
sqlsrv_free_stmt($extra);
}
} else {
$this->setAffectedRows($result, $extra);
}
$this->setReturnedRows($result);
$this->handleAutoIncrementedValue($result, $extra);
}
/**
* 用于获取最后插入的ID
* @access public
* @return integer
*/
public function mssql_insert_id()
{
$query = "SELECT @@IDENTITY as last_insert_id";
$result = sqlsrv_query($this->_linkID, $query);
list($last_insert_id) = sqlsrv_fetch_array($result);
sqlsrv_free_stmt($result);
return $last_insert_id;
}
function _close()
{
$rez = sqlsrv_free_stmt($this->_queryID);
$this->_queryID = false;
return $rez;
}
private function get_result()
{
$output = array();
$stmt1 = sqlsrv_prepare( $this->link, $this->sp_prepared_call, $this->params);
if( !$stmt1 )
$output = -1;
$stmt = sqlsrv_execute($stmt1);
if ($stmt == TRUE){
while ( $aRow = sqlsrv_fetch_array( $stmt1 ) )
{
$output[] = $aRow;
}
} else {
$output = -1;
}
///Free Resource
if( !$stmt1 )
sqlsrv_free_stmt($stmt1);
return $output;
}
