Example usage for org.hibernate SQLQuery setLong

List of usage examples for org.hibernate SQLQuery setLong

Introduction

In this page you can find the example usage for org.hibernate SQLQuery setLong.

Prototype

@Deprecated
@SuppressWarnings("unchecked")
default Query<R> setLong(int position, long val) 

Source Link

Document

Bind a positional long-valued parameter.

Usage

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();
        }

    }

}