0001: /*
0002: Copyright (C) 2005 Know Gate S.L. All rights reserved.
0003: C/Oņa, 107 1š2 28050 Madrid (Spain)
0004:
0005: Redistribution and use in source and binary forms, with or without
0006: modification, are permitted provided that the following conditions
0007: are met:
0008:
0009: 1. Redistributions of source code must retain the above copyright
0010: notice, this list of conditions and the following disclaimer.
0011:
0012: 2. The end-user documentation included with the redistribution,
0013: if any, must include the following acknowledgment:
0014: "This product includes software parts from hipergate
0015: (http://www.hipergate.org/)."
0016: Alternately, this acknowledgment may appear in the software itself,
0017: if and wherever such third-party acknowledgments normally appear.
0018:
0019: 3. The name hipergate must not be used to endorse or promote products
0020: derived from this software without prior written permission.
0021: Products derived from this software may not be called hipergate,
0022: nor may hipergate appear in their name, without prior written
0023: permission.
0024:
0025: This library is distributed in the hope that it will be useful,
0026: but WITHOUT ANY WARRANTY; without even the implied warranty of
0027: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
0028:
0029: You should have received a copy of hipergate License with this code;
0030: if not, visit http://www.hipergate.org or mail to info@hipergate.org
0031: */
0032:
0033: package com.knowgate.crm;
0034:
0035: import java.util.Date;
0036: import java.util.Iterator;
0037: import java.util.Map;
0038: import java.util.HashMap;
0039: import java.util.Arrays;
0040:
0041: import java.sql.Connection;
0042: import java.sql.SQLException;
0043: import java.sql.PreparedStatement;
0044: import java.sql.ResultSet;
0045: import java.sql.Timestamp;
0046: import java.sql.Types;
0047:
0048: import com.knowgate.debug.DebugFile;
0049: import com.knowgate.misc.Gadgets;
0050: import com.knowgate.hipergate.Address;
0051: import com.knowgate.hipergate.DBLanguages;
0052: import com.knowgate.hipergate.datamodel.ColumnList;
0053: import com.knowgate.hipergate.datamodel.ImportLoader;
0054:
0055: /**
0056: * <p>Load Contact, Company and Address data from a single source</p>
0057: * Contact loader creates or updates simultaneously registers at k_companies,
0058: * k_contacts and k_addresses tables and the links between them k_x_contact_addr.
0059: * <br><br>
0060: * @author Sergio Montoro Ten
0061: * @version 1.0
0062: */
0063: public class ContactLoader implements ImportLoader {
0064:
0065: // ---------------------------------------------------------------------------
0066:
0067: private Object[] aValues;
0068: private PreparedStatement oCompUpdt, oContUpdt, oAddrUpdt;
0069: private PreparedStatement oCompInst, oContInst, oAddrInst,
0070: oContAddr, oCompAddr;
0071: private PreparedStatement oCompLook, oContLook, oAddrLook;
0072: private PreparedStatement oCompWook, oContWook, oAddrWook;
0073: private PreparedStatement oCompName, oContPort;
0074: private PreparedStatement oAddrComp, oAddrCont;
0075: private HashMap oCompSectorsMap, oCompStatusMap, oCompTypesMap;
0076: private HashMap oContGendersMap, oContStatusMap, oContDeptsMap,
0077: oContDivsMap, oContTitlesMap;
0078: private HashMap oAddrLocsMap, oAddrTypesMap, oAddrSalutMap;
0079:
0080: // ---------------------------------------------------------------------------
0081:
0082: private void init() {
0083: aValues = new Object[ColumnNames.length];
0084: for (int c = aValues.length - 1; c >= 0; c--)
0085: aValues[c] = null;
0086: oCompInst = oContInst = oAddrInst = oContAddr = oCompAddr = null;
0087: oCompUpdt = oContUpdt = oAddrUpdt = null;
0088: oCompLook = oContLook = oAddrLook = null;
0089: oCompWook = oContWook = oAddrWook = null;
0090: oCompSectorsMap = new HashMap();
0091: oCompStatusMap = new HashMap();
0092: oCompTypesMap = new HashMap();
0093: oContGendersMap = new HashMap();
0094: oContStatusMap = new HashMap();
0095: oContDeptsMap = new HashMap();
0096: oContDivsMap = new HashMap();
0097: oContTitlesMap = new HashMap();
0098: oAddrLocsMap = new HashMap();
0099: oAddrTypesMap = new HashMap();
0100: oAddrSalutMap = new HashMap();
0101: }
0102:
0103: // ---------------------------------------------------------------------------
0104:
0105: /**
0106: * Default construtor
0107: */
0108: public ContactLoader() {
0109: init();
0110: }
0111:
0112: // ---------------------------------------------------------------------------
0113:
0114: /**
0115: * Create ContactLoader and call prepare() on Connection
0116: * @param oConn Connection
0117: * @throws SQLException
0118: */
0119: public ContactLoader(Connection oConn) throws SQLException {
0120: init();
0121: prepare(oConn, null);
0122: }
0123:
0124: // ---------------------------------------------------------------------------
0125:
0126: /**
0127: * Set all column values to null
0128: */
0129: public void setAllColumnsToNull() {
0130: if (DebugFile.trace) {
0131: DebugFile
0132: .writeln("Begin ContactLoader.setAllColumnsToNull()");
0133: DebugFile.incIdent();
0134: }
0135:
0136: for (int c = aValues.length - 1; c >= 0; c--)
0137: aValues[c] = null;
0138:
0139: if (DebugFile.trace) {
0140: DebugFile.decIdent();
0141: DebugFile
0142: .writeln("End ContactLoader.setAllColumnsToNull()");
0143: }
0144: } // setAllColumnsToNull
0145:
0146: // ---------------------------------------------------------------------------
0147:
0148: /**
0149: * <p>Get column index at ColumnNames array given its name</p>
0150: * This method performs binary search assuming that ColumnNames is sorted in
0151: * ascending order
0152: * @param sColumnName String Column name (case insensitive)
0153: * @return int Column index or -1 if not found
0154: */
0155: public int getColumnIndex(String sColumnName) {
0156: int iIndex = Arrays.binarySearch(ColumnNames, sColumnName,
0157: String.CASE_INSENSITIVE_ORDER);
0158: if (iIndex < 0)
0159: iIndex = -1;
0160: return iIndex;
0161: }
0162:
0163: // ---------------------------------------------------------------------------
0164:
0165: public int columnCount() {
0166: return aValues.length;
0167: }
0168:
0169: // ---------------------------------------------------------------------------
0170:
0171: public String[] columnNames() throws IllegalStateException {
0172: return ColumnNames;
0173: }
0174:
0175: // ---------------------------------------------------------------------------
0176:
0177: /**
0178: * Put value for a given column
0179: * @param iColumnIndex Column index [0..getColumnCount()-1]
0180: * @param oValue Value for column
0181: * @throws ArrayIndexOutOfBoundsException
0182: */
0183: public void put(int iColumnIndex, Object oValue)
0184: throws ArrayIndexOutOfBoundsException {
0185: aValues[iColumnIndex] = oValue;
0186: }
0187:
0188: // ---------------------------------------------------------------------------
0189:
0190: /**
0191: * <p>Put value for a given column</p>
0192: * If a previous value already exists then it is replaced
0193: * @param sColumnName Column name (case sensitive)
0194: * @param oValue Value for column
0195: * @throws ArrayIndexOutOfBoundsException
0196: */
0197: public void put(String sColumnName, Object oValue)
0198: throws ArrayIndexOutOfBoundsException {
0199: int iColumnIndex = getColumnIndex(sColumnName.toLowerCase());
0200: if (-1 == iColumnIndex)
0201: throw new ArrayIndexOutOfBoundsException(
0202: "Cannot find column named " + sColumnName);
0203: aValues[iColumnIndex] = oValue;
0204: }
0205:
0206: // ---------------------------------------------------------------------------
0207:
0208: /**
0209: * Put all values from a map on their corresponding columns matching by name
0210: * @param oValues Map
0211: */
0212: public void putAll(Map oValues) {
0213: int iColumnIndex;
0214: String sColumnName;
0215: if (DebugFile.trace) {
0216: DebugFile.writeln("Begin ContactLoader.putAll()");
0217: DebugFile.incIdent();
0218: }
0219: Iterator oIter = oValues.keySet().iterator();
0220: while (oIter.hasNext()) {
0221: sColumnName = (String) oIter.next();
0222: iColumnIndex = getColumnIndex(sColumnName.toLowerCase());
0223: if (iColumnIndex > 0) {
0224: Object oVal = oValues.get(sColumnName);
0225: if (oVal == null)
0226: aValues[iColumnIndex] = null;
0227: else if (oVal.getClass().getName().startsWith("[L")) {
0228: aValues[iColumnIndex] = java.lang.reflect.Array
0229: .get(oVal, 0);
0230: } else {
0231: aValues[iColumnIndex] = oVal;
0232: }
0233: if (DebugFile.trace)
0234: DebugFile.writeln(sColumnName.toLowerCase() + "="
0235: + aValues[iColumnIndex]);
0236: } else {
0237: if (DebugFile.trace)
0238: DebugFile.writeln(sColumnName + " not found");
0239: }// fi (iColumnIndex)
0240: } // wend
0241: if (DebugFile.trace) {
0242: DebugFile.decIdent();
0243: DebugFile.writeln("End ContactLoader.putAll()");
0244: }
0245: } // putAll
0246:
0247: // ---------------------------------------------------------------------------
0248:
0249: /**
0250: * Get column by index
0251: * @param iColumnIndex int Colunm index [0..getColumnCount()-1]
0252: * @return Object Column value
0253: * @throws ArrayIndexOutOfBoundsException
0254: */
0255: public Object get(int iColumnIndex)
0256: throws ArrayIndexOutOfBoundsException {
0257: return aValues[iColumnIndex];
0258: } // get
0259:
0260: // ---------------------------------------------------------------------------
0261:
0262: /**
0263: * Get column by name
0264: * @param sColumnName String Column name (case sensitive)
0265: * @return Object Column value
0266: * @throws ArrayIndexOutOfBoundsException If no column with sucjh name was found
0267: */
0268: public Object get(String sColumnName)
0269: throws ArrayIndexOutOfBoundsException {
0270: int iColumnIndex = getColumnIndex(sColumnName.toLowerCase());
0271: if (-1 == iColumnIndex)
0272: throw new ArrayIndexOutOfBoundsException(
0273: "Cannot find column named " + sColumnName);
0274: return aValues[iColumnIndex];
0275: }
0276:
0277: // ---------------------------------------------------------------------------
0278:
0279: /**
0280: * <p>Prepare statements for execution</p>
0281: * This method needs to be called only once if the default constructor was used.<br>
0282: * If ContactLoader(Connection) constructor was used, there is no need to call prepare()
0283: * and a SQLException will be raised if the attempt is made.<br>
0284: * It is neccesary to call close() always for prepared instances as a failure
0285: * to do so will leave open cursors on the database causing it eventually to stop.
0286: * @param oConn Connection Open JDBC database connection
0287: * @param oColList ColumnList This parameter is ignored
0288: * @throws SQLException
0289: */
0290: public void prepare(Connection oConn, ColumnList oColList)
0291: throws SQLException {
0292:
0293: if (DebugFile.trace) {
0294: DebugFile.writeln("Begin ContactLoader.prepare()");
0295: DebugFile.incIdent();
0296: }
0297:
0298: if (oCompUpdt != null || oCompInst != null || oCompLook != null
0299: || oCompWook != null || oContAddr != null
0300: || oContAddr != null) {
0301: if (DebugFile.trace)
0302: DebugFile.decIdent();
0303: throw new SQLException(
0304: "Either ContactLoader.prepare() has already been called or statements were not properly closed",
0305: "HY010");
0306: }
0307:
0308: oCompUpdt = oConn
0309: .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=?)");
0310: oCompInst = oConn
0311: .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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
0312: oCompLook = oConn
0313: .prepareStatement(
0314: "SELECT NULL FROM k_companies_lookup WHERE gu_owner=? AND id_section=? AND vl_lookup=?",
0315: ResultSet.TYPE_FORWARD_ONLY,
0316: ResultSet.CONCUR_READ_ONLY);
0317: oCompWook = oConn
0318: .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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
0319: oCompName = oConn
0320: .prepareStatement(
0321: "SELECT gu_company FROM k_companies WHERE nm_legal=? AND gu_workarea=?",
0322: ResultSet.TYPE_FORWARD_ONLY,
0323: ResultSet.CONCUR_READ_ONLY);
0324:
0325: oContUpdt = oConn
0326: .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=?)");
0327: oContInst = oConn
0328: .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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
0329: oContLook = oConn
0330: .prepareStatement(
0331: "SELECT NULL FROM k_contacts_lookup WHERE gu_owner=? AND id_section=? AND vl_lookup=?",
0332: ResultSet.TYPE_FORWARD_ONLY,
0333: ResultSet.CONCUR_READ_ONLY);
0334: oContWook = oConn
0335: .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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
0336: oContPort = oConn
0337: .prepareStatement(
0338: "SELECT gu_contact FROM k_contacts WHERE sn_passport=? AND gu_workarea=?",
0339: ResultSet.TYPE_FORWARD_ONLY,
0340: ResultSet.CONCUR_READ_ONLY);
0341:
0342: oAddrUpdt = oConn
0343: .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=?)");
0344: oAddrInst = oConn
0345: .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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
0346: oAddrLook = oConn
0347: .prepareStatement(
0348: "SELECT NULL FROM k_addresses_lookup WHERE gu_owner=? AND id_section=? AND vl_lookup=?",
0349: ResultSet.TYPE_FORWARD_ONLY,
0350: ResultSet.CONCUR_READ_ONLY);
0351: oAddrWook = oConn
0352: .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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
0353: oAddrComp = oConn
0354: .prepareStatement(
0355: "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=?",
0356: ResultSet.TYPE_FORWARD_ONLY,
0357: ResultSet.CONCUR_READ_ONLY);
0358: oAddrCont = oConn
0359: .prepareStatement(
0360: "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=?",
0361: ResultSet.TYPE_FORWARD_ONLY,
0362: ResultSet.CONCUR_READ_ONLY);
0363:
0364: oContAddr = oConn
0365: .prepareStatement("INSERT INTO k_x_contact_addr (gu_contact,gu_address) VALUES(?,?)");
0366: oCompAddr = oConn
0367: .prepareStatement("INSERT INTO k_x_company_addr (gu_company,gu_address) VALUES(?,?)");
0368:
0369: if (DebugFile.trace) {
0370: DebugFile.decIdent();
0371: DebugFile.writeln("End ContactLoader.prepare()");
0372: }
0373: } // prepare
0374:
0375: // ---------------------------------------------------------------------------
0376:
0377: /**
0378: * <p>Close prepared statements</p>
0379: * This method must always be called before object is destroyed or else
0380: * @throws SQLException
0381: */
0382: public void close() throws SQLException {
0383:
0384: oCompSectorsMap.clear();
0385: oCompStatusMap.clear();
0386: oCompTypesMap.clear();
0387: oContGendersMap.clear();
0388: oContStatusMap.clear();
0389: oContDeptsMap.clear();
0390: oContDivsMap.clear();
0391: oContTitlesMap.clear();
0392: oAddrLocsMap.clear();
0393: oAddrTypesMap.clear();
0394: oAddrSalutMap.clear();
0395:
0396: if (oAddrComp != null) {
0397: oAddrComp.close();
0398: oAddrComp = null;
0399: }
0400: if (oAddrCont != null) {
0401: oAddrCont.close();
0402: oAddrCont = null;
0403: }
0404:
0405: if (oCompAddr != null) {
0406: oCompAddr.close();
0407: oCompAddr = null;
0408: }
0409: if (oContAddr != null) {
0410: oContAddr.close();
0411: oContAddr = null;
0412: }
0413:
0414: if (oCompUpdt != null) {
0415: oCompUpdt.close();
0416: oCompUpdt = null;
0417: }
0418: if (oCompInst != null) {
0419: oCompInst.close();
0420: oCompInst = null;
0421: }
0422: if (oCompLook != null) {
0423: oCompLook.close();
0424: oCompLook = null;
0425: }
0426: if (oCompWook != null) {
0427: oCompWook.close();
0428: oCompWook = null;
0429: }
0430: if (oCompName != null) {
0431: oCompName.close();
0432: oCompName = null;
0433: }
0434:
0435: if (oContUpdt != null) {
0436: oContUpdt.close();
0437: oContUpdt = null;
0438: }
0439: if (oContInst != null) {
0440: oContInst.close();
0441: oContInst = null;
0442: }
0443: if (oContLook != null) {
0444: oContLook.close();
0445: oContLook = null;
0446: }
0447: if (oContWook != null) {
0448: oContWook.close();
0449: oContWook = null;
0450: }
0451: if (oContPort != null) {
0452: oContPort.close();
0453: oContPort = null;
0454: }
0455:
0456: if (oAddrUpdt != null) {
0457: oAddrUpdt.close();
0458: oAddrUpdt = null;
0459: }
0460: if (oAddrInst != null) {
0461: oAddrInst.close();
0462: oAddrInst = null;
0463: }
0464: if (oAddrLook != null) {
0465: oAddrLook.close();
0466: oAddrLook = null;
0467: }
0468: if (oAddrWook != null) {
0469: oAddrWook.close();
0470: oAddrWook = null;
0471: }
0472: } // close
0473:
0474: // ---------------------------------------------------------------------------
0475:
0476: private static boolean test(int iInputValue, int iBitMask) {
0477: return (iInputValue & iBitMask) != 0;
0478: } // test
0479:
0480: // ---------------------------------------------------------------------------
0481:
0482: /**
0483: * Add a lookup value to a table
0484: * @param sSection String Section. Usually the name of the column at the base table
0485: * @param sValue String Internal hidden value of the lookup
0486: * @param oConn Connection
0487: * @param oSelStmt PreparedStatement
0488: * @param oInsStmt PreparedStatement
0489: * @param oCacheMap HashMap
0490: * @throws SQLException
0491: */
0492: private void addLookUp(String sSection, String sValue,
0493: Connection oConn, PreparedStatement oSelStmt,
0494: PreparedStatement oInsStmt, HashMap oCacheMap)
0495: throws SQLException {
0496: String sTr;
0497: char[] aTr;
0498: final String EmptyStr = "";
0499: boolean bExistsLookup;
0500:
0501: if (DebugFile.trace) {
0502: DebugFile
0503: .writeln("Begin ContactLoader.addLookUp("
0504: + sSection
0505: + ","
0506: + sValue
0507: + ","
0508: + "[Connection],[PreparedStatement],[PreparedStatement],[HashMap]");
0509: DebugFile.incIdent();
0510: }
0511:
0512: if (null == sValue)
0513: sValue = EmptyStr;
0514: if (!EmptyStr.equals(sValue)) {
0515: if (!oCacheMap.containsKey(sValue)) {
0516: oSelStmt.setObject(1, get(gu_workarea), Types.CHAR);
0517: oSelStmt.setString(2, sSection);
0518: oSelStmt.setString(3, sValue);
0519: ResultSet oRSet = oSelStmt.executeQuery();
0520: bExistsLookup = oRSet.next();
0521: oRSet.close();
0522: if (!bExistsLookup) {
0523: aTr = ((String) ColumnNames[id_sector])
0524: .toLowerCase().toCharArray();
0525: aTr[0] = Character.toUpperCase(aTr[0]);
0526: sTr = new String(aTr);
0527: oInsStmt.setObject(1, get(gu_workarea), Types.CHAR);
0528: oInsStmt.setString(2, "id_sector");
0529: oInsStmt.setInt(3, DBLanguages
0530: .nextLookuUpProgressive(oConn,
0531: "k_companies_lookup",
0532: (String) get(gu_workarea),
0533: "id_sector"));
0534: oInsStmt.setObject(4, ColumnNames[id_sector],
0535: Types.VARCHAR);
0536: for (int t = 5; t <= 17; t++)
0537: oCompWook.setString(t, sTr);
0538: oInsStmt.executeUpdate();
0539: } // fi (!bExistsLookup)
0540: oCacheMap.put(sValue, sValue);
0541: } // fi (!oCacheMap.containsKey(sValue))
0542: }
0543:
0544: if (DebugFile.trace) {
0545: DebugFile.decIdent();
0546: DebugFile.writeln("End ContactLoader.addLookUp()");
0547: }
0548: } // addLookUp
0549:
0550: // ---------------------------------------------------------------------------
0551:
0552: private String getCompanyGuid(Connection oConn,
0553: Object sCompanyLegalname, Object sWorkArea)
0554: throws SQLException {
0555: String sCompGuid;
0556: oCompName.setObject(1, sCompanyLegalname, Types.VARCHAR);
0557: oCompName.setObject(2, sWorkArea, Types.VARCHAR);
0558: ResultSet oRSet = oCompName.executeQuery();
0559: if (oRSet.next())
0560: sCompGuid = oRSet.getString(1);
0561: else
0562: sCompGuid = null;
0563: oRSet.close();
0564: if (null == sCompGuid)
0565: sCompGuid = Gadgets.generateUUID();
0566: return sCompGuid;
0567: } // getCompanyGuid
0568:
0569: // ---------------------------------------------------------------------------
0570:
0571: private String getContactGuid(Connection oConn,
0572: Object sContactPassport, Object sWorkArea)
0573: throws SQLException {
0574: String sContGuid;
0575: oContPort.setObject(1, sContactPassport, Types.VARCHAR);
0576: oContPort.setObject(2, sWorkArea, Types.VARCHAR);
0577: ResultSet oRSet = oContPort.executeQuery();
0578: if (oRSet.next())
0579: sContGuid = oRSet.getString(1);
0580: else
0581: sContGuid = null;
0582: oRSet.close();
0583: if (null == sContGuid)
0584: sContGuid = Gadgets.generateUUID();
0585: return sContGuid;
0586: } // getContactGuid
0587:
0588: // ---------------------------------------------------------------------------
0589:
0590: private String getAddressGuid(Connection oConn, Object oIxAddr,
0591: Object oGuWrkA, Object oGuCont, Object oGuComp, int iFlags)
0592: throws SQLException {
0593: String sAddrGuid;
0594: ResultSet oRSet;
0595: if (oIxAddr == null)
0596: return Gadgets.generateUUID();
0597: if (test(iFlags, WRITE_CONTACTS)) {
0598: oAddrCont.setObject(1, oGuWrkA, Types.CHAR);
0599: oAddrCont.setObject(2, oIxAddr, Types.INTEGER);
0600: oAddrCont.setObject(3, oGuCont, Types.CHAR);
0601: oRSet = oAddrCont.executeQuery();
0602: if (oRSet.next())
0603: sAddrGuid = oRSet.getString(1);
0604: else
0605: sAddrGuid = null;
0606: oRSet.close();
0607: } else {
0608: oAddrComp.setObject(1, oGuWrkA, Types.CHAR);
0609: oAddrComp.setObject(2, oIxAddr, Types.INTEGER);
0610: oAddrComp.setObject(3, oGuComp, Types.CHAR);
0611: oRSet = oAddrComp.executeQuery();
0612: if (oRSet.next())
0613: sAddrGuid = oRSet.getString(1);
0614: else
0615: sAddrGuid = null;
0616: oRSet.close();
0617: }
0618: if (null == sAddrGuid)
0619: sAddrGuid = Gadgets.generateUUID();
0620: return sAddrGuid;
0621: } // getAddressGuid
0622:
0623: // ---------------------------------------------------------------------------
0624:
0625: private String getColNull(int iColIndex)
0626: throws ArrayIndexOutOfBoundsException, ClassCastException {
0627: if (DebugFile.trace) {
0628: if (iColIndex < 0 || iColIndex >= aValues.length)
0629: throw new ArrayIndexOutOfBoundsException(
0630: "ContactLoader.getColNull() column index "
0631: + String.valueOf(iColIndex)
0632: + " must be in the range between 0 and "
0633: + String.valueOf(aValues.length));
0634: DebugFile.writeln("ContactLoader.getColNull("
0635: + String.valueOf(iColIndex) + ") : "
0636: + aValues[iColIndex]);
0637: }
0638: String sRetVal;
0639: if (null == aValues[iColIndex])
0640: sRetVal = null;
0641: else {
0642: try {
0643: sRetVal = aValues[iColIndex].toString();
0644: } catch (ClassCastException cce) {
0645: if (aValues[iColIndex] == null)
0646: throw new ClassCastException(
0647: "ContactLoader.getColNull("
0648: + String.valueOf(iColIndex)
0649: + ") could not cast null to String");
0650: else
0651: throw new ClassCastException(
0652: "ContactLoader.getColNull("
0653: + String.valueOf(iColIndex)
0654: + ") could not cast "
0655: + aValues[iColIndex].getClass()
0656: .getName() + " "
0657: + aValues[iColIndex] + " to String");
0658: }
0659: if (sRetVal.length() == 0
0660: || sRetVal.equalsIgnoreCase("null"))
0661: sRetVal = null;
0662: }
0663: return sRetVal;
0664: } // getColNull
0665:
0666: // ---------------------------------------------------------------------------
0667:
0668: /**
0669: * Store properties curently held in RAM into the database
0670: * @param oConn Opened JDBC connection
0671: * @param sWorkArea String GUID of WorkArea to which inserted data will belong
0672: * @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}
0673: * @throws SQLException
0674: * @throws IllegalArgumentException
0675: * @throws NullPointerException
0676: * @throws ClassCastException
0677: */
0678: public void store(Connection oConn, String sWorkArea, int iFlags)
0679: throws SQLException, IllegalArgumentException,
0680: NullPointerException, ClassCastException {
0681:
0682: if (oCompUpdt == null || oContUpdt == null || oAddrUpdt == null)
0683: throw new SQLException(
0684: "Invalid command sequece. Must call ContactLoader.prepare() before ContactLoader.store()");
0685:
0686: if (!test(iFlags, MODE_APPEND) && !test(iFlags, MODE_UPDATE))
0687: throw new IllegalArgumentException(
0688: "ContactLoader.store() Flags bitmask must contain either MODE_APPEND, MODE_UPDATE or both");
0689:
0690: if (!test(iFlags, WRITE_COMPANIES)
0691: && !test(iFlags, WRITE_CONTACTS))
0692: throw new IllegalArgumentException(
0693: "ContactLoader.store() Flags bitmask must contain either WRITE_COMPANIES, WRITE_CONTACTS or both");
0694:
0695: if (null == sWorkArea)
0696: throw new NullPointerException(
0697: "ContactLoader.store() Default WorkArea cannot be null");
0698:
0699: if (null == getColNull(nm_legal)
0700: && test(iFlags, WRITE_COMPANIES))
0701: throw new NullPointerException(
0702: "ContactLoader.store() nm_legal cannot be null");
0703:
0704: if (null == getColNull(nm_legal)
0705: && test(iFlags, WRITE_COMPANIES))
0706: throw new NullPointerException(
0707: "ContactLoader.store() nm_legal cannot be null");
0708:
0709: if (null == getColNull(gu_company)
0710: && test(iFlags, WRITE_COMPANIES)
0711: && test(iFlags, MODE_UPDATE)
0712: && !test(iFlags, MODE_APPEND))
0713: throw new NullPointerException(
0714: "ContactLoader.store() gu_company cannot be null when using UPDATE mode");
0715:
0716: if (null == getColNull(gu_contact)
0717: && test(iFlags, WRITE_CONTACTS)
0718: && test(iFlags, MODE_UPDATE)
0719: && !test(iFlags, MODE_APPEND))
0720: throw new NullPointerException(
0721: "ContactLoader.store() gu_contact cannot be null when using UPDATE mode");
0722:
0723: if (null == getColNull(gu_address)
0724: && test(iFlags, WRITE_ADDRESSES)
0725: && test(iFlags, MODE_UPDATE)
0726: && !test(iFlags, MODE_APPEND))
0727: throw new NullPointerException(
0728: "ContactLoader.store() gu_address cannot be null when using UPDATE mode");
0729:
0730: if (DebugFile.trace) {
0731: DebugFile.writeln("Begin ContactLoader.store([Connection],"
0732: + sWorkArea + "," + String.valueOf(iFlags) + ")");
0733: DebugFile.incIdent();
0734: StringBuffer oRow = new StringBuffer();
0735: oRow.append('{');
0736: oRow.append(ColumnNames[0] + "=");
0737: oRow.append(aValues[0] == null ? "null" : aValues[0]);
0738: for (int d = 1; d < aValues.length; d++) {
0739: oRow.append("," + ColumnNames[d] + "=");
0740: oRow.append(aValues[d] == null ? "null" : aValues[d]);
0741: } // next
0742: oRow.append('}');
0743: DebugFile.writeln(oRow.toString());
0744: }
0745:
0746: int iAffected;
0747: Timestamp tsNow = new Timestamp(new Date().getTime());
0748:
0749: if (null == get(gu_workarea)) {
0750: if (DebugFile.trace)
0751: DebugFile.writeln("setting workarea to " + sWorkArea);
0752: put(gu_workarea, sWorkArea);
0753: } else {
0754: if (DebugFile.trace)
0755: DebugFile.writeln("workarea for current record is "
0756: + getColNull(gu_workarea));
0757: }
0758: if (test(iFlags, WRITE_COMPANIES)
0759: && getColNull(gu_company) == null)
0760: put(gu_company, getCompanyGuid(oConn, aValues[nm_legal],
0761: get(gu_workarea)));
0762: if (test(iFlags, WRITE_CONTACTS)
0763: && getColNull(gu_contact) == null)
0764: put(gu_contact, getContactGuid(oConn, aValues[sn_passport],
0765: get(gu_workarea)));
0766: if (test(iFlags, WRITE_ADDRESSES)
0767: && aValues[gu_address] == null)
0768: put(gu_address, getAddressGuid(oConn, aValues[ix_address],
0769: get(gu_workarea), get(gu_contact), get(gu_company),
0770: iFlags));
0771:
0772: if (test(iFlags, WRITE_COMPANIES)) {
0773: if (test(iFlags, WRITE_LOOKUPS)) {
0774: addLookUp("id_sector", getColNull(id_sector), oConn,
0775: oCompLook, oCompWook, oCompSectorsMap);
0776: addLookUp("id_status", getColNull(id_company_status),
0777: oConn, oCompLook, oCompWook, oCompStatusMap);
0778: addLookUp("tp_company", getColNull(tp_company), oConn,
0779: oCompLook, oCompWook, oCompTypesMap);
0780: } // if (test(WRITE_LOOKUPS))
0781:
0782: iAffected = 0;
0783: if ((test(iFlags, MODE_UPDATE) || test(iFlags,
0784: WRITE_CONTACTS))
0785: && (getColNull(nm_legal) != null || getColNull(gu_company) != null)) {
0786: if (DebugFile.trace)
0787: DebugFile
0788: .writeln("COMPANY MODE_UPDATE AND IS NOT NEW COMPANY");
0789: oCompUpdt.setString(1, getColNull(nm_legal));
0790: if (DebugFile.trace)
0791: DebugFile.writeln("PreparedStatement.setObject(2, "
0792: + aValues[gu_workarea] + ", Types.CHAR)");
0793: oCompUpdt
0794: .setObject(2, aValues[gu_workarea], Types.CHAR);
0795: oCompUpdt.setString(3, getColNull(nm_commercial));
0796: if (aValues[dt_modified] == null)
0797: oCompUpdt.setTimestamp(4, tsNow);
0798: else {
0799: if (DebugFile.trace)
0800: DebugFile
0801: .writeln("PreparedStatement.setObject(4, "
0802: + aValues[dt_modified]
0803: + ", Types.TIMESTAMP)");
0804: oCompUpdt.setObject(4, aValues[dt_modified],
0805: Types.TIMESTAMP);
0806: }
0807: if (aValues[dt_founded] == null)
0808: oCompUpdt.setNull(5, Types.TIMESTAMP);
0809: else {
0810: if (DebugFile.trace)
0811: DebugFile
0812: .writeln("PreparedStatement.setObject(5, "
0813: + aValues[dt_founded]
0814: + ", Types.TIMESTAMP)");
0815: oCompUpdt.setObject(5, aValues[dt_founded],
0816: Types.TIMESTAMP);
0817: }
0818: oCompUpdt.setString(6, getColNull(id_legal));
0819: oCompUpdt.setString(7, getColNull(id_sector));
0820: oCompUpdt.setString(8, getColNull(id_company_status));
0821: oCompUpdt.setString(9, getColNull(id_company_ref));
0822: oCompUpdt.setString(10, getColNull(tp_company));
0823: oCompUpdt.setString(11, getColNull(gu_geozone));
0824: if (DebugFile.trace)
0825: DebugFile
0826: .writeln("PreparedStatement.setObject(12, "
0827: + aValues[nu_employees]
0828: + ", Types.INTEGER)");
0829: oCompUpdt.setObject(12, aValues[nu_employees],
0830: Types.INTEGER);
0831: if (DebugFile.trace)
0832: DebugFile
0833: .writeln("PreparedStatement.setObject(13, "
0834: + aValues[im_revenue]
0835: + ", Types.FLOAT)");
0836: oCompUpdt.setObject(13, aValues[im_revenue],
0837: Types.FLOAT);
0838: oCompUpdt.setString(14, getColNull(gu_sales_man));
0839: oCompUpdt.setString(15, getColNull(tx_franchise));
0840: oCompUpdt.setString(16, getColNull(de_company));
0841: if (DebugFile.trace)
0842: DebugFile.writeln("PreparedStatement.setString(17,"
0843: + aValues[gu_company] + ")");
0844: oCompUpdt.setString(17, (String) aValues[gu_company]);
0845: oCompUpdt.setString(18, (String) aValues[nm_legal]);
0846: oCompUpdt.setString(19, (String) aValues[gu_workarea]);
0847: if (DebugFile.trace)
0848: DebugFile
0849: .writeln("PreparedStatement.executeUpdate(oCompUpdt)");
0850: iAffected = oCompUpdt.executeUpdate();
0851: if (DebugFile.trace)
0852: DebugFile.writeln("affected="
0853: + String.valueOf(iAffected));
0854: }
0855: if (test(iFlags, MODE_APPEND) && (iAffected == 0)) {
0856: if (DebugFile.trace)
0857: DebugFile
0858: .writeln("COMPANY MODE_APPEND AND affected=0");
0859: oCompInst.setString(1, getColNull(nm_legal));
0860: if (DebugFile.trace)
0861: DebugFile
0862: .writeln("PreparedStatement.setObject(2, "
0863: + aValues[gu_workarea] + " "
0864: + getColNull(gu_workarea)
0865: + ", Types.CHAR)");
0866: oCompInst
0867: .setObject(2, aValues[gu_workarea], Types.CHAR);
0868: oCompInst.setString(3, getColNull(nm_commercial));
0869: if (aValues[dt_modified] == null) {
0870: oCompInst.setTimestamp(4, tsNow);
0871: } else {
0872: if (DebugFile.trace)
0873: DebugFile
0874: .writeln("PreparedStatement.setObject(4, "
0875: + aValues[dt_modified]
0876: + ", Types.TIMESTAMP)");
0877: oCompInst.setObject(4, aValues[dt_modified],
0878: Types.TIMESTAMP);
0879: }
0880: if (aValues[dt_founded] == null) {
0881: oCompInst.setNull(5, Types.TIMESTAMP);
0882: } else {
0883: if (DebugFile.trace)
0884: DebugFile
0885: .writeln("PreparedStatement.setObject(5, "
0886: + aValues[dt_founded]
0887: + ", Types.TIMESTAMP)");
0888: oCompInst.setObject(5, aValues[dt_founded],
0889: Types.TIMESTAMP);
0890: }
0891: oCompInst.setString(6, getColNull(id_legal));
0892: oCompInst.setString(7, getColNull(id_sector));
0893: oCompInst.setString(8, getColNull(id_company_status));
0894: oCompInst.setString(9, getColNull(id_company_ref));
0895: oCompInst.setString(10, getColNull(tp_company));
0896: oCompInst.setString(11, getColNull(gu_geozone));
0897: if (DebugFile.trace)
0898: DebugFile
0899: .writeln("PreparedStatement.setObject(12, "
0900: + aValues[nu_employees]
0901: + ", Types.INTEGER)");
0902: oCompInst.setObject(12, aValues[nu_employees],
0903: Types.INTEGER);
0904: if (DebugFile.trace)
0905: DebugFile
0906: .writeln("PreparedStatement.setObject(13, "
0907: + aValues[im_revenue]
0908: + ", Types.FLOAT)");
0909: oCompInst.setObject(13, aValues[im_revenue],
0910: Types.FLOAT);
0911: oCompInst.setString(14, getColNull(gu_sales_man));
0912: oCompInst.setString(15, getColNull(tx_franchise));
0913: oCompInst.setString(16, getColNull(de_company));
0914: if (DebugFile.trace)
0915: DebugFile.writeln("PreparedStatement.setString(17,"
0916: + aValues[gu_company] + ")");
0917: oCompInst.setString(17, (String) aValues[gu_company]);
0918: if (DebugFile.trace)
0919: DebugFile
0920: .writeln("PreparedStatement.executeUpdate(oCompInst)");
0921: iAffected = oCompInst.executeUpdate();
0922: if (DebugFile.trace)
0923: DebugFile.writeln("affected="
0924: + String.valueOf(iAffected));
0925: }
0926: } // fi (bWriteCompanies)
0927:
0928: if (test(iFlags, WRITE_CONTACTS)) {
0929: if (test(iFlags, WRITE_LOOKUPS)) {
0930: addLookUp("id_status", getColNull(id_contact_status),
0931: oConn, oCompLook, oCompWook, oContStatusMap);
0932: addLookUp("de_title", getColNull(de_title), oConn,
0933: oCompLook, oCompWook, oContTitlesMap);
0934: addLookUp("id_gender", getColNull(id_gender), oConn,
0935: oCompLook, oCompWook, oContGendersMap);
0936: addLookUp("tx_dept", getColNull(tx_dept), oConn,
0937: oCompLook, oCompWook, oContDeptsMap);
0938: addLookUp("tx_division", getColNull(tx_division),
0939: oConn, oCompLook, oCompWook, oContDivsMap);
0940: } // if (test(WRITE_LOOKUPS))
0941:
0942: iAffected = 0;
0943: if (DebugFile.trace)
0944: DebugFile
0945: .writeln("MODE_UPDATE="
0946: + String.valueOf(test(iFlags,
0947: MODE_UPDATE))
0948: + " "
0949: + (getColNull(sn_passport) == null ? "sn_passport IS NULL"
0950: : "sn_passport IS NOT NULL")
0951: + " "
0952: + (getColNull(gu_contact) == null ? "gu_contact IS NULL"
0953: : "gu_contact IS NOT NULL"));
0954: if (test(iFlags, MODE_UPDATE)
0955: && (getColNull(sn_passport) != null || getColNull(gu_contact) != null)) {
0956: if (DebugFile.trace)
0957: DebugFile
0958: .writeln("CONTACT MODE_UPDATE AND IS NOT NEW CONTACT");
0959: oContUpdt
0960: .setObject(1, aValues[gu_workarea], Types.CHAR);
0961: oContUpdt.setString(2, getColNull(tx_nickname));
0962: oContUpdt.setString(3, getColNull(tx_pwd));
0963: oContUpdt.setString(4, getColNull(tx_challenge));
0964: oContUpdt.setString(5, getColNull(tx_reply));
0965: oContUpdt.setObject(6, aValues[dt_pwd_expires],
0966: Types.TIMESTAMP);
0967: if (aValues[dt_modified] == null)
0968: oContUpdt.setTimestamp(7, tsNow);
0969: else
0970: oContUpdt.setObject(7, aValues[dt_modified],
0971: Types.TIMESTAMP);
0972: oContUpdt.setString(8, getColNull(gu_writer));
0973: if (test(iFlags, WRITE_COMPANIES))
0974: oContUpdt.setString(9, getColNull(gu_company));
0975: else
0976: oContUpdt.setNull(9, Types.CHAR);
0977: oContUpdt.setString(10, getColNull(id_contact_status));
0978: oContUpdt.setString(11, getColNull(id_contact_ref));
0979: oContUpdt.setString(12, getColNull(tx_name));
0980: oContUpdt.setString(13, getColNull(tx_surname));
0981: oContUpdt.setString(14, getColNull(de_title));
0982: oContUpdt.setString(15, getColNull(id_gender));
0983: if (DebugFile.trace)
0984: DebugFile
0985: .writeln("PreparedStatement.setObject(16, "
0986: + aValues[dt_birth]
0987: + ", Types.TIMESTAMP)");
0988: oContUpdt.setObject(16, aValues[dt_birth],
0989: Types.TIMESTAMP);
0990: if (DebugFile.trace)
0991: DebugFile
0992: .writeln("PreparedStatement.setObject(17, "
0993: + aValues[ny_age]
0994: + ", Types.INTEGER)");
0995: oContUpdt.setObject(17, aValues[ny_age], Types.INTEGER);
0996: oContUpdt.setString(18, getColNull(sn_passport));
0997: oContUpdt.setString(19, getColNull(tp_passport));
0998: oContUpdt.setString(20, getColNull(sn_drivelic));
0999: if (DebugFile.trace)
1000: DebugFile
1001: .writeln("PreparedStatement.setObject(21, "
1002: + aValues[dt_drivelic]
1003: + ", Types.TIMESTAMP)");
1004: oContUpdt.setObject(21, aValues[dt_drivelic],
1005: Types.TIMESTAMP);
1006: oContUpdt.setString(22, getColNull(tx_dept));
1007: oContUpdt.setString(23, getColNull(tx_division));
1008: oContUpdt.setString(24, getColNull(gu_geozone));
1009: oContUpdt.setString(25, getColNull(tx_comments));
1010: if (DebugFile.trace)
1011: DebugFile.writeln("PreparedStatement.setString(26,"
1012: + aValues[gu_contact] + ")");
1013: oContUpdt.setString(26, (String) aValues[gu_contact]);
1014: if (test(iFlags, NO_DUPLICATED_NAMES)) {
1015: oContUpdt.setString(27, getColNull(tx_name));
1016: oContUpdt.setString(28, getColNull(tx_surname));
1017: oContUpdt.setString(29,
1018: (String) aValues[gu_workarea]);
1019: } else {
1020: oContUpdt.setNull(27, Types.VARCHAR);
1021: oContUpdt.setNull(28, Types.VARCHAR);
1022: oContUpdt.setNull(29, Types.CHAR);
1023: }
1024: if (DebugFile.trace)
1025: DebugFile
1026: .writeln("PreparedStatement.executeUpdate(oContUpdt)");
1027: iAffected = oContUpdt.executeUpdate();
1028: if (DebugFile.trace)
1029: DebugFile.writeln("affected="
1030: + String.valueOf(iAffected));
1031: } // fi (MODE_UPDATE && !bIsNewContact)
1032:
1033: if (test(iFlags, MODE_APPEND) && (iAffected == 0)) {
1034: if (DebugFile.trace)
1035: DebugFile
1036: .writeln("CONTACT MODE_APPEND AND affected=0");
1037: oContInst
1038: .setObject(1, aValues[gu_workarea], Types.CHAR);
1039: oContInst.setString(2, getColNull(tx_nickname));
1040: oContInst.setString(3, getColNull(tx_pwd));
1041: oContInst.setString(4, getColNull(tx_challenge));
1042: oContInst.setString(5, getColNull(tx_reply));
1043: oContInst.setObject(6, aValues[dt_pwd_expires],
1044: Types.TIMESTAMP);
1045: if (aValues[dt_modified] == null)
1046: oContInst.setTimestamp(7, tsNow);
1047: else
1048: oContInst.setObject(7, aValues[dt_modified],
1049: Types.TIMESTAMP);
1050: oContInst.setString(8, getColNull(gu_writer));
1051: if (test(iFlags, WRITE_COMPANIES))
1052: oContInst.setString(9, getColNull(gu_company));
1053: else
1054: oContInst.setNull(9, Types.CHAR);
1055: oContInst.setString(10, getColNull(id_contact_status));
1056: oContInst.setString(11, getColNull(id_contact_ref));
1057: oContInst.setString(12, getColNull(tx_name));
1058: oContInst.setString(13, getColNull(tx_surname));
1059: oContInst.setString(14, getColNull(de_title));
1060: oContInst.setString(15, getColNull(id_gender));
1061: oContInst.setObject(16, aValues[dt_birth],
1062: Types.TIMESTAMP);
1063: oContInst.setObject(17, aValues[ny_age], Types.INTEGER);
1064: oContInst.setString(18, getColNull(sn_passport));
1065: oContInst.setString(19, getColNull(tp_passport));
1066: oContInst.setString(20, getColNull(sn_drivelic));
1067: oContInst.setObject(21, aValues[dt_drivelic],
1068: Types.TIMESTAMP);
1069: oContInst.setString(22, getColNull(tx_dept));
1070: oContInst.setString(23, getColNull(tx_division));
1071: oContInst.setString(24, getColNull(gu_geozone));
1072: oContInst.setString(25, getColNull(tx_comments));
1073: if (DebugFile.trace)
1074: DebugFile.writeln("PreparedStatement.setString(26,"
1075: + aValues[gu_contact] + ")");
1076: oContInst.setString(26, (String) aValues[gu_contact]);
1077: if (DebugFile.trace)
1078: DebugFile
1079: .writeln("PreparedStatement.executeUpdate(oContInst)");
1080: iAffected = oContInst.executeUpdate();
1081: if (DebugFile.trace)
1082: DebugFile.writeln("affected="
1083: + String.valueOf(iAffected));
1084: } // fi (MODE_APPEND && iAffected==0)
1085: } // fi (WRITE_CONTACTS)
1086:
1087: if (test(iFlags, WRITE_LOOKUPS)) {
1088: addLookUp("tp_location", getColNull(tp_location), oConn,
1089: oAddrLook, oAddrWook, oAddrLocsMap);
1090: addLookUp("tp_street", getColNull(tp_street), oConn,
1091: oAddrLook, oAddrWook, oAddrTypesMap);
1092: addLookUp("tx_salutation", getColNull(tp_street), oConn,
1093: oAddrLook, oAddrWook, oAddrSalutMap);
1094: } // if (test(WRITE_LOOKUPS))
1095:
1096: iAffected = 0;
1097: if (test(iFlags, MODE_UPDATE) && getColNull(gu_address) != null) {
1098: if (DebugFile.trace)
1099: DebugFile
1100: .writeln("ADDRESS MODE_UPDATE AND IS NOT NEW ADDRESS");
1101:
1102: if (null != aValues[ix_address])
1103: oAddrUpdt.setObject(1, aValues[ix_address],
1104: Types.INTEGER);
1105: else {
1106: if (test(iFlags, WRITE_CONTACTS))
1107: oAddrUpdt.setInt(1, Address.nextLocalIndex(oConn,
1108: "k_x_contact_addr", "gu_contact",
1109: (String) aValues[gu_contact]));
1110: else
1111: oAddrUpdt.setInt(1, Address.nextLocalIndex(oConn,
1112: "k_x_company_addr", "gu_company",
1113: (String) aValues[gu_company]));
1114: }
1115: oAddrUpdt.setObject(2, aValues[gu_workarea], Types.CHAR);
1116: if (null != aValues[bo_active])
1117: oAddrUpdt.setObject(3, aValues[bo_active],
1118: Types.SMALLINT);
1119: else
1120: oAddrUpdt.setShort(3, (short) 1);
1121: if (aValues[dt_modified] == null)
1122: oAddrUpdt.setTimestamp(4, tsNow);
1123: else
1124: oAddrUpdt.setObject(4, aValues[dt_modified],
1125: Types.TIMESTAMP);
1126: oAddrUpdt.setString(5, getColNull(tp_location));
1127: if (test(iFlags, WRITE_COMPANIES))
1128: oAddrUpdt
1129: .setString(
1130: 6,
1131: (String) (getColNull(nm_commercial) == null ? getColNull(nm_legal)
1132: : aValues[nm_commercial]));
1133: else
1134: oAddrUpdt.setNull(6, Types.VARCHAR);
1135: oAddrUpdt.setString(7, getColNull(tp_street));
1136: oAddrUpdt.setString(8, getColNull(nm_street));
1137: oAddrUpdt.setString(9, getColNull(nu_street));
1138: oAddrUpdt.setString(10, getColNull(tx_addr1));
1139: oAddrUpdt.setString(11, getColNull(tx_addr2));
1140: oAddrUpdt.setString(12, getColNull(id_country));
1141: oAddrUpdt.setString(13, getColNull(nm_country));
1142: oAddrUpdt.setString(14, getColNull(id_state));
1143: oAddrUpdt.setString(15, getColNull(nm_state));
1144: oAddrUpdt.setString(16, getColNull(mn_city));
1145: oAddrUpdt.setString(17, getColNull(zipcode));
1146: oAddrUpdt.setString(18, getColNull(work_phone));
1147: oAddrUpdt.setString(19, getColNull(direct_phone));
1148: oAddrUpdt.setString(20, getColNull(home_phone));
1149: oAddrUpdt.setString(21, getColNull(mov_phone));
1150: oAddrUpdt.setString(22, getColNull(fax_phone));
1151: oAddrUpdt.setString(23, getColNull(other_phone));
1152: oAddrUpdt.setString(24, getColNull(po_box));
1153: oAddrUpdt.setString(25, getColNull(tx_email));
1154: oAddrUpdt.setString(26, getColNull(tx_email_alt));
1155: oAddrUpdt.setString(27, getColNull(url_addr));
1156: oAddrUpdt.setObject(28, aValues[coord_x], Types.FLOAT);
1157: oAddrUpdt.setObject(29, aValues[coord_y], Types.FLOAT);
1158: oAddrUpdt.setString(30, getColNull(contact_person));
1159: oAddrUpdt.setString(31, getColNull(tx_salutation));
1160: oAddrUpdt.setString(32, getColNull(id_address_ref));
1161: oAddrUpdt.setString(33, getColNull(tx_remarks));
1162: if (DebugFile.trace)
1163: DebugFile.writeln("PreparedStatement.setString(34,"
1164: + aValues[gu_address] + ")");
1165: oAddrUpdt.setString(34, (String) aValues[gu_address]);
1166: if (test(iFlags, NO_DUPLICATED_MAILS)) {
1167: oAddrUpdt.setString(35, getColNull(tx_email));
1168: oAddrUpdt.setString(36, (String) aValues[gu_workarea]);
1169: } else {
1170: oAddrUpdt.setNull(35, Types.VARCHAR);
1171: oAddrUpdt.setNull(36, Types.CHAR);
1172: }
1173: if (DebugFile.trace)
1174: DebugFile
1175: .writeln("PreparedStatement.executeUpdate(oAddrUpdt)");
1176: iAffected = oAddrUpdt.executeUpdate();
1177: if (DebugFile.trace)
1178: DebugFile.writeln("affected="
1179: + String.valueOf(iAffected));
1180: }
1181:
1182: if (test(iFlags, MODE_APPEND) && (iAffected == 0)) {
1183: if (DebugFile.trace)
1184: DebugFile.writeln("ADDRESS MODE_APPEND AND affected=0");
1185:
1186: if (null != aValues[ix_address])
1187: oAddrInst.setObject(1, aValues[ix_address],
1188: Types.INTEGER);
1189: else
1190: oAddrInst.setInt(1, Address.nextLocalIndex(oConn,
1191: "k_x_contact_addr", "gu_contact",
1192: (String) aValues[gu_contact]));
1193: oAddrInst.setString(2, (String) aValues[gu_workarea]);
1194: if (null != aValues[bo_active])
1195: oAddrInst.setObject(3, aValues[bo_active],
1196: Types.SMALLINT);
1197: else
1198: oAddrInst.setShort(3, (short) 1);
1199: if (aValues[dt_modified] == null)
1200: oAddrInst.setTimestamp(4, tsNow);
1201: else
1202: oAddrInst.setObject(4, aValues[dt_modified],
1203: Types.TIMESTAMP);
1204: oAddrInst.setString(5, getColNull(tp_location));
1205: if (test(iFlags, WRITE_COMPANIES))
1206: oAddrInst
1207: .setString(
1208: 6,
1209: (String) (getColNull(nm_commercial) == null ? getColNull(nm_legal)
1210: : aValues[nm_commercial]));
1211: else
1212: oAddrInst.setNull(6, Types.VARCHAR);
1213: oAddrInst.setString(7, getColNull(tp_street));
1214: oAddrInst.setString(8, getColNull(nm_street));
1215: oAddrInst.setString(9, getColNull(nu_street));
1216: oAddrInst.setString(10, getColNull(tx_addr1));
1217: oAddrInst.setString(11, getColNull(tx_addr2));
1218: oAddrInst.setString(12, getColNull(id_country));
1219: oAddrInst.setString(13, getColNull(nm_country));
1220: oAddrInst.setString(14, getColNull(id_state));
1221: oAddrInst.setString(15, getColNull(nm_state));
1222: oAddrInst.setString(16, getColNull(mn_city));
1223: oAddrInst.setString(17, getColNull(zipcode));
1224: oAddrInst.setString(18, getColNull(work_phone));
1225: oAddrInst.setString(19, getColNull(direct_phone));
1226: oAddrInst.setString(20, getColNull(home_phone));
1227: oAddrInst.setString(21, getColNull(mov_phone));
1228: oAddrInst.setString(22, getColNull(fax_phone));
1229: oAddrInst.setString(23, getColNull(other_phone));
1230: oAddrInst.setString(24, getColNull(po_box));
1231: oAddrInst.setString(25, getColNull(tx_email));
1232: oAddrInst.setString(26, getColNull(tx_email_alt));
1233: oAddrInst.setString(27, getColNull(url_addr));
1234: oAddrInst.setObject(28, aValues[coord_x], Types.FLOAT);
1235: oAddrInst.setObject(29, aValues[coord_y], Types.FLOAT);
1236: oAddrInst.setString(30, getColNull(contact_person));
1237: oAddrInst.setString(31, getColNull(tx_salutation));
1238: oAddrInst.setString(32, getColNull(id_address_ref));
1239: oAddrInst.setString(33, getColNull(tx_remarks));
1240: if (DebugFile.trace)
1241: DebugFile.writeln("PreparedStatement.setString(34,"
1242: + aValues[gu_address] + ")");
1243: oAddrInst.setString(34, (String) aValues[gu_address]);
1244: if (DebugFile.trace)
1245: DebugFile
1246: .writeln("PreparedStatement.executeUpdate(oAddrInst)");
1247: iAffected = oAddrInst.executeUpdate();
1248: if (DebugFile.trace)
1249: DebugFile.writeln("affected="
1250: + String.valueOf(iAffected));
1251:
1252: if (test(iFlags, WRITE_COMPANIES)
1253: && !test(iFlags, WRITE_CONTACTS)) {
1254: if (DebugFile.trace)
1255: DebugFile
1256: .writeln("Writting link between company and address");
1257: oCompAddr.setString(1, (String) aValues[gu_company]);
1258: oCompAddr.setString(2, (String) aValues[gu_address]);
1259: if (DebugFile.trace)
1260: DebugFile
1261: .writeln("PreparedStatement.executeUpdate(oCompAddr)");
1262: oCompAddr.executeUpdate();
1263: } else if (test(iFlags, WRITE_CONTACTS)) {
1264: if (DebugFile.trace)
1265: DebugFile
1266: .writeln("Writting link between contact and address");
1267: oContAddr.setString(1, (String) aValues[gu_contact]);
1268: oContAddr.setString(2, (String) aValues[gu_address]);
1269: if (DebugFile.trace)
1270: DebugFile
1271: .writeln("PreparedStatement.executeUpdate(oContAddr)");
1272: oContAddr.executeUpdate();
1273: }
1274: } // fi test(iFlags,MODE_APPEND) && (iAffected==0))
1275:
1276: if (DebugFile.trace) {
1277: DebugFile.decIdent();
1278: DebugFile.writeln("End ContactLoader.store()");
1279: }
1280: } // store
1281:
1282: // ---------------------------------------------------------------------------
1283:
1284: public static final int MODE_APPEND = ImportLoader.MODE_APPEND;
1285: public static final int MODE_UPDATE = ImportLoader.MODE_UPDATE;
1286: public static final int MODE_APPENDUPDATE = ImportLoader.MODE_APPENDUPDATE;
1287: public static final int WRITE_LOOKUPS = ImportLoader.WRITE_LOOKUPS;
1288:
1289: public static final int WRITE_COMPANIES = 32;
1290: public static final int WRITE_CONTACTS = 64;
1291: public static final int WRITE_ADDRESSES = 128;
1292: public static final int NO_DUPLICATED_NAMES = 256;
1293: public static final int NO_DUPLICATED_MAILS = 512;
1294:
1295: // ---------------------------------------------------------------------------
1296:
1297: // Keep this list sorted
1298: private static final String[] ColumnNames = { "", "bo_active",
1299: "bo_change_pwd", "bo_private", "contact_person", "coord_x",
1300: "coord_y", "de_company", "de_title", "direct_phone",
1301: "dt_birth", "dt_created", "dt_drivelic", "dt_founded",
1302: "dt_modified", "dt_pwd_expires", "fax_phone", "gu_address",
1303: "gu_company", "gu_contact", "gu_geozone", "gu_sales_man",
1304: "gu_workarea", "gu_writer", "home_phone", "id_address_ref",
1305: "id_company_ref", "id_company_status", "id_contact_ref",
1306: "id_contact_status", "id_country", "id_gender", "id_legal",
1307: "id_sector", "id_state", "im_revenue", "ix_address",
1308: "mn_city", "mov_phone", "nm_commercial", "nm_company",
1309: "nm_country", "nm_legal", "nm_state", "nm_street",
1310: "nu_employees", "nu_street", "ny_age", "other_phone",
1311: "po_box", "sn_drivelic", "sn_passport", "tp_company",
1312: "tp_location", "tp_passport", "tp_street", "tx_addr1",
1313: "tx_addr2", "tx_challenge", "tx_comments", "tx_dept",
1314: "tx_division", "tx_email", "tx_email_alt", "tx_franchise",
1315: "tx_name", "tx_nickname", "tx_pwd", "tx_remarks",
1316: "tx_reply", "tx_salutation", "tx_surname", "url_addr",
1317: "work_phone", "zipcode" };
1318:
1319: // ---------------------------------------------------------------------------
1320:
1321: // Keep these column indexes in sync with ColumnNames array
1322: public static int bo_active = 1;
1323: public static int bo_change_pwd = 2;
1324: public static int bo_private = 3;
1325: public static int contact_person = 4;
1326: public static int coord_x = 5;
1327: public static int coord_y = 6;
1328: public static int de_company = 7;
1329: public static int de_title = 8;
1330: public static int direct_phone = 9;
1331: public static int dt_birth = 10;
1332: public static int dt_created = 11;
1333: public static int dt_drivelic = 12;
1334: public static int dt_founded = 13;
1335: public static int dt_modified = 14;
1336: public static int dt_pwd_expires = 15;
1337: public static int fax_phone = 16;
1338: public static int gu_address = 17;
1339: public static int gu_company = 18;
1340: public static int gu_contact = 19;
1341: public static int gu_geozone = 20;
1342: public static int gu_sales_man = 21;
1343: public static int gu_workarea = 22;
1344: public static int gu_writer = 23;
1345: public static int home_phone = 24;
1346: public static int id_address_ref = 25;
1347: public static int id_company_ref = 26;
1348: public static int id_company_status = 27;
1349: public static int id_contact_ref = 28;
1350: public static int id_contact_status = 29;
1351: public static int id_country = 30;
1352: public static int id_gender = 31;
1353: public static int id_legal = 32;
1354: public static int id_sector = 33;
1355: public static int id_state = 34;
1356: public static int im_revenue = 35;
1357: public static int ix_address = 36;
1358: public static int mn_city = 37;
1359: public static int mov_phone = 38;
1360: public static int nm_commercial = 39;
1361: //public static int nm_company =40;
1362: public static int nm_country = 41;
1363: public static int nm_legal = 42;
1364: public static int nm_state = 43;
1365: public static int nm_street = 44;
1366: public static int nu_employees = 45;
1367: public static int nu_street = 46;
1368: public static int ny_age = 47;
1369: public static int other_phone = 48;
1370: public static int po_box = 49;
1371: public static int sn_drivelic = 50;
1372: public static int sn_passport = 51;
1373: public static int tp_company = 52;
1374: public static int tp_location = 53;
1375: public static int tp_passport = 54;
1376: public static int tp_street = 55;
1377: public static int tx_addr1 = 56;
1378: public static int tx_addr2 = 57;
1379: public static int tx_challenge = 58;
1380: public static int tx_comments = 59;
1381: public static int tx_dept = 60;
1382: public static int tx_division = 61;
1383: public static int tx_email = 62;
1384: public static int tx_email_alt = 63;
1385: public static int tx_franchise = 64;
1386: public static int tx_name = 65;
1387: public static int tx_nickname = 66;
1388: public static int tx_pwd = 67;
1389: public static int tx_remarks = 68;
1390: public static int tx_reply = 69;
1391: public static int tx_salutation = 70;
1392: public static int tx_surname = 71;
1393: public static int url_addr = 72;
1394: public static int work_phone = 73;
1395: public static int zipcode = 74;
1396: }
|