public void SortCarsByFuelType()
{
var data = new List <Car>
{
new Car {
Model = "Car1", Engine = new Engine {
FuelType = "Diesel"
}
},
new Car {
Model = "Car2", Engine = new Engine {
FuelType = "Gasoline"
}
},
new Car {
Model = "Car3", Engine = new Engine {
FuelType = "Electricity"
}
},
}.AsQueryable();
var mockSet = new Mock <DbSet <Car> >();
mockSet.As <IQueryable <Car> >().Setup(m => m.Provider).Returns(data.Provider);
mockSet.As <IQueryable <Car> >().Setup(m => m.Expression).Returns(data.Expression);
mockSet.As <IQueryable <Car> >().Setup(m => m.ElementType).Returns(data.ElementType);
mockSet.As <IQueryable <Car> >().Setup(m => m.GetEnumerator()).Returns(data.GetEnumerator());
var mockContext = new Mock <CarDealershipContext>();
mockContext.Setup(m => m.Cars).Returns(mockSet.Object);
var service = new CarBusiness(mockContext.Object);
var SortedCars = service.SortCarsByFuelType();
Assert.AreEqual("Diesel", SortedCars[0].Engine.FuelType);
Assert.AreEqual("Electricity", SortedCars[1].Engine.FuelType);
Assert.AreEqual("Gasoline", SortedCars[2].Engine.FuelType);
}
public void SortCarsByFuelEconomyDescending()
{
var data = new List <Car>
{
new Car {
Engine = new Engine {
EconomyPerHundredKm = 5m
}
},
new Car {
Engine = new Engine {
EconomyPerHundredKm = 6m
}
},
new Car {
Engine = new Engine {
EconomyPerHundredKm = 7m
}
}
}.AsQueryable();
var mockSet = new Mock <DbSet <Car> >();
mockSet.As <IQueryable <Car> >().Setup(m => m.Provider).Returns(data.Provider);
mockSet.As <IQueryable <Car> >().Setup(m => m.Expression).Returns(data.Expression);
mockSet.As <IQueryable <Car> >().Setup(m => m.ElementType).Returns(data.ElementType);
mockSet.As <IQueryable <Car> >().Setup(m => m.GetEnumerator()).Returns(data.GetEnumerator());
var mockContext = new Mock <CarDealershipContext>();
mockContext.Setup(m => m.Cars).Returns(mockSet.Object);
var service = new CarBusiness(mockContext.Object);
var SortedCars = service.SortCarsByFuelEconomyDescending();
Assert.AreEqual(7m, SortedCars[0].Engine.EconomyPerHundredKm);
Assert.AreEqual(6m, SortedCars[1].Engine.EconomyPerHundredKm);
Assert.AreEqual(5m, SortedCars[2].Engine.EconomyPerHundredKm);
}
public void SortCarsByPowerDescending()
{
var data = new List <Car>
{
new Car {
Engine = new Engine {
Power = 100
}
},
new Car {
Engine = new Engine {
Power = 120
}
},
new Car {
Engine = new Engine {
Power = 140
}
}
}.AsQueryable();
var mockSet = new Mock <DbSet <Car> >();
mockSet.As <IQueryable <Car> >().Setup(m => m.Provider).Returns(data.Provider);
mockSet.As <IQueryable <Car> >().Setup(m => m.Expression).Returns(data.Expression);
mockSet.As <IQueryable <Car> >().Setup(m => m.ElementType).Returns(data.ElementType);
mockSet.As <IQueryable <Car> >().Setup(m => m.GetEnumerator()).Returns(data.GetEnumerator());
var mockContext = new Mock <CarDealershipContext>();
mockContext.Setup(m => m.Cars).Returns(mockSet.Object);
var service = new CarBusiness(mockContext.Object);
var SortedCars = service.SortCarsByPowerDescending();
Assert.AreEqual(140, SortedCars[0].Engine.Power);
Assert.AreEqual(120, SortedCars[1].Engine.Power);
Assert.AreEqual(100, SortedCars[2].Engine.Power);
}
public void GetCarsWithLowerDisplacement()
{
var data = new List <Car>
{
new Car {
Model = "Car1", Engine = new Engine {
Displacement = 1900
}
},
new Car {
Model = "Car2", Engine = new Engine {
Displacement = 1800
}
},
new Car {
Model = "Car3", Engine = new Engine {
Displacement = 2700
}
},
}.AsQueryable();
var mockSet = new Mock <DbSet <Car> >();
mockSet.As <IQueryable <Car> >().Setup(m => m.Provider).Returns(data.Provider);
mockSet.As <IQueryable <Car> >().Setup(m => m.Expression).Returns(data.Expression);
mockSet.As <IQueryable <Car> >().Setup(m => m.ElementType).Returns(data.ElementType);
mockSet.As <IQueryable <Car> >().Setup(m => m.GetEnumerator()).Returns(data.GetEnumerator());
var mockContext = new Mock <CarDealershipContext>();
mockContext.Setup(m => m.Cars).Returns(mockSet.Object);
var service = new CarBusiness(mockContext.Object);
var CarsFound = service.GetCarsWithLowerDisplacement(2000);
Assert.AreEqual(2, CarsFound.Count());
Assert.AreEqual("Car1", CarsFound[0].Model);
Assert.AreEqual("Car2", CarsFound[1].Model);
}
public void SortCarsByDealershipNameDescending()
{
var data = new List <Car>
{
new Car {
Model = "Car1", CarDealership = new CarDealership {
Name = "NameA"
}
},
new Car {
Model = "Car2", CarDealership = new CarDealership {
Name = "NameB"
}
},
new Car {
Model = "Car3", CarDealership = new CarDealership {
Name = "NameC"
}
},
}.AsQueryable();
var mockSet = new Mock <DbSet <Car> >();
mockSet.As <IQueryable <Car> >().Setup(m => m.Provider).Returns(data.Provider);
mockSet.As <IQueryable <Car> >().Setup(m => m.Expression).Returns(data.Expression);
mockSet.As <IQueryable <Car> >().Setup(m => m.ElementType).Returns(data.ElementType);
mockSet.As <IQueryable <Car> >().Setup(m => m.GetEnumerator()).Returns(data.GetEnumerator());
var mockContext = new Mock <CarDealershipContext>();
mockContext.Setup(m => m.Cars).Returns(mockSet.Object);
var service = new CarBusiness(mockContext.Object);
var SortedCars = service.SortCarsByDealershipNameDescending();
Assert.AreEqual("NameC", SortedCars[0].CarDealership.Name);
Assert.AreEqual("NameB", SortedCars[1].CarDealership.Name);
Assert.AreEqual("NameA", SortedCars[2].CarDealership.Name);
}
public MonthBusiness GetBusinessByMonthes(Month start, Month end, SearchSettings settings = null, Region region = null)
{
if (settings == null)
{
settings = App.SearchSettings;
}
var carBusinesses = new List <CarBusiness>();
var sql = string.Empty;
if (settings.SelectedDBSearchType == DBSearchType.All)
{
var sb = new StringBuilder(1000);
sb.Append("SELECT DISTINCT ").Append("(");
//настройки с поиском старых заказов
var oldSettings = new SearchSettings(settings);
oldSettings.SelectedDBSearchType = DBSearchType.Old;
sb.Append(GetBusinessByMonthesQuery(start, end, oldSettings, region));
//sb.Append(")");
sb.Append("\r\n UNION \r\n");//.Append("("); //объединяем два запроса
//настройки с поиском актуальных заказов
var curentSettings = new SearchSettings(settings);
curentSettings.SelectedDBSearchType = DBSearchType.Curent;
sb.Append(GetBusinessByMonthesQuery(start, end, curentSettings, region));
sb.Append(")");//.Append(" ORDER BY [Document No_]");
sql = sb.ToString();
}
else
{
sql = GetBusinessByMonthesQuery(start, end, settings, region);
}
try
{
using (var con = new SqlConnection(m_connectionString))
{
var com = new SqlCommand(sql);
com.Connection = con;
con.Open();
using (var reader = com.ExecuteReader())
{
var curentCar = string.Empty;
var previosCar = string.Empty;
var buyer = string.Empty;
CarBusiness cb = null;
while (reader.Read())
{
curentCar = (string)reader["CarName"];
var no_ = (string)reader["No_"];
cb = carBusinesses.FirstOrDefault(_cb => _cb.ItemNo.Equals(no_));
if (cb == null)
{
previosCar = curentCar;
cb = new CarBusiness();
cb.Monthes = Month.GetMonthes(new DateTime(start.Year, start.Index, 1), new DateTime(end.Year, end.Index, 1));
cb.ItemNo = (string)reader["No_"];
carBusinesses.Add(cb);
var number = ((string)reader["CarNumber"]).Trim();
var lastCharIndex = number.Last(c => char.IsLetter(c)) + 1;
if ((lastCharIndex) < number.Length)
{
number = number.Substring(lastCharIndex, number.Length - lastCharIndex);
}
cb.Name = $"{curentCar} ({number})";
}
buyer = (string)reader["Buyer"];
if (cb.Count > 0 && cb.Business.Last().Title.Equals(buyer))
{
cb.Business.Last().DateEnd = ((DateTime)reader["DateEnd"]);
}
else
{
var b = new Leasing();
b.DateStart = ((DateTime)reader["DateStart"]);
b.DateEnd = ((DateTime)reader["DateEnd"]);
b.Title = buyer;
b.Type = BusinessType.Leasing;
b.Comment = (string)reader["Comment"];
b.Monthes = Month.GetMonthes(b.DateStart, b.DateEnd);
b.Saler = (string)reader["Saler"];
b.Blocked = (bool)reader["Blocked"];
cb.Add(b);
}
}
}
}
}
catch (SqlException sqlEx)
{
m_loger.Log("Возникло исключение при запросе выборки из БД", sqlEx,
new LogParameter("Запрос", sql));
}
catch (Exception ex)
{
m_loger.Log("Возникло исключение при запросе выборки из БД", ex);
}
var monthBusiness = new MonthBusiness(carBusinesses);
monthBusiness.Monthes = Month.GetMonthes(new DateTime(start.Year, start.Index, 1), new DateTime(end.Year, end.Index, 1));
return(monthBusiness);
}
public MonthBusiness GetBusinessByMonth(Month month, SearchSettings settings = null, Region region = null)
{
if (settings == null)
{
settings = App.SearchSettings;
}
var carBusinesses = new List <CarBusiness>();
#region Old
/*
* var sql = $@"SELECT DISTINCT
* l.[Document No_]
* ,h.[Salesperson Code]
* ,h.[Bal_ Account No_]
* ,h.[Sell-to Customer Name] as Buyer
* ,h.[Bill-to Name]
* ,h.[Ship-to Name]
* ,l.[Description] as CarName
* ,l.[Vehicle Reg_ No_] as CarNumber
* ,h.[Venicle Operation Area]
* ,h.[Date Begin] as DateStart
* ,h.[Time Begin]
* ,h.[Date End] as DateEnd
* ,h.[Time End]
* ,h.[Comment Text] as Comment
* FROM [CARLSON_Test_10052018].[dbo].[Carlson$Sales{(invoice ? " Invoice" : "")} Line] l
* INNER JOIN [CARLSON_Test_10052018].[dbo].[Carlson$Sales{(invoice ? " Invoice" : "")} Header] h ON h.[Sell-to Customer No_] = l.[Sell-to Customer No_]
* WHERE l.[Vehicle Reg_ No_] != ''
* AND h.[Date End] > h.[Date Begin]
* ORDER BY l.[Document No_], h.[Date Begin]";
*/
#endregion
var sql = string.Empty;
if (settings.SelectedDBSearchType == DBSearchType.All)
{
var sb = new StringBuilder(1000);
sb.Append("SELECT DISTINCT (");
//настройки с поиском старых заказов
var oldSettings = new SearchSettings(settings);
oldSettings.SelectedDBSearchType = DBSearchType.Old;
sb.Append(GetBusinessByMonthQuery(month, oldSettings, region));
sb.Append("\r\n UNION \r\n"); //объединяем два запроса
//настройки с поиском актуальных заказов
var curentSettings = new SearchSettings(settings);
curentSettings.SelectedDBSearchType = DBSearchType.Curent;
sb.Append(GetBusinessByMonthQuery(month, curentSettings, region));
sb.Append(")");
sql = sb.ToString();
}
else
{
sql = GetBusinessByMonthQuery(month, settings, region);
}
//sql = $@"SELECT
// i.[No_]
// , i.[Description] as CarName
// , i.[Vehicle Reg_ No_] as CarNumber
// , i.[Blocked]
// , l.[Document No_]
// ,h.[Salesperson Code]
// ,h.[Bal_ Account No_]
// ,h.[Sell-to Customer Name] as Buyer
// ,h.[Bill-to Name]
// ,h.[Ship-to Name]
// ,h.[Venicle Operation Area]
// ,h.[Date Begin] as DateStart
// ,h.[Time Begin]
// ,h.[Date End] as DateEnd
// ,h.[Time End]
// ,h.[Comment Text] as Comment
// FROM Carlson$Item i
// LEFT JOIN [Carlson$Sales {(settings.SelectedDBSearchType == DBSearchType.Curent ? string.Empty : invoice)}Line] l ON l.No_ = i.No_
// LEFT JOIN [Carlson$Sales {(settings.SelectedDBSearchType == DBSearchType.Curent ? string.Empty : invoice)}Header] h ON h.No_ = l.[Document No_]
// WHERE 1 = 1
// {(settings.IncludeBlocked ? string.Empty : "AND i.Blocked = 0")}
// AND i.IsService = 0
// AND i.IsFranchise = 0
// AND h.[Date Begin] IS NOT NULL
// {(region == null || string.IsNullOrWhiteSpace(region.DBKey) ? "" : "AND i.[Responsibility Center] = '" + region.DBKey + "'")}
// AND ((h.[Date Begin] BETWEEN '{month.GetSqlDate(1)}' AND '{month.Next().GetSqlDate(1)}') OR (h.[Date End] BETWEEN '{month.GetSqlDate(1)}' AND '{month.Next().GetSqlDate(1)}'))
// ORDER BY l.[Document No_]";
try
{
using (var con = new SqlConnection(m_connectionString))
{
var com = new SqlCommand(sql);
com.Connection = con;
con.Open();
using (var reader = com.ExecuteReader())
{
var curentCar = string.Empty;
var previosCar = string.Empty;
var buyer = string.Empty;
CarBusiness cb = null;
while (reader.Read())
{
curentCar = (string)reader["CarName"];
var no_ = (string)reader["No_"];
cb = carBusinesses.FirstOrDefault(_cb => _cb.ItemNo.Equals(no_));
if (cb == null)
{
previosCar = curentCar;
cb = new CarBusiness();
cb.Month = month;
cb.ItemNo = (string)reader["No_"];
carBusinesses.Add(cb);
var number = ((string)reader["CarNumber"]).Trim();
var lastCharIndex = number.Last(c => char.IsLetter(c)) + 1;
if ((lastCharIndex) < number.Length)
{
number = number.Substring(lastCharIndex, number.Length - lastCharIndex);
}
cb.Name = $"{curentCar} ({number})";
}
buyer = (string)reader["Buyer"];
if (cb.Count > 0 && cb.Business.Last().Title.Equals(buyer))
{
cb.Business.Last().DateEnd = ((DateTime)reader["DateEnd"]);
}
else
{
var b = new Leasing();
b.DateStart = ((DateTime)reader["DateStart"]);
b.DateEnd = ((DateTime)reader["DateEnd"]);
b.Title = buyer;
b.Type = BusinessType.Leasing;
b.Comment = (string)reader["Comment"];
b.CurrentMonth = month;
cb.Add(b);
}
}
}
}
}
catch (Exception ex)
{
m_loger.Log("Возникло исключение при запросе выборки из БД", ex);
}
var monthBusiness = new MonthBusiness(carBusinesses);
monthBusiness.Month = month;
return(monthBusiness);
}
public void Init()
{
carBusiness = new CarBusiness();
car = new Car();
}
