Пример #1
0
    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", ""));
        }
    }