GetInvoiceArBalanceListAdvance

package org.jleaf.erp.fin.bo.paymentorderar;

import java.util.List;

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.CriteriaHelper;
import org.jleaf.core.dao.QueryBuilder;
import org.jleaf.erp.fin.FinanceConstants;
import org.jleaf.erp.fin.dao.InvoiceArBalanceDao;
import org.jleaf.erp.master.MasterConstants;
import org.jleaf.erp.master.entity.Partner;
import org.jleaf.util.DtoUtil;
import org.jleaf.util.ValidationUtil;
import org.jleaf.workflow.core.jpa.entity.MasterDocument;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * JLEAFFIN-107
 * Get invoice ar balance by advance parameter
 *
 * @author ivan, Sep 14, 2018
 * @since 1.0.0
 * @version 1.0.0
 */
//@formatter:off
@Service
@InfoIn(value = {
    @Info(name = "tenantId", description = "Tenant id", type = Long.class),
    @Info(name = "datetime", description = "Datetime", type = String.class),
    @Info(name = "ouId", description = "OU id", type = Long.class),
    @Info(name = "docTypeId", description = "Doc type id", type = Long.class),
    @Info(name = "customerId", description = "Cutomer id", type = String.class),
    @Info(name = "customerCodeName", description = "Customer code or name", type = String.class),
    @Info(name = "currCode", description = "Currency code", type = String.class),
    @Info(name = "dueDate", description = "Due date", type = String.class),
    @Info(name = "docDateFrom", description = "Doc date from", type = String.class),
    @Info(name = "docDateTo", description = "Doc date to", type = String.class),
    @Info(name = "docNo", description = "Doc No", type = String.class),
    @Info(name = "remark", description = "Remark", type = String.class)
})
@InfoOut(value = {
    @Info(name = "invoiceArBalanceList", description = "list of invoice ar balance(uniqueKey, invoiceArBalanceId, ouId, docTypeId, docDesc, invoiceArId, docNo, docDate, partnerId, parnerCode, partnerName, dueDate, invoiceNo, invoiceDate, curCode, amount, remark, paymentAmount, flagPayment, remainingAmount, version)", type = List.class)
})
@ErrorList(errorKeys ={

})
//@formatter:off
public class GetInvoiceArBalanceListAdvance extends AbstractBusinessFunction implements BusinessFunction {
    @Autowired
    private InvoiceArBalanceDao invoiceArBalanceDao;

    @Override
    public String getDescription() {
        return "Get Invoice AR balance List by advance parameter";
    }

    @SuppressWarnings("unchecked")
    @Override
    public Dto execute(Dto inputDto) throws Exception {
//      @Info(name = "tenantId", description = "Tenant id", type = Long.class),
//      @Info(name = "ouId", description = "OU id", type = Long.class),
//      @Info(name = "customerId", description = "Customer id", type = String.class),
//      @Info(name = "currCode", description = "Currency code", type = String.class),
//      @Info(name = "dueDate", description = "Due date", type = String.class),
//      @Info(name = "remark", description = "Remark", type = String.class)

        ValidationUtil.valDtoContainsKey(inputDto, "tenantId");
        ValidationUtil.valDtoContainsKey(inputDto, "ouId");
        ValidationUtil.valDtoContainsKey(inputDto, "customerId");
        ValidationUtil.valBlankOrNull(inputDto, "currCode");
        ValidationUtil.valDtoContainsKey(inputDto, "dueDate");
        ValidationUtil.valDtoContainsKey(inputDto, "remark");

        Long tenantId = inputDto.getLong("tenantId");
        String datetime = inputDto.getString("datetime");

        Long ouId = inputDto.getLong("ouId");
        Long customerId = inputDto.getLong("customerId");
        String currCode = inputDto.getString("currCode");
        String dueDate = inputDto.getString("dueDate");
        String remark = inputDto.getString("remark");
        String customerCodeName = inputDto.getString("customerCodeName");
        String docDateFrom = inputDto.getString("docDateFrom");
        String docDateTo = inputDto.getString("docDateTo");
        //String flgPayment = inputDto.getString("flgPayment");
        String dueDateFrom = GeneralConstants.START_DATE;
        String dueDateTo = GeneralConstants.END_DATE;

        if(docDateFrom.isEmpty()){
            docDateFrom = GeneralConstants.START_DATE;
        }

        if(docDateTo.isEmpty()){
            docDateTo = GeneralConstants.END_DATE;
        }

        if(MasterConstants.DUE_DATE_TYPE_OVERDUE.equals(dueDate)){
            dueDateTo = datetime.substring(0,8);
        } else if(MasterConstants.DUE_DATE_TYPE_CURRENT.equals(dueDate)){
            dueDateFrom = datetime.substring(0,8);
            dueDateTo = dueDateFrom;
        } else if(MasterConstants.DUE_DATE_TYPE_NOT_YET_DUE.equals(dueDate)){
            dueDateFrom = datetime.substring(0,8);
        }

        List<Object[]> result = null;
        /**
         * invoiceArBalanceId, ouId, docTypeId,
         * invoiceArId, docNo, docDate, extDocNo,
         * extDocDate, refDocTypeId, refId,
         * partnerId, parnerCode, partnerName, dueDate,
         * curCode, amount, remark, paymentAmount,
         * flagPayment, version
         */

        StringBuilder filterCustomerCodeName = new StringBuilder();
        filterCustomerCodeName.append(" AND ( ")
        .append(CriteriaHelper.likeExpressionIgnoreCase(customerCodeName, "B.partner_code"))
        .append(" OR ")
        .append(CriteriaHelper.likeExpressionIgnoreCase(customerCodeName, "B.partner_name"))
        .append(" ) ");

        StringBuilder filterRemark = new StringBuilder();
        filterRemark.append(" AND ").append(CriteriaHelper.likeExpressionIgnoreCase(remark, "A.remark"));

        QueryBuilder builder = new QueryBuilder();
        builder.add(" SELECT '' || A.id || A.doc_type_id AS unique_key, A.id, A.ou_id, A.doc_type_id, C.doc_desc ")
                .add(" , A.invoice_ar_id, A.doc_no, A.doc_date ")
                .add(" , A.partner_id,  B.partner_code, B.partner_name, A.due_date ")
                .add(" , A.invoice_no, A.invoice_date ")
                .add(" , A.curr_code, A.amount, A.remark, A.payment_amount ")
                .add(" , A.flg_payment, (A.amount-A.payment_amount) AS remaining_amount, A.version ")
            .add(" FROM vw_fi_os_all_invoice_ar A ")
            .add(" INNER JOIN ").add(Partner.TABLE_NAME).add(" B ON A.partner_id = B.partner_id ")
                .add(" AND A.tenant_id = B.tenant_id ")
            .add(" INNER JOIN ").add(MasterDocument.TABLE_NAME).add(" C ON A.doc_type_id = C.doc_type_id ")
            .add(" WHERE A.tenant_id = :tenantId ")
                .add(" AND A.doc_type_id IN (") .add(FinanceConstants.DOCUMENT_RETURN_PURCH_INVOICE)
                .add(", ") .add(FinanceConstants.DOCUMENT_RETURN_VAT_IN) .add(")")
                .add(" AND A.doc_date BETWEEN :docDateFrom AND :docDateTo ")
                .add(" AND A.due_date BETWEEN :dueDateFrom AND :dueDateTo ")
                .addIfNotEmpty(currCode, " AND A.curr_code = :currCode ")
                .addIfNotEquals(ouId, GeneralConstants.NULL_REF_VALUE_LONG, " AND A.ou_id = :ouId ")
                .addIfNotEquals(customerId, GeneralConstants.NULL_REF_VALUE_LONG, " AND A.partner_id = :customerId ")
                //View yg digunakan sudah filter yg outstanding, jadi tidak perlu filter flag payment lagi
                //.addIfNotEmpty(flgPayment, " AND A.flg_payment = '"+flgPayment+"' ")
                .addIfNotEmpty(customerCodeName, filterCustomerCodeName.toString())
                .addIfNotEmpty(remark, filterRemark.toString())
            .add(" ORDER BY invoice_no, invoice_date, C.doc_desc, A.doc_no, A.doc_date ");

        Query q = invoiceArBalanceDao.createNativeQuery(builder.toString());
        q.setParameter("tenantId", tenantId);
        q.setParameter("docDateFrom", docDateFrom);
        q.setParameter("docDateTo", docDateTo);
        q.setParameter("dueDateTo", dueDateTo);
        q.setParameter("dueDateFrom", dueDateFrom);
        if (!GeneralConstants.EMPTY_VALUE.equals(currCode)) {
            q.setParameter("currCode", currCode);
        }
        if (!GeneralConstants.NULL_REF_VALUE_LONG.equals(ouId)) {
            q.setParameter("ouId", ouId);
        }
        if (!GeneralConstants.NULL_REF_VALUE_LONG.equals(customerId)) {
            q.setParameter("customerId", customerId);
        }

        result = q.getResultList();

        return new Dto().put("invoiceArBalanceList", DtoUtil.createDtoListFromArray(result,
                "uniqueKey", "invoiceArBalanceId", "ouId", "docTypeId", "docDesc", "invoiceArId", "docNo", "docDate",
                "partnerId", "partnerCode", "partnerName", "dueDate", "invoiceNo", "invoiceDate", "currCode", "amount", "remark",
                "paymentAmount", "flagPayment", "remainingAmount", "version"));
    }
}