public async Task <QueryResultList <VechicleList> > ExecuteAsync(VehicleQueriesHandler handler)
{
var sql = $@"
select
v.id, v.year, v.board, v.color_name, v.color_hex,
v.brand_name, v.model_name, v.fuel_name
from view_vehicles_list v
left join announcements a on a.id_vehicle=v.id
where a.date_sale is null
{(Year != null ? "and v.year=@Year " : null)}
{(FuelId != null ? "and v.fuel_id=@FuelId " : null)}
{(ColorId != null ? "and v.color_id=@ColorId " : null)}
{(!string.IsNullOrWhiteSpace(ModelId) ? "and v.model_id=@ModelId " : null)}
{(!string.IsNullOrWhiteSpace(BrandId) ? "and v.brand_id=@BrandId " : null)}
order by v.date_creation desc;
";
var parameters = new
{
Year,
ColorId,
FuelId,
ModelId,
BrandId
};
var vehicles = await handler.DbConnection.QueryAsync <VechicleList>(sql, parameters);
return(new QueryResultList <VechicleList>(vehicles));
}
public async Task <QueryResultOne <ModelDetail> > ValidateAsync(VehicleQueriesHandler handler)
{
if (string.IsNullOrWhiteSpace(Id))
{
return(new QueryResultOne <ModelDetail>(EStatusCode.InvalidData, $"Parameter {nameof(Id)} is required"));
}
return(await Task.FromResult <QueryResultOne <ModelDetail> >(null));
}
public async Task <QueryResultList <AnnouncementReportList> > ValidateAsync(VehicleQueriesHandler handler)
{
if (StartDate > EndDate)
{
return(new QueryResultList <AnnouncementReportList>(EStatusCode.InvalidData, $"Paramenter {nameof(StartDate)} cannot be greater than the {nameof(EndDate)}"));
}
return(await Task.FromResult <QueryResultList <AnnouncementReportList> >(null));
}
public AuthenticationHandler(
IOptionsMonitor <AuthenticationSchemeOptions> options,
ILoggerFactory logger, UrlEncoder encoder, ISystemClock clock,
VehicleMutationsHandler mutationsHandler, VehicleQueriesHandler queriesHandler
) : base(options, logger, encoder, clock)
{
_mutationsHanlder = mutationsHandler;
_queriesHanlder = queriesHandler;
}
public BaseTests(VehicleFixture fixture, string url)
{
Request = fixture.Request;
EntitiesFactory = fixture.EntitiesFactory;
MutationsDbContext = fixture.MutationsHandler.DbContext;
MutationsHandler = fixture.MutationsHandler;
QueriesHandler = fixture.QueriesHandler;
Uri = new Uri($"{fixture.Client.BaseAddress}{url}");
}
public async Task <QueryResultList <BrandList> > ExecuteAsync(VehicleQueriesHandler handler)
{
var sql = @"
select id, name
from brands
order by name;
";
var brands = await handler.DbConnection.QueryAsync <BrandList>(sql);
return(new QueryResultList <BrandList>(brands));
}
public async Task <QueryResultList <ColorList> > ExecuteAsync(VehicleQueriesHandler handler)
{
var sql = @"
select
c.id, c.name, c.hex as color_hex
from colors c
order by c.name asc;
";
var colors = await handler.DbConnection.QueryAsync <ColorList>(sql);
return(new QueryResultList <ColorList>(colors));
}
public async Task <QueryResultList <FuelList> > ExecuteAsync(VehicleQueriesHandler handler)
{
var sql = @"
select
f.id, f.name
from fuels f
order by f.name asc;
";
var fuels = await handler.DbConnection.QueryAsync <FuelList>(sql);
return(new QueryResultList <FuelList>(fuels));
}
public async Task <QueryResultList <AnnouncementSelectList> > ExecuteAsync(VehicleQueriesHandler handler)
{
var sql = $@"
select a.id, a.vehicle_name as name
from view_announcements_list a
where a.date_sale is null
{(!IncludeReserved ? " and not exists(select r.id from reservations r where r.id_announcement=a.id)" : null)}
order by a.vehicle_name asc;
";
var announcements = await handler.DbConnection.QueryAsync <AnnouncementSelectList>(sql);
return(new QueryResultList <AnnouncementSelectList>(announcements));
}
public async Task <QueryResultList <ModelSelectList> > ExecuteAsync(VehicleQueriesHandler handler)
{
var sql = $@"
select m.id, m.name
from models m
where 1=1
{(!string.IsNullOrWhiteSpace(BrandId) ? "and m.id_brand=@BrandId" : null)}
order by m.name asc;
";
var models = await handler.DbConnection.QueryAsync <ModelSelectList>(sql, new { BrandId });
return(new QueryResultList <ModelSelectList>(models));
}
public async Task <QueryResultList <ReservationList> > ExecuteAsync(VehicleQueriesHandler handler)
{
var sql = @"
select
r.id, r.contact_name, r.contact_phone,
r.vehicle_model_name, r.vehicle_brand_name
from view_reservations_list r
where r.date_sale is null
order by r.date_creation asc;
";
var reservations = await handler.DbConnection.QueryAsync <ReservationList>(sql);
return(new QueryResultList <ReservationList>(reservations));
}
public async Task <QueryResultList <VehicleSelectList> > ExecuteAsync(VehicleQueriesHandler handler)
{
var sql = $@"
select v.id, v.name
from view_vehicles_list v
where 1=1
{(!IncludeAnnouncements ? "and not exists(select a.id from announcements a where a.id_vehicle=v.id)" : null)}
{(!string.IsNullOrWhiteSpace(ModelId) ? "and v.model_id=@ModelId" : null)}
order by v.name asc;
";
var vehicles = await handler.DbConnection.QueryAsync <VehicleSelectList>(sql, new { ModelId });
return(new QueryResultList <VehicleSelectList>(vehicles));
}
public async Task <QueryResultOne <VehiclePhoto> > ExecuteAsync(VehicleQueriesHandler handler)
{
var fileName = $"{Id}.jpg";
var fileBytes = await Base64.LoadFileAsync(EPath.Photos, fileName);
if (fileBytes == null)
{
return(new QueryResultOne <VehiclePhoto>(EStatusCode.NotFound, $"File with {nameof(Id)}: {Id} does not exists"));
}
var fileResult = new VehiclePhoto {
Bytes = fileBytes, FileName = fileName
};
return(new QueryResultOne <VehiclePhoto>(fileResult));
}
public async Task <QueryResultOne <BrandDetail> > ExecuteAsync(VehicleQueriesHandler handler)
{
var sql = @"
select b.id, b.name
from brands b
where b.id=@Id;
";
var brand = await handler.DbConnection.QueryFirstOrDefaultAsync <BrandDetail>(sql, new { Id });
if (brand == null)
{
return(new QueryResultOne <BrandDetail>(EStatusCode.NotFound, $"Brand with id: {Id} does not exists"));
}
return(new QueryResultOne <BrandDetail>(brand));
}
public async Task <QueryResultList <AnnouncementList> > ExecuteAsync(VehicleQueriesHandler handler)
{
var sortColumnIndex = Array.IndexOf(columnsSort, SortColumn) + 1;
var sortOrderStr = SortOrder == EOrder.Asc ? "asc" : "desc";
var sqlBody = $@"
from view_announcements_list a
where 1=1
{(!IncludeReserved? $" and not exists(select r.id from reservations r where r.id_announcement=a.id)" : null)}
{(!IncludeSold && DateSale == null ? $" and a.date_sale is null" : null)}
{(DateSale != null ? $" and date_format(a.date_sale,'%Y-%m-%d')=@DataSale" : null)}
{(Year != null ? $" and a.vehicle_year=@Year" : null)}
{(ColorId != null ? $" and a.color_id=@ColorId" : null)}
{(BrandId != null ? $" and a.brand_id=@BrandId" : null)}
{(ModelId != null ? $" and a.model_id=@ModelId" : null)}
";
var sqlResult = $@"
-- paginate rows
select
a.id, a.date_creation, a.date_sale, a.price_purchase, a.price_sale,
a.vehicle_id, a.vehicle_year, a.vehicle_model_name, a.vehicle_brand_name, a.vehicle_fuel_name,
a.vehicle_color_name, a.vehicle_color_hex, a.photo_date as vehicle_photo_date
{sqlBody}
order by {sortColumnIndex} {sortOrderStr}
limit @Offset, @Limit;
-- count rows
select count(a.id) as total {sqlBody};
";
var parameters = new {
Year,
ColorId,
BrandId,
ModelId,
DataSale = DateSale?.ToString("yyyy-MM-dd"),
Offset = (Page - 1) * Limit,
Limit,
};
var data = await handler.DbConnection.QueryMultipleAsync(sqlResult, parameters);
var announcements = await data.ReadAsync <AnnouncementList>();
var totalRows = await data.ReadFirstAsync <Int64>();
return(new QueryResultList <AnnouncementList>(announcements, totalRows));
}
public async Task <QueryResultOne <ModelDetail> > ExecuteAsync(VehicleQueriesHandler handler)
{
var sql = @"
select m.id, m.name, b.id as brand_id, b.name as brand_name
from models m
join brands b on b.id=m.id_brand
where m.id=@Id;
";
var model = await handler.DbConnection.QueryFirstOrDefaultAsync <ModelDetail>(sql, new { Id });
if (model == null)
{
return(new QueryResultOne <ModelDetail>(EStatusCode.NotFound, $"Model with id: {Id} is not found"));
}
return(new QueryResultOne <ModelDetail>(model));
}
public async Task <QueryResultList <AnnouncementList> > ValidateAsync(VehicleQueriesHandler handler)
{
if (Page <= 0)
{
return(new QueryResultList <AnnouncementList>(EStatusCode.InvalidData, $"Parameter {nameof(Page)} require postive"));
}
if (Limit <= 0)
{
return(new QueryResultList <AnnouncementList>(EStatusCode.InvalidData, $"Paramter {nameof(Limit)} require positive"));
}
if (!columnsSort.Contains(SortColumn))
{
return(new QueryResultList <AnnouncementList>(EStatusCode.InvalidData, $"Parameter {nameof(SortColumn)} require in {string.Join(", ", columnsSort)}"));
}
return(await Task.FromResult <QueryResultList <AnnouncementList> >(null));
}
public async Task <QueryResultOne <ReservationDetail> > ExecuteAsync(VehicleQueriesHandler handler)
{
var sql = @"
select
r.id, r.contact_name, r.contact_phone,
r.announcement_id, r.announcement_name
from view_reservations_list r
where r.id=@Id
";
var reservation = await handler.DbConnection.QueryFirstOrDefaultAsync <ReservationDetail>(sql, new { Id });
if (reservation == null)
{
return(new QueryResultOne <ReservationDetail>(EStatusCode.NotFound, $"Reservation with id: {Id} does not exists"));
}
return(new QueryResultOne <ReservationDetail>(reservation));
}
public async Task <QueryResultOne <AnnouncementDetail> > ExecuteAsync(VehicleQueriesHandler handler)
{
var sql = @"
select
a.id, a.price_purchase, a.price_sale,
a.date_sale, a.vehicle_id, a.vehicle_name,
a.brand_id, a.vehicle_brand_name as brand_name,
a.model_id, a.vehicle_model_name as model_name
from view_announcements_list a
where a.id=@Id
";
var announcement = await handler.DbConnection.QueryFirstOrDefaultAsync <AnnouncementDetail>(sql, new { Id });
if (announcement == null)
{
return(new QueryResultOne <AnnouncementDetail>(EStatusCode.NotFound, $"Announcement with id: {Id} does not exists"));
}
return(new QueryResultOne <AnnouncementDetail>(announcement));
}
private void ConfigureServices(IServiceCollection services)
{
var contentRoot = GetProjectPath();
var manager = new ApplicationPartManager
{
ApplicationParts =
{
new AssemblyPart(typeof(Startup).GetTypeInfo().Assembly)
},
FeatureProviders =
{
new ControllerFeatureProvider(),
new ViewComponentFeatureProvider()
}
};
services.AddDbContext <VehicleMutationsDbContext>(options => options
.UseMySql(Configuration.GetConnectionString("VehicleMutations"))
.EnableDetailedErrors()
);
services.AddDbContext <VehicleQueriesDbContext>(options => options
.UseMySql(Configuration.GetConnectionString("VehicleQueries"))
.EnableDetailedErrors()
);
var scope = services
.BuildServiceProvider()
.CreateScope();
var scopedServices = scope.ServiceProvider;
MutationsDbContext = scopedServices.GetRequiredService <VehicleMutationsDbContext>();
QueriesDbContext = scopedServices.GetRequiredService <VehicleQueriesDbContext>();
MutationsHandler = new VehicleMutationsHandler(MutationsDbContext);
QueriesHandler = new VehicleQueriesHandler(QueriesDbContext);
EntitiesFactory = new EntitiesFactory(MutationsDbContext);
MutationsDbContext.Database.EnsureCreated();
services.AddSingleton(manager);
}
public async Task <QueryResultList <AnnouncementReportList> > ExecuteAsync(VehicleQueriesHandler handler)
{
var sql = $@"
select
a.id, a.date_sale, a.vehicle_model_name,
a.vehicle_brand_name, a.profit
from view_announcements_list a
where a.date_sale is not null
{(StartDate != null ? " and a.date_sale>=@StartDate" : null)}
{(EndDate != null ? " and a.date_sale<=@EndDate" : null)}
order by a.date_creation desc;
";
var parameters = new {
StartDate = StartDate?.ToString("yyyy-MM-dd"),
EndDate = EndDate?.ToString("yyyy-MM-dd")
};
var announcements = await handler.DbConnection.QueryAsync <AnnouncementReportList>(sql, parameters);
return(new QueryResultList <AnnouncementReportList>(announcements));
}
public async Task <QueryResultOne <VehicleDetail> > ExecuteAsync(VehicleQueriesHandler handler)
{
var sql = @"
select
v.id, v.year, v.color_id, v.color_name, v.color_hex, photo_date,
v.fuel_id, v.fuel_name, v.brand_id, v.brand_name, v.model_id, v.model_name
from view_vehicles_list v
where v.id=@Id
";
var vehicle = await handler.DbConnection.QueryFirstOrDefaultAsync <VehicleDetail>(sql, new { Id });
if (vehicle == null)
{
return(new QueryResultOne <VehicleDetail>(EStatusCode.NotFound, $"Vehicle with {nameof(Id)} does not exists"));
}
if (vehicle.PhotoDate != null)
{
vehicle.ImageBase64 = await Base64.LoadBase64Async(EPath.Photos, $"{vehicle.Id}.jpg");
}
return(new QueryResultOne <VehicleDetail>(vehicle));
}
public VehiclesController(VehicleMutationsHandler mutationsHandler, VehicleQueriesHandler queriesHandler)
{
_mutationsHanlder = mutationsHandler;
_queriesHanlder = queriesHandler;
}
public async Task <QueryResultList <BrandList> > ValidateAsync(VehicleQueriesHandler handler)
{
return(await Task.FromResult <QueryResultList <BrandList> >(null));
}
