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 ImportInvestorBankExcel(FormCollection collection)
{
ImportInvestorBankExcelModel model = new ImportInvestorBankExcelModel();
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(EXCELINVESTORBANKERROR_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.InvestorBankTableName] != null) {
importExcelTable = (PagingDataTable)ds.Tables[model.InvestorBankTableName];
}
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 bankName = string.Empty;
int abaNumber = 0;
string accountName = string.Empty;
string accountNumber = string.Empty;
string ffcName = string.Empty;
string ffcNumber = string.Empty;
string reference = string.Empty;
string swift = string.Empty;
string iban = string.Empty;
string phone = string.Empty;
string fax = string.Empty;
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);
bankName = row.GetValue(model.BankName);
int.TryParse(row.GetValue(model.ABANumber), out abaNumber);
accountName = row.GetValue(model.AccountName);
accountNumber = row.GetValue(model.AccountNumber);
ffcName = row.GetValue(model.FFCName);
ffcNumber = row.GetValue(model.FFCNumber);
reference = row.GetValue(model.Reference);
swift = row.GetValue(model.Swift);
iban = row.GetValue(model.IBAN);
phone = row.GetValue(model.Phone);
fax = row.GetValue(model.Fax);
DeepBlue.Models.Entity.Investor investor = InvestorRepository.FindInvestor(investorName);
if (investor == null) {
error.Errors.Add(new ErrorInfo(model.InvestorName, "Investor does not exist"));
}
else {
// Attempt to create new investor account.
InvestorAccount investorAccount = InvestorRepository.FindInvestorAccount(
investor.InvestorID,
bankName,
abaNumber,
accountName,
accountNumber,
ffcName,
ffcNumber,
reference,
swift,
iban,
phone,
fax
);
if (investorAccount == null) {
investorAccount = new InvestorAccount();
investorAccount.CreatedBy = Authentication.CurrentUser.UserID;
investorAccount.CreatedDate = DateTime.Now;
}
else {
error.Errors.Add(new ErrorInfo(model.InvestorName, "Investor Bank Account Information already exist"));
}
if (error.Errors.Count() == 0) {
investorAccount.Comments = string.Empty;
investorAccount.EntityID = Authentication.CurrentEntity.EntityID;
investorAccount.IsPrimary = false;
investorAccount.LastUpdatedBy = Authentication.CurrentUser.UserID;
investorAccount.LastUpdatedDate = DateTime.Now;
investorAccount.Routing = abaNumber;
investorAccount.Reference = reference;
investorAccount.FFC = ffcName;
investorAccount.FFCNumber = ffcNumber;
investorAccount.IBAN = iban;
investorAccount.SWIFT = swift;
investorAccount.Account = accountName;
investorAccount.AccountNumberCash = accountNumber;
investorAccount.BankName = bankName;
investorAccount.Phone = phone;
investorAccount.Fax = fax;
investorAccount.InvestorID = investor.InvestorID;
if (error.Errors.Count() == 0) {
errorInfo = InvestorRepository.SaveInvestorAccount(investorAccount);
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 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 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
});
}
public ActionResult ImportCapitalCallExcel(FormCollection collection)
{
ImportManualCapitalCallExcelModel model=new ImportManualCapitalCallExcelModel();
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(EXCELCAPITALCALLERROR_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.ManualCapitalCallTableName]!=null) {
importExcelTable=(PagingDataTable)ds.Tables[model.ManualCapitalCallTableName];
}
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 fundName=string.Empty;
decimal capitalCallAmount=0;
decimal managementFeesInterest=0;
decimal investedAmountInterest=0;
decimal managementFees=0;
decimal fundExpenses=0;
DateTime capitalCallDate;
DateTime capitalCallDueDate;
DeepBlue.Models.Deal.ImportExcelError error;
DeepBlue.Models.Entity.Investor investor;
DeepBlue.Models.Entity.Fund fund;
DeepBlue.Models.Entity.InvestorFund investorFund;
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);
fundName=row.GetValue(model.FundName);
decimal.TryParse(row.GetValue(model.CapitalCallAmount),out capitalCallAmount);
decimal.TryParse(row.GetValue(model.ManagementFeesInterest),out managementFeesInterest);
decimal.TryParse(row.GetValue(model.InvestedAmountInterest),out investedAmountInterest);
decimal.TryParse(row.GetValue(model.ManagementFees),out managementFees);
decimal.TryParse(row.GetValue(model.FundExpenses),out fundExpenses);
DateTime.TryParse(row.GetValue(model.CapitalCallDate),out capitalCallDate);
DateTime.TryParse(row.GetValue(model.CapitalCallDueDate),out capitalCallDueDate);
investor=null;
fund=null;
investorFund=null;
if(string.IsNullOrEmpty(investorName)==false) {
investor=InvestorRepository.FindInvestor(investorName);
} else {
error.Errors.Add(new ErrorInfo(model.InvestorName,"Investor is required"));
}
if(string.IsNullOrEmpty(fundName)==false) {
fund=FundRepository.FindFund(fundName);
} else {
error.Errors.Add(new ErrorInfo(model.InvestorName,"Fund is required"));
}
if(investor!=null&&fund!=null) {
investorFund=InvestorRepository.FindInvestorFund(investor.InvestorID,fund.FundID);
}
if(error.Errors.Count()==0) {
if(investorFund==null) {
error.Errors.Add(new ErrorInfo(model.InvestorName,"Investror Commitment does not exist"));
}
}
if(error.Errors.Count()==0) {
Models.Entity.CapitalCall capitalCall=CapitalCallRepository.FindCapitalCall(
(fund!=null?fund.FundID:0),
capitalCallDate,
capitalCallDueDate,
(int)Models.CapitalCall.Enums.CapitalCallType.Manual);
if(capitalCall==null) {
capitalCall=new Models.Entity.CapitalCall();
capitalCall.CreatedBy=Authentication.CurrentUser.UserID;
capitalCall.CreatedDate=DateTime.Now;
}
capitalCall.CapitalCallDate=capitalCallDate;
capitalCall.CapitalCallDueDate=capitalCallDueDate;
capitalCall.CapitalCallTypeID=(int)Models.CapitalCall.Enums.CapitalCallType.Manual;
capitalCall.LastUpdatedBy=Authentication.CurrentUser.UserID;
capitalCall.LastUpdatedDate=DateTime.Now;
capitalCall.FundID=(fund!=null?fund.FundID:0);
capitalCall.InvestedAmountInterest=0;
capitalCall.CapitalAmountCalled+=capitalCallAmount;
capitalCall.InvestedAmountInterest=(capitalCall.ExistingInvestmentAmount??0)+investedAmountInterest;
capitalCall.FundExpenses=(capitalCall.FundExpenses??0)+fundExpenses;
capitalCall.ManagementFees=(capitalCall.ManagementFees??0)+managementFees;
capitalCall.ManagementFeeInterest=(capitalCall.ManagementFeeInterest??0)+managementFeesInterest;
capitalCall.CapitalCallNumber=Convert.ToString(CapitalCallRepository.FindCapitalCallNumber((fund!=null?fund.FundID:0)));
if(error.Errors.Count()==0) {
errorInfo=CapitalCallRepository.SaveCapitalCall(capitalCall);
if(errorInfo!=null) {
error.Errors.Add(new ErrorInfo(model.InvestorName,ValidationHelper.GetErrorInfo(errorInfo)));
}
}
if(error.Errors.Count()==0) {
bool isNewItem=false;
CapitalCallLineItem item=CapitalCallRepository.FindCapitalCallLineItem(capitalCall.CapitalCallID,investor.InvestorID);
if(item==null) {
item=new CapitalCallLineItem();
item.CreatedBy=Authentication.CurrentUser.UserID;
item.CreatedDate=DateTime.Now;
isNewItem=true;
}
item.LastUpdatedBy=Authentication.CurrentUser.UserID;
item.LastUpdatedDate=DateTime.Now;
item.CapitalAmountCalled=capitalCallAmount;
item.ManagementFeeInterest=managementFeesInterest;
item.InvestedAmountInterest=investedAmountInterest;
item.ManagementFees=managementFees;
item.FundExpenses=fundExpenses;
item.InvestorID=investor.InvestorID;
item.CapitalCallID=capitalCall.CapitalCallID;
errorInfo=CapitalCallRepository.SaveCapitalCallLineItem(item);
if(errorInfo!=null) {
error.Errors.Add(new ErrorInfo(model.InvestorName,ValidationHelper.GetErrorInfo(errorInfo)));
} else {
if(isNewItem) {
if(item.InvestorID>0) {
if(investorFund!=null) {
// Reduce investor unfunded amount = investor unfunded amount – capital call amount for investor.
investorFund.UnfundedAmount=investorFund.UnfundedAmount-item.CapitalAmountCalled;
errorInfo=InvestorRepository.SaveInvestorFund(investorFund);
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 ImportCapitalDistributionExcel(FormCollection collection)
{
ImportManualCapitalDistributionExcelModel model=new ImportManualCapitalDistributionExcelModel();
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(EXCELCAPITALCALLERROR_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.ManualCapitalDistributionTableName]!=null) {
importExcelTable=(PagingDataTable)ds.Tables[model.ManualCapitalDistributionTableName];
}
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 fundName=string.Empty;
decimal capitalDistributionAmount=0;
decimal returnManagementFees=0;
decimal returnFundExpenses=0;
decimal costReturned=0;
decimal profits=0;
decimal profitsReturned=0;
DateTime distributionDate;
DateTime distributionDueDate;
DeepBlue.Models.Deal.ImportExcelError error;
DeepBlue.Models.Entity.Investor investor;
DeepBlue.Models.Entity.Fund fund;
DeepBlue.Models.Entity.InvestorFund investorFund;
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);
fundName=row.GetValue(model.FundName);
decimal.TryParse(row.GetValue(model.CapitalDistributionAmount),out capitalDistributionAmount);
decimal.TryParse(row.GetValue(model.ReturnManagementFees),out returnManagementFees);
decimal.TryParse(row.GetValue(model.ReturnFundExpenses),out returnFundExpenses);
decimal.TryParse(row.GetValue(model.CostReturned),out costReturned);
decimal.TryParse(row.GetValue(model.Profits),out profits);
decimal.TryParse(row.GetValue(model.ProfitsReturned),out profitsReturned);
DateTime.TryParse(row.GetValue(model.DistributionDate),out distributionDate);
DateTime.TryParse(row.GetValue(model.DistributionDueDate),out distributionDueDate);
investor=null;
fund=null;
investorFund=null;
if(string.IsNullOrEmpty(investorName)==false) {
investor=InvestorRepository.FindInvestor(investorName);
} else {
error.Errors.Add(new ErrorInfo(model.InvestorName,"Investor is required"));
}
if(string.IsNullOrEmpty(fundName)==false) {
fund=FundRepository.FindFund(fundName);
} else {
error.Errors.Add(new ErrorInfo(model.InvestorName,"Fund is required"));
}
if(investor!=null&&fund!=null) {
investorFund=InvestorRepository.FindInvestorFund(investor.InvestorID,fund.FundID);
}
if(error.Errors.Count()==0) {
if(investorFund==null) {
error.Errors.Add(new ErrorInfo(model.InvestorName,"Investror Commitment does not exist"));
}
}
if(error.Errors.Count()==0) {
Models.Entity.CapitalDistribution distribution=CapitalCallRepository.FindCapitalDistribution(
(fund!=null?fund.FundID:0),
distributionDate,
distributionDueDate,
true);
if(distribution==null) {
distribution=new Models.Entity.CapitalDistribution();
distribution.CreatedBy=Authentication.CurrentUser.UserID;
distribution.CreatedDate=DateTime.Now;
}
distribution.CapitalDistributionDate=distributionDate;
distribution.CapitalDistributionDueDate=distributionDueDate;
distribution.PreferredReturn=(distribution.PreferredReturn??0)+profitsReturned;
distribution.ReturnManagementFees=(distribution.ReturnManagementFees??0)+returnManagementFees;
distribution.ReturnFundExpenses=(distribution.ReturnFundExpenses??0)+returnFundExpenses;
distribution.Profits=(distribution.Profits??0)+profits;
distribution.DistributionAmount=distribution.DistributionAmount+capitalDistributionAmount;
distribution.LastUpdatedBy=Authentication.CurrentUser.UserID;
distribution.LastUpdatedDate=DateTime.Now;
distribution.DistributionNumber=Convert.ToString(CapitalCallRepository.FindCapitalCallDistributionNumber((fund!=null?fund.FundID:0)));
distribution.FundID=(fund!=null?fund.FundID:0);
distribution.CapitalReturn=(distribution.CapitalReturn??0)+costReturned;
distribution.IsManual=true;
if(error.Errors.Count()==0) {
errorInfo=CapitalCallRepository.SaveCapitalDistribution(distribution);
if(errorInfo!=null) {
error.Errors.Add(new ErrorInfo(model.InvestorName,ValidationHelper.GetErrorInfo(errorInfo)));
}
}
if(error.Errors.Count()==0) {
CapitalDistributionLineItem item=CapitalCallRepository.FindCapitalDistributionLineItem(distribution.CapitalDistributionID,investor.InvestorID);
if(item==null) {
item=new CapitalDistributionLineItem();
item.CreatedBy=Authentication.CurrentUser.UserID;
item.CreatedDate=DateTime.Now;
}
item.CapitalReturn=0;
item.CreatedBy=Authentication.CurrentUser.UserID;
item.CreatedDate=DateTime.Now;
item.LastUpdatedBy=Authentication.CurrentUser.UserID;
item.LastUpdatedDate=DateTime.Now;
item.InvestorID=(investor!=null?investor.InvestorID:0);
item.DistributionAmount=capitalDistributionAmount;
item.ReturnManagementFees=returnManagementFees;
item.ReturnFundExpenses=returnFundExpenses;
item.Profits=profits;
item.PreferredReturn=profitsReturned;
item.CapitalReturn=costReturned;
item.CapitalDistributionID=distribution.CapitalDistributionID;
errorInfo=CapitalCallRepository.SaveCapitalDistributionLineItem(item);
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
});
}
