public InterestRateModel GetRates(string zipcode)
{
InterestRateModel output = null;
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM rates WHERE zip_code = @zipCode", conn);
cmd.Parameters.AddWithValue("@zipCode", zipcode);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
output = new InterestRateModel();
output.ZipCode = Convert.ToString(reader["zip_code"]);
output.SavingsRate = Convert.ToDouble(reader["savings_rate"]);
output.CheckingRate = Convert.ToDouble(reader["checking_rate"]);
output.CDRate = Convert.ToDouble(reader["cd_rate"]);
}
}
}
catch (SqlException ex)
{
throw;
}
return(output);
}
private InterestRateModel GetIRList(TargetType target)
{
var model = new InterestRateModel
{
TargetValue = target,
Banks = _bankRepository.All.ToList(),
Terms = _termRepository.All.OrderBy(t => t.MonthValue).ToList(),
InterestRates = _interestRateRepository.All.Where(i => i.TargetValue == (int)target).OrderBy(i => i.Term.MonthValue).ToList()
};
return(model);
}
public List <InterestRateModel> GetHttpData()
{
string webData = HttpHelper.HttpGet(URL, "");
string[] strArray = webData.Split(SpiltString, StringSplitOptions.RemoveEmptyEntries);
var lsRate = new List <InterestRateModel>();
int i = 1;
var rate = new InterestRateModel();
//利率
string interestRate = strArray[i].Substring(strArray[i].IndexOf(IntersetRateMark) + IntersetRateMark.Length);
interestRate = interestRate.Substring(0, interestRate.IndexOf(IntersetRateEndMark));
//借款时间
string loadTime = strArray[i].Substring(strArray[i].IndexOf(LoadTimeMark) + LoadTimeMark.Length);
loadTime = loadTime.Substring(0, loadTime.IndexOf(LoadTimeEndMark));
//标类型
string markType = strArray[i].Substring(strArray[i].IndexOf(MarkTypeMark));
markType = markType.Substring(markType.IndexOf(MarkTypeMark2) + MarkTypeMark2.Length);
markType = markType.Substring(0, markType.IndexOf(MarkTypeEndMark));
//期限还款方式
string timePayment = strArray[i].Substring(strArray[i].IndexOf(TimePaymentMark) + TimePaymentMark.Length);
timePayment = timePayment.Substring(0, timePayment.IndexOf(TimePaymentEndMark));
timePayment = timePayment.Replace(" ", "");
int spanIndex = timePayment.IndexOf("</span>");
//期限
int timeLimint = int.Parse(timePayment.Substring(0, spanIndex));
timePayment = timePayment.Substring(spanIndex + "</span>".Length);
//3</span>个月/按月分期
if (timePayment.Substring(0, timePayment.IndexOf("/")).Contains("月"))
{
//一个月按30天算
timeLimint *= 30;
}
//还款方式
string paymentMethod = timePayment.Substring(timePayment.IndexOf("/") + "/".Length);
rate.InterestRate = decimal.Parse(interestRate);
rate.MarkTypeName = MarkTypeDic[markType];
rate.PaymentMethod = paymentMethod;
rate.LoadTime = DateTime.Parse(loadTime);
rate.TimeLimit = timeLimint;
lsRate.Add(rate);
return(lsRate);
}
// GET: Home/ViewRates?zipcode=12345
public ActionResult ViewRates(string zipcode)
{
// If the user provided a zip code, set it in session
if (!String.IsNullOrEmpty(zipcode))
{
Session["visitor_zipcode"] = zipcode; //<-- Sets the zip code into session
// under the key "visitor_zipcode"
}
// Get the zipcode out of session but cast it as a string
zipcode = Session["visitor_zipcode"] as string;
InterestRateModel model = dal.GetRates(zipcode);
return(View("ViewRates", model));
}
public bool ReadInterestRate(string fileName, out string errorMsg)
{
string[] ExcelColumns = new string[] { "利率", "标类型", "还款方式", "借款时间", "借款期限" };
var lsDt = ExcelHelper.ReadWholeExcel(fileName);
//逐个DataTable校验
int Count = lsDt.Count;
for (int i = 0; i < Count; i++)
{
if (lsDt[i].Rows.Count == 0)
{
i--; Count--;
lsDt.RemoveAt(i);
continue;
}
foreach (var col in ExcelColumns)
{
if (!lsDt[i].Columns.Contains(col))
{
i--; Count--;
lsDt.RemoveAt(i);
break;
}
}
}
if (Count == 0)
{
errorMsg = "Excel 模板错误或导入模板为空.";
return(false);
}
var lsRate = new List <InterestRateModel>();
try {
foreach (var dt in lsDt)
{
foreach (DataRow dr in dt.Rows)
{
var rate = new InterestRateModel()
{
InterestRate = decimal.Parse(dr["利率"].ToString())
,
MarkTypeName = dr["标类型"].ToString()
,
PaymentMethod = dr["还款方式"].ToString()
,
LoadTime = DateTime.Parse(dr["借款时间"].ToString())
,
TimeLimit = int.Parse(dr["借款期限"].ToString())
};
lsRate.Add(rate);
}
}
}catch (Exception ex)
{
errorMsg = ex.Message;
return(false);
}
errorMsg = null;
return(insertData.BathInsertInterestRate(lsRate) > 0);
}
