bootstraptable 分页查询

1.前端配置

2.后台输出格式化数据

1.前端配置

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>SearchGoods</title>
    <link href="~/Scripts/bootstrap/css/bootstrap.min.css" rel="stylesheet" />
    <link href="~/Scripts/bootstrap/bootstrap-table/css/bootstrap-table.min.css" rel="stylesheet" />
    <script src="~/Scripts/jquery-1.10.2.min.js"></script>
    <script src="~/Scripts/bootstrap/js/bootstrap.min.js"></script>
    <script src="~/Scripts/bootstrap/bootstrap-table/js/bootstrap-table.min.js"></script>
    <script src="~/Scripts/bootstrap/bootstrap-table/js/bootstrap-table-zh-CN.js"></script>
    <script src="~/Scripts/layer/layer.js"></script>
    <style>
        body {
            font-size:12px;
            padding:10px;
        }
    </style>
</head>
<body>         
    <input id="search" name="search_text" class="form-control" style="width:215px;" placeholder="输入后，按回车键搜索" type="text">
    <table id="list"></table>
    <script>
              
        //项目类型
        function ProjTypeFormatter(value, row, index) {
            return row.GroupTypeName == null ? row.GoodsTypeName : "检验组合";
        }
        //项目名称
        function ProjNameFormatter(value, row, index) {
            return row.GroupName == null ? row.GoodsName : row.GroupName;
        }
        //库存
        function stockFormatter(value, row, index) {
            try {
                var StockUnitName = row.StockUnitName;
                var GoodsCount = row.GoodsCount;
                var result = '';
                if (row.NotIncludeIntoStock == 1 || row.Id > 0) {
                    result = '-';
                } else {
                    result = GoodsCount + StockUnitName;
                }
                return ' <span>' + result + '</span>';
            } catch (e) {
                return ' <span>0</span>';
            }
        }
        //单价区间显示
        function PriceFormatter(value, row, index) {
            var price = row.MinSalePrice + "~" + row.MaxSalePrice;
            if(parseFloat(row.MinSalePrice) == parseFloat(row.MinSalePrice)) {
                price = row.MinSalePrice;
            }
            var SaleUnitName = row.SaleUnitName;
            var result = price + '/' + SaleUnitName;
            return row.Id > 0 ? '-' : ' <span>' + result + '</span>';
        }
        //添加操作
        function operationFormatter(value, row, index) {
            var price = row.MinSalePrice + "~" + row.MaxSalePrice;
            return '<a class="glyphicon glyphicon-plus" href="javascript:void(0)"  title="添加"></a> ';
        }

        var $table;
        var rows = 10;
        var queryUrl = '/Base/GetGoodListCreateHis';
        var isCheck = '@ViewData["isCheck"]'; //1检查化验 0处置处方
        var typeIds = '@ViewData["typeIds"]'; //商品类型id, 例如：5,9,10,11,12,54,55
        var keyWord = '@ViewData["keyWord"]'; //关键字，例如：基础检查 | jcjc
        $table = $("#list").bootstrapTable({
            url: queryUrl,
            cache: false,                       // 不缓存
            sidePagination: "server",           //分页方式：client客户端分页，server服务端分页（*）
            pagination: true,                   // 开启分页功能
            pageNumber: 1,                      //第一页开始
            pageSize: rows,                     // 设置默认分页为 10
            //search: false,                       // 开启搜索功能
            //searchOnEnterKey: true,             //设置为 true时，按回车触发搜索方法，否则自动触发搜索方法。
            //searchText: "",                     //初始化搜索文字
            toolbar: '#search',
            toolbarAlign: 'right',
            queryParams: function (params) {
                var temp = {
                    rows: params.limit,                         //页面大小
                    page: (params.offset / params.limit) + 1,   //页码
                    typeIds: typeIds,
                    keyWord: keyWord,
                    isCheck: isCheck
                    //sort: params.sort,                          //排序列名
                    //sortOrder: params.order                     //排位命令（desc，asc）
                };
                return temp; //这里的键的名字和控制器的变量名必须一直，这边改动，控制器也需要改成一样的
            },
            columns: [
                            {
                                title: '项目类型',
                                sortable: true,
                                align: 'center',
                                valign: 'middle',
                                formatter: ProjTypeFormatter
                            },
                           {
                               title: '项目名称',
                               align: 'center',
                               valign: 'middle',
                               formatter: ProjNameFormatter

                           },
                            {
                                title: '库存',
                                field: 'GoodsCount',
                                align: 'center',
                                valign: 'middle',
                                formatter: stockFormatter
                            },
                            {
                                title: '单价',
                                sortable: true,
                                field: 'MinSalePrice',
                                align: 'center',
                                valign: 'middle',
                                formatter: PriceFormatter,
                            },
                           {
                               title: '操作',
                               align: 'center',
                               valign: 'middle',
                               formatter: operationFormatter,
                           }
            ],
            onLoadSuccess: function (data) {

            },
            onClickRow: function (row) {
                //检查化验
                if (isCheck == '1') {
                    parent.addGoods(row);
                    parent.getTotal();
                    parent.layer.close(parent.layer.getFrameIndex(window.name));
                } else {
                    //处置处方
                    parent.addItem(row);
                    parent.layer.close(parent.layer.getFrameIndex(window.name));
                }
            },
            onSearch: function (text) {
                console.log(text)
            }
        });

        $("#search").keyup(function (event) {
            var searchText = $(this).val();

            var param = $("#list").bootstrapTable('getOptions');
            param.queryParams = function (params) {
                var temp = {
                    rows: params.limit,                         //页面大小
                    page: (params.offset / params.limit) + 1,   //页码
                    //typeIds: typeIds,
                    keyWord: searchText,
                    isCheck: isCheck
                    //sort: params.sort,                          //排序列名
                    //sortOrder: params.order                     //排位命令（desc，asc）
                };
                return temp; //这里的键的名字和控制器的变量名必须一直，这边改动，控制器也需要改成一样的
            }

            if (event.keyCode == 13) {
                $("#list").bootstrapTable("refresh");
            }
        })
    </script>
</body>
</html>

2.后台输出格式化数据

#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<GoodsType> checkTypeItem = new List<GoodsType>();
List<int> checkIDList = new List<int>();
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<int>(sqlCount, new object() { }).FirstOrDefault(); //总记录数
var sql = (sqlData + whereStr + orderStr + pageStr);
List<GetMedicalGroup> list = DbContext.Database.SqlQuery<GetMedicalGroup>(sql, new object() { }).ToList(); //当前页数据
if (total > 0 && list.Count == 0)
{
pageStr = " LIMIT 0," + pageSize; //分页
sql = (sqlData + whereStr + orderStr + pageStr);
list = DbContext.Database.SqlQuery<GetMedicalGroup>(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<int> listHC = new List<int>();
List<int> listCZ = new List<int>();
List<int> listCF = new List<int>();

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<GetMedicalGroup> good = new List<GetMedicalGroup>();
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);
}

posted on 2018-08-31 09:12 biind 阅读(493) 评论(0) 编辑收藏举报