package org.jleaf.erp.mlm.bo.member;

import java.util.List;

import javax.persistence.Query;

import org.jleaf.core.AbstractBusinessFunction;
import org.jleaf.core.BusinessFunction;
import org.jleaf.core.Dto;
import org.jleaf.core.GeneralConstants;
import org.jleaf.core.annotation.Info;
import org.jleaf.core.annotation.InfoIn;
import org.jleaf.core.annotation.InfoOut;
import org.jleaf.core.dao.CriteriaHelper;
import org.jleaf.core.dao.CriteriaHelper.CriteriaHelperEnum;
import org.jleaf.core.dao.QueryBuilder;
import org.jleaf.erp.mlm.dao.MemberDao;
import org.jleaf.erp.mlm.entity.Member;
import org.jleaf.util.DtoUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;


/**
 * @author Alvin Yurisman, 16 February 2015
 *
 * modified 2 April 2015 : add keyword filter
 * modified 28 Januari 2016 : tambah where name saat menerima parameter _keyword
 * modified 11 September 2017 : mengembalikan data DS Id, DS code dan DS name
 */

@Service
@InfoIn(value = {@Info(name = "memberCode", description = "member code", type = String.class, required=false),
                 @Info(name = "name", description = "name", type = String.class, required=false),
                 @Info(name = "mobilePhoneNumber", description = "Mobile Phone Number", type = String.class, required=false),
                 @Info(name = "active", description = "active", type = String.class, required=false),
                 @Info(name = "_keyword", description = "keyword filter", type = String.class, required=false),
                 @Info(name = "limit", description = "offset", type = Long.class, required=false),
                 @Info(name = "offset", description = "limit", type = Long.class, required=false)})
@InfoOut(value = {@Info(name = "memberList", description = "member list (memberId, memberCode, name, uplineId, uplineName, sponsorId, sponsorName, active, flgDataComplete, mobilePhone, activeDatetime, registeredDate,dsId,dsCode,dsName)")})

public class GetMemberList extends AbstractBusinessFunction implements BusinessFunction {

    @Autowired
    MemberDao memberDao;

    @SuppressWarnings("unchecked")
    @Override
    public Dto execute(Dto inputDto) throws Exception {
        List<Object[]> result = null;

        String keyword = inputDto.getString("_keyword");
        String mobilePhoneNumber = GeneralConstants.EMPTY_VALUE;

        if(inputDto.get("mobilePhoneNumber")!=null){
            mobilePhoneNumber = inputDto.getString("mobilePhoneNumber");
        }

        QueryBuilder builder = new QueryBuilder();
        builder.add(" SELECT a.member_id, a.member_code, a.name, b.member_code as uplineId, b.name as uplineName, ")
            .add(" c.member_code as sponsorId, c.name as sponsorName, a.active, a.flg_data_complete, a.mobile_phone, SUBSTRING(a.active_datetime,1,6) as active_date , d.registered_date, ")
            .add(" X.member_id as ds_id,X.member_code as ds_code,X.name as ds_name")
            .add(" FROM mlm_member a ")
            .add(" INNER JOIN mlm_member b ON a.upline_id = b.member_id ")
            .add(" INNER JOIN mlm_member c ON a.sponsor_id = c.member_id ")
            .add(" INNER JOIN mlm_bkt d ON a.member_code = d.bkt_code ")
            .add(" LEFT JOIN ").add(Member.TABLE_NAME).add(" X ON X.member_id =A.ds_id ")
            .add(" WHERE true")
            .addIfNotEmpty(keyword, " AND ( "
              + CriteriaHelper.likeExpressionIgnoreCase(keyword, "a.member_code", CriteriaHelperEnum.BOTH)
              + " OR  "
              + CriteriaHelper.likeExpressionIgnoreCase(keyword, "a.mobile_phone", CriteriaHelperEnum.BOTH)
              + " OR  "
              + CriteriaHelper.likeExpressionIgnoreCase(keyword, "a.name", CriteriaHelperEnum.BOTH)
              +" ) ")
            .addIfNotEmpty(inputDto.getString("memberCode"), " AND " + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("memberCode"), "a.member_code"))
            .addIfNotEmpty(mobilePhoneNumber, " AND " + CriteriaHelper.likeExpressionIgnoreCase(mobilePhoneNumber, "a.mobile_phone"))
            .addIfNotEmpty(inputDto.getString("name"), " AND " + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("name"), "a.name"))
            .addIfNotEmpty(inputDto.getString("active"), " AND " + CriteriaHelper.equalsExpressionIgnoreCase(inputDto.getString("active"), "a.active"))
            .add(" ORDER BY a.member_code, a.name ASC ");
            if(inputDto.getLong("limit")!=null) builder.add(" LIMIT " + inputDto.getLong("limit"));
            if(inputDto.getLong("offset")!=null) builder.add(" OFFSET " + inputDto.getLong("offset"));

        Query query = memberDao.createNativeQuery(builder.toString());
        result = query.getResultList();

        return new Dto().putList("memberList", DtoUtil.createDtoListFromArray(result, "memberId", "memberCode", "name", "uplineId", "uplineName", "sponsorId", "sponsorName", "active", "flgDataComplete","mobilePhone","activeDatetime","registeredDate","dsId","dsCode","dsName"));
    }

    @Override
    public String getDescription() {
        return "return get member list";
    }

}