public DataTable QueryLog(int pageSize, int pageNumber, string condition, string sort)
{
string commandText = "";
if (pageNumber == 1)
{
commandText = string.Format("SELECT TOP {0} {1} FROM [{2}log] ORDER BY {3}",
pageSize,
RDBSFields.LOGSERVER,
RDBSHelper.RDBSTablePre,
sort);
}
else
{
commandText = string.Format("SELECT {0} FROM (SELECT TOP {3} ROW_NUMBER() OVER (ORDER BY {2}) AS [rowid],{0} FROM [{1}Log]) AS [temp] WHERE [rowid] BETWEEN {4} AND {3}",
RDBSFields.LOGSERVER,
RDBSHelper.RDBSTablePre,
sort,
pageNumber * pageSize,
(pageNumber - 1) * pageSize + 1);
}
return(RDBSHelper.ExecuteDataset(CommandType.Text, commandText).Tables[0]);
}
/// <summary>
/// 获得销售商品列表
/// </summary>
/// <param name="pageSize">每页数</param>
/// <param name="pageNumber">当前页数</param>
/// <param name="storeId">店铺id</param>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="orderState">订单状态</param>
/// <returns></returns>
public DataTable GetSaleProductList(int pageSize, int pageNumber, int storeId, string startTime, string endTime, int orderState)
{
string condition = GetSaleProductListCondition(storeId, startTime, endTime, orderState);
string commandText;
if (pageNumber == 1)
{
commandText = string.Format("SELECT TOP {2} [temp2].[psn],[temp2].[name],[temp2].[realcount],[temp2].[shopprice],[temp1].[osn],[temp1].[addtime] FROM (SELECT [oid],[osn],[addtime] FROM [{0}orders] {1}) AS [temp1] LEFT JOIN [{0}orderproducts] AS [temp2] ON [temp1].[oid]=[temp2].[oid] ORDER BY [recordid] DESC",
RDBSHelper.RDBSTablePre,
condition,
pageSize);
}
else
{
commandText = string.Format("SELECT [psn],[name],[realcount],[shopprice],[osn],[addtime] FROM (SELECT TOP {1} ROW_NUMBER() OVER (ORDER BY [recordid] DESC) AS [rowid],[temp2].[psn],[temp2].[name],[temp2].[realcount],[temp2].[shopprice],[temp1].[osn],[temp1].[addtime] FROM (SELECT [oid],[osn],[addtime] FROM [{0}orders] {3}) AS [temp1] LEFT JOIN [{0}orderproducts] AS [temp2] ON [temp1].[oid]=[temp2].[oid]) AS [temp] WHERE [temp].[rowid] BETWEEN {2} AND {1}",
RDBSHelper.RDBSTablePre,
pageNumber * pageSize,
(pageNumber - 1) * pageSize + 1,
condition);
}
return(RDBSHelper.ExecuteDataset(CommandType.Text, commandText).Tables[0]);
}
public DataTable GetShopAreaList()
{
string commandText = string.Format("SELECT DISTINCT Area FROM {0}shops", RDBSHelper.RDBSTablePre);
return(RDBSHelper.ExecuteDataset(CommandType.Text, commandText).Tables[0]);
}
public DataTable GetJobCityList()
{
string commandText = string.Format("SELECT DISTINCT City FROM {0}Jobs", RDBSHelper.RDBSTablePre);
return(RDBSHelper.ExecuteDataset(CommandType.Text, commandText).Tables[0]);
}