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

namespace MarketData.DataAccess
{
  public class DividendHistoryDA
  {
    private DividendHistoryDA()
    {
    }
    public static List<DivExDateItem> GetLatestDivExDates()
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand=null;
      List<DivExDateItem> divExDates = new List<DivExDateItem>();
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select max(div_ex_date),symbol from dividendhistory group by symbol order by symbol asc");
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          DivExDateItem divExDate = new DivExDateItem();
          divExDate.DivExDate = sqlDataReader.GetDateTime(0);
          divExDate.Symbol = sqlDataReader.GetString(1);
          divExDates.Add(divExDate);
        }
        return divExDates;
      }
      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 DividendHistory GetDividendHistory(List<DivExDateItem> divExDates)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand =null;
      DividendHistory dividendHistory = new DividendHistory();
      String strQuery = null;

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

        foreach (DivExDateItem divExDate in divExDates)
        {
          StringBuilder sb = new StringBuilder();
          sb.Append("select symbol,dividend_type,cash_amount,div_ex_date,declaration_date,record_date,payment_date,modified from dividendhistory where symbol=");
          sb.Append(SqlUtils.AddQuotes(divExDate.Symbol));
          sb.Append(" and ");
          sb.Append("div_ex_date=").Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(divExDate.DivExDate)));
          strQuery = sb.ToString();
          sqlCommand = new MySqlCommand(strQuery, sqlConnection);
          sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
          sqlDataReader = sqlCommand.ExecuteReader();
          if (sqlDataReader.Read())
          {
            DividendHistoryItem dividendHistoryItem = new DividendHistoryItem();
            dividendHistoryItem.Symbol = sqlDataReader.GetString(0);
            if (!sqlDataReader.IsDBNull(1)) dividendHistoryItem.DividendType = sqlDataReader.GetString(1);
            if (!sqlDataReader.IsDBNull(2)) dividendHistoryItem.CashAmount = sqlDataReader.GetDouble(2);
            if (!sqlDataReader.IsDBNull(3)) dividendHistoryItem.DivExDate = sqlDataReader.GetDateTime(3);
            if (!sqlDataReader.IsDBNull(4)) dividendHistoryItem.DeclarationDate = sqlDataReader.GetDateTime(4);
            if (!sqlDataReader.IsDBNull(5)) dividendHistoryItem.RecordDate = sqlDataReader.GetDateTime(5);
            if (!sqlDataReader.IsDBNull(6)) dividendHistoryItem.PaymentDate = sqlDataReader.GetDateTime(6);
            if (!sqlDataReader.IsDBNull(7)) dividendHistoryItem.Modified = sqlDataReader.GetDateTime(7);
            dividendHistory.Add(dividendHistoryItem);
          }
          sqlCommand.Dispose();
          sqlDataReader.Close();
        }
        return dividendHistory;
      }
      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 DividendHistory GetDividendHistory(String symbol)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand=null;
      DividendHistory dividendHistory = new DividendHistory();
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select symbol,dividend_type,cash_amount,div_ex_date,declaration_date,record_date,payment_date,modified from dividendhistory where symbol=");
        sb.Append(SqlUtils.AddQuotes(symbol));
        sb.Append(" order by div_ex_date desc");
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          DividendHistoryItem dividendHistoryItem = new DividendHistoryItem();
          dividendHistoryItem.Symbol = sqlDataReader.GetString(0);
          if (!sqlDataReader.IsDBNull(1)) dividendHistoryItem.DividendType = sqlDataReader.GetString(1);
          if (!sqlDataReader.IsDBNull(2)) dividendHistoryItem.CashAmount = sqlDataReader.GetDouble(2);
          if (!sqlDataReader.IsDBNull(3)) dividendHistoryItem.DivExDate = sqlDataReader.GetDateTime(3);
          if (!sqlDataReader.IsDBNull(4)) dividendHistoryItem.DeclarationDate = sqlDataReader.GetDateTime(4);
          if (!sqlDataReader.IsDBNull(5)) dividendHistoryItem.RecordDate = sqlDataReader.GetDateTime(5);
          if (!sqlDataReader.IsDBNull(6)) dividendHistoryItem.PaymentDate = sqlDataReader.GetDateTime(6);
          if (!sqlDataReader.IsDBNull(7)) dividendHistoryItem.Modified = sqlDataReader.GetDateTime(7);
          dividendHistory.Add(dividendHistoryItem);
        }
        return dividendHistory;
      }
      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 DividendHistory GetDividendHistory(String symbol,int[] years)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand=null;
      DividendHistory dividendHistory = new DividendHistory();
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select symbol,dividend_type,cash_amount,div_ex_date,declaration_date,record_date,payment_date,modified from dividendhistory where symbol=");
        sb.Append(SqlUtils.AddQuotes(symbol));
        sb.Append(" and year(div_ex_date) in ").Append(SqlUtils.CreateInClauseInt(years.ToList()));
        sb.Append(" order by div_ex_date desc");
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          DividendHistoryItem dividendHistoryItem = new DividendHistoryItem();
          dividendHistoryItem.Symbol = sqlDataReader.GetString(0);
          if (!sqlDataReader.IsDBNull(1)) dividendHistoryItem.DividendType = sqlDataReader.GetString(1);
          if (!sqlDataReader.IsDBNull(2)) dividendHistoryItem.CashAmount = sqlDataReader.GetDouble(2);
          if (!sqlDataReader.IsDBNull(3)) dividendHistoryItem.DivExDate = sqlDataReader.GetDateTime(3);
          if (!sqlDataReader.IsDBNull(4)) dividendHistoryItem.DeclarationDate = sqlDataReader.GetDateTime(4);
          if (!sqlDataReader.IsDBNull(5)) dividendHistoryItem.RecordDate = sqlDataReader.GetDateTime(5);
          if (!sqlDataReader.IsDBNull(6)) dividendHistoryItem.PaymentDate = sqlDataReader.GetDateTime(6);
          if (!sqlDataReader.IsDBNull(7)) dividendHistoryItem.Modified = sqlDataReader.GetDateTime(7);
          dividendHistory.Add(dividendHistoryItem);
        }
        return dividendHistory;
      }
      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 InsertOrUpdate(DividendHistory dividendHistory)
    {
      MySqlConnection sqlConnection = null;
      MySqlTransaction sqlTransaction = null;
      String strQuery = null;

      try
      {
        if (null == dividendHistory || 0 == dividendHistory.Count) return false;
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        DeleteDividendHistory(dividendHistory, sqlConnection,sqlTransaction);
        DateTime modified = DateTime.Now;
        for (int index = 0; index < dividendHistory.Count; index++)
        {
          DividendHistoryItem dividendHistoryItem = dividendHistory[index];
          StringBuilder sb = new StringBuilder();
          sb.Append("insert into dividendhistory(symbol,dividend_type,cash_amount,div_ex_date,declaration_date,record_date,payment_date,modified)values(");
          sb.Append(SqlUtils.AddQuotes(dividendHistoryItem.Symbol)).Append(",");
          sb.Append(SqlUtils.AddQuotes(dividendHistoryItem.DividendType)).Append(",");
          if (null == dividendHistoryItem.CashAmount) sb.Append("null").Append(",");
          else sb.Append(dividendHistoryItem.CashAmount.Value).Append(",");

          sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(dividendHistoryItem.DivExDate))).Append(",");

          if (null == dividendHistoryItem.DeclarationDate) sb.Append("null").Append(",");
          else sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(dividendHistoryItem.DeclarationDate.Value))).Append(",");

          if (null == dividendHistoryItem.RecordDate) sb.Append("null").Append(",");
          else sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(dividendHistoryItem.RecordDate.Value))).Append(",");

          if (null == dividendHistoryItem.PaymentDate) sb.Append("null").Append(",");
          else sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(dividendHistoryItem.PaymentDate.Value))).Append(",");

          sb.Append(SqlUtils.AddQuotes(SqlUtils.ToSqlDateTime(modified)));
          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 void DeleteDividendHistory(DividendHistory dividendHistory,MySqlConnection sqlConnection,MySqlTransaction sqlTransaction)
    {
      String strQuery = null;
      try
      {
        for (int index = 0; index < dividendHistory.Count; index++)
        {
          StringBuilder sb = new StringBuilder();
          DividendHistoryItem dividendHistoryItem = dividendHistory[index];
          sb.Append("delete from dividendhistory where ");
          sb.Append("symbol=").Append(SqlUtils.AddQuotes(dividendHistoryItem.Symbol));
          sb.Append(" and div_ex_date=").Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(dividendHistoryItem.DivExDate)));
          strQuery = sb.ToString();
          MySqlCommand sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
          sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
          sqlCommand.ExecuteNonQuery();
					sqlCommand.Dispose();
        }
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
      }
      finally
      {
      }
    }
  }
}