IsInvoiceAlreadyPaidByMappingCashBankInOthersToSo

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

import org.jleaf.core.AbstractBusinessFunction;
import org.jleaf.core.BusinessFunction;
import org.jleaf.core.Dto;
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.cb.entity.MappingCashBankInSoBalanceItem;
import org.jleaf.erp.sls.SalesConstants;
import org.jleaf.erp.sls.dao.SalesInvoiceDao;
import org.jleaf.erp.sls.dao.SalesInvoiceItemDao;
import org.jleaf.erp.sls.entity.*;
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;

import javax.persistence.Query;
import java.util.List;


/**
 * Is InvoiceAdvance exists by id
 * @author Fredi, Sep 19, 2014
 * @version 1.0
 */

//@ formatter:off
@Service
@InfoIn(value = {
    @Info(name = "invoiceId", description = "invoiceId", type = Long.class, required = true)
})
@InfoOut(value = {
    @Info(name = "exists", description = "exists", type = Boolean.class, required = true),

})
@ErrorList(errorKeys = {

})
//@ formatter:on
public class IsInvoiceAlreadyPaidByMappingCashBankInOthersToSo extends AbstractBusinessFunction implements BusinessFunction {
    private static Logger log = LoggerFactory.getLogger(IsInvoiceAlreadyPaidByMappingCashBankInOthersToSo.class);

    @Autowired
    private SalesInvoiceDao salesInvoiceDao;

    @Autowired
    private SalesInvoiceItemDao salesInvoiceItemDao;

    @Override
    public Dto execute(Dto inputDto) throws Exception {
        ValidationUtil.valDtoContainsKey(inputDto, "invoiceId");

        Dto outputDto = new Dto();

        List<Object[]> result = null;

        // Ambil Total Payment
        QueryBuilder builderTotalPayment = new QueryBuilder();
        builderTotalPayment.add(" SELECT CASE WHEN COALESCE(C.amount, 0) = 0 THEN 0 ELSE A.gross_amount END AS payment" )
                            .add(" FROM ") .add(SalesInvoice.TABLE_NAME) .add(" A ")
                            .add(" INNER JOIN ") .add(SalesOrder.TABLE_NAME) .add(" B ON A.ref_id = B.so_id ")
                            .add(" LEFT JOIN ") .add(MappingCashBankInSoBalanceItem.TABLE_NAME) .add("C ON B.so_id = C.so_id AND B.doc_no = C.doc_no")
                            .add(" WHERE A.invoice_id = pInvoiceId ");

        Query queryTotalPayment = salesInvoiceDao.createNativeQuery(builderTotalPayment.toString());
        queryTotalPayment.setParameter("invoiceId", inputDto.getLong("invoiceId"));

        Double totalPayment = (Double) queryTotalPayment.getSingleResult();

        // Ambil Total Tax
        QueryBuilder builderTotalTax = new QueryBuilder();
        builderTotalTax.add(" SELECT SUM(L.tax_amount) AS total_amount " )
                .add(" FROM ") .add(SalesInvoiceItem.TABLE_NAME) .add(" Z ")
                .add(" INNER JOIN ") .add(SalesInvoice.TABLE_NAME) .add(" A ON Z.invoice_id = A.invoice_id ")
                .add(" INNER JOIN ") .add(SalesOrder.TABLE_NAME) .add(" B ON A.ref_id = B.so_id AND A.ref_doc_type_id = B.doc_type_id ")
                .add(" INNER JOIN ") .add(DeliveryOrder.TABLE_NAME) .add(" C ON B.so_id = C.ref_id ")
                .add(" INNER JOIN ") .add(SalesOrderBalanceInvoice.TABLE_NAME) .add(" F ON A.tenant_id = F.tenant_id AND F.ou_id = A.ou_id AND F.ref_doc_type_id = :DoDocTypeId ")
                .add("              AND F.ref_id = C.do_id AND F.so_id = C.ref_id AND F.ref_item_id = Z.ref_item_id ")
                .add("              AND F.do_receipt_item_id = Z.do_receipt_item_id ")
                .add(" INNER JOIN ") .add(SalesOrderBalanceInvoiceTax.TABLE_NAME) .add(" L ON L.tenant_id = F.tenant_id ")
                .add("              AND L.ou_id = F.ou_id AND L.partner_id = F.partner_id AND L.ref_doc_type_id = F.ref_doc_type_id ")
                .add("              AND L.ref_id = F.ref_id AND L.ref_item_id = F.ref_item_id AND L.do_receipt_item_id = F.do_receipt_item_id ")
                .add(" WHERE A.invoice_id = :invoiceId ");

        Query queryTotalTax = salesInvoiceItemDao.createNativeQuery(builderTotalTax.toString());
        queryTotalTax.setParameter("invoiceId", inputDto.getLong("invoiceId"));
        queryTotalTax.setParameter("DoDocTypeId", SalesConstants.DOCUMENT_DELIVERY_ORDER);

        Double totalTax = (Double) queryTotalTax.getSingleResult();

        // Ambil Total Amount
        QueryBuilder builderTotalAmount = new QueryBuilder();
        builderTotalAmount.add(" SELECT SUM(L.tax_amount) AS total_amount " )
                .add(" FROM ") .add(SalesInvoiceItem.TABLE_NAME) .add(" Z ")
                .add(" INNER JOIN ") .add(SalesInvoice.TABLE_NAME) .add(" A ON Z.invoice_id = A.invoice_id ")
                .add(" INNER JOIN ") .add(SalesOrder.TABLE_NAME) .add(" B ON A.ref_id = B.so_id AND A.ref_doc_type_id = B.doc_type_id ")
                .add(" INNER JOIN ") .add(DeliveryOrder.TABLE_NAME) .add(" C ON B.so_id = C.ref_id ")
                .add(" INNER JOIN ") .add(SalesOrderBalanceInvoice.TABLE_NAME) .add(" F ON A.tenant_id = F.tenant_id AND F.ou_id = A.ou_id AND F.ref_doc_type_id = :DoDocTypeId ")
                .add("              AND F.ref_id = C.do_id AND F.so_id = C.ref_id AND F.ref_item_id = Z.ref_item_id ")
                .add("              AND F.do_receipt_item_id = Z.do_receipt_item_id ")
                .add(" WHERE A.invoice_id = :invoiceId ");

        Query queryTotalAmount = salesInvoiceItemDao.createNativeQuery(builderTotalAmount.toString());
        queryTotalAmount.setParameter("invoiceId", inputDto.getLong("invoiceId"));
        queryTotalAmount.setParameter("DoDocTypeId", SalesConstants.DOCUMENT_DELIVERY_ORDER);

        Double totalAmount = (Double) queryTotalAmount.getSingleResult();

        Double totalDue = totalAmount + totalTax - totalPayment;

        boolean isExists = false;
        if (totalDue == 0 && totalDue != null) {
            isExists = true;
        }

        outputDto.put("exists", isExists);
        log.debug("outputDtoEmailTemplate >> "+outputDto);
        log.info("outputDtoEmailTemplate >> "+outputDto);

        return outputDto;
    }

    @Override
    public String getDescription() {
        return "Is Invoice Already Paid By Mapping CashBank In Others To So";
    }

}