SHARE
TWEET

Untitled

a guest Feb 26th, 2020 82 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. package org.jleaf.erp.sls.bo.deliveryorder;
  2.  
  3. import java.util.List;
  4.  
  5. import javax.persistence.Query;
  6.  
  7. import org.jleaf.core.AbstractBusinessFunction;
  8. import org.jleaf.core.BusinessFunction;
  9. import org.jleaf.core.Dto;
  10. import org.jleaf.core.GeneralConstants;
  11. import org.jleaf.core.annotation.Info;
  12. import org.jleaf.core.annotation.InfoIn;
  13. import org.jleaf.core.annotation.InfoOut;
  14. import org.jleaf.core.dao.QueryBuilder;
  15. import org.jleaf.erp.inv.entity.ProductBalanceStock;
  16. import org.jleaf.erp.master.entity.Product;
  17. import org.jleaf.erp.master.entity.ProductStatus;
  18. import org.jleaf.erp.sls.SalesConstants;
  19. import org.jleaf.erp.sls.dao.SalesOrderBalanceItemDao;
  20. import org.jleaf.erp.sls.entity.DraftDo;
  21. import org.jleaf.erp.sls.entity.DraftDoItem;
  22. import org.jleaf.erp.sls.entity.SalesOrder;
  23. import org.jleaf.erp.sls.entity.SalesOrderBalanceItem;
  24. import org.jleaf.erp.sls.entity.SalesOrderItem;
  25. import org.jleaf.util.DtoUtil;
  26. import org.jleaf.util.ValidationUtil;
  27. import org.springframework.beans.factory.annotation.Autowired;
  28. import org.springframework.stereotype.Service;
  29. /**
  30.  *
  31.  * @author Ivan, 21 Jan 2020
  32.  */
  33.  
  34. //@formatter:off
  35. @Service("getOutstandingSalesOrderBalanceItemListForDeliveryOrder")
  36. @InfoIn(value = {
  37.         @Info(name = "tenantId", description = "tenant id", type = Long.class),
  38.         @Info(name = "ouId", description = "ou id", type = Long.class),
  39.         @Info(name = "soId", description = "so id", type = Long.class),
  40.         @Info(name = "warehouseId", description = "warehouse id", type = Long.class),
  41. //      @Info(name = "partnerShipAddressId", description = "partner ship address id", type = Long.class),
  42.         @Info(name = "draftDoId", description = "draft do id", type = Long.class),
  43.         @Info(name = "userLoginId", description = "user login id", type = Long.class),
  44.         @Info(name = "roleLoginId", description = "role login id", type = Long.class)
  45. })
  46. @InfoOut(value = {
  47.         @Info(name = "itemList", description = "outstanding sales order balance item list for delivery order (soItemId, soItemLineNo, productId, productCode, productName, subCtgrProductId, subCtgrProductCode, subCtgrProductName, ctgrProductId, ctgrProductCode, ctgrProductName, brandId, brandCode, brandName, balanceStockQty, balanceStockBaseUomId, balanceStockBaseUomCode, balanceStockBaseUomName, outstandingQtyInt, outstandingQtyBaseUomId, outstandingQtyBaseUomCode, outstandingQtyBaseUomName, flagSerialNumber, productStatus, productStatusDesc)", type = Long.class)
  48. })
  49. //@formatter:on
  50. public class GetOutstandingSalesOrderBalanceItemListForDeliveryOrderForSasa extends AbstractBusinessFunction implements BusinessFunction {
  51.  
  52.     @Autowired
  53.     SalesOrderBalanceItemDao salesOrderBalanceItemDao;
  54.  
  55.     public String getDescription() {
  56.         return "get outstanding sales order balance item list for delivery order";
  57.     }
  58.  
  59.     @SuppressWarnings("unchecked")
  60.     public Dto execute(Dto inputDto) throws Exception {
  61.         // validate inputDto have key in @InfoIn
  62.         ValidationUtil.valDtoContainsKey(inputDto, "tenantId");
  63.         ValidationUtil.valDtoContainsKey(inputDto, "ouId");
  64.         ValidationUtil.valDtoContainsKey(inputDto, "soId");
  65.         ValidationUtil.valDtoContainsKey(inputDto, "warehouseId");
  66.         ValidationUtil.valDtoContainsKey(inputDto, "draftDoId");
  67.         ValidationUtil.valDtoContainsKey(inputDto, "userLoginId");
  68.         ValidationUtil.valDtoContainsKey(inputDto, "roleLoginId");
  69.  
  70.         // prepare list for query result
  71.         List<Object[]> result = null;
  72.  
  73.         //@formatter:off
  74.         // create native sql
  75.        
  76.         QueryBuilder builder = new QueryBuilder();
  77.         builder.add(" WITH filtered_stock AS( ")
  78.                 .add("      SELECT B.draft_do_item_id, C.product_status, D.flg_sell, SUM(COALESCE(C.qty, 0)) as balance_stock_qty, C.base_uom_id ")
  79.                 .add("      FROM ").add(DraftDo.TABLE_NAME).add(" A ")
  80.                 .add("              INNER JOIN ").add(DraftDoItem.TABLE_NAME).add(" B ON A.draft_do_id = B.draft_do_id AND A.tenant_id = B.tenant_id ")
  81.                 .add("              INNER JOIN ").add(ProductBalanceStock.TABLE_NAME).add(" C ON B.product_id = C.product_id AND A.tenant_id = C.tenant_id ")
  82.                 .add("              INNER JOIN ").add(ProductStatus.TABLE_NAME).add(" D ON C.product_status = D.product_status_code AND A.tenant_id = D.tenant_id ")
  83.                 .add("      WHERE A.draft_do_id = :draftDoId AND C.warehouse_id = :warehouseId ")
  84.                 .add("      GROUP BY B.draft_do_item_id, C.product_id, C.product_status, D.flg_sell, C.base_uom_id ")
  85.                 .add(" ) ")
  86.                 .add(" SELECT D.so_item_id, B.line_no AS soItemLineNo, B.product_id, f_get_product_code(B.product_id) AS product_code, f_get_product_name(B.product_id) AS product_name, ")
  87.                 .add("      f_get_ctgr_product_code(G.ctgr_product_id) AS ctgr_product_code, f_get_ctgr_product_name(G.ctgr_product_id) AS ctgr_product_name, ")
  88.                 .add("      f_get_sub_ctgr_product_code(G.sub_ctgr_product_id) AS sub_ctgr_product_code, f_get_sub_ctgr_product_name(G.sub_ctgr_product_id) AS sub_ctgr_product_name, ")
  89.                 .add("      f_get_brand_code(G.brand_id) AS brand_code, f_get_brand_name(G.brand_id) AS brand_name, ")
  90.                 .add("      F.balance_stock_qty AS balanceStockQty, COALESCE(F.base_uom_id, G.base_uom_id) AS balanceStockBaseUomId, ")
  91.                 .add("      f_get_uom_code(G.base_uom_id) AS balanceStockBaseUomCode, f_get_uom_name(G.base_uom_id) AS balanceStockBaseUomName, ")
  92.                 .add("      E.qty_so_int - E.qty_cancel_int + E.qty_add_int - E.qty_dlv_int AS outstandingQtyInt, ")
  93.                 .add("      f_get_uom_code(E.base_uom_id) AS outstandingQtyBaseUomCode, f_get_uom_name(E.base_uom_id) AS outstandingQtyBaseUomName, ")
  94.                 .add("      f_get_flg_serial_number(B.product_id) AS flg_serial_number, ")
  95.                 .add("      COALESCE(F.product_status, :EMPTY) AS productStatusCode, COALESCE(F.product_status, :EMPTY) AS productStatusDesc, B.qty_picking AS qty_dlv_int ")
  96.                 .add(" FROM ").add(DraftDo.TABLE_NAME).add(" A ")
  97.                 .add("      INNER JOIN ").add(DraftDoItem.TABLE_NAME).add(" B ON A.draft_do_id = B.draft_do_id AND A.tenant_id = B.tenant_id ")
  98.                 .add("      INNER JOIN ").add(SalesOrder.TABLE_NAME).add(" C ON A.ref_id = C.so_id AND A.tenant_id = C.tenant_id ")
  99.                 .add("      INNER JOIN ").add(SalesOrderItem.TABLE_NAME).add(" D ON B.ref_id = D.so_item_id AND C.tenant_id = D.tenant_id ")
  100.                 .add("      INNER JOIN ").add(SalesOrderBalanceItem.TABLE_NAME).add(" E ON D.so_item_id = E.so_item_id AND D.tenant_id = E.tenant_id ")
  101.                 .add("      LEFT OUTER JOIN filtered_stock F ON B.draft_do_item_id = F.draft_do_item_id ")
  102.                 .add("      INNER JOIN ").add(Product.TABLE_NAME).add(" G ON G.product_id = B.product_id AND B.tenant_id = G.tenant_id ")
  103.                 .add(" WHERE F.flg_sell = :YES AND f_authorize_user_role_policy_product(:tenantId,:userLoginId,:roleLoginId,B.product_id) = :ONE ")
  104.                 .add("      AND E.status_item = :RELEASED ")
  105.                 .add("      AND A.tenant_id = :tenantId ")
  106.                 .add("      AND A.ou_id = :ouId ")
  107.                 .add("      AND A.draft_do_id = :draftDoId ")
  108.                 .add(" GROUP BY D.so_item_id, B.line_no, B.product_id, G.ctgr_product_id, G.sub_ctgr_product_id, G.brand_id, F.balance_stock_qty, ")
  109.                 .add("      F.base_uom_id, G.base_uom_id, E.qty_so_int, E.qty_cancel_int, E.qty_add_int, E.qty_dlv_int, E.base_uom_id, F.product_status, B.qty_picking ")
  110.                 .add(" ORDER BY B.line_no ");
  111.         //@formatter:on
  112.  
  113.         // create native query
  114.         Query q = salesOrderBalanceItemDao.createNativeQuery(builder.toString());
  115.         q.setParameter("tenantId", inputDto.getLong("tenantId"));
  116.         q.setParameter("ouId", inputDto.getLong("ouId"));
  117.         //q.setParameter("soId", inputDto.getLong("soId"));
  118. //      q.setParameter("partnerShipAddressId", inputDto.getLong("partnerShipAddressId"));
  119.         q.setParameter("warehouseId", inputDto.getLong("warehouseId"));
  120.         q.setParameter("draftDoId", inputDto.getLong("draftDoId"));
  121.         q.setParameter("userLoginId", inputDto.getLong("userLoginId"));
  122.         q.setParameter("roleLoginId", inputDto.getLong("roleLoginId"));
  123.         q.setParameter("RELEASED", SalesConstants.RELEASED_TRANSACTION);
  124.         q.setParameter("YES", GeneralConstants.YES);
  125.         q.setParameter("ONE", 1L);
  126.         q.setParameter("EMPTY", GeneralConstants.EMPTY_VALUE);
  127.  
  128.         // get query result
  129.         result = q.getResultList();
  130.  
  131.         // return as Dto using DtoUtil.createDtoListFromArray
  132.         return new Dto().putList("itemList", DtoUtil.createDtoListFromArray(result,
  133.                 "soItemId", "soItemLineNo", "productId", "productCode", "productName",
  134.                 "ctgrProductCode", "ctgrProductName", "subCtgrProductCode", "subCtgrProductName",
  135.                 "brandCode", "brandName", "balanceStockQty", "balanceStockBaseUomId",
  136.                 "balanceStockBaseUomCode", "balanceStockBaseUomName",
  137.                 "outstandingQtyInt", "outstandingQtyBaseUomCode", "outstandingQtyBaseUomName", "flagSerialNumber", "productStatus", "productStatusDesc", "qtyDlvInt"));
  138.     }
  139. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top