RDOCreateFromPrepayRequest .java

package org.aoi.rdo.form;

import static org.compiere.model.SystemIDs.COLUMN_C_INVOICE_C_BPARTNER_ID;
import static org.compiere.model.SystemIDs.COLUMN_C_INVOICE_C_CURRENCY_ID;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Vector;
import java.util.logging.Level;

import org.adempiere.webui.apps.AEnv;
import org.adempiere.webui.apps.form.WCreateFromWindow;
import org.adempiere.webui.component.Grid;
import org.adempiere.webui.component.GridFactory;
import org.adempiere.webui.component.Label;
import org.adempiere.webui.component.ListModelTable;
import org.adempiere.webui.component.Panel;
import org.adempiere.webui.component.Row;
import org.adempiere.webui.component.Rows;
import org.adempiere.webui.editor.WEditor;
import org.adempiere.webui.editor.WSearchEditor;
import org.adempiere.webui.editor.WStringEditor;
import org.adempiere.webui.event.ValueChangeEvent;
import org.adempiere.webui.event.ValueChangeListener;
import org.aoi.rdo.model.MRDOPrepayRequest;
import org.aoi.rdo.model.MRDOPrepayRequestLine;
import org.compiere.apps.IStatusBar;
import org.compiere.grid.CreateFrom;
import org.compiere.minigrid.IMiniTable;
import org.compiere.model.GridTab;
import org.compiere.model.MLookup;
import org.compiere.model.MLookupFactory;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.DisplayType;
import org.compiere.util.Env;
import org.compiere.util.KeyNamePair;
import org.compiere.util.Msg;
import org.jfree.util.Log;
import org.zkoss.zk.ui.event.Event;
import org.zkoss.zk.ui.event.EventListener;
import org.zkoss.zk.ui.event.Events;
import org.zkoss.zul.Borderlayout;
import org.zkoss.zul.Center;
import org.zkoss.zul.Space;

public class RDOCreateFromPrepayRequest extends CreateFrom implements EventListener<Event>, ValueChangeListener{

    private WCreateFromWindow window;

    public int m_C_Payment_ID = 0;
    public int m_C_BPartner_ID = 0;
    public int m_C_Currency_ID = 0;
    private boolean IsSOTrx;

    private int COLUMN_PREPAYREQUESTLINE_C_ORDER_ID = columnId_OrderId();

    protected int columnId_OrderId (){
        // get id column order_id on prepaypayment request line
        int id = 0;
        StringBuffer sql = new StringBuffer();
        sql.append(" select ad_column_id from ad_column ")
            .append(" where lower(columnname) like '%c_order_id%' ")
            .append(" and ad_table_id = (select ad_table_id from ad_table where lower(tablename) like '%rdo_prepayrequestline%')");
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            pstmt = DB.prepareStatement(sql.toString(), null);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                id = rs.getInt("ad_column_id");
            }
        } catch (Exception e) {
            Log.error("Erorr", e);
        }
        return id;
    }

    public RDOCreateFromPrepayRequest(GridTab mTab) {
        super(mTab);
        if (log.isLoggable(Level.INFO)) log.info(mTab.toString());

        window = new WCreateFromWindow(this, getGridTab().getWindowNo());

        p_WindowNo = getGridTab().getWindowNo();

        try
        {
            if (!dynInit())
                return;
            zkInit();
            setInitOK(true);
        }
        catch(Exception e)
        {
            log.log(Level.SEVERE, "", e);
            setInitOK(false);
        }
        AEnv.showWindow(window);
    }

    public void showWindow()
    {
        window.setVisible(true);
    }

    public void closeWindow()
    {
        window.dispose();
    }

    @Override
    public Object getWindow() {
        return window;
    }

    /** Window No               */
    private int p_WindowNo;

    /** Logger          */
    private CLogger log = CLogger.getCLogger(getClass());

    protected Label bPartnerLabel = new Label();
    protected WEditor bPartnerField;

    protected Label currencyLabel = new Label();
    protected WEditor currencyField;

    protected Label orderLabel = new Label();
    protected WEditor orderField;

    /* Added by Roynaldo - 20191010 - Invoice Vendor Parameter */
    protected Label invoiceVendorLabel = new Label();
    protected WStringEditor invoiceVendorField;
    /* End Added by Roynaldo - 20191010 - Invoice Vendor Parameter */

    //add pi filter
    protected Label piNumberLabel = new Label();
    protected WStringEditor piNumberField;

    @Override
    public boolean dynInit() throws Exception {
        log.config("");
        window.setTitle(" Prepayment - Create Lines From Prepayment Request");

        m_C_Payment_ID = Env.getContextAsInt(Env.getCtx(), getGridTab().getWindowNo(), "C_Payment_ID");
        m_C_BPartner_ID = Env.getContextAsInt(Env.getCtx(), getGridTab().getWindowNo(), "C_BPartner_ID");
        m_C_Currency_ID = Env.getContextAsInt(Env.getCtx(), getGridTab().getWindowNo(), "C_Currency_ID");
        IsSOTrx = Env.getContext(Env.getCtx(), getGridTab().getWindowNo(), "IsSOTrx").equals("Y");

        initBPartner(IsSOTrx);
        initCurrency();
        initOrder();
        loadOrderData(0, null, null);

        orderField.addValueChangeListener(this);

        invoiceVendorField =    new WStringEditor("kst_InvoiceVendor", false, false, true, 10, 30, null, null);
        invoiceVendorField.getComponent().addEventListener(Events.ON_CHANGE, this);

        piNumberField = new WStringEditor();
        piNumberField.getComponent().addEventListener(Events.ON_CHANGE, this);
        return true;
    }

    protected void zkInit() throws Exception {
        bPartnerLabel.setText(Msg.getElement(Env.getCtx(), "C_BPartner_ID"));
        currencyLabel.setText(Msg.getElement(Env.getCtx(), "C_Currency_ID"));
        orderLabel.setText(Msg.getElement(Env.getCtx(), "C_Order_ID", IsSOTrx));
        /* Added by Roynaldo - 20191010 - Invoice Vendor Parameter */
        invoiceVendorLabel.setText(Msg.getElement(Env.getCtx(), "kst_InvoiceVendor"));
        /* End Added by Roynaldo - 20191010 - Invoice Vendor Parameter */
        piNumberLabel.setText("NO PI");

        Borderlayout parameterLayout = new Borderlayout();
        parameterLayout.setHeight("135px");
        parameterLayout.setWidth("100%");
        Panel parameterPanel = window.getParameterPanel();
        parameterPanel.appendChild(parameterLayout);

        Grid parameterStdLayout = GridFactory.newGridLayout();
        Panel parameterStdPanel = new Panel();
        parameterStdPanel.appendChild(parameterStdLayout);

        Center center = new Center();
        parameterLayout.appendChild(center);
        center.appendChild(parameterStdPanel);

        Rows rows = (Rows) parameterStdLayout.newRows();
        Row row = rows.newRow();
        row.appendChild(bPartnerLabel.rightAlign());
        row.appendChild(bPartnerField.getComponent());


        row.appendChild(piNumberLabel.rightAlign());
        row.appendChild(piNumberField.getComponent());

        row = rows.newRow();
        row.appendChild(currencyLabel.rightAlign());
        row.appendChild(currencyField.getComponent());
        row = rows.newRow();
        row.appendChild(orderLabel.rightAlign());
        row.appendChild(orderField.getComponent());
        /* Added by Roynaldo - 20191010 - Invoice Vendor Parameter */
        row = rows.newRow();
        row.appendChild(invoiceVendorLabel.rightAlign());
        row.appendChild(invoiceVendorField.getComponent());
        /* End Added by Roynaldo - 20191010 - Invoice Vendor Parameter */
    }

    protected void configureMiniTable (IMiniTable miniTable)
    {
        miniTable.setColumnClass(0, Boolean.class, false);          //  0-Selection
        miniTable.setColumnClass(1, String.class, true);            //  1-DocumentNo
        miniTable.setColumnClass(2, Timestamp.class, true);         //  2-DateOrdered
        miniTable.setColumnClass(3, BigDecimal.class, false);       //  3-GrandTotal
        miniTable.setColumnClass(4, String.class, true);            //  4-Description
        miniTable.setColumnClass(5, String.class, true);            //  5-No_pi
        //  Table UI
        miniTable.autoSize();
    }

    protected Vector<String> getOISColumnNames()
    {
        //  Header Info
        Vector<String> columnNames = new Vector<String>(5);
        columnNames.add(Msg.getMsg(Env.getCtx(), "Select"));
        columnNames.add(Msg.getElement(Env.getCtx(), "C_Order_ID", IsSOTrx));
        columnNames.add(Msg.getElement(Env.getCtx(), "DateOrdered"));
        columnNames.add(Msg.getElement(Env.getCtx(), "GrandTotal"));
        columnNames.add(Msg.getElement(Env.getCtx(), "Description"));
        columnNames.add(Msg.getElement(Env.getCtx(), "ar_pi"));

        return columnNames;
    }

    protected Vector<Vector<Object>> getOrderData(int C_Order_ID, String kst_InvoiceVendor, String ar_pi)
    {
        Vector<Vector<Object>> data = new Vector<Vector<Object>>();

        StringBuffer sql = new StringBuffer();
        //sql.append("select co.C_Order_ID, co.DocumentNo, co.DateOrdered, co.GrandTotal, co.Description ")
        sql.append("select co.C_Order_ID, co.DocumentNo, co.DateOrdered, co.ar_pi as noPI, ")
            .append("co.GrandTotal - ( ")
            .append("select coalesce(sum(rpl.PayAmt),0) ")
            .append("from RDO_PrepayRequestLine rpl join RDO_PrepayRequest rpp on (rpl.RDO_PrepayRequest_ID=rpp.RDO_PrepayRequest_ID) ")
            .append("where rpp.DocStatus in ('DR', 'IP', 'CO', 'CL') ")
            .append("and rpl.C_Order_ID=co.C_Order_ID) as GrandTotal, ")
            .append("co.Description ")
            .append("from C_Order co ")
            .append("where co.IsSOTrx=? ")
            .append("and co.DocStatus in ('CO', 'CL') ")
            .append("and co.C_BPartner_ID=? ")
            .append("and co.C_Currency_ID=? ")
            /* Added by Roynaldo - 20191010 - Created>08-Oct & Payment Term=Before Shipment or letter of credit */
            .append("and trunc(co.Created)>='2019-10-08' ");
        if(ar_pi!= null && !ar_pi.equals("")){
            sql.append(" and co.ar_pi like '"+ar_pi+"' ");
        }
            sql.append("and co.C_PaymentTerm_ID IN (select c_paymentterm_id from C_PaymentTerm "
                    + "where lower(name) like '%letter of credit%' or lower (name) like '%before shipment%') ")
            /* End Added by Roynaldo - 20191010 - Created>08-Oct & Payment Term=Before Shipment */
            .append("and exists ( ")
            .append("select 1 ")
            .append("from C_OrderLine col ")
            .append("where col.C_Order_ID=co.C_Order_ID ")
            /* Added by Roynaldo - 20191010 - Order Line with Price Only */
            .append("and col.PriceActual>0 ")
            /* End Added by Roynaldo - 20191010 - Order Line with Price Only */
            .append("and not exists ( ")
            //.append("select 1 ")
            .append("select rpl.C_Order_ID, coo.GrandTotal, sum(rpl.PayAmt) ")
            .append("from RDO_PrepayRequestLine rpl join RDO_PrepayRequest rpp on (rpl.RDO_PrepayRequest_ID=rpp.RDO_PrepayRequest_ID) ")
            //
            .append("join C_Order coo on (rpl.C_Order_ID=coo.C_Order_ID) ")
            .append("where rpp.C_BPartner_ID=co.C_BPartner_ID ")
            .append("and rpp.DocStatus in ('DR', 'IP', 'CO', 'CL') ")
            //.append("and rpl.C_Order_ID=co.C_Order_ID) ");
            .append("and rpl.C_Order_ID=co.C_Order_ID ")
            .append("and rpl.C_Order_ID=coo.C_Order_ID ")
            .append("group by rpl.C_Order_ID, coo.GrandTotal ")
            .append("having sum(rpl.PayAmt)>=coo.GrandTotal) ");

        if (C_Order_ID>0)
            sql.append("and co.C_Order_ID=?");

        if (kst_InvoiceVendor!=null && !kst_InvoiceVendor.equals(""))
            sql.append("and co.C_Order_ID IN (")
                .append("select distinct coo.C_Order_ID ")
                .append("from kst_Receipt kr join kst_ReceiptLine krl on (kr.kst_Receipt_ID=krl.kst_Receipt_ID) ")
                .append("join kst_InvoiceVendor kiv on (kr.kst_InvoiceVendor_ID=kiv.kst_InvoiceVendor_ID) ")
                .append("join C_OrderLine colo on (krl.C_OrderLine_ID=colo.C_OrderLine_ID) ")
                .append("join C_Order coo on (colo.C_Order_ID=coo.C_Order_ID) ")
                .append("where kiv.kst_InvoiceVendor=? ")
                .append("and trunc(coo.Created)>='2019-10-08'")
                .append("and coo.C_PaymentTerm_ID IN (select c_paymentterm_id from C_PaymentTerm "
                    + "where lower(name) like '%letter of credit%' or lower (name) like '%before shipment%') ")
                .append(")");


        //hanya ambil order yang nilai closed prepayment nya tidak sama dengan Y
        sql.append(" AND co.tpb_closed_prepayment <> 'Y' )");

        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            pstmt = DB.prepareStatement(sql.toString(), null);
            pstmt.setString(1, IsSOTrx? "Y" : "N");
            pstmt.setInt(2, m_C_BPartner_ID);
            pstmt.setInt(3, m_C_Currency_ID);
            int idxpar = 3;
            if (C_Order_ID>0){
                idxpar++;
                pstmt.setInt(idxpar, C_Order_ID);
            }
            if(kst_InvoiceVendor!=null && !kst_InvoiceVendor.equals("")){
                idxpar++;
                pstmt.setString(idxpar, kst_InvoiceVendor);
            }
            rs = pstmt.executeQuery();
            while (rs.next()){
                Vector<Object> line = new Vector<Object>(5);
                line.add(new Boolean(false));           //  0-Selection
                KeyNamePair pp = new KeyNamePair(rs.getInt("C_Order_ID"), rs.getString("DocumentNo"));
                line.add(pp);
                line.add(rs.getTimestamp("DateOrdered"));
                line.add(rs.getBigDecimal("GrandTotal"));
                line.add(rs.getString("Description"));
                line.add(rs.getString("noPI"));
                data.add(line);
            }
        }catch (SQLException e){
            log.warning(e.toString());
        }finally{
            DB.close(rs, pstmt);
            pstmt = null;
            rs = null;
        }

        return data;
    }

    @Override
    public void info(IMiniTable miniTable, IStatusBar statusBar) {
        // TODO Auto-generated method stub

    }

    @Override
    public boolean save(IMiniTable miniTable, String trxName) {
        int prepayRequestID = (Integer) getGridTab().getValue("RDO_PrepayRequest_ID");
        MRDOPrepayRequest prepayRequest = new MRDOPrepayRequest(Env.getCtx(), prepayRequestID, trxName);
        if (log.isLoggable(Level.CONFIG)) log.config(prepayRequest.toString());

        //  Lines
        for (int i = 0; i < miniTable.getRowCount(); i++)
        {
            if (((Boolean)miniTable.getValueAt(i, 0)).booleanValue())
            {
                KeyNamePair pp = (KeyNamePair) miniTable.getValueAt(i, 1);
                int C_Order_ID = pp.getKey();

                BigDecimal payAmt = (BigDecimal) miniTable.getValueAt(i, 3);

                MRDOPrepayRequestLine requestLine = new MRDOPrepayRequestLine(Env.getCtx(), 0, trxName);
                requestLine.setAD_Org_ID(prepayRequest.getAD_Org_ID());
                requestLine.setRDO_PrepayRequest_ID(prepayRequestID);
                requestLine.setC_Order_ID(C_Order_ID);
                requestLine.setPayAmt(payAmt);
                requestLine.saveEx(trxName);
            }
        }
        return true;
    }

    protected void initBPartner (boolean forInvoice) throws Exception
    {
        //  load BPartner
        int AD_Column_ID = COLUMN_C_INVOICE_C_BPARTNER_ID;        //  C_Invoice.C_BPartner_ID
        MLookup lookup = MLookupFactory.get (Env.getCtx(), p_WindowNo, 0, AD_Column_ID, DisplayType.Search);
        bPartnerField = new WSearchEditor ("C_BPartner_ID", true, true, false, lookup);

        int C_BPartner_ID = Env.getContextAsInt(Env.getCtx(), p_WindowNo, "C_BPartner_ID");
        bPartnerField.setValue(new Integer(C_BPartner_ID));
    }

    protected void initCurrency() throws Exception
    {
        int AD_Column_ID = COLUMN_C_INVOICE_C_CURRENCY_ID;      //  C_Invoice.C_Currency_ID
        MLookup lookup = MLookupFactory.get (Env.getCtx(), p_WindowNo, 0, AD_Column_ID, DisplayType.Search);
        currencyField = new WSearchEditor ("C_Currency_ID", true, true, false, lookup);

        int C_Currency_ID = Env.getContextAsInt(Env.getCtx(), p_WindowNo, "C_Currency_ID");
        currencyField.setValue(new Integer(C_Currency_ID));
    }

    protected void initOrder() throws Exception
    {
        int AD_Column_ID = COLUMN_PREPAYREQUESTLINE_C_ORDER_ID;
        MLookup lookup = MLookupFactory.get (Env.getCtx(), p_WindowNo, 0, AD_Column_ID, DisplayType.Search);
        orderField = new WSearchEditor ("C_Order_ID", false, false, true, lookup);
    }

    protected void loadOrderData(int C_Order_ID, String kst_InvoiceVendor, String piNo)
    {
        loadTableOIS(getOrderData(C_Order_ID, kst_InvoiceVendor,piNo));
    }

    /**
     *  Load Order data into Table
     *  @param data data
     */
    protected void loadTableOIS (Vector<?> data)
    {
        window.getWListbox().clear();

        //  Remove previous listeners
        window.getWListbox().getModel().removeTableModelListener(window);
        //  Set Model
        ListModelTable model = new ListModelTable(data);
        model.addTableModelListener(window);
        window.getWListbox().setData(model, getOISColumnNames());
        //

        configureMiniTable(window.getWListbox());
    }   //  loadOrder

    private boolean     m_actionActive = false;

    @Override
    public void onEvent(Event e) throws Exception {
        if (m_actionActive)
            return;
        m_actionActive = true;

        if (e.getTarget().equals(invoiceVendorField.getComponent()) || e.getTarget().equals(piNumberField.getComponent())){
            int C_Order_ID = 0;
            if (orderField.getValue()!=null)
                C_Order_ID = (Integer) orderField.getValue();
            String kst_InvoiceVendor = invoiceVendorField.getDisplay();
            String piNo = (String) piNumberField.getValue();
            loadOrderData(C_Order_ID, kst_InvoiceVendor,piNo);
        }

        m_actionActive = false;
    }

    @Override
    public void valueChange(ValueChangeEvent e) {
        if (log.isLoggable(Level.CONFIG)) log.config(e.getPropertyName() + "=" + e.getNewValue());

        if(e.getNewValue()==null)
            return ;

        if (e.getPropertyName().equals("C_Order_ID"))
        {
            int C_Order_ID = (Integer) e.getNewValue();
            String kst_InvoiceVendor = (String) invoiceVendorField.getValue();
            String piNo = (String) piNumberField.getValue();
            loadOrderData(C_Order_ID, kst_InvoiceVendor,piNo);
        }

    }

}