package jp.agentec.sinaburocast.service; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.sql.Timestamp; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Properties; import java.util.TreeMap; import jp.agentec.sinaburocast.common.SinaburoConstant; import jp.agentec.sinaburocast.common.SinaburoConstant.EnqueteStatusType; import jp.agentec.sinaburocast.common.exception.ExpectedException; import jp.agentec.sinaburocast.common.exception.SystemException; import jp.agentec.sinaburocast.common.io.FileUtil; import jp.agentec.sinaburocast.common.util.PropertyUtil; import jp.agentec.sinaburocast.common.util.SinaburoUtil; import jp.agentec.sinaburocast.common.util.velocity.TemplateUtil; import jp.agentec.sinaburocast.entity.AdminNotifyMail; import jp.agentec.sinaburocast.entity.AdminUser; import jp.agentec.sinaburocast.entity.Answer; import jp.agentec.sinaburocast.entity.Enquete; import jp.agentec.sinaburocast.entity.EnqueteSummary; import jp.agentec.sinaburocast.entity.Member; import jp.agentec.sinaburocast.entity.MemberNotifyMail; import jp.agentec.sinaburocast.entity.Prefecture; import jp.agentec.sinaburocast.entity.Question; import jp.agentec.sinaburocast.form.admin.enquete.EnqueteRegistForm; import jp.agentec.sinaburocast.form.admin.enquete.EnqueteSearchForm; import jp.agentec.sinaburocast.form.batch.EnqueteSumBatchForm; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import org.apache.velocity.VelocityContext; import org.apache.velocity.tools.generic.DateTool; import org.apache.velocity.tools.generic.MathTool; import org.seasar.extension.jdbc.AutoSelect; 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 EnqueteService extends AbstractService<Enquete> { private final Logger logger = Logger.getLogger(getClass()); public static final String ID_SEQ_NAME = "enquete_id_seq"; public ReplyService replyService; public AdminNotifyMailService adminNotifyMailService; public MemberNotifyMailService memberNotifyMailService; public AnswerService answerService; public QuestionService questionService; public EnqueteSummaryService enqueteSummaryService; public MemberService memberService; public PrefectureService prefectureService; private static final String CSV_MAKE_FILE_PATH = PropertyUtil.getProperty(SinaburoConstant.filePath.CSV_MAKE_FILE_PATH); private static final String RESULT_PATH_PC = PropertyUtil.getString("ENQUETE_RESULT")+"user"; private static final String RESULT_PATH_MB = PropertyUtil.getString("ENQUETE_RESULT")+"mb"; private static final String RESULT_PATH_SP = PropertyUtil.getString("ENQUETE_RESULT")+"sp"; /** * IDを発行して、登録する。 */ public int insertEnquete(Enquete enquete, String insId) { enquete.enqueteId = getSeqNextVal(Integer.class, ID_SEQ_NAME); return super.insert(enquete, insId); } public Enquete findById(Integer enqueteId) { return select().id(enqueteId).getSingleResult(); } // public List<Enquete> findAllOrderById() { // return select().orderBy("enqueteId asc").getResultList(); // } public List<Enquete> findEnqueteSummaryById() { return select().innerJoin("enqueteSummary").where(new SimpleWhere() .isNotNull("enqueteSummary.outputHtmlFilename", true) .isNotNull("enqueteSummary.outputMbHtmlFilename", true) .isNotNull("enqueteSummary.outputSpHtmlFilename", true) .eq("dispFlg", SinaburoConstant.MemberValidFlg.VALID) .eq("delFlg", SinaburoConstant.MemberDelFlg.NOT_DEL) .eq("enqueteType",SinaburoConstant.EnqueteType.EMONITOR)) .orderBy("startDate desc").getResultList(); } public List<Enquete> findEnqueteSummaryByYmd(String yyyy) { return select().innerJoin("enqueteSummary").where("substr(start_date,1,4) = ? and disp_flg = ? and del_flg = ? ", yyyy, Integer.parseInt(SinaburoConstant.MemberValidFlg.VALID), Integer.parseInt(SinaburoConstant.MemberDelFlg.NOT_DEL)) .orderBy("startDate desc").getResultList(); } public List<Enquete> findEnqueteByType(String yyyy, int type) { return select() .where("end_date >= ? and start_date <= ? and enquete_type = ? and disp_flg = ? and del_flg = ? and status = ? " , yyyy, yyyy, type, Integer.parseInt(SinaburoConstant.MemberValidFlg.VALID), Integer.parseInt(SinaburoConstant.MemberDelFlg.NOT_DEL),SinaburoConstant.EnqueteStatusType.ENFORCEMENT) .orderBy("startDate desc").getResultList(); } /** * 実施中のアンケートを取得する。 * @param yyyy * @param type * @return */ public List<Enquete> findEnqueteByTypeEnquete(String yyyy, int type) { return select() .where("T1_.end_date >= ? and T1_.start_date <= ? and T1_.enquete_type = ? and T1_.disp_flg = ? and T1_.del_flg = ? AND T1_.status = ?" , yyyy, yyyy, type, Integer.parseInt(SinaburoConstant.MemberValidFlg.VALID), Integer.parseInt(SinaburoConstant.DelFlg.NOT_DEL) ,SinaburoConstant.EnqueteStatusType.ENFORCEMENT) .orderBy("startDate desc").getResultList(); } //TODO テストデータ作成用 public List<Enquete> findEnqueteByTypeEnquete(Integer enqueteId) { return select().leftOuterJoin("questionList")//.leftOuterJoin("answerList") .where("T1_.enquete_id = T2_.enquete_id and T1_.del_flg=? and T2_.del_flg=? " + "and T1_.enquete_id = ? and T2_.enquete_id = ?", Integer.parseInt(SinaburoConstant.DelFlg.NOT_DEL) ,Integer.parseInt(SinaburoConstant.DelFlg.NOT_DEL),enqueteId,enqueteId) .orderBy("T1_.enquete_id,T2_.Question_id").getResultList(); } //TODO テストデータ作成用 public List<Member> getMember(Integer smember_id,Integer emember_id) { return jdbcManager.selectBySql(Member.class, "select * from m_member where member_id BETWEEN ? AND ?", smember_id,emember_id).getResultList(); } //TODO テストデータ作成用 public BeanMap getMemberMinMax() { return jdbcManager.selectBySql(BeanMap.class, "select min(member_id) min,max(member_id) max from m_member where login_id like 'huka%'").getSingleResult(); } // /** // * アンケート回答者の年齢別回答者数を求める。 // * @return 年齢別回答者数 // */ // public List<BeanMap> getEnqueteAnswerAge(String enqueteId){ // StringBuilder sb = new StringBuilder(); // sb.append("SELECT SAI10,SUM(CNT) CNT from ( "); // sb.append(" SELECT * FROM ( "); // sb.append(" SELECT '10代' SAI10, 0 CNT UNION ALL "); // sb.append(" SELECT '20代' SAI10, 0 CNT UNION ALL "); // sb.append(" SELECT '30代' SAI10, 0 CNT UNION ALL "); // sb.append(" SELECT '40代' SAI10, 0 CNT UNION ALL "); // sb.append(" SELECT '50代' SAI10, 0 CNT UNION ALL "); // sb.append(" SELECT '60代' SAI10, 0 CNT UNION ALL "); // sb.append(" SELECT '70代' SAI10, 0 CNT UNION ALL "); // sb.append(" SELECT '80代~' SAI10, 0 CNT UNION ALL "); // sb.append(" SELECT '不明' SAI1, 0 CNT ) R "); // sb.append(" UNION ALL "); // sb.append(" "); // sb.append(" SELECT "); // sb.append(" MA.SAI10, "); // sb.append(" COUNT (1) CNT "); // sb.append(" FROM ( "); // sb.append(" SELECT "); // sb.append(" "); // sb.append(" CASE "); // sb.append(" WHEN COALESCE (birthday, '') = '' THEN '不明' "); // sb.append(" WHEN FLOOR ((to_number (to_char (current_timestamp, 'yyyyMMdd'), '99999999') - to_number (birthday, '99999999')) / 100000) * 10 >= 80 THEN '80代~' "); // sb.append(" ELSE FLOOR ((to_number (to_char (current_timestamp, 'yyyyMMdd'), '99999999') - to_number (birthday, '99999999')) / 100000) * 10 ||'代' "); // sb.append(" END SAI10, "); // sb.append(" * "); // sb.append(" FROM "); // sb.append(" m_member M "); // sb.append(" INNER JOIN "); // sb.append(" ( "); // sb.append(" SELECT "); // sb.append(" enquete_id, "); // sb.append(" member_id "); // sb.append(" , "); // sb.append(" answer_times "); // sb.append(" FROM "); // sb.append(" t_reply "); // sb.append(" WHERE enquete_id = ? "); // sb.append(" GROUP BY "); // sb.append(" member_id, "); // sb.append(" enquete_id, "); // sb.append(" answer_times "); // sb.append(" ) R "); // sb.append(" ON M.member_id = R.member_id "); // sb.append(" ) MA "); // sb.append(" GROUP BY sAI10 ) RESULT "); // sb.append(" GROUP BY SAI10 "); // sb.append(" ORDER BY (CASE SAI10 WHEN '不明' THEN 1 ELSE 0 END),SAI10 "); // // return jdbcManager.selectBySql(BeanMap.class, sb.toString(),Integer.parseInt(enqueteId)).getResultList(); // } /** * アンケート回答者の年齢別回答者数を求める。 * @param enqueteId * @return 年齢別回答者数 */ public List<BeanMap> getEnqueteAnswerAgeBySql(String enqueteId){ return jdbcManager.selectBySqlFile(BeanMap.class,"jp/agentec/sinaburocast/service/cm/getEnqueteAnswerAgeBySql.sql",Integer.parseInt(enqueteId)).getResultList(); } // /** // * アンケート回答者の各性別の数を求める。 // * @return アンケート回答者の各性別の数 // */ // public BeanMap getEnqueteAnswerGender(String enqueteId){ // StringBuilder sb = new StringBuilder(); // sb.append("SELECT "); // sb.append(" "); // sb.append(" SUM(CASE WHEN gender_cd = 1 THEN 1 ELSE 0 END) MAN_CNT "); // sb.append(" ,SUM(CASE WHEN gender_cd = 2 THEN 1 ELSE 0 END) WOMAN_CNT "); // sb.append(" ,SUM(CASE WHEN gender_cd not in (1,2) THEN 1 ELSE 0 END) WHO_CNT "); // sb.append(",ROUND( (SUM(CASE WHEN gender_cd = 1 THEN 1 ELSE 0 END) / (COUNT(1)*1.0) * 100 ),0) MAN_CNT_PER "); // sb.append(",ROUND( (SUM(CASE WHEN gender_cd = 2 THEN 1 ELSE 0 END) / (COUNT(1)*1.0) * 100 ),0) WOMAN_CNT_PER "); // sb.append(",ROUND( (SUM(CASE WHEN gender_cd not in (1,2) THEN 1 ELSE 0 END) / (COUNT(1)*1.0) * 100 ),0) WHO_CNT_PER "); // // sb.append(" ,COUNT(1) TOTAL_CNT "); // sb.append("FROM "); // sb.append(" m_member m "); // sb.append(" INNER JOIN ( "); // sb.append(" SELECT "); // sb.append(" enquete_id, member_id,answer_times "); // sb.append(" FROM "); // sb.append(" t_reply "); // sb.append(" WHERE "); // sb.append(" enquete_id = ? "); // sb.append(" GROUP BY "); // sb.append(" member_id, enquete_id, answer_times "); // sb.append(" ) r "); // sb.append(" ON m.member_id = r.member_id "); // // return jdbcManager.selectBySql(BeanMap.class, sb.toString(),Integer.parseInt(enqueteId)).getSingleResult(); // } /** * アンケート回答者の各性別の数を求める。 * @return アンケート回答者の各性別の数 */ public BeanMap getEnqueteAnswerGenderBySql(String enqueteId){ return jdbcManager.selectBySqlFile(BeanMap.class,"jp/agentec/sinaburocast/service/cm/getEnqueteAnswerGenderBySql.sql",Integer.parseInt(enqueteId)).getSingleResult(); } // /** // * アンケート回答者の各性別の数を求める。 // * @return アンケート回答者の各性別の数 // */ // public ArrayList<ArrayList<BeanMap>> getEnqueteResultSum(String enqueteId){ // StringBuilder sb = new StringBuilder(); // sb.append("SELECT Q.question_no,Q.question_name,A.answer,A.answer_id, count(R.answer_id) CNT from m_question Q "); // sb.append(" LEFT JOIN m_answer A "); // sb.append(" ON Q.enquete_id = A.enquete_id "); // sb.append(" AND Q.question_id = A.question_id "); // sb.append(" "); // sb.append(" LEFT JOIN t_reply R "); // sb.append(" ON R.enquete_id = A.enquete_id "); // sb.append(" AND R.question_id = A.question_id "); // sb.append(" AND R.answer_id = A.answer_id "); // sb.append(" "); // sb.append("WHERE Q.enquete_id = ? AND Q.control_id <= 2 and Q.del_flg = 0"); // sb.append("GROUP BY Q.question_no,A.answer_id,A.answer,Q.question_name "); // sb.append("ORDER BY Q.question_no,A.answer_id "); // // // List<BeanMap> tempResult = jdbcManager.selectBySql(BeanMap.class, sb.toString(),Integer.parseInt(enqueteId)).getResultList(); // // // // if(tempResult.isEmpty()){ // return null; // } // //取得したデータを階層付ける。 // ArrayList<ArrayList<BeanMap>> result = new ArrayList<ArrayList<BeanMap>>(); // // Integer preQuestionNo = Integer.valueOf(1); // // ArrayList<BeanMap> resultPart = new ArrayList<BeanMap>(); // for (BeanMap beanMap : tempResult) { // // if(((Integer)beanMap.get("questionNo")).equals(preQuestionNo)){ // resultPart.add(beanMap); // }else{ // result.add(resultPart); // preQuestionNo = (Integer)beanMap.get("questionNo"); // resultPart = new ArrayList<BeanMap>(); // } // } // // result.add(resultPart); // // return result; // } /** * アンケート回答者の各性別の数を求める。 * @return アンケート回答者の各性別の数 */ public ArrayList<ArrayList<BeanMap>> getEnqueteResultSumBySql(String enqueteId){ StringBuilder sb = new StringBuilder(); List<BeanMap> tempResult = jdbcManager.selectBySqlFile(BeanMap.class,"jp/agentec/sinaburocast/service/cm/getEnqueteResultSumBySql.sql",Integer.parseInt(enqueteId)).getResultList(); if(tempResult.isEmpty()){ return null; } //取得したデータを階層付ける。 ArrayList<ArrayList<BeanMap>> result = new ArrayList<ArrayList<BeanMap>>(); Integer preQuestionNo = null; ArrayList<BeanMap> resultPart = new ArrayList<BeanMap>(); for (BeanMap beanMap : tempResult) { if(preQuestionNo == null){ preQuestionNo = (Integer)beanMap.get("questionNo"); } if(((Integer)beanMap.get("questionNo")).equals(preQuestionNo)){//前と同じ場合 resultPart.add(beanMap); }else{ result.add(resultPart); preQuestionNo = (Integer)beanMap.get("questionNo"); resultPart = new ArrayList<BeanMap>(); resultPart.add(beanMap); } } result.add(resultPart); return result; } public Map<Integer, Integer> getTotalCountMap(Integer enqueteType, ArrayList<ArrayList<BeanMap>> enqueteResultSumList){ Map<Integer, Integer> totalCountMap = new HashMap<Integer, Integer>(); Integer preQuestionId = null; for(ArrayList<BeanMap> list : enqueteResultSumList){ for(BeanMap enqueteResultSum:list){ Integer questionId = (Integer)enqueteResultSum.get("questionId"); if(!questionId.equals(preQuestionId)){ Integer totalConunt; if(enqueteType == SinaburoConstant.EnqueteType.EMONITOR){ // eモニの場合メンバーIDで集計 totalConunt = questionService.getAnswerMemberCount(questionId); } else { // eモニ以外はメンバーIDがないので回答回数で集計する totalConunt = questionService.getAnswerTimesCount(questionId); } totalCountMap.put(questionId, totalConunt); break; } preQuestionId = (Integer)enqueteResultSum.get("questionId"); } } return totalCountMap; } /** * アンケート検索結果を戻す。「直近1ヶ月の間に登録されたもの」 * @param enqueteSearchForm * @return */ public ArrayList<Enquete> getEnquete1Month(EnqueteSearchForm enqueteSearchForm) { Timestamp preMonth=SinaburoUtil.addMonth(-1); //件数取得 AutoSelect<Enquete> cntSelect = select().where( "insert_Date >= ? AND del_flg = ?",preMonth,Integer.parseInt(SinaburoConstant.DelFlg.NOT_DEL)); enqueteSearchForm.setPaging(null, enqueteSearchForm.pageNo, cntSelect.getCount()); return (ArrayList<Enquete>)select().where( "insert_Date >= ? AND del_flg = ?",preMonth,Integer.parseInt(SinaburoConstant.DelFlg.NOT_DEL)) .orderBy("enquete_Id DESC ").offset(enqueteSearchForm.getOffSet("")).limit(enqueteSearchForm.getLimit("")).getResultList(); } /** * アンケート検索結果を戻す。 * @param enqueteSearchForm * @return */ public ArrayList<Enquete> getEnqueteByNameStartEnd(EnqueteSearchForm enqueteSearchForm) { String start="00000000"; String end="99999999"; if(StringUtil.isNotBlank(enqueteSearchForm.operationTermFrom)){ start = enqueteSearchForm.operationTermFrom.replace(SinaburoConstant.datePikerSign.signSlash, ""); } if(StringUtil.isNotBlank(enqueteSearchForm.operationTermTo)){ end = enqueteSearchForm.operationTermTo.replace(SinaburoConstant.datePikerSign.signSlash, ""); } //件数取得 AutoSelect<Enquete> cntSelect = select().where( "enquete_name like ? AND (CASE WHEN start_Date IS NULL THEN '00000000'" + " WHEN start_Date='' THEN '00000000' ELSE start_date END) >= ? " + " AND (CASE WHEN end_date IS NULL THEN COALESCE(end_date,'99999999') " + " WHEN end_date = '' THEN '999999' ELSE end_date END) <= ? " + " AND del_flg = ?", "%"+(enqueteSearchForm.enqueteName==null?"":enqueteSearchForm.enqueteName).replace("%", "\\%")+"%" ,start,end,Integer.parseInt(SinaburoConstant.DelFlg.NOT_DEL)); enqueteSearchForm.setPaging(null, enqueteSearchForm.pageNo, cntSelect.getCount()); return (ArrayList<Enquete>)select().where( "enquete_name like ? AND (CASE WHEN start_Date IS NULL THEN '00000000'" + " WHEN start_Date='' THEN '00000000' ELSE start_date END) >= ? " + " AND (CASE WHEN end_date IS NULL THEN COALESCE(end_date,'99999999') " + " WHEN end_date = '' THEN '999999' ELSE end_date END) <= ? " + " AND del_flg = ?", "%"+(enqueteSearchForm.enqueteName==null?"":enqueteSearchForm.enqueteName).replace("%", "\\%")+"%" ,start,end,Integer.parseInt(SinaburoConstant.DelFlg.NOT_DEL)) .orderBy("enquete_Id DESC ").offset(enqueteSearchForm.getOffSet("")).limit(enqueteSearchForm.getLimit("")).getResultList(); } /** * アンケートを削除する。 * @param enqueteSearchForm * @return */ public int enqueteDelete(String eid,AdminUser adminUserInfo) { //enquete.delFlg = Integer.parseInt(SinaburoConstant.DelFlg.DEL); //物理削除 replyService.deleteByEnqueteId(eid);//アンケート回答結果削除「物理」 adminNotifyMailService.deleteByEnqueteId(eid);//管理者用通知メール設定情報削除「物理」 memberNotifyMailService.deleteByEnqueteId(eid);//回答ユーザ用通知メール設定情報削除「物理」 enqueteSummaryService.deleteByEnqueteId(eid);//アンケート集計結果削除「物理」 //論理削除 Enquete enquete = findById(Integer.parseInt(eid)); Integer count = enquete.delFlg.equals(Integer.parseInt(SinaburoConstant.DelFlg.DEL)) ? 0:1; enquete.delFlg = Integer.parseInt(SinaburoConstant.DelFlg.DEL); update(enquete,adminUserInfo.loginId);//アンケート削除「論理」 answerService.deleteByEnqueteId(eid,adminUserInfo.loginId);//設問回答マスタ削除「論理」 return count+questionService.deleteByEnqueteId(eid,adminUserInfo.loginId);//設問マスタ削除「論理」 } /** * アンケート回答者の各性別の数を求める。 * @return アンケート回答者の各性別の数 */ public int getNextEnqueteNo(){ StringBuilder sb = new StringBuilder(); sb.append("select COALESCE(max(enquete_no),0)+1 from m_enquete"); return jdbcManager.selectBySql(Integer.class, sb.toString()).getSingleResult(); } /** * 登録、更新用のEntityを返す。 * @param enqueteRegistForm * @param adminUserInfo * @return */ private Enquete getEnqueteForRegistOrUpdate(EnqueteRegistForm enqueteRegistForm,AdminUser adminUserInfo) { //#26740 【Eモニ】アンケート実施期間終了後にアンケート情報の更新操作を行うとステータスが未実施で更新されてしまう if (enqueteRegistForm.enquete.status == null) { enqueteRegistForm.enquete.status=0; } enqueteRegistForm.enquete.adminUserId = adminUserInfo.adminUserId; if (enqueteRegistForm.enquete.startDate != null) { try { String sDate = SinaburoUtil.dateStrToYYMMDD(enqueteRegistForm.enquete.startDate); enqueteRegistForm.enquete.startDate = sDate.replace("/", ""); } catch (Exception e) { logger.error("Start date fomat failed"); // 日付の形式チェックは事前に行っているのでログだけ出力する } } if (enqueteRegistForm.enquete.endDate != null) { try { String eDate = SinaburoUtil.dateStrToYYMMDD(enqueteRegistForm.enquete.endDate); enqueteRegistForm.enquete.endDate = eDate.replace("/", ""); } catch (Exception e) { logger.error("End date fomat failed");// 日付の形式チェックは事前に行っているのでログだけ出力する } } if (StringUtils.isNotEmpty(enqueteRegistForm.enquete.startDate) && StringUtils.isNotEmpty(enqueteRegistForm.enquete.endDate)) { // 翌日のバッチ処理を待たずに期間に入っていれば実施中にする if (Integer.valueOf(enqueteRegistForm.enquete.startDate) <= Integer.valueOf(SinaburoUtil.getToDayString()) && Integer.valueOf(SinaburoUtil.getToDayString()) <= Integer.valueOf(enqueteRegistForm.enquete.endDate)) { enqueteRegistForm.enquete.status = EnqueteStatusType.ENFORCEMENT; } } enqueteRegistForm.enquete.enqueteNo = getNextEnqueteNo(); enqueteRegistForm.enquete.delFlg = Integer.parseInt(SinaburoConstant.DelFlg.NOT_DEL); return enqueteRegistForm.enquete; } /** * アンケートの基本情報を登録する。 * @param enqueteRegistForm * @param adminUserInfo * @return */ public int registEnquete(EnqueteRegistForm enqueteRegistForm,AdminUser adminUserInfo) { int cnt=0; cnt = this.insertEnquete(getEnqueteForRegistOrUpdate(enqueteRegistForm,adminUserInfo),adminUserInfo.loginId); if(enqueteRegistForm.enquete.enqueteType == SinaburoConstant.EnqueteType.ORGANIZATION_CONTRIBUTE){ Question question = new Question(); question.enqueteId=enqueteRegistForm.enquete.enqueteId; question.questionNo=1; question.questionName="寄付ポイント"; question.question="寄付していただけるポイントを入力して下さい。10ポイント単位で入力してください。"; question.requiredFlg=1; question.condRequiredQuestionNo=0; question.condRequiredAnswer=""; question.controlId=SinaburoConstant.ControlType.TEXTFIELD; question.dispWitdth = 20; question.minInputText=2; question.maxInputText=10; question.inputTextLimit=1; question.delFlg=Integer.parseInt(SinaburoConstant.DelFlg.NOT_DEL); Question question2 = new Question(); question2.enqueteId=enqueteRegistForm.enquete.enqueteId; question2.questionNo=2; question2.questionName="1%支援対象団体を選択してください。 "; question2.question="◆団体:No1~●●●<BR>◆基金:No●●● "; question2.requiredFlg=1;//必須 question2.controlId=6; question2.delFlg= Integer.parseInt(SinaburoConstant.DelFlg.NOT_DEL); questionService.insertQuestion(question, adminUserInfo.loginId); questionService.insertQuestion(question2, adminUserInfo.loginId); Answer answer = new Answer(); answer.enqueteId = enqueteRegistForm.enquete.enqueteId; answer.questionId = question.questionId; answer.answerNo=1; answer.answer="text"; answer.freeTextFlg=0; answer.freeTextColWidth=0; answer.freeTextRowNum=0; answer.freeTextLimit=0; answer.delFlg=(short)Integer.parseInt(SinaburoConstant.DelFlg.NOT_DEL); answer.defaultFlg=0; Answer answer2 = new Answer(); answer2.enqueteId = enqueteRegistForm.enquete.enqueteId; answer2.questionId = question2.questionId; answer2.answerNo=1; answer2.answer="org"; answer2.freeTextFlg=0; answer2.freeTextColWidth=0; answer2.freeTextRowNum=0; answer2.freeTextLimit=0; answer2.delFlg=(short)Integer.parseInt(SinaburoConstant.DelFlg.NOT_DEL); answer2.defaultFlg=0; answerService.insertAnswer(answer, adminUserInfo.loginId); answerService.insertAnswer(answer2, adminUserInfo.loginId); } return cnt; } /** * アンケートの基本情報を更新する。 * @param enqueteRegistForm * @param adminUserInfo * @return */ public int updateEnquete(EnqueteRegistForm enqueteRegistForm,AdminUser adminUserInfo) { int enqueteId = Integer.parseInt(enqueteRegistForm.eid); //#26740 【Eモニ】アンケート実施期間終了後にアンケート情報の更新操作を行うとステータスが未実施で更新されてしまう Enquete enqueteDest =findById(enqueteId); enqueteRegistForm.enquete.status = enqueteDest.status; Enquete enqueteSrc = getEnqueteForRegistOrUpdate(enqueteRegistForm,adminUserInfo); Beans.copy(enqueteSrc, enqueteDest).excludesNull().excludes("enqueteNo","delFlg","version").execute(); return update(enqueteDest,adminUserInfo.loginId); } // /** // * アンケート結果を取得する。 // * @return アンケート結果 // */ // public List<BeanMap> getEnqueteResult(String enqueteId){ // StringBuilder sb = new StringBuilder(); // sb.append("SELECT R.member_id "); // sb.append(" ,Q.question_no "); // sb.append(" ,Q.question_name "); // sb.append(" ,(CASE WHEN Q.control_id in (0,1,2) and A.free_text_flg is null THEN A.answer ELSE R.answer END) AS answer "); // sb.append(" ,to_char(R.insert_date , 'yyyy/MM/dd HH24:MI:SS') AS insert_date "); // sb.append(" from t_reply R "); // // sb.append(" INNER JOIN m_answer A "); // sb.append(" ON R.enquete_id = A.enquete_id "); // sb.append(" AND R.question_id = A.question_id "); // sb.append(" "); // sb.append(" INNER JOIN m_question Q "); // sb.append(" ON R.enquete_id = A.enquete_id "); // sb.append(" AND R.question_id = A.question_id "); // sb.append(" AND R.answer_id = A.answer_id "); // sb.append(" AND R.question_no = Q.question_no "); // sb.append(" "); // sb.append("WHERE Q.enquete_id = ? "); // sb.append("and R.member_id is not null "); // sb.append("ORDER BY R.member_id,Q.question_no,A.answer_id "); // // return jdbcManager.selectBySql(BeanMap.class, sb.toString(),Integer.parseInt(enqueteId)).getResultList(); // } /** * アンケート結果を取得する。 * @param minMemberId TODO * @param maxMemberId TODO * @return アンケート結果 */ public List<BeanMap> getEnqueteResultBySql(String enqueteId, Integer minMemberId, Integer maxMemberId){ BeanMap param = new BeanMap(); param.put("enquete_Id", Integer.parseInt(enqueteId)); param.put("minMemberId", minMemberId); param.put("maxMemberId", maxMemberId); return jdbcManager.selectBySqlFile(BeanMap.class,"jp/agentec/sinaburocast/service/cm/getEnqueteResultBySql.sql",param).getResultList(); } public BeanMap getEnqueteResultMinMaxMemberIdBySql(String enqueteId){ return jdbcManager.selectBySqlFile(BeanMap.class,"jp/agentec/sinaburocast/service/cm/getEnqueteResultMinMaxMemberIdBySql.sql",Integer.parseInt(enqueteId)).getSingleResult(); } /** * アンケート結果を取得する。 * @return アンケート結果 */ public List<BeanMap> getEnqueteResultByNotLoginSql(String enqueteId){ return jdbcManager.selectBySqlFile(BeanMap.class,"jp/agentec/sinaburocast/service/cm/getEnqueteResultByNotLoginSql.sql",Integer.parseInt(enqueteId)).getResultList(); } /** * アンケート集計結果ファイル作成 * @param enqueteId * @param memberSearchAction * @return * @throws UnsupportedEncodingException * @throws Exception */ public String makeEnqueteResultForCsv(EnqueteSumBatchForm enqueteSumBatchForm, String csvFileName) throws UnsupportedEncodingException, Exception{ String enqueteId=enqueteSumBatchForm.eid; StringBuilder headerSb = new StringBuilder(); StringBuilder answerHeaderSb = new StringBuilder(); StringBuilder answerSb = new StringBuilder(); BeanMap minMaxMemberId = getEnqueteResultMinMaxMemberIdBySql(enqueteId); Integer minId = (Integer)minMaxMemberId.get("minid"); Integer maxId = (Integer)minMaxMemberId.get("maxid"); boolean isNotFirst = false; String contentsTitle="アンケート名:"+enqueteSumBatchForm.enquete.enqueteName+SinaburoConstant.newLine.RN +"期間:"+(StringUtils.isEmpty(enqueteSumBatchForm.enquete.startDate)?"":SinaburoUtil.convertStringDateToFormat(enqueteSumBatchForm.enquete.startDate, "/")) +" - " +(StringUtils.isEmpty(enqueteSumBatchForm.enquete.endDate)?"":SinaburoUtil.convertStringDateToFormat(enqueteSumBatchForm.enquete.endDate, "/")) +SinaburoConstant.newLine.RN +"回答者人数:"+enqueteSumBatchForm.answerCount+SinaburoConstant.newLine.RN; //FileUtil.writeFile(csvFileName,contentsTitle,"Shift_JIS",false); FileUtil.writeFile(csvFileName,SinaburoUtil.toSJIS(contentsTitle),"Windows-31J",false); if(minId == null){ return ""; } int count =999; minId--; List<BeanMap> enqueteResultList = getEnqueteResultBySql(enqueteId, ++minId, minId+=count); if(enqueteResultList.isEmpty()){ return ""; } List<Question> questionList = questionService.findAllOrderByEnqueteId(enqueteId); //ヘッダー作成 headerSb=new StringBuilder(); answerHeaderSb = new StringBuilder(); headerSb.append("回答日時\t"); answerHeaderSb.append("\t"); for(Question question : questionList){ headerSb.append(question.questionName.toString().replace(SinaburoConstant.newLine.RN, "") + "\t\t"); answerHeaderSb.append("回答\t"); answerHeaderSb.append("FA\t"); } do { if(enqueteResultList.size() > 0){ answerSb = new StringBuilder(); Integer preMemberId = (Integer)enqueteResultList.get(0).get("memberId"); Integer preQuestionNo = 0; ArrayList<Integer> memberIdList = new ArrayList<Integer>(); int qcnt = 0; for (int i = 0; i < enqueteResultList.size(); i++) { BeanMap enqueteResult = enqueteResultList.get(i); Integer memberId = (Integer)enqueteResult.get("memberId"); Integer questionNo = (Integer)enqueteResult.get("questionNo"); String answer = enqueteResult.get("answer").toString().replace(SinaburoConstant.newLine.RN, ""); String insertDate = (String)enqueteResult.get("insertDate"); if(!preMemberId.equals(memberId)){ for(;qcnt < questionList.size(); qcnt++){ answerSb.append("\t\t"); } answerSb.append("\t{{{"+preMemberId+"}}}"+SinaburoConstant.newLine.RN); qcnt = 1; } else { if(!preQuestionNo.equals(questionNo)){ qcnt++; } } if(preQuestionNo.equals(questionNo) && preMemberId.equals(memberId)){ answerSb.append(","+answer); }else{ if(qcnt == 1){ answerSb.append(insertDate+"\t"); } for(int t = qcnt; t < questionNo ; t++){ if(t != 1){ answerSb.append("\t\t"); } } qcnt = questionNo; if(questionNo.equals(1)){ answerSb.append(answer); }else{ answerSb.append("\t\t"+answer); } } preQuestionNo = questionNo; if(!memberIdList.contains(memberId)){ memberIdList.add(memberId); } preMemberId = memberId; } for(;qcnt < questionList.size(); qcnt++){ answerSb.append("\t\t"); } if(!isNotFirst){ headerSb.delete(headerSb.lastIndexOf("\t")-1, headerSb.length()-1); answerHeaderSb.delete(answerHeaderSb.lastIndexOf("\t")-3, answerHeaderSb.length()-1); } headerSb.append("{{{0}}}"+SinaburoConstant.newLine.RN); answerHeaderSb.append(SinaburoConstant.newLine.RN); answerSb.append("\t{{{"+preMemberId+"}}}"+SinaburoConstant.newLine.RN); headerSb.append(answerHeaderSb.toString()); if(isNotFirst){headerSb = new StringBuilder(); } headerSb.append(answerSb.toString()); /**ヘッダー、回答まで作成完*/ /**会員情報取得*/ String memberInfo = getMemberDownloadContents(memberService.getMemberList(memberIdList),false, true); memberInfo = memberInfo.replace("\"", ""); String [] lines = memberInfo.split(SinaburoConstant.newLine.RN); memberInfo = null; TreeMap<String,String> lineMap = new TreeMap<String,String>(); for(int i=1;i<lines.length;i++){ String [] line = lines[i].split("\t"); lineMap.put(line[0],lines[i]); } String result = headerSb.toString(); result=result.replace("{{{0}}}", lines[0]); java.util.Iterator ite = lineMap.entrySet().iterator(); while (ite.hasNext()){ Object o = ite.next(); java.util.Map.Entry ent = (java.util.Map.Entry)o; result=result.replace("{{{"+ent.getKey().toString()+"}}}",(String)ent.getValue()); } //FileUtil.writeFile(csvFileName,SinaburoUtil.addDoubleStr(result),"Shift_JIS",true); FileUtil.writeFile(csvFileName,SinaburoUtil.toSJIS(SinaburoUtil.addDoubleStr(result)),"Windows-31J",true); } if(minId >= maxId){break;}//最後に到達した場合 enqueteResultList = getEnqueteResultBySql(enqueteId, ++minId, Math.min(minId+=count, maxId)); isNotFirst=true; } while (true); return ""; // "アンケート名:"+enqueteSumBatchForm.enquete.enqueteName+SinaburoConstant.newLine.RN // +"期間:"+SinaburoUtil.convertStringDateToFormat(enqueteSumBatchForm.enquete.startDate, "/")+" - "+SinaburoUtil.convertStringDateToFormat(enqueteSumBatchForm.enquete.endDate, "/")+SinaburoConstant.newLine.RN // +"回答者人数:"+enqueteSumBatchForm.answerCount+SinaburoConstant.newLine.RN // +resultSb.toString(); } /** * アンケート集計結果ファイル作成 * @param enqueteId * @param memberSearchAction * @return * @throws UnsupportedEncodingException * @throws Exception */ public String makeEnqueteResultForCsvNotLogin(EnqueteSumBatchForm enqueteSumBatchForm) throws UnsupportedEncodingException, Exception { String enqueteId = enqueteSumBatchForm.eid; StringBuilder headerSb = new StringBuilder(); StringBuilder answerHeaderSb = new StringBuilder(); StringBuilder answerSb = new StringBuilder(); List<Question> questionList = questionService.findAllOrderByEnqueteId(enqueteId); // ヘッダー作成 headerSb.append("回答日時\t"); answerHeaderSb.append("\t"); for (Question question : questionList) { headerSb.append(question.questionName + "\t\t"); answerHeaderSb.append("回答\t"); answerHeaderSb.append("FA\t"); } List<BeanMap> enqueteResultList = getEnqueteResultByNotLoginSql(enqueteId); if (enqueteResultList.isEmpty()) { return ""; } Short preAnswerTimes = (Short) enqueteResultList.get(0).get("answerTimes"); Integer preQuestionNo = 0; int qcnt = 0; for (int i = 0; i < enqueteResultList.size(); i++) { BeanMap enqueteResult = enqueteResultList.get(i); Short answerTimes = (Short) enqueteResult.get("answerTimes"); Integer questionNo = (Integer) enqueteResult.get("questionNo"); String answer = enqueteResult.get("answer").toString().replace(SinaburoConstant.newLine.RN, ""); String insertDate = (String) enqueteResult.get("insertDate"); if (!preAnswerTimes.equals(answerTimes)) { for (; qcnt < questionList.size(); qcnt++) { answerSb.append("\t\t"); } answerSb.append(SinaburoConstant.newLine.RN); qcnt = 1; } else { if (!preQuestionNo.equals(questionNo)) { qcnt++; } } if (preQuestionNo.equals(questionNo) && preAnswerTimes.equals(answerTimes)) { answerSb.append("," + answer); } else { if (qcnt == 1) { answerSb.append(insertDate + "\t"); } for (int t = qcnt; t < questionNo; t++) { if (t != 1) { answerSb.append("\t\t"); } } qcnt = questionNo; if (questionNo.equals(1)) { answerSb.append(answer); } else { answerSb.append("\t\t" + answer); } } preQuestionNo = questionNo; preAnswerTimes = answerTimes; } headerSb.delete(headerSb.lastIndexOf("\t") - 1, headerSb.length() - 1); answerHeaderSb.delete(answerHeaderSb.lastIndexOf("\t") - 3, answerHeaderSb.length() - 1); answerHeaderSb.append(SinaburoConstant.newLine.RN); headerSb.append(SinaburoConstant.newLine.RN + answerHeaderSb.toString()); headerSb.append(answerSb.toString() + SinaburoConstant.newLine.RN); /** ヘッダー、回答まで作成完 */ String result = headerSb.toString(); String startDate = ""; String endDate = ""; if (StringUtil.isBlank(enqueteSumBatchForm.enquete.startDate)) { startDate = "未設定"; } else { startDate = SinaburoUtil.convertStringDateToFormat(enqueteSumBatchForm.enquete.startDate, "/"); } if (StringUtil.isBlank(enqueteSumBatchForm.enquete.endDate)) { endDate = "未設定"; } else { endDate = SinaburoUtil.convertStringDateToFormat(enqueteSumBatchForm.enquete.endDate, "/"); } return "アンケート名:" + enqueteSumBatchForm.enquete.enqueteName + SinaburoConstant.newLine.RN + "期間:" + startDate + " - " + endDate + SinaburoConstant.newLine.RN + "回答者人数:" + enqueteSumBatchForm.answerCount + SinaburoConstant.newLine.RN + result; } /** * アンケートをコピーする。 * @param eid * @param adminLoginId */ public void enqueteCopy(Integer eid,String adminLoginId){ Enquete enquete = this.findById(eid); enquete.enqueteName ="コピー"+enquete.enqueteName; enquete.version = 0; enquete.status = 0; enquete.dispFlg = 0; enquete.startDate=""; enquete.endDate=""; this.insertEnquete(enquete, adminLoginId); AdminNotifyMail adminNotifyMail = adminNotifyMailService.findByEnqueteId(eid); if(adminNotifyMail !=null){ adminNotifyMail.version=0; adminNotifyMail.enqueteId = enquete.enqueteId; adminNotifyMailService.insertAdminNotifyMail(adminNotifyMail, adminLoginId); } MemberNotifyMail memberNotifyMail =memberNotifyMailService.findByEnqueteId(eid); if(memberNotifyMail != null){ memberNotifyMail.version=0; memberNotifyMail.enqueteId = enquete.enqueteId; memberNotifyMailService.insertMemberNotifyMail(memberNotifyMail,adminLoginId); } } /** * アンケートステータスを未実施から実施中に変更する * @return ret 更新件数 */ public int updateStatusToEnforcement(String loginId) { String sql = "update m_enquete " + "set " + "status = ?, " + "update_date = now(), " + "update_id = ? " + "where " + "to_char(now() , 'yyyymmdd') >= start_date and to_char(now() , 'yyyymmdd') <= end_date " + "and " + "status = ? " + "and " + "del_flg = ? "; int ret = jdbcManager.updateBySql(sql,Integer.class,String.class,Integer.class,Integer.class).params (SinaburoConstant.EnqueteStatusType.ENFORCEMENT,loginId,SinaburoConstant.EnqueteStatusType.UNENFORCEMENT,SinaburoConstant.DelFlg.NOT_DEL).execute(); return ret; } /** * アンケートステータスを実施中から完了に変更する * @return ret 更新件数 */ public int updateStatusToComplete(String loginId) { String sql = "update m_enquete " + "set " + "status = ?, " + "update_date = now(), " + "update_id = ? " + "where " + "end_date < to_char(now() , 'yyyymmdd') " + "and " + "status = ? " + "and " + "del_flg = ? "; int ret = jdbcManager.updateBySql(sql,Integer.class,String.class,Integer.class,Integer.class).params (SinaburoConstant.EnqueteStatusType.COMPLETE,loginId,SinaburoConstant.EnqueteStatusType.ENFORCEMENT,SinaburoConstant.DelFlg.NOT_DEL).execute(); return ret; } /** * アンケートを作成する。 * * @param enqueteSumBatchForm * @param adminUser.loginId * @return * @throws Exception */ public String enqueteSumBatch(EnqueteSumBatchForm enqueteSumBatchForm, AdminUser adminUser) throws Exception { EnqueteSummary enqueteSummary = new EnqueteSummary(); enqueteSummary.enqueteId = Integer.parseInt(enqueteSumBatchForm.eid); enqueteSumBatchForm.enquete = findById(enqueteSummary.enqueteId); enqueteSummary.status = enqueteSumBatchForm.enquete.status; enqueteSummary.summaryDate = SinaburoUtil.getTimestamp(); String pcFile = null; String mbFile = null; String spFile = null; if(enqueteSumBatchForm.enquete.enqueteType == SinaburoConstant.EnqueteType.ORGANIZATION_CONTRIBUTE){ return null; } // 回答が存在しない場合ファイルを作成しない。 if(!replyService.isExistAnswer(enqueteSummary.enqueteId)){ if(StringUtil.equals(enqueteSumBatchForm.exeKbun, "admin")){ return "/admin/enquete/enqueteResult/?eid="+enqueteSumBatchForm.eid; } else { return null; } } VelocityContext context = new VelocityContext(); boolean makeHtmlFlg = makeEnqueteSum2(context, enqueteSumBatchForm); context.put("cssPath","/em/css"); context.put("jsPath","/em/js"); context.put("date",new org.apache.velocity.tools.generic.DateTool()); context.put("ContentType","Shift_JIS"); //HTMLの集計ファイルを作成するのはEモニ,WEBアンケートだけ if( makeHtmlFlg && (enqueteSumBatchForm.enquete.enqueteType == SinaburoConstant.EnqueteType.EMONITOR || enqueteSumBatchForm.enquete.enqueteType == SinaburoConstant.EnqueteType.WEB_ENQUETE)){ //String pcContents = SinaburoUtil.utf8ToSjis(TemplateUtil.parse("/batch/enqueteSumBatchPc_TMPUTIL.html", context)); //String mbContents = SinaburoUtil.utf8ToSjis(TemplateUtil.parse("/batch/enqueteSumBatchMb_TMPUTIL.html", context)); //String spContents = SinaburoUtil.utf8ToSjis(TemplateUtil.parse("/batch/enqueteSumBatchSp_TMPUTIL.html", context)); String pcContents = TemplateUtil.parse("/batch/enqueteSumBatchPc_TMPUTIL.html", context); String mbContents = TemplateUtil.parse("/batch/enqueteSumBatchMb_TMPUTIL.html", context); String spContents = TemplateUtil.parse("/batch/enqueteSumBatchSp_TMPUTIL.html", context); pcFile = RESULT_PATH_PC+"/enquete"+enqueteSumBatchForm.eid+".html"; mbFile = RESULT_PATH_MB+"/enquete"+enqueteSumBatchForm.eid+".html"; spFile = RESULT_PATH_SP+"/enquete"+enqueteSumBatchForm.eid+".html"; logger.info("★★★pcFile★="+pcFile); logger.info("★★★mbFile★="+mbFile); logger.info("★★★spFile★="+spFile); enqueteSummary.outputHtmlFilename = pcFile; enqueteSummary.outputMbHtmlFilename = mbFile; enqueteSummary.outputSpHtmlFilename = spFile; //SinaburoUtil.writeFile(pcFile,pcContents,"Shift_JIS"); //SinaburoUtil.writeFile(mbFile,mbContents,"Shift_JIS"); //SinaburoUtil.writeFile(spFile,spContents,"Shift_JIS"); SinaburoUtil.writeFile(pcFile,SinaburoUtil.toSJIS(pcContents),"Windows-31J"); SinaburoUtil.writeFile(mbFile,SinaburoUtil.toSJIS(mbContents),"Windows-31J"); SinaburoUtil.writeFile(spFile,SinaburoUtil.toSJIS(spContents),"Windows-31J"); } //管理者画面で実行した場合 CSV作成を行う。 if(StringUtil.equals(enqueteSumBatchForm.exeKbun, "admin")){ String result = ""; String dateStr = new DateTool().format("yyyyMMdd_HHmmss",DateTool.getSystemDate()); String csvFileName = CSV_MAKE_FILE_PATH+enqueteSumBatchForm.enquete.enqueteId+"_"+dateStr+"."+SinaburoConstant.FileType.TSV; if(SinaburoUtil.contain(enqueteSumBatchForm.enquete.enqueteType, SinaburoConstant.EnqueteType.PUBLIC_COMMENT ,SinaburoConstant.EnqueteType.WEB_ENQUETE ,SinaburoConstant.EnqueteType.RECRUITMENT)){ result = makeEnqueteResultForCsvNotLogin(enqueteSumBatchForm); result = SinaburoUtil.addDoubleStr(result); result = SinaburoUtil.toSJIS(result); //org.seasar.framework.util.FileUtil.write(csvFileName, result.getBytes("Shift_JIS")); org.seasar.framework.util.FileUtil.write(csvFileName, result.getBytes("Windows-31J")); }else{ result = makeEnqueteResultForCsv(enqueteSumBatchForm, csvFileName); } enqueteSummary.outputCsvFilename = csvFileName; } //管理者画面で実行した場合 if(StringUtil.equals(enqueteSumBatchForm.exeKbun, "admin")){ enqueteSummary.answerCount = Integer.parseInt(enqueteSumBatchForm.answerCount); enqueteSummaryService.deleteInsert(enqueteSummary, adminUser.loginId); return "/admin/enquete/enqueteResult/resultFileList/?eid="+enqueteSumBatchForm.eid; } enqueteSummaryService.deleteInsert(enqueteSummary, PropertyUtil.getString("BATCH_UPDATE")); if(!(enqueteSumBatchForm.enquete.enqueteType == 0 || enqueteSumBatchForm.enquete.enqueteType == 4)){ return null; } return null; } /** * バッチ作成 * アンケート集計結果を作成する。 * @param enqueteSumBatchForm * * @return PC画面またモバイル画面 * @throws IOException * @throws SystemException * @throws ExpectedException */ public boolean makeEnqueteSum2(VelocityContext context, EnqueteSumBatchForm enqueteSumBatchForm) throws IOException, SystemException, ExpectedException { //アンケート取得 enqueteSumBatchForm.enquete = findById(Integer.parseInt(enqueteSumBatchForm.eid)); //年齢別 enqueteSumBatchForm.ageList = getEnqueteAnswerAgeBySql(enqueteSumBatchForm.eid); //性別 enqueteSumBatchForm.genderInfo = getEnqueteAnswerGenderBySql(enqueteSumBatchForm.eid); //設問集計 enqueteSumBatchForm.enqueteResultSumList = getEnqueteResultSumBySql(enqueteSumBatchForm.eid); if(enqueteSumBatchForm.enqueteResultSumList == null){ return false; } //有効回答者数 enqueteSumBatchForm.totalCountMap = getTotalCountMap(enqueteSumBatchForm.enquete.enqueteType, enqueteSumBatchForm.enqueteResultSumList); context.put("enqueteSumBatchForm",enqueteSumBatchForm); //MathTool mathTool = new MathTool(); context.put("math",new MathTool()); return true; } /** * 会員リスト作成 * @param list * @param isMemberWithdraw * @param hasHeader TODO * @return * @throws UnsupportedEncodingException * @throws Exception */ public String getMemberDownloadContents(List<Member> list,boolean isMemberWithdraw, boolean hasHeader)throws UnsupportedEncodingException, Exception{ StringBuffer str = new StringBuffer(); if(hasHeader){ str.append("\"会員ID\"\t\"ログインID\"\t\"パスワード\"\t\"氏\"\t\"名\"\t\"氏(カナ)\"\t\"名(カナ)\"\t\"PCメールアドレス\"\t\"携帯メールアドレス\"\t\"電話\"\t\"性別\"\t\""+ "生年月日\"\t\"年代\"\t\"郵便番号\"\t\"都道府県\"\t\"市区町村\"\t\"町名・番地\"\t\"建物名\"\t\"ポイント\"\t\"メモ\"\t\"削除フラグ\""); if(isMemberWithdraw){ str.append("\t\"退会有無\"\t\"退会日時\"\t\"退会理由区分\"\t\"退会理由コメント\""); } str.append("\r\n"); } List<Prefecture> prefectureInfo = prefectureService.findAllOrderById(); //都道府県 HashMap<Integer,String> map = new HashMap<Integer,String>(); for (Prefecture data : prefectureInfo) { map.put(data.prefectureId,data.prefecture); } for (Member report : list) { str.append("\""+report.memberId+"\"\t\""+report.loginId+"\"\t\""+report.password+"\"\t\""+report.firstName+"\"\t\""+report.lastName+"\"\t\""+report.firstNameKana+"\"\t\""+report.lastNameKana); str.append("\"\t\""+report.pcEmail+"\"\t\""+report.mbEmail+"\"\t\""+report.telno+"\"\t\""); if (report.genderCd != null) { if (report.genderCd == SinaburoConstant.GenderType.MAN) { str.append("男"); } else if (report.genderCd == SinaburoConstant.GenderType.WOMAN) { str.append("女"); } else { str.append(""); } } str.append("\"\t\""+report.birthday); String kdk = getOld(report.birthday); str.append("\"\t\""+kdk); str.append("\"\t\""+report.zipCode); str.append("\"\t\""+map.get(report.prefectureId)); str.append("\"\t\""+report.cityName+"\"\t\""+report.areaName+"\"\t\""+report.buildingName); str.append("\"\t\""+report.pointNum); if (!StringUtil.isBlank(report.memo)) { str.append("\"\t\""+report.memo.replace("\\r\\n", "")); } else { str.append("\"\t\""+report.memo); } str.append("\"\t\""+"0"); if(isMemberWithdraw){ if(report.memberWithdraw == null) { str.append("\"\t\"0"); str.append("\"\t\"\"\t\"\"\t\"\""); } else { str.append("\"\t\"1"); str.append("\"\t\""+SinaburoUtil.dateToString(report.memberWithdraw.insertDate)); if (!StringUtil.isBlank(report.memberWithdraw.reasonComment)) { str.append("\"\t\""+getReason_type(report.memberWithdraw.reasonType)); } else { str.append("\"\t\""); } if (!StringUtil.isBlank(report.memberWithdraw.reasonComment)) { str.append("\"\t\""+report.memberWithdraw.reasonComment.replace("\r\n", "")+"\""); } else { str.append("\"\t\""+report.memberWithdraw.reasonComment+"\""); } } } str.append("\r\n"); } return str.toString().replace("null", ""); } /** * 年齢を返す * @param birth_day yyyyMMdd 形式の誕生日 * @return 年齢 * @throws ParseException */ public String getOld(String birth_day) throws ParseException { if(StringUtil.isBlank(birth_day)) return ""; SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd"); Calendar cal = Calendar.getInstance(); cal.setTime(df.parse(birth_day)); Date now = new Date(); if (cal.getTime().after(now)) { return ""; // マイナスは0 } Calendar nowCal = Calendar.getInstance(); nowCal.setTime(now); String nowYmd = df.format(now); String old = ((Long.parseLong(nowYmd) - Long.parseLong(birth_day)) / 100000L) * 10+""; return old; } /** * 退会理由 * @param type * @return */ public String getReason_type(int type) { Properties prop = PropertyUtil.getProperties("application_ja",false); String withDrawCheckBoxComment0 = prop.get("withDraw.checkBoxComment0").toString(); String withDrawCheckBoxComment1 = prop.get("withDraw.checkBoxComment1").toString(); String withDrawCheckBoxComment2 = prop.get("withDraw.checkBoxComment2").toString(); String result=""; int test = type; ArrayList<String> list = new ArrayList<String>(); if(test - 4 >= 0){ list.add(withDrawCheckBoxComment2); test -= 4; } if(test - 2 >= 0){ list.add(withDrawCheckBoxComment1); test -= 2; } if(test - 1 >= 0){ list.add(withDrawCheckBoxComment0); test -= 1; } if(!list.isEmpty()){result=list.get(list.size() -1);} for(int i=list.size()-2;i >= 0;i--){ result+=":"+list.get(i); } return result; } }