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