001: /*
002: Copyright (C) 2003 Know Gate S.L. All rights reserved.
003: C/Oņa, 107 1ē2 28050 Madrid (Spain)
004:
005: Redistribution and use in source and binary forms, with or without
006: modification, are permitted provided that the following conditions
007: are met:
008:
009: 1. Redistributions of source code must retain the above copyright
010: notice, this list of conditions and the following disclaimer.
011:
012: 2. The end-user documentation included with the redistribution,
013: if any, must include the following acknowledgment:
014: "This product includes software parts from hipergate
015: (http://www.hipergate.org/)."
016: Alternately, this acknowledgment may appear in the software itself,
017: if and wherever such third-party acknowledgments normally appear.
018:
019: 3. The name hipergate must not be used to endorse or promote products
020: derived from this software without prior written permission.
021: Products derived from this software may not be called hipergate,
022: nor may hipergate appear in their name, without prior written
023: permission.
024:
025: This library is distributed in the hope that it will be useful,
026: but WITHOUT ANY WARRANTY; without even the implied warranty of
027: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
028:
029: You should have received a copy of hipergate License with this code;
030: if not, visit http://www.hipergate.org or mail to info@hipergate.org
031: */
032:
033: package com.knowgate.crm;
034:
035: import java.io.FileNotFoundException;
036: import java.io.IOException;
037: import java.io.UnsupportedEncodingException;
038:
039: import java.sql.SQLException;
040: import java.sql.Statement;
041: import java.sql.ResultSet;
042:
043: import com.knowgate.debug.DebugFile;
044: import com.knowgate.jdc.JDCConnection;
045: import com.knowgate.misc.Gadgets;
046: import com.knowgate.misc.CSVParser;
047: import com.knowgate.dataobjs.DB;
048:
049: /**
050: * <p>DirectList</p>
051: * <p>A subclass of DistributionList with methods for loading List Members from
052: * text files.</p>
053: * <p>Copyright: Copyright (c) KnowGate 2003-2006</p>
054: * @author Sergio Montoro Ten
055: * @version 3.0
056: */
057:
058: public class DirectList extends DistributionList {
059:
060: private CSVParser oCSV;
061:
062: // ----------------------------------------------------------
063:
064: /**
065: * Default constructor
066: */
067: public DirectList() {
068: oCSV = new CSVParser();
069: }
070:
071: // ----------------------------------------------------------
072:
073: /**
074: * Constructor
075: * @param String Name of character set to be used when parsing files (ISO-8859-1, UTF-8, etc.)
076: * @since 3.0
077: */
078: public DirectList(String sCharSetName) {
079: oCSV = new CSVParser(sCharSetName);
080: }
081:
082: // ----------------------------------------------------------
083:
084: /**
085: * Get last error line
086: * @return int
087: */
088: public int errorLine() {
089: return oCSV.errorLine();
090: }
091:
092: // ----------------------------------------------------------
093:
094: /**
095: * Get line count after parsing a text file
096: * @return int
097: */
098: public int getLineCount() {
099: return oCSV.getLineCount();
100: }
101:
102: // ----------------------------------------------------------
103:
104: private int[] checkValues() throws IllegalStateException {
105:
106: String sMail, sName, sSurN, sSalt, sFrmt;
107:
108: if (DebugFile.trace) {
109: DebugFile.writeln("Begin DirectList.checkValues()");
110: DebugFile.incIdent();
111: }
112:
113: if (0 == oCSV.getLineCount())
114: throw new IllegalStateException(
115: "Trying to parse an empty file");
116:
117: int iMail = getColumnPosition(DB.tx_email);
118: int iName = getColumnPosition(DB.tx_name);
119: int iSurN = getColumnPosition(DB.tx_surname);
120: int iSalt = getColumnPosition(DB.tx_salutation);
121: int iFrmt = getColumnPosition(DB.id_format);
122:
123: int CheckCodes[] = new int[oCSV.getLineCount()];
124:
125: int iLines = oCSV.getLineCount();
126:
127: for (int r = 0; r < iLines; r++) {
128: CheckCodes[r] = CHECK_OK;
129:
130: if (iMail >= 0) {
131: sMail = getField(iMail, r);
132: if (sMail.length() > 100)
133: CheckCodes[r] = CHECK_INVALID_EMAIL;
134: else if (!Gadgets.checkEMail(sMail))
135: CheckCodes[r] = CHECK_INVALID_EMAIL;
136: }
137:
138: if (iName >= 0) {
139: sName = getField(iName, r);
140: if (sName.length() > 100)
141: CheckCodes[r] = CHECK_NAME_TOO_LONG;
142: else if (sName.indexOf(',') >= 0
143: || sName.indexOf(';') >= 0
144: || sName.indexOf('`') >= 0
145: || sName.indexOf('¨') >= 0
146: || sName.indexOf('?') >= 0
147: || sName.indexOf('"') >= 0)
148: CheckCodes[r] = CHECK_INVALID_NAME;
149: }
150:
151: if (iSurN >= 0) {
152: sSurN = getField(iName, r);
153: if (sSurN.length() > 100)
154: CheckCodes[r] = CHECK_SURNAME_TOO_LONG;
155: else if (sSurN.indexOf(',') >= 0
156: || sSurN.indexOf(';') >= 0
157: || sSurN.indexOf('`') >= 0
158: || sSurN.indexOf('¨') >= 0
159: || sSurN.indexOf('?') >= 0
160: || sSurN.indexOf('"') >= 0)
161: CheckCodes[r] = CHECK_INVALID_SURNAME;
162: }
163:
164: if (iSalt >= 0) {
165: sSalt = getField(iSalt, r);
166: if (sSalt.length() > 16)
167: CheckCodes[r] = CHECK_SALUTATION_TOO_LONG;
168: else if (sSalt.indexOf(',') >= 0
169: || sSalt.indexOf(';') >= 0
170: || sSalt.indexOf('`') >= 0
171: || sSalt.indexOf('¨') >= 0
172: || sSalt.indexOf('?') >= 0
173: || sSalt.indexOf('"') >= 0)
174: CheckCodes[r] = CHECK_INVALID_SALUTATION;
175: }
176:
177: if (iFrmt >= 0) {
178: sFrmt = getField(iFrmt, r);
179: if (sFrmt.length() > 4)
180: CheckCodes[r] = CHECK_INVALID_FORMAT;
181: else if (sFrmt.indexOf(',') >= 0
182: || sFrmt.indexOf(';') >= 0
183: || sFrmt.indexOf('`') >= 0
184: || sFrmt.indexOf('¨') >= 0
185: || sFrmt.indexOf('?') >= 0
186: || sFrmt.indexOf('"') >= 0)
187: CheckCodes[r] = CHECK_INVALID_SALUTATION;
188: }
189:
190: } // next
191:
192: if (DebugFile.trace) {
193: DebugFile.decIdent();
194: DebugFile.writeln("End DirectList.checkValues()");
195: }
196:
197: return CheckCodes;
198: } // checkValues
199:
200: // ----------------------------------------------------------
201:
202: /**
203: * Parse a delimited text file
204: * @param sFilePath File Path
205: * @param sFileDescriptor Delimited Column List.<br>
206: * The only valid column names are { tx_email, tx_name, tx_surname, id_format, tx_salutation }.<br>
207: * Column names may be delimited by ',' ';' or '\t'.
208: * Columns names may be quoted.
209: * @return Array of status for each parsed line.<br>
210: * <table><tr><td>CHECK_OK</td><td>Line is OK</td></tr>
211: * <tr><td>CHECK_INVALID_EMAIL</td><td>tx_email is longer than 100 characters or it is rejected by method Gadgets.checkEMail()</td></tr>
212: * <tr><td>CHECK_NAME_TOO_LONG</td><td>tx_name is longer than 100 characters</td></tr>
213: * <tr><td>CHECK_INVALID_NAME</td><td>tx_name contains forbidden characters { ',' ';' '`' '¨' '?' '"' }</td></tr>
214: * <tr><td>CHECK_SURNAME_TOO_LONG</td><td>tx_surname is longer than 100 characters</td></tr>
215: * <tr><td>CHECK_INVALID_SURNAME</td><td>tx_surname contains forbidden characters { ',' ';' '`' '¨' '?' '"' }</td></tr>
216: * <tr><td>CHECK_INVALID_FORMAT</td><td>id_format is longer than 4 characters</td></tr>
217: * <tr><td>CHECK_SALUTATION_TOO_LONG</td><td>tx_salutation is longer than 16 characters</td></tr>
218: * <tr><td>CHECK_INVALID_SALUTATION</td><td>tx_salutation contains forbidden characters { ',' ';' '`' '¨' '?' '"' }</td></tr>
219: * </table>
220: * @throws ArrayIndexOutOfBoundsException
221: * @throws FileNotFoundException
222: * @throws IllegalArgumentException
223: * @throws IOException
224: * @throws NullPointerException
225: * @throws UnsupportedEncodingException
226: * @see com.knowgate.misc.CSVParser
227: */
228: public int[] parseFile(String sFilePath, String sFileDescriptor)
229: throws ArrayIndexOutOfBoundsException,
230: NullPointerException, IllegalArgumentException,
231: UnsupportedEncodingException, IOException,
232: FileNotFoundException {
233: int[] aRetVals;
234:
235: if (DebugFile.trace) {
236: DebugFile.writeln("Begin DirectList.parseFile(" + sFilePath
237: + "," + sFileDescriptor + "," + ")");
238: DebugFile.incIdent();
239: }
240:
241: oCSV.parseFile(sFilePath, sFileDescriptor);
242:
243: aRetVals = checkValues();
244:
245: if (DebugFile.trace) {
246: DebugFile.decIdent();
247: DebugFile.writeln("End DirectList.parseFile()");
248: }
249:
250: return aRetVals;
251: } // parseFile
252:
253: // ----------------------------------------------------------
254:
255: /**
256: * @param sColumnName Column Name
257: * @return Zero based index for column position or -1 if column was not found.
258: */
259: public int getColumnPosition(String sColumnName) {
260: return oCSV.getColumnPosition(sColumnName);
261: } // getColumnPosition
262:
263: // ----------------------------------------------------------
264:
265: /**
266: * <p>Get line from a parsed file.</p>
267: * Lines are delimited by the Line Feed (LF, CHAR(10), '\n') character
268: * @param iLine Line Number [0..getLineCount()-1]
269: * @return Full Text for Line. If iLine<0 or iLine>=getLineCount() then <b>null</b>
270: * @throws IllegalStateException If parseFile() has not been called prior to getLine()
271: */
272: public String getLine(int iLine) throws IllegalStateException {
273: String sRetVal;
274:
275: try {
276: sRetVal = oCSV.getLine(iLine);
277: } catch (java.io.UnsupportedEncodingException e) {
278: sRetVal = null;
279: }
280:
281: return sRetVal;
282: } // getLine
283:
284: // ----------------------------------------------------------
285:
286: /**
287: * <p>Get value for a field at a given row and column.</p>
288: * Column indexes are zero based.
289: * Row indexes range from 0 to getLineCount()-1.
290: * @param iCol Column Index
291: * @param iRow Row Index
292: * @return Field Value
293: * @throws IllegalStateException If parseFile() method was not called prior to
294: * getField()
295: * @throws ArrayIndexOutOfBoundsException If Column or Row Index is out of bounds.
296: */
297:
298: public String getField(int iCol, int iRow)
299: throws ArrayIndexOutOfBoundsException {
300: String sRetVal;
301:
302: try {
303: sRetVal = oCSV.getField(iCol, iRow);
304: } catch (java.io.UnsupportedEncodingException e) {
305: sRetVal = null;
306: }
307:
308: return sRetVal;
309: } // getField
310:
311: // ----------------------------------------------------------
312:
313: /**
314: * <p>Get value for a field at a given row and column.</p>
315: * @param sCol Column Name
316: * @param iRow Row Name
317: * @throws ArrayIndexOutOfBoundsException
318: */
319: public String getField(String sCol, int iRow)
320: throws ArrayIndexOutOfBoundsException {
321:
322: int iCol = getColumnPosition(sCol);
323:
324: if (iCol == -1)
325: throw new ArrayIndexOutOfBoundsException("Column " + sCol
326: + " not found");
327:
328: return getField(iCol, iRow);
329: }
330:
331: // ----------------------------------------------------------
332:
333: /**
334: * <p>Adds members to a Static, Direct or Black Distribution List.</p>
335: * @param oConn Database connection
336: * @param sListId DistributionList GUID
337: * @param iStatus 1 if loaded members are to be set as active, 0 if loaded member are to be set as unactive.
338: * @throws IllegalArgumentException If DistributionList does not exist.
339: * @throws ClassCastException If sListId type is DYNAMIC.
340: * @throws IllegalStateException If parseFile() has not been called prior to updateList()
341: * @throws StringIndexOutOfBoundsException If a row if malformed
342: * @throws SQLException
343: */
344: public void updateList(JDCConnection oConn, String sListId,
345: short iStatus) throws IllegalArgumentException,
346: IllegalStateException, ClassCastException, SQLException {
347: Statement oStmt;
348: ResultSet oRSet;
349: boolean bExists;
350: short iTpList;
351:
352: if (DebugFile.trace) {
353: DebugFile
354: .writeln("Begin DirectList.updateList([Connection], "
355: + sListId + ")");
356: DebugFile.incIdent();
357: }
358:
359: oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
360: ResultSet.CONCUR_READ_ONLY);
361: oRSet = oStmt.executeQuery("SELECT " + DB.tp_list + " FROM "
362: + DB.k_lists + " WHERE " + DB.gu_list + "='" + sListId
363: + "'");
364: bExists = oRSet.next();
365: if (bExists)
366: iTpList = oRSet.getShort(1);
367: else
368: iTpList = -1;
369: oRSet.close();
370: oStmt.close();
371:
372: if (!bExists)
373: throw new IllegalArgumentException("List does not exist");
374:
375: if (iTpList == com.knowgate.crm.DistributionList.TYPE_DYNAMIC)
376: throw new IllegalArgumentException(
377: "Dynamic lists cannot be updated by directly loading members");
378:
379: if (0 == oCSV.getLineCount())
380: throw new IllegalStateException(
381: "Must call parseFile() on a valid non-empty delimited file before calling updateList() method");
382:
383: int iMail = getColumnPosition(DB.tx_email);
384: int iName = getColumnPosition(DB.tx_name);
385: int iSurN = getColumnPosition(DB.tx_surname);
386: int iSalt = getColumnPosition(DB.tx_salutation);
387: int iFrmt = getColumnPosition(DB.id_format);
388:
389: int ChkCods[] = checkValues();
390:
391: ListMember oMbr = new ListMember();
392:
393: oMbr.put(DB.tp_member, ListMember.ClassId);
394: oMbr.put(DB.bo_active, iStatus);
395:
396: int iLines = oCSV.getLineCount();
397:
398: for (int r = 0; r < iLines; r++) {
399:
400: if (ChkCods[r] == CHECK_OK) {
401:
402: oMbr.replace(DB.gu_member, Gadgets.generateUUID());
403:
404: oMbr.replace(DB.tx_email, getField(iMail, r));
405:
406: oMbr.replace(DB.tx_name, getField(iName, r));
407:
408: oMbr.replace(DB.tx_surname, getField(iSurN, r));
409:
410: oMbr.replace(DB.tx_salutation, getField(iSalt, r));
411:
412: oMbr.replace(DB.tp_member, ListMember.ClassId);
413:
414: if (iFrmt >= 0)
415: oMbr.replace(DB.id_format, getField(iFrmt, r)
416: .toUpperCase());
417: else
418: oMbr.replace(DB.id_format, "TXT");
419:
420: try {
421: oMbr.store(oConn, sListId);
422: } catch (NoSuchFieldException nsfe) {
423: /* never really thrown with paramenters passed to ListMember.store() from this method */
424: }
425: } // fi (CHECK_OK)
426: } // next
427:
428: if (DebugFile.trace) {
429: DebugFile.decIdent();
430: DebugFile.writeln("End DirectList.updateList()");
431: }
432: } // updateList
433:
434: // ----------------------------------------------------------
435:
436: /**
437: * <p>Remove members from a Static, Direct or Black Distribution List.</p>
438: * Members are matched by their e-mail address (tx_email column)
439: * @param oConn Database connection
440: * @param sListId DistributionList GUID
441: * @throws IllegalArgumentException If DistributionList does not exist.
442: * @throws ClassCastException If sListId type is DYNAMIC
443: * @throws IllegalStateException If parseFile() has not been called prior to updateList()
444: * @throws SQLException
445: */
446: public void removeFromList(JDCConnection oConn, String sListId)
447: throws IllegalArgumentException, SQLException,
448: ClassCastException {
449: Statement oDlte;
450: Statement oStmt;
451: ResultSet oRSet;
452: boolean bExists;
453: short iTpList;
454: String sSQL;
455:
456: if (DebugFile.trace) {
457: DebugFile
458: .writeln("Begin DirectList.removeFromList([Connection], "
459: + sListId + ")");
460: DebugFile.incIdent();
461: }
462:
463: oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
464: ResultSet.CONCUR_READ_ONLY);
465: oRSet = oStmt.executeQuery("SELECT " + DB.tp_list + " FROM "
466: + DB.k_lists + " WHERE " + DB.gu_list + "='" + sListId
467: + "'");
468: bExists = oRSet.next();
469: if (bExists)
470: iTpList = oRSet.getShort(1);
471: else
472: iTpList = -1;
473: oRSet.close();
474: oStmt.close();
475:
476: if (!bExists)
477: throw new IllegalArgumentException("List does not exist");
478:
479: if (iTpList == com.knowgate.crm.DistributionList.TYPE_DYNAMIC)
480: throw new ClassCastException(
481: "Dynamic lists cannot be updated by directly removing members");
482:
483: if (0 == oCSV.getLineCount())
484: throw new IllegalStateException(
485: "Must call parseFile() on a valid non-empty delimited file before calling updateList() method");
486:
487: int iMail = getColumnPosition(DB.tx_email);
488:
489: int ChkCods[] = checkValues();
490:
491: int iLines = oCSV.getLineCount();
492:
493: sSQL = "DELETE FROM " + DB.k_x_list_members + " WHERE "
494: + DB.gu_list + "='" + sListId + "' AND " + DB.tx_email
495: + "=";
496:
497: oDlte = oConn.createStatement();
498:
499: for (int r = 0; r < iLines; r++) {
500: if (ChkCods[r] == CHECK_OK)
501: oDlte.addBatch(sSQL + "'" + getField(iMail, r) + "'");
502: } // next
503:
504: oDlte.executeBatch();
505: oDlte.close();
506:
507: if (DebugFile.trace) {
508: DebugFile.decIdent();
509: DebugFile.writeln("End DirectList.removeFromList()");
510: }
511: } // removeFromList
512:
513: // ----------------------------------------------------------
514:
515: // **********************************************************
516: // Constantes Publicas
517:
518: public static final int CHECK_OK = 0;
519: public static final int CHECK_INVALID_EMAIL = 1;
520: public static final int CHECK_NAME_TOO_LONG = 2;
521: public static final int CHECK_SURNAME_TOO_LONG = 4;
522: public static final int CHECK_INVALID_FORMAT = 8;
523: public static final int CHECK_SALUTATION_TOO_LONG = 16;
524: public static final int CHECK_INVALID_NAME = 32;
525: public static final int CHECK_INVALID_SURNAME = 64;
526: public static final int CHECK_INVALID_SALUTATION = 128;
527:
528: // **********************************************************
529: // Public Constants
530:
531: public static final short ClassId = 96;
532:
533: }
|