Ejemplo n.º 1
0
        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
            });
        }
Ejemplo n.º 2
0
        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
            });
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
        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
            });
        }
Ejemplo n.º 5
0
        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
            });
        }
Ejemplo n.º 6
0
        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
            });
        }