public Dictionary<DateTime, double> GetSalesReport(OrderSearchCondition orderSearchCondition)
{
Dictionary<DateTime, double> results = new Dictionary<DateTime, double>();
try
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
EntityConnection entityConn = (EntityConnection)ctx.Connection;
using (SqlConnection sqlConn = (SqlConnection)entityConn.StoreConnection)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConn;
var query = new StringBuilder("SELECT DATEADD(dd, DATEDIFF(dd, 0, o.OrderDate), 0) as odate,");
query.Append(" COALESCE(SUM(o.BillAmount),0) AS TotalAmount FROM Orders AS o WHERE 1=1");
if (orderSearchCondition.ToOrderDate == orderSearchCondition.FromOrderDate)
{
query.Append(" AND DATEADD(dd, DATEDIFF(dd, 0, o.OrderDate), 0) = @date");
cmd.Parameters.AddWithValue("@date", orderSearchCondition.FromOrderDate);
}
else
{
query.Append(" AND DATEADD(dd, DATEDIFF(dd, 0, o.OrderDate), 0) >= @fromdate");
query.Append(" AND DATEADD(dd, DATEDIFF(dd, 0, o.OrderDate), 0) <= @todate");
cmd.Parameters.AddWithValue("@fromdate", orderSearchCondition.FromOrderDate);
cmd.Parameters.AddWithValue("@todate", orderSearchCondition.ToOrderDate);
}
query.Append(" GROUP BY DATEADD(dd, DATEDIFF(dd, 0, o.OrderDate), 0) ORDER BY odate ASC");
cmd.CommandText = query.ToString();
cmd.CommandType = CommandType.Text;
if (sqlConn.State != ConnectionState.Open)
{
sqlConn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
results.Add(reader.GetDateTime(0), reader.GetDouble(1));
}
}
}
}
}
catch (Exception ex)
{
LogService.Error("Error while calculating sales report", ex);
throw new ArgumentException("Error while calculating sales report", ex);
}
return results;
}
public Collection<Order> Search(OrderSearchCondition condition)
{
try
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
ctx.ContextOptions.LazyLoadingEnabled = false;
ctx.Orders.MergeOption = MergeOption.NoTracking;
var items = ctx.Orders.Include("Customer")
.Include("Customer.ContactDetail")
.Include("Payments")
.Include("Employee").Include("Employee.ContactDetail")
.Include("OrderDetails").Where(x => x.Status == true);
if (condition.OrderNo > 0)
{
items = items.Where(x => x.BillNo == condition.OrderNo);
}
else
{
if (condition.CustomerId != Guid.Empty)
{
items = items.Where(x => x.CustomerId == condition.CustomerId);
}
else
{
if (!string.IsNullOrEmpty(condition.CustomerMobile))
{
items = items.Where(x => x.Customer.ContactDetail.Mobile.Contains(condition.CustomerMobile));
}
if (!string.IsNullOrEmpty(condition.CustomerName))
{
items = items.Where(x => x.Customer.ContactDetail.ContactName.Contains(condition.CustomerName));
}
if (!string.IsNullOrEmpty(condition.CustomerNo))
{
items = items.Where(x => x.Customer.SSN.Contains(condition.CustomerNo));
}
}
if (condition.EmployeeId != Guid.Empty)
{
items = items.Where(x => x.EmployeeId == condition.EmployeeId);
}
if (condition.MinAmount > 0 && condition.MaxAmount > 0)
{
items = items.Where(x => x.BillAmount >= condition.MinAmount && x.BillAmount <= condition.MaxAmount);
}
else if (condition.MinAmount <= 0 && condition.MaxAmount > 0)
{
items = items.Where(x => x.BillAmount <= condition.MaxAmount);
}
else if (condition.MinAmount > 0 && condition.MaxAmount <= 0)
{
items = items.Where(x => x.BillAmount >= condition.MinAmount);
}
if ((condition.FromOrderDate != DateTime.MinValue && condition.ToOrderDate != DateTime.MinValue)
&& (condition.FromOrderDate == condition.ToOrderDate))
{
items = items.Where(x => EntityFunctions.TruncateTime(x.OrderDate) == condition.FromOrderDate);
}
else if (condition.FromOrderDate != DateTime.MinValue && condition.ToOrderDate != DateTime.MinValue)
{
items = items.Where(x => EntityFunctions.TruncateTime(x.OrderDate) > condition.FromOrderDate && EntityFunctions.TruncateTime(x.OrderDate) <= condition.ToOrderDate);
}
else if (condition.FromOrderDate != DateTime.MinValue && condition.ToOrderDate >= DateTime.MinValue)
{
items = items.Where(x => EntityFunctions.TruncateTime(x.OrderDate) <= condition.ToOrderDate);
}
else if (condition.FromOrderDate >= DateTime.MinValue && condition.ToOrderDate != DateTime.MinValue)
{
items = items.Where(x => EntityFunctions.TruncateTime(x.OrderDate) >= condition.FromOrderDate);
}
}
items = items.OrderByDescending(x => x.BillNo).OrderByDescending(x => x.OrderDate);
if (condition.PageNo > 0 && condition.PageSize > 0)
{
items = items.Skip((condition.PageNo - 1) * condition.PageSize).Take(condition.PageSize);
}
else
{
items.Take(500);
}
return new Collection<Order>(items.ToList());
}
}
catch (Exception ex)
{
LogService.Error("Error while searching orders", ex);
throw new ArgumentException("Error while searching orders", ex);
}
}