public List <Track_Album> GetAllTracksFromAlbum(int AlbumId)
{
return(Dbset.SqlQuery(String.Format(@"
SELECT Id,TrackSequence,TrackId,AlbumId
FROM dbo.Track_Album
WHERE {0}= AlbumId
Order By TrackSequence", AlbumId)
).ToList());
}
public async Task <IList <IChartIndicatorEntity> > GetForLayoutAsync(int layoutId)
{
const string query = @"
SELECT I.*
FROM dbo.ChartPlot P
INNER JOIN dbo.ChartIndicator I
ON I.PlotId = P.PlotId
WHERE P.LayoutId = @LayoutId ";
var records = await Dbset.SqlQuery(query, new object[] { new SqlParameter("@LayoutId", layoutId) }).ToListAsync();
return(records.Select(r => r as IChartIndicatorEntity).ToList());
}
public async Task <IList <IChartIndicatorEntity> > GetForPeriodAsync(QuotePeriod period)
{
const string query = @"
SELECT I.*
FROM dbo.ChartLayout L
INNER JOIN dbo.ChartIndicator I
ON L.PlotId = I.PlotId
WHERE L.Deleted = 0
AND L.Period = @Period ";
var records = await Dbset.SqlQuery(query, new object[] { new SqlParameter("@Period", (int)period) }).ToListAsync();
return(records.Select(r => r as IChartIndicatorEntity).ToList());
}
public List <CompanyToProcess> FindCompaniesToCalculate(int maxCompanyCount)
{
var sql = $@"
SELECT TOP {maxCompanyCount} C.*
FROM dbo.Company C
WHERE C.LastCalculated < C.LastUpdated AND C.Filtered = 1";
var companies = Dbset.SqlQuery(sql)
.Select(c =>
new CompanyToProcess
{
Ticker = c.Ticker,
LastCalculated = c.LastCalculated,
LastUpdated = c.LastUpdated,
QuotesJson = c.HistoryQuotesJson
}).ToList();
return(companies);
}
//TODO:
public async Task <List <CompanyQuotesModel> > FindCompaniesForJobAsync(string jobId, int count, int sectorId, bool sp500, bool isIndex)
{
var query = $@"
SELECT TOP {count} C.*
FROM [dbo].[Company] C
LEFT JOIN [dbo].[ScheduledJobDetails] J
ON C.Ticker = J.Ticker AND J.JobId = @JobId
WHERE C.Filtered = 1
AND (C.SectorId = @SectorId OR @SectorId = 0)
AND (C.SP500 = 1 OR @SP500 = 0)
AND J.JobId IS NULL" ;
if (isIndex)
{
query = $@"
SELECT TOP {count} C.*
FROM [dbo].[Company] C
LEFT JOIN [dbo].[ScheduledJobDetails] J
ON C.Ticker = J.Ticker AND J.JobId = @JobId
WHERE C.Filtered = 1
AND (C.IsIndex = 1)
AND (@SectorId = 0)
AND (@SP500 = 0)
AND J.JobId IS NULL";
}
var records = await Dbset.SqlQuery(query,
new SqlParameter("@JobId", jobId),
new SqlParameter("@SectorId", sectorId),
new SqlParameter("@SP500", sp500)
).ToListAsync();
return(records.Select(c =>
new CompanyQuotesModel
{
Ticker = c.Ticker,
LastUpdated = c.LastUpdated,
HistoryQuotesJson = c.HistoryQuotesJson
}).ToList());
}
public async Task <List <Indicator> > GetByStrategyIdAsync(int id)
{
const string query = @"
SELECT DISTINCT I.*
FROM [dbo].[Indicator] I
INNER JOIN (
SELECT [DataSeriesV1] AS IndicatorId, [RuleId]
FROM [dbo].[Rule]
WHERE [DataSourceV1] = 0
UNION
SELECT [DataSeriesV2], [RuleId]
FROM [dbo].[Rule]
WHERE [DataSourceV2] = 0
) R ON I.IndicatorId = R.IndicatorId
INNER JOIN [dbo].[RuleSetDetails] RS ON RS.RuleId = R.RuleId
INNER JOIN [dbo].[StrategyRuleSet] S ON S.RuleSetId = RS.RuleSetId
WHERE I.Deleted = 0 AND S.StrategyId = @strategyId
";
var records = await Dbset.SqlQuery(query, new[] { new SqlParameter("@strategyId", id) }).ToListAsync();
return(records);
}