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