/// <summary> /// Update InsStore periods (delete old data and insert new data) /// </summary> /// <param name="insStoreID">InsStore Id</param> /// <param name="periods">InsStore periods</param> public void UpdatePeriods(int insStoreID, IEnumerable <Common.InsStorePeriod> periods) { bool isNewTran = _da.BeginTransaction(); try { _da.DbContext.Execute("delete from InsStorePeriods where InsStoreID = " + insStoreID.ToString()); foreach (var period in periods) { var db_InsStorePeriods = new DBModel.InsStorePeriods() { InsStoreID = insStoreID, StartDate = StorageLib.ToDbTime(period.StartDate), EndDate = StorageLib.ToDbTime(period.EndDate), IsLastDirty = period.IsLastDirty }; _da.DbContext.Insert(db_InsStorePeriods); } _da.Commit(isNewTran); } catch (Exception ex) { _da.Rollback(isNewTran); throw new Exception("Database error occurred while updating periods.", ex); } }
/// <summary> /// Update free days data (delete old free days and insert new free days) /// </summary> /// <param name="insStoreID">InsStore Id</param> /// <param name="freeDays">Free days list</param> public void UpdateFreeDays(int insStoreID, IEnumerable <DateTime> freeDays) { bool isNewTran = _da.BeginTransaction(); try { _da.DbContext.Execute("delete from InsStoreFreeDays where InsStoreID = " + insStoreID.ToString()); foreach (var day in freeDays) { var db_InsStoreFreeDays = new DBModel.InsStoreFreeDays() { InsStoreID = insStoreID, Date = StorageLib.ToDbTime(day.Date) }; _da.DbContext.Insert(db_InsStoreFreeDays); } _da.Commit(isNewTran); } catch (Exception ex) { _da.Rollback(isNewTran); throw new Exception("Database error occurred while updating free days.", ex); } }
/// <summary> /// Store price bars to InsStore /// </summary> /// <param name="insStoreID">InsStore Id</param> /// <param name="bars">Bars array</param> /// <param name="date1">First date (ignore time)</param> /// <param name="date2">Last date (ignore time)</param> /// <param name="cancel">Cancel object (use for cancel continuous operation)</param> public void InsertBars(int insStoreID, IEnumerable <DbBarHistory> bars, DateTime date1, DateTime date2, CancellationToken cancel) { if (bars == null) { return; } if (date2 == DateTime.MaxValue) { date2 = date2.AddDays(-1); } using (var db = new DaContext(_options)) { using (var trans = db.Database.BeginTransaction()) { try { var insStoreParam = new NpgsqlParameter("@InsStoreID", insStoreID); var time1Param = new NpgsqlParameter("@Time1", StorageLib.ToDbTime(date1.Date)); var time2Param = new NpgsqlParameter("@Time2", StorageLib.ToDbTime(date2.Date.AddDays(1))); db.Database.ExecuteSqlCommand("delete from barhistory where insstore_id = @InsStoreID and bar_time >= @Time1 and bar_time < @Time2", insStoreParam, time1Param, time2Param); var timeParam = new NpgsqlParameter("@Time", NpgsqlDbType.Integer); var opParam = new NpgsqlParameter("@OpenPrice", NpgsqlDbType.Integer); var cpParam = new NpgsqlParameter("@CloseDelta", NpgsqlDbType.Smallint); var hpParam = new NpgsqlParameter("@HighDelta", NpgsqlDbType.Smallint); var lpParam = new NpgsqlParameter("@LowDelta", NpgsqlDbType.Smallint); var vParam = new NpgsqlParameter("@Volume", NpgsqlDbType.Integer); foreach (var bar in bars) { if (cancel.IsCancellationRequested) { break; } timeParam.Value = bar.Time; opParam.Value = bar.OpenPrice; cpParam.Value = bar.CloseDelta; hpParam.Value = bar.HighDelta; lpParam.Value = bar.LowDelta; vParam.Value = bar.Volume; db.Database.ExecuteSqlCommand("insert into barhistory (insstore_id, bar_time, open, close_d, high_d, low_d, volume) values (@InsStoreID, @Time, @OpenPrice, @CloseDelta, @HighDelta, @LowDelta, @Volume)", insStoreParam, timeParam, opParam, cpParam, hpParam, lpParam, vParam); } db.Database.CommitTransaction(); } catch (Exception ex) { db.Database.RollbackTransaction(); throw new Exception("Database error occurred while inserting bars", ex); } } } }
/// <summary> /// Load historical data into bars /// </summary> /// <param name="bars">Bars object</param> /// <param name="insStoreID">InsStote Id</param> /// <param name="date1">First date (without time)</param> /// <param name="date2">Last date (without time)</param> /// <returns>Async task</returns> public Task LoadHistoryAsync(BarRow bars, int insStoreID, DateTime date1, DateTime date2) { if (date2 == DateTime.MaxValue) { date2 = date2.AddDays(-1); } int d1 = StorageLib.ToDbTime(date1.Date); int d2 = StorageLib.ToDbTime(date2.Date.AddDays(1)); return(Task.Run(() => { var loadedBars = _da.DbContext.Table <BarHistory>() .Where(b => b.InsStoreID == insStoreID && b.Time >= d1 && b.Time < d2) .OrderBy(b => b.Time); bars.SuspendEvents(); foreach (var bar in loadedBars) { bars.AddTick(StorageLib.ToDateTime(bar.Time), bar.OpenPrice, 0); bars.AddTick(StorageLib.ToDateTime(bar.Time), bar.HighPrice, 0); bars.AddTick(StorageLib.ToDateTime(bar.Time), bar.LowPrice, 0); bars.AddTick(StorageLib.ToDateTime(bar.Time), bar.ClosePrice, bar.Volume); } bars.CloseLastBar(); bars.ResumeEvents(); })); }
/// <summary> /// Store price bars to InsStore /// </summary> /// <param name="insStoreID">InsStore Id</param> /// <param name="bars">Bars array</param> /// <param name="date1">First date (ignore time)</param> /// <param name="date2">Last date (ignore time)</param> /// <param name="cancel">Cancel object (use for cancel continuous operation)</param> public void InsertBars(int insStoreID, IEnumerable <Bar> bars, DateTime date1, DateTime date2, CancellationToken cancel) { if (bars == null) { return; } if (date2 == DateTime.MaxValue) { date2 = date2.AddDays(-1); } bool isNewTran = _da.BeginTransaction(); try { _da.DbContext.Execute("delete from BarHistory where InsStoreID = ? and Time >= ? and Time < ?", insStoreID, StorageLib.ToDbTime(date1.Date), StorageLib.ToDbTime(date2.Date.AddDays(1))); foreach (var bar in bars) { if (cancel.IsCancellationRequested) { break; } var db_BarHistory = new DBModel.BarHistory() { InsStoreID = insStoreID, Time = StorageLib.ToDbTime(bar.Time), OpenPrice = bar.Open, ClosePrice = bar.Close, HighPrice = bar.High, LowPrice = bar.Low, Volume = bar.Volume }; _da.DbContext.Insert(db_BarHistory); } _da.Commit(isNewTran); } catch (Exception ex) { _da.Rollback(isNewTran); throw new Exception("Database error occurred while inserting bars", ex); } }
/// <summary> /// Delete price bars from db /// </summary> /// <param name="insStoreID">InsStore</param> /// <param name="date1">First date (without time)</param> /// <param name="date2">Last date (without time)</param> public void DeleteBars(int insStoreID, DateTime date1, DateTime date2) { if (date2 == DateTime.MaxValue) { date2 = date2.AddDays(-1); } try { _da.DbContext.Execute("delete from BarHistory where InsStoreID = ? and Time >= ? and Time < ?", insStoreID, StorageLib.ToDbTime(date1.Date), StorageLib.ToDbTime(date2.Date.AddDays(1))); } catch (Exception ex) { throw new Exception("Database error occurred while deleting bars", ex); } }
/// <summary> /// Insert new Cashflow object into db /// </summary> /// <param name="cashflow">Cashflow object (CashflowID = 0)</param> /// <returns>New Id (also set CashflowID)</returns> public int InsertCashflow(CommonData.Cashflow cashflow) { Cashflow db_cashflow = new Cashflow() { CashflowID = cashflow.CashflowID, Summa = cashflow.Summa, Time = StorageLib.ToDbTime(cashflow.Time), TradeID = cashflow.TradeID, Spend = (byte)cashflow.Spend, AccountID = cashflow.AccountID }; _da.DbContext.Insert(db_cashflow); cashflow.CashflowID = db_cashflow.CashflowID; return(cashflow.CashflowID); }
/// <summary> /// Update Order object /// </summary> /// <param name="order">Order object (OrderID > 0)</param> public void UpdateOrder(CommonData.Order order) { Order db_order = new Order() { OrderID = order.OrderID, Time = StorageLib.ToDbTime(order.Time), InsID = order.InsID, BuySell = (byte)order.BuySell, LotCount = order.LotCount, Price = order.Price, Status = (byte)order.Status, AccountID = order.AccountID, StopOrderID = order.StopOrderID, OrderNo = order.OrderNo }; _da.DbContext.Update(db_order); }
/// <summary> /// Insert Trade object /// </summary> /// <param name="trade">Trade object with TradeID = 0</param> /// <returns>New TradeID value and set it to object</returns> public int InsertTrade(CommonData.Trade trade) { Trade db_trade = new Trade() { TradeID = trade.TradeID, OrderID = trade.OrderID, Time = StorageLib.ToDbTime(trade.Time), InsID = trade.InsID, BuySell = (byte)trade.BuySell, LotCount = trade.LotCount, Price = trade.Price, AccountID = trade.AccountID, Comm = trade.Comm, TradeNo = trade.TradeNo }; _da.DbContext.Insert(db_trade); trade.TradeID = db_trade.TradeID; return(trade.TradeID); }
/// <summary> /// Update StopOrder object /// </summary> /// <param name="stopOrder">StopOrder with StopOrderID > 0</param> public void UpdateStopOrder(CommonData.StopOrder stopOrder) { StopOrder db_stopOrder = new StopOrder() { StopOrderID = stopOrder.StopOrderID, Time = StorageLib.ToDbTime(stopOrder.Time), InsID = stopOrder.InsID, BuySell = (byte)stopOrder.BuySell, StopType = (byte)stopOrder.StopType, EndTime = StorageLib.ToDbTime(stopOrder.EndTime), AlertPrice = stopOrder.AlertPrice, Price = stopOrder.Price, LotCount = stopOrder.LotCount, Status = (byte)stopOrder.Status, AccountID = stopOrder.AccountID, CompleteTime = StorageLib.ToDbTime(stopOrder.CompleteTime), StopOrderNo = stopOrder.StopOrderNo }; _da.DbContext.Update(db_stopOrder); }
/// <summary> /// Get historical data /// </summary> /// <param name="insStoreID">InsStote Id</param> /// <param name="date1">First date (without time)</param> /// <param name="date2">Last date (without time)</param> /// <returns>Async task</returns> public Task <IEnumerable <DbBarHistory> > GetHistoryAsync(int insStoreID, DateTime date1, DateTime date2) { if (date2 == DateTime.MaxValue) { date2 = date2.AddDays(-1); } return(Task.Run <IEnumerable <DbBarHistory> >(() => { int d1 = StorageLib.ToDbTime(date1.Date); int d2 = StorageLib.ToDbTime(date2.AddDays(1).Date); using (var db = new DaContext(_options)) { var list = db.DbBarHistory .Where(r => r.InsStoreID == insStoreID && r.Time >= d1 && r.Time < d2) .OrderBy(r => r.Time) .ToList(); return list; } })); }
/// <summary> /// Get Trades by account, date and id. /// </summary> /// <param name="accountID">Account</param> /// <param name="dateFrom">From date-time (or null)</param> /// <param name="idFrom">From tradeId (or zero)</param> /// <returns>Trade list</returns> public IEnumerable <CommonData.Trade> GetTrades(int accountID, DateTime?dateFrom, int idFrom) { List <CommonData.Trade> trades = new List <CommonData.Trade>(); List <Trade> db_trades; var db_alltrades = _da.DbContext.Table <Trade>().Where(r => r.AccountID == accountID && r.TradeID >= idFrom); if (dateFrom != null) { int df = StorageLib.ToDbTime(dateFrom.Value); db_trades = db_alltrades.Where(r => r.Time >= df).ToList(); } else { db_trades = db_alltrades.ToList(); } foreach (var db_trade in db_trades) { var trade = new CommonData.Trade() { TradeID = db_trade.TradeID, OrderID = db_trade.OrderID, Time = StorageLib.ToDateTime(db_trade.Time), InsID = db_trade.InsID, BuySell = (BuySell)db_trade.BuySell, LotCount = db_trade.LotCount, Price = db_trade.Price, AccountID = db_trade.AccountID, Comm = db_trade.Comm, TradeNo = db_trade.TradeNo }; trades.Add(trade); } return(trades); }
/// <summary> /// Delete price bars from db /// </summary> /// <param name="insStoreID">InsStore</param> /// <param name="date1">First date (without time)</param> /// <param name="date2">Last date (without time)</param> public void DeleteBars(int insStoreID, DateTime date1, DateTime date2) { if (date2 == DateTime.MaxValue) { date2 = date2.AddDays(-1); } using (var db = new DaContext(_options)) { try { var insStoreParam = new NpgsqlParameter("@InsStoreID", insStoreID); var time1Param = new NpgsqlParameter("@Time1", StorageLib.ToDbTime(date1.Date)); var time2Param = new NpgsqlParameter("@Time2", StorageLib.ToDbTime(date2.Date.AddDays(1))); db.Database.ExecuteSqlCommand("delete from barhistory where insstore_id = @InsStoreID and bar_time >= @Time1 and bar_time < @Time2", insStoreParam, time1Param, time2Param); } catch (Exception ex) { throw new Exception("Database error occurred while deleting bars", ex); } } }