widana

FindTotalSummarySalesOrder

Nov 14th, 2016
115
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 8.65 KB | None | 0 0
  1. package org.jleaf.erp.sls.bo.salesorder;
  2.  
  3. import java.math.BigDecimal;
  4. import java.math.BigInteger;
  5. import java.util.List;
  6.  
  7. import javax.persistence.Query;
  8.  
  9. import org.jleaf.common.entity.ComboValue;
  10. import org.jleaf.core.AbstractBusinessFunction;
  11. import org.jleaf.core.BusinessFunction;
  12. import org.jleaf.core.Dto;
  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.QueryBuilder;
  18. import org.jleaf.erp.master.bo.ComboIdMasterConstants;
  19. import org.jleaf.erp.master.entity.Tax;
  20. import org.jleaf.erp.sls.bo.returninvoice.SubmitReturnSalesInvoiceForCland;
  21. import org.jleaf.erp.sls.dao.SalesOrderDao;
  22. import org.jleaf.erp.sls.entity.SalesOrder;
  23. import org.jleaf.erp.sls.entity.SalesOrderItem;
  24. import org.jleaf.util.Calc;
  25. import org.jleaf.util.DtoUtil;
  26. import org.jleaf.util.ValidationUtil;
  27. import org.slf4j.Logger;
  28. import org.slf4j.LoggerFactory;
  29. import org.springframework.beans.factory.annotation.Autowired;
  30. import org.springframework.stereotype.Service;
  31.  
  32. //@formatter:off
  33. @Service("findTotalSummarySalesOrder")
  34. @InfoIn(value = {
  35.         @Info(name = "tenantId", description = "tenant Id", type = Long.class),
  36.         @Info(name = "soId", description = "so id", type = Long.class)})
  37. @InfoOut(value = {
  38.         @Info(name = "totalItem", description = "total item", type = Long.class),
  39.         @Info(name = "totalItemAmount", description = "total item amount", type = Double.class),
  40.         @Info(name = "totalTaxAmount", description = "total tax amount", type = Double.class),
  41.         @Info(name = "grandTotal", description = "grandTotal", type = Double.class),
  42.         @Info(name = "currCode", description = "currCode", type = String.class),
  43.         @Info(name = "currPropKey", description = "currPropKey", type = String.class)
  44. })
  45. @ErrorList(errorKeys = {})
  46. //@formatter:on
  47. public class FindTotalSummarySalesOrderForCland extends AbstractBusinessFunction implements BusinessFunction {
  48.    
  49.     private final Logger log =  LoggerFactory.getLogger(FindTotalSummarySalesOrderForCland.class);
  50.    
  51.     @Autowired
  52.     SalesOrderDao salesOrderDao;
  53.  
  54.     @Override
  55.     public String getDescription() {
  56.         return "Find total summary sales order";
  57.     }
  58.  
  59.     @SuppressWarnings("unchecked")
  60.     @Override
  61.     public Dto execute(Dto inputDto) throws Exception {
  62.         Dto outputDto = new Dto();
  63.        
  64.         ValidationUtil.valDtoContainsKey(inputDto, "tenantId");
  65.         ValidationUtil.valDtoContainsKey(inputDto, "soId");
  66.  
  67.         Long tenantId = inputDto.getLong("tenantId");
  68.         Long soId = inputDto.getLong("soId");
  69.  
  70.         // get total products
  71.         QueryBuilder queryBuilderTotalItem = new QueryBuilder();
  72.         queryBuilderTotalItem.add("SELECT COUNT(a.so_item_id) AS totalItem FROM ")
  73.                              .add(SalesOrderItem.TABLE_NAME)
  74.                              .add(" a WHERE a.tenant_id = :tenantId ")
  75.                              .add(" AND a.so_id = :soId ");
  76.        
  77.         Query queryTotalItem = salesOrderDao.createNativeQuery(queryBuilderTotalItem.toString());
  78.         queryTotalItem.setParameter("tenantId", tenantId);
  79.         queryTotalItem.setParameter("soId", soId);
  80.        
  81.         List<Object> resultTotalItem = queryTotalItem.getResultList();
  82.         Long totalItem = 0L;
  83.        
  84.         if (resultTotalItem.size() > 0) {
  85.             totalItem = ((BigInteger) resultTotalItem.get(0)).longValue();
  86.         }
  87.        
  88.         outputDto.put("totalItem", totalItem);
  89.        
  90.         ////////////////////
  91.         QueryBuilder builderSummaryTotal = new QueryBuilder();
  92.        
  93.         builderSummaryTotal.add(" SELECT ")
  94.         .add(" COALESCE(SUM(A.qty_so),0) AS total_qty_item_so , COALESCE(A.qty_so * A.gross_sell_price,0) AS total_amount_so ")
  95.         .add(" FROM ")
  96.         .add(SalesOrderItem.TABLE_NAME).add(" A ")
  97.         .add(" WHERE true  ")
  98.         .add(" AND ")
  99.         .add(" A.tenant_id = :tenantId ")
  100.         .add(" AND ")
  101.         .add(" A.so_id = :soId ");
  102.        
  103.         Query resultQuerySummaryTotal = salesOrderDao.createNativeQuery(builderSummaryTotal.toString());
  104.         resultQuerySummaryTotal.setParameter("tenantId", tenantId);
  105.         resultQuerySummaryTotal.setParameter("soId", soId);
  106.        
  107.         List<Object[]> listForSummaryTotal = resultQuerySummaryTotal.getResultList();
  108.        
  109.         if(listForSummaryTotal.size() > 0) {
  110.             List<Dto> summaryTotal = DtoUtil.createDtoListFromArray(listForSummaryTotal, "totalQtyItemSo","totalAmountSo");
  111.             outputDto.put("totalQtyItemSo", summaryTotal.get(0).getDouble("totalQtyItemSo"));
  112.             outputDto.put("totalAmountSo", summaryTotal.get(0).getDouble("totalAmountSo"));
  113.         }
  114.         ////////////////////
  115.        
  116.        
  117.         // get currency for this sales order
  118.         QueryBuilder queryBuilderCurrency = new QueryBuilder();
  119.         queryBuilderCurrency.add("SELECT a.curr_code, b.prop_key FROM ")
  120.                             .add(SalesOrder.TABLE_NAME)
  121.                             .add(" a INNER JOIN ")
  122.                             .add(ComboValue.TABLE_NAME)
  123.                             .add(" b ON a.curr_code = b.code AND b.combo_id = :COMBO_CURRENCY ")
  124.                             .add(" WHERE a.tenant_id = :tenantId ")
  125.                             .add(" AND a.so_id = :soId ");
  126.        
  127.         Query queryCurrency = salesOrderDao.createNativeQuery(queryBuilderCurrency.toString());
  128.         queryCurrency.setParameter("tenantId", tenantId);
  129.         queryCurrency.setParameter("soId", soId);
  130.         queryCurrency.setParameter("COMBO_CURRENCY", ComboIdMasterConstants.COMBO_CURRENCY);
  131.        
  132.         List<Object[]> resultCurrency = queryCurrency.getResultList();
  133.        
  134.         if (resultCurrency.size() > 0) {
  135.             List<Dto> currency = DtoUtil.createDtoListFromArray(resultCurrency, "currCode", "currPropKey");
  136.             outputDto.put("currCode", currency.get(0).getString("currCode"));
  137.             outputDto.put("currPropKey", currency.get(0).getString("currPropKey"));
  138.         }
  139.        
  140.         Calc totalItemAmount = new Calc(0);
  141.         Calc totalDiscountAmount = new Calc(0);
  142.         if (totalItem > 0) {
  143.             // get total nett item amount (kena pajak + tidak kena pajak) dan grand total
  144.             QueryBuilder queryBuilderTotal = new QueryBuilder();
  145.             queryBuilderTotal.add("SELECT SUM(a.nett_item_amount) AS totalItemAmount, SUM(a.discount_amount) * a.qty_so AS total_discount_amount FROM ")
  146.                              .add(SalesOrderItem.TABLE_NAME)
  147.                              .add(" a WHERE a.tenant_id = :tenantId ")
  148.                              .add(" AND a.so_id = :soId ")
  149.                              .add(" GROUP BY a.qty_so ");
  150.            
  151.             Query queryTotal = salesOrderDao.createNativeQuery(queryBuilderTotal.toString());
  152.             queryTotal.setParameter("tenantId", tenantId);
  153.             queryTotal.setParameter("soId", soId);
  154.            
  155.             List<Dto> resultTotal = DtoUtil.createDtoListFromArray(queryTotal.getResultList(), "totalItemAmount", "totalDiscountAmount");
  156.            
  157.             if (resultTotal != null && !resultTotal.isEmpty()) {
  158.                 for (Dto dto : resultTotal) {
  159.                     Double sumItemAmount = Double.valueOf(dto.getString("totalItemAmount"));
  160.                     Double sumDiscountAmount = Double.valueOf(dto.getString("totalDiscountAmount"));
  161.                     totalItemAmount = totalItemAmount.add(sumItemAmount);
  162.                     totalDiscountAmount = totalDiscountAmount.add(sumDiscountAmount);
  163.                 }
  164.             }
  165.         }
  166.  
  167.         outputDto.put("totalItemAmount", totalItemAmount.doubleValue());
  168.         outputDto.put("totalDiscountAmount", totalDiscountAmount.doubleValue());
  169.        
  170.         Calc totalTaxAmountBigDecimal = new Calc(0);
  171.         if (totalItem > 0) {
  172.             QueryBuilder builderTotalTax = new QueryBuilder();
  173.             builderTotalTax.add("SELECT a.tax_id, d.flg_amount, f_tax_rounding(a.tenant_id, SUM(a.nett_item_amount), a.tax_percentage) AS sumTaxAmount FROM ")
  174.             .add(SalesOrderItem.TABLE_NAME)
  175.             .add(" a INNER JOIN ")
  176.             .add(SalesOrder.TABLE_NAME)
  177.             .add(" b ON a.tenant_id = b.tenant_id AND a.so_id = b.so_id ")
  178.             .add(" INNER JOIN ")
  179.             .add(Tax.TABLE_NAME)
  180.             .add(" d ON a.tax_id = d.tax_id AND a.tenant_id = d.tenant_id ")
  181.             .add(" WHERE a.tenant_id = :tenantId AND a.so_id = :soId ")
  182.             .add(" GROUP BY a.tax_id, d.flg_amount, a.tax_percentage, a.tenant_id ");
  183.  
  184.             Query queryTotalTax = salesOrderDao.createNativeQuery(builderTotalTax.toString());
  185.             queryTotalTax.setParameter("tenantId", tenantId);
  186.             queryTotalTax.setParameter("soId", soId);
  187.  
  188.             List<Object[]> resultTotalTax = queryTotalTax.getResultList();
  189.  
  190.             if (resultTotalTax.size() > 0) {
  191.                 List<Dto> returnList = DtoUtil.createDtoListFromArray(resultTotalTax, "taxId", "flgAmount", "sumTaxAmount");
  192.  
  193.                 for (Dto dto : returnList) {
  194.                     Double flgAmount = Double.valueOf(dto.getString("flgAmount"));
  195.                     Double sumTaxAmount = Double.valueOf(dto.getString("sumTaxAmount"));
  196.                     Calc taxBigDecimal = new Calc(sumTaxAmount).multiply(flgAmount);
  197.                     totalTaxAmountBigDecimal = totalTaxAmountBigDecimal.add(taxBigDecimal);
  198.                 }
  199.  
  200.             }
  201.         }
  202.        
  203.         Double grandTotal = totalItemAmount.add(totalTaxAmountBigDecimal.doubleValue()).getValue().doubleValue();
  204.        
  205.         outputDto.put("totalTaxAmount", totalTaxAmountBigDecimal.doubleValue());
  206.         Double grandTotalWithTax = new Calc(grandTotal).doubleValue();
  207.         outputDto.put("grandTotal", grandTotalWithTax);
  208.        
  209.         log.info(" LOAD outputDto " + outputDto);
  210.  
  211.        
  212.         return outputDto;
  213.     }
  214.  
  215. }
Advertisement
Add Comment
Please, Sign In to add comment