319 lines
17 KiB
C#
319 lines
17 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using MySql.Data.MySqlClient;
|
|
using MarketData.MarketDataModel;
|
|
using MarketData.Utils;
|
|
|
|
namespace MarketData.DataAccess
|
|
{
|
|
public class InsiderTransactionDA
|
|
{
|
|
private InsiderTransactionDA()
|
|
{
|
|
}
|
|
public static InsiderTransactionSummaries GetInsiderTransactionSummaries(String symbol,DateTime? minDate)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
InsiderTransactionSummaries insiderTransactionSummaries =new InsiderTransactionSummaries();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select transaction_date,sum(number_or_value_acquired_disposed) from InsiderTransaction where ");
|
|
sb.Append(" symbol=").Append("'").Append(symbol).Append("' ");
|
|
if(null!=minDate)sb.Append(" and transaction_date>=").Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(minDate.Value)).Append("'");
|
|
sb.Append(" and number_or_value_acquired_disposed is not null group by transaction_date order by transaction_date desc ");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
InsiderTransactionSummary insiderTransactionSummary=new InsiderTransactionSummary();
|
|
insiderTransactionSummary.Symbol=symbol;
|
|
insiderTransactionSummary.TransactionDate=sqlDataReader.GetDateTime(0);
|
|
insiderTransactionSummary.NumberOfSharesAcquiredDisposed=sqlDataReader.GetDouble(1);
|
|
insiderTransactionSummaries.Add(insiderTransactionSummary);
|
|
}
|
|
return insiderTransactionSummaries;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("GetInsiderTransactionSummaries Exception: {0}",exception.ToString()));
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
|
|
public static InsiderTransactions GetLatestInsiderTransactions()
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand =null;
|
|
InsiderTransactions insiderTransactions=new InsiderTransactions();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("SELECT symbol,filing_date,transaction_date,insider_name,ownership_type,securities,nature_of_transaction,number_or_value_acquired_disposed,price,form,sec_accession_number,form_row_number,modified FROM insidertransaction WHERE TRANSACTION_DATE=(SELECT MAX(TRANSACTION_DATE) FROM insidertransaction WHERE TRANSACTION_DATE<=NOW() LIMIT 1)");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
InsiderTransaction insiderTransaction=new InsiderTransaction();
|
|
insiderTransaction.Symbol=sqlDataReader.GetString(0);
|
|
insiderTransaction.FilingDate=sqlDataReader.GetDateTime(1);
|
|
insiderTransaction.TransactionDate=sqlDataReader.GetDateTime(2);
|
|
insiderTransaction.InsiderName=sqlDataReader.GetString(3);
|
|
insiderTransaction.OwnershipType=sqlDataReader.GetString(4);
|
|
insiderTransaction.Securities=sqlDataReader.GetString(5);
|
|
insiderTransaction.NatureOfTransaction=sqlDataReader.GetString(6);
|
|
if(!sqlDataReader.IsDBNull(7))insiderTransaction.NumberOrValueAcquiredDisposed=sqlDataReader.GetDouble(7);
|
|
else insiderTransaction.NumberOrValueAcquiredDisposed=Double.NaN;
|
|
if(!sqlDataReader.IsDBNull(8))insiderTransaction.Price=sqlDataReader.GetDouble(8);
|
|
else insiderTransaction.Price=Double.NaN;
|
|
insiderTransaction.Form=sqlDataReader.GetString(9);
|
|
insiderTransaction.SECAccessionNumber=sqlDataReader.GetString(10);
|
|
insiderTransaction.FormRowNumber=sqlDataReader.GetString(11);
|
|
insiderTransaction.Modified=sqlDataReader.GetDateTime(12);
|
|
insiderTransactions.Add(insiderTransaction);
|
|
}
|
|
return insiderTransactions;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("GetLatestInsiderTransactions Exception: {0}",exception.ToString()));
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
|
|
public static InsiderTransactions GetInsiderTransactions(String symbol)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand =null;
|
|
InsiderTransactions insiderTransactions=new InsiderTransactions();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select symbol,filing_date,transaction_date,insider_name,ownership_type,securities,nature_of_transaction,number_or_value_acquired_disposed,price,form,sec_accession_number,form_row_number,modified from InsiderTransaction where symbol=");
|
|
sb.Append("'").Append(symbol).Append("'").Append(" order by filing_date desc");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
InsiderTransaction insiderTransaction=new InsiderTransaction();
|
|
insiderTransaction.Symbol=sqlDataReader.GetString(0);
|
|
insiderTransaction.FilingDate=sqlDataReader.GetDateTime(1);
|
|
insiderTransaction.TransactionDate=sqlDataReader.GetDateTime(2);
|
|
insiderTransaction.InsiderName=sqlDataReader.GetString(3);
|
|
insiderTransaction.OwnershipType=sqlDataReader.GetString(4);
|
|
insiderTransaction.Securities=sqlDataReader.GetString(5);
|
|
insiderTransaction.NatureOfTransaction=sqlDataReader.GetString(6);
|
|
if(!sqlDataReader.IsDBNull(7))insiderTransaction.NumberOrValueAcquiredDisposed=sqlDataReader.GetDouble(7);
|
|
else insiderTransaction.NumberOrValueAcquiredDisposed=Double.NaN;
|
|
if(!sqlDataReader.IsDBNull(8))insiderTransaction.Price=sqlDataReader.GetDouble(8);
|
|
else insiderTransaction.Price=Double.NaN;
|
|
insiderTransaction.Form=sqlDataReader.GetString(9);
|
|
insiderTransaction.SECAccessionNumber=sqlDataReader.GetString(10);
|
|
insiderTransaction.FormRowNumber=sqlDataReader.GetString(11);
|
|
insiderTransaction.Modified=sqlDataReader.GetDateTime(12);
|
|
insiderTransactions.Add(insiderTransaction);
|
|
}
|
|
return insiderTransactions;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("GetInsiderTransactions Exception: {0}",exception.ToString()));
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
|
|
|
|
public static bool InsertInsiderTransactions(InsiderTransactions insiderTransactions)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlTransaction sqlTransaction = null;
|
|
Dictionary<String,InsiderTransaction> insiderTransactionsUniqueKey = new Dictionary<String,InsiderTransaction>();
|
|
String strQuery = null;
|
|
DateTime modified=DateTime.Now;
|
|
|
|
try
|
|
{
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
if (!DeleteInsiderTransactions(insiderTransactions, sqlConnection)) return false;
|
|
sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
|
|
foreach (InsiderTransaction insiderTransaction in insiderTransactions)
|
|
{
|
|
String insiderTransactionKey = insiderTransaction.Form + insiderTransaction.SECAccessionNumber + insiderTransaction.FormRowNumber;
|
|
if (insiderTransactionsUniqueKey.ContainsKey(insiderTransactionKey))
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,"*********************************************************************************************************");
|
|
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Collection already contains a value for Form:{0} SECAccessionNumber:{1} FormRowNumber:{2}",
|
|
insiderTransaction.Form,insiderTransaction.SECAccessionNumber,insiderTransaction.FormRowNumber));
|
|
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Item attempting to add: Symbol:{0} InsiderName:{1} OwnershipType:{2} Securities:{3} NatureOfTransaction:{4} NumberOrValueAcquiredDisposed:{5} Price:{7} FilingDate:{7} TransactionDate:{8}",
|
|
insiderTransaction.Symbol, insiderTransaction.InsiderName,insiderTransaction.OwnershipType,insiderTransaction.Securities,insiderTransaction.NatureOfTransaction,insiderTransaction.NumberOrValueAcquiredDisposed,insiderTransaction.Price,
|
|
Utility.DateTimeToStringMMHDDHYYYY(insiderTransaction.FilingDate),Utility.DateTimeToStringMMSDDSYYYY(insiderTransaction.TransactionDate)));
|
|
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Item already in collection: Symbol:{0} InsiderName:{1} OwnershipType:{2} Securities:{3} NatureOfTransaction:{4} NumberOrValueAcquiredDisposed:{5} Price:{6} FilingDate:{7} TransactionDate:{8}",
|
|
insiderTransaction.Symbol,insiderTransaction.InsiderName,insiderTransaction.OwnershipType,insiderTransaction.Securities,insiderTransaction.NatureOfTransaction,insiderTransaction.NumberOrValueAcquiredDisposed,insiderTransaction.Price,
|
|
Utility.DateTimeToStringMMHDDHYYYY(insiderTransaction.FilingDate),Utility.DateTimeToStringMMSDDSYYYY(insiderTransaction.TransactionDate)));
|
|
|
|
MDTrace.WriteLine(LogLevel.DEBUG,"*********************************************************************************************************");
|
|
|
|
continue;
|
|
}
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("insert into InsiderTransaction (symbol,filing_date,transaction_date,insider_name,ownership_type,securities,nature_of_transaction,number_or_value_acquired_disposed,price,form,sec_accession_number,form_row_number,modified) ");
|
|
sb.Append("values(");
|
|
sb.Append("'").Append(insiderTransaction.Symbol).Append("'").Append(",");
|
|
sb.Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(insiderTransaction.FilingDate)).Append("'").Append(",");
|
|
sb.Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(insiderTransaction.TransactionDate)).Append("'").Append(",");
|
|
sb.Append("'").Append(SqlUtils.SqlString(insiderTransaction.InsiderName)).Append("'").Append(",");
|
|
sb.Append("'").Append(SqlUtils.SqlString(insiderTransaction.OwnershipType)).Append("'").Append(",");
|
|
sb.Append("'").Append(SqlUtils.SqlString(insiderTransaction.Securities)).Append("'").Append(",");
|
|
sb.Append("'").Append(SqlUtils.SqlString(insiderTransaction.NatureOfTransaction)).Append("'").Append(",");
|
|
if (!Double.IsNaN(insiderTransaction.NumberOrValueAcquiredDisposed)) sb.Append("'").Append(insiderTransaction.NumberOrValueAcquiredDisposed).Append("'").Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(insiderTransaction.Price)) sb.Append("'").Append(insiderTransaction.Price).Append("'").Append(",");
|
|
else sb.Append("null").Append(",");
|
|
sb.Append("'").Append(insiderTransaction.Form).Append("'").Append(",");
|
|
sb.Append("'").Append(insiderTransaction.SECAccessionNumber).Append("'").Append(",");
|
|
sb.Append("'").Append(insiderTransaction.FormRowNumber).Append("'").Append(",");
|
|
sb.Append("'").Append(SqlUtils.ToSqlDateTime(modified)).Append("'");
|
|
sb.Append(")");
|
|
strQuery = sb.ToString();
|
|
MySqlCommand sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlCommand.ExecuteNonQuery();
|
|
insiderTransactionsUniqueKey.Add(insiderTransactionKey, insiderTransaction);
|
|
sqlCommand.Dispose();
|
|
}
|
|
sqlTransaction.Commit();
|
|
return true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("InsertInsiderTransactions Exception: {0}",exception.ToString()));
|
|
MDTrace.WriteLine(LogLevel.DEBUG,"Query was '" + strQuery + "'");
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
if(null!=sqlTransaction)sqlTransaction.Dispose();
|
|
}
|
|
}
|
|
private static bool DeleteInsiderTransactions(InsiderTransactions insiderTransactions, MySqlConnection sqlConnection)
|
|
{
|
|
StringBuilder sb = null;
|
|
String strQuery = null;
|
|
MySqlTransaction sqlTransaction = null;
|
|
|
|
try
|
|
{
|
|
if (null == insiderTransactions) return false;
|
|
sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
|
|
foreach (InsiderTransaction insiderTransaction in insiderTransactions)
|
|
{
|
|
sb = new StringBuilder();
|
|
sb.Append("delete from InsiderTransaction where ");
|
|
sb.Append("symbol='").Append(insiderTransaction.Symbol).Append("'").Append(" and ");
|
|
sb.Append("form='").Append(insiderTransaction.Form).Append("'").Append(" and ");
|
|
sb.Append("sec_accession_number='").Append(insiderTransaction.SECAccessionNumber).Append("'").Append(" and ");
|
|
sb.Append("form_row_number='").Append(insiderTransaction.FormRowNumber).Append("'");
|
|
strQuery = sb.ToString();
|
|
MySqlCommand sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlCommand.ExecuteNonQuery();
|
|
}
|
|
sqlTransaction.Commit();
|
|
return true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("DeleteInsiderTransactions Exception: {0}",exception.ToString()));
|
|
if (null != strQuery) MDTrace.WriteLine(LogLevel.DEBUG,"Query was " + strQuery);
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlTransaction)sqlTransaction.Dispose();
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// Delete InsiderTransaction for given symbol for all years>=given year.
|
|
/// </summary>
|
|
/// <param name="symbol">The symbol.</param>
|
|
/// <param name="yearsGreaterEqual">All years greater then or equal to the year.</param>
|
|
public static bool DeleteInsiderTransactionsYearsGreaterEqual(String symbol, int yearsGreaterEqual)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
StringBuilder sb = null;
|
|
String strQuery = null;
|
|
MySqlTransaction sqlTransaction = null;
|
|
|
|
try
|
|
{
|
|
if (null == symbol) return false;
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
|
|
sb = new StringBuilder();
|
|
sb.Append("DELETE FROM InsiderTransaction WHERE symbol=");
|
|
sb.Append("'").Append(symbol).Append("'");
|
|
sb.Append(" AND YEAR(filing_date)>=").Append(yearsGreaterEqual);
|
|
strQuery = sb.ToString();
|
|
MySqlCommand sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlCommand.ExecuteNonQuery();
|
|
sqlTransaction.Commit();
|
|
return true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("DeleteInsiderTransactionsYearsGreaterEqual Exception: {0}",exception.ToString()));
|
|
if (null != strQuery) MDTrace.WriteLine(LogLevel.DEBUG,"Query was " + strQuery);
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlTransaction)sqlTransaction.Dispose();
|
|
if(null!=sqlConnection)sqlConnection.Close();
|
|
}
|
|
}
|
|
}
|
|
}
|