<html>
<head><title>Test</title></head>
<body>
<h1>Test Paging</h1>
<?php
// General variables for navBar.
$paging = new PagingClass( getService('getDataCount', array()), $_REQUEST['pg'] );
// Get data according to range.
$data = getService('getData', array('RangeStart' => $paging->getStart(), 'RangeEnd' => $paging->getEnd()));
// Output navBar and data.
print $paging->writeNavBar();
print Service2Table($data);
print $paging->writeNavBar();
?>
<?php
//
// @Author: HomeTom
// @Date: 5/26/2011
//
class PagingClass {
private $pageSize;
private $pageButtonCount;
private $totalCount;
private $pageCount;
private $currentPage;
private $BaseUrl;
//
// Parameters:
// $totalCount: Total number of rows/records.
// $curPage: Current page index (usually passed as request parameter).
// Preassumption: No parameter uses the name "pg", which is used for paging.
//
public function __construct($totalCount, $curPage) {
$this->pageSize = 3; // Default page size.
$this->pageButtonCount = 4; // Default number of paging buttons.
$this->totalCount = $totalCount;
$this->pageCount = ceil($totalCount / $this->pageSize);
$this->currentPage = $curPage;
if ($this->currentPage == "") { $this->currentPage = 1; }
else if ($this->currentPage < 0) { $this->currentPage = 0; }
else if ($this->currentPage >= $this->pageCount) { $this->currentPage = $this->pageCount - 1; }
// Base URL used by page links. Page parameter should be at the end. E.g. "index.php?pg="
$baseUrl = $_SERVER['PHP_SELF'] . "?" . $_SERVER['QUERY_STRING'];
if (preg_match("#pg=[0-9]*$#", $baseUrl) > 0) {
$this->BaseUrl = preg_replace("#pg=[0-9]*$#", "", $baseUrl) . "pg=";
} else if ( empty($_SERVER['QUERY_STRING']) ) {
$this->BaseUrl = $baseUrl . "pg=";
} else {
$this->BaseUrl = $baseUrl . "&pg=";
}
}
// Get start and end row/record number in current page.
public function getStart() { return $this->currentPage * $this->pageSize + 1; }
public function getEnd() { return (1 + $this->currentPage) * $this->pageSize; }
//
// Parameters:
// $PageCount: Total number of pages.
// $CurrentPageIndex: Current page index.
// $PageButtonCount: Number of page buttons to show on one page.
//
public function writeNavBar() {
$PageCount = $this->pageCount;
$CurrentPageIndex = $this->currentPage;
$PageButtonCount = $this->pageButtonCount;
$baseUrl = $this->BaseUrl; //"index.php?pg=";
$DEBUG = 0;
$lblNext = "Next";
$lblPrev = "Prev";
$lblFirst = "First";
$lblLast = "Last";
$s = "";
if ($DEBUG) {
print "pagecount: $PageCount, currentPageIndex: $CurrentPageIndex, ";
print "PageButtonCount: $PageButtonCount<br>";
}
$startPage = (floor(($CurrentPageIndex)/$PageButtonCount) * $PageButtonCount);
if ($DEBUG) print "startpage = $startPage<br>";
$tmp = $PageCount - $PageButtonCount;
if ($tmp > 0 && $tmp < $startPage) { $startPage = $tmp; }
// First.
if ($CurrentPageIndex == 0) { $s .= $lblFirst . " "; }
else { $s .= "<a href=\"" . $baseUrl . "0\">" . $lblFirst . "</a> "; }
// Prev.
if ($CurrentPageIndex == 0) { $s .= $lblPrev . " "; }
else
{
$j = $CurrentPageIndex - 1;
$s .= "<a href=\"" . $baseUrl . $j . "\">" . $lblPrev . "</a> ";
}
// ...
if ($startPage > 0) { $s .= "<a href=\"" . $baseUrl . ($startPage - 1) . "\">...</a> "; }
for ($i = 0; $i < $PageCount; $i ++) {
if ($i < $startPage || $i >= $startPage + $PageButtonCount) { continue; }
if ($i == $CurrentPageIndex) { $s .= " " . (1 + $i); }
else { $s .= " <a href='" . $baseUrl . $i . "'>". (1 + $i) . "</a>"; }
}
// ...
if ($startPage + $PageButtonCount <= $PageCount - 1) {
$j = $PageButtonCount + $startPage;
$s .= " <a href=\"" . $baseUrl . $j . "\">...</a> ";
}
// Next.
if ($CurrentPageIndex >= $PageCount - 1) { $s .= " " . $lblNext; }
else
{
$j = $CurrentPageIndex + 1;
$s .= " <a href=\"" . $baseUrl . $j . "\">" . $lblNext . "</a>";
}
// Last.
if ($CurrentPageIndex >= $PageCount - 1) { $s .= " " . $lblLast; }
else { $s .= " <a href=\"" . $baseUrl . ($PageCount - 1) . "\">" . $lblLast . "</a>"; }
return $s;
}
}
//
// This function retrieves a web service return result.
// http://www.php.net/manual/en/soapclient.soapcall.php
//
// Parameters:
// $func: Name of the web service function.
// $params: Array of parameters used by this function.
//
function getService($func, $params) {
try {
$url = "http://localhost/test/TestService.asmx?WSDL";
$client = new SoapClient($url);
$result = $client->$func($params);
//reset($result); // 'reset' sets the array pointer to the start of the array.
// 'each' gets the current key/value pair into a separate array.
list($key, $val) = each($result);
//print $key . ": " . $val . "<br>";
return $val;
} catch (Exception $e) {
die ("<br><font color='red'>getService() error: " . $e->getMessage() . "</font><br>");
}
}
//
// This function converts web service return result into a table.
//
// rows are delimited by '\n',
// cols are delimited by '\t'.
//
// explode() v.s. split():
// Biggest difference is explode() takes a delimiter to split by,
// while split() takes a regular expression. explode is faster.
//
function Service2Table($val) {
$row_delimiter = "\n";
$col_delimiter = "\t";
$DEBUG = 0;
$v = "";
$v_row = "";
$rows = explode($row_delimiter, $val);
$row_count = count($rows);
if ($DEBUG) print "<br>row count: " . $row_count . "<br>";
for ($i = 0; $i < $row_count; $i ++) {
$s = $rows[$i];
if ($s != "") {
if ($DEBUG) print "$i. $s<br>";
$cols = explode($col_delimiter, $s);
$col_count = count($cols);
$v_row = "";
for ($j = 0; $j < $col_count; $j ++) {
$v_row .= "<td>" . $cols[$j] . " </td>";
}
$v .= "<tr>$v_row</tr>";
}
}
$v = "<table border=1>$v</table>";
return $v;
}
?>
</body>
</html>
On server side, the web service function (in C#) looks like this:
[WebMethod]
public string getData(int RangeStart, int RangeEnd) {
string row_delimiter = "\n";
string col_delimiter = "\t";
string s = "";
string connStr = ConfigurationManager.ConnectionStrings["LocalDB"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
string sql = @"with TmpDataTable AS
(SELECT *, ROW_NUMBER() OVER (ORDER BY JobID) as 'RowNum' FROM DataTable)
SELECT * FROM TmpDataTable WHERE RowNum between " + RangeStart + " and " + RangeEnd;
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader sdr = cmd.ExecuteReader();
int fieldCount = sdr.VisibleFieldCount;
// get column names.
s = sdr.GetName(0);
for (int i = 1; i < fieldCount; i++)
{
s += col_delimiter + formatStr(sdr.GetName(i));
}
s += row_delimiter;
if (sdr.HasRows) {
// get column values.
while (sdr.Read()) {
s += sdr[0];
for (int i = 1; i < fieldCount; i++) {
s += col_delimiter + formatStr(sdr[i]);
}
s += row_delimiter;
}
}
conn.Close();
return s.Trim();
}
[1] Pagination in SQL Server
[2] retrieve specific range of rows in a SQL Server table
No comments:
Post a Comment