abirama62

BF conditional approve SO

Feb 25th, 2021
849
128 days
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. package org.jleaf.erp.sls.bo.salesorder;
  2.  
  3. import java.math.BigDecimal;
  4. import java.sql.Connection;
  5. import java.sql.PreparedStatement;
  6. import java.sql.ResultSet;
  7. import java.text.DecimalFormat;
  8. import java.text.NumberFormat;
  9. import java.util.ArrayList;
  10. import java.util.Arrays;
  11. import java.util.List;
  12.  
  13. import javax.persistence.NoResultException;
  14. import javax.persistence.Query;
  15.  
  16. import org.jleaf.core.AbstractBusinessFunction;
  17. import org.jleaf.core.BusinessFunction;
  18. import org.jleaf.core.Dto;
  19. import org.jleaf.core.GeneralConstants;
  20. import org.jleaf.core.annotation.ErrorList;
  21. import org.jleaf.core.annotation.Info;
  22. import org.jleaf.core.annotation.InfoIn;
  23. import org.jleaf.core.annotation.InfoOut;
  24. import org.jleaf.core.dao.QueryBuilder;
  25. import org.jleaf.erp.master.MasterConstants;
  26. import org.jleaf.erp.master.entity.Partner;
  27. import org.jleaf.erp.master.entity.PartnerType;
  28. import org.jleaf.erp.master.entity.SellPriceProductForSo;
  29. import org.jleaf.erp.sls.SalesConstants;
  30. import org.jleaf.erp.sls.SalesConstantsForSasa;
  31. import org.jleaf.erp.sls.dao.SalesOrderDao;
  32. import org.jleaf.erp.sls.entity.*;
  33. import org.jleaf.util.Calc;
  34. import org.jleaf.util.DateUtil;
  35. import org.jleaf.util.DtoUtil;
  36. import org.jleaf.util.ValidationUtil;
  37. import org.slf4j.Logger;
  38. import org.slf4j.LoggerFactory;
  39. import org.springframework.beans.factory.annotation.Autowired;
  40. import org.springframework.jdbc.core.JdbcTemplate;
  41. import org.springframework.stereotype.Service;
  42.  
  43.  
  44. /**
  45.  * Find Sales Order Info For appproval purpose
  46.  *
  47.  * @author fredie, 27 Feb 2014
  48.  * @version 1.0
  49.  */
  50.  
  51. //@ formatter:off
  52. @Service("findSalesOrderInfoForApproval")
  53. @InfoIn(value = {
  54.     @Info(name="sessionId", description="Session Id", type=String.class, required=true),
  55.     @Info(name="userId", description="User Id", type=Long.class, required=true),
  56.     @Info(name="roleId", description="Role Id", type=Long.class, required=true),
  57.     @Info(name="soId", description="Sales Order Id", type=Long.class, required=true),
  58.     @Info(name="datetime", description="Datetime", type=String.class, required=true),
  59.     @Info(name = "arCurrCode", description = "AR curr code", type = String.class)
  60. })
  61. @InfoOut(value = {
  62.     @Info(name = "soId", description = "Sales Order Id", type = Long.class),
  63.     @Info(name = "totalNettAmount", description = "Total nett amount", type = Double.class),
  64.     @Info(name = "totalpurchAmount", description = "Total Purchasing Amount", type = Double.class),
  65.     @Info(name = "totalCurrCode", description = "Curr Code of totalNettAmount", type = String.class),
  66.     @Info(name = "gpPercentage", description = "GP Percentage", type = Double.class),
  67.     @Info(name = "gpAmount", description = "GP Amount", type = Double.class),
  68.     @Info(name = "arAmount", description = "AR Amount", type = Double.class),
  69.     @Info(name = "arCurrCode", description = "AR curr code", type = String.class),
  70.     @Info(name = "currCodeAr", description = "curr code AR", type = String.class),
  71.     @Info(name = "amountAr", description = "Amount AR", type = Double.class),
  72.     @Info(name = "notYetDueArAmount", description = "Not Yet Due Ar Amount", type = Double.class),
  73.     @Info(name = "currentArAmount", description = "Current AR Amount", type = Double.class),
  74.     @Info(name = "overdueArAmount", description = "Overdue AR Amount", type = Double.class),
  75.     @Info(name = "comparePriceString", description = "Compare Price String", type = String.class),
  76.     @Info(name = "flagFaultComparePrice", description = "Flag Fault Compare Price", type = Long.class),
  77.     @Info(name = "flgPriceAfterDiscLowerThanMstrMinSellPrice", description = "Flag Sell Price After Disc Lower Than Master Min Sell Price", type = String.class),
  78.     @Info(name = "flgChequeGiroReject", description = "Flg Cheque/Giro Reject", type = String.class),
  79.     @Info(name = "chequeGiroNo", description = "Cheque/Giro No", type = String.class),
  80.     @Info(name = "amountLimit", description = "Amount Limit Partner", type = Double.class),
  81.     @Info(name = "totalAmountCreditLimitUsage", description = "Total Amount Credit Limit Usage", type = Double.class),
  82.     @Info(name = "amountLimitBalance", description = "Amount Limit Balance", type = Double.class),
  83.     @Info(name = "flgOverDueDate", description = "flg Over Due Date", type = String.class)
  84. })
  85. @ErrorList(errorKeys = {
  86.  
  87.  })
  88. //@ formatter:on
  89. public class FindSalesOrderInfoForApprovalForSasa extends AbstractBusinessFunction implements BusinessFunction {
  90.     private static final Logger log = LoggerFactory.getLogger(FindSalesOrderInfoForApprovalForSasa.class);
  91.      
  92.     @Autowired
  93.     SalesOrderDao salesOrderDao;
  94.    
  95.     @Autowired
  96.     private JdbcTemplate jdbcTemplate;
  97.      
  98.     @Override
  99.     public String getDescription() {
  100.          return "Find Sales Order Info For appproval purpose";
  101.     }
  102.      
  103.     @SuppressWarnings("unchecked")
  104.     @Override
  105.     public Dto execute(Dto inputDto) throws Exception {    
  106.         ValidationUtil.valDtoContainsKey(inputDto, "soId");
  107.         ValidationUtil.valDtoContainsKey(inputDto, "arCurrCode");
  108.         ValidationUtil.valDtoContainsKey(inputDto, "datetime");
  109.         ValidationUtil.valDatetime(inputDto, "datetime");
  110.  
  111.         log.info("inputDto: "+inputDto);
  112.        
  113.         Long soId = inputDto.getLong("soId");
  114.         Long userId = inputDto.getLong("userId");
  115.         Long roleId = inputDto.getLong("roleId");
  116.         String arCurrCode = inputDto.getString("arCurrCode");
  117.         String datetime = inputDto.getString("datetime");
  118.         String date = datetime.substring(0, 8);
  119.  
  120.         String flgPriceAfterDiscLowerThanMstrMinSellPrice = GeneralConstants.NO;
  121.         String flgOverDueDate = GeneralConstants.NO;
  122.         String flgChequeGiroReject = GeneralConstants.NO;
  123.         String chequeGiroNo = GeneralConstants.EMPTY_VALUE;
  124.         Double totalAmountCreditLimitUsage = 0D;
  125.         Double amountLimit = 0D;
  126.         Double saldoAr = 0D;
  127.         Double saldoTaxAr = 0D;
  128.         Integer digitDecimal = 0;
  129.         Double inProgressSoAmount = 0D;
  130.         Double unAllocatedCbIn = 0D;
  131.         Double amountLimitBalance = 0D;
  132.        
  133.         Dto outputDto = new Dto();
  134.          
  135.         // get summary nettItemAmount
  136.         QueryBuilder builderNettAmount = new QueryBuilder();
  137.         builderNettAmount.add(" SELECT SUM(A.nett_item_amount) AS total_nett_amount, A.curr_code, B.partner_id, B.partner_bill_to_id, B.tenant_id, B.ou_id ")
  138.             .add(" FROM " + SalesOrderItem.TABLE_NAME + " A ")
  139.             .add(" INNER JOIN "+ SalesOrder.TABLE_NAME+" B ON A.so_id = B.so_id " )
  140.             .add(" WHERE A.so_id = :soId ")
  141.             .add(" GROUP BY A.curr_code, B.partner_id, B.tenant_id, B.ou_id, B.partner_bill_to_id");
  142.            
  143.         Query queryNettAmount = salesOrderDao.createNativeQuery(builderNettAmount.toString());
  144.         queryNettAmount.setParameter("soId", soId);
  145.          
  146.         List<Object[]> resultNettAmount = queryNettAmount.getResultList();
  147.         Double totalNettAmount = 0d;
  148.         String totalCurrCode = GeneralConstants.EMPTY_VALUE;
  149.         Long partnerId  = GeneralConstants.NULL_REF_VALUE_LONG;
  150.         Long partnerBillToId  = GeneralConstants.NULL_REF_VALUE_LONG;
  151.         Long tenantId  = GeneralConstants.NULL_REF_VALUE_LONG;
  152.         Long ouId  = GeneralConstants.NULL_REF_VALUE_LONG;
  153.  
  154.         if(resultNettAmount!=null && resultNettAmount.size()>0){
  155.             List<Dto> resultNettAmountList = new ArrayList<Dto>();
  156.             resultNettAmountList = DtoUtil.createDtoListFromArray(resultNettAmount, "totalNettAmount", "totalCurrCode", "partnerId", "partnerBillToId", "tenantId", "ouId");
  157.            
  158.             totalNettAmount = resultNettAmountList.get(0).getDouble("totalNettAmount");
  159.             totalCurrCode = resultNettAmountList.get(0).getString("totalCurrCode");
  160.             partnerId = resultNettAmountList.get(0).getLong("partnerId");
  161.             partnerBillToId = resultNettAmountList.get(0).getLong("partnerBillToId");
  162.             tenantId = resultNettAmountList.get(0).getLong("tenantId");
  163.             ouId = resultNettAmountList.get(0).getLong("ouId");
  164.         }
  165.        
  166.         totalNettAmount = new Calc(totalNettAmount).doubleValue();
  167.        
  168.         // get purcha and GP
  169.         QueryBuilder builderGp = new QueryBuilder();
  170.         builderGp
  171.             .add(" WITH summary_purch_per_currency AS (")
  172.             .add("  SELECT SUM(A.price * B.qty_so) AS total_price_purch, A.curr_code AS curr_code_purch, B.curr_code AS curr_code_so ")
  173.             .add("  FROM "+SalesOrderItemPurchasing.TABLE_NAME+" A ")
  174.             .add("  INNER JOIN "+SalesOrderItem.TABLE_NAME+" B ON A.so_item_id = B.so_item_id ")
  175.             .add("  WHERE B.so_id = :soId ")
  176.             .add("  GROUP BY A.curr_code, B.curr_code ")
  177.             .add(" ) ")
  178.             .add(" SELECT COALESCE(SUM(f_get_amount_in_nearest_com_rate(:tenantId, A.total_price_purch, :date, A.curr_code_purch, A.curr_code_so )), 0) AS total_price_as_so_currency ")
  179.             .add(" FROM summary_purch_per_currency A ");
  180.  
  181.         Double totalpurchAmount = 0d;
  182.         Query queryGp = salesOrderDao.createNativeQuery(builderGp.toString());
  183.         queryGp.setParameter("tenantId", tenantId);
  184.         queryGp.setParameter("date", date);
  185.         queryGp.setParameter("soId", soId);
  186.  
  187.         Object resultQueryGp = queryGp.getSingleResult();
  188.         if(resultQueryGp!=null ){
  189.             totalpurchAmount = Double.valueOf(resultQueryGp.toString());
  190.         }
  191.        
  192.        
  193.         // get external commission
  194.         QueryBuilder builderExtComm = new QueryBuilder();
  195.         builderExtComm
  196.             .add(" SELECT COALESCE(SUM(B.commission_amount * f_commercial_rate(A.tenant_id, A.doc_date, B.commission_curr_code, A.curr_code)),0) AS ext_comm_amount FROM ")
  197.             .add(SalesOrder.TABLE_NAME)
  198.             .add(" A ")
  199.             .add(" INNER JOIN ")
  200.             .add(SalesOrderExternalCommission.TABLE_NAME)
  201.             .add(" B ON A.so_id=B.so_id AND A.tenant_id = B.tenant_id ")
  202.             .add(" WHERE A.so_id = :soId AND A.tenant_id = :tenantId ");
  203.        
  204.         Double extCommAmount = 0d;
  205.         Query queryExtComm = salesOrderDao.createNativeQuery(builderExtComm.toString());
  206.         queryExtComm.setParameter("soId", soId);
  207.         queryExtComm.setParameter("tenantId", tenantId);
  208.        
  209.         Object resultExtComm = queryExtComm.getSingleResult();
  210.         if(resultExtComm!=null ){
  211.             extCommAmount = Double.valueOf(resultExtComm.toString());
  212.         }
  213.        
  214.         Double gpAmount = new Calc(totalNettAmount).subtract(extCommAmount).subtract(totalpurchAmount).doubleValue();
  215.         Double gpPercentage = 0d;
  216.        
  217.         /**
  218.          * Modify By Henik , Nov 19 , 2015
  219.          * -> modify untuk perhitungan gpPercentage yang sebelum nya gpPercentage = (selisih harga jual - harga ref beli) / harga beli
  220.          * sekarang di ubah gpPercentage = (selsih harga jual - harga bref beli) / harga jual
  221.          */
  222.         // lakukan perhitungan GP lebih lanjut, jika dan hanya jika, total amount purch tidak 0 (nol), tuk menghindari divide by zero
  223.         if (totalpurchAmount.compareTo(0d) != 0) {
  224.             gpPercentage = new Calc(gpAmount).multiply(100).divide(totalNettAmount, BigDecimal.ROUND_HALF_UP, 2).doubleValue();
  225.         }
  226.        
  227.         // get ar amount
  228.         QueryBuilder builderArAmount = new QueryBuilder();
  229.         builderArAmount
  230.             .add(" WITH all_currency_invoice_ar AS ( ")
  231.             .add("      SELECT SUM(A.amount - A.payment_amount) AS ar_amount, A.curr_code ")
  232.             .add("      FROM vw_fi_all_invoice_ar A ")
  233.             .add("      WHERE A.partner_id = :partnerId ")
  234.             .add("          AND A.flg_payment IN (:flgPayments) ")
  235.             .add("      GROUP BY A.curr_code, A.flg_payment ")
  236.             .add(" ), current_os_invoice_ar AS ( ")
  237.             .add("      SELECT SUM( f_get_amount_in_nearest_com_rate(:tenantId, A.ar_amount, :date, A.curr_code, :arCurrCode ) ) AS total_ar_amount ")
  238.             .add("      FROM all_currency_invoice_ar A")
  239.             .add(" ) SELECT COALESCE(A.total_ar_amount, 0) ")
  240.             .add(" FROM current_os_invoice_ar A ");
  241.         Query queryArAmount = salesOrderDao.createNativeQuery(builderArAmount.toString());
  242.         queryArAmount.setParameter("partnerId", partnerId);
  243.         queryArAmount.setParameter("tenantId", tenantId);
  244.         queryArAmount.setParameter("date", date);
  245.         queryArAmount.setParameter("arCurrCode", arCurrCode);
  246.         queryArAmount.setParameter("flgPayments", Arrays.asList(new String[] {SalesConstants.IN_PROGRESS_TRANSACTION, GeneralConstants.NO}));
  247.  
  248.         Object resultArAmount = queryArAmount.getSingleResult();
  249.         Double arAmount = Double.valueOf(resultArAmount.toString());
  250.                
  251.         outputDto.put("soId", soId);
  252.         outputDto.put("totalNettAmount", Double.valueOf(new DecimalFormat("#.00").format(totalNettAmount)));
  253.         outputDto.put("totalpurchAmount", totalpurchAmount);
  254.         outputDto.put("totalCurrCode", totalCurrCode);
  255.         outputDto.put("extCommAmount", extCommAmount);
  256.         outputDto.put("gpPercentage", Double.valueOf(new DecimalFormat("#.00").format(gpPercentage)));
  257.         outputDto.put("gpAmount", Double.valueOf(new DecimalFormat("#.00").format(gpAmount)));
  258.         outputDto.put("arAmount", Double.valueOf(new DecimalFormat("#.00").format(arAmount)));
  259.         outputDto.put("arCurrCode", arCurrCode);
  260.        
  261.         //not yet due dan overdue ar amount
  262.         Connection conn = jdbcTemplate.getDataSource().getConnection();
  263.         conn.setAutoCommit(false);
  264.         PreparedStatement stm = null;
  265.  
  266.         try {
  267.  
  268.             String sessionId = inputDto.getString("sessionId");
  269.             String dueDateType = GeneralConstants.EMPTY_VALUE;
  270.             String dueDateFrom = GeneralConstants.START_DATE;
  271.             String dueDateTo = GeneralConstants.END_DATE;
  272.             String dateNow = DateUtil.dateNow();
  273.  
  274.             stm = conn.prepareStatement("SELECT f_get_summary_aging_ar_list_for_inquiry_progress_ar(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
  275.             stm.setString(1, sessionId);
  276.             stm.setLong(2, tenantId);
  277.             stm.setLong(3, ouId);
  278.             stm.setLong(4, userId);
  279.             stm.setLong(5, roleId);
  280.             stm.setLong(6, partnerId);
  281.             stm.setString(7, dueDateType);
  282.             stm.setString(8, dueDateFrom);
  283.             stm.setString(9, dueDateTo);
  284.             stm.setString(10, dateNow);
  285.  
  286.             ResultSet resultSetFromFunction = stm.executeQuery();
  287.             ResultSet resultSetFromQuery = null;
  288.             if (resultSetFromFunction.next()) {
  289.                 resultSetFromQuery = (ResultSet) resultSetFromFunction.getObject(1);
  290.             }
  291.  
  292.             resultSetFromFunction.close();
  293.  
  294.             Dto detailDto = new Dto();
  295.  
  296.             while (resultSetFromQuery != null && resultSetFromQuery.next()) {
  297.                 detailDto.put("currCodeAr", resultSetFromQuery.getString(1));
  298.                 detailDto.put("amountAr", resultSetFromQuery.getBigDecimal(2).doubleValue());
  299.                 detailDto.put("notYetDueArAmount", resultSetFromQuery.getBigDecimal(3).doubleValue());
  300.                 detailDto.put("currentArAmount", resultSetFromQuery.getBigDecimal(4).doubleValue());
  301.                 detailDto.put("overdueArAmount", resultSetFromQuery.getBigDecimal(5).doubleValue());
  302.             }
  303.  
  304.             stm.close();
  305.             conn.commit();
  306.             conn.close();
  307.  
  308.             outputDto.putAll(detailDto);
  309.  
  310.         } catch (Exception e) {
  311.             if (stm != null)
  312.                 stm.close();
  313.  
  314.             if (conn != null && !conn.isClosed())
  315.                 conn.rollback();
  316.  
  317.             throw e;
  318.         }
  319.  
  320.         // flag harga jual setelah dipotong diskon < batas bawah harga jual master yg disimpan di item SO
  321.         QueryBuilder builderFlgSellPrice = new QueryBuilder();
  322.         builderFlgSellPrice.add("SELECT 1 FROM ").add(SalesOrderItem.TABLE_NAME).add(" A ")
  323.                 .add(" INNER JOIN "+SalesOrderItemCustom.TABLE_NAME+" B ON A.so_item_id = B.so_item_id ")
  324.                 .add(" WHERE A.tenant_id = :tenantId ")
  325.                 .add(" AND so_id = :soId ")
  326.                 .add(" AND (A.tax_price + A.nett_sell_price) < B.master_min_sell_price ");
  327.  
  328.         Query queryPriceGreaterThanMasterSellPrice = salesOrderDao.createNativeQuery(builderFlgSellPrice.toString());
  329.         queryPriceGreaterThanMasterSellPrice.setParameter("tenantId", tenantId);
  330.         queryPriceGreaterThanMasterSellPrice.setParameter("soId", soId);
  331.         List<Object[]> resultList = queryPriceGreaterThanMasterSellPrice.getResultList();
  332.         if (!resultList.isEmpty()){
  333.             flgPriceAfterDiscLowerThanMstrMinSellPrice = GeneralConstants.YES;
  334.         }
  335.         log.info("flgPriceAfterDiscLowerThanMstrMinSellPrice: "+flgPriceAfterDiscLowerThanMstrMinSellPrice);
  336.  
  337.         // flg ada cek/giro tolak, cuma sebagai history
  338.         QueryBuilder builderChequeGiroReject = new QueryBuilder();
  339.         builderChequeGiroReject.add("select B.cheque_giro_no ")
  340.                 .add(" FROM "+SalesOrder.TABLE_NAME+ " A ")
  341.                 .add(" INNER JOIN "+ SalesConstantsForSasa.TABLE_CHEQUE_GIRO_REALIZATION+" B ON A.tenant_id = B.tenant_id AND A.partner_bill_to_id = B.partner_id ")
  342.                 .add(" INNER JOIN "+SalesConstantsForSasa.TABLE_IN_OUT_CASHBANK+" C ON B.in_out_cashbank_id = C.in_out_cashbank_id ")
  343.                 .add(" WHERE B.realization_status = :realizationStatus ")
  344.                 .add(" AND C.status_doc = :statusDoc ")
  345.                 .add(" AND A.so_id = :soId ")
  346.                 .add(" ORDER BY B.realization_date ")
  347.                 .add(" DESC ")
  348.                 .add(" LIMIT 1 ");
  349.  
  350.         Query queryChequeGiroReject = salesOrderDao.createNativeQuery(builderChequeGiroReject.toString());
  351.         queryChequeGiroReject.setParameter("soId", soId);
  352.         queryChequeGiroReject.setParameter("statusDoc", SalesConstants.RELEASED_TRANSACTION);
  353.         queryChequeGiroReject.setParameter("realizationStatus", SalesConstantsForSasa.CHEQUE_GIRO_STATUS_REJECT);
  354. //
  355.         Object resultCGList = null;
  356.         try{
  357.             resultCGList = queryChequeGiroReject.getSingleResult();
  358.         } catch (NoResultException e) {}
  359.  
  360.         if(resultCGList!=null){
  361.             flgChequeGiroReject = GeneralConstants.YES;
  362.             chequeGiroNo = resultCGList.toString();
  363.         }
  364.  
  365.         log.info("flgChequeGiroReject: "+flgChequeGiroReject);
  366.         log.info("chequeGiroNo: "+chequeGiroNo);
  367.  
  368.         // flag lewat dari plafon toko
  369.         //Get Plafon (Amount Limit) Partner
  370.         QueryBuilder builderPlafonCustomer = new QueryBuilder();
  371.         builderPlafonCustomer.add("SELECT B.amount_limit ")
  372.                 .add(" FROM "+SalesOrder.TABLE_NAME+" A ")
  373.                 .add(" INNER JOIN "+ PartnerType.TABLE_NAME+ " B ON A.tenant_id = B.tenant_id AND A.partner_bill_to_id = B.partner_id ")
  374.                 .add(" WHERE B.group_partner = :partnerType ")
  375.                 .add(" AND A.so_id = :soId");
  376.         Query queryPlafonCustomer = salesOrderDao.createNativeQuery(builderPlafonCustomer.toString());
  377.         queryPlafonCustomer.setParameter("soId", soId);
  378.         queryPlafonCustomer.setParameter("partnerType", SalesConstants.GROUP_PARTNER_CUSTOMER);
  379.  
  380.         Object resultPlafonCustomer = null;
  381.         try {
  382.             resultPlafonCustomer = queryPlafonCustomer.getSingleResult();
  383.         } catch (NoResultException e) {}
  384.  
  385.         if(resultPlafonCustomer!=null ){
  386.             amountLimit = Double.valueOf(resultPlafonCustomer.toString());
  387.         }
  388.         log.info("amountLimitPlafon: "+amountLimit);
  389.  
  390.         //Get Piutang
  391.         /*SaldoAr*/
  392.         QueryBuilder builderSaldoAr = new QueryBuilder();
  393.         builderSaldoAr.add("SELECT COALESCE(SUM(A.amount-A.payment_amount), 0) ")
  394.                 .add(" FROM "+SalesConstantsForSasa.TABLE_INVOICE_AR_BALANCE+" A ")
  395.                 .add(" WHERE A.flg_payment <> :flagYes ")
  396.                 .add(" AND A.partner_id = :partnerId ")
  397.                 .add(" AND A.tenant_id = :tenantId ")
  398.                 .add("");
  399.  
  400.         Query querySaldoAr = salesOrderDao.createNativeQuery(builderSaldoAr.toString());
  401.         querySaldoAr.setParameter("flagYes", GeneralConstants.YES);
  402.         querySaldoAr.setParameter("partnerId", partnerBillToId);
  403.         querySaldoAr.setParameter("tenantId", tenantId);
  404.  
  405.         Object resultSaldo = null;
  406.         try {
  407.             resultSaldo = querySaldoAr.getSingleResult();
  408.         } catch (NoResultException e) {}
  409.  
  410.         if(resultSaldo!=null ){
  411.             saldoAr = Double.valueOf(resultSaldo.toString());
  412.         }
  413.         log.info("saldoAr: "+saldoAr);
  414.  
  415.         /*SaldoTaxAr*/
  416.         QueryBuilder builderSaldoTaxAr = new QueryBuilder();
  417.         builderSaldoTaxAr.add("SELECT COALESCE(SUM(A.tax_amount-A.payment_amount), 0) ")
  418.                 .add(" FROM "+SalesConstantsForSasa.TABLE_INVOICE_TAX_AR_BALANCE+" A ")
  419.                 .add(" INNER JOIN "+SalesConstantsForSasa.TABLE_INVOICE_AR_BALANCE+" B ON A.invoice_ar_balance_id = B.invoice_ar_balance_id")
  420.                 .add(" WHERE A.flg_payment <> :flagYes ")
  421.                 .add(" AND A.partner_id = :partnerId ")
  422.                 .add(" AND A.tenant_id = :tenantId ")
  423.                 .add("");
  424.  
  425.         Query querySaldoTaxAr = salesOrderDao.createNativeQuery(builderSaldoTaxAr.toString());
  426.         querySaldoTaxAr.setParameter("flagYes", GeneralConstants.YES);
  427.         querySaldoTaxAr.setParameter("partnerId", partnerBillToId);
  428.         querySaldoTaxAr.setParameter("tenantId", tenantId);
  429.  
  430.         resultSaldo = null;
  431.         try {
  432.             resultSaldo = querySaldoTaxAr.getSingleResult();
  433.         } catch (NoResultException e) {}
  434.  
  435.         if(resultSaldo!=null ){
  436.             saldoTaxAr = Double.valueOf(resultSaldo.toString());
  437.         }
  438.         log.info("saldoTaxAr: "+saldoTaxAr);
  439.  
  440.         //Get SO Belum Jadi DO
  441.         QueryBuilder builderSONotYetDO = new QueryBuilder();
  442.         builderSONotYetDO
  443.                 .add(" WITH count_amount AS ( ")
  444.                     .add(" WITH qty_progress AS ( ")
  445.                         .add(" SELECT so_item_id, (qty_so - qty_dlv - qty_cancel + qty_add) AS qty ")
  446.                         .add(" FROM ").add(SalesOrderBalanceItem.TABLE_NAME).add(" A ")
  447.                         .add(" WHERE A.status_item NOT IN (:voidDoc, :rejectedDoc) ")
  448.                         .add(" AND (qty_so - qty_dlv - qty_cancel + qty_add) > 0 ) ")
  449.                     .add("SELECT COALESCE(SUM((B.nett_sell_price + B.tax_price)*C.qty), 0) AS amount_total ")
  450.                     .add(" FROM "+SalesOrder.TABLE_NAME+" A ")
  451.                     .add(" INNER JOIN "+SalesOrderItem.TABLE_NAME+" B ON A.so_id = B.so_id ")
  452.                     .add(" INNER JOIN qty_progress C ON B.so_item_id = C.so_item_id ")
  453.                     .add(" WHERE A.tenant_id = :tenantId ")
  454.                     .add(" AND A.status_doc NOT IN (:voidDoc, :rejectedDoc)")
  455.                     .add(" AND A.workflow_status IN (:statusApproved)")
  456.                     .add(" AND NOT EXISTS (SELECT 1 "+
  457.                         "FROM "+DeliveryOrder.TABLE_NAME+" Y "+
  458.                         "WHERE A.so_id = Y.ref_id "+
  459.                         "AND Y.status_doc = :statusDocRelease) ")
  460.                     .add(" AND A.partner_bill_to_id = :partnerId ")
  461.  
  462.                     .add(" UNION ALL ")
  463.  
  464.                     .add("SELECT COALESCE(SUM(B.nett_item_amount+B.tax_amount), 0) AS amount_total ")
  465.                     .add(" FROM "+SalesOrder.TABLE_NAME+" A ")
  466.                     .add(" INNER JOIN "+SalesOrderItem.TABLE_NAME+" B ON A.so_id = B.so_id ")
  467.                     .add(" WHERE A.tenant_id = :tenantId ")
  468.                     .add(" AND A.status_doc IN (:progressDoc, :draftDoc)")
  469.                     .add(" AND NOT EXISTS (SELECT 1 "+
  470.                             "FROM "+DeliveryOrder.TABLE_NAME+" Y "+
  471.                             "WHERE A.so_id = Y.ref_id "+
  472.                             "AND Y.status_doc = :statusDocRelease) ")
  473.                     .add(" AND A.partner_bill_to_id = :partnerId ")
  474.                 .add(" ) SELECT SUM(amount_total) FROM count_amount ")
  475.                     .add("");
  476.  
  477.         Query querySONotYetDO = salesOrderDao.createNativeQuery(builderSONotYetDO.toString());
  478.         querySONotYetDO.setParameter("partnerId", partnerBillToId);
  479.         querySONotYetDO.setParameter("tenantId", tenantId);
  480.  
  481.         querySONotYetDO.setParameter("statusDocRelease", MasterConstants.RELEASED);
  482.         querySONotYetDO.setParameter("voidDoc", SalesConstants.VOID_TRANSACTION);
  483.         querySONotYetDO.setParameter("rejectedDoc", SalesConstants.CANCELED_TRANSACTION);
  484.         querySONotYetDO.setParameter("progressDoc", SalesConstants.IN_PROGRESS_TRANSACTION);
  485.         querySONotYetDO.setParameter("draftDoc", SalesConstants.DRAFT_TRANSACTION);
  486.         querySONotYetDO.setParameter("statusApproved", SalesConstants.WORKFLOW_STATUS_APPROVED);
  487.  
  488.  
  489.         resultSaldo = null;
  490.         try {
  491.             resultSaldo = querySONotYetDO.getSingleResult();
  492.         } catch (NoResultException e) {}
  493.  
  494.         if(resultSaldo!=null ){
  495.             inProgressSoAmount = Double.valueOf(resultSaldo.toString());
  496.         }
  497.         log.info("inProgressSoAmount: "+inProgressSoAmount);
  498.  
  499.         //Get un-allocated in_cashbank from customer
  500.         QueryBuilder builderUnAllocatedCbIn = new QueryBuilder();
  501.         builderUnAllocatedCbIn.add("WITH data_cb_in_with_conv_with_data_cg_receipt AS (")
  502.                 .add(" SELECT COALESCE(SUM(A.amount), 0) AS amount " +
  503.                         "FROM fi_receipt_ar_balance A " +
  504.                         " WHERE A.tenant_id = :tenantId " +
  505.                         //" AND A.ou_id = :ouId " +
  506.                         " AND A.flg_alloc = :flgNo " +
  507.                         " AND A.doc_type_id IN (:docCbInPartnerReceive, :docConvCbInOtherToPartner) " +
  508.                         " AND A.partner_id = :partnerId")
  509.                 .add(" UNION ALL ")
  510.                 .add(" SELECT COALESCE(SUM(A.amount), 0) AS amount " +
  511.                         " FROM fi_receipt_ar_balance A " +
  512.                         " INNER JOIN cb_cheque_giro_balance B ON A.ref_item_id = B.in_out_cheque_giro_payment_id " +
  513.                         " AND B.flg_realization = :flgYes " +
  514.                         " WHERE A.tenant_id = :tenantId " +
  515.                         //" AND A.ou_id = :ouId " +
  516.                         " AND A.flg_alloc = :flgNo " +
  517.                         " AND A.doc_type_id = :docTypeIdCGReceipt " +
  518.                         " AND A.partner_id = :partnerId ")
  519.                 .add(" ) ")
  520.                 .add(" SELECT SUM(amount) ")
  521.                 .add(" FROM data_cb_in_with_conv_with_data_cg_receipt ")
  522.                 .add("");
  523.  
  524.         Query queryUnAllocatedCbIn = salesOrderDao.createNativeQuery(builderUnAllocatedCbIn.toString());
  525.         queryUnAllocatedCbIn.setParameter("partnerId", partnerBillToId);
  526.         queryUnAllocatedCbIn.setParameter("tenantId", tenantId);
  527.         //queryUnAllocatedCbIn.setParameter("ouId", ouId);
  528.         queryUnAllocatedCbIn.setParameter("flgNo", GeneralConstants.NO);
  529.         queryUnAllocatedCbIn.setParameter("flgYes", GeneralConstants.YES);
  530.         queryUnAllocatedCbIn.setParameter("docCbInPartnerReceive", SalesConstantsForSasa.DOC_CB_IN_PARTNER_RECEIVE);
  531.         queryUnAllocatedCbIn.setParameter("docConvCbInOtherToPartner", SalesConstantsForSasa.DOC_CONV_CB_IN_OTHER_TO_PARTNER);
  532.         queryUnAllocatedCbIn.setParameter("docTypeIdCGReceipt", SalesConstantsForSasa.DOC_TYPE_ID_CG_RECEIPT);
  533.  
  534.         resultSaldo = null;
  535.         try {
  536.             resultSaldo = queryUnAllocatedCbIn.getSingleResult();
  537.         } catch (NoResultException e) {}
  538.  
  539.         if(resultSaldo!=null ){
  540.             unAllocatedCbIn = Double.valueOf(resultSaldo.toString());
  541.         }
  542.         log.info("unAllocatedCbIn: "+unAllocatedCbIn);
  543.  
  544.         /* Count Sisa Plafon*/
  545.         totalAmountCreditLimitUsage = (saldoAr + saldoTaxAr) + inProgressSoAmount - unAllocatedCbIn;
  546.         amountLimitBalance = amountLimit - totalAmountCreditLimitUsage;
  547.         log.info("Total Credit Limit Usage: "+totalAmountCreditLimitUsage);
  548.         log.info("Sisa Plafon: "+amountLimitBalance);
  549.  
  550.  
  551.         // cek apakah ada piutang overdue
  552.         QueryBuilder builderStatusOverDuePartner = new QueryBuilder();
  553.         builderStatusOverDuePartner.add(" SELECT f_get_status_over_due_partner(:tenantId, :ouId, :userId, :roleId, :datetime, :partnerId) AS status_over_due ");
  554.  
  555.         Query queryStatusOverDuePartner = salesOrderDao.createNativeQuery(builderStatusOverDuePartner.toString());
  556.         queryStatusOverDuePartner.setParameter("tenantId", tenantId);
  557.         queryStatusOverDuePartner.setParameter("ouId", ouId);
  558.         queryStatusOverDuePartner.setParameter("userId", userId);
  559.         queryStatusOverDuePartner.setParameter("roleId", roleId);
  560.         queryStatusOverDuePartner.setParameter("datetime", DateUtil.dateTimeNow());
  561.         queryStatusOverDuePartner.setParameter("partnerId", partnerBillToId);
  562.  
  563.         Object statusOverDuePartner = null;
  564.         try {
  565.             statusOverDuePartner = queryStatusOverDuePartner.getSingleResult();
  566.         } catch (NoResultException e) {}
  567.         if (statusOverDuePartner!= null) {
  568.             flgOverDueDate = statusOverDuePartner.toString();
  569.         }
  570.         log.info("flgOverDueDate: "+flgOverDueDate);
  571.  
  572.  
  573.         // compare gross price product from m_sell_price_product_for_so
  574.         //select, trus loop, trus tampung di stringbuilder product_code dan gross_price kedua table
  575.         int preferredFractionDigits = 2;
  576.         NumberFormat formatter = NumberFormat.getNumberInstance();
  577.         formatter.setMinimumFractionDigits(preferredFractionDigits);
  578.        
  579.         QueryBuilder builderComparePrice = new QueryBuilder();
  580.         builderComparePrice.add(" SELECT A.product_id, f_get_product_code(A.product_id) AS product_code, B.curr_code AS curr_code_for_so, B.gross_sell_price AS sell_price_for_so, A.gross_sell_price AS so_sell_price, A.curr_code AS so_curr_code FROM ")
  581.         .add(SalesOrderItem.TABLE_NAME)
  582.         .add(" A INNER JOIN ").add(SellPriceProductForSo.TABLE_NAME).add(" B ON A.product_id = B.product_id ")
  583.         .add(" INNER JOIN ").add(SalesOrder.TABLE_NAME).add(" C ON A.so_id=C.so_id AND B.tenant_id = C.tenant_id AND B.ou_id = C.ou_id ")
  584.         .add(" INNER JOIN ").add(Partner.TABLE_NAME).add(" D ON C.partner_id = D.partner_id ")
  585.         .add(" WHERE A.gross_sell_price < B.gross_sell_price ")
  586.         .add(" AND B.curr_code = A.curr_code ")
  587.         .add(" AND C.doc_date BETWEEN B.date_from AND B.date_to ")
  588.         .add(" AND B.price_level = D.price_level ")
  589.         .add(" AND C.so_id = :soId ");
  590.  
  591.         Query queryComparePrice = salesOrderDao.createNativeQuery(builderComparePrice.toString());
  592.         queryComparePrice.setParameter("soId", soId);
  593.  
  594.         List<Object[]> resultComparePrice = queryComparePrice.getResultList();
  595.         StringBuilder comparePriceString = new StringBuilder();
  596.         Long productId  = GeneralConstants.NULL_REF_VALUE_LONG;
  597.         String productCode = GeneralConstants.EMPTY_VALUE;
  598.         String currCodeForSo = GeneralConstants.EMPTY_VALUE;
  599.         Double sellPriceForSo  = 0d;
  600.         Double soSellPrice  = 0d;
  601.         String soCurrCode = GeneralConstants.EMPTY_VALUE;
  602.         Long flagFaultComparePrice = 0L;
  603.  
  604.         if(resultComparePrice!=null && resultComparePrice.size()>0){
  605.             List<Dto> resultComparePriceList = new ArrayList<Dto>();
  606.             resultComparePriceList = DtoUtil.createDtoListFromArray(resultComparePrice, "productId", "productCode", "currCodeForSo", "sellPriceForSo", "soSellPrice", "soCurrCode");
  607.  
  608.             for (Dto dto : resultComparePriceList) {
  609.                 productId = dto.getLong("productId");
  610.                 productCode = dto.getString("productCode");
  611.                 currCodeForSo = dto.getString("currCodeForSo");
  612.                 sellPriceForSo = dto.getDouble("sellPriceForSo");
  613.                 soSellPrice = dto.getDouble("soSellPrice");
  614.                 soCurrCode = dto.getString("soCurrCode");
  615.  
  616.                 flagFaultComparePrice = 1L;
  617.                 comparePriceString.append("Product with code ").append(productCode).append(" SO Price(").append(soCurrCode).append(" ").append(formatter.format(Double.valueOf(soSellPrice))).append(") is below default product sell price(").append(currCodeForSo).append(" ").append(formatter.format(Double.valueOf(sellPriceForSo))).append(") \n");
  618.             }
  619.         }
  620.         outputDto.put("comparePriceString", comparePriceString.toString());
  621.         outputDto.put("flagFaultComparePrice", flagFaultComparePrice);
  622.         outputDto.put("flgPriceAfterDiscLowerThanMstrMinSellPrice", flgPriceAfterDiscLowerThanMstrMinSellPrice);
  623.         outputDto.put("flgChequeGiroReject", flgChequeGiroReject);
  624.         outputDto.put("chequeGiroNo", chequeGiroNo);
  625.         outputDto.put("amountLimit", amountLimit);
  626.         outputDto.put("totalAmountCreditLimitUsage", totalAmountCreditLimitUsage);
  627.         outputDto.put("amountLimitBalance", amountLimitBalance);
  628.         outputDto.put("flgOverDueDate", flgOverDueDate);
  629.  
  630.         log.info("outputDto Result: "+outputDto);
  631.  
  632.         return outputDto;
  633.      }
  634.  }
  635.  
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×