List of usage examples for org.hibernate.type LongType INSTANCE
LongType INSTANCE
To view the source code for org.hibernate.type LongType INSTANCE.
Click Source Link
From source file:org.egov.egf.web.actions.voucher.CommonAction.java
License:Open Source License
@Action(value = "/voucher/common-ajaxLoadProjectCodesForSubScheme") public String ajaxLoadProjectCodesForSubScheme() { if (LOGGER.isDebugEnabled()) LOGGER.debug("Starting ajaxLoadProjectCodesForSubScheme..."); final String sql = "select pc.id as id,pc.code as code,pc.name as name from egw_projectcode pc,egf_subscheme_project ssp where pc.id=ssp.projectcodeid and ssp.subschemeid=" + subSchemeId;/*from w w w.j a v a2 s . c o m*/ final SQLQuery pcQuery = persistenceService.getSession().createSQLQuery(sql); pcQuery.addScalar("id", LongType.INSTANCE).addScalar("code").addScalar("name") .setResultTransformer(Transformers.aliasToBean(LoanGrantBean.class)); projectCodeList = pcQuery.list(); if (LOGGER.isDebugEnabled()) LOGGER.debug("Completed ajaxLoadProjectCodesForSubScheme."); return "projectcodes"; }
From source file:org.egov.services.cheque.ChequeAssignmentService.java
License:Open Source License
@SuppressWarnings("unchecked") public List<ChequeAssignment> getPaymentVouchersConsolidatedMode(final Map<String, String[]> parameters, final CVoucherHeader voucherHeader) throws ParseException { if (LOGGER.isDebugEnabled()) LOGGER.debug("Starting getPaymentVouchersConsolidatedMode..."); final String filterConditions = getFilterParamaters(parameters, voucherHeader); setStatusValues();//from www .j a va 2 s. c o m query = getSession().createSQLQuery( "select vh.id as voucherid ,vh.voucherNumber as voucherNumber ,vh.voucherDate as voucherDate,sum(misbill.paidamount) as paidAmount,current_date as chequeDate from Paymentheader ph,voucherheader vh,vouchermis vmis, Miscbilldetail misbill " + " where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vmis.voucherheaderid= vh.id and vh.status =" + approvedstatus + " " + filterConditions + " " + " and vh.id not in (select voucherHeaderId from egf_InstrumentVoucher iv, EGF_INSTRUMENTHEADER ih where iv.INSTRUMENTHEADERID = ih.id and ih.ID_STATUS in (" + statusId + ") ) and vh.type='" + FinancialConstants.STANDARD_VOUCHER_TYPE_PAYMENT + "' and vh.name NOT IN ('" + FinancialConstants.PAYMENTVOUCHER_NAME_REMITTANCE + "' , '" + FinancialConstants.PAYMENTVOUCHER_NAME_SALARY + "') " + " group by vh.id,vh.voucherNumber,vh.voucherDate order by vh.voucherNumber ") .addScalar("voucherid", LongType.INSTANCE).addScalar("voucherNumber").addScalar("voucherDate") .addScalar("paidAmount", BigDecimalType.INSTANCE).addScalar("chequeDate") .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug("Completed getPaymentVouchersConsolidatedMode."); return query.list(); }
From source file:org.egov.services.cheque.ChequeAssignmentService.java
License:Open Source License
@SuppressWarnings("unchecked") public List<ChequeAssignment> getContractorSupplierPaymentsForChequeAssignment( final Map<String, String[]> parameters) throws ParseException { if (LOGGER.isDebugEnabled()) LOGGER.debug("Starting getContractorSupplierPaymentsForChequeAssignment..."); final Bankaccount ba = (Bankaccount) persistenceService.find(" from Bankaccount where id=?", Long.valueOf(parameters.get("bankaccount")[0])); String billCondition = "";// "'"+FinancialConstants.STANDARD_EXPENDITURETYPE_CONTINGENT+"'"; if (null != parameters.get("voucherName") && null != parameters.get("voucherName")[0] && FinancialConstants.PAYMENTVOUCHER_NAME_PENSION .equalsIgnoreCase(parameters.get("voucherName")[0])) billCondition = " in ('" + FinancialConstants.STANDARD_EXPENDITURETYPE_PENSION + "') "; else//from ww w . j a va 2s. com billCondition = " not in ('" + FinancialConstants.STANDARD_EXPENDITURETYPE_CONTINGENT + "','" + FinancialConstants.STANDARD_EXPENDITURETYPE_PENSION + "')"; final String supplierBillPaymentQuery = "select vh.id as voucherid ,vh.voucherNumber as voucherNumber ,vh.voucherDate as voucherDate ,0 as detailtypeid ,0 as detailkeyid ,misbill.paidto as paidTo,sum(misbill.paidamount) as paidAmount,current_date as chequeDate " + " from Paymentheader ph,voucherheader vh,vouchermis vmis, Miscbilldetail misbill ,voucherheader billvh, eg_billregister br, eg_billregistermis billmis, generalledger gl, " + " egf_instrumentvoucher iv right outer join voucherheader pvh on (pvh.id=iv.VOUCHERHEADERID) " + " where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vh.name='Bill Payment' and vmis.voucherheaderid= vh.id and vh.status =" + approvedstatus + " " + filterConditions + " " + " and gl.voucherheaderid =vh.id and gl.creditamount>0 and gl.glcodeid in (" + ba.getChartofaccounts().getId() + ") and br.id=billmis.billid and billmis.voucherheaderid=billvh.id and br.expendituretype " + billCondition + " and misbill.billvhid=billvh.id " + " and pvh.id=vh.id and iv.id IS NULL group by vh.id,vh.voucherNumber,vh.voucherDate,misbill.paidto " + " union select vh.id as voucherid ,vh.voucherNumber as voucherNumber ,vh.voucherDate as voucherDate ,0 as detailtypeid ,0 as detailkeyid ,misbill.paidto as paidTo,sum(misbill.paidamount) as paidAmount,current_date as chequeDate " + " from Paymentheader ph,voucherheader vh,vouchermis vmis, Miscbilldetail misbill ,voucherheader billvh, eg_billregister br, eg_billregistermis billmis, generalledger gl, " + " egf_instrumentvoucher iv right outer join voucherheader pvh on (pvh.id=iv.VOUCHERHEADERID) left outer join egf_instrumentheader ih on (ih.ID=iv.INSTRUMENTHEADERID) " + " where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vh.name='Bill Payment' and vmis.voucherheaderid= vh.id and vh.status =" + approvedstatus + " " + filterConditions + " " + " and gl.voucherheaderid =vh.id and gl.creditamount>0 and gl.glcodeid in (" + ba.getChartofaccounts().getId() + ") and br.id=billmis.billid and billmis.voucherheaderid=billvh.id and br.expendituretype " + billCondition + " and misbill.billvhid=billvh.id " + " and pvh.id=vh.id and ih.id IN (SELECT MAX(ih.id) FROM egf_instrumentvoucher iv RIGHT OUTER JOIN voucherheader pvh ON (pvh.id=iv.VOUCHERHEADERID) LEFT OUTER JOIN " + " egf_instrumentheader ih ON (ih.ID =iv.INSTRUMENTHEADERID) WHERE pvh.id =vh.id AND ih.payto =misbill.paidto) AND ih.ID_STATUS NOT IN (" + statusId + ") group by vh.id,vh.voucherNumber,vh.voucherDate,misbill.paidto " + " union select vh.id as voucherid ,vh.voucherNumber as voucherNumber ,vh.voucherDate as voucherDate ,0 as detailtypeid ,0 as detailkeyid ,misbill.paidto as paidTo,sum(misbill.paidamount) as paidAmount,current_date as chequeDate " + " from Paymentheader ph,voucherheader vh,vouchermis vmis, Miscbilldetail misbill ,voucherheader billvh, eg_billregister br, eg_billregistermis billmis, generalledger gl " + " where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vh.name='Bill Payment' and vmis.voucherheaderid= vh.id and vh.status =" + approvedstatus + " " + filterConditions + " " + " and gl.voucherheaderid =vh.id and gl.creditamount>0 and gl.glcodeid in (" + ba.getChartofaccounts().getId() + ") and br.id=billmis.billid and billmis.voucherheaderid=billvh.id and br.expendituretype " + billCondition + " and misbill.billvhid=billvh.id " + " and misbill.paidto NOT IN (SELECT DISTINCT(ih.payto) FROM egf_instrumentvoucher iv RIGHT OUTER JOIN voucherheader pvh " + " ON (pvh.id=iv.VOUCHERHEADERID) LEFT OUTER JOIN egf_instrumentheader ih ON (ih.ID=iv.INSTRUMENTHEADERID) WHERE pvh.id=vh.id AND ih.ID_STATUS IN (" + statusId + ")) " + " group by vh.id,vh.voucherNumber,vh.voucherDate,misbill.paidto order by paidto,voucherNumber "; query = getSession().createSQLQuery(supplierBillPaymentQuery).addScalar("voucherid", LongType.INSTANCE) .addScalar("voucherNumber").addScalar("voucherDate").addScalar("detailtypeid", LongType.INSTANCE) .addScalar("detailkeyid", LongType.INSTANCE).addScalar("paidTo") .addScalar("paidAmount", BigDecimalType.INSTANCE).addScalar("chequeDate") .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug("CONTRACTOR/SUPLLIER BILL PAYMENT QUERY - " + supplierBillPaymentQuery); if (LOGGER.isDebugEnabled()) LOGGER.debug("Completed getContractorSupplierPaymentsForChequeAssignment."); return query.list(); }
From source file:org.egov.services.cheque.ChequeAssignmentService.java
License:Open Source License
@SuppressWarnings("unchecked") public List<ChequeAssignment> getDirectBankPaymentsForChequeAssignment() throws ParseException { if (LOGGER.isDebugEnabled()) LOGGER.debug("Starting getDirectBankPaymentsForChequeAssignment..."); final String bankPaymentQuery = "select vh.id as voucherid ,vh.voucherNumber as voucherNumber ,0 as detailtypeid ,0 as detailkeyid,vh.voucherDate as voucherDate ,misbill.paidto as paidTo,sum(misbill.paidamount) as paidAmount,current_date as chequeDate" + " From Paymentheader ph,voucherheader vh,vouchermis vmis, Miscbilldetail misbill, " + " egf_instrumentvoucher iv right outer join voucherheader pvh on (pvh.id=iv.VOUCHERHEADERID)" + " Where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vh.name in ('Direct Bank Payment','Advance Payment') and vmis.voucherheaderid= vh.id and vh.status =" + approvedstatus + " " + filterConditions + " " + " and pvh.id=vh.id and iv.id IS NULL group by vh.id,vh.voucherNumber,vh.voucherDate,misbill.paidto " + " union select vh.id as voucherid ,vh.voucherNumber as voucherNumber ,0 as detailtypeid ,0 as detailkeyid,vh.voucherDate as voucherDate ,misbill.paidto as paidTo,sum(misbill.paidamount) as paidAmount,current_date as chequeDate" + " From Paymentheader ph,voucherheader vh,vouchermis vmis, Miscbilldetail misbill, " + " egf_instrumentvoucher iv right outer join voucherheader pvh on (pvh.id=iv.VOUCHERHEADERID)" + " left outer join egf_instrumentheader ih on (ih.ID=iv.INSTRUMENTHEADERID)" + " Where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vh.name in ('Direct Bank Payment','Advance Payment') and vmis.voucherheaderid= vh.id and vh.status =" + approvedstatus + " " + filterConditions + " " + " and pvh.id=vh.id and ih.id IN (SELECT MAX(ih.id) FROM egf_instrumentvoucher iv RIGHT OUTER JOIN voucherheader pvh ON (pvh.id=iv.VOUCHERHEADERID) LEFT OUTER JOIN " + " egf_instrumentheader ih ON (ih.ID =iv.INSTRUMENTHEADERID) WHERE pvh.id =vh.id AND ih.payto =misbill.paidto) AND ih.ID_STATUS NOT IN (" + statusId + ") group by vh.id,vh.voucherNumber,vh.voucherDate,misbill.paidto " + " union select vh.id as voucherid ,vh.voucherNumber as voucherNumber ,0 as detailtypeid ,0 as detailkeyid,vh.voucherDate as voucherDate ,misbill.paidto as paidTo,sum(misbill.paidamount) as paidAmount,current_date as chequeDate" + " From Paymentheader ph,voucherheader vh,vouchermis vmis, Miscbilldetail misbill " + " Where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vh.name in ('Direct Bank Payment','Advance Payment') and vmis.voucherheaderid= vh.id and vh.status =" + approvedstatus + " " + filterConditions + " " + " and misbill.paidto NOT IN (SELECT DISTINCT(ih.payto) FROM egf_instrumentvoucher iv RIGHT OUTER JOIN voucherheader pvh " + " ON (pvh.id=iv.VOUCHERHEADERID) LEFT OUTER JOIN egf_instrumentheader ih ON (ih.ID=iv.INSTRUMENTHEADERID) WHERE pvh.id=vh.id AND ih.ID_STATUS IN (" + statusId + ")) group by vh.id,vh.voucherNumber,vh.voucherDate,misbill.paidto " + " order by paidto,voucherNumber "; query = getSession().createSQLQuery(bankPaymentQuery).addScalar("voucherid", LongType.INSTANCE) .addScalar("voucherNumber").addScalar("detailtypeid", LongType.INSTANCE) .addScalar("detailkeyid", LongType.INSTANCE).addScalar("voucherDate").addScalar("paidTo") .addScalar("paidAmount", BigDecimalType.INSTANCE).addScalar("chequeDate") .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug("DIRECT BANK PAYMENT QUERY - " + bankPaymentQuery); if (LOGGER.isDebugEnabled()) LOGGER.debug("Completed getDirectBankPaymentsForChequeAssignment."); return query.list(); }
From source file:org.egov.services.cheque.ChequeAssignmentService.java
License:Open Source License
@SuppressWarnings("unchecked") private void getExpenseBillPaymentsHavingNoCheques() throws NumberFormatException, ApplicationException { if (LOGGER.isDebugEnabled()) LOGGER.debug("Starting getExpenseBillPaymentsHavingNoCheques... NOT YET ASSIGNED"); List<ChequeAssignment> billChequeAssignmentList = null; final List<Long> billVHIds = new ArrayList<Long>(); final List<Long> billVHIdsForDebtitSideCC = new ArrayList<Long>(); List<Object[]> generalLedgerDetailList = new ArrayList<Object[]>(); List<Object[]> generalLedgerDetailListForDebtitSideCC = new ArrayList<Object[]>(); final Map<Long, List<Object[]>> billVHIdAndgeneralLedgerDetailListMap = new HashMap<Long, List<Object[]>>(); final Map<Long, List<Object[]>> billVHIdAndGLDListForDebtitSideCCMap = new HashMap<Long, List<Object[]>>(); final String strQuery = "select vh.id as voucherid ,vh.voucherNumber as voucherNumber ,vh.voucherDate as voucherDate ,0 as detailtypeid ,0 as detailkeyid ," + " misbill.paidto as paidTo,case when sum(misbill.paidamount) is null then 0 else sum(misbill.paidamount) end as paidAmount,current_date as chequeDate, misbill.billvhid as billVHId " + " from Paymentheader ph,egf_instrumentvoucher iv right outer join voucherheader vh on (vh.id=iv.VOUCHERHEADERID) ,vouchermis vmis, Miscbilldetail misbill, generalledger gl ,voucherheader billvh, eg_billregister br,eg_billregistermis billmis " + " where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vmis.voucherheaderid= vh.id and vh.status =" + approvedstatus + " " + filterConditions + " " + " and gl.voucherheaderid =vh.id and gl.creditamount>0 and misbill.billvhid=billvh.id and br.id=billmis.billid and billmis.voucherheaderid=billvh.id and br.expendituretype='" + FinancialConstants.STANDARD_EXPENDITURETYPE_CONTINGENT + "' and iv.id is null " + " group by misbill.billvhid,vh.id,vh.voucherNumber,vh.voucherDate,misbill.paidto "; query = getSession().createSQLQuery(strQuery).addScalar("voucherid", LongType.INSTANCE) .addScalar("voucherNumber").addScalar("voucherDate") .addScalar("paidAmount", BigDecimalType.INSTANCE).addScalar("chequeDate").addScalar("paidTo") .addScalar("billVHId", LongType.INSTANCE).addScalar("detailtypeid", LongType.INSTANCE) .addScalar("detailkeyid", LongType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug("NOT YET ASSIGNED No cheques - " + strQuery); billChequeAssignmentList = query.list(); for (final ChequeAssignment ca : billChequeAssignmentList) billVHIds.add(ca.getBillVHId().longValue()); if (billVHIds != null && billVHIds.size() > 0) generalLedgerDetailList = getDetailTypeKeyAmtForBillVHId(billVHIds); for (final Object[] gld : generalLedgerDetailList) if (billVHIdAndgeneralLedgerDetailListMap.containsKey(getLongValue(gld[3]))) billVHIdAndgeneralLedgerDetailListMap.get(getLongValue(gld[3])).add(gld); else {//from w ww.j a v a2 s. c o m final List<Object[]> generalLedgerDetails = new ArrayList<Object[]>(); generalLedgerDetails.add(gld); billVHIdAndgeneralLedgerDetailListMap.put(getLongValue(gld[3]), generalLedgerDetails); } for (final ChequeAssignment ca : billChequeAssignmentList) { final List<Object[]> detailTypeKeyAmtList = billVHIdAndgeneralLedgerDetailListMap .get(ca.getBillVHId().longValue()); if (detailTypeKeyAmtList == null || detailTypeKeyAmtList.size() == 0) billVHIdsForDebtitSideCC.add(ca.getBillVHId().longValue()); } if (billVHIdsForDebtitSideCC != null && billVHIdsForDebtitSideCC.size() > 0) generalLedgerDetailListForDebtitSideCC = getDetailTypeKeyAmtForDebtitSideCC(billVHIdsForDebtitSideCC); for (final Object[] gld : generalLedgerDetailListForDebtitSideCC) if (billVHIdAndGLDListForDebtitSideCCMap.containsKey(getLongValue(gld[3]))) billVHIdAndGLDListForDebtitSideCCMap.get(getLongValue(gld[3])).add(gld); else { final List<Object[]> generalLedgerDetails = new ArrayList<Object[]>(); generalLedgerDetails.add(gld); billVHIdAndGLDListForDebtitSideCCMap.put(getLongValue(gld[3]), generalLedgerDetails); } for (final ChequeAssignment chqAssgn : billChequeAssignmentList) { if (LOGGER.isDebugEnabled()) LOGGER.debug("NOT YET ASSIGNED: Start Checking for Billvhid " + chqAssgn.getBillVHId()); if (LOGGER.isDebugEnabled()) LOGGER.debug("NOT YET ASSIGNED: checking getDetailTypeKeyAmtForBillVHId for Net payable codes"); List<Object[]> detailTypeKeyAmtList = billVHIdAndgeneralLedgerDetailListMap .get(chqAssgn.getBillVHId()) != null ? billVHIdAndgeneralLedgerDetailListMap.get(chqAssgn.getBillVHId()) : new ArrayList<Object[]>(); if (detailTypeKeyAmtList != null && detailTypeKeyAmtList.size() != 0) { if (LOGGER.isDebugEnabled()) LOGGER.debug("NOT YET ASSIGNED: detailTypeKeyAmtList for Billvhid " + chqAssgn.getBillVHId() + " size :" + detailTypeKeyAmtList.size()); if (detailTypeKeyAmtList.size() < 2) { tempExpenseChequeAssignmentList.add(chqAssgn); if (LOGGER.isDebugEnabled()) LOGGER.debug( "NOT YET ASSIGNED: adding inside detailTypeKeyAmtList.size()<2 block to Assignment List\n" + chqAssgn); } else for (final Object[] detailTypeKeyAmtObj : detailTypeKeyAmtList) { final ChequeAssignment ca = new ChequeAssignment(); ca.setVoucherid(new BigDecimal(chqAssgn.getVoucherid())); ca.setVoucherNumber(chqAssgn.getVoucherNumber()); if (LOGGER.isDebugEnabled()) LOGGER.debug("NOT YET ASSIGNED: Voucher Number" + chqAssgn.getVoucherNumber()); ca.setVoucherDate(chqAssgn.getVoucherDate()); ca.setPaidAmount(BigDecimal.valueOf(Double.valueOf(detailTypeKeyAmtObj[2].toString()))); ca.setChequeDate(chqAssgn.getChequeDate()); ca.setPaidTo(getEntity(Integer.parseInt(detailTypeKeyAmtObj[0].toString()), (Serializable) detailTypeKeyAmtObj[1]).getName()); ca.setDetailtypeid(Long.valueOf(detailTypeKeyAmtObj[0].toString())); ca.setDetailkeyid(Long.valueOf(detailTypeKeyAmtObj[1].toString())); if (LOGGER.isDebugEnabled()) LOGGER.debug( "NOT YET ASSIGNED: detailTypeKeyAmtList.size()>=2 block to Assignment List\n" + ca); tempExpenseChequeAssignmentList.add(ca); } } else { if (LOGGER.isDebugEnabled()) LOGGER.debug("NOT YET ASSIGNED: checking getDetailTypeKeyAmtForDebtitSideCC for " + chqAssgn.getBillVHId()); detailTypeKeyAmtList = billVHIdAndGLDListForDebtitSideCCMap.get(chqAssgn.getBillVHId()) != null ? billVHIdAndGLDListForDebtitSideCCMap.get(chqAssgn.getBillVHId()) : new ArrayList<Object[]>(); if (detailTypeKeyAmtList == null || detailTypeKeyAmtList.size() == 0) { if (LOGGER.isDebugEnabled()) LOGGER.debug( "NOT YET ASSIGNED: adding to checkassignlist as detailTypeKeyAmtList is null or zero" + chqAssgn); tempExpenseChequeAssignmentList.add(chqAssgn); } else if (detailTypeKeyAmtList != null && detailTypeKeyAmtList.size() == 1) { if (LOGGER.isDebugEnabled()) LOGGER.debug("NOT YET ASSIGNED: adding to checkassignlist as detailTypeKeyAmtList is 1" + chqAssgn); tempExpenseChequeAssignmentList.add(chqAssgn); } else { BigDecimal deduction = BigDecimal.valueOf(0); // THIS dedcution will work for only one subledger .If more than one you cannot have non subledger dedcution // as // you can not distribute that among multiple people // Also this needs same subledger entity used on debit and credit side if (detailTypeKeyAmtList.size() == 1) deduction = getNonSubledgerDeductions(chqAssgn.getBillVHId()); Map<String, BigDecimal> dedMap = new HashMap<String, BigDecimal>(); dedMap = getSubledgerAmtForDeduction(chqAssgn.getBillVHId()); String key = ""; for (final Object[] obj : detailTypeKeyAmtList) { final ChequeAssignment c = new ChequeAssignment(); c.setChequeDate(chqAssgn.getChequeDate()); c.setVoucherHeaderId(chqAssgn.getVoucherid()); c.setVoucherNumber(chqAssgn.getVoucherNumber()); if (LOGGER.isDebugEnabled()) LOGGER.debug("NOT YET ASSIGNED: Voucher Number :" + chqAssgn.getVoucherNumber()); c.setVoucherDate(chqAssgn.getVoucherDate()); c.setDetailtypeid(Long.valueOf(obj[0].toString())); c.setDetailkeyid(Long.valueOf(obj[1].toString())); key = obj[0].toString() + DELIMETER + obj[1].toString(); // deduct only if deduction is available if (deduction != null) obj[2] = (BigDecimal.valueOf(Double.valueOf(obj[2].toString()))).subtract(deduction); c.setPaidAmount( dedMap.get(key) == null ? BigDecimal.valueOf(Double.valueOf(obj[2].toString())) : (BigDecimal.valueOf(Double.valueOf(obj[2].toString()))) .subtract(dedMap.get(key))); c.setPaidTo(getEntity(Integer.valueOf(obj[0].toString()), (Serializable) obj[1]).getName()); if (LOGGER.isDebugEnabled()) LOGGER.debug( "NOT YET ASSIGNED: detailTypeKeyAmtList.size()>=2 block to Assignment List\n" + c); tempExpenseChequeAssignmentList.add(c); } } } } if (LOGGER.isDebugEnabled()) LOGGER.debug("Completed getExpenseBillPaymentsHavingNoCheques."); }
From source file:org.egov.services.cheque.ChequeAssignmentService.java
License:Open Source License
@SuppressWarnings("unchecked") private void getExpenseBillPaymentsWithNoSurrenderedCheque() throws NumberFormatException, ApplicationException { if (LOGGER.isDebugEnabled()) LOGGER.debug("Starting getExpenseBillPaymentsWithNoSurrenderedCheque...ALREADY ASSIGNED: "); final List<Long> billVHIds = new ArrayList<Long>(); final List<Long> billVHIdsForDebtitSideCC = new ArrayList<Long>(); final Map<Long, List<Object[]>> billVHIdAndgeneralLedgerDetailListMap = new HashMap<Long, List<Object[]>>(); final Map<Long, List<Object[]>> billVHIdAndGLDListForDebtitSideCCMap = new HashMap<Long, List<Object[]>>(); List<Object[]> generalLedgerDetailList = new ArrayList<Object[]>(); List<Object[]> generalLedgerDetailListForDebtitSideCC = new ArrayList<Object[]>(); List<ChequeAssignment> billChequeAssignmentList = null; final String strQuery = " select vh.id as voucherid ,vh.voucherNumber as voucherNumber ,vh.voucherDate as voucherDate ,0 as detailtypeid ,0 as detailkeyid ," + " misbill.paidto as paidTo,case when sum(misbill.paidamount) is null then 0 else sum(misbill.paidamount) end as paidAmount,current_date as chequeDate,misbill.billvhid as billVHId " + " from Paymentheader ph, voucherheader vh ,vouchermis vmis, Miscbilldetail misbill , generalledger gl,voucherheader billvh, eg_billregister br,eg_billregistermis billmis " + " where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vmis.voucherheaderid= vh.id and vh.status =" + approvedstatus + " " + filterConditions + " " + " and gl.voucherheaderid =vh.id and gl.creditamount>0 and misbill.billvhid=billvh.id and br.id=billmis.billid and billmis.voucherheaderid=billvh.id and br.expendituretype='" + FinancialConstants.STANDARD_EXPENDITURETYPE_CONTINGENT + "' " + " and not exists(select 1 from egf_instrumentvoucher iv, egf_instrumentheader ih where ih.id= iv.instrumentheaderid and iv.voucherheaderid=vh.id and ih.id_status not in (" + statusId + ") ) " + " and exists (select 1 from egf_instrumentvoucher iv where iv.voucherheaderid=vh.id) group by misbill.billvhid,vh.id,vh.voucherNumber,vh.voucherDate,misbill.paidto "; final Query query = getSession().createSQLQuery(strQuery).addScalar("voucherid", LongType.INSTANCE) .addScalar("voucherNumber").addScalar("voucherDate") .addScalar("paidAmount", BigDecimalType.INSTANCE).addScalar("chequeDate").addScalar("paidTo") .addScalar("billVHId", LongType.INSTANCE).addScalar("detailtypeid", LongType.INSTANCE) .addScalar("detailkeyid", LongType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug("ALREADY ASSIGNED: No surrendered cheques - " + strQuery); billChequeAssignmentList = query.list(); for (final ChequeAssignment ca : billChequeAssignmentList) billVHIds.add(ca.getBillVHId().longValue()); if (billVHIds != null && billVHIds.size() > 0) generalLedgerDetailList = getDetailTypeKeyAmtForBillVHId(billVHIds); for (final Object[] gld : generalLedgerDetailList) if (billVHIdAndgeneralLedgerDetailListMap.containsKey(getLongValue(gld[3]))) billVHIdAndgeneralLedgerDetailListMap.get(getLongValue(gld[3])).add(gld); else {/* w ww . j a v a2 s .c om*/ final List<Object[]> generalLedgerDetails = new ArrayList<Object[]>(); generalLedgerDetails.add(gld); billVHIdAndgeneralLedgerDetailListMap.put(getLongValue(gld[3]), generalLedgerDetails); } for (final ChequeAssignment ca : billChequeAssignmentList) { final List<Object[]> detailTypeKeyAmtList = billVHIdAndgeneralLedgerDetailListMap .get(ca.getBillVHId().longValue()); if (detailTypeKeyAmtList == null || detailTypeKeyAmtList.size() == 0) billVHIdsForDebtitSideCC.add(ca.getBillVHId().longValue()); } if (billVHIdsForDebtitSideCC != null && billVHIdsForDebtitSideCC.size() > 0) generalLedgerDetailListForDebtitSideCC = getDetailTypeKeyAmtForDebtitSideCC(billVHIdsForDebtitSideCC); for (final Object[] gld : generalLedgerDetailListForDebtitSideCC) if (billVHIdAndGLDListForDebtitSideCCMap.containsKey(getLongValue(gld[3]))) billVHIdAndGLDListForDebtitSideCCMap.get(getLongValue(gld[3])).add(gld); else { final List<Object[]> generalLedgerDetails = new ArrayList<Object[]>(); generalLedgerDetails.add(gld); billVHIdAndGLDListForDebtitSideCCMap.put(getLongValue(gld[3]), generalLedgerDetails); } for (final ChequeAssignment chqAssgn : billChequeAssignmentList) { if (LOGGER.isDebugEnabled()) LOGGER.debug("ALREADY ASSIGNED: Start Checking for Billvhid " + chqAssgn.getBillVHId()); if (LOGGER.isDebugEnabled()) LOGGER.debug("ALREADY ASSIGNED: checking getDetailTypeKeyAmtForBillVHId for Net payable codes"); List<Object[]> detailTypeKeyAmtList = billVHIdAndgeneralLedgerDetailListMap .get(chqAssgn.getBillVHId().longValue()) != null ? billVHIdAndgeneralLedgerDetailListMap.get(chqAssgn.getBillVHId().longValue()) : new ArrayList<Object[]>(); if (detailTypeKeyAmtList != null && detailTypeKeyAmtList.size() != 0) { if (LOGGER.isDebugEnabled()) LOGGER.debug("ALREADY ASSIGNED: detailTypeKeyAmtList for Billvhid " + chqAssgn.getBillVHId() + " size :" + detailTypeKeyAmtList.size()); if (detailTypeKeyAmtList.size() < 2)// single subledger { final String queryString = " select distinct(ih.payTo) from egf_InstrumentHeader ih, egf_InstrumentVoucher iv where iv.instrumentHeaderId=ih.id " + "and iv.voucherHeaderId=" + chqAssgn.getVoucherid() + " and ih.payTo=:payTo and ih.id_status in (" + statusId + ") "; if (LOGGER.isDebugEnabled()) LOGGER.debug("ALREADY ASSIGNED: queryString" + queryString); final List<Object> payTo = getSession().createSQLQuery(queryString) .setString("payTo", chqAssgn.getPaidTo()).list(); if (payTo == null || payTo.size() == 0) { if (LOGGER.isDebugEnabled()) LOGGER.debug(" ALREADY ASSIGNED: adding to chequeAssignlist as payTo s null or size 0" + chqAssgn); tempExpenseChequeAssignmentList.add(chqAssgn); } else { if (LOGGER.isDebugEnabled()) LOGGER.debug("ALREADY ASSIGNED: Not adding continuing"); continue; } } else { if (LOGGER.isDebugEnabled()) LOGGER.debug("ALREADY ASSIGNED: Entering detailTypeKeyAmtList.size()>2 code"); for (final Object[] detailTypeKeyAmtObj : detailTypeKeyAmtList) { String queryString = " select distinct(ih.payTo) from egf_InstrumentHeader ih, egf_InstrumentVoucher iv where " + "iv.instrumentHeaderId=ih.id and iv.voucherHeaderId=" + chqAssgn.getVoucherid() + " " + "and ih.detailTypeId=" + detailTypeKeyAmtObj[0] + " and ih.detailKeyId=" + detailTypeKeyAmtObj[1] + " " + "and ih.id_status in (" + statusId + ") "; if (LOGGER.isDebugEnabled()) LOGGER.debug("queryString" + queryString); List<Object> payTo = getSession().createSQLQuery(queryString).list(); if (payTo == null || payTo.size() == 0) { // this check will avoid already assigned by single subledger take subleger logic as it should be // single subledger take payto queryString = " select distinct(ih.payTo) from egf_InstrumentHeader ih, egf_InstrumentVoucher iv where iv.instrumentHeaderId=ih.id " + "and iv.voucherHeaderId=" + chqAssgn.getVoucherid() + " and ih.payTo=:payTo and ih.id_status in (" + statusId + ") "; if (LOGGER.isDebugEnabled()) LOGGER.debug("ALREADY ASSIGNED: queryString" + queryString); payTo = getSession().createSQLQuery(queryString) .setString("payTo", chqAssgn.getPaidTo()).list(); if (payTo != null) continue; final ChequeAssignment ca = new ChequeAssignment(); ca.setVoucherid(new BigDecimal(chqAssgn.getVoucherid())); ca.setVoucherNumber(chqAssgn.getVoucherNumber()); ca.setVoucherDate(chqAssgn.getVoucherDate()); ca.setPaidAmount(BigDecimal.valueOf(Double.valueOf(detailTypeKeyAmtObj[2].toString()))); ca.setChequeDate(chqAssgn.getChequeDate()); ca.setPaidTo(getEntity(Integer.parseInt(detailTypeKeyAmtObj[0].toString()), (Serializable) detailTypeKeyAmtObj[1]).getName()); ca.setDetailtypeid(Long.valueOf(detailTypeKeyAmtObj[0].toString())); ca.setDetailkeyid(Long.valueOf(detailTypeKeyAmtObj[1].toString())); if (LOGGER.isDebugEnabled()) LOGGER.debug(" ALREADY ASSIGNED: adding to chequeAssignlist" + ca); tempExpenseChequeAssignmentList.add(ca); } else { if (LOGGER.isDebugEnabled()) LOGGER.debug("ALREADY ASSIGNED: Not adding continuing"); continue; } } } } else { if (LOGGER.isDebugEnabled()) LOGGER.debug("ALREADY ASSIGNED: entering getDetailTypeKeyAmtForDebtitSideCC "); detailTypeKeyAmtList = billVHIdAndGLDListForDebtitSideCCMap .get(chqAssgn.getBillVHId().longValue()) != null ? billVHIdAndGLDListForDebtitSideCCMap.get(chqAssgn.getBillVHId().longValue()) : new ArrayList<Object[]>(); if (detailTypeKeyAmtList == null || detailTypeKeyAmtList.size() == 0) { final String queryString = " select distinct(ih.payTo) from egf_InstrumentHeader ih, egf_InstrumentVoucher iv where iv.instrumentHeaderId=ih.id and iv.voucherHeaderId=" + chqAssgn.getVoucherid() + " and ih.payTo =:payTo and ih.id_status in (" + statusId + ") "; if (LOGGER.isDebugEnabled()) LOGGER.debug("ALREADY ASSIGNED: queryString" + queryString); final List<Object> payTo = getSession().createSQLQuery(queryString) .setString("payTo", chqAssgn.getPaidTo()).list(); if (payTo == null || payTo.size() == 0) { if (LOGGER.isDebugEnabled()) LOGGER.debug("ALREADY ASSIGNED: adding to chequeAssignlist as payto is null or 0" + chqAssgn); tempExpenseChequeAssignmentList.add(chqAssgn); } } else { Map<String, BigDecimal> dedMap = new HashMap<String, BigDecimal>(); dedMap = getSubledgerAmtForDeduction(chqAssgn.getBillVHId()); String key = ""; for (final Object[] obj : detailTypeKeyAmtList) { String queryString = " select distinct(ih.payTo) from egf_InstrumentHeader ih, egf_InstrumentVoucher iv where iv.instrumentHeaderId=ih.id and iv.voucherHeaderId=" + chqAssgn.getVoucherid() + " and ih.detailTypeId=" + obj[0] + " and ih.detailKeyId=" + obj[1] + " and ih.id_status in (" + statusId + ") "; if (LOGGER.isDebugEnabled()) LOGGER.debug("ALREADY ASSIGNED: Querying for " + queryString); List<Object> payTo = getSession().createSQLQuery(queryString).list(); if (payTo == null || payTo.size() == 0) { // this check will avoid already assigned by single subledger take subleger logic as it should be // single subledger take payto queryString = " select distinct(ih.payTo) from egf_InstrumentHeader ih, egf_InstrumentVoucher iv where iv.instrumentHeaderId=ih.id " + "and iv.voucherHeaderId=" + chqAssgn.getVoucherid() + " and ih.payTo=:payTo and ih.id_status in (" + statusId + ") "; if (LOGGER.isDebugEnabled()) LOGGER.debug("ALREADY ASSIGNED: queryString" + queryString); payTo = getSession().createSQLQuery(queryString) .setString("payTo", chqAssgn.getPaidTo()).list(); if (payTo != null) continue; final ChequeAssignment c = new ChequeAssignment(); c.setChequeDate(chqAssgn.getChequeDate()); c.setVoucherHeaderId(chqAssgn.getVoucherid()); c.setVoucherNumber(chqAssgn.getVoucherNumber()); c.setVoucherDate(chqAssgn.getVoucherDate()); c.setDetailtypeid(Long.valueOf(obj[0].toString())); c.setDetailkeyid(Long.valueOf(obj[1].toString())); key = obj[0].toString() + DELIMETER + obj[1].toString(); c.setPaidAmount( dedMap.get(key) == null ? BigDecimal.valueOf(Double.valueOf(obj[2].toString())) : (BigDecimal.valueOf(Double.valueOf(obj[2].toString()))) .subtract(dedMap.get(key))); c.setPaidTo( getEntity(Integer.valueOf(obj[0].toString()), (Serializable) obj[1]).getName()); if (LOGGER.isDebugEnabled()) LOGGER.debug( "ALREADY ASSIGNED: adding to chequeAssignlist as from payTo==null || payTo.size()==0 \n" + c); tempExpenseChequeAssignmentList.add(c); } else continue; } } } } if (LOGGER.isDebugEnabled()) LOGGER.debug("Completed getExpenseBillPaymentsWithNoSurrenderedCheque."); }
From source file:org.egov.services.cheque.ChequeAssignmentService.java
License:Open Source License
@SuppressWarnings("unchecked") private void getExpenseBillPaymentsWithSurrenderedCheques() throws NumberFormatException, ApplicationException { if (LOGGER.isDebugEnabled()) LOGGER.debug("Starting getExpenseBillPaymentsWithSurrenderedCheques...ASSIGNED BUT SURRENDARD: "); List<ChequeAssignment> billChequeAssignmentList = null; final List<Long> billVHIds = new ArrayList<Long>(); final List<Long> billVHIdsForDebtitSideCC = new ArrayList<Long>(); final Map<Long, List<Object[]>> billVHIdAndgeneralLedgerDetailListMap = new HashMap<Long, List<Object[]>>(); final Map<Long, List<Object[]>> billVHIdAndGLDListForDebtitSideCCMap = new HashMap<Long, List<Object[]>>(); List<Object[]> generalLedgerDetailList = new ArrayList<Object[]>(); List<Object[]> generalLedgerDetailListForDebtitSideCC = new ArrayList<Object[]>(); final String strQuery = " select vh.id as voucherid ,vh.voucherNumber as voucherNumber ,vh.voucherDate as voucherDate ,0 as detailtypeid ,0 as detailkeyid ," + " misbill.paidto as paidTo,case when sum(misbill.paidamount)=null then 0 else sum(misbill.paidamount) end as paidAmount,current_date as chequeDate,misbill.billvhid as billVHId " + " from Paymentheader ph, voucherheader vh ,vouchermis vmis, Miscbilldetail misbill , generalledger gl,voucherheader billvh, eg_billregister br,eg_billregistermis billmis " + " where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vmis.voucherheaderid= vh.id and vh.status =" + approvedstatus + " " + filterConditions + " " + " and gl.voucherheaderid =vh.id and gl.creditamount>0 and misbill.billvhid=billvh.id and br.id=billmis.billid and billmis.voucherheaderid=billvh.id and br.expendituretype='" + FinancialConstants.STANDARD_EXPENDITURETYPE_CONTINGENT + "' " + " and exists(select 1 from egf_instrumentvoucher iv, egf_instrumentheader ih where ih.id= iv.instrumentheaderid and iv.voucherheaderid=vh.id and ih.id_status not in (" + statusId + ") ) " + " group by misbill.billvhid,vh.id,vh.voucherNumber,vh.voucherDate,misbill.paidto "; final Query query = getSession().createSQLQuery(strQuery).addScalar("voucherid", LongType.INSTANCE) .addScalar("voucherNumber").addScalar("voucherDate") .addScalar("paidAmount", BigDecimalType.INSTANCE).addScalar("chequeDate").addScalar("paidTo") .addScalar("billVHId", LongType.INSTANCE).addScalar("detailtypeid", LongType.INSTANCE) .addScalar("detailkeyid", LongType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug("ASSIGNED BUT SURRENDARD: With surrendered cheques - " + strQuery); billChequeAssignmentList = query.list(); for (final ChequeAssignment ca : billChequeAssignmentList) billVHIds.add(ca.getBillVHId().longValue()); if (billVHIds != null && billVHIds.size() > 0) generalLedgerDetailList = getDetailTypeKeyAmtForBillVHId(billVHIds); for (final Object[] gld : generalLedgerDetailList) if (billVHIdAndgeneralLedgerDetailListMap.containsKey(getLongValue(gld[3]))) billVHIdAndgeneralLedgerDetailListMap.get(getLongValue(gld[3])).add(gld); else {//from ww w . j a v a 2 s . co m final List<Object[]> generalLedgerDetails = new ArrayList<Object[]>(); generalLedgerDetails.add(gld); billVHIdAndgeneralLedgerDetailListMap.put(getLongValue(gld[3]), generalLedgerDetails); } for (final ChequeAssignment ca : billChequeAssignmentList) { final List<Object[]> detailTypeKeyAmtList = billVHIdAndgeneralLedgerDetailListMap .get(ca.getBillVHId().longValue()); if (detailTypeKeyAmtList == null || detailTypeKeyAmtList.size() == 0) billVHIdsForDebtitSideCC.add(ca.getBillVHId().longValue()); } if (billVHIdsForDebtitSideCC != null && billVHIdsForDebtitSideCC.size() > 0) generalLedgerDetailListForDebtitSideCC = getDetailTypeKeyAmtForDebtitSideCC(billVHIdsForDebtitSideCC); for (final Object[] gld : generalLedgerDetailListForDebtitSideCC) if (billVHIdAndGLDListForDebtitSideCCMap.containsKey(getLongValue(gld[3]))) billVHIdAndGLDListForDebtitSideCCMap.get(getLongValue(gld[3])).add(gld); else { final List<Object[]> generalLedgerDetails = new ArrayList<Object[]>(); generalLedgerDetails.add(gld); billVHIdAndGLDListForDebtitSideCCMap.put(getLongValue(gld[3]), generalLedgerDetails); } for (final ChequeAssignment chqAssgn : billChequeAssignmentList) { if (LOGGER.isDebugEnabled()) LOGGER.debug("ASSIGNED BUT SURRENDARD: With surrendered cheques - for Billvhid" + chqAssgn.getBillVHId()); List<Object[]> detailTypeKeyAmtList = billVHIdAndgeneralLedgerDetailListMap .get(chqAssgn.getBillVHId().longValue()) != null ? billVHIdAndgeneralLedgerDetailListMap.get(chqAssgn.getBillVHId().longValue()) : new ArrayList<Object[]>(); if (detailTypeKeyAmtList != null && detailTypeKeyAmtList.size() != 0) { if (LOGGER.isDebugEnabled()) LOGGER.debug("ASSIGNED BUT SURRENDARD: With surrendered cheques - for Billvhid " + chqAssgn.getBillVHId() + " and size " + detailTypeKeyAmtList); if (detailTypeKeyAmtList.size() < 2) { final String queryString = " select iv.id,ih.id_status from egf_instrumentheader ih, egf_instrumentvoucher iv where iv.instrumentheaderid=ih.id and iv.voucherheaderid=" + chqAssgn.getVoucherid() + " and ih.payTo=:payTo order by id desc "; if (LOGGER.isDebugEnabled()) LOGGER.debug("instrumentStatus- " + queryString); final List<Object[]> instrumentStatus = getSession().createSQLQuery(queryString) .setString("payTo", chqAssgn.getPaidTo()).list(); if (instrumentStatus == null || instrumentStatus.size() == 0 || !instrumentStatus.get(0)[1].toString().equalsIgnoreCase(instrumentNewStatus) && !instrumentStatus.get(0)[1].toString() .equalsIgnoreCase(instrumentReconciledStatus)) { if (LOGGER.isDebugEnabled()) LOGGER.debug( "ASSIGNED BUT SURRENDARD: Adding to chequeAssignmentlist as istrumentStatus " + chqAssgn); tempExpenseChequeAssignmentList.add(chqAssgn); } else { if (LOGGER.isDebugEnabled()) LOGGER.debug("ASSIGNED BUT SURRENDARD: Continuing not adding"); continue; } } else for (final Object[] detailTypeKeyAmtObj : detailTypeKeyAmtList) { final String queryString = " select iv.id,ih.id_status from egf_instrumentheader ih, egf_instrumentvoucher iv where iv.instrumentheaderid=ih.id and iv.voucherheaderid=" + chqAssgn.getVoucherid() + " and ih.detailtypeid=" + detailTypeKeyAmtObj[0] + " and ih.detailkeyid=" + detailTypeKeyAmtObj[1] + " order by id desc "; if (LOGGER.isDebugEnabled()) LOGGER.debug( "ASSIGNED BUT SURRENDARD: Inside detailTypeKeyAmtList loop- " + queryString); final List<Object[]> instrumentStatus = getSession().createSQLQuery(queryString).list(); if (instrumentStatus == null || instrumentStatus.size() == 0 || !instrumentStatus.get(0)[1].toString().equalsIgnoreCase(instrumentNewStatus) && !instrumentStatus.get(0)[1].toString() .equalsIgnoreCase(instrumentReconciledStatus)) { final ChequeAssignment ca = new ChequeAssignment(); ca.setVoucherid(new BigDecimal(chqAssgn.getVoucherid())); ca.setVoucherNumber(chqAssgn.getVoucherNumber()); ca.setVoucherDate(chqAssgn.getVoucherDate()); ca.setPaidAmount(BigDecimal.valueOf(Double.valueOf(detailTypeKeyAmtObj[2].toString()))); ca.setChequeDate(chqAssgn.getChequeDate()); ca.setPaidTo(getEntity(Integer.parseInt(detailTypeKeyAmtObj[0].toString()), (Serializable) detailTypeKeyAmtObj[1]).getName()); ca.setDetailtypeid(Long.valueOf(detailTypeKeyAmtObj[0].toString())); ca.setDetailkeyid(Long.valueOf(detailTypeKeyAmtObj[1].toString())); if (LOGGER.isDebugEnabled()) LOGGER.debug("ASSIGNED BUT SURRENDARD: inside loop adding " + ca); tempExpenseChequeAssignmentList.add(ca); } else continue; } } // End of checking bills with SL where credit amount>0 else { if (LOGGER.isDebugEnabled()) LOGGER.debug("ASSIGNED BUT SURRENDARD: checking getDetailTypeKeyAmtForDebtitSideCC for " + chqAssgn.getBillVHId()); detailTypeKeyAmtList = billVHIdAndGLDListForDebtitSideCCMap .get(chqAssgn.getBillVHId().longValue()) != null ? billVHIdAndGLDListForDebtitSideCCMap.get(chqAssgn.getBillVHId().longValue()) : new ArrayList<Object[]>(); if (detailTypeKeyAmtList == null || detailTypeKeyAmtList.size() == 0) { final String queryString = " select iv.id,ih.id_status from egf_instrumentheader ih, egf_instrumentvoucher iv where iv.instrumentheaderid=ih.id and iv.voucherheaderid=" + chqAssgn.getVoucherid() + " and ih.payTo=:payTo order by id desc "; if (LOGGER.isDebugEnabled()) LOGGER.debug("ASSIGNED BUT SURRENDARD: getDetailTypeKeyAmtForDebtitSideCC " + queryString); final List<Object[]> instrumentStatus = getSession().createSQLQuery(queryString) .setString("payTo", chqAssgn.getPaidTo()).list(); if (instrumentStatus == null || instrumentStatus.size() == 0 || !instrumentStatus.get(0)[1].toString().equalsIgnoreCase(instrumentNewStatus) && !instrumentStatus.get(0)[1].toString() .equalsIgnoreCase(instrumentReconciledStatus)) { if (LOGGER.isDebugEnabled()) LOGGER.debug( "ASSIGNED BUT SURRENDARD: Adding to chequeAssignmentlist in getDetailTypeKeyAmtForDebtitSideCC " + chqAssgn); tempExpenseChequeAssignmentList.add(chqAssgn); } } else if (detailTypeKeyAmtList != null && detailTypeKeyAmtList.size() == 1) { final String queryString = " select iv.id,ih.id_status from egf_instrumentheader ih, egf_instrumentvoucher iv where iv.instrumentheaderid=ih.id and iv.voucherheaderid=" + chqAssgn.getVoucherid() + " and ih.payTo=:payTo order by id desc "; if (LOGGER.isDebugEnabled()) LOGGER.debug("ASSIGNED BUT SURRENDARD: detailTypeKeyAmtList size=1" + queryString); final List<Object[]> instrumentStatus = getSession().createSQLQuery(queryString) .setString("payTo", chqAssgn.getPaidTo()).list(); if (instrumentStatus == null || instrumentStatus.size() == 0 || !instrumentStatus.get(0)[1].toString().equalsIgnoreCase(instrumentNewStatus) && !instrumentStatus.get(0)[1].toString() .equalsIgnoreCase(instrumentReconciledStatus)) { final String queryString2 = " select iv.id,ih.id_status from egf_instrumentheader ih, " + " egf_instrumentvoucher iv where iv.instrumentheaderid=ih.id and iv.voucherheaderid=" + chqAssgn.getVoucherid() + " " + " and ih.payTo=:payTo order by id desc "; if (LOGGER.isDebugEnabled()) LOGGER.debug("ASSIGNED BUT SURRENDARD: detailTypeKeyAmtList again checking " + queryString2); final List<Object[]> instrumentStatusWithsubledgerPaidto = getSession() .createSQLQuery(queryString2) .setString("payTo", getEntity(Integer.parseInt(detailTypeKeyAmtList.get(0)[0].toString()), (Serializable) detailTypeKeyAmtList.get(0)[1]).getName()) .list(); if (instrumentStatusWithsubledgerPaidto == null || instrumentStatusWithsubledgerPaidto.size() == 0 || !instrumentStatusWithsubledgerPaidto.get(0)[1].toString() .equalsIgnoreCase(instrumentNewStatus) && !instrumentStatusWithsubledgerPaidto.get(0)[1].toString() .equalsIgnoreCase(instrumentReconciledStatus)) { if (LOGGER.isDebugEnabled()) LOGGER.debug("ASSIGNED BUT SURRENDARD: adding inside again checking" + chqAssgn); tempExpenseChequeAssignmentList.add(chqAssgn); } } } else// if more than 1 SL entries with debit side CC { Map<String, BigDecimal> dedMap = new HashMap<String, BigDecimal>(); dedMap = getSubledgerAmtForDeduction(chqAssgn.getBillVHId()); String key = ""; for (final Object[] obj : detailTypeKeyAmtList) { final String queryString = " select iv.id,ih.id_status from egf_instrumentheader ih, egf_instrumentvoucher iv where " + "iv.instrumentheaderid=ih.id and iv.voucherheaderid=" + chqAssgn.getVoucherid() + "" + " and ih.detailtypeid=" + obj[0] + " and ih.detailkeyid=" + obj[1] + " order by id desc "; if (LOGGER.isDebugEnabled()) LOGGER.debug("ASSIGNED BUT SURRENDARD: detailTypeKeyAmtList checking " + queryString); final List<Object[]> instrumentStatus = getSession().createSQLQuery(queryString).list(); if (instrumentStatus == null || instrumentStatus.size() == 0 || !instrumentStatus.get(0)[1].toString().equalsIgnoreCase(instrumentNewStatus) && !instrumentStatus.get(0)[1].toString() .equalsIgnoreCase(instrumentReconciledStatus)) { final ChequeAssignment c = new ChequeAssignment(); c.setChequeDate(chqAssgn.getChequeDate()); c.setVoucherHeaderId(chqAssgn.getVoucherid()); c.setVoucherNumber(chqAssgn.getVoucherNumber()); c.setVoucherDate(chqAssgn.getVoucherDate()); c.setDetailtypeid(Long.valueOf(obj[0].toString())); c.setDetailkeyid(Long.valueOf(obj[1].toString())); key = obj[0].toString() + DELIMETER + obj[1].toString(); c.setPaidAmount( dedMap.get(key) == null ? BigDecimal.valueOf(Double.valueOf(obj[2].toString())) : (BigDecimal.valueOf(Double.valueOf(obj[2].toString()))) .subtract(dedMap.get(key))); c.setPaidTo( getEntity(Integer.valueOf(obj[0].toString()), (Serializable) obj[1]).getName()); if (LOGGER.isDebugEnabled()) LOGGER.debug( "ASSIGNED BUT SURRENDARD: adding inside detailTypeKeyAmtList loop" + c); tempExpenseChequeAssignmentList.add(c); } else continue; } } } // End of main Else } if (LOGGER.isDebugEnabled()) LOGGER.debug("Completed getExpenseBillPaymentsWithSurrenderedCheques."); }
From source file:org.egov.services.payment.PaymentService.java
License:Open Source License
public List<ChequeAssignment> getPaymentVoucherForRTGSInstrument(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(); /*/*w w w . j a v a 2s .c o m*/ * EgBillSubType egSubType = new EgBillSubType(); egSubType = (EgBillSubType) persistenceService.find( * " from EgBillSubType where name = ?", FinancialConstants.BILLSUBTYPE_TNEBBILL); if (egSubType.getId() != null) * sql.append(" and (bmis.billsubtype is null or bmis.billsubtype not in (" + egSubType.getId() + "))"); */if (!"".equals(parameters.get("fromDate")[0])) sql.append( " 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])); 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," + " misbill.paidto as paidTo,sum(misbill.paidamount) as paidAmount,current_date as chequeDate" + " , ba.accountnumber AS bankAccNumber, ba.id AS bankAccountId ," + " bill.id as billId, bill.billnumber as billNumber ,bill.expenditureType as expenditureType" + " 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, Miscbilldetail misbill ,eg_billregistermis bmis, eg_billregister bill " + " where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vmis.voucherheaderid= vh.id " + " and vh.status =" + approvedstatus + " " + sql + " " + " and bmis.voucherheaderid=misbill.billvhid and bmis.billid=bill.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,misbill.paidto, " + " ba.accountnumber, ba.id , bill.id, bill.billnumber,bill.expenditureType " + " order by ba.id,dept.name,vh.voucherNumber ").addScalar("voucherid", LongType.INSTANCE) .addScalar("voucherNumber").addScalar("departmentName").addScalar("voucherDate") .addScalar("paidTo").addScalar("paidAmount", BigDecimalType.INSTANCE).addScalar("chequeDate") .addScalar("bankAccNumber").addScalar("bankAccountId", LongType.INSTANCE) .addScalar("billId", LongType.INSTANCE).addScalar("billNumber").addScalar("expenditureType") .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, misbill.paidto as paidTo" + ",sum(misbill.paidamount) as paidAmount,current_date as chequeDate , ba.accountnumber AS bankAccNumber " + " , ba.id AS bankAccountId , " + " bill.id as billId, bill.billnumber as billNumber ,bill.expenditureType as expenditureType" + " from Paymentheader ph,eg_department dept, bankaccount ba,eg_billregistermis bmis, " + " eg_billregister bill ,voucherheader vh LEFT " + " JOIN EGF_INSTRUMENTVOUCHER IV ON VH.ID=IV.VOUCHERHEADERID LEFT JOIN EGF_INSTRUMENTHEADER IH " + " ON IV.INSTRUMENTHEADERID=IH.ID,vouchermis vmis, Miscbilldetail misbill " + ",(select max(iv1.instrumentheaderid) as maxihid,iv1.voucherheaderid as iv1vhid from egf_instrumentvoucher iv1 group by iv1.voucherheaderid) as table1" + " where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vmis.voucherheaderid= vh.id " + " and vh.status =" + approvedstatus + " " + sql + " " + " and bmis.voucherheaderid=misbill.billvhid and bmis.billid=bill.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,misbill.paidto,ba.accountnumber," + " ba.id , bill.id, bill.billnumber ,bill.expenditureType order by ba.id,dept.name,vh.voucherNumber ") .addScalar("voucherid", LongType.INSTANCE).addScalar("voucherNumber") .addScalar("departmentName").addScalar("voucherDate").addScalar("paidTo") .addScalar("paidAmount", BigDecimalType.INSTANCE).addScalar("chequeDate") .addScalar("bankAccNumber").addScalar("bankAccountId", LongType.INSTANCE) .addScalar("billId", LongType.INSTANCE).addScalar("billNumber").addScalar("expenditureType") .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.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 w w .j a va 2 s .c om " 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.payment.PaymentService.java
License:Open Source License
public List<ChequeAssignment> getDirectBankPaymentVoucherForRTGSInstrument( final Map<String, String[]> parameters, final CVoucherHeader voucherHeader) throws ApplicationException, ParseException { if (LOGGER.isDebugEnabled()) LOGGER.debug("Starting getPaymentVoucherNotInInstrument..."); final List<ChequeAssignment> chequeAssignmentList = new ArrayList<ChequeAssignment>(); final StringBuffer sql = new StringBuffer(); if (!"".equals(parameters.get("fromDate")[0])) sql.append(/* www.j a v a 2s. c o 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])); 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 , misbill.paidto AS paidTo, SUM(misbill.paidamount) AS paidAmount,current_date AS chequeDate," + " ba.accountnumber AS bankAccNumber, ba.id AS bankAccountId ,vh.name AS expenditureType FROM Paymentheader ph," + " voucherheader vh, vouchermis vmis, Miscbilldetail misbill, eg_department dept, bankaccount ba, egf_instrumentvoucher iv RIGHT OUTER JOIN voucherheader pvh" + " ON (pvh.id =iv.VOUCHERHEADERID) WHERE ph.voucherheaderid =misbill.payvhid AND ph.voucherheaderid =vh.id AND vh.name ='" + FinancialConstants.PAYMENTVOUCHER_NAME_DIRECTBANK + "'" + " AND vmis.voucherheaderid = vh.id AND vh.status =" + approvedstatus + sql + " AND pvh.id =vh.id AND iv.id IS NULL" + " AND dept.id = vmis.departmentid AND ph.bankaccountnumberid= ba.id GROUP BY vh.id, vh.voucherNumber, dept.name , " + " vh.voucherDate, misbill.paidto,ba.accountnumber, ba.id,vh.name" + " UNION SELECT vh.id AS voucherid ,vh.voucherNumber AS voucherNumber ,dept.name AS departmentName,vh.voucherDate AS voucherDate ," + " misbill.paidto AS paidTo, SUM(misbill.paidamount) AS paidAmount, current_date AS chequeDate,ba.accountnumber AS bankAccNumber," + " ba.id AS bankAccountId, vh.name AS expenditureType FROM Paymentheader ph,voucherheader vh,vouchermis vmis,eg_department dept,bankaccount ba," + " Miscbilldetail misbill, egf_instrumentvoucher iv RIGHT OUTER JOIN voucherheader pvh ON (pvh.id=iv.VOUCHERHEADERID) LEFT OUTER JOIN egf_instrumentheader ih" + " ON (ih.ID =iv.INSTRUMENTHEADERID) WHERE ph.voucherheaderid =misbill.payvhid AND ph.voucherheaderid =vh.id AND vh.name ='" + FinancialConstants.PAYMENTVOUCHER_NAME_DIRECTBANK + "'" + " AND vmis.voucherheaderid = vh.id AND vh.status =" + approvedstatus + sql + " AND pvh.id =vh.id AND dept.id = vmis.departmentid" + " AND ph.bankaccountnumberid= ba.id AND ih.id IN (SELECT MAX(ih.id) FROM egf_instrumentvoucher iv RIGHT OUTER JOIN voucherheader pvh" + " ON (pvh.id=iv.VOUCHERHEADERID) LEFT OUTER JOIN egf_instrumentheader ih ON (ih.ID =iv.INSTRUMENTHEADERID) WHERE pvh.id =vh.id " + " " + " ) and ih.id_status not in (" + statusId + ") GROUP BY vh.id, vh.voucherNumber, dept.name , vh.voucherDate, misbill.paidto, ba.accountnumber, ba.id, vh.name " + " order by bankAccountId, departmentName, voucherNumber ") .addScalar("voucherid", LongType.INSTANCE).addScalar("voucherNumber") .addScalar("departmentName").addScalar("voucherDate").addScalar("paidTo") .addScalar("paidAmount", BigDecimalType.INSTANCE).addScalar("chequeDate") .addScalar("bankAccNumber").addScalar("bankAccountId", LongType.INSTANCE) .addScalar("expenditureType") .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); chequeAssignmentList.addAll(query.list()); } if (LOGGER.isDebugEnabled()) LOGGER.debug("Completed getPaymentVoucherNotInInstrument."); return chequeAssignmentList; }