/// <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);
}
}
}