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