package jp.agentec.sinaburocast.service;

import java.util.List;

import jp.agentec.sinaburocast.entity.EnqueteMail;
import jp.agentec.sinaburocast.entity.NotifyMailSendResult;

import org.seasar.extension.jdbc.where.SimpleWhere;
import org.seasar.framework.container.annotation.tiger.Component;
import org.seasar.framework.container.annotation.tiger.InstanceType;

@Component(instance=InstanceType.SINGLETON)
public class NotifyMailSendResultService extends AbstractService<NotifyMailSendResult> {

	private static final String ID_SEQ_NAME = "notify_mail_send_result_id_seq";

	public void deleteByMemberNotifyMailId(Integer memberNotifyMailId){
		List<NotifyMailSendResult> deleteList = findByMemberNotifyMailId(memberNotifyMailId);
		if (deleteList != null && deleteList.size() > 0) {
			jdbcManager.deleteBatch(deleteList).execute();
		}
	}

	public NotifyMailSendResult findById(Long id) {
		return select().id(id).innerJoin("memberNotifyMail").innerJoin("memberNotifyMail.enquete").getSingleResult();
	}

	public int insertNotifyMailSendResult(NotifyMailSendResult notifyMailSendResult, String insId) {
		notifyMailSendResult.notifyMailSendResultId = getSeqNextVal(Long.class, ID_SEQ_NAME);
		return insert(notifyMailSendResult, insId);
	}

	public Long getFindByMemberNotifyMailIdConut(Integer memberNotifyMailId) {
		return select().where(new SimpleWhere().eq("memberNotifyMailId", memberNotifyMailId)).getCount();
	}

	public List<NotifyMailSendResult> findErrorByMemberNotifyMailId(Integer memberNotifyMailId) {
		return select().where(new SimpleWhere().eq("memberNotifyMailId", memberNotifyMailId).ne("sendResultCd", 0)).orderBy("answerTimes desc").getResultList();
	}
	
	public List<NotifyMailSendResult> findByMemberNotifyMailId(Integer memberNotifyMailId) {
		return select().where(new SimpleWhere().eq("memberNotifyMailId", memberNotifyMailId)).orderBy("answerTimes desc").getResultList();
	}
	
	public List<NotifyMailSendResult> findByMemberNotifyMailId(Integer memberNotifyMailId, int offset, int limit) {
		return select().where(new SimpleWhere().eq("memberNotifyMailId", memberNotifyMailId)).orderBy("updateDate desc").offset(offset).limit(limit).getResultList();
	}

	public List<NotifyMailSendResult> findByMessageId(String messageId) {
		return select().where(new SimpleWhere().eq("messageId", messageId)).getResultList();
	}

	public EnqueteMail findByMemberNotifyMailIdExchangeEnqueteMail(Integer memberNotifyMailId) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT ");
		sb.append("     mail.member_notify_mail_id AS member_notify_mail_id ");
		sb.append("     ,mail.subject AS subject ");
		sb.append("     ,MAX (result.insert_date) AS delivery_date ");
		sb.append("     ,COUNT(*) AS delivery_total_num ");
		sb.append("     ,SUM (CASE WHEN COALESCE (send_result_cd ,0) = 0 THEN 1 ELSE 0 END) AS delivery_success_num ");
		sb.append("     ,SUM (CASE WHEN COALESCE (send_result_cd ,0) != 0 THEN 1 ELSE 0 END) AS delivery_fail_num ");
		sb.append("     ,MAX (result.update_date) AS update_date ");
		sb.append(" FROM ");
		sb.append("     t_notify_mail_send_result result ");
		sb.append("     INNER JOIN t_member_notify_mail mail ");
		sb.append("     ON  mail.member_notify_mail_id = result.member_notify_mail_id ");
		sb.append(" WHERE ");
		sb.append("     mail.member_notify_mail_id = ? ");
		sb.append(" GROUP BY ");
		sb.append("     mail.member_notify_mail_id ");
		return jdbcManager.selectBySql(EnqueteMail.class, sb.toString(), memberNotifyMailId).getSingleResult();
	}
}