public void SaveUsage()
{
string updateScript = "update AlphaVantageKeys set DateLastUsed='<date>', DailyCallCount=<callCount> where ApiKey='<ApiKey>'"
.Replace("<date>", DateTime.Today.ToString())
.Replace("<ApiKey>", this.Key)
.Replace("<callCount>", (this.CallsPerDay - this.dailyCallsLeft).ToString());
SQLUtilities.ExecuteScript(updateScript);
}
internal static List <AlphaVantageAgent.KeyAgent> LoadKeys(AlphaVantageAgent parent)
{
List <AlphaVantageAgent.KeyAgent> keyAgents;
string keyQuery = @"select ApiKey,
coalesce(DateLastUsed, '1900-01-01') as DateLastUsed,
coalesce(DailyCallCount, 0) as DailyCallCount,
DailyCallMax as DailyCallMax,
coalesce(CallsPerMinute, 0) as CallsPerMinute
from AlphaVantageKeys;";
List <KeyDto> dtos = SQLUtilities.FromQuery <KeyDto>(keyQuery);
keyAgents = dtos.Select(d => new AlphaVantageAgent.KeyAgent(parent, d.DailyCallMax, d.CallsPerMinute, d.ApiKey, d.DateLastUsed, d.DailyCallCount))
.ToList();
return(keyAgents);
}
public void Run(string key)
{
string fileName;
using (var client = new WebClient())
{
fileName = AlphaVantageConfigs.BuildFilename(this.OutputSize, this.ApiFunction, this.TargetSymbol);
string url = AlphaVantageConfigs.BuildUrl(this.OutputSize, this.ApiFunction, this.TargetSymbol, key);
Console.WriteLine("key agent {2} requesting file {0} from {1}", fileName, url, key);
client.DownloadFile(url, fileName);
}
List <StockDataPoint> data = new List <StockDataPoint>();
try
{
using (TextFieldParser csvParser = new TextFieldParser(fileName))
{
csvParser.SetDelimiters(new string[] { "," });
csvParser.ReadLine();
int rowsLoaded = 0;
while (!csvParser.EndOfData)
{
string[] fields = csvParser.ReadFields();
data.Add(new StockDataPoint(fields));
rowsLoaded += 1;
}
Console.WriteLine("key agent {0} loaded {1} rows", key, rowsLoaded);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
DataTable table = SQLUtilities.ToDataTable <StockDataPoint>(data);
using (SqlConnection connection = new SqlConnection(AlphaVantageConfigs.ConnectionString))
{
connection.Open();
string tempTableName = "#StockData";
StockDataPoint.SqlCreateTempTable(connection, tempTableName);
SqlBulkCopy sb = new SqlBulkCopy(connection);
sb.DestinationTableName = tempTableName;
sb.WriteToServer(table);
string updateScript = "";
string insertScript = "";
if (this.ApiFunction == ApiFunction.Daily && this.OutputSize == OutputSize.Full)
{
updateScript = $@"update s
set s.HistoricalLoadStatus = 1,
s.MinDate = t.MinDate
from StockSymbol s
inner join (select Symbol, min(PriceDate) as MinDate
from StockPriceDaily ss
where ss.Symbol = '{this.TargetSymbol}'
group by Symbol) t
on s.Symbol = t.Symbol
where s.Symbol = '{this.TargetSymbol}';";
}
else if (this.ApiFunction == ApiFunction.ThirtyMin && this.OutputSize == OutputSize.Full)
{
updateScript = $@"update s
set s.HalfHourlyFullStatus = 1,
s.MinDateTime = t.MinDateTime
from StockSymbol s
inner join (select Symbol, min(PriceDate) as MinDateTime
from StockPrice30Min ss
where ss.Symbol = '{this.TargetSymbol}'
group by Symbol) t
on s.Symbol = t.Symbol
where s.Symbol = '{this.TargetSymbol}';";
}
insertScript = $@"insert into <Table> with (tablock)
(Symbol, PriceDate, [Open], High, Low, [Close], Volume)
select '{this.TargetSymbol}', [Open], [High], [Low], [Close], Volume
from #StockData t
where not exists (select 1
from StockPrice s
where s.Symbol = '{this.TargetSymbol}'
and cast([TimeStamp] as date) = s.PriceDate
and cast([TimeStamp] as time(0)) = s.PriceTime);"
.Replace("<Table>", this.ApiFunction == ApiFunction.Daily ? "StockPriceDaily" : "StockPrice30Min");
string script = $@"
begin try
begin transaction;
{insertScript}
{updateScript}
commit transaction;
end try
begin catch
--do some stuff or whatever...
end catch
";
SqlCommand cmd = new SqlCommand(insertScript, connection);
cmd.ExecuteNonQuery();
connection.Close();
}
Console.WriteLine("key agent {0} saved {1} to database", key, this.Descriptor);
}
private void pollDatabase()
{
Console.WriteLine("Agent poll database start");
string symbolQuery = @"select s.Symbol,CompanyName,Industry,HistoricalLoadStatus,HourlyFullStatus,MaxTimestamp
from StockSymbol s
left join (select Symbol, cast(max(PriceDate) as datetime) + cast(max(PriceTime) as datetime) as MaxTimestamp
from StockPrice sp
group by Symbol) sp on s.Symbol = sp.Symbol
where (HistoricalLoadStatus = 0
or HourlyFullStatus = 0
or exists (select 1
from Util_DateTimes dt
where MaxTimestamp < dt.DateTimePoint
and dt.DateTimePoint < getdate()
)
)
and IsActive = 1
order by s.LoadPriority, s.Symbol;";
List <StockSymbol> symbols = SQLUtilities.FromQuery <StockSymbol>(symbolQuery);
foreach (StockSymbol s in symbols)
{
if (!s.HistoricalLoadStatus)
{
lock (this.tasks)
{
ILoadTask newTask = new LoadSymbolPriceData(stockSymbol: s.Symbol,
outputSize: OutputSize.Full,
apiFunction: ApiFunction.Daily);
this.tasks.Add(newTask);
}
}
if (!s.HourlyFullStatus)
{
lock (this.tasks)
{
ILoadTask newTask = new LoadSymbolPriceData(stockSymbol: s.Symbol,
outputSize: OutputSize.Full,
apiFunction: ApiFunction.ThirtyMin);
this.tasks.Add(newTask);
}
}
//if ((s.UpdateTimestamp - DateTime.Now).TotalMinutes > 30)
//{
// lock (this.tasks)
// {
// ILoadTask newTask = new LoadSymbolPriceData(stockSymbol: s.Symbol,
// outputSize: OutputSize.Compact,
// apiFunction: ApiFunction.ThirtyMin);
// this.tasks.Add(new LoadSymbolPriceData(stockSymbol: s.Symbol,
// outputSize: OutputSize.Compact,
// apiFunction: ApiFunction.ThirtyMin));
// }
//}
}
}