public decimal GetServiceEarnings(ReportSearchRequest request) { // SELECT SUM(Price) AS Total FROM ServiceResponse AS SR // INNER JOIN[Service] AS S ON S.ID = SR.ServiceID // WHERE YEAR(S.DateCreated) = 2020 AND MONTH(S.DateCreated) = 1 AND SR.Approved = 1 if (request != null) { var result = _context.ServiceResponse .Join(_context.Service, SR => SR.ServiceId, S => S.Id, (SR, S) => new { ServiceResponse = SR, Service = S }) .Where(i => i.Service.DateCreated.Year == request.year && i.Service.DateCreated.Month == request.month) .Where(i => i.ServiceResponse.Approved) .Sum(i => i.ServiceResponse.Price); return(result); } else { var result = _context.ServiceResponse .Join(_context.Service, SR => SR.ServiceId, S => S.Id, (SR, S) => new { ServiceResponse = SR, Service = S }) .Where(i => i.ServiceResponse.Approved) .Sum(i => i.ServiceResponse.Price); return(result); } }
public EToolService.Model.Models.Product GetMostSellingProduct(ReportSearchRequest request) { // QUERY: // SELECT* FROM Product WHERE ID = ( // SELECT ProductID FROM( // SELECT TOP 1 PO.ProductID AS ProductID, SUM(PO.Quantity) AS Quantity, COUNT(PO.ProductID) AS[Count] // FROM ProductOrder AS PO // INNER JOIN[Order] AS O ON O.ID = PO.OrderID // WHERE YEAR(O.OrderDate) = 2020 AND MONTH(O.OrderDate) = 1 // GROUP BY ProductID // ORDER BY Quantity DESC, [Count] DESC) AS MostSelling) StringBuilder query = new StringBuilder(); query.AppendLine(@"SELECT * FROM Product WHERE ID = ("); query.AppendLine(@"SELECT ProductID FROM("); query.AppendLine(@"SELECT TOP 1 PO.ProductID AS ProductID, SUM(PO.Quantity) AS Quantity, COUNT(PO.ProductID) AS [Count]"); query.AppendLine(@"FROM ProductOrder AS PO"); query.AppendLine(@"INNER JOIN [Order] AS O ON O.ID = PO.OrderID"); query.AppendLine(@"WHERE YEAR(O.OrderDate) = " + request.year.ToString() + " AND MONTH(O.OrderDate) = " + request.month.ToString()); query.AppendLine(@"GROUP BY ProductID"); query.AppendLine(@"ORDER BY Quantity DESC, [Count] DESC) AS MostSelling)"); var product = _context.Product.FromSqlRaw(query.ToString()).FirstOrDefault(); return(_mapper.Map <EToolService.Model.Models.Product>(product)); }
/// <summary> /// /// </summary> /// <param name="request"></param> /// <returns></returns> public IQueryable <Report> QueryByRequest(ReportSearchRequest request) { var reports = _reportRepository.AsNoTracking().AsQueryable(); if (request.CreatedById != null) { reports = reports.Where(model => model.CreatedById == request.CreatedById); } //reports = reports.OrderBy($"{request.SortMember} {request.SortDirection}"); return(reports); }
public int GetServiceCount(ReportSearchRequest request) { if (request != null) { return(_context.ServiceResponse .Join(_context.Service, SR => SR.ServiceId, S => S.Id, (SR, S) => new { ServiceResponse = SR, Service = S }) .Where(x => x.Service.DateCreated.Month == request.month && x.Service.DateCreated.Year == request.year) .Where(x => (bool)x.ServiceResponse.Approved) .ToList() .Count()); } return(_context.Service.ToList().Count()); }
public int GetSoldProducts(ReportSearchRequest request) { // QUERY: // SELECT SUM(Quantity) // FROM[Order] AS O // INNER JOIN ProductOrder AS PO ON O.ID = PO.OrderID // WHERE YEAR(O.OrderDate) = {request.year} AND MONTH(O.OrderDate) = {request.month} var result = _context.Order .Join(_context.ProductOrder, t => t.Id, a => a.OrderId, (t, a) => new { Order = t, ProductOrder = a }) .Where(i => i.Order.OrderDate.Year == request.year && i.Order.OrderDate.Month == request.month) .Sum(i => i.ProductOrder.Quantity); return(result); }
public Model.Models.Customer GetTopCustomer(ReportSearchRequest request) { StringBuilder query = new StringBuilder(); query.AppendLine(@"SELECT * FROM Customer WHERE ID = ("); query.AppendLine(@"SELECT CustomerID FROM("); query.AppendLine(@"SELECT TOP 1 CustomerID, Count(CustomerID) AS [Count]"); query.AppendLine(@"FROM [Order]"); if (request != null) { query.AppendLine(@"WHERE YEAR(OrderDate) = " + request.year + " AND MONTH(OrderDate) = " + request.month); } query.AppendLine(@"GROUP BY CustomerID"); query.AppendLine(@"ORDER BY [Count] DESC) AS Customers)"); var customer = _context.Customer.FromSqlRaw(query.ToString()).FirstOrDefault(); return(_mapper.Map <Model.Models.Customer>(customer)); }
public List <EToolService.Model.Models.Services> GetServices(ReportSearchRequest request) { var query = _context.Service .Include(x => x.Customer) .Join(_context.ServiceResponse, s => s.Id, sp => sp.ServiceId, (s, sp) => new { Service = s, ServiceResponse = sp }) .AsQueryable(); if (request != null) { query = query.Where(x => x.Service.DateCreated.Year == request.year && x.Service.DateCreated.Month == request.month) .Where(x => x.ServiceResponse.Approved); } var data = query.ToList(); List <EToolService.Model.Models.Services> services = new List <EToolService.Model.Models.Services>(); foreach (var obj in data) { services.Add(new EToolService.Model.Models.Services { Approved = obj.ServiceResponse.Approved, Attachment = obj.Service.Attachment, CustomerId = obj.Service.CustomerId, CustomerName = obj.Service.Customer.CompanyName, DateCreated = obj.Service.DateCreated, Id = obj.Service.Id, Note = obj.Service.Note, Price = obj.ServiceResponse.Price, ServiceType = obj.Service.ServiceType, ToolName = obj.Service.ToolName, Urgent = obj.Service.Urgent }); } return(services); }
public decimal GetProductionEarnings(ReportSearchRequest request) { // SELECT SUM(P.Price) FROM Product AS P // INNER JOIN ProductOrder AS PO ON P.ID = PO.ProductID // INNER JOIN[Order] AS O ON O.ID = PO.OrderID // WHERE YEAR(O.OrderDate) = 2020 AND MONTH(O.OrderDate) = 1 if (request != null) { var result = _context.Product .Join(_context.ProductOrder, p => p.Id, po => po.ProductId, (p, po) => new { Product = p, ProductOrder = po }) .Join(_context.Order, entry => entry.ProductOrder.OrderId, o => o.Id, (entry, o) => new { Product = entry.Product, ProductOrder = entry.ProductOrder, Order = o }) .Where(x => x.Order.OrderDate.Year == request.year && x.Order.OrderDate.Month == request.month) .Sum(x => (x.Product.Price * (decimal)(1 - x.Product.Discount)) * x.ProductOrder.Quantity); return(result); } else { var result = _context.Product .Join(_context.ProductOrder, p => p.Id, po => po.ProductId, (p, po) => new { Product = p, ProductOrder = po }) .Join(_context.Order, entry => entry.ProductOrder.OrderId, o => o.Id, (entry, o) => new { Product = entry.Product, ProductOrder = entry.ProductOrder, Order = o }) .Sum(x => (x.Product.Price * (decimal)(1 - x.Product.Discount)) * x.ProductOrder.Quantity); return(result); } }
private async Task <EToolService.Model.Models.Product> GetMostSellingProduct(ReportSearchRequest request) { return(await _service.GetWithUrl <EToolService.Model.Models.Product>(request, "MostSelling")); }
private async Task <int> GetProductCount(ReportSearchRequest request) { return(await _service.GetWithUrl <int>(request, "ProductCount")); }
private async void btnPdf_Click(object sender, EventArgs e) { var months = (List <KeyValuePair <int, string> >)comboMjesec.DataSource; var year = int.Parse(comboGodina.SelectedValue.ToString()); var month = months[comboMjesec.SelectedIndex].Value; var fileName = month.ToLower() + "_" + year.ToString() + ".pdf"; var path = Path.Combine(Directory.GetCurrentDirectory(), "Data", "Reports", $"izvjestaj_{fileName}"); // var path = "..\\..\\Data\\Reports\\izvjestaj_" + fileName; var mjesec = int.Parse(comboMjesec.SelectedValue.ToString()); var godina = int.Parse(comboGodina.SelectedValue.ToString()); var request = new ReportSearchRequest { year = godina, month = mjesec }; var tableData = await _service.GetWithUrl <List <EToolService.Model.Models.Services> >(request, "GetServices"); PdfWriter writer = new PdfWriter(path); PdfDocument pdf = new PdfDocument(writer); Document document = new Document(pdf); Paragraph header = new Paragraph("Izvještaj za mjesec " + month + " " + year + ". godine") .SetTextAlignment(TextAlignment.CENTER) .SetFontSize(20); document.Add(header); document.Add(new Paragraph(new Text("\n"))); document.Add(new Paragraph("Ukupan broj servisiranih alata: " + await GetNumberOfServices(request))); document.Add(new Paragraph("Mjesečna zarada na servisiranju: " + await GetServiceEarnings(request) + " KM")); document.Add(new Paragraph("Broj reparisanih alata: " + tableData.Where(x => x.ServiceType.Equals("Reparacija")).Count())); document.Add(new Paragraph("Broj naoštrenih alata: " + tableData.Where(x => x.ServiceType.Equals("Oštrenje")).Count())); document.Add(new Paragraph(new Text("\n"))); float[] columnWidths = { 1, 5, 4, 3.5f, 5.5f, 2.3f, 4 }; Table table = new Table(UnitValue.CreatePercentArray(columnWidths)); table.SetHorizontalAlignment(iText.Layout.Properties.HorizontalAlignment.CENTER); Cell cell = new Cell(1, 7) .Add(new Paragraph("Prikaz servisiranih alata")) .SetFontSize(13) .SetFontColor(DeviceGray.BLACK) .SetBackgroundColor(DeviceGray.WHITE) .SetTextAlignment(TextAlignment.CENTER); table.AddHeaderCell(cell); Cell[] headerFooter = new Cell[] { new Cell().SetTextAlignment(TextAlignment.CENTER).SetBackgroundColor(new DeviceGray(0.75f)).Add(new Paragraph("RB.")), new Cell().SetTextAlignment(TextAlignment.CENTER).SetBackgroundColor(new DeviceGray(0.75f)).Add(new Paragraph("Naziv alata")), new Cell().SetTextAlignment(TextAlignment.CENTER).SetBackgroundColor(new DeviceGray(0.75f)).Add(new Paragraph("Tip alata")), new Cell().SetTextAlignment(TextAlignment.CENTER).SetBackgroundColor(new DeviceGray(0.75f)).Add(new Paragraph("Datum kreiranja")), new Cell().SetTextAlignment(TextAlignment.CENTER).SetBackgroundColor(new DeviceGray(0.75f)).Add(new Paragraph("Kupac")), new Cell().SetTextAlignment(TextAlignment.CENTER).SetBackgroundColor(new DeviceGray(0.75f)).Add(new Paragraph("Potvrdjeno")), new Cell().SetTextAlignment(TextAlignment.CENTER).SetBackgroundColor(new DeviceGray(0.75f)).Add(new Paragraph("Cijena")) }; foreach (Cell hfCell in headerFooter) { table.AddHeaderCell(hfCell); } int counter = 1; tableData = tableData.OrderBy(x => x.ServiceType).ToList(); if (tableData != null) { foreach (var service in tableData) { table.AddCell(new Cell().SetTextAlignment(TextAlignment.CENTER).Add(new Paragraph(counter++.ToString()))); table.AddCell(new Cell().SetTextAlignment(TextAlignment.CENTER).Add(new Paragraph(service.ToolName))); table.AddCell(new Cell().SetTextAlignment(TextAlignment.CENTER).Add(new Paragraph(service.ServiceType))); table.AddCell(new Cell().SetTextAlignment(TextAlignment.CENTER).Add(new Paragraph(service.DateCreated.ToString("dd/MM/yyyy")))); table.AddCell(new Cell().SetTextAlignment(TextAlignment.CENTER).Add(new Paragraph(service.CustomerName))); table.AddCell(new Cell().SetTextAlignment(TextAlignment.CENTER).Add(new Paragraph(service.Approved ? "DA" : "NE"))); table.AddCell(new Cell().SetTextAlignment(TextAlignment.CENTER).Add(new Paragraph(service.Price.ToString("N") + " KM"))); } } Cell sum = new Cell(1, 7) .Add(new Paragraph("Ukupno: " + tableData?.Where(x => x.Approved).Sum(x => x.Price).ToString("N") + " KM")) .SetFontSize(13) .SetFontColor(DeviceGray.BLACK) .SetBackgroundColor(DeviceGray.WHITE) .SetTextAlignment(TextAlignment.RIGHT) .SetBold(); document.Add(table); document.Add(sum); document.Close(); System.Diagnostics.Process.Start(path); }
private async void Reload() { try { var month = int.Parse(comboMjesec.SelectedValue.ToString()); var year = int.Parse(comboGodina.SelectedValue.ToString()); var request = new ReportSearchRequest { year = year, month = month }; var productCount = await GetProductCount(request); valBrojProdanih.Text = productCount.ToString(); var mostSelling = await GetMostSellingProduct(request); if (mostSelling != null) { valNajprodavaniji.Text = mostSelling.ProductName; valNajprodavaniji.ForeColor = System.Drawing.Color.SeaGreen; } else { valNajprodavaniji.Text = "Nema prodanih proizvoda"; valNajprodavaniji.ForeColor = System.Drawing.Color.DarkRed; } var months = (List <KeyValuePair <int, string> >)comboMjesec.DataSource; lblSubtitle.Text = "Izvještaj za mjesec " + months[comboMjesec.SelectedIndex].Value + " " + year.ToString() + ". godine"; var numberOfServices = await GetNumberOfServices(request); valBrojServisa.Text = numberOfServices.ToString(); var serviceEarnings = await GetServiceEarnings(request); valUkupnoServis.Text = serviceEarnings.ToString("N") + " KM"; var productionEarnings = await GetProductionEarnings(request); valUkupnoProizvodnja.Text = productionEarnings.ToString("N") + " KM"; var topCustomer = await GetTopCustomer(request); if (topCustomer != null) { valNajboljiKupac.Text = topCustomer.CompanyName; valNajboljiKupac.ForeColor = System.Drawing.Color.SeaGreen; } else { valNajboljiKupac.Text = "Nema ostvarenih narudžbi"; valNajboljiKupac.ForeColor = System.Drawing.Color.DarkRed; } } catch (Exception) { return; } }
private async Task <EToolService.Model.Models.Customer> GetTopCustomer(ReportSearchRequest request) { return(await _service.GetWithUrl <EToolService.Model.Models.Customer>(request, "TopCustomer")); }
public int GetServiceCount([FromQuery] ReportSearchRequest request) { return(_service.GetServiceCount(request)); }
public List <EToolService.Model.Models.Services> GetServices([FromQuery] ReportSearchRequest request) { return(_service.GetServices(request)); }
public EToolService.Model.Models.Product MostSellingProduct([FromQuery] ReportSearchRequest request) { return(_service.GetMostSellingProduct(request)); }
private async Task <int> GetNumberOfServices(ReportSearchRequest request) { return(await _service.GetWithUrl <int>(request, "ServiceNumber")); }
public decimal GetProductionEarnings([FromQuery] ReportSearchRequest request) { return(_service.GetProductionEarnings(request)); }
public Model.Models.Customer GetTopCustomer([FromQuery] ReportSearchRequest request) { return(_service.GetTopCustomer(request)); }
private async Task <decimal> GetServiceEarnings(ReportSearchRequest request) { return(await _service.GetWithUrl <decimal>(request, "ServiceEarnings")); }
public int GetSoldProducts([FromQuery] ReportSearchRequest request) { return(_service.GetSoldProducts(request)); }