|
1
14 package org.compiere.apps.form;
15
16 import java.awt.*;
17 import javax.swing.*;
18 import java.awt.event.*;
19 import javax.swing.border.*;
20 import javax.swing.event.*;
21 import java.util.*;
22 import java.sql.*;
23 import java.math.*;
24
25 import org.compiere.util.*;
26 import org.compiere.plaf.*;
27 import org.compiere.swing.*;
28 import org.compiere.apps.*;
29 import org.compiere.minigrid.*;
30 import org.compiere.grid.ed.*;
31 import org.compiere.model.*;
32
33
39 public class VMatch extends CPanel
40 implements FormPanel, ActionListener, TableModelListener, ListSelectionListener
41 {
42
45 public VMatch()
46 {
47 }
49
54 public void init (int WindowNo, FormFrame frame)
55 {
56 m_WindowNo = WindowNo;
57 m_frame = frame;
58 Log.trace(Log.l1_User, "VMatch.init - WinNo=" + m_WindowNo,
59 "AD_Client_ID=" + m_AD_Client_ID + ", AD_Org_ID=" + m_AD_Org_ID + ", By=" + m_by);
60 Env.setContext(Env.getCtx(), m_WindowNo, "IsSOTrx", "N");
61
62 try
63 {
64 jbInit();
65 dynInit();
66 frame.getContentPane().add(mainPanel, BorderLayout.CENTER);
67 frame.getContentPane().add(statusBar, BorderLayout.SOUTH);
68 }
69 catch(Exception  e)
70 {
71 Log.error("VMatch.init", e);
72 }
73 }
75
76 private int m_WindowNo = 0;
77
78 private FormFrame m_frame;
79
80 private int m_AD_Client_ID = Env.getContextAsInt(Env.getCtx(), m_WindowNo, "#AD_Client_ID");;
81 private int m_AD_Org_ID = Env.getContextAsInt(Env.getCtx(), m_WindowNo, "#AD_Org_ID");;
82 private int m_by = Env.getContextAsInt(Env.getCtx(), m_WindowNo, "#AD_User_ID");
83
84
85 private String [] m_matchOptions = new String [] {
86 Msg.getElement(Env.getCtx(), "C_Invoice_ID", false),
87 Msg.getElement(Env.getCtx(), "M_InOut_ID", false),
88 Msg.getElement(Env.getCtx(), "C_Order_ID", false) };
89 private static final int MATCH_INVOICE = 0;
90 private static final int MATCH_SHIPMENT = 1;
91 private static final int MATCH_ORDER = 2;
92
93
94 private String [] m_matchMode = new String [] {
95 Msg.translate(Env.getCtx(), "NotMatched"),
96 Msg.translate(Env.getCtx(), "Matched")};
97 private static final int MODE_NOTMATCHED = 0;
98 private static final int MODE_MATCHED = 1;
99
100
101 private static final int I_BPartner = 3;
102 private static final int I_Line = 4;
103 private static final int I_Product = 5;
104 private static final int I_QTY = 6;
105 private static final int I_MATCHED = 7;
106
107
108 private StringBuffer m_sql = null;
109 private String m_dateColumn = "";
110 private String m_qtyColumn = "";
111 private String m_groupBy = "";
112
113 private BigDecimal m_xMatched = Env.ZERO;
114 private BigDecimal m_xMatchedTo = Env.ZERO;
115
116 private CPanel mainPanel = new CPanel();
118 private StatusBar statusBar = new StatusBar();
119 private BorderLayout mainLayout = new BorderLayout();
120 private CPanel northPanel = new CPanel();
121 private GridBagLayout northLayout = new GridBagLayout();
122 private CLabel matchFromLabel = new CLabel();
123 private CComboBox matchFrom = new CComboBox(m_matchOptions);
124 private CLabel matchToLabel = new CLabel();
125 private CComboBox matchTo = new CComboBox();
126 private CLabel matchModeLabel = new CLabel();
127 private CComboBox matchMode = new CComboBox(m_matchMode);
128 private VLookup onlyVendor = VLookup.createBPartner(m_WindowNo);
129 private VLookup onlyProduct = VLookup.createProduct(m_WindowNo);
130 private CLabel onlyVendorLabel = new CLabel();
131 private CLabel onlyProductLabel = new CLabel();
132 private CLabel dateFromLabel = new CLabel();
133 private CLabel dateToLabel = new CLabel();
134 private VDate dateFrom = new VDate("DateFrom", false, false, true, DisplayType.Date, "DateFrom");
135 private VDate dateTo = new VDate("DateTo", false, false, true, DisplayType.Date, "DateTo");
136 private CButton bSearch = new CButton();
137 private CPanel southPanel = new CPanel();
138 private GridBagLayout southLayout = new GridBagLayout();
139 private CLabel xMatchedLabel = new CLabel();
140 private CLabel xMatchedToLabel = new CLabel();
141 private CLabel differenceLabel = new CLabel();
142 private VNumber xMatched = new VNumber("xMatched", false, true, false, DisplayType.Quantity, "xMatched");
143 private VNumber xMatchedTo = new VNumber("xMatchedTo", false, true, false, DisplayType.Quantity, "xMatchedTo");
144 private VNumber difference = new VNumber("Difference", false, true, false, DisplayType.Quantity, "Difference");
145 private CButton bProcess = new CButton();
146 private CPanel centerPanel = new CPanel();
147 private BorderLayout centerLayout = new BorderLayout(5,5);
148 private JScrollPane xMatchedScrollPane = new JScrollPane();
149 private TitledBorder xMatchedBorder = new TitledBorder("xMatched");
150 private MiniTable xMatchedTable = new MiniTable();
151 private JScrollPane xMatchedToScrollPane = new JScrollPane();
152 private TitledBorder xMatchedToBorder = new TitledBorder("xMatchedTo");
153 private MiniTable xMatchedToTable = new MiniTable();
154 private CPanel xPanel = new CPanel();
155 private JCheckBox sameProduct = new JCheckBox();
156 private JCheckBox sameBPartner = new JCheckBox();
157 private JCheckBox sameQty = new JCheckBox();
158 private FlowLayout xLayout = new FlowLayout(FlowLayout.CENTER, 10, 0);
159
160
173 private void jbInit() throws Exception
174 {
175 mainPanel.setLayout(mainLayout);
176 northPanel.setLayout(northLayout);
177 matchFromLabel.setText(Msg.translate(Env.getCtx(), "MatchFrom"));
178 matchToLabel.setText(Msg.translate(Env.getCtx(), "MatchTo"));
179 matchModeLabel.setText(Msg.translate(Env.getCtx(), "MatchMode"));
180 onlyVendorLabel.setText(Msg.translate(Env.getCtx(), "C_BPartner_ID"));
181 onlyProductLabel.setText(Msg.translate(Env.getCtx(), "M_Product_ID"));
182 dateFromLabel.setText(Msg.translate(Env.getCtx(), "DateFrom"));
183 dateToLabel.setText(Msg.translate(Env.getCtx(), "DateTo"));
184 bSearch.setText(Msg.translate(Env.getCtx(), "Search"));
185 southPanel.setLayout(southLayout);
186 xMatchedLabel.setText(Msg.translate(Env.getCtx(), "ToBeMatched"));
187 xMatchedToLabel.setText(Msg.translate(Env.getCtx(), "Matching"));
188 differenceLabel.setText(Msg.translate(Env.getCtx(), "Difference"));
189 bProcess.setText(Msg.translate(Env.getCtx(), "Process"));
190 centerPanel.setLayout(centerLayout);
191 xMatchedScrollPane.setBorder(xMatchedBorder);
192 xMatchedScrollPane.setPreferredSize(new Dimension(450, 200));
193 xMatchedToScrollPane.setBorder(xMatchedToBorder);
194 xMatchedToScrollPane.setPreferredSize(new Dimension(450, 200));
195 sameProduct.setSelected(true);
196 sameProduct.setText(Msg.translate(Env.getCtx(), "SameProduct"));
197 sameBPartner.setSelected(true);
198 sameBPartner.setText(Msg.translate(Env.getCtx(), "SameBPartner"));
199 sameQty.setSelected(true);
200 sameQty.setText(Msg.translate(Env.getCtx(), "SameQty"));
201 xPanel.setLayout(xLayout);
202 mainPanel.add(northPanel, BorderLayout.NORTH);
203 northPanel.add(matchFromLabel, new GridBagConstraints(0, 0, 1, 1, 0.0, 0.0
204 ,GridBagConstraints.EAST, GridBagConstraints.NONE, new Insets(12, 12, 5, 5), 0, 0));
205 northPanel.add(matchFrom, new GridBagConstraints(1, 0, 1, 1, 0.0, 0.0
206 ,GridBagConstraints.WEST, GridBagConstraints.HORIZONTAL, new Insets(12, 0, 5, 0), 0, 0));
207 northPanel.add(matchToLabel, new GridBagConstraints(2, 0, 1, 1, 0.0, 0.0
208 ,GridBagConstraints.EAST, GridBagConstraints.NONE, new Insets(12, 5, 5, 5), 0, 0));
209 northPanel.add(matchTo, new GridBagConstraints(3, 0, 1, 1, 0.0, 0.0
210 ,GridBagConstraints.WEST, GridBagConstraints.HORIZONTAL, new Insets(12, 0, 5, 0), 0, 0));
211 northPanel.add(matchModeLabel, new GridBagConstraints(0, 1, 1, 1, 0.0, 0.0
212 ,GridBagConstraints.EAST, GridBagConstraints.NONE, new Insets(0, 12, 5, 5), 0, 0));
213 northPanel.add(matchMode, new GridBagConstraints(1, 1, 1, 1, 0.0, 0.0
214 ,GridBagConstraints.WEST, GridBagConstraints.HORIZONTAL, new Insets(0, 0, 5, 0), 0, 0));
215 northPanel.add(onlyVendor, new GridBagConstraints(1, 2, 1, 1, 0.0, 0.0
216 ,GridBagConstraints.WEST, GridBagConstraints.HORIZONTAL, new Insets(5, 0, 5, 0), 0, 0));
217 northPanel.add(onlyProduct, new GridBagConstraints(3, 2, 1, 1, 0.0, 0.0
218 ,GridBagConstraints.WEST, GridBagConstraints.HORIZONTAL, new Insets(5, 0, 5, 0), 0, 0));
219 northPanel.add(onlyVendorLabel, new GridBagConstraints(0, 2, 1, 1, 0.0, 0.0
220 ,GridBagConstraints.EAST, GridBagConstraints.NONE, new Insets(5, 12, 5, 5), 0, 0));
221 northPanel.add(onlyProductLabel, new GridBagConstraints(2, 2, 1, 1, 0.0, 0.0
222 ,GridBagConstraints.EAST, GridBagConstraints.NONE, new Insets(5, 5, 5, 5), 0, 0));
223 northPanel.add(dateFromLabel, new GridBagConstraints(0, 3, 1, 1, 0.0, 0.0
224 ,GridBagConstraints.EAST, GridBagConstraints.NONE, new Insets(0, 12, 5, 5), 0, 0));
225 northPanel.add(dateToLabel, new GridBagConstraints(2, 3, 1, 1, 0.0, 0.0
226 ,GridBagConstraints.EAST, GridBagConstraints.NONE, new Insets(0, 0, 5, 5), 0, 0));
227 northPanel.add(dateFrom, new GridBagConstraints(1, 3, 1, 1, 0.0, 0.0
228 ,GridBagConstraints.WEST, GridBagConstraints.HORIZONTAL, new Insets(0, 0, 5, 0), 0, 0));
229 northPanel.add(dateTo, new GridBagConstraints(3, 3, 1, 1, 0.0, 0.0
230 ,GridBagConstraints.WEST, GridBagConstraints.HORIZONTAL, new Insets(0, 0, 5, 0), 0, 0));
231 northPanel.add(bSearch, new GridBagConstraints(4, 3, 1, 1, 0.0, 0.0
232 ,GridBagConstraints.EAST, GridBagConstraints.NONE, new Insets(0, 12, 5, 12), 0, 0));
233 mainPanel.add(southPanel, BorderLayout.SOUTH);
234 southPanel.add(xMatchedLabel, new GridBagConstraints(0, 0, 1, 1, 0.0, 0.0
235 ,GridBagConstraints.EAST, GridBagConstraints.NONE, new Insets(5, 12, 5, 5), 0, 0));
236 southPanel.add(xMatched, new GridBagConstraints(1, 0, 1, 1, 0.0, 0.0
237 ,GridBagConstraints.WEST, GridBagConstraints.HORIZONTAL, new Insets(5, 0, 5, 0), 0, 0));
238 southPanel.add(xMatchedToLabel, new GridBagConstraints(2, 0, 1, 1, 0.0, 0.0
239 ,GridBagConstraints.EAST, GridBagConstraints.NONE, new Insets(5, 10, 5, 5), 0, 0));
240 southPanel.add(bProcess, new GridBagConstraints(6, 0, 1, 1, 0.0, 0.0
241 ,GridBagConstraints.NORTHEAST, GridBagConstraints.NONE, new Insets(5, 12, 5, 12), 0, 0));
242 southPanel.add(differenceLabel, new GridBagConstraints(4, 0, 1, 1, 0.0, 0.0
243 ,GridBagConstraints.EAST, GridBagConstraints.NONE, new Insets(5, 10, 5, 5), 0, 0));
244 mainPanel.add(centerPanel, BorderLayout.CENTER);
245 centerPanel.add(xMatchedScrollPane, BorderLayout.NORTH);
246 xMatchedScrollPane.getViewport().add(xMatchedTable, null);
247 centerPanel.add(xMatchedToScrollPane, BorderLayout.SOUTH);
248 centerPanel.add(xPanel, BorderLayout.CENTER);
249 xPanel.add(sameBPartner, null);
250 xPanel.add(sameProduct, null);
251 xPanel.add(sameQty, null);
252 xMatchedToScrollPane.getViewport().add(xMatchedToTable, null);
253 southPanel.add(difference, new GridBagConstraints(5, 0, 1, 1, 0.0, 0.0
254 ,GridBagConstraints.WEST, GridBagConstraints.HORIZONTAL, new Insets(5, 0, 5, 0), 0, 0));
255 southPanel.add(xMatchedTo, new GridBagConstraints(3, 0, 1, 1, 0.0, 0.0
256 ,GridBagConstraints.WEST, GridBagConstraints.HORIZONTAL, new Insets(5, 0, 5, 0), 0, 0));
257 }
259
263 private void dynInit()
264 {
265 ColumnInfo[] layout = new ColumnInfo[] {
266 new ColumnInfo(" ", ".", IDColumn.class, false, false, ""),
267 new ColumnInfo(Msg.translate(Env.getCtx(), "DocumentNo"), ".", String .class), new ColumnInfo(Msg.translate(Env.getCtx(), "Date"), ".", Timestamp.class),
269 new ColumnInfo(Msg.translate(Env.getCtx(), "C_BPartner_ID"),".", KeyNamePair.class, "."), new ColumnInfo(Msg.translate(Env.getCtx(), "Line"), ".", KeyNamePair.class, "."),
271 new ColumnInfo(Msg.translate(Env.getCtx(), "M_Product_ID"), ".", KeyNamePair.class, "."), new ColumnInfo(Msg.translate(Env.getCtx(), "Qty"), ".", Double .class),
273 new ColumnInfo(Msg.translate(Env.getCtx(), "Matched"), ".", Double .class)
274 };
275
276 xMatchedTable.prepareTable(layout, "", "", false, "");
277 xMatchedToTable.prepareTable(layout, "", "", true, "");
278
279 CompiereColor.setBackground (this);
281
282 matchFrom.addActionListener(this);
284 matchTo.addActionListener(this);
285 bSearch.addActionListener(this);
286 xMatchedTable.getSelectionModel().addListSelectionListener(this);
287 xMatchedToTable.getModel().addTableModelListener(this);
288 bProcess.addActionListener(this);
289 sameBPartner.addActionListener(this);
290 sameProduct.addActionListener(this);
291 sameQty.addActionListener(this);
292 cmd_matchFrom();
294 statusBar.setStatusLine("");
295 statusBar.setStatusDB(0);
296 }
298
301 public void dispose()
302 {
303 m_frame.dispose();
304 }
306
307
308
312 public void actionPerformed (ActionEvent e)
313 {
314 setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
315 if (e.getSource() == matchFrom)
316 cmd_matchFrom();
317 else if (e.getSource() == matchTo)
318 cmd_matchTo();
319 else if (e.getSource() == bSearch)
320 cmd_search();
321 else if (e.getSource() == bProcess)
322 cmd_process();
323 else if (e.getSource() == sameBPartner
324 || e.getSource() == sameProduct
325 || e.getSource() == sameQty)
326 cmd_searchTo();
327 setCursor(Cursor.getDefaultCursor());
328 }
330
333 private void cmd_matchFrom()
334 {
335 String selection = (String )matchFrom.getSelectedItem();
337 Vector vector = new Vector(2);
338 if (selection.equals(m_matchOptions[MATCH_INVOICE]))
339 vector.add(m_matchOptions[MATCH_SHIPMENT]);
340 else if (selection.equals(m_matchOptions[MATCH_ORDER]))
341 vector.add(m_matchOptions[MATCH_SHIPMENT]);
342 else {
344 vector.add(m_matchOptions[MATCH_INVOICE]);
345 vector.add(m_matchOptions[MATCH_ORDER]);
346 }
347 matchTo.setModel(new DefaultComboBoxModel(vector));
348 xMatchedBorder.setTitle(selection);
350 xMatchedScrollPane.repaint();
351 xMatchedTable.setRowCount(0);
353 cmd_matchTo();
355 }
357
360 private void cmd_matchTo()
361 {
362 String selection = (String )matchTo.getSelectedItem();
364 xMatchedToBorder.setTitle(selection);
365 xMatchedToScrollPane.repaint();
366 xMatchedToTable.setRowCount(0);
368 }
370
373 private void cmd_search()
374 {
375 Log.trace(Log.l3_Util, "VMatch.cmd_search");
376 int display = matchFrom.getSelectedIndex();
378 String matchToString = (String )matchTo.getSelectedItem();
379 int matchTo = MATCH_INVOICE;
380 if (matchToString.equals(m_matchOptions[MATCH_SHIPMENT]))
381 matchTo = MATCH_SHIPMENT;
382 else if (matchToString.equals(m_matchOptions[MATCH_ORDER]))
383 matchTo = MATCH_ORDER;
384 tableInit(display, matchTo);
386 if (onlyProduct.getValue() != null)
389 {
390 Integer Product = (Integer )onlyProduct.getValue();
391 m_sql.append(" AND lin.M_Product_ID=").append(Product);
392 }
393 if (onlyVendor.getValue() != null)
395 {
396 Integer Vendor = (Integer )onlyVendor.getValue();
397 m_sql.append(" AND hdr.C_BPartner_ID=").append(Vendor);
398 }
399 Timestamp from = (Timestamp)dateFrom.getValue();
401 Timestamp to = (Timestamp)dateTo.getValue();
402 if (from != null && to != null)
403 m_sql.append(" AND ").append(m_dateColumn).append(" BETWEEN ")
404 .append(DB.TO_DATE(from)).append(" AND ").append(DB.TO_DATE(to));
405 else if (from != null)
406 m_sql.append(" AND ").append(m_dateColumn).append(" >= ").append(DB.TO_DATE(from));
407 else if (to != null)
408 m_sql.append(" AND ").append(m_dateColumn).append(" <= ").append(DB.TO_DATE(to));
409
410 tableLoad (xMatchedTable);
412 xMatched.setValue(Env.ZERO);
413 statusBar.setStatusLine(matchFrom.getSelectedItem().toString()
415 + "# = " + xMatchedTable.getRowCount(),
416 xMatchedTable.getRowCount() == 0);
417 statusBar.setStatusDB(0);
418 }
420
423 private void cmd_process()
424 {
425 Log.trace(Log.l4_Data, "VMatch.cmd_process");
426 int matchedRow = xMatchedTable.getSelectedRow();
428 if (matchedRow < 0)
429 return;
430 KeyNamePair lineMatched = (KeyNamePair)xMatchedTable.getValueAt(matchedRow, I_Line);
432 KeyNamePair Product = (KeyNamePair)xMatchedTable.getValueAt(matchedRow, I_Product);
433
434 int M_Product_ID = Product.getKey();
435 double totalQty = m_xMatched.doubleValue();
436
437 for (int row = 0; row < xMatchedToTable.getRowCount(); row++)
439 {
440 IDColumn id = (IDColumn)xMatchedToTable.getValueAt(row, 0);
441 if (id != null && id.isSelected())
442 {
443 KeyNamePair ProductCompare = (KeyNamePair)xMatchedToTable.getValueAt(row, I_Product);
445 if (Product.getKey() != ProductCompare.getKey())
446 continue;
447
448 KeyNamePair lineMatchedTo = (KeyNamePair)xMatchedToTable.getValueAt(row, I_Line);
449
450 double qty = 0.0;
452 if (matchMode.getSelectedIndex() == MODE_NOTMATCHED)
453 qty = ((Double )xMatchedToTable.getValueAt(row, I_QTY)).doubleValue(); qty -= ((Double )xMatchedToTable.getValueAt(row, I_MATCHED)).doubleValue(); if (qty > totalQty)
456 qty = totalQty;
457 totalQty -= qty;
458
459 boolean invoice = true;
461 if (matchFrom.getSelectedIndex() == MATCH_ORDER ||
462 matchTo.getSelectedItem().equals(m_matchOptions[MATCH_ORDER]))
463 invoice = false;
464 int M_InOutLine_ID = 0;
466 int Line_ID = 0;
467 if (matchFrom.getSelectedIndex() == MATCH_SHIPMENT)
468 {
469 M_InOutLine_ID = lineMatched.getKey(); Line_ID = lineMatchedTo.getKey();
471 }
472 else
473 {
474 M_InOutLine_ID = lineMatchedTo.getKey(); Line_ID = lineMatched.getKey();
476 }
477
478 createMatchRecord(invoice, M_InOutLine_ID, Line_ID, M_Product_ID, qty);
480 }
481 }
482 cmd_search();
484 }
486
487
488
492 public void valueChanged (ListSelectionEvent e)
493 {
494 if (e.getValueIsAdjusting())
495 return;
496 setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
498 cmd_searchTo();
499 setCursor(Cursor.getDefaultCursor());
500 }
502
505 private void cmd_searchTo()
506 {
507 int row = xMatchedTable.getSelectedRow();
508 Log.trace(Log.l3_Util, "VMatch.cmd_searchTo", "Row=" + row);
509
510 double qty = 0.0;
511 if (row < 0)
512 {
513 xMatchedToTable.setRowCount(0);
514 }
515 else
516 {
517 String displayString = (String )matchTo.getSelectedItem();
519 int display = MATCH_INVOICE;
520 if (displayString.equals(m_matchOptions[MATCH_SHIPMENT]))
521 display = MATCH_SHIPMENT;
522 else if (displayString.equals(m_matchOptions[MATCH_ORDER]))
523 display = MATCH_ORDER;
524 int matchTo = matchFrom.getSelectedIndex();
525 tableInit (display, matchTo); KeyNamePair BPartner = (KeyNamePair)xMatchedTable.getValueAt(row, I_BPartner);
528 KeyNamePair Product = (KeyNamePair)xMatchedTable.getValueAt(row, I_Product);
529 Log.trace(Log.l5_DData, "BPartner=" + BPartner, "Product=" + Product);
530 if (sameBPartner.isSelected())
532 m_sql.append(" AND hdr.C_BPartner_ID=").append(BPartner.getKey());
533 if (sameProduct.isSelected())
534 m_sql.append(" AND lin.M_Product_ID=").append(Product.getKey());
535
536 double docQty = ((Double )xMatchedTable.getValueAt(row, I_QTY)).doubleValue();
538 double matchedQty = ((Double )xMatchedTable.getValueAt(row, I_MATCHED)).doubleValue();
539 qty = docQty - matchedQty;
540 if (sameQty.isSelected())
541 m_sql.append(" AND ").append(m_qtyColumn).append("=").append(docQty);
542 tableLoad (xMatchedToTable);
544 }
545 m_xMatched = new BigDecimal (qty);
547 xMatched.setValue(m_xMatched);
548 xMatchedTo.setValue(Env.ZERO);
549 difference.setValue(m_xMatched);
550 statusBar.setStatusLine(matchFrom.getSelectedItem().toString()
552 + "# = " + xMatchedTable.getRowCount() + " - "
553 + matchTo.getSelectedItem().toString()
554 + "# = " + xMatchedToTable.getRowCount(),
555 xMatchedToTable.getRowCount() == 0);
556 statusBar.setStatusDB(0);
557 }
559
560
561
565 public void tableChanged (TableModelEvent e)
566 {
567 if (e.getColumn() != 0)
568 return;
569 Log.trace(Log.l4_Data, "VMatch.tableChanged",
570 "Row=" + e.getFirstRow() + "-" + e.getLastRow() + ", Col=" + e.getColumn()
571 + ", Type=" + e.getType());
572 setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
573
574 int matchedRow = xMatchedTable.getSelectedRow();
576 KeyNamePair Product = (KeyNamePair)xMatchedTable.getValueAt(matchedRow, 5);
577
578 double qty = 0.0;
580 int noRows = 0;
581 for (int row = 0; row < xMatchedToTable.getRowCount(); row++)
582 {
583 IDColumn id = (IDColumn)xMatchedToTable.getValueAt(row, 0);
584 if (id != null && id.isSelected())
585 {
586 KeyNamePair ProductCompare = (KeyNamePair)xMatchedToTable.getValueAt(row, 5);
587 if (Product.getKey() != ProductCompare.getKey())
588 {
589 id.setSelected(false);
590 }
591 else
592 {
593 if (matchMode.getSelectedIndex() == MODE_NOTMATCHED)
594 qty += ((Double )xMatchedToTable.getValueAt(row, I_QTY)).doubleValue(); qty -= ((Double )xMatchedToTable.getValueAt(row, I_MATCHED)).doubleValue(); noRows++;
597 }
598 }
599 }
600 m_xMatchedTo = new BigDecimal(qty);
602 xMatchedTo.setValue(m_xMatchedTo);
603 difference.setValue(m_xMatched.subtract(m_xMatchedTo));
604 bProcess.setEnabled(noRows != 0);
605 setCursor(Cursor.getDefaultCursor());
606 statusBar.setStatusDB(noRows);
608 }
610
611
612
625 private void tableInit (int display, int matchTo)
626 {
627 boolean matched = matchMode.getSelectedIndex() == MODE_MATCHED;
628 Log.trace(Log.l4_Data, "VMatch.tableInit", "Display=" + m_matchOptions[display]
629 + ", MatchTo=" + m_matchOptions[matchTo]
630 + ", Matched=" + matched);
631
632 m_sql = new StringBuffer ();
633 if (display == MATCH_INVOICE)
634 {
635 m_dateColumn = "hdr.DateInvoiced";
636 m_qtyColumn = "lin.QtyInvoiced";
637 m_sql.append("SELECT hdr.C_Invoice_ID,hdr.DocumentNo, hdr.DateInvoiced, bp.Name,hdr.C_BPartner_ID,"
638 + " lin.Line,lin.C_InvoiceLine_ID, p.Name,lin.M_Product_ID,"
639 + " lin.QtyInvoiced,SUM(NVL(mi.Qty,0)) "
640 + "FROM C_Invoice hdr, C_BPartner bp, C_InvoiceLine lin, M_Product p,"
641 + " C_DocType dt, M_MatchInv mi "
642 + "WHERE hdr.C_BPartner_ID=bp.C_BPartner_ID AND hdr.C_Invoice_ID=lin.C_Invoice_ID"
643 + " AND lin.M_Product_ID=p.M_Product_ID AND hdr.C_DocType_ID=dt.C_DocType_ID"
644 + " AND dt.DocBaseType IN ('API','APC')"
645 + " AND lin.C_InvoiceLine_ID=mi.C_InvoiceLine_ID(+)");
646 m_groupBy = " GROUP BY hdr.C_Invoice_ID,hdr.DocumentNo,hdr.DateInvoiced,bp.Name,hdr.C_BPartner_ID,"
647 + " lin.Line,lin.C_InvoiceLine_ID,p.Name,lin.M_Product_ID,lin.QtyInvoiced "
648 + "HAVING "
649 + (matched ? "0" : "lin.QtyInvoiced")
650 + "<>SUM(NVL(mi.Qty,0))";
651 }
652 else if (display == MATCH_ORDER)
653 {
654 m_dateColumn = "hdr.DateOrdered";
655 m_qtyColumn = "lin.QtyOrdered";
656 m_sql.append("SELECT hdr.C_Order_ID,hdr.DocumentNo, hdr.DateOrdered, bp.Name,hdr.C_BPartner_ID,"
657 + " lin.Line,lin.C_OrderLine_ID, p.Name,lin.M_Product_ID,"
658 + " lin.QtyOrdered,SUM(NVL(mo.Qty,0)) "
659 + "FROM C_Order hdr, C_BPartner bp, C_OrderLine lin, M_Product p,"
660 + " C_DocType dt, M_MatchPO mo "
661 + "WHERE hdr.C_BPartner_ID=bp.C_BPartner_ID AND hdr.C_Order_ID=lin.C_Order_ID"
662 + " AND lin.M_Product_ID=p.M_Product_ID AND hdr.C_DocType_ID=dt.C_DocType_ID"
663 + " AND dt.DocBaseType='POO'"
664 + " AND lin.C_OrderLine_ID=mo.C_OrderLine_ID(+)");
665 m_groupBy = " GROUP BY hdr.C_Order_ID,hdr.DocumentNo,hdr.DateOrdered,bp.Name,hdr.C_BPartner_ID,"
666 + " lin.Line,lin.C_OrderLine_ID,p.Name,lin.M_Product_ID,lin.QtyOrdered "
667 + "HAVING "
668 + (matched ? "0" : "lin.QtyOrdered")
669 + "<>SUM(NVL(mo.Qty,0))";
670 }
671 else {
673 m_dateColumn = "hdr.MovementDate";
674 m_qtyColumn = "lin.MovementQty";
675 boolean toPO = matchTo == MATCH_ORDER;
676 m_sql.append("SELECT hdr.M_InOut_ID,hdr.DocumentNo, hdr.MovementDate, bp.Name,hdr.C_BPartner_ID,"
677 + " lin.Line,lin.M_InOutLine_ID, p.Name,lin.M_Product_ID,"
678 + " lin.MovementQty,SUM(NVL(m.Qty,0)) "
679 + "FROM M_InOut hdr, C_BPartner bp, M_InOutLine lin, M_Product p, C_DocType dt, ")
680 .append(toPO ? "M_MatchPO m " : "M_MatchInv m ")
681 .append("WHERE hdr.C_BPartner_ID=bp.C_BPartner_ID AND hdr.M_InOut_ID=lin.M_InOut_ID"
682 + " AND lin.M_Product_ID=p.M_Product_ID AND hdr.C_DocType_ID = dt.C_DocType_ID"
683 + " AND dt.DocBaseType='MMR'"
684 + " AND lin.M_InOutLine_ID=m.M_InOutLine_ID(+)");
685 m_groupBy = " GROUP BY hdr.M_InOut_ID,hdr.DocumentNo,hdr.MovementDate,bp.Name,hdr.C_BPartner_ID,"
686 + " lin.Line,lin.M_InOutLine_ID,p.Name,lin.M_Product_ID,lin.MovementQty "
687 + "HAVING "
688 + (matched ? "0" : "lin.MovementQty")
689 + "<>SUM(NVL(m.Qty,0))";
690 }
691 Log.trace(7, "VMatch.tableInit", m_sql);
692 }
694
695
699 private void tableLoad (MiniTable table)
700 {
701 String sql = MRole.getDefault().addAccessSQL(
702 m_sql.toString(), "hdr", MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO)
703 + m_groupBy;
704 try
705 {
706 Statement stmt = DB.createStatement();
707 ResultSet rs = stmt.executeQuery(sql);
708 table.loadTable(rs);
709 stmt.close();
710 }
711 catch (SQLException e)
712 {
713 Log.error("MVatch.tableFill " + sql, e);
714 }
715 }
717
726 private boolean createMatchRecord (boolean invoice, int M_InOutLine_ID, int Line_ID,
727 int M_Product_ID, double qty)
728 {
729 if (qty == 0.0)
730 return true;
731 Log.trace(Log.l6_Database, "VMatch.createMatchRecord", "IsInvoice=" + invoice
732 + ", M_InOutLine_ID=" + M_InOutLine_ID + ", Line_ID=" + Line_ID
733 + ", M_Product_ID=" + M_Product_ID + ", Qty=" + qty);
734 StringBuffer sql = new StringBuffer ("INSERT INTO ");
736 int id = 0;
737 if (invoice)
738 {
739 sql.append("M_MatchInv (M_MatchInv_ID, ");
740 id = DB.getKeyNextNo(Env.getCtx(), m_WindowNo, "M_MatchInv");
741 }
742 else
743 {
744 sql.append("M_MatchPO (M_MatchPO_ID, ");
745 id = DB.getKeyNextNo(Env.getCtx(), m_WindowNo, "M_MatchInv");
746 }
747 sql.append("AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy, "
748 + "M_InOutLine_ID,");
749 sql.append(invoice ? "C_InvoiceLine_ID," : "C_OrderLine_ID,");
750 sql.append("M_Product_ID, DateTrx,Qty,"
751 + "Processing,Processed,Posted) VALUES (");
752 sql.append(id).append(",");
754 sql.append(m_AD_Client_ID).append(",").append(m_AD_Org_ID).append(",'Y',SysDate,")
756 .append(m_by).append(",SysDate,").append(m_by).append(",");
757 sql.append(M_InOutLine_ID).append(",").append(Line_ID).append(",");
759 if (M_Product_ID == 0)
761 sql.append("NULL");
762 else
763 sql.append(M_Product_ID);
764 sql.append(",SysDate,").append(qty).append(",");
765 sql.append("'N','Y','N')");
767 int no = DB.executeUpdate(sql.toString());
769 if (no != 1)
770 Log.error("VMatch.createMatchRecord - not created: " + sql.toString());
771
772 if (!invoice && no == 1)
774 {
775 sql = new StringBuffer ("UPDATE M_Storage "
777 + "SET QtyOrdered = QtyOrdered-").append(qty)
778 .append(" WHERE M_Product_ID=").append(M_Product_ID)
779 .append(" AND M_Locator_ID=(SELECT M_Locator_ID FROM M_InOutLine WHERE M_InOutLine_ID=")
780 .append(M_InOutLine_ID).append(")");
781 no = DB.executeUpdate(sql.toString());
782 if (no != 1)
783 Log.error("VMatch.createMatchRecord - QtyOrdered not updated: " + sql.toString());
784 }
785 return no == 1;
786 }
788 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.