· 6 years ago · Dec 06, 2019, 01:32 AM
1package org.aoi.rdo.form;
2
3import static org.compiere.model.SystemIDs.COLUMN_C_INVOICE_C_BPARTNER_ID;
4import static org.compiere.model.SystemIDs.COLUMN_C_INVOICE_C_CURRENCY_ID;
5
6import java.math.BigDecimal;
7import java.sql.PreparedStatement;
8import java.sql.ResultSet;
9import java.sql.SQLException;
10import java.sql.Timestamp;
11import java.util.Vector;
12import java.util.logging.Level;
13
14import org.adempiere.webui.apps.AEnv;
15import org.adempiere.webui.apps.form.WCreateFromWindow;
16import org.adempiere.webui.component.Grid;
17import org.adempiere.webui.component.GridFactory;
18import org.adempiere.webui.component.Label;
19import org.adempiere.webui.component.ListModelTable;
20import org.adempiere.webui.component.Panel;
21import org.adempiere.webui.component.Row;
22import org.adempiere.webui.component.Rows;
23import org.adempiere.webui.editor.WEditor;
24import org.adempiere.webui.editor.WSearchEditor;
25import org.adempiere.webui.editor.WStringEditor;
26import org.adempiere.webui.event.ValueChangeEvent;
27import org.adempiere.webui.event.ValueChangeListener;
28import org.aoi.rdo.model.MRDOPrepayRequest;
29import org.aoi.rdo.model.MRDOPrepayRequestLine;
30import org.compiere.apps.IStatusBar;
31import org.compiere.grid.CreateFrom;
32import org.compiere.minigrid.IMiniTable;
33import org.compiere.model.GridTab;
34import org.compiere.model.MLookup;
35import org.compiere.model.MLookupFactory;
36import org.compiere.util.CLogger;
37import org.compiere.util.DB;
38import org.compiere.util.DisplayType;
39import org.compiere.util.Env;
40import org.compiere.util.KeyNamePair;
41import org.compiere.util.Msg;
42import org.jfree.util.Log;
43import org.zkoss.zk.ui.event.Event;
44import org.zkoss.zk.ui.event.EventListener;
45import org.zkoss.zk.ui.event.Events;
46import org.zkoss.zul.Borderlayout;
47import org.zkoss.zul.Center;
48import org.zkoss.zul.Space;
49
50public class RDOCreateFromPrepayRequest extends CreateFrom implements EventListener<Event>, ValueChangeListener{
51
52 private WCreateFromWindow window;
53
54 public int m_C_Payment_ID = 0;
55 public int m_C_BPartner_ID = 0;
56 public int m_C_Currency_ID = 0;
57 private boolean IsSOTrx;
58
59 private int COLUMN_PREPAYREQUESTLINE_C_ORDER_ID = columnId_OrderId();
60
61 protected int columnId_OrderId (){
62 // get id column order_id on prepaypayment request line
63 int id = 0;
64 StringBuffer sql = new StringBuffer();
65 sql.append(" select ad_column_id from ad_column ")
66 .append(" where lower(columnname) like '%c_order_id%' ")
67 .append(" and ad_table_id = (select ad_table_id from ad_table where lower(tablename) like '%rdo_prepayrequestline%')");
68 PreparedStatement pstmt = null;
69 ResultSet rs = null;
70
71 try {
72 pstmt = DB.prepareStatement(sql.toString(), null);
73 rs = pstmt.executeQuery();
74 while (rs.next()) {
75 id = rs.getInt("ad_column_id");
76 }
77 } catch (Exception e) {
78 Log.error("Erorr", e);
79 }
80 return id;
81 }
82
83 public RDOCreateFromPrepayRequest(GridTab mTab) {
84 super(mTab);
85 if (log.isLoggable(Level.INFO)) log.info(mTab.toString());
86
87 window = new WCreateFromWindow(this, getGridTab().getWindowNo());
88
89 p_WindowNo = getGridTab().getWindowNo();
90
91 try
92 {
93 if (!dynInit())
94 return;
95 zkInit();
96 setInitOK(true);
97 }
98 catch(Exception e)
99 {
100 log.log(Level.SEVERE, "", e);
101 setInitOK(false);
102 }
103 AEnv.showWindow(window);
104 }
105
106 public void showWindow()
107 {
108 window.setVisible(true);
109 }
110
111 public void closeWindow()
112 {
113 window.dispose();
114 }
115
116 @Override
117 public Object getWindow() {
118 return window;
119 }
120
121 /** Window No */
122 private int p_WindowNo;
123
124 /** Logger */
125 private CLogger log = CLogger.getCLogger(getClass());
126
127 protected Label bPartnerLabel = new Label();
128 protected WEditor bPartnerField;
129
130 protected Label currencyLabel = new Label();
131 protected WEditor currencyField;
132
133 protected Label orderLabel = new Label();
134 protected WEditor orderField;
135
136 /* Added by Roynaldo - 20191010 - Invoice Vendor Parameter */
137 protected Label invoiceVendorLabel = new Label();
138 protected WStringEditor invoiceVendorField;
139 /* End Added by Roynaldo - 20191010 - Invoice Vendor Parameter */
140
141 //add pi filter
142 protected Label piNumberLabel = new Label();
143 protected WStringEditor piNumberField;
144
145 @Override
146 public boolean dynInit() throws Exception {
147 log.config("");
148 window.setTitle(" Prepayment - Create Lines From Prepayment Request");
149
150 m_C_Payment_ID = Env.getContextAsInt(Env.getCtx(), getGridTab().getWindowNo(), "C_Payment_ID");
151 m_C_BPartner_ID = Env.getContextAsInt(Env.getCtx(), getGridTab().getWindowNo(), "C_BPartner_ID");
152 m_C_Currency_ID = Env.getContextAsInt(Env.getCtx(), getGridTab().getWindowNo(), "C_Currency_ID");
153 IsSOTrx = Env.getContext(Env.getCtx(), getGridTab().getWindowNo(), "IsSOTrx").equals("Y");
154
155 initBPartner(IsSOTrx);
156 initCurrency();
157 initOrder();
158 loadOrderData(0, null, null);
159
160 orderField.addValueChangeListener(this);
161
162 invoiceVendorField = new WStringEditor("kst_InvoiceVendor", false, false, true, 10, 30, null, null);
163 invoiceVendorField.getComponent().addEventListener(Events.ON_CHANGE, this);
164
165 piNumberField = new WStringEditor();
166 piNumberField.getComponent().addEventListener(Events.ON_CHANGE, this);
167 return true;
168 }
169
170 protected void zkInit() throws Exception {
171 bPartnerLabel.setText(Msg.getElement(Env.getCtx(), "C_BPartner_ID"));
172 currencyLabel.setText(Msg.getElement(Env.getCtx(), "C_Currency_ID"));
173 orderLabel.setText(Msg.getElement(Env.getCtx(), "C_Order_ID", IsSOTrx));
174 /* Added by Roynaldo - 20191010 - Invoice Vendor Parameter */
175 invoiceVendorLabel.setText(Msg.getElement(Env.getCtx(), "kst_InvoiceVendor"));
176 /* End Added by Roynaldo - 20191010 - Invoice Vendor Parameter */
177 piNumberLabel.setText("NO PI");
178
179 Borderlayout parameterLayout = new Borderlayout();
180 parameterLayout.setHeight("135px");
181 parameterLayout.setWidth("100%");
182 Panel parameterPanel = window.getParameterPanel();
183 parameterPanel.appendChild(parameterLayout);
184
185 Grid parameterStdLayout = GridFactory.newGridLayout();
186 Panel parameterStdPanel = new Panel();
187 parameterStdPanel.appendChild(parameterStdLayout);
188
189 Center center = new Center();
190 parameterLayout.appendChild(center);
191 center.appendChild(parameterStdPanel);
192
193 Rows rows = (Rows) parameterStdLayout.newRows();
194 Row row = rows.newRow();
195 row.appendChild(bPartnerLabel.rightAlign());
196 row.appendChild(bPartnerField.getComponent());
197
198
199 row.appendChild(piNumberLabel.rightAlign());
200 row.appendChild(piNumberField.getComponent());
201
202 row = rows.newRow();
203 row.appendChild(currencyLabel.rightAlign());
204 row.appendChild(currencyField.getComponent());
205 row = rows.newRow();
206 row.appendChild(orderLabel.rightAlign());
207 row.appendChild(orderField.getComponent());
208 /* Added by Roynaldo - 20191010 - Invoice Vendor Parameter */
209 row = rows.newRow();
210 row.appendChild(invoiceVendorLabel.rightAlign());
211 row.appendChild(invoiceVendorField.getComponent());
212 /* End Added by Roynaldo - 20191010 - Invoice Vendor Parameter */
213 }
214
215 protected void configureMiniTable (IMiniTable miniTable)
216 {
217 miniTable.setColumnClass(0, Boolean.class, false); // 0-Selection
218 miniTable.setColumnClass(1, String.class, true); // 1-DocumentNo
219 miniTable.setColumnClass(2, Timestamp.class, true); // 2-DateOrdered
220 miniTable.setColumnClass(3, BigDecimal.class, false); // 3-GrandTotal
221 miniTable.setColumnClass(4, String.class, true); // 4-Description
222 miniTable.setColumnClass(5, String.class, true); // 5-No_pi
223 // Table UI
224 miniTable.autoSize();
225 }
226
227 protected Vector<String> getOISColumnNames()
228 {
229 // Header Info
230 Vector<String> columnNames = new Vector<String>(5);
231 columnNames.add(Msg.getMsg(Env.getCtx(), "Select"));
232 columnNames.add(Msg.getElement(Env.getCtx(), "C_Order_ID", IsSOTrx));
233 columnNames.add(Msg.getElement(Env.getCtx(), "DateOrdered"));
234 columnNames.add(Msg.getElement(Env.getCtx(), "GrandTotal"));
235 columnNames.add(Msg.getElement(Env.getCtx(), "Description"));
236 columnNames.add(Msg.getElement(Env.getCtx(), "ar_pi"));
237
238 return columnNames;
239 }
240
241 protected Vector<Vector<Object>> getOrderData(int C_Order_ID, String kst_InvoiceVendor, String ar_pi)
242 {
243 Vector<Vector<Object>> data = new Vector<Vector<Object>>();
244
245 StringBuffer sql = new StringBuffer();
246 //sql.append("select co.C_Order_ID, co.DocumentNo, co.DateOrdered, co.GrandTotal, co.Description ")
247 sql.append("select co.C_Order_ID, co.DocumentNo, co.DateOrdered, co.ar_pi as noPI, ")
248 .append("co.GrandTotal - ( ")
249 .append("select coalesce(sum(rpl.PayAmt),0) ")
250 .append("from RDO_PrepayRequestLine rpl join RDO_PrepayRequest rpp on (rpl.RDO_PrepayRequest_ID=rpp.RDO_PrepayRequest_ID) ")
251 .append("where rpp.DocStatus in ('DR', 'IP', 'CO', 'CL') ")
252 .append("and rpl.C_Order_ID=co.C_Order_ID) as GrandTotal, ")
253 .append("co.Description ")
254 .append("from C_Order co ")
255 .append("where co.IsSOTrx=? ")
256 .append("and co.DocStatus in ('CO', 'CL') ")
257 .append("and co.C_BPartner_ID=? ")
258 .append("and co.C_Currency_ID=? ")
259 /* Added by Roynaldo - 20191010 - Created>08-Oct & Payment Term=Before Shipment or letter of credit */
260 .append("and trunc(co.Created)>='2019-10-08' ");
261 if(ar_pi!= null && !ar_pi.equals("")){
262 sql.append(" and co.ar_pi like '"+ar_pi+"' ");
263 }
264 sql.append("and co.C_PaymentTerm_ID IN (select c_paymentterm_id from C_PaymentTerm "
265 + "where lower(name) like '%letter of credit%' or lower (name) like '%before shipment%') ")
266 /* End Added by Roynaldo - 20191010 - Created>08-Oct & Payment Term=Before Shipment */
267 .append("and exists ( ")
268 .append("select 1 ")
269 .append("from C_OrderLine col ")
270 .append("where col.C_Order_ID=co.C_Order_ID ")
271 /* Added by Roynaldo - 20191010 - Order Line with Price Only */
272 .append("and col.PriceActual>0 ")
273 /* End Added by Roynaldo - 20191010 - Order Line with Price Only */
274 .append("and not exists ( ")
275 //.append("select 1 ")
276 .append("select rpl.C_Order_ID, coo.GrandTotal, sum(rpl.PayAmt) ")
277 .append("from RDO_PrepayRequestLine rpl join RDO_PrepayRequest rpp on (rpl.RDO_PrepayRequest_ID=rpp.RDO_PrepayRequest_ID) ")
278 //
279 .append("join C_Order coo on (rpl.C_Order_ID=coo.C_Order_ID) ")
280 .append("where rpp.C_BPartner_ID=co.C_BPartner_ID ")
281 .append("and rpp.DocStatus in ('DR', 'IP', 'CO', 'CL') ")
282 //.append("and rpl.C_Order_ID=co.C_Order_ID) ");
283 .append("and rpl.C_Order_ID=co.C_Order_ID ")
284 .append("and rpl.C_Order_ID=coo.C_Order_ID ")
285 .append("group by rpl.C_Order_ID, coo.GrandTotal ")
286 .append("having sum(rpl.PayAmt)>=coo.GrandTotal) ");
287
288 if (C_Order_ID>0)
289 sql.append("and co.C_Order_ID=?");
290
291 if (kst_InvoiceVendor!=null && !kst_InvoiceVendor.equals(""))
292 sql.append("and co.C_Order_ID IN (")
293 .append("select distinct coo.C_Order_ID ")
294 .append("from kst_Receipt kr join kst_ReceiptLine krl on (kr.kst_Receipt_ID=krl.kst_Receipt_ID) ")
295 .append("join kst_InvoiceVendor kiv on (kr.kst_InvoiceVendor_ID=kiv.kst_InvoiceVendor_ID) ")
296 .append("join C_OrderLine colo on (krl.C_OrderLine_ID=colo.C_OrderLine_ID) ")
297 .append("join C_Order coo on (colo.C_Order_ID=coo.C_Order_ID) ")
298 .append("where kiv.kst_InvoiceVendor=? ")
299 .append("and trunc(coo.Created)>='2019-10-08'")
300 .append("and coo.C_PaymentTerm_ID IN (select c_paymentterm_id from C_PaymentTerm "
301 + "where lower(name) like '%letter of credit%' or lower (name) like '%before shipment%') ")
302 .append(")");
303
304
305 //hanya ambil order yang nilai closed prepayment nya tidak sama dengan Y
306 sql.append(" AND co.tpb_closed_prepayment <> 'Y' )");
307
308 PreparedStatement pstmt = null;
309 ResultSet rs = null;
310 try{
311 pstmt = DB.prepareStatement(sql.toString(), null);
312 pstmt.setString(1, IsSOTrx? "Y" : "N");
313 pstmt.setInt(2, m_C_BPartner_ID);
314 pstmt.setInt(3, m_C_Currency_ID);
315 int idxpar = 3;
316 if (C_Order_ID>0){
317 idxpar++;
318 pstmt.setInt(idxpar, C_Order_ID);
319 }
320 if(kst_InvoiceVendor!=null && !kst_InvoiceVendor.equals("")){
321 idxpar++;
322 pstmt.setString(idxpar, kst_InvoiceVendor);
323 }
324 rs = pstmt.executeQuery();
325 while (rs.next()){
326 Vector<Object> line = new Vector<Object>(5);
327 line.add(new Boolean(false)); // 0-Selection
328 KeyNamePair pp = new KeyNamePair(rs.getInt("C_Order_ID"), rs.getString("DocumentNo"));
329 line.add(pp);
330 line.add(rs.getTimestamp("DateOrdered"));
331 line.add(rs.getBigDecimal("GrandTotal"));
332 line.add(rs.getString("Description"));
333 line.add(rs.getString("noPI"));
334 data.add(line);
335 }
336 }catch (SQLException e){
337 log.warning(e.toString());
338 }finally{
339 DB.close(rs, pstmt);
340 pstmt = null;
341 rs = null;
342 }
343
344 return data;
345 }
346
347 @Override
348 public void info(IMiniTable miniTable, IStatusBar statusBar) {
349 // TODO Auto-generated method stub
350
351 }
352
353 @Override
354 public boolean save(IMiniTable miniTable, String trxName) {
355 int prepayRequestID = (Integer) getGridTab().getValue("RDO_PrepayRequest_ID");
356 MRDOPrepayRequest prepayRequest = new MRDOPrepayRequest(Env.getCtx(), prepayRequestID, trxName);
357 if (log.isLoggable(Level.CONFIG)) log.config(prepayRequest.toString());
358
359 // Lines
360 for (int i = 0; i < miniTable.getRowCount(); i++)
361 {
362 if (((Boolean)miniTable.getValueAt(i, 0)).booleanValue())
363 {
364 KeyNamePair pp = (KeyNamePair) miniTable.getValueAt(i, 1);
365 int C_Order_ID = pp.getKey();
366
367 BigDecimal payAmt = (BigDecimal) miniTable.getValueAt(i, 3);
368
369 MRDOPrepayRequestLine requestLine = new MRDOPrepayRequestLine(Env.getCtx(), 0, trxName);
370 requestLine.setAD_Org_ID(prepayRequest.getAD_Org_ID());
371 requestLine.setRDO_PrepayRequest_ID(prepayRequestID);
372 requestLine.setC_Order_ID(C_Order_ID);
373 requestLine.setPayAmt(payAmt);
374 requestLine.saveEx(trxName);
375 }
376 }
377 return true;
378 }
379
380 protected void initBPartner (boolean forInvoice) throws Exception
381 {
382 // load BPartner
383 int AD_Column_ID = COLUMN_C_INVOICE_C_BPARTNER_ID; // C_Invoice.C_BPartner_ID
384 MLookup lookup = MLookupFactory.get (Env.getCtx(), p_WindowNo, 0, AD_Column_ID, DisplayType.Search);
385 bPartnerField = new WSearchEditor ("C_BPartner_ID", true, true, false, lookup);
386
387 int C_BPartner_ID = Env.getContextAsInt(Env.getCtx(), p_WindowNo, "C_BPartner_ID");
388 bPartnerField.setValue(new Integer(C_BPartner_ID));
389 }
390
391 protected void initCurrency() throws Exception
392 {
393 int AD_Column_ID = COLUMN_C_INVOICE_C_CURRENCY_ID; // C_Invoice.C_Currency_ID
394 MLookup lookup = MLookupFactory.get (Env.getCtx(), p_WindowNo, 0, AD_Column_ID, DisplayType.Search);
395 currencyField = new WSearchEditor ("C_Currency_ID", true, true, false, lookup);
396
397 int C_Currency_ID = Env.getContextAsInt(Env.getCtx(), p_WindowNo, "C_Currency_ID");
398 currencyField.setValue(new Integer(C_Currency_ID));
399 }
400
401 protected void initOrder() throws Exception
402 {
403 int AD_Column_ID = COLUMN_PREPAYREQUESTLINE_C_ORDER_ID;
404 MLookup lookup = MLookupFactory.get (Env.getCtx(), p_WindowNo, 0, AD_Column_ID, DisplayType.Search);
405 orderField = new WSearchEditor ("C_Order_ID", false, false, true, lookup);
406 }
407
408 protected void loadOrderData(int C_Order_ID, String kst_InvoiceVendor, String piNo)
409 {
410 loadTableOIS(getOrderData(C_Order_ID, kst_InvoiceVendor,piNo));
411 }
412
413 /**
414 * Load Order data into Table
415 * @param data data
416 */
417 protected void loadTableOIS (Vector<?> data)
418 {
419 window.getWListbox().clear();
420
421 // Remove previous listeners
422 window.getWListbox().getModel().removeTableModelListener(window);
423 // Set Model
424 ListModelTable model = new ListModelTable(data);
425 model.addTableModelListener(window);
426 window.getWListbox().setData(model, getOISColumnNames());
427 //
428
429 configureMiniTable(window.getWListbox());
430 } // loadOrder
431
432 private boolean m_actionActive = false;
433
434 @Override
435 public void onEvent(Event e) throws Exception {
436 if (m_actionActive)
437 return;
438 m_actionActive = true;
439
440 if (e.getTarget().equals(invoiceVendorField.getComponent()) || e.getTarget().equals(piNumberField.getComponent())){
441 int C_Order_ID = 0;
442 if (orderField.getValue()!=null)
443 C_Order_ID = (Integer) orderField.getValue();
444 String kst_InvoiceVendor = invoiceVendorField.getDisplay();
445 String piNo = (String) piNumberField.getValue();
446 loadOrderData(C_Order_ID, kst_InvoiceVendor,piNo);
447 }
448
449 m_actionActive = false;
450 }
451
452 @Override
453 public void valueChange(ValueChangeEvent e) {
454 if (log.isLoggable(Level.CONFIG)) log.config(e.getPropertyName() + "=" + e.getNewValue());
455
456 if(e.getNewValue()==null)
457 return ;
458
459 if (e.getPropertyName().equals("C_Order_ID"))
460 {
461 int C_Order_ID = (Integer) e.getNewValue();
462 String kst_InvoiceVendor = (String) invoiceVendorField.getValue();
463 String piNo = (String) piNumberField.getValue();
464 loadOrderData(C_Order_ID, kst_InvoiceVendor,piNo);
465 }
466
467 }
468
469}