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();
	}


}