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));
}
public static async Task <IEnumerable <Provision> > SearchProvisionsAsync(this MasterSideLetterDataAccess dataAccess, SearchRequest request)
{
var preparedQuery = GetProvisionSearchQuery(request);
var results = await dataAccess.QueryAsync <Provision>(preparedQuery.Sql.ToString(), preparedQuery.Parameters);
return(results);
}
public static async Task <IEnumerable <Fund> > GetFundsAsync(this MasterSideLetterDataAccess dataAccess, string userName, string query = null, int?maxRows = null)
{
var sql = new StringBuilder("select ");
if (maxRows.HasValue)
{
sql.AppendFormat(" TOP {0} ", maxRows.Value);
}
sql.Append(
@"f.*
, uf.IsFavorite
, uf.LastAccessedDate
from v_Fund f
left join UserFund uf
on f.Id = uf.FundId
and uf.UserName = @userName");
if (!string.IsNullOrEmpty(query))
{
sql.Append(" where f.Name like '%' + @query + '%' or f.SponsorName like '%' + @query + '%' or f.BusinessUnitName like '%' + @query + '%' or f.StrategyName like '%' + @query + '%' ");
}
return(await dataAccess.QueryAsync <Fund>(sql.ToString(), new { userName, query }));
}
public static async Task <int> UpdateFundAsync(this MasterSideLetterDataAccess dataAccess, Fund fund)
{
await dataAccess.ValidateFundReferences(fund);
return(await dataAccess.ExecuteAsync("update Fund set ModifiedDate = getdate(), Name = @Name, SponsorId = @SponsorId, BusinessUnitId = @BusinessUnitId, StrategyId = @StrategyId, Year = @Year, Size = @Size where Id = @Id",
fund));
}
public static Task <IEnumerable <FundInvestor> > GetFundInvestorsByFundAsync(this MasterSideLetterDataAccess dataAccess, int fundId)
{
return(dataAccess.QueryAsync <FundInvestor>(
@"select fi.*
from v_FundInvestor fi
where fi.FundId = @fundId", new { fundId }));
}
public static async Task <int> CreateFundAsync(this MasterSideLetterDataAccess dataAccess, Fund fund)
{
await dataAccess.ValidateFundReferences(fund);
return(await dataAccess.ExecuteScalarAsync <int>(
"insert Fund(Name, SponsorId, BusinessUnitId, StrategyId, Year, Size) output inserted.Id values(@Name, @SponsorId, @BusinessUnitId, @StrategyId, @Year, @Size)",
fund));
}
public static async Task UpdateProvisionAsync(this MasterSideLetterDataAccess dataAccess, Provision provision)
{
if (string.IsNullOrWhiteSpace(provision.ProvisionType))
{
provision.ProvisionType = null;
}
await dataAccess.ExecuteAsync(@"update Provision set FundInvestorId = @FundInvestorId, ProvisionType = @ProvisionType, IsProvisionTypeInherited = @IsProvisionTypeInherited, Content = @Content, Notes = @Notes, ModifiedDate = getdate() where Id = @Id", provision);
}
public static async Task UpdateFundInvestorSideLetterAsync(this MasterSideLetterDataAccess dataAccess, int id, string fileName, Stream readStream)
{
var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("id", id, DbType.Int32);
dynamicParameters.Add("fileName", fileName, DbType.String);
dynamicParameters.Add("fileContent", readStream, DbType.Binary);
await dataAccess.ExecuteAsync("update FundInvestor set SideLetterFileName = @fileName, SideLetterFileContent = @fileContent where Id = @id", dynamicParameters);
}
public static IEnumerable <Provision> GetFilteredProvisions(this MasterSideLetterDataAccess dataAccess, SearchRequest request, bool?hasType = null)
{
var filteredRequest = new SearchRequest(request)
{
TargetText = null, ProvisionSearchLimit = 0
};
var preparedQuery = GetProvisionSearchQuery(filteredRequest, hasType);
return(dataAccess.Query <Provision>(preparedQuery.Sql.ToString(), preparedQuery.Parameters, null, false));
}
public static async Task <Investor> GetInvestorAsync(this MasterSideLetterDataAccess dataAccess, int id, string userName)
{
return(await dataAccess.QueryFirstOrDefaultAsync <Investor>(
@"select i.*
, ui.IsFavorite
, ui.LastAccessedDate
from Investor i
left join UserInvestor ui
on i.Id = ui.InvestorId
and ui.UserName = @userName
where Id = @id", new { id, userName }));
}
public static async Task UpdateFundIsFavoriteAsync(this MasterSideLetterDataAccess dataAccess, int id, string userName, bool isFavorite)
{
await dataAccess.ExecuteAsync(@"update UserFund
set IsFavorite = @isFavorite
where FundId = @id
and UserName = @userName
if @@RowCount = 0
begin
insert UserFund(FundId,UserName,IsFavorite) values(@id, @userName, @isFavorite)
end", new { userName, id, isFavorite });
}
public static async Task UpdateInvestorLastAccessedDate(this MasterSideLetterDataAccess dataAccess, int id, string userName)
{
await dataAccess.ExecuteAsync(@"update UserInvestor
set LastAccessedDate = getdate()
where InvestorId = @id
and UserName = @userName
if @@RowCount = 0
begin
insert UserInvestor(InvestorId,UserName,LastAccessedDate) values(@id, @userName, getdate())
end", new { userName, id });
}
public static async Task <IEnumerable <string> > SearchProvisionTypesAsync(this MasterSideLetterDataAccess dataAccess, string query, int?limit = null)
{
var sql = new StringBuilder("select ");
if (limit.HasValue)
{
sql.Append($" TOP {limit} ");
}
sql.Append(" ProvisionType from v_Provision where ProvisionType like '%' + @query + '%' group by ProvisionType ");
return(await dataAccess.QueryAsync <string>(sql.ToString(), new { query, limit }));
}
public static async Task <IEnumerable <Investor> > GetInvestorsAsync(this MasterSideLetterDataAccess dataAccess, string userName, string query = null, string typeQuery = null, int?maxRows = null)
{
var sql = new StringBuilder("select ");
if (maxRows.HasValue)
{
sql.AppendFormat(" TOP {0} ", maxRows.Value);
}
sql.Append(
@"i.*
, ui.IsFavorite
, ui.LastAccessedDate
from Investor i
left join UserInvestor ui
on i.Id = ui.InvestorId
and ui.UserName = @userName
where 1 = 1 ");
if (!string.IsNullOrEmpty(query))
{
sql.Append(" and (i.Name like '%' + @query + '%' OR i.InvestorType like '%' + @query + '%') ");
}
if (!string.IsNullOrEmpty(typeQuery))
{
sql.Append(" and i.InvestorType like '%' + @typeQuery + '%'");
}
var investorList = (await dataAccess.QueryAsync <Investor>(sql.ToString(), new { userName, query, typeQuery })).ToList();
foreach (var investor in investorList)
{
var fundInvestor = await dataAccess.QueryFirstOrDefaultAsync <Investor>(
@"select
InvestorId,
SUM(Commitment) as aggregated,
Count(FundId) as fundNos
from [v_FundInvestor] where InvestorId = @investorId
group by InvestorId", new { investorId = investor.Id });
if (fundInvestor != null)
{
investor.Aggregated = fundInvestor.Aggregated;
investor.FundNos = fundInvestor.FundNos;
}
await dataAccess.AddRecentInvestmentsAsync(investor);
}
return(investorList);
}
public static async Task <int> UpdateSearchSettingsAsync(this MasterSideLetterDataAccess dataAccess, SearchSettings settings)
{
return(await dataAccess.ExecuteAsync(
@"update SearchSettings
set Algorithm1Weight = @Algorithm1Weight,
Algorithm2Weight = @Algorithm2Weight,
Algorithm3Weight = @Algorithm3Weight,
Algorithm1Threshold = @Algorithm1Threshold,
Algorithm2Threshold = @Algorithm2Threshold,
Algorithm3Threshold = @Algorithm3Threshold,
WeightedThreshold = @WeightedThreshold,
InheritThreshold = @InheritThreshold,
MslGroupingThreshold = @MslGroupingThreshold", settings));
}
public static async Task <Fund> GetFundAsync(this MasterSideLetterDataAccess dataAccess, int id, string userName)
{
return(await dataAccess.QueryFirstOrDefaultAsync <Fund>(
@"select
f.*
, uf.IsFavorite
, uf.LastAccessedDate
from v_Fund f
left Join UserFund uf
on f.Id = uf.FundId
and uf.UserName = @userName
where f.Id = @id
", new { id, userName }));
}
public static async Task <BusinessUnit> GetOrCreateBusinessUnitAsync(this MasterSideLetterDataAccess dataAccess, string name)
{
var existing = await dataAccess.GetBusinessUnitByNameAsync(name);
if (existing != null)
{
return(existing);
}
var newBusinessUnit = new BusinessUnit {
Name = name
};
newBusinessUnit.Id = await dataAccess.CreateBusinessUnitAsync(newBusinessUnit);
return(newBusinessUnit);
}
public static async Task <Sponsor> GetOrCreateSponsorAsync(this MasterSideLetterDataAccess dataAccess, string name)
{
var existing = await dataAccess.GetSponsorByNameAsync(name);
if (existing != null)
{
return(existing);
}
var newSponsor = new Sponsor {
Name = name
};
newSponsor.Id = await dataAccess.CreateSponsorAsync(newSponsor);
return(newSponsor);
}
public static async Task <Strategy> GetOrCreateStrategyAsync(this MasterSideLetterDataAccess dataAccess, string name)
{
var existing = await dataAccess.GetStrategyByNameAsync(name);
if (existing != null)
{
return(existing);
}
var newStrategy = new Strategy {
Name = name
};
newStrategy.Id = await dataAccess.CreateStrategyAsync(newStrategy);
return(newStrategy);
}
private static async Task AddRecentInvestmentsAsync(this MasterSideLetterDataAccess dataAccess, Investor investor)
{
var recentInvestments = await dataAccess.QueryAsync <Investment>(
@"select TOP 3
FundId,
FundName,
FundYear
from [v_FundInvestor]
where InvestorId = @investorId
and FundId is not null
and FundName is not null
group by FundId,FundName,FundYear
order by FundYear desc, FundName asc", new { investorId = investor.Id });
investor.RecentInvestments = recentInvestments.ToArray();
}
public static async Task <Investor> GetOrCreateInvestorAsync(this MasterSideLetterDataAccess dataAccess, string name, string type)
{
var existing = await dataAccess.GetInvestorByNameAsync(name);
if (existing != null)
{
return(existing);
}
var newInvestor = new Investor {
Name = name, InvestorType = type
};
newInvestor.Id = await dataAccess.CreateInvestorAsync(newInvestor);
return(newInvestor);
}
public static async Task <IEnumerable <Fund> > GetRecentFundsAsync(this MasterSideLetterDataAccess dataAccess, string userName, int?limit = null)
{
var sql = new StringBuilder("select ");
if (limit.HasValue)
{
sql.Append($" TOP {limit.Value} ");
}
sql.Append(@" f.*
, uf.IsFavorite
, uf.LastAccessedDate
from v_Fund f
join UserFund uf
on f.Id = uf.FundId
and uf.UserName = @userName
ORDER BY uf.LastAccessedDate DESC");
return(await dataAccess.QueryAsync <Fund>(sql.ToString(), new { userName }));
}
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 <IEnumerable <Investor> > GetFavoriteInvestorsAsync(this MasterSideLetterDataAccess dataAccess, string userName, int?limit = null)
{
var sql = new StringBuilder("select ");
if (limit.HasValue)
{
sql.Append($" TOP {limit.Value} ");
}
sql.Append(@" i.*
, ui.IsFavorite
, ui.LastAccessedDate
from Investor i
join UserInvestor ui
on i.Id = ui.InvestorId
and ui.UserName = @userName
where ui.IsFavorite = 1");
var investorList = (await dataAccess.QueryAsync <Investor>(sql.ToString(), new { userName })).ToList();
foreach (var investor in investorList)
{
var fundInvestor = await dataAccess.QueryFirstOrDefaultAsync <Investor>(
@"select
InvestorId,
SUM(Commitment) as aggregated,
Count(FundId) as fundNos
from [v_FundInvestor] where InvestorId = @investorId
group by InvestorId", new { investorId = investor.Id });
if (fundInvestor != null)
{
investor.Aggregated = fundInvestor.Aggregated;
investor.FundNos = fundInvestor.FundNos;
}
await dataAccess.AddRecentInvestmentsAsync(investor);
}
return(investorList);
}
public static async Task <Fund> GetOrCreateFundAsync(this MasterSideLetterDataAccess dataAccess, string name, string sponsorName, string businessUnitName, string strategyName, int?year, decimal?size)
{
var existing = await dataAccess.GetFundByNameAsync(name);
if (existing != null)
{
return(existing);
}
var newFund = new Fund
{
Name = name,
SponsorName = sponsorName,
BusinessUnitName = businessUnitName,
StrategyName = strategyName,
Year = year,
Size = size
};
newFund.Id = await dataAccess.CreateFundAsync(newFund);
return(newFund);
}
private static async Task ValidateFundReferences(this MasterSideLetterDataAccess dataAccess, Fund fund)
{
if (!string.IsNullOrEmpty(fund.SponsorName))
{
var sponsor = await dataAccess.GetOrCreateSponsorAsync(fund.SponsorName);
fund.SponsorId = sponsor.Id;
}
if (!string.IsNullOrEmpty(fund.StrategyName))
{
var strategy = await dataAccess.GetOrCreateStrategyAsync(fund.StrategyName);
fund.StrategyId = strategy.Id;
}
if (!string.IsNullOrEmpty(fund.BusinessUnitName))
{
var businessUnit = await dataAccess.GetOrCreateBusinessUnitAsync(fund.BusinessUnitName);
fund.BusinessUnitId = businessUnit.Id;
}
}
public static async Task <int> RemoveFundInvestorSideLetterAsync(this MasterSideLetterDataAccess dataAccess, int id)
{
return(await dataAccess.ExecuteAsync(
@"update FundInvestor set SideLetterFileName = null, SideLetterFileContent = null where Id = @id;
delete Provision where FundInvestorId = @id;", new { id }));
}
public static async Task <int> BatchDeleteFundInvestorAsync(this MasterSideLetterDataAccess dataAccess, int[] ids)
{
return(await dataAccess.ExecuteAsync("delete FundInvestor where Id in @ids", new { ids }));
}
public static async Task <int> DeleteFundInvestorAsync(this MasterSideLetterDataAccess dataAccess, int id)
{
await dataAccess.RemoveFundInvestorSideLetterAsync(id);
return(await dataAccess.ExecuteAsync("delete FundInvestor where Id = @id", new { id }));
}
public static async Task <IEnumerable <FundInvestor> > SearchFundInvestorsAsync(this MasterSideLetterDataAccess dataAccess, SearchRequest request)
{
var preparedQuery = new PreparedQuery(request);
preparedQuery.Sql.Append("select");
if (request.SideLetterSearchLimit > 0)
{
preparedQuery.Sql.Append($" TOP {request.SideLetterSearchLimit} ");
}
preparedQuery.Sql.Append(" fi.* ");
preparedQuery.Sql.Append(" from v_FundInvestor fi ");
preparedQuery.Sql.Append(" where 1=1 ");
// apply target text to all text columns
if (!string.IsNullOrEmpty(request.TargetText))
{
SearchHelper.AppendSearchCondition(request.TargetText,
new List <string>
{
"fi.FundName",
"fi.FundSponsorName",
"fi.FundBusinessUnitName",
"fi.FundStrategyName",
"fi.InvestorName",
"fi.InvestorType",
"fi.Entity",
"fi.Counsel",
"fi.Notes",
"fi.SideLetterFileName"
},
preparedQuery);
}
// apply relevant filters
if (request.FundValues != null && request.FundValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.FundName in @FundValues ");
}
if (request.InvestorValues != null && request.InvestorValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.InvestorName in @InvestorValues ");
}
if (request.SponsorValues != null && request.SponsorValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.FundSponsorName in @SponsorValues ");
}
if (request.BusinessUnitValues != null && request.BusinessUnitValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.FundBusinessUnitName in @BusinessUnitValues ");
}
if (request.StrategyValues != null && request.StrategyValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.FundStrategyName in @StrategyValues ");
}
if (request.InvestorTypeValues != null && request.InvestorTypeValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.InvestorType in @InvestorTypeValues ");
}
if (request.EntityValues != null && request.EntityValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.Entity in @EntityValues ");
}
if (request.CounselValues != null && request.CounselValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.Counsel in @CounselValues ");
}
if (request.SizeMin.HasValue)
{
preparedQuery.Sql.Append(" and fi.FundSize >= @SizeMin ");
}
if (request.SizeMax.HasValue)
{
preparedQuery.Sql.Append(" and fi.FundSize <= @SizeMax ");
}
if (request.YearMin.HasValue)
{
preparedQuery.Sql.Append(" and fi.FundYear >= @YearMin ");
}
if (request.YearMax.HasValue)
{
preparedQuery.Sql.Append(" and fi.FundYear <= @YearMax ");
}
if (request.CommitmentMin.HasValue)
{
preparedQuery.Sql.Append(" and fi.Commitment >= @CommitmentMin ");
}
if (request.CommitmentMax.HasValue)
{
preparedQuery.Sql.Append(" and fi.Commitment <= @CommitmentMax ");
}
if (request.AggregateSizeMin.HasValue)
{
preparedQuery.Sql.Append(" and fi.Aggregated >= @AggregateSizeMin ");
}
if (request.AggregateSizeMax.HasValue)
{
preparedQuery.Sql.Append(" and fi.Aggregated <= @AggregateSizeMax ");
}
if (request.InvestorId.HasValue)
{
preparedQuery.Sql.Append(" and fi.InvestorId = @InvestorId ");
}
if (request.FundId.HasValue)
{
preparedQuery.Sql.Append(" and fi.FundId = @FundId");
}
return(await dataAccess.QueryAsync <FundInvestor>(preparedQuery.Sql.ToString(), preparedQuery.Parameters));
}