private void SetDeliveryStatus(OrderGeneralDataHelper order, DateTime?shipCh) { if (order.OrderDate == null) { order.ShippedStatus = DeliveryStatus.NotSent; } else if (shipCh == null) { order.ShippedStatus = DeliveryStatus.OnWay; } else { order.ShippedStatus = DeliveryStatus.Done; } }
public List <OrderGeneralDataHelper> GetOrdersGeneralData(int offset = 0, int recordCount = 12) { var orders = new List <OrderGeneralDataHelper>(); try { using (var connection = this.providerFactory.CreateConnection()) { connection.ConnectionString = this.connectionString; connection.Open(); var command = (SqlCommand)connection.CreateCommand(); if (recordCount <= 0) { command.Parameters.AddWithValue("@recordCount", 12); } else { command.Parameters.AddWithValue("@recordCount", recordCount); } if (offset <= 0) { command.Parameters.AddWithValue("@offset", 0); } else { command.Parameters.AddWithValue("@offset", offset); } command.CommandText = "SELECT ord.OrderID, ( select CONCAT(cust.CompanyName ,' (',cust.ContactName,')') from Customers cust " + "where cust.CustomerID = ord.CustomerID) as 'Customer', OrderDate, (select CONVERT(money, FORMAT(SUM((UnitPrice - (UnitPrice * Discount)) * Quantity) + ord.Freight, 'N', 'en-us'),1) " + "from [Order Details] det where ord.OrderID = det.OrderID) AS 'Cost', " + "ShippedDate " + "FROM[dbo].[Orders] ord ORDER BY OrderDate desc OFFSET @offset ROWS FETCH NEXT @recordCount ROWS ONLY"; using (IDataReader reader = command.ExecuteReader()) { while (reader.Read()) { OrderGeneralDataHelper order = new OrderGeneralDataHelper(); order.OrderID = (int)reader[OrdersFields.OrderID]; order.Customer = reader["Customer"] as string; order.Cost = reader["Cost"] as decimal?; order.OrderDate = reader["OrderDate"] as DateTime?; var shipCh = reader["ShippedDate"] as DateTime?; this.SetDeliveryStatus(order, shipCh); orders.Add(order); } } } return(orders); } catch (SqlException exc) { // ... this.UnresolvedExceptions.Add(exc); return(null); } catch (InvalidCastException exc) { // ... this.UnresolvedExceptions.Add(exc); return(null); } catch (Exception exc) { this.UnresolvedExceptions.Add(exc); return(null); } }