public async Task <FaceDto> GetFaces(Guid id)
{
using (DashboardDbContext dbContext = new DashboardDbContext())
{
return(await dbContext.Faces
.Select(x => new FaceDto
{
Id = x.Id,
FaceId = x.FaceId,
FileUrl = x.FileUrl,
Age = x.Age,
Gender = x.Gender,
Glassess = x.Glassess,
Smile = x.Smile,
Moustache = x.Moustache,
Beard = x.Beard,
Sideburns = x.Sideburns,
Pitch = x.Pitch,
Yaw = x.Yaw,
Roll = x.Roll,
FaceRectangleHeight = x.FaceRectangleHeight,
FaceRectangleLeft = x.FaceRectangleLeft,
FaceRectangleTop = x.FaceRectangleTop,
FaceRectangleWidth = x.FaceRectangleWidth
}).FirstAsync(x => x.FaceId == id));
}
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="logger">ILogger</param>
/// <param name="context">DbContext</param>
public PortalCrudService(
ILogger <PortalCrudService> logger,
DashboardDbContext context)
{
_logger = logger;
_context = context;
}
/// <summary>
/// 收货区域订单统计报表
/// </summary>
/// <returns></returns>
public List <StatisticalReports> AreaOrderStatisticalReport(string province, string city)
{
var sql = "";
if (string.IsNullOrWhiteSpace(province) && string.IsNullOrWhiteSpace(city))
{
sql = @"select d.FullName as Times,IFNULL(sum(o.PayFee),0) as Sales,IFNULL(SUM(o.RefundFee),0) as Refund,count(1) as OrderNumber from bnt_orders o
LEFT JOIN bnt_system_districts d on o.Province=d.Id
where o.OrderStatus in(1,2,3)
GROUP BY o.Province;";
}
if (!string.IsNullOrWhiteSpace(province) && string.IsNullOrWhiteSpace(city))
{
sql = @"select d.FullName as Times,IFNULL(sum(o.PayFee),0) as Sales,IFNULL(SUM(o.RefundFee),0) as Refund,count(1) as OrderNumber from bnt_orders o
LEFT JOIN bnt_system_districts d on o.City=d.Id
where o.OrderStatus in(1,2,3) and o.Province='{0}'
GROUP BY o.City;";
sql = string.Format(sql, province);
}
if (!string.IsNullOrWhiteSpace(province) && !string.IsNullOrWhiteSpace(city))
{
sql = @"select d.FullName as Times,IFNULL(sum(o.PayFee),0) as Sales,IFNULL(SUM(o.RefundFee),0) as Refund,count(1) as OrderNumber from bnt_orders o
LEFT JOIN bnt_system_districts d on o.District=d.Id
where o.OrderStatus in(1,2,3) and o.City='{0}'
GROUP BY o.District;";
sql = string.Format(sql, city);
}
using (var dbContext = new DashboardDbContext())
{
var report = dbContext.Database.SqlQuery <StatisticalReports>(sql).ToList();
return(report);
}
}
public async void Create_User()
{
var options = new DbContextOptionsBuilder <DashboardDbContext>()
.UseInMemoryDatabase(databaseName: "Create_New_User")
.Options;
var user = new User()
{
UserId = 1,
Email = "*****@*****.**",
Name = "John",
Surname = "Smith",
Password = "******",
IsActive = true
};
using (var context = new DashboardDbContext(options))
{
var mockCheckEmailService = new CheckEmailService(new DashboardDbContext(options), new Mock <ILogger <CheckEmailService> >().Object);
var service = new UserCrudService(context, _mockLogger.Object, mockCheckEmailService, _mockSendGridService.Object);
await service.CreateUserAsync(user);
await context.SaveChangesAsync();
}
using (var context = new DashboardDbContext(options))
{
Assert.Equal(1, context.Users.CountAsync().Result);
Assert.Equal(user.Name, context.Users.SingleAsync().Result.Name);
}
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(
IApplicationBuilder app,
IWebHostEnvironment env,
DashboardDbContext context,
IOptions <MainUserData> options
)
{
context.Database.Migrate();
DatabaseInitializer.Initialize(context, options);
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
app.UseSwagger();
app.UseSwaggerUI(c =>
{
c.SwaggerEndpoint("/swagger/v1/swagger.json", "My API V1");
});
app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthentication();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="context">DbContext</param>
/// <param name="logger">ILogger</param>
public UserAuthService(
DashboardDbContext context,
ILogger <UserAuthService> logger
)
{
_context = context;
_logger = logger;
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="context">DbContext</param>
/// <param name="logger">ILogger</param>
public CheckEmailService(
DashboardDbContext context,
ILogger <CheckEmailService> logger
)
{
_context = context;
_logger = logger;
}
public static bool Initialize(DashboardDbContext context, IOptions <MainUserData> options)
{
context.Database.EnsureCreated();
if (string.IsNullOrWhiteSpace(options.Value.Email) ||
string.IsNullOrWhiteSpace(options.Value.Password))
{
return(false);
}
var email = options.Value.Email;
var password = options.Value.Password;
var name = options.Value.Name ?? "";
var surname = options.Value.Surname ?? "";
if (!IsValidEmail(email))
{
return(false);
}
var userInDb = context.Users.Where(u => u.Email == email).FirstOrDefault();
if (userInDb == null)
{
var user = new User()
{
Email = email,
Name = name,
Surname = surname,
Password = new HashService().Hash(password),
IsActive = true,
isPermanent = true,
Claims = new string[] { ClaimType.isAdmin.ToString() }
};
try
{
context.Add(user);
context.SaveChanges();
return(true);
}
catch (Exception)
{
return(false);
}
}
userInDb.Password = new HashService().Hash(password);
userInDb.Name = name;
userInDb.Surname = surname;
try
{
context.SaveChanges();
return(true);
}
catch (Exception)
{
return(false);
}
}
public List <StatisticalReports> MonthStatisticalReports(string year)
{
using (var dbContext = new DashboardDbContext())
{
var sqlorder = $"SELECT MONTH(CreateTime) as Times ,SUM(PayFee) AS Sales , COUNT(*) AS OrderNumber,SUM(RefundFee) as Refund from bnt_orders WHERE YEAR(CreateTime)={year} and (OrderStatus=1 OR OrderStatus=2 OR OrderStatus=3) GROUP BY MONTH(CreateTime)";
var applyWork = dbContext.Database.SqlQuery <StatisticalReports>(sqlorder).ToList();
return(applyWork);
}
}
public List <StatisticalReports> YearStatisticalReportsMember()
{
using (var dbContext = new DashboardDbContext())
{
var sqlorder = "select *,(select Sum(NewMemberNumber) from bnt_view_member_state_year where Times<=s.Times) as MemberNumber from bnt_view_member_state_year s";
var applyWork = dbContext.Database.SqlQuery <StatisticalReports>(sqlorder).ToList();
return(applyWork);
}
}
public List <StatisticalReports> MonthStatisticalReportsMember(string year)
{
using (var dbContext = new DashboardDbContext())
{
var sqlorder = $"select s.Months as Times ,s.NewMemberNumber,(select Sum(NewMemberNumber) from bnt_view_member_state_month where YearsMonths<=s.YearsMonths ) as MemberNumber from bnt_view_member_state_month s where s.years={year} ORDER BY Months";
var applyWork = dbContext.Database.SqlQuery <StatisticalReports>(sqlorder).ToList();
return(applyWork);
}
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="context">DbContext</param>
/// <param name="http">HttpClient</param>
/// <param name="notificationsService">Email Notification service</param>
public QueryPortalService(
DashboardDbContext context,
HttpClient http,
INotificationsService notificationsService
)
{
_context = context;
_http = http;
_notificationsService = notificationsService;
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="context">DbContext</param>
/// <param name="logger">ILogger</param>
/// <param name="checkEmailService">Check Email Service</param>
/// <param name="sendGridService">Sending email notification service</param>
public UserCrudService(
DashboardDbContext context,
ILogger <UserCrudService> logger,
ICheckEmailService checkEmailService,
ISendGridService sendGridService
)
{
_context = context;
_logger = logger;
_checkEmailService = checkEmailService;
_sendGridService = sendGridService;
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="secret">JWT Token Secret Key</param>
/// <param name="options">JWT Token options</param>
/// <param name="logger">Ilogger</param>
/// <param name="context">Database</param>
public TokenGeneratorService(
IOptions <JwtTokenSecretKey> secret,
IOptions <JwtTokenOptions> options,
ILogger <TokenGeneratorService> logger,
DashboardDbContext context)
{
_secret = secret.Value.jwtTokenSecretKey;
_issuer = options.Value.issuer;
_audience = options.Value.audience;
_accessTokenExpirationInMinutes = options.Value.AccessTokenExpirationInMinutes;
_logger = logger;
_context = context;
}
private static void MigrateDatabaseProd()
{
IConfigurationRoot configuration = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json")
.Build();
var builder = new DbContextOptionsBuilder <DashboardDbContext>();
var connectionString = configuration.GetConnectionString("Postgres");
builder.UseNpgsql(connectionString);
using (var context = new DashboardDbContext(builder.Options))
{
context.Database.Migrate();
}
}
public async void Get_One()
{
var options = new DbContextOptionsBuilder <DashboardDbContext>()
.UseInMemoryDatabase(databaseName: "Get_One")
.Options;
var user1 = new User()
{
UserId = 1,
Email = "*****@*****.**",
Name = "John",
Surname = "Smith",
Password = "******",
IsActive = true
};
var user2 = new User()
{
UserId = 2,
Email = "*****@*****.**",
Name = "John",
Surname = "Smith",
Password = "******",
IsActive = true
};
using (var context = new DashboardDbContext(options))
{
var mockCheckEmailService = new CheckEmailService(new DashboardDbContext(options), new Mock <ILogger <CheckEmailService> >().Object);
var service = new UserCrudService(context, _mockLogger.Object, mockCheckEmailService, _mockSendGridService.Object);
await service.CreateUserAsync(user1);
await service.CreateUserAsync(user2);
await context.SaveChangesAsync();
}
using (var context = new DashboardDbContext(options))
{
var mockCheckEmailService = new CheckEmailService(new DashboardDbContext(options), new Mock <ILogger <CheckEmailService> >().Object);
var service = new UserCrudService(context, _mockLogger.Object, mockCheckEmailService, _mockSendGridService.Object);
var result = await service.GetOneAsync(user1.UserId);
Assert.Equal(user1.Email, result.Email);
}
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env, DashboardDbContext dashboardDbContext)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
app.UseDefaultFiles();
app.UseStaticFiles();
app.UseHttpsRedirection();
app.UseRouting();
dashboardDbContext.Database.EnsureCreated();
app.UseAuthentication();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
}
public StatisticalReports StatisticalReports(DateTime?startTime = null, DateTime?endTime = null)
{
var checkStartTime = startTime == null;
var checkEndTime = endTime == null;
using (var dbContext = new DashboardDbContext())
{
var sqlorder = from o in dbContext.Orders
where (checkStartTime || o.CreateTime >= startTime) && (checkEndTime || o.CreateTime <= endTime) &&
(o.OrderStatus == OrderStatus.WaitingForDelivery ||
o.OrderStatus == OrderStatus.WaitingForReceiving || o.OrderStatus == OrderStatus.Completed)
select o;
return(new StatisticalReports()
{
Times = $"{startTime?.ToString("yyyy/MM/dd")}-{(endTime==null? DateTime.Now.ToString("yyyy/MM/dd"):endTime?.ToString("yyyy/MM/dd"))}",
OrderNumber = sqlorder.Count(),
Sales = sqlorder.Sum(s => (decimal?)s.PayFee) ?? 0,
Refund = sqlorder.Sum(s => (decimal?)s.RefundFee) ?? 0
});
}
}
public AuthorizationController(
IUserAuthService authService,
IMapper mapper,
IConfiguration config,
ITokenGeneratorService jwtTokenGenerator,
IOptions <JwtTokenSecretKey> secret,
IOptions <JwtTokenOptions> options,
DashboardDbContext context,
IOptions <MainUserData> optionsMainUser
)
{
_authService = authService;
_mapper = mapper;
_config = config;
_jwtTokenGenerator = jwtTokenGenerator;
_context = context;
_optionsMainUser = optionsMainUser;
_jwtTokenSecretKey = secret.Value.jwtTokenSecretKey;
_issuer = options.Value.issuer;
_audience = options.Value.audience;
}
public async void Authorize_Valid_User()
{
var options = new DbContextOptionsBuilder <DashboardDbContext>()
.UseInMemoryDatabase(databaseName: "Authorize_Valid_User")
.Options;
var user = new User()
{
Email = "*****@*****.**",
Password = "******",
IsActive = true
};
using (var context = new DashboardDbContext(options))
{
var serviceCheckEmail = new CheckEmailService(context, _mockLoggerCheckEmail.Object);
var serviceCrud = new UserCrudService(context, _mockLoggerUserCrud.Object, serviceCheckEmail, _mockSendGridService.Object);
var userCreate = new User()
{
Email = user.Email,
Password = user.Password,
IsActive = user.IsActive
};
await serviceCrud.CreateUserAsync(userCreate);
await context.SaveChangesAsync();
}
using (var context = new DashboardDbContext(options))
{
var serviceAuth = new UserAuthService(context, _mockLogger.Object);
var result = serviceAuth.AuthUser(user);
Assert.Equal(1, await context.Users.CountAsync());
Assert.NotNull(result);
Assert.Equal(user.Email, result.Email);
}
}
public StatisticalReports StatisticalReportsMember(DateTime?startTime = null, DateTime?endTime = null)
{
var checkStartTime = startTime == null;
var checkEndTime = endTime == null;
using (var dbContext = new DashboardDbContext())
{
var sqlnewmember = from m in dbContext.Members
where (checkStartTime || m.CreateTime >= startTime) && (checkEndTime || m.CreateTime <= endTime)
select m.Id;
var sqlmember = from m in dbContext.Members
where (checkEndTime || m.CreateTime <= endTime)
select m.Id;
return(new StatisticalReports()
{
Times = $"{startTime?.ToString("yyyy/MM/dd")}-{(endTime == null ? DateTime.Now.ToString("yyyy/MM/dd") : endTime?.ToString("yyyy/MM/dd"))}",
//Times = $"{startTime}-{endTime}",
NewMemberNumber = sqlnewmember.Count(),
MemberNumber = sqlmember.Count()
});
}
}
protected BaseRepository(DashboardDbContext dbContext)
{
Db = dbContext;
}
public UserRepository(DashboardDbContext dbContext) : base(dbContext)
{
}
public ImagesController(DashboardDbContext dashboardDbContext)
{
_dashboardDbContext = dashboardDbContext;
}
public VehiclesController(DashboardDbContext dashboardDbContext)
{
_dashboardDbContext = dashboardDbContext;
}
public CategoriesController(DashboardDbContext dashboardDbContext)
{
_dashboardDbContext = dashboardDbContext;
}
public UnitOfWork(DashboardDbContext dbContext)
{
_dbContext = dbContext;
UserRepository = new UserRepository(dbContext);
StickerRepository = new StickerRepository(dbContext);
}
public DbRepository(DashboardDbContext _context)
{
this._context = _context;
}
public MakesController(DashboardDbContext context, IMapper mapper)
{
this.mapper = mapper;
this.context = context;
}
public ContaController(IConfiguration configuration, DashboardDbContext dashboardDbContext)
{
_dashboardDbContext = dashboardDbContext;
_configuration = configuration;
_auth = new AuthService(_configuration);
}
