List of usage examples for org.hibernate SQLQuery setString
@Deprecated @SuppressWarnings("unchecked") default Query<R> setString(int position, String val)
From source file:mpimp.assemblxweb.db.util.AssemblXWebDBUtil.java
License:Open Source License
public static void enterJ5AccountCredentials(AssemblXWebModel model) throws Exception { Session hibernateSession = null;// ww w.j a v a 2 s . c om try { hibernateSession = HibernateSessionFactory.getSession(); hibernateSession.beginTransaction(); SQLQuery query = hibernateSession .createSQLQuery("insert into user (login_name, password)" + " values (:loginName, :password)"); // query.setString("firstName", model.getOperator().getFirstName()); // query.setString("lastName", model.getOperator().getLastName()); query.setString("loginName", model.getOperator().getLogin()); query.setString("password", model.getOperator().getPassword()); if (query.executeUpdate() != 1) { if (hibernateSession.getTransaction() != null && hibernateSession.getTransaction().isActive()) { hibernateSession.getTransaction().rollback(); } String message = "Insertion of j5 credentials failed!"; 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 entering of j5 credentials. " + e.getStackTrace(); 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 . java 2s. c o m 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 ww w .j a v a2s . c om 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') "); }//from ww w .j a v 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.collection.service.CollectionReportService.java
License:Open Source License
public SQLQuery getOnlinePaymentReportData(final String districtName, final String ulbName, final String fromDate, final String toDate, final String transactionId) { final SimpleDateFormat dateFormatter = new SimpleDateFormat("dd/MM/yyyy"); final StringBuilder queryStr = new StringBuilder(500); queryStr.append("select * from ").append(environmentSettings.statewideSchemaName()) .append(".onlinepayment_view opv where 1=1"); if (StringUtils.isNotBlank(districtName)) queryStr.append(" and opv.districtName=:districtName "); if (StringUtils.isNotBlank(ulbName)) queryStr.append(" and opv.ulbName=:ulbName "); if (StringUtils.isNotBlank(fromDate)) queryStr.append(" and opv.transactiondate>=:fromDate "); if (StringUtils.isNotBlank(toDate)) queryStr.append(" and opv.transactiondate<=:toDate "); if (StringUtils.isNotBlank(transactionId)) queryStr.append(" and opv.transactionnumber like :transactionnumber "); queryStr.append(" order by receiptdate desc "); final SQLQuery query = getCurrentSession().createSQLQuery(queryStr.toString()); if (StringUtils.isNotBlank(districtName)) query.setString("districtName", districtName); if (StringUtils.isNotBlank(ulbName)) query.setString("ulbName", ulbName); try {/*from w w w. j a v a2s . c om*/ if (StringUtils.isNotBlank(fromDate)) query.setDate("fromDate", dateFormatter.parse(fromDate)); if (StringUtils.isNotBlank(toDate)) query.setDate("toDate", dateFormatter.parse(toDate)); } catch (final ParseException e) { LOGGER.error("Exception parsing Date" + e.getMessage()); } if (StringUtils.isNotBlank(transactionId)) query.setString("transactionnumber", "%" + transactionId + "%"); queryStr.append(" order by opv.receiptdate desc"); query.setResultTransformer(new AliasToBeanResultTransformer(OnlinePaymentResult.class)); return query; }
From source file:org.egov.collection.service.CollectionReportService.java
License:Open Source License
public List<Object[]> getUlbNames(final String districtName) { final StringBuilder queryStr = new StringBuilder("select distinct ulbname from ") .append(environmentSettings.statewideSchemaName()).append(".onlinepayment_view opv where 1=1"); if (StringUtils.isNotBlank(districtName)) queryStr.append(" and opv.districtName=:districtName "); final SQLQuery query = getCurrentSession().createSQLQuery(queryStr.toString()); if (StringUtils.isNotBlank(districtName)) query.setString("districtName", districtName); return query.list(); }
From source file:org.egov.collection.service.CollectionReportService.java
License:Open Source License
public CollectionSummaryReportResult getCollectionSummaryReport(final Date fromDate, final Date toDate, final String paymentMode, final String source, final Long serviceId, final int status, final String serviceType) { final SimpleDateFormat fromDateFormatter = new SimpleDateFormat("yyyy-MM-dd 00:00:00"); final SimpleDateFormat toDateFormatter = new SimpleDateFormat("yyyy-MM-dd 23:59:59"); StringBuilder aggregateQuery = new StringBuilder(); StringBuilder userwiseQuery = new StringBuilder(); final StringBuilder finalUserwiseQuery = new StringBuilder(); final StringBuilder finalAggregateQuery = new StringBuilder(); final StringBuilder selectQuery = new StringBuilder("SELECT "); final StringBuilder fromQuery = new StringBuilder( " FROM EGCL_COLLECTIONHEADER EGCL_COLLECTIONHEADER INNER JOIN EGCL_COLLECTIONINSTRUMENT EGCL_COLLECTIONINSTRUMENT ON EGCL_COLLECTIONHEADER.ID = EGCL_COLLECTIONINSTRUMENT.COLLECTIONHEADER") .append(" INNER JOIN EGF_INSTRUMENTHEADER EGF_INSTRUMENTHEADER ON EGCL_COLLECTIONINSTRUMENT.INSTRUMENTHEADER = EGF_INSTRUMENTHEADER.ID") .append(" INNER JOIN EGW_STATUS EGW_STATUS ON EGCL_COLLECTIONHEADER.STATUS = EGW_STATUS.ID") .append(" INNER JOIN EGF_INSTRUMENTTYPE EGF_INSTRUMENTTYPE ON EGF_INSTRUMENTHEADER.INSTRUMENTTYPE = EGF_INSTRUMENTTYPE.ID") .append(" INNER JOIN EGCL_COLLECTIONMIS EGCL_COLLECTIONMIS ON EGCL_COLLECTIONHEADER.ID = EGCL_COLLECTIONMIS.COLLECTIONHEADER") .append(" INNER JOIN EGCL_SERVICEDETAILS SER ON SER.ID = EGCL_COLLECTIONHEADER.SERVICEDETAILS "); final StringBuilder whereQuery = new StringBuilder(" WHERE EGW_STATUS.DESCRIPTION != 'Cancelled'"); final StringBuilder groupQuery = new StringBuilder( " GROUP BY source, counterName, employeeName, USERID,serviceName "); aggregateQuery.append(/*ww w . j ava 2 s. c o m*/ " ,count(distinct(EGCL_COLLECTIONHEADER.ID)) as totalReceiptCount ,EGCL_COLLECTIONHEADER.SOURCE AS source, SER.NAME AS serviceName, '' AS counterName, '' AS employeeName, 0 AS USERID ") .append(fromQuery); userwiseQuery.append( " ,count(distinct(EGCL_COLLECTIONHEADER.ID)) as totalReceiptCount ,EGCL_COLLECTIONHEADER.SOURCE AS source, SER.NAME AS serviceName, EG_LOCATION.NAME AS counterName, EG_USER.NAME AS employeeName, EG_USER.ID AS USERID") .append(fromQuery) .append(" LEFT JOIN EG_LOCATION EG_LOCATION ON EGCL_COLLECTIONHEADER.LOCATION = EG_LOCATION.ID " + " INNER JOIN EG_USER EG_USER ON EGCL_COLLECTIONHEADER.CREATEDBY = EG_USER.ID "); if (fromDate != null && toDate != null) { whereQuery.append(" AND EGCL_COLLECTIONHEADER.RECEIPTDATE between to_timestamp('") .append(fromDateFormatter.format(fromDate) + "', 'YYYY-MM-DD HH24:MI:SS') and " + " to_timestamp('") .append(toDateFormatter.format(toDate) + "', 'YYYY-MM-DD HH24:MI:SS') "); } if (!source.isEmpty() && !source.equals(CollectionConstants.ALL)) { whereQuery.append(" AND EGCL_COLLECTIONHEADER.SOURCE=:source"); } else { userwiseQuery.setLength(0); userwiseQuery.append(aggregateQuery); } if (serviceId != null && serviceId != -1) whereQuery.append(" AND EGCL_COLLECTIONHEADER.SERVICEDETAILS =:serviceId"); if (status != -1) whereQuery.append(" AND EGCL_COLLECTIONHEADER.STATUS =:searchStatus"); if (!serviceType.equals(CollectionConstants.ALL)) whereQuery.append(" AND SER.SERVICETYPE =:serviceType"); if (StringUtils.isNotBlank(paymentMode) && !paymentMode.equals(CollectionConstants.ALL)) { whereQuery.append(" AND EGF_INSTRUMENTTYPE.TYPE in (:paymentMode)"); if (paymentMode.equals(CollectionConstants.INSTRUMENTTYPE_ONLINE)) { userwiseQuery.setLength(0); userwiseQuery.append(aggregateQuery); } userwiseQuery = prepareSelectQuery(paymentMode).append(userwiseQuery).append(whereQuery) .append(groupQuery); aggregateQuery = prepareSelectQuery(paymentMode).append(aggregateQuery).append(whereQuery) .append(groupQuery); } else { userwiseQuery.append(whereQuery); aggregateQuery.append(whereQuery); userwiseQuery = prepareQueryForAllPaymentMode(userwiseQuery, groupQuery); aggregateQuery = prepareQueryForAllPaymentMode(aggregateQuery, groupQuery); } final StringBuilder finalSelectQuery = new StringBuilder( "SELECT cast(sum(cashCount) AS NUMERIC) AS cashCount,cast(sum(chequeddCount) AS NUMERIC) AS chequeddCount,cast(sum(onlineCount) AS NUMERIC) AS onlineCount,source,counterName,employeeName,serviceName,cast(sum(cashAmount) AS NUMERIC) AS cashAmount, cast(sum(chequeddAmount) AS NUMERIC) AS chequeddAmount, cast(sum(onlineAmount) AS NUMERIC) AS onlineAmount ,USERID,cast(sum(bankCount) AS NUMERIC) AS bankCount, cast(sum(bankAmount) AS NUMERIC) AS bankAmount, " + " cast(sum(cardCount) AS NUMERIC) AS cardCount, cast(sum(cardAmount) AS NUMERIC) AS cardAmount, cast(sum(totalReceiptCount) AS NUMERIC) as totalReceiptCount FROM ("); final StringBuilder finalGroupQuery = new StringBuilder( " ) AS RESULT GROUP BY RESULT.source,RESULT.counterName,RESULT.employeeName,RESULT.USERID,RESULT.serviceName order by source,employeeName, serviceName "); finalUserwiseQuery.append(finalSelectQuery).append(userwiseQuery).append(finalGroupQuery); finalAggregateQuery.append(finalSelectQuery).append(aggregateQuery).append(finalGroupQuery); final SQLQuery userwiseSqluery = createSQLQuery(finalUserwiseQuery.toString()); final SQLQuery aggregateSqlQuery = createSQLQuery(finalAggregateQuery.toString()); if (!source.isEmpty() && !source.equals(CollectionConstants.ALL)) { userwiseSqluery.setString("source", source); aggregateSqlQuery.setString("source", source); } if (serviceId != null && serviceId != -1) { userwiseSqluery.setLong("serviceId", serviceId); aggregateSqlQuery.setLong("serviceId", serviceId); } if (status != -1) { userwiseSqluery.setLong("searchStatus", status); aggregateSqlQuery.setLong("searchStatus", status); } if (!serviceType.equals(CollectionConstants.ALL)) { userwiseSqluery.setString("serviceType", serviceType); aggregateSqlQuery.setString("serviceType", serviceType); } if (StringUtils.isNotBlank(paymentMode) && !paymentMode.equals(CollectionConstants.ALL)) if (paymentMode.equals(CollectionConstants.INSTRUMENTTYPE_CHEQUEORDD)) { userwiseSqluery.setParameterList("paymentMode", new ArrayList<>(Arrays.asList("cheque", "dd"))); aggregateSqlQuery.setParameterList("paymentMode", new ArrayList<>(Arrays.asList("cheque", "dd"))); } else { userwiseSqluery.setString("paymentMode", paymentMode); aggregateSqlQuery.setString("paymentMode", paymentMode); } final List<CollectionSummaryReport> reportResults = populateQueryResults(userwiseSqluery.list()); final List<CollectionSummaryReport> aggrReportResults = populateQueryResults(aggregateSqlQuery.list()); final CollectionSummaryReportResult collResult = new CollectionSummaryReportResult(); collResult.setCollectionSummaryReportList(reportResults); collResult.setAggrCollectionSummaryReportList(aggrReportResults); return collResult; }
From source file:org.egov.commons.dao.ChartOfAccountsHibernateDAO.java
License:Open Source License
@Deprecated public int getDetailTypeIdByName(final String glCode, final Connection connection, final String name) { final SQLQuery query = persistenceService.getSession().createSQLQuery( "SELECT a.ID FROM accountdetailtype a,chartofaccountdetail coad WHERE coad.DETAILTYPEID =a.ID AND coad.glcodeid=(SELECT ID FROM chartofaccounts WHERE glcode=:glCode) AND a.NAME=:name"); query.setString("glCode", glCode); query.setString("name", name); List accountDtlTypeList = query.list(); return (accountDtlTypeList != null) && (accountDtlTypeList.size() != 0) ? Integer.valueOf(accountDtlTypeList.get(0).toString()) : 0;/*from w w w .ja va 2 s .c o m*/ }
From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
/** * @return/*from w w w .ja va2 s.c om*/ */ @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"; }
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 {/*from ww w . j a v a 2s. 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(); } } }