package com.yuanchung.sales.dao.busiOpport.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.busiOpport.BusiOpportDAO;
import com.yuanchung.sales.exception.SystemException;
import com.yuanchung.sales.model.businessOpportunity.BusinessOpportunity;
import com.yuanchung.sales.model.businessOpportunity.ContactBusiopport;
import com.yuanchung.sales.model.customer.ConRole;
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 BusiOpportDAOImpl extends HibernateDaoSupport implements
		BusiOpportDAO {
	// 根據(jù)ID查找客戶
	public Customer getCustomerById(int customerId) throws DataAccessException {
		return (Customer) getHibernateTemplate().find(
				"from Customer as c where c.id=? and c.flag=1",
				customerId).get(0);
	}

	// 保存業(yè)務(wù)機會
	public void save(BusinessOpportunity businessOpportunity)
			throws DataAccessException {
		getHibernateTemplate().save(businessOpportunity);
	}

	// 根據(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});
	}

	

	// 根據(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);
	}

	// 查找所有的業(yè)務(wù)機會
	public List getBusiOpport(User user) throws DataAccessException {
		return getHibernateTemplate().find(
				"from BusinessOpportunity bo where bo.flag=1 and bo.user=? order by bo.creatDate desc", user);
	}
	public List getBusiOpport(String userIds){
		StringBuffer hql = new StringBuffer("from BusinessOpportunity bo where bo.flag=1 and bo.user ");
		hql.append("in (" + userIds + ") order by bo.creatDate desc");
		return getHibernateTemplate().find(hql.toString());
	}

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

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

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

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

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

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

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

	// 更新用戶選項
	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);
	}

	// 根據(jù)選項查找聯(lián)系人
	public List getBusiOpportByUserDefined(String hql)
			throws DataAccessException {
		return getHibernateTemplate().find(hql);
	}

	// 根據(jù)id查找業(yè)務(wù)機會
	public BusinessOpportunity getById(int id) throws DataAccessException {
		return (BusinessOpportunity) getHibernateTemplate().get(
				BusinessOpportunity.class, id);
	}

	// 修改業(yè)務(wù)機會
	public void updateBusiOpport(BusinessOpportunity busiOpport)
			throws DataAccessException {
		getHibernateTemplate().saveOrUpdate(busiOpport);
	}

	// 刪除業(yè)務(wù)機會
	public void delete(BusinessOpportunity busiOpport)
			throws DataAccessException {
		getHibernateTemplate().delete(busiOpport);
	}

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

	/**
	 * 根據(jù)名稱模糊查找業(yè)務(wù)機會對象列表;
	 */
	public List getAllBusiOpportByName(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 business_opportunity b where 1=1 and b.flag=1 and BusiOpport_Name like '%"
										+ nameLike + "%'").addEntity(
								BusinessOpportunity.class);// 操作的是sql,添加實體后轉(zhuǎn)化成hql操作，這個是必須的;
				List list = query.list();
				return list;
			}

		});
	}

	/**
	 * 搜索頭幾條記錄;
	 */
	public List getTopBusiOpport() throws DataAccessException {
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session
						.createSQLQuery(
								"SELECT * FROM business_opportunity b where 1=1 and b.flag=1 order by Creat_Date desc")
						.addEntity(BusinessOpportunity.class);
				List list = query.list();
				return list;
			}
		});
	}

		
	/**
	 * 搜索刪除狀態(tài)的業(yè)務(wù)機會;
	 */
	public List getBusiOpportByDelete(User user, int flag) throws DataAccessException {
		try {
			return getHibernateTemplate().find("from BusinessOpportunity as bo where bo.user=? and bo.flag=? order by bo.lastModifyTime desc", new Object[]{user, flag});//2為刪除狀態(tài);
		} catch(Exception e) {
			e.printStackTrace();
			logger.error("find businessOpports error!");
			throw new SystemException("find businessOpports error!");
		}
		
	}
	//查找最新的業(yè)務(wù)機會
	public BusinessOpportunity getLastestBusiOpport() throws DataAccessException {
		return (BusinessOpportunity)getHibernateTemplate().find("from BusinessOpportunity as bo where bo.id >= (select max(b.id) from BusinessOpportunity as b)").get(0);
	}
	//根據(jù)ID查找聯(lián)系人
	public CustomerContact getContactById(int contactId) throws DataAccessException {
		return (CustomerContact)getHibernateTemplate().get(CustomerContact.class, contactId);
	}
	/**
	 * 根據(jù)聯(lián)系人查找業(yè)務(wù)機會
	 * @param contactId聯(lián)系人主鍵
	 * @return
	 * @throws DataAccessException
	 */
	public List getBusiOpportsByContactId(int contactId) throws DataAccessException {
		return getHibernateTemplate().find("from BusinessOpportunity as bo where bo.contactId=? order by bo.creatDate desc", contactId);
	}
	/**
	 * 保存聯(lián)系人---業(yè)務(wù)機會
	 * @param contactBusiopport
	 * @throws DataAccessException
	 */
	public void saveContactBusiOpport(ContactBusiopport contactBusiopport) throws DataAccessException {
		getHibernateTemplate().save(contactBusiopport);
	}
	//查找聯(lián)系人根據(jù)業(yè)務(wù)機會ID
	public List getContactsByBusiOpportId(int busiOpportId) throws DataAccessException {
		return getHibernateTemplate().find("select cc,  from CustomerContact as cc join cc.contactBusiopports as cb where cb.businessOpportunity.id=?", busiOpportId);
	}
	public void  saveConRole(ContactBusiopport ContactBusiopport) throws DataAccessException {
		getHibernateTemplate().save(ContactBusiopport);
	}
	//根據(jù)客戶ID查找聯(lián)系人
	public List getContactRoleByCustomerId(int customerId) throws DataAccessException {
		return getHibernateTemplate().find("from ContactRole as cr where cr.customerContact.customer.id=?", customerId);
	}
	//查找所有的角色
	public List getAllConRole() throws DataAccessException {
		return getHibernateTemplate().find("from ConRole");
	}	
	//根據(jù)主鍵查找角色
	public ConRole getConRoleById(int conRoleId) throws DataAccessException {
		return (ConRole)getHibernateTemplate().get(ConRole.class, conRoleId);
	}
	
	//查找所有未刪除的聯(lián)系人
	public List getContactByUser(User user, int flag) throws DataAccessException {
		return getHibernateTemplate().find("from CustomerContact as cc where cc.user=? and cc.flag=?", new Object[]{user, flag});
	}	
	//根據(jù)業(yè)務(wù)機會ID查找聯(lián)系人---業(yè)務(wù)機會
	public List getContactBusiOpportByBusiOpportId(int busiOpportId) throws  DataAccessException {
		return getHibernateTemplate().find("from ContactBusiopport as cb where cb.businessOpportunity.id=?", busiOpportId);
	}
	//根據(jù)客戶ID查找所有的聯(lián)系人
	public List getContactsByCustomerId(int customerId, List<Integer> contactIdArray) throws  DataAccessException {
		StringBuffer hql = new StringBuffer();
		hql.append("from CustomerContact as cc where cc.customer.id=?");
		for(Integer o : contactIdArray) {			
			hql.append(" and cc.id != "+o);
		}		
		return getHibernateTemplate().find(hql.toString(), customerId);		
	}
	//查找聯(lián)系人---業(yè)務(wù)機會
	public ContactBusiopport getContactBusiopportById(int conBusiId) throws DataAccessException {
		return (ContactBusiopport)getHibernateTemplate().get(ContactBusiopport.class, conBusiId);
	}
	//保存聯(lián)系人---業(yè)務(wù)機會
	public void saveContactBusiopport(ContactBusiopport conBusi) throws DataAccessException {
		getHibernateTemplate().save(conBusi);
	}
	//更新聯(lián)系人---業(yè)務(wù)機會
	public void updateContactBusiopport(ContactBusiopport conBusi) throws DataAccessException {
		try{
			getHibernateTemplate().update(conBusi);
		}catch(Exception e){
			e.printStackTrace();			
		}
	}
	//批量更新業(yè)務(wù)機會的狀態(tài)
	public void updateBusiOpports(int modifyManId, String modifyTime, int customerId, int flag) throws DataAccessException {
		try{
			getHibernateTemplate().bulkUpdate("update BusinessOpportunity as bo set bo.flag=?, bo.modifyManId=?, bo.lastModifyTime=? where bo.customer.id=?", new Object[]{flag, modifyManId, modifyTime, customerId});
		}catch(Exception e){
			e.printStackTrace();			
		}
	}
	//更新聯(lián)系人根據(jù)ID
	public void updateBusiOpportById(int modifyManId, String modifyTime, int busiOpportId, int flag) throws DataAccessException {
		try{
			getHibernateTemplate().bulkUpdate("update BusinessOpportunity as bo set bo.flag=?, bo.modifyManId=?, bo.lastModifyTime=? where bo.id=?", new Object[]{flag, modifyManId, modifyTime, busiOpportId});
		}catch(Exception e){
			e.printStackTrace();	
			logger.error("bulk update contacts error...");
			throw new SystemException("bulk update contacts error...");
		}
	}
	//根據(jù)聯(lián)系人名稱查找模糊查找聯(lián)系人
	public List getBusiOpportsByNameLike(User user, int flag, String name) throws DataAccessException {
		try{
			return getHibernateTemplate().find("from BusinessOpportunity as bo where bo.flag=? and bo.user=? and bo.busiOpportName like '%"+name+"%'", new Object[]{flag, user});
		}catch(Exception e){
			e.printStackTrace();
			logger.error("bulk update contacts error...");
			throw new SystemException("bulk update contacts error...");
		}
	}
	//根據(jù)客戶ID刪除聯(lián)系人
	public void deleteByCustomerId(int customerId, int flag) throws DataAccessException {
		try{			
			getHibernateTemplate().bulkUpdate("delete from CustomerContact as cc where cc.customer.id=? and cc.flag=?", new Object[]{customerId, flag});
		}catch(Exception e){
			e.printStackTrace();
			logger.error(e.getMessage());
			throw new SystemException(e.getMessage());
		}
	}
	

	//根據(jù)業(yè)務(wù)機會對象刪除業(yè)務(wù)機會
	public void deleteByObject(BusinessOpportunity busiOpport) throws DataAccessException {
		
		try{
			this.getHibernateTemplate().delete(busiOpport);
			logger.debug("刪除成功！");
		}catch(Exception e){
			e.printStackTrace();
			logger.error(e.getMessage());
			throw new SystemException(e.getMessage());
		}
	}
}