package com.yuanchung.sales.dao.customer.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.yuanchung.sales.dao.customer.CustomerDAO;
import com.yuanchung.sales.exception.SystemException;
import com.yuanchung.sales.model.businessOpportunity.BusinessOpportunity;
import com.yuanchung.sales.model.customer.Customer;
import com.yuanchung.sales.model.customer.CustomerContact;

import com.yuanchung.sales.model.service.CustAccount;
import com.yuanchung.sales.model.service.ServiceOrder;
import com.yuanchung.sales.model.user.User;
import com.yuanchung.sales.model.userDefined.UserDefined;
import com.yuanchung.sales.model.userDefined.UserField;
import com.yuanchung.sales.model.userDefined.UserFilter;

public class CustomerDAOImpl extends HibernateDaoSupport implements CustomerDAO {
	// 根據(jù)用戶取得所有的客戶
	public List getByUser(User user, int flag) throws DataAccessException {
		try {
			return getHibernateTemplate()
					.find(
							"from Customer as c where c.user=? and c.flag=? order by c.lastModifyTime desc ",
							new Object[] { user, flag });
		} catch (RuntimeException e) {
			logger.error(e);
			throw e;
		}
	}

	public List getByUser(String userIds, int flag) {
		StringBuffer hql = new StringBuffer("");
		hql.append("from Customer as c where c.user in (" + userIds + ") ");
		hql.append("and c.flag=" + flag +  "order by c.lastModifyTime desc");
		return getHibernateTemplate().find(hql.toString());
	}

	// 查找相關(guān)的客戶
	public List getCustomerByName(final String nameLike)
			throws DataAccessException {
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createSQLQuery(
						"select * from customer c where 1=1 and c.flag=1 and customer_name like '%"
								+ nameLike + "%';").addEntity(Customer.class);// 注意：必須轉(zhuǎn)化成對象來映射;
				List list = query.list();
				return list;
			}
		});
	}

	// 保存客戶
	public void save(Customer customer) throws DataAccessException {
		try {
			getHibernateTemplate().saveOrUpdate(customer);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 保存用戶自定義選項
	public void saveUserDefined(UserDefined userDefined)
			throws DataAccessException {
		try {
			getHibernateTemplate().save(userDefined);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 保存自定義選項過濾條件
	public void saveUserFilter(UserFilter userFilter)
			throws DataAccessException {
		try {
			getHibernateTemplate().save(userFilter);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 保存用戶顯示字段
	public void saveUserField(UserField userField) throws DataAccessException {
		try {
			getHibernateTemplate().save(userField);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// /根據(jù)用戶和類型查找選項
	public List getUserDefinedByUserAndType(User user, int type)
			throws DataAccessException {
		return getHibernateTemplate().find(
				"from UserDefined as ud where ud.user=? and ud.type=?",
				new Object[] { user, type });
	}

	// 根據(jù)用戶和自定義選項搜索客戶
	public List<Customer> getByUserAndUserDefined(User user,
			UserDefined userDefined) throws DataAccessException {
		return getHibernateTemplate().find(
				"from Customer as c where c.user=? and c.flag=1", user);
	}

	// 根據(jù)id搜索選項
	public UserDefined getUserDefinedById(int userDefinedId)
			throws DataAccessException {
		try {
			return (UserDefined) getHibernateTemplate().get(UserDefined.class,
					userDefinedId);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	// 根據(jù)用戶自定義查詢語句查找客戶
	public List<Customer> getCustomerByUserHql(String hql)
			throws DataAccessException {
		try {
			return getHibernateTemplate().find(hql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	// 根據(jù)選項搜索顯示字段
	public UserField getUserFieldByOption(UserDefined userDefined)
			throws DataAccessException {
		return (UserField) getHibernateTemplate().find(
				"from UserField as uf where uf.userDefined=?", userDefined)
				.get(0);
	}

	// 根據(jù)id查找客戶
	public Customer getById(int customerId) throws DataAccessException {
		try {
			return (Customer) getHibernateTemplate().get(Customer.class,
					customerId);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	// 修改客戶
	public void update(Customer customer) throws DataAccessException {
		try {
			getHibernateTemplate().saveOrUpdate(customer);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 刪除用戶
	public void delete(Customer customer) throws DataAccessException {
		try {
			getHibernateTemplate().delete(customer);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 根據(jù)選項查找過濾條件
	public List getUserFilterByOption(UserDefined userDefined)
			throws DataAccessException {
		return getHibernateTemplate().find(
				"from UserFilter as uf where uf.userDefined=?", userDefined);
	}

	// 更新用戶選項
	public void updateUserDefined(UserDefined userDefined)
			throws DataAccessException {
		getHibernateTemplate().saveOrUpdate(userDefined);
	}

	// 根據(jù)id查詢過濾條件
	public UserFilter getUserFilter(int id) throws DataAccessException {
		return (UserFilter) getHibernateTemplate().get(UserFilter.class, id);
	}

	// 更新過濾條件
	public void updateUserFilter(UserFilter userFilter)
			throws DataAccessException {
		getHibernateTemplate().saveOrUpdate(userFilter);
	}

	// 根據(jù)選項搜索顯示字段
	public UserField getUserFieldByUserDefined(UserDefined userDefined)
			throws DataAccessException {
		return (UserField) getHibernateTemplate().find(
				"from UserField as uf where uf.userDefined=?", userDefined)
				.get(0);
	}

	// 修改顯示字段
	public void updateUserField(UserField userField) throws DataAccessException {
		getHibernateTemplate().saveOrUpdate(userField);
	}

	// 刪除過濾條件
	public void deleteFilter(UserFilter userFilter) throws DataAccessException {
		getHibernateTemplate().delete(userFilter);
	}

	/**
	 * 獲取最新頭幾個客戶;
	 */
	public List<Customer> getTopCustomer() throws DataAccessException {
		return this
				.getHibernateTemplate()
				.find(
						"FROM Customer c where 1=1 and c.flag=1 order by createTime desc limit 0,8");
	}

	/**
	 * 凍結(jié)該客戶;
	 */
	public void clearCustomer(final Integer id) throws DataAccessException {
		this.getHibernateTemplate().executeFind(new HibernateCallback() {

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuffer sql = new StringBuffer(
						"update Customer set flag = 1 where id =" + id);
				session.createQuery(sql.toString()).executeUpdate();
				return null;
			}
		});
	}

	/**
	 * 搜索凍結(jié)客戶列表;
	 */
	public List getCustomerByDelete(User user, int flag)
			throws DataAccessException {
		try {
			return getHibernateTemplate()
					.find(
							"from Customer c where c.user=? and c.flag=? order by c.lastModifyTime desc",
							new Object[] { user, flag });
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("find data exception");
			throw new SystemException("find data exception");
		}
	}

	/**
	 * 徹底刪除客戶以及相關(guān)信息;
	 */
	public boolean deleteCustomerAndSome(Integer id) throws DataAccessException {
		try {
			Customer customer = (Customer) this.getHibernateTemplate().get(
					Customer.class, id);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * 根據(jù)客戶搜索聯(lián)系人;
	 */
	public List getCusConByCus(Customer customer) throws DataAccessException {
		try {
			return this.getHibernateTemplate().find(
					"from CustomerContact cc where 1=1 and cc.customer=?",
					customer);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 根據(jù)客戶搜索業(yè)務機會列表;
	 */
	public List getOpportByCus(Customer customer) throws DataAccessException {
		try {
			return getHibernateTemplate().find(
					"from BusinessOpportunity bo where 1=1 and bo.customer=?",
					customer);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 根據(jù)業(yè)務機會搜索業(yè)務活動列表;
	 */
	public List getPlanByOpport(BusinessOpportunity businessOpportunity)
			throws DataAccessException {
		try {
			return this
					.getHibernateTemplate()
					.find(
							"from BusinessActivityPlan bap where 1=1 and bap.businessopportunity=?",
							businessOpportunity);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 刪除該業(yè)務機會
	 */
	public boolean deleteOpport(BusinessOpportunity bo)
			throws DataAccessException {
		try {
			this.getHibernateTemplate().delete(bo);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	/*
	 * public boolean deleteCusCon(List listCusCon) throws DataAccessException {
	 * try { this.getHibernateTemplate().deleteAll(listCusCon); return true; }
	 * catch (Exception e) { e.printStackTrace(); } return false; }
	 */

	/**
	 * 搜索該客戶的用戶客戶聯(lián)系表;
	 */
	public List getUserConListByCusCon(CustomerContact cusCon)
			throws DataAccessException {
		try {
			return this.getHibernateTemplate().find(
					"from UserContact uc where uc.customerContact=?", cusCon);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 刪除該客戶的用戶聯(lián)系人關(guān)聯(lián)表;
	 */
	public boolean deleteListUserCon(List listUserCon)
			throws DataAccessException {
		try {
			this.getHibernateTemplate().deleteAll(listUserCon);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 刪除該用戶聯(lián)系人;
	 */
	public boolean deleteCusCon(CustomerContact cusCon)
			throws DataAccessException {
		try {
			this.getHibernateTemplate().delete(cusCon);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 刪除該客戶的聯(lián)系人;
	 */
	public boolean updateCusCon(CustomerContact cusCon)
			throws DataAccessException {
		try {
			this.getHibernateTemplate().update(cusCon);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 修改業(yè)務機會;
	 */
	public boolean updateOpport(BusinessOpportunity businessOpportunity)
			throws DataAccessException {
		try {
			this.getHibernateTemplate().update(businessOpportunity);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	// 根據(jù)客戶ID搜索聯(lián)系人
	public List findContactByCustomerId(int customerId)
			throws DataAccessException {
		try {
			return getHibernateTemplate()
					.find(
							"from CustomerContact as cc where cc.flag=1 and cc.customer.id=?",
							customerId);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	// 根據(jù)客戶ID搜索業(yè)務機會
	public List findBusiOpportByCustomerId(int customerId)
			throws DataAccessException {
		try {
			return getHibernateTemplate()
					.find(
							"from BusinessOpportunity as bo where bo.flag=1 and bo.customer.id=?",
							customerId);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	// 根據(jù)用戶和相關(guān)項取得未處理的任務
	public List findTaskByFunctionIdAndRecordId(User user, int functionId,
			int recordaid) throws DataAccessException {
		try {
			return getHibernateTemplate()
					.find(
							"from ActivityRask as ar where ar.user=? and ar.functionId=? and ar.recordId=?",
							new Object[] { user, functionId, recordaid });
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	// ---------------------------------------------更新后--------------------------------------------------------//
	// 查找最新保存的客戶
	public Customer getCustomerLastest() throws DataAccessException {
		try {
			return (Customer) getHibernateTemplate()
					.find(
							"from Customer as c where c.id >=(select max(cc.id) from Customer as cc)")
					.get(0);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	// 查找上次修改人
	public User getUserById(int userId) throws DataAccessException {
		try {
			return (User) getHibernateTemplate().get(User.class, userId);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	// 更新客戶的狀態(tài)
	public void updateCustomers(int modifyManId, String modifiTime,
			int customerId, int flag) throws DataAccessException {
		try {
			getHibernateTemplate()
					.bulkUpdate(
							"update Customer as c set c.flag=?, c.modifyManId=?, c.lastModifyTime=? where c.id=?",
							new Object[] { flag, modifyManId, modifiTime,
									customerId });
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("update customer error");
			throw new SystemException("update customer error");
		}
	}

	// 根據(jù)名稱模糊查找所有的客戶
	public List getCustoemrsByNameLike(int flag, User user, String name)
			throws DataAccessException {
		try {
			return getHibernateTemplate().find(
					"from Customer as c where c.flag=? and c.user=? and c.customerName like '%"
							+ name + "%'", new Object[] { flag, user });
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("find customer error");
			throw new SystemException("find customer error");
		}
	}
	//************小洪2009-2-28**************//
	//取得ID最大的客戶
	public Customer getCustomerByMaxId(){
		Customer customer = null;
		try {
			String queryString1 = "from Customer c";
			String queryString2 = "from Customer c where c.id = (select max(id) from Customer where 1=1)";
			List<Customer> solist1 = super.getSession().createQuery(
					queryString1).list();
			if (solist1.size()>0) {
				List<Customer> solist2 = super.getSession().createQuery(
						queryString2).list();
				customer = solist2.get(0);
			}
		} catch (RuntimeException re) {
			re.getMessage();
			throw re;
		}
		return customer;
	}
	//保存客戶賬號
	public void saveCustAccount(CustAccount custAccount){
		try {
			getHibernateTemplate().save(custAccount);
		} catch (RuntimeException re) {
			re.getMessage();
			throw re;
		}
	}
	//*************小洪2009-2-28end****************//
}