public static DataRowCollection SelectFromDB(String query, String database)
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = kIP;
con = dbManager.CreateConnection(ip, database);
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet("data");
dataAdapter.Fill(ds, "data");
DataRowCollection drc = ds.Tables["data"].Rows;
return drc;
}
catch (System.Exception ex)
{
logger.Warn(ex.ToString());
}
finally
{
dbManager.Close(con);
}
return null;
}
public static int Execute(String query, String database)
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = kIP;
con = dbManager.CreateConnection(ip, database);
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
int ret = cmd.ExecuteNonQuery();
return ret;
}
catch (System.Exception ex)
{
logger.Warn(ex.ToString());
}
finally
{
dbManager.Close(con);
}
return -1;
}
public static Dictionary<String, String> LoadShortLongCodeMapFromDB()
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = ConfigManager.Ins().Config.GetValue(ConfigKeyConst.DB_SERVER_IP);
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_MADVIPER);
con.Open();
String query = "select * from stock_long_code";
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet("query_result");
dataAdapter.Fill(ds, "query_result");
DataRowCollection drc = ds.Tables["query_result"].Rows;
if (drc.Count <= 0)
{
return null;
}
Dictionary<String, String> ret = new Dictionary<string, string>();
for (int i = 0; i < drc.Count; ++i)
{
DataRow dr = drc[i];
String shortCode = dr["short_code"].ToString();
String longCode = dr["long_code"].ToString();
if (shortCode.Length == 0)
{
logger.Warn("Strange data {0} - {1}", shortCode, longCode);
}
ret.Add(shortCode, longCode);
}
ds.Clear();
return ret;
}
catch (System.Exception ex)
{
logger.Error(ex.ToString());
Util.KillWithNotice(ex.ToString());
}
finally
{
dbManager.Close(con);
}
return null;
}
public static Dictionary<String, String> LoadBloombergStockDataFromDB()
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = ConfigManager.Ins().Config.GetValue(ConfigKeyConst.DB_SERVER_IP);
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_CORREL);
con.Open();
return LoadFromDB_Raw(con);
}
catch (System.Exception ex)
{
logger.Error(ex.ToString());
Util.KillWithNotice(ex.ToString());
}
finally
{
dbManager.Close(con);
}
return null;
}
Dictionary<String, BondSpotInfo> GetBondDic()
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = ConfigManager.Ins().Config.GetValue(ConfigKeyConst.BOND_DB_SERVER_IP);
con = dbManager.OpenConnection(ip, "baadf00d", CommonConst.DATABASE_BOND_MARKETDATA);
return GetBondDic_Raw(con);
}
catch (System.Exception ex)
{
logger.Error(ex.ToString());
Util.KillWithNotice(ex.ToString());
}
finally
{
con.Close();
dbManager.Close(con);
}
return null;
}
KospiFutureInfo GetKospiFutureInfoFromDB(short futureSeriesOffset)
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = ConfigManager.Ins().Config.GetValue(ConfigKeyConst.DB_SERVER_IP);
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_MADVIPER);
con.Open();
String query = "select * from kospi_future_info order by maturity";
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet("query_result");
dataAdapter.Fill(ds, "query_result");
DataRowCollection drc = ds.Tables["query_result"].Rows;
if (drc.Count != 2)
{
logger.Error("DB에 kospi future 정보가 두개가 아닙니다.");
Util.KillWithNotice("DB에 kospi future 정보가 두개가 아닙니다.");
}
Trace.Assert(futureSeriesOffset == 0 || futureSeriesOffset == 1);
DataRow dr = drc[futureSeriesOffset];
String code = dr["code"].ToString();
double upLimit = Convert.ToDouble(dr["up_limit"]);
double downLimit = Convert.ToDouble(dr["down_limit"]);
long maturity = Convert.ToInt64(dr["maturity"]);
KospiFutureInfo kfi = new KospiFutureInfo();
kfi.Code = code;
kfi.DownLimit = downLimit;
kfi.UpLimit = upLimit;
kfi.Maturity = DateTime.ParseExact("" + maturity, "yyyyMMdd", null);
ds.Clear();
return kfi;
}
catch (System.Exception ex)
{
logger.Warn(ex.ToString());
}
finally
{
dbManager.Close(con);
}
return null;
}
public static DateTime GetOptionMaturityFromDB(int optionSeries)
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = ConfigManager.Ins().Config.GetValue(ConfigKeyConst.DB_SERVER_IP);
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_MADVIPER);
con.Open();
String query = String.Format(
"select * from maturity_info where series_number = {0}",
optionSeries);
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet("data");
dataAdapter.Fill(ds, "data");
DataRowCollection drc = ds.Tables["data"].Rows;
if (drc.Count <= 0)
{
return DateTime.MaxValue;
}
DataRow dr = drc[0];
DateTime maturity = Convert.ToDateTime(dr["maturity"]);
ds.Clear();
return maturity;
}
catch (System.Exception ex)
{
logger.Error(ex.ToString());
Util.KillWithNotice(ex.ToString());
}
finally
{
dbManager.Close(con);
}
return DateTime.MaxValue;
}
static List<ElwInfo> LoadValidatedElwInfos()
{
List<ElwInfo> ret = new List<ElwInfo>();
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = ConfigManager.Ins().Config.GetValue(ConfigKeyConst.DB_SERVER_IP);
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_MADVIPER);
con.Open();
String query = "select * from validated_elw_list";
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet("validated_elw_list");
dataAdapter.Fill(ds, "validated_elw_list");
DataRowCollection drc = ds.Tables["validated_elw_list"].Rows;
if (drc.Count <= 0)
{
return ret;
}
for (int i = 0; i < drc.Count; ++i)
{
DataRow dr = drc[i];
String code = dr["code"].ToString();
String name = dr["name"].ToString();
long maturity = Convert.ToInt64(dr["maturity"]);
double strike = Convert.ToDouble(dr["strike"]);
int changeRate = Convert.ToInt32(dr["change_rate"]);
String callPut = dr["call_put"].ToString();
long issuedCount = Convert.ToInt64(dr["issued_count"]);
ElwInfo ei = new ElwInfo();
ei.Code = code;
ei.Name = name;
ei.Maturity = maturity;
ei.Strike = strike;
ei.ChangeRate = changeRate;
ei.CallPut = callPut.CompareTo("Call") == 0 ? CallPutFlag.Call : CallPutFlag.Put;
ei.IssuedCount = issuedCount;
ei.IsValidated = true;
ret.Add(ei);
}
ds.Clear();
}
catch (System.Exception ex)
{
logger.Error(ex.ToString());
Util.KillWithNotice(ex.ToString());
}
finally
{
dbManager.Close(con);
}
return ret;
}
UsdFutureInfo GetUsdFutureInfoFromDB(short futureSeriesOffset)
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = ConfigManager.Ins().Config.GetValue(ConfigKeyConst.DB_SERVER_IP);
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_MADVIPER);
con.Open();
String query = "select * from usd_future_info order by maturity";
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet("query_result");
dataAdapter.Fill(ds, "query_result");
DataRowCollection drc = ds.Tables["query_result"].Rows;
if (drc.Count != 2)
{
logger.Error("DB에 Usd future 정보가 두개가 아닙니다.");
Util.KillWithNotice("DB에 Usd future 정보가 두개가 아닙니다.");
}
Trace.Assert(futureSeriesOffset == 0 || futureSeriesOffset == 1);
DataRow dr = drc[futureSeriesOffset];
String code = dr["code"].ToString();
DateTime maturity = Convert.ToDateTime(dr["maturity"]);
UsdFutureInfo ufi = new UsdFutureInfo();
ufi.Code = code;
ufi.Maturity = maturity;
ds.Clear();
return ufi;
}
catch (System.Exception ex)
{
logger.Warn(ex.ToString());
}
finally
{
dbManager.Close(con);
}
return null;
}
void InsertFutureSpreadInfo(KospiFutureSpreadInfo data)
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = IP;
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_MADVIPER);
con.Open();
String query_template = String.Format("insert into kospi_future_spread_info ({0}) values ({1})",
"code, future_1, future_2",
"'{0}', '{1}', '{2}'");
String query = String.Format(
query_template,
data.Code,
data.FutureCode1,
data.FutureCode2);
logger.Debug(query);
MySqlCommand cmd = new MySqlCommand(query, con);
int ret = cmd.ExecuteNonQuery();
if (ret <= 0)
{
logger.Warn(String.Format("Query: ({0})", query));
}
}
catch (System.Exception ex)
{
logger.Warn(ex.ToString());
}
finally
{
dbManager.Close(con);
}
}
void InsertFutureInfo(KospiFutureInfo data)
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = IP;
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_MADVIPER);
con.Open();
String query_template = String.Format("insert into kospi_future_info ({0}) values ({1})",
"code, up_limit, down_limit, yes_price, maturity",
"'{0}', {1}, {2}, {3}, {4}");
String query = String.Format(
query_template,
data.Code,
data.UpLimit,
data.DownLimit,
data.YesterdayLastPrice,
data.Maturity.ToString("yyyyMMdd"));
logger.Debug(query);
MySqlCommand cmd = new MySqlCommand(query, con);
int ret = cmd.ExecuteNonQuery();
if (ret <= 0)
{
logger.Warn(String.Format("Query: ({0})", query));
}
logger.Info("Delete kospi future spread info complete...");
}
catch (System.Exception ex)
{
logger.Warn(ex.ToString());
}
finally
{
dbManager.Close(con);
}
}
void InsertElwInfoIntoDB(List<CP_LoadingDatum_Elw> infos)
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = IP;
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_MADVIPER);
con.Open();
foreach (CP_LoadingDatum_Elw info in infos)
{
/*
* 0 underlying_code
* 1 underlying_name
* 2 code
* 3 name
* 4 maturity
* 5 strike
* 6 change_rate
* 7 call_put
* 8 remain_days
* 9 gearing
* 10 issued_count
* 11 long_code
* */
String query_template = String.Format("insert into elw_info ({0}) values ({1})",
"underlying_code, underlying_name, code, name, maturity, strike, change_rate, call_put, remain_days, gearing, issued_count, long_code",
"'{0}', '{1}', '{2}', '{3}', {4}, {5}, {6}, '{7}', {8}, {9}, {10}, '{11}'");
String query = String.Format(
query_template,
info.UnderlyingCode,
info.UnderlyingName,
info.Code,
info.Name,
info.Maturity,
info.Strike,
info.ChangeRate,
info._CallPut,
info.RemainDays,
info.Gearing,
info.IssuedCount,
info.LongCode);
logger.Debug(query);
MySqlCommand cmd = new MySqlCommand(query, con);
int ret = cmd.ExecuteNonQuery();
if (ret <= 0)
{
logger.Warn(String.Format("Query: ({0})", query));
}
}
}
catch (System.Exception ex)
{
logger.Warn(ex.ToString());
}
finally
{
dbManager.Close(con);
}
}
void DeleteOptionInfoDB()
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = IP;
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_MADVIPER);
con.Open();
String query = "delete from option_info";
MySqlCommand cmd = new MySqlCommand(query, con);
int ret = cmd.ExecuteNonQuery();
if (ret < 0)
{
logger.Warn(String.Format("Query: ({0})", query));
}
logger.Info("Delete option info from DB is complete...");
}
catch (System.Exception ex)
{
logger.Warn(ex.ToString());
}
finally
{
dbManager.Close(con);
}
}
void DeleteLongCode()
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = IP;
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_MADVIPER);
con.Open();
String query = "delete from stock_long_code";
MySqlCommand cmd = new MySqlCommand(query, con);
int ret = cmd.ExecuteNonQuery();
if (ret < 0)
{
logger.Warn(String.Format("Query: ({0})", query));
}
}
catch (System.Exception ex)
{
logger.Warn(ex.ToString());
}
finally
{
dbManager.Close(con);
}
}
public void Initialize()
{
// database에 접속해서 실시간 Account를 가져온다.
// 시간이 많이 걸리지는 않으므로 따로 Thread로 빼지 않고 바로 진행한다.
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = ConfigManager.Ins().Config.GetValue(ConfigKeyConst.DB_SERVER_IP);
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_MADVIPER);
con.Open();
String loginID = ConfigManager.Ins().Config.GetValue(ConfigKeyConst.LOGIN_ID);
int useAccount = Convert.ToInt32(Util.RemoveComma(ConfigManager.Ins().Config.GetValue(ConfigKeyConst.USE_ACCOUNT)));
if (useAccount == 0)
{
}
else
{
LoadAccounts(con, loginID);
}
}
catch (System.Exception ex)
{
logger.Error(ex.ToString());
Util.KillWithNotice(ex.ToString());
}
finally
{
dbManager.Close(con);
}
}
void UpdateStrategy()
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = ConfigManager.Ins().Config.GetValue(ConfigKeyConst.DB_SERVER_IP);
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_MADVIPER);
con.Open();
String query = "select * from strategy";
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet("strategy");
dataAdapter.Fill(ds, "strategy");
DataRowCollection drc = ds.Tables["strategy"].Rows;
listView1.Items.Clear();
if (drc.Count <= 0)
{
return;
}
for (int i = 0; i < drc.Count; ++i)
{
DataRow dr = drc[i];
String strategyName = dr["StrategyName"].ToString();
String input = dr["Input"].ToString();
String template = dr["StrategyTemplate"].ToString();
System.Windows.Forms.ListViewItem lvItem =
new System.Windows.Forms.ListViewItem(new string[] {
strategyName, input, template
}, -1);
lvItem.Name = strategyName;
listView1.Items.Add(lvItem);
}
ds.Clear();
}
catch (System.Exception ex)
{
logger.Warn(ex.ToString());
}
finally
{
dbManager.Close(con);
}
}
void InsertOptionDataIntoDB(LoadingData_Option data)
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = IP;
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_MADVIPER);
con.Open();
foreach (LoadingDatum_Option info in data.Data)
{
/*
* 0 code
* 1 name
* 2 strike
* 3 maturity
* 4 up_limit
* 5 down_limit
* */
String query_template = String.Format("insert into option_info ({0}) values ({1})",
"code, name, strike, maturity, up_limit, down_limit",
"'{0}', '{1}', {2}, {3}, {4}, {5}");
String query = String.Format(
query_template,
info.Code,
info.Name,
info.Strike,
info.Maturity,
info.UpLimit,
info.DownLimit);
logger.Debug(query);
MySqlCommand cmd = new MySqlCommand(query, con);
int ret = cmd.ExecuteNonQuery();
if (ret <= 0)
{
logger.Warn(String.Format("Query: ({0})", query));
}
}
logger.Info("InsertOptionDataIntoDB is complete...");
}
catch (System.Exception ex)
{
logger.Warn(ex.ToString());
}
finally
{
dbManager.Close(con);
}
}
LoadingData_Elw GetLoadingDataFromDB(DateTime maturity)
{
LoadingData_Elw data = new LoadingData_Elw();
List<CP_LoadingDatum_Elw> elwInfos = new List<CP_LoadingDatum_Elw>();
String kospi200 = "U180";
String query_template = "select * from elw_info where underlying_code = '{0}' and maturity = {1}";
String query = String.Format(query_template, kospi200, maturity.ToString("yyyyMMdd"));
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = ConfigManager.Ins().Config.GetValue(ConfigKeyConst.DB_SERVER_IP);
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_MADVIPER);
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet("query_result");
dataAdapter.Fill(ds, "query_result");
DataRowCollection drc = ds.Tables["query_result"].Rows;
if (drc.Count <= 0)
{
return data;
}
for (int i = 0; i < drc.Count; ++i)
{
CP_LoadingDatum_Elw datum = new CP_LoadingDatum_Elw();
DataRow dr = drc[i];
datum.UnderlyingCode = Convert.ToString(dr["underlying_code"]);
datum.UnderlyingName = Convert.ToString(dr["underlying_name"]);
datum.Code = Convert.ToString(dr["code"]);
datum.Name = Convert.ToString(dr["name"]);
datum.Maturity = Convert.ToInt64(dr["maturity"]);
datum.Strike = Convert.ToDouble(dr["strike"]);
datum.ChangeRate = Convert.ToInt32(dr["change_rate"]);
datum._CallPut = Convert.ToString(dr["call_put"]);
datum.RemainDays = Convert.ToInt64(dr["remain_days"]);
datum.Gearing = Convert.ToDouble(dr["gearing"]);
datum.IssuedCount = Convert.ToInt64(dr["issued_count"]);
datum.LongCode = Convert.ToString(dr["long_code"]);
elwInfos.Add(datum);
}
ds.Clear();
data.Data = elwInfos;
}
catch (System.Exception ex)
{
logger.Warn(ex.ToString());
}
finally
{
dbManager.Close(con);
}
return data;
}
void SaveLongCode_Raw(String shortCode, String longCode, String name)
{
DBManager dbManager = new DBManager();
MySqlConnection con = null;
try
{
String ip = IP;
con = dbManager.CreateConnection(ip, CommonConst.DATABASE_MADVIPER);
con.Open();
String query_template = String.Format("insert into stock_long_code ({0}) values ({1})",
"short_code, long_code, name",
"'{0}', '{1}', '{2}'");
String query = String.Format(
query_template,
shortCode,
longCode,
name);
logger.Debug(query);
MySqlCommand cmd = new MySqlCommand(query, con);
int ret = cmd.ExecuteNonQuery();
if (ret <= 0)
{
logger.Warn(String.Format("Query: ({0})", query));
}
}
catch (System.Exception ex)
{
logger.Warn(ex.ToString());
}
finally
{
dbManager.Close(con);
}
}