public async Task <BaseResponse <PageResultDto <WorkDto> > > FilterWork([FromRoute] PageDto pageDto, [FromQuery] FilterParamWorkDto dto)
{
var response = new BaseResponse <PageResultDto <WorkDto> >
{
Data = await _workService.FilterWork(pageDto, dto),
Status = true
};
return(await Task.FromResult(response));
}
public async Task <PageResultDto <WorkDto> > FilterWork(PageDto pageDto, FilterParamWorkDto filterParamWorkDto)
{
_logger.LogInformation("start method filter work");
if (filterParamWorkDto.FromDate > filterParamWorkDto.ToDate)
{
throw new BusinessException("Invalid parameter!", ErrorCode.INVALID_PARAMETER);
}
var query = new StringBuilder();
query.Append("SELECT a.id as Id, a.name as Name, a.execution_time as ExecutionTime, a.value as Value, a.modified_at as ModifiedAt, ");
query.Append(" b.id as FarmingLocationId, b.name as FarmingLocationName, c.code as FarmingLocationCode, ");
query.Append(" c.id as ShrimpBreedId, c.name as ShrimpBreedName, c.code as ShrimpBreedCode, c.description as ShrimpBreedDescription, c.attachment as ShrimpBreedAttachment, ");
query.Append(" d.id as UserId, d.fullname as FullName, d.phone as Phone, d.email as Email, d.address as Address, ");
query.Append(" f.id as ShrimpCropId, f.code as ShrimpCropCode, f.name as ShrimpCropName, f.from_date as ShrimpCropFromDate, f.to_date as ShrimpCropToDate, ");
query.Append(" g.id as ManagementFactorId, g.name as ManagementFactorName, g.code as ManagementFactorCode, g.sample_value as SampleValue, g.description as Description, ");
query.Append(" h.id as MeasureUnitId, h.name as MeasureUnitName, h.description as MeasureUnitDescription, ");
query.Append(" CAST(STUFF((SELECT ', '+ convert(nvarchar(50), file_id) FROM bys_main.bys_work_picture WHERE work_id = a.id FOR XML PATH ('')), 1, 2, '') AS nvarchar(150)) AS Pictures ");
query.Append("FROM bys_main.bys_work a ");
query.Append(" LEFT JOIN bys_main.bys_farming_location b on b.id = a.farming_location_id ");
query.Append(" LEFT JOIN bys_main.bys_shrimp_breed c on c.id = a.shrimp_breed_id ");
query.Append(" LEFT JOIN bys_sc_account.bys_user d on d.id = a.curator ");
query.Append(" LEFT JOIN bys_main.bys_shrimp_crop_management_factor e on e.id = a.shrimp_crop_management_factor_id ");
query.Append(" LEFT JOIN bys_main.bys_shrimp_crop f on f.id = e.shrimp_crop_id ");
query.Append(" LEFT JOIN bys_main.bys_management_factor g on g.id = e.management_factor_id ");
query.Append(" LEFT JOIN bys_sc_common.bys_measure_unit h on h.id = g.unit_id ");
query.Append("WHERE (@FromDate is null OR (a.execution_time >= @FromDate)) ");
query.Append(" AND (@ToDate is null OR (a.execution_time <= @Todate)) ");
query.Append(" AND (@FarmingLocationId is null OR (a.farming_location_id = @FarmingLocationId AND (@ShrimpCropId is null OR (f.id = @ShrimpCropId)))) ");
query.Append(" AND (@FactorGroup is null OR (g.factor_group = @Factorgroup)) ");
query.Append(" AND (@Curator is null OR (a.curator = @Curator)) ");
query.Append(" AND a.status != @StatusWork ");
if (filterParamWorkDto.Status == WorkStatus.LateDeadline.ToString())
{
query.Append("AND (a.execution_time < @Now AND a.value is null) ");
}
if (filterParamWorkDto.Status == WorkStatus.Completed.ToString())
{
query.Append("AND a.value is not null ");
}
query.Append("ORDER BY a.execution_time ");
var param = new
{
Page = pageDto.Page,
pageSize = pageDto.PageSize,
FromDate = filterParamWorkDto.FromDate.FromUnixTimeStamp(),
ToDate = filterParamWorkDto.ToDate.FromUnixTimeStamp(),
FarmingLocationId = filterParamWorkDto.FarmingLocationId,
ShrimpCropId = filterParamWorkDto.ShrimpCropId,
FactorGroup = filterParamWorkDto.FactorGroup,
Curator = filterParamWorkDto.Curator,
StatusWork = StatusWork.Delete.ToString(),
Now = DateTime.UtcNow
};
var count = (await this.DatabaseConnectService.SelectAsync <WorkResultDto>(query.ToString(), param)).Count;
query.Append("OFFSET @Page * @PageSize ROWS FETCH NEXT @pageSize ROWS ONLY; ");
var items = (await this.DatabaseConnectService.SelectAsync <WorkResultDto>(query.ToString(), param))
.Select(x => x.ToWorkDto()).ToArray();
var result = new PageResultDto <WorkDto>
{
Items = items,
TotalCount = count,
PageIndex = pageDto.Page,
PageSize = pageDto.PageSize,
TotalPages = (int)Math.Ceiling(count / (double)pageDto.PageSize),
};
_logger.LogInformation("end method filter work");
return(result);
}
