FindSalesOrderInfoForApprovalForIndocom

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.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.Partner;
import org.jleaf.erp.master.entity.SellPriceProductForSo;
import org.jleaf.erp.sls.SalesConstants;
import org.jleaf.erp.sls.dao.SalesOrderDao;
import org.jleaf.erp.sls.entity.SalesOrder;
import org.jleaf.erp.sls.entity.SalesOrderExternalCommission;
import org.jleaf.erp.sls.entity.SalesOrderItem;
import org.jleaf.erp.sls.entity.SalesOrderItemPurchasing;
import org.jleaf.util.Calc;
import org.jleaf.util.DateUtil;
import org.jleaf.util.DtoUtil;
import org.jleaf.util.ValidationUtil;
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)
})
@ErrorList(errorKeys = {

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

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

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

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

        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 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", "tenantId", "ouId");

            totalNettAmount = resultNettAmountList.get(0).getDouble("totalNettAmount");
            totalCurrCode = resultNettAmountList.get(0).getString("totalCurrCode");
            partnerId = resultNettAmountList.get(0).getLong("partnerId");
            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_int) 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");
            Long userId = inputDto.getLong("userId");
            Long roleId = inputDto.getLong("roleId");
            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;
        }

        // 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);

        return outputDto;
     }
 }