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; }