List of usage examples for org.hibernate SQLQuery setInteger
@Deprecated @SuppressWarnings("unchecked") default Query<R> setInteger(int position, int val)
From source file:it.mdps.gestguide.database.dao.hibernate.MezzoDaoImpl.java
@SuppressWarnings("unchecked") @Override//from w w w . j av a 2s. c o m @Transactional(readOnly = true) public List<Mezzo> getAvailableVehicles(int schoolId, int licenseId, Date fromDate, Date toDate) { Session session = super.sessionFactory.getCurrentSession(); String sql = "select * FROM Mezzo WHERE id_autoscuola = :schoolId " + " AND id_mezzo in ( " + " SELECT a.id_mezzo FROM Abilitazione_Mezzo a, Mezzo m " // seleziona mezzi abilitati alla patente richiesta e appartenenti alla scuola + " WHERE m.id_autoscuola = :schoolId AND m.id_mezzo=a.id_mezzo " + " AND id_patente = :licenseId)" + " AND id_mezzo NOT IN (" + " SELECT id_mezzo FROM Prenotazione " // seleziona mezzi impegnati in quell'arco temporale + " WHERE id_autoscuola = :schoolId " + " AND (" + " (:fromDate BETWEEN data_inizio AND data_fine) " + " OR" + " (:toDate BETWEEN data_inizio AND data_fine)" + " OR" + " (:fromDate <= data_inizio AND :toDate >= data_fine)" + " )" + ")"; SQLQuery query = session.createSQLQuery(sql); query.addEntity(Mezzo.class); query.setInteger("schoolId", schoolId); query.setInteger("licenseId", licenseId); query.setDate("fromDate", fromDate); query.setDate("toDate", toDate); return query.list(); }
From source file:lt.emasina.resthub.server.factory.DataFactory.java
License:Open Source License
private SQLQuery getPagedSQLQuery(final Session session, final PagedHandler<?, ?> handler) throws SQLException { final Query q = handler.getQuery(); Integer perPage = handler.getPerPage(); Integer page = handler.getPage(); if (page == null || perPage == null) { perPage = q.getRowsLimit();/*from w w w .j a va2s.c o m*/ page = 1; } Integer startRow = perPage * (page - 1) + 1; if (handler instanceof LobHandler) { startRow = startRow + ((LobHandler) handler).getRow(); perPage = 1; } StringBuilder sb = new StringBuilder(); sb.append("select * from " + " (select ROWNUM ROW_NUMBER___, A.* from ("); sb.append(q.getSql()).append(") A").append(" where ROWNUM < (:").append(START_ROW_PARAM).append(" + :") .append(NUM_ROWS_PARAM).append(") ").append(") where ROW_NUMBER___ >= :").append(START_ROW_PARAM); String sql = sb.toString(); final SQLQuery query = session.createSQLQuery(sql); handler.applyParameters(query); query.setInteger(START_ROW_PARAM, startRow); query.setInteger(NUM_ROWS_PARAM, perPage); return query; }
From source file:model.manager.PatientManager.java
License:Open Source License
public static List<ArtDto> getIedeaArtData(Session session, Patient patient) { SQLQuery query = session.createSQLQuery( "select f.form as form, atc.code as code, min(pickupdate) as startdate, max(pickupdate) as enddate" + " from prescription sc, package p, packageddrugs pd, stock s, drug d, form f, atccode atc" + " where sc.id = p.prescription" + " and p.id = pd.parentpackage" + " and pd.stock = s.id" + " and s.drug = d.id" + " and d.form = f.id" + " and d.atccode_id = atc.id" + " and d.sidetreatment = 'F'" + " and sc.patient = :patid" + " and pickupdate is not null" + " group by f.form, atc.code"); query.setInteger("patid", patient.getId()); query.setResultTransformer(new AliasToBeanResultTransformer(ArtDto.class)); @SuppressWarnings("unchecked") List<ArtDto> list = query.list(); return list;//from w ww . j a v a 2 s .c o m }
From source file:models.Ledger.java
public void resetAll() { Session session;/*from w w w . ja v a 2 s. co m*/ session = DatabaseUtil.getSessionFactory().openSession(); Transaction tx = null; try { tx = session.beginTransaction(); String sql = "ALTER TABLE ledgers AUTO_INCREMENT = :id"; SQLQuery query = session.createSQLQuery(sql); query.setInteger("id", 1); System.out.println(query.executeUpdate()); session.flush(); tx.commit(); } catch (HibernateException ex) { System.out.println(ex.getMessage()); if (tx != null) { tx.rollback(); } } finally { session.close(); } }
From source file:models.TrialBalance.java
public void resetAll() { Session session;// w ww .j ava 2s. c om session = DatabaseUtil.getSessionFactory().openSession(); Transaction tx = null; try { tx = session.beginTransaction(); String sql = "ALTER TABLE trial_balances AUTO_INCREMENT = :id"; SQLQuery query = session.createSQLQuery(sql); query.setInteger("id", 1); System.out.println(query.executeUpdate()); session.flush(); tx.commit(); } catch (HibernateException ex) { System.out.println(ex.getMessage()); if (tx != null) { tx.rollback(); } } finally { session.close(); } }
From source file:mpimp.assemblxweb.db.util.AssemblXWebDBUtil.java
License:Open Source License
@SuppressWarnings("unchecked") public static Boolean isSessionValid(AssemblXWebModel model) throws AssemblXException { Session hibernateSession = null;// w w w.jav a2 s . co m try { hibernateSession = HibernateSessionFactory.getSession(); hibernateSession.beginTransaction(); String queryString = "select expire from j5session where user_id = :userId"; SQLQuery query = hibernateSession.createSQLQuery(queryString); query.setInteger("userId", model.getOperator().getOperatorId()); ArrayList<Timestamp> result = (ArrayList<Timestamp>) query.list(); hibernateSession.getTransaction().commit(); Timestamp expireTimestamp; if (result.size() == 1) { expireTimestamp = result.get(0); Calendar currentCalendar = Calendar.getInstance(); currentCalendar.setTime(new Date()); int compareResult = expireTimestamp.compareTo(currentCalendar.getTime()); if (compareResult > 0) { return true; } else { return false; } } else { return false; } } catch (Exception e) { // if (hibernateSession.getTransaction() != null && hibernateSession.getTransaction().isActive()) { // hibernateSession.getTransaction().rollback(); // } String message = "Error during j5 session check. " + e.getMessage(); logger_.error(message); throw new AssemblXException(message, AssemblXWebDBUtil.class); } finally { HibernateSessionFactory.closeSession(); } }
From source file:mpimp.assemblxweb.db.util.AssemblXWebDBUtil.java
License:Open Source License
public static void insertOrUpdateJ5Session(AssemblXWebModel model) throws Exception { Session hibernateSession = null;//from w w w. jav a 2s . c om try { hibernateSession = HibernateSessionFactory.getSession(); hibernateSession.beginTransaction(); String queryString = "select session_id from j5session where user_id = :userId"; SQLQuery query = hibernateSession.createSQLQuery(queryString); query.setInteger("userId", model.getOperator().getOperatorId()); ArrayList<String> result = (ArrayList<String>) query.list(); Boolean sessionIdPresent = false; if (result.size() == 1) { sessionIdPresent = true; } hibernateSession.getTransaction().commit(); // //////////// SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss"); Calendar currentCalendar = Calendar.getInstance(); currentCalendar.add(Calendar.HOUR_OF_DAY, 10); String dateString = dateFormat.format(currentCalendar.getTime()); if (sessionIdPresent == false) { // we will have to insert a session id try { hibernateSession = HibernateSessionFactory.getSession(); hibernateSession.beginTransaction(); } catch (Exception e) { // if (hibernateSession.getTransaction() != null && hibernateSession.getTransaction().isActive()) { // hibernateSession.getTransaction().rollback(); // } String message = "Error during update of j5 session data. " + e.getMessage(); logger_.error(message); throw new AssemblXException(message, AssemblXWebDBUtil.class); } queryString = "insert into j5session (session_id, expire, user_id)" + "values (:sessionId, :expire, :userId)"; query = hibernateSession.createSQLQuery(queryString); query.setString("sessionId", model.getJ5SessionId()); query.setString("expire", dateString); query.setInteger("userId", model.getOperator().getOperatorId()); if (query.executeUpdate() != 1) { // if (hibernateSession.getTransaction() != null && hibernateSession.getTransaction().isActive()) { // hibernateSession.getTransaction().rollback(); // } String message = "Insertion of new j5 session id failed!"; logger_.error(message); throw new AssemblXException(message, AssemblXWebDBUtil.class); } hibernateSession.getTransaction().commit(); } else { // we will have to update the session id try { hibernateSession = HibernateSessionFactory.getSession(); hibernateSession.beginTransaction(); } catch (Exception e) { // if (hibernateSession.getTransaction() != null && hibernateSession.getTransaction().isActive()) { // hibernateSession.getTransaction().rollback(); // } String message = "Error during update of j5 session data. " + e.getMessage(); logger_.error(message); throw new AssemblXException(message, AssemblXWebDBUtil.class); } queryString = "update j5session set session_id = :sessionId, expire = :expire " + "where user_id = :userId"; query = hibernateSession.createSQLQuery(queryString); query.setString("sessionId", model.getJ5SessionId()); query.setString("expire", dateString); query.setInteger("userId", model.getOperator().getOperatorId()); if (query.executeUpdate() != 1) { // if (hibernateSession.getTransaction() != null && hibernateSession.getTransaction().isActive()) { // hibernateSession.getTransaction().rollback(); // } String message = "Insertion of new j5 session id failed!"; logger_.error(message); throw new AssemblXException(message, AssemblXWebDBUtil.class); } hibernateSession.getTransaction().commit(); } } catch (Exception e) { // if (hibernateSession.getTransaction() != null && hibernateSession.getTransaction().isActive()) { // hibernateSession.getTransaction().rollback(); // } String message = "Error during update of j5 session data. " + e.getMessage(); logger_.error(message); throw new AssemblXException(message, AssemblXWebDBUtil.class); } finally { HibernateSessionFactory.closeSession(); } }
From source file:org.b3mn.poem.Structure.java
License:Open Source License
public static Structure instance(int owner_id, String hierarchy) { Structure structure = null;//from w w w .j av a2 s . c o m SQLQuery query = Persistance.getSession().createSQLQuery("select * from ensure_descendant(:hierarchy,:id)") .addEntity("structure", Structure.class); query.setString("hierarchy", hierarchy); query.setInteger("id", owner_id); try { structure = (Structure) query.uniqueResult(); } catch (Exception e) { e.printStackTrace(); } Persistance.commit(); return structure; }
From source file:org.egov.collection.service.CollectionReportHeadWiseService.java
License:Open Source License
public CollectionSummaryHeadWiseReportResult getCollectionSummaryReport(final Date fromDate, final Date toDate, final String paymentMode, final String source, final String glCode, final int status, final Integer branchId) { final SimpleDateFormat fromDateFormatter = new SimpleDateFormat("yyyy-MM-dd 00:00:00"); final SimpleDateFormat toDateFormatter = new SimpleDateFormat("yyyy-MM-dd 23:59:59"); final StringBuilder defaultQueryStr = new StringBuilder(500); final StringBuilder aggregateQueryStr = new StringBuilder(); StringBuilder rebateQueryStr = new StringBuilder(""); StringBuilder revenueHeadQueryStr = new StringBuilder(""); final StringBuilder selectQueryStr = new StringBuilder( "SELECT (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='cash' THEN count(distinct(EGCL_COLLECTIONHEADER.id)) END) AS cashCount, " + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='cheque' THEN count(distinct(EGCL_COLLECTIONHEADER.id)) WHEN EGF_INSTRUMENTTYPE.TYPE='dd' THEN count(distinct(EGCL_COLLECTIONHEADER.id)) END) AS chequeddCount, " + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE= 'online' THEN count(distinct(EGCL_COLLECTIONHEADER.id)) END) AS onlineCount, " + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='card' THEN count(distinct(EGCL_COLLECTIONHEADER.id)) END) AS cardCount, " + " count(distinct(EGCL_COLLECTIONHEADER.id)) as totalReceiptCount, " + " EGCL_COLLECTIONHEADER.SOURCE AS source,CAO.NAME || '-' || CAO.GLCODE AS GLCODE,"); final StringBuilder revSelectQueryStr = new StringBuilder(selectQueryStr).append( " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='cash' THEN SUM(EGCL_COLLECTIONDETAILS.CRAMOUNT) END) AS cashAmount, " + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='cheque' THEN SUM(EGCL_COLLECTIONDETAILS.CRAMOUNT) WHEN EGF_INSTRUMENTTYPE.TYPE='dd' THEN SUM(EGCL_COLLECTIONDETAILS.CRAMOUNT) END) AS chequeddAmount," + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='card' THEN SUM(EGCL_COLLECTIONDETAILS.CRAMOUNT) END) AS cardAmount, " + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE= 'online' THEN SUM(EGCL_COLLECTIONDETAILS.CRAMOUNT) END) AS onlineAmount"); final StringBuilder rebateSelectQueryStr = new StringBuilder(selectQueryStr).append( " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='cash' THEN SUM(EGCL_COLLECTIONDETAILS.DRAMOUNT) END) AS cashAmount, " + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='cheque' THEN SUM(EGCL_COLLECTIONDETAILS.DRAMOUNT) WHEN EGF_INSTRUMENTTYPE.TYPE='dd' THEN SUM(EGCL_COLLECTIONDETAILS.DRAMOUNT) END) AS chequeddAmount," + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='card' THEN SUM(EGCL_COLLECTIONDETAILS.DRAMOUNT) END) AS cardAmount, " + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE= 'online' THEN SUM(EGCL_COLLECTIONDETAILS.DRAMOUNT) END) AS onlineAmount"); final StringBuilder fromQueryStr = new StringBuilder(" FROM " + " EGCL_COLLECTIONHEADER EGCL_COLLECTIONHEADER INNER JOIN EGCL_COLLECTIONINSTRUMENT EGCL_COLLECTIONINSTRUMENT ON EGCL_COLLECTIONHEADER.ID = EGCL_COLLECTIONINSTRUMENT.COLLECTIONHEADER " + " INNER JOIN EGF_INSTRUMENTHEADER EGF_INSTRUMENTHEADER ON EGCL_COLLECTIONINSTRUMENT.INSTRUMENTHEADER = EGF_INSTRUMENTHEADER.ID " + " INNER JOIN EGW_STATUS EGW_STATUS ON EGCL_COLLECTIONHEADER.STATUS = EGW_STATUS.ID" + " INNER JOIN EGF_INSTRUMENTTYPE EGF_INSTRUMENTTYPE ON EGF_INSTRUMENTHEADER.INSTRUMENTTYPE = EGF_INSTRUMENTTYPE.ID" + " INNER JOIN EGCL_COLLECTIONMIS EGCL_COLLECTIONMIS ON EGCL_COLLECTIONHEADER.ID = EGCL_COLLECTIONMIS.COLLECTIONHEADER " + " INNER JOIN EGCL_COLLECTIONDETAILS EGCL_COLLECTIONDETAILS ON EGCL_COLLECTIONHEADER.ID = EGCL_COLLECTIONDETAILS.COLLECTIONHEADER " + " INNER JOIN CHARTOFACCOUNTS CAO ON CAO.ID = EGCL_COLLECTIONDETAILS.CHARTOFACCOUNT "); StringBuilder whereQueryStr = new StringBuilder(" WHERE EGW_STATUS.DESCRIPTION != 'Cancelled' "); StringBuilder creditWhereQueryStr = new StringBuilder(" AND EGCL_COLLECTIONDETAILS.CRAMOUNT>0 "); StringBuilder debitWhereQueryStr = new StringBuilder( " AND EGCL_COLLECTIONDETAILS.DRAMOUNT>0 AND CAO.purposeid in (select id from EGF_ACCOUNTCODE_PURPOSE where name ='" + CollectionConstants.PURPOSE_NAME_REBATE + "')"); final StringBuilder queryStrGroup = new StringBuilder( " GROUP BY source,CAO.NAME,CAO.GLCODE,EGF_INSTRUMENTTYPE.TYPE "); final StringBuilder finalSelectQueryStr = new StringBuilder( "SELECT sum(cashCount) AS cashCount,sum(chequeddCount) AS chequeddCount,sum(onlineCount) AS onlineCount,SOURCE,glCode,sum(cashAmount) AS cashAmount, sum(chequeddAmount) AS chequeddAmount, " + " sum(cardCount) AS cardCount, sum(cardAmount) AS cardAmount, cast(sum(totalReceiptCount) AS NUMERIC) as totalReceiptCount,sum(onlineAmount) AS onlineAmount FROM ("); final StringBuilder finalGroupQuery = new StringBuilder( " ) AS RESULT GROUP BY RESULT.SOURCE,RESULT.glCode order by source, glCode"); if (fromDate != null && toDate != null) { whereQueryStr.append(" AND EGCL_COLLECTIONHEADER.RECEIPTDATE between to_timestamp('" + fromDateFormatter.format(fromDate) + "', 'YYYY-MM-DD HH24:MI:SS') and " + " to_timestamp('" + toDateFormatter.format(toDate) + "', 'YYYY-MM-DD HH24:MI:SS') "); }// w w w . j av a 2 s.c o m if (!source.isEmpty() && !source.equals(CollectionConstants.ALL)) { whereQueryStr.append(" AND EGCL_COLLECTIONHEADER.SOURCE=:source"); } if (glCode != null) { whereQueryStr.append(" AND CAO.GLCODE =:glCode"); } if (branchId != null && branchId != -1) { whereQueryStr.append(" AND EGCL_COLLECTIONMIS.DEPOSITEDBRANCH=:branchId"); } if (status != -1) { whereQueryStr.append(" AND EGCL_COLLECTIONHEADER.STATUS =:searchStatus"); } if (StringUtils.isNotBlank(paymentMode) && !paymentMode.equals(CollectionConstants.ALL)) { whereQueryStr.append(" AND EGF_INSTRUMENTTYPE.TYPE in (:paymentMode)"); revenueHeadQueryStr.append(revSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(creditWhereQueryStr).append(queryStrGroup); rebateQueryStr.append(rebateSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(debitWhereQueryStr).append(queryStrGroup); } else { revenueHeadQueryStr.append(revSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(creditWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE = 'cash'") .append(queryStrGroup); revenueHeadQueryStr.append(" union "); revenueHeadQueryStr.append(revSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(creditWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE in( 'cheque','dd') ") .append(queryStrGroup); revenueHeadQueryStr.append(" union "); revenueHeadQueryStr.append(revSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(creditWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE = 'card'") .append(queryStrGroup); revenueHeadQueryStr.append(" union "); revenueHeadQueryStr.append(revSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(creditWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE = 'online'") .append(queryStrGroup); rebateQueryStr.append(rebateSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(debitWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE = 'cash'") .append(queryStrGroup); rebateQueryStr.append(" union "); rebateQueryStr.append(rebateSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(debitWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE in( 'cheque','dd') ") .append(queryStrGroup); rebateQueryStr.append(" union "); rebateQueryStr.append(rebateSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(debitWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE = 'card'") .append(queryStrGroup); rebateQueryStr.append(" union "); rebateQueryStr.append(rebateSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(debitWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE = 'online'") .append(queryStrGroup); } final StringBuilder finalRevQueryStr = new StringBuilder(finalSelectQueryStr).append(revenueHeadQueryStr) .append(finalGroupQuery); final StringBuilder finalRebateQueryStr = new StringBuilder(finalSelectQueryStr).append(rebateQueryStr) .append(finalGroupQuery); final SQLQuery aggrQuery = (SQLQuery) getCurrentSession().createSQLQuery(finalRevQueryStr.toString()) .addScalar("cashCount", org.hibernate.type.StringType.INSTANCE) .addScalar("cashAmount", DoubleType.INSTANCE) .addScalar("chequeddCount", org.hibernate.type.StringType.INSTANCE) .addScalar("chequeddAmount", DoubleType.INSTANCE) .addScalar("onlineCount", org.hibernate.type.StringType.INSTANCE) .addScalar("onlineAmount", DoubleType.INSTANCE) .addScalar("source", org.hibernate.type.StringType.INSTANCE) .addScalar("glCode", org.hibernate.type.StringType.INSTANCE) .addScalar("cardAmount", DoubleType.INSTANCE) .addScalar("cardCount", org.hibernate.type.StringType.INSTANCE) .addScalar("totalReceiptCount", org.hibernate.type.StringType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(CollectionSummaryHeadWiseReport.class)); final SQLQuery rebateQuery = (SQLQuery) getCurrentSession().createSQLQuery(finalRebateQueryStr.toString()) .addScalar("cashCount", org.hibernate.type.StringType.INSTANCE) .addScalar("cashAmount", DoubleType.INSTANCE) .addScalar("chequeddCount", org.hibernate.type.StringType.INSTANCE) .addScalar("chequeddAmount", DoubleType.INSTANCE) .addScalar("onlineCount", org.hibernate.type.StringType.INSTANCE) .addScalar("onlineAmount", DoubleType.INSTANCE) .addScalar("source", org.hibernate.type.StringType.INSTANCE) .addScalar("glCode", org.hibernate.type.StringType.INSTANCE) .addScalar("cardAmount", DoubleType.INSTANCE) .addScalar("cardCount", org.hibernate.type.StringType.INSTANCE) .addScalar("totalReceiptCount", org.hibernate.type.StringType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(CollectionSummaryHeadWiseReport.class)); if (!source.isEmpty() && !source.equals(CollectionConstants.ALL)) { aggrQuery.setString("source", source); rebateQuery.setString("source", source); } if (glCode != null) { aggrQuery.setString("glCode", glCode); rebateQuery.setString("glCode", glCode); } if (status != -1) { aggrQuery.setLong("searchStatus", status); rebateQuery.setLong("searchStatus", status); } if (StringUtils.isNotBlank(paymentMode) && !paymentMode.equals(CollectionConstants.ALL)) if (paymentMode.equals(CollectionConstants.INSTRUMENTTYPE_CHEQUEORDD)) { aggrQuery.setParameterList("paymentMode", new ArrayList<>(Arrays.asList("cheque", "dd"))); rebateQuery.setParameterList("paymentMode", new ArrayList<>(Arrays.asList("cheque", "dd"))); } else { aggrQuery.setString("paymentMode", paymentMode); rebateQuery.setString("paymentMode", paymentMode); } if (branchId != null && branchId != -1) { aggrQuery.setInteger("branchId", branchId); rebateQuery.setInteger("branchId", branchId); } final List<CollectionSummaryHeadWiseReport> rebateReportResultList = populateQueryResults( rebateQuery.list()); final List<CollectionSummaryHeadWiseReport> aggrReportResults = populateQueryResults(aggrQuery.list()); final CollectionSummaryHeadWiseReportResult collResult = new CollectionSummaryHeadWiseReportResult(); if (!aggrReportResults.isEmpty()) { rebateTotal(aggrReportResults.get(0), rebateReportResultList); } collResult.setAggrCollectionSummaryReportList(aggrReportResults); collResult.setRebateCollectionSummaryReportList(rebateReportResultList); return collResult; }
From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
/** * @return//from w ww. java2 s .c o m */ @Transactional public String schedule() { // Step1: mark which are all we are going to process count = 0; // persistenceService.getSession().getTransaction().setTimeout(900); if (LOGGER.isDebugEnabled()) LOGGER.debug("Started at " + new Date()); markForProcessing(BRS_TRANSACTION_TYPE_CHEQUE); persistenceService.getSession().flush(); // step2 :find duplicate and mark to be processed manually findandUpdateDuplicates(); final List<AutoReconcileBean> detailList = getStatmentsForProcessing(BRS_TRANSACTION_TYPE_CHEQUE); final String statusQury = "select id from EgwStatus where upper(moduletype)=upper('instrument') and upper(description)=upper('" + FinancialConstants.INSTRUMENT_RECONCILED_STATUS + "')"; statusId = (Integer) persistenceService.find(statusQury); final Long instrumentTypeId = getInstrumentType(FinancialConstants.INSTRUMENT_TYPE_CHEQUE); final Long instrumentTypeDDId = getInstrumentType(FinancialConstants.INSTRUMENT_TYPE_DD); // where instrumentheaderid= (select id.....) is used to fetch only one record may be double submit or two instrument // entries // let the user decide final String recociliationQuery = "update EGF_InstrumentHeader set id_status=:statusId, lastmodifiedby=:userId,lastmodifieddate=CURRENT_DATE" + " where id= (select id from egf_instrumentheader where instrumentNumber=:instrumentNo and " + " instrumentAmount=:amount and bankaccountid=:accountId and ispaycheque=:ispaycheque and instrumentType in (" + instrumentTypeId + "," + instrumentTypeDDId + ")" + " and id_status=(select id from Egw_Status where upper(moduletype)=upper('instrument') and upper(description)=upper(:instrumentStatus)))"; final String recociliationAmountQuery = "update egf_instrumentOtherdetails set reconciledamount=:amount,instrumentstatusdate=:txDate " + " ,lastmodifiedby=:userId,lastmodifieddate=CURRENT_DATE,reconciledOn=:reconciliationDate " + " where instrumentheaderid= (select id from egf_instrumentheader where instrumentNumber=:instrumentNo and " + " instrumentAmount=:amount and bankaccountid=:accountId and ispaycheque=:ispaycheque and instrumentType in (" + instrumentTypeId + "," + instrumentTypeDDId + ")" + " and id_status=(select id from Egw_Status where upper(moduletype)=upper('instrument') and upper(description)=upper(:instrumentStatus)))"; 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); rowCount = 0; for (final AutoReconcileBean bean : detailList) { int updated = -1; try { updateQuery.setLong("statusId", statusId); updateQuery.setLong("accountId", accountId); updateQuery.setString("instrumentNo", bean.getInstrumentNo()); updateQuery.setInteger("userId", ApplicationThreadLocals.getUserId().intValue()); updateQuery2.setDate("txDate", bean.getTxDate()); updateQuery2.setDate("reconciliationDate", reconciliationDate); updateQuery2.setLong("accountId", accountId); updateQuery2.setString("instrumentNo", bean.getInstrumentNo()); 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", '0'); updateQuery.setString("instrumentStatus", FinancialConstants.INSTRUMENT_DEPOSITED_STATUS); updated = updateQuery.executeUpdate(); if (updated != 0) { updateQuery2.setBigDecimal("amount", bean.getCredit()); updateQuery2.setCharacter("ispaycheque", '0'); updateQuery2.setString("instrumentStatus", FinancialConstants.INSTRUMENT_RECONCILED_STATUS); updated = updateQuery2.executeUpdate(); } } // 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 { 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(); } } processCSL(); return "result"; }