public <R> GridResponse<R> getGridResponse(String query, String sidx, String sord, Integer page, int perPage, R recordExample) { return getGridResponse(query, sidx, sord, page, perPage, recordExample, new HashMap<>()); }
public <R> GridResponse<R> getGridResponse(String query, String sidx, String sord, Integer page, int perPage, R recordExample, Map<String, Object> parameters) { sidx = sidx != null ? sidx.toLowerCase() : ""; sord = sord != null ? sord.toLowerCase() : ""; Preconditions.checkState(Arrays.asList("asc", "desc", "").contains(sord)); Preconditions.checkState(Arrays.asList(recordExample.getClass().getDeclaredFields()).stream().map(Field::getName).map(String::toLowerCase).collect(Collectors.toList()).contains(sidx)); query += addQueryWhereForObject(recordExample); parameters.putAll(getParametersForObject(recordExample)); String queryCount = String.format(query, "COUNT(*)", ""); String queryRecords = String.format(query, "*", "ORDER BY " + sidx + " " + sord) + String.format(" LIMIT %d OFFSET %d", perPage, perPage * (page - 1)); Integer countRecords = jdbcTemplate.queryForObject(queryCount, parameters, Long.class).intValue(); List<R> records = jdbcTemplate.query(queryRecords, parameters, new BeanPropertyRowMapper(recordExample.getClass())); return new GridResponse<>(page, Double.valueOf(Math.ceil((1.0 * countRecords) / perPage)).intValue(), countRecords, records); }
public GridResponse<DocumentPositionDTO> findAll(final Long documentId, final String _sidx, final String _sord, int page, int perPage, final DocumentPositionDTO position) { String query = "SELECT %s FROM ( SELECT p.*, p.document_id AS document, product.number AS product, product.name AS productName, product.unit, additionalcode.code AS additionalcode, " + "palletnumber.number AS palletnumber, location.number AS storagelocation, resource.number AS resource, \n" + "(coalesce(r1.resourcesCount,0) < 2 AND p.quantity >= coalesce(resource.quantity,0)) AS lastResource " + " FROM materialflowresources_position p\n" + " LEFT JOIN basic_product product ON (p.product_id = product.id)\n" + " LEFT JOIN basic_additionalcode additionalcode ON (p.additionalcode_id = additionalcode.id)\n" + " LEFT JOIN basic_palletnumber palletnumber ON (p.palletnumber_id = palletnumber.id)\n" + " LEFT JOIN materialflowresources_resource resource ON (p.resource_id = resource.id)\n" + " LEFT JOIN (SELECT palletnumber_id, count(id) as resourcesCount FROM materialflowresources_resource GROUP BY palletnumber_id) r1 ON r1.palletnumber_id = resource.palletnumber_id \n" + " LEFT JOIN materialflowresources_storagelocation location ON (p.storagelocation_id = location.id) WHERE p.document_id = :documentId %s) q "; Map<String, Object> parameters = Maps.newHashMap(); parameters.put("documentId", documentId); return lookupUtils.getGridResponse(query, _sidx, _sord, page, perPage, position, parameters); }
@ResponseBody @RequestMapping(value = "records", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE) public GridResponse<R> getRecords(@RequestParam String sidx, @RequestParam String sord, @RequestParam(defaultValue = "1", required = false, value = "page") Integer page, @RequestParam(value = "rows") int perPage, @RequestParam(defaultValue = "0", required = false, value = "context") Long context, R record) { String query = getQueryForRecords(context); return lookupUtils.getGridResponse(query, sidx, sord, page, perPage, record, getQueryParameters(context, record)); }
public GridResponse<ActionForPlannedEventDTO> findAll(final Long plannedEventId, final String _sidx, final String _sord, int page, int perPage, ActionForPlannedEventDTO actionForPlannedEventDto) { String query = "SELECT %s FROM ( SELECT afpe.id AS id, a.name AS action, afpe.plannedevent_id AS plannedEvent, " + "s.name || ' ' || s.surname || ' - ' || s.number AS responsibleWorker, " + "afpe.description AS description, afpe.state AS state, afpe.reason AS reason " + "FROM cmmsmachineparts_actionforplannedevent afpe JOIN cmmsmachineparts_action a ON a.id = afpe.action_id " + "LEFT JOIN basic_staff s ON s.id = afpe.responsibleworker_id " + "WHERE afpe.plannedevent_id = :plannedEventId %s) q "; Map<String, Object> parameters = new HashMap<>(); parameters.put("plannedEventId", plannedEventId); GridResponse<ActionForPlannedEventDTO> gridResponse = lookupUtils.getGridResponse(query, _sidx, _sord, page, perPage, actionForPlannedEventDto, parameters); translateActionStates(gridResponse); return gridResponse; }
private GridResponse<ResourceDTO> getResponse(String sidx, String sord, Integer page, int perPage, ResourceDTO record, Long context) { String additionalCode = record.getAc(); boolean useAdditionalCode = org.apache.commons.lang3.StringUtils.isNotEmpty(additionalCode); Map<String, Object> parameters = geParameters(context, record, useAdditionalCode, additionalCode); boolean properFilter = prepareWasteFilter(record); if ("wasteString".equals(sidx)) { sidx = "waste"; } boolean properFilterLastResource = prepareLastResourceFilter(record); if ("lastResourceString".equals(sidx)) { sidx = "lastResource"; } String query = getQuery(context, useAdditionalCode, !properFilter, !properFilterLastResource); GridResponse<ResourceDTO> response = lookupUtils.getGridResponse(query, sidx, sord, page, perPage, record, parameters); if (response.getRows().isEmpty() && useAdditionalCode) { parameters = geParameters(context, record, false, additionalCode); query = getQuery(context, false, !properFilter, !properFilterLastResource); response = lookupUtils.getGridResponse(query, sidx, sord, page, perPage, record, parameters); } setTranslatedWasteFlag(response); setTranslatedLastResourceFlag(response); return response; }