using System.Text;
using MySql.Data.MySqlClient;
using MarketData.MarketDataModel;
using MarketData.Utils;

namespace MarketData.DataAccess
{
  public class FundamentalDA
  {
    private FundamentalDA()
    {
    }

    /// <summary>
    /// Gets a distinct list of asof dates from the fundamentals
    /// </summary>
    /// <returns></returns>
    public static List<DateTime> GetDistinctAsOf()
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand =null;
      List<DateTime> dates = new List<DateTime>();
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select distinct(asof) from fundamentals order by 1 desc");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read()) 
        {
          dates.Add(sqlDataReader.GetDateTime(0));
        }
        return dates;
      }
      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();
      }
    }

    /// <summary>
    /// Gets the symbols for a particular asof date
    /// </summary>
    /// <returns></returns>
    public static List<String> GetSymbolsAsOf(DateTime asof)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand =null;
      List<String> symbols = new List<String>();
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select symbol from fundamentals where asof =").Append(SqlUtils.ToSqlDate(asof,true)).Append(" order by 1 asc");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read()) 
        {
          symbols.Add(sqlDataReader.GetString(0));
        }
        return symbols;
      }
      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();
      }
    }

    /// <summary>
    /// Updates the beta36, beta24, and bet06 for a symbol for a specific date
    /// </summary>
    /// <returns></returns>
    public static bool UpdateBeta(String symbol,DateTime asof,double beta36, double beta24, double beta06)
    {
      MySqlConnection sqlConnection = null;
      MySqlTransaction sqlTransaction = null;
			MySqlCommand sqlCommand =null;

      List<String> symbols = new List<String>();
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        sb.Append("update fundamentals set ");

        sb.Append("beta_calc_36=");
        if (!Double.IsNaN(beta36)) sb.Append(beta36).Append(",");
        else sb.Append("null").Append(",");

        sb.Append("beta_calc_24=");
        if (!Double.IsNaN(beta24)) sb.Append(beta24).Append(",");
        else sb.Append("null").Append(",");


        sb.Append("beta_calc_06=");
        if (!Double.IsNaN(beta06)) sb.Append(beta06);
        else sb.Append("null");

        sb.Append(" where ");
        sb.Append("symbol='").Append(symbol).Append("' and asof =").Append(SqlUtils.ToSqlDate(asof,true));
        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);
        return false;
      }
      finally
      {
        if(null != sqlTransaction)sqlTransaction.Dispose();
				if(null != sqlCommand)sqlCommand.Dispose();
        if(null != sqlConnection) sqlConnection.Close();
      }
    }

    public static bool CheckFundamentalModifiedOn(String symbol,DateTime modified)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand =null;
      String strQuery = null;
      DateTime maxDate = DateTime.Parse("01-01-0001");

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select count(*) from fundamentals").Append(" ");
        sb.Append("where symbol='").Append(symbol).Append("'").Append(" ");
        sb.Append("and asof='").Append(Utility.DateTimeToStringYYYYHMMHDD(modified)).Append("'");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        sqlDataReader.Read();
        int recordCount = sqlDataReader.GetInt32(0);
        return 0 == recordCount ? 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();}
        if (null != sqlConnection) sqlConnection.Close();
      }
    }
    public static TimeSeriesCollection GetTotalCashMils(String symbol)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand =null;
      TimeSeriesCollection timeSeriesCollection=new TimeSeriesCollection();
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select symbol,asof,total_cash from fundamentals where symbol='").Append(symbol).Append("' order by asof desc");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read()) 
        {
          TimeSeriesElement timeSeriesElement=new TimeSeriesElement();
          timeSeriesElement.Symbol=sqlDataReader.GetString(0);
          timeSeriesElement.AsOf=sqlDataReader.GetDateTime(1);
          if(!sqlDataReader.IsDBNull(2))timeSeriesElement.Value=sqlDataReader.GetDouble(2)/1000000.00;
          else timeSeriesElement.Value=double.NaN;
          timeSeriesElement.Type=TimeSeriesElement.ElementType.OTHER;
          timeSeriesElement.OtherType="Total_Cash";
          if(double.IsNaN(timeSeriesElement.Value))continue;
          timeSeriesCollection.Add(timeSeriesElement);
        }
        return timeSeriesCollection;
      }
      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 TimeSeriesCollection GetPERatio(String symbol)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand =null;
      TimeSeriesCollection timeSeriesCollection=new TimeSeriesCollection();
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select symbol,asof,pe from fundamentals where symbol='").Append(symbol).Append("' order by asof desc");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read()) 
        {
          TimeSeriesElement timeSeriesElement=new TimeSeriesElement();
          timeSeriesElement.Symbol=sqlDataReader.GetString(0);
          timeSeriesElement.AsOf=sqlDataReader.GetDateTime(1);
          if(!sqlDataReader.IsDBNull(2))timeSeriesElement.Value=sqlDataReader.GetDouble(2);
          else timeSeriesElement.Value=double.NaN;
          timeSeriesElement.Type=TimeSeriesElement.ElementType.OTHER;
          timeSeriesElement.OtherType="PE";
          if(double.IsNaN(timeSeriesElement.Value))continue;
          timeSeriesCollection.Add(timeSeriesElement);
        }
        return timeSeriesCollection;
      }
      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();
      }
    }

    /// <summary>
    /// Retrieves a collection of timeseries for the given symbols with each symbol having the specified max asof and no more than maxSeries elements in the series
    /// </summary>
    /// <param name="symbols"></param>
    /// <param name="maxDate"></param>
    /// <param name="maxSeries"></param>
    /// <returns></returns>
    public static Dictionary<String,TimeSeriesCollection> GetEPS(List<String> symbols, DateTime maxDate,int maxSeries)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand = null;
      Dictionary<String,TimeSeriesCollection> timeSeriesCollection = new Dictionary<String,TimeSeriesCollection>();
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));

        sb.Append("SELECT B.symbol, B.asof, B.eps FROM ");
        sb.Append("(SELECT symbol, asof, eps, ROW_NUMBER() OVER(PARTITION BY symbol ORDER BY asof desc) AS rownum FROM fundamentals ");
        sb.Append($"WHERE symbol IN {SqlUtils.CreateInClause(symbols)} AND asof<={SqlUtils.ToSqlDate(maxDate,true)} )B ");
        sb.Append($"WHERE B.rownum<={maxSeries}");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          TimeSeriesElement timeSeriesElement = new TimeSeriesElement();
          timeSeriesElement.Symbol = sqlDataReader.GetString(0);
          timeSeriesElement.AsOf = sqlDataReader.GetDateTime(1);
          if (!sqlDataReader.IsDBNull(2)) timeSeriesElement.Value = sqlDataReader.GetDouble(2);
          else timeSeriesElement.Value = double.NaN;
          timeSeriesElement.Type = TimeSeriesElement.ElementType.OTHER;
          timeSeriesElement.OtherType = "EPS";
          if (double.IsNaN(timeSeriesElement.Value)) continue;
          if(!timeSeriesCollection.ContainsKey(timeSeriesElement.Symbol))
          {
            timeSeriesCollection.Add(timeSeriesElement.Symbol,new TimeSeriesCollection());
          }
          timeSeriesCollection[timeSeriesElement.Symbol].Add(timeSeriesElement);
        }
        return timeSeriesCollection;
      }
      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 Fundamental GetFundamental(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 symbol,asof,next_earnings_date,beta,low52,high52,volume,market_cap,pe,eps,peg,return_on_assets,return_on_equity,total_cash,total_debt,shares_outstanding,revenue,revenue_per_share,qtrly_revenue_growth,gross_profit,ebitda,net_income_available_to_common,book_value_per_share,operating_cashflow,leveraged_free_cashflow,book_value_per_share*shares_outstanding as equity,trailing_pe,ebit,enterprise_value,source,beta_calc_36,beta_calc_06,beta_calc_24 from fundamentals where symbol=");
        sb.Append("'").Append(symbol).Append("'").Append(" ");
        sb.Append("and asof=(select max(asof) from fundamentals 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;
        Fundamental fundamental = new Fundamental();
        fundamental.Symbol=sqlDataReader.GetString(0);
        fundamental.AsOf = sqlDataReader.GetDateTime(1);
        if (!sqlDataReader.IsDBNull(2)) fundamental.NextEarningsDate = sqlDataReader.GetDateTime(2);
        if (!sqlDataReader.IsDBNull(3)) fundamental.Beta = sqlDataReader.GetDouble(3);
        if (!sqlDataReader.IsDBNull(4)) fundamental.Low52 = sqlDataReader.GetDouble(4);
        if (!sqlDataReader.IsDBNull(5)) fundamental.High52 = sqlDataReader.GetDouble(5);
        if (!sqlDataReader.IsDBNull(6)) fundamental.Volume = sqlDataReader.GetInt64(6);
        if (!sqlDataReader.IsDBNull(7)) fundamental.MarketCap = sqlDataReader.GetDouble(7);
        if (!sqlDataReader.IsDBNull(8)) fundamental.PE = sqlDataReader.GetDouble(8);
        if (!sqlDataReader.IsDBNull(9)) fundamental.EPS = sqlDataReader.GetDouble(9);
        if (!sqlDataReader.IsDBNull(10)) fundamental.PEG = sqlDataReader.GetDouble(10);
        if (!sqlDataReader.IsDBNull(11)) fundamental.ReturnOnAssets = sqlDataReader.GetDouble(11);
        if (!sqlDataReader.IsDBNull(12)) fundamental.ReturnOnEquity = sqlDataReader.GetDouble(12);
        if (!sqlDataReader.IsDBNull(13)) fundamental.TotalCash = sqlDataReader.GetDouble(13);
        if (!sqlDataReader.IsDBNull(14)) fundamental.TotalDebt = sqlDataReader.GetDouble(14);
        if (!sqlDataReader.IsDBNull(15)) fundamental.SharesOutstanding = sqlDataReader.GetDouble(15);
        if (!sqlDataReader.IsDBNull(16)) fundamental.Revenue = sqlDataReader.GetDouble(16);
        if (!sqlDataReader.IsDBNull(17)) fundamental.RevenuePerShare = sqlDataReader.GetDouble(17);
        if (!sqlDataReader.IsDBNull(18)) fundamental.QtrlyRevenueGrowth = sqlDataReader.GetDouble(18);
        if (!sqlDataReader.IsDBNull(19)) fundamental.GrossProfit = sqlDataReader.GetDouble(19);
        if (!sqlDataReader.IsDBNull(20)) fundamental.EBITDA = sqlDataReader.GetDouble(20);
        if (!sqlDataReader.IsDBNull(21)) fundamental.NetIncomeAvailableToCommon = sqlDataReader.GetDouble(21);
        if (!sqlDataReader.IsDBNull(22)) fundamental.BookValuePerShare = sqlDataReader.GetDouble(22);
        if (!sqlDataReader.IsDBNull(23)) fundamental.OperatingCashflow = sqlDataReader.GetDouble(23);
        if (!sqlDataReader.IsDBNull(24)) fundamental.LeveragedFreeCashflow = sqlDataReader.GetDouble(24);
        if (!sqlDataReader.IsDBNull(25)) fundamental.Equity = sqlDataReader.GetDouble(25);
        if (!sqlDataReader.IsDBNull(26)) fundamental.TrailingPE = sqlDataReader.GetDouble(26);
        if (!sqlDataReader.IsDBNull(27)) fundamental.EBIT = sqlDataReader.GetDouble(27);
        if (!sqlDataReader.IsDBNull(28)) fundamental.EnterpriseValue = sqlDataReader.GetDouble(28);
        if (!sqlDataReader.IsDBNull(29)) fundamental.Source = sqlDataReader.GetString(29);
        if (!sqlDataReader.IsDBNull(30)) fundamental.BetaCalc36 = sqlDataReader.GetDouble(30);
        if (!sqlDataReader.IsDBNull(31)) fundamental.BetaCalc06 = sqlDataReader.GetDouble(31);
        if (!sqlDataReader.IsDBNull(32)) fundamental.BetaCalc24 = sqlDataReader.GetDouble(32);
        BalanceSheet balanceSheet=BalanceSheetDA.GetBalanceSheetOnOrBefore(symbol,fundamental.AsOf,BalanceSheet.PeriodType.Annual);
        if(null!=balanceSheet&&!double.IsNaN(balanceSheet.TotalStockHolderEquity)&&!double.IsNaN(fundamental.TotalDebt)&&0!=fundamental.TotalDebt)fundamental.DebtToEquity=fundamental.TotalDebt/balanceSheet.TotalStockHolderEquity;
        return fundamental;
      }
      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 Fundamental GetFundamental(String symbol, DateTime asof)
    {
      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 symbol,asof,next_earnings_date,beta,low52,high52,volume,market_cap,pe,eps,peg,return_on_assets,return_on_equity,total_cash,total_debt,shares_outstanding,revenue,revenue_per_share,qtrly_revenue_growth,gross_profit,ebitda,net_income_available_to_common,book_value_per_share,operating_cashflow,leveraged_free_cashflow,book_value_per_share*shares_outstanding as equity,trailing_pe,ebit,enterprise_value,source,beta_calc_36,beta_calc_06,beta_calc_24 from fundamentals where symbol=");
        sb.Append("'").Append(symbol).Append("'").Append(" ");
        sb.Append("and asof=").Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(asof)).Append("'").Append(";");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        if (!sqlDataReader.Read()) return null;
        BalanceSheet balanceSheet=BalanceSheetDA.GetBalanceSheetOnOrBefore(symbol,asof,BalanceSheet.PeriodType.Annual);
        Fundamental fundamental = new Fundamental();
        fundamental.Symbol = sqlDataReader.GetString(0);
        fundamental.AsOf = sqlDataReader.GetDateTime(1);
        if (!sqlDataReader.IsDBNull(2)) fundamental.NextEarningsDate = sqlDataReader.GetDateTime(2);
        if (!sqlDataReader.IsDBNull(3)) fundamental.Beta = sqlDataReader.GetDouble(3);
        if (!sqlDataReader.IsDBNull(4)) fundamental.Low52 = sqlDataReader.GetDouble(4);
        if (!sqlDataReader.IsDBNull(5)) fundamental.High52 = sqlDataReader.GetDouble(5);
        if (!sqlDataReader.IsDBNull(6)) fundamental.Volume = sqlDataReader.GetInt64(6);
        if (!sqlDataReader.IsDBNull(7)) fundamental.MarketCap = sqlDataReader.GetDouble(7);
        if (!sqlDataReader.IsDBNull(8)) fundamental.PE = sqlDataReader.GetDouble(8);
        if (!sqlDataReader.IsDBNull(9)) fundamental.EPS = sqlDataReader.GetDouble(9);
        if (!sqlDataReader.IsDBNull(10)) fundamental.PEG = sqlDataReader.GetDouble(10);
        if (!sqlDataReader.IsDBNull(11)) fundamental.ReturnOnAssets = sqlDataReader.GetDouble(11);
        if (!sqlDataReader.IsDBNull(12)) fundamental.ReturnOnEquity = sqlDataReader.GetDouble(12);
        if (!sqlDataReader.IsDBNull(13)) fundamental.TotalCash = sqlDataReader.GetDouble(13);
        if (!sqlDataReader.IsDBNull(14)) fundamental.TotalDebt = sqlDataReader.GetDouble(14);
        if (!sqlDataReader.IsDBNull(15)) fundamental.SharesOutstanding = sqlDataReader.GetDouble(15);
        if (!sqlDataReader.IsDBNull(16)) fundamental.Revenue = sqlDataReader.GetDouble(16);
        if (!sqlDataReader.IsDBNull(17)) fundamental.RevenuePerShare = sqlDataReader.GetDouble(17);
        if (!sqlDataReader.IsDBNull(18)) fundamental.QtrlyRevenueGrowth = sqlDataReader.GetDouble(18);
        if (!sqlDataReader.IsDBNull(19)) fundamental.GrossProfit = sqlDataReader.GetDouble(19);
        if (!sqlDataReader.IsDBNull(20)) fundamental.EBITDA = sqlDataReader.GetDouble(20);
        if (!sqlDataReader.IsDBNull(21)) fundamental.NetIncomeAvailableToCommon = sqlDataReader.GetDouble(21);
        if (!sqlDataReader.IsDBNull(22)) fundamental.BookValuePerShare = sqlDataReader.GetDouble(22);
        if (!sqlDataReader.IsDBNull(23)) fundamental.OperatingCashflow = sqlDataReader.GetDouble(23);
        if (!sqlDataReader.IsDBNull(24)) fundamental.LeveragedFreeCashflow = sqlDataReader.GetDouble(24);
        if (!sqlDataReader.IsDBNull(25)) fundamental.Equity = sqlDataReader.GetDouble(25);
        if (!sqlDataReader.IsDBNull(26)) fundamental.TrailingPE = sqlDataReader.GetDouble(26);
        if (!sqlDataReader.IsDBNull(27)) fundamental.EBIT = sqlDataReader.GetDouble(27);
        if (!sqlDataReader.IsDBNull(28)) fundamental.EnterpriseValue = sqlDataReader.GetDouble(28);
        if (!sqlDataReader.IsDBNull(29)) fundamental.Source = sqlDataReader.GetString(29);
        if (!sqlDataReader.IsDBNull(30)) fundamental.BetaCalc36 = sqlDataReader.GetDouble(30);
        if (!sqlDataReader.IsDBNull(31)) fundamental.BetaCalc06 = sqlDataReader.GetDouble(31);
        if (!sqlDataReader.IsDBNull(32)) fundamental.BetaCalc24 = sqlDataReader.GetDouble(32);
        if (null != balanceSheet && !double.IsNaN(balanceSheet.TotalStockHolderEquity) && !double.IsNaN(fundamental.TotalDebt) && 0 != fundamental.TotalDebt)
        {
          fundamental.DebtToEquity=fundamental.TotalDebt/balanceSheet.TotalStockHolderEquity;
        }
        return fundamental;
      }
      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 maximum date on record on or before asof
    public static DateTime? GetMaxDateFromFundamental(String symbol,DateTime asof)
    {
      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 asof from fundamentals where symbol=");
        sb.Append("'").Append(symbol).Append("'").Append(" ");
        sb.Append("and asof<=").Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(asof)).Append("'");
        sb.Append(" order by asof desc ");
        sb.Append(" limit 1");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        if (!sqlDataReader.Read()) return maxDate;
        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();
      }
    }

    /// <summary>
    /// Retrieve latest MarketCap, PE, EBITDA, RevenuePerShare for all symbols with aasof being no more recent than the provided date
    /// Given a tradeDate of 04/18/2025 this method might return a collection similar to below.  The model returned is a subset of the fundamental 
    /// 07/15/2018 ^FTSE
    /// 03/13/2019 ^GSPC
    /// 04/17/2025 AA
    /// </summary>
    /// <param name="tradeDate">The as of date</param>
    /// <returns></returns>
    public static FundamentalsV2 GetFundamentalsMaxDateV2(DateTime tradeDate)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand=null;
      String strQuery = null;
      FundamentalsV2 fundamentals = new FundamentalsV2();

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("SELECT A.asof,A.symbol, A.market_cap,A.ebitda,A.pe,A.revenue_per_share,A.beta,A.beta_calc_36,A.beta_calc_06,A.beta_calc_24 FROM fundamentals A JOIN ");
        sb.Append("(SELECT MAX(asof) asof,symbol FROM fundamentals WHERE asof<=").Append("'");
        sb.Append(Utility.DateTimeToStringYYYYHMMHDD(tradeDate.Date));
        sb.Append("'");
        sb.Append(" GROUP BY symbol ORDER BY symbol ASC)B ");
        sb.Append(" ON A.asof=B.asof AND A.symbol=B.symbol ");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while(sqlDataReader.Read())
        {
          FundamentalV2 fundamental = new FundamentalV2();
          fundamental.AsOf = sqlDataReader.GetDateTime(0);
          fundamental.Symbol = sqlDataReader.GetString(1);
          if(!sqlDataReader.IsDBNull(2)) fundamental.MarketCap = sqlDataReader.GetDouble(2);
          if(!sqlDataReader.IsDBNull(3)) fundamental.EBITDA = sqlDataReader.GetDouble(3);
          if(!sqlDataReader.IsDBNull(4)) fundamental.PE = sqlDataReader.GetDouble(4);
          if(!sqlDataReader.IsDBNull(5)) fundamental.RevenuePerShare = sqlDataReader.GetDouble(5);
          if(!sqlDataReader.IsDBNull(6)) fundamental.Beta = sqlDataReader.GetDouble(6);
          if(!sqlDataReader.IsDBNull(7)) fundamental.BetaCalc36 = sqlDataReader.GetDouble(7);
          if(!sqlDataReader.IsDBNull(8)) fundamental.BetaCalc06 = sqlDataReader.GetDouble(8);
          if(!sqlDataReader.IsDBNull(9)) fundamental.BetaCalc24 = sqlDataReader.GetDouble(9);
          if(!fundamentals.ContainsKey(fundamental.Symbol))fundamentals.Add(fundamental.Symbol,fundamental);
        }
        return fundamentals;
      }
      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 Fundamental GetFundamentalMaxDate(String symbol, DateTime asof)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand=null;
      String strQuery = null;

      try
      {
        DateTime? maxDate=GetMaxDateFromFundamental(symbol,asof);   // get the maximum date on record on or before max date
        if(null==maxDate)return null;
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select symbol,asof,next_earnings_date,beta,low52,high52,volume,market_cap,pe,eps,peg,return_on_assets,return_on_equity,total_cash,total_debt,shares_outstanding,revenue,revenue_per_share,qtrly_revenue_growth,gross_profit,ebitda,net_income_available_to_common,book_value_per_share,operating_cashflow,leveraged_free_cashflow,book_value_per_share*shares_outstanding as equity,trailing_pe,ebit,enterprise_value,source,beta_calc_36,beta_calc_06,beta_calc_24 from fundamentals where symbol=");
        sb.Append("'").Append(symbol).Append("'").Append(" ");
        sb.Append("and asof=").Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(maxDate.Value)).Append("'");
        sb.Append(" limit 1");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        if (!sqlDataReader.Read()) return null;
        double totalStockHolderEquity=BalanceSheetDA.GetTotalStockHolderEquityOnOrBefore(symbol,asof,BalanceSheet.PeriodType.Annual);
        Fundamental fundamental = new Fundamental();
        fundamental.Symbol = sqlDataReader.GetString(0);
        fundamental.AsOf = sqlDataReader.GetDateTime(1);
        if (!sqlDataReader.IsDBNull(2)) fundamental.NextEarningsDate = sqlDataReader.GetDateTime(2);
        if (!sqlDataReader.IsDBNull(3)) fundamental.Beta = sqlDataReader.GetDouble(3);
        if (!sqlDataReader.IsDBNull(4)) fundamental.Low52 = sqlDataReader.GetDouble(4);
        if (!sqlDataReader.IsDBNull(5)) fundamental.High52 = sqlDataReader.GetDouble(5);
        if (!sqlDataReader.IsDBNull(6)) fundamental.Volume = sqlDataReader.GetInt64(6);
        if (!sqlDataReader.IsDBNull(7)) fundamental.MarketCap = sqlDataReader.GetDouble(7);
        if (!sqlDataReader.IsDBNull(8)) fundamental.PE = sqlDataReader.GetDouble(8);
        if (!sqlDataReader.IsDBNull(9)) fundamental.EPS = sqlDataReader.GetDouble(9);
        if (!sqlDataReader.IsDBNull(10)) fundamental.PEG = sqlDataReader.GetDouble(10);
        if (!sqlDataReader.IsDBNull(11)) fundamental.ReturnOnAssets = sqlDataReader.GetDouble(11);
        if (!sqlDataReader.IsDBNull(12)) fundamental.ReturnOnEquity = sqlDataReader.GetDouble(12);
        if (!sqlDataReader.IsDBNull(13)) fundamental.TotalCash = sqlDataReader.GetDouble(13);
        if (!sqlDataReader.IsDBNull(14)) fundamental.TotalDebt = sqlDataReader.GetDouble(14);
        if (!sqlDataReader.IsDBNull(15)) fundamental.SharesOutstanding = sqlDataReader.GetDouble(15);
        if (!sqlDataReader.IsDBNull(16)) fundamental.Revenue = sqlDataReader.GetDouble(16);
        if (!sqlDataReader.IsDBNull(17)) fundamental.RevenuePerShare = sqlDataReader.GetDouble(17);
        if (!sqlDataReader.IsDBNull(18)) fundamental.QtrlyRevenueGrowth = sqlDataReader.GetDouble(18);
        if (!sqlDataReader.IsDBNull(19)) fundamental.GrossProfit = sqlDataReader.GetDouble(19);
        if (!sqlDataReader.IsDBNull(20)) fundamental.EBITDA = sqlDataReader.GetDouble(20);
        if (!sqlDataReader.IsDBNull(21)) fundamental.NetIncomeAvailableToCommon = sqlDataReader.GetDouble(21);
        if (!sqlDataReader.IsDBNull(22)) fundamental.BookValuePerShare = sqlDataReader.GetDouble(22);
        if (!sqlDataReader.IsDBNull(23)) fundamental.OperatingCashflow = sqlDataReader.GetDouble(23);
        if (!sqlDataReader.IsDBNull(24)) fundamental.LeveragedFreeCashflow = sqlDataReader.GetDouble(24);
        if (!sqlDataReader.IsDBNull(25)) fundamental.Equity = sqlDataReader.GetDouble(25);
        if (!sqlDataReader.IsDBNull(26)) fundamental.TrailingPE = sqlDataReader.GetDouble(26);
        if (!sqlDataReader.IsDBNull(27)) fundamental.EBIT = sqlDataReader.GetDouble(27);
        if (!sqlDataReader.IsDBNull(28)) fundamental.EnterpriseValue = sqlDataReader.GetDouble(28);
        if (!sqlDataReader.IsDBNull(29)) fundamental.Source = sqlDataReader.GetString(29);
        if (!sqlDataReader.IsDBNull(30)) fundamental.BetaCalc36 = sqlDataReader.GetDouble(30);
        if (!sqlDataReader.IsDBNull(31)) fundamental.BetaCalc06 = sqlDataReader.GetDouble(31);
        if (!sqlDataReader.IsDBNull(32)) fundamental.BetaCalc24 = sqlDataReader.GetDouble(32);
        if (!double.IsNaN(totalStockHolderEquity) && !double.IsNaN(fundamental.TotalDebt))
        {
          if(0.00==totalStockHolderEquity)fundamental.TotalDebt=0.00;
          else fundamental.DebtToEquity=fundamental.TotalDebt/totalStockHolderEquity;
        }
        return fundamental;
      }
      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 InsertFundamentals(Fundamentals fundamentals)
    {
      MySqlConnection sqlConnection = null;
      MySqlTransaction sqlTransaction = null;
      String strQuery = null;

      try
      {
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        DeleteFundamentals(fundamentals, sqlConnection, sqlTransaction);
        for (int index = 0; index < fundamentals.Count; index++)
        {
          Fundamental fundamental = fundamentals[index];
          StringBuilder sb = new StringBuilder();
          sb.Append("insert into fundamentals (symbol,asof,next_earnings_date,beta,beta_calc_36,beta_calc_24,beta_calc_06,low52,high52,volume,market_cap,pe,eps,peg,return_on_assets,return_on_equity,total_cash,total_debt,shares_outstanding,revenue,revenue_per_share,qtrly_revenue_growth,gross_profit,ebitda,net_income_available_to_common,book_value_per_share,operating_cashflow,leveraged_free_cashflow,trailing_pe,ebit,enterprise_value,source) ");
          sb.Append("values(");
          sb.Append("'").Append(fundamental.Symbol).Append("'").Append(",");
          sb.Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(fundamental.AsOf)).Append("'").Append(",");
          if (Utility.IsEpoch(fundamental.NextEarningsDate)) sb.Append("null").Append(",");
          else sb.Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(fundamental.NextEarningsDate)).Append("'").Append(",");
          if (!Double.IsNaN(fundamental.Beta)) sb.Append(fundamental.Beta).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.BetaCalc36)) sb.Append(fundamental.BetaCalc36).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.BetaCalc24)) sb.Append(fundamental.BetaCalc24).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.BetaCalc06)) sb.Append(fundamental.BetaCalc06).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.Low52)) sb.Append(fundamental.Low52).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.High52)) sb.Append(fundamental.High52).Append(",");
          else sb.Append("null").Append(",");
          sb.Append(fundamental.Volume).Append(",");
          if (!Double.IsNaN(fundamental.MarketCap)) sb.Append(fundamental.MarketCap).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.PE)) sb.Append(fundamental.PE).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.EPS)) sb.Append(fundamental.EPS).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.PEG)) sb.Append(fundamental.PEG).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.ReturnOnAssets))sb.Append(fundamental.ReturnOnAssets).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.ReturnOnEquity)) sb.Append(fundamental.ReturnOnEquity).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.TotalCash)) sb.Append(fundamental.TotalCash).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.TotalDebt)) sb.Append(fundamental.TotalDebt).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.SharesOutstanding)) sb.Append(fundamental.SharesOutstanding).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.Revenue)) sb.Append(fundamental.Revenue).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.RevenuePerShare)) sb.Append(fundamental.RevenuePerShare).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.QtrlyRevenueGrowth)) sb.Append(fundamental.QtrlyRevenueGrowth).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.GrossProfit)) sb.Append(fundamental.GrossProfit).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.EBITDA)) sb.Append(fundamental.EBITDA).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.NetIncomeAvailableToCommon)) sb.Append(fundamental.NetIncomeAvailableToCommon).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.BookValuePerShare)) sb.Append(fundamental.BookValuePerShare).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.OperatingCashflow)) sb.Append(fundamental.OperatingCashflow).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.LeveragedFreeCashflow)) sb.Append(fundamental.LeveragedFreeCashflow).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.TrailingPE)) sb.Append(fundamental.TrailingPE).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.EBIT)) sb.Append(fundamental.EBIT).Append(",");
          else sb.Append("null").Append(",");
          if (!Double.IsNaN(fundamental.EnterpriseValue)) sb.Append(fundamental.EnterpriseValue).Append(",");
          else sb.Append("null").Append(",");
          if (null != fundamental.Source) sb.Append(SqlUtils.AddQuotes(fundamental.Source));
          else sb.Append("null");
          sb.Append(")");
          strQuery = sb.ToString();
          MySqlCommand sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
          sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
          sqlCommand.ExecuteNonQuery();
					sqlCommand.Dispose();
        }
        sqlTransaction.Commit();
        return true;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        MDTrace.WriteLine(LogLevel.DEBUG,"Query was '"+strQuery+"'");
        return false;
      }
      finally
      {
				if(null!=sqlTransaction)sqlTransaction.Dispose();
        if (null != sqlConnection) sqlConnection.Close();
      }
    }

    public static bool InsertFundamental(Fundamental fundamental)
    {
      MySqlConnection sqlConnection = null;
      MySqlTransaction sqlTransaction = null;
      String strQuery = null;

      try
      {
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        DeleteFundamental(fundamental, sqlConnection, sqlTransaction);
        StringBuilder sb = new StringBuilder();
        sb.Append("insert into fundamentals (symbol,asof,next_earnings_date,beta,beta_calc_36,beta_calc_24,beta_calc_06,low52,high52,volume,market_cap,pe,eps,peg,return_on_assets,return_on_equity,total_cash,total_debt,shares_outstanding,revenue,revenue_per_share,qtrly_revenue_growth,gross_profit,ebitda,net_income_available_to_common,book_value_per_share,operating_cashflow,leveraged_free_cashflow,trailing_pe,ebit,enterprise_value,source) ");
        sb.Append("values(");
        sb.Append("'").Append(fundamental.Symbol).Append("'").Append(",");
        sb.Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(fundamental.AsOf)).Append("'").Append(",");
        if (Utility.IsEpoch(fundamental.NextEarningsDate)) sb.Append("null").Append(",");
        else sb.Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(fundamental.NextEarningsDate)).Append("'").Append(",");
        if (!Double.IsNaN(fundamental.Beta)) sb.Append(fundamental.Beta).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.BetaCalc36)) sb.Append(fundamental.BetaCalc36).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.BetaCalc24)) sb.Append(fundamental.BetaCalc24).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.BetaCalc06)) sb.Append(fundamental.BetaCalc06).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.Low52)) sb.Append(fundamental.Low52).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.High52)) sb.Append(fundamental.High52).Append(",");
        else sb.Append("null").Append(",");
        sb.Append(fundamental.Volume).Append(",");
        if (!Double.IsNaN(fundamental.MarketCap)) sb.Append(fundamental.MarketCap).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.PE)) sb.Append(fundamental.PE).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.EPS)) sb.Append(fundamental.EPS).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.PEG)) sb.Append(fundamental.PEG).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.ReturnOnAssets))sb.Append(fundamental.ReturnOnAssets).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.ReturnOnEquity)) sb.Append(fundamental.ReturnOnEquity).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.TotalCash)) sb.Append(fundamental.TotalCash).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.TotalDebt)) sb.Append(fundamental.TotalDebt).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.SharesOutstanding)) sb.Append(fundamental.SharesOutstanding).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.Revenue)) sb.Append(fundamental.Revenue).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.RevenuePerShare)) sb.Append(fundamental.RevenuePerShare).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.QtrlyRevenueGrowth)) sb.Append(fundamental.QtrlyRevenueGrowth).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.GrossProfit)) sb.Append(fundamental.GrossProfit).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.EBITDA)) sb.Append(fundamental.EBITDA).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.NetIncomeAvailableToCommon)) sb.Append(fundamental.NetIncomeAvailableToCommon).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.BookValuePerShare)) sb.Append(fundamental.BookValuePerShare).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.OperatingCashflow)) sb.Append(fundamental.OperatingCashflow).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.LeveragedFreeCashflow)) sb.Append(fundamental.LeveragedFreeCashflow).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.TrailingPE)) sb.Append(fundamental.TrailingPE).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.EBIT)) sb.Append(fundamental.EBIT).Append(",");
        else sb.Append("null").Append(",");
        if (!Double.IsNaN(fundamental.EnterpriseValue)) sb.Append(fundamental.EnterpriseValue).Append(",");
        else sb.Append("null").Append(",");
        if (null != fundamental.Source) sb.Append(SqlUtils.AddQuotes(fundamental.Source));
        else sb.Append("null");
        sb.Append(")");
        strQuery = sb.ToString();
        MySqlCommand sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlCommand.ExecuteNonQuery();
				sqlCommand.Dispose();
        sqlTransaction.Commit();
        return true;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        MDTrace.WriteLine(LogLevel.DEBUG,"Query was '"+strQuery+"'");
        return false;
      }
      finally
      {
				if(null!=sqlTransaction)sqlTransaction.Dispose();
        if (null != sqlConnection) sqlConnection.Close();
      }
    }
    private static bool DeleteFundamentals(Fundamentals fundamentals, MySqlConnection sqlConnection, MySqlTransaction sqlTransaction)
    {
      for (int index = 0; index < fundamentals.Count; index++)
      {
        DeleteFundamental(fundamentals[index], sqlConnection, sqlTransaction);
      }
      return true;
    }
    private static bool DeleteFundamental(Fundamental fundamental, MySqlConnection sqlConnection, MySqlTransaction sqlTransaction)
    {
      StringBuilder sb = new StringBuilder();
      String strQuery = null;

      try
      {
        sb.Append("delete from fundamentals where ");
        sb.Append("symbol='").Append(fundamental.Symbol).Append("'");
        sb.Append(" and ");
        sb.Append("asof='").Append(Utility.DateTimeToStringYYYYHMMHDD(fundamental.AsOf)).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
      {
      }
    }
  }
}