/// <summary>
        /// Count
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int Count(CompounderQueryParams parameters)
        {
            var dates   = FmpHelper.BuildDatesList(parameters.YearFrom, parameters.YearTo, parameters.Dates);
            var command = DbCommands.Compounder(DataContext.Database.GetDbConnection(), Sql.Compounder(parameters, dates), parameters, dates);

            return(QueryAsEnumerable(command, ResultSetFunctions.Compounder).Count());
        }
        /// <summary>
        /// Run
        /// </summary>
        /// <param name="parameters"></param>
        /// <param name="symbolList"></param>
        /// <returns></returns>
        public ResultSetListReinvestment FindBySymbol(CompounderQueryParams parameters, List <string> symbolList)
        {
            ResultSetListReinvestment resultSetList = null;
            QueryFactory queryFactory = new QueryFactory(DataContext.ConnectionString);

            var dates             = FmpHelper.BuildDatesList(parameters.YearFrom, parameters.YearTo, parameters.Dates);
            var command           = DbCommands.FindBySymbol(DataContext.Database.GetDbConnection(), Sql.FindBySymbol(symbolList, dates), symbolList, dates);
            var queryAsEnumerable = QueryAsEnumerable(command, ResultSetFunctions.FindBySymbol).ToList();

            queryAsEnumerable = AddHistoryData(queryAsEnumerable, parameters, queryFactory.RoeHistoryQuery, a => a.RoeHistory);
            queryAsEnumerable = AddHistoryData(queryAsEnumerable, parameters, queryFactory.RevenueHistoryQuery, a => a.RevenueHistory);
            queryAsEnumerable = AddHistoryData(queryAsEnumerable, parameters, queryFactory.EpsHistoryQuery, a => a.EpsHistory);

            queryAsEnumerable = AdjustToGrowthKoef(queryAsEnumerable, parameters.RoeGrowthKoef, r => r.RoeHistory);
            queryAsEnumerable = AdjustToGrowthKoef(queryAsEnumerable, parameters.RevenueGrowthKoef, r => r.RevenueHistory);
            queryAsEnumerable = AdjustToGrowthKoef(queryAsEnumerable, parameters.EpsGrowthKoef, r => r.EpsHistory);

            List <ResultSetReinvestment> listOfResultSets = queryAsEnumerable.ToList();

            resultSetList            = new ResultSetListReinvestment(listOfResultSets);
            resultSetList.CountTotal = queryAsEnumerable.Count();

            resultSetList = AddHistoryData(resultSetList, parameters, queryFactory.ReinvestmentHistoryQuery, a => a.ReinvestmentHistory);
            resultSetList = AddHistoryData(resultSetList, parameters, queryFactory.IncrementalRoeQuery, a => a.IncrementalRoe);
            resultSetList = AddHistoryData(resultSetList, parameters, queryFactory.OperatingIncomeHistoryQuery, a => a.OperatingIncome);
            resultSetList = AddHistoryData(resultSetList, parameters, queryFactory.CashConversionQuery, a => a.CashConversionHistory);
            resultSetList = AddCompanyName(resultSetList);
            resultSetList = AddDebtEquityIncome(resultSetList);

            return(resultSetList);
        }
Beispiel #3
0
        /// <summary>
        /// Compounder
        /// </summary>
        /// <param name="parameters"></param>
        /// <param name="dates"></param>
        /// <returns></returns>
        public static string Compounder(CompounderQueryParams parameters, List <string> dates)
        {
            string sqlBase = $@"select v.Symbol, v.Date, v.Equity, v.Debt, v.NetIncome, v.Roe, v.ReinvestmentRate, v.DebtEquityRatio
                from ViewCompounder v 
                inner join Stocks s
                on v.Symbol = s.Symbol
                where 1 = 1
                and v.Date in (@Dates) ";

            string datesAsParam = CreateCommaSeparatedParams("@Dates", dates.Count);
            string sql          = sqlBase.Replace("@Dates", datesAsParam);

            if (parameters.RoeFrom.HasValue)
            {
                sql += " and v.Roe >= @RoeFrom ";
            }
            if (parameters.RoeTo.HasValue)
            {
                sql += " and Roe <= @RoeTo ";
            }
            if (parameters.ReinvestmentRateFrom.HasValue)
            {
                sql += " and v.ReinvestmentRate >= @ReinvestmentRateFrom ";
            }
            if (parameters.ReinvestmentRateTo.HasValue)
            {
                sql += " and ReinvestmentRate <= @ReinvestmentRateTo ";
            }
            if (parameters.DebtEquityRatioFrom.HasValue)
            {
                sql += " and DebtEquityRatio >= @DebtEquityRatioFrom ";
            }
            if (parameters.DebtEquityRatioTo.HasValue)
            {
                sql += " and DebtEquityRatio <= @DebtEquityRatioTo ";
            }
            if (parameters.Exchanges.Any())
            {
                sql += " and s.Exchange in(@Exchanges) ";
            }

            string exchangesAsParam = CreateCommaSeparatedParams("@Exchanges", parameters.SelectedFmpExchanges.Count);

            sql = sql.Replace("@Exchanges", exchangesAsParam);

            var ascDesc = parameters.Descending ? " DESC " : " ASC ";

            sql += $" order by {parameters.OrderBy} {ascDesc} ";

            return(sql);
        }
        /// <summary>
        /// Run
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public ResultSetListReinvestment Run(CompounderQueryParams parameters)
        {
            ResultSetListReinvestment resultSetList = null;
            QueryFactory queryFactory = new QueryFactory(DataContext.ConnectionString);

            var p = parameters;

            var dates   = FmpHelper.BuildDatesList(parameters.YearFrom, parameters.YearTo, parameters.Dates);
            var command = DbCommands.Compounder(DataContext.Database.GetDbConnection(), Sql.Compounder(parameters, dates), parameters, dates);

            var queryAsEnumerable = QueryAsEnumerable(command, ResultSetFunctions.Compounder);

            if (queryAsEnumerable.Count() > parameters.MaxResultCount)
            {
                return(new ResultSetListReinvestment(new List <ResultSetReinvestment>())
                {
                    CountTotal = queryAsEnumerable.Count()
                });
            }

            var queryAsEnumerableList = queryAsEnumerable.ToList();

            queryAsEnumerableList = AddHistoryData(queryAsEnumerableList, parameters, queryFactory.RoeHistoryQuery, a => a.RoeHistory);
            queryAsEnumerableList = AddHistoryData(queryAsEnumerableList, parameters, queryFactory.RevenueHistoryQuery, a => a.RevenueHistory);
            queryAsEnumerableList = AddHistoryData(queryAsEnumerableList, parameters, queryFactory.EpsHistoryQuery, a => a.EpsHistory);

            queryAsEnumerableList = AdjustToGrowthKoef(queryAsEnumerableList, parameters.RoeGrowthKoef, r => r.RoeHistory);
            queryAsEnumerableList = AdjustToGrowthKoef(queryAsEnumerableList, parameters.RevenueGrowthKoef, r => r.RevenueHistory);
            queryAsEnumerableList = AdjustToGrowthKoef(queryAsEnumerableList, parameters.EpsGrowthKoef, r => r.EpsHistory);

            List <ResultSetReinvestment> listOfResultSets = p.PageSize == 0
                ? queryAsEnumerableList.ToList()
                : queryAsEnumerableList.Skip(p.CurrentPage * p.PageSize).Take(p.PageSize).ToList();

            resultSetList            = new ResultSetListReinvestment(listOfResultSets);
            resultSetList.CountTotal = queryAsEnumerableList.Count();

            resultSetList = AddHistoryData(resultSetList, parameters, queryFactory.ReinvestmentHistoryQuery, a => a.ReinvestmentHistory);
            resultSetList = AddHistoryData(resultSetList, parameters, queryFactory.IncrementalRoeQuery, a => a.IncrementalRoe);
            resultSetList = AddHistoryData(resultSetList, parameters, queryFactory.OperatingIncomeHistoryQuery, a => a.OperatingIncome);
            resultSetList = AddHistoryData(resultSetList, parameters, queryFactory.CashConversionQuery, a => a.CashConversionHistory);
            resultSetList = AddCompanyName(resultSetList);
            resultSetList = AddDebtEquityIncome(resultSetList);

            return(resultSetList);
        }
        /// <summary>
        /// Compounder
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="dates"></param>
        /// <returns></returns>
        public static DbCommand Compounder(DbConnection connection, string sql, CompounderQueryParams parameters, List <string> dates)
        {
            var command = connection.CreateCommand();

            command.CommandText = sql;
            command.CommandType = CommandType.Text;

            AddDoubleParameter(command, "@RoeFrom", DbType.Double, parameters.RoeFrom);
            AddDoubleParameter(command, "@RoeTo", DbType.Double, parameters.RoeTo);
            AddDoubleParameter(command, "@ReinvestmentRateFrom", DbType.Double, parameters.ReinvestmentRateFrom);
            AddDoubleParameter(command, "@ReinvestmentRateTo", DbType.Double, parameters.ReinvestmentRateTo);

            AddStringListParameter(command, "@Dates", DbType.String, dates);
            AddStringListParameter(command, "@Exchanges", DbType.String, parameters.SelectedFmpExchanges);

            AddDoubleParameter(command, "@DebtEquityRatioFrom", DbType.Double, parameters.DebtEquityRatioFrom);
            AddDoubleParameter(command, "@DebtEquityRatioTo", DbType.Double, parameters.DebtEquityRatioTo);

            return(command);
        }