public IEnumerable <SalesReportRequest> GetSales() { List <SalesReportRequest> sales = new List <SalesReportRequest>(); using (var cn = new SqlConnection(Settings.GetConnectionString())) { SqlCommand cmd = new SqlCommand("GetSales"); cmd.Connection = cn; cmd.CommandType = CommandType.StoredProcedure; cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { SalesReportRequest currentRow = new SalesReportRequest(); currentRow.FirstName = dr["FirstName"].ToString(); currentRow.LastName = dr["LastName"].ToString(); currentRow.TotalSales = (decimal)dr["TotalSales"]; currentRow.TotalVehicles = (int)dr["TotalVehicles"]; currentRow.UserName = currentRow.FirstName + " " + currentRow.LastName; sales.Add(currentRow); } return(sales); } } }
public IEnumerable <SalesReportRequest> GetSales() { List <SalesReportRequest> salesReportList = new List <SalesReportRequest>(); foreach (var user in _users) { SalesReportRequest salesReport = new SalesReportRequest(); salesReport.UserName = user.FirstName + " " + user.LastName; salesReport.FirstName = user.FirstName; salesReport.LastName = user.LastName; foreach (var sale in _sales) { if (sale.UserId == user.UserId) { salesReport.TotalSales = salesReport.TotalSales + sale.PurchasePrice; salesReport.TotalVehicles = salesReport.TotalVehicles + 1; } } if (salesReport.TotalSales != 0) { salesReportList.Add(salesReport); } } return(salesReportList); }
public IEnumerable <SalesReportRequest> GetSalesSearchResults(SalesSearchParameters parameters) { SalesSearchParameters parameter = new SalesSearchParameters(); List <SalesReportRequest> resultsList = new List <SalesReportRequest>(); parameter.UserName = parameters.UserName; parameter.ToDate = parameters.ToDate; parameter.FromDate = parameters.FromDate; using (var cn = new SqlConnection(Settings.GetConnectionString())) { SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; string query = "SELECT FirstName, LastName, SUM(PurchasePrice) AS TotalSales, COUNT(UserId) AS TotalVehicles FROM Sales INNER JOIN AspNetUsers u ON u.Id = Sales.UserId "; if (!string.IsNullOrEmpty(parameter.UserName)) { query += "AND CONCAT(FirstName,' ',LastName) Like @UserName "; cmd.Parameters.AddWithValue("@UserName", parameters.UserName + '%'); } if (parameter.ToDate.HasValue) { query += "AND DatePurchased <= @ToDate "; cmd.Parameters.AddWithValue("@ToDate", parameters.ToDate); } if (parameter.FromDate.HasValue) { query += "AND DatePurchased >= @FromDate "; cmd.Parameters.AddWithValue("@FromDate", parameters.FromDate); } query += "GROUP BY FirstName, LastName ORDER BY TotalSales, TotalVehicles"; cmd.CommandText = query; cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { SalesReportRequest currentRow = new SalesReportRequest(); currentRow.FirstName = dr["FirstName"].ToString(); currentRow.LastName = dr["LastName"].ToString(); currentRow.TotalSales = (decimal)dr["TotalSales"]; currentRow.TotalVehicles = (int)dr["TotalVehicles"]; currentRow.UserName = currentRow.FirstName + " " + currentRow.LastName; resultsList.Add(currentRow); } return(resultsList); } } }
public SalesReportsViewModel() { SalesReportRequest salesReport = new SalesReportRequest(); UsersItem user = new UsersItem(); }