public async Task <JqTableData <OrderInfo> > QueryPaginationData(JqTableParams param)
{
IEnumerable <OrderInfo> users;
var paginationQuery = new PaginationQuery()
{
NeedTotalCount = true,
TableName = "OrderInfo",
Fields = "*",
Orderby = " ORDER BY BuyDate DESC ",
PageIndex = param.iDisplayStart / param.iDisplayLength + 1,
PageSize = param.iDisplayLength,
SqlWhere = GenerateSqlWhere(param)
};
var jqTableData = new JqTableData <OrderInfo>()
{
sEcho = param.sEcho,
};
using (IDbConnection conn = DBConnection.CreateConnection())
{
conn.Open();
DynamicParameters dynamicParameters = paginationQuery.GenerateParameters();
users = await conn.QueryAsync <OrderInfo>(Procedure.GetSpQuery, dynamicParameters, commandType : CommandType.StoredProcedure);
if (paginationQuery.NeedTotalCount)
{
jqTableData.iTotalDisplayRecords = dynamicParameters.Get <int>("@recordCount");
}
}
jqTableData.iTotalRecords = users.Count();
jqTableData.aaData = users;
return(jqTableData);
}
public async Task <IActionResult> GetAsync(JqTableParams param)
{
try
{
var pagedData = await _orderService.QueryPaginationData(param);
return(Ok(pagedData));
}
catch (Exception ex)
{
_log.Error($"获取分页数据失败：{ex.ToString()}");
return(BadRequest());
}
}
public async Task <IActionResult> GetWithdrawApply(JqTableParams param)
{
try
{
var pagedData = await _profitService.QueryWithdrawData(param);
return(Ok(pagedData));
}
catch (Exception ex)
{
_log.Error($"获取分页数据失败：{ex.ToString()}");
return(BadRequest());
}
}
private string GenerateSqlWhere(JqTableParams param)
{
DateTime?startTime = null; //操作日志创建时间
DateTime?endTime = null;
var strWhere = " 1=1 ";
if (!string.IsNullOrEmpty(param.sDateTimeRange))
{
startTime = Convert.ToDateTime(param.sDateTimeRange.Replace(" - ", ",").Split(',')[0]);
endTime = Convert.ToDateTime(param.sDateTimeRange.Replace(" - ", ",").Split(',')[1]);
}
startTime = startTime ?? Convert.ToDateTime("2016-01-01 00:00:00"); //时间区间默认值
endTime = endTime ?? Convert.ToDateTime("2099-01-01 00:00:00"); //时间区间默认值
if (!string.IsNullOrEmpty(param.sSearch))
{
strWhere += string.Format(" and (Id like '%{0}%' or ShopName like '%{0}%' or ItemId like '%{0}%' or ItemPrice like '%{0}%' or BackPrice like '%{0}%' ) ", param.sSearch);
}
strWhere += string.Format(" and BuyDate > '{0}' and BuyDate < '{1}'", startTime, endTime);
return(strWhere);
}
