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

namespace MarketData.DataAccess
{
  public class ValuationDA
  {
    private ValuationDA()
    {
    }
    public static List<DateTime> GetValuationDates()
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand=null;
      List<DateTime> valuationDates = new List<DateTime>();
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select distinct(valuation_date) from valuations order by valuation_date desc");
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          valuationDates.Add(sqlDataReader.GetDateTime(0));
        }
        return valuationDates;
      }
      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 DateTime GetLatestValuationDate()
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand=null;
      DateTime valuationDate=Utility.Epoch;
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select max(valuation_date) from valuations");
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        if (sqlDataReader.Read()) valuationDate = sqlDataReader.GetDateTime(0);
        return valuationDate;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return valuationDate;
      }
      finally
      {
				if(null!=sqlCommand)sqlCommand.Dispose();
        if (null != sqlDataReader) sqlDataReader.Close();
        if (null != sqlConnection) sqlConnection.Close();
      }
    }
    public static Valuations GetValuation(DateTime valuationDate)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand=null;
      Valuations valuations = new Valuations();
      String strQuery = null;

      try
      {
        if (Utility.IsEpoch(valuationDate)) return null;
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        StringBuilder sb = new StringBuilder();
        sb.Append("select valuation_date,symbol,company,next_earnings_date,long_term_debt,revenue,debt_load,roic_avg,bvps_dates,avg_equity_growth,avg_equity_growth_2y,avg_equity_growth_4y,eps_dates,avg_eps_growth,avg_eps_growth_2y,avg_eps_growth_4y,revenue_dates,avg_revenue_growth,avg_revenue_growth_2y,avg_revenue_growth_4y,avg_freecashflow_growth,avg_operating_cashflow,avg_working_capital,bvps,eps,pe,low_pe,trailing_pe,avg_low_trailing,current_stock_estimate_price,price_estimate_10y,todays_price_for_required_return,mos,mos_80,intrinsic_value,rgv,latest_price,upside_pcnt,downside_pcnt,mean_target_price,low_target_price,high_target_price,bargain_mos,bargain_mos_80,price_to_book,peg,implied_earnings_growth,beta90,beta2y,roic_slope,roic_dates,market_cap,earnings_yield,ebit,enterprise_value,tlb_rank_roic,fundamental_value,net_current_asset_value_per_share,roic_latest,sector,industry,debt_to_equity,sec_13,sec_13_filing_date,roc_latest,tlb_rank_roc,operating_earnings,am_rank,acquirers_multiple,dividend_yield,operating_cashflow,shares_outstanding,modified").Append(" ");
        sb.Append("from valuations where valuation_date=").Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(valuationDate)));
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        valuations.ValuationDate = valuationDate;
        while (sqlDataReader.Read())
        {
          Valuation valuation = new Valuation();
          valuation.Symbol = sqlDataReader.GetString(1);
          valuation.Company = sqlDataReader.GetString(2);
          if (!sqlDataReader.IsDBNull(3)) valuation.NextEarningsDate = sqlDataReader.GetDateTime(3);
          else valuation.NextEarningsDate = Utility.Epoch;
          if (!sqlDataReader.IsDBNull(4)) valuation.LongTermDebt = sqlDataReader.GetDouble(4);
          if (!sqlDataReader.IsDBNull(5)) valuation.Revenue = sqlDataReader.GetDouble(5);
          valuation.DebtLoad = sqlDataReader.GetString(6);
          if (!sqlDataReader.IsDBNull(7)) valuation.AverageROIC = sqlDataReader.GetDouble(7);
          if (!sqlDataReader.IsDBNull(8)) valuation.BVPSDates = sqlDataReader.GetString(8);
          if (!sqlDataReader.IsDBNull(9)) valuation.AverageEquityGrowth = sqlDataReader.GetDouble(9);
          if (!sqlDataReader.IsDBNull(10)) valuation.AverageEquityGrowth2Y = sqlDataReader.GetDouble(10);
          if (!sqlDataReader.IsDBNull(11)) valuation.AverageEquityGrowth4Y = sqlDataReader.GetDouble(11);
          valuation.EPSDates = sqlDataReader.GetString(12);
          if (!sqlDataReader.IsDBNull(13)) valuation.AverageEPSGrowth = sqlDataReader.GetDouble(13);
          if (!sqlDataReader.IsDBNull(14)) valuation.AverageEPSGrowth2Y = sqlDataReader.GetDouble(14);
          if (!sqlDataReader.IsDBNull(15)) valuation.AverageEPSGrowth4Y = sqlDataReader.GetDouble(15);
          valuation.RevenueDates = sqlDataReader.GetString(16);
          if (!sqlDataReader.IsDBNull(17)) valuation.AverageRevenueGrowth = sqlDataReader.GetDouble(17);
          if (!sqlDataReader.IsDBNull(18)) valuation.AverageRevenueGrowth2Y = sqlDataReader.GetDouble(18);
          if (!sqlDataReader.IsDBNull(19)) valuation.AverageRevenueGrowth4Y = sqlDataReader.GetDouble(19);
          if (!sqlDataReader.IsDBNull(20)) valuation.AverageFreeCashflowGrowth = sqlDataReader.GetDouble(20);
          if (!sqlDataReader.IsDBNull(21)) valuation.AverageOperatingCashflow = sqlDataReader.GetDouble(21);
          if (!sqlDataReader.IsDBNull(22)) valuation.AverageWorkingCapital = sqlDataReader.GetDouble(22);
          if (!sqlDataReader.IsDBNull(23)) valuation.BVPS = sqlDataReader.GetDouble(23);
          if (!sqlDataReader.IsDBNull(24)) valuation.EPS = sqlDataReader.GetDouble(24);
          if (!sqlDataReader.IsDBNull(25)) valuation.PE = sqlDataReader.GetDouble(25);
          if (!sqlDataReader.IsDBNull(26)) valuation.LowPE = sqlDataReader.GetDouble(26);
          if (!sqlDataReader.IsDBNull(27)) valuation.TrailingPE = sqlDataReader.GetDouble(27);
          if (!sqlDataReader.IsDBNull(28)) valuation.AverageLowTrailing = sqlDataReader.GetDouble(28);
          if (!sqlDataReader.IsDBNull(29)) valuation.CurrentStockEstimatePrice = sqlDataReader.GetDouble(29);
          if (!sqlDataReader.IsDBNull(30)) valuation.PriceEstimate10Y = sqlDataReader.GetDouble(30);
          if (!sqlDataReader.IsDBNull(31)) valuation.TodaysPriceForRequiredReturn = sqlDataReader.GetDouble(31);
          if (!sqlDataReader.IsDBNull(32)) valuation.MOS = sqlDataReader.GetDouble(32);
          if (!sqlDataReader.IsDBNull(33)) valuation.MOS80 = sqlDataReader.GetDouble(33);
          if (!sqlDataReader.IsDBNull(34)) valuation.IntrinsicValue = sqlDataReader.GetDouble(34);
          if (!sqlDataReader.IsDBNull(35)) valuation.RGV = sqlDataReader.GetDouble(35);
          if (!sqlDataReader.IsDBNull(36)) valuation.LatestPrice = sqlDataReader.GetDouble(36);
          if (!sqlDataReader.IsDBNull(37)) valuation.UpsidePcnt = sqlDataReader.GetDouble(37);
          if (!sqlDataReader.IsDBNull(38)) valuation.DownsidePcnt = sqlDataReader.GetDouble(38);
          if (!sqlDataReader.IsDBNull(39)) valuation.MeanTargetPrice = sqlDataReader.GetDouble(39);
          if (!sqlDataReader.IsDBNull(40)) valuation.LowTargetPrice = sqlDataReader.GetDouble(40);
          if (!sqlDataReader.IsDBNull(41)) valuation.HighTargetPrice = sqlDataReader.GetDouble(41);
          valuation.Bargain = sqlDataReader.GetBoolean(42);
          valuation.Bargain80 = sqlDataReader.GetBoolean(43);
          if (!sqlDataReader.IsDBNull(44)) valuation.PBVPS = sqlDataReader.GetDouble(44);
          if (!sqlDataReader.IsDBNull(45)) valuation.PEG = sqlDataReader.GetDouble(45);
          if (!sqlDataReader.IsDBNull(46)) valuation.ImpliedEarningsGrowth = sqlDataReader.GetDouble(46);
          if (!sqlDataReader.IsDBNull(47)) valuation.Beta90 = sqlDataReader.GetDouble(47);
          if (!sqlDataReader.IsDBNull(48)) valuation.Beta720 = sqlDataReader.GetDouble(48);
          if (!sqlDataReader.IsDBNull(49)) valuation.ROICSlope = sqlDataReader.GetDouble(49);
          if (!sqlDataReader.IsDBNull(50)) valuation.ROICDates = sqlDataReader.GetString(50);
          if (!sqlDataReader.IsDBNull(51)) valuation.MarketCap = sqlDataReader.GetDouble(51);
          if (!sqlDataReader.IsDBNull(52)) valuation.EarningsYield = sqlDataReader.GetDouble(52);
          if (!sqlDataReader.IsDBNull(53)) valuation.EBIT = sqlDataReader.GetDouble(53);
          if (!sqlDataReader.IsDBNull(54)) valuation.EnterpriseValue = sqlDataReader.GetDouble(54);
          if (!sqlDataReader.IsDBNull(55)) valuation.TLBRankROIC = sqlDataReader.GetDouble(55);
          if (!sqlDataReader.IsDBNull(56)) valuation.FundamentalValue = sqlDataReader.GetDouble(56);
          if (!sqlDataReader.IsDBNull(57)) valuation.NetCurrentAssetValuePerShare = sqlDataReader.GetDouble(57);
          if (!sqlDataReader.IsDBNull(58)) valuation.LatestROIC = sqlDataReader.GetDouble(58);
          if (!sqlDataReader.IsDBNull(59)) valuation.Sector=sqlDataReader.GetString(59);
          if (!sqlDataReader.IsDBNull(60)) valuation.Industry = sqlDataReader.GetString(60);
          if (!sqlDataReader.IsDBNull(61)) valuation.DebtToEquity = sqlDataReader.GetDouble(61);
          if (!sqlDataReader.IsDBNull(62)) valuation.SEC13 = sqlDataReader.GetBoolean(62);
          if (!sqlDataReader.IsDBNull(63)) valuation.SEC13FilingDate = sqlDataReader.GetDateTime(63);
          if (!sqlDataReader.IsDBNull(64)) valuation.LatestROC = sqlDataReader.GetDouble(64);
          if (!sqlDataReader.IsDBNull(65)) valuation.TLBRankROC = sqlDataReader.GetDouble(65);
          if (!sqlDataReader.IsDBNull(66)) valuation.OperatingEarnings = sqlDataReader.GetDouble(66);
          if (!sqlDataReader.IsDBNull(67)) valuation.AMRank = sqlDataReader.GetDouble(67);
          if (!sqlDataReader.IsDBNull(68)) valuation.AcquirersMultiple = sqlDataReader.GetDouble(68);
          if (!sqlDataReader.IsDBNull(69)) valuation.DividendYield = sqlDataReader.GetDouble(69);
          if (!sqlDataReader.IsDBNull(70)) valuation.OperatingCashflow = sqlDataReader.GetDouble(70);
          if (!sqlDataReader.IsDBNull(71)) valuation.SharesOutstanding = sqlDataReader.GetDouble(71);
          valuation.Modified = sqlDataReader.GetDateTime(72);
          valuations.Add(valuation);
        }
        return valuations;
      }
      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 AddValuations(Valuations valuations)
    {
      MySqlConnection sqlConnection = null;
			MySqlCommand sqlCommand=null;
      MySqlTransaction sqlTransaction = null;
      String strQuery = null;
      DateTime modified = DateTime.Now;

      try
      {
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        RemoveValuation(valuations.ValuationDate,sqlTransaction);
        foreach(Valuation valuation in valuations)
        {
          StringBuilder sb = new StringBuilder();
          sb.Append("insert into valuations(valuation_date,symbol,company,next_earnings_date,long_term_debt,revenue,debt_load,roic_avg,roic_slope,roic_dates,bvps_dates,avg_equity_growth,avg_equity_growth_2y,avg_equity_growth_4y,eps_dates,avg_eps_growth,avg_eps_growth_2y,avg_eps_growth_4y,revenue_dates,avg_revenue_growth,avg_revenue_growth_2y,avg_revenue_growth_4y,avg_freecashflow_growth,avg_operating_cashflow,avg_working_capital,bvps,eps,pe,low_pe,trailing_pe,avg_low_trailing,current_stock_estimate_price,price_estimate_10y,todays_price_for_required_return,mos,mos_80,intrinsic_value,rgv,latest_price,upside_pcnt,downside_pcnt,mean_target_price,low_target_price,high_target_price,bargain_mos,bargain_mos_80,price_to_book,peg,implied_earnings_growth,beta90,beta2y,market_cap,earnings_yield,ebit,enterprise_value,tlb_rank_roic,fundamental_value,net_current_asset_value_per_share,roic_latest,sector,industry,debt_to_equity,sec_13,sec_13_filing_date,roc_latest,tlb_rank_roc,operating_earnings,am_rank,acquirers_multiple,dividend_yield,operating_cashflow,shares_outstanding,modified)values(");
          sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(valuations.ValuationDate))).Append(",");
          sb.Append(SqlUtils.AddQuotes(valuation.Symbol)).Append(",");
          if (null == valuation.Company) sb.Append("null").Append(",");
          else sb.Append(SqlUtils.AddQuotes(SqlUtils.SqlString(valuation.Company))).Append(",");
          if (Utility.IsEpoch(valuation.NextEarningsDate)) sb.Append("null").Append(",");
          else sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(valuation.NextEarningsDate))).Append(",");
          if (double.IsNaN(valuation.LongTermDebt) || double.IsInfinity(valuation.LongTermDebt)) sb.Append("null").Append(",");
          else sb.Append(valuation.LongTermDebt).Append(",");
          if (double.IsNaN(valuation.Revenue) || double.IsInfinity(valuation.Revenue)) sb.Append("null").Append(",");
          else sb.Append(valuation.Revenue).Append(",");
          if (null == valuation.DebtLoad) sb.Append("null").Append(",");
          else sb.Append(SqlUtils.AddQuotes(valuation.DebtLoad)).Append(",");
          if (double.IsNaN(valuation.AverageROIC) || double.IsInfinity(valuation.AverageROIC)) sb.Append("null").Append(",");
          else sb.Append(valuation.AverageROIC).Append(",");
          if (double.IsNaN(valuation.ROICSlope) || double.IsInfinity(valuation.ROICSlope)) sb.Append("null").Append(",");
          else sb.Append(valuation.ROICSlope).Append(",");
          if (null == valuation.ROICDates) sb.Append("null").Append(",");
          else sb.Append(SqlUtils.AddQuotes(valuation.ROICDates)).Append(",");
          if (null == valuation.BVPSDates) sb.Append("null").Append(",");
          else sb.Append(SqlUtils.AddQuotes(valuation.BVPSDates)).Append(",");
          if (double.IsNaN(valuation.AverageEquityGrowth) || double.IsInfinity(valuation.AverageEquityGrowth)) sb.Append("null").Append(",");
          else sb.Append(valuation.AverageEquityGrowth).Append(",");
          if (double.IsNaN(valuation.AverageEquityGrowth2Y) || double.IsInfinity(valuation.AverageEquityGrowth2Y)) sb.Append("null").Append(",");
          else sb.Append(valuation.AverageEquityGrowth2Y).Append(",");
          if (double.IsNaN(valuation.AverageEquityGrowth4Y) || double.IsInfinity(valuation.AverageEquityGrowth4Y)) sb.Append("null").Append(",");
          else sb.Append(valuation.AverageEquityGrowth4Y).Append(",");
          if (null == valuation.EPSDates) sb.Append("null").Append(",");
          else sb.Append(SqlUtils.AddQuotes(valuation.EPSDates)).Append(",");
          if (double.IsNaN(valuation.AverageEPSGrowth) || double.IsInfinity(valuation.AverageEPSGrowth)) sb.Append("null").Append(",");
          else sb.Append(valuation.AverageEPSGrowth).Append(",");
          if (double.IsNaN(valuation.AverageEPSGrowth2Y) || double.IsInfinity(valuation.AverageEPSGrowth2Y)) sb.Append("null").Append(",");
          else sb.Append(valuation.AverageEPSGrowth2Y).Append(",");
          if (double.IsNaN(valuation.AverageEPSGrowth4Y) || double.IsInfinity(valuation.AverageEPSGrowth4Y)) sb.Append("null").Append(",");
          else sb.Append(valuation.AverageEPSGrowth4Y).Append(",");
          if (null == valuation.RevenueDates) sb.Append("null").Append(",");
          else sb.Append(SqlUtils.AddQuotes(valuation.RevenueDates)).Append(",");
          if (double.IsNaN(valuation.AverageRevenueGrowth) || double.IsInfinity(valuation.AverageRevenueGrowth)) sb.Append("null").Append(",");
          else sb.Append(valuation.AverageRevenueGrowth).Append(",");
//          if (double.IsNaN(valuation.AverageRevenueGrowthQtr) || double.IsInfinity(valuation.AverageRevenueGrowthQtr)) sb.Append("null").Append(",");
//          else sb.Append(valuation.AverageRevenueGrowthQtr).Append(",");
          if (double.IsNaN(valuation.AverageRevenueGrowth2Y) || double.IsInfinity(valuation.AverageRevenueGrowth2Y)) sb.Append("null").Append(",");
          else sb.Append(valuation.AverageRevenueGrowth2Y).Append(",");
          if (double.IsNaN(valuation.AverageRevenueGrowth4Y) || double.IsInfinity(valuation.AverageRevenueGrowth4Y)) sb.Append("null").Append(",");
          else sb.Append(valuation.AverageRevenueGrowth4Y).Append(",");
          if (double.IsNaN(valuation.AverageFreeCashflowGrowth) || double.IsInfinity(valuation.AverageFreeCashflowGrowth)) sb.Append("null").Append(",");
          else sb.Append(valuation.AverageFreeCashflowGrowth).Append(",");
          if (double.IsNaN(valuation.AverageOperatingCashflow) || double.IsInfinity(valuation.AverageOperatingCashflow)) sb.Append("null").Append(","); 
          else sb.Append(valuation.AverageOperatingCashflow).Append(",");
          if (double.IsNaN(valuation.AverageWorkingCapital) || double.IsInfinity(valuation.AverageWorkingCapital)) sb.Append("null").Append(",");
          else sb.Append(valuation.AverageWorkingCapital).Append(",");
          if (double.IsNaN(valuation.BVPS) || double.IsInfinity(valuation.BVPS)) sb.Append("null").Append(",");
          else sb.Append(valuation.BVPS).Append(",");
          if (double.IsNaN(valuation.EPS) || double.IsInfinity(valuation.EPS)) sb.Append("null").Append(",");
          else sb.Append(valuation.EPS).Append(",");
          if (double.IsNaN(valuation.PE) || double.IsInfinity(valuation.PE)) sb.Append("null").Append(",");
          else sb.Append(valuation.PE).Append(",");
          if (double.IsNaN(valuation.LowPE) || double.IsInfinity(valuation.LowPE)) sb.Append("null").Append(",");
          else sb.Append(valuation.LowPE).Append(",");
          if (double.IsNaN(valuation.TrailingPE) || double.IsInfinity(valuation.TrailingPE)) sb.Append("null").Append(",");
          else sb.Append(valuation.TrailingPE).Append(",");
          if (double.IsNaN(valuation.AverageLowTrailing) || double.IsInfinity(valuation.AverageLowTrailing)) sb.Append("null").Append(",");
          else sb.Append(valuation.AverageLowTrailing).Append(",");
          if (double.IsNaN(valuation.CurrentStockEstimatePrice) || double.IsInfinity(valuation.CurrentStockEstimatePrice)) sb.Append("null").Append(",");
          else sb.Append(valuation.CurrentStockEstimatePrice).Append(",");
          if (valuation.PriceEstimate10Y<0 || double.IsNaN(valuation.PriceEstimate10Y) || double.IsInfinity(valuation.PriceEstimate10Y) || Utility.OutOfRange(valuation.PriceEstimate10Y)) sb.Append("null").Append(",");
          else sb.Append(valuation.PriceEstimate10Y).Append(",");
          if (double.IsNaN(valuation.TodaysPriceForRequiredReturn) || double.IsInfinity(valuation.TodaysPriceForRequiredReturn) || Utility.OutOfRange(valuation.TodaysPriceForRequiredReturn)) sb.Append("null").Append(",");
          else sb.Append(valuation.TodaysPriceForRequiredReturn).Append(",");
          if (double.IsNaN(valuation.MOS) || double.IsInfinity(valuation.MOS) || Utility.OutOfRange(valuation.MOS)) sb.Append("null").Append(",");
          else sb.Append(valuation.MOS).Append(",");
          if (double.IsNaN(valuation.MOS80) || double.IsInfinity(valuation.MOS80) || Utility.OutOfRange(valuation.MOS80)) sb.Append("null").Append(",");
          else sb.Append(valuation.MOS80).Append(",");
          if (double.IsNaN(valuation.IntrinsicValue) || double.IsInfinity(valuation.IntrinsicValue) || Utility.OutOfRange(valuation.IntrinsicValue)) sb.Append("null").Append(",");
          else sb.Append(valuation.IntrinsicValue).Append(",");
          if (double.IsNaN(valuation.RGV) || double.IsInfinity(valuation.RGV) || Utility.OutOfRange(valuation.RGV)) sb.Append("null").Append(",");
          else sb.Append(valuation.RGV).Append(",");
          if (double.IsNaN(valuation.LatestPrice) || double.IsInfinity(valuation.LatestPrice)) sb.Append("null").Append(",");
          else sb.Append(valuation.LatestPrice).Append(",");
          if (double.IsNaN(valuation.UpsidePcnt) || double.IsInfinity(valuation.UpsidePcnt)) sb.Append("null").Append(",");
          else sb.Append(valuation.UpsidePcnt).Append(",");
          if (double.IsNaN(valuation.DownsidePcnt) || double.IsInfinity(valuation.DownsidePcnt)) sb.Append("null").Append(",");
          else sb.Append(valuation.DownsidePcnt).Append(",");
          if (double.IsNaN(valuation.MeanTargetPrice) || double.IsInfinity(valuation.MeanTargetPrice)) sb.Append("null").Append(",");
          else sb.Append(valuation.MeanTargetPrice).Append(",");
          if (double.IsNaN(valuation.LowTargetPrice) || double.IsInfinity(valuation.LowTargetPrice)) sb.Append("null").Append(",");
          else sb.Append(valuation.LowTargetPrice).Append(",");
          if (double.IsNaN(valuation.HighTargetPrice) || double.IsInfinity(valuation.HighTargetPrice)) sb.Append("null").Append(",");
          else sb.Append(valuation.HighTargetPrice).Append(",");
          sb.Append(valuation.Bargain).Append(",");
          sb.Append(valuation.Bargain80).Append(",");
          if (double.IsNaN(valuation.PBVPS) || double.IsInfinity(valuation.PBVPS)) sb.Append("null").Append(",");
          else sb.Append(valuation.PBVPS).Append(",");
          if (double.IsNaN(valuation.PEG) || double.IsInfinity(valuation.PEG)) sb.Append("null").Append(",");
          else sb.Append(valuation.PEG).Append(",");
          if (double.IsNaN(valuation.ImpliedEarningsGrowth) || double.IsInfinity(valuation.ImpliedEarningsGrowth)) sb.Append("null").Append(",");
          else sb.Append(valuation.ImpliedEarningsGrowth).Append(",");
          if (double.IsNaN(valuation.Beta90) || double.IsInfinity(valuation.Beta90)) sb.Append("null").Append(",");
          else sb.Append(valuation.Beta90).Append(",");
          if (double.IsNaN(valuation.Beta720) || double.IsInfinity(valuation.Beta720)) sb.Append("null").Append(",");
          else sb.Append(valuation.Beta720).Append(",");
          if (double.IsNaN(valuation.MarketCap) || double.IsInfinity(valuation.MarketCap)) sb.Append("null").Append(",");
          else sb.Append(valuation.MarketCap).Append(",");
          if (double.IsNaN(valuation.EarningsYield) || double.IsInfinity(valuation.EarningsYield)) sb.Append("null").Append(",");
          else sb.Append(valuation.EarningsYield).Append(",");
          if (double.IsNaN(valuation.EBIT) || double.IsInfinity(valuation.EBIT)) sb.Append("null").Append(",");
          else sb.Append(valuation.EBIT).Append(",");
          if (double.IsNaN(valuation.EnterpriseValue) || double.IsInfinity(valuation.EnterpriseValue)) sb.Append("null").Append(",");
          else sb.Append(valuation.EnterpriseValue).Append(",");
          if (double.IsNaN(valuation.TLBRankROIC) || double.IsInfinity(valuation.TLBRankROIC)) sb.Append("null").Append(",");
          else sb.Append(valuation.TLBRankROIC).Append(",");
          if (double.IsNaN(valuation.FundamentalValue) || double.IsInfinity(valuation.FundamentalValue)) sb.Append("null").Append(",");
          else sb.Append(valuation.FundamentalValue).Append(",");
          if (double.IsNaN(valuation.NetCurrentAssetValuePerShare) || double.IsInfinity(valuation.NetCurrentAssetValuePerShare)) sb.Append("null").Append(",");
          else sb.Append(valuation.NetCurrentAssetValuePerShare).Append(",");
          if (double.IsNaN(valuation.LatestROIC) || double.IsInfinity(valuation.LatestROIC)) sb.Append("null").Append(",");
          else sb.Append(valuation.LatestROIC).Append(",");
          if (null==valuation.Sector) sb.Append("null").Append(",");
          else sb.Append(SqlUtils.AddQuotes(valuation.Sector)).Append(",");
          if (null==valuation.Industry) sb.Append("null").Append(",");
          else sb.Append(SqlUtils.AddQuotes(valuation.Industry)).Append(",");
          if (double.IsNaN(valuation.DebtToEquity) || double.IsInfinity(valuation.DebtToEquity)) sb.Append("null").Append(",");
          else sb.Append(valuation.DebtToEquity).Append(",");
          sb.Append(valuation.SEC13).Append(",");
          if (Utility.IsEpoch(valuation.SEC13FilingDate)) sb.Append("null").Append(",");
          else sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(valuation.SEC13FilingDate))).Append(",");
          if (double.IsNaN(valuation.LatestROC) || double.IsInfinity(valuation.LatestROC)) sb.Append("null").Append(",");
          else sb.Append(valuation.LatestROC).Append(",");
          if (double.IsNaN(valuation.TLBRankROC) || double.IsInfinity(valuation.TLBRankROC)) sb.Append("null").Append(",");
          else sb.Append(valuation.TLBRankROC).Append(",");
          if (double.IsNaN(valuation.OperatingEarnings) || double.IsInfinity(valuation.OperatingEarnings)) sb.Append("null").Append(",");
          else sb.Append(valuation.OperatingEarnings).Append(",");
          if (double.IsNaN(valuation.AMRank) || double.IsInfinity(valuation.AMRank)) sb.Append("null").Append(",");
          else sb.Append(valuation.AMRank).Append(",");
          if (double.IsNaN(valuation.AcquirersMultiple) || double.IsInfinity(valuation.AcquirersMultiple)) sb.Append("null").Append(",");
          else sb.Append(valuation.AcquirersMultiple).Append(",");
          if (double.IsNaN(valuation.DividendYield) || double.IsInfinity(valuation.DividendYield)) sb.Append("null").Append(",");
          else sb.Append(valuation.DividendYield).Append(",");
          if (double.IsNaN(valuation.OperatingCashflow) || double.IsInfinity(valuation.OperatingCashflow)) sb.Append("null").Append(",");
          else sb.Append(valuation.OperatingCashflow).Append(",");
          if (double.IsNaN(valuation.SharesOutstanding) || double.IsInfinity(valuation.SharesOutstanding)) sb.Append("null").Append(",");
          else sb.Append(valuation.SharesOutstanding).Append(",");
          sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(modified))).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,strQuery);

        return false;
      }
      finally
      {
				if(null!=sqlCommand)sqlCommand.Dispose();
        if (null != sqlTransaction) sqlTransaction.Dispose();
        if (null != sqlConnection) sqlConnection.Close();
      }
    }
    public static void RemoveValuationBySymbol(String symbol,DateTime valuationDate)
    {
      MySqlConnection sqlConnection = null;
      MySqlCommand sqlCommand=null;
      MySqlTransaction sqlTransaction=null;
      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("delete from valuations where symbol='").Append(symbol).Append("'");
        sb.Append(" and valuation_date=").Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(valuationDate)));
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection,sqlTransaction);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlCommand.ExecuteNonQuery();
        sqlTransaction.Commit();
        return;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return;
      }
      finally
      {
        if(null!=sqlCommand)sqlCommand.Dispose();
        if (null != sqlConnection) sqlConnection.Close();
      }
    }
    public static bool RemoveValuation(DateTime valuationDate,MySqlTransaction sqlTransaction)
    {
      MySqlConnection sqlConnection = null;
			MySqlCommand sqlCommand=null;
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("delete from valuations where valuation_date=");
        sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(valuationDate)));
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection,sqlTransaction);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlCommand.ExecuteNonQuery();
        return true;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return false;
      }
      finally
      {
				if(null!=sqlCommand)sqlCommand.Dispose();
        if (null != sqlConnection) sqlConnection.Close();
      }
    }
  }
}
