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.services.payment.PaymentService.java
License:Open Source License
public List<ChequeAssignment> getPaymentVoucherNotInInstrument(final Map<String, String[]> parameters, final CVoucherHeader voucherHeader) throws ApplicationException, ParseException { if (LOGGER.isDebugEnabled()) LOGGER.debug("Starting getPaymentVoucherNotInInstrument..."); List<ChequeAssignment> chequeAssignmentList = new ArrayList<ChequeAssignment>(); if (parameters.get("paymentMode")[0].equals(FinancialConstants.MODEOFPAYMENT_CHEQUE)) { final String billType = parameters.get("billType")[0]; chequeAssignmentService.setStatusAndFilterValues(parameters, voucherHeader); if (voucherHeader.getName() != null) if (voucherHeader.getName().equalsIgnoreCase(FinancialConstants.PAYMENTVOUCHER_NAME_PENSION)) { final String[] voucherName = new String[1]; voucherName[0] = voucherHeader.getName(); parameters.put("voucherName", voucherName); }//from w w w . java2 s .co m // parameters.put("voucherHeader", ); if (billType == null || billType.equalsIgnoreCase("-1") || billType.equalsIgnoreCase("0")) chequeAssignmentList.addAll(chequeAssignmentService.getPaymentVoucherNotInInstrument(parameters)); else if (billType.equalsIgnoreCase(FinancialConstants.STANDARD_EXPENDITURETYPE_CONTINGENT)) chequeAssignmentList.addAll(chequeAssignmentService.getExpenseBillPayments()); else if (billType.equalsIgnoreCase(FinancialConstants.STANDARD_EXPENDITURETYPE_WORKS + "-" + FinancialConstants.STANDARD_EXPENDITURETYPE_PURCHASE)) chequeAssignmentList.addAll( chequeAssignmentService.getContractorSupplierPaymentsForChequeAssignment(parameters)); else if (billType.equalsIgnoreCase(FinancialConstants.PAYMENTVOUCHER_NAME_DIRECTBANK)) chequeAssignmentList.addAll(chequeAssignmentService.getDirectBankPaymentsForChequeAssignment()); } else { final StringBuffer sql = new StringBuffer(); 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()); sql.append(" and ph.bankaccountnumberid=" + parameters.get("bankaccount")[0]); sql.append(" and lower(ph.type)=lower('" + parameters.get("paymentMode")[0] + "')"); 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_SALARY)) { 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, misbill.paidto as paidTo from Paymentheader ph,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 " + " where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vmis.voucherheaderid= vh.id and vh.status =" + approvedstatus + " " + sql + " " + " and IV.VOUCHERHEADERID IS NULL and vh.type='" + FinancialConstants.STANDARD_VOUCHER_TYPE_PAYMENT + "' and vh.name ='" + FinancialConstants.PAYMENTVOUCHER_NAME_SALARY + "'" + " group by vh.id,vh.voucherNumber,vh.voucherDate,misbill.paidto order by vh.voucherNumber ") .addScalar("voucherid", LongType.INSTANCE).addScalar("voucherNumber") .addScalar("voucherDate").addScalar("paidAmount").addScalar("chequeDate") .addScalar("paidTo").setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug(" for salary " + 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 ,vh.voucherDate as voucherDate,sum(misbill.paidamount) as paidAmount,current_date as chequeDate, misbill.paidto as paidTo from Paymentheader ph,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 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 ='" + FinancialConstants.PAYMENTVOUCHER_NAME_SALARY + "'" + " group by vh.id,vh.voucherNumber,vh.voucherDate,misbill.paidto order by vh.voucherNumber ") .addScalar("voucherid", LongType.INSTANCE).addScalar("voucherNumber") .addScalar("voucherDate").addScalar("paidAmount", BigDecimalType.INSTANCE) .addScalar("chequeDate").addScalar("paidTo") .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug(" for salary " + query); chequeAssignmentList.addAll(query.list()); final List<ChequeAssignment> tempChequeAssignmentList = chequeAssignmentList; Float paidAmt; String paidTo, nextPaidTo; int i, j; // / if (LOGGER.isDebugEnabled()) LOGGER.debug(" interating " + tempChequeAssignmentList.size() + " times"); for (i = 0; i < tempChequeAssignmentList.size(); i++) for (j = i + 1; j < tempChequeAssignmentList.size(); j++) if (tempChequeAssignmentList.get(j).getVoucherid() .equals(tempChequeAssignmentList.get(i).getVoucherid()) && tempChequeAssignmentList.get(j).getVoucherNumber() .equals(tempChequeAssignmentList.get(i).getVoucherNumber())) { paidAmt = tempChequeAssignmentList.get(i).getPaidAmount().floatValue(); paidAmt += tempChequeAssignmentList.get(j).getPaidAmount().floatValue(); tempChequeAssignmentList.get(i).setPaidAmount(new BigDecimal(paidAmt)); paidTo = tempChequeAssignmentList.get(i).getPaidTo(); nextPaidTo = tempChequeAssignmentList.get(j).getPaidTo(); tempChequeAssignmentList.get(i).setPaidTo(paidTo + " , " + nextPaidTo); tempChequeAssignmentList.remove(j); j--; } if (tempChequeAssignmentList.size() != chequeAssignmentList.size()) chequeAssignmentList = tempChequeAssignmentList; if (LOGGER.isDebugEnabled()) LOGGER.debug(" interating " + tempChequeAssignmentList.size() + " Done"); } else if (voucherHeader.getName() != null && voucherHeader.getName().equalsIgnoreCase(FinancialConstants.PAYMENTVOUCHER_NAME_PENSION)) { 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, misbill.paidto as paidTo from Paymentheader ph,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 " + " where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vmis.voucherheaderid= vh.id and vh.status =" + approvedstatus + " " + sql + " " + " and IV.VOUCHERHEADERID IS NULL and vh.type='" + FinancialConstants.STANDARD_VOUCHER_TYPE_PAYMENT + "' and vh.name ='" + FinancialConstants.PAYMENTVOUCHER_NAME_PENSION + "'" + " group by vh.id,vh.voucherNumber,vh.voucherDate,misbill.paidto order by vh.voucherNumber ") .addScalar("voucherid", LongType.INSTANCE).addScalar("voucherNumber") .addScalar("voucherDate").addScalar("paidAmount", BigDecimalType.INSTANCE) .addScalar("chequeDate").addScalar("paidTo") .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug(" for salary " + 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 ,vh.voucherDate as voucherDate,sum(misbill.paidamount) as paidAmount,current_date as chequeDate, misbill.paidto as paidTo from Paymentheader ph,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 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 ='" + FinancialConstants.PAYMENTVOUCHER_NAME_PENSION + "'" + " group by vh.id,vh.voucherNumber,vh.voucherDate,misbill.paidto order by vh.voucherNumber ") .addScalar("voucherid", LongType.INSTANCE).addScalar("voucherNumber") .addScalar("voucherDate").addScalar("paidAmount", BigDecimalType.INSTANCE) .addScalar("chequeDate").addScalar("paidTo") .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug(" for salary " + query); chequeAssignmentList.addAll(query.list()); final List<ChequeAssignment> tempChequeAssignmentList = chequeAssignmentList; Float paidAmt; String paidTo, nextPaidTo; int i, j; // / if (LOGGER.isDebugEnabled()) LOGGER.debug(" interating " + tempChequeAssignmentList.size() + " times"); for (i = 0; i < tempChequeAssignmentList.size(); i++) for (j = i + 1; j < tempChequeAssignmentList.size(); j++) if (tempChequeAssignmentList.get(j).getVoucherid() .equals(tempChequeAssignmentList.get(i).getVoucherid()) && tempChequeAssignmentList.get(j).getVoucherNumber() .equals(tempChequeAssignmentList.get(i).getVoucherNumber())) { paidAmt = tempChequeAssignmentList.get(i).getPaidAmount().floatValue(); paidAmt += tempChequeAssignmentList.get(j).getPaidAmount().floatValue(); tempChequeAssignmentList.get(i).setPaidAmount(new BigDecimal(paidAmt)); paidTo = tempChequeAssignmentList.get(i).getPaidTo(); nextPaidTo = tempChequeAssignmentList.get(j).getPaidTo(); tempChequeAssignmentList.get(i).setPaidTo(paidTo + " , " + nextPaidTo); tempChequeAssignmentList.remove(j); j--; } if (tempChequeAssignmentList.size() != chequeAssignmentList.size()) chequeAssignmentList = tempChequeAssignmentList; if (LOGGER.isDebugEnabled()) LOGGER.debug(" interating " + tempChequeAssignmentList.size() + " Done"); } else if (voucherHeader.getName() == null || !voucherHeader.getName() .equalsIgnoreCase(FinancialConstants.PAYMENTVOUCHER_NAME_REMITTANCE)) {// / // do // not // include // salary // payments // and // remittances 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 LEFT JOIN EGF_INSTRUMENTVOUCHER IV ON VH.ID=IV.VOUCHERHEADERID LEFT JOIN EGF_INSTRUMENTHEADER IH ON IV.INSTRUMENTHEADERID=IH.ID,vouchermis vmis, Miscbilldetail misbill " + " where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vmis.voucherheaderid= vh.id and vh.status =" + approvedstatus + " " + sql + " " + " 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,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(" 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 ,vh.voucherDate as voucherDate,sum(misbill.paidamount) as paidAmount,current_date as chequeDate from Paymentheader ph,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 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,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(" for non salary and remittance" + query); chequeAssignmentList.addAll(query.list()); } else { StringBuilder tempquery1 = new StringBuilder(); tempquery1.append( "select vh.id as voucherid ,vh.voucherNumber as voucherNumber ,vh.voucherDate as voucherDate,sum(misbill.paidamount) as paidAmount,current_date as chequeDate,misbill.paidto as paidTo from Paymentheader ph,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_remittance rem "); tempquery1.append(" where ph.voucherheaderid=misbill.payvhid and rem.paymentvhid=vh.id "); String recoveryId = parameters.get("recoveryId")[0]; if (!recoveryId.isEmpty()) tempquery1.append(" and rem.tdsid= " + parameters.get("recoveryId")[0]); tempquery1.append(" and ph.voucherheaderid=vh.id and vmis.voucherheaderid= vh.id and vh.status ="); tempquery1.append(approvedstatus); tempquery1.append(" "); tempquery1.append(sql); tempquery1.append(" "); tempquery1.append(" and IV.VOUCHERHEADERID IS NULL and vh.type='"); tempquery1.append(FinancialConstants.STANDARD_VOUCHER_TYPE_PAYMENT); tempquery1.append("' and vh.name ='"); tempquery1.append(FinancialConstants.PAYMENTVOUCHER_NAME_REMITTANCE); tempquery1.append("'"); tempquery1.append( " group by vh.id,vh.voucherNumber,vh.voucherDate,misbill.paidto order by vh.voucherNumber "); query = getSession().createSQLQuery(tempquery1.toString()).addScalar("voucherid", LongType.INSTANCE) .addScalar("voucherNumber").addScalar("voucherDate") .addScalar("paidAmount", BigDecimalType.INSTANCE).addScalar("chequeDate") .addScalar("paidTo").setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug(" for salary and remittance" + query); chequeAssignmentList = query.list(); // below one handles // assign-->surrendar-->assign-->surrendar-->....... StringBuilder tempquery = new StringBuilder(); if (LOGGER.isDebugEnabled()) LOGGER.debug("checking cheque assigned and surrendard"); tempquery.append( "select vh.id as voucherid ,vh.voucherNumber as voucherNumber ,vh.voucherDate as voucherDate,sum(misbill.paidamount) as paidAmount,current_date as chequeDate,misbill.paidto as paidTo from Paymentheader ph,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_remittance rem "); tempquery.append( ", (select max(iv1.instrumentheaderid) as maxihid,iv1.voucherheaderid as iv1vhid from egf_instrumentvoucher iv1 group by iv1.voucherheaderid) table1"); tempquery.append(" where ph.voucherheaderid=misbill.payvhid and rem.paymentvhid=vh.id "); String recoveryId1 = parameters.get("recoveryId")[0]; if (!recoveryId1.isEmpty()) tempquery.append(" and rem.tdsid= " + parameters.get("recoveryId")[0]); tempquery.append(" and ph.voucherheaderid=vh.id and vmis.voucherheaderid= vh.id and vh.status ="); tempquery.append(approvedstatus); tempquery.append(" "); tempquery.append(sql); tempquery.append(" "); tempquery.append( " and IV.VOUCHERHEADERID IS NOT NULL and iv.instrumentheaderid=table1.maxihid and table1.iv1vhid=vh.id and ih.id_status not in ("); tempquery.append(statusId); tempquery.append(") and vh.type='"); tempquery.append(FinancialConstants.STANDARD_VOUCHER_TYPE_PAYMENT); tempquery.append("' and vh.name ='"); tempquery.append(FinancialConstants.PAYMENTVOUCHER_NAME_REMITTANCE); tempquery.append("'"); tempquery.append( " group by vh.id,vh.voucherNumber,vh.voucherDate,misbill.paidto order by vh.voucherNumber "); query = getSession().createSQLQuery(tempquery.toString()).addScalar("voucherid", LongType.INSTANCE) .addScalar("voucherNumber").addScalar("voucherDate") .addScalar("paidAmount", BigDecimalType.INSTANCE).addScalar("chequeDate") .addScalar("paidTo").setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug(" for salary and remittance" + 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> getPaymentVoucherForRemittanceRTGSInstrument( final Map<String, String[]> parameters, final CVoucherHeader voucherHeader) throws ApplicationException, ParseException { if (LOGGER.isDebugEnabled()) LOGGER.debug("Starting getPaymentVoucherNotInInstrument..."); List<ChequeAssignment> chequeAssignmentList = new ArrayList<ChequeAssignment>(); boolean nonSubledger = false; final StringBuffer sql = new StringBuffer(); if (!"".equals(parameters.get("fromDate")[0])) sql.append(//from w w w . jav a 2 s .co m " and vh.voucherDate>='" + sdf.format(formatter.parse(parameters.get("fromDate")[0])) + "' "); if (!"".equals(parameters.get("toDate")[0])) sql.append(" and vh.voucherDate<='" + sdf.format(formatter.parse(parameters.get("toDate")[0])) + "'"); if (!StringUtils.isEmpty(voucherHeader.getVoucherNumber())) sql.append(" and vh.voucherNumber like '%" + voucherHeader.getVoucherNumber() + "%'"); if (voucherHeader.getFundId() != null) sql.append(" and vh.fundId=" + voucherHeader.getFundId().getId()); if (voucherHeader.getVouchermis().getFundsource() != null) sql.append(" and vmis.fundsourceId=" + voucherHeader.getVouchermis().getFundsource().getId()); if (voucherHeader.getVouchermis().getDepartmentid() != null) sql.append(" and vmis.departmentid=" + voucherHeader.getVouchermis().getDepartmentid().getId()); if (voucherHeader.getVouchermis().getSchemeid() != null) sql.append(" and vmis.schemeid=" + voucherHeader.getVouchermis().getSchemeid().getId()); if (voucherHeader.getVouchermis().getSubschemeid() != null) sql.append(" and vmis.subschemeid=" + voucherHeader.getVouchermis().getSubschemeid().getId()); if (voucherHeader.getVouchermis().getFunctionary() != null) sql.append(" and vmis.functionaryid=" + voucherHeader.getVouchermis().getFunctionary().getId()); if (voucherHeader.getVouchermis().getDivisionid() != null) sql.append(" and vmis.divisionid=" + voucherHeader.getVouchermis().getDivisionid().getId()); if (parameters.get("bankaccount") != null && !parameters.get("bankaccount")[0].equals("-1")) { sql.append(" and ph.bankaccountnumberid=" + parameters.get("bankaccount")[0]); sql.append(" and lower(ph.type)=lower('" + parameters.get("paymentMode")[0] + "')"); sql.append(" and ph.bankaccountnumberid=ba.id"); } else sql.append(" and ph.bankaccountnumberid=ba.id") // TODO this is hardcode to rtgs (read from financials Constants) .append(" and lower(ph.type)=lower('" + parameters.get("paymentMode")[0] + "')"); if (!"0".equals(parameters.get("drawingOfficerId")[0])) sql.append(" and ph.drawingofficer_id = " + new Long(parameters.get("drawingOfficerId")[0])); if (!"".equals(parameters.get("recoveryId")[0])) { final Recovery recovery = (Recovery) persistenceService.find("from Recovery where id=?", new Long(parameters.get("recoveryId")[0])); if (recovery.getChartofaccounts().getChartOfAccountDetails().isEmpty()) nonSubledger = true; sql.append(" and gl.glcodeid = " + recovery.getChartofaccounts().getId()); } else sql.append(" and gl.glcodeid in (select distinct glcodeid from tds where remittance_mode='A')"); 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 if (nonSubledger) { query = getSession() .createSQLQuery(" select vh.id as voucherid ,vh.voucherNumber as voucherNumber ," + " dept.name AS departmentName, vh.voucherDate as voucherDate," + " recovery.remitted as paidTo,sum(misbill.paidamount) as paidAmount,current_date as chequeDate" + " , ba.accountnumber AS bankAccNumber, ba.id AS bankAccountId ," + " gl.glcodeid as glcodeId," + " CONCAT(CONCAT(DO.name,'/'),do.tan) AS drawingOfficerNameTAN " + " 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 ,generalledger gl,eg_drawingofficer do,tds recovery" + " where recovery.type = '" + parameters.get("recoveryCode")[0] + "' and ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vmis.voucherheaderid= vh.id " + " and vh.id= gl.voucherheaderid and ph.drawingofficer_id= do.id " + " and vh.status =" + approvedstatus + " " + sql + " " + " and IV.VOUCHERHEADERID IS NULL and vh.type='" + FinancialConstants.STANDARD_VOUCHER_TYPE_PAYMENT + "' " + " and vh.name = '" + FinancialConstants.PAYMENTVOUCHER_NAME_REMITTANCE + "' " + " group by vh.id, vh.voucherNumber, dept.name , vh.voucherDate,misbill.paidto, " + " ba.accountnumber, ba.id ," + " gl.glcodeid,DO.name,do.tan,recovery.remitted " + " 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("glcodeId", LongType.INSTANCE).addScalar("drawingOfficerNameTAN") .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); // TODO Changet the debug statement to appropriate sentence if (LOGGER.isDebugEnabled()) LOGGER.debug(" 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, recovery.remitted as paidTo" + " ,sum(misbill.paidamount) as paidAmount,current_date as chequeDate , ba.accountnumber AS bankAccNumber " + " , ba.id AS bankAccountId , " + " gl.glcodeid as glcodeId," + " CONCAT(CONCAT(DO.name,'/'),do.tan) AS drawingOfficerNameTAN " + " from Paymentheader ph,eg_department dept, bankaccount ba," + " generalledger gl,eg_drawingofficer do, 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,tds recovery" + ",(select max(iv1.instrumentheaderid) as maxihid,iv1.voucherheaderid as iv1vhid from egf_instrumentvoucher iv1 group by iv1.voucherheaderid) as table1 " + " where recovery.type = '" + parameters.get("recoveryCode")[0] + "' and ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vmis.voucherheaderid= vh.id " + " and vh.status =" + approvedstatus + " " + sql + " " + " 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 = '" + FinancialConstants.PAYMENTVOUCHER_NAME_REMITTANCE + "'" + " and vh.id= gl.voucherheaderid " + " and ph.drawingofficer_id= do.id " + " group by vh.id, vh.voucherNumber, dept.name , vh.voucherDate,misbill.paidto,ba.accountnumber," + " ba.id ," + " gl.glcodeid,DO.name,do.tan,recovery.remitted 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("glcodeId", LongType.INSTANCE).addScalar("drawingOfficerNameTAN") .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug(" Surrendered rtgs nos" + query); chequeAssignmentList.addAll(query.list()); } else { 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 ," + " gl.glcodeid as glcodeId," + " CONCAT(CONCAT(DO.name,'/'),do.tan) AS drawingOfficerNameTAN " + " 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 , generalledgerdetail gld,generalledger gl,eg_drawingofficer do" + " where ph.voucherheaderid=misbill.payvhid and ph.voucherheaderid=vh.id and vmis.voucherheaderid= vh.id " + " and vh.id= gl.voucherheaderid and gl.id=gld.generalledgerid and ph.drawingofficer_id= do.id " + " and vh.status =" + approvedstatus + " " + sql + " " + " and IV.VOUCHERHEADERID IS NULL and vh.type='" + FinancialConstants.STANDARD_VOUCHER_TYPE_PAYMENT + "' " + " and vh.name = '" + FinancialConstants.PAYMENTVOUCHER_NAME_REMITTANCE + "' " + " group by vh.id, vh.voucherNumber, dept.name , vh.voucherDate,misbill.paidto, " + " ba.accountnumber, ba.id ," + " gl.glcodeid,DO.name,do.tan " + " 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("glcodeId", LongType.INSTANCE).addScalar("drawingOfficerNameTAN") .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); // TODO Changet the debug statement to appropriate sentence if (LOGGER.isDebugEnabled()) LOGGER.debug(" 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 , " + " gl.glcodeid as glcodeId," + " CONCAT(CONCAT(DO.name,'/'),do.tan) AS drawingOfficerNameTAN " + " from Paymentheader ph,eg_department dept, bankaccount ba," + " generalledgerdetail gld,generalledger gl,eg_drawingofficer do, 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 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 = '" + FinancialConstants.PAYMENTVOUCHER_NAME_REMITTANCE + "'" + " and vh.id= gl.voucherheaderid and gl.id=gld.generalledgerid and gl.glcodeid in (select distinct glcodeid from tds where remittance_mode='A')" + " and ph.drawingofficer_id= do.id " + " group by vh.id, vh.voucherNumber, dept.name , vh.voucherDate,misbill.paidto,ba.accountnumber," + " ba.id ," + " gl.glcodeid,DO.name,do.tan 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("glcodeId", LongType.INSTANCE).addScalar("drawingOfficerNameTAN") .setResultTransformer(Transformers.aliasToBean(ChequeAssignment.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug(" Surrendered rtgs nos" + query); chequeAssignmentList.addAll(query.list()); } if (LOGGER.isDebugEnabled()) LOGGER.debug("Completed getPaymentVoucherNotInInstrument."); return chequeAssignmentList; }
From source file:org.egov.services.report.FunctionwiseIEService.java
License:Open Source License
/** * * @param reportSearch/* w w w . jav a 2s. c o m*/ * @return * @throws ApplicationException * @throws ParseException for Main report getMajor Code and Minor Code for Sub Report get only minor code */ public List<CommonReportBean> getMinorAndMajorCodeList(final ReportSearch reportSearch) throws ApplicationException, ParseException { String sql = ""; if (reportSearch.getByDepartment() && reportSearch.getByDetailCode()) sql = " select coa.glcode as accCode,coa.name as name,null as schedule,null as FIEscheduleId,0 as isMajor from Chartofaccounts coa" + " where coa.type=:type and length(coa.glcode)=" + reportSearch.getMinorCodeLen() + " and coa.glcode like :glcode and classification=4 and isActiveForPosting=true order by 1 "; else if (reportSearch.getByDepartment()) sql = " select coa.glcode as accCode,coa.name as name,mp.schedule as schedule,mp.id as FIEscheduleId,0 as isMajor from Chartofaccounts coa,Schedulemapping mp" + " where coa.FIEscheduleId=mp.id and coa.type=:type and length(coa.glcode)=" + reportSearch.getMinorCodeLen() + " and coa.FIEscheduleId=:FIEscheduleId order by 1"; else sql = " select coa.glcode as accCode,coa.name as name,mp.schedule as schedule,mp.id as FIEscheduleId, 0 as isMajor from Chartofaccounts coa,Schedulemapping mp" + " where coa.FIEscheduleId=mp.id and coa.type=:type and length(coa.glcode)=" + reportSearch.getMinorCodeLen() + " Union " + " select coa.glcode as accCode,coa.name as name,null as schedule,null as FIEscheduleId ,1 as isMajor from Chartofaccounts coa" + " where coa.type=:type and length(coa.glcode)=" + reportSearch.getMajorCodeLen() + " order by 1"; final Query query = persistenceService.getSession().createSQLQuery(sql) .addScalar("accCode", StringType.INSTANCE).addScalar("name", StringType.INSTANCE) .addScalar("schedule", StringType.INSTANCE).addScalar("FIEscheduleId", LongType.INSTANCE) .addScalar("isMajor", BooleanType.INSTANCE).setString("type", reportSearch.getIncExp()) .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class)); if (reportSearch.getByDetailCode()) query.setString("glcode", reportSearch.getGlcode() + "%"); else if (reportSearch.getByDepartment()) query.setLong("FIEscheduleId", reportSearch.getFIEscheduleId()); if (LOGGER.isDebugEnabled()) LOGGER.debug("----------------" + sql); return query.list(); }
From source file:org.egov.services.report.FunctionwiseIEService.java
License:Open Source License
/** * * @param reportSearch//from w ww. j a v a 2s. c o m * @return * @throws ApplicationException * @throws ParseException for Main report getMajor Code and Minor Code for Sub Report get only minor code */ public List<CommonReportBean> getMinorAndMajorCodeListForCapitalExp(final ReportSearch reportSearch) throws ApplicationException, ParseException { String sql = ""; if (reportSearch.getByDepartment() && reportSearch.getByDetailCode()) sql = " select coa.glcode as accCode,coa.name as name,null as schedule,null as FIEscheduleId,0 as isMajor from Chartofaccounts coa" + " where coa.type=:type and length(coa.glcode)=" + reportSearch.getMinorCodeLen() + " and coa.glcode like :glcode and classification=4 and isActiveForPosting=true order by 1 "; else if (reportSearch.getByDepartment()) sql = " select coa.glcode as accCode,coa.name as name,mp.schedule as schedule,mp.id as FIEscheduleId,0 as isMajor from Chartofaccounts coa,Schedulemapping mp" + " where coa.FIEscheduleId=mp.id and coa.type=:type and length(coa.glcode)=" + reportSearch.getMinorCodeLen() + " and coa.FIEscheduleId=:FIEscheduleId order by 1"; else sql = " select coa.glcode as accCode,coa.name as name,mp.schedule as schedule,mp.id as FIEscheduleId, 0 as isMajor from Chartofaccounts coa,Schedulemapping mp" + " where coa.FIEscheduleId=mp.id and coa.type=:type and length(coa.glcode)=" + reportSearch.getMinorCodeLen() + " Union " + " select coa.glcode as accCode,coa.name as name,null as schedule,null as FIEscheduleId ,1 as isMajor from Chartofaccounts coa" + " where coa.type=:type and length(coa.glcode)=" + reportSearch.getMajorCodeLen() + "and coa.glcode in (" + capExpCodesWithQuotesCond + ")" + " order by 1"; final Query query = persistenceService.getSession().createSQLQuery(sql) .addScalar("accCode", StringType.INSTANCE).addScalar("name", StringType.INSTANCE) .addScalar("schedule", StringType.INSTANCE).addScalar("FIEscheduleId", LongType.INSTANCE) .addScalar("isMajor", BooleanType.INSTANCE).setString("type", "A") .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class)); if (reportSearch.getByDetailCode()) query.setString("glcode", reportSearch.getGlcode() + "%"); else if (reportSearch.getByDepartment()) query.setLong("FIEscheduleId", reportSearch.getFIEscheduleId()); if (LOGGER.isDebugEnabled()) LOGGER.debug("----------------" + sql); return query.list(); }
From source file:org.egov.services.report.FunctionwiseIEService.java
License:Open Source License
public List<CommonReportBean> getIncomeMinorAndMajorCodeList(final ReportSearch reportSearch) throws ApplicationException, ParseException { String sql = ""; if (reportSearch.getByDepartment() && reportSearch.getByDetailCode()) sql = " select coa.glcode as accCode,coa.name as name,null as schedule,null as FIEscheduleId,0 as isMajor from Chartofaccounts coa" + " where coa.type=:type and length(coa.glcode)=" + reportSearch.getMinorCodeLen() + " and coa.glcode like :glcode and classification=4 and isActiveForPosting=true order by 1 "; else if (reportSearch.getByDepartment()) sql = " select coa.glcode as accCode,coa.name as name,mp.schedule as schedule,mp.id as FIEscheduleId,0 as isMajor from Chartofaccounts coa,Schedulemapping mp" + " where coa.FIEscheduleId=mp.id and coa.type=:type and length(coa.glcode)=" + reportSearch.getMinorCodeLen() + " and coa.FIEscheduleId=:FIEscheduleId order by 1"; else/* w w w . jav a 2 s . c o m*/ sql = " select coa.glcode as accCode,coa.name as name,mp.schedule as schedule,mp.id as FIEscheduleId, 0 as isMajor from Chartofaccounts coa,Schedulemapping mp" + " where coa.FIEscheduleId=mp.id and coa.type=:type and length(coa.glcode)=" + reportSearch.getMinorCodeLen() + " Union " + " select coa.glcode as accCode,coa.name as name,null as schedule,null as FIEscheduleId ,1 as isMajor from Chartofaccounts coa" + " where coa.type=:type and length(coa.glcode)=" + reportSearch.getMajorCodeLen() + " order by 1"; final Query query = persistenceService.getSession().createSQLQuery(sql) .addScalar("accCode", StringType.INSTANCE).addScalar("name", StringType.INSTANCE) .addScalar("schedule", StringType.INSTANCE).addScalar("FIEscheduleId", LongType.INSTANCE) .addScalar("isMajor", BooleanType.INSTANCE).setString("type", reportSearch.getIncExp()) .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class)); if (reportSearch.getByDetailCode()) query.setString("glcode", reportSearch.getGlcode() + "%"); else if (reportSearch.getByDepartment()) query.setLong("FIEscheduleId", reportSearch.getFIEscheduleId()); if (LOGGER.isDebugEnabled()) LOGGER.debug("----------------" + sql); return query.list(); }
From source file:org.egov.services.report.LoanGrantService.java
License:Open Source License
@SuppressWarnings("unchecked") public List<Object> schemeUtilizationBy(final Integer schemeId, final Integer subSchemeId, final Date fromDate, final Date toDate, final List<Integer> projectCodeIdList, final Integer fundId) { final Accountdetailtype detailType = (Accountdetailtype) find("from Accountdetailtype where upper(name)=?", "PROJECTCODE"); final StringBuffer schemeUtilSql = new StringBuffer(512); String pcStr = ""; if (projectCodeIdList != null && projectCodeIdList.size() > 0) { pcStr = projectCodeIdList.toString(); pcStr = pcStr.replace("[", "("); pcStr = pcStr.replace("]", ")"); }// www. j a v a 2 s . c o m if (subSchemeId != null) { schemeUtilSql.append( "select ss.name as subScheme, pc.code as code, vh.voucherNumber as vouchernumber, vh.voucherDate as voucherdate, gld.amount as amount, gld.detailkeyid as id " + "from voucherheader vh, generalledger gl, generalledgerdetail gld , egf_subscheme_project ssp, egw_projectcode pc" + ",sub_scheme ss " + " where " + " vh.id= gl.voucherheaderid and vh.status not in (1,2,4) " + " and gl.id= gld.generalledgerid "); if (fromDate != null) schemeUtilSql.append(" and vh.voucherdate>='" + Constants.DD_MON_YYYYFORMAT.format(fromDate) + "'"); if (toDate != null) schemeUtilSql.append(" and vh.voucherdate<='" + Constants.DD_MON_YYYYFORMAT.format(toDate) + "'"); schemeUtilSql.append(" and gld.detailtypeid=" + detailType.getId() + " and gld.detailkeyid= ssp.projectcodeid " + " and ssp.subschemeid=" + subSchemeId + " and ss.id=" + subSchemeId + " and ss.id=ssp.subschemeid" + " and pc.id= gld.detailkeyid " + " and pc.id= ssp.projectcodeid and vh.fundid=" + fundId + " "); if (projectCodeIdList != null && projectCodeIdList.size() > 0) schemeUtilSql.append(" and ssp.projectcodeid in " + pcStr + " "); schemeUtilSql.append("ORDER by ss.name, pc.code,vh.voucherdate "); } else if (schemeId != null) { schemeUtilSql.append( "select ss.name as subScheme, pc.code as code, vh.voucherNumber as vouchernumber, vh.voucherDate as voucherdate, gld.amount as amount, gld.detailkeyid as id " + "from voucherheader vh, generalledger gl, generalledgerdetail gld , egf_subscheme_project ssp, egw_projectcode pc" + ",sub_scheme ss,scheme s " + " where " + " vh.id= gl.voucherheaderid and vh.status not in (1,2,4) " + " and gl.id= gld.generalledgerid "); if (fromDate != null) schemeUtilSql.append(" and vh.voucherdate>='" + Constants.DD_MON_YYYYFORMAT.format(fromDate) + "'"); if (toDate != null) schemeUtilSql.append(" and vh.voucherdate<='" + Constants.DD_MON_YYYYFORMAT.format(toDate) + "'"); schemeUtilSql.append( " and gld.detailtypeid=" + detailType.getId() + " and gld.detailkeyid= ssp.projectcodeid " + " and ssp.subschemeid=ss.id" + " and ss.schemeid=s.id" + " and s.id=" + schemeId + " and ss.id=ssp.subschemeid" + " and pc.id= gld.detailkeyid " + " and pc.id= ssp.projectcodeid and vh.fundid=" + fundId + " "); if (projectCodeIdList != null && projectCodeIdList.size() > 0) schemeUtilSql.append(" and ssp.projectcodeid in " + pcStr + " "); schemeUtilSql.append(" ORDER by ss.name, pc.code,vh.voucherdate "); } final String schemeUtilSqlQry = schemeUtilSql.toString(); final SQLQuery schemeUtilQry = getSession().createSQLQuery(schemeUtilSqlQry); schemeUtilQry.addScalar("subScheme").addScalar("code").addScalar("voucherNumber").addScalar("voucherDate") .addScalar("amount", BigDecimalType.INSTANCE).addScalar("id", LongType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(LoanGrantBean.class)); final List<Object> projecCodeResultList = schemeUtilQry.list(); return projecCodeResultList; }
From source file:org.egov.works.reports.service.WorkProgressRegisterService.java
License:Open Source License
@Transactional public List<EstimateAbstractReport> searchEstimateAbstractReportByDepartmentWise( final EstimateAbstractReport estimateAbstractReport) { Query query = null;/*from w w w.j a v a2 s .c o m*/ query = entityManager.unwrap(Session.class) .createSQLQuery(getQueryForDepartmentWiseReport(estimateAbstractReport)) .addScalar("departmentName", StringType.INSTANCE).addScalar("lineEstimates", LongType.INSTANCE) .addScalar("adminSanctionedEstimates", LongType.INSTANCE) .addScalar("adminSanctionedAmountInCrores", StringType.INSTANCE) .addScalar("technicalSanctionedEstimates", LongType.INSTANCE) .addScalar("loaCreated", LongType.INSTANCE).addScalar("agreementValueInCrores", StringType.INSTANCE) .addScalar("workInProgress", LongType.INSTANCE).addScalar("workCompleted", LongType.INSTANCE) .addScalar("billsCreated", LongType.INSTANCE).addScalar("billValueInCrores", StringType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(EstimateAbstractReport.class)); query = setParameterForDepartmentWiseReport(estimateAbstractReport, query); return query.list(); }
From source file:org.egov.works.reports.service.WorkProgressRegisterService.java
License:Open Source License
@Transactional public List<EstimateAbstractReport> searchEstimateAbstractReportByTypeOfWorkWise( final EstimateAbstractReport estimateAbstractReport) { Query query = null;//w w w. j av a 2 s .c o m if (estimateAbstractReport.getDepartments() != null && !estimateAbstractReport.getDepartments().toString().equalsIgnoreCase("[null]")) { query = entityManager.unwrap(Session.class) .createSQLQuery(getQueryForTypeOfWorkWiseReport(estimateAbstractReport)) .addScalar("typeOfWorkName", StringType.INSTANCE) .addScalar("subTypeOfWorkName", StringType.INSTANCE) .addScalar("departmentName", StringType.INSTANCE).addScalar("lineEstimates", LongType.INSTANCE) .addScalar("adminSanctionedEstimates", LongType.INSTANCE) .addScalar("adminSanctionedAmountInCrores", StringType.INSTANCE) .addScalar("technicalSanctionedEstimates", LongType.INSTANCE) .addScalar("loaCreated", LongType.INSTANCE) .addScalar("agreementValueInCrores", StringType.INSTANCE) .addScalar("workInProgress", LongType.INSTANCE).addScalar("workCompleted", LongType.INSTANCE) .addScalar("billsCreated", LongType.INSTANCE) .addScalar("billValueInCrores", StringType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(EstimateAbstractReport.class)); query = setParameterForTypeOfWorkWiseReport(estimateAbstractReport, query); } else { query = entityManager.unwrap(Session.class) .createSQLQuery(getQueryForTypeOfWorkWiseReport(estimateAbstractReport)) .addScalar("typeOfWorkName", StringType.INSTANCE) .addScalar("subTypeOfWorkName", StringType.INSTANCE) .addScalar("lineEstimates", LongType.INSTANCE) .addScalar("adminSanctionedEstimates", LongType.INSTANCE) .addScalar("adminSanctionedAmountInCrores", StringType.INSTANCE) .addScalar("technicalSanctionedEstimates", LongType.INSTANCE) .addScalar("loaCreated", LongType.INSTANCE) .addScalar("agreementValueInCrores", StringType.INSTANCE) .addScalar("workInProgress", LongType.INSTANCE).addScalar("workCompleted", LongType.INSTANCE) .addScalar("billsCreated", LongType.INSTANCE) .addScalar("billValueInCrores", StringType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(EstimateAbstractReport.class)); query = setParameterForTypeOfWorkWiseReport(estimateAbstractReport, query); } return query.list(); }
From source file:org.hoteia.qalingo.core.dao.RetailerDao.java
License:Apache License
public List<GeolocatedStore> findStoresByGeoloc(final String latitude, final String longitude, final String distance, int maxResults, Object... params) { Float latitudeFloat = new Float(latitude); Float longitudeFloat = new Float(longitude); String queryString = "SELECT store.id, store.code, ((ACOS(SIN(:latitude * PI() / 180) * SIN(latitude * PI() / 180) + COS(:latitude * PI() / 180) * COS(latitude * PI() / 180) * COS((:longitude - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM teco_store store HAVING distance <= :distanceValue ORDER BY distance ASC"; Query query = createNativeQuery(queryString); query.setParameter("latitude", latitudeFloat.floatValue()); query.setParameter("longitude", longitudeFloat.floatValue()); query.setParameter("distanceValue", distance); query.setMaxResults(maxResults);/* www . j a v a 2s . co m*/ query.unwrap(SQLQuery.class).addScalar("id", LongType.INSTANCE).addScalar("code", StringType.INSTANCE) .addScalar("distance", DoubleType.INSTANCE); @SuppressWarnings("unchecked") List<Object[]> objects = query.getResultList(); List<GeolocatedStore> stores = new ArrayList<GeolocatedStore>(); for (Iterator<Object[]> iterator = objects.iterator(); iterator.hasNext();) { Object[] object = iterator.next(); GeolocatedStore geolocatedStore = new GeolocatedStore(); geolocatedStore.setId((Long) object[0]); geolocatedStore.setCode((String) object[1]); geolocatedStore.setDistance((Double) object[2]); stores.add(geolocatedStore); } return stores; }
From source file:org.hoteia.qalingo.core.dao.RetailerDao.java
License:Apache License
public List<GeolocatedStore> findStoresByGeolocAndCountry(final String countryCode, final String latitude, final String longitude, final String distance, int maxResults, Object... params) { if (StringUtils.isNotEmpty(latitude) && StringUtils.isNotEmpty(longitude)) { Float latitudeFloat = new Float(latitude); Float longitudeFloat = new Float(longitude); String queryString = "SELECT store.id, store.code, ((ACOS(SIN(:latitude * PI() / 180) * SIN(latitude * PI() / 180) + COS(:latitude * PI() / 180) * COS(latitude * PI() / 180) * COS((:longitude - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM teco_store store WHERE country_code = :countryCode HAVING distance <= :distanceValue ORDER BY distance ASC"; Query query = createNativeQuery(queryString); query.setParameter("latitude", latitudeFloat.floatValue()); query.setParameter("longitude", longitudeFloat.floatValue()); query.setParameter("countryCode", countryCode); query.setParameter("distanceValue", distance); query.setMaxResults(maxResults); query.unwrap(SQLQuery.class).addScalar("id", LongType.INSTANCE).addScalar("code", StringType.INSTANCE) .addScalar("distance", DoubleType.INSTANCE); @SuppressWarnings("unchecked") List<Object[]> objects = query.getResultList(); List<GeolocatedStore> stores = new ArrayList<GeolocatedStore>(); for (Iterator<Object[]> iterator = objects.iterator(); iterator.hasNext();) { Object[] object = iterator.next(); GeolocatedStore geolocatedStore = new GeolocatedStore(); geolocatedStore.setId((Long) object[0]); geolocatedStore.setCode((String) object[1]); geolocatedStore.setDistance((Double) object[2]); stores.add(geolocatedStore); }//from w ww.j a va 2 s . co m return stores; } return null; }