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