public static List<ProductEntity> GetProductsByName(string ProductName)
        {
            List<ProductEntity> lst_product = null;
            using (InventoryEntities db = new InventoryEntities())
            {
                lst_product = (from p in db.products
                               join c in db.categories on p.category equals c.id
                               join s in db.sub_category on p.sub_category equals s.id
                               where (p.product_name.Contains(ProductName) && (p.status == true)
                                  || (ProductName == "") && (p.status == true))
                               select new ProductEntity
                               {
                                   id = p.id,
                                   product_name = p.product_name,
                                   image_url = p.image_url,
                                   brand = p.brand,
                                   category = c.category_name,
                                   sub_category = s.subcategory_name,
                                   weight = p.weight,
                                   cost_price = p.cost_price,
                                   sell_price = p.sell_price,
                                   status = p.status,
                                   Stock = p.Stock
                               }).ToList();


            }
            return lst_product;
        }
        public static int AddUpdateSubCategory(sub_category ob)
        {
            int _Categoryid = 0;
            using (InventoryEntities db = new InventoryEntities())
            {
                if (ob.id > 0)
                {
                    sub_category temp = db.sub_category.Where(u => u.id == ob.id).FirstOrDefault();
                    if (temp != null)
                    {
                        temp.id = ob.id;
                        temp.subcategory_name = ob.subcategory_name;
                        temp.category = ob.category;

                    }

                }
                else
                {
                    db.sub_category.Add(ob);
                }
                int x = db.SaveChanges();
                if (x > 0)
                {
                    _Categoryid = ob.id;
                }

            }
            return _Categoryid;
        }
        public static int AddUpdateDealer(dealer ob)
        {
            int delarid = 0;
            using (InventoryEntities db = new InventoryEntities())
            {
                if (ob.id > 0)
                {
                    dealer temp = db.dealers.Where(u => u.id == ob.id).FirstOrDefault();
                    if (temp != null)
                    {
                        temp.id = ob.id;
                        temp.dealer_name = ob.dealer_name;
                        temp.dealer_address = ob.dealer_address;
                    }

                }
                else
                {
                    db.dealers.Add(ob);
                }
                int x = db.SaveChanges();
                if (x > 0)
                {
                    delarid = ob.id;
                }

            }
            return delarid;
        }
 public static List<category> GetAllCategoryByName(string Category_Name)
 {
     List<category> _Category = null;
     using (InventoryEntities db = new InventoryEntities())
     {
         _Category = (from u in db.categories where (u.category_name == Category_Name) select u).ToList();
     }
     return _Category;
 }
 public static List<dealer> GetAllDelars(int Delarid)
 {
     List<dealer> _delars = null;
     using (InventoryEntities db = new InventoryEntities())
     {
         _delars = (from u in db.dealers where ((Delarid == null) || (u.id == Delarid) || (Delarid == 0)) select u).ToList();
     }
     return _delars;
 }
 public static List<dealer> GetDealerByName(string Delar_Name)
 {
     List<dealer> _delars = null;
     using (InventoryEntities db = new InventoryEntities())
     {
         _delars = (from u in db.dealers where (u.dealer_name == Delar_Name) select u).ToList();
     }
     return _delars;
 }
 public static List<sub_category> GetAllSubCategoryByName(string Category_Name,int CategoryId)
 {
     List<sub_category> _Category = null;
     using (InventoryEntities db = new InventoryEntities())
     {
         _Category = (from u in db.sub_category where ((u.subcategory_name == Category_Name)&&(u.category==CategoryId)) select u).ToList();
     }
     return _Category;
 }
 public static List<product> GetAllProduct(int ProductId)
 {
     List<product> _product = null;
     using (InventoryEntities db = new InventoryEntities())
     {
         _product = (from u in db.products where ((ProductId == null) || (u.id == ProductId) || (ProductId == 0)) select u).ToList();
     }
     return _product;
 }
 public static List<category> GetAllCategory(int CategoryId)
 {
     List<category> _category = null;
     using (InventoryEntities db = new InventoryEntities())
     {
         _category = (from u in db.categories where ((CategoryId == null) || (u.id == CategoryId) || (CategoryId == 0)) select u).ToList();
     }
     return _category;
 }
 public static List<sub_category> GetAllSubCategory(int subCategoryId,int CategoryId)
 {
     List<sub_category> _category = null;
     using (InventoryEntities db = new InventoryEntities())
     {
         _category = (from u in db.sub_category where (((subCategoryId == null) || (u.id == subCategoryId) || (subCategoryId == 0))
                          &&((CategoryId==0)||(u.category==CategoryId)||(CategoryId==null))) select u).ToList();
     }
     return _category;
 }
 public static bool CheckLogin(string userName, string Password)
 {
     bool flag = false;
     using (InventoryEntities db = new InventoryEntities())
     {
         int count = (from u in db.Users where u.UserName==userName && u.Password==Password select u ).Count();
         if (count != 0)
             flag = true;
     }
     return flag;
 }
 public static bool CheckProductNameAvailable(string ProductName)
 {
     bool flag = false;
     using (InventoryEntities db = new InventoryEntities())
     {
         int num = (from p in db.products
                    where p.product_name == ProductName
                    select p).Count();
         if (num == 0)
             flag = true;
     }
     return flag;
 }
 public static bool DeleteSubCategory(int Category_id)
 {
     bool flag = false;
     using (InventoryEntities db = new InventoryEntities())
     {
         sub_category temp = db.sub_category.Where(u => u.id == Category_id).FirstOrDefault();
         if (temp != null)
         {
             db.sub_category.Remove(temp);
             db.SaveChanges();
             flag = true;
         }
     }
     return flag;
 }
 public static bool DeleteDealer(int Dealer_id)
 {
     bool flag =false;
     using (InventoryEntities db = new InventoryEntities())
     {
         dealer temp = db.dealers.Where(u => u.id == Dealer_id).FirstOrDefault();
         if (temp != null)
         {
             db.dealers.Remove(temp);
             db.SaveChanges();
             flag = true;
         }
     }
     return flag;
 }
 public static decimal GetOverAllBalance(DateTime? StartDate, DateTime? EndDate)
 {
     decimal balance = 0;
     using (InventoryEntities db = new InventoryEntities())
     {
         try
         {
             balance = (from s in db.selling_history
                        where (((s.payment_date >= StartDate) || (StartDate == null))
                        && ((s.payment_date <= EndDate) || (EndDate == null)))
                        select (s.credit - s.debit)).Sum();
         }
         catch { }
     }
     return balance;
 }
 public static List<SubCategoryEntity> GetAllSubCategoryEntity(int subCategoryId,int CategoryId)
 {
     List<SubCategoryEntity> _subcategory = null;
     using (InventoryEntities db = new InventoryEntities())
     {
         _subcategory = (from u in db.sub_category
                         join c in db.categories on u.category equals c.id
                         where ((subCategoryId == null) || (u.id == subCategoryId) || (subCategoryId == 0)&&
                         ((CategoryId==0)||(c.id==CategoryId)||(CategoryId==null)))
                         select new SubCategoryEntity
                         {
                             id = u.id,
                             category_name = c.category_name,
                             sub_category_name = u.subcategory_name
                         }).ToList();
     }
     return _subcategory;
 }
        public static bool AddBulkSellingHistory(List<selling_history> lst_sellinghistory)
        {
            bool flag = false;
            using (InventoryEntities db = new InventoryEntities())
            {
                foreach (selling_history item in lst_sellinghistory)
                {
                    db.selling_history.Add(item);
                }

                int x = db.SaveChanges();
                if (x > 0)
                {
                    flag = true;
                }

            }
            return flag;
        }
        public static int AddUpdateSellingHistory(selling_history ob)
        {
            int _id = 0;
            using (InventoryEntities db = new InventoryEntities())
            {
                if (ob.id > 0)
                {
                    selling_history temp = db.selling_history.Where(u => u.id == ob.id).FirstOrDefault();
                    if (temp != null)
                    {
                        temp.id = ob.id;
                        temp.dealer_id = ob.dealer_id;
                        temp.product_id = ob.product_id;
                        temp.quantity = ob.quantity;
                        temp.credit = ob.credit;
                        temp.debit = ob.debit;
                        temp.transaction_type = ob.transaction_type;
                        temp.customer_info = ob.customer_info;
                        temp.payment_type = ob.payment_type;
                        temp.payment_date = ob.payment_date;
                        temp.customer_name = ob.customer_name;
                        temp.remarks = ob.remarks;
                    }

                }
                else
                {
                    db.selling_history.Add(ob);
                }
                int x = db.SaveChanges();
                if (x > 0)
                {
                    _id = ob.id;
                }

            }
            return _id;
        }
        public static int AddUpdateProduct(product ob)
        {
            int _Productid = 0;
            using (InventoryEntities db = new InventoryEntities())
            {
                if (ob.id > 0)
                {
                    product temp = db.products.Where(u => u.id == ob.id).FirstOrDefault();
                    if (temp != null)
                    {
                        temp.id = ob.id;
                        temp.product_name = ob.product_name;
                        temp.category = ob.category;
                        temp.image_url = ob.image_url;
                        temp.brand = ob.brand;
                        temp.sub_category = ob.sub_category;
                        temp.sell_price = ob.sell_price;
                        temp.cost_price = ob.cost_price;
                        temp.weight = ob.weight;
                        temp.status = ob.status;
                        temp.Stock = ob.Stock;
                    }

                }
                else
                {
                    db.products.Add(ob);
                }
                int x = db.SaveChanges();
                if (x > 0)
                {
                    _Productid = ob.id;
                }

            }
            return _Productid;
        }
 public static List<TransactionPurchaseEntity> GetAllDebitTransaction(DateTime? StartDate, DateTime? EndDate)
 {
     List<TransactionPurchaseEntity> lsttxns = null;
     using (InventoryEntities db = new InventoryEntities())
     {
         lsttxns = (from s in db.selling_history
                    join d in db.dealers on s.dealer_id equals d.id
                    join p in db.products on s.product_id equals p.id
                    where (((s.payment_date >= StartDate) || (StartDate == null))
                    && ((s.payment_date <= EndDate) || (EndDate == null)) && s.transaction_type == 2)
                    select new TransactionPurchaseEntity
                        {
                            ID = s.id,
                            DelarName = d.dealer_name,
                            ProductName = p.product_name,
                            Credit = s.credit,
                            Debit = s.debit,
                            PaymentType = s.payment_type,
                            Remarks = s.remarks,
                            PaymentDate = s.payment_date
                        }
                    ).ToList();
     }
     return lsttxns;
 }
 public static List<product> GetProducts(int ProductId)
 {
     List<product> lst_product = null;
     using (InventoryEntities db = new InventoryEntities())
     {
         lst_product = (from p in db.products
                        where ((p.id == ProductId) || (ProductId == 0))
                        select p).ToList();
     }
     return lst_product;
 }
 public static List<product> GetProductBySubcategory(int subcategoryId)
 {
     List<product> lst_product = null;
     using (InventoryEntities db = new InventoryEntities())
     {
         lst_product = db.products.Where(s => s.sub_category == subcategoryId && s.status == true).ToList();
     }
     return lst_product;
 }
        public static int UpdateProductStock(int ProductId)
        {
            int Currentstock = 0;
            using (InventoryEntities db = new InventoryEntities())
            {
                product temp = GetAllProduct(ProductId).FirstOrDefault();
                if (temp != null)
                {
                    int CreditStock = 0;
                    int DebitStock = 0;
                    try
                    {
                        CreditStock = db.selling_history.Where(s => s.product_id == ProductId && s.transaction_type == 2).Sum(q => q.quantity); //Debit Transactions

                    }
                    catch
                    { }
                    try
                    {
                        DebitStock = db.selling_history.Where(s => s.product_id == ProductId && s.transaction_type == 1).Sum(q => q.quantity); //Credit Transactions
                    }
                    catch
                    { }
                    Currentstock = CreditStock - DebitStock;
                    temp.Stock = Currentstock;
                    AddUpdateProduct(temp);
                }
            }
            return Currentstock;
        }
 public static List<product> GetEmptyStockList()
 {
     List<product> lstProducts = null;
     using (InventoryEntities db = new InventoryEntities())
     {
         lstProducts = (from p in db.products where ((p.Stock == 0) && (p.status == true)) select p).ToList();
     }
     return lstProducts;
 }