public IActionResult GetSalesInvoiceReportDateRange([FromQuery] DateTime startDateTime, [FromQuery] DateTime endDateTime, [FromQuery] int?branchId, [FromQuery] string invoiceNumber, [FromQuery] string salesPerson) { // Validate user inpute if (startDateTime == null || endDateTime == null) { return(BadRequest()); } // Set the filter to "DateRange" var filter = new SalesInvoiceReportFilterDto { BranchId = branchId, InvoiceNumber = invoiceNumber, SalesPerson = salesPerson }; if (startDateTime != DateTime.MinValue) { filter.StartDateTime = startDateTime; } if (endDateTime != DateTime.MinValue) { filter.EndDateTime = endDateTime; } // Return to response var listToReturn = GetSalesInvoiceReport(filter); return(Ok(listToReturn)); }
public IActionResult GetSalesInvoiceReportThisMonth([FromQuery] int?branchId, [FromQuery] string invoiceNumber, [FromQuery] string salesPerson) { // Set the filter to "ThisMonth" var filter = new SalesInvoiceReportFilterDto(); filter.SetThisMonth(); filter.BranchId = branchId; filter.InvoiceNumber = invoiceNumber; filter.SalesPerson = salesPerson; // Return to response var listToReturn = GetSalesInvoiceReport(filter); return(Ok(listToReturn)); }
private List <SalesInvoiceReportDto> GetSalesInvoiceReport(SalesInvoiceReportFilterDto filter) { // Set NoTracking for ChangeTracker _context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking; // Join invoice, and sales tables var invoiceGroups = _context.Invoice .Where(i => i.CommitDate >= filter.StartDateTime) .Where(i => i.CommitDate < filter.EndDateTime) // LINQ to SQL Leaky abstractions pitfull //.Where(i => filter.BranchId.HasValue ? (i.Daily == filter.BranchId.Value) : true) .Where(i => filter.BranchId.HasValue ? (i.Branch == filter.BranchId.GetValueOrDefault()) : true) .Where(i => filter.SalesPerson == null ? true : i.Sales == filter.SalesPerson) .Where(i => filter.InvoiceNumber == null ? true : i.InvoiceNumber.ToString().Contains(filter.InvoiceNumber)) .Select(i => new { i.InvoiceNumber, i.Total, i.CommitDate, i.Branch, i.Sales, i.CardId }) .Join( _context.Branch .Where(b => b.Country.ToLower() != "hidden") .Select(b => new { b.Id, b.Name }), i => i.Branch, b => b.Id, (i, b) => new { i.InvoiceNumber, i.Total, i.CommitDate, BranchName = b.Name, SalesPerson = i.Sales, CustomerId = i.CardId } ) .Join( _context.Sales .Select(s => new { s.InvoiceNumber, s.Code, s.Quantity, s.CommitPrice, s.SupplierPrice }), i => i.InvoiceNumber, s => s.InvoiceNumber, (i, s) => new { i.InvoiceNumber, i.Total, i.CommitDate, i.BranchName, i.CustomerId, i.SalesPerson, s.Code, s.Quantity, s.CommitPrice, s.SupplierPrice } ) .GroupBy(i => i.InvoiceNumber); // Apply sales Daily report logic var resultList = new List <SalesInvoiceReportDto>(); foreach (var group in invoiceGroups) { string CustomerName; if (group.FirstOrDefault().CustomerId == 0) { CustomerName = "Cash Sales"; } else { CustomerName = _context.Card.FirstOrDefault(c => c.Id == group.FirstOrDefault().CustomerId).Name; } resultList.Add(new SalesInvoiceReportDto() { InvoiceNumber = group.Key, BranchName = group.FirstOrDefault().BranchName, TillNumber = _context.Orders.FirstOrDefault(o => o.InvoiceNumber == group.Key).StationId, LocalInvoiceNumber = _context.Orders.FirstOrDefault(o => o.InvoiceNumber == group.Key).PoNumber, InvoiceDateTime = group.FirstOrDefault().CommitDate, Customer = CustomerName, SalesPerson = group.FirstOrDefault().SalesPerson, AmountWithGST = Math.Round(group.FirstOrDefault().Total.GetValueOrDefault(), 2), ProfitWithGST = Math.Round(group .Sum(i => (decimal)((i.CommitPrice - i.SupplierPrice) * (decimal)i.Quantity * 1.15m)) , 2) }); } return(resultList); }