/// <summary>
/// 将列表数据转换为字符串参数
/// </summary>
/// <typeparam name="T">参数类型</typeparam>
/// <param name="lstParms">任意数据类型的参数集</param>
/// <param name="needSParm">是否需要两边加单引号</param>
/// <returns></returns>
public static string CastToSQLParmString <T>(List <T> lstParms, bool needSParm)
{
StringBuilder sb = new StringBuilder();
string retVal = "";
if (lstParms.Count > 0)
{
foreach (T parm in lstParms)
{
if (needSParm)
{
sb.Append(SysFunction.SParm(parm.ToString()) + ",");
}
else
{
sb.Append(parm.ToString() + ",");
}
}
retVal = sb.ToString();
retVal = retVal.Substring(0, retVal.Length - 1);
}
return(retVal);
}
/// <summary>获取指定类型的板块
/// </summary>
/// <param name="lstStockBlockType"></param>
/// <returns></returns>
public DataTable GetStockBlock(List <StockBlockType> lstStockBlockType)
{
const string sqlMod = "SELECT * FROM StockBlock WHERE BKType IN ({0})";
List <string> lstName = BLL.ConvertBlockTypeList2Name(lstStockBlockType);
string sParm = SysFunction.SParm(lstName.ToArray(), true);
return(SQLHelper.ExecuteDataTable(string.Format(sqlMod, sParm), CommandType.Text, _cnn));
}
/// <summary>获取个股的所有收盘价
/// </summary>
/// <param name="stkCode">股票代码</param>
/// <param name="isComposite">是否指数</param>
/// <param name="startDay">开始日期</param>
/// <param name="endDay">结束日期</param>
/// <returns></returns>
public Dictionary <DateTime, decimal> GetDayCloseValue(string stkCode, bool isComposite, DateTime startDay = default(DateTime), DateTime endDay = default(DateTime))
{
DataTable closePrice = _dbo.GetStockAllPrice(BLL.GetKLineDBTableName(KLineType.Day, isComposite), stkCode, new List <ValueType> {
ValueType.Close
}, startDay, endDay);
Dictionary <DateTime, decimal> ret = SysFunction.GetColDictionary <DateTime, decimal>(closePrice, 0, 1);
return(ret);
}
/// <summary> 所有股票（不包括指数）的第一个交易日。如果数据不全，则不代表上市日
/// </summary>
/// <returns></returns>
public Dictionary <string, DateTime> GetAllStockFirstDay()
{
if (_stockFirstDay.Count > 0)
{
const string strSql = "SELECT StkCode, FirstDay = MIN(TradeDay) FROM KLineDay GROUP BY StkCode";
DataTable dt = SQLHelper.ExecuteDataTable(strSql, CommandType.Text, _cnn);
_stockFirstDay = SysFunction.GetColDictionary <string, DateTime>(dt, 0, 1);
}
return(_stockFirstDay);
}
/// <summary> 指数代码列表
/// </summary>
/// <returns></returns>
public List <string> GetZSCodeList()
{
const string strSql = "SELECT StkCode FROM StockHead WHERE StkType = 0";
if (_stockZSCodeList == null || _stockZSCodeList.Count == 0)
{
DataTable dt = SQLHelper.ExecuteDataTable(strSql, CommandType.Text, _cnn);
_stockZSCodeList = SysFunction.GetColList <string>(dt, 0).ToList();
}
return(_stockZSCodeList);
}
/// <summary> 查找所有历史交易日（取上证指数为参考项）
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public List <DateTime> GetAllTradeDay(string tableName)
{
if (!AllTypeTradeDay.ContainsKey(tableName))
{
const string sqlMod = "SELECT DISTINCT TradeDay FROM {0} WHERE StkCode = '999999' ORDER BY TradeDay";
DataTable dtDays = SQLHelper.ExecuteDataTable(string.Format(sqlMod, tableName), CommandType.Text, _cnn);
List <DateTime> lstDay = SysFunction.GetColList <DateTime>(dtDays, 0).ToList();
AllTypeTradeDay.Add(tableName, lstDay);
}
return(AllTypeTradeDay[tableName]);
}
/// <summary> 获取个股总交易日数
/// </summary>
/// <param name="stkCode">股票代码</param>
/// <returns></returns>
public int GetStockTradeDayCount(string stkCode)
{
if (_allStockTradeDayCount.Count > 0 && !_allStockTradeDayCount.ContainsKey(stkCode))
{
return(0);
}
if (_allStockTradeDayCount.Count == 0)
{
const string strSql = "SELECT StkCode, DayCount = COUNT(TradeDay) FROM KLineDay GROUP BY StkCode";
DataTable dtCount = SQLHelper.ExecuteDataTable(strSql, CommandType.Text, _cnn);
_allStockTradeDayCount = SysFunction.GetColDictionary <string, int>(dtCount, 0, 1);
}
return(_allStockTradeDayCount.ContainsKey(stkCode) ? _allStockTradeDayCount[stkCode] : 0);
}
/// <summary> 清空指定的板块信息
/// </summary>
/// <param name="lstStockBlockType">为 Null 则清空所有</param>
public void ClearStockBlock(List <StockBlockType> lstStockBlockType = null)
{
if (lstStockBlockType == null)
{
base.TruncateTable("StockBlock");
}
else
{
if (lstStockBlockType.Count > 0)
{
const string modSql = "DELETE FROM StockBlock WHERE BKType IN ({0})";
List <string> lstName = BLL.ConvertBlockTypeList2Name(lstStockBlockType);
string sParm = SysFunction.SParm(lstName.ToArray(), true);
SQLHelper.ExecuteNonQuery(string.Format(modSql, sParm), CommandType.Text, _cnn);
}
}
}
/// <summary> 计算个股所有日涨幅
/// </summary>
/// <param name="dayTableName"></param>
/// <param name="stkCode">股票代码</param>
/// <returns></returns>
public Dictionary <DateTime, decimal> CalcStockRatio_OneStock(string dayTableName, string stkCode)
{
#region SQL
#region 原始SQL语句
/*
* SELECT curP.TradeDay, Increase = (curP.[Close] - prepP.[Close]) / prepP.[Close] * 100 FROM
* (
* SELECT RowNum = ROW_NUMBER() OVER ( ORDER BY TradeDay), TradeDay, [Close] FROM KLineDay
* WHERE StkCode = '600620'
* ) curP
* JOIN
* (
* SELECT RowNum = ROW_NUMBER() OVER ( ORDER BY TradeDay) + 1, TradeDay, [Close] FROM KLineDay
* WHERE StkCode = '600620'
* ) prepP
* ON curP.RowNum = prepP.RowNum
*/
#endregion 原始SQL语句
const string sqlMod = "SELECT curP.TradeDay, Increase = (curP.[Close] - prepP.[Close]) / prepP.[Close] * 100 FROM " + "\r\n"
+ "(" + "\r\n"
+ " SELECT RowNum = ROW_NUMBER() OVER ( ORDER BY TradeDay), TradeDay, [Close] FROM {0}" + "\r\n"
+ " WHERE StkCode = '{1}'" + "\r\n"
+ ") curP" + "\r\n"
+ "JOIN " + "\r\n"
+ "(" + "\r\n"
+ " SELECT RowNum = ROW_NUMBER() OVER ( ORDER BY TradeDay) + 1, TradeDay, [Close] FROM {0}" + "\r\n"
+ " WHERE StkCode = '{1}'" + "\r\n"
+ ") prepP" + "\r\n"
+ "ON curP.RowNum = prepP.RowNum";
#endregion SQL
DataTable dt = SQLHelper.ExecuteDataTable(string.Format(sqlMod, dayTableName, stkCode), CommandType.Text, _cnn);
Dictionary <DateTime, decimal> ret = SysFunction.GetColDictionary <DateTime, decimal>(dt, 0, 1);
return(ret);
}
/// <summary> 计算单日所有个股涨幅
/// </summary>
/// <param name="dayTableName"></param>
/// <param name="day"></param>
/// <returns></returns>
public Dictionary <string, decimal> CalcStockRatio_OneDay(string dayTableName, DateTime day)
{
#region SQL
#region 原始SQL语句
/*
* SELECT curP.StkCode, Increase = (curP.[Close] - prepP.[Close]) / prepP.[Close] * 100
* FROM KLineDay curP
* JOIN
* (
* SELECT a.* FROM KLineDay a
* JOIN (SELECT RecId = MAX(RecId) FROM KLineDay WHERE TradeDay < '2017/09/21' GROUP BY MarkType, StkCode) b
* ON a.RecId = b.RecId
* ) prepP
* ON curP.StkCode = prepP.StkCode
* WHERE curP.TradeDay = '2017/09/21'
*/
#endregion 原始SQL语句
const string sqlMod = "SELECT curP.StkCode, Increase = (curP.[Close] - prepP.[Close]) / prepP.[Close] * 100" + "\r\n"
+ "FROM {0} curP" + "\r\n"
+ "JOIN" + "\r\n"
+ "(" + "\r\n"
+ " SELECT a.* FROM {0} a" + "\r\n"
+ " JOIN (SELECT RecId = MAX(RecId) FROM {0} WHERE TradeDay < '{1}' GROUP BY MarkType, StkCode) b" + "\r\n"
+ " ON a.RecId = b.RecId" + "\r\n"
+ ") prepP" + "\r\n"
+ "ON curP.StkCode = prepP.StkCode" + "\r\n"
+ "WHERE curP.TradeDay = '{1}'";
#endregion SQL
DataTable dt = SQLHelper.ExecuteDataTable(string.Format(sqlMod, day.ToShortDateString()), CommandType.Text, _cnn);
Dictionary <string, decimal> ret = SysFunction.GetColDictionary <string, decimal>(dt, 0, 1);
return(ret);
}
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");
}
private void bkgDataImport_DoWork(object sender, DoWorkEventArgs e)
{
// < 覆盖，指数，TDX文件，K线级别 >
TupleValue <bool, bool, bool, KLineType> arg = (TupleValue <bool, bool, bool, KLineType>)e.Argument;
BLLDataImport bllDaImpt = new BLLDataImport(CommProp.ConnectionString);
bool isConvert = arg.Value1;
bool isComposite = arg.Value2;
bool useTDXFile = arg.Value3;
KLineType kLineType = arg.Value4;
UIInProcess(true);
_processCancel = false;
if (useTDXFile)
{
this.LoadFileList_TDXDayFile(isComposite);
}
else
{
this.LoadFileList_exportFile();
}
// List<TupleValue<完整文件名, StockHead>>
List <TupleValue <string, StockHead> > lstStockData = bllDaImpt.LoadMrkTypeAndCodeFromDataFile(AllFile, isComposite, useTDXFile);
if (lstStockData.Count > 0)
{
Stopwatch stopWatch = Stopwatch.StartNew();
try
{
bllDaImpt.OpenConnection();
int count = lstStockData.Count;
int insLineCount = 0;
string msgString = string.Empty;
// 显示百分比提示信息
Action <int, int, int> showMsg = ((per, all, lineCnt) =>
{
SysFunction.BackspaceInConsole(msgString, txtConsole);
msgString = string.Format("{0} / {1}，已导入：{2} 行)", per, all, lineCnt.ToString("N0"));
Console.Write(msgString);
});
Console.Write("正在导入...（");
// 是否需要删表动作，如果表中无记录，则省去 Delete 动作
bool haveRecord = bllDaImpt.GetTableRecordCount(BLL.GetKLineDBTableName(kLineType, isComposite)) > 0;
for (int i = 0; i < count; i++)
{
showMsg(i + 1, count, insLineCount);
// TupleValue<完整文件名, StockHead>
TupleValue <string, StockHead> stkData = lstStockData[i];
insLineCount += bllDaImpt.InsertStkKLine(stkData, isConvert, isComposite, useTDXFile, kLineType, haveRecord);
// 最后一批完成后，再刷一下
if (i == count - 1)
{
showMsg(i + 1, count, insLineCount);
}
if (_processCancel)
{
break;
}
}
if (_processCancel)
{
Console.WriteLine(" 导入终止！");
}
else
{
Console.WriteLine(" 导入完成！");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
bllDaImpt.CloseConnection();
_processCancel = false;
Console.WriteLine("总耗时：{0}", stopWatch.Elapsed);
}
}
UIInProcess(false);
}