ImportOrder


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.model.*;
20  import org.compiere.util.*;
21  
22  /**
23   *  Import Order from I_Order
24   *
25   *  @author     Jorg Janke
26   *  @version    $Id: ImportOrder.java,v 1.9 2003/09/05 04:59:48 jjanke Exp $
27   */
28  public class ImportOrder extends SvrProcess
29  {
30      /**
31       *  Import BPartner Constructor
32       */
33      public ImportOrder()
34      {
35          super();
36      }   //  ImportOrder
37  
38      /** Client to be imported to        */
39      private int             m_AD_Client_ID = 0;
40      /** Organization to be imported to      */
41      private int             m_AD_Org_ID = 0;
42      /** Delete old Imported             */
43      private boolean         m_deleteOldImported = false;
44      /** Document Action                 */
45      private String            m_docAction = MOrder.DOCACTION_Process;
46  
47  
48      /** Effective                       */
49      private Timestamp       m_DateValue = null;
50  
51      /**
52       *  Prepare - e.g., get Parameters.
53       */
54      protected void prepare()
55      {
56          ProcessInfoParameter[] para = getParameter();
57          for (int i = 0; i < para.length; i++)
58          {
59              String   name = para[i].getParameterName();
60              if (name.equals("AD_Client_ID"))
61                  m_AD_Client_ID = ((BigDecimal)para[i].getParameter()).intValue();
62              else if (name.equals("AD_Org_ID"))
63                  m_AD_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();
64              else if (name.equals("DeleteOldImported"))
65                  m_deleteOldImported = "Y".equals(para[i].getParameter());
66              else if (name.equals("DocAction"))
67                  m_docAction = (String  )para[i].getParameter();
68              else
69                  log.error("prepare - Unknown Parameter: " + name);
70          }
71          if (m_DateValue == null)
72              m_DateValue = new Timestamp (System.currentTimeMillis());
73      }   //  prepare
74  
75  
76      /**
77       *  Perrform process.
78       *  @return Message
79       *  @throws Exception
80       */
81      protected String   doIt() throws java.lang.Exception  
82      {
83          StringBuffer   sql = null;
84          int no = 0;
85          String   clientCheck = " AND AD_Client_ID=" + m_AD_Client_ID;
86  
87          //  ****    Prepare ****
88  
89          //  Delete Old Imported
90          if (m_deleteOldImported)
91          {
92              sql = new StringBuffer   ("DELETE I_Order "
93                    + "WHERE I_IsImported='Y'").append (clientCheck);
94              no = DB.executeUpdate (sql.toString ());
95              log.debug ("doIt - Delete Old Impored =" + no);
96          }
97  
98          //  Set Client, Org, IsActive, Created/Updated
99          sql = new StringBuffer   ("UPDATE I_Order "
100               + "SET AD_Client_ID = COALESCE (AD_Client_ID,").append (m_AD_Client_ID).append ("),"
101               + " AD_Org_ID = COALESCE (AD_Org_ID,").append (m_AD_Org_ID).append ("),"
102               + " IsActive = COALESCE (IsActive, 'Y'),"
103               + " Created = COALESCE (Created, SysDate),"
104               + " CreatedBy = COALESCE (CreatedBy, 0),"
105               + " Updated = COALESCE (Updated, SysDate),"
106               + " UpdatedBy = COALESCE (UpdatedBy, 0),"
107               + " I_ErrorMsg = NULL,"
108               + " I_IsImported = 'N' "
109               + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
110         no = DB.executeUpdate (sql.toString ());
111         log.info ("doIt - Reset=" + no);
112 
113         sql = new StringBuffer   ("UPDATE I_Order o "
114             + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Org, '"
115             + "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0"
116             + " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))"
117             + " AND I_IsImported<>'Y'").append (clientCheck);
118         no = DB.executeUpdate (sql.toString ());
119         if (no != 0)
120             log.warn ("doIt - Invalid Org=" + no);
121 
122         //  Document Type - PO - SO
123         sql = new StringBuffer   ("UPDATE I_Order o " //  PO Document Type Name
124               + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName"
125               + " AND d.DocBaseType='POO' AND o.AD_Client_ID=d.AD_Client_ID) "
126               + "WHERE C_DocType_ID IS NULL AND IsSOTrx='N' AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").append (clientCheck);
127         no = DB.executeUpdate (sql.toString ());
128         log.debug ("doIt - Set PO DocType=" + no);
129         sql = new StringBuffer   ("UPDATE I_Order o " //  SO Document Type Name
130               + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName"
131               + " AND d.DocBaseType='SOO' AND o.AD_Client_ID=d.AD_Client_ID) "
132               + "WHERE C_DocType_ID IS NULL AND IsSOTrx='Y' AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").append (clientCheck);
133         no = DB.executeUpdate (sql.toString ());
134         log.debug ("doIt - Set SO DocType=" + no);
135         sql = new StringBuffer   ("UPDATE I_Order o "
136               + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName"
137               + " AND d.DocBaseType IN ('SOO','POO') AND o.AD_Client_ID=d.AD_Client_ID) "
138             //+ "WHERE C_DocType_ID IS NULL AND IsSOTrx IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").append (clientCheck);
139               + "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").append (clientCheck);
140         no = DB.executeUpdate (sql.toString ());
141         log.debug ("doIt - Set DocType=" + no);
142         sql = new StringBuffer   ("UPDATE I_Order "   //  Error Invalid Doc Type Name
143               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid DocTypeName, ' "
144               + "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL"
145               + " AND I_IsImported<>'Y'").append (clientCheck);
146         no = DB.executeUpdate (sql.toString ());
147         if (no != 0)
148             log.warn ("doIt - Invalid DocTypeName=" + no);
149         //  DocType Default
150         sql = new StringBuffer   ("UPDATE I_Order o " //  Default PO
151               + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.IsDefault='Y'"
152               + " AND d.DocBaseType='POO' AND ROWNUM=1 AND o.AD_Client_ID=d.AD_Client_ID) "
153               + "WHERE C_DocType_ID IS NULL AND IsSOTrx='N' AND I_IsImported<>'Y'").append (clientCheck);
154         no = DB.executeUpdate (sql.toString ());
155         log.debug ("doIt - Set PO Default DocType=" + no);
156         sql = new StringBuffer   ("UPDATE I_Order o " //  Default SO
157               + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.IsDefault='Y'"
158               + " AND d.DocBaseType='SOO' AND ROWNUM=1 AND o.AD_Client_ID=d.AD_Client_ID) "
159               + "WHERE C_DocType_ID IS NULL AND IsSOTrx='Y' AND I_IsImported<>'Y'").append (clientCheck);
160         no = DB.executeUpdate (sql.toString ());
161         log.debug ("doIt - Set SO Default DocType=" + no);
162         sql = new StringBuffer   ("UPDATE I_Order o "
163               + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.IsDefault='Y'"
164               + " AND d.DocBaseType IN('SOO','POO') AND ROWNUM=1 AND o.AD_Client_ID=d.AD_Client_ID) "
165               + "WHERE C_DocType_ID IS NULL AND IsSOTrx IS NULL AND I_IsImported<>'Y'").append (clientCheck);
166         no = DB.executeUpdate (sql.toString ());
167         log.debug ("doIt - Set Default DocType=" + no);
168         sql = new StringBuffer   ("UPDATE I_Order "   // No DocType
169               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No DocType, ' "
170               + "WHERE C_DocType_ID IS NULL"
171               + " AND I_IsImported<>'Y'").append (clientCheck);
172         no = DB.executeUpdate (sql.toString ());
173         if (no != 0)
174             log.warn ("doIt - No DocType=" + no);
175 
176         //  Set IsSOTrx
177         sql = new StringBuffer   ("UPDATE I_Order o SET IsSOTrx='Y' "
178               + "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='SOO' AND o.AD_Client_ID=d.AD_Client_ID)"
179               + " AND C_DocType_ID IS NOT NULL"
180               + " AND I_IsImported<>'Y'").append (clientCheck);
181         no = DB.executeUpdate (sql.toString ());
182         log.debug ("doIt - Set IsSOTrx=Y=" + no);
183         sql = new StringBuffer   ("UPDATE I_Order o SET IsSOTrx='N' "
184               + "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='POO' AND o.AD_Client_ID=d.AD_Client_ID)"
185               + " AND C_DocType_ID IS NOT NULL"
186               + " AND I_IsImported<>'Y'").append (clientCheck);
187         no = DB.executeUpdate (sql.toString ());
188         log.debug ("doIt - Set IsSOTrx=N=" + no);
189 
190         //  Price List
191         sql = new StringBuffer   ("UPDATE I_Order o "
192               + "SET M_PriceList_ID=(SELECT M_PriceList_ID FROM M_PriceList p WHERE p.IsDefault='Y'"
193               + " AND p.C_Currency_ID=o.C_Currency_ID AND p.IsSOPriceList=o.IsSOTrx AND ROWNUM=1 AND o.AD_Client_ID=p.AD_Client_ID) "
194               + "WHERE M_PriceList_ID IS NULL AND I_IsImported<>'Y'").append (clientCheck);
195         no = DB.executeUpdate (sql.toString ());
196         log.debug("doIt - Set Default Currency PriceList=" + no);
197         sql = new StringBuffer   ("UPDATE I_Order o "
198               + "SET M_PriceList_ID=(SELECT M_PriceList_ID FROM M_PriceList p WHERE p.IsDefault='Y'"
199               + " AND p.IsSOPriceList=o.IsSOTrx AND ROWNUM=1 AND o.AD_Client_ID=p.AD_Client_ID) "
200               + "WHERE M_PriceList_ID IS NULL AND C_Currency_ID IS NULL AND I_IsImported<>'Y'").append (clientCheck);
201         no = DB.executeUpdate (sql.toString ());
202         log.debug("doIt - Set Default PriceList=" + no);
203         sql = new StringBuffer   ("UPDATE I_Order o "
204               + "SET M_PriceList_ID=(SELECT M_PriceList_ID FROM M_PriceList p "
205               + " WHERE p.C_Currency_ID=o.C_Currency_ID AND p.IsSOPriceList=o.IsSOTrx AND ROWNUM=1 AND o.AD_Client_ID=p.AD_Client_ID) "
206               + "WHERE M_PriceList_ID IS NULL AND I_IsImported<>'Y'").append (clientCheck);
207         no = DB.executeUpdate (sql.toString ());
208         log.debug("doIt - Set Currency PriceList=" + no);
209         sql = new StringBuffer   ("UPDATE I_Order o "
210               + "SET M_PriceList_ID=(SELECT M_PriceList_ID FROM M_PriceList p "
211               + " WHERE p.IsSOPriceList=o.IsSOTrx AND ROWNUM=1 AND o.AD_Client_ID=p.AD_Client_ID) "
212               + "WHERE M_PriceList_ID IS NULL AND C_Currency_ID IS NULL AND I_IsImported<>'Y'").append (clientCheck);
213         no = DB.executeUpdate (sql.toString ());
214         log.debug("doIt - Set PriceList=" + no);
215         //
216         sql = new StringBuffer   ("UPDATE I_Order "
217               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No PriceList, ' "
218               + "WHERE M_PriceList_ID IS NULL"
219               + " AND I_IsImported<>'Y'").append (clientCheck);
220         no = DB.executeUpdate (sql.toString ());
221         if (no != 0)
222             log.warn("doIt - No PriceList=" + no);
223 
224         //  Payment Term
225         sql = new StringBuffer   ("UPDATE I_Order o "
226               + "SET C_PaymentTerm_ID=(SELECT C_PaymentTerm_ID FROM C_PaymentTerm p"
227               + " WHERE o.PaymentTermValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID) "
228               + "WHERE C_PaymentTerm_ID IS NULL AND PaymentTermValue IS NOT NULL AND I_IsImported<>'Y'").append (clientCheck);
229         no = DB.executeUpdate (sql.toString ());
230         log.debug("doIt - Set PaymentTerm=" + no);
231         sql = new StringBuffer   ("UPDATE I_Order o "
232               + "SET C_PaymentTerm_ID=(SELECT C_PaymentTerm_ID FROM C_PaymentTerm p"
233               + " WHERE p.IsDefault='Y' AND ROWNUM=1 AND o.AD_Client_ID=p.AD_Client_ID) "
234               + "WHERE C_PaymentTerm_ID IS NULL AND o.PaymentTermValue IS NULL AND I_IsImported<>'Y'").append (clientCheck);
235         no = DB.executeUpdate (sql.toString ());
236         log.debug("doIt - Set Default PaymentTerm=" + no);
237         //
238         sql = new StringBuffer   ("UPDATE I_Order "
239               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No PaymentTerm, ' "
240               + "WHERE C_PaymentTerm_ID IS NULL"
241               + " AND I_IsImported<>'Y'").append (clientCheck);
242         no = DB.executeUpdate (sql.toString ());
243         if (no != 0)
244             log.warn ("doIt - No PaymentTerm=" + no);
245 
246         //  Warehouse
247         sql = new StringBuffer   ("UPDATE I_Order o "
248               + "SET M_Warehouse_ID=(SELECT M_Warehouse_ID FROM M_Warehouse w"
249               + " WHERE ROWNUM=1 AND o.AD_Client_ID=w.AD_Client_ID AND o.AD_Org_ID=w.AD_Org_ID) "
250               + "WHERE M_Warehouse_ID IS NULL AND I_IsImported<>'Y'").append (clientCheck);
251         no = DB.executeUpdate (sql.toString ());
252         log.debug("doIt - Set Warehouse=" + no);
253         sql = new StringBuffer   ("UPDATE I_Order o "
254               + "SET M_Warehouse_ID=(SELECT M_Warehouse_ID FROM M_Warehouse w"
255               + " WHERE o.AD_Client_ID=w.AD_Client_ID) "
256               + "WHERE M_Warehouse_ID IS NULL"
257               + " AND EXISTS (SELECT AD_Client_ID FROM M_Warehouse w WHERE w.AD_Client_ID=o.AD_Client_ID GROUP BY AD_Client_ID HAVING COUNT(*)=1)"
258               + " AND I_IsImported<>'Y'").append (clientCheck);
259         no = DB.executeUpdate (sql.toString ());
260         log.debug("doIt - Set Only Warehouse=" + no);
261         //
262         sql = new StringBuffer   ("UPDATE I_Order "
263               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Warehouse, ' "
264               + "WHERE M_Warehouse_ID IS NULL"
265               + " AND I_IsImported<>'Y'").append (clientCheck);
266         no = DB.executeUpdate (sql.toString ());
267         if (no != 0)
268             log.warn ("doIt - No Warehouse=" + no);
269 
270         //  BP from EMail
271         sql = new StringBuffer   ("UPDATE I_Order o "
272               + "SET (C_BPartner_ID,AD_User_ID)=(SELECT C_BPartner_ID,AD_User_ID FROM AD_User u"
273               + " WHERE o.EMail=u.EMail AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL) "
274               + "WHERE C_BPartner_ID IS NULL AND EMail IS NOT NULL"
275               + " AND I_IsImported<>'Y'").append (clientCheck);
276         no = DB.executeUpdate (sql.toString ());
277         log.debug("doIt - Set BP from EMail=" + no);
278         //  BP from ContactName
279         sql = new StringBuffer   ("UPDATE I_Order o "
280               + "SET (C_BPartner_ID,AD_User_ID)=(SELECT C_BPartner_ID,AD_User_ID FROM AD_User u"
281               + " WHERE o.ContactName=u.Name AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL) "
282               + "WHERE C_BPartner_ID IS NULL AND ContactName IS NOT NULL"
283               + " AND EXISTS (SELECT Name FROM AD_User u WHERE o.ContactName=u.Name AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL GROUP BY Name HAVING COUNT(*)=1)"
284               + " AND I_IsImported<>'Y'").append (clientCheck);
285         no = DB.executeUpdate (sql.toString ());
286         log.debug("doIt - Set BP from ContactName=" + no);
287         //  BP from Value
288         sql = new StringBuffer   ("UPDATE I_Order o "
289               + "SET C_BPartner_ID=(SELECT C_BPartner_ID FROM C_BPartner bp"
290               + " WHERE o.BPartnerValue=bp.Value AND o.AD_Client_ID=bp.AD_Client_ID AND ROWNUM=1) "
291               + "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NOT NULL"
292               + " AND I_IsImported<>'Y'").append (clientCheck);
293         no = DB.executeUpdate (sql.toString ());
294         log.debug("doIt - Set BP from Value=" + no);
295         //  Default BP
296         sql = new StringBuffer   ("UPDATE I_Order o "
297               + "SET C_BPartner_ID=(SELECT C_BPartnerCashTrx_ID FROM AD_ClientInfo c"
298               + " WHERE o.AD_Client_ID=c.AD_Client_ID) "
299               + "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NULL AND Name IS NULL"
300               + " AND I_IsImported<>'Y'").append (clientCheck);
301         no = DB.executeUpdate (sql.toString ());
302         log.debug("doIt - Set Default BP=" + no);
303 
304         //  Existing Location ? Exact Match
305         sql = new StringBuffer   ("UPDATE I_Order o "
306               + "SET (BillTo_ID,C_BPartner_Location_ID)=(SELECT C_BPartner_Location_ID,C_BPartner_Location_ID"
307               + " FROM C_BPartner_Location bpl INNER JOIN C_Location l ON (bpl.C_Location_ID=l.C_Location_ID)"
308               + " WHERE o.C_BPartner_ID=bpl.C_BPartner_ID AND bpl.AD_Client_ID=o.AD_Client_ID"
309               + " AND DUMP(o.Address1)=DUMP(l.Address1) AND DUMP(o.Address2)=DUMP(l.Address2)"
310               + " AND DUMP(o.City)=DUMP(l.City) AND DUMP(o.Postal)=DUMP(l.Postal)"
311               + " AND DUMP(o.C_Region_ID)=DUMP(l.C_Region_ID) AND DUMP(o.C_Country_ID)=DUMP(l.C_Country_ID)) "
312               + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL"
313               + " AND I_IsImported='N'").append (clientCheck);
314         no = DB.executeUpdate (sql.toString ());
315         log.debug("doIt - Found Location=" + no);
316         //  Set Bill Location from BPartner
317         sql = new StringBuffer   ("UPDATE I_Order o "
318               + "SET BillTo_ID=(SELECT C_BPartner_Location_ID FROM C_BPartner_Location l"
319               + " WHERE l.C_BPartner_ID=o.C_BPartner_ID AND o.AD_Client_ID=l.AD_Client_ID"
320               + " AND ((l.IsBillTo='Y' AND o.IsSOTrx='Y') OR (l.IsPayFrom='Y' AND o.IsSOTrx='N'))"
321               + " AND ROWNUM=1) "
322               + "WHERE C_BPartner_ID IS NOT NULL AND BillTo_ID IS NULL"
323               + " AND I_IsImported<>'Y'").append (clientCheck);
324         no = DB.executeUpdate (sql.toString ());
325         log.debug("doIt - Set BP BillTo from BP=" + no);
326         //  Set Location from BPartner
327         sql = new StringBuffer   ("UPDATE I_Order o "
328               + "SET C_BPartner_Location_ID=(SELECT C_BPartner_Location_ID FROM C_BPartner_Location l"
329               + " WHERE l.C_BPartner_ID=o.C_BPartner_ID AND o.AD_Client_ID=l.AD_Client_ID"
330               + " AND ((l.IsShipTo='Y' AND o.IsSOTrx='Y') OR o.IsSOTrx='N')"
331               + " AND ROWNUM=1) "
332               + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL"
333               + " AND I_IsImported<>'Y'").append (clientCheck);
334         no = DB.executeUpdate (sql.toString ());
335         log.debug("doIt - Set BP Location from BP=" + no);
336         //
337         sql = new StringBuffer   ("UPDATE I_Order "
338               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No BP Location, ' "
339               + "WHERE C_BPartner_ID IS NOT NULL AND (BillTo_ID IS NULL OR C_BPartner_Location_ID IS NULL)"
340               + " AND I_IsImported<>'Y'").append (clientCheck);
341         no = DB.executeUpdate (sql.toString ());
342         if (no != 0)
343             log.warn ("doIt - No BP Location=" + no);
344 
345         //  Set Country
346         sql = new StringBuffer   ("UPDATE I_Order o "
347               + "SET CountryCode=(SELECT CountryCode FROM C_Country c WHERE c.IsDefault='Y'"
348               + " AND c.AD_Client_ID IN (0, o.AD_Client_ID) AND ROWNUM=1) "
349               + "WHERE C_BPartner_ID IS NULL AND CountryCode IS NULL AND C_Country_ID IS NULL"
350               + " AND I_IsImported<>'Y'").append (clientCheck);
351         no = DB.executeUpdate (sql.toString ());
352         log.debug("doIt - Set Country Default=" + no);
353         sql = new StringBuffer   ("UPDATE I_Order o "
354               + "SET C_Country_ID=(SELECT C_Country_ID FROM C_Country c"
355               + " WHERE o.CountryCode=c.CountryCode AND c.AD_Client_ID IN (0, o.AD_Client_ID)) "
356               + "WHERE C_BPartner_ID IS NULL AND C_Country_ID IS NULL AND CountryCode IS NOT NULL"
357               + " AND I_IsImported<>'Y'").append (clientCheck);
358         no = DB.executeUpdate (sql.toString ());
359         log.debug("doIt - Set Country=" + no);
360         //
361         sql = new StringBuffer   ("UPDATE I_Order "
362               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Country, ' "
363               + "WHERE C_BPartner_ID IS NULL AND C_Country_ID IS NULL"
364               + " AND I_IsImported<>'Y'").append (clientCheck);
365         no = DB.executeUpdate (sql.toString ());
366         if (no != 0)
367             log.warn ("doIt - Invalid Country=" + no);
368 
369         //  Set Region
370         sql = new StringBuffer   ("UPDATE I_Order o "
371               + "Set RegionName=(SELECT Name FROM C_Region r"
372               + " WHERE r.IsDefault='Y' AND r.C_Country_ID=o.C_Country_ID"
373               + " AND r.AD_Client_ID IN (0, o.AD_Client_ID) AND ROWNUM=1) "
374               + "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL AND RegionName IS NULL"
375               + " AND I_IsImported<>'Y'").append (clientCheck);
376         no = DB.executeUpdate (sql.toString ());
377         log.debug("doIt - Set Region Default=" + no);
378         //
379         sql = new StringBuffer   ("UPDATE I_Order o "
380               + "Set C_Region_ID=(SELECT C_Region_ID FROM C_Region r"
381               + " WHERE r.Name=o.RegionName AND r.C_Country_ID=o.C_Country_ID"
382               + " AND r.AD_Client_ID IN (0, o.AD_Client_ID)) "
383               + "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL AND RegionName IS NOT NULL"
384               + " AND I_IsImported<>'Y'").append (clientCheck);
385         no = DB.executeUpdate (sql.toString ());
386         log.debug("doIt - Set Region=" + no);
387         //
388         sql = new StringBuffer   ("UPDATE I_Order o "
389               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Region, ' "
390               + "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL "
391               + " AND EXISTS (SELECT * FROM C_Country c"
392               + " WHERE c.C_Country_ID=o.C_Country_ID AND c.HasRegion='Y')"
393               + " AND I_IsImported<>'Y'").append (clientCheck);
394         no = DB.executeUpdate (sql.toString ());
395         if (no != 0)
396             log.warn ("doIt - Invalid Region=" + no);
397 
398         //  Product
399         sql = new StringBuffer   ("UPDATE I_Order o "
400               + "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p"
401               + " WHERE o.ProductValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID AND ROWNUM=1) "
402               + "WHERE M_Product_ID IS NULL AND ProductValue IS NOT NULL"
403               + " AND I_IsImported<>'Y'").append (clientCheck);
404         no = DB.executeUpdate (sql.toString ());
405         log.debug("doIt - Set Product from Value=" + no);
406         sql = new StringBuffer   ("UPDATE I_Order o "
407               + "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p"
408               + " WHERE o.UPC=p.UPC AND o.AD_Client_ID=p.AD_Client_ID AND ROWNUM=1) "
409               + "WHERE M_Product_ID IS NULL AND UPC IS NOT NULL"
410               + " AND I_IsImported<>'Y'").append (clientCheck);
411         no = DB.executeUpdate (sql.toString ());
412         log.debug("doIt - Set Product from UPC=" + no);
413         sql = new StringBuffer   ("UPDATE I_Order o "
414               + "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p"
415               + " WHERE o.SKU=p.SKU AND o.AD_Client_ID=p.AD_Client_ID AND ROWNUM=1) "
416               + "WHERE M_Product_ID IS NULL AND SKU IS NOT NULL"
417               + " AND I_IsImported<>'Y'").append (clientCheck);
418         no = DB.executeUpdate (sql.toString ());
419         log.debug("doIt - Set Product fom SKU=" + no);
420         sql = new StringBuffer   ("UPDATE I_Order "
421               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Product, ' "
422               + "WHERE M_Product_ID IS NULL AND (ProductValue IS NOT NULL OR UPC IS NOT NULL OR SKU IS NOT NULL)"
423               + " AND I_IsImported<>'Y'").append (clientCheck);
424         no = DB.executeUpdate (sql.toString ());
425         if (no != 0)
426             log.warn ("doIt - Invalid Product=" + no);
427 
428         //  Tax
429         sql = new StringBuffer   ("UPDATE I_Order o "
430               + "SET C_Tax_ID=(SELECT C_Tax_ID FROM C_Tax t"
431               + " WHERE o.TaxIndicator=t.TaxIndicator AND o.AD_Client_ID=t.AD_Client_ID AND ROWNUM=1) "
432               + "WHERE C_Tax_ID IS NULL AND TaxIndicator IS NOT NULL"
433               + " AND I_IsImported<>'Y'").append (clientCheck);
434         no = DB.executeUpdate (sql.toString ());
435         log.debug ("doIt - Set Tax=" + no);
436         sql = new StringBuffer   ("UPDATE I_Order "
437               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Tax, ' "
438               + "WHERE C_Tax_ID IS NULL AND TaxIndicator IS NOT NULL"
439               + " AND I_IsImported<>'Y'").append (clientCheck);
440         no = DB.executeUpdate (sql.toString ());
441         if (no != 0)
442             log.warn ("doIt - Invalid Tax=" + no);
443 
444         //  -- New BPartner ---------------------------------------------------
445 
446         //  Go through Order Records w/o
447         sql = new StringBuffer   ("SELECT * FROM I_Order "
448               + "WHERE I_IsImported='N' AND C_BPartner_ID IS NULL").append (clientCheck);
449         try
450         {
451             PreparedStatement pstmt = DB.prepareStatement (sql.toString ());
452             ResultSet rs = pstmt.executeQuery ();
453             while (rs.next ())
454             {
455                 X_I_Order imp = new X_I_Order (getCtx (), rs);
456                 if (imp.getBPartnerValue () == null)
457                 {
458                     if (imp.getEmail () != null)
459                         imp.setBPartnerValue (imp.getEmail ());
460                     else if (imp.getName () != null)
461                         imp.setBPartnerValue (imp.getName ());
462                     else
463                         continue;
464                 }
465                 if (imp.getName () == null)
466                 {
467                     if (imp.getContactName () != null)
468                         imp.setName (imp.getContactName ());
469                     else
470                         imp.setName (imp.getBPartnerValue ());
471                 }
472                 //  BPartner
473                 MBPartner bp = new MBPartner (getCtx (), -1);
474                 bp.setClientOrg (imp.getAD_Client_ID (), imp.getAD_Org_ID ());
475                 bp.setValue (imp.getBPartnerValue ());
476                 bp.setName (imp.getName ());
477                 if (!bp.save ())
478                     continue;
479                 imp.setC_BPartner_ID (bp.getC_BPartner_ID ());
480                 //  Location
481                 X_C_Location loc = new X_C_Location (getCtx (), 0);
482                 loc.setAddress1 (imp.getAddress1 ());
483                 loc.setAddress2 (imp.getAddress2 ());
484                 loc.setCity (imp.getCity ());
485                 loc.setPostal (imp.getPostal ());
486                 if (imp.getC_Region_ID () != 0)
487                     loc.setC_Region_ID (imp.getC_Region_ID ());
488                 loc.setC_Country_ID (imp.getC_Country_ID ());
489                 if (!loc.save ())
490                     continue;
491                 imp.setC_Location_ID (loc.getC_Location_ID ());
492                 //  BP Location
493                 MBPartner_Location bpl = new MBPartner_Location (getCtx (), 0, imp.getC_BPartner_ID ());
494                 bpl.setC_Location_ID (imp.getC_Location_ID ());
495                 bpl.setClientOrg (imp.getAD_Client_ID (), imp.getAD_Org_ID ());
496                 if (!bpl.save ())
497                     continue;
498                 imp.setBillTo_ID (bpl.getC_BPartner_Location_ID ());
499                 imp.setC_BPartner_Location_ID (bpl.getC_BPartner_Location_ID ());
500                 //  User/Contact
501                 if (imp.getContactName () != null || imp.getEmail () != null || imp.getPhone () != null)
502                 {
503                     MUser u = new MUser (getCtx (), 0, imp.getC_BPartner_ID ());
504                     u.setClientOrg (imp.getAD_Client_ID (), imp.getAD_Org_ID ());
505                     if (imp.getContactName () == null)
506                         u.setName (imp.getName ());
507                     else
508                         u.setName (imp.getContactName ());
509                     u.setEmail (imp.getEmail ());
510                     u.setPhone (imp.getPhone ());
511                     if (u.save ())
512                         imp.setAD_User_ID (u.getAD_User_ID ());
513                 }
514                 imp.save ();
515             } //    for all new BPartners
516             rs.close ();
517             pstmt.close ();
518             //
519         }
520         catch (SQLException e)
521         {
522             log.error ("doIt - CreateBP", e);
523         }
524         sql = new StringBuffer   ("UPDATE I_Order "
525               + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No BPartner, ' "
526               + "WHERE C_BPartner_ID IS NULL"
527               + " AND I_IsImported<>'Y'").append (clientCheck);
528         no = DB.executeUpdate (sql.toString ());
529         if (no != 0)
530             log.warn ("doIt - No BPartner=" + no);
531 
532         //  -- New Orders -----------------------------------------------------
533 
534         int noInsert = 0;
535         int noInsertLine = 0;
536 
537         //  Go through Order Records w/o
538         sql = new StringBuffer   ("SELECT * FROM I_Order "
539               + "WHERE I_IsImported='N'").append (clientCheck)
540             .append(" ORDER BY C_BPartner_ID, BillTo_ID, C_BPartner_Location_ID, I_Order_ID");
541         try
542         {
543             PreparedStatement pstmt = DB.prepareStatement (sql.toString ());
544             ResultSet rs = pstmt.executeQuery ();
545             //
546             int C_BPartner_ID = 0;
547             int BillTo_ID = 0;
548             int C_BPartner_Location_ID = 0;
549             MOrder order = null;
550             int lineNo = 0;
551             while (rs.next ())
552             {
553                 X_I_Order imp = new X_I_Order (getCtx (), rs);
554                 //  New Order
555                 if (C_BPartner_ID != imp.getC_BPartner_ID() || BillTo_ID != imp.getBillTo_ID() || C_BPartner_Location_ID != imp.getC_BPartner_Location_ID())
556                 {
557                     if (order != null)
558                         order.process(m_docAction);
559                     C_BPartner_ID = imp.getC_BPartner_ID();
560                     BillTo_ID = imp.getBillTo_ID();
561                     C_BPartner_Location_ID = imp.getC_BPartner_Location_ID();
562                     order = new MOrder (getCtx(), 0);
563                     order.setClientOrg (imp.getAD_Client_ID(), imp.getAD_Org_ID());
564                     order.setC_DocTypeTarget_ID(imp.getC_DocType_ID());
565                     order.setIsSOTrx(imp.isSOTrx());
566                     if (imp.getDocumentNo() != null)
567                         order.setDocumentNo(imp.getDocumentNo());
568                     //
569                     order.setC_BPartner_ID(imp.getC_BPartner_ID());
570                     order.setBillTo_ID(imp.getBillTo_ID());
571                     order.setC_BPartner_Location_ID(imp.getC_BPartner_Location_ID());
572                     if (imp.getAD_User_ID() != 0)
573                         order.setAD_User_ID(imp.getAD_User_ID());
574                     //
575                     if (imp.getDescription() != null)
576                         order.setDescription(imp.getDescription());
577                     order.setC_PaymentTerm_ID(imp.getC_PaymentTerm_ID());
578                     order.setM_PriceList_ID(imp.getM_PriceList_ID());
579                     order.setM_Warehouse_ID(imp.getM_Warehouse_ID());
580                     if (imp.getM_Shipper_ID() != 0)
581                         order.setM_Shipper_ID(imp.getM_Shipper_ID());
582                     if (imp.getSalesRep_ID() != 0)
583                         order.setSalesRep_ID(imp.getSalesRep_ID());
584                     //
585                     if (imp.getAD_OrgTrx_ID() != 0)
586                         order.setAD_OrgTrx_ID(imp.getAD_OrgTrx_ID());
587                     if (imp.getC_Activity_ID() != 0)
588                         order.setC_Activity_ID(imp.getC_Activity_ID());
589                     if (imp.getC_Campaign_ID() != 0)
590                         order.setC_Campaign_ID(imp.getC_Campaign_ID());
591                     if (imp.getC_Project_ID() != 0)
592                         order.setC_Project_ID(imp.getC_Project_ID());
593                     //
594                     order.save();
595                     noInsert++;
596                     lineNo = 10;
597                 }
598                 imp.setC_Order_ID(order.getC_Order_ID());
599                 //  New OrderLine
600                 MOrderLine line = new MOrderLine (order);
601                 line.setLine(lineNo);
602                 lineNo += 10;
603                 if (imp.getM_Product_ID() != 0)
604                     line.setM_Product_ID(imp.getM_Product_ID());
605                 line.setQtyOrdered(imp.getQtyOrdered());
606                 line.setPrice();
607                 if (imp.getPriceActual().compareTo(Env.ZERO) != 0)
608                     line.setPriceActual(imp.getPriceActual());
609                 if (imp.getC_Tax_ID() != 0)
610                     line.setC_Tax_ID(imp.getC_Tax_ID());
611                 else
612                 {
613                     line.setTax();
614                     imp.setC_Tax_ID(line.getC_Tax_ID());
615                 }
616                 if (imp.getFreightAmt() != null)
617                     line.setFreightAmt(imp.getFreightAmt());
618                 line.save();
619                 imp.setC_OrderLine_ID(line.getC_OrderLine_ID());
620                 imp.setI_IsImported(true);
621                 imp.setProcessed(true);
622                 //
623                 if (imp.save())
624                     noInsertLine++;
625             }
626             if (order != null)
627                 order.process (m_docAction);
628             rs.close();
629             pstmt.close();
630         }
631         catch (Exception   e)
632         {
633             log.error ("doIt - CreateOrder", e);
634         }
635 
636         //  Set Error to indicator to not imported
637         sql = new StringBuffer   ("UPDATE I_Order "
638             + "SET I_IsImported='N', Updated=SysDate "
639             + "WHERE I_IsImported<>'Y'").append(clientCheck);
640         no = DB.executeUpdate(sql.toString());
641         addLog (0, null, new BigDecimal (no), "@Errors@");
642         //
643         addLog (0, null, new BigDecimal (noInsert), "@C_Order_ID@: @Inserted@");
644         addLog (0, null, new BigDecimal (noInsertLine), "@C_OrderLine_ID@: @Inserted@");
645         return "";
646     }   //  doIt
647 
648 }   //  ImportOrder
649
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags