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