List of usage examples for org.hibernate SQLQuery setLong
@Deprecated @SuppressWarnings("unchecked") default Query<R> setLong(int position, long val)
From source file:gov.nih.nci.caarray.security.SecurityUtils.java
License:BSD License
private static Map<Long, Privileges> getPermissionsWithCanonicalTable(String userName, String className, String attributeName, Collection<Long> protectableIds, Application application) { final String sql = " select distinct cast(pe.attribute_value as unsigned), " + "p.privilege_name from csm_protection_element pe " + "inner join csm_pg_pe pgpe on pe.protection_element_id = pgpe.protection_element_id " + "inner join csm_user_group_role_pg ugrpg " + "on pgpe.protection_group_id = ugrpg.protection_group_id " + "inner join csm_role r on ugrpg.role_id = r.role_id " + "inner join csm_user_group ug on ugrpg.group_id = ug.group_id " + "inner join csm_role_privilege rp on r.role_id = rp.role_id " + "inner join csm_privilege p on rp.privilege_id = p.privilege_id " + "inner join csm_user u on ug.user_id = u.user_id " + "where pe.object_id = :class_name and pe.attribute = :attr_name " + "and pe.attribute_value in (:attr_values) and u.login_name = :login_name " + "and pe.application_id = :app_id order by pe.attribute_value, p.privilege_name"; final SQLQuery query = hibernateHelper.getCurrentSession().createSQLQuery(sql); query.setParameterList("attr_values", protectableIds); query.setString("login_name", userName); query.setString("class_name", className); query.setString("attr_name", attributeName); query.setLong("app_id", application.getApplicationId()); @SuppressWarnings("unchecked") final List<Object[]> results = query.list(); return createPrivilegesMapFromResults(results); }
From source file:gov.nih.nci.caintegrator.data.CaIntegrator2DaoImpl.java
License:BSD License
/** * {@inheritDoc}// w ww .j av a2 s . c o m */ @Override @SuppressWarnings(UNCHECKED) // Hibernate operations are untyped public Set<String> retrieveGeneSymbolsInStudy(Collection<String> symbols, Study study) { List<String> symbolsUpper = toUpperCase(symbols); String symbolsSql = "select distinct(gene.symbol) " + "from gene, reporter_genes, abstract_reporter, reporter_list, array_data_reporter_lists, array_data " + "where gene.id = reporter_genes.gene_id " + "and abstract_reporter.id = reporter_genes.reporter_id " + "and abstract_reporter.reporter_list_id = reporter_list.id " + "and array_data_reporter_lists.reporter_list_id = reporter_list.id " + "and array_data.id = array_data_reporter_lists.array_data_id " + "and gene.symbol in (" + getSymbolParameterPlaceholders(symbolsUpper) + ") " + "and array_data.study_id = :studyId "; SQLQuery query = getCurrentSession().createSQLQuery(symbolsSql); setSymbolParameters(query, symbolsUpper); query.setLong("studyId", study.getId()); List<String> symbolList = query.list(); Set<String> symbolsInStudy = Sets.newHashSet(); symbolsInStudy.addAll(symbolList); return symbolsInStudy; }
From source file:nl.strohalm.cyclos.dao.members.ReferenceDAOImpl.java
License:Open Source License
public List<PaymentAwaitingFeedbackDTO> searchPaymentsAwaitingFeedback( final PaymentsAwaitingFeedbackQuery query) { final ResultType resultType = query.getResultType(); final PageParameters pageParameters = query.getPageParameters(); final boolean countOnly = resultType == ResultType.PAGE && pageParameters != null && pageParameters.getMaxResults() == 0; // There are 2 tables which contains payments that can have feedback: transfers and scheduled payments // As we need an union, we need a native SQL final Member member = query.getMember(); Boolean expired = query.getExpired(); final StringBuilder sql = new StringBuilder(); sql.append(" select "); if (countOnly) { sql.append(" count(*) as row_count"); } else {//from w ww. j a va2s. c o m sql.append(" * "); } sql.append(" from ( "); { sql.append( " select t.id, t.type_id as transferTypeId, false as scheduled, t.date, t.amount, tm.id as memberId, tm.name as memberName, ta.owner_name as memberUsername"); sql.append( " from transfers t inner join transfer_types tt on t.type_id = tt.id inner join accounts ta on t.to_account_id = ta.id inner join members tm on ta.member_id = tm.id"); if (member != null) { sql.append(" inner join accounts a on t.from_account_id = a.id"); } sql.append(" left join refs tf on tf.transfer_id = t.id"); sql.append(" where tt.requires_feedback = true"); sql.append(" and t.date >= tt.feedback_enabled_since"); sql.append(" and t.parent_id is null"); sql.append(" and t.chargeback_of_id is null"); sql.append(" and t.scheduled_payment_id is null"); sql.append(" and t.process_date is not null"); if (expired != null) { sql.append(" and t.feedback_deadline " + (expired ? "<" : ">=") + " now()"); } sql.append(" and tf.id is null"); if (member != null) { sql.append(" and a.member_id = :memberId"); } sql.append(" union "); sql.append(" select sp.id, sp.type_id, true, sp.date, sp.amount, tm.id, tm.name, ta.owner_name"); sql.append( " from scheduled_payments sp inner join transfer_types tt on sp.type_id = tt.id inner join accounts ta on sp.to_account_id = ta.id inner join members tm on ta.member_id = tm.id"); if (member != null) { sql.append(" inner join accounts a on sp.from_account_id = a.id"); } sql.append(" left join refs tf on tf.scheduled_payment_id = sp.id"); sql.append(" where tt.requires_feedback = true"); if (expired != null) { sql.append(" and sp.feedback_deadline " + (expired ? "<" : ">=") + " now()"); } sql.append(" and sp.date >= tt.feedback_enabled_since"); sql.append(" and tf.id is null"); if (member != null) { sql.append(" and a.member_id = :memberId"); } } sql.append(") as awaiting "); if (!countOnly) { sql.append("order by date"); } SQLQuery sqlQuery = getSession().createSQLQuery(sql.toString()); if (member != null) { sqlQuery.setLong("memberId", member.getId()); } if (countOnly) { // Handle the special case for count only sqlQuery.addScalar("row_count", StandardBasicTypes.INTEGER); int count = ((Number) sqlQuery.uniqueResult()).intValue(); return new PageImpl<PaymentAwaitingFeedbackDTO>(pageParameters, count, Collections.<PaymentAwaitingFeedbackDTO>emptyList()); } else { // Execute the search sqlQuery.addScalar("id", StandardBasicTypes.LONG); sqlQuery.addScalar("transferTypeId", StandardBasicTypes.LONG); sqlQuery.addScalar("scheduled", StandardBasicTypes.BOOLEAN); sqlQuery.addScalar("date", StandardBasicTypes.CALENDAR); sqlQuery.addScalar("amount", StandardBasicTypes.BIG_DECIMAL); sqlQuery.addScalar("memberId", StandardBasicTypes.LONG); sqlQuery.addScalar("memberName", StandardBasicTypes.STRING); sqlQuery.addScalar("memberUsername", StandardBasicTypes.STRING); getHibernateQueryHandler().applyPageParameters(pageParameters, sqlQuery); // We'll always use an iterator, even if it is for later adding it to a list Iterator<PaymentAwaitingFeedbackDTO> iterator = new ScrollableResultsIterator<PaymentAwaitingFeedbackDTO>( sqlQuery, new Transformer<Object[], PaymentAwaitingFeedbackDTO>() { public PaymentAwaitingFeedbackDTO transform(final Object[] input) { PaymentAwaitingFeedbackDTO dto = new PaymentAwaitingFeedbackDTO(); dto.setId((Long) input[0]); dto.setTransferTypeId((Long) input[1]); dto.setScheduled(Boolean.TRUE.equals(input[2])); dto.setDate((Calendar) input[3]); dto.setAmount((BigDecimal) input[4]); dto.setMemberId((Long) input[5]); dto.setMemberName((String) input[6]); dto.setMemberUsername((String) input[7]); TransferType transferType = (TransferType) getSession().load(TransferType.class, dto.getTransferTypeId()); dto.setCurrency(getFetchDao().fetch(transferType.getCurrency())); return dto; } }); if (resultType == ResultType.ITERATOR) { return new IteratorListImpl<PaymentAwaitingFeedbackDTO>(iterator); } else { List<PaymentAwaitingFeedbackDTO> list = new ArrayList<PaymentAwaitingFeedbackDTO>(); CollectionUtils.addAll(list, iterator); DataIteratorHelper.close(iterator); if (resultType == ResultType.PAGE) { // For page, we need another search for the total count query.setPageForCount(); int totalCount = PageHelper.getTotalCount(searchPaymentsAwaitingFeedback(query)); return new PageImpl<PaymentAwaitingFeedbackDTO>(pageParameters, totalCount, list); } else { return list; } } } }
From source file:org.broadleafcommerce.common.util.UpdateExecutor.java
License:Apache License
/** * Perform an update query using a String template and params. Note, this is only intended for special * usage with update queries that have an IN clause at the end. This implementation uses Hibernate Session * directly to avoid a problem with assigning NULL values. The query should be written in native SQL. * </p>/*from www . j a va2s . c om*/ * An example looks like: 'UPDATE BLC_SNDBX_WRKFLW_ITEM SET SCHEDULED_DATE = ? WHERE WRKFLW_SNDBX_ITEM_ID IN (%s)' * * @param em The entity manager to use for the persistence operation * @param template the overall update sql template. The IN clause parameter should be written using 'IN (%s)'. * @param params any other params that are present in the sql template, other than the IN clause. Should be written using '?'. Should be in order. Can be null. * @param types the {@link org.hibernate.type.Type} instances that identify the types for the params. Should be in order and match the length of params. Can be null. * @param ids the ids to include in the IN clause. * @return the total number of records updated in the database */ public static int executeUpdateQuery(EntityManager em, String template, Object[] params, Type[] types, List<Long> ids) { int response = 0; List<Long[]> runs = buildRuns(ids); for (Long[] run : runs) { String queryString = String.format(template, buildInClauseTemplate(run.length)); SQLQuery query = em.unwrap(Session.class).createSQLQuery(queryString); int counter = 0; if (!ArrayUtils.isEmpty(params)) { for (Object param : params) { query.setParameter(counter, param, types[counter]); counter++; } } for (Long id : run) { query.setLong(counter, id); counter++; } response += query.executeUpdate(); } return response; }
From source file:org.ednovo.gooru.infrastructure.persistence.hibernate.index.ContentIdexDaoImpl.java
License:Open Source License
@Override public Long getSubscriptionCountByContentId(long contentId) { SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(GET_SUBSCRIPTION_BY_CONTENT_ID); query.addScalar("subscriberCount", StandardBasicTypes.LONG); query.setLong(CONTENT_ID, contentId); List<Long> list = query.list(); return (list.size() > 0 ? list.get(0) : 0); }
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.ja v a 2 s . c om*/ 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 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(/* www . jav a 2s . c om*/ " ,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.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
/** * @return/*from w w w.java 2s . 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"; }
From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
private void markForProcessing(final String type) { final StringBuffer sql = new StringBuffer(256); sql.append("update ").append(TABLENAME).append(" set action='").append(BRS_ACTION_TO_BE_PROCESSED) .append("' where type='").append(type) .append("' and accountid=:accountId and txdate>=:fromDate and txDate<=:toDate and (action is null or action!='processed')"); if (BRS_TRANSACTION_TYPE_BANK.equalsIgnoreCase(type)) sql.append(" and CSLno is not null "); final SQLQuery markQuery = persistenceService.getSession().createSQLQuery(sql.toString()); markQuery.setDate("fromDate", fromDate); markQuery.setDate("toDate", toDate); markQuery.setLong("accountId", accountId); markQuery.executeUpdate();//from w ww. ja va2 s .c o m }
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 ww .j av 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(); } } }