lookup_product_pos

package org.jleaf.erp.pos_intgr.bo.stockproduct;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import org.jleaf.common.entity.OU;
import org.jleaf.common.entity.OUType;
import org.jleaf.core.*;
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.InventoryConstants;
import org.jleaf.erp.inv.dao.ProductBalanceStockDao;
import org.jleaf.erp.inv.entity.ProductBalance;
import org.jleaf.erp.inv.entity.ProductBalanceStock;
import org.jleaf.erp.master.entity.*;
import org.jleaf.erp.pos_intgr.PosIntegrationConstants;
import org.jleaf.erp.pos_intgr.PosIntegrationConstantsForSasa;
import org.jleaf.erp.pos_intgr.entity.PointOfSales;
import org.jleaf.erp.pos_intgr.entity.PointOfSalesItem;
import org.jleaf.util.DtoUtil;
import org.jleaf.util.ValidationUtil;
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;

//@formatter:off
@Service
@InfoIn(value = { @Info(name = "tenantId", description = "tenant id", type = Long.class),
        @Info(name = "ouId", description = "ou id", type = Long.class),
        @Info(name = "warehouseId", description = "warehouse id", type = Long.class),
        @Info(name = "ctgrProductId", description = "category product id", type = Long.class),
        @Info(name = "subCtgrProductId", description = "sub category product id", type = Long.class),
        @Info(name = "brandId", description = "brand id", type = Long.class),
        @Info(name = "productCodeName", description = "product code/name", type = String.class),
        @Info(name = "serialNumber", description = "serial number", type = String.class),
        @Info(name = "parentId", description = "ou parent id", type = Long.class),
        @Info(name = "date", description = "date", type = String.class),
        @Info(name = "limit", description = "offset", type = Long.class, required = false),
        @Info(name = "offset", description = "limit", type = Long.class, required = false),
        @Info(name = "userLoginId", description = "user login id", type = Long.class),
        @Info(name = "roleLoginId", description = "role login id", type = Long.class),
        @Info(name = "levelPrice", description = "price level", type = String.class),
        @Info(name = "customerId", description = "id customer", type = Long.class)
})
@InfoOut(value = {
        @Info(name = "productList", description = "product list (productId, productCode, productName, productBalanceId, serialNumber, lotNumber, productYearMade, productExpiredDate, productBalanceStockId, baseUomId, baseUomCode, baseUomName, qty, currCode, grossSellPrice, flagTaxAmount, taxId, taxAmount, sellPrice, minSellPrice, flgStockService)", type = List.class) })
@ErrorList(errorKeys = {})
//@formatter:off
public class GetLookupProductForPointOfSales extends AbstractBusinessFunction implements BusinessFunction {
    private static final Logger log = LoggerFactory.getLogger(GetLookupProductForPointOfSales.class);

    @Autowired
    ProductBalanceStockDao productBalanceStockDao;

    @Override
    public String getDescription() {
        return "get lookup product for POS";
    }

    @SuppressWarnings("unchecked")
    @Override
    public Dto execute(Dto inputDto) throws Exception {
        // validasi parameter input
        ValidationUtil.valDtoContainsKey(inputDto, "tenantId");
        ValidationUtil.valDtoContainsKey(inputDto, "ouId");
        ValidationUtil.valDtoContainsKey(inputDto, "warehouseId");
        ValidationUtil.valDtoContainsKey(inputDto, "productCodeName");
        ValidationUtil.valDtoContainsKey(inputDto, "parentId");
        ValidationUtil.valDtoContainsKey(inputDto, "date");
        ValidationUtil.valDtoContainsKey(inputDto, "levelPrice");
        ValidationUtil.valDtoContainsKey(inputDto, "customerId");

        // create variable
        List<Object[]> result = new ArrayList<>();
        List<Object[]> resultQueryList = new ArrayList<>();
        Boolean isOuException = false;
        Boolean isLevelPriceException = false;
        String priceLevel = inputDto.getString("levelPrice");
        String priceLevelSpecial = GeneralConstants.EMPTY_VALUE;
        String priceLevelException = GeneralConstants.EMPTY_VALUE;
        String ouException = GeneralConstants.EMPTY_VALUE;
        String ouCode = GeneralConstants.EMPTY_VALUE;
        QueryBuilder builder = new QueryBuilder();

        // get param for OU exception, price level exception, price level special dan get ouCode
        builder = new QueryBuilder();
        builder .add(" SELECT f_get_value_system_config_by_param_code(:tenantId, :ouExceptionCode) AS ou_exception, ")
                .add("        f_get_value_system_config_by_param_code(:tenantId, :levelPriceExceptionCode) AS level_price_exception, ")
                .add("        f_get_value_system_config_by_param_code(:tenantId, :levelPriceSpecCode) AS level_price_special, ")
                .add("        f_get_ou_code(:ouId) AS ou_code ");

        Query query = productBalanceStockDao.createNativeQuery(builder.toString());
        query.setParameter("tenantId", inputDto.getLong("tenantId"));
        query.setParameter("ouExceptionCode", PosIntegrationConstantsForSasa.OU_EXCEPTION);
        query.setParameter("levelPriceExceptionCode", PosIntegrationConstantsForSasa.LEVEL_PRICE_EXCEPTION);
        query.setParameter("levelPriceSpecCode", PosIntegrationConstantsForSasa.LEVEL_PRICE_SPECIAL);
        query.setParameter("ouId", inputDto.getLong("ouId"));

        resultQueryList = query.getResultList();
        if(resultQueryList != null && resultQueryList.size() > 0){
            List<Dto> outputResultQueryListDto = DtoUtil.createDtoListFromArray(resultQueryList, "ouException", "priceLevelException",
                    "priceLevelSpecial", "ouCode");

            ouException = outputResultQueryListDto.get(0).getString("ouException");
            priceLevelException = outputResultQueryListDto.get(0).getString("priceLevelException");
            priceLevelSpecial = outputResultQueryListDto.get(0).getString("priceLevelSpecial");
            ouCode = outputResultQueryListDto.get(0).getString("ouCode");

        }

        //check if OU and levelPrice is exceptional
        isOuException = isKeyExistsInArrayString(ouException, ouCode);
        isLevelPriceException = isKeyExistsInArrayString(priceLevelException, priceLevel);

        if (isOuException && isLevelPriceException){
            priceLevelSpecial = priceLevelSpecial;
        }else {
            priceLevelSpecial = priceLevel;
        }

        builder = new QueryBuilder();
        builder.add(" SELECT a.product_id, a.product_code, a.product_name, COALESCE(b.product_balance_id, :EMPTYID) AS product_balance_id, COALESCE(b.serial_number, :EMPTY) AS serial_number, " )
                .add(" COALESCE(b.lot_number, :EMPTY) AS lot_number, COALESCE(b.product_year_made, :EMPTY) AS product_year_made, COALESCE(b.product_expired_date, :EMPTY) AS product_expired_date, ")
                .add(" COALESCE(c.product_balance_stock_id, :EMPTYID) AS product_balance_stock_id, a.base_uom_id, e.uom_code, e.uom_name, COALESCE(c.qty, 0) AS qty, ")
                .add(" COALESCE(j.curr_code, COALESCE(k.curr_code, :EMPTY)) AS curr_code, COALESCE(j.gross_sell_price, COALESCE(k.gross_sell_price, :PRICENOTSET)) AS gross_sell_price, " )
                .add(" COALESCE(j.flg_tax_amount, COALESCE(k.flg_tax_amount, :NO)) AS flg_tax_amount, COALESCE(j.tax_id, COALESCE(k.tax_id, :EMPTYID)) AS tax_id, ")
                .add(" COALESCE(j.tax_amount, COALESCE(k.tax_amount, :PRICENOTSET)) AS tax_amount, COALESCE(j.sell_price, COALESCE(k.sell_price, :PRICENOTSET)) AS sell_price, " )
                .add(" COALESCE(j.min_sell_price, COALESCE(k.min_sell_price, :PRICENOTSET)) AS min_sell_price, ")
                .add(" COALESCE(l.gross_sell_price, :PRICENOTSET) AS het_price, COALESCE(o.gross_sell_price, 0) AS last_sell_price, ")
                .add(" (CASE WHEN g.group_product_code = :SERVICE  THEN :NO ELSE :YES END) AS flg_stock,  ")
                .add(" COALESCE(n.whs_location_code, :EMPTY) AS whs_location_code  ")
                .add(" FROM ").add(Product.TABLE_NAME)
                .add(" a LEFT OUTER JOIN ").add(ProductBalance.TABLE_NAME)
                .add(" b ON a.tenant_id = b.tenant_id AND a.product_id = b.product_id ")
                .add(" LEFT OUTER JOIN ").add(ProductBalanceStock.TABLE_NAME)
                .add(" c ON b.tenant_id = c.tenant_id AND b.product_id = c.product_id AND b.product_balance_id = c.product_balance_id AND c.product_status = :defaultProductStatus AND c.warehouse_id = :warehouseId ")
                .add(" INNER JOIN ").add(OuStructure.TABLE_NAME)
                .add(" d ON d.ou_id = :ouId AND d.ou_bu_id = :parentId ")
                .add(" INNER JOIN ").add(Uom.TABLE_NAME)
                .add(" e ON e.tenant_id = a.tenant_id AND e.uom_id = a.base_uom_id ")
                .add(" INNER JOIN ").add(CtgrProduct.TABLE_NAME)
                .add(" f ON f.ctgr_product_id = a.ctgr_product_id ")
                .add(" INNER JOIN ").add(GroupProduct.TABLE_NAME)
                .add(" g ON g.group_product_id = f.group_product_id ")
                .add(" INNER JOIN ").add(OU.TABLE_NAME)
                .add(" h ON h.ou_id = d.ou_bu_id AND h.tenant_id = a.tenant_id ")
                .add(" INNER JOIN ").add(OUType.TABLE_NAME)
                .add(" i ON i.ou_type_id = h.ou_type_id AND i.tenant_id = a.tenant_id ")
                .add(" INNER JOIN ").add(SellPriceProductForSo.TABLE_NAME)
                .add(" l ON a.tenant_id = l.tenant_id AND l.ou_id = :parentId AND a.product_id = l.product_id AND :date BETWEEN l.date_from AND l.date_to AND l.active = :YES AND UPPER(l.price_level) = UPPER(:priceLevelHet) ")
                .add(" LEFT OUTER JOIN ").add(SellPriceProductForSo.TABLE_NAME)
                .add(" j ON a.tenant_id = j.tenant_id AND j.ou_id = :ouId AND a.product_id = j.product_id AND :date BETWEEN j.date_from AND j.date_to AND j.active = :YES AND UPPER(j.price_level) = UPPER(:priceLevelSpec) ")
                .add(" LEFT OUTER JOIN ").add(SellPriceProductForSo.TABLE_NAME)
                .add(" k ON a.tenant_id = k.tenant_id AND k.ou_id = :parentId AND a.product_id = k.product_id AND :date BETWEEN k.date_from AND k.date_to AND k.active = :YES AND UPPER(k.price_level) = UPPER(:priceLevel) ")
                .add(" LEFT OUTER JOIN ( ")
                    .add(" SELECT DISTINCT ON (B.product_id) B.product_id, A.doc_date, A.doc_no, A.tenant_id, A.ou_id, A.partner_id, B.gross_sell_price ")
                    .add(" FROM ").add(PointOfSales.TABLE_NAME).add(" A ")
                    .add(" INNER JOIN ").add(PointOfSalesItem.TABLE_NAME).add(" B ")
                    .add(" ON A.tenant_id = B.tenant_id AND A.process_no = B.process_no AND A.trx_pos_id = B.trx_pos_id ")
                    .add(" WHERE A.tenant_id = :tenantId ")
                    .add(" AND A.partner_id = :customerId ")
                    .add(" AND A.ou_id = :ouId ")
                    .add(" AND A.status = :statusSubmitted ")
                    .add(" ORDER BY B.product_id DESC, A.doc_date DESC, A.doc_no DESC ")
                .add(" ) o ON a.tenant_id = o.tenant_id AND a.product_id = o.product_id ")
                .add(" LEFT JOIN ").add(WhsLocationProduct.TABLE_NAME)
                .add(" m ON a.product_id = m.product_id AND a.tenant_id = m.tenant_id ")
                .add(" LEFT JOIN ").add(WhsLocation.TABLE_NAME)
                .add(" n on m.whs_location_id = n.whs_location_id AND a.tenant_id = n.tenant_id ")
                .add(" WHERE a.tenant_id = :tenantId AND a.active = :YES AND a.flg_sell = :YES ")
                .addIfNotEmpty(inputDto.getString("productCodeName"),
                        " AND ( " + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("productCodeName"),
                                "a.product_code"))
                .addIfNotEmpty(inputDto.getString("productCodeName"), " OR " + CriteriaHelper
                        .likeExpressionIgnoreCase(inputDto.getString("productCodeName"), "a.product_name") + " ) ")
                .add(" ORDER BY a.product_name, a.product_code ASC ")
                .add("");
        if (inputDto.getLong("limit") != null)
            builder.add(" LIMIT " + inputDto.getLong("limit"));
        if (inputDto.getLong("offset") != null)
            builder.add(" OFFSET " + inputDto.getLong("offset"));

        log.info("-------QUERY-------"+builder.toString());
        log.debug("-------QUERY-------"+builder.toString());

        Query q = productBalanceStockDao.createNativeQuery(builder.toString());
        q.setParameter("tenantId", inputDto.getLong("tenantId"));
        q.setParameter("defaultProductStatus", InventoryConstants.DEFAULT_PRODUCT_STATUS);
        q.setParameter("warehouseId", inputDto.getLong("warehouseId"));
        q.setParameter("ouId", inputDto.getLong("ouId"));
        q.setParameter("customerId", inputDto.getLong("customerId"));
        q.setParameter("YES", GeneralConstants.YES);
        q.setParameter("date", inputDto.getString("date"));
        q.setParameter("parentId", inputDto.getLong("parentId"));
        q.setParameter("EMPTY", GeneralConstants.EMPTY_VALUE);
        q.setParameter("NO", GeneralConstants.NO);
        q.setParameter("PRICENOTSET", new Double(-99));
        q.setParameter("EMPTYID", GeneralConstants.NULL_REF_VALUE_LONG);
        q.setParameter("SERVICE", PosIntegrationConstantsForSasa.GROUP_PRODUCT_SERVICE);
        q.setParameter("priceLevel", inputDto.getString("levelPrice"));
        q.setParameter("priceLevelHet", PosIntegrationConstantsForSasa.LEVEL_PRICE_HET);
        q.setParameter("priceLevelSpec", priceLevelSpecial);
        q.setParameter("statusSubmitted", PosIntegrationConstants.SUBMITTED_TRANSACTION);


        result = q.getResultList();
        return new Dto().putList("productList",
                DtoUtil.createDtoListFromArray(result, "productId", "productCode", "productName", "productBalanceId",
                        "serialNumber", "lotNumber", "productYearMade", "productExpiredDate", "productBalanceStockId",
                        "baseUomId", "baseUomCode", "baseUomName", "qty", "currCode", "grossSellPrice", "flagTaxAmount",
                        "taxId", "taxAmount", "sellPrice", "minSellPrice", "hetPrice", "lastSellPrice", "flgStockService", "whsLocationCode"));
    }

    private Boolean isKeyExistsInArrayString(String arrayString, String keySearch) {
        Boolean isExists = false;
        String[] arrOfStr = arrayString.split(",");

        for(String comparator : arrOfStr){
            if (keySearch.equals(comparator.trim())){
                isExists = true;
                break;
            }

        }
        return isExists;
    }
}