GetNotYetPaidDueInvoiceAmountForSoApproval

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 = "soId", description = "SO id", type = Long.class)
})
@InfoOut(value = {
        @Info(name = "amount", description = "amount of not yet paid due invoice", type = Long.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, "soId");

        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(" WITH current_so AS ( ")
                .add(" SELECT tenant_id, ou_id, partner_id FROM ")
                .add(SalesOrder.TABLE_NAME)
                .add(" A WHERE A.so_id = :soId ) ")
                .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(" LEFT OUTER JOIN ")
                .add(SalesConstantsForXcom.INVOICE_TAX_AR_BALANCE_TABLE_NAME)
                .add(" C ON C.invoice_ar_balance_id = A.invoice_ar_balance_id ")
                .add(" INNER JOIN current_so D ON A.tenant_id = D.tenant_id AND A.ou_id = D.ou_id AND A.partner_id = D.partner_id ")
                .add(" WHERE 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("soId", inputDto.getLong("soId"));
        q.setParameter("startDate", GeneralConstants.START_DATE);
        q.setParameter("endDate", dueDate);
        q.setParameter("NO", GeneralConstants.NO);

        Object amount = q.getSingleResult();

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