Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. addScalar

Introduction

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

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

From source file:org.egov.egf.web.actions.budget.BudgetProposalDetailAction.java

License:Open Source License

@Action(value = "/budget/budgetProposalDetail-ajaxLoadFunctions")
public String ajaxLoadFunctions() {
    final String functionLists = "functionList";
    if (getBudgetDetail() != null && getBudgetDetail().getBudget() != null
            && getBudgetDetail().getExecutingDepartment() != null) {
        final Budget budget = budgetService.find("from Budget where id=?",
                getBudgetDetail().getBudget().getId());
        final String budgetName = budget.getName();

        final Integer deptId = getBudgetDetail().getExecutingDepartment().getId().intValue();

        String accountType;//from   w ww  .ja  va  2  s .  com
        accountType = budgetDetailHelper.accountTypeForFunctionDeptMap(budgetName);

        final String sqlStr = "select distinct (f.name)  as name,f.id as id  from eg_dept_functionmap m,function f where departmentid=:deptId"
                + " and  budgetaccount_Type=:accountType and f.id= m.functionid order by f.name";

        final SQLQuery sqlQuery = persistenceService.getSession().createSQLQuery(sqlStr);

        sqlQuery.setInteger("deptId", deptId).setString("accountType", accountType);
        sqlQuery.addScalar(NAME).addScalar("id", LongType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(CFunction.class));
        if (!sqlQuery.list().isEmpty())
            functionList = sqlQuery.list();
        else
            functionList = employeeService.getAllFunctions();
        if (functionList.isEmpty())
            dropdownData.put(functionLists, functionService.findAll());
        else
            dropdownData.put(functionLists, functionList);
    }
    return FUNCTION;
}

From source file:org.egov.egf.web.actions.budget.BudgetProposalDetailAction.java

License:Open Source License

@Action(value = "/budget/budgetProposalDetail-ajaxLoadBudgetGroups")
public String ajaxLoadBudgetGroups() {
    request.get("id");

    final String sqlStr = "select  distinct (bg.name) as name ,bg.id  as id from egf_budgetgroup bg where bg.isActive=true "
            + "  order  by bg.name";

    final SQLQuery sqlQuery = persistenceService.getSession().createSQLQuery(sqlStr);
    sqlQuery.addScalar(NAME).addScalar("id", LongType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(BudgetGroup.class));
    budgetGroupList = sqlQuery.list();//  w w w .  j av  a 2s .  co  m
    return BUDGETGROUP;
}

From source file:org.egov.egf.web.actions.report.TrialBalanceAction.java

License:Open Source License

private void gererateReportForAsOnDate()

{
    String voucherMisTable = "";
    String misClause = "";
    String misDeptCond = "";
    String tsDeptCond = "";
    String functionaryCond = "";
    String tsfunctionaryCond = "";
    String functionIdCond = "";
    String tsFunctionIdCond = "";
    String fieldIdCond = "";
    String tsFieldIdCond = "";
    String fundcondition = "";
    List<TrialBalanceBean> forAllFunds = new ArrayList<TrialBalanceBean>();

    if (rb.getFundId() != null)
        fundcondition = " and fundid=:fundId";
    else/*from  w  w w. j av a2 s.c  o m*/
        fundcondition = " and fundid in (select id from fund where isactive=true and isnotleaf!=true )";
    // if(LOGGER.isInfoEnabled()) LOGGER.info("fund cond query  "+fundcondition);
    if (null != rb.getDepartmentId() || null != rb.getFunctionaryId()) {
        voucherMisTable = ",vouchermis mis ";
        misClause = " and mis.voucherheaderid=vh.id ";
    }

    if (null != rb.getDepartmentId()) {
        misDeptCond = " and mis.DEPARTMENTID= :departmentId";
        tsDeptCond = " and DEPARTMENTID= :departmentId";
    }
    if (null != rb.getFunctionaryId()) {
        functionaryCond = " and mis.FUNCTIONARYID= :functionaryId";
        tsfunctionaryCond = " and FUNCTIONARYID= :functionaryId";
    }
    if (null != rb.getFunctionId()) {
        functionIdCond = " and gl.voucherheaderid in (select distinct(voucherheaderid) from generalledger where functionid =:functionId)";
        tsFunctionIdCond = " and FUNCTIONID= functionId";
    }
    if (null != rb.getDivisionId()) {
        fieldIdCond = " and mis.divisionId= :divisionId";
        tsFieldIdCond = " and divisionId= :divisionId";
    }
    String defaultStatusExclude = null;
    final List<AppConfigValues> listAppConfVal = appConfigValuesService.getConfigValuesByModuleAndKey("EGF",
            "statusexcludeReport");
    if (null != listAppConfVal)
        defaultStatusExclude = listAppConfVal.get(0).getValue();
    else
        throw new ApplicationRuntimeException("Exlcude statusses not  are not defined for Reports");
    final String query = " SELECT gl.glcode AS \"accCode\" ,coa.name AS \"accName\" ,vh.fundid AS \"fundId\",(SUM(debitamount)+SUM((SELECT case when SUM(OPENINGDEBITBALANCE)  is null  then 0 else SUM(OPENINGDEBITBALANCE) end FROM transactionsummary WHERE"
            + " financialyearid=(SELECT id FROM financialyear WHERE startingdate<=:toDate AND endingdate>=:toDate)"
            + " AND glcodeid =(SELECT id FROM chartofaccounts WHERE glcode=gl.glcode) AND fundid=vh.fundid"
            + fundcondition + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond
            + "))/COUNT(*))-"
            + " (SUM(creditamount)+SUM((SELECT  case when SUM(OPENINGCREDITBALANCE)  is null  then 0 else SUM(OPENINGCREDITBALANCE) end FROM"
            + " transactionsummary WHERE financialyearid=(SELECT id FROM financialyear  WHERE startingdate<=:toDate AND endingdate>=:toDate)"
            + " AND glcodeid =(SELECT id FROM chartofaccounts WHERE glcode=gl.glcode) AND fundid=vh.fundid"
            + fundcondition + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond
            + "))/COUNT(*) ) as \"amount\" " + " FROM generalledger gl,chartofaccounts   coa,voucherheader vh "
            + voucherMisTable + " WHERE coa.glcode=gl.glcode AND gl.voucherheaderid=vh.id" + misClause
            + " AND vh.status not in (" + defaultStatusExclude + ") "
            + " AND  vh.voucherdate<=:toDate AND vh.voucherdate>=(SELECT startingdate FROM financialyear WHERE  startingdate<=:toDate AND   endingdate>=:toDate) "
            + fundcondition + " " + misDeptCond + functionaryCond + functionIdCond + fieldIdCond
            + " GROUP BY gl.glcode,coa.name,vh.fundid    HAVING (SUM(debitamount)>0 OR SUM(creditamount)>0)    And"
            + " (SUM(debitamount)+SUM((SELECT case when SUM(OPENINGDEBITBALANCE) IS NULL then 0 else SUM(OPENINGDEBITBALANCE) end FROM"
            + " transactionsummary WHERE  financialyearid=(SELECT id FROM financialyear       WHERE startingdate <=:toDate"
            + " AND endingdate >=:toDate) AND glcodeid =(SELECT id FROM chartofaccounts WHERE glcode=gl.glcode) "
            + fundcondition + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond
            + "))/COUNT(*))-"
            + " (SUM(creditamount)+SUM((SELECT  case when SUM(OPENINGCREDITBALANCE) IS NULL then 0 else SUM(OPENINGCREDITBALANCE) end FROM"
            + " transactionsummary WHERE financialyearid=(SELECT id FROM financialyear    WHERE startingdate<=:toDate AND endingdate>=:toDate) "
            + " AND glcodeid =(SELECT id FROM chartofaccounts WHERE glcode=gl.glcode)  " + fundcondition
            + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond + "))/COUNT(*) )<>0" + " union"
            + " SELECT coa.glcode AS \"accCode\" ,coa.name AS \"accName\" , fu.id as \"fundId\", SUM((SELECT case when SUM(OPENINGDEBITBALANCE) IS NULL then 0 else SUM(OPENINGDEBITBALANCE) end "
            + " FROM transactionsummary WHERE financialyearid=(SELECT id FROM financialyear WHERE  startingdate<=:toDate AND endingdate>=:toDate)"
            + " AND glcodeid =(SELECT id FROM chartofaccounts WHERE  glcode=coa.glcode) AND fundid= (select id from fund where id=fu.id)"
            + " " + fundcondition + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond
            + ")) - SUM((SELECT  case when SUM(OPENINGCREDITBALANCE) IS NULL then 0 else SUM(OPENINGCREDITBALANCE) end as \"amount\" FROM transactionsummary WHERE"
            + " financialyearid=(SELECT id FROM financialyear       WHERE startingdate<=:toDate AND endingdate>=:toDate) AND glcodeid =(SELECT id FROM chartofaccounts"
            + " WHERE glcode=coa.glcode)AND fundid= (select id from fund where id=fu.id)" + fundcondition
            + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond + ")) "
            + " FROM chartofaccounts  coa, fund fu  WHERE  fu.id IN(SELECT fundid from transactionsummary WHERE financialyearid = (SELECT id FROM financialyear WHERE startingdate<=:toDate "
            + " AND endingdate>=:toDate) " + fundcondition + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond
            + tsFieldIdCond
            + " AND glcodeid =(SELECT id   FROM chartofaccounts WHERE  glcode=coa.glcode) ) AND coa.id NOT IN(SELECT glcodeid FROM generalledger gl,voucherheader vh "
            + voucherMisTable + " WHERE " + " vh.status not in (" + defaultStatusExclude + ") " + misClause
            + misDeptCond + functionaryCond + functionIdCond + fieldIdCond
            + " AND vh.id=gl.voucherheaderid AND vh.fundid=fu.id AND vh.voucherdate<=:toDate AND vh.voucherdate>=(SELECT startingdate FROM financialyear WHERE  startingdate<=:toDate AND   endingdate>=:toDate) "
            + fundcondition + ")" + " GROUP BY coa.glcode,coa.name, fu.id"
            + " HAVING((SUM((SELECT case when SUM(OPENINGDEBITBALANCE) IS NULL then 0 else SUM(OPENINGDEBITBALANCE) end FROM transactionsummary WHERE"
            + " financialyearid=(SELECT id FROM financialyear       WHERE startingdate<=:toDate AND endingdate>=:toDate) AND glcodeid =(SELECT id FROM chartofaccounts WHERE glcode=coa.glcode) "
            + fundcondition + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond + " )) >0 )"
            + " OR (SUM((SELECT  case when SUM(OPENINGCREDITBALANCE) IS NULL then 0 else SUM(OPENINGCREDITBALANCE) end FROM transactionsummary WHERE financialyearid=(SELECT id FROM financialyear WHERE startingdate<=:toDate AND endingdate>=:toDate)"
            + " AND glcodeid =(SELECT id FROM chartofaccounts WHERE glcode=coa.glcode)     " + fundcondition
            + tsDeptCond + tsfunctionaryCond + tsFunctionIdCond + tsFieldIdCond
            + "))>0 ))  ORDER BY \"accCode\"";
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("&&&query  " + query);
    try {
        new Double(0);
        final SQLQuery SQLQuery = persistenceService.getSession().createSQLQuery(query);
        SQLQuery.addScalar("accCode").addScalar("accName").addScalar("fundId", StringType.INSTANCE)
                .addScalar("amount", BigDecimalType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(TrialBalanceBean.class));
        if (null != rb.getFundId())
            SQLQuery.setInteger("fundId", rb.getFundId());
        if (null != rb.getDepartmentId())
            SQLQuery.setInteger("departmentId", rb.getDepartmentId());
        if (null != rb.getFunctionaryId())
            SQLQuery.setInteger("functionaryId", rb.getFunctionaryId());
        if (null != rb.getFunctionId())
            SQLQuery.setInteger("functionId", rb.getFunctionId());
        if (null != rb.getDivisionId())
            SQLQuery.setInteger("divisionId", rb.getDivisionId());
        if (null != rb.getFromDate())
            SQLQuery.setDate("fromDate", rb.getFromDate());
        SQLQuery.setDate("toDate", rb.getToDate());
        if (LOGGER.isInfoEnabled())
            LOGGER.info("query ---->" + SQLQuery);
        forAllFunds = SQLQuery.list();

    } catch (final Exception e) {
        LOGGER.error("Error in getReport" + e.getMessage(), e);

    }

    for (final Fund f : fundList)
        fundWiseTotalMap.put(f.getId() + "_amount", BigDecimal.ZERO);
    // List<>
    try {
        final Map<String, TrialBalanceBean> nonDuplicateMap = new LinkedHashMap<String, TrialBalanceBean>();

        for (final TrialBalanceBean tb : forAllFunds)
            if (nonDuplicateMap.containsKey(tb.getAccCode())) {
                // tb1=nonDuplicateMap.get(tb.getAccCode());

                if (tb.getAmount().signum() == -1) {
                    nonDuplicateMap.get(tb.getAccCode()).addToAmountMap(tb.getFundId() + "_amount",
                            numberToString(tb.getAmount().abs().toString()).toString() + " Cr");
                    if (nonDuplicateMap.get(tb.getAccCode()).getCreditAmount() != null)
                        nonDuplicateMap.get(tb.getAccCode()).setCreditAmount(
                                nonDuplicateMap.get(tb.getAccCode()).getCreditAmount().add(tb.getAmount()));
                    else
                        nonDuplicateMap.get(tb.getAccCode()).setCreditAmount(tb.getAmount());
                    totalAmount = fundWiseTotalMap.get(tb.getFundId() + "_amount")
                            .subtract(tb.getAmount().abs());
                    fundWiseTotalMap.put(tb.getFundId() + "_amount", totalAmount);

                } else if (tb.getAmount().signum() == 1) {
                    nonDuplicateMap.get(tb.getAccCode()).addToAmountMap(tb.getFundId() + "_amount",
                            numberToString(tb.getAmount().toString()).toString() + " Dr");
                    if (nonDuplicateMap.get(tb.getAccCode()).getDebitAmount() != null)
                        nonDuplicateMap.get(tb.getAccCode()).setDebitAmount(
                                nonDuplicateMap.get(tb.getAccCode()).getDebitAmount().add(tb.getAmount()));
                    else
                        nonDuplicateMap.get(tb.getAccCode()).setDebitAmount(tb.getAmount());
                    totalAmount = fundWiseTotalMap.get(tb.getFundId() + "_amount").add(tb.getAmount());
                    fundWiseTotalMap.put(tb.getFundId() + "_amount", totalAmount);

                }
            } else {
                if (tb.getAmount().signum() == -1) {
                    tb.addToAmountMap(tb.getFundId() + "_amount",
                            numberToString(tb.getAmount().abs().toString()).toString() + " Cr");
                    tb.setCreditAmount(tb.getAmount());
                    totalAmount = fundWiseTotalMap.get(tb.getFundId() + "_amount")
                            .subtract(tb.getAmount().abs());
                    fundWiseTotalMap.put(tb.getFundId() + "_amount", totalAmount);

                }

                else if (tb.getAmount().signum() == 1) {
                    tb.addToAmountMap(tb.getFundId() + "_amount",
                            numberToString(tb.getAmount().toString()).toString() + " Dr");
                    tb.setDebitAmount(tb.getAmount());
                    totalAmount = fundWiseTotalMap.get(tb.getFundId() + "_amount").add(tb.getAmount());
                    fundWiseTotalMap.put(tb.getFundId() + "_amount", totalAmount);
                }
                nonDuplicateMap.put(tb.getAccCode(), tb);

            }
        final Collection<TrialBalanceBean> values = nonDuplicateMap.values();
        for (final TrialBalanceBean tb : values) {
            if (tb.getDebitAmount() != null)
                tb.setDebit(numberToString(tb.getDebitAmount().toString()).toString() + " Dr");
            else
                tb.setDebit("0.00");
            if (tb.getCreditAmount() != null)
                tb.setCredit(numberToString(tb.getCreditAmount().abs().toString()).toString() + " Cr");
            else
                tb.setCredit("0.00");
            if (LOGGER.isDebugEnabled())
                LOGGER.debug(tb);
            if (tb.getDebitAmount() != null && tb.getCreditAmount() != null) {
                final BigDecimal add = tb.getDebitAmount().subtract(tb.getCreditAmount().abs());
                totalCreditAmount = totalCreditAmount.add(add);
                if (add.signum() == -1)
                    tb.setAmount1(numberToString(add.abs().toString()) + " Cr");
                else
                    tb.setAmount1(numberToString(add.toString()) + " Dr");
            } else if (tb.getDebitAmount() != null)
                tb.setAmount1(numberToString(tb.getDebitAmount().toString()) + " Dr");
            else if (tb.getCreditAmount() != null)
                tb.setAmount1(numberToString(tb.getCreditAmount().abs().toString()) + " Cr");
            else
                tb.setAmount1("0.00");

        }

        al.addAll(values);
        /*
         * for(TrialBalanceBean c:al) { if(LOGGER.isInfoEnabled()) LOGGER.info("Items Before Sorting"+c); }
         */
        Collections.sort(al, new COAcomparator());

        /*
         * for(TrialBalanceBean c:al) { if(LOGGER.isInfoEnabled()) LOGGER.info("Items After Sorting"+c); }
         */
        final TrialBalanceBean tbTotal = new TrialBalanceBean();
        tbTotal.setAccCode("Total");
        for (final String key : fundWiseTotalMap.keySet()) {
            String totalStr = "0.0";
            final BigDecimal total = fundWiseTotalMap.get(key);
            if (total != null && total.signum() == -1)
                totalStr = numberToString(total.abs().toString()) + " Cr";
            else if (total != null && total.signum() == 1)
                totalStr = numberToString(total.toString()) + " Dr";
            tbTotal.addToAmountMap(key, totalStr);

            if (totalCreditAmount != null && totalCreditAmount.signum() == -1)
                totalStr = numberToString(total.abs().toString()) + " Cr";
            else if (totalCreditAmount != null && totalCreditAmount.signum() == 1)
                totalStr = numberToString(total.toString()) + " Dr";
            tbTotal.setAmount1(totalStr);
        }

        al.add(tbTotal);

    } catch (final Exception e) {

    }

}

From source file:org.egov.services.deduction.ScheduledRemittanceService.java

License:Open Source License

/**
 *
 * @param dept//  www.  j a va 2  s  .  c  o  m
 * @param bankaccount
 * @return This query depends on back update of data remittancedate. If remittance date is null it will pick the amount else
 * it will ignore Voucher cancellation of remittance should reflect in setting remittancedate in gl to null This is taken
 * care. Should be maintained as same.
 */

private List<AutoRemittanceBean> getNonControleCodeReceiptRecoveries(final Integer dept,
        final int bankaccount) {

    final StringBuffer qry = new StringBuffer(2048);
    qry.append(" SELECT DISTINCT gl.id AS generalledgerId,  vh.fundid  AS fundId,  gl.debitAmount   "
            + "   AS gldtlAmount, " + bankaccount + " AS bankAccountId  "
            + " FROM VOUCHERHEADER vh ,  VOUCHERMIS mis,  GENERALLEDGER gl ,  VOUCHERHEADER payinslip,fund f,  "
            + " EGF_INSTRUMENTHEADER ih,EGF_INSTRUMENTOTHERDETAILS io , egcl_collectionvoucher cv,egcl_collectioninstrument ci, TDS recovery "
            + " WHERE  recovery.GLCODEID =gl.GLCODEID  AND vh.ID =gl.VOUCHERHEADERID"
            + " AND gl.remittanceDate    IS NULL AND mis.VOUCHERHEADERID   =vh.ID AND vh.STATUS =0 and vh.fundid=f.id "
            + " AND io.payinslipid =payinslip.id and io.instrumentheaderid=ih.id "
            + " and cv.voucherheaderid= vh.id and ci.collectionheaderid= cv.collectionheaderid and ci.instrumentmasterid= ih.id "
            + " and payinslip.status=0 AND ih.id_status NOT     IN  ("
            + " select id from egw_status where moduletype='Instrument' and  description in ('"
            + FinancialConstants.INSTRUMENT_CANCELLED_STATUS + "','"
            + FinancialConstants.INSTRUMENT_SURRENDERED_STATUS + "','"
            + FinancialConstants.INSTRUMENT_SURRENDERED_FOR_REASSIGN_STATUS + "') )" + " AND recovery.ID      ="
            + recovery.getId() + " AND payinslip.voucherdate    >= :startdate  ");
    if (lastRunDate != null)
        qry.append(" and  payinslip.voucherdate    <= :lastrundate");

    if (receiptFundCodes != null && !receiptFundCodes.isEmpty())
        qry.append(" and  f.code in (:fundCodes) ");

    final SQLQuery query = persistenceService.getSession().createSQLQuery(qry.toString());
    query.addScalar("generalledgerId", IntegerType.INSTANCE).addScalar("fundId", IntegerType.INSTANCE)
            .addScalar("gldtlAmount", DoubleType.INSTANCE).addScalar("bankAccountId", IntegerType.INSTANCE);
    if (lastRunDate != null)
        query.setDate("lastrundate", new java.sql.Date(lastRunDate.getTime()));

    if (startDate != null)
        query.setDate("startdate", new java.sql.Date(startDate.getTime()));
    if (receiptFundCodes != null && !receiptFundCodes.isEmpty())
        query.setParameterList("fundCodes", receiptFundCodes);
    query.setResultTransformer(Transformers.aliasToBean(AutoRemittanceBean.class));
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("ReceiptRecoveries query " + qry);
    return query.list();
}

From source file:org.egov.services.deduction.ScheduledRemittanceService.java

License:Open Source License

private Collection<? extends AutoRemittanceBean> getNonControleCodeGJVRecovries(final Integer dept,
        final int bankaccount) {
    final StringBuffer qry = new StringBuffer(2048);
    qry.append(" SELECT DISTINCT gl.id AS generalledgerId,  vh.fundid           AS fundId,  gl.creditamount   "
            + "   AS gldtlAmount, " + bankaccount + " AS bankAccountId "
            + " FROM VOUCHERHEADER vh ,  VOUCHERMIS mis,  GENERALLEDGER gl , fund f, TDS recovery "
            + " WHERE  recovery.GLCODEID =gl.GLCODEID  AND vh.ID =gl.VOUCHERHEADERID"
            + " AND gl.remittanceDate    IS NULL AND mis.VOUCHERHEADERID   =vh.ID AND vh.STATUS =0 and vh.fundid=f.id "
            + " and vh.name='" + FinancialConstants.JOURNALVOUCHER_NAME_GENERAL + "' and vh.moduleid is null "
            + " AND recovery.ID      =" + recovery.getId() + " AND vh.voucherdate    >= :startdate  ");
    if (lastRunDate != null)
        qry.append(" and  vh.voucherdate    <= :lastrundate");

    if (receiptFundCodes != null && !receiptFundCodes.isEmpty())
        qry.append(" and  f.code in (:fundCodes) ");

    final SQLQuery query = persistenceService.getSession().createSQLQuery(qry.toString());
    query.addScalar("generalledgerId", IntegerType.INSTANCE).addScalar("fundId", IntegerType.INSTANCE)
            .addScalar("gldtlAmount", DoubleType.INSTANCE).addScalar("bankAccountId", IntegerType.INSTANCE);
    if (lastRunDate != null)
        query.setDate("lastrundate", new java.sql.Date(lastRunDate.getTime()));

    if (startDate != null)
        query.setDate("startdate", new java.sql.Date(startDate.getTime()));
    if (receiptFundCodes != null && !receiptFundCodes.isEmpty())
        query.setParameterList("fundCodes", receiptFundCodes);
    query.setResultTransformer(Transformers.aliasToBean(AutoRemittanceBean.class));
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("ReceiptRecoveries query " + qry);
    return query.list();

}

From source file:org.egov.services.deduction.ScheduledRemittanceService.java

License:Open Source License

private List<AutoRemittanceBean> getNonControleCodeJVRecoveries(final Integer dept) {

    final StringBuffer qry = new StringBuffer(2048);
    qry.append(" SELECT DISTINCT gl.id AS generalledgerId,  vh.fundid           AS fundId,  gl.creditamount   "
            + "   AS gldtlAmount,ih.bankaccountid AS bankAccountId "
            + " FROM VOUCHERHEADER vh ,  VOUCHERMIS mis,  GENERALLEDGER gl ,  VOUCHERHEADER payment,  "
            + " EGF_INSTRUMENTHEADER ih, EGF_INSTRUMENTVOUCHER iv ,TDS recovery,miscbilldetail mb "
            + " WHERE  recovery.GLCODEID =gl.GLCODEID  AND vh.ID =gl.VOUCHERHEADERID "
            + " AND gl.remittanceDate    IS NULL AND mis.VOUCHERHEADERID   =vh.ID AND vh.STATUS =0  "
            + " AND ih.id =iv.instrumentheaderid "
            + " AND iv.voucherheaderid    =payment.id and payment.status=0 AND ih.id_status NOT     IN ("
            + "select id from egw_status where moduletype='Instrument' and description in ('"
            + FinancialConstants.INSTRUMENT_CANCELLED_STATUS + "','"
            + FinancialConstants.INSTRUMENT_SURRENDERED_STATUS + "','"
            + FinancialConstants.INSTRUMENT_SURRENDERED_FOR_REASSIGN_STATUS + "') "
            + " ) and mb.billvhid=vh.id and mb.payvhid=payment.id " + " AND recovery.ID      ="
            + recovery.getId() + "  ");

    if (lastRunDate != null)
        qry.append(" and (ih.instrumentdate<= :lastrundate or ih.transactiondate<=:lastrundate )");
    if (startDate != null)
        qry.append(" and (ih.instrumentdate >=:startdate or ih.transactiondate>=:startdate )");

    final SQLQuery query = persistenceService.getSession().createSQLQuery(qry.toString());
    query.addScalar("generalledgerId", IntegerType.INSTANCE).addScalar("fundId", IntegerType.INSTANCE)
            .addScalar("gldtlAmount", DoubleType.INSTANCE).addScalar("bankAccountId", IntegerType.INSTANCE);
    if (lastRunDate != null)
        query.setDate("lastrundate", new java.sql.Date(lastRunDate.getTime()));

    if (startDate != null)
        query.setDate("startdate", new java.sql.Date(startDate.getTime()));

    query.setResultTransformer(Transformers.aliasToBean(AutoRemittanceBean.class));
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("getNonControleCodeJVRecoveries query " + qry);
    return query.list();

}

From source file:org.egov.services.deduction.ScheduledRemittanceService.java

License:Open Source License

/**
 *
 * @param gjvBankAccountId/*from w ww.  j  av  a  2 s  .c  o  m*/
 * @param glcode will return all GJV recoveries which dont have payments attached to it fund condition is not added as we have
 * to search . Whatever mapped in app config should succed others should fail
 */
@SuppressWarnings("unchecked")
private List<AutoRemittanceBean> getGJVRecovries(final Integer deptId, final Integer gjvBankAccountId) {
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("Fetching GJVRecovries");

    final StringBuffer queryStr = new StringBuffer(
            "SELECT distinct gl.id as generalledgerId,  vh.fundid AS fundId,  egr.GLDTLAMT AS gldtlAmount,   gld.DETAILTYPEID  AS detailtypeId,"
                    + " gld.DETAILKEYID   AS detailkeyId,   egr.ID   AS remittanceGldtlId, " + gjvBankAccountId
                    + " as bankAccountId,  "
                    + " egr.GLDTLAMT- (SELECT case when SUM(egd.REMITTEDAMT) = NULL then 0 else SUM(egd.REMITTEDAMT) end     "
                    + " FROM EG_REMITTANCE_GLDTL egr1,     eg_remittance_detail egd,     eg_remittance eg,     voucherheader vh   WHERE vh.status!    =4  "
                    + " AND eg.PAYMENTVHID  =vh.id   AND egd.remittanceid=eg.id   AND egr1.id         =egd.remittancegldtlid  "
                    + " AND egr1.id         =egr.id   ) AS pendingAmount FROM VOUCHERHEADER vh ,  "
                    + " VOUCHERMIS mis,   GENERALLEDGER gl ,"
                    + " GENERALLEDGERDETAIL gld,   EG_REMITTANCE_GLDTL egr,   TDS recovery5_ WHERE recovery5_.GLCODEID =gl.GLCODEID AND"
                    + " gld.ID                =egr.GLDTLID AND gl.ID                 =gld.GENERALLEDGERID AND vh.ID   =gl.VOUCHERHEADERID "
                    + " and gl.remittanceDate is null "
                    + " AND mis.VOUCHERHEADERID   =vh.ID AND vh.STATUS=0 and vh.moduleid is null  and vh.name= '"
                    + FinancialConstants.JOURNALVOUCHER_NAME_GENERAL + "'" + " AND mis.departmentid  =  "
                    + deptId + " AND vh.moduleid is null"
                    + " AND egr.GLDTLAMT-   (SELECT case when SUM(egd.REMITTEDAMT) = NULL then 0 else SUM(egd.REMITTEDAMT) end   FROM EG_REMITTANCE_GLDTL egr1,  "
                    + " eg_remittance_detail egd,     eg_remittance eg,     voucherheader vh   WHERE vh.status !=4  "
                    + " AND eg.PAYMENTVHID   =vh.id   AND egd.remittanceid =eg.id   AND egr1.id          =egd.remittancegldtlid   "
                    + " AND egr1.id          =egr.id   )                   >0 AND recovery5_.ID      ="
                    + recovery.getId());

    if (lastRunDate != null)
        queryStr.append(" and vh.voucherdate<= '" + sdf.format(lastRunDate) + "' ");

    if (startDate != null)
        queryStr.append(" and vh.voucherdate>= '" + sdf.format(startDate) + "' ");
    final SQLQuery query = persistenceService.getSession().createSQLQuery(queryStr.toString());
    query.addScalar("generalledgerId", IntegerType.INSTANCE).addScalar("fundId", IntegerType.INSTANCE)
            .addScalar("gldtlAmount", DoubleType.INSTANCE).addScalar("detailtypeId", IntegerType.INSTANCE)
            .addScalar("detailkeyId", IntegerType.INSTANCE).addScalar("remittanceGldtlId", IntegerType.INSTANCE)
            .addScalar("pendingAmount", DoubleType.INSTANCE).addScalar("bankAccountId", IntegerType.INSTANCE);
    /*
     * if(lastRunDate!=null) { query.setDate("lastrundate", new java.sql.Date(lastRunDate.getTime())); } if(lastRunDate!=null)
     * { query.setDate("startdate", new java.sql.Date(startDate.getTime())); }
     */
    query.setResultTransformer(Transformers.aliasToBean(AutoRemittanceBean.class));
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("Query for GJVRecovries" + queryStr);
    return query.list();

}

From source file:org.egov.services.deduction.ScheduledRemittanceService.java

License:Open Source License

/**
 *
 * @param recoveryId Will return all voucher recoveries which are billpayment done and check also assigned
 * @param deptId//  ww w  . j  av  a 2 s  .  co m
 * @return
 */
@SuppressWarnings("deprecation")
private List<AutoRemittanceBean> getJVRecoveries(final Integer deptId) {
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("Fetching JVRecoveries");
    final StringBuffer queryStr = new StringBuffer(
            "SELECT distinct gl.id as generalledgerId,  vh.fundid AS fundId,  egr.GLDTLAMT AS gldtlAmount,   gld.DETAILTYPEID  AS detailtypeId,"
                    + " gld.DETAILKEYID   AS detailkeyId,   egr.ID   AS remittanceGldtlId,ih.bankaccountid as bankAccountId,  "
                    + " egr.GLDTLAMT- (SELECT case when SUM(egd.REMITTEDAMT) = NULL then 0 else SUM(egd.REMITTEDAMT) end    "
                    + " FROM EG_REMITTANCE_GLDTL egr1,     eg_remittance_detail egd,     eg_remittance eg,     voucherheader vh   WHERE vh.status!    =4  "
                    + " AND eg.PAYMENTVHID  =vh.id   AND egd.remittanceid=eg.id   AND egr1.id         =egd.remittancegldtlid  "
                    + " AND egr1.id         =egr.id   ) AS pendingAmount FROM VOUCHERHEADER vh left outer JOIN miscbilldetail mb on vh.id=mb.billvhid ,  "
                    + " VOUCHERMIS mis,   GENERALLEDGER gl,   voucherheader ph,   egf_instrumentheader ih,   egf_instrumentvoucher iv ,"
                    + " GENERALLEDGERDETAIL gld,   EG_REMITTANCE_GLDTL egr,   TDS recovery5_ WHERE recovery5_.GLCODEID =gl.GLCODEID AND"
                    + " gld.ID                =egr.GLDTLID AND gl.ID                 =gld.GENERALLEDGERID AND vh.ID   =gl.VOUCHERHEADERID "
                    + " and gl.remittanceDate is null "
                    + " AND mis.VOUCHERHEADERID   =vh.ID AND vh.STATUS=0 AND mb.payvhid =ph.id AND ih.id =iv.instrumentheaderid "
                    + " AND iv.voucherheaderid    =ph.id and ph.status!=4 AND ih.id_status NOT    IN ("
                    + " select id from egw_status where moduletype='Instrument' and description in ('"
                    + FinancialConstants.INSTRUMENT_CANCELLED_STATUS + "','"
                    + FinancialConstants.INSTRUMENT_SURRENDERED_STATUS + "','"
                    + FinancialConstants.INSTRUMENT_SURRENDERED_FOR_REASSIGN_STATUS + "') "
                    + " ) AND mis.departmentid  =  " + deptId
                    + " AND egr.GLDTLAMT-   (SELECT case when SUM(egd.REMITTEDAMT) = NULL then 0 else SUM(egd.REMITTEDAMT) end   FROM EG_REMITTANCE_GLDTL egr1,  "
                    + " eg_remittance_detail egd,     eg_remittance eg,     voucherheader vh   WHERE vh.status !=4  "
                    + " AND eg.PAYMENTVHID   =vh.id   AND egd.remittanceid =eg.id   AND egr1.id          =egd.remittancegldtlid   "
                    + " AND egr1.id          =egr.id   )                   >0 AND recovery5_.ID      ="
                    + recovery.getId());
    if (lastRunDate != null)
        queryStr.append(" and (ih.instrumentdate<='" + sdf.format(lastRunDate) + "'  or ih.transactiondate<='"
                + sdf.format(lastRunDate) + "') ");
    if (startDate != null)
        queryStr.append(" and (ih.instrumentdate>='" + sdf.format(startDate) + "' or ih.transactiondate>='"
                + sdf.format(startDate) + "' ) ");
    final SQLQuery query = persistenceService.getSession().createSQLQuery(queryStr.toString());
    query.addScalar("generalledgerId", IntegerType.INSTANCE).addScalar("fundId", IntegerType.INSTANCE)
            .addScalar("gldtlAmount", DoubleType.INSTANCE).addScalar("detailtypeId", IntegerType.INSTANCE)
            .addScalar("detailkeyId", IntegerType.INSTANCE).addScalar("remittanceGldtlId", IntegerType.INSTANCE)
            .addScalar("pendingAmount", DoubleType.INSTANCE).addScalar("bankAccountId", IntegerType.INSTANCE);
    /*
     * if(lastRunDate!=null) { query.setDate("lastrundate", new java.sql.Date(lastRunDate.getTime())); } if(lastRunDate!=null)
     * { query.setDate("startdate", new java.sql.Date(startDate.getTime())); }
     */
    query.setResultTransformer(Transformers.aliasToBean(AutoRemittanceBean.class));
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("JVRecoveries query " + queryStr);
    return query.list();

}

From source file:org.egov.services.deduction.ScheduledRemittanceService.java

License:Open Source License

/**
 *
 * @param recoveryId Will return all receipt recoveries which are Remitted and approved
 * @param deptId//from w w w .  ja  v a2 s .c om
 * @param lastRunDate
 * @param startDate
 * @param receiptBankAccountId
 * @return
 *
 */
private List getReceiptRecoveries(final Integer deptId, final Integer receiptBankAccountId) {

    if (LOGGER.isDebugEnabled())
        LOGGER.debug("Fetching ReceiptRecoveries");
    final StringBuffer queryStr = new StringBuffer(
            "SELECT distinct gl.id as generalledgerId,  vh.fundid AS fundId,  egr.GLDTLAMT AS gldtlAmount,   gld.DETAILTYPEID  AS detailtypeId,"
                    + " gld.DETAILKEYID   AS detailkeyId,   egr.ID   AS remittanceGldtlId,"
                    + receiptBankAccountId + " as bankAccountId, "
                    + " egr.GLDTLAMT- (SELECT case when SUM(egd.REMITTEDAMT) = NULL then 0 else SUM(egd.REMITTEDAMT) end    "
                    + " FROM EG_REMITTANCE_GLDTL egr1,     eg_remittance_detail egd,     eg_remittance eg,     voucherheader vh   WHERE vh.status!    =4  "
                    + " AND eg.PAYMENTVHID  =vh.id   AND egd.remittanceid=eg.id   AND egr1.id         =egd.remittancegldtlid  "
                    + " AND egr1.id         =egr.id   ) AS pendingAmount FROM VOUCHERHEADER vh ,  "
                    + " VOUCHERMIS mis,   GENERALLEDGER gl,   voucherheader payinslip, fund f,  egf_instrumentheader ih,  egf_instrumentotherdetails io,"
                    + " GENERALLEDGERDETAIL gld,   EG_REMITTANCE_GLDTL egr,  egcl_collectionvoucher cv, egcl_collectioninstrument ci,TDS recovery5_ WHERE recovery5_.GLCODEID =gl.GLCODEID AND"
                    + " gld.ID                =egr.GLDTLID AND gl.ID                 =gld.GENERALLEDGERID AND vh.ID   =gl.VOUCHERHEADERID "
                    + " and gl.remittanceDate is null and f.id=vh.fundid "
                    + " AND mis.VOUCHERHEADERID   =vh.ID AND vh.STATUS=0  AND io.payinslipid =payinslip.id "
                    + " and cv.voucherheaderid= vh.id    and ci.collectionheaderid= cv.collectionheaderid and ci.instrumentmasterid= ih.id"
                    + " and payinslip.status=0 AND ih.id_status NOT     IN ("
                    + "select id from egw_status where moduletype='Instrument' and description in ('"
                    + FinancialConstants.INSTRUMENT_CANCELLED_STATUS + "','"
                    + FinancialConstants.INSTRUMENT_SURRENDERED_STATUS + "','"
                    + FinancialConstants.INSTRUMENT_SURRENDERED_FOR_REASSIGN_STATUS + "') "
                    + " ) AND mis.departmentid  =  " + deptId
                    + " AND egr.GLDTLAMT-   (SELECT case when SUM(egd.REMITTEDAMT) = NULL then 0 else SUM(egd.REMITTEDAMT) end   FROM EG_REMITTANCE_GLDTL egr1,  "
                    + " eg_remittance_detail egd,     eg_remittance eg,     voucherheader vh   WHERE vh.status !=4  "
                    + " AND eg.PAYMENTVHID   =vh.id   AND egd.remittanceid =eg.id   AND egr1.id          =egd.remittancegldtlid   "
                    + " AND egr1.id          =egr.id   )                   >0 AND recovery5_.ID      ="
                    + recovery.getId());
    if (lastRunDate != null)
        queryStr.append(" and payinslip.voucherdate<='" + sdf.format(lastRunDate) + "' ");

    if (startDate != null)
        queryStr.append(" and payinslip.voucherdate>='" + sdf.format(startDate) + "'");
    if (receiptFundCodes != null && !receiptFundCodes.isEmpty())
        queryStr.append(" and f.code in (:fundCodes) ");

    final SQLQuery query = persistenceService.getSession().createSQLQuery(queryStr.toString());
    query.addScalar("generalledgerId", IntegerType.INSTANCE).addScalar("fundId", IntegerType.INSTANCE)
            .addScalar("gldtlAmount", DoubleType.INSTANCE).addScalar("detailtypeId", IntegerType.INSTANCE)
            .addScalar("detailkeyId", IntegerType.INSTANCE).addScalar("remittanceGldtlId", IntegerType.INSTANCE)
            .addScalar("pendingAmount", DoubleType.INSTANCE).addScalar("bankAccountId", IntegerType.INSTANCE);
    /*
     * if(lastRunDate!=null) { query.setDate("lastrundate", new java.sql.Date(lastRunDate.getTime())); } if(startDate!=null) {
     * query.setDate("startdate", new java.sql.Date(startDate.getTime())); }
     */
    if (receiptFundCodes != null && !receiptFundCodes.isEmpty())
        query.setParameterList("fundCodes", receiptFundCodes);
    query.setResultTransformer(Transformers.aliasToBean(AutoRemittanceBean.class));
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("ReceiptRecoveries query " + queryStr);
    return query.list();

}

From source file:org.egov.services.report.LoanGrantService.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Object> schemeUtilizationBy(final Integer schemeId, final Integer subSchemeId, final Date fromDate,
        final Date toDate, final List<Integer> projectCodeIdList, final Integer fundId) {
    final Accountdetailtype detailType = (Accountdetailtype) find("from Accountdetailtype where upper(name)=?",
            "PROJECTCODE");
    final StringBuffer schemeUtilSql = new StringBuffer(512);
    String pcStr = "";
    if (projectCodeIdList != null && projectCodeIdList.size() > 0) {
        pcStr = projectCodeIdList.toString();
        pcStr = pcStr.replace("[", "(");
        pcStr = pcStr.replace("]", ")");
    }/*from   ww w . j ava  2  s .c om*/
    if (subSchemeId != null) {
        schemeUtilSql.append(
                "select ss.name as subScheme, pc.code as code, vh.voucherNumber as vouchernumber, vh.voucherDate as voucherdate, gld.amount as amount, gld.detailkeyid as id "
                        + "from voucherheader vh, generalledger gl, generalledgerdetail gld , egf_subscheme_project ssp, egw_projectcode pc"
                        + ",sub_scheme ss " + "   where "
                        + " vh.id= gl.voucherheaderid  and vh.status not in (1,2,4) "
                        + " and gl.id= gld.generalledgerid ");
        if (fromDate != null)
            schemeUtilSql.append(" and vh.voucherdate>='" + Constants.DD_MON_YYYYFORMAT.format(fromDate) + "'");
        if (toDate != null)
            schemeUtilSql.append(" and vh.voucherdate<='" + Constants.DD_MON_YYYYFORMAT.format(toDate) + "'");
        schemeUtilSql.append(" and gld.detailtypeid=" + detailType.getId()
                + " and gld.detailkeyid= ssp.projectcodeid " + " and ssp.subschemeid=" + subSchemeId
                + " and ss.id=" + subSchemeId + " and ss.id=ssp.subschemeid" + " and pc.id= gld.detailkeyid "
                + " and pc.id= ssp.projectcodeid and vh.fundid=" + fundId + " ");
        if (projectCodeIdList != null && projectCodeIdList.size() > 0)
            schemeUtilSql.append(" and ssp.projectcodeid in " + pcStr + " ");
        schemeUtilSql.append("ORDER by ss.name, pc.code,vh.voucherdate ");
    } else if (schemeId != null) {
        schemeUtilSql.append(
                "select ss.name as subScheme, pc.code as code, vh.voucherNumber as vouchernumber, vh.voucherDate as voucherdate, gld.amount as amount, gld.detailkeyid as id "
                        + "from voucherheader vh, generalledger gl, generalledgerdetail gld , egf_subscheme_project ssp, egw_projectcode pc"
                        + ",sub_scheme ss,scheme s " + "   where "
                        + " vh.id= gl.voucherheaderid  and vh.status not in (1,2,4) "
                        + " and gl.id= gld.generalledgerid ");
        if (fromDate != null)
            schemeUtilSql.append(" and vh.voucherdate>='" + Constants.DD_MON_YYYYFORMAT.format(fromDate) + "'");
        if (toDate != null)
            schemeUtilSql.append(" and vh.voucherdate<='" + Constants.DD_MON_YYYYFORMAT.format(toDate) + "'");
        schemeUtilSql.append(
                " and gld.detailtypeid=" + detailType.getId() + " and gld.detailkeyid= ssp.projectcodeid "
                        + " and ssp.subschemeid=ss.id" + " and ss.schemeid=s.id" + " and s.id=" + schemeId
                        + " and ss.id=ssp.subschemeid" + " and pc.id= gld.detailkeyid "
                        + " and pc.id= ssp.projectcodeid  and vh.fundid=" + fundId + " ");
        if (projectCodeIdList != null && projectCodeIdList.size() > 0)
            schemeUtilSql.append(" and ssp.projectcodeid in " + pcStr + " ");
        schemeUtilSql.append(" ORDER by ss.name, pc.code,vh.voucherdate ");
    }
    final String schemeUtilSqlQry = schemeUtilSql.toString();
    final SQLQuery schemeUtilQry = getSession().createSQLQuery(schemeUtilSqlQry);
    schemeUtilQry.addScalar("subScheme").addScalar("code").addScalar("voucherNumber").addScalar("voucherDate")
            .addScalar("amount", BigDecimalType.INSTANCE).addScalar("id", LongType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(LoanGrantBean.class));

    final List<Object> projecCodeResultList = schemeUtilQry.list();

    return projecCodeResultList;
}