|
public List findByGroupByMaterial(String date1,String date2){
try {
StringBuffer hql = new StringBuffer();
hql.append("SELECT t.mt_material_id ID, ")
.append(" t1.name mt_name, ")
.append(" t1.code MT_CODE, ")
.append(" SUM(t.num)NUM, ")
.append(" SUM(decode((SELECT substr(s.code, ")
.append(" 0, ")
.append(" 3) ")
.append(" FROM im_affair_type s ")
.append(" WHERE s.id = t.im_affair_type_id), ")
.append(" 'imp', ")
.append(" 1, ")
.append(" 'exp', ")
.append(" -1) * t.num * t.price) MONEY ")
.append("FROM im_affair t, ")
.append(" mt_material t1 ")
.append("WHERE t.mt_material_id = t1.id AND ")
.append(" To_date('"+date1+"', ")
.append(" 'yyyy-mm-dd') <= ")
.append(" t.accountant_date AND ")
.append(" t.accountant_date >= ")
.append(" To_date('"+date2+"', ")
.append(" 'yyyy-mm-dd') ")
// .append(" t.accountant_date BETWEEN ")
// .append(" To_date('"+date1+"', ")
// .append(" 'yyyy-mm-dd') AND ")
// .append(" To_date('"+date2+"', ")
// .append(" 'yyyy-mm-dd') ")
.append("GROUP BY t.mt_material_id, ")
.append(" t1.NAME, ")
.append(" t1.code ");
List list = session.createSQLQuery(hql.toString())
.addScalar("ID", Hibernate.LONG)//这里ID要用大写才对，不然会报列名错误
.addScalar("MT_NAME", Hibernate.STRING)//
.addScalar("MT_CODE", Hibernate.STRING)
.addScalar("NUM", Hibernate.DOUBLE)
.addScalar("MONEY", Hibernate.DOUBLE)//同上，都要大写
.list();
log.info("findByGroupByMaterial successful and the list size is "+list.size());
return list;
} catch (HibernateException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw e;
}
}
|
