WMSGenerateMovementReject.java

package org.aoi.enh.allocation.process;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;
import java.util.logging.Level;

import org.aoi.mrp.model.X_kst_ImportSetting;
import org.compiere.model.I_C_Order;
import org.compiere.model.MMovement;
import org.compiere.model.MMovementLine;
import org.compiere.model.MProduct;
import org.compiere.model.Query;
import org.compiere.process.DocAction;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;

public class WMSGenerateMovementReject extends SvrProcess {

    /** Client to be imported to        */
    private int             m_AD_Client_ID = 0;
    /** Organization to be imported to      */
    private int             m_AD_Org_ID = 0;
    /** RecevingDate        */
    private Timestamp       p_DateFrom;
    private Timestamp       p_DateTo;
    /** Document Type       */
    private int             p_C_DocType_ID = 0;
    /** Document Type   Return  */
    private int             p_C_DocType_ID_Return = 0;


    @Override
    protected void prepare() {
        ProcessInfoParameter[] para = getParameter();
        for (int i = 0; i < para.length; i++)
        {
            String name = para[i].getParameterName();
            if (name.equals("AD_Client_ID"))
                m_AD_Client_ID = para[i].getParameterAsInt();
            else if (name.equals("AD_Org_ID"))
                m_AD_Org_ID = para[i].getParameterAsInt();
            else if (name.equals("MovementDate")){
                p_DateFrom = para[i].getParameterAsTimestamp();
                p_DateTo = para[i].getParameter_ToAsTimestamp();
            }
            else if (name.equals("C_DocType_ID"))
                p_C_DocType_ID = para[i].getParameterAsInt();
            else if (name.equals("C_DocType_ID_Return"))
                p_C_DocType_ID_Return = para[i].getParameterAsInt();
            else
                log.log(Level.SEVERE, "Unknown Parameter: " + name);
        }
    }

    @Override
    protected String doIt() throws Exception {
        String msg ="";
        String sqlSetting = " Name = 'WMS'";
        X_kst_ImportSetting impSett = new Query(Env.getCtx(),X_kst_ImportSetting.Table_Name,sqlSetting,null)
                                        .setOrderBy("kst_importsetting_id desc")
                                        .setOnlyActiveRecords(true)
                                        .first();

        String ip = impSett.getkst_IP();
        String dbname = impSett.getkst_DatabaseName();
        String port = impSett.getkst_Port();
        String user = impSett.getkst_UserName();
        String password = impSett.getkst_Password();

        String sqlReject = "Select * from wms_movereject_v";
        if ( p_DateFrom != null || p_DateTo != null ){
            sqlReject += " Where movement_date between ? and ? ";
        }

        ArrayList<Integer> listMove = new ArrayList<Integer>();

        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            pstmt = DB.prepareStatement(sqlReject.toString(), get_TrxName());
            if ( p_DateFrom != null || p_DateTo != null ){
                pstmt.setTimestamp(1, p_DateFrom);
                pstmt.setTimestamp(2, p_DateTo);
            }

            rs = pstmt.executeQuery();
            MMovement move = null;
            int idxline = 0;
            while (rs.next()){
                int locatorFrom = rs.getInt("from_location_name");
                int locatorTo = rs.getInt("destination_name");

                move = new Query(getCtx(), MMovement.Table_Name, " M_Movement_UU like ?  and docstatus not in ('VO','RE','CO' )", get_TrxName())
                                .setOnlyActiveRecords(true)
                                .setClient_ID()
                                .setParameters("%"+rs.getString("material_movement_id"))
                                .first();

                // Create New Movement
                if ( move == null){
                    idxline = 0;
                    move  = new MMovement(getCtx(), 0, get_TrxName());
                    move.set_ValueOfColumn("AD_Client_ID", m_AD_Client_ID);
                    move.setAD_Org_ID(m_AD_Org_ID);
                    // move.setDocumentNo(rs.getString("material_movement_id"));
                    move.setC_DocType_ID(p_C_DocType_ID);
                    move.setMovementDate(rs.getTimestamp("movement_date"));
                    move.set_ValueOfColumn("POReference", rs.getString("po_buyer"));
                    move.setDescription("Generated from WMS Movement - " + rs.getString("material_movement_id"));
                    move.setProcessing(false);
                    move.setProcessed(false);
                    move.setIsInTransit(false);
                    move.setIsApproved(false);
                    move.setM_Movement_UU(rs.getString("material_movement_id"));
                    /* Added by Roynaldo - 20180621 */
                    //move.set_ValueOfColumn("kst_so_id", rs.getInt("c_order_id"));
                    int kst_so_id = new Query(getCtx(), I_C_Order.Table_Name, "IsSOTrx='Y' and DocStatus in ('CO', 'CL') and POReference=?", get_TrxName())
                        .setParameters(rs.getString("po_buyer"))
                        .firstId();
                    if ( kst_so_id <= 0) {
                        msg = "PO Buyer Not Found in SO";
                        return "PO Buyer Not Found in SO "+rs.getString("po_buyer");
                    }
                    else
                        move.set_ValueOfColumn("kst_so_id", kst_so_id);
                    /* End Added by Roynaldo - 20180621 */
                    if (move.save() ){
                        listMove.add(move.get_ID());
                        MMovementLine line = new MMovementLine(move);

                        MProduct product = new Query(getCtx(), MProduct.Table_Name, " value = ? ", get_TrxName())
                                                .setClient_ID()
                                                .setOnlyActiveRecords(true)
                                                .setParameters(rs.getString("item_code"))
                                                .first();
                        if (product == null )
                            return "Product Value Not Found ";

                        line.setM_Locator_ID(locatorFrom);
                        line.setM_LocatorTo_ID(locatorTo);
                        idxline = idxline + 10;
                        line.setLine(idxline);
                        line.setM_Product_ID(product.get_ID());
                        int C_UOM_ID = new Query(getCtx(), "C_UOM", "AD_Client_ID=? AND AD_Org_ID=? AND X12DE355=?", get_TrxName())
                            .setParameters(m_AD_Client_ID, m_AD_Org_ID, rs.getString("UOM"))
                            .firstId();
                        if (C_UOM_ID<0)
                            C_UOM_ID = new Query(getCtx(), "C_UOM", "AD_Client_ID=? AND AD_Org_ID=? AND X12DE355=?", get_TrxName())
                            .setParameters(m_AD_Client_ID, 0, rs.getString("UOM"))
                            .firstId();
                        if (C_UOM_ID<0)
                            return "UOM Not Found - " + rs.getString("material_movement_line_id");
                        line.setMovementQty(rs.getBigDecimal("qty"));
                        line.set_ValueOfColumn("QtyOrdered", rs.getBigDecimal("qty"));
                        line.set_ValueOfColumn("C_UOM_ID", C_UOM_ID);
                        line.saveEx(get_TrxName());

                    }
                }
                else {
                    // Input Line Already Exists Header
                    // listMove.add(move.get_ID());
                    MMovementLine line = new MMovementLine(move);
//
                    MProduct product = new Query(getCtx(), MProduct.Table_Name, " value = ? ", get_TrxName())
                                            .setClient_ID()
                                            .setOnlyActiveRecords(true)
                                            .setParameters(rs.getString("item_code"))
                                            .first();
                    if (product == null )
                        return "Product Value Not Found ";

                    line.setM_Locator_ID(locatorFrom);
                    line.setM_LocatorTo_ID(locatorTo);
                    idxline = idxline + 10;
                    line.setLine(idxline);
                    line.setM_Product_ID(product.get_ID());
                    int C_UOM_ID = new Query(getCtx(), "C_UOM", "AD_Client_ID=? AND AD_Org_ID=? AND X12DE355=?", get_TrxName())
                        .setParameters(m_AD_Client_ID, m_AD_Org_ID, rs.getString("UOM"))
                        .firstId();
                    if (C_UOM_ID<0)
                        C_UOM_ID = new Query(getCtx(), "C_UOM", "AD_Client_ID=? AND AD_Org_ID=? AND X12DE355=?", get_TrxName())
                        .setParameters(m_AD_Client_ID, 0, rs.getString("UOM"))
                        .firstId();
                    if (C_UOM_ID<0)
                        return "UOM Not Found - " + rs.getString("material_movement_line_id");
                    line.setMovementQty(rs.getBigDecimal("qty"));
                    line.set_ValueOfColumn("QtyOrdered", rs.getBigDecimal("qty"));
                    line.set_ValueOfColumn("C_UOM_ID", C_UOM_ID);
                    line.saveEx(get_TrxName());
                }
            }

            Set<Integer> hasMap = new HashSet<>();
            hasMap.addAll(listMove);
            hasMap.clear();
            listMove.addAll(hasMap);

            for (Integer listMovement : listMove) {
                MMovement movement = new MMovement(getCtx(), listMovement, get_TrxName());
                // Validate Is Integration Flag WMS
                String wms_id = movement.getDescription().substring(movement.getDescription().length() -36, movement.getDescription().length() );
                //Update DB Link Is Integration
                int vcount  = DB.executeUpdate(" select * "
                        + " FROM dblink('"
                        + "dbname=" + dbname +" port=" + port +" host=" + ip +" user=" + user +"  password=" + password +"' "
                        + ",'Update material_movements set is_integrate = true, integration_date = now()  "
                        + "WHERE id =''" + wms_id  +"''' ) tt(updated text);", true, get_TrxName());
                System.out.println("WMS #"+wms_id+" Updated IsIntegration...");

            }

            for (Integer listMovement : listMove) {
                try {
                    MMovement movement = new MMovement(getCtx(), listMovement, get_TrxName());
                    if ( !movement.getDocStatus().equals("CO")){
                        movement.processIt(DocAction.ACTION_Complete);
                        movement.saveEx(get_TrxName());

                        msg = msg + "\n" + movement.getDocumentNo();
                    }
                } catch (Exception e) {
                    System.out.println("Error Complete Movement.."+e.getMessage());
                }
            }

        }catch (Exception e){
            msg = e.toString();
            return msg;
        }finally{
            DB.close(rs, pstmt);
            pstmt = null;
            rs = null;
        }

        msg = "Process Completed for Document No : \n " + msg;
        return msg;
    }

}