/// <summary>
/// Get InsStore by instrument and timeframe
/// </summary>
/// <param name="insID"></param>
/// <param name="tf"></param>
/// <returns></returns>
public CommonData.InsStore GetInsStore(int insID, Timeframes tf)
{
using (var db = new DaContext(_options))
{
return(db.InsStore.FirstOrDefault(s => s.InsID == insID && s.Tf == tf));
}
}
/// <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>
/// Get position trades
/// </summary>
/// <param name="posIDs">Position ID list</param>
/// <returns>Position trades list</returns>
public IEnumerable <PosTrade> GetPosTrades(IEnumerable <int> posIDs)
{
using (var db = new DaContext(_options))
{
return(db.PosTrade.Where(r => posIDs.Contains(r.PosID)).ToList());
}
}
/// <summary>
/// Get TestConfig by Id
/// </summary>
/// <param name="testConfigID">Id</param>
/// <returns>TestConfig</returns>
public DbTestConfig GetTestConfigByID(int testConfigID)
{
using (var db = new DaContext(_options))
{
return(db.TestConfig.Find(testConfigID));
}
}
/// <summary>
/// Get all tickSources
/// </summary>
/// <returns>TickSources list</returns>
public IEnumerable <DbTickSource> GetTickSources()
{
using (var db = new DaContext(_options))
{
return(db.TickSource.ToList());
}
}
/// <summary>
/// Get Chart object by Id
/// </summary>
/// <param name="chartId"></param>
/// <returns></returns>
public CommonData.Chart GetChartByID(int chartId)
{
DbChart chart = null;
using (var db = new DaContext(_options))
{
chart = db.Chart.Find(chartId);
if (chart == null)
{
return(null);
}
}
var xd = new XDocument();
if (!string.IsNullOrEmpty(chart.Data))
{
xd = XDocument.Parse(chart.Data);
}
return(new CommonData.Chart()
{
ChartID = chart.ChartID,
InsID = chart.InsID,
Tf = (Timeframes)chart.Tf,
AccountID = chart.AccountID,
XmlData = xd
});
}
public IEnumerable <Series> GetSeries(int accountID)
{
using (var db = new DaContext(_options))
{
return(db.Series.Where(r => r.AccountID == accountID).ToList());
}
}
/// <summary>
/// Get all instrums
/// </summary>
/// <returns></returns>
public IEnumerable <Instrum> GetInstrums()
{
using (var db = new DaContext(_options))
{
return(db.Instrum.ToList());
}
}
/// <summary>
/// Delete all object replications
/// </summary>
/// <param name="replObject">Replication object</param>
public void DeleteReplications(ReplObjects replObject)
{
using (var db = new DaContext(_options))
{
db.Database.ExecuteSqlCommand("delete from replication where repl_object = " + ((int)replObject).ToString());
}
}
/// <summary>
/// Get all users
/// </summary>
/// <returns>User list</returns>
public IEnumerable <User> GetUsers()
{
using (var db = new DaContext(_options))
{
return(db.User.ToList());
}
}
/// <summary>
/// Insert replications
/// </summary>
/// <param name="replObject">Replication object</param>
/// <param name="rid_lid">Replications (key=remoteId, val=localId)</param>
public void InsertReplications(ReplObjects replObject, Dictionary <int, int> rid_lid)
{
if (rid_lid == null)
{
throw new ArgumentNullException();
}
if (!rid_lid.Any())
{
return;
}
using (var db = new DaContext(_options))
{
foreach (var rid in rid_lid.Keys)
{
int lid = rid_lid[rid];
var isFound = db.Replication.Any(r => r.ReplObject == replObject && r.LocalID == lid && r.RemoteID == rid);
if (!isFound)
{
db.Replication.Add(new Replication()
{
LocalID = lid, RemoteID = rid, ReplObject = replObject
});
}
}
db.SaveChanges();
}
}
/// <summary>
/// Get TickSource by Id
/// </summary>
/// <param name="tickSourceID">Id</param>
/// <returns>TickSiurce</returns>
public DbTickSource GetTickSourceByID(int tickSourceID)
{
using (var db = new DaContext(_options))
{
return(db.TickSource.Find(tickSourceID));
}
}
/// <summary>
/// Get Chart object by account, instrument and timeframe (single chart)
/// </summary>
/// <param name="accountID"></param>
/// <param name="insID"></param>
/// <param name="tf"></param>
/// <returns></returns>
public CommonData.Chart GetChart(int accountID, int insID, Timeframes tf)
{
return(null); //?????????????????????
DbChart chart = null;
using (var db = new DaContext(_options))
{
chart = db.Chart.FirstOrDefault(r => r.AccountID == accountID && r.InsID == insID && r.Tf == (byte)tf);
if (chart == null)
{
return(null);
}
}
var xd = new XDocument();
if (!string.IsNullOrEmpty(chart.Data))
{
xd = XDocument.Parse(chart.Data);
}
return(new CommonData.Chart()
{
ChartID = chart.ChartID,
InsID = chart.InsID,
Tf = (Timeframes)chart.Tf,
AccountID = chart.AccountID,
XmlData = xd
});
}
/// <summary>
/// Get all accounts
/// </summary>
/// <returns>Accounts list</returns>
public IEnumerable <Account> GetAccounts()
{
using (var db = new DaContext(_options))
{
return(db.Account.ToList());
}
}
/// <summary>
/// Create stop-order
/// </summary>
/// <param name="accountID">Account id</param>
/// <param name="time">Date and time</param>
/// <param name="insID">Instrum id</param>
/// <param name="bs">Buy or sell</param>
/// <param name="st">StopLoss or TakeProfit</param>
/// <param name="endTime">End time or null</param>
/// <param name="alertPrice">Alert price</param>
/// <param name="price">Order price or null</param>
/// <param name="lotCount">Lots count</param>
/// <param name="status">Stop order status</param>
/// <param name="completeTime">Complete date and time</param>
/// <param name="stopOrderNo">Global stop-order number</param>
/// <returns>New stop-order</returns>
public StopOrder CreateStopOrder(int accountID, DateTime time, int insID, BuySell bs, StopOrderType st, DateTime?endTime, decimal alertPrice, decimal?price, int lotCount,
StopOrderStatus status, DateTime?completeTime, long stopOrderNo)
{
StopOrder stopOrder = new StopOrder()
{
AccountID = accountID,
Time = time,
InsID = insID,
BuySell = bs,
StopType = st,
EndTime = endTime,
AlertPrice = alertPrice,
Price = price,
LotCount = lotCount,
Status = status,
CompleteTime = completeTime,
StopOrderNo = stopOrderNo
};
using (var db = new DaContext(_options))
{
db.StopOrder.Add(stopOrder);
db.SaveChanges();
}
return(stopOrder);
}
/// <summary>
/// Get Holdings by account Id
/// </summary>
/// <param name="accountID">account Id</param>
/// <returns>Holdings list</returns>
public IEnumerable <Holding> GetHoldings(int accountID)
{
using (var db = new DaContext(_options))
{
return(db.Holding.Where(r => r.AccountID == accountID).ToList());
}
}
/// <summary>
/// Get StopOrder list by IDs
/// </summary>
/// <param name="ids">StopOrderID list</param>
/// <returns>StopOrder list</returns>
public IEnumerable <StopOrder> GetStopOrders(IEnumerable <int> ids)
{
using (var db = new DaContext(_options))
{
return(db.StopOrder.Where(r => ids.Contains(r.StopOrderID)).ToList());
}
}
/// <summary>
/// Get cash by account Id
/// </summary>
/// <param name="accountID">account Id</param>
/// <returns>Cash</returns>
public Cash GetCash(int accountID)
{
using (var db = new DaContext(_options))
{
return(db.Cash.FirstOrDefault(c => c.AccountID == accountID));
}
}
/// <summary>
/// Get all replications by objType
/// </summary>
/// <returns>TestConfigs list</returns>
public IEnumerable <Replication> GetReplications(ReplObjects objType)
{
using (var db = new DaContext(_options))
{
return(db.Replication.Where(r => r.ReplObject == objType).ToList());
}
}
/// <summary>
/// Delete all account data by accountID (trades, stoporders, orders, holdings, cashes)
/// Account not delete
/// </summary>
/// <param name="accountID">AccountID</param>
public void DeleteAccountData(int accountID)
{
using (var db = new DaContext(_options))
{
using (var trans = db.Database.BeginTransaction())
{
try
{
db.Database.ExecuteSqlCommand(string.Format("delete from postrade where trade_id in (select trade_id from trade where account_id = {0})", accountID.ToString()));
db.Database.ExecuteSqlCommand(string.Format("delete from postrade where pos_id in (select pos_id from positions where account_id = {0})", accountID.ToString()));
db.Database.ExecuteSqlCommand("delete from positions where account_id = " + accountID.ToString());
db.Database.ExecuteSqlCommand("delete from trade where account_id = " + accountID.ToString());
db.Database.ExecuteSqlCommand("delete from stoporder where account_id = " + accountID.ToString());
db.Database.ExecuteSqlCommand("delete from orders where account_id = " + accountID.ToString());
db.Database.ExecuteSqlCommand("delete from holding where account_id = " + accountID.ToString());
db.Database.ExecuteSqlCommand("delete from cash where account_id = " + accountID.ToString());
db.Database.ExecuteSqlCommand("delete from seriesvalue where series_id in (select series_id from series where account_id = " + accountID.ToString() + ")");
db.Database.ExecuteSqlCommand("delete from series where account_id = " + accountID.ToString());
db.Database.CommitTransaction();
}
catch (Exception ex)
{
db.Database.RollbackTransaction();
throw new Exception("Database error occurred while deleting account data", ex);
}
}
}
}
/// <summary>
/// Get all TestConfigs
/// </summary>
/// <returns>TestConfigs list</returns>
public IEnumerable <DbTestConfig> GetTestConfigs()
{
using (var db = new DaContext(_options))
{
return(db.TestConfig.ToList());
}
}
/// <summary>
/// Update tickSource
/// </summary>
/// <param name="tickSource">TickSource</param>
public void UpdateTickSource(DbTickSource tickSource)
{
using (var db = new DaContext(_options))
{
db.Update(tickSource);
db.SaveChanges();
}
}
/// <summary>
/// Update StopOrder object
/// </summary>
/// <param name="stopOrder">StopOrder object (StopOrderID > 0)</param>
public void UpdateStopOrder(StopOrder stopOrder)
{
using (var db = new DaContext(_options))
{
db.Update(stopOrder);
db.SaveChanges();
}
}
/// <summary>
/// Update Order object
/// </summary>
/// <param name="order">Order object (OrderID > 0)</param>
public void UpdateOrder(Order order)
{
using (var db = new DaContext(_options))
{
db.Update(order);
db.SaveChanges();
}
}
/// <summary>
/// Update Holding object
/// </summary>
/// <param name="holding">Holding object</param>
public void UpdateHolding(Holding holding)
{
using (var db = new DaContext(_options))
{
db.Update(holding);
db.SaveChanges();
}
}
/// <summary>
/// Update cash object
/// </summary>
/// <param name="cash">Cash object (CashID > 0)</param>
public void UpdateCash(CommonData.Cash cash)
{
using (var db = new DaContext(_options))
{
db.Update(cash);
db.SaveChanges();
}
}
/// <summary>
/// Update position
/// </summary>
/// <param name="pos">Position object</param>
public void UpdatePosition(Position pos)
{
using (var db = new DaContext(_options))
{
db.Update <Position>(pos);
db.SaveChanges();
}
}
/// <summary>
/// Update user data
/// </summary>
/// <param name="user">User object</param>
public void UpdateUser(User user)
{
using (var db = new DaContext(_options))
{
db.Update <User>(user);
db.SaveChanges();
}
}
/// <summary>
/// Update account data
/// </summary>
/// <param name="account">Account data</param>
public void UpdateAccount(Account account)
{
using (var db = new DaContext(_options))
{
db.Update(account);
db.SaveChanges();
}
}
/// <summary>
/// Update repository object
/// </summary>
/// <param name="ro"></param>
public void Update(CommonData.ReposObject ro)
{
using (var db = new DaContext(_options))
{
db.Update <ReposObject>(ro);
db.SaveChanges();
}
}
