378 lines
19 KiB
C#
378 lines
19 KiB
C#
using System;
|
|
using System.Text;
|
|
using System.Collections.Generic;
|
|
using MySql.Data.MySqlClient;
|
|
using MarketData.MarketDataModel;
|
|
using MarketData.Utils;
|
|
|
|
namespace MarketData.DataAccess
|
|
{
|
|
public class CashflowStatementDA
|
|
{
|
|
private CashflowStatementDA()
|
|
{
|
|
}
|
|
// ********************************************* B A L A N C E S H E E T **************************************
|
|
public static CashflowStatement GetCashflowStatementOnOrBefore(String symbol, DateTime asof,CashflowStatement.PeriodType periodType)
|
|
{
|
|
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,depreciation_and_amortization,deferred_income_taxes,stock_based_compensation,accounts_receivable,inventory,accounts_payable,accrued_liabilities,operating_cashflow,free_cashflow,period,modified from cashflowstatement where symbol=");
|
|
sb.Append("'").Append(symbol).Append("'").Append(" ");
|
|
sb.Append("and period=").Append(periodType.Equals(CashflowStatement.PeriodType.Annual) ? 0 : 1).Append(" ");
|
|
sb.Append("and asof<=").Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(asof)).Append("'");
|
|
sb.Append(" order by asof desc");;
|
|
sb.Append(" limit 1").Append(";");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
if (!sqlDataReader.Read()) return null;
|
|
CashflowStatement cashflowStatement=new CashflowStatement();
|
|
cashflowStatement.Symbol=sqlDataReader.GetString(0);
|
|
cashflowStatement.AsOf=sqlDataReader.GetDateTime(1);
|
|
if (!sqlDataReader.IsDBNull(2))cashflowStatement.DepreciationAndAmortization=sqlDataReader.GetDouble(2);
|
|
if (!sqlDataReader.IsDBNull(3))cashflowStatement.DeferredIncomeTaxes=sqlDataReader.GetDouble(3);
|
|
if (!sqlDataReader.IsDBNull(4))cashflowStatement.StockBasedCompensation=sqlDataReader.GetDouble(4);
|
|
if (!sqlDataReader.IsDBNull(5))cashflowStatement.AccountsReceivable=sqlDataReader.GetDouble(5);
|
|
if (!sqlDataReader.IsDBNull(6))cashflowStatement.Inventory=sqlDataReader.GetDouble(6);
|
|
if (!sqlDataReader.IsDBNull(7))cashflowStatement.AccountsPayable=sqlDataReader.GetDouble(7);
|
|
if (!sqlDataReader.IsDBNull(8))cashflowStatement.AccruedLiabilities=sqlDataReader.GetDouble(8);
|
|
if (!sqlDataReader.IsDBNull(9))cashflowStatement.OperatingCashflow=sqlDataReader.GetDouble(9);
|
|
if (!sqlDataReader.IsDBNull(10))cashflowStatement.FreeCashflow=sqlDataReader.GetDouble(10);
|
|
if (!sqlDataReader.IsDBNull(11)) cashflowStatement.Period = sqlDataReader.GetInt32(11) == 0 ? CashflowStatement.PeriodType.Annual : CashflowStatement.PeriodType.Quarterly;
|
|
if (!sqlDataReader.IsDBNull(12)) cashflowStatement.Modified = sqlDataReader.GetDateTime(12);
|
|
return cashflowStatement;
|
|
}
|
|
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 CheckCashflowStatementModifiedOn(String symbol, DateTime modified,CashflowStatement.PeriodType periodType)
|
|
{
|
|
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 cashflowstatement");
|
|
sb.Append(" where symbol='").Append(symbol).Append("'").Append(" ");
|
|
sb.Append(" and period=").Append(periodType.Equals(CashflowStatement.PeriodType.Annual) ? 0 : 1).Append("").Append(" ");
|
|
sb.Append("and modified='").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();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static List<DateTime> GetCashflowStatementDates(String symbol,CashflowStatement.PeriodType periodType)
|
|
{
|
|
List<DateTime> dates = new List<DateTime>();
|
|
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 asof from cashflowstatement where symbol='").Append(symbol).Append("' ");
|
|
sb.Append("and period='").Append(periodType.Equals(CashflowStatement.PeriodType.Annual) ? 0 : 1).Append("'");
|
|
sb.Append(" order by 1 asc;");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while(sqlDataReader.Read())
|
|
{
|
|
DateTime asof=sqlDataReader.GetDateTime(0);
|
|
dates.Add(asof);
|
|
}
|
|
return dates;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return dates;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static DateTime? GetLatestCashflowStatementDate(String symbol,CashflowStatement.PeriodType periodType)
|
|
{
|
|
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 cashflowstatement");
|
|
sb.Append(" where symbol='").Append(symbol).Append("' ");
|
|
sb.Append(" and period=").Append(periodType.Equals(CashflowStatement.PeriodType.Annual) ? 0 : 1);
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
if (sqlDataReader.Read())
|
|
{
|
|
if (sqlDataReader.IsDBNull(0)) maxDate = null;
|
|
else 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();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static CashflowStatement GetCashflowStatement(String symbol,CashflowStatement.PeriodType periodType)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand =null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
DateTime? latestDate = GetLatestCashflowStatementDate(symbol,periodType);
|
|
if (null == latestDate) return null;
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select symbol,asof,depreciation_and_amortization,deferred_income_taxes,stock_based_compensation,accounts_receivable,inventory,accounts_payable,accrued_liabilities,operating_cashflow,free_cashflow,period,modified from cashflowstatement where symbol=");
|
|
sb.Append("'").Append(symbol).Append("'").Append(" ");
|
|
sb.Append("and period=").Append(periodType.Equals(CashflowStatement.PeriodType.Annual) ? 0 : 1).Append(" ");
|
|
sb.Append("and asof=").Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(latestDate.Value)).Append("'").Append(";");
|
|
strQuery = sb.ToString(); ;
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
if (!sqlDataReader.Read()) return null;
|
|
CashflowStatement cashflowStatement=new CashflowStatement();
|
|
cashflowStatement.Symbol=sqlDataReader.GetString(0);
|
|
cashflowStatement.AsOf=sqlDataReader.GetDateTime(1);
|
|
if (!sqlDataReader.IsDBNull(2))cashflowStatement.DepreciationAndAmortization=sqlDataReader.GetDouble(2);
|
|
if (!sqlDataReader.IsDBNull(3))cashflowStatement.DeferredIncomeTaxes=sqlDataReader.GetDouble(3);
|
|
if (!sqlDataReader.IsDBNull(4))cashflowStatement.StockBasedCompensation=sqlDataReader.GetDouble(4);
|
|
if (!sqlDataReader.IsDBNull(5))cashflowStatement.AccountsReceivable=sqlDataReader.GetDouble(5);
|
|
if (!sqlDataReader.IsDBNull(6))cashflowStatement.Inventory=sqlDataReader.GetDouble(6);
|
|
if (!sqlDataReader.IsDBNull(7))cashflowStatement.AccountsPayable=sqlDataReader.GetDouble(7);
|
|
if (!sqlDataReader.IsDBNull(8))cashflowStatement.AccruedLiabilities=sqlDataReader.GetDouble(8);
|
|
if (!sqlDataReader.IsDBNull(9))cashflowStatement.OperatingCashflow=sqlDataReader.GetDouble(9);
|
|
if (!sqlDataReader.IsDBNull(10))cashflowStatement.FreeCashflow=sqlDataReader.GetDouble(10);
|
|
if (!sqlDataReader.IsDBNull(11)) cashflowStatement.Period = sqlDataReader.GetInt32(11) == 0 ? CashflowStatement.PeriodType.Annual : CashflowStatement.PeriodType.Quarterly;
|
|
if (!sqlDataReader.IsDBNull(12)) cashflowStatement.Modified = sqlDataReader.GetDateTime(12);
|
|
return cashflowStatement;
|
|
}
|
|
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 CashflowStatement GetCashflowStatement(String symbol, DateTime asof,CashflowStatement.PeriodType periodType)
|
|
{
|
|
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,depreciation_and_amortization,deferred_income_taxes,stock_based_compensation,accounts_receivable,inventory,accounts_payable,accrued_liabilities,operating_cashflow,free_cashflow,period,modified from cashflowstatement where symbol=");
|
|
sb.Append("'").Append(symbol).Append("'").Append(" ");
|
|
sb.Append("and period=").Append(periodType.Equals(CashflowStatement.PeriodType.Annual) ? 0 : 1).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;
|
|
CashflowStatement cashflowStatement=new CashflowStatement();
|
|
cashflowStatement.Symbol=sqlDataReader.GetString(0);
|
|
cashflowStatement.AsOf=sqlDataReader.GetDateTime(1);
|
|
if (!sqlDataReader.IsDBNull(2))cashflowStatement.DepreciationAndAmortization=sqlDataReader.GetDouble(2);
|
|
if (!sqlDataReader.IsDBNull(3))cashflowStatement.DeferredIncomeTaxes=sqlDataReader.GetDouble(3);
|
|
if (!sqlDataReader.IsDBNull(4))cashflowStatement.StockBasedCompensation=sqlDataReader.GetDouble(4);
|
|
if (!sqlDataReader.IsDBNull(5))cashflowStatement.AccountsReceivable=sqlDataReader.GetDouble(5);
|
|
if (!sqlDataReader.IsDBNull(6))cashflowStatement.Inventory=sqlDataReader.GetDouble(6);
|
|
if (!sqlDataReader.IsDBNull(7))cashflowStatement.AccountsPayable=sqlDataReader.GetDouble(7);
|
|
if (!sqlDataReader.IsDBNull(8))cashflowStatement.AccruedLiabilities=sqlDataReader.GetDouble(8);
|
|
if (!sqlDataReader.IsDBNull(9))cashflowStatement.OperatingCashflow=sqlDataReader.GetDouble(9);
|
|
if (!sqlDataReader.IsDBNull(10))cashflowStatement.FreeCashflow=sqlDataReader.GetDouble(10);
|
|
if (!sqlDataReader.IsDBNull(11)) cashflowStatement.Period = sqlDataReader.GetInt32(11) == 0 ? CashflowStatement.PeriodType.Annual : CashflowStatement.PeriodType.Quarterly;
|
|
if (!sqlDataReader.IsDBNull(12)) cashflowStatement.Modified = sqlDataReader.GetDateTime(12);
|
|
return cashflowStatement;
|
|
}
|
|
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 InsertCashflowStatement(List<CashflowStatement> cashflowStatements)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlTransaction sqlTransaction = null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
DateTime modified=DateTime.Now;
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
|
|
DeleteCashflowStatements(cashflowStatements, sqlConnection, sqlTransaction);
|
|
for (int index = 0; index < cashflowStatements.Count; index++)
|
|
{
|
|
CashflowStatement cashflowStatement = cashflowStatements[index];
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("insert into cashflowstatement (symbol,asof,depreciation_and_amortization,deferred_income_taxes,stock_based_compensation,accounts_receivable,inventory,accounts_payable,accrued_liabilities,operating_cashflow,free_cashflow,period,modified) ");
|
|
sb.Append("values(");
|
|
sb.Append("'").Append(cashflowStatement.Symbol).Append("'").Append(",");
|
|
sb.Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(cashflowStatement.AsOf)).Append("'").Append(",");
|
|
if (!Double.IsNaN(cashflowStatement.DepreciationAndAmortization)) sb.Append(cashflowStatement.DepreciationAndAmortization).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(cashflowStatement.DeferredIncomeTaxes)) sb.Append(cashflowStatement.DeferredIncomeTaxes).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(cashflowStatement.StockBasedCompensation)) sb.Append(cashflowStatement.StockBasedCompensation).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(cashflowStatement.AccountsReceivable)) sb.Append(cashflowStatement.AccountsReceivable).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(cashflowStatement.Inventory)) sb.Append(cashflowStatement.Inventory).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(cashflowStatement.AccountsPayable)) sb.Append(cashflowStatement.AccountsPayable).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(cashflowStatement.AccruedLiabilities)) sb.Append(cashflowStatement.AccruedLiabilities).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(cashflowStatement.OperatingCashflow)) sb.Append(cashflowStatement.OperatingCashflow).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(cashflowStatement.FreeCashflow)) sb.Append(cashflowStatement.FreeCashflow).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
sb.Append(CashflowStatement.PeriodType.Annual.Equals(cashflowStatement.Period)?0:1).Append(",");
|
|
sb.Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(modified)).Append("'");
|
|
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 DeleteCashflowStatements(List<CashflowStatement> cashflowStatements, MySqlConnection sqlConnection, MySqlTransaction sqlTransaction)
|
|
{
|
|
for (int index = 0; index < cashflowStatements.Count; index++)
|
|
{
|
|
DeleteCashflowStatement(cashflowStatements[index], sqlConnection, sqlTransaction);
|
|
}
|
|
return true;
|
|
}
|
|
private static bool DeleteCashflowStatement(CashflowStatement cashflowStatement, MySqlConnection sqlConnection, MySqlTransaction sqlTransaction)
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
sb.Append("delete from cashflowstatement where ");
|
|
sb.Append("symbol='").Append(cashflowStatement.Symbol).Append("'");
|
|
sb.Append(" and ");
|
|
sb.Append("asof='").Append(Utility.DateTimeToStringYYYYHMMHDD(cashflowStatement.AsOf)).Append("'");
|
|
sb.Append(" and period=").Append(cashflowStatement.Period.Equals(CashflowStatement.PeriodType.Annual)?0:1);
|
|
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
|
|
{
|
|
}
|
|
}
|
|
}
|
|
}
|