282 lines
11 KiB
C#
282 lines
11 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using MySql.Data.MySqlClient;
|
|
using MarketData.MarketDataModel;
|
|
using MarketData.Utils;
|
|
|
|
namespace MarketData.DataAccess
|
|
{
|
|
public class ZacksRankDA
|
|
{
|
|
private ZacksRankDA()
|
|
{
|
|
}
|
|
|
|
// Get the latest rank for symbol that falls on or before the specified date
|
|
public static Dictionary<String,ZacksRank> GetZacksRankOnOrBefore(List<String> symbols, DateTime maxDate)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
Dictionary<String,ZacksRank> zacksRanks = new Dictionary<String,ZacksRank>();
|
|
|
|
try
|
|
{
|
|
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("SELECT B.symbol, B.zacks_rank, B.date, B.type ");
|
|
sb.Append("FROM (SELECT symbol,zacks_rank,date,type , ROW_NUMBER() OVER ");
|
|
sb.Append("(PARTITION BY symbol ORDER BY date desc) AS rownum ");
|
|
sb.Append("FROM zacksrank WHERE symbol IN ").Append(SqlUtils.CreateInClause(symbols)).Append(" and DATE<=").Append(SqlUtils.ToSqlDate(maxDate,true)).Append(")B ");
|
|
sb.Append("WHERE B.rownum<=1");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
|
|
while(sqlDataReader.Read())
|
|
{
|
|
ZacksRank zacksRank=new ZacksRank();
|
|
zacksRank.Symbol=sqlDataReader.GetString(0);
|
|
zacksRank.Rank=sqlDataReader.GetString(1);
|
|
zacksRank.Date=sqlDataReader.GetDateTime(2);
|
|
if(!sqlDataReader.IsDBNull(3))zacksRank.Type=sqlDataReader.GetString(3);
|
|
if(!zacksRanks.ContainsKey(zacksRank.Symbol))
|
|
{
|
|
zacksRanks.Add(zacksRank.Symbol, zacksRank);
|
|
}
|
|
}
|
|
return zacksRanks;
|
|
}
|
|
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();
|
|
}
|
|
}
|
|
|
|
// Get the latest rank for symbol that falls on or before the specified date
|
|
public static ZacksRank GetZacksRankOnOrBefore(String symbol, DateTime date)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
DateTime? maxDate=GetMaxDateOnOrBefore(symbol,date);
|
|
if(null==maxDate)return null;
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select symbol,zacks_rank,date,type from zacksrank where symbol=");
|
|
sb.Append("'").Append(symbol).Append("'").Append(" ");
|
|
sb.Append("and date=").Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(maxDate.Value)).Append("'");
|
|
sb.Append(" limit 1").Append(";");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
if (!sqlDataReader.Read()) return null;
|
|
ZacksRank zacksRank=new ZacksRank();
|
|
zacksRank.Symbol=sqlDataReader.GetString(0);
|
|
zacksRank.Rank=sqlDataReader.GetString(1);
|
|
zacksRank.Date=sqlDataReader.GetDateTime(2);
|
|
if(!sqlDataReader.IsDBNull(3))zacksRank.Type=sqlDataReader.GetString(3);
|
|
return zacksRank;
|
|
}
|
|
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();
|
|
}
|
|
}
|
|
|
|
// Get the most recent rank date on or before the given date for specified symbol
|
|
private static DateTime? GetMaxDateOnOrBefore(String symbol, DateTime date)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
DateTime? maxDate=null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select date from zacksrank where symbol=");
|
|
sb.Append("'").Append(symbol).Append("'").Append(" ");
|
|
sb.Append("and date<=").Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(date)).Append("' ");
|
|
sb.Append(" order by date desc ");
|
|
sb.Append(" limit 1").Append(";");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
if (!sqlDataReader.Read()) return null;
|
|
if(!sqlDataReader.IsDBNull(0))maxDate=sqlDataReader.GetDateTime(0);
|
|
return maxDate;
|
|
}
|
|
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();
|
|
}
|
|
}
|
|
|
|
// Get the latest rank for symbol
|
|
public static ZacksRank GetZacksRank(String symbol)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select date,symbol,zacks_rank,type from zacksrank where symbol=");
|
|
sb.Append("'").Append(symbol).Append("'").Append(" ");
|
|
sb.Append("and date=").Append(" (select max(date) from zacksrank where symbol='").Append(symbol).Append("')");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
if (!sqlDataReader.Read()) return null;
|
|
ZacksRank zacksRank=new ZacksRank();
|
|
zacksRank.Date=sqlDataReader.GetDateTime(0);
|
|
zacksRank.Symbol=sqlDataReader.GetString(1);
|
|
zacksRank.Rank=sqlDataReader.GetString(2);
|
|
if(!sqlDataReader.IsDBNull(3))zacksRank.Type=sqlDataReader.GetString(3);
|
|
return zacksRank;
|
|
}
|
|
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();
|
|
}
|
|
}
|
|
|
|
// Insert a rank if the latest rank that we have is different from the one to be added or we do not have a rank
|
|
public static bool InsertZacksRank(ZacksRank zacksRank)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlTransaction sqlTransaction = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
if(null==zacksRank||null==zacksRank.Symbol||null==zacksRank.Rank||"".Equals(zacksRank.Rank))return false;
|
|
ZacksRank latestRank=GetZacksRank(zacksRank.Symbol);
|
|
if(null!=latestRank&&latestRank.Rank.Equals(zacksRank.Rank))return true;
|
|
zacksRank.Type=GetChangeType(latestRank,zacksRank);
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
|
|
DeleteZacksRank(zacksRank, sqlConnection, sqlTransaction);
|
|
sqlTransaction.Commit();
|
|
sqlTransaction = sqlConnection.BeginTransaction();
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("insert into zacksrank (symbol,date,zacks_rank,type) ");
|
|
sb.Append("values(");
|
|
sb.Append("'").Append(zacksRank.Symbol).Append("'").Append(",");
|
|
sb.Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(zacksRank.Date)).Append("'").Append(",");
|
|
sb.Append("'").Append(zacksRank.Rank).Append("'").Append(",");
|
|
if(null==zacksRank.Type)sb.Append("null");
|
|
else sb.Append("'").Append(zacksRank.Type).Append("'");
|
|
sb.Append(")");
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlCommand.ExecuteNonQuery();
|
|
sqlTransaction.Commit();
|
|
return true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
MDTrace.WriteLine(LogLevel.DEBUG,"Query was '" + strQuery + "'");
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if(null!=sqlTransaction)sqlTransaction.Dispose();
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
|
|
private static String GetChangeType(ZacksRank latestRank,ZacksRank newRank)
|
|
{
|
|
if(null==latestRank)return "Initial";
|
|
if(null==latestRank.Rank||null==newRank||null==newRank.Rank)return null;
|
|
int latestRankNumber=int.Parse(latestRank.Rank.Substring(0,1));
|
|
int newRankNumber=int.Parse(newRank.Rank.Substring(0,1));
|
|
if(newRankNumber>latestRankNumber)return "Downgrades";
|
|
else if(newRankNumber<latestRankNumber)return "Upgrades";
|
|
return "NoChange";
|
|
|
|
}
|
|
|
|
// Delete a ranking on symbol and date
|
|
private static bool DeleteZacksRank(ZacksRank zacksRank,MySqlConnection sqlConnection,MySqlTransaction sqlTransaction)
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
if(null==zacksRank||null==zacksRank.Symbol||null==zacksRank.Rank||"".Equals(zacksRank.Rank))return false;
|
|
sb.Append("delete from zacksrank where ");
|
|
sb.Append("symbol='").Append(zacksRank.Symbol).Append("'");
|
|
sb.Append(" and ");
|
|
sb.Append("date='").Append(Utility.DateTimeToStringYYYYHMMHDD(zacksRank.Date)).Append("'");
|
|
strQuery = sb.ToString();
|
|
MySqlCommand sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlCommand.ExecuteNonQuery();
|
|
sqlCommand.Dispose();
|
|
return true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
sqlTransaction.Rollback();
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
if (null != strQuery) MDTrace.WriteLine(LogLevel.DEBUG,"Query was " + strQuery);
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
}
|
|
}
|
|
}
|
|
} |