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