KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > knowgate > hipergate > ProductLoader


1 /*
2   Copyright (C) 2005 Know Gate S.L. All rights reserved.
3                       C/Oņa, 107 1š2 28050 Madrid (Spain)
4
5   Redistribution and use in source and binary forms, with or without
6   modification, are permitted provided that the following conditions
7   are met:
8
9   1. Redistributions of source code must retain the above copyright
10      notice, this list of conditions and the following disclaimer.
11
12   2. The end-user documentation included with the redistribution,
13      if any, must include the following acknowledgment:
14      "This product includes software parts from hipergate
15      (http://www.hipergate.org/)."
16      Alternately, this acknowledgment may appear in the software itself,
17      if and wherever such third-party acknowledgments normally appear.
18
19   3. The name hipergate must not be used to endorse or promote products
20      derived from this software without prior written permission.
21      Products derived from this software may not be called hipergate,
22      nor may hipergate appear in their name, without prior written
23      permission.
24
25   This library is distributed in the hope that it will be useful,
26   but WITHOUT ANY WARRANTY; without even the implied warranty of
27   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
28
29   You should have received a copy of hipergate License with this code;
30   if not, visit http://www.hipergate.org or mail to info@hipergate.org
31 */

32
33 package com.knowgate.hipergate;
34
35 import java.util.Date JavaDoc;
36 import java.util.Iterator JavaDoc;
37 import java.util.Map JavaDoc;
38 import java.util.HashMap JavaDoc;
39 import java.util.Arrays JavaDoc;
40
41 import java.sql.Connection JavaDoc;
42 import java.sql.SQLException JavaDoc;
43 import java.sql.PreparedStatement JavaDoc;
44 import java.sql.ResultSet JavaDoc;
45 import java.sql.Timestamp JavaDoc;
46 import java.sql.Types JavaDoc;
47
48 import com.knowgate.debug.DebugFile;
49 import com.knowgate.dataobjs.DB;
50 import com.knowgate.misc.Gadgets;
51 import com.knowgate.hipergate.Address;
52 import com.knowgate.hipergate.DBLanguages;
53 import com.knowgate.hipergate.Product;
54 import com.knowgate.hipergate.datamodel.ColumnList;
55 import com.knowgate.hipergate.datamodel.ImportLoader;
56
57 /**
58  * <p>Load Product data from a single source</p>
59  * Product loader creates or updates simultaneously registers at k_companies,
60  * k_contacts and k_addresses tables and the links between them k_x_contact_addr.
61  * <br><br>
62  * @author Sergio Montoro Ten
63  * @version 1.0
64  */

65 public class ProductLoader implements ImportLoader {
66
67   private Object JavaDoc[] aValues;
68
69   private PreparedStatement JavaDoc oProdUpdt, oFareUpdt, oLocaUpdt, oAttrUpdt,
70       oKeysDlte, oAddrUpdt;
71   private PreparedStatement JavaDoc oProdInsr, oFareInsr, oLocaInsr, oAttrInsr,
72       oKeysInsr, oAddrInsr;
73   private PreparedStatement JavaDoc oCatgDlte, oCatgInsr;
74   private String JavaDoc sLastCategoryGuid, sLastCategoryName, sLastOwnGuid;
75
76   // ---------------------------------------------------------------------------
77

78   private void init() {
79     aValues = new Object JavaDoc[ColumnNames.length];
80     for (int c = aValues.length - 1; c >= 0; c--) aValues[c] = null;
81     oProdUpdt = oFareUpdt = oLocaUpdt = oAttrUpdt = oAddrUpdt = null;
82     oProdInsr = oFareInsr = oLocaInsr = oAttrInsr = oAddrInsr = null;
83     sLastCategoryGuid = sLastCategoryName = sLastOwnGuid = "";
84   }
85
86   // ---------------------------------------------------------------------------
87

88   public ProductLoader() {
89     init();
90   }
91
92   // ---------------------------------------------------------------------------
93

94   /**
95    * Create ProductLoader and call prepare() on Connection
96    * @param oConn Connection
97    * @throws SQLException
98    */

99   public ProductLoader(Connection JavaDoc oConn) throws SQLException JavaDoc {
100     init();
101     prepare(oConn, null);
102   }
103
104   // ---------------------------------------------------------------------------
105

106   /**
107    * <p>Prepare statements for execution</p>
108    * This method needs to be called only once if the default constructor was used.<br>
109    * If ProductLoader(Connection) constructor was used, there is no need to call prepare()
110    * and a SQLException will be raised if the attempt is made.<br>
111    * It is neccesary to call close() always for prepared instances as a failure
112    * to do so will leave open cursors on the database causing it eventually to stop.
113    * @param oConn Connection Open JDBC database connection
114    * @param oColList ColumnList This parameter is ignored
115    * @throws SQLException
116    */

117   public void prepare(Connection JavaDoc oConn, ColumnList oColList) throws
118       SQLException JavaDoc {
119
120     if (DebugFile.trace) {
121       DebugFile.writeln("Begin ProductLoader.prepare()");
122       DebugFile.incIdent();
123     }
124
125     if (oProdUpdt != null || oFareUpdt != null || oAttrUpdt != null ||
126         oAddrUpdt != null) {
127       if (DebugFile.trace) DebugFile.decIdent();
128       throw new SQLException JavaDoc("Either ProductLoader.prepare() has already been called or statements were not properly closed",
129                              "HY010");
130     }
131
132     oProdUpdt = oConn.prepareStatement("UPDATE " + DB.k_products + " SET gu_owner=?,nm_product=?,id_status=?,is_compound=?,gu_blockedby=?,dt_modified=?,dt_uploaded=?,id_language=?,de_product=?,pr_list=?,pr_sale=?,pr_discount=?,pr_purchase=?,id_currency=?,pct_tax_rate=?,is_tax_included=?,dt_start=?,dt_end=?,tag_product=?,id_ref=?,gu_address=? WHERE gu_product=?");
133     oProdInsr = oConn.prepareStatement("INSERT INTO " + DB.k_products + " (gu_owner,nm_product,id_status,is_compound,gu_blockedby,dt_modified,dt_uploaded,id_language,de_product,pr_list,pr_sale,pr_discount,pr_purchase,id_currency,pct_tax_rate,is_tax_included,dt_start,dt_end,tag_product,id_ref,gu_address,gu_product) VALUES (?,?,?,?,?,NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
134     oFareInsr = oConn.prepareStatement("INSERT INTO " + DB.k_prod_fares + " (pr_sale,tp_fare,id_currency,pct_tax_rate,is_tax_included,dt_start,dt_end,gu_product,id_fare) VALUES (?,?,?,?,?,?,?,?,?)");
135     oFareUpdt = oConn.prepareStatement("UPDATE " + DB.k_prod_fares + " SET pr_sale=?,tp_fare=?,id_currency=?,pct_tax_rate=?,is_tax_included=?,dt_start=?,dt_end=? WHERE gu_product=? AND id_fare=?");
136     oLocaInsr = oConn.prepareStatement("INSERT INTO " + DB.k_prod_locats + " (gu_location,gu_owner,pg_prod_locat,id_cont_type,id_prod_type,len_file,xprotocol,xhost,xport,xpath,xfile,xanchor,xoriginalfile,dt_modified,dt_uploaded,de_prod_locat,status,nu_current_stock,nu_reserved_stock,nu_min_stock,vs_stamp,tx_email,tag_prod_locat,gu_product) VALUES(?,?,1,?,?,?,?,?,?,?,?,?,?,NULL,?,?,?,?,?,?,?,?,?,?)");
137     oLocaUpdt = oConn.prepareStatement("UPDATE " + DB.k_prod_locats + " SET gu_owner=?,id_cont_type=?,id_prod_type=?,len_file=?,xprotocol=?,xhost=?,xport=?,xpath=?,xfile=?,xanchor=?,xoriginalfile=?,dt_modified=?,dt_uploaded=?,de_prod_locat=?,status=?,nu_current_stock=?,nu_reserved_stock=?,nu_min_stock=?,vs_stamp=?,tx_email=?,tag_prod_locat=? WHERE gu_product=?");
138     oAttrInsr = oConn.prepareStatement("INSERT INTO " + DB.k_prod_attr + " (adult_rated,alturl,author,availability,brand,client,color,contact_person,country_code,country,cover,days_to_deliver,department,disk_space,display,doc_no,dt_acknowledge,dt_expire,dt_out,email,fax,forward_to,icq_id,ip_addr,isbn,nu_lines,memory,mobilephone,office,ordinal,organization,pages,paragraphs,phone1,phone2,power,project,product_group,rank,reference_id,revised_by,rooms,scope,signature,size_x,size_y,size_z,speed,state_code,state,subject,target,template,typeof,upload_by,weight,words,zip_code,gu_product) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
139     oAttrUpdt = oConn.prepareStatement("UPDATE " + DB.k_prod_attr + " SET adult_rated=?,alturl=?,author=?,availability=?,brand=?,client=?,color=?,contact_person=?,country_code=?,country=?,cover=?,days_to_deliver=?,department=?,disk_space=?,display=?,doc_no=?,dt_acknowledge=?,dt_expire=?,dt_out=?,email=?,fax=?,forward_to=?,icq_id=?,ip_addr=?,isbn=?,nu_lines=?,memory=?,mobilephone=?,office=?,ordinal=?,organization=?,pages=?,paragraphs=?,phone1=?,phone2=?,power=?,project=?,product_group=?,rank=?,reference_id=?,revised_by=?,rooms=?,scope=?,signature=?,size_x=?,size_y=?,size_z=?,speed=?,state_code=?,state=?,subject=?,target=?,template=?,typeof=?,upload_by=?,weight=?,words=?,zip_code=? WHERE gu_product=?");
140     oKeysInsr = oConn.prepareStatement("INSERT INTO " + DB.k_prod_keywords +
141                                        " (gu_product,tx_keywords) VALUES (?,?)");
142     oKeysDlte = oConn.prepareStatement("DELETE FROM " + DB.k_prod_keywords +
143                                        " WHERE gu_product=?");
144     oAddrUpdt = oConn.prepareStatement("UPDATE " + DB.k_addresses + " SET dt_modified=?,tp_location=?,nm_company=?,tp_street=?,nm_street=?,nu_street=?,tx_addr1=?,tx_addr2=?,id_country=?,nm_country=?,id_state=?,nm_state=?,mn_city=?,zipcode=?,work_phone=?,direct_phone=?,home_phone=?,mov_phone=?,fax_phone=?,other_phone=?,po_box=?,tx_email=?,tx_email_alt=?,url_addr=?,coord_x=?,coord_y=?,contact_person=?,tx_salutation=?,tx_remarks=? WHERE gu_address=?");
145     oAddrInsr = oConn.prepareStatement("INSERT INTO " + DB.k_addresses + " (gu_address,ix_address,gu_workarea,bo_active,dt_modified,tp_location,nm_company,tp_street,nm_street,nu_street,tx_addr1,tx_addr2,id_country,nm_country,id_state,nm_state,mn_city,zipcode,work_phone,direct_phone,home_phone,mov_phone,fax_phone,other_phone,po_box,tx_email,tx_email_alt,url_addr,coord_x,coord_y,contact_person,tx_salutation,tx_remarks) VALUES (?,1,?,1,NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
146     oCatgInsr = oConn.prepareStatement("INSERT INTO " + DB.k_x_cat_objs +
147         " (gu_category,gu_object,id_class,bi_attribs,od_position) VALUES (?,?,15,0,?)");
148     oCatgDlte = oConn.prepareStatement("DELETE FROM " + DB.k_x_cat_objs +
149                                        " WHERE gu_object=? AND id_class=15");
150   }
151
152   // ---------------------------------------------------------------------------
153

154   /**
155    * <p>Close prepared statements</p>
156    * This method must always be called before object is destroyed or else
157    * cursors may be left open at the database
158    * @throws SQLException
159    */

160   public void close() throws SQLException JavaDoc {
161     if (null != oProdUpdt) {
162       oProdUpdt.close();
163       oProdUpdt = null;
164     }
165     if (null != oProdInsr) {
166       oProdInsr.close();
167       oProdInsr = null;
168     }
169     if (null != oFareInsr) {
170       oFareInsr.close();
171       oFareInsr = null;
172     }
173     if (null != oFareUpdt) {
174       oFareUpdt.close();
175       oFareUpdt = null;
176     }
177     if (null != oLocaInsr) {
178       oLocaInsr.close();
179       oLocaInsr = null;
180     }
181     if (null != oLocaUpdt) {
182       oLocaUpdt.close();
183       oLocaUpdt = null;
184     }
185     if (null != oAttrInsr) {
186       oAttrInsr.close();
187       oAttrInsr = null;
188     }
189     if (null != oAttrUpdt) {
190       oAttrUpdt.close();
191       oAttrUpdt = null;
192     }
193     if (null != oKeysInsr) {
194       oKeysInsr.close();
195       oKeysInsr = null;
196     }
197     if (null != oKeysDlte) {
198       oKeysDlte.close();
199       oKeysDlte = null;
200     }
201     if (null != oAddrUpdt) {
202       oAddrUpdt.close();
203       oAddrUpdt = null;
204     }
205     if (null != oAddrInsr) {
206       oAddrInsr.close();
207       oAddrInsr = null;
208     }
209     if (null != oCatgInsr) {
210       oCatgInsr.close();
211       oCatgInsr = null;
212     }
213     if (null != oCatgDlte) {
214       oCatgDlte.close();
215       oCatgDlte = null;
216     }
217   }
218
219   // ---------------------------------------------------------------------------
220

221   /**
222    * Set all column values to null
223    */

224   public void setAllColumnsToNull() {
225     if (DebugFile.trace) {
226       DebugFile.writeln("Begin ProductLoader.setAllColumnsToNull()");
227       DebugFile.incIdent();
228     }
229
230     for (int c = aValues.length - 1; c >= 0; c--)
231       aValues[c] = null;
232
233     if (DebugFile.trace) {
234       DebugFile.decIdent();
235       DebugFile.writeln("End ProductLoader.setAllColumnsToNull()");
236     }
237   } // setAllColumnsToNull
238

239   // ---------------------------------------------------------------------------
240

241   /**
242    * <p>Get column index at ColumnNames array given its name</p>
243    * This method performs binary search assuming that ColumnNames is sorted in
244    * ascending order
245    * @param sColumnName String Column name (case insensitive)
246    * @return int Column index or -1 if not found
247    */

248   public int getColumnIndex(String JavaDoc sColumnName) {
249     int iIndex = Arrays.binarySearch(ColumnNames, sColumnName,
250                                      String.CASE_INSENSITIVE_ORDER);
251     if (iIndex < 0) iIndex = -1;
252     return iIndex;
253   }
254
255   // ---------------------------------------------------------------------------
256

257   public int columnCount() {
258     return aValues.length;
259   }
260
261   // ---------------------------------------------------------------------------
262

263   public String JavaDoc[] columnNames() throws IllegalStateException JavaDoc {
264     return ColumnNames;
265   }
266
267   // ---------------------------------------------------------------------------
268

269   /**
270    * Put value for a given column
271    * @param iColumnIndex Column index [0..getColumnCount()-1]
272    * @param oValue Value for column
273    * @throws ArrayIndexOutOfBoundsException
274    */

275   public void put(int iColumnIndex, Object JavaDoc oValue) throws
276       ArrayIndexOutOfBoundsException JavaDoc {
277     aValues[iColumnIndex] = oValue;
278   }
279
280   // ---------------------------------------------------------------------------
281

282   /**
283    * <p>Put value for a given column</p>
284    * If a previous value already exists then it is replaced
285    * @param sColumnName Column name (case sensitive)
286    * @param oValue Value for column
287    * @throws ArrayIndexOutOfBoundsException
288    */

289   public void put(String JavaDoc sColumnName, Object JavaDoc oValue) throws
290       ArrayIndexOutOfBoundsException JavaDoc {
291     int iColumnIndex = getColumnIndex(sColumnName.toLowerCase());
292     if ( -1 == iColumnIndex)throw new ArrayIndexOutOfBoundsException JavaDoc(
293         "Cannot find column named " + sColumnName);
294     aValues[iColumnIndex] = oValue;
295   }
296
297   // ---------------------------------------------------------------------------
298

299   /**
300    * Put all values from a map on their corresponding columns matching by name
301    * @param oValues Map
302    */

303   public void putAll(Map JavaDoc oValues) {
304     int iColumnIndex;
305     String JavaDoc sColumnName;
306     if (DebugFile.trace) {
307       DebugFile.writeln("Begin ProductLoader.putAll()");
308       DebugFile.incIdent();
309     }
310     Iterator JavaDoc oIter = oValues.keySet().iterator();
311     while (oIter.hasNext()) {
312       sColumnName = (String JavaDoc) oIter.next();
313       iColumnIndex = getColumnIndex(sColumnName.toLowerCase());
314       if (iColumnIndex > 0) {
315         Object JavaDoc oVal = oValues.get(sColumnName);
316         if (oVal == null)
317           aValues[iColumnIndex] = null;
318         else if (oVal.getClass().getName().startsWith("[L")) {
319           aValues[iColumnIndex] = java.lang.reflect.Array.get(oVal, 0);
320         }
321         else {
322           aValues[iColumnIndex] = oVal;
323         }
324         if (DebugFile.trace) DebugFile.writeln(sColumnName.toLowerCase() + "=" +
325                                                aValues[iColumnIndex]);
326       }
327       else {
328         if (DebugFile.trace) DebugFile.writeln(sColumnName + " not found");
329       } // fi (iColumnIndex)
330
} // wend
331
if (DebugFile.trace) {
332       DebugFile.decIdent();
333       DebugFile.writeln("End ProductLoader.putAll()");
334     }
335   } // putAll
336

337   // ---------------------------------------------------------------------------
338

339   /**
340    * Get column by index
341    * @param iColumnIndex int Colunm index [0..getColumnCount()-1]
342    * @return Object Column value
343    * @throws ArrayIndexOutOfBoundsException
344    */

345   public Object JavaDoc get(int iColumnIndex) throws ArrayIndexOutOfBoundsException JavaDoc {
346     return aValues[iColumnIndex];
347   } // get
348

349   // ---------------------------------------------------------------------------
350

351   /**
352    * Get column by name
353    * @param sColumnName String Column name (case sensitive)
354    * @return Object Column value
355    * @throws ArrayIndexOutOfBoundsException If no column with sucjh name was found
356    */

357   public Object JavaDoc get(String JavaDoc sColumnName) throws ArrayIndexOutOfBoundsException JavaDoc {
358     int iColumnIndex = getColumnIndex(sColumnName.toLowerCase());
359     if ( -1 == iColumnIndex)throw new ArrayIndexOutOfBoundsException JavaDoc(
360         "Cannot find column named " + sColumnName);
361     return aValues[iColumnIndex];
362   }
363
364   // ---------------------------------------------------------------------------
365

366   private static boolean test(int iInputValue, int iBitMask) {
367     return (iInputValue & iBitMask) != 0;
368   } // test
369

370   // ---------------------------------------------------------------------------
371

372   private String JavaDoc getColNull(int iColIndex) throws
373       ArrayIndexOutOfBoundsException JavaDoc, ClassCastException JavaDoc {
374     if (DebugFile.trace) {
375       if (iColIndex < 0 || iColIndex >= aValues.length)
376         throw new ArrayIndexOutOfBoundsException JavaDoc(
377             "ProductLoader.getColNull() column index " +
378             String.valueOf(iColIndex) + " must be in the range between 0 and " +
379             String.valueOf(aValues.length));
380       DebugFile.writeln("ProductLoader.getColNull(" + String.valueOf(iColIndex) +
381                         ") : " + aValues[iColIndex]);
382     }
383     String JavaDoc sRetVal;
384     if (null == aValues[iColIndex])
385       sRetVal = null;
386     else {
387       try {
388         sRetVal = aValues[iColIndex].toString();
389       }
390       catch (ClassCastException JavaDoc cce) {
391         if (aValues[iColIndex] == null)
392           throw new ClassCastException JavaDoc("ProductLoader.getColNull(" +
393                                        String.valueOf(iColIndex) +
394                                        ") could not cast null to String");
395         else
396           throw new ClassCastException JavaDoc("ProductLoader.getColNull(" +
397                                        String.valueOf(iColIndex) +
398                                        ") could not cast " +
399                                        aValues[iColIndex].getClass().getName() +
400                                        " " + aValues[iColIndex] + " to String");
401       }
402       if (sRetVal.length() == 0 || sRetVal.equalsIgnoreCase("null"))
403         sRetVal = null;
404     }
405     return sRetVal;
406   } // getColNull
407

408   // ---------------------------------------------------------------------------
409

410   private void storeAddress(Connection JavaDoc oConn, String JavaDoc sWorkArea) throws
411       SQLException JavaDoc {
412
413     if (DebugFile.trace) {
414       DebugFile.writeln("Begin ProductLoader.storeAddress([Connection], " +
415                         sWorkArea + ")");
416       DebugFile.incIdent();
417     }
418
419     oAddrUpdt.setTimestamp(1, new Timestamp JavaDoc(new Date JavaDoc().getTime()));
420     oAddrUpdt.setString(2, getColNull(tp_location));
421     oAddrUpdt.setString(3, getColNull(nm_company));
422     oAddrUpdt.setString(4, getColNull(tp_street));
423     oAddrUpdt.setString(5, getColNull(nm_street));
424     oAddrUpdt.setString(6, getColNull(nu_street));
425     oAddrUpdt.setString(7, getColNull(tx_addr1));
426     oAddrUpdt.setString(8, getColNull(tx_addr2));
427     oAddrUpdt.setString(9, getColNull(id_country));
428     oAddrUpdt.setString(10, getColNull(nm_country));
429     oAddrUpdt.setString(11, getColNull(id_state));
430     oAddrUpdt.setString(12, getColNull(nm_state));
431     oAddrUpdt.setString(13, getColNull(mn_city));
432     oAddrUpdt.setString(14, getColNull(zip_code));
433     oAddrUpdt.setString(15, getColNull(work_phone));
434     oAddrUpdt.setString(16, getColNull(direct_phone));
435     oAddrUpdt.setString(17, getColNull(home_phone));
436     oAddrUpdt.setString(18, getColNull(mov_phone));
437     oAddrUpdt.setString(19, getColNull(fax_phone));
438     oAddrUpdt.setString(20, getColNull(other_phone));
439     oAddrUpdt.setString(21, getColNull(po_box));
440     oAddrUpdt.setString(22, getColNull(tx_email));
441     oAddrUpdt.setString(23, getColNull(tx_email_alt));
442     oAddrUpdt.setString(24, getColNull(url_addr));
443     if (null==aValues[coord_x])
444       oAddrUpdt.setNull(25, Types.FLOAT);
445     else
446       oAddrUpdt.setObject(25, aValues[coord_x], Types.FLOAT);
447     if (null==aValues[coord_y])
448       oAddrUpdt.setNull(26, Types.FLOAT);
449     else
450       oAddrUpdt.setObject(26, aValues[coord_y], Types.FLOAT);
451     oAddrUpdt.setString(27, getColNull(contact_person));
452     oAddrUpdt.setString(28, getColNull(tx_salutation));
453     oAddrUpdt.setString(29, getColNull(tx_remarks));
454     oAddrUpdt.setObject(30, get(gu_address), Types.CHAR);
455     if (DebugFile.trace) DebugFile.writeln("PreparedStatement.executeUpdate()");
456     int iAffected = oAddrUpdt.executeUpdate();
457     if (DebugFile.trace) DebugFile.writeln("iAffected = " +
458                                            String.valueOf(iAffected));
459     if (0 == iAffected) {
460       oAddrInsr.setObject(1, get(gu_address), Types.CHAR);
461       oAddrInsr.setObject(2, sWorkArea, Types.CHAR);
462       oAddrInsr.setString(3, getColNull(tp_location));
463       oAddrInsr.setString(4, getColNull(nm_company));
464       oAddrInsr.setString(5, getColNull(tp_street));
465       oAddrInsr.setString(6, getColNull(nm_street));
466       oAddrInsr.setString(7, getColNull(nu_street));
467       oAddrInsr.setString(8, getColNull(tx_addr1));
468       oAddrInsr.setString(9, getColNull(tx_addr2));
469       oAddrInsr.setString(10, getColNull(id_country));
470       oAddrInsr.setString(11, getColNull(nm_country));
471       oAddrInsr.setString(12, getColNull(id_state));
472       oAddrInsr.setString(13, getColNull(nm_state));
473       oAddrInsr.setString(14, getColNull(mn_city));
474       oAddrInsr.setString(15, getColNull(zip_code));
475       oAddrInsr.setString(16, getColNull(work_phone));
476       oAddrInsr.setString(17, getColNull(direct_phone));
477       oAddrInsr.setString(18, getColNull(home_phone));
478       oAddrInsr.setString(19, getColNull(mov_phone));
479       oAddrInsr.setString(20, getColNull(fax_phone));
480       oAddrInsr.setString(21, getColNull(other_phone));
481       oAddrInsr.setString(22, getColNull(po_box));
482       oAddrInsr.setString(23, getColNull(tx_email));
483       oAddrInsr.setString(24, getColNull(tx_email_alt));
484       oAddrInsr.setString(25, getColNull(url_addr));
485       if (null==aValues[coord_x])
486         oAddrInsr.setNull(26, Types.FLOAT);
487       else
488         oAddrInsr.setObject(26, aValues[coord_x], Types.FLOAT);
489       if (null==aValues[coord_y])
490         oAddrInsr.setNull(27, Types.FLOAT);
491       else
492         oAddrInsr.setObject(27, aValues[coord_y], Types.FLOAT);
493       oAddrInsr.setString(28, getColNull(contact_person));
494       oAddrInsr.setString(29, getColNull(tx_salutation));
495       oAddrInsr.setString(30, getColNull(tx_remarks));
496       if (DebugFile.trace) DebugFile.writeln(
497           "PreparedStatement.executeUpdate()");
498       oAddrInsr.executeUpdate();
499     }
500     if (DebugFile.trace) {
501       DebugFile.decIdent();
502       DebugFile.writeln("End ProductLoader.storeAddress()");
503     }
504   } // storeAddress
505

506   // ---------------------------------------------------------------------------
507

508   private void storeFare(Connection JavaDoc oConn, boolean bNewPrd) throws SQLException JavaDoc {
509
510     if (DebugFile.trace) {
511       DebugFile.writeln("Begin ProductLoader.storeFare([Connection])");
512       DebugFile.incIdent();
513     }
514
515     int iAffected = 0;
516
517     if (!bNewPrd) {
518       oFareUpdt.setObject(1, aValues[pr_sale], Types.DECIMAL);
519       oFareUpdt.setObject(2, aValues[tp_fare], Types.VARCHAR);
520       oFareUpdt.setObject(3, aValues[id_currency], Types.VARCHAR);
521       oFareUpdt.setObject(4, aValues[pct_tax_rate], Types.FLOAT);
522       oFareUpdt.setObject(5, aValues[is_tax_included], Types.SMALLINT);
523       oFareUpdt.setObject(6, aValues[dt_start], Types.TIMESTAMP);
524       oFareUpdt.setObject(7, aValues[dt_end], Types.TIMESTAMP);
525       oFareUpdt.setObject(8, aValues[gu_product], Types.CHAR);
526       oFareUpdt.setObject(9, aValues[id_fare], Types.VARCHAR);
527
528       if (DebugFile.trace) DebugFile.writeln(
529           "PreparedStatement.executeUpdate()");
530       iAffected = oFareUpdt.executeUpdate();
531       if (DebugFile.trace) DebugFile.writeln("iAffected = " +
532                                              String.valueOf(iAffected));
533     }
534
535     if (0 == iAffected) {
536       oFareInsr.setObject(1, aValues[pr_sale], Types.DECIMAL);
537       oFareInsr.setObject(2, aValues[tp_fare], Types.VARCHAR);
538       oFareInsr.setObject(3, aValues[id_currency], Types.VARCHAR);
539       oFareInsr.setObject(4, aValues[pct_tax_rate], Types.FLOAT);
540       oFareInsr.setObject(5, aValues[is_tax_included], Types.SMALLINT);
541       oFareInsr.setObject(6, aValues[dt_start], Types.TIMESTAMP);
542       oFareInsr.setObject(7, aValues[dt_end], Types.TIMESTAMP);
543       oFareInsr.setObject(8, aValues[gu_product], Types.CHAR);
544       oFareInsr.setObject(9, aValues[id_fare], Types.VARCHAR);
545       oFareInsr.executeUpdate();
546     }
547
548     if (DebugFile.trace) {
549       DebugFile.decIdent();
550       DebugFile.writeln("End ProductLoader.storeFare()");
551     }
552   } // storeFare
553

554   // ---------------------------------------------------------------------------
555

556   private void storeAttr(Connection JavaDoc oConn, boolean bNewPrd) throws SQLException JavaDoc {
557
558     if (DebugFile.trace) {
559       DebugFile.writeln("Begin ProductLoader.storeAttr([Connection], "+String.valueOf(bNewPrd)+")");
560       DebugFile.incIdent();
561     }
562
563     int iAffected = 0;
564     int[] aColIdxs = new int[] {
565         adult_rated, alturl, author, availability, brand, client, color,
566         contact_person, country_code, country, cover, days_to_deliver,
567         department, disk_space, display, doc_no, dt_acknowledge, dt_expire,
568         dt_out, email, fax, forward_to, icq_id, ip_addr, isbn, nu_lines, memory,
569         mobilephone, office, ordinal, organization, pages, paragraphs, phone1,
570         phone2, power, project, product_group, rank, reference_id, revised_by,
571         rooms, scope, signature, size_x, size_y, size_z, speed, state_code,
572         state, subject, target, template, typeof, upload_by, weight, words,
573         zip_code, gu_product};
574     int nColIdxs = aColIdxs.length;
575
576     if (!bNewPrd) {
577       for (int c = 0; c < nColIdxs; c++) {
578         oAttrUpdt.setObject(c + 1, aValues[aColIdxs[c]]);
579       }
580       if (DebugFile.trace) {
581         DebugFile.writeln("gu_product="+aValues[aColIdxs[gu_product]]);
582         DebugFile.writeln("PreparedStatement.executeUpdate()");
583       }
584       iAffected = oAttrUpdt.executeUpdate();
585       if (DebugFile.trace) DebugFile.writeln("iAffected = " + String.valueOf(iAffected));
586     }
587
588     if (0 == iAffected) {
589       for (int c = 0; c < nColIdxs; c++) {
590         oAttrInsr.setObject(c + 1, aValues[aColIdxs[c]]);
591       }
592       if (DebugFile.trace) {
593         DebugFile.writeln("gu_product="+aValues[aColIdxs[gu_product]]);
594         DebugFile.writeln("PreparedStatement.executeUpdate()");
595       }
596       oAttrInsr.executeUpdate();
597     }
598
599     if (DebugFile.trace) {
600       DebugFile.decIdent();
601       DebugFile.writeln("End ProductLoader.storeAttr()");
602     }
603   } // storeAttr
604

605   // ---------------------------------------------------------------------------
606

607   private void storeLocation(Connection JavaDoc oConn, String JavaDoc sOwner,
608                              boolean bNewPrd) throws SQLException JavaDoc {
609
610     if (DebugFile.trace) {
611       DebugFile.writeln("Begin ProductLoader.storeLocation([Connection], " +
612                         sOwner + ", " + String.valueOf(bNewPrd) + ")");
613       DebugFile.incIdent();
614       DebugFile.writeln("len_file="+aValues[len_file]);
615     }
616
617
618     int iAffected = 0;
619
620     if (!bNewPrd) {
621       oLocaUpdt.setString(1, sOwner);
622       if (null == aValues[id_cont_type])
623         oLocaUpdt.setInt(2, 100);
624       else
625         oLocaUpdt.setObject(2, aValues[id_cont_type], Types.INTEGER);
626       if (null == aValues[id_prod_type])
627         oLocaUpdt.setString(3, "?");
628       else
629         oLocaUpdt.setObject(3, "".equals(aValues[id_prod_type]) ? "?" : aValues[id_prod_type], Types.VARCHAR);
630       if (null == aValues[len_file])
631         oLocaUpdt.setInt(4, 0);
632       else
633         oLocaUpdt.setObject(4, aValues[len_file], Types.INTEGER);
634       if (null == aValues[xprotocol])
635         oLocaUpdt.setString(5, "file://");
636       else
637         oLocaUpdt.setObject(5, aValues[xprotocol], Types.VARCHAR);
638       if (null == aValues[xhost])
639         oLocaUpdt.setString(6, "localhost");
640       else
641         oLocaUpdt.setObject(6, aValues[xhost], Types.VARCHAR);
642       oLocaUpdt.setObject(7, aValues[xport], Types.INTEGER);
643       oLocaUpdt.setObject(8, aValues[xoriginalfile], Types.VARCHAR);
644       oLocaUpdt.setObject(9, new Timestamp JavaDoc(new Date JavaDoc().getTime()),
645                           Types.TIMESTAMP);
646       oLocaUpdt.setObject(10, aValues[dt_uploaded], Types.TIMESTAMP);
647       oLocaUpdt.setObject(11, aValues[de_prod_locat], Types.VARCHAR);
648       oLocaUpdt.setObject(12, aValues[status], Types.INTEGER);
649       oLocaUpdt.setObject(13, aValues[nu_current_stock], Types.FLOAT);
650       oLocaUpdt.setObject(14, aValues[nu_reserved_stock], Types.FLOAT);
651       oLocaUpdt.setObject(15, aValues[nu_min_stock], Types.FLOAT);
652       oLocaUpdt.setObject(16, aValues[vs_stamp], Types.VARCHAR);
653       oLocaUpdt.setObject(17, aValues[tx_email], Types.VARCHAR);
654       oLocaUpdt.setObject(18, aValues[tag_prod_locat], Types.VARCHAR);
655       oLocaUpdt.setObject(19, aValues[gu_product], Types.VARCHAR);
656
657       if (DebugFile.trace) DebugFile.writeln(
658           "PreparedStatement.executeUpdate()");
659       iAffected = oLocaUpdt.executeUpdate();
660       if (DebugFile.trace) DebugFile.writeln("iAffected = " +
661                                              String.valueOf(iAffected));
662     }
663
664     if (0 == iAffected) {
665       oLocaInsr.setString(1, Gadgets.generateUUID());
666       oLocaInsr.setString(2, sOwner);
667       if (null == aValues[id_cont_type])
668         oLocaInsr.setInt(3, 100);
669       else
670         oLocaInsr.setObject(3, aValues[id_cont_type], Types.INTEGER);
671       if (null == aValues[id_prod_type])
672         oLocaInsr.setString(4, "?");
673       else
674         oLocaInsr.setObject(4, "".equals(aValues[id_prod_type]) ? "?" : aValues[id_prod_type], Types.VARCHAR);
675       if (null == aValues[len_file])
676         oLocaInsr.setInt(5, 0);
677       else
678         oLocaInsr.setObject(5, aValues[len_file], Types.INTEGER);
679       if (null == aValues[xprotocol])
680         oLocaInsr.setString(6, "file://");
681       else
682         oLocaInsr.setObject(6, aValues[xprotocol], Types.VARCHAR);
683       if (null == aValues[xhost])
684         oLocaInsr.setString(7, "localhost");
685       else
686         oLocaInsr.setObject(7, aValues[xhost], Types.VARCHAR);
687       oLocaInsr.setObject(8, aValues[xport], Types.INTEGER);
688       oLocaInsr.setObject(9, aValues[xpath], Types.VARCHAR);
689       oLocaInsr.setObject(10, aValues[xfile], Types.VARCHAR);
690       oLocaInsr.setObject(11, aValues[xanchor], Types.VARCHAR);
691       oLocaInsr.setObject(12, aValues[xoriginalfile], Types.VARCHAR);
692       oLocaInsr.setObject(13, aValues[dt_uploaded], Types.TIMESTAMP);
693       oLocaInsr.setObject(14, aValues[de_prod_locat], Types.VARCHAR);
694       oLocaInsr.setObject(15, aValues[status], Types.INTEGER);
695       oLocaInsr.setObject(16, aValues[nu_current_stock], Types.FLOAT);
696       oLocaInsr.setObject(17, aValues[nu_reserved_stock], Types.FLOAT);
697       oLocaInsr.setObject(18, aValues[nu_min_stock], Types.FLOAT);
698       oLocaInsr.setObject(19, aValues[vs_stamp], Types.VARCHAR);
699       oLocaInsr.setObject(20, aValues[tx_email], Types.VARCHAR);
700       oLocaInsr.setObject(21, aValues[tag_prod_locat], Types.VARCHAR);
701       oLocaInsr.setObject(22, aValues[gu_product], Types.VARCHAR);
702       if (DebugFile.trace) DebugFile.writeln("PreparedStatement.executeInsert()");
703       oLocaInsr.executeUpdate();
704     }
705
706     if (DebugFile.trace) {
707       DebugFile.decIdent();
708       DebugFile.writeln("End ProductLoader.storeLocation()");
709     }
710   } // storeLocation
711

712   // ---------------------------------------------------------------------------
713

714   private void storeKeywords(Connection JavaDoc oConn, boolean bNewPrd) throws
715       SQLException JavaDoc {
716
717     if (DebugFile.trace) {
718       DebugFile.writeln("Begin ProductLoader.storeKeywords([Connection], " +
719                         String.valueOf(bNewPrd) + ")");
720       DebugFile.incIdent();
721     }
722
723     if (!bNewPrd) {
724       oKeysDlte.setObject(1, aValues[gu_product], Types.CHAR);
725       oKeysDlte.executeUpdate();
726     }
727
728     if (null != aValues[tx_keywords]) {
729       oKeysInsr.setObject(1, aValues[gu_product], Types.CHAR);
730       oKeysInsr.setObject(2, aValues[tx_keywords], Types.VARCHAR);
731       oKeysInsr.executeUpdate();
732     }
733
734     if (DebugFile.trace) {
735       DebugFile.decIdent();
736       DebugFile.writeln("End ProductLoader.storeKeywords()");
737     }
738   } // storeKeywords
739

740   // ---------------------------------------------------------------------------
741

742   private void insertProduct(Connection JavaDoc oConn, String JavaDoc sOwner) throws
743       SQLException JavaDoc,NullPointerException JavaDoc {
744
745     if (DebugFile.trace) {
746       DebugFile.writeln("Begin ProductLoader.insertProduct([Connection], " + sOwner + ")");
747       if (null==aValues[gu_product]) throw new NullPointerException JavaDoc("ProductLoader.insertProduct() gu_product cannot be null");
748       DebugFile.incIdent();
749     }
750
751     oProdInsr.setString(1, sOwner);
752     oProdInsr.setObject(2, aValues[nm_product], Types.VARCHAR);
753     oProdInsr.setObject(3, aValues[id_status], Types.SMALLINT);
754     oProdInsr.setObject(4, aValues[is_compound], Types.SMALLINT);
755     oProdInsr.setObject(5, aValues[gu_blockedby], Types.CHAR);
756     oProdInsr.setObject(6, aValues[dt_uploaded], Types.TIMESTAMP);
757     oProdInsr.setObject(7, aValues[id_language], Types.CHAR);
758     oProdInsr.setObject(8, aValues[de_product], Types.VARCHAR);
759     oProdInsr.setObject(9, aValues[pr_list], Types.DECIMAL);
760     oProdInsr.setObject(10, aValues[pr_sale], Types.DECIMAL);
761     oProdInsr.setObject(11, aValues[pr_discount], Types.DECIMAL);
762     oProdInsr.setObject(12, aValues[pr_purchase], Types.DECIMAL);
763     oProdInsr.setObject(13, aValues[id_currency], Types.VARCHAR);
764     oProdInsr.setObject(14, aValues[pct_tax_rate], Types.FLOAT);
765     oProdInsr.setObject(15, aValues[is_tax_included], Types.SMALLINT);
766     oProdInsr.setObject(16, aValues[dt_start], Types.TIMESTAMP);
767     oProdInsr.setObject(17, aValues[dt_end], Types.TIMESTAMP);
768     oProdInsr.setObject(18, aValues[tag_product], Types.VARCHAR);
769     oProdInsr.setObject(19, aValues[id_ref], Types.VARCHAR);
770     oProdInsr.setObject(20, aValues[gu_address], Types.CHAR);
771     oProdInsr.setObject(21, aValues[gu_product], Types.CHAR);
772     oProdInsr.executeUpdate();
773
774     if (DebugFile.trace) {
775       DebugFile.decIdent();
776       DebugFile.writeln("End ProductLoader.insertProduct() : " + aValues[gu_product]);
777     }
778   } // insertProduct
779

780   // ---------------------------------------------------------------------------
781

782   private void updateProduct(Connection JavaDoc oConn, String JavaDoc sOwner) throws
783       SQLException JavaDoc,NullPointerException JavaDoc {
784
785     if (DebugFile.trace) {
786       DebugFile.writeln("Begin ProductLoader.updateProduct([Connection], " + sOwner + ")");
787       if (null==aValues[gu_product]) throw new NullPointerException JavaDoc("ProductLoader.updateProduct() gu_product cannot be null");
788       DebugFile.incIdent();
789     }
790
791     oProdUpdt.setString(1, sOwner);
792     oProdUpdt.setObject(2, aValues[nm_product], Types.VARCHAR);
793     oProdUpdt.setObject(3, aValues[id_status], Types.SMALLINT);
794     oProdUpdt.setObject(4, aValues[is_compound], Types.SMALLINT);
795     oProdUpdt.setObject(5, aValues[gu_blockedby], Types.CHAR);
796     oProdUpdt.setObject(6, new Timestamp JavaDoc(new Date JavaDoc().getTime()), Types.TIMESTAMP);
797     oProdUpdt.setObject(7, aValues[dt_uploaded], Types.TIMESTAMP);
798     oProdUpdt.setObject(8, aValues[id_language], Types.CHAR);
799     oProdUpdt.setObject(9, aValues[de_product], Types.VARCHAR);
800     oProdUpdt.setObject(10, aValues[pr_list], Types.DECIMAL);
801     oProdUpdt.setObject(11, aValues[pr_sale], Types.DECIMAL);
802     oProdUpdt.setObject(12, aValues[pr_discount], Types.DECIMAL);
803     oProdUpdt.setObject(13, aValues[pr_purchase], Types.DECIMAL);
804     oProdUpdt.setObject(14, aValues[id_currency], Types.VARCHAR);
805     oProdUpdt.setObject(15, aValues[pct_tax_rate], Types.FLOAT);
806     oProdUpdt.setObject(16, aValues[is_tax_included], Types.SMALLINT);
807     oProdUpdt.setObject(17, aValues[dt_start], Types.TIMESTAMP);
808     oProdUpdt.setObject(18, aValues[dt_end], Types.TIMESTAMP);
809     oProdUpdt.setObject(19, aValues[tag_product], Types.VARCHAR);
810     oProdUpdt.setObject(20, aValues[id_ref], Types.VARCHAR);
811     oProdUpdt.setObject(21, aValues[gu_address], Types.CHAR);
812     oProdUpdt.setObject(22, aValues[gu_product], Types.CHAR);
813     int iAffected = oProdUpdt.executeUpdate();
814
815     if (DebugFile.trace) {
816       DebugFile.decIdent();
817       DebugFile.writeln("End ProductLoader.updateProduct() : " + String.valueOf(iAffected));
818     }
819   } // updateProduct
820

821   // ---------------------------------------------------------------------------
822

823   /**
824    * Store properties curently held in RAM into the database
825    * @param oConn Opened JDBC connection
826    * @param sWorkArea String GUID of WorkArea to which inserted data will belong
827    * @param iFlags int A boolean combination of {MODE_APPEND|MODE_UPDATE|WRITE_ADDRESSES|WRITE_LOOKUPS|NO_DUPLICATED_NAMES|NO_DUPLICATED_REFERENCES}
828    * @throws SQLException
829    * @throws IllegalArgumentException
830    * @throws NullPointerException
831    * @throws ClassCastException
832    */

833   public void store(Connection JavaDoc oConn, String JavaDoc sWorkArea, int iFlags) throws
834       SQLException JavaDoc, IllegalArgumentException JavaDoc, NullPointerException JavaDoc,
835       ClassCastException JavaDoc {
836
837     boolean bNewPrd = false, bNewAdr = false;
838
839     String JavaDoc sAdrGuid;
840     String JavaDoc sPrdGuid;
841     String JavaDoc sCatGuid;
842     PreparedStatement JavaDoc oStmt;
843     ResultSet JavaDoc oRSet;
844
845     if (oProdUpdt == null || oFareUpdt == null || oAttrUpdt == null ||
846         oAddrUpdt == null)
847       throw new SQLException JavaDoc("Invalid command sequece. Must call ProductLoader.prepare() before ProductLoader.store()");
848
849     if (!test(iFlags, MODE_APPEND) && !test(iFlags, MODE_UPDATE))
850       throw new IllegalArgumentException JavaDoc("ProductLoader.store() Flags bitmask must contain either MODE_APPEND, MODE_UPDATE or both");
851
852     if (null == sWorkArea)
853       throw new NullPointerException JavaDoc(
854           "ProductLoader.store() Default WorkArea cannot be null");
855
856     if (null == getColNull(nm_product))
857       throw new NullPointerException JavaDoc(
858           "ProductLoader.store() nm_product cannot be null");
859
860     if (null == getColNull(id_ref) && test(iFlags, NO_DUPLICATED_REFERENCES))
861       throw new NullPointerException JavaDoc("ProductLoader.store() Product reference must be suplied at column id_ref if NO_DUPLICATED_REFERENCES is set");
862
863     if (null == getColNull(nm_category) && null == getColNull(gu_category))
864       throw new NullPointerException JavaDoc(
865           "ProductLoader.store() a Category is requiered for placing the products");
866
867     if (DebugFile.trace) {
868       DebugFile.writeln("Begin Productloader.store([Connection], " + sWorkArea +
869                         ", " + String.valueOf(iFlags) + ")");
870       DebugFile.incIdent();
871     }
872
873     // ***************************************************
874
// Parse URL substrings from url_addr if xhost is null
875

876     if (null != aValues[url_addr] && null == aValues[xhost]) {
877       ProductLocation oLoca = new ProductLocation();
878       try {
879         oLoca.setURL( (String JavaDoc) aValues[url_addr]);
880       }
881       catch (java.net.MalformedURLException JavaDoc badurl) {
882         if (DebugFile.trace) DebugFile.decIdent();
883         throw new IllegalArgumentException JavaDoc("Productloader.store() " +
884                                            aValues[url_addr] +
885                                            " does not have a valid URL syntax");
886       }
887       aValues[xprotocol] = oLoca.getStringNull(DB.xprotocol, "file://");
888       aValues[xhost] = oLoca.getStringNull(DB.xhost, "localhost");
889       aValues[xpath] = oLoca.getPath();
890       if (oLoca.isNull(DB.xport))
891         aValues[xport] = null;
892       else
893         aValues[xport] = oLoca.getInteger(DB.xport);
894       aValues[xfile] = oLoca.getStringNull(DB.xfile, null);
895       aValues[xanchor] = oLoca.getStringNull(DB.xanchor, null);
896       oLoca = null;
897     }
898
899     // ***************************************************
900

901     if (null != aValues[gu_category]) {
902
903       // *************************************************************************
904
// If gu_category is set then check whether or not it exists at the database
905

906       boolean bExists;
907       sCatGuid = (String JavaDoc) get(gu_category);
908
909       if (sLastCategoryGuid.equals(sCatGuid)) {
910         bExists = true;
911       }
912       else {
913         if (DebugFile.trace)
914           DebugFile.writeln("Connection.prepareStatement(SELECT " +
915                             DB.nm_category + "," + DB.gu_owner +
916                             " FROM " + DB.k_categories +
917                             " WHERE " + DB.gu_category + "='" + sCatGuid + "')");
918
919         oStmt = oConn.prepareStatement("SELECT " + DB.nm_category + "," + DB.gu_owner +
920                                        " FROM " + DB.k_categories +
921                                        " WHERE " +DB.gu_category + "=?",
922                                        ResultSet.TYPE_FORWARD_ONLY,
923                                        ResultSet.CONCUR_READ_ONLY);
924         oStmt.setString(1, sCatGuid);
925         oRSet = oStmt.executeQuery();
926         bExists = oRSet.next();
927         if (bExists) {
928           sLastCategoryGuid = sCatGuid;
929           sLastCategoryName = oRSet.getString(1);
930           sLastOwnGuid = oRSet.getString(2);
931         }
932         oRSet.close();
933         oStmt.close();
934       }
935       if (!bExists) {
936         // If gu_category does not exist then raise and exception
937
if (DebugFile.trace) DebugFile.decIdent();
938         throw new SQLException JavaDoc("ProductLoader.store() Category " +
939                                get(gu_category) + " does not exist", "01S06");
940       }
941     }
942     else {
943
944       // *******************************************************
945
// If gu_category is not set then nm_category must be set.
946
// Get category GUID from its name.
947

948       if (DebugFile.trace)
949         DebugFile.writeln("Connection.prepareStatement(SELECT " +
950                           DB.gu_category + "," + DB.gu_owner +
951                           " FROM " + DB.k_categories +
952                           " WHERE " + DB.nm_category + "='" + get(nm_category) +
953                           "')");
954       oStmt = oConn.prepareStatement("SELECT " + DB.gu_category + "," + DB.gu_owner +
955                                      " FROM " + DB.k_categories +
956                                      " WHERE " + DB.nm_category + "=?",
957                                      ResultSet.TYPE_FORWARD_ONLY,
958                                      ResultSet.CONCUR_READ_ONLY);
959       oStmt.setObject(1, get(nm_category), Types.VARCHAR);
960       oRSet = oStmt.executeQuery();
961       if (oRSet.next()) {
962         sLastCategoryName = (String JavaDoc) get(nm_category);
963         sLastCategoryGuid = sCatGuid = oRSet.getString(1);
964         sLastOwnGuid = oRSet.getString(2);
965       }
966       else {
967         sCatGuid = null;
968         sLastCategoryGuid = sLastCategoryName = "";
969       }
970       oRSet.close();
971       oStmt.close();
972
973       if (null == sCatGuid)
974         if (DebugFile.trace) DebugFile.decIdent();
975       throw new SQLException JavaDoc("ProductLoader.store() Category " + get(nm_category) + " not found", "01S06");
976     }
977
978     if (null == aValues[gu_product]) {
979
980       // ********************************************************************************
981
// If gu_product is not set then check whether the product reference already exists
982

983       if (null != getColNull(id_ref) && test(iFlags, NO_DUPLICATED_REFERENCES)) {
984         sPrdGuid = Product.getIdFromReference(oConn, getColNull(id_ref),
985                                               sWorkArea);
986         // If reference does not exist then it is a new Product
987
bNewPrd = (sPrdGuid != null);
988       }
989       else if (test(iFlags, NO_DUPLICATED_NAMES)) {
990         // If there is no Product reference but no duplicated names are allowed,
991
// then look for a product with the same name
992
sPrdGuid = Product.getIdFromName(oConn, getColNull(nm_product),
993                                          sWorkArea);
994         // If a Product with same name does not exist then it is a new Product
995
bNewPrd = (sPrdGuid != null);
996       }
997       else {
998         // If there is no reference nor no duplicated product names is set then
999
// this must be a new product
1000
bNewPrd = true;
1001        sPrdGuid = Gadgets.generateUUID();
1002      }
1003      put(gu_product, (sPrdGuid != null ? sPrdGuid : Gadgets.generateUUID()));
1004    }
1005    else {
1006      bNewPrd = false;
1007    }
1008
1009    if (DebugFile.trace) DebugFile.writeln((bNewPrd ? "new" : "existing")+" gu_product="+get(gu_product));
1010
1011    if (bNewPrd) {
1012
1013      // ************************
1014
// If this is a new product
1015

1016      if (null == getColNull(gu_address)) {
1017        if (DebugFile.trace)
1018          DebugFile.writeln("Connection.prepareStatement(SELECT " +
1019                            DB.gu_address + " FROM " + DB.k_products +
1020                            " WHERE " + DB.gu_product + "='" + get(gu_product) + "')");
1021
1022        oStmt = oConn.prepareStatement("SELECT " + DB.gu_address + " FROM " +
1023                                       DB.k_products + " WHERE " +
1024                                       DB.gu_product + "=?",
1025                                       ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
1026        oStmt.setObject(1, get(gu_product), Types.CHAR);
1027        oRSet = oStmt.executeQuery();
1028        if (oRSet.next()) {
1029          sAdrGuid = oRSet.getString(1);
1030          if (oRSet.wasNull()) sAdrGuid = null;
1031        }
1032        else {
1033          sAdrGuid = null;
1034        }
1035        if ( (null == sAdrGuid) &&
1036            (aValues[nm_street] != null || aValues[tx_addr1] != null ||
1037             aValues[id_country] != null || aValues[nm_country] != null ||
1038             aValues[id_state] != null || aValues[nm_state] != null ||
1039             aValues[mn_city] != null || aValues[zip_code] != null ||
1040             aValues[work_phone] != null || aValues[direct_phone] != null ||
1041             aValues[home_phone] != null || aValues[mov_phone] != null ||
1042             aValues[po_box] != null || aValues[tx_email] != null ||
1043             aValues[url_addr] != null || aValues[coord_x] != null)) {
1044          sAdrGuid = Gadgets.generateUUID();
1045          bNewAdr = true;
1046        }
1047        else {
1048          bNewAdr = false;
1049        }
1050        put(gu_address, sAdrGuid);
1051      }
1052      else {
1053        bNewAdr = false;
1054      } // fi (gu_address==null)
1055

1056      if (DebugFile.trace) DebugFile.writeln((bNewAdr ? "new" : "existing")+" gu_address="+get(gu_address));
1057
1058      if (null != aValues[gu_address]) {
1059        storeAddress(oConn, sWorkArea);
1060      }
1061
1062      insertProduct(oConn, sLastOwnGuid);
1063
1064      if (null != aValues[id_fare]) storeFare(oConn, true);
1065
1066      storeAttr(oConn, true);
1067
1068      storeLocation(oConn, sLastOwnGuid, true);
1069
1070      storeKeywords(oConn, true);
1071
1072      oCatgInsr.setString(1, sLastCategoryGuid);
1073      oCatgInsr.setObject(2, aValues[gu_product], Types.CHAR);
1074      if (null == aValues[od_position])
1075        oCatgInsr.setInt(3, 0);
1076      else
1077        oCatgInsr.setObject(3, aValues[od_position], Types.INTEGER);
1078      oCatgInsr.executeUpdate();
1079
1080    }
1081    else {
1082
1083      // ****************************
1084
// If this is not a new product
1085

1086      bNewAdr = aValues[nm_street] != null || aValues[tx_addr1] != null ||
1087          aValues[id_country] != null || aValues[nm_country] != null ||
1088          aValues[id_state] != null || aValues[nm_state] != null ||
1089          aValues[mn_city] != null || aValues[zip_code] != null ||
1090          aValues[work_phone] != null || aValues[direct_phone] != null ||
1091          aValues[home_phone] != null || aValues[mov_phone] != null ||
1092          aValues[po_box] != null || aValues[tx_email] != null ||
1093          aValues[url_addr] != null || aValues[coord_x] != null;
1094
1095      if (bNewAdr) {
1096        put(gu_address, Gadgets.generateUUID());
1097      }
1098
1099      if (null != aValues[gu_address]) {
1100        storeAddress(oConn, sWorkArea);
1101      }
1102
1103      updateProduct(oConn, sLastOwnGuid);
1104
1105      if (null != aValues[id_fare]) storeFare(oConn, false);
1106
1107      storeAttr(oConn, false);
1108
1109      storeLocation(oConn, sLastOwnGuid, false);
1110
1111      storeKeywords(oConn, false);
1112
1113      oCatgDlte.setObject(1, aValues[gu_product], Types.CHAR);
1114      oCatgDlte.executeUpdate();
1115
1116      oCatgInsr.setString(1, sLastCategoryGuid);
1117      oCatgInsr.setObject(2, aValues[gu_product], Types.CHAR);
1118      if (null == aValues[od_position])
1119        oCatgInsr.setInt(3, 0);
1120      else
1121        oCatgInsr.setObject(3, aValues[od_position], Types.INTEGER);
1122      oCatgInsr.executeUpdate();
1123
1124    } // fi (!bNewPrd)
1125
} // store
1126

1127  // ---------------------------------------------------------------------------
1128

1129  public static final int MODE_APPEND = ImportLoader.MODE_APPEND;
1130  public static final int MODE_UPDATE = ImportLoader.MODE_UPDATE;
1131  public static final int MODE_APPENDUPDATE = ImportLoader.MODE_APPENDUPDATE;
1132  public static final int WRITE_LOOKUPS = ImportLoader.WRITE_LOOKUPS;
1133
1134  public static final int WRITE_ADDRESSES = 128;
1135  public static final int NO_DUPLICATED_NAMES = 256;
1136  public static final int NO_DUPLICATED_REFERENCES = 512;
1137
1138  // ---------------------------------------------------------------------------
1139

1140  // Keep this list sorted
1141
private static final String JavaDoc[] ColumnNames = { "", "adult_rated","alturl","author","availability","bo_active","brand","client","color","contact_person","coord_x","coord_y","country","country_code","cover","days_to_deliver","de_prod_locat","de_product","department","direct_phone","disk_space","display","doc_no","dt_acknowledge","dt_created","dt_end","dt_expire","dt_modified","dt_out","dt_start","dt_uploaded","email","fax","fax_phone","forward_to","gu_address","gu_blockedby","gu_category","gu_location","gu_owner","gu_product","gu_user","home_phone","icq_id","id_cont_type","id_country","id_currency","id_fare","id_language","id_prod_type","id_ref","id_state","id_status","ip_addr","is_compound","is_tax_included","isbn","ix_address","len_file","memory","mn_city","mobilephone","mov_phone","nm_category","nm_company","nm_country","nm_product","nm_state","nm_street","nu_current_stock","nu_lines","nu_min_stock","nu_reserved_stock","nu_street","od_position","office","ordinal","organization","other_phone","pages","paragraphs","pct_tax_rate","pg_prod_locat","phone1","phone2","po_box","power","pr_discount","pr_list","pr_purchase","pr_sale","product_group","project","rank","reference_id","revised_by","rooms","scope","signature","size_x","size_y","size_z","speed","state","state_code","status","subject","tag_prod_locat","tag_product","target","template","tp_fare","tp_location","tp_street","tx_addr1","tx_addr2","tx_email","tx_email_alt","tx_keywords","tx_remarks","tx_salutation","typeof","upload_by","url_addr","vs_stamp","weight","words","work_phone","xanchor","xfile","xhost","xoriginalfile","xpath","xport","xprotocol","zip_code" };
1142
1143  public static int adult_rated = 1;
1144  public static int alturl = 2;
1145  public static int author = 3;
1146  public static int availability = 4;
1147  public static int bo_active = 5;
1148  public static int brand = 6;
1149  public static int client = 7;
1150  public static int color = 8;
1151  public static int contact_person = 9;
1152  public static int coord_x = 10;
1153  public static int coord_y = 11;
1154  public static int country = 12;
1155  public static int country_code = 13;
1156  public static int cover = 14;
1157  public static int days_to_deliver = 15;
1158  public static int de_prod_locat = 16;
1159  public static int de_product = 17;
1160  public static int department = 18;
1161  public static int direct_phone = 19;
1162  public static int disk_space = 20;
1163  public static int display = 21;
1164  public static int doc_no = 22;
1165  public static int dt_acknowledge = 23;
1166  public static int dt_created = 24;
1167  public static int dt_end = 25;
1168  public static int dt_expire = 26;
1169  public static int dt_modified = 27;
1170  public static int dt_out = 28;
1171  public static int dt_start = 29;
1172  public static int dt_uploaded = 30;
1173  public static int email = 31;
1174  public static int fax = 32;
1175  public static int fax_phone = 33;
1176  public static int forward_to = 34;
1177  public static int gu_address = 35;
1178  public static int gu_blockedby = 36;
1179  public static int gu_category = 37;
1180  public static int gu_location = 38;
1181  public static int gu_owner = 39;
1182  public static int gu_product = 40;
1183  public static int gu_user = 41;
1184  public static int home_phone = 42;
1185  public static int icq_id = 43;
1186  public static int id_cont_type = 44;
1187  public static int id_country = 45;
1188  public static int id_currency = 46;
1189  public static int id_fare = 47;
1190  public static int id_language = 48;
1191  public static int id_prod_type = 49;
1192  public static int id_ref = 50;
1193  public static int id_state = 51;
1194  public static int id_status = 52;
1195  public static int ip_addr = 53;
1196  public static int is_compound = 54;
1197  public static int is_tax_included = 55;
1198  public static int isbn = 56;
1199  public static int ix_address = 57;
1200  public static int len_file = 58;
1201  public static int memory = 59;
1202  public static int mn_city = 60;
1203  public static int mobilephone = 61;
1204  public static int mov_phone = 62;
1205  public static int nm_category = 63;
1206  public static int nm_company = 64;
1207  public static int nm_country = 65;
1208  public static int nm_product = 66;
1209  public static int nm_state = 67;
1210  public static int nm_street = 68;
1211  public static int nu_current_stock = 69;
1212  public static int nu_lines = 70;
1213  public static int nu_min_stock = 71;
1214  public static int nu_reserved_stock = 72;
1215  public static int nu_street = 73;
1216  public static int od_position = 74;
1217  public static int office = 75;
1218  public static int ordinal = 76;
1219  public static int organization = 77;
1220  public static int other_phone = 78;
1221  public static int pages = 79;
1222  public static int paragraphs = 80;
1223  public static int pct_tax_rate = 81;
1224  public static int pg_prod_locat = 82;
1225  public static int phone1 = 83;
1226  public static int phone2 = 84;
1227  public static int po_box = 85;
1228  public static int power = 86;
1229  public static int pr_discount = 87;
1230  public static int pr_list = 88;
1231  public static int pr_purchase = 89;
1232  public static int pr_sale = 90;
1233  public static int product_group = 91;
1234  public static int project = 92;
1235  public static int rank = 93;
1236  public static int reference_id = 94;
1237  public static int revised_by = 95;
1238  public static int rooms = 96;
1239  public static int scope = 97;
1240  public static int signature = 98;
1241  public static int size_x = 99;
1242  public static int size_y = 100;
1243  public static int size_z = 101;
1244  public static int speed = 102;
1245  public static int state = 103;
1246  public static int state_code = 104;
1247  public static int status = 105;
1248  public static int subject = 106;
1249  public static int tag_prod_locat = 107;
1250  public static int tag_product = 108;
1251  public static int target = 109;
1252  public static int template = 110;
1253  public static int tp_fare = 111;
1254  public static int tp_location = 112;
1255  public static int tp_street = 113;
1256  public static int tx_addr1 = 114;
1257  public static int tx_addr2 = 115;
1258  public static int tx_email = 116;
1259  public static int tx_email_alt = 117;
1260  public static int tx_keywords = 118;
1261  public static int tx_remarks = 119;
1262  public static int tx_salutation = 120;
1263  public static int typeof = 121;
1264  public static int upload_by = 122;
1265  public static int url_addr = 123;
1266  public static int vs_stamp = 124;
1267  public static int weight = 125;
1268  public static int words = 126;
1269  public static int work_phone = 127;
1270  public static int xanchor = 128;
1271  public static int xfile = 129;
1272  public static int xhost = 130;
1273  public static int xoriginalfile = 131;
1274  public static int xpath = 132;
1275  public static int xport = 133;
1276  public static int xprotocol = 134;
1277  public static int zip_code = 135;
1278
1279}
1280
Popular Tags