package jp.agentec.sinaburocast.service; import java.io.UnsupportedEncodingException; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.apache.log4j.Logger; import org.seasar.extension.jdbc.AutoSelect; import org.seasar.extension.jdbc.where.SimpleWhere; import org.seasar.framework.container.annotation.tiger.Component; import org.seasar.framework.container.annotation.tiger.InstanceType; import org.seasar.framework.util.StringUtil; import jp.agentec.sinaburocast.common.SinaburoConstant; import jp.agentec.sinaburocast.common.util.SinaburoUtil; import jp.agentec.sinaburocast.entity.GiftExchange; import jp.agentec.sinaburocast.form.admin.enquete.GiftExchangeSearchForm; @Component(instance=InstanceType.SINGLETON) public class GiftExchangeService extends AbstractService<GiftExchange> { private final Logger logger = Logger.getLogger(getClass()); private static final String ID_SEQ_NAME = "gift_exchange_id_seq"; /** * IDを発行して、登録する。 * */ public int insertGiftExchange(GiftExchange giftExchange, String insId) { giftExchange.giftExchangeId = getSeqNextVal(Integer.class, ID_SEQ_NAME); return super.insert(giftExchange, insId); } public GiftExchange findById(Integer giftExchangeId) { return select().id(giftExchangeId).getSingleResult(); } public List<GiftExchange> findAllOrderById() { return select().orderBy("giftExchangeId asc").getResultList(); } public List<GiftExchange> findBymemberIdList(Integer memberId) { return select().where(new SimpleWhere().eq("memberId", memberId)).getResultList(); } public List<GiftExchange> findBymemberId(Integer memberId) { return select() .where(new SimpleWhere().eq("memberId", memberId)) .innerJoin("gift") .orderBy("applyDate") .getResultList(); } public Integer getSumRequiredPointByMemberId(Integer memberId){ List<GiftExchange> giftExchangeList = findBymemberId(memberId); int sumRequiredPoint = 0; for(GiftExchange giftExchange : giftExchangeList){ sumRequiredPoint += giftExchange.gift.requiredPoint; } return sumRequiredPoint; } /** * ポイント交換申請情報取得(画面用) * @param giftExchangeSearchForm * @return ArrayList<GiftExchange> */ public ArrayList<GiftExchange> findAllByPaging(GiftExchangeSearchForm giftExchangeSearchForm){ //件数取得 AutoSelect<GiftExchange> counter = this.getAutoSelectForFindAllByPaging(giftExchangeSearchForm); giftExchangeSearchForm.setPaging(null, giftExchangeSearchForm.pageNo, counter.getCount()); //結果取得 return (ArrayList<GiftExchange>)this.getAutoSelectForFindAllByPaging(giftExchangeSearchForm).orderBy("applyDate,memberId") .offset(giftExchangeSearchForm.getOffSet("")) .limit(giftExchangeSearchForm.getLimit("")) .getResultList(); } /** * ポイント交換申請情報を検索するための、AutoSelectを返す。 * @param giftExchangeSearchForm * @return AutoSelect<GiftExchange> */ private AutoSelect<GiftExchange> getAutoSelectForFindAllByPaging(GiftExchangeSearchForm giftExchangeSearchForm){ Date fromDay = null; Date toDay = null; if (!StringUtil.isBlank(giftExchangeSearchForm.fromDay)){ //fromDay =SinaburoUtil.stringToDateSlash(giftExchangeSearchForm.fromDay); fromDay=SinaburoUtil.convertStringToTimestamp(giftExchangeSearchForm.fromDay,"yyyy-MM-dd"); } if (!StringUtil.isBlank(giftExchangeSearchForm.toDay)){ //toDay =SinaburoUtil.getAddDate(SinaburoUtil.stringToDateSlash(giftExchangeSearchForm.toDay),1); toDay=SinaburoUtil.convertStringToTimestamp(SinaburoUtil.getNextDay(giftExchangeSearchForm.toDay),"yyyy-MM-dd"); } SimpleWhere where = new SimpleWhere().ge("applyDate", fromDay) .lt("applyDate", toDay) .eq("member.validFlg", SinaburoConstant.MemberValidFlg.VALID) .eq("member.delFlg", SinaburoConstant.MemberDelFlg.NOT_DEL); // 区分(エコボ:1、ICHICO:2) if (giftExchangeSearchForm.giftType != null) { where.eq("gift.giftType", giftExchangeSearchForm.giftType); } return select().innerJoin("member").innerJoin("member.prefecture").innerJoin("gift").where(where); } /** * CSVファイル出力ポイント交換申請情報取得 * @param giftExchangeSearchForm * @return String ポイント交換申請情報文字列 */ public String getGiftExchangeForCsv(GiftExchangeSearchForm giftExchangeSearchForm) throws UnsupportedEncodingException, Exception{ StringBuffer str = new StringBuffer(); str.append("\"申請日\",\"会員ID\",\"氏名\",\"メールアドレス\",\"申請(枚数/ポイント数)\",\"郵便番号\",\"都道府県\",\"市区町村\",\"町名・番地\",\"建物名\",\"会員番号\",\"電話番号\""+"\r\n"); List<GiftExchange> giftExchangeList = getGiftExchangeByFromDayAndToDay(giftExchangeSearchForm); for (GiftExchange giftExchange : giftExchangeList) { //申請日 str.append("\"" + SinaburoUtil.dateToString2(giftExchange.applyDate) + "\","); //会員ID str.append("\"" + giftExchange.memberId +"\","); //氏名 str.append("\"" + giftExchange.member.firstName + " " + giftExchange.member.lastName + "\","); //メールアドレス str.append("\"" + replaceNullWithBlank(giftExchange.member.pcEmail) +"\","); //申請枚数 String unit = null; if(SinaburoConstant.giftType.ECOBO.equals(giftExchange.gift.giftType)) { unit = "枚"; } else { unit = "ポイント"; } str.append("\"" + giftExchange.cnt + unit + "\","); //郵便番号 str.append("\"" + giftExchange.member.zipCode + "\","); //都道府県 str.append("\"" + giftExchange.member.prefecture.prefecture + "\","); //市区町村 str.append("\"" + giftExchange.member.cityName + "\","); //町名・番地 str.append("\"" + giftExchange.member.areaName + "\","); //建物名 str.append("\"" + replaceNullWithBlank(giftExchange.member.buildingName) + "\","); //会員番号 str.append("\"" + replaceNullWithBlank(giftExchange.member.memberNum) + "\","); //電話番号 str.append("\"" + replaceNullWithBlank(giftExchange.member.telno) + "\""); str.append("\r\n"); } return str.toString(); } /** * null値を空白に置き換え */ private String replaceNullWithBlank(String str) { if(str != null) { return str; } return ""; } /** * ポイント交換申請情報取得(CSV出力用) * @param giftExchangeSearchForm * @return List<GiftExchange> */ private List<GiftExchange> getGiftExchangeByFromDayAndToDay(GiftExchangeSearchForm giftExchangeSearchForm){ Date fromDay = null; Date toDay = null; //#30951 「ポイント交換申請結果CSV出力」でエラー画面 対応 if (!StringUtil.isBlank(giftExchangeSearchForm.fromDay)){ logger.info("giftExchangeSearchForm.fromDay:" + giftExchangeSearchForm.fromDay); //fromDay =SinaburoUtil.stringToDateSlash(giftExchangeSearchForm.fromDay); fromDay = SinaburoUtil.convertStringToTimestamp(giftExchangeSearchForm.fromDay,"yyyy-MM-dd"); } if (!StringUtil.isBlank(giftExchangeSearchForm.toDay)){ logger.info("giftExchangeSearchForm.toDay:" + giftExchangeSearchForm.toDay); //toDay =SinaburoUtil.getAddDate(SinaburoUtil.stringToDateSlash(giftExchangeSearchForm.toDay),1); toDay = SinaburoUtil.convertStringToTimestamp(SinaburoUtil.getNextDay(giftExchangeSearchForm.toDay),"yyyy-MM-dd"); } SimpleWhere where = new SimpleWhere().ge("applyDate", fromDay) .lt("applyDate", toDay) .eq("member.validFlg", SinaburoConstant.MemberValidFlg.VALID) .eq("member.delFlg", SinaburoConstant.MemberDelFlg.NOT_DEL); // 区分(エコボ:1、ICHICO:2) if (giftExchangeSearchForm.giftType != null) { where.eq("gift.giftType", giftExchangeSearchForm.giftType); } return select().innerJoin("member").innerJoin("member.prefecture").innerJoin("gift").where(where) .orderBy("applyDate,memberId") .getResultList(); } }