public int InsertStkKLine(TupleValue <string, StockHead> stkInfo, bool isConvert, bool isComposite, bool useTDXFile, KLineType kLineType, bool haveRecord = true)
{
string fileFullName = stkInfo.Value1;
StockHead stkHead = stkInfo.Value2;
string tableName = BLL.GetKLineDBTableName(kLineType, isComposite);
DateTime existMaxDay = DateTime.MinValue;
DataTable insTable = _dbo.GetEmptyTable(tableName);
if (haveRecord)
{
if (isConvert)
{
_dbo.DeleteTable(tableName, stkHead);
}
else
{
existMaxDay = _dbo.FindMaxExistTradeDay(tableName, stkHead);
}
}
if (useTDXFile)
{
this.LoadDayLineFileData_TDXDayFile(fileFullName, stkHead, existMaxDay, ref insTable);
}
else
{
this.LoadDayLineFileData_exportFile(fileFullName, stkHead, existMaxDay, ref insTable);
}
_dbo.BulkWriteTable(insTable, DataRowState.Added);
return(insTable.Rows.Count);
}
/// <summary>
/// 从文本文件加载数据，小于最大日期的数据直接过滤掉
/// </summary>
private void LoadDayLineFileData_TDXDayFile(string fileFullName, StockHead stkHead, DateTime existMaxDay, ref DataTable insTable)
{
bool isConvert = existMaxDay == DateTime.MinValue;
const string idxTabMarkType = "MarkType",
idxTabStkCode = "StkCode",
idxTabTradeDay = "TradeDay",
idxTabOpen = "Open",
idxTabHigh = "High",
idxTabLow = "Low",
idxTabClose = "Close",
idxTabVolume = "Volume",
idxTabAmount = "Amount";
const int lineLength = 32;
using (var reader = new BinaryReader(File.OpenRead(fileFullName))) // C:\new_tdx\vipdoc\sz\lday\sz399001.day
{
long len = reader.BaseStream.Length / lineLength;
for (int i = 0; i < len; i++)
{
string dateOrg = reader.ReadUInt32().ToString();
double open = reader.ReadInt32() / 100.00;
double high = reader.ReadInt32() / 100.00;
double low = reader.ReadInt32() / 100.00;
double close = reader.ReadInt32() / 100.00;
float amount = reader.ReadSingle();
int vol = reader.ReadInt32();
reader.ReadInt32(); //reservation
string dateStr = dateOrg.Substring(0, 4) + "/" + dateOrg.Substring(4, 2) + "/" + dateOrg.Substring(6, 2);
DateTime tradeDate;
if (DateTime.TryParse(dateStr, out tradeDate) &&
(isConvert || tradeDate > existMaxDay) &&
vol > 0)
{
DataRow newRow = insTable.NewRow();
newRow[idxTabMarkType] = stkHead.MarkType;
newRow[idxTabStkCode] = stkHead.StkCode;
newRow[idxTabTradeDay] = tradeDate;
newRow[idxTabOpen] = open;
newRow[idxTabHigh] = high;
newRow[idxTabLow] = low;
newRow[idxTabClose] = close;
newRow[idxTabVolume] = vol;
newRow[idxTabAmount] = amount;
insTable.Rows.Add(newRow);
}
}
}
}
/// <summary>找到指定股票代码已有数据的最大交易日
/// </summary>
public DateTime FindMaxExistTradeDay(string tableName, StockHead stkHead)
{
DateTime retVal = DateTime.MinValue;
const string strSql = "SELECT TradeDay = MAX(TradeDay) FROM {0}" + "\r\n"
+ "WHERE MarkType = '{1}' AND StkCode = '{2}'";
object objMaxDay = SQLHelper.ExecuteScalar(string.Format(strSql, tableName, stkHead.MarkType, stkHead.StkCode), CommandType.Text, _cnn);
if (objMaxDay != System.DBNull.Value)
{
retVal = (DateTime)objMaxDay;
}
return(retVal);
}
/// <summary> 读取通达信 tnf 文件，获取 代码、名称、缩写 信息
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="zsPrefix">指数类型前缀集合</param>
/// <param name="markType">sh or sz</param>
/// <returns></returns>
private List <StockHead> LoadTDXStockHeadFile(string fileName, string[] zsPrefix, string markType)
{
List <StockHead> ret = new List <StockHead>();
Dictionary <string, string> allDayLineFile = GetAllTDXDayLineFile();
using (BinaryReader reader = new BinaryReader(File.OpenRead(fileName)))
{
/*
* 头部无用区域 共 50 字节
* IP:Encoding.Default.GetString(reader.ReadBytes(40)).TrimEnd('\0')
* 可能是某个数量：reader.ReadInt16()
* 日期：reader.ReadInt32()
* 时间：reader.ReadInt32()
*/
reader.ReadBytes(50);
int count = (int)((reader.BaseStream.Length - 50) / 314);
for (int i = 0; i < count; i++)
{
string stkCode = Encoding.Default.GetString(reader.ReadBytes(9)).TrimEnd('\0').Trim();
reader.ReadBytes(12); // 未知区域
string stkName = Encoding.Default.GetString(reader.ReadBytes(18)).Trim('\0').Trim();
reader.ReadBytes(246); // 未知区域
// 拼音缩写
string stkNameAbbr = Encoding.Default.GetString(reader.ReadBytes(9)).TrimEnd('\0').Trim();
reader.ReadBytes(20); // 未知区域
string fullCode = markType.ToLower() + stkCode;
if (allDayLineFile.ContainsKey(fullCode))
{
StockHead stkHead = new StockHead();
stkHead.MarkType = markType;
stkHead.StkCode = stkCode;
stkHead.StkName = stkName.Replace(" ", ""); // 有些三个字的名字中间会有空格
stkHead.StkNameAbbr = stkNameAbbr;
stkHead.StkType = zsPrefix.Any(stkCode.StartsWith) ? "0" : "1";
ret.Add(stkHead);
}
}
}
return(ret);
}
/// <summary>
/// 从通达信导出的文件 / 自有文件 名获取市场类型及股票代码
/// 返回值：List<TupleValue<完整文件名, StockHead>>
/// </summary>
public List <TupleValue <string, StockHead> > LoadMrkTypeAndCodeFromDataFile(List <FileInfo> allFile, bool isComposite, bool useTDXFile)
{
List <TupleValue <string, StockHead> > ret = new List <TupleValue <string, StockHead> >();
List <string> codeList;
if (isComposite)
{
codeList = _dbo.GetZSCodeList();
}
else
{
codeList = _dbo.GetStockACodeList();
}
foreach (FileInfo file in allFile)
{
string[] nameSplit;
if (useTDXFile)
{
nameSplit = new[] { file.Name.Substring(0, 2), file.Name.Substring(2, 6) }
}
;
else
{
nameSplit = file.Name.Replace(file.Extension, "").Split('#');
}
StockHead stkHead = new StockHead
{
MarkType = nameSplit[0].ToLower(),
StkCode = nameSplit[1],
StkType = isComposite ? "0" : "1"
};
if (codeList.Contains(stkHead.StkCode))
{
ret.Add(new TupleValue <string, StockHead>(file.FullName, stkHead));
}
}
return(ret);
}
/// <summary>新增或修改 StockHead(StkName)
/// </summary>
public void InsertOrUpdateStockHead(StockHead stockHead)
{
StockHead existStkHead = this.FindStockHead(stockHead.MarkType, stockHead.StkCode);
if (string.IsNullOrEmpty(existStkHead.StkCode))
{
const string strSql = "INSERT INTO StockHead(MarkType, StkCode, StkName, StkNameAbbr, StkType) " + "\r\n"
+ "VALUES('{0}','{1}','{2}','{3}','{4}') ";
SQLHelper.ExecuteNonQuery(string.Format(strSql, stockHead.MarkType, stockHead.StkCode, stockHead.StkName, stockHead.StkNameAbbr, stockHead.StkType), CommandType.Text, _cnn);
}
else
{
if (existStkHead.StkName.Trim() != stockHead.StkName.Trim())
{
const string strSql = "UPDATE StockHead SET StkName = '{0}', stockHead = '{1}' WHERE MarkType = '{2}' AND StkCode = '{3}'";
SQLHelper.ExecuteNonQuery(string.Format(strSql, stockHead.StkName, stockHead.StkNameAbbr, stockHead.MarkType, stockHead.StkCode), CommandType.Text, _cnn);
}
}
}
public void ImportStockHead()
{
DataTable dtStockHead = _dbo.GetStockHeadAll();
TupleValue <string, string> headFileName = StockHeadFileName();
string fileSH = (CommProp.TDXFolder + headFileName.Value1).Replace(@"\\", @"\");
string fileSZ = (CommProp.TDXFolder + headFileName.Value2).Replace(@"\\", @"\");
List <StockHead> lstStockHeadSH = LoadTDXStockHeadFile(fileSH, new[] { "999", "000", "880" }, "sh");
List <StockHead> lstStockHeadSZ = LoadTDXStockHeadFile(fileSZ, new[] { "399" }, "sz");
string tableName = typeof(StockHead).Name;
Action <List <StockHead>, string> updateStockHeadTable =
(
(lstStockHeadFile, markType) =>
{
dtStockHead.DefaultView.RowFilter = string.Format("MarkType = '{0}'", markType);
// 现有已存在的代码
List <string> lstAllExistsCode = dtStockHead.DefaultView.ToTable().Rows.Cast <DataRow>().Select(row => row["StkCode"].ToString().Trim()).ToList();
// 与 TDX文件 共有，需要检验更新的
List <StockHead> lstNeedUpdate = lstStockHeadFile.Where(stkHead => lstAllExistsCode.Contains(stkHead.StkCode)).ToList();
// TDX文件 中有，当前没有的，需要增加
List <StockHead> lstNeedAdd = lstStockHeadFile.Where(stkHead => !lstAllExistsCode.Contains(stkHead.StkCode)).ToList();
// TDX文件 中没有，需要删除
List <string> lstNeedDelCode = lstAllExistsCode.Where(stkCode => !lstStockHeadFile.Select(stkHead => stkHead.StkCode).ToList().Contains(stkCode)).ToList();
// 处理需要删除或修改的行
foreach (DataRow dr in dtStockHead.Rows.Cast <DataRow>().Where(row => row["MarkType"].ToString() == markType))
{
string stkCode = dr["StkCode"].ToString();
if (lstNeedDelCode.Contains(stkCode))
{
dr.Delete();
}
else
{
StockHead updHead = lstNeedUpdate.FirstOrDefault(head => head.StkCode == stkCode);
if (updHead != null)
{
if (dr["StkName"].ToString() != updHead.StkName)
{
dr["StkName"] = updHead.StkName;
}
if (dr["StkNameAbbr"].ToString() != updHead.StkNameAbbr)
{
dr["StkNameAbbr"] = updHead.StkNameAbbr;
}
if (dr["StkType"].ToString() != updHead.StkType)
{
dr["StkType"] = updHead.StkType;
}
}
}
}
// 这样更新大批量有点慢，但是通常没有什么需要删除或修改的
if (SysFunction.TableChanged(dtStockHead))
{
const string strSql = "SELECT * FROM StockHead";
SysFunction.SaveChanges(_cnn, strSql, dtStockHead);
dtStockHead.AcceptChanges();
}
// 新增行
DataTable insTable = _dbo.GetEmptyTable(tableName);
foreach (StockHead stkHead in lstNeedAdd)
{
DataRow newRow = insTable.NewRow();
newRow["MarkType"] = stkHead.MarkType;
newRow["StkCode"] = stkHead.StkCode;
newRow["StkName"] = stkHead.StkName;
newRow["StkNameAbbr"] = stkHead.StkNameAbbr;
newRow["StkType"] = stkHead.StkType;
insTable.Rows.Add(newRow);
}
_dbo.BulkWriteTable(insTable, DataRowState.Added);
}
);
//_dbo.TruncateTable(tableName);
updateStockHeadTable(lstStockHeadSH, "sh");
updateStockHeadTable(lstStockHeadSZ, "sz");
}
/// <summary>
/// 从文本文件加载数据，小于最大日期的数据直接过滤掉
/// </summary>
private void LoadDayLineFileData_exportFile(string fileFullName, StockHead stkHead, DateTime existMaxDay, ref DataTable insTable)
{
bool isConvert = existMaxDay == DateTime.MinValue;
StreamReader sr = new StreamReader(fileFullName, Encoding.Default);
string line;
decimal vol;
const string idxTabMarkType = "MarkType",
idxTabStkCode = "StkCode",
idxTabTradeDay = "TradeDay",
idxTabOpen = "Open",
idxTabHigh = "High",
idxTabLow = "Low",
idxTabClose = "Close",
idxTabVolume = "Volume",
idxTabAmount = "Amount";
const int idxTradeDay = 0,
idxOpen = 1,
idxHigh = 2,
idxLow = 3,
idxClose = 4,
idxVolume = 5,
idxAmount = 6;
bool firstLine = true;
while ((line = sr.ReadLine()) != null)
{
// 第一行中有股票名称
if (firstLine)
{
string[] split = line.Split(' ');
if (split.Length > 1)
{
stkHead.StkName = split[1];
for (int i = 2; i < split.Length; i++)
{
if (split[i].IndexOf("日线") < 0 && split[i].IndexOf("分钟线") < 0)
{
stkHead.StkName += split[i];
}
else
{
break;
}
}
}
firstLine = false;
}
// 第三行开始为数据
else
{
string[] split = line.Split('\t');
DateTime tradeDate;
if (DateTime.TryParse(split[idxTradeDay], out tradeDate))
{
if (isConvert || tradeDate > existMaxDay)
{
vol = decimal.Parse(split[idxVolume]);
if (vol > 0)
{
DataRow newRow = insTable.NewRow();
newRow[idxTabMarkType] = stkHead.MarkType;
newRow[idxTabStkCode] = stkHead.StkCode;
newRow[idxTabTradeDay] = split[idxTradeDay];
newRow[idxTabOpen] = split[idxOpen];
newRow[idxTabHigh] = split[idxHigh];
newRow[idxTabLow] = split[idxLow];
newRow[idxTabClose] = split[idxClose];
newRow[idxTabVolume] = vol;
newRow[idxTabAmount] = split[idxAmount];
insTable.Rows.Add(newRow);
}
}
}
}
}
}
public void DeleteTable(string tableName, StockHead stkHead)
{
const string strSql = "DELETE FROM {0} WHERE MarkType = '{1}' AND StkCode = '{2}'";
SQLHelper.ExecuteNonQuery(string.Format(strSql, tableName, stkHead.MarkType, stkHead.StkCode), CommandType.Text, _cnn);
}