List of usage examples for org.hibernate.type StringType INSTANCE
StringType INSTANCE
To view the source code for org.hibernate.type StringType INSTANCE.
Click Source Link
From source file:org.efs.openreports.util.EncryptedStringUserType.java
License:Open Source License
@Override public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException { String encryptedValue = (String) StringType.INSTANCE.nullSafeGet(rs, names[0]); if (encryptedValue != null) { return decrypt(encryptedValue); } else {//from w w w . j av a2 s . c om return null; } }
From source file:org.efs.openreports.util.EncryptedStringUserType.java
License:Open Source License
@Override public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException { if (value != null) { String encryptedValue = encrypt((String) value); StringType.INSTANCE.nullSafeSet(st, encryptedValue, index); } else {/*from w ww.j av a 2 s. c o m*/ StringType.INSTANCE.nullSafeSet(st, value.toString(), index); } }
From source file:org.egov.egf.web.actions.brs.ManualReconcileHelper.java
License:Open Source License
public List<ReconcileBean> getUnReconciledCheques(ReconcileBean reconBean) { List<ReconcileBean> list = new ArrayList<ReconcileBean>(); String instrumentCondition = ""; if (reconBean.getInstrumentNo() != null && !reconBean.getInstrumentNo().isEmpty()) { instrumentCondition = "and (ih.instrumentNumber='" + reconBean.getInstrumentNo() + "' or ih.transactionnumber='" + reconBean.getInstrumentNo() + "' )"; }/*from w ww .j a va 2s. c o m*/ try { String voucherExcludeStatuses = getExcludeStatuses(); StringBuffer query = new StringBuffer().append( " select string_agg(distinct v.vouchernumber, ',') as \"voucherNumber\" ,ih.id as \"ihId\", case when ih.instrumentNumber is null then 'Direct' else ih.instrumentNumber end as \"chequeNumber\", " + " to_char(ih.instrumentdate,'dd/mm/yyyy') as \"chequeDate\" ,ih.instrumentAmount as \"chequeAmount\",rec.transactiontype as \"txnType\" , " + " case when rec.transactionType='Cr' then 'Payment' else 'Receipt' end as \"type\" " + " FROM BANKRECONCILIATION rec, BANKACCOUNT BANK," + " VOUCHERHEADER v ,egf_instrumentheader ih, egf_instrumentotherdetails io, egf_instrumentVoucher iv WHERE " + " ih.bankAccountId = BANK.ID AND bank.id =:bankAccId AND IH.INSTRUMENTDATE <= :toDate " + " AND v.ID= iv.voucherheaderid and v.STATUS not in (" + voucherExcludeStatuses + ") " + instrumentCondition + " AND ((ih.id_status=(select id from egw_status where moduletype='Instrument' and description='Deposited') and ih.ispaycheque='0') or (ih.ispaycheque='1' and ih.id_status=(select id from egw_status where moduletype='Instrument' and description='New'))) " + " AND rec.instrumentHeaderId=ih.id and iv.instrumentHeaderid=ih.id and io.instrumentheaderid=ih.id and ih.instrumentNumber is not null" + " group by ih.id,rec.transactiontype " + " union " + " select string_agg(distinct v.vouchernumber, ',') as \"voucherNumber\" , ih.id as \"ihId\", case when ih.transactionnumber is null then 'Direct' else ih.transactionnumber end as \"chequeNumber\", " + " to_char(ih.transactiondate,'dd/mm/yyyy') as \"chequedate\" ,ih.instrumentAmount as \"chequeamount\",rec.transactiontype as \"txnType\", case when rec.transactionType= 'Cr' then 'Payment' else 'Receipt' end as \"type\" " + " FROM BANKRECONCILIATION rec, BANKACCOUNT BANK," + " VOUCHERHEADER v ,egf_instrumentheader ih, egf_instrumentotherdetails io, egf_instrumentVoucher iv WHERE ih.bankAccountId = BANK.ID AND bank.id = :bankAccId " + " AND IH.INSTRUMENTDATE <= :toDate " + instrumentCondition + " AND v.ID= iv.voucherheaderid and v.STATUS not in (" + voucherExcludeStatuses + ") AND ((ih.id_status=(select id from egw_status where moduletype='Instrument' and description='Deposited') and ih.ispaycheque='0')or (ih.ispaycheque='1' and ih.id_status=(select id from egw_status where moduletype='Instrument' and description='New'))) " + " AND rec.instrumentHeaderId=ih.id and iv.instrumentHeaderid=ih.id and io.instrumentheaderid=ih.id and ih.transactionnumber is not null" + " group by ih.id,rec.transactiontype order by 4 "); if (reconBean.getLimit() != null & reconBean.getLimit() != 0) { query.append(" limit " + reconBean.getLimit()); } // if(LOGGER.isInfoEnabled()) LOGGER.info(" query for getUnReconciledCheques: " + query); /*String query=" SELECT decode(rec.chequeNumber, null, 'Direct', rec.chequeNumber) as \"chequeNumber\",rec.chequedate as \"chequedate\" ,amount as \"chequeamount\",transactiontype as \"txnType\" ,rec.type as \"type\" from bankreconciliation rec, bankAccount bank, voucherheader vh " +" where rec.bankAccountId = bank.id AND bank.id ="+bankAccId+" and rec.isReversed = 0 AND (rec.reconciliationDate > to_date('"+recDate+"' || ' 23:59:59','DD-MON-YYYY HH24:MI:SS') " +" OR (rec.isReconciled = 0)) AND vh.VOUCHERDATE <= to_date('"+recDate+"' || ' 23:59:59','DD-MON-YYYY HH24:MI:SS') and vh.id=rec.VOUCHERHEADERID and vh.STATUS<>4" +" union " +" select refno as \"chequeNumber\", txndate as \"chequedate\", txnamount as \"chequeamount\", decode(type,'R','Dr','Cr') as \"txnType\", " +" type as \"type\" from bankentries be,bankAccount bank where be.bankAccountId = bank.id and bank.id ="+bankAccId+" " +" and txndate<= to_date('"+recDate+"' || ' 23:59:59','DD-MON-YYYY HH24:MI:SS') and voucherheaderid is null "; */ SQLQuery createSQLQuery = persistenceService.getSession().createSQLQuery(query.toString()); createSQLQuery.setLong("bankAccId", reconBean.getAccountId()); createSQLQuery.setDate("toDate", reconBean.getReconciliationDate()); createSQLQuery.addScalar("voucherNumber", StringType.INSTANCE); createSQLQuery.addScalar("ihId", LongType.INSTANCE); createSQLQuery.addScalar("chequeDate", StringType.INSTANCE); createSQLQuery.addScalar("chequeNumber", StringType.INSTANCE); createSQLQuery.addScalar("chequeAmount", BigDecimalType.INSTANCE); createSQLQuery.addScalar("txnType", StringType.INSTANCE); createSQLQuery.addScalar("type", StringType.INSTANCE); createSQLQuery.setResultTransformer(Transformers.aliasToBean(ReconcileBean.class)); list = (List<ReconcileBean>) createSQLQuery.list(); } catch (Exception e) { LOGGER.error("Exp in getUnReconciledCheques:" + e.getMessage()); throw new ApplicationRuntimeException(e.getMessage()); } return list; }
From source file:org.egov.egf.web.actions.payment.BankEntriesNotInBankBookAction.java
License:Open Source License
@SkipValidation @Action(value = "/payment/bankEntriesNotInBankBook-search") public String search() { Query query = null;/*from ww w . j av a2 s. com*/ query = persistenceService.getSession().createSQLQuery(getQuery()).addScalar("refNum", StringType.INSTANCE) .addScalar("type", StringType.INSTANCE).addScalar("date", DateType.INSTANCE) .addScalar("amount", BigDecimalType.INSTANCE).addScalar("remarks", StringType.INSTANCE) .addScalar("glcodeDetail", StringType.INSTANCE).addScalar("beId", LongType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(BankEntriesNotInBankBook.class)); bankEntriesNotInBankBookList = query.list(); List<BankEntriesNotInBankBook> tempList = new ArrayList<BankEntriesNotInBankBook>(); for (BankEntriesNotInBankBook bean : bankEntriesNotInBankBookList) { bean.setDateId(FORMATDDMMYYYY.format(bean.getDate())); tempList.add(bean); } bankEntriesNotInBankBookList = tempList; if (bankEntriesNotInBankBookList.size() == 0) bankEntriesNotInBankBookList.add(new BankEntriesNotInBankBook()); prepareNewform(); addDropdownData("bankList", bankHibernateDAO.getAllBanksByFund(voucherHeader.getFundId().getId())); addDropdownData("bankBranchList", bankBranchHibernateDAO.getAllBankBranchsByBank(bank)); addDropdownData("bankAccountList", bankaccountHibernateDAO.getBankAccountByBankBranch(bank_branch)); mode = "save"; return NEW; }
From source file:org.egov.egf.web.actions.report.DayBookReportAction.java
License:Open Source License
private void prepareResultList() { String voucherDate = "", voucherNumber = "", voucherType = "", narration = "", status = ""; Query query = null;// w ww . j a va 2 s. co m query = persistenceService.getSession().createSQLQuery(getQuery()) .addScalar("voucherdate", StringType.INSTANCE).addScalar("vouchernumber", StringType.INSTANCE) .addScalar("glcode", StringType.INSTANCE).addScalar("particulars", StringType.INSTANCE) .addScalar("type", StringType.INSTANCE).addScalar("narration", StringType.INSTANCE) .addScalar("status", StringType.INSTANCE).addScalar("creditamount", StringType.INSTANCE) .addScalar("debitamount", StringType.INSTANCE).addScalar("vhId", StringType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(DayBook.class)); dayBookDisplayList = query.list(); for (DayBook bean : dayBookDisplayList) { bean.setDebitamount( new BigDecimal(bean.getDebitamount()).setScale(2, BigDecimal.ROUND_HALF_EVEN).toString()); bean.setCreditamount( new BigDecimal(bean.getCreditamount()).setScale(2, BigDecimal.ROUND_HALF_EVEN).toString()); if (voucherDate != null && !voucherDate.equalsIgnoreCase("") && voucherDate.equalsIgnoreCase(bean.getVoucherdate()) && voucherNumber.equalsIgnoreCase(bean.getVouchernumber())) { bean.setVoucherdate(""); } else { voucherDate = bean.getVoucherdate(); } if (voucherType != null && !voucherType.equalsIgnoreCase("") && voucherType.equalsIgnoreCase(bean.getType()) && voucherNumber.equalsIgnoreCase(bean.getVouchernumber())) { bean.setType(""); } else { voucherType = bean.getType(); } if (status != null && !status.equalsIgnoreCase("") && status.equalsIgnoreCase(bean.getStatus()) && voucherNumber.equalsIgnoreCase(bean.getVouchernumber())) { bean.setStatus(""); } else { status = bean.getStatus(); } if (voucherNumber != null && !voucherNumber.equalsIgnoreCase("") && voucherNumber.equalsIgnoreCase(bean.getVouchernumber())) { bean.setVouchernumber(""); } else { voucherNumber = bean.getVouchernumber(); } if (narration != null && !narration.equalsIgnoreCase("") && narration.equalsIgnoreCase(bean.getNarration())) { bean.setNarration(""); } else { narration = bean.getNarration(); } } }
From source file:org.egov.egf.web.actions.report.JournalBookReportAction.java
License:Open Source License
private void prepareResultList() { String voucherDate = "", voucherNumber = "", voucherName = "", narration = ""; Query query = null;/*from w w w . j a v a 2 s. c o m*/ query = persistenceService.getSession().createSQLQuery(getQuery()) .addScalar("voucherdate", StringType.INSTANCE).addScalar("vouchernumber", StringType.INSTANCE) .addScalar("code", StringType.INSTANCE).addScalar("accName", StringType.INSTANCE) .addScalar("narration", StringType.INSTANCE).addScalar("debitamount", StringType.INSTANCE) .addScalar("creditamount", StringType.INSTANCE).addScalar("voucherName", StringType.INSTANCE) .addScalar("vhId", StringType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(GeneralLedgerBean.class)); journalBookDisplayList = query.list(); for (GeneralLedgerBean bean : journalBookDisplayList) { bean.setDebitamount( new BigDecimal(bean.getDebitamount()).setScale(2, BigDecimal.ROUND_HALF_EVEN).toString()); bean.setCreditamount( new BigDecimal(bean.getCreditamount()).setScale(2, BigDecimal.ROUND_HALF_EVEN).toString()); if (voucherDate != null && !voucherDate.equalsIgnoreCase("") && voucherDate.equalsIgnoreCase(bean.getVoucherdate()) && voucherNumber.equalsIgnoreCase(bean.getVouchernumber())) { bean.setVoucherdate(""); } else { voucherDate = bean.getVoucherdate(); } if (voucherName != null && !voucherName.equalsIgnoreCase("") && voucherName.equalsIgnoreCase(bean.getVoucherName()) && voucherNumber.equalsIgnoreCase(bean.getVouchernumber())) { bean.setVoucherName(""); } else { voucherName = bean.getVoucherName(); } if (voucherNumber != null && !voucherNumber.equalsIgnoreCase("") && voucherNumber.equalsIgnoreCase(bean.getVouchernumber())) { bean.setVouchernumber(""); } else { voucherNumber = bean.getVouchernumber(); } if (narration != null && !narration.equalsIgnoreCase("") && narration.equalsIgnoreCase(bean.getNarration())) { bean.setNarration(""); } else { narration = bean.getNarration(); } } }
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//w w w.j av a 2s .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.model.service.BudgetUploadReportService.java
License:Open Source License
@ReadOnly public List<BudgetUploadReport> search(BudgetUploadReport budgetUploadReport) { String reMaterializedPath = "", beMaterializedPath = ""; Long functionId = null, deptId = null; Integer fundId = null;//w ww. j a va 2 s .co m Budget reBudget = new Budget(); Budget beBudget = new Budget(); if (budgetUploadReport.getReBudget() != null && budgetUploadReport.getReBudget().getId() != null) { reBudget = budgetService.findById(budgetUploadReport.getReBudget().getId(), false); reMaterializedPath = reBudget.getMaterializedPath(); } if (reBudget != null) { beBudget = budgetService.getReferenceBudgetFor(reBudget); beMaterializedPath = beBudget.getMaterializedPath(); } if (budgetUploadReport.getFund() != null && budgetUploadReport.getFund().getId() != null) fundId = budgetUploadReport.getFund().getId(); if (budgetUploadReport.getFunction() != null && budgetUploadReport.getFunction().getId() != null) functionId = budgetUploadReport.getFunction().getId(); if (budgetUploadReport.getDepartment() != null && budgetUploadReport.getDepartment().getId() != null) deptId = budgetUploadReport.getDepartment().getId(); List<BudgetUploadReport> budgetUploadReportList = new ArrayList<BudgetUploadReport>(); Query query = null; query = persistenceService.getSession() .createSQLQuery(getQuery(reMaterializedPath, beMaterializedPath, fundId, functionId, deptId)) .addScalar("fundCode", StringType.INSTANCE).addScalar("functionCode", StringType.INSTANCE) .addScalar("glCode", StringType.INSTANCE).addScalar("deptCode", StringType.INSTANCE) .addScalar("approvedReAmount", BigDecimalType.INSTANCE) .addScalar("planningReAmount", BigDecimalType.INSTANCE) .addScalar("approvedBeAmount", BigDecimalType.INSTANCE) .addScalar("planningBeAmount", BigDecimalType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(BudgetUploadReport.class)); budgetUploadReportList = query.list(); return budgetUploadReportList; }
From source file:org.egov.services.payment.PaymentService.java
License:Open Source License
public List<ChequeAssignment> getPaymentVoucherForTNEBRTGSInstrument(final Map<String, String[]> parameters, final CVoucherHeader voucherHeader) throws ApplicationException, ParseException { if (LOGGER.isDebugEnabled()) LOGGER.debug("Starting getPaymentVoucherNotInInstrument..."); List<ChequeAssignment> chequeAssignmentList = new ArrayList<ChequeAssignment>(); final StringBuffer sql = new StringBuffer(); final StringBuffer TNEBsql = new StringBuffer(); EgBillSubType egSubType = new EgBillSubType(); egSubType = (EgBillSubType) persistenceService.find(" from EgBillSubType where name = ?", FinancialConstants.BILLSUBTYPE_TNEBBILL); if (egSubType.getId() != null) TNEBsql.append(" bmis.billsubtype = " + egSubType.getId() + ""); if (parameters.get("region")[0] != null && !parameters.get("region")[0].equalsIgnoreCase("")) TNEBsql.append(" and ebd.region = '" + parameters.get("region")[0] + "'"); if (!"".equals(parameters.get("fromDate")[0])) sql.append(/*from w ww . j a v a2s . co m*/ " and vh.voucherDate>='" + sdf.format(formatter.parse(parameters.get("fromDate")[0])) + "' "); if (!"".equals(parameters.get("toDate")[0])) sql.append(" and vh.voucherDate<='" + sdf.format(formatter.parse(parameters.get("toDate")[0])) + "'"); if (!StringUtils.isEmpty(voucherHeader.getVoucherNumber())) sql.append(" and vh.voucherNumber like '%" + voucherHeader.getVoucherNumber() + "%'"); if (voucherHeader.getFundId() != null) sql.append(" and vh.fundId=" + voucherHeader.getFundId().getId()); if (voucherHeader.getVouchermis().getFundsource() != null) sql.append(" and vmis.fundsourceId=" + voucherHeader.getVouchermis().getFundsource().getId()); if (voucherHeader.getVouchermis().getDepartmentid() != null) sql.append(" and vmis.departmentid=" + voucherHeader.getVouchermis().getDepartmentid().getId()); if (voucherHeader.getVouchermis().getSchemeid() != null) sql.append(" and vmis.schemeid=" + voucherHeader.getVouchermis().getSchemeid().getId()); if (voucherHeader.getVouchermis().getSubschemeid() != null) sql.append(" and vmis.subschemeid=" + voucherHeader.getVouchermis().getSubschemeid().getId()); if (voucherHeader.getVouchermis().getFunctionary() != null) sql.append(" and vmis.functionaryid=" + voucherHeader.getVouchermis().getFunctionary().getId()); if (voucherHeader.getVouchermis().getDivisionid() != null) sql.append(" and vmis.divisionid=" + voucherHeader.getVouchermis().getDivisionid().getId()); if (parameters.get("bankaccount") != null && !parameters.get("bankaccount")[0].equals("-1")) { sql.append(" and ph.bankaccountnumberid=" + parameters.get("bankaccount")[0]); sql.append(" and lower(ph.type)=lower('" + parameters.get("paymentMode")[0] + "')"); sql.append(" and ph.bankaccountnumberid=ba.id"); } else sql.append(" and ph.bankaccountnumberid=ba.id") .append(" and lower(ph.type)=lower('" + parameters.get("paymentMode")[0] + "')"); sql.append(" and vmis.departmentid =dept.id "); final List<AppConfigValues> appList = appConfigValuesService.getConfigValuesByModuleAndKey("EGF", "APPROVEDVOUCHERSTATUS"); final String approvedstatus = appList.get(0).getValue(); final List<String> descriptionList = new ArrayList<String>(); descriptionList.add("New"); descriptionList.add("Reconciled"); final List<EgwStatus> egwStatusList = egwStatusDAO.getStatusListByModuleAndCodeList("Instrument", descriptionList); String statusId = ""; for (final EgwStatus egwStatus : egwStatusList) statusId = statusId + egwStatus.getId() + ","; statusId = statusId.substring(0, statusId.length() - 1); persistenceService.find(" from Bankaccount where id=?", Long.valueOf(parameters.get("bankaccount")[0])); String payTo = null; try { final List<AppConfigValues> configValues = appConfigValuesService.getConfigValuesByModuleAndKey( FinancialConstants.MODULE_NAME_APPCONFIG, FinancialConstants.EB_VOUCHER_PROPERTY_BANKBRANCH); for (final AppConfigValues appConfigVal : configValues) payTo = appConfigVal.getValue(); } catch (final Exception e) { throw new ApplicationRuntimeException( "Appconfig value for EB Voucher propartys is not defined in the system"); } if (payTo != null) payTo = payTo.substring(0, 20); Query query = null; if (LOGGER.isDebugEnabled()) LOGGER.debug("statusId -- > " + statusId); chequeList = new ArrayList<ChequeAssignment>(); if (voucherHeader.getName() == null || !voucherHeader.getName().equalsIgnoreCase(FinancialConstants.PAYMENTVOUCHER_NAME_REMITTANCE)) { // / // Only // for // bill // payment // screen query = getSession().createSQLQuery( " SELECT vh.id AS voucherid , vh.voucherNumber AS voucherNumber , dept.name AS departmentName, " + " vh.voucherDate AS voucherDate, '" + payTo + "' AS paidTo , ph.paymentamount AS paidAmount, current_date AS chequeDate ,ba.accountnumber AS bankAccNumber ," + " ba.id AS bankAccountId FROM paymentheader ph , eg_department dept, bankaccount ba, voucherheader vh LEFT JOIN EGF_INSTRUMENTVOUCHER IV " + " ON VH.ID=IV.VOUCHERHEADERID LEFT JOIN EGF_INSTRUMENTHEADER IH ON IV.INSTRUMENTHEADERID=IH.ID,vouchermis vmis " + " WHERE ph.voucherheaderid IN ( SELECT DISTINCT misbill.payvhid " + " FROM egf_ebdetails ebd , eg_billregistermis bmis, eg_billregister bill , Miscbilldetail misbill WHERE bill.id = ebd.billid " + " AND bmis.billid = bill.id AND " + TNEBsql + " AND bmis.voucherheaderid = misbill.billvhid ) AND ph.voucherheaderid = vh.id " + " AND vmis.voucherheaderid = vh.id AND vh.status = " + approvedstatus + " " + sql + " " + " AND ph.bankaccountnumberid=ba.id AND vmis.departmentid = dept.id AND IV.VOUCHERHEADERID IS NULL AND " + " vh.type = '" + FinancialConstants.STANDARD_VOUCHER_TYPE_PAYMENT + "' AND vh.name NOT IN " + " ('" + FinancialConstants.PAYMENTVOUCHER_NAME_REMITTANCE + "' , '" + FinancialConstants.PAYMENTVOUCHER_NAME_SALARY + "', '" + FinancialConstants.PAYMENTVOUCHER_NAME_PENSION + "') " + " GROUP BY vh.id,vh.voucherNumber,dept.name , vh.voucherDate, ba.accountnumber, ba.id , ph.paymentamount ORDER BY ba.id,dept.name,vh.voucherNumber ") .addScalar("voucherid", LongType.INSTANCE).addScalar("voucherNumber") .addScalar("departmentName").addScalar("voucherDate").addScalar("paidTo", StringType.INSTANCE) .addScalar("paidAmount", BigDecimalType.INSTANCE).addScalar("chequeDate") .addScalar("bankAccNumber").addScalar("bankAccountId", LongType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug(" for non salary and remittance" + query); LOGGER.info(" for non salary and remittance" + query); chequeAssignmentList = query.list(); // below one handles // assign-->surrendar-->assign-->surrendar-->....... if (LOGGER.isDebugEnabled()) LOGGER.debug("checking cheque assigned and surrendard"); query = getSession().createSQLQuery( " SELECT vh.id AS voucherid , vh.voucherNumber AS voucherNumber , dept.name AS departmentName, " + " vh.voucherDate AS voucherDate, '" + payTo + "' AS paidTo , ph.paymentamount AS paidAmount, current_date AS chequeDate ,ba.accountnumber AS bankAccNumber ," + " ba.id AS bankAccountId FROM paymentheader ph , eg_department dept, bankaccount ba, voucherheader vh LEFT JOIN EGF_INSTRUMENTVOUCHER IV " + " ON VH.ID=IV.VOUCHERHEADERID LEFT JOIN EGF_INSTRUMENTHEADER IH ON IV.INSTRUMENTHEADERID=IH.ID,vouchermis vmis,(SELECT MAX(iv1.instrumentheaderid) AS maxihid," + " iv1.voucherheaderid AS iv1vhid FROM egf_instrumentvoucher iv1 GROUP BY iv1.voucherheaderid ) as table1 WHERE ph.voucherheaderid IN ( SELECT DISTINCT misbill.payvhid " + " FROM egf_ebdetails ebd , eg_billregistermis bmis, eg_billregister bill , Miscbilldetail misbill WHERE bill.id = ebd.billid " + " AND bmis.billid = bill.id AND " + TNEBsql + " AND bmis.voucherheaderid = misbill.billvhid ) AND ph.voucherheaderid = vh.id " + " AND vmis.voucherheaderid = vh.id AND vh.status = " + approvedstatus + " " + sql + " " + " AND ph.bankaccountnumberid=ba.id AND vmis.departmentid = dept.id AND IV.VOUCHERHEADERID IS NOT NULL AND iv.instrumentheaderid = table1.maxihid AND table1.iv1vhid = vh.id AND " + " ih.id_status NOT IN (" + statusId + ") AND vh.type = '" + FinancialConstants.STANDARD_VOUCHER_TYPE_PAYMENT + "' AND vh.name NOT IN " + " ('" + FinancialConstants.PAYMENTVOUCHER_NAME_REMITTANCE + "' , '" + FinancialConstants.PAYMENTVOUCHER_NAME_SALARY + "', '" + FinancialConstants.PAYMENTVOUCHER_NAME_PENSION + "') " + " GROUP BY vh.id,vh.voucherNumber,dept.name , vh.voucherDate, ba.accountnumber, ba.id , ph.paymentamount ORDER BY ba.id,dept.name,vh.voucherNumber ") .addScalar("voucherid", LongType.INSTANCE).addScalar("voucherNumber") .addScalar("departmentName").addScalar("voucherDate").addScalar("paidTo", StringType.INSTANCE) .addScalar("paidAmount", BigDecimalType.INSTANCE).addScalar("chequeDate") .addScalar("bankAccNumber").addScalar("bankAccountId", LongType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug(" Surrendered rtgs nos" + query); LOGGER.info(" Surrendered rtgs nos" + query); chequeAssignmentList.addAll(query.list()); } if (LOGGER.isDebugEnabled()) LOGGER.debug("Completed getPaymentVoucherNotInInstrument."); return chequeAssignmentList; }
From source file:org.egov.services.report.FunctionwiseIEService.java
License:Open Source License
/** * * @param reportSearch//w w w.j av a 2s .c o m * @return * @throws ApplicationException * @throws ParseException for Main report getMajor Code and Minor Code for Sub Report get only minor code */ public List<CommonReportBean> getMinorAndMajorCodeList(final ReportSearch reportSearch) throws ApplicationException, ParseException { String sql = ""; if (reportSearch.getByDepartment() && reportSearch.getByDetailCode()) sql = " select coa.glcode as accCode,coa.name as name,null as schedule,null as FIEscheduleId,0 as isMajor from Chartofaccounts coa" + " where coa.type=:type and length(coa.glcode)=" + reportSearch.getMinorCodeLen() + " and coa.glcode like :glcode and classification=4 and isActiveForPosting=true order by 1 "; else if (reportSearch.getByDepartment()) sql = " select coa.glcode as accCode,coa.name as name,mp.schedule as schedule,mp.id as FIEscheduleId,0 as isMajor from Chartofaccounts coa,Schedulemapping mp" + " where coa.FIEscheduleId=mp.id and coa.type=:type and length(coa.glcode)=" + reportSearch.getMinorCodeLen() + " and coa.FIEscheduleId=:FIEscheduleId order by 1"; else sql = " select coa.glcode as accCode,coa.name as name,mp.schedule as schedule,mp.id as FIEscheduleId, 0 as isMajor from Chartofaccounts coa,Schedulemapping mp" + " where coa.FIEscheduleId=mp.id and coa.type=:type and length(coa.glcode)=" + reportSearch.getMinorCodeLen() + " Union " + " select coa.glcode as accCode,coa.name as name,null as schedule,null as FIEscheduleId ,1 as isMajor from Chartofaccounts coa" + " where coa.type=:type and length(coa.glcode)=" + reportSearch.getMajorCodeLen() + " order by 1"; final Query query = persistenceService.getSession().createSQLQuery(sql) .addScalar("accCode", StringType.INSTANCE).addScalar("name", StringType.INSTANCE) .addScalar("schedule", StringType.INSTANCE).addScalar("FIEscheduleId", LongType.INSTANCE) .addScalar("isMajor", BooleanType.INSTANCE).setString("type", reportSearch.getIncExp()) .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class)); if (reportSearch.getByDetailCode()) query.setString("glcode", reportSearch.getGlcode() + "%"); else if (reportSearch.getByDepartment()) query.setLong("FIEscheduleId", reportSearch.getFIEscheduleId()); if (LOGGER.isDebugEnabled()) LOGGER.debug("----------------" + sql); return query.list(); }