GetAuthorizedTopSellingProductBalanceStockListForGoodsTransf

package org.jleaf.erp.inv.bo.productbalancestock;

import java.util.List;

import javax.persistence.Query;

import org.jleaf.common.entity.UserRole;
import org.jleaf.core.AbstractBusinessFunction;
import org.jleaf.core.BusinessFunction;
import org.jleaf.core.CoreException;
import org.jleaf.core.CoreExceptionConstants;
import org.jleaf.core.Dto;
import org.jleaf.core.GeneralConstants;
import org.jleaf.core.annotation.ErrorList;
import org.jleaf.core.annotation.Info;
import org.jleaf.core.annotation.InfoIn;
import org.jleaf.core.annotation.InfoOut;
import org.jleaf.core.dao.CriteriaHelper;
import org.jleaf.core.dao.QueryBuilder;
import org.jleaf.erp.inv.bo.InventoryCustomConstantsForXCom;
import org.jleaf.erp.inv.dao.InventoryDao;
import org.jleaf.erp.inv.entity.ProductBalance;
import org.jleaf.erp.inv.entity.ProductBalanceStock;
import org.jleaf.erp.master.entity.CommissionOu;
import org.jleaf.erp.master.entity.CtgrProduct;
import org.jleaf.erp.master.entity.PolicyProduct;
import org.jleaf.erp.master.entity.PolicyWarehouse;
import org.jleaf.erp.master.entity.Product;
import org.jleaf.erp.master.entity.SubCtgrProduct;
import org.jleaf.erp.master.entity.Uom;
import org.jleaf.erp.master.entity.Warehouse;
import org.jleaf.erp.master.entity.WarehouseOu;
import org.jleaf.util.DtoUtil;
import org.jleaf.util.ValidationUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 *
 * @author Adrian
 * Mar 30, 2017
 */

@Service
@InfoIn(value = {
        @Info(name = "tenantId", description = "tenant id", type = Long.class),
        @Info(name = "warehouseId", description = "warehouse from id", type = Long.class),
        @Info(name = "subCtgrProductId", description = "sub ctgr product id", type = Long.class),
        @Info(name = "productCodeName", description = "product code / name", type = String.class),
        @Info(name = "serialNumber", description = "serial No", type = String.class),
        @Info(name = "dateFrom", description = "date from", type = String.class),
        @Info(name = "dateTo", description = "date to", type = String.class),
        @Info(name = "warehouseToId", description = "warehouse to id", type = Long.class),
        @Info(name = "active", description = "flag product active / tidak", type = String.class),
        @Info(name = "userLoginId", description = "user login id", type = Long.class),
        @Info(name = "roleLoginId", description = "role login id", type = Long.class),
        @Info(name = "limit", description = "limit", type = Long.class),
        @Info(name = "offset", description = "offset", type = Long.class)
})
@InfoOut(value = {
        @Info(name = "productBalanceStockList", description = "list of product balance stock (id, warehouseId, warehouseCode, warehouseName, productId, productCode, productName, ctgrProductId, ctgrProductCode, ctgrProductName, subCtgrProductId, subCtgrProductCode, subCtgrProductName, productBalanceId, serialNumber, lotNumber, productExpiredDate, productYearMade, qty, baseUomId, baseUomCode, baseUomName, minQty, maxQty, productStatus)", type = List.class)
})
@ErrorList(errorKeys = {
        CoreExceptionConstants.DTO_CANNOT_NULL
})
public class GetAuthorizedTopSellingProductBalanceStockListForGoodsTransferOut extends AbstractBusinessFunction implements BusinessFunction {

    @Autowired
    InventoryDao inventoryDao;

    public String getDescription() {
        return "get authorized product balance stock list for goods transfer";
    }

    @SuppressWarnings("unchecked")
    public Dto execute(Dto inputDto) throws Exception {
        if (inputDto == null) {
            throw new CoreException(CoreExceptionConstants.DTO_CANNOT_NULL);
        }

        ValidationUtil.valDtoContainsKey(inputDto, "tenantId");
        ValidationUtil.valDtoContainsKey(inputDto, "warehouseId");
        ValidationUtil.valDtoContainsKey(inputDto, "subCtgrProductId");
        ValidationUtil.valDtoContainsKey(inputDto, "productCodeName");
        ValidationUtil.valDtoContainsKey(inputDto, "serialNumber");
        ValidationUtil.valDtoContainsKey(inputDto, "dateFrom");
        ValidationUtil.valDtoContainsKey(inputDto, "dateTo");
        ValidationUtil.valDtoContainsKey(inputDto, "warehouseToId");
        ValidationUtil.valDtoContainsKey(inputDto, "active");
        ValidationUtil.valDtoContainsKey(inputDto, "userLoginId");
        ValidationUtil.valDtoContainsKey(inputDto, "roleLoginId");
        ValidationUtil.valDtoContainsKey(inputDto, "limit");
        ValidationUtil.valDtoContainsKey(inputDto, "offset");

        ValidationUtil.valDate(inputDto, "dateFrom");
        ValidationUtil.valDate(inputDto, "dateTo");

        List<Object[]> result = null;

        QueryBuilder builder = new QueryBuilder();
        builder.add(" WITH ")
               .add(InventoryCustomConstantsForXCom.TOP_SELLING_PRODUCT_TABLE_NAME)
               .add(" AS ( ")
               .add(" SELECT L.product_id, SUM(L.qty) AS selling_qty FROM ")
               .add(InventoryCustomConstantsForXCom.I_TRX_POS_TABLE_NAME)
               .add(" K INNER JOIN ")
               .add(InventoryCustomConstantsForXCom.I_TRX_POS_ITEM_TABLE_NAME)
               .add(" L ON K.trx_pos_id = L.trx_pos_id AND K.tenant_id = L.tenant_id AND K.process_no = L.process_no ")
               .add(" INNER JOIN ")
               .add(CommissionOu.TABLE_NAME)
               .add(" M ON L.tenant_id = M.tenant_id AND L.group_product_ou_id = M.group_product_ou_id ")
               .add(" INNER JOIN ")
               .add(WarehouseOu.TABLE_NAME)
               .add(" O ON K.ou_id = O.ou_id ")
               .add(" WHERE K.tenant_id = :tenantId AND O.warehouse_id = :warehouseToId AND K.doc_date BETWEEN :dateFrom AND :dateTo AND K.doc_date BETWEEN M.date_from AND M.date_to AND M.flg_promo = :NO ")
               .add(" AND NOT EXISTS( SELECT 1 FROM ")
               .add(InventoryCustomConstantsForXCom.I_TRX_LOG_VOIDED_POS_TABLE_NAME)
               .add(" N WHERE K.tenant_id = N.tenant_id AND K.doc_no = N.doc_no AND K.doc_date = N.doc_date AND K.ou_id = N.ou_id ) ")
               .add(" GROUP BY L.product_id ) ")
               .add("SELECT a.product_balance_stock_id, a.warehouse_id, b.warehouse_code, b.warehouse_name, a.product_id, c.product_code, c.product_name, c.ctgr_product_id, d.ctgr_product_code, d.ctgr_product_name, c.sub_ctgr_product_id, e.sub_ctgr_product_code, e.sub_ctgr_product_name, a.product_balance_id, f.serial_number, f.lot_number, f.product_expired_date, f.product_year_made, SUM(a.qty) AS qty, c.base_uom_id, g.uom_code, g.uom_name, c.min_qty, c.max_qty, a.product_status FROM ")
               .add(ProductBalanceStock.TABLE_NAME)
               .add(" a INNER JOIN ")
               .add(Warehouse.TABLE_NAME)
               .add(" b ON a.warehouse_id = b.warehouse_id ")
               .add(" INNER JOIN ")
               .add(Product.TABLE_NAME)
               .add(" c ON a.product_id = c.product_id ")
               .add(" INNER JOIN ")
               .add(CtgrProduct.TABLE_NAME)
               .add(" d ON c.ctgr_product_id = d.ctgr_product_id ")
               .add(" INNER JOIN ")
               .add(SubCtgrProduct.TABLE_NAME)
               .add(" e ON c.sub_ctgr_product_id = e.sub_ctgr_product_id ")
               .add(" INNER JOIN ")
               .add(ProductBalance.TABLE_NAME)
               .add(" f ON a.product_balance_id = f.product_balance_id ")
               .add(" INNER JOIN ")
               .add(Uom.TABLE_NAME)
               .add(" g ON c.base_uom_id = g.uom_id ")
               .add(" INNER JOIN ")
               .add(UserRole.TABLE_NAME)
               .add(" h ON h.user_id = :userLoginId AND h.role_id = :roleLoginId ")
               .add(" INNER JOIN ")
               .add(PolicyProduct.TABLE_NAME)
               .add(" i ON i.sub_ctgr_product_id = c.sub_ctgr_product_id AND i.user_role_id = h.user_role_id ")
               .add(" INNER JOIN ")
               .add(PolicyWarehouse.TABLE_NAME)
               .add(" j ON j.warehouse_id = a.warehouse_id AND j.user_role_id = h.user_role_id ")
               .add(" INNER JOIN ")
               .add(InventoryCustomConstantsForXCom.TOP_SELLING_PRODUCT_TABLE_NAME)
               .add(" P ON P.product_id = A.product_id ")
               .add(" WHERE a.tenant_id = :tenantId ")
               .addIfNotEquals(inputDto.getLong("warehouseId"), GeneralConstants.NULL_REF_VALUE_LONG, " AND a.warehouse_id = " + inputDto.getLong("warehouseId") + " ")
               .addIfNotEquals(inputDto.getLong("subCtgrProductId"), GeneralConstants.NULL_REF_VALUE_LONG, " AND c.sub_ctgr_product_id = " + inputDto.getLong("subCtgrProductId") + " ")
               .addIfNotEmpty(inputDto.getString("productCodeName"), " AND (" + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("productCodeName"), "c.product_code") + " OR " + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("productCodeName"), "c.product_name") + ")")
               .addIfNotEmpty(inputDto.getString("serialNumber"), " AND " + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("serialNumber"), "f.serial_number"))
               .addIfNotEmpty(inputDto.getString("active"), " AND c.active = '" + inputDto.getString("active") + "'")
               .add(" GROUP BY a.product_balance_stock_id, a.warehouse_id, b.warehouse_code, b.warehouse_name, a.product_id, c.product_code, c.product_name, c.ctgr_product_id, d.ctgr_product_code, d.ctgr_product_name, c.sub_ctgr_product_id, e.sub_ctgr_product_code, e.sub_ctgr_product_name, a.product_balance_id, f.serial_number, f.lot_number, f.product_expired_date, f.product_year_made, c.base_uom_id, g.uom_code, g.uom_name, c.min_qty, c.max_qty, a.product_status, P.selling_qty ")
               .add(" ORDER BY P.selling_qty ");


        Query q = inventoryDao.createNativeQuery(builder.toString());
        q.setParameter("tenantId", inputDto.getLong("tenantId"));
        q.setParameter("userLoginId", inputDto.getLong("userLoginId"));
        q.setParameter("roleLoginId", inputDto.getLong("roleLoginId"));
        q.setParameter("dateFrom", inputDto.getString("dateFrom"));
        q.setParameter("dateTo", inputDto.getString("dateTo"));
        q.setParameter("NO", GeneralConstants.NO);
        q.setParameter("warehouseToId", inputDto.getLong("warehouseToId"));

        Long limit = inputDto.getLong("limit");
        Long offset = inputDto.getLong("offset");
        if(limit!=null && offset!=null){
            q.setMaxResults(limit.intValue());
            q.setFirstResult(offset.intValue());
        }

        result = q.getResultList();
        return new Dto().putList("productBalanceStockList", DtoUtil.createDtoListFromArray(result,
                "id", "warehouseId", "warehouseCode", "warehouseName", "productId", "productCode", "productName", "ctgrProductId", "ctgrProductCode", "ctgrProductName", "subCtgrProductId", "subCtgrProductCode", "subCtgrProductName", "productBalanceId", "serialNumber", "lotNumber", "productExpiredDate", "productYearMade", "qty", "baseUomId", "baseUomCode", "baseUomName", "minQty", "maxQty", "productStatus"));
    }

}