bootstraptable 分页循环_bootstraptable 分页查询

#region 查询参数

int pageSize = 10; //默认每页10条

int pageNumber = 1; //默认查询第一页

string keyWord = string.Empty; //搜索关键字

string typeIds = string.Empty; //商品类型id,例如：5,9,10,11,12,54,55

string isCheck = string.Empty; //1检查化验 0处置处方

if (Request.QueryString["rows"] != null)

{

pageSize = int.Parse(Request.QueryString["rows"]);

}

if (Request.QueryString["page"] != null)

{

pageNumber = int.Parse(Request.QueryString["page"]);

}

if (Request.QueryString["keyWord"] != null)

{

keyWord = Request.QueryString["keyWord"];

}

if (Request.QueryString["typeIds"] != null)

{

typeIds = Request.QueryString["typeIds"];

}

if (Request.QueryString["isCheck"] != null)

{

isCheck = Request.QueryString["isCheck"];

}

#endregion

#region 查询结果

string sqlCount = @"SELECT count(1)

FROM goods AS g

INNER JOIN goodstype AS gt ON gt.Id = g.TypeId

INNER JOIN pethospitalgoods AS p ON p.ArticleNum = g.ArticleNumber ";

string sqlData = @"SELECT g.JoinDiscount,g.ArticleNumber,g.GoodsName,g.TypeId AS GoodsType,gt.GoodsTypeName,

g.ShortSpell,g.Specification,g.UnitGroup,p.Stock AS GoodsCount,p.StockUnitId,g.NotIncludeIntoStock,

p.MinSalePrice,p.MaxSalePrice,g.RecipeUnit,g.SaleUnit FROM goods AS g

INNER JOIN goodstype AS gt ON gt.Id = g.TypeId

INNER JOIN pethospitalgoods AS p ON p.ArticleNum = g.ArticleNumber ";

//查询条件

string whereStr = @" WHERE g.GoodsStatus in(1,3,4) AND p.DeptId=" + userinfo.DeptId;

//商品类型搜索

if(typeIds.Length >0)

{

whereStr += " AND gt.`Id` IN(" + typeIds + ")";

}

else

{

//关键字搜索

//检查化验所有商品id

GoodsForOperator gfo = new GoodsForOperator();

List checkTypeItem = new List();

List checkIDList = new List();

checkTypeItem = gfo.GetGoodsType(1);

checkIDList = checkTypeItem.Select(x => x.Id).ToList();

//耗材处置处方所有的商品类型

var findic = gfo.GetGoodsTypeDisposalManagement();

var czhccfIDList = GetChildList(findic).Select(x => x.Id);

whereStr += " AND gt.`Id` IN(" + (isCheck == "1" ? string.Join(",", checkIDList) : string.Join(",", czhccfIDList)) + ")";

if(keyWord.Length > 0)

{

whereStr += " AND (g.`GoodsName` LIKE '%" + keyWord + "%' or g.ShortSpell like '%" + keyWord + "%')";

}

}

string orderStr = " ORDER BY g.NotIncludeIntoStock,p.Stock DESC"; //排序

string pageStr = " LIMIT " + ((pageNumber - 1) * pageSize) + "," + pageSize; //分页

sqlCount += whereStr;

int total = DbContext.Database.SqlQuery(sqlCount, new object() { }).FirstOrDefault(); //总记录数

var sql = (sqlData + whereStr + orderStr + pageStr);

List list = DbContext.Database.SqlQuery(sql, new object() { }).ToList(); //当前页数据

if (total > 0 && list.Count == 0)

{

pageStr = " LIMIT 0," + pageSize; //分页

sql = (sqlData + whereStr + orderStr + pageStr);

list = DbContext.Database.SqlQuery(sql, new object() { }).ToList(); //当前页数据

}

#endregion

#region 处理结果

if (list.Count > 0)

{

var unitList = DbContext.Unit.ToList(); //单位

//检查化验

if (isCheck == "1")

{

for (int i = 0; i < list.Count; i++)

{

//销售单位

var findSale = unitList.FirstOrDefault(x => x.Id == list[i].SaleUnit);

list[i].SaleUnitName = findSale?.Name;

list[i].StockUnitId = list[i].SaleUnit;

list[i].StockUnitName = findSale?.Name;

//处方单位

var findRecipe = unitList.FirstOrDefault(x => x.Id == list[i].RecipeUnit);

list[i].RecipeUnitName = findRecipe?.Name;

}

}

else //处置耗材处方

{

List listHC = new List();

List listCZ = new List();

List listCF = new List();

GoodsForOperator gfo = new GoodsForOperator();

listHC = gfo.GetGoodsType(2).Select(x => x.Id).ToList(); //耗材

listCZ = gfo.GetGoodsType(3).Select(x => x.Id).ToList(); //处置

listCF = gfo.GetGoodsType(4).Select(x => x.Id).ToList(); //处方

List good = new List();

for (int i = 0; i < list.Count; i++)

{

//销售单位

var findSale = unitList.FirstOrDefault(x => x.Id == list[i].SaleUnit);

list[i].SaleUnitName = findSale?.Name;

list[i].StockUnitId = list[i].SaleUnit;

list[i].StockUnitName = findSale?.Name;

//处方单位

var findRecipe = unitList.FirstOrDefault(x => x.Id == list[i].RecipeUnit);

list[i].RecipeUnitName = findRecipe?.Name;

if (listCF.Exists(x => x == list[i].GoodsType))

{

list[i].Type = 1; //处方

good.Add(list[i]);

}

else if (listHC.Exists(x => x == list[i].GoodsType))

{

list[i].Type = 2; //耗材

good.Add(list[i]);

}

else if (listCZ.Exists(x => x == list[i].GoodsType))

{

list[i].Type = 0;//处置

good.Add(list[i]);

}

}

list = list.OrderBy(x=>x.Type).ToList();

}

}

var obj = new { total = total, rows = list };

return JsonConvert.SerializeObject(obj);

#endregion

}

catch (Exception ex)

{

LogInfo.Error("Base->SearchGoodsByTypeId", "分页查询商品列表失败，错误信息：" + ex.Message, "", userinfo.Id);

var obj = new { total = 0, rows = new object() { } };

return JsonConvert.SerializeObject(obj);

}