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