package com.lgx8.management.serviceImpl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
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.entities.RebateContent;
import com.lgx8.management.service.RebateContentService;
public class RebateContentServiceImpl extends BaseDao implements
RebateContentService {
public List<RebateContent> listRebateContentByCondition(String year,
String month, String fllx, String name, int pageSize, int curPage) {
List<RebateContent> result = new ArrayList<RebateContent>(pageSize / 2);
StringBuffer conditions = new StringBuffer();
conditions.append(" and str_to_date(flsj,'%Y-%m-%d') >= '"+ year +"-"+month+"-01' and str_to_date(flsj,'%Y-%m-%d') <= '"+ year +"-"+month+"-32'");
if (fllx != null && fllx.length() != 0) {
conditions.append(" and fllx='" + fllx + "'");
}
if (name != null && name.length() != 0) {
conditions.append(" and reservation03='" + name + "'");
}
String sql = " select fllx,FLSYFBH,reservation03,sum(fled) totals from rebate ";
if (conditions != null && !conditions.toString().equals("")) {
sql = sql + " where " + conditions.toString().substring(4,conditions.toString().length());
}
sql = sql + " group by flsyfbh,reservation03 ";
sql = sql + " order by fllx,flsyfbh ";
sql = sql + " limit " + curPage * pageSize + "," + pageSize;
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()) {
RebateContent rc = new RebateContent();
rc.setName(rs.getString("reservation03"));
rc.setOid(rs.getLong("flsyfbh"));
rc.setJf_count(rs.getDouble("totals"));
String type = rs.getString("fllx");
if("01".equals(type))
{
rc.setFllx("联盟商家");
}else if("02".equals(type))
{
rc.setFllx("推广站");
}else if("03".equals(type))
{
rc.setFllx("推广中心");
}else if("04".equals(type))
{
rc.setFllx("运营中心");
}
result.add(rc);
}
} catch (Exception e) {
// TODO: handle exception
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return result;
}
public int getTotal(String year,
String month, String fllx, String name) {
int result = 0;
StringBuffer conditions = new StringBuffer();
conditions.append(" and str_to_date(flsj,'%Y-%m-%d') >= '"+ year +"-"+month+"-01' and str_to_date(flsj,'%Y-%m-%d') <= '"+ year +"-"+month+"-32'");
if (fllx != null && fllx.length() != 0) {
conditions.append(" and fllx='" + fllx + "'");
}
if (name != null && name.length() != 0) {
conditions.append(" and reservation03='" + name + "'");
}
String sql = "select count(*) counts from (select fllx,FLSYFBH,reservation03,sum(fled) totals from rebate ";
if (conditions != null && !conditions.toString().equals("")) {
sql = sql + " where " + conditions.toString().substring(4,conditions.toString().length());
}
sql = sql + " group by flsyfbh,reservation03) 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("counts");
}
} catch (Exception e) {
// TODO: handle exception
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return result;
}
}