public static SelectList GetAA10Items(string fieldName, string orderBy)
{
DA_Adapter adapter = new DA_Adapter();
string sOrderBy = "AAA102";
if (orderBy != "")
sOrderBy = orderBy;
DataSet ds = adapter.AA10_Read("AAA100='" + fieldName + "'", sOrderBy);
List<Models.AA10_Item> aa10ItemList = new List<Models.AA10_Item>();
AA10_Item item;
foreach (DataRow dr in ds.Tables[0].Rows)
{
//为了后台返回值轻松取得DropDownList的Text值，将text隐藏于value中
item = new AA10_Item(dr["AAA103"].ToString(), dr["AAA102"].ToString() + "," + dr["AAA103"].ToString());
aa10ItemList.Add(item);
}
return new SelectList(aa10ItemList, "Value", "Text");
}
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;
}
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 MyInfoEdit_Basic(FormCollection values)
{
if (Session["loginedcustom"] == null)
{
return RedirectToAction("Login", "Account");
}
Message.CustomInfo loginUser = Session["loginedcustom"] as Message.CustomInfo;
CustomModel m = new CustomModel();
m.CustomName = (values["sCustomName"] == null ? "" : values["sCustomName"].ToString());
m.Sex = (values["sSex"] == null ? "" : values["sSex"].ToString());
m.Birthday = ((values["dtBirthday"] == null || values["dtBirthday"].ToString() == "") ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(values["dtBirthday"]));
m.Address = (values["sAddress"] == null ? "" : values["sAddress"].ToString());
m.CellPhone = (values["sCellPhone"] == null ? "" : values["sCellPhone"].ToString());
m.Email = (values["sEmail"] == null ? "" : values["sEmail"].ToString());
m.OrganAddress = (values["sOrganAddress"] == null ? "" : values["sOrganAddress"].ToString());
m.OrganName = (values["sOrganName"] == null ? "" : values["sOrganName"].ToString());
m.WorkYears = (values["sWorkYears"] == null ? "" : values["sWorkYears"].ToString());
m.OrganDpt = (values["sOrganDpt"] == null ? "" : values["sOrganDpt"].ToString());
string sql = "update T_Custom set " +
"sCustomName=" + "'" + m.CustomName + "'" +
",sSex=" + "'" + m.Sex + "'" +
",dtBirthday=" + ((m.Birthday.Year == 1 || m.Birthday.Year == 1900) ? "null" : "'" + m.Birthday.ToString("yyyy-MM-dd") + "'") +
",sAddress=" + "'" + m.Address + "'" +
",sCellPhone=" + "'" + m.CellPhone + "'" +
",sEmail=" + "'" + m.Email + "'" +
",sOrganAddress=" + "'" + m.OrganAddress + "'" +
",sOrganName=" + "'" + m.OrganName + "'" +
",sWorkYears=" + "'" + m.WorkYears + "'" +
",sOrganDpt=" + "'" + m.OrganDpt + "'" +
" where sCustomID=" + "'" + loginUser.CustomID + "'";
DA_Adapter da = new DA_Adapter();
if (da.Common_Excute(sql) == 0)
{
//成功
CustomModel model = GetCustomModel();
return View("MyInfo", model);
}
else
{
//失败
ModelState.AddModelError("", "保存失败。");
CustomModel model = GetCustomModel();
return View(model);
}
}
