package jp.agentec.sinaburocast.service;

import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;

import javax.mail.MessagingException;

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.csv.MemberInfoCsv;
import jp.agentec.sinaburocast.dto.MemberDetailDto;
import jp.agentec.sinaburocast.dto.MemberReportDto;
import jp.agentec.sinaburocast.entity.AdminUser;
import jp.agentec.sinaburocast.entity.DeliveryGroup;
import jp.agentec.sinaburocast.entity.DeliveryGroupMember;
import jp.agentec.sinaburocast.entity.Member;
import jp.agentec.sinaburocast.entity.MemberWithdraw;
import jp.agentec.sinaburocast.entity.Message;
import jp.agentec.sinaburocast.entity.PasswordMailSend;
import jp.agentec.sinaburocast.entity.PointGet;
import jp.agentec.sinaburocast.form.admin.member.MemberReportForm;
import jp.agentec.sinaburocast.form.admin.member.MemberSearchForm;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.RandomStringUtils;
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 MemberService extends AbstractService<Member> {

	private static final String ID_SEQ_NAME = "member_id_seq";
	public MessageService messageService;

	public MemberWithdrawService memberWithdrawService;

	public PasswordMailSendService passwordMailSendService;

	public PointDonationService pointDonationService;

	public PointGetService pointGetService;

	public GiftExchangeService giftExchangeService;

	public DeliveryGroupMemberService deliveryGroupMemberService;

	public DeliveryGroupService deliveryGroupService;

	public ReplyService replyService;

	public EnqueteMailSendResultService enqueteMailSendResultService;

	private static Integer MEMBER_ID = null;

	/**
	 * IDを発行して、登録する。
	 *
	 */
	public int insertMember(Member member, String insId) {
		member.memberId = getSeqNextVal(Integer.class, ID_SEQ_NAME);
		MEMBER_ID = member.memberId;
		return super.insert(member, insId);
	}

	public Member findById(Integer memberId) {
		return select().id(memberId).where(new SimpleWhere()
		.eq("validFlg", SinaburoConstant.MemberValidFlg.VALID)
		.eq("delFlg", SinaburoConstant.MemberDelFlg.NOT_DEL)).getSingleResult();
	}

	public Member findByDeleteId(Integer memberId) {
		return select().id(memberId).getSingleResult();
	}

	public Member findByUserId(String userId) {
		return select().where(new SimpleWhere().eq("loginId", userId).eq("validFlg", SinaburoConstant.MemberValidFlg.VALID).eq("delFlg", SinaburoConstant.MemberDelFlg.NOT_DEL))
				//.orderBy("memberId asc")
				.getSingleResult();
	}

	/**
	 * ID、パスワード問い合わせで入力されたメールの存在をチェック
	 * @param email 入力されたメールアドレス
	 * @return emailが一致するMember情報
	 */
	public List<Member> findByEmail(String email) {
		//入力されたemailでmb_email,pc_emailともに検索する。
		return jdbcManager.from(Member.class).where(" '"+email+"' in (mb_email,pc_email) and del_flg = ? and valid_flg = ? "
			,Integer.parseInt(SinaburoConstant.DelFlg.NOT_DEL)
			,SinaburoConstant.ValidFlg.VALID
			).getResultList();
	}



	public Member findByPcEmail(String mail) {

		return select().where(new SimpleWhere()
				.eq("pcEmail", mail)
				.eq("validFlg", SinaburoConstant.MemberValidFlg.VALID)
				.eq("delFlg", SinaburoConstant.MemberDelFlg.NOT_DEL)
				).orderBy("memberId asc").getSingleResult();
	}

	public Member findByMoEmail(String mail) {

		return select().where(new SimpleWhere()
				.eq("mbEmail", mail)
				.eq("validFlg", SinaburoConstant.MemberValidFlg.VALID)
				.eq("delFlg", SinaburoConstant.MemberDelFlg.NOT_DEL)
				).orderBy("memberId asc").getSingleResult();
	}

	public List<Member> findByLoginId(String loginId) {

		return select().where(new SimpleWhere()
		.eq("loginId", loginId)
		.eq("validFlg", SinaburoConstant.MemberValidFlg.VALID)
		.eq("delFlg",SinaburoConstant.MemberDelFlg.NOT_DEL)
		).orderBy("memberId asc").getResultList();
	}


    /**
     * 新規登録時、メール送信
     *
     * @param member ユーザー情報
     * @param email  ユーザーが入力したEmailアドレス
     * @return insertの結果件数。
     * @throws MessagingException
     * @throws UnsupportedEncodingException
     */
	public int insertRegistMailSendLogic(Member member,String email,String url)throws MessagingException, UnsupportedEncodingException {

    	int retValue = 0;

		//登録
    	retValue = insertMember(member,member.loginId);

    	//メール送信
		// 新規登録メール送信
		if (retValue == 1) {
			Message messageInfo = messageService.findById(SinaburoConstant.MessageCode.REPLYMAIL);

			String contents = messageInfo.message;

			contents=contents.replace("\\r\\n", "\r\n");

	    	StringBuilder body = new StringBuilder();
			contents = contents.replaceAll("\\%loginId\\%", member.loginId);
			contents = contents.replaceAll("\\%passWord\\%", member.password);


			List<PasswordMailSend> passwordMailSends = passwordMailSendService.findAllByMailType(SinaburoConstant.MailType.MEMBER_REGIST);
			PasswordMailSend passwordMailSend = new  PasswordMailSend();
	    	//onetimeTicketは唯一
	    	ArrayList<String> onetimeTicketList = new ArrayList<String>();
	    	for (PasswordMailSend pms : passwordMailSends) {
	    		onetimeTicketList.add(pms.onetimeTicket);
			}

	    	String month = PropertyUtil.getProperty("mail.add_regist_expired_month");
	    	passwordMailSend.memberId = member.memberId;
	    	passwordMailSend.expiredDate = SinaburoUtil.addMonth(Integer.parseInt(month));
	    	passwordMailSend.mailType=SinaburoConstant.MailType.MEMBER_REGIST;

			do {
				passwordMailSend.onetimeTicket = RandomStringUtils.randomAlphanumeric(30);
				//既に登録されていたら作りなおす
			} while (onetimeTicketList.contains(passwordMailSend.onetimeTicket) );


			contents = contents.replaceAll("\\%OneTimeTicket\\%", "?onetimeTicket="+passwordMailSend.onetimeTicket);
			//contents = contents.replaceAll("\\{url\\}", url+"/em/user/login/");

			String title = "e-モニター会員登録認証メール";
			body.append(contents);

			passwordMailSendService.insertPasswordMailSend(passwordMailSend,"REGIST");

			//メール送信
			MailUtil.send(email,title, body.toString());
		}

    	return retValue;
    }

    /**
     * 新規登録時、メール送信
     *
     * @param member ユーザー情報
     * @param email  ユーザーが入力したEmailアドレス
     * @return insertの結果件数。
     * @throws MessagingException
     * @throws UnsupportedEncodingException
     */
	public void memberRegisterd(Member member,String email,String url)throws MessagingException, UnsupportedEncodingException {

		Message messageInfo = messageService.findById(SinaburoConstant.MessageCode.MEMBER_REGISTERD);

		String contents = messageInfo.message;

		contents=contents.replace("\\r\\n", "\r\n");

    	StringBuilder body = new StringBuilder();
		contents = contents.replaceAll("\\%loginId\\%", member.loginId);
		contents = contents.replaceAll("\\%passWord\\%", member.password);
		contents = contents.replaceAll("\\{url\\}", url+"/em/user/login/");

		String title = "e-モニター会員登録認証メール";
		body.append(contents);

		//メール送信
		MailUtil.send(email,title, body.toString());
    }

	/**
	 * 管理者ユーザ検索
	 * @param pageKey
	 * @param curPage
	 * @param memberSearchForm
	 * @param groupflg
	 * @return
	 */
	public  List<MemberDetailDto> findByMemberList(String pageKey,Integer curPage,MemberSearchForm memberSearchForm,boolean groupflg) {

		boolean flg = false;
		StringBuffer sql = new StringBuffer();
		List<Object> values = new ArrayList<Object>();
		if (!StringUtil.isBlank(memberSearchForm.memberId)) {
			sql.append(" CAST(member_id as varchar) = ? ");
			//values.add(Integer.parseInt(memberSearchForm.memberId));
			values.add(memberSearchForm.memberId);
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.loginId)) {
			if (flg) sql.append(" and ");
			sql.append("login_Id like ? ");
			values.add("%"+memberSearchForm.loginId.trim().replace("%", "\\%")+"%");
			//values.add(memberSearchForm.loginId);
			flg = true;

		}

		if (!StringUtil.isBlank(memberSearchForm.firstName)) {
			if (flg) sql.append(" and ");
			sql.append("first_name like ? ");
			values.add("%"+memberSearchForm.firstName.trim().replace("%", "\\%")+"%");
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.lastName)) {
			if (flg) sql.append(" and ");
			sql.append("last_name like ? ");
			values.add("%"+memberSearchForm.lastName.trim().replace("%", "\\%")+"%");
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.firstNameKana)) {
			if (flg) sql.append(" and ");
			sql.append("first_name_kana like ? ");
			values.add("%"+memberSearchForm.firstNameKana.trim().replace("%", "\\%")+"%");
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.lastNameKana)) {
			if (flg) sql.append(" and ");
			sql.append("last_name_kana like ? ");
			values.add("%"+memberSearchForm.lastNameKana.trim().replace("%", "\\%")+"%");
			flg = true;
		}
		if (!StringUtil.isBlank(memberSearchForm.pcEmail)) {
			if (flg) sql.append(" and ");
			sql.append("pc_email like ? ");
			values.add("%"+memberSearchForm.pcEmail.trim().replace("%", "\\%")+"%");
			flg = true;
		}
		if (!StringUtil.isBlank(memberSearchForm.mbEmail)) {
			if (flg) sql.append(" and ");
			sql.append("mb_email like ? ");
			values.add("%"+memberSearchForm.mbEmail.trim().replace("%", "\\%")+"%");
			flg = true;
		}
		if (!StringUtil.isBlank(memberSearchForm.telNo)) {
			if (flg) sql.append(" and ");
			sql.append("telno like ? ");
			values.add("%"+memberSearchForm.telNo.trim().replace("%", "\\%")+"%");
			flg = true;
		}
		if (!StringUtil.isBlank(memberSearchForm.genderCd)) {
			if (flg) sql.append(" and ");
			sql.append(" CAST(gender_cd as varchar) = ? ");
			//values.add(Integer.parseInt(memberSearchForm.genderCd));
			values.add(memberSearchForm.genderCd);
			flg = true;
		}
		if (!StringUtil.isBlank(memberSearchForm.birthdayY+memberSearchForm.birthdayM+memberSearchForm.birthdayD) &&
				!StringUtil.isBlank(memberSearchForm.birthdayY1+memberSearchForm.birthdayM1+memberSearchForm.birthdayD1)) {
			if (flg) sql.append(" and ");
			sql.append("COALESCE(birthday,'') != '' AND birthday BETWEEN ? AND ? ");
			values.add(memberSearchForm.birthdayY+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayM,2)+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayD,2));
			values.add(memberSearchForm.birthdayY1+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayM1,2)+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayD1,2));
			flg = true;
		} else {
			if (!StringUtil.isBlank(memberSearchForm.birthdayY+memberSearchForm.birthdayM+memberSearchForm.birthdayD)) {
				if (flg) sql.append(" and ");
				sql.append("COALESCE(birthday,'') != '' AND birthday >= ? ");
				values.add(memberSearchForm.birthdayY+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayM,2)+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayD,2));
				flg = true;
			}
			if (!StringUtil.isBlank(memberSearchForm.birthdayY1+memberSearchForm.birthdayM1+memberSearchForm.birthdayD1)) {
				if (flg) sql.append(" and ");
				sql.append("COALESCE(birthday,'') != '' AND birthday <= ? ");
				values.add(memberSearchForm.birthdayY1+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayM1,2)+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayD1,2));
				flg = true;
			}
		}

		//年齢検索
		if (!StringUtil.isBlank(memberSearchForm.age) && !StringUtil.isBlank(memberSearchForm.age1)) {
			if (flg) sql.append(" and ");
			sql.append(" COALESCE(birthDay,'') != '' AND (FLOOR((to_number (to_char (current_timestamp, 'yyyyMMdd'), '99999999') - to_number (birthday, '99999999')) / 100000) * 10) BETWEEN ? AND ?");
			values.add(Integer.parseInt(memberSearchForm.age));
			values.add(Integer.parseInt(memberSearchForm.age1));
			flg = true;
		} else {
			if (!StringUtil.isBlank(memberSearchForm.age)) {
				if (flg) sql.append(" and ");
				sql.append(" COALESCE(birthDay,'') != '' AND (FLOOR((to_number (to_char (current_timestamp, 'yyyyMMdd'), '99999999') - to_number (birthday, '99999999')) / 100000) * 10) >= ?");
				values.add(Integer.parseInt(memberSearchForm.age));
				flg = true;
			}
			if (!StringUtil.isBlank(memberSearchForm.age1)) {
				if (flg) sql.append(" and ");
				sql.append(" COALESCE(birthDay,'') != '' AND (FLOOR((to_number (to_char (current_timestamp, 'yyyyMMdd'), '99999999') - to_number (birthday, '99999999')) / 100000) * 10) <= ?");
				values.add(Integer.parseInt(memberSearchForm.age1));
				flg = true;
			}
		}

		//登録日検索
		if (!StringUtil.isBlank(memberSearchForm.fromDay) && !StringUtil.isBlank(memberSearchForm.toDay)) {
			if (flg) sql.append(" and ");
			sql.append(" TO_CHAR(insert_date,'yyyyMMdd') BETWEEN ? AND ? ");
			values.add(memberSearchForm.fromDay.replaceAll("/", ""));
			values.add(memberSearchForm.toDay.replaceAll("/", ""));
			flg = true;
		} else {
			if (!StringUtil.isBlank(memberSearchForm.fromDay)) {
				if (flg) sql.append(" and ");
				sql.append(" TO_CHAR(insert_date,'yyyyMMdd') >= ? ");
				values.add(memberSearchForm.fromDay.replaceAll("/", ""));
				flg = true;
			}
			if (!StringUtil.isBlank(memberSearchForm.toDay)) {
				if (flg) sql.append(" and ");
				sql.append("  TO_CHAR(insert_date,'yyyyMMdd') <= ? ");
				values.add(memberSearchForm.toDay.replaceAll("/", ""));
				flg = true;
			}
		}
		if (!StringUtil.isBlank(memberSearchForm.prefectureId)) {
			if (flg) sql.append(" and ");
			sql.append("CAST(prefecture_id as varchar) = ? ");
			values.add(memberSearchForm.prefectureId);
			flg = true;
		}
		if (!StringUtil.isBlank(memberSearchForm.zipCode) && !StringUtil.isBlank(memberSearchForm.zipCode1)) {
			if (flg) sql.append(" and ");
			sql.append("COALESCE(zip_code,'') != '' AND zip_code BETWEEN ? AND ? ");
			values.add(memberSearchForm.zipCode);
			values.add(memberSearchForm.zipCode1);
			flg = true;
		} else {
			if (!StringUtil.isBlank(memberSearchForm.zipCode)) {
				if (flg) sql.append(" and ");
				sql.append(" COALESCE(zip_code,'') != '' AND zip_code >= ? ");
				values.add(memberSearchForm.zipCode);
				flg = true;
			}
			if (!StringUtil.isBlank(memberSearchForm.zipCode1)) {
				if (flg) sql.append(" and ");
				sql.append("COALESCE(zip_code,'') != '' AND zip_code <= ? ");
				values.add(memberSearchForm.zipCode1);
				flg = true;
			}
		}
		if (!StringUtil.isBlank(memberSearchForm.areaName)) {
			if (flg) sql.append(" and ");
			sql.append("area_name like ? ");
			values.add("%"+memberSearchForm.areaName.trim().replace("%", "\\%")+"%");
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.cityName)) {
			if (flg) sql.append(" and ");
			sql.append("city_name like ? ");
			values.add("%"+memberSearchForm.cityName.trim().replace("%", "\\%")+"%");
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.buildingName)) {
			if (flg) sql.append(" and ");
			sql.append("building_name like ? ");
			values.add("%"+memberSearchForm.buildingName.trim().replace("%", "\\%")+"%");
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.point)) {
			if (flg) sql.append(" and ");
			//sql.append("CAST(point_num as varchar) >= ? ");
			sql.append("point_num >= ? ");
			values.add(Integer.parseInt(memberSearchForm.point));
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.memo)) {
			if (flg) sql.append(" and ");
			sql.append("memo like ?");
			values.add("%"+memberSearchForm.memo.trim().replace("%", "\\%")+"%");
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.withDrawFlg) && !memberSearchForm.withDrawFlg.equals("1")) {
			if (memberSearchForm.withDrawFlg.equals("0")) {
				if (flg) sql.append(" and ");
				sql.append("CAST(del_flg as varchar) = ?");
				values.add("0");
			} else if (memberSearchForm.withDrawFlg.equals("2")) {
				if (flg) sql.append(" and ");
				sql.append("CAST(del_flg as varchar) = ?");
				values.add("1");
			}
			flg = true;
		}

		if (flg) {
			sql.append(" and ");
			sql.append("CAST(del_flg as varchar) <> ?");
			values.add("2");
		}

		AutoSelect<Member> resultCnt;
		if (flg) {
			resultCnt= select().where(sql.toString(),values.toArray());
		} else {
			resultCnt= select().where(new SimpleWhere().ne("delFlg", "2"));
		}

		memberSearchForm.setPaging(null, curPage, resultCnt.getCount());
		List<Member> memberList;

		//配信グルプ作成
		if (groupflg) {
			if (flg) {
				memberList = select().where(sql.toString(),values.toArray())
						.orderBy("memberId desc,insertDate desc").getResultList();
			} else  {
				memberList = select().where(new SimpleWhere().ne("delFlg", "2"))
						.orderBy("memberId desc,insertDate desc").getResultList();
			}
		} else {
			if (flg) {
				memberList = select().where(sql.toString(),values.toArray())
						.orderBy("memberId desc,insertDate desc").offset(memberSearchForm.getOffSet(pageKey))
						.limit(memberSearchForm.getLimit(pageKey)).getResultList();
			} else {
				memberList = select().where(new SimpleWhere().ne("delFlg", "2"))
						.orderBy("memberId desc,insertDate desc").offset(memberSearchForm.getOffSet(pageKey))
						.limit(memberSearchForm.getLimit(pageKey)).getResultList();
			}
		}

		List<MemberDetailDto> dtoList = new ArrayList<MemberDetailDto> ();

		for (Member member : memberList) {
			MemberDetailDto dto = new MemberDetailDto();
			dto.memberId = member.memberId+"";
			dto.loginId = member.loginId;
			dto.name1 = member.firstName;
			dto.name2 = member.lastName;

			if (StringUtil.isBlank(member.pcEmail) ) {
				dto.mail = member.mbEmail;
			}else {
				dto.mail = member.pcEmail;
			}

			dto.birthDay = member.birthday;
			dto.address1 = member.cityName;
			dto.address2 = member.areaName;
			dtoList.add(dto);
		}

//		return select().where(new SimpleWhere()
//		.eq("validFlg", SinaburoConstant.MemberValidFlg.VALID)
//		.eq("delFlg",SinaburoConstant.MemberDelFlg.NOT_DEL))
//		.orderBy("memberId asc").getResultList();
		return dtoList;
	}

	public Member findByIdAdmin(Integer memberId) {
		return select().id(memberId).getSingleResult();
	}

	/**
	 * 分析レポート検索 月別
	 * @return
	 */
	public  List<MemberReportDto> findByMemberReportList() {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT insert_date AS DATE,SUM(ALL_REG) AS ALL_REG,SUM(ALL_RET) AS ALL_RET,SUM(ALL_JUN) AS ALL_JUN");
				sb.append(" ,SUM(PC_REG) AS PC_REG,SUM(PC_RET) AS PC_RET,SUM(PC_JUN) AS PC_JUN");
				sb.append(" ,SUM(MB_REG) AS MB_REG,SUM(MB_RET) AS MB_RET,SUM(MB_JUN) AS MB_JUN");
				sb.append(" FROM (");
				sb.append("		SELECT  '1',SUBSTRING(M.insert_date,1,6) INSERT_DATE");
				sb.append("			,COUNT(1) ALL_REG");
				sb.append("			,0        ALL_RET ");
				sb.append("			,COUNT(1) ALL_JUN ");
				sb.append("			,SUM(CASE WHEN COALESCE(LTRIM(pc_email),'') != '' THEN 1 ELSE 0 END ) PC_REG");
				sb.append("			,0                                                                    PC_RET");
				sb.append("			,SUM(CASE WHEN COALESCE(LTRIM(pc_email),'') != '' THEN 1 ELSE 0 END ) PC_JUN");
				sb.append("			,SUM(CASE WHEN COALESCE(LTRIM(mb_email),'') != '' THEN 1 ELSE 0 END ) MB_REG");
				sb.append("			,0                                                                    MB_RET");
				sb.append("			,SUM(CASE WHEN COALESCE(LTRIM(mb_email),'') != '' THEN 1 ELSE 0 END ) MB_JUN");
				sb.append("			FROM");
				sb.append("			(SELECT  member_id,TO_CHAR(insert_date, 'yyyyMMdd') AS insert_date,mb_email,pc_email ");
				sb.append("			FROM	m_member ) M");
				sb.append("		GROUP BY SUBSTRING(M.insert_date,1,6) ");
				sb.append("		UNION ALL");
				sb.append("		SELECT '2',SUBSTRING(W.insert_date,1,6) INSERT_DATE");
				sb.append("		       ,0 ALL_REG");
				sb.append("		       ,COUNT(1) ALL_RET");
				sb.append("		       ,-COUNT(1) ALL_JUN");
				sb.append("			,0                                                                      PC_REG");
				sb.append("			,SUM(CASE WHEN COALESCE(LTRIM(W.pc_email),'') != '' THEN 1 ELSE 0 END ) PC_RET");
				sb.append("			,-SUM(CASE WHEN COALESCE(LTRIM(W.pc_email),'') != '' THEN 1 ELSE 0 END ) PC_JUN");
				sb.append("			,0                                                                      MB_REG");
				sb.append("			,SUM(CASE WHEN COALESCE(LTRIM(mb_email),'') != '' THEN 1 ELSE 0 END )   MB_RET");
				sb.append("			,-SUM(CASE WHEN COALESCE(LTRIM(mb_email),'') != '' THEN 1 ELSE 0 END )   MB_JUN");
				sb.append("		FROM");
				sb.append("			(SELECT  TO_CHAR(WIT.INSERT_DATE,'yyyyMMdd') AS insert_date");
				sb.append("				,MEM.pc_email");
				sb.append("				,MEM.mb_email");
				sb.append("			FROM t_member_withdraw WIT");
				sb.append("				INNER JOIN m_member MEM");
				sb.append("				ON MEM.member_id = WIT.member_id");
				sb.append("				) W");
				sb.append("		GROUP BY SUBSTRING(W.insert_date,1,6) ");
				sb.append(") TEMP_RESULT");
				sb.append(" GROUP BY insert_date");
				sb.append(" ORDER BY INSERT_DATE DESC");

		List<BeanMap> tempResult = jdbcManager.selectBySql(BeanMap.class, sb.toString()).getResultList();

		List<MemberReportDto> dtoList = new ArrayList<MemberReportDto> ();
		for (BeanMap beanMap : tempResult) {
			MemberReportDto list = new MemberReportDto();
			list.date = (String) beanMap.get("date");
			list.total = (String) beanMap.get("allReg").toString();
			list.totalT = (String) beanMap.get("allRet").toString();
			list.totalUp = (String) beanMap.get("allJun").toString();
			list.pcTotal = (String) beanMap.get("pcReg").toString();
			list.pcTotalt = (String) beanMap.get("pcRet").toString();
			list.pcTotalUp = (String) beanMap.get("pcJun").toString();
			list.mbTotal = (String) beanMap.get("mbReg").toString();
			list.mbTotalt = (String) beanMap.get("mbRet").toString();
			list.mbTotalUp = (String) beanMap.get("mbJun").toString();
			dtoList.add(list);
		}
		return dtoList;
	}

	/**
	 * 分析レポート検索 日時
	 * @return
	 */
	public  List<MemberReportDto> findByMemberReportListDay(MemberReportForm memberReportForm) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT insert_date AS DATE,SUM(ALL_REG) AS ALL_REG,SUM(ALL_RET) AS ALL_RET,SUM(ALL_JUN) AS ALL_JUN");
				sb.append(" ,SUM(PC_REG) AS PC_REG,SUM(PC_RET) AS PC_RET,SUM(PC_JUN) AS PC_JUN");
				sb.append(" ,SUM(MB_REG) AS MB_REG,SUM(MB_RET) AS MB_RET,SUM(MB_JUN) AS MB_JUN");
				sb.append(" FROM (");
				sb.append("		SELECT  '1',M.insert_date AS INSERT_DATE");
				sb.append("			,COUNT(1) ALL_REG");
				sb.append("			,0        ALL_RET ");
				sb.append("			,COUNT(1) ALL_JUN ");
				sb.append("			,SUM(CASE WHEN COALESCE(LTRIM(pc_email),'') != '' THEN 1 ELSE 0 END ) PC_REG");
				sb.append("			,0                                                                    PC_RET");
				sb.append("			,SUM(CASE WHEN COALESCE(LTRIM(pc_email),'') != '' THEN 1 ELSE 0 END ) PC_JUN");
				sb.append("			,SUM(CASE WHEN COALESCE(LTRIM(mb_email),'') != '' THEN 1 ELSE 0 END ) MB_REG");
				sb.append("			,0                                                                    MB_RET");
				sb.append("			,SUM(CASE WHEN COALESCE(LTRIM(mb_email),'') != '' THEN 1 ELSE 0 END ) MB_JUN");
				sb.append("			FROM");
				sb.append("			(SELECT  member_id,TO_CHAR(insert_date, 'yyyyMMdd') AS insert_date,mb_email,pc_email ");
				sb.append("			FROM	m_member WHERE TO_CHAR(INSERT_DATE,'yyyyMMdd') BETWEEN ? AND ?) M");
				sb.append("		GROUP BY M.insert_date ");
				sb.append("		UNION ALL");
				sb.append("		SELECT '2',W.insert_date INSERT_DATE");
				sb.append("		       ,0 ALL_REG");
				sb.append("		       ,COUNT(1) ALL_RET");
				sb.append("		       ,-COUNT(1) ALL_JUN");
				sb.append("			,0                                                                      PC_REG");
				sb.append("			,SUM(CASE WHEN COALESCE(LTRIM(W.pc_email),'') != '' THEN 1 ELSE 0 END ) PC_RET");
				sb.append("			,-SUM(CASE WHEN COALESCE(LTRIM(W.pc_email),'') != '' THEN 1 ELSE 0 END ) PC_JUN");
				sb.append("			,0                                                                      MB_REG");
				sb.append("			,SUM(CASE WHEN COALESCE(LTRIM(mb_email),'') != '' THEN 1 ELSE 0 END )   MB_RET");
				sb.append("			,-SUM(CASE WHEN COALESCE(LTRIM(mb_email),'') != '' THEN 1 ELSE 0 END )   MB_JUN");
				sb.append("		FROM");
				sb.append("			(SELECT  TO_CHAR(WIT.INSERT_DATE,'yyyyMMdd') AS insert_date");
				sb.append("				,MEM.pc_email");
				sb.append("				,MEM.mb_email");
				sb.append("			FROM t_member_withdraw WIT");
				sb.append("				INNER JOIN m_member MEM");
				sb.append("				ON MEM.member_id = WIT.member_id");
				sb.append("				WHERE TO_CHAR(WIT.INSERT_DATE,'yyyyMMdd') BETWEEN ? AND ?) W");
				sb.append("		GROUP BY W.insert_date ");
				sb.append(") TEMP_RESULT");
				sb.append(" GROUP BY insert_date");
				sb.append(" ORDER BY INSERT_DATE DESC");
		List<Object> values = new ArrayList<Object>();
			values.add(memberReportForm.fromDay.replaceAll("/", ""));
			values.add(memberReportForm.toDay.replaceAll("/", ""));
			values.add(memberReportForm.fromDay.replaceAll("/", ""));
			values.add(memberReportForm.toDay.replaceAll("/", ""));
		List<BeanMap> tempResult = jdbcManager.selectBySql(BeanMap.class, sb.toString(),values.toArray()).getResultList();

		List<MemberReportDto> dtoList = new ArrayList<MemberReportDto> ();
		for (BeanMap beanMap : tempResult) {
			MemberReportDto list = new MemberReportDto();
			list.date = (String) beanMap.get("date");
			list.total = (String) beanMap.get("allReg").toString();
			list.totalT = (String) beanMap.get("allRet").toString();
			list.totalUp = (String) beanMap.get("allJun").toString();
			list.pcTotal = (String) beanMap.get("pcReg").toString();
			list.pcTotalt = (String) beanMap.get("pcRet").toString();
			list.pcTotalUp = (String) beanMap.get("pcJun").toString();
			list.mbTotal = (String) beanMap.get("mbReg").toString();
			list.mbTotalt = (String) beanMap.get("mbRet").toString();
			list.mbTotalUp = (String) beanMap.get("mbJun").toString();
			dtoList.add(list);
		}
		return dtoList;
	}

	//管理者ユーザ検索
	public  List<Member> findByMemberListCvs(MemberSearchForm memberSearchForm) {

		boolean flg = false;
		StringBuffer sql = new StringBuffer();
		List<Object> values = new ArrayList<Object>();
		if (!StringUtil.isBlank(memberSearchForm.memberId)) {
			sql.append(" CAST(T1_.member_id as varchar) = ? ");
			//values.add(Integer.parseInt(memberSearchForm.memberId));
			values.add(memberSearchForm.memberId);
			flg = true;
		}

//		if (!StringUtil.isBlank(memberSearchForm.loginId)) {
//			if (flg) sql.append(" and ");
//			sql.append("login_Id = ? ");
//			values.add(memberSearchForm.loginId);
//			flg = true;
//
//		}

		if (!StringUtil.isBlank(memberSearchForm.loginId)) {
			if (flg) sql.append(" and ");
			sql.append("login_Id like ? ");
			values.add("%"+memberSearchForm.loginId.trim().replace("%", "\\%")+"%");
			//values.add(memberSearchForm.loginId);
			flg = true;

		}

		if (!StringUtil.isBlank(memberSearchForm.firstName)) {
			if (flg) sql.append(" and ");
			sql.append("first_name like ? ");
			values.add("%"+memberSearchForm.firstName.trim().replace("%", "\\%")+"%");
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.lastName)) {
			if (flg) sql.append(" and ");
			sql.append("last_name like ? ");
			values.add("%"+memberSearchForm.lastName.trim().replace("%", "\\%")+"%");
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.firstNameKana)) {
			if (flg) sql.append(" and ");
			sql.append("first_name_kana like ? ");
			values.add("%"+memberSearchForm.firstNameKana.trim().replace("%", "\\%")+"%");
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.lastNameKana)) {
			if (flg) sql.append(" and ");
			sql.append("last_name_kana like ? ");
			values.add("%"+memberSearchForm.lastNameKana.trim().replace("%", "\\%")+"%");
			flg = true;
		}
		if (!StringUtil.isBlank(memberSearchForm.pcEmail)) {
			if (flg) sql.append(" and ");
			sql.append("pc_email like ? ");
			values.add("%"+memberSearchForm.pcEmail.trim().replace("%", "\\%")+"%");
			flg = true;
		}
		if (!StringUtil.isBlank(memberSearchForm.mbEmail)) {
			if (flg) sql.append(" and ");
			sql.append("mb_email like ? ");
			values.add("%"+memberSearchForm.mbEmail.trim().replace("%", "\\%")+"%");
			flg = true;
		}
		if (!StringUtil.isBlank(memberSearchForm.telNo)) {
			if (flg) sql.append(" and ");
			sql.append("telno like ? ");
			values.add("%"+memberSearchForm.telNo.trim().replace("%", "\\%")+"%");
			flg = true;
		}
		if (!StringUtil.isBlank(memberSearchForm.genderCd)) {
			if (flg) sql.append(" and ");
			sql.append(" CAST(gender_cd as varchar) = ? ");
			//values.add(Integer.parseInt(memberSearchForm.genderCd));
			values.add(memberSearchForm.genderCd);
			flg = true;
		}

//		if (!StringUtil.isBlank(memberSearchForm.birthdayY+memberSearchForm.birthdayM+memberSearchForm.birthdayD)) {
//			if (flg) sql.append(" and ");
//			sql.append("birthday = ? ");
//			values.add(memberSearchForm.birthdayY+memberSearchForm.birthdayM+memberSearchForm.birthdayD);
//			flg = true;
//		}
//		if (!StringUtil.isBlank(memberSearchForm.age)) {
//			if (flg) sql.append(" and ");
//			if (memberSearchForm.age.equals("100")) {
//				sql.append(" COALESCE(birthDay,'') != '' AND (FLOOR((to_number (to_char (current_timestamp, 'yyyyMMdd'), '99999999') - to_number (birthday, '99999999')) / 100000) * 10) > ?");
//			} else {
//				sql.append(" COALESCE(birthDay,'') != '' AND (FLOOR((to_number (to_char (current_timestamp, 'yyyyMMdd'), '99999999') - to_number (birthday, '99999999')) / 100000) * 10) = ?");
//			}
//			values.add(Integer.parseInt(memberSearchForm.age));
//			flg = true;
//		}

		if (!StringUtil.isBlank(memberSearchForm.birthdayY+memberSearchForm.birthdayM+memberSearchForm.birthdayD) &&
				!StringUtil.isBlank(memberSearchForm.birthdayY1+memberSearchForm.birthdayM1+memberSearchForm.birthdayD1)) {
			if (flg) sql.append(" and ");
			sql.append("COALESCE(birthday,'') != '' AND birthday BETWEEN ? AND ? ");
			values.add(memberSearchForm.birthdayY+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayM,2)+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayD,2));
			values.add(memberSearchForm.birthdayY1+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayM1,2)+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayD1,2));
			flg = true;
		} else {
			if (!StringUtil.isBlank(memberSearchForm.birthdayY+memberSearchForm.birthdayM+memberSearchForm.birthdayD)) {
				if (flg) sql.append(" and ");
				sql.append("COALESCE(birthday,'') != '' AND birthday >= ? ");
				values.add(memberSearchForm.birthdayY+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayM,2)+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayD,2));
				flg = true;
			}
			if (!StringUtil.isBlank(memberSearchForm.birthdayY1+memberSearchForm.birthdayM1+memberSearchForm.birthdayD1)) {
				if (flg) sql.append(" and ");
				sql.append("COALESCE(birthday,'') != '' AND birthday <= ? ");
				values.add(memberSearchForm.birthdayY1+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayM1,2)+SinaburoUtil.LeftPadZero(memberSearchForm.birthdayD1,2));
				flg = true;
			}
		}

		//年齢検索
		if (!StringUtil.isBlank(memberSearchForm.age) && !StringUtil.isBlank(memberSearchForm.age1)) {
			if (flg) sql.append(" and ");
			sql.append(" COALESCE(birthDay,'') != '' AND (FLOOR((to_number (to_char (current_timestamp, 'yyyyMMdd'), '99999999') - to_number (birthday, '99999999')) / 100000) * 10) BETWEEN ? AND ?");
			values.add(Integer.parseInt(memberSearchForm.age));
			values.add(Integer.parseInt(memberSearchForm.age1));
			flg = true;
		} else {
			if (!StringUtil.isBlank(memberSearchForm.age)) {
				if (flg) sql.append(" and ");
				sql.append(" COALESCE(birthDay,'') != '' AND (FLOOR((to_number (to_char (current_timestamp, 'yyyyMMdd'), '99999999') - to_number (birthday, '99999999')) / 100000) * 10) >= ?");
				values.add(Integer.parseInt(memberSearchForm.age));
				flg = true;
			}
			if (!StringUtil.isBlank(memberSearchForm.age1)) {
				if (flg) sql.append(" and ");
				sql.append(" COALESCE(birthDay,'') != '' AND (FLOOR((to_number (to_char (current_timestamp, 'yyyyMMdd'), '99999999') - to_number (birthday, '99999999')) / 100000) * 10) <= ?");
				values.add(Integer.parseInt(memberSearchForm.age1));
				flg = true;
			}
		}

		//登録日検索
		if (!StringUtil.isBlank(memberSearchForm.fromDay) && !StringUtil.isBlank(memberSearchForm.toDay)) {
			if (flg) sql.append(" and ");
			sql.append(" TO_CHAR(T1_.insert_date,'yyyyMMdd') BETWEEN ? AND ? ");
			values.add(memberSearchForm.fromDay.replaceAll("/", ""));
			values.add(memberSearchForm.toDay.replaceAll("/", ""));
			flg = true;
		} else {
			if (!StringUtil.isBlank(memberSearchForm.fromDay)) {
				if (flg) sql.append(" and ");
				sql.append(" TO_CHAR(T1_.insert_date,'yyyyMMdd') >= ? ");
				values.add(memberSearchForm.fromDay.replaceAll("/", ""));
				flg = true;
			}
			if (!StringUtil.isBlank(memberSearchForm.toDay)) {
				if (flg) sql.append(" and ");
				sql.append("  TO_CHAR(T1_.insert_date,'yyyyMMdd') <= ? ");
				values.add(memberSearchForm.toDay.replaceAll("/", ""));
				flg = true;
			}
		}

		if (!StringUtil.isBlank(memberSearchForm.prefectureId)) {
			if (flg) sql.append(" and ");
			sql.append("CAST(prefecture_id as varchar) = ? ");
			values.add(memberSearchForm.prefectureId);
			flg = true;
		}
//		if (!StringUtil.isBlank(memberSearchForm.zipCode)) {
//			if (flg) sql.append(" and ");
//			sql.append("zip_code = ? ");
//			values.add(memberSearchForm.zipCode);
//			flg = true;
//		}

		if (!StringUtil.isBlank(memberSearchForm.zipCode) && !StringUtil.isBlank(memberSearchForm.zipCode1)) {
			if (flg) sql.append(" and ");
			sql.append("COALESCE(zip_code,'') != '' AND zip_code BETWEEN ? AND ? ");
			values.add(memberSearchForm.zipCode);
			values.add(memberSearchForm.zipCode1);
			flg = true;
		} else {
			if (!StringUtil.isBlank(memberSearchForm.zipCode)) {
				if (flg) sql.append(" and ");
				sql.append(" COALESCE(zip_code,'') != '' AND zip_code >= ? ");
				values.add(memberSearchForm.zipCode);
				flg = true;
			}
			if (!StringUtil.isBlank(memberSearchForm.zipCode1)) {
				if (flg) sql.append(" and ");
				sql.append("COALESCE(zip_code,'') != '' AND zip_code <= ? ");
				values.add(memberSearchForm.zipCode1);
				flg = true;
			}
		}


		if (!StringUtil.isBlank(memberSearchForm.areaName)) {
			if (flg) sql.append(" and ");
			sql.append("area_name like ? ");
			values.add("%"+memberSearchForm.areaName.trim().replace("%", "\\%")+"%");
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.cityName)) {
			if (flg) sql.append(" and ");
			sql.append("city_name like ? ");
			values.add("%"+memberSearchForm.cityName.trim().replace("%", "\\%")+"%");
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.buildingName)) {
			if (flg) sql.append(" and ");
			sql.append("building_name like ? ");
			values.add("%"+memberSearchForm.buildingName.trim().replace("%", "\\%")+"%");
			flg = true;
		}

//		if (!StringUtil.isBlank(memberSearchForm.point)) {
//			if (flg) sql.append(" and ");
//			sql.append("CAST(point_num as varchar) = ? ");
//			values.add(memberSearchForm.point);
//			flg = true;
//		}

		if (!StringUtil.isBlank(memberSearchForm.point)) {
			if (flg) sql.append(" and ");
			//sql.append("CAST(point_num as varchar) >= ? ");
			sql.append("point_num >= ? ");
			values.add(Integer.parseInt(memberSearchForm.point));
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.memo)) {
			if (flg) sql.append(" and ");
			sql.append("memo like ?");
			values.add("%"+memberSearchForm.memo.trim().replace("%", "\\%")+"%");
			flg = true;
		}

		if (!StringUtil.isBlank(memberSearchForm.withDrawFlg) && !memberSearchForm.withDrawFlg.equals("1")) {
			if (memberSearchForm.withDrawFlg.equals("0")) {
				if (flg) sql.append(" and ");
				sql.append("CAST(del_flg as varchar) = ?");
				values.add("0");
			} else if (memberSearchForm.withDrawFlg.equals("2")) {
				if (flg) sql.append(" and ");
				sql.append("CAST(del_flg as varchar) = ?");
				values.add("1");
			}
			flg = true;
		}

		if (flg) {
			sql.append(" and ");
			sql.append("CAST(del_flg as varchar) <> ?");
			values.add("2");
		}

		List<Member> memberList;

		//配信グルプ作成
		if (flg) {
			memberList = select().leftOuterJoin("memberWithdraw").where(sql.toString(),values.toArray())
					.orderBy("memberId asc").getResultList();
		} else {
			memberList = select().leftOuterJoin("memberWithdraw").where(new SimpleWhere().ne("delFlg", "2"))
					.orderBy("memberId asc").getResultList();
		}


		return memberList;
	}

	// 管理者からの会員編集
	// 削除フラグがあると退会テーブルに新規登録
	public int updateMember(Member member,String id) {

    	int retValue = 0;
    	//ポイントチェック
    	int nowPoint = Integer.parseInt( pointGetService.findPointByMemberIdAdmin(member.memberId));

		if (!member.pointNum.equals(nowPoint)) {

			//登録ポイントチェック
			if(nowPoint > member.pointNum){
				pointGetService.upDownPointLogic(nowPoint-member.pointNum,member.memberId,id);
			} else {
    			PointGet pointGet = new PointGet();
    	    	pointGet.memberId = member.memberId;
    	    	pointGet.pointGetDate = SinaburoUtil.getToDayString();
    	    	pointGet.pointGetNum = member.pointNum-nowPoint;
    	    	pointGet.pointUseNum = 0;
    	    	pointGet.validFlg = SinaburoConstant.ValidFlg.VALID;

    			pointGetService.insertPointGet(pointGet, id);
			}
		}

		//更新
    	retValue = super.update(member, id);

    	//退会情報テーブル新規登録
    	if (retValue == 1) {
			MemberWithdraw memberWithdrawFlg = new MemberWithdraw();
			memberWithdrawFlg = memberWithdrawService.findBymemberId(member.memberId);

    		if (member.delFlg == 1 ) {
	    		MemberWithdraw memberWithdraw = new MemberWithdraw();
	    		memberWithdraw.memberId = member.memberId;
	    		if (memberWithdrawFlg == null ) {
	    			retValue = memberWithdrawService.insertMemberWithdraw(memberWithdraw, id);
	    		}
    		} else {
    			MemberWithdraw memberWithdraw = new MemberWithdraw();
    	    	memberWithdraw = memberWithdrawService.findBymemberId(member.memberId);
    	    	if (memberWithdraw != null) {
    	    		memberWithdrawService.delete(memberWithdraw);
    	    	}
    		}
		}

    	return retValue;
    }

	// 管理者からの会員物理削除
	// MemberIdがあるテーブルすべて削除
	public int deleteMember(String memberId,String id){

    	int retValue = 0;

		//削除
    	//退会テーブル
//    //	MemberWithdraw memberWithdraw = new MemberWithdraw();
//    	List<MemberWithdraw> memberWithdrawList = memberWithdrawService.findBymemberIdList(Integer.parseInt(memberId));
//    	for (MemberWithdraw memberWithdraw : memberWithdrawList) {
//    		memberWithdrawService.delete(memberWithdraw);
//    	}
//
//    	//PasswordMailSend passwordMailSend = new PasswordMailSend();
//    	List<PasswordMailSend> passwordMailSendList = passwordMailSendService.findBymemberIdList(Integer.parseInt(memberId));
//    	for (PasswordMailSend passwordMailSend : passwordMailSendList) {
//    		passwordMailSendService.delete(passwordMailSend);
//    	}
//
//    	//PointDonation pointDonation = new PointDonation();
//    	List <PointDonation> pointDonationList = pointDonationService.findBymemberIdList(Integer.parseInt(memberId));
//    	for (PointDonation pointDonation : pointDonationList) {
//    		pointDonationService.delete(pointDonation);
//    	}
//
//    	//PointGet pointGet = new PointGet();
//    	List<PointGet> pointGetList = pointGetService.findBymemberIdList(Integer.parseInt(memberId));
//		for (PointGet pointGet : pointGetList) {
//			pointGetService.delete(pointGet);
//    	}
//
//    	//GiftExchange giftExchange = new GiftExchange();
//    	List<GiftExchange> giftExchangeList = giftExchangeService.findBymemberIdList(Integer.parseInt(memberId));
//    	for (GiftExchange giftExchange : giftExchangeList) {
//    		giftExchangeService.delete(giftExchange);
//    	}
//
//
////    	List<DeliveryGroupMember> deliveryGroupMemberList = deliveryGroupMemberService.findBymemberIdList(Integer.parseInt(memberId));
////		for (DeliveryGroupMember member : deliveryGroupMemberList) {
////			deliveryGroupMemberService.delete(member);
////		}
////    	if (deliveryGroupMemberList.size() != 0) {
////    		deliveryGroupMember.memberId = Integer.parseInt(memberId);
////    		deliveryGroupMemberService.delete(deliveryGroupMember);
////    	}
//
//    	//Reply reply = new Reply();
//    	List<Reply> replyList = replyService.findBymemberIdList(Integer.parseInt(memberId));
//    	for (Reply reply : replyList) {
//    		replyService.delete(reply);
//    	}
//
//    	List<EnqueteMailSendResult> enqueteMailSendResultList = enqueteMailSendResultService.findAllOrderByIdList(Integer.parseInt(memberId));
//    	for (EnqueteMailSendResult info : enqueteMailSendResultList ) {
//    		enqueteMailSendResultService.delete(info);
//    	}

    	List<DeliveryGroupMember> deliveryGroupMemberList = deliveryGroupMemberService.findBymemberIdList(Integer.parseInt(memberId));
		for (DeliveryGroupMember member : deliveryGroupMemberList) {

			List<DeliveryGroup> list = deliveryGroupService.findAllOrderByIdAllList(member.deliveryGroupId);

			if (!CollectionUtils.isEmpty(list) && list.get(0).deliveryGroupMemberList2.size() == 1) {
				for (DeliveryGroup group : list) {
					deliveryGroupService.deliteDeliveryGroup(String.valueOf(group.deliveryGroupId), id);
				}
			}
			//deliveryGroupMemberService.delete(member);
		}

		List<DeliveryGroupMember> deliveryGroupMemberList2 = deliveryGroupMemberService.findBymemberIdList(Integer.parseInt(memberId));
		for (DeliveryGroupMember member : deliveryGroupMemberList2) {
			member.delFlg = 1;
			deliveryGroupMemberService.update(member, id);
		}

    	Member member = new Member();
    	member = findByDeleteId(Integer.parseInt(memberId));
    	member.loginId = "";
    	member.password = "";
    	member.firstName = null;
    	member.lastName = null;
    	member.firstNameKana = null;
    	member.lastNameKana = null;
    	member.pcEmail= null;
    	member.mbEmail = null;
    	member.zipCode = null;
    	member.prefectureId = null;
    	member.cityName = null;
    	member.areaName = null;
    	member.buildingName = null;
    	member.telno = null;
    	member.pointNum = 0;
    	member.validFlg = 0;
    	member.delFlg = 2;

    	//super.delete(member);

    	super.update(member, id);

    	return retValue;
    }

	//一括登録
	public int memberAllRegist(List<MemberInfoCsv> memberCsvList,AdminUser adminUserInfo){

    	int totalCount =0;

    	for (int i=0;i<memberCsvList.size();i++) {

    		MemberInfoCsv memberInfoCsv = memberCsvList.get(i);

    		if (memberInfoCsv.deleteFlg.equals("1")) {
    			if (memberInfoCsv.memberId != null) {
    				Member member = new Member();
		    		member = findByDeleteId(Integer.parseInt(memberInfoCsv.memberId));
		    		totalCount += deleteMember(String.valueOf(member.memberId),adminUserInfo.loginId);
    			}
    		} else {
    			int nowPoint;
	    		//会員IDがない場合、新規登録StringUtil.equals(memberInfoCsv.memberId, "")
	    		//if (memberInfoCsv.memberId == null) {
    			if (StringUtil.equals(memberInfoCsv.memberId, "")) {
	    			Member memberIns = Beans.createAndCopy( Member.class,memberInfoCsv).execute();
	    			memberIns.validFlg = 1;
	    			memberIns.delFlg = 0;
	    			memberIns.authFailNum = 0;

	    			totalCount += insertMember(memberIns, adminUserInfo.loginId);

	    			PointGet pointGet = new PointGet();
	    	    	pointGet.memberId = MEMBER_ID;
	    	    	pointGet.pointGetDate = SinaburoUtil.getToDayString();
	    	    	pointGet.pointGetNum = memberIns.pointNum;
	    	    	pointGet.pointUseNum = 0;
	    	    	pointGet.validFlg = SinaburoConstant.ValidFlg.VALID;

	    			pointGetService.insertPointGet(pointGet, adminUserInfo.loginId);

	    		} else {
	    			Member member = new Member();
//	    			if (!StringUtil.equals(memberInfoCsv.memberId, "")) {
	    			member = findByDeleteId(Integer.parseInt(memberInfoCsv.memberId));
//	    			} else {
//	    				member = null;
//	    			}

		    		if (member == null) {
		    			Member memberIns = Beans.createAndCopy(Member.class,memberInfoCsv).execute();
		    			memberIns.validFlg = 1;
		    			memberIns.delFlg = 0;
		    			memberIns.authFailNum = 0;


		    			totalCount += insertMember(memberIns, adminUserInfo.loginId);

		    			PointGet pointGet = new PointGet();
		    	    	pointGet.memberId = MEMBER_ID;
		    	    	pointGet.pointGetDate = SinaburoUtil.getToDayString();
		    	    	pointGet.pointGetNum = memberIns.pointNum;
		    	    	pointGet.pointUseNum = 0;
		    	    	pointGet.validFlg = SinaburoConstant.ValidFlg.VALID;

		    			pointGetService.insertPointGet(pointGet, adminUserInfo.loginId);

		    		} else {
		    			nowPoint = Integer.parseInt(pointGetService.findPointByMemberIdAdmin(member.memberId));

		    			Member memberUpd = Beans.createAndCopy(Member.class,memberInfoCsv).execute();
//		    			memberUpd.validFlg = member.validFlg;
//		    			memberUpd.delFlg =  member.delFlg;
		    			memberUpd.authFailNum = member.authFailNum;
		    			memberUpd.memberId = member.memberId;
		    			memberUpd.version = member.version;
		    			memberUpd.insertDate = member.insertDate;
		    			memberUpd.insId = member.insId;

		    			if (memberInfoCsv.drawFlg.equals("1")) {
		    				memberUpd.validFlg = 0;
		    			} else {
		    				memberUpd.validFlg = 1;
		    			}
		    			memberUpd.delFlg =  Integer.parseInt(memberInfoCsv.drawFlg);

		    			if (!memberUpd.pointNum.equals(nowPoint)) {

		    				if(nowPoint > memberUpd.pointNum){
		    					pointGetService.upDownPointLogic(nowPoint-memberUpd.pointNum,member.memberId,adminUserInfo.loginId);
		    				} else {
				    			PointGet pointGet = new PointGet();
				    	    	pointGet.memberId = member.memberId;
				    	    	pointGet.pointGetDate = SinaburoUtil.getToDayString();
				    	    	pointGet.pointGetNum = memberUpd.pointNum-nowPoint;
				    	    	pointGet.pointUseNum = 0;
				    	    	pointGet.validFlg = SinaburoConstant.ValidFlg.VALID;

				    			pointGetService.insertPointGet(pointGet, adminUserInfo.loginId);
		    				}
		    			}

		    			int retValue = super.update(memberUpd, adminUserInfo.loginId);

		    			//退会有無チェック
		    	    	if (retValue == 1) {
		    				MemberWithdraw memberWithdrawFlg = new MemberWithdraw();
		    				memberWithdrawFlg = memberWithdrawService.findBymemberId(member.memberId);

		    	    		if (memberInfoCsv.drawFlg.equals("1")) {
		    		    		MemberWithdraw memberWithdraw = new MemberWithdraw();
		    		    		memberWithdraw.memberId = member.memberId;
		    		    		if (memberWithdrawFlg == null ) {
			    					retValue = memberWithdrawService.insertMemberWithdraw(memberWithdraw, adminUserInfo.loginId);
		    		    		}
		    	    		} else {
		    	    			MemberWithdraw memberWithdraw = new MemberWithdraw();
		    	    	    	memberWithdraw = memberWithdrawService.findBymemberId(member.memberId);
		    	    	    	if (memberWithdraw != null) {
		    	    	    		memberWithdrawService.delete(memberWithdraw);
		    	    	    	}
		    	    		}
		    			}
		    	    	totalCount += retValue;
		    		}
	    		}
    		}

		}

		return totalCount;
	}

	//管理者登録時 ポイントテーブル登録
	public int insertMemberAdmin(Member member, String insId) {
		member.memberId = getSeqNextVal(Integer.class, ID_SEQ_NAME);

		PointGet pointGet = new PointGet();
    	pointGet.memberId = member.memberId;
    	pointGet.pointGetDate = SinaburoUtil.getToDayString();
    	pointGet.pointGetNum = member.pointNum;
    	pointGet.pointUseNum = 0;
    	pointGet.validFlg = SinaburoConstant.ValidFlg.VALID;

		pointGetService.insertPointGet(pointGet, insId);
		return super.insert(member, insId);
	}

	/**
	 * 会員情報を会員IDで取得する。
	 * @param memberIdList
	 * @return
	 */
	public List<Member> getMemberList(List<Integer> memberIdList){
		return select().where(new SimpleWhere().in("memberId", memberIdList)).orderBy("memberId asc").getResultList();
	}
}