abirama62

lookup_product_pos

Sep 24th, 2020
875
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. package org.jleaf.erp.pos_intgr.bo.stockproduct;
  2.  
  3. import java.util.ArrayList;
  4. import java.util.List;
  5.  
  6. import javax.persistence.Query;
  7.  
  8. import org.jleaf.common.entity.OU;
  9. import org.jleaf.common.entity.OUType;
  10. import org.jleaf.core.*;
  11. import org.jleaf.core.annotation.ErrorList;
  12. import org.jleaf.core.annotation.Info;
  13. import org.jleaf.core.annotation.InfoIn;
  14. import org.jleaf.core.annotation.InfoOut;
  15. import org.jleaf.core.dao.CriteriaHelper;
  16. import org.jleaf.core.dao.QueryBuilder;
  17. import org.jleaf.erp.inv.InventoryConstants;
  18. import org.jleaf.erp.inv.dao.ProductBalanceStockDao;
  19. import org.jleaf.erp.inv.entity.ProductBalance;
  20. import org.jleaf.erp.inv.entity.ProductBalanceStock;
  21. import org.jleaf.erp.master.entity.*;
  22. import org.jleaf.erp.pos_intgr.PosIntegrationConstants;
  23. import org.jleaf.erp.pos_intgr.PosIntegrationConstantsForSasa;
  24. import org.jleaf.erp.pos_intgr.entity.PointOfSales;
  25. import org.jleaf.erp.pos_intgr.entity.PointOfSalesItem;
  26. import org.jleaf.util.DtoUtil;
  27. import org.jleaf.util.ValidationUtil;
  28. import org.slf4j.Logger;
  29. import org.slf4j.LoggerFactory;
  30. import org.springframework.beans.factory.annotation.Autowired;
  31. import org.springframework.beans.factory.annotation.Qualifier;
  32. import org.springframework.stereotype.Service;
  33.  
  34. //@formatter:off
  35. @Service
  36. @InfoIn(value = { @Info(name = "tenantId", description = "tenant id", type = Long.class),
  37.         @Info(name = "ouId", description = "ou id", type = Long.class),
  38.         @Info(name = "warehouseId", description = "warehouse id", type = Long.class),
  39.         @Info(name = "ctgrProductId", description = "category product id", type = Long.class),
  40.         @Info(name = "subCtgrProductId", description = "sub category product id", type = Long.class),
  41.         @Info(name = "brandId", description = "brand id", type = Long.class),
  42.         @Info(name = "productCodeName", description = "product code/name", type = String.class),
  43.         @Info(name = "serialNumber", description = "serial number", type = String.class),
  44.         @Info(name = "parentId", description = "ou parent id", type = Long.class),
  45.         @Info(name = "date", description = "date", type = String.class),
  46.         @Info(name = "limit", description = "offset", type = Long.class, required = false),
  47.         @Info(name = "offset", description = "limit", type = Long.class, required = false),
  48.         @Info(name = "userLoginId", description = "user login id", type = Long.class),
  49.         @Info(name = "roleLoginId", description = "role login id", type = Long.class),
  50.         @Info(name = "levelPrice", description = "price level", type = String.class),
  51.         @Info(name = "customerId", description = "id customer", type = Long.class)
  52. })
  53. @InfoOut(value = {
  54.         @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) })
  55. @ErrorList(errorKeys = {})
  56. //@formatter:off
  57. public class GetLookupProductForPointOfSales extends AbstractBusinessFunction implements BusinessFunction {
  58.     private static final Logger log = LoggerFactory.getLogger(GetLookupProductForPointOfSales.class);
  59.  
  60.     @Autowired
  61.     ProductBalanceStockDao productBalanceStockDao;
  62.  
  63.     @Override
  64.     public String getDescription() {
  65.         return "get lookup product for POS";
  66.     }
  67.  
  68.     @SuppressWarnings("unchecked")
  69.     @Override
  70.     public Dto execute(Dto inputDto) throws Exception {
  71.         // validasi parameter input
  72.         ValidationUtil.valDtoContainsKey(inputDto, "tenantId");
  73.         ValidationUtil.valDtoContainsKey(inputDto, "ouId");
  74.         ValidationUtil.valDtoContainsKey(inputDto, "warehouseId");
  75.         ValidationUtil.valDtoContainsKey(inputDto, "productCodeName");
  76.         ValidationUtil.valDtoContainsKey(inputDto, "parentId");
  77.         ValidationUtil.valDtoContainsKey(inputDto, "date");
  78.         ValidationUtil.valDtoContainsKey(inputDto, "levelPrice");
  79.         ValidationUtil.valDtoContainsKey(inputDto, "customerId");
  80.  
  81.         // create variable
  82.         List<Object[]> result = new ArrayList<>();
  83.         List<Object[]> resultQueryList = new ArrayList<>();
  84.         Boolean isOuException = false;
  85.         Boolean isLevelPriceException = false;
  86.         String priceLevel = inputDto.getString("levelPrice");
  87.         String priceLevelSpecial = GeneralConstants.EMPTY_VALUE;
  88.         String priceLevelException = GeneralConstants.EMPTY_VALUE;
  89.         String ouException = GeneralConstants.EMPTY_VALUE;
  90.         String ouCode = GeneralConstants.EMPTY_VALUE;
  91.         QueryBuilder builder = new QueryBuilder();
  92.  
  93.         // get param for OU exception, price level exception, price level special dan get ouCode
  94.         builder = new QueryBuilder();
  95.         builder .add(" SELECT f_get_value_system_config_by_param_code(:tenantId, :ouExceptionCode) AS ou_exception, ")
  96.                 .add("        f_get_value_system_config_by_param_code(:tenantId, :levelPriceExceptionCode) AS level_price_exception, ")
  97.                 .add("        f_get_value_system_config_by_param_code(:tenantId, :levelPriceSpecCode) AS level_price_special, ")
  98.                 .add("        f_get_ou_code(:ouId) AS ou_code ");
  99.  
  100.         Query query = productBalanceStockDao.createNativeQuery(builder.toString());
  101.         query.setParameter("tenantId", inputDto.getLong("tenantId"));
  102.         query.setParameter("ouExceptionCode", PosIntegrationConstantsForSasa.OU_EXCEPTION);
  103.         query.setParameter("levelPriceExceptionCode", PosIntegrationConstantsForSasa.LEVEL_PRICE_EXCEPTION);
  104.         query.setParameter("levelPriceSpecCode", PosIntegrationConstantsForSasa.LEVEL_PRICE_SPECIAL);
  105.         query.setParameter("ouId", inputDto.getLong("ouId"));
  106.  
  107.         resultQueryList = query.getResultList();
  108.         if(resultQueryList != null && resultQueryList.size() > 0){
  109.             List<Dto> outputResultQueryListDto = DtoUtil.createDtoListFromArray(resultQueryList, "ouException", "priceLevelException",
  110.                     "priceLevelSpecial", "ouCode");
  111.  
  112.             ouException = outputResultQueryListDto.get(0).getString("ouException");
  113.             priceLevelException = outputResultQueryListDto.get(0).getString("priceLevelException");
  114.             priceLevelSpecial = outputResultQueryListDto.get(0).getString("priceLevelSpecial");
  115.             ouCode = outputResultQueryListDto.get(0).getString("ouCode");
  116.  
  117.         }
  118.  
  119.         //check if OU and levelPrice is exceptional
  120.         isOuException = isKeyExistsInArrayString(ouException, ouCode);
  121.         isLevelPriceException = isKeyExistsInArrayString(priceLevelException, priceLevel);
  122.  
  123.         if (isOuException && isLevelPriceException){
  124.             priceLevelSpecial = priceLevelSpecial;
  125.         }else {
  126.             priceLevelSpecial = priceLevel;
  127.         }
  128.  
  129.         builder = new QueryBuilder();
  130.         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, " )
  131.                 .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, ")
  132.                 .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, ")
  133.                 .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, " )
  134.                 .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, ")
  135.                 .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, " )
  136.                 .add(" COALESCE(j.min_sell_price, COALESCE(k.min_sell_price, :PRICENOTSET)) AS min_sell_price, ")
  137.                 .add(" COALESCE(l.gross_sell_price, :PRICENOTSET) AS het_price, COALESCE(o.gross_sell_price, 0) AS last_sell_price, ")
  138.                 .add(" (CASE WHEN g.group_product_code = :SERVICE  THEN :NO ELSE :YES END) AS flg_stock,  ")
  139.                 .add(" COALESCE(n.whs_location_code, :EMPTY) AS whs_location_code  ")
  140.                 .add(" FROM ").add(Product.TABLE_NAME)
  141.                 .add(" a LEFT OUTER JOIN ").add(ProductBalance.TABLE_NAME)
  142.                 .add(" b ON a.tenant_id = b.tenant_id AND a.product_id = b.product_id ")
  143.                 .add(" LEFT OUTER JOIN ").add(ProductBalanceStock.TABLE_NAME)
  144.                 .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 ")
  145.                 .add(" INNER JOIN ").add(OuStructure.TABLE_NAME)
  146.                 .add(" d ON d.ou_id = :ouId AND d.ou_bu_id = :parentId ")
  147.                 .add(" INNER JOIN ").add(Uom.TABLE_NAME)
  148.                 .add(" e ON e.tenant_id = a.tenant_id AND e.uom_id = a.base_uom_id ")
  149.                 .add(" INNER JOIN ").add(CtgrProduct.TABLE_NAME)
  150.                 .add(" f ON f.ctgr_product_id = a.ctgr_product_id ")
  151.                 .add(" INNER JOIN ").add(GroupProduct.TABLE_NAME)
  152.                 .add(" g ON g.group_product_id = f.group_product_id ")
  153.                 .add(" INNER JOIN ").add(OU.TABLE_NAME)
  154.                 .add(" h ON h.ou_id = d.ou_bu_id AND h.tenant_id = a.tenant_id ")
  155.                 .add(" INNER JOIN ").add(OUType.TABLE_NAME)
  156.                 .add(" i ON i.ou_type_id = h.ou_type_id AND i.tenant_id = a.tenant_id ")
  157.                 .add(" INNER JOIN ").add(SellPriceProductForSo.TABLE_NAME)
  158.                 .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) ")
  159.                 .add(" LEFT OUTER JOIN ").add(SellPriceProductForSo.TABLE_NAME)
  160.                 .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) ")
  161.                 .add(" LEFT OUTER JOIN ").add(SellPriceProductForSo.TABLE_NAME)
  162.                 .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) ")
  163.                 .add(" LEFT OUTER JOIN ( ")
  164.                     .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 ")
  165.                     .add(" FROM ").add(PointOfSales.TABLE_NAME).add(" A ")
  166.                     .add(" INNER JOIN ").add(PointOfSalesItem.TABLE_NAME).add(" B ")
  167.                     .add(" ON A.tenant_id = B.tenant_id AND A.process_no = B.process_no AND A.trx_pos_id = B.trx_pos_id ")
  168.                     .add(" WHERE A.tenant_id = :tenantId ")
  169.                     .add(" AND A.partner_id = :customerId ")
  170.                     .add(" AND A.ou_id = :ouId ")
  171.                     .add(" AND A.status = :statusSubmitted ")
  172.                     .add(" ORDER BY B.product_id DESC, A.doc_date DESC, A.doc_no DESC ")
  173.                 .add(" ) o ON a.tenant_id = o.tenant_id AND a.product_id = o.product_id ")
  174.                 .add(" LEFT JOIN ").add(WhsLocationProduct.TABLE_NAME)
  175.                 .add(" m ON a.product_id = m.product_id AND a.tenant_id = m.tenant_id ")
  176.                 .add(" LEFT JOIN ").add(WhsLocation.TABLE_NAME)
  177.                 .add(" n on m.whs_location_id = n.whs_location_id AND a.tenant_id = n.tenant_id ")
  178.                 .add(" WHERE a.tenant_id = :tenantId AND a.active = :YES AND a.flg_sell = :YES ")
  179.                 .addIfNotEmpty(inputDto.getString("productCodeName"),
  180.                         " AND ( " + CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("productCodeName"),
  181.                                 "a.product_code"))
  182.                 .addIfNotEmpty(inputDto.getString("productCodeName"), " OR " + CriteriaHelper
  183.                         .likeExpressionIgnoreCase(inputDto.getString("productCodeName"), "a.product_name") + " ) ")
  184.                 .add(" ORDER BY a.product_name, a.product_code ASC ")
  185.                 .add("");
  186.         if (inputDto.getLong("limit") != null)
  187.             builder.add(" LIMIT " + inputDto.getLong("limit"));
  188.         if (inputDto.getLong("offset") != null)
  189.             builder.add(" OFFSET " + inputDto.getLong("offset"));
  190.  
  191.         log.info("-------QUERY-------"+builder.toString());
  192.         log.debug("-------QUERY-------"+builder.toString());
  193.  
  194.         Query q = productBalanceStockDao.createNativeQuery(builder.toString());
  195.         q.setParameter("tenantId", inputDto.getLong("tenantId"));
  196.         q.setParameter("defaultProductStatus", InventoryConstants.DEFAULT_PRODUCT_STATUS);
  197.         q.setParameter("warehouseId", inputDto.getLong("warehouseId"));
  198.         q.setParameter("ouId", inputDto.getLong("ouId"));
  199.         q.setParameter("customerId", inputDto.getLong("customerId"));
  200.         q.setParameter("YES", GeneralConstants.YES);
  201.         q.setParameter("date", inputDto.getString("date"));
  202.         q.setParameter("parentId", inputDto.getLong("parentId"));
  203.         q.setParameter("EMPTY", GeneralConstants.EMPTY_VALUE);
  204.         q.setParameter("NO", GeneralConstants.NO);
  205.         q.setParameter("PRICENOTSET", new Double(-99));
  206.         q.setParameter("EMPTYID", GeneralConstants.NULL_REF_VALUE_LONG);
  207.         q.setParameter("SERVICE", PosIntegrationConstantsForSasa.GROUP_PRODUCT_SERVICE);
  208.         q.setParameter("priceLevel", inputDto.getString("levelPrice"));
  209.         q.setParameter("priceLevelHet", PosIntegrationConstantsForSasa.LEVEL_PRICE_HET);
  210.         q.setParameter("priceLevelSpec", priceLevelSpecial);
  211.         q.setParameter("statusSubmitted", PosIntegrationConstants.SUBMITTED_TRANSACTION);
  212.  
  213.  
  214.         result = q.getResultList();
  215.         return new Dto().putList("productList",
  216.                 DtoUtil.createDtoListFromArray(result, "productId", "productCode", "productName", "productBalanceId",
  217.                         "serialNumber", "lotNumber", "productYearMade", "productExpiredDate", "productBalanceStockId",
  218.                         "baseUomId", "baseUomCode", "baseUomName", "qty", "currCode", "grossSellPrice", "flagTaxAmount",
  219.                         "taxId", "taxAmount", "sellPrice", "minSellPrice", "hetPrice", "lastSellPrice", "flgStockService", "whsLocationCode"));
  220.     }
  221.  
  222.     private Boolean isKeyExistsInArrayString(String arrayString, String keySearch) {
  223.         Boolean isExists = false;
  224.         String[] arrOfStr = arrayString.split(",");
  225.  
  226.         for(String comparator : arrOfStr){
  227.             if (keySearch.equals(comparator.trim())){
  228.                 isExists = true;
  229.                 break;
  230.             }
  231.  
  232.         }
  233.         return isExists;
  234.     }
  235. }
  236.  
RAW Paste Data