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