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

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;

import javax.persistence.Query;

import org.jleaf.common.CommonExceptionConstants;
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.ErrorList;
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.entity.Partner;
import org.jleaf.erp.sls.SalesConstants;
import org.jleaf.erp.sls.SalesConstantsForXcom;
import org.jleaf.erp.sls.SalesExceptionConstants;
import org.jleaf.erp.sls.dao.SalesOrderDao;
import org.jleaf.erp.sls.entity.SalesOrder;
import org.jleaf.util.DtoUtil;
import org.jleaf.util.ValidationUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 *
 * @author Adrian
 * Apr 3, 2017
 */

//@formatter:off
@Service
@InfoIn(value = {
        @Info(name = "tenantId", description = "tenant id", type = Long.class),
        @Info(name = "ouId", description = "ou id", type = Long.class),
        @Info(name = "partnerId", description = "partner id", type = Long.class)
})
@InfoOut(value = {
        @Info(name = "amount", description = "amount of not yet paid due invoice", type = Double.class),
        @Info(name = "tax amount", description = "tax amount of not yet paid due invoice", type = Double.class) })
@ErrorList(errorKeys = {

})
//@formatter:on
public class GetNotYetPaidDueInvoiceAmountForSoApproval extends AbstractBusinessFunction implements BusinessFunction {

    @Autowired
    SalesOrderDao salesOrderDao;

    public String getDescription() {
        // write description of this business function here
        return "get amount of not yet paid due invoice for SO approval";
    }

    @SuppressWarnings("unchecked")
    public Dto execute(Dto inputDto) throws Exception {
        // validate inputDto have key in @InfoIn
        ValidationUtil.valDtoContainsKey(inputDto, "tenantId");
        ValidationUtil.valDtoContainsKey(inputDto, "ouId");
        ValidationUtil.valDtoContainsKey(inputDto, "partnerId");

        String dueDate;
        Calendar calendar = Calendar.getInstance();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");

        calendar.add(Calendar.DATE, -1);
        dueDate = dateFormat.format(calendar.getTime());

        // @formatter:off
        // create native sql
        QueryBuilder builder = new QueryBuilder();
        builder.add(" SELECT COALESCE(SUM(A.amount - A.payment_amount), 0) AS due_amount FROM ")
        .add(SalesConstantsForXcom.INVOICE_AR_BALANCE_TABLE_NAME)
        .add(" A LEFT OUTER JOIN ")
        .add(SalesOrder.TABLE_NAME)
        .add(" B ON A.ref_doc_type_id = B.doc_type_id AND A.ref_id = B.so_id ")
        .add(" WHERE A.tenant_id = :tenantId ")
        .add(" AND A.ou_id = :ouId ")
        .add(" AND A.partner_id = :partnerId ")
        .add(" AND A.due_date BETWEEN :startDate AND :endDate ")
        .add(" AND A.flg_payment = :NO ");
        // @formatter:on

        // create native query
        Query q = salesOrderDao.createNativeQuery(builder.toString());

        // set query parameter (if any)
        q.setParameter("tenantId", inputDto.getLong("tenantId"));
        q.setParameter("ouId", inputDto.getLong("ouId"));
        q.setParameter("partnerId", inputDto.getLong("partnerId"));
        q.setParameter("startDate", GeneralConstants.START_DATE);
        q.setParameter("endDate", dueDate);
        q.setParameter("NO", GeneralConstants.NO);

        Object amount = q.getSingleResult();

        // @formatter:off
        // create native sql
        QueryBuilder builderTax = new QueryBuilder();
        builderTax.add(" SELECT COALESCE(SUM(A.gov_tax_amount - A.payment_amount), 0) AS due_amount FROM ")
                .add(SalesConstantsForXcom.INVOICE_TAX_AR_BALANCE_TABLE_NAME)
                .add(" A INNER JOIN ")
                .add(SalesConstantsForXcom.INVOICE_AR_BALANCE_TABLE_NAME)
                .add(" B ON A.invoice_ar_balance_id = B.invoice_ar_balance_id ")
                .add(" WHERE A.tenant_id = :tenantId ")
                .add(" AND A.ou_id = :ouId ")
                .add(" AND A.partner_id = :partnerId ")
                .add(" AND A.due_date BETWEEN :startDate AND :endDate ")
                .add(" AND A.flg_payment = :NO ");
        // @formatter:on

        // create native query
        Query qTax = salesOrderDao.createNativeQuery(builderTax.toString());

        // set query parameter (if any)
        qTax.setParameter("tenantId", inputDto.getLong("tenantId"));
        qTax.setParameter("ouId", inputDto.getLong("ouId"));
        qTax.setParameter("partnerId", inputDto.getLong("partnerId"));
        qTax.setParameter("startDate", GeneralConstants.START_DATE);
        qTax.setParameter("endDate", dueDate);
        qTax.setParameter("NO", GeneralConstants.NO);

        Object taxAmount = qTax.getSingleResult();

        // return query result
        return new Dto().put("amount", amount).put("taxAmount", taxAmount);
    }
}