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.hibernate.Transaction;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.yuanchung.sales.dao.customer.ContactDAO;
import com.yuanchung.sales.exception.SystemException;
import com.yuanchung.sales.model.customer.Customer;
import com.yuanchung.sales.model.customer.CustomerContact;
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 ContactDAOImpl extends HibernateDaoSupport implements ContactDAO {
	// 保存聯(lián)系人
	public void save(CustomerContact contact) throws DataAccessException {
		try {
			getHibernateTemplate().save(contact);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 根據(jù)用戶查找客戶
	public List getCustomerByUser(User user, int flag)
			throws DataAccessException {
		return getHibernateTemplate().find(
				"from Customer as c where c.user=? and c.flag=?",
				new Object[] { user, flag });
	}

	// 查找所有的客戶
	public List getAllCusomer() throws DataAccessException {
		try {
			return getHibernateTemplate()
					.find("from Customer c where c.flag=1");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	// 根據(jù)名稱模糊查詢客戶
	public List getCustomerByNamelike(User user, String customerName)
			throws DataAccessException {
		return getHibernateTemplate()
				.find(
						"from Customer as c where 1=1 and c.flag=1 and c.user=? and c.customerName like '%"
								+ customerName + "%'", user);
	}

	// 根據(jù)名稱查找客戶
	public Customer getCustomerByName(String name) throws DataAccessException {
		try {
			return (Customer) getHibernateTemplate()
					.find(
							"from Customer as c where 1=1 and c.flag=1 and c.customerName=?",
							name).get(0);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	// 根據(jù)用戶查找聯(lián)系人
	// from CustomerContact cc left join cc.userContacts uc where 1=1 and
	// cc.flag=1 uc.user=?
	public List getContactByUser(User user) throws DataAccessException {
		// try {
		return getHibernateTemplate()
				.find(
						"from CustomerContact cc where cc.flag=1 and cc.user=? order by  cc.lastModifyTime desc",
						user);
		// } catch (Exception e) {
		// e.printStackTrace();
		// }
		// return null;
	}

	public List getContactByUser(String userIds) {
		StringBuffer hql = new StringBuffer("from CustomerContact cc where cc.flag=1 and ");
		hql.append(" cc.user in (" + userIds + ") order by  cc.lastModifyTime desc");
		return getHibernateTemplate()
				.find(hql.toString());
	}

	// 根據(jù)查找聯(lián)系人
	public List getContact() throws DataAccessException {
		try {
			return getHibernateTemplate().find(
					"from CustomerContact cc where cc.flag=1");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	// 保存用戶自定義選項
	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ù)選項查找聯(lián)系人
	public List getContactByUserDefined(String hql) throws DataAccessException {
		return getHibernateTemplate().find(hql);
	}

	// 根據(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 UserField getUserFieldByOption(UserDefined userDefined)
			throws DataAccessException {
		return (UserField) getHibernateTemplate().find(
				"from UserField as uf where uf.userDefined=?", userDefined)
				.get(0);
	}

	// 根據(jù)id查找聯(lián)系人
	public CustomerContact getById(int contactId) throws DataAccessException {
		try {
			return (CustomerContact) getHibernateTemplate().get(
					CustomerContact.class, contactId);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	// 刪除聯(lián)系人
	public void delete(CustomerContact customerContact)
			throws DataAccessException {
		try {
			getHibernateTemplate().delete(customerContact);
		} 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);
	}

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

	// 更新聯(lián)系人
	public void update(CustomerContact contact) throws DataAccessException {
		getHibernateTemplate().saveOrUpdate(contact);
	}

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

	/**
	 * 根據(jù)名稱搜索聯(lián)系人;
	 */
	public List getContactByName(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_contact where 1=1 and flag=1 and Name like '%"
								+ nameLike + "%'").addEntity(
						CustomerContact.class);
				List list = query.list();
				return list;
			}

		});

	}

	/**
	 * 搜索左邊頁面的頭幾條聯(lián)系人;
	 */
	public List<CustomerContact> getTopContact() throws DataAccessException {
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				// 內(nèi)連接兩張表;根據(jù)關(guān)系表排列時間來搜索聯(lián)系人記錄;
				Query query = session.createSQLQuery(
						"SELECT c.* FROM customer_contact c where c.flag=1;")
						.addEntity(CustomerContact.class);
				List<CustomerContact> list = query.list();
				return list;
			}

		});
	}

	/**
	 * 顯示被凍結(jié)的聯(lián)系人列表;
	 */
	public List getContactByDelete(User user, int flag)
			throws DataAccessException {
		try {
			return getHibernateTemplate()
					.find(
							"from CustomerContact as cc where cc.user=? and cc.flag=? order by cc.lastModifyTime desc",
							new Object[] { user, flag });
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("find delete data error");
			throw new SystemException("find delete data error");
		}
	}

	/**
	 * 添加聯(lián)系人和聯(lián)系人關(guān)聯(lián)表;
	 */
	public void addContact(CustomerContact contact) throws DataAccessException {
		try {
			this.getHibernateTemplate().save(contact);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 獲取客戶;
	 */
	public Customer getCustomerById(Integer id) throws DataAccessException {
		try {
			return (Customer) this.getHibernateTemplate().get(Customer.class,
					id);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 根據(jù)聯(lián)系人搜索用戶－聯(lián)系人;
	 */
	public List getUserContactByCon(CustomerContact contact)
			throws DataAccessException {
		try {
			return this.getHibernateTemplate().find(
					"from UserContact uc where uc.customerContact=?", contact);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public void delete(String sql) throws DataAccessException {
		try {
			getHibernateTemplate().bulkUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 查找最新聯(lián)系人
	public CustomerContact findContactLastest() throws DataAccessException {
		return (CustomerContact) getHibernateTemplate()
				.find(
						"from CustomerContact as cc where cc.id >= (select max(c.id) from CustomerContact as c)")
				.get(0);
	}

	// 找出上次修改人
	public User findLastModifyMan(int userId) throws DataAccessException {
		return (User) getHibernateTemplate().find(
				"from User as u where u.id=?", userId).get(0);
	}

	// 根據(jù)聯(lián)系人ID查找業(yè)務(wù)機(jī)會
	public List getBusiOpportsByContactId(int contactId)
			throws DataAccessException {
		try {
			return getHibernateTemplate()
					.find(
							"select bo from BusinessOpportunity as bo join bo.contactBusiopports as cbs where cbs.customerContact.id=?",
							contactId);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	// 根據(jù)姓名模糊查找聯(lián)系人
	public List getContactsByName(User user, String name, int flag)
			throws DataAccessException {
		try {
			return getHibernateTemplate().find(
					"from CustomerContact as cc where cc.user=? and cc.name like '%"
							+ name + "%' " + " and cc.flag=?",
					new Object[] { user, flag });
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	// 更新聯(lián)系人
	public void updateContacts(int modifyManId, String modifyTime,
			int customerId, int flag) throws DataAccessException {
		try {
			getHibernateTemplate()
					.bulkUpdate(
							"update CustomerContact as cc set cc.flag=?, cc.modifyManId=?, cc.lastModifyTime=?  where cc.customer.id=?",
							new Object[] { flag, modifyManId, modifyTime,
									customerId });
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 根據(jù)聯(lián)系ID查找
	public void updateContactById(int modifyManId, String modifyTime,
			int contactId, int flag) throws DataAccessException {
		try {
			getHibernateTemplate()
					.bulkUpdate(
							"update CustomerContact as cc set cc.flag=?, cc.modifyManId=?, cc.lastModifyTime=?  where cc.id=?",
							new Object[] { flag, modifyManId, modifyTime,
									contactId });
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("find cotact exception!");
			throw new SystemException("find cotact exception!");
		}
	}

	// 根據(jù)ID查找用戶
	public User getUserById(int userId) throws DataAccessException {
		try {
			return (User) getHibernateTemplate().get(User.class, userId);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("find user exception!");
			throw new SystemException("find user exception!");
		}
	}

	// 根據(jù)客戶ID刪除業(yè)務(wù)機(jī)會
	public void deleteByCustomerId(int customerId, int flag)
			throws DataAccessException {
		try {
			getHibernateTemplate()
					.bulkUpdate(
							"delete from BusinessOpportunity as bo where bo.customer.id=? and bo.flag=?",
							new Object[] { customerId, flag });
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
			throw new SystemException(e.getMessage());
		}
	}
}