package com.yuanchung.sales.dao.order.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.order.OrderDAO;
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.order.Order;
import com.yuanchung.sales.model.user.User;
import com.yuanchung.sales.vo.order.CustomerOrderVo;

public class OrderDAOImpl extends HibernateDaoSupport implements OrderDAO {

	// 根據(jù)客戶ID查找客戶
	public Customer getCustomerById(int customerId) throws DataAccessException {
		return (Customer) this.getHibernateTemplate().find(
				"from Customer c where c.id=? and c.flag=1", customerId).get(0);
	}

	// 根據(jù)用戶查找客戶
	public List getCustomerByUser(User user, int flag)
			throws DataAccessException {
		return this.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 this.getHibernateTemplate().find(
				"from Customer as c where 1=1 "
						+ "c.flag=1 and c.user=? and c.customerName like '%"
						+ customerName + "%'", user);
	}

	/*
	 * //保存用戶自定義選項(xiàng) public void saveUserDefined(UserDefined userDefined) throws
	 * DataAccessException { this.getHibernateTemplate().save(userDefined); }
	 * 
	 * //保存自定義選項(xiàng)過(guò)濾條件 public void saveUserFilter(UserFilter userFilter) throws
	 * DataAccessException { this.getHibernateTemplate().save(userFilter); }
	 * 
	 * //保存用戶顯示字段 public void saveUserField(UserField userField) throws
	 * DataAccessException { this.getHibernateTemplate().save(userField); }
	 * 
	 * //根據(jù)用戶和類型查找選項(xiàng) public List getUserDefinedByUserAndType(User user, int
	 * type) throws DataAccessException { return this.getHibernateTemplate()
	 * .find( "from UserDefined as ud where ud.user=? and ud.type=? order by
	 * ud.inDate desc", new Object[] { user, type }); }
	 * 
	 * //根據(jù)id搜索選項(xiàng) public UserDefined getUserDefinedById(int userDefinedId)
	 * throws DataAccessException { return
	 * (UserDefined)this.getHibernateTemplate().get(UserDefined.class,userDefinedId); }
	 * 
	 * //根據(jù)選項(xiàng)查找過(guò)濾條件 public List getUserFilterByOption(UserDefined userDefined)
	 * throws DataAccessException { return
	 * this.getHibernateTemplate().find("from UserDefiend uf where
	 * uf.userDefined=?",userDefined); }
	 * 
	 * //根據(jù)選項(xiàng)搜索顯示字段 public UserField getUserFieldByOption(UserDefined
	 * userDefined) throws DataAccessException { return
	 * (UserField)this.getHibernateTemplate().find("from UserField uf where
	 * uf.userDefined=?",userDefined); }
	 * 
	 * //更新用戶選項(xiàng) public void updateUserDefined(UserDefined userDefined) throws
	 * DataAccessException {
	 * this.getHibernateTemplate().saveOrUpdate(userDefined); }
	 * 
	 * //根據(jù)id查詢過(guò)濾條件 public UserFilter getUserFilter(int id) throws
	 * DataAccessException { return
	 * (UserFilter)this.getHibernateTemplate().get(UserFilter.class, id); }
	 * 
	 * //更新過(guò)濾條件 public void updateUserFilter(UserFilter userFilter) throws
	 * DataAccessException {
	 * this.getHibernateTemplate().saveOrUpdate(userFilter); }
	 * 
	 * //修改顯示字段 public void updateUserField(UserField userField) throws
	 * DataAccessException { this.getHibernateTemplate().update(userField); }
	 */

	// 1.查找所有的訂單
	public List getAllOrder(User user) throws DataAccessException {
		return this
				.getHibernateTemplate()
				.find(
						"from Order o where o.flag=1 and o.user=? order by o.orderDate desc",
						user);
	}

	// 2.根據(jù)客戶名稱查找訂單
	public List<CustomerOrderVo> getOrderByCustomerName(String customerName)
			throws DataAccessException {

		return this.getHibernateTemplate().executeFind(new HibernateCallback() {

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session
						.createSQLQuery("select *from order o,customer c where 1=1 and o.customerId=c.id and c.Customer_name='customerName'");
				 List<CustomerOrderVo> list = query.list();
				return list;
			}
		});
	}

	// 3.保存訂單
	public void save(Order order) throws DataAccessException {
		this.getHibernateTemplate().save(order);
	}

	// 4.修改訂單
	public void update(Order order) throws DataAccessException {
		this.getHibernateTemplate().update(order);
	}

	// 5.刪除訂單
	public void delete(Order order) throws DataAccessException {
		this.getHibernateTemplate().delete(order);
	}

	// 6.搜索前面幾張訂單的記錄
	public List getTopOrder() throws DataAccessException {
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {

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

	// 7.搜索刪除狀態(tài)的訂單
	public List getOrderByDelete(User user, int flag)
			throws DataAccessException {
		try {
			return getHibernateTemplate()
					.find(
							"from Order as o where o.user=? and o.flag=? order by o.orderDate desc",
							new Object[] { user, flag }); // 2為刪除狀態(tài);
		} catch (Exception e) {
			e.printStackTrace();
			logger.debug("find order error!");
			throw new SystemException("find order error!");
		}

	}

	// 8.查詢最近的訂單
	public Order getLastestOrder() throws DataAccessException {
		return (Order) this
				.getHibernateTemplate()
				.find(
						"from order as o where o.orderId >= (select max(o.orderId) from Order as 00)").get(0);
	}

	// 9.根據(jù)ID查找聯(lián)系人
	public CustomerContact getCustomerContactById(int customercontactId)
			throws DataAccessException {
		return (CustomerContact) getHibernateTemplate().get(
				CustomerContact.class, customercontactId);
	}

	// 10.根據(jù)訂單號(hào)查找訂單
	public Order getOrderById(int orderId) {
		return (Order) getHibernateTemplate().get(Order.class, orderId);
	}
}