package jp.agentec.sinaburocast.service; import java.io.UnsupportedEncodingException; import java.util.ArrayList; import java.util.Date; import java.util.List; 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; 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; @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"); } return select().innerJoin("member").innerJoin("member.prefecture").where(new SimpleWhere() .ge("applyDate", fromDay) .lt("applyDate", toDay) .eq("member.validFlg", SinaburoConstant.MemberValidFlg.VALID) .eq("member.delFlg", SinaburoConstant.MemberDelFlg.NOT_DEL)); } /** * CSVファイル出力ポイント交換申請情報取得 * @param giftExchangeSearchForm * @return String ポイント交換申請情報文字列 */ public String getGiftExchangeForCsv(GiftExchangeSearchForm giftExchangeSearchForm) throws UnsupportedEncodingException, Exception{ StringBuffer str = new StringBuffer(); str.append("\"申請日\"\t\"会員ID\"\t\"氏名\"\t\"メールアドレス\"\t\"申請枚数\"\t\"郵便番号\"\t\"都道府県\"\t\"市区町村\"\t\"町名・番地\"\t\"建物名\""+"\r\n"); List<GiftExchange> giftExchangeList = getGiftExchangeByFromDayAndToDay(giftExchangeSearchForm); for (GiftExchange giftExchange : giftExchangeList) { //申請日 str.append("\"" + SinaburoUtil.dateToString2(giftExchange.applyDate) + "\"\t"); //会員ID str.append("\"" + giftExchange.memberId +"\"\t"); //氏名 str.append("\"" + giftExchange.member.firstName + " " + giftExchange.member.lastName + "\"\t"); //メールアドレス str.append("\"" + giftExchange.member.pcEmail +"\"\t"); //申請枚数 str.append("\"" + giftExchange.cnt + "\"\t"); //郵便番号 str.append("\"" + giftExchange.member.zipCode + "\"\t"); //都道府県 str.append("\"" + giftExchange.member.prefecture.prefecture + "\"\t"); //市区町村 str.append("\"" + giftExchange.member.cityName + "\"\t"); //町名・番地 str.append("\"" + giftExchange.member.areaName + "\"\t"); //建物名 str.append("\"" + giftExchange.member.buildingName + "\""); str.append("\r\n"); } return str.toString(); } /** * ポイント交換申請情報取得(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"); } return select().innerJoin("member").innerJoin("member.prefecture").where(new SimpleWhere() .ge("applyDate", fromDay) .lt("applyDate", toDay) .eq("member.validFlg", SinaburoConstant.MemberValidFlg.VALID) .eq("member.delFlg", SinaburoConstant.MemberDelFlg.NOT_DEL)) .orderBy("applyDate,memberId") .getResultList(); } }