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