PHP pg_fetch_result函数代码示例

 /**
  * Returns a specific field of the current row.
  *
  * @param string $field
  * @return mixed
  */
 public function fetchResult($field)
 {
     if ($this->currentRowNumber >= $this->numRows()) {
         return false;
     }
     return pg_fetch_result($this->result, $this->currentRowNumber, $field);
 }

function getWeekNumber($mday, $month, $year)
{
    $sql = "SELECT EXTRACT(week FROM DATE '{$year}-{$month}-{$mday}')";
    $rslt = db_exec($sql);
    return pg_fetch_result($rslt, 0, 0);
    //return round(date("z", mktime(0,0,0,$month,$mday,$year) ) / 7);
}

function display()
{
    extract($_REQUEST);
    $sql = "SELECT * FROM cubit.documents WHERE id='{$id}'";
    $doc_rslt = db_exec($sql) or errDie("Unable to retrieve documents.");
    $doc_data = pg_fetch_array($doc_rslt);
    extract($doc_data);
    if (!empty($doc_type)) {
        $sql = "SELECT type_name FROM cubit.document_types WHERE id='{$doc_type}'";
        $type_rslt = db_exec($sql) or errDie("Unable to retrieve document type.");
        $type_name = pg_fetch_result($type_rslt, 0);
    } else {
        $type_name = "";
    }
    if (!empty($team_id)) {
        $sql = "SELECT team_name FROM cubit.teams WHERE id='{$team_id}'";
        $team_rslt = db_exec($sql) or errDie("Unable to retrieve team.");
        $team_name = pg_fetch_result($team_rslt, 0);
    } else {
        $team_name = "";
    }
    $OUTPUT = "<table " . TMPL_tblDflts . ">\n\t<tr><td valign='top'>\n\t<table " . TMPL_tblDflts . ">\n\t\t<tr>\n\t\t\t<th colspan='2'>Details</th>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Title</td>\n\t\t\t<td>{$title}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Project</td>\n\t\t\t<td>{$project}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Area</td>\n\t\t\t<td>{$area}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Discipline</td>\n\t\t\t<td>{$discipline}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Document Type</td>\n\t\t\t<td>{$type_name}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Revision</td>\n\t\t\t<td>{$revision}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Drawing Number</td>\n\t\t\t<td>{$drawing_num}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Sheet Number</td>\n\t\t\t<td>{$sheet_num}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>File Location</td>\n\t\t\t<td>{$location}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Contract</td>\n\t\t\t<td>{$contract}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Contractor</td>\n\t\t\t<td>{$contractor}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Code</td>\n\t\t\t<td>{$code}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Issue For</td>\n\t\t\t<td>{$issue_for}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Comments</td>\n\t\t\t<td>{$comments}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>QS</td>\n\t\t\t<td>{$qs}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Team Permissions</td>\n\t\t\t<td>{$team_name}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Status</td>\n\t\t\t<td>{$status}</td>\n\t\t</tr>\n\t</table>\n\t</td><td valign='top'>";
    $sql = "SELECT * FROM document_files WHERE doc_id='{$id}'";
    $df_rslt = db_exec($sql) or errDie("Unable to retrieve files.");
    $file_out = "";
    while ($df_data = pg_fetch_array($df_rslt)) {
        $file_out .= "<tr class='" . bg_class() . "'>\n\t\t\t<td><a href='getfile.php?key=doc&id={$df_data['id']}'>{$df_data['filename']}</a></td>\n\t\t\t<td>" . getFilesize($df_data["size"]) . "</td>\n\t\t</tr>";
    }
    if (empty($file_out)) {
        $file_out = "<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='2'>No files found.</td>\n\t\t</tr>";
    }
    $OUTPUT .= "<table " . TMPL_tblDflts . ">\n\t\t<tr>\n\t\t\t<th colspan='2'>Files</th>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th>File</th>\n\t\t\t<th>Size</th>\n\t\t</tr>\n\t\t{$file_out}\n\t</table>\n\t</td></tr>\n\t</table>";
    return $OUTPUT;
}

function display()
{
    $sql = "SELECT * FROM cubit.stock ORDER BY stkcod ASC";
    $stock_rslt = db_exec($sql) or errDie("Unable to retrieve stock.");
    $stock_out = "";
    while ($stock_data = pg_fetch_array($stock_rslt)) {
        // Retrieve units on order
        $sql = "SELECT sum(qty) FROM cubit.sorders_items WHERE stkid='{$stock_data['stkid']}'";
        $order_rslt = db_exec($sql) or errDie("Unable to retrieve orders.");
        $order_qty = pg_fetch_result($order_rslt, 0);
        if (empty($stock_data["minlvl"])) {
            continue;
        }
        if (empty($order_qty)) {
            $order_qty = 0;
        }
        $units = $stock_data["units"] - $order_qty;
        $required = abs($units - $stock_data["minlvl"]);
        // We're not required to purchase this item
        if ($units > $stock_data["minlvl"]) {
            continue;
        }
        $stock_out .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>{$stock_data['stkcod']}</td>\n\t\t\t\t<td>{$stock_data['stkdes']}</td>\n\t\t\t\t<td align='center'>" . sprint3($stock_data['units']) . "</td>\n\t\t\t\t<td align='center'>" . sprint3($order_qty) . "</td>\n\t\t\t\t<td align='center'>" . sprint3($stock_data['minlvl']) . "</td>\n\t\t\t\t<td align='center'><b>" . sprint3($required) . "</b></td>\n\t\t\t\t<td><a href='purchase-new.php'>Purchase</a></td>\n\t\t\t</tr>";
    }
    if (empty($stock_out)) {
        $stock_out = "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='7'>No items required for purchase.</td>\n\t\t\t</tr>";
    }
    $OUTPUT = "\n\t\t<center>\n\t\t<h3>Required Purchases</h3>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Stock Code</th>\n\t\t\t\t<th>Stock Description</th>\n\t\t\t\t<th>Current Units</th>\n\t\t\t\t<th>Units on Order</th>\n\t\t\t\t<th>Minimum Level</th>\n\t\t\t\t<th>Minimum Required</th>\n\t\t\t\t<th>Options</th>\n\t\t\t</tr>\n\t\t\t{$stock_out}\n\t\t</table>\n\t\t</center>";
    return $OUTPUT;
}

 function get_db_variables()
 {
     $sql_str = "SELECT " . "var_name, " . "var_value " . "FROM " . "portal_config_variables ";
     $this->dbconn->connect();
     $result = pg_query($this->dbconn->conn, $sql_str);
     if (!$result) {
         echo "An error occurred while executing the query - " . $sql_str . " - " . pg_last_error($this->dbconn->conn);
         $this->dbconn->disconnect();
         return false;
     }
     $n_rows = pg_num_rows($result);
     $vars = array();
     for ($i = 0; $i < $n_rows; $i++) {
         $vars[pg_fetch_result($result, $i, 'var_name')] = pg_fetch_result($result, $i, 'var_value');
     }
     $this->dbconn->disconnect();
     // set member variables from name/value pairs
     $this->upload_path = $vars["upload_path"];
     $this->mapfile_path = $vars["mapfile_path"];
     $this->layer_config_path = $vars["layer_config_path"];
     $this->mapservice_config_path = $vars["mapservice_config_path"];
     $this->mapservice_name = $vars["mapservice_name"];
     $this->map_agent = $vars["map_agent"];
     $this->map_agent_launch_url = $vars["map_agent_launch_url"];
     $this->output_dir = $vars["output_dir"];
     $this->ogr2ogr_path = $vars["ogr2ogr_path"];
     $this->confirmation_email_template = $vars["confirmation_email_template"];
     return true;
 }

 function stock_is_blocked($stkid)
 {
     $sql = "SELECT blocked FROM cubit.stock WHERE stkid='{$stkid}'";
     $stock_rslt = db_exec($sql) or errDie("Unable to check if stock is blocked.");
     $blocked = pg_fetch_result($stock_rslt, 0);
     return $blocked;
 }

function budgetTotalFromYear($accid, $budfor)
{
    db_conn("cubit");
    $sql = "SELECT SUM(bi.amt) FROM cubit.buditems bi, cubit.budgets bd\n\t\t\tWHERE bi.budid=bd.budid AND bi.id='{$accid}' AND bd.budfor='{$budfor}'\n\t\t\t\tAND bd.prdtyp='yr' AND bi.prd='" . BUDGET_YEARS_INDEX . "'";
    $rslt = db_exec($sql) or errDie("Error reading monthly budget total.");
    return pg_fetch_result($rslt, 0, 0);
}

function getNumOfComm($postId)
{
    $sql = pg_prepare(getDatabaseConnection(), "getNumOfComm", "SELECT count(*) FROM comments_posts WHERE post_id=\$1");
    $result = pg_execute(getDatabaseConnection(), "getNumOfComm", [$postId]) or die('Query failed: ' . pg_last_error());
    $numComm = pg_fetch_result($result, 0, 0);
    return $numComm;
}

    private function getObjIds()
    {
        $this->conn = $this->openConnectionTrans();
        if ($this->conn) {
            $sql = <<<SQL
\t\t\t\tSELECT 
\t\t\t\t\tSTRING_AGG((OBJ_ID)::TEXT, ',') AS OBJ_IDS
\t\t\t\tFROM GARAGE.CONTAINERS AS A 
\t\t\t\tINNER JOIN HAULAGE_LIST H on (A.haulage_list_id=H.id)
\t\t\t\tINNER JOIN HAULAGE_NOTICE_CONTAINERS HNC ON (HNC.ID = A.HAULAGE_NOTICE_CONTAINERS_ID)
\t\t\t\tLEFT JOIN ATP.HAULAGE_LIST HL ON (HL.CONTAINERS_NEW_ID = HNC.CONTAINERS_NEW_ID)
\t\t\t\tLEFT JOIN DRIVERS_CARS DC ON (DC.ID = HL.DRIVERS_CARS_ID)
\t\t\t\tWHERE A.DRIVERS_ID IS NOT NULL 
\t\t\t\tAND (A.LOCATION_ID IS NOT NULL AND NOT (A.LOCATION_ID = 400 AND A.CONTAINERS_STATUS_ID = 13 ))
\t\t\t\tAND (A.containers_status_id is distinct from 13) AND OUR_CARS IS TRUE AND H.ROUTE IS NOT NULL
\t\t\t\tAND CHANGED_ROUTE IS NOT TRUE --не обновлять статусы для машин которые сменили маршрут
SQL;
            if ($cur = pg_query($this->conn, $sql)) {
                return pg_fetch_result($cur, 0, 'obj_ids');
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

function orderDate($stkid, $from_date, $to_date)
{
    $sql = "SELECT minlvl FROM cubit.stock WHERE stkid='{$stkid}'";
    $min_rslt = db_exec($sql) or errDie("Unable to retrieve stock minimum stock level.");
    $min = pg_fetch_result($min_rslt, 0);
    if (empty($min)) {
        $min = 0;
    }
    $from_time = strtotime($from_date);
    $to_time = strtotime(date("Y-m-t", $from_time));
    $sales = averageSalesQty($stkid, date("Y-m-d", $from_time), date("Y-m-d", $to_time), "DAILY");
    if ($sales <= $min) {
        return date("Y-m-d");
    } else {
        $tmp_sales = $sales;
        $ret = 0;
        while ($tmp_sales > $min) {
            $to_time = $from_time * (1 * (60 * 60 * 24));
            $tmp_sales = averageSalesQty($stkid, date("Y-m-d", $from_time), date("Y-m-d", $to_time), "MONTHLY");
            $ret = 1;
        }
        if ($ret) {
            return date("Y-m-d", $to_time);
        }
    }
    return;
}

function _send_cake_info($res, $from, $to_session)
{
    global $connection;
    global $logger;
    $result = pg_query($connection, 'SELECT *, users.nick as author_nick
                        FROM pies
                             JOIN users ON users.id = pies.author
                        WHERE pies.id = ' . $from->pieid);
    if (!$result) {
        throw new Exception("Failed to load pie information");
    }
    $row = pg_fetch_assoc($result);
    $settings = json_decode($row['settings'], true);
    $info = array();
    $info['name'] = $row['name'];
    $info['description'] = $row['description'];
    $info['created_at'] = $row['start'];
    $info['author'] = $row['author_nick'];
    $info['visible'] = $row['visible'] == 't';
    $info['links'] = $settings['links'] ?: array();
    $info['statuses'] = $settings['statuses'] ?: array('', '', '', '', '', '', '', '', '', 'all done');
    $result = pg_query($connection, 'SELECT count(pieces.id) as pieces_count FROM pieces WHERE pie = ' . $from->pieid);
    $info['pieces_count'] = pg_fetch_result($result, 0, "pieces_count");
    $msg = array('cake_info', $info);
    if ($to_session) {
        $res->to_session($from, $msg);
    } else {
        $res->to_pie($from, $msg);
    }
}

function amtOnOrder($stkid)
{
    $sql = "\n\tSELECT sum(qty) FROM cubit.purchases\n\t\tLEFT JOIN cubit.pur_items ON purchases.purid=pur_items.purid\n\tWHERE stkid='{$stkid}' AND received='n'";
    $pur_rslt = db_exec($sql) or errDie("Unable to retrieve purchases.");
    $on_order = pg_fetch_result($pur_rslt, 0);
    return $on_order;
}

 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 setActiveGroup($verbose)
{
    global $dbManager;
    $stmt = __METHOD__;
    $sql = "SELECT user_pk,group_pk FROM users LEFT JOIN groups ON group_name=user_name WHERE group_fk IS NULL";
    $dbManager->prepare($stmt, $sql);
    $res = $dbManager->execute($stmt);
    if (pg_num_rows($res) == 0) {
        pg_free_result($res);
        return 0;
    }
    $userGroupMap = pg_fetch_all($res);
    pg_free_result($res);
    $selectStmt = __METHOD__ . '.select';
    $sql = "SELECT user_fk,min(group_fk) group_fk FROM group_user_member WHERE user_fk=\$1";
    $updateStmt = __METHOD__ . '.update';
    $dbManager->prepare($updateStmt, "UPDATE users SET group_fk=\$2 WHERE user_pk=\$1");
    foreach ($userGroupMap as $row) {
        if (!empty($row['group_pk'])) {
            pg_free_result($dbManager->execute($updateStmt, $row));
            continue;
        }
        $rowrow = $dbManager->getSingleRow($sql, array($row['user_pk']), $selectStmt);
        pg_fetch_result($dbManager->execute($updateStmt, $rowrow));
    }
}

function view()
{
    global $_GET;
    extract($_GET);
    if (!isset($target)) {
        $OUTPUT = "<li class=err>Invalid use of module</li>";
        require "template.php";
    }
    $vars = "";
    foreach ($_GET as $key => $value) {
        if ($key != "target") {
            $vars .= "&{$key}={$value}";
        }
    }
    // compute the auth url
    db_conn("cubit");
    $sql = "SELECT * FROM cubitnet_sitesettings WHERE div='" . USER_DIV . "'";
    $rslt = db_exec($sql) or errDie("Error reading username and password for Cubit.co.za");
    if (pg_num_rows($rslt) < 1) {
        $OUTPUT = "<li class=err>Cubit.co.za Settings not set up yet.\r\n\t\t\tPlease <a href='cubitnet_settings.php'>enter</a> the settings first</li>";
        require "template.php";
    }
    extract(pg_fetch_array($rslt));
    db_conn("cubit");
    $sql = "SELECT setting_value FROM cubitnet_settings WHERE setting_name='cubitnet_hash'";
    $rslt = db_exec($sql) or errDie("Error reading hash value for Cubit.co.za.");
    if (pg_num_rows($rslt) < 1) {
        $OUTPUT = "<li class=err>Cubit.co.za Settings not set up yet.\r\n\t\t\tPlease <a href='cubitnet_settings.php'>enter</a> the settings first</li>";
        require "template.php";
    }
    $cubitnet_hash = pg_fetch_result($rslt, 0, 0);
    print "\r\n\t<frameset rows=0,* border=0>\r\n\t\t<frame name=https_auth src='" . IDENTIFY_URL . "?hash={$cubitnet_hash}&username={$cn_username}&password={$cn_password}'>\r\n\t\t<frame name=data_frame src='{$target}?{$vars}'>\r\n\t</frameset>";
}