public async Task <PageResultDto <AreaDto> > FilterAreas(PageDto pageDto, string searchKey)
{
var query = "@SearchKey is null or (bys_area.name LIKE @SearchKey OR bys_area.code LIKE @SearchKey OR bys_area.address LIKE @SearchKey)";
var param = new
{
SearchKey = searchKey.FormatSearch()
};
var count = await this.DatabaseConnectService.Connection.CountAsync <Areas>(x => x
.Where($"{query}")
.WithParameters(param));
var items = (await this.DatabaseConnectService.Connection.FindAsync <Areas>(x => x
.Where($"{query}")
.WithParameters(param)))
.Skip(pageDto.Page * pageDto.PageSize)
.Take(pageDto.PageSize)
.Select(x => x.ToAreaDto()).ToArray();
var result = new PageResultDto <AreaDto>
{
Items = items,
TotalCount = count,
PageIndex = pageDto.Page,
PageSize = pageDto.PageSize,
TotalPages = (int)Math.Ceiling(count / (double)pageDto.PageSize),
};
return(result);
}
public IActionResult Delete(List <string> ids)
{
var result = new PageResultDto();
try
{
List <InterInvite> parm = new List <InterInvite>();
foreach (var item in ids)
{
parm.Add(new InterInvite {
ID = item
});
}
var i = _services.Delete <InterInvite>(parm);
result.Code = i == 0 ? (int)ResultCode.SUCCESS : (int)ResultCode.INTERNAL_SERVER_ERROR;
result.Count = ids.Count;
result.Data = ids;
}
catch (Exception ex)
{
result.Code = (int)ResultCode.INTERNAL_SERVER_ERROR;
result.Msg = "内部操作错误，请联系管理员或查看错误日志。";
log.Error($"/{System.Reflection.MethodBase.GetCurrentMethod().Name}方法/错误信息：【{ex.Message}】");
}
return(Ok(result));
}
/// <summary>
///
/// </summary>
/// <param name="request"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public async Task <PageResultDto <ArticleListDto> > Handle(ArticlePageByCategoryQuery request, CancellationToken cancellationToken)
{
StringBuilder sqlBuilder = new StringBuilder(@"select SQL_CALC_FOUND_ROWS Articles.Id,Articles.Title,Articles.Remark,Articles.ReadCount,Articles.likeCount,Articles.CreateTime,ArticleCategory.Category as CategoryName
from Articles
LEFT JOIN ArticleCategory on Articles.CategoryId= ArticleCategory.Id ");
if (!String.IsNullOrEmpty(request.CategoryId))
{
sqlBuilder.Append("where Articles.CategoryId=@CategoryId ");
}
sqlBuilder.Append("limit @Skip,@Take;");
sqlBuilder.Append("SELECT FOUND_ROWS() as Total;");
var sql = sqlBuilder.ToString();
var dapperPageInfo = await _dapper.QueryPage <ArticleListDto>(sql, new { CategoryId = request.CategoryId, Skip = (request.Page - 1) * request.Rows, Take = request.Rows });
PageResultDto <ArticleListDto> result = new PageResultDto <ArticleListDto>()
{
State = 1,
Result = new ResultPage <ArticleListDto>()
{
TotalCount = (int)dapperPageInfo.TotalCount,
Data = dapperPageInfo.Data
}
};
return(result);
}
public IActionResult Delete(List <string> ids)
{
var result = new PageResultDto();
try
{
//string parm =$" '{string.Join("','",ids.ToArray())}'";
List <RetailBrand> parm = new List <RetailBrand>();
foreach (var item in ids)
{
parm.Add(new RetailBrand {
ID = item
});
}
var i = _services.Delete <RetailBrand>(parm);
//直接用dapper执行
//var i = _dapper.Execute("delete from retail_brand where id = @id", parm);
result.Code = i == 0 ? (int)ResultCode.SUCCESS : (int)ResultCode.INTERNAL_SERVER_ERROR;
result.Count = ids.Count;
result.Data = ids;
}
catch (Exception ex)
{
result.Code = (int)ResultCode.INTERNAL_SERVER_ERROR;
result.Msg = "内部操作错误，请联系管理员或查看错误日志。";
log.Error($"/{System.Reflection.MethodBase.GetCurrentMethod().Name}方法/错误信息：【{ex.Message}】");
}
return(Ok(result));
}
public async virtual Task <PageResultDto <TDto> > List(TListInput input)
{
var query = CreateFilterQuery(input.Filter);
query = CreateListQuery(query);
var page = new PageResultDto <TDto>();
//先算数量后排序
if (IsCalculateCount)
{
page.MaxCount = query.Count();
}
if (!string.IsNullOrEmpty(input.Order))
{
query = query.OrderBy(input.Order);
}
query = query.Skip(input.SkipCount);
if (input.MaxResultCount == 0)
{
input.MaxResultCount = 10;
}
query = query.Take(input.MaxResultCount);
page.Items = (await query.ToListAsync()).Adapt <List <TDto> >();
return(page);
}
//public async Task<List<Student>> GetPaginatedResult(int currentPage, string searchString, string sortBy, int pageSize = 10)
//{
// var query = _studentRepository.GetAll();
// if (!string.IsNullOrEmpty(searchString))
// {
// query = query.Where(s => s.Name.Contains(searchString) || s.Email.Contains(searchString));
// }
// query = query.OrderBy(sortBy);
// return await query.Skip((currentPage - 1) * pageSize).Take(pageSize).AsNoTracking().ToListAsync();
//}
public async Task <PageResultDto <Student> > GetPaginatedResult(GetStudentInput input)
{
var query = _studentRepository.GetAll();
if (!string.IsNullOrEmpty(input.FilterText))
{
query = query.Where(s => s.Name.Contains(input.FilterText) || s.Email.Contains(input.FilterText));
}
var count = query.Count();
query = query.OrderBy(input.Sorting).Skip((input.CurrentPage - 1) * input.MaxResultCount).Take(input.MaxResultCount);
var models = await query.AsNoTracking().ToListAsync();
var dtos = new PageResultDto <Student>
{
TotalCount = count,
CurrentPage = input.CurrentPage,
MaxResultCount = input.MaxResultCount,
Data = models,
FilterText = input.FilterText,
Sorting = input.Sorting
};
return(dtos);
}
public async Task <PageResultDto <Department> > GetPagedDepartmentsList(GetDepartmentInput input)
{
var query = _departmentRepository.GetAll();
if (!string.IsNullOrEmpty(input.FilterText))
{
query = query.Where(s => s.Name.Contains(input.FilterText));
}
var count = query.Count();
query = query.OrderBy(input.Sorting).Skip((input.CurrentPage - 1) * input.MaxResultCount).Take(input.MaxResultCount);
//将查询结果转换为集合加载到内存中
var models = await query.Include(a => a.Administrator).AsNoTracking().ToListAsync();
var dtos = new PageResultDto <Department>
{
TotalCount = count,
CurrentPage = input.CurrentPage,
MaxResultCount = input.MaxResultCount,
Data = models,
FilterText = input.FilterText,
Sorting = input.Sorting
};
return(dtos);
}
public async Task <PageResultDto <FeedDto> > SearchAsync(FeedQuerySearch querySearch)
{
var query = _unitOfWork.FeedRepository.FindAll();
if (!string.IsNullOrEmpty(querySearch.SearchKey))
{
query = query.Where(x => x.Content.Contains(querySearch.SearchKey));
}
if (!string.IsNullOrEmpty(querySearch.UserSlug))
{
query = query.Where(x => x.UserSlug.Equals(querySearch.UserSlug));
}
var feeds = await query.OrderByDescending(x => x.CreatedAt).Skip(querySearch.GetSkip()).Take(querySearch.GetTake()).ToListAsync();
var result = new PageResultDto <FeedDto>(await query.CountAsync(), GetTotalPage(await query.CountAsync(), querySearch.GetTake()), feeds.Select(x => x.ToDto()).ToList());
foreach (var item in result.Items)
{
var user = await _unitOfWork.UserRepository
.FindAll(x => x.Id.Equals(item.CreatedBy))
.Include(x => x.UserDetail)
.SingleOrDefaultAsync();
item.FullName = $"{user?.UserDetail?.FirstName} {user?.UserDetail?.LastName}";
item.AvatarUrl = user?.UserDetail?.Avatar ?? $"{ApiUrl}/assets/no_image.png";
item.UserSlug = querySearch.UserSlug;
}
return(result);
}
public async Task <PageResultDto <BusDto> > GetAllPagingAsync(GetBusPagingRequestDto request)
{
// Select and Join
var query = from b in _context.Buses
where b.IsDeleted == false
from dr in _context.Drivers.Where(dr => dr.Id == b.DriverId).DefaultIfEmpty()
from m in _context.AppUsers.Where(m => b.MonitorId == m.Id).DefaultIfEmpty()
from r in _context.Routes.Where(r => b.RouteId == r.Id).DefaultIfEmpty()
select new { b, dr, m, r };
// Filter
if (request.LicenseCode != null)
{
query = query.Where(x => x.b.LicenseCode.Contains(request.LicenseCode));
}
if (request.Status != null)
{
query = query.Where(x => (int)x.b.Status == request.Status);
}
if (request.DriverName != null)
{
query = query.Where(x => x.dr.Name.Contains(request.DriverName));
}
if (request.RouteName != null)
{
query = query.Where(x => x.r.Name.Contains(request.RouteName));
}
// Paging
int totalRow = await query.CountAsync();
var data = await query.Skip((request.PageIndex - 1) *request.PageSize)
.Take(request.PageSize)
.Select(x => new BusDto()
{
Id = x.b.Id,
LicenseCode = x.b.LicenseCode,
Name = x.b.Name,
MaxSize = x.b.MaxSize,
MaxSpeed = x.b.MaxSpeed,
Description = x.b.Description,
Status = (int)x.b.Status,
DriverId = x.dr == null ? -1 : x.dr.Id,
DriverName = x.dr == null ? null : x.dr.Name,
MonitorId = x.m == null ? Guid.Empty : x.m.Id,
MonitorName = x.m == null ? null : x.m.FullName,
RouteId = x.r == null ? -1 : x.r.Id,
RouteName = x.r == null ? null : x.r.Name
})
.ToListAsync();
// Return
var pageResutlDto = new PageResultDto <BusDto>()
{
StatusCode = ResponseCode.Success,
Items = data,
TotalRecord = totalRow
};
return(pageResutlDto);
}
public void SearchGroupReturnsStatusOK()
{
var countryDto = GetDto(typeof(CountryDto).FullName, DtoRetriveMethods.FakeOne);
var total = 1;
var items = new List <CountryDto> {
countryDto
};
var pageResultDto = new PageResultDto <CountryDto>(total, items);
_mockService.Setup(x => x.SearchAsync(It.IsAny <Expression <Func <Country, bool> > >(), 0, 10))
.Returns(Task.FromResult(pageResultDto));
var controller = InitController(new[] { _mockService.Object });
var result = Execute <PageResultDto <CountryDto> >(() => controller.SearchCountry("name"));
Assert.IsNotNull(result);
Assert.AreEqual(HttpStatusCode.OK, result.StatusCode);
var requestUrl = result.Response.RequestMessage.RequestUri.ToString();
Assert.AreEqual(_url, requestUrl);
Assert.AreEqual(API_PREFIX, GetPrefix(requestUrl));
Assert.IsNotNull(result.Items);
Assert.AreEqual(result.Items.TotalRecord, pageResultDto.TotalRecord);
Assert.AreEqual(result.Items.ToTalPage, pageResultDto.ToTalPage);
}
public async Task <PageResultDto <ShrimpBreedDto> > FilterShrimpBreed(PageDto pageDto, string searchKey)
{
var query = new StringBuilder();
query.Append("@SearchKey is null OR (bys_shrimp_breed.code LIKE @SearchKey OR bys_shrimp_breed.name LIKE @SearchKey OR bys_shrimp_breed.description LIKE @SearchKey)");
var param = new
{
SearchKey = searchKey.FormatSearch()
};
var count = await this.DatabaseConnectService.Connection.CountAsync <ShrimpBreed>(x => x
.Where($"{query}")
.WithParameters(param));
var items = (await this.DatabaseConnectService.Connection.FindAsync <ShrimpBreed>(x => x
.Where($"{query}")
.WithParameters(param)))
.Skip(pageDto.Page * pageDto.PageSize)
.Take(pageDto.PageSize)
.Select(x => x.ToShrimpBreedDto()).ToArray();
var result = new PageResultDto <ShrimpBreedDto>
{
Items = items,
TotalCount = count,
PageIndex = pageDto.Page,
PageSize = pageDto.PageSize,
TotalPages = (int)Math.Ceiling(count / (double)pageDto.PageSize),
};
return(result);
}
public IActionResult Query(int page = 1, int limit = 20, string search = "")
{
var result = new PageResultDto();
try
{
DynamicParameters parm = new DynamicParameters();
parm.Add("search", search);
parm.Add("page", page);
parm.Add("size", limit);
parm.Add("@count", dbType: System.Data.DbType.Int32, direction: System.Data.ParameterDirection.Output);
var par = new PageParameters <InterInvite>();
par.Proc = new PageProc {
ProcName = "pr_inter_invite_list", ProcParm = parm, ProcOutName = "count"
};
var list = _services.Query(par, out int outCount);
result.Data = list;
result.Count = outCount;
}
catch (Exception ex)
{
result.Code = (int)ResultCode.INTERNAL_SERVER_ERROR;
result.Msg = "内部操作错误，请联系管理员或查看错误日志。";
log.Error($"/{System.Reflection.MethodBase.GetCurrentMethod().Name}方法/错误信息：【{ex.Message}】");
}
return(Ok(result));
}
public async Task <IHttpActionResult> GetAll()
{
var result = await _collection.Find(Builders <NotifyKiosk> .Filter.Where(n => n.UserId.Equals(_userId))).ToListAsync();
var pageResult = new PageResultDto <NotifyKioskDto>(result.Count(), result.Select(x => Mapper.Map <NotifyKioskDto>(x)).ToArray());
return(Ok(pageResult));
}
public async Task <PageResultDto <StopDto> > GetAllPaging(GetStopPagingReqestDto request)
{
var query = from s in _context.Stops
where s.IsDeleted == false
join r in _context.Routes on s.RouteId equals r.Id
select new { s, r };
// Filter
if (!string.IsNullOrEmpty(request.Name))
{
query = query.Where(x => x.s.Name.Contains(request.Name));
}
if (!string.IsNullOrEmpty(request.Address))
{
query = query.Where(x => x.s.Address.Contains(request.Address));
}
if (request.Status >= 0)
{
query = query.Where(x => (int)x.s.Status == request.Status);
}
if (request.RouteId != null)
{
query = query.Where(x => x.s.RouteId == request.RouteId);
}
// Paging
int totalRow = await query.CountAsync();
var data = await query.Skip((request.PageIndex - 1) *request.PageSize)
.Take(request.PageSize)
.Select(x => new StopDto()
{
Id = x.s.Id,
Name = x.s.Name,
Address = x.s.Address,
Longitude = x.s.Longitude,
Latitude = x.s.Latitude,
TimePickUp = x.s.TimePickUp,
TimeDropOff = x.s.TimeDropOff,
NumberOfStudents = x.s.NumberOfStudents,
Status = (int)x.s.Status,
TypeStop = (int)x.s.TypeStop,
RouteId = x.s.RouteId,
RouteName = x.r.Name
}).ToListAsync();
// Return
var pageResult = new PageResultDto <StopDto>()
{
StatusCode = ResponseCode.Success,
Message = "Thành công",
TotalRecord = totalRow,
Items = data
};
return(pageResult);
}
public async Task <IActionResult> EditAsync()
{
//返回值对象
var result = new PageResultDto();
//获取表单提交的数据（文件和文本框等）
var files = Request.Form.Files;
var para = Request.Form["data"];
//将表单数据转为实体对象
var model = System.Text.Json.JsonSerializer.Deserialize <InterInvite>(para);
if (model == null || string.IsNullOrEmpty(model.CompanyID))
{
result.Code = (int)ResultCode.UNAUTHORIZED;
result.Msg = "参数无效";
return(Ok(result));
}
model.State = model.State == "on" ? "0" : "1";
if (files != null && files.Count > 0)
{
var list = await new Common.FileUpload(_host).UploadAsync(files, "brand");
if (list != null && list.Count > 0)
{
model.Images = list.FirstOrDefault();
}
}
int res;
try
{
if (!string.IsNullOrEmpty(model.ID) && !model.ID.ToString().ToLower().Equals("string"))
{
res = _services.Update <InterInvite>(model);
}
else
{
SnowFlakeWork snowFlake = new SnowFlakeWork(1);//雪花ID
model.ID = snowFlake.NextID().ToString();
res = _services.Insert <InterInvite>(model);
}
result.Count = res > 0 ? 1 : 0;
}
catch (Exception ex)
{
result.Code = (int)ResultCode.INTERNAL_SERVER_ERROR;
result.Msg = "内部操作错误，请联系管理员或查看错误日志。";
log.Error($"/{System.Reflection.MethodBase.GetCurrentMethod().Name}方法/错误信息：【{ex.Message}】");
}
return(Ok(result));
}
public override async Task <PageResultDto <ConfigurationMessage> > GetAsync(MessageQueryModel query, CancellationToken cancellationToken = default)
{
var result = new PageResultDto <ConfigurationMessage>();
if (cancellationToken.IsCancellationRequested)
{
return(result);
}
var sqlParams = new List <object>
{
new SqlParameter("@Environment", _source.Environment),
new SqlParameter("@NameSpace", _source.NameSpace)
};
var sqlWhere = new StringBuilder("WHERE [Environment]=@Environment AND [NameSpace]=@NameSpace ");
if (query.Id.HasValue)
{
sqlWhere = sqlWhere.Append("AND [Id]=@Id ");
sqlParams.Add(new SqlParameter("@Id", query.Id));
}
if (!string.IsNullOrEmpty(query.Key))
{
sqlWhere = sqlWhere.Append("AND [Key] LIKE CONCAT('%',@Key,'%') ");
sqlParams.Add(new SqlParameter("@Key", query.Key));
}
var sql = $@"SELECT * FROM {GetTableName()} {sqlWhere} ORDER BY [UpdateTime] DESC offset {query.PageIndex * query.PageSize} rows fetch next {query.PageSize} rows only";
using var connection = new SqlConnection(_source.DbConnection);
var reader = connection.ExecuteQuery(sql, sqlParams.ToArray());
var list = new List <ConfigurationMessage>();
while (reader.Read())
{
list.Add(new ConfigurationMessage
{
Id = Convert.ToInt32(reader["Id"]),
Key = reader["Key"].ToString(),
Value = reader["Value"].ToString(),
Description = reader["Description"].ToString(),
Environment = reader["Environment"].ToString(),
NameSpace = reader["NameSpace"].ToString(),
CreateTime = Convert.ToDateTime(reader["CreateTime"].ToString()),
UpdateTime = Convert.ToDateTime(reader["UpdateTime"].ToString()),
UtcTime = Convert.ToDateTime(reader["UtcTime"].ToString())
});
}
result.Count = await GetCountAsync(cancellationToken);
result.Items = list;
return(await Task.FromResult(result));
}
public PageResultDto<UserDto> Get(int pageSize, int pageIndex)
{
var data = this.FacadeService.GetAll(pageSize, pageIndex);
var result = new PageResultDto<UserDto>
{
CurrentPage = 0,
PageSize = data.Count,
Result = data,
TotalCount = data.Count,
TotalPages = 1
};
return result;
}
/// <summary>
/// 分页结果
/// </summary>
/// <param name="total"></param>
/// <param name="data"></param>
/// <returns></returns>
protected async Task <PageResultDto <TListDto> > GetPageDto(long total, List <TEntity> data)
{
var result = new PageResultDto <TListDto>();
if (data.Any())
{
result.Data = await MapToListDtosAsync(data);
result.Total = total;
}
return(result);
}
public PageResultDto<CompanyDto> Get()
{
var data = this.FacadeService.GetAll();
var paged = new PageResultDto<CompanyDto>
{
CurrentPage = 1,
PageSize = data.Count,
Result = data,
TotalCount = data.Count,
TotalPages = 1
};
return paged;
}
public PageResultDto<CharterDto> Get(CharterType charterType, long companyId, int pageIndex, int pageSize)
{
var res = new PageResultDto<CharterDto>();
if (charterType == CharterType.In)
{
res = this._charterInFacadeService.GetAll(companyId, pageIndex, pageSize);
}
else
{
res = this._charterOutFacadeService.GetAll(companyId, pageIndex, pageSize);
}
return res;
}
public PageResultDto<VoyageDto> Get(long companyId, long vesselId)
{
var data = this.FacadeService.GetByFilter(companyId, vesselId, true, true);
var result = new PageResultDto<VoyageDto>
{
PageSize = data.Count,
CurrentPage = 0,
TotalCount = data.Count,
TotalPages = 1,
Result = data
};
return result;
}
public PageResultDto<VesselDto> Get(CharterType charterType, long companyId)
{
var res = new PageResultDto<VesselDto>();
if (charterType == CharterType.In)
{
res.Result = this._vesselFacadeService.GetOwnedVessels(companyId) as IList<VesselDto>;
}
else
{
// todo bzcomment
res.Result = this._vesselFacadeService.GetOwnedOrCharterInVessels(companyId) as IList<VesselDto>;
}
return res;
}
// GET api/charteritem
public PageResultDto<CharterItemDto> Get(long id,CharterType charterType)
{
var res = new PageResultDto<CharterItemDto>();
if (charterType==CharterType.In)
{
res= this._charterInFacadeService.GetAllItem(id,0,0);
}
else
{
res = this._charterOutFacadeService.GetAllItem(id, 0, 0);
}
return res;
}
public async Task <PageResultDto <UserDto> > GetAllPaging(GetUserPagingRequestDto request)
{
var query = _userManager.Users.Where(x => x.IsDeleted == false).AsQueryable();
if (!string.IsNullOrEmpty(request.UserName))
{
query = query.Where(x => x.UserName.Contains(request.UserName));
}
if (!string.IsNullOrEmpty(request.FullName))
{
query = query.Where(x => x.FullName.Contains(request.FullName));
}
if (!string.IsNullOrEmpty(request.PhoneNumber))
{
query = query.Where(x => x.PhoneNumber.Contains(request.PhoneNumber));
}
if (request.TypeAccount >= 0)
{
query = query.Where(x => x.TypeAccount == (TypeAccount)request.TypeAccount);
}
//3. Paging
int totalRow = await query.CountAsync();
var data = await query.Skip((request.PageIndex - 1) *request.PageSize)
.Take(request.PageSize)
.Select(x => new UserDto()
{
Email = x.Email,
PhoneNumber = x.PhoneNumber,
UserName = x.UserName,
Id = x.Id,
TypeAccount = (int)x.TypeAccount,
FullName = x.FullName,
Dob = x.Dob,
Status = (int)x.Status,
Roles = null
}).ToListAsync();
//4. Select and projection
var pagedResult = new PageResultDto <UserDto>()
{
StatusCode = ResponseCode.Success,
Message = "Thành công",
TotalRecord = totalRow,
Items = data
};
return(pagedResult);
}
public PageResultDto<VesselDto> Get(long companyId, int? pageSize = null, int? pageIndex = null)
{
var dtos = this.FacadeService.GetCompanyVessels(companyId);
var result = new PageResultDto<VesselDto>
{
CurrentPage = 0,
PageSize = 0,
TotalCount = dtos.Count,
Result = dtos,
TotalPages = 0
};
return result;
}
public async Task <PageResultDto <ShrimpCropDto> > FilterShrimpCrop(PageDto pageDto, string searchKey, string farmingLocationId, string shrimpBreedId)
{
_logger.LogInformation("start method filter shrimpcrop");
var query = new StringBuilder();
query.Append("(@SearchKey is null or (bys_shrimp_crop.name LIKE @SearchKey OR bys_shrimp_crop.code LIKE @SearchKey))");
query.Append(" AND (@FarmingLocationId is null or bys_shrimp_crop.farming_location_id LIKE @FarmingLocationId)");
query.Append(" AND (@ShrimpBreedId is null or bys_shrimp_crop.shrimp_breed_id LIKE @ShrimpBreedId)");
var param = new
{
FarmingLocationId = farmingLocationId.FormatSearch(),
ShrimpBreedId = shrimpBreedId.FormatSearch(),
SearchKey = searchKey.FormatSearch()
};
var count = await this.DatabaseConnectService.Connection.CountAsync <ShrimpCrop>(x => x
.Include <FarmingLocation>(join => join.LeftOuterJoin())
.Include <ShrimpBreed>(join => join.LeftOuterJoin())
.Where($"{query}")
.WithParameters(param));
var items = (await this.DatabaseConnectService.Connection.FindAsync <ShrimpCrop>(x => x
.Include <FarmingLocation>(join => join.LeftOuterJoin())
.Include <ShrimpBreed>(join => join.LeftOuterJoin())
.Include <ShrimpCropManagementFactor>(join => join.LeftOuterJoin())
.Where($"{query}")
.WithParameters(param)))
.OrderByDescending(x => x.CreatedAt)
.Skip(pageDto.Page * pageDto.PageSize)
.Take(pageDto.PageSize)
.Select(x => x.ToShrimpCropDto())
.ToArray();
var result = new PageResultDto <ShrimpCropDto>
{
Items = items,
TotalCount = count,
PageIndex = pageDto.Page,
PageSize = pageDto.PageSize,
TotalPages = (int)Math.Ceiling(count / (double)pageDto.PageSize),
};
_logger.LogInformation("end method shrimpcrop");
return(result);
}
public async Task <PageResultDto <DetailUserResultDto> > FilterUser(PageDto pageDto, string searchKey, string groupId)
{
_logger.LogInformation("start method filter user");
var query = new StringBuilder();
query.Append("@SearchKey is null OR (bys_account.username LIKE @SearchKey OR bys_user.fullname LIKE @SearchKey OR bys_user.email LIKE @SearchKey) ");
query.Append("AND (@GroupId is null OR (bys_users_groups.group_id = @GroupId)) ");
query.Append("AND bys_user.status = @Status");
var param = new
{
SearchKey = searchKey.FormatSearch(),
GroupId = groupId,
Status = AccountStatus.Active.ToString()
};
var count = (await this.DatabaseConnectService.Connection.FindAsync <User>(x => x
.Include <Data.Entity.Account.Account>(j => j.InnerJoin())
.Include <UserGroup>(j => j.LeftOuterJoin())
.Where($"{query}")
.WithParameters(param))).Distinct().Count();
var items = (await this.DatabaseConnectService.Connection.FindAsync <User>(x => x
.Include <Data.Entity.Account.Account>(j => j.InnerJoin())
.Include <UserGroup>(j => j.LeftOuterJoin())
.Include <Group>(j => j.LeftOuterJoin())
.Where($"{query}")
.WithParameters(param)))
.OrderByDescending(x => x.CreatedAt)
.Skip(pageDto.Page * pageDto.PageSize)
.Take(pageDto.PageSize)
.Select(x => x.ToDetailUserDto()).ToArray();
var result = new PageResultDto <DetailUserResultDto>
{
Items = items,
TotalCount = count,
PageIndex = pageDto.Page,
PageSize = pageDto.PageSize,
TotalPages = (int)Math.Ceiling(count / (double)pageDto.PageSize),
};
_logger.LogInformation("end method filter user");
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="request"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public async Task <PageResultDto <ArticleCategoryDto> > Handle(ArticleCategoryListQuery request, CancellationToken cancellationToken)
{
var sql = "select * from ArticleCategory";
var entities = await _dapper.QueryAsync <ArticleCategory>(sql);
PageResultDto <ArticleCategoryDto> result = new PageResultDto <ArticleCategoryDto>()
{
State = 1,
Result = new ResultPage <ArticleCategoryDto>()
{
TotalCount = entities.Count(),
Data = Mapper.Map <List <ArticleCategoryDto> >(entities)
}
};
return(result);
}
public async Task <PageResultDto <RouteRoleEntity> > GetAsync(MessageQueryModel query, CancellationToken cancellationToken = default)
{
var result = new PageResultDto <RouteRoleEntity>();
if (cancellationToken.IsCancellationRequested)
{
return(result);
}
var sqlParams = new List <object>
{
new NpgsqlParameter("@IsValid", true)
};
var sqlWhere = new StringBuilder("WHERE IsValid=@IsValid ");
if (!string.IsNullOrEmpty(query.ApiRoute))
{
sqlWhere = sqlWhere.Append("AND ApiRoute LIKE CONCAT('%',@ApiRoute,'%')");
sqlParams.Add(new NpgsqlParameter("@ApiRoute", query.ApiRoute));
}
var sql = $@"SELECT * FROM {GetTableName()} {sqlWhere} ORDER BY UpdateTime DESC LIMIT {query.PageSize} OFFSET {query.PageIndex * query.PageSize}";
await using var connection = new NpgsqlConnection(_options.Value.DbConnection);
var reader = connection.ExecuteQuery(sql, sqlParams.ToArray());
var list = new List <RouteRoleEntity>();
while (reader.Read())
{
list.Add(new RouteRoleEntity
{
Id = Convert.ToInt32(reader["Id"]),
Roles = reader["Roles"].ToString().ToObject <List <string> >(),
ApiName = reader["ApiName"].ToString(),
ApiRoute = reader["ApiRoute"].ToString(),
IsValid = Convert.ToBoolean(reader["IsValid"].ToString()),
CreateTime = Convert.ToDateTime(reader["CreateTime"].ToString()),
UpdateTime = Convert.ToDateTime(reader["UpdateTime"].ToString()),
});
}
result.Count = await GetCountAsync(query, cancellationToken);
result.Items = list;
return(await Task.FromResult(result));
}
public IActionResult Query(string id)
{
var result = new PageResultDto();
try
{
result.Code = (int)ResultCode.SUCCESS;
result.Data = _services.QueryFirst <InterInvite>(id);
result.Count = 1;
}
catch (Exception ex)
{
result.Code = (int)ResultCode.INTERNAL_SERVER_ERROR;
result.Msg = "内部操作错误，请联系管理员或查看错误日志。";
log.Error($"/{System.Reflection.MethodBase.GetCurrentMethod().Name}方法/错误信息：【{ex.Message}】");
}
return(Ok(result));
}
public IActionResult Update(InterInvite model)
{
var result = new PageResultDto();
try
{
var i = _services.Update <InterInvite>(model);
result.Code = i == 0 ? (int)ResultCode.SUCCESS : (int)ResultCode.INTERNAL_SERVER_ERROR;
result.Count = 1;
result.Data = model;
}
catch (Exception ex)
{
result.Code = (int)ResultCode.INTERNAL_SERVER_ERROR;
result.Msg = "内部操作错误，请联系管理员或查看错误日志。";
log.Error($"/{System.Reflection.MethodBase.GetCurrentMethod().Name}方法/错误信息：【{ex.Message}】");
}
return(Ok(result));
}
public PageResultDto <TDestination> Convert(PagedList <TSource> source,
PageResultDto <TDestination> destination,
ResolutionContext context)
{
var itemMapping = context.Mapper.Map <IEnumerable <TDestination> >(source.Items);
var pageResultDto = new PageResultDto <TDestination>
{
TotalItems = source.TotalItems,
Items = itemMapping.ToList(),
HasNext = source.HasNext,
HasPrevious = source.HasPrevious,
PageNumber = source.PageNumber,
PageSize = source.PageSize,
TotalPages = source.TotalPages
};
return(pageResultDto);
}
public async Task <PageResultDto <ManagementFactorDto> > FilterManagementFactor(PageDto pageDto, string searchKey, string dataType, string factorGroup)
{
var query = new StringBuilder();
query.Append("(@DataType is null OR bys_management_factor.data_type LIKE @DataType)");
query.Append(" AND (@FactorGroup is null OR bys_management_factor.factor_group LIKE @FactorGroup)");
query.Append(" AND (@SearchKey is null or (bys_management_factor.name LIKE @SearchKey OR bys_management_factor.description LIKE @SearchKey))");
query.Append(" AND bys_management_factor.status = @Status");
var param = new
{
DataType = dataType.FormatSearch(),
FactorGroup = factorGroup.FormatSearch(),
SearchKey = searchKey.FormatSearch(),
Status = EntityStatus.Alive.ToString()
};
var factorGroupFromDb = await GetMasterData(CommonConstants.FACTOR_GROUP);
var count = await this.DatabaseConnectService.Connection.CountAsync <ManagementFactor>(x => x
.Include <MeasureUnit>(join => join.LeftOuterJoin())
.Where($"{query}")
.WithParameters(param));
var items = (await this.DatabaseConnectService.Connection.FindAsync <ManagementFactor>(x => x
.Include <MeasureUnit>(join => join.LeftOuterJoin())
.Where($"{query}")
.WithParameters(param)))
.Skip(pageDto.Page * pageDto.PageSize)
.Take(pageDto.PageSize)
.Select(x => x.ToManagementFactorDto(factorGroupFromDb.Childs.Where(y => y.Code == x.FactorGroup).FirstOrDefault())).ToArray();
var result = new PageResultDto <ManagementFactorDto>
{
Items = items,
TotalCount = count,
PageIndex = pageDto.Page,
PageSize = pageDto.PageSize,
TotalPages = (int)Math.Ceiling(count / (double)pageDto.PageSize),
};
return(result);
}
public async Task <PageResultDto <DriverDto> > GetAllPagingAsync(GetDriverPagingRequestDto request)
{
var query = _context.Drivers.Where(x => x.IsDeleted == false).AsQueryable();
// Filter
if (!string.IsNullOrEmpty(request.Name))
{
query = query.Where(x => x.Name.Contains(request.Name));
}
if (!string.IsNullOrEmpty(request.PhoneNumber))
{
query = query.Where(x => x.PhoneNumber.Contains(request.PhoneNumber));
}
if (request.Status >= 0)
{
query = query.Where(x => (int)x.Status == request.Status);
}
// Paging
var totalRow = await query.CountAsync();
var data = await query.Skip((request.PageIndex - 1) *request.PageSize)
.Take(request.PageSize)
.Select(x => new DriverDto()
{
Id = x.Id,
Name = x.Name,
Address = x.Address,
Dob = x.Dob,
Email = x.Email,
PhoneNumber = x.PhoneNumber,
Status = (int)x.Status
}).ToListAsync();
var pageResult = new PageResultDto <DriverDto>()
{
StatusCode = ResponseCode.Success,
TotalRecord = totalRow,
Message = "Thực hiện thành công",
Items = data
};
return(pageResult);
}
public async Task <PageResultDto <RouteDto> > GetAllPagingAsync(GetRoutePagingRequestDto request)
{
var query = _context.Routes.Where(x => x.IsDeleted == false).AsQueryable();
// Filter
if (!string.IsNullOrEmpty(request.Name))
{
query = query.Where(x => x.Name.Contains(request.Name));
}
if (!string.IsNullOrEmpty(request.RouteCode))
{
query = query.Where(x => x.RouteCode.Contains(request.RouteCode));
}
if (request.Status >= 0)
{
query = query.Where(x => (int)x.Status == request.Status);
}
// Paging
int totalRow = await query.CountAsync();
var data = await query.Skip((request.PageIndex - 1) *request.PageSize)
.Take(request.PageSize)
.Select(x => new RouteDto()
{
Id = x.Id,
Name = x.Name,
Distance = x.Distance,
RouteCode = x.RouteCode,
Description = x.Desctiption,
Status = (int)x.Status
}).ToListAsync();
// Return
var pageResult = new PageResultDto <RouteDto>()
{
TotalRecord = totalRow,
Message = "Thực hiện thành công",
Items = data
};
return(pageResult);
}
public List<UserDto> GetAll(int pageSize, int pageIndex)
{
var fetch = new ListFetchStrategy<FuelUser>().WithPaging(pageSize, pageIndex );
_userRepository.GetAll(fetch);
var finalResult = new PageResultDto<UserDto>
{
CurrentPage = pageIndex,
PageSize = pageSize,
Result = _mapper.MapToModel(fetch.PageCriteria.PageResult.Result).ToList(),
TotalCount = fetch.PageCriteria.PageResult.TotalCount,
TotalPages = fetch.PageCriteria.PageResult.TotalPages
};
foreach (var user in finalResult.Result)
user.Code = user.FirstName;
return finalResult.Result.ToList();
}
/// <summary>
///
/// </summary>
/// <param name="request"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public async Task <PageResultDto <MessageOutputDto> > Handle(MessageSkipPageQuery request, CancellationToken cancellationToken)
{
var sqlBuilder = new StringBuilder(@"select SQL_CALC_FOUND_ROWS Id,UserName,ImageUrl,Message,CreateTime from Message order by CreateTime Desc limit @skip,@take ;");
sqlBuilder.Append("SELECT FOUND_ROWS() as Total;");
var messages = await _dapper.QueryPage <MessageOutputDto>(sqlBuilder.ToString(), new { skip = request.Skip, take = request.Size });
PageResultDto <MessageOutputDto> result = new PageResultDto <MessageOutputDto>
{
State = 1,
Result = new ResultPage <MessageOutputDto>()
{
TotalCount = (int)messages.TotalCount,
Data = messages.Data
}
};
return(result);
}
public IActionResult Query(string id)
{
var result = new PageResultDto();
try
{
result.Code = (int)ResultCode.SUCCESS;
result.Data = _services.QueryFirst <RetailBrand>(id);
//直接用dapper执行
//_dapper.QueryFirst<RetailBrand>("select * from retail_brand where id=@id",new { id });
result.Count = 1;
}
catch (Exception ex)
{
result.Code = (int)ResultCode.INTERNAL_SERVER_ERROR;
result.Msg = "内部操作错误，请联系管理员或查看错误日志。";
log.Error($"/{System.Reflection.MethodBase.GetCurrentMethod().Name}方法/错误信息：【{ex.Message}】");
}
return(Ok(result));
}
public PageResultDto<CharterItemDto> GetAllItem(long charterId, int pageIndex, int pageSize)
{
var res = new PageResultDto<CharterItemDto>();// { Result = _charterInToDtoMapper.MapToDtoModel(_charterInRepository.GetCharterStartById(charterId)); };
return res;
}
public PageResultDto<InvoiceDto> GetByFilter(int companyId, DateTime fromDate, DateTime toDate, string invoiceNumber, int pageSize, int pageIndex, bool submitedState)
{
var toDateParam = toDate.Date.AddDays(1);
var fetch = new ListFetchStrategy<Invoice>()
.Include(o => o.InvoiceItems)
.Include(o => o.Supplier)
.Include(o => o.Transporter)
//.Include(o => o.Owner)
// .Include(o => o.InvoiceRefrence)
// .Include(o => o.OrderRefrences)
.Include(o => o.ApproveWorkFlows)
.Include(o => o.ApproveWorkFlows.Last().CurrentWorkflowStep)
.Include(o => o.ApproveWorkFlows.Last().CurrentWorkflowStep.ActorUser)
// .Include(c => c.OrderRefrences.Select(d => d.OrderItems))
.Include(c => c.InvoiceItems.Select(d => d.Good))
// .Include(c => c.AdditionalPrices)
// .Include(c => c.AdditionalPrices.Select(d=>d.EffectiveFactor))
.Include(c => c.InvoiceItems.Select(d => d.Good.GoodUnits))
.WithPaging(pageSize, pageIndex + 1);
// var invoiceType = _invoiceDtoMapper.MapInvoiceTypeDtoToInvoiceTypeEntity(invoiceTypeDto);
invoiceRepository.Find(o =>
(string.IsNullOrEmpty(invoiceNumber) || o.InvoiceNumber.Contains(invoiceNumber)) &&
(!submitedState || o.State == States.Submitted) &&
(companyId == -1 || o.OwnerId == companyId)
&& (fromDate == DateTime.MinValue || o.InvoiceDate >= fromDate.Date)
&& (toDate == DateTime.MinValue || o.InvoiceDate <= toDateParam)
, fetch);
// _invoiceRepository.Find(o => o.InvoiceType == invoiceType, fetch);
var invoicesPageResult = fetch.PageCriteria.PageResult;
// var d = _invoiceRepository.GetAll();
// var invoiceDtos = _invoiceDtoMapper.MapToModel(d);
var result = new PageResultDto<InvoiceDto>
{
CurrentPage = invoicesPageResult.CurrentPage,
PageSize = invoicesPageResult.PageSize,
Result = invoiceDtoMapper.MapToModelWithAllIncludes(invoicesPageResult.Result).ToList(),
TotalCount = invoicesPageResult.TotalCount,
TotalPages = invoicesPageResult.TotalPages,
};
return result;
}
//================================================================================
public PageResultDto<FuelReportDto> GetAll(int pageSize, int pageIndex, bool includeReferencesLookup)
{
List<FuelReport> data = fuelReportDomainService.GetAll();
List<FuelReportDto> mapped = null;
if (includeReferencesLookup)
mapped = fuelReportMapper.MapToModel(data, populateFuelReportDetailDtoReferencesLookup).ToList();
else
mapped = fuelReportMapper.MapToModel(data).ToList();
var result = new PageResultDto<FuelReportDto>
{
CurrentPage = 1,
PageSize = 1,
Result = mapped,
TotalCount = 1,
TotalPages = 1
};
return result;
}
//================================================================================
public PageResultDto<FuelReportDto> GetByFilter(long companyId, long vesselInCompanyId, int pageSize, int pageIndex, bool includeReferencesLookup)
{
PageResult<FuelReport> data = fuelReportDomainService.GetByFilter(companyId, vesselInCompanyId, pageSize, pageIndex);
List<FuelReportDto> mapped = null;
if (includeReferencesLookup)
mapped = fuelReportMapper.MapToModel(data.Result, populateFuelReportDetailDtoReferencesLookup).ToList();
else
mapped = fuelReportMapper.MapToModel(data.Result).ToList();
var result = new PageResultDto<FuelReportDto>
{
CurrentPage = data.CurrentPage,
PageSize = data.PageSize,
Result = mapped,
TotalCount = data.TotalCount,
TotalPages = data.TotalPages
};
return result;
}
//================================================================================
//private PageResultDto<FuelReportInventoryOperationDto> mapPageResult(PageResult<InventoryOperation> pageResult, GoodDto good)
//{
// var result = new PageResultDto<FuelReportInventoryOperationDto>()
// {
// CurrentPage = pageResult.CurrentPage,
// PageSize = pageResult.PageSize,
// TotalCount = pageResult.TotalCount,
// TotalPages = pageResult.TotalPages,
// Result = inventoryOperationDtoMapper.MapToModel(pageResult.Result).ToList()
// };
// result.Result.ToList().ForEach(invDto => invDto.Good = good);
// return result;
//}
//================================================================================
private PageResultDto<FuelReportInventoryOperationDto> mapPageResult(PageResult<InventoryOperation> pageResult, ScrapDto scrap)
{
var invOperationDtos = new List<FuelReportInventoryOperationDto>();
foreach (var invOpr in pageResult.Result)
{
foreach (var detailDto in scrap.ScrapDetails)
{
var invOprDto = inventoryOperationDtoMapper.MapToModel(invOpr);
invOprDto.Good = detailDto.Good;
invOperationDtos.Add(invOprDto);
}
}
var result = new PageResultDto<FuelReportInventoryOperationDto>()
{
CurrentPage = pageResult.CurrentPage,
PageSize = pageResult.PageSize,
TotalCount = pageResult.TotalCount * scrap.ScrapDetails.Count,
Result = invOperationDtos
};
return result;
}
// todo: input parameters must be converted to IPageCritera
public PageResultDto<OrderDto> GetAll(int pageSize, int pageIndex)
{
var fetch = new ListFetchStrategy<Order>().WithPaging(pageSize, pageIndex);
_orderRepository.GetAll(fetch);
var finalResult = new PageResultDto<OrderDto>
{
CurrentPage = pageIndex,
PageSize = pageSize,
Result = _orderDtoMapper.MapToModel(fetch.PageCriteria.PageResult.Result.ToList()).ToList(),
TotalCount = fetch.PageCriteria.PageResult.TotalCount,
TotalPages = fetch.PageCriteria.PageResult.TotalPages
};
return finalResult;
}
public PageResultDto<OrderDto> GetByFilter(int companyId, int orderCreatorId, string orderTypesString, DateTime fromDate, DateTime toDate, int pageSize, int pageIndex, long? supplierId, long? transporterId, bool includeOrderItem, string orderIdListString, string orderCode, bool submitedState)
{
_unitOfWorkScope.LazyLoadingEnabled = false;
_unitOfWorkScope.ProxyCreationEnabled = false;
var toDateParam = toDate.Date.AddDays(1);
var fetch =
new ListFetchStrategy<Order>().Include(o => o.FromVesselInCompany).Include(o => o.ToVesselInCompany).Include(o => o.Supplier).Include(o => o.Receiver).
Include(o => o.Transporter).Include(o => o.Owner).Include(o => o.ApproveWorkFlows).Include
(o => o.ApproveWorkFlows.Last().CurrentWorkflowStep).Include(o => o.ApproveWorkFlows.Last().CurrentWorkflowStep.ActorUser).
WithPaging(pageSize, pageIndex + 1);
if (includeOrderItem)
fetch.Include(c => c.OrderItems);
var orderTypes = new List<OrderTypes>();
if (!string.IsNullOrEmpty(orderTypesString))
{
var orderTypeDtos = orderTypesString.Split(',');
orderTypes.AddRange(orderTypeDtos.Where(c => c != "0").Select(item => (OrderTypes) int.Parse(item)));
}
var orderlist = new List<long>();
if (!string.IsNullOrEmpty(orderIdListString))
{
var orderArray = orderIdListString.Split(',');
orderlist.AddRange(orderArray.Where(c => c != "0").Select(long.Parse));
_orderRepository.Find(o => orderlist.Count == 0 || orderlist.Contains(o.Id), fetch);
}
else
{
_orderRepository.Find
(
o =>
(orderTypes.Count == 0 || orderTypes.Contains(o.OrderType))
&& (companyId == -1 || o.OwnerId == companyId)
&& (!submitedState || o.State == States.Submitted)
&& (fromDate == DateTime.MinValue || o.OrderDate >= fromDate.Date)
&& (toDate == DateTime.MinValue || o.OrderDate <= toDateParam)
&& (supplierId == 0 || supplierId == null || o.SupplierId == supplierId)
&& (transporterId == 0 || transporterId == null || o.TransporterId == transporterId)
&& (string.IsNullOrEmpty(orderCode) || o.Code == orderCode)
, fetch);
}
var ordersPageResult = fetch.PageCriteria.PageResult;
var result = new PageResultDto<OrderDto>
{
CurrentPage = ordersPageResult.CurrentPage,
PageSize = ordersPageResult.PageSize,
Result = _orderDtoMapper.MapToModelWithAllIncludes(ordersPageResult.Result).ToList(),
TotalCount = ordersPageResult.TotalCount,
TotalPages = ordersPageResult.TotalPages,
};
return result;
}
