using System;
using System.Collections.Generic;
using System.Data;
using GeoScene.Globe;
namespace Cyberpipe
{
class PaginationUtil
{
private static int _total = 0;
private static int _beginIndex = 1;
private static int _pagesize = 10;
private static string _sql = "";
private static MainFrm.PageControlStuct _pageControl;
// private static DataGridViewX _dataGridView1;
private static MainFrm.DataGridViewDelegate _mInitDataGridViewX1;
private static PaginationUtil _singlePagination = null;
private static string _queryInfo="";
private static string _layerName="";
//定义单例模式，防止点击事件被多次绑定
public static PaginationUtil InitPagination(string sql, int total, MainFrm.PageControlStuct pageControl, MainFrm.DataGridViewDelegate mInitDataGridViewX1,string queryInfo,string layerName)
{
if (_singlePagination == null)
{
_singlePagination = new PaginationUtil(sql, total, pageControl, mInitDataGridViewX1, queryInfo, layerName);
_pageControl.firstpage.Click += _singlePagination.FirstPageButtonClick;
_pageControl.nextpage.Click += _singlePagination.NextPageButtonClick;
_pageControl.prepage.Click += _singlePagination.PrePageButtonClick;
_pageControl.lastpage.Click += _singlePagination.LastPageButtonClick;
return _singlePagination;
}
_queryInfo = queryInfo;
_layerName = layerName;
_sql = sql;
_total = total;
return _singlePagination;
}
private PaginationUtil(string sql, int total, MainFrm.PageControlStuct pageControl, MainFrm.DataGridViewDelegate mInitDataGridViewX1,string queryInfo,string layerName)
{
_pageControl = pageControl;
_mInitDataGridViewX1 = mInitDataGridViewX1;
_queryInfo = queryInfo;
_layerName = layerName;
_sql = sql;
_total = total;
}
/// <summary>
/// 执行sql语句，将执行的分页结果加载到指定datatable上
/// </summary>
public void FillDataGridView()
{
string sql = PageQuerySql(_sql, _beginIndex, _pagesize);
ComputePageInfo(_beginIndex,_pagesize,_total);
DataTable table = OledbHelper.QueryTable(sql);
// _dataGridView1.DataSource = table;
_mInitDataGridViewX1(table, _queryInfo, _layerName, true);
//控制首页、上一页、下一页、末页按钮的显示
_pageControl.nextpage.Visible = _beginIndex + _pagesize - 1 < _total;
_pageControl.lastpage.Visible = _beginIndex + _pagesize - 1 < _total;
_pageControl.firstpage.Visible = _beginIndex > 1;
_pageControl.prepage.Visible = _beginIndex > 1;
}
/// <summary>
/// 查询总记录数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
// public static int Count(string sql)
// {
// DataTable table = OledbHelper.QueryTable(sql);
// int result = int.Parse(table.Rows[0].ItemArray[0].ToString());
// return result;
// }
/// <summary>
/// 将一般查询语句转换为分页查询语句
/// </summary>
/// <param name="sql">原sql语句</param>
/// <param name="begin">开始索引</param>
/// <param name="pageSize">每页记录数</param>
/// <returns></returns>
private static string PageQuerySql(string sql, int begin, int pageSize)
{
int end = begin + pageSize - 1;
string result = "select * from (select rownum 序号,A.* from (" + sql + ") A) B where 序号 between " + begin + " and " + end;
return result;
}
/// <summary>
/// 查询的分页信息
/// </summary>
/// <param name="begin"></param>
/// <param name="pagesize"></param>
/// <param name="total"></param>
private void ComputePageInfo(int begin, int pagesize, int total)
{
int pageCount = (total - 1) / pagesize + 1;
int curPage = begin / pagesize + 1;
_pageControl.pageinfo.Text = "每页" + pagesize + "条，" + curPage + "/" + pageCount + "页";
}
/// <summary>
/// 重置查询条件
/// </summary>
public static void ResetPagination()
{
_total = 0;
_beginIndex = 1;
}
private void FirstPageButtonClick(object sender, EventArgs e)
{
_beginIndex = 1;
FillDataGridView();
}
private void NextPageButtonClick(object sender, EventArgs e)
{
_beginIndex = _beginIndex + _pagesize;
FillDataGridView();
}
private void PrePageButtonClick(object sender, EventArgs e)
{
_beginIndex = _beginIndex - _pagesize;
FillDataGridView();
}
private void LastPageButtonClick(object sender, EventArgs e)
{
while (_beginIndex < _total - _pagesize + 1) _beginIndex += _pagesize;
FillDataGridView();
}
/// <summary>
/// 获取原查询的语句
/// </summary>
/// <param name="mGlobeControl"></param>
/// <param name="layerName"></param>
/// <param name="filedObject"></param>
/// <param name="filedValue"></param>
/// <returns></returns>
public static string GetQuerySql(GSOGlobeControl mGlobeControl, string layerName,
string filedObject, string filedValue)
{
string sql = "";
if (filedObject == "")
{
sql = "select " + GetpipeLineFields.GetFields(layerName, mGlobeControl)
+ " from " + layerName;
}
else
{
sql = "select " + GetpipeLineFields.GetFields(layerName, mGlobeControl)
+ " from " + layerName + " where " + filedObject + " like '%"
+ filedValue + "%'";
}
return sql;
}
public static string GetQuerySql(GSOGlobeControl mGlobeControl, string layerName,
string filedObject, string mathT, string leftFiledValue, string rightFiledValue)
{
string sql = "";
if (mathT == "区间")
{
sql = "select " + GetpipeLineFields.GetFields(layerName, mGlobeControl)
+ " from " + layerName + " where " + filedObject + ">" + leftFiledValue
+ "and " + filedObject + "<" + rightFiledValue;
}
else
{
sql = "select " + GetpipeLineFields.GetFields(layerName, mGlobeControl)
+ " from " + layerName + " where " + filedObject + mathT + rightFiledValue;
}
return sql;
}
public static string GetQuerySql(GSOGlobeControl mGlobeControl,
string layerName, string filedObject, List<string> filedValueList)
{
string sql = "select " + GetpipeLineFields.GetFields(layerName, mGlobeControl)
+ " from " + layerName + " where 1>2 ";
foreach (string value in filedValueList)
{
if (value == "无")
{
sql += " or " + filedObject + " is null";
}
else
{
sql += " or " + filedObject + " = '" + value + "'";
}
}
return sql;
}
public static string GetQuerySql(GSOGlobeControl mGlobeControl, string layerName, string whereSql)
{
string sql = "select " + GetpipeLineFields.GetFields(layerName, mGlobeControl)
+ " from " + layerName + " where " + whereSql;
return sql;
}
/// <summary>
/// 查询记录总数
/// </summary>
/// <param name="layerName"></param>
/// <param name="filedObject"></param>
/// <param name="filedValue"></param>
/// <returns></returns>
public static int CountSql(string layerName,
string filedObject, string filedValue)
{
string sql = "";
if (filedObject == "")
{
sql = "select count(1) from " + layerName;
}
else
{
sql = "select count(1) from " + layerName + " where " + filedObject + " like '%"
+ filedValue + "%'";
}
DataTable table = OledbHelper.QueryTable(sql);
int result = int.Parse(table.Rows[0].ItemArray[0].ToString());
return result;
}
public static int CountSql(string layerName,
string filedObject, string mathT, string leftFiledValue, string rightFiledValue)
{
string sql = "";
if (mathT == "区间")
{
sql = "select count(1) from " + layerName + " where " + filedObject + ">" + leftFiledValue
+ "and " + filedObject + "<" + rightFiledValue;
}
else
{
sql = "select count(1) from " + layerName + " where " + filedObject + mathT + rightFiledValue;
}
DataTable table = OledbHelper.QueryTable(sql);
int result = int.Parse(table.Rows[0].ItemArray[0].ToString());
return result;
}
public static int CountSql(string layerName, string filedObject, List<string> filedValueList)
{
string sql = "select count(1) from " + layerName + " where 1>2 ";
foreach (string value in filedValueList)
{
if (value == "无")
{
sql += " or " + filedObject + " is null";
}
else
{
sql += " or " + filedObject + " = '" + value + "'";
}
}
DataTable table = OledbHelper.QueryTable(sql);
int result = int.Parse(table.Rows[0].ItemArray[0].ToString());
return result;
}
public static int CountSql(string layerName, string whereSql)
{
string sql = "select count(1) from " + layerName + " where " + whereSql;
DataTable table = OledbHelper.QueryTable(sql);
int result = int.Parse(table.Rows[0].ItemArray[0].ToString());
return result;
}
}
}
