/** Ota tagit tietokannasta
* @return array
*/
function get_tags()
{
/* $result_tags array
* $end_array array
*/
$dbconn = pg_connect("host=localhost port=5432 dbname=noaa user=noaa password=123");
$result_tags = pg_query_params($dbconn, 'SELECT question_id, tag
FROM tags
WHERE question_id IN
(
SELECT question_id
FROM tags
WHERE tag = $1
)
ORDER BY question_id', array($_GET['tag']));
while ($tags_and_Qid = pg_fetch_array($result_tags)) {
// Add the Tag to an array of tags for that question
$end_array[$tags_and_Qid['question_id']]['tag'][] = $tags_and_Qid['tag'];
}
// to check if 0 messages
if (count($end_array) == 0) {
header("Location: index.php?" . "no_question_found");
} else {
return $end_array;
}
}
public function getMisc()
{
$query_result = $this->get('id', null, null);
$array = $query_result->result_array();
$infos = null;
foreach ($array as $invite) {
$id = $invite['id'];
// Calcul du nombre de vols total
$this->db->where('invite', $id);
$this->db->from('vol');
$infos[$id]['locations'] = $this->db->count_all_results();
// Calcul du nombre moyen de vols par mois
$res = pg_query_params("SELECT * FROM GSI.F_INVITE_GETMOIS(\$1)", array($id));
$nb_mois = pg_fetch_result($res, 0, 0);
if ($nb_mois == 0) {
$infos[$id]['locations_par_mois'] = $infos[$id]['locations'];
} else {
$infos[$id]['locations_par_mois'] = round($infos[$id]['locations'] / $nb_mois, 2);
}
// Calcul du chiffre d'affaire
$this->db->select_sum('prix', 'total');
$this->db->where('invite', $id);
$query = $this->db->get('vol');
$row = $query->row_array();
if ($row['total'] == null) {
$row['total'] = 0;
}
$infos[$id]['chiffre_d\'affaire (€)'] = $row['total'];
}
return $infos;
}
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 sex_ratio_ft()
{
global $DBC;
$result = pg_query_params($DBC, "\n\t\t\t\t\t\t\t\t\t\tWITH men_number_tb AS(\nSELECT count(customerid) AS men_number FROM customers WHERE sex='M' AND user_group_id=2\n)\nSELECT round(avg(men_number) / count(customerid), 2) FROM customers, men_number_tb WHERE user_group_id=2\n\n\t\t\t\t\t\t\t\t\t\t", array());
$row = pg_fetch_array($result);
echo $row[0];
}
public function exec($query, $params = array(), $html_safe = true, $debug = false)
{
// Process the request.
if ($debug) {
echo "<pre>" . $query . "</pre><br />";
}
if ($html_safe) {
foreach ($params as &$p) {
$p = htmlspecialchars($p);
}
}
$result = pg_query_params($this->db, $query, $params);
if (!$result) {
echo "<b>Query Failed:</b><pre>" . $query . "</pre><br />";
}
// Check for errors.
$pg_error = pg_last_error();
// If there are errors, put them in the error list
if (strlen($pg_error) > 0) {
$this->errors .= $pg_error;
echo $pg_error;
return false;
} else {
return new PgSqlResult($result);
}
}
function num_of_customer_ft()
{
global $DBC;
$result = pg_query_params($DBC, "SELECT count(customerid) FROM customers WHERE user_group_id = \$1", array(2));
$row = pg_fetch_array($result);
echo $row[0];
}
function check_serviceprovider_unlocked($id)
{
$query = "select record_locked from techmatcher.serviceprovider where serviceprovider_id = \$1";
$result = pg_query_params($query, array($id));
$value = pg_fetch_row($result);
return $value;
}
/** Ota salasanan hash tietokannasta
* @return string
*/
function get_original_passhash_md5()
{
$dbconn = pg_connect("host=localhost port=5432 dbname=noaa user=noaa password=123");
if (!$dbconn) {
echo "An error occurred - Hhhh\n";
exit;
}
if (!empty($_POST['login']['email'])) {
$result = pg_query_params($dbconn, 'SELECT passhash_md5
FROM users
WHERE email=$1', array($_POST['login']['email']));
while ($row = pg_fetch_array($result)) {
/*
* $passhash_md5 string
*/
$passhash_md5 = $row['passhash_md5'];
}
} else {
if (!empty($_SESSION['login']['email'])) {
$result = pg_query_params($dbconn, 'SELECT passhash_md5
FROM users
WHERE email=$1', array($_SESSION['login']['email']));
while ($row = pg_fetch_array($result)) {
$passhash_md5 = $row['passhash_md5'];
}
}
}
return $passhash_md5;
}
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;
}
function comprobar_nick_modificar(&$error, $nick, $id)
{
$res = pg_query_params("select * from usuarios where nick = \$1 and id != \$2", array($nick, $id));
if (pg_num_rows($res) > 0) {
$error[] = "nick cogido. Escoja otro.";
}
}
function getStartEndDate($cons_id)
{
$query = "select subscriptioneffectivedate,subscriptionenddate from techmatcher.currentsubscribers_vw where itconsumer_id = \$1;";
$result = pg_query_params($query, array($cons_id));
$row = pg_fetch_row($result);
return $row;
}
function get_provider_data($provider_id)
{
$qry = pg_query_params("select sp_home_page, contactemail from techmatcher.serviceprovider where serviceprovider_id=\$1", array($provider_id)) or die(pg_errormessage());
$result = pg_fetch_assoc($qry);
$_SESSION['provider']['contactemail'] = $result['contactemail'];
return $result["sp_home_page"];
}
function comprobar_tiene_cita($id_usuario)
{
$res = pg_query_params("select * from citas where id_usuario = \$1", array($id_usuario));
if (pg_num_rows($res) > 0) {
return true;
}
return false;
}
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));
}
public function query()
{
$this->sqlResult = pg_query_params($this->con, $this->statement, $this->params);
if (!$this->sqlResult) {
unset($this->sqlResult);
throw new DBException("Result in a query lead to an error.");
}
}
/**
* @param string $sql
* @param string[] $values
*
* @throws \Exception
*
* @return resource
*/
public function query($sql, array $values = [])
{
$result = pg_query_params($this->handler, $sql, $values);
if ($result === false) {
throw new \Exception(pg_last_error($this->handler));
}
return $result;
}
public function query($sql, $params)
{
if (!($pgresult = pg_query_params($this->connection, $sql, $params))) {
throw new Exception("DBへの問い合わせに失敗");
}
$result = new OnyokuDBResult($pgresult);
return $result;
}
function update($polo)
{
$conexao = new Conexao();
$dbCon = $conexao->getConexao();
$sql = "update " . self::$tabela . " set nome=\$1, cidade=\$2, uf=\$3 where id_polo=\$4";
$params = array($polo->getNome(), $polo->getCidade(), $polo->getEstado(), $polo->getId());
pg_query_params($dbCon, $sql, $params);
$conexao->closeConexao();
}
function comprobar_existe_copia($codigo, $id, &$error)
{
$res = pg_query_params("select id\n from copias\n where codigo = \$1 and\n id != \$2", array($codigo, $id));
if (pg_num_rows($res) > 0) {
$res = pg_query("rollback");
$error[] = "ya existe una película con ese código";
throw new Exception();
}
}
function getFeedbackTypes()
{
$result = pg_query_params("select * from techmatcher.feedbacktypelookup ORDER BY feedbacktype_id", array());
$feedbacktypes = array();
while ($name = pg_fetch_assoc($result)) {
$feedbacktypes[] = $name;
}
return $feedbacktypes;
}
function get_stafff_namee($staff_id)
{
$result = pg_query_params("SELECT spstaffname FROM techmatcher.staff WHERE spstaff_id= \$1", array($staff_id));
$names = array();
while ($name = pg_fetch_assoc($result)) {
$names[] = $name;
}
return $names;
}
function comprobar_existe_articulo($codigo, &$error)
{
$res = pg_query_params("select *\n from articulos\n where codigo = \$1", array($codigo));
if (pg_num_rows($res) > 0) {
$res = pg_query("rollback");
$error[] = "Ya existe un artículo con ese código";
throw new Exception();
}
}
function update($curso)
{
$conexao = new Conexao();
$dbCon = $conexao->getConexao();
$sql = "update " . self::$tabela . " set nome=\$1, tipo=\$2, polo=\$3 where id_curso=\$4";
$params = array($curso->getNome(), $curso->getTipo()['id'], $curso->getPolo()->getId(), $curso->getId());
pg_query_params($dbCon, $sql, $params);
$conexao->closeConexao();
}
function get_customername_hiredate($consumer_iddddd)
{
$result = pg_query_params("select * from techmatcher.itconsumer where itconsumer_id=\$1", array($consumer_iddddd));
$customer_names = array();
while ($result1 = pg_fetch_assoc($result)) {
$customer_names[] = $result1;
}
return $customer_names;
}
function comprobar_existe_socio($numero, $id, &$error)
{
$res = pg_query_params("select id\n from socios\n where numero = \$1 and\n id != \$2", array($numero, $id));
if (pg_num_rows($res) > 0) {
$res = pg_query("rollback");
$error[] = "ya existe un socio con ese número";
throw new Exception();
}
}
function comprobar_nick($nick, &$error)
{
$res = pg_query_params("select * from usuarios where nick = \$1", array($nick));
if ($nick == "") {
$error[] = "el nombre es obligatorio.";
} elseif (pg_num_rows($res) > 0) {
$error[] = "el nombre de usuario ya esta cogido. Vuelva a intentarlo.";
}
}
/**
* Выполняет параметризованный 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;
}
function db_backend_delete($db, $id)
{
$sql = '
DELETE FROM
pastes
WHERE
id = $1
';
$res = pg_query_params($db, $sql, array($id)) or die("Failed to execute query '{$sql}'");
}
public function daoExecuteQuery($query, $params = 0)
{
$conexao = new Conexao();
$connection = $conexao->getConexao();
if ($params) {
pg_query_params($connection, $query, $params);
} else {
pg_query($connection, $query);
}
}
function imageResize()
{
global $thumbsizes, $settings, $db_lnk, $is_aspect;
$size = $_GET['size'];
$model = $_GET['model'];
$filename = $_GET['filename'];
$val = $thumbsizes[$model][$size];
list($width, $height) = explode('x', $val);
list($id, $hash, $ext) = explode('.', $filename);
if ($hash == md5(SecuritySalt . $model . $id . $ext . $size . SITE_NAME)) {
if ($model == 'User') {
$s_result = pg_query_params($db_lnk, 'SELECT profile_picture_path FROM users WHERE id = $1', array($id));
$row = pg_fetch_assoc($s_result);
$fullPath = $row['profile_picture_path'];
} else {
if ($model == 'Organization') {
$s_result = pg_query_params($db_lnk, 'SELECT logo_url FROM organizations WHERE id = $1', array($id));
$row = pg_fetch_assoc($s_result);
$fullPath = $row['logo_url'];
} else {
if ($model == 'Board') {
$s_result = pg_query_params($db_lnk, 'SELECT background_picture_path FROM boards WHERE id = $1', array($id));
$row = pg_fetch_assoc($s_result);
$fullPath = $row['background_picture_path'];
} else {
if ($model == 'CardAttachment') {
$s_result = pg_query_params($db_lnk, 'SELECT path FROM card_attachments WHERE id = $1', array($id));
$row = pg_fetch_assoc($s_result);
$fullPath = $row['path'];
}
}
}
}
$fullPath = dirname(dirname(dirname(dirname(__FILE__)))) . '/' . $fullPath;
$query_string = $_GET;
$query_string['id'] = $id;
$query_string['ext'] = $ext;
$query_string['hash'] = $hash;
$is_aspect = false;
if (!empty($aspect[$model][$size])) {
$is_aspect = true;
}
$mediadir = dirname(dirname(dirname(dirname(__FILE__)))) . '/client/img/' . $query_string['size'] . '/' . $query_string['model'] . '/';
if (!file_exists($mediadir)) {
mkdir($mediadir, 0777, true);
}
$filename = $query_string['id'] . '.' . $query_string['hash'] . '.' . $query_string['ext'];
$writeTo = $mediadir . $filename;
$img_string = resizeFile($fullPath, $width, $height, $writeTo, $is_aspect, false, $query_string);
header('Location:' . $_SERVER['REQUEST_URI'] . '?chrome-3xx-fix');
} else {
// Invalid request
return false;
}
}
