Example usage for org.hibernate SQLQuery setResultTransformer

Introduction

In this page you can find the example usage for org.hibernate SQLQuery setResultTransformer.

Prototype

@Deprecated
Query<R> setResultTransformer(ResultTransformer transformer);

Source Link

Document

Set a strategy for handling the query results.

Usage

From source file:org.generationcp.middleware.dao.dms.ExperimentDao.java

License:Open Source License

private List<Map<String, Object>> getObservationUnitsQueryResult(final int datasetId,
        final List<MeasurementVariableDto> selectionMethodsAndTraits, final List<String> observationUnitIds) {

    try {/*from   w  w  w  .ja  va  2  s .  c om*/
        final String observationUnitTableQuery = this.getObservationUnitsQuery(selectionMethodsAndTraits);
        final SQLQuery query = this.createQueryAndAddScalar(selectionMethodsAndTraits,
                observationUnitTableQuery);
        query.setParameter("datasetId", datasetId);
        query.setParameterList("observationUnitIds", observationUnitIds);

        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        final List<Map<String, Object>> results = query.list();
        return results;

    } catch (final Exception e) {
        final String error = "An internal error has ocurred when trying to execute the operation";
        ExperimentDao.LOG.error(error);
        throw new MiddlewareException(error);
    }
}

---

From source file:org.generationcp.middleware.dao.dms.ExperimentDao.java

License:Open Source License

public Map<Integer, Map<String, List<Object>>> getValuesFromObservations(final int studyId,
        final List<Integer> datasetTypeIds, final Map<Integer, Integer> inputVariableDatasetMap) {

    final StringBuilder queryString = new StringBuilder("SELECT \n"
            + "CASE WHEN e.parent_id IS NULL THEN e.nd_experiment_id ELSE e.parent_id END as `experimentId`,\n"
            + "p.observable_id as `variableId`, \n" + "p.value \n" + "FROM nd_experiment e \n"
            + "INNER JOIN project proj ON proj.project_id = e.project_id AND proj.study_id = :studyId \n"
            + "INNER JOIN phenotype p ON p.nd_experiment_id = e.nd_experiment_id \n"
            + "WHERE proj.dataset_type_id IN (:datasetTypeIds) ");

    if (!inputVariableDatasetMap.isEmpty()) {
        queryString.append("AND (");
        final Iterator<Map.Entry<Integer, Integer>> iterator = inputVariableDatasetMap.entrySet().iterator();
        while (iterator.hasNext()) {
            final Map.Entry<Integer, Integer> entry = iterator.next();
            queryString.append(String.format("(p.observable_id = %s AND e.project_id = %s %n)", entry.getKey(),
                    entry.getValue()));/*from  ww  w  . j  av a  2  s. c  om*/
            if (iterator.hasNext()) {
                queryString.append(" OR ");
            } else {
                queryString.append(") \n");
            }
        }
    }

    queryString.append("ORDER BY `experimentId`, `variableId` ;");

    final SQLQuery q = this.getSession().createSQLQuery(queryString.toString());
    q.addScalar("experimentId", new IntegerType());
    q.addScalar("variableId", new StringType());
    q.addScalar("value", new StringType());
    q.setParameter("studyId", studyId);
    q.setParameterList("datasetTypeIds", datasetTypeIds);
    q.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
    final List<Map<String, Object>> results = q.list();

    final Map<Integer, Map<String, List<Object>>> map = new HashMap<>();

    for (final Map<String, Object> row : results) {
        final Integer experimentId = (Integer) row.get("experimentId");
        final String variableId = (String) row.get("variableId");
        final Object value = row.get("value");
        if (!map.containsKey(experimentId)) {
            map.put(experimentId, new HashMap<String, List<Object>>());
        }
        if (!map.get(experimentId).containsKey(variableId)) {
            map.get(experimentId).put(variableId, new ArrayList<Object>());
        }
        // Group values per variable and experimentId.
        map.get(experimentId).get(variableId).add(value);
    }

    return map;
}

---

From source file:org.generationcp.middleware.dao.dms.GeolocationPropertyDao.java

License:Open Source License

public Map<Integer, String> getGeoLocationPropertyByVariableId(final Integer datasetId,
        final Integer instanceDbId) {
    Preconditions.checkNotNull(datasetId);
    final String sql = "SELECT " + "    gp.type_id as variableId, " + "      gp.value as value " + "FROM "
            + "    nd_experiment e " + "        INNER JOIN "
            + "    nd_geolocationprop gp ON gp.nd_geolocation_id = e.nd_geolocation_id " + "WHERE "
            + "      e.project_id = :datasetId " + "      and e.nd_geolocation_id = :instanceDbId";

    final SQLQuery query = this.getSession().createSQLQuery(sql);
    query.addScalar("variableId").addScalar("value").setParameter("datasetId", datasetId)
            .setParameter("instanceDbId", instanceDbId);
    query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);

    final List<Map<String, Object>> results = query.list();
    final Map<Integer, String> geoProperties = new HashMap<>();
    for (final Map<String, Object> result : results) {
        final Integer variableId = (Integer) result.get("variableId");
        final String value = (String) result.get("value");
        geoProperties.put(variableId, value);
    }/* w  w  w . j  a va  2 s  .  c  o m*/
    return geoProperties;
}

---

From source file:org.generationcp.middleware.dao.gdms.DatasetDAO.java

License:Open Source License

public List<Name> getGermplasmNamesByMarkerId(final Integer markerId) {
    try {// ww  w  .  j a va  2  s .  co  m
        if (markerId != null) {
            SQLQuery query = this.getSession().createSQLQuery(DatasetDAO.GET_GERMPLASM_NAMES_BY_MARKER_ID);
            query.addScalar("nid");
            query.addScalar("germplasmId");
            query.addScalar("typeId");
            query.addScalar("nstat");
            query.addScalar("userId");
            query.addScalar("nval");
            query.addScalar("locationId");
            query.addScalar("ndate");
            query.addScalar("referenceId");
            query.setParameter("markerId", markerId);
            query.setResultTransformer(Transformers.aliasToBean(Name.class));
            return query.list();
        }
        return new ArrayList<>();
    } catch (HibernateException e) {
        DatasetDAO.LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException(e.getMessage(), e);
    }
}

---

From source file:org.generationcp.middleware.dao.GermplasmListDAO.java

License:Open Source License

/**
 * @param folderIds//  w w w.  j ava 2s  .  c  o m
 *            a group of folder ids for which we want to return children
 * @return the resultant map which contains the folder meta data
 */
public Map<Integer, ListMetadata> getGermplasmFolderMetadata(final List<Integer> folderIds) {

    if (folderIds.isEmpty()) {
        return Collections.<Integer, ListMetadata>emptyMap();
    }

    final String folderMetaDataQuery = "SELECT parent.listid AS listId, COUNT(child.listid) AS numberOfChildren FROM listnms parent "
            + "LEFT OUTER JOIN listnms child ON child.lhierarchy = parent.listid "
            + "WHERE parent.listid IN (:folderIds) GROUP BY parent.listid";
    final SQLQuery setResultTransformer = this.getSession().createSQLQuery(folderMetaDataQuery);
    setResultTransformer.setParameterList("folderIds", folderIds);
    setResultTransformer.addScalar("listId", new IntegerType());
    setResultTransformer.addScalar("numberOfChildren", new IntegerType());
    setResultTransformer.setResultTransformer(Transformers.aliasToBean(ListMetadata.class));
    final List<ListMetadata> list = setResultTransformer.list();
    return Maps.uniqueIndex(list, new Function<ListMetadata, Integer>() {
        @Override
        public Integer apply(final ListMetadata folderMetaData) {
            return folderMetaData.getListId();
        }
    });
}

---

From source file:org.generationcp.middleware.dao.ProjectDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Project> getProjectsByUser(final WorkbenchUser user, final String cropName) {
    final List<Project> projects = new ArrayList<>();
    try {//from   www  .ja v  a2s  .c  om
        if (user != null) {
            final SQLQuery query = this.getSession().createSQLQuery(GET_PROJECTS_BY_USER_ID);
            query.setParameter("userId", user.getUserid());
            query.setParameter("cropName", cropName);
            query.addScalar("project_id").addScalar("project_uuid").addScalar("project_name")
                    .addScalar("start_date").addScalar("user_id").addScalar("crop_type")
                    .addScalar("last_open_date");
            query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
            final List<Map<String, Object>> results = query.list();

            for (final Map<String, Object> result : results) {
                final Long project_id = Long.valueOf((Integer) result.get("project_id"));
                final String project_uuid = (String) result.get("project_uuid");
                final String project_name = (String) result.get("project_name");
                final Date start_date = (Date) result.get("start_date");
                final Integer user_id = (Integer) result.get("user_id");
                final CropType crop_type = new CropType((String) result.get("crop_type"));
                final Date last_open_date = (Date) result.get("last_open_date");
                final Project u = new Project(project_id, project_uuid, project_name, start_date, user_id,
                        crop_type, last_open_date);
                projects.add(u);
            }
            return projects;
        }
    } catch (final HibernateException e) {
        throw new MiddlewareQueryException("Error in getProjectsByUser(user=" + user
                + ") query from ProjectUserInfoDao: " + e.getMessage(), e);
    }
    return new ArrayList<>();
}

---

From source file:org.generationcp.middleware.manager.GermplasmDataManagerImpl.java

License:Open Source License

/**
 * (non-Javadoc)/*from   w w  w. j  ava 2  s . c  o m*/
 *
 * @see org.generationcp.middleware.manager.api.GermplasmDataManager#getGermplasmWithAllNamesAndAncestry(java.util.Set, int)
 */
@SuppressWarnings("unchecked")
@Override
public List<Germplasm> getGermplasmWithAllNamesAndAncestry(final Set<Integer> gids,
        final int numberOfLevelsToTraverse) {
    final Monitor monitor = MonitorFactory.start("org.generationcp.middleware.manager.GermplasmDataManagerImpl"
            + ".getGermplasmWithAllNamesAndAncestry(Set<Integer> - SetSize(" + gids.size() + ") , int)");

    try {
        final StringBuilder commaSeparatedListOfGids = this.getGidsAsCommaSeparatedList(gids);

        final SQLQuery storedProcedure = this.getActiveSession()
                .createSQLQuery("CALL getGermplasmWithNamesAndAncestry(:gids, :numberOfLevelsToTraverse) ");
        storedProcedure.setParameter("gids", commaSeparatedListOfGids.toString());
        storedProcedure.setParameter("numberOfLevelsToTraverse", numberOfLevelsToTraverse);

        storedProcedure.addEntity("g", Germplasm.class);
        storedProcedure.addJoin("n", "g.names");
        // Be very careful changing anything here.
        // The entity has been added again because the distinct root entity works on the
        // Last added entity
        storedProcedure.addEntity("g", Germplasm.class);
        storedProcedure.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
        return storedProcedure.list();
    } finally {
        monitor.stop();
    }

}

---

From source file:org.hil.core.dao.hibernate.ChildrenDaoHibernate.java

License:Open Source License

public List<RegionVaccinationReportData> getChildrenVaccinationReport(String timeFrom, String timeTo,
        Commune commune, District district) {

    List<RegionVaccinationReportData> statistics = new ArrayList<RegionVaccinationReportData>();

    String[] timeFromYM = timeFrom.split("/");
    String strTimeFrom = timeFromYM[1] + "-" + timeFromYM[0] + "-01 00:00:00";
    log.debug("From: " + strTimeFrom);

    Calendar calendar = Calendar.getInstance();
    String[] timeToYM = timeTo.split("/");
    int year = Integer.parseInt(timeToYM[1]);
    String strTimeFromY = year + "-01-01 00:00:00";
    calendar.set(year, Integer.parseInt(timeToYM[0]), 1);
    int maxDay = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
    String strTimeTo = year + "-" + timeToYM[0] + "-" + maxDay + " 23:59:59";
    log.debug("To: " + strTimeTo);

    String queryLocation = "";
    if (commune != null)
        queryLocation = " AND cm.id = :communeId";
    else if (district != null)
        queryLocation = " AND cm.id_district = :districtId GROUP BY cm.id ORDER BY cm.id ASC";

    String sql = this.buidlSQLforReport("commune", queryLocation, "strTimeFrom");
    log.debug(sql);/*w  w  w  .j a  v a 2  s.  c  o  m*/

    SQLQuery qry = getSession().createSQLQuery(sql);
    if (commune != null)
        qry.setParameter("communeId", commune.getId());
    else if (district != null)
        qry.setParameter("districtId", district.getId());
    qry.setParameter("timeYear", year);
    qry.setParameter("strTimeFrom", strTimeFrom);
    qry.setParameter("strTimeTo", strTimeTo);

    qry.addScalar("regionName", Hibernate.STRING);
    qry.addScalar("childrenUnder1", Hibernate.INTEGER);
    qry.addScalar("VGBL24", Hibernate.INTEGER);
    qry.addScalar("VGBG24", Hibernate.INTEGER);
    qry.addScalar("BCG", Hibernate.INTEGER);
    qry.addScalar("DPT_VGB_Hib1", Hibernate.INTEGER);
    qry.addScalar("OPV1", Hibernate.INTEGER);
    qry.addScalar("DPT_VGB_Hib2", Hibernate.INTEGER);
    qry.addScalar("OPV2", Hibernate.INTEGER);
    qry.addScalar("DPT_VGB_Hib3", Hibernate.INTEGER);
    qry.addScalar("OPV3", Hibernate.INTEGER);
    qry.addScalar("measles1", Hibernate.INTEGER);
    qry.addScalar("eVGBL24", Hibernate.INTEGER);
    qry.addScalar("eVGBG24", Hibernate.INTEGER);
    qry.addScalar("eBCG", Hibernate.INTEGER);
    qry.addScalar("eDPT_VGB_Hib1", Hibernate.INTEGER);
    qry.addScalar("eOPV1", Hibernate.INTEGER);
    qry.addScalar("eDPT_VGB_Hib2", Hibernate.INTEGER);
    qry.addScalar("eOPV2", Hibernate.INTEGER);
    qry.addScalar("eDPT_VGB_Hib3", Hibernate.INTEGER);
    qry.addScalar("eOPV3", Hibernate.INTEGER);
    qry.addScalar("eMeasles1", Hibernate.INTEGER);
    qry.addScalar("protectedTetanusCases", Hibernate.INTEGER);
    qry.addScalar("reactionNormalCases", Hibernate.INTEGER);
    qry.addScalar("reactionSeriousCases", Hibernate.INTEGER);
    qry.addScalar("amountOfFinish", Hibernate.INTEGER);
    qry.setResultTransformer(Transformers.aliasToBean(RegionVaccinationReportData.class));

    statistics = qry.list();

    if (commune != null && statistics.size() > 0) {
        RegionVaccinationReportData same = new RegionVaccinationReportData();
        same.setAmountOfFinish(statistics.get(0).getAmountOfFinish());
        same.setAmountOfFinish(statistics.get(0).getAmountOfFinish());
        same.setBCG(statistics.get(0).getBCG());
        same.setChildrenUnder1(statistics.get(0).getChildrenUnder1());
        same.setDPT_VGB_Hib1(statistics.get(0).getDPT_VGB_Hib1());
        same.setDPT_VGB_Hib2(statistics.get(0).getDPT_VGB_Hib2());
        same.setDPT_VGB_Hib3(statistics.get(0).getDPT_VGB_Hib3());
        same.setMeasles1(statistics.get(0).getMeasles1());
        same.setOPV1(statistics.get(0).getOPV1());
        same.setOPV2(statistics.get(0).getOPV2());
        same.setOPV3(statistics.get(0).getOPV3());
        same.setVGBG24(statistics.get(0).getVGBG24());
        same.setVGBL24(statistics.get(0).getVGBL24());
        same.setProtectedTetanusCases(statistics.get(0).getProtectedTetanusCases());
        same.setReactionNormalCases(statistics.get(0).getReactionNormalCases());
        same.setReactionSeriousCases(statistics.get(0).getReactionSeriousCases());
        same.seteBCG(statistics.get(0).geteBCG());
        same.seteDPT_VGB_Hib1(statistics.get(0).geteDPT_VGB_Hib1());
        same.seteDPT_VGB_Hib2(statistics.get(0).geteDPT_VGB_Hib2());
        same.seteDPT_VGB_Hib3(statistics.get(0).geteDPT_VGB_Hib3());
        same.seteMeasles1(statistics.get(0).geteMeasles1());
        same.seteOPV1(statistics.get(0).geteOPV1());
        same.seteOPV2(statistics.get(0).geteOPV2());
        same.seteOPV3(statistics.get(0).geteOPV3());
        same.seteVGBG24(statistics.get(0).geteVGBG24());
        same.seteVGBL24(statistics.get(0).geteVGBL24());
        statistics.add(same);

        String sql2 = this.buidlSQLforReport("commune", queryLocation, "strTimeFromY");
        SQLQuery qry2 = getSession().createSQLQuery(sql2);
        qry2.setParameter("communeId", commune.getId());
        qry2.setParameter("timeYear", year);
        qry2.setParameter("strTimeTo", strTimeTo);
        qry2.setParameter("strTimeFromY", strTimeFromY);
        qry2.addScalar("regionName", Hibernate.STRING);
        qry2.addScalar("childrenUnder1", Hibernate.INTEGER);
        qry2.addScalar("VGBL24", Hibernate.INTEGER);
        qry2.addScalar("VGBG24", Hibernate.INTEGER);
        qry2.addScalar("BCG", Hibernate.INTEGER);
        qry2.addScalar("DPT_VGB_Hib1", Hibernate.INTEGER);
        qry2.addScalar("OPV1", Hibernate.INTEGER);
        qry2.addScalar("DPT_VGB_Hib2", Hibernate.INTEGER);
        qry2.addScalar("OPV2", Hibernate.INTEGER);
        qry2.addScalar("DPT_VGB_Hib3", Hibernate.INTEGER);
        qry2.addScalar("OPV3", Hibernate.INTEGER);
        qry2.addScalar("measles1", Hibernate.INTEGER);
        qry2.addScalar("eVGBL24", Hibernate.INTEGER);
        qry2.addScalar("eVGBG24", Hibernate.INTEGER);
        qry2.addScalar("eBCG", Hibernate.INTEGER);
        qry2.addScalar("eDPT_VGB_Hib1", Hibernate.INTEGER);
        qry2.addScalar("eOPV1", Hibernate.INTEGER);
        qry2.addScalar("eDPT_VGB_Hib2", Hibernate.INTEGER);
        qry2.addScalar("eOPV2", Hibernate.INTEGER);
        qry2.addScalar("eDPT_VGB_Hib3", Hibernate.INTEGER);
        qry2.addScalar("eOPV3", Hibernate.INTEGER);
        qry2.addScalar("eMeasles1", Hibernate.INTEGER);
        qry2.addScalar("protectedTetanusCases", Hibernate.INTEGER);
        qry2.addScalar("reactionNormalCases", Hibernate.INTEGER);
        qry2.addScalar("reactionSeriousCases", Hibernate.INTEGER);
        qry2.addScalar("amountOfFinish", Hibernate.INTEGER);
        qry2.setResultTransformer(Transformers.aliasToBean(RegionVaccinationReportData.class));
        statistics.add((RegionVaccinationReportData) qry2.list().get(0));
    } else if (district != null && statistics.size() > 0) {
        String sql2 = this.buidlSQLforReport("district", queryLocation, "strTimeFrom");
        //log.debug(sql2);
        SQLQuery qry2 = getSession().createSQLQuery(sql2);
        qry2.setParameter("districtId", district.getId());
        qry2.setParameter("timeYear", year);
        qry2.setParameter("strTimeFrom", strTimeFrom);
        qry2.setParameter("strTimeTo", strTimeTo);

        qry2.addScalar("regionName", Hibernate.STRING);
        qry2.addScalar("childrenUnder1", Hibernate.INTEGER);
        qry2.addScalar("VGBL24", Hibernate.INTEGER);
        qry2.addScalar("VGBG24", Hibernate.INTEGER);
        qry2.addScalar("BCG", Hibernate.INTEGER);
        qry2.addScalar("DPT_VGB_Hib1", Hibernate.INTEGER);
        qry2.addScalar("OPV1", Hibernate.INTEGER);
        qry2.addScalar("DPT_VGB_Hib2", Hibernate.INTEGER);
        qry2.addScalar("OPV2", Hibernate.INTEGER);
        qry2.addScalar("DPT_VGB_Hib3", Hibernate.INTEGER);
        qry2.addScalar("OPV3", Hibernate.INTEGER);
        qry2.addScalar("measles1", Hibernate.INTEGER);
        qry2.addScalar("eVGBL24", Hibernate.INTEGER);
        qry2.addScalar("eVGBG24", Hibernate.INTEGER);
        qry2.addScalar("eBCG", Hibernate.INTEGER);
        qry2.addScalar("eDPT_VGB_Hib1", Hibernate.INTEGER);
        qry2.addScalar("eOPV1", Hibernate.INTEGER);
        qry2.addScalar("eDPT_VGB_Hib2", Hibernate.INTEGER);
        qry2.addScalar("eOPV2", Hibernate.INTEGER);
        qry2.addScalar("eDPT_VGB_Hib3", Hibernate.INTEGER);
        qry2.addScalar("eOPV3", Hibernate.INTEGER);
        qry2.addScalar("eMeasles1", Hibernate.INTEGER);
        qry2.addScalar("protectedTetanusCases", Hibernate.INTEGER);
        qry2.addScalar("reactionNormalCases", Hibernate.INTEGER);
        qry2.addScalar("reactionSeriousCases", Hibernate.INTEGER);
        qry2.addScalar("amountOfFinish", Hibernate.INTEGER);
        qry2.setResultTransformer(Transformers.aliasToBean(RegionVaccinationReportData.class));
        statistics.add((RegionVaccinationReportData) qry2.list().get(0));

        String sql3 = this.buidlSQLforReport("district", queryLocation, "strTimeFromY");
        //log.debug(sql3);
        SQLQuery qry3 = getSession().createSQLQuery(sql3);
        qry3.setParameter("districtId", district.getId());
        qry3.setParameter("timeYear", year);
        qry3.setParameter("strTimeTo", strTimeTo);
        qry3.setParameter("strTimeFromY", strTimeFromY);
        qry3.addScalar("regionName", Hibernate.STRING);
        qry3.addScalar("childrenUnder1", Hibernate.INTEGER);
        qry3.addScalar("VGBL24", Hibernate.INTEGER);
        qry3.addScalar("VGBG24", Hibernate.INTEGER);
        qry3.addScalar("BCG", Hibernate.INTEGER);
        qry3.addScalar("DPT_VGB_Hib1", Hibernate.INTEGER);
        qry3.addScalar("OPV1", Hibernate.INTEGER);
        qry3.addScalar("DPT_VGB_Hib2", Hibernate.INTEGER);
        qry3.addScalar("OPV2", Hibernate.INTEGER);
        qry3.addScalar("DPT_VGB_Hib3", Hibernate.INTEGER);
        qry3.addScalar("OPV3", Hibernate.INTEGER);
        qry3.addScalar("measles1", Hibernate.INTEGER);
        qry3.addScalar("eVGBL24", Hibernate.INTEGER);
        qry3.addScalar("eVGBG24", Hibernate.INTEGER);
        qry3.addScalar("eBCG", Hibernate.INTEGER);
        qry3.addScalar("eDPT_VGB_Hib1", Hibernate.INTEGER);
        qry3.addScalar("eOPV1", Hibernate.INTEGER);
        qry3.addScalar("eDPT_VGB_Hib2", Hibernate.INTEGER);
        qry3.addScalar("eOPV2", Hibernate.INTEGER);
        qry3.addScalar("eDPT_VGB_Hib3", Hibernate.INTEGER);
        qry3.addScalar("eOPV3", Hibernate.INTEGER);
        qry3.addScalar("eMeasles1", Hibernate.INTEGER);
        qry3.addScalar("protectedTetanusCases", Hibernate.INTEGER);
        qry3.addScalar("reactionNormalCases", Hibernate.INTEGER);
        qry3.addScalar("reactionSeriousCases", Hibernate.INTEGER);
        qry3.addScalar("amountOfFinish", Hibernate.INTEGER);
        qry3.setResultTransformer(Transformers.aliasToBean(RegionVaccinationReportData.class));
        statistics.add((RegionVaccinationReportData) qry3.list().get(0));
    }

    log.debug("Report: " + statistics.size());
    return statistics;
}

---

From source file:org.hil.core.dao.hibernate.ChildrenDaoHibernate.java

License:Open Source License

public List<ChildrenPrintVO> searchChildrenForPrint(ChildrenSearchVO params) {
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
    String strDOBFrom = format.format(params.getDateOfBirthFrom());
    String strDOBTo = format.format(params.getDateOfBirthTo());

    String sql = "Select cvh.id_children as childId, c.full_name as fullName, c.date_of_birth as dateOfBirth, v.village_name as villageName, cm.commune_name as communeName,"
            + " c.child_code as childCode, c.mother_name as motherName, c.mother_birth_year as motherBirthYear, c.gender as gender, c.father_name as fatherName, c.father_birth_year as fatherBirthYear,"
            + " c.father_mobile as fatherMobile, c.mother_mobile as motherMobile, c.caretaker_name as caretakerName, c.caretaker_mobile as caretakerMobile, "
            + " group_concat(if(cvh.id_vaccination=1, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS VGB,"
            + " group_concat(if(cvh.id_vaccination=2, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS BCG,"
            + " group_concat(if(cvh.id_vaccination=3, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS DPT_VGB_Hib1,"
            + " group_concat(if(cvh.id_vaccination=4, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS OPV1,"
            + " group_concat(if(cvh.id_vaccination=5, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS DPT_VGB_Hib2,"
            + " group_concat(if(cvh.id_vaccination=6, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS OPV2,"
            + " group_concat(if(cvh.id_vaccination=7, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS DPT_VGB_Hib3,"
            + " group_concat(if(cvh.id_vaccination=8, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS OPV3,"
            + " group_concat(if(cvh.id_vaccination=9, DATE_FORMAT(date_of_immunization, '%d/%m/%Y'), null)) AS measles1"
            + " FROM children c, children_vaccination_history cvh, village v, commune cm"
            + " where cvh.id_children=c.id and DATE(c.date_of_birth) >=:strDOBFrom and DATE(c.date_of_birth) <=:strDOBTo  and "
            + " c.id_village=v.id and v.id_commune=cm.id and cm.id=:communeId and (cvh.vaccinated=1 or cvh.vaccinated=0 or cvh.vaccinated=3)"
            + " group by cvh.id_children  order by c.id asc";

    SQLQuery qry = getSession().createSQLQuery(sql);
    qry.setParameter("communeId", params.getCommuneId());
    qry.setParameter("strDOBFrom", strDOBFrom);
    qry.setParameter("strDOBTo", strDOBTo);

    qry.addScalar("fullName", Hibernate.STRING);
    qry.addScalar("dateOfBirth", Hibernate.DATE);
    qry.addScalar("childCode", Hibernate.STRING);
    qry.addScalar("gender", Hibernate.BOOLEAN);
    qry.addScalar("villageName", Hibernate.STRING);
    qry.addScalar("communeName", Hibernate.STRING);
    qry.addScalar("fatherName", Hibernate.STRING);
    qry.addScalar("fatherBirthYear", Hibernate.INTEGER);
    qry.addScalar("fatherMobile", Hibernate.STRING);
    qry.addScalar("motherName", Hibernate.STRING);
    qry.addScalar("motherBirthYear", Hibernate.INTEGER);
    qry.addScalar("motherMobile", Hibernate.STRING);
    qry.addScalar("caretakerName", Hibernate.STRING);
    qry.addScalar("caretakerMobile", Hibernate.STRING);
    qry.addScalar("VGB", Hibernate.STRING);
    qry.addScalar("BCG", Hibernate.STRING);
    qry.addScalar("DPT_VGB_Hib1", Hibernate.STRING);
    qry.addScalar("OPV1", Hibernate.STRING);
    qry.addScalar("DPT_VGB_Hib2", Hibernate.STRING);
    qry.addScalar("OPV2", Hibernate.STRING);
    qry.addScalar("DPT_VGB_Hib3", Hibernate.STRING);
    qry.addScalar("OPV3", Hibernate.STRING);
    qry.addScalar("measles1", Hibernate.STRING);

    qry.setResultTransformer(Transformers.aliasToBean(ChildrenPrintVO.class));

    List<ChildrenPrintVO> list = qry.list();

    return list;/*  ww w  .j  a v  a 2s.  com*/
}

---

From source file:org.hil.core.dao.hibernate.ChildrenDaoHibernate.java

License:Open Source License

public List<ChildrenVaccinatedInLocationVO> getChildrenVaccinatedInLocationReport(String timeFrom,
        String timeTo, Commune commune, District district, Vaccination vaccine) {
    List<ChildrenVaccinatedInLocationVO> statistics = new ArrayList<ChildrenVaccinatedInLocationVO>();

    String[] timeFromYM = timeFrom.split("/");
    String strTimeFrom = timeFromYM[1] + "-" + timeFromYM[0] + "-01 00:00:00";
    log.debug("From: " + strTimeFrom);

    Calendar calendar = Calendar.getInstance();
    String[] timeToYM = timeTo.split("/");
    int year = Integer.parseInt(timeToYM[1]);
    String strTimeFromY = year + "-01-01 00:00:00";
    calendar.set(year, Integer.parseInt(timeToYM[0]), 1);
    int maxDay = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
    String strTimeTo = year + "-" + timeToYM[0] + "-" + maxDay + " 23:59:59";
    log.debug("To: " + strTimeTo);

    String sql = "";

    if (commune != null) {
        sql = "(SELECT c.id as id, cm.commune_name as communeName, v.village_name as villageName, c.child_code as childCode, "
                + " c.full_name as fullName, c.gender as gender, c.date_of_birth as dateOfBirth, c.mother_name as motherName, "
                + "   cvh.date_of_immunization as dateOfImmunization, cvh.other_vaccinated_location as otherLocation, null as vaccinatedCommune "
                + " FROM children_vaccination_history cvh, children c, village v, commune cm "
                + " WHERE cvh.id_vaccination=:vaccine AND cvh.vaccinated=1 AND cvh.other_vaccinated_location BETWEEN 1 AND 4 AND cvh.id_children=c.id "
                + " AND c.id_village=v.id AND v.id_commune=cm.id AND cm.id=:cId "
                + " AND DATE(cvh.date_of_immunization) BETWEEN :beginTime AND :endTime) " + " UNION ALL "
                + " (SELECT c.id as id, cm2.commune_name as communeName, v.village_name as villageName, c.child_code as childCode, "
                + " c.full_name as fullName, c.gender as gender, c.date_of_birth as dateOfBirth, c.mother_name as motherName, "
                + " cvh.date_of_immunization as dateOfImmunization, cvh.other_vaccinated_location as otherLocation, cm.commune_name as vaccinatedCommune "
                + " FROM children_vaccination_history cvh, children c, village v,commune cm, commune cm2 "
                + " WHERE cvh.id_vaccination=:vaccine AND cvh.vaccinated=1 AND cvh.id_vaccinated_location=cm.id AND cm.id=:cId "
                + " AND DATE(cvh.date_of_immunization) BETWEEN :beginTime AND :endTime "
                + " And cvh.id_children=c.id and v.id=c.id_village and cm2.id=v.id_commune) order by id";
    } else if (district != null) {
        sql = "(SELECT c.id as id, cm.commune_name as communeName, v.village_name as villageName, c.child_code as childCode, "
                + " c.full_name as fullName, c.gender as gender, c.date_of_birth as dateOfBirth, c.mother_name as motherName, "
                + " cvh.date_of_immunization as dateOfImmunization, cvh.other_vaccinated_location as otherLocation, null as vaccinatedCommune"
                + " FROM children_vaccination_history cvh, children c, village v, commune cm, district d "
                + " WHERE cvh.id_vaccination=:vaccine AND cvh.vaccinated=1 AND cvh.other_vaccinated_location BETWEEN 1 AND 4 AND cvh.id_children=c.id "
                + " AND c.id_village=v.id AND v.id_commune=cm.id AND cm.id_district=d.id AND d.id=:dId "
                + " AND DATE(cvh.date_of_immunization) BETWEEN :beginTime AND :endTime)" + " UNION ALL "
                + " (SELECT c.id as id, cm2.commune_name as communeName, v.village_name as villageName, c.child_code as childCode, "
                + " c.full_name as fullName, c.gender as gender, c.date_of_birth as dateOfBirth, c.mother_name as motherName, "
                + " cvh.date_of_immunization as dateOfImmunization, cvh.other_vaccinated_location as otherLocation, cm.commune_name as vaccinatedCommune "
                + " FROM children_vaccination_history cvh, children c, village v,commune cm, district d, commune cm2 "
                + " WHERE cvh.id_vaccination=:vaccine AND cvh.vaccinated=1 AND cvh.id_vaccinated_location=cm.id AND cm.id_district=d.id AND d.id=:dId"
                + " AND DATE(cvh.date_of_immunization) BETWEEN :beginTime AND :endTime "
                + " AND cvh.id_children=c.id and v.id=c.id_village and cm2.id=v.id_commune) ORDER BY id";
    }//from  w  ww.  j  a v a2s .  c  om

    log.debug(sql);

    SQLQuery qry = getSession().createSQLQuery(sql);
    if (commune != null)
        qry.setParameter("cId", commune.getId());
    else if (district != null)
        qry.setParameter("dId", district.getId());
    qry.setParameter("vaccine", vaccine.getId());
    qry.setParameter("beginTime", strTimeFrom);
    qry.setParameter("endTime", strTimeTo);

    qry.addScalar("id", Hibernate.LONG);
    qry.addScalar("communeName", Hibernate.STRING);
    qry.addScalar("villageName", Hibernate.STRING);
    qry.addScalar("childCode", Hibernate.STRING);
    qry.addScalar("fullName", Hibernate.STRING);
    qry.addScalar("gender", Hibernate.BOOLEAN);
    qry.addScalar("dateOfBirth", Hibernate.DATE);
    qry.addScalar("motherName", Hibernate.STRING);
    qry.addScalar("dateOfImmunization", Hibernate.DATE);
    qry.addScalar("otherLocation", Hibernate.SHORT);
    qry.addScalar("vaccinatedCommune", Hibernate.STRING);

    qry.setResultTransformer(Transformers.aliasToBean(ChildrenVaccinatedInLocationVO.class));

    statistics = qry.list();

    log.debug("Report: " + statistics.size());

    return statistics;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• »