KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > knowgate > crm > ContactLoader


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.crm;
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.misc.Gadgets;
50 import com.knowgate.hipergate.Address;
51 import com.knowgate.hipergate.DBLanguages;
52 import com.knowgate.hipergate.datamodel.ColumnList;
53 import com.knowgate.hipergate.datamodel.ImportLoader;
54
55 /**
56  * <p>Load Contact, Company and Address data from a single source</p>
57  * Contact loader creates or updates simultaneously registers at k_companies,
58  * k_contacts and k_addresses tables and the links between them k_x_contact_addr.
59  * <br><br>
60  * @author Sergio Montoro Ten
61  * @version 1.0
62  */

63 public class ContactLoader implements ImportLoader {
64
65   // ---------------------------------------------------------------------------
66

67   private Object JavaDoc[] aValues;
68   private PreparedStatement JavaDoc oCompUpdt, oContUpdt, oAddrUpdt;
69   private PreparedStatement JavaDoc oCompInst, oContInst, oAddrInst, oContAddr, oCompAddr;
70   private PreparedStatement JavaDoc oCompLook, oContLook, oAddrLook;
71   private PreparedStatement JavaDoc oCompWook, oContWook, oAddrWook;
72   private PreparedStatement JavaDoc oCompName, oContPort;
73   private PreparedStatement JavaDoc oAddrComp, oAddrCont;
74   private HashMap JavaDoc oCompSectorsMap, oCompStatusMap, oCompTypesMap;
75   private HashMap JavaDoc oContGendersMap, oContStatusMap, oContDeptsMap, oContDivsMap, oContTitlesMap;
76   private HashMap JavaDoc oAddrLocsMap, oAddrTypesMap, oAddrSalutMap;
77
78   // ---------------------------------------------------------------------------
79

80   private void init() {
81     aValues = new Object JavaDoc[ColumnNames.length];
82     for (int c = aValues.length - 1; c >= 0; c--) aValues[c] = null;
83     oCompInst=oContInst=oAddrInst=oContAddr=oCompAddr=null;
84     oCompUpdt=oContUpdt=oAddrUpdt=null;
85     oCompLook=oContLook=oAddrLook=null;
86     oCompWook=oContWook=oAddrWook=null;
87     oCompSectorsMap = new HashMap JavaDoc();
88     oCompStatusMap = new HashMap JavaDoc();
89     oCompTypesMap = new HashMap JavaDoc();
90     oContGendersMap = new HashMap JavaDoc();
91     oContStatusMap = new HashMap JavaDoc();
92     oContDeptsMap = new HashMap JavaDoc();
93     oContDivsMap = new HashMap JavaDoc();
94     oContTitlesMap = new HashMap JavaDoc();
95     oAddrLocsMap = new HashMap JavaDoc();
96     oAddrTypesMap = new HashMap JavaDoc();
97     oAddrSalutMap = new HashMap JavaDoc();
98   }
99
100   // ---------------------------------------------------------------------------
101

102   /**
103    * Default construtor
104    */

105   public ContactLoader() {
106     init();
107   }
108
109   // ---------------------------------------------------------------------------
110

111   /**
112    * Create ContactLoader and call prepare() on Connection
113    * @param oConn Connection
114    * @throws SQLException
115    */

116   public ContactLoader(Connection JavaDoc oConn) throws SQLException JavaDoc {
117     init();
118     prepare(oConn, null);
119   }
120
121   // ---------------------------------------------------------------------------
122

123   /**
124    * Set all column values to null
125    */

126   public void setAllColumnsToNull() {
127     if (DebugFile.trace) {
128       DebugFile.writeln("Begin ContactLoader.setAllColumnsToNull()");
129       DebugFile.incIdent();
130     }
131
132     for (int c=aValues.length-1; c>=0; c--)
133       aValues[c] = null;
134
135     if (DebugFile.trace) {
136       DebugFile.decIdent();
137       DebugFile.writeln("End ContactLoader.setAllColumnsToNull()");
138     }
139   } // setAllColumnsToNull
140

141   // ---------------------------------------------------------------------------
142

143   /**
144    * <p>Get column index at ColumnNames array given its name</p>
145    * This method performs binary search assuming that ColumnNames is sorted in
146    * ascending order
147    * @param sColumnName String Column name (case insensitive)
148    * @return int Column index or -1 if not found
149    */

150    public int getColumnIndex(String JavaDoc sColumnName) {
151     int iIndex = Arrays.binarySearch(ColumnNames, sColumnName, String.CASE_INSENSITIVE_ORDER);
152     if (iIndex<0) iIndex=-1;
153     return iIndex;
154   }
155
156   // ---------------------------------------------------------------------------
157

158   public int columnCount() {
159     return aValues.length;
160   }
161
162   // ---------------------------------------------------------------------------
163

164   public String JavaDoc[] columnNames() throws IllegalStateException JavaDoc {
165     return ColumnNames;
166   }
167
168   // ---------------------------------------------------------------------------
169

170   /**
171    * Put value for a given column
172    * @param iColumnIndex Column index [0..getColumnCount()-1]
173    * @param oValue Value for column
174    * @throws ArrayIndexOutOfBoundsException
175    */

176   public void put(int iColumnIndex, Object JavaDoc oValue)
177     throws ArrayIndexOutOfBoundsException JavaDoc {
178     aValues[iColumnIndex] = oValue;
179   }
180
181   // ---------------------------------------------------------------------------
182

183   /**
184    * <p>Put value for a given column</p>
185    * If a previous value already exists then it is replaced
186    * @param sColumnName Column name (case sensitive)
187    * @param oValue Value for column
188    * @throws ArrayIndexOutOfBoundsException
189    */

190   public void put(String JavaDoc sColumnName, Object JavaDoc oValue)
191     throws ArrayIndexOutOfBoundsException JavaDoc {
192     int iColumnIndex = getColumnIndex(sColumnName.toLowerCase());
193     if (-1==iColumnIndex) throw new ArrayIndexOutOfBoundsException JavaDoc("Cannot find column named "+sColumnName);
194     aValues[iColumnIndex] = oValue;
195   }
196
197   // ---------------------------------------------------------------------------
198

199   /**
200    * Put all values from a map on their corresponding columns matching by name
201    * @param oValues Map
202    */

203   public void putAll(Map JavaDoc oValues) {
204     int iColumnIndex;
205     String JavaDoc sColumnName;
206     if (DebugFile.trace) {
207       DebugFile.writeln("Begin ContactLoader.putAll()");
208       DebugFile.incIdent();
209     }
210     Iterator JavaDoc oIter = oValues.keySet().iterator();
211     while (oIter.hasNext()) {
212       sColumnName = (String JavaDoc) oIter.next();
213       iColumnIndex = getColumnIndex(sColumnName.toLowerCase());
214       if (iColumnIndex>0) {
215         Object JavaDoc oVal = oValues.get(sColumnName);
216         if (oVal==null)
217           aValues[iColumnIndex] = null;
218         else if (oVal.getClass().getName().startsWith("[L")) {
219           aValues[iColumnIndex] = java.lang.reflect.Array.get(oVal,0);
220         } else {
221           aValues[iColumnIndex] = oVal;
222         }
223         if (DebugFile.trace) DebugFile.writeln(sColumnName.toLowerCase()+"="+aValues[iColumnIndex]);
224       } else {
225         if (DebugFile.trace) DebugFile.writeln(sColumnName + " not found");
226       }// fi (iColumnIndex)
227
} // wend
228
if (DebugFile.trace) {
229       DebugFile.decIdent();
230       DebugFile.writeln("End ContactLoader.putAll()");
231     }
232   } // putAll
233

234   // ---------------------------------------------------------------------------
235

236   /**
237    * Get column by index
238    * @param iColumnIndex int Colunm index [0..getColumnCount()-1]
239    * @return Object Column value
240    * @throws ArrayIndexOutOfBoundsException
241    */

242   public Object JavaDoc get(int iColumnIndex)
243     throws ArrayIndexOutOfBoundsException JavaDoc {
244     return aValues[iColumnIndex];
245   } // get
246

247   // ---------------------------------------------------------------------------
248

249   /**
250    * Get column by name
251    * @param sColumnName String Column name (case sensitive)
252    * @return Object Column value
253    * @throws ArrayIndexOutOfBoundsException If no column with sucjh name was found
254    */

255   public Object JavaDoc get(String JavaDoc sColumnName)
256     throws ArrayIndexOutOfBoundsException JavaDoc {
257     int iColumnIndex = getColumnIndex(sColumnName.toLowerCase());
258     if (-1==iColumnIndex) throw new ArrayIndexOutOfBoundsException JavaDoc("Cannot find column named "+sColumnName);
259     return aValues[iColumnIndex];
260   }
261
262   // ---------------------------------------------------------------------------
263

264   /**
265    * <p>Prepare statements for execution</p>
266    * This method needs to be called only once if the default constructor was used.<br>
267    * If ContactLoader(Connection) constructor was used, there is no need to call prepare()
268    * and a SQLException will be raised if the attempt is made.<br>
269    * It is neccesary to call close() always for prepared instances as a failure
270    * to do so will leave open cursors on the database causing it eventually to stop.
271    * @param oConn Connection Open JDBC database connection
272    * @param oColList ColumnList This parameter is ignored
273    * @throws SQLException
274    */

275   public void prepare(Connection JavaDoc oConn, ColumnList oColList)
276     throws SQLException JavaDoc {
277
278     if (DebugFile.trace) {
279       DebugFile.writeln("Begin ContactLoader.prepare()");
280       DebugFile.incIdent();
281     }
282
283     if (oCompUpdt!=null || oCompInst!=null || oCompLook!=null || oCompWook!=null || oContAddr!=null || oContAddr!=null) {
284       if (DebugFile.trace) DebugFile.decIdent();
285       throw new SQLException JavaDoc("Either ContactLoader.prepare() has already been called or statements were not properly closed","HY010");
286     }
287
288     oCompUpdt = oConn.prepareStatement("UPDATE k_companies SET nm_legal=?,gu_workarea=?,nm_commercial=?,dt_modified=?,dt_founded=?,id_legal=?,id_sector=?,id_status=?,id_ref=?,tp_company=?,gu_geozone=?,nu_employees=?,im_revenue=?,gu_sales_man=?,tx_franchise=?,de_company=? WHERE gu_company=? OR (nm_legal=? AND gu_workarea=?)");
289     oCompInst = oConn.prepareStatement("INSERT INTO k_companies (nm_legal,gu_workarea,nm_commercial,dt_modified,dt_founded,id_legal,id_sector,id_status,id_ref,tp_company,gu_geozone,nu_employees,im_revenue,gu_sales_man,tx_franchise,de_company,gu_company) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
290     oCompLook = oConn.prepareStatement("SELECT NULL FROM k_companies_lookup WHERE gu_owner=? AND id_section=? AND vl_lookup=?",ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
291     oCompWook = oConn.prepareStatement("INSERT INTO k_companies_lookup (gu_owner,id_section,pg_lookup,vl_lookup,tr_es,tr_en,tr_de,tr_it,tr_fr,tr_pt,tr_ca,tr_eu,tr_ja,tr_cn,tr_tw,tr_fi,tr_ru) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
292     oCompName = oConn.prepareStatement("SELECT gu_company FROM k_companies WHERE nm_legal=? AND gu_workarea=?",ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
293
294     oContUpdt = oConn.prepareStatement("UPDATE k_contacts SET gu_workarea=?,tx_nickname=?,tx_pwd=?,tx_challenge=?,tx_reply=?,dt_pwd_expires=?,dt_modified=?,gu_writer=?,gu_company=?,id_status=?,id_ref=?,tx_name=?,tx_surname=?,de_title=?,id_gender=?,dt_birth=?,ny_age=?,sn_passport=?,tp_passport=?,sn_drivelic=?,dt_drivelic=?,tx_dept=?,tx_division=?,gu_geozone=?,tx_comments=? WHERE gu_contact=? OR (tx_name=? AND tx_surname=? AND gu_workarea=?)");
295     oContInst = oConn.prepareStatement("INSERT INTO k_contacts (gu_workarea,tx_nickname,tx_pwd,tx_challenge,tx_reply,dt_pwd_expires,dt_modified,gu_writer,gu_company,id_status,id_ref,tx_name,tx_surname,de_title,id_gender,dt_birth,ny_age,sn_passport,tp_passport,sn_drivelic,dt_drivelic,tx_dept,tx_division,gu_geozone,tx_comments,gu_contact) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
296     oContLook = oConn.prepareStatement("SELECT NULL FROM k_contacts_lookup WHERE gu_owner=? AND id_section=? AND vl_lookup=?",ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
297     oContWook = oConn.prepareStatement("INSERT INTO k_contacts_lookup (gu_owner,id_section,pg_lookup,vl_lookup,tr_es,tr_en,tr_de,tr_it,tr_fr,tr_pt,tr_ca,tr_eu,tr_ja,tr_cn,tr_tw,tr_fi,tr_ru) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
298     oContPort = oConn.prepareStatement("SELECT gu_contact FROM k_contacts WHERE sn_passport=? AND gu_workarea=?",ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
299
300     oAddrUpdt = oConn.prepareStatement("UPDATE k_addresses SET 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=?,id_ref=?,tx_remarks=? WHERE gu_address=? OR (tx_email=? AND gu_workarea=?)");
301     oAddrInst = oConn.prepareStatement("INSERT INTO k_addresses (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,id_ref,tx_remarks,gu_address) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
302     oAddrLook = oConn.prepareStatement("SELECT NULL FROM k_addresses_lookup WHERE gu_owner=? AND id_section=? AND vl_lookup=?",ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
303     oAddrWook = oConn.prepareStatement("INSERT INTO k_addresses_lookup (gu_owner,id_section,pg_lookup,vl_lookup,tr_es,tr_en,tr_de,tr_it,tr_fr,tr_pt,tr_ca,tr_eu,tr_ja,tr_cn,tr_tw,tr_fi,tr_ru) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
304     oAddrComp = oConn.prepareStatement("SELECT a.gu_address FROM k_addresses a, k_x_company_addr x WHERE a.gu_address=x.gu_address AND a.gu_workarea=? AND a.ix_address=? AND x.gu_company=?",ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
305     oAddrCont = oConn.prepareStatement("SELECT a.gu_address FROM k_addresses a, k_x_contact_addr x WHERE a.gu_address=x.gu_address AND a.gu_workarea=? AND a.ix_address=? AND x.gu_contact=?",ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
306
307     oContAddr = oConn.prepareStatement("INSERT INTO k_x_contact_addr (gu_contact,gu_address) VALUES(?,?)");
308     oCompAddr = oConn.prepareStatement("INSERT INTO k_x_company_addr (gu_company,gu_address) VALUES(?,?)");
309
310     if (DebugFile.trace) {
311       DebugFile.decIdent();
312       DebugFile.writeln("End ContactLoader.prepare()");
313     }
314   } // prepare
315

316   // ---------------------------------------------------------------------------
317

318   /**
319    * <p>Close prepared statements</p>
320    * This method must always be called before object is destroyed or else
321    * @throws SQLException
322    */

323   public void close()
324     throws SQLException JavaDoc {
325
326     oCompSectorsMap.clear();
327     oCompStatusMap.clear();
328     oCompTypesMap.clear();
329     oContGendersMap.clear();
330     oContStatusMap.clear();
331     oContDeptsMap.clear();
332     oContDivsMap.clear();
333     oContTitlesMap.clear();
334     oAddrLocsMap.clear();
335     oAddrTypesMap.clear();
336     oAddrSalutMap.clear();
337
338     if (oAddrComp!=null) { oAddrComp.close(); oAddrComp=null; }
339     if (oAddrCont!=null) { oAddrCont.close(); oAddrCont=null; }
340
341     if (oCompAddr!=null) { oCompAddr.close(); oCompAddr=null; }
342     if (oContAddr!=null) { oContAddr.close(); oContAddr=null; }
343
344     if (oCompUpdt!=null) { oCompUpdt.close(); oCompUpdt=null; }
345     if (oCompInst!=null) { oCompInst.close(); oCompInst=null; }
346     if (oCompLook!=null) { oCompLook.close(); oCompLook=null; }
347     if (oCompWook!=null) { oCompWook.close(); oCompWook=null; }
348     if (oCompName!=null) { oCompName.close(); oCompName=null; }
349
350     if (oContUpdt!=null) { oContUpdt.close(); oContUpdt=null; }
351     if (oContInst!=null) { oContInst.close(); oContInst=null; }
352     if (oContLook!=null) { oContLook.close(); oContLook=null; }
353     if (oContWook!=null) { oContWook.close(); oContWook=null; }
354     if (oContPort!=null) { oContPort.close(); oContPort=null; }
355
356     if (oAddrUpdt!=null) { oAddrUpdt.close(); oAddrUpdt=null; }
357     if (oAddrInst!=null) { oAddrInst.close(); oAddrInst=null; }
358     if (oAddrLook!=null) { oAddrLook.close(); oAddrLook=null; }
359     if (oAddrWook!=null) { oAddrWook.close(); oAddrWook=null; }
360   } // close
361

362   // ---------------------------------------------------------------------------
363

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

368   // ---------------------------------------------------------------------------
369

370   /**
371    * Add a lookup value to a table
372    * @param sSection String Section. Usually the name of the column at the base table
373    * @param sValue String Internal hidden value of the lookup
374    * @param oConn Connection
375    * @param oSelStmt PreparedStatement
376    * @param oInsStmt PreparedStatement
377    * @param oCacheMap HashMap
378    * @throws SQLException
379    */

380   private void addLookUp(String JavaDoc sSection, String JavaDoc sValue, Connection JavaDoc oConn,
381                          PreparedStatement JavaDoc oSelStmt, PreparedStatement JavaDoc oInsStmt,
382                          HashMap JavaDoc oCacheMap) throws SQLException JavaDoc {
383     String JavaDoc sTr;
384     char[] aTr;
385     final String JavaDoc EmptyStr = "";
386     boolean bExistsLookup;
387
388     if (DebugFile.trace) {
389       DebugFile.writeln("Begin ContactLoader.addLookUp("+sSection+","+sValue+","+
390                         "[Connection],[PreparedStatement],[PreparedStatement],[HashMap]");
391       DebugFile.incIdent();
392     }
393
394     if (null==sValue) sValue = EmptyStr;
395     if (!EmptyStr.equals(sValue)) {
396       if (!oCacheMap.containsKey(sValue)) {
397         oSelStmt.setObject(1, get(gu_workarea), Types.CHAR);
398         oSelStmt.setString(2, sSection);
399         oSelStmt.setString(3, sValue);
400         ResultSet JavaDoc oRSet = oSelStmt.executeQuery();
401         bExistsLookup = oRSet.next();
402         oRSet.close();
403         if (!bExistsLookup) {
404           aTr = ((String JavaDoc) ColumnNames[id_sector]).toLowerCase().toCharArray();
405           aTr[0] = Character.toUpperCase(aTr[0]);
406           sTr = new String JavaDoc(aTr);
407           oInsStmt.setObject(1, get(gu_workarea), Types.CHAR);
408           oInsStmt.setString(2, "id_sector");
409           oInsStmt.setInt(3, DBLanguages.nextLookuUpProgressive(oConn, "k_companies_lookup", (String JavaDoc) get(gu_workarea), "id_sector"));
410           oInsStmt.setObject(4, ColumnNames[id_sector], Types.VARCHAR);
411           for (int t=5; t<=17; t++) oCompWook.setString(t, sTr);
412           oInsStmt.executeUpdate();
413         } // fi (!bExistsLookup)
414
oCacheMap.put(sValue, sValue);
415       } // fi (!oCacheMap.containsKey(sValue))
416
}
417
418     if (DebugFile.trace) {
419       DebugFile.decIdent();
420       DebugFile.writeln("End ContactLoader.addLookUp()");
421     }
422   } // addLookUp
423

424   // ---------------------------------------------------------------------------
425

426   private String JavaDoc getCompanyGuid(Connection JavaDoc oConn, Object JavaDoc sCompanyLegalname, Object JavaDoc sWorkArea)
427     throws SQLException JavaDoc {
428     String JavaDoc sCompGuid;
429     oCompName.setObject(1, sCompanyLegalname, Types.VARCHAR);
430     oCompName.setObject(2, sWorkArea, Types.VARCHAR);
431     ResultSet JavaDoc oRSet = oCompName.executeQuery();
432     if (oRSet.next())
433       sCompGuid = oRSet.getString(1);
434     else
435       sCompGuid = null;
436     oRSet.close();
437     if (null==sCompGuid)
438       sCompGuid = Gadgets.generateUUID();
439     return sCompGuid;
440   } // getCompanyGuid
441

442   // ---------------------------------------------------------------------------
443

444   private String JavaDoc getContactGuid(Connection JavaDoc oConn, Object JavaDoc sContactPassport, Object JavaDoc sWorkArea)
445     throws SQLException JavaDoc {
446     String JavaDoc sContGuid;
447     oContPort.setObject(1, sContactPassport, Types.VARCHAR);
448     oContPort.setObject(2, sWorkArea, Types.VARCHAR);
449     ResultSet JavaDoc oRSet = oContPort.executeQuery();
450     if (oRSet.next())
451       sContGuid = oRSet.getString(1);
452     else
453       sContGuid = null;
454     oRSet.close();
455     if (null==sContGuid)
456       sContGuid = Gadgets.generateUUID();
457     return sContGuid;
458   } // getContactGuid
459

460   // ---------------------------------------------------------------------------
461

462   private String JavaDoc getAddressGuid(Connection JavaDoc oConn, Object JavaDoc oIxAddr, Object JavaDoc oGuWrkA,
463                                 Object JavaDoc oGuCont, Object JavaDoc oGuComp, int iFlags)
464     throws SQLException JavaDoc {
465     String JavaDoc sAddrGuid;
466     ResultSet JavaDoc oRSet;
467     if (oIxAddr==null) return Gadgets.generateUUID();
468     if (test(iFlags, WRITE_CONTACTS)) {
469       oAddrCont.setObject(1, oGuWrkA, Types.CHAR);
470       oAddrCont.setObject(2, oIxAddr, Types.INTEGER);
471       oAddrCont.setObject(3, oGuCont, Types.CHAR);
472       oRSet = oAddrCont.executeQuery();
473       if (oRSet.next())
474         sAddrGuid = oRSet.getString(1);
475       else
476         sAddrGuid = null;
477       oRSet.close();
478     } else {
479       oAddrComp.setObject(1, oGuWrkA, Types.CHAR);
480       oAddrComp.setObject(2, oIxAddr, Types.INTEGER);
481       oAddrComp.setObject(3, oGuComp, Types.CHAR);
482       oRSet = oAddrComp.executeQuery();
483       if (oRSet.next())
484         sAddrGuid = oRSet.getString(1);
485       else
486         sAddrGuid = null;
487       oRSet.close();
488     }
489     if (null==sAddrGuid) sAddrGuid = Gadgets.generateUUID();
490     return sAddrGuid;
491   } // getAddressGuid
492

493   // ---------------------------------------------------------------------------
494

495   private String JavaDoc getColNull (int iColIndex)
496     throws ArrayIndexOutOfBoundsException JavaDoc,ClassCastException JavaDoc {
497     if (DebugFile.trace) {
498       if (iColIndex<0 || iColIndex>=aValues.length)
499         throw new ArrayIndexOutOfBoundsException JavaDoc("ContactLoader.getColNull() column index "+String.valueOf(iColIndex)+" must be in the range between 0 and "+String.valueOf(aValues.length));
500       DebugFile.writeln("ContactLoader.getColNull("+String.valueOf(iColIndex)+") : "+aValues[iColIndex]);
501     }
502     String JavaDoc sRetVal;
503     if (null==aValues[iColIndex])
504       sRetVal = null;
505     else {
506       try {
507         sRetVal = aValues[iColIndex].toString();
508       } catch (ClassCastException JavaDoc cce){
509         if (aValues[iColIndex]==null)
510           throw new ClassCastException JavaDoc("ContactLoader.getColNull("+String.valueOf(iColIndex)+") could not cast null to String");
511         else
512           throw new ClassCastException JavaDoc("ContactLoader.getColNull("+String.valueOf(iColIndex)+") could not cast "+aValues[iColIndex].getClass().getName()+" "+aValues[iColIndex]+" to String");
513       }
514       if (sRetVal.length()==0 || sRetVal.equalsIgnoreCase("null"))
515         sRetVal = null;
516     }
517     return sRetVal;
518   } // getColNull
519

520   // ---------------------------------------------------------------------------
521

522   /**
523    * Store properties curently held in RAM into the database
524    * @param oConn Opened JDBC connection
525    * @param sWorkArea String GUID of WorkArea to which inserted data will belong
526    * @param iFlags int A boolean combination of {MODE_APPEND|MODE_UPDATE|WRITE_COMPANIES|WRITE_CONTACTS|WRITE_ADDRESSES|WRITE_LOOKUPS|NO_DUPLICATED_NAMES|NO_DUPLICATED_MAILS}
527    * @throws SQLException
528    * @throws IllegalArgumentException
529    * @throws NullPointerException
530    * @throws ClassCastException
531    */

532   public void store(Connection JavaDoc oConn, String JavaDoc sWorkArea, int iFlags)
533     throws SQLException JavaDoc,IllegalArgumentException JavaDoc,NullPointerException JavaDoc,ClassCastException JavaDoc {
534
535     if (oCompUpdt==null || oContUpdt==null || oAddrUpdt==null)
536       throw new SQLException JavaDoc("Invalid command sequece. Must call ContactLoader.prepare() before ContactLoader.store()");
537
538     if (!test(iFlags,MODE_APPEND) && !test(iFlags,MODE_UPDATE))
539       throw new IllegalArgumentException JavaDoc("ContactLoader.store() Flags bitmask must contain either MODE_APPEND, MODE_UPDATE or both");
540
541     if (!test(iFlags,WRITE_COMPANIES) && !test(iFlags,WRITE_CONTACTS))
542       throw new IllegalArgumentException JavaDoc("ContactLoader.store() Flags bitmask must contain either WRITE_COMPANIES, WRITE_CONTACTS or both");
543
544     if (null==sWorkArea)
545       throw new NullPointerException JavaDoc("ContactLoader.store() Default WorkArea cannot be null");
546
547     if (null==getColNull(nm_legal) && test(iFlags,WRITE_COMPANIES))
548       throw new NullPointerException JavaDoc("ContactLoader.store() nm_legal cannot be null");
549
550     if (null==getColNull(nm_legal) && test(iFlags,WRITE_COMPANIES))
551       throw new NullPointerException JavaDoc("ContactLoader.store() nm_legal cannot be null");
552
553     if (null==getColNull(gu_company) && test(iFlags,WRITE_COMPANIES) && test(iFlags,MODE_UPDATE) && !test(iFlags,MODE_APPEND))
554       throw new NullPointerException JavaDoc("ContactLoader.store() gu_company cannot be null when using UPDATE mode");
555
556     if (null==getColNull(gu_contact) && test(iFlags,WRITE_CONTACTS) && test(iFlags,MODE_UPDATE) && !test(iFlags,MODE_APPEND))
557       throw new NullPointerException JavaDoc("ContactLoader.store() gu_contact cannot be null when using UPDATE mode");
558
559     if (null==getColNull(gu_address) && test(iFlags,WRITE_ADDRESSES) && test(iFlags,MODE_UPDATE) && !test(iFlags,MODE_APPEND))
560       throw new NullPointerException JavaDoc("ContactLoader.store() gu_address cannot be null when using UPDATE mode");
561
562     if (DebugFile.trace) {
563       DebugFile.writeln("Begin ContactLoader.store([Connection],"+sWorkArea+","+String.valueOf(iFlags)+")");
564       DebugFile.incIdent();
565       StringBuffer JavaDoc oRow = new StringBuffer JavaDoc();
566       oRow.append('{');
567       oRow.append(ColumnNames[0]+"=");
568       oRow.append(aValues[0]==null ? "null" : aValues[0]);
569       for (int d=1; d<aValues.length; d++) {
570         oRow.append(","+ColumnNames[d]+"=");
571         oRow.append(aValues[d]==null ? "null" : aValues[d]);
572       } // next
573
oRow.append('}');
574       DebugFile.writeln(oRow.toString());
575     }
576
577     int iAffected;
578     Timestamp JavaDoc tsNow = new Timestamp JavaDoc(new Date JavaDoc().getTime());
579
580     if (null==get(gu_workarea)) {
581       if (DebugFile.trace) DebugFile.writeln("setting workarea to "+sWorkArea);
582       put(gu_workarea, sWorkArea);
583     } else {
584       if (DebugFile.trace) DebugFile.writeln("workarea for current record is "+getColNull(gu_workarea));
585     }
586     if (test(iFlags,WRITE_COMPANIES) && getColNull(gu_company)==null)
587       put(gu_company, getCompanyGuid(oConn, aValues[nm_legal], get(gu_workarea)));
588     if (test(iFlags,WRITE_CONTACTS) && getColNull(gu_contact)==null)
589       put(gu_contact, getContactGuid(oConn, aValues[sn_passport], get(gu_workarea)));
590     if (test(iFlags,WRITE_ADDRESSES) && aValues[gu_address]==null)
591       put(gu_address, getAddressGuid(oConn, aValues[ix_address], get(gu_workarea), get(gu_contact), get(gu_company), iFlags));
592
593     if (test(iFlags,WRITE_COMPANIES)) {
594       if (test(iFlags,WRITE_LOOKUPS)) {
595         addLookUp("id_sector", getColNull(id_sector), oConn, oCompLook, oCompWook, oCompSectorsMap);
596         addLookUp("id_status", getColNull(id_company_status), oConn, oCompLook, oCompWook, oCompStatusMap);
597         addLookUp("tp_company", getColNull(tp_company), oConn, oCompLook, oCompWook, oCompTypesMap);
598       } // if (test(WRITE_LOOKUPS))
599

600       iAffected = 0;
601       if ((test(iFlags,MODE_UPDATE) || test(iFlags,WRITE_CONTACTS)) &&
602           (getColNull(nm_legal)!=null || getColNull(gu_company)!=null)) {
603         if (DebugFile.trace) DebugFile.writeln("COMPANY MODE_UPDATE AND IS NOT NEW COMPANY");
604         oCompUpdt.setString(1, getColNull(nm_legal));
605          if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setObject(2, "+aValues[gu_workarea]+", Types.CHAR)");
606         oCompUpdt.setObject(2, aValues[gu_workarea], Types.CHAR);
607         oCompUpdt.setString(3, getColNull(nm_commercial));
608         if (aValues[dt_modified]==null)
609           oCompUpdt.setTimestamp(4, tsNow);
610         else {
611           if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setObject(4, "+aValues[dt_modified]+", Types.TIMESTAMP)");
612           oCompUpdt.setObject(4, aValues[dt_modified], Types.TIMESTAMP);
613         }
614         if (aValues[dt_founded]==null)
615           oCompUpdt.setNull(5, Types.TIMESTAMP);
616         else {
617           if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setObject(5, "+aValues[dt_founded]+", Types.TIMESTAMP)");
618           oCompUpdt.setObject(5, aValues[dt_founded], Types.TIMESTAMP);
619         }
620         oCompUpdt.setString(6, getColNull(id_legal));
621         oCompUpdt.setString(7, getColNull(id_sector));
622         oCompUpdt.setString(8, getColNull(id_company_status));
623         oCompUpdt.setString(9, getColNull(id_company_ref));
624         oCompUpdt.setString(10, getColNull(tp_company));
625         oCompUpdt.setString(11, getColNull(gu_geozone));
626         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setObject(12, "+aValues[nu_employees]+", Types.INTEGER)");
627         oCompUpdt.setObject(12, aValues[nu_employees], Types.INTEGER);
628         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setObject(13, "+aValues[im_revenue]+", Types.FLOAT)");
629         oCompUpdt.setObject(13, aValues[im_revenue], Types.FLOAT);
630         oCompUpdt.setString(14, getColNull(gu_sales_man));
631         oCompUpdt.setString(15, getColNull(tx_franchise));
632         oCompUpdt.setString(16, getColNull(de_company));
633         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setString(17,"+aValues[gu_company]+")");
634         oCompUpdt.setString(17, (String JavaDoc) aValues[gu_company]);
635         oCompUpdt.setString(18, (String JavaDoc) aValues[nm_legal]);
636         oCompUpdt.setString(19, (String JavaDoc) aValues[gu_workarea]);
637         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.executeUpdate(oCompUpdt)");
638         iAffected = oCompUpdt.executeUpdate();
639         if (DebugFile.trace) DebugFile.writeln("affected="+String.valueOf(iAffected));
640       }
641       if (test(iFlags,MODE_APPEND) && (iAffected==0)) {
642         if (DebugFile.trace) DebugFile.writeln("COMPANY MODE_APPEND AND affected=0");
643         oCompInst.setString(1, getColNull(nm_legal));
644         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setObject(2, "+aValues[gu_workarea]+" "+getColNull(gu_workarea)+", Types.CHAR)");
645         oCompInst.setObject(2, aValues[gu_workarea], Types.CHAR);
646         oCompInst.setString(3, getColNull(nm_commercial));
647         if (aValues[dt_modified]==null) {
648           oCompInst.setTimestamp(4, tsNow);
649         } else {
650           if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setObject(4, "+aValues[dt_modified]+", Types.TIMESTAMP)");
651           oCompInst.setObject(4, aValues[dt_modified], Types.TIMESTAMP);
652         }
653         if (aValues[dt_founded]==null) {
654           oCompInst.setNull(5, Types.TIMESTAMP);
655         } else {
656           if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setObject(5, "+aValues[dt_founded]+", Types.TIMESTAMP)");
657           oCompInst.setObject(5, aValues[dt_founded], Types.TIMESTAMP);
658         }
659         oCompInst.setString(6, getColNull(id_legal));
660         oCompInst.setString(7, getColNull(id_sector));
661         oCompInst.setString(8, getColNull(id_company_status));
662         oCompInst.setString(9, getColNull(id_company_ref));
663         oCompInst.setString(10, getColNull(tp_company));
664         oCompInst.setString(11, getColNull(gu_geozone));
665         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setObject(12, "+aValues[nu_employees]+", Types.INTEGER)");
666         oCompInst.setObject(12, aValues[nu_employees], Types.INTEGER);
667         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setObject(13, "+aValues[im_revenue]+", Types.FLOAT)");
668         oCompInst.setObject(13, aValues[im_revenue], Types.FLOAT);
669         oCompInst.setString(14, getColNull(gu_sales_man));
670         oCompInst.setString(15, getColNull(tx_franchise));
671         oCompInst.setString(16, getColNull(de_company));
672         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setString(17,"+aValues[gu_company]+")");
673         oCompInst.setString(17, (String JavaDoc) aValues[gu_company]);
674         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.executeUpdate(oCompInst)");
675         iAffected = oCompInst.executeUpdate();
676         if (DebugFile.trace) DebugFile.writeln("affected="+String.valueOf(iAffected));
677       }
678     } // fi (bWriteCompanies)
679

680     if (test(iFlags,WRITE_CONTACTS)) {
681       if (test(iFlags,WRITE_LOOKUPS)) {
682         addLookUp("id_status", getColNull(id_contact_status), oConn, oCompLook, oCompWook, oContStatusMap);
683         addLookUp("de_title", getColNull(de_title), oConn, oCompLook, oCompWook, oContTitlesMap);
684         addLookUp("id_gender", getColNull(id_gender), oConn, oCompLook, oCompWook, oContGendersMap);
685         addLookUp("tx_dept", getColNull(tx_dept), oConn, oCompLook, oCompWook, oContDeptsMap);
686         addLookUp("tx_division", getColNull(tx_division), oConn, oCompLook, oCompWook, oContDivsMap);
687       } // if (test(WRITE_LOOKUPS))
688

689       iAffected = 0;
690       if (DebugFile.trace) DebugFile.writeln("MODE_UPDATE="+String.valueOf(test(iFlags,MODE_UPDATE))+" "+(getColNull(sn_passport)==null ? "sn_passport IS NULL" : "sn_passport IS NOT NULL")+" "+(getColNull(gu_contact)==null ? "gu_contact IS NULL" : "gu_contact IS NOT NULL"));
691       if (test(iFlags,MODE_UPDATE) && (getColNull(sn_passport)!=null || getColNull(gu_contact)!=null)) {
692         if (DebugFile.trace) DebugFile.writeln("CONTACT MODE_UPDATE AND IS NOT NEW CONTACT");
693         oContUpdt.setObject(1, aValues[gu_workarea], Types.CHAR);
694         oContUpdt.setString(2, getColNull(tx_nickname));
695         oContUpdt.setString(3, getColNull(tx_pwd));
696         oContUpdt.setString(4, getColNull(tx_challenge));
697         oContUpdt.setString(5, getColNull(tx_reply));
698         oContUpdt.setObject(6, aValues[dt_pwd_expires], Types.TIMESTAMP);
699         if (aValues[dt_modified]==null)
700           oContUpdt.setTimestamp(7, tsNow);
701         else
702           oContUpdt.setObject(7, aValues[dt_modified], Types.TIMESTAMP);
703         oContUpdt.setString(8, getColNull(gu_writer));
704         if (test(iFlags,WRITE_COMPANIES))
705           oContUpdt.setString(9, getColNull(gu_company));
706         else
707           oContUpdt.setNull(9,Types.CHAR);
708         oContUpdt.setString(10, getColNull(id_contact_status));
709         oContUpdt.setString(11, getColNull(id_contact_ref));
710         oContUpdt.setString(12, getColNull(tx_name));
711         oContUpdt.setString(13, getColNull(tx_surname));
712         oContUpdt.setString(14, getColNull(de_title));
713         oContUpdt.setString(15, getColNull(id_gender));
714         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setObject(16, "+aValues[dt_birth]+", Types.TIMESTAMP)");
715         oContUpdt.setObject(16, aValues[dt_birth], Types.TIMESTAMP);
716         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setObject(17, "+aValues[ny_age]+", Types.INTEGER)");
717         oContUpdt.setObject(17, aValues[ny_age], Types.INTEGER);
718         oContUpdt.setString(18, getColNull(sn_passport));
719         oContUpdt.setString(19, getColNull(tp_passport));
720         oContUpdt.setString(20, getColNull(sn_drivelic));
721         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setObject(21, "+aValues[dt_drivelic]+", Types.TIMESTAMP)");
722         oContUpdt.setObject(21, aValues[dt_drivelic], Types.TIMESTAMP);
723         oContUpdt.setString(22, getColNull(tx_dept));
724         oContUpdt.setString(23, getColNull(tx_division));
725         oContUpdt.setString(24, getColNull(gu_geozone));
726         oContUpdt.setString(25, getColNull(tx_comments));
727         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setString(26,"+aValues[gu_contact]+")");
728         oContUpdt.setString(26, (String JavaDoc) aValues[gu_contact]);
729         if (test(iFlags,NO_DUPLICATED_NAMES)) {
730           oContUpdt.setString(27, getColNull(tx_name));
731           oContUpdt.setString(28, getColNull(tx_surname));
732           oContUpdt.setString(29, (String JavaDoc) aValues[gu_workarea]);
733         } else {
734           oContUpdt.setNull(27, Types.VARCHAR);
735           oContUpdt.setNull(28, Types.VARCHAR);
736           oContUpdt.setNull(29, Types.CHAR);
737         }
738         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.executeUpdate(oContUpdt)");
739           iAffected = oContUpdt.executeUpdate();
740         if (DebugFile.trace) DebugFile.writeln("affected="+String.valueOf(iAffected));
741       } // fi (MODE_UPDATE && !bIsNewContact)
742

743       if (test(iFlags,MODE_APPEND) && (iAffected==0)) {
744         if (DebugFile.trace) DebugFile.writeln("CONTACT MODE_APPEND AND affected=0");
745         oContInst.setObject(1, aValues[gu_workarea], Types.CHAR);
746         oContInst.setString(2, getColNull(tx_nickname));
747         oContInst.setString(3, getColNull(tx_pwd));
748         oContInst.setString(4, getColNull(tx_challenge));
749         oContInst.setString(5, getColNull(tx_reply));
750         oContInst.setObject(6, aValues[dt_pwd_expires], Types.TIMESTAMP);
751         if (aValues[dt_modified]==null)
752           oContInst.setTimestamp(7, tsNow);
753         else
754           oContInst.setObject(7, aValues[dt_modified], Types.TIMESTAMP);
755         oContInst.setString(8, getColNull(gu_writer));
756         if (test(iFlags,WRITE_COMPANIES))
757           oContInst.setString(9, getColNull(gu_company));
758         else
759           oContInst.setNull(9,Types.CHAR);
760         oContInst.setString(10, getColNull(id_contact_status));
761         oContInst.setString(11, getColNull(id_contact_ref));
762         oContInst.setString(12, getColNull(tx_name));
763         oContInst.setString(13, getColNull(tx_surname));
764         oContInst.setString(14, getColNull(de_title));
765         oContInst.setString(15, getColNull(id_gender));
766         oContInst.setObject(16, aValues[dt_birth], Types.TIMESTAMP);
767         oContInst.setObject(17, aValues[ny_age], Types.INTEGER);
768         oContInst.setString(18, getColNull(sn_passport));
769         oContInst.setString(19, getColNull(tp_passport));
770         oContInst.setString(20, getColNull(sn_drivelic));
771         oContInst.setObject(21, aValues[dt_drivelic], Types.TIMESTAMP);
772         oContInst.setString(22, getColNull(tx_dept));
773         oContInst.setString(23, getColNull(tx_division));
774         oContInst.setString(24, getColNull(gu_geozone));
775         oContInst.setString(25, getColNull(tx_comments));
776         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setString(26,"+aValues[gu_contact]+")");
777         oContInst.setString(26, (String JavaDoc) aValues[gu_contact]);
778         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.executeUpdate(oContInst)");
779         iAffected = oContInst.executeUpdate();
780         if (DebugFile.trace) DebugFile.writeln("affected="+String.valueOf(iAffected));
781       } // fi (MODE_APPEND && iAffected==0)
782
} // fi (WRITE_CONTACTS)
783

784     if (test(iFlags,WRITE_LOOKUPS)) {
785       addLookUp("tp_location", getColNull(tp_location), oConn, oAddrLook, oAddrWook, oAddrLocsMap);
786       addLookUp("tp_street", getColNull(tp_street), oConn, oAddrLook, oAddrWook, oAddrTypesMap);
787       addLookUp("tx_salutation", getColNull(tp_street), oConn, oAddrLook, oAddrWook, oAddrSalutMap);
788     } // if (test(WRITE_LOOKUPS))
789

790     iAffected = 0;
791     if (test(iFlags,MODE_UPDATE) && getColNull(gu_address)!=null) {
792       if (DebugFile.trace) DebugFile.writeln("ADDRESS MODE_UPDATE AND IS NOT NEW ADDRESS");
793
794       if (null!=aValues[ix_address])
795         oAddrUpdt.setObject(1, aValues[ix_address], Types.INTEGER);
796       else {
797         if (test(iFlags,WRITE_CONTACTS))
798           oAddrUpdt.setInt(1, Address.nextLocalIndex(oConn, "k_x_contact_addr", "gu_contact", (String JavaDoc) aValues[gu_contact]));
799         else
800           oAddrUpdt.setInt(1, Address.nextLocalIndex(oConn, "k_x_company_addr", "gu_company", (String JavaDoc) aValues[gu_company]));
801       }
802       oAddrUpdt.setObject(2, aValues[gu_workarea], Types.CHAR);
803       if (null!=aValues[bo_active])
804         oAddrUpdt.setObject(3, aValues[bo_active], Types.SMALLINT);
805       else
806         oAddrUpdt.setShort(3, (short)1);
807       if (aValues[dt_modified]==null)
808         oAddrUpdt.setTimestamp(4, tsNow);
809       else
810         oAddrUpdt.setObject(4, aValues[dt_modified], Types.TIMESTAMP);
811       oAddrUpdt.setString(5, getColNull(tp_location));
812       if (test(iFlags,WRITE_COMPANIES))
813         oAddrUpdt.setString(6, (String JavaDoc) (getColNull(nm_commercial)==null ? getColNull(nm_legal) : aValues[nm_commercial]));
814       else
815         oAddrUpdt.setNull(6,Types.VARCHAR);
816       oAddrUpdt.setString(7, getColNull(tp_street));
817       oAddrUpdt.setString(8, getColNull(nm_street));
818       oAddrUpdt.setString(9, getColNull(nu_street));
819       oAddrUpdt.setString(10, getColNull(tx_addr1));
820       oAddrUpdt.setString(11, getColNull(tx_addr2));
821       oAddrUpdt.setString(12, getColNull(id_country));
822       oAddrUpdt.setString(13, getColNull(nm_country));
823       oAddrUpdt.setString(14, getColNull(id_state));
824       oAddrUpdt.setString(15, getColNull(nm_state));
825       oAddrUpdt.setString(16, getColNull(mn_city));
826       oAddrUpdt.setString(17, getColNull(zipcode));
827       oAddrUpdt.setString(18, getColNull(work_phone));
828       oAddrUpdt.setString(19, getColNull(direct_phone));
829       oAddrUpdt.setString(20, getColNull(home_phone));
830       oAddrUpdt.setString(21, getColNull(mov_phone));
831       oAddrUpdt.setString(22, getColNull(fax_phone));
832       oAddrUpdt.setString(23, getColNull(other_phone));
833       oAddrUpdt.setString(24, getColNull(po_box));
834       oAddrUpdt.setString(25, getColNull(tx_email));
835       oAddrUpdt.setString(26, getColNull(tx_email_alt));
836       oAddrUpdt.setString(27, getColNull(url_addr));
837       oAddrUpdt.setObject(28, aValues[coord_x], Types.FLOAT);
838       oAddrUpdt.setObject(29, aValues[coord_y], Types.FLOAT);
839       oAddrUpdt.setString(30, getColNull(contact_person));
840       oAddrUpdt.setString(31, getColNull(tx_salutation));
841       oAddrUpdt.setString(32, getColNull(id_address_ref));
842       oAddrUpdt.setString(33, getColNull(tx_remarks));
843       if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setString(34,"+aValues[gu_address]+")");
844       oAddrUpdt.setString(34, (String JavaDoc) aValues[gu_address]);
845       if (test(iFlags,NO_DUPLICATED_MAILS)) {
846         oAddrUpdt.setString(35, getColNull(tx_email));
847         oAddrUpdt.setString(36, (String JavaDoc) aValues[gu_workarea]);
848       } else {
849         oAddrUpdt.setNull(35, Types.VARCHAR);
850         oAddrUpdt.setNull(36, Types.CHAR);
851       }
852       if (DebugFile.trace) DebugFile.writeln("PreparedStatement.executeUpdate(oAddrUpdt)");
853       iAffected = oAddrUpdt.executeUpdate();
854       if (DebugFile.trace) DebugFile.writeln("affected="+String.valueOf(iAffected));
855     }
856
857     if (test(iFlags,MODE_APPEND) && (iAffected==0)) {
858       if (DebugFile.trace) DebugFile.writeln("ADDRESS MODE_APPEND AND affected=0");
859
860       if (null!=aValues[ix_address])
861         oAddrInst.setObject(1, aValues[ix_address], Types.INTEGER);
862       else
863         oAddrInst.setInt(1, Address.nextLocalIndex(oConn, "k_x_contact_addr", "gu_contact", (String JavaDoc) aValues[gu_contact]));
864       oAddrInst.setString(2, (String JavaDoc) aValues[gu_workarea]);
865       if (null!=aValues[bo_active])
866         oAddrInst.setObject(3, aValues[bo_active], Types.SMALLINT);
867       else
868         oAddrInst.setShort(3, (short)1);
869       if (aValues[dt_modified]==null)
870         oAddrInst.setTimestamp(4, tsNow);
871       else
872         oAddrInst.setObject(4, aValues[dt_modified], Types.TIMESTAMP);
873       oAddrInst.setString(5, getColNull(tp_location));
874       if (test(iFlags,WRITE_COMPANIES))
875         oAddrInst.setString(6, (String JavaDoc) (getColNull(nm_commercial)==null ? getColNull(nm_legal) : aValues[nm_commercial]));
876       else
877         oAddrInst.setNull(6,Types.VARCHAR);
878       oAddrInst.setString(7, getColNull(tp_street));
879       oAddrInst.setString(8, getColNull(nm_street));
880       oAddrInst.setString(9, getColNull(nu_street));
881       oAddrInst.setString(10, getColNull(tx_addr1));
882       oAddrInst.setString(11, getColNull(tx_addr2));
883       oAddrInst.setString(12, getColNull(id_country));
884       oAddrInst.setString(13, getColNull(nm_country));
885       oAddrInst.setString(14, getColNull(id_state));
886       oAddrInst.setString(15, getColNull(nm_state));
887       oAddrInst.setString(16, getColNull(mn_city));
888       oAddrInst.setString(17, getColNull(zipcode));
889       oAddrInst.setString(18, getColNull(work_phone));
890       oAddrInst.setString(19, getColNull(direct_phone));
891       oAddrInst.setString(20, getColNull(home_phone));
892       oAddrInst.setString(21, getColNull(mov_phone));
893       oAddrInst.setString(22, getColNull(fax_phone));
894       oAddrInst.setString(23, getColNull(other_phone));
895       oAddrInst.setString(24, getColNull(po_box));
896       oAddrInst.setString(25, getColNull(tx_email));
897       oAddrInst.setString(26, getColNull(tx_email_alt));
898       oAddrInst.setString(27, getColNull(url_addr));
899       oAddrInst.setObject(28, aValues[coord_x], Types.FLOAT);
900       oAddrInst.setObject(29, aValues[coord_y], Types.FLOAT);
901       oAddrInst.setString(30, getColNull(contact_person));
902       oAddrInst.setString(31, getColNull(tx_salutation));
903       oAddrInst.setString(32, getColNull(id_address_ref));
904       oAddrInst.setString(33, getColNull(tx_remarks));
905       if (DebugFile.trace) DebugFile.writeln("PreparedStatement.setString(34,"+aValues[gu_address]+")");
906       oAddrInst.setString(34, (String JavaDoc) aValues[gu_address]);
907       if (DebugFile.trace) DebugFile.writeln("PreparedStatement.executeUpdate(oAddrInst)");
908       iAffected = oAddrInst.executeUpdate();
909       if (DebugFile.trace) DebugFile.writeln("affected="+String.valueOf(iAffected));
910
911       if (test(iFlags,WRITE_COMPANIES) && !test(iFlags,WRITE_CONTACTS)) {
912         if (DebugFile.trace) DebugFile.writeln("Writting link between company and address");
913         oCompAddr.setString(1, (String JavaDoc) aValues[gu_company]);
914         oCompAddr.setString(2, (String JavaDoc) aValues[gu_address]);
915         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.executeUpdate(oCompAddr)");
916         oCompAddr.executeUpdate();
917       } else if (test(iFlags,WRITE_CONTACTS)) {
918         if (DebugFile.trace) DebugFile.writeln("Writting link between contact and address");
919         oContAddr.setString(1, (String JavaDoc) aValues[gu_contact]);
920         oContAddr.setString(2, (String JavaDoc) aValues[gu_address]);
921         if (DebugFile.trace) DebugFile.writeln("PreparedStatement.executeUpdate(oContAddr)");
922         oContAddr.executeUpdate();
923       }
924     } // fi test(iFlags,MODE_APPEND) && (iAffected==0))
925

926     if (DebugFile.trace) {
927       DebugFile.decIdent();
928       DebugFile.writeln("End ContactLoader.store()");
929     }
930   } // store
931

932   // ---------------------------------------------------------------------------
933

934   public static final int MODE_APPEND = ImportLoader.MODE_APPEND;
935   public static final int MODE_UPDATE = ImportLoader.MODE_UPDATE;
936   public static final int MODE_APPENDUPDATE = ImportLoader.MODE_APPENDUPDATE;
937   public static final int WRITE_LOOKUPS = ImportLoader.WRITE_LOOKUPS;
938
939   public static final int WRITE_COMPANIES = 32;
940   public static final int WRITE_CONTACTS = 64;
941   public static final int WRITE_ADDRESSES = 128;
942   public static final int NO_DUPLICATED_NAMES = 256;
943   public static final int NO_DUPLICATED_MAILS = 512;
944
945   // ---------------------------------------------------------------------------
946

947   // Keep this list sorted
948
private static final String JavaDoc[] ColumnNames = { "", "bo_active","bo_change_pwd","bo_private","contact_person","coord_x" ,"coord_y" ,"de_company" ,"de_title" ,"direct_phone","dt_birth","dt_created","dt_drivelic","dt_founded","dt_modified","dt_pwd_expires","fax_phone" ,"gu_address","gu_company","gu_contact","gu_geozone","gu_sales_man","gu_workarea","gu_writer","home_phone","id_address_ref","id_company_ref","id_company_status","id_contact_ref","id_contact_status","id_country","id_gender","id_legal","id_sector","id_state","im_revenue","ix_address","mn_city","mov_phone","nm_commercial","nm_company","nm_country","nm_legal","nm_state","nm_street","nu_employees","nu_street","ny_age","other_phone","po_box","sn_drivelic","sn_passport","tp_company","tp_location","tp_passport","tp_street","tx_addr1","tx_addr2","tx_challenge","tx_comments","tx_dept","tx_division","tx_email","tx_email_alt","tx_franchise","tx_name","tx_nickname","tx_pwd","tx_remarks","tx_reply","tx_salutation","tx_surname","url_addr","work_phone","zipcode"};
949
950   // ---------------------------------------------------------------------------
951

952   // Keep these column indexes in sync with ColumnNames array
953
public static int bo_active =1;
954   public static int bo_change_pwd =2;
955   public static int bo_private =3;
956   public static int contact_person =4;
957   public static int coord_x =5;
958   public static int coord_y =6;
959   public static int de_company =7;
960   public static int de_title =8;
961   public static int direct_phone =9;
962   public static int dt_birth =10;
963   public static int dt_created =11;
964   public static int dt_drivelic =12;
965   public static int dt_founded =13;
966   public static int dt_modified =14;
967   public static int dt_pwd_expires =15;
968   public static int fax_phone =16;
969   public static int gu_address =17;
970   public static int gu_company =18;
971   public static int gu_contact =19;
972   public static int gu_geozone =20;
973   public static int gu_sales_man =21;
974   public static int gu_workarea =22;
975   public static int gu_writer =23;
976   public static int home_phone =24;
977   public static int id_address_ref =25;
978   public static int id_company_ref =26;
979   public static int id_company_status =27;
980   public static int id_contact_ref =28;
981   public static int id_contact_status =29;
982   public static int id_country =30;
983   public static int id_gender =31;
984   public static int id_legal =32;
985   public static int id_sector =33;
986   public static int id_state =34;
987   public static int im_revenue =35;
988   public static int ix_address =36;
989   public static int mn_city =37;
990   public static int mov_phone =38;
991   public static int nm_commercial =39;
992   //public static int nm_company =40;
993
public static int nm_country =41;
994   public static int nm_legal =42;
995   public static int nm_state =43;
996   public static int nm_street =44;
997   public static int nu_employees =45;
998   public static int nu_street =46;
999   public static int ny_age =47;
1000  public static int other_phone =48;
1001  public static int po_box =49;
1002  public static int sn_drivelic =50;
1003  public static int sn_passport =51;
1004  public static int tp_company =52;
1005  public static int tp_location =53;
1006  public static int tp_passport =54;
1007  public static int tp_street =55;
1008  public static int tx_addr1 =56;
1009  public static int tx_addr2 =57;
1010  public static int tx_challenge =58;
1011  public static int tx_comments =59;
1012  public static int tx_dept =60;
1013  public static int tx_division =61;
1014  public static int tx_email =62;
1015  public static int tx_email_alt =63;
1016  public static int tx_franchise =64;
1017  public static int tx_name =65;
1018  public static int tx_nickname =66;
1019  public static int tx_pwd =67;
1020  public static int tx_remarks =68;
1021  public static int tx_reply =69;
1022  public static int tx_salutation =70;
1023  public static int tx_surname =71;
1024  public static int url_addr =72;
1025  public static int work_phone =73;
1026  public static int zipcode =74;
1027}
1028
Popular Tags