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.services.deduction; import org.egov.billsaccounting.services.CreateVoucher; import org.egov.billsaccounting.services.VoucherConstant; import org.egov.commons.Bankaccount; import org.egov.commons.CFinancialYear; import org.egov.commons.CGeneralLedger; import org.egov.commons.CVoucherHeader; import org.egov.commons.dao.FinancialYearDAO; import org.egov.deduction.model.EgRemittance; import org.egov.deduction.model.EgRemittanceDetail; import org.egov.deduction.model.EgRemittanceGldtl; import org.egov.eis.entity.DrawingOfficer; import org.egov.eis.service.EisCommonService; import org.egov.infra.admin.master.entity.AppConfigValues; import org.egov.infra.admin.master.entity.User; import org.egov.infra.admin.master.service.AppConfigValueService; import org.egov.infra.config.core.ApplicationThreadLocals; import org.egov.infra.exception.ApplicationRuntimeException; import org.egov.infra.validation.exception.ValidationError; import org.egov.infra.validation.exception.ValidationException; import org.egov.infra.workflow.service.SimpleWorkflowService; import org.egov.infstr.services.PersistenceService; import org.egov.model.bills.Miscbilldetail; import org.egov.model.deduction.AutoRemittanceBean; import org.egov.model.payment.Paymentheader; import org.egov.model.recoveries.Recovery; import org.egov.model.recoveries.RemittanceSchedulePayment; import org.egov.model.recoveries.RemittanceSchedulerLog; import org.egov.pims.commons.Position; import org.egov.services.payment.PaymentService; import org.egov.services.recoveries.RecoveryService; import org.egov.utils.FinancialConstants; import org.hibernate.SQLQuery; import org.hibernate.transform.Transformers; import org.hibernate.type.DoubleType; import org.hibernate.type.IntegerType; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import java.math.BigDecimal; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import java.util.Set; /** * @author mani * */ public class ScheduledRemittanceService { /** * 1. Select the Remittances pending for payment for a given coa,fund,department 2. keep the count of vouhcers selected and * total payment amount 3. Create a payment voucher and send to workflow 4. Update the Remittance tables as 5. There will not * be partial payment but Payment voucher cancellation will be there */ private final static Logger LOGGER = LoggerFactory.getLogger(ScheduledRemittanceService.class); private final static int fundIndex = 0; private final static int bankAccountIdIndex = 1; private final static int detailTypeIndex = 2; private final static int detailKeyIndex = 3; // id,code into fundMap . private Map<Integer, String> fundMap; private Map<Integer, String> deptMap; private Map<Integer, Integer> deptDOMap; // fundcode-bankaccountid private Map<String, Integer> receiptBankAccountMap; @Autowired @Qualifier("persistenceService") private PersistenceService persistenceService; @Autowired CreateVoucher createVoucher; private FinancialYearDAO financialYearDAO; @Autowired @Qualifier("recoveryPersistenceService") private RecoveryService recoveryService; private PersistenceService<EgRemittanceGldtl, Integer> egRemittancegldtlService; private RemittancePersistenceService remittancePersistenceService; private PaymentService paymentService; private PersistenceService<RemittanceSchedulerLog, Integer> remittanceSchedulerLogService; private SimpleWorkflowService<Paymentheader> paymentWorkflowService; private final SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy"); private List<String> receiptFundCodes; private EisCommonService eisCommonService; private HashMap<String, Integer> GJVBankAccountMap; private ArrayList<String> GJVFundCodes; private Date startDate; private final Date today = new Date(); private String glcode; private String jobName; private Date lastRunDate; private StringBuffer errorMessage = new StringBuffer(1024); private Recovery recovery; private Map<String, List<AutoRemittanceBean>> voucherGroupMap = new HashMap<String, List<AutoRemittanceBean>>(); // Map of remittance amount grouped for a voucher combination and subledger Map<String, Double> detailKeyGroupeMap = new HashMap<String, Double>(); private List<AutoRemittanceBean> recoveries; String remitted; private boolean isControlCode = true; private Long schedularLogId; private boolean successForAutoRemittance = true; private User user; private Position nextOwner; @Autowired private AppConfigValueService appConfigValueService; /** * Our jboss Trnasaction manager does not support nested transactions https://community.jboss.org/thread/206684 so all have to * be managed by flush,clear * * this api called the schedulars by coa and iniitates * * any issues if trigger is not getting fired check QRTZ_TRIGGERS table. if the job type is manual on any exception it is * thrown back to * @return */ public boolean searchRecovery(final String recoveryGlcode, final String recoveryjobName, final Long recoverySchedularLogId, final Integer deptId, final Date recoverylastRunDate) { glcode = recoveryGlcode; jobName = recoveryjobName; lastRunDate = recoverylastRunDate; schedularLogId = recoverySchedularLogId; if (LOGGER.isDebugEnabled()) LOGGER.debug("Starting job :{} with glcode {} on {} " + jobName, glcode, new Date().toString()); try { // Common data like dept,fund,dept_do mapping populated to map loadCommonData(); validate(glcode); new Date(); recovery = recoveryService.find("from Recovery where chartofaccounts.glcode=" + glcode); if (recovery == null) { LOGGER.error("glcode is not mapped to tds :" + glcode + "\n"); throw new ApplicationRuntimeException("glcode is not mapped to tds : " + glcode); } remitted = recovery.getRemitted(); final List coads = remittancePersistenceService.findAllBy("from CChartOfAccountDetail where glcodeId=?", recovery.getChartofaccounts()); if (coads == null || coads.size() == 0) isControlCode = false; for (final Integer dept : deptMap.keySet()) { // if deptId is not null then it is from manual screen invocation for a department if (deptId != null) if (!dept.equals(deptId)) continue; if (LOGGER.isDebugEnabled()) { LOGGER.debug("**********************************************"); LOGGER.debug("Starting for Department :" + deptMap.get(dept)); } // if drawing officer not found or he does not have tan number payment will not be generated final DrawingOfficer drawingOfficer = validateDrawingOfficer(errorMessage, dept); if (drawingOfficer == null) continue; // initialize for each department so that i wont contain previous department data detailKeyGroupeMap = new HashMap<String, Double>(); voucherGroupMap = new HashMap<String, List<AutoRemittanceBean>>(); if (isControlCode == false) { recoveries = searchNonControlCodeRecoveryByCOA(dept); if (recoveries.isEmpty()) { errorMessage.append(" No Recoveries found for " + deptMap.get(dept) + "\n"); continue; } createRemittanceForNonControlCodeRecovery(dept, drawingOfficer); } else { recoveries = searchRecoveryByCOA(dept); if (recoveries.isEmpty()) { errorMessage.append(" No Recoveries found for " + deptMap.get(dept) + "\n"); continue; } // combination for which different voucher should be created like fund or fund-function when // function is mandatory String voucherCombination = null; // combination for which group the detailkey and find total amount String detailKeyCombination = null; for (final AutoRemittanceBean bean : recoveries) { // All receipt will have 0 as bankaccountid and s it is targetted to have only municipal fund if (bean.getBankAccountId() == 0) bean.setBankAccountId(receiptBankAccountMap.get(fundMap.get(bean.getFundId()))); else if (bean.getBankAccountId() == -1) bean.setBankAccountId(GJVBankAccountMap.get(fundMap.get(bean.getFundId()))); voucherCombination = bean.getFundId() + "-" + bean.getBankAccountId(); detailKeyCombination = voucherCombination + "-" + bean.getDetailtypeId() + "-" + bean.getDetailkeyId(); if (LOGGER.isDebugEnabled()) { LOGGER.debug(bean.toString()); LOGGER.debug("detailKeyCombination:" + detailKeyCombination); } if (voucherGroupMap.get(voucherCombination) == null) { voucherGroupMap.put(voucherCombination, new ArrayList<AutoRemittanceBean>()); voucherGroupMap.get(voucherCombination).add(bean); } else voucherGroupMap.get(voucherCombination).add(bean); if (detailKeyGroupeMap.get(detailKeyCombination) == null) detailKeyGroupeMap.put(detailKeyCombination, bean.getPendingAmount()); else detailKeyGroupeMap.put(detailKeyCombination, detailKeyGroupeMap.get(detailKeyCombination) + bean.getPendingAmount()); } /* * Commenting as of now pan validation //validate for pan number only if income tax String name = * recovery.getChartofaccounts().getName(); if(name.toUpperCase().contains(INCOME_TAX)) { * validateTaxMandatoryFields(); } */ for (final String voucher : voucherGroupMap.keySet()) try { if (LOGGER.isDebugEnabled()) LOGGER.debug(" Starting for VoucherCombination :" + voucher); // create pre approved voucher,add to paymentheader, Miscbilldetail send to workflow // Here there is no chance of voucherheader coming as null as create voucher throws validation // exception on any issues final Bankaccount ba = (Bankaccount) persistenceService .find(" from Bankaccount where id=" + Integer.parseInt(voucher.split("-")[bankAccountIdIndex]) + ""); if (ba == null) { LOGGER.error("Bank Glcode for fundId " + fundMap.get(Integer.parseInt(voucher.split("-")[fundIndex])) + " ,recoverId:" + recovery.getType() + " not found"); errorMessage.append("Bank Glcode for fundId " + fundMap.get(Integer.parseInt(voucher.split("-")[fundIndex])) + " ,recoverId:" + recovery.getType() + " not found \n"); continue; } final CVoucherHeader voucherHeader = createPayment(dept, voucher, drawingOfficer, ba); if (LOGGER.isDebugEnabled()) LOGGER.debug("VoucherCreated :" + voucherHeader.getVoucherNumber()); // create remittance,remittancedetail final List<Integer> glIds = createRemittance(voucher, voucherHeader); // update generalledger with remittance date. This field will help in limiting no of rows to search // for payment // This field needs to be set to null when remittance payment voucher is cancelled. // This field should not be set when partial payment is done. updateRemittancedateInLeddger(glIds); updateScheduleLogDetail(voucherHeader, schedularLogId); // updates payment voucher ids into log table if (LOGGER.isDebugEnabled()) LOGGER.debug(" Remittance Created SuccessFully for " + voucher); persistenceService.getSession().flush(); persistenceService.getSession().clear(); } catch (final ValidationException e) { errorMessage.append(dept + ":" + e.getErrors().toString() + "\n"); successForAutoRemittance = false; // updateScheduleLog(errorMessage.toString(),jobName,glcode,false); } catch (final Exception e) { errorMessage.append(dept + ":" + e.getMessage() + "\n"); successForAutoRemittance = false; // updateScheduleLog(errorMessage.toString(),jobName,glcode,false); } if (LOGGER.isDebugEnabled()) { LOGGER.debug("Done for Department :" + deptMap.get(dept)); LOGGER.debug("**********************************************"); } } } } catch (final ValidationException ve) { successForAutoRemittance = false; errorMessage.append(ve.getErrors().toString()); } catch (final ApplicationRuntimeException e) { successForAutoRemittance = false; errorMessage.append(e.getMessage() + "\n"); } catch (final Exception e) { successForAutoRemittance = false; errorMessage.append(e.getMessage() + "\n"); } finally { updateScheduleLog(errorMessage.toString(), jobName, glcode, true, schedularLogId); } return successForAutoRemittance; } private boolean createRemittanceForNonControlCodeRecovery(final Integer dept, final DrawingOfficer drawingOfficer) { // combination for which different voucher should be created like fund or fund-function when // function is mandatory String voucherCombination = null; // combination for which group the detailkey and find total amount for (final AutoRemittanceBean bean : recoveries) { // All receipt will have 0 as bankaccountid and s it is targetted to have only municipal fund if (bean.getBankAccountId() == 0) bean.setBankAccountId(receiptBankAccountMap.get(fundMap.get(bean.getFundId()))); else if (bean.getBankAccountId() == -1) bean.setBankAccountId(GJVBankAccountMap.get(fundMap.get(bean.getFundId()))); voucherCombination = bean.getFundId() + "-" + bean.getBankAccountId(); if (LOGGER.isDebugEnabled()) { LOGGER.debug(bean.toString()); LOGGER.debug("voucherCombination:" + voucherCombination); } if (voucherGroupMap.get(voucherCombination) == null) { voucherGroupMap.put(voucherCombination, new ArrayList<AutoRemittanceBean>()); voucherGroupMap.get(voucherCombination).add(bean); } else voucherGroupMap.get(voucherCombination).add(bean); } for (final String voucher : voucherGroupMap.keySet()) try { if (LOGGER.isDebugEnabled()) LOGGER.debug(" Starting for VoucherCombination :" + voucher); // create pre approved voucher,add to paymentheader, Miscbilldetail send to workflow // Here there is no chance of voucherheader coming as null as create voucher throws validation exception on any // issues final Bankaccount ba = (Bankaccount) persistenceService.find(" from Bankaccount where id=" + Integer.parseInt(voucher.split("-")[bankAccountIdIndex]) + ""); if (ba == null) { LOGGER.error( "Bank Glcode for fundId " + fundMap.get(Integer.parseInt(voucher.split("-")[fundIndex])) + " ,recoverId:" + recovery.getType() + " not found"); errorMessage.append( "Bank Glcode for fundId " + fundMap.get(Integer.parseInt(voucher.split("-")[fundIndex])) + " ,recoverId:" + recovery.getType() + " not found \n"); continue; } final CVoucherHeader voucherHeader = createPayment(dept, voucher, drawingOfficer, ba); if (LOGGER.isDebugEnabled()) LOGGER.debug("VoucherCreated :" + voucherHeader.getVoucherNumber()); // create remittance,remittancedetail final List<Integer> glIds = createRemittance(voucher, voucherHeader); // update generalledger with remittance date. This field will help in limiting no of rows to search for payment // This field needs to be set to null when remittance payment voucher is cancelled. // This field should not be set when partial payment is done. updateRemittancedateInLeddger(glIds); updateScheduleLogDetail(voucherHeader, schedularLogId); // updates payment voucher ids into log table if (LOGGER.isDebugEnabled()) LOGGER.debug(" Remittance Created SuccessFully for " + voucher); persistenceService.getSession().flush(); persistenceService.getSession().clear(); } catch (final ValidationException e) { errorMessage.append(dept + ":" + e.getErrors() + "\n"); // updateScheduleLog(errorMessage.toString(),jobName,glcode,false); } catch (final Exception e) { errorMessage.append(dept + ":" + e.getMessage() + "\n"); // updateScheduleLog(errorMessage.toString(),jobName,glcode,false); } if (LOGGER.isDebugEnabled()) { LOGGER.debug("Done for Department :" + deptMap.get(dept)); LOGGER.debug("**********************************************"); } return true; } /** * * @param dept * @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(); } private DrawingOfficer validateDrawingOfficer(final StringBuffer errorMessage, final Integer dept) { if (deptDOMap.get(dept) == null) { LOGGER.error("Drawing officer Mapping Not found for department code " + deptMap.get(dept)); errorMessage .append("Drawing officer Mapping Not found for department code " + deptMap.get(dept) + "\n"); return null; } else { final DrawingOfficer drawingOfficer1 = (DrawingOfficer) persistenceService .find("from DrawingOfficer where id=? ", deptDOMap.get(dept).intValue()); if (drawingOfficer1.getTan() == null) { LOGGER.error("Drawing officer Mapping Not found for department code " + deptMap.get(dept)); errorMessage.append( "Drawing officer Mapping Not found for department code " + deptMap.get(dept) + "\n"); return null; } else return drawingOfficer1; } } private void updateScheduleLogDetail(final CVoucherHeader voucherHeader, final Long schedularLogId) { final RemittanceSchedulePayment rsPaymentLog = new RemittanceSchedulePayment(); rsPaymentLog.setVoucherheaderId(voucherHeader); rsPaymentLog.setSchId((RemittanceSchedulerLog) persistenceService.getSession() .load(RemittanceSchedulerLog.class, schedularLogId)); persistenceService.getSession().save(rsPaymentLog); } /** * updates generalledger table's remittance date to date on which remittance created for recovery * @param glIds */ 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); } /** * Loads department <b>id,code</b> into deptMap . Loads fund <b>id,code</b> into fundMap . Loads department to Drawing officer * as deptartment's id,Drawing officer's id into deptDOMap Useful as this is not bound to hibernate session can be used across * sessions Remittance processing is done by departmnet ,fund. For search we need "id" where as to create voucher we need * "code" for both department and fund. Drawing officer id is saved into paymentheader table as these are responsible for * remittance of recovery done. * */ private void loadCommonData() { if (LOGGER.isDebugEnabled()) LOGGER.debug("Starting loadCommonData"); // every time we need new map if dept-do mapping not found it will remove dept from processing. if (deptMap == null) deptMap = getDepartments(); if (fundMap == null) fundMap = getFunds(); // do not check not null as it might get update run time any time deptDOMap = getDOsForDepartment(); loadReceiptBankAccounts(); loadGJVbankAccounts(); loadStartDate(); loadNextOwner(); if (LOGGER.isDebugEnabled()) LOGGER.debug("loadCommonData Completed"); } /** * */ private void loadNextOwner() { user = (User) persistenceService.find("from User where userName='ASSTBUDGET'"); nextOwner = eisCommonService.getPositionByUserId(user.getId()); } private void loadGJVbankAccounts() { try { GJVBankAccountMap = new HashMap<String, Integer>(); String value = ""; final List<AppConfigValues> appConfigList = appConfigValueService.getConfigValuesByModuleAndKey("EGF", "AuoRemittance_Account_Number_For_GJV"); if (appConfigList == null) throw new ValidationException(Arrays.asList( new ValidationError("AuoRemittance_Account_Number_For_GJV app config key not defined", "AuoRemittance_Account_Number_For_GJV app config key not defined"))); for (final AppConfigValues appConfigVal : appConfigList) { value = appConfigVal.getValue(); final List<Bankaccount> bankAcountsList = persistenceService .findAllBy("from Bankaccount ba where accountNumber=?", value.split("-")[1]); if (bankAcountsList.size() == 1) GJVBankAccountMap.put(value.split("-")[0], bankAcountsList.get(0).getId().intValue()); else throw new ValidationException(Arrays.asList(new ValidationError( "AuoRemittance_Account_Number_For_Receipts app config value does not return proper single account", "AuoRemittance_Account_Number_For_GJV app config value does not return proper single account"))); } GJVFundCodes = new ArrayList<String>(); for (final String s : GJVBankAccountMap.keySet()) GJVFundCodes.add(s); LOGGER.debug("Funds Mapped for GJVs:" + GJVFundCodes); } catch (final NullPointerException e) { throw new ValidationException(Arrays .asList(new ValidationError("AuoRemittance_Account_Number_For_GJV app config key not defined", "AuoRemittance_Account_Number_For_GJV app config key not defined"))); } } /** * will load receipt bank accounts and load the funds to be considered for receipt this is common for all . In case you need * to consider fund for each glcode you have to implement that . Understand this logic before doing any code change adding * appconfig for capital,etc fund ie other than Municipal fund As of now some wrong receipt vouchers exist in system belong to * capital fund where as that should be of Municipal fund */ private void loadReceiptBankAccounts() { try { // will get result as fundcode-bankaccountnumber // expect is row 1. 01-****700 // 2. 02-****701 receiptBankAccountMap = new HashMap<String, Integer>(); String value = ""; final List<AppConfigValues> appConfigList = appConfigValueService.getConfigValuesByModuleAndKey("EGF", "AuoRemittance_Account_Number_For_Receipts"); if (appConfigList == null) throw new ValidationException(Arrays.asList( new ValidationError("AuoRemittance_Account_Number_For_Receipts app config key not defined", "AuoRemittance_Account_Number_For_Receipts app config key not defined"))); for (final AppConfigValues appConfigVal : appConfigList) { value = appConfigVal.getValue(); final List<Bankaccount> bankAcountsList = persistenceService .findAllBy("from Bankaccount ba where accountNumber=?", value.split("-")[1]); if (bankAcountsList.size() == 1) receiptBankAccountMap.put(value.split("-")[0], bankAcountsList.get(0).getId().intValue()); else throw new ValidationException(Arrays.asList(new ValidationError( "AuoRemittance_Account_Number_For_Receipts app config value does not return proper single account", "AuoRemittance_Account_Number_For_Receipts app config value does not return proper single account"))); } receiptFundCodes = new ArrayList<String>(); for (final String s : receiptBankAccountMap.keySet()) receiptFundCodes.add(s); if (LOGGER.isDebugEnabled()) LOGGER.debug("Funds Mapped for Receipts:" + receiptFundCodes); } catch (final NullPointerException e) { throw new ValidationException(Arrays.asList( new ValidationError("AuoRemittance_Account_Number_For_Receipts app config key not defined", "AuoRemittance_Account_Number_For_Receipts app config key not defined"))); } } private Map<Integer, Integer> getDOsForDepartment() { @SuppressWarnings("unchecked") final List<Object[]> list = persistenceService.getSession() .createSQLQuery( "select department_id,drawingofficer_id from eg_dept_do_mapping order by department_id") .list(); final Map<Integer, Integer> deptMap = new LinkedHashMap<Integer, Integer>(); for (final Object[] dept : list) { final BigDecimal id = (BigDecimal) dept[0]; final BigDecimal officer = (BigDecimal) dept[1]; deptMap.put(id.intValue(), officer.intValue()); } if (list == null || list.isEmpty()) throw new ValidationException(Arrays.asList(new ValidationError("Department Drawing officer not found", "Department Drawing officer not found"))); return deptMap; } private Map<Integer, String> getDepartments() { @SuppressWarnings("unchecked") final List<Object[]> list = persistenceService.getSession() .createSQLQuery("select id_dept,dept_Code from eg_department order by dept_Code").list(); final Map<Integer, String> deptMap = new LinkedHashMap<Integer, String>(); for (final Object[] dept : list) { final BigDecimal id = (BigDecimal) dept[0]; deptMap.put(id.intValue(), (String) dept[1]); } return deptMap; } /** * * @param glcode All common validation should be put here as of now 2 validations 1. Drawing officer mapping to department, * drawing officer tan number added in the search itself 2. Bank account to coa-fund mapping is added in the search itself * * * */ private void validate(final String glcode) { } private void updateScheduleLog(final String message, final String jobName, final String glcode, final boolean success, final Long schedularLogId) { final RemittanceSchedulerLog record = (RemittanceSchedulerLog) persistenceService.getSession() .load(RemittanceSchedulerLog.class, schedularLogId); record.setGlcode(glcode); record.setLastRunDate(new Date()); if (success) record.setStatus(FinancialConstants.REMITTANCE_SCHEDULER_LOG_STATUS_SUCCESS); else record.setStatus(FinancialConstants.REMITTANCE_SCHEDULER_LOG_STATUS_FAILURE); if (jobName != null && jobName.equalsIgnoreCase("Manual")) record.setSchType(FinancialConstants.REMITTANCE_SCHEDULER_SCHEDULAR_TYPE_MANUAL); else record.setSchType(FinancialConstants.REMITTANCE_SCHEDULER_SCHEDULAR_TYPE_AUTO); record.setRemarks(message); record.setSchJobName(jobName); record.setCreatedDate(new Date()); record.setCreatedBy(ApplicationThreadLocals.getUserId().intValue()); remittanceSchedulerLogService.persist(record); } @SuppressWarnings("deprecation") private List<Integer> createRemittance(final String voucher, final CVoucherHeader voucherHeader) { final List<Integer> glIds = new ArrayList<Integer>(); final EgRemittance remit = new EgRemittance(); remit.setFund(voucherHeader.getFundId()); remit.setRecovery(recovery); final CFinancialYear financialYearByDate = financialYearDAO .getFinancialYearByDate(voucherHeader.getVoucherDate()); remit.setFinancialyear(financialYearByDate); remit.setCreateddate(today); remit.setCreatedby(BigDecimal.valueOf(ApplicationThreadLocals.getUserId())); remit.setLastmodifiedby(BigDecimal.valueOf(ApplicationThreadLocals.getUserId())); remit.setLastmodifieddate(today); remit.setMonth(BigDecimal.valueOf(today.getMonth())); remit.setVoucherheader(voucherHeader); remit.setAsOnDate(voucherHeader.getVoucherDate()); final Set<EgRemittanceDetail> egRemittanceDetail = new HashSet<EgRemittanceDetail>(); EgRemittanceDetail remitDetail = null; int count = 0; for (final AutoRemittanceBean bean : voucherGroupMap.get(voucher)) { remitDetail = new EgRemittanceDetail(); remitDetail.setEgRemittance(remit); remitDetail.setRemittedamt(BigDecimal.valueOf(bean.getPendingAmount())); remitDetail.setLastmodifieddate(today); // update to EgRemittanceGldtl only if the recovery is control code . if (isControlCode) { final EgRemittanceGldtl remittancegldtl = (EgRemittanceGldtl) egRemittancegldtlService.getSession() .load(EgRemittanceGldtl.class, Integer.valueOf(bean.getRemittanceGldtlId())); remittancegldtl.setRemittedamt(BigDecimal.valueOf(bean.getGldtlAmount())); remitDetail.setEgRemittanceGldtl(remittancegldtl); } else { remitDetail.setGeneralLedger((CGeneralLedger) remittancePersistenceService.getSession() .load(CGeneralLedger.class, Long.valueOf(bean.getGeneralledgerId()))); remitDetail.setRemittedamt(BigDecimal.valueOf(bean.getGldtlAmount())); } egRemittanceDetail.add(remitDetail); glIds.add(bean.getGeneralledgerId()); if (LOGGER.isDebugEnabled()) { count++; LOGGER.debug("No of remittance Items added. You can see if the transaction is getting slower here " + count); } } remit.setEgRemittanceDetail(egRemittanceDetail); remittancePersistenceService.persist(remit); return glIds; } private CVoucherHeader createPayment(final Integer dept, final String voucher, final DrawingOfficer drawingOfficer, final Bankaccount ba) { double totalAmount = 0d; HashMap<String, Object> subledgertDetailMap = null; HashMap<String, Object> detailMap = null; final List<HashMap<String, Object>> accountdetails = new ArrayList<HashMap<String, Object>>(); final List<HashMap<String, Object>> subledgerDetails = new ArrayList<HashMap<String, Object>>(); detailMap = new HashMap<String, Object>(); if (!isControlCode) { final List<AutoRemittanceBean> uniquelist = voucherGroupMap.get(voucher); for (final AutoRemittanceBean bean : uniquelist) totalAmount += bean.getGldtlAmount(); } else for (final String detail : detailKeyGroupeMap.keySet()) if (detail.startsWith(voucher)) { totalAmount += detailKeyGroupeMap.get(detail); final String[] split = detail.split("-"); subledgertDetailMap = new HashMap<String, Object>(); subledgertDetailMap.put(VoucherConstant.DETAILTYPEID, split[detailTypeIndex]); subledgertDetailMap.put(VoucherConstant.DETAILKEYID, split[detailKeyIndex]); subledgertDetailMap.put(VoucherConstant.GLCODE, glcode); subledgertDetailMap.put(VoucherConstant.DEBITAMOUNT, detailKeyGroupeMap.get(detail)); subledgerDetails.add(subledgertDetailMap); } final HashMap<String, Object> headerdetails = new HashMap<String, Object>(); headerdetails.put(VoucherConstant.VOUCHERNAME, FinancialConstants.PAYMENTVOUCHER_NAME_REMITTANCE); headerdetails.put(VoucherConstant.VOUCHERTYPE, FinancialConstants.STANDARD_VOUCHER_TYPE_PAYMENT); headerdetails.put(VoucherConstant.VOUCHERDATE, today); headerdetails.put(VoucherConstant.DEPARTMENTCODE, deptMap.get(dept)); headerdetails.put(VoucherConstant.FUNDCODE, fundMap.get(Integer.parseInt(voucher.split("-")[fundIndex]))); // headerdetails.put(VoucherConstant.FUNCTIONCODE, voucherHeader.getVouchermis().getFunction().getCode()); detailMap = new HashMap<String, Object>(); detailMap.put(VoucherConstant.CREDITAMOUNT, 0); detailMap.put(VoucherConstant.DEBITAMOUNT, totalAmount); detailMap.put(VoucherConstant.GLCODE, glcode); accountdetails.add(detailMap); detailMap = new HashMap<String, Object>(); detailMap.put(VoucherConstant.CREDITAMOUNT, totalAmount); detailMap.put(VoucherConstant.DEBITAMOUNT, 0); detailMap.put(VoucherConstant.GLCODE, ba.getChartofaccounts().getGlcode()); accountdetails.add(detailMap); final CVoucherHeader voucherHeader = createVoucher.createPreApprovedVoucher(headerdetails, accountdetails, subledgerDetails); final Paymentheader ph = new Paymentheader(); ph.setVoucherheader(voucherHeader); ph.setBankaccount(ba); ph.setPaymentAmount(BigDecimal.valueOf(totalAmount)); ph.setType(FinancialConstants.MODEOFPAYMENT_RTGS); ph.setDrawingOfficer(drawingOfficer); paymentService.persist(ph); final Miscbilldetail miscbillDetail = new Miscbilldetail(); miscbillDetail.setBillamount(BigDecimal.valueOf(totalAmount)); miscbillDetail.setPaidamount(BigDecimal.valueOf(totalAmount)); miscbillDetail.setPassedamount(BigDecimal.valueOf(totalAmount)); miscbillDetail.setPayVoucherHeader(voucherHeader); miscbillDetail.setPaidto(remitted); persistenceService.getSession().save(miscbillDetail); ph.transition().start().withOwner(nextOwner); paymentWorkflowService.transition("uac_ao_approve|" + user.getId(), ph, "created from schedular"); return voucherHeader; } /** * * @return This api needs to be updated to logic of fetching */ private Map<Integer, String> getFunds() { @SuppressWarnings("unchecked") final List<Object[]> list = persistenceService.getSession() .createSQLQuery("select id,code from Fund where isactive=true order by code").list(); final Map<Integer, String> fundMap = new HashMap<Integer, String>(); for (final Object[] fund : list) { final BigDecimal id = (BigDecimal) fund[0]; fundMap.put(id.intValue(), (String) fund[1]); } return fundMap; } /** * @author Manikanta * @param glcode Will select the sum of recovery for the specified period Vouchers selected of not yet paid or paid and * cancelled Will read misc getHeaderMandateFields from baseVoucherAction Will need the egRemittanceGldetailid to update so * you cannot group by coa,fund,department that you have to manually sum and update the total remittance payment amount. can * make use of remitrecovery service search() method by passing search fields in voucherheader and date in remittancebean but * there no need to select the vouchernumber as we require only remittance_gldtl_id from voucher * */ /** * @author Elzan * @param glcode Will select the sum of recovery for the specified period- period means the date from the last run scheduler * date to system date. From the remittance scheduler log get the latest rundate for the successful job. There can be case * where the scheduler ran and failed We should not be considering this date. * ** Next step is to identify the vouchers that should come for remittance payment. There are basically 4 categories here. For * all these categories there is a common rule- once remitted vouchers should not come for remittance again unless the * associated payment is cancelled. * **** 1.Select all the approved vouchers where the selected glcode comes in the credit side and moduleid=10. This is to identify * collection receipts. For all these vouchers check for the payinslip reference in the instrumentotherdetail table. Payinslip * should exist and should be approved. * **** 2.Select all the Journal Vouchers where moduleid is not 10 (not collection receipts), where the selected glcode comes in * the credit side. For all these vouchers check if the payment is made to the concerned parties (contractor, supplier, * employee, DO etc). Check if the instrument is allocated to these payments - cheque,cash or RTGS. Consider the vouchers if * all these are done. The cheques/RTGS assigned should be valid and not in "Surrendered" state. * *** 3.Select all approved adjustment entries where there is no payment needed (voucher type="Journal Voucher" and * name="JVGeneral") where the selected glcode comes in the credit side. * *** 4.Direct bank payments where the deductions are made. Here select all such approved payments where the selected glcode * comes in the credit side. Check if there is a valid instrument allocated to these payments - cheque,cash or RTGS this will * be manual so not picking up * * ** How to figure out how many payment voucher needs to be created? Read the configuration parameters from the app_config * "DEFAULTTXNMISATTRRIBUTES". Aggregate the vouchers based on the configuration parameters Do not consider Drawing officer * for aggregation since the department to DO mapping is one to one. * ** How to calculate the remittance payment amount? There are 2 categories here - not remitted at all, partially remitted * (there in the system now) 1. For all the selected vouchers get the gldtlamt from EG_REMITTANCE_GLDTL WHERE remittedamt IS * NOT NULL 2. For all the selected vouchers get the gldtlamt from EG_REMITTANCE_GLDTL WHERE gldtlamt!=remittedamt 3. Select * only those generalledger entries which has remittance date as null 4. One issue is the manual generation of remittance * allows partial so generalledger entry for remittance payment is not updated. So the search might get longer. All the * parameters for remittance payment needs to be identified and set for the following- 1. Payto - will be from the recovery * master 2. DO (new field) - based on the department of the payment, get this value from the DEPARTMENT-DO mapping master. 3. * Subledger information will be same as that of the original voucher 4. The bank from which the payment needs to be made will * be configured in the set up master (either in recovery master or separately) 5. Approver to which the payment needs to be * send to 6. function to be used in case the function is made non-mandatory in "DEFAULTTXNMISATTRRIBUTES". 7. Department for * the payment voucher will be the bill department (as per aggregation) 8. Fund for the payment voucher will be the bill fund * (as per aggregation) * @param lastRunDate * @return */ private List<AutoRemittanceBean> searchRecoveryByCOA(final Integer deptId) { recoveries = new ArrayList<AutoRemittanceBean>(); recoveries.addAll(getReceiptRecoveries(deptId, 0)); recoveries.addAll(getJVRecoveries(deptId)); recoveries.addAll(getGJVRecovries(deptId, -1)); return recoveries; } private List<AutoRemittanceBean> searchNonControlCodeRecoveryByCOA(final Integer dept) { recoveries = new ArrayList<AutoRemittanceBean>(); recoveries.addAll(getNonControleCodeReceiptRecoveries(dept, 0)); recoveries.addAll(getNonControleCodeJVRecoveries(dept)); recoveries.addAll(getNonControleCodeGJVRecovries(dept, -1)); return recoveries; } 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(); } private List<AutoRemittanceBean> getNonControleCodeJVRecoveries(final Integer dept) { final StringBuffer qry = new StringBuffer(2048); qry.append(" SELECT DISTINCT gl.id AS generalledgerId, vh.fundid AS fundId, gl.creditamount " + " AS gldtlAmount,ih.bankaccountid AS bankAccountId " + " FROM VOUCHERHEADER vh , VOUCHERMIS mis, GENERALLEDGER gl , VOUCHERHEADER payment, " + " EGF_INSTRUMENTHEADER ih, EGF_INSTRUMENTVOUCHER iv ,TDS recovery,miscbilldetail mb " + " 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 ih.id =iv.instrumentheaderid " + " AND iv.voucherheaderid =payment.id and payment.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 mb.billvhid=vh.id and mb.payvhid=payment.id " + " AND recovery.ID =" + recovery.getId() + " "); if (lastRunDate != null) qry.append(" and (ih.instrumentdate<= :lastrundate or ih.transactiondate<=:lastrundate )"); if (startDate != null) qry.append(" and (ih.instrumentdate >=:startdate or ih.transactiondate>=:startdate )"); 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())); query.setResultTransformer(Transformers.aliasToBean(AutoRemittanceBean.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug("getNonControleCodeJVRecoveries query " + qry); return query.list(); } private void loadStartDate() { final SimpleDateFormat stringToDate = new SimpleDateFormat("dd/MM/yyyy"); String value = null; try { final List<AppConfigValues> appConfigList = appConfigValueService.getConfigValuesByModuleAndKey("EGF", "AutoRemittance_Start_Date"); if (appConfigList == null) throw new ValidationException( Arrays.asList(new ValidationError("AutoRemittance_Start_Date app config key not defined", "AutoRemittance_Start_Date app config key not defined"))); for (final AppConfigValues appConfigVal : appConfigList) value = appConfigVal.getValue(); startDate = stringToDate.parse(value); } catch (final ParseException e) { throw new ValidationException(Arrays.asList(new ValidationError( "Error in parsing AutoRemittance_Start_Date app config value (should be in dd/mm/yyyy format)", "Error in parsing AutoRemittance_Start_Date app config value (should be in dd/mm/yyyy format)"))); } catch (final NullPointerException e) { throw new ValidationException( Arrays.asList(new ValidationError("AutoRemittance_Start_Date app config value not added", "AutoRemittance_Start_Date app config value not added"))); } } /** * * @param gjvBankAccountId * @param glcode will return all GJV recoveries which dont have payments attached to it fund condition is not added as we have * to search . Whatever mapped in app config should succed others should fail */ @SuppressWarnings("unchecked") private List<AutoRemittanceBean> getGJVRecovries(final Integer deptId, final Integer gjvBankAccountId) { if (LOGGER.isDebugEnabled()) LOGGER.debug("Fetching GJVRecovries"); final StringBuffer queryStr = new StringBuffer( "SELECT distinct gl.id as generalledgerId, vh.fundid AS fundId, egr.GLDTLAMT AS gldtlAmount, gld.DETAILTYPEID AS detailtypeId," + " gld.DETAILKEYID AS detailkeyId, egr.ID AS remittanceGldtlId, " + gjvBankAccountId + " as bankAccountId, " + " egr.GLDTLAMT- (SELECT case when SUM(egd.REMITTEDAMT) = NULL then 0 else SUM(egd.REMITTEDAMT) end " + " FROM EG_REMITTANCE_GLDTL egr1, eg_remittance_detail egd, eg_remittance eg, voucherheader vh WHERE vh.status! =4 " + " AND eg.PAYMENTVHID =vh.id AND egd.remittanceid=eg.id AND egr1.id =egd.remittancegldtlid " + " AND egr1.id =egr.id ) AS pendingAmount FROM VOUCHERHEADER vh , " + " VOUCHERMIS mis, GENERALLEDGER gl ," + " GENERALLEDGERDETAIL gld, EG_REMITTANCE_GLDTL egr, TDS recovery5_ WHERE recovery5_.GLCODEID =gl.GLCODEID AND" + " gld.ID =egr.GLDTLID AND gl.ID =gld.GENERALLEDGERID AND vh.ID =gl.VOUCHERHEADERID " + " and gl.remittanceDate is null " + " AND mis.VOUCHERHEADERID =vh.ID AND vh.STATUS=0 and vh.moduleid is null and vh.name= '" + FinancialConstants.JOURNALVOUCHER_NAME_GENERAL + "'" + " AND mis.departmentid = " + deptId + " AND vh.moduleid is null" + " AND egr.GLDTLAMT- (SELECT case when SUM(egd.REMITTEDAMT) = NULL then 0 else SUM(egd.REMITTEDAMT) end FROM EG_REMITTANCE_GLDTL egr1, " + " eg_remittance_detail egd, eg_remittance eg, voucherheader vh WHERE vh.status !=4 " + " AND eg.PAYMENTVHID =vh.id AND egd.remittanceid =eg.id AND egr1.id =egd.remittancegldtlid " + " AND egr1.id =egr.id ) >0 AND recovery5_.ID =" + recovery.getId()); if (lastRunDate != null) queryStr.append(" and vh.voucherdate<= '" + sdf.format(lastRunDate) + "' "); if (startDate != null) queryStr.append(" and vh.voucherdate>= '" + sdf.format(startDate) + "' "); final SQLQuery query = persistenceService.getSession().createSQLQuery(queryStr.toString()); query.addScalar("generalledgerId", IntegerType.INSTANCE).addScalar("fundId", IntegerType.INSTANCE) .addScalar("gldtlAmount", DoubleType.INSTANCE).addScalar("detailtypeId", IntegerType.INSTANCE) .addScalar("detailkeyId", IntegerType.INSTANCE).addScalar("remittanceGldtlId", IntegerType.INSTANCE) .addScalar("pendingAmount", DoubleType.INSTANCE).addScalar("bankAccountId", IntegerType.INSTANCE); /* * if(lastRunDate!=null) { query.setDate("lastrundate", new java.sql.Date(lastRunDate.getTime())); } if(lastRunDate!=null) * { query.setDate("startdate", new java.sql.Date(startDate.getTime())); } */ query.setResultTransformer(Transformers.aliasToBean(AutoRemittanceBean.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug("Query for GJVRecovries" + queryStr); return query.list(); } /** * * @param recoveryId Will return all voucher recoveries which are billpayment done and check also assigned * @param deptId * @return */ @SuppressWarnings("deprecation") private List<AutoRemittanceBean> getJVRecoveries(final Integer deptId) { if (LOGGER.isDebugEnabled()) LOGGER.debug("Fetching JVRecoveries"); final StringBuffer queryStr = new StringBuffer( "SELECT distinct gl.id as generalledgerId, vh.fundid AS fundId, egr.GLDTLAMT AS gldtlAmount, gld.DETAILTYPEID AS detailtypeId," + " gld.DETAILKEYID AS detailkeyId, egr.ID AS remittanceGldtlId,ih.bankaccountid as bankAccountId, " + " egr.GLDTLAMT- (SELECT case when SUM(egd.REMITTEDAMT) = NULL then 0 else SUM(egd.REMITTEDAMT) end " + " FROM EG_REMITTANCE_GLDTL egr1, eg_remittance_detail egd, eg_remittance eg, voucherheader vh WHERE vh.status! =4 " + " AND eg.PAYMENTVHID =vh.id AND egd.remittanceid=eg.id AND egr1.id =egd.remittancegldtlid " + " AND egr1.id =egr.id ) AS pendingAmount FROM VOUCHERHEADER vh left outer JOIN miscbilldetail mb on vh.id=mb.billvhid , " + " VOUCHERMIS mis, GENERALLEDGER gl, voucherheader ph, egf_instrumentheader ih, egf_instrumentvoucher iv ," + " GENERALLEDGERDETAIL gld, EG_REMITTANCE_GLDTL egr, TDS recovery5_ WHERE recovery5_.GLCODEID =gl.GLCODEID AND" + " gld.ID =egr.GLDTLID AND gl.ID =gld.GENERALLEDGERID AND vh.ID =gl.VOUCHERHEADERID " + " and gl.remittanceDate is null " + " AND mis.VOUCHERHEADERID =vh.ID AND vh.STATUS=0 AND mb.payvhid =ph.id AND ih.id =iv.instrumentheaderid " + " AND iv.voucherheaderid =ph.id and ph.status!=4 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 mis.departmentid = " + deptId + " AND egr.GLDTLAMT- (SELECT case when SUM(egd.REMITTEDAMT) = NULL then 0 else SUM(egd.REMITTEDAMT) end FROM EG_REMITTANCE_GLDTL egr1, " + " eg_remittance_detail egd, eg_remittance eg, voucherheader vh WHERE vh.status !=4 " + " AND eg.PAYMENTVHID =vh.id AND egd.remittanceid =eg.id AND egr1.id =egd.remittancegldtlid " + " AND egr1.id =egr.id ) >0 AND recovery5_.ID =" + recovery.getId()); if (lastRunDate != null) queryStr.append(" and (ih.instrumentdate<='" + sdf.format(lastRunDate) + "' or ih.transactiondate<='" + sdf.format(lastRunDate) + "') "); if (startDate != null) queryStr.append(" and (ih.instrumentdate>='" + sdf.format(startDate) + "' or ih.transactiondate>='" + sdf.format(startDate) + "' ) "); final SQLQuery query = persistenceService.getSession().createSQLQuery(queryStr.toString()); query.addScalar("generalledgerId", IntegerType.INSTANCE).addScalar("fundId", IntegerType.INSTANCE) .addScalar("gldtlAmount", DoubleType.INSTANCE).addScalar("detailtypeId", IntegerType.INSTANCE) .addScalar("detailkeyId", IntegerType.INSTANCE).addScalar("remittanceGldtlId", IntegerType.INSTANCE) .addScalar("pendingAmount", DoubleType.INSTANCE).addScalar("bankAccountId", IntegerType.INSTANCE); /* * if(lastRunDate!=null) { query.setDate("lastrundate", new java.sql.Date(lastRunDate.getTime())); } if(lastRunDate!=null) * { query.setDate("startdate", new java.sql.Date(startDate.getTime())); } */ query.setResultTransformer(Transformers.aliasToBean(AutoRemittanceBean.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug("JVRecoveries query " + queryStr); return query.list(); } /** * * @param recoveryId Will return all receipt recoveries which are Remitted and approved * @param deptId * @param lastRunDate * @param startDate * @param receiptBankAccountId * @return * */ private List getReceiptRecoveries(final Integer deptId, final Integer receiptBankAccountId) { if (LOGGER.isDebugEnabled()) LOGGER.debug("Fetching ReceiptRecoveries"); final StringBuffer queryStr = new StringBuffer( "SELECT distinct gl.id as generalledgerId, vh.fundid AS fundId, egr.GLDTLAMT AS gldtlAmount, gld.DETAILTYPEID AS detailtypeId," + " gld.DETAILKEYID AS detailkeyId, egr.ID AS remittanceGldtlId," + receiptBankAccountId + " as bankAccountId, " + " egr.GLDTLAMT- (SELECT case when SUM(egd.REMITTEDAMT) = NULL then 0 else SUM(egd.REMITTEDAMT) end " + " FROM EG_REMITTANCE_GLDTL egr1, eg_remittance_detail egd, eg_remittance eg, voucherheader vh WHERE vh.status! =4 " + " AND eg.PAYMENTVHID =vh.id AND egd.remittanceid=eg.id AND egr1.id =egd.remittancegldtlid " + " AND egr1.id =egr.id ) AS pendingAmount FROM VOUCHERHEADER vh , " + " VOUCHERMIS mis, GENERALLEDGER gl, voucherheader payinslip, fund f, egf_instrumentheader ih, egf_instrumentotherdetails io," + " GENERALLEDGERDETAIL gld, EG_REMITTANCE_GLDTL egr, egcl_collectionvoucher cv, egcl_collectioninstrument ci,TDS recovery5_ WHERE recovery5_.GLCODEID =gl.GLCODEID AND" + " gld.ID =egr.GLDTLID AND gl.ID =gld.GENERALLEDGERID AND vh.ID =gl.VOUCHERHEADERID " + " and gl.remittanceDate is null and f.id=vh.fundid " + " AND mis.VOUCHERHEADERID =vh.ID AND vh.STATUS=0 AND io.payinslipid =payinslip.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 mis.departmentid = " + deptId + " AND egr.GLDTLAMT- (SELECT case when SUM(egd.REMITTEDAMT) = NULL then 0 else SUM(egd.REMITTEDAMT) end FROM EG_REMITTANCE_GLDTL egr1, " + " eg_remittance_detail egd, eg_remittance eg, voucherheader vh WHERE vh.status !=4 " + " AND eg.PAYMENTVHID =vh.id AND egd.remittanceid =eg.id AND egr1.id =egd.remittancegldtlid " + " AND egr1.id =egr.id ) >0 AND recovery5_.ID =" + recovery.getId()); if (lastRunDate != null) queryStr.append(" and payinslip.voucherdate<='" + sdf.format(lastRunDate) + "' "); if (startDate != null) queryStr.append(" and payinslip.voucherdate>='" + sdf.format(startDate) + "'"); if (receiptFundCodes != null && !receiptFundCodes.isEmpty()) queryStr.append(" and f.code in (:fundCodes) "); final SQLQuery query = persistenceService.getSession().createSQLQuery(queryStr.toString()); query.addScalar("generalledgerId", IntegerType.INSTANCE).addScalar("fundId", IntegerType.INSTANCE) .addScalar("gldtlAmount", DoubleType.INSTANCE).addScalar("detailtypeId", IntegerType.INSTANCE) .addScalar("detailkeyId", IntegerType.INSTANCE).addScalar("remittanceGldtlId", IntegerType.INSTANCE) .addScalar("pendingAmount", 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 " + queryStr); return query.list(); } public void setFinancialYearDAO(final FinancialYearDAO financialYearDAO) { this.financialYearDAO = financialYearDAO; } public void setRemittancePersistenceService(final RemittancePersistenceService remittancePersistenceService) { this.remittancePersistenceService = remittancePersistenceService; } public void setEgRemittancegldtlService( final PersistenceService<EgRemittanceGldtl, Integer> egRemittancegldtlService) { this.egRemittancegldtlService = egRemittancegldtlService; } public RemittancePersistenceService getRemittancePersistenceService() { return remittancePersistenceService; } public void setPaymentService(final PaymentService paymentService) { this.paymentService = paymentService; } public void setPaymentWorkflowService(final SimpleWorkflowService<Paymentheader> paymentWorkflowService) { this.paymentWorkflowService = paymentWorkflowService; } public void setRemittanceSchedulerLogService( final PersistenceService<RemittanceSchedulerLog, Integer> remittanceSchedulerLogService) { this.remittanceSchedulerLogService = remittanceSchedulerLogService; } public PersistenceService<RemittanceSchedulerLog, Integer> getRemittanceSchedulerLogService() { return remittanceSchedulerLogService; } public Map<Integer, Integer> getDeptDOMap() { return deptDOMap; } public void setDeptDOMap(final Map<Integer, Integer> deptDOMap) { this.deptDOMap = deptDOMap; } public boolean isSuccessForAutoRemittance() { return successForAutoRemittance; } public void setSuccessForAutoRemittance(final boolean successForAutoRemittance) { this.successForAutoRemittance = successForAutoRemittance; } public StringBuffer getErrorMessage() { return errorMessage; } public void setErrorMessage(final StringBuffer errorMessage) { this.errorMessage = errorMessage; } }