/// <summary>
/// Gets all needed add STK code.
/// </summary>
/// <returns></returns>
private async Task <List <string> > GetAllNeededAddStkCode()
{
using (var se = new StockEntities())
{
// 取得category = 1所有項目的列表
var stkCodeListInCat1 = se.ListedStock.Where(l => l.StkCategory == 1).Select(l => l.StkCode).ToList();
// 取得股票價格所有項目的列表
var stockPriceList = se.StockHistoricalPrice.Select(p => p.StkCode).Distinct().ToList();
var exceptList = stkCodeListInCat1.Except(stockPriceList).ToList();
// 取得stkCodeListInCat1除了stockPriceList的差集, 結果為沒有任何價格紀錄需要被新增的集合
this.totalStkAmount = exceptList.Count();
return(exceptList);
}
}
public List <ListedStock> ReadStkCodeAndNameWithCat(int Category)
{ //取得category X 股票代碼分類 StockData.StkCategory
//Stock = 1
//ListedWarrant = 2
//TaiwanDepositaryReceipt = 3
//RealEstateInvestmentTrust = 4
//ManageStocks = 5
//AssetBasedSecurities = 6
//ETF = 7
//SpecialStock = 8
using (StockEntities se = new StockEntities())
{
var stkCodeAndNameList = (from stkCodeName in se.ListedStock
where stkCodeName.StkCategory == Category
select stkCodeName).ToList();
return(stkCodeAndNameList);
}
}
public List <StockHistoricalPrice> ReadHistoricalPrice(string stkId)
{
var startDate = StockTypeSettings.StartDate;
var endDate = StockTypeSettings.EndDate;
if (startDate == DateTime.Parse("0001/1/1"))
{
startDate = DateTime.Now.AddYears(-1);
}
if (endDate == DateTime.Parse("0001/1/1"))
{
endDate = DateTime.Now;
}
using (StockEntities se = new StockEntities())
{ //取得listbox被選中的股票的歷史價格
var oneOfStkHistoryPrice = (from oneOfStkPrice in se.StockHistoricalPrice
where oneOfStkPrice.StkCode == stkId &&
oneOfStkPrice.Date >= startDate && oneOfStkPrice.Date <= endDate
select oneOfStkPrice).ToList();
return(oneOfStkHistoryPrice);
}
}
public async Task UpdatePriceToDB(IProgress <RptStructure> progress)
{
// using (var se = new StockEntities())
// {
// // 刪除所有價格資料
// se.StockHistoricalPrice.Where(s => true).DeleteFromQuery();
// se.BulkSaveChanges(operation => operation.BatchTimeout = 200);
// }
var neededUpdateList = await this.GetAllNeededAddStkCode(); // 取得stkcode差集列表
var allStkPrice = await this.GetAllStkPrice(neededUpdateList, progress); // 取得所有需要新增的股票價格列表
using (var se = new StockEntities())
{
// 新增所有缺少記錄的價格資料
var index = 0;
RptStructure RptProgress;
foreach (var priceOfList in allStkPrice)
{
RptProgress = new RptStructure();
await se.BulkInsertAsync(priceOfList);
await se.BulkSaveChangesAsync();
RptProgress.WrittingDBProgress = (float)++index / allStkPrice.Count * 100;
progress.Report(RptProgress);
}
RptProgress = new RptStructure()
{
CompletedMsg = "更新歷史價格完成!"
};
progress.Report(RptProgress);
}
}
/// <summary>Writes to database.</summary>
public void WriteToDb()
{
using (var se = new StockEntities())
{
var deleteData = from ls in se.ListedStock select ls;
bulkRemoveStopwatchTime.Start();
se.BulkDelete(deleteData);
bulkRemoveStopwatchTime.Stop();
dataTransformStopwatchTime.Start();
var stockList = new List <ListedStock>();
// 將自訂類別轉換成ListedStock類別
foreach (var sd in this.stockDataCollection)
{
var stock = new ListedStock
{
StkCategory = sd.IntStkCategory,
StkCode = sd.StrStkCode,
StkName = sd.StrStkName,
ISIN_code = sd.StrISIN_code,
SubmitDate = sd.StrSubmitDate,
MarketNo = sd.StrMarketNo,
BelongClass = sd.StrBelongClass,
CFI_code = sd.StrCFI_code
};
stockList.Add(stock);
}
// 如果找不到重複的主鍵，便加入到儲存列表中
var allStkInDb = (from stk in se.ListedStock
orderby stk.StkCategory, stk.StkCode
select stk).ToList();
var neededWritingStockList = new List <ListedStock>();
// 剔除已經在資料庫內的StkCode
foreach (var incomeStk in stockList)
{
var match = false;
foreach (var dbStk in allStkInDb)
{
if (incomeStk.StkCode == dbStk.StkCode)
{
match = true;
}
}
if (!match)
{
neededWritingStockList.Add(incomeStk);
}
}
dataTransformStopwatchTime.Stop();
bulkAddStopwatchTime.Start();
se.BulkInsert(neededWritingStockList);
se.BulkSaveChanges();
bulkAddStopwatchTime.Stop();
}
;
}
