public Dictionary<String, List<Tuple<DateTime, String>>> GetContentData(DateTime start, DateTime end)
{
DBManager manager = new DBManager();
MySqlConnection con = manager.OpenConnection(Const.DATABASE_NAME);
String queryTemplate =
"select * from research_up_down_content where " +
"CalDate >= '{0}' and CalDate <= '{1}'";
String query = String.Format(
queryTemplate,
start.ToString("yyyy-MM-dd"),
end.ToString("yyyy-MM-dd"));
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataAdapter data_adapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet("data");
data_adapter.Fill(ds, "data");
DataRowCollection drc = ds.Tables["data"].Rows;
if (drc.Count <= 0)
{
logger.Warn("data count is 0.");
return null;
}
Dictionary<String, List<Tuple<DateTime, String>>> contents =
new Dictionary<String, List<Tuple<DateTime, String>>>();
for (int i = 0; i < drc.Count; ++i)
{
DataRow dr = drc[i];
DateTime dt = Convert.ToDateTime(dr["CalDate"]);
String stockName = Convert.ToString(dr["StockName"]);
String content = Convert.ToString(dr["Content"]);
if (!contents.ContainsKey(stockName))
{
contents.Add(stockName, new List<Tuple<DateTime, String>>());
}
contents[stockName].Add(new Tuple<DateTime, String>(dt, content));
}
ds.Clear();
con.Close();
return contents;
}
void LoadTickersFromDB()
{
DBManager manager = new DBManager();
MySqlConnection con = manager.OpenConnection(Const.DATABASE_NAME);
String query = "select * from product_list";
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataAdapter data_adapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet("product_list");
data_adapter.Fill(ds, "product_list");
DataRowCollection drc = ds.Tables["product_list"].Rows;
if (drc.Count <= 0)
{
logger.Warn("Data count is 0.");
return;
}
for (int i = 0; i < drc.Count; ++i)
{
DataRow dr = drc[i];
String ticker = Convert.ToString(dr["ticker"]);
String description = Convert.ToString(dr["description"]);
if (!_tickers.ContainsKey(ticker))
{
_tickers.Add(ticker, description);
}
if (!_reverseTickers.ContainsKey(description))
{
_reverseTickers.Add(description, ticker);
}
}
ds.Clear();
con.Close();
}
void SetActiveDays()
{
DBManager manager = new DBManager();
MySqlConnection con = manager.OpenConnection(Const.DATABASE_NAME);
String query =
"select * from raw_price_data where ticker = 'KOSPI Index' " +
"order by DateTime";
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataAdapter data_adapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet("raw_price_data");
data_adapter.Fill(ds, "raw_price_data");
DataRowCollection drc = ds.Tables["raw_price_data"].Rows;
if (drc.Count <= 0)
{
logger.Warn("Data count is 0.");
return;
}
_active_days.Clear();
for (int i = 0; i < drc.Count; ++i)
{
DataRow dr = drc[i];
DateTime dt = Convert.ToDateTime(dr["DateTime"]);
_active_days.Add(dt);
}
ds.Clear();
con.Close();
}
public void LoadMarketData(String ticker)
{
if (_data.ContainsKey(ticker))
{
return;
}
DBManager manager = new DBManager();
MySqlConnection con = manager.OpenConnection(Const.DATABASE_NAME);
String query = String.Format(
"select * from raw_price_data where Ticker = '{0}' order by DateTime",
ticker);
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataAdapter data_adapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet("raw_price_data");
data_adapter.Fill(ds, "raw_price_data");
DataRowCollection drc = ds.Tables["raw_price_data"].Rows;
if (drc.Count <= 0)
{
logger.Warn("Data count is 0.");
return;
}
SortedList<DateTime, double> output = new SortedList<DateTime, double>();
for (int i = 0; i < drc.Count; ++i)
{
DataRow dr = drc[i];
DateTime dt = Convert.ToDateTime(dr["DateTime"]);
double last_price = Convert.ToDouble(dr["LastPrice"]);
output.Add(dt, last_price);
}
ds.Clear();
con.Close();
_data.Add(ticker, output);
}
DataFromDB LoadDataFromDB(MacroSpotData last_data, int limit)
{
DBManager manager = new DBManager();
MySqlConnection con = manager.OpenConnection(Const.DATABASE_NAME);
SortedList<DateTime, double> kospi_data =
LoadDataFromDB_Raw(con, "KOSPI Index", limit);
SortedList<DateTime, double> ktb_data =
LoadDataFromDB_Raw(con, "KWNDF3 CMPN CURNCY", limit);
SortedList<DateTime, double> usdkrw_data =
LoadDataFromDB_Raw(con, "USDKRW Curncy", limit);
SortedList<DateTime, double> kospi_future_data =
LoadDataFromDB_Raw(con, "KM1 R:00_0_R Index", limit);
SortedList<DateTime, double> ktb_future_data =
LoadDataFromDB_Raw(con, "KE1 R:00_0_R Comdty", limit);
SortedList<DateTime, double> usdkrw_future_data =
LoadDataFromDB_Raw(con, "KU1 R:00_0_R Curncy", limit);
SortedList<DateTime, double> active_days =
new SortedList<DateTime, double>();
active_days = GetActiveDays(kospi_data, ktb_data);
active_days = GetActiveDays(active_days, usdkrw_data);
active_days = GetActiveDays(active_days, kospi_future_data);
active_days = GetActiveDays(active_days, ktb_future_data);
active_days = GetActiveDays(active_days, usdkrw_future_data);
DataFromDB output = new DataFromDB();
output.ActiveDays = active_days;
output.Kospi = kospi_data;
output.Ktb = ktb_data;
output.Usdkrw = usdkrw_data;
output.KospiFuture = kospi_future_data;
output.KtbFuture = ktb_future_data;
output.UsdkrwFuture = usdkrw_future_data;
if (last_data != null)
{
DateTime key = last_data.CurDateTime;
output.ActiveDays.Add(key, 0);
output.Kospi.Add(key, last_data.KospiPrice);
output.KospiFuture.Add(key, last_data.KospiFuturePrice);
output.Ktb.Add(key, last_data.BondRate);
output.KtbFuture.Add(key, last_data.BondFuturePrice);
output.Usdkrw.Add(key, last_data.UsdkrwPrice);
output.UsdkrwFuture.Add(key, last_data.UsdkrwFuturePrice);
}
con.Close();
return output;
}
void LoadData()
{
DBManager manager = new DBManager();
MySqlConnection con = manager.OpenConnection(Const.DATABASE_NAME);
foreach (String ticker in _targets.Keys)
{
SortedList<DateTime, double> data =
LoadDataFromDB_Raw(con, ticker, int.MaxValue);
this._data.Add(ticker, data);
DailyDataManager.Ins().SetData(ticker, data);
}
con.Close();
}
List<Tuple<String, double>> GetSectorDataFromDB(String sector_ticker)
{
DBManager manager = new DBManager();
MySqlConnection con = manager.OpenConnection(Const.DATABASE_NAME);
try
{
String query = String.Format("select * from " +
"(select * from sector_member_map where sector_ticker = '{0}') a " +
"left join sector_weight b on a.member_ticker = b.member_ticker",
sector_ticker);
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataAdapter data_adapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet("sector_member_weight");
data_adapter.Fill(ds, "sector_member_weight");
DataRowCollection drc = ds.Tables["sector_member_weight"].Rows;
if (drc.Count <= 0)
{
logger.Warn("{0} data count is 0.", sector_ticker);
return null;
}
List<Tuple<String, double>> ret = new List<Tuple<string, double>>();
for (int i = 0; i < drc.Count; ++i)
{
DataRow dr = drc[i];
String member_ticker = Convert.ToString(dr["member_ticker"]);
double weight = Convert.ToDouble(dr["weight"]);
ret.Add(new Tuple<String, double>(member_ticker, weight));
}
ds.Clear();
return ret;
}
catch (Exception ex)
{
logger.Error(ex.ToString());
}
finally
{
con.Close();
}
return null;
}
void Insert_Count(DateTime calDate, Dictionary<String, int> countDic)
{
DBManager manager = new DBManager();
MySqlConnection con = manager.OpenConnection(Const.DATABASE_NAME);
foreach (String name in countDic.Keys)
{
String query = String.Format(
"insert into research_up_down_count (CalDate, StockName, Count) " +
"values ('{0}', '{1}', {2})",
calDate.ToString("yyyy-MM-dd"),
name,
countDic[name]);
MySqlCommand cmd = new MySqlCommand(query, con);
int ret = cmd.ExecuteNonQuery();
if (ret != 1)
{
throw new Exception();
}
}
con.Close();
}
void Insert_Content(DateTime calDate,
Dictionary<String, List<String>> logs)
{
DBManager manager = new DBManager();
MySqlConnection con = manager.OpenConnection(Const.DATABASE_NAME);
foreach (String name in logs.Keys)
{
List<String> contents = logs[name];
foreach (String content in contents)
{
String queryTemplate =
"insert into research_up_down_content " +
"(CalDate, StockName, Content) " +
"values ('{0}', '{1}', '{2}')";
String query = String.Format(
queryTemplate,
calDate.ToString("yyyy-MM-dd"),
name,
content);
MySqlCommand cmd = new MySqlCommand(query, con);
int ret = cmd.ExecuteNonQuery();
if (ret != 1)
{
throw new Exception();
}
}
}
con.Close();
}
void DeletePrevData(DateTime calDate)
{
DBManager manager = new DBManager();
MySqlConnection con = manager.OpenConnection(Const.DATABASE_NAME);
DeletePrevContentData(con, calDate);
DeletePrevCountData(con, calDate);
con.Close();
}