package com.lgx8.management.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.springframework.orm.hibernate3.SessionFactoryUtils;
import com.lgx8.common.dao.impl.BaseDao;
import com.lgx8.management.dao.CustmerConsumeCountDao;
import com.lgx8.management.entities.CustmerConsumeCount;
public class CustmerConsumeCountDaoImpl extends BaseDao implements
CustmerConsumeCountDao {
public List<CustmerConsumeCount> getCustmerConsumeByCondition(String name,
String st_time, String ed_time, int curPage) {
List<CustmerConsumeCount> list = new ArrayList<CustmerConsumeCount>(getCustmerConsumeByConditionPageSize(name, st_time, ed_time)/2);
String sql = "SELECT c.xfsjbh xfsjbh,m.sjmc sjmc,sum(c.xfje) total_xfje,sum(c.hdjfs) total_hdjfs FROM custmerxf c,merchant m where c.xfsjbh=m.id";
StringBuffer condition = new StringBuffer();
if(name!=null&&name.length()!=0){
condition.append(" and m.name like '%"+name+"%'");
}
if(st_time!=null&&st_time.length()!=0){
condition.append(" and c.xfsj>str_to_date('"+st_time.split(" ")[0]+"','%Y-%m_%d')");
}
if(ed_time!=null&&ed_time.length()!=0){
condition.append(" and c.xfsj<str_to_date('"+ed_time.split(" ")[0]+"','%Y-%m-%d')");
}
sql = sql + condition.toString()+" group by c.xfsjbh limit "+curPage*8+","+8;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = SessionFactoryUtils.getDataSource(getHibernateTemplate().getSessionFactory()).getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
CustmerConsumeCount ccc = new CustmerConsumeCount();
ccc.setMerchant_id(rs.getLong("xfsjbh"));
ccc.setMerchant_name(rs.getString("sjmc"));
ccc.setCustmerConsumeScore(rs.getDouble("total_hdjfs"));
ccc.setCustmerConsumeXfje(rs.getDouble("total_xfje"));
list.add(ccc);
}
} catch (Exception e) {
// TODO: handle exception
}finally{
if(rs!=null){
try {
rs.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
if(ps!=null){
try {
ps.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
return list;
}
public int getCustmerConsumeByConditionPageSize(String name,
String st_time, String ed_time) {
int result = 0;
String sql = "select count(*) co from ( SELECT c.xfsjbh xfsjbh,m.sjmc sjmc,sum(c.xfje) total_xfje,sum(c.hdjfs) total_hdjfs FROM custmerxf c,merchant m where c.xfsjbh=m.id";
StringBuffer condition = new StringBuffer();
if(name!=null&&name.length()!=0){
condition.append(" and m.name like '%"+name+"%'");
}
if(st_time!=null&&st_time.length()!=0){
condition.append(" and c.xfsj>str_to_date('"+st_time.split(" ")[0]+"','%Y-%m_%d')");
}
if(ed_time!=null&&ed_time.length()!=0){
condition.append(" and c.xfsj<str_to_date('"+ed_time.split(" ")[0]+"','%Y-%m-%d')");
}
sql = sql + condition.toString()+" group by c.xfsjbh ) t";
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = SessionFactoryUtils.getDataSource(getHibernateTemplate().getSessionFactory()).getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
result = rs.getInt("co");
}
} catch (Exception e) {
// TODO: handle exception
}finally{
if(rs!=null){
try {
rs.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
if(ps!=null){
try {
ps.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
return result;
}
}