コード例 #1
0
        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));
        }
コード例 #2
0
        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);
        }
コード例 #3
0
        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 }));
        }
コード例 #4
0
        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));
        }
コード例 #5
0
 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 }));
 }
コード例 #6
0
        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));
        }
コード例 #7
0
        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);
        }
コード例 #8
0
        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);
        }
コード例 #9
0
        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));
        }
コード例 #10
0
 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 }));
 }
コード例 #11
0
 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 });
 }
コード例 #12
0
 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 });
 }
コード例 #13
0
        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 }));
        }
コード例 #14
0
        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);
        }
コード例 #15
0
 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));
 }
コード例 #16
0
 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 }));
 }
コード例 #17
0
        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);
        }
コード例 #18
0
        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);
        }
コード例 #19
0
        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);
        }
コード例 #20
0
        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();
        }
コード例 #21
0
        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);
        }
コード例 #22
0
        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 }));
        }
コード例 #23
0
        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));
        }
コード例 #24
0
        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);
        }
コード例 #25
0
        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);
        }
コード例 #26
0
        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;
            }
        }
コード例 #27
0
 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 }));
 }
コード例 #28
0
 public static async Task <int> BatchDeleteFundInvestorAsync(this MasterSideLetterDataAccess dataAccess, int[] ids)
 {
     return(await dataAccess.ExecuteAsync("delete FundInvestor where Id in @ids", new { ids }));
 }
コード例 #29
0
        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 }));
        }
コード例 #30
0
        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));
        }