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
      {
      }
    }
  }
}