ImpFormat


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-2001 Jorg Janke, parts
11   * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
12   * Contributor(s): ______________________________________.
13   *****************************************************************************/
14  package org.compiere.impexp;
15  
16  import java.util.*;
17  import java.sql.*;
18  
19  import org.compiere.util.*;
20  
21  /**
22   *  Import Format a Row
23   *
24   *  @author Jorg Janke
25   *  @version $Id: ImpFormat.java,v 1.11 2003/01/20 05:39:20 jjanke Exp $
26   */
27  public final class ImpFormat
28  {
29      /**
30       *  Format
31       *  @param name name
32       *  @param AD_Table_ID table
33       *  @param formatType format type
34       */
35      public ImpFormat (String   name, int AD_Table_ID, String   formatType)
36      {
37          setName(name);
38          setTable(AD_Table_ID);
39          setFormatType(formatType);
40      }   //  ImpFormat
41  
42      private String        m_name;
43      private String        m_formatType;
44  
45      /** The Table to be imported        */
46      private int         m_AD_Table_ID;
47      private String        m_tableName;
48      private String        m_tablePK;
49      private String        m_tableUnique1;
50      private String        m_tableUnique2;
51      private String        m_tableUniqueParent;
52      private String        m_tableUniqueChild;
53      //
54      private String        m_BPartner;
55      private ArrayList   m_rows  = new ArrayList();
56  
57      /**
58       *  Set Name
59       *  @param newName new name
60       */
61      public void setName(String   newName)
62      {
63          if (newName == null || newName.length() == 0)
64              throw new IllegalArgumentException  ("Name must be at least 1 char");
65          else
66              m_name = newName;
67      }
68  
69      /**
70       *  Get Name
71       *  @return name
72       */
73      public String   getName()
74      {
75          return m_name;
76      }   //  getName
77  
78      /**
79       *  Import Table
80       *  @param AD_Table_ID table
81       */
82      public void setTable (int AD_Table_ID)
83      {
84          m_AD_Table_ID = AD_Table_ID;
85          m_tableName = null;
86          m_tablePK = null;
87          String   sql = "SELECT t.TableName,c.ColumnName "
88              + "FROM AD_Table t INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID AND c.IsKey='Y') "
89              + "WHERE t.AD_Table_ID=?";
90          try
91          {
92              PreparedStatement pstmt = DB.prepareStatement(sql);
93              pstmt.setInt(1, AD_Table_ID);
94              ResultSet rs = pstmt.executeQuery();
95              if (rs.next())
96              {
97                  m_tableName = rs.getString(1);
98                  m_tablePK = rs.getString(2);
99              }
100             rs.close();
101             pstmt.close();
102         }
103         catch (SQLException e)
104         {
105             Log.error("ImpFormat.setTable", e);
106         }
107         if (m_tableName == null || m_tablePK == null)
108             Log.error("ImpFormat.setTable - Data not forund for AD_Table_ID=" + AD_Table_ID);
109 
110         //  Set Additional Table Info
111         m_tableUnique1 = "";
112         m_tableUnique2 = "";
113         m_tableUniqueParent = "";
114         m_tableUniqueChild = "";
115 
116         if (m_AD_Table_ID == 311)           //  I_061_SyncItem
117         {
118             m_tableUnique1 = "H_UPC";                   //  UPC = unique
119             m_tableUnique2 = "Value";
120             m_tableUniqueChild = "H_Commodity1";        //  Vendor No may not be unique !
121             m_tableUniqueParent = "H_PartnrID";         //          Makes it unique
122         }
123         else if (m_AD_Table_ID == 532)      //  I_Product
124         {
125             m_tableUnique1 = "UPC";                     //  UPC = unique
126             m_tableUnique2 = "Value";
127             m_tableUniqueChild = "VendorProductNo";     //  Vendor No may not be unique !
128             m_tableUniqueParent = "BPartner_Value";     //          Makes it unique
129         }
130         else if (m_AD_Table_ID == 533)      //  I_BPartner
131         {
132             m_tableUnique1 = "Value";               //  the key
133         }
134         else if (m_AD_Table_ID == 534)      //  I_ElementValue
135         {
136             m_tableUniqueParent = "ElementName";            //  the parent key
137             m_tableUniqueChild = "Value";                   //  the key
138         }
139         else if (m_AD_Table_ID == 535)      //  I_ReportLine
140         {
141             m_tableUniqueParent = "ReportLineSetName";      //  the parent key
142             m_tableUniqueChild = "Name";                    //  the key
143         }
144     }   //  setTable
145 
146     /**
147      *  Get Import Table Name
148      *  @return AD_Table_ID
149      */
150     public int getAD_Table_ID()
151     {
152         return m_AD_Table_ID;
153     }   //  getAD_Table_ID
154 
155     /**
156      *  Format Type
157      */
158     public static final String    FORMATTYPE_FIXED = "F";
159     public static final String    FORMATTYPE_COMMA = "C";
160     public static final String    FORMATTYPE_TAB = "T";
161     public static final String    FORMATTYPE_XML = "X";
162 
163     /**
164      *  Set Format Type
165      *  @param newFormatType - F/C/T/X
166      */
167     public void setFormatType(String   newFormatType)
168     {
169         if (newFormatType.equals(FORMATTYPE_FIXED) || newFormatType.equals(FORMATTYPE_COMMA)
170             || newFormatType.equals(FORMATTYPE_TAB) || newFormatType.equals(FORMATTYPE_XML))
171             m_formatType = newFormatType;
172         else
173             throw new IllegalArgumentException  ("FormatType must be F/C/T/X");
174     }   //  setFormatType
175 
176     /**
177      *  Set Format Type
178      *  @return format type  - F/C/T/X
179      */
180     public String   getFormatType()
181     {
182         return m_formatType;
183     }   //  getFormatType
184 
185     /**
186      *  Set Business Partner
187      *  @param newBPartner (value)
188      */
189     public void setBPartner(String   newBPartner)
190     {
191         m_BPartner = newBPartner;
192     }   //  setBPartner
193 
194     /**
195      *  Get Business Partner
196      *  @return BPartner (value)
197      */
198     public String   getBPartner()
199     {
200         return m_BPartner;
201     }   //  getVPartner
202 
203     /*************************************************************************/
204 
205     /**
206      *  Add Format Row
207      *  @param row row
208      */
209     public void addRow (ImpFormatRow row)
210     {
211         m_rows.add (row);
212     }   //  addRow
213 
214     /**
215      *  Get Row
216      *  @param index index
217      *  @return Import Format Row
218      */
219     public ImpFormatRow getRow (int index)
220     {
221         if (index >=0 && index < m_rows.size())
222             return (ImpFormatRow)m_rows.get(index);
223         return null;
224     }   //  getRow
225 
226     /**
227      *  Get Row Count
228      *  @return row count
229      */
230     public int getRowCount()
231     {
232         return m_rows.size();
233     }   //  getRowCount
234 
235     /*************************************************************************/
236 
237     /**
238      *  Factory load
239      *  @param name name
240      *  @return Import Format
241      */
242     public static ImpFormat load (String   name)
243     {
244         Log.trace(Log.l3_Util, "ImpFormat.load - " + name);
245         ImpFormat retValue = null;
246         String   SQL = "SELECT * FROM AD_ImpFormat WHERE Name=?";
247         int ID = 0;
248         try
249         {
250             PreparedStatement pstmt = DB.prepareStatement(SQL);
251             pstmt.setString (1, name);
252             ResultSet rs = pstmt.executeQuery();
253             if (rs.next())
254             {
255                 retValue = new ImpFormat (name, rs.getInt("AD_Table_ID"), rs.getString("FormatType"));
256                 ID = rs.getInt ("AD_ImpFormat_ID");
257             }
258             rs.close();
259             pstmt.close();
260         }
261         catch (SQLException e)
262         {
263             Log.error ("ImpFormat.load", e);
264             return null;
265         }
266         loadRows (retValue, ID);
267         return retValue;
268     }   //  getFormat
269 
270     /**
271      *  Load Format Rows with ID
272      *  @param format format
273      *  @param ID id
274      */
275     private static void loadRows (ImpFormat format, int ID)
276     {
277         String   SQL = "SELECT f.SeqNo,c.ColumnName,f.StartNo,f.EndNo,f.DataType,c.FieldLength,"      //  1..6
278             + "f.DataFormat,f.DecimalPoint,f.DivideBy100,f.ConstantValue,f.Callout "                //  7..11
279             + "FROM AD_ImpFormat_Row f,AD_Column c "
280             + "WHERE AD_ImpFormat_ID=? AND f.AD_Column_ID=c.AD_Column_ID "
281             + "ORDER BY SeqNo";
282         try
283         {
284             PreparedStatement pstmt = DB.prepareStatement(SQL);
285             pstmt.setInt (1, ID);
286             ResultSet rs = pstmt.executeQuery();
287             while (rs.next())
288             {
289                 ImpFormatRow row = new ImpFormatRow (rs.getInt(1),
290                     rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getInt(6));
291                 //
292                 row.setFormatInfo(rs.getString(7), rs.getString(8),
293                     rs.getString(9).equals("Y"),
294                     rs.getString(10), rs.getString(11));
295                 //
296                 format.addRow (row);
297             }
298             rs.close();
299             pstmt.close();
300         }
301         catch (SQLException e)
302         {
303             Log.error ("ImpFormat.loadRows", e);
304         }
305     }   //  loadLines
306 
307     /*************************************************************************/
308 
309     /**
310      *  Parse Line returns ArrayList of values
311      *
312      *  @param line line
313      *  @param withLabel true if with label
314      *  @param trace create trace info
315      *  @param ignoreEmpty - ignore empty fields
316      *  @return Array of values
317      */
318     public String  [] parseLine (String   line, boolean withLabel, boolean trace, boolean ignoreEmpty)
319     {
320         if (trace)
321             Log.trace(Log.l4_Data, "ImpFormat.parseLine - " + line);
322 
323         ArrayList list = new ArrayList();
324         //  for all columns
325         for (int i = 0; i < m_rows.size(); i++)
326         {
327             ImpFormatRow row = (ImpFormatRow)m_rows.get(i);
328             StringBuffer   entry = new StringBuffer   ();
329             //  Label-Start
330             if (withLabel)
331             {
332                 entry.append(row.getColumnName());
333                 entry.append("=");
334                 if (row.isString())
335                     entry.append("'");
336                 else if (row.isDate())
337                     entry.append("TO_DATE('");
338             }
339 
340             //  Get Data
341             String   info = null;
342             if (row.isConstant())
343                 info = "Constant";
344             else if (m_formatType.equals(FORMATTYPE_FIXED))
345             {
346                 //  check length
347                 if (row.getStartNo() > 0 && row.getEndNo() <= line.length())
348                     info = line.substring(row.getStartNo()-1, row.getEndNo());
349             }
350             else
351             {
352                 info = parseFlexFormat (line, m_formatType, row.getStartNo());
353             }
354 
355             if (info == null)
356                 info = "";
357 
358             //  Interpret Data
359             entry.append(row.parse(info));
360 
361             //  Label-End
362             if (withLabel)
363             {
364                 if (row.isString())
365                     entry.append("'");
366                 else if (row.isDate())
367                     entry.append("','YYYY-MM-DD HH24:MI:SS')");     //  JDBC Timestamp format w/o miliseconds
368             }
369 
370             if (!ignoreEmpty || (ignoreEmpty && info.length() != 0))
371                 list.add(entry.toString());
372             //
373             if (trace)
374                 Log.trace(Log.l5_DData, info + "=>" + entry.toString() + " (Length=" + info.length() + ")");
375         }   //  for all columns
376 
377         String  [] retValue = new String  [list.size()];
378         list.toArray(retValue);
379         return retValue;
380     }   //  parseLine
381 
382     /**
383      *  Parse flexible line format.
384      *  A bit inefficient as it always starts from the start
385      *
386      *  @param line the line to be parsed
387      *  @param formatType Comma or Tab
388      *  @param fieldNo number of field to be returned
389      *  @throws IllegalArgumentException if format unknows
390      *  @return field in lime or ""
391      */
392     private String   parseFlexFormat (String   line, String   formatType, int fieldNo)
393     {
394         final char QUOTE = '"';
395         //  check input
396         char delimiter = ' ';
397         if (formatType.equals(FORMATTYPE_COMMA))
398             delimiter = ',';
399         else if (formatType.equals(FORMATTYPE_TAB))
400             delimiter = '\t';
401         else
402             throw new IllegalArgumentException   ("ImpFormat.parseFlexFormat - unknown format: " + formatType);
403         if (line == null || line.length() == 0 || fieldNo < 0)
404             return "";
405 
406         //  We need to read line sequentially as the fields may be delimited
407         //  with quotes (") when fields contain the delimiter
408         //  Example:    "Artikel,bez","Artikel,""nr""",DEM,EUR
409         //  needs to result in - Artikel,bez - Artikel,"nr" - DEM - EUR
410         int pos = 0;
411         int length = line.length();
412         for (int field = 1; field <= fieldNo && pos < length; field++)
413         {
414             StringBuffer   content = new StringBuffer  ();
415             //  two delimiter directly after each other
416             if (line.charAt(pos) == delimiter)
417             {
418                 pos++;
419                 continue;
420             }
421             //  Handle quotes
422             if (line.charAt(pos) == QUOTE)
423             {
424                 pos++;  //  move over beginning quote
425                 while (pos < length)
426                 {
427                     //  double quote
428                     if (line.charAt(pos) == QUOTE && pos+1 < length && line.charAt(pos+1) == QUOTE)
429                     {
430                         content.append(line.charAt(pos++));
431                         pos++;
432                     }
433                     //  end quote
434                     else if (line.charAt(pos) == QUOTE)
435                     {
436                         pos++;
437                         break;
438                     }
439                     //  normal character
440                     else
441                         content.append(line.charAt(pos++));
442                 }
443                 //  we should be at end of line or a delimiter
444                 if (pos < length && line.charAt(pos) != delimiter)
445                     Log.trace(Log.l1_User, "ImpFormat.parseFlexFormat - Did not find delimiter at pos " + pos, line);
446                 pos++;  //  move over delimiter
447             }
448             else // plain copy
449             {
450                 while (pos < length && line.charAt(pos) != delimiter)
451                     content.append(line.charAt(pos++));
452                 pos++;  //  move over delimiter
453             }
454             if (field == fieldNo)
455                 return content.toString();
456         }
457 
458         //  nothing found
459         return "";
460     }   //  parseFlexFormat
461 
462     /*************************************************************************/
463 
464     /**
465      *  Insert/Update Database.
466      *  @param ctx context
467      *  @param line line
468      *  @return true if inserted/updated
469      */
470     public boolean updateDB (Properties ctx, String   line)
471     {
472         if (line == null || line.trim().length() == 0)
473         {
474             Log.trace(10, "No Line");
475             return false;
476         }
477         String  [] nodes = parseLine (line, true, false, true);   //  with label, no trace, ignore empty
478         if (nodes.length == 0)
479         {
480             Log.trace(10, "Nothing parsed from: " + line);
481             return false;
482         }
483     //  Log.trace(Log.l4_Data, "ImpFormat.updateDB - listSize=" + nodes.length);
484 
485         //  Standard Fields
486         int AD_Client_ID = Env.getContextAsInt(ctx, "#AD_Client_ID");
487         int AD_Org_ID = Env.getContextAsInt(ctx, "#AD_Org_ID");
488         int UpdatedBy = Env.getContextAsInt(ctx, "#AD_User_ID");
489 
490 
491         //  Check if the record is already there ------------------------------
492         StringBuffer   sql = new StringBuffer   ("SELECT COUNT(*), MAX(")
493             .append(m_tablePK).append(") FROM ").append(m_tableName)
494             .append(" WHERE AD_Client_ID=").append(AD_Client_ID).append(" AND (");
495         //
496         String   where1 = null;
497         String   where2 = null;
498         String   whereParentChild = null;
499         for (int i = 0; i < nodes.length; i++)
500         {
501             if (nodes[i].endsWith("=''") || nodes[i].endsWith("=0"))
502                 ;
503             else if (nodes[i].startsWith(m_tableUnique1 + "="))
504                 where1 = nodes[i];
505             else if (nodes[i].startsWith(m_tableUnique2 + "="))
506                 where2 = nodes[i];
507             else if (nodes[i].startsWith(m_tableUniqueParent + "=") || nodes[i].startsWith(m_tableUniqueChild + "="))
508             {
509                 if (whereParentChild == null)
510                     whereParentChild = nodes[i];
511                 else
512                     whereParentChild += " AND " + nodes[i];
513             }
514         }
515         StringBuffer   find = new StringBuffer  ();
516         if (where1 != null)
517             find.append(where1);
518         if (where2 != null)
519         {
520             if (find.length() > 0)
521                 find.append(" OR ");
522             find.append(where2);
523         }
524         if (whereParentChild != null && whereParentChild.indexOf(" AND ") != -1)    //  need to have both criteria
525         {
526             if (find.length() > 0)
527                 find.append(" OR (").append(whereParentChild).append(")");  //  may have only one
528             else
529                 find.append(whereParentChild);
530         }
531         sql.append(find).append(")");
532         int count = 0;
533         int ID = 0;
534         try
535         {
536             if (find.length() > 0)
537             {
538                 PreparedStatement pstmt = DB.prepareStatement(sql.toString());
539                 ResultSet rs = pstmt.executeQuery();
540                 if (rs.next())
541                 {
542                     count = rs.getInt(1);
543                     if (count == 1)
544                         ID = rs.getInt(2);
545                 }
546                 rs.close();
547                 pstmt.close();
548             }
549         }
550         catch (SQLException e)
551         {
552             Log.error("ImpFormat.updateDB - " + sql.toString(), e);
553             return false;
554         }
555 
556 
557         //  Insert Basic Record -----------------------------------------------
558         if (ID == 0)
559         {
560             ID = DB.getKeyNextNo(ctx, 0, m_tableName);      //  get ID
561             sql = new StringBuffer  ("INSERT INTO ")
562                 .append(m_tableName).append("(").append(m_tablePK).append(",")
563                 .append("AD_Client_ID,AD_Org_ID,Created,CreatedBy,Updated,UpdatedBy,IsActive")  //  StdFields
564                 .append(") VALUES (").append(ID).append(",")
565                 .append(AD_Client_ID).append(",").append(AD_Org_ID).append(",SysDate,").append(UpdatedBy)
566                 .append(",SysDate,").append(UpdatedBy).append(",'Y'")
567                 .append(")");
568             //
569             int no = DB.executeUpdate(sql.toString());
570             if (no != 1)
571             {
572                 Log.error("ImpFormat.updateDB - Insert records=" + no + "; SQL=" + sql.toString());
573                 return false;
574             }
575             Log.trace(Log.l6_Database, "New ID=" + ID, find);
576         }
577         else
578             Log.trace(Log.l6_Database, "Old ID=" + ID, find);
579 
580         //  Update Info -------------------------------------------------------
581         sql = new StringBuffer   ("UPDATE ")
582             .append(m_tableName).append(" SET ");
583         for (int i = 0; i < nodes.length; i++)
584             sql.append(nodes[i]).append(",");       //  column=value
585         sql.append("IsActive='Y',Processed='N',I_IsImported='N',Updated=SysDate,UpdatedBy=").append(UpdatedBy);
586         sql.append(" WHERE ").append(m_tablePK).append("=").append(ID);
587         //  Update Cmd
588         int no = DB.executeUpdate(sql.toString());
589         if (no != 1)
590         {
591             Log.error("ImpFormat.updateDB - ID=" + ID + " - rows updated=" + no);
592             return true;
593         }
594         return true;
595     }   //  updateDB
596 
597 }   //  ImpFormat
598
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags