PHP pg_result_seek函数代码示例

 function fetch_referendums($status, $start, $pagelen, $search, $order)
 {
     global $dbconn, $expanded_referendums;
     global $sql_names;
     global $sql_select, $sql_from, $sql_where;
     global $sql_fud_names, $sql_fud_select, $sql_fud_from, $sql_fud_where;
     global $sql_type_select, $sql_type_from, $sql_type_where;
     $sql_fetch = " from" . "  (select " . $sql_select . $sql_fud_select . $sql_type_select . "    from " . $sql_from . $sql_fud_from . $sql_type_from . "    where " . $sql_where . $sql_fud_where . $sql_type_where . "    order by i.referendum) as v" . " where " . $search;
     $sql = "select count(*) from (select distinct on (referendum) referendum " . $sql_fetch . " group by referendum) as r";
     $row = pg_fetch_row(pg_query($dbconn, $sql)) or die('Could query: ' . pg_last_error());
     $len = (int) $row[0];
     $sql = "select * " . $sql_fetch . " order by " . $order;
     " limit " . ($start + $pagelen);
     $rows = pg_query($dbconn, $sql) or die('Could query: ' . pg_last_error());
     pg_result_seek($rows, $start);
     $v = array();
     while ($row = pg_fetch_row($rows)) {
         $row = array_combine($sql_names, $row);
         $row['expanded'] = in_array($row['referendum'], $expanded_referendums);
         if (!isset($v[count($v) - 1]) || $v[count($v) - 1]['referendum'] != $row['referendum']) {
             $v[] = $row;
         } else {
             if (!isset($v[count($v) - 1]['subrows'])) {
                 $v[count($v) - 1]['subrows'] = array($v[count($v) - 1]);
             }
             $v[count($v) - 1]['subrows'][] = $row;
         }
     }
     return array('len' => $len, 'referendums' => $v);
 }

 public function rowSeek($rowNum)
 {
     $re = @pg_result_seek($this->query_result, $rowNum);
     if (!$re) {
         throw new Exception($this->errorInfo());
     }
     return $re;
 }

 public function seek($offset)
 {
     if ($this->offsetExists($offset) && pg_result_seek($this->_result, $offset)) {
         $this->_current_row = $this->_internal_row = $offset;
         return true;
     } else {
         return false;
     }
 }

 /**
  * Go to a row int the RecordSet.
  *
  * @param int $row Row to go to.
  * @return bool Returns TRUE on success, FALSE if failed.
  */
 function SetRow($row = 0)
 {
     $this->row = $row;
     if (pg_result_seek($this->result, $row) === FALSE) {
         return FALSE;
     } else {
         return TRUE;
     }
 }

function data_seek($result, $row = 0, $dbtype = 'mysql')
{
    if ($dbtype == 'mysql') {
        mysqli_data_seek($result, $row);
    } else {
        if ($dbtype == 'postgres') {
            pg_result_seek($result, $row);
        }
    }
}

 public function seek($offset)
 {
     if ($this->offsetExists($offset) and pg_result_seek($this->_result, $offset)) {
         // Set the current row to the offset
         $this->_current_row = $this->_internal_row = $offset;
         return TRUE;
     } else {
         return FALSE;
     }
 }

 /**
  * Constructor method for the adapter.  This constructor implements the setting of the
  * 3 required properties for the object.
  * 
  * @param resource $d The datasource resource
  */
 function pgsqlAdapter($d)
 {
     parent::RecordSetAdapter($d);
     $fieldcount = pg_num_fields($d);
     $ob = "";
     $be = $this->isBigEndian;
     $fc = pack('N', $fieldcount);
     if (pg_num_rows($d) > 0) {
         pg_result_seek($d, 0);
         while ($line = pg_fetch_row($d)) {
             // write all of the array elements
             $ob .= "\n" . $fc;
             foreach ($line as $value) {
                 // write all of the array elements
                 if (is_string($value)) {
                     // type as string
                     $os = $this->_directCharsetHandler->transliterate($value);
                     //string flag, string length, and string
                     $len = strlen($os);
                     if ($len < 65536) {
                         $ob .= "" . pack('n', $len) . $os;
                     } else {
                         $ob .= "\f" . pack('N', $len) . $os;
                     }
                 } elseif (is_float($value) || is_int($value)) {
                     // type as double
                     $b = pack('d', $value);
                     // pack the bytes
                     if ($be) {
                         // if we are a big-endian processor
                         $r = strrev($b);
                     } else {
                         // add the bytes to the output
                         $r = $b;
                     }
                     $ob .= "" . $r;
                 } elseif (is_bool($value)) {
                     //type as bool
                     $ob .= "";
                     $ob .= pack('c', $value);
                 } elseif (is_null($value)) {
                     // null
                     $ob .= "";
                 }
             }
         }
     }
     $this->serializedData = $ob;
     $this->numRows = pg_num_rows($d);
     for ($i = 0; $i < $fieldcount; $i++) {
         $this->columnNames[$i] = $this->_charsetHandler->transliterate(pg_field_name($d, $i));
     }
 }

function display()
{
    extract($_REQUEST);
    $fields = array();
    $fields["from_year"] = date("Y");
    $fields["from_month"] = date("m");
    $fields["from_day"] = "01";
    $fields["to_year"] = date("Y");
    $fields["to_month"] = date("m");
    $fields["to_day"] = date("d");
    $fields["inc_perc"] = 0;
    $fields["dec_perc"] = 0;
    extract($fields, EXTR_SKIP);
    $from_date = "{$from_year}-{$from_month}-{$from_day}";
    $to_date = "{$to_year}-{$to_month}-{$to_day}";
    $OUTPUT = "<center>\n\t<h3>Point in Time Sales Forecast</h3>\n\t<form method='post' action='" . SELF . "'>\n\t<table " . TMPL_tblDflts . ">\n\t\t<tr>\n\t\t\t<th colspan='3'>Date Range</th>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>" . mkDateSelect("from", $from_year, $from_month, $from_day) . "</td>\n\t\t\t<td>&nbsp; <b>To</b> &nbsp;</td>\n\t\t\t<td>" . mkDateSelect("to", $to_year, $to_month, $to_day) . "</td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th>Increase</th>\n\t\t\t<th>&nbsp;</th>\n\t\t\t<th>Decrease</th>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td align='center'>\n\t\t\t\t<span style='font-weight: bold'>+</span>\n\t\t\t\t<input type='text' name='inc_perc' value='{$inc_perc}' size='4'\n\t\t\t\tstyle='text-align: center' />\n\t\t\t\t<span style='font-weight: bold'>%</span>\n\t\t\t</td>\n\t\t\t<td>&nbsp;</td>\n\t\t\t<td align='center'>\n\t\t\t\t<span style='font-weight: bold'>-</span>\n\t\t\t\t<input type='text' name='dec_perc' value='{$dec_perc}' size='4'\n\t\t\t\tstyle='text-align: center' />\n\t\t\t\t<span style='font-weight: bold'>%</span>\n\t\t\t</td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<td colspan='3' align='center'>\n\t\t\t\t<input type='submit' value='Apply' />\n\t\t\t</td>\n\t\t</tr>\n\t</table>\n\t</form>";
    $sql = "SELECT whid, whname FROM exten.warehouses ORDER BY whname ASC";
    $wh_rslt = db_exec($sql) or errDie("Unable to retrieve stores.");
    $stores_th_lv1 = $stores_th_lv2 = "";
    while ($wh_data = pg_fetch_array($wh_rslt)) {
        $stores_th_lv1 .= "<th colspan='2'>{$wh_data['whname']}</th>";
        $stores_th_lv2 .= "<th>Actual</th><th>Average<br>per Week</th>";
    }
    // Retrieve unique stock
    $sql = "SELECT DISTINCT(stkcod) FROM cubit.stock ORDER BY stkcod ASC";
    $stkcod_rslt = db_exec($sql) or errDie("Unable to retrieve stock codes.");
    $stock_out = "";
    while ($stkcod = pg_fetch_array($stkcod_rslt)) {
        $stkcod = $stkcod["stkcod"];
        $sql = "SELECT stkdes FROM cubit.stock\n\t\t\t\tWHERE stkcod='{$stkcod}'";
        $stkdes_rslt = db_exec($sql) or errDie("Unable to retrieve stock.");
        $stkdes = pg_fetch_result($stkdes_rslt, 0);
        $stock_out .= "<tr class='" . bg_class() . "'>\n\t\t\t<td>{$stkcod}</td>\n\t\t\t<td>{$stkdes}</td>\n\t\t\t<td>\n\t\t\t\t" . totalActual($stkcod, $from_date, $to_date) . "\n\t\t\t</td>\n\t\t\t<td>\n\t\t\t\t<!--" . totalProjected($stkcod, $inc_perc, $dec_perc, $from_date, $to_date) . "-->\n\t\t\t\t" . totalWeekAverages($stkcod, $from_date, $to_date) . "\n\t\t\t</td>";
        pg_result_seek($wh_rslt, 0);
        while ($wh_data = pg_fetch_array($wh_rslt)) {
            $sql = "SELECT stkid, units FROM cubit.stock\n\t\t\t\t\tWHERE stkcod='{$stkcod}' AND whid='{$wh_data['whid']}'";
            $stock_rslt = db_exec($sql) or errDie("Unable to retrieve stock.");
            $stock_data = pg_fetch_array($stock_rslt);
            $stkid = $stock_data["stkid"];
            // Don't go beyond this point unless the stock exists in this store
            if (empty($stkid)) {
                $stock_out .= "<td>0.00</td><td>0.00</td>";
                continue;
            }
            // Total sales for the selected period
            $actual_sales = actualSales($stkid, $from_date, $to_date);
            $projected_sales = projectedSales($stkid, $inc_perc, $dec_perc, $from_date, $to_date);
            $stock_out .= "\n\t\t\t\t<input type='hidden' name='stkid[]' value='{$stkid}' />\n\t\t\t\t<td>\n\t\t\t\t\t<input type='hidden' name='actual_sales[{$stkid}]'\n\t\t\t\t\tvalue='{$actual_sales}' />\n\t\t\t\t\t{$actual_sales}\n\t\t\t\t</td>\n\t\t\t\t<td>\n\t\t\t\t\t<input type='hidden' name='projected_sales[{$stkid}]'\n\t\t\t\t\tvalue='{$projected_sales}' />\n\t\t\t\t\t<!--{$projected_sales}-->\n\t\t\t\t\t" . weekAverages($stkid, $from_date, $to_date) . "\n\t\t\t\t</td>";
        }
    }
    $OUTPUT .= "\n\t<form method='post' action='" . SELF . "'>\n\t<table " . TMPL_tblDflts . ">\n\t\t<tr>\n\t\t\t<th rowspan='2'>Stock Code</th>\n\t\t\t<th rowspan='2'>Stock Item</th>\n\t\t\t<th colspan='2'>Total</th>\n\t\t\t{$stores_th_lv1}\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th>Actual</th>\n\t\t\t<th>Average<br>per Week</th>\n\t\t\t{$stores_th_lv2}\n\t\t</tr>\n\t\t{$stock_out}\n\t\t<tr>\n\t</table>\n\t</form>";
    return $OUTPUT;
}

 /**
  * Constructor method for the adapter.  This constructor implements the setting of the
  * 3 required properties for the object.
  * 
  * @param resource $d The datasource resource
  */
 function pgsqlAdapter($d)
 {
     parent::RecordSetAdapter($d);
     $fieldcount = pg_num_fields($d);
     for ($i = 0; $i < $fieldcount; $i++) {
         $this->columns[] = pg_field_name($d, $i);
     }
     if (pg_num_rows($d) > 0) {
         pg_result_seek($d, 0);
         while ($line = pg_fetch_row($d)) {
             $this->rows[] = $line;
         }
     }
 }

 function rewind()
 {
     if (isset($this->queryId) && is_resource($this->queryId) && pg_num_rows($this->queryId)) {
         if (pg_result_seek($this->queryId, 0) === false) {
             $this->connection->_raiseError("");
         }
     } elseif (!$this->queryId) {
         $this->stmt->free();
         if (is_array($this->sort_params)) {
             $this->stmt->addOrder($this->sort_params);
         }
         if ($this->limit) {
             $this->stmt->addLimit($this->offset, $this->limit);
         }
         $this->queryId = $this->stmt->execute();
     }
     $this->key = 0;
     $this->next();
 }

function getData($dbh, $sql, $filename)
{
    //$sql = "SELECT ST_X(geom) AS X, ST_Y(geom) AS Y, gid, standort, einheit, messw_bl, lon, lat, petrograph FROM public.bodenluft_4326";
    $result = pg_query($dbh, $sql);
    if (!$result) {
        die("Error in SQL query: " . pg_last_error());
    }
    //$filename = 'Bodenluft_4326_statistics_php.csv';
    $fp = fopen($filename, "w+");
    // fetch a row and write the column names (!) out to the file -> first line of CSV
    $row = pg_fetch_assoc($result);
    $line = "";
    $comma = "";
    foreach ($row as $name => $value) {
        $line .= strtoupper($comma . str_replace('"', '""', $name));
        //letters to uppercase and ',' and ' ' are "deleted"
        $comma = ",";
    }
    $line .= "\n";
    fputs($fp, $line);
    // remove the result pointer back to the start
    pg_result_seek($result, 0);
    // and loop through the actual data
    while ($row = pg_fetch_assoc($result)) {
        $line = "";
        $comma = "";
        $replace = array(",", " ");
        foreach ($row as $value) {
            $line .= $comma . str_replace($replace, '', $value);
            $comma = ",";
        }
        $line .= "\n";
        fputs($fp, $line);
    }
    fclose($fp);
    // free memory
    pg_free_result($result);
    $output = "<script>console.log( 'Writing file finished' );</script>";
    echo $output;
}

 function Move($zp_row)
 {
     global $db;
     if (@pg_result_seek($this->resource, $zp_row)) {
         return;
     } else {
         $db->set_error(pg_last_error());
     }
 }

function compare()
{
    extract($_REQUEST);
    if ($prd == "week") {
        $dates = lastWeekDates();
    } else {
        $dates = lastMonthDates();
    }
    $start_date = $dates["start_date"];
    $end_date = $dates["end_date"];
    // Store headings
    $sql = "SELECT whid, whname FROM exten.warehouses ORDER BY whname ASC";
    $wh_rslt = db_exec($sql) or errDie("Unable to retrieve stores.");
    $stores_th_lv1 = $stores_th_lv2 = "";
    while ($wh_data = pg_fetch_array($wh_rslt)) {
        $stores_th_lv1 .= "<th colspan='2'>{$wh_data['whname']}</th>";
        $stores_th_lv2 .= "<th>Actual</th><th>Projected</th>";
    }
    // Retrieve unique stock
    $sql = "SELECT DISTINCT(stkcod) FROM cubit.stock ORDER BY stkcod ASC";
    $stkcod_rslt = db_exec($sql) or errDie("Unable to retrieve stock codes.");
    $stock_out = "";
    while ($stkcod = pg_fetch_array($stkcod_rslt)) {
        $stkcod = $stkcod["stkcod"];
        $sql = "SELECT stkdes FROM cubit.stock\n\t\t\t\tWHERE stkcod='{$stkcod}'";
        $stkdes_rslt = db_exec($sql) or errDie("Unable to retrieve stock.");
        $stkdes = pg_fetch_result($stkdes_rslt, 0);
        $stock_out .= "<tr class='" . bg_class() . "'>\n\t\t\t<td>{$stkcod}</td>\n\t\t\t<td>{$stkdes}</td>\n\t\t\t<td>\n\t\t\t\t" . totalActual($stkcod, $start_date, $end_date, $forecast_id) . "\n\t\t\t</td>\n\t\t\t<td>\n\t\t\t\t" . totalProjected($stkcod, $inc_perc, $dec_perc, $start_date, $end_date, $forecast_id) . "\n\t\t\t</td>";
        pg_result_seek($wh_rslt, 0);
        while ($wh_data = pg_fetch_array($wh_rslt)) {
            $sql = "SELECT stkid, units FROM cubit.stock\n\t\t\t\t\tWHERE stkcod='{$stkcod}' AND whid='{$wh_data['whid']}'";
            $stock_rslt = db_exec($sql) or errDie("Unable to retrieve stock.");
            $stock_data = pg_fetch_array($stock_rslt);
            $stkid = $stock_data["stkid"];
            // Don't go beyond this point unless the stock exists in this store
            if (empty($stkid)) {
                $stock_out .= "<td>0.00</td><td>0.00</td>";
                continue;
            }
            $sql = "SELECT actual, projected FROM cubit.forecast_items\n\t\t\t\t\tWHERE stkid='{$stkid}' AND forecast_id='{$forecast_id}'";
            $fci_rslt = db_exec($sql) or errDie("Unable to retrieve items.");
            $fci_data = pg_fetch_array($fci_rslt);
            // Total sales for the selected period
            $current_actual = actualSales($stkid, $start_date, $end_date, $forecast_id);
            $current_projected = projectedSales($stkid, $inc_perc, $dec_perc, $start_date, $end_date, $forecast_id);
            if (empty($current_actual)) {
                $current_actual = "0.00";
            }
            if (empty($current_projected)) {
                $current_projected = "0.00";
            }
            if (empty($fci_data["actual"])) {
                $fci_data["actual"] = "0.00";
            }
            if (empty($fci_data["projected"])) {
                $fci_data["projected"] = "0.00";
            }
            $actual_sales = sprint($current_actual - $fci_data["actual"]);
            $projected_sales = sprint($current_projected - $fci_data["projected"]);
            if ($actual_sales > 0) {
                $actual_color = "green";
            } elseif ($actual_sales < 0) {
                $actual_color = "red";
            } else {
                $actual_color = "black";
            }
            if ($projected_sales > 0) {
                $projected_color = "green";
            } elseif ($projected_sales < 0) {
                $projected_color = "red";
            } else {
                $projected_color = "black";
            }
            $stock_out .= "\n\t\t\t\t<input type='hidden' name='stkid[]' value='{$stkid}' />\n\t\t\t\t<td>\n\t\t\t\t\t<input type='hidden' name='actual_sales[{$stkid}]'\n\t\t\t\t\tvalue='{$actual_sales}' />\n\t\t\t\t\t<span style='color: {$actual_color}'>{$actual_sales}</span>\n\t\t\t\t</td>\n\t\t\t\t<td>\n\t\t\t\t\t<input type='hidden' name='projected_sales[{$stkid}]'\n\t\t\t\t\tvalue='{$projected_sales}' />\n\t\t\t\t\t<span style='color: {$projected_color}'>{$projected_sales}</span>\n\t\t\t\t</td>";
        }
    }
    $OUTPUT = "\n\t<center>\n\t<h3>Compare Sales Forecast</h3>\n\t<table " . TMPL_tblDflts . ">\n\t\t<tr>\n\t\t\t<th rowspan='2'>Stock Code</th>\n\t\t\t<th rowspan='2'>Stock Item</th>\n\t\t\t<th colspan='2'>Total</th>\n\t\t\t{$stores_th_lv1}\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th>Actual</th>\n\t\t\t<th>Projected</th>\n\t\t\t{$stores_th_lv2}\n\t\t</tr>\n\t\t{$stock_out}\n\t\t<tr>\n\t</table>\n\t</center>";
    return $OUTPUT;
}

 /**
  * Se Mueve al resultado indicado por $number en un select
  *
  * @param int $number
  * @param resource $resultQuery
  * @return boolean
  */
 function data_seek($number, $resultQuery = NULL)
 {
     if (!$resultQuery) {
         $resultQuery = $this->last_result_query;
         if (!$resultQuery) {
             return false;
         }
     }
     if (($success = pg_result_seek($resultQuery, $number)) !== false) {
         return $success;
     } else {
         throw new KumbiaException($this->error());
     }
 }

	function query($sql, $verbose=false){
		return new TestPostgrResult(array());
		
		$sql = trim($sql);
		if($this->_postgr_link === false){
			$this->_postgr_link = pg_connect("host=" . $this->host . " user=" . $this->user . " password=" . $this->pass . " dbname=" . $this->database);
			pg_query($this->_postgr_link, "SET NAMES 'utf8'");
		}
		if($this->_postgr_link === false){
			throw new DatabaseException("could not connect to MySQL");
		};

		if($this->_query_cache->get($sql)){
			if($verbose)echo "found in cache<br>";
			$result = $this->_query_cache->get($sql);
			if(pg_num_rows($result)){
				if($verbose) echo ": seeking to 0";
				pg_result_seek($result, 0);
			}
			$ret = new PostgrResult($this->_postgr_link, $result);
			if($verbose) echo "<br>";
		}else{
			if($verbose) echo "not in cache";
			$this->_query_count++;
			/**
			 * this following line should be run once per connection to postgres
			 *
			 * i'm running it before every query. I can probably optimize this
			 * to run once per connection, but I need to do some thorough testing...
			 *
			 * http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html
			 */
			pg_query($this->_postgr_link, "SET NAMES 'utf8'");
			$timer = new Timer();
			$timer->start();
			$result = pg_query($this->_postgr_link, $sql);
			$ret = new PostgrResult($this->_postgr_link, $result);
			$timer->stop();
			$time = $timer->read();
			
			if(is_object($this->logger)){
				$this->logger->log($this, ALogger::$LOW, $sql);
			}
			
			/**
			 * the query is too long! oh noes!
			 */
			if($time > .1){
				/**
				 * save the query to the DB, so I can look at it later
				 */
				if(is_object($this->logger)){
					$this->logger->longQuery($time, $sql);
				}
			}
			
			if(pg_last_error($this->_postgr_link)){
				if($verbose) echo "postgr_error: " . pg_last_error($this->_postgr_link) . "<br>";
				throw new DatabaseException(pg_last_error($this->_postgr_link));
			}
			if(strpos($sql, "SELECT") === 0){
				if($verbose) echo ": select: $sql<br><br>";
				$this->_query_cache->put($sql, $result);
			}else{
				if($verbose) echo ": not select: $sql<br>";
				if($verbose) echo "clearing cache<br>";
				$this->_query_cache->reset();
			}

		}
		return $ret;
	}