1 14 package org.compiere.grid; 15 16 import javax.swing.table.*; 17 import java.util.*; 18 import java.sql.*; 19 import java.math.*; 20 import java.text.*; 21 import java.beans.*; 22 import java.awt.event.*; 23 24 import org.compiere.util.*; 25 import org.compiere.model.*; 26 import org.compiere.plaf.*; 27 import org.compiere.grid.ed.*; 28 29 35 public class VCreateFromShipment extends VCreateFrom implements VetoableChangeListener 36 { 37 41 VCreateFromShipment(MTab mTab) 42 { 43 super (mTab); 44 } 47 48 private int m_C_Invoice_ID = 0; 49 50 55 protected boolean dynInit() throws Exception 56 { 57 Log.trace(Log.l3_Util, "VCreateFromShipment.dynInit"); 58 setTitle(Msg.getElement(Env.getCtx(), "M_InOut_ID", false) + " .. " + Msg.translate(Env.getCtx(), "CreateFrom")); 59 60 parameterBankPanel.setVisible(false); 61 shipmentLabel.setVisible(false); 62 shipmentField.setVisible(false); 63 64 int AD_Column_ID = 3537; MLocatorLookup locator = new MLocatorLookup(Env.getCtx(), p_WindowNo); 67 locatorField = new VLocator ("M_Locator_ID", true, false, true, locator, p_WindowNo); 68 69 initBPartner(false); 70 bPartnerField.addVetoableChangeListener(this); 71 return true; 72 } 74 78 protected void initBPDetails(int C_BPartner_ID) 79 { 80 Log.trace(Log.l3_Util, "VCreateFromShipment.initBPDetails"); 81 82 invoiceField.removeActionListener(this); 84 invoiceField.removeAllItems(); 85 KeyNamePair pp = new KeyNamePair(0,""); 87 invoiceField.addItem(pp); 88 StringBuffer display = new StringBuffer ("i.DocumentNo||' - '||") 89 .append(DB.TO_CHAR("DateInvoiced", DisplayType.Date, Env.getAD_Language(Env.getCtx()))) 90 .append("|| ' - ' ||") 91 .append(DB.TO_CHAR("GrandTotal", DisplayType.Amount, Env.getAD_Language(Env.getCtx()))); 92 StringBuffer sql = new StringBuffer ("SELECT i.C_Invoice_ID,").append(display) 94 .append(" FROM C_Invoice i " 95 + "WHERE i.C_BPartner_ID=? AND i.IsSOTrx='N' AND i.DocStatus IN ('CL','CO')" 96 + " AND i.C_Invoice_ID IN " 97 + "(SELECT il.C_Invoice_ID FROM C_InvoiceLine il" 98 + " LEFT OUTER JOIN M_MatchInv mi ON (il.C_InvoiceLine_ID=mi.C_InvoiceLine_ID) " 99 + "GROUP BY il.C_Invoice_ID,mi.C_InvoiceLine_ID,il.QtyInvoiced " 100 + "HAVING (il.QtyInvoiced<>SUM(mi.Qty) AND mi.C_InvoiceLine_ID IS NOT NULL)" 101 + " OR mi.C_InvoiceLine_ID IS NULL) " 102 + "ORDER BY i.DateInvoiced"); 103 104 try 105 { 106 PreparedStatement pstmt = DB.prepareStatement(sql.toString()); 107 pstmt.setInt(1, C_BPartner_ID); 108 ResultSet rs = pstmt.executeQuery(); 109 while (rs.next()) 110 { 111 pp = new KeyNamePair(rs.getInt(1), rs.getString(2)); 112 invoiceField.addItem(pp); 113 } 114 rs.close(); 115 pstmt.close(); 116 } 117 catch (SQLException e) 118 { 119 Log.error ("VCreateFromShipment.initBPDetails\nSQL=" + sql, e); 120 } 121 invoiceField.setSelectedIndex(0); 122 invoiceField.addActionListener(this); 123 } 125 126 130 public void actionPerformed(ActionEvent e) 131 { 132 super.actionPerformed(e); 133 Log.trace(Log.l3_Util, "VCreateFromShipment.actionPerformed " + e.getActionCommand()); 134 135 if (e.getSource().equals(orderField)) 137 { 138 KeyNamePair pp = (KeyNamePair)orderField.getSelectedItem(); 139 if (pp == null || pp.getKey() == 0) 140 ; 141 else 142 { 143 int C_Order_ID = pp.getKey(); 144 invoiceField.setSelectedIndex(-1); 146 shipmentField.setSelectedIndex(-1); 147 loadOrder(C_Order_ID, false); 148 } 149 } 150 else if (e.getSource().equals(invoiceField)) 152 { 153 KeyNamePair pp = (KeyNamePair)invoiceField.getSelectedItem(); 154 if (pp == null || pp.getKey() == 0) 155 ; 156 else 157 { 158 int C_Invoice_ID = pp.getKey(); 159 orderField.setSelectedIndex(-1); 161 shipmentField.setSelectedIndex(-1); 162 loadInvoice(C_Invoice_ID); 163 } 164 } 165 } 167 168 172 public void vetoableChange (PropertyChangeEvent e) 173 { 174 Log.trace(Log.l3_Util, "VCreateFromShipment.vetoableChange " + e.getPropertyName() + "=" + e.getNewValue()); 175 176 if (e.getPropertyName() == "C_BPartner_ID") 178 { 179 int C_BPartner_ID = ((Integer )e.getNewValue()).intValue(); 180 initBPartnerOIS (C_BPartner_ID, false); 181 } 182 tableChanged(null); 183 } 185 186 190 private void loadInvoice (int C_Invoice_ID) 191 { 192 Log.trace(Log.l3_Util, "VCreateFromShipment.loadInvoice - " + C_Invoice_ID); 193 m_C_Invoice_ID = C_Invoice_ID; 195 Vector data = new Vector(); 196 StringBuffer sql = new StringBuffer ("SELECT l.QtyInvoiced-SUM(NVL(mi.Qty,0))," 197 + " l.C_UOM_ID,uom.UOMSymbol," + " l.M_Product_ID,p.Name, l.C_InvoiceLine_ID,l.Line," + " l.C_OrderLine_ID "); if (Env.isBaseLanguage(Env.getCtx(), "C_UOM")) 201 { 202 sql.append("FROM C_UOM uom, C_InvoiceLine l, M_Product p, M_MatchInv mi "); 203 sql.append("WHERE l.C_UOM_ID=uom.C_UOM_ID"); 204 } 205 else 206 { 207 sql.append("FROM C_UOM_Trl uom, C_InvoiceLine_v l, M_Product p, M_MatchInv mi "); 208 sql.append("WHERE l.C_UOM_ID=uom.C_UOM_ID AND uom.AD_Language='").append(Env.getAD_Language(Env.getCtx())).append("'"); 209 } 210 sql.append(" AND l.M_Product_ID=p.M_Product_ID" 211 + " AND l.C_InvoiceLine_ID=mi.C_InvoiceLine_ID(+)" 212 + " AND l.C_Invoice_ID=? " + "GROUP BY l.QtyInvoiced,l.C_UOM_ID,uom.UOMSymbol,l.M_Product_ID,p.Name, l.C_InvoiceLine_ID,l.Line,l.C_OrderLine_ID " 214 + "ORDER BY l.Line"); 215 try 216 { 217 PreparedStatement pstmt = DB.prepareStatement(sql.toString()); 218 pstmt.setInt(1, C_Invoice_ID); 219 ResultSet rs = pstmt.executeQuery(); 220 while (rs.next()) 221 { 222 Vector line = new Vector(7); 223 line.add(new Boolean (false)); line.add(new Double (rs.getDouble(1))); KeyNamePair pp = new KeyNamePair(rs.getInt(2), rs.getString(3).trim()); 226 line.add(pp); pp = new KeyNamePair(rs.getInt(4), rs.getString(5)); 228 line.add(pp); int i = rs.getInt(8); 230 if (rs.wasNull()) 231 line.add(null); else 233 line.add(new KeyNamePair(i," ")); 234 line.add(null); pp = new KeyNamePair(rs.getInt(6), rs.getString(7)); 236 line.add(pp); data.add(line); 238 } 239 rs.close(); 240 pstmt.close(); 241 } 242 catch (SQLException e) 243 { 244 Log.error ("VCreateFromShipment.loadInvoice", e); 245 } 246 loadTableOIS (data); 247 } 249 250 253 protected void info() 254 { 255 TableModel model = dataTable.getModel(); 256 int rows = model.getRowCount(); 257 int count = 0; 258 for (int i = 0; i < rows; i++) 259 { 260 if (((Boolean )model.getValueAt(i, 0)).booleanValue()) 261 count++; 262 } 263 statusBar.setStatusLine(String.valueOf(count)); 264 } 266 267 271 protected boolean save() 272 { 273 Log.trace(Log.l3_Util, "VCreateFromShipment.save"); 274 276 Integer loc = (Integer )locatorField.getValue(); 277 if (loc == null || loc.intValue() == 0) 278 { 279 locatorField.setBackground(CompierePLAF.getFieldBackground_Error()); 280 return false; 281 } 282 int M_Locator_ID = loc.intValue(); 283 int AD_Client_ID = ((Integer )p_mTab.getValue("AD_Client_ID")).intValue(); 285 int AD_Org_ID = ((Integer )p_mTab.getValue("AD_Org_ID")).intValue(); 286 int CreatedBy = Env.getContextAsInt(Env.getCtx(), "#AD_User_ID"); 287 int M_InOut_ID = ((Integer )p_mTab.getValue("M_InOut_ID")).intValue(); 288 Log.trace( Log.l4_Data, "Client=" + AD_Client_ID + ", Org=" + AD_Org_ID 289 + ", User=" + CreatedBy + ", InOut=" + M_InOut_ID + ", Locator=" + M_Locator_ID); 290 291 300 301 TableModel model = dataTable.getModel(); 303 int rows = model.getRowCount(); 304 for (int i = 0; i < rows; i++) 305 { 306 if (((Boolean )model.getValueAt(i, 0)).booleanValue()) 307 { 308 int M_InOutLine_ID = DB.getKeyNextNo(Env.getCtx(), p_WindowNo, "M_InOutLine"); 310 Double d = (Double )model.getValueAt(i, 1); BigDecimal MovementQty = new BigDecimal(d.doubleValue()); 312 KeyNamePair pp = (KeyNamePair)model.getValueAt(i, 2); int C_UOM_ID = pp.getKey(); 314 pp = (KeyNamePair)model.getValueAt(i, 3); int M_Product_ID = pp.getKey(); 316 int C_OrderLine_ID = 0; 317 pp = (KeyNamePair)model.getValueAt(i, 4); if (pp != null) 319 C_OrderLine_ID = pp.getKey(); 320 int C_InvoiceLine_ID = 0; 321 pp = (KeyNamePair)model.getValueAt(i, 6); if (pp != null) 323 C_InvoiceLine_ID = pp.getKey(); 324 boolean isInvoiced = (C_InvoiceLine_ID != 0); 325 Log.trace(Log.l5_DData, "Line=" + M_InOutLine_ID + ", Qty=" + MovementQty 327 + ", Product=" + M_Product_ID + ", OrderLine=" + C_OrderLine_ID + ", InvoiceLine=" + C_InvoiceLine_ID); 328 String Description = null; 330 331 StringBuffer sql = new StringBuffer ("INSERT INTO M_InOutLine"); 332 sql.append("(M_InOutLine_ID,M_InOut_ID,"); 333 sql.append("AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"); 334 sql.append("Line,Description,"); 335 sql.append("M_Product_ID,C_UOM_ID,MovementQty,"); 336 sql.append("C_OrderLine_ID,M_Locator_ID,IsInvoiced)"); 337 sql.append(" VALUES ("); 338 sql.append(M_InOutLine_ID).append(",").append(M_InOut_ID).append(","); 340 sql.append(AD_Client_ID).append(",").append(AD_Org_ID).append(",'Y',"); 341 sql.append("SysDate,").append(CreatedBy).append(",SysDate,").append(CreatedBy).append(","); 342 sql.append("(SELECT (NVL(Max(Line),0))+10 FROM M_InOutLine WHERE M_InOut_ID=").append(M_InOut_ID).append("),"); 344 if (Description == null || Description.length() == 0) 346 sql.append("NULL,"); 347 else 348 sql.append("'").append(Description).append("',"); 349 sql.append(M_Product_ID).append(",").append(C_UOM_ID).append(",").append(MovementQty).append(","); 351 if (C_OrderLine_ID == 0) 353 sql.append("NULL,"); 354 else 355 sql.append(C_OrderLine_ID).append(","); 356 sql.append(M_Locator_ID).append(","); 357 sql.append(isInvoiced ? "'Y')" : "'N')"); 358 int no = DB.executeUpdate(sql.toString()); 360 if (no != 1) 361 Log.error("VCreateFromShipment.save - Line NOT created #" + no); 362 } } 365 370 if (p_C_Order_ID != 0) 371 { 372 String sql = "UPDATE M_InOut SET C_Order_ID=NULL" 373 + " WHERE M_InOut_ID=" + M_InOut_ID 374 + " AND C_Order_ID IS NOT NULL AND C_Order_ID <> " + p_C_Order_ID; 375 int no = DB.executeUpdate(sql); 376 if (no == 0) 377 { 378 sql = "UPDATE M_InOut SET C_Order_ID=" + p_C_Order_ID 379 + " WHERE M_InOut_ID=" + M_InOut_ID; 380 no = DB.executeUpdate(sql); 381 } 382 } 383 if (m_C_Invoice_ID != 0) 384 { 385 String sql = "UPDATE M_InOut SET C_Invoice_ID=NULL" 386 + " WHERE M_InOut_ID=" + M_InOut_ID 387 + " AND C_Invoice_ID IS NOT NULL AND C_Invoice_ID <> " + m_C_Invoice_ID; 388 int no = DB.executeUpdate(sql); 389 if (no == 0) 390 { 391 sql = "UPDATE M_InOut SET C_Invoice_ID=" + m_C_Invoice_ID 392 + " WHERE M_InOut_ID=" + M_InOut_ID; 393 no = DB.executeUpdate(sql); 394 } 395 } 396 return true; 397 } 399 } | Popular Tags |