/// <summary>
/// 将csv文件导入数据库
/// </summary>
/// <param name="code">证券代码</param>
/// <param name="name">证券名称</param>
/// <param name="csvPath">csv文件路径</param>
/// <returns>受影响行数</returns>
public int ImportFromCSV(string code, string name, string csvPath)
{
DataTable dt = TXTUtility.ReadTxtSplitByRegStr(csvPath, 1, @",", null);
if (dt == null)
{
return(0);
}
else
{
int insertedLinesCount = 0;
foreach (DataRow row in dt.Rows)
{
StockDayPrice stock1 = new StockDayPrice();
stock1.Code = code;
stock1.Name = name;
stock1.Date = Convert.ToDateTime(row["Date"]);
stock1.Open = Convert.ToDouble(row["Open"]);
stock1.High = Convert.ToDouble(row["High"]);
stock1.Low = Convert.ToDouble(row["Low"]);
stock1.Close = Convert.ToDouble(row["Close"]);
stock1.Volume = Convert.ToDouble(row["Volume"]);
stock1.AdjClose = Convert.ToDouble(row["Adj Close"]);
int j = this.AddNew(stock1);
insertedLinesCount += j;
}
return(insertedLinesCount);
}
}
/// <summary>
/// 将xlsx文件导入数据库
/// </summary>
/// <param name="xlsxPath">xlsx文件路径</param>
/// <returns>受影响行数</returns>
public int ImportFromXlsx(string xlsxPath)
{
using (FileStream stream = new FileStream(xlsxPath, FileMode.Open, FileAccess.Read))
{
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheetAt(0);
int insertedLinesCount = 0;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
StockDayPrice stock1 = new StockDayPrice();
stock1.Code = row.GetCell(0).StringCellValue;
stock1.Name = "Unknown";
stock1.Date = row.GetCell(1).DateCellValue;
stock1.Open = row.GetCell(2).NumericCellValue;
stock1.High = row.GetCell(3).NumericCellValue;
stock1.Low = row.GetCell(4).NumericCellValue;
stock1.Close = row.GetCell(5).NumericCellValue;
stock1.Volume = row.GetCell(6).NumericCellValue;
stock1.AdjClose = row.GetCell(7).NumericCellValue;
int j = this.AddNew(stock1);
insertedLinesCount += j;
}
return(insertedLinesCount);
}
}
/// <summary>
/// 将csv文件导入数据库(不推荐使用)
/// </summary>
/// <param name="code">证券代码</param>
/// <param name="name">证券名称</param>
/// <param name="csvPath">csv文件路径</param>
/// <returns>受影响行数</returns>
public int ImportFromCSVAsDB(string code, string name, string csvPath)
{
DataTable dt = CSVUtility.ReadCSVAsDB(csvPath);
if (dt == null)
{
return(0);
}
else
{
int insertedLinesCount = 0;
foreach (DataRow row in dt.Rows)
{
StockDayPrice stock1 = new StockDayPrice();
stock1.Code = code;
stock1.Name = name;
stock1.Date = (DateTime)row["Date"];
stock1.Open = (double)row["Open"];
stock1.High = (double)row["High"];
stock1.Low = (double)row["Low"];
stock1.Close = (double)row["Close"];
stock1.Volume = Convert.ToDouble(row["Volume"]);//(double)会报错Specified cast is not valid.奇怪！！！超过int范围时row["Volume"]为DBNull
stock1.AdjClose = (double)row["Adj Close"];
int j = this.AddNew(stock1);
insertedLinesCount += j;
}
return(insertedLinesCount);
}
}
/// <summary>
/// 根据指定的日期和股票代码，查询相应日K线数据
/// </summary>
/// <param name="date">日期</param>
/// <param name="code">股票代码</param>
/// <returns>StockDayPrice对象</returns>
/// <exception cref="System.Exception">指定日期股票数据重复</exception>
public StockDayPrice GetByDateCode(DateTime date, string code)
{
DataTable dt = new DataTable();
dt = SQLHelper.ExecuteDataTable("select * from T_DayPrice where [Date]=@Date and [Code]=@Code", new SqlParameter("Date", date), new SqlParameter("Code", code));
if (dt.Rows.Count == 1)
{
DataRow row = dt.Rows[0];
StockDayPrice st = new StockDayPrice();
st.ID = (int)row["ID"];
st.Code = (string)row["Code"];
st.Name = (string)row["Name"];
st.Date = (DateTime)row["Date"];
st.Open = (double)row["Open"];
st.High = (double)row["High"];
st.Low = (double)row["Low"];
st.Close = (double)row["Close"];
st.Volume = (double)row["Volume"];
st.AdjClose = (double)row["AdjClose"];
return(st);
}
else if (dt.Rows.Count > 1)
{
throw new Exception(string.Format("{0}股票{1}日数据重复！", code, date));
}
else
{
return(null);
}
}
public int AddNew(StockDayPrice stock)
{
if (this.CheckExists(stock.Date, stock.Code))
{
return(0);
}
else
{
return(stockService.AddNew(stock));
}
}
public bool CheckExists(DateTime date, string code)
{
StockDayPrice stock = stockService.GetByDateCode(date, code);
if (stock == null)
{
return(false);
}
else
{
return(true);
}
}
public int Update(StockDayPrice stock)
{
return(SQLHelper.ExecuteNonQuery("update T_DayPrice set [Open]=@Open,[High]=@High,[Low]=@Low,[Close]=@Close,[Volume]=@Volume,[AdjClose]=@AdjClose where [Code]=@Code and [Name]=@Name and [Date]=@Date",
new SqlParameter("Code", stock.Code),
new SqlParameter("Name", stock.Name),
new SqlParameter("Date", stock.Date),
new SqlParameter("Open", stock.Open),
new SqlParameter("High", stock.High),
new SqlParameter("Low", stock.Low),
new SqlParameter("Close", stock.Close),
new SqlParameter("Volume", stock.Volume),
new SqlParameter("AdjClose", stock.AdjClose)
));
}
public int AddNew(StockDayPrice stock)
{
return(SQLHelper.ExecuteNonQuery("insert into T_DayPrice([Code],[Name],[Date],[Open],[High] ,[Low] ,[Close],[Volume] ,[AdjClose]) values(@Code,@Name,@Date,@Open,@High,@Low,@Close,@Volume,@AdjClose)",
new SqlParameter("Code", stock.Code),
new SqlParameter("Name", stock.Name),
new SqlParameter("Date", stock.Date),
new SqlParameter("Open", stock.Open),
new SqlParameter("High", stock.High),
new SqlParameter("Low", stock.Low),
new SqlParameter("Close", stock.Close),
new SqlParameter("Volume", stock.Volume),
new SqlParameter("AdjClose", stock.AdjClose)
));
}
public IEnumerable <StockDayPrice> GetByCode(string code)
{
DataTable dt = new DataTable();
dt = SQLHelper.ExecuteDataTable("select * from T_DayPrice where [Code]=@Code order by [Date]", new SqlParameter("Code", code));
List <StockDayPrice> list = new List <StockDayPrice>();
foreach (DataRow row in dt.Rows)
{
StockDayPrice st = new StockDayPrice();
st.ID = (int)row["ID"];
st.Code = (string)row["Code"];
st.Name = (string)row["Name"];
st.Date = (DateTime)row["Date"];
st.Open = (double)row["Open"];
st.High = (double)row["High"];
st.Low = (double)row["Low"];
st.Close = (double)row["Close"];
st.Volume = (double)row["Volume"];
st.AdjClose = (double)row["AdjClose"];
list.Add(st);
}
return(list);
}
public int Update(StockDayPrice stock)
{
return(stockService.Update(stock));
}
//注意：当date日的portfolio存在时，该函数什么也不做
public void PGeneraterByDate(DateTime date)
{
DateTime yesterday = this.portfolioManager.GetMaxDate();
if (date <= yesterday)
{
return;
}
List <Portfolio> oldPortfolios = this.portfolioManager.GetByDate(yesterday).ToList(); //count>0，至少含cash
IEnumerable <CheckList> newCheckLists = this.checkListManager.GetByDate(date); //count可能为0,
bool done = false;
//有交割单则逐条处理交割单
if (newCheckLists.Count() != 0)
{
foreach (var checkList in newCheckLists)
{
if (checkList.TradeName == "银行转证券" || checkList.TradeName == "证券转银行")
{
foreach (var portfolio in oldPortfolios)
{
if (portfolio.Code == "cash")
{
portfolio.ClosePrice += checkList.SettlementAmount;
portfolio.CostPrice += checkList.SettlementAmount;
done = true;
}
}
}
else if (checkList.TradeName.Trim() == "利息归本")
{
foreach (var portfolio in oldPortfolios)
{
if (portfolio.Code == "cash")
{
portfolio.ClosePrice += checkList.SettlementAmount;
done = true;
}
}
}
else
{
foreach (var portfolio in oldPortfolios)
{
if (portfolio.Code == "cash")
{
portfolio.ClosePrice += checkList.SettlementAmount;
}
if (portfolio.Code == checkList.Code)
{
if ((portfolio.Position + checkList.TradeDirection * checkList.TradeVolume) == 0)
{
portfolio.CostPrice = 0;
}
else
{
portfolio.CostPrice = (portfolio.CostPrice * portfolio.Position + checkList.SettlementAmount * (-1)) / (portfolio.Position + checkList.TradeDirection * checkList.TradeVolume);
}
portfolio.Position += (checkList.TradeDirection * checkList.TradeVolume);
//portfolio.ClosePrice = this.stockDayPriceManager.GetByDateCode(date, portfolio.Code).Close;
done = true;
}
}
}
//将没有处理的交割单（新建仓的证券）加入portfolio
if (done == false)
{
Portfolio newPortfolio = new Portfolio();
newPortfolio.Date = date;
newPortfolio.Code = checkList.Code;
if (checkList.TradeName.Trim() == "新股申购")
{
newPortfolio.Name = checkList.Name + "(新股申购)";
}
else
{
newPortfolio.Name = checkList.Name;
}
newPortfolio.Position = (checkList.TradeDirection * checkList.TradeVolume);
newPortfolio.BeginDate = date;
newPortfolio.CostPrice = checkList.SettlementAmount / checkList.TradeVolume * (-1);
newPortfolio.ClosePrice = newPortfolio.CostPrice;//暂时取CostPrice，后面会统一更新//this.stockDayPriceManager.GetByDateCode(date, checkList.Code).Close;
oldPortfolios.Add(newPortfolio);
}
else
{
done = false;
}
}
}
//更新portfolio的日期和收盘价，然后将portfolio中股数非0的portfolio数据上传数据库
foreach (var portfolio in oldPortfolios)
{
if (portfolio.Position != 0)
{
portfolio.Date = date;
if (portfolio.Code != "cash" && portfolio.Name.Substring(portfolio.Name.Length - Math.Min(portfolio.Name.Length, 6)) != "(新股申购)")
{
StockDayPrice dayPrice = this.stockDayPriceManager.GetByDateCode(date, portfolio.Code);
if (dayPrice != null)
{
portfolio.ClosePrice = dayPrice.Close;
}
else
{
//如果数据库中找不到收盘价，则自动下载更新股票数据，并重新查找收盘价
this.stockDayPriceManager.AutoUpdateByCodeName(portfolio.Code, portfolio.Name, date);
dayPrice = this.stockDayPriceManager.GetByDateCode(date, portfolio.Code);
if (dayPrice != null)
{
portfolio.ClosePrice = dayPrice.Close;
}
else //如果仍然找不到收盘价，说明更新数据失败。删除date日的数据（cash数据），跳出循环，不更新portfolio数据。
{
this.portfolioManager.DeleteByDate(date);
return;
}
}
}
this.portfolioManager.AddNew(portfolio);
}
}
}
