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); }