public async Task <IActionResult> GetSalesCategoryBranchReportDateRange([FromBody] SalesCategoryBranchReportFilterDto filter)
{
// Return to response
var listToReturn = await GetSalesCategoryBranchReport(filter);
return(Ok(listToReturn));
}
public async Task <IActionResult> GetSalesCategoryBranchReportLastMonth([FromBody] SalesCategoryBranchReportFilterDto filter)
{
// Set the filter to "LastMonth"
filter.SetLastMonth();
// Return to response
var listToReturn = await GetSalesCategoryBranchReport(filter);
return(Ok(listToReturn));
}
private async Task <List <SalesCategoryBranchReportDto> > GetSalesCategoryBranchReport(SalesCategoryBranchReportFilterDto filter)
{
// Set NoTracking for ChangeTracker
_context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
var resultList = new List <SalesCategoryBranchReportDto>();
// Make commandText ready
string commandText = null;
string categories = "";
if (filter.Categories.Count > 0)
{
categories = " and cat in (";
for (int i = 0; i < filter.Categories.Count; i++)
{
categories += @"'" + filter.Categories[i] + @"'";
if (i < filter.Categories.Count - 1)
{
categories += ",";
}
else
{
categories += ") ";
}
}
}
commandText = @"select b.id as BranchId
, round(sum(s.commit_price * s.quantity * 1.15), 2) as Sales
, round(sum((s.commit_price - s.supplier_price) * s.quantity * 1.15), 2) as Profit
, (select round(sum(amount * 1.15), 2) from budget
where branch_id = b.id"
+ categories +
@" and date >= @startDateTime
and date < @endDateTime) as Budget
from sales s
join invoice i on s.invoice_number = i.invoice_number
join branch b on i.branch = b.id
where b.fax <> 'hidden4mreport'"
+ categories +
@" and i.commit_date >= @startDateTime
and i.commit_date < @endDateTime
group by b.id
order by b.id";
// Run SQL Command
using (var connection = (SqlConnection)_context.Database.GetDbConnection())
{
var command = new SqlCommand(commandText, connection);
command.Parameters.AddWithValue("@startDateTime", filter.StartDateTime);
command.Parameters.AddWithValue("@endDateTime", filter.EndDateTime);
_context.Database.OpenConnection();
using (var result = await command.ExecuteReaderAsync())
{
while (result.Read())
{
SalesCategoryBranchReportDto dto = new SalesCategoryBranchReportDto
{
BranchId = Convert.ToInt32(result["BranchId"]),
AmountWithGST = Convert.ToDecimal(result["Sales"] is DBNull ? 0 : result["Sales"]),
ProfitWithGST = Convert.ToDecimal(result["Profit"] is DBNull ? 0 : result["Profit"]),
BudgetWithGST = Convert.ToDecimal(result["Budget"] is DBNull ? 0 : result["Budget"])
};
dto.BudgetWithGST = Math.Round(dto.BudgetWithGST, 2);
resultList.Add(dto);
}
}
_context.Database.CloseConnection();
return(resultList);
}
}
