public static string GetUserFullName(string userName)
{
var storageDbEntities = new StorageDBEntities();
User user = storageDbEntities.Users.Where(u => u.Username == userName).FirstOrDefault();
return user != null ? string.Format("{0} {1}", user.Name, user.Surname) : string.Empty;
}
public static int? GetUserID(string userName)
{
var storageDbEntities = new StorageDBEntities();
User user = storageDbEntities.Users.Where(u => u.Username == userName).FirstOrDefault();
return user != null ? user.ID : (int?) null;
}
public static bool ValidateUser(string userName, string password)
{
var storageDbEntities = new StorageDBEntities();
User user = storageDbEntities.Users.Where(u => u.Username == userName && u.Password == password).FirstOrDefault();
return user != null;
}
public static void DeleteClient(int id)
{
var storageDbEntities = new StorageDBEntities();
Client client = storageDbEntities.Clients.Where(c => c.ID == id && c.UserID == UserHelper.UserID).FirstOrDefault();
storageDbEntities.Clients.DeleteObject(client);
storageDbEntities.SaveChanges();
}
public static void DeleteProduct(int id)
{
var storageDbEntities = new StorageDBEntities();
Category category = storageDbEntities.Categories.Where(p => p.ID == id && p.UserID == UserHelper.UserID).FirstOrDefault();
storageDbEntities.Categories.DeleteObject(category);
storageDbEntities.SaveChanges();
}
public static CategoryModel GetCategory(int id)
{
var storageDbEntities = new StorageDBEntities();
CategoryModel category = (from c in storageDbEntities.Categories
where c.ID == id && c.UserID == UserHelper.UserID
select new CategoryModel
{
ID = c.ID,
Name = c.Name,
}).SingleOrDefault();
return category;
}
public static void UpdateProduct(int id, CategoryModel categoryModel)
{
var storageDbEntities = new StorageDBEntities();
Category category = storageDbEntities.Categories.Where(c => c.ID == id && c.UserID == UserHelper.UserID).FirstOrDefault();
if (category != null)
{
category.Name = categoryModel.Name;
}
storageDbEntities.SaveChanges();
}
public static bool IsActive(string userName)
{
var storageDbEntities = new StorageDBEntities();
User user = storageDbEntities.Users.Where(u => u.Username == userName).FirstOrDefault();
if(user != null)
{
return user.ExpirationDate >= DateTime.Now;
}
return false;
}
public static List<CategoryModel> GetCategoryList()
{
var storageDbEntities = new StorageDBEntities();
List<CategoryModel> list = (from c in storageDbEntities.Categories
where c.UserID == UserHelper.UserID
select new CategoryModel
{
ID = c.ID,
Name = c.Name,
}).ToList();
return list;
}
public static void UpdateUser(UserModel userModel)
{
var storageDbEntities = new StorageDBEntities();
User user = storageDbEntities.Users.Where(u => u.ID == userModel.ID).FirstOrDefault();
if (user != null)
{
user.Name = userModel.Name;
user.Surname = userModel.Surname;
}
storageDbEntities.SaveChanges();
}
public static void UpdateClient(int id, ClientModel clientModel)
{
var storageDbEntities = new StorageDBEntities();
Client client = storageDbEntities.Clients.Where(c => c.ID == id && c.UserID == UserHelper.UserID).FirstOrDefault();
if (client != null)
{
client.Name = clientModel.Name;
client.Address = clientModel.Address;
client.Telephone = clientModel.Telephone;
}
storageDbEntities.SaveChanges();
}
public static ClientModel GetClient(int id)
{
var storageDbEntities = new StorageDBEntities();
ClientModel client = (from c in storageDbEntities.Clients
where c.ID == id && c.UserID == UserHelper.UserID
select new ClientModel
{
ID = c.ID,
Name = c.Name,
Address = c.Address,
Telephone = c.Telephone
}).SingleOrDefault();
return client;
}
public static UserModel GetUser(string userName)
{
var storageDbEntities = new StorageDBEntities();
UserModel userModel = (from u in storageDbEntities.Users
where u.Username == userName
select new UserModel
{
ID = u.ID,
Name = u.Name,
Surname = u.Surname
}).FirstOrDefault();
return userModel;
}
public static List<ClientModel> GetClientList()
{
var storageDbEntities = new StorageDBEntities();
List<ClientModel> list = (from c in storageDbEntities.Clients
where c.UserID == UserHelper.UserID
select new ClientModel
{
ID = c.ID,
Name = c.Name,
Address = c.Address,
Telephone = c.Telephone
}).ToList();
return list;
}
public static void CreateProduct(CategoryModel categoryModel)
{
if (UserHelper.UserID != null)
{
var storageDbEntities = new StorageDBEntities();
Category newCategory = new Category
{
Name = categoryModel.Name,
UserID = UserHelper.UserID.Value
};
storageDbEntities.Categories.AddObject(newCategory);
storageDbEntities.SaveChanges();
}
}
public static ProductModel GetProductByCode(string code)
{
var storageDbEntities = new StorageDBEntities();
ProductModel product = (from p in storageDbEntities.Products
where p.Code == code && p.UserID == UserHelper.UserID
select new ProductModel
{
ID = p.ID,
Code = p.Code,
Name = p.Name,
WholesalePrice = p.WholesalePrice,
ShallowWholesalePrice = p.ShallowWholesalePrice,
RetailPrice = p.RetailPrice,
Unit = p.Unit
}).SingleOrDefault();
return product;
}
public static void DeleteInvoice(int id)
{
var storageDbEntities = new StorageDBEntities();
Invoice invoice = storageDbEntities.Invoices.Where(inv => inv.ID == id && inv.UserID == UserHelper.UserID).FirstOrDefault();
if (invoice != null)
{
foreach (ProductsInInvoice productsInInvoice in invoice.ProductsInInvoices.ToList())
{
storageDbEntities.ProductsInInvoices.DeleteObject(productsInInvoice);
storageDbEntities.SaveChanges();
}
storageDbEntities.Invoices.DeleteObject(invoice);
storageDbEntities.SaveChanges();
}
}
public static void CreateClient(ClientModel clientModel)
{
if (UserHelper.UserID != null)
{
var storageDbEntities = new StorageDBEntities();
Client newClient = new Client
{
Name = clientModel.Name,
Address = clientModel.Address,
Telephone = clientModel.Telephone,
UserID = UserHelper.UserID.Value
};
storageDbEntities.Clients.AddObject(newClient);
storageDbEntities.SaveChanges();
}
}
public static void CreateProduct(ProductModel productModel)
{
if (UserHelper.UserID != null)
{
var storageDbEntities = new StorageDBEntities();
Product newProduct = new Product
{
Code = productModel.Code,
Name = productModel.Name,
Unit = productModel.Unit,
WholesalePrice = productModel.WholesalePrice,
ShallowWholesalePrice = productModel.ShallowWholesalePrice,
RetailPrice = productModel.RetailPrice,
CategoryID = productModel.Category.ID,
UserID = UserHelper.UserID.Value
};
storageDbEntities.Products.AddObject(newProduct);
storageDbEntities.SaveChanges();
}
}
public static ProductModel GetProduct(int id)
{
var storageDbEntities = new StorageDBEntities();
ProductModel product = (from p in storageDbEntities.Products
where p.ID == id && p.UserID == UserHelper.UserID
select new ProductModel
{
ID = p.ID,
Code = p.Code,
Name = p.Name,
WholesalePrice = p.WholesalePrice,
ShallowWholesalePrice = p.ShallowWholesalePrice,
RetailPrice = p.RetailPrice,
Unit = p.Unit,
Category = new CategoryModel
{
ID = (int?) p.Category.ID,
Name = p.Category.Name
}
}).SingleOrDefault();
return product;
}
public static List<ProductModel> GetProductList()
{
var storageDbEntities = new StorageDBEntities();
List<ProductModel> list = (from p in storageDbEntities.Products
where p.UserID == UserHelper.UserID
select new ProductModel
{
ID = p.ID,
Code = p.Code,
Name = p.Name,
WholesalePrice = p.WholesalePrice,
ShallowWholesalePrice = p.ShallowWholesalePrice,
RetailPrice = p.RetailPrice,
Unit = p.Unit,
Category = new CategoryModel
{
ID = (int?)p.Category.ID,
Name = p.Category.Name
}
}).ToList();
list = list.OrderBy(l => Convert.ToInt32(l.Code)).ToList();
return list;
}
public static List<ProductModel> GetProductListByFilter(int categoryID, string code, string name)
{
var storageDbEntities = new StorageDBEntities();
IQueryable<Product> products = storageDbEntities.Products;
if(categoryID != -1)
{
products = products.Where(p => p.CategoryID == categoryID);
}
if(!String.IsNullOrEmpty(code))
{
products = products.Where(p => p.Code == code);
}
if(!String.IsNullOrEmpty(name))
{
products = products.Where(p => p.Name.Contains(name));
}
List<ProductModel> list = products.Where(p => p.UserID == UserHelper.UserID).Select(p => new ProductModel
{
ID = p.ID,
Code = p.Code,
Name = p.Name,
WholesalePrice = p.WholesalePrice,
ShallowWholesalePrice = p.ShallowWholesalePrice,
RetailPrice = p.RetailPrice,
Unit = p.Unit,
Category = new CategoryModel
{
ID = (int?) p.Category.ID,
Name = p.Category.Name
}
}).ToList();
list = list.OrderBy(l => Convert.ToInt32(l.Code)).ToList();
return list;
}
public static bool IsProductCodeAvailable(string code)
{
var storageDbEntities = new StorageDBEntities();
return storageDbEntities.Products.Where(p => p.Code == code && p.UserID == UserHelper.UserID).FirstOrDefault() == null;
}
public static int GetNextInvoiceNumber(string type)
{
var storageDbEntities = new StorageDBEntities();
var currentNumber = (from inv in storageDbEntities.Invoices
where inv.Type == type && inv.UserID == UserHelper.UserID
select inv.Number).DefaultIfEmpty().Max();
return currentNumber + 1;
}
public static void UpdateProduct(int id, ProductModel productModel)
{
var storageDbEntities = new StorageDBEntities();
Product product = storageDbEntities.Products.Where(p => p.ID == id && p.UserID == UserHelper.UserID).FirstOrDefault();
if (product != null)
{
product.Code = productModel.Code;
product.Name = productModel.Name;
product.Unit = productModel.Unit;
product.WholesalePrice = productModel.WholesalePrice;
product.ShallowWholesalePrice = productModel.ShallowWholesalePrice;
product.RetailPrice = productModel.RetailPrice;
product.CategoryID = productModel.Category.ID;
}
storageDbEntities.SaveChanges();
}
public static void SaveInvoice(InvoiceModel invoiceModel)
{
if (UserHelper.UserID != null)
{
var storageDbEntities = new StorageDBEntities();
Invoice invoice = new Invoice
{
SupplierID = invoiceModel.Supplier.ID,
RecipientID = invoiceModel.Recipient.ID,
Date = invoiceModel.Date,
Type = invoiceModel.Type,
Number = invoiceModel.Number,
PriceType = invoiceModel.PriceType,
UserID = UserHelper.UserID.Value
};
foreach (ProductsInInvoiceModel productsInInvoiceModel in invoiceModel.Products.Where(p => p.ProductID > 0).ToList())
{
ProductsInInvoice productsInInvoice = new ProductsInInvoice
{
Price = productsInInvoiceModel.Price,
Quantity = productsInInvoiceModel.Quantity,
ProductID = productsInInvoiceModel.ProductID
};
invoice.ProductsInInvoices.Add(productsInInvoice);
}
storageDbEntities.Invoices.AddObject(invoice);
storageDbEntities.SaveChanges();
}
}
public static void UpdateInvoice(InvoiceModel invoiceModel)
{
var storageDbEntities = new StorageDBEntities();
Invoice invoice = storageDbEntities.Invoices.Where(inv => inv.ID == invoiceModel.ID && inv.UserID == UserHelper.UserID).FirstOrDefault();
if (invoice != null)
{
// updates invoice metadata
invoice.SupplierID = invoiceModel.Supplier.ID;
invoice.RecipientID = invoiceModel.Recipient.ID;
invoice.PriceType = invoiceModel.PriceType;
foreach (var productInInvoice in invoice.ProductsInInvoices.ToList())
{
ProductsInInvoiceModel productsInInvoiceModel = invoiceModel.Products.Where(p => p.ProductID == productInInvoice.ProductID).FirstOrDefault();
// product was updated by user
if (productsInInvoiceModel != null)
{
productInInvoice.Price = productsInInvoiceModel.Price;
productInInvoice.Quantity = productsInInvoiceModel.Quantity;
invoiceModel.Products.Remove(productsInInvoiceModel);
}
// product was deleted by user
else
{
storageDbEntities.ProductsInInvoices.DeleteObject(productInInvoice);
}
}
// products were created by user
foreach (var productsInInvoiceModel in invoiceModel.Products.Where(p => p.ProductID > 0).ToList())
{
ProductsInInvoice productsInInvoice = new ProductsInInvoice
{
Price = productsInInvoiceModel.Price,
Quantity = productsInInvoiceModel.Quantity,
ProductID = productsInInvoiceModel.ProductID
};
invoice.ProductsInInvoices.Add(productsInInvoice);
}
storageDbEntities.SaveChanges();
}
}
public static InvoiceModel GetInvoice(int id)
{
var storageDbEntities = new StorageDBEntities();
Invoice invoice = storageDbEntities.Invoices.Where(inv => inv.ID == id && inv.UserID == UserHelper.UserID).FirstOrDefault();
if (invoice != null)
{
InvoiceModel invoiceModel = new InvoiceModel
{
Supplier = new ClientModel
{
ID = invoice.Supplier.ID,
Name = invoice.Supplier.Name,
Address = invoice.Supplier.Address,
Telephone = invoice.Supplier.Telephone
},
Recipient = new ClientModel
{
ID = invoice.Recipient.ID,
Name = invoice.Recipient.Name,
Address = invoice.Recipient.Address,
Telephone = invoice.Recipient.Telephone
},
Date = invoice.Date,
Type = invoice.Type,
Number = invoice.Number,
PriceType = invoice.PriceType,
ID = invoice.ID
};
foreach (ProductsInInvoice productsInInvoice in invoice.ProductsInInvoices.ToList())
{
ProductsInInvoiceModel productsInInvoiceModel = new ProductsInInvoiceModel
{
ID = productsInInvoice.ID,
Price = productsInInvoice.Price,
Quantity = productsInInvoice.Quantity,
Total = productsInInvoice.Price * Convert.ToDecimal(productsInInvoice.Quantity),
Product = new ProductModel
{
ID = productsInInvoice.Product.ID,
Name = productsInInvoice.Product.Name,
Code = productsInInvoice.Product.Code,
Unit = productsInInvoice.Product.Unit,
}
};
invoiceModel.Products.Add(productsInInvoiceModel);
invoiceModel.MasterTotal += productsInInvoiceModel.Total;
}
invoiceModel.Products = invoiceModel.Products.OrderBy(p => Convert.ToInt32(p.Product.Code)).ToList();
for (int position = 0; position < invoiceModel.Products.Count; position++)
{
invoiceModel.Products[position].Position = position + 1;
}
return invoiceModel;
}
return null;
}
public static List<InvoiceModel> GetInvoiceList(string type)
{
var storageDbEntities = new StorageDBEntities();
List<InvoiceModel> list = (from inv in storageDbEntities.Invoices
where inv.Type == type && inv.UserID == UserHelper.UserID
select new InvoiceModel
{
ID = inv.ID,
Number = inv.Number,
Date = inv.Date
}
).ToList();
return list;
}