private List <SalesMonthlyReportDto> GetSalesMonthlyReportEF(SalesMonthlyReportFilterDto 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.Monthly == filter.BranchId.Value) : true)
.Where(i => filter.BranchId.HasValue ? (i.Branch == filter.BranchId.GetValueOrDefault()) : true)
.Select(i => new { i.InvoiceNumber, i.Total, i.CommitDate, i.Branch })
.Join(
_context.Branch
.Where(b => b.Fax.ToLower() != "hidden4mreport")
//.Where(b => b.Activated == true)
.Select(b => new { b.Id }),
i => i.Branch,
b => b.Id,
(i, b) => new { i.InvoiceNumber, i.Total, i.CommitDate }
)
.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, s.Code, s.Quantity, s.CommitPrice, s.SupplierPrice }
)
.GroupBy(i => i.CommitDate.Year.ToString() + i.CommitDate.Month.ToString());
// Apply sales Monthly report logic
var resultList = new List <SalesMonthlyReportDto>();
foreach (var group in invoiceGroups)
{
var year = group.FirstOrDefault().CommitDate.Year;
var month = group.FirstOrDefault().CommitDate.Month;
resultList.Add(new SalesMonthlyReportDto()
{
StartDate = new DateTime(year, month, 1),
EndDate = new DateTime(year, month, 1).AddMonths(1),
TotalWithGST = Math.Round((decimal)group
.Select(i => new { i.InvoiceNumber, i.Total })
.Distinct()
.Sum(i => i.Total), 2),
ProfitWithGST = Math.Round(group
.Sum(i => (decimal)((i.CommitPrice - i.SupplierPrice) * (decimal)i.Quantity * 1.15m))
, 2),
InvoiceQuantity = group
.Select(i => new { i.InvoiceNumber, i.Total })
.Distinct()
.Count()
});
}
return(resultList.OrderBy(r => r.StartDate).ToList());
}
public IActionResult GetSalesMonthlyReportLast24Months([FromQuery] int?branchId)
{
// Set the filter to "Last24Months"
var filter = new SalesMonthlyReportFilterDto();
filter.SetLast24Months();
filter.BranchId = branchId;
// Return to response
var listToReturn = GetSalesMonthlyReport(filter);
return(Ok(listToReturn));
}
public IActionResult GetSalesMonthlyReportDateRange([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 SalesMonthlyReportFilterDto
{
StartDateTime = startDateTime,
EndDateTime = endDateTime,
BranchId = branchId
};
// Return to response
var listToReturn = GetSalesMonthlyReport(filter);
return(Ok(listToReturn));
}
private List <SalesMonthlyReportDto> GetSalesMonthlyReport(SalesMonthlyReportFilterDto filter)
{
// Set NoTracking for ChangeTracker
_context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
var resultList = new List <SalesMonthlyReportDto>();
// Execute proc_MonthlySalesReport_TotalAndQuantity
using (var command = _context.Database.GetDbConnection().CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "proc_MonthlySalesReport_TotalAndQuantity";
var param1 = command.CreateParameter();
param1.ParameterName = "@branch_id";
param1.DbType = DbType.Int32;
param1.Value = filter.BranchId;
command.Parameters.Add(param1);
var param2 = command.CreateParameter();
param2.ParameterName = "@start_datetime";
param2.DbType = DbType.DateTime;
param2.Value = filter.StartDateTime;
command.Parameters.Add(param2);
var param3 = command.CreateParameter();
param3.ParameterName = "@end_datetime";
param3.DbType = DbType.DateTime;
param3.Value = filter.EndDateTime;
command.Parameters.Add(param3);
_context.Database.OpenConnection();
using (var result = command.ExecuteReader())
{
while (result.Read())
{
SalesMonthlyReportDto monthlySales = new SalesMonthlyReportDto
{
StartDate = new DateTime(int.Parse(result["yearMonth"].ToString().Substring(0, 4)), int.Parse(result["yearMonth"].ToString().Substring(5, 2)), 1),
EndDate = new DateTime(int.Parse(result["yearMonth"].ToString().Substring(0, 4)), int.Parse(result["yearMonth"].ToString().Substring(5, 2)), 1).AddMonths(1),
TotalWithGST = Convert.ToDecimal(result["totalWithGST"]),
InvoiceQuantity = Convert.ToInt32(result["invoiceQuantity"])
};
resultList.Add(monthlySales);
}
}
_context.Database.CloseConnection();
}
// Execute proc_MonthlySalesReport_ProfitAndQuantity
using (var command = _context.Database.GetDbConnection().CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "proc_MonthlySalesReport_ProfitAndQuantity";
var param1 = command.CreateParameter();
param1.ParameterName = "@branch_id";
param1.DbType = DbType.Int32;
param1.Value = filter.BranchId;
command.Parameters.Add(param1);
var param2 = command.CreateParameter();
param2.ParameterName = "@start_datetime";
param2.DbType = DbType.DateTime;
param2.Value = filter.StartDateTime;
command.Parameters.Add(param2);
var param3 = command.CreateParameter();
param3.ParameterName = "@end_datetime";
param3.DbType = DbType.DateTime;
param3.Value = filter.EndDateTime;
command.Parameters.Add(param3);
_context.Database.OpenConnection();
using (var result = command.ExecuteReader())
{
while (result.Read())
{
var startDate = new DateTime(int.Parse(result["yearMonth"].ToString().Substring(0, 4)), int.Parse(result["yearMonth"].ToString().Substring(5, 2)), 1);
resultList.FirstOrDefault(r => r.StartDate == startDate).ProfitWithGST = Convert.ToDecimal(result["profitWithGST"]);
}
}
_context.Database.CloseConnection();
}
return(resultList.OrderBy(r => r.StartDate).ToList());
}
