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

}