KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > compiere > process > ImportProduct


1 /******************************************************************************
2  * The contents of this file are subject to the Compiere License Version 1.1
3  * ("License"); You may not use this file except in compliance with the License
4  * You may obtain a copy of the License at http://www.compiere.org/license.html
5  * Software distributed under the License is distributed on an "AS IS" basis,
6  * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
7  * the specific language governing rights and limitations under the License.
8  * The Original Code is Compiere ERP & CRM Business Solution
9  * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc.
10  * Portions created by Jorg Janke are Copyright (C) 1999-2003 Jorg Janke, parts
11  * created by ComPiere are Copyright (C) ComPiere, Inc.; All Rights Reserved.
12  * Contributor(s): ______________________________________.
13  *****************************************************************************/

14 package org.compiere.process;
15
16 import java.sql.*;
17 import java.math.*;
18
19 import org.compiere.util.*;
20
21 /**
22  * Import Products from I_Product
23  *
24  * @author Jorg Janke
25  * @version $Id: ImportProduct.java,v 1.7 2003/11/06 07:08:06 jjanke Exp $
26  */

27 public class ImportProduct extends SvrProcess
28 {
29     /**
30      * Import Product Constructor
31      */

32     public ImportProduct()
33     {
34         super();
35         Log.trace(Log.l1_User, "ImportProduct");
36     } // ImportProduct
37

38     /** Client to be imported to */
39     private int m_AD_Client_ID = 0;
40     /** Delete old Imported */
41     private boolean m_deleteOldImported = false;
42
43     /** Organization to be imported to */
44     private int m_AD_Org_ID = 0;
45     /** Effective */
46     private Timestamp m_DateValue = null;
47
48     /**
49      * Prepare - e.g., get Parameters.
50      */

51     protected void prepare()
52     {
53         ProcessInfoParameter[] para = getParameter();
54         for (int i = 0; i < para.length; i++)
55         {
56             String JavaDoc 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     } // prepare
67

68
69     /**
70      * Perrform process.
71      * @return Message
72      * @throws Exception
73      */

74     protected String JavaDoc doIt() throws java.lang.Exception JavaDoc
75     {
76         StringBuffer JavaDoc sql = null;
77         int no = 0;
78         String JavaDoc clientCheck = " AND AD_Client_ID=" + m_AD_Client_ID;
79
80         // **** Prepare ****
81

82         // Delete Old Imported
83
if (m_deleteOldImported)
84         {
85             sql = new StringBuffer JavaDoc ("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         // Set Client, Org, IaActive, Created/Updated, ProductType
92
sql = new StringBuffer JavaDoc ("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         // Set Optional BPartner
108
sql = new StringBuffer JavaDoc ("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         //
116
sql = new StringBuffer JavaDoc ("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         // **** Find Product
125
// EAN/UPC
126
sql = new StringBuffer JavaDoc ("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         // Value
135
sql = new StringBuffer JavaDoc ("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         // BP ProdNo
144
sql = new StringBuffer JavaDoc ("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         // Copy From Product if Import does not have value
155
String JavaDoc[] strFields = new String JavaDoc[] {"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 JavaDoc ("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         // if (no != 0)
168
Log.trace(Log.l5_DData, "ImportProduct.doIt", strFields[i] + " Default from existing Product=" + no);
169         }
170         String JavaDoc[] numFields = new String JavaDoc[] {"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 JavaDoc ("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         // if (no != 0)
182
Log.trace(Log.l5_DData, "ImportProduct.doIt", numFields[i] + " Default from existing Product=" + no);
183         }
184
185         // Copy From Product_PO if Import does not have value
186
String JavaDoc[] strFieldsPO = new String JavaDoc[] {"UPC",
187             "PriceEffective","VendorProductNo","VendorCategory","Manufacturer",
188             "Discontinued","DiscontinuedBy"};
189         for (int i = 0; i < strFieldsPO.length; i++)
190         {
191             sql = new StringBuffer JavaDoc ("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 JavaDoc[] numFieldsPO = new String JavaDoc[] {"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 JavaDoc ("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         // Set UOM (System/own)
219
sql = new StringBuffer JavaDoc ("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         //
229
sql = new StringBuffer JavaDoc ("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         //
236
sql = new StringBuffer JavaDoc ("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         // Set Product Category (own)
244
sql = new StringBuffer JavaDoc ("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         //
252
sql = new StringBuffer JavaDoc ("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         //
260
sql = new StringBuffer JavaDoc ("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         // Set Currency
268
sql = new StringBuffer JavaDoc ("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         //
278
sql = new StringBuffer JavaDoc ("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         //
286
sql = new StringBuffer JavaDoc ("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         // Verify ProductType
294
sql = new StringBuffer JavaDoc ("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         // Unique UPC/Value
302
sql = new StringBuffer JavaDoc ("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         //
309
sql = new StringBuffer JavaDoc ("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         // Mandatory Value
317
sql = new StringBuffer JavaDoc ("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         // Vendor Product No
325
// sql = new StringBuffer ("UPDATE I_Product i "
326
// + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Mandatory VendorProductNo,' "
327
// + "WHERE I_IsImported<>'Y'"
328
// + " AND VendorProductNo IS NULL AND (C_BPartner_ID IS NOT NULL OR BPartner_Value IS NOT NULL)").append(clientCheck);
329
// no = DB.executeUpdate(sql.toString());
330
// Log.trace(Log.l3_Util, "ImportProduct.doIt", "No Mandatory VendorProductNo=" + no);
331
sql = new StringBuffer JavaDoc ("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         //
338
sql = new StringBuffer JavaDoc ("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         // Get Default Tax Category
349
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 JavaDoc ("ImportProduct.doIt - TaxCategory", e);
363         }
364         Log.trace(Log.l6_Database, "ImportProduct.doIt", "C_TaxCategory_ID=" + C_TaxCategory_ID);
365
366
367         // -------------------------------------------------------------------
368
int noInsert = 0;
369         int noUpdate = 0;
370         int noInsertPO = 0;
371         int noUpdatePO = 0;
372
373         // Go through Records
374
sql = new StringBuffer JavaDoc ("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             // Insert Product from Import
380
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             // Update Product from Import
395
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             // Update Product_PO from Import
409
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             // Insert Product from Import
426
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             // Set Imported = Y
445
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             //
450
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                 // Product
461
if (newProduct) // Insert new Product
462
{
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 JavaDoc ("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 // Update Product
483
{
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 JavaDoc ("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                 // Do we have PO Info
504
if (C_BPartner_ID != 0)
505                 {
506                     no = 0;
507                     // If Product existed, Try to Update first
508
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 JavaDoc ("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) // Insert PO
532
{
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--; // assume that product also did not exist
546
conn.rollback();
547                             sql = new StringBuffer JavaDoc ("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                 } // C_BPartner_ID != 0
555

556                 // Update I_Product
557
pstmt_setImported.setInt(1, M_Product_ID);
558                 pstmt_setImported.setInt(2, I_Product_ID);
559                 no = pstmt_setImported.executeUpdate();
560                 //
561
conn.commit();
562             } // for all I_Product
563
rs.close();
564             pstmt.close();
565
566             //
567
pstmt_insertProduct.close();
568             pstmt_updateProduct.close();
569             pstmt_insertProductPO.close();
570             pstmt_updateProductPO.close();
571             pstmt_setImported.close();
572             //
573
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 JavaDoc ("ImportProduct.doIt", e);
588         }
589         finally
590         {
591             if (conn != null)
592                 conn.close();
593             conn = null;
594         }
595
596         // Set Error to indicator to not imported
597
sql = new StringBuffer JavaDoc ("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     } // doIt
608

609 } // ImportProduct
610
Popular Tags