package jp.agentec.sinaburocast.service; import java.util.ArrayList; import java.util.List; import jp.agentec.sinaburocast.common.SinaburoConstant; import jp.agentec.sinaburocast.csv.DeliveryGroupInfoCsv; import jp.agentec.sinaburocast.dto.GroupDetailDto; import jp.agentec.sinaburocast.dto.MemberDetailDto; import jp.agentec.sinaburocast.entity.DeliveryGroup; import jp.agentec.sinaburocast.entity.DeliveryGroupMember; import jp.agentec.sinaburocast.form.admin.member.DeliveryGroupSearchForm; import org.seasar.extension.jdbc.where.SimpleWhere; import org.seasar.framework.beans.util.BeanMap; 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 DeliveryGroupService extends AbstractService<DeliveryGroup> { private static final String ID_SEQ_NAME = "delivery_group_id_seq"; public DeliveryGroupMemberService deliveryGroupMemberService; public DeliveryGroup findById(Integer deliveryGroupId) { return select().id(deliveryGroupId).where(new SimpleWhere() .eq("delFlg", SinaburoConstant.MemberDelFlg.NOT_DEL)).getSingleResult(); } public DeliveryGroup findByIdName(Integer deliveryGroupId) { return select().id(deliveryGroupId).getSingleResult(); } public List<DeliveryGroup> findAllOrderById() { return select().where(new SimpleWhere() .eq("delFlg", SinaburoConstant.MemberDelFlg.NOT_DEL)).orderBy("insert_date desc").getResultList(); } public List<DeliveryGroup> findAllOrderById(Integer deliveryGroupId) { return select().where(new SimpleWhere() .eq("delFlg", SinaburoConstant.MemberDelFlg.NOT_DEL).ne("deliveryGroupId", deliveryGroupId)).orderBy("updateDate asc").getResultList(); } public List<DeliveryGroup> findAllOrderByIdAllList(Integer deliveryGroupId) { // return select().leftOuterJoin("deliveryGroupMember") // .where(new SimpleWhere().eq("deliveryGroupId", deliveryGroupId) // .eq("delFlg",0)).getResultList(); return select().leftOuterJoin("deliveryGroupMemberList2") .where(new SimpleWhere().eq("deliveryGroupId", deliveryGroupId) .eq("delFlg",0)).getResultList(); } /** * 配信グループ登録 * */ public int insertDeliveryGroup(DeliveryGroup deliveryGroup, List<MemberDetailDto> list, String adminId) { int retValue = 0; int deliveryGroupId = getSeqNextVal(Integer.class, ID_SEQ_NAME); deliveryGroup.deliveryGroupId = deliveryGroupId; //登録 retValue = super.insert(deliveryGroup, adminId); if (retValue == 1) { for (MemberDetailDto member : list) { DeliveryGroupMember deliveryGroupMember = new DeliveryGroupMember(); deliveryGroupMember.deliveryGroupId = deliveryGroupId; deliveryGroupMember.memberId = Integer.parseInt(member.memberId); deliveryGroupMember.delFlg = 0; retValue = deliveryGroupMemberService.insert(deliveryGroupMember, adminId); } } return retValue; } /** * 配信グループ一括登録 * @param deliveryGroup * @param list * @param adminId * @return */ public int insertDeliveryGroupUpload(DeliveryGroup deliveryGroup, List<DeliveryGroupInfoCsv> list, String adminId) { int retValue = 0; int deliveryGroupId = getSeqNextVal(Integer.class, ID_SEQ_NAME); deliveryGroup.deliveryGroupId = deliveryGroupId; //登録 retValue = super.insert(deliveryGroup, adminId); if (retValue == 1) { for (DeliveryGroupInfoCsv member : list) { DeliveryGroupMember deliveryGroupMember = new DeliveryGroupMember(); deliveryGroupMember.deliveryGroupId = deliveryGroupId; deliveryGroupMember.memberId = Integer.parseInt(member.memberId); deliveryGroupMember.delFlg = 0; retValue = deliveryGroupMemberService.insert(deliveryGroupMember, adminId); } } return retValue; } /** * 配信グループ登録 * 配信グループリスト再抽出 * */ public int insertDeliveryGroupRe(DeliveryGroup deliveryGroup, List<DeliveryGroupMember> list, String adminId) { int retValue = 0; int deliveryGroupId = getSeqNextVal(Integer.class, ID_SEQ_NAME); deliveryGroup.deliveryGroupId = deliveryGroupId; deliveryGroup.memo = ""; //登録 retValue = super.insert(deliveryGroup, adminId); if (retValue == 1) { for (DeliveryGroupMember member : list) { member.deliveryGroupId = deliveryGroupId; member.delFlg = 0; retValue = deliveryGroupMemberService.insert(member, adminId); } } return retValue; } public List<GroupDetailDto> findByMemberGroupList(String pageKey,Integer curPage,DeliveryGroupSearchForm deliveryGroupSearchForm,boolean groupflg) { boolean flg = false; StringBuffer sql = new StringBuffer(); List<Object> values = new ArrayList<Object>(); // sql.append("select W.delivery_group_name AS listname,W.memo AS memo, W.update_id AS writer ,W.update_date AS updateday,count(1) AS count,W.delivery_group_id AS groupid"); // sql.append(" from"); // sql.append("(select m.delivery_group_name, m.memo, TO_CHAR(m.update_date,'yyyyMMddHH24miss') as update_date, m.update_id ,m.delivery_group_id "); // sql.append(" from r_delivery_group_member r inner join m_delivery_group m on m.delivery_group_id = r.delivery_group_id "); sql.append("select W.delivery_group_name AS listname,W.memo AS memo, W.update_id AS writer ,W.update_date AS updateday,sum(case WHEN W.member_id is null then 0 else 1 end ) AS count,W.delivery_group_id AS groupid"); sql.append(" from"); sql.append("(select m.delivery_group_name, m.memo, TO_CHAR(m.update_date,'yyyyMMddHH24miss') as update_date, m.update_id ,m.delivery_group_id,r.member_id "); sql.append(" from m_delivery_group m left join r_delivery_group_member r on r.delivery_group_id = m.delivery_group_id "); if (!StringUtil.isBlank(deliveryGroupSearchForm.fromDay) && !StringUtil.isBlank(deliveryGroupSearchForm.fromDay)) { sql.append("where"); sql.append(" TO_CHAR(m.update_date,'yyyyMMdd') BETWEEN ? AND ? "); values.add(deliveryGroupSearchForm.fromDay.replaceAll("/", "")); values.add(deliveryGroupSearchForm.toDay.replaceAll("/", "")); flg = true; } if (!StringUtil.isBlank(deliveryGroupSearchForm.listWriter)) { if (flg) { sql.append(" and "); } else { sql.append(" where "); } sql.append(" m.update_id = ? "); //values.add(Integer.parseInt(memberSearchForm.memberId)); values.add(deliveryGroupSearchForm.listWriter); flg = true; } if (!StringUtil.isBlank(deliveryGroupSearchForm.listName)) { if (flg) { sql.append(" and "); } else { sql.append(" where "); } sql.append(" m.delivery_group_name like ? "); values.add("%"+deliveryGroupSearchForm.listName.trim().replace("%", "\\%")+"%"); flg = true; } if (flg) { sql.append(" and "); } else { sql.append(" where "); } sql.append(" m.del_flg = ? "); values.add(Integer.parseInt(SinaburoConstant.MemberDelFlg.NOT_DEL)); sql.append(" and r.del_flg = ? "); values.add(Integer.parseInt(SinaburoConstant.MemberDelFlg.NOT_DEL)); sql.append(" ) W group by W.delivery_group_name,W.memo, W.update_id ,W.update_date,W.delivery_group_id order by update_date desc"); // AutoSelect<GroupDetailDto> resultCnt; // resultCnt = jdbcManager.selectBySql(BeanMap.class, sql.toString(),values.toArray()).getResultList(); long resultCnt = 0; List<BeanMap> tempResult1 = jdbcManager.selectBySql(BeanMap.class, sql.toString(),values.toArray()).getResultList(); if (tempResult1 != null) { resultCnt = tempResult1.size(); } deliveryGroupSearchForm.setPaging(null, curPage, resultCnt); List<BeanMap> tempResult = jdbcManager.selectBySql(BeanMap.class, sql.toString(),values.toArray()).offset(deliveryGroupSearchForm.getOffSet(pageKey)) .limit(deliveryGroupSearchForm.getLimit(pageKey)).getResultList(); List<GroupDetailDto> dtoList = new ArrayList<GroupDetailDto> (); for (BeanMap beanMap : tempResult) { GroupDetailDto list = new GroupDetailDto(); list.groupId = (String) beanMap.get("groupid").toString(); list.listName = (String) beanMap.get("listname").toString(); list.count = (String) beanMap.get("count").toString(); list.memo = (String) beanMap.get("memo").toString(); list.updateDay = (String) beanMap.get("updateday").toString(); list.writer = (String) beanMap.get("writer").toString(); dtoList.add(list); } return dtoList; } //配信グループ削除 public int deliteDeliveryGroup(String groupId,String updateId) { int retValue = 0; DeliveryGroup deliveryGroup = findById(Integer.parseInt(groupId)); if (deliveryGroup == null) { return retValue; } deliveryGroup.delFlg = 1; retValue = update(deliveryGroup, updateId); if (retValue > 0) { List <DeliveryGroupMember> list = deliveryGroupMemberService.findAllOrderByIdAll(Integer.parseInt(groupId)); for (DeliveryGroupMember member : list) { member.delFlg = 1; deliveryGroupMemberService.update(member, updateId); } } return retValue; } }