Java tutorial
/* * Copyright (C) 2016 FormKiQ Inc. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package com.formkiq.core.dao; import static com.formkiq.core.domain.type.ReportColumnOperation.SUM; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.UUID; import java.util.function.Predicate; import java.util.stream.Collectors; import javax.persistence.Query; import org.hibernate.SQLQuery; import org.hibernate.Session; import org.hibernate.transform.AliasToBeanResultTransformer; import org.hibernate.type.PostgresUUIDType; import org.hibernate.type.StringType; import org.hibernate.type.TimestampType; import org.springframework.stereotype.Repository; import org.springframework.util.StringUtils; import com.formkiq.core.domain.FolderFormReport; import com.formkiq.core.domain.type.ReportColumnInput; import com.formkiq.core.domain.type.ReportDTO; import com.formkiq.core.domain.type.ReportFilter; import com.formkiq.core.domain.type.ReportInput; import com.formkiq.core.domain.type.ReportListDTO; /** * UserDao hibernate implemenation. * */ @Repository public class ReportDaoImpl extends AbstractDaoImpl implements ReportDao { /** * Add Scalars. * @param q {@link SQLQuery} * @param input {@link ReportInput} */ private void addScalars(final SQLQuery q, final ReportInput input) { for (ReportColumnInput c : input.getColumns()) { q.addScalar(c.getTitle(), StringType.INSTANCE); } } /** * Appends the FROM SQL from statement. * @param sb {@link StringBuilder} * @param input {@link ReportInput} */ private void appendFromSQL(final StringBuilder sb, final ReportInput input) { sb.append(" from folder_forms f, " + "jsonb_array_elements(f.data->'sections') AS sections"); sb.append(input.getColumns().stream().map(i -> "jsonb_array_elements(sections->'fields') AS f" + i.getId()) .collect(Collectors.joining(", ", ", ", ""))); } /** * Appends the GROUP BY SQL from statement. * @param sb {@link StringBuilder} * @param input {@link ReportInput} */ private void appendGroupBySQL(final StringBuilder sb, final ReportInput input) { Predicate<? super ReportColumnInput> opPredicate = e -> StringUtils.hasText(e.getOperation()); boolean groupByNeeded = input.getColumns().stream().anyMatch(opPredicate); if (groupByNeeded) { sb.append(" group by "); List<ReportColumnInput> list = input.getColumns().stream().filter(opPredicate.negate()) .collect(Collectors.toList()); sb.append(list.stream().map(i -> "\"" + i.getTitle() + "\"").collect(Collectors.joining(", "))); } } /** * Appends Select fields statement. * @param sb {@link StringBuilder} * @param input {@link ReportInput} */ private void appendSelectSQL(final StringBuilder sb, final ReportInput input) { sb.append("select "); sb.append(input.getColumns().stream() .map(i -> SUM.name().equalsIgnoreCase(i.getOperation()) ? "sum(cast(f" + i.getId() + "->>'value' as money)) as \"" + i.getTitle() + "\"" : "f" + i.getId() + "->>'value' as \"" + i.getTitle() + "\"") .collect(Collectors.joining(", "))); } /** * Appends the WHERE Date SQL from statement. * @param sb {@link StringBuilder} * @param input {@link ReportInput} */ private void appendWhereDateSQL(final StringBuilder sb, final ReportInput input) { ReportFilter filter = input.getFilter(); if (filter != null) { if (filter.getStartdate() != null) { sb.append(" and f.inserted_date >= :start"); } if (filter.getEnddate() != null) { sb.append(" and f.inserted_date <= :end"); } } } /** * Appends the WHERE SQL from statement. * @param sb {@link StringBuilder} * @param input {@link ReportInput} */ private void appendWhereSQL(final StringBuilder sb, final ReportInput input) { sb.append(" where f.parent_uuid is not null "); sb.append(" and f.data ->> 'source_form_uuid'=:s"); sb.append(input.getColumns().stream().map(i -> "f" + i.getId() + "->>'id'=:i" + i.getId()) .collect(Collectors.joining(" and ", " and ", ""))); } /** * Create SQL Parameter Map. * @param input {@link ReportInput} * @return {@link Map} */ private Map<Object, Object> createParameterMap(final ReportInput input) { Map<Object, Object> map = new HashMap<>(); map.put("s", input.getFormuuid()); for (ReportColumnInput c : input.getColumns()) { map.put("i" + c.getId(), c.getId()); } ReportFilter filter = input.getFilter(); if (filter != null) { if (filter.getStartdate() != null) { map.put("start", filter.getStartdate()); } if (filter.getEnddate() != null) { map.put("end", filter.getEnddate()); } } return map; } @SuppressWarnings({ "unchecked", "resource" }) @Override public ReportListDTO getReports(final String folderid) { String sql = "select data #>> '{name}' as name, " + "data #>> '{formuuid}' as formuuid, " + "updated_date as updateddate, " + "inserted_date as inserteddate, " + "report_uuid as uuid, " + "sha1_hash as sha1hash " + "from folder_form_reports " + "where folder_id=:folder"; Session session = getEntityManager().unwrap(Session.class); List<ReportDTO> list = session.createSQLQuery(sql.toString()).addScalar("name", StringType.INSTANCE) .addScalar("formuuid", StringType.INSTANCE).addScalar("uuid", StringType.INSTANCE) .addScalar("sha1hash", StringType.INSTANCE).addScalar("updateddate", TimestampType.INSTANCE) .addScalar("inserteddate", TimestampType.INSTANCE) .setParameter("folder", UUID.fromString(folderid), PostgresUUIDType.INSTANCE) .setResultTransformer(new AliasToBeanResultTransformer(ReportDTO.class)).list(); ReportListDTO dto = new ReportListDTO(); dto.setReports(list); return dto; } @Override public FolderFormReport getReports(final String folderid, final String reportuuid) { String jql = "select r from FolderFormReport r " + "where r.folderid=:folder and r.reportUUID=:uuid"; Query query = getEntityManager().createQuery(jql).setParameter("folder", UUID.fromString(folderid)) .setParameter("uuid", UUID.fromString(reportuuid)); return (FolderFormReport) getSingleResult(query); } @SuppressWarnings({ "unchecked", "resource" }) @Override public List<Map<String, Object>> runReport(final ReportInput input) { StringBuilder sb = new StringBuilder(); appendSelectSQL(sb, input); appendFromSQL(sb, input); appendWhereSQL(sb, input); appendWhereDateSQL(sb, input); appendGroupBySQL(sb, input); Session session = getEntityManager().unwrap(Session.class); SQLQuery q = session.createSQLQuery(sb.toString()); addScalars(q, input); Map<Object, Object> map = createParameterMap(input); q.setProperties(map); List<Object[]> list = q.list(); return translateToMap(input.getColumns(), list); } @Override public FolderFormReport saveReport(final FolderFormReport report) { if (StringUtils.isEmpty(report.getFolderformreportid())) { report.setFolderformreportid(UUID.randomUUID()); getEntityManager().persist(report); } else { getEntityManager().merge(report); } return report; } /** * Translates a Results to a Map. * @param columns {@link List} * @param results {@link List} * @return {@link List} */ private List<Map<String, Object>> translateToMap(final List<ReportColumnInput> columns, final List<Object[]> results) { List<Map<String, Object>> list = new ArrayList<>(); for (Object[] objs : results) { Map<String, Object> map = new HashMap<>(); list.add(map); for (int i = 0; i < objs.length; i++) { map.put(columns.get(i).getTitle(), objs[i]); } } return list; } }