多表查询

多表查询

开发工具以及关键技术：Visual Studio 2017 MVC JQuery
撰写时间：2019-04-18

今天在这里给大家分享的功能是多表查询

效果图：

MVC代码：

public ActionResult SelectOverdueBillInfo(BsgridPage bsgridPage, int ManagerTypeID, int AreaID, int CustomerTypeID, int CustomerStatusID, int BusinessTypeID, int NumberStatusID, int ArrearsTypeID, string CustomerIdentification, string ContractNumber, string ZoneDescription, string UserNumber)
{
    var list = (from tbOverdueBillInfo in myModels.XPW_OverdueBillInfo
                join tbManagerType in myModels.XSYS_ManagerType on tbOverdueBillInfo.ManagerTypeID equals tbManagerType.ManagerTypeID
                 join tbArea in myModels.XSYS_Area on tbOverdueBillInfo.AreaID equals tbArea.AreaID
                 join tbCustomerType in myModels.XSYS_CustomerType on tbOverdueBillInfo.CustomerTypeID equals tbCustomerType.CustomerTypeID
                 join tbCustomerStatus in myModels.XSYS_StatusType on tbOverdueBillInfo.CustomerStatusID equals tbCustomerStatus.StatusTypeID
                 join tbBusinessType in myModels.XSYS_BusinessType on tbOverdueBillInfo.BusinessTypeID equals tbBusinessType.BusinessTypeID
                 join tbNumberStatus in myModels.XSYS_StatusType on tbOverdueBillInfo.NumberStatusID equals tbNumberStatus.StatusTypeID
                 join tbArrearsType in myModels.XSYS_ArrearsType on tbOverdueBillInfo.ArrearsTypeID equals tbArrearsType.ArrearsTypeID
                 join tbCustomerManager in myModels.PW_Staff on tbOverdueBillInfo.CustomerManagerID equals tbCustomerManager.StaffID
                 orderby tbOverdueBillInfo.OverdueBillInfoID ascending
                 select new OverdueBillInfoVo
                 {
                     OverdueBillInfoID = tbOverdueBillInfo.OverdueBillInfoID,//欠费ID
                     ManagerTypeID = tbOverdueBillInfo.ManagerTypeID,//经理类型ID
                     AreaID = tbOverdueBillInfo.AreaID,//区域ID
                     CustomerTypeID = tbOverdueBillInfo.CustomerTypeID,//客户类型ID
                     CustomerStatusID = tbOverdueBillInfo.CustomerStatusID,//客户状态ID
                     BusinessTypeID = tbOverdueBillInfo.BusinessTypeID,//业务类型ID
                     NumberStatusID = tbOverdueBillInfo.NumberStatusID,//号码状态ID
                     ArrearsTypeID = tbOverdueBillInfo.ArrearsTypeID,//欠费类型ID
                     ManagerTypeName = tbManagerType.ManagerType,//经理类型
                     AreaName = tbArea.Area,//区域
                     CustomerTypeName = tbCustomerType.CustomerType,//客户类型
                     CustomerStatusName = tbCustomerStatus.StatusType,//客户状态
                     BusinessTypeName = tbBusinessType.BusinessType,//业务类型
                     NumberStatusName = tbNumberStatus.StatusType,//号码状态
                     ArrearsTypeName = tbArrearsType.ArrearsType,//欠费类型
                     ContractNumber = tbOverdueBillInfo.ContractNumber,//合同号码
                     ZoneDescription = tbOverdueBillInfo.ZoneDescription,//区号
                     UserNumber = tbOverdueBillInfo.UserNumber,//用户号码
                     CustomerIdentification = tbOverdueBillInfo.CustomerIdentification,//客户标识
                     CustomerName = tbOverdueBillInfo.CustomerName,//客户名称
                     CustomerAddress = tbOverdueBillInfo.CustomerAddress,//客户地址
                     CustomerManagerID = tbOverdueBillInfo.CustomerManagerID,//客户经理ID
                     CustomerManagerName = tbCustomerManager.StaffName,//客户经理名称
                     ArrearsMoney = tbOverdueBillInfo.ArrearsMoney,//欠费金额
                     LateFeeEstimation = tbOverdueBillInfo.LateFeeEstimation,//滞留金估算
                     ArrearsMonthly = tbOverdueBillInfo.ArrearsMonthly,//欠费月次
                     ContactPhoneNumber = tbOverdueBillInfo.ContactPhoneNumber,//联系人电话号码
                     StartOverdueTimeStr = tbOverdueBillInfo.StartOverdueTime.ToString(),//最早欠费时间
                     EndOverdueTimeStr = tbOverdueBillInfo.EndOverdueTime.ToString()//最后欠费时间
                 });
    if (ManagerTypeID > 0)//经理类型ID
    {
        list = list.Where(m => m.ManagerTypeID == ManagerTypeID);
    }
    if (AreaID > 0)//区域ID
    {
        list = list.Where(m => m.AreaID == AreaID);
    }
    if (CustomerTypeID > 0)//客户类型ID
    {
        list = list.Where(m => m.CustomerTypeID == CustomerTypeID);
    }
    if (CustomerStatusID > 0)//客户状态ID
    {
        list = list.Where(m => m.CustomerStatusID == CustomerStatusID);
    }
    if (BusinessTypeID > 0)//业务类型ID
    {
        list = list.Where(m => m.BusinessTypeID == BusinessTypeID);
    }
    if (NumberStatusID > 0)//号码状态ID
    {
        list = list.Where(m => m.NumberStatusID == NumberStatusID);
    }
    if (ArrearsTypeID > 0)//欠费类型ID
    {
        list = list.Where(m => m.ArrearsTypeID == ArrearsTypeID);
    }
    if (!string.IsNullOrEmpty(CustomerIdentification)||!string.IsNullOrEmpty(ContractNumber) ||!string.IsNullOrEmpty(ZoneDescription) ||!string.IsNullOrEmpty(UserNumber))//客户标识,合同号码,区号,用户号码
    {
        list = list.Where(m => m.CustomerIdentification.Contains(CustomerIdentification.Trim()) || m.ContractNumber.Contains(ContractNumber.Trim()) || m.ZoneDescription.Contains(ZoneDescription.Trim()) || m.UserNumber.Contains(UserNumber.Trim()));
    }

    int intTotalRows = list.Count();

    List<OverdueBillInfoVo> OverdueBillInfoVo = list.Skip(bsgridPage.GetStartIndex()).Take(bsgridPage.pageSize).ToList();
    Bsgrid<OverdueBillInfoVo> bsgrid = new Bsgrid<OverdueBillInfoVo>()
    {
        success = true,
        totalRows = intTotalRows,
        curPage = bsgridPage.curPage,
        data = OverdueBillInfoVo
    };
    return Json(bsgrid, JsonRequestBehavior.AllowGet);
}

html代码：

JQuery代码：
使用的是bsgrid插件

    //绑定表格
    $(function () {
	    tabOverdueBillInfo = $.fn.bsgrid.init("tabOverdueBillInfo", {
	    url: '/QueryPlatform/AccountInfoQuery/SelectOverdueBillInfo',
	    autoLoad: false,
	    stripeRows: true,
	    rowHoverColor: true,
	    displayBlankRows: false,
	    pageSize: 10,
	    pageSizeSelect: true,
	    paingLittleToolbar: true,
	    pagingToolbarAlign: "left",
	    event: {
	        customRowEvents: {
	            click: function (record, rowIndex, trObj, options) {
	                if (record != null) {
	                    OverdueBillInfoID = record.OverdueBillInfoID;
	                    searchUserArrearsInfo();
	                    searchAccountArrearsInfo();
	                }
	            }
	        }
	    }
	    searchOverdueBillInfo();
    });
    
    //执行查询按钮
    function searchOverdueBillInfo() {
        var ManagerTypeID = $("#SelectManagerType").val();
        var AreaID = $("#SelectArea").val();
        var CustomerTypeID = $("#SelectCustomerType").val();
        var CustomerStatusID = $("#SelectCustomerStatus").val();
        var BusinessTypeID = $("#SelectBusinessType").val();
        var NumberStatusID = $("#SelectNumberStatus").val();
        var ArrearsTypeID = $("#SelectArrearsType").val();
        var CustomerIdentification = $("#txtCustomerIdentification").val();
        var ContractNumber = $("#txtContractNumber").val();
        var ZoneDescription = $("#txtZoneDescription").val();
        var UserNumber = $("#txtUserNumber").val();

    if (ManagerTypeID == undefined || ManagerTypeID == "") {
        ManagerTypeID = 0;
    }
    if (AreaID == undefined || AreaID == "") {
        AreaID = 0;
    }
    if (CustomerTypeID == undefined || CustomerTypeID == "") {
        CustomerTypeID = 0;
    }
    if (CustomerStatusID == undefined || CustomerStatusID == "") {
        CustomerStatusID = 0;
    }
    if (BusinessTypeID == undefined || BusinessTypeID == "") {
        BusinessTypeID = 0;
    }
    if (NumberStatusID == undefined || NumberStatusID == "") {
        NumberStatusID = 0;
    }
    if (ArrearsTypeID == undefined || ArrearsTypeID == "") {
        ArrearsTypeID = 0;
    }
    tabOverdueBillInfo.search("ManagerTypeID=" + ManagerTypeID + "&AreaID=" + AreaID + "&CustomerTypeID=" + CustomerTypeID + "&CustomerStatusID=" + CustomerStatusID + "&BusinessTypeID=" + BusinessTypeID + "&NumberStatusID=" + NumberStatusID + "&ArrearsTypeID=" + ArrearsTypeID + "&CustomerIdentification=" + CustomerIdentification + "&ContractNumber=" + ContractNumber + "&ZoneDescription=" + ZoneDescription + "&UserNumber=" + UserNumber);
}