public ActionResult Search() { //_stockManager.StockReport(); StockViewModel stockViewModel = new StockViewModel(); var purchases = _purchaseManager.GetAll(); var products = _productManager.GetAll(); var categories = _categoryManager.GetAll(); var purchaseDetails = _purchaseManager.GetAllDetails(); var salesDetails = _saleDetailsManager.GetAllSaleDetails(); var report = (from pu in purchaseDetails join Purchases in purchases on new { PurchaseId = pu.PurchaseId } equals new { PurchaseId = Purchases.Id } into Purchases_join from Purchases in Purchases_join.DefaultIfEmpty() join Products in products on new { ProductId = pu.ProductId } equals new { ProductId = Products.Id } into Products_join from Products in Products_join.DefaultIfEmpty() join Categories in categories on new { CategoryId = Products.CategoryId } equals new { CategoryId = Categories.Id } into Categories_join from Categories in Categories_join.DefaultIfEmpty() group new { Purchases, pu, Products, Categories } by new { Purchases.Date, pu.ProductId, Products.Name, Column1 = Categories.Name, Products.Code } into g select new StockViewModel { PurchaseDate = g.Key.Date, Code = g.Key.Code, Name = g.Key.Name, Category = g.Key.Column1, OpeningBalance = (g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g1 select new { Column1 = g1.Sum(p => p.Quantity) }).First().Column1)), Inqty = g.Sum(p => p.pu.Quantity), Out = ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g2 select new { Column1 = g2.Sum(p => p.Quantity) }).First().Column1), ClosingBalance = ((g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g3 select new { Column1 = g3.Sum(p => p.Quantity) }).First().Column1)) + g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g4 select new { Column1 = g4.Sum(p => p.Quantity) }).First().Column1)) }).ToList(); stockViewModel.StockReport = report; stockViewModel.CategorySelectListItems = _categoryManager.GetAll() .Select(c => new SelectListItem() { Value = c.Name, Text = c.Name }).ToList(); stockViewModel.ProductSelectListItems = _productManager.GetAll() .Select(c => new SelectListItem() { Value = c.Name, Text = c.Name }).ToList(); ViewBag.category = stockViewModel.CategorySelectListItems; ViewBag.product = stockViewModel.ProductSelectListItems; return(View(stockViewModel)); }
public ActionResult Search() { PurchaseReportViewModel _purchaseReportViewModel = new PurchaseReportViewModel(); var purchases = _purchaseManager.GetAll(); var products = _productManager.GetAll(); var categories = _categoryManager.GetAll(); var purchaseDetails = _purchaseDetailsManager.GetAll(); var salesDetails = _saleDetailsManager.GetAll(); var report = (from pu in purchaseDetails join Purchases in purchases on new { PurchaseId = pu.PurchaseId } equals new { PurchaseId = Purchases.Id } into Purchases_join from Purchases in Purchases_join.DefaultIfEmpty() join Products in products on new { ProductId = pu.ProductId } equals new { ProductId = Products.Id } into Products_join from Products in Products_join.DefaultIfEmpty() join Categories in categories on new { CategoryId = Products.CategoryId } equals new { CategoryId = Categories.Id } into Categories_join from Categories in Categories_join.DefaultIfEmpty() group new { Purchases, pu, Products, Categories } by new { pu.Purchase.Date, pu.ProductId, Products.Name, Column1 = Categories.Name, Products.Code } into g select new PurchaseReportViewModel { Date = g.Key.Date, Code = g.Key.Code, Name = g.Key.Name, Category = g.Key.Column1, Availableqty = (g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g1 select new { Column1 = g1.Sum(p => p.Quantity) }).First().Column1)), CP = ((g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g2 select new { Column1 = g2.Sum(p => p.Quantity) }).First().Column1)) * g.Sum(p => p.pu.UnitPrice) / g.Count()), SalesPrice = ((g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g3 select new { Column1 = g3.Sum(p => p.Quantity) }).First().Column1)) * g.Sum(p => p.pu.MRP) / g.Count()), Profit = ((g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g4 select new { Column1 = g4.Sum(p => p.Quantity) }).First().Column1)) * g.Sum(p => p.pu.MRP) / g.Count() - (g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g5 select new { Column1 = g5.Sum(p => p.Quantity) }).First().Column1)) * g.Sum(p => p.pu.UnitPrice) / g.Count()) }).ToList(); _purchaseReportViewModel.PurchaseReport = report; return(View(_purchaseReportViewModel)); }
public ActionResult Search(string product, string category, DateTime?startdate, DateTime?enddate) { StockViewModel stockViewModel = new StockViewModel(); var purchases = _purchaseManager.GetAll(); var products = _productManager.GetAll(); var categories = _categoryManager.GetAll(); var purchaseDetails = _purchaseManager.GetAllDetails(); var salesDetails = _saleDetailsManager.GetAllSaleDetails(); if (product != "" && category != "" && startdate != null && enddate != null) { var report = (from pu in purchaseDetails join Purchases in purchases on new { PurchaseId = pu.PurchaseId } equals new { PurchaseId = Purchases.Id } into Purchases_join from Purchases in Purchases_join.DefaultIfEmpty() join Products in products on new { ProductId = pu.ProductId } equals new { ProductId = Products.Id } into Products_join from Products in Products_join.DefaultIfEmpty() join Categories in categories on new { CategoryId = Products.CategoryId } equals new { CategoryId = Categories.Id } into Categories_join from Categories in Categories_join.DefaultIfEmpty() group new { Purchases, pu, Products, Categories } by new { Purchases.Date, pu.ProductId, Products.Name, Column1 = Categories.Name, Products.Code } into g where g.Key.Name == product && g.Key.Column1 == category && g.Key.Date >= startdate && g.Key.Date <= enddate select new StockViewModel { PurchaseDate = g.Key.Date, Code = g.Key.Code, Name = g.Key.Name, Category = g.Key.Column1, OpeningBalance = (g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g1 select new { Column1 = g1.Sum(p => p.Quantity) }).First().Column1)), Inqty = g.Sum(p => p.pu.Quantity), Out = ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g2 select new { Column1 = g2.Sum(p => p.Quantity) }).First().Column1), ClosingBalance = ((g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g3 select new { Column1 = g3.Sum(p => p.Quantity) }).First().Column1)) + g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g4 select new { Column1 = g4.Sum(p => p.Quantity) }).First().Column1)) }).ToList(); stockViewModel.StockReport = report; } // no null else if (product != "" && category == "" && startdate != null && enddate != null) { var report = (from pu in purchaseDetails join Purchases in purchases on new { PurchaseId = pu.PurchaseId } equals new { PurchaseId = Purchases.Id } into Purchases_join from Purchases in Purchases_join.DefaultIfEmpty() join Products in products on new { ProductId = pu.ProductId } equals new { ProductId = Products.Id } into Products_join from Products in Products_join.DefaultIfEmpty() join Categories in categories on new { CategoryId = Products.CategoryId } equals new { CategoryId = Categories.Id } into Categories_join from Categories in Categories_join.DefaultIfEmpty() group new { Purchases, pu, Products, Categories } by new { Purchases.Date, pu.ProductId, Products.Name, Column1 = Categories.Name, Products.Code } into g where g.Key.Name == product && g.Key.Date >= startdate && g.Key.Date <= enddate select new StockViewModel { PurchaseDate = g.Key.Date, Code = g.Key.Code, Name = g.Key.Name, Category = g.Key.Column1, OpeningBalance = (g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g1 select new { Column1 = g1.Sum(p => p.Quantity) }).First().Column1)), Inqty = g.Sum(p => p.pu.Quantity), Out = ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g2 select new { Column1 = g2.Sum(p => p.Quantity) }).First().Column1), ClosingBalance = ((g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g3 select new { Column1 = g3.Sum(p => p.Quantity) }).First().Column1)) + g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g4 select new { Column1 = g4.Sum(p => p.Quantity) }).First().Column1)) }).ToList(); stockViewModel.StockReport = report; } //category null else if (product == "" && category != "" && startdate != null && enddate != null) { var report = (from pu in purchaseDetails join Purchases in purchases on new { PurchaseId = pu.PurchaseId } equals new { PurchaseId = Purchases.Id } into Purchases_join from Purchases in Purchases_join.DefaultIfEmpty() join Products in products on new { ProductId = pu.ProductId } equals new { ProductId = Products.Id } into Products_join from Products in Products_join.DefaultIfEmpty() join Categories in categories on new { CategoryId = Products.CategoryId } equals new { CategoryId = Categories.Id } into Categories_join from Categories in Categories_join.DefaultIfEmpty() group new { Purchases, pu, Products, Categories } by new { Purchases.Date, pu.ProductId, Products.Name, Column1 = Categories.Name, Products.Code } into g where g.Key.Column1 == category && g.Key.Date >= startdate && g.Key.Date <= enddate select new StockViewModel { PurchaseDate = g.Key.Date, Code = g.Key.Code, Name = g.Key.Name, Category = g.Key.Column1, OpeningBalance = (g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g1 select new { Column1 = g1.Sum(p => p.Quantity) }).First().Column1)), Inqty = g.Sum(p => p.pu.Quantity), Out = ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g2 select new { Column1 = g2.Sum(p => p.Quantity) }).First().Column1), ClosingBalance = ((g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g3 select new { Column1 = g3.Sum(p => p.Quantity) }).First().Column1)) + g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g4 select new { Column1 = g4.Sum(p => p.Quantity) }).First().Column1)) }).ToList(); stockViewModel.StockReport = report; } //product null else if (product == "" && category == "" && startdate != null && enddate != null) { var report = (from pu in purchaseDetails join Purchases in purchases on new { PurchaseId = pu.PurchaseId } equals new { PurchaseId = Purchases.Id } into Purchases_join from Purchases in Purchases_join.DefaultIfEmpty() join Products in products on new { ProductId = pu.ProductId } equals new { ProductId = Products.Id } into Products_join from Products in Products_join.DefaultIfEmpty() join Categories in categories on new { CategoryId = Products.CategoryId } equals new { CategoryId = Categories.Id } into Categories_join from Categories in Categories_join.DefaultIfEmpty() group new { Purchases, pu, Products, Categories } by new { Purchases.Date, pu.ProductId, Products.Name, Column1 = Categories.Name, Products.Code } into g where g.Key.Date >= startdate && g.Key.Date <= enddate select new StockViewModel { PurchaseDate = g.Key.Date, Code = g.Key.Code, Name = g.Key.Name, Category = g.Key.Column1, OpeningBalance = (g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g1 select new { Column1 = g1.Sum(p => p.Quantity) }).First().Column1)), Inqty = g.Sum(p => p.pu.Quantity), Out = ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g2 select new { Column1 = g2.Sum(p => p.Quantity) }).First().Column1), ClosingBalance = ((g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g3 select new { Column1 = g3.Sum(p => p.Quantity) }).First().Column1)) + g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g4 select new { Column1 = g4.Sum(p => p.Quantity) }).First().Column1)) }).ToList(); stockViewModel.StockReport = report; } //product & category null else if (product != "" && category == "" && startdate == null && enddate == null) { var report = (from pu in purchaseDetails join Purchases in purchases on new { PurchaseId = pu.PurchaseId } equals new { PurchaseId = Purchases.Id } into Purchases_join from Purchases in Purchases_join.DefaultIfEmpty() join Products in products on new { ProductId = pu.ProductId } equals new { ProductId = Products.Id } into Products_join from Products in Products_join.DefaultIfEmpty() join Categories in categories on new { CategoryId = Products.CategoryId } equals new { CategoryId = Categories.Id } into Categories_join from Categories in Categories_join.DefaultIfEmpty() group new { Purchases, pu, Products, Categories } by new { Purchases.Date, pu.ProductId, Products.Name, Column1 = Categories.Name, Products.Code } into g where g.Key.Name == product select new StockViewModel { PurchaseDate = g.Key.Date, Code = g.Key.Code, Name = g.Key.Name, Category = g.Key.Column1, OpeningBalance = (g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g1 select new { Column1 = g1.Sum(p => p.Quantity) }).First().Column1)), Inqty = g.Sum(p => p.pu.Quantity), Out = ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g2 select new { Column1 = g2.Sum(p => p.Quantity) }).First().Column1), ClosingBalance = ((g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g3 select new { Column1 = g3.Sum(p => p.Quantity) }).First().Column1)) + g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g4 select new { Column1 = g4.Sum(p => p.Quantity) }).First().Column1)) }).ToList(); stockViewModel.StockReport = report; } //product not null else if (product == "" && category != "" && startdate == null && enddate == null) { var report = (from pu in purchaseDetails join Purchases in purchases on new { PurchaseId = pu.PurchaseId } equals new { PurchaseId = Purchases.Id } into Purchases_join from Purchases in Purchases_join.DefaultIfEmpty() join Products in products on new { ProductId = pu.ProductId } equals new { ProductId = Products.Id } into Products_join from Products in Products_join.DefaultIfEmpty() join Categories in categories on new { CategoryId = Products.CategoryId } equals new { CategoryId = Categories.Id } into Categories_join from Categories in Categories_join.DefaultIfEmpty() group new { Purchases, pu, Products, Categories } by new { Purchases.Date, pu.ProductId, Products.Name, Column1 = Categories.Name, Products.Code } into g where g.Key.Column1 == category select new StockViewModel { PurchaseDate = g.Key.Date, Code = g.Key.Code, Name = g.Key.Name, Category = g.Key.Column1, OpeningBalance = (g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g1 select new { Column1 = g1.Sum(p => p.Quantity) }).First().Column1)), Inqty = g.Sum(p => p.pu.Quantity), Out = ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g2 select new { Column1 = g2.Sum(p => p.Quantity) }).First().Column1), ClosingBalance = ((g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g3 select new { Column1 = g3.Sum(p => p.Quantity) }).First().Column1)) + g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g4 select new { Column1 = g4.Sum(p => p.Quantity) }).First().Column1)) }).ToList(); stockViewModel.StockReport = report; } //Category not null else if (product == "" && category == "" && startdate != null && enddate == null) { var report = (from pu in purchaseDetails join Purchases in purchases on new { PurchaseId = pu.PurchaseId } equals new { PurchaseId = Purchases.Id } into Purchases_join from Purchases in Purchases_join.DefaultIfEmpty() join Products in products on new { ProductId = pu.ProductId } equals new { ProductId = Products.Id } into Products_join from Products in Products_join.DefaultIfEmpty() join Categories in categories on new { CategoryId = Products.CategoryId } equals new { CategoryId = Categories.Id } into Categories_join from Categories in Categories_join.DefaultIfEmpty() group new { Purchases, pu, Products, Categories } by new { Purchases.Date, pu.ProductId, Products.Name, Column1 = Categories.Name, Products.Code } into g where g.Key.Date >= startdate select new StockViewModel { PurchaseDate = g.Key.Date, Code = g.Key.Code, Name = g.Key.Name, Category = g.Key.Column1, OpeningBalance = (g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g1 select new { Column1 = g1.Sum(p => p.Quantity) }).First().Column1)), Inqty = g.Sum(p => p.pu.Quantity), Out = ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g2 select new { Column1 = g2.Sum(p => p.Quantity) }).First().Column1), ClosingBalance = ((g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g3 select new { Column1 = g3.Sum(p => p.Quantity) }).First().Column1)) + g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g4 select new { Column1 = g4.Sum(p => p.Quantity) }).First().Column1)) }).ToList(); stockViewModel.StockReport = report; } //startDate not null else if (product == "" && category == "" && startdate == null && enddate != null) { var report = (from pu in purchaseDetails join Purchases in purchases on new { PurchaseId = pu.PurchaseId } equals new { PurchaseId = Purchases.Id } into Purchases_join from Purchases in Purchases_join.DefaultIfEmpty() join Products in products on new { ProductId = pu.ProductId } equals new { ProductId = Products.Id } into Products_join from Products in Products_join.DefaultIfEmpty() join Categories in categories on new { CategoryId = Products.CategoryId } equals new { CategoryId = Categories.Id } into Categories_join from Categories in Categories_join.DefaultIfEmpty() group new { Purchases, pu, Products, Categories } by new { Purchases.Date, pu.ProductId, Products.Name, Column1 = Categories.Name, Products.Code } into g where g.Key.Date <= enddate select new StockViewModel { PurchaseDate = g.Key.Date, Code = g.Key.Code, Name = g.Key.Name, Category = g.Key.Column1, OpeningBalance = (g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g1 select new { Column1 = g1.Sum(p => p.Quantity) }).First().Column1)), Inqty = g.Sum(p => p.pu.Quantity), Out = ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g2 select new { Column1 = g2.Sum(p => p.Quantity) }).First().Column1), ClosingBalance = ((g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g3 select new { Column1 = g3.Sum(p => p.Quantity) }).First().Column1)) + g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g4 select new { Column1 = g4.Sum(p => p.Quantity) }).First().Column1)) }).ToList(); stockViewModel.StockReport = report; } //endDate not null else { var report = (from pu in purchaseDetails join Purchases in purchases on new { PurchaseId = pu.PurchaseId } equals new { PurchaseId = Purchases.Id } into Purchases_join from Purchases in Purchases_join.DefaultIfEmpty() join Products in products on new { ProductId = pu.ProductId } equals new { ProductId = Products.Id } into Products_join from Products in Products_join.DefaultIfEmpty() join Categories in categories on new { CategoryId = Products.CategoryId } equals new { CategoryId = Categories.Id } into Categories_join from Categories in Categories_join.DefaultIfEmpty() group new { Purchases, pu, Products, Categories } by new { Purchases.Date, pu.ProductId, Products.Name, Column1 = Categories.Name, Products.Code } into g select new StockViewModel { PurchaseDate = g.Key.Date, Code = g.Key.Code, Name = g.Key.Name, Category = g.Key.Column1, OpeningBalance = (g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g1 select new { Column1 = g1.Sum(p => p.Quantity) }).First().Column1)), Inqty = g.Sum(p => p.pu.Quantity), Out = ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g2 select new { Column1 = g2.Sum(p => p.Quantity) }).First().Column1), ClosingBalance = ((g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g3 select new { Column1 = g3.Sum(p => p.Quantity) }).First().Column1)) + g.Sum(p => p.pu.Quantity) - ((from sa in salesDetails where sa.ProductId == g.Key.ProductId group sa by new { sa.ProductId } into g4 select new { Column1 = g4.Sum(p => p.Quantity) }).First().Column1)) }).ToList(); stockViewModel.StockReport = report; } stockViewModel.CategorySelectListItems = _categoryManager.GetAll() .Select(c => new SelectListItem() { Value = c.Name, Text = c.Name }).ToList(); stockViewModel.ProductSelectListItems = _productManager.GetAll() .Select(c => new SelectListItem() { Value = c.Name, Text = c.Name }).ToList(); ViewBag.category = stockViewModel.CategorySelectListItems; ViewBag.product = stockViewModel.ProductSelectListItems; stockViewModel.PurchaseDate = Convert.ToDateTime(startdate); stockViewModel.ExpireDate = Convert.ToDateTime(enddate); return(View(stockViewModel)); }
public ActionResult Search() { SalesReportViewModel salesReportViewModel = new SalesReportViewModel(); var purchases = _purchaseManager.GetAll(); var products = _productManager.GetAll(); var categories = _categoryManager.GetAll(); var purchaseDetails = _purchaseManager.GetAllDetails(); var salesDetails = _salesManager.GetAllSaleDetails(); var report = (from sa in salesDetails join Products in products on new { ProductId = sa.ProductId } equals new { ProductId = Products.Id } into Products_join from Products in Products_join.DefaultIfEmpty() join Categories in categories on new { CategoryId = Products.CategoryId } equals new { CategoryId = Categories.Id } into Categories_join from Categories in Categories_join.DefaultIfEmpty() group new { sa.SalesMaster, sa, Products, Categories } by new { sa.SalesMaster.Date, sa.ProductId, Products.Name, Products.Code, Column1 = Categories.Name } into g select new SalesReportViewModel { Date = g.Key.Date, Code = g.Key.Code, Name = g.Key.Name, Category = g.Key.Column1, Soldqnty = g.Sum(p => p.sa.Quantity), CP = g.Sum(p => p.sa.Quantity) * ((from Pu in purchaseDetails where Pu.ProductId == g.Key.ProductId group Pu by new { Pu.ProductId } into g1 select new { Column1 = (g1.Sum(p => p.UnitPrice) / g.Count()) }).First().Column1), SalesPrice = (g.Sum(p => p.sa.Quantity) * ((from Pu in purchaseDetails where Pu.ProductId == g.Key.ProductId group Pu by new { Pu.ProductId } into g2 select new { Column1 = (g2.Sum(p => p.MRP) / g.Count()) }).First().Column1)), Profit = (g.Sum(p => p.sa.Quantity) * ((from Pu in purchaseDetails where Pu.ProductId == g.Key.ProductId group Pu by new { Pu.ProductId } into g3 select new { Column1 = (g3.Sum(p => p.MRP) / g.Count()) }).First().Column1) - g.Sum(p => p.sa.Quantity) * ((from Pu in purchaseDetails where Pu.ProductId == g.Key.ProductId group Pu by new { Pu.ProductId } into g4 select new { Column1 = (g4.Sum(p => p.UnitPrice) / g.Count()) }).First().Column1)) }).ToList(); salesReportViewModel.SalesReport = report; return(View(salesReportViewModel)); }