package jp.agentec.sinaburocast.service; import java.io.UnsupportedEncodingException; import java.net.URLEncoder; import java.text.MessageFormat; import java.text.ParseException; import java.util.ArrayList; import java.util.Date; import java.util.Hashtable; import java.util.List; import javax.ejb.TransactionAttribute; import javax.ejb.TransactionAttributeType; import javax.mail.MessagingException; import javax.transaction.UserTransaction; 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.entity.AdminNotifyMail; import jp.agentec.sinaburocast.entity.Answer; import jp.agentec.sinaburocast.entity.Enquete; import jp.agentec.sinaburocast.entity.Member; import jp.agentec.sinaburocast.entity.MemberNotifyMail; import jp.agentec.sinaburocast.entity.NotifyMailSendResult; import jp.agentec.sinaburocast.entity.PointGet; import jp.agentec.sinaburocast.entity.Question; import jp.agentec.sinaburocast.entity.Reply; import jp.agentec.sinaburocast.form.user.EnqueteForm; import jp.agentec.sinaburocast.helper.KeyHelper; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import org.seasar.extension.jdbc.where.SimpleWhere; import org.seasar.framework.beans.util.BeanMap; import org.seasar.framework.beans.util.Beans; 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 ReplyService extends AbstractService<Reply> { private final Logger logger = Logger.getLogger(getClass()); public static final String ID_SEQ_NAME = "reply_id_seq"; public MemberNotifyMailService memberNotifyMailService; public NotifyMailSendResultService notifyMailSendResultService; public PointGetService pointGetService; public MemberService memberService; public EnqueteService enqueteService; public QuestionService questionService; public UserTransaction userTransaction; public KeyHelper keyHelper; /** * IDを発行して、登録する。 */ public int insertReply(Reply reply, String insId) { reply.replyId = (long)getSeqNextVal(Integer.class, ID_SEQ_NAME); return super.insert(reply, insId); } public Reply findById(Long replyId) { return select().id(replyId).getSingleResult(); } public Reply findBymemberId(Integer memberId) { return select().where(new SimpleWhere().eq("memberId", memberId)).getSingleResult(); } public List<Reply> findAllOrderById() { return select().orderBy("replyId asc").getResultList(); } public List<Reply> findBymemberIdList(Integer memberId) { return select().where(new SimpleWhere().eq("memberId", memberId)).getResultList(); } public List<Reply> findByQuestionIdList(Integer questionId) { return select().where(new SimpleWhere().eq("questionId", questionId)).getResultList(); } /** * アンケート回答回数取得 * @return アンケート回答回数 */ public int getAnswerTimes(Integer memberId,Integer enqueteId) { String sql = "select COALESCE(max(answer_times),0) from t_reply where member_id= ? and enquete_id = ? "; return jdbcManager.selectBySql(Integer.class, sql,memberId,enqueteId).getSingleResult(); } public int getAnswerTimes(Integer enqueteId) { String sql = "select COALESCE(max(answer_times),0) from t_reply where member_id is null and enquete_id = ? "; return jdbcManager.selectBySql(Integer.class, sql,enqueteId).getSingleResult(); } /** * アンケート回答結果を取得する。 * アンケート回答メールでURL謳歌した場合。 * ★ソート重要★ * @return */ public List<Reply> getReplyList(Integer enqueteId,String memberId ,String answerTimes){ //return select().innerJoin("answerReply").where(new SimpleWhere().eq("enqueteId", enqueteId).eq("memberId", memberId).eq("answerTimes", answerTimes)).orderBy("answerId").getResultList(); return select().where(new SimpleWhere().eq("enqueteId", enqueteId).eq("memberId", memberId).eq("answerTimes", answerTimes)).getResultList(); } /** * アンケート回答結果を取得する。 * @return */ public List<Reply> getReplyList(Integer enqueteId,Integer memberId ,Integer answerTimes){ return select().innerJoin("question", true).innerJoin("answerReply", true).where(new SimpleWhere().eq("enqueteId", enqueteId).eq("memberId", memberId).eq("answerTimes", answerTimes)).orderBy("question.questionNo, answerReply.answerNo").getResultList(); } /** * reply(アンケート回答回答)よりAnswerを復元する。 * @param enqueteForm */ public void setReplyLogic(EnqueteForm enqueteForm){ Enquete enquete = enqueteService.findById(Integer.parseInt(enqueteForm.eid)); ArrayList<Question> questionList = questionService.findAllOrderByEnqueteId(enqueteForm.eid); List<Reply> replyList = getReplyList(enquete.enqueteId,enqueteForm.qid,enqueteForm.timeid); Hashtable<Integer, Reply> replyMap = new Hashtable<Integer, Reply>(); for (Reply reply : replyList) { replyMap.put(reply.answerId, reply); } //回答結果を設定する。 for (Question question : questionList) { for (int i = question.answerList.size()-1; i >= 0; i--) { Answer answer = question.answerList.get(i); Reply reply = null; if((reply = replyMap.get(answer.answerId)) != null){ //answer.answerIdValue = reply.answer; //回答値設定 switch (question.controlId) { case SinaburoConstant.ControlType.RADIO: case SinaburoConstant.ControlType.CHECKBOX: //チェックボックス case SinaburoConstant.ControlType.SELECTMENU://セレクトメニュー //自由入力欄 answer.answerValue=answer.answer; break; case SinaburoConstant.ControlType.TEXTAREA: //テキストエリア case SinaburoConstant.ControlType.TEXTFIELD://テキストボックス //手入力欄 answer.answerIdValue=reply.answer; break; case SinaburoConstant.ControlType.HIDUKE: //日付入力フォーム answer.answerValue=reply.answer; } }else{ question.answerList.remove(i); } } } enqueteForm.enquete = enquete; enqueteForm.questionList = questionList; } /** * * @param questionList 設問マスタリスト * @param enquete アンケート * @param isNoCheckEnquete ポイント付与をするかしないかのフラグ * @param eid アンケートID * @return * @throws ParseException */ // TODO replyRegistAndMailSendLogicとマージ public int replyRegistLogic(Member member, ArrayList<Question> questionList, Enquete enquete, Boolean isNoCheckEnquete) { Integer answerTimes = 1; if (member != null && (enquete.enqueteType == SinaburoConstant.EnqueteType.EMONITOR || enquete.enqueteType == SinaburoConstant.EnqueteType.ORGANIZATION_CONTRIBUTE)) { answerTimes = getAnswerTimes(member.memberId, enquete.enqueteId) + 1; } else { // eモニ以外は会員情報をクリアする member = new Member(); answerTimes = getAnswerTimes(enquete.enqueteId) + 1; } // ■回答内容登録 for (Question question : questionList) { for (Answer answer : question.answerList) { if (StringUtil.isBlank(answer.answerIdValue)) continue; Reply reply = new Reply(); Beans.copy(answer, reply); reply.memberId = member.memberId; reply.questionNo = question.questionNo; // 回答値設定 switch (question.controlId) { case SinaburoConstant.ControlType.RADIO: case SinaburoConstant.ControlType.CHECKBOX: // チェックボックス case SinaburoConstant.ControlType.SELECTMENU:// セレクトメニュー // 自由入力欄 reply.answer = answer.answerValue; break; case SinaburoConstant.ControlType.TEXTAREA: // テキストエリア case SinaburoConstant.ControlType.TEXTFIELD:// テキストボックス // 手入力欄 reply.answer = answer.answerIdValue; break; case SinaburoConstant.ControlType.HIDUKE: // 日付入力フォーム reply.answer = answer.answerValue; } reply.answerId = answer.answerId; reply.enqueteId = enquete.enqueteId; reply.questionId = question.questionId; reply.answerTimes = answerTimes; reply.zipCode = member.zipCode; reply.genderCd = member.genderCd; if (StringUtil.isNotBlank(member.birthday)) { try { reply.age = SinaburoUtil.getAge(member.birthday); } catch (ParseException e) { logger.error("Birthday parse failed. mamberId:" + reply.memberId + ",birthday:" + member.birthday); } } this.insertReply(reply, member.loginId); } } if (!isNoCheckEnquete) { // ■ポイント支給 pointGetテーブル、memberテーブル更新 PointGet pointGet = new PointGet(); pointGet.memberId = member.memberId; pointGet.pointGetDate = SinaburoUtil.getToDayString(); pointGet.pointGetNum = enquete.pointNum; pointGet.pointUseNum = 0; pointGet.validFlg = SinaburoConstant.ValidFlg.VALID; pointGetService.insertPointGet(pointGet, member.loginId); member.pointNum += enquete.pointNum; memberService.update(member); } return 1; } /** * * @param questionList 設問マスタリスト * @param enquete アンケート * @param isNoCheckEnquete TODO * @param eid アンケートID * @return * @throws Exception * @throws ParseException */ @TransactionAttribute(TransactionAttributeType.NEVER) public int replyRegistAndMailSendLogic(Member member,ArrayList<Question> questionList,Enquete enquete, Boolean isNoCheckEnquete) throws Exception { Integer answerTimes = 1; String inputMailAddress = null; // answerTimesが同じものが登録されてしまうことがあるのでsynchronizedにする。 // TODO サーバが複数台ある場合この方法ではブロックできないので管理用のテーブルを作成して参照するなど構造を見直す必要があります。 synchronized (answerTimes) { userTransaction.begin(); if (member != null && enquete.enqueteType == SinaburoConstant.EnqueteType.EMONITOR) { answerTimes = getAnswerTimes(member.memberId, enquete.enqueteId) + 1; } else { // eモニ以外は会員情報をクリアする member = new Member(); answerTimes = getAnswerTimes(enquete.enqueteId) + 1; } // ■回答内容登録 for (Question question : questionList) { for (Answer answer : question.answerList) { if(StringUtil.isBlank(answer.answerIdValue))continue; Reply reply = new Reply(); Beans.copy(answer, reply); reply.memberId = member.memberId; reply.questionNo = question.questionNo; //回答値設定 switch (question.controlId) { case SinaburoConstant.ControlType.RADIO: case SinaburoConstant.ControlType.CHECKBOX: //チェックボックス case SinaburoConstant.ControlType.SELECTMENU://セレクトメニュー //自由入力欄 reply.answer = answer.answerValue; break; case SinaburoConstant.ControlType.TEXTAREA: //テキストエリア case SinaburoConstant.ControlType.TEXTFIELD://テキストボックス //手入力欄 reply.answer = answer.answerIdValue; if(question.mailType == 1){ inputMailAddress = answer.answerIdValue; } break; case SinaburoConstant.ControlType.HIDUKE: //日付入力フォーム reply.answer = answer.answerValue; } reply.answerId = answer.answerId; reply.enqueteId = enquete.enqueteId; reply.questionId = question.questionId; reply.answerTimes = answerTimes; reply.zipCode = member.zipCode; reply.genderCd=member.genderCd; if(StringUtil.isNotBlank(member.birthday)){ try { reply.age=SinaburoUtil.getAge(member.birthday); } catch (ParseException e) { logger.error("Birthday parse failed. mamberId:" + reply.memberId + ",birthday:" + member.birthday); } } this.insertReply(reply, member.loginId); } } if (!isNoCheckEnquete) { // ■ポイント支給 pointGetテーブル、memberテーブル更新 PointGet pointGet = new PointGet(); pointGet.memberId = member.memberId; pointGet.pointGetDate = SinaburoUtil.getToDayString(); pointGet.pointGetNum = enquete.pointNum; pointGet.pointUseNum = 0; pointGet.validFlg = SinaburoConstant.ValidFlg.VALID; pointGetService.insertPointGet(pointGet, member.loginId); member.pointNum += enquete.pointNum; memberService.update(member); } userTransaction.commit(); } if(enquete.enqueteType == SinaburoConstant.EnqueteType.EMONITOR || enquete.enqueteType == SinaburoConstant.EnqueteType.PUBLIC_COMMENT || enquete.enqueteType == SinaburoConstant.EnqueteType.RECRUITMENT || enquete.enqueteType == SinaburoConstant.EnqueteType.WEB_ENQUETE){ //■メール送信 BeanMap wehreMap = new BeanMap(); wehreMap.put("enqueteId", enquete.enqueteId); wehreMap.put("notifyMailSendFlg", SinaburoConstant.notifyMailSendFlg.SEND); // e-モニアンケートの場合、利用者にメール送信 if(enquete.enqueteType == SinaburoConstant.EnqueteType.EMONITOR){ //1件のみのはず List<MemberNotifyMail> memberNotifyMailList = memberNotifyMailService.findByCondition(wehreMap); for (MemberNotifyMail memberNotifyMail : memberNotifyMailList) { String enqueteEncodeId = ""; try { enqueteEncodeId = URLEncoder.encode(keyHelper.encryptEnqueteId(enquete.enqueteId) ,"utf-8"); } catch (Exception e) { logger.error("EnqueteId encode failed." + e.toString()); } String mailbody = MessageFormat.format( memberNotifyMail.mailBody+"\r\n"+ PropertyUtil.getString("ENQUETE_CONFIRM_URL"), enqueteEncodeId); //ユーザーに送信 if(StringUtil.isNotBlank(member.pcEmail)){ try { MailUtil.send(member.pcEmail, memberNotifyMail.subject, mailbody); } catch (UnsupportedEncodingException e) { logger.error("Mail encode failed." + e.toString()); } catch (MessagingException e) { logger.error("Mail send failed." + e.toString()); } } if(StringUtil.isNotBlank(member.mbEmail)){ try{ MailUtil.send(member.mbEmail, memberNotifyMail.subject,mailbody); } catch (UnsupportedEncodingException e) { logger.error("Mail encode failed." + e.toString()); } catch (MessagingException e) { logger.error("Mail send failed." + e.toString()); } } //BCCに送信 if(StringUtil.isNotBlank(memberNotifyMail.notifyMailSendBcc)){ try{ MailUtil.send(memberNotifyMail.notifyMailSendBcc,memberNotifyMail.subject, mailbody); } catch (UnsupportedEncodingException e) { logger.error("Mail encode failed." + e.toString()); } catch (MessagingException e) { logger.error("Mail send failed." + e.toString()); } } } } else if(enquete.enqueteType == SinaburoConstant.EnqueteType.WEB_ENQUETE){ List<MemberNotifyMail> memberNotifyMailList = memberNotifyMailService.findByCondition(wehreMap); for (MemberNotifyMail memberNotifyMail : memberNotifyMailList) { if (StringUtil.isNotBlank(inputMailAddress)) { NotifyMailSendResult notifyMailSendResult = new NotifyMailSendResult(); notifyMailSendResult.memberNotifyMailId = memberNotifyMail.memberNotifyMailId; notifyMailSendResult.answerTimes = answerTimes; notifyMailSendResult.email = inputMailAddress; notifyMailSendResult.sendResultCd = 0; try { // 送信結果テーブルに登録 notifyMailSendResult.messageId = MailUtil.send(inputMailAddress, memberNotifyMail.subject, memberNotifyMail.mailBody); } catch (UnsupportedEncodingException e) { logger.error("Mail encode failed." + e.toString()); } catch (MessagingException e) { logger.error("Mail send failed." + e.toString()); if (e.toString().contains("SMTPAddressFailedException:")) { notifyMailSendResult.sendResultCd = Integer.valueOf(SinaburoUtil.extractRegexString(e.toString(), "SMTPAddressFailedException: (\\d+) ", 1)); notifyMailSendResult.statusCd = SinaburoUtil.extractRegexString(e.toString(), "SMTPAddressFailedException: \\d+ ([^\\s]+)", 1); notifyMailSendResult.sendResult = SinaburoUtil.extractRegexString(e.toString(), "SMTPAddressFailedException: [^:]+: (.+)$", 1); } else if (e.toString().contains("java.net.ConnectException:")) { notifyMailSendResult.sendResultCd = 451; notifyMailSendResult.sendResult = SinaburoUtil.extractRegexString(e.toString(), "java.net.ConnectException: (.+)$", 1); } else { notifyMailSendResult.sendResultCd = 999; } notifyMailSendResult.messageId = "FAILED"; } notifyMailSendResultService.insertNotifyMailSendResult(notifyMailSendResult, ""); } // BCCに送信 if (StringUtil.isNotBlank(memberNotifyMail.notifyMailSendBcc)) { try { MailUtil.send(memberNotifyMail.notifyMailSendBcc, memberNotifyMail.subject, memberNotifyMail.mailBody); } catch (UnsupportedEncodingException e) { logger.error("Mail encode failed." + e.toString()); } catch (MessagingException e) { logger.error("Mail send failed." + e.toString()); } } } } List<AdminNotifyMail> adminNotifyMailList = adminNotifyMailService.findByCondition(wehreMap); StringBuffer adminMailBody = new StringBuffer(); if(adminNotifyMailList != null && adminNotifyMailList.size() > 0){ adminMailBody.append(adminNotifyMailList.get(0).mailBody); adminMailBody.append("\r\n\r\n"); adminMailBody.append("アンケート名: "); adminMailBody.append(enquete.enqueteName); List<Reply> replyList = getReplyList(enquete.enqueteId, member.memberId, answerTimes); Integer prequestionId = null; for(Reply reply : replyList){ if(prequestionId == null || prequestionId != reply.question.questionId){ adminMailBody.append("\r\n"); adminMailBody.append(reply.question.questionName); adminMailBody.append(": "); } else { adminMailBody.append(","); } //回答値設定 switch (reply.question.controlId) { case SinaburoConstant.ControlType.RADIO: case SinaburoConstant.ControlType.CHECKBOX: //チェックボックス case SinaburoConstant.ControlType.SELECTMENU://セレクトメニュー //自由入力欄 adminMailBody.append(reply.answerReply.answer); break; case SinaburoConstant.ControlType.TEXTAREA: //テキストエリア case SinaburoConstant.ControlType.TEXTFIELD://テキストボックス case SinaburoConstant.ControlType.HIDUKE: //日付入力フォーム //手入力欄 adminMailBody.append(reply.answer); break; } prequestionId = reply.question.questionId; } } for (AdminNotifyMail adminNotifyMail : adminNotifyMailList) { if(StringUtil.isNotBlank(adminNotifyMail.notifyMailSendTo)){ String [] bccAdress = adminNotifyMail.notifyMailSendTo.split(","); for(int i=0;i<bccAdress.length;i++){ try{ MailUtil.send(bccAdress[i],adminNotifyMail.subject, adminMailBody.toString()); } catch (UnsupportedEncodingException e) { logger.error("Mail encode failed." + e.toString()); } catch (MessagingException e) { logger.error("Mail send failed." + e.toString()); } } } if(StringUtil.isNotBlank(adminNotifyMail.notifyMailSendCc)){ String [] bccAdress = adminNotifyMail.notifyMailSendCc.split(","); for(int i=0;i<bccAdress.length;i++){ try{ MailUtil.send(bccAdress[i],adminNotifyMail.subject ,adminMailBody.toString()); } catch (UnsupportedEncodingException e) { logger.error("Mail encode failed." + e.toString()); } catch (MessagingException e) { logger.error("Mail send failed." + e.toString()); } } } } } return 1; } public AdminNotifyMailService adminNotifyMailService; public List<Reply> findAllByEnqueteId(String eid) { return select().where(new SimpleWhere().eq("enqueteId",eid)).getResultList(); } /** * アンケート検索画面で削除ボタンを押した場合。 * @param eid アンケートID * @return */ public int deleteByEnqueteId(String eid){ List<Reply> list = findAllByEnqueteId(eid); if(!list.isEmpty()){ jdbcManager.deleteBatch(list).execute(); } return 0; } /** * 設問一覧で削除した場合 * @param questionId * @param loginId * @return */ public int deleteByquestionId(Integer questionId,String loginId){ List<Reply> replyList = this.findByQuestionIdList(questionId); for (Reply reply : replyList) { delete(reply); } return 0; } /** * アンケートIdの回答件数を求める。 * @param eid * @return */ public Long getCountByEnqueteId(Integer eid){ return select().where(new SimpleWhere().eq("enqueteId", eid)).getCount(); } /** * アンケート回答者数を数える。WEB、REC、募集 * @param enqueteId * @return */ public Integer getAnswerCount(Integer enqueteId){ String sql="select max(answer_times) from t_reply where enquete_id=?"; Integer result = jdbcManager.selectBySql(Integer.class,sql, enqueteId).getSingleResult(); return result == null ? 0 :result; //return select().where(new SimpleWhere().eq("enqueteId", enqueteId)).getCount(); } /** * 指定したアンケートに回答があるか返す。 * * @param enqueteId * @return */ public boolean isExistAnswer(Integer enqueteId){ return getCountByEnqueteId(enqueteId) > 0; } /** * アンケート回答にアンケートIDの不整合を検知した場合 * アラートメールを送信する * * @param enqueteId * @param loginId */ public void sendReplyAlertMail(String enqueteId, String chkEnqueteId, String loginId){ logger.info("SendReplyAlertMail enqueteId:" + enqueteId + " chkEnqueteId:" + chkEnqueteId + " loginId:" + loginId); String subject = "ENQUETE REPLY ALERT"; StringBuffer body = new StringBuffer(""); body.append("eモニアンケート回答結果に不整合異常を検知しました。\r\n"); body.append("\r\n"); body.append("検知日時:" + SinaburoUtil.dateToString(new Date(System.currentTimeMillis()))); body.append("\r\n"); body.append("アンケートID:" + enqueteId); body.append("\r\n"); body.append("ログインID:" + loginId); body.append("\r\n"); body.append("\r\n"); body.append("管理者に問い合わせてください。\r\n"); String [] toAdress = PropertyUtil.getString("ENQUETE_REPLY_ALERT_MAIL").split(","); for(int i=0;i<toAdress.length;i++){ try{ if (!StringUtils.isEmpty(toAdress[i])){ MailUtil.send(toAdress[i], subject, body.toString()); } } catch (UnsupportedEncodingException e) { logger.error("Mail encode failed." + e.toString()); } catch (MessagingException e) { logger.error("Mail send failed." + e.toString()); } } } }