BF conditional approve SO

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

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import javax.persistence.NoResultException;
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.MasterConstants;
import org.jleaf.erp.master.entity.Partner;
import org.jleaf.erp.master.entity.PartnerType;
import org.jleaf.erp.master.entity.SellPriceProductForSo;
import org.jleaf.erp.sls.SalesConstants;
import org.jleaf.erp.sls.SalesConstantsForSasa;
import org.jleaf.erp.sls.dao.SalesOrderDao;
import org.jleaf.erp.sls.entity.*;
import org.jleaf.util.Calc;
import org.jleaf.util.DateUtil;
import org.jleaf.util.DtoUtil;
import org.jleaf.util.ValidationUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;


/**
 * Find Sales Order Info For appproval purpose
 *
 * @author fredie, 27 Feb 2014
 * @version 1.0
 */

//@ formatter:off
@Service("findSalesOrderInfoForApproval")
@InfoIn(value = {
    @Info(name="sessionId", description="Session Id", type=String.class, required=true),
    @Info(name="userId", description="User Id", type=Long.class, required=true),
    @Info(name="roleId", description="Role Id", type=Long.class, required=true),
    @Info(name="soId", description="Sales Order Id", type=Long.class, required=true),
    @Info(name="datetime", description="Datetime", type=String.class, required=true),
    @Info(name = "arCurrCode", description = "AR curr code", type = String.class)
})
@InfoOut(value = {
    @Info(name = "soId", description = "Sales Order Id", type = Long.class),
    @Info(name = "totalNettAmount", description = "Total nett amount", type = Double.class),
    @Info(name = "totalpurchAmount", description = "Total Purchasing Amount", type = Double.class),
    @Info(name = "totalCurrCode", description = "Curr Code of totalNettAmount", type = String.class),
    @Info(name = "gpPercentage", description = "GP Percentage", type = Double.class),
    @Info(name = "gpAmount", description = "GP Amount", type = Double.class),
    @Info(name = "arAmount", description = "AR Amount", type = Double.class),
    @Info(name = "arCurrCode", description = "AR curr code", type = String.class),
    @Info(name = "currCodeAr", description = "curr code AR", type = String.class),
    @Info(name = "amountAr", description = "Amount AR", type = Double.class),
    @Info(name = "notYetDueArAmount", description = "Not Yet Due Ar Amount", type = Double.class),
    @Info(name = "currentArAmount", description = "Current AR Amount", type = Double.class),
    @Info(name = "overdueArAmount", description = "Overdue AR Amount", type = Double.class),
    @Info(name = "comparePriceString", description = "Compare Price String", type = String.class),
    @Info(name = "flagFaultComparePrice", description = "Flag Fault Compare Price", type = Long.class),
    @Info(name = "flgPriceAfterDiscLowerThanMstrMinSellPrice", description = "Flag Sell Price After Disc Lower Than Master Min Sell Price", type = String.class),
    @Info(name = "flgChequeGiroReject", description = "Flg Cheque/Giro Reject", type = String.class),
    @Info(name = "chequeGiroNo", description = "Cheque/Giro No", type = String.class),
    @Info(name = "amountLimit", description = "Amount Limit Partner", type = Double.class),
    @Info(name = "totalAmountCreditLimitUsage", description = "Total Amount Credit Limit Usage", type = Double.class),
    @Info(name = "amountLimitBalance", description = "Amount Limit Balance", type = Double.class),
    @Info(name = "flgOverDueDate", description = "flg Over Due Date", type = String.class)
})
@ErrorList(errorKeys = {

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

    @Autowired
    SalesOrderDao salesOrderDao;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public String getDescription() {
         return "Find Sales Order Info For appproval purpose";
    }

    @SuppressWarnings("unchecked")
    @Override
    public Dto execute(Dto inputDto) throws Exception {
        ValidationUtil.valDtoContainsKey(inputDto, "soId");
        ValidationUtil.valDtoContainsKey(inputDto, "arCurrCode");
        ValidationUtil.valDtoContainsKey(inputDto, "datetime");
        ValidationUtil.valDatetime(inputDto, "datetime");

        log.info("inputDto: "+inputDto);

        Long soId = inputDto.getLong("soId");
        Long userId = inputDto.getLong("userId");
        Long roleId = inputDto.getLong("roleId");
        String arCurrCode = inputDto.getString("arCurrCode");
        String datetime = inputDto.getString("datetime");
        String date = datetime.substring(0, 8);

        String flgPriceAfterDiscLowerThanMstrMinSellPrice = GeneralConstants.NO;
        String flgOverDueDate = GeneralConstants.NO;
        String flgChequeGiroReject = GeneralConstants.NO;
        String chequeGiroNo = GeneralConstants.EMPTY_VALUE;
        Double totalAmountCreditLimitUsage = 0D;
        Double amountLimit = 0D;
        Double saldoAr = 0D;
        Double saldoTaxAr = 0D;
        Integer digitDecimal = 0;
        Double inProgressSoAmount = 0D;
        Double unAllocatedCbIn = 0D;
        Double amountLimitBalance = 0D;

        Dto outputDto = new Dto();

        // get summary nettItemAmount
        QueryBuilder builderNettAmount = new QueryBuilder();
        builderNettAmount.add(" SELECT SUM(A.nett_item_amount) AS total_nett_amount, A.curr_code, B.partner_id, B.partner_bill_to_id, B.tenant_id, B.ou_id ")
            .add(" FROM " + SalesOrderItem.TABLE_NAME + " A ")
            .add(" INNER JOIN "+ SalesOrder.TABLE_NAME+" B ON A.so_id = B.so_id " )
            .add(" WHERE A.so_id = :soId ")
            .add(" GROUP BY A.curr_code, B.partner_id, B.tenant_id, B.ou_id, B.partner_bill_to_id");

        Query queryNettAmount = salesOrderDao.createNativeQuery(builderNettAmount.toString());
        queryNettAmount.setParameter("soId", soId);

        List<Object[]> resultNettAmount = queryNettAmount.getResultList();
        Double totalNettAmount = 0d;
        String totalCurrCode = GeneralConstants.EMPTY_VALUE;
        Long partnerId  = GeneralConstants.NULL_REF_VALUE_LONG;
        Long partnerBillToId  = GeneralConstants.NULL_REF_VALUE_LONG;
        Long tenantId  = GeneralConstants.NULL_REF_VALUE_LONG;
        Long ouId  = GeneralConstants.NULL_REF_VALUE_LONG;

        if(resultNettAmount!=null && resultNettAmount.size()>0){
            List<Dto> resultNettAmountList = new ArrayList<Dto>();
            resultNettAmountList = DtoUtil.createDtoListFromArray(resultNettAmount, "totalNettAmount", "totalCurrCode", "partnerId", "partnerBillToId", "tenantId", "ouId");

            totalNettAmount = resultNettAmountList.get(0).getDouble("totalNettAmount");
            totalCurrCode = resultNettAmountList.get(0).getString("totalCurrCode");
            partnerId = resultNettAmountList.get(0).getLong("partnerId");
            partnerBillToId = resultNettAmountList.get(0).getLong("partnerBillToId");
            tenantId = resultNettAmountList.get(0).getLong("tenantId");
            ouId = resultNettAmountList.get(0).getLong("ouId");
        }

        totalNettAmount = new Calc(totalNettAmount).doubleValue();

        // get purcha and GP
        QueryBuilder builderGp = new QueryBuilder();
        builderGp
            .add(" WITH summary_purch_per_currency AS (")
            .add("  SELECT SUM(A.price * B.qty_so) AS total_price_purch, A.curr_code AS curr_code_purch, B.curr_code AS curr_code_so ")
            .add("  FROM "+SalesOrderItemPurchasing.TABLE_NAME+" A ")
            .add("  INNER JOIN "+SalesOrderItem.TABLE_NAME+" B ON A.so_item_id = B.so_item_id ")
            .add("  WHERE B.so_id = :soId ")
            .add("  GROUP BY A.curr_code, B.curr_code ")
            .add(" ) ")
            .add(" SELECT COALESCE(SUM(f_get_amount_in_nearest_com_rate(:tenantId, A.total_price_purch, :date, A.curr_code_purch, A.curr_code_so )), 0) AS total_price_as_so_currency ")
            .add(" FROM summary_purch_per_currency A ");

        Double totalpurchAmount = 0d;
        Query queryGp = salesOrderDao.createNativeQuery(builderGp.toString());
        queryGp.setParameter("tenantId", tenantId);
        queryGp.setParameter("date", date);
        queryGp.setParameter("soId", soId);

        Object resultQueryGp = queryGp.getSingleResult();
        if(resultQueryGp!=null ){
            totalpurchAmount = Double.valueOf(resultQueryGp.toString());
        }


        // get external commission
        QueryBuilder builderExtComm = new QueryBuilder();
        builderExtComm
            .add(" SELECT COALESCE(SUM(B.commission_amount * f_commercial_rate(A.tenant_id, A.doc_date, B.commission_curr_code, A.curr_code)),0) AS ext_comm_amount FROM ")
            .add(SalesOrder.TABLE_NAME)
            .add(" A ")
            .add(" INNER JOIN ")
            .add(SalesOrderExternalCommission.TABLE_NAME)
            .add(" B ON A.so_id=B.so_id AND A.tenant_id = B.tenant_id ")
            .add(" WHERE A.so_id = :soId AND A.tenant_id = :tenantId ");

        Double extCommAmount = 0d;
        Query queryExtComm = salesOrderDao.createNativeQuery(builderExtComm.toString());
        queryExtComm.setParameter("soId", soId);
        queryExtComm.setParameter("tenantId", tenantId);

        Object resultExtComm = queryExtComm.getSingleResult();
        if(resultExtComm!=null ){
            extCommAmount = Double.valueOf(resultExtComm.toString());
        }

        Double gpAmount = new Calc(totalNettAmount).subtract(extCommAmount).subtract(totalpurchAmount).doubleValue();
        Double gpPercentage = 0d;

        /**
         * Modify By Henik , Nov 19 , 2015
         * -> modify untuk perhitungan gpPercentage yang sebelum nya gpPercentage = (selisih harga jual - harga ref beli) / harga beli
         * sekarang di ubah gpPercentage = (selsih harga jual - harga bref beli) / harga jual
         */
        // lakukan perhitungan GP lebih lanjut, jika dan hanya jika, total amount purch tidak 0 (nol), tuk menghindari divide by zero
        if (totalpurchAmount.compareTo(0d) != 0) {
            gpPercentage = new Calc(gpAmount).multiply(100).divide(totalNettAmount, BigDecimal.ROUND_HALF_UP, 2).doubleValue();
        }

        // get ar amount
        QueryBuilder builderArAmount = new QueryBuilder();
        builderArAmount
            .add(" WITH all_currency_invoice_ar AS ( ")
            .add("      SELECT SUM(A.amount - A.payment_amount) AS ar_amount, A.curr_code ")
            .add("      FROM vw_fi_all_invoice_ar A ")
            .add("      WHERE A.partner_id = :partnerId ")
            .add("          AND A.flg_payment IN (:flgPayments) ")
            .add("      GROUP BY A.curr_code, A.flg_payment ")
            .add(" ), current_os_invoice_ar AS ( ")
            .add("      SELECT SUM( f_get_amount_in_nearest_com_rate(:tenantId, A.ar_amount, :date, A.curr_code, :arCurrCode ) ) AS total_ar_amount ")
            .add("      FROM all_currency_invoice_ar A")
            .add(" ) SELECT COALESCE(A.total_ar_amount, 0) ")
            .add(" FROM current_os_invoice_ar A ");
        Query queryArAmount = salesOrderDao.createNativeQuery(builderArAmount.toString());
        queryArAmount.setParameter("partnerId", partnerId);
        queryArAmount.setParameter("tenantId", tenantId);
        queryArAmount.setParameter("date", date);
        queryArAmount.setParameter("arCurrCode", arCurrCode);
        queryArAmount.setParameter("flgPayments", Arrays.asList(new String[] {SalesConstants.IN_PROGRESS_TRANSACTION, GeneralConstants.NO}));

        Object resultArAmount = queryArAmount.getSingleResult();
        Double arAmount = Double.valueOf(resultArAmount.toString());

        outputDto.put("soId", soId);
        outputDto.put("totalNettAmount", Double.valueOf(new DecimalFormat("#.00").format(totalNettAmount)));
        outputDto.put("totalpurchAmount", totalpurchAmount);
        outputDto.put("totalCurrCode", totalCurrCode);
        outputDto.put("extCommAmount", extCommAmount);
        outputDto.put("gpPercentage", Double.valueOf(new DecimalFormat("#.00").format(gpPercentage)));
        outputDto.put("gpAmount", Double.valueOf(new DecimalFormat("#.00").format(gpAmount)));
        outputDto.put("arAmount", Double.valueOf(new DecimalFormat("#.00").format(arAmount)));
        outputDto.put("arCurrCode", arCurrCode);

        //not yet due dan overdue ar amount
        Connection conn = jdbcTemplate.getDataSource().getConnection();
        conn.setAutoCommit(false);
        PreparedStatement stm = null;

        try {

            String sessionId = inputDto.getString("sessionId");
            String dueDateType = GeneralConstants.EMPTY_VALUE;
            String dueDateFrom = GeneralConstants.START_DATE;
            String dueDateTo = GeneralConstants.END_DATE;
            String dateNow = DateUtil.dateNow();

            stm = conn.prepareStatement("SELECT f_get_summary_aging_ar_list_for_inquiry_progress_ar(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            stm.setString(1, sessionId);
            stm.setLong(2, tenantId);
            stm.setLong(3, ouId);
            stm.setLong(4, userId);
            stm.setLong(5, roleId);
            stm.setLong(6, partnerId);
            stm.setString(7, dueDateType);
            stm.setString(8, dueDateFrom);
            stm.setString(9, dueDateTo);
            stm.setString(10, dateNow);

            ResultSet resultSetFromFunction = stm.executeQuery();
            ResultSet resultSetFromQuery = null;
            if (resultSetFromFunction.next()) {
                resultSetFromQuery = (ResultSet) resultSetFromFunction.getObject(1);
            }

            resultSetFromFunction.close();

            Dto detailDto = new Dto();

            while (resultSetFromQuery != null && resultSetFromQuery.next()) {
                detailDto.put("currCodeAr", resultSetFromQuery.getString(1));
                detailDto.put("amountAr", resultSetFromQuery.getBigDecimal(2).doubleValue());
                detailDto.put("notYetDueArAmount", resultSetFromQuery.getBigDecimal(3).doubleValue());
                detailDto.put("currentArAmount", resultSetFromQuery.getBigDecimal(4).doubleValue());
                detailDto.put("overdueArAmount", resultSetFromQuery.getBigDecimal(5).doubleValue());
            }

            stm.close();
            conn.commit();
            conn.close();

            outputDto.putAll(detailDto);

        } catch (Exception e) {
            if (stm != null)
                stm.close();

            if (conn != null && !conn.isClosed())
                conn.rollback();

            throw e;
        }

        // flag harga jual setelah dipotong diskon < batas bawah harga jual master yg disimpan di item SO
        QueryBuilder builderFlgSellPrice = new QueryBuilder();
        builderFlgSellPrice.add("SELECT 1 FROM ").add(SalesOrderItem.TABLE_NAME).add(" A ")
                .add(" INNER JOIN "+SalesOrderItemCustom.TABLE_NAME+" B ON A.so_item_id = B.so_item_id ")
                .add(" WHERE A.tenant_id = :tenantId ")
                .add(" AND so_id = :soId ")
                .add(" AND (A.tax_price + A.nett_sell_price) < B.master_min_sell_price ");

        Query queryPriceGreaterThanMasterSellPrice = salesOrderDao.createNativeQuery(builderFlgSellPrice.toString());
        queryPriceGreaterThanMasterSellPrice.setParameter("tenantId", tenantId);
        queryPriceGreaterThanMasterSellPrice.setParameter("soId", soId);
        List<Object[]> resultList = queryPriceGreaterThanMasterSellPrice.getResultList();
        if (!resultList.isEmpty()){
            flgPriceAfterDiscLowerThanMstrMinSellPrice = GeneralConstants.YES;
        }
        log.info("flgPriceAfterDiscLowerThanMstrMinSellPrice: "+flgPriceAfterDiscLowerThanMstrMinSellPrice);

        // flg ada cek/giro tolak, cuma sebagai history
        QueryBuilder builderChequeGiroReject = new QueryBuilder();
        builderChequeGiroReject.add("select B.cheque_giro_no ")
                .add(" FROM "+SalesOrder.TABLE_NAME+ " A ")
                .add(" INNER JOIN "+ SalesConstantsForSasa.TABLE_CHEQUE_GIRO_REALIZATION+" B ON A.tenant_id = B.tenant_id AND A.partner_bill_to_id = B.partner_id ")
                .add(" INNER JOIN "+SalesConstantsForSasa.TABLE_IN_OUT_CASHBANK+" C ON B.in_out_cashbank_id = C.in_out_cashbank_id ")
                .add(" WHERE B.realization_status = :realizationStatus ")
                .add(" AND C.status_doc = :statusDoc ")
                .add(" AND A.so_id = :soId ")
                .add(" ORDER BY B.realization_date ")
                .add(" DESC ")
                .add(" LIMIT 1 ");

        Query queryChequeGiroReject = salesOrderDao.createNativeQuery(builderChequeGiroReject.toString());
        queryChequeGiroReject.setParameter("soId", soId);
        queryChequeGiroReject.setParameter("statusDoc", SalesConstants.RELEASED_TRANSACTION);
        queryChequeGiroReject.setParameter("realizationStatus", SalesConstantsForSasa.CHEQUE_GIRO_STATUS_REJECT);
//
        Object resultCGList = null;
        try{
            resultCGList = queryChequeGiroReject.getSingleResult();
        } catch (NoResultException e) {}

        if(resultCGList!=null){
            flgChequeGiroReject = GeneralConstants.YES;
            chequeGiroNo = resultCGList.toString();
        }

        log.info("flgChequeGiroReject: "+flgChequeGiroReject);
        log.info("chequeGiroNo: "+chequeGiroNo);

        // flag lewat dari plafon toko
        //Get Plafon (Amount Limit) Partner
        QueryBuilder builderPlafonCustomer = new QueryBuilder();
        builderPlafonCustomer.add("SELECT B.amount_limit ")
                .add(" FROM "+SalesOrder.TABLE_NAME+" A ")
                .add(" INNER JOIN "+ PartnerType.TABLE_NAME+ " B ON A.tenant_id = B.tenant_id AND A.partner_bill_to_id = B.partner_id ")
                .add(" WHERE B.group_partner = :partnerType ")
                .add(" AND A.so_id = :soId");
        Query queryPlafonCustomer = salesOrderDao.createNativeQuery(builderPlafonCustomer.toString());
        queryPlafonCustomer.setParameter("soId", soId);
        queryPlafonCustomer.setParameter("partnerType", SalesConstants.GROUP_PARTNER_CUSTOMER);

        Object resultPlafonCustomer = null;
        try {
            resultPlafonCustomer = queryPlafonCustomer.getSingleResult();
        } catch (NoResultException e) {}

        if(resultPlafonCustomer!=null ){
            amountLimit = Double.valueOf(resultPlafonCustomer.toString());
        }
        log.info("amountLimitPlafon: "+amountLimit);

        //Get Piutang
        /*SaldoAr*/
        QueryBuilder builderSaldoAr = new QueryBuilder();
        builderSaldoAr.add("SELECT COALESCE(SUM(A.amount-A.payment_amount), 0) ")
                .add(" FROM "+SalesConstantsForSasa.TABLE_INVOICE_AR_BALANCE+" A ")
                .add(" WHERE A.flg_payment <> :flagYes ")
                .add(" AND A.partner_id = :partnerId ")
                .add(" AND A.tenant_id = :tenantId ")
                .add("");

        Query querySaldoAr = salesOrderDao.createNativeQuery(builderSaldoAr.toString());
        querySaldoAr.setParameter("flagYes", GeneralConstants.YES);
        querySaldoAr.setParameter("partnerId", partnerBillToId);
        querySaldoAr.setParameter("tenantId", tenantId);

        Object resultSaldo = null;
        try {
            resultSaldo = querySaldoAr.getSingleResult();
        } catch (NoResultException e) {}

        if(resultSaldo!=null ){
            saldoAr = Double.valueOf(resultSaldo.toString());
        }
        log.info("saldoAr: "+saldoAr);

        /*SaldoTaxAr*/
        QueryBuilder builderSaldoTaxAr = new QueryBuilder();
        builderSaldoTaxAr.add("SELECT COALESCE(SUM(A.tax_amount-A.payment_amount), 0) ")
                .add(" FROM "+SalesConstantsForSasa.TABLE_INVOICE_TAX_AR_BALANCE+" A ")
                .add(" INNER JOIN "+SalesConstantsForSasa.TABLE_INVOICE_AR_BALANCE+" B ON A.invoice_ar_balance_id = B.invoice_ar_balance_id")
                .add(" WHERE A.flg_payment <> :flagYes ")
                .add(" AND A.partner_id = :partnerId ")
                .add(" AND A.tenant_id = :tenantId ")
                .add("");

        Query querySaldoTaxAr = salesOrderDao.createNativeQuery(builderSaldoTaxAr.toString());
        querySaldoTaxAr.setParameter("flagYes", GeneralConstants.YES);
        querySaldoTaxAr.setParameter("partnerId", partnerBillToId);
        querySaldoTaxAr.setParameter("tenantId", tenantId);

        resultSaldo = null;
        try {
            resultSaldo = querySaldoTaxAr.getSingleResult();
        } catch (NoResultException e) {}

        if(resultSaldo!=null ){
            saldoTaxAr = Double.valueOf(resultSaldo.toString());
        }
        log.info("saldoTaxAr: "+saldoTaxAr);

        //Get SO Belum Jadi DO
        QueryBuilder builderSONotYetDO = new QueryBuilder();
        builderSONotYetDO
                .add(" WITH count_amount AS ( ")
                    .add(" WITH qty_progress AS ( ")
                        .add(" SELECT so_item_id, (qty_so - qty_dlv - qty_cancel + qty_add) AS qty ")
                        .add(" FROM ").add(SalesOrderBalanceItem.TABLE_NAME).add(" A ")
                        .add(" WHERE A.status_item NOT IN (:voidDoc, :rejectedDoc) ")
                        .add(" AND (qty_so - qty_dlv - qty_cancel + qty_add) > 0 ) ")
                    .add("SELECT COALESCE(SUM((B.nett_sell_price + B.tax_price)*C.qty), 0) AS amount_total ")
                    .add(" FROM "+SalesOrder.TABLE_NAME+" A ")
                    .add(" INNER JOIN "+SalesOrderItem.TABLE_NAME+" B ON A.so_id = B.so_id ")
                    .add(" INNER JOIN qty_progress C ON B.so_item_id = C.so_item_id ")
                    .add(" WHERE A.tenant_id = :tenantId ")
                    .add(" AND A.status_doc NOT IN (:voidDoc, :rejectedDoc)")
                    .add(" AND A.workflow_status IN (:statusApproved)")
                    .add(" AND NOT EXISTS (SELECT 1 "+
                        "FROM "+DeliveryOrder.TABLE_NAME+" Y "+
                        "WHERE A.so_id = Y.ref_id "+
                        "AND Y.status_doc = :statusDocRelease) ")
                    .add(" AND A.partner_bill_to_id = :partnerId ")

                    .add(" UNION ALL ")

                    .add("SELECT COALESCE(SUM(B.nett_item_amount+B.tax_amount), 0) AS amount_total ")
                    .add(" FROM "+SalesOrder.TABLE_NAME+" A ")
                    .add(" INNER JOIN "+SalesOrderItem.TABLE_NAME+" B ON A.so_id = B.so_id ")
                    .add(" WHERE A.tenant_id = :tenantId ")
                    .add(" AND A.status_doc IN (:progressDoc, :draftDoc)")
                    .add(" AND NOT EXISTS (SELECT 1 "+
                            "FROM "+DeliveryOrder.TABLE_NAME+" Y "+
                            "WHERE A.so_id = Y.ref_id "+
                            "AND Y.status_doc = :statusDocRelease) ")
                    .add(" AND A.partner_bill_to_id = :partnerId ")
                .add(" ) SELECT SUM(amount_total) FROM count_amount ")
                    .add("");

        Query querySONotYetDO = salesOrderDao.createNativeQuery(builderSONotYetDO.toString());
        querySONotYetDO.setParameter("partnerId", partnerBillToId);
        querySONotYetDO.setParameter("tenantId", tenantId);

        querySONotYetDO.setParameter("statusDocRelease", MasterConstants.RELEASED);
        querySONotYetDO.setParameter("voidDoc", SalesConstants.VOID_TRANSACTION);
        querySONotYetDO.setParameter("rejectedDoc", SalesConstants.CANCELED_TRANSACTION);
        querySONotYetDO.setParameter("progressDoc", SalesConstants.IN_PROGRESS_TRANSACTION);
        querySONotYetDO.setParameter("draftDoc", SalesConstants.DRAFT_TRANSACTION);
        querySONotYetDO.setParameter("statusApproved", SalesConstants.WORKFLOW_STATUS_APPROVED);


        resultSaldo = null;
        try {
            resultSaldo = querySONotYetDO.getSingleResult();
        } catch (NoResultException e) {}

        if(resultSaldo!=null ){
            inProgressSoAmount = Double.valueOf(resultSaldo.toString());
        }
        log.info("inProgressSoAmount: "+inProgressSoAmount);

        //Get un-allocated in_cashbank from customer
        QueryBuilder builderUnAllocatedCbIn = new QueryBuilder();
        builderUnAllocatedCbIn.add("WITH data_cb_in_with_conv_with_data_cg_receipt AS (")
                .add(" SELECT COALESCE(SUM(A.amount), 0) AS amount " +
                        "FROM fi_receipt_ar_balance A " +
                        " WHERE A.tenant_id = :tenantId " +
                        //" AND A.ou_id = :ouId " +
                        " AND A.flg_alloc = :flgNo " +
                        " AND A.doc_type_id IN (:docCbInPartnerReceive, :docConvCbInOtherToPartner) " +
                        " AND A.partner_id = :partnerId")
                .add(" UNION ALL ")
                .add(" SELECT COALESCE(SUM(A.amount), 0) AS amount " +
                        " FROM fi_receipt_ar_balance A " +
                        " INNER JOIN cb_cheque_giro_balance B ON A.ref_item_id = B.in_out_cheque_giro_payment_id " +
                        " AND B.flg_realization = :flgYes " +
                        " WHERE A.tenant_id = :tenantId " +
                        //" AND A.ou_id = :ouId " +
                        " AND A.flg_alloc = :flgNo " +
                        " AND A.doc_type_id = :docTypeIdCGReceipt " +
                        " AND A.partner_id = :partnerId ")
                .add(" ) ")
                .add(" SELECT SUM(amount) ")
                .add(" FROM data_cb_in_with_conv_with_data_cg_receipt ")
                .add("");

        Query queryUnAllocatedCbIn = salesOrderDao.createNativeQuery(builderUnAllocatedCbIn.toString());
        queryUnAllocatedCbIn.setParameter("partnerId", partnerBillToId);
        queryUnAllocatedCbIn.setParameter("tenantId", tenantId);
        //queryUnAllocatedCbIn.setParameter("ouId", ouId);
        queryUnAllocatedCbIn.setParameter("flgNo", GeneralConstants.NO);
        queryUnAllocatedCbIn.setParameter("flgYes", GeneralConstants.YES);
        queryUnAllocatedCbIn.setParameter("docCbInPartnerReceive", SalesConstantsForSasa.DOC_CB_IN_PARTNER_RECEIVE);
        queryUnAllocatedCbIn.setParameter("docConvCbInOtherToPartner", SalesConstantsForSasa.DOC_CONV_CB_IN_OTHER_TO_PARTNER);
        queryUnAllocatedCbIn.setParameter("docTypeIdCGReceipt", SalesConstantsForSasa.DOC_TYPE_ID_CG_RECEIPT);

        resultSaldo = null;
        try {
            resultSaldo = queryUnAllocatedCbIn.getSingleResult();
        } catch (NoResultException e) {}

        if(resultSaldo!=null ){
            unAllocatedCbIn = Double.valueOf(resultSaldo.toString());
        }
        log.info("unAllocatedCbIn: "+unAllocatedCbIn);

        /* Count Sisa Plafon*/
        totalAmountCreditLimitUsage = (saldoAr + saldoTaxAr) + inProgressSoAmount - unAllocatedCbIn;
        amountLimitBalance = amountLimit - totalAmountCreditLimitUsage;
        log.info("Total Credit Limit Usage: "+totalAmountCreditLimitUsage);
        log.info("Sisa Plafon: "+amountLimitBalance);


        // cek apakah ada piutang overdue
        QueryBuilder builderStatusOverDuePartner = new QueryBuilder();
        builderStatusOverDuePartner.add(" SELECT f_get_status_over_due_partner(:tenantId, :ouId, :userId, :roleId, :datetime, :partnerId) AS status_over_due ");

        Query queryStatusOverDuePartner = salesOrderDao.createNativeQuery(builderStatusOverDuePartner.toString());
        queryStatusOverDuePartner.setParameter("tenantId", tenantId);
        queryStatusOverDuePartner.setParameter("ouId", ouId);
        queryStatusOverDuePartner.setParameter("userId", userId);
        queryStatusOverDuePartner.setParameter("roleId", roleId);
        queryStatusOverDuePartner.setParameter("datetime", DateUtil.dateTimeNow());
        queryStatusOverDuePartner.setParameter("partnerId", partnerBillToId);

        Object statusOverDuePartner = null;
        try {
            statusOverDuePartner = queryStatusOverDuePartner.getSingleResult();
        } catch (NoResultException e) {}
        if (statusOverDuePartner!= null) {
            flgOverDueDate = statusOverDuePartner.toString();
        }
        log.info("flgOverDueDate: "+flgOverDueDate);


        // compare gross price product from m_sell_price_product_for_so
        //select, trus loop, trus tampung di stringbuilder product_code dan gross_price kedua table
        int preferredFractionDigits = 2;
        NumberFormat formatter = NumberFormat.getNumberInstance();
        formatter.setMinimumFractionDigits(preferredFractionDigits);

        QueryBuilder builderComparePrice = new QueryBuilder();
        builderComparePrice.add(" SELECT A.product_id, f_get_product_code(A.product_id) AS product_code, B.curr_code AS curr_code_for_so, B.gross_sell_price AS sell_price_for_so, A.gross_sell_price AS so_sell_price, A.curr_code AS so_curr_code FROM ")
        .add(SalesOrderItem.TABLE_NAME)
        .add(" A INNER JOIN ").add(SellPriceProductForSo.TABLE_NAME).add(" B ON A.product_id = B.product_id ")
        .add(" INNER JOIN ").add(SalesOrder.TABLE_NAME).add(" C ON A.so_id=C.so_id AND B.tenant_id = C.tenant_id AND B.ou_id = C.ou_id ")
        .add(" INNER JOIN ").add(Partner.TABLE_NAME).add(" D ON C.partner_id = D.partner_id ")
        .add(" WHERE A.gross_sell_price < B.gross_sell_price ")
        .add(" AND B.curr_code = A.curr_code ")
        .add(" AND C.doc_date BETWEEN B.date_from AND B.date_to ")
        .add(" AND B.price_level = D.price_level ")
        .add(" AND C.so_id = :soId ");

        Query queryComparePrice = salesOrderDao.createNativeQuery(builderComparePrice.toString());
        queryComparePrice.setParameter("soId", soId);

        List<Object[]> resultComparePrice = queryComparePrice.getResultList();
        StringBuilder comparePriceString = new StringBuilder();
        Long productId  = GeneralConstants.NULL_REF_VALUE_LONG;
        String productCode = GeneralConstants.EMPTY_VALUE;
        String currCodeForSo = GeneralConstants.EMPTY_VALUE;
        Double sellPriceForSo  = 0d;
        Double soSellPrice  = 0d;
        String soCurrCode = GeneralConstants.EMPTY_VALUE;
        Long flagFaultComparePrice = 0L;

        if(resultComparePrice!=null && resultComparePrice.size()>0){
            List<Dto> resultComparePriceList = new ArrayList<Dto>();
            resultComparePriceList = DtoUtil.createDtoListFromArray(resultComparePrice, "productId", "productCode", "currCodeForSo", "sellPriceForSo", "soSellPrice", "soCurrCode");

            for (Dto dto : resultComparePriceList) {
                productId = dto.getLong("productId");
                productCode = dto.getString("productCode");
                currCodeForSo = dto.getString("currCodeForSo");
                sellPriceForSo = dto.getDouble("sellPriceForSo");
                soSellPrice = dto.getDouble("soSellPrice");
                soCurrCode = dto.getString("soCurrCode");

                flagFaultComparePrice = 1L;
                comparePriceString.append("Product with code ").append(productCode).append(" SO Price(").append(soCurrCode).append(" ").append(formatter.format(Double.valueOf(soSellPrice))).append(") is below default product sell price(").append(currCodeForSo).append(" ").append(formatter.format(Double.valueOf(sellPriceForSo))).append(") \n");
            }
        }
        outputDto.put("comparePriceString", comparePriceString.toString());
        outputDto.put("flagFaultComparePrice", flagFaultComparePrice);
        outputDto.put("flgPriceAfterDiscLowerThanMstrMinSellPrice", flgPriceAfterDiscLowerThanMstrMinSellPrice);
        outputDto.put("flgChequeGiroReject", flgChequeGiroReject);
        outputDto.put("chequeGiroNo", chequeGiroNo);
        outputDto.put("amountLimit", amountLimit);
        outputDto.put("totalAmountCreditLimitUsage", totalAmountCreditLimitUsage);
        outputDto.put("amountLimitBalance", amountLimitBalance);
        outputDto.put("flgOverDueDate", flgOverDueDate);

        log.info("outputDto Result: "+outputDto);

        return outputDto;
     }
 }