public double GetCusomerTotalAmount(Guid customerId, DateTime fromDate)
{
if (customerId == Guid.Empty)
throw new ArgumentNullException("customerId");
try
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
var queryString = new StringBuilder("SELECT COALESCE(SUM(op.PaidAmount),0) FROM Payments op");
queryString.Append(" INNER JOIN Orders o on o.Id = op.OrderId AND o.CustomerId = {0}")
.Append(" AND (DATEADD(dd, DATEDIFF(dd, 0, o.OrderDate), 0) >= {1} AND DATEADD(dd, DATEDIFF(dd, 0, o.OrderDate), 0) <= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))");
var query = ctx.ExecuteStoreQuery<double>(queryString.ToString(), customerId, fromDate);
return query.First();
}
}
catch (Exception ex)
{
LogService.Error("Error while calculating customer order amount", ex);
throw new ArgumentException("Error while calculating customer order amount", ex);
}
}
public int GetTotalOrders(DateTime fromDate, DateTime toDate)
{
try
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
var queryString = new StringBuilder("SELECT COUNT(Id) FROM Orders o");
queryString.Append(" WHERE (DATEADD(dd, DATEDIFF(dd, 0, o.OrderDate), 0) >= {0} AND DATEADD(dd, DATEDIFF(dd, 0, o.OrderDate), 0) <= {1})");
var query = ctx.ExecuteStoreQuery<int>(queryString.ToString(), fromDate, toDate);
return query.First();
}
}
catch (Exception ex)
{
LogService.Error("Error while calculating total orders", ex);
throw new ArgumentException("Error while calculating total orders", ex);
}
}
