250 lines
11 KiB
C#
250 lines
11 KiB
C#
using System;
|
|
using System.Text;
|
|
using System.Diagnostics;
|
|
using System.Collections.Generic;
|
|
using MySql.Data.MySqlClient;
|
|
using MarketData.MarketDataModel;
|
|
using MarketData.Utils;
|
|
|
|
namespace MarketData.DataAccess
|
|
{
|
|
// Persistence for Zacks Earnings Announcements
|
|
public class EarningsAnnouncementsDA
|
|
{
|
|
private EarningsAnnouncementsDA()
|
|
{
|
|
}
|
|
public static DateTime? GetNextEarningsDate(String symbol)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
List<DateTime> earningsDates=new List<DateTime>();
|
|
DateTime currentDate=DateTime.Now.Date;
|
|
DateGenerator dateGenerator=new DateGenerator();
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select date from earningsannouncements where symbol=").Append(SqlUtils.AddQuotes(symbol)).Append(" order by date desc limit 2");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
earningsDates.Add(sqlDataReader.GetDateTime(0));
|
|
}
|
|
if(2!=earningsDates.Count)return null;
|
|
DateTime latestAnnouncementDate=earningsDates[0];
|
|
DateTime previousAnnouncementDate=earningsDates[1];
|
|
if(latestAnnouncementDate>currentDate)return latestAnnouncementDate;
|
|
TimeSpan daysBetween=latestAnnouncementDate-previousAnnouncementDate;
|
|
return dateGenerator.GetNextBusinessDay(latestAnnouncementDate+daysBetween);
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static EarningsAnnouncements GetEarningsAnnouncements(String symbol)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
EarningsAnnouncements earningsAnnouncements=new EarningsAnnouncements();
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select symbol,date,period_ending,estimate,reported,surprise,surprise_pct,time from earningsannouncements where symbol=").Append(SqlUtils.AddQuotes(symbol)).Append(" order by date desc");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
EarningsAnnouncement earningsAnnouncement=new EarningsAnnouncement();
|
|
earningsAnnouncement.Symbol=symbol;
|
|
earningsAnnouncement.Date=sqlDataReader.GetDateTime(1);
|
|
if(!sqlDataReader.IsDBNull(2))earningsAnnouncement.PeriodEnding=sqlDataReader.GetDateTime(2);
|
|
if(!sqlDataReader.IsDBNull(3))earningsAnnouncement.Estimate=sqlDataReader.GetDouble(3);
|
|
if(!sqlDataReader.IsDBNull(4))earningsAnnouncement.Reported=sqlDataReader.GetDouble(4);
|
|
if(!sqlDataReader.IsDBNull(5))earningsAnnouncement.Surprise=sqlDataReader.GetDouble(5);
|
|
if(!sqlDataReader.IsDBNull(6))earningsAnnouncement.SurprisePct=sqlDataReader.GetDouble(6);
|
|
if(!sqlDataReader.IsDBNull(7))earningsAnnouncement.Time=sqlDataReader.GetString(7);
|
|
earningsAnnouncements.Add(earningsAnnouncement);
|
|
}
|
|
return earningsAnnouncements;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return earningsAnnouncements;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static EarningsAnnouncement GetLatestEarningsAnnouncement(String symbol)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
EarningsAnnouncement earningsAnnouncement=null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select symbol,date,period_ending,estimate,reported,surprise,surprise_pct,time from earningsannouncements where symbol=").Append(SqlUtils.AddQuotes(symbol));
|
|
sb.Append("and date=(select max(date) from earningsannouncements where symbol=").Append(SqlUtils.AddQuotes(symbol)).Append(")");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
if(!sqlDataReader.Read())return null;
|
|
earningsAnnouncement=new EarningsAnnouncement();
|
|
earningsAnnouncement.Symbol=symbol;
|
|
earningsAnnouncement.Date=sqlDataReader.GetDateTime(1);
|
|
if(!sqlDataReader.IsDBNull(2))earningsAnnouncement.PeriodEnding=sqlDataReader.GetDateTime(2);
|
|
if(!sqlDataReader.IsDBNull(3))earningsAnnouncement.Estimate=sqlDataReader.GetDouble(3);
|
|
if(!sqlDataReader.IsDBNull(4))earningsAnnouncement.Reported=sqlDataReader.GetDouble(4);
|
|
if(!sqlDataReader.IsDBNull(5))earningsAnnouncement.Surprise=sqlDataReader.GetDouble(5);
|
|
if(!sqlDataReader.IsDBNull(6))earningsAnnouncement.SurprisePct=sqlDataReader.GetDouble(6);
|
|
if(!sqlDataReader.IsDBNull(7))earningsAnnouncement.Time=sqlDataReader.GetString(7);
|
|
return earningsAnnouncement;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static bool InsertEarningsAnnouncements(EarningsAnnouncements earningsAnnouncements)
|
|
{
|
|
MySqlCommand sqlCommand=null;
|
|
MySqlConnection sqlConnection=null;
|
|
MySqlTransaction sqlTransaction=null;
|
|
|
|
try
|
|
{
|
|
if (null == earningsAnnouncements || 0 == earningsAnnouncements.Count) return false;
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
|
|
foreach(EarningsAnnouncement earningsAnnouncement in earningsAnnouncements)
|
|
{
|
|
if(!EarningsAnnouncementExists(earningsAnnouncement,sqlConnection,sqlTransaction))InsertEarningsAnnouncement(earningsAnnouncement,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 InsertEarningsAnnouncement(EarningsAnnouncement earningsAnnouncement,MySqlConnection sqlConnection,MySqlTransaction sqlTransaction)
|
|
{
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
if (null == earningsAnnouncement || null == earningsAnnouncement.Symbol || Utility.IsEpoch(earningsAnnouncement.Date) || Utility.IsEpoch(earningsAnnouncement.PeriodEnding)) return false;
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("insert into EarningsAnnouncements(symbol,date,period_ending,estimate,reported,surprise,surprise_pct,time,modified) values(");
|
|
sb.Append(SqlUtils.AddQuotes(earningsAnnouncement.Symbol)).Append(",");
|
|
sb.Append(SqlUtils.AddQuotes(SqlUtils.SqlDate(earningsAnnouncement.Date))).Append(",");
|
|
sb.Append(SqlUtils.AddQuotes(SqlUtils.SqlDate(earningsAnnouncement.PeriodEnding))).Append(",");
|
|
if(double.IsNaN(earningsAnnouncement.Estimate))sb.Append("null").Append(",");
|
|
else sb.Append(earningsAnnouncement.Estimate).Append(",");
|
|
if(double.IsNaN(earningsAnnouncement.Reported))sb.Append("null").Append(",");
|
|
else sb.Append(earningsAnnouncement.Reported).Append(",");
|
|
if(double.IsNaN(earningsAnnouncement.Surprise))sb.Append("null").Append(",");
|
|
else sb.Append(earningsAnnouncement.Surprise).Append(",");
|
|
if(double.IsNaN(earningsAnnouncement.SurprisePct))sb.Append("null").Append(",");
|
|
else sb.Append(earningsAnnouncement.SurprisePct).Append(",");
|
|
if(null==earningsAnnouncement.Time)sb.Append("null").Append(",");
|
|
else sb.Append(SqlUtils.AddQuotes(earningsAnnouncement.Time)).Append(",");
|
|
sb.Append(SqlUtils.AddQuotes(SqlUtils.ToSqlDateTime(DateTime.Now)));
|
|
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 EarningsAnnouncementExists(EarningsAnnouncement earningsAnnouncement,MySqlConnection sqlConnection,MySqlTransaction sqlTransaction)
|
|
{
|
|
MySqlDataReader sqlDataReader=null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
if (null == earningsAnnouncement || null == earningsAnnouncement.Symbol) return false;
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("select count(*) from earningsannouncements where symbol=").Append(SqlUtils.AddQuotes(earningsAnnouncement.Symbol)).Append(" and ");
|
|
sb.Append(" date=").Append(SqlUtils.AddQuotes(SqlUtils.SqlDate(earningsAnnouncement.Date)));
|
|
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();}
|
|
}
|
|
}
|
|
}
|
|
}
|