Example usage for org.hibernate Query setInteger

List of usage examples for org.hibernate Query setInteger

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Query
  6. setInteger

Introduction

In this page you can find the example usage for org.hibernate Query setInteger.

Prototype

@Deprecated
@SuppressWarnings("unchecked")
default Query<R> setInteger(String name, int val)

Source Link

Document

Bind a named int-valued parameter.

Usage

From source file:com.exam.bean.feedback.java

public String review(int id) {
    Session s = HibernateUtil.getSessionFactory().openSession();
    s.beginTransaction();//from  www.ja  va2  s . com
    Query q = s.createQuery("update Contact set status=:s where id=:id");
    q.setString("s", "Review");
    q.setInteger("id", id);
    q.executeUpdate();
    s.getTransaction().commit();
    s.close();
    FacesContext context = FacesContext.getCurrentInstance();
    context.addMessage(null, new FacesMessage("Success"));
    return "feedback.xhtml";
}

From source file:com.example.app.model.profile.ProfileDAO.java

License:Open Source License

/**
 * Test if the specified user can perform all the operations on any of the specified profiles.
 *
 * @param user the User.// w ww  .j  a va 2  s.co  m
 * @param profiles the Profiles.
 * @param timeZone the timezone.
 * @param operations the MembershipOperations to check
 *
 * @return true or false.
 */
@Contract("null,_,_->false")
public boolean canOperate(@Nullable User user, @Nonnull Collection<Profile> profiles, TimeZone timeZone,
        @Nonnull MembershipOperation... operations) {
    if (user == null || profiles.isEmpty())
        return false;
    final Date now = convertForPersistence(getZonedDateTimeForComparison(timeZone));
    Preconditions.checkArgument(operations.length > 0);
    final Query query = getSession().createQuery("SELECT COUNT(m) FROM Membership m INNER JOIN m.profile p\n"
            + " INNER JOIN m.operations  op\n" + " WHERE m.user = :user\n" + " AND p IN (:profiles)\n"
            + " AND op IN (:operations)\n" + " AND (m.startDate IS NULL OR m.startDate <= :today)\n"
            + " AND (m.endDate IS NULL OR m.endDate >= :today)\n" + " GROUP BY m\n"
            + "  HAVING COUNT(op) = :operationCount");
    query.setCacheable(true).setCacheRegion(ProjectCacheRegions.PROFILE_QUERY);
    query.setParameter("user", user);
    query.setParameterList("profiles", profiles);
    query.setParameterList("operations", operations);
    query.setParameter("today", now);
    query.setInteger("operationCount", operations.length);
    return Optional.ofNullable(((Number) query.uniqueResult())).map(Number::intValue).map(i -> i > 0)
            .orElse(false);
}

From source file:com.example.app.model.profile.ProfileDAO.java

License:Open Source License

/**
 * Returns a boolean flag on whether or not the given User can perform the given MembershipOperation on the given Profile
 *
 * @param user the User, may be null/*from   www  .j  a  va 2s.  co  m*/
 * @param profileType the ProfileType.
 * @param operations the MembershipOperations to check
 * @param timeZone the timezone.
 *
 * @return a boolean flag.  If true, the given user can perform the given operation on the given profile
 */
public boolean canOperate(@Nullable User user, @Nullable ProfileType profileType, TimeZone timeZone,
        MembershipOperation... operations) {
    if (user == null || profileType == null)
        return false;
    Preconditions.checkArgument(operations.length > 0);
    final Date now = convertForPersistence(getZonedDateTimeForComparison(timeZone));
    final Query query = getSession().createQuery(
            "SELECT COUNT(m) FROM Membership m INNER JOIN m.profile p INNER JOIN p.profileType pt\n"
                    + " INNER JOIN m.operations  op\n" + " WHERE m.user = :user\n"
                    + " AND pt.id = :profileTypeId\n" + " AND op IN (:operations)\n"
                    + " AND (m.startDate IS NULL OR m.startDate <= :today)\n"
                    + " AND (m.endDate IS NULL OR m.endDate >= :today)\n" + " GROUP BY m\n"
                    + "  HAVING COUNT(op) = :operationCount");
    query.setCacheable(true).setCacheRegion(ProjectCacheRegions.PROFILE_QUERY);
    query.setParameter("user", user);
    query.setParameter("profileTypeId", profileType.getId());
    query.setParameterList("operations", operations);
    query.setParameter("today", now);
    query.setInteger("operationCount", operations.length);
    return Optional.ofNullable(((Number) query.uniqueResult())).map(Number::intValue).map(i -> i > 0)
            .orElse(false);
}

From source file:com.example.app.profile.model.ProfileDAO.java

License:Open Source License

/**
 * Test if the specified user can perform all the operations on any of the specified profiles.
 *
 * @param user the User.//from  w ww  .ja  va2s  .  c  om
 * @param profiles the Profiles.
 * @param timeZone the timezone.
 * @param operations the MembershipOperations to check
 *
 * @return true or false.
 */
@Contract("null,_,_->false")
public boolean canOperate(@Nullable User user, @Nonnull Collection<Profile> profiles, TimeZone timeZone,
        @Nonnull MembershipOperation... operations) {
    if (user != null && _appUtil.userHasAdminRole(user))
        return true;
    if (user == null || profiles.isEmpty())
        return false;
    final Date now = convertForPersistence(getZonedDateTimeForComparison(timeZone));
    Preconditions.checkArgument(operations.length > 0);
    final Query query = getSession().createQuery("SELECT COUNT(m) FROM Membership m INNER JOIN m.profile p\n"
            + " INNER JOIN m.operations  op\n" + " WHERE m.user = :user\n" + " AND p IN (:profiles)\n"
            + " AND op IN (:operations)\n" + " AND (m.startDate IS NULL OR m.startDate <= :today)\n"
            + " AND (m.endDate IS NULL OR m.endDate >= :today)\n" + " GROUP BY m\n"
            + "  HAVING COUNT(op) = :operationCount");
    query.setCacheable(true).setCacheRegion(ProjectCacheRegions.PROFILE_QUERY);
    query.setParameter("user", user);
    query.setParameterList("profiles", profiles);
    query.setParameterList("operations", operations);
    query.setParameter("today", now);
    query.setInteger("operationCount", operations.length);
    return Optional.ofNullable(((Number) query.uniqueResult())).map(Number::intValue).map(i -> i > 0)
            .orElse(false);
}

From source file:com.example.app.profile.model.ProfileDAO.java

License:Open Source License

/**
 * Returns a boolean flag on whether or not the given User can perform the given MembershipOperation on the given Profile
 *
 * @param user the User, may be null// w  w w  . ja v  a2  s . c om
 * @param profileType the ProfileType.
 * @param operations the MembershipOperations to check
 * @param timeZone the timezone.
 *
 * @return a boolean flag.  If true, the given user can perform the given operation on the given profile
 */
public boolean canOperate(@Nullable User user, @Nullable ProfileType profileType, TimeZone timeZone,
        MembershipOperation... operations) {
    if (user != null && _appUtil.userHasAdminRole(user))
        return true;
    if (user == null || profileType == null)
        return false;
    Preconditions.checkArgument(operations.length > 0);
    final Date now = convertForPersistence(getZonedDateTimeForComparison(timeZone));
    final Query query = getSession().createQuery(
            "SELECT COUNT(m) FROM Membership m INNER JOIN m.profile p INNER JOIN p.profileType pt\n"
                    + " INNER JOIN m.operations  op\n" + " WHERE m.user = :user\n"
                    + " AND pt.id = :profileTypeId\n" + " AND op IN (:operations)\n"
                    + " AND (m.startDate IS NULL OR m.startDate <= :today)\n"
                    + " AND (m.endDate IS NULL OR m.endDate >= :today)\n" + " GROUP BY m\n"
                    + "  HAVING COUNT(op) = :operationCount");
    query.setCacheable(true).setCacheRegion(ProjectCacheRegions.PROFILE_QUERY);
    query.setParameter("user", user);
    query.setParameter("profileTypeId", profileType.getId());
    query.setParameterList("operations", operations);
    query.setParameter("today", now);
    query.setInteger("operationCount", operations.length);
    return Optional.ofNullable(((Number) query.uniqueResult())).map(Number::intValue).map(i -> i > 0)
            .orElse(false);
}

From source file:com.examples.hql.HQLExamples.java

public static void main(String[] args) {
    SessionFactory sf = HibernateUtil.getSessionFactory();
    Session session = sf.openSession();/*from   w  w  w  . j a v a  2  s .c  o m*/

    try {

        Query q = session.createQuery("from PurchaseOrderHeader");

        List<PurchaseOrderHeader> listPOH = q.list();

        for (PurchaseOrderHeader poh : listPOH) {

            System.out.println(poh.getPoheaderId() + " | " + poh.getPonumber() + " | " + poh.getPodate() + " | "
                    + poh.getOrderValue());
        }

        System.out.println("----------***********-------------");

        Query q1 = session.createQuery("from PurchaseOrderHeader where POHeaderID=:pohid");
        q1.setInteger("pohid", 1);

        PurchaseOrderHeader pohead = (PurchaseOrderHeader) q1.uniqueResult();

        System.out.println(pohead.getPoheaderId() + " | " + pohead.getPonumber() + " | " + pohead.getPodate()
                + " | " + pohead.getOrderValue());

        System.out.println("----------***********-------------");

        PurchaseOrderHeader po = (PurchaseOrderHeader) session.get(PurchaseOrderHeader.class, 2);

        System.out.println(po.getPoheaderId() + " | " + po.getPonumber() + " | " + po.getPodate() + " | "
                + po.getOrderValue());

        System.out.println("----------***********-------------");

        PurchaseOrderHeader po1 = (PurchaseOrderHeader) session.load(PurchaseOrderHeader.class, 3);

        System.out.println(po1.getPoheaderId() + " | " + po1.getPonumber() + " | " + po1.getPodate() + " | "
                + po1.getOrderValue());

        System.out.println("----------***********-------------");

        Query q2 = session.createQuery("from PurchaseOrderHeader where PONumber like '%PO%'");
        //q2.setParameter("PONumber", "PO102030");

        List<PurchaseOrderHeader> listPOH1 = q2.list();

        for (PurchaseOrderHeader poh : listPOH1) {

            System.out.println(poh.getPoheaderId() + " | " + poh.getPonumber() + " | " + poh.getPodate() + " | "
                    + poh.getOrderValue());
        }

        System.out.println("----------***********-------------");

        //Query q3=session.createQuery("select poh.ponumber,det.quantity from  PurchaseOrderHeader poh JOIN poh.purchaseOrderDetail det ");

        Query q3 = session.createQuery(
                "select poh.ponumber,det.quantity from  PurchaseOrderHeader poh LEFT JOIN poh.purchaseOrderDetail det ");

        List<Object[]> objList = q3.list();

        for (Object[] objList1 : objList) {

            System.out.println(objList1[0] + " | " + objList1[1]);
        }

        System.out.println("----------***********-------------");

        session.close();

        sf.close();

        System.out.println("Git by swamy....");

    } catch (Exception ex) {
        ex.printStackTrace();
    }
}

From source file:com.exilant.eGov.src.common.EGovernCommon.java

License:Open Source License

public BigDecimal getAccountBalance(final String recDate, final String bankAccountId)
        throws TaskFailedException {

    BigDecimal opeAvailable = BigDecimal.ZERO;
    BigDecimal totalAvailable = BigDecimal.ZERO;
    Query pst = null;
    List<Object[]> resultset = null;
    List<Object[]> resultset1 = null;
    try {//from w w w . java  2  s .c  om
        final String str = "SELECT case when sum(openingDebitBalance) is null then 0 else sum(openingDebitBalance) end - case when sum(openingCreditBalance) is null then 0 else sum(openingCreditBalance) end AS \"openingBalance\" "
                + " FROM transactionSummary WHERE financialYearId=( SELECT id FROM financialYear WHERE startingDate <=? "
                + " AND endingDate >=? )  AND glCodeId =(select glcodeid from bankaccount where id=? )";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("getAccountBalance(EGovernCommon.java): " + str);
        pst = persistenceService.getSession().createSQLQuery(str);
        SimpleDateFormat dtSlashFormat = new SimpleDateFormat("dd/MMM/yyyy");
        Date reconDate = dtSlashFormat.parse(recDate);
        java.sql.Date sDate = new java.sql.Date(reconDate.getTime());
        pst.setDate(0, sDate);
        pst.setDate(1, sDate);
        pst.setInteger(2, Integer.valueOf(bankAccountId));
        List list = pst.list();
        if (list == null || list.size() == 0)
            if (LOGGER.isDebugEnabled())
                LOGGER.debug("Else resultset in getAccountBalance...");

        if (list != null || list.size() > 0) {
            opeAvailable = new BigDecimal(list.get(0).toString());
        }

        /* for (final Object[] element : resultset)
         {
            if(element[0]!=null)
        opeAvailable = new BigDecimal(element[0].toString());
         }*/

        if (LOGGER.isDebugEnabled())
            LOGGER.debug("opening balance  " + opeAvailable);

        final String str1 = "SELECT (case when sum(gl.debitAmount) is null then 0 else sum(gl.debitAmount) end - case when sum(gl.creditAmount)  is null then 0 else sum(gl.creditAmount) end ) + "
                + opeAvailable
                + " as \"totalAmount\" FROM   generalLedger gl, voucherHeader vh WHERE vh.id = gl.voucherHeaderId and gl.glCodeid = (select glcodeid from bankaccount where id=?) AND  "
                + " vh.voucherDate >=( SELECT startingDate FROM financialYear WHERE startingDate <= ? AND endingDate >= ?) AND vh.voucherDate <= ? and vh.status!=4";

        if (LOGGER.isDebugEnabled())
            LOGGER.debug("Curr Yr Bal: " + str1);
        pst = persistenceService.getSession().createSQLQuery(str1);
        pst.setInteger(0, Integer.valueOf(bankAccountId));
        pst.setDate(1, reconDate);
        pst.setDate(2, reconDate);
        pst.setDate(3, reconDate);
        List list2 = pst.list();
        if (list2 != null)
            totalAvailable = new BigDecimal(list2.get(0).toString());
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("total balance  " + totalAvailable);

        if (resultset1 == null || resultset1.size() == 0)
            if (LOGGER.isDebugEnabled())
                LOGGER.debug("Else resultset in getAccountBalance...");

        totalAvailable = totalAvailable.setScale(2, BigDecimal.ROUND_HALF_UP);
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("total balance before return " + totalAvailable);
        return totalAvailable;
    } catch (final Exception e) {
        LOGGER.error(e.getMessage(), e);
        throw taskExc;
    }
}

From source file:com.exilant.eGov.src.domain.ChartOfAccts.java

License:Open Source License

public void newUpdate() throws TaskFailedException, SQLException {
    created = eGovernCommon.getCurrentDate();
    Query pstmt = null;
    try {/*w w w. j  a va  2s  .  c o m*/
        created = formatter.format(sdf.parse(created));
    } catch (final ParseException parseExp) {
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(parseExp.getMessage(), parseExp);
    }
    setCreated(created);
    setLastModified(created);
    final StringBuilder query = new StringBuilder(500);
    query.append("update ChartOfAccounts set ");
    if (glCode != null)
        query.append("glCode=?,");
    if (name != null)
        query.append("name=?,");
    if (description != null)
        query.append("description=?,");
    if (isActiveForPosting != null)
        query.append("ISACTIVEFORPOSTING=?,");
    if (parentId != null)
        query.append("PARENTID=?,");
    if (lastModified != null)
        query.append("LASTMODIFIED=?,");
    if (modifiedBy != null)
        query.append("MODIFIEDBY=?,");
    if (created != null)
        query.append("CREATED=?,");
    if (purposeid != null)
        query.append("PURPOSEID=?,");
    if (operation != null)
        query.append("OPERATION=?,");
    if (FIEoperation != null)
        query.append("FIEOPERATION=?,");
    if (type != null)
        query.append("type=?,");
    if (classname != null)
        query.append("class=?,");
    if (classification != null)
        query.append("CLASSIFICATION=?,");
    if (functionreqd != null)
        query.append("FUNCTIONREQD=?,");
    if (scheduleId != null)
        query.append("SCHEDULEID=?,");
    if (FIEscheduleId != null)
        query.append("FIEscheduleId=?,");
    if (receiptscheduleid != null)
        query.append("RECEIPTSCHEDULEID=?,");
    if (receiptoperation != null)
        query.append("RECEIPTOPERATION=?,");
    if (paymentscheduleid != null)
        query.append("PAYMENTSCHEDULEID=?,");
    if (paymentoperation != null)
        query.append("PAYMENTOPERATION=?,");
    if (budgetCheckReqd != null)
        query.append("BUDGETCHECKREQ=?,");
    final int lastIndexOfComma = query.lastIndexOf(",");
    query.deleteCharAt(lastIndexOfComma);
    query.append(" where id=?");

    try {
        int i = 1;
        pstmt = persistenceService.getSession().createSQLQuery(query.toString());

        if (glCode != null)
            pstmt.setString(i++, glCode);
        if (name != null)
            pstmt.setString(i++, name);
        if (description != null)
            pstmt.setString(i++, description);
        if (isActiveForPosting != null)
            pstmt.setString(i++, isActiveForPosting);
        if (parentId != null)
            pstmt.setString(i++, parentId);
        if (lastModified != null)
            pstmt.setString(i++, lastModified);
        if (modifiedBy != null)
            pstmt.setString(i++, modifiedBy);
        if (created != null)
            pstmt.setString(i++, created);
        if (purposeid != null)
            pstmt.setString(i++, purposeid);
        if (operation != null)
            pstmt.setString(i++, operation);
        if (FIEoperation != null)
            pstmt.setString(i++, FIEoperation);
        if (type != null)
            pstmt.setString(i++, type);
        if (classname != null)
            pstmt.setString(i++, classname);
        if (classification != null)
            pstmt.setString(i++, classification);
        if (functionreqd != null)
            pstmt.setString(i++, functionreqd);
        if (scheduleId != null)
            pstmt.setString(i++, scheduleId);
        if (FIEscheduleId != null)
            pstmt.setInteger(i++, FIEscheduleId);
        if (receiptscheduleid != null)
            pstmt.setString(i++, receiptscheduleid);
        if (receiptoperation != null)
            pstmt.setString(i++, receiptoperation);
        if (paymentscheduleid != null)
            pstmt.setString(i++, paymentscheduleid);
        if (paymentoperation != null)
            pstmt.setString(i++, paymentoperation);
        if (budgetCheckReqd != null)
            pstmt.setString(i++, budgetCheckReqd);
        pstmt.setString(i++, id);

        pstmt.executeUpdate();
    } catch (final HibernateException e) {
        LOGGER.error("Exception occured while getting the data  " + e.getMessage(),
                new HibernateException(e.getMessage()));
    } catch (final Exception e) {
        LOGGER.error("Exception occured while getting the data  " + e.getMessage(),
                new Exception(e.getMessage()));
    }
}

From source file:com.exilant.eGov.src.domain.GeneralLedger.java

License:Open Source License

/**
 * Function to get all the recoveries not in fund
 *
 * @param ACCOUNTDETAILTYPE/* w  w w.  j a va2  s . com*/
 * @param ACCOUNTDETAILKEY
 * @param FUND
 * @param date
 * @param status
 * @return HashMap with account code as the key and the total pending recovery amount for that account code.
 * @throws SQLException
 * @throws TaskFailedException
 */
public HashMap getRecoveryForSubLedgerNotInFund(final Integer ACCOUNTDETAILTYPE, final Integer ACCOUNTDETAILKEY,
        final Integer FUND, final Date date, final int status) throws SQLException, TaskFailedException {
    final HashMap<String, BigDecimal> hmA = new HashMap<String, BigDecimal>();
    final HashMap<String, BigDecimal> hmB = new HashMap<String, BigDecimal>();
    HashMap<String, BigDecimal> hmFinal = new HashMap<String, BigDecimal>();
    final SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy");
    final String vDate = formatter.format(date);
    Query pst = null;
    List<Object[]> rs = null;
    try {

        // Query1 - to get the sum of credit amount glcode wise
        String selQuery = "SELECT GL.GLCODE as ACCOUNTCODE,SUM(GLD.AMOUNT) AS CREDITAMOUNT FROM VOUCHERHEADER VH,GENERALLEDGER GL,GENERALLEDGERDETAIL GLD "
                + " WHERE VH.FUNDID NOT IN (?) AND GLD.DETAILTYPEID= ? AND DETAILKEYID= ? AND VH.STATUS= ? AND GL.CREDITAMOUNT>0 "
                + " AND VH.ID=GL.VOUCHERHEADERID AND GL.ID=GLD.GENERALLEDGERID AND VH.VOUCHERDATE<= ? GROUP BY GL.GLCODE";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("query (CreditAmount)--> " + selQuery);
        pst = persistenceService.getSession().createSQLQuery(selQuery);
        pst.setInteger(0, FUND);
        pst.setInteger(1, ACCOUNTDETAILTYPE);
        pst.setInteger(2, ACCOUNTDETAILKEY);
        pst.setInteger(3, status);
        pst.setString(4, vDate);
        rs = pst.list();
        for (final Object[] element : rs)
            hmA.put(element[0].toString(), new BigDecimal(element[1].toString()));
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("map size -------> " + hmA.size());

        // Query2 - to get the sum of debit amount glcode wise
        selQuery = "SELECT GL.GLCODE AS GLCODE ,SUM(GLD.AMOUNT) AS DEBITAMOUNT FROM VOUCHERHEADER VH,GENERALLEDGER GL,GENERALLEDGERDETAIL GLD  "
                + " WHERE VH.FUNDID NOT IN (?)   AND GLD.DETAILTYPEID= ? AND DETAILKEYID= ? AND VH.STATUS= ? AND GL.DEBITAMOUNT>0 AND  "
                + " VH.ID=GL.VOUCHERHEADERID AND GL.ID=GLD.GENERALLEDGERID AND VH.VOUCHERDATE<= ? GROUP BY GL.GLCODE";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("query (DebitAmount)--> " + selQuery);
        pst = persistenceService.getSession().createSQLQuery(selQuery);
        pst.setInteger(0, FUND);
        pst.setInteger(1, ACCOUNTDETAILTYPE);
        pst.setInteger(2, ACCOUNTDETAILKEY);
        pst.setInteger(3, status);
        pst.setString(4, vDate);
        rs = pst.list();
        for (final Object[] elementB : rs)
            hmB.put(elementB[0].toString(), new BigDecimal(elementB[1].toString()));
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("map size -------> " + hmB.size());

        if (hmA.size() == 0)
            return hmB;
        else if (hmB.size() == 0) {
            final Set<Map.Entry<String, BigDecimal>> setA = hmA.entrySet();
            for (final Map.Entry<String, BigDecimal> meA : setA)
                hmFinal.put(meA.getKey(), meA.getValue().multiply(new BigDecimal(-1)));
            return hmFinal;
        }

        // Calculating the recovery amount as:
        // Recoveryamount=DEBITAMOUNT(query 2)- CREDITAMOUNT(query 1)

        hmFinal = hmB;
        final Set<Map.Entry<String, BigDecimal>> setA = hmA.entrySet();
        for (final Map.Entry<String, BigDecimal> meA : setA)
            if (hmFinal.containsKey(meA.getKey())) {
                final BigDecimal iC = hmFinal.get(meA.getKey()).subtract(meA.getValue());
                hmFinal.put(meA.getKey(), iC);
            } else
                hmFinal.put(meA.getKey(), meA.getValue().multiply(new BigDecimal(-1)));
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("hmCopy------>" + hmFinal);
    } catch (final Exception e) {
        LOGGER.error("Exception in getRecoveryForSubLedgerNotInFund():" + e);
        throw taskExc;
    } finally {
    }
    return hmFinal;
}

From source file:com.exilant.eGov.src.domain.GeneralLedger.java

License:Open Source License

/**
 * Function to get all the recoveries for a particular fund
 *
 * @param ACCOUNTDETAILTYPE//from w w  w.  ja v  a2s  .  c  om
 * @param ACCOUNTDETAILKEY
 * @param FUND
 * @param date
 * @param status
 * @return HashMap with account code as the key and the total pending recovery amount for that account code.
 * @throws SQLException
 * @throws TaskFailedException
 */
public HashMap getRecoveryForSubLedger(final Integer ACCOUNTDETAILTYPE, final Integer ACCOUNTDETAILKEY,
        final Integer FUND, final Date date, final int status) throws SQLException, TaskFailedException {
    final HashMap<String, BigDecimal> hmA = new HashMap<String, BigDecimal>();
    final HashMap<String, BigDecimal> hmB = new HashMap<String, BigDecimal>();
    HashMap<String, BigDecimal> hmFinal = new HashMap<String, BigDecimal>();
    Query pst = null;
    List<Object[]> rs = null;
    final SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy");
    final String vDate = formatter.format(date);
    try {

        // Query1 - to get the sum of credit amount glcode wise
        String selQuery = " SELECT GL.GLCODE as ACCOUNTCODE, SUM(GLD.AMOUNT) as CREDITAMOUNT FROM VOUCHERHEADER VH,GENERALLEDGER GL,GENERALLEDGERDETAIL GLD "
                + " WHERE VH.FUNDID= ?   AND GLD.DETAILTYPEID= ? AND DETAILKEYID= ? AND VH.STATUS= ? AND GL.CREDITAMOUNT>0 "
                + " AND VH.ID=GL.VOUCHERHEADERID AND GL.ID=GLD.GENERALLEDGERID AND VH.VOUCHERDATE<= ? GROUP BY GL.GLCODE";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("query (CreditAmount)--> " + selQuery);
        pst = persistenceService.getSession().createSQLQuery(selQuery);
        pst.setInteger(0, FUND);
        pst.setInteger(1, ACCOUNTDETAILTYPE);
        pst.setInteger(2, ACCOUNTDETAILKEY);
        pst.setInteger(3, status);
        pst.setString(4, vDate);
        rs = pst.list();
        for (final Object[] element : rs)
            hmA.put(element[0].toString(), new BigDecimal(element[1].toString()));
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("map size -------> " + hmA.size());

        // Query2 - to get the sum of debit amount glcode wise
        selQuery = "SELECT GL.GLCODE as GLCODE, SUM(GLD.AMOUNT) as DEBITAMOUNT FROM VOUCHERHEADER VH,GENERALLEDGER GL,GENERALLEDGERDETAIL GLD  "
                + "WHERE VH.FUNDID= ? AND GLD.DETAILTYPEID= ? AND DETAILKEYID= ? AND VH.STATUS= ? AND GL.DEBITAMOUNT>0 AND "
                + "VH.ID=GL.VOUCHERHEADERID AND GL.ID=GLD.GENERALLEDGERID AND VH.VOUCHERDATE<= ? GROUP BY GL.GLCODE";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("query (DebitAmount)--> " + selQuery);
        pst = persistenceService.getSession().createSQLQuery(selQuery);
        pst.setInteger(0, FUND);
        pst.setInteger(1, ACCOUNTDETAILTYPE);
        pst.setInteger(2, ACCOUNTDETAILKEY);
        pst.setInteger(3, status);
        pst.setString(4, vDate);
        rs = pst.list();
        for (final Object[] element : rs)
            hmB.put(element[0].toString(), new BigDecimal(element[1].toString()));
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("map size -------> " + hmB.size());

        if (hmA.size() == 0)
            return hmB;
        else if (hmB.size() == 0) {
            final Set<Map.Entry<String, BigDecimal>> setA = hmA.entrySet();
            for (final Map.Entry<String, BigDecimal> meA : setA)
                hmFinal.put(meA.getKey(), meA.getValue().multiply(new BigDecimal(-1)));
            return hmFinal;
        }

        hmFinal = hmB;
        final Set<Map.Entry<String, BigDecimal>> setA = hmA.entrySet();
        for (final Map.Entry<String, BigDecimal> meA : setA)
            if (hmFinal.containsKey(meA.getKey())) {
                final BigDecimal iC = hmFinal.get(meA.getKey()).subtract(meA.getValue());
                hmFinal.put(meA.getKey(), iC);
            } else
                hmFinal.put(meA.getKey(), meA.getValue().multiply(new BigDecimal(-1)));
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("hmCopy------>" + hmFinal);
    } catch (final Exception e) {
        LOGGER.error("Exception in getRecoveryForSubLedger():" + e);
        throw taskExc;
    } finally {
    }
    return hmFinal;
}