protected void btImportARM_Click(object sender, EventArgs e)
{
try
{
if (Request.QueryString["upfile"] == null)
{
Write(0, "");
return;
}
if (!fileUpARM.HasFile)
{
Write(0, "no file");
return;
}
DataTable dtExcelData = new DataTable();
#region 文件数据 读取
string FilePath = this.fileUpARM.FileName;
//string strUploadUrl = Server.MapPath("./");//此"."可以换成项目文件里的其它文件夹名称
string strUploadUrl = Server.MapPath("~/");//此"."可以换成项目文件里的其它文件夹名称
strUploadUrl = strUploadUrl.Replace("Settings\\", "");
#region 文件到临时文件夹
string strUploadFolder = @"\UploadFiles\\Temp\\";
// 临时文件夹
//string sTempUploadFold = strUploadUrl + strUploadFolder; // this.MapPath("~/UploadFiles/Temp/");
string sTempUploadFold = strUploadUrl;
string sFileExt1 = Path.GetExtension(this.fileUpARM.FileName).ToLower();
string sTempFileName1 = Guid.NewGuid().ToString() + sFileExt1;
// 临时文件路径
string sTempFilePath1 = Path.Combine(sTempUploadFold, sTempFileName1); // "D:\\Test\\" + FilePath;//
try
{
// 文件上传到临时文件夹
this.fileUpARM.SaveAs(sTempFilePath1);
}
catch (Exception ex)
{
string exmsg = string.Format("Failed to upload the file due to insufficient privilege: {0}", ex.Message);
PageCommon.WriteJsEnd(this, exmsg, PageCommon.Js_RefreshSelf);
}
#endregion
try
{
PageCommon commonMgr = new PageCommon();
if (sTempFilePath1.Substring(sTempFilePath1.LastIndexOf(".")).ToLower() == ".xlsx")
{
dtExcelData = commonMgr.GetTableFromXlsx(sTempFilePath1);
}
else if (sTempFilePath1.Substring(sTempFilePath1.LastIndexOf(".")).ToLower() == ".xls")
{
dtExcelData = commonMgr.GetTableFromXls(sTempFilePath1);
}
else if (sTempFilePath1.Substring(sTempFilePath1.LastIndexOf(".")).ToLower() == ".csv")
{
dtExcelData = commonMgr.GetTableFromCsv(sTempFilePath1);
}
}
catch (Exception ex)
{
Write(0, "Import Error. Please make sure row 1 of the file has data.");
return;
}
finally
{
if (File.Exists(sTempFilePath1))
{
File.Delete(sTempFilePath1);
}
}
#endregion
if (dtExcelData == null || dtExcelData.Rows.Count == 0)
{
Write(0, "No Data");
return;
}
List <LPWeb.Model.Company_LoanProgramDetails> detailList = new List <LPWeb.Model.Company_LoanProgramDetails>();
#region Check Data
if (dtExcelData.Columns.Count < 7)
{
Write(0, "Columns err");
return;
}
int rowNum = 2;
bool isErr = false;
foreach (DataRow dr in dtExcelData.Rows)
{
var lpDetail = new LPWeb.Model.Company_LoanProgramDetails();
lpDetail.Enabled = true;
if (dr["Investor"] != DBNull.Value)
{
lpDetail.Investor = dr["Investor"].ToString();
}
if (dr["Program"] != DBNull.Value)
{
lpDetail.Program = dr["Program"].ToString();
}
if (dr["Index"] != DBNull.Value)
{
lpDetail.IndexType = dr["Index"].ToString();
}
if (dr["Margin"] != DBNull.Value)
{
try
{
lpDetail.Margin = Convert.ToDecimal(ReplaceEToD(dr["Margin"]));
}
catch
{
Write(0, "The following rows/Column contain invalid data:Row <" + rowNum + "> Column <Margin> must be decimal.");
isErr = true;
break;
}
}
if (dr["1st Adj"] != DBNull.Value)
{
try
{
lpDetail.FirstAdj = Convert.ToDecimal(ReplaceEToD(dr["1st Adj"]));
}
catch
{
Write(0, "The following rows/Column contain invalid data:Row <" + rowNum + "> Column <FirstAdj> must be decimal.");
isErr = true;
break;
}
}
if (dr["Sub Adj"] != DBNull.Value)
{
try
{
lpDetail.SubAdj = Convert.ToDecimal(ReplaceEToD(dr["Sub Adj"]));
}
catch
{
Write(0, "The following rows/Column contain invalid data:Row <" + rowNum + "> Column <Sub Adj> must be decimal.");
isErr = true;
break;
}
}
if (dr["Lifetime"] != DBNull.Value)
{
try
{
lpDetail.LifetimeCap = Convert.ToDecimal(ReplaceEToD(dr["Lifetime"]));
}
catch
{
Write(0, "The following rows/Column contain invalid data:Row <" + rowNum + "> Column <Lifetime> must be decimal.");
isErr = true;
break;
}
}
rowNum++;
detailList.Add(lpDetail);
}
if (isErr)
{
return;
}
#endregion
#region Import
ServiceTypes bllServiceTypes = new ServiceTypes();
LPWeb.BLL.ContactCompanies bllContactCompanies = new ContactCompanies();
Company_LoanProgramDetails bllLoanProgramDetails = new Company_LoanProgramDetails();
foreach (LPWeb.Model.Company_LoanProgramDetails detail in detailList)
{
#region LoanProgramID
LPWeb.Model.Company_Loan_Programs loanpro = bllLoanProgram.GetModelList(" LoanProgram ='" + detail.Program.Trim() + "'").FirstOrDefault();
if (loanpro != null && loanpro.LoanProgram.Trim() == detail.Program.Trim())
{
detail.LoanProgramID = loanpro.LoanProgramID;
if (!loanpro.IsARM)
{
loanpro.IsARM = true;
bllLoanProgram.Update(loanpro);
}
}
else
{
loanpro = new LPWeb.Model.Company_Loan_Programs();
loanpro.IsARM = true;
loanpro.LoanProgram = detail.Program;
int loanprogramsId = bllLoanProgram.Add(loanpro);
detail.LoanProgramID = loanprogramsId;
}
#endregion
#region InvestorID
//detail.LenderCompanyId; 已取出 在CR67
var ServiceType = bllServiceTypes.GetModelList(" Name ='Investor'").FirstOrDefault(); //cr67
int InvestorServiceTypeId = ServiceType == null ? 0 : ServiceType.ServiceTypeId;
string sWhere = string.Format("[Name]='{0}'", detail.Investor.Trim());
if (InvestorServiceTypeId > 0)
{
sWhere += " AND ServiceTypeId=" + InvestorServiceTypeId;
}
LPWeb.Model.ContactCompanies contactComp = bllContactCompanies.GetModelList(sWhere).FirstOrDefault();
if (contactComp != null && contactComp.Name.Trim() == detail.Investor.Trim())
{
detail.InvestorID = contactComp.ContactCompanyId;
}
else
{
contactComp = new LPWeb.Model.ContactCompanies();
contactComp.Name = detail.Investor.Trim();
//var type = bllServiceTypes.GetModelList(" Name ='Lender' or Name = 'Lending '").FirstOrDefault();
if (ServiceType != null)
{
contactComp.ServiceTypeId = ServiceType.ServiceTypeId;
contactComp.ServiceTypes = ServiceType.Name;
}
contactComp.Enabled = true;
var contactCompanyId = bllContactCompanies.Add(contactComp);
detail.InvestorID = contactCompanyId;
}
#endregion
if (bllLoanProgramDetails.Exists(detail.LoanProgramID, detail.InvestorID))
{
bllLoanProgramDetails.Update(detail);
}
else
{
bllLoanProgramDetails.Add(detail);
}
}
#endregion
Write(1, "");
}
catch (Exception ex)
{
Write(0, ex.Message.Replace("'", "").Replace("\r\n", ""));
}
}