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