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 Task <IEnumerable <FundInvestor> > GetFundInvestorsByInvestorAsync(this MasterSideLetterDataAccess dataAccess, int investorId) { return(dataAccess.QueryAsync <FundInvestor>( @"select fi.* from v_FundInvestor fi where fi.InvestorId = @investorId", new { investorId })); }
public static async Task <IEnumerable <Provision> > GetProvisionsByFundInvestorAsync(this MasterSideLetterDataAccess dataAccess, int fundInvestorId) { var fundInvestorIds = new List <int> { fundInvestorId }; return(await dataAccess.QueryAsync <Provision>(@"select * from v_Provision p where p.FundInvestorId in @fundInvestorIds", new { fundInvestorIds })); }
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); }
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 <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 <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 Task <IEnumerable <BusinessUnit> > GetBusinessUnitsAsync(this MasterSideLetterDataAccess dataAccess) { return(dataAccess.QueryAsync <BusinessUnit>("select * from BusinessUnit")); }
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); }
public static async Task <IEnumerable <Provision> > GetProvisionsAsync(this MasterSideLetterDataAccess dataAccess, List <int> provisionIds) { return(await dataAccess.QueryAsync <Provision>(@"select * from v_Provision p where p.Id in @provisionIds", new { provisionIds })); }
public static Task <IEnumerable <Sponsor> > GetSponsorsAsync(this MasterSideLetterDataAccess dataAccess) { return(dataAccess.QueryAsync <Sponsor>("select * from Sponsor")); }
public static async Task <List <Investor> > SearchInvestorsAsync(this MasterSideLetterDataAccess dataAccess, SearchRequest request) { var preparedQuery = new PreparedQuery(request); preparedQuery.Sql.Append("select"); if (request.InvestorSearchLimit > 0) { preparedQuery.Sql.Append($" TOP {request.InvestorSearchLimit} "); } preparedQuery.Sql.Append(" i.* , ui.IsFavorite, ui.LastAccessedDate "); preparedQuery.Sql.Append(" from v_Investor i "); preparedQuery.Sql.Append(" left join UserInvestor ui "); preparedQuery.Sql.Append(" on i.Id = ui.InvestorId "); preparedQuery.Sql.Append(" and ui.UserName = @UserName "); 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> { "i.Name", "i.InvestorType" }, preparedQuery); } // apply relevant filters if (request.InvestorValues != null && request.InvestorValues.Length > 0) { preparedQuery.Sql.Append(" and i.Name in @InvestorValues "); } if (request.InvestorTypeValues != null && request.InvestorTypeValues.Length > 0) { preparedQuery.Sql.Append(" and i.InvestorType in @InvestorTypeValues "); } if (request.AggregateSizeMin.HasValue) { preparedQuery.Sql.Append(" and i.Aggregated >= @AggregateSizeMin "); } if (request.AggregateSizeMax.HasValue) { preparedQuery.Sql.Append(" and i.Aggregated <= @AggregateSizeMax "); } IEnumerable <Investor> results = await dataAccess.QueryAsync <Investor>(preparedQuery.Sql.ToString(), preparedQuery.Parameters); var investors = results.ToList(); foreach (var investor in investors) { await dataAccess.AddRecentInvestmentsAsync(investor); } return(investors); }
public static Task <IEnumerable <Strategy> > GetStrategiesAsync(this MasterSideLetterDataAccess dataAccess) { return(dataAccess.QueryAsync <Strategy>("select * from Strategy")); }
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)); }
public static async Task <IEnumerable <string> > GetFilterContentAsync(this MasterSideLetterDataAccess dataAccess, FilterContentRequest request) { var sql = new StringBuilder("select "); if (request.FilterLimit > 0) { sql.Append($" TOP {request.FilterLimit} "); } sql.Append(" * from ("); switch (request.FilterType) { case "Fund": sql.Append( @"select distinct Name from Fund where Name like '%' + @TargetText + '%' "); break; case "Investor": sql.Append( @"select distinct Name from Investor where Name like '%' + @TargetText + '%' "); break; case "Sponsor": sql.Append( @"select distinct SponsorName from v_SponsorNames where SponsorName like '%' + @TargetText + '%' "); break; case "BusinessUnit": sql.Append( @"select distinct BusinessUnitName from v_BusinessUnitNames where BusinessUnitName like '%' + @TargetText + '%' "); break; case "Strategy": sql.Append( @"select distinct StrategyName from v_StrategyNames where StrategyName like '%' + @TargetText + '%' "); break; case "InvestorType": sql.Append( @"select distinct InvestorType from Investor where InvestorType like '%' + @TargetText + '%' "); break; case "ProvisionType": sql.Append( @"select distinct ProvisionType from v_Provision where ProvisionType like '%' + @TargetText + '%' "); break; case "Entity": sql.Append( @"select distinct Entity from FundInvestor where Entity like '%' + @TargetText + '%' "); break; case "Counsel": sql.Append( @"select distinct Counsel from FundInvestor where Counsel like '%' + @TargetText + '%' "); break; } sql.Append(") as s"); return(await dataAccess.QueryAsync <string>(sql.ToString(), request)); }
public static async Task <IEnumerable <Fund> > SearchFundsAsync(this MasterSideLetterDataAccess dataAccess, SearchRequest request) { var preparedQuery = new PreparedQuery(request); preparedQuery.Sql.Append("select"); if (request.FundSearchLimit > 0) { preparedQuery.Sql.Append($" TOP {request.FundSearchLimit} "); } preparedQuery.Sql.Append(" f.*, uf.IsFavorite, uf.LastAccessedDate "); preparedQuery.Sql.Append(" from v_Fund f "); preparedQuery.Sql.Append(" left join UserFund uf "); preparedQuery.Sql.Append(" on f.Id = uf.FundId "); preparedQuery.Sql.Append(" and uf.UserName = @UserName "); 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> { "f.Name", "f.SponsorName", "f.BusinessUnitName", "f.StrategyName" }, preparedQuery); } // apply relevant filters if (request.FundValues != null && request.FundValues.Length > 0) { preparedQuery.Sql.Append(" and f.Name in @FundValues "); } // apply relevant filters if (request.SponsorValues != null && request.SponsorValues.Length > 0) { preparedQuery.Sql.Append(" and f.SponsorName in @SponsorValues "); } if (request.BusinessUnitValues != null && request.BusinessUnitValues.Length > 0) { preparedQuery.Sql.Append(" and f.BusinessUnitName in @BusinessUnitValues "); } if (request.StrategyValues != null && request.StrategyValues.Length > 0) { preparedQuery.Sql.Append(" and f.StrategyName in @StrategyValues "); } if (request.SizeMin.HasValue) { preparedQuery.Sql.Append(" and f.Size >= @SizeMin "); } if (request.SizeMax.HasValue) { preparedQuery.Sql.Append(" and f.Size <= @SizeMax "); } if (request.YearMin.HasValue) { preparedQuery.Sql.Append(" and f.Year >= @YearMin "); } if (request.YearMax.HasValue) { preparedQuery.Sql.Append(" and f.Year <= @YearMax "); } preparedQuery.Sql.Append(" ORDER BY uf.LastAccessedDate DESC "); return(await dataAccess.QueryAsync <Fund>(preparedQuery.Sql.ToString(), preparedQuery.Parameters)); }