Example usage for org.hibernate Query setDate

List of usage examples for org.hibernate Query setDate

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

Introduction

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

Prototype

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

Source Link

Document

Bind the val (time is truncated) of a given Date object to a named query parameter.

Usage

From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java

License:Open Source License

@Action(value = "/brs/autoReconciliation-generateReport")
@SuppressWarnings({ "unchecked", "deprecation" })
@Transactional(readOnly = true)/*from   w  w  w. j a  v a2s .c o  m*/
public String generateReport() {
    // bankStatments not in BankBook

    try {
        bankBookBalance = eGovernCommon.getAccountBalance(dateFormatter.format(toDate), accountId.toString())
                .setScale(2, BigDecimal.ROUND_HALF_UP);
    } catch (final HibernateException e) {
        throw new ApplicationRuntimeException(e.getMessage());
    } catch (final TaskFailedException e) {
        throw new ApplicationRuntimeException(e.getMessage());
    }
    bankAccount = (Bankaccount) persistenceService.find("from Bankaccount ba where id=?",
            Long.valueOf(accountId));
    final String statmentsNotInBankBookStr = "select id,txDate,instrumentNo,debit,credit,narration,type,action as \"errorCode\",errorMessage from "
            + TABLENAME + " where accountId=:accountId and txdate>=:fromDate "
            + " and txdate<=:toDate and reconciliationdate is null and (errorMesSage is null or errorMessage !=:multipleEntryErrorMessage)"
            + " order by  txDate ";
    final Query statmentsNotInBankBookQry = persistenceService.getSession()
            .createSQLQuery(statmentsNotInBankBookStr).addScalar("instrumentNo").addScalar("credit")
            .addScalar("debit").addScalar("txDate").addScalar("id", LongType.INSTANCE).addScalar("narration")
            .addScalar("type").addScalar("errorCode").addScalar("errorMessage")
            .setResultTransformer(Transformers.aliasToBean(AutoReconcileBean.class));

    statmentsNotInBankBookQry.setDate("fromDate", fromDate).setDate("toDate", toDate)
            .setString("multipleEntryErrorMessage", BRS_MESSAGE_MORE_THAN_ONE_MATCH)
            .setLong("accountId", accountId);
    statementsNotInBankBookList = statmentsNotInBankBookQry.list();
    notInBooktotalDebit = BigDecimal.ZERO;
    notInBooktotalCredit = BigDecimal.ZERO;
    notInBookNet = BigDecimal.ZERO;

    for (final AutoReconcileBean ab : statementsNotInBankBookList) {
        notInBooktotalDebit = notInBooktotalDebit.add(ab.getDebit() == null ? BigDecimal.ZERO : ab.getDebit());
        notInBooktotalCredit = notInBooktotalCredit
                .add(ab.getCredit() == null ? BigDecimal.ZERO : ab.getCredit());
    }
    notInBookNet = notInBooktotalCredit.subtract(notInBooktotalDebit);
    if (notInBookNet.compareTo(BigDecimal.ZERO) == -1)
        notInBookNetBal = notInBookNet + "(Dr)";
    else
        notInBookNetBal = notInBookNet + "(Cr)";
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("notInBookNet" + notInBookNet);

    final CFinancialYear finYearByDate = financialYearDAO.getFinYearByDate(toDate);
    finYearStartDate = finYearByDate.getStartingDate();

    String entriesNotInBankStamentStr = "select  instrumentnumber as \"instrumentNo\","
            + " instrumentdate as \"txDate\", instrumentamount as \"credit\",null as \"debit\", payto as \"narration\"  from egf_instrumentheader  where bankaccountid=:accountId and instrumentdate BETWEEN"
            + " :fromDate and :toDate and ispaycheque='0' and id_status=(select id from egw_status where moduletype='Instrument'  and description='Deposited')"
            + " and instrumentnumber is not null and instrumentamount is not null and instrumentnumber||'-'||instrumentamount not in (select  instrumentno||'-'|| credit from egf_brs_bankstatements "
            + " where accountid=:accountId and txdate between :fromDate and :toDate and action=:action and errorMessage =:multipleEntryErrorMessage  and instrumentno is not null and  credit is not null and credit>0) "
            + " union " + " select   instrumentnumber as \"instrumentNo\","
            + " instrumentdate as \"txDate\", instrumentamount \"debit\",null as \"credit\", payto as \"narration\" "
            + " from egf_instrumentheader where bankaccountid=:accountId and instrumentdate BETWEEN :fromDate and :toDate "
            + " and ispaycheque='1' and id_status=(select id from egw_status where moduletype='Instrument'  and description='New')"
            + " and  instrumentnumber is not null   and instrumentamount is not null and instrumentnumber||'-'||instrumentamount not in  (select  instrumentno||'-'|| debit from egf_brs_bankstatements"
            + " where accountid=:accountId and txdate between :fromDate and :toDate and action=:action and errorMessage =:multipleEntryErrorMessage  and instrumentno is not null and debit is not null and debit>0) order by \"txDate\"";
    Query entriesNotInBankStamentQry = persistenceService.getSession()
            .createSQLQuery(entriesNotInBankStamentStr).addScalar("instrumentNo").addScalar("credit")
            .addScalar("debit").addScalar("txDate").addScalar("narration")
            .setResultTransformer(Transformers.aliasToBean(AutoReconcileBean.class));

    entriesNotInBankStamentQry.setDate("fromDate", finYearStartDate).setDate("toDate", toDate)
            .setString("action", BRS_ACTION_TO_BE_PROCESSED_MANUALLY).setLong("accountId", accountId)
            .setString("multipleEntryErrorMessage", BRS_MESSAGE_MORE_THAN_ONE_MATCH);
    entriesNotInBankStament = entriesNotInBankStamentQry.list();

    /**
     * ---------------------------------------
     */

    notInStatementTotalDebit = BigDecimal.ZERO;
    notInStatementTotalCredit = BigDecimal.ZERO;
    notInStatementNet = BigDecimal.ZERO;
    for (final AutoReconcileBean ab : entriesNotInBankStament) {
        // LOGGER.error("notInStatementTotalDebit=="+notInStatementTotalDebit+"           "+ab.getDebit());
        notInStatementTotalDebit = notInStatementTotalDebit
                .add(ab.getDebit() == null ? BigDecimal.ZERO : ab.getDebit());
        LOGGER.error(
                "no=" + ab.getInstrumentNo() + " t =" + notInStatementTotalCredit + " a=" + ab.getCredit());
        notInStatementTotalCredit = notInStatementTotalCredit
                .add(ab.getCredit() == null ? BigDecimal.ZERO : ab.getCredit());
        // LOGGER.error("notInStatementTotalCredit=="+notInStatementTotalCredit+"           "+"notInStatementTotalDebit=="+notInStatementTotalDebit+"           count"+i);
    }
    LOGGER.error("notInStatementTotalCredit==" + notInStatementTotalCredit + "           "
            + "notInStatementTotalDebit==" + notInStatementTotalDebit);
    notInStatementNet = notInStatementTotalCredit.subtract(notInStatementTotalDebit);// this one will be reverse
    // LOGGER.error("notInStatementTotalCredit=="+notInStatementTotalCredit+"           "+"notInStatementTotalDebit=="+notInStatementTotalDebit
    // +"notInStatementNet                       "+notInStatementNet);
    // for match

    entriesNotInBankStamentStr = "select  sum(instrumentamount) as \"credit\"  from egf_instrumentheader  where bankaccountid=:accountId and instrumentdate BETWEEN"
            + " :fromDate and :toDate and ispaycheque='0' and id_status=(select id from egw_status where moduletype='Instrument'  and description='Deposited')"
            + " and instrumentnumber is not null and instrumentamount is not null and instrumentnumber||'-'||instrumentamount not in (select  instrumentno||'-'|| credit from egf_brs_bankstatements "
            + " where accountid=:accountId and txdate between :fromDate and :toDate and action=:action and errorMessage =:multipleEntryErrorMessage  and instrumentno is not null and  credit is not null and credit>0) "
            + " union " + " select   sum(instrumentamount) as \"credit\" "
            + " from egf_instrumentheader where bankaccountid=:accountId and instrumentdate BETWEEN :fromDate and :toDate "
            + " and ispaycheque='1' and id_status=(select id from egw_status where moduletype='Instrument'  and description='New')"
            + " and  instrumentnumber is not null   and instrumentamount is not null and instrumentnumber||'-'||instrumentamount not in  (select  instrumentno||'-'|| debit from egf_brs_bankstatements"
            + " where accountid=:accountId and txdate between :fromDate and :toDate and action=:action and errorMessage =:multipleEntryErrorMessage  and instrumentno is not null and debit is not null and debit>0) ";
    entriesNotInBankStamentQry = persistenceService.getSession().createSQLQuery(entriesNotInBankStamentStr)
            // .addScalar("instrumentNo")
            .addScalar("credit")
            // .addScalar("debit")
            // .addScalar("txDate")
            // .addScalar("narration")
            .setResultTransformer(Transformers.aliasToBean(AutoReconcileBean.class));

    entriesNotInBankStamentQry.setDate("fromDate", finYearStartDate).setDate("toDate", toDate)
            .setString("action", BRS_ACTION_TO_BE_PROCESSED_MANUALLY).setLong("accountId", accountId)
            .setString("multipleEntryErrorMessage", BRS_MESSAGE_MORE_THAN_ONE_MATCH);
    final List<AutoReconcileBean> entriesNotInBankStament1 = entriesNotInBankStamentQry.list();
    if (entriesNotInBankStament1.size() > 0) {
        notInStatementTotalCredit = entriesNotInBankStament1.get(0).getCredit();
        if (notInStatementTotalCredit == null)
            notInStatementTotalCredit = BigDecimal.ZERO;
    }
    if (entriesNotInBankStament1.size() > 1) {
        notInStatementTotalDebit = entriesNotInBankStament1.get(1).getCredit();
        if (notInStatementTotalDebit == null)
            notInStatementTotalDebit = BigDecimal.ZERO;
    }

    notInStatementNet = notInStatementTotalCredit.subtract(notInStatementTotalDebit);

    final String statmentsfoundButNotProcessed = "select id,txDate,instrumentNo,debit,credit,narration,type,action as \"errorCode\",errorMessage "
            + "from " + TABLENAME
            + " where accountId=:accountId and txdate>=:fromDate  and txdate<=:toDate and reconciliationdate is null "
            + " and  errorMessage =:multipleEntryErrorMessage order by  txDate ";
    final Query statmentsfoundButNotProcessedQry = persistenceService.getSession()
            .createSQLQuery(statmentsfoundButNotProcessed).addScalar("instrumentNo").addScalar("credit")
            .addScalar("debit").addScalar("txDate").addScalar("id", LongType.INSTANCE).addScalar("narration")
            .addScalar("type").addScalar("errorCode").addScalar("errorMessage")
            .setResultTransformer(Transformers.aliasToBean(AutoReconcileBean.class));

    statmentsfoundButNotProcessedQry.setDate("fromDate", fromDate).setDate("toDate", toDate)
            .setString("multipleEntryErrorMessage", BRS_MESSAGE_MORE_THAN_ONE_MATCH)
            .setLong("accountId", accountId);
    statementsFoundButNotProcessed = statmentsfoundButNotProcessedQry.list();
    notprocessedDebit = BigDecimal.ZERO;
    notprocessedCredit = BigDecimal.ZERO;
    notprocessedNet = BigDecimal.ZERO;

    for (final AutoReconcileBean ab : statementsFoundButNotProcessed) {
        LOGGER.error("notprocessedDebit==" + notprocessedDebit + "           " + ab.getDebit());
        notprocessedDebit = notprocessedDebit.add(ab.getDebit() == null ? BigDecimal.ZERO : ab.getDebit());
        LOGGER.error("notprocessedCredit==" + notprocessedCredit + "           " + ab.getCredit());
        notprocessedCredit = notprocessedCredit.add(ab.getCredit() == null ? BigDecimal.ZERO : ab.getCredit());
        LOGGER.error("notprocessedDebit==" + notprocessedDebit + "           " + "notprocessedCredit=="
                + notprocessedCredit);
    }
    LOGGER.error("notprocessedDebit==" + notprocessedDebit + "           " + "notprocessedCredit=="
            + notprocessedCredit);
    notprocessedNet = notprocessedCredit.subtract(notprocessedDebit);// this one will be reverse
    LOGGER.error("notprocessedDebit==" + notprocessedDebit + "           " + "notprocessedCredit=="
            + notprocessedCredit);
    totalNotReconciledAmount = notInStatementNet.add(notprocessedNet.negate());
    brsBalance = bankBookBalance.add(notInStatementNet).add(notInBookNet).add(notprocessedNet);
    return "report";

}

From source file:org.egov.egf.web.actions.payment.ConcurrenceReportAction.java

License:Open Source License

@Action(value = "/payment/concurrenceReport-ajaxLoadPaymentHeader")
public String ajaxLoadPaymentHeader() {
    grandTol = BigDecimal.ZERO;//from  www.  j  ava2s .c o m
    if (parameters.containsKey("bankAccount.id") && parameters.get("bankAccount.id")[0] != null) {
        final Integer id = Integer.valueOf(parameters.get("bankAccount.id")[0]);
        bankAccount = (Bankaccount) persistenceService.find("from Bankaccount where id=?", id);
        bankAccountExist = true;
        bankAccount.getBankbranch().getBank().getName();
    }
    if (parameters.containsKey("asOnDate") && parameters.get("asOnDate")[0] != null) {
        setDateData(parameters.get("asOnDate")[0], " ");
        final Query query = generateQuery();
        query.setDate("date", asOnDate)
                .setResultTransformer(Transformers.aliasToBean(ConcurrenceReportData.class));
        paymentHeaderList.addAll(query.list());
    } else if (parameters.containsKey("fromDate") && parameters.get("fromDate")[0] != null
            && parameters.containsKey("toDate") && parameters.get("toDate")[0] != null) {
        setDateData(parameters.get("fromDate")[0], parameters.get("toDate")[0]);
        final Query query = generateQuery();
        query.setDate("fromDate", fromDate).setDate("toDate", toDate)
                .setResultTransformer(Transformers.aliasToBean(ConcurrenceReportData.class));
        paymentHeaderList.addAll(query.list());
    }
    generatePaymentList();
    return "results";
}

From source file:org.egov.egf.web.actions.payment.DishonorChequeWorkflowAction.java

License:Open Source License

private void updateInstrumentDetailsAfterDishonor() {
    final InstrumentHeader instHeader = instrumentHeaderService.findByNamedQuery("INSTRUMENTHEADERBYID",
            dishonorChequeView.getInstrumentHeader().getId());
    // set the instrument status of dishonored state
    instHeader.setStatusId(getDishonoredStatus());
    instrumentHeaderService.persist(instHeader);
    final String instOtherDetailUpdate = "Update InstrumentOtherDetails iod set iod.dishonorBankRefNo=:refNo, iod.modifiedBy.id=:modifiedby , iod.modifiedDate=:modifiedDate , iod.instrumentStatusDate=:InstrumentUpdatedDate where "
            + " iod.instrumentHeaderId=:instrumentHeaderId ";
    final Query instOtherDetailUpdateQuery = persistenceService.getSession()
            .createQuery(instOtherDetailUpdate.toString());
    instOtherDetailUpdateQuery.setString("refNo", dishonorChequeView.getBankReferenceNumber());
    instOtherDetailUpdateQuery.setLong("modifiedby", ApplicationThreadLocals.getUserId().intValue());
    instOtherDetailUpdateQuery.setDate("modifiedDate", new Date());
    instOtherDetailUpdateQuery.setDate("InstrumentUpdatedDate", dishonorChequeView.getTransactionDate());
    instOtherDetailUpdateQuery.setLong("instrumentHeaderId", dishonorChequeView.getInstrumentHeader().getId());

    instOtherDetailUpdateQuery.executeUpdate();
}

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

License:Open Source License

private Query setParameterForBudgetAppDisplay(Query query, Date asOnDate, Date startDate) {
    if (function.getId() != null && function.getId() != -1) {
        query.setLong("functionId", function.getId());
    }/*  w w w  . ja  v a2  s. c  o m*/
    if (department.getId() != null && department.getId() != -1) {
        query.setLong("departmentId", department.getId());
    }
    if (fund.getId() != null && fund.getId() != -1) {
        query.setLong("fundId", fund.getId());
    }
    if (budgetGroup.getMinCode().getId() != null) {
        query.setLong("glCodeId", budgetGroup.getMinCode().getId());
    }
    if (asOnDate != null) {
        query.setDate("strAODate", asOnDate);
    }

    if (startDate != null) {
        query.setDate("strStDate", startDate);
    }

    return query;
}

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

License:Open Source License

@SuppressWarnings("unchecked")
@ValidationErrorPage(NEW)//from   ww  w  .j av  a  2s. c o m
@ReadOnly
@Action(value = "/report/rtgsIssueRegisterReport-search")
public String search() {
    searchResult = Boolean.TRUE;
    if (LOGGER.isDebugEnabled())
        LOGGER.debug(" Seraching RTGS result for given criteria ");

    final Query query = persistenceService.getSession().createSQLQuery(getQueryString().toString())
            .addScalar("ihId", BigDecimalType.INSTANCE).addScalar("rtgsNumber").addScalar("rtgsDate")
            .addScalar("vhId", BigDecimalType.INSTANCE).addScalar("paymentNumber").addScalar("paymentDate")
            .addScalar("paymentAmount").addScalar("department").addScalar("status").addScalar("bank")
            .addScalar("bankBranch").addScalar("dtId", BigDecimalType.INSTANCE)
            .addScalar("dkId", BigDecimalType.INSTANCE).addScalar("accountNumber");
    if (null == parameters.get("rtgsAssignedFromDate")[0]
            || parameters.get("rtgsAssignedFromDate")[0].equalsIgnoreCase(""))
        query.setDate("finStartDate", new java.sql.Date(fromDate.getTime()));
    if (LOGGER.isInfoEnabled())
        LOGGER.info("Search Query ------------>" + query);

    query.setResultTransformer(Transformers.aliasToBean(BankAdviceReportInfo.class));
    rtgsDisplayList = query.list();
    populateSubLedgerDetails();
    rtgsReportList.addAll(rtgsDisplayList);
    return "search";
}

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

License:Open Source License

private void getReportForDateRange() {

    if (LOGGER.isDebugEnabled())
        LOGGER.debug("Starting getTBReport | Getting result for Date Range");
    String voucherMisTable = "";
    String misClause = "";
    String misDeptCond = "";
    String tsDeptCond = "";
    String functionaryCond = "";
    String tsfunctionaryCond = "";
    String functionIdCond = "";
    String tsFunctionIdCond = "";
    String tsdivisionIdCond = "";
    String misdivisionIdCond = "";
    if (null != rb.getDepartmentId() || null != rb.getFunctionaryId() || null != rb.getDivisionId()) {
        voucherMisTable = ",vouchermis mis ";
        misClause = " and mis.voucherheaderid=vh.id ";
    }//w ww .ja  v a 2  s  . c o  m

    if (null != rb.getDepartmentId()) {
        misDeptCond = " and mis.DepartmentId= :departmentId";
        tsDeptCond = " and ts.DepartmentId= :departmentId";
    }
    if (null != rb.getFunctionaryId()) {
        functionaryCond = " and mis.FunctionaryId= :functionaryId";
        tsfunctionaryCond = " and ts.FunctionaryId= :functionaryId";
    }
    if (null != rb.getFunctionId()) {
        functionIdCond = " and gl.functionid =:functionId";
        tsFunctionIdCond = " and ts.FUNCTIONID= :functionId";
    }
    if (null != rb.getDivisionId()) {
        misdivisionIdCond = " and mis.divisionId= :divisionId";
        tsdivisionIdCond = " and ts.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");
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("get Opening balance for all account codes");
    // get Opening balance for all account codes
    final String openingBalanceStr = "SELECT coa.glcode AS accCode ,coa.name  AS accName, SUM(ts.openingcreditbalance) as creditOPB,"
            + "sum(ts.openingdebitbalance) as debitOPB"
            + " FROM transactionsummary ts,chartofaccounts coa,financialyear fy "
            + " WHERE ts.glcodeid=coa.id  AND ts.financialyearid=fy.id and ts.FundId=:fundId " + tsDeptCond
            + tsfunctionaryCond + tsFunctionIdCond + tsdivisionIdCond
            + " AND fy.startingdate<=:fromDate AND fy.endingdate>=:toDate "
            + " GROUP BY ts.glcodeid,coa.glcode,coa.name ORDER BY coa.glcode ASC";
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("Query Str" + openingBalanceStr);
    final Query openingBalanceQry = persistenceService.getSession().createSQLQuery(openingBalanceStr)
            .addScalar("accCode").addScalar("accName").addScalar("creditOPB", BigDecimalType.INSTANCE)
            .addScalar("debitOPB", BigDecimalType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(TrialBalanceBean.class));

    openingBalanceQry.setInteger("fundId", rb.getFundId());

    if (null != rb.getDepartmentId())
        openingBalanceQry.setInteger("departmentId", rb.getDepartmentId());
    if (null != rb.getFunctionaryId())
        openingBalanceQry.setInteger("functionaryId", rb.getFunctionaryId());
    if (null != rb.getFunctionId())
        openingBalanceQry.setInteger("functionId", rb.getFunctionId());
    if (null != rb.getDivisionId())
        openingBalanceQry.setInteger("divisionId", rb.getDivisionId());
    openingBalanceQry.setDate("fromDate", rb.getFromDate());
    openingBalanceQry.setDate("toDate", rb.getToDate());
    final List<TrialBalanceBean> openingBalanceList = openingBalanceQry.list();
    if (LOGGER.isInfoEnabled())
        LOGGER.info("Opening balance query ---->" + openingBalanceQry);

    if (LOGGER.isDebugEnabled())
        LOGGER.debug("get Opening balance for all account codes reulted in " + openingBalanceList.size());

    if (LOGGER.isDebugEnabled())
        LOGGER.debug("get till date balance for all account codes");
    // get till date balance for all account codes
    final String tillDateOPBStr = "SELECT coa.glcode AS accCode ,coa.name  AS accName, SUM(gl.creditAmount) as tillDateCreditOPB,sum(gl.debitAmount) as tillDateDebitOPB"
            + " FROM generalledger  gl,chartofaccounts coa,financialyear fy,Voucherheader vh " + voucherMisTable
            + " WHERE gl.glcodeid=coa.id and vh.id=gl.voucherheaderid  and vh.fundid=:fundId " + misClause
            + misDeptCond + functionaryCond + functionIdCond + misdivisionIdCond
            + " AND vh.voucherdate>=fy.startingdate AND vh.voucherdate<=:fromDateMinus1 "
            + " AND fy.startingdate<=:fromDate AND fy.endingdate>=:toDate" + " AND vh.status not in ("
            + defaultStatusExclude + ")" + " GROUP BY gl.glcodeid,coa.glcode,coa.name ORDER BY coa.glcode ASC";
    final Query tillDateOPBQry = persistenceService.getSession().createSQLQuery(tillDateOPBStr)
            .addScalar("accCode").addScalar("accName").addScalar("tillDateCreditOPB", BigDecimalType.INSTANCE)
            .addScalar("tillDateDebitOPB", BigDecimalType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(TrialBalanceBean.class));
    tillDateOPBQry.setInteger("fundId", rb.getFundId());

    if (null != rb.getDepartmentId())
        tillDateOPBQry.setInteger("departmentId", rb.getDepartmentId());
    if (null != rb.getFunctionaryId())
        tillDateOPBQry.setInteger("functionaryId", rb.getFunctionaryId());
    if (null != rb.getFunctionId())
        tillDateOPBQry.setInteger("functionId", rb.getFunctionId());
    if (null != rb.getDivisionId())
        tillDateOPBQry.setInteger("divisionId", rb.getDivisionId());

    tillDateOPBQry.setDate("fromDate", rb.getFromDate());
    // tillDateOPBQry.setDate("fromDate",rb.getFromDate());
    tillDateOPBQry.setDate("toDate", rb.getToDate());
    final Calendar cal = Calendar.getInstance();
    cal.setTime(rb.getFromDate());
    cal.add(Calendar.DATE, -1);
    tillDateOPBQry.setDate("fromDateMinus1", cal.getTime());
    final List<TrialBalanceBean> tillDateOPBList = tillDateOPBQry.list();
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("get till date balance for all account codes reulted in " + tillDateOPBList.size());
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("get current debit and credit sum for all account codes  ");
    // get current debit and credit sum for all account codes
    final String currentDebitCreditStr = "SELECT coa.glcode AS accCode ,coa.name  AS accName, SUM(gl.creditAmount) as creditAmount,sum(gl.debitAmount) as debitAmount"
            + " FROM generalledger gl,chartofaccounts coa,financialyear fy,Voucherheader vh " + voucherMisTable
            + " WHERE gl.glcodeid=coa.id and vh.id= gl.voucherheaderid AND  vh.fundid=:fundId " + misClause
            + misDeptCond + functionaryCond + functionIdCond + misdivisionIdCond
            + " AND vh.voucherdate>=:fromDate AND vh.voucherdate<=:toDate "
            + " AND fy.startingdate<=:fromDate AND fy.endingdate>=:toDate" + " AND vh.status not in ("
            + defaultStatusExclude + ") " + " GROUP BY gl.glcodeid,coa.glcode,coa.name ORDER BY coa.glcode ASC";
    final Query currentDebitCreditQry = persistenceService.getSession().createSQLQuery(currentDebitCreditStr)
            .addScalar("accCode").addScalar("accName").addScalar("creditAmount", BigDecimalType.INSTANCE)
            .addScalar("debitAmount", BigDecimalType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(TrialBalanceBean.class));
    currentDebitCreditQry.setInteger("fundId", rb.getFundId());
    if (null != rb.getDepartmentId())
        currentDebitCreditQry.setInteger("departmentId", rb.getDepartmentId());
    if (null != rb.getFunctionaryId())
        currentDebitCreditQry.setInteger("functionaryId", rb.getFunctionaryId());
    if (null != rb.getFunctionId())
        currentDebitCreditQry.setInteger("functionId", rb.getFunctionId());
    if (null != rb.getDivisionId())
        currentDebitCreditQry.setInteger("divisionId", rb.getDivisionId());
    currentDebitCreditQry.setDate("fromDate", rb.getFromDate());
    currentDebitCreditQry.setDate("toDate", rb.getToDate());

    final List<TrialBalanceBean> currentDebitCreditList = currentDebitCreditQry.list();
    if (LOGGER.isInfoEnabled())
        LOGGER.info("closing balance query ---->" + currentDebitCreditQry);
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("get current debit and credit sum for all account codes resulted in   "
                + currentDebitCreditList.size());
    final Map<String, TrialBalanceBean> tbMap = new LinkedHashMap<String, TrialBalanceBean>();
    totalClosingBalance = BigDecimal.ZERO;
    totalOpeningBalance = BigDecimal.ZERO;

    /**
     * out of 3 list put one(openingBalanceList) into Linked hash map with accountcode as key So that if other two lists has
     * entry for an account code it will be merged else new entry will added to map finally return the contents of the map as
     * list
     */
    if (!openingBalanceList.isEmpty())
        for (final TrialBalanceBean tb : openingBalanceList) {
            tb.setOpeningBalance(tb.getDebitOPB().subtract(tb.getCreditOPB()));
            tb.setClosingBalance(tb.getOpeningBalance());
            tbMap.put(tb.getAccCode(), tb);

        }
    for (final TrialBalanceBean tillDateTB : tillDateOPBList)
        if (null != tbMap.get(tillDateTB.getAccCode())) {
            final BigDecimal opb = tbMap.get(tillDateTB.getAccCode()).getOpeningBalance()
                    .add(tillDateTB.getTillDateDebitOPB().subtract(tillDateTB.getTillDateCreditOPB()));
            tbMap.get(tillDateTB.getAccCode()).setOpeningBalance(opb);
            tbMap.get(tillDateTB.getAccCode()).setClosingBalance(opb);

        } else {
            tillDateTB.setOpeningBalance(
                    tillDateTB.getTillDateDebitOPB().subtract(tillDateTB.getTillDateCreditOPB()));
            tillDateTB.setClosingBalance(tillDateTB.getOpeningBalance());
            tbMap.put(tillDateTB.getAccCode(), tillDateTB);
        }
    BigDecimal cb = BigDecimal.ZERO;
    for (final TrialBalanceBean currentAmounts : currentDebitCreditList)
        if (null != tbMap.get(currentAmounts.getAccCode())) {

            tbMap.get(currentAmounts.getAccCode()).setDebitAmount(currentAmounts.getDebitAmount());
            tbMap.get(currentAmounts.getAccCode()).setCreditAmount(currentAmounts.getCreditAmount());
            cb = tbMap.get(currentAmounts.getAccCode()).getOpeningBalance().add(currentAmounts.getDebitAmount())
                    .subtract(currentAmounts.getCreditAmount());
            tbMap.get(currentAmounts.getAccCode()).setClosingBalance(cb);
            if (LOGGER.isDebugEnabled())
                LOGGER.debug("old amounts" + totalOpeningBalance + "    " + totalClosingBalance);
            if (LOGGER.isDebugEnabled())
                LOGGER.debug("Current amounts" + tbMap.get(currentAmounts.getAccCode()).getOpeningBalance()
                        + "    " + cb);
            totalOpeningBalance = totalOpeningBalance
                    .add(tbMap.get(currentAmounts.getAccCode()).getOpeningBalance());
            totalClosingBalance = totalClosingBalance.add(cb);
            if (LOGGER.isDebugEnabled())
                LOGGER.debug("After Amounts" + totalOpeningBalance + "    " + totalClosingBalance);
        } else {
            currentAmounts.setOpeningBalance(BigDecimal.ZERO);
            cb = currentAmounts.getOpeningBalance().add(currentAmounts.getDebitAmount())
                    .subtract(currentAmounts.getCreditAmount());
            currentAmounts.setClosingBalance(cb);
            currentAmounts.setOpeningBalance(BigDecimal.ZERO);
            tbMap.put(currentAmounts.getAccCode(), currentAmounts);
            if (LOGGER.isDebugEnabled())
                LOGGER.debug("old getTBReport" + totalOpeningBalance + "    " + totalClosingBalance);
            if (LOGGER.isDebugEnabled())
                LOGGER.debug("Current amounts" + tbMap.get(currentAmounts.getAccCode()).getOpeningBalance()
                        + "    " + cb);
            totalClosingBalance = totalClosingBalance.add(cb);
            totalOpeningBalance = totalOpeningBalance.add(currentAmounts.getOpeningBalance());
            if (LOGGER.isDebugEnabled())
                LOGGER.debug("After getTBReport" + totalOpeningBalance + "    " + totalClosingBalance);

        }
    al.addAll(tbMap.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); }
     */
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("Exiting getTBReport" + totalOpeningBalance + "    " + totalClosingBalance);
}

From source file:org.egov.egf.web.actions.voucher.CancelVoucherAction.java

License:Open Source License

@ValidationErrorPage(value = SEARCH)
@SkipValidation/*from w w  w. j a v a2 s  .  co  m*/
@Action(value = "/voucher/cancelVoucher-update")
public String update() {
    CVoucherHeader voucherObj;

    final Date modifiedDate = new Date();
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("Inside CancelVoucher| cancelVoucherSubmit | Selected No of Vouchers for cancellation  ="
                + selectedVhs.length);
    final String cancelVhQuery = "Update CVoucherHeader vh set vh.status="
            + FinancialConstants.CANCELLEDVOUCHERSTATUS
            + ",vh.lastModifiedBy.id=:modifiedby, vh.lastModifiedDate=:modifiedDate   where vh.id=:vhId";
    final String cancelVhByCGNQuery = "Update CVoucherHeader vh set vh.status="
            + FinancialConstants.CANCELLEDVOUCHERSTATUS
            + ",vh.lastModifiedBy.id=:modifiedby , vh.lastModifiedDate=:modifiedDate where vh.refvhId=:vhId";
    final String cancelVhByRefCGNQuery = "Update CVoucherHeader vh set vh.status="
            + FinancialConstants.CANCELLEDVOUCHERSTATUS
            + ",vh.lastModifiedBy.id=:modifiedby , vh.lastModifiedDate=:modifiedDate where vh.voucherNumber=:vhNum";
    String voucherId = "";
    final Session session = persistenceService.getSession();
    for (int i = 0; i < selectedVhs.length; i++) {
        voucherObj = (CVoucherHeader) persistenceService.find("from CVoucherHeader vh where vh.id=?",
                selectedVhs[i]);
        final boolean value = cancelBillAndVoucher.canCancelVoucher(voucherObj);

        if (!value) {
            addActionMessage(getText("cancel.voucher.failure", new String[] { voucherObj.getVoucherNumber() }));
            continue;
        }
        voucherId = voucherObj.getId().toString();
        switch (voucherObj.getType()) {

        case FinancialConstants.STANDARD_VOUCHER_TYPE_JOURNAL: {

            final Query query = session.createQuery(cancelVhQuery);
            query.setLong("modifiedby", loggedInUser);
            query.setTimestamp("modifiedDate", modifiedDate);
            query.setLong("vhId", selectedVhs[i]);
            query.executeUpdate();
            // for old vouchers when workflow was not implemented
            if (voucherObj.getState() == null
                    && !voucherObj.getName().equals(FinancialConstants.JOURNALVOUCHER_NAME_GENERAL))
                cancelBill(selectedVhs[i]);
            else if (voucherObj.getState() != null
                    && !voucherObj.getName().equals(FinancialConstants.JOURNALVOUCHER_NAME_GENERAL))
                cancelBill(selectedVhs[i]);
            break;
        }
        case FinancialConstants.STANDARD_VOUCHER_TYPE_PAYMENT: {
            final Query query = session.createQuery(cancelVhQuery);
            query.setLong("vhId", selectedVhs[i]);
            query.setLong("modifiedby", loggedInUser);
            query.setTimestamp("modifiedDate", modifiedDate);
            query.executeUpdate();
            if (FinancialConstants.PAYMENTVOUCHER_NAME_REMITTANCE.equalsIgnoreCase(voucherObj.getName())) {
                int count = paymentService.backUpdateRemittanceDateInGL(voucherHeader.getId());
            }
            break;
        }
        case FinancialConstants.STANDARD_VOUCHER_TYPE_CONTRA: {
            final Query query = session.createQuery(cancelVhQuery);
            query.setLong("vhId", selectedVhs[i]);
            query.setLong("modifiedby", loggedInUser);
            query.setTimestamp("modifiedDate", modifiedDate);
            query.executeUpdate();
            if (FinancialConstants.CONTRAVOUCHER_NAME_INTERFUND.equalsIgnoreCase(voucherObj.getName())) {
                Long vhId;
                vhId = voucherObj.getId();
                final Query queryFnd = session.createQuery(cancelVhByCGNQuery);
                queryFnd.setLong("vhId", vhId);
                queryFnd.setLong("modifiedby", loggedInUser);
                queryFnd.setDate("modifiedDate", modifiedDate);
                queryFnd.executeUpdate();
            }
            break;
        }
        case FinancialConstants.STANDARD_VOUCHER_TYPE_RECEIPT: {
            final Query query = session.createQuery(cancelVhQuery);
            query.setLong("vhId", selectedVhs[i]);
            query.setLong("modifiedby", loggedInUser);
            query.setTimestamp("modifiedDate", modifiedDate);
            query.executeUpdate();
            break;
        }
        }
    }
    if (LOGGER.isDebugEnabled())
        LOGGER.debug(" Cancel Voucher | CancelVoucher | Vouchers Cancelled ");
    if (voucherId != "")
        addActionMessage(getText("Vouchers Cancelled Succesfully"));
    return SEARCH;
}

From source file:org.egov.lcms.transactions.service.SearchLegalCaseService.java

License:Open Source License

private Query setParametersToQuery(final LegalCaseSearchResult legalCaseSearchResultObj,
        final Query queryResult) {
    queryResult.setString("moduleType", LcmsConstants.MODULE_TYPE_LEGALCASE);
    if (StringUtils.isNotBlank(legalCaseSearchResultObj.getLcNumber()))
        queryResult.setString("lcNumber", legalCaseSearchResultObj.getLcNumber());
    if (StringUtils.isNotBlank(legalCaseSearchResultObj.getCaseNumber()))
        queryResult.setString("caseNumber", legalCaseSearchResultObj.getCaseNumber() + "%");
    if (legalCaseSearchResultObj.getCourtId() != null)
        queryResult.setInteger("court", legalCaseSearchResultObj.getCourtId());
    if (legalCaseSearchResultObj.getCasecategory() != null)
        queryResult.setInteger("casetype", legalCaseSearchResultObj.getCasecategory());
    if (legalCaseSearchResultObj.getCourtType() != null)
        queryResult.setInteger("courttype", legalCaseSearchResultObj.getCourtType());
    if (StringUtils.isNotBlank(legalCaseSearchResultObj.getStandingCouncil()))
        queryResult.setString("standingcoouncil", legalCaseSearchResultObj.getStandingCouncil() + "%");
    if (legalCaseSearchResultObj.getStatusId() != null)
        queryResult.setInteger("status", legalCaseSearchResultObj.getStatusId());

    if (legalCaseSearchResultObj.getCaseFromDate() != null)
        queryResult.setDate("fromdate", legalCaseSearchResultObj.getCaseFromDate());
    if (legalCaseSearchResultObj.getCaseToDate() != null)
        queryResult.setDate("toDate", legalCaseSearchResultObj.getCaseToDate());
    if (legalCaseSearchResultObj.getPetitionTypeId() != null)
        queryResult.setInteger("petiontionType", legalCaseSearchResultObj.getPetitionTypeId());
    if (legalCaseSearchResultObj.getReportStatusId() != null)
        queryResult.setInteger("reportStatus", legalCaseSearchResultObj.getReportStatusId());
    if (legalCaseSearchResultObj.getIsStatusExcluded() != null) {
        final List<String> statusCodeList = new ArrayList<>();
        statusCodeList.add(LcmsConstants.LEGALCASE_STATUS_CLOSED);
        statusCodeList.add(LcmsConstants.LEGALCASE_STATUS_JUDGMENT_IMPLIMENTED);
        queryResult.setParameterList("statusCodeList", statusCodeList);
    }/*from  w w w . j a  v a2 s . c  om*/
    queryResult.setResultTransformer(new AliasToBeanResultTransformer(LegalCaseSearchResult.class));
    return queryResult;
}

From source file:org.egov.pims.commons.service.EisCommonsServiceImpl.java

License:Open Source License

public Position getPositionByUserId(Long userId) {

    Position userPosition = null;

    Date currentDate = new Date();
    try {//from   w w w. j a  va  2 s .c  o m

        String mainStr = "";
        mainStr = " select POS_ID from EG_EIS_EMPLOYEEINFO ev where ev.USER_ID = :userid and ((ev.to_Date is null and ev.from_Date <= :thisDate ) "
                + " OR (ev.from_Date <= :thisDate AND ev.to_Date >= :thisDate)) and ev.IS_PRIMARY ='Y'";
        Query qry = getCurrentSession().createSQLQuery(mainStr).addScalar("POS_ID", IntegerType.INSTANCE);
        qry.setLong("userid", userId);
        qry.setDate("thisDate", currentDate);
        List retList = qry.list();
        if (retList != null && !retList.isEmpty()) {
            Integer posId = null;
            for (Iterator iter = retList.iterator(); iter.hasNext();) {
                posId = (Integer) iter.next();
            }
            if (posId != null) {
                userPosition = getPositionById(posId);
            }
        }
    } catch (HibernateException he) {

        throw new ApplicationRuntimeException(STR_EXCEPTION + he.getMessage(), he);
    } catch (Exception he) {
        throw new ApplicationRuntimeException(STR_EXCEPTION + he.getMessage(), he);
    }
    return userPosition;

}

From source file:org.egov.pims.commons.service.EisCommonsServiceImpl.java

License:Open Source License

public Position getPositionForUserByIdAndDate(Integer userId, Date assignDate) {
    Position userPosition = null;
    //PersonalInformation personalInformation = new PersonalInformation();
    try {//from   ww w . jav  a2  s  . c  o m

        String mainStr = "";
        mainStr = " select POS_ID from EG_EIS_EMPLOYEEINFO ev where ev.USER_ID = :userid and ((ev.to_Date is null and ev.from_Date <= :thisDate ) OR (ev.from_Date <= :thisDate AND ev.to_Date > :thisDate))";
        Query qry = getCurrentSession().createSQLQuery(mainStr).addScalar("POS_ID", IntegerType.INSTANCE);
        qry.setInteger("userid", userId);
        qry.setDate("thisDate", assignDate);
        List retList = qry.list();
        if (retList != null && !retList.isEmpty()) {
            Integer posId = null;
            for (Iterator iter = retList.iterator(); iter.hasNext();) {
                posId = (Integer) iter.next();
            }
            if (posId != null) {
                userPosition = getPositionById(posId);
            }
        }
    } catch (HibernateException he) {
        throw new ApplicationRuntimeException(STR_EXCEPTION + he.getMessage(), he);
    } catch (Exception he) {
        throw new ApplicationRuntimeException(STR_EXCEPTION + he.getMessage(), he);
    }
    return userPosition;

}