package org.jleaf.erp.sls.bo.tasksales;

import java.util.List;

import javax.persistence.Query;

import org.jleaf.common.entity.User;
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.QueryBuilder;
import org.jleaf.erp.master.dao.PartnerDao;
import org.jleaf.erp.master.entity.Partner;
import org.jleaf.erp.master.entity.PartnerAddress;
import org.jleaf.erp.master.entity.PartnerCp;
import org.jleaf.erp.master.entity.PartnerType;
import org.jleaf.erp.sls.SalesConstants;
import org.jleaf.erp.sls.dao.OrderDao;
import org.jleaf.erp.sls.entity.PolicyCustomerGroupBrand;
import org.jleaf.erp.sls.entity.UserPartner;
import org.jleaf.util.DtoUtil;
import org.jleaf.util.ValidationUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 *
 * @author Ephraim Jehudah, April 09, 2020
 *
 **/

//@formatter:off
@Service
@InfoIn(value = {
    @Info(name = "apiKey", description = "api Key", type = String.class),
    @Info(name = "salesmanId", description = "salesmanId", type = Long.class),
    @Info(name = "datetime", description = "datetime", type = String.class),
    @Info(name = "userLoginId", description = "user Login Id", type = Long.class),
    @Info(name = "tenantLoginId", description = "tenant Login Id", type = Long.class),
    @Info(name = "roleLoginId", description = "roleLoginId", type = Long.class),
})
@InfoOut(value = {
    @Info(name = "customerWithSaldoPiutangList", description = "customerWithSaldoPiutangList (customerId, customerCode, customerName, totalSaldoPiutang, totalSaldoPiutangJatuhTempo, totalSaldoPiutangBelumJatuhTempo)", type = List.class)
})
//@formatter:on
public class GetCustomerListWithSaldoPiutangBySalesman extends AbstractBusinessFunction implements BusinessFunction {
    private static final Logger log = LoggerFactory.getLogger(GetCustomerListWithSaldoPiutangBySalesman.class);

    @Autowired
    OrderDao orderDao;

    @Override
    public String getDescription() {
        return "Get Customer List With Saldo Piutang By Salesman";
    }

    @SuppressWarnings("unchecked")
    @Override
    public Dto execute(Dto inputDto) throws Exception {
        log.info("Input BF {} -. {} ", inputDto);

        ValidationUtil.valDtoContainsKey(inputDto, "apiKey");
        ValidationUtil.valDtoContainsKey(inputDto, "salesmanId");
        ValidationUtil.valDtoContainsKey(inputDto, "datetime");
        ValidationUtil.valDtoContainsKey(inputDto, "userLoginId");
        ValidationUtil.valDtoContainsKey(inputDto, "tenantLoginId");
        ValidationUtil.valDtoContainsKey(inputDto, "roleLoginId");

        Long salesmanId = inputDto.getLong("salesmanId");
        Long userLoginId = inputDto.getLong("userLoginId");
        Long tenantLoginId = inputDto.getLong("tenantLoginId");
        Long roleLoginId = inputDto.getLong("roleLoginId");
        String datetime = inputDto.getString("datetime");
        String yearMonthDate = datetime.substring(0,8);

        Dto outputDto = new Dto();
        List<Object[]> result = null;

        QueryBuilder builder = new QueryBuilder();
        builder.add("WITH data_piutang_jatuh_tempo AS (")
        .add(" SELECT A.partner_id,A.tenant_id,COALESCE(SUM(A.amount-A.payment_amount),0) as amount")
        .add(" 	FROM vw_fi_os_all_invoice_ar A")
        .add(" 	INNER JOIN vw_mapping_salesman_customer B ON A.tenant_id =B.tenant_id")
        .add(" AND A.partner_id = B.salesman_id")
        .add(" WHERE A.tenant_id = :tenantId AND B.salesman_id = :salesmanId")
        .add(" 	AND A.due_date <= :yearMonthDate")
        .add(" 	GROUP BY A.partner_id,A.tenant_id")
        .add(" ),data_piutang_belum_jatuh_tempo AS (")
        .add(" SELECT A.partner_id,A.tenant_id,COALESCE(SUM(A.amount-A.payment_amount),0) as amount")
        .add(" 	FROM vw_fi_os_all_invoice_ar A")
        .add(" 	INNER JOIN vw_mapping_salesman_customer B ON A.tenant_id =B.tenant_id")
        .add(" AND A.partner_id = B.salesman_id")
        .add(" WHERE A.tenant_id = :tenantId")
        .add(" 	AND B.salesman_id = :salesmanId")
        .add(" 	AND A.due_date > :yearMonthDate")
        .add(" 	GROUP BY A.partner_id,A.tenant_id")
        .add(" ) SELECT A.partner_id,f_get_partner_code(A.partner_id) AS partner_code,")
        .add(" 	f_get_partner_name(A.partner_id) AS partner_name,")
        .add(" 	COALESCE(SUM(A.amount+B.amount),0) as seluruh_saldo,")
        .add(" 	COALESCE(SUM(A.amount),0) AS jatuh_tempo, COALESCE(SUM(B.amount),0) AS belum_jatuh_tempo")
        .add(" 	FROM data_piutang_jatuh_tempo A")
        .add(" 	INNER JOIN data_piutang_belum_jatuh_tempo B ON A.partner_id = B.partner_id")
        .add(" 	AND A.tenant_id = B.tenant_id")
        .add(" WHERE A.tenant_id = :tenantId")
        .add(" 	GROUP BY A.partner_id");

        log.info("Query  BF {} --> {} " , this.getClass(), builder.toString());

        Query query = orderDao.createNativeQuery(builder.toString());
        query.setParameter("salesmanId",salesmanId);
        query.setParameter("tenantId",tenantLoginId);
        query.setParameter("yearMonthDate",yearMonthDate);

        result = query.getResultList();

        // jumlah key list = jumlah kolom
        outputDto = new Dto();
        outputDto.putList("customerWithSaldoPiutangList",DtoUtil.createDtoListFromArray(result,
        "customerId", "customerCode", "customerName",
        "totalSaldoPiutang", "totalSaldoPiutangJatuhTempo", "totalSaldoPiutangBelumJatuhTempo"));

        log.debug("Output BF {} -. {} ", this.getClass(), outputDto);

        return outputDto;
    }

}