API tools faq
paste
Advertisement
Guest User

WaveStatusMonitorSCH

a guest
Mar 30th, 2018
89
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 20.78 KB | None | 0 0
raw download clone embed print report
  1. // $Id: Sch.xml 2808 2014-10-27 05:13:07Z noriyuki_sawa_ha.daifuku.co.jp $
  2. package jp.co.daifuku.wms.gadgets.gadget.wavestatus;
  3.  
  4.  
  5.  
  6. /*
  7.  * Copyright(c) 2000-2013 DAIFUKU Co.,Ltd. All Rights Reserved.
  8.  *
  9.  * This software is the proprietary information of DAIFUKU Co.,Ltd.
  10.  * Use is subject to license terms.
  11.  */
  12. import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveStatusMonitorSCHParams.ASPG_BAR;
  13. import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveStatusMonitorSCHParams.ASPG_BAR_MAX;
  14. import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveStatusMonitorSCHParams.PROGRESS_BAR1_DATA1;
  15. import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveStatusMonitorSCHParams.PROGRESS_MAX;
  16. import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveStatusMonitorSCHParams.SRPG_BAR;
  17. import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveStatusMonitorSCHParams.SRPG_BAR_MAX;
  18. import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveStatusMonitorSCHParams.TXT_ASCASEPICK;
  19. import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveStatusMonitorSCHParams.TXT_ASPALLETPICK;
  20. import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveStatusMonitorSCHParams.TXT_ASPIECEPICKING;
  21. import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveStatusMonitorSCHParams.TXT_SRCASEPICK;
  22. import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveStatusMonitorSCHParams.TXT_SRPALLETPICK;
  23. import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveStatusMonitorSCHParams.TXT_SRPIECEPICKING;
  24. import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveStatusMonitorSCHParams.TXT_STATUS;
  25. import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveStatusMonitorSCHParams.TXT_WAVENUM;
  26. import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveStatusMonitorSCHParams.TXT_WORKDATE;
  27.  
  28. import java.sql.Connection;
  29. import java.sql.SQLException;
  30. import java.util.Date;
  31. import java.util.Locale;
  32.  
  33. import jp.co.daifuku.dgate.dashboard.function.AbstractParams;
  34. import jp.co.daifuku.dgate.dashboard.function.AbstractSCH;
  35. import jp.co.daifuku.dgate.dashboard.function.GadgetUserInfo;
  36. import jp.co.daifuku.dgate.util.DgateUserResource;
  37. import jp.co.daifuku.wms7.base.util.FormatUtil;
  38. import jp.co.daifuku.wms7.dbhandler.DBConstants;
  39. import jp.co.daifuku.wms7.dbhandler.DBConstants.dnretrievalPlanStatusFlag;
  40. import jp.co.daifuku.wms7.dbhandler.DBConstants.dnshipworkinfo;
  41. import jp.co.daifuku.wms7.dbhandler.DBConstants.dnwave;
  42. import jp.co.daifuku.wms7.dbhandler.DBConstants.dnwaveStatus;
  43. import jp.co.daifuku.wms7.dbhandler.DBConstants.dnworkinfo;
  44. import jp.co.daifuku.wms7.dbhandler.DBConstants.dnworkinfoTypes;
  45. import jp.co.daifuku.wms7.dbhandler.DBConstants.dnworkinfoloadwork;
  46. import jp.co.daifuku.wms7.dbhandler.DBHandler;
  47. import jp.co.daifuku.wms7.dbhandler.DBRecord;
  48. import jp.co.daifuku.wms7.dbhandler.DBStores;
  49. import jp.co.daifuku.wms7.dbhandler.SQLQuery;
  50.  
  51. /**
  52.  * Scheduler(SCH) class  generated by BusiTune
  53.  *
  54.  * @version $Revision: 2808 $, $Date: 2014-10-27 14:13:07 +0900 (譛�, 27 10 2014) $
  55.  * @author  Dgate Designer.
  56.  * @author  Last commit: $Author: noriyuki_sawa_ha.daifuku.co.jp $
  57.  */
  58. public class WaveStatusMonitorSCH
  59.         extends AbstractSCH
  60. {
  61.     //------------------------------------------------------------
  62.     // fields (upper case only)
  63.     //------------------------------------------------------------
  64.  
  65.     //------------------------------------------------------------
  66.     // class variables (prefix '$')
  67.     //------------------------------------------------------------
  68.  
  69.     //------------------------------------------------------------
  70.     // instance variables (prefix '_')
  71.     //------------------------------------------------------------
  72.  
  73.     //------------------------------------------------------------
  74.     // constructors
  75.     //------------------------------------------------------------
  76.     /**
  77.      * Constructor to create SCH object
  78.      * @param conn Database Connection
  79.      * @param locale Browser Locale
  80.      * @param userInfo UserInfo
  81.      * @throws SQLException
  82.      */
  83.     public WaveStatusMonitorSCH(Connection conn, Locale locale, GadgetUserInfo userInfo) throws SQLException
  84.     {
  85.         //super(conn, locale, userInfo);
  86.         super(java.sql.DriverManager.getConnection("jdbc:oracle:thin:@172.16.142.101:1521:orcl", "wms", "wms"), locale, userInfo);
  87.     }
  88.     //------------------------------------------------------------
  89.     // public methods
  90.     //------------------------------------------------------------
  91.     /**
  92.      *
  93.      * @return
  94.      */
  95.     public AbstractParams query()
  96.     {
  97.         WaveStatusMonitorSCHParams params = new WaveStatusMonitorSCHParams();
  98.         initiate(params);
  99.         SQLQuery _sqlQuery=createSqlOfWorkStatus();
  100.         try
  101.         {
  102.             DBHandler handler = new DBHandler();
  103.             DBStores dbWorkStatus = null;
  104.             DBStores dbProgress = null;        
  105.             dbWorkStatus = handler.select(getConnection(), _sqlQuery);
  106.             int TOTAL_WORK_QTY = 0,TOTAL_RESULT_QTY=0;
  107.             int PIECES_PICK_QTY=0,CASE_PICK_QTY=0,PALLET_PICK_QTY=0;
  108.             int TOTAL_PIECES_PICK_QTY=0,TOTAL_PALLET_PICK_QTY=0,TOTAL_CASE_PICK_QTY=0;
  109.             Date workDate= new Date();
  110.             params.set(TXT_WORKDATE, FormatUtil.convertDateToString(workDate,DBConstants.WMS_FORMATTER.DATE_FORMAT));
  111.             if (!dbWorkStatus.isEmpty())
  112.             {
  113.                  for (DBRecord record : dbWorkStatus)
  114.                  {
  115.                      //params.set(TXT_WORKDATE, FormatUtil.convertDateToString(record.getDate("WAVE_START_TIME"),DBConstants.WMS_FORMATTER.DATE_FORMAT));
  116.                      params.set(TXT_WAVENUM, record.getString("WAVE_NO"));
  117.                      params.set(TXT_STATUS, record.getInt("STATUS_FLAG"));
  118.                      params.set(PROGRESS_BAR1_DATA1, record.getInt("RESULT_QTY")*100/record.getInt("TOTAL_PLAN_QTY"));
  119.                      params.set(PROGRESS_MAX, 100);
  120.                      _sqlQuery.clear();
  121.                      _sqlQuery=createSqlOfProgress(record.getString("WAVE_SEQ"));
  122.                      dbProgress=handler.select(getConnection(), _sqlQuery);
  123.                    if(!dbProgress.isEmpty()) {
  124.                      for (DBRecord record1 : dbProgress) {
  125.                          if(record1.getInt("WORK_TYPE")==DBConstants.dnworkinfoTypes.WORK_TYPE.RETRIEVAL
  126.                                  ||record1.getInt("WORK_TYPE")==DBConstants.dnworkinfoTypes.WORK_TYPE.RETRIEVAL_SHUTTLE_CART
  127.                                  //2018/03/30 EDIT:START
  128.                                  ||record1.getInt("WORK_TYPE")==DBConstants.dnworkinfoTypes.WORK_TYPE.PIECE_PICKING)  {
  129.                                  //2018/03/30 EDIT:END
  130.                              TOTAL_WORK_QTY+=record1.getInt("TOTAL_WORK_QTY");
  131.                              TOTAL_RESULT_QTY+=record1.getInt("TOTAL_RESULT_QTY");
  132.                              PIECES_PICK_QTY+=record1.getInt("PIECES_PICK_QTY");
  133.                              TOTAL_PIECES_PICK_QTY+=record1.getInt("TOTAL_PIECES_PICK_QTY");
  134.                              CASE_PICK_QTY+=record1.getInt("CASE_PICK_QTY");
  135.                              TOTAL_CASE_PICK_QTY+=record1.getInt("TOTAL_CASE_PICK_QTY");
  136.                              PALLET_PICK_QTY+=record1.getInt("PALLET_PICK_QTY");
  137.                              TOTAL_PALLET_PICK_QTY+=record1.getInt("TOTAL_PALLET_PICK_QTY");
  138.                              
  139.                          }
  140.                          //2018/03/30 EDIT:START
  141.                          else if(record1.getInt("WORK_TYPE")==DBConstants.dnworkinfoTypes.WORK_TYPE.SELECTIVE_RACK_PICKING) {
  142.                           //2018/03/30 EDIT:END
  143.                              params.set(SRPG_BAR, record1.getInt("TOTAL_RESULT_QTY")*100/record1.getInt("TOTAL_WORK_QTY"));
  144.                              params.set(SRPG_BAR_MAX,100);
  145.                              params.set(TXT_SRPIECEPICKING,record1.getInt("PIECES_PICK_QTY")+"/"+record1.getInt("TOTAL_PIECES_PICK_QTY"));
  146.                              params.set(TXT_SRCASEPICK,record1.getInt("CASE_PICK_QTY")+"/"+record1.getInt("TOTAL_CASE_PICK_QTY"));
  147.                              params.set(TXT_SRPALLETPICK,record1.getInt("PALLET_PICK_QTY")+"/"+record1.getInt("TOTAL_PALLET_PICK_QTY"));
  148.                          }
  149.                        }
  150.                      }
  151.                      params.set(ASPG_BAR, TOTAL_RESULT_QTY*100/TOTAL_WORK_QTY);
  152.                      params.set(ASPG_BAR_MAX,100);
  153.                      params.set(TXT_ASPIECEPICKING,PIECES_PICK_QTY+"/"+TOTAL_PIECES_PICK_QTY);
  154.                      params.set(TXT_ASCASEPICK,CASE_PICK_QTY+"/"+TOTAL_CASE_PICK_QTY);
  155.                      params.set(TXT_ASPALLETPICK,PALLET_PICK_QTY+"/"+TOTAL_PALLET_PICK_QTY);
  156.                  }
  157.             }
  158.         }catch (Exception e) {
  159.            
  160.         }
  161.         return params;
  162.     }
  163.  
  164.     //------------------------------------------------------------
  165.     // accessor methods
  166.     //------------------------------------------------------------
  167.  
  168.     //------------------------------------------------------------
  169.     // package methods
  170.     //------------------------------------------------------------
  171.  
  172.     //------------------------------------------------------------
  173.     // protected methods
  174.     //------------------------------------------------------------
  175.  
  176.     //------------------------------------------------------------
  177.     // private methods
  178.     //------------------------------------------------------------
  179.     private void initiate(WaveStatusMonitorSCHParams params) {
  180.         params.set(TXT_WORKDATE, null);
  181.         params.set(TXT_WAVENUM, null);
  182.         params.set(TXT_STATUS, null);
  183.         params.set(PROGRESS_BAR1_DATA1,0);
  184.         params.set(PROGRESS_MAX, 100);
  185.         params.set(ASPG_BAR, 0);
  186.         params.set(ASPG_BAR_MAX,100);
  187.         params.set(TXT_ASPIECEPICKING,0+"/"+0);
  188.         params.set(TXT_ASCASEPICK,0+"/"+0);
  189.         params.set(TXT_ASPALLETPICK,0+"/"+0);
  190.         params.set(SRPG_BAR, 0);
  191.         params.set(SRPG_BAR_MAX,100);
  192.         params.set(TXT_SRPIECEPICKING,0+"/"+0);
  193.         params.set(TXT_SRCASEPICK,0+"/"+0);
  194.         params.set(TXT_SRPALLETPICK,0+"/"+0);
  195.     }
  196.     private SQLQuery createSqlOfWorkStatus()
  197.     {
  198.         SQLQuery sqlQuery = new SQLQuery();
  199.         sqlQuery.append("SELECT RetrivalPlan.WAVE_SEQ,");
  200.         sqlQuery.append("       RetrivalPlan.WAVE_NO,");
  201.         sqlQuery.append("       RetrivalPlan.STATUS_FLAG,");
  202.         sqlQuery.append("       (SELECT SUM(DNRETRIEVALPLAN.PLAN_QTY)");
  203.         sqlQuery.append("               FROM DNRETRIEVALPLAN");
  204.         sqlQuery.append("        WHERE DNRETRIEVALPLAN.WAVE_SEQ = RetrivalPlan.WAVE_SEQ");
  205.         sqlQuery.append("        AND DNRETRIEVALPLAN.STATUS_FLAG != ").append(dnretrievalPlanStatusFlag.STATUS_FLAG.DELTED);
  206.         sqlQuery.append("        GROUP BY DNRETRIEVALPLAN.WAVE_SEQ) TOTAL_PLAN_QTY,");
  207.         sqlQuery.append("         NVL((SELECT SUM(DNSHIPWORKINFO.RESULT_QTY)");
  208.         sqlQuery.append("                         FROM DNSHIPWORKINFO");
  209.         sqlQuery.append("                  WHERE DNSHIPWORKINFO.WAVE_SEQ = RetrivalPlan.WAVE_SEQ");
  210.         sqlQuery.append("                  AND DNSHIPWORKINFO.STATUS_FLAG = ").append(dnshipworkinfo.STATUS_FLAG.COMPLETED).append(" ),0) AS RESULT_QTY ");
  211.         sqlQuery.append("FROM (SELECT*FROM (SELECT  DNWAVE.WAVE_SEQ,");
  212.         sqlQuery.append("                           DNWAVE.WAVE_NO,");
  213.         sqlQuery.append("                           DNWAVE.STATUS_FLAG");
  214.         sqlQuery.append("                           FROM DNWAVE");
  215.         sqlQuery.append("                   WHERE ( DNWAVE.STATUS_FLAG = ").append(dnwaveStatus.DNWAVE_STATUS_TYPE.ONQUEUE);
  216.         sqlQuery.append("                   OR DNWAVE.STATUS_FLAG = ").append(dnwaveStatus.DNWAVE_STATUS_TYPE.WORKING);
  217.         sqlQuery.append("                   OR DNWAVE.STATUS_FLAG = ").append(dnwaveStatus.DNWAVE_STATUS_TYPE.SHORTAGE).append(" ) ");
  218.         sqlQuery.append("                   AND   DNWAVE.WAVE_TYPE = ").append(dnwaveStatus.DNWAVE_TYPE.WAVE);
  219.         sqlQuery.append("                   ORDER BY DNWAVE.STATUS_FLAG DESC,");
  220.         sqlQuery.append("                   DNWAVE.SHORTAGE_ALLOC_FLAG DESC,");
  221.         sqlQuery.append("                   DNWAVE.WAVE_START_TIME ASC)");
  222.         sqlQuery.append("      WHERE ROWNUM <= 1) RetrivalPlan");
  223.         return sqlQuery;
  224.     }
  225.     private SQLQuery createSqlOfProgress(String wave_seq)
  226.     {
  227.         SQLQuery sqlQuery = new SQLQuery();
  228.         sqlQuery.append("SELECT CASE WHEN PROGRESS.WORK_TYPE= ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.RFT_CONSOLIDATE));
  229.         sqlQuery.append("                 AND PROGRESS.PICKING_TYPE= ").append(dnshipworkinfo.PICKING_TYPE.BASKET);
  230.         sqlQuery.append("            THEN ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.PIECE_PICKING));
  231.         sqlQuery.append("            WHEN PROGRESS.WORK_TYPE= ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.RFT_CONSOLIDATE));
  232.         sqlQuery.append("                 AND PROGRESS.PICKING_TYPE= ").append(dnshipworkinfo.PICKING_TYPE.CASE_INDUCTION);
  233.         sqlQuery.append("            THEN ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.RETRIEVAL_SHUTTLE_CART));
  234.         sqlQuery.append("       ELSE PROGRESS.WORK_TYPE END WORK_TYPE,");
  235.         sqlQuery.append("       SUM(PROGRESS.RESULT_QTY) AS TOTAL_RESULT_QTY,");
  236.         sqlQuery.append("       SUM(PROGRESS.WORK_QTY) AS TOTAL_WORK_QTY,");
  237.         sqlQuery.append("       SUM(PROGRESS.PIECES_PICK) AS PIECES_PICK_QTY,");
  238.         sqlQuery.append("       SUM(PROGRESS.TOTAL_PIECES_PICK) AS TOTAL_PIECES_PICK_QTY,");
  239.         sqlQuery.append("       SUM(PROGRESS.CASE_PICK) AS CASE_PICK_QTY,");
  240.         sqlQuery.append("       SUM(PROGRESS.TOTAL_CASE_PICK) AS TOTAL_CASE_PICK_QTY,");
  241.         sqlQuery.append("       SUM(PROGRESS.PALLET_PICK) AS PALLET_PICK_QTY,");
  242.         sqlQuery.append("       SUM(PROGRESS.TOTAL_PALLET_PICK) AS TOTAL_PALLET_PICK_QTY");
  243.         sqlQuery.append("FROM (SELECT DNWORKINFO.WORK_TYPE,");
  244.         sqlQuery.append("             CASE WHEN DNWORKINFO.WORK_TYPE != ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.RFT_CONSOLIDATE));
  245.         sqlQuery.append("                  THEN DNWORKINFO.WORK_QTY ELSE 0  ");
  246.         sqlQuery.append("             END WORK_QTY,");
  247.         sqlQuery.append("             DNSHIPWORKINFO.PICKING_TYPE,");
  248.         sqlQuery.append("             CASE");
  249.         sqlQuery.append("               WHEN DNSHIPWORKINFO.STATUS_FLAG = ").append(dnshipworkinfo.STATUS_FLAG.COMPLETED);
  250.         sqlQuery.append("               THEN DNSHIPWORKINFO.RESULT_QTY ELSE 0");
  251.         sqlQuery.append("             END AS RESULT_QTY,");
  252.         sqlQuery.append("             CASE");
  253.         sqlQuery.append("               WHEN (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.PIECE_PICKING));
  254.         sqlQuery.append("                    OR (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.RFT_CONSOLIDATE)).append(" AND DNSHIPWORKINFO.PICKING_TYPE = ").append(dnshipworkinfo.PICKING_TYPE.BASKET).append(" ) ");
  255.         sqlQuery.append("                    OR (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.SELECTIVE_RACK_PICKING)).append(" AND (DNWORKINFO.WORK_QTY != DNSTOCK.STOCK_QTY OR DNSTOCK.STOCK_QTY != DMITEM.PIECE_PER_PALLET ) AND MOD(DNWORKINFO.WORK_QTY,NVL(DMITEM.PIECE_PER_CASE,1)) > 0)) AND DNSHIPWORKINFO.STATUS_FLAG = ").append(dnshipworkinfo.STATUS_FLAG.COMPLETED);
  256.         sqlQuery.append("               THEN DNSHIPWORKINFO.RESULT_QTY ELSE 0");
  257.         sqlQuery.append("             END AS PIECES_PICK,");
  258.         sqlQuery.append("             CASE");
  259.         sqlQuery.append("               WHEN (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.PIECE_PICKING));
  260.         sqlQuery.append("                    OR (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.SELECTIVE_RACK_PICKING)).append(" AND (DNWORKINFO.WORK_QTY != DNSTOCK.STOCK_QTY OR DNSTOCK.STOCK_QTY != DMITEM.PIECE_PER_PALLET ) AND MOD(DNWORKINFO.WORK_QTY,NVL(DMITEM.PIECE_PER_CASE,1)) > 0))  ");
  261.         sqlQuery.append("               THEN DNWORKINFO.WORK_QTY ELSE 0");
  262.         sqlQuery.append("             END AS TOTAL_PIECES_PICK,");
  263.         sqlQuery.append("             CASE");
  264.         sqlQuery.append("               WHEN (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.RETRIEVAL_SHUTTLE_CART));
  265.         sqlQuery.append("                    OR (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.RFT_CONSOLIDATE)).append(" AND DNSHIPWORKINFO.PICKING_TYPE = ").append(dnshipworkinfo.PICKING_TYPE.CASE_INDUCTION).append(" ) ");
  266.         sqlQuery.append("                    OR (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.SELECTIVE_RACK_PICKING)).append(" AND (DNWORKINFO.WORK_QTY != DNSTOCK.STOCK_QTY OR DNSTOCK.STOCK_QTY != DMITEM.PIECE_PER_PALLET ) AND MOD(DNWORKINFO.WORK_QTY,NVL(DMITEM.PIECE_PER_CASE,1)) = 0)) AND DNSHIPWORKINFO.STATUS_FLAG = ").append(dnshipworkinfo.STATUS_FLAG.COMPLETED);
  267.         sqlQuery.append("               THEN FLOOR(DNSHIPWORKINFO.RESULT_QTY / NVL(DMITEM.PIECE_PER_CASE,1)) ELSE 0");
  268.         sqlQuery.append("             END AS CASE_PICK,");
  269.         sqlQuery.append("             CASE");
  270.         sqlQuery.append("               WHEN (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.RETRIEVAL_SHUTTLE_CART));
  271.         sqlQuery.append("                    OR (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.SELECTIVE_RACK_PICKING)).append(" AND (DNWORKINFO.WORK_QTY != DNSTOCK.STOCK_QTY OR DNSTOCK.STOCK_QTY != DMITEM.PIECE_PER_PALLET ) AND MOD(DNWORKINFO.WORK_QTY,NVL(DMITEM.PIECE_PER_CASE,1)) = 0))  ");
  272.         sqlQuery.append("               THEN FLOOR(DNWORKINFO.WORK_QTY / NVL(DMITEM.PIECE_PER_CASE,1)) ELSE 0");
  273.         sqlQuery.append("             END AS TOTAL_CASE_PICK,");
  274.         sqlQuery.append("             CASE");
  275.         sqlQuery.append("               WHEN (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.RETRIEVAL));
  276.         sqlQuery.append("                    OR (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.SELECTIVE_RACK_PICKING)).append(" AND (DNWORKINFO.WORK_QTY = DNSTOCK.STOCK_QTY AND DNSTOCK.STOCK_QTY = DMITEM.PIECE_PER_PALLET))) AND DNSHIPWORKINFO.STATUS_FLAG = ").append(dnshipworkinfo.STATUS_FLAG.COMPLETED);
  277.         sqlQuery.append("               THEN FLOOR(DNSHIPWORKINFO.RESULT_QTY/NVL(DMITEM.PIECE_PER_PALLET,1)) ELSE 0");  
  278.         sqlQuery.append("             END AS PALLET_PICK,");
  279.         sqlQuery.append("             CASE");
  280.         sqlQuery.append("               WHEN (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.RETRIEVAL));
  281.         sqlQuery.append("                    OR (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.SELECTIVE_RACK_PICKING)).append(" AND (DNWORKINFO.WORK_QTY = DNSTOCK.STOCK_QTY AND DNSTOCK.STOCK_QTY = DMITEM.PIECE_PER_PALLET))) ");
  282.         sqlQuery.append("               THEN FLOOR(DNWORKINFO.WORK_QTY/NVL(DMITEM.PIECE_PER_PALLET,1)) ELSE 0");  
  283.         sqlQuery.append("             END AS TOTAL_PALLET_PICK");
  284.         sqlQuery.append("       FROM DNWAVE JOIN DNRETRIEVALPLAN ON DNWAVE.WAVE_SEQ=DNRETRIEVALPLAN.WAVE_SEQ ");
  285.         sqlQuery.append("            LEFT JOIN DNWORKINFO ON DNRETRIEVALPLAN.PLAN_UKEY = DNWORKINFO.PLAN_UKEY ").append(" AND DNWORKINFO.LOAD_WORK_STATUS != ").append(dnworkinfoTypes.LOAD_WORK_STATUS.DELETE);    
  286.         sqlQuery.append("            JOIN DNSTOCK ON DNSTOCK.STOCK_ID=DNWORKINFO.STOCK_ID");
  287.         sqlQuery.append("            JOIN DMITEM  ON DMITEM.ITEM_CODE=DNSTOCK.ITEM_CODE");
  288.         sqlQuery.append("            LEFT JOIN DNSHIPWORKINFO ON DNSHIPWORKINFO.WAVE_SEQ = DNWAVE.WAVE_SEQ AND DNWORKINFO.STOCK_ID=DNSHIPWORKINFO.STOCK_ID");
  289.         sqlQuery.append("       WHERE DNWAVE.WAVE_SEQ = '"+wave_seq+"'");
  290.         sqlQuery.append("       AND   DNWAVE.WAVE_TYPE = ").append(dnwaveStatus.DNWAVE_TYPE.WAVE);
  291.         sqlQuery.append("       AND ( DNWAVE.STATUS_FLAG = ").append(dnwaveStatus.DNWAVE_STATUS_TYPE.ONQUEUE);
  292.         sqlQuery.append("           OR DNWAVE.STATUS_FLAG = ").append(dnwaveStatus.DNWAVE_STATUS_TYPE.WORKING);
  293.         sqlQuery.append("           OR DNWAVE.STATUS_FLAG = ").append(dnwaveStatus.DNWAVE_STATUS_TYPE.SHORTAGE).append(" ) ");
  294.         sqlQuery.append("       AND DNRETRIEVALPLAN.STATUS_FLAG != ").append(dnretrievalPlanStatusFlag.STATUS_FLAG.DELTED);
  295.         sqlQuery.append("       AND ( DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.RETRIEVAL));
  296.         sqlQuery.append("           OR DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.RETRIEVAL_SHUTTLE_CART));
  297.         sqlQuery.append("           OR DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.SELECTIVE_RACK_PICKING));
  298.         sqlQuery.append("           OR DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.PIECE_PICKING));
  299.         sqlQuery.append("           OR DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.RFT_CONSOLIDATE)).append(" ))PROGRESS ");
  300.         sqlQuery.append("GROUP BY PROGRESS.WORK_TYPE,PROGRESS.PICKING_TYPE");
  301.         return sqlQuery;
  302.     }
  303.     //------------------------------------------------------------
  304.     // utility methods
  305.     //------------------------------------------------------------
  306.     /**
  307.      * Returns current repository info for this class
  308.      * @return version
  309.      */
  310.     public static String getVersion()
  311.     {
  312.         return "$Id: Sch.xml 2808 2014-10-27 05:13:07Z noriyuki_sawa_ha.daifuku.co.jp $";
  313.     }
  314. }
  315. //end of class
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!