619 lines
32 KiB
C#
619 lines
32 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 BalanceSheetDA
|
|
{
|
|
private BalanceSheetDA()
|
|
{
|
|
}
|
|
// ********************************************* B A L A N C E S H E E T **************************************
|
|
// get the greatest date on record that is on or before asof date
|
|
public static DateTime? GetMaxDateFromBalanceSheet(String symbol, DateTime asof,BalanceSheet.PeriodType periodType)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
DateTime? maxDate=null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select asof from balancesheet where symbol=");
|
|
sb.Append("'").Append(symbol).Append("'").Append(" ");
|
|
sb.Append("and asof<=").Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(asof)).Append("' ");
|
|
sb.Append("and period=").Append(periodType.Equals(BalanceSheet.PeriodType.Annual) ? 0 : 1).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;
|
|
if(!sqlDataReader.IsDBNull(0))maxDate=sqlDataReader.GetDateTime(0);
|
|
return maxDate;
|
|
}
|
|
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 double GetTotalStockHolderEquityOnOrBefore(String symbol, DateTime asof,BalanceSheet.PeriodType periodType)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
double totalStockholderEquity=double.NaN;
|
|
|
|
try
|
|
{
|
|
DateTime? maxDate=GetMaxDateFromBalanceSheet(symbol,asof,periodType);
|
|
if(null==maxDate)return double.NaN;
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select total_stockholder_equity from balancesheet where symbol=");
|
|
sb.Append("'").Append(symbol).Append("'").Append(" ");
|
|
sb.Append("and period=").Append(periodType.Equals(BalanceSheet.PeriodType.Annual) ? 0 : 1).Append(" ");
|
|
sb.Append("and asof=").Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(maxDate.Value)).Append("'");
|
|
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 double.NaN;
|
|
if(!sqlDataReader.IsDBNull(0))totalStockholderEquity=sqlDataReader.GetDouble(0);
|
|
return totalStockholderEquity;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return double.NaN;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static BalanceSheet GetBalanceSheetOnOrBefore(String symbol, DateTime asof,BalanceSheet.PeriodType periodType)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand=null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
DateTime? maxDate=GetMaxDateFromBalanceSheet(symbol,asof,periodType);
|
|
if(null==maxDate)return null;
|
|
StringBuilder sb = new StringBuilder();
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select symbol,asof,long_term_debt,other_liabilities,deferred_long_term_liabilities,minority_interest,negative_goodwill,total_stockholder_equity,inventory,property_plant_and_equipment,intangible_assets,accumulated_amortization,goodwill,period,total_assets,total_current_assets,total_liabilities,total_current_liabilities,cash_and_cash_equivalents,modified from balancesheet where symbol=");
|
|
sb.Append("'").Append(symbol).Append("'").Append(" ");
|
|
sb.Append("and period=").Append(periodType.Equals(BalanceSheet.PeriodType.Annual) ? 0 : 1).Append(" ");
|
|
sb.Append("and asof=").Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(maxDate.Value)).Append("'");
|
|
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;
|
|
BalanceSheet balanceSheet=new BalanceSheet();
|
|
balanceSheet.Symbol=sqlDataReader.GetString(0);
|
|
balanceSheet.AsOf=sqlDataReader.GetDateTime(1);
|
|
if (!sqlDataReader.IsDBNull(2))balanceSheet.LongTermDebt= sqlDataReader.GetDouble(2);
|
|
if (!sqlDataReader.IsDBNull(3))balanceSheet.OtherLiabilities=sqlDataReader.GetDouble(3);
|
|
if (!sqlDataReader.IsDBNull(4))balanceSheet.DeferredLongTermLiabilities=sqlDataReader.GetDouble(4);
|
|
if (!sqlDataReader.IsDBNull(5))balanceSheet.MinorityInterest=sqlDataReader.GetDouble(5);
|
|
if (!sqlDataReader.IsDBNull(6))balanceSheet.NegativeGoodwill=sqlDataReader.GetDouble(6);
|
|
if (!sqlDataReader.IsDBNull(7)) balanceSheet.TotalStockHolderEquity = sqlDataReader.GetDouble(7);
|
|
if (!sqlDataReader.IsDBNull(8)) balanceSheet.Inventory = sqlDataReader.GetDouble(8);
|
|
if (!sqlDataReader.IsDBNull(9)) balanceSheet.PropertyPlantAndEquipment = sqlDataReader.GetDouble(9);
|
|
if (!sqlDataReader.IsDBNull(10)) balanceSheet.IntangibleAssets = sqlDataReader.GetDouble(10);
|
|
if (!sqlDataReader.IsDBNull(11)) balanceSheet.AccumulatedAmortization = sqlDataReader.GetDouble(11);
|
|
if (!sqlDataReader.IsDBNull(12)) balanceSheet.Goodwill = sqlDataReader.GetDouble(12);
|
|
if (!sqlDataReader.IsDBNull(13)) balanceSheet.Period = sqlDataReader.GetInt32(13) == 0 ? BalanceSheet.PeriodType.Annual : BalanceSheet.PeriodType.Quarterly;
|
|
if (!sqlDataReader.IsDBNull(14)) balanceSheet.TotalAssets = sqlDataReader.GetDouble(14);
|
|
if (!sqlDataReader.IsDBNull(15)) balanceSheet.TotalCurrentAssets = sqlDataReader.GetDouble(15);
|
|
if (!sqlDataReader.IsDBNull(16)) balanceSheet.TotalLiabilities = sqlDataReader.GetDouble(16);
|
|
if (!sqlDataReader.IsDBNull(17)) balanceSheet.TotalCurrentLiabilities = sqlDataReader.GetDouble(17);
|
|
if (!sqlDataReader.IsDBNull(18)) balanceSheet.CashAndCashEquivalents = sqlDataReader.GetDouble(18);
|
|
if (!sqlDataReader.IsDBNull(19)) balanceSheet.Modified = sqlDataReader.GetDateTime(19);
|
|
return balanceSheet;
|
|
}
|
|
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 CheckBalanceSheetModifiedOn(String symbol, DateTime modified,BalanceSheet.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 balancesheet");
|
|
sb.Append(" where symbol='").Append(symbol).Append("'").Append(" ");
|
|
sb.Append(" and period=").Append(periodType.Equals(BalanceSheet.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> GetBalanceSheetDates(String symbol,BalanceSheet.PeriodType periodType)
|
|
{
|
|
List<DateTime> balanceSheetDates = 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 balancesheet where symbol='").Append(symbol).Append("' ");
|
|
sb.Append("and period='").Append(periodType.Equals(BalanceSheet.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);
|
|
balanceSheetDates.Add(asof);
|
|
}
|
|
return balanceSheetDates;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception);
|
|
return balanceSheetDates;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlCommand)sqlCommand.Dispose();
|
|
if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
|
|
if (null != sqlConnection) sqlConnection.Close();
|
|
}
|
|
}
|
|
public static DateTime? GetLatestBalanceSheetDate(String symbol,BalanceSheet.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 balancesheet");
|
|
sb.Append(" where symbol='").Append(symbol).Append("' ");
|
|
sb.Append(" and period=").Append(periodType.Equals(BalanceSheet.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 BalanceSheet GetBalanceSheet(String symbol,BalanceSheet.PeriodType periodType)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlDataReader sqlDataReader = null;
|
|
MySqlCommand sqlCommand =null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
DateTime? latestDate = GetLatestBalanceSheetDate(symbol,periodType);
|
|
if (null == latestDate) return null;
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sb.Append("select symbol,asof,long_term_debt,other_liabilities,deferred_long_term_liabilities,minority_interest,negative_goodwill,total_stockholder_equity,inventory,property_plant_and_equipment,intangible_assets,accumulated_amortization,goodwill,period,total_assets,total_current_assets,total_liabilities,total_current_liabilities,cash_and_cash_equivalents,modified from balancesheet where symbol=");
|
|
sb.Append("'").Append(symbol).Append("'").Append(" ");
|
|
sb.Append("and period=").Append(periodType.Equals(BalanceSheet.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;
|
|
BalanceSheet balanceSheet=new BalanceSheet();
|
|
balanceSheet.Symbol=sqlDataReader.GetString(0);
|
|
balanceSheet.AsOf=sqlDataReader.GetDateTime(1);
|
|
if (!sqlDataReader.IsDBNull(2))balanceSheet.LongTermDebt= sqlDataReader.GetDouble(2);
|
|
if (!sqlDataReader.IsDBNull(3))balanceSheet.OtherLiabilities=sqlDataReader.GetDouble(3);
|
|
if (!sqlDataReader.IsDBNull(4))balanceSheet.DeferredLongTermLiabilities=sqlDataReader.GetDouble(4);
|
|
if (!sqlDataReader.IsDBNull(5))balanceSheet.MinorityInterest=sqlDataReader.GetDouble(5);
|
|
if (!sqlDataReader.IsDBNull(6))balanceSheet.NegativeGoodwill=sqlDataReader.GetDouble(6);
|
|
if (!sqlDataReader.IsDBNull(7))balanceSheet.TotalStockHolderEquity = sqlDataReader.GetDouble(7);
|
|
if (!sqlDataReader.IsDBNull(8)) balanceSheet.Inventory = sqlDataReader.GetDouble(8);
|
|
if (!sqlDataReader.IsDBNull(9)) balanceSheet.PropertyPlantAndEquipment = sqlDataReader.GetDouble(9);
|
|
if (!sqlDataReader.IsDBNull(10)) balanceSheet.IntangibleAssets = sqlDataReader.GetDouble(10);
|
|
if (!sqlDataReader.IsDBNull(11)) balanceSheet.AccumulatedAmortization = sqlDataReader.GetDouble(11);
|
|
if (!sqlDataReader.IsDBNull(12)) balanceSheet.Goodwill = sqlDataReader.GetDouble(12);
|
|
if (!sqlDataReader.IsDBNull(13)) balanceSheet.Period = sqlDataReader.GetInt32(13)==0?BalanceSheet.PeriodType.Annual:BalanceSheet.PeriodType.Quarterly;
|
|
if (!sqlDataReader.IsDBNull(14)) balanceSheet.TotalAssets = sqlDataReader.GetDouble(14);
|
|
if (!sqlDataReader.IsDBNull(15)) balanceSheet.TotalCurrentAssets = sqlDataReader.GetDouble(15);
|
|
if (!sqlDataReader.IsDBNull(16)) balanceSheet.TotalLiabilities = sqlDataReader.GetDouble(16);
|
|
if (!sqlDataReader.IsDBNull(17)) balanceSheet.TotalCurrentLiabilities = sqlDataReader.GetDouble(17);
|
|
if (!sqlDataReader.IsDBNull(18)) balanceSheet.CashAndCashEquivalents = sqlDataReader.GetDouble(18);
|
|
if (!sqlDataReader.IsDBNull(19)) balanceSheet.Modified = sqlDataReader.GetDateTime(19);
|
|
return balanceSheet;
|
|
}
|
|
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 BalanceSheet GetBalanceSheet(String symbol, DateTime asof,BalanceSheet.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,long_term_debt,other_liabilities,deferred_long_term_liabilities,minority_interest,negative_goodwill,total_stockholder_equity,inventory,property_plant_and_equipment,intangible_assets,accumulated_amortization,goodwill,period,total_assets,total_current_assets,total_liabilities,total_current_liabilities,cash_and_cash_equivalents,modified from balancesheet where symbol=");
|
|
sb.Append("'").Append(symbol).Append("'").Append(" ");
|
|
sb.Append("and period=").Append(periodType.Equals(BalanceSheet.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;
|
|
BalanceSheet balanceSheet=new BalanceSheet();
|
|
balanceSheet.Symbol=sqlDataReader.GetString(0);
|
|
balanceSheet.AsOf=sqlDataReader.GetDateTime(1);
|
|
if (!sqlDataReader.IsDBNull(2))balanceSheet.LongTermDebt= sqlDataReader.GetDouble(2);
|
|
if (!sqlDataReader.IsDBNull(3))balanceSheet.OtherLiabilities=sqlDataReader.GetDouble(3);
|
|
if (!sqlDataReader.IsDBNull(4))balanceSheet.DeferredLongTermLiabilities=sqlDataReader.GetDouble(4);
|
|
if (!sqlDataReader.IsDBNull(5))balanceSheet.MinorityInterest=sqlDataReader.GetDouble(5);
|
|
if (!sqlDataReader.IsDBNull(6))balanceSheet.NegativeGoodwill=sqlDataReader.GetDouble(6);
|
|
if (!sqlDataReader.IsDBNull(7)) balanceSheet.TotalStockHolderEquity = sqlDataReader.GetDouble(7);
|
|
if (!sqlDataReader.IsDBNull(8)) balanceSheet.Inventory = sqlDataReader.GetDouble(8);
|
|
if (!sqlDataReader.IsDBNull(9)) balanceSheet.PropertyPlantAndEquipment = sqlDataReader.GetDouble(9);
|
|
if (!sqlDataReader.IsDBNull(10)) balanceSheet.IntangibleAssets = sqlDataReader.GetDouble(10);
|
|
if (!sqlDataReader.IsDBNull(11)) balanceSheet.AccumulatedAmortization = sqlDataReader.GetDouble(11);
|
|
if (!sqlDataReader.IsDBNull(12)) balanceSheet.Goodwill = sqlDataReader.GetDouble(12);
|
|
if (!sqlDataReader.IsDBNull(13)) balanceSheet.Period = sqlDataReader.GetInt32(13) == 0 ? BalanceSheet.PeriodType.Annual : BalanceSheet.PeriodType.Quarterly;
|
|
if (!sqlDataReader.IsDBNull(14)) balanceSheet.TotalAssets = sqlDataReader.GetDouble(14);
|
|
if (!sqlDataReader.IsDBNull(15)) balanceSheet.TotalCurrentAssets = sqlDataReader.GetDouble(15);
|
|
if (!sqlDataReader.IsDBNull(16)) balanceSheet.TotalLiabilities = sqlDataReader.GetDouble(16);
|
|
if (!sqlDataReader.IsDBNull(17)) balanceSheet.TotalCurrentLiabilities = sqlDataReader.GetDouble(17);
|
|
if (!sqlDataReader.IsDBNull(18)) balanceSheet.CashAndCashEquivalents = sqlDataReader.GetDouble(18);
|
|
if (!sqlDataReader.IsDBNull(19)) balanceSheet.Modified = sqlDataReader.GetDateTime(19);
|
|
return balanceSheet;
|
|
}
|
|
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 TimeSeriesCollection GetInventory(String symbol,BalanceSheet.PeriodType period)
|
|
{
|
|
TimeSeriesCollection timeSeriesCollection = new TimeSeriesCollection();
|
|
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,inventory from balancesheet where symbol='").Append(symbol).Append("'").Append(" ");
|
|
sb.Append(" and period=").Append(period.Equals(BalanceSheet.PeriodType.Annual) ? 0 : 1).Append(" order by asof desc;");
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
TimeSeriesElement timeSeriesElement = new TimeSeriesElement();
|
|
timeSeriesElement.Symbol = sqlDataReader.GetString(0);
|
|
timeSeriesElement.AsOf = sqlDataReader.GetDateTime(1);
|
|
timeSeriesElement.Type = period.Equals(BalanceSheet.PeriodType.Quarterly) ? TimeSeriesElement.ElementType.QuarterlyInventory : TimeSeriesElement.ElementType.Inventory;
|
|
if (!sqlDataReader.IsDBNull(2)) timeSeriesElement.Value = sqlDataReader.GetDouble(2);
|
|
else continue;
|
|
timeSeriesCollection.Add(timeSeriesElement);
|
|
}
|
|
return timeSeriesCollection;
|
|
}
|
|
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 TimeSeriesCollection GetGoodwill(String symbol, BalanceSheet.PeriodType period)
|
|
{
|
|
TimeSeriesCollection timeSeriesCollection = new TimeSeriesCollection();
|
|
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,goodwill from balancesheet where symbol='").Append(symbol).Append("'").Append(" ");
|
|
sb.Append(" and period=").Append(period.Equals(BalanceSheet.PeriodType.Annual) ? 0 : 1).Append(" order by asof desc;");
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
TimeSeriesElement timeSeriesElement = new TimeSeriesElement();
|
|
timeSeriesElement.Symbol = sqlDataReader.GetString(0);
|
|
timeSeriesElement.AsOf = sqlDataReader.GetDateTime(1);
|
|
timeSeriesElement.Type = period.Equals(BalanceSheet.PeriodType.Quarterly) ? TimeSeriesElement.ElementType.QuarterlyInventory : TimeSeriesElement.ElementType.Inventory;
|
|
if (!sqlDataReader.IsDBNull(2)) timeSeriesElement.Value = sqlDataReader.GetDouble(2);
|
|
else continue;
|
|
timeSeriesCollection.Add(timeSeriesElement);
|
|
}
|
|
return timeSeriesCollection;
|
|
}
|
|
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 TimeSeriesCollection GetTotalStockholderEquity(String symbol, BalanceSheet.PeriodType period)
|
|
{
|
|
TimeSeriesCollection timeSeriesCollection = new TimeSeriesCollection();
|
|
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,total_stockholder_equity from balancesheet where symbol='").Append(symbol).Append("'").Append(" ");
|
|
sb.Append(" and period=").Append(period.Equals(BalanceSheet.PeriodType.Annual) ? 0 : 1).Append(" order by asof desc;");
|
|
strQuery = sb.ToString();
|
|
sqlCommand = new MySqlCommand(strQuery, sqlConnection);
|
|
sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
|
|
sqlDataReader = sqlCommand.ExecuteReader();
|
|
while (sqlDataReader.Read())
|
|
{
|
|
TimeSeriesElement timeSeriesElement = new TimeSeriesElement();
|
|
timeSeriesElement.Symbol = sqlDataReader.GetString(0);
|
|
timeSeriesElement.AsOf = sqlDataReader.GetDateTime(1);
|
|
timeSeriesElement.Type = period.Equals(BalanceSheet.PeriodType.Quarterly) ? TimeSeriesElement.ElementType.QuarterlyInventory : TimeSeriesElement.ElementType.Inventory;
|
|
if (!sqlDataReader.IsDBNull(2)) timeSeriesElement.Value = sqlDataReader.GetDouble(2);
|
|
else continue;
|
|
timeSeriesCollection.Add(timeSeriesElement);
|
|
}
|
|
return timeSeriesCollection;
|
|
}
|
|
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 InsertBalanceSheets(List<BalanceSheet> balanceSheets)
|
|
{
|
|
MySqlConnection sqlConnection = null;
|
|
MySqlTransaction sqlTransaction = null;
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
if(null==balanceSheets)return false;
|
|
DateTime modified = DateTime.Now;
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
|
|
DeleteBalanceSheets(balanceSheets, sqlConnection, sqlTransaction);
|
|
for (int index = 0; index < balanceSheets.Count; index++)
|
|
{
|
|
BalanceSheet balanceSheet = balanceSheets[index];
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("insert into balancesheet (symbol,asof,long_term_debt,other_liabilities,deferred_long_term_liabilities,minority_interest,negative_goodwill,total_stockholder_equity,inventory,property_plant_and_equipment,intangible_assets,accumulated_amortization,goodwill,period,total_assets,total_current_assets,total_liabilities,total_current_liabilities,cash_and_cash_equivalents,modified) ");
|
|
sb.Append("values(");
|
|
sb.Append("'").Append(balanceSheet.Symbol).Append("'").Append(",");
|
|
sb.Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(balanceSheet.AsOf)).Append("'").Append(",");
|
|
if (!Double.IsNaN(balanceSheet.LongTermDebt)) sb.Append(balanceSheet.LongTermDebt).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(balanceSheet.OtherLiabilities)) sb.Append(balanceSheet.OtherLiabilities).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(balanceSheet.DeferredLongTermLiabilities)) sb.Append(balanceSheet.DeferredLongTermLiabilities).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(balanceSheet.MinorityInterest)) sb.Append(balanceSheet.MinorityInterest).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(balanceSheet.NegativeGoodwill)) sb.Append(balanceSheet.NegativeGoodwill).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(balanceSheet.TotalStockHolderEquity)) sb.Append(balanceSheet.TotalStockHolderEquity).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(balanceSheet.Inventory)) sb.Append(balanceSheet.Inventory).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(balanceSheet.PropertyPlantAndEquipment)) sb.Append(balanceSheet.PropertyPlantAndEquipment).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(balanceSheet.IntangibleAssets)) sb.Append(balanceSheet.IntangibleAssets).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(balanceSheet.AccumulatedAmortization)) sb.Append(balanceSheet.AccumulatedAmortization).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(balanceSheet.Goodwill)) sb.Append(balanceSheet.Goodwill).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
sb.Append(BalanceSheet.PeriodType.Annual.Equals(balanceSheet.Period)?0:1).Append(",");
|
|
if (!Double.IsNaN(balanceSheet.TotalAssets)) sb.Append(balanceSheet.TotalAssets).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(balanceSheet.TotalCurrentAssets)) sb.Append(balanceSheet.TotalCurrentAssets).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(balanceSheet.TotalLiabilities)) sb.Append(balanceSheet.TotalLiabilities).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(balanceSheet.TotalCurrentLiabilities)) sb.Append(balanceSheet.TotalCurrentLiabilities).Append(",");
|
|
else sb.Append("null").Append(",");
|
|
if (!Double.IsNaN(balanceSheet.CashAndCashEquivalents)) sb.Append(balanceSheet.CashAndCashEquivalents).Append(",");
|
|
else sb.Append("null").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 DeleteBalanceSheets(List<BalanceSheet> balanceSheets, MySqlConnection sqlConnection, MySqlTransaction sqlTransaction)
|
|
{
|
|
for (int index = 0; index < balanceSheets.Count; index++)
|
|
{
|
|
DeleteBalanceSheet(balanceSheets[index], sqlConnection, sqlTransaction);
|
|
}
|
|
return true;
|
|
}
|
|
private static bool DeleteBalanceSheet(BalanceSheet balanceSheet, MySqlConnection sqlConnection, MySqlTransaction sqlTransaction)
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
String strQuery = null;
|
|
|
|
try
|
|
{
|
|
sb.Append("delete from balancesheet where ");
|
|
sb.Append("symbol='").Append(balanceSheet.Symbol).Append("'");
|
|
sb.Append(" and ");
|
|
sb.Append("asof='").Append(Utility.DateTimeToStringYYYYHMMHDD(balanceSheet.AsOf)).Append("'");
|
|
sb.Append(" and period=").Append(balanceSheet.Period.Equals(BalanceSheet.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
|
|
{
|
|
}
|
|
}
|
|
}
|
|
}
|