abirama62

BF Seacrh PO AP

Aug 3rd, 2021
763
133 days
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. package org.jleaf.erp.fin.bo.paymentorder;
  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.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.fin.dao.InvoiceApBalanceDao;
  18. import org.jleaf.erp.master.MasterConstants;
  19. import org.jleaf.erp.master.entity.Partner;
  20. import org.jleaf.util.DateUtil;
  21. import org.jleaf.util.DtoUtil;
  22. import org.jleaf.util.ValidationUtil;
  23. import org.jleaf.workflow.core.jpa.entity.MasterDocument;
  24. import org.springframework.beans.factory.annotation.Autowired;
  25. import org.springframework.stereotype.Service;
  26.  
  27. /**
  28.  * JLEAFFIN-107
  29.  * Get invoice ap balance by advance parameter
  30.  *
  31.  * @author fredie, Jun 4, 2013
  32.  * @since 1.0.0
  33.  * @version 1.0.0
  34.  */
  35. //@formatter:off
  36. @Service
  37. @InfoIn(value = {
  38.     @Info(name = "tenantId", description = "Tenant id", type = Long.class),
  39.     @Info(name = "datetime", description = "Datetime", type = String.class),
  40.     @Info(name = "ouId", description = "OU id", type = Long.class),
  41.     @Info(name = "docTypeId", description = "Doc type id", type = Long.class),
  42.     @Info(name = "supplierId", description = "Partner / Supplier id", type = String.class),
  43.     @Info(name = "supplierCodeName", description = "Partner / Supplier code or name", type = String.class),
  44.     @Info(name = "currCode", description = "Currency code", type = String.class),
  45.     @Info(name = "dueDate", description = "Due date", type = String.class),
  46.     @Info(name = "docDateFrom", description = "Doc date from", type = String.class),
  47.     @Info(name = "docDateTo", description = "Doc date to", type = String.class),
  48.     @Info(name = "docNo", description = "Doc No", type = String.class),
  49.     @Info(name = "remark", description = "Remark", type = String.class)
  50. })
  51. @InfoOut(value = {
  52.     @Info(name = "invoiceApBalanceList", description = "list of invoice ap balance(uniqueKey, invoiceApBalanceId, ouId, docTypeId, docDesc, invoiceApId, docNo, docDate, partnerId, parnerCode, partnerName, dueDate, invoiceNo, invoiceDate, curCode, amount, remark, paymentAmount, flagPayment, remainingAmount, version)", type = List.class)
  53. })
  54. @ErrorList(errorKeys ={
  55.        
  56. })
  57. //@formatter:off
  58. public class GetInvoiceApBalanceListAdvance extends AbstractBusinessFunction implements BusinessFunction {
  59.  
  60.     @Autowired
  61.     private InvoiceApBalanceDao invoiceApBalanceDao;
  62.    
  63.     @Override
  64.     public String getDescription() {
  65.         return "Get Invoice AP balance List by advance parameter";
  66.     }
  67.  
  68.     @SuppressWarnings("unchecked")
  69.     @Override
  70.     public Dto execute(Dto inputDto) throws Exception {
  71. //      @Info(name = "tenantId", description = "Tenant id", type = Long.class),
  72. //      @Info(name = "ouId", description = "OU id", type = Long.class),
  73. //      @Info(name = "supplierId", description = "Partner / Supplier id", type = String.class),
  74. //      @Info(name = "currCode", description = "Currency code", type = String.class),
  75. //      @Info(name = "dueDate", description = "Due date", type = String.class),
  76. //      @Info(name = "remark", description = "Remark", type = String.class)
  77.        
  78.         ValidationUtil.valDtoContainsKey(inputDto, "tenantId");
  79.         ValidationUtil.valDtoContainsKey(inputDto, "ouId");
  80.         ValidationUtil.valDtoContainsKey(inputDto, "supplierId");
  81.         ValidationUtil.valBlankOrNull(inputDto, "currCode");
  82.         ValidationUtil.valDtoContainsKey(inputDto, "dueDate");
  83.         ValidationUtil.valDtoContainsKey(inputDto, "remark");
  84.        
  85.         Long tenantId = inputDto.getLong("tenantId");
  86.         String datetime = inputDto.getString("datetime");
  87.        
  88.         Long ouId = inputDto.getLong("ouId");
  89.         Long supplierId = inputDto.getLong("supplierId");
  90.         String currCode = inputDto.getString("currCode");
  91.         String dueDate = inputDto.getString("dueDate");
  92.         String remark = inputDto.getString("remark");
  93.         String supplierCodeName = inputDto.getString("supplierCodeName");
  94.         String docDateFrom = inputDto.getString("docDateFrom");
  95.         String docDateTo = inputDto.getString("docDateTo");
  96.         //String flgPayment = inputDto.getString("flgPayment");
  97.         String dueDateFrom = GeneralConstants.START_DATE;
  98.         String dueDateTo = GeneralConstants.END_DATE;
  99.        
  100.         if(docDateFrom.isEmpty()){
  101.             docDateFrom = GeneralConstants.START_DATE;
  102.         }
  103.        
  104.         if(docDateTo.isEmpty()){
  105.             docDateTo = GeneralConstants.END_DATE;
  106.         }
  107.        
  108.         if(MasterConstants.DUE_DATE_TYPE_OVERDUE.equals(dueDate)){
  109.             dueDateTo = datetime.substring(0,8);
  110.         } else if(MasterConstants.DUE_DATE_TYPE_CURRENT.equals(dueDate)){
  111.             dueDateFrom = datetime.substring(0,8);
  112.             dueDateTo = dueDateFrom;
  113.         } else if(MasterConstants.DUE_DATE_TYPE_NOT_YET_DUE.equals(dueDate)){
  114.             dueDateFrom = datetime.substring(0,8);
  115.         }
  116.        
  117.         List<Object[]> result = null;
  118.         /**
  119.          * invoiceApBalanceId, ouId, docTypeId,
  120.          * invoiceApId, docNo, docDate, extDocNo,
  121.          * extDocDate, refDocTypeId, refId,
  122.          * partnerId, parnerCode, partnerName, dueDate,
  123.          * curCode, amount, remark, paymentAmount,
  124.          * flagPayment, version
  125.          */
  126.        
  127.         StringBuilder filterSupplierCodeName = new StringBuilder();
  128.         filterSupplierCodeName.append(" AND ( ")
  129.         .append(CriteriaHelper.likeExpressionIgnoreCase(supplierCodeName, "B.partner_code"))
  130.         .append(" OR ")
  131.         .append(CriteriaHelper.likeExpressionIgnoreCase(supplierCodeName, "B.partner_name"))
  132.         .append(" ) ");
  133.        
  134.         StringBuilder filterRemark = new StringBuilder();
  135.         filterRemark.append(" AND ").append(CriteriaHelper.likeExpressionIgnoreCase(remark, "A.remark"));
  136.        
  137.         QueryBuilder builder = new QueryBuilder();
  138.         builder.add(" SELECT '' || A.id || A.doc_type_id AS unique_key, A.id, A.ou_id, A.doc_type_id, C.doc_desc ")
  139.                 .add(" , A.invoice_ap_id, A.doc_no, A.doc_date ")
  140.                 .add(" , A.partner_id,  B.partner_code, B.partner_name, A.due_date ")
  141.                 .add(" , A.invoice_no, A.invoice_date ")
  142.                 .add(" , A.curr_code, A.amount, A.remark, A.payment_amount ")
  143.                 .add(" , A.flg_payment, (A.amount-A.payment_amount) AS remaining_amount, A.version ")
  144.             .add(" FROM vw_fi_os_all_invoice_ap A ")
  145.             .add(" INNER JOIN ").add(Partner.TABLE_NAME).add(" B ON A.partner_id = B.partner_id ")
  146.                 .add(" AND A.tenant_id = B.tenant_id ")
  147.             .add(" INNER JOIN ").add(MasterDocument.TABLE_NAME).add(" C ON A.doc_type_id = C.doc_type_id ")
  148.             .add(" WHERE A.tenant_id = :tenantId ")
  149.                 .add(" AND A.doc_date BETWEEN :docDateFrom AND :docDateTo ")
  150.                 .add(" AND A.due_date BETWEEN :dueDateFrom AND :dueDateTo ")
  151.                 .addIfNotEmpty(currCode, " AND A.curr_code = :currCode ")
  152.                 .addIfNotEquals(ouId, GeneralConstants.NULL_REF_VALUE_LONG, " AND A.ou_id = :ouId ")
  153.                 .addIfNotEquals(supplierId, GeneralConstants.NULL_REF_VALUE_LONG, " AND A.partner_id = :supplierId ")
  154.                 //View yg digunakan sudah filter yg outstanding, jadi tidak perlu filter flag payment lagi
  155.                 //.addIfNotEmpty(flgPayment, " AND A.flg_payment = '"+flgPayment+"' ")
  156.                 .addIfNotEmpty(supplierCodeName, filterSupplierCodeName.toString())
  157.                 .addIfNotEmpty(remark, filterRemark.toString())
  158.             .add(" ORDER BY A.invoice_no, A.invoice_date, C.doc_desc, A.doc_no, A.doc_date ");
  159.        
  160.         Query q = invoiceApBalanceDao.createNativeQuery(builder.toString());
  161.         q.setParameter("tenantId", tenantId);
  162.         q.setParameter("docDateFrom", docDateFrom);
  163.         q.setParameter("docDateTo", docDateTo);
  164.         q.setParameter("dueDateTo", dueDateTo);
  165.         q.setParameter("dueDateFrom", dueDateFrom);
  166.         if (!GeneralConstants.EMPTY_VALUE.equals(currCode)) {
  167.             q.setParameter("currCode", currCode);
  168.         }
  169.         if (!GeneralConstants.NULL_REF_VALUE_LONG.equals(ouId)) {
  170.             q.setParameter("ouId", ouId);
  171.         }
  172.         if (!GeneralConstants.NULL_REF_VALUE_LONG.equals(supplierId)) {
  173.             q.setParameter("supplierId", supplierId);
  174.         }
  175.        
  176.         result = q.getResultList();
  177.        
  178.         return new Dto().put("invoiceApBalanceList", DtoUtil.createDtoListFromArray(result,
  179.                 "uniqueKey", "invoiceApBalanceId", "ouId", "docTypeId", "docDesc", "invoiceApId", "docNo", "docDate",
  180.                 "partnerId", "partnerCode", "partnerName", "dueDate", "invoiceNo", "invoiceDate", "curCode", "amount", "remark",
  181.                 "paymentAmount", "flagPayment", "remainingAmount", "version"));
  182.     }
  183. }
  184.  
RAW Paste Data