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);
}
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;
}
public function checkUserSession()
{
if (!isset($this->SessionID)) {
return array(false, "Session key empty");
}
$SessionID = $this->SessionID;
$sql = "SELECT Email FROM Sessions WHERE SessionID = ?";
$params = array($SessionID);
global $conn;
if ($conn) {
$stmt = sqlsrv_query($conn, $sql, $params);
if ($stmt === false) {
return array(false, "Connection to server failed.");
} else {
if (sqlsrv_has_rows($stmt) > 0) {
$email = '';
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
$email = $row['Email'];
}
return array(true, $email);
} else {
return array(false, "Failed to authenticate");
}
}
}
}
/**
* DiscoverRowBuilder constructor.
* @param array $location
*/
function __construct(array $location)
{
echo '
<div id="' . $location['id_location'] . '" class="contentLocation">
<table style="width:100%" class="table" onclick="openlocation(' . $location['id_location'] . ', \'' . $location['name'] . '\')">
<tr>
<th>Name:</th>
<th>Description:</th>
</tr>
<tr>
<td>' . $location['name'] . '</td>
<td>' . $location['description'] . '</td>
</tr>
</table>';
$filemanager = new FileManager();
$stmt = $filemanager->getImages($location['id_location']);
$images = array();
while ($idImage = sqlsrv_fetch_array($stmt)['id_image']) {
array_push($images, $idImage);
}
if (count($images) > 0) {
echo '<div class="newSlides">';
foreach ($images as $image) {
$fileName = glob('../images/' . $image . '.*')[0];
echo '<img src="../images/' . $fileName . '"\\>';
}
echo '</div>';
}
echo '</div>';
}
public function loadLocations(int $from, int $to, string $nameFilter)
{
$datas = $this->model->loadLocationsByIdAndName($from, $to, $nameFilter);
while ($location = sqlsrv_fetch_array($datas)) {
new DiscoverRowBuilder($location);
}
}
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 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;
}
/**
* Dumps the Founder data into a table
*
* @param int $CompanyID
*/
function dumpFounderData($CompanyID)
{
// include the data access class
include_once "DAO.php";
try {
// create a new data access object
$db = new DAO();
$sql = "EXEC dbo.GetFounderByCompanyID @CompanyID = " . $CompanyID;
// set the select statement
$db->setSQL($sql);
// execute the SQL
if ($db->execute()) {
// did we get some rows
if (sqlsrv_has_rows($db->getResultSet())) {
// output the table and the first row (column headers)
echo '<br>';
echo '<table class="sorted table-autosort:0 table-stripeclass:alternate">';
echo "<thead><tr>";
echo "<th class='table-sortable:default' width='100'>Name</th>";
echo "</tr></thead><tbody>";
// output the table rows
while ($row = sqlsrv_fetch_array($db->getResultSet(), SQLSRV_FETCH_ASSOC)) {
echo '<tr><td class="left" width="70"><a href="../Participant/History.php?wfID=' . $row['ID'] . '" target="_blank">' . $row['FounderName'] . '</a></td></tr>';
}
// finish the table
echo "</tbody></table>";
} else {
echo "<div class='err'>No data found.</div>";
}
}
} catch (Exception $e) {
echo $e->getMessage(), "\n";
}
echo "</br>";
}
private function fetch_next()
{
if ($row = sqlsrv_fetch_array($this->rsrc, SQLSRV_FETCH_ASSOC)) {
$row = array_change_key_case($row, CASE_LOWER);
}
return $row;
}
/**
* Execute Query to obtain one or more objects from the NECLIMS db; returns string on error
*
* @param $sql
* @param optional class specification
*/
function query($sql, $object = NULL)
{
// include object class if specifed
if ($object != NULL) {
require_once $object . ".cls.php";
}
// create a data access object
$dao = new DAO();
// pass the sql statement to the data access object
$dao->setSQL($sql);
// declare an array for storing the row results
$retVal = array();
try {
// run the sql statement
if ($dao->execute() && sqlsrv_has_rows($dao->getResultSet())) {
// object specified.
if ($object != NULL) {
// while there were more results/rows, save the object in the array
while ($row = sqlsrv_fetch_object($dao->getResultSet(), $object . "")) {
$retVal[] = $row;
}
} else {
// while there were more results/rows, save the object in the array
while ($row = sqlsrv_fetch_array($dao->getResultSet(), SQLSRV_FETCH_ASSOC)) {
$retVal[] = $row;
}
}
}
} catch (Exception $e) {
return "Query Error: " . $e->getMessage() . ". SQL: " . $sql . ". Object specified: " . $object;
}
// return to the caller
return $retVal;
//error_log(print_r($retVal, true));
}
function processWalking($results, $fileRow)
{
global $csvData;
global $sqlDB;
global $plat;
//this is where I'm going to do things
foreach ($results as $result) {
$startDate = $result['DateBegan'];
$endDate = $result['DateStopped'];
$diff = date_diff($startDate, $endDate);
$days = $diff->format("%a");
$currentDate = $result['DateBegan'];
$formattedDate = date_format($currentDate, 'm/d/y');
$tempData = array();
while ($currentDate <= $endDate) {
$dayDate = $formattedDate . ' ' . "00:00:00";
$endDay = $formattedDate . ' ' . "23:59:59";
$querry = "SELECT TOP 1 RecID FROM vPhoneData1\n\t\t\t\tWHERE (TaskSetName = '1 PING' or TaskSetName =\n\t\t\t\t '3 REPORT CGI_OR_H2RL') AND LocalTimetag1 > ? and\n\t\t\t\t LocalTimetag1 < ? and cast(FORMDATA as varchar(max)) like\n\t\t\t\t '%" . $plat . "%'";
$stmt = sqlsrv_prepare($sqlDB, $querry, array(&$dayDate, &$endDay));
sqlsrv_execute($stmt);
$fetched = sqlsrv_fetch_array($stmt);
if (!empty($fetched)) {
$formattedDate = date_format($currentDate, 'm/d/y');
$fileRow['Survey Dt'] = $formattedDate;
$fileRow['Completed By'] = $result['TechName'];
$fileRow['Instrument'] = $result['Instrument'];
$fileRow['Rate'] = $result['RateCode'];
$fileRow['Survey Method'] = 'WALK';
$tempData[] = $fileRow;
}
$currentDate = date_add($currentDate, date_interval_create_from_date_string('1 days'));
$formattedDate = date_format($currentDate, 'm/d/y');
$fetched = null;
}
if (!empty($tempData)) {
$daycount = count($tempData);
if ($result[6] > 0) {
$dailyTotal = floor($result[6] / $daycount);
$firstTotal = $dailyTotal + $result[6] % $daycount;
$isFirst = TRUE;
} else {
$dailyTotal = 0;
}
foreach ($tempData as $tempRow) {
if ($isFirst === TRUE) {
$tempRow['Quantity'] = $firstTotal;
$isFirst = FALSE;
} elseif ($dailyTotal > 0) {
$tempRow['Quantity'] = $dailyTotal;
} else {
$tempRow['Quantity'] = 0;
}
if ($tempRow['Quantity'] > 0) {
$csvData[] = $tempRow;
}
}
}
}
}
public static function fetch_array($queryDB = '', $objectStr = '', $fetchType = 'SQLSRV_FETCH_ASSOC')
{
$row = sqlsrv_fetch_array($queryDB, $fetchType);
if (is_object($objectStr)) {
$objectStr($row);
}
return $row;
}
public function _fetch_hash($query_results)
{
if (is_resource($query_results)) {
return sqlsrv_fetch_array($query_results, SQLSRV_FETCH_ASSOC);
} else {
return false;
}
}
function usersymptommanifest($result)
{
$json = array();
while ($row = sqlsrv_fetch_array($result)) {
$json[] = array('id' => $row['id'], 'userid' => $row['userid'], 'datetime' => $row['datetime'], 'symptomtable' => $row['symptomtable'], 'symptomid' => $row['symptomid'], 'symptom' => $row['symptom'], 'rating' => $row['rating'], 'comment' => $row['comment']);
}
return json_encode($json);
}
public function get_all_results_array($result)
{
$return_array = array();
while ($tab = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
$return_array[] = $tab;
}
return $return_array;
}
public function next_record()
{
$this->record = sqlsrv_fetch_array($this->stmt, SQLSRV_FETCH_ASSOC);
if ($this->record === false) {
$this->record = array();
$this->free_result();
}
return $this->record;
}
function getAll($sql)
{
$result = sqlsrv_query($this->con, $sql);
$arr = array();
while ($row = sqlsrv_fetch_array($result)) {
$arr[] = $row;
}
return $arr;
}
/**
* Enviar query para metodo query e devolve resultados como array
*
* @param string $query sql query
*
* @return array
*/
public function results_array($query)
{
$this->query($query);
$results = array();
while ($res = sqlsrv_fetch_array($this->_query, SQLSRV_FETCH_ASSOC)) {
$results[] = $res;
}
return $results;
}
function queryRegion($conn)
{
$sql = "SELECT DISTINCT region, users, avgPageLoadTime FROM API_QUERY order by users desc";
$stmt = sqlsrv_query($conn, $sql);
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) {
echo $row[0] . ", " . $row[1] . ", " . $row[2] . "<br />";
// echo $row[0]."<br />";
}
}
public function load(string $username)
{
$query = 'SELECT * FROM person WHERE username = ?';
$stmt = sqlsrv_query(Database::getConnection(), $query, array($username));
if (sqlsrv_errors()) {
http_response_code(500);
}
return sqlsrv_fetch_array($stmt);
}
function db_exec($conn, $tsql, $params)
{
$stmt = sqlsrv_query($conn, $tsql, $params);
if (!$stmt) {
echo "exec failed.\n";
die(print_r(sqlsrv_errors(), true));
}
$member = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
return $member;
}
public function getCampaignLeader_ByUserID()
{
$query = "SELECT * FROM tblTRX_CampaignLeaders_Hdr WHERE TableID = " . $this->idcl;
$cl = sqlsrv_fetch_array($this->clsDB->getDataset($query));
if (count($cl) != 0) {
return ucwords(trim($cl['CampaignLeader']));
} else {
header('Location: index.php');
}
}
function testsql()
{
include "dbitcon.php";
$sqlq = "SELECT * FROM Roles";
$test = sqlsrv_query($conn, $sqlq);
while ($row = sqlsrv_fetch_array($test, SQLSRV_FETCH_ASSOC)) {
printf("Name: %s Role: %s", $row[DisplayName], $row[role]);
echo "<br><br>";
}
}
public function get_next($res)
{
$data = sqlsrv_fetch_array($res, SQLSRV_FETCH_ASSOC);
foreach ($data as $key => $value) {
if (is_a($value, "DateTime")) {
$data[$key] = $value->format("Y-m-d H:i");
}
}
return $data;
}
function devuelveRowArray($result)
{
$row = sqlsrv_fetch_array($result, SQLSRV_FETCH_NUMERIC);
if ($row == NULL) {
// Si ya termino de recorrer todas las fila
sqlsrv_free_stmt($result);
}
// Se libera de memoria el resultado
return $row;
}
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;
}
function getResponseSets()
{
global $conn;
$qry = "SELECT title, ResponseSet, id from dbo.q_response_sets ORDER BY title";
$rst = sqlsrv_prepare($conn, $qry);
sqlsrv_execute($rst);
while ($row = sqlsrv_fetch_array($rst)) {
echo $row['title'];
}
}
