List of usage examples for org.hibernate.type BooleanType INSTANCE
BooleanType INSTANCE
To view the source code for org.hibernate.type BooleanType INSTANCE.
Click Source Link
From source file:com.timesoft.kaitoo.ws.common.CoreContent.java
public static void fildHibernateParameter(final SQLQuery sqlQuery, final Map<String, Object> paramMap) throws HibernateException { Set<String> set = paramMap.keySet(); for (Iterator<String> iter = set.iterator(); iter.hasNext();) { String paramName = iter.next(); Object paramValue = paramMap.get(paramName); if (paramValue != null) { if (paramValue instanceof java.lang.String) { sqlQuery.setParameter(paramName, paramValue.toString().trim(), StringType.INSTANCE); } else if (paramValue instanceof java.lang.Character) { sqlQuery.setParameter(paramName, paramValue, CharacterType.INSTANCE); } else if (paramValue instanceof java.lang.Integer) { sqlQuery.setParameter(paramName, paramValue, IntegerType.INSTANCE); } else if (paramValue instanceof java.util.Date) { sqlQuery.setParameter(paramName, paramValue, DateType.INSTANCE); } else if (paramValue instanceof java.lang.Long) { sqlQuery.setParameter(paramName, paramValue, LongType.INSTANCE); } else if (paramValue instanceof java.sql.Timestamp) { sqlQuery.setParameter(paramName, paramValue, TimestampType.INSTANCE); } else if (paramValue instanceof java.lang.Boolean) { sqlQuery.setParameter(paramName, paramValue, BooleanType.INSTANCE); } else if (paramValue instanceof java.lang.Float) { sqlQuery.setParameter(paramName, paramValue, FloatType.INSTANCE); } else if (paramValue instanceof java.lang.Double) { sqlQuery.setParameter(paramName, paramValue, DoubleType.INSTANCE); } else if (paramValue instanceof java.lang.Byte) { sqlQuery.setParameter(paramName, paramValue, ByteType.INSTANCE); } else if (paramValue instanceof java.util.Calendar) { sqlQuery.setParameter(paramName, paramValue, CalendarType.INSTANCE); }/*w ww. java2 s. co m*/ } } }
From source file:org.egov.services.report.FunctionwiseIEService.java
License:Open Source License
/** * * @param reportSearch//from ww w . j a v a 2 s . c o m * @return * @throws ApplicationException * @throws ParseException for Main report getMajor Code and Minor Code for Sub Report get only minor code */ public List<CommonReportBean> getMinorAndMajorCodeList(final ReportSearch reportSearch) throws ApplicationException, ParseException { String sql = ""; if (reportSearch.getByDepartment() && reportSearch.getByDetailCode()) sql = " select coa.glcode as accCode,coa.name as name,null as schedule,null as FIEscheduleId,0 as isMajor from Chartofaccounts coa" + " where coa.type=:type and length(coa.glcode)=" + reportSearch.getMinorCodeLen() + " and coa.glcode like :glcode and classification=4 and isActiveForPosting=true order by 1 "; else if (reportSearch.getByDepartment()) sql = " select coa.glcode as accCode,coa.name as name,mp.schedule as schedule,mp.id as FIEscheduleId,0 as isMajor from Chartofaccounts coa,Schedulemapping mp" + " where coa.FIEscheduleId=mp.id and coa.type=:type and length(coa.glcode)=" + reportSearch.getMinorCodeLen() + " and coa.FIEscheduleId=:FIEscheduleId order by 1"; else sql = " select coa.glcode as accCode,coa.name as name,mp.schedule as schedule,mp.id as FIEscheduleId, 0 as isMajor from Chartofaccounts coa,Schedulemapping mp" + " where coa.FIEscheduleId=mp.id and coa.type=:type and length(coa.glcode)=" + reportSearch.getMinorCodeLen() + " Union " + " select coa.glcode as accCode,coa.name as name,null as schedule,null as FIEscheduleId ,1 as isMajor from Chartofaccounts coa" + " where coa.type=:type and length(coa.glcode)=" + reportSearch.getMajorCodeLen() + " order by 1"; final Query query = persistenceService.getSession().createSQLQuery(sql) .addScalar("accCode", StringType.INSTANCE).addScalar("name", StringType.INSTANCE) .addScalar("schedule", StringType.INSTANCE).addScalar("FIEscheduleId", LongType.INSTANCE) .addScalar("isMajor", BooleanType.INSTANCE).setString("type", reportSearch.getIncExp()) .setResultTransformer(Transformers.aliasToBean(CommonReportBean.class)); if (reportSearch.getByDetailCode()) query.setString("glcode", reportSearch.getGlcode() + "%"); else if (reportSearch.getByDepartment()) query.setLong("FIEscheduleId", reportSearch.getFIEscheduleId()); if (LOGGER.isDebugEnabled()) LOGGER.debug("----------------" + sql); return query.list(); }
From source file:org.egov.services.report.FunctionwiseIEService.java
License:Open Source License
/** * * @param reportSearch/* w w w. j a va 2s .c om*/ * @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 av 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;// ww w. ja va2s. 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 a va 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 "; 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 w w w . j a v a 2s. c o m 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 www.ja v a2 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.FundFlowService.java
License:Open Source License
/** * get All Receipt Bank Accounts for the selected Fund * *//* ww w .j a v a 2s .co m*/ public List<FundFlowBean> getAllReceiptAccounts(final Long fundId) { final StringBuffer allAccounts = new StringBuffer(500); allAccounts.append( "select ba.id as bankAccountId, ba.accountnumber as accountNumber, coa.glcode as glcode,b.code as bankName ,fd.name as fundName, " + " case when ba.narration = null then 0 else (case when instr(ba.narration,'" + FinancialConstants.BANKACCOUNT_WALKIN_PAYMENT_DESCRIPTION + "',1) = 1 then 1 else 0 end ) end as walkinPaymentAccount " + " from Chartofaccounts coa, fund fd, bankaccount ba left outer join bankbranch bb on ba.branchid=bb.id left outer " + " join bank b on bb.bankid=b.id where coa.id=ba.glcodeid and ba.fundid= fd.id and ba.isactive=true and ba.type in ('RECEIPTS') "); if (fundId != null && fundId != -1) allAccounts.append(" and ba.fundid=" + fundId); else allAccounts.append(" order by fd.code, walkinPaymentAccount, b.code,coa.glcode,ba.accountnumber"); final Query allAccountsQry = getSession().createSQLQuery(allAccounts.toString()).addScalar("bankAccountId") .addScalar("accountNumber").addScalar("glcode").addScalar("bankName").addScalar("fundName") .addScalar("walkinPaymentAccount", BooleanType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(FundFlowBean.class)); final List<FundFlowBean> allAccountslist = allAccountsQry.list(); return allAccountslist; }
From source file:org.openeos.services.ui.form.abstractform.UIBeanSelectorProvider.java
License:Apache License
private Criterion buildSqlRestriction(String sqlRestriction, Object value) { try {//w w w . j av a2 s. c om String result = sqlRestriction; int first = result.indexOf("@"); List<Object> values = new ArrayList<Object>(); List<Type> types = new ArrayList<Type>(); while (first != -1) { String parameter = result.substring(first); int second = parameter.indexOf("@", 1); if (second == -1) { LOG.warn( "There are a sql restrictoion not well formed, the sql restriction is: '{}' for bean class {} ", sqlRestriction, beanClass.getName()); break; } parameter = parameter.substring(0, second + 1); String parameterWithoutRim = parameter.substring(1, parameter.length() - 1); Map<String, Object> mvelContext = new HashMap<String, Object>(); mvelContext.put("source", value); Object parameterValue = MVEL.eval(parameterWithoutRim, mvelContext); // TODO Make more types if (String.class.isAssignableFrom(parameterValue.getClass())) { values.add(parameterValue); types.add(StringType.INSTANCE); } else if (Boolean.class.isAssignableFrom(parameterValue.getClass())) { values.add(parameterValue); types.add(BooleanType.INSTANCE); } else { throw new UnsupportedOperationException(); } result = result.substring(0, first) + "? " + result.substring(second + first + 1); first = result.indexOf("@"); } Type[] t = new Type[types.size()]; return Restrictions.sqlRestriction(result, values.toArray(), types.toArray(t)); } catch (CompileException ex) { LOG.warn(String.format("Compiling validation expression '%s' of class '%s' has thrown an error: %s", sqlRestriction, beanClass.toString(), ex.getMessage()), ex); return Restrictions.sqlRestriction("0=1"); } }