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