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