List of usage examples for org.hibernate SQLQuery setInteger
@Deprecated @SuppressWarnings("unchecked") default Query<R> setInteger(int position, int val)
From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
private void processCSL() { markForProcessing(BRS_TRANSACTION_TYPE_BANK); final List<AutoReconcileBean> CSLList = getStatmentsForProcessing(BRS_TRANSACTION_TYPE_BANK); final Long instrumentTypeId = getInstrumentType(FinancialConstants.INSTRUMENT_TYPE_BANK_TO_BANK); final String recociliationQuery = "update EGF_InstrumentHeader set id_status=:statusId, lastmodifiedby=:userId,lastmodifieddate=CURRENT_DATE" + " where id = (select ih.id from egf_instrumentheader ih,egf_instrumentvoucher iv,voucherheader vh where " + " instrumentAmount=:amount and bankaccountid=:accountId and ispaycheque=:ispaycheque and instrumentType in (" + instrumentTypeId + ")" + " and id_status=(select id from Egw_Status where upper(moduletype)=upper('instrument') and upper(description)=" + " upper(:instrumentStatus)) and iv.instrumentheaderid=ih.id and iv.voucherheaderid=ih.id and vh.vouchernumber=:cslNo ) "; final String recociliationAmountQuery = "update egf_instrumentOtherdetails set reconciledamount=:amount,instrumentstatusdate=:txDate " + " ,lastmodifiedby=:userId,lastmodifieddate=CURRENT_DATE,reconciledOn=:reconciliationDate " + " where instrumentheaderid = (select ih.id from egf_instrumentheader ih,egf_instrumentvoucher iv,voucherheader vh where " + " instrumentAmount=:amount and bankaccountid=:accountId and ispaycheque=:ispaycheque and instrumentType in (" + instrumentTypeId + ")" + " and id_status=(select id from Egw_Status where upper(moduletype)=upper('instrument') and upper(description)=" + " upper(:instrumentStatus)) and iv.instrumentheaderid=ih.id and iv.voucherheaderid=ih.id and vh.vouchernumber=:cslNo ) "; final SQLQuery updateQuery = persistenceService.getSession().createSQLQuery(recociliationQuery); final SQLQuery updateQuery2 = persistenceService.getSession().createSQLQuery(recociliationAmountQuery); final String backUpdateBankStmtquery = "update " + TABLENAME + " set action='" + BRS_ACTION_PROCESSED + "' ,reconciliationDate=:reconciliationDate where id=:id"; final String backUpdateFailureBRSquery = "update " + TABLENAME + " set action='" + BRS_ACTION_TO_BE_PROCESSED_MANUALLY + "',errormessage=:e where id=:id"; final SQLQuery backupdateQuery = persistenceService.getSession().createSQLQuery(backUpdateBankStmtquery); final SQLQuery backupdateFailureQuery = persistenceService.getSession() .createSQLQuery(backUpdateFailureBRSquery); for (final AutoReconcileBean bean : CSLList) { int updated = -1; try {//w w w .j av a2 s . c om updateQuery.setLong("statusId", statusId); updateQuery.setLong("accountId", accountId); updateQuery.setString("cslNo", bean.getCSLno()); updateQuery.setInteger("userId", ApplicationThreadLocals.getUserId().intValue()); updateQuery2.setDate("txDate", bean.getTxDate()); updateQuery2.setDate("reconciliationDate", reconciliationDate); updateQuery2.setLong("accountId", accountId); updateQuery2.setString("cslNo", bean.getCSLno()); updateQuery2.setInteger("userId", ApplicationThreadLocals.getUserId().intValue()); if (bean.getDebit() != null && bean.getDebit().compareTo(BigDecimal.ZERO) != 0) { updateQuery.setBigDecimal("amount", bean.getDebit()); updateQuery.setCharacter("ispaycheque", '1'); updateQuery.setString("instrumentStatus", FinancialConstants.INSTRUMENT_CREATED_STATUS); updated = updateQuery.executeUpdate(); if (updated != 0) { updateQuery2.setBigDecimal("amount", bean.getDebit()); updateQuery2.setCharacter("ispaycheque", '1'); updateQuery2.setString("instrumentStatus", FinancialConstants.INSTRUMENT_RECONCILED_STATUS); updated = updateQuery2.executeUpdate(); } } else { updateQuery.setBigDecimal("amount", bean.getCredit()); updateQuery.setCharacter("ispaycheque", '1'); updateQuery.setString("instrumentStatus", FinancialConstants.INSTRUMENT_CREATED_STATUS); updated = updateQuery.executeUpdate(); if (updated != 0) { updateQuery2.setBigDecimal("amount", bean.getCredit()); updateQuery2.setCharacter("ispaycheque", '1'); updateQuery2.setString("instrumentStatus", FinancialConstants.INSTRUMENT_RECONCILED_STATUS); updated = updateQuery2.executeUpdate(); } if (updated == 0) { } } // if updated is 0 means nothing got updated means could not find matching row in instrumentheader if (updated == 0) { backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.setString("e", DID_NOT_FIND_MATCH_IN_BANKBOOK); backupdateFailureQuery.executeUpdate(); } else if (updated == -1) { backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.setString("e", DID_NOT_FIND_MATCH_IN_BANKBOOK); backupdateFailureQuery.executeUpdate(); // if(LOGGER.isDebugEnabled()) LOGGER.debug(count); } else { backupdateQuery.setLong("id", bean.getId()); backupdateQuery.setDate("reconciliationDate", reconciliationDate); backupdateQuery.executeUpdate(); count++; // if(LOGGER.isDebugEnabled()) LOGGER.debug(count); } rowCount++; if (LOGGER.isDebugEnabled()) LOGGER.debug("out of " + rowCount + "==>succesfull " + count); if (rowCount % 20 == 0) persistenceService.getSession().flush(); // These exception might be because the other entires in instrument which is not in egf_brs_bankstatements // so any issues leave it for manual update } catch (final HibernateException e) { if (e.getCause().getMessage().contains("single-row subquery returns more")) backupdateFailureQuery.setString("e", BRS_MESSAGE_MORE_THAN_ONE_MATCH); else backupdateFailureQuery.setString("e", e.getMessage()); backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.executeUpdate(); } catch (final Exception e) { backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.setString("e", e.getMessage()); backupdateFailureQuery.executeUpdate(); } } }
From source file:org.egov.egf.web.actions.budget.BudgetProposalDetailAction.java
License:Open Source License
@Action(value = "/budget/budgetProposalDetail-ajaxLoadFunctions") public String ajaxLoadFunctions() { final String functionLists = "functionList"; if (getBudgetDetail() != null && getBudgetDetail().getBudget() != null && getBudgetDetail().getExecutingDepartment() != null) { final Budget budget = budgetService.find("from Budget where id=?", getBudgetDetail().getBudget().getId()); final String budgetName = budget.getName(); final Integer deptId = getBudgetDetail().getExecutingDepartment().getId().intValue(); String accountType;//from w w w . ja v a 2s . com accountType = budgetDetailHelper.accountTypeForFunctionDeptMap(budgetName); final String sqlStr = "select distinct (f.name) as name,f.id as id from eg_dept_functionmap m,function f where departmentid=:deptId" + " and budgetaccount_Type=:accountType and f.id= m.functionid order by f.name"; final SQLQuery sqlQuery = persistenceService.getSession().createSQLQuery(sqlStr); sqlQuery.setInteger("deptId", deptId).setString("accountType", accountType); sqlQuery.addScalar(NAME).addScalar("id", LongType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(CFunction.class)); if (!sqlQuery.list().isEmpty()) functionList = sqlQuery.list(); else functionList = employeeService.getAllFunctions(); if (functionList.isEmpty()) dropdownData.put(functionLists, functionService.findAll()); else dropdownData.put(functionLists, functionList); } return FUNCTION; }
From source file:org.egov.egf.web.actions.report.TrialBalanceAction.java
License:Open Source License
private void gererateReportForAsOnDate() { String voucherMisTable = ""; String misClause = ""; String misDeptCond = ""; String tsDeptCond = ""; String functionaryCond = ""; String tsfunctionaryCond = ""; String functionIdCond = ""; String tsFunctionIdCond = ""; String fieldIdCond = ""; String tsFieldIdCond = ""; String fundcondition = ""; List<TrialBalanceBean> forAllFunds = new ArrayList<TrialBalanceBean>(); if (rb.getFundId() != null) fundcondition = " and fundid=:fundId"; else// w ww. j a va 2 s .co m fundcondition = " and fundid in (select id from fund where isactive=true and isnotleaf!=true )"; // if(LOGGER.isInfoEnabled()) LOGGER.info("fund cond query "+fundcondition); if (null != rb.getDepartmentId() || null != rb.getFunctionaryId()) { voucherMisTable = ",vouchermis mis "; misClause = " and mis.voucherheaderid=vh.id "; } if (null != rb.getDepartmentId()) { misDeptCond = " and mis.DEPARTMENTID= :departmentId"; tsDeptCond = " and DEPARTMENTID= :departmentId"; } if (null != rb.getFunctionaryId()) { functionaryCond = " and mis.FUNCTIONARYID= :functionaryId"; tsfunctionaryCond = " and FUNCTIONARYID= :functionaryId"; } if (null != rb.getFunctionId()) { functionIdCond = " and gl.voucherheaderid in (select distinct(voucherheaderid) from generalledger where functionid =:functionId)"; tsFunctionIdCond = " and FUNCTIONID= functionId"; } if (null != rb.getDivisionId()) { fieldIdCond = " and mis.divisionId= :divisionId"; tsFieldIdCond = " and divisionId= :divisionId"; } String defaultStatusExclude = null; final List<AppConfigValues> listAppConfVal = appConfigValuesService.getConfigValuesByModuleAndKey("EGF", "statusexcludeReport"); if (null != listAppConfVal) defaultStatusExclude = listAppConfVal.get(0).getValue(); else throw new ApplicationRuntimeException("Exlcude statusses not are not defined for Reports"); final String query = " SELECT gl.glcode AS \"accCode\" ,coa.name AS \"accName\" ,vh.fundid AS \"fundId\",(SUM(debitamount)+SUM((SELECT case when SUM(OPENINGDEBITBALANCE) is null then 0 else SUM(OPENINGDEBITBALANCE) end FROM transactionsummary WHERE" + " financialyearid=(SELECT id FROM financialyear WHERE startingdate<=:toDate AND endingdate>=:toDate)" + " AND glcodeid =(SELECT id FROM chartofaccounts WHERE glcode=gl.glcode) AND fundid=vh.fundid" + fundcondition + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond + "))/COUNT(*))-" + " (SUM(creditamount)+SUM((SELECT case when SUM(OPENINGCREDITBALANCE) is null then 0 else SUM(OPENINGCREDITBALANCE) end FROM" + " transactionsummary WHERE financialyearid=(SELECT id FROM financialyear WHERE startingdate<=:toDate AND endingdate>=:toDate)" + " AND glcodeid =(SELECT id FROM chartofaccounts WHERE glcode=gl.glcode) AND fundid=vh.fundid" + fundcondition + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond + "))/COUNT(*) ) as \"amount\" " + " FROM generalledger gl,chartofaccounts coa,voucherheader vh " + voucherMisTable + " WHERE coa.glcode=gl.glcode AND gl.voucherheaderid=vh.id" + misClause + " AND vh.status not in (" + defaultStatusExclude + ") " + " AND vh.voucherdate<=:toDate AND vh.voucherdate>=(SELECT startingdate FROM financialyear WHERE startingdate<=:toDate AND endingdate>=:toDate) " + fundcondition + " " + misDeptCond + functionaryCond + functionIdCond + fieldIdCond + " GROUP BY gl.glcode,coa.name,vh.fundid HAVING (SUM(debitamount)>0 OR SUM(creditamount)>0) And" + " (SUM(debitamount)+SUM((SELECT case when SUM(OPENINGDEBITBALANCE) IS NULL then 0 else SUM(OPENINGDEBITBALANCE) end FROM" + " transactionsummary WHERE financialyearid=(SELECT id FROM financialyear WHERE startingdate <=:toDate" + " AND endingdate >=:toDate) AND glcodeid =(SELECT id FROM chartofaccounts WHERE glcode=gl.glcode) " + fundcondition + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond + "))/COUNT(*))-" + " (SUM(creditamount)+SUM((SELECT case when SUM(OPENINGCREDITBALANCE) IS NULL then 0 else SUM(OPENINGCREDITBALANCE) end FROM" + " transactionsummary WHERE financialyearid=(SELECT id FROM financialyear WHERE startingdate<=:toDate AND endingdate>=:toDate) " + " AND glcodeid =(SELECT id FROM chartofaccounts WHERE glcode=gl.glcode) " + fundcondition + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond + "))/COUNT(*) )<>0" + " union" + " SELECT coa.glcode AS \"accCode\" ,coa.name AS \"accName\" , fu.id as \"fundId\", SUM((SELECT case when SUM(OPENINGDEBITBALANCE) IS NULL then 0 else SUM(OPENINGDEBITBALANCE) end " + " FROM transactionsummary WHERE financialyearid=(SELECT id FROM financialyear WHERE startingdate<=:toDate AND endingdate>=:toDate)" + " AND glcodeid =(SELECT id FROM chartofaccounts WHERE glcode=coa.glcode) AND fundid= (select id from fund where id=fu.id)" + " " + fundcondition + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond + ")) - SUM((SELECT case when SUM(OPENINGCREDITBALANCE) IS NULL then 0 else SUM(OPENINGCREDITBALANCE) end as \"amount\" FROM transactionsummary WHERE" + " financialyearid=(SELECT id FROM financialyear WHERE startingdate<=:toDate AND endingdate>=:toDate) AND glcodeid =(SELECT id FROM chartofaccounts" + " WHERE glcode=coa.glcode)AND fundid= (select id from fund where id=fu.id)" + fundcondition + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond + ")) " + " FROM chartofaccounts coa, fund fu WHERE fu.id IN(SELECT fundid from transactionsummary WHERE financialyearid = (SELECT id FROM financialyear WHERE startingdate<=:toDate " + " AND endingdate>=:toDate) " + fundcondition + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond + " AND glcodeid =(SELECT id FROM chartofaccounts WHERE glcode=coa.glcode) ) AND coa.id NOT IN(SELECT glcodeid FROM generalledger gl,voucherheader vh " + voucherMisTable + " WHERE " + " vh.status not in (" + defaultStatusExclude + ") " + misClause + misDeptCond + functionaryCond + functionIdCond + fieldIdCond + " AND vh.id=gl.voucherheaderid AND vh.fundid=fu.id AND vh.voucherdate<=:toDate AND vh.voucherdate>=(SELECT startingdate FROM financialyear WHERE startingdate<=:toDate AND endingdate>=:toDate) " + fundcondition + ")" + " GROUP BY coa.glcode,coa.name, fu.id" + " HAVING((SUM((SELECT case when SUM(OPENINGDEBITBALANCE) IS NULL then 0 else SUM(OPENINGDEBITBALANCE) end FROM transactionsummary WHERE" + " financialyearid=(SELECT id FROM financialyear WHERE startingdate<=:toDate AND endingdate>=:toDate) AND glcodeid =(SELECT id FROM chartofaccounts WHERE glcode=coa.glcode) " + fundcondition + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond + " )) >0 )" + " OR (SUM((SELECT case when SUM(OPENINGCREDITBALANCE) IS NULL then 0 else SUM(OPENINGCREDITBALANCE) end FROM transactionsummary WHERE financialyearid=(SELECT id FROM financialyear WHERE startingdate<=:toDate AND endingdate>=:toDate)" + " AND glcodeid =(SELECT id FROM chartofaccounts WHERE glcode=coa.glcode) " + fundcondition + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond + "))>0 )) ORDER BY \"accCode\""; if (LOGGER.isDebugEnabled()) LOGGER.debug("&&&query " + query); try { new Double(0); final SQLQuery SQLQuery = persistenceService.getSession().createSQLQuery(query); SQLQuery.addScalar("accCode").addScalar("accName").addScalar("fundId", StringType.INSTANCE) .addScalar("amount", BigDecimalType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(TrialBalanceBean.class)); if (null != rb.getFundId()) SQLQuery.setInteger("fundId", rb.getFundId()); if (null != rb.getDepartmentId()) SQLQuery.setInteger("departmentId", rb.getDepartmentId()); if (null != rb.getFunctionaryId()) SQLQuery.setInteger("functionaryId", rb.getFunctionaryId()); if (null != rb.getFunctionId()) SQLQuery.setInteger("functionId", rb.getFunctionId()); if (null != rb.getDivisionId()) SQLQuery.setInteger("divisionId", rb.getDivisionId()); if (null != rb.getFromDate()) SQLQuery.setDate("fromDate", rb.getFromDate()); SQLQuery.setDate("toDate", rb.getToDate()); if (LOGGER.isInfoEnabled()) LOGGER.info("query ---->" + SQLQuery); forAllFunds = SQLQuery.list(); } catch (final Exception e) { LOGGER.error("Error in getReport" + e.getMessage(), e); } for (final Fund f : fundList) fundWiseTotalMap.put(f.getId() + "_amount", BigDecimal.ZERO); // List<> try { final Map<String, TrialBalanceBean> nonDuplicateMap = new LinkedHashMap<String, TrialBalanceBean>(); for (final TrialBalanceBean tb : forAllFunds) if (nonDuplicateMap.containsKey(tb.getAccCode())) { // tb1=nonDuplicateMap.get(tb.getAccCode()); if (tb.getAmount().signum() == -1) { nonDuplicateMap.get(tb.getAccCode()).addToAmountMap(tb.getFundId() + "_amount", numberToString(tb.getAmount().abs().toString()).toString() + " Cr"); if (nonDuplicateMap.get(tb.getAccCode()).getCreditAmount() != null) nonDuplicateMap.get(tb.getAccCode()).setCreditAmount( nonDuplicateMap.get(tb.getAccCode()).getCreditAmount().add(tb.getAmount())); else nonDuplicateMap.get(tb.getAccCode()).setCreditAmount(tb.getAmount()); totalAmount = fundWiseTotalMap.get(tb.getFundId() + "_amount") .subtract(tb.getAmount().abs()); fundWiseTotalMap.put(tb.getFundId() + "_amount", totalAmount); } else if (tb.getAmount().signum() == 1) { nonDuplicateMap.get(tb.getAccCode()).addToAmountMap(tb.getFundId() + "_amount", numberToString(tb.getAmount().toString()).toString() + " Dr"); if (nonDuplicateMap.get(tb.getAccCode()).getDebitAmount() != null) nonDuplicateMap.get(tb.getAccCode()).setDebitAmount( nonDuplicateMap.get(tb.getAccCode()).getDebitAmount().add(tb.getAmount())); else nonDuplicateMap.get(tb.getAccCode()).setDebitAmount(tb.getAmount()); totalAmount = fundWiseTotalMap.get(tb.getFundId() + "_amount").add(tb.getAmount()); fundWiseTotalMap.put(tb.getFundId() + "_amount", totalAmount); } } else { if (tb.getAmount().signum() == -1) { tb.addToAmountMap(tb.getFundId() + "_amount", numberToString(tb.getAmount().abs().toString()).toString() + " Cr"); tb.setCreditAmount(tb.getAmount()); totalAmount = fundWiseTotalMap.get(tb.getFundId() + "_amount") .subtract(tb.getAmount().abs()); fundWiseTotalMap.put(tb.getFundId() + "_amount", totalAmount); } else if (tb.getAmount().signum() == 1) { tb.addToAmountMap(tb.getFundId() + "_amount", numberToString(tb.getAmount().toString()).toString() + " Dr"); tb.setDebitAmount(tb.getAmount()); totalAmount = fundWiseTotalMap.get(tb.getFundId() + "_amount").add(tb.getAmount()); fundWiseTotalMap.put(tb.getFundId() + "_amount", totalAmount); } nonDuplicateMap.put(tb.getAccCode(), tb); } final Collection<TrialBalanceBean> values = nonDuplicateMap.values(); for (final TrialBalanceBean tb : values) { if (tb.getDebitAmount() != null) tb.setDebit(numberToString(tb.getDebitAmount().toString()).toString() + " Dr"); else tb.setDebit("0.00"); if (tb.getCreditAmount() != null) tb.setCredit(numberToString(tb.getCreditAmount().abs().toString()).toString() + " Cr"); else tb.setCredit("0.00"); if (LOGGER.isDebugEnabled()) LOGGER.debug(tb); if (tb.getDebitAmount() != null && tb.getCreditAmount() != null) { final BigDecimal add = tb.getDebitAmount().subtract(tb.getCreditAmount().abs()); totalCreditAmount = totalCreditAmount.add(add); if (add.signum() == -1) tb.setAmount1(numberToString(add.abs().toString()) + " Cr"); else tb.setAmount1(numberToString(add.toString()) + " Dr"); } else if (tb.getDebitAmount() != null) tb.setAmount1(numberToString(tb.getDebitAmount().toString()) + " Dr"); else if (tb.getCreditAmount() != null) tb.setAmount1(numberToString(tb.getCreditAmount().abs().toString()) + " Cr"); else tb.setAmount1("0.00"); } al.addAll(values); /* * for(TrialBalanceBean c:al) { if(LOGGER.isInfoEnabled()) LOGGER.info("Items Before Sorting"+c); } */ Collections.sort(al, new COAcomparator()); /* * for(TrialBalanceBean c:al) { if(LOGGER.isInfoEnabled()) LOGGER.info("Items After Sorting"+c); } */ final TrialBalanceBean tbTotal = new TrialBalanceBean(); tbTotal.setAccCode("Total"); for (final String key : fundWiseTotalMap.keySet()) { String totalStr = "0.0"; final BigDecimal total = fundWiseTotalMap.get(key); if (total != null && total.signum() == -1) totalStr = numberToString(total.abs().toString()) + " Cr"; else if (total != null && total.signum() == 1) totalStr = numberToString(total.toString()) + " Dr"; tbTotal.addToAmountMap(key, totalStr); if (totalCreditAmount != null && totalCreditAmount.signum() == -1) totalStr = numberToString(total.abs().toString()) + " Cr"; else if (totalCreditAmount != null && totalCreditAmount.signum() == 1) totalStr = numberToString(total.toString()) + " Dr"; tbTotal.setAmount1(totalStr); } al.add(tbTotal); } catch (final Exception e) { } }
From source file:org.generationcp.middleware.dao.gdms.AlleleValuesDAO.java
License:Open Source License
@SuppressWarnings("unchecked") public List<Integer> getGidsByMarkersAndAlleleValues(List<Integer> markerIdList, List<String> alleleValueList) throws MiddlewareQueryException { List<Integer> values = new ArrayList<Integer>(); if (markerIdList.isEmpty() || alleleValueList.isEmpty()) { throw new MiddlewareQueryException("markerIdList and alleleValueList must not be empty"); }//from w ww. ja v a 2 s. c o m if (markerIdList.size() != alleleValueList.size()) { throw new MiddlewareQueryException("markerIdList and alleleValueList must have the same size"); } List<String> placeholderList = new ArrayList<String>(); for (int i = 0; i < markerIdList.size(); i++) { placeholderList.add("(?,?)"); } String placeholders = StringUtil.joinIgnoreNull(",", placeholderList); String sql = new StringBuffer().append("SELECT gid ").append("FROM gdms_allele_values ") .append("WHERE (marker_id, allele_bin_value) IN (" + placeholders + ") ").toString(); try { SQLQuery query = this.getSession().createSQLQuery(sql); for (int i = 0; i < markerIdList.size(); i++) { int baseIndex = i * 2; query.setInteger(baseIndex, markerIdList.get(i)); query.setString(baseIndex + 1, alleleValueList.get(i)); } values = query.list(); } catch (HibernateException e) { this.logAndThrowException("Error with getGidsByMarkersAndAlleleValues(markerIdList=" + markerIdList + ", alleleValueList=" + alleleValueList + "): " + e.getMessage(), e); } return values; }
From source file:org.generationcp.middleware.dao.gdms.MapDAO.java
License:Open Source License
public List<MapInfo> getMapInfoByMapAndChromosome(final Integer mapId, final String chromosome) { final SQLQuery query = this.getSession().createSQLQuery(MapDAO.GET_MAP_INFO_BY_MAP_AND_CHROMOSOME); query.setInteger("mapId", mapId); query.setString("chromosome", chromosome); query.addScalar("marker_id", new IntegerType()); query.addScalar("marker_name", new StringType()); query.addScalar("map_name", new StringType()); query.addScalar("map_type", new StringType()); query.addScalar("start_position", new FloatType()); query.addScalar("linkage_group", new StringType()); query.addScalar("map_unit", new StringType()); final List<MapInfo> mapInfoList = new ArrayList<MapInfo>(); try {/* ww w .j a v a2 s.com*/ @SuppressWarnings("rawtypes") final List results = query.list(); for (final Object o : results) { final Object[] result = (Object[]) o; if (result != null) { final Integer markerId = (Integer) result[0]; final String markerName = (String) result[1]; final String mapName = (String) result[2]; final String mapType = (String) result[3]; final Float startPosition = (Float) result[4]; final String linkageGroup = (String) result[5]; final String mapUnit = (String) result[6]; final MapInfo mapInfo = new MapInfo(markerId, markerName, mapId, mapName, linkageGroup, startPosition, mapType, mapUnit); mapInfoList.add(mapInfo); } } } catch (final HibernateException e) { MapDAO.LOG.error(e.getMessage(), e); throw new MiddlewareQueryException("Error with getMapInfoByMapAndChromosome() query: " + e.getMessage(), e); } return mapInfoList; }
From source file:org.generationcp.middleware.dao.gdms.MapDAO.java
License:Open Source License
public List<MapInfo> getMapInfoByMapChromosomeAndPosition(final Integer mapId, final String chromosome, final Float startPosition) { final SQLQuery query = this.getSession().createSQLQuery(MapDAO.GET_MAP_INFO_BY_MAP_CHROMOSOME_AND_POSITION); query.setInteger("mapId", mapId); query.setString("chromosome", chromosome); query.setFloat("startPosition", startPosition); query.addScalar("marker_id", new IntegerType()); query.addScalar("marker_name", new StringType()); query.addScalar("map_name", new StringType()); query.addScalar("map_type", new StringType()); query.addScalar("linkage_group", new StringType()); query.addScalar("map_unit", new StringType()); final List<MapInfo> mapInfoList = new ArrayList<MapInfo>(); try {// w w w . j a va2s . c om @SuppressWarnings("rawtypes") final List results = query.list(); for (final Object o : results) { final Object[] result = (Object[]) o; if (result != null) { final Integer markerId = (Integer) result[0]; final String markerName = (String) result[1]; final String mapName = (String) result[2]; final String mapType = (String) result[3]; final String linkageGroup = (String) result[4]; final String mapUnit = (String) result[5]; final MapInfo mapInfo = new MapInfo(markerId, markerName, mapId, mapName, linkageGroup, startPosition, mapType, mapUnit); mapInfoList.add(mapInfo); } } } catch (final HibernateException e) { MapDAO.LOG.error(e.getMessage(), e); throw new MiddlewareQueryException( "Error with getMapInfoByMapChromosomeAndPosition() query: " + e.getMessage(), e); } return mapInfoList; }
From source file:org.generationcp.middleware.dao.gdms.MapDAO.java
License:Open Source License
public List<MapInfo> getMapInfoByMarkersAndMap(final List<Integer> markers, final Integer mapId) { final SQLQuery query = this.getSession().createSQLQuery(MapDAO.GET_MAP_INFO_BY_MARKERS_AND_MAP); query.setParameterList("markerIdList", markers); query.setInteger("mapId", mapId); query.addScalar("marker_id", new IntegerType()); query.addScalar("marker_name", new StringType()); query.addScalar("map_name", new StringType()); query.addScalar("map_type", new StringType()); query.addScalar("start_position", new FloatType()); query.addScalar("linkage_group", new StringType()); query.addScalar("map_unit", new StringType()); final List<MapInfo> mapInfoList = new ArrayList<MapInfo>(); try {// www.j a v a 2 s . c o m @SuppressWarnings("rawtypes") final List results = query.list(); for (final Object o : results) { final Object[] result = (Object[]) o; if (result != null) { final Integer markerId = (Integer) result[0]; final String markerName = (String) result[1]; final String mapName = (String) result[2]; final String mapType = (String) result[3]; final Float startPosition = (Float) result[4]; final String linkageGroup = (String) result[5]; final String mapUnit = (String) result[6]; final MapInfo mapInfo = new MapInfo(markerId, markerName, mapId, mapName, linkageGroup, startPosition, mapType, mapUnit); mapInfoList.add(mapInfo); } } } catch (final HibernateException e) { MapDAO.LOG.error(e.getMessage(), e); throw new MiddlewareQueryException("Error with getMapInfoByMarkersAndMap() query: " + e.getMessage(), e); } return mapInfoList; }
From source file:org.generationcp.middleware.dao.oms.CVTermDao.java
License:Open Source License
public List<Integer> findMethodTermIdsByTrait(final Integer traitId) { try {/*w w w. j a v a 2 s . c om*/ // Standard variable has the combination of property-scale-method final StringBuilder queryString = new StringBuilder(); queryString.append("SELECT DISTINCT cvrm.object_id "); queryString.append("FROM cvterm_relationship cvr "); queryString.append( "INNER JOIN cvterm_relationship cvrp ON cvr.subject_id = cvrp.subject_id AND cvrp.type_id = 1200 "); queryString.append( "INNER JOIN cvterm_relationship cvrs ON cvr.subject_id = cvrs.subject_id AND cvrs.type_id = 1220 "); queryString.append( "INNER JOIN cvterm_relationship cvrm ON cvr.subject_id = cvrm.subject_id AND cvrm.type_id = 1210 "); queryString.append("WHERE cvrp.object_id = :traitId"); final SQLQuery query = this.getSession().createSQLQuery(queryString.toString()); query.setInteger("traitId", traitId); return query.list(); } catch (final HibernateException e) { this.logAndThrowException("Error at findMethodTermIdsByTrait :" + e.getMessage(), e); } return new ArrayList<>(); }
From source file:org.generationcp.middleware.dao.oms.CVTermDao.java
License:Open Source License
public List<Integer> findScaleTermIdsByTrait(final Integer traitId) { try {// ww w. jav a2s .c om // Standard variable has the combination of property-scale-method final StringBuilder queryString = new StringBuilder(); queryString.append("SELECT DISTINCT cvrs.object_id "); queryString.append("FROM cvterm_relationship cvr "); queryString.append( "INNER JOIN cvterm_relationship cvrp ON cvr.subject_id = cvrp.subject_id AND cvrp.type_id = 1200 "); queryString.append( "INNER JOIN cvterm_relationship cvrs ON cvr.subject_id = cvrs.subject_id AND cvrs.type_id = 1220 "); queryString.append( "INNER JOIN cvterm_relationship cvrm ON cvr.subject_id = cvrm.subject_id AND cvrm.type_id = 1210 "); queryString.append("WHERE cvrp.object_id = :traitId"); final SQLQuery query = this.getSession().createSQLQuery(queryString.toString()); query.setInteger("traitId", traitId); return query.list(); } catch (final HibernateException e) { this.logAndThrowException("Error at findScaleTermIdsByTrait :" + e.getMessage(), e); } return new ArrayList<>(); }
From source file:org.openbrr.collector.flossmole.sf.SfDataProcessor.java
@SuppressWarnings("unchecked") private <T extends ProjectAttribute> void processSfProjectAttributeData(Session _session, String _fileType, String _tableName, Class<T> _class, int _projectCodePos, int _attrNamePos) throws IOException { /*/*from ww w . ja va2 s .co m*/ * sfProjectInfo has the following fields; * proj_unixname, code, description, date_collected, datasource_id */ Map<String, Integer> attrNameIdMap = new HashMap<String, Integer>(); Map<Integer, List<Integer>> projectAttrIdsMap = new HashMap<Integer, List<Integer>>(); //track time Timestamp start = new Timestamp(System.currentTimeMillis()); System.out.println("\nProcessing " + _fileType + " data"); Transaction tx = _session.beginTransaction(); String projAttrData = getDataFile(_fileType); BufferedReader fr = null; String line = null; try { System.out.println("Querying: " + _class.getSimpleName()); List<ProjectAttribute> projAttrs = (List<ProjectAttribute>) _session .createQuery("from " + _class.getSimpleName()).list(); for (ProjectAttribute attr : projAttrs) { attrNameIdMap.put(attr.getName(), attr.getId()); } //read all the current data List<Object[]> data = _session.createSQLQuery("select * from " + _tableName).list(); for (Object[] rec : data) { Integer projId = (Integer) rec[0]; Integer attrId = (Integer) rec[1]; List<Integer> attrIdList = projectAttrIdsMap.get(projId); if (attrIdList == null) { attrIdList = new ArrayList<Integer>(); projectAttrIdsMap.put(projId, attrIdList); } attrIdList.add(attrId); } SQLQuery insertQuery = _session.createSQLQuery("insert into " + _tableName + " values(?, ?)"); //Timestamp now = new Timestamp(System.currentTimeMillis()); fr = new BufferedReader(new FileReader(new File(FlossmoleConstants.UNPROCESSED_FOLDER, projAttrData))); int recCount = 0; Constructor<T> attrConst = _class.getConstructor(String.class); while (fr.ready()) { try { line = fr.readLine(); if (!isValid(line)) { continue; } List<String> tokens = tokenize(line); //check for the right number of tokens if (tokens.size() < 5) { System.out.println( "Number of Tokens less than 3 in file: '" + _fileType + "'; line: " + line); continue; } Integer projectId = codeIdMap.get(tokens.get(_projectCodePos)); String attrName = tokens.get(_attrNamePos); if (projectId == null) { logger.debug("No Project found for code [" + tokens.get(_projectCodePos) + "] in sfRawLicenseData. skipping data.."); continue; } Integer attrId = attrNameIdMap.get(attrName); if (attrId == null) { ProjectAttribute attr = attrConst.newInstance(attrName); _session.save(attr); attrId = attr.getId(); attrNameIdMap.put(attrName, attrId); } List<Integer> attrIds = projectAttrIdsMap.get(projectId); if (attrIds == null) { attrIds = new ArrayList<Integer>(); projectAttrIdsMap.put(projectId, attrIds); } if (!attrIds.contains(attrId)) { insertQuery.setInteger(0, projectId).setInteger(1, attrId).executeUpdate(); attrIds.add(attrId); } //for testing recCount++; if (testRun && recCount > testRunCount) { break; } if (recCount % batchMarker == 0) { System.out.print("."); } } catch (Exception e) { System.out.println("Error Processing line: " + line); e.printStackTrace(); } } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } finally { if (fr != null) fr.close(); } tx.commit(); Timestamp end = new Timestamp(System.currentTimeMillis()); System.out.println("Time taken : " + (end.getTime() - start.getTime()) / 1000 + " sec"); }