public IEnumerable <SalesReportModel> GetSales(SalesReportParametersModel parameters) { List <SalesReportModel> _sales = new List <SalesReportModel>(); using (var conn = new SqlConnection()) { conn.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString(); string query = "SELECT Salesperson, SUM(PurchasePrice) AS SalesTotal, Count(SaleID) AS VehicleTotal" + " FROM Sale" + " WHERE 1 = 1"; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; if (!string.IsNullOrEmpty(parameters.Salesperson)) { query += " AND Salesperson >= @User "; cmd.Parameters.AddWithValue("@User", parameters.Salesperson); } if (parameters.FromDate.HasValue) { query += " AND Date >= @FromDate "; cmd.Parameters.AddWithValue("@FromDate", parameters.FromDate.Value); } if (parameters.ToDate.HasValue) { query += " AND Date <= @ToDate "; cmd.Parameters.AddWithValue("@ToDate", parameters.ToDate.Value); } query += " GROUP BY Salesperson ORDER BY Salesperson"; cmd.CommandText = query; conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { SalesReportModel row = new SalesReportModel(); row.Salesperson = reader["Salesperson"].ToString(); row.SalesTotal = (decimal)reader["SalesTotal"]; row.VehicleTotal = (int)reader["VehicleTotal"]; _sales.Add(row); } } } return(_sales.AsEnumerable()); }
public IHttpActionResult Sales(string salesperson = null, DateTime?fromDate = null, DateTime?toDate = null) { ISaleRepository repo = SaleRepositoryFactory.GetRepository(); SalesReportParametersModel parameters = new SalesReportParametersModel() { Salesperson = salesperson, FromDate = fromDate, ToDate = toDate }; var sales = repo.GetSales(parameters); return(Ok(sales)); }
public IEnumerable <SalesReportModel> GetSales(SalesReportParametersModel parameters) { List <Sale> userFiltered; List <Sale> fromDateFiltered; List <Sale> toDateFiltered; if (!string.IsNullOrEmpty(parameters.Salesperson)) { userFiltered = _sales.FindAll(x => x.Salesperson == parameters.Salesperson); } else { userFiltered = _sales; } if (parameters.FromDate.HasValue) { fromDateFiltered = userFiltered.FindAll(x => x.Date >= parameters.FromDate); } else { fromDateFiltered = userFiltered; } if (parameters.ToDate.HasValue) { toDateFiltered = fromDateFiltered.FindAll(x => x.Date <= parameters.ToDate); } else { toDateFiltered = fromDateFiltered; } var sales = from s in toDateFiltered group s by(s.Salesperson) into g select new SalesReportModel { Salesperson = g.First().Salesperson, SalesTotal = g.Sum(x => x.PurchasePrice), VehicleTotal = g.Count() }; return(sales); }