package jp.bne.deno.ordermaid.dao;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityTransaction;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceException;
import javax.persistence.Query;
import javax.persistence.TemporalType;

import org.apache.log4j.Logger;

import com.google.inject.Inject;

import jp.bne.deno.ordermaid.model.OrderData;
import jp.bne.deno.ordermaid.model.OrderDetail;
import jp.bne.deno.ordermaid.model.OrderItem;
import jp.bne.deno.ordermaid.model.SalesSummary;

/**
 * 注文関連のDAO.
 * 
 * @author hasegawt
 * 
 */
public class OrderTable {
	@Inject
	EntityManager em;

	Logger logger = Logger.getLogger(OrderTable.class);

	/**
	 * 注文IDで注文データを取得する.
	 * 
	 * @param orderId
	 * @return
	 */
	public OrderData find(int orderId) {
		return em.find(OrderData.class, orderId);
	}

	/**
	 * 注文を登録する.
	 * 
	 * @param order
	 */
	public void addOrder(OrderData order) {
		EntityTransaction transaction = em.getTransaction();

		transaction.begin();
		try {
			for (OrderDetail detail : order.getDetails()) {
				detail.setOrder(order);
				for (OrderItem item : detail.getItems()) {
					item.setDetail(detail);
				}
			}
			em.merge(order);
			transaction.commit();
		} catch (Throwable e) {
			e.printStackTrace();
			transaction.rollback();
			throw new PersistenceException(e);
		}
	}

	/**
	 * 注文を削除する.
	 * 
	 * @param order
	 */
	public void removeOrderDetail(OrderDetail detail) {
		EntityTransaction transaction = em.getTransaction();
		transaction.begin();
		try {
			em.remove(detail);
			em.flush();
			transaction.commit();
		} catch (Throwable e) {
			transaction.rollback();
		}
	}

	/**
	 * 席毎の最新注文データを取得する.
	 * 
	 * @param sheetId
	 * @return
	 */
	public OrderDetail findBySheetId(int sheetId) {
		OrderDetail detail = new OrderDetail();
		String sql = "select max(seq) from orderdetail where sheetid = :sheetid";
		try {
			Query query = em.createNativeQuery(sql);
			query.setParameter("sheetid", sheetId);
			Object result = query.getSingleResult();
			if (result != null) {
				Integer seq = Integer.valueOf(query.getSingleResult()
						.toString());
				detail = em.find(OrderDetail.class, seq);
			}
		} catch (NoResultException e) {
			logger.warn(e);
		}

		return detail;
	}

	/**
	 * 指定月の注文データを全件取得する.
	 * 
	 * @param sheetId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<OrderData> findOrderByMonth(Date date) {
		String sql = "select o from OrderData o "
				+ "where o.orderDate between ?1 and ?2 "
				+ "order by o.orderDate,o.orderId";
		Query query = em.createQuery(sql);

		// fromは入力年月日の年月の1日
		Calendar from = Calendar.getInstance();
		from.setTime(date);
		from.set(Calendar.DATE, 1);

		// toは入力年月日の年(月+1)の1日
		Calendar to = Calendar.getInstance();
		to.setTime(from.getTime());
		to.add(Calendar.MONTH, 1);
		to.set(Calendar.DATE, 1);
		query.setParameter(1, from, TemporalType.DATE);
		query.setParameter(2, to, TemporalType.DATE);

		List<OrderData> list = new ArrayList<OrderData>();
		try {
			list = (List<OrderData>) query.getResultList();
		} catch (NoResultException e) {
			logger.warn(e.getMessage());
		}
		return list;
	}

	/**
	 * 指定日の注文データを全件取得する.
	 * 
	 * @param sheetId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<OrderData> findOrderByDay(Date date) {
		String sql = "select o from OrderData o "
				+ "where o.orderDate between ?1 and ?2 "
				+ "order by o.orderDate,o.orderId";
		Query query = em.createQuery(sql);

		Calendar today = Calendar.getInstance();
		today.setTime(date);
		today.set(Calendar.HOUR, 0);
		today.set(Calendar.MINUTE, 0);
		today.set(Calendar.SECOND, 0);
		today.set(Calendar.MILLISECOND, 0);
		Calendar tomorrow = Calendar.getInstance();
		tomorrow.setTime(today.getTime());
		tomorrow.add(Calendar.DATE, 1);
		tomorrow.set(Calendar.HOUR, 0);
		tomorrow.set(Calendar.MINUTE, 0);
		tomorrow.set(Calendar.SECOND, 0);
		tomorrow.set(Calendar.MILLISECOND, 0);
		query.setParameter(1, today, TemporalType.DATE);
		query.setParameter(2, tomorrow, TemporalType.DATE);

		List<OrderData> list = new ArrayList<OrderData>();
		try {
			list = (List<OrderData>) query.getResultList();
		} catch (NoResultException e) {
			logger.warn(e.getMessage());
		}
		return list;
	}

	/**
	 * 通年集計する.
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SalesSummary> summarySales() {
		String sql = "select rownum() id,orderdate,sum(price) price,"
				+ " count(distinct orderid) groups,count(distinct seq) amount "
				+ " from (select formatdatetime(orderdate,'yyyy-MM-dd') orderdate ,"
				+ " o.orderid, m.price,d.seq"
				+ " from orderdata o ,orderdetail d,orderitem i,menuitem m "
				+ " where o.orderid = d.orderid and d.seq = i.detail_id"
				+ " and i.menu_id = m.menuid)"
				+ " group by orderdate order by orderdate";
		Query query = em.createNativeQuery(sql, SalesSummary.class);

		List<SalesSummary> list = new ArrayList<SalesSummary>();
		try {
			list = (List<SalesSummary>) query.getResultList();
		} catch (NoResultException e) {
			logger.warn(e.getMessage());
		}
		return list;
	}
}
