GetAuthorizedTopSellingProductBalanceStockListForRequestGood

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.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.util.DtoUtil;
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 = "ouId", description = "ou id warehouse from", 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 GetAuthorizedTopSellingProductBalanceStockListForRequestGoodsTransferOut 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);
        }

        List<Object[]> result = null;

        QueryBuilder builder = new QueryBuilder();
        builder.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(" 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(" AND A.product_id IN ( SELECT L.product_id 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(InventoryCustomConstantsForXCom.M_COMMISION_OU_TABLE_NAME)
               .add(" M ON L.tenant_id = M.tenant_id AND L.group_product_ou_id = M.group_product_ou_id ")
               .add(" WHERE K.tenant_id = :tenantId AND K.ou_id = :ouId 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(" 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 ");


        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("ouId", inputDto.getLong("ouId"));

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

}