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));
}
