//从本地数据库获取股票上市退市信息
public List <StockIPOInfo> GetStockListInfoFromSql()
{
List <StockIPOInfo> stockInfos = new List <StockIPOInfo>();
var sqlStr = "select [Code],[SecName],[IPODate],[DelistDate],[UpdateDateTime] from [Common].[dbo].[StockInfo]";
DataTable dt = new DataTable();
try
{
dt = sqlReader.GetDataTable(sqlStr);
}
catch
{
logger.Warn(string.Format("There is no IPO information from specialized sqlserver!!!"));
}
foreach (DataRow dr in dt.Rows)
{
StockIPOInfo info = new StockIPOInfo();
info.code = Convert.ToString(dr[0]);
info.name = Convert.ToString(dr[1]);
info.IPODate = Convert.ToDateTime(dr[2]);
if (dr[3] != DBNull.Value)
{
info.DelistDate = Convert.ToDateTime(dr[3]);
}
else
{
info.DelistDate = new DateTime(2099, 12, 31);
}
info.updateTime = Convert.ToDateTime(dr[4]);
stockInfos.Add(info);
}
return(stockInfos);
}
public int GetDuration(DateTime start, DateTime end)
{
if (allTradeDays.Count == 0)
{
var sqlStr = string.Format("select DateTime from [Common].[dbo].[TransactionDate]");
var res = sqlReader.GetDataTable(sqlStr);
allTradeDays = res.ToList <DateTime>();
}
return(allTradeDays.IndexOf(end) - allTradeDays.IndexOf(start) + 1);
}
private void LoadStockTransactionToRedisFromSql(string code, List <DateTime> tradingDates)
{
var existedDateInRedis = GetExistedDateInRedis(code, tradingDates.First(), tradingDates.Last());
var nonExistedDateIntervalInRedis = Computor.GetNoExistedInterval <DateTime>(tradingDates, existedDateInRedis);
if (nonExistedDateIntervalInRedis != null && nonExistedDateIntervalInRedis.Count > 0)
{
string sqlStr = GenerateSqlString(code, nonExistedDateIntervalInRedis);
var dt = sqlReader.GetDataTable(sqlStr);
WriteToRedis(code, dt);
}
}
public DataTable Get(string code, DateTime begin, DateTime end)
{
if (begin.Date != end.Date)
{
throw new ArgumentException("开始时间和结束时间必须是同一天");
}
var sqlStr = string.Format(@"SELECT [stkcd],convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) as tdatetime
,[cp],[S1],[S2],[S3],[S4],[S5],[B1],[B2],[B3],[B4],[B5],[SV1],[SV2],[SV3],[SV4],[SV5],[BV1],[BV2],[BV3],[BV4],[BV5],[ts],[tt],[HighLimit],[LowLimit]
FROM [WindFullMarket{0}].[dbo].[MarketData_{1}]
where ((ttime>=91500000 and ttime<=113000000) or (ttime>=130000000 and ttime<=150100000)) and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) >='{2}'
and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) <='{3}'",
begin.ToString("yyyyMM"), code.Replace('.', '_'), begin, end);
if (begin.Date <= new DateTime(2011, 07, 31)) //公司数据库在2011年8月1日开始改变了ttime字段的格式
{
sqlStr = string.Format(@"SELECT [stkcd],convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(rtrim(ttime),3,0,':'),6,0,':')) as tdatetime
,[cp],[S1],[S2],[S3],[S4],[S5],[B1],[B2],[B3],[B4],[B5],[SV1],[SV2],[SV3],[SV4],[SV5],[BV1],[BV2],[BV3],[BV4],[BV5],[ts],[tt]
FROM [WindFullMarket{0}].[dbo].[MarketData_{1}]
where ((ttime>=91500 and ttime<=113000) or (ttime>=130000 and ttime<=150100)) and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(rtrim(ttime),3,0,':'),6,0,':')) >='{2}'
and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(rtrim(ttime),3,0,':'),6,0,':')) <='{3}'",
begin.ToString("yyyyMM"), code.Replace('.', '_'), begin, end);
}
if (begin.Date >= new DateTime(2012, 03, 01) && begin.Date <= new DateTime(2012, 05, 31))
{
sqlStr = string.Format(@"SELECT [stkcd],convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(rtrim(substring(ttime,0,7)),3,0,':'),6,0,':')) as tdatetime
,[cp],[S1],[S2],[S3],[S4],[S5],[B1],[B2],[B3],[B4],[B5],[SV1],[SV2],[SV3],[SV4],[SV5],[BV1],[BV2],[BV3],[BV4],[BV5],[ts],[tt],[HighLimit],[LowLimit]
FROM [WindFullMarket{0}].[dbo].[MarketData_{1}]
where ((substring(ttime,0,7)>=91500 and substring(ttime,0,7)<=113000) or (substring(ttime,0,7)>=130000 and substring(ttime,0,7)<=150100)) and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(rtrim(substring(ttime,0,7)),3,0,':'),6,0,':')) >='{2}'
and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(rtrim(substring(ttime,0,7)),3,0,':'),6,0,':')) <='{3}'",
begin.ToString("yyyyMM"), code.Replace('.', '_'), begin, end);
}
return(sqlReader.GetDataTable(sqlStr));
}
private List <StockBasicInfo> loadInfoDailyFromSql(string code, DateTime start, DateTime end)
{
List <StockBasicInfo> infoList = new List <StockBasicInfo>();
var timeList = SplitTimeYearly(start, end);
foreach (var day in timeList)
{
DateTime dayStart = day;
DateTime dayEnd = DateTimeExtension.DateUtils.PreviousOrCurrentTradeDay(new DateTime(day.Year, 12, 31));
if (dayEnd > end)
{
dayEnd = end;
}
var sqlStr = string.Format(@"select [Code],[DateTime] from [StockInfo].[dbo].[BasicInfoDaily{0}]
where Code='{1}' and DateTime>='{2}' and DateTime<='{3}'",
dayStart.Year, code, dayStart, dayEnd);
var dt = sqlReader.GetDataTable(sqlStr);
var tradedays = dateRepo.GetStockTransactionDate(dayStart, dayEnd);
if (dt.Rows.Count < tradedays.Count()) //数据不足，去万德拉取
{
var dtAll = windReader.GetDailyDataTable(code, "", dayStart, dayEnd);
var dtLack = getLackData(dt, dtAll);
if (dtLack.Rows.Count > 0)
{
WriteToSql(dtLack);
}
infoList.AddRange(dataTableToList(dtAll));
}
else
{
infoList.AddRange(dataTableToList(dt));
}
}
return(infoList);
}
public DataTable GetFromSpecializedSQLServer(string code, DateTime date, ConnectionType type)
{
var sqlReader = new SqlServerReader(type);
var sqlStr = string.Format(@"SELECT [stkcd],[tdatetime],[cp],[S1],[S2],[S3],[S4],[S5],[B1],[B2],[B3],[B4],[B5],[SV1],[SV2],[SV3],[SV4],[SV5],[BV1],[BV2],[BV3],[BV4],[BV5],[ts],[tt],[HighLimit],[LowLimit]
FROM [StockTickTransaction{0}].[dbo].[{1}]",
date.ToString("yyyy"), date.ToString("yyyy-MM-dd"));
return(sqlReader.GetDataTable(sqlStr));
}
private void LoadDataToRedisFromSqlServerIfNecessary(string code, DateTime date)
{
if (!ExistInRedis(code, date))
{
var sqlStr = string.Format(@"SELECT convert(varchar(30),[tdatetime],121) as tdatetime ,[cp] ,[S1] ,[S2] ,[S3] ,[S4] ,[S5]
,[B1] ,[B2] ,[B3],[B4] ,[B5] ,[SV1] ,[SV2] ,[SV3] ,[SV4] ,[SV5] ,[BV1] ,[BV2] ,[BV3],[BV4],[BV5],[ts],[tt],[HighLimit],[LowLimit]
FROM [StockTickTransaction{0}].[dbo].[{1}] where rtrim(stkcd)='{2}'", date.Year, date.ToString("yyyy-MM-dd"), code);
var dt = sqlReader.GetDataTable(sqlStr);
var key = string.Format(RedisKeyFormat, code.ToUpper(), date.ToString("yyyy-MM-dd"));
BulkWriteToRedis(key, dt);
}
}
private void LoadDataToRedisFromSqlServerIfNecessary(string code, DateTime date)
{
if (!ExistInRedis(code, date))
{
DateTime startTime = date.Date;
DateTime endTime = date.AddHours(15);
var sqlStr = string.Format(@"select [Code],[DateTime] ,[open],[HIGH],[LOW],[CLOSE],[VOLUME],[Amount] from [StockMinuteTransaction].[dbo].[{0}_{1}]
where DateTime>='{2}' and DateTime<='{3}'",
code.ToUpper().Split('.')[0], code.ToUpper().Split('.')[1], startTime, endTime);
try
{
var dt = sqlReader.GetDataTable(sqlStr);
var key = string.Format(RedisKeyFormat, code.ToUpper(), "Minute", date.ToString("yyyy-MM-dd"));
BulkWriteToRedis(key, dt);
}
catch (Exception e)
{
}
}
}
public DataTable Get(string code, DateTime begin, DateTime end)
{
if (begin.Date != end.Date)
{
throw new ArgumentException("开始时间和结束时间必须是同一天");
}
var sqlStr = string.Format(@"SELECT [stkcd],convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) as tdatetime
,[cp],[S1],[S2],[S3],[S4],[S5],[B1],[B2],[B3],[B4],[B5],[SV1],[SV2],[SV3],[SV4],[SV5],[BV1],[BV2],[BV3],[BV4],[BV5],[ts],[tt]
FROM [WindFullMarket{0}].[dbo].[MarketData_{1}]
where ((ttime>=93000000 and ttime<=113000000) or (ttime>=130000000 and ttime<=150000000)) and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) >='{2}'
and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) <='{3}'",
begin.ToString("yyyyMM"), code.Replace('.', '_'), begin, end);
return(sqlReader.GetDataTable(sqlStr));
}
private DataTable GetStockDailyTransactionFromSqlServer170(string code, DateTime begin, DateTime end)
{
var sqlStr = string.Format(@"SELECT [stkcd] as [Code]
,convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')) as [DateTime]
,[Open] as [open]
,[High] as [high]
,[Low] as [low]
,[Close] as [close]
,[Volume] as [volume]
,[Amount] as [amount]
FROM [DayLine].[dbo].[DailyData] where tdate>={2} and tdate<={3} order by [DateTime]",
code.Split('.')[0], code.Split('.')[1], begin.ToString("yyyyMMdd"), end.ToString("yyyyMMdd"));
var res = sqlReader170.GetDataTable(sqlStr);
return(res);
}
/// <summary>
/// 从sql源中读取数据
/// </summary>
/// <param name="code">股票代码</param>
/// <param name="startDate">开始时间</param>
/// <param name="endDate">结束时间</param>
private DataTable getDataFromSql(string code, DateTime startDate, DateTime endDate)
{
DataTable dt = new DataTable();
var sqlStr = string.Format(@"SELECT [stkcd] as [Code]
,DATEADD(mi,-1,convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(rtrim(ttime),3,0,':'))) as [DateTime]
,[Open] as [open]
,[High] as [high]
,[Low] as [low]
,[Close] as [close]
,[Volume] as [volume]
,[Amount] as [amount]
FROM [MinuteLine].[dbo].[Min1_{0}_{1}] where tdate>={2} and tdate<={3} order by [DateTime]",
code.Split('.')[0], code.Split('.')[1], startDate.ToString("yyyyMMdd"), endDate.ToString("yyyyMMdd"));
dt = sqlReaderSource.GetDataTable(sqlStr);
return(dt);
}
private List <StockMinuteTransaction> LoadStockMinuteFromSql(string code, DateTime currentTime)
{
List <StockMinuteTransaction> data = new List <StockMinuteTransaction>();
var exist = ExistInSqlServer(code, currentTime.Date);
if (exist != false)
{
DateTime dayStart = currentTime.Date + new TimeSpan(9, 25, 00);
DateTime dayEnd = currentTime.Date + new TimeSpan(15, 00, 00);
var sqlStr = string.Format(@"select [Code],[DateTime] ,[open],[HIGH],[LOW],[CLOSE],[VOLUME],[Amount] from [StockMinuteTransactionByTick{0}].[dbo].[Transaction{1}]
where Code='{2}' and DateTime>='{3}' and DateTime<='{4}'",
currentTime.Year, currentTime.ToString("yyyy-MM"), code, dayStart, dayEnd);
var dt = sqlReader.GetDataTable(sqlStr);
//dt转化为list
data = dataTableToTransactionList(dt);
}
return(data);
}
public List <StockTransaction> GetStockTransactionFrom170SqlByCode(string code, DateTime start, DateTime end)
{
var stocks = new List <StockTransaction>();
DateTime startTime = start.Date;
DateTime endTime = end.Date.AddHours(15);
try
{
var sqlStr = string.Format(@"select [Code],[DateTime] ,[open],[HIGH],[LOW],[CLOSE],[VOLUME],[Amount] from [StockMinuteTransaction].[dbo].[{0}_{1}]
where DateTime>='{2}' and DateTime<='{3}'",
code.ToUpper().Split('.')[0], code.ToUpper().Split('.')[1], startTime, endTime);
var dt = sqlReader170.GetDataTable(sqlStr);
var list = datatableToList(dt);
stocks.AddRange(list);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(stocks);
}
public DataTable Get(string code, DateTime begin, DateTime end)
{
if (begin.Date != end.Date)
{
throw new ArgumentException("开始时间和结束时间必须是同一天");
}
var sqlStr = string.Format(@"SELECT [stkcd],convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) as tdatetime
,[cp],[S1],[S2],[S3],[S4],[S5],[B1],[B2],[B3],[B4],[B5],[SV1],[SV2],[SV3],[SV4],[SV5],[BV1],[BV2],[BV3],[BV4],[BV5],[ts],[tt],[OpenInterest]
FROM [WindFullMarket{0}].[dbo].[MarketData_{1}]
where ((ttime>=93000000 and ttime<=113000000) or (ttime>=130000000 and ttime<=150000000)) and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) >='{2}'
and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) <='{3}'",
begin.ToString("yyyyMM"), code.Replace('.', '_'), begin, end);
// var sqlStr = string.Format(@"SELECT rtrim([code]) as [stkcd],stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+reverse(stuff(stuff(stuff(reverse(rtrim(ttime)),4,0,'.'),7,0,':'),10,0,':')) as tdatetime
//,[lastprice] as [cp],[ask1] as [S1],[ask2] as [S2],[ask3] as [S3],[ask4] as [S4],[ask5] as [S5],[bid1] as [B1],[bid2] as [B2],[bid3] as [B3],[bid4] as [B4],[bid5] as [B5],[askv1] as [SV1],[askv2] as [SV2],[askv3] as [SV3],[askv4] as [SV4],[askv5] as [SV5],[bidv1] as [BV1],[bidv2] as [BV2],[bidv3] as [BV3],[bidv4] as [BV4],[bidv5] as [BV5],[volume] as [ts],[amount] as [tt],[OpenInterest]
// FROM [TickData_50ETFOption].[dbo].[MarketData_{1}]
// where ((ttime>=93000000 and ttime<=113000000 and ttime%10000000<=5959999 and ttime%100000<=59999 ) or (ttime>=130000000 and ttime<=150000000 and ttime%10000000<=5959999 and ttime%100000<=59999 )) and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+reverse(stuff(stuff(stuff(reverse(rtrim(ttime)),4,0,'.'),7,0,':'),10,0,':'))) >='{2}'
// and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+reverse(stuff(stuff(stuff(reverse(rtrim(ttime)),4,0,'.'),7,0,':'),10,0,':'))) <='{3}'",
// begin.ToString("yyyyMM"), code.Replace('.', '_'), begin, end);
return(sqlReader.GetDataTable(sqlStr));
}
private void BulkLoadStockMinuteToRedisFromSql(string code, DateTime currentTime)
{
DateTime latestTime = GetLatestTimeFromRedis(code, currentTime);
var start = latestTime == default(DateTime) ? new DateTime(currentTime.Year, 1, 1) : latestTime.AddMinutes(1);
var end = GetEndTime(currentTime);
if (start < end)
{
Dictionary <DateTime, DateTime> dateSpan = SplitDateTimeMonthly(start, end);
foreach (var item in dateSpan)
{
var exist = ExistInSqlServer(code, item.Value);
if (exist != false)
{
var sqlStr = string.Format(@"select [Code],[DateTime] ,[open],[HIGH],[LOW],[CLOSE],[VOLUME],[Amount] from [StockMinuteTransaction{0}].[dbo].[Transaction{1}]
where Code='{2}' and DateTime>='{3}' and DateTime<='{4}'",
item.Key.Year, item.Key.ToString("yyyy-MM"), code, item.Key, item.Value);
var dt = sqlReader.GetDataTable(sqlStr);
WriteToRedis(dt);
}
}
}
}
private DataTable ReadFromSqlServer(string underlyingCode, DateTime start, DateTime end)
{
var sqlStr = string.Format("select * from [Common].dbo.[OptionInfo] where option_mark_code='{0}' and listed_date<='{2}' and expire_date>'{1}';", underlyingCode, start, end);
return(sqlReader.GetDataTable(sqlStr));
}