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

import java.io.UnsupportedEncodingException;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.yuanchung.sales.dao.report.ReportDAO;
import com.yuanchung.sales.model.report.Report;

/**
 * 報表DAO
 * @author gzq
 *
 */
public class ReportDAOImpl extends ReportBaseDAOImpl implements ReportDAO {
	private static final Log log = LogFactory.getLog(ReportDAOImpl.class);
	/**
	 * 保存報表
	 * @param report  報表
	 */
	public void save(Report transientInstance) {
		log.debug("saving Report instance");
		getHibernateTemplate().save(transientInstance);
		log.debug("save successful");
	}

	/**
	 * 刪除報表
	 * @param report  報表
	 */
	public void delete(Report persistentInstance) {
		log.debug("deleting Report instance");
		getHibernateTemplate().delete(persistentInstance);
		log.debug("delete successful");
	}

	/**
	 * 根據ID讀取報表
	 * @param id  報表ID
	 * @return
	 */
	public Report findById(java.lang.Integer id) {
		log.debug("getting Report instance with id: " + id);
		return (Report) getHibernateTemplate().get("com.yuanchung.sales.model.report.Report", id);
	}

	/**
	 * 讀取所有報表
	 * @return  List<Report>
	 */
	public List findAll() {
		log.debug("finding all Report instances");
		return getHibernateTemplate().find("from Report");
	}
	/**
	 * 根據時間范圍獲得新增客戶數
	 * @param startDate  開始日期
	 * @param endDate    結束日期
	 * @return List(){month,customerCount}
	 */
	public List getNewCreatedCustomerCount(String startDate,String endDate){
		StringBuilder hqlSB = new StringBuilder();
		hqlSB.append("select ");
		hqlSB.append("concat(extract(year from inDate),'-',extract(month from inDate)),");
		hqlSB.append("count(*) ");
		hqlSB.append("from Customer ");
		hqlSB.append("where ");
		hqlSB.append("inDate between ? and ? group by concat(extract(year from inDate),'-',extract(month from inDate))");
		return getHibernateTemplate().find(hqlSB.toString(), new Object[]{startDate,endDate});
	}
	/**
	 * 獲得已忽視的客戶數
	 * @param startDate  開始日期
	 * @param endDate    結束日期
	 * @return List(){month,customerCount}
	 */
	public List getIgnoredCustomerCount(String startDate,String endDate){
		StringBuilder sqlSB = new StringBuilder();
		sqlSB.append("select ");
		sqlSB.append("concat(extract(year from create_date),'-',extract(month from create_date)),");
		sqlSB.append("count(*) ");
		sqlSB.append("from activity_task ");
		sqlSB.append("where recordId is not null and ");
		sqlSB.append("datediff(now(), cast(create_date as date)) > 60 and ");
		sqlSB.append("create_date between :startDate and :endDate ");
		sqlSB.append("group by ");
		sqlSB.append("concat(extract(year from create_date),'-',extract(month from create_date));");
		
		List lst = this.getSession().createSQLQuery(sqlSB.toString()).setString("startDate", startDate).setString("endDate", endDate).list();
		if(lst != null && lst.size() > 0){
			for(int i=0,len=lst.size();i<len;i++){
				Object[] objs = (Object[])lst.get(i);
				byte[] strBytes = (byte[])objs[0];
				try {
					objs[0] = new String(strBytes, "UTF-8");
				} catch (UnsupportedEncodingException e) {
					log.error(this.getClass(), e);
				}
			}
		}
		
		return lst;
	}
	/**
	 * 獲得最近聯系的客戶數
	 * @param startDate  開始日期
	 * @param endDate    結束日期
	 * @return List(){month,customerCount}
	 */
	public List getRecentlyContactCustomerCount(String startDate,String endDate){
		StringBuilder sqlSB = new StringBuilder();
		sqlSB.append("select ");
		sqlSB.append("concat(extract(year from create_date),'-',extract(month from create_date)),");
		sqlSB.append("count(*) ");
		sqlSB.append("from activity_task ");
		sqlSB.append("where recordId is not null and ");
		sqlSB.append("datediff(now(), cast(create_date as date)) < 30 and ");
		sqlSB.append("create_date between :startDate and :endDate ");
		sqlSB.append("group by ");
		sqlSB.append("concat(extract(year from create_date),'-',extract(month from create_date))");
		
		List lst = this.getSession().createSQLQuery(sqlSB.toString()).setString("startDate", startDate).setString("endDate", endDate).list();
		if(lst != null && lst.size() > 0){
			for(int i=0,len=lst.size();i<len;i++){
				Object[] objs = (Object[])lst.get(i);
				byte[] strBytes = (byte[])objs[0];
				try {
					objs[0] = new String(strBytes, "UTF-8");
				} catch (UnsupportedEncodingException e) {
					log.error(this.getClass(), e);
				}
			}
		}
		
		return lst;
	}
}