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.evolveum.midpoint.repo.sql.SqlRepositoryServiceImpl.java

License:Apache License

/**
 * This method provides object parsing from String and validation.
 *//*from   www .j  a v  a2  s  . c  om*/
private <T extends ObjectType> PrismObject<T> updateLoadedObject(GetObjectResult result, Class<T> type,
        Collection<SelectorOptions<GetOperationOptions>> options, Session session) throws SchemaException {

    String xml = RUtil.getXmlFromByteArray(result.getFullObject(), getConfiguration().isUseZip());
    PrismObject<T> prismObject;
    try {
        prismObject = getPrismContext().parseObject(xml);
    } catch (SchemaException e) {
        LOGGER.debug("Couldn't parse object because of schema exception ({}):\nObject: {}", e, xml);
        throw e;
    }

    if (UserType.class.equals(prismObject.getCompileTimeClass())) {
        if (SelectorOptions.hasToLoadPath(UserType.F_JPEG_PHOTO, options)) {
            //todo improve, use user.hasPhoto flag and take options into account [lazyman]
            //call this only when options contains INCLUDE user/jpegPhoto
            Query query = session.getNamedQuery("get.userPhoto");
            query.setString("oid", prismObject.getOid());
            byte[] photo = (byte[]) query.uniqueResult();
            if (photo != null) {
                PrismProperty property = prismObject.findOrCreateProperty(UserType.F_JPEG_PHOTO);
                property.setRealValue(photo);
            }
        }
    } else if (ShadowType.class.equals(prismObject.getCompileTimeClass())) {
        //we store it because provisioning now sends it to repo, but it should be transient
        prismObject.removeContainer(ShadowType.F_ASSOCIATION);

        LOGGER.debug("Loading definitions for shadow attributes.");

        Short[] counts = result.getCountProjection();
        Class[] classes = GetObjectResult.EXT_COUNT_CLASSES;

        for (int i = 0; i < classes.length; i++) {
            if (counts[i] == null || counts[i] == 0) {
                continue;
            }

            applyShadowAttributeDefinitions(classes[i], prismObject, session);
        }
        LOGGER.debug("Definitions for attributes loaded. Counts: {}", Arrays.toString(counts));
    }

    validateObjectType(prismObject, type);

    return prismObject;
}

From source file:com.evolveum.midpoint.repo.sql.SqlRepositoryServiceImpl.java

License:Apache License

private <T extends ShadowType> List<PrismObject<T>> listResourceObjectShadowsAttempt(String resourceOid,
        Class<T> resourceObjectShadowType, OperationResult result)
        throws ObjectNotFoundException, SchemaException {

    List<PrismObject<T>> list = new ArrayList<>();
    Session session = null;/*from  www .j a v a  2s . c om*/
    try {
        session = beginReadOnlyTransaction();
        Query query = session.getNamedQuery("listResourceObjectShadows");
        query.setString("oid", resourceOid);
        query.setResultTransformer(GetObjectResult.RESULT_TRANSFORMER);

        List<GetObjectResult> shadows = query.list();
        LOGGER.debug("Query returned {} shadows, transforming to JAXB types.",
                new Object[] { (shadows != null ? shadows.size() : 0) });

        if (shadows != null) {
            for (GetObjectResult shadow : shadows) {
                PrismObject<T> prismObject = updateLoadedObject(shadow, resourceObjectShadowType, null,
                        session);
                list.add(prismObject);
            }
        }
        session.getTransaction().commit();
    } catch (SchemaException | RuntimeException ex) {
        handleGeneralException(ex, session, result);
    } finally {
        cleanupSessionAndResult(session, result);
    }

    return list;
}

From source file:com.evolveum.midpoint.repo.sql.SqlRepositoryServiceImpl.java

License:Apache License

private <T extends ObjectType> String getVersionAttempt(Class<T> type, String oid, OperationResult result)
        throws ObjectNotFoundException, SchemaException {
    String version = null;/*from  www.  j av  a  2 s  .co m*/
    Session session = null;
    try {
        session = beginReadOnlyTransaction();
        Query query = session.getNamedQuery("getVersion");
        query.setString("oid", oid);

        Number versionLong = (Number) query.uniqueResult();
        if (versionLong == null) {
            throw new ObjectNotFoundException(
                    "Object '" + type.getSimpleName() + "' with oid '" + oid + "' was not found.");
        }
        version = versionLong.toString();
    } catch (RuntimeException ex) {
        handleGeneralRuntimeException(ex, session, result);
    } finally {
        cleanupSessionAndResult(session, result);
    }

    return version;
}

From source file:com.evolveum.midpoint.repo.sql.SqlRepositoryServiceImpl.java

License:Apache License

private boolean isAnySubordinateAttempt(String upperOrgOid, Collection<String> lowerObjectOids) {
    Session session = null;//from  w  w  w  .j  a  va  2 s. c  om
    try {
        session = beginTransaction();

        Query query;
        if (lowerObjectOids.size() == 1) {
            query = session.getNamedQuery("isAnySubordinateAttempt.oneLowerOid");
        } else {
            StringBuilder sb = new StringBuilder();
            sb.append("select count(*) from ROrgClosure o where ");

            sb.append('(');
            Iterator<String> iterator = lowerObjectOids.iterator();
            int paramIndex = 0;
            while (iterator.hasNext()) {
                iterator.next();
                sb.append("(o.ancestorOid=:aOid").append(paramIndex);
                sb.append(" and o.descendantOid=:dOid").append(paramIndex);
                sb.append(')');
                paramIndex++;
                if (iterator.hasNext()) {
                    sb.append(" or ");
                }
            }
            sb.append(')');

            query = session.createQuery(sb.toString());
        }

        Iterator<String> iterator = lowerObjectOids.iterator();
        int paramIndex = 0;
        while (iterator.hasNext()) {
            String subOid = iterator.next();
            query.setString("aOid" + paramIndex, upperOrgOid);
            query.setString("dOid" + paramIndex, subOid);
            paramIndex++;
        }

        Number number = (Number) query.uniqueResult();
        return (number != null && number.longValue() != 0L) ? true : false;
    } catch (RuntimeException ex) {
        handleGeneralException(ex, session, null);
    } finally {
        cleanupSessionAndResult(session, null);
    }

    throw new SystemException("isAnySubordinateAttempt failed somehow, this really should not happen.");
}

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

public String review(int id) {
    Session s = HibernateUtil.getSessionFactory().openSession();
    s.beginTransaction();//  w  ww. j  a v  a2s .  co m
    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.login.oneall.model.OneAllDAO.java

License:Open Source License

/**
 * Gets principal for user token.//from ww  w.  ja v a2s .  c o  m
 *
 * @param userToken the user token
 * @param provider the provider
 * @param domains the domains.  If empty, will return any principal within the system that is linked to the given token
 *
 * @return the principal for user token
 */
@Nullable
public Principal getPrincipalForUserToken(@Nonnull String userToken, @Nonnull String provider,
        @Nonnull AuthenticationDomainList domains) {
    if (!domains.isEmpty()) {
        return _principalDAO.getPrincipalByOpenAuth(OneAllLoginService.SERVICE_IDENTIFIER, userToken, provider,
                domains);
    } else {
        final String queryString = "select p from Principal p \n" + "inner join p.credentials cred \n"
                + "where cred.openAuthType = :openAuthType \n" + "and cred.openAuthId = :openAuthId \n"
                + "and cred.openAuthSubType = :openAuthSubType";
        return doInTransaction(session -> {
            Query query = getSession().createQuery(queryString);
            query.setParameter("openAuthType", OneAllLoginService.SERVICE_IDENTIFIER);
            query.setString("openAuthSubType", provider);
            query.setString("openAuthId", userToken);
            query.setMaxResults(1);
            return (Principal) query.uniqueResult();
        });
    }
}

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

License:Open Source License

public BigDecimal getAccountBalance(final Date VoucherDate, final String bankAccountId)
        throws TaskFailedException {
    BigDecimal totalAvailable = BigDecimal.ZERO;
    BigDecimal opeAvailable = BigDecimal.ZERO;
    Query pst = null;
    List<Object[]> resultset = null;
    List<Object[]> resultset1 = null;
    try {/*from  w w w. j a va2s.com*/
        final SimpleDateFormat formatter = dtFormat;
        final String vcDate = formatter.format(VoucherDate);

        final String str = "SELECT case when sum(openingDebitBalance) = null then 0 else sum(openingDebitBalance) end-  case when sum(openingCreditBalance) = 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);
        pst.setString(0, vcDate);
        pst.setString(1, vcDate);
        pst.setString(2, bankAccountId);
        resultset = pst.list();
        for (final Object[] element : resultset)
            opeAvailable = new BigDecimal(element[0].toString());
        if (resultset == null || resultset.size() == 0)
            if (LOGGER.isDebugEnabled())
                LOGGER.debug("Else resultset in getbalance");

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

        final String str1 = "SELECT (case when sum(gl.debitAmount) = null then 0 else sum(gl.debitAmount) end) - (case when sum(gl.creditAmount) = 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 TO_CHAR(startingDate, 'dd-Mon-yyyy') 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.setString(0, bankAccountId);
        pst.setString(1, vcDate);
        pst.setString(2, vcDate);
        pst.setString(3, vcDate);
        resultset1 = pst.list();
        for (final Object[] element : resultset1) {
            totalAvailable = new BigDecimal(element[0].toString());
            if (LOGGER.isDebugEnabled())
                LOGGER.debug("total balance  " + totalAvailable);
        }
        if (resultset1 == null || resultset1.size() == 0)
            if (LOGGER.isDebugEnabled())
                LOGGER.debug("Else resultset in getbalance...");

        totalAvailable = totalAvailable.setScale(2, BigDecimal.ROUND_HALF_UP);
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("total balance before return " + totalAvailable);

    } catch (final Exception e) {
        LOGGER.error(e.getMessage(), e);
        throw taskExc;
    }
    return totalAvailable;
}

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

License:Open Source License

/**
 * To get the EGW_STATUS id// www. j  ava 2s. c  o  m
 * @param con
 * @param moduleType
 * @param description
 * @return statusId
 */
public String getEGWStatusId(final String moduleType, final String description) throws TaskFailedException {
    String statusId = "0";
    Query pstmt = null;
    List<Object[]> rs = null;
    try {
        final String sql = " select distinct id from egw_status where upper(moduletype)= ? and upper(description)= ? ";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("statement" + sql);
        pstmt = persistenceService.getSession().createSQLQuery(sql);
        pstmt.setString(0, moduleType.toUpperCase());
        pstmt.setString(1, description.toUpperCase());
        rs = pstmt.list();
        for (final Object[] element : rs)
            statusId = element[0].toString();

        if (LOGGER.isDebugEnabled())
            LOGGER.debug("$$$$$$$$$$$$$$$$$$$statusId===" + statusId);
        if (statusId == null || statusId.equals("0"))
            throw taskExc;

    } catch (final Exception e) {
        LOGGER.error("Exception in getEGWStatusId=====:" + e.getMessage());
        throw taskExc;
    }
    return statusId;
}

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

License:Open Source License

@Override
public void execute(final String taskName, final String gridName, final DataCollection dc, final Connection con,
        final boolean errorOnNoData, final boolean gridHasColumnHeading, final String prefix)
        throws TaskFailedException {
    ////  w  w w .  j a  v a2 s .  c  o  m
    int noOfRec = 0;
    List<Object[]> rset = null;
    Query pst = null;

    final String cgn = dc.getValue("drillDownCgn");
    try {
        String relationType = "";
        String relationTypeID = "";
        String relationBillTable = "";
        String relationBillID = "";
        String chequeId = "";
        String sql = "select sph.type,sph.chequeid from subledgerpaymentheader sph,voucherheader  vh  where "
                + " sph.voucherheaderid=vh.id and vh.cgn= ?";
        pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, cgn);
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(sql);
        rset = pst.list();
        for (final Object[] element : rset) {
            relationType = element[0].toString();
            chequeId = element[1].toString();
        }
        dc.addValue("pay_hide", relationType);
        if (chequeId == null || chequeId.equals("0"))
            dc.addValue("subLedgerPaymentHeader_typeOfPayment", "Cash");
        else
            dc.addValue("subLedgerPaymentHeader_typeOfPayment", "Cheque");
        relationTypeID = relationType + "id";
        relationBillTable = relationType + "billdetail";
        relationBillID = relationType + "billid";
        sql = "select sph.type as \"pay_type\"," + relationTypeID + " as \"payToid\","
                + " paidby as \"paidByid\",bankaccountid as \"accId\",worksdetailid as \"worksDetailid\", "
                + "f.name as \"fund_name\",f.id as \"fund_id\",fsrc.name as \"fundSource_id\",fsrc.name as \"fundSource_name\" "
                + " from subledgerpaymentheader" + " sph,voucherheader  vh ,fund f ,fundsource fsrc where "
                + " sph.voucherheaderid=vh.id  and f.id=vh.fundid and fsrc.id=vh.fundsourceid"
                + " and vh.cgn= ?";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(sql);
        pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, cgn);
        rset = pst.list();
        for (final Object[] element : rset) {
            dc.addValue("pay_type", element[0].toString());
            dc.addValue("payToid", element[1].toString());
            dc.addValue("paidByid", element[2].toString());
            dc.addValue("accId", element[3].toString());
            dc.addValue("worksDetailid", element[4].toString());
            dc.addValue("fund_name", element[5].toString());
            dc.addValue("fund_id", element[6].toString());
            dc.addValue("fundSource_id", element[7].toString());
            dc.addValue("fundSource_name", element[8].toString());
        }
        // rset.close();
        // billcollector
        sql = "select a.name as \"paidBy\",b.glcode as \"billCollector_cashInHandDesc\" from billcollector a,chartofaccounts b where "
                + " a.cashinhand=b.id and a.id= ?";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(sql);
        pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, dc.getValue("paidByid"));
        rset = pst.list();
        for (final Object[] element : rset) {
            dc.addValue("paidBy", element[0].toString());
            dc.addValue("billCollector_cashInHandDesc", element[1].toString());
        }
        // rset.close();
        // supplier/contractor name
        sql = "select name  as \"payTo\" from relation where id= ?";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(sql);
        pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, dc.getValue("payToid"));
        rset = pst.list();
        for (final Object[] element : rset)
            dc.addValue("payTo", element[0].toString());
        // rset.close();
        // workorder
        sql = "select name  as \"worksDetail_id\" ,advanceamount as \"worksDetail_advanceAmount\" from worksDetail where id= ?";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(sql);
        pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, dc.getValue("worksDetailid"));
        rset = pst.list();
        for (final Object[] element : rset) {
            dc.addValue("worksDetail_id", element[0].toString());
            dc.addValue("worksDetail_advanceAmount", element[1].toString());
        }
        // rset.close();
        // bank name
        sql = "select a.name||' '||b.branchname as \"subLedgerPaymentHeader_bankId\" from bank a ,bankbranch b, bankaccount c  where"
                + " a.id=b.bankid and b.id=c.branchid and c.id= ?";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(sql);
        pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, dc.getValue("accId"));
        rset = pst.list();
        for (final Object[] element : rset)
            dc.addValue("subLedgerPaymentHeader_bankId", element[0].toString());
        // rset.close();
        // acount number
        sql = "select accountnumber as \"branchAccountId\" from bankaccount where id= ?";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(sql);
        pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, dc.getValue("accId"));
        rset = pst.list();
        for (final Object[] element : rset)
            dc.addValue("branchAccountId", element[0].toString());
        // rset.close();
        sql = "select count(*)" + " from " + relationBillTable + " a ,"
                + " voucherheader b ,subledgerpaymentheader sph  " + " where b.id=a.voucherheaderid  and "
                + " sph." + relationBillID + "=a.id and "
                + " sph.voucherheaderid =(select id from voucherheader where cgn= ?)"
                + " and passedamount>(a.paidamount+tdsamount+advadjamt)-sph.paidamount " + " and a."
                + relationTypeID + "= ? and b.fundid=" + " and a.worksdetailid= ?" + " order by a.billDate";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(sql);
        pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, cgn);
        pst.setString(1, dc.getValue("payToid"));
        pst.setString(2, dc.getValue("fund_id"));
        pst.setString(3, dc.getValue("worksDetailid"));
        rset = pst.list();
        for (final Object[] element : rset)
            noOfRec = Integer.parseInt(element[0].toString());
        // rset.close();
        if (noOfRec > 0) {
            final String[][] grid = new String[noOfRec + 1][13];
            sql = "select a.id as \"billNoId\",billNumber as\"billNo\",vouchernumber as \"d_voucherNo\" ,"
                    + "to_char(billdate,'dd-Mon-yyyy') as \"billDate\",a.PassedAmount as \"passedAmount\","
                    + " advadjamt as \"advance\",TDSamount as \"tds\",OtherRecoveries as \"otherRecoveries\","
                    + " a.passedAmount-(advadjamt+tdsamount+otherrecoveries) as \"net\","
                    + " a.PaidAmount-sph.paidamount as \"earlierPayment\" ,"
                    + " sph.paidamount as \"slph_paidAmount\"," +
                    // ((a.passedAmount-(advadjamt+tdsamount+otherrecoveries))-a.paidamount)
                    " rownum as \"slNo\" ,'1' as \"billSelect\" from " + relationBillTable + " a ,"
                    + " voucherheader b ,subledgerpaymentheader sph  " + " where b.id=a.voucherheaderid  and "
                    + " sph." + relationBillID + "=a.id and "
                    + " sph.voucherheaderid =(select id from voucherheader where cgn= ?)"
                    + " and passedamount>(a.paidamount+tdsamount+advadjamt)-sph.paidamount " + " and a."
                    + relationTypeID + "= ? and b.fundid= ?" + " and a.worksdetailid= ? order by a.billDate";
            if (LOGGER.isDebugEnabled())
                LOGGER.debug(sql);
            pst = persistenceService.getSession().createSQLQuery(sql);
            pst.setString(0, cgn);
            pst.setString(1, dc.getValue("payToid"));
            pst.setString(2, dc.getValue("fund_id"));
            pst.setString(3, dc.getValue("worksDetailid"));
            rset = pst.list();
            // grid[0][x] we filled control name
            for (final Object[] element : rset) {
                dc.addValue("billNoId", element[0].toString());
                dc.addValue("billNo", element[1].toString());
                dc.addValue("d_voucherNo", element[2].toString());
                dc.addValue("billDate", element[3].toString());
                dc.addValue("passedAmount", element[4].toString());
                dc.addValue("advance", element[5].toString());
                dc.addValue("tds", element[6].toString());
                dc.addValue("otherRecoveries", element[7].toString());
                dc.addValue("net", element[8].toString());
                dc.addValue("earlierPayment", element[9].toString());
                dc.addValue("slph_paidAmount", element[10].toString());
                dc.addValue("slNo", element[11].toString());
                dc.addValue("billSelect", element[12].toString());
            }
            int idx = 1;// grid[from 1][x] we start filling data
            for (final Object[] element : rset) {
                grid[idx][0] = element[0].toString();
                grid[idx][1] = element[1].toString();
                grid[idx][2] = element[2].toString();
                grid[idx][3] = element[3].toString();
                grid[idx][4] = element[4].toString();
                grid[idx][5] = element[5].toString();
                grid[idx][6] = element[6].toString();
                grid[idx][7] = element[7].toString();
                grid[idx][8] = element[8].toString();
                grid[idx][9] = element[9].toString();
                grid[idx][10] = element[10].toString();
                grid[idx][11] = element[11].toString();
                grid[idx][12] = element[12].toString();
                idx++;
            }
            dc.addGrid(gridName, grid);
        }
        sql = "select cgn as \"voucherHeader_cgn\",vouchernumber as \"voucherHeader_voucherNumber\",to_char(voucherdate,'dd-Mon-yyyy') as \"voucherHeader_voucherDate\","
                + " chequenumber as \"chequeDetail_chequeNumber\" ,to_char(chequedate,'dd-Mon-yyyy')  as \"chequeDetail_chequeDate\",vh.description as \"narration\",vh.fundsourceid as \"fundsource_id\" from voucherheader vh,subledgerpaymentheader sph,chequedetail cq where"
                + " sph.voucherheaderid=vh.id  and cq.id=sph.chequeid"
                + " and chequeid >0 and chequeid is not null  and vh.cgn= ?" + " union "
                + " select cgn as \"voucherHeader_cgn\",vouchernumber as \"voucherHeader_voucherNumber\",to_char(voucherdate,'dd-Mon-yyyy') as \"voucherHeader_voucherDate\",'','',vh.description as \"narration\",vh.fundsourceid as \"fundsource_id\" from voucherheader vh,subledgerpaymentheader sph  where"
                + " sph.voucherheaderid=vh.id " + " and (chequeid is  null or chequeid=0) and vh.cgn= ?";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(sql);
        pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, cgn);
        pst.setString(1, cgn);
        rset = pst.list();
        for (final Object[] element : rset) {
            dc.addValue("voucherHeader_cgn", element[0].toString());
            dc.addValue("voucherHeader_voucherNumber", element[1].toString());
            dc.addValue("voucherHeader_voucherDate", element[2].toString());
            dc.addValue("chequeDetail_chequeNumber", element[3].toString());
            dc.addValue("chequeDetail_chequeDate", element[4].toString());
            dc.addValue("subLedgerPaymentHeader_narration", element[5].toString());
            dc.addValue("fundsource_id", element[6].toString());
        }
        // rset.close();
        // st.close();
    } catch (final Exception e) {
        LOGGER.error("Error in executing query");
        throw taskExc;
    }

}

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

License:Open Source License

@Override
public void execute(final String taskName, final String gridName, final DataCollection dc, final Connection con,
        final boolean errorOnNoData, final boolean gridHasColumnHeading, final String prefix)
        throws TaskFailedException {
    ///*from www.  j  a  v  a2s . c o  m*/
    int noOfRec = 0;
    List<Object[]> rset = null;
    Query pst = null;
    final String cgn = dc.getValue("drillDownCgn");
    try {
        // String mmonth="",fundid="",fundSourceid="",chequeId="";
        String mmonth = "", chequeId = "";

        String sql = "select sbd.mmonth as \"salaryBillDetail_mmonth\" ,vh.fundid as \"fund_id\",vh.fundSourceid as \"fundSource_id\",sph.chequeid from salarybilldetail sbd,voucherheader  vh ,subledgerpaymentheader sph "
                + " where  sph.salarybillid=sbd.id and sph.voucherheaderid=vh.id and vh.cgn= ?";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(sql);
        pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, cgn);
        rset = pst.list();
        for (final Object[] element : rset) {
            mmonth = element[0].toString();
            // fundid=rset.getString(2);
            // fundSourceid=rset.getString(3);
            chequeId = element[3].toString();
        }
        // rset.close();
        if (chequeId == null || chequeId.equals("0"))
            dc.addValue("subLedgerPaymentHeader_typeOfPayment", "Cash");
        else
            dc.addValue("subLedgerPaymentHeader_typeOfPayment", "Cheque");
        sql = "select  paidby as \"subLedgerPaymentHeader_paidBy\",bankaccountid as \"accId\", "
                + " f.id as \"fund_id\", " + " fs.id as \"fundSource_id\" ,"
                + " paidto as \"chequeDetail_payTo\" ," + " from subledgerpaymentheader"
                + " sph,voucherheader  vh ,fund f,fundSource fs where "
                + " sph.voucherheaderid=vh.id  and f.id=vh.fundid and fs.id=vh.fundSourceid and vh.cgn= ?";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(sql);
        pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, cgn);
        rset = pst.list();
        for (final Object[] element : rset) {
            dc.addValue("subLedgerPaymentHeader_paidBy", element[0].toString());
            dc.addValue("accId", element[1].toString());
            dc.addValue("fund_id", element[2].toString());
            dc.addValue("fundSource_id", element[3].toString());
            dc.addValue("chequeDetail_payTo", element[4].toString());
        }

        sql = "select a.name as \"subLedgerPaymentHeader_paidBy\",c.glcode as \"billCollector_chequeInHandDesc\",b.glcode as \"billCollector_cashInHandDesc\" from billcollector a,chartofaccounts b,chartofaccounts c where "
                + " a.cashinhand=b.id and a.chequeinhand=c.id and b.id!=c.id and a.id= ?";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(sql);
        pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, dc.getValue("subLedgerPaymentHeader_paidBy"));
        rset = pst.list();
        for (final Object[] element : rset) {
            dc.addValue("subLedgerPaymentHeader_paidBy", element[0].toString());
            dc.addValue("billCollector_chequeInHandDesc", element[1].toString());
            dc.addValue("billCollector_cashInHandDesc", element[2].toString());
        }

        dc.addValue("salaryBillDetail_mmonth", mmonth);

        sql = "select  b.id as \"subLedgerPaymentHeader_bankId\" from bank a ,bankbranch b, bankaccount c  where"
                + " a.id=b.bankid and b.id=c.branchid and c.id= ?";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(sql);
        pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, dc.getValue("accId"));
        rset = pst.list();
        for (final Object[] element : rset)
            dc.addValue("subLedgerPaymentHeader_bankId", element[0].toString());
        dc.addValue("subLedgerPaymentHeader_branchAccountId", dc.getValue("accId"));

        sql = " select count(*) from salaryBillDetail s,voucherHeader v, subledgerpaymentheader sph "
                + " where   v.id=s.voucherHeaderId AND "
                + " sph.salarybillid=s.id and sph.voucherheaderid in(select id from voucherheader where cgn= ?)";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(sql);
        pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, cgn);
        rset = pst.list();
        for (final Object[] element : rset)
            noOfRec = Integer.parseInt(element[0].toString());

        if (noOfRec > 0) {
            final String[][] grid = new String[noOfRec + 1][7];
            sql = "select s.id as \"salaryBillDetail_id\","
                    + " v.id as \"voucherHeader_id\",v.voucherNumber as \"voucherHeader_voucherNumber1\","
                    + " to_char(v.voucherdate,'dd-mon-yyyy') as \"voucherHeader_voucherDate1\",s.grossPay as \"salaryBillDetail_grossPay\","
                    + " s.totalDeductions as \"salaryBillDetail_totalDed\",s.netPay as \"salaryBillDetail_netPay\" "
                    + " from salaryBillDetail s,voucherHeader v, subledgerpaymentheader sph where    v.id=s.voucherHeaderId AND"
                    + " sph.salarybillid=s.id and sph.voucherheaderid in(select id from voucherheader where cgn= ?) ";
            if (LOGGER.isDebugEnabled())
                LOGGER.debug(sql);
            pst = persistenceService.getSession().createSQLQuery(sql);
            pst.setString(0, cgn);
            rset = pst.list();

            for (final Object[] element : rset) {
                grid[0][0] = element[0].toString();
                grid[0][1] = element[1].toString();
                grid[0][2] = element[2].toString();
                grid[0][3] = element[3].toString();
                grid[0][4] = element[4].toString();
                grid[0][5] = element[5].toString();
                grid[0][6] = element[6].toString();
            }
            int idx = 1;// grid[from 1][x] we start filling data
            for (final Object[] element : rset) {
                grid[idx][0] = element[0].toString();
                grid[idx][1] = element[1].toString();
                grid[idx][2] = element[2].toString();
                grid[idx][3] = element[3].toString();
                grid[idx][4] = element[4].toString();
                grid[idx][5] = element[5].toString();
                grid[idx][6] = element[6].toString();
                idx++;
            }
            // rset.close();
            dc.addGrid(gridName, grid);
        }
        sql = "select cgn as \"voucherHeader_cgn\",vouchernumber as \"voucherHeader_voucherNumber\",to_char(voucherdate,'dd-mon-yyyy') as \"voucherHeader_voucherDate\","
                + " chequenumber as \"chequeDetail_chequeNumber\" ,to_char(chequedate,'dd-mon-yyyy')  as \"chequeDetail_chequeDate\",vh.description as \"narration\" from voucherheader vh,subledgerpaymentheader sph,chequedetail cq where"
                + " sph.voucherheaderid=vh.id  and cq.id=sph.chequeid"
                + " and chequeid is not null and chequeid>0 and vh.cgn= ?" + " union "
                + " select cgn as \"voucherHeader_cgn\",vouchernumber as \"voucherHeader_voucherNumber\",to_char(voucherdate,'dd-mon-yyyy') as \"voucherHeader_voucherDate\",'','',vh.description as \"narration\" from voucherheader vh,subledgerpaymentheader sph  where"
                + " sph.voucherheaderid=vh.id " + " and (chequeid is  null or chequeid =0 )and vh.cgn= ?";
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(sql);
        pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, cgn);
        pst.setString(1, cgn);
        rset = pst.list();
        for (final Object[] element : rset) {
            dc.addValue("voucherHeader_cgn", element[0].toString());
            dc.addValue("voucherHeader_voucherNumber", element[1].toString());
            dc.addValue("voucherHeader_voucherDate", element[2].toString());
            dc.addValue("chequeDetail_chequeNumber", element[3].toString());
            dc.addValue("chequeDetail_chequeDate", element[4].toString());
            dc.addValue("subLedgerPaymentHeader_narration", element[5].toString());
        }

    } catch (final Exception e) {
        LOGGER.error("exilError" + e.getMessage());
        throw taskExc;
    }
}