function order_list_by_time($filter)
{
//require_once('includes/sql_connection.inc.php');
global $DBC;
$result = pg_query_params($DBC, "SELECT \n\t\t\t\t\t\t\t\t\t\t\tdate_trunc(\$1, orderdate) as date_filter, \n\t\t\t\t\t\t\t\t\t\t\tcount(distinct orderid) as numberof_order,\n\t\t\t\t\t\t\t\t\t\t\tcount(distinct customerid) as numberof_customer,\n\t\t\t\t\t\t\t\t\t\t\tcount(distinct prod_id) as numberof_prod,\n\t\t\t\t\t\t\t\t\t\t\tsum(quantity) as quantity_by_time,\n\t\t\t\t\t\t\t\t\t\t\tsum(total) as total_by_time \n\t\t\t\t\t\t\t\t\t\tFROM orders NATURAL JOIN orderlines\n\t\t\t\t\t\t\t\t\t\tGROUP BY date_filter\n\t\t\t\t\t\t\t\t\t\tORDER BY total_by_time DESC", array($filter));
if ($result) {
$order_list_by_time = pg_fetch_all($result);
switch ($filter) {
case 'day':
$format = 'D d/M/Y';
break;
case 'week':
$format = 'W M/Y';
break;
case 'month':
$format = 'M/Y';
break;
case 'year':
$format = 'Y';
break;
default:
break;
}
for ($i = 0; $i < count($order_list_by_time); $i++) {
echo '<tr>
<td>' . date($format, strtotime($order_list_by_time[$i]['date_filter'])) . '</td>
<td>' . $order_list_by_time[$i]['numberof_order'] . '</td>
<td>' . $order_list_by_time[$i]['numberof_customer'] . '</td>
<td>' . $order_list_by_time[$i]['numberof_prod'] . '</td>
<td>' . $order_list_by_time[$i]['quantity_by_time'] . '</td>
<td style="font-weight: bold;" class="price">' . round($order_list_by_time[$i]['total_by_time']) . '</td>
</tr>';
}
}
}
function get_shifts($date, $date_of_week, $time)
{
$query = "select * from shifts where date= \$1 and day_of_week = \$2 and time = \$3";
$result = pg_query_params($GLOBALS['db'], $query, array($handle, $shift, $time));
$rows = pg_fetch_all($result);
return $rows;
}
public function advanceSearchForUser($owner, $item_number, $pos_review, $neg_review, $ownerSort, $activitySort)
{
$query = "SELECT a.username, a.display_pic, SUM(CASE WHEN a.value_type='review' THEN a.positive END) AS pos_review, SUM(CASE WHEN a.value_type='review' THEN a.negative END) AS neg_review, SUM(CASE WHEN a.value_type='item' THEN a.positive END) AS item FROM augmented_member a WHERE ";
$ownerCondition = " TRUE ";
if (isset($owner) && !empty($owner)) {
$ownerCondition = " a.username LIKE '%{$owner}%' ";
}
$itemNumCondition = " TRUE ";
if (isset($item_number) && !empty($item_number)) {
$itemNumCondition = " EXISTS(SELECT * FROM augmented_member a1 \n WHERE a1.value_type = 'item' \n AND a1.positive >= {$item_number}\n AND a1.username = a.username) ";
}
$posNumCondition = " TRUE ";
if (isset($pos_review) && !empty($pos_review)) {
$posNumCondition = " EXISTS(SELECT * FROM augmented_member a2 \n WHERE a2.value_type = 'review' \n AND a2.positive >= {$pos_review}\n AND a2.username = a.username) ";
}
$negNumCondition = " TRUE ";
if (isset($neg_review) && !empty($neg_review) && $neg_review != "unlimited") {
$negNumCondition = " EXISTS(SELECT * FROM augmented_member a3 \n WHERE a3.value_type = 'review' \n AND a3.negative <= {$neg_review}\n AND a3.username = a.username) ";
}
$query = $query . $ownerCondition . " AND " . $itemNumCondition . " AND " . $posNumCondition . " AND " . $negNumCondition . " AND a.account_type = 'member' GROUP BY a.username, a.display_pic ";
if (isset($ownerSort) && !empty($ownerSort) && isset($activitySort) && !empty($activitySort)) {
$query = $query . " ORDER BY a.username " . $ownerSort . ", " . "SUM(*) as activity " . $activitySort . ";";
} else {
if (isset($ownerSort) && !empty($ownerSort)) {
$query = $query . " ORDER BY a.username " . $ownerSort . " ";
}
if (isset($activitySort) && !empty($activitySort)) {
$query = $query . " ORDER BY SUM(a.positive) " . $activitySort . " ";
}
}
return pg_fetch_all(pg_query($query));
}
function getSubsTypeDetailDesc($subscriber_type)
{
$qry = "select productname, productdescription, subcriptionrate as subscriptionrate \nfrom techmatcher.subscriptiontype where subscribertype_id =\$1 order by subscriptionrate asc";
$result = pg_query_params($qry, array($subscriber_type));
$value = pg_fetch_all($result);
return $value;
}
/**
* Взять информацию по найденным результатам
*
* @return array массив с пользователями
*/
function getRecords($order_by = NULL)
{
if ($this->matches) {
$sql = "SELECT * FROM search_users_simple WHERE id IN (" . implode(', ', $this->matches) . ')';
if ($order_by) {
$sql .= " ORDER BY {$order_by}";
} else {
if ($this->_sortby && (($desc = $this->_sort == SPH_SORT_ATTR_DESC) || $this->_sort == SPH_SORT_ATTR_ASC)) {
$sql .= " ORDER BY {$this->_sortby}" . ($desc ? ' DESC' : '');
}
}
if ($res = pg_query(DBConnect(), $sql)) {
if (!$order_by && ($this->_sort == SPH_SORT_RELEVANCE || $this->_sort == SPH_SORT_EXTENDED)) {
$links = array();
$rows = array();
while ($row = pg_fetch_assoc($res)) {
$links[$row['id']] = $row;
}
for ($i = 0; $i < count($this->matches); $i++) {
$rows[] = $links[$this->matches[$i]];
}
} else {
$rows = pg_fetch_all($res);
}
return $rows;
}
}
return array();
}
function get_provider_billing_address($provider_id)
{
$qry_provider_address = "SELECT address.* FROM techmatcher.serviceprovidertoaddress, techmatcher.address\r\n WHERE serviceprovidertoaddress.address_id = address.address_id AND serviceprovidertoaddress.serviceprovider_id = \$1 AND\r\n serviceprovidertoaddress.address_deleted = FALSE AND address.addresstype_id = 4\r\n ORDER BY address.country ASC, address.state ASC, address.city ASC, address.addressline1 ASC";
$result = pg_query_params($qry_provider_address, array($provider_id)) or die(pg_errormessage());
$result1 = pg_fetch_all($result);
return $result1;
}
public function view()
{
session_start();
include 'models/searchModel.php';
$searchModel = new searchModel();
$isUserSearch = false;
$itemArray = false;
$search = '';
if (!empty($_REQUEST)) {
if ($_POST['action'] == 'search' && !empty($_POST['search'])) {
$search = $_POST['search'];
$result = $searchModel->searchByItemName($_POST['search']);
$itemArray = pg_fetch_all($result);
}
if ($_REQUEST['action'] == 'searchForItem') {
$search = $_REQUEST['item'];
$unavailable_item = empty($_REQUEST['unavailable_item']) ? '' : $_REQUEST['unavailable_item'];
$result = $searchModel->advanceSearchForItem($_REQUEST['item'], $_REQUEST['owner'], $_REQUEST['category'], $_REQUEST['price_start'], $_REQUEST['price_end'], $_REQUEST['location'], $_REQUEST['date_start'], $_REQUEST['date_end'], $unavailable_item);
$itemArray = pg_fetch_all($result);
}
if ($_POST['action'] == 'searchForUser') {
$isUserSearch = true;
$data = $searchModel->advanceSearchForUser($_POST['owner'], $_POST['item_number'], $_POST['pos_review'], $_POST['neg_review'], $_POST['ownerSort'], $_POST['activitySort']);
}
}
include 'views/search.php';
}
function getQueryResults($fname, $lname, $quiz, $coursecode)
{
$connectionString = "host=ec2-54-225-101-64.compute-1.amazonaws.com port=5432 dbname=d1nigmib60rp1v user=jykiewmddlbjft password=kRqkD183znoOpPNTlDq6f_Xs29";
$dbconnection = pg_connect($connectionString);
$result = pg_query($dbconnection, "SELECT u.fname, u.lname, u.id, r.quizname, q.coursecode, r.score, r.timetaken, r.datetaken\n\t\tFROM result r\n\t\tinner join useraccount u on r.userid=u.id\n\t\tinner join quiz q on r.quizname=q.name\n\t\twhere u.fname like '%{$fname}%'\n\t\tand u.lname like '%{$lname}%'\n\t\tand r.quizname like '%{$quiz}%'\n\t\tand q.coursecode like '%{$coursecode}%'\n\t\torder by score desc");
return json_encode(pg_fetch_all($result));
}
public function getall()
{
if ($this->resource === false) {
return;
}
return pg_fetch_all($this->resource);
}
function queryTable($query)
{
$this->connection = pg_connect("{$this->connstring}") or die("Connection failed: " . pg_last_error());
$result = pg_query($query) or die("Query failed: " . pg_last_error());
$fetch = pg_fetch_all($result);
return $fetch;
}
function getAllResultsGroupedByStudentAndCourse()
{
$connectionString = "host=ec2-54-225-101-64.compute-1.amazonaws.com port=5432 dbname=d1nigmib60rp1v user=jykiewmddlbjft password=kRqkD183znoOpPNTlDq6f_Xs29";
$dbconnection = pg_connect($connectionString);
$result = pg_query($dbconnection, " SELECT fname,lname,emailaddress,coursecode,AVG(score) as average_score,COUNT(*) number_taken\n \t\t\tFROM useraccount LEFT JOIN result LEFT JOIN quiz ON quiz.name=result.quizname ON useraccount.id=result.userid\n\t\t\tGROUP BY useraccount.id,useraccount.fname,useraccount.lname,quiz.coursecode,useraccount.emailaddress\n\t\t\tORDER BY coursecode,AVG(score) desc;");
return json_encode(pg_fetch_all($result));
}
public static function query($query, $instance = null)
{
if ($instance === null) {
$instance = Db::$lastInstance;
}
$result = pg_query(Db::get($instance), $query);
return is_bool($result) ? null : pg_fetch_all($result);
}
function updateProviderBillingAddress($provider_id, $address1, $address2, $city, $state, $zipcode, $phone)
{
$qry = "select aa.address_id from techmatcher.serviceprovidertoaddress it,techmatcher.address aa where it.serviceprovider_id=\$1 and aa.addresstype_id=4 AND aa.address_id = it.address_id and it.address_deleted=\$2;";
$r1 = pg_query_params($qry, array($cons_id, 'FALSE'));
$r2 = pg_fetch_all($r1);
$address_id = $r2[0]['address_id'];
pg_query_params("Update techmatcher.address set addressline1 =\$1, addressline2 =\$2,city= \$3, state = \$4, country = \$5, zipcode = \$6, phonenumber= \$7 Where address_id=\$8", array($address1, $address2, $city, $state, 'USA', $zipcode, $phone, $address_id));
}
function getChotelData()
{
global $dbconn;
$sql = "select * from chotel";
$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error());
$dataArr = pg_fetch_all($result);
//return $dataArr;
echo json_encode($dataArr);
}
public function select($query)
{
$result = call_user_func_array([$this, 'query'], func_get_args());
$rows = pg_fetch_all($result);
if (!is_array($rows)) {
return [];
}
return $rows;
}
/**
* Выполняет параметризованный SQL запрос и возвращает его результат
* @param string $query - текст запроса
* @param array $bindvars - массив параметров
* @return resource
* @throws Exception
*/
function execQuery($query, $bindvars = array())
{
$result = pg_query_params($this->connection, $query, $bindvars);
if ($result === false) {
throw new Exception(pg_last_error());
}
$result = pg_fetch_all($result);
return $result === false ? array() : $result;
}
/**
* Requete PGIS pour prendre le point le plus proche d'un bar
* @return type
*/
public function rendNodeLePlusProche()
{
$lat = $_GET['lat'];
$lon = $_GET['lon'];
$sql = "SELECT * FROM ways_vertices_pgr ORDER BY the_geom <-> ST_GeometryFromText('POINT({$lon} {$lat})',4326) LIMIT 1;";
$rs = pg_query($this->conn, $sql);
$result = pg_fetch_all($rs);
return $result;
}
private function getData($userReq, $channel, $team)
{
$userEscaped = pg_escape_string($userReq);
$channelEscaped = pg_escape_string($channel);
$teamEscaped = pg_escape_string($team);
$sql = $this->get_sql($channelEscaped, $teamEscaped, $userEscaped);
$result = pg_query($this->conn, $sql);
$respost = pg_fetch_all($result);
$values = array();
$days = array();
$flag = false;
for ($i = 0; $i <= 7; $i++) {
for ($e = 0; $e < count($respost); $e++) {
if ($i == $respost[$e]['DiaSemana'] && !$flag) {
$values[$i] = $respost[$e]['valor'];
$days[$i] = $respost[$e]['DiaSemana'];
$flag = true;
}
}
if (!$flag) {
$values[$i] = '0.0000';
$days[$i] = $i;
}
$flag = false;
}
array_shift($values);
array_shift($days);
$sql2 = $this->get_sql($channel, $team);
$result2 = pg_query($this->conn, $sql2);
$respost2 = pg_fetch_all($result2);
$ds = $respost2[0]['DiaSemana'];
$qids = 0;
$arr = array();
$tds = 0;
for ($i = 0; $i <= count($respost2); $i++) {
$item = $respost2[$i];
if ($item['DiaSemana'] != $ds) {
$arr[$ds] = $tds / $qids;
$ds = $respost2[$i]['DiaSemana'];
$qids = 0;
$tds = 0;
}
$tds += $item['valor'];
$qids++;
}
$arr2 = array();
for ($i = 0; $i <= 7; $i++) {
if (!isset($arr[$i])) {
$arr2[] = '0.0000';
} else {
$arr2[] = $arr[$i];
}
}
array_shift($arr2);
return array('valores' => $values, 'valores_channel' => $arr2, 'dias' => $days, 'user' => $respost[0]['User'], 'channel' => $respost[0]['Channel'], 'team' => $respost[0]['Team']);
}
public function GetAll()
{
$sql = 'SELECT birthday08.id,birthday08.uid,birthday08.name,birthday08.surname,birthday08.type,birthday08.message, professions.name as prof, users.login,users.email FROM birthday08 LEFT JOIN freelancer ON freelancer.uid=birthday08.uid LEFT JOIN professions ON professions.id=freelancer.spec LEFT JOIN users ON users.uid=birthday08.uid ORDER BY birthday08.id ASC ';
$res = pg_query(DBConnect(), $sql);
if (pg_num_rows($res)) {
return @pg_fetch_all($res);
} else {
return false;
}
}
/**
* SelectBucketDataset
* \brief Return a select list showing all the successful bucket
* runs on a particular $upload_pk.
* This list can be included in UI's to let the user select
* which data they wish to view.
* The most recent results are the default selection.
*
* \param string $upload_pk
* \param string &$ars_pk return ars_pk of the selected element, may be zero
* if there are no data. This is also used to pass in
* the selected ars_pk.
* \param string $id HTML element id
* \param string $extra Extra info for the select element, e.g. "onclick=..."
*
* \return select string, select value is $ars_pk
* If there are no rows to select, $ars_pk is returned 0
* and a simple string $NoData is returned;
* If there are only 1 row, an empty string is returned, and $ars_pk is
* set to that row.
*/
function SelectBucketDataset($upload_pk, &$ars_pk, $id = "selectbucketdataset", $extra = "")
{
global $PG_CONN;
$NoDataText = _("No data available. Use Jobs > Agents to schedule a bucket scan.");
$NoData = "<b>{$NoDataText}</b><p>";
$name = $id;
$select = "<select name='{$name}' id='{$id}' {$extra}>";
/* get the bucketpool recs */
$sql = "select ars_pk, bucketpool_pk, bucketpool_name, version from bucketpool, bucket_ars where active='Y' and bucketpool_fk=bucketpool_pk and ars_success=True and upload_fk='{$upload_pk}' order by ars_starttime desc";
$result = pg_query($PG_CONN, $sql);
DBCheckResult($result, $sql, __FILE__, __LINE__);
$NumRows = pg_num_rows($result);
if ($NumRows == 0) {
return $NoData;
}
$rows = pg_fetch_all($result);
pg_free_result($result);
if ($NumRows == 1) {
$ars_pk = $rows[0]['ars_pk'];
return "";
/* only one row */
}
/* Find the users default_bucketpool_fk */
$sql = "select default_bucketpool_fk from users where user_pk='{$_SESSION['UserId']}'";
$result = pg_query($PG_CONN, $sql);
DBCheckResult($result, $sql, __FILE__, __LINE__);
$row = pg_fetch_assoc($result);
$DefaultBucketpool_pk = $row['default_bucketpool_fk'];
pg_free_result($result);
/* Find the default selected row if ars_pk wasn't passed in */
if (empty($ars_pk)) {
foreach ($rows as $row) {
if ($row['bucketpool_pk'] == $DefaultBucketpool_pk) {
$ars_pk = $row['ars_pk'];
break;
}
}
reset($rows);
}
// $select .= "<option value=''";
foreach ($rows as $row) {
$select .= "<option value='{$row['ars_pk']}'";
if (empty($ars_pk)) {
$select .= " SELECTED ";
$ars_pk = $row["ars_pk"];
} else {
if ($ars_pk == $row['ars_pk']) {
$select .= " SELECTED ";
}
}
$select .= ">{$row['bucketpool_name']}, v {$row['version']}\n";
}
$select .= "</select>";
return $select;
}
public static function getFlags($data)
{
global $dbconn;
$sql = "SELECT ip_flagged_items.*, ip_addr_info.asnum, ip_addr_info.mm_country, ip_addr_info.mm_city, ip_addr_info.mm_postal, ip_addr_info.flagged FROM ip_flagged_items, ip_addr_info WHERE (ip_addr_info.ip_addr=ip_flagged_items.ip_addr_f) AND ip_flagged_items.ip_addr_f = '" . $data['ip_addr_f'] . "' ORDER BY ip_flagged_items.id_f DESC";
//echo $sql;
$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error());
$fArr = pg_fetch_all($result);
pg_free_result($result);
pg_close($dbconn);
return $fArr;
}
function getAttributes($class)
{
global $db_conn;
$sql = "\n SELECT\n a.name,\n d.name AS datatype,\n c.name AS classifier,\n s.name AS classifier_stereotype,\n a.multiplicity_range_lower::integer,\n a.multiplicity_range_upper,\n a.initialvalue_body\n FROM\n xplan_model.uml_attributes a LEFT JOIN\n xplan_model.datatypes d ON a.datatype = d.xmi_id LEFT JOIN\n xplan_model.uml_classes c ON a.classifier = c.xmi_id LEFT JOIN\n xplan_model.stereotypes s ON c.stereotype_id = s.xmi_id\n WHERE\n uml_class_id = " . $class['id'] . "\n ";
output($sql);
$result = pg_fetch_all(pg_query($db_conn, $sql));
if ($result == false) {
$result = array();
}
return $result;
}
function get_searched_users($keyword)
{
global $dbconn;
$query = "SELECT u.name, u.email, u.is_active FROM users u WHERE name LIKE \$1;";
$result = pg_query_params($dbconn, $query, array('%' . $keyword . '%'));
if ($result) {
return pg_fetch_all($result);
} else {
return array();
}
}
public function daoFetchAll($query, $params = 0)
{
$conexao = new Conexao();
$connection = $conexao->getConexao();
if ($params) {
$result = pg_query_params($connection, $query, $params);
} else {
$result = pg_query($connection, $query);
}
$conexao->closeConexao();
return pg_fetch_all($result);
}
function get_image_data($netid)
{
init_db();
$query = "SELECT image_id, image_name, profile FROM images WHERE netid='" . pg_escape_string($netid) . "'";
$results = pg_query($query);
if ($results) {
$images = pg_fetch_all($result);
} else {
$images = null;
}
return $images;
}
public function execute($sql, $bindParams = array(), $additionalParameters = array())
{
$sql = $this->bind($sql, $bindParams);
if ($result = pg_query($this->connection, $sql)) {
$rows = pg_fetch_all($result);
$this->affectedRows = pg_affected_rows($result);
pg_free_result($result);
return empty($rows) ? null : $rows;
} else {
$this->executeError($sql);
}
}
/**
* Возвращает текущие процента по переводам
* @return array массив с текущими процентами за перевод, в котором:
* индекс - id обмена; значение - процент
*/
function GetAll()
{
global $DB;
$res = $DB->squery("SELECT * FROM intrates");
$ret = pg_fetch_all($res);
if ($ret) {
foreach ($ret as $val) {
$out[$val['id']] = $val['val'];
}
}
return $out;
}
function getPointsFromTrajectory($tid)
{
require 'db_config.php';
$query = 'SELECT ST_X(lonlat) as lon, ST_Y(lonlat) as lat, EXTRACT(EPOCH FROM date + time) AS unixtimestamp FROM "Trajectory", "Point", "hasTP" WHERE "Trajectory".id = "hasTP".tid AND "Point".id = "hasTP".pid AND "Trajectory".id = ' . strval($tid) . ' ORDER BY pid ASC;';
$result = pg_query($db, $query);
$rset = pg_fetch_all($result);
$ml = [];
foreach ($rset as $t) {
$ml[] = new Trajectory($t['lon'], $t['lat'], $t['unixtimestamp']);
}
return $ml;
}
function get_project_detail($project_owner_email, $project_title)
{
global $dbconn;
$query = "SELECT *\n\t\t\t\t FROM projects_own_by_user p\n\t\t\t\t WHERE p.title = \$1\n\t\t\t\t AND p.owner_email = \$2;";
$result = pg_query_params($dbconn, $query, array($project_title, $project_owner_email));
if (pg_num_rows($result) > 0) {
return pg_fetch_all($result);
} else {
echo pg_last_error();
return array();
}
}
/**
* Make execution by SQL script.
*
* @param string $sql
* @param string $connection
*/
static function query($sql, $connection = 'default')
{
# executando/verificando validade da query.
if (!($query = pg_query(self::connection($connection), $sql))) {
return false;
}
# efetuando/verificando requisição no banco.
if (($fetch = pg_fetch_all($query)) !== null) {
return empty($fetch) ? true : $fetch;
}
return false;
}
