public void Delete(Guid id)
{
if (id.Equals(Guid.Empty))
throw new ArgumentException("Employee id cannot be empty!");
try
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
var entity = GetEmployeeById(id);
if (entity != null)
{
ctx.Employees.Attach(entity);
ctx.Employees.DeleteObject(entity);
ctx.SaveChanges();
}
}
}
catch (Exception ex)
{
LogService.Error("Error while deleting Employee", ex);
throw ex;
}
}
public Guid Add(Customer entity)
{
if (string.IsNullOrEmpty(entity.SSN))
throw new ArgumentException("Customer number cannot be empty!");
if (string.IsNullOrEmpty(entity.ContactDetail.ContactName))
throw new ArgumentException("Customer name cannot be empty!");
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
if (CheckDuplicate(entity.Id, entity.SSN, ctx))
{
throw new ArgumentException("Duplicate Customer number found!");
}
try
{
ctx.Customers.AddObject(entity);
ctx.SaveChanges();
return entity.Id;
}
catch (Exception ex)
{
LogService.Error("Error while adding customer", ex);
throw new ArgumentException("Error while adding new customer!");
}
}
}
public Guid Add(Order entity)
{
if (entity.BillNo <= 0)
throw new ArgumentException("Order No cannot be empty!");
if (entity.CustomerId == Guid.Empty)
throw new ArgumentException("Order Customer cannot be empty!");
if (entity.EmployeeId == Guid.Empty)
throw new ArgumentException("Order Employee cannot be empty!");
if (CheckDuplicateBillNo(entity.Id, entity.BillNo))
throw new ArgumentException("Duplicate Order No found!");
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
try
{
ctx.Orders.MergeOption = MergeOption.NoTracking;
ctx.Orders.AddObject(entity);
ctx.SaveChanges();
var id = entity.Id;
ctx.Detach(entity);
return id;
}
catch (Exception ex)
{
LogService.Error("Error while adding order", ex);
throw new ArgumentException("Error while adding new order!", ex);
}
}
}
public Purchase GetPurchaseById(Guid id)
{
if (id == Guid.Empty)
throw new ArgumentNullException("id");
try
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
ctx.Purchases.MergeOption = MergeOption.NoTracking;
ctx.Suppliers.MergeOption = MergeOption.NoTracking;
ctx.ContactDetails.MergeOption = MergeOption.NoTracking;
ctx.PurchasePayments.MergeOption = MergeOption.NoTracking;
ctx.Employees.MergeOption = MergeOption.NoTracking;
ctx.PurchaseDetails.MergeOption = MergeOption.NoTracking;
ctx.Purchases.MergeOption = MergeOption.NoTracking;
var entity = ctx.Purchases.Include("Supplier").Include("Supplier.ContactDetail")
.Include("PurchaseDetails")
.Include("PurchasePayments")
.Include("Employee").Include("Employee.ContactDetail").SingleOrDefault(x => x.Id.Equals(id));
return entity;
}
}
catch (Exception ex)
{
LogService.Error("Error while fetching Purchase", ex);
throw new ArgumentException("Error while fetching Purchase", ex);
}
}
public Guid Add(Purchase entity)
{
if (string.IsNullOrEmpty(entity.BillNo))
throw new ArgumentException("Purchase No cannot be empty!");
if (entity.SupplierId == Guid.Empty)
throw new ArgumentException("Purchase supplier cannot be empty!");
if (entity.EmployeeId == Guid.Empty)
throw new ArgumentException("Purchase Employee cannot be empty!");
if (CheckDuplicateBillNo(entity.Id, entity.BillNo))
throw new ArgumentException("Duplicate Purchase No found!");
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
try
{
ctx.Purchases.MergeOption = MergeOption.NoTracking;
ctx.Purchases.AddObject(entity);
ctx.SaveChanges();
var id = entity.Id;
ctx.Detach(entity);
return id;
}
catch (Exception ex)
{
LogService.Error("Error while adding Purchase", ex);
throw new ArgumentException("Error while adding new Purchase!", ex);
}
}
}
public Guid Add(Setting entity)
{
using (var ctx = new OpenPOSDbEntities())
{
ctx.Settings.AddObject(entity);
ctx.SaveChanges();
return entity.Id;
}
}
public Setting GetSettingById(Guid id)
{
using (var ctx = new OpenPOSDbEntities())
{
ctx.ContextOptions.LazyLoadingEnabled = false;
ctx.Settings.MergeOption = MergeOption.NoTracking;
return ctx.Settings.Where(x => x.Id == id).FirstOrDefault();
}
}
public ICollection<Setting> GetSettings()
{
using (var ctx = new OpenPOSDbEntities())
{
ctx.ContextOptions.LazyLoadingEnabled = false;
ctx.Settings.MergeOption = MergeOption.NoTracking;
return ctx.Settings.ToList();
}
}
public ICollection<Setting> GetSettingsByCategory(string category)
{
using (var ctx = new OpenPOSDbEntities())
{
ctx.ContextOptions.LazyLoadingEnabled = false;
ctx.Settings.MergeOption = MergeOption.NoTracking;
return ctx.Settings.Where(x => x.Category == category).ToList();
}
}
public ICollection<Employee> Search(EmployeeSearchCondition condition)
{
try
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
ctx.ContextOptions.LazyLoadingEnabled = false;
ctx.Employees.MergeOption = MergeOption.NoTracking;
ctx.ContactDetails.MergeOption = MergeOption.NoTracking;
var items = ctx.Employees.Include("ContactDetail").Where(x => x.Status == true);
if (!string.IsNullOrEmpty(condition.Mobile)
&& !string.IsNullOrEmpty(condition.Email)
&& !string.IsNullOrEmpty(condition.Email))
{
items = items.Where(x => x.ContactDetail.ContactName.Contains(condition.Name)
|| x.ContactDetail.Mobile.Contains(condition.Mobile)
|| x.ContactDetail.Email.Contains(condition.Email));
}
else
{
if (!string.IsNullOrEmpty(condition.Name))
{
items = items.Where(x => x.ContactDetail.ContactName.Contains(condition.Name));
}
if (!string.IsNullOrEmpty(condition.Mobile))
{
items = items.Where(x => x.ContactDetail.Mobile.Contains(condition.Mobile));
}
if (!string.IsNullOrEmpty(condition.Email))
{
items = items.Where(x => x.ContactDetail.Email.Contains(condition.Email));
}
}
items = items.OrderBy(x => x.ContactDetail.ContactName);
if (condition.PageNo > 0 && condition.PageSize > 0)
{
items = items.Skip((condition.PageNo - 1) * condition.PageSize).Take(condition.PageSize);
}
return new Collection<Employee>(items.ToList());
}
}
catch (Exception ex)
{
LogService.Error("Error while searching employees", ex);
throw ex;
}
}
public List<Product> GetProductByBarcode(string barcode)
{
try
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
ctx.Products.MergeOption = MergeOption.NoTracking;
return ctx.Products.Where(x => x.Barcode.Equals(barcode, StringComparison.OrdinalIgnoreCase) && x.Status == true).ToList();
}
}
catch (Exception ex)
{
LogService.Error("Error while fetching products", ex);
throw ex;
}
}
public Employee GetEmployeeById(Guid id)
{
try
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
ctx.Employees.MergeOption = MergeOption.NoTracking;
return ctx.Employees.Include("ContactDetail").SingleOrDefault(x => x.Id.Equals(id));
}
}
catch (Exception ex)
{
LogService.Error("Error while fetching customer", ex);
throw ex;
}
}
public Guid Add(Employee entity)
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
try
{
ctx.Employees.AddObject(entity);
ctx.SaveChanges();
return entity.Id;
}
catch (Exception ex)
{
LogService.Error("Error while adding Employee", ex);
throw new ArgumentException("Error while adding new Employee!");
}
}
}
public Dictionary<Guid, double> GetCusomerTotalAmount(IEnumerable<Guid> customerIds, DateTime fromDate)
{
if (customerIds == null || customerIds.Count() == 0)
throw new ArgumentNullException("customerIds");
try
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
var query = "SELECT o.CustomerId AS CustomerId, COALESCE(SUM(op.PaidAmount),0) AS TotalAmount FROM Payments op";
query += " INNER JOIN Orders o on o.Id = op.OrderId AND o.CustomerId IN ('" + string.Join("','", customerIds) + "') ";
query += " AND (DATEADD(dd, DATEDIFF(dd, 0, o.OrderDate), 0) >= @startDate AND DATEADD(dd, DATEDIFF(dd, 0, o.OrderDate), 0) <= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))";
query += " GROUP BY o.CustomerId";
EntityConnection entityConn = (EntityConnection)ctx.Connection;
using (SqlConnection sqlConn = (SqlConnection)entityConn.StoreConnection)
{
SqlCommand cmd = new SqlCommand(query, sqlConn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@startDate", fromDate);
if (sqlConn.State != ConnectionState.Open)
{
sqlConn.Open();
}
Dictionary<Guid, double> results = new Dictionary<Guid, double>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
results.Add(reader.GetGuid(0), reader.GetDouble(1));
}
}
return results;
}
}
}
catch (Exception ex)
{
LogService.Error("Error while calculating customer order amount", ex);
throw new ArgumentException("Error while calculating customer order amount", ex);
}
}
public Order GetOrderById(Guid id)
{
try
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
ctx.Orders.MergeOption = MergeOption.NoTracking;
var entity = ctx.Orders.Include("Customer").Include("Customer.ContactDetail")
.Include("OrderDetails")
.Include("Payments")
.Include("Employee").Include("Employee.ContactDetail").SingleOrDefault(x => x.Id.Equals(id));
return entity;
}
}
catch (Exception ex)
{
LogService.Error("Error while fetching order", ex);
throw new ArgumentException("Error while fetching order", ex);
}
}
public void Update(Setting entity)
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
try
{
ctx.Settings.Attach(entity);
ctx.ObjectStateManager.ChangeObjectState(entity, System.Data.EntityState.Modified);
ctx.Settings.ApplyCurrentValues(entity);
ctx.SaveChanges();
}
catch (Exception ex)
{
LogService.Error("Error while updating settings", ex);
throw ex;
}
}
}
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 Guid Add(Product entity)
{
if (string.IsNullOrEmpty(entity.Barcode))
throw new ArgumentException("Product barcode cannot be empty!");
if (string.IsNullOrEmpty(entity.Name))
throw new ArgumentException("Product name cannot be empty!");
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
try
{
entity.Status = true;
ctx.Products.AddObject(entity);
ctx.SaveChanges();
return entity.Id;
}
catch (Exception ex)
{
LogService.Error("Error while adding product", ex);
throw new ArgumentException("Error while adding new product!");
}
}
}
public Customer GetCustomerBySSN(string ssn)
{
try
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
ctx.Customers.MergeOption = MergeOption.NoTracking;
ctx.ContactDetails.MergeOption = MergeOption.NoTracking;
return ctx.Customers.Include("ContactDetail").SingleOrDefault(x => x.SSN.Equals(ssn, StringComparison.OrdinalIgnoreCase) && x.Status == true);
}
}
catch (Exception ex)
{
LogService.Error("Error while fetching customer", ex);
return null;
}
}
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);
}
}
public Order NewOrder(Guid customerId, Guid employeeId)
{
using (var scope = new TransactionScope(TransactionScopeOption.Required))
{
var entity = new Order();
entity.OrderDate = DateTime.Now;
entity.SystemId = Environment.MachineName;
entity.Status = true;
using (var ctx = new OpenPOSDbEntities())
{
entity.BillNo = ctx.Orders.Max(x => x.BillNo) + 1;
}
entity.CustomerId = customerId;
entity.EmployeeId = employeeId;
Add(entity);
entity = GetOrderById(entity.Id);
scope.Complete();
return entity;
}
}
public void UpdateOrderCustomer(Order entity)
{
if (entity.Id.Equals(Guid.Empty))
throw new ArgumentException("Order Id cannot be empty!");
if (entity.BillNo <= 0)
throw new ArgumentException("Order No cannot be empty!");
if (entity.CustomerId == Guid.Empty || entity.Customer == null)
throw new ArgumentException("Order Customer cannot be empty!");
try
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
ctx.ExecuteStoreCommand("UPDATE Orders SET CustomerId = {0} WHERE Id = {1};", entity.CustomerId, entity.Id);
}
}
catch (Exception ex)
{
LogService.Error("Error while updating order customer", ex);
throw new ArgumentException("Error while updating order customer!", ex);
}
}
public void Update(Order entity)
{
if (entity.Id.Equals(Guid.Empty))
throw new ArgumentException("Order Id cannot be empty!");
if (entity.BillNo <= 0)
throw new ArgumentException("Order No cannot be empty!");
if (entity.CustomerId == Guid.Empty)
throw new ArgumentException("Order Customer cannot be empty!");
if (entity.EmployeeId == Guid.Empty)
throw new ArgumentException("Order Employee cannot be empty!");
if (CheckDuplicateBillNo(entity.Id, entity.BillNo))
throw new ArgumentException("Duplicate Order No found!");
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
try
{
using (var scope = new TransactionScope(TransactionScopeOption.Required))
{
ctx.ExecuteStoreCommand("DELETE FROM OrderDetails WHERE OrderId = {0};", entity.Id);
ctx.ExecuteStoreCommand("DELETE FROM Payments WHERE OrderId = {0};", entity.Id);
foreach (var od in entity.OrderDetails)
{
ctx.AttachTo("OrderDetails", od);
ctx.ObjectStateManager.ChangeObjectState(od, System.Data.EntityState.Added);
}
foreach (var payment in entity.Payments)
{
ctx.AttachTo("Payments", payment);
ctx.ObjectStateManager.ChangeObjectState(payment, System.Data.EntityState.Added);
}
ctx.AttachTo("Orders", entity);
ctx.ObjectStateManager.ChangeObjectState(entity, System.Data.EntityState.Modified);
ctx.SaveChanges();
scope.Complete();
}
}
catch (Exception ex)
{
LogService.Error("Error while updating order", ex);
throw new ArgumentException("Error while updating order!", ex);
}
}
}
public Order GetOrderByOrderNo(int orderNo)
{
try
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
ctx.Orders.MergeOption = MergeOption.NoTracking;
return ctx.Orders.Include("Customer").Include("Customer.ContactDetail")
.Include("OrderDetails")
.Include("Payments")
.Include("Employee").Include("Employee.ContactDetail").Where(x => x.BillNo == orderNo && x.Status == true).FirstOrDefault();
}
}
catch (Exception ex)
{
LogService.Error("Error while fetching order by bill no", ex);
throw new ArgumentException("Error while fetching order by bill no", ex);
}
}
private bool CheckDuplicateBillNo(Guid orderId, int billNo)
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
var query = ctx.Orders.Where(x => x.BillNo == billNo);
if (orderId != Guid.Empty)
{
query = query.Where(x => x.Id != orderId);
}
return query.Count() > 0;
}
}
public ICollection<Customer> GetTopCustomers(int count, DateTime fromDate, DateTime toDate)
{
var lstCustomers = new List<Customer>();
var cids = new Dictionary<Guid, 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 queryString = new StringBuilder("SELECT TOP " + count + " CustomerId, SUM(od.Price*od.Quantity) AS TQ FROM Orders o");
queryString.Append(" INNER JOIN OrderDetails od ON od.OrderId = o.Id");
queryString.Append(" WHERE DATEADD(dd, DATEDIFF(dd, 0, o.OrderDate), 0) >= @fromdate");
queryString.Append(" AND DATEADD(dd, DATEDIFF(dd, 0, o.OrderDate), 0) <= @todate");
queryString.Append(" AND CustomerId NOT IN ('b004dfc3-e53f-4e6b-952f-67ceae170da6')");
queryString.Append(" GROUP BY CustomerId ORDER BY TQ DESC");
cmd.Parameters.AddWithValue("@fromdate", fromDate);
cmd.Parameters.AddWithValue("@todate", toDate);
cmd.CommandText = queryString.ToString();
cmd.CommandType = CommandType.Text;
if (sqlConn.State != ConnectionState.Open)
{
sqlConn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
cids.Add(reader.GetGuid(0), reader.GetDouble(1));
}
}
}
}
if (cids != null && cids.Count > 0)
{
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
ctx.ContextOptions.LazyLoadingEnabled = false;
ctx.Customers.MergeOption = MergeOption.NoTracking;
var ids = cids.Keys.ToList();
lstCustomers = ctx.Customers.Include("ContactDetail").Where(x => ids.Contains(x.Id) && x.Status == true).ToList();
foreach (var p in lstCustomers)
{
p.TotalAmount = cids[p.Id];
}
}
}
}
catch (Exception ex)
{
LogService.Error("Error while searching top customers", ex);
throw new ArgumentException("Error while searching top customers", ex);
}
return new Collection<Customer>(lstCustomers);
}
public void Update(Employee entity)
{
if (entity.Id.Equals(Guid.Empty))
throw new ArgumentException("Employee Id cannot be empty!");
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
try
{
ctx.Employees.Attach(entity);
ctx.ObjectStateManager.ChangeObjectState(entity, System.Data.EntityState.Modified);
ctx.Employees.ApplyCurrentValues(entity);
ctx.SaveChanges();
}
catch (Exception ex)
{
LogService.Error("Error while updating Employee", ex);
throw ex;
}
}
}
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 ICollection<Product> GetTopProducts(int count, DateTime fromDate, DateTime toDate)
{
var lstProducts = new List<Product>();
var pids = new Dictionary<Guid, Tuple<double, 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 queryString = new StringBuilder("SELECT TOP " + count + " ProductId, SUM(Quantity) AS TQ, SUM(Quantity*Price) AS TA FROM OrderDetails od");
queryString.Append(" INNER JOIN Orders o ON o.Id = od.OrderId");
queryString.Append(" AND DATEADD(dd, DATEDIFF(dd, 0, o.OrderDate), 0) >= @fromdate");
queryString.Append(" AND DATEADD(dd, DATEDIFF(dd, 0, o.OrderDate), 0) <= @todate");
queryString.Append(" WHERE ProductId IS NOT NULL AND ProductId NOT IN ('56C9FA56-F363-E011-AF1E-001E90ED96B8','989DA90C-1431-E111-B051-001E90ED96B8')");
queryString.Append(" GROUP BY ProductId ORDER BY TQ DESC");
cmd.Parameters.AddWithValue("@fromdate", fromDate);
cmd.Parameters.AddWithValue("@todate", toDate);
cmd.CommandText = queryString.ToString();
cmd.CommandType = CommandType.Text;
if (sqlConn.State != ConnectionState.Open)
{
sqlConn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
pids.Add(reader.GetGuid(0), Tuple.Create<double, double>(reader.GetDouble(1), reader.GetDouble(2)));
}
}
}
}
using (OpenPOSDbEntities ctx = new OpenPOSDbEntities())
{
ctx.ContextOptions.LazyLoadingEnabled = false;
ctx.Products.MergeOption = MergeOption.NoTracking;
var ids = pids.Keys.ToList();
lstProducts = ctx.Products.Include("Supplier").Include("Category").Include("TaxCategory").Where(x => ids.Contains(x.Id)).ToList();
foreach (var p in lstProducts)
{
p.Sold = pids[p.Id].Item1;
p.SubTotal = pids[p.Id].Item2;
}
}
}
catch (Exception ex)
{
LogService.Error("Error while searching top products", ex);
throw new ArgumentException("Error while searching top products", ex);
}
return new Collection<Product>(lstProducts);
}
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);
}
}