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

namespace MarketData.DataAccess
{
  public class FundamentalDA
  {
    private FundamentalDA()
    {
    }
    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();
        if (null != sqlConnection) sqlConnection.Close();
      }
    }
    public static DateTime GetLatestDate(String symbol)
    {
      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 max(asof) from fundamentals").Append(" ");
        sb.Append("where symbol='").Append(symbol).Append("'");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        if (sqlDataReader.Read())
        {
          if (!sqlDataReader.IsDBNull(0))maxDate=sqlDataReader.GetDateTime(0);
        }
        return maxDate;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return maxDate;
      }
      finally
      {
				if(null!=sqlCommand)sqlCommand.Dispose();
        if(null!=sqlDataReader)sqlDataReader.Close();
        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();
        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();
        if (null != sqlConnection) sqlConnection.Close();
      }
    }
    public static TimeSeriesCollection GetEPS(String symbol,DateTime? maxDate=null)
    {
      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"));
        if(null==maxDate)sb.Append("select symbol,asof,eps from fundamentals where symbol='").Append(symbol).Append("' order by asof desc");
        else sb.Append("select symbol,asof,eps from fundamentals where symbol='").Append(symbol).Append("'").Append(" and asof<=").Append(SqlUtils.AddQuotes(SqlUtils.ToSqlDateTime(maxDate.Value))).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 = "EPS";
          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();
        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 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);
        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();
        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 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 (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();
        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();
        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 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 (!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();
        if (null != sqlConnection) sqlConnection.Close();
      }
    }

    public static Fundamentals GetFundamentalMaxDateTop(String symbol, DateTime asof, int top)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand=null;
      String strQuery = null;
      Fundamentals fundamentals = new Fundamentals();

      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 from fundamentals where symbol=");
        sb.Append("'").Append(symbol).Append("'").Append(" ");
        sb.Append("and asof<=").Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(maxDate.Value)).Append("'");
        sb.Append(" order by asof desc ");
        sb.Append(" limit ").Append(top);
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while(sqlDataReader.Read())
        {

          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 (!double.IsNaN(totalStockHolderEquity) && !double.IsNaN(fundamental.TotalDebt))
          {
            if(0.00==totalStockHolderEquity)fundamental.TotalDebt=0.00;
            else fundamental.DebtToEquity=fundamental.TotalDebt/totalStockHolderEquity;
          }
          fundamentals.Add(fundamental);
        }
        return fundamentals;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return null;
      }
      finally
      {
				if(null!=sqlCommand)sqlCommand.Dispose();
        if (null != sqlDataReader) sqlDataReader.Close();
        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,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.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,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.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
      {
      }
    }
  }
}
