List of usage examples for org.hibernate SQLQuery setParameterList
@Override
NativeQuery<T> setParameterList(String name, Object[] values);
From source file:models.JournalDetail.java
public Double getSumByThisMonth(String year, String month, String[] accountNo, String calc) { Session session = DatabaseUtil.getSessionFactory().openSession(); Transaction tx = null;/*ww w. j a va2s . c o m*/ Double total = 0.00; String calcSelect = "Debet - Credit".equals(calc) ? "jd.debet - jd.credit" : "jd.credit - jd.debet"; try { tx = session.beginTransaction(); String sql = "select sum(" + calcSelect + ") from Journal_details jd inner join journals j on j.id = jd.journal_id " + " where month(j.date) = :month and year(j.date) = :year and jd.account_no in(:no) "; SQLQuery query = session.createSQLQuery(sql); query.setParameter("month", month); query.setParameter("year", year); query.setParameterList("no", accountNo); List list = query.list(); total = Double.parseDouble(list.get(0) != null ? list.get(0).toString() : "0.00"); session.flush(); tx.commit(); } catch (HibernateException e) { System.out.println(e.getMessage()); if (tx != null) { tx.rollback(); } } finally { session.close(); } return total; }
From source file:models.JournalDetail.java
public Double getSumByUntilMonth(String year, String month, String[] accountNo, String calc) { Session session = DatabaseUtil.getSessionFactory().openSession(); Transaction tx = null;/* ww w .j a v a 2 s . c om*/ Double total = 0.00; String calcSelect = "Debet - Credit".equals(calc) ? "jd.debet - jd.credit" : "jd.credit - jd.debet"; try { tx = session.beginTransaction(); String sql = "select sum(" + calcSelect + ") from journal_details jd inner join journals j on j.id = jd.journal_id " + " where month(j.date) <= :month and year(j.date) = :year and jd.account_no in(:no) "; SQLQuery query = session.createSQLQuery(sql); query.setParameter("month", month); query.setParameter("year", year); query.setParameterList("no", accountNo); List list = query.list(); total = Double.parseDouble(list.get(0) != null ? list.get(0).toString() : "0.00"); session.flush(); tx.commit(); } catch (HibernateException e) { System.out.println(e.getMessage()); if (tx != null) { tx.rollback(); } } finally { session.close(); } return total; }
From source file:models.JournalDetail.java
public Double getBalanceSheetSummary(Date periode, String year, String accountNo, String calc) { Session session = DatabaseUtil.getSessionFactory().openSession(); Transaction tx = null;// ww w . jav a 2 s .co m Double total = null; String[] args; accountNo = accountNo.trim(); String calcSelect = "Debet - Credit".equals(calc.trim()) ? "jd.debet - jd.credit" : "jd.credit - jd.debet"; String sql; SQLQuery query; try { tx = session.beginTransaction(); System.out.println("Account Data : " + accountNo); if (accountNo.contains("to")) { args = Formula.args(accountNo.trim(), "to"); String arg1 = args[0].trim(); String arg2 = args[1].trim(); System.out.println("Data ke 0 : " + args[0]); System.out.println("Data ke 1 : " + arg1); sql = "select sum(" + calcSelect + ") from journal_details jd inner join journals j on j.id = jd.journal_id " + " where j.date <= :date and year(j.date) = :year and jd.account_no BETWEEN :arg0 and :arg1 "; query = session.createSQLQuery(sql); query.setParameter("date", periode); query.setParameter("year", year); query.setParameter("arg0", arg1); query.setParameter("arg1", arg2); total = (Double) query.uniqueResult(); } else { args = Formula.args(accountNo.trim(), "\\,"); sql = "select sum(" + calcSelect + ") from journal_details jd inner join journals j on j.id = jd.journal_id " + " where j.date <= :date and year(j.date) = :year and jd.account_no in(:no) "; query = session.createSQLQuery(sql); query.setParameter("date", periode); query.setParameter("year", year); query.setParameterList("no", args); total = (Double) query.uniqueResult(); } session.flush(); tx.commit(); } catch (HibernateException e) { total = 0.00; System.out.println(e.getMessage()); if (tx != null) { tx.rollback(); } } finally { session.close(); } if (total == null) { total = 0.00; } return total; }
From source file:models.ProfitLossStandardSummary.java
public Double[] GetSummary(String args) { Session session = DatabaseUtil.getSessionFactory().openSession(); Transaction tx = null;/*w ww .j a va2 s.c o m*/ String formula = Formula.main(args); Double[] total = new Double[2]; try { tx = session.beginTransaction(); String sql; SQLQuery query; switch (formula.toLowerCase().trim()) { case "sum": sql = "select " + formula + "(total_this_month)," + formula + "(total_until_month) from profit_loss_standard_summaries" + " where ref >= :arg1 and ref <= :arg2"; query = session.createSQLQuery(sql); Integer[] arg = Formula.ref(args); query.setParameter("arg1", arg[0].toString()); query.setParameter("arg2", arg[1].toString()); break; default: sql = "select " + formula + "(total_this_month)," + formula + "(total_until_month) from profit_loss_standard_summaries" + " where ref in(:arg) "; query = session.createSQLQuery(sql); query.setParameterList("arg", Formula.ref(args)); break; } List list = query.list(); Iterator it = list.iterator(); Double total1 = 0.00; Double total2 = 0.00; while (it.hasNext()) { Object obj[] = (Object[]) it.next(); total1 += Double.parseDouble(obj[0].toString()); total2 += Double.parseDouble(obj[1].toString()); } System.out.println("Total 0 " + total1); System.out.println("Total 1 " + total2); total[0] = total1; total[1] = total2; tx.commit(); } catch (HibernateException e) { System.out.println(e.getMessage()); if (tx != null) { tx.rollback(); } } finally { session.close(); } return total; }
From source file:org.egov.collection.service.CollectionReportHeadWiseService.java
License:Open Source License
public CollectionSummaryHeadWiseReportResult getCollectionSummaryReport(final Date fromDate, final Date toDate, final String paymentMode, final String source, final String glCode, final int status, final Integer branchId) { final SimpleDateFormat fromDateFormatter = new SimpleDateFormat("yyyy-MM-dd 00:00:00"); final SimpleDateFormat toDateFormatter = new SimpleDateFormat("yyyy-MM-dd 23:59:59"); final StringBuilder defaultQueryStr = new StringBuilder(500); final StringBuilder aggregateQueryStr = new StringBuilder(); StringBuilder rebateQueryStr = new StringBuilder(""); StringBuilder revenueHeadQueryStr = new StringBuilder(""); final StringBuilder selectQueryStr = new StringBuilder( "SELECT (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='cash' THEN count(distinct(EGCL_COLLECTIONHEADER.id)) END) AS cashCount, " + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='cheque' THEN count(distinct(EGCL_COLLECTIONHEADER.id)) WHEN EGF_INSTRUMENTTYPE.TYPE='dd' THEN count(distinct(EGCL_COLLECTIONHEADER.id)) END) AS chequeddCount, " + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE= 'online' THEN count(distinct(EGCL_COLLECTIONHEADER.id)) END) AS onlineCount, " + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='card' THEN count(distinct(EGCL_COLLECTIONHEADER.id)) END) AS cardCount, " + " count(distinct(EGCL_COLLECTIONHEADER.id)) as totalReceiptCount, " + " EGCL_COLLECTIONHEADER.SOURCE AS source,CAO.NAME || '-' || CAO.GLCODE AS GLCODE,"); final StringBuilder revSelectQueryStr = new StringBuilder(selectQueryStr).append( " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='cash' THEN SUM(EGCL_COLLECTIONDETAILS.CRAMOUNT) END) AS cashAmount, " + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='cheque' THEN SUM(EGCL_COLLECTIONDETAILS.CRAMOUNT) WHEN EGF_INSTRUMENTTYPE.TYPE='dd' THEN SUM(EGCL_COLLECTIONDETAILS.CRAMOUNT) END) AS chequeddAmount," + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='card' THEN SUM(EGCL_COLLECTIONDETAILS.CRAMOUNT) END) AS cardAmount, " + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE= 'online' THEN SUM(EGCL_COLLECTIONDETAILS.CRAMOUNT) END) AS onlineAmount"); final StringBuilder rebateSelectQueryStr = new StringBuilder(selectQueryStr).append( " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='cash' THEN SUM(EGCL_COLLECTIONDETAILS.DRAMOUNT) END) AS cashAmount, " + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='cheque' THEN SUM(EGCL_COLLECTIONDETAILS.DRAMOUNT) WHEN EGF_INSTRUMENTTYPE.TYPE='dd' THEN SUM(EGCL_COLLECTIONDETAILS.DRAMOUNT) END) AS chequeddAmount," + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE='card' THEN SUM(EGCL_COLLECTIONDETAILS.DRAMOUNT) END) AS cardAmount, " + " (CASE WHEN EGF_INSTRUMENTTYPE.TYPE= 'online' THEN SUM(EGCL_COLLECTIONDETAILS.DRAMOUNT) END) AS onlineAmount"); final StringBuilder fromQueryStr = new StringBuilder(" FROM " + " EGCL_COLLECTIONHEADER EGCL_COLLECTIONHEADER INNER JOIN EGCL_COLLECTIONINSTRUMENT EGCL_COLLECTIONINSTRUMENT ON EGCL_COLLECTIONHEADER.ID = EGCL_COLLECTIONINSTRUMENT.COLLECTIONHEADER " + " INNER JOIN EGF_INSTRUMENTHEADER EGF_INSTRUMENTHEADER ON EGCL_COLLECTIONINSTRUMENT.INSTRUMENTHEADER = EGF_INSTRUMENTHEADER.ID " + " INNER JOIN EGW_STATUS EGW_STATUS ON EGCL_COLLECTIONHEADER.STATUS = EGW_STATUS.ID" + " INNER JOIN EGF_INSTRUMENTTYPE EGF_INSTRUMENTTYPE ON EGF_INSTRUMENTHEADER.INSTRUMENTTYPE = EGF_INSTRUMENTTYPE.ID" + " INNER JOIN EGCL_COLLECTIONMIS EGCL_COLLECTIONMIS ON EGCL_COLLECTIONHEADER.ID = EGCL_COLLECTIONMIS.COLLECTIONHEADER " + " INNER JOIN EGCL_COLLECTIONDETAILS EGCL_COLLECTIONDETAILS ON EGCL_COLLECTIONHEADER.ID = EGCL_COLLECTIONDETAILS.COLLECTIONHEADER " + " INNER JOIN CHARTOFACCOUNTS CAO ON CAO.ID = EGCL_COLLECTIONDETAILS.CHARTOFACCOUNT "); StringBuilder whereQueryStr = new StringBuilder(" WHERE EGW_STATUS.DESCRIPTION != 'Cancelled' "); StringBuilder creditWhereQueryStr = new StringBuilder(" AND EGCL_COLLECTIONDETAILS.CRAMOUNT>0 "); StringBuilder debitWhereQueryStr = new StringBuilder( " AND EGCL_COLLECTIONDETAILS.DRAMOUNT>0 AND CAO.purposeid in (select id from EGF_ACCOUNTCODE_PURPOSE where name ='" + CollectionConstants.PURPOSE_NAME_REBATE + "')"); final StringBuilder queryStrGroup = new StringBuilder( " GROUP BY source,CAO.NAME,CAO.GLCODE,EGF_INSTRUMENTTYPE.TYPE "); final StringBuilder finalSelectQueryStr = new StringBuilder( "SELECT sum(cashCount) AS cashCount,sum(chequeddCount) AS chequeddCount,sum(onlineCount) AS onlineCount,SOURCE,glCode,sum(cashAmount) AS cashAmount, sum(chequeddAmount) AS chequeddAmount, " + " sum(cardCount) AS cardCount, sum(cardAmount) AS cardAmount, cast(sum(totalReceiptCount) AS NUMERIC) as totalReceiptCount,sum(onlineAmount) AS onlineAmount FROM ("); final StringBuilder finalGroupQuery = new StringBuilder( " ) AS RESULT GROUP BY RESULT.SOURCE,RESULT.glCode order by source, glCode"); if (fromDate != null && toDate != null) { whereQueryStr.append(" AND EGCL_COLLECTIONHEADER.RECEIPTDATE between to_timestamp('" + fromDateFormatter.format(fromDate) + "', 'YYYY-MM-DD HH24:MI:SS') and " + " to_timestamp('" + toDateFormatter.format(toDate) + "', 'YYYY-MM-DD HH24:MI:SS') "); }/*from w w w . j a va 2s . c o m*/ if (!source.isEmpty() && !source.equals(CollectionConstants.ALL)) { whereQueryStr.append(" AND EGCL_COLLECTIONHEADER.SOURCE=:source"); } if (glCode != null) { whereQueryStr.append(" AND CAO.GLCODE =:glCode"); } if (branchId != null && branchId != -1) { whereQueryStr.append(" AND EGCL_COLLECTIONMIS.DEPOSITEDBRANCH=:branchId"); } if (status != -1) { whereQueryStr.append(" AND EGCL_COLLECTIONHEADER.STATUS =:searchStatus"); } if (StringUtils.isNotBlank(paymentMode) && !paymentMode.equals(CollectionConstants.ALL)) { whereQueryStr.append(" AND EGF_INSTRUMENTTYPE.TYPE in (:paymentMode)"); revenueHeadQueryStr.append(revSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(creditWhereQueryStr).append(queryStrGroup); rebateQueryStr.append(rebateSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(debitWhereQueryStr).append(queryStrGroup); } else { revenueHeadQueryStr.append(revSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(creditWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE = 'cash'") .append(queryStrGroup); revenueHeadQueryStr.append(" union "); revenueHeadQueryStr.append(revSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(creditWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE in( 'cheque','dd') ") .append(queryStrGroup); revenueHeadQueryStr.append(" union "); revenueHeadQueryStr.append(revSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(creditWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE = 'card'") .append(queryStrGroup); revenueHeadQueryStr.append(" union "); revenueHeadQueryStr.append(revSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(creditWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE = 'online'") .append(queryStrGroup); rebateQueryStr.append(rebateSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(debitWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE = 'cash'") .append(queryStrGroup); rebateQueryStr.append(" union "); rebateQueryStr.append(rebateSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(debitWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE in( 'cheque','dd') ") .append(queryStrGroup); rebateQueryStr.append(" union "); rebateQueryStr.append(rebateSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(debitWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE = 'card'") .append(queryStrGroup); rebateQueryStr.append(" union "); rebateQueryStr.append(rebateSelectQueryStr).append(fromQueryStr).append(whereQueryStr) .append(debitWhereQueryStr).append(" AND EGF_INSTRUMENTTYPE.TYPE = 'online'") .append(queryStrGroup); } final StringBuilder finalRevQueryStr = new StringBuilder(finalSelectQueryStr).append(revenueHeadQueryStr) .append(finalGroupQuery); final StringBuilder finalRebateQueryStr = new StringBuilder(finalSelectQueryStr).append(rebateQueryStr) .append(finalGroupQuery); final SQLQuery aggrQuery = (SQLQuery) getCurrentSession().createSQLQuery(finalRevQueryStr.toString()) .addScalar("cashCount", org.hibernate.type.StringType.INSTANCE) .addScalar("cashAmount", DoubleType.INSTANCE) .addScalar("chequeddCount", org.hibernate.type.StringType.INSTANCE) .addScalar("chequeddAmount", DoubleType.INSTANCE) .addScalar("onlineCount", org.hibernate.type.StringType.INSTANCE) .addScalar("onlineAmount", DoubleType.INSTANCE) .addScalar("source", org.hibernate.type.StringType.INSTANCE) .addScalar("glCode", org.hibernate.type.StringType.INSTANCE) .addScalar("cardAmount", DoubleType.INSTANCE) .addScalar("cardCount", org.hibernate.type.StringType.INSTANCE) .addScalar("totalReceiptCount", org.hibernate.type.StringType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(CollectionSummaryHeadWiseReport.class)); final SQLQuery rebateQuery = (SQLQuery) getCurrentSession().createSQLQuery(finalRebateQueryStr.toString()) .addScalar("cashCount", org.hibernate.type.StringType.INSTANCE) .addScalar("cashAmount", DoubleType.INSTANCE) .addScalar("chequeddCount", org.hibernate.type.StringType.INSTANCE) .addScalar("chequeddAmount", DoubleType.INSTANCE) .addScalar("onlineCount", org.hibernate.type.StringType.INSTANCE) .addScalar("onlineAmount", DoubleType.INSTANCE) .addScalar("source", org.hibernate.type.StringType.INSTANCE) .addScalar("glCode", org.hibernate.type.StringType.INSTANCE) .addScalar("cardAmount", DoubleType.INSTANCE) .addScalar("cardCount", org.hibernate.type.StringType.INSTANCE) .addScalar("totalReceiptCount", org.hibernate.type.StringType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(CollectionSummaryHeadWiseReport.class)); if (!source.isEmpty() && !source.equals(CollectionConstants.ALL)) { aggrQuery.setString("source", source); rebateQuery.setString("source", source); } if (glCode != null) { aggrQuery.setString("glCode", glCode); rebateQuery.setString("glCode", glCode); } if (status != -1) { aggrQuery.setLong("searchStatus", status); rebateQuery.setLong("searchStatus", status); } if (StringUtils.isNotBlank(paymentMode) && !paymentMode.equals(CollectionConstants.ALL)) if (paymentMode.equals(CollectionConstants.INSTRUMENTTYPE_CHEQUEORDD)) { aggrQuery.setParameterList("paymentMode", new ArrayList<>(Arrays.asList("cheque", "dd"))); rebateQuery.setParameterList("paymentMode", new ArrayList<>(Arrays.asList("cheque", "dd"))); } else { aggrQuery.setString("paymentMode", paymentMode); rebateQuery.setString("paymentMode", paymentMode); } if (branchId != null && branchId != -1) { aggrQuery.setInteger("branchId", branchId); rebateQuery.setInteger("branchId", branchId); } final List<CollectionSummaryHeadWiseReport> rebateReportResultList = populateQueryResults( rebateQuery.list()); final List<CollectionSummaryHeadWiseReport> aggrReportResults = populateQueryResults(aggrQuery.list()); final CollectionSummaryHeadWiseReportResult collResult = new CollectionSummaryHeadWiseReportResult(); if (!aggrReportResults.isEmpty()) { rebateTotal(aggrReportResults.get(0), rebateReportResultList); } collResult.setAggrCollectionSummaryReportList(aggrReportResults); collResult.setRebateCollectionSummaryReportList(rebateReportResultList); return collResult; }
From source file:org.egov.collection.service.CollectionReportService.java
License:Open Source License
public CollectionSummaryReportResult getCollectionSummaryReport(final Date fromDate, final Date toDate, final String paymentMode, final String source, final Long serviceId, final int status, final String serviceType) { final SimpleDateFormat fromDateFormatter = new SimpleDateFormat("yyyy-MM-dd 00:00:00"); final SimpleDateFormat toDateFormatter = new SimpleDateFormat("yyyy-MM-dd 23:59:59"); StringBuilder aggregateQuery = new StringBuilder(); StringBuilder userwiseQuery = new StringBuilder(); final StringBuilder finalUserwiseQuery = new StringBuilder(); final StringBuilder finalAggregateQuery = new StringBuilder(); final StringBuilder selectQuery = new StringBuilder("SELECT "); final StringBuilder fromQuery = new StringBuilder( " FROM EGCL_COLLECTIONHEADER EGCL_COLLECTIONHEADER INNER JOIN EGCL_COLLECTIONINSTRUMENT EGCL_COLLECTIONINSTRUMENT ON EGCL_COLLECTIONHEADER.ID = EGCL_COLLECTIONINSTRUMENT.COLLECTIONHEADER") .append(" INNER JOIN EGF_INSTRUMENTHEADER EGF_INSTRUMENTHEADER ON EGCL_COLLECTIONINSTRUMENT.INSTRUMENTHEADER = EGF_INSTRUMENTHEADER.ID") .append(" INNER JOIN EGW_STATUS EGW_STATUS ON EGCL_COLLECTIONHEADER.STATUS = EGW_STATUS.ID") .append(" INNER JOIN EGF_INSTRUMENTTYPE EGF_INSTRUMENTTYPE ON EGF_INSTRUMENTHEADER.INSTRUMENTTYPE = EGF_INSTRUMENTTYPE.ID") .append(" INNER JOIN EGCL_COLLECTIONMIS EGCL_COLLECTIONMIS ON EGCL_COLLECTIONHEADER.ID = EGCL_COLLECTIONMIS.COLLECTIONHEADER") .append(" INNER JOIN EGCL_SERVICEDETAILS SER ON SER.ID = EGCL_COLLECTIONHEADER.SERVICEDETAILS "); final StringBuilder whereQuery = new StringBuilder(" WHERE EGW_STATUS.DESCRIPTION != 'Cancelled'"); final StringBuilder groupQuery = new StringBuilder( " GROUP BY source, counterName, employeeName, USERID,serviceName "); aggregateQuery.append(/*w ww . j ava 2 s . c o m*/ " ,count(distinct(EGCL_COLLECTIONHEADER.ID)) as totalReceiptCount ,EGCL_COLLECTIONHEADER.SOURCE AS source, SER.NAME AS serviceName, '' AS counterName, '' AS employeeName, 0 AS USERID ") .append(fromQuery); userwiseQuery.append( " ,count(distinct(EGCL_COLLECTIONHEADER.ID)) as totalReceiptCount ,EGCL_COLLECTIONHEADER.SOURCE AS source, SER.NAME AS serviceName, EG_LOCATION.NAME AS counterName, EG_USER.NAME AS employeeName, EG_USER.ID AS USERID") .append(fromQuery) .append(" LEFT JOIN EG_LOCATION EG_LOCATION ON EGCL_COLLECTIONHEADER.LOCATION = EG_LOCATION.ID " + " INNER JOIN EG_USER EG_USER ON EGCL_COLLECTIONHEADER.CREATEDBY = EG_USER.ID "); if (fromDate != null && toDate != null) { whereQuery.append(" AND EGCL_COLLECTIONHEADER.RECEIPTDATE between to_timestamp('") .append(fromDateFormatter.format(fromDate) + "', 'YYYY-MM-DD HH24:MI:SS') and " + " to_timestamp('") .append(toDateFormatter.format(toDate) + "', 'YYYY-MM-DD HH24:MI:SS') "); } if (!source.isEmpty() && !source.equals(CollectionConstants.ALL)) { whereQuery.append(" AND EGCL_COLLECTIONHEADER.SOURCE=:source"); } else { userwiseQuery.setLength(0); userwiseQuery.append(aggregateQuery); } if (serviceId != null && serviceId != -1) whereQuery.append(" AND EGCL_COLLECTIONHEADER.SERVICEDETAILS =:serviceId"); if (status != -1) whereQuery.append(" AND EGCL_COLLECTIONHEADER.STATUS =:searchStatus"); if (!serviceType.equals(CollectionConstants.ALL)) whereQuery.append(" AND SER.SERVICETYPE =:serviceType"); if (StringUtils.isNotBlank(paymentMode) && !paymentMode.equals(CollectionConstants.ALL)) { whereQuery.append(" AND EGF_INSTRUMENTTYPE.TYPE in (:paymentMode)"); if (paymentMode.equals(CollectionConstants.INSTRUMENTTYPE_ONLINE)) { userwiseQuery.setLength(0); userwiseQuery.append(aggregateQuery); } userwiseQuery = prepareSelectQuery(paymentMode).append(userwiseQuery).append(whereQuery) .append(groupQuery); aggregateQuery = prepareSelectQuery(paymentMode).append(aggregateQuery).append(whereQuery) .append(groupQuery); } else { userwiseQuery.append(whereQuery); aggregateQuery.append(whereQuery); userwiseQuery = prepareQueryForAllPaymentMode(userwiseQuery, groupQuery); aggregateQuery = prepareQueryForAllPaymentMode(aggregateQuery, groupQuery); } final StringBuilder finalSelectQuery = new StringBuilder( "SELECT cast(sum(cashCount) AS NUMERIC) AS cashCount,cast(sum(chequeddCount) AS NUMERIC) AS chequeddCount,cast(sum(onlineCount) AS NUMERIC) AS onlineCount,source,counterName,employeeName,serviceName,cast(sum(cashAmount) AS NUMERIC) AS cashAmount, cast(sum(chequeddAmount) AS NUMERIC) AS chequeddAmount, cast(sum(onlineAmount) AS NUMERIC) AS onlineAmount ,USERID,cast(sum(bankCount) AS NUMERIC) AS bankCount, cast(sum(bankAmount) AS NUMERIC) AS bankAmount, " + " cast(sum(cardCount) AS NUMERIC) AS cardCount, cast(sum(cardAmount) AS NUMERIC) AS cardAmount, cast(sum(totalReceiptCount) AS NUMERIC) as totalReceiptCount FROM ("); final StringBuilder finalGroupQuery = new StringBuilder( " ) AS RESULT GROUP BY RESULT.source,RESULT.counterName,RESULT.employeeName,RESULT.USERID,RESULT.serviceName order by source,employeeName, serviceName "); finalUserwiseQuery.append(finalSelectQuery).append(userwiseQuery).append(finalGroupQuery); finalAggregateQuery.append(finalSelectQuery).append(aggregateQuery).append(finalGroupQuery); final SQLQuery userwiseSqluery = createSQLQuery(finalUserwiseQuery.toString()); final SQLQuery aggregateSqlQuery = createSQLQuery(finalAggregateQuery.toString()); if (!source.isEmpty() && !source.equals(CollectionConstants.ALL)) { userwiseSqluery.setString("source", source); aggregateSqlQuery.setString("source", source); } if (serviceId != null && serviceId != -1) { userwiseSqluery.setLong("serviceId", serviceId); aggregateSqlQuery.setLong("serviceId", serviceId); } if (status != -1) { userwiseSqluery.setLong("searchStatus", status); aggregateSqlQuery.setLong("searchStatus", status); } if (!serviceType.equals(CollectionConstants.ALL)) { userwiseSqluery.setString("serviceType", serviceType); aggregateSqlQuery.setString("serviceType", serviceType); } if (StringUtils.isNotBlank(paymentMode) && !paymentMode.equals(CollectionConstants.ALL)) if (paymentMode.equals(CollectionConstants.INSTRUMENTTYPE_CHEQUEORDD)) { userwiseSqluery.setParameterList("paymentMode", new ArrayList<>(Arrays.asList("cheque", "dd"))); aggregateSqlQuery.setParameterList("paymentMode", new ArrayList<>(Arrays.asList("cheque", "dd"))); } else { userwiseSqluery.setString("paymentMode", paymentMode); aggregateSqlQuery.setString("paymentMode", paymentMode); } final List<CollectionSummaryReport> reportResults = populateQueryResults(userwiseSqluery.list()); final List<CollectionSummaryReport> aggrReportResults = populateQueryResults(aggregateSqlQuery.list()); final CollectionSummaryReportResult collResult = new CollectionSummaryReportResult(); collResult.setCollectionSummaryReportList(reportResults); collResult.setAggrCollectionSummaryReportList(aggrReportResults); return collResult; }
From source file:org.egov.ptis.actions.reports.ActiveDemandReportAction.java
License:Open Source License
@ValidationErrorPage(value = NEW) public String search() { resultPage = Boolean.TRUE;//from w w w .j a v a2 s .c o m SQLQuery query = prepareQuery(); for (String key : params.keySet()) { Object value = params.get(key); if (value instanceof Collection) { query.setParameterList(key, (Collection) value); } else { query.setParameter(key, value); } } resultList = query.list(); prepareTotals(); return NEW; }
From source file:org.egov.ptis.actions.reports.EgsEduCessCollectionReportAction.java
License:Open Source License
@ValidationErrorPage(value = SRCH_FORM) public String generateReport() { LOGGER.debug("Inside generateReport method"); Map<String, String> srchParams = new HashMap<String, String>(); StringTokenizer yearTokenizer = new StringTokenizer(year, "-"); String years[] = new String[2]; int i = 0;// www . j a v a2 s. c o m while (yearTokenizer.hasMoreTokens()) { years[i++] = yearTokenizer.nextToken(); } StringBuffer qryStr = new StringBuffer("select ca.glcode, sum(ch.totalamount) " + "from EGCL_COLLECTIONDETAILS cd, egcl_collectionheader ch, chartofaccounts ca " + "where cd.id_collectionheader = ch.id " + "and ca.glcode in (:glcodeList) " + "and cd.id_accounthead = ca.id "); if (day != null && !day.equals("")) { String dateStr = new SimpleDateFormat("dd/MM/yyyy").format(day); qryStr.append("and to_char(ch.created_date, 'dd/MM/yyyy') = :day "); srchParams.put("day", dateStr); reportInfo.setDateString("Dated : " + dateStr); } else if (month != null && month != -1) { String yearStr; StringBuffer dateStr = new StringBuffer(); if (month.intValue() < APRIL) { yearStr = String.valueOf(Integer.valueOf(years[0]).intValue() + 1); } else { yearStr = years[0]; } String monthStr = StringUtils.leftPad(String.valueOf(month + 1), 2, "0"); dateStr.append(monthStr).append("/"); dateStr.append(yearStr); qryStr.append( "and to_char(ch.created_date, 'MM') || '/' || to_char(ch.created_date, 'yyyy') = :month "); srchParams.put("month", dateStr.toString()); Calendar calendar = Calendar.getInstance(); calendar.set(Integer.valueOf(yearStr), month, 01); int maxDay = calendar.getActualMaximum(DAY_OF_MONTH); int minDay = calendar.getActualMinimum(DAY_OF_MONTH); StringBuffer fromDate = new StringBuffer(); StringBuffer toDate = new StringBuffer(); fromDate.append(minDay).append("/").append(monthStr).append("/").append(yearStr); toDate.append(maxDay).append("/").append(monthStr).append("/").append(yearStr); reportInfo.setDateString("From : " + fromDate.toString() + " To : " + toDate.toString()); } else { fromDate.setYear(Integer.valueOf(years[0]).intValue()); fromDate.setMonth(APRIL); fromDate.setDate(1); toDate.setYear(Integer.valueOf(years[0]).intValue() + 1); toDate.setMonth(MARCH); toDate.setDate(31); qryStr.append( "and to_date(to_char(ch.created_date,'dd/MM/yy'),'dd/MM/yy') >= to_date(:fromDate,'dd/MM/yy') "); qryStr.append( "and to_date(to_char(ch.created_date,'dd/MM/yy'),'dd/MM/yy') <= to_date(:toDate,'dd/MM/yy') "); srchParams.put("fromDate", dateFormat.format(fromDate)); srchParams.put("toDate", dateFormat.format(toDate)); reportInfo .setDateString("From : " + dateFormat.format(fromDate) + " To : " + dateFormat.format(toDate)); } qryStr.append("group by ca.glcode"); SQLQuery qry = getPersistenceService().getSession().createSQLQuery(qryStr.toString()); qry.setParameterList("glcodeList", EDU_EGS_CESS_GLCODE_LIST); for (String srchParam : srchParams.keySet()) { qry.setParameter(srchParam, srchParams.get(srchParam)); } list = qry.list(); if (list != null && !list.isEmpty()) { ReportRequest reportInput = prepareReportData(); reportInput.setPrintDialogOnOpenReport(true); reportId = reportViewerUtil.addReportToTempCache(reportService.createReport(reportInput)); } else { recordsExist = FALSE; return SRCH_FORM; } LOGGER.debug("Exit from generateReport method"); return REPORT; }
From source file:org.egov.services.deduction.ScheduledRemittanceService.java
License:Open Source License
/** * * @param dept//from w ww. ja va2 s .co m * @param bankaccount * @return This query depends on back update of data remittancedate. If remittance date is null it will pick the amount else * it will ignore Voucher cancellation of remittance should reflect in setting remittancedate in gl to null This is taken * care. Should be maintained as same. */ private List<AutoRemittanceBean> getNonControleCodeReceiptRecoveries(final Integer dept, final int bankaccount) { final StringBuffer qry = new StringBuffer(2048); qry.append(" SELECT DISTINCT gl.id AS generalledgerId, vh.fundid AS fundId, gl.debitAmount " + " AS gldtlAmount, " + bankaccount + " AS bankAccountId " + " FROM VOUCHERHEADER vh , VOUCHERMIS mis, GENERALLEDGER gl , VOUCHERHEADER payinslip,fund f, " + " EGF_INSTRUMENTHEADER ih,EGF_INSTRUMENTOTHERDETAILS io , egcl_collectionvoucher cv,egcl_collectioninstrument ci, TDS recovery " + " WHERE recovery.GLCODEID =gl.GLCODEID AND vh.ID =gl.VOUCHERHEADERID" + " AND gl.remittanceDate IS NULL AND mis.VOUCHERHEADERID =vh.ID AND vh.STATUS =0 and vh.fundid=f.id " + " AND io.payinslipid =payinslip.id and io.instrumentheaderid=ih.id " + " and cv.voucherheaderid= vh.id and ci.collectionheaderid= cv.collectionheaderid and ci.instrumentmasterid= ih.id " + " and payinslip.status=0 AND ih.id_status NOT IN (" + " select id from egw_status where moduletype='Instrument' and description in ('" + FinancialConstants.INSTRUMENT_CANCELLED_STATUS + "','" + FinancialConstants.INSTRUMENT_SURRENDERED_STATUS + "','" + FinancialConstants.INSTRUMENT_SURRENDERED_FOR_REASSIGN_STATUS + "') )" + " AND recovery.ID =" + recovery.getId() + " AND payinslip.voucherdate >= :startdate "); if (lastRunDate != null) qry.append(" and payinslip.voucherdate <= :lastrundate"); if (receiptFundCodes != null && !receiptFundCodes.isEmpty()) qry.append(" and f.code in (:fundCodes) "); final SQLQuery query = persistenceService.getSession().createSQLQuery(qry.toString()); query.addScalar("generalledgerId", IntegerType.INSTANCE).addScalar("fundId", IntegerType.INSTANCE) .addScalar("gldtlAmount", DoubleType.INSTANCE).addScalar("bankAccountId", IntegerType.INSTANCE); if (lastRunDate != null) query.setDate("lastrundate", new java.sql.Date(lastRunDate.getTime())); if (startDate != null) query.setDate("startdate", new java.sql.Date(startDate.getTime())); if (receiptFundCodes != null && !receiptFundCodes.isEmpty()) query.setParameterList("fundCodes", receiptFundCodes); query.setResultTransformer(Transformers.aliasToBean(AutoRemittanceBean.class)); if (LOGGER.isDebugEnabled()) LOGGER.debug("ReceiptRecoveries query " + qry); return query.list(); }
From source file:org.egov.services.deduction.ScheduledRemittanceService.java
License:Open Source License
/** * updates generalledger table's remittance date to date on which remittance created for recovery * @param glIds/*from ww w . j a v a 2s . c o m*/ */ private void updateRemittancedateInLeddger(final List<Integer> glIds) { if (LOGGER.isDebugEnabled()) LOGGER.debug( "Starting updateRemittancedateInLeddger with " + glIds.size() + " glIds detailed as" + glIds); int size = glIds.size(); int suceessCount = 0; if (size <= 999) { final SQLQuery glQuery = persistenceService.getSession() .createSQLQuery("update generalledger set remittancedate=:date where id in (:glIds)"); glQuery.setDate("date", new java.sql.Date(new Date().getTime())); glQuery.setParameterList("glIds", glIds); suceessCount += glQuery.executeUpdate(); } else { // this part is incomplete int fromIndex = 0; int toIndex = 999; while (size % 1000 >= 1000) { final SQLQuery glQuery = persistenceService.getSession() .createSQLQuery("update generalledger set remittancedate=:date where id in (:glIds)"); glQuery.setDate("date", new java.sql.Date(new Date().getTime())); glQuery.setParameterList("glIds", glIds.subList(fromIndex, toIndex)); suceessCount += glQuery.executeUpdate(); fromIndex += 1000; toIndex += 1000; size -= 1000; } final SQLQuery glQuery = persistenceService.getSession() .createSQLQuery("update generalledger set remittancedate=:date where id in (:glIds)"); glQuery.setDate("date", new java.sql.Date(new Date().getTime())); glQuery.setParameterList("glIds", glIds.subList(toIndex + 1, size)); } if (LOGGER.isDebugEnabled()) LOGGER.debug("Completed updateRemittancedateInLeddger " + suceessCount); }