286 lines
13 KiB
C#
286 lines
13 KiB
C#
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
|
|
{
|
|
}
|
|
}
|
|
}
|
|
}
|