public DataTable PagerOrderList(OrderState state, int pageSize, int currentPageIndex, out int recordCount, out int pageCount, string @orderby)
{
int _state=(int)state;
pageCount=0;
recordCount=0;
const string ms_sql=@"SELECT * FROM (SELECT
orderNo,
o.id,
totalFee,
isGuestBuy,
buynerId,
idnum as buynerIdNum,
email as buynerEmail,
buynerName,
buynerPhone,
buynerAddress,
submitTime,
orderState,
sendTime,
confirmTime,
payTime,
email,
ROW_NUMBER()OVER(ORDER BY {3}) as rowNum
FROM
$PREFIX_b2c_order o
LEFT JOIN $PREFIX_b2c_guest g ON o.isGuestBuy=1 AND o.buynerId=g.id
WHERE ({1}=-1 OR orderState={1})) t WHERE rowNum BETWEEN {2} AND ({2}+{0})";
const string my_sql=@"SELECT
orderNo,
o.id,
totalFee,
isGuestBuy,
buynerId,
idnum as buynerIdNum,
email as buynerEmail,
buynerName,
buynerPhone,
buynerAddress,
submitTime,
orderState,
sendTime,
confirmTime,
payTime,
email
FROM
$PREFIX_b2c_order o
LEFT JOIN $PREFIX_b2c_guest g ON o.isGuestBuy AND o.buynerId=g.id
WHERE ({1}=-1 OR orderState={1}) ORDER BY {3} LIMIT {2},{0}
";
string condition=state==OrderState.Unkown?"":" OrderState="+_state;
entity.SqlFormat format=new entity.SqlFormat();
//排序规则
if (String.IsNullOrEmpty(orderby)) orderby = String.Intern("orderNo DESC");
//记录数
recordCount = int.Parse(base.db.ExecuteScalar(
new SqlQuery(format.Format("SELECT COUNT(0) FROM $PREFIX_b2c_Order {0}",condition.Length==0?"":"WHERE "+condition)
)).ToString());
//页数
pageCount = recordCount / pageSize;
if (recordCount % pageSize != 0) pageCount++;
//对当前页数进行验证
if (currentPageIndex > pageCount&¤tPageIndex!=1)currentPageIndex= pageCount;
if (currentPageIndex < 1) currentPageIndex = 1;
//跳过记录数
int skipCount = pageSize * (currentPageIndex - 1);
//如果调过记录为0条，且为OLEDB时候，则用sql1
string sql =base.db.DbType==DataBaseType.MySQL || base.db.DbType==DataBaseType.SQLite?my_sql:ms_sql;
sql=format.Format(sql,pageSize.ToString(), _state.ToString(),skipCount.ToString(),orderby);
return base.GetDataSet(new SqlQuery(sql)).Tables[0];
}
