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