List of usage examples for org.hibernate SQLQuery setDate
@Deprecated @SuppressWarnings("unchecked") default Query<R> setDate(int position, Date val)
From source file:org.egov.collection.service.CollectionReportService.java
License:Open Source License
public SQLQuery getOnlinePaymentReportData(final String districtName, final String ulbName, final String fromDate, final String toDate, final String transactionId) { final SimpleDateFormat dateFormatter = new SimpleDateFormat("dd/MM/yyyy"); final StringBuilder queryStr = new StringBuilder(500); queryStr.append("select * from ").append(environmentSettings.statewideSchemaName()) .append(".onlinepayment_view opv where 1=1"); if (StringUtils.isNotBlank(districtName)) queryStr.append(" and opv.districtName=:districtName "); if (StringUtils.isNotBlank(ulbName)) queryStr.append(" and opv.ulbName=:ulbName "); if (StringUtils.isNotBlank(fromDate)) queryStr.append(" and opv.transactiondate>=:fromDate "); if (StringUtils.isNotBlank(toDate)) queryStr.append(" and opv.transactiondate<=:toDate "); if (StringUtils.isNotBlank(transactionId)) queryStr.append(" and opv.transactionnumber like :transactionnumber "); queryStr.append(" order by receiptdate desc "); final SQLQuery query = getCurrentSession().createSQLQuery(queryStr.toString()); if (StringUtils.isNotBlank(districtName)) query.setString("districtName", districtName); if (StringUtils.isNotBlank(ulbName)) query.setString("ulbName", ulbName); try {//from w ww .j a v a 2s . c o m if (StringUtils.isNotBlank(fromDate)) query.setDate("fromDate", dateFormatter.parse(fromDate)); if (StringUtils.isNotBlank(toDate)) query.setDate("toDate", dateFormatter.parse(toDate)); } catch (final ParseException e) { LOGGER.error("Exception parsing Date" + e.getMessage()); } if (StringUtils.isNotBlank(transactionId)) query.setString("transactionnumber", "%" + transactionId + "%"); queryStr.append(" order by opv.receiptdate desc"); query.setResultTransformer(new AliasToBeanResultTransformer(OnlinePaymentResult.class)); return query; }
From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
/** * @return//from w w w .j ava2 s . co m */ @Transactional public String schedule() { // Step1: mark which are all we are going to process count = 0; // persistenceService.getSession().getTransaction().setTimeout(900); if (LOGGER.isDebugEnabled()) LOGGER.debug("Started at " + new Date()); markForProcessing(BRS_TRANSACTION_TYPE_CHEQUE); persistenceService.getSession().flush(); // step2 :find duplicate and mark to be processed manually findandUpdateDuplicates(); final List<AutoReconcileBean> detailList = getStatmentsForProcessing(BRS_TRANSACTION_TYPE_CHEQUE); final String statusQury = "select id from EgwStatus where upper(moduletype)=upper('instrument') and upper(description)=upper('" + FinancialConstants.INSTRUMENT_RECONCILED_STATUS + "')"; statusId = (Integer) persistenceService.find(statusQury); final Long instrumentTypeId = getInstrumentType(FinancialConstants.INSTRUMENT_TYPE_CHEQUE); final Long instrumentTypeDDId = getInstrumentType(FinancialConstants.INSTRUMENT_TYPE_DD); // where instrumentheaderid= (select id.....) is used to fetch only one record may be double submit or two instrument // entries // let the user decide final String recociliationQuery = "update EGF_InstrumentHeader set id_status=:statusId, lastmodifiedby=:userId,lastmodifieddate=CURRENT_DATE" + " where id= (select id from egf_instrumentheader where instrumentNumber=:instrumentNo and " + " instrumentAmount=:amount and bankaccountid=:accountId and ispaycheque=:ispaycheque and instrumentType in (" + instrumentTypeId + "," + instrumentTypeDDId + ")" + " and id_status=(select id from Egw_Status where upper(moduletype)=upper('instrument') and upper(description)=upper(:instrumentStatus)))"; final String recociliationAmountQuery = "update egf_instrumentOtherdetails set reconciledamount=:amount,instrumentstatusdate=:txDate " + " ,lastmodifiedby=:userId,lastmodifieddate=CURRENT_DATE,reconciledOn=:reconciliationDate " + " where instrumentheaderid= (select id from egf_instrumentheader where instrumentNumber=:instrumentNo and " + " instrumentAmount=:amount and bankaccountid=:accountId and ispaycheque=:ispaycheque and instrumentType in (" + instrumentTypeId + "," + instrumentTypeDDId + ")" + " and id_status=(select id from Egw_Status where upper(moduletype)=upper('instrument') and upper(description)=upper(:instrumentStatus)))"; final SQLQuery updateQuery = persistenceService.getSession().createSQLQuery(recociliationQuery); final SQLQuery updateQuery2 = persistenceService.getSession().createSQLQuery(recociliationAmountQuery); final String backUpdateBankStmtquery = "update " + TABLENAME + " set action='" + BRS_ACTION_PROCESSED + "' ,reconciliationDate=:reconciliationDate where id=:id"; final String backUpdateFailureBRSquery = "update " + TABLENAME + " set action='" + BRS_ACTION_TO_BE_PROCESSED_MANUALLY + "',errormessage=:e where id=:id"; final SQLQuery backupdateQuery = persistenceService.getSession().createSQLQuery(backUpdateBankStmtquery); final SQLQuery backupdateFailureQuery = persistenceService.getSession() .createSQLQuery(backUpdateFailureBRSquery); rowCount = 0; for (final AutoReconcileBean bean : detailList) { int updated = -1; try { updateQuery.setLong("statusId", statusId); updateQuery.setLong("accountId", accountId); updateQuery.setString("instrumentNo", bean.getInstrumentNo()); updateQuery.setInteger("userId", ApplicationThreadLocals.getUserId().intValue()); updateQuery2.setDate("txDate", bean.getTxDate()); updateQuery2.setDate("reconciliationDate", reconciliationDate); updateQuery2.setLong("accountId", accountId); updateQuery2.setString("instrumentNo", bean.getInstrumentNo()); updateQuery2.setInteger("userId", ApplicationThreadLocals.getUserId().intValue()); if (bean.getDebit() != null && bean.getDebit().compareTo(BigDecimal.ZERO) != 0) { updateQuery.setBigDecimal("amount", bean.getDebit()); updateQuery.setCharacter("ispaycheque", '1'); updateQuery.setString("instrumentStatus", FinancialConstants.INSTRUMENT_CREATED_STATUS); updated = updateQuery.executeUpdate(); if (updated != 0) { updateQuery2.setBigDecimal("amount", bean.getDebit()); updateQuery2.setCharacter("ispaycheque", '1'); updateQuery2.setString("instrumentStatus", FinancialConstants.INSTRUMENT_RECONCILED_STATUS); updated = updateQuery2.executeUpdate(); } } else { updateQuery.setBigDecimal("amount", bean.getCredit()); updateQuery.setCharacter("ispaycheque", '0'); updateQuery.setString("instrumentStatus", FinancialConstants.INSTRUMENT_DEPOSITED_STATUS); updated = updateQuery.executeUpdate(); if (updated != 0) { updateQuery2.setBigDecimal("amount", bean.getCredit()); updateQuery2.setCharacter("ispaycheque", '0'); updateQuery2.setString("instrumentStatus", FinancialConstants.INSTRUMENT_RECONCILED_STATUS); updated = updateQuery2.executeUpdate(); } } // if updated is 0 means nothing got updated means could not find matching row in instrumentheader if (updated == 0) { backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.setString("e", DID_NOT_FIND_MATCH_IN_BANKBOOK); backupdateFailureQuery.executeUpdate(); } else { backupdateQuery.setLong("id", bean.getId()); backupdateQuery.setDate("reconciliationDate", reconciliationDate); backupdateQuery.executeUpdate(); count++; // if(LOGGER.isDebugEnabled()) LOGGER.debug(count); } rowCount++; if (LOGGER.isDebugEnabled()) LOGGER.debug("out of " + rowCount + "==>succesfull " + count); if (rowCount % 20 == 0) persistenceService.getSession().flush(); // These exception might be because the other entires in instrument which is not in egf_brs_bankstatements // so any issues leave it for manual update } catch (final HibernateException e) { if (e.getCause().getMessage().contains("single-row subquery returns more")) backupdateFailureQuery.setString("e", BRS_MESSAGE_MORE_THAN_ONE_MATCH); else backupdateFailureQuery.setString("e", e.getMessage()); backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.executeUpdate(); } catch (final Exception e) { backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.setString("e", e.getMessage()); backupdateFailureQuery.executeUpdate(); } } processCSL(); return "result"; }
From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
private void markForProcessing(final String type) { final StringBuffer sql = new StringBuffer(256); sql.append("update ").append(TABLENAME).append(" set action='").append(BRS_ACTION_TO_BE_PROCESSED) .append("' where type='").append(type) .append("' and accountid=:accountId and txdate>=:fromDate and txDate<=:toDate and (action is null or action!='processed')"); if (BRS_TRANSACTION_TYPE_BANK.equalsIgnoreCase(type)) sql.append(" and CSLno is not null "); final SQLQuery markQuery = persistenceService.getSession().createSQLQuery(sql.toString()); markQuery.setDate("fromDate", fromDate); markQuery.setDate("toDate", toDate); markQuery.setLong("accountId", accountId); markQuery.executeUpdate();/*w ww .ja va2 s .com*/ }
From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
private void processCSL() { markForProcessing(BRS_TRANSACTION_TYPE_BANK); final List<AutoReconcileBean> CSLList = getStatmentsForProcessing(BRS_TRANSACTION_TYPE_BANK); final Long instrumentTypeId = getInstrumentType(FinancialConstants.INSTRUMENT_TYPE_BANK_TO_BANK); final String recociliationQuery = "update EGF_InstrumentHeader set id_status=:statusId, lastmodifiedby=:userId,lastmodifieddate=CURRENT_DATE" + " where id = (select ih.id from egf_instrumentheader ih,egf_instrumentvoucher iv,voucherheader vh where " + " instrumentAmount=:amount and bankaccountid=:accountId and ispaycheque=:ispaycheque and instrumentType in (" + instrumentTypeId + ")" + " and id_status=(select id from Egw_Status where upper(moduletype)=upper('instrument') and upper(description)=" + " upper(:instrumentStatus)) and iv.instrumentheaderid=ih.id and iv.voucherheaderid=ih.id and vh.vouchernumber=:cslNo ) "; final String recociliationAmountQuery = "update egf_instrumentOtherdetails set reconciledamount=:amount,instrumentstatusdate=:txDate " + " ,lastmodifiedby=:userId,lastmodifieddate=CURRENT_DATE,reconciledOn=:reconciliationDate " + " where instrumentheaderid = (select ih.id from egf_instrumentheader ih,egf_instrumentvoucher iv,voucherheader vh where " + " instrumentAmount=:amount and bankaccountid=:accountId and ispaycheque=:ispaycheque and instrumentType in (" + instrumentTypeId + ")" + " and id_status=(select id from Egw_Status where upper(moduletype)=upper('instrument') and upper(description)=" + " upper(:instrumentStatus)) and iv.instrumentheaderid=ih.id and iv.voucherheaderid=ih.id and vh.vouchernumber=:cslNo ) "; final SQLQuery updateQuery = persistenceService.getSession().createSQLQuery(recociliationQuery); final SQLQuery updateQuery2 = persistenceService.getSession().createSQLQuery(recociliationAmountQuery); final String backUpdateBankStmtquery = "update " + TABLENAME + " set action='" + BRS_ACTION_PROCESSED + "' ,reconciliationDate=:reconciliationDate where id=:id"; final String backUpdateFailureBRSquery = "update " + TABLENAME + " set action='" + BRS_ACTION_TO_BE_PROCESSED_MANUALLY + "',errormessage=:e where id=:id"; final SQLQuery backupdateQuery = persistenceService.getSession().createSQLQuery(backUpdateBankStmtquery); final SQLQuery backupdateFailureQuery = persistenceService.getSession() .createSQLQuery(backUpdateFailureBRSquery); for (final AutoReconcileBean bean : CSLList) { int updated = -1; try {//from w w w.j a va 2 s. c o m updateQuery.setLong("statusId", statusId); updateQuery.setLong("accountId", accountId); updateQuery.setString("cslNo", bean.getCSLno()); updateQuery.setInteger("userId", ApplicationThreadLocals.getUserId().intValue()); updateQuery2.setDate("txDate", bean.getTxDate()); updateQuery2.setDate("reconciliationDate", reconciliationDate); updateQuery2.setLong("accountId", accountId); updateQuery2.setString("cslNo", bean.getCSLno()); updateQuery2.setInteger("userId", ApplicationThreadLocals.getUserId().intValue()); if (bean.getDebit() != null && bean.getDebit().compareTo(BigDecimal.ZERO) != 0) { updateQuery.setBigDecimal("amount", bean.getDebit()); updateQuery.setCharacter("ispaycheque", '1'); updateQuery.setString("instrumentStatus", FinancialConstants.INSTRUMENT_CREATED_STATUS); updated = updateQuery.executeUpdate(); if (updated != 0) { updateQuery2.setBigDecimal("amount", bean.getDebit()); updateQuery2.setCharacter("ispaycheque", '1'); updateQuery2.setString("instrumentStatus", FinancialConstants.INSTRUMENT_RECONCILED_STATUS); updated = updateQuery2.executeUpdate(); } } else { updateQuery.setBigDecimal("amount", bean.getCredit()); updateQuery.setCharacter("ispaycheque", '1'); updateQuery.setString("instrumentStatus", FinancialConstants.INSTRUMENT_CREATED_STATUS); updated = updateQuery.executeUpdate(); if (updated != 0) { updateQuery2.setBigDecimal("amount", bean.getCredit()); updateQuery2.setCharacter("ispaycheque", '1'); updateQuery2.setString("instrumentStatus", FinancialConstants.INSTRUMENT_RECONCILED_STATUS); updated = updateQuery2.executeUpdate(); } if (updated == 0) { } } // if updated is 0 means nothing got updated means could not find matching row in instrumentheader if (updated == 0) { backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.setString("e", DID_NOT_FIND_MATCH_IN_BANKBOOK); backupdateFailureQuery.executeUpdate(); } else if (updated == -1) { backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.setString("e", DID_NOT_FIND_MATCH_IN_BANKBOOK); backupdateFailureQuery.executeUpdate(); // if(LOGGER.isDebugEnabled()) LOGGER.debug(count); } else { backupdateQuery.setLong("id", bean.getId()); backupdateQuery.setDate("reconciliationDate", reconciliationDate); backupdateQuery.executeUpdate(); count++; // if(LOGGER.isDebugEnabled()) LOGGER.debug(count); } rowCount++; if (LOGGER.isDebugEnabled()) LOGGER.debug("out of " + rowCount + "==>succesfull " + count); if (rowCount % 20 == 0) persistenceService.getSession().flush(); // These exception might be because the other entires in instrument which is not in egf_brs_bankstatements // so any issues leave it for manual update } catch (final HibernateException e) { if (e.getCause().getMessage().contains("single-row subquery returns more")) backupdateFailureQuery.setString("e", BRS_MESSAGE_MORE_THAN_ONE_MATCH); else backupdateFailureQuery.setString("e", e.getMessage()); backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.executeUpdate(); } catch (final Exception e) { backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.setString("e", e.getMessage()); backupdateFailureQuery.executeUpdate(); } } }
From source file:org.egov.egf.web.actions.brs.ManualReconcileHelper.java
License:Open Source License
public Map<String, String> getUnReconciledDrCr(Long bankAccId, Date fromDate, Date toDate) { Map<String, String> unreconMap = new LinkedHashMap<String, String>(); //String ="decode(iv.voucherHeaderId,null,0,ih.instrumentAmount)"; String instrumentsForBrsEntryTotal = "case when br.voucherHeaderId is null then ih.instrumentAmount else 0 end"; //String instrumentsForOtherTotal="decode(br.voucherHeaderId,null,ih.instrumentAmount,0)"; String voucherExcludeStatuses = getExcludeStatuses(); String totalQuery = "SELECT (sum(CASE WHEN ih.ispaycheque='1' then ih.instrumentAmount else 0 end )) AS \"brs_creditTotal\", " + " (sum(CASE WHEN ih.ispaycheque = '0' then ih.instrumentAmount else 0 end)) AS \"brs_debitTotal\" " + " FROM egf_instrumentheader ih WHERE ih.bankAccountId =:bankAccountId " + " AND IH.INSTRUMENTDATE >= :fromDate" + " AND IH.INSTRUMENTDATE <= :toDate" + " AND ( (ih.ispaycheque='0' and ih.id_status=(select id from egw_status where moduletype='Instrument' " + " and description='Deposited'))or (ih.ispaycheque='1' and ih.id_status=(select id from egw_status where " + " moduletype='Instrument' and description='New'))) " + " and ih.instrumentnumber is not null"; //see u might need to exclude brs entries here String otherTotalQuery = " SELECT (sum(case when ih.ispaycheque='1' then ih.instrumentAmount else 0 end)) AS \"brs_creditTotalOthers\", " + " (sum(case when ih.ispaycheque= '0' then ih.instrumentAmount else 0 end)) AS \"brs_debitTotalOthers\" " + " FROM egf_instrumentheader ih WHERE ih.bankAccountId =:bankAccountId" + " AND IH.transactiondate >= :fromDate" + " AND IH.transactiondate <= :toDate " + " AND ( (ih.ispaycheque='0' and ih.id_status=(select id from egw_status where moduletype='Instrument'" + " and description='Deposited'))or (ih.ispaycheque='1' and ih.id_status=(select id from egw_status where" + " moduletype='Instrument' and description='New'))) " + " AND ih.transactionnumber is not null"; String brsEntryQuery = " SELECT (sum(case when ih.ispaycheque= '1' then " + instrumentsForBrsEntryTotal + " else 0 end )) AS \"brs_creditTotalBrsEntry\", " + " (sum(case when ih.ispaycheque= '0' then " + instrumentsForBrsEntryTotal + " else 0 end)) AS \"brs_debitTotalBrsEntry\" " + " FROM egf_instrumentheader ih, bankentries br WHERE ih.bankAccountId = :bankAccountId" + " AND IH.transactiondate >= :fromDate " + " AND IH.transactiondate <= :toDate " + " AND ( (ih.ispaycheque='0' and ih.id_status=(select id from egw_status where moduletype='Instrument' " + " and description='Deposited')) or (ih.ispaycheque='1' and ih.id_status=(select id from egw_status where moduletype='Instrument' and description='New'))) " + " AND br.instrumentHeaderid=ih.id and ih.transactionnumber is not null"; if (LOGGER.isInfoEnabled()) LOGGER.info(" query for total : " + totalQuery); String unReconciledDrCr = ""; String creditTotal = null;/*w w w .ja va 2 s. c o m*/ String creditOthertotal = null; String debitTotal = null; String debitOtherTotal = null; String creditTotalBrsEntry = null; String debitTotalBrsEntry = null; try { SQLQuery totalSQLQuery = persistenceService.getSession().createSQLQuery(totalQuery); totalSQLQuery.setLong("bankAccountId", bankAccId); totalSQLQuery.setDate("fromDate", fromDate); totalSQLQuery.setDate("toDate", toDate); List list = totalSQLQuery.list(); if (list.size() > 0) { if (LOGGER.isDebugEnabled()) LOGGER.debug(list.get(0)); Object[] my = (Object[]) list.get(0); creditTotal = my[0] != null ? my[0].toString() : null; debitTotal = my[1] != null ? my[1].toString() : null; } if (LOGGER.isInfoEnabled()) LOGGER.info(" query for other than cheque/DD: " + otherTotalQuery); totalSQLQuery = persistenceService.getSession().createSQLQuery(otherTotalQuery); totalSQLQuery.setLong("bankAccountId", bankAccId); totalSQLQuery.setDate("fromDate", fromDate); totalSQLQuery.setDate("toDate", toDate); list = totalSQLQuery.list(); if (list.size() > 0) { if (LOGGER.isDebugEnabled()) LOGGER.debug(list.get(0)); Object[] my = (Object[]) list.get(0); creditOthertotal = my[0] != null ? my[0].toString() : null; debitOtherTotal = my[1] != null ? my[1].toString() : null; } if (LOGGER.isInfoEnabled()) LOGGER.info(" query for bankEntries: " + brsEntryQuery); totalSQLQuery = persistenceService.getSession().createSQLQuery(brsEntryQuery); totalSQLQuery.setLong("bankAccountId", bankAccId); totalSQLQuery.setDate("fromDate", fromDate); totalSQLQuery.setDate("toDate", toDate); list = totalSQLQuery.list(); if (list.size() > 0) { if (LOGGER.isDebugEnabled()) LOGGER.debug(list.get(0)); Object[] my = (Object[]) list.get(0); creditTotalBrsEntry = my[0] != null ? my[0].toString() : null; debitTotalBrsEntry = my[1] != null ? my[1].toString() : null; } /* ReconcileBean reconBean=new ReconcileBean(); reconBean.setCreditAmount(BigDecimal.valueOf(creditTotal)); reconBean.setDebitAmount(debitTotal); */ creditTotal = creditTotal == null ? "0" : creditTotal; debitTotal = debitTotal == null ? "0" : debitTotal; creditOthertotal = creditOthertotal == null ? "0" : creditOthertotal; debitOtherTotal = debitOtherTotal == null ? "0" : debitOtherTotal; debitTotalBrsEntry = debitTotalBrsEntry == null ? "0" : debitTotalBrsEntry; unreconMap.put("Cheque/DD/Cash Payments", creditTotal); unreconMap.put("Cheque/DD/Cash Receipts", debitTotal); unreconMap.put("RTGS Payments", creditOthertotal); unreconMap.put("Other Receipts", debitOtherTotal); unreconMap.put("BRS Entry", debitTotalBrsEntry); /*//unReconciledDrCr="Cheque/DD/Cash Payments:"+(creditTotal != null ? creditTotal : "0" )+",RTGS Payments:"+(creditOthertotal!= null ? creditOthertotal : "0") +",Cheque/DD/Cash Receipts:"+(debitTotal!= null ? debitTotal : "0") +",Other Receipts:"+( debitOtherTotal!= null ? debitOtherTotal : "0")+""+ "/"+(creditTotalBrsEntry!= null ? creditTotalBrsEntry : "0") +",Net:"+( debitTotalBrsEntry!= null ? debitTotalBrsEntry : "0")+"";*/ } catch (Exception e) { LOGGER.error("Exp in getUnReconciledDrCr" + e.getMessage()); } return unreconMap; }
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 www .j a va 2 s .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.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 . jav a2 s . c om 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/* w w w . ja v a2s . 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
/** * updates generalledger table's remittance date to date on which remittance created for recovery * @param glIds/*from www. j a v a 2 s. c o m*/ */ private void updateRemittancedateInLeddger(final List<Integer> glIds) { if (LOGGER.isDebugEnabled()) LOGGER.debug( "Starting updateRemittancedateInLeddger with " + glIds.size() + " glIds detailed as" + glIds); int size = glIds.size(); int suceessCount = 0; if (size <= 999) { final SQLQuery glQuery = persistenceService.getSession() .createSQLQuery("update generalledger set remittancedate=:date where id in (:glIds)"); glQuery.setDate("date", new java.sql.Date(new Date().getTime())); glQuery.setParameterList("glIds", glIds); suceessCount += glQuery.executeUpdate(); } else { // this part is incomplete int fromIndex = 0; int toIndex = 999; while (size % 1000 >= 1000) { final SQLQuery glQuery = persistenceService.getSession() .createSQLQuery("update generalledger set remittancedate=:date where id in (:glIds)"); glQuery.setDate("date", new java.sql.Date(new Date().getTime())); glQuery.setParameterList("glIds", glIds.subList(fromIndex, toIndex)); suceessCount += glQuery.executeUpdate(); fromIndex += 1000; toIndex += 1000; size -= 1000; } final SQLQuery glQuery = persistenceService.getSession() .createSQLQuery("update generalledger set remittancedate=:date where id in (:glIds)"); glQuery.setDate("date", new java.sql.Date(new Date().getTime())); glQuery.setParameterList("glIds", glIds.subList(toIndex + 1, size)); } if (LOGGER.isDebugEnabled()) LOGGER.debug("Completed updateRemittancedateInLeddger " + suceessCount); }
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(); }