protected void Page_Load(object sender, EventArgs e) { #region 校验页面参数 if (this.Request.QueryString["CloseDialogCodes"] == null) { PageCommon.WriteJsEnd(this, "Missing required query string.", "try{window.parent.location.href=window.parent.location.href}catch{}"); } string sCloseDialogCodes = this.Request.QueryString["CloseDialogCodes"].ToString() + ";"; bool bIsValid = PageCommon.ValidateQueryString(this, "FileID", QueryStringType.ID); if (bIsValid == false) { PageCommon.WriteJsEnd(this, "Missing required query string.", sCloseDialogCodes); } string sFileID = this.Request.QueryString["FileID"].ToString(); this.iFileID = Convert.ToInt32(sFileID); #endregion #region 获取Borrower和Property信息 #region Property LPWeb.BLL.Loans LoanManager = new LPWeb.BLL.Loans(); DataTable LoanInfo = LoanManager.GetLoanInfo(this.iFileID); if (LoanInfo.Rows.Count == 0) { PageCommon.WriteJsEnd(this, "Invalid query string.", sCloseDialogCodes); } string sPropertyAddress = LoanInfo.Rows[0]["PropertyAddr"].ToString(); string sPropertyCity = LoanInfo.Rows[0]["PropertyCity"].ToString(); string sPropertyState = LoanInfo.Rows[0]["PropertyState"].ToString(); string sPropertyZip = LoanInfo.Rows[0]["PropertyZip"].ToString(); string sProperty = sPropertyAddress + ", " + sPropertyCity + ", " + sPropertyState + " " + sPropertyZip; #endregion #region Borrower DataTable BorrowerInfo = LoanManager.GetBorrowerInfo(this.iFileID); if (BorrowerInfo.Rows.Count == 0) { PageCommon.WriteJsEnd(this, "There is no Borrower in this loan.", sCloseDialogCodes); } string sFirstName = BorrowerInfo.Rows[0]["FirstName"].ToString(); string sMiddleName = BorrowerInfo.Rows[0]["MiddleName"].ToString(); string sLastName1 = BorrowerInfo.Rows[0]["LastName"].ToString(); string sBorrower = sLastName1 + ", " + sFirstName; if (sMiddleName != string.Empty) { sBorrower += " " + sMiddleName; } this.lbBorrower.Text = sBorrower; this.lbProperty.Text = sProperty; #endregion #endregion #region 查询条件 string sWhere = string.Empty; if (this.Request.QueryString["search"] == null) // 未设置查询条件 { sWhere = " and (1=0)"; } else { sWhere = ""; // Branch if (this.Request.QueryString["Branch"] != null) { string sBranch = this.Request.QueryString["Branch"]; //gdc 20120621 #1794 sWhere += " And UserId IN ( SELECT u.UserId from Users as u "; sWhere += " left outer join GroupUsers as c on u.UserId=c.UserID "; sWhere += " left outer join Groups as d on c.GroupID=d.GroupId "; sWhere += " left outer join Branches as e on d.BranchID=e.BranchId "; sWhere += " where e.BranchId = " + sBranch; sWhere += " ) "; } // Company if (this.Request.QueryString["UserRole"] != null) { string sUserRole = this.Request.QueryString["UserRole"]; sWhere += " and RoleId=" + sUserRole; } // Last Name if (this.Request.QueryString["LastName"] != null) { string sLastName = this.Request.QueryString["LastName"]; sWhere += " and LastName like '" + sLastName + "%'"; } } #endregion #region 排序 string sOrderByField = "FullName"; if (this.Request.QueryString["OrderByField"] != null) { sOrderByField = this.Request.QueryString["OrderByField"]; } string sOrderByType = "asc"; // default asc if (this.Request.QueryString["OrderByType"] != null) { sOrderByType = this.Request.QueryString["OrderByType"]; if (sOrderByType != "asc" && sOrderByType != "desc") { sOrderByType = "asc"; } } #endregion if (this.IsPostBack == false) { #region 加载Branch Filter string sSql2 = string.Empty; if (CurrUser.bIsCompanyExecutive) { sSql2 = "Select BranchId, [Name] from Branches where [Enabled]=1 order by [Name]"; } else if (CurrUser.bIsRegionExecutive || CurrUser.bIsDivisionExecutive) { sSql2 = string.Format("select a.BranchID, b.Name from dbo.lpfn_GetUserBranches_Executive({0}) as a inner join Branches as b on a.BranchID=b.BranchId where b.Enabled=1 order by b.Name", CurrUser.iUserID); } else if (CurrUser.bIsBranchManager) { sSql2 = string.Format("select a.BranchID, b.Name from dbo.lpfn_GetUserBranches_Branch_Manager({0}) as a inner join Branches as b on a.BranchID=b.BranchId where b.Enabled=1 order by b.Name", CurrUser.iUserID); } else { sSql2 = string.Format("select a.BranchID, b.Name from dbo.lpfn_GetUserBranches({0}) as a inner join Branches as b on a.BranchID=b.BranchId where b.Enabled=1 order by b.Name", CurrUser.iUserID); } DataTable BranchList = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql2); DataRow AllRow = BranchList.NewRow(); AllRow["BranchId"] = 0; AllRow["Name"] = "All"; BranchList.Rows.InsertAt(AllRow, 0); this.ddlBranch.DataSource = BranchList; this.ddlBranch.DataBind(); #endregion #region 加载User Role Filter string sSql3 = "select RoleId, Name from Roles where Name in ('Branch Manager','Closer','Doc Prep', 'Jr Processor','Loan Officer','Loan Officer Assistant','Processor','Shipper','Underwriter') order by Name"; DataTable UserRoleList = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql3); DataTable LoanRoleList = UserRoleList.Copy(); DataRow AllUserRoleRow = UserRoleList.NewRow(); AllUserRoleRow["RoleId"] = 0; AllUserRoleRow["Name"] = "All"; UserRoleList.Rows.InsertAt(AllUserRoleRow, 0); this.ddlUserRole.DataSource = UserRoleList; this.ddlUserRole.DataBind(); #endregion #region 加载Loan Role Filter DataRow AllLoanRoleRow = LoanRoleList.NewRow(); AllLoanRoleRow["RoleId"] = 0; AllLoanRoleRow["Name"] = "-- select --"; LoanRoleList.Rows.InsertAt(AllLoanRoleRow, 0); this.ddlLoanRole.DataSource = LoanRoleList; this.ddlLoanRole.DataBind(); #endregion #region 加载Contact List string sSql = "select * from ( " + "select a.UserId,a.LastName,dbo.lpfn_GetUserName(a.UserId) as FullName,' ' as BranchId, ' ' as BranchName,a.RoleId,b.Name as UserRole " //gdc 20120621 #1794 + "from Users as a " + "left outer join Roles as b on a.RoleId=b.RoleId " //+ "left outer join GroupUsers as c on a.UserId=c.UserID " //gdc 20120621 #1794 //+ "left outer join Groups as d on c.GroupID=d.GroupId " //gdc 20120621 #1794 //+ "left outer join Branches as e on d.BranchID=e.BranchId " //gdc 20120621 #1794 + " where a.UserEnabled=1 and a.UserId not in (select UserID from LoanTeam where FileId=" + iFileID + ")) as m " + "where 1=1 " + sWhere; #region get row count int iRowCount = this.GetLoanTeamListRowCount(sSql, sWhere); this.AspNetPager1.RecordCount = iRowCount; #endregion int iPageSize = this.AspNetPager1.PageSize; int iPageIndex = 1; if (this.Request.QueryString["PageIndex"] != null) { iPageIndex = Convert.ToInt32(this.Request.QueryString["PageIndex"]); } DataTable LoanTeamList = this.GetLoanTeamList(sSql, iRowCount, iPageSize, iPageIndex, sOrderByField, sOrderByType); #region UserBranchInfo gdc 20120621 #1794 // Get user branch info LPWeb.BLL.Users UsersManager = new LPWeb.BLL.Users(); DataTable dtUserBranch = new DataTable(); dtUserBranch = UsersManager.GetUserBranchInfo(); if (null != dtUserBranch) { foreach (DataRow item in LoanTeamList.Rows) { var nUserID = item["UserId"].ToString(); var sUserBranch = item["BranchName"].ToString(); // concatenates all user branch names, using "," between each name StringBuilder sbBName = new StringBuilder(); DataRow[] drs = dtUserBranch.Select(string.Format("UserId={0}", nUserID)); if (null != drs) { foreach (DataRow row in drs) { if (sbBName.Length > 0) { sbBName.Append(", "); } sbBName.Append(row["Name"]); } } //int nDisLen = 30; //if (sbBName.Length > nDisLen) //{ // lblBranch.ToolTip = sbBName.ToString(); // lblBranch.Text = sbBName.ToString().Substring(0, nDisLen) + "..."; //} //else // lblBranch.Text = sbBName.ToString(); item["BranchName"] = sbBName.ToString(); } } #endregion #region set EmptyDataText if (this.Request.QueryString["search"] == null) { this.gridLoanTeamList.EmptyDataText = "Please enter conditions and click Display."; } else { if (iRowCount == 0) { this.gridLoanTeamList.EmptyDataText = "There is no loan team by the conditions."; } } #endregion this.gridLoanTeamList.DataSource = LoanTeamList; this.gridLoanTeamList.DataBind(); #endregion } }
protected void Page_Load(object sender, EventArgs e) { #region 校验页面参数 if (this.Request.QueryString["CloseDialogCodes"] == null) { PageCommon.WriteJsEnd(this, "Missing required query string.", "try{window.parent.location.href=window.parent.location.href}catch{}"); } string sCloseDialogCodes = this.Request.QueryString["CloseDialogCodes"].ToString() + ";"; bool bIsValid = PageCommon.ValidateQueryString(this, "FileID", QueryStringType.ID); if (bIsValid == false) { PageCommon.WriteJsEnd(this, "Missing required query string.", sCloseDialogCodes); } string sFileID = this.Request.QueryString["FileID"].ToString(); this.iFileID = Convert.ToInt32(sFileID); #endregion #region 获取Borrower和Property信息 #region Property LPWeb.BLL.Loans LoanManager = new LPWeb.BLL.Loans(); DataTable LoanInfo = LoanManager.GetLoanInfo(this.iFileID); if (LoanInfo.Rows.Count == 0) { PageCommon.WriteJsEnd(this, "Invalid query string.", sCloseDialogCodes); } string sPropertyAddress = LoanInfo.Rows[0]["PropertyAddr"].ToString(); string sPropertyCity = LoanInfo.Rows[0]["PropertyCity"].ToString(); string sPropertyState = LoanInfo.Rows[0]["PropertyState"].ToString(); string sPropertyZip = LoanInfo.Rows[0]["PropertyZip"].ToString(); string sProperty = sPropertyAddress + ", " + sPropertyCity + ", " + sPropertyState + " " + sPropertyZip; #endregion #region Borrower DataTable BorrowerInfo = LoanManager.GetBorrowerInfo(this.iFileID); if (BorrowerInfo.Rows.Count == 0) { PageCommon.WriteJsEnd(this, "There is no Borrower in this loan.", sCloseDialogCodes); } string sFirstName = BorrowerInfo.Rows[0]["FirstName"].ToString(); string sMiddleName = BorrowerInfo.Rows[0]["MiddleName"].ToString(); string sLastName = BorrowerInfo.Rows[0]["LastName"].ToString(); string sBorrower = sLastName + ", " + sFirstName; if (sMiddleName != string.Empty) { sBorrower += " " + sMiddleName; } this.lbBorrower.Text = sBorrower; this.lbProperty.Text = sProperty; #endregion #endregion #region 查询条件 string sWhere = string.Empty; if (this.Request.QueryString["search"] == null) // 未设置查询条件 { sWhere = " (1=0)"; } else { //sWhere = " (FileId <> " + this.iFileID + ")"; sWhere = " (1=1) "; // Service Type if (this.Request.QueryString["ServiceType"] != null) { string sServiceType = this.Request.QueryString["ServiceType"]; sWhere += " and ServiceTypes='" + sServiceType + "'"; } // Company if (this.Request.QueryString["Company"] != null) { string sCompany = this.Request.QueryString["Company"]; sWhere += " and CompanyName like '" + sCompany + "%'"; } // Zip if (this.Request.QueryString["Zip"] != null) { string sZip = this.Request.QueryString["Zip"]; sWhere += " and MailingZip like '" + sZip + "%'"; } } #endregion #region 排序 string sOrderByField = "ContactsName"; if (this.Request.QueryString["OrderByField"] != null) { sOrderByField = this.Request.QueryString["OrderByField"]; } int iOrderByType = 0; // default asc if (this.Request.QueryString["OrderByType"] != null) { string sOrderByType = this.Request.QueryString["OrderByType"]; if (sOrderByType != "0") { sOrderByType = "1"; } iOrderByType = Convert.ToInt32(sOrderByType); } #endregion if (this.IsPostBack == false) { #region 加载Service Type Filter string sSql2 = "select * from ServiceTypes where Enabled=1 order by Name"; DataTable ServiceTypeList = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql2); DataRow AllRow = ServiceTypeList.NewRow(); AllRow["ServiceTypeId"] = 0; AllRow["Name"] = "All"; AllRow["Enabled"] = true; ServiceTypeList.Rows.InsertAt(AllRow, 0); this.ddlServiceType.DataSource = ServiceTypeList; this.ddlServiceType.DataBind(); #endregion #region 加载Contact Role Filter string sSql3 = "select * from ContactRoles where Enabled=1 and Name <> 'Borrower' and Name <> 'CoBorrower' order by Name"; DataTable ContactRoleList = LPWeb.DAL.DbHelperSQL.ExecuteDataTable(sSql3); DataRow EmptyRow = ContactRoleList.NewRow(); EmptyRow["ContactRoleId"] = 0; EmptyRow["Name"] = "-- select --"; EmptyRow["Enabled"] = true; ContactRoleList.Rows.InsertAt(EmptyRow, 0); this.ddlContactRole.DataSource = ContactRoleList; this.ddlContactRole.DataBind(); #endregion #region 加载Contact List int iPageSize = this.AspNetPager1.PageSize; int iPageIndex = 1; if (this.Request.QueryString["PageIndex"] != null) { iPageIndex = Convert.ToInt32(this.Request.QueryString["PageIndex"]); } int iRowCount = 0; LPWeb.BLL.LoanContacts contacts = new LPWeb.BLL.LoanContacts(); DataSet ds = contacts.GetDistinctLoanContactsForReassign(iPageSize, iPageIndex, sWhere, out iRowCount, sOrderByField, iOrderByType); this.AspNetPager1.RecordCount = iRowCount; #region set EmptyDataText if (this.Request.QueryString["search"] == null) { this.gridLoanContactList.EmptyDataText = "Please enter conditions and click Display."; } else { if (iRowCount == 0) { this.gridLoanContactList.EmptyDataText = "There is no loan contact by the conditions."; } } #endregion this.gridLoanContactList.DataSource = ds; this.gridLoanContactList.DataBind(); #endregion } }
public void BindData() { LPWeb.BLL.Loans bllLoans = new LPWeb.BLL.Loans(); LPWeb.BLL.LoanTeam bllLoanTeam = new LPWeb.BLL.LoanTeam(); LPWeb.BLL.Users bllUsers = new LPWeb.BLL.Users(); LPWeb.Model.Loans loanInfo = bllLoans.GetModel(LoanID); DataTable LoanInfo_datatable = bllLoans.GetLoanInfo(LoanID); decimal loanAmount = 0; LoanPointFields LoanPointFieldsMgr = new LoanPointFields(); //LPWeb.Model.LoanPointFields PointFieldInfo = LoanPointFieldsMgr.GetPointFieldInfo(LoanID, 21017); LPWeb.Model.LoanPointFields PointFieldInfo = LoanPointFieldsMgr.GetModel(LoanID, 21017); if (PointFieldInfo != null && !string.IsNullOrEmpty(PointFieldInfo.CurrentValue)) { decimal.TryParse(PointFieldInfo.CurrentValue, out loanAmount); } if (loanAmount <= 0 && LoanInfo_datatable != null && LoanInfo_datatable.Rows.Count > 0) { loanAmount = LoanInfo_datatable.Rows[0]["LoanAmount"] == DBNull.Value ? 0 : (decimal)LoanInfo_datatable.Rows[0]["LoanAmount"]; } this.labLoanamount.Text = loanAmount.ToString("n2"); var list = new List <ViewCompDetail>(); var Lo = new ViewCompDetail(); Lo.Type = "Loan Officer"; var LOUserId = bllLoanTeam.GetLoanOfficerID(LoanID); Lo.Name = bllLoanTeam.GetLoanOfficer(LoanID); Lo.Rate = GetUserCompRate(LOUserId); Lo.Amount = Lo.Rate * loanAmount / 100.00M; list.Add(Lo); loanInfo.BranchID = loanInfo.BranchID != null ? loanInfo.BranchID : 0; var branchM = new ViewCompDetail(); branchM.Type = "Branch Manager"; LPWeb.BLL.BranchManagers bllbranM = new LPWeb.BLL.BranchManagers(); var branchUserId = 0; var branchObj = bllbranM.GetModelList("BranchId =" + loanInfo.BranchID).FirstOrDefault(); if (branchObj != null) { branchUserId = branchObj.BranchMgrId; } LPWeb.Model.Users userBrM = bllUsers.GetModel(branchUserId); if (userBrM != null && userBrM.UserId == branchUserId) { branchM.Name = userBrM.LastName + "," + userBrM.FirstName; branchM.Rate = GetBranchMgrCompRate(LOUserId); branchM.Amount = branchM.Rate * loanAmount / 100.00M; } list.Add(branchM); loanInfo.DivisionID = loanInfo.DivisionID != null ? loanInfo.DivisionID : 0; var divisionM = new ViewCompDetail(); divisionM.Type = "Division Manager"; LPWeb.BLL.DivisionExecutives bllDivM = new LPWeb.BLL.DivisionExecutives(); var DivMUserId = 0; var divobj = bllDivM.GetModelList("DivisionId =" + loanInfo.DivisionID).FirstOrDefault(); if (divobj != null) { DivMUserId = divobj.ExecutiveId; } LPWeb.Model.Users userDivM = bllUsers.GetModel(DivMUserId); if (userDivM != null && userDivM.UserId == DivMUserId) { divisionM.Name = userDivM.LastName + "," + userDivM.FirstName; divisionM.Rate = GetDivisionMgrCompRate(LOUserId); divisionM.Amount = divisionM.Rate * loanAmount / 100.00M; } list.Add(divisionM); loanInfo.RegionID = loanInfo.RegionID != null ? loanInfo.RegionID : 0; var RegionM = new ViewCompDetail(); RegionM.Type = "Region Manager"; LPWeb.BLL.RegionExecutives bllRegionM = new LPWeb.BLL.RegionExecutives(); var RegionMUserId = 0; var regionObj = bllRegionM.GetModelList("RegionId =" + loanInfo.RegionID).FirstOrDefault(); if (regionObj != null) { RegionMUserId = regionObj.ExecutiveId; } LPWeb.Model.Users userRegionM = bllUsers.GetModel(RegionMUserId); if (userRegionM != null && userRegionM.UserId == RegionMUserId) { RegionM.Name = userRegionM.LastName + "," + userRegionM.FirstName; RegionM.Rate = GetRegionMgrCompRate(LOUserId); RegionM.Amount = RegionM.Rate * loanAmount / 100.00M; } list.Add(RegionM); gvCompDetail.DataSource = list; gvCompDetail.DataBind(); }