public static List <vwProduct> retrieveAll()
{
List <vwProduct> products;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
products = (from p in context.Products
select new vwProduct
{
ProductName = p.ProductName,
ProductAttributes = p.ProductAttributes,
ProductBarcode = p.ProductBarcode,
ProductBarcodeImage = p.ProductBarcodeImage,
ProductCategories = (from category in context.Categories where category.CategoryId == p.ProductCategory select category.CategoryValue).FirstOrDefault(),
ProductDateAdded = p.ProductDateAdded,
ProductId = p.ProductId,
ProductImage = p.ProductImage,
ProductQuantity = p.ProductQuantity,
ProductReorderLimit = p.ProductReorderLimit,
ProductReplenishLimit = p.ProductReplenishLimit,
ProductSku = p.ProductSku,
ProductStatuses = (from stat in context.Statuses where stat.StatusId == p.ProductStatus select stat.StatusValue).FirstOrDefault(),
ProductStore = (from s in context.Stores where s.StoreId == p.ProductStore select s.StoreName).FirstOrDefault(),
ProductSubCategories = (from subcat in context.SubCategories where subcat.SubCategoryId == p.ProductSubCategory select subcat.SubCategoryValue).FirstOrDefault(),
ProductSupplier = (from sup in context.Suppliers where sup.SupplierId == p.ProductSupplier select sup.SupplierName).FirstOrDefault(),
ProductUnitCost = p.ProductUnitCost,
ProductVat = p.ProductVat,
ProductWarehouse = (from warehouse in context.Warehouses where warehouse.WarehouseId == p.ProductWarehouse select warehouse.WarehouseName).FirstOrDefault(),
ProductDiscount = p.ProductDiscount,
ProductDescription = p.ProductDescription,
ProductUnitOfMeasurement = (from uom in context.UnitOfMeasurements where uom.UnitOfMeasurementId == p.ProductUnitOfMeasurement select uom.UnitOfMeasurementName).FirstOrDefault()
}).ToList();
}
return(products);
}
public static List <vwTransaction> retrieveTransactionToday(string dateTime)
{
List <vwTransaction> transactions;
using (SMLIB.Context.Context context = new SMLIB.Context.Context()) {
transactions = (from t in context.Transactions
where t.TransactionDate == dateTime
orderby t.Transaction_Time descending
select new vwTransaction {
TransactionDate = dateTime,
TransactionAmountReceived = t.TransactionAmountReceived,
TransactionAmountReturned = t.TransactionAmountReturned,
TransactionId = t.TransactionId,
TransactionSubTotal = t.TransactionSubTotal,
Transaction_OwnerId = t.Transaction_OwnerId,
Transaction_Remarks = t.Transaction_Remarks,
Transaction_Status = t.Transaction_Status,
TransactionNewAmountReturned = t.TransactionNewAmountReturned,
TransactionTime = t.Transaction_Time,
TransactionDateModified = t.TransactionDateModified,
TransactionTimeModified = t.TransactionTimeModified,
TransactionCashier = (from u in context.UserDetails where u.UserDetailId == t.Transaction_CashierId select u.UserFirstName + " " + u.UserLastName).FirstOrDefault(),
TransactionCustomer = (from udetail in context.UserDetails where udetail.UserDetailId == t.Transaction_OwnerId select udetail.UserFirstName + " " + udetail.UserLastName).FirstOrDefault()
}).ToList();
}
return(transactions);
}
public static void delete(Guid id)
{
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
var product = (from p in context.Products where p.ProductId == id select p).FirstOrDefault();
context.Products.Remove(product);
context.SaveChanges();
}
}
public static void delete(Guid id)
{
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
var sc = (from subcat in context.SubCategories where subcat.SubCategoryId == id select subcat).FirstOrDefault();
context.SubCategories.Remove(sc);
context.SaveChanges();
}
}
public static void delete(Guid id)
{
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
var warehouse = (from w in context.Warehouses where w.WarehouseId == id select w).FirstOrDefault();
context.Warehouses.Remove(warehouse);
context.SaveChanges();
}
}
public static void update(Guid id, string subcategory)
{
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
var sc = (from subcat in context.SubCategories where subcat.SubCategoryId == id select subcat).FirstOrDefault();
sc.SubCategoryValue = subcategory;
context.SaveChanges();
}
}
public static void update(Guid id, string name)
{
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
var stat = (from s in context.Statuses where s.StatusId == id select s).FirstOrDefault();
stat.StatusValue = name;
context.SaveChanges();
}
}
public static void delete(Guid id)
{
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
var stat = (from status in context.Statuses where status.StatusId == id select status).FirstOrDefault();
context.Statuses.Remove(stat);
context.SaveChanges();
}
}
public static void delete(Guid id)
{
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
var cat = (from c in context.Categories where c.CategoryId == id select c).FirstOrDefault();
context.Categories.Remove(cat);
context.SaveChanges();
}
}
public static bool checkIfCategoryIsUsed(Guid id)
{
bool isUsed;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
isUsed = context.Products.Any(x => x.ProductCategory == id);
}
return(isUsed);
}
public static bool checkIfCategoryExists(string category)
{
bool exist = false;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
exist = context.Categories.Any(c => c.CategoryValue == category.ToLower());
}
return(exist);
}
public static List <Category> retrieve()
{
List <Category> categories;
using (SMLIB.Context.Context context = new SMLIB.Context.Context()) {
categories = (from category in context.Categories
select category).Distinct().ToList();
}
return(categories);
}
public static Store retrieveByName(string name)
{
Store store;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
store = (from s in context.Stores where s.StoreName == name select s).FirstOrDefault();
}
return(store);
}
public static bool checkIfProductNameExists(string name)
{
bool b = false;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
b = context.Products.Any(x => x.ProductName == name);
}
return(b);
}
public static bool checkIfWarehouseExists(string name)
{
bool b = false;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
b = context.Warehouses.Any(x => x.WarehouseName == name);
}
return(b);
}
public static Guid retrieveId(string unitOfMeasurement)
{
Guid id;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
id = (from m in context.UnitOfMeasurements where m.UnitOfMeasurementName == unitOfMeasurement select m.UnitOfMeasurementId).FirstOrDefault();
}
return(id);
}
public static bool checkIfStatusExists(string name)
{
bool b = false;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
b = context.Statuses.Any(x => x.StatusValue == name);
}
return(b);
}
public static List <Status> retrieveByName(string name)
{
List <Status> s;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
s = (from stat in context.Statuses where stat.StatusValue == name select stat).ToList();
}
return(s);
}
public static List <Category> retrieveCategoryByName(string category)
{
List <Category> categories;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
categories = (from c in context.Categories where c.CategoryValue == category select c).ToList();
}
return(categories);
}
public static bool checkIfSubCategoryExists(string subCategory)
{
bool b = false;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
b = context.SubCategories.Any(x => x.SubCategoryValue == subCategory);
}
return(b);
}
public static List <SubCategory> retrieveByName(string subCategory)
{
List <SubCategory> subcateg;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
subcateg = (from subcat in context.SubCategories where subcat.SubCategoryValue == subCategory select subcat).ToList();
}
return(subcateg);
}
public static bool checkProductQuantity(string search)
{
bool b;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
b = context.Products.All(product => product.ProductQuantity == 0 && (product.ProductName == search || product.ProductBarcode == search || product.ProductSku == search));
}
return(b);
}
public static List <Warehouse> retrieveByName(string name)
{
List <Warehouse> warehouse;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
warehouse = (from w in context.Warehouses where w.WarehouseName == name select w).ToList();
}
return(warehouse);
}
public static List <SubCategory> retrieve()
{
List <SubCategory> subCategories;
using (SMLIB.Context.Context context = new SMLIB.Context.Context()) {
subCategories = (from subcat in context.SubCategories
select subcat).ToList();
}
return(subCategories);
}
public static Guid getStoreId(string store)
{
Guid id;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
id = (from s in context.Stores where s.StoreName == store select s.StoreId).FirstOrDefault();
}
return(id);
}
public static bool checkIfSupplierExists(string name)
{
bool b = false;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
b = context.Suppliers.Any(x => x.SupplierName == name);
}
return(b);
}
public static void delete(Guid id)
{
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
var sup = (from s in context.Suppliers where s.SupplierId == id select s).FirstOrDefault();
context.Suppliers.Remove(sup);
context.SaveChanges();
}
}
public static List <Supplier> retrieveByName(string name)
{
List <Supplier> supplier;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
supplier = (from s in context.Suppliers where s.SupplierName == name select s).ToList();
}
return(supplier);
}
public static void updateOrderQuantity(Guid transactionId, Guid productId, double quantity)
{
using (SMLIB.Context.Context context = new SMLIB.Context.Context()) {
var orders = (from order in context.Orders
where order.Order_Transaction == transactionId && order.Order_ProductId == productId
select order).FirstOrDefault();
orders.Order_Quantity = quantity;
context.SaveChanges();
}
}
public static List <Supplier> suppliers()
{
List <Supplier> suppliers;
using (SMLIB.Context.Context context = new SMLIB.Context.Context())
{
suppliers = (from sup in context.Suppliers
select sup).ToList();
}
return(suppliers);
}
