public bool CreateSale(SalesDto sale) { using (var dbContext = new SalesBogEntities()) { var dbSale = new Sales { ConsultantID = sale.ConsultantID, SaleDescription = sale.SaleDescription, SaleDate = DateTime.Now }; var prodSales = new List <ProductSales>(); var products = new List <Products>(); foreach (var item in sale.Products) { prodSales.Add(new ProductSales { ProductID = item.ID, Sales = dbSale, ProductCount = item.ProductCount }); } dbContext.ProductSales.AddRange(prodSales); return(dbContext.SaveChanges() > 0 ? true : false); } }
public List <SaleConsultantProductsDto> GetSalesByProductPrice(DateTime startDate, DateTime endDate, decimal minPrice, decimal maxPrice) { using (var dbContext = new SalesBogEntities()) { var result = (from s in dbContext.Sales join ps in dbContext.ProductSales on s.ID equals ps.SaleID join p in dbContext.Products on ps.ProductID equals p.ID join c in dbContext.Consultants on s.ConsultantID equals c.ID where s.SaleDate >= startDate && s.SaleDate < endDate && p.Price >= minPrice && p.Price < maxPrice group ps by new { SaleID = s.ID, s.ConsultantID, s.SaleDate, c.PersonalNumber, FullName = c.FirstName + " " + c.LastName } into g select new SaleConsultantProductsDto { SaleID = g.Key.SaleID, SaleDate = (DateTime)g.Key.SaleDate, ConsultantID = (int)g.Key.ConsultantID, PersonalNumber = g.Key.PersonalNumber, FullName = g.Key.FullName, Quantity = g.Select(l => l.ProductID).Distinct().Count() }).ToList(); return(result); } }
public SalesDto GetSaleById(int?id) { using (var dbContext = new SalesBogEntities()) { var dbSale = dbContext.Sales.Find(id); SalesDto saleDto = new SalesDto { ID = dbSale.ID, Consultant = new ConsultantDto { ID = dbSale.ID, FirstName = dbSale.Consultants.FirstName, LastName = dbSale.Consultants.LastName }, SaleDate = dbSale.SaleDate, SaleDescription = dbSale.SaleDescription, Products = dbSale.ProductSales.Select(s => new ProductDto { ID = s.Products.ID, Price = (decimal)s.Products.Price, ProductCode = s.Products.ProductCode, ProductName = s.Products.ProductName, ProductCount = (int)s.ProductCount }).ToList() }; return(saleDto); } }
public List <SaleConsultantProductsDto> GetConsultantsBySummedSales(DateTime?startDate, DateTime?endDate) { using (var dbContext = new SalesBogEntities()) { var consultants = dbContext.Consultants.ToList(); Dictionary <int, int> consultantHierarchySums = new Dictionary <int, int>(); var allConsultantOwnSaleSums = (from c in dbContext.Consultants join s in dbContext.Sales on c.ID equals s.ConsultantID into sf from sl in sf.DefaultIfEmpty() join sp in dbContext.ProductSales on sl.ID equals sp.SaleID into spf from spl in spf.DefaultIfEmpty() where startDate == null && endDate != null ? sl.SaleDate < endDate : endDate == null && startDate != null ? sl.SaleDate >= startDate : startDate != null && endDate != null ? sl.SaleDate >= startDate && sl.SaleDate < endDate : 1 == 1 group sl by new { c.ID } into g select new { g.Key.ID, Count = g.Distinct().Count(p => p.ID != null) }).ToDictionary(d => d.ID, d => d.Count); foreach (var consultant in consultants) { IEnumerable <Consultants> allNodes = TraverseHierarchy(consultant, node => node.Consultants1); var ids = allNodes.Select(s => s.ID); var res = (from c in dbContext.Consultants join s in dbContext.Sales on c.ID equals s.ConsultantID into sf from sl in sf.DefaultIfEmpty() join sp in dbContext.ProductSales on sl.ID equals sp.SaleID into spf from spl in spf.DefaultIfEmpty() where startDate == null && endDate != null ? sl.SaleDate < endDate : endDate == null && startDate != null ? sl.SaleDate >= startDate : startDate != null && endDate != null ? sl.SaleDate >= startDate && sl.SaleDate < endDate : 1 == 1 && ids.Contains(c.ID) group sl by new { c.ID } into g select new { g.Key.ID, Count = g.Distinct().Count(p => p.ID != null) }).Sum(q => q.Count); consultantHierarchySums.Add(consultant.ID, res); } var result = (from c in consultants join k in consultantHierarchySums on c.ID equals k.Key join s in allConsultantOwnSaleSums on c.ID equals s.Key group new { k, s } by new { c.ID, c.BirthDate, c.PersonalNumber, FullName = c.FirstName + " " + c.LastName } into g2 select new SaleConsultantProductsDto { ConsultantBirthDate = (DateTime)g2.Key.BirthDate, ConsultantID = (int)g2.Key.ID, PersonalNumber = g2.Key.PersonalNumber, FullName = g2.Key.FullName, Quantity = g2.Sum(q => q.s.Value), QuantityOverHierarchy = g2.Sum(q => q.k.Value) }).OrderByDescending(o => o.QuantityOverHierarchy).ToList(); return(result); } }
public List <SalesDto> GetSales() { using (var dbContext = new SalesBogEntities()) { var dbSales = dbContext.Sales.ToList(); List <SalesDto> sales = new List <SalesDto>(); foreach (var item in dbSales) { sales.Add(new SalesDto { ID = item.ID, SaleDate = item.SaleDate, SaleDescription = item.SaleDescription, ConsultantID = (int)item.ConsultantID, Consultant = new ConsultantDto { FirstName = item.Consultants.FirstName, LastName = item.Consultants.LastName, ID = item.Consultants.ID, PersonalNumber = item.Consultants.PersonalNumber }, Products = item.ProductSales.Select( s => new ProductDto { ID = s.Products.ID, Price = (decimal)s.Products.Price, ProductName = s.Products.ProductName }).ToList() }); } return(sales); } }
public bool EditSales(SalesDto sale) { using (var dbContext = new SalesBogEntities()) { var dbSale = dbContext.Sales.Where(w => w.ID == sale.ID).FirstOrDefault(); dbSale.SaleDescription = sale.SaleDescription; dbSale.ConsultantID = sale.ConsultantID; //Existing Products Edit var existingDBProducts = dbContext.ProductSales.Where(w => w.SaleID == sale.ID).ToList(); var modelProducts = sale.Products.Where(w => w.IsDeleted == false && w.ID != 0).ToList(); var equalProducts = (from e in existingDBProducts join m in modelProducts on e.ProductID equals m.ID where e.SaleID == sale.ID select e).ToList(); if (equalProducts.Count > 0) { foreach (var item in equalProducts) { var correspondingProduct = modelProducts.Where(w => w.ID == item.ProductID).FirstOrDefault(); item.ProductID = correspondingProduct.ID; item.ProductCount = correspondingProduct.ProductCount; } } //New Products Add var newProducts = modelProducts.Where(s => !existingDBProducts.Where(w => w.SaleID == sale.ID).Select(a => a.ProductID).Contains(s.ID)).ToList(); if (newProducts.Count() > 0) { var prodSales = new List <ProductSales>(); foreach (var item in newProducts) { prodSales.Add(new ProductSales { ProductID = item.ID, Sales = dbSale, ProductCount = item.ProductCount }); } dbContext.ProductSales.AddRange(prodSales); } // Deleted Product var deletedProducts = sale.Products.Where(w => w.IsDeleted == true).ToList(); if (deletedProducts.Count() > 0) { foreach (var item in deletedProducts) { var deletedItem = dbContext.ProductSales.Where(w => w.ProductID == item.ID && w.SaleID == dbSale.ID).FirstOrDefault(); dbContext.ProductSales.Remove(deletedItem); } } return(dbContext.SaveChanges() > 0 ? true : false); } }
public bool DeleteProduct(int id) { using (var dbContext = new SalesBogEntities()) { Products prod = dbContext.Products.Find(id); dbContext.Products.Remove(prod); return(dbContext.SaveChanges() > 0 ? true : false); } }
public bool DeleteConsultant(int id) { using (var dbContext = new SalesBogEntities()) { Consultants consultant = dbContext.Consultants.Find(id); dbContext.Consultants.Remove(consultant); return(dbContext.SaveChanges() > 0 ? true : false); } }
public bool EditProduct(ProductDto product) { using (var dbContext = new SalesBogEntities()) { var dbProduct = dbContext.Products.Where(s => s.ID == product.ID).FirstOrDefault(); dbProduct.Price = product.Price; dbProduct.ProductCode = product.ProductCode; dbProduct.ProductName = product.ProductName; return(dbContext.SaveChanges() > 0 ? true : false); } }
public bool DeleteSale(int id) { using (var dbContext = new SalesBogEntities()) { Sales sale = dbContext.Sales.Find(id); dbContext.Sales.Remove(sale); return(dbContext.SaveChanges() > 0 ? true : false); } }
public bool CreateProduct(ProductDto product) { using (var dbContext = new SalesBogEntities()) { dbContext.Products.Add(new Products { Price = product.Price, ProductCode = product.ProductCode, ProductName = product.ProductName }); return(dbContext.SaveChanges() > 0 ? true : false); } }
public bool EditConultant(ConsultantDto consultant) { using (var dbContext = new SalesBogEntities()) { var dbConsultant = dbContext.Consultants.Where(s => s.ID == consultant.ID).FirstOrDefault(); dbConsultant.FirstName = consultant.FirstName; dbConsultant.LastName = consultant.LastName; dbConsultant.Gender = consultant.Gender; dbConsultant.BirthDate = consultant.BirthDate; dbConsultant.RecommenderConsultantID = consultant.RecommenderConsultantID; dbConsultant.PersonalNumber = consultant.PersonalNumber; return(dbContext.SaveChanges() > 0 ? true : false); } }
public ProductDto GetProductById(int id) { using (var dbContext = new SalesBogEntities()) { var dbProduct = dbContext.Products.Find(id); ProductDto data = new ProductDto { ID = dbProduct.ID, Price = (decimal)dbProduct.Price, ProductName = dbProduct.ProductName, ProductCode = dbProduct.ProductCode }; return(data); } }
public bool CreateConsultant(ConsultantDto consultant) { using (var dbContext = new SalesBogEntities()) { dbContext.Consultants.Add(new Consultants { FirstName = consultant.FirstName, LastName = consultant.LastName, BirthDate = consultant.BirthDate, PersonalNumber = consultant.PersonalNumber, Gender = consultant.Gender, RecommenderConsultantID = consultant.RecommenderConsultantID }); return(dbContext.SaveChanges() > 0 ? true : false); } }
public bool CheckIsConsultantRecommender(int?id, string personalNo) { using (var dbContext = new SalesBogEntities()) { if (id != null) { var currentRecommenderConsultant = dbContext.Consultants.Where(w => w.ID == id).FirstOrDefault(); while (currentRecommenderConsultant.PersonalNumber != personalNo) { return(CheckIsConsultantRecommender(currentRecommenderConsultant.RecommenderConsultantID, personalNo)); } return(true); } return(false); } }
public ConsultantDto GetConsultantById(int id) { using (var dbContext = new SalesBogEntities()) { var dbConsultant = dbContext.Consultants.Find(id); ConsultantDto consultantDto = new ConsultantDto { ID = dbConsultant.ID, FirstName = dbConsultant.FirstName, LastName = dbConsultant.LastName, PersonalNumber = dbConsultant.PersonalNumber, BirthDate = dbConsultant.BirthDate, Gender = dbConsultant.Gender, RecommenderConsultantID = dbConsultant.RecommenderConsultantID }; return(consultantDto); } }
public List <ProductDto> GetProducts() { using (var dbContext = new SalesBogEntities()) { var dbProducts = dbContext.Products.ToList(); List <ProductDto> data = new List <ProductDto>(); foreach (var item in dbProducts) { data.Add(new ProductDto { ID = item.ID, Price = (decimal)item.Price, ProductCode = item.ProductCode, ProductName = item.ProductName }); } return(data); } }
public List <ConsultantDto> GetConsultants() { using (var dbContext = new SalesBogEntities()) { var dbConsultants = dbContext.Consultants.ToList(); List <ConsultantDto> consultants = new List <ConsultantDto>(); foreach (var item in dbConsultants) { consultants.Add(new ConsultantDto { FirstName = item.FirstName, LastName = item.LastName, Gender = item.Gender, BirthDate = item.BirthDate.Value.Date, ID = item.ID, PersonalNumber = item.PersonalNumber }); } return(consultants); } }
public List <SaleConsultantProductsDto> GetConsultantsByProductQuantity(DateTime startDate, DateTime endDate, string productCode, decimal minQuantityOfProducts) { using (var dbContext = new SalesBogEntities()) { var result = (from s in dbContext.Sales join ps in dbContext.ProductSales on s.ID equals ps.SaleID join p in dbContext.Products on ps.ProductID equals p.ID join c in dbContext.Consultants on s.ConsultantID equals c.ID where s.SaleDate >= startDate && s.SaleDate < endDate && (string.IsNullOrEmpty(productCode) ? 1 == 1 : p.ProductCode == productCode) group ps by new { s.ConsultantID, c.BirthDate, c.PersonalNumber, FullName = c.FirstName + " " + c.LastName, p.ProductCode } into g where g.Count() > minQuantityOfProducts select new SaleConsultantProductsDto { ConsultantBirthDate = (DateTime)g.Key.BirthDate, ConsultantID = (int)g.Key.ConsultantID, PersonalNumber = g.Key.PersonalNumber, FullName = g.Key.FullName, ProductCode = g.Key.ProductCode, Quantity = g.Count() }).ToList(); return(result); } }
public List <SaleConsultantProductsDto> GetConsultantsByTopSoldProducts(DateTime?startDate, DateTime?endDate) { using (var dbContext = new SalesBogEntities()) { var consultants = dbContext.Consultants.ToList(); var products = dbContext.Products.ToList(); List <ConsultantSale> allConsultantsTopSales = new List <ConsultantSale>(); List <ConsultantSale> allConsultantsTopProfitableSales = new List <ConsultantSale>(); foreach (var consultant in consultants) { var consultantTopSale = (from c in dbContext.Consultants join s in dbContext.Sales on c.ID equals s.ConsultantID into sf from sl in sf.DefaultIfEmpty() join sp in dbContext.ProductSales on sl.ID equals sp.SaleID into spf from spl in spf.DefaultIfEmpty() where startDate == null && endDate != null ? sl.SaleDate < endDate : endDate == null && startDate != null ? sl.SaleDate >= startDate : startDate != null && endDate != null ? sl.SaleDate >= startDate && sl.SaleDate < endDate : 1 == 1 && c.ID == consultant.ID group sl by new { c.ID, spl.ProductID } into g select new ConsultantSale { ConsultantID = g.Key.ID, ProductID = g.Key.ProductID, MaxValue = g.Distinct().Count(p => p.ID != null) }).OrderByDescending(d => d.MaxValue).FirstOrDefault(); var consultantTopProfitableSale = (from c in dbContext.Consultants join s in dbContext.Sales on c.ID equals s.ConsultantID into sf from sl in sf.DefaultIfEmpty() join sp in dbContext.ProductSales on sl.ID equals sp.SaleID into spf from spl in spf.DefaultIfEmpty() where startDate == null && endDate != null ? sl.SaleDate < endDate : endDate == null && startDate != null ? sl.SaleDate >= startDate : startDate != null && endDate != null ? sl.SaleDate >= startDate && sl.SaleDate < endDate : 1 == 1 && c.ID == consultant.ID group spl by new { c.ID, spl.ProductID } into g select new ConsultantSale { ConsultantID = g.Key.ID, ProductID = g.Key.ProductID, MaxValue = g.Sum(s => s.ProductCount * s.Products.Price) }).OrderByDescending(d => d.MaxValue).FirstOrDefault(); allConsultantsTopSales.Add(consultantTopSale); allConsultantsTopProfitableSales.Add(consultantTopProfitableSale); } var result = (from c in consultants join k in allConsultantsTopSales on c.ID equals k.ConsultantID join s in allConsultantsTopProfitableSales on c.ID equals s.ConsultantID join p1 in products on k.ProductID equals p1.ID join p2 in products on s.ProductID equals p2.ID group new { k, s } by new { c.ID, c.BirthDate, c.PersonalNumber, FullName = c.FirstName + " " + c.LastName, TopCode = p1.ProductCode, TopName = p1.ProductName, ProfitableCode = p2.ProductCode, ProfitableName = p2.ProductName } into g2 select new SaleConsultantProductsDto { ConsultantBirthDate = (DateTime)g2.Key.BirthDate, ConsultantID = (int)g2.Key.ID, PersonalNumber = g2.Key.PersonalNumber, FullName = g2.Key.FullName, TopSoldProductCode = g2.Key.TopCode, TopSoldProductName = g2.Key.TopName, TopSoldProductTotalQuantity = (int)g2.Sum(s => s.k.MaxValue), TopProfitableProductCode = g2.Key.ProfitableCode, TopProfitableProductName = g2.Key.ProfitableName, TopProfitableProductTotalAmount = (decimal)g2.Sum(s => s.s.MaxValue) }).OrderByDescending(o => o.TopProfitableProductTotalAmount).ToList(); return(result); } }