BT Add DO

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

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.persistence.Query;

import org.jleaf.common.CommonExceptionConstants;
import org.jleaf.core.BusinessFunction;
import org.jleaf.core.BusinessTransaction;
import org.jleaf.core.CoreException;
import org.jleaf.core.DefaultBusinessTransaction;
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.inv.dao.ProductBalanceStockDao;
import org.jleaf.erp.inv.entity.ProductBalanceStock;
import org.jleaf.erp.master.MasterConstants;
import org.jleaf.erp.master.MasterExceptionConstants;
import org.jleaf.erp.master.bo.ComboIdMasterConstants;
import org.jleaf.erp.sls.SalesConstants;
import org.jleaf.erp.sls.SalesExceptionConstants;
import org.jleaf.erp.sls.dao.DeliveryOrderDao;
import org.jleaf.erp.sls.dao.DeliveryOrderItemDao;
import org.jleaf.erp.sls.dao.DeliveryOrderProductDao;
import org.jleaf.erp.sls.dao.SalesOrderBalanceItemDao;
import org.jleaf.erp.sls.entity.DeliveryOrder;
import org.jleaf.erp.sls.entity.DeliveryOrderItem;
import org.jleaf.erp.sls.entity.DeliveryOrderProduct;
import org.jleaf.erp.sls.entity.SalesOrderBalanceItem;
import org.jleaf.util.Calc;
import org.jleaf.util.DtoUtil;
import org.jleaf.util.GsonUtil;
import org.jleaf.util.ValidationUtil;
import org.jleaf.validator.CommonBusinessValidator;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

/**
 *
 * @see http://jleaf.org:8181/browse/SLSSVC-38
 * modified by Fredi, 7 Agustus 2014
 * flgDelivery, partnerShipToId, dan partnerShipAddressId
 * tidak diinput, melainkan diset dari Sales Order terkait (dengan demikian, pada info-in juga perlu dihapus)
 *
 * Modified Fitra 2018 Jan 4,
 * Update balance product stock diganti menggunakan native query
 */
//@formatter:off
@Service
@InfoIn(value = {
        @Info(name = "docNo", description = "Doc No", type = String.class),
        @Info(name = "docDate", description = "Doc Date", type = String.class),
        @Info(name = "ouId", description = "Ou id", type = Long.class),
        @Info(name = "warehouseId", description = "Warehouse id", type = Long.class),
//      @Info(name = "partnerShipToId", description = "Partner Ship to id", type = Long.class),
//      @Info(name = "partnerShipAddressId", description = "Partner Ship address id", type = Long.class),
        @Info(name = "deliveryCode", description = "Delivery code", type = String.class),
        @Info(name = "remark", description = "Remark", type = String.class),
        @Info(name = "refId", description = "refId (So Id)", type = Long.class),
        @Info(name = "itemList", description = "list of do item(lineNo, productId, productName, productStatus, refId, qtyDlvInt)", type = List.class),
        @Info(name = "eta", description = "Eta", type = String.class),
        @Info(name = "etaDay", description = "Eta day", type = String.class),
        @Info(name = "tenantLoginId", description = "Tenant login id", type = Long.class),
        @Info(name = "userLoginId", description = "User login id", type = Long.class),
        @Info(name = "roleLoginId", description = "User login id", type = Long.class),
        @Info(name = "datetime", description = "Datetime", type = String.class)
})
@InfoOut(value = {
        @Info(name = "id", description = "Do id", type = Long.class),
        @Info(name = "docNo", description = "Doc no", type = String.class),
        @Info(name = "docDate", description = "Doc date", type = String.class),
        @Info(name = "ouId", description = "Ou id", type = Long.class),
        @Info(name = "warehouseId", description = "Warehouse Id", type = Long.class),
        @Info(name = "partnerShipToId", description = "Partner Ship to id", type = Long.class),
        @Info(name = "partnerShipAddressId", description = "Partner Ship address id", type = Long.class),
        @Info(name = "deliveryCode", description = "Delivery Code", type = String.class),
        @Info(name = "remark", description = "remark", type = String.class),
        @Info(name = "refId", description = "refId (So id)", type = String.class),
        @Info(name = "version", description = "Version", type = Long.class)
})
@ErrorList(errorKeys = {
        CommonExceptionConstants.TENANT_LOGIN_CANT_USED,
        CommonExceptionConstants.DATA_CANT_USE_DIFF_TENANT,
        MasterExceptionConstants.DATA_NOT_ACTIVE,
        SalesExceptionConstants.OU_IN_SALES_ORDER_MUST_NOT_AN_OUTLET,
        SalesExceptionConstants.USER_ROLE_NOT_AUTHORIZED_IN_OU,
        MasterExceptionConstants.DATA_NOT_ACTIVE,
        MasterExceptionConstants.POLICY_WAREHOUSE_AUTHORIZATION_FAILED,
        SalesExceptionConstants.PARTNER_NOT_AUTHORIZED_AS_CUSTOMER,
        MasterExceptionConstants.PARTNER_ID_NOT_FOUND,
        SalesExceptionConstants.CUSTOMER_NOT_RELATED_AS_CUSTOMER_SHIP_TO,
        MasterExceptionConstants.PARTNER_ADDRESS_ID_NOT_FOUND,
        SalesExceptionConstants.PARTNER_ADDRESS_IS_NOT_ASSOCIATED_TO_PARTNER
})
//@formatter:on
public class AddDeliveryOrder extends DefaultBusinessTransaction implements BusinessTransaction {
    private static Logger log = LoggerFactory.getLogger(AddDeliveryOrder.class);

    @Autowired
    DeliveryOrderDao deliveryOrderDao;

    @Autowired
    DeliveryOrderItemDao deliveryOrderItemDao;

    @Autowired
    DeliveryOrderProductDao deliveryOrderProductDao;

    @Autowired
    SalesOrderBalanceItemDao salesOrderBalanceItemDao;

    @Autowired
    ProductBalanceStockDao productBalanceStockDao;

    @Autowired
    @Qualifier("valTenantLoginCanUse")
    BusinessFunction valTenantLoginCanUse;

    @Autowired
    @Qualifier("findSalesOrderById")
    BusinessFunction findSalesOrderById;

    @Autowired
    @Qualifier("findOUById")
    BusinessFunction findOUById;

    @Autowired
    @Qualifier("valOUAsMainBusinessUnitOrBranch")
    BusinessFunction valOUAsMainBusinessUnitOrBranch;

    @Autowired
    @Qualifier("isOUAsSubBusinessUnit")
    BusinessFunction isOUAsSubBusinessUnit;

    @Autowired
    @Qualifier("isUserRoleExistsByIndex")
    BusinessFunction isUserRoleExistsByIndex;

    @Autowired
    @Qualifier("getPolicyOUListByPolicyId")
    BusinessFunction getPolicyOUListByPolicyId;

    @Autowired
    @Qualifier("findWarehouseById")
    BusinessFunction findWarehouseById;

    @Autowired
    @Qualifier("valAuthorizedPolicyWarehouse")
    BusinessFunction valAuthorizedPolicyWarehouse;

    @Autowired
    @Qualifier("isPartnerTypeExistsByIndex")
    BusinessFunction isPartnerTypeExistsByIndex;

    @Autowired
    @Qualifier("findPartnerById")
    BusinessFunction findPartnerById;

    @Autowired
    @Qualifier("isPartnerRelationExistsByIndex")
    BusinessFunction isPartnerRelationExistsByIndex;

    @Autowired
    @Qualifier("findPartnerAddressById")
    BusinessFunction findPartnerAddressById;

    @Autowired
    @Qualifier("isDeliveryOrderExistsByIndex")
    BusinessFunction isDeliveryOrderExistsByIndex;

    @Autowired
    @Qualifier("findSalesOrderItemById")
    BusinessFunction findSalesOrderItemById;

    @Autowired
    @Qualifier("findSalesOrderBalanceItemById")
    BusinessFunction findSalesOrderBalanceItemById;

    @Autowired
    @Qualifier("isProductHasSerialNumber")
    BusinessFunction isProductHasSerialNumber;

    @Autowired
    @Qualifier("findProductBalanceStockByIndex")
    BusinessFunction findProductBalanceStockByIndex;

    @Autowired
    @Qualifier("findProductBalanceById")
    BusinessFunction findProductBalanceById;

    @Autowired
    @Qualifier("valSufficientProductBalanceStock")
    BusinessFunction valSufficientProductBalanceStock;

    @Autowired
    @Qualifier("valWarehouseExistsInOu")
    BusinessFunction valWarehouseExistsInOu;

    @Autowired
    @Qualifier("valAuthorizedPolicyOU")
    BusinessFunction valAuthorizedPolicyOU;

    @Autowired
    @Qualifier("valComboValueByCode")
    BusinessFunction valComboValueByCode;

    @Autowired
    @Qualifier("isProductStatusExistsByIndex")
    BusinessFunction isProductStatusExistsByIndex;

    @Autowired
    @Qualifier("findProductById")
    BusinessFunction findProductById;

    @Override
    public String getDescription() {
        return "Add delivery order";
    }

    @SuppressWarnings("unchecked")
    @Override
    public Dto prepare(Dto inputDto, Dto originalDto) throws Exception {
        // Validation not empty / null / date / etc
        // docNo
        ValidationUtil.valBlankOrNull(inputDto.getString("docNo"), "Document No");
        // docDate
        ValidationUtil.valBlankOrNull(inputDto.getString("docDate"), "Document Date");
        // ouId
        ValidationUtil.valDtoContainsKey(inputDto, "ouId");
        // warehouseId
        ValidationUtil.valDtoContainsKey(inputDto, "warehouseId");
        // partnerShipToId
//      ValidationUtil.valDtoContainsKey(inputDto, "partnerShipToId");
        // partnerShipAddressId
//      ValidationUtil.valDtoContainsKey(inputDto, "partnerShipAddressId");
        // deliveryCode
        ValidationUtil.valBlankOrNull(inputDto.getString("deliveryCode"), "Delivery code");
        // remark
        ValidationUtil.valDtoContainsKey(inputDto, "remark");
        // refId (maksudnya soId)
        ValidationUtil.valDtoContainsKey(inputDto, "refId");
        ValidationUtil.valDtoContainsKey(inputDto, "itemList");
        // itemList (lineNo, refId, qtyDlvInt), (maksudnya refId adalah
        // soItemId)
        // tenantLoginId
        // userLoginId
        // roleLoginId
        // datetime

        // validation date
        ValidationUtil.valDate(inputDto.getString("docDate"), "Document Date");

        ValidationUtil.valBlankOrNull(inputDto.get("eta")==null?null:inputDto.getString("eta"), "ETD");
        ValidationUtil.valDate(inputDto.getString("eta"), "ETD");
        ValidationUtil.valBlankOrNull(inputDto.get("etaDay")==null?null:inputDto.getString("etaDay"), "ETD Day");

        Long tenantLoginId = inputDto.getLong("tenantLoginId");
        Long userLoginId = inputDto.getLong("userLoginId");
        Long roleLoginId = inputDto.getLong("roleLoginId");
        String datetime = inputDto.getString("datetime");

        String docNo = inputDto.getString("docNo");
        String docDate = inputDto.getString("docDate");
        Long ouId = inputDto.getLong("ouId");
        Long warehouseId = inputDto.getLong("warehouseId");
        String remark = inputDto.getString("remark");
        Long refId = inputDto.getLong("refId");
        String deliveryCode = inputDto.getString("deliveryCode");
        String eta = inputDto.getString("eta");
        String etaDay = inputDto.getString("etaDay");

        // - validation: tenant login can use
        Dto inputDtoForValTenantLoginCanUse = new Dto();
        inputDtoForValTenantLoginCanUse.put("tenantLoginId", tenantLoginId);
        inputDtoForValTenantLoginCanUse.put("userLoginId", userLoginId);
        inputDtoForValTenantLoginCanUse.put("roleLoginId", roleLoginId);
        valTenantLoginCanUse.execute(inputDto);

        // - validation: document date >= soDocDate (ambil dulu pakai
        // findSalesOrderById)
        Dto inputDtoForFindSalesOrderById = new Dto();
        inputDtoForFindSalesOrderById.put("id", refId);
        Dto outputDtoForFindSalesOrderById = findSalesOrderById.execute(inputDtoForFindSalesOrderById);

        // get partner ship to from SO
        Long partnerShipToId = outputDtoForFindSalesOrderById.getLong("partnerShipToId");
        Long partnerShipAddressId = outputDtoForFindSalesOrderById.getLong("partnerShipAddressId");
        String flgDelivery = outputDtoForFindSalesOrderById.getString("flgDelivery");

        String docDateSalesOrder = outputDtoForFindSalesOrderById.getString("docDate");
        CommonBusinessValidator.valCompareDate(docDate, docDateSalesOrder, CommonBusinessValidator.COMPARE_GREATER_EQUAL, "Delivery Order Document Date",
                "Sales Order Document Date");

        // - ouId harus terdaftar dan aktif dan harus merupakan BU dan bukan
        // merupakan outlet (pakai findOUById, valOUAsBusinessUnit dan
        // isOuHasOutlet)
        Dto ouDto = findOUById.execute(new Dto().put("id", ouId));
        if (!tenantLoginId.equals(ouDto.getLong("tenantId"))) {
            throw new CoreException(CommonExceptionConstants.DATA_CANT_USE_DIFF_TENANT, "Delivery Order", "OU");
        }

        if (!GeneralConstants.YES.equals(ouDto.getString("active"))) {
            throw new CoreException(MasterExceptionConstants.DATA_NOT_ACTIVE, "OU");
        }

        valOUAsMainBusinessUnitOrBranch.execute(new Dto().put("id", ouId));

        Dto inputDtoForValAuthorizedPolicyOU = new Dto();
        inputDtoForValAuthorizedPolicyOU.put("userLoginId", userLoginId);
        inputDtoForValAuthorizedPolicyOU.put("roleLoginId", roleLoginId);
        inputDtoForValAuthorizedPolicyOU.put("ouId", ouId);
        inputDtoForValAuthorizedPolicyOU.put("varName", "OU");
        valAuthorizedPolicyOU.execute(inputDtoForValAuthorizedPolicyOU);

        // - gudang harus terdaftar dan aktif (pakai findWarehouseById)
        Dto warehouseDto = findWarehouseById.execute(new Dto().put("id", warehouseId));
        if (!warehouseDto.getString("active").equals(GeneralConstants.YES)) {
            throw new CoreException(MasterExceptionConstants.DATA_NOT_ACTIVE, "Warehouse");
        }

        // - gudang harus termasuk dalam OU yg dipilih
        // (valWarehouseOuExistsByIndex)
        Dto inputDtoForValWarehouseExistsInOu = new Dto();
        inputDtoForValWarehouseExistsInOu.put("warehouseId", warehouseId);
        inputDtoForValWarehouseExistsInOu.put("ouId", ouId);
        valWarehouseExistsInOu.execute(inputDtoForValWarehouseExistsInOu);

        // - user dan role login harus berhak untuk warehouse tersebut
        // (valAuthorizedPolicyWarehouse)
        Dto inputDtoForValAuthorizedPolicyWarehouse = new Dto();
        inputDtoForValAuthorizedPolicyWarehouse.put("userLoginId", userLoginId);
        inputDtoForValAuthorizedPolicyWarehouse.put("roleLoginId", roleLoginId);
        inputDtoForValAuthorizedPolicyWarehouse.put("warehouseId", warehouseId);
        inputDtoForValAuthorizedPolicyWarehouse.put("varName", "DeliveryOrder");
        valAuthorizedPolicyWarehouse.execute(inputDtoForValAuthorizedPolicyWarehouse);

        // - partner (ambil dari SO nya) harus masih merupakan Customer
        // (isPartnerTypeExistsByIndex)
        Long partnerId = outputDtoForFindSalesOrderById.getLong("partnerId");
        Dto inputDtoForIsPartnerTypeExistsByIndex = new Dto();
        inputDtoForIsPartnerTypeExistsByIndex.put("tenantId", tenantLoginId);
        inputDtoForIsPartnerTypeExistsByIndex.put("partnerId", partnerId);
        inputDtoForIsPartnerTypeExistsByIndex.put("groupPartner", MasterConstants.GROUP_PARTNER_CODE_CUSTOMER);

        Dto ouputDtoForIsPartnerTypeExistsByIndex = isPartnerTypeExistsByIndex.execute(inputDtoForIsPartnerTypeExistsByIndex);
        if (!ouputDtoForIsPartnerTypeExistsByIndex.getBoolean("exists")) {
            throw new CoreException(SalesExceptionConstants.PARTNER_NOT_AUTHORIZED_AS_CUSTOMER, partnerId);
        }

        // - partnerShipToId harus terdaftar di master partner dan aktif dan
        // punya relasi dengan partner sebagai ship to (ambil dari SO)
        // (isPartnerRelationExistsByIndex) dan cek lagi flag shipping Y (bisa
        // lihat di AddSalesOrder contoh yg mirip)
        Dto outputDtoForFindPartnerById = findPartnerById.execute(new Dto().put("id", partnerShipToId));
        if (!outputDtoForFindPartnerById.getString("active").equals(GeneralConstants.YES)) {
            throw new CoreException(MasterExceptionConstants.DATA_NOT_ACTIVE, "Partner");
        }

        if (!outputDtoForFindPartnerById.getLong("tenantId").equals(tenantLoginId)) {
            throw new CoreException(CommonExceptionConstants.DATA_CANT_USE_DIFF_TENANT, "Partner", "Partner Ship To");
        }

        Dto paramCustomerShipToRelation = new Dto();
        paramCustomerShipToRelation.put("tenantId", tenantLoginId);
        paramCustomerShipToRelation.put("partnerId", partnerId);
        paramCustomerShipToRelation.put("relationId", partnerShipToId);

        Dto resultCustomerShipToRelation = isPartnerRelationExistsByIndex.execute(paramCustomerShipToRelation);

        if (resultCustomerShipToRelation.getBoolean("exists")) {
            Dto partnerRelationDto = resultCustomerShipToRelation.getDto("partnerRelationDto");
            if (!GeneralConstants.YES.equals(partnerRelationDto.getString("flagShipping"))) {
                throw new CoreException(SalesExceptionConstants.CUSTOMER_NOT_RELATED_AS_CUSTOMER_SHIP_TO, partnerShipToId, partnerId);
            }
        } else {
            throw new CoreException(SalesExceptionConstants.CUSTOMER_NOT_RELATED_AS_CUSTOMER_SHIP_TO, partnerShipToId, partnerId);
        }

        // - partnerShipAddressId harus terdaftar di master partner address dan
        // aktif dan punya relasi dengan partner. (cukup cek apakah partner id
        // di partner address dan partnerShipToId itu sama atau tidak) gunakan
        // FindPartnerAddressById
        Dto ouputDtoForFindPartnerAddressById = findPartnerAddressById.execute(new Dto().put("id", partnerShipAddressId));
        if (!ouputDtoForFindPartnerAddressById.getString("active").equals(GeneralConstants.YES)) {
            throw new CoreException(MasterExceptionConstants.DATA_NOT_ACTIVE, "Partner Ship Address");
        }

        if (!ouputDtoForFindPartnerAddressById.getLong("partnerId").equals(partnerShipToId)) {
            throw new CoreException(SalesExceptionConstants.PARTNER_ADDRESS_IS_NOT_ASSOCIATED_TO_PARTNER, partnerShipAddressId, partnerShipToId);
        }

        // validation eta >= so date
        CommonBusinessValidator.valCompareDate(inputDto.getString("eta"), inputDto.getString("docDate"), CommonBusinessValidator.COMPARE_GREATER_EQUAL,
                "ETD", "Document Date");

        // validation combo eta day
        Dto inputComboEtaDayDto = new Dto();
        inputComboEtaDayDto.put("comboId", ComboIdMasterConstants.COMBO_ETADAY);
        inputComboEtaDayDto.put("code", inputDto.getString("etaDay"));
        inputComboEtaDayDto.put("varName", "ETD Day");
        valComboValueByCode.execute(inputComboEtaDayDto);

        // - semua validasi di atas (yg berhubungan dengan master) harus
        // memiliki tenantId yg sama dengan tenantLoginId

        // - status doc diisi DRAFT

        // - cek data exists atau nggak berdasarkan unique constraint (jika
        // exists, maka throw error) gunakan isDeliveryOrderExistsByIndex
        Dto inputDtoForIsDeliveryOrderExistsByIndex = new Dto();
        inputDtoForIsDeliveryOrderExistsByIndex.put("tenantId", tenantLoginId);
        inputDtoForIsDeliveryOrderExistsByIndex.put("docTypeId", SalesConstants.DOCUMENT_DELIVERY_ORDER);
        inputDtoForIsDeliveryOrderExistsByIndex.put("docNo", docNo);
        inputDtoForIsDeliveryOrderExistsByIndex.put("docDate", docDate);
        inputDtoForIsDeliveryOrderExistsByIndex.put("ouId", ouId);

        Dto outputDtoForIsDeliveryOrderExistsByIndex = isDeliveryOrderExistsByIndex.execute(inputDtoForIsDeliveryOrderExistsByIndex);
        if (outputDtoForIsDeliveryOrderExistsByIndex.getBoolean("exists")) {
            throw new CoreException(SalesExceptionConstants.DELIVERY_ORDER_ALREADY_EXISTS, tenantLoginId, SalesConstants.DOCUMENT_DELIVERY_ORDER, docNo,
                    docDate, ouId);
        }

        // create delivery order dto for insert
        Dto deliveryOrderDto = new Dto();
        deliveryOrderDto.put("tenantId", tenantLoginId);
        deliveryOrderDto.put("docTypeId", SalesConstants.DOCUMENT_DELIVERY_ORDER);
        deliveryOrderDto.put("docNo", docNo);
        deliveryOrderDto.put("docDate", docDate);
        deliveryOrderDto.put("ouId", ouId);
        deliveryOrderDto.put("extDocNo", outputDtoForFindSalesOrderById.getString("extDocNo"));
        deliveryOrderDto.put("extDocDate", outputDtoForFindSalesOrderById.getString("extDocDate"));
        deliveryOrderDto.put("refDocTypeId", outputDtoForFindSalesOrderById.getLong("docTypeId"));
        deliveryOrderDto.put("refId", outputDtoForFindSalesOrderById.getLong("id"));
        deliveryOrderDto.put("remark", remark);
        deliveryOrderDto.put("partnerShipToId", partnerShipToId);
        deliveryOrderDto.put("partnerShipAddressId", partnerShipAddressId);
        deliveryOrderDto.put("warehouseId", warehouseId);
        deliveryOrderDto.put("noVehicle", GeneralConstants.EMPTY_VALUE);
        deliveryOrderDto.put("flgDelivery", flgDelivery);
        deliveryOrderDto.put("deliveryCode", deliveryCode);
        deliveryOrderDto.put("eta", eta);
        deliveryOrderDto.put("etaDay", etaDay);
        deliveryOrderDto.put("statusDoc", SalesConstants.DRAFT_TRANSACTION);
        deliveryOrderDto.put("workflowStatus", SalesConstants.WORKFLOW_STATUS_DRAFT);

        this.prepareInsertAudit(deliveryOrderDto, userLoginId, datetime);
        this.prepareUpdateAudit(deliveryOrderDto, userLoginId, datetime);

        List<Dto> failList = new ArrayList<Dto>();
        List<Dto> resultDeliveryOrderItemList = new ArrayList<Dto>();

        // - qtyDlvInt harus > 0 (jika error waktu looping di list, maka harus
        // di try catch dan disimpan dulu, tidak langsung di throw, contohnya
        // bisa lihat di AddPurchaseOrder)

        Map<Long, Dto> mapItemVsQty = new HashMap<Long, Dto>();

        List<Dto> itemList = inputDto.getList("itemList");
        if (itemList != null && !itemList.isEmpty()) {
            for (Dto itemDto : itemList) {
                // itemDto = lineNo, refId, qtyDlvInt
                String lineNo = itemDto.get("lineNo").toString();
                Long itemRefId = Long.valueOf(itemDto.get("refId").toString());
                Double qtyDlvInt = Double.valueOf(itemDto.get("qtyDlvInt").toString());
                // get So Item
                Dto refSoItemDto = findSalesOrderItemById.execute(new Dto().put("id", itemRefId));
                Long itemDoProductId = refSoItemDto.getLong("productId");
                itemDto.put("productId", itemDoProductId);

                if (!refSoItemDto.getLong("soId").equals(Long.valueOf(inputDto.getLong("refId").toString()))) {
                    throw new CoreException(SalesExceptionConstants.DELIVERY_ORDER_ITEM_NOT_FROM_REFERENCE_SALES_ORDER_ITEM, refSoItemDto.getLong("productId"));
                }

                // get ratio for soQty and soQtyInt
                Dto soBalanceItemDto = findSalesOrderBalanceItemById.execute(new Dto().put("soItemId", itemRefId));
                Double qtySo = soBalanceItemDto.getDouble("qtySo");
                Double qtySoInt = soBalanceItemDto.getDouble("qtySoInt");
                //b.qty_so_int - b.qty_cancel_int + b.qty_add_int - b.qty_dlv_int + b.qty_return_int AS outstandingQtyInt,

                // count qtyDlvSoCalc using ratio
                Calc qtyDlvSoCalc = new Calc(qtySo).multiply(qtyDlvInt).divide(qtySoInt);

                try {
                    if (qtyDlvInt <= 0) {
                        throw new CoreException(SalesExceptionConstants.QUANTITY_INTERNAL_MUST_BE_GREATER_THAN_ZERO);
                    }

                    if(!SalesConstants.RELEASED_TRANSACTION.equals(soBalanceItemDto.get("statusItem"))){
                        throw new CoreException(SalesExceptionConstants.BALANCE_DELIVERY_ORDER_ITEM_ALREADY_USED_IN_OTHER_TRANSACTION);
                    }

                    ValidationUtil.valBlankOrNull(itemDto.get("productStatus")==null?null:itemDto.getString("productStatus"), "Product Status Item");
                    String productStatus = itemDto.getString("productStatus");

                    // validation product status
                    Dto outputIsProductStatusExistsByIndex = isProductStatusExistsByIndex.execute(new Dto()
                            .put("tenantId", tenantLoginId)
                            .put("productStatusCode", productStatus));

                    if(!outputIsProductStatusExistsByIndex.getBoolean("exists")){
                        throw new CoreException(MasterExceptionConstants.PRODUCT_STATUS_NOT_FOUND, tenantLoginId, productStatus);
                    } else{
                        Dto productStatusDto = outputIsProductStatusExistsByIndex.getDto("productStatusDto");
                        if(!GeneralConstants.YES.equals(productStatusDto.get("flgSell"))){
                            throw new CoreException(SalesExceptionConstants.PRODUCT_WITH_STATUS_IS_NOT_FOR_SELL, productStatus);
                        }
                    }

                    Dto deliveryOrderItemDto = new Dto();
                    deliveryOrderItemDto.put("tenantId", tenantLoginId);
                    deliveryOrderItemDto.put("doId", GeneralConstants.NULL_REF_VALUE_LONG);
                    deliveryOrderItemDto.put("lineNo", lineNo);
                    deliveryOrderItemDto.put("refDocTypeId", refSoItemDto.getLong("refDocTypeId"));
                    deliveryOrderItemDto.put("refId", itemRefId);
                    deliveryOrderItemDto.put("productId", itemDoProductId);
                    deliveryOrderItemDto.put("qtyDlvSo", qtyDlvSoCalc.doubleValue());
                    deliveryOrderItemDto.put("soUomId", refSoItemDto.get("soUomId"));
                    deliveryOrderItemDto.put("qtyDlvInt", qtyDlvInt);
                    deliveryOrderItemDto.put("baseUomId", refSoItemDto.get("baseUomId"));
                    deliveryOrderItemDto.put("remark", GeneralConstants.EMPTY_VALUE);
                    deliveryOrderItemDto.put("productStatus", productStatus);

                    // check if product has serial number
                    Dto inputDtoForIsProductHasSerialNumber = new Dto();
                    inputDtoForIsProductHasSerialNumber.put("tenantId", tenantLoginId);
                    inputDtoForIsProductHasSerialNumber.put("productId", itemDoProductId);
                    Dto outputDtoForIsProductHasSerialNumber = isProductHasSerialNumber.execute(inputDtoForIsProductHasSerialNumber);

                    String flgSerialNumber = GeneralConstants.NO;
                    Long productBalanceId = outputDtoForIsProductHasSerialNumber.getLong("productBalanceId");

                    if (outputDtoForIsProductHasSerialNumber.getBoolean("hasSerialNumber")) {
                        flgSerialNumber = GeneralConstants.YES;
                    } else {
                        flgSerialNumber = GeneralConstants.NO;

                        Dto itemProductBalanceDto = findProductBalanceById.execute(new Dto().put("id", productBalanceId));
                        Dto paramSufficientProductBalanceStock = new Dto();
                        paramSufficientProductBalanceStock.put("tenantId", tenantLoginId);
                        paramSufficientProductBalanceStock.put("productId", itemDoProductId);
                        paramSufficientProductBalanceStock.put("serialNumber", itemProductBalanceDto.getString("serialNumber"));
                        paramSufficientProductBalanceStock.put("lotNumber", itemProductBalanceDto.getString("lotNumber"));
                        paramSufficientProductBalanceStock.put("qty", qtyDlvInt);
                        paramSufficientProductBalanceStock.put("warehouseId", warehouseId);
                        paramSufficientProductBalanceStock.put("productStatus", productStatus);
                        valSufficientProductBalanceStock.execute(paramSufficientProductBalanceStock);

                        Dto deliveryOrderProductDto = new Dto();
                        deliveryOrderProductDto.put("tenantId", tenantLoginId);
                        deliveryOrderProductDto.put("doItemId", GeneralConstants.NULL_REF_VALUE_LONG);
                        deliveryOrderProductDto.put("lineNo", lineNo);
                        deliveryOrderProductDto.put("productId", itemDoProductId);
                        deliveryOrderProductDto.put("productBalanceId", productBalanceId);
                        deliveryOrderProductDto.put("productStatus", productStatus);
                        deliveryOrderProductDto.put("qtyDlvInt", qtyDlvInt);
                        deliveryOrderProductDto.put("baseUomId", refSoItemDto.get("baseUomId"));
                        deliveryOrderProductDto.put("remark", GeneralConstants.EMPTY_VALUE);

                        deliveryOrderItemDto.put("deliveryOrderProduct", deliveryOrderProductDto);
                    }
                    deliveryOrderItemDto.put("flgSerialNumber", flgSerialNumber);
                    deliveryOrderItemDto.put("productBalanceId", productBalanceId);

                    this.prepareInsertAudit(deliveryOrderItemDto, userLoginId, datetime);
                    this.prepareUpdateAudit(deliveryOrderItemDto, userLoginId, datetime);

                    resultDeliveryOrderItemList.add(deliveryOrderItemDto);

                    Dto findProductByIdDto = findProductById.execute(new Dto().put("id", itemDoProductId));

                    //map to check qtyDlvInt < qtyOs
                    Dto mapItemDto = mapItemVsQty.get(deliveryOrderItemDto.getLong("refId"));
                    if (mapItemDto == null) {
                        mapItemDto = new Dto();
                        mapItemDto.putDouble("qtyDlvSo", deliveryOrderItemDto.getDouble("qtyDlvSo"));
                        mapItemDto.putString("productCode", findProductByIdDto.getString("code"));
                        mapItemDto.putString("productName", findProductByIdDto.getString("name"));
                    } else {
                        mapItemDto.putDouble("qtyDlvSo", new Calc(mapItemDto.getDouble("qtyDlvSo")).add(deliveryOrderItemDto.getDouble("qtyDlvSo")).doubleValue());
                    }
                    mapItemVsQty.put(deliveryOrderItemDto.getLong("refId"), mapItemDto);

                } catch (CoreException e) {
                    log.error("", e);
                    Dto errorDto = (Dto) itemDto.clone();
                    errorDto.put("errorKey", e.getErrorKey());

                    List<Object> argsList = new ArrayList<Object>();
                    if (e.getParamValues() != null && e.getParamValues().length > 0)
                        for (Object paramValue : e.getParamValues()) {
                            argsList.add(paramValue);
                        }
                    errorDto.put("args", argsList);
                    errorDto.put("version", GeneralConstants.NULL_REF_VALUE_LONG);

                    failList.add(errorDto);
                }
            }
            // Validasi total qty
            for (Entry<Long, Dto> entry : mapItemVsQty.entrySet()) {
                Dto entryDto = entry.getValue();

                // qty tolerance = ( qty_so - qty_cancel + qty add) * percentage tolerance
                // validasi : qty so - qty cancel + qty add + qty tolerance - qty dlv (yg ada di saldo item) + qty retur >= 0
                Dto soBalanceItemDto = findSalesOrderBalanceItemById.execute(new Dto().put("soItemId", entry.getKey()));
                Calc totalQtySo = new Calc(soBalanceItemDto.getDouble("qtySo"))
                                        .add(soBalanceItemDto.getDouble("qtyAdd"))
                                        .subtract(soBalanceItemDto.getDouble("qtyCancel"));
                Calc qtySoToleranceCalc = totalQtySo.multiply(soBalanceItemDto.getDouble("toleranceDlvQty")).divide(100);
                Calc qtyDlvSoMaxInput = totalQtySo.add(qtySoToleranceCalc)
                                            .subtract(soBalanceItemDto.getDouble("qtyDlv"));

                if (entryDto.getDouble("qtyDlvSo") > qtyDlvSoMaxInput.doubleValue()) {
                    // throw error
                    throw new CoreException(SalesExceptionConstants.TOTAL_QTY_DLV_SO_IS_MORE_THAN_ALLOWED, entryDto.getDouble("qtyDlvSo"),
                            qtyDlvSoMaxInput.doubleValue(), entryDto.getString("productCode"), entryDto.getString("productName"));
                }

            }
        }

        if (!failList.isEmpty()) {
            Dto errorParamDto = new Dto();
            DtoUtil.putInErrorParamDto(errorParamDto, "failList",
                    new String[] { "lineNo", "productId", "productName", "refId", "qtyDlvInt", "errorKey", "args" }, failList);
            throw new CoreException(SalesExceptionConstants.DELIVERY_ORDER_ITEMS_ERROR, errorParamDto);
        }

        inputDto.put("resultDeliveryOrder", deliveryOrderDto);
        inputDto.put("resultDeliveryOrderItemList", resultDeliveryOrderItemList);

        return null;
    }

    @SuppressWarnings("unchecked")
    @Override
    public Dto process(Dto inputDto, Dto originalDto) throws Exception {
        Dto outputDto = new Dto();

        // add delivery order
        DeliveryOrder deliveryOrder = GsonUtil.fromDto(inputDto.getDto("resultDeliveryOrder"), DeliveryOrder.class);
        deliveryOrderDao.persist(deliveryOrder);

        List<Dto> resultDeliveryOrderItemList = inputDto.getList("resultDeliveryOrderItemList");
        for (Dto deliveryOrderItemDto : resultDeliveryOrderItemList) {
            // add delivery order item
            deliveryOrderItemDto.put("doId", deliveryOrder.getId());
            DeliveryOrderItem deliveryOrderItem = GsonUtil.fromDto(deliveryOrderItemDto, DeliveryOrderItem.class);

            deliveryOrderItemDao.persist(deliveryOrderItem);

            // update sl_so_balance_item
            Dto salesOrderBalanceItemDto = findSalesOrderBalanceItemById.execute(new Dto().put("soItemId", deliveryOrderItem.getRefId()));
            this.prepareUpdateAudit(salesOrderBalanceItemDto, inputDto.getLong("userLoginId"), inputDto.getString("datetime"));

            SalesOrderBalanceItem salesOrderBalanceItem = GsonUtil.fromDto(salesOrderBalanceItemDto, SalesOrderBalanceItem.class);
            salesOrderBalanceItem.setStatusItem(SalesConstants.IN_PROGRESS_TRANSACTION);
            salesOrderBalanceItem.setQtyDlv(salesOrderBalanceItem.getQtyDlv() + deliveryOrderItem.getQtyDlvSo());
            salesOrderBalanceItem.setQtyDlvInt(salesOrderBalanceItem.getQtyDlvInt() + deliveryOrderItem.getQtyDlvInt());

            salesOrderBalanceItemDao.merge(salesOrderBalanceItem.getSoItemId(), salesOrderBalanceItem);

            // insert DoProduct and update in_product_balance_stock if no
            // serial number
            if (GeneralConstants.NO.equals(deliveryOrderItemDto.getString("flgSerialNumber"))) {
                Dto deliveryOrderProductDto = deliveryOrderItemDto.getDto("deliveryOrderProduct");
                deliveryOrderProductDto.put("doItemId", deliveryOrderItem.getId());

                DeliveryOrderProduct deliveryOrderProduct = GsonUtil.fromDto(deliveryOrderProductDto, DeliveryOrderProduct.class);
                deliveryOrderProductDao.persist(deliveryOrderProduct);

                // update in_product_balance_stock
                Long productBalanceId = deliveryOrderItemDto.getLong("productBalanceId");
                Dto inputDtoForFindProductBalanceStockByIndex = new Dto();
                inputDtoForFindProductBalanceStockByIndex.put("tenantId", deliveryOrder.getTenantId());
                inputDtoForFindProductBalanceStockByIndex.put("warehouseId", deliveryOrder.getWarehouseId());
                inputDtoForFindProductBalanceStockByIndex.put("productId", deliveryOrderItem.getProductId());
                inputDtoForFindProductBalanceStockByIndex.put("productBalanceId", productBalanceId);
                inputDtoForFindProductBalanceStockByIndex.put("productStatus", deliveryOrderItemDto.get("productStatus"));

                Dto productBalanceStockDto = findProductBalanceStockByIndex.execute(inputDtoForFindProductBalanceStockByIndex);
                this.prepareUpdateAudit(productBalanceStockDto, inputDto.getLong("userLoginId"), inputDto.getString("datetime"));

                ProductBalanceStock productBalanceStock = GsonUtil.fromDto(productBalanceStockDto, ProductBalanceStock.class);
                //productBalanceStock.setQty(productBalanceStock.getQty() - deliveryOrderItem.getQtyDlvInt());

                //productBalanceStockDao.merge(productBalanceStock.getId(), productBalanceStock);

                //query untuk mengurangi product balance stock
                QueryBuilder builder;
                builder = new QueryBuilder();
                builder.add("UPDATE ").add(ProductBalanceStock.TABLE_NAME)
                    .add(" SET qty = qty - :qtyPerubahan, ")
                    .add(" update_datetime = :updateDatetime, ")
                    .add(" update_user_id = :updateUserId, ")
                    .add(" version = version+1 ")
                    .add(" WHERE product_balance_stock_id = :id");
                Query q;
                q = productBalanceStockDao.createNativeQuery(builder.toString());

                q.setParameter("updateDatetime", inputDto.getString("datetime"));
                q.setParameter("updateUserId", inputDto.getLong("userLoginId"));
                q.setParameter("qtyPerubahan",deliveryOrderItem.getQtyDlvInt());
                q.setParameter("id", productBalanceStockDto.getLong("id"));
                log.debug("query update stock :"+builder.toString());
                q.executeUpdate();
            }

        }

        outputDto.put("id", deliveryOrder.getId());
        outputDto.put("docNo", deliveryOrder.getDocNo());
        outputDto.put("docDate", deliveryOrder.getDocDate());
        outputDto.put("ouId", deliveryOrder.getOuId());
        outputDto.put("warehouseId", deliveryOrder.getWarehouseId());
        outputDto.put("partnerShipToId", deliveryOrder.getPartnerShipToId());
        outputDto.put("partnerShipAddressId", deliveryOrder.getPartnerShipAddressId());
        outputDto.put("deliveryCode", deliveryOrder.getDeliveryCode());
        outputDto.put("remark", deliveryOrder.getRemark());
        outputDto.put("refId", deliveryOrder.getRefId());
        outputDto.put("version", deliveryOrder.getVersion());
        outputDto.put("eta", deliveryOrder.getEta());
        outputDto.put("etaDay", deliveryOrder.getEtaDay());

        return outputDto;
    }
}