private List <SalesPaymentReportDto> GetSalesPaymentReport(SalesPaymentReportFilterDto filter)
{
// Turn Object Tracking off
_context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
// Join trans, tran_detail, and enum tables
var tranGroups = _context.Trans
.Where(t => filter.BranchId.HasValue ? t.Branch == filter.BranchId.GetValueOrDefault() : true)
.Select(t => new { t.Id, t.Amount, t.Branch })
.Join(
_context.Branch
.Where(b => b.Fax.ToLower() != "hidden4mreport")
//.Where(b => b.Activated == true)
.Select(b => new { b.Id }),
t => t.Branch,
b => b.Id,
(t, b) => new { t.Id, t.Amount }
)
.Join(
_context.TranDetail
.Where(td => td.TransDate >= filter.StartDateTime)
.Where(td => td.TransDate < filter.EndDateTime)
.Select(td => new { td.Id, td.PaymentMethod }),
t => t.Id,
td => td.Id,
(t, td) => new { t.Amount, td.PaymentMethod })
.Join(
_context.Enum
.Where(e => e.Class == "payment_method")
.Select(e => new { e.Id, e.Name })
.ToList(), // because the id in enum table is not unique
t => t.PaymentMethod,
e => e.Id,
(t, e) => new { t.Amount, PaymentMethod = e.Name })
.GroupBy(t => t.PaymentMethod);
/* Apply report logic */
var resultList = new List <SalesPaymentReportDto>();
foreach (var group in tranGroups)
{
resultList.Add(new SalesPaymentReportDto
{
PaymentMethod = group.Key,
Total = Math.Round(group
.Sum(g => g.Amount)
, 2)
});
}
return(resultList);
}
public IActionResult GetSalesPaymentReportLastMonth([FromQuery] int?branchId)
{
// Set the filter to "LastMonth"
var filter = new SalesPaymentReportFilterDto();
filter.SetLastMonth();
filter.BranchId = branchId;
// Return to response
var listToReturn = GetSalesPaymentReport(filter);
return(Ok(listToReturn));
}
public IActionResult GetSalesPaymentReportDateRange([FromQuery] DateTime startDateTime,
[FromQuery] DateTime endDateTime, [FromQuery] int?branchId)
{
// Validate user inpute
if (startDateTime == null || endDateTime == null)
{
return(BadRequest());
}
// Set the filter to "DateRange"
var filter = new SalesPaymentReportFilterDto
{
StartDateTime = startDateTime,
EndDateTime = endDateTime,
BranchId = branchId
};
// Return to response
var listToReturn = GetSalesPaymentReport(filter);
return(Ok(listToReturn));
}
