clientinfodaoimpl.java

一个oa系统

package com.oa.crm.db;

import java.sql.CallableStatement;
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.hibernate.HibernateException;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.oa.crm.db.ClientInfo;

/**
 * @author chenzhaofu
 * 
 */
public class ClientInfoDAOImpl extends HibernateDaoSupport implements
		ClientInfoDAO {

	/**
	 * @author chenzhaofu
	 * @param @
	 */
	public void addClientInfo(ClientInfo clientInfo) {
		Connection conn = getHibernateTemplate().getSessionFactory().openSession().connection();
		CallableStatement cstmt = null;
		try {
			cstmt = conn.prepareCall("{call add_client_info(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
			cstmt.setString(1, clientInfo.getClientName());
			cstmt.setString(2, clientInfo.getNetworkId());
			cstmt.setString(3, clientInfo.getCalling());
			cstmt.setString(4, clientInfo.getEmail());
			cstmt.setString(5, clientInfo.getRivetPhone());
			cstmt.setString(6, clientInfo.getMobilePhone());
			cstmt.setString(7, clientInfo.getFax());
			cstmt.setString(8, clientInfo.getAddress());
			cstmt.setString(9, clientInfo.getClientState());
			cstmt.setString(10, clientInfo.getServerMode());
			cstmt.setString(11, clientInfo.getPostalcode());
			cstmt.setString(12, clientInfo.getLinkman());
			cstmt.setString(13, clientInfo.getEmpId());
			cstmt.setString(14, clientInfo.getQq());
			cstmt.setString(15, clientInfo.getMsn());
			cstmt.setString(16, clientInfo.getSize());
			cstmt.setString(17, clientInfo.getRemark());
			cstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} finally {
			try {

				if (cstmt != null) {
					cstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e) {
				// TODO: handle exception
			}
		}
	}

	public void deleteClientInfo(String clientId) {
		// TODO 自动生成方法存根
		try { 
			ClientInfo clientInfo = (ClientInfo) getHibernateTemplate().find("from ClientInfo where client_id = '" + clientId + "'").get(0);
			getHibernateTemplate().delete(clientInfo);
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public int getTotalRecord(String empId) {
		// TODO 自动生成方法存根
		int counts = 0;
		 List list = null;
			try {  
				list = getHibernateTemplate().find("select count(*) from ClientInfo where emp_id=?", empId);
				String str = list.get(0).toString();
				counts = Integer.parseInt(str);
			} catch (HibernateException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		return counts;
	}

	public ArrayList<ClientInfo> showAllCleintInfo(String empId) {
		// TODO 自动生成方法存根
		ArrayList<ClientInfo> clientInfoList = new ArrayList<ClientInfo>();
		try { 
			clientInfoList = (ArrayList<ClientInfo>) getHibernateTemplate().find("from ClientInfo where emp_id = ?", empId);
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return clientInfoList;
	}

	public ArrayList<ClientInfo> showMenberClientInfo(int start, int count,
			String empId) {
		// TODO 自动生成方法存根
		ArrayList<ClientInfo> clientInfoList = new ArrayList<ClientInfo>();
		String str = "select *from client_info where emp_id ='" + empId + "' limit " + start + ",5";
		Connection conn = getHibernateTemplate().getSessionFactory().openSession().connection();
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		try {
			pstmt = conn.prepareStatement(str);
			rst = pstmt.executeQuery();
			while (rst.next()) {
				ClientInfo clientInfo = new ClientInfo();
				clientInfo.setClientId(rst.getString(1));
				clientInfo.setClientName(rst.getString(2));
				clientInfo.setLinkman(rst.getString("linkman"));
				clientInfo.setRivetPhone(rst.getString("rivet_phone"));
				clientInfo.setMobilePhone(rst.getString("mobile_phone"));
				clientInfo.setClientState(rst.getString("client_state"));
				clientInfo.setServerMode(rst.getString("server_mode"));
				clientInfo.setEmpId(rst.getString("emp_id"));
				clientInfoList.add(clientInfo);
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch
			e.printStackTrace();
		} finally {
			try {
				if (rst != null) {
					rst.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e) {
				// TODO: handle exception
			}
		}

		return clientInfoList;
	}

	public void updateClientInfo(ClientInfo clientInfo) {
		try { 
			getHibernateTemplate().update(clientInfo);
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public ClientInfo findOneClientInfoById(String clientId) {
		// TODO 自动生成方法存根
		ClientInfo clientInfo = new ClientInfo();
		clientInfo = null;
		List list = null;
		try {  
			list = getHibernateTemplate().find("from ClientInfo where client_id =?", clientId);
			if (list.size() > 0) {
				clientInfo = (ClientInfo) list.get(0);
			}
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return clientInfo;
	}

	/**
	 * @author chenzhaofu 冲突查询(根据客户名称查询)
	 */
	public ArrayList<ClientInfo> findAllClientInfoByClientName(String clientName) {
		// TODO 自动生成方法存根
		String sql = "from ClientInfo where client_Name like ?";
		ArrayList<ClientInfo> clientInfoList = (ArrayList<ClientInfo>) getHibernateTemplate().find(sql, "%" + clientName + "%");
		return clientInfoList;
	}

	/**
	 * @author chenzhaofu 查询所有的公共用户,放弃用户(查询所有员工的放弃用户)
	 */
	public ArrayList<ClientInfo> findAllQuitClientInfo() {
		String str = "放弃";
		String sql = "from ClientInfo where server_mode =?";
		ArrayList<ClientInfo> list = (ArrayList<ClientInfo>) getHibernateTemplate().find(sql, str);
		return list;
	}

	/**
	 * @author chenzhaofu 获取公共客户
	 */
	public void abtainQuitClientInfo(String empId, ClientInfo clientInfo) {
		// TODO 自动生成方法存根
		try {  
			clientInfo.setEmpId(empId);
			clientInfo.setServerMode("跟踪");
			getHibernateTemplate().update(clientInfo);
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * @author chenzhaofu 转让客户
	 * 
	 */
	public void attornClientInfo(String empId, ClientInfo clientInfo) {
		try { 
			clientInfo.setEmpId(empId);
			if (clientInfo.getServerMode().equals("放弃")) {
				clientInfo.setServerMode("跟踪");
			}
			if (clientInfo.getServerMode().equals("成交")) {
				clientInfo.setServerMode("成交");
			}
			getHibernateTemplate().update(clientInfo);
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * @author chenzhaofu 截取并排序查询
	 * 
	 */
	public ArrayList<ClientInfo> showMenberSortClientInfo(int start, int count,
			String empId, String sortFiled, String sortType) {
		// TODO 自动生成方法存根
		ArrayList<ClientInfo> clientInfoList = new ArrayList<ClientInfo>();
		String str = "select *from client_info where emp_id ='" + empId + "' order by " + sortFiled + " " + sortType + " limit "+ start + ",3";
		Connection conn = getHibernateTemplate().getSessionFactory().openSession().connection();
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		try {
			pstmt = conn.prepareStatement(str);
			rst = pstmt.executeQuery();
			while (rst.next()) {
				ClientInfo clientInfo = new ClientInfo();
				clientInfo.setClientId(rst.getString(1));
				clientInfo.setClientName(rst.getString(2));
				clientInfo.setLinkman(rst.getString("linkman"));
				clientInfo.setRivetPhone(rst.getString("rivet_phone"));
				clientInfo.setMobilePhone(rst.getString("mobile_phone"));
				clientInfo.setClientState(rst.getString("client_state"));
				clientInfo.setServerMode(rst.getString("server_mode"));
				clientInfo.setEmpId(rst.getString("emp_id"));
				clientInfoList.add(clientInfo);
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch
			e.printStackTrace();
		} finally {
			try {
				if (rst != null) {
					rst.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e) {
				// TODO: handle exception
			}
		}
		return clientInfoList;
	}

	public int getTotalRecordByClientName(String clientName) {
		// TODO 自动生成方法存根
		int count = 0;
		String sql = "select count(*) from ClientInfo where client_name like ?";
		count = Integer.parseInt(getHibernateTemplate().find(sql,"%" + clientName + "%").get(0).toString());
		return count;
	}

	public int getTotalRecordMyClientByClientName(String clientName,String empId) {
		String[] data = new String[2];
		data[0] = "%" + clientName + "%";
		data[1] = empId;
		int count = 0;
		String sql = "select count(*) from ClientInfo where client_name like ? and empId = ?";
		count = Integer.parseInt(getHibernateTemplate().find(sql, data).get(0).toString());
		return count;
	}

	public ArrayList<ClientInfo> showMenberClientInfoByName(int start,int count, String clientName) {
		// TODO 自动生成方法存根
		ArrayList<ClientInfo> clientInfoList = new ArrayList<ClientInfo>();
		String str = "select *from client_info where client_name like '%" + clientName + "%' limit " + start + ",5";
		Connection conn = getHibernateTemplate().getSessionFactory().openSession().connection();
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		try {