public void CanReturnZeroResultsOutOfDateRange()
{
ReportsRepositoryADO repo = new ReportsRepositoryADO();
SalesSearchParameters testParametersOne = new SalesSearchParameters
{
MaxDate = new DateTime(2015, 4, 2),
MinDate = new DateTime(2014, 1, 2),
UserName = null
};
SalesSearchParameters testParametersTwo = new SalesSearchParameters
{
MaxDate = new DateTime(2018, 4, 2),
MinDate = new DateTime(2017, 4, 2),
UserName = null
};
List <SalesReportListingItem> searchedSalesReportOne = repo.SearchSalesReports(testParametersOne).ToList();
List <SalesReportListingItem> searchedSalesReportTwo = repo.SearchSalesReports(testParametersOne).ToList();
Assert.AreEqual(0, searchedSalesReportOne.Count);
Assert.AreEqual(0, searchedSalesReportTwo.Count);
}
public static SalesSearchParameters CreateSalesParameter(string userName, string minYear,
string maxYear)
{
SalesSearchParameters parameters = new SalesSearchParameters();
if (userName == "null")
{
parameters.UserName = "";
}
else
{
parameters.UserName = userName;
}
if (minYear == "null")
{
parameters.MinDate = null;
}
else
{
parameters.MinDate = DateTime.Parse(minYear + "/1/1");
}
if (maxYear == "null")
{
parameters.MaxDate = null;
}
else
{
parameters.MaxDate = DateTime.Parse(maxYear + "/1/1");
}
return(parameters);
}
public List <PurchaseLog> QueryPurchaseLogs(SalesSearchParameters Parameters, List <PurchaseLog> purchaseLogs, List <User> users)
{
List <PurchaseLog> queriedPurchaseLogs = new List <PurchaseLog>();
if (Parameters.MaxDate.HasValue && Parameters.MinDate.HasValue && !string.IsNullOrEmpty(Parameters.UserName))
{
User userNameQuery = users.FirstOrDefault(user => user.UserName == Parameters.UserName);
queriedPurchaseLogs = purchaseLogs.Where(p => p.DateSold >= Parameters.MinDate && p.DateSold
<= Parameters.MaxDate && p.SalesPersonId == userNameQuery.Id).ToList();
}
else if (Parameters.MinDate.HasValue && Parameters.MaxDate.HasValue && string.IsNullOrEmpty(Parameters.UserName))
{
queriedPurchaseLogs = purchaseLogs.Where(p => p.DateSold >= Parameters.MinDate && p.DateSold <= Parameters.MaxDate).ToList();
}
else if (Parameters.MinDate.HasValue)
{
queriedPurchaseLogs = purchaseLogs.Where(p => p.DateSold >= Parameters.MinDate).ToList();
}
else if (Parameters.MaxDate.HasValue)
{
queriedPurchaseLogs = purchaseLogs.Where(p => p.DateSold <= Parameters.MaxDate).ToList();
}
else if (!String.IsNullOrEmpty(Parameters.UserName))
{
User userNameQuery = users.FirstOrDefault(user => user.UserName == Parameters.UserName);
queriedPurchaseLogs = purchaseLogs.Where(p => p.SalesPersonId == userNameQuery.Id).ToList();
}
return(queriedPurchaseLogs);
}
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 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 void CanReturnZeroResultsWrongUserName()
{
SalesSearchParameters parameters = new SalesSearchParameters
{
MaxDate = new DateTime(2017, 4, 2),
MinDate = new DateTime(2017, 1, 2),
UserName = "******"
};
List <SalesReportListingItem> searchedSalesReport = repo.SearchSalesReports(parameters).ToList();
Assert.AreEqual(0, searchedSalesReport.Count);
}
public void CanSearchSalesFromDate()
{
var repo = SalesFactory.GetSalesRepository();
var parameters = new SalesSearchParameters {
FromDate = DateTime.Parse("01/02/2015")
};
var found = repo.GetSalesSearchResults(parameters).ToList();
Assert.AreEqual(1, found.Count());
Assert.AreEqual("Rad", found[0].FirstName);
Assert.AreEqual(20000, found[0].TotalSales);
Assert.AreEqual(3, found[0].TotalVehicles);
}
public void CanSearchSalesBetweenDates()
{
var repo = SalesFactory.GetSalesRepository();
var parameters = new SalesSearchParameters {
ToDate = DateTime.Parse("02/04/2016"), FromDate = DateTime.Parse("01/02/2009")
};
var found = repo.GetSalesSearchResults(parameters).ToList();
Assert.AreEqual(1, found.Count());
Assert.AreEqual("Joes", found[0].LastName);
Assert.AreEqual(14500, found[0].TotalSales);
Assert.AreEqual(2, found[0].TotalVehicles);
}
public void CanReturnZeroResultsWrongUserName()
{
ReportsRepositoryADO repo = new ReportsRepositoryADO();
SalesSearchParameters parameters = new SalesSearchParameters
{
MaxDate = new DateTime(2017, 4, 2),
MinDate = new DateTime(2017, 1, 2),
UserName = "******"
};
List <SalesReportListingItem> searchedSalesReport = repo.SearchSalesReports(parameters).ToList();
Assert.AreEqual(0, searchedSalesReport.Count);
}
public IHttpActionResult GetSalesReport(string userName, string fromDate, string toDate)
{
var repo = SalesFactory.GetSalesRepository();
var parameters = new SalesSearchParameters();
parameters.UserName = userName;
var date = new DateTime();
if (DateTime.TryParse(fromDate, out date))
{
parameters.FromDate = date;
}
else
{
parameters.FromDate = null;
}
date = new DateTime();
if (DateTime.TryParse(toDate, out date))
{
parameters.ToDate = date;
}
else
{
parameters.ToDate = null;
}
try
{
var sales = repo.GetSalesSearchResults(parameters);
if (sales.Count() != 0)
{
return(Ok(sales));
}
else
{
return(NotFound());
}
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public void CanSearchOnUserName()
{
SalesSearchParameters parameters = new SalesSearchParameters
{
MaxDate = null,
MinDate = null,
UserName = "******"
};
List <SalesReportListingItem> searchedSalesReport = repo.SearchSalesReports(parameters).ToList();
Assert.AreEqual(1, searchedSalesReport.Count);
Assert.AreEqual(27000, searchedSalesReport[0].Sales);
Assert.AreEqual("00000000-0000-0000-0000-000000000000", searchedSalesReport[0].UserId);
Assert.AreEqual("Sales Test User 1", searchedSalesReport[0].UserName);
}
public void CanSearchOnDateAndUserNameChange()
{
SalesSearchParameters parameters = new SalesSearchParameters
{
MaxDate = new DateTime(2017, 4, 2),
MinDate = new DateTime(2017, 1, 2),
UserName = "******"
};
List <SalesReportListingItem> searchedSalesReport = repo.SearchSalesReports(parameters).ToList();
Assert.AreEqual(1, searchedSalesReport.Count);
Assert.AreEqual(10000, searchedSalesReport[0].Sales);
Assert.AreEqual("00000000-0000-0000-0000-000000000000", searchedSalesReport[0].UserId);
Assert.AreEqual("Sales Test User 1", searchedSalesReport[0].UserName);
}
public void CanSearchOnMaxDateChange()
{
ReportsRepositoryADO repo = new ReportsRepositoryADO();
SalesSearchParameters parameters = new SalesSearchParameters
{
MaxDate = new DateTime(2020, 1, 1),
MinDate = null,
UserName = null
};
List <SalesReportListingItem> searchedSalesReport = repo.SearchSalesReports(parameters).ToList();
Assert.AreEqual(2, searchedSalesReport.Count);
Assert.AreEqual(18000m, searchedSalesReport[1].Sales);
Assert.AreEqual("11111111-1111-1111-1111-111111111111", searchedSalesReport[1].UserId);
Assert.AreEqual("*****@*****.**", searchedSalesReport[1].UserName);
}
public void CanSearchOnMinDateChange()
{
ReportsRepositoryADO repo = new ReportsRepositoryADO();
SalesSearchParameters parameters = new SalesSearchParameters
{
MaxDate = null,
MinDate = new DateTime(2017, 1, 1),
UserName = null
};
List <SalesReportListingItem> searchedSalesReport = repo.SearchSalesReports(parameters).ToList();
Assert.AreEqual(1, searchedSalesReport.Count);
Assert.AreEqual(27000, searchedSalesReport[0].Sales);
Assert.AreEqual("00000000-0000-0000-0000-000000000000", searchedSalesReport[0].UserId);
Assert.AreEqual("*****@*****.**", searchedSalesReport[0].UserName);
}
public IEnumerable <SalesReportListingItem> SearchSalesReports(SalesSearchParameters Parameters)
{
List <PurchaseLog> purchaseLogs = new List <PurchaseLog>();
PurchaseLogRepositoryMock purchaseRepo = new PurchaseLogRepositoryMock();
purchaseLogs = purchaseRepo.GetPurchaseLogs().ToList();
UserRepositoryMock userRepo = new UserRepositoryMock();
List <User> users = userRepo.GetUsers().ToList();
List <PurchaseLog> queriedPurchaseLogs = QueryPurchaseLogs(Parameters, purchaseLogs, users);
foreach (var purchase in queriedPurchaseLogs)
{
SalesReportListingItem salesReportItem = new SalesReportListingItem
{
Sales = purchase.PurchasePrice,
CarsSold = 0,
UserId = purchase.SalesPersonId,
UserName = userRepo.GetUserById(purchase.SalesPersonId).UserName
};
if (_salesReports.Exists(s => s.UserId == purchase.SalesPersonId))
{
int index = _salesReports.FindIndex(s => s.UserId == purchase.SalesPersonId);
_salesReports[index].CarsSold += 1;
_salesReports[index].Sales += salesReportItem.Sales;
}
else
{
salesReportItem.CarsSold += 1;
_salesReports.Add(salesReportItem);
}
}
return(_salesReports);
}
public IHttpActionResult Search(string userID, DateTime?fromDate, DateTime?toDate)
{
var repo = PurchaseRepositoryFactory.GetRepository();
try
{
var parameters = new SalesSearchParameters()
{
UserID = userID,
FromDate = fromDate,
ToDate = toDate
};
var result = repo.SearchSales(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IEnumerable <SalesReportListingItem> SearchSalesReports(SalesSearchParameters Parameters)
{
List <SalesReportListingItem> salesReports = new List <SalesReportListingItem>();
using (var dbConnection = new SqlConnection(Settings.GetConnectionString()))
{
string query = "SELECT a.UserName As \"UserName\", a.Id AS \"UserId\", SUM(p.PurchasePrice) AS \"TotalSales\", COUNT(p.SalesPersonId) AS \"TotalCarsSold\" " +
"FROM PurchaseLog p INNER JOIN AspNetUsers a ON a.UserName = p.SalesPersonId WHERE 1 = 1 ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = dbConnection;
if (Parameters.MinDate.HasValue)
{
query += "AND DateSold >= @MinDate ";
cmd.Parameters.AddWithValue("@MinDate", Parameters.MinDate.Value);
}
if (Parameters.MaxDate.HasValue)
{
query += "AND DateSold <= @MaxDate ";
cmd.Parameters.AddWithValue("@MaxDate", Parameters.MaxDate.Value);
}
if (!string.IsNullOrEmpty(Parameters.UserName))
{
query += "AND UserName = @UserName ";
cmd.Parameters.AddWithValue("@UserName", Parameters.UserName);
}
query += "GROUP BY UserName, Id ORDER BY TotalSales DESC";
cmd.CommandText = query;
dbConnection.Open();
try {
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
SalesReportListingItem salesReportItem = new SalesReportListingItem();
salesReportItem.UserName = dr["UserName"].ToString();
salesReportItem.UserId = dr["UserId"].ToString();
salesReportItem.Sales = (decimal)dr["TotalSales"];
salesReportItem.CarsSold = (int)dr["TotalCarsSold"];
salesReports.Add(salesReportItem);
}
}
}
catch (Exception ex)
{
string errorMessage = String.Format(CultureInfo.CurrentCulture,
"Exception Type: {0}, Message: {1}{2}",
ex.GetType(),
ex.Message,
ex.InnerException == null ? String.Empty :
String.Format(CultureInfo.CurrentCulture,
" InnerException Type: {0}, Message: {1}",
ex.InnerException.GetType(),
ex.InnerException.Message));
System.Diagnostics.Debug.WriteLine(errorMessage);
dbConnection.Close();
}
}
return(salesReports);
}
public IEnumerable <SalesReportRequest> GetSalesSearchResults(SalesSearchParameters parameters)
{
List <Sales> results = _sales;
var sales = _sales;
var users = _users;
if (parameters.FromDate != null && parameters.ToDate != null)
{
DateTime fromDate = Convert.ToDateTime(parameters.FromDate);
DateTime toDate = Convert.ToDateTime(parameters.ToDate);
sales = (from sale in sales
where sale.DatePurchased <= toDate && sale.DatePurchased >= fromDate
select sale).ToList();
}
if (parameters.FromDate == null && parameters.ToDate != null)
{
DateTime fromDate = Convert.ToDateTime(parameters.FromDate);
DateTime toDate = Convert.ToDateTime(parameters.ToDate);
sales = (from sale in sales
where sale.DatePurchased <= toDate
select sale).ToList();
}
if (parameters.FromDate != null && parameters.ToDate == null)
{
DateTime fromDate = Convert.ToDateTime(parameters.FromDate);
DateTime toDate = Convert.ToDateTime(parameters.ToDate);
sales = (from sale in sales
where sale.DatePurchased >= fromDate
select sale).ToList();
}
if (parameters.UserName != null)
{
sales = (from user in _users
join sale in sales on user.UserId equals sale.UserId
where user.FirstName.Contains(parameters.UserName) || user.LastName.Contains(parameters.UserName)
select sale).ToList();
}
var salesReportRequest = new List <SalesReportRequest>();
salesReportRequest = (from user in users
join sale in sales on user.UserId equals sale.UserId
select new SalesReportRequest()
{
DatePurchased = sale.DatePurchased,
FirstName = user.FirstName,
LastName = user.LastName,
UserId = user.UserId,
UserName = user.FirstName + " " + user.LastName
}).ToList();
var report = salesReportRequest.GroupBy(s => s.UserId).Select(s => s);
var selectedReport = (from r in report
select new SalesReportRequest()
{
FirstName = users.Find(u => u.UserId == r.Key).FirstName,
LastName = users.Find(u => u.UserId == r.Key).LastName,
TotalSales = sales.Where(s => s.UserId == r.Key).Sum(s => s.PurchasePrice),
TotalVehicles = sales.Where(s => s.UserId == r.Key).Count(),
}).ToList();
foreach (var item in selectedReport)
{
item.UserName = item.FirstName + " " + item.LastName;
}
return(selectedReport);
}
