Example usage for org.hibernate SQLQuery setResultTransformer

List of usage examples for org.hibernate SQLQuery setResultTransformer

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. setResultTransformer

Introduction

In this page you can find the example usage for org.hibernate SQLQuery setResultTransformer.

Prototype

@Deprecated
Query<R> setResultTransformer(ResultTransformer transformer);

Source Link

Document

Set a strategy for handling the query results.

Usage

From source file:org.egov.ptis.domain.service.report.ReportService.java

License:Open Source License

@SuppressWarnings("unchecked")
@ReadOnly//from   w ww .  jav  a2 s  . c o m
public List<NatureOfUsageResult> getNatureOfUsageReportList(final HttpServletRequest request) {
    final StringBuilder query = new StringBuilder();
    query.append(
            "select distinct pi.upicno \"assessmentNumber\", pi.ownersname \"ownerName\", pi.mobileno \"mobileNumber\", pi.houseno \"doorNumber\", pi.address \"address\", cast(pi.AGGREGATE_CURRENT_FIRSTHALF_DEMAND as numeric) \"halfYearTax\" from egpt_mv_propertyInfo pi ");
    final StringBuilder whereQuery = new StringBuilder(" where pi.upicno is not null and pi.isactive = true ");
    final String natureOfUsage = request.getParameter("natureOfUsage");
    final String ward = request.getParameter("ward");
    final String block = request.getParameter(BLOCK);
    final StringBuilder srchCriteria = new StringBuilder("Total number of properties with");
    final Map<String, Object> params = new HashMap<>();
    if (StringUtils.isNotBlank(natureOfUsage) && !"-1".equals(natureOfUsage)) {
        final PropertyUsage propertyUsage = propertyUsageService.findById(Long.valueOf(natureOfUsage));
        srchCriteria.append(" Nature of usage : " + propertyUsage.getUsageName());
        query.append(",EGPT_MV_CURRENT_FLOOR_DETAIL fd ");
        whereQuery.append(" and fd.basicpropertyid = pi.basicpropertyid and fd.natureofusage = :natureOfUsage");
        params.put("natureOfUsage", propertyUsage.getUsageName());
    }
    if (StringUtils.isNotBlank(ward) && !"-1".equals(ward)) {
        final Boundary wardBndry = boundaryService.getBoundaryById(Long.valueOf(ward));
        srchCriteria.append(" Ward : " + wardBndry.getName());
        whereQuery.append(" and pi.wardid = :ward");
        params.put("ward", Long.valueOf(ward));
    }
    if (StringUtils.isNotBlank(block) && !"-1".equals(block)) {
        final Boundary blockBndry = boundaryService.getBoundaryById(Long.valueOf(block));
        srchCriteria.append(" Block : " + blockBndry.getName());
        whereQuery.append(" and pi.blockid = :block");
        params.put(BLOCK, Long.valueOf(block));
    }
    final SQLQuery sqlQuery = propertyTaxCommonUtils.getSession()
            .createSQLQuery(query.append(whereQuery).toString());
    for (final String key : params.keySet())
        sqlQuery.setParameter(key, params.get(key));
    sqlQuery.setResultTransformer(Transformers.aliasToBean(NatureOfUsageResult.class));
    final List<NatureOfUsageResult> results = sqlQuery.list();
    srchCriteria.append(" are : " + results.size());
    return results;
}

From source file:org.egov.ptis.domain.service.report.ReportService.java

License:Open Source License

@SuppressWarnings("unchecked")
@ReadOnly/*  w  ww.j  a  va 2 s . c o m*/
public List<ApartmentDCBReportResult> prepareQueryForApartmentDCBReport(final Long boundaryId,
        final String mode, final Long apartmentId) {
    final String PROPERTY = "property";
    final StringBuilder queryStr = new StringBuilder();
    final StringBuilder commonFromQry = new StringBuilder();
    final StringBuilder finalCommonQry = new StringBuilder();
    final StringBuilder finalSelectQry = new StringBuilder();
    final StringBuilder finalGrpQry = new StringBuilder();
    final StringBuilder boundaryQry = new StringBuilder();
    final StringBuilder whereQry = new StringBuilder();
    whereQry.append(" where ");
    whereQry.append(
            " pd.apartment=a.id and p.id=pd.id_property and pi.basicpropertyid=p.id_basic_property  and pi.isexempted=false and p.status in ('A','I')  and pi.isactive = true and pi.isexempted = false ");
    commonFromQry.append(
            " from egpt_mv_propertyinfo pi , egpt_apartment a,egpt_property_detail pd,egpt_property p ");
    if (boundaryId != -1 && boundaryId != null && boundaryId != 0)
        boundaryQry.append(" and pi.wardid = " + boundaryId);
    if (apartmentId != -1 && apartmentId != null && apartmentId != 0)
        whereQry.append(" and pd.apartment = " + apartmentId);
    finalCommonQry.append(" cast(COALESCE(sum(pi.ARREAR_DEMAND),0) as numeric) as \"dmndArrearPT\",");
    finalCommonQry.append(
            " cast(COALESCE(sum(pi.pen_aggr_arrear_demand),0) AS numeric) as \"dmndArrearPFT\", cast(COALESCE(sum(pi.annualdemand),0) AS numeric) as \"dmndCurrentPT\", ");
    finalCommonQry.append(
            " cast(COALESCE(sum(pi.pen_aggr_current_firsthalf_demand),0)+COALESCE(sum(pi.pen_aggr_current_secondhalf_demand),0) AS numeric) as \"dmndCurrentPFT\",");
    finalCommonQry.append(
            " cast(COALESCE(sum(pi.ARREAR_COLLECTION),0) AS numeric) as \"clctnArrearPT\", cast(COALESCE(sum(pi.pen_aggr_arr_coll),0) AS numeric) as \"clctnArrearPFT\",");
    finalCommonQry.append(" cast(COALESCE(sum(pi.annualcoll),0) AS numeric) as \"clctnCurrentPT\",");
    finalCommonQry.append(
            " cast(COALESCE(sum(pi.pen_aggr_current_firsthalf_coll),0)+COALESCE(sum(pi.pen_aggr_current_secondhalf_coll),0) AS numeric) as \"clctnCurrentPFT\"  ");
    if (!mode.equalsIgnoreCase(PROPERTY)) {
        finalSelectQry.append(
                "select count(distinct pi.upicno) as \"assessmentCount\",cast(a.id as integer) as \"apartmentId\",a.name as \"apartmentName\", ");
        finalGrpQry.append(" group by a.id,a.name order by a.name");
    } else if (mode.equalsIgnoreCase(PROPERTY)) {
        finalSelectQry.append(
                "select distinct pi.upicno as \"assessmentNo\", pi.houseno as \"houseNo\", pi.ownersname as \"ownerName\", ");
        finalGrpQry.append(" group by pi.upicno, pi.houseno, pi.ownersname order by pi.upicno ");
    }
    queryStr.append(finalSelectQry).append(finalCommonQry).append(commonFromQry).append(whereQry)
            .append(boundaryQry).append(finalGrpQry);
    final SQLQuery sqlQuery = propertyTaxCommonUtils.getSession().createSQLQuery(queryStr.toString());
    sqlQuery.setResultTransformer(new AliasToBeanResultTransformer(ApartmentDCBReportResult.class));
    return sqlQuery.list();
}

From source file:org.egov.ptis.web.controller.reports.DCBReportVLTController.java

License:Open Source License

@RequestMapping(value = "/result", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
public @ResponseBody void dcbReportSearchResult(@RequestParam final String boundaryId,
        @RequestParam final String mode, @RequestParam final String courtCase, final HttpServletRequest request,
        final HttpServletResponse response) throws IOException {
    final PropertyTypeMaster propertyTypeMaster = propertyTypeMasterDAO
            .getPropertyTypeMasterByCode(PropertyTaxConstants.OWNERSHIP_TYPE_VAC_LAND);
    final List<String> propertyType = new ArrayList<>();
    propertyType.add(propertyTypeMaster.getId().toString());
    final SQLQuery query = reportService.prepareQueryForDCBReport(Long.valueOf(boundaryId), mode,
            Boolean.valueOf(courtCase), propertyType);
    query.setResultTransformer(new AliasToBeanResultTransformer(DCBReportResult.class));
    final List<DCBReportResult> resultList = query.list();
    final String result = new StringBuilder("{ \"data\":")
            .append(toJSON(resultList, DCBReportResult.class, DCBReportHelperAdaptor.class)).append("}")
            .toString();//from  w  ww. j  av a  2  s. c o m
    IOUtils.write(result, response.getWriter());
}

From source file:org.egov.services.deduction.ScheduledRemittanceService.java

License:Open Source License

/**
 *
 * @param dept//from w  w  w.  java2s. c o  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

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();

}

From source file:org.egov.services.deduction.ScheduledRemittanceService.java

License:Open Source License

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();

}

From source file:org.egov.services.deduction.ScheduledRemittanceService.java

License:Open Source License

/**
 *
 * @param gjvBankAccountId//  www. ja va  2s .co m
 * @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();

}

From source file:org.egov.services.deduction.ScheduledRemittanceService.java

License:Open Source License

/**
 *
 * @param recoveryId Will return all voucher recoveries which are billpayment done and check also assigned
 * @param deptId// www. j  a  v  a2  s. c o m
 * @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();

}

From source file:org.egov.services.deduction.ScheduledRemittanceService.java

License:Open Source License

/**
 *
 * @param recoveryId Will return all receipt recoveries which are Remitted and approved
 * @param deptId//  w ww.ja  va 2  s . com
 * @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();

}

From source file:org.egov.tl.web.controller.DCBReportController.java

License:Open Source License

private SQLQuery prepareQuery(final String paramList, final String licensenumber, String mode,
        final String reportType) {
    StringBuilder query = new StringBuilder();
    final StringBuilder selectQry1 = new StringBuilder();
    final StringBuilder selectQry2 = new StringBuilder();
    StringBuilder fromQry = new StringBuilder();
    StringBuilder whereQry = new StringBuilder();
    final StringBuilder groupByQry = new StringBuilder();
    selectQry2.append(//  w  w w. j a va2s.co m
            "  cast(SUM(arr_demand) as bigint) AS arr_demand,cast(SUM(curr_demand) as bigint) AS curr_demand,cast(SUM(arr_coll) as bigint) AS arr_coll,cast(SUM(curr_coll) as bigint) AS curr_coll,"
                    + "cast(SUM(arr_balance) as bigint) AS arr_balance,cast(SUM(curr_balance) as bigint) AS curr_balance ");
    fromQry = new StringBuilder(" from egtl_mv_dcb_view dcbinfo,eg_boundary boundary ");

    if (mode.equalsIgnoreCase(PROPERTY)) {
        selectQry1.append(
                "select distinct dcbinfo.licenseNumber as licenseNumber ,cast(dcbinfo.licenseId as integer) as licenseid,dcbinfo.username as \"username\", ");
        fromQry = new StringBuilder(" from egtl_mv_dcb_view dcbinfo ");
        if (licensenumber != null && !"".equals(licensenumber)) {

            whereQry = whereQry.append(" where  dcbinfo.licenseNumber = '" + licensenumber.toUpperCase() + "'");
        }
        groupByQry.append("group by dcbinfo.licenseNumber,dcbinfo.licenseId,dcbinfo.username ");
        if (licensenumber != null && !"".equals(licensenumber)) {
            whereQry.append(" and ");
        } else {
            whereQry.append(" where ");
        }
        whereQry.append(" dcbinfo.licenseNumber is not null  ");
        if (paramList != null && !paramList.equalsIgnoreCase("") && reportType.equalsIgnoreCase("localityWise"))
            whereQry = whereQry.append(" and dcbinfo.locality in (" + paramList + ")");

    }

    query = selectQry1.append(selectQry2).append(fromQry).append(whereQry).append(groupByQry);
    final SQLQuery finalQuery = entityManager.unwrap(Session.class).createSQLQuery(query.toString());
    finalQuery.setResultTransformer(new AliasToBeanResultTransformer(DCBReportResult.class));
    return finalQuery;
}