639 lines
28 KiB
C#
Executable File
639 lines
28 KiB
C#
Executable File
using System;
|
|
using System.Text;
|
|
using MySql.Data.MySqlClient;
|
|
using MarketData.MarketDataModel;
|
|
using MarketData.Utils;
|
|
|
|
namespace MarketData.DataAccess
|
|
{
|
|
public class AnalystRatingsDA
|
|
{
|
|
private AnalystRatingsDA()
|
|
{
|
|
}
|
|
|
|
public static AnalystRatings GetAnalystRatings()
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand =null;
|
|
AnalystRatings analystRatings = new AnalystRatings();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select A.* from ");
|
|
sb.Append("(select date,symbol,upper(company),brokerage_firm,type,ratings_change,price_target from analystratings ");
|
|
sb.Append(" union ");
|
|
sb.Append(" select zr.date,zr.symbol,upper(sm.company) as company,'Zacks' as brokerage_firm,zr.type,zr.zacks_rank as ratings_change,null as price_target from zacksrank zr ");
|
|
sb.Append(" left join securitymaster sm on sm.symbol=zr.symbol ");
|
|
sb.Append(" where zr.symbol=@symbol and zr.type is not null ");
|
|
sb.Append(" )A ");
|
|
sb.Append(" order by date desc ");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
AnalystRating analystRating = new AnalystRating();
|
|
analystRating.Date = sqlDataReader.GetDateTime(0);
|
|
analystRating.Symbol = sqlDataReader.GetString(1);
|
|
analystRating.CompanyName = sqlDataReader.GetString(2);
|
|
analystRating.BrokerageFirm = sqlDataReader.GetString(3);
|
|
analystRating.Type = sqlDataReader.GetString(4);
|
|
analystRating.RatingsChange = sqlDataReader.GetString(5);
|
|
if (!sqlDataReader.IsDBNull(6)) analystRating.PriceTarget = sqlDataReader.GetDouble(6);
|
|
analystRatings.Add(analystRating);
|
|
}
|
|
analystRatings.CalculateUpdatePercentiles();
|
|
return analystRatings;
|
|
}
|
|
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 AnalystRatings GetAnalystRatings(String symbol, DateTime minDate,DateTime maxDate)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand =null;
|
|
AnalystRatings analystRatings = new AnalystRatings();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("set @symbol='").Append(symbol).Append("';");
|
|
sb.Append("set @minDate='").Append(Utility.DateTimeToStringYYYYHMMHDD(minDate)).Append("';");
|
|
sb.Append("set @maxDate='").Append(Utility.DateTimeToStringYYYYHMMHDD(maxDate)).Append("';");
|
|
sb.Append(" select A.* from ");
|
|
sb.Append(" (select date,symbol,upper(company),brokerage_firm,type,ratings_change,price_target from analystratings where symbol=@symbol ");
|
|
sb.Append(" union ");
|
|
sb.Append(" select zr.date,zr.symbol,upper(sm.company) as company,'Zacks' as brokerage_firm,zr.type,zr.zacks_rank as ratings_change,null as price_target from zacksrank zr ");
|
|
sb.Append(" left join securitymaster sm on sm.symbol=zr.symbol ");
|
|
sb.Append(" where zr.symbol=@symbol and zr.type is not null ");
|
|
sb.Append(" )A ");
|
|
sb.Append(" where A.date>=@minDate and A.date<=@maxDate ");
|
|
sb.Append(" order by date ");
|
|
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
AnalystRating analystRating = new AnalystRating();
|
|
analystRating.Date = sqlDataReader.GetDateTime(0);
|
|
analystRating.Symbol = sqlDataReader.GetString(1);
|
|
analystRating.CompanyName = sqlDataReader.GetString(2);
|
|
analystRating.BrokerageFirm = sqlDataReader.GetString(3);
|
|
analystRating.Type = sqlDataReader.GetString(4);
|
|
analystRating.RatingsChange = sqlDataReader.GetString(5);
|
|
if (!sqlDataReader.IsDBNull(6)) analystRating.PriceTarget = sqlDataReader.GetDouble(6);
|
|
analystRatings.Add(analystRating);
|
|
}
|
|
analystRatings.CalculateUpdatePercentiles();
|
|
return analystRatings;
|
|
}
|
|
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 DateTime GetMaxDateNoZacks()
|
|
{
|
|
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 max(date) from analystratings where brokerage_firm<>'Zacks'");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
if (!sqlDataReader.Read()) return Utility.Epoch;
|
|
return sqlDataReader.GetDateTime(0);
|
|
}
|
|
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 AnalystRatings GetAnalystRatingsMaxDateNoZacks(String symbol,DateTime maxDate)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand =null;
|
|
AnalystRatings analystRatings = new AnalystRatings();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select date,symbol,upper(company),brokerage_firm,type,ratings_change,price_target from analystratings ");
|
|
sb.Append(" where symbol='").Append(symbol).Append("'").Append(" and ");
|
|
sb.Append(" date<=").Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(maxDate)).Append("'").Append(" ");
|
|
sb.Append("order by date");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
AnalystRating analystRating = new AnalystRating();
|
|
analystRating.Date = sqlDataReader.GetDateTime(0);
|
|
analystRating.Symbol = sqlDataReader.GetString(1);
|
|
analystRating.CompanyName = sqlDataReader.GetString(2);
|
|
analystRating.BrokerageFirm = sqlDataReader.GetString(3);
|
|
analystRating.Type = sqlDataReader.GetString(4);
|
|
analystRating.RatingsChange = sqlDataReader.GetString(5);
|
|
if (!sqlDataReader.IsDBNull(6)) analystRating.PriceTarget = sqlDataReader.GetDouble(6);
|
|
analystRatings.Add(analystRating);
|
|
}
|
|
analystRatings.CalculateUpdatePercentiles();
|
|
return analystRatings;
|
|
}
|
|
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 Dictionary<String,AnalystRatings> GetAnalystRatingsDowngradesMaxDateNoZacks(List<String> symbols,DateTime maxDate)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand =null;
|
|
Dictionary<String,AnalystRatings> analystRatings = new Dictionary<String,AnalystRatings>();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
if (null == symbols || 0 == symbols.Count) return analystRatings;
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("SELECT B.date, B.symbol,B.company, B.brokerage_firm, B.type, B.ratings_change, B.price_target from ");
|
|
sb.Append("(SELECT date, symbol, upper(company) AS company, brokerage_firm, TYPE, ratings_change, price_target, ROW_NUMBER() OVER (");
|
|
sb.Append(" PARTITION BY symbol ORDER BY DATE DESC) AS rownum from analystratings ");
|
|
sb.Append("WHERE symbol IN ").Append(SqlUtils.CreateInClause(symbols)).Append(" AND date<=").Append(SqlUtils.ToSqlDate(maxDate,true));
|
|
sb.Append(" AND type='Downgrades')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())
|
|
{
|
|
AnalystRating analystRating = new AnalystRating();
|
|
analystRating.Date = sqlDataReader.GetDateTime(0);
|
|
analystRating.Symbol = sqlDataReader.GetString(1);
|
|
analystRating.CompanyName = sqlDataReader.GetString(2);
|
|
analystRating.BrokerageFirm = sqlDataReader.GetString(3);
|
|
analystRating.Type = sqlDataReader.GetString(4);
|
|
analystRating.RatingsChange = sqlDataReader.GetString(5);
|
|
if (!sqlDataReader.IsDBNull(6)) analystRating.PriceTarget = sqlDataReader.GetDouble(6);
|
|
if(!analystRatings.ContainsKey(analystRating.Symbol))
|
|
{
|
|
analystRatings.Add(analystRating.Symbol,new AnalystRatings());
|
|
}
|
|
analystRatings[analystRating.Symbol].Add(analystRating);
|
|
}
|
|
return analystRatings;
|
|
}
|
|
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 AnalystRatings GetAnalystRatingsMaxDate(String symbol,DateTime maxDate)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand =null;
|
|
AnalystRatings analystRatings = new AnalystRatings();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("set @symbol='").Append(symbol).Append("';");
|
|
sb.Append("set @maxDate='").Append(Utility.DateTimeToStringYYYYHMMHDD(maxDate)).Append("';");
|
|
sb.Append(" select A.* from ");
|
|
sb.Append(" (select date,symbol,upper(company),brokerage_firm,type,ratings_change,price_target from analystratings where symbol=@symbol ");
|
|
sb.Append(" union ");
|
|
sb.Append(" select zr.date,zr.symbol,upper(sm.company) as company,'Zacks' as brokerage_firm,zr.type,zr.zacks_rank as ratings_change,null as price_target from zacksrank zr ");
|
|
sb.Append(" left join securitymaster sm on sm.symbol=zr.symbol ");
|
|
sb.Append(" where zr.symbol=@symbol and zr.type is not null ");
|
|
sb.Append(" )A ");
|
|
sb.Append(" where A.date<=@maxDate ");
|
|
sb.Append(" order by date");
|
|
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
AnalystRating analystRating = new AnalystRating();
|
|
analystRating.Date = sqlDataReader.GetDateTime(0);
|
|
analystRating.Symbol = sqlDataReader.GetString(1);
|
|
analystRating.CompanyName = sqlDataReader.GetString(2);
|
|
analystRating.BrokerageFirm = sqlDataReader.GetString(3);
|
|
analystRating.Type = sqlDataReader.GetString(4);
|
|
analystRating.RatingsChange = sqlDataReader.GetString(5);
|
|
if (!sqlDataReader.IsDBNull(6)) analystRating.PriceTarget = sqlDataReader.GetDouble(6);
|
|
analystRatings.Add(analystRating);
|
|
}
|
|
analystRatings.CalculateUpdatePercentiles();
|
|
return analystRatings;
|
|
}
|
|
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 AnalystRatings GetAnalystRatings(String symbol, DateTime date)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand =null;
|
|
AnalystRatings analystRatings = new AnalystRatings();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("set @symbol='").Append(symbol).Append("';");
|
|
sb.Append("set @date='").Append(Utility.DateTimeToStringYYYYHMMHDD(date)).Append("';");
|
|
sb.Append(" select A.* from ");
|
|
sb.Append(" (select date,symbol,upper(company),brokerage_firm,type,ratings_change,price_target from analystratings where symbol=@symbol ");
|
|
sb.Append(" union ");
|
|
sb.Append(" select zr.date,zr.symbol,upper(sm.company) as company,'Zacks' as brokerage_firm,zr.type,zr.zacks_rank as ratings_change,null as price_target from zacksrank zr ");
|
|
sb.Append(" left join securitymaster sm on sm.symbol=zr.symbol ");
|
|
sb.Append(" where zr.symbol=@symbol and zr.type is not null ");
|
|
sb.Append(" )A ");
|
|
sb.Append(" where A.date=@date ");
|
|
sb.Append(" order by A.symbol");
|
|
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
AnalystRating analystRating = new AnalystRating();
|
|
analystRating.Date = sqlDataReader.GetDateTime(0);
|
|
analystRating.Symbol = sqlDataReader.GetString(1);
|
|
analystRating.CompanyName = sqlDataReader.GetString(2);
|
|
analystRating.BrokerageFirm = sqlDataReader.GetString(3);
|
|
analystRating.Type = sqlDataReader.GetString(4);
|
|
analystRating.RatingsChange = sqlDataReader.GetString(5);
|
|
if (!sqlDataReader.IsDBNull(6)) analystRating.PriceTarget = sqlDataReader.GetDouble(6);
|
|
analystRatings.Add(analystRating);
|
|
}
|
|
analystRatings.CalculateUpdatePercentiles();
|
|
return analystRatings;
|
|
}
|
|
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 AnalystRatings GetAnalystRatings(DateTime date)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand =null;
|
|
AnalystRatings analystRatings = new AnalystRatings();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append(" select A.* from ");
|
|
sb.Append(" (select date,symbol,upper(company),brokerage_firm,type,ratings_change,price_target from analystratings ");
|
|
sb.Append(" union ");
|
|
sb.Append(" select zr.date,zr.symbol,upper(sm.company) as company,'Zacks' as brokerage_firm,zr.type,zr.zacks_rank as ratings_change,null as price_target from zacksrank zr ");
|
|
sb.Append(" left join securitymaster sm on sm.symbol=zr.symbol ");
|
|
sb.Append(" where zr.type is not null ");
|
|
sb.Append(" )A ");
|
|
sb.Append(" where A.date=").Append(SqlUtils.SqlDate(date,true));
|
|
sb.Append(" order by A.symbol; ");
|
|
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
AnalystRating analystRating = new AnalystRating();
|
|
analystRating.Date = sqlDataReader.GetDateTime(0);
|
|
analystRating.Symbol = sqlDataReader.GetString(1);
|
|
analystRating.CompanyName = sqlDataReader.GetString(2);
|
|
analystRating.BrokerageFirm = sqlDataReader.GetString(3);
|
|
analystRating.Type = sqlDataReader.GetString(4);
|
|
analystRating.RatingsChange = sqlDataReader.GetString(5);
|
|
if (!sqlDataReader.IsDBNull(6)) analystRating.PriceTarget = sqlDataReader.GetDouble(6);
|
|
analystRatings.Add(analystRating);
|
|
}
|
|
analystRatings.CalculateUpdatePercentiles();
|
|
return analystRatings;
|
|
}
|
|
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 AnalystRatings GetAnalystRatings(String symbol)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
AnalystRatings analystRatings = new AnalystRatings();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
|
|
sb.Append(" set @symbol='").Append(symbol).Append("';");
|
|
sb.Append(" select A.* from ");
|
|
sb.Append(" (select date,symbol,upper(company),brokerage_firm,type,ratings_change,price_target from analystratings where symbol=@symbol ");
|
|
sb.Append(" union ");
|
|
sb.Append(" select zr.date,zr.symbol,upper(sm.company) as company,'Zacks' as brokerage_firm,zr.type,zr.zacks_rank as ratings_change,null as price_target from zacksrank zr ");
|
|
sb.Append(" left join securitymaster sm on sm.symbol=zr.symbol ");
|
|
sb.Append(" where zr.symbol=@symbol and zr.type is not null ");
|
|
sb.Append(" )A ");
|
|
sb.Append(" order by date desc ");
|
|
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
AnalystRating analystRating = new AnalystRating();
|
|
analystRating.Date = sqlDataReader.GetDateTime(0);
|
|
analystRating.Symbol = sqlDataReader.GetString(1);
|
|
analystRating.CompanyName = sqlDataReader.GetString(2);
|
|
analystRating.BrokerageFirm = sqlDataReader.GetString(3);
|
|
analystRating.Type = sqlDataReader.GetString(4);
|
|
analystRating.RatingsChange = sqlDataReader.GetString(5);
|
|
if (!sqlDataReader.IsDBNull(6)) analystRating.PriceTarget = sqlDataReader.GetDouble(6);
|
|
analystRatings.Add(analystRating);
|
|
}
|
|
analystRatings.CalculateUpdatePercentiles();
|
|
return analystRatings;
|
|
}
|
|
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 List<String> GetAnalystRatingsDates()
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
List<String> analystRatingsDates = new List<String>();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select distinct date from analystratings order by date desc");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
DateTime ratingDate = sqlDataReader.GetDateTime(0);
|
|
analystRatingsDates.Add(Utility.DateTimeToStringMMHDDHYYYY(ratingDate));
|
|
}
|
|
return analystRatingsDates;
|
|
}
|
|
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 InsertAnalystRatings(AnalystRatings analystRatings)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlTransaction sqlTransaction = null;
|
|
MySqlCommand sqlCommand =null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
if (null == analystRatings || 0 == analystRatings.Count) return true;
|
|
analystRatings = new AnalystRatings(analystRatings.DistinctBy(x => new {x.Symbol, x.Date.Date, x.BrokerageFirm}).ToList());
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
|
|
DeleteAnalystRatings(analystRatings, sqlConnection, sqlTransaction);
|
|
for (int index = 0; index < analystRatings.Count; index++)
|
|
{
|
|
AnalystRating analystRating = analystRatings[index];
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("insert into analystratings (date,symbol,company,brokerage_firm,type,ratings_change,price_target) ");
|
|
sb.Append("values(");
|
|
sb.Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(analystRating.Date)).Append("'").Append(",");
|
|
sb.Append("'").Append(analystRating.Symbol).Append("'").Append(",");
|
|
sb.Append("'").Append(SqlUtils.SqlString(analystRating.CompanyName)).Append("'").Append(",");
|
|
sb.Append("'").Append(SqlUtils.SqlString(analystRating.BrokerageFirm)).Append("'").Append(",");
|
|
sb.Append("'").Append(analystRating.Type).Append("'").Append(",");
|
|
sb.Append("'").Append(analystRating.RatingsChange).Append("'").Append(",");
|
|
if (!Double.IsNaN(analystRating.PriceTarget)) sb.Append(analystRating.PriceTarget);
|
|
else sb.Append("null");
|
|
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();
|
|
}
|
|
}
|
|
|
|
public static bool ContainsAnalystRating(AnalystRating analystRating)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand = null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("select count(*) from analystratings where ");
|
|
sb.Append("symbol='").Append(analystRating.Symbol).Append("'").Append(" and ");
|
|
sb.Append("date='").Append(Utility.DateTimeToStringYYYYHMMHDD(analystRating.Date)).Append("'").Append(" and ");
|
|
sb.Append("brokerage_firm='").Append(SqlUtils.SqlString(analystRating.BrokerageFirm)).Append("'");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
if (!sqlDataReader.Read()) return false;
|
|
int count = sqlDataReader.GetInt32(0);
|
|
return count > 0 ? true : false;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG, exception);
|
|
MDTrace.WriteLine(LogLevel.DEBUG, "Query was '" + strQuery + "'");
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
if (null != sqlDataReader) {sqlDataReader.Close(); sqlDataReader.Dispose();}
|
|
if (null != sqlCommand) sqlCommand.Dispose();
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
|
|
private static bool DeleteAnalystRatings(AnalystRatings analystRatings, MySqlConnection sqlConnection, MySqlTransaction sqlTransaction)
|
|
{
|
|
try
|
|
{
|
|
foreach (AnalystRating analystRating in analystRatings)
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
String strQuery = null;
|
|
sb.Append("delete from analystratings where ");
|
|
sb.Append("symbol='").Append(analystRating.Symbol).Append("'");
|
|
sb.Append(" and ");
|
|
sb.Append("date='").Append(Utility.DateTimeToStringYYYYHMMHDD(analystRating.Date)).Append("'").Append(" ");
|
|
sb.Append("and brokerage_firm='").Append(SqlUtils.SqlString(analystRating.BrokerageFirm)).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);
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|