public List <ReportField> GetReport(string userName, string strategyName, DateTime startDate, DateTime endDate)
{
var result = new List <ReportField>();
using (var aConnection = new SqlConnection(_connectionString))
{
var cmd = new SqlCommand(
"SELECT o.Symbol, o.Type, o.Status, o.TIF, o.ExecutedQuantity, s.SignalName, s.Date as SignalDate,"
+ " s.DataBaseEntry as SignalDBDate, o.Date, o.FilledDate, o.DataBaseEntry as OrderDBDate"
+ " FROM[dbo].[Orders] o"
+ " LEFT JOIN[dbo].[Signals] s ON o.SignalID = s.SignalID"
+ " WHERE s.UserLogin = @userLogin AND s.SignalName = @signalName AND s.Date BETWEEN @startDate AND @endDate", aConnection);
cmd.Parameters.AddWithValue("userLogin", userName);
cmd.Parameters.AddWithValue("signalName", strategyName);
cmd.Parameters.AddWithValue("startDate", startDate);
cmd.Parameters.AddWithValue("endDate", endDate);
SqlTransaction transaction = null;
try
{
aConnection.Open();
transaction =
aConnection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted, "Orders Select");
cmd.Transaction = transaction;
using (var reader = cmd.ExecuteReader())
{
if (!reader.HasRows)
{
return(result);
}
while (reader.Read())
{
try
{
var qty = (decimal)reader["ExecutedQuantity"];
var field = new ReportField
{
Symbol = (string)reader["Symbol"],
TradeType = DBConverters.ParseOrderType((string)reader["Type"]),
Status = DBConverters.ParseOrderStatus((string)reader["Status"]),
TimeInForce = DBConverters.ParseTif((string)reader["TIF"]),
Quantity = qty,
Side = qty > 0 ? Side.Buy : Side.Sell,
SignalName = (string)reader["SignalName"],
SignalGeneratedDateTime = (DateTime)reader["SignalDate"],
OrderFilledDate = (DateTime)reader["FilledDate"],
OrderGeneratedDate = (DateTime)reader["Date"],
DBOrderEntryDate = (DateTime)reader["OrderDBDate"],
DBSignalEntryDate = (DateTime)reader["SignalDBDate"]
};
field.CalculateDiff();
result.Add(field);
}
catch (Exception ex)
{
Logger.Error("Failed to parse report field", ex);
}
}
}
transaction.Commit();
}
catch (Exception e)
{
Logger.Error("Failed to load report", e);
transaction?.Rollback();
return(result);
}
}
return(result);
}
public IEnumerable <Order> GetOrderHistory(AccountInfo info, int count, int skip)
{
if (skip < 0 || count < 1)
{
return(new List <Order>(0));
}
var result = new List <Order>();
using (var aConnection = new SqlConnection(_connectionString))
{
//select N latest records of each symbol
var cmd = new SqlCommand("WITH TOPRECORDS AS ( "
+ "SELECT s.[Symbol], h.[Quantity], h.[ExecutedQuantity], h.[Status], h.[OrderId], h.[Price], "
+ "h.[Type], h.[TIF], h.[Date], h.[BrokerID], h.[OpeningQuantity], h.[ClosingQuantity], h.[Origin], ROW_NUMBER() "
+ "OVER(PARTITION BY h.[Symbol], h.[DataFeed] ORDER BY [Date] DESC) AS RowNumber FROM [Orders] AS h "
+ "INNER JOIN[dbo].[Securities] AS s ON s.Symbol = h.Symbol AND s.DataFeed = h.DataFeed "
+ "WHERE h.[UserName] = @userName AND h.[AccountId] = @account AND h.[BrokerName] = @broker) "
+ "SELECT * FROM TOPRECORDS WHERE RowNumber > @start AND RowNumber <= @end", aConnection);
cmd.Parameters.AddWithValue("start", skip);
cmd.Parameters.AddWithValue("end", skip + count);
cmd.Parameters.AddWithValue("userName", info.UserName);
cmd.Parameters.AddWithValue("account", info.ID);
cmd.Parameters.AddWithValue("broker", info.BrokerName);
SqlTransaction transaction = null;
try
{
aConnection.Open();
transaction = aConnection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted, "Orders Select");
cmd.Transaction = transaction;
using (var reader = cmd.ExecuteReader())
{
if (!reader.HasRows)
{
return(result);
}
while (reader.Read())
{
try
{
var qty = (decimal)reader["Quantity"];
var execQty = (decimal)reader["ExecutedQuantity"];
var status = DBConverters.ParseOrderStatus((string)reader["Status"]);
var order = new Order((string)reader["OrderId"], (string)reader["Symbol"])
{
BrokerID = (string)reader["BrokerID"],
AvgFillPrice = status == Status.Filled ? (decimal)reader["Price"] : 0,
Price = status == Status.Cancelled ? (decimal)reader["Price"] : 0,
OrderSide = qty > 0 ? Side.Buy : Side.Sell,
OrderType = DBConverters.ParseOrderType((string)reader["Type"]),
BrokerName = info.BrokerName,
AccountId = info.ID,
TimeInForce = DBConverters.ParseTif((string)reader["TIF"]),
OpenDate = (DateTime)reader["Date"],
CancelledQuantity = status == Status.Cancelled ? Math.Abs(execQty) : 0,
FilledQuantity = status == Status.Filled ? Math.Abs(execQty) : 0,
OpeningQty = status == Status.Filled ? (decimal)reader["OpeningQuantity"] : 0,
ClosingQty = status == Status.Filled ? (decimal)reader["ClosingQuantity"] : 0,
Origin = reader["Origin"] as string
};
if (status == Status.Cancelled)
{
order.CancelledQuantity = Math.Abs(qty);
}
else
{
order.FilledQuantity = Math.Abs(qty);
}
result.Add(order);
}
catch (Exception ex)
{
Logger.Error("Failed to parse historical order", ex);
}
}
}
transaction.Commit();
}
catch (Exception e)
{
Logger.Error("Failed to load order history", e);
transaction?.Rollback();
return(result);
}
}
return(result);
}
