package com.lgx8.management.dao.impl;
import java.sql.SQLException;
import java.util.List;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import com.lgx8.common.PageList;
import com.lgx8.common.dao.impl.BaseDao;
import com.lgx8.gateway.entities.Orders;
import com.lgx8.management.dao.IAccountDetailDao;
import com.lgx8.management.entities.AccountDetail;
import com.lgx8.right.action.form.AccountDetailQueryForm;
import com.lgx8.right.entities.User;
@SuppressWarnings("unchecked")
public class AccountDetailDao extends BaseDao implements IAccountDetailDao{
public PageList queryAccountDetails(User user,AccountDetailQueryForm form,final int pageNum, final int pageSize) {
String fromDate = form.getFromDate();
String toDate = form.getToDate();
final String orderSql = " select id 'id',confirmtime 'time', score 'score',status 'status','order' as 'type' from orders where userid="+user.getId()
+" and status in("+Orders.STATUS_PAID+","+Orders.STATUS_CONFIRM+","+Orders.STATUS_SENDED+","+ Orders.STATUS_RETURN_OK+")";
final String custmerxfSql = " select ID,XFSJ,HDJFS,0,'custmerxf' from custmerxf where HYBH="+user.getId();
final String queryStr = "select count(*) from ("+orderSql+" union "+custmerxfSql+") t ";
String sql = " select * from ("+orderSql+" union "+custmerxfSql+") t ";
String condition = "";
if(null!=fromDate&&!"".equals(fromDate.trim())){
condition = " where t.time>= str_to_date('"+fromDate.trim()+"','%Y-%m-%d %H:%i:%s') ";
}
if(null!=toDate&&!"".equals(toDate.trim())){
if(!"".equals(condition)){
condition += " and t.time<=str_to_date('"+toDate.trim()+"','%Y-%m-%d %H:%i:%s') ";
}else{
condition = " where t.time<=str_to_date('"+toDate.trim()+"','%Y-%m-%d %H:%i:%s') ";
}
}
final String condSql =sql+condition+" order by t.time desc";
final String condStr = condition;
List list = getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Query query = session.createSQLQuery(queryStr+condStr);
List result = query.list();
return result;
}
});
int totalRecordNum = Integer.parseInt(list.get(0).toString());
List data = this.getHibernateTemplate().executeFind(new HibernateCallback<Object>(){
public Object doInHibernate(Session session){
SQLQuery query = session.createSQLQuery(condSql);
query.setResultTransformer(Transformers.aliasToBean(AccountDetail.class));
query.addScalar("id", Hibernate.LONG);
query.addScalar("time", Hibernate.TIMESTAMP);
query.addScalar("score",Hibernate.DOUBLE);
query.addScalar("type",Hibernate.STRING);
query.addScalar("status",Hibernate.INTEGER);
List result = query.setFirstResult((pageNum-1)*pageSize).setMaxResults(
pageSize).list();
System.out.println(result);
return result;
}
});
int totalPageNum = totalRecordNum/pageSize;
PageList pageList = new PageList();
pageList.setPageSize(pageSize);
pageList.setCurrentPageNum(pageNum);
pageList.setDataList(data);
pageList.setTotalPageNum(totalPageNum);
pageList.setTotalRecordNum(totalRecordNum);
return pageList;
}
public PageList queryAccountDetails(String sql,String condition,final int pageNum, final int pageSize) {
final String queryStr = "select count(*) from ( "+sql+" ) t ";
final String condSql = "select * from ( " + sql + " ) t "+condition+" order by t.time desc";
final String condStr = condition;
List list = getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Query query = session.createSQLQuery(queryStr+condStr);
List result = query.list();
return result;
}
});
int totalRecordNum = Integer.parseInt(list.get(0).toString());
List data = this.getHibernateTemplate().executeFind(new HibernateCallback<Object>(){
public Object doInHibernate(Session session){
SQLQuery query = session.createSQLQuery(condSql);
query.setResultTransformer(Transformers.aliasToBean(AccountDetail.class));
query.addScalar("id", Hibernate.LONG);
query.addScalar("time", Hibernate.TIMESTAMP);
query.addScalar("score",Hibernate.DOUBLE);
query.addScalar("type",Hibernate.STRING);
query.addScalar("status",Hibernate.INTEGER);
query.addScalar("description",Hibernate.STRING);
List result = query.setFirstResult((pageNum-1)*pageSize).setMaxResults(
pageSize).list();
System.out.println(result);
return result;
}
});
int totalPageNum = totalRecordNum/pageSize;
PageList pageList = new PageList();
pageList.setPageSize(pageSize);
pageList.setCurrentPageNum(pageNum);
pageList.setDataList(data);
pageList.setTotalPageNum(totalPageNum);
pageList.setTotalRecordNum(totalRecordNum);
return pageList;
}
}