/// <summary>
/// 获取当前K线前一个交易日指数的的资金沉淀去用于计算资金流入流出
/// </summary>
/// <param name="kline"></param>
/// <returns></returns>
private decimal GetLastTradingDayIndexInsSendimentaryMoney(USeKLine kline)
{
decimal lastTradingDaySendimentaryMoney = 0m;
DateTime preTradingDay = m_tradeCalendarManager.GetPreTradingDate(kline.DateTime);
string strSel = string.Format(@"select * from {0}.day_kline where contract='{1}' AND date_time='{2:yyyy-MM-dd 00:00:00}';",
m_alphaDBName, kline.InstrumentCode, preTradingDay);
DataTable table = MySQLDriver.GetTableFromDB(m_dbConnStr, strSel);
foreach (DataRow row in table.Rows)
{
try
{
USeKLine tempKline = new USeKLine();
tempKline.InstrumentCode = row["contract"].ToString();
tempKline.Market = (USeMarket)Enum.Parse(typeof(USeMarket), row["exchange"].ToString());
tempKline.Close = Convert.ToDecimal(row["price_close"]);
tempKline.DateTime = Convert.ToDateTime(row["date_time"]);
tempKline.OpenInterest = Convert.ToDecimal(row["openinterest"]);
tempKline.SettlementPrice = (row["settlement_price"] != DBNull.Value) ? Convert.ToDecimal(row["settlement_price"]) : 0m;
tempKline.SendimentaryMoney = (row["sendimentary_money"] != DBNull.Value) ? Convert.ToDecimal(row["sendimentary_money"]) : 0m;
return(lastTradingDaySendimentaryMoney = tempKline.SendimentaryMoney);
}
catch (Exception ex)
{
throw new Exception("GetLastTradingDayIndexInsSendimentaryMoney:" + ex.Message);
}
}
return(lastTradingDaySendimentaryMoney);
}
/// <summary>
/// 获取合约指定交易日日K线。
/// </summary>
public USeKLine GetMin1KLine(USeInstrument instrument, DateTime time)
{
string strSel = string.Format(@"select * from {0}.{1}
where contract = '{1}' and date_time = '{3:yyyy-MM-dd HH:mm:00}';",
m_alphaDBName, GetMin1KLineTableName(instrument.Market),
instrument.InstrumentCode, time);
DataTable table = MySQLDriver.GetTableFromDB(m_dbConnStr, strSel);
if (table != null && table.Rows.Count > 0)
{
DataRow row = table.Rows[0];
USeKLine kline = new USeKLine()
{
InstrumentCode = row["contract"].ToString(),
Market = USeTraderProtocol.ToUseMarket(row["exchange"].ToString()),
Cycle = USeCycleType.Day,
DateTime = Convert.ToDateTime(row["date_time"]),
Open = row["price_open"].ToDecimal(),
High = row["price_high"].ToDecimal(),
Low = row["price_low"].ToDecimal(),
Close = row["price_close"].ToDecimal(),
Volumn = row["volumn"].ToInt(),
Turnover = row["turnover"].ToDecimal(),
OpenInterest = row["openinterest"].ToDecimal()
};
return(kline);
}
return(null);
}
/// <summary>
/// 获取当天交易日的K线
/// </summary>
public List <USeKLine> GetTodayDayKlineList(DateTime dateTime)
{
string strSel = string.Format(@"select * from {0}.day_kline where date_time>='{1:yyyy-MM-dd 00:00:00}';",
m_alphaDBName, dateTime);
DataTable table = MySQLDriver.GetTableFromDB(m_dbConnStr, strSel);
List <USeKLine> klineList = new List <USeKLine>();
foreach (DataRow row in table.Rows)
{
try
{
USeKLine kline = new USeKLine();
kline.InstrumentCode = row["contract"].ToString();
kline.Market = (USeMarket)Enum.Parse(typeof(USeMarket), row["exchange"].ToString());
kline.Close = Convert.ToDecimal(row["price_close"]);
kline.DateTime = Convert.ToDateTime(row["date_time"]);
kline.OpenInterest = Convert.ToDecimal(row["openinterest"]);
kline.SettlementPrice = (row["settlement_price"] != DBNull.Value) ? Convert.ToDecimal(row["settlement_price"]) : 0m;
klineList.Add(kline);
}
catch (Exception ex)
{
throw new Exception("初始化当天交易日的K线:" + ex.Message);
}
}
return(klineList);
}
/// <summary>
/// 初始化方法。
/// </summary>
public void Initialize()
{
string strSel = string.Format(@"select * from {0}.varieties
where exchange in ({1});",
m_alphaDBName, USeTraderProtocol.GetInternalFutureMarketSqlString());
DataTable table = MySQLDriver.GetTableFromDB(m_dbConnStr, strSel);
List <USeProduct> productList = new List <USeProduct>();
foreach (DataRow row in table.Rows)
{
string exchange = row["exchange"].ToString();
USeProduct product = new USeProduct()
{
ProductCode = row["code"].ToString(),
Market = (USeMarket)Enum.Parse(typeof(USeMarket), exchange),
ShortName = row["short_name"].ToString(),
LongName = row["long_name"].ToString(),
VolumeMultiple = Convert.ToDecimal(row["volume_multiple"]),
PriceTick = Convert.ToDecimal(row["price_tick"])
};
productList.Add(product);
}
m_varietiesDic.Clear();
foreach (USeProduct product in productList)
{
m_varietiesDic.Add(product.ProductCode, product);
}
}
/// <summary>
/// 加载交易日历。
/// </summary>
private void LoadTradeCalendar()
{
try
{
string strSel = string.Format(@"select * from {0}.trade_calendar where today >= '{1}'
order by today limit 0,15 ;", m_alphaDBName, DateTime.Today.ToString("yyyy-MM-dd"));
DataTable table = MySQLDriver.GetTableFromDB(m_dbConnStr, strSel);
List <TradeCalendar> calendarList = new List <TradeCalendar>();
foreach (DataRow row in table.Rows)
{
TradeCalendar calendar = new TradeCalendar()
{
Today = Convert.ToDateTime(row["today"]),
IsTradeDay = Convert.ToInt32(row["is_trading_day"]) == 1,
PreTradeDay = Convert.ToDateTime(row["pre_trade_day"]),
NextTradeDay = Convert.ToDateTime(row["next_trade_day"])
};
calendarList.Add(calendar);
}
m_tradeCalendarList = calendarList;
}
catch (Exception ex)
{
throw new Exception("加载交易日历失败," + ex.Message);
}
}
private USeKLine GetSeriesTradingKline(DateTime dateTime, string seriesInstrument)
{
string strSel = string.Format(@"select * from {0}.day_kline where contract='{1}' AND date_time='{2:yyyy-MM-dd 00:00:00}';",
m_alphaDBName, seriesInstrument, dateTime);
DataTable table = MySQLDriver.GetTableFromDB(m_dbConnStr, strSel);
foreach (DataRow row in table.Rows)
{
try
{
USeKLine tempKline = new USeKLine();
tempKline.InstrumentCode = row["contract"].ToString();
tempKline.Market = (USeMarket)Enum.Parse(typeof(USeMarket), row["exchange"].ToString());
tempKline.Close = Convert.ToDecimal(row["price_close"]);
tempKline.DateTime = Convert.ToDateTime(row["date_time"]);
tempKline.OpenInterest = Convert.ToDecimal(row["openinterest"]);
tempKline.SettlementPrice = (row["settlement_price"] != DBNull.Value) ? Convert.ToDecimal(row["settlement_price"]) : 0m;
tempKline.SendimentaryMoney = (row["sendimentary_money"] != DBNull.Value) ? Convert.ToDecimal(row["sendimentary_money"]) : 0m;
tempKline.FlowFund = (row["flow_fund"] != DBNull.Value) ? Convert.ToDecimal(row["flow_fund"]) : 0m;
return(tempKline);
}
catch (Exception ex)
{
throw new Exception("GetTradingDaySeriesKline:" + ex.Message);
}
}
return(null);
}
/// <summary>
/// 初始化方法
/// </summary>
public void Initialize()
{
string strSel = string.Format(@"select * from {0}.contracts;",
m_alphaDBName, USeTraderProtocol.GetInternalFutureMarketSqlString());
DataTable table = MySQLDriver.GetTableFromDB(m_dbConnStr, strSel);
List <USeInstrumentDetail> instrumentList = new List <USeInstrumentDetail>();
foreach (DataRow row in table.Rows)
{
try
{
if (row["exchange_long_margin_ratio"] == DBNull.Value ||
row["exchange_short_margin_ratio"] == DBNull.Value)
{
continue;
}
USeMarket exchange = (USeMarket)Enum.Parse(typeof(USeMarket), row["exchange"].ToString());
USeInstrumentDetail instrument = new USeInstrumentDetail();
instrument.Instrument = new USeInstrument(row["contract"].ToString(),
row["contract_name"].ToString(),
exchange);
instrument.OpenDate = Convert.ToDateTime(row["open_date"]);
instrument.ExpireDate = Convert.ToDateTime(row["expire_date"]);
instrument.StartDelivDate = Convert.ToDateTime(row["start_deliv_date"]);
instrument.EndDelivDate = Convert.ToDateTime(row["end_deliv_date"]);
instrument.VolumeMultiple = Convert.ToInt32(row["volume_multiple"]);
instrument.IsTrading = true;
instrument.Varieties = row["varieties"].ToString();
instrument.PriceTick = Convert.ToDecimal(row["price_tick"]);
instrument.ExchangeLongMarginRatio = (row["exchange_long_margin_ratio"] != DBNull.Value) ? Convert.ToDecimal(row["exchange_long_margin_ratio"]) : 0m;
instrument.ExchangeShortMarginRatio = (row["exchange_short_margin_ratio"] != DBNull.Value) ? Convert.ToDecimal(row["exchange_short_margin_ratio"]) : 0m;
instrument.ProductClass = (USeProductClass)Enum.Parse(typeof(USeProductClass), row["product_class"].ToString());
instrument.UnderlyingInstrument = (row["underlying_instrument"] != DBNull.Value)? row["underlying_instrument"].ToString():"";
instrument.MaxMarketOrderVolume = (row["max_market_order_volume"] != DBNull.Value) ? Convert.ToInt16(row["max_market_order_volume"]) : 0;
instrument.MinMarketOrderVolume = (row["min_market_order_volume"] != DBNull.Value) ? Convert.ToInt16(row["min_market_order_volume"]) : 0;
instrument.MaxLimitOrderVolume = (row["max_limit_order_volume"] != DBNull.Value) ? Convert.ToInt16(row["max_limit_order_volume"]) : 0;
instrument.MinLimitOrderVolume = (row["min_limit_order_volume"] != DBNull.Value) ? Convert.ToInt16(row["min_limit_order_volume"]) : 0;
instrumentList.Add(instrument);
}
catch (Exception ex)
{
throw new Exception("初始化合约信息异常:" + ex.Message);
}
}
m_instrumentList = instrumentList;
}
/// <summary>
/// 初始化。
/// </summary>
public void Initialize(DateTime?beginDate = null, DateTime?endDate = null)
{
m_tradingDateDic.Clear();
StringBuilder sbCmdText = new StringBuilder();
sbCmdText.Append(string.Format("select * from {0}.trade_calendar", m_alphaDBName));
if (beginDate.HasValue || endDate.HasValue)
{
sbCmdText.Append(" where");
bool flag = false;
if (beginDate.HasValue)
{
if (flag)
{
sbCmdText.Append(" and");
}
sbCmdText.Append(string.Format(" (today >= '{0:yyyy-MM-dd}')", beginDate.Value));
flag = true;
}
if (endDate.HasValue)
{
if (flag)
{
sbCmdText.Append(" and");
}
sbCmdText.Append(string.Format(" (today <= '{0:yyyy-MM-dd}')", endDate.Value));
flag = true;
}
}
sbCmdText.Append(" order by today;");
DataTable table = MySQLDriver.GetTableFromDB(m_dbConnStr, sbCmdText.ToString());
if (table != null && table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
TradeCalendar calendar = new TradeCalendar()
{
IsTradeDay = Convert.ToBoolean(row["is_trading_day"]),
NextTradeDay = Convert.ToDateTime(row["next_trade_day"]),
PreTradeDay = Convert.ToDateTime(row["pre_trade_day"]),
Today = Convert.ToDateTime(row["today"])
};
m_tradingDateDic.Add(calendar.Today, calendar);
}
}
}
public override List <string> GetTablesOfDB(string dbName)
{
KnifeConfig.MySqlConnectionString = pGenerateConnectionString(this.pHost, this.pSchema, this.pUser, this.pPwd);
mDriver = new MySQLDriver();
DataTable dt = mDriver.ExecDQLForDataTable(
string.Format("select TABLE_NAME as TableName from `TABLES` where TABLE_SCHEMA='{0}'", dbName));
if (dt.Rows.Count <= 0)
{
return(null);
}
this.pDbName = dbName;
return(pGetTableNames(dt));
}
static void Main(string[] args)
{
MySQLDriver driver = new MySQLDriver($"Server=YOUR_SERVER;Port=YOUR_PORT;Database=YOUR_DB;User=YOUR_USER;Pwd=USER_PASSWORD;");
var result = driver.Save(new UserRole()
{
Name = "Administrator"
});
//var result = await driver.SaveAsync<UserRole>(new UserRole());
var cb = new CriteriaBuilder().Equal <UserRole, string>(ur => ur.Name, "Administrator");
var collection = driver.Search <UserRole>(cb);
foreach (var item in collection)
{
Console.WriteLine(item);
}
}
static void Main(string[] args)
{
//select* from alpha.day_kline where contract like '%l9'
string strl = "select contract, exchange from alpha.day_kline where contract like '%999' and exchange = 'CZCE' group by contract,exchange";
DataTable table = MySQLDriver.GetTableFromDB("", strl);
foreach (DataRow row in table.Rows)
{
string contract = row["contract"].ToString();
string update = "update set ";
//郑州
string newContract = contract.Substring(0, contract.Length - 2) + "9999";
//郑州
//string newContract = contract.Substring(0, contract.Length - 2).ToLower() + "9999";
}
}
/// <summary>
/// 加载交易区间。
/// </summary>
private void LoadTradeRange()
{
try
{
string strSel = string.Format(@"select * from {0}.future_trade_range;", m_alphaDBName);
DataTable table = MySQLDriver.GetTableFromDB(m_dbConnStr, strSel);
Dictionary <string, List <TradeRangeItem> > tradeRangeDic = new Dictionary <string, List <TradeRangeItem> >();
foreach (DataRow row in table.Rows)
{
string varieties = row["varieties"].ToString();
List <TradeRangeItem> rangeList = null;
if (tradeRangeDic.TryGetValue(varieties, out rangeList) == false)
{
rangeList = new List <TradeRangeItem>(4);
tradeRangeDic.Add(varieties, rangeList);
}
TimeSpan beginTime;
TimeSpan endTime;
if (TimeSpan.TryParse(row["begin_time"].ToString(), out beginTime) == false)
{
throw new ArgumentException(string.Format("Invalid begin_time {0}", row["begin_time"].ToString()));
}
if (TimeSpan.TryParse(row["end_time"].ToString(), out endTime) == false)
{
throw new ArgumentException(string.Format("Invalid end_time {0}", row["end_time"].ToString()));
}
bool isNight = Convert.ToInt32(row["is_night"]) == 1;
TradeRangeItem rangeItem = new TradeRangeItem(beginTime, endTime, isNight);
rangeList.Add(rangeItem);
}
m_tradeRangeDic = tradeRangeDic;
}
catch (Exception ex)
{
throw new Exception("加载交易区间失败," + ex.Message);
}
}
/// <summary>
/// 初始化方法。
/// </summary>
public void Initialize(DateTime tradeDay)
{
string strSel = string.Format(@"select * from {0}.contracts
where open_date <= '{1}' and expire_date >= '{1}' and exchange in({2});",
m_alphaDBName, tradeDay.ToString("yyyy-MM-dd"), USeTraderProtocol.GetInternalFutureMarketSqlString());
DataTable table = MySQLDriver.GetTableFromDB(m_dbConnStr, strSel);
List <USeInstrumentDetail> instrumentList = new List <USeInstrumentDetail>();
foreach (DataRow row in table.Rows)
{
USeMarket exchange = (USeMarket)Enum.Parse(typeof(USeMarket), row["exchange"].ToString());
USeInstrumentDetail instrument = new USeInstrumentDetail()
{
Instrument = new USeInstrument(row["contract"].ToString(),
row["contract_name"].ToString(),
exchange),
OpenDate = Convert.ToDateTime(row["open_date"]),
ExpireDate = Convert.ToDateTime(row["expire_date"]),
StartDelivDate = Convert.ToDateTime(row["start_deliv_date"]),
EndDelivDate = Convert.ToDateTime(row["end_deliv_date"]),
VolumeMultiple = Convert.ToInt32(row["volume_multiple"]),
IsTrading = true,
Varieties = row["varieties"].ToString(),
PriceTick = Convert.ToDecimal(row["price_tick"]),
ExchangeLongMarginRatio = Convert.ToDecimal(row["exchange_long_margin_ratio"]),
ExchangeShortMarginRatio = Convert.ToDecimal(row["exchange_short_margin_ratio"]),
ProductClass = (USeProductClass)Enum.Parse(typeof(USeProductClass), row["product_class"].ToString()),
UnderlyingInstrument = row["underlying_instrument"].ToString(),
MaxMarketOrderVolume = Convert.ToInt16(row["max_market_order_volume"]),
MinMarketOrderVolume = Convert.ToInt16(row["min_market_order_volume"]),
MaxLimitOrderVolume = Convert.ToInt16(row["max_limit_order_volume"]),
MinLimitOrderVolume = Convert.ToInt16(row["min_limit_order_volume"])
};
instrumentList.Add(instrument);
}
m_instrumentList = instrumentList;
}
/// <summary>
/// 获取某个交易日，某个品种的主力合约
/// </summary>
/// <param name="dateTime"></param>
/// <param name="productID"></param>
/// <returns></returns>
private string GetTradingDaySeriesKline(DateTime dateTime, string productID)
{
string strSel = string.Format(@"select * from {0}.main_contract where varieties='{1}' AND settlement_date='{2:yyyy-MM-dd}';",
m_alphaDBName, productID, dateTime);
DataTable table = MySQLDriver.GetTableFromDB(m_dbConnStr, strSel);
foreach (DataRow row in table.Rows)
{
try
{
return(row["main_contract"].ToString());
}
catch (Exception ex)
{
throw new Exception("GetTradingDaySeriesKline:" + ex.Message);
}
}
return("");
}
/// <summary>
/// 获取某一个交易日某一个品种的所有合约K线用于计算Index
/// </summary>
/// <param name="dateTime"></param>
/// <param name="productID"></param>
/// <returns></returns>
private List <USeKLine> GetTradingDayIndexKlineContracts(DateTime dateTime, string productID)
{
string strSel = string.Format(@"select * from {0}.day_kline where contract like '{1}%' AND date_time='{2:yyyy-MM-dd 00:00:00}';",
m_alphaDBName, productID, dateTime);
DataTable table = MySQLDriver.GetTableFromDB(m_dbConnStr, strSel);
List <USeKLine> klineList = new List <USeKLine>();
foreach (DataRow row in table.Rows)
{
try
{
USeKLine tempKline = new USeKLine();
tempKline.InstrumentCode = row["contract"].ToString();
tempKline.Market = (USeMarket)Enum.Parse(typeof(USeMarket), row["exchange"].ToString());
tempKline.Close = Convert.ToDecimal(row["price_close"]);
tempKline.DateTime = Convert.ToDateTime(row["date_time"]);
tempKline.OpenInterest = Convert.ToDecimal(row["openinterest"]);
tempKline.SettlementPrice = (row["settlement_price"] != DBNull.Value) ? Convert.ToDecimal(row["settlement_price"]) : 0m;
tempKline.SendimentaryMoney = (row["sendimentary_money"] != DBNull.Value) ? Convert.ToDecimal(row["sendimentary_money"]) : 0m;
if (VerifyISIndexOrSeries(tempKline) == 0 && USeTraderProtocol.GetVarieties(tempKline.InstrumentCode) == productID)
{
klineList.Add(tempKline);
}
}
catch (Exception ex)
{
throw new Exception("初始化当天交易日的K线:" + ex.Message);
}
}
return(klineList);
}
public override int Connect(string host, string user, string pwd)
{
KnifeConfig.MySqlConnectionString = pGenerateConnectionString(host, this.pSchema, user, pwd);
mDriver = new MySQLDriver();
return(TestConnect());
}
/// <summary>
/// 获取一定范围内的日线数据的历史数据每个品种
/// </summary>
/// <param name="now"></param>
/// <param name="dbConnStr"></param>
/// <param name="marketDataDBName"></param>
/// <returns></returns>
private List <USeKLine> GetPreDayKLineData(DateTime now, string dbConnStr, string marketDataDBName)
{
//逆向排序
string strSel = string.Format(@"select * from {0}.day_kline where date_time >='{1}'
order by date_time desc;", marketDataDBName, now.ToString("yyyy-MM-dd"));
DataTable table = MySQLDriver.GetTableFromDB(dbConnStr, strSel);
List <USeKLine> kLineList = new List <USeKLine>();
foreach (DataRow row in table.Rows)
{
USeKLine kline = new USeKLine();
if (row["contract"] == DBNull.Value)
{
kline.InstrumentCode = "";
}
else
{
kline.InstrumentCode = row["contract"].ToString();
}
if (row["date_time"] == DBNull.Value)
{
kline.DateTime = DateTime.MinValue;
}
else
{
kline.DateTime = row["date_time"].ToDateTime();
}
if (row["sendimentary_money"] == DBNull.Value)
{
//因为起始状态如果昨天的资金沉淀为空的话，今天计算的时候就可以直接用昨天的计算补齐；如果不为空就直接取
//OpenInterest* LastPrice* perSharesContract *exchangeLongMarginRatio;//资金沉淀
decimal openInterest = row["openinterest"] != DBNull.Value? Convert.ToDecimal(row["openinterest"]):0m;
decimal settlementPrice = row["settlement_price"] != DBNull.Value? Convert.ToDecimal(row["settlement_price"]):0m;
double perSharesContract = 0;
decimal exchangeLongMarginRatio = 0;
foreach (USeInstrumentDetail ins in m_insDetailList)
{
if (ins.Instrument.InstrumentCode == kline.InstrumentCode)
{
perSharesContract = ins.VolumeMultiple;
exchangeLongMarginRatio = ins.ExchangeLongMarginRatio;
}
}
kline.SendimentaryMoney = openInterest * settlementPrice * Convert.ToDecimal(perSharesContract) * exchangeLongMarginRatio;
}
else
{
kline.SendimentaryMoney = Convert.ToDecimal(row["sendimentary_money"]);
}
kLineList.Add(kline);
}
return(kLineList);
}
