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 CreateManualCapitalCall(FormCollection collection)
{
CreateCapitalCallModel model=new CreateCapitalCallModel();
ResultModel resultModel=new ResultModel();
List<InvestorFund> investorFunds=new List<InvestorFund>();
this.TryUpdateModel(model,collection);
if(ModelState.IsValid) {
// Attempt to create manual capital call.
Models.Entity.CapitalCall capitalCall=new Models.Entity.CapitalCall();
CapitalCallLineItem item;
capitalCall.CapitalAmountCalled=model.CapitalAmountCalled;
capitalCall.CapitalCallDate=model.CapitalCallDate;
capitalCall.CapitalCallDueDate=model.CapitalCallDueDate;
capitalCall.CapitalCallTypeID=(int)Models.CapitalCall.Enums.CapitalCallType.Manual;
capitalCall.CreatedBy=Authentication.CurrentUser.UserID;
capitalCall.CreatedDate=DateTime.Now;
capitalCall.LastUpdatedBy=Authentication.CurrentUser.UserID;
capitalCall.LastUpdatedDate=DateTime.Now;
capitalCall.ExistingInvestmentAmount=model.ExistingInvestmentAmount??0;
capitalCall.NewInvestmentAmount=model.NewInvestmentAmount;
capitalCall.FundID=model.FundId;
capitalCall.InvestmentAmount=model.InvestedAmount??0;
capitalCall.InvestedAmountInterest=model.InvestedAmountInterest??0;
capitalCall.FundExpenses=model.FundExpenses??0;
capitalCall.ManagementFees=model.ManagementFees??0;
capitalCall.ManagementFeeInterest=model.ManagementFeeInterest??0;
capitalCall.CapitalCallNumber=Convert.ToString(CapitalCallRepository.FindCapitalCallNumber(model.FundId));
int index;
for(index=1;index<model.InvestorCount+1;index++) {
item=new CapitalCallLineItem();
item.CreatedBy=Authentication.CurrentUser.UserID;
item.CreatedDate=DateTime.Now;
item.LastUpdatedBy=Authentication.CurrentUser.UserID;
item.LastUpdatedDate=DateTime.Now;
item.CapitalAmountCalled=DataTypeHelper.ToDecimal(collection[index.ToString()+"_"+"CapitalAmountCalled"]);
item.ManagementFeeInterest=DataTypeHelper.ToDecimal(collection[index.ToString()+"_"+"ManagementFeeInterest"]);
item.InvestedAmountInterest=DataTypeHelper.ToDecimal(collection[index.ToString()+"_"+"InvestedAmountInterest"]);
item.ManagementFees=DataTypeHelper.ToDecimal(collection[index.ToString()+"_"+"ManagementFees"]);
item.FundExpenses=DataTypeHelper.ToDecimal(collection[index.ToString()+"_"+"FundExpenses"]);
item.InvestorID=DataTypeHelper.ToInt32(collection[index.ToString()+"_"+"InvestorId"]);
if(item.InvestorID>0) {
InvestorFund investorFund=InvestorRepository.FindInvestorFund(item.InvestorID,capitalCall.FundID);
if(investorFund!=null) {
// Reduce investor unfunded amount = investor unfunded amount – capital call amount for investor.
investorFund.UnfundedAmount=investorFund.UnfundedAmount-item.CapitalAmountCalled;
investorFunds.Add(investorFund);
}
capitalCall.CapitalCallLineItems.Add(item);
}
}
if(capitalCall.CapitalCallLineItems.Count==0) {
ModelState.AddModelError("InvestorCount","Select any one investor");
} else {
IEnumerable<ErrorInfo> errorInfo=CapitalCallRepository.SaveCapitalCall(capitalCall);
if(errorInfo!=null) {
resultModel.Result+=ValidationHelper.GetErrorInfo(errorInfo);
} else {
foreach(var investorFund in investorFunds) {
errorInfo=InvestorRepository.SaveInvestorFund(investorFund);
resultModel.Result+=ValidationHelper.GetErrorInfo(errorInfo);
}
}
if(string.IsNullOrEmpty(resultModel.Result)) {
resultModel.Result+="True||"+(CapitalCallRepository.FindCapitalCallNumber(model.FundId));
}
}
}
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 ImportCapitalCall(FormCollection collection)
{
Models.Entity.CapitalCall capitalCall=new Models.Entity.CapitalCall();
ResultModel resultModel=new ResultModel();
this.TryUpdateModel(capitalCall,collection);
capitalCall.CreatedBy=Authentication.CurrentUser.UserID;
capitalCall.CreatedDate=DateTime.Now;
capitalCall.LastUpdatedBy=Authentication.CurrentUser.UserID;
capitalCall.LastUpdatedDate=DateTime.Now;
capitalCall.CapitalCallTypeID=(int)Models.CapitalCall.Enums.CapitalCallType.Manual;
capitalCall.CapitalCallNumber=Convert.ToString(CapitalCallRepository.FindCapitalCallNumber(capitalCall.FundID));
IEnumerable<ErrorInfo> errorInfo=CapitalCallRepository.SaveCapitalCall(capitalCall);
if(errorInfo==null) {
resultModel.Result+="True||"+capitalCall.CapitalCallID;
} else {
resultModel.Result=ValidationHelper.GetErrorInfo(errorInfo);
}
return View("Result",resultModel);
}
public ActionResult Create(FormCollection collection)
{
CreateCapitalCallModel model=new CreateCapitalCallModel();
ResultModel resultModel=new ResultModel();
this.TryUpdateModel(model,collection);
if((model.AddManagementFees??false)==true) {
DateTime fromDate=(model.FromDate??Convert.ToDateTime("01/01/1900"));
DateTime toDate=(model.ToDate??Convert.ToDateTime("01/01/1900"));
if(fromDate.Year<=1900)
ModelState.AddModelError("FromDate","From Date is required");
if(toDate.Year<=1900)
ModelState.AddModelError("ToDate","To Date is required");
if(fromDate.Year>1900&&toDate.Year>1900) {
if(fromDate.Subtract(toDate).Days>=0) {
ModelState.AddModelError("ToDate","To Date must be greater than From Date");
}
}
if((model.ManagementFees??0)<=1) {
ModelState.AddModelError("ManagementFees","Fee Amount is required");
}
}
if(ModelState.IsValid) {
// Attempt to create capital call.
Models.Entity.CapitalCall capitalCall=new Models.Entity.CapitalCall();
CapitalCallLineItem item;
capitalCall.CapitalAmountCalled=model.CapitalAmountCalled;
capitalCall.CapitalCallDate=model.CapitalCallDate;
capitalCall.CapitalCallDueDate=model.CapitalCallDueDate;
capitalCall.CapitalCallNumber=string.Empty;
capitalCall.CapitalCallTypeID=(int)Models.CapitalCall.Enums.CapitalCallType.Reqular;
capitalCall.CreatedBy=Authentication.CurrentUser.UserID;
capitalCall.CreatedDate=DateTime.Now;
capitalCall.LastUpdatedBy=Authentication.CurrentUser.UserID;
capitalCall.LastUpdatedDate=DateTime.Now;
capitalCall.ExistingInvestmentAmount=model.ExistingInvestmentAmount??0;
capitalCall.NewInvestmentAmount=model.NewInvestmentAmount;
capitalCall.FundID=model.FundId;
capitalCall.CapitalCallNumber=Convert.ToString(CapitalCallRepository.FindCapitalCallNumber(model.FundId));
capitalCall.InvestmentAmount=(capitalCall.NewInvestmentAmount??0)+(capitalCall.ExistingInvestmentAmount??0);
List<InvestorFund> investorFunds=CapitalCallRepository.GetAllInvestorFunds(capitalCall.FundID);
if(investorFunds!=null) {
// Find non managing total commitment.
decimal nonManagingMemberTotalCommitment=investorFunds.Where(fund => fund.InvestorTypeID==(int)DeepBlue.Models.Investor.Enums.InvestorType.NonManagingMember).Sum(fund => fund.TotalCommitment);
// Find managing total commitment.
decimal managingMemberTotalCommitment=investorFunds.Where(fund => fund.InvestorTypeID==(int)DeepBlue.Models.Investor.Enums.InvestorType.ManagingMember).Sum(fund => fund.TotalCommitment);
// Calculate managing total commitment.
decimal totalCommitment=nonManagingMemberTotalCommitment+managingMemberTotalCommitment;
if((model.AddFundExpenses??false)==true) {
capitalCall.FundExpenses=model.FundExpenseAmount??0;
} else {
capitalCall.FundExpenses=0;
}
if((model.AddManagementFees??false)==true) {
capitalCall.ManagementFeeStartDate=model.FromDate;
capitalCall.ManagementFeeEndDate=model.ToDate;
capitalCall.ManagementFees=model.ManagementFees;
} else {
capitalCall.ManagementFees=0;
}
// Check new investment amount and existing investment amount.
decimal investmentAmount=(capitalCall.NewInvestmentAmount??0)+(capitalCall.ExistingInvestmentAmount??0);
decimal capitalAmount=capitalCall.CapitalAmountCalled-(capitalCall.ManagementFees??0)-(capitalCall.FundExpenses??0);
if(((decimal.Round(investmentAmount)==decimal.Round(capitalAmount))==false)) {
ModelState.AddModelError("NewInvestmentAmount","(New Investment Amount + Existing Investment Amount) should be equal to (Capital Amount - Management Fees - Fund Expenses).");
} else {
foreach(var investorFund in investorFunds) {
// Attempt to create capital call line item for each investor fund.
item=new CapitalCallLineItem();
item.CreatedBy=Authentication.CurrentUser.UserID;
item.CreatedDate=DateTime.Now;
item.LastUpdatedBy=Authentication.CurrentUser.UserID;
item.LastUpdatedDate=DateTime.Now;
// Calculate Management Fees investor fund
if((model.AddManagementFees??false)==true) {
if(investorFund.InvestorTypeID==(int)DeepBlue.Models.Investor.Enums.InvestorType.NonManagingMember) {
item.ManagementFees=decimal.Multiply(
decimal.Divide(investorFund.TotalCommitment,nonManagingMemberTotalCommitment)
,(capitalCall.ManagementFees??0));
}
}
// Calculate Fund Expense for investor fund
if((model.AddFundExpenses??false)==true) {
item.FundExpenses=decimal.Multiply(
decimal.Divide(investorFund.TotalCommitment,totalCommitment)
,(capitalCall.FundExpenses??0));
}
// Calculate Capital Amount for investor fund
item.CapitalAmountCalled=CalculateCapitalAmountCalled(investorFund.TotalCommitment,
totalCommitment,
nonManagingMemberTotalCommitment,
capitalCall.CapitalAmountCalled,
(capitalCall.ManagementFees??0),
(DeepBlue.Models.Investor.Enums.InvestorType)investorFund.InvestorTypeID);
item.ExistingInvestmentAmount=(investorFund.TotalCommitment/totalCommitment)*capitalCall.ExistingInvestmentAmount;
item.NewInvestmentAmount=(investorFund.TotalCommitment/totalCommitment)*capitalCall.NewInvestmentAmount;
item.InvestmentAmount=(investorFund.TotalCommitment/totalCommitment)*capitalCall.InvestmentAmount;
item.InvestorID=investorFund.InvestorID;
// Reduce investor unfunded amount = investor unfunded amount - (capital call amount + management fees + fund expenses).
investorFund.UnfundedAmount=decimal.Subtract((investorFund.UnfundedAmount??0)
,
decimal.Add(item.CapitalAmountCalled,
decimal.Add(
(item.ManagementFees??0)
,(item.FundExpenses??0))
)
);
capitalCall.CapitalCallLineItems.Add(item);
}
IEnumerable<ErrorInfo> errorInfo=CapitalCallRepository.SaveCapitalCall(capitalCall);
if(errorInfo!=null) {
resultModel.Result+=ValidationHelper.GetErrorInfo(errorInfo);
} else {
foreach(var investorFund in investorFunds) {
errorInfo=InvestorRepository.SaveInvestorFund(investorFund);
resultModel.Result+=ValidationHelper.GetErrorInfo(errorInfo);
}
}
if(string.IsNullOrEmpty(resultModel.Result)) {
resultModel.Result+="True||"+(CapitalCallRepository.FindCapitalCallNumber(model.FundId))+"||"+capitalCall.CapitalCallID;
}
}
}
}
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);
}
