Example usage for org.hibernate.type StringType INSTANCE

Introduction

In this page you can find the example usage for org.hibernate.type StringType INSTANCE.

Prototype

StringType INSTANCE

To view the source code for org.hibernate.type StringType INSTANCE.

Click Source Link

Usage

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

License:Open Source License

/**
 *
 * @param reportSearch/*from  w  w w. ja  v  a2s.co 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/*from   w  w  w  .j a v  a2s  .co 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.FunctionwiseIEService.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<CommonReportBean> getAmountListForMinorCode(final FunctionwiseIE functionwiseIE,
        final ReportSearch reportSearch) throws ApplicationException, ParseException {

    String sql = "";
    Query query = null;/*  w  ww  . j  a v a 2  s.c  o  m*/

    if (reportSearch.getByDetailCode()) {
        if (reportSearch.getIncExp().equalsIgnoreCase("E"))
            sql = "SELECT SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen()
                    + ") as accCode,d.dept_name as deptName ,(SUM(gl.debitamount)-SUM(gl.creditamount)) AS amount,0 as isMajor ";
        else
            sql = "SELECT SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen()
                    + ") as accCode,d.dept_name as deptName ,(SUM(gl.creditamount)-SUM(gl.debitamount)) AS amount,0 as isMajor ";

        if (reportSearch.getIncExp().equalsIgnoreCase("E"))
            sql = sql
                    + " FROM GENERALLEDGER gl,FUNCTION fn,VOUCHERHEADER vh, CHARTOFACCOUNTS coa,vouchermis vmis,eg_department d  "
                    + " WHERE vh.id=vmis.voucherheaderid and vh.ID=gl.voucherheaderid AND SUBSTR(gl.glcode,1,"
                    + reportSearch.getMinorCodeLen() + ")=coa.glcode AND (coa.TYPE='" + reportSearch.getIncExp()
                    + "' " + capExpCodeCond + ")"
                    + " and d.dept_name=:deptName and coa.glcode like :glcode AND fn.id = gl.functionid  and vmis.departmentid=d.id_dept "
                    + getFilterQueryVoucherAsOnDate(reportSearch) + getFilterQueryGL(reportSearch)
                    + " GROUP BY  SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen() + "),d.dept_name ";
        else
            sql = sql
                    + " FROM GENERALLEDGER gl,FUNCTION fn,VOUCHERHEADER vh, CHARTOFACCOUNTS coa,vouchermis vmis,eg_department d  "
                    + " WHERE vh.id=vmis.voucherheaderid and vh.ID=gl.voucherheaderid AND SUBSTR(gl.glcode,1,"
                    + reportSearch.getMinorCodeLen() + ")=coa.glcode AND coa.TYPE='" + reportSearch.getIncExp()
                    + "' "
                    + " and d.dept_name=:deptName and coa.glcode like :glcode AND fn.id = gl.functionid  and vmis.departmentid=d.id_dept "
                    + getFilterQueryVoucherAsOnDate(reportSearch) + getFilterQueryGL(reportSearch)
                    + " GROUP BY  SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen() + "),d.dept_name ";
        sql = sql + "order by 2,1 ";
        query = persistenceService.getSession().createSQLQuery(sql).addScalar("accCode", StringType.INSTANCE)
                .addScalar("amount", BigDecimalType.INSTANCE).addScalar("isMajor", BooleanType.INSTANCE)
                .addScalar("deptName", StringType.INSTANCE).setString("glcode", reportSearch.getGlcode() + "%")
                .setString("deptName", reportSearch.getDepartment().getName())
                .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class));
    } else if (reportSearch.getByDepartment()) {
        if (reportSearch.getIncExp().equalsIgnoreCase("E"))
            sql = "SELECT SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen()
                    + ") as accCode,d.dept_name as deptName ,(SUM(gl.debitamount)-SUM(gl.creditamount)) AS amount,0 as isMajor ";
        else
            sql = "SELECT SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen()
                    + ") as accCode,d.dept_name as deptName ,(SUM(gl.creditamount)-SUM(gl.debitamount)) AS amount,0 as isMajor ";

        if (reportSearch.getIncExp().equalsIgnoreCase("E"))
            sql = sql
                    + " FROM GENERALLEDGER gl,FUNCTION fn,VOUCHERHEADER vh, CHARTOFACCOUNTS coa,vouchermis vmis,eg_department d  "
                    + " WHERE vh.id=vmis.voucherheaderid and vh.ID=gl.voucherheaderid AND SUBSTR(gl.glcode,1,"
                    + reportSearch.getMinorCodeLen() + ")=coa.glcode AND (coa.TYPE='" + reportSearch.getIncExp()
                    + "'" + capExpCodeCond + ")"
                    + " AND fn.id = gl.functionid  and vmis.departmentid=d.id_dept "
                    + getFilterQueryVoucherAsOnDate(reportSearch) + getFilterQueryGL(reportSearch)
                    + " GROUP BY  SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen() + "),d.dept_name ";
        else
            sql = sql
                    + " FROM GENERALLEDGER gl,FUNCTION fn,VOUCHERHEADER vh, CHARTOFACCOUNTS coa,vouchermis vmis,eg_department d  "
                    + " WHERE vh.id=vmis.voucherheaderid and vh.ID=gl.voucherheaderid AND SUBSTR(gl.glcode,1,"
                    + reportSearch.getMinorCodeLen() + ")=coa.glcode AND coa.TYPE='" + reportSearch.getIncExp()
                    + "' " + " AND fn.id = gl.functionid  and vmis.departmentid=d.id_dept "
                    + getFilterQueryVoucherAsOnDate(reportSearch) + getFilterQueryGL(reportSearch)
                    + " GROUP BY  SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen() + "),d.dept_name ";
        sql = sql + "order by 2,1 ";
        query = persistenceService.getSession().createSQLQuery(sql).addScalar("accCode", StringType.INSTANCE)
                .addScalar("amount", BigDecimalType.INSTANCE).addScalar("isMajor", BooleanType.INSTANCE)
                .addScalar("deptName", StringType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class));

    } else {
        if (reportSearch.getIncExp().equalsIgnoreCase("E"))

            sql = "SELECT coa.majorcode as accCode,coa.name as accName,(SUM(gl.debitamount)-SUM(gl.creditamount)) AS amount,1 as isMajor ";
        else
            sql = "SELECT coa.majorcode as accCode,coa.name as accName,(SUM(gl.creditamount)-SUM(gl.debitamount)) AS amount,1 as isMajor ";

        if (reportSearch.getIncExp().equalsIgnoreCase("E"))
            sql = sql
                    + " FROM GENERALLEDGER gl,FUNCTION fn,VOUCHERHEADER vh, CHARTOFACCOUNTS coa,vouchermis vmis  "
                    + " WHERE vh.id=vmis.voucherheaderid and vh.ID=gl.voucherheaderid AND SUBSTR(gl.glcode,1,"
                    + reportSearch.getMajorCodeLen() + ")=coa.glcode AND (coa.TYPE='" + reportSearch.getIncExp()
                    + "'" + capExpCodeCond + ")" + " AND fn.id = gl.functionid "
                    + getFilterQueryVoucherAsOnDate(reportSearch) + getFilterQueryGL(reportSearch)
                    + " GROUP BY coa.majorcode,coa.name ";
        else
            sql = sql
                    + " FROM GENERALLEDGER gl,FUNCTION fn,VOUCHERHEADER vh, CHARTOFACCOUNTS coa,vouchermis vmis  "
                    + " WHERE vh.id=vmis.voucherheaderid and vh.ID=gl.voucherheaderid AND SUBSTR(gl.glcode,1,"
                    + reportSearch.getMajorCodeLen() + ")=coa.glcode AND coa.TYPE='" + reportSearch.getIncExp()
                    + "' " + " AND fn.id = gl.functionid " + getFilterQueryVoucherAsOnDate(reportSearch)
                    + getFilterQueryGL(reportSearch) + " GROUP BY coa.majorcode,coa.name ";
        if (reportSearch.getIncExp().equalsIgnoreCase("E"))
            sql = sql + " Union SELECT SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen()
                    + ") as accCode, coa.name  as accName,(SUM(gl.debitamount)-SUM(gl.creditamount)) AS amount ,0 as isMajor ";
        else
            sql = sql + " Union SELECT SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen()
                    + ") as accCode, coa.name  as accName,(SUM(gl.creditamount)-SUM(gl.debitamount)) AS amount ,0 as isMajor ";

        if (reportSearch.getIncExp().equalsIgnoreCase("E"))
            sql = sql
                    + " FROM GENERALLEDGER gl,FUNCTION fn,VOUCHERHEADER vh, CHARTOFACCOUNTS coa,vouchermis vmis "
                    + " WHERE vh.id=vmis.voucherheaderid and vh.ID=gl.voucherheaderid AND SUBSTR(gl.glcode,1,"
                    + reportSearch.getMinorCodeLen() + ")=coa.glcode AND (coa.TYPE='" + reportSearch.getIncExp()
                    + "'" + capExpCodeCond + ")" + " AND fn.id = gl.functionid "
                    + getFilterQueryVoucherAsOnDate(reportSearch) + getFilterQueryGL(reportSearch)
                    + " GROUP BY SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen()
                    + "),coa.name order by 1,2 ";
        else
            sql = sql
                    + " FROM GENERALLEDGER gl,FUNCTION fn,VOUCHERHEADER vh, CHARTOFACCOUNTS coa,vouchermis vmis "
                    + " WHERE vh.id=vmis.voucherheaderid and vh.ID=gl.voucherheaderid AND SUBSTR(gl.glcode,1,"
                    + reportSearch.getMinorCodeLen() + ")=coa.glcode AND coa.TYPE='" + reportSearch.getIncExp()
                    + "' " + " AND fn.id = gl.functionid " + getFilterQueryVoucherAsOnDate(reportSearch)
                    + getFilterQueryGL(reportSearch) + " GROUP BY SUBSTR(coa.glcode,1,"
                    + reportSearch.getMinorCodeLen() + "),coa.name order by 1,2 ";
        query = persistenceService.getSession().createSQLQuery(sql).addScalar("accCode", StringType.INSTANCE)
                .addScalar("amount", BigDecimalType.INSTANCE).addScalar("isMajor", BooleanType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class));
    }

    if (LOGGER.isDebugEnabled())
        LOGGER.debug("sql===" + sql);
    final List<CommonReportBean> list = query.list();
    return list;

}

---

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

License:Open Source License

@SuppressWarnings("unchecked")
public List<CommonReportBean> getPreviousYearAmountListForMinorCode(final FunctionwiseIE functionwiseIE,
        final ReportSearch reportSearch) throws ApplicationException, ParseException {

    String sql = "";
    Query query = null;/*from w ww.  j  av a  2s  .c  o m*/

    if (reportSearch.getByDetailCode()) {
        if (reportSearch.getIncExp().equalsIgnoreCase("E"))
            sql = "SELECT SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen()
                    + ") as accCode,d.dept_name as deptName ,(SUM(gl.debitamount)-SUM(gl.creditamount)) AS amount,0 as isMajor ";
        else
            sql = "SELECT SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen()
                    + ") as accCode,d.dept_name as deptName ,(SUM(gl.creditamount)-SUM(gl.debitamount)) AS amount,0 as isMajor ";

        sql = sql
                + " FROM GENERALLEDGER gl,FUNCTION fn,VOUCHERHEADER vh, CHARTOFACCOUNTS coa,vouchermis vmis,eg_department d  "
                + " WHERE vh.id=vmis.voucherheaderid and vh.ID=gl.voucherheaderid AND SUBSTR(gl.glcode,1,"
                + reportSearch.getMinorCodeLen() + ")=coa.glcode AND coa.TYPE='" + reportSearch.getIncExp()
                + "' "
                + " and d.dept_name=:deptName and coa.glcode like :glcode AND fn.id = gl.functionid  and vmis.departmentid=d.id_dept "
                + getFilterQueryVoucherAsOnPreviousYearDate(reportSearch) + getFilterQueryGL(reportSearch)
                + " GROUP BY  SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen() + "),d.dept_name ";
        sql = sql + "order by 2,1 ";
        query = persistenceService.getSession().createSQLQuery(sql).addScalar("accCode", StringType.INSTANCE)
                .addScalar("amount", BigDecimalType.INSTANCE).addScalar("isMajor", BooleanType.INSTANCE)
                .addScalar("deptName", StringType.INSTANCE).setString("glcode", reportSearch.getGlcode() + "%")
                .setString("deptName", reportSearch.getDepartment().getName())
                .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class));
    } else if (reportSearch.getByDepartment()) {
        if (reportSearch.getIncExp().equalsIgnoreCase("E"))
            sql = "SELECT SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen()
                    + ") as accCode,d.dept_name as deptName ,(SUM(gl.debitamount)-SUM(gl.creditamount)) AS amount,0 as isMajor ";
        else
            sql = "SELECT SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen()
                    + ") as accCode,d.dept_name as deptName ,(SUM(gl.creditamount)-SUM(gl.debitamount)) AS amount,0 as isMajor ";

        sql = sql
                + " FROM GENERALLEDGER gl,FUNCTION fn,VOUCHERHEADER vh, CHARTOFACCOUNTS coa,vouchermis vmis,eg_department d  "
                + " WHERE vh.id=vmis.voucherheaderid and vh.ID=gl.voucherheaderid AND SUBSTR(gl.glcode,1,"
                + reportSearch.getMinorCodeLen() + ")=coa.glcode AND coa.TYPE='" + reportSearch.getIncExp()
                + "' " + " AND fn.id = gl.functionid  and vmis.departmentid=d.id_dept "
                + getFilterQueryVoucherAsOnPreviousYearDate(reportSearch) + getFilterQueryGL(reportSearch)
                + " GROUP BY  SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen() + "),d.dept_name ";
        sql = sql + "order by 2,1 ";
        query = persistenceService.getSession().createSQLQuery(sql).addScalar("accCode", StringType.INSTANCE)
                .addScalar("amount", BigDecimalType.INSTANCE).addScalar("isMajor", BooleanType.INSTANCE)
                .addScalar("deptName", StringType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class));

    } else {
        if (reportSearch.getIncExp().equalsIgnoreCase("E"))

            sql = "SELECT coa.majorcode as accCode,coa.name as accName,(SUM(gl.debitamount)-SUM(gl.creditamount)) AS amount,1 as isMajor ";
        else
            sql = "SELECT coa.majorcode as accCode,coa.name as accName,(SUM(gl.creditamount)-SUM(gl.debitamount)) AS amount,1 as isMajor ";
        sql = sql + " FROM GENERALLEDGER gl,FUNCTION fn,VOUCHERHEADER vh, CHARTOFACCOUNTS coa,vouchermis vmis  "
                + " WHERE vh.id=vmis.voucherheaderid and vh.ID=gl.voucherheaderid AND SUBSTR(gl.glcode,1,"
                + reportSearch.getMajorCodeLen() + ")=coa.glcode AND coa.TYPE='" + reportSearch.getIncExp()
                + "' " + " AND fn.id = gl.functionid " + getFilterQueryVoucherAsOnPreviousYearDate(reportSearch)
                + getFilterQueryGL(reportSearch) + " GROUP BY coa.majorcode,coa.name ";
        if (reportSearch.getIncExp().equalsIgnoreCase("E"))
            sql = sql + " Union SELECT SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen()
                    + ") as accCode, coa.name  as accName,(SUM(gl.debitamount)-SUM(gl.creditamount)) AS amount ,0 as isMajor ";
        else
            sql = sql + " Union SELECT SUBSTR(coa.glcode,1," + reportSearch.getMinorCodeLen()
                    + ") as accCode, coa.name  as accName,(SUM(gl.creditamount)-SUM(gl.debitamount)) AS amount ,0 as isMajor ";

        sql = sql + " FROM GENERALLEDGER gl,FUNCTION fn,VOUCHERHEADER vh, CHARTOFACCOUNTS coa,vouchermis vmis "
                + " WHERE vh.id=vmis.voucherheaderid and vh.ID=gl.voucherheaderid AND SUBSTR(gl.glcode,1,"
                + reportSearch.getMinorCodeLen() + ")=coa.glcode AND coa.TYPE='" + reportSearch.getIncExp()
                + "' " + " AND fn.id = gl.functionid " + getFilterQueryVoucherAsOnPreviousYearDate(reportSearch)
                + getFilterQueryGL(reportSearch) + " GROUP BY SUBSTR(coa.glcode,1,"
                + reportSearch.getMinorCodeLen() + "),coa.name order by 1,2 ";
        query = persistenceService.getSession().createSQLQuery(sql).addScalar("accCode", StringType.INSTANCE)
                .addScalar("amount", BigDecimalType.INSTANCE).addScalar("isMajor", BooleanType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class));
    }

    if (LOGGER.isDebugEnabled())
        LOGGER.debug("sql===" + sql);
    final List<CommonReportBean> list = query.list();
    return list;

}

---

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

License:Open Source License

private List<CommonReportBean> getBudgetApprAmountListForMinorCode(final ReportSearch reportSearch,
        final String isBeRe, final String queryStr) {
    Query query = null;//from ww  w.j  a  v a  2s. com
    if (reportSearch.getAsOnDate().getMonth() == 2 && reportSearch.getAsOnDate().getDate() == 31) {
        if (reportSearch.getByDepartment()) {
            query = persistenceService.getSession().createSQLQuery(queryStr)
                    .addScalar("accCode", StringType.INSTANCE).addScalar("amount", BigDecimalType.INSTANCE)
                    .addScalar("isMajor", BooleanType.INSTANCE).addScalar("deptName", StringType.INSTANCE)
                    .setString("isBeRe", isBeRe).setLong("finYearId", reportSearch.getFinYearId())
                    .setInteger("fundId", reportSearch.getFund().getId())
                    .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class));

            if (reportSearch.getFunction() != null && reportSearch.getFunction().getId() != null
                    && reportSearch.getFunction().getId() != -1)
                query.setLong("functionId", reportSearch.getFunction().getId());
            if (reportSearch.getByDetailCode()) {
                query.setString("deptName", reportSearch.getDepartment().getName());
                query.setString("glcode", reportSearch.getGlcode() + "%");
            } else
                query.setLong("FIEscheduleId", reportSearch.getFIEscheduleId());
        } else {
            query = persistenceService.getSession().createSQLQuery(queryStr)
                    .addScalar("accCode", StringType.INSTANCE).addScalar("amount", BigDecimalType.INSTANCE)
                    .addScalar("isMajor", BooleanType.INSTANCE).setString("isBeRe", isBeRe)
                    .setLong("finYearId", reportSearch.getFinYearId())
                    .setInteger("fundId", reportSearch.getFund().getId())
                    .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class));
            if (reportSearch.getFunction() != null && reportSearch.getFunction().getId() != null
                    && reportSearch.getFunction().getId() != -1)
                query.setLong("functionId", reportSearch.getFunction().getId());

        }
    } else if (reportSearch.getByDepartment()) {
        query = persistenceService.getSession().createSQLQuery(queryStr)
                .addScalar("accCode", StringType.INSTANCE).addScalar("amount", BigDecimalType.INSTANCE)
                .addScalar("isMajor", BooleanType.INSTANCE).addScalar("deptName", StringType.INSTANCE)
                .setString("isBeRe", isBeRe).setDate("asOnDate", reportSearch.getAsOnDate())
                .setLong("finYearId", reportSearch.getFinYearId())
                .setInteger("fundId", reportSearch.getFund().getId())
                .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class));

        if (reportSearch.getFunction() != null && reportSearch.getFunction().getId() != null
                && reportSearch.getFunction().getId() != -1)
            query.setLong("functionId", reportSearch.getFunction().getId());
        if (reportSearch.getByDetailCode()) {
            query.setString("deptName", reportSearch.getDepartment().getName());
            query.setString("glcode", reportSearch.getGlcode() + "%");
        } else
            query.setLong("FIEscheduleId", reportSearch.getFIEscheduleId());
    } else {
        query = persistenceService.getSession().createSQLQuery(queryStr)
                .addScalar("accCode", StringType.INSTANCE).addScalar("amount", BigDecimalType.INSTANCE)
                .addScalar("isMajor", BooleanType.INSTANCE).setString("isBeRe", isBeRe)
                .setDate("asOnDate", reportSearch.getAsOnDate())
                .setLong("finYearId", reportSearch.getFinYearId())
                .setInteger("fundId", reportSearch.getFund().getId())
                .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class));
        if (reportSearch.getFunction() != null && reportSearch.getFunction().getId() != null
                && reportSearch.getFunction().getId() != -1)
            query.setLong("functionId", reportSearch.getFunction().getId());

    }
    return query.list();

}

---

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

License:Open Source License

@SuppressWarnings("unchecked")
private List<CommonReportBean> getBudgetAmountListForMinorCode(final ReportSearch reportSearch,
        final String isBeRe, final String queryStr) {

    Query query = null;/*from  w w  w.j a v a 2  s  .c o  m*/
    if (reportSearch.getByDepartment()) {
        query = persistenceService.getSession().createSQLQuery(queryStr)
                .addScalar("accCode", StringType.INSTANCE).addScalar("amount", BigDecimalType.INSTANCE)
                .addScalar("isMajor", BooleanType.INSTANCE).addScalar("deptName", StringType.INSTANCE)
                .setString("isBeRe", isBeRe).setDate("asOnDate", reportSearch.getAsOnDate())
                .setLong("finYearId", reportSearch.getFinYearId())
                .setInteger("fundId", reportSearch.getFund().getId())

                .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class));
        if (reportSearch.getFunction() != null && reportSearch.getFunction().getId() != null
                && reportSearch.getFunction().getId() != -1)
            query.setLong("functionId", reportSearch.getFunction().getId());
        if (reportSearch.getByDetailCode()) {
            query.setString("deptName", reportSearch.getDepartment().getName());
            query.setString("glcode", reportSearch.getGlcode() + "%");
        } else
            query.setLong("FIEscheduleId", reportSearch.getFIEscheduleId());
    } else {
        query = persistenceService.getSession().createSQLQuery(queryStr)
                .addScalar("accCode", StringType.INSTANCE).addScalar("amount", BigDecimalType.INSTANCE)
                .addScalar("isMajor", BooleanType.INSTANCE).setString("isBeRe", isBeRe)
                .setDate("asOnDate", reportSearch.getAsOnDate())
                .setLong("finYearId", reportSearch.getFinYearId())
                .setInteger("fundId", reportSearch.getFund().getId())
                .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class));
        if (reportSearch.getFunction() != null && reportSearch.getFunction().getId() != null
                && reportSearch.getFunction().getId() != -1)
            query.setLong("functionId", reportSearch.getFunction().getId());
    }

    return query.list();
}

---

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

License:Open Source License

/**
 * @param subSchemeId//  w  w  w.j a  va  2 s.  c  o 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.works.master.service.ContractorService.java

License:Open Source License

public void searchContractor(final Map<String, Object> criteriaMap) {
    if (logger.isDebugEnabled())
        logger.debug("Inside searchContractor");
    final String contractorName = (String) criteriaMap.get(WorksConstants.CONTRACTOR_NAME);
    final String contractorCode = (String) criteriaMap.get(WorksConstants.CONTRACTOR_CODE);
    final Long departmentId = (Long) criteriaMap.get(WorksConstants.DEPARTMENT_ID);
    final Long gradeId = (Long) criteriaMap.get(WorksConstants.GRADE_ID);
    final Date searchDate = (Date) criteriaMap.get(WorksConstants.SEARCH_DATE);
    final List<AppConfigValues> configList = worksService.getAppConfigValue("Works", "CONTRACTOR_STATUS");
    final String status = configList.get(0).getValue();

    final Criteria criteria = getSession().createCriteria(Contractor.class);
    if (org.apache.commons.lang.StringUtils.isNotEmpty(contractorCode))
        criteria.add(Restrictions.sqlRestriction("lower({alias}.code) like lower(?)",
                "%" + contractorCode.trim() + "%", StringType.INSTANCE));

    if (org.apache.commons.lang.StringUtils.isNotEmpty(contractorName))
        criteria.add(Restrictions.sqlRestriction("lower({alias}.name) like lower(?)",
                "%" + contractorName.trim() + "%", StringType.INSTANCE));

    criteria.createAlias("contractorDetails", "detail").createAlias("detail.status", "status");
    criteria.add(Restrictions.eq("status.description", status));
    if (departmentId != null)
        criteria.add(Restrictions.eq("detail.department.id", departmentId));

    if (gradeId != null)
        criteria.add(Restrictions.eq("detail.grade.id", gradeId));

    if (searchDate != null)
        criteria.add(Restrictions.le("detail.validity.startDate", searchDate))
                .add(Restrictions.or(Restrictions.ge("detail.validity.endDate", searchDate),
                        Restrictions.isNull("detail.validity.endDate")));

    criteria.addOrder(Order.asc("name"));
    criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
    criteria.list();//  www  .  j  ava2s. c om
}

---

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;// w  w w . j a va  2s .  c om
    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;//from  w  ww  .ja v a 2s.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();

}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• »