public bool AddInvoice(Invoice invoice, List<Product> products )
{
try
{
using (TransactionScope ts = new TransactionScope())
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
//Invoice newInvoice=new Invoice(){ InvoiceNo = invoice.InvoiceNo, CostPrice = invoice.CostPrice, Due = invoice.Due, InvoiceDate = invoice.InvoiceDate, TotalPrice = invoice.TotalPrice, vat = invoice.vat};
dataContext.Invoices.Add(invoice);
dataContext.SaveChanges();
if (invoice.InvoiceID>0)
{
foreach (Product product in products)
{
SellProduct sellProduct=new SellProduct(){ InvoiceID = invoice.InvoiceID, Price = product.Price, ProductID = product.ProductID, Quantiry = product.Quantity, Size = product.Size};
dataContext.SellProducts.Add(sellProduct);
dataContext.Database.ExecuteSqlCommand("Update Products set Quantity= Quantity-" + product.Quantity + "where ProductID="+product.ProductID);
dataContext.SaveChanges();
}
}
}
ts.Complete();
return true;
}
return false;
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public bool AddCategory(Category category)
{
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
dataContext.Categoets.Add(category);
dataContext.SaveChanges();
return true;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public bool AddCompany(PersonType company)
{
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
dataContext.Companies.Add(company);
dataContext.SaveChanges();
return true;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public bool DeleteCompany(PersonType company)
{
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
dataContext.Companies.Attach(company);
dataContext.Entry(company).State=EntityState.Deleted;
dataContext.SaveChanges();
return true;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public List<SellProduct> GetAllProducts()
{
List<SellProduct> sellProducts = null;
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
sellProducts = dataContext.SellProducts.ToList();
return sellProducts;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public List<Invoice> GetInvoiceWithDate(DateTime fromDate, DateTime toDate)
{
List<Invoice> invoices = null;
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
invoices = dataContext.Invoices.Where(x=>x.InvoiceDate>=fromDate).ToList();
return invoices;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public List<Invoice> GetInvoices()
{
List<Invoice> invoices = null;
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
invoices = dataContext.Invoices.ToList();
return invoices;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public bool AddProduct(Product product)
{
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
dataContext.Products.Add(product);
dataContext.SaveChanges();
return true;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public List<PersonType> GetCompanies()
{
try
{
List<PersonType> companies = null;
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
companies = dataContext.Companies.ToList();
return companies;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public List<Category> GetCategoriesByID()
{
try
{
List<Category> categories = null;
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
categories = dataContext.Categoets.ToList();
return categories;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public bool CheckCategory(Category category)
{
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
Category aCompany = dataContext.Categoets.FirstOrDefault(b => b.Name == category.Name && b.CompanyID==category.CompanyID);
if (aCompany == null)
{
return false;
}
return true;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public bool CheckCompany(PersonType company)
{
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
PersonType aCompany = dataContext.Companies.Where(b => b.Name == company.Name).FirstOrDefault();
if (aCompany==null)
{
return false;
}
return true;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public List<TotalProductReport> GetInvoices(DateTime fromdate, DateTime toDate)
{
List<TotalProductReport> totalProduct=new List<TotalProductReport>();
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
List<TotalProductReport> data = (from inv in dataContext.Invoices
join sp in dataContext.SellProducts on inv.InvoiceID equals sp.InvoiceID
join p in dataContext.Products on sp.ProductID equals p.ProductID
where inv.InvoiceDate >= fromdate && inv.InvoiceDate <= toDate
select new TotalProductReport()
{
InvoiceDate = inv.InvoiceDate,
InvoiceNo = inv.InvoiceNo,
ProductName= p.ProductName,
Quantiry= sp.Quantiry,
Price= sp.Price
}).ToList();
return data;
//(from ep in dbContext.tbl_EntryPoint
// join e in dbContext.tbl_Entry on ep.EID equals e.EID
// join t in dbContext.tbl_Title on e.TID equals t.TID
// where e.OwnerID == user.UID
// select new
// {
// UID = e.OwnerID,
// TID = e.TID,
// Title = t.Title,
// EID = e.EID
// }).Take(10);
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public bool CheckNewBarCode(string barcode)
{
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
Product aProduct = dataContext.Products.FirstOrDefault(x => x.BarCode == barcode);
if (aProduct == null)
{
return true;
}
return false;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public bool CheckProduct(Product product)
{
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
Product aProduct = dataContext.Products.FirstOrDefault(x => x.ProductName == product.ProductName && x.CategoryID == product.CategoryID);
if (aProduct == null)
{
return false;
}
return true;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public bool DeleteProduct(Product product)
{
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
dataContext.Products.Attach(product);
dataContext.Entry(product).State = EntityState.Deleted;
dataContext.SaveChanges();
return true;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public bool UpdateCategory(Category category)
{
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
dataContext.Categoets.Attach(category);
dataContext.Entry(category).State = EntityState.Modified;
dataContext.SaveChanges();
return true;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public bool UpdateProduct(Product product)
{
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
int noOfRowUpdated =
dataContext.Database.ExecuteSqlCommand("Update student set studentname ='changed student by command' where studentid=1");
//dataContext.Products.Attach(product);
//dataContext.Entry<Product>(product).Property()
return true;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
//_cnt.Users.Attach(user);
//_cnt.Entry<User>(user).Property(u => u.PasswordHash).IsModified = true;
//_cnt.SaveChanges();
}
public Product GetDataBarCode(string barCode)
{
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
Product aProduct = dataContext.Products.FirstOrDefault(x => x.BarCode == barCode);
return aProduct;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public UserGateway()
{
dataContext=new SalesAndInventorySystemDataContext();
}
public PersonType GetCompanies(int CompanyID)
{
try
{
PersonType company = null;
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
company = dataContext.Companies.SingleOrDefault(x => x.ID==CompanyID);
return company;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public bool UpdateCompany(PersonType company)
{
try
{
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
dataContext.Companies.Attach(company);
dataContext.Entry(company).State = EntityState.Modified;
dataContext.SaveChanges();
return true;
//_cnt.Users.Attach(user);
//_cnt.Entry<User>(user).Property(u => u.PasswordHash).IsModified = true;
//_cnt.SaveChanges();
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
public Category GetCategoryByID(int CategoryID)
{
try
{
Category category = null;
using (SalesAndInventorySystemDataContext dataContext = new SalesAndInventorySystemDataContext())
{
category = dataContext.Categoets.SingleOrDefault(x => x.CategoryID == CategoryID);
return category;
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
