Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

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

Introduction

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

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

From source file:org.egov.services.report.LoanGrantService.java

License:Open Source License

/**
 * @param subSchemeId/*from w  w  w  .  j av  a  2  s .co m*/
 * @return
 */
@SuppressWarnings("unchecked")
public List<LoanGrantBean> fundingPatternBy(final Integer subSchemeId, final Integer schemeId) {
    List<LoanGrantBean> fundingPatternList = null;
    final StringBuffer sql = new StringBuffer();
    sql.append(
            " select ss.name as subScheme, fa.name  as name ,  sum(lgd.percentage) as amount  from egf_LoanGrantDetail lgd,"
                    + "egf_LoanGrantHeader lgh,egf_fundingAgency fa,sub_scheme ss ");
    if (schemeId != null && subSchemeId == null)
        sql.append(",Scheme s ");
    sql.append(" where lgd.headerid=lgh.id and fa.id=lgd.agencyid  and ss.id=lgh.subSchemeId ");
    if (schemeId != null && subSchemeId == null)
        sql.append(" and s.id=ss.schemeid and  s.id= " + schemeId);
    else
        sql.append(" and lgh.subSchemeId=" + subSchemeId);
    sql.append(" group by");
    /*
     * if(schemeId!=null && subSchemeId==null) { sql.append(" ss.name ,"); }
     */
    sql.append(" ss.name , fa.name order by ss.name,fa.name");
    final SQLQuery patternSql = getSession().createSQLQuery(sql.toString());
    patternSql.addScalar("subScheme", StringType.INSTANCE).addScalar("name", StringType.INSTANCE)
            .addScalar("amount", BigDecimalType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(LoanGrantBean.class));
    fundingPatternList = patternSql.list();
    return fundingPatternList;
}

From source file:org.egov.services.report.LoanGrantService.java

License:Open Source License

/**
 * @param subSchemeId/*ww  w . j a  v a  2 s  .c om*/
 * @param fromDate
 * @param toDate
 * @param agencyId
 * @param faTypeId
 * @param pcTypeId
 * @param sql
 * @return
 */
private List<Object> getDataByAgency(final Integer schemeId, final Integer subSchemeId, final Date fromDate,
        final Date toDate, final Long agencyId, final Integer pcTypeId, final Integer faTypeId,
        final Integer fundId) {
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("Starting getDataByAgency for agencyId:" + agencyId);
    final StringBuffer sql = new StringBuffer();
    sql.append(
            " select * from (select distinct vh.vouchernumber as voucherNumber, gld1.amount as amount, null as agencyAmount,pc.code as code, gld1.detailkeyid as detailKey, gld1.detailtypeid detailType "
                    + "from voucherheader vh, generalledger gl1,generalledger gl2, generalledgerdetail gld1, generalledgerdetail gld2, "
                    + "egf_subscheme_project ssp,egw_projectcode pc ");
    if (subSchemeId == null)
        sql.append(",scheme s,sub_scheme ss ");
    sql.append(" where vh.id= gl1.voucherheaderid and gl1.id= gld1.generalledgerid "
            + " and gl2.id=gld2.generalledgerid and gl2.voucherheaderid=vh.id and gl1.creditamount>0 "
            + " and gl2.debitamount>0 and gld1.detailtypeid=" + pcTypeId + " and gld2.detailtypeid=" + faTypeId
            + " and ssp.projectcodeid=gld1.detailkeyid and pc.id=gld1.detailkeyid and pc.id=ssp.projectcodeid "
            + " and vh.type='Journal Voucher' and vh.name='JVGeneral' and vh.fundid=" + fundId + " ");
    if (subSchemeId != null)
        sql.append(" and ssp.subschemeId=" + subSchemeId);
    else
        sql.append(" and ss.schemeid=s.id and s.id=" + schemeId + " and ssp.subschemeid=ss.id");
    if (fromDate != null)
        sql.append(" and vh.voucherdate>='" + Constants.DD_MON_YYYYFORMAT.format(fromDate) + "' ");
    if (toDate != null)
        sql.append(" and vh.voucherdate<='" + Constants.DD_MON_YYYYFORMAT.format(toDate) + "' ");
    sql.append(" and  gld2.detailkeyid=" + agencyId);
    sql.append(" union ");

    sql.append(
            " select distinct vh.vouchernumber as voucherNumber,null as amount,gld2.amount as agencyAmount,null as code, gld2.detailKeyid as detailKey, gld2.detailtypeid detailType "
                    + "from voucherheader vh, generalledger gl1,generalledger gl2, generalledgerdetail gld1, generalledgerdetail gld2 ");
    sql.append(",egf_loangrantdetail lgd,egf_loanGrantHeader lg,egf_subscheme_project ssp ");
    // check here if u have errors
    if (subSchemeId == null)
        sql.append(",scheme s,sub_scheme ss ");
    sql.append(" where vh.id= gl1.voucherheaderid and gl1.id= gld1.generalledgerid "
            + "and gl2.id=gld2.generalledgerid and gl2.voucherheaderid=vh.id and gl1.creditamount>0 "
            + "and gl2.debitamount>0 and gld1.detailtypeid=" + pcTypeId + " and gld2.detailtypeid=" + faTypeId
            + "and vh.type='Journal Voucher' and vh.name='JVGeneral' and lg.id=lgd.headerid and ssp.projectcodeid=gld1.detailkeyid and vh.fundid="
            + fundId + " ");
    if (subSchemeId != null)
        sql.append("  and lg.subschemeId=" + subSchemeId + " and ssp.subschemeid=" + subSchemeId);
    else
        sql.append(" and ss.schemeid=s.id and s.id=" + schemeId
                + " and lg.subschemeId=ss.id and ssp.subschemeid=ss.id");
    if (fromDate != null)
        sql.append(" and vh.voucherdate>='" + Constants.DD_MON_YYYYFORMAT.format(fromDate) + "' ");
    if (toDate != null)
        sql.append(" and vh.voucherdate<='" + Constants.DD_MON_YYYYFORMAT.format(toDate) + "' ");
    sql.append(" and  gld2.detailkeyid=" + agencyId);
    if (subSchemeId != null)
        sql.append(" and lgd.agencyId= " + agencyId);
    sql.append(" ) order by  voucherNumber,detailType desc,detailKey");
    final SQLQuery gcSql = getSession().createSQLQuery(sql.toString());
    if (LOGGER.isInfoEnabled())
        LOGGER.info("sql:  " + sql.toString());
    gcSql.addScalar("voucherNumber").addScalar("code").addScalar("amount", BigDecimalType.INSTANCE)
            .addScalar("agencyAmount", BigDecimalType.INSTANCE).addScalar("detailKey", IntegerType.INSTANCE)
            .addScalar("detailType", IntegerType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(LoanGrantBean.class));
    // Grant Contribution List
    final List<Object> gcList = gcSql.list();
    if (gcList.size() > 0) {
        final List<LoanGrantBean> grantAmountList = getGrantAmountBy(schemeId, subSchemeId, agencyId);
        if (grantAmountList != null && grantAmountList.size() > 0) {
            // check if fa is used in loan header else ommit
            ((LoanGrantBean) gcList.get(0)).setAgencyName(grantAmountList.get(0).getAgencyName());
            ((LoanGrantBean) gcList.get(0)).setGrantAmount(grantAmountList.get(0).getGrantAmount());
        }

    } else {

        final List<LoanGrantBean> grantAmountList = getGrantAmountBy(schemeId, subSchemeId, agencyId);
        if (grantAmountList != null && grantAmountList.size() > 0)
            if (grantAmountList.get(0).getGrantAmount() != null
                    && grantAmountList.get(0).getGrantAmount().compareTo(BigDecimal.ZERO) != 0) {
                gcList.add(0, new LoanGrantBean());
                ((LoanGrantBean) gcList.get(0)).setAgencyName(grantAmountList.get(0).getAgencyName());
                ((LoanGrantBean) gcList.get(0)).setGrantAmount(grantAmountList.get(0).getGrantAmount());
            }
    }
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("exiting getDataByAgency ");
    return gcList;
}

From source file:org.egov.services.report.LoanGrantService.java

License:Open Source License

/**
 * @param agencyId//from  w ww  . j av  a2  s . co  m
 * @param subSchemeId
 * @param schemeId
 * @return
 */
private List<LoanGrantBean> getGrantAmountBy(final Integer schemeId, final Integer subSchemeId,
        final Long agencyId) {
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("Starting getGrantAmountBy for" + agencyId);
    final StringBuffer gaSql = new StringBuffer();
    gaSql.append(
            "select fa.name as agencyName, sum( case when lgd.grantamount = null THEN 0 else lgd.grantamount end)*100000 as grantAmount");
    gaSql.append(" from egf_loangrantheader lg, egf_loangrantdetail lgd, egf_fundingagency fa");
    if (subSchemeId == null)
        gaSql.append(", sub_scheme ss,scheme s");
    gaSql.append(" where lg.id= lgd.headerid and lgd.agencyid=fa.id ");
    if (subSchemeId == null)
        gaSql.append(" and lg.subschemeid=ss.id and ss.schemeid=s.id and s.id=" + schemeId);
    else
        gaSql.append(" and lg.subschemeid=" + subSchemeId);
    gaSql.append(" and lgd.agencyid=" + agencyId);
    gaSql.append(" group by fa.name");
    if (LOGGER.isInfoEnabled())
        LOGGER.info("GrantAmoountSql for Schemeid" + schemeId + " SubSchemeId " + subSchemeId + "  agencyId"
                + agencyId + ":" + gaSql.toString());
    final SQLQuery gaSQLQuery = getSession().createSQLQuery(gaSql.toString());
    gaSQLQuery.addScalar("agencyName").addScalar("grantAmount", BigDecimalType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(LoanGrantBean.class));
    final List<LoanGrantBean> galist = gaSQLQuery.list();
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("Exiting from  getGrantAmountBy for" + agencyId);
    return galist;

}

From source file:org.egov.services.report.LoanGrantService.java

License:Open Source License

public List<Object> getLoanByAgency(final Integer schemeId, final Long agencyId, final Integer faTypeId,
        final Integer fundId) {
    final StringBuffer sql = new StringBuffer(512);
    sql.append("SELECT DISTINCT vh.vouchernumber AS voucherNumber,  gld.amount    AS amount,  "
            + " gld.detailkeyid               AS detailKey,   gld.detailtypeid detailType ,vh.voucherdate  "
            + " FROM voucherheader vh,   vouchermis vmis,    generalledger gl,   generalledgerdetail gld  "
            + " WHERE vh.id            = gl.voucherheaderid  "
            + " AND gl.id             = gld.generalledgerid  " + " AND gl.debitamount    >0  "
            + " AND gld.detailtypeid  =  " + faTypeId + " AND vh.type            ='Payment'  "
            + " AND vh.name            ='Direct Bank Payment'  " + " and vh.status in (0,5)   "
            + " and vmis.schemeid=  " + schemeId + " and vh.fundid=" + fundId
            + " and vmis.voucherheaderid=vh.id");
    if (agencyId != null && agencyId != -1)
        sql.append(" and  gld.detailkeyid =" + agencyId);
    sql.append(" order by vh.voucherdate ");
    final SQLQuery loanSql = getSession().createSQLQuery(sql.toString());
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("getLoanByAgency sql:" + sql.toString());
    loanSql.addScalar("voucherNumber").addScalar("amount", BigDecimalType.INSTANCE)
            .addScalar("detailKey", IntegerType.INSTANCE).addScalar("detailType", IntegerType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(LoanGrantBean.class));
    final List<Object> repayedList = loanSql.list();

    final List<LoanGrantBean> loanAmountList = getLoanAmountBy(schemeId, agencyId);
    if (loanAmountList != null && loanAmountList.size() > 0)
        if (loanAmountList.get(0).getLoanAmount() != null
                && loanAmountList.get(0).getLoanAmount().compareTo(BigDecimal.ZERO) != 0) {
            repayedList.add(0, new LoanGrantBean());
            ((LoanGrantBean) repayedList.get(0)).setAgencyName(loanAmountList.get(0).getAgencyName());
            ((LoanGrantBean) repayedList.get(0)).setLoanAmount(loanAmountList.get(0).getLoanAmount());
            final BigDecimal loanPaidSoFar = getLoanPaidSoFar(schemeId, agencyId);
            ((LoanGrantBean) repayedList.get(0)).setAgencyAmount(loanPaidSoFar);
            ((LoanGrantBean) repayedList.get(0))
                    .setBalance(loanAmountList.get(0).getLoanAmount().subtract(loanPaidSoFar));
        }// w  w  w  .  ja va 2 s.  co m
    return repayedList;
}

From source file:org.egov.services.report.LoanGrantService.java

License:Open Source License

/**
 * @param schemeId//from   w w  w. ja  v  a 2s  . c  om
 * @param agencyId
 * @return
 */
private BigDecimal getLoanPaidSoFar(final Integer schemeId, final Long agencyId) {
    BigDecimal amount = BigDecimal.ZERO;
    final SQLQuery query = getSession()
            .createSQLQuery("select amount as amount from egf_loan_paid where schemeid=" + schemeId
                    + " and agencyid=" + agencyId);
    query.addScalar("amount", BigDecimalType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(LoanGrantBean.class));
    final List<LoanGrantBean> list = query.list();
    if (list != null && list.size() > 0)
        if (list.get(0).getAmount() != null)
            amount = list.get(0).getAmount();
    return amount;
}

From source file:org.egov.services.report.LoanGrantService.java

License:Open Source License

/**
 * @param schemeId/*w ww  .  j a  v a  2s.co m*/
 * @param agencyId is mandatory
 * @return
 */
private List<LoanGrantBean> getLoanAmountBy(final Integer schemeId, final Long agencyId) {

    if (LOGGER.isDebugEnabled())
        LOGGER.debug("Starting getLoanAmountBy for" + agencyId);
    final StringBuffer loanSql = new StringBuffer(256);
    loanSql.append(
            "select fa.name as agencyName, sum( case when lgd.loanamount  = null then 0 else lgd.loanamount)*100000 as loanAmount");
    loanSql.append(
            " from egf_loangrantheader lg, egf_loangrantdetail lgd, egf_fundingagency fa,sub_scheme ss,scheme s");
    loanSql.append(" where lg.id= lgd.headerid and lgd.agencyid=fa.id ");
    loanSql.append(" and lgd.agencyid=" + agencyId);
    loanSql.append(" and lg.subSchemeId=ss.id");
    loanSql.append(" and s.id=" + schemeId);
    loanSql.append(" and s.id=ss.schemeid ");
    loanSql.append(" group by fa.name");
    if (LOGGER.isInfoEnabled())
        LOGGER.info(
                "GrantAmoountSql for Schemeid" + schemeId + "  agencyId" + agencyId + ":" + loanSql.toString());
    final SQLQuery gaSQLQuery = getSession().createSQLQuery(loanSql.toString());
    gaSQLQuery.addScalar("agencyName").addScalar("loanAmount", BigDecimalType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(LoanGrantBean.class));
    final List<LoanGrantBean> galist = gaSQLQuery.list();
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("Exiting from  getLoanAmountBy for" + agencyId);
    return galist;

}

From source file:org.gbif.portal.dao.geospatial.impl.hibernate.CellDensityDAOImpl.java

License:Open Source License

/**
 * TODO refactor into HQL/*w  ww .j a v  a  2 s.  c om*/
 * 
 * @see org.gbif.portal.dao.geospatial.CellDensityDAO#getTotalsPerCountry(org.gbif.portal.model.ModelEntityType, java.lang.Long)
 */
@SuppressWarnings("unchecked")
public List<Object[]> getTotalsPerCountry(final ModelEntityType met, final Long entityId) {
    return (List<Object[]>) getHibernateTemplate().execute(new HibernateCallback() {
        public Object doInHibernate(Session session) {
            SQLQuery query = session.createSQLQuery("select cc.iso_country_code the_iso_country_code, "
                    + "cc.iso_country_code the_iso_country_code,"
                    + "sum(count) total_count from cell_density cd "
                    + "inner join cell_country cc on cd.cell_id=cc.cell_id "
                    + "where cd.type=:type and cd.entity_id=:entityId " + "group by iso_country_code "
                    + "order by total_count desc");
            query.setParameter("type", met.getValue());
            query.setParameter("entityId", entityId);
            query.addScalar("the_iso_country_code", Hibernate.STRING);
            query.addScalar("total_count", Hibernate.INTEGER);
            return query.list();
        }
    });
}

From source file:org.gbif.portal.dao.geospatial.impl.hibernate.CellDensityDAOImpl.java

License:Open Source License

/**
 * TODO refactor into HQL/*  w w  w . j  av a  2s  .c o  m*/
 * 
 * @see org.gbif.portal.dao.geospatial.CellDensityDAO#getTotalsPerRegion(org.gbif.portal.model.ModelEntityType, java.lang.Long)
 */
@SuppressWarnings("unchecked")
public List<Object[]> getTotalsPerRegion(final ModelEntityType met, final Long entityId) {
    return (List<Object[]>) getHibernateTemplate().execute(new HibernateCallback() {
        public Object doInHibernate(Session session) {
            SQLQuery query = session.createSQLQuery(
                    "select 0, co.region the_region, sum(count) total_count from cell_density cd "
                            + "inner join cell_country cc on cd.cell_id=cc.cell_id "
                            + "inner join country co on co.iso_country_code = cc.iso_country_code "
                            + "where cd.type=:type and cd.entity_id=:entityId group by region order by total_count desc");
            query.setParameter("type", met.getValue());
            query.setParameter("entityId", entityId);
            query.addScalar("the_region", Hibernate.STRING);
            query.addScalar("total_count", Hibernate.INTEGER);
            return query.list();
        }
    });
}

From source file:org.gbif.portal.dao.geospatial.impl.hibernate.CountryDAOImpl.java

License:Open Source License

/**
 * @see org.gbif.portal.dao.resources.DataResourceDAO#getCountryCountsForCountry(java.lang.Long)
 */// w ww.j a  va2  s. c  om
@SuppressWarnings("unchecked")
public List<Object[]> getCountryCountsForCountry(final String isoCountryCode, final boolean geoRefOnly,
        final Locale locale) {
    HibernateTemplate template = getHibernateTemplate();
    return (List) template.execute(new HibernateCallback() {

        public Object doInHibernate(Session session) {
            StringBuffer sb = new StringBuffer(
                    "select dp.iso_country_code as the_iso_country_code, cn.name as cn_name,");
            if (geoRefOnly) {
                sb.append("  sum(rc.occurrence_coordinate_count) as the_count from resource_country rc"
                        + " inner join data_resource dr on rc.data_resource_id=dr.id "
                        + " inner join data_provider dp on dr.data_provider_id=dp.id "
                        + " inner join country_name cn on dp.iso_country_code=cn.iso_country_code" + " where ");
            } else {
                sb.append("  sum(rc.count) as the_count from resource_country rc"
                        + " inner join data_resource dr on rc.data_resource_id=dr.id "
                        + " inner join data_provider dp on dr.data_provider_id=dp.id "
                        + " inner join country_name cn on dp.iso_country_code=cn.iso_country_code" + " where ");
            }
            if (isoCountryCode != null) {
                sb.append(" rc.iso_country_code=:isoCountryCode and cn.locale=:locale");
            }

            if (geoRefOnly) {
                sb.append(" and rc.occurrence_coordinate_count>0");
            }

            sb.append(" and dr.deleted is null");

            sb.append(" group by cn_name");
            SQLQuery query = session.createSQLQuery(sb.toString());
            if (isoCountryCode != null) {
                query.setParameter("isoCountryCode", isoCountryCode);
                query.setParameter("locale", getLocaleForQuery(locale));
                query.addScalar("the_iso_country_code", Hibernate.STRING);
                query.addScalar("cn_name", Hibernate.STRING);
                query.addScalar("the_count", Hibernate.INTEGER);
            }
            query.setCacheable(true);
            logger.debug("query is: " + sb.toString());
            return query.list();
        }
    });
}

From source file:org.gbif.portal.dao.geospatial.impl.hibernate.CountryDAOImpl.java

License:Open Source License

/**
 * @see org.gbif.portal.dao.taxonomy.TaxonConceptDAO#getCountryCountsForTaxonConcept(java.lang.Long)
 *///w  w  w.  j ava  2s  . c  o  m
@SuppressWarnings("unchecked")
public List<Object[]> getCountryCountsForTaxonConcept(final long taxonConceptId, final Locale locale) {
    HibernateTemplate template = getHibernateTemplate();
    return (List) template.execute(new HibernateCallback() {

        public Object doInHibernate(Session session) {
            SQLQuery query = session.createSQLQuery("select tc.iso_country_code the_iso_country_code, "
                    + " tc.iso_country_code the_iso_country_code2, cn.name as cn_name, tc.count as the_count from taxon_country tc"
                    + " inner join country_name cn on tc.iso_country_code=cn.iso_country_code"
                    + " where tc.taxon_concept_id=:taxonConceptId and cn.locale=:locale order by cn_name");
            query.setParameter("taxonConceptId", taxonConceptId);
            query.setParameter("locale", getLocaleForQuery(locale));
            query.addScalar("the_iso_country_code", Hibernate.STRING);
            query.addScalar("the_iso_country_code2", Hibernate.STRING);
            query.addScalar("the_count", Hibernate.INTEGER);
            query.setCacheable(true);
            return query.list();
        }
    });
}