ValNoProductConsignmentItemDuplicate

package org.jleaf.erp.purch.bo.po;

import java.util.List;

import javax.persistence.Query;

import org.jleaf.core.AbstractBusinessFunction;
import org.jleaf.core.BusinessFunction;
import org.jleaf.core.CoreException;
import org.jleaf.core.Dto;
import org.jleaf.core.GeneralConstants;
import org.jleaf.core.annotation.Info;
import org.jleaf.core.annotation.InfoIn;
import org.jleaf.core.dao.QueryBuilder;
import org.jleaf.erp.master.dao.ProductDao;
import org.jleaf.erp.master.entity.Product;
import org.jleaf.erp.purch.PurchasingExceptionConstantsForCland;
import org.jleaf.erp.purch.entity.PurchaseOrder;
import org.jleaf.erp.purch.entity.PurchaseOrderExt;
import org.jleaf.erp.purch.entity.PurchaseOrderItem;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * Memperbaiki pesan exception
 * modified by Adrian
 * May 28, 2018
 */

/**
 * Perlu dilakukan validasi untuk produk konsinyasi dalam saat bersamaan tidak
 * boleh ada produk yang duplikat
 *
 * 1. Cek ke tabel pu_po_item untuk PO yang masih DRAFT, product_id tersebut
 * tidak boleh ada 2. Cek ke tabel pu_po_balance_item_consignment tidak boleh
 * ada product_id itu yang status_item = 'R'
 *
 * @author Ali
 *
 */
// @formatter:off
@Service
@InfoIn(value = {
        @Info(name = "currentPoId", description = "current po ID", type = Long.class),
        @Info(name = "productId", description = "product ID to check", type = Long.class) })
// @formatter:on
public class ValNoProductConsignmentItemDuplicate extends
        AbstractBusinessFunction implements BusinessFunction {

    private static final Logger log = LoggerFactory
            .getLogger(ValNoProductConsignmentItemDuplicate.class);
    @Autowired
    private ProductDao productDao;

    public String getDescription() {
        return "Validation no product consignment item duplicate";
    }

    public Dto execute(Dto inputDto) throws Exception {
        Long currentPoId = inputDto.getLong("currentPoId");
        Long productId = inputDto.getLong("productId");

        log.debug(">>>>>>>>>> log current PO Id >>>>>>>>>>>>>>" + currentPoId);
        log.debug(">>>>>>>>>> log product Id >>>>>>>>>>>>>>" + productId);


        // Cek ke tabel pu_po_item untuk PO yang masih DRAFT, product_id
        // tersebut tidak boleh ada
        QueryBuilder builder = new QueryBuilder();
        builder.add("SELECT A.po_item_id, A.po_id, B.doc_no, C.product_code FROM ");
        builder.add(PurchaseOrderItem.TABLE_NAME).add(" A");
        builder.add(" INNER JOIN ");
        builder.add(PurchaseOrder.TABLE_NAME).add(" B ");
        builder.add(" ON A.po_id=B.po_id ");
        builder.add(" INNER JOIN ");
        builder.add(Product.TABLE_NAME).add(" C ");
        builder.add(" ON C.product_id=A.product_id ");
        builder.add(" INNER JOIN ");
        builder.add(PurchaseOrderExt.TABLE_NAME).add(" D ");
        builder.add(" ON D.po_id = B.po_id ");
        builder.add(" WHERE B.status_doc IN ('D','I') AND C.product_id = "
                + productId);
        builder.addIfNotEmpty(currentPoId, " AND A.po_id <> " + currentPoId);
        builder.add(" AND D.flg_buy_consignment = :YES ");
        builder.add(" AND D.flg_konsinyasi_manual = :NO ");

        Query query = productDao.createNativeQuery(builder.toString());
        query.setParameter("YES", GeneralConstants.YES);
        query.setParameter("NO", GeneralConstants.NO);

        List<Object[]> list = query.getResultList();
        int countDuplicate = list.size();

        log.debug(">>>>>>>>>> isi buildernya >>>>>>>>>>>>>>" + builder.toString());

        if (countDuplicate > 0) {
            String productCode = String.valueOf(list.get(0)[3]);
            String docNo = String.valueOf(list.get(0)[2]);
            throw new CoreException(
                    PurchasingExceptionConstantsForCland.PRODUCT_CURRENTLY_USED_IN_ANOTHER_TRANSACTION,
                    productCode,
                    docNo);
        }

        // Cek ke tabel pu_po_balance_item_consignment tidak boleh ada
        // product_id itu yang status_item <> 'F'
        builder = new QueryBuilder();
        builder.add("SELECT A.po_item_id, A.po_id, B.doc_no, D.product_code FROM ");
        builder.add(PurchaseOrderItem.TABLE_NAME).add(" A");
        builder.add(" INNER JOIN ");
        builder.add(PurchaseOrder.TABLE_NAME).add(" B ");
        builder.add(" ON A.po_id=B.po_id ");
        builder.add(" INNER JOIN ");
        builder.add(" pu_po_balance_item_consignment C");
        builder.add(" ON A.po_item_id=C.po_item_id ");
        builder.add(" INNER JOIN ");
        builder.add(Product.TABLE_NAME).add(" D ");
        builder.add(" ON D.product_id=A.product_id ");
        builder.add(" INNER JOIN ");
        builder.add(PurchaseOrderExt.TABLE_NAME).add(" E ");
        builder.add(" ON E.po_id = B.po_id ");
        builder.add(" WHERE C.status_item <> 'F' AND D.product_id = " + productId);
        builder.add(" AND E.flg_buy_consignment = :YES ");
        builder.add(" AND E.flg_konsinyasi_manual = :NO ");

        query = productDao.createNativeQuery(builder.toString());
        query.setParameter("YES", GeneralConstants.YES);
        query.setParameter("NO", GeneralConstants.NO);

        list = query.getResultList();
        countDuplicate = list.size();

        log.debug(">>>>>>>>>> isi buildernya yang ke 2 >>>>>>>>>>>>>>" + builder.toString());

        if (countDuplicate > 0) {
            String productCode = String.valueOf(list.get(0)[3]);
            String docNo = String.valueOf(list.get(0)[2]);
            throw new CoreException(
                    PurchasingExceptionConstantsForCland.PRODUCT_CURRENTLY_USED_IN_ANOTHER_TRANSACTION,
                    productCode,
                    docNo);
        }

        return null;
    }

}