public void Delete(Guid id) { if (id.Equals(Guid.Empty)) { throw new ArgumentException("Product id cannot be empty!"); } try { using (OpenPOSDbEntities ctx = new OpenPOSDbEntities()) { var entity = GetProductById(id); if (entity != null) { ctx.Products.Attach(entity); ctx.Products.DeleteObject(entity); ctx.SaveChanges(); } } } catch (Exception ex) { LogService.Error("Error while deleting product", ex); throw ex; } }
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 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(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 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 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 Update(Product entity) { if (entity.Id.Equals(Guid.Empty)) { throw new ArgumentException("Product Id cannot be empty!"); } 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 { ctx.Products.Attach(entity); ctx.ObjectStateManager.ChangeObjectState(entity, System.Data.EntityState.Modified); ctx.Products.ApplyCurrentValues(entity); ctx.SaveChanges(); } catch (Exception ex) { LogService.Error("Error while updating product", 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 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 Guid Add(Setting entity) { using (var ctx = new OpenPOSDbEntities()) { ctx.Settings.AddObject(entity); ctx.SaveChanges(); return(entity.Id); } }
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 <Setting> GetSettings() { using (var ctx = new OpenPOSDbEntities()) { ctx.ContextOptions.LazyLoadingEnabled = false; ctx.Settings.MergeOption = MergeOption.NoTracking; return(ctx.Settings.ToList()); } }
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 <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; } }
private bool CheckDuplicate(Guid id, string number, OpenPOSDbEntities ctx) { var query = ctx.Customers.Where(x => x.SSN.Equals(number, StringComparison.InvariantCultureIgnoreCase)); if (!id.Equals(Guid.Empty)) { query = query.Where(x => x.Id != id); } return(query.Count() != 0); }
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); } }
private bool CheckDuplicateBillNo(Guid purchaseId, string billNo) { using (OpenPOSDbEntities ctx = new OpenPOSDbEntities()) { var query = ctx.Purchases.Where(x => x.BillNo == billNo); if (purchaseId != Guid.Empty) { query = query.Where(x => x.Id != purchaseId); } return(query.Count() > 0); } }
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 Product GetProductById(Guid id) { try { using (OpenPOSDbEntities ctx = new OpenPOSDbEntities()) { ctx.Products.MergeOption = MergeOption.NoTracking; return(ctx.Products.SingleOrDefault(x => x.Id.Equals(id))); } } catch (Exception ex) { LogService.Error("Error while fetching products", 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 Product GetProductByName(string name) { try { using (OpenPOSDbEntities ctx = new OpenPOSDbEntities()) { ctx.Products.MergeOption = MergeOption.NoTracking; return(ctx.Products.Where(x => x.Name.Equals(name, StringComparison.OrdinalIgnoreCase) && x.Status == true).FirstOrDefault()); } } catch (Exception ex) { LogService.Error("Error while fetching products", ex); throw ex; } }
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 ICollection <T> Search() { try { using (var ctx = new OpenPOSDbEntities()) { //var query = ctx.CreateQuery<T>("SELECT * FROM " + ctx.GetTableName<T>()); var query = ctx.CreateObjectSet <T>(); return(new Collection <T>(query.ToList())); } } catch (Exception ex) { LogService.Error("Error while search masterdata", 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 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 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); } }
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 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); } }
public void Update(Customer entity) { if (entity.Id.Equals(Guid.Empty)) { throw new ArgumentException("Customer Id cannot be empty!"); } 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.Attach(entity); ctx.ContactDetails.Attach(entity.ContactDetail); ctx.ObjectStateManager.ChangeObjectState(entity, System.Data.EntityState.Modified); ctx.ObjectStateManager.ChangeObjectState(entity.ContactDetail, System.Data.EntityState.Modified); ctx.Customers.ApplyCurrentValues(entity); ctx.ContactDetails.ApplyCurrentValues(entity.ContactDetail); ctx.SaveChanges(); } catch (Exception ex) { LogService.Error("Error while updating customer", 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); } }