public ActionResult Create(FormCollection collection) { CreateModel model = new CreateModel(); ResultModel resultModel = new ResultModel(); IEnumerable<ErrorInfo> errorInfo = null; this.TryUpdateModel(model); string ErrorMessage = InvestorNameAvailable(model.InvestorName, model.InvestorId); StringBuilder errors; EmailAttribute emailValidation = new EmailAttribute(); ZipAttribute zipAttribute = new ZipAttribute(); WebAddressAttribute webAttribute = new WebAddressAttribute(); int count = 0; string errorTitle = string.Empty; if (String.IsNullOrEmpty(ErrorMessage) == false) { ModelState.AddModelError("InvestorName", ErrorMessage); } ErrorMessage = SocialSecurityTaxIdAvailable(model.SocialSecurityTaxId, model.InvestorId); if (String.IsNullOrEmpty(ErrorMessage) == false) { ModelState.AddModelError("SocialSecurityTaxId", ErrorMessage); } if (ModelState.IsValid) { // Attempt to create new deal. DeepBlue.Models.Entity.Investor investor = new DeepBlue.Models.Entity.Investor(); investor.Alias = model.Alias; investor.IsDomestic = model.DomesticForeign; investor.InvestorEntityTypeID = model.EntityType; investor.InvestorName = model.InvestorName; investor.FirstName = model.Alias; investor.ResidencyState = model.StateOfResidency; investor.Social = model.SocialSecurityTaxId ?? ""; investor.Notes = model.Notes; investor.Source = model.Source; investor.FOIA = model.FOIA; investor.ERISA = model.ERISA; investor.TaxID = 0; investor.FirstName = string.Empty; investor.LastName = "n/a"; investor.ManagerName = string.Empty; investor.MiddleName = string.Empty; investor.PrevInvestorID = 0; investor.CreatedBy = Authentication.CurrentUser.UserID; investor.CreatedDate = DateTime.Now; investor.LastUpdatedBy = Authentication.CurrentUser.UserID; investor.LastUpdatedDate = DateTime.Now; investor.EntityID = Authentication.CurrentEntity.EntityID; investor.TaxExempt = false; // Attempt to create new investor address. InvestorAddress investorAddress = new InvestorAddress(); investorAddress.CreatedBy = Authentication.CurrentUser.UserID; investorAddress.CreatedDate = DateTime.Now; investorAddress.EntityID = Authentication.CurrentEntity.EntityID; investorAddress.LastUpdatedBy = Authentication.CurrentUser.UserID; investorAddress.LastUpdatedDate = DateTime.Now; investorAddress.Address = new Address(); investorAddress.Address.Address1 = model.Address1 ?? ""; investorAddress.Address.Address2 = model.Address2 ?? ""; investorAddress.Address.AddressTypeID = (int)DeepBlue.Models.Admin.Enums.AddressType.Work; investorAddress.Address.City = model.City ?? ""; investorAddress.Address.Country = model.Country; investorAddress.Address.CreatedBy = Authentication.CurrentUser.UserID; investorAddress.Address.CreatedDate = DateTime.Now; investorAddress.Address.LastUpdatedBy = Authentication.CurrentUser.UserID; investorAddress.Address.LastUpdatedDate = DateTime.Now; investorAddress.Address.EntityID = Authentication.CurrentEntity.EntityID; investorAddress.Address.PostalCode = model.Zip; investorAddress.Address.State = model.State; if (string.IsNullOrEmpty(investorAddress.Address.Address1) == false || string.IsNullOrEmpty(investorAddress.Address.Address2) == false || string.IsNullOrEmpty(investorAddress.Address.City) == false || string.IsNullOrEmpty(investorAddress.Address.PostalCode) == false || string.IsNullOrEmpty(investorAddress.Address.County) == false || string.IsNullOrEmpty(model.Phone) == false || string.IsNullOrEmpty(model.Email) == false || string.IsNullOrEmpty(model.WebAddress) == false || string.IsNullOrEmpty(model.Fax) == false ) { errorTitle = "<b>Address Information:</b>"; errors = new StringBuilder(); errorInfo = ValidationHelper.Validate(investorAddress.Address); if (errorInfo.Any()) { errors.Append(ValidationHelper.GetErrorInfo(errorInfo)); } if (emailValidation.IsValid(model.Email) == false) errors.Append("Invalid Email\n"); if (zipAttribute.IsValid(model.Zip) == false) errors.Append("Invalid Zip\n"); if (webAttribute.IsValid(model.WebAddress) == false) errors.Append("Invalid Web Address\n"); if (string.IsNullOrEmpty(errors.ToString()) == false) { resultModel.Result = string.Format("{0}\n{1}\n", errorTitle, errors.ToString()); } if (string.IsNullOrEmpty(resultModel.Result)) { /* Add New Investor Address */ investor.InvestorAddresses.Add(investorAddress); /* Investor Communication Values */ AddCommunication(investor, Models.Admin.Enums.CommunicationType.HomePhone, model.Phone); AddCommunication(investor, Models.Admin.Enums.CommunicationType.Email, model.Email); AddCommunication(investor, Models.Admin.Enums.CommunicationType.WebAddress, model.WebAddress); AddCommunication(investor, Models.Admin.Enums.CommunicationType.Fax, model.Fax); } } /* Bank Account */ count = 0; InvestorAccount investorAccount; for (int index = 0; index < model.AccountLength; index++) { if (DataTypeHelper.ToInt32(collection[(index + 1).ToString() + "_" + "BankIndex"]) <= 0) continue; count++; // Attempt to create new investor account. investorAccount = new InvestorAccount(); investorAccount.Comments = string.Empty; investorAccount.CreatedBy = Authentication.CurrentUser.UserID; investorAccount.CreatedDate = DateTime.Now; investorAccount.EntityID = Authentication.CurrentEntity.EntityID; investorAccount.IsPrimary = false; investorAccount.LastUpdatedBy = Authentication.CurrentUser.UserID; investorAccount.LastUpdatedDate = DateTime.Now; investorAccount.Routing = DataTypeHelper.ToInt32(collection[(index + 1).ToString() + "_" + "ABANumber"]); investorAccount.Reference = Convert.ToString(collection[(index + 1).ToString() + "_" + "Reference"]); investorAccount.AccountOf = Convert.ToString(collection[(index + 1).ToString() + "_" + "AccountOf"]); investorAccount.FFC = Convert.ToString(collection[(index + 1).ToString() + "_" + "FFC"]); investorAccount.FFCNumber = Convert.ToString(collection[(index + 1).ToString() + "_" + "FFCNumber"]); investorAccount.IBAN = Convert.ToString(collection[(index + 1).ToString() + "_" + "IBAN"]); investorAccount.ByOrderOf = Convert.ToString(collection[(index + 1).ToString() + "_" + "ByOrderOf"]); investorAccount.SWIFT = Convert.ToString(collection[(index + 1).ToString() + "_" + "Swift"]); investorAccount.Account = Convert.ToString(collection[(index + 1).ToString() + "_" + "Account"]); investorAccount.AccountNumberCash = Convert.ToString(collection[(index + 1).ToString() + "_" + "AccountNumber"]); investorAccount.Attention = Convert.ToString(collection[(index + 1).ToString() + "_" + "Attention"]); investorAccount.BankName = Convert.ToString(collection[(index + 1).ToString() + "_" + "BankName"]); investorAccount.Phone = Convert.ToString(collection[(index + 1).ToString() + "_" + "AccountPhone"]); investorAccount.Fax = Convert.ToString(collection[(index + 1).ToString() + "_" + "AccountFax"]); if (string.IsNullOrEmpty(investorAccount.Comments) == false || string.IsNullOrEmpty(investorAccount.Reference) == false || string.IsNullOrEmpty(investorAccount.AccountOf) == false || string.IsNullOrEmpty(investorAccount.FFC) == false || string.IsNullOrEmpty(investorAccount.FFCNumber) == false || string.IsNullOrEmpty(investorAccount.IBAN) == false || string.IsNullOrEmpty(investorAccount.ByOrderOf) == false || string.IsNullOrEmpty(investorAccount.SWIFT) == false || string.IsNullOrEmpty(investorAccount.Account) == false || string.IsNullOrEmpty(investorAccount.AccountNumberCash) == false || string.IsNullOrEmpty(investorAccount.Attention) == false || string.IsNullOrEmpty(investorAccount.BankName) == false || string.IsNullOrEmpty(investorAccount.Phone) == false || string.IsNullOrEmpty(investorAccount.Fax) == false || investorAccount.Routing > 0) { errorInfo = ValidationHelper.Validate(investorAccount); if (errorInfo.Any()) { resultModel.Result += string.Format("<b>Bank Information {0}:</b>\n{1}\n", count.ToString(), ValidationHelper.GetErrorInfo(errorInfo)); } if (string.IsNullOrEmpty(resultModel.Result)) { investor.InvestorAccounts.Add(investorAccount); } } } count = 0; /* Contact Address */ InvestorContact investorContact; ContactAddress contactAddress; for (int index = 0; index < model.ContactLength; index++) { if (DataTypeHelper.ToInt32(collection[(index + 1).ToString() + "_" + "ContactIndex"]) <= 0) continue; count++; // Attempt to create new investor contact. investorContact = new InvestorContact(); investorContact.CreatedBy = Authentication.CurrentUser.UserID; investorContact.CreatedDate = DateTime.Now; investorContact.EntityID = Authentication.CurrentEntity.EntityID; investorContact.LastUpdatedBy = Authentication.CurrentUser.UserID; investorContact.LastUpdatedDate = DateTime.Now; investorContact.Contact = new Contact(); investorContact.Contact.ContactName = Convert.ToString(collection[(index + 1).ToString() + "_" + "ContactPerson"]); investorContact.Contact.CreatedBy = Authentication.CurrentUser.UserID; investorContact.Contact.CreatedDate = DateTime.Now; investorContact.Contact.FirstName = "n/a"; investorContact.Contact.LastName = "n/a"; investorContact.Contact.LastUpdatedBy = Authentication.CurrentUser.UserID; investorContact.Contact.LastUpdatedDate = DateTime.Now; investorContact.Contact.ReceivesDistributionNotices = DataTypeHelper.CheckBoolean(collection[(index + 1).ToString() + "_" + "DistributionNotices"]); investorContact.Contact.ReceivesFinancials = DataTypeHelper.CheckBoolean(collection[(index + 1).ToString() + "_" + "Financials"]); investorContact.Contact.ReceivesInvestorLetters = DataTypeHelper.CheckBoolean(collection[(index + 1).ToString() + "_" + "InvestorLetters"]); investorContact.Contact.ReceivesK1 = DataTypeHelper.CheckBoolean(collection[(index + 1).ToString() + "_" + "K1"]); investorContact.Contact.Designation = collection[(index + 1).ToString() + "_" + "Designation"]; investorContact.Contact.EntityID = Authentication.CurrentEntity.EntityID; // Attempt to create new investor contact address. contactAddress = new ContactAddress(); contactAddress.CreatedBy = Authentication.CurrentUser.UserID; contactAddress.CreatedDate = DateTime.Now; contactAddress.EntityID = Authentication.CurrentEntity.EntityID; contactAddress.LastUpdatedBy = Authentication.CurrentUser.UserID; contactAddress.LastUpdatedDate = DateTime.Now; contactAddress.Address = new Address(); contactAddress.Address.Address1 = Convert.ToString(collection[(index + 1).ToString() + "_" + "ContactAddress1"]); contactAddress.Address.Address2 = Convert.ToString(collection[(index + 1).ToString() + "_" + "ContactAddress2"]); contactAddress.Address.AddressTypeID = (int)DeepBlue.Models.Admin.Enums.AddressType.Work; contactAddress.Address.City = Convert.ToString(collection[(index + 1).ToString() + "_" + "ContactCity"]); contactAddress.Address.Country = Convert.ToInt32(collection[(index + 1).ToString() + "_" + "ContactCountry"]); contactAddress.Address.CreatedBy = Authentication.CurrentUser.UserID; contactAddress.Address.CreatedDate = DateTime.Now; contactAddress.Address.EntityID = Authentication.CurrentEntity.EntityID; contactAddress.Address.LastUpdatedBy = Authentication.CurrentUser.UserID; contactAddress.Address.LastUpdatedDate = DateTime.Now; contactAddress.Address.PostalCode = collection[(index + 1).ToString() + "_" + "ContactZip"]; contactAddress.Address.State = Convert.ToInt32(collection[(index + 1).ToString() + "_" + "ContactState"]); /* Add Investor Contact Communication Values */ string contactPhoneNo = collection[(index + 1).ToString() + "_" + "ContactPhoneNumber"]; string contactFaxNo = collection[(index + 1).ToString() + "_" + "ContactFaxNumber"]; string contactEmail = collection[(index + 1).ToString() + "_" + "ContactEmail"]; string contactWebAddress = collection[(index + 1).ToString() + "_" + "ContactWebAddress"]; if (string.IsNullOrEmpty(contactAddress.Address.Address1) == false || string.IsNullOrEmpty(contactAddress.Address.Address2) == false || string.IsNullOrEmpty(contactAddress.Address.City) == false || string.IsNullOrEmpty(contactAddress.Address.PostalCode) == false || string.IsNullOrEmpty(investorContact.Contact.ContactName) == false || string.IsNullOrEmpty(contactPhoneNo) == false || string.IsNullOrEmpty(contactFaxNo) == false || string.IsNullOrEmpty(contactEmail) == false || string.IsNullOrEmpty(contactWebAddress) == false ) { errorInfo = ValidationHelper.Validate(contactAddress.Address); errorInfo = errorInfo.Union(ValidationHelper.Validate(investorContact.Contact)); errorTitle = "<b>Contact Information {0}:</b>\n{1}\n"; errors = new StringBuilder(); if (errorInfo.Any()) { errors.Append(ValidationHelper.GetErrorInfo(errorInfo)); } if (emailValidation.IsValid(contactEmail) == false) errors.Append("Invalid Email\n"); if (webAttribute.IsValid(contactWebAddress) == false) errors.Append("Invalid Web Address\n"); if (zipAttribute.IsValid(contactAddress.Address.PostalCode) == false) errors.Append("Invalid Zip\n"); if (string.IsNullOrEmpty(errors.ToString()) == false) { resultModel.Result += string.Format(errorTitle, count.ToString(), errors.ToString()); } if (string.IsNullOrEmpty(resultModel.Result)) { investorContact.Contact.ContactAddresses.Add(contactAddress); } if (string.IsNullOrEmpty(resultModel.Result)) { investor.InvestorContacts.Add(investorContact); AddCommunication(investorContact.Contact, Models.Admin.Enums.CommunicationType.HomePhone, contactPhoneNo); AddCommunication(investorContact.Contact, Models.Admin.Enums.CommunicationType.Fax, contactFaxNo); AddCommunication(investorContact.Contact, Models.Admin.Enums.CommunicationType.Email, contactEmail); AddCommunication(investorContact.Contact, Models.Admin.Enums.CommunicationType.WebAddress, contactWebAddress); } } } if (string.IsNullOrEmpty(resultModel.Result)) { errorInfo = InvestorRepository.SaveInvestor(investor); if (errorInfo != null) { resultModel.Result = ValidationHelper.GetErrorInfo(errorInfo); } else { resultModel.Result += SaveCustomValues(collection, investor.InvestorID); } } if (string.IsNullOrEmpty(resultModel.Result)) { resultModel.Result = "True||" + investor.InvestorID; } } if (ModelState.IsValid == false) { foreach (var values in ModelState.Values.ToList()) { foreach (var err in values.Errors.ToList()) { if (string.IsNullOrEmpty(err.ErrorMessage) == false) { resultModel.Result += err.ErrorMessage + "\n"; } } } } return View("Result", resultModel); }
public ActionResult ImportInvestorContactExcel(FormCollection collection) { ImportInvestorContactExcelModel model = new ImportInvestorContactExcelModel(); ResultModel resultModel = new ResultModel(); MemoryCacheManager cacheManager = new MemoryCacheManager(); int totalPages = 0; int totalRows = 0; int completedRows = 0; int? succssRows = 0; int? errorRows = 0; this.TryUpdateModel(model); if (ModelState.IsValid) { string key = string.Format(EXCELINVESTORCONTACTERROR_BY_KEY, model.SessionKey); List<DeepBlue.Models.Deal.ImportExcelError> errors = cacheManager.Get(key, () => { return new List<DeepBlue.Models.Deal.ImportExcelError>(); }); DataSet ds = ExcelConnection.ImportExcelDataset(model.SessionKey); if (ds != null) { PagingDataTable importExcelTable = null; if (ds.Tables[model.InvestorContactTableName] != null) { importExcelTable = (PagingDataTable)ds.Tables[model.InvestorContactTableName]; } if (importExcelTable != null) { importExcelTable.PageSize = model.PageSize; PagingDataTable table = importExcelTable.Skip(model.PageIndex); totalPages = importExcelTable.TotalPages; totalRows = importExcelTable.TotalRows; if (totalPages > model.PageIndex) { completedRows = (model.PageIndex * importExcelTable.PageSize); } else { completedRows = totalRows; } int rowNumber = 0; string investorName = string.Empty; string contactPerson = string.Empty; string designation = string.Empty; string telephone = string.Empty; string fax = string.Empty; string email = string.Empty; string webAddress = string.Empty; string address = string.Empty; string city = string.Empty; string stateName = string.Empty; string zip = string.Empty; string countryName = string.Empty; bool receivesDistributionCapitalCallNotices = false; bool financials = false; bool k1 = false; bool investorLetters = false; COUNTRY country = null; STATE state = null; DeepBlue.Models.Deal.ImportExcelError error; DeepBlue.Models.Entity.Investor investor; EmailAttribute emailValidation = new EmailAttribute(); ZipAttribute zipAttribute = new ZipAttribute(); WebAddressAttribute webAttribute = new WebAddressAttribute(); IEnumerable<ErrorInfo> errorInfo; StringBuilder rowErrors; foreach (DataRow row in table.Rows) { int.TryParse(row.GetValue("RowNumber"), out rowNumber); error = new DeepBlue.Models.Deal.ImportExcelError { RowNumber = rowNumber }; rowErrors = new StringBuilder(); investorName = row.GetValue(model.InvestorName); contactPerson = row.GetValue(model.ContactPerson); designation = row.GetValue(model.Designation); telephone = row.GetValue(model.Telephone); fax = row.GetValue(model.Fax); email = row.GetValue(model.Email); webAddress = row.GetValue(model.WebAddress); address = row.GetValue(model.Address); city = row.GetValue(model.City); stateName = row.GetValue(model.State); zip = row.GetValue(model.Zip); countryName = row.GetValue(model.Country); bool.TryParse(row.GetValue(model.ReceivesDistributionCapitalCallNotices), out receivesDistributionCapitalCallNotices); bool.TryParse(row.GetValue(model.Financials), out financials); bool.TryParse(row.GetValue(model.InvestorLetters), out investorLetters); bool.TryParse(row.GetValue(model.K1), out k1); investor = null; country = null; state = null; if (string.IsNullOrEmpty(investorName) == false) { investor = InvestorRepository.FindInvestor(investorName); } if (investor == null) { error.Errors.Add(new ErrorInfo(model.InvestorName, "Investor does not exist")); } else { if (string.IsNullOrEmpty(countryName) == false) { country = AdminRepository.FindCountry(countryName); } if (string.IsNullOrEmpty(stateName) == false) { state = AdminRepository.FindState(stateName); } // Attempt to create new investor contact. InvestorContact investorContact = InvestorRepository.FindInvestorContact(investor.InvestorID, contactPerson, designation, receivesDistributionCapitalCallNotices, financials, k1, investorLetters ); if (investorContact == null) { investorContact = new InvestorContact(); investorContact.CreatedBy = Authentication.CurrentUser.UserID; investorContact.CreatedDate = DateTime.Now; } else { error.Errors.Add(new ErrorInfo(model.InvestorName, "Investor contact already exist")); } if (error.Errors.Count() == 0) { investorContact.InvestorID = investor.InvestorID; investorContact.EntityID = Authentication.CurrentEntity.EntityID; investorContact.LastUpdatedBy = Authentication.CurrentUser.UserID; investorContact.LastUpdatedDate = DateTime.Now; investorContact.Contact = new Contact(); investorContact.Contact.CreatedBy = Authentication.CurrentUser.UserID; investorContact.Contact.CreatedDate = DateTime.Now; investorContact.Contact.ContactName = contactPerson; investorContact.Contact.FirstName = "n/a"; investorContact.Contact.LastName = "n/a"; investorContact.Contact.LastUpdatedBy = Authentication.CurrentUser.UserID; investorContact.Contact.LastUpdatedDate = DateTime.Now; investorContact.Contact.ReceivesDistributionNotices = receivesDistributionCapitalCallNotices; investorContact.Contact.ReceivesFinancials = financials; investorContact.Contact.ReceivesInvestorLetters = investorLetters; investorContact.Contact.ReceivesK1 = k1; investorContact.Contact.Designation = designation; investorContact.Contact.EntityID = Authentication.CurrentEntity.EntityID; // Attempt to create new investor contact address. ContactAddress contactAddress = null; contactAddress = new ContactAddress(); contactAddress.CreatedBy = Authentication.CurrentUser.UserID; contactAddress.CreatedDate = DateTime.Now; contactAddress.EntityID = Authentication.CurrentEntity.EntityID; contactAddress.LastUpdatedBy = Authentication.CurrentUser.UserID; contactAddress.LastUpdatedDate = DateTime.Now; contactAddress.Address = new Address(); contactAddress.Address.CreatedBy = Authentication.CurrentUser.UserID; contactAddress.Address.CreatedDate = DateTime.Now; contactAddress.Address.Address1 = address; contactAddress.Address.AddressTypeID = (int)DeepBlue.Models.Admin.Enums.AddressType.Work; contactAddress.Address.City = city; contactAddress.Address.Country = (country != null ? country.CountryID : 0); contactAddress.Address.EntityID = Authentication.CurrentEntity.EntityID; contactAddress.Address.LastUpdatedBy = Authentication.CurrentUser.UserID; contactAddress.Address.LastUpdatedDate = DateTime.Now; contactAddress.Address.PostalCode = zip; contactAddress.Address.State = (state != null ? state.StateID : 0); /* Add Investor Contact Communication Values */ if (string.IsNullOrEmpty(contactAddress.Address.Address1) == false || string.IsNullOrEmpty(contactAddress.Address.Address2) == false || string.IsNullOrEmpty(contactAddress.Address.City) == false || string.IsNullOrEmpty(contactAddress.Address.PostalCode) == false || string.IsNullOrEmpty(investorContact.Contact.ContactName) == false || string.IsNullOrEmpty(fax) == false || string.IsNullOrEmpty(email) == false || string.IsNullOrEmpty(webAddress) == false ) { errorInfo = ValidationHelper.Validate(contactAddress.Address); errorInfo = errorInfo.Union(ValidationHelper.Validate(investorContact.Contact)); if (errorInfo.Any()) { error.Errors.Add(new ErrorInfo(model.InvestorName, ValidationHelper.GetErrorInfo(errorInfo))); } if (emailValidation.IsValid(email) == false) error.Errors.Add(new ErrorInfo(model.Email, "Invalid Email")); if (webAttribute.IsValid(webAddress) == false) error.Errors.Add(new ErrorInfo(model.WebAddress, "Invalid Web Address")); if (zipAttribute.IsValid(contactAddress.Address.PostalCode) == false) error.Errors.Add(new ErrorInfo(model.Zip, "Invalid Zip")); if (error.Errors.Count() == 0) { investorContact.Contact.ContactAddresses.Add(contactAddress); AddCommunication(investorContact.Contact, Models.Admin.Enums.CommunicationType.HomePhone, telephone); AddCommunication(investorContact.Contact, Models.Admin.Enums.CommunicationType.Fax, fax); AddCommunication(investorContact.Contact, Models.Admin.Enums.CommunicationType.Email, email); AddCommunication(investorContact.Contact, Models.Admin.Enums.CommunicationType.WebAddress, webAddress); } } if (error.Errors.Count() == 0) { errorInfo = InvestorRepository.SaveInvestorContact(investorContact); if (errorInfo != null) { error.Errors.Add(new ErrorInfo(model.InvestorName, ValidationHelper.GetErrorInfo(errorInfo))); } } } } StringBuilder sberror = new StringBuilder(); foreach (var e in error.Errors) { sberror.AppendFormat("{0},", e.ErrorMessage); } importExcelTable.AddError(rowNumber - 1, sberror.ToString()); errors.Add(error); } } } if (errors != null) { succssRows = errors.Where(e => e.Errors.Count == 0).Count(); errorRows = errors.Where(e => e.Errors.Count > 0).Count(); } } else { foreach (var values in ModelState.Values.ToList()) { foreach (var err in values.Errors.ToList()) { if (string.IsNullOrEmpty(err.ErrorMessage) == false) { resultModel.Result += err.ErrorMessage + "\n"; } } } } return Json(new { Result = resultModel.Result, TotalRows = totalRows, CompletedRows = completedRows, TotalPages = totalPages, PageIndex = model.PageIndex, SuccessRows = succssRows, ErrorRows = errorRows }); }
public ActionResult ImportInvestorExcel(FormCollection collection) { ImportInvestorExcelModel model = new ImportInvestorExcelModel(); ResultModel resultModel = new ResultModel(); MemoryCacheManager cacheManager = new MemoryCacheManager(); int totalPages = 0; int totalRows = 0; int completedRows = 0; int? succssRows = 0; int? errorRows = 0; this.TryUpdateModel(model); if (ModelState.IsValid) { string key = string.Format(EXCELINVESTORERROR_BY_KEY, model.SessionKey); List<DeepBlue.Models.Deal.ImportExcelError> errors = cacheManager.Get(key, () => { return new List<DeepBlue.Models.Deal.ImportExcelError>(); }); DataSet ds = ExcelConnection.ImportExcelDataset(model.SessionKey); if (ds != null) { PagingDataTable importExcelTable = null; if (ds.Tables[model.InvestorTableName] != null) { importExcelTable = (PagingDataTable)ds.Tables[model.InvestorTableName]; } if (importExcelTable != null) { importExcelTable.PageSize = model.PageSize; PagingDataTable table = importExcelTable.Skip(model.PageIndex); totalPages = importExcelTable.TotalPages; totalRows = importExcelTable.TotalRows; if (totalPages > model.PageIndex) { completedRows = (model.PageIndex * importExcelTable.PageSize); } else { completedRows = totalRows; } int rowNumber = 0; string investorName = string.Empty; string displayName = string.Empty; string socialSecurityID = string.Empty; bool domesticForeign = false; string stateOfResidencyName = string.Empty; string entityType = string.Empty; string source = string.Empty; bool foia = false; bool erisa = false; string notes = string.Empty; string phone = string.Empty; string fax = string.Empty; string email = string.Empty; string webAddress = string.Empty; string address1 = string.Empty; string address2 = string.Empty; string city = string.Empty; string stateName = string.Empty; string zip = string.Empty; string countryName = string.Empty; Models.Entity.InvestorEntityType investorEntityType; Models.Entity.STATE stateOfResidency; Models.Entity.COUNTRY country; Models.Entity.STATE state; DeepBlue.Models.Deal.ImportExcelError error; EmailAttribute emailValidation = new EmailAttribute(); ZipAttribute zipAttribute = new ZipAttribute(); WebAddressAttribute webAttribute = new WebAddressAttribute(); IEnumerable<ErrorInfo> errorInfo; StringBuilder rowErrors; foreach (DataRow row in table.Rows) { int.TryParse(row.GetValue("RowNumber"), out rowNumber); error = new DeepBlue.Models.Deal.ImportExcelError { RowNumber = rowNumber }; rowErrors = new StringBuilder(); investorName = row.GetValue(model.InvestorName); displayName = row.GetValue(model.DisplayName); socialSecurityID = row.GetValue(model.SocialSecurityID); domesticForeign = row.GetValue(model.DomesticForeign).ToLower() == "domestic"; stateOfResidencyName = row.GetValue(model.StateOfResidency); entityType = row.GetValue(model.EntityType); source = row.GetValue(model.Source); bool.TryParse(row.GetValue(model.FOIA), out foia); bool.TryParse(row.GetValue(model.ERISA), out erisa); phone = row.GetValue(model.Phone); fax = row.GetValue(model.Fax); email = row.GetValue(model.Email); webAddress = row.GetValue(model.WebAddress); address1 = row.GetValue(model.Address1); address2 = row.GetValue(model.Address2); city = row.GetValue(model.City); stateName = row.GetValue(model.State); zip = row.GetValue(model.Zip); countryName = row.GetValue(model.Country); investorEntityType = null; stateOfResidency = null; state = null; country = null; DeepBlue.Models.Entity.Investor investor = InvestorRepository.FindInvestor(investorName); if (investor != null) { error.Errors.Add(new ErrorInfo(model.InvestorName, "Investor already exist")); } else { investor = new Models.Entity.Investor(); if (string.IsNullOrEmpty(entityType) == false) { investorEntityType = AdminRepository.FindInvestorEntityType(entityType); } if (string.IsNullOrEmpty(stateOfResidencyName) == false) { stateOfResidency = AdminRepository.FindState(stateOfResidencyName); } if (string.IsNullOrEmpty(countryName) == false) { country = AdminRepository.FindCountry(countryName); } if (string.IsNullOrEmpty(stateName) == false) { state = AdminRepository.FindState(stateName); } investor.Alias = displayName; investor.IsDomestic = domesticForeign; investor.InvestorEntityTypeID = (investorEntityType != null ? investorEntityType.InvestorEntityTypeID : 0); investor.InvestorName = investorName; investor.FirstName = displayName; investor.ResidencyState = (stateOfResidency != null ? stateOfResidency.StateID : 0); investor.Social = socialSecurityID; investor.Notes = notes; investor.TaxID = 0; investor.FirstName = string.Empty; investor.LastName = "n/a"; investor.ManagerName = string.Empty; investor.MiddleName = string.Empty; investor.PrevInvestorID = 0; investor.CreatedBy = Authentication.CurrentUser.UserID; investor.CreatedDate = DateTime.Now; investor.LastUpdatedBy = Authentication.CurrentUser.UserID; investor.LastUpdatedDate = DateTime.Now; investor.EntityID = Authentication.CurrentEntity.EntityID; investor.TaxExempt = false; investor.Source = source; investor.ERISA = erisa; investor.FOIA = foia; // Attempt to create new investor address. InvestorAddress investorAddress = new InvestorAddress(); investorAddress.CreatedBy = Authentication.CurrentUser.UserID; investorAddress.CreatedDate = DateTime.Now; investorAddress.EntityID = Authentication.CurrentEntity.EntityID; investorAddress.LastUpdatedBy = Authentication.CurrentUser.UserID; investorAddress.LastUpdatedDate = DateTime.Now; investorAddress.Address = new Address(); investorAddress.Address.Address1 = address1; investorAddress.Address.Address2 = address2; investorAddress.Address.AddressTypeID = (int)DeepBlue.Models.Admin.Enums.AddressType.Work; investorAddress.Address.City = city; investorAddress.Address.Country = (country != null ? country.CountryID : 0); investorAddress.Address.CreatedBy = Authentication.CurrentUser.UserID; investorAddress.Address.CreatedDate = DateTime.Now; investorAddress.Address.LastUpdatedBy = Authentication.CurrentUser.UserID; investorAddress.Address.LastUpdatedDate = DateTime.Now; investorAddress.Address.EntityID = Authentication.CurrentEntity.EntityID; investorAddress.Address.PostalCode = zip; investorAddress.Address.State = (state != null ? state.StateID : 0); if (string.IsNullOrEmpty(investorAddress.Address.Address1) == false || string.IsNullOrEmpty(investorAddress.Address.Address2) == false || string.IsNullOrEmpty(investorAddress.Address.City) == false || string.IsNullOrEmpty(investorAddress.Address.PostalCode) == false || string.IsNullOrEmpty(investorAddress.Address.County) == false || string.IsNullOrEmpty(phone) == false || string.IsNullOrEmpty(email) == false || string.IsNullOrEmpty(webAddress) == false || string.IsNullOrEmpty(fax) == false ) { errorInfo = ValidationHelper.Validate(investorAddress.Address); if (errorInfo.Any()) { error.Errors.Add(new ErrorInfo(model.Address1, ValidationHelper.GetErrorInfo(errorInfo))); } if (emailValidation.IsValid(email) == false) error.Errors.Add(new ErrorInfo(model.Email, "Invalid Email")); if (zipAttribute.IsValid(zip) == false) error.Errors.Add(new ErrorInfo(model.Email, "Invalid Zip")); if (webAttribute.IsValid(webAddress) == false) error.Errors.Add(new ErrorInfo(model.Email, "Invalid Web Address")); if (error.Errors.Count() == 0) { /* Add New Investor Address */ investor.InvestorAddresses.Add(investorAddress); /* Investor Communication Values */ AddCommunication(investor, Models.Admin.Enums.CommunicationType.HomePhone, phone); AddCommunication(investor, Models.Admin.Enums.CommunicationType.Email, email); AddCommunication(investor, Models.Admin.Enums.CommunicationType.WebAddress, webAddress); AddCommunication(investor, Models.Admin.Enums.CommunicationType.Fax, fax); } } if (error.Errors.Count() == 0) { errorInfo = InvestorRepository.SaveInvestor(investor); if (errorInfo != null) { error.Errors.Add(new ErrorInfo(model.InvestorName, ValidationHelper.GetErrorInfo(errorInfo))); } } } StringBuilder sberror = new StringBuilder(); foreach (var e in error.Errors) { sberror.AppendFormat("{0},", e.ErrorMessage); } importExcelTable.AddError(rowNumber - 1, sberror.ToString()); errors.Add(error); } } } if (errors != null) { succssRows = errors.Where(e => e.Errors.Count == 0).Count(); errorRows = errors.Where(e => e.Errors.Count > 0).Count(); } } else { foreach (var values in ModelState.Values.ToList()) { foreach (var err in values.Errors.ToList()) { if (string.IsNullOrEmpty(err.ErrorMessage) == false) { resultModel.Result += err.ErrorMessage + "\n"; } } } } return Json(new { Result = resultModel.Result, TotalRows = totalRows, CompletedRows = completedRows, TotalPages = totalPages, PageIndex = model.PageIndex, SuccessRows = succssRows, ErrorRows = errorRows }); }