public ActionResult GetBoxs(BoxSearchDTO req)
{
int total = 0;
var list = BoxRepository.GetList(out total, req);
return(Json(new { rows = list, total = total }, JsonRequestBehavior.AllowGet));
}
public ActionResult GetBoxs(BoxSearchDTO req)
{
if (req.ListType == 1)
{
req.offset = (req.offset - 1) * req.limit;
}
var list = _boxRepository.GetList(out int total, req);
return(NewtonSoftJson(new
{
rows = list,
total = total,
code = 0,
msg = ""
}, JsonRequestBehavior.AllowGet));
}
public List <BoxListDTO> GetList(out int total, BoxSearchDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
int totalCount = 0;
string order = "Id desc";
List <BoxListDTO> list = new List <BoxListDTO>();
if (!string.IsNullOrEmpty(req.Sort))
{
if (req.Sort.Equals("id", StringComparison.OrdinalIgnoreCase))
{
order = "Id desc";
}
else
{
order = string.Format("{0} {1}", req.Sort, req.Order);
}
}
var sql = db.Queryable <R_Box>()
.JoinTable <R_Restaurant>((s1, s2) => s1.R_Restaurant_Id == s2.Id && s2.IsDelete == false)
.JoinTable <R_Area>((s1, s3) => s1.R_Area_Id == s3.Id && s3.IsDelete == false)
.Select(@"s1.*,Restaurant=s2.Name,RestaurantArea=s3.Name,
TableNum=(select COUNT(0) from R_BoxTable where R_Box_Id=s1.id)").Where(" s1.IsDelete = 0 ");
if (req.Restaurant > 0)
{
sql.Where(" s1.R_Restaurant_Id=" + req.Restaurant);
}
var data = sql.ToDataTable();
/*性能问题*/
//if (req.Restaurant > 0)
//{
// DataRow[] dr = data.Select(" R_Restaurant_Id=" + req.Restaurant);
// DataTable dtNew = data.Clone();
// for (int i = 0; i < dr.Length; i++)
// {
// dtNew.ImportRow(dr[i]);
// }
// data = dtNew;//重新赋值
//}
if (data != null && data.Rows.Count > 0)
{
DataView dtv = data.DefaultView;
dtv.Sort = order;
data = dtv.ToTable();
totalCount = data.Rows.Count;
var rows = data.Rows.Cast <DataRow>();
var curRows = rows.Skip(req.offset).Take(req.limit).ToArray();
foreach (DataRow item in curRows)
{
list.Add(new BoxListDTO()
{
Id = Convert.ToInt32(item["id"]),
Name = item["Name"].ToString(),
Description = item["Description"].ToString(),
RestaurantArea = item["RestaurantArea"].ToString(),
Restaurant = item["Restaurant"].ToString(),
TableNum = Convert.ToInt32(item["TableNum"])
});
}
}
total = totalCount;
return(list);
}
}
