public async Task Put([FromBody] FundInvestor fundInvestor)
{
using (var dataAccess = new MasterSideLetterDataAccess(_connectionStrings.MasterSideLetterDb))
{
await dataAccess.UpdateFundInvestorAsync(fundInvestor);
}
}
public async Task <int> Post([FromBody] FundInvestor fundInvestor)
{
using (var dataAccess = new MasterSideLetterDataAccess(_connectionStrings.MasterSideLetterDb))
{
return(await dataAccess.CreateFundInvestorAsync(fundInvestor));
}
}
public async Task InvestorBatchUpload(int investorId)
{
using (var dataAccess = new MasterSideLetterDataAccess(_connectionStrings.MasterSideLetterDb))
{
foreach (var file in Request.Form.Files)
{
var fileExtension = Path.GetExtension(file.FileName).ToLower();
if (fileExtension != ".doc" && fileExtension != ".docx")
{
throw new ArgumentException($"Invalid file type {fileExtension}.");
}
var fundInvestor = new FundInvestor {
InvestorId = investorId
};
fundInvestor.Id = await dataAccess.CreateFundInvestorAsync(fundInvestor);
using (var readStream = file.OpenReadStream())
{
await dataAccess.UpdateFundInvestorSideLetterAsync(fundInvestor.Id, file.FileName, readStream);
await dataAccess.DeleteProvisionsByFundInvestorAsync(fundInvestor.Id);
var paragraphs = fileExtension == ".docx" ? DocumentHelper.ExtractDocxParagraphs(readStream) : DocumentHelper.ExtractDocParagraphs(readStream);
foreach (var paragraph in paragraphs.Where(paragraph => !string.IsNullOrWhiteSpace(paragraph)))
{
await dataAccess.CreateProvisionAsync(new Provision { FundInvestorId = fundInvestor.Id, Content = paragraph });
}
}
}
}
}
public static async Task <int> UpdateFundInvestorAsync(this MasterSideLetterDataAccess dataAccess, FundInvestor fundInvestor)
{
await dataAccess.ValidateFundInvestorAsync(fundInvestor);
return(await dataAccess.ExecuteAsync(
@" update FundInvestor
set FundId = @FundId,
FundSponsorName = @FundSponsorName,
FundBusinessUnitName = @FundBusinessUnitName,
FundStrategyName = @FundStrategyName,
InvestorId = @InvestorId,
InvestorType = @InvestorType,
Entity = @Entity,
Commitment = @Commitment,
Counsel = @Counsel,
Notes = @Notes,
FundYear = @FundYear,
FundSize = @FundSize,
ModifiedDate = getdate()
where Id = @id", fundInvestor));
}
public static async Task <int> CreateFundInvestorAsync(this MasterSideLetterDataAccess dataAccess, FundInvestor fundInvestor)
{
await dataAccess.ValidateFundInvestorAsync(fundInvestor);
return(await dataAccess.ExecuteScalarAsync <int>(
@" insert FundInvestor(
FundId,
FundSponsorName,
FundBusinessUnitName,
FundStrategyName,
InvestorId,
InvestorType,
Entity,
Commitment,
Counsel,
Notes,
FundYear,
FundSize
) output inserted.Id
values(
@FundId,
@FundSponsorName,
@FundBusinessUnitName,
@FundStrategyName,
@InvestorId,
@InvestorType,
@Entity,
@Commitment,
@Counsel,
@Notes,
@FundYear,
@FundSize
)", fundInvestor));
}
private static async Task ValidateFundInvestorAsync(this MasterSideLetterDataAccess dataAccess, FundInvestor fundInvestor)
{
if (!string.IsNullOrEmpty(fundInvestor.FundName))
{
var fund = await dataAccess.GetOrCreateFundAsync(
fundInvestor.FundName,
fundInvestor.FundSponsorName,
fundInvestor.FundBusinessUnitName,
fundInvestor.FundStrategyName,
fundInvestor.FundYear,
fundInvestor.FundSize
);
var updated = false;
if (fund.SponsorName != fundInvestor.FundSponsorName)
{
updated = true;
var sponsor = await dataAccess.GetOrCreateSponsorAsync(fundInvestor.FundSponsorName);
fund.SponsorId = sponsor.Id;
fund.SponsorName = sponsor.Name;
}
if (fund.BusinessUnitName != fundInvestor.FundBusinessUnitName)
{
updated = true;
var businessUnit = await dataAccess.GetOrCreateBusinessUnitAsync(fundInvestor.FundBusinessUnitName);
fund.BusinessUnitId = businessUnit.Id;
fund.BusinessUnitName = businessUnit.Name;
}
if (fund.StrategyName != fundInvestor.FundStrategyName)
{
updated = true;
var strategy = await dataAccess.GetOrCreateStrategyAsync(fundInvestor.FundStrategyName);
fund.StrategyId = strategy.Id;
fund.StrategyName = strategy.Name;
}
if (fund.Year != fundInvestor.FundYear)
{
updated = true;
fund.Year = fundInvestor.FundYear;
}
if (fund.Size != fundInvestor.FundSize)
{
updated = true;
fund.Size = fundInvestor.FundSize;
}
if (updated)
{
await dataAccess.UpdateFundAsync(fund);
}
fundInvestor.FundId = fund.Id;
}
if (!string.IsNullOrEmpty(fundInvestor.InvestorName))
{
var investor = await dataAccess.GetOrCreateInvestorAsync(fundInvestor.InvestorName, fundInvestor.InvestorType);
if (investor.InvestorType != fundInvestor.InvestorType)
{
investor.InvestorType = fundInvestor.InvestorType;
await dataAccess.UpdateInvestorAsync(investor);
}
fundInvestor.InvestorId = investor.Id;
}
}
public static async Task <MasterSideLetterContent> GenerateMasterSideLetterContentAsync(this MasterSideLetterDataAccess dataAccess, int fundId, IList <int> fundInvestorIds)
{
var msl = new MasterSideLetterContent();
msl.Fund = await dataAccess.QueryFirstOrDefaultAsync <Fund>("select * from v_Fund where Id = @fundId", new { fundId });
var fundInvestorIndex = new Dictionary <int, FundInvestor>();
var provisions = await dataAccess.QueryAsync <Provision>("select * from v_Provision where FundInvestorId in @fundInvestorIds and ProvisionType is not null", new { fundInvestorIds });
var provisionTypeGroups = provisions.OrderBy(p => p.ProvisionType).GroupBy(p => p.ProvisionType);
foreach (var provisionTypeGroup in provisionTypeGroups)
{
var provisionTypeSection = new MasterSideLetterContent.ProvisionTypeSection
{
ProvisionType = provisionTypeGroup.Key
};
var contentGroups = provisionTypeGroup.GroupBy(c => NormalizeContent(c.Content)).OrderByDescending(g => g.Count());
foreach (var contentGroup in contentGroups)
{
var provisionSection = new MasterSideLetterContent.ProvisionSection
{
Content = contentGroup.First().Content
};
foreach (var provision in contentGroup)
{
FundInvestor fundInvestor;
if (fundInvestorIndex.ContainsKey(provision.FundInvestorId))
{
fundInvestor = fundInvestorIndex[provision.FundInvestorId];
}
else
{
fundInvestor = new FundInvestor
{
Id = provision.FundInvestorId,
FundId = provision.FundId,
FundName = provision.FundName,
FundSponsorName = provision.FundSponsorName,
FundBusinessUnitName = provision.FundBusinessUnitName,
FundStrategyName = provision.FundStrategyName,
FundYear = provision.FundYear,
FundSize = provision.FundSize,
InvestorId = provision.InvestorId,
InvestorName = provision.InvestorName,
InvestorType = provision.InvestorType,
Entity = provision.Entity,
Commitment = provision.Commitment,
Counsel = provision.Counsel,
Notes = provision.Notes,
SideLetterFileName = provision.SideLetterFileName
};
fundInvestorIndex.Add(fundInvestor.Id, fundInvestor);
}
provisionSection.FundInvestors.Add(fundInvestor);
}
provisionTypeSection.ProvisionSections.Add(provisionSection);
}
msl.ProvisionTypeSections.Add(provisionTypeSection);
}
msl.FundInvestors.AddRange(fundInvestorIndex.Values.OrderBy(fi => fi.InvestorName).ThenBy(fi => fi.Entity));
return(msl);
}
