public IEnumerable <SalesReportQueryRow> FilterSalesReport(string user, DateTime?fromDate, DateTime?toDate)
{
var sales = new List <SalesReportQueryRow>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = (
@"SELECT U.UserName
,SUM(S.PurchasePrice) AS TotalSales
,COUNT(V.VehicleId) AS TotalVehicles
FROM Sales AS S
INNER JOIN Vehicles AS V ON S.SaleId = V.SaleId
INNER JOIN AspNetUsers AS U ON U.Id = V.UserId
WHERE 1 = 1
");
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (!string.IsNullOrEmpty(user))
{
query += "AND U.Id = @UserId ";
cmd.Parameters.AddWithValue("@UserId", user);
}
if (fromDate.HasValue)
{
query += "AND S.Date >= @FromDate ";
cmd.Parameters.AddWithValue("@FromDate", fromDate.Value);
}
if (toDate.HasValue)
{
query += "AND S.Date <= @ToDate ";
cmd.Parameters.AddWithValue("@ToDate", toDate.Value);
}
query += "GROUP BY U.UserName ";
query += "ORDER BY TotalSales DESC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var row = new SalesReportQueryRow();
row.TotalVehicles = (int)dr["TotalVehicles"];
row.TotalSales = (decimal)dr["TotalSales"];
row.UserName = dr["UserName"].ToString();
sales.Add(row);
}
}
}
return(sales);
}
public IEnumerable <SalesReportQueryRow> GetSalesReport()
{
var sales = new List <SalesReportQueryRow>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
var cmd = new SqlCommand("SalesReport", cn);
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var row = new SalesReportQueryRow();
row.TotalVehicles = (int)dr["TotalVehicles"];
row.TotalSales = (decimal)dr["TotalSales"];
row.UserName = dr["UserName"].ToString();
sales.Add(row);
}
}
}
return(sales);
}
