public ActionResult OrganIndex(string organid) { string sql1 = "select t1.*,"+ "isnull((select count(1) from t_applyrecord t10,t_product t20 where t10.sproductcode=t20.sproductcode and t10.sCaseState='2' and t20.sOrganID=t1.sOrganID),0) as HandledCount "+ " from t_foreignorgan t1 where sorganid='" + organid + "'"; string sql2 = " select t1.sProductCode,t1.sProductName,t1.sOrganID, t1.sProductType, t1.dAnnualRate, t1.sApplyCondition, t1.sRequiredFile, t1.sMemo, t1.sDetails,t1.sRepaymentType,t1.sChars," + "t1.dMoneyTop,t1.dMoneyBottom," + "t1.nTermTop, t1.nTermBottom," + "t1.nGetLoanDays,t1.dServerFeeOnce,t1.dServerFeeMonthly," + " t2.sOrganName, t2.sLogo" + " from T_Product t1" + " left join T_ForeignOrgan t2 on t1.sOrganID=t2.sOrganID" + " where t1.sOrganID='" + organid + "'"; string sql3 = " select t1.sCustomName, t1.sOrganID, t2.sOrganName" + " from T_Custom t1" + " left join T_ForeignOrgan t2 on t1.sOrganID=t2.sOrganID"+ " where t1.sOrganID='" + organid + "'"; DA_Adapter da = new DA_Adapter(); DataSet ds = new DataSet(); string err=""; da.Common_Query_MultiTable(ref ds, sql1, "T_Organ", sql2, "T_Product", sql3, "T_Custom", ref err); DataRow dr = ds.Tables[0].Rows[0]; OrganPage o = new OrganPage(); o.HandledCount = dr["HandledCount"].ToString(); o.Memo = dr["sMemo"].ToString(); o.OrganLogo = "../photos/" + dr["sLogo"].ToString(); o.OrganName = dr["sOrganName"].ToString(); o.OrganType = dr["sEasyName"].ToString(); o.Ranking = "3"; o.Tel = dr["sTel"].ToString(); #region 加载产品列表 DataRow[] listCustomRows; ProductModel p; decimal dYuanMoney = 100000; int term = 12; foreach (DataRow drProduct in ds.Tables["T_Product"].Rows) { listCustomRows = ds.Tables["T_Custom"].Select("sOrganID='" + dr["sOrganID"].ToString() + "'"); p = BizCommon.Convert2Product(drProduct, listCustomRows, dYuanMoney, term); o.ProductList.Add(p);//未分页显示。 } #endregion return View(o); }
public ActionResult ProductList(string type,string money,string term1,string term,string pindex,string orderby,string organeasyname) { //前端页要用这三个参数 ViewBag.type=type; ViewBag.money = money; ViewBag.term = term; ViewBag.term1 = term1; ViewBag.orderby = orderby; ViewBag.organeasyname = organeasyname; decimal dYuanMoney = Convert.ToDecimal(money) * 10000; DA_Adapter da = new DA_Adapter(); string sql1 = " select t1.sProductCode,t1.sProductName,t1.sOrganID, t1.sProductType, t1.dAnnualRate, t1.sApplyCondition, t1.sRequiredFile, t1.sMemo, t1.sDetails,t1.sRepaymentType,t1.sChars," + "t1.dMoneyTop,t1.dMoneyBottom,"+ "t1.nTermTop, t1.nTermBottom," + "t1.nGetLoanDays,t1.dServerFeeOnce,t1.dServerFeeMonthly," + "(t1.dServerFeeMonthly+t1.dAnnualRate+t1.dServerFeeOnce) as dTotalCost,"+ " t2.sOrganName, t2.sLogo" + " from T_Product t1" + " inner join T_ForeignOrgan t2 on t1.sOrganID=t2.sOrganID"+ " where t1.sProductType='" + type + "' and t1.dMoneyBottom<=" + dYuanMoney.ToString() + " and t1.dMoneyTop>=" + dYuanMoney.ToString() + " and t1.nTermBottom<="+ term +" and t1.nTermTop>="+ term; if (organeasyname != null && organeasyname != "") { sql1 += " and t2.sEasyName='" + organeasyname + "'"; } if (orderby != null && orderby != "") { sql1 += " order by " + orderby; } string sql2 = " select t1.sCustomName, t1.sOrganID, t2.sOrganName" + " from T_Custom t1" + " left join T_ForeignOrgan t2 on t1.sOrganID=t2.sOrganID"; DataSet ds = new DataSet(); string err = ""; da.Common_Query_MultiTable(ref ds, sql1, "T_Product", sql2, "T_Custom", ref err); List<ProductModel> products = new List<ProductModel>(); ProductModel p; DataRow[] listCustomRows; foreach (DataRow dr in ds.Tables["T_Product"].Rows) { listCustomRows=ds.Tables["T_Custom"].Select("sOrganID='" + dr["sOrganID"].ToString() + "'"); p = BizCommon.Convert2Product(dr, listCustomRows, dYuanMoney, Convert.ToInt32(term)); products.Add(p);//未分页显示。 } //总页数(每页显示10条) int countPerPage = 10; int pageCount = products.Count / countPerPage + (products.Count % countPerPage > 0 ? 1 : 0); int pageIndex = (pindex == null || pindex == "") ? 1 : Convert.ToInt32(pindex); pageIndex = pageIndex > pageCount ? pageCount : pageIndex; ViewBag.PageCount = pageCount; ViewBag.PageIndex = pageIndex; var productsShow = new List<ProductModel>(); int start = pageIndex < 1 ? 0 : ((pageIndex - 1) * countPerPage); int end = (pageIndex * countPerPage) > products.Count ? products.Count : (pageIndex * countPerPage); for (int i = start; i < end; i++) { productsShow.Add(products[i]); } return View(productsShow); }
CustomModel GetCustomModel() { Message.CustomInfo loginUser = Session["loginedcustom"] as Message.CustomInfo; string sql1 = "select t1.sEmail,t1.sCustomName,t1.sCertState,t1.sSex,t1.dtBirthday,t1.sCellPhone,t1.sOrganID,t1.sWorkYears, " + "t1.sOrganAddress,t1.sOrganDpt,t1.sAddress," + "t2.sOrganName " + " from t_custom t1 " + " left join t_foreignorgan t2 on t1.sorganid=t2.sorganid " + " where t1.sCustomID='" + loginUser.CustomID + "'"; string sql2 = " select t1.sProductCode,t1.sProductName,t1.sOrganID, t1.sProductType, t1.dAnnualRate, t1.sApplyCondition, t1.sRequiredFile, t1.sMemo, t1.sDetails,t1.sRepaymentType,t1.sChars," + "t1.dMoneyTop,t1.dMoneyBottom," + "t1.nTermTop, t1.nTermBottom," + "t1.nGetLoanDays,t1.dServerFeeOnce,t1.dServerFeeMonthly," + " t2.sOrganName, t2.sLogo" + " from T_Product t1" + " left join T_ForeignOrgan t2 on t1.sOrganID=t2.sOrganID" + " where t1.sOrganID='" + loginUser.OrganID + "'"; string sql3 = "select t1.*," + "t2.aaa103 as sProductTypeName " + " from t_applyrecord t1 " + " left join aa10 t2 on t1.sProductType=t2.aaa102 and t2.aaa100='sProductType'" + " inner join t_product t3 on t1.sProductCode=t3.sProductCode and t3.sOrganID='" + loginUser.OrganID + "'"; DA_Adapter da = new DA_Adapter(); DataSet ds = new DataSet(); string err = ""; da.Common_Query_MultiTable(ref ds, sql1, "T_Custom", sql2, "T_Product", sql3, "T_ApplyRecord", ref err); DataRow drCustom = ds.Tables["T_Custom"].Rows[0]; CustomModel m = new CustomModel(); m.CellPhone = drCustom["sCellPhone"] is DBNull ? "" : drCustom["sCellPhone"].ToString(); m.CustomID = loginUser.CustomID; m.CustomName = drCustom["sCustomName"] is DBNull ? "" : drCustom["sCustomName"].ToString(); m.CertState = drCustom["sCertState"] is DBNull ? "" : drCustom["sCertState"].ToString(); m.CertStateDisplay = ToCertState(m.CertState); m.DateOfBirth = drCustom["dtBirthday"] is DBNull ? "" : Convert.ToDateTime(drCustom["dtBirthday"]).ToString("yyyy-MM-dd"); m.Email = drCustom["sEmail"] is DBNull ? "" : drCustom["sEmail"].ToString(); m.Occupation = ""; m.OrganAddress = drCustom["sAddress"] is DBNull ? "" : drCustom["sAddress"].ToString(); m.OrganID = drCustom["sOrganID"] is DBNull ? "" : drCustom["sOrganID"].ToString(); m.OrganName = drCustom["sOrganName"] is DBNull ? "" : drCustom["sOrganName"].ToString(); m.Sex = drCustom["sSex"] is DBNull ? "" : drCustom["sSex"].ToString(); m.WorkingAge = drCustom["sWorkYears"] is DBNull ? "" : drCustom["sWorkYears"].ToString(); m.Address = drCustom["sAddress"] is DBNull ? "" : drCustom["sAddress"].ToString(); m.WorkYears = drCustom["sWorkYears"] is DBNull ? "" : drCustom["sWorkYears"].ToString(); m.OrganDpt = drCustom["sOrganDpt"] is DBNull ? "" : drCustom["sOrganDpt"].ToString(); #region 加载产品列表 DataRow[] listCustomRows; ProductModel p; decimal dYuanMoney = 100000; int term = 12; foreach (DataRow drProduct in ds.Tables["T_Product"].Rows) { listCustomRows = ds.Tables["T_Custom"].Select("sOrganID='" + drCustom["sOrganID"].ToString() + "'"); p = BizCommon.Convert2Product(drProduct, listCustomRows, dYuanMoney, term); m.ProductList.Add(p);//未分页显示。 } #endregion #region 加载申请列表 ApplyingRecord apply; foreach (DataRow drApply in ds.Tables["T_ApplyRecord"].Rows) { apply = new ApplyingRecord(); apply.CarCustomerMonthlySalary = drApply["dCarCustomerMonthlySalary"] is DBNull ? 0 : Convert.ToDecimal(drApply["dCarCustomerMonthlySalary"]); apply.CarProperty = drApply["sCarProperty"] is DBNull ? "" : drApply["sCarProperty"].ToString(); apply.CarPurchasingPeriod = drApply["sCarPurchasingPeriod"] is DBNull ? "" : drApply["sCarPurchasingPeriod"].ToString(); apply.CaseState = drApply["sCaseState"] is DBNull ? "" : drApply["sCaseState"].ToString(); apply.CreatTime = drApply["dtCreatTime"] is DBNull ? "" : drApply["dtCreatTime"].ToString(); apply.CustomerEmail = drApply["sCustomerEmail"] is DBNull ? "" : drApply["sCustomerEmail"].ToString(); apply.CustomerName = drApply["sCustomerName"] is DBNull ? "" : drApply["sCustomerName"].ToString(); apply.CustomerPhone = drApply["sCustomerPhone"] is DBNull ? "" : drApply["sCustomerPhone"].ToString(); apply.FirmAccountBill = drApply["dFirmAccountBill"] is DBNull ? 0 : Convert.ToDecimal(drApply["dFirmAccountBill"]); apply.FirmAge = drApply["sFirmAge"] is DBNull ? "" : drApply["sFirmAge"].ToString(); apply.FirmProperty = drApply["sFirmProperty"] is DBNull ? "" : drApply["sFirmProperty"].ToString(); apply.FirmType = drApply["sFirmType"] is DBNull ? "" : drApply["sFirmType"].ToString(); apply.HouseIncome = drApply["sHouseIncome"] is DBNull ? "" : drApply["sHouseIncome"].ToString(); apply.HouseLocalorNot = drApply["sHouseLocalorNot"] is DBNull ? "" : drApply["sHouseLocalorNot"].ToString(); apply.HouseNew = drApply["sHouseNew"] is DBNull ? "" : drApply["sHouseNew"].ToString(); apply.HouseType = drApply["sFirmType"] is DBNull ? "" : drApply["sFirmType"].ToString(); apply.PerslCardNo = drApply["sPerslCardNo"] is DBNull ? "" : drApply["sPerslCardNo"].ToString(); apply.PerslCreditAllowance = drApply["sPerslCreditAllowance"] is DBNull ? "" : drApply["sPerslCreditAllowance"].ToString(); apply.PerslCreditDue = drApply["sPerslCreditDue"] is DBNull ? "" : drApply["sPerslCreditDue"].ToString(); apply.PerslCreditOwner = drApply["sPerslCreditOwner"] is DBNull ? "" : drApply["sPerslCreditOwner"].ToString(); apply.PerslEmployment = drApply["sPerslEmployment"] is DBNull ? "" : drApply["sPerslEmployment"].ToString(); apply.PerslLoan = drApply["sPerslLoan"] is DBNull ? "" : drApply["sPerslLoan"].ToString(); apply.PerslLoanDue = drApply["sPerslLoanDue"] is DBNull ? "" : drApply["sPerslLoanDue"].ToString(); apply.PerslLoanSucc = drApply["sPerslLoanSucc"] is DBNull ? "" : drApply["sPerslLoanSucc"].ToString(); apply.PerslSalaryType = drApply["sPerslSalaryType"] is DBNull ? "" : drApply["sPerslSalaryType"].ToString(); apply.PerslWorkingAge = drApply["sPerslWorkingAge"] is DBNull ? "" : drApply["sPerslWorkingAge"].ToString(); apply.PerslYoBirth = drApply["sPerslYoBirth"] is DBNull ? "" : drApply["sPerslYoBirth"].ToString(); apply.ProductCode = drApply["sProductCode"] is DBNull ? "" : drApply["sProductCode"].ToString(); apply.ProductType = drApply["sProductType"] is DBNull ? "" : drApply["sProductType"].ToString(); m.ApplyingRecordList.Add(apply); } #endregion return m; }