WaveListSCH

// $Id: Sch.xml 2808 2014-10-27 05:13:07Z noriyuki_sawa_ha.daifuku.co.jp $
package jp.co.daifuku.wms.gadgets.gadget.wavestatus;

/*
 * Copyright(c) 2000-2013 DAIFUKU Co.,Ltd. All Rights Reserved.
 *
 * This software is the proprietary information of DAIFUKU Co.,Ltd.
 * Use is subject to license terms.
 */
import static jp.co.daifuku.wms.gadgets.gadget.wavestatus.WaveListSCHParams.*;

import java.net.URL;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Locale;
import jp.co.daifuku.dgate.dashboard.function.AbstractParams;
import jp.co.daifuku.dgate.dashboard.function.AbstractSCH;
import jp.co.daifuku.dgate.dashboard.function.GadgetUserInfo;
import jp.co.daifuku.dgate.dashboard.ui.control.TextBlock;
import jp.co.daifuku.dgate.dashboard.ui.frame.Gadget;
import jp.co.daifuku.dgate.util.DgateUserResource;
import jp.co.daifuku.wms7.dbhandler.DBConstants;
import jp.co.daifuku.wms7.dbhandler.DBHandler;
import jp.co.daifuku.wms7.dbhandler.DBRecord;
import jp.co.daifuku.wms7.dbhandler.DBStores;
import jp.co.daifuku.wms7.dbhandler.SQLQuery;
import jp.co.daifuku.wms7.dbhandler.DBConstants.dnretrievalPlanStatusFlag;
import jp.co.daifuku.wms7.dbhandler.DBConstants.dnwaveStatus;
import jp.co.daifuku.wms7.dbhandler.DBConstants.dnworkinfoTypes;
import jp.co.daifuku.wms7.dbhandler.DBConstants.dnworkinfoloadwork;
import jp.co.daifuku.wms7.dgatecollect.GadgetUtil;
import jp.co.daifuku.dgate.Resources;
import jp.co.daifuku.dgate.action.dashboard.GetGadgetList;
/**
 * Scheduler(SCH) class  generated by BusiTune
 *
 * @version $Revision: 2808 $, $Date: 2014-10-27 14:13:07 +0900 (隴幢ｿｽ, 27 10 2014) $
 * @author  Dgate Designer.
 * @author  Last commit: $Author: noriyuki_sawa_ha.daifuku.co.jp $
 */
public class WaveListSCH
        extends AbstractSCH
{
    //------------------------------------------------------------
    // fields (upper case only)
    //------------------------------------------------------------

    //------------------------------------------------------------
    // class variables (prefix '$')
    //------------------------------------------------------------

    //------------------------------------------------------------
    // instance variables (prefix '_')
    //------------------------------------------------------------

    //------------------------------------------------------------
    // constructors
    //------------------------------------------------------------
    /**
     * Constructor to create SCH object
     * @param conn Database Connection
     * @param locale Browser Locale
     * @param userInfo UserInfo
     * @throws SQLException
     */
    public WaveListSCH(Connection conn, Locale locale, GadgetUserInfo userInfo) throws SQLException
    {
     //super(conn, locale, userInfo);
     super(java.sql.DriverManager.getConnection("jdbc:oracle:thin:@172.16.142.101:1521:orcl", "wms", "wms"), locale, userInfo);
    }

    //------------------------------------------------------------
    // public methods
    //------------------------------------------------------------
    /**
     *
     * @return
     */
    public AbstractParams query()
    {
        WaveListSCHParams params = new WaveListSCHParams();
        clearList(params);
        SQLQuery _sqlQuery=createSqlOfWorkStatus();
        try
        {
            DBHandler handler = new DBHandler();
            DBStores dbWorkList = null;
            dbWorkList = handler.select(getConnection(), _sqlQuery,3);
            if (!dbWorkList.isEmpty())
            {
                int i=0;
                for (DBRecord record : dbWorkList)
                {
                    switch(i){
                        case 0:
                            params.set(LIST_WAVENUM1,record.getString("WAVE_NO"));
                            params.set(LIST_PIECEPICKNUM1,record.getString("TOTAL_PIECES_PICK_QTY")  );
                            params.set(LIST_CASEPICKNUM1,record.getString("TOTAL_CASE_PICK_QTY")  );
                            params.set(LIST_PALLETPICKNUM1,record.getString("TOTAL_PALLET_PICK_QTY")  );
                            params.set(LIST_STATUS1,record.getInt("STATUS_FLAG") );
                            break;
                        case 1:
                            params.set(LIST_WAVENUM2,record.getString("WAVE_NO") );
                            params.set(LIST_PIECEPICKNUM2,record.getString("TOTAL_PIECES_PICK_QTY")  );
                            params.set(LIST_CASEPICKNUM2,record.getString("TOTAL_CASE_PICK_QTY")  );
                            params.set(LIST_PALLETPICKNUM2,record.getString("TOTAL_PALLET_PICK_QTY")  );
                            params.set(LIST_STATUS2,record.getInt("STATUS_FLAG") );
                            break;
                        case 2:
                            params.set(LIST_WAVENUM3,record.getString("WAVE_NO") );
                            params.set(LIST_PIECEPICKNUM3,record.getString("TOTAL_PIECES_PICK_QTY")  );
                            params.set(LIST_CASEPICKNUM3,record.getString("TOTAL_CASE_PICK_QTY")  );
                            params.set(LIST_PALLETPICKNUM3,record.getString("TOTAL_PALLET_PICK_QTY")  );
                            params.set(LIST_STATUS3,record.getInt("STATUS_FLAG") );
                            break;
                    }
                    i++;
                }
            }
        }catch(Exception e) {

        }
        return params;
    }

    //------------------------------------------------------------
    // accessor methods
    //------------------------------------------------------------

    //------------------------------------------------------------
    // package methods
    //------------------------------------------------------------

    //------------------------------------------------------------
    // protected methods
    //------------------------------------------------------------

    //------------------------------------------------------------
    // private methods
    //------------------------------------------------------------
    private void clearList(WaveListSCHParams params) {
        params.set(LIST_WAVENUM1,null);
        params.set(LIST_PIECEPICKNUM1,null );
        params.set(LIST_CASEPICKNUM1,null);
        params.set(LIST_PALLETPICKNUM1,null);
        params.set(LIST_STATUS1,null );
        params.set(LIST_WAVENUM2,null);
        params.set(LIST_PIECEPICKNUM2,null );
        params.set(LIST_CASEPICKNUM2,null);
        params.set(LIST_PALLETPICKNUM2,null);
        params.set(LIST_STATUS2,null );
        params.set(LIST_WAVENUM3,null);
        params.set(LIST_PIECEPICKNUM3,null );
        params.set(LIST_CASEPICKNUM3,null);
        params.set(LIST_PALLETPICKNUM3,null);
        params.set(LIST_STATUS3,null );
    }
    private SQLQuery createSqlOfWorkStatus()
    {
        SQLQuery sqlQuery = new SQLQuery();
        sqlQuery.append("SELECT PROGRESS.WAVE_SEQ,PROGRESS.WAVE_NO,PROGRESS.STATUS_FLAG,");
        sqlQuery.append("       SUM(PROGRESS.TOTAL_PIECES_PICK) AS TOTAL_PIECES_PICK_QTY,");
        sqlQuery.append("       SUM(PROGRESS.TOTAL_CASE_PICK) AS TOTAL_CASE_PICK_QTY,");
        sqlQuery.append("       SUM(PROGRESS.TOTAL_PALLET_PICK) AS TOTAL_PALLET_PICK_QTY");
        sqlQuery.append("FROM (SELECT DNWAVE.WAVE_SEQ,DNWAVE.WAVE_NO,DNWAVE.STATUS_FLAG,DNWAVE.PRIORITY,DNWAVE.SHORTAGE_ALLOC_FLAG,DNWAVE.WAVE_START_TIME,DNWORKINFO.WORK_TYPE,");
        sqlQuery.append("             CASE");
        sqlQuery.append("               WHEN (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.PIECE_PICKING));
        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 DMITEM.ITEM_CODE IS NOT NULL  ");
        sqlQuery.append("               THEN DNWORKINFO.WORK_QTY ELSE 0");
        sqlQuery.append("             END AS TOTAL_PIECES_PICK,");
        sqlQuery.append("             CASE");
        sqlQuery.append("               WHEN (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.RETRIEVAL_SHUTTLE_CART));
        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 DMITEM.ITEM_CODE IS NOT NULL ");
        sqlQuery.append("               THEN FLOOR(DNWORKINFO.WORK_QTY / NVL(DMITEM.PIECE_PER_CASE,1)) ELSE 0");
        sqlQuery.append("             END AS TOTAL_CASE_PICK,");
        sqlQuery.append("             CASE");
        sqlQuery.append("               WHEN (DNWORKINFO.WORK_TYPE = ").append(String.valueOf(dnworkinfoTypes.WORK_TYPE.RETRIEVAL));
        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 DMITEM.ITEM_CODE IS NOT NULL ");
        sqlQuery.append("               THEN FLOOR(DNWORKINFO.WORK_QTY/NVL(DMITEM.PIECE_PER_PALLET,1)) ELSE 0");
        sqlQuery.append("             END AS TOTAL_PALLET_PICK");
        sqlQuery.append("       FROM DNWAVE JOIN DNRETRIEVALPLAN ON DNWAVE.WAVE_SEQ=DNRETRIEVALPLAN.WAVE_SEQ ");
        sqlQuery.append("       LEFT JOIN DNWORKINFO ON DNRETRIEVALPLAN.PLAN_UKEY = DNWORKINFO.PLAN_UKEY ").append(" AND DNWORKINFO.LOAD_WORK_STATUS != ").append(dnworkinfoTypes.LOAD_WORK_STATUS.DELETE);
        sqlQuery.append("       LEFT JOIN DNSHIPWORKINFO ON DNSHIPWORKINFO.WORK_UKEY=DNWORKINFO.WORK_UKEY");
        sqlQuery.append("       LEFT JOIN DNSTOCK ON DNSTOCK.STOCK_ID=DNWORKINFO.STOCK_ID");
        sqlQuery.append("       LEFT JOIN DMITEM  ON DMITEM.ITEM_CODE=DNSTOCK.ITEM_CODE");
        sqlQuery.append("       WHERE   DNWAVE.WAVE_TYPE = ").append(dnwaveStatus.DNWAVE_TYPE.WAVE);
        sqlQuery.append("       AND DNRETRIEVALPLAN.STATUS_FLAG != ").append(dnretrievalPlanStatusFlag.STATUS_FLAG.DELTED);
        sqlQuery.append("       AND ( DNWAVE.STATUS_FLAG = ").append(dnwaveStatus.DNWAVE_STATUS_TYPE.ONQUEUE);
        sqlQuery.append("               OR DNWAVE.STATUS_FLAG = ").append(dnwaveStatus.DNWAVE_STATUS_TYPE.WORKING);
        sqlQuery.append("               OR DNWAVE.STATUS_FLAG = ").append(dnwaveStatus.DNWAVE_STATUS_TYPE.SHORTAGE).append(" ) ");
        sqlQuery.append("       )PROGRESS");
        sqlQuery.append("GROUP BY PROGRESS.WAVE_SEQ,PROGRESS.WAVE_NO,PROGRESS.STATUS_FLAG,PROGRESS.PRIORITY,PROGRESS.SHORTAGE_ALLOC_FLAG,PROGRESS.WAVE_START_TIME");
        sqlQuery.append("ORDER BY PROGRESS.STATUS_FLAG DESC,PROGRESS.PRIORITY DESC,PROGRESS.SHORTAGE_ALLOC_FLAG DESC,PROGRESS.WAVE_START_TIME ASC");
        return sqlQuery;
    }
    //------------------------------------------------------------
    // utility methods
    //------------------------------------------------------------
    /**
     * Returns current repository info for this class
     * @return version
     */
    public static String getVersion()
    {
        return "$Id: Sch.xml 2808 2014-10-27 05:13:07Z noriyuki_sawa_ha.daifuku.co.jp $";
    }
}
//end of class