SQL级联表树形（三）

作者：秋名
撰写时间：2020 年05 月05日
需求：
1，树形选择，改为多选状态，查询表格
2，树形父节点要计算有几个楼层子节点，子节点里面对应宿舍，宿舍对应着床位。（X层-y间-z床 – 已入 a 人）

使用的是layui前端框架：https://www.layui.com/demo/laydate.html
树形zTree_v3使用文档： http://www.treejs.cn/v3/api.php

引用zTree_v3封装好的插件

 <link href="~/A_Resources/R_PC/zTree_v3/css/zTreeStyle/zTreeStyle.css" rel="stylesheet" />
     <script src="~/A_Resources/R_PC/zTree_v3/js/jquery.ztree.core-3.5.js"></script>
    <script src="~/A_Resources/R_PC/zTree_v3/js/jquery.ztree.excheck-3.5.js"></script>

html：

 <div class="zTreeDemoBackground left">
      <ul id="tree" class="ztree" style="width: 90%;    height: 90%; margin:0 5%;background: #fff;border:none;"></ul>
  </div>

JS

  //树形控件
        function TreeView() {
            $.ajax({
                type: "Get",
                url: "Get_TreeView",
                async: false,
                success: function (data) {
                    //console.log(data)
                    var treeObj = $.fn.zTree.init($("#tree"), setting, data);
                }
            });
        }
        var setting = {
            check: {
                enable: true,//是否显示复选框
                chkStyle: "checkbox",
             // chkboxType: { "Y": "ps", "N": "ps" },//是否选择子节点时，选择父节点
                chkboxType: { "Y": "", "N": "" },
                isSimpleData: true,              //数据是否采用简单 Array 格式，默认false
                treeNodeKey: "id",               //在isSimpleData格式下，当前节点id属性
                treeNodeParentKey: "pId",        //在isSimpleData格式下，当前节点的父节点id属性
                showLine: true,                  //是否显示节点间的连线
                checkable: true
            },
            data: {
                simpleData: {
                    enable: true
                }
            },
            callback: {
                onCheck: zTreeOnClick
				//可以是onCheck/也可以onclike
            },
            view: {
                //是否支持同时选中多个节点
                selectedMulti: false
            }
        };
        function zTreeOnClick(event, treeId, treeNode) {
            $("#Hide_ID").val(treeNode.id)
            var treeObj = $.fn.zTree.getZTreeObj("tree");//获取树节点元素
            nodes = treeObj.getCheckedNodes(true);//获取树节点选中元素
            //console.log(treeObj, nodes);
            id = "";
            for (var i = 0; i < nodes.length; i++) {
                id += nodes[i].id+",";
                //因为要获取多个ID，要加上","分隔一下。不然就拼接在一起
            }
            console.log(id);
            //宿舍表格查询
            var ID_QinShi = id //接收树节点选中的值
            layuiTable.reload("tab_QinShi", {
                url: "Select_SuSheH", //请求后台
                page: {
                    curr: 1,
                },
                where: {
                    ID_QinShi: ID_QinShi  //通过ID筛选（前：是后台接收。后：是页面获取值）
                }

            });
        }

后台：

   //树形类字段
    public class TreeVo
{
    public int? ParentID { get; set; }
    public int? SonID { get; set; }
    public string NodeName { get; set; }
    public int? SupplierParentID { get; set; }
    public int? SupplierSonID { get; set; }
}
  /// <summary>
        ///宿舍楼栋树形图
        /// </summary>
        /// <returns></returns>
        public JsonResult Get_TreeView()
        {
            List<Dictionary<string, object>> jsonlist = new List<Dictionary<string, object>>();
            List<TreeVo> treeList = new List<TreeVo>();
            var listSuShe = (from TbTrees in myModel.HB宿管楼栋
                             select new
                             {
                                 宿舍楼栋ID = TbTrees.宿舍楼栋ID,//父亲id
                                 宿舍楼栋MC = TbTrees.宿舍楼栋MC.Trim(),儿子id  
                                 宿舍楼层ID = TbTrees.宿舍楼层ID//节点名
                             }).ToList();
            for (int i = 0; i < listSuShe.Count; i++)
            {
                TreeVo tree = new TreeVo();
                tree.ParentID = listSuShe[i].宿舍楼栋ID;//父亲id  
                tree.SonID = listSuShe[i].宿舍楼层ID;//儿子id
                if (listSuShe[i].宿舍楼层ID == 0)
                {
                    int J = int.Parse(tree.ParentID.ToString());
                    int I = int.Parse(tree.SonID.ToString());
                    int Count = (from tbJurisdictionGroupUser in myModel.HB宿管楼栋
                                 where tbJurisdictionGroupUser.宿舍楼层ID == J
                                 select tbJurisdictionGroupUser).Count();

                    int Count2 = (from tbSuSe in myModel.HB宿管楼栋宿舍
                                  where tbSuSe.宿舍楼栋ID == J
                                  select tbSuSe).Count();

     int Count3 = (from tbSuSe in myModel.HB宿管楼栋宿舍
              join tbChuagwei in myModel.HB宿管宿舍床位 on tbSuSe.宿舍ID equals tbChuagwei.宿舍ID
              join tbStu in myModel.G机构学生 on tbChuagwei.分配机构学生ID equals tbStu.机构学生ID
                                  where tbSuSe.宿舍楼栋ID == J
                                  select tbSuSe).Count();
                    tree.NodeName = listSuShe[i].宿舍楼栋MC.ToString().Trim() + "（" + Count +"层"+"-"+ Count2 + "间"+"-"+"已入住"+ Count3 + "人"+"）";//拼接节点名
                }
                else
                {
                    tree.NodeName = listSuShe[i].宿舍楼栋MC.ToString().Trim();//节点名
                }
                treeList.Add(tree);
            }
            foreach (var model in treeList)
            {
                Dictionary<string, object> jsonobj = new Dictionary<string, object>();
                jsonobj.Add("id", model.ParentID); //父亲id
                jsonobj.Add("pId", model.SonID);//儿子id
                jsonobj.Add("name", model.NodeName);//节点名称
                                                    //jsonobj.Add("icon", "");
                jsonlist.Add(jsonobj);
            }
            return Json(jsonlist, JsonRequestBehavior.AllowGet);
        }
		
		
		
		
		
		
        / <summary>
        / 宿舍号表格查询
        / </summary>
        / <returns></returns>
        public ActionResult Select_SuSheH(LayuiTablePage layuiTablePage,string ID_QinShi)
        {
            // string[] ss= "".Split(',');//截取字符串
            //  var o='d3';
            string[] strs = ID_QinShi.Split(',');//截取一下前端传过来的两个ID值
            ///集合里面的值不能重复
            HashSet<int> vs = new HashSet<int>();//声明集合
            foreach (var item in strs)//遍历前端传来的ID值。
            {
                try
                {
                     //Dictionary<int,string>
                    int i = Convert.ToInt32(item);//接收的ID为String，必须要转换一下。
                    //Add 重复返回fasle，成功true
                    vs.Add(i);
                   // vs.Contains(i);判断是否为空/有值
                }
                catch (FormatException)//FormatException强制转换报的错
                {
                }
            }
            // 1 ,2  3
 List<SuSheBMVo> listSuSe = (from tb宿舍 in myModel.HB宿管楼栋宿舍
      join tb房型 in myModel.HB宿管床位类型与价格 on tb宿舍.房型床位类别ID equals tb房型.房型床位类别ID
                      //orderby tb宿舍.宿舍ID descending
					  select new SuSheBMVo
					  {
					  宿舍ID = tb宿舍.宿舍ID,
					  房型床位类别ID = tb宿舍.房型床位类别ID,
					  宿舍编码 = tb宿舍.宿舍编码,
					  性别= tb宿舍.性别,
					  房型床位类别MC = tb房型.房型床位类别MC,
					  床位数=tb宿舍.床位数,
					  入住人数=tb宿舍.入住人数,
					  宿舍楼层ID=tb宿舍.宿舍楼层ID
					  }).ToList();
            //将前端传过来的值，带着对应ID的数据返回前端
            listSuSe = listSuSe.Where((m) =>
            {
               return vs.Contains(m.宿舍楼层ID==null?0:Convert.ToInt32(m.宿舍楼层ID));
                // ture  返回该条数据
                //false 不返回数据
                //return true;
            }).ToList();

            //计算总条数
            int totalRow = listSuSe.Count();
            //分页数据
            List<SuSheBMVo> dbSuse = listSuSe
                              .Skip(layuiTablePage.GetStartIndex())
                               .Take(layuiTablePage.limit)
                               .ToList();
            //实例化 
            LayuiTableData<SuSheBMVo> layuiTableData = new LayuiTableData<SuSheBMVo>();
            layuiTableData.count = totalRow;
            layuiTableData.data = dbSuse;

            return Json(layuiTableData, JsonRequestBehavior.AllowGet);
        }