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); }
public IEnumerable <SalesReport> Search(SalesReportParameters parameters) { List <SalesReport> salesReport = new List <SalesReport>(); using (var cn = new SqlConnection(Settings.GetConnectionString())) { string query = "SELECT FullName, SUM(PurchasePrice) As TotalSales, COUNT(PurchaseID) AS TotalVehicles " + "FROM AspNetUsers u INNER JOIN Purchases p ON u.Id = p.UserID WHERE 1=1 "; SqlCommand cmd = new SqlCommand(); if (!string.IsNullOrEmpty(parameters.User)) { query += "AND FullName = @FullName "; cmd.Parameters.AddWithValue("@FullName", parameters.User); } if (!string.IsNullOrEmpty(parameters.FromDate)) { query += "AND CreateDate >= @FromDate "; var date = DateTime.Parse(parameters.FromDate).ToShortDateString(); cmd.Parameters.AddWithValue("@FromDate", date); } if (!string.IsNullOrEmpty(parameters.ToDate)) { query += "AND CreateDate <= @ToDate "; var date = DateTime.Parse(parameters.ToDate).ToShortDateString(); cmd.Parameters.AddWithValue("@ToDate", date); } query += "GROUP BY FullName"; cmd.Connection = cn; cmd.CommandText = query; cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { SalesReport row = new SalesReport(); row.User = dr["FullName"].ToString(); row.TotalSales = (decimal)dr["TotalSales"]; row.TotalVehicles = (int)dr["TotalVehicles"]; salesReport.Add(row); } } } return(salesReport); }
public IHttpActionResult SalesReport(DateTime?toDate, DateTime?fromDate, string userName) { var repo = GuildCarsRepositoryFactory.GetRepository(); try { var parameters = new SalesReportParameters { UserName = userName, ToDate = toDate, FromDate = fromDate }; var result = repo.SalesReport(parameters); return(Ok(result)); } catch (Exception ex) { return(BadRequest(ex.Message)); } }
public IHttpActionResult SalesReport(string userId, DateTime?fromDate, DateTime?toDate) { var repo = AdminFactory.GetRepository(); try { var parameters = new SalesReportParameters() { UserId = userId, FromDate = fromDate, ToDate = toDate }; var result = repo.GetSalesReport(parameters); return(Ok(result)); } catch (Exception ex) { return(BadRequest(ex.Message)); } }
public IHttpActionResult Search(string user, string fromDate, string toDate) { var repo = ReportsRepositoryFactory.GetRepository(); try { var paramaters = new SalesReportParameters() { User = user, FromDate = fromDate, ToDate = toDate }; var results = repo.Search(paramaters); return(Ok(results)); } catch (Exception ex) { return(BadRequest(ex.Message)); } }
public IEnumerable <SalesReportItem> GetSalesReport(SalesReportParameters parameters) { var purchaseRepo = new PurchaseRepoQA(); var purchases = purchaseRepo.GetAll(); var users = GetUsers(); if (parameters.FromDate.HasValue) { purchases = purchases.Where(p => p.CreatedDate >= parameters.FromDate).ToList(); } if (parameters.ToDate.HasValue) { purchases = purchases.Where(p => p.CreatedDate <= parameters.ToDate).ToList(); } if (!string.IsNullOrEmpty(parameters.UserId)) { purchases = purchases.Where(p => p.UserId == parameters.UserId).ToList(); } var query = from p in purchases join user in users on p.UserId equals user.UserId group p by new { p.UserId, user.FirstName, user.LastName } into grp select new SalesReportItem { FirstName = grp.Key.FirstName, LastName = grp.Key.LastName, TotalVehicles = grp.Count(), TotalSales = grp.Sum(p => p.PurchasePrice) }; return(query); }
public IEnumerable <SalesReport> SalesReport(SalesReportParameters parameters) { throw new NotImplementedException(); }