404 lines
16 KiB
C#
404 lines
16 KiB
C#
using System;
|
|
using System.Text;
|
|
using System.Diagnostics;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using MySql.Data.MySqlClient;
|
|
using MarketData.MarketDataModel;
|
|
using MarketData.Utils;
|
|
|
|
namespace MarketData.DataAccess
|
|
{
|
|
public class HeadlinesDA
|
|
{
|
|
private HeadlinesDA()
|
|
{
|
|
}
|
|
public static DateTime GetMaxHeadlineDate()
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand = null;
|
|
String strQuery = null;
|
|
DateTime maxDate = Utility.Epoch;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select max(asof) from headlines");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
if (sqlDataReader.Read())
|
|
{
|
|
maxDate=sqlDataReader.GetDateTime(0);
|
|
}
|
|
return maxDate;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG, exception);
|
|
return Utility.Epoch;
|
|
}
|
|
finally
|
|
{
|
|
if (null != sqlCommand) sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static List<String> GetHeadlineDates()
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
List<String> headlineDates=new List<String>();
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select distinct asof from headlines order by 1 desc");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
headlineDates.Add(sqlDataReader.GetDateTime(0).ToShortDateString());
|
|
}
|
|
return headlineDates;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return headlineDates;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static Headlines GetHeadlines(String symbol)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
Headlines headlines=new Headlines();
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select h.symbol, h.asof, h.headline, h.source, h.modified,sm.company from headlines h left outer join securitymaster sm on h.symbol=sm.symbol where h.symbol=").Append(SqlUtils.AddQuotes(symbol));
|
|
sb.Append(" order by h.asof desc, h.modified desc");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
Headline headline=new Headline();
|
|
headline.Symbol=symbol;
|
|
headline.Date=sqlDataReader.GetDateTime(1);
|
|
headline.Entry=sqlDataReader.GetString(2);
|
|
headline.Source=sqlDataReader.GetString(3);
|
|
headline.Modified=sqlDataReader.GetDateTime(4);
|
|
headline.CompanyName=sqlDataReader.GetString(5);
|
|
headlines.Add(headline);
|
|
}
|
|
return headlines;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return headlines;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static Headlines GetHeadlines()
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
Headlines headlines=new Headlines();
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select h.symbol, h.asof, h.headline, h.source, h.modified, sm.company from headlines h left outer join securitymaster sm on h.symbol=sm.symbol");
|
|
sb.Append(" order by h.asof desc,h.symbol,h.modified desc;");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
Headline headline=new Headline();
|
|
headline.Symbol=sqlDataReader.GetString(0);
|
|
headline.Date=sqlDataReader.GetDateTime(1);
|
|
headline.Entry=sqlDataReader.GetString(2);
|
|
headline.Source=sqlDataReader.GetString(3);
|
|
headline.Modified=sqlDataReader.GetDateTime(4);
|
|
headline.CompanyName=sqlDataReader.GetString(5);
|
|
headlines.Add(headline);
|
|
}
|
|
return headlines;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return headlines;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
// This was authored for mobile app. It wants the sorting to match the WPF app.
|
|
public static Headlines GetLatestHeadlines()
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand = null;
|
|
String strQuery = null;
|
|
Headlines headlines = new Headlines();
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select h.symbol, h.asof, h.headline, h.source, h.modified, sm.company from headlines h left outer join securitymaster sm on h.symbol=sm.symbol");
|
|
sb.Append(" where h.asof=(select max(asof) from headlines)");
|
|
sb.Append(" order by h.asof desc,h.modified desc,h.symbol desc;");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
Headline headline = new Headline();
|
|
headline.Symbol = sqlDataReader.GetString(0);
|
|
headline.Date = sqlDataReader.GetDateTime(1);
|
|
headline.Entry = sqlDataReader.GetString(2);
|
|
headline.Source = sqlDataReader.GetString(3);
|
|
headline.Modified = sqlDataReader.GetDateTime(4);
|
|
headline.CompanyName = sqlDataReader.GetString(5);
|
|
headlines.Add(headline);
|
|
}
|
|
return headlines;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG, exception);
|
|
return headlines;
|
|
}
|
|
finally
|
|
{
|
|
if (null != sqlCommand) sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static Headlines GetHeadlines(String symbol,DateTime dateTime)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
Headlines headlines=new Headlines();
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select h.symbol, h.asof, h.headline, h.source, h.modified, sm.company from headlines h left outer join securitymaster sm on h.symbol=sm.symbol where h.asof=").Append(SqlUtils.AddQuotes(SqlUtils.SqlDate(dateTime)));
|
|
sb.Append(" and h.symbol=").Append(SqlUtils.AddQuotes(symbol));
|
|
sb.Append("order by h.asof desc, h.modified desc");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
Headline headline=new Headline();
|
|
headline.Symbol=sqlDataReader.GetString(0);
|
|
headline.Date=sqlDataReader.GetDateTime(1);
|
|
headline.Entry=sqlDataReader.GetString(2);
|
|
headline.Source=sqlDataReader.GetString(3);
|
|
headline.Modified=sqlDataReader.GetDateTime(4);
|
|
headline.CompanyName=sqlDataReader.GetString(5);
|
|
headlines.Add(headline);
|
|
}
|
|
return headlines;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return headlines;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static Headlines GetHeadlines(DateTime dateTime)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
Headlines headlines=new Headlines();
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select h.symbol, h.asof, h.headline, h.source, h.modified, sm.company from headlines h left outer join securitymaster sm on h.symbol=sm.symbol where h.asof=").Append(SqlUtils.AddQuotes(SqlUtils.SqlDate(dateTime)));
|
|
sb.Append("order by h.modified desc,h.symbol");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
Headline headline=new Headline();
|
|
headline.Symbol=sqlDataReader.GetString(0);
|
|
headline.Date=sqlDataReader.GetDateTime(1);
|
|
headline.Entry=sqlDataReader.GetString(2);
|
|
headline.Source=sqlDataReader.GetString(3);
|
|
headline.Modified=sqlDataReader.GetDateTime(4);
|
|
headline.CompanyName=sqlDataReader.GetString(5);
|
|
headlines.Add(headline);
|
|
}
|
|
return headlines;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return headlines;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static bool InsertHeadlines(Headlines headlines)
|
|
{
|
|
MySqlCommand sqlCommand=null;
|
|
MySqlConnection sqlConnection=null;
|
|
MySqlTransaction sqlTransaction=null;
|
|
|
|
try
|
|
{
|
|
if (null == headlines || 0 == headlines.Count) return false;
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
|
|
headlines=new Headlines(headlines.Distinct(new HeadlinesEqualityComparer()).ToList());
|
|
foreach(Headline headline in headlines)
|
|
{
|
|
if(!HeadlineExists(headline,sqlConnection,sqlTransaction))InsertHeadline(headline,sqlConnection,sqlTransaction);
|
|
}
|
|
sqlTransaction.Commit();
|
|
return true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if(null!=sqlTransaction)sqlTransaction.Dispose();
|
|
if(null!=sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
private static bool InsertHeadline(Headline headline,MySqlConnection sqlConnection,MySqlTransaction sqlTransaction)
|
|
{
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
if (null == headline || null == headline.Symbol || null==headline.Entry) return false;
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("insert into Headlines(symbol,asof,headline,source,modified) values(");
|
|
sb.Append(SqlUtils.AddQuotes(headline.Symbol)).Append(",");
|
|
sb.Append(SqlUtils.AddQuotes(SqlUtils.SqlDate(headline.Date))).Append(",");
|
|
sb.Append(SqlUtils.AddQuotes(SqlUtils.SqlString(headline.Entry))).Append(",");
|
|
sb.Append(SqlUtils.AddQuotes(SqlUtils.SqlString(headline.Source))).Append(",");
|
|
if(Utility.IsEpoch(headline.Modified))sb.Append(SqlUtils.AddQuotes(SqlUtils.ToSqlDateTime(DateTime.Now)));
|
|
else sb.Append(SqlUtils.AddQuotes(SqlUtils.ToSqlDateTime(headline.Modified)));
|
|
sb.Append(")");
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlCommand.ExecuteNonQuery();
|
|
return true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Query was '{0}'",strQuery));
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
}
|
|
}
|
|
private static bool HeadlineExists(Headline headline,MySqlConnection sqlConnection,MySqlTransaction sqlTransaction)
|
|
{
|
|
MySqlDataReader sqlDataReader=null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
if (null == headline || null == headline.Symbol) return false;
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("select count(*) from headlines where ");
|
|
sb.Append(" asof=").Append(SqlUtils.AddQuotes(SqlUtils.SqlDate(headline.Date))).Append(" and ");
|
|
sb.Append(" headline=").Append(SqlUtils.AddQuotes(SqlUtils.SqlString(headline.Entry)));
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader=sqlCommand.ExecuteReader();
|
|
if(!sqlDataReader.Read())return false;
|
|
return 0==sqlDataReader.GetInt32(0)?false:true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if(null!=sqlDataReader){sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
}
|
|
}
|
|
}
|
|
}
|