EnqueteMailService.java.svn-base

package jp.agentec.sinaburocast.service;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.mail.MessagingException;

import jp.agentec.sinaburocast.common.SinaburoConstant;
import jp.agentec.sinaburocast.common.util.MailUtil;
import jp.agentec.sinaburocast.common.util.PropertyUtil;
import jp.agentec.sinaburocast.common.util.SinaburoUtil;
import jp.agentec.sinaburocast.csv.MailCsv;
import jp.agentec.sinaburocast.dto.MailSendResultDto;
import jp.agentec.sinaburocast.entity.DeliveryGroupMember;
import jp.agentec.sinaburocast.entity.EnqueteMail;
import jp.agentec.sinaburocast.entity.EnqueteMailSendResult;
import jp.agentec.sinaburocast.entity.Member;
import jp.agentec.sinaburocast.entity.MemberNotifyMail;
import jp.agentec.sinaburocast.form.admin.mail.MailResultForm;
import jp.agentec.sinaburocast.form.admin.mail.MailSearchForm;
import jp.agentec.sinaburocast.helper.KeyHelper;
import jp.agentec.sinaburocast.service.csv.CsvService;

import org.apache.commons.io.IOUtils;
import org.apache.log4j.Logger;
import org.apache.struts.upload.FormFile;
import org.seasar.extension.jdbc.AutoSelect;
import org.seasar.framework.container.annotation.tiger.Component;
import org.seasar.framework.container.annotation.tiger.InstanceType;
import org.seasar.framework.util.StringUtil;

@Component(instance=InstanceType.SINGLETON)
public class EnqueteMailService extends AbstractService<EnqueteMail> {
	private final Logger logger = Logger.getLogger(getClass());

	private static final String ID_SEQ_NAME = "enquete_mail_id_seq";

	public DeliveryGroupMemberService deliveryGroupMemberService;

	public EnqueteMailSendResultService enqueteMailSendResultService;

	public MemberNotifyMailService memberNotifyMailService;

	public CsvService<MailCsv> csvService;

	public EnqueteMailAddressTmpService enqueteMailAddressTmpService;

	public KeyHelper keyHelper;


	/**
	 * アンケートメール検索
	 * @param enqueteMailId
	 * @return
	 */
    public EnqueteMail findById(Integer enqueteMailId) {
        return select().id(enqueteMailId).getSingleResult();
    }

    /**
     * 検索及びページング
     * @param pageKey
     * @param curPage
     * @param mailSearchForm
     * @return
     */
    public List<EnqueteMail> findAllOrderById(String pageKey,Integer curPage,MailSearchForm mailSearchForm) {

		boolean flg = false;
		StringBuffer sql = new StringBuffer();
		List<Object> values = new ArrayList<Object>();

		if (!StringUtil.isBlank(mailSearchForm.mailSubject.trim())) {
			sql.append(" subject like ? ");
			values.add("%"+mailSearchForm.mailSubject.trim().replace("%", "\\%")+"%");
			flg = true;
		}

		if (!StringUtil.isBlank(mailSearchForm.fromDay)) {
			if (flg) sql.append(" and ");
			sql.append(" to_char (insert_date, 'yyyyMMdd') >= ? ");
			values.add(mailSearchForm.fromDay.replaceAll("/", ""));
			flg = true;
		}

		if (!StringUtil.isBlank(mailSearchForm.toDay)) {
			if (flg) sql.append(" and ");
			sql.append(" to_char (insert_date, 'yyyyMMdd') <= ? ");
			values.add(mailSearchForm.toDay.replaceAll("/", ""));
			flg = true;
		}
		if (mailSearchForm.backFlg) {
			if (flg) sql.append(" and ");
			sql.append(" insert_date > current_timestamp + '-1 month' ");
			flg = true;

		}

		AutoSelect<EnqueteMail> resultCnt;
		if (flg) {
			resultCnt= select().where(sql.toString(),values.toArray());
		} else {
			resultCnt= select();
		}
		mailSearchForm.setPaging(null, curPage, resultCnt.getCount());

		if (flg) {
			return select().where(sql.toString(),values.toArray())
					.orderBy("insert_date desc").offset(mailSearchForm.getOffSet(pageKey))
					.limit(mailSearchForm.getLimit(pageKey)).getResultList();
		} else  {
			return  select().orderBy("insert_date desc").offset(mailSearchForm.getOffSet(pageKey))
					.limit(mailSearchForm.getLimit(pageKey)).getResultList();
		}
    }


    /**
     * メール送信結果一覧検索
     * @param pageKey
     * @param curPage
     * @param mailResultForm
     * @return
     */
	public List<MailSendResultDto> findAllOrderByIdResult(String pageKey, Integer curPage, MailResultForm mailResultForm) {
		Object[] values;

		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT ");
		sb.append("     * ");
		sb.append(" FROM ");
		sb.append("     ( ");
		sb.append("         SELECT ");
		sb.append("             enquete_mail_id ");
		sb.append("             ,subject ");
		sb.append("             ,delivery_date ");
		sb.append("             ,delivery_total_num ");
		sb.append("             ,delivery_success_num ");
		sb.append("             ,delivery_fail_num ");
		sb.append("             ,update_date ");
		sb.append("             ,CASE ");
		sb.append("                 WHEN enquete_id IS null THEN 0 ");
		sb.append("                 ELSE 1 ");
		sb.append("             END AS TYPE ");
		sb.append("         FROM ");
		sb.append("             t_enquete_mail ");
		sb.append("         WHERE ");
		sb.append("             status = 1 ");
		sb.append("         UNION ALL ");
		sb.append("         SELECT ");
		sb.append("             mail.member_notify_mail_id AS member_notify_mail_id ");
		sb.append("             ,'' 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("             ,2 AS TYPE ");
		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("         GROUP BY ");
		sb.append("             mail.member_notify_mail_id ");
		sb.append("     ) mail_result ");
		if (mailResultForm.selectType != -1) {
			sb.append("     WHERE type = ? ");
			values = new Object[] { mailResultForm.selectType };
		} else {
			values = new Object[] {};
		}

		sb.append(" ORDER BY ");
		sb.append("     update_date desc ");

		long resultCnt = jdbcManager.getCountBySql(sb.toString(), values);
		mailResultForm.setPaging(null, curPage, resultCnt);
		List<MailSendResultDto> mailSendResultDtoList = jdbcManager.selectBySql(MailSendResultDto.class, sb.toString(), values).limit(mailResultForm.getLimit(pageKey)).offset(mailResultForm.getOffSet(pageKey)).getResultList();

		for (MailSendResultDto dto : mailSendResultDtoList) {
			if (dto.type == 2) {
				MemberNotifyMail mail = memberNotifyMailService.findById(dto.enqueteMailId);
				if (mail != null) {
					dto.subject = mail.enquete.enqueteName;
				}
			}
		}

		return mailSendResultDtoList;
	}

    /**
     * IDを発行して、登録する。
     * @param enqueteMail
     * @param insId
     * @return
     */
	public int insertEnqueteMail(EnqueteMail enqueteMail, String insId) {
		enqueteMail.enqueteMailId = getSeqNextVal(Integer.class, ID_SEQ_NAME);
		int result = super.insert(enqueteMail, insId);
		if (result != 1) {
			enqueteMail.enqueteMailId = null;
		}
		return enqueteMail.enqueteMailId;
	}


	@Resource
	protected Map requestScope;

	/**
	 * 即時送信の場合、メール送信
	 * @param member
	 * @param email
	 * @param url
	 * @return
	 * @throws Exception
	 */
	public int insertRegistMailSendLogic(EnqueteMail enqueteMail,String insId,String enqUrl, List<DeliveryGroupMember> list)throws Exception {

    	int retValue = 0;
    	int enqueteMailId;

 		//登録
    	if (enqueteMail.enqueteMailId == null) {
        	enqueteMail.deliveryDate = SinaburoUtil.getTimestamp();
    		retValue = insertEnqueteMail(enqueteMail,insId);
    		enqueteMailId = retValue;
    	} else {
    		EnqueteMail info = findById(enqueteMail.enqueteMailId);
    		enqueteMail.deliveryDate = info.deliveryDate;
			enqueteMail.version = info.version;
			enqueteMail.insertDate = info.insertDate;
			enqueteMail.insId = info.insId;
    		retValue = update(enqueteMail,insId);
    		enqueteMailId = enqueteMail.enqueteMailId;
    	}

    	int mailTotalNum = 0;
    	int deliveryFailNum = 0;
    	//メール送信
		for (DeliveryGroupMember member : list) {
			String email = null;
			if (enqueteMail.deliveryTargetType.equals(SinaburoConstant.DeliveryTargetType.PC)) {
				if (StringUtil.isBlank(member.member.pcEmail)) {
					continue;
				}
				email = member.member.pcEmail;
			} else {
				if (StringUtil.isBlank(member.member.mbEmail)) {
					continue;
				}
				email = member.member.mbEmail;
			}

			String contents = enqueteMail.mailBody;

			String name = null;
			if (!StringUtil.isBlank(member.member.firstName) || !StringUtil.isBlank(member.member.lastName)) {
				name = (StringUtil.isBlank(member.member.firstName) ? "" : member.member.firstName) + " " + (StringUtil.isBlank(member.member.lastName) ? "" : member.member.lastName);
			} else {
				name = "eモニ会員";
			}
			contents = contents.replaceAll("\\%memberName\\%", name);
			contents = contents.replaceAll("\\%loginId\\%", member.member.loginId);
			contents = contents.replaceAll("\\%passWord\\%", member.member.password);
			if (member.member.memberId != null) {
				contents = contents.replaceAll("\\%memberId\\%", member.member.memberId.toString());
			}
			contents = contents.replaceAll("\\%pointNum\\%", member.member.pointNum.toString());
			int next = 0;
			if (contents.indexOf("%enqUrl") >= 0) {

				String month = PropertyUtil.getProperty("ENQUETE_KEY_MONTH");
				String day = PropertyUtil.getProperty("ENQUETE_KEY_DAY");
				String hour = PropertyUtil.getProperty("ENQUETE_KEY_HOUR");

				do {
					String enqId = contents.substring(contents.indexOf("%enqUrl") + 7, contents.indexOf("%", contents.indexOf("%enqUrl") + 7));
					next = contents.indexOf("%enqUrl") + 7;
					String key = keyHelper.encrypt(member.member.memberId, Integer.parseInt(enqId), Integer.parseInt(month), Integer.parseInt(day), Integer.parseInt(hour));
					String serverUrl = enqUrl + "/em/user/enquete/?qUrlId=" + key;

					contents = contents.replaceAll("\\%enqUrl" + enqId + "\\%", serverUrl);
				} while (contents.indexOf("%", next) >= 0);
			}

			String title = enqueteMail.subject;
			StringBuilder body = new StringBuilder();
			body.append(contents);

			// メール送信
			EnqueteMailSendResult enqueteMailSendResult = new EnqueteMailSendResult();
			enqueteMailSendResult.enqueteMailId = enqueteMailId;
			enqueteMailSendResult.memberId = member.member.memberId;
			enqueteMailSendResult.email = email;
			enqueteMailSendResult.sendResultCd = 0;
			try {
				enqueteMailSendResult.messageId = MailUtil.send(email, title, body.toString());
			} catch (MessagingException e) {
				deliveryFailNum++;
				logger.error("Mail send failed." + e.toString());
				if (e.toString().contains("SMTPAddressFailedException:")) {
					enqueteMailSendResult.sendResultCd = Integer.valueOf(SinaburoUtil.extractRegexString(e.toString(), "SMTPAddressFailedException: (\\d+) ", 1));
					enqueteMailSendResult.statusCd = SinaburoUtil.extractRegexString(e.toString(), "SMTPAddressFailedException: \\d+ ([^\\s]+)", 1);
					enqueteMailSendResult.sendResult = SinaburoUtil.extractRegexString(e.toString(), "SMTPAddressFailedException: [^:]+: (.+)$", 1);
				} else if (e.toString().contains("java.net.ConnectException:")) {
					enqueteMailSendResult.sendResultCd = 451;
					enqueteMailSendResult.sendResult = SinaburoUtil.extractRegexString(e.toString(), "java.net.ConnectException: (.+)$", 1);
				} else {
					enqueteMailSendResult.sendResultCd = 999;
				}
				enqueteMailSendResult.messageId = "FAILED";
			}

			enqueteMailSendResultService.insertEnqueteMail(enqueteMailSendResult, insId);
			mailTotalNum++;
		}
    	
    	//送信件数更新
		EnqueteMail info = findById(enqueteMailId);
		enqueteMail.version = info.version;
		enqueteMail.insertDate = info.insertDate;
		enqueteMail.insId = info.insId;
		enqueteMail.deliveryTotalNum = mailTotalNum;
		enqueteMail.deliverySuccessNum = mailTotalNum - deliveryFailNum;
		enqueteMail.deliveryFailNum = deliveryFailNum;
		enqueteMail.status = SinaburoConstant.MailStatusType.COMPLETE;
		if (info.deliveryTargetMethod.equals(SinaburoConstant.DeliveryMethodType.IMMEDIATE+"")) {
			enqueteMail.deliveryDate = SinaburoUtil.getTimestamp();
		}
		retValue = update(enqueteMail,insId);

    	return retValue;
    }

	public List<DeliveryGroupMember> fileRead(FormFile file) throws Exception {
		List<DeliveryGroupMember> deliveryGroupMemberList = new ArrayList<DeliveryGroupMember>();
		StringBuffer isOk = new StringBuffer("OK");
		InputStream in = null;
		try {
			in = file.getInputStream();
			//20170508 SJIS文字化け対応
			String csvData = IOUtils.toString(in, "Windows-31J");
			//String csvData = IOUtils.toString(in, "Shift_JIS");
			List<MailCsv> list = csvService.parseAll(csvData, MailCsv.class, isOk);
			for (MailCsv csv : list) {
				DeliveryGroupMember deliveryGroupMember = new DeliveryGroupMember();
				Member member = new Member();
				member.pcEmail = csv.mailAddress;
				member.mbEmail = csv.mailAddress;
				member.firstName = "";
				member.lastName = "";
				member.loginId = "";
				member.password = "";
				member.memberId = null;
				member.pointNum = 0;
				deliveryGroupMember.member = member;
				deliveryGroupMemberList.add(deliveryGroupMember);
			}
		} finally {
			IOUtils.closeQuietly(in);
		}
		return deliveryGroupMemberList;
	}

	/**
	 * アンケートメール削除
	 * @param member
	 * @param id
	 * @return
	 */
	public int deleteMail(EnqueteMail enqueteMail) {
		int result = 0;
		List<EnqueteMailSendResult> deleteInfo = enqueteMailSendResultService.findAllOrderById(enqueteMail.enqueteMailId);

		for (EnqueteMailSendResult infoDelete : deleteInfo) {
			enqueteMailSendResultService.delete(infoDelete);
		}

		enqueteMailAddressTmpService.deleteByEnqueteMailId(enqueteMail.enqueteMailId);

		EnqueteMail info = findById(enqueteMail.enqueteMailId);
		result = super.delete(info);

		return result;
	}

	/**
	 * メール予約送信バッチで対象アンケート検索
	 * @return
	 */
	 public List<EnqueteMail> findAllBatch() {
		StringBuffer sql = new StringBuffer();
		List<Object> values = new ArrayList<Object>();

		sql.append(" delivery_date <= current_timestamp and status =? ");
		values.add(0);

		return select().where(sql.toString(),values.toArray())
				.orderBy("delivery_date").getResultList();

	 }
}