companyfeeimp.java

自己制作的联通CRM,支持客户分类,管理,升级,积分管理等等..

package com.jn0801.company;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.jn0801.company.dao.CompanyFeeBean;
import com.jn0801.tools.DBConnection;

/**
 * 这是集团话费处理接口的实现类
 * @author zjj
 *
 */
public class CompanyFeeIMP implements CompanyFeeIFC{

	/**
	 * 根据条件查询集团话费表
	 * @param companyFeeBean	集团话费表的bean
	 * @return	集团话费的集合
	 */
	public List<CompanyFeeBean> searchfee(CompanyFeeBean companyFeeBean) {
		Connection con = null;
		String sql = "";
		List<CompanyFeeBean> list = new ArrayList<CompanyFeeBean>();
		sql = "select * from companyfee where companyid in " +
				"(select companyid from companyfee group by companyid having count(mdate)>=6) " +
				"and mdate<=to_char(add_months(sysdate,-1),'yyyy-MM') " +
				"and mdate>=to_char(add_months(sysdate,-6),'yyyy-MM')";
		if(companyFeeBean.getCompanyid() != null && !companyFeeBean.getCompanyid().equals(""))
		{
			sql = sql + " and companyid='" + companyFeeBean.getCompanyid() + "'";
		}
		try {
			con = DBConnection.getConnection();
			QueryRunner queryRunner = new QueryRunner();
			list = (List<CompanyFeeBean>)queryRunner.query(con, sql,new BeanListHandler(CompanyFeeBean.class));
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DbUtils.closeQuietly(con);
		}
		return list;
	}

	
	
	/**
	 * 根据条件查询集团话费表
	 * @param companyid		集团编号
	 * @param mdate			记录时间
	 * @param companyname	集团名称
	 * @return	集团的信息
	 */
	public CompanyFeeBean selectfee(String companyid, String mdate, String companyname) {
		Connection con = null;
		String sql = "";
		sql = "select * from companyfee where 1=1";
		CompanyFeeBean companyBean = new CompanyFeeBean();
		if(companyid != null && !companyid.equals(""))
		{
			sql = sql + " and companyid='" + companyid + "'";
		}
		if(mdate != null && !mdate.equals(""))
		{
			sql = sql + " and mdate='" + mdate + "'";
		}
		if(companyname != null && !companyname.equals(""))
		{
			sql = sql + " and companyname like '%" + companyname + "%'";
		}
		
		try {
			con = DBConnection.getConnection();
			QueryRunner queryRunner = new QueryRunner();
			companyBean = (CompanyFeeBean)queryRunner.query(con, sql,new BeanHandler(CompanyFeeBean.class));
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DbUtils.closeQuietly(con);
		}
		return companyBean;

	}

	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}