Java tutorial
/* * eGov SmartCity eGovernance suite aims to improve the internal efficiency,transparency, * accountability and the service delivery of the government organizations. * * Copyright (C) 2017 eGovernments Foundation * * The updated version of eGov suite of products as by eGovernments Foundation * is available at http://www.egovernments.org * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see http://www.gnu.org/licenses/ or * http://www.gnu.org/licenses/gpl.html . * * In addition to the terms of the GPL license to be adhered to in using this * program, the following additional terms are to be complied with: * * 1) All versions of this program, verbatim or modified must carry this * Legal Notice. * Further, all user interfaces, including but not limited to citizen facing interfaces, * Urban Local Bodies interfaces, dashboards, mobile applications, of the program and any * derived works should carry eGovernments Foundation logo on the top right corner. * * For the logo, please refer http://egovernments.org/html/logo/egov_logo.png. * For any further queries on attribution, including queries on brand guidelines, * please contact contact@egovernments.org * * 2) Any misrepresentation of the origin of the material is prohibited. It * is required that all modified versions of this material be marked in * reasonable ways as different from the original version. * * 3) This license does not grant any rights to any user of the program * with regards to rights under trademark law for use of the trade names * or trademarks of eGovernments Foundation. * * In case of any queries, you can reach eGovernments Foundation at contact@egovernments.org. * */ package org.egov.egf.web.actions.brs; import org.apache.log4j.Logger; import org.egov.commons.EgwStatus; import org.egov.commons.dao.EgwStatusHibernateDAO; import org.egov.egf.model.ReconcileBean; import org.egov.infra.admin.master.entity.AppConfigValues; import org.egov.infra.admin.master.service.AppConfigValueService; import org.egov.infra.exception.ApplicationRuntimeException; import org.egov.infstr.services.PersistenceService; import org.egov.model.instrument.InstrumentHeader; import org.egov.services.instrument.InstrumentHeaderService; import org.egov.services.instrument.InstrumentOtherDetailsService; import org.egov.utils.FinancialConstants; import org.hibernate.SQLQuery; import org.hibernate.transform.Transformers; import org.hibernate.type.BigDecimalType; import org.hibernate.type.LongType; import org.hibernate.type.StringType; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.util.ArrayList; import java.util.Date; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; @Service public class ManualReconcileHelper { private static final Logger LOGGER = Logger.getLogger(ManualReconcileHelper.class); @Autowired private AppConfigValueService appConfigValueService; @Autowired @Qualifier("persistenceService") private PersistenceService persistenceService; @Autowired @Qualifier("instrumentOtherDetailsService") private InstrumentOtherDetailsService instrumentOtherDetailsService; @Autowired private EgwStatusHibernateDAO egwStatusHibernateDAO; @Autowired @Qualifier("instrumentHeaderService") private InstrumentHeaderService instrumentHeaderService; 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; 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; } private String getExcludeStatuses() { List<AppConfigValues> configValuesByModuleAndKey = appConfigValueService .getConfigValuesByModuleAndKey("EGF", "statusexcludeReport"); final String statusExclude = configValuesByModuleAndKey.get(0).getValue(); return statusExclude; } 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() + "' )"; } 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; } @Transactional public void update(List<Date> reconDates, List<Long> instrumentHeaders) { int i = 0; EgwStatus reconciledStatus = egwStatusHibernateDAO.getStatusByModuleAndCode( FinancialConstants.STATUS_MODULE_INSTRUMENT, FinancialConstants.INSTRUMENT_RECONCILED_STATUS); for (Date reconcileOn : reconDates) { if (reconcileOn != null) { Long ihId = instrumentHeaders.get(i); InstrumentHeader ih = instrumentHeaderService.reconcile(reconcileOn, ihId, reconciledStatus); instrumentOtherDetailsService.reconcile(reconcileOn, ihId, ih.getInstrumentAmount()); } i++; } } }