public List <SalesReportItem> SearchSales(SalesSearchParameters parameters)
{
List <SalesReportItem> sales = new List <SalesReportItem>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = "SELECT FirstName, LastName, Sum(PurchasePrice) AS TotalSales, Count(*) AS TotalVehicles FROM Purchase ";
query += "INNER JOIN AspNetUsers ON Purchase.UserID = AspNetUsers.Id ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (!string.IsNullOrEmpty(parameters.UserID))
{
query += "WHERE Purchase.UserID = @UserID ";
cmd.Parameters.AddWithValue("@UserID", parameters.UserID);
}
else
{
query += "WHERE 1 = 1 ";
}
if (parameters.FromDate.HasValue)
{
query += "AND PurchaseDate >= @FromDate ";
cmd.Parameters.AddWithValue("@FromDate", parameters.FromDate);
}
if (parameters.ToDate.HasValue)
{
query += "AND PurchaseDate <= @ToDate ";
cmd.Parameters.AddWithValue("@ToDate", parameters.ToDate);
}
query += "GROUP BY LastName, FirstName ";
query += "ORDER BY LastName ASC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
SalesReportItem row = new SalesReportItem();
row.FirstName = dr["FirstName"].ToString();
row.LastName = dr["LastName"].ToString();
row.TotalSales = (decimal)dr["TotalSales"];
row.TotalVehicles = (int)dr["TotalVehicles"];
sales.Add(row);
}
}
}
return(sales);
}
public IEnumerable <SalesReportItem> GetSalesReport(SalesReportParameters parameters)
{
List <SalesReportItem> items = new List <SalesReportItem>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = "SELECT FirstName, LastName, UserId, SUM(PurchasePrice) as 'TotalSales', COUNT(PurchasePrice) as 'TotalVehicles' " +
"FROM Purchases p " +
"INNER JOIN AspNetUsers a ON p.UserId = a.Id " +
"WHERE 1 = 1 ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (parameters.FromDate.HasValue)
{
query += $"AND p.CreatedDate >= @FromDate ";
cmd.Parameters.AddWithValue("@FromDate", parameters.FromDate.Value);
}
if (parameters.ToDate.HasValue)
{
query += $"AND p.CreatedDate <= @ToDate ";
cmd.Parameters.AddWithValue("@Todate ", parameters.ToDate.Value);
}
if (!string.IsNullOrEmpty(parameters.UserId))
{
query += $"AND a.Id = @UserId ";
cmd.Parameters.AddWithValue("@UserId", parameters.UserId);
}
query += "GROUP BY FirstName, LastName, p.UserId ";
query += "ORDER BY TotalSales DESC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
SalesReportItem row = new SalesReportItem();
row.FirstName = dr["FirstName"].ToString();
row.LastName = dr["LastName"].ToString();
row.TotalVehicles = (int)dr["TotalVehicles"];
row.UserId = dr["UserId"].ToString();
row.TotalSales = (decimal)dr["TotalSales"];
items.Add(row);
}
}
}
return(items);
}