Example #1
0
        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;
            }
        }
Example #2
0
        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!");
                }
            }
        }
Example #3
0
        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);
                }
            }
        }
Example #4
0
        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);
            }
        }
Example #5
0
        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);
                }
            }
        }
Example #6
0
 public Guid Add(Setting entity)
 {
     using (var ctx = new OpenPOSDbEntities())
     {
         ctx.Settings.AddObject(entity);
         ctx.SaveChanges();
         return entity.Id;
     }
 }
Example #7
0
        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();
            }
        }
Example #8
0
        public ICollection<Setting> GetSettings()
        {
            using (var ctx = new OpenPOSDbEntities())
            {
                ctx.ContextOptions.LazyLoadingEnabled = false;
                ctx.Settings.MergeOption = MergeOption.NoTracking;

                return ctx.Settings.ToList();
            }
        }
Example #9
0
        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();
            }
        }
Example #10
0
        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;
            }
        }
Example #11
0
 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;
     }
 }
Example #12
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;
     }
 }
Example #13
0
 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!");
         }
     }
 }
Example #14
0
        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);
            }
        }
Example #15
0
 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);
     }
 }
Example #16
0
        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;
                }
            }
        }
Example #17
0
        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);
            }
        }
Example #18
0
        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!");
                }
            }
        }
Example #19
0
 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;
     }
 }
Example #20
0
        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);
            }
        }
Example #21
0
        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;
            }
        }
Example #22
0
        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);
            }
        }
Example #23
0
        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);
                }
            }
        }
Example #24
0
 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);
     }
 }
Example #25
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;
            }
        }
Example #26
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);
        }
Example #27
0
        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;
                }
            }
        }
Example #28
0
        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;
        }
Example #29
0
        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);
        }
Example #30
0
 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);
     }
 }