List of usage examples for org.hibernate SQLQuery setParameterList
@Override
NativeQuery<T> setParameterList(String name, Object[] values);
From source file:org.egov.services.deduction.ScheduledRemittanceService.java
License:Open Source License
private Collection<? extends AutoRemittanceBean> getNonControleCodeGJVRecovries(final Integer dept, final int bankaccount) { final StringBuffer qry = new StringBuffer(2048); qry.append(" SELECT DISTINCT gl.id AS generalledgerId, vh.fundid AS fundId, gl.creditamount " + " AS gldtlAmount, " + bankaccount + " AS bankAccountId " + " FROM VOUCHERHEADER vh , VOUCHERMIS mis, GENERALLEDGER gl , fund f, TDS recovery " + " WHERE recovery.GLCODEID =gl.GLCODEID AND vh.ID =gl.VOUCHERHEADERID" + " AND gl.remittanceDate IS NULL AND mis.VOUCHERHEADERID =vh.ID AND vh.STATUS =0 and vh.fundid=f.id " + " and vh.name='" + FinancialConstants.JOURNALVOUCHER_NAME_GENERAL + "' and vh.moduleid is null " + " AND recovery.ID =" + recovery.getId() + " AND vh.voucherdate >= :startdate "); if (lastRunDate != null) qry.append(" and vh.voucherdate <= :lastrundate"); if (receiptFundCodes != null && !receiptFundCodes.isEmpty()) qry.append(" and f.code in (:fundCodes) "); final SQLQuery query = persistenceService.getSession().createSQLQuery(qry.toString()); query.addScalar("generalledgerId", IntegerType.INSTANCE).addScalar("fundId", IntegerType.INSTANCE) .addScalar("gldtlAmount", DoubleType.INSTANCE).addScalar("bankAccountId", IntegerType.INSTANCE); if (lastRunDate != null) query.setDate("lastrundate", new java.sql.Date(lastRunDate.getTime())); if (startDate != null) query.setDate("startdate", new java.sql.Date(startDate.getTime())); if (receiptFundCodes != null && !receiptFundCodes.isEmpty()) query.setParameterList("fundCodes", receiptFundCodes); query.setResultTransformer(Transformers.aliasToBean(AutoRemittanceBean.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug("ReceiptRecoveries query " + qry); return query.list(); }
From source file:org.egov.services.deduction.ScheduledRemittanceService.java
License:Open Source License
/** * * @param recoveryId Will return all receipt recoveries which are Remitted and approved * @param deptId//from ww w . ja v a 2 s. co m * @param lastRunDate * @param startDate * @param receiptBankAccountId * @return * */ private List getReceiptRecoveries(final Integer deptId, final Integer receiptBankAccountId) { if (LOGGER.isDebugEnabled()) LOGGER.debug("Fetching ReceiptRecoveries"); final StringBuffer queryStr = new StringBuffer( "SELECT distinct gl.id as generalledgerId, vh.fundid AS fundId, egr.GLDTLAMT AS gldtlAmount, gld.DETAILTYPEID AS detailtypeId," + " gld.DETAILKEYID AS detailkeyId, egr.ID AS remittanceGldtlId," + receiptBankAccountId + " as bankAccountId, " + " egr.GLDTLAMT- (SELECT case when SUM(egd.REMITTEDAMT) = NULL then 0 else SUM(egd.REMITTEDAMT) end " + " FROM EG_REMITTANCE_GLDTL egr1, eg_remittance_detail egd, eg_remittance eg, voucherheader vh WHERE vh.status! =4 " + " AND eg.PAYMENTVHID =vh.id AND egd.remittanceid=eg.id AND egr1.id =egd.remittancegldtlid " + " AND egr1.id =egr.id ) AS pendingAmount FROM VOUCHERHEADER vh , " + " VOUCHERMIS mis, GENERALLEDGER gl, voucherheader payinslip, fund f, egf_instrumentheader ih, egf_instrumentotherdetails io," + " GENERALLEDGERDETAIL gld, EG_REMITTANCE_GLDTL egr, egcl_collectionvoucher cv, egcl_collectioninstrument ci,TDS recovery5_ WHERE recovery5_.GLCODEID =gl.GLCODEID AND" + " gld.ID =egr.GLDTLID AND gl.ID =gld.GENERALLEDGERID AND vh.ID =gl.VOUCHERHEADERID " + " and gl.remittanceDate is null and f.id=vh.fundid " + " AND mis.VOUCHERHEADERID =vh.ID AND vh.STATUS=0 AND io.payinslipid =payinslip.id " + " and cv.voucherheaderid= vh.id and ci.collectionheaderid= cv.collectionheaderid and ci.instrumentmasterid= ih.id" + " and payinslip.status=0 AND ih.id_status NOT IN (" + "select id from egw_status where moduletype='Instrument' and description in ('" + FinancialConstants.INSTRUMENT_CANCELLED_STATUS + "','" + FinancialConstants.INSTRUMENT_SURRENDERED_STATUS + "','" + FinancialConstants.INSTRUMENT_SURRENDERED_FOR_REASSIGN_STATUS + "') " + " ) AND mis.departmentid = " + deptId + " AND egr.GLDTLAMT- (SELECT case when SUM(egd.REMITTEDAMT) = NULL then 0 else SUM(egd.REMITTEDAMT) end FROM EG_REMITTANCE_GLDTL egr1, " + " eg_remittance_detail egd, eg_remittance eg, voucherheader vh WHERE vh.status !=4 " + " AND eg.PAYMENTVHID =vh.id AND egd.remittanceid =eg.id AND egr1.id =egd.remittancegldtlid " + " AND egr1.id =egr.id ) >0 AND recovery5_.ID =" + recovery.getId()); if (lastRunDate != null) queryStr.append(" and payinslip.voucherdate<='" + sdf.format(lastRunDate) + "' "); if (startDate != null) queryStr.append(" and payinslip.voucherdate>='" + sdf.format(startDate) + "'"); if (receiptFundCodes != null && !receiptFundCodes.isEmpty()) queryStr.append(" and f.code in (:fundCodes) "); final SQLQuery query = persistenceService.getSession().createSQLQuery(queryStr.toString()); query.addScalar("generalledgerId", IntegerType.INSTANCE).addScalar("fundId", IntegerType.INSTANCE) .addScalar("gldtlAmount", DoubleType.INSTANCE).addScalar("detailtypeId", IntegerType.INSTANCE) .addScalar("detailkeyId", IntegerType.INSTANCE).addScalar("remittanceGldtlId", IntegerType.INSTANCE) .addScalar("pendingAmount", DoubleType.INSTANCE).addScalar("bankAccountId", IntegerType.INSTANCE); /* * if(lastRunDate!=null) { query.setDate("lastrundate", new java.sql.Date(lastRunDate.getTime())); } if(startDate!=null) { * query.setDate("startdate", new java.sql.Date(startDate.getTime())); } */ if (receiptFundCodes != null && !receiptFundCodes.isEmpty()) query.setParameterList("fundCodes", receiptFundCodes); query.setResultTransformer(Transformers.aliasToBean(AutoRemittanceBean.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug("ReceiptRecoveries query " + queryStr); return query.list(); }
From source file:org.egov.wtms.application.service.CurrentDcbService.java
License:Open Source License
@SuppressWarnings("unchecked") @ReadOnly//from www . j av a2 s.com public List<DCBReportResult> getReportResult(final String paramList, final String connectionType, final String mode, final String reportType) { StringBuilder query; final StringBuilder selectQry1 = new StringBuilder(); final StringBuilder selectQry2 = new StringBuilder(); StringBuilder fromQry; StringBuilder whereQry = new StringBuilder(); final StringBuilder groupByQry = new StringBuilder(); selectQry2.append( " cast(SUM(arr_demand) as bigint) AS arrDemand,cast(SUM(curr_demand) as bigint) AS currDemand,cast(SUM(arr_coll) as bigint) AS arrColl,cast(SUM(curr_coll) as bigint) AS currColl,") .append("cast(SUM(arr_balance) as bigint) AS arrBalance,cast(SUM(curr_balance) as bigint) AS currBalance "); fromQry = new StringBuilder(" from egwtr_mv_dcb_view dcbinfo,eg_boundary boundary "); if (ZONEWISE.equalsIgnoreCase(mode)) { selectQry1.append( "select distinct cast(dcbinfo.zoneid as integer) as \"zoneId\",boundary.name as \"boundaryName\", count(hscno) as countofconsumerno,"); groupByQry.append(" group by dcbinfo.zoneid,boundary.name order by boundary.name"); whereQry.append(" where dcbinfo.zoneid=boundary.id "); if (isNotBlank(paramList)) whereQry = whereQry.append(" and dcbinfo.zoneid in (:searchParam)"); } else if (WARDWISE.equalsIgnoreCase(mode)) { selectQry1.append( "select distinct cast(dcbinfo.wardid as integer) as \"wardId\",boundary.name as \"boundaryName\",count(hscno) as countOfConsumerNo, "); groupByQry.append(" group by dcbinfo.wardid,boundary.name order by boundary.name"); whereQry.append(" where dcbinfo.wardid=boundary.id "); if (isNotBlank(paramList) && reportType.equalsIgnoreCase("wardWise")) whereQry = whereQry.append(" and dcbinfo.wardid in (:searchParam)"); if (isNotBlank(paramList) && !reportType.equalsIgnoreCase("wardWise")) whereQry = whereQry.append(" and dcbinfo.zoneid in (:searchParam)"); } else if (BLOCKWISE.equalsIgnoreCase(mode)) { selectQry1.append( "select distinct cast(dcbinfo.block as integer) as \"wardId\",boundary.name as \"boundaryName\", count(hscno) as countOfConsumerNo,"); groupByQry.append(" group by dcbinfo.block,boundary.name order by boundary.name"); whereQry.append(" where dcbinfo.block=boundary.id "); if (isNotBlank(paramList) && reportType.equalsIgnoreCase("blockWise")) whereQry = whereQry.append(" and dcbinfo.block in (:searchParam)"); if (isNotBlank(paramList) && !reportType.equalsIgnoreCase("blockWise")) whereQry = whereQry.append(" and dcbinfo.wardid in (:searchParam)"); } else if (LOCALITYWISE.equalsIgnoreCase(mode)) { selectQry1.append( "select distinct cast(dcbinfo.locality as integer) as \"locality\",boundary.name as \"boundaryName\", count(hscno) as countOfConsumerNo, "); groupByQry.append(" group by dcbinfo.locality,boundary.name order by boundary.name"); whereQry.append(" where dcbinfo.locality=boundary.id and dcbinfo.locality in (:searchParam)"); } else if (PROPERTY.equalsIgnoreCase(mode)) { selectQry1.append( "select distinct dcbinfo.hscno as hscNo,dcbinfo.propertyid as \"propertyId\" ,dcbinfo.username as \"userName\", "); fromQry = new StringBuilder(" from egwtr_mv_dcb_view dcbinfo "); groupByQry.append("group by dcbinfo.hscno,dcbinfo.propertyid,dcbinfo.username "); whereQry.append(" where dcbinfo.hscno is not null "); if (isNotBlank(paramList) && reportType.equalsIgnoreCase("localityWise")) whereQry = whereQry.append(" and dcbinfo.locality in (:searchParam)"); else whereQry = whereQry.append(" and dcbinfo.block in (:searchParam)"); } if (isNotEmpty(connectionType)) whereQry.append(" and dcbinfo.connectiontype =:connectionType"); whereQry.append(" and dcbinfo.connectionstatus = 'ACTIVE'"); query = selectQry1.append(selectQry2).append(fromQry).append(whereQry).append(groupByQry); final SQLQuery sqlQuery = entityManager.unwrap(Session.class).createSQLQuery(query.toString()); if (isNotBlank(paramList)) { final List<Integer> locationList = new ArrayList<>(); for (final String location : paramList.split(",")) locationList.add(Integer.parseInt(location)); sqlQuery.setParameterList("searchParam", locationList); } if (isNotBlank(connectionType)) sqlQuery.setParameter("connectionType", connectionType); return prepareResult(sqlQuery.list(), mode); }
From source file:org.generationcp.middleware.dao.AttributeDAO.java
License:Open Source License
@SuppressWarnings("unchecked") public List<Attribute> getAttributeValuesByTypeAndGIDList(final Integer attributeType, final List<Integer> gidList) { List<Attribute> returnList = new ArrayList<>(); if (gidList != null && !gidList.isEmpty()) { try {// w w w . j a va 2s . c o m final String sql = "SELECT {a.*}" + " FROM atributs a" + " WHERE a.atype=:attributeType" + " AND a.gid in (:gidList)"; final SQLQuery query = this.getSession().createSQLQuery(sql); query.addEntity("a", Attribute.class); query.setParameter("attributeType", attributeType); query.setParameterList("gidList", gidList); returnList = query.list(); } catch (final HibernateException e) { throw new MiddlewareQueryException("Error with getAttributeValuesByTypeAndGIDList(attributeType=" + attributeType + ", gidList=" + gidList + "): " + e.getMessage(), e); } } return returnList; }
From source file:org.generationcp.middleware.dao.CharacterDataDAO.java
License:Open Source License
@SuppressWarnings("rawtypes") public List<CharacterDataElement> getValuesByOunitIDList(List<Integer> ounitIdList) throws MiddlewareQueryException { try {//from w w w . ja v a2 s. c om SQLQuery query = getSession().createSQLQuery(CharacterData.GET_BY_OUNIT_ID_LIST); query.setParameterList("ounitIdList", ounitIdList); List<CharacterDataElement> dataValues = new ArrayList<CharacterDataElement>(); List results = query.list(); for (Object o : results) { Object[] result = (Object[]) o; if (result != null) { Integer ounitId = (Integer) result[0]; Integer variateId = (Integer) result[1]; String variateName = (String) result[2]; String value = (String) result[3]; CharacterDataElement dataElement = new CharacterDataElement(ounitId, variateId, variateName, value); dataValues.add(dataElement); } } return dataValues; } catch (HibernateException e) { throw new MiddlewareQueryException("Error with getValuesByOunitIDList(ounitIdList=" + ounitIdList + ") query from CharacterData: " + e.getMessage(), e); } }
From source file:org.generationcp.middleware.dao.CharacterLevelDAO.java
License:Open Source License
@SuppressWarnings("rawtypes") public List<CharacterLevelElement> getValuesByOunitIDList(List<Integer> ounitIdList) throws MiddlewareQueryException { List<CharacterLevelElement> levelValues = new ArrayList<CharacterLevelElement>(); if (ounitIdList == null || ounitIdList.isEmpty()) { return levelValues; }//from www .j av a 2 s . c o m try { SQLQuery query = getSession().createSQLQuery(CharacterLevel.GET_BY_OUNIT_ID_LIST); query.setParameterList("ounitIdList", ounitIdList); List results = query.list(); for (Object o : results) { Object[] result = (Object[]) o; if (result != null) { Integer ounitId = (Integer) result[0]; Integer factorId = (Integer) result[1]; String factorName = (String) result[2]; String value = (String) result[3]; CharacterLevelElement levelElement = new CharacterLevelElement(ounitId, factorId, factorName, value); levelValues.add(levelElement); } } return levelValues; } catch (HibernateException e) { throw new MiddlewareQueryException("Error with getValuesByOunitIDList(ounitIdList=" + ounitIdList + ") [get Character Level Values] query: " + e.getMessage(), e); } }
From source file:org.generationcp.middleware.dao.dms.DmsProjectDao.java
License:Open Source License
/*** * Count calculated traits in the speficified datasets. * @param projectIds//from w w w. j a va 2 s.co m * @return */ public int countCalculatedVariablesInDatasets(final Set<Integer> projectIds) { // Check if the variable is used in trial level and/or environment level of studies except for the specified programUUID. final SQLQuery query = this.getSession().createSQLQuery(COUNT_CALCULATED_VARIABLES_IN_DATASETS); query.setParameterList("projectIds", projectIds); return ((BigInteger) query.uniqueResult()).intValue(); }
From source file:org.generationcp.middleware.dao.dms.DmsProjectDao.java
License:Open Source License
public List<MeasurementVariable> getObservationSetVariables(final List<Integer> observationSetIds, final List<Integer> variableTypes) { try {/*from w ww. ja v a 2 s . c o m*/ final String query = " SELECT distinct " // + " pp.variable_id AS " + OBS_SET_VARIABLE_ID + ", " // + " variable.name AS " + OBS_SET_VARIABLE_NAME + ", " // + " variable.definition AS " + OBS_SET_DESCRIPTION + ", " // + " pp.alias AS " + OBS_SET_ALIAS + ", " // + " pp.value as " + OBS_SET_VALUE + ", " + " variableType.cvterm_id AS " + OBS_SET_VARIABLE_TYPE_ID + ", " // + " scale.name AS " + OBS_SET_SCALE + ", " // + " method.name AS " + OBS_SET_METHOD + ", " // + " property.name AS " + OBS_SET_PROPERTY + ", " // + " dataType.cvterm_id AS " + OBS_SET_DATA_TYPE_ID + ", " // + " category.cvterm_id AS " + OBS_SET_CATEGORY_ID + ", " // + " category.name AS " + OBS_SET_CATEGORY_NAME + ", " // + " category.definition AS " + OBS_SET_CATEGORY_DESCRIPTION + ", " // + " (SELECT formula_id FROM formula WHERE target_variable_id = pp.variable_id and active = 1 LIMIT 1) AS " + OBS_SET_FORMULA_ID + ", " + " scaleMinRange.value AS " + OBS_SET_SCALE_MIN_RANGE + ", " // + " scaleMaxRange.value AS " + OBS_SET_SCALE_MAX_RANGE + ", " // + " vo.expected_min AS " + OBS_SET_EXPECTED_MIN + ", " // + " vo.expected_max AS " + OBS_SET_EXPECTED_MAX + ", " // + " cropOntology.value AS " + OBS_SET_CROP_ONTOLOGY_ID + "," + " pp.value as " + OBS_SET_VARIABLE_VALUE + " FROM project dataset " // + " INNER JOIN projectprop pp ON dataset.project_id = pp.project_id " // + " INNER JOIN cvterm variable ON pp.variable_id = variable.cvterm_id " // + " INNER JOIN cvterm variableType ON pp.type_id = variableType.cvterm_id " // + " INNER JOIN cvterm_relationship cvtrscale ON variable.cvterm_id = cvtrscale.subject_id " // + " AND cvtrscale.type_id = " + TermId.HAS_SCALE.getId() // + " INNER JOIN cvterm scale ON cvtrscale.object_id = scale.cvterm_id " // + " INNER JOIN cvterm_relationship cvtrmethod ON variable.cvterm_id = cvtrmethod.subject_id " // + " AND cvtrmethod.type_id = " + TermId.HAS_METHOD.getId() // + " INNER JOIN cvterm method ON cvtrmethod.object_id = method.cvterm_id " // + " INNER JOIN cvterm_relationship cvtrproperty ON variable.cvterm_id = cvtrproperty.subject_id " // + " AND cvtrproperty.type_id = " + TermId.HAS_PROPERTY.getId() // + " INNER JOIN cvterm property ON cvtrproperty.object_id = property.cvterm_id " // + " INNER JOIN cvterm_relationship cvtrdataType ON scale.cvterm_id = cvtrdataType.subject_id " // + " AND cvtrdataType.type_id = " + TermId.HAS_TYPE.getId() // + " INNER JOIN cvterm dataType ON cvtrdataType.object_id = dataType.cvterm_id " // + " LEFT JOIN cvterm_relationship cvtrcategory ON scale.cvterm_id = cvtrcategory.subject_id " + " AND cvtrcategory.type_id = " + TermId.HAS_VALUE.getId() // + " LEFT JOIN cvterm category ON cvtrcategory.object_id = category.cvterm_id " // + " LEFT JOIN cvtermprop scaleMaxRange on scale.cvterm_id = scaleMaxRange.cvterm_id " // + " AND scaleMaxRange.type_id = " + TermId.MAX_VALUE.getId() // + " LEFT JOIN cvtermprop scaleMinRange on scale.cvterm_id = scaleMinRange.cvterm_id " // + " AND scaleMinRange.type_id = " + TermId.MIN_VALUE.getId() // + " LEFT JOIN variable_overrides vo ON variable.cvterm_id = vo.cvterm_id " // + " AND dataset.program_uuid = vo.program_uuid " // + " LEFT JOIN cvtermprop cropOntology ON cropOntology.cvterm_id = variable.cvterm_id" // + " AND cropOntology.type_id = " + TermId.CROP_ONTOLOGY_ID.getId() + " WHERE " // + " dataset.project_id in (:observationSetIds) " // + " AND pp.type_id in (:variableTypes) " + " ORDER BY pp.rank "; final SQLQuery sqlQuery = this.getSession().createSQLQuery(query); sqlQuery.setParameterList("observationSetIds", observationSetIds); sqlQuery.setParameterList("variableTypes", variableTypes); sqlQuery.addScalar(OBS_SET_VARIABLE_ID).addScalar(OBS_SET_VARIABLE_NAME).addScalar(OBS_SET_DESCRIPTION) .addScalar(OBS_SET_ALIAS).addScalar(OBS_SET_VALUE).addScalar(OBS_SET_VARIABLE_TYPE_ID) .addScalar(OBS_SET_SCALE).addScalar(OBS_SET_METHOD).addScalar(OBS_SET_PROPERTY) .addScalar(OBS_SET_DATA_TYPE_ID).addScalar(OBS_SET_CATEGORY_ID).addScalar(OBS_SET_CATEGORY_NAME) .addScalar(OBS_SET_CATEGORY_DESCRIPTION).addScalar(OBS_SET_SCALE_MIN_RANGE, new DoubleType()) .addScalar(OBS_SET_SCALE_MAX_RANGE, new DoubleType()) .addScalar(OBS_SET_EXPECTED_MIN, new DoubleType()) .addScalar(OBS_SET_EXPECTED_MAX, new DoubleType()) .addScalar(OBS_SET_FORMULA_ID, new IntegerType()).addScalar(OBS_SET_CROP_ONTOLOGY_ID) .addScalar(OBS_SET_VARIABLE_VALUE); sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); final List<Map<String, Object>> results = sqlQuery.list(); final Map<Integer, MeasurementVariable> variables = new LinkedHashMap<>(); for (final Map<String, Object> result : results) { final Integer variableId = (Integer) result.get("variableId"); if (!variables.containsKey(variableId)) { variables.put(variableId, new MeasurementVariable()); final MeasurementVariable measurementVariable = variables.get(variableId); measurementVariable.setTermId(variableId); measurementVariable.setName((String) result.get(OBS_SET_VARIABLE_NAME)); measurementVariable.setAlias((String) result.get(OBS_SET_ALIAS)); measurementVariable.setValue((String) result.get(OBS_SET_VALUE)); measurementVariable.setDescription((String) result.get(OBS_SET_DESCRIPTION)); measurementVariable.setScale((String) result.get(OBS_SET_SCALE)); measurementVariable.setMethod((String) result.get(OBS_SET_METHOD)); measurementVariable.setProperty((String) result.get(OBS_SET_PROPERTY)); final VariableType variableType = VariableType .getById((Integer) result.get(OBS_SET_VARIABLE_TYPE_ID)); measurementVariable.setVariableType(variableType); //TODO: fix the saving of Treatment Factor Variables in the projectprop table. // Right now, the saved typeid is 1100. It should be 1809(VariableType.TREATMENT_FACTOR.getid()) if (variableType != null) { measurementVariable.setFactor(!variableType.getRole().equals(PhenotypicType.VARIATE)); } final DataType dataType = DataType.getById((Integer) result.get(OBS_SET_DATA_TYPE_ID)); measurementVariable.setDataType(dataType.getName()); measurementVariable.setDataTypeId(dataType.getId()); final Integer formulaId = (Integer) result.get(OBS_SET_FORMULA_ID); if (formulaId != null) { final Formula formula = (Formula) this.getSession().createCriteria(Formula.class) .add(Restrictions.eq("formulaId", formulaId)).add(Restrictions.eq("active", true)) .uniqueResult(); if (formula != null) { measurementVariable.setFormula(FormulaUtils.convertToFormulaDto(formula)); } } final Double scaleMinRange = (Double) result.get(OBS_SET_SCALE_MIN_RANGE); final Double scaleMaxRange = (Double) result.get(OBS_SET_SCALE_MAX_RANGE); final Double expectedMin = (Double) result.get(OBS_SET_EXPECTED_MIN); final Double expectedMax = (Double) result.get(OBS_SET_EXPECTED_MAX); measurementVariable.setMinRange(expectedMin != null ? expectedMin : scaleMinRange); measurementVariable.setMaxRange(expectedMax != null ? expectedMax : scaleMaxRange); measurementVariable.setScaleMinRange(scaleMinRange); measurementVariable.setScaleMaxRange(scaleMaxRange); measurementVariable.setVariableMinRange(expectedMin); measurementVariable.setVariableMaxRange(expectedMax); measurementVariable.setCropOntology((String) result.get(OBS_SET_CROP_ONTOLOGY_ID)); } final MeasurementVariable measurementVariable = variables.get(variableId); if (measurementVariable.getValue() == null || measurementVariable.getValue().isEmpty()) { measurementVariable.setValue((String) result.get(OBS_SET_VARIABLE_VALUE)); } final Object categoryId = result.get(OBS_SET_CATEGORY_ID); if (categoryId != null) { if (measurementVariable.getPossibleValues() == null || measurementVariable.getPossibleValues().isEmpty()) { measurementVariable.setPossibleValues(new ArrayList<ValueReference>()); } final ValueReference valueReference = // new ValueReference((Integer) categoryId, // Objects.toString(result.get(OBS_SET_CATEGORY_NAME)), // Objects.toString(result.get(OBS_SET_CATEGORY_DESCRIPTION))); if (!measurementVariable.getPossibleValues().contains(valueReference)) { measurementVariable.getPossibleValues().add(valueReference); } } } return new ArrayList<>(variables.values()); } catch (final HibernateException e) { throw new MiddlewareQueryException( "Error getting datasets variables for dataset=" + observationSetIds + ": " + e.getMessage(), e); } }
From source file:org.generationcp.middleware.dao.dms.ExperimentDao.java
License:Open Source License
public boolean checkIfAnyLocationIDsExistInExperiments(final int dataSetId, final List<Integer> locationIds) { try {/*from w ww.j a v a2s . com*/ final String sql = "SELECT count(*) FROM nd_experiment exp " + "WHERE exp.nd_geolocation_id in (:locationIds) " + "AND exp.project_id = :dataSetId "; final SQLQuery query = this.getSession().createSQLQuery(sql); query.setParameterList("locationIds", locationIds); query.setParameter("dataSetId", dataSetId); long count = 0L; final Object obj = query.uniqueResult(); if (obj != null) { count = ((Number) obj).longValue(); } return count != 0; } catch (final HibernateException e) { final String message = "Error at checkIfLocationIDsExistInExperiments=" + locationIds + "," + dataSetId + "," + " query at ExperimentDao: " + e.getMessage(); ExperimentDao.LOG.error(message, e); throw new MiddlewareQueryException(message, e); } }
From source file:org.generationcp.middleware.dao.dms.ExperimentDao.java
License:Open Source License
public boolean areAllInstancesExistInDataset(final int datasetId, final Set<Integer> instanceIds) { final StringBuilder sql = new StringBuilder(); sql.append("SELECT COUNT(DISTINCT e.nd_geolocation_id) FROM nd_experiment e ") .append(" WHERE e.project_id = :datasetId and e.nd_geolocation_id in (:instanceIds)"); try {// ww w.j av a 2s. c o m final SQLQuery query = this.getSession().createSQLQuery(sql.toString()); query.setParameter("datasetId", datasetId); query.setParameterList("instanceIds", instanceIds); final BigInteger count = (BigInteger) query.uniqueResult(); return count.intValue() == instanceIds.size(); } catch (final HibernateException e) { final String error = "Error at areAllInstancesExistInDataset=" + datasetId + "," + instanceIds + " query at ExperimentDao: " + e.getMessage(); ExperimentDao.LOG.error(error); throw new MiddlewareQueryException(error, e); } }