/// <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);
}
/// <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);
}
