ImportBPartner


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 BPartners from I_BPartner
23   *
24   *  @author     Jorg Janke
25   *  @version    $Id: ImportBPartner.java,v 1.10 2003/11/06 07:08:06 jjanke Exp $
26   */
27  public class ImportBPartner extends SvrProcess
28  {
29      /**
30       *  Import BPartner Constructor
31       */
32      public ImportBPartner()
33      {
34          super();
35          Log.trace(Log.l1_User, "ImportBPartner");
36      }   //  ImportBPartner
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   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("ImportBPartner.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   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          //  ****    Prepare ****
81  
82          //  Delete Old Imported
83          if (m_deleteOldImported)
84          {
85              sql = new StringBuffer   ("DELETE I_BPartner "
86                  + "WHERE I_IsImported='Y'").append(clientCheck);
87              no = DB.executeUpdate(sql.toString());
88              Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Delete Old Impored =" + no);
89          }
90  
91          //  Set Client, Org, IsActive, Created/Updated
92          sql = new StringBuffer   ("UPDATE I_BPartner "
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             + " I_ErrorMsg = NULL,"
101             + " I_IsImported = 'N' "
102             + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
103         no = DB.executeUpdate(sql.toString());
104         Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Reset=" + no);
105 
106         //  Set BP_Group
107         sql = new StringBuffer   ("UPDATE I_BPartner i "
108             + "SET GroupValue=(SELECT Value FROM C_BP_Group g WHERE g.IsDefault='Y'"
109             + " AND g.AD_Client_ID=i.AD_Client_ID AND ROWNUM=1) "
110             + "WHERE GroupValue IS NULL AND C_BP_Group_ID IS NULL"
111             + " AND I_IsImported<>'Y'").append(clientCheck);
112         no = DB.executeUpdate(sql.toString());
113         Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Set Group Default=" + no);
114         //
115         sql = new StringBuffer   ("UPDATE I_BPartner i "
116             + "SET C_BP_Group_ID=(SELECT C_BP_Group_ID FROM C_BP_Group g"
117             + " WHERE i.GroupValue=g.Value AND g.AD_Client_ID=i.AD_Client_ID) "
118             + "WHERE C_BP_Group_ID IS NULL"
119             + " AND I_IsImported<>'Y'").append(clientCheck);
120         no = DB.executeUpdate(sql.toString());
121         Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Set Group=" + no);
122         //
123         sql = new StringBuffer   ("UPDATE I_BPartner "
124             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Group, ' "
125             + "WHERE C_BP_Group_ID IS NULL"
126             + " AND I_IsImported<>'Y'").append(clientCheck);
127         no = DB.executeUpdate(sql.toString());
128         Log.trace(Log.l3_Util, "ImportBPartner.doIt", "Invalid Group=" + no);
129 
130         //  Set Country
131         sql = new StringBuffer   ("UPDATE I_BPartner i "
132             + "SET CountryCode=(SELECT CountryCode FROM C_Country c WHERE c.IsDefault='Y'"
133             + " AND c.AD_Client_ID IN (0, i.AD_Client_ID) AND ROWNUM=1) "
134             + "WHERE CountryCode IS NULL AND C_Country_ID IS NULL"
135             + " AND I_IsImported<>'Y'").append(clientCheck);
136         no = DB.executeUpdate(sql.toString());
137         Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Set Country Default=" + no);
138         //
139         sql = new StringBuffer   ("UPDATE I_BPartner i "
140             + "SET C_Country_ID=(SELECT C_Country_ID FROM C_Country c"
141             + " WHERE i.CountryCode=c.CountryCode AND c.AD_Client_ID IN (0, i.AD_Client_ID)) "
142             + "WHERE C_Country_ID IS NULL"
143             + " AND I_IsImported<>'Y'").append(clientCheck);
144         no = DB.executeUpdate(sql.toString());
145         Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Set Country=" + no);
146         //
147         sql = new StringBuffer   ("UPDATE I_BPartner "
148             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Country, ' "
149             + "WHERE C_Country_ID IS NULL"
150             + " AND I_IsImported<>'Y'").append(clientCheck);
151         no = DB.executeUpdate(sql.toString());
152         Log.trace(Log.l3_Util, "ImportBPartner.doIt", "Invalid Country=" + no);
153 
154         //  Set Region
155         sql = new StringBuffer   ("UPDATE I_BPartner i "
156             + "Set RegionName=(SELECT Name FROM C_Region r"
157             + " WHERE r.IsDefault='Y' AND r.C_Country_ID=i.C_Country_ID"
158             + " AND r.AD_Client_ID IN (0, i.AD_Client_ID) AND ROWNUM=1) "
159             + "WHERE RegionName IS NULL AND C_Region_ID IS NULL"
160             + " AND I_IsImported<>'Y'").append(clientCheck);
161         no = DB.executeUpdate(sql.toString());
162         Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Set Region Default=" + no);
163         //
164         sql = new StringBuffer   ("UPDATE I_BPartner i "
165             + "Set C_Region_ID=(SELECT C_Region_ID FROM C_Region r"
166             + " WHERE r.Name=i.RegionName AND r.C_Country_ID=i.C_Country_ID"
167             + " AND r.AD_Client_ID IN (0, i.AD_Client_ID)) "
168             + "WHERE C_Region_ID IS NULL"
169             + " AND I_IsImported<>'Y'").append(clientCheck);
170         no = DB.executeUpdate(sql.toString());
171         Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Set Region=" + no);
172         //
173         sql = new StringBuffer   ("UPDATE I_BPartner i "
174             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Region, ' "
175             + "WHERE C_Region_ID IS NULL "
176             + " AND EXISTS (SELECT * FROM C_Country c"
177             + " WHERE c.C_Country_ID=i.C_Country_ID AND c.HasRegion='Y')"
178             + " AND I_IsImported<>'Y'").append(clientCheck);
179         no = DB.executeUpdate(sql.toString());
180         Log.trace(Log.l3_Util, "ImportBPartner.doIt", "Invalid Region=" + no);
181 
182         //  Set Greeting
183         sql = new StringBuffer   ("UPDATE I_BPartner i "
184             + "SET C_Greeting_ID=(SELECT C_Greeting_ID FROM C_Greeting g"
185             + " WHERE i.BPContactGreeting=g.Name AND g.AD_Client_ID IN (0, i.AD_Client_ID)) "
186             + "WHERE C_Greeting_ID IS NULL AND BPContactGreeting IS NOT NULL"
187             + " AND I_IsImported<>'Y'").append(clientCheck);
188         no = DB.executeUpdate(sql.toString());
189         Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Set Greeting=" + no);
190         //
191         sql = new StringBuffer   ("UPDATE I_BPartner i "
192             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Greeting, ' "
193             + "WHERE C_Greeting_ID IS NULL AND BPContactGreeting IS NOT NULL"
194             + " AND I_IsImported<>'Y'").append(clientCheck);
195         no = DB.executeUpdate(sql.toString());
196         Log.trace(Log.l3_Util, "ImportBPartner.doIt", "Invalid Greeting=" + no);
197 
198 
199         //  Existing BPartner ? Match Value
200         sql = new StringBuffer   ("UPDATE I_BPartner i "
201             + "SET C_BPartner_ID=(SELECT C_BPartner_ID FROM C_BPartner p"
202             + " WHERE i.Value=p.Value AND p.AD_Client_ID=i.AD_Client_ID) "
203             + "WHERE C_BPartner_ID IS NULL AND Value IS NOT NULL"
204             + " AND I_IsImported='N'").append(clientCheck);
205         no = DB.executeUpdate(sql.toString());
206         Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Found BPartner=" + no);
207 
208         //  Existing Contact ? Match Name
209         sql = new StringBuffer   ("UPDATE I_BPartner i "
210             + "SET AD_User_ID=(SELECT AD_User_ID FROM AD_User c"
211             + " WHERE i.ContactName=c.Name AND i.C_BPartner_ID=c.C_BPartner_ID AND c.AD_Client_ID=i.AD_Client_ID) "
212             + "WHERE C_BPartner_ID IS NOT NULL AND AD_User_ID IS NULL AND ContactName IS NOT NULL"
213             + " AND I_IsImported='N'").append(clientCheck);
214         no = DB.executeUpdate(sql.toString());
215         Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Found Contact=" + no);
216 
217         //  Existing Location ? Exact Match
218         sql = new StringBuffer   ("UPDATE I_BPartner i "
219             + "SET C_BPartner_Location_ID=(SELECT C_BPartner_Location_ID"
220             + " FROM C_BPartner_Location bpl INNER JOIN C_Location l ON (bpl.C_Location_ID=l.C_Location_ID)"
221             + " WHERE i.C_BPartner_ID=bpl.C_BPartner_ID AND bpl.AD_Client_ID=i.AD_Client_ID"
222             + " AND DUMP(i.Address1)=DUMP(l.Address1) AND DUMP(i.Address2)=DUMP(l.Address2)"
223             + " AND DUMP(i.City)=DUMP(l.City) AND DUMP(i.Postal)=DUMP(l.Postal) AND DUMP(i.Postal_Add)=DUMP(l.Postal_Add)"
224             + " AND DUMP(i.C_Region_ID)=DUMP(l.C_Region_ID) AND DUMP(i.C_Country_ID)=DUMP(l.C_Country_ID)) "
225             + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL"
226             + " AND I_IsImported='N'").append(clientCheck);
227         no = DB.executeUpdate(sql.toString());
228         Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Found Location=" + no);
229 
230 
231         //  -------------------------------------------------------------------
232         int noInsert = 0;
233         int noUpdate = 0;
234 
235         //  Go through Records
236         sql = new StringBuffer   ("SELECT I_BPartner_ID, C_BPartner_ID,"
237             + "C_BPartner_Location_ID,COALESCE (Address1,Address2,City),"
238             + "AD_User_ID,ContactName "
239             + "FROM I_BPartner "
240             + "WHERE I_IsImported='N'").append(clientCheck);
241         Connection conn = DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED);
242         try
243         {
244             //  Insert BPartner
245             PreparedStatement pstmt_insertBPartner = conn.prepareStatement
246                 ("INSERT INTO C_BPartner (C_BPartner_ID,"
247                 + "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
248                 + "Value,Name,Name2,Description,DUNS,TaxID,NAICS,C_BP_Group_ID,IsSummary) "
249                 + "SELECT ?,"
250                 + "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy,"
251                 + "Value,Name,Name2,Description,DUNS,TaxID,NAICS,C_BP_Group_ID,'N' "
252                 + "FROM I_BPartner "
253                 + "WHERE I_BPartner_ID=?");
254 
255             //  Update BPartner
256             PreparedStatement pstmt_updateBPartner = conn.prepareStatement
257                 ("UPDATE C_BPartner "
258                 + "SET (Value,Name,Name2,Description,DUNS,TaxID,NAICS,C_BP_Group_ID,Updated,UpdatedBy)="
259                 + "(SELECT Value,Name,Name2,Description,DUNS,TaxID,NAICS,C_BP_Group_ID,SysDate,UpdatedBy"
260                 + " FROM I_BPartner"
261                 + " WHERE I_BPartner_ID=?) "
262                 + "WHERE C_BPartner_ID=?");
263 
264             //  Insert Location
265             PreparedStatement pstmt_insertLocation = conn.prepareStatement
266                 ("INSERT INTO C_Location (C_Location_ID,"
267                 + "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
268                 + "Address1,Address2,City,Postal,Postal_Add,C_Country_ID,C_Region_ID) "
269                 + "SELECT ?,"
270                 + "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy,"
271                 + "Address1,Address2,City,Postal,Postal_Add,C_Country_ID,C_Region_ID "
272                 + "FROM I_BPartner "
273                 + "WHERE I_BPartner_ID=?");
274 
275         //  PreparedStatement pstmt_updateLocation = conn.prepareStatement
276         //      ("");
277 
278             //  Insert BP Location
279             PreparedStatement pstmt_insertBPLocation = conn.prepareStatement
280                 ("INSERT INTO C_BPartner_Location (C_BPartner_Location_ID,"
281                 + "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
282                 + "Name,IsBillTo,IsShipTo,IsPayFrom,IsRemitTo,"
283                 + "Phone,Phone2,Fax, C_BPartner_ID,C_Location_ID) "
284                 + "SELECT ?,"
285                 + "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy,"
286                 + "City,'Y','Y','Y','Y',"
287                 + "Phone,Phone2,Fax, ?,? "
288                 + "FROM I_BPartner "
289                 + "WHERE I_BPartner_ID=?");
290 
291         //  PreparedStatement pstmt_updateBPLocation = conn.prepareStatement
292         //      ("");
293 
294             //  Insert Contact
295             PreparedStatement pstmt_insertBPContact = conn.prepareStatement
296                 ("INSERT INTO AD_User (AD_User_ID,"
297                 + "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
298                 + "C_BPartner_ID,C_BPartner_Location_ID,C_Greeting_ID,"
299                 + "Name,Title,Description,Comments,Phone,Phone2,Fax,EMail,Birthday) "
300                 + "SELECT ?,"
301                 + "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy,"
302                 + "?,?,C_Greeting_ID,"
303                 + "ContactName,Title,ContactDescription,Comments,Phone,Phone2,Fax,EMail,Birthday "
304                 + "FROM I_BPartner "
305                 + "WHERE I_BPartner_ID=?");
306 
307             //  Update Contact
308             PreparedStatement pstmt_updateBPContact = conn.prepareStatement
309                 ("UPDATE AD_User "
310                 + "SET (C_Greeting_ID,"
311                 + "Name,Title,Description,Comments,Phone,Phone2,Fax,EMail,Birthday,Updated,UpdatedBy)="
312                 + "(SELECT C_Greeting_ID,"
313                 + "ContactName,Title,ContactDescription,Comments,Phone,Phone2,Fax,EMail,Birthday,SysDate,UpdatedBy"
314                 + " FROM I_BPartner WHERE I_BPartner_ID=?) "
315                 + "WHERE AD_User_ID=?");
316 
317             //  Set Imported = Y
318             PreparedStatement pstmt_setImported = conn.prepareStatement
319                 ("UPDATE I_BPartner SET I_IsImported='Y',"
320                 + " C_BPartner_ID=?, C_BPartner_Location_ID=?, AD_User_ID=?, "
321                 + " Updated=SysDate, Processed='Y' WHERE I_BPartner_ID=?");
322             //
323             PreparedStatement pstmt = DB.prepareStatement(sql.toString());
324             ResultSet rs = pstmt.executeQuery();
325             while (rs.next())
326             {
327                 int I_BPartner_ID = rs.getInt(1);
328                 int C_BPartner_ID = rs.getInt(2);
329                 boolean newBPartner = C_BPartner_ID == 0;
330                 int C_BPartner_Location_ID = rs.getInt(3);
331                 boolean newLocation = rs.getString(4) != null;
332                 int AD_User_ID = rs.getInt(5);
333                 boolean newContact =  rs.getString(6) != null;
334                 Log.trace(Log.l6_Database, "I_BPartner_ID=" + I_BPartner_ID
335                     + ", C_BPartner_ID=" + C_BPartner_ID
336                     + ", C_BPartner_Location_ID=" + C_BPartner_Location_ID + " create=" + newLocation
337                     + ", AD_User_ID=" + AD_User_ID + " create=" + newContact);
338 
339 
340                 //  ****    Create/Update BPartner
341                 if (newBPartner)    //  Insert new BPartner
342                 {
343                     C_BPartner_ID = DB.getKeyNextNo(m_AD_Client_ID, "C_BPartner");
344                     pstmt_insertBPartner.setInt(1, C_BPartner_ID);
345                     pstmt_insertBPartner.setInt(2, I_BPartner_ID);
346                     try
347                     {
348                         no = pstmt_insertBPartner.executeUpdate();
349                         Log.trace(10, "Insert BPartner = " + no);
350                         noInsert++;
351                     }
352                     catch (SQLException ex)
353                     {
354                         Log.trace(10, "Insert BPartner - " + ex.toString());
355                         sql = new StringBuffer   ("UPDATE I_BPartner i "
356                             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert BPartner: " + ex.toString()))
357                             .append("WHERE I_BPartner_ID=").append(I_BPartner_ID);
358                         DB.executeUpdate(sql.toString());
359                         continue;
360                     }
361                 }
362                 else                //  Update existing BPartner
363                 {
364                     pstmt_updateBPartner.setInt(1, I_BPartner_ID);
365                     pstmt_updateBPartner.setInt(2, C_BPartner_ID);
366                     try
367                     {
368                         no = pstmt_updateBPartner.executeUpdate();
369                         Log.trace(10, "Update BPartner = " + no);
370                         noUpdate++;
371                     }
372                     catch (SQLException ex)
373                     {
374                         Log.trace(10, "Update BPartner - " + ex.toString());
375                         sql = new StringBuffer   ("UPDATE I_BPartner i "
376                             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Update BPartner: " + ex.toString()))
377                             .append("WHERE I_BPartner_ID=").append(I_BPartner_ID);
378                         DB.executeUpdate(sql.toString());
379                         continue;
380                     }
381                 }
382 
383                 //  ****    Create/Update BPartner Location
384                 if (C_BPartner_Location_ID != 0)        //  Update Location
385                 {
386                 }
387                 else if (newLocation)                   //  New Location
388                 {
389                     int C_Location_ID = DB.getKeyNextNo(m_AD_Client_ID, "C_Location");
390                     pstmt_insertLocation.setInt(1, C_Location_ID);
391                     pstmt_insertLocation.setInt(2, I_BPartner_ID);
392                     try
393                     {
394                         no = pstmt_insertLocation.executeUpdate();
395                         Log.trace(10, "Insert Location = " + no);
396                     }
397                     catch (SQLException ex)
398                     {
399                         Log.trace(10, "Insert Location - " + ex.toString());
400                         conn.rollback();
401                         noInsert--;
402                         sql = new StringBuffer   ("UPDATE I_BPartner i "
403                             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert Location: " + ex.toString()))
404                             .append("WHERE I_BPartner_ID=").append(I_BPartner_ID);
405                         DB.executeUpdate(sql.toString());
406                         continue;
407                     }
408                     //
409                     C_BPartner_Location_ID = DB.getKeyNextNo(m_AD_Client_ID, "C_BPartner_Location");
410                     pstmt_insertBPLocation.setInt(1, C_BPartner_Location_ID);
411                     pstmt_insertBPLocation.setInt(2, C_BPartner_ID);
412                     pstmt_insertBPLocation.setInt(3, C_Location_ID);
413                     pstmt_insertBPLocation.setInt(4, I_BPartner_ID);
414                     try
415                     {
416                         no = pstmt_insertBPLocation.executeUpdate();
417                         Log.trace(10, "Insert BP Location = " + no);
418                     }
419                     catch (SQLException ex)
420                     {
421                         Log.trace(10, "Insert BPLocation - " + ex.toString());
422                         conn.rollback();
423                         noInsert--;
424                         sql = new StringBuffer   ("UPDATE I_BPartner i "
425                             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert BPLocation: " + ex.toString()))
426                             .append("WHERE I_BPartner_ID=").append(I_BPartner_ID);
427                         DB.executeUpdate(sql.toString());
428                         continue;
429                     }
430                 }
431 
432                 //  ****    Create/Update Contact
433                 if (AD_User_ID != 0)
434                 {
435                     pstmt_updateBPContact.setInt(1, I_BPartner_ID);
436                     pstmt_updateBPContact.setInt(2, AD_User_ID);
437                     try
438                     {
439                         no = pstmt_updateBPContact.executeUpdate();
440                         Log.trace(10, "Update BP Contact = " + no);
441                     }
442                     catch (SQLException ex)
443                     {
444                         Log.trace(10, "Update BP Contact - " + ex.toString());
445                         conn.rollback();
446                         noInsert--;
447                         sql = new StringBuffer   ("UPDATE I_BPartner i "
448                             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Update BP Contact: " + ex.toString()))
449                             .append("WHERE I_BPartner_ID=").append(I_BPartner_ID);
450                         DB.executeUpdate(sql.toString());
451                         continue;
452                     }
453                 }
454                 else if (newContact)                    //  New Contact
455                 {
456                     AD_User_ID = DB.getKeyNextNo(m_AD_Client_ID, "AD_User");
457                     pstmt_insertBPContact.setInt(1, AD_User_ID);
458                     pstmt_insertBPContact.setInt(2, C_BPartner_ID);
459                     if (C_BPartner_Location_ID == 0)
460                         pstmt_insertBPContact.setNull(3, Types.NUMERIC);
461                     else
462                         pstmt_insertBPContact.setInt(3, C_BPartner_Location_ID);
463                     pstmt_insertBPContact.setInt(4, I_BPartner_ID);
464                     try
465                     {
466                         no = pstmt_insertBPContact.executeUpdate();
467                         Log.trace(10, "Insert BP Contact = " + no);
468                     }
469                     catch (SQLException ex)
470                     {
471                         Log.trace(10, "Insert BPContact - " + ex.toString());
472                         conn.rollback();
473                         noInsert--;
474                         sql = new StringBuffer   ("UPDATE I_BPartner i "
475                             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert BPContact: " + ex.toString()))
476                             .append("WHERE I_BPartner_ID=").append(I_BPartner_ID);
477                         DB.executeUpdate(sql.toString());
478                         continue;
479                     }
480                 }
481 
482                 //  Update I_Product
483                 pstmt_setImported.setInt(1, C_BPartner_ID);
484                 if (C_BPartner_Location_ID == 0)
485                     pstmt_setImported.setNull(2, Types.NUMERIC);
486                 else
487                     pstmt_setImported.setInt(2, C_BPartner_Location_ID);
488                 if (AD_User_ID == 0)
489                     pstmt_setImported.setNull(3, Types.NUMERIC);
490                 else
491                     pstmt_setImported.setInt(3, AD_User_ID);
492                 pstmt_setImported.setInt(4, I_BPartner_ID);
493                 no = pstmt_setImported.executeUpdate();
494                 //
495                 conn.commit();
496             }   //  for all I_Product
497             rs.close();
498             pstmt.close();
499             //
500             pstmt_insertBPartner.close();
501             pstmt_updateBPartner.close();
502             pstmt_insertLocation.close();
503         //  pstmt_updateLocation.close();
504             pstmt_insertBPLocation.close();
505         //  pstmt_updateBPLocation.close();
506             pstmt_insertBPContact.close();
507             pstmt_updateBPContact.close();
508             pstmt_setImported.close();
509             //
510             conn.close();
511             conn = null;
512         }
513         catch (SQLException e)
514         {
515             try
516             {
517                 if (conn != null)
518                     conn.close();
519                 conn = null;
520             }
521             catch (SQLException ex)
522             {
523             }
524             throw new Exception   ("ImportBPartner.doIt", e);
525         }
526         finally
527         {
528             if (conn != null)
529                 conn.close();
530             conn = null;
531         }
532 
533         //  Set Error to indicator to not imported
534         sql = new StringBuffer   ("UPDATE I_BPartner "
535             + "SET I_IsImported='N', Updated=SysDate "
536             + "WHERE I_IsImported<>'Y'").append(clientCheck);
537         no = DB.executeUpdate(sql.toString());
538         addLog (0, null, new BigDecimal (no), "@Errors@");
539         addLog (0, null, new BigDecimal (noInsert), "@C_BPartner_ID@: @Inserted@");
540         addLog (0, null, new BigDecimal (noUpdate), "@C_BPartner_ID@: @Updated@");
541         return "";
542     }   //  doIt
543 
544 }   //  ImportBPartner
545
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags