Example usage for org.hibernate Query setString

List of usage examples for org.hibernate Query setString

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

Introduction

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

Prototype

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

Source Link

Document

Bind a named String-valued parameter.

Usage

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 {//from   w  w w . j ava  2 s.co  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

@SuppressWarnings("deprecation")
@Transactional// ww  w.j a v  a2  s. c  o m
public void insert() throws SQLException, TaskFailedException {
    final EGovernCommon commommethods = new EGovernCommon();
    Query pst = null;
    try {
        effectiveDate = String.valueOf(commommethods.getCurrentDate());
        Date dt = new Date();
        final SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
        final SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy");
        dt = sdf.parse(effectiveDate);
        effectiveDate = formatter.format(dt);

        description = commommethods.formatString(description);
        setId(String.valueOf(PrimaryKeyGenerator.getNextKey("GeneralLedger")));

        if (functionId == null || functionId.equals(""))
            functionId = null;
        String insertQuery;
        insertQuery = "INSERT INTO generalledger (id, voucherLineID, effectiveDate, glCodeID, "
                + "glCode, debitAmount, creditAmount,";
        insertQuery += "description,voucherHeaderId,functionId) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        if (LOGGER.isInfoEnabled())
            LOGGER.info(insertQuery);
        pst = persistenceService.getSession().createSQLQuery(insertQuery);
        pst.setBigInteger(0, BigInteger.valueOf(Long.valueOf(id)));
        pst.setBigInteger(1,
                voucherLineId == null ? BigInteger.ZERO : BigInteger.valueOf(Long.valueOf(voucherLineId)));
        pst.setTimestamp(2, dt);
        pst.setBigInteger(3,
                glCodeId.equalsIgnoreCase("null") ? null : BigInteger.valueOf(Long.valueOf(glCodeId)));
        pst.setString(4, glCode);
        pst.setDouble(5, debitAmount.equalsIgnoreCase("null") ? null : Double.parseDouble(debitAmount));
        pst.setDouble(6, creditAmount.equalsIgnoreCase("null") ? null : Double.parseDouble(creditAmount));
        pst.setString(7, description);
        pst.setBigInteger(8, voucherHeaderId.equalsIgnoreCase("null") ? null
                : BigInteger.valueOf(Long.valueOf(voucherHeaderId)));
        pst.setBigInteger(9, functionId == null ? null : BigInteger.valueOf(Long.valueOf(functionId)));
        pst.executeUpdate();
    } catch (final Exception e) {
        LOGGER.error(e.getMessage(), e);
        throw taskExc;
    } finally {
    }

}

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

License:Open Source License

public void newUpdate() throws TaskFailedException, SQLException {
    Query pstmt = null;
    final StringBuilder query = new StringBuilder(500);
    query.append("update generalledger set ");
    if (voucherLineId != null)
        query.append("VOUCHERLINEID=?,");
    if (effectiveDate != null)
        query.append("EFFECTIVEDATE=?,");
    if (glCodeId != null)
        query.append("GLCODEID=?,");
    if (glCode != null)
        query.append("GLCODE=?,");
    if (debitAmount != null)
        query.append("DEBITAMOUNT=?,");
    if (creditAmount != null)
        query.append("CREDITAMOUNT=?,");
    if (description != null)
        query.append("DESCRIPTION=?,");
    if (voucherHeaderId != null)
        query.append("VOUCHERHEADERID=?,");
    if (functionId != null)
        query.append("FUNCTIONID=?,");
    final int lastIndexOfComma = query.lastIndexOf(",");
    query.deleteCharAt(lastIndexOfComma);
    query.append(" where id=?");
    try {/* w w  w .ja v  a2  s  .c o  m*/
        int i = 1;
        pstmt = persistenceService.getSession().createSQLQuery(query.toString());
        if (voucherLineId != null)
            pstmt.setString(i++, voucherLineId);
        if (effectiveDate != null)
            pstmt.setString(i++, effectiveDate);
        if (glCodeId != null)
            pstmt.setString(i++, glCodeId);
        if (glCode != null)
            pstmt.setString(i++, glCode);
        if (debitAmount != null)
            pstmt.setString(i++, debitAmount);
        if (creditAmount != null)
            pstmt.setString(i++, creditAmount);
        if (description != null)
            pstmt.setString(i++, description);
        if (voucherHeaderId != null)
            pstmt.setString(i++, voucherHeaderId);
        if (functionId != null)
            pstmt.setString(i++, functionId);
        pstmt.setString(i++, id);

        pstmt.executeUpdate();
    } catch (final Exception e) {
        LOGGER.error("Exp in update: " + e.getMessage());
        throw taskExc;
    }
}

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//from w w w .ja  v a2  s  . c  o  m
 * @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 .  j ava2s .  co  m*/
 * @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;
}

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

License:Open Source License

public void newUpdate() throws TaskFailedException, SQLException {
    lastModifiedDate = eGovernCommon.getCurrentDate();
    Query pstmt = null;
    try {/* ww  w . j  av  a 2 s.  c o m*/
        lastModifiedDate = formatter.format(sdf.parse(lastModifiedDate));
    } catch (final ParseException parseExp) {
        LOGGER.error("error inside newUpdate" + parseExp.getMessage(), parseExp);
    }
    setLastModifiedDate(lastModifiedDate);
    final StringBuilder query = new StringBuilder(500);
    query.append("update schedulemapping set ");
    if (reportType != null)
        query.append("REPORTTYPE=?,");
    if (schedule != null)
        query.append("SCHEDULE=?,");
    if (scheduleName != null)
        query.append("SCHEDULENAME=?,");
    if (createdBy != null)
        query.append("CREATEDBY=?,");
    if (createdDate != null && !createdDate.isEmpty())
        query.append("CREATEDDATE=?,");
    if (lastModifiedBy != null)
        query.append("LASTMODIFIEDBY=?,");
    if (lastModifiedDate != null)
        query.append("LASTMODIFIEDDATE=?,");
    if (repSubType != null)
        query.append("REPSUBTYPE=?,");
    if (isRemission != null)
        query.append("ISREMISSION=?,");
    final int lastIndexOfComma = query.lastIndexOf(",");
    query.deleteCharAt(lastIndexOfComma);
    query.append(" where id=?");
    try {
        int i = 1;
        pstmt = persistenceService.getSession().createSQLQuery(query.toString());
        if (reportType != null)
            pstmt.setString(i++, reportType);
        if (schedule != null)
            pstmt.setString(i++, schedule);
        if (scheduleName != null)
            pstmt.setString(i++, scheduleName);
        if (createdBy != null)
            pstmt.setString(i++, createdBy);
        if (createdDate != null && !createdDate.isEmpty())
            pstmt.setString(i++, createdDate);
        if (lastModifiedBy != null)
            pstmt.setString(i++, lastModifiedBy);
        if (lastModifiedDate != null)
            pstmt.setString(i++, lastModifiedDate);
        if (repSubType != null)
            pstmt.setString(i++, repSubType);
        if (isRemission != null)
            pstmt.setString(i++, isRemission);
        pstmt.setString(i++, id);

        pstmt.executeUpdate();
    } catch (final Exception e) {
        LOGGER.error("Exp in update: " + e.getMessage(), e);
        throw taskExc;
    }
}

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

License:Open Source License

public String getRole(final Connection con) throws TaskFailedException {
    // if(LOGGER.isDebugEnabled()) LOGGER.debug(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>domain user");
    final String query = "select r.Role_name as role from EG_ROLES r, EG_USER u,EG_USERROLE ur where u.user_name=? and ur.id_role=r.id_role and u.id_user=ur.id_user ";
    String role = "";
    try {//from  w w w .j a v  a2s. c o  m
        final Query ps = persistenceService.getSession().createSQLQuery(query);
        ps.setString(0, userName);
        final List<Object[]> rs = ps.list();
        for (final Object[] element : rs)
            role = element[0].toString();
    } catch (final Exception ex) {
        LOGGER.error("Task Failed Error" + ex.getMessage(), ex);
        throw new TaskFailedException();
    }
    return role;
}

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

License:Open Source License

public int getId() throws TaskFailedException {
    final String query = "select id_user from EG_USER where user_name=? ";
    int userId = 0;
    try {/*  ww  w .  j av  a2 s.  co  m*/
        final Query ps = persistenceService.getSession().createSQLQuery(query);
        ps.setString(0, userName);
        final List<Object[]> rs = ps.list();
        for (final Object[] element : rs)
            userId = Integer.parseInt(element[0].toString());
    } catch (final Exception ex) {
        LOGGER.error("EXP in getId" + ex.getMessage());
        throw new TaskFailedException();
    }
    return userId;
}

From source file:com.exilant.eGov.src.reports.GeneralLedgerReport.java

License:Open Source License

private String getAccountName(final String glCode) throws TaskFailedException {
    String accountName = "";
    Query pst = null;
    try {// www  .  j  a va  2 s . c o  m
        final String query = "select name as \"name\" from  CHARTOFACCOUNTS where GLCODE=?";
        pst = persistenceService.getSession().createSQLQuery(query);
        pst.setString(0, glCode);
        final List list = pst.list();
        final Object[] objects = list.toArray();
        accountName = objects[0].toString();

    } catch (final Exception e) {
        LOGGER.error("Exp in getAccountName:" + e.getMessage(), e);
        throw taskExc;
    }

    return accountName;
}

From source file:com.exilant.eGov.src.transactions.CommonMethodsImpl.java

License:Open Source License

/**
 * Get the property tax code for the year
 */// w w w.ja  v a  2  s .  c om
@Override
public String getPTCode(final String forYear, final Connection connection) throws Exception {
    String ptCodeAndName = "";
    try {
        if (!forYear.equalsIgnoreCase("")) {
            if (LOGGER.isInfoEnabled())
                LOGGER.info("  forYear  " + forYear);
            final String query1 = "select financialyear from financialyear  where ? between startingdate and endingdate";
            Query pst = persistenceService.getSession().createSQLQuery(query1);
            pst.setString(0, forYear);
            List<Object[]> rset = pst.list();
            String fId = "", isOld = "";
            for (final Object[] element : rset)
                fId = element[0].toString();
            if (rset == null || rset.size() == 0) {
                final String query2 = "select a.glcode,a.name from chartofaccounts a,egf_tax_account_mapping b where b.glcodeid=a.id and upper(b.financialyear)=upper('old')";
                pst = persistenceService.getSession().createSQLQuery(query2);
                rset = pst.list();
                for (final Object[] element : rset) {
                    ptCodeAndName = element[0].toString();
                    ptCodeAndName = ptCodeAndName + "#" + element[1].toString();
                    if (LOGGER.isInfoEnabled())
                        LOGGER.info(">>>ptCodeAndName " + ptCodeAndName);
                }
            }
            if (!fId.equalsIgnoreCase("")) {
                final String query3 = "select a.isold from egf_tax_account_mapping a,egf_tax_code b,financialyear c where a.taxcodeid=b.id and b.code='PT' and a.financialyear=c.financialyear and c.financialyear= ?";
                pst = persistenceService.getSession().createSQLQuery(query3);
                pst.setString(0, fId);
                rset = pst.list();
                for (final Object[] element : rset) {
                    if (LOGGER.isInfoEnabled())
                        LOGGER.info("   inside 2    ");
                    isOld = element[0].toString();
                }
                if (isOld != null && isOld.equals("1")) {
                    if (LOGGER.isInfoEnabled())
                        LOGGER.info("   inside 4    ");
                    final String query4 = "select a.glcode,a.name from chartofaccounts a,egf_tax_account_mapping b where b.glcodeid=a.id and upper(b.financialyear)=upper('old')";
                    pst = persistenceService.getSession().createSQLQuery(query4);
                    rset = pst.list();
                    for (final Object[] element : rset) {
                        ptCodeAndName = element[0].toString();
                        ptCodeAndName = ptCodeAndName + "#" + element[1].toString();
                        if (LOGGER.isInfoEnabled())
                            LOGGER.info(">>>ptCodeAndName** " + ptCodeAndName);
                    }
                } else {
                    if (LOGGER.isInfoEnabled())
                        LOGGER.info("   inside 5   ");
                    final String query5 = "select a.glcode,a.name from chartofaccounts a,egf_tax_account_mapping b,egf_tax_code c,financialyear d where b.taxcodeid=c.id and c.code='PT' and b.glcodeid=a.id and b.financialyear=d.financialyear and d.financialyear= ?";
                    pst = persistenceService.getSession().createSQLQuery(query5);
                    pst.setString(0, fId);
                    rset = pst.list();
                    for (final Object[] element : rset) {
                        ptCodeAndName = element[0].toString();
                        ptCodeAndName = ptCodeAndName + "#" + element[1].toString();
                        if (LOGGER.isInfoEnabled())
                            LOGGER.info(">>>ptCodeAndName " + ptCodeAndName);
                    }
                    if (rset == null || rset.size() == 0)
                        throw new Exception("Property Tax code not Found for " + forYear);

                }
            }
        } else {
            // if foryear is not given, then use Sespense code
            final String query = "select a.glcode, a.name from chartofaccounts a,egf_accountcode_purpose b where a.purposeid=b.id and upper(b.name)=upper('SuspenseCode')";
            Query pst = persistenceService.getSession().createSQLQuery(query);
            List<Object[]> rset = pst.list();
            for (final Object[] element : rset) {
                ptCodeAndName = element[0].toString();
                ptCodeAndName = ptCodeAndName + "#" + element[1].toString();
                if (LOGGER.isInfoEnabled())
                    LOGGER.info(">>>ptCodeAndName1 " + ptCodeAndName);
            }
            if (rset == null || rset.size() == 0)
                throw new Exception("Property Tax code not Found for " + forYear);
        }
    } catch (final Exception e) {
        LOGGER.error(" PT code not found " + e.toString(), e);
        throw new Exception(e.toString());
    }
    return ptCodeAndName;
}