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


1 /*
2   Copyright (C) 2003 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.sql.SQLException ;
36 import java.sql.CallableStatement ;
37 import java.sql.PreparedStatement ;
38 import java.sql.Statement ;
39 import java.sql.ResultSet ;
40 import java.sql.Types ;
41
42 import com.knowgate.debug.DebugFile;
43 import com.knowgate.jdc.JDCConnection;
44 import com.knowgate.misc.Gadgets;
45 import com.knowgate.dataobjs.DB;
46 import com.knowgate.dataobjs.DBCommand;
47 import com.knowgate.dataobjs.DBPersist;
48
49 import com.knowgate.hipergate.QueryByForm;
50
51 /**
52  * <p>Distribution List</p>
53  * @author Sergio Montoro Ten
54  * @version 3.0
55  */
56 public class DistributionList extends DBPersist {
57
58   public DistributionList() {
59     super(DB.k_lists, "DistributionList");
60   }
61
62   /**
63    * Create and load distribution list
64    * @param oConn JDCConnection HDBC Connection
65    * @param sListGUID String List GUID
66    * @throws SQLException
67    */
68   public DistributionList(JDCConnection oConn, String sListGUID) throws SQLException {
69     super(DB.k_lists, "DistributionList");
70     load(oConn, new Object []{sListGUID});
71   }
72
73   // ----------------------------------------------------------
74
75   /**
76    * Create a distribution list and load its by name
77    * @param oConn JDCConnection JDBC Connection
78    * @param sListDesc String List Description
79    * @param sWorkAreaGUID String GUID of WorkArea to which list belongs
80    * @throws SQLException
81    */
82   public DistributionList(JDCConnection oConn, String sListDesc, String sWorkAreaGUID) throws SQLException {
83     super(DB.k_lists, "DistributionList");
84
85     String sListGUID;
86     PreparedStatement oStmt = oConn.prepareStatement("SELECT "+DB.gu_list+" FROM "+DB.k_lists+" WHERE "+DB.gu_workarea+"=? AND "+DB.de_list+"=?",
87                                                      ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
88     oStmt.setString(1, sWorkAreaGUID);
89     oStmt.setString(2, sListDesc);
90     ResultSet oRSet = oStmt.executeQuery();
91     if (oRSet.next())
92       sListGUID = oRSet.getString(1);
93     else
94       sListGUID = null;
95     oRSet.close();
96     oStmt.close();
97
98     if (null!=sListGUID)
99       load(oConn, new Object []{sListGUID});
100   }
101
102   // ----------------------------------------------------------
103
104   /**
105    * Count active members of this list
106    * @param oConn JDBC Database Connection
107    * @return Count of members of this list which bo_active field is not zero.
108    * @throws SQLException
109    */
110   public int memberCount(JDCConnection oConn) throws SQLException {
111
112     if (DebugFile.trace) {
113       DebugFile.writeln("Begin DistributionList.memberCount([Connection])");
114       DebugFile.incIdent();
115     }
116
117     String sSQL;
118     String sTableName;
119     String sWhere;
120     Statement oStmt;
121     ResultSet oRSet;
122     int iCount;
123
124     String sBlackList = blackList(oConn);
125
126     if (getShort(DB.tp_list)==TYPE_DYNAMIC) {
127       sTableName = DB.k_member_address;
128
129       QueryByForm oQBF = new QueryByForm(oConn, DB.k_member_address, "m", getString(DB.gu_query));
130
131       sWhere = "m." + DB.gu_workarea + "='" + getString(DB.gu_workarea) + "' AND ";
132       sWhere+= "(" + oQBF.composeSQL() + ") AND ";
133       sWhere+= " NOT EXISTS (SELECT " + DB.tx_email + " FROM " + DB.k_x_list_members + " b WHERE b." + DB.gu_list + "='" + sBlackList + "' AND b." + DB.tx_email + "=m." + DB.tx_email + ")";
134
135       oQBF = null;
136     }
137     else {
138       sTableName = DB.k_x_list_members;
139       sWhere = "m." + DB.gu_list + "='" + getString(DB.gu_list) + "' AND ";
140       sWhere+= "m." + DB.bo_active + "<>0 ";
141
142       if (getShort(DB.tp_list)!=TYPE_BLACK)
143         sWhere+= " AND NOT EXISTS (SELECT " + DB.tx_email + " FROM " + DB.k_x_list_members + " b WHERE b." + DB.gu_list + "='" + sBlackList + "' AND b." + DB.tx_email + "=m." + DB.tx_email + ")";
144     }
145
146     sSQL = "SELECT COUNT(*) FROM " + sTableName + " m WHERE " + sWhere;
147
148     oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
149
150     try { oStmt.setQueryTimeout(120); } catch (SQLException sqle) { /* ignore */}
151
152     if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(" + sSQL + ")");
153
154     oRSet = oStmt.executeQuery(sSQL);
155
156     oRSet.next();
157
158     if (oConn.getDataBaseProduct()==JDCConnection.DBMS_ORACLE)
159       iCount = oRSet.getBigDecimal(1).intValue();
160     else
161       iCount = oRSet.getInt(1);
162
163     oRSet.close();
164     oStmt.close();
165
166     if (DebugFile.trace) {
167       DebugFile.decIdent();
168       DebugFile.writeln("End DistributionList.memberCount()");
169     }
170
171     return iCount;
172   } // memberCount
173
174   // ----------------------------------------------------------
175
176   /**
177    * Get e-mail address for all active members
178    * @param oConn JDBC Database Connection
179    * @return String with e-mail addresses delimited by commas
180    * @throws SQLException
181    * @throws IllegalStateException
182    */
183   public String activeMembers(JDCConnection oConn)
184     throws SQLException ,IllegalStateException {
185
186     if (DebugFile.trace) {
187       DebugFile.writeln("Begin DistributionList.activeMembers([Connection])");
188       if (isNull(DB.tp_list))
189         throw new IllegalStateException ("DistributionList.activeMembers() list type not set");
190       DebugFile.incIdent();
191     }
192
193     String sSQL;
194     String sTableName;
195     String sWhere;
196     StringBuffer oBuffer;
197     Statement oStmt;
198     ResultSet oRSet;
199
200     String sBlackList = blackList(oConn);
201
202     if (getShort(DB.tp_list)==TYPE_DYNAMIC) {
203       sTableName = DB.k_member_address;
204
205       QueryByForm oQBF = new QueryByForm(oConn, DB.k_member_address, "m", getString(DB.gu_query));
206
207       sWhere = "m." + DB.gu_workarea + "='" + getString(DB.gu_workarea) + "' AND ";
208       sWhere+= "(" + oQBF.composeSQL() + ") AND ";
209       sWhere+= " NOT EXISTS (SELECT " + DB.tx_email + " FROM " + DB.k_x_list_members + " b WHERE b." + DB.gu_list + "='" + sBlackList + "' AND b." + DB.tx_email + "=m." + DB.tx_email + ")";
210
211       oQBF = null;
212     }
213     else {
214       sTableName = DB.k_x_list_members;
215       sWhere = "m." + DB.gu_list + "='" + getString(DB.gu_list) + "' AND ";
216       sWhere+= "m." + DB.bo_active + "<>0 ";
217
218       if (getShort(DB.tp_list)!=TYPE_BLACK)
219         sWhere+= " AND NOT EXISTS (SELECT " + DB.tx_email + " FROM " + DB.k_x_list_members + " b WHERE b." + DB.gu_list + "='" + sBlackList + "' AND b." + DB.tx_email + "=m." + DB.tx_email + ")";
220     }
221
222     sSQL = "SELECT " + DB.tx_email + " FROM " + sTableName + " m WHERE " + sWhere;
223
224     oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
225
226     try { oStmt.setQueryTimeout(120); } catch (SQLException sqle) { /* ignore */}
227
228     if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(" + sSQL + ")");
229
230     oRSet = oStmt.executeQuery(sSQL);
231
232     try { oRSet.setFetchSize(500); } catch (SQLException sqle) { /* ignore */}
233
234     oBuffer = new StringBuffer (4096);
235
236     if (oRSet.next())
237       oBuffer.append(oRSet.getString(1));
238
239     while (oRSet.next()) {
240       oBuffer.append(",");
241       oBuffer.append(oRSet.getString(1));
242     } // wend
243
244     oRSet.close();
245     oStmt.close();
246
247     if (DebugFile.trace) {
248       DebugFile.decIdent();
249       DebugFile.writeln("End DistributionList.activeMembers()");
250     }
251
252     return oBuffer.toString();
253   } // activeMembers
254
255   // ----------------------------------------------------------
256
257   /**
258    * Get GUIDs for all active contacts
259    * @param oConn JDBC Database Connection
260    * @return String with GUIDs delimited by commas
261    * @throws SQLException
262    */
263
264   public String activeContacts(JDCConnection oConn) throws SQLException {
265
266     if (getShort(DB.tp_list)==TYPE_DIRECT)
267       throw new SQLException ("Contacts cannot be directly retrived for DIRECT lists");
268
269     if (DebugFile.trace) {
270       DebugFile.writeln("Begin DistributionList.activeContacts([Connection])");
271       DebugFile.incIdent();
272     }
273
274     String sSQL;
275     String sTableName = null;
276     String sWhere = null;
277     StringBuffer oBuffer;
278     Statement oStmt;
279     ResultSet oRSet;
280
281     String sBlackList = blackList(oConn);
282
283     if (getShort(DB.tp_list)==TYPE_DYNAMIC) {
284       sTableName = DB.k_member_address;
285
286       QueryByForm oQBF = new QueryByForm(oConn, DB.k_member_address, "m", getString(DB.gu_query));
287
288       sWhere = "m." + DB.gu_workarea + "='" + getString(DB.gu_workarea) + "' AND ";
289       sWhere+= "(" + oQBF.composeSQL() + ") AND " + DB.gu_contact + " IS NOT NULL AND ";
290       sWhere+= " NOT EXISTS (SELECT " + DB.tx_email + " FROM " + DB.k_x_list_members + " b WHERE b." + DB.gu_list + "='" + sBlackList + "' AND b." + DB.tx_email + "=m." + DB.tx_email + ")";
291
292       oQBF = null;
293     }
294     else if (getShort(DB.tp_list)!=TYPE_DIRECT) {
295       sTableName = DB.k_x_list_members;
296       sWhere = "m." + DB.gu_list + "='" + getString(DB.gu_list) + "' AND ";
297       sWhere+= "m." + DB.bo_active + "<>0 AND " + DB.gu_contact + " IS NOT NULL ";
298
299       if (getShort(DB.tp_list)!=TYPE_BLACK)
300         sWhere+= " AND NOT EXISTS (SELECT " + DB.tx_email + " FROM " + DB.k_x_list_members + " b WHERE b." + DB.gu_list + "='" + sBlackList + "' AND b." + DB.tx_email + "=m." + DB.tx_email + ")";
301     }
302
303     sSQL = "SELECT " + DB.gu_contact + " FROM " + sTableName + " m WHERE " + sWhere;
304
305     oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
306
307     try { oStmt.setQueryTimeout(120); } catch (SQLException sqle) { /* ignore */}
308
309     if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(" + sSQL + ")");
310
311     oRSet = oStmt.executeQuery(sSQL);
312
313     try { oRSet.setFetchSize(500); } catch (SQLException sqle) { /* ignore */}
314
315     oBuffer = new StringBuffer (4096);
316
317     if (oRSet.next())
318       oBuffer.append(oRSet.getString(1));
319
320     while (oRSet.next()) {
321       oBuffer.append(",");
322       oBuffer.append(oRSet.getString(1));
323     } // wend
324
325     oRSet.close();
326     oStmt.close();
327
328     if (DebugFile.trace) {
329       DebugFile.decIdent();
330       DebugFile.writeln("End DistributionList.activeContacts()");
331     }
332
333     return oBuffer.toString();
334   }
335
336   // ----------------------------------------------------------
337
338   /**
339    * Get GUIDs for all active companies
340    * @param oConn JDBC Database Connection
341    * @return String with GUIDs delimited by commas
342    * @throws SQLException
343    */
344
345   public String activeCompanies(JDCConnection oConn) throws SQLException {
346
347     if (getShort(DB.tp_list)==TYPE_DIRECT)
348       throw new SQLException ("Companies cannot be directly retrived for DIRECT lists");
349
350     if (DebugFile.trace) {
351       DebugFile.writeln("Begin DistributionList.activeCompanies([Connection])");
352       DebugFile.incIdent();
353     }
354
355     String sBlackList = blackList(oConn);
356
357     String sSQL;
358     String sTableName;
359     String sWhere;
360     StringBuffer oBuffer;
361     Statement oStmt;
362     ResultSet oRSet;
363
364     if (getShort(DB.tp_list)==TYPE_DYNAMIC) {
365       sTableName = DB.k_member_address;
366
367       QueryByForm oQBF = new QueryByForm(oConn, DB.k_member_address, "m", getString(DB.gu_query));
368
369       sWhere = "m." + DB.gu_workarea + "='" + getString(DB.gu_workarea) + "' AND ";
370       sWhere+= "(" + oQBF.composeSQL() + ") AND " + DB.gu_company + " IS NOT NULL AND ";
371       sWhere+= " NOT EXISTS (SELECT " + DB.tx_email + " FROM " + DB.k_x_list_members + " b WHERE b." + DB.gu_list + "='" + sBlackList + "' AND b." + DB.tx_email + "=m." + DB.tx_email + ")";
372
373       oQBF = null;
374     }
375     else {
376       sTableName = DB.k_x_list_members;
377       sWhere = "m." + DB.gu_list + "='" + getString(DB.gu_list) + "' AND ";
378       sWhere+= "m." + DB.bo_active + "<>0 AND " + DB.gu_company + " IS NOT NULL ";
379
380       if (getShort(DB.tp_list)!=TYPE_BLACK)
381         sWhere+= " AND NOT EXISTS (SELECT " + DB.tx_email + " FROM " + DB.k_x_list_members + " b WHERE b." + DB.gu_list + "='" + sBlackList + "' AND b." + DB.tx_email + "=m." + DB.tx_email + ")";
382     }
383
384     sSQL = "SELECT " + DB.gu_company + " FROM " + sTableName + " m WHERE " + sWhere;
385
386     oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
387
388     try { oStmt.setQueryTimeout(120); } catch (SQLException sqle) { /* ignore */}
389
390     if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(" + sSQL + ")");
391
392     oRSet = oStmt.executeQuery(sSQL);
393
394     try { oRSet.setFetchSize(500); } catch (SQLException sqle) { /* ignore */}
395
396     oBuffer = new StringBuffer (4096);
397
398     if (oRSet.next())
399       oBuffer.append(oRSet.getString(1));
400
401     while (oRSet.next()) {
402       oBuffer.append(",");
403       oBuffer.append(oRSet.getString(1));
404     } // wend
405
406     oRSet.close();
407     oStmt.close();
408
409     if (DebugFile.trace) {
410       DebugFile.decIdent();
411       DebugFile.writeln("End DistributionList.activeCompanies()");
412     }
413
414     return oBuffer.toString();
415   } // activeCompanies
416
417   // ----------------------------------------------------------
418
419   /**
420    * <p>Find out if list contains a particular member</p>
421    * Member is searched at k_x_list_members table either at gu_company and
422    * gu_contact or tx_email. If list is Dynamic or Static, member is searched at
423    * gu_company and gu_contact. If list is Direct or Black, member is searched at tx_email
424    * @param oConn Database Connection
425    * @param sMember If this is a Static or Dynamic list then sMember must be the GUID of Contact or Company searched.<br>
426    * If this is a Direct or Black list then sMember must be an e-mail address.
427    * @return <b>true</b> if sMember is contained in list.<br>
428    * Take into account that the member may be unactive or blocked and still be contained at the list.<br>
429    * Unactive (k_x_list_members.bo_active=0) and blocked (present at black list) members should not receive any e-mails.
430    * @throws SQLException
431    */
432   public boolean contains (JDCConnection oConn, String sMember) throws SQLException {
433     boolean bRetVal;
434     PreparedStatement oStmt;
435     ResultSet oRSet;
436     QueryByForm oQBF;
437
438     if (DebugFile.trace) {
439       DebugFile.writeln("Begin DistributionList.contains([Connection], " + sMember + ")");
440       DebugFile.incIdent();
441     }
442
443     switch (getShort(DB.tp_list)) {
444
445       case TYPE_DYNAMIC:
446         oQBF = new QueryByForm(oConn, DB.k_member_address, "ma", getString (DB.gu_query));
447
448         if (DebugFile.trace)
449           DebugFile.writeln("Connection.prepareStatement(SELECT NULL FROM " + DB.k_member_address + " ma WHERE ma." + DB.gu_workarea + "=? AND (ma." + DB.gu_contact + "='" + sMember + "' OR ma." + DB.gu_company + "='" + sMember + "') AND (" + oQBF.composeSQL() + "))");
450
451         oStmt = oConn.prepareStatement("SELECT NULL FROM " + DB.k_member_address + " ma WHERE ma." + DB.gu_workarea + "=? AND (ma." + DB.gu_contact + "=? OR ma." + DB.gu_company + "=?) AND (" + oQBF.composeSQL() + ")", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
452
453         oStmt.setString(1, getString(DB.gu_workarea));
454         oStmt.setString(2, sMember);
455         oStmt.setString(3, sMember);
456         oRSet = oStmt.executeQuery();
457         bRetVal = oRSet.next();
458         oRSet.close();
459         oStmt.close();
460
461         oQBF = null;
462         break;
463
464       case TYPE_STATIC:
465
466         if (DebugFile.trace)
467           DebugFile.writeln("Connection.prepareStatement(SELECT NULL FROM " + DB.k_x_list_members + " WHERE " + DB.gu_list + "='" + getString(DB.gu_list) + "' AND (" + DB.gu_contact + "='" + sMember + "' OR " + DB.gu_company + "='" + sMember + "'))");
468
469         oStmt = oConn.prepareStatement("SELECT NULL FROM " + DB.k_x_list_members + " WHERE " + DB.gu_list + "=? AND (" + DB.gu_contact + "=? OR " + DB.gu_company + "=?)", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
470         oStmt.setString(1, getString(DB.gu_list));
471         oStmt.setString(2, sMember);
472         oStmt.setString(3, sMember);
473         oRSet = oStmt.executeQuery();
474         bRetVal = oRSet.next();
475         oRSet.close();
476         oStmt.close();
477         break;
478
479       case TYPE_DIRECT:
480       case TYPE_BLACK:
481
482         if (DebugFile.trace)
483           DebugFile.writeln("Connection.prepareStatement(SELECT NULL FROM " + DB.k_x_list_members + " WHERE " + DB.gu_list + "=? AND " + DB.tx_email + "='" + sMember + "')");
484
485         oStmt = oConn.prepareStatement("SELECT NULL FROM " + DB.k_x_list_members + " WHERE " + DB.gu_list + "=? AND " + DB.tx_email + "=?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
486         oStmt.setString(1, getString(DB.gu_list));
487         oStmt.setString(2, sMember);
488         oRSet = oStmt.executeQuery();
489         bRetVal = oRSet.next();
490         oRSet.close();
491         oStmt.close();
492         break;
493
494       default:
495         throw new java.lang.IllegalArgumentException ("DistributionList.contains() invalid value of tp_list property");
496     }
497
498     if (DebugFile.trace) {
499       DebugFile.decIdent();
500       DebugFile.writeln("End DistributionList.contains() : " + String.valueOf(bRetVal));
501     }
502     return bRetVal;
503   }
504
505   // ----------------------------------------------------------
506
507   /**
508    * Store DistributionList
509    * Automatically generates gu_list GUID if not explicitly set.
510    * @param oConn Database Connection
511    * @throws SQLException
512    */
513   public boolean store(JDCConnection oConn) throws SQLException {
514
515     if (!AllVals.containsKey(DB.gu_list))
516       put(DB.gu_list, Gadgets.generateUUID());
517
518     return super.store(oConn);
519   } // store()
520
521   // ----------------------------------------------------------
522
523   public boolean delete(JDCConnection oConn) throws SQLException {
524     return DistributionList.delete(oConn, getString(DB.gu_list));
525   } // delete()
526
527   // ----------------------------------------------------------
528
529   /**
530    * <p>Get associated Black List GUID</p>
531    * The Black List is that witch tp_list=BLACK_LIST AND gu_query=this.gu_list
532    * @param oConn Database Connection
533    * @return Black List GUID or <b>null</b> if there is no associated Black List.
534    * @throws SQLException
535    * @throws IllegalStateException if this DistributionList has not been previously loaded
536    */
537   public String blackList(JDCConnection oConn) throws SQLException , IllegalStateException {
538     PreparedStatement oStmt;
539     ResultSet oRSet;
540     String sBlackListId;
541
542     if (DebugFile.trace) {
543       DebugFile.writeln("Begin DistributionList.blackList([Connection])");
544       if (isNull(DB.gu_workarea))
545         throw new IllegalStateException ("DistributionList.blackList() workarea is not set");
546       if (isNull(DB.gu_list))
547         throw new IllegalStateException ("DistributionList.blackList() list GUID is not set");
548       DebugFile.incIdent();
549     }
550
551     oStmt = oConn.prepareStatement("SELECT " + DB.gu_list + " FROM " + DB.k_lists + " WHERE " + DB.gu_workarea + "=? AND " + DB.tp_list + "=" + String.valueOf(TYPE_BLACK) + " AND " + DB.gu_query + "=?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
552
553     try { oStmt.setQueryTimeout(10); } catch (SQLException e) { }
554
555     oStmt.setString(1, getString(DB.gu_workarea));
556     oStmt.setString(2, getString(DB.gu_list));
557
558     try { oStmt.setQueryTimeout(20); } catch (SQLException sqle) { }
559
560     oRSet = oStmt.executeQuery();
561
562     if (oRSet.next())
563       sBlackListId = oRSet.getString(1);
564     else
565       sBlackListId = null;
566
567     oRSet.close();
568     oStmt.close();
569
570     if (DebugFile.trace) {
571       DebugFile.decIdent();
572       DebugFile.writeln("End DistributionList.blackList() : " + (sBlackListId!=null ? sBlackListId : "null"));
573     }
574
575     return sBlackListId;
576   } // blackList()
577
578   // ----------------------------------------------------------
579
580   /**
581    * <p>Append members of a list to this DistributionList.<p>
582    * Members that where already present are not touched.
583    * Results are placed at this DistributionList.
584    * @param oConn Database Connection
585    * @param sListGUID GUID of DistributionList to be appended
586    * @throws SQLException
587    * @throws IllegalArgumentException If sListGUID==null
588    * @throws IllegalStateException If this.gu_list is not set
589    * @throws ClassCastException If this DistributionList type is DYNAMIC
590    */
591   public void append(JDCConnection oConn, String sListGUID) throws SQLException ,IllegalArgumentException ,IllegalStateException ,ClassCastException {
592     Statement oInsrt;
593     String sSQL;
594     String sColumnList;
595     DistributionList oAppendedList;
596
597     if (DebugFile.trace) {
598       DebugFile.writeln("Begin DistributionList.append([Connection], " + (sListGUID!=null ? sListGUID : "null") + ")");
599       DebugFile.incIdent();
600     }
601
602     if (null==sListGUID)
603       throw new IllegalArgumentException ("list id cannot be null");
604
605     if (null==get(DB.gu_list))
606       throw new IllegalStateException ("list id not set");
607
608     if (getShort(DB.tp_list)==DistributionList.TYPE_DYNAMIC)
609       throw new ClassCastException ("append operation not supported for Dynamic lists");
610
611     if (sListGUID.equals(getString(DB.gu_list))) return;
612
613     oAppendedList = new DistributionList(oConn, sListGUID);
614
615     // *******************************************************************
616 // A�adir los miembros que no estuviesen ya presentes en la lista base
617
618     oInsrt = oConn.createStatement();
619
620     try { oInsrt.setQueryTimeout(120); } catch (SQLException sqle) { /* ignore */}
621
622     if (oAppendedList.getShort(DB.tp_list)==TYPE_DYNAMIC) {
623
624       // Componer la sentencia SQL de filtrado de datos a partir de la definici�n de la consulta almacenada en la tabla k_queries
625 QueryByForm oQBF = new QueryByForm(oConn, DB.k_member_address, "ma", oAppendedList.getString(DB.gu_query));
626       sColumnList = DB.tx_email + "," + DB.tx_name + "," + DB.tx_surname + "," + DB.tx_salutation + "," + DB.gu_company + "," + DB.gu_contact;
627
628       sSQL = "INSERT INTO " + DB.k_x_list_members + " ("+DB.gu_list+"," + sColumnList + ") " +
629              "SELECT '" + getString(DB.gu_list) + "'," + sColumnList + " FROM " + DB.k_member_address + " ma WHERE ma.gu_workarea='" + oAppendedList.getString(DB.gu_workarea) + "' AND (" + oQBF.composeSQL() + ") AND " +
630              "ma." + DB.tx_email + " NOT IN (SELECT " + DB.tx_email + " FROM " + DB.k_x_list_members + " WHERE " + DB.gu_list + "='" + getString(DB.gu_list) + "')";
631     }
632
633     else {
634
635       sColumnList = DB.tx_email + "," + DB.tx_name + "," + DB.tx_surname + "," + DB.tx_salutation + "," + DB.bo_active + "," + DB.gu_company + "," + DB.gu_contact + "," + DB.id_format;
636
637       sSQL = "INSERT INTO " + DB.k_x_list_members + " ("+DB.gu_list+"," + sColumnList + ") " +
638              "SELECT '" + getString(DB.gu_list) + "'," + sColumnList + " FROM " + DB.k_x_list_members + " WHERE " + DB.gu_list + "='" + sListGUID + "' AND " +
639              DB.tx_email + " NOT IN (SELECT " + DB.tx_email + " FROM " + DB.k_x_list_members + " WHERE " + DB.gu_list + "='" + getString(DB.gu_list) + "')";
640
641     }
642
643     if (DebugFile.trace) DebugFile.writeln("Statement.execute(" + sSQL + ")");
644
645     oInsrt.execute(sSQL);
646     oInsrt.close();
647
648     if (DebugFile.trace) {
649       DebugFile.decIdent();
650       DebugFile.writeln("End DistributionList.append()");
651     }
652   } // append
653
654   // ----------------------------------------------------------
655
656   /**
657    * Overwrite members of this DistributionList with members of given DistributionList.
658    * Members of sListGUID not present at this list are NOT appended.
659    * @param oConn Database Connection
660    * @throws SQLException
661    * @throws IllegalArgumentException If sListGUID==null
662    * @throws IllegalStateException If this.gu_list is not set
663    * @throws ClassCastException If this DistributionList type is DYNAMIC
664    */
665   public void overwrite(JDCConnection oConn, String sListGUID) throws SQLException ,IllegalArgumentException ,ClassCastException ,IllegalStateException {
666     Statement oInsrt;
667     PreparedStatement oUpdt;
668     ResultSet oRSet;
669     String sSQL;
670     String sColumnList;
671     DistributionList oAppendedList;
672
673     if (DebugFile.trace) {
674       DebugFile.writeln("Begin DistributionList.overwrite([Connection], " + (sListGUID!=null ? sListGUID : "null") + ")");
675       DebugFile.incIdent();
676     }
677
678     if (null==sListGUID)
679       throw new IllegalArgumentException ("list id cannot be null");
680
681     if (null==get(DB.gu_list))
682       throw new IllegalStateException ("list id not set");
683
684     if (getShort(DB.tp_list)==DistributionList.TYPE_DYNAMIC)
685       throw new ClassCastException ("overwrite operation not supported for Dynamic lists");
686
687     if (sListGUID.equals(getString(DB.gu_list))) return;
688
689     oAppendedList = new DistributionList(oConn, sListGUID);
690
691     sColumnList = DB.tx_email + "," + DB.tx_name + "," + DB.tx_surname + "," + DB.tx_salutation + "," + DB.bo_active + "," + DB.gu_company + "," + DB.gu_contact + "," + DB.id_format;
692
693     // ************************************************************************************
694 // Actualizar los miembros de la lista a a�adir que ya est�n presentes en la lista base
695
696     // Preparar la sentencia de actualizaci�n de registros en la lista base
697 sSQL = "UPDATE " + DB.k_x_list_members + " SET " + DB.tx_name + "=?," + DB.tx_surname + "=?," + DB.tx_salutation + "=?," + DB.bo_active + "=?," + DB.gu_company + "=?," + DB.gu_contact + "=?," + DB.id_format + "=? WHERE " + DB.gu_list + "='" + getString(DB.gu_list) + "' AND " + DB.tx_email + "=?";
698
699     if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(" + sSQL + ")");
700
701     oUpdt = oConn.prepareStatement(sSQL);
702
703     // Preparar la sentencia para leer registros comunes en la lista a�adida
704 oInsrt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
705
706     try { oInsrt.setQueryTimeout(60); } catch (SQLException sqle) { /* ignore */}
707
708     if (oAppendedList.getShort(DB.tp_list)==TYPE_DYNAMIC) {
709       QueryByForm oQBF = new QueryByForm(oConn, DB.k_member_address, "b", oAppendedList.getString(DB.gu_query));
710
711       sSQL = "SELECT b." + DB.tx_name + ",b." + DB.tx_surname + ",b." + DB.tx_salutation + ",1,b." + DB.gu_company + ",b." + DB.gu_contact + ",'TXT', a." + DB.tx_email + " FROM " + DB.k_x_list_members + " a, " + DB.k_member_address + " b WHERE a." + DB.gu_list + "='" + getString(DB.gu_list) + "' AND b." + DB.gu_workarea + "='" + oAppendedList.getString(DB.gu_workarea) + "' AND (" + oQBF.composeSQL() + ") AND a." + DB.tx_email + "=b." + DB.tx_email;
712     }
713     else
714       sSQL = "SELECT b." + DB.tx_name + ",b." + DB.tx_surname + ",b." + DB.tx_salutation + ",b." + DB.bo_active + ",b." + DB.gu_company + ",b." + DB.gu_contact + ",b." + DB.id_format + ", a." + DB.tx_email + " FROM " + DB.k_x_list_members + " a, " + DB.k_x_list_members + " b WHERE a." + DB.gu_list + "='" + getString(DB.gu_list) + "' AND b." + DB.gu_list + "='" + sListGUID + "' AND a." + DB.tx_email + "=b." + DB.tx_email;
715
716     if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(" + sSQL + ")");
717
718     oRSet = oInsrt.executeQuery(sSQL);
719
720     // Recorrer los registros de la lista a�adida que ya est�n en la lista base
721 // y actualizar sus campos con los valores de la lista a�adida.
722 while (oRSet.next()) {
723       oUpdt.setObject(1, oRSet.getObject(1), Types.VARCHAR); // tx_name
724 oUpdt.setObject(2, oRSet.getObject(2), Types.VARCHAR); // tx_surname
725 oUpdt.setObject(3, oRSet.getObject(3), Types.VARCHAR); // tx_salutation
726 oUpdt.setObject(4, oRSet.getObject(4), Types.SMALLINT); // bo_active
727 oUpdt.setObject(5, oRSet.getObject(5), Types.VARCHAR); // gu_company
728 oUpdt.setObject(6, oRSet.getObject(6), Types.VARCHAR); // gu_contact
729 oUpdt.setObject(7, oRSet.getObject(7), Types.VARCHAR); // id_format
730 oUpdt.setObject(8, oRSet.getObject(8), Types.VARCHAR); // tx_email
731 oUpdt.executeUpdate();
732     } // wend
733
734     oInsrt.close();
735     oUpdt.close();
736
737     if (DebugFile.trace) {
738       DebugFile.decIdent();
739       DebugFile.writeln("End DistributionList.overwrite()");
740     }
741   } // overwrite
742
743   // ----------------------------------------------------------
744
745   /**
746    * Remove from this DistributionList those members present at given DistributionList.
747    * @param oConn Database Connection
748    * @throws SQLException
749    * @throws IllegalArgumentException If sListGUID==null
750    * @throws IllegalStateException If this.gu_list is not set
751    * @throws ClassCastException If this DistributionList type is DYNAMIC
752    */
753   public void substract(JDCConnection oConn, String sListGUID) throws SQLException ,IllegalArgumentException ,IllegalStateException ,ClassCastException {
754     String sSQL;
755     Statement oDlte;
756     DistributionList oAppendedList;
757
758     if (DebugFile.trace) {
759       DebugFile.writeln("Begin DistributionList.substract([Connection], " + (sListGUID!=null ? sListGUID : "null") + ")");
760       DebugFile.incIdent();
761     }
762
763     if (null==sListGUID)
764       throw new IllegalArgumentException ("list id cannot be null");
765
766     if (null==get(DB.gu_list))
767       throw new IllegalStateException ("list id not set");
768
769     if (getShort(DB.tp_list)==DistributionList.TYPE_DYNAMIC)
770       throw new ClassCastException ("substract operation not supported for Dynamic lists");
771
772     oAppendedList = new DistributionList(oConn, sListGUID);
773
774     if (sListGUID.equals(getString(DB.gu_list)))
775
776       sSQL = "DELETE FROM " + DB.k_x_list_members + " WHERE " + DB.gu_list + "='" + getString(DB.gu_list) + "'";
777
778     else if (oAppendedList.getShort(DB.tp_list)==TYPE_DYNAMIC) {
779
780       QueryByForm oQBF = new QueryByForm(oConn, DB.k_member_address, "ma", oAppendedList.getString(DB.gu_query));
781
782       sSQL = "DELETE FROM " + DB.k_x_list_members + " WHERE " + DB.gu_list + "='" + getString(DB.gu_list) + "' AND " + DB.tx_email + " IN (SELECT " + DB.tx_email + " FROM " + DB.k_member_address + " ma WHERE ma." + DB.gu_workarea + "='" + oAppendedList.getString(DB.gu_workarea) + "' AND (" + oQBF.composeSQL() + "))";
783     }
784
785     else
786
787       sSQL = "DELETE FROM " + DB.k_x_list_members + " WHERE " + DB.gu_list + "='" + getString(DB.gu_list) + "' AND " + DB.tx_email + " IN (SELECT " + DB.tx_email + " FROM " + DB.k_x_list_members + " WHERE " + DB.gu_list + "='" + sListGUID + "')";
788
789     oDlte = oConn.createStatement();
790
791     if (DebugFile.trace) DebugFile.writeln("Statement.execute(" + sSQL + ")");
792
793     oDlte.execute(sSQL);
794
795     oDlte.close();
796
797     if (DebugFile.trace) {
798       DebugFile.decIdent();
799       DebugFile.writeln("End DistributionList.substract()");
800     }
801   } // substract
802
803   // ----------------------------------------------------------
804
805   /**
806    * Clone this DistributionList.
807    * The associated Black List, if it exists, is also cloned and associated to the new clone.
808    * @param oConn Database Connection
809    * @return New DistributionList GUID
810    * @throws SQLException
811    */
812   public String clone(JDCConnection oConn) throws SQLException {
813     String sSQL;
814     String sCloneId;
815     Statement oStmt;
816     DistributionList oClone;
817
818     if (DebugFile.trace) {
819       DebugFile.writeln("Begin DistributionList.clone()");
820       DebugFile.incIdent();
821     }
822
823     oClone = new DistributionList(oConn, getString(DB.gu_list));
824
825     oClone.remove(DB.gu_list);
826     oClone.store(oConn);
827
828     sCloneId = oClone.getString(DB.gu_list);
829
830     oStmt = oConn.createStatement();
831     oStmt.setQueryTimeout(60);
832
833     sSQL = "INSERT INTO " + DB.k_x_list_members + "(gu_list,tx_email,tx_name,tx_surname,tx_salutation,bo_active,tp_member,gu_company,gu_contact,id_format) SELECT '" + oClone.getString(DB.gu_list) + "',tx_email,tx_name,tx_surname,tx_salutation,bo_active,tp_member,gu_company,gu_contact,id_format FROM " + DB.k_x_list_members + " WHERE " + DB.gu_list + "='" + getString(DB.gu_list) + "'";
834
835     if (DebugFile.trace) DebugFile.writeln("Statement.execute(" + sSQL + ")");
836
837     oStmt.execute(sSQL);
838
839     String sBlackList = blackList(oConn);
840
841     if (null!=sBlackList) {
842       oClone = new DistributionList(oConn, sBlackList);
843
844       oClone.remove(DB.gu_list);
845       oClone.replace(DB.gu_query, sCloneId);
846       oClone.store(oConn);
847
848       sSQL = "INSERT INTO " + DB.k_x_list_members + "(gu_list,tx_email,tx_name,tx_surname,tx_salutation,bo_active,tp_member,gu_company,gu_contact,id_format) SELECT '" + oClone.getString(DB.gu_list) + "',tx_email,tx_name,tx_surname,tx_salutation,bo_active,tp_member,gu_company,gu_contact,id_format FROM " + DB.k_x_list_members + " WHERE " + DB.gu_list + "='" + sBlackList + "'";
849
850       if (DebugFile.trace) DebugFile.writeln("Statement.execute(" + sSQL + ")");
851
852       oStmt.execute(sSQL);
853     } // fi(sBlackList)
854
855     oStmt.close();
856
857     if (DebugFile.trace) {
858       DebugFile.decIdent();
859       DebugFile.writeln("End DistributionList.clone() : " + sCloneId);
860     }
861     return sCloneId;
862   } // clone
863
864   // ----------------------------------------------------------
865
866   /**
867    * Print List Members to a comma delimited String.
868    * @param oConn Database Connection
869    * @param bPrintHeader <b>true</b> if column names are to be printed at first row.
870    * @return Comma delimited String with one Member per line.
871    * @throws SQLException
872    */
873   public String print(JDCConnection oConn, boolean bPrintHeader) throws SQLException {
874     String sSQL;
875     String sColumnList;
876     String sTableName;
877     String sWhere;
878     StringBuffer oBuffer;
879     Statement oStmt;
880     ResultSet oRSet;
881     Object oFld;
882
883     // Imprime los miembros de una lista en formato de texto delimitado por comas
884
885     if (DebugFile.trace) {
886       DebugFile.writeln("Begin DistributionList.print([Connection])");
887       DebugFile.incIdent();
888     }
889
890     oBuffer = new StringBuffer ();
891
892     oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
893
894     try { oStmt.setQueryTimeout(120); } catch (SQLException sqle) { /* ignore */}
895
896     sColumnList = "m." + DB.tx_email + ",m." + DB.tx_name + ",m." + DB.tx_surname + ",m." + DB.tx_salutation + ",m." + DB.bo_active + ",m." + DB.gu_company + ",m." + DB.gu_contact + ",m." + DB.dt_modified;
897
898     if (getShort(DB.tp_list)==TYPE_DYNAMIC) {
899       sTableName = DB.k_member_address;
900
901       QueryByForm oQBF = new QueryByForm(oConn, DB.k_member_address, "m", getString(DB.gu_query));
902
903       sWhere = "m." + DB.gu_workarea + "='" + getString(DB.gu_workarea) + "' AND (" + oQBF.composeSQL() + ")";
904
905       oQBF = null;
906     }
907     else {
908       sTableName = DB.k_x_list_members;
909       sWhere = "m." + DB.gu_list + "='" + getString(DB.gu_list) + "'";
910     }
911
912     sSQL = "SELECT " + sColumnList + " FROM " + sTableName + " m WHERE " + sWhere;
913
914     if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(" + sSQL + ")");
915
916     oRSet = oStmt.executeQuery(sSQL);
917
918     try { oRSet.setFetchSize(500); } catch (SQLException sqle) { /* ignore */}
919
920     if (bPrintHeader) oBuffer.append(sColumnList + "\n");
921
922     while (oRSet.next()) {
923       oBuffer.append(oRSet.getString(1));
924       oBuffer.append(",");
925
926       oFld = oRSet.getObject(2); // tx_name
927 if (!oRSet.wasNull()) oBuffer.append(oFld);
928       oBuffer.append(",");
929
930       oFld = oRSet.getObject(3); // tx_surname
931 if (!oRSet.wasNull()) oBuffer.append(oFld);
932       oBuffer.append(",");
933
934       oFld = oRSet.getObject(4); // tx_salutation
935 if (!oRSet.wasNull()) oBuffer.append(oFld);
936       oBuffer.append(",");
937
938       oBuffer.append(String.valueOf(oRSet.getShort(5)));
939       oBuffer.append(",");
940
941       oFld = oRSet.getObject(6); // gu_company
942 if (!oRSet.wasNull()) oBuffer.append(oFld);
943       oBuffer.append(",");
944
945       oFld = oRSet.getObject(7); // gu_contact
946 if (!oRSet.wasNull()) oBuffer.append(oFld);
947       oBuffer.append(",");
948
949       oBuffer.append(oRSet.getString(8));
950       oBuffer.append("\n");
951     } // wend
952
953     oRSet.close();
954     oStmt.close();
955
956     if (DebugFile.trace) {
957       DebugFile.decIdent();
958       DebugFile.writeln("End DistributionList.print()");
959     }
960
961     return oBuffer.toString();
962   } // print()
963
964   // ----------------------------------------------------------
965
966   // **********************************************************
967 // Static Methods
968
969   /**
970    * Delete Distribution List
971    * Call k_sp_del_list stored procedure.<br>
972    * Associated Black List (if present) is also deleted.
973    * @param oConn Database Connection
974    * @param sListGUID GUID of DistributionList to be deleted
975    * @throws SQLException
976    */
977   public static boolean delete(JDCConnection oConn, String sListGUID) throws SQLException {
978     boolean bRetVal;
979
980     if (DebugFile.trace) {
981       DebugFile.writeln("Begin DistributionList.delete([Connection]," + sListGUID + ")");
982       DebugFile.incIdent();
983     }
984
985     if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) {
986       Statement oStmt = oConn.createStatement();
987       oStmt.executeQuery("SELECT k_sp_del_list ('" + sListGUID + "')");
988       oStmt.close();
989       bRetVal = true;
990     } else {
991       CallableStatement oCall = oConn.prepareCall("{ call k_sp_del_list ('" + sListGUID + "') }");
992       bRetVal = oCall.execute();
993       oCall.close();
994     }
995
996     if (DebugFile.trace) {
997       DebugFile.decIdent();
998       DebugFile.writeln("End DistributionList.delete() : " + String.valueOf(bRetVal));
999     }
1000
1001    return bRetVal;
1002  } // delete()
1003
1004  // **********************************************************
1005 // Public Constants
1006
1007  public static final short ClassId = 96;
1008
1009  public static final short TYPE_STATIC=(short)1;
1010  public static final short TYPE_DYNAMIC=(short)2;
1011  public static final short TYPE_DIRECT=(short)3;
1012  public static final short TYPE_BLACK=(short)4;
1013}
1014

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Free Books   Free Magazines  

Popular Tags