API tools faq
paste
Advertisement
henikseptiana15

BF GetSalesOrderListforInquirySo

henikseptiana15
Apr 27th, 2021
1,225
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 13.41 KB | None | 0 0
raw download clone embed print report
  1. package org.jleaf.erp.sls.bo.inquiry;
  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.CoreException;
  10. import org.jleaf.core.CoreExceptionConstants;
  11. import org.jleaf.core.Dto;
  12. import org.jleaf.core.GeneralConstants;
  13. import org.jleaf.core.annotation.ErrorList;
  14. import org.jleaf.core.annotation.Info;
  15. import org.jleaf.core.annotation.InfoIn;
  16. import org.jleaf.core.annotation.InfoOut;
  17. import org.jleaf.core.dao.CriteriaHelper;
  18. import org.jleaf.core.dao.QueryBuilder;
  19. import org.jleaf.erp.inv.entity.DeliveryOrderReceipt;
  20. import org.jleaf.erp.inv.entity.LogBook;
  21. import org.jleaf.erp.master.entity.BrandExt;
  22. import org.jleaf.erp.sls.SalesConstantsForKtmt;
  23. import org.jleaf.erp.sls.dao.SoStatusDao;
  24. import org.jleaf.erp.sls.entity.DeliveryOrder;
  25. import org.jleaf.erp.sls.entity.SalesOrder;
  26. import org.jleaf.erp.sls.entity.SoBrandItem;
  27. import org.jleaf.erp.sls.entity.SoInfo;
  28. import org.jleaf.erp.sls.entity.SoStatus;
  29. import org.jleaf.util.DtoUtil;
  30. import org.springframework.beans.factory.annotation.Autowired;
  31. import org.springframework.stereotype.Service;
  32.  
  33. /**
  34.  *
  35.  * @author Agik, May 31, 2016
  36.  *
  37.  * Get SO list for Inquiry SO
  38.  *
  39.  * @modify Adrian
  40.  * Dec 29, 2016
  41.  *
  42.  * Menampilkan total qty do dan flag do receipt
  43.  * Menambahkan infoIn flgDoReceipt
  44.  */
  45.  
  46. //@formatter:off
  47. @Service
  48. @InfoIn(value = {
  49.         @Info(name = "tenantId", description = "tenant id", type = Long.class),
  50.         @Info(name = "ouId", description = "ou id", type = Long.class),
  51.         @Info(name = "periodStart", description = "period start", type = String.class),
  52.         @Info(name = "periodEnd", description = "period end", type = String.class),
  53.         @Info(name = "customer", description = "customer", type = String.class),
  54.         @Info(name = "soNo", description = "SO No", type = String.class),
  55.         @Info(name = "flgTypeSo", description = "Flg Type So", type = String.class),
  56.         @Info(name = "statusSo", description = "status SO", type = String.class),
  57.         @Info(name = "statusSettlement", description = "status Settlement", type = String.class),
  58.         @Info(name = "statusPrint", description = "status Print", type = String.class),
  59.         @Info(name = "statusScan", description = "status Scan", type = String.class),
  60.         @Info(name = "statusExpedition", description = "status Expedition", type = String.class),
  61.         @Info(name = "statusInvoice", description = "status Invoice", type = String.class),
  62.         @Info(name = "flgAutoSettle", description = "flg auto settlement", type = String.class),
  63.         @Info(name = "ctgrProductId", description = "ctgr Product Id", type = Long.class),
  64.         @Info(name = "groupBrand", description = "group Brand", type = String.class),
  65.         @Info(name = "brandId", description = "brandId", type = Long.class),
  66.         @Info(name = "salesman", description = "salesman", type = String.class),
  67.         @Info(name = "custDocNo", description = "cust Doc No", type = String.class),
  68.         @Info(name = "offset", description = "offset", type=Long.class),
  69.         @Info(name = "limit", description = "limit", type=Long.class),
  70.         @Info(name = "doDocNo", description = "DO Doc No", type = String.class),
  71.         @Info(name = "flgDoReceipt", description = "Flag DO Receipt", type = String.class) 
  72. })
  73. @InfoOut(value = {
  74.         @Info(name = "soList", description = "list of SO", type = List.class)
  75. })
  76. @ErrorList(errorKeys = {
  77. })
  78. //@formatter:on
  79. public class GetSalesOrderListforInquirySo extends AbstractBusinessFunction implements BusinessFunction {
  80.    
  81.     @Autowired
  82.     private SoStatusDao soStatusDao;
  83.  
  84.     @Override
  85.     public String getDescription() {
  86.         return "Get SO list for Inquiry SO";
  87.     }
  88.  
  89.     @SuppressWarnings("unchecked")
  90.     @Override
  91.     public Dto execute(Dto inputDto) throws Exception {
  92.        
  93.         if (inputDto == null) {
  94.             throw new CoreException(CoreExceptionConstants.DTO_CANNOT_NULL);
  95.         }
  96.        
  97.         Long brandId = inputDto.getLong("brandId");
  98.        
  99.         List<Object[]> result = null;
  100.  
  101.         //@formatter:off
  102.         StringBuilder filterCustomer = new StringBuilder();
  103.         filterCustomer.append(" AND ( ")
  104.                         .append(CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("customer"), "f_get_partner_code(A.partner_id)"))
  105.                         .append(" OR ")
  106.                         .append(CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("customer"), "f_get_partner_name(A.partner_id)"))
  107.                         .append(" ) ");
  108.        
  109.         StringBuilder filterSoNo = new StringBuilder();
  110.         filterSoNo.append(" AND ")
  111.                         .append(CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("soNo"), "A.doc_no"));
  112.        
  113.         StringBuilder filterSalesman = new StringBuilder();
  114.         filterSalesman.append(" AND ( ")
  115.                         .append(CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("salesman"), "f_get_partner_code(B.salesman_id)"))
  116.                         .append(" OR ")
  117.                         .append(CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("salesman"), "f_get_partner_name(B.salesman_id)"))
  118.                         .append(" ) ");
  119.        
  120.         StringBuilder filterExtDocNo = new StringBuilder();
  121.         filterExtDocNo.append(" AND ")
  122.                         .append(CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("custDocNo"), "B.ext_doc_no"));
  123.        
  124.         /*
  125.         StringBuilder filterBrand = new StringBuilder();
  126.         filterBrand.append(" AND ")
  127.                         .append(CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("brand"), "G.brand"));
  128.         */
  129.         StringBuilder filterDoDocNo = new StringBuilder();
  130.         filterDoDocNo.append(" AND ")
  131.                         .append(CriteriaHelper.likeExpressionIgnoreCase(inputDto.getString("doDocNo"), "I.doc_no"));
  132.        
  133.         QueryBuilder builder = new QueryBuilder();
  134.         builder.add(" WITH so_brand AS ( ")
  135.                .add("   SELECT A.so_id, B.group_brand, string_agg(f_get_brand_code(A.brand_id), ', ') AS brand ")
  136.                .add("   FROM ").add(SoBrandItem.TABLE_NAME).add(" A ")
  137.                .add("   INNER JOIN ").add(BrandExt.TABLE_NAME).add(" B ON A.brand_id = B.brand_id ")
  138.                .addIfNotEquals(brandId, GeneralConstants.NULL_REF_VALUE_LONG, " WHERE A.brand_id = :brandId ")
  139.                .addIfNotEmpty(inputDto.getString("groupBrand"), " AND B.group_brand = :groupBrand ")
  140.                .add("   GROUP BY A.so_id, B.group_brand ")
  141.                .add(" ) ")
  142.                .add("SELECT A.so_id, A.tenant_id, A.ou_id, B.doc_no, B.doc_date, B.partner_id, f_get_partner_code(B.partner_id) AS partner_code, ")
  143.                .add(" f_get_partner_name(B.partner_id) AS partner_name, B.remark, f_get_total_brand_amount_include_tax_sob_by_so_id(A.so_id) AS so_amount, ")
  144.                .add(" f_get_role_name(D.current_role_id) AS current_role, B.status_doc, A.status_settlement, A.so_settlement_id, A.status_picking, ")
  145.                .add(" A.status_do, A.do_id, A.status_log_book, A.log_book_id, A.status_invoice, A.invoice_id, E.flg_auto_settle, ")
  146.                .add(" G.brand AS brand_code, G.group_brand, B.ext_doc_no, ")
  147.                .add(" f_get_partner_code(B.salesman_id) AS salesman_code, f_get_partner_name(B.salesman_id) AS salesman_name, A.version AS version_so_status, ")
  148.                .add(" f_get_status_cetak_report(A.so_id, B.doc_type_id, '").add(SalesConstantsForKtmt.GROUP_PRINT_FORM_DO).add("' ) AS status_print_do, ")
  149.                .add(" COALESCE(H.doc_no, '-') AS log_book_no, COALESCE(H.doc_date, '-') AS log_book_date, ")
  150.                .add(" CASE WHEN (E.flg_type_so = 'REG')  ")
  151.                .add("           THEN 'REGULAR' ")
  152.                .add("       WHEN (E.flg_type_so = 'TRL')  ")
  153.                .add("           THEN 'TRIAL' ")
  154.                .add("       WHEN (E.flg_type_so = 'FOC')  ")
  155.                .add("           THEN 'FOC' ")
  156.                .add("       WHEN (E.flg_type_so = 'SMP')  ")
  157.                .add("           THEN 'SAMPEL' ")
  158.                .add("       WHEN (E.flg_type_so = 'RVS')  ")
  159.                .add("           THEN 'REVISI' ")
  160.                .add("       WHEN (E.flg_type_so = 'CON')  ")
  161.                .add("           THEN 'KONSINYASI' ")
  162.                .add(" END AS type_so, f_get_username(B.create_user_id) AS create_username, COALESCE(I.doc_no, '-') AS delivery_order_no, ")
  163.                .add(" f_get_total_jenis_sku_of_so(B.tenant_id, A.so_id) AS total_jenis_sku_so, E.remark_internal, E.remark_settlement, ")
  164.                .add(" CASE WHEN EXISTS ( SELECT TRUE FROM ")
  165.                .add(DeliveryOrderReceipt.TABLE_NAME)
  166.                .add(" WHERE ref_id = A.do_id ) ")
  167.                .add(" THEN 'Y' ")
  168.                .add(" ELSE 'N' ")
  169.                .add(" END AS flg_do_receipt, ")
  170.                .add(" f_get_total_qty_do_sob_by_so_id(A.so_id) ")
  171.                .add(" FROM ").add(SoStatus.TABLE_NAME).add(" A ")
  172.                .add(" INNER JOIN ").add(SalesOrder.TABLE_NAME).add(" B ON A.so_id = B.so_id ")
  173.                .add(" INNER JOIN awe_currdoc_status D ON B.tenant_id = D.tenant_id AND D.scheme = :scheme AND B.so_id = D.doc_id ")
  174.                .add(" INNER JOIN ").add(SoInfo.TABLE_NAME).add(" E ON A.so_id = E.so_id ")
  175.                .add(" INNER JOIN so_brand G ON B.so_id = G.so_id ")
  176.                .add(" LEFT JOIN ").add(LogBook.TABLE_NAME).add(" H ON A.log_book_id = H.log_book_id ")
  177.                .add(" LEFT JOIN ").add(DeliveryOrder.TABLE_NAME).add(" I ON B.so_id = I.ref_id AND B.doc_type_id = I.ref_doc_type_id ")
  178.                .add(" WHERE A.tenant_id = :tenantId ")
  179.                .add(" AND A.ou_id = :ouId ")
  180.                .add(" AND A.doc_date BETWEEN :periodStart AND :periodEnd ")
  181.                .addIfNotEmpty(inputDto.getString("customer"), filterCustomer.toString())
  182.                .addIfNotEmpty(inputDto.getString("soNo"), filterSoNo.toString())
  183.                .addIfNotEmpty(inputDto.getString("flgTypeSo"), " AND E.flg_type_so = :flgTypeSo ")
  184.                .addIfNotEmpty(inputDto.getString("statusSo"), " AND B.status_doc = :statusSo ")
  185.                .addIfNotEmpty(inputDto.getString("statusSettlement"), " AND A.status_settlement = :statusSettlement ")
  186.                .addIfNotEmpty(inputDto.getString("statusPrint"), " AND A.status_picking = :statusPrint ")
  187.                .addIfNotEmpty(inputDto.getString("statusScan"), " AND A.status_do = :statusScan ")
  188.                .addIfNotEmpty(inputDto.getString("statusExpedition"), " AND A.status_log_book = :statusExpedition ")
  189.                .addIfNotEmpty(inputDto.getString("statusInvoice"), " AND A.status_invoice = :statusInvoice ")
  190.                .addIfNotEmpty(inputDto.getString("flgAutoSettle"), " AND E.flg_auto_settle = :flgAutoSettle ")
  191.                /*.addIfElseEquals(inputDto.getString("flgDoReceipt"), GeneralConstants.YES,
  192.                        " AND EXISTS ( SELECT TRUE FROM "+ DeliveryOrderReceipt.TABLE_NAME+" WHERE ref_id = A.do_id ) ", " AND NOT EXISTS ( SELECT TRUE FROM "+ DeliveryOrderReceipt.TABLE_NAME+" WHERE ref_id = A.do_id ) ")*/
  193.                .addIfNotEmpty(inputDto.getString("flgDoReceipt"), " AND ")
  194.                .addIfElseEquals(inputDto.getString("flgDoReceipt"), GeneralConstants.NO, " NOT ", " ")
  195.                .addIfNotEmpty(inputDto.getString("flgDoReceipt"), " EXISTS ( SELECT TRUE FROM "+ DeliveryOrderReceipt.TABLE_NAME+" WHERE ref_id = A.do_id ) ")
  196.                .addIfNotEmpty(inputDto.getString("salesman"), filterSalesman.toString())
  197.                .addIfNotEmpty(inputDto.getString("custDocNo"), filterExtDocNo.toString())
  198.                //.addIfNotEmpty(inputDto.getString("brand"), filterBrand.toString())
  199.                .addIfNotEmpty(inputDto.getString("doDocNo"), filterDoDocNo.toString())
  200.                .add(" ORDER BY B.doc_date, B.doc_no ")
  201.                .addIfNotNull(inputDto.get("limit"), " LIMIT :limit ")
  202.                .addIfNotNull(inputDto.get("offset"), " OFFSET :offset ");
  203.         //@formatter:on
  204.    
  205.         Query query = soStatusDao.createNativeQuery(builder.toString());
  206.         query.setParameter("scheme", SalesConstantsForKtmt.SHEME_DOCUMENT_SALES_ORDER_BY_BRAND);
  207.         query.setParameter("tenantId", inputDto.getLong("tenantId"));
  208.         query.setParameter("ouId", inputDto.getLong("ouId"));
  209.         query.setParameter("periodStart", inputDto.getString("periodStart"));
  210.         query.setParameter("periodEnd", inputDto.getString("periodEnd"));
  211.        
  212.         if (!inputDto.getString("statusSo").isEmpty())
  213.             query.setParameter("statusSo", inputDto.getString("statusSo"));
  214.         if (!inputDto.getString("statusSettlement").isEmpty())
  215.             query.setParameter("statusSettlement", inputDto.getString("statusSettlement"));
  216.         if (!inputDto.getString("statusPrint").isEmpty())
  217.             query.setParameter("statusPrint", inputDto.getString("statusPrint"));
  218.         if (!inputDto.getString("statusScan").isEmpty())
  219.             query.setParameter("statusScan", inputDto.getString("statusScan"));
  220.         if (!inputDto.getString("statusExpedition").isEmpty())
  221.             query.setParameter("statusExpedition", inputDto.getString("statusExpedition"));
  222.         if (!inputDto.getString("statusInvoice").isEmpty())
  223.             query.setParameter("statusInvoice", inputDto.getString("statusInvoice"));
  224.         if (!inputDto.getString("flgAutoSettle").isEmpty())
  225.             query.setParameter("flgAutoSettle", inputDto.getString("flgAutoSettle"));
  226.         if (!inputDto.getString("groupBrand").isEmpty())
  227.             query.setParameter("groupBrand", inputDto.getString("groupBrand"));
  228.         if (!inputDto.getString("flgTypeSo").isEmpty())
  229.             query.setParameter("flgTypeSo", inputDto.getString("flgTypeSo"));
  230.         if (inputDto.get("limit") != null)
  231.             query.setParameter("limit", inputDto.getLong("limit"));
  232.         if (inputDto.get("offset") != null)
  233.             query.setParameter("offset", inputDto.getLong("offset"));
  234.         if (!GeneralConstants.NULL_REF_VALUE_LONG.equals(brandId))
  235.             query.setParameter("brandId", brandId);
  236.  
  237.         result = query.getResultList();
  238.         return new Dto().putList("soList", DtoUtil.createDtoListFromArray(result, "soId", "tenantId", "ouId", "docNo", "docDate",
  239.                 "partnerId", "partnerCode", "partnerName", "remark", "soAmount", "currentRole", "statusSo", "statusSoSettlement",
  240.                 "soSettlementId", "statusPicking", "statusScan", "scanId", "statusExpedition", "expeditionId", "statusInvoice",
  241.                 "invoiceId", "flgAutoSettle", "brandCode", "groupBrand", "extDocNo",  
  242.                 "salesmanCode", "salesmanName", "versionSoStatus", "statusPrintDo", "logBookNo", "logBookDate", "typeSo", "createUserName",
  243.                 "deliveryOrderNo", "totalJenisSkuSo", "remarkInternal", "remarkSettlement", "flgDoReceipt", "totalQtyDo"));
  244.     }
  245. }
  246.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!