1 14 package org.compiere.process; 15 16 import java.sql.*; 17 import java.math.*; 18 19 import org.compiere.util.*; 20 21 27 public class ImportProduct extends SvrProcess 28 { 29 32 public ImportProduct() 33 { 34 super(); 35 Log.trace(Log.l1_User, "ImportProduct"); 36 } 38 39 private int m_AD_Client_ID = 0; 40 41 private boolean m_deleteOldImported = false; 42 43 44 private int m_AD_Org_ID = 0; 45 46 private Timestamp m_DateValue = null; 47 48 51 protected void prepare() 52 { 53 ProcessInfoParameter[] para = getParameter(); 54 for (int i = 0; i < para.length; i++) 55 { 56 String name = para[i].getParameterName(); 57 if (name.equals("AD_Client_ID")) 58 m_AD_Client_ID = ((BigDecimal)para[i].getParameter()).intValue(); 59 else if (name.equals("DeleteOldImported")) 60 m_deleteOldImported = "Y".equals(para[i].getParameter()); 61 else 62 Log.error("ImportProduct.prepare - Unknown Parameter: " + name); 63 } 64 if (m_DateValue == null) 65 m_DateValue = new Timestamp (System.currentTimeMillis()); 66 } 68 69 74 protected String doIt() throws java.lang.Exception 75 { 76 StringBuffer sql = null; 77 int no = 0; 78 String clientCheck = " AND AD_Client_ID=" + m_AD_Client_ID; 79 80 82 if (m_deleteOldImported) 84 { 85 sql = new StringBuffer ("DELETE I_Product " 86 + "WHERE I_IsImported='Y'").append(clientCheck); 87 no = DB.executeUpdate(sql.toString()); 88 Log.trace(Log.l5_DData, "ImportProduct.doIt", "Delete Old Impored =" + no); 89 } 90 91 sql = new StringBuffer ("UPDATE I_Product " 93 + "SET AD_Client_ID = COALESCE (AD_Client_ID, ").append(m_AD_Client_ID).append(")," 94 + " AD_Org_ID = COALESCE (AD_Org_ID, 0)," 95 + " IsActive = COALESCE (IsActive, 'Y')," 96 + " Created = COALESCE (Created, SysDate)," 97 + " CreatedBy = COALESCE (CreatedBy, 0)," 98 + " Updated = COALESCE (Updated, SysDate)," 99 + " UpdatedBy = COALESCE (UpdatedBy, 0)," 100 + " ProductType = COALESCE (ProductType, 'I')," 101 + " I_ErrorMsg = NULL," 102 + " I_IsImported = 'N' " 103 + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL"); 104 no = DB.executeUpdate(sql.toString()); 105 Log.trace(Log.l5_DData, "ImportProduct.doIt", "Reset=" + no); 106 107 sql = new StringBuffer ("UPDATE I_Product i " 109 + "SET C_BPartner_ID=(SELECT C_BPartner_ID FROM C_BPartner p" 110 + " WHERE i.BPartner_Value=p.Value AND i.AD_Client_ID=p.AD_Client_ID) " 111 + "WHERE C_BPartner_ID IS NULL" 112 + " AND I_IsImported<>'Y'").append(clientCheck); 113 no = DB.executeUpdate(sql.toString()); 114 Log.trace(Log.l5_DData, "ImportProduct.doIt", "BPartner=" + no); 115 sql = new StringBuffer ("UPDATE I_Product " 117 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid BPartner,' " 118 + "WHERE C_BPartner_ID IS NULL" 119 + " AND I_IsImported<>'Y'").append(clientCheck); 120 no = DB.executeUpdate(sql.toString()); 121 Log.trace(Log.l3_Util, "ImportProduct.doIt", "Invalid BPartner=" + no); 122 123 124 sql = new StringBuffer ("UPDATE I_Product i " 127 + "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p" 128 + " WHERE i.UPC=p.UPC AND i.AD_Client_ID=p.AD_Client_ID) " 129 + "WHERE M_Product_ID IS NULL" 130 + " AND I_IsImported='N'").append(clientCheck); 131 no = DB.executeUpdate(sql.toString()); 132 Log.trace(Log.l5_DData, "ImportProduct.doIt", "Product Existing UPC=" + no); 133 134 sql = new StringBuffer ("UPDATE I_Product i " 136 + "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p" 137 + " WHERE i.Value=p.Value AND i.AD_Client_ID=p.AD_Client_ID) " 138 + "WHERE M_Product_ID IS NULL" 139 + " AND I_IsImported='N'").append(clientCheck); 140 no = DB.executeUpdate(sql.toString()); 141 Log.trace(Log.l5_DData, "ImportProduct.doIt", "Product Existing Value=" + no); 142 143 sql = new StringBuffer ("UPDATE I_Product i " 145 + "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product_po p" 146 + " WHERE i.C_BPartner_ID=p.C_BPartner_ID" 147 + " AND i.VendorProductNo=p.VendorProductNo AND i.AD_Client_ID=p.AD_Client_ID) " 148 + "WHERE M_Product_ID IS NULL" 149 + " AND I_IsImported='N'").append(clientCheck); 150 no = DB.executeUpdate(sql.toString()); 151 Log.trace(Log.l5_DData, "ImportProduct.doIt", "Product Existing Vendor ProductNo=" + no); 152 153 154 String [] strFields = new String [] {"Value","Name","Description","DocumentNote","Help", 156 "UPC","SKU","Classification","ProductType", 157 "Discontinued","DiscontinuedBy","ImageURL","DescriptionURL"}; 158 for (int i = 0; i < strFields.length; i++) 159 { 160 sql = new StringBuffer ("UPDATE I_PRODUCT i " 161 + "SET ").append(strFields[i]).append(" = (SELECT ").append(strFields[i]).append(" FROM M_Product p" 162 + " WHERE i.M_Product_ID=p.M_Product_ID AND i.AD_Client_ID=p.AD_Client_ID) " 163 + "WHERE M_Product_ID IS NOT NULL" 164 + " AND ").append(strFields[i]).append(" IS NULL" 165 + " AND I_IsImported='N'").append(clientCheck); 166 no = DB.executeUpdate(sql.toString()); 167 Log.trace(Log.l5_DData, "ImportProduct.doIt", strFields[i] + " Default from existing Product=" + no); 169 } 170 String [] numFields = new String [] {"C_UOM_ID","M_Product_Category_ID", 171 "Volume","Weight","ShelfWidth","ShelfHeight","ShelfDepth","UnitsPerPallet"}; 172 for (int i = 0; i < numFields.length; i++) 173 { 174 sql = new StringBuffer ("UPDATE I_PRODUCT i " 175 + "SET ").append(numFields[i]).append(" = (SELECT ").append(numFields[i]).append(" FROM M_Product p" 176 + " WHERE i.M_Product_ID=p.M_Product_ID AND i.AD_Client_ID=p.AD_Client_ID) " 177 + "WHERE M_Product_ID IS NOT NULL" 178 + " AND (").append(numFields[i]).append(" IS NULL OR ").append(numFields[i]).append("=0)" 179 + " AND I_IsImported='N'").append(clientCheck); 180 no = DB.executeUpdate(sql.toString()); 181 Log.trace(Log.l5_DData, "ImportProduct.doIt", numFields[i] + " Default from existing Product=" + no); 183 } 184 185 String [] strFieldsPO = new String [] {"UPC", 187 "PriceEffective","VendorProductNo","VendorCategory","Manufacturer", 188 "Discontinued","DiscontinuedBy"}; 189 for (int i = 0; i < strFieldsPO.length; i++) 190 { 191 sql = new StringBuffer ("UPDATE I_PRODUCT i " 192 + "SET ").append(strFieldsPO[i]).append(" = (SELECT ").append(strFieldsPO[i]).append(" FROM M_Product_PO p" 193 + " WHERE i.M_Product_ID=p.M_Product_ID AND i.C_BPartner_ID=p.C_BPartner_ID AND i.AD_Client_ID=p.AD_Client_ID) " 194 + "WHERE M_Product_ID IS NOT NULL AND C_BPartner_ID IS NOT NULL" 195 + " AND ").append(strFieldsPO[i]).append(" IS NULL" 196 + " AND I_IsImported='N'").append(clientCheck); 197 no = DB.executeUpdate(sql.toString()); 198 if (no != 0) 199 Log.trace(Log.l5_DData, "ImportProduct.doIt", strFieldsPO[i] + " Default from existing Product=" + no); 200 } 201 String [] numFieldsPO = new String [] {"C_UOM_ID","C_Currency_ID", 202 "PriceList","PricePO","RoyaltyAmt", 203 "Order_Min","Order_Pack","CostPerOrder","DeliveryTime_Promised"}; 204 for (int i = 0; i < numFieldsPO.length; i++) 205 { 206 sql = new StringBuffer ("UPDATE I_PRODUCT i " 207 + "SET ").append(numFieldsPO[i]).append(" = (SELECT ").append(numFieldsPO[i]).append(" FROM M_Product_PO p" 208 + " WHERE i.M_Product_ID=p.M_Product_ID AND i.C_BPartner_ID=p.C_BPartner_ID AND i.AD_Client_ID=p.AD_Client_ID) " 209 + "WHERE M_Product_ID IS NOT NULL AND C_BPartner_ID IS NOT NULL" 210 + " AND (").append(numFieldsPO[i]).append(" IS NULL OR ").append(numFieldsPO[i]).append("=0)" 211 + " AND I_IsImported='N'").append(clientCheck); 212 no = DB.executeUpdate(sql.toString()); 213 if (no != 0) 214 Log.trace(Log.l5_DData, "ImportProduct.doIt", numFieldsPO[i] + " Default from existing Product=" + no); 215 } 216 217 218 sql = new StringBuffer ("UPDATE I_Product i " 220 + "SET X12DE355 = COALESCE ((" 221 + "SELECT X12DE355 FROM C_UOM u WHERE u.IsDefault='Y' AND u.AD_Client_ID IN (0,i.AD_Client_ID) AND ROWNUM=1" 222 + "), 'EA') " 223 + "WHERE X12DE355 IS NULL AND C_UOM_ID IS NULL" 224 + " AND I_IsImported<>'Y'" 225 + " AND C_UOM_ID IS NULL").append(clientCheck); 226 no = DB.executeUpdate(sql.toString()); 227 Log.trace(Log.l5_DData, "ImportProduct.doIt", "Set UOM Default=" + no); 228 sql = new StringBuffer ("UPDATE I_Product i " 230 + "SET C_UOM_ID = (SELECT C_UOM_ID FROM C_UOM u WHERE u.X12DE355=i.X12DE355 AND u.AD_Client_ID IN (0,i.AD_Client_ID)) " 231 + "WHERE C_UOM_ID IS NULL" 232 + " AND I_IsImported<>'Y'").append(clientCheck); 233 no = DB.executeUpdate(sql.toString()); 234 Log.trace(Log.l5_DData, "ImportProduct.doIt", "Set UOM=" + no); 235 sql = new StringBuffer ("UPDATE I_Product " 237 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid UOM, ' " 238 + "WHERE C_UOM_ID IS NULL" 239 + " AND I_IsImported<>'Y'").append(clientCheck); 240 no = DB.executeUpdate(sql.toString()); 241 Log.trace(Log.l3_Util, "ImportProduct.doIt", "Invalid UOM=" + no); 242 243 sql = new StringBuffer ("UPDATE I_Product " 245 + "SET ProductCategory_Value=(SELECT Value FROM M_Product_Category" 246 + " WHERE IsDefault='Y' AND AD_Client_ID=").append(m_AD_Client_ID).append(" AND ROWNUM=1) " 247 + "WHERE ProductCategory_Value IS NULL AND M_Product_Category_ID IS NULL" 248 + " AND I_IsImported<>'Y'").append(clientCheck); 249 no = DB.executeUpdate(sql.toString()); 250 Log.trace(Log.l5_DData, "ImportProduct.doIt", "Set Category Default=" + no); 251 sql = new StringBuffer ("UPDATE I_Product i " 253 + "SET M_Product_Category_ID=(SELECT M_Product_Category_ID FROM M_Product_Category c" 254 + " WHERE i.ProductCategory_Value=c.Value AND i.AD_Client_ID=c.AD_Client_ID) " 255 + "WHERE M_Product_Category_ID IS NULL" 256 + " AND I_IsImported<>'Y'").append(clientCheck); 257 no = DB.executeUpdate(sql.toString()); 258 Log.trace(Log.l5_DData, "ImportProduct.doIt", "Set Category=" + no); 259 sql = new StringBuffer ("UPDATE I_Product " 261 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid ProdCategorty,' " 262 + "WHERE M_Product_Category_ID IS NULL" 263 + " AND I_IsImported<>'Y'").append(clientCheck); 264 no = DB.executeUpdate(sql.toString()); 265 Log.trace(Log.l3_Util, "ImportProduct.doIt", "Invalid Category=" + no); 266 267 sql = new StringBuffer ("UPDATE I_Product i " 269 + "SET ISO_Code=(SELECT ISO_Code FROM C_Currency c" 270 + " INNER JOIN C_AcctSchema a ON (a.C_Currency_ID=c.C_Currency_ID)" 271 + " INNER JOIN AD_ClientInfo ci ON (a.C_AcctSchema_ID=ci.C_AcctSchema1_ID)" 272 + " WHERE ci.AD_Client_ID=i.AD_Client_ID) " 273 + "WHERE C_Currency_ID IS NULL AND ISO_Code IS NULL" 274 + " AND I_IsImported<>'Y'").append(clientCheck); 275 no = DB.executeUpdate(sql.toString()); 276 Log.trace(Log.l5_DData, "ImportProduct.doIt", "Set Currency Default=" + no); 277 sql = new StringBuffer ("UPDATE I_Product i " 279 + "SET C_Currency_ID=(SELECT C_Currency_ID FROM C_Currency c" 280 + " WHERE i.ISO_Code=c.ISO_Code AND c.AD_Client_ID IN (0,i.AD_Client_ID)) " 281 + "WHERE C_Currency_ID IS NULL" 282 + " AND I_IsImported<>'Y'").append(clientCheck); 283 no = DB.executeUpdate(sql.toString()); 284 Log.trace(Log.l5_DData, "ImportProduct.doIt", "Set Currency=" + no); 285 sql = new StringBuffer ("UPDATE I_Product " 287 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Currency,' " 288 + "WHERE C_Currency_ID IS NULL" 289 + " AND I_IsImported<>'Y'").append(clientCheck); 290 no = DB.executeUpdate(sql.toString()); 291 Log.trace(Log.l3_Util, "ImportProduct.doIt", "Invalid Currency=" + no); 292 293 sql = new StringBuffer ("UPDATE I_Product " 295 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid ProductType,' " 296 + "WHERE ProductType NOT IN ('I','S')" 297 + " AND I_IsImported<>'Y'").append(clientCheck); 298 no = DB.executeUpdate(sql.toString()); 299 Log.trace(Log.l3_Util, "ImportProduct.doIt", "Invalid ProductType=" + no); 300 301 sql = new StringBuffer ("UPDATE I_Product i " 303 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Value not unique,' " 304 + "WHERE I_IsImported<>'Y'" 305 + " AND Value IN (SELECT Value FROM I_Product ii WHERE i.AD_Client_ID=ii.AD_Client_ID GROUP BY Value HAVING COUNT(*) > 1)").append(clientCheck); 306 no = DB.executeUpdate(sql.toString()); 307 Log.trace(Log.l3_Util, "ImportProduct.doIt", "Not Unique Value=" + no); 308 sql = new StringBuffer ("UPDATE I_Product i " 310 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=UPC not unique,' " 311 + "WHERE I_IsImported<>'Y'" 312 + " AND UPC IN (SELECT UPC FROM I_Product ii WHERE i.AD_Client_ID=ii.AD_Client_ID GROUP BY UPC HAVING COUNT(*) > 1)").append(clientCheck); 313 no = DB.executeUpdate(sql.toString()); 314 Log.trace(Log.l3_Util, "ImportProduct.doIt", "Not Unique UPC=" + no); 315 316 sql = new StringBuffer ("UPDATE I_Product i " 318 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Mandatory Value,' " 319 + "WHERE Value IS NULL" 320 + " AND I_IsImported<>'Y'").append(clientCheck); 321 no = DB.executeUpdate(sql.toString()); 322 Log.trace(Log.l3_Util, "ImportProduct.doIt", "No Mandatory Value=" + no); 323 324 sql = new StringBuffer ("UPDATE I_Product " 332 + "SET VendorProductNo=Value " 333 + "WHERE C_BPartner_ID IS NOT NULL AND VendorProductNo IS NULL" 334 + " AND I_IsImported='N'").append(clientCheck); 335 no = DB.executeUpdate(sql.toString()); 336 Log.trace(Log.l5_DData, "ImportProduct.doIt", "VendorProductNo Set to Value=" + no); 337 sql = new StringBuffer ("UPDATE I_Product i " 339 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=VendorProductNo not unique,' " 340 + "WHERE I_IsImported<>'Y'" 341 + " AND C_BPartner_ID IS NOT NULL" 342 + " AND (C_BPartner_ID, VendorProductNo) IN " 343 + " (SELECT C_BPartner_ID, VendorProductNo FROM I_Product ii WHERE i.AD_Client_ID=ii.AD_Client_ID GROUP BY C_BPartner_ID, VendorProductNo HAVING COUNT(*) > 1)") 344 .append(clientCheck); 345 no = DB.executeUpdate(sql.toString()); 346 Log.trace(Log.l3_Util, "ImportProduct.doIt", "Not Unique VendorProductNo=" + no); 347 348 int C_TaxCategory_ID = 0; 350 try 351 { 352 PreparedStatement pstmt = DB.prepareStatement 353 ("SELECT C_TaxCategory_ID FROM C_TaxCategory WHERE IsDefault='Y'" + clientCheck); 354 ResultSet rs = pstmt.executeQuery(); 355 if (rs.next()) 356 C_TaxCategory_ID = rs.getInt(1); 357 rs.close(); 358 pstmt.close(); 359 } 360 catch (SQLException e) 361 { 362 throw new Exception ("ImportProduct.doIt - TaxCategory", e); 363 } 364 Log.trace(Log.l6_Database, "ImportProduct.doIt", "C_TaxCategory_ID=" + C_TaxCategory_ID); 365 366 367 int noInsert = 0; 369 int noUpdate = 0; 370 int noInsertPO = 0; 371 int noUpdatePO = 0; 372 373 sql = new StringBuffer ("SELECT I_Product_ID, M_Product_ID, C_BPartner_ID " 375 + "FROM I_Product WHERE I_IsImported='N'").append(clientCheck); 376 Connection conn = DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED); 377 try 378 { 379 PreparedStatement pstmt_insertProduct = conn.prepareStatement 381 ("INSERT INTO M_Product (M_Product_ID," 382 + "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy," 383 + "Value,Name,Description,DocumentNote,Help," 384 + "UPC,SKU,C_UOM_ID,IsSummary,M_Product_Category_ID,C_TaxCategory_ID," 385 + "ProductType,ImageURL,DescriptionURL) " 386 + "SELECT ?," 387 + "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy," 388 + "Value,Name,Description,DocumentNote,Help," 389 + "UPC,SKU,C_UOM_ID,'N',M_Product_Category_ID," + C_TaxCategory_ID + "," 390 + "ProductType,ImageURL,DescriptionURL " 391 + "FROM I_Product " 392 + "WHERE I_Product_ID=?"); 393 394 PreparedStatement pstmt_updateProduct = conn.prepareStatement 396 ("UPDATE M_PRODUCT " 397 + "SET (Value,Name,Description,DocumentNote,Help," 398 + "UPC,SKU,C_UOM_ID,M_Product_Category_ID,Classification,ProductType," 399 + "Volume,Weight,ShelfWidth,ShelfHeight,ShelfDepth,UnitsPerPallet," 400 + "Discontinued,DiscontinuedBy,Updated,UpdatedBy)= " 401 + "(SELECT Value,Name,Description,DocumentNote,Help," 402 + "UPC,SKU,C_UOM_ID,M_Product_Category_ID,Classification,ProductType," 403 + "Volume,Weight,ShelfWidth,ShelfHeight,ShelfDepth,UnitsPerPallet," 404 + "Discontinued,DiscontinuedBy,SysDate,UpdatedBy" 405 + " FROM I_Product WHERE I_Product_ID=?) " 406 + "WHERE M_Product_ID=?"); 407 408 PreparedStatement pstmt_updateProductPO = conn.prepareStatement 410 ("UPDATE M_Product_PO " 411 + "SET (IsCurrentVendor,C_UOM_ID,C_Currency_ID,UPC," 412 + "PriceList,PricePO,RoyaltyAmt,PriceEffective," 413 + "VendorProductNo,VendorCategory,Manufacturer," 414 + "Discontinued,DiscontinuedBy,Order_Min,Order_Pack," 415 + "CostPerOrder,DeliveryTime_Promised,Updated,UpdatedBy)= " 416 + "(SELECT 'Y',C_UOM_ID,C_Currency_ID,UPC," 417 + "PriceList,PricePO,RoyaltyAmt,PriceEffective," 418 + "VendorProductNo,VendorCategory,Manufacturer," 419 + "Discontinued,DiscontinuedBy,Order_Min,Order_Pack," 420 + "CostPerOrder,DeliveryTime_Promised,SysDate,UpdatedBy" 421 + " FROM I_Product" 422 + " WHERE I_Product_ID=?) " 423 + "WHERE M_Product_ID=? AND C_BPartner_ID=?"); 424 425 PreparedStatement pstmt_insertProductPO = conn.prepareStatement 427 ("INSERT INTO M_Product_PO (M_Product_ID,C_BPartner_ID, " 428 + "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy," 429 + "IsCurrentVendor,C_UOM_ID,C_Currency_ID,UPC," 430 + "PriceList,PricePO,RoyaltyAmt,PriceEffective," 431 + "VendorProductNo,VendorCategory,Manufacturer," 432 + "Discontinued,DiscontinuedBy,Order_Min,Order_Pack," 433 + "CostPerOrder,DeliveryTime_Promised) " 434 + "SELECT ?,?, " 435 + "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy," 436 + "'Y',C_UOM_ID,C_Currency_ID,UPC," 437 + "PriceList,PricePO,RoyaltyAmt,PriceEffective," 438 + "VendorProductNo,VendorCategory,Manufacturer," 439 + "Discontinued,DiscontinuedBy,Order_Min,Order_Pack," 440 + "CostPerOrder,DeliveryTime_Promised " 441 + "FROM I_Product " 442 + "WHERE I_Product_ID=?"); 443 444 PreparedStatement pstmt_setImported = conn.prepareStatement 446 ("UPDATE I_Product SET I_IsImported='Y', M_Product_ID=?, " 447 + "Updated=SysDate, Processed='Y' WHERE I_Product_ID=?"); 448 449 PreparedStatement pstmt = DB.prepareStatement(sql.toString()); 451 ResultSet rs = pstmt.executeQuery(); 452 while (rs.next()) 453 { 454 int I_Product_ID = rs.getInt(1); 455 int M_Product_ID = rs.getInt(2); 456 int C_BPartner_ID = rs.getInt(3); 457 boolean newProduct = M_Product_ID == 0; 458 Log.trace(Log.l6_Database, "I_Product_ID=" + I_Product_ID + ", M_Product_ID=" + M_Product_ID + ", C_BPartner_ID=" + C_BPartner_ID); 459 460 if (newProduct) { 463 M_Product_ID = DB.getKeyNextNo(m_AD_Client_ID, "M_Product"); 464 pstmt_insertProduct.setInt(1, M_Product_ID); 465 pstmt_insertProduct.setInt(2, I_Product_ID); 466 try 467 { 468 no = pstmt_insertProduct.executeUpdate(); 469 Log.trace(10, "Insert Product = " + no); 470 noInsert++; 471 } 472 catch (SQLException ex) 473 { 474 Log.trace(10, "Insert Product - " + ex.toString()); 475 sql = new StringBuffer ("UPDATE I_Product i " 476 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert Product: " + ex.toString())) 477 .append("WHERE I_Product_ID=").append(I_Product_ID); 478 DB.executeUpdate(sql.toString()); 479 continue; 480 } 481 } 482 else { 484 pstmt_updateProduct.setInt(1, I_Product_ID); 485 pstmt_updateProduct.setInt(2, M_Product_ID); 486 try 487 { 488 no = pstmt_updateProduct.executeUpdate(); 489 Log.trace(10, "Update Product = " + no); 490 noUpdate++; 491 } 492 catch (SQLException ex) 493 { 494 Log.trace(10, "Update Product - " + ex.toString()); 495 sql = new StringBuffer ("UPDATE I_Product i " 496 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Update Product: " + ex.toString())) 497 .append("WHERE I_Product_ID=").append(I_Product_ID); 498 DB.executeUpdate(sql.toString()); 499 continue; 500 } 501 } 502 503 if (C_BPartner_ID != 0) 505 { 506 no = 0; 507 if (!newProduct) 509 { 510 pstmt_updateProductPO.setInt(1, I_Product_ID); 511 pstmt_updateProductPO.setInt(2, M_Product_ID); 512 pstmt_updateProductPO.setInt(3, C_BPartner_ID); 513 try 514 { 515 no = pstmt_updateProductPO.executeUpdate(); 516 Log.trace(10, "Update Product_PO = " + no); 517 noUpdatePO++; 518 } 519 catch (SQLException ex) 520 { 521 Log.trace(10, "Update Product_PO - " + ex.toString()); 522 noUpdate--; 523 conn.rollback(); 524 sql = new StringBuffer ("UPDATE I_Product i " 525 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Update Product_PO: " + ex.toString())) 526 .append("WHERE I_Product_ID=").append(I_Product_ID); 527 DB.executeUpdate(sql.toString()); 528 continue; 529 } 530 } 531 if (no == 0) { 533 pstmt_insertProductPO.setInt(1, M_Product_ID); 534 pstmt_insertProductPO.setInt(2, C_BPartner_ID); 535 pstmt_insertProductPO.setInt(3, I_Product_ID); 536 try 537 { 538 no = pstmt_insertProductPO.executeUpdate(); 539 Log.trace(10, "Insert Product_PO = " + no); 540 noInsertPO++; 541 } 542 catch (SQLException ex) 543 { 544 Log.trace(10, "Insert Product_PO - " + ex.toString()); 545 noInsert--; conn.rollback(); 547 sql = new StringBuffer ("UPDATE I_Product i " 548 + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert Product_PO: " + ex.toString())) 549 .append("WHERE I_Product_ID=").append(I_Product_ID); 550 DB.executeUpdate(sql.toString()); 551 continue; 552 } 553 } 554 } 556 pstmt_setImported.setInt(1, M_Product_ID); 558 pstmt_setImported.setInt(2, I_Product_ID); 559 no = pstmt_setImported.executeUpdate(); 560 conn.commit(); 562 } rs.close(); 564 pstmt.close(); 565 566 pstmt_insertProduct.close(); 568 pstmt_updateProduct.close(); 569 pstmt_insertProductPO.close(); 570 pstmt_updateProductPO.close(); 571 pstmt_setImported.close(); 572 conn.close(); 574 conn = null; 575 } 576 catch (SQLException e) 577 { 578 try 579 { 580 if (conn != null) 581 conn.close(); 582 conn = null; 583 } 584 catch (SQLException ex) 585 { 586 } 587 throw new Exception ("ImportProduct.doIt", e); 588 } 589 finally 590 { 591 if (conn != null) 592 conn.close(); 593 conn = null; 594 } 595 596 sql = new StringBuffer ("UPDATE I_Product " 598 + "SET I_IsImported='N', Updated=SysDate " 599 + "WHERE I_IsImported<>'Y'").append(clientCheck); 600 no = DB.executeUpdate(sql.toString()); 601 addLog (0, null, new BigDecimal (no), "@Errors@"); 602 addLog (0, null, new BigDecimal (noInsert), "@M_Product_ID@: @Inserted@"); 603 addLog (0, null, new BigDecimal (noUpdate), "@M_Product_ID@: @Updated@"); 604 addLog (0, null, new BigDecimal (noInsertPO), "@M_Product_ID@ @Purchase@: @Inserted@"); 605 addLog (0, null, new BigDecimal (noUpdatePO), "@M_Product_ID@ @Purchase@: @Updated@"); 606 return ""; 607 } 609 } | Popular Tags |