KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > sellwin > db > ActivityDB


1 package sellwin.db;
2
3 import sellwin.domain.*;
4 import sellwin.utils.*;
5
6 import java.sql.*;
7 import java.util.ArrayList JavaDoc;
8
9 // SellWin http://sourceforge.net/projects/sellwincrm
10
//Contact support@open-app.com for commercial help with SellWin
11
//This software is provided "AS IS", without a warranty of any kind.
12

13
14 /**
15  * This class implements the DBInterface for
16  * the Activity class objects which are stored
17  * in the activity database table.
18  */

19 public class ActivityDB extends DBType implements DBInterface {
20
21     private Connection con;
22
23     private final static String JavaDoc selectByOppQuery =
24             "SELECT " +
25                 "pk, subject, " +
26                 "type, place, message, start_date, duration, " +
27                 "group_alarm, group_alarm_ack, " +
28                 "alarm, alarm_ack, modified_by, modified_date " +
29             "FROM activity " +
30             "WHERE opp_pk=";
31
32     private final static String JavaDoc selectAlarmsQuery =
33             "SELECT " +
34                 "activity.pk, opportunity.pk, activity.subject, " +
35                 "activity.type, activity.place, activity.message, " +
36                 "activity.start_date, activity.duration," +
37                 "activity.group_alarm, activity.group_alarm_ack, " +
38                 "activity.alarm, activity.alarm_ack, " +
39                 "activity.modified_by, activity.modified_date " +
40             "FROM activity, opportunity, user_group, user_group_member " +
41             "WHERE opportunity.pk = activity.opp_pk AND " +
42             "opportunity.group_name = user_group.name AND " +
43             "user_group.pk = user_group_member.user_group_pk AND " +
44             "activity.alarm = 'Y' AND activity.alarm_ack = 'N' AND ";
45
46     private final static String JavaDoc selectQuery =
47             "SELECT " +
48                 "opp_pk, subject, " +
49                 "type, place, message, start_date, duration, " +
50                 "group_alarm, group_alarm_ack, " +
51                 "alarm, alarm_ack, modified_by, modified_date " +
52             "FROM activity " +
53             "WHERE pk=";
54
55     private final static String JavaDoc updateQuery =
56             "UPDATE activity " +
57             "SET " ;
58
59     private final static String JavaDoc insertQuery =
60             "INSERT INTO activity VALUES ( ";
61
62     private final static String JavaDoc deleteQuery =
63             "DELETE FROM activity WHERE pk =";
64
65     private final static String JavaDoc deleteOppQuery =
66             "DELETE FROM activity where opp_pk = ";
67             
68
69     /**
70      * a do-nothing constructor but necessary to
71      * do the operations offered by this class
72      *
73      */

74     public ActivityDB() {
75     }
76
77     /**
78      * construct using a particular database type
79      * @param dbType the type of database found in Prefs.java
80      */

81     public ActivityDB(int dbType) {
82         DB_TYPE=dbType;
83     }
84
85     /**
86      * a constructor that accepts an existing Connection
87      * to use for future operations
88      *
89      * @param con the Connection to use
90      */

91     public ActivityDB(Connection con) {
92         this.con = con;
93     }
94
95     /**
96      * get the Connection in use
97      *
98      * @return the Connection in use
99      */

100     public Connection getConnection() {
101         return this.con;
102     }
103
104     /**
105      * set the Connection to use
106      *
107      * @param con the Connection to use for any future IO's
108      */

109     public void setConnection(Connection con)
110         throws SQLException {
111
112         this.con = con;
113     }
114
115     /**
116      * select a single activity row using the passed
117      * primary key
118      *
119      * @param pk the primary key to search with
120      * @return the Activity(s) that were selected
121      * @exception java.sql.SQLException
122      */

123     public final Object JavaDoc selectRow(Object JavaDoc pk)
124         throws SQLException {
125
126         Activity activity = new Activity("");
127         long pkValue = ((Long JavaDoc)pk).longValue();
128         activity.setPK(pkValue);
129         Statement stmt = null;
130         ResultSet rs = null;
131         String JavaDoc query = selectQuery + pkValue;
132
133         try {
134             stmt = con.createStatement();
135             if (Prefs.DEBUG) LogWrite.write(query);
136             rs = stmt.executeQuery(query);
137
138             int i;
139             while (rs.next()) {
140                 i=1;
141                 activity.setOppPK(rs.getLong(i)); i++;
142                 activity.setSubject(rs.getString(i)); i++;
143                 activity.setType(rs.getString(i)); i++;
144                 activity.setPlace(rs.getString(i)); i++;
145                 activity.setMessage(rs.getString(i)); i++;
146                 activity.setStartDate(rs.getDate(i)); i++;
147                 activity.setDuration(new Integer JavaDoc(rs.getInt(i))); i++;
148                 boolean x;
149                 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false;
150                 activity.setGroupAlarm(x); i++;
151                 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false;
152                 activity.setGroupAlarmAck(x); i++;
153                 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false;
154                 activity.setAlarm(new Boolean JavaDoc(x)); i++;
155                 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false;
156                 activity.setAlarmAck(x); i++;
157                 activity.setModifiedBy(rs.getString(i)); i++;
158                 activity.setModifiedDate(rs.getDate(i));
159             }
160         } catch (SQLException e) {
161             throw e;
162         } finally {
163             try {
164                 if (rs != null) rs.close();
165             } catch (SQLException x) { throw x; }
166             try {
167                 if (stmt != null) stmt.close();
168             } catch (SQLException x) { throw x; }
169         }
170
171         return activity;
172     }
173
174
175     /**
176      * update a single activity row using the passed
177      * Activity object's attributes. All columns
178      * get updated by this routine regardless of whether
179      * an attribute was modified or not.
180      *
181      * @param obj the object we want to update with
182      * @exception java.sql.SQLException
183      */

184     public void updateRow(Object JavaDoc obj)
185         throws SQLException {
186
187         Activity activity = (Activity)obj;
188         long pk = activity.getPK();
189
190         Statement stmt = null;
191         StringBuffer JavaDoc query = new StringBuffer JavaDoc(updateQuery);
192
193         try {
194             stmt = con.createStatement();
195
196             query.append("subject=");
197             query.append(JDBC.quoteMore(activity.getSubject()));
198             query.append("type=");
199             query.append(JDBC.quoteMore(activity.getType()));
200             query.append("place=");
201             query.append(JDBC.quoteMore(activity.getPlace()));
202             query.append("message=");
203             query.append(JDBC.quoteMore(activity.getMessage()));
204             query.append("start_date=");
205             query.append(JDBC.quoteMore(DateUtils.format(DB_TYPE,activity.getStartDate())));
206             query.append("duration=");
207             query.append(activity.getDuration()).append(",");
208             String JavaDoc x;
209             if (activity.getGroupAlarm()) x="Y"; else x="N";
210             query.append("group_alarm=");
211             query.append(JDBC.quoteMore(x));
212             if (activity.getGroupAlarmAck()) x="Y"; else x="N";
213             query.append("group_alarm_ack=");
214             query.append(JDBC.quoteMore(x));
215             if (activity.getAlarm().booleanValue()) x="Y"; else x="N";
216             query.append("alarm=");
217             query.append(JDBC.quoteMore(x));
218             if (activity.getAlarmAck()) x="Y"; else x="N";
219             query.append("alarm_ack=");
220             query.append(JDBC.quoteMore(x));
221             query.append("modified_by=");
222             query.append(JDBC.quoteMore(activity.getModifiedBy()));
223             if (DB_TYPE == Prefs.MYSQL)
224                 query.append("modified_date=CURRENT_DATE ");
225             else
226                 query.append("modified_date=SYSDATE ");
227             query.append("WHERE pk=").append(activity.getPK());
228
229             if (Prefs.DEBUG) LogWrite.write(query.toString());
230             int updatedRows = stmt.executeUpdate(query.toString());
231         } catch (SQLException e) {
232             throw e;
233         } finally {
234             try { if (stmt != null) stmt.close();
235             } catch (SQLException x) { }
236         }
237     }
238
239     /**
240      * insert a new activity row using the passed
241      * Activity object as the column values.
242      *
243      * @param obj the object we are trying to insert
244      * @param load true if the row is to be loaded, false if
245      * the row is to be added for the first time
246      * @return the newly assigned primary key of the new row
247      * @exception java.sql.SQLException
248      */

249     public long insertRow(Object JavaDoc obj, boolean load)
250         throws SQLException {
251
252         Activity activity = (Activity)obj;
253         if (!load)
254             activity.setPK(DBUtils.generatePK());
255
256         Statement stmt = null;
257         StringBuffer JavaDoc query = new StringBuffer JavaDoc(insertQuery);
258
259         try {
260             stmt = con.createStatement();
261
262             query.append(activity.getPK()).append(",");
263             query.append(activity.getOppPK()).append(",");
264             query.append(JDBC.quoteMore(activity.getSubject()));
265             query.append(JDBC.quoteMore(activity.getType()));
266             query.append(JDBC.quoteMore(activity.getPlace()));
267             query.append(JDBC.quoteMore(activity.getMessage()));
268             query.append(JDBC.quoteMore(DateUtils.format(DB_TYPE, activity.getStartDate())));
269             query.append(activity.getDuration()).append(",");
270             String JavaDoc x;
271             if (activity.getGroupAlarm()) x="Y"; else x="N";
272             query.append(JDBC.quoteMore(x));
273             if (activity.getGroupAlarmAck()) x="Y"; else x="N";
274             query.append(JDBC.quoteMore(x));
275             if (activity.getAlarm().booleanValue()) x = "Y"; else x="N";
276             query.append(JDBC.quoteMore(x));
277             if (activity.getAlarmAck()) x = "Y"; else x="N";
278             query.append(JDBC.quoteMore(x));
279             query.append(JDBC.quoteMore(activity.getModifiedBy()));
280             if (DB_TYPE == Prefs.MYSQL)
281                 query.append("CURRENT_DATE");
282             else
283                 query.append("SYSDATE");
284             query.append(")");
285
286             if (Prefs.DEBUG) LogWrite.write(query.toString());
287             int rc = stmt.executeUpdate(query.toString());
288         } catch (SQLException e) {
289             throw e;
290         } finally {
291             try { if (stmt != null) stmt.close();
292             } catch (SQLException x) { }
293         }
294
295         return activity.getPK();
296     }
297
298     /**
299      * delete a single activity row using the passed
300      * primary key value
301      *
302      * @param ojb primary key stored in a Long
303      * @exception java.sql.SQLException
304      */

305     public final void deleteRow(Object JavaDoc obj)
306         throws SQLException {
307
308         long pkValue = ((Long JavaDoc)obj).longValue();
309         String JavaDoc query = deleteQuery + pkValue;
310     
311         Statement stmt = null;
312         try {
313             stmt = con.createStatement();
314             if (Prefs.DEBUG) LogWrite.write(query);
315             stmt.executeUpdate(query);
316         } catch (SQLException e) {
317             throw e;
318         } finally {
319             try { if (stmt != null) stmt.close();
320             } catch (SQLException x) { }
321         }
322     }
323
324     /**
325      * delete all activity rows using the passed
326      * opportunity primary key value
327      *
328      * @param ojb primary key stored in a Long
329      * @exception java.sql.SQLException
330      */

331     public final void deleteOpportunityRows(Object JavaDoc obj)
332         throws SQLException {
333
334         long opp_pk = ((Long JavaDoc)obj).longValue();
335         String JavaDoc query = deleteOppQuery + opp_pk;
336     
337         Statement stmt = null;
338         try {
339             stmt = con.createStatement();
340             if (Prefs.DEBUG) LogWrite.write(query);
341             stmt.executeUpdate(query);
342         } catch (SQLException e) {
343             throw e;
344         } finally {
345             try { if (stmt != null) stmt.close();
346             } catch (SQLException x) { }
347         }
348     }
349
350     /**
351      * select all activity rows for a given opportunity
352      *
353      * @param opp_pk the opportunity primary key we are searching with
354      * @param list the list of activities owned by the opportunity
355      * which gets filled in by this routine
356      * @exception java.sql.SQLException
357      */

358     public final void selectByOppRows(long opp_pk, ArrayList JavaDoc list)
359         throws SQLException {
360
361         Activity activity = null;
362         Statement stmt = null;
363         ResultSet rs = null;
364         String JavaDoc query = selectByOppQuery + opp_pk;
365
366         try {
367             stmt = con.createStatement();
368             if (Prefs.DEBUG) LogWrite.write(query);
369             rs = stmt.executeQuery(query);
370
371             int i;
372             while (rs.next()) {
373                 i=1;
374                 activity = new Activity();
375                 activity.setPK(rs.getLong(i)); i++;
376                 activity.setOppPK(opp_pk);
377                 activity.setSubject(rs.getString(i)); i++;
378                 activity.setType(rs.getString(i)); i++;
379                 activity.setPlace(rs.getString(i)); i++;
380                 activity.setMessage(rs.getString(i)); i++;
381                 activity.setStartDate(rs.getDate(i)); i++;
382                 activity.setDuration(new Integer JavaDoc(rs.getInt(i))); i++;
383                 boolean x;
384                 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false;
385                 activity.setGroupAlarm(x); i++;
386                 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false;
387                 activity.setGroupAlarmAck(x); i++;
388                 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false;
389                 activity.setAlarm(new Boolean JavaDoc(x)); i++;
390                 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false;
391                 activity.setAlarmAck(x); i++;
392                 activity.setModifiedBy(rs.getString(i)); i++;
393                 activity.setModifiedDate(rs.getDate(i));
394                 list.add(activity);
395             }
396         } catch (SQLException e) {
397             throw e;
398         } finally {
399             try {
400                 if (rs != null) rs.close();
401             } catch (SQLException x) { throw x; }
402             try {
403                 if (stmt != null) stmt.close();
404             } catch (SQLException x) { throw x; }
405         }
406     }
407
408     /**
409      * select all activity rows for a given user that
410      * have active alarms earlier than tomorrow
411      *
412      * @param user_pk the opportunity primary key we are searching with
413      * @param list the list of activities owned by the user
414      * @exception java.sql.SQLException
415      */

416     public final ArrayList JavaDoc selectActiveAlarms(long user_pk)
417         throws SQLException {
418
419         ArrayList JavaDoc acts = new ArrayList JavaDoc();
420         Activity activity = null;
421         Statement stmt = null;
422         ResultSet rs = null;
423         StringBuffer JavaDoc query = new StringBuffer JavaDoc(selectAlarmsQuery);
424
425         if (DB_TYPE == Prefs.MYSQL)
426             query.append( "activity.start_date < DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY) AND ");
427         else
428             query.append( "activity.start_date < SYSDATE + 1 AND ");
429         
430         query.append("user_group_member.user_pk = ");
431         query.append(user_pk);
432
433         try {
434             stmt = con.createStatement();
435             if (Prefs.DEBUG) LogWrite.write(query.toString());
436             rs = stmt.executeQuery(query.toString());
437
438             int i;
439             while (rs.next()) {
440                 i=1;
441                 activity = new Activity();
442                 activity.setPK(rs.getLong(i)); i++;
443                 activity.setOppPK(rs.getLong(i)); i++;
444                 activity.setSubject(rs.getString(i)); i++;
445                 activity.setType(rs.getString(i)); i++;
446                 activity.setPlace(rs.getString(i)); i++;
447                 activity.setMessage(rs.getString(i)); i++;
448                 activity.setStartDate(rs.getDate(i)); i++;
449                 activity.setDuration(new Integer JavaDoc(rs.getInt(i))); i++;
450                 boolean x;
451                 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false;
452                 activity.setGroupAlarm(x); i++;
453                 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false;
454                 activity.setGroupAlarmAck(x); i++;
455                 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false;
456                 activity.setAlarm(new Boolean JavaDoc(x)); i++;
457                 if (rs.getString(i).charAt(0)=='Y') x=true; else x=false;
458                 activity.setAlarmAck(x); i++;
459                 activity.setModifiedBy(rs.getString(i)); i++;
460                 activity.setModifiedDate(rs.getDate(i));
461                 acts.add(activity);
462             }
463         } catch (SQLException e) {
464             LogWrite.write(e);
465             throw e;
466         } catch (Exception JavaDoc f) {
467             LogWrite.write(f);
468         } finally {
469             try {
470                 if (rs != null) rs.close();
471             } catch (SQLException x) { throw x; }
472             try {
473                 if (stmt != null) stmt.close();
474             } catch (SQLException x) { throw x; }
475         }
476
477         LogWrite.write("ActivityDB.selectActiveAlarms acts="+ acts.size());
478         return acts;
479     }
480
481     /**
482      * truncate the whole table
483      *
484      * @exception java.sql.SQLException
485      */

486     public final void truncate()
487         throws SQLException {
488
489         String JavaDoc query = "truncate table activity";
490     
491         Statement stmt = null;
492         try {
493             stmt = con.createStatement();
494             if (Prefs.DEBUG) LogWrite.write(query);
495             stmt.executeUpdate(query);
496         } catch (SQLException e) {
497             throw e;
498         } finally {
499             try { if (stmt != null) stmt.close();
500             } catch (SQLException x) { }
501         }
502     }
503 }
504
Popular Tags