GetRemainingCreditAmountForSoApproval

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

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

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.ErrorList;
import org.jleaf.core.annotation.Info;
import org.jleaf.core.annotation.InfoIn;
import org.jleaf.core.annotation.InfoOut;
import org.jleaf.core.dao.QueryBuilder;
import org.jleaf.erp.master.entity.PartnerType;
import org.jleaf.erp.sls.SalesConstantsForXcom;
import org.jleaf.erp.sls.dao.SalesOrderDao;
import org.jleaf.erp.sls.entity.SalesOrder;
import org.jleaf.util.Calc;
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 = "remaining credit amount", type = Double.class) })
@ErrorList(errorKeys = {

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

    @Autowired
    SalesOrderDao salesOrderDao;

    public String getDescription() {
        // write description of this business function here
        return "get remaining credit amount";
    }

    @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");

        // @formatter:off
        // create native sql
        QueryBuilder builder = new QueryBuilder();
        builder.add(" WITH os_invoice AS ( ")
                .add(" SELECT COALESCE(SUM(A.amount - A.payment_amount), 0) AS os_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 )")
                .add(" SELECT (A.amount_limit - B.os_amount) FROM ")
                .add(PartnerType.TABLE_NAME)
                .add(" A, os_invoice B ")
                .add(" WHERE A.partner_id = :partnerId ")
                .add(" AND A.group_partner = :CUSTOMER ");
        // @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", GeneralConstants.END_DATE);
        q.setParameter("NO", GeneralConstants.NO);
        q.setParameter("CUSTOMER", SalesConstantsForXcom.GROUP_PARTNER_CUSTOMER);

        Object amount = q.getSingleResult();

        // @formatter:off
        // create native sql
        QueryBuilder builderTax = new QueryBuilder();
        builderTax.add(" WITH os_invoice AS ( ")
                .add(" SELECT COALESCE(SUM(A.gov_tax_amount - A.payment_amount), 0) AS os_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 )")
                .add(" SELECT (A.amount_limit - B.os_amount) FROM ")
                .add(PartnerType.TABLE_NAME)
                .add(" A, os_invoice B ")
                .add(" WHERE A.partner_id = :partnerId ")
                .add(" AND A.group_partner = :CUSTOMER ");
        // @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", GeneralConstants.END_DATE);
        qTax.setParameter("NO", GeneralConstants.NO);
        qTax.setParameter("CUSTOMER", SalesConstantsForXcom.GROUP_PARTNER_CUSTOMER);

        Object taxAmount = qTax.getSingleResult();

        // return query result
        return new Dto().put("amount", new Calc(Double.valueOf(amount.toString())).add(Double.valueOf(taxAmount.toString())).doubleValue());
    }
}