KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > sellwin > db > LeadDB


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 Lead class objects which are stored
17  * in the lead database table.
18  */

19 public class LeadDB extends DBType implements DBInterface {
20     private Connection con;
21
22     private final static String JavaDoc deleteQuery =
23         "DELETE FROM lead WHERE pk = ";
24     private final static String JavaDoc insertQuery =
25         "INSERT INTO lead " +
26         "VALUES (";
27     private final static String JavaDoc updateQuery =
28             "UPDATE lead " +
29             "SET ";
30     private final static String JavaDoc selectQuery =
31             "SELECT campaign_pk, address_pk, company, " +
32             "interest_level, contacted_by_pk, "+
33             "contact_date, converted_opp, "+
34             "modified_by, modified_date " +
35             "FROM lead "+
36             "WHERE pk = ";
37
38     private final static String JavaDoc selectAllQuery =
39             "SELECT pk, campaign_pk, address_pk, company, " +
40             "interest_level, contacted_by_pk, "+
41             "contact_date, converted_opp, "+
42             "modified_by, modified_date " +
43             "FROM lead ";
44
45     private final static String JavaDoc selectByCampaignQuery =
46             "SELECT pk, address_pk, company, " +
47             "interest_level, contacted_by_pk, "+
48             "contact_date, converted_opp, "+
49             "modified_by, modified_date " +
50             "FROM lead "+
51             "WHERE campaign_pk = ";
52
53     /**
54      * a do-nothing constructor but necessary to
55      * do the operations offered by this class
56      */

57     public LeadDB() {
58     }
59
60     /**
61      * construct using a db type
62      * @param dbType the db type to assume
63      */

64     public LeadDB(int dbType) {
65         DB_TYPE = dbType;
66     }
67
68     /**
69      * a version of the constructor when a connection
70      * is already obtained from somewhere else
71      *
72      * @param con the Connection to use
73      */

74     public LeadDB(Connection con) {
75         this.con = con;
76     }
77
78     /**
79      * return the Connection in use
80      *
81      * @return the Connection in use
82      */

83     public Connection getConnection() {
84         return this.con;
85     }
86
87     /**
88      * set the Connection to use
89      *
90      * @param con the Connection to use for any future IO's
91      */

92     public final void setConnection(Connection con)
93         throws SQLException {
94
95         this.con = con;
96     }
97
98     /**
99      * select a single lead row using the passed
100      * primary key
101      *
102      * @param name description
103      * @return the Lead row(s) that were selected
104      * @exception java.sql.SQLException
105      */

106     public final Object JavaDoc selectRow(Object JavaDoc pk)
107         throws SQLException {
108
109         Lead lead = new Lead();
110         lead.setPK(((Long JavaDoc)pk).longValue());
111
112         Statement stmt = null;
113         ResultSet rs = null;
114         String JavaDoc query = selectQuery + lead.getPK();
115
116         try {
117             stmt = con.createStatement();
118             if (Prefs.DEBUG) LogWrite.write(query);
119             rs = stmt.executeQuery(query);
120
121             int i;
122
123             while (rs.next()) {
124                 i=1;
125                 lead.setCampaignKey(rs.getLong(i)); i++;
126
127                 lead.getAddress().setPK(rs.getLong(i)); i++;
128                 AddressDB addr = new AddressDB(DB_TYPE);
129                 addr.setConnection(getConnection());
130                 Address a = (Address)addr.selectRow(new Long JavaDoc(lead.getAddress().getPK()));
131                 lead.setAddress(a);
132
133                 lead.setCompany(rs.getString(i)); i++;
134                 lead.setInterestLevel(rs.getString(i)); i++;
135
136                 lead.getContactedBy().setPK(rs.getLong(i)); i++;
137                 SalesPersonDB spd = new SalesPersonDB(DB_TYPE);
138                 spd.setConnection(getConnection());
139                 SalesPerson sp = (SalesPerson)spd.selectRow(new Long JavaDoc(lead.getContactedBy().getPK()));
140                 lead.setContactedBy(sp);
141
142                 lead.setContactDate(rs.getDate(i)); i++;
143                 String JavaDoc x = rs.getString(i); i++;
144                 Boolean JavaDoc val;
145                 if (x.equals("Y")) val = new Boolean JavaDoc(true); else val = new Boolean JavaDoc(false);
146                 lead.setConverted(val);
147                 lead.setModifiedBy(rs.getString(i)); i++;
148                 lead.setModifiedDate(rs.getDate(i));
149             }
150
151         } catch (SQLException e) {
152             throw e;
153         } finally {
154             try {
155                 if (rs != null) rs.close();
156             } catch (SQLException x) { throw x; }
157             try {
158                 if (stmt != null) stmt.close();
159             } catch (SQLException x) { throw x; }
160         }
161
162         return lead;
163     }
164
165
166     /**
167      * update a single lead row using the passed
168      * Lead object's attributes. All columns
169      * get updated by this routine regardless of whether
170      * an attribute was modified or not.
171      *
172      * @param name description
173      * @exception java.sql.SQLException
174      */

175     public final void updateRow(Object JavaDoc obj)
176         throws SQLException {
177
178         Lead lead = (Lead)obj;
179
180         StringBuffer JavaDoc query = new StringBuffer JavaDoc(updateQuery);
181         Statement stmt = con.createStatement();
182
183         String JavaDoc flag;
184         Boolean JavaDoc x = lead.getConverted();
185         if (x.booleanValue()) flag = "Y"; else flag = "N";
186         query.append("converted_opp=");
187         query.append("'").append(flag).append("',");
188         query.append("modified_by=");
189         query.append("'").append(lead.getModifiedBy()).append("',");
190         query.append("modified_date=");
191         if (DB_TYPE == Prefs.MYSQL)
192             query.append("CURRENT_DATE");
193         else
194             query.append("SYSDATE");
195         query.append(" WHERE pk=");
196         query.append(lead.getPK());
197
198         if (Prefs.DEBUG) LogWrite.write(query.toString());
199         int updatedRows = stmt.executeUpdate(query.toString());
200     }
201
202     /**
203      * insert a new lead row using the passed
204      * Lead object as the column values.
205      *
206      * @param obj the Lead to insert or load
207      * @param load true if the Lead is to be loaded or false if
208      * its to be added
209      * @return the newly assigned primary key of the new row
210      * @exception java.sql.SQLException
211      */

212     public final long insertRow(Object JavaDoc obj, boolean load)
213         throws SQLException {
214
215         Lead lead = (Lead)obj;
216
217         //insert the Address first
218
Address address = lead.getAddress();
219         AddressDB a= new AddressDB(DB_TYPE);
220         a.setConnection(getConnection());
221         lead.getAddress().setPK(a.insertRow(address, load));
222
223
224         //we are assuming that the 'contacted by' sales person
225
//will be already in the system when this lead gets added
226
//so we won't add it here
227

228         if (!load)
229             lead.setPK(DBUtils.generatePK());
230         StringBuffer JavaDoc query = new StringBuffer JavaDoc(insertQuery);
231
232         Statement stmt = con.createStatement();
233
234         query.append(lead.getPK()).append(",");
235         query.append(lead.getCampaignKey()).append(",");
236         query.append(lead.getAddress().getPK()).append(",");
237         query.append(JDBC.quoteMore(lead.getCompany()));
238         query.append(JDBC.quoteMore(lead.getInterestLevel()));
239         query.append(lead.getContactedBy().getPK()).append(",");
240         query.append(JDBC.quoteMore(DateUtils.format(DB_TYPE, lead.getContactDate())));
241     
242         String JavaDoc x;
243         if (lead.getConverted().booleanValue()) x = "Y"; else x="N";
244         query.append(JDBC.quoteMore(x));
245         query.append(JDBC.quoteMore(lead.getModifiedBy()));
246         if (DB_TYPE == Prefs.MYSQL)
247             query.append("CURRENT_DATE");
248         else
249             query.append("SYSDATE");
250         query.append(")");
251
252         if (Prefs.DEBUG) LogWrite.write(query.toString());
253         int rc = stmt.executeUpdate(query.toString());
254
255         return lead.getPK();
256     }
257
258     /**
259      * delete a single lead row using the passed
260      * primary key value
261      *
262      * @param ojb primary key stored in a Long
263      * @exception java.sql.SQLException
264      */

265     public final void deleteRow(Object JavaDoc obj)
266         throws SQLException {
267
268         long pkValue = ((Long JavaDoc)obj).longValue();
269
270         String JavaDoc query = deleteQuery + pkValue;
271
272         Statement stmt = null;
273
274         try {
275             stmt = con.createStatement();
276             if (Prefs.DEBUG) LogWrite.write(query);
277             stmt.executeUpdate(query);
278         } catch (SQLException e) {
279             throw e;
280         } finally {
281             try {
282                 if (stmt != null) stmt.close();
283             } catch (SQLException x) { }
284         }
285     }
286
287     /**
288      * select all lead rows using the passed
289      * campaign primary key
290      *
291      * @param name description
292      * @return the Lead row(s) that were selected
293      * @exception java.sql.SQLException
294      */

295     public final ArrayList JavaDoc selectByCampaignRows(long camp_pk)
296         throws SQLException {
297
298         ArrayList JavaDoc leads = new ArrayList JavaDoc();
299
300         Statement stmt = null;
301         ResultSet rs = null;
302         String JavaDoc query = selectByCampaignQuery + camp_pk;
303
304         try {
305             stmt = con.createStatement();
306             if (Prefs.DEBUG) LogWrite.write(query);
307             rs = stmt.executeQuery(query);
308
309             int i;
310             Lead lead=null;
311
312             while (rs.next()) {
313                 i=1;
314                 lead = new Lead();
315                 lead.setPK(rs.getLong(i)); i++;
316                 lead.setCampaignKey(camp_pk);
317
318                 lead.getAddress().setPK(rs.getLong(i)); i++;
319                 AddressDB addr = new AddressDB(DB_TYPE);
320                 addr.setConnection(getConnection());
321                 Address a = (Address)addr.selectRow(new Long JavaDoc(lead.getAddress().getPK()));
322                 lead.setAddress(a);
323
324                 lead.setCompany(rs.getString(i)); i++;
325                 lead.setInterestLevel(rs.getString(i)); i++;
326
327                 lead.getContactedBy().setPK(rs.getLong(i)); i++;
328                 SalesPersonDB spd = new SalesPersonDB(DB_TYPE);
329                 spd.setConnection(getConnection());
330                 SalesPerson sp = (SalesPerson)spd.selectRow(new Long JavaDoc(lead.getContactedBy().getPK()));
331                 lead.setContactedBy(sp);
332
333                 lead.setContactDate(rs.getDate(i)); i++;
334                 String JavaDoc x = rs.getString(i); i++;
335                 Boolean JavaDoc val;
336                 if (x.equals("Y")) val = new Boolean JavaDoc(true); else val = new Boolean JavaDoc(false);
337                 lead.setConverted(val);
338                 lead.setModifiedBy(rs.getString(i)); i++;
339                 lead.setModifiedDate(rs.getDate(i));
340
341                 leads.add(lead);
342             }
343         } catch (SQLException e) {
344             throw e;
345         } finally {
346             try {
347                 if (rs != null) rs.close();
348             } catch (SQLException x) { throw x; }
349             try {
350                 if (stmt != null) stmt.close();
351             } catch (SQLException x) { throw x; }
352         }
353
354         return leads;
355     }
356     /**
357      * truncate the whole table
358      *
359      * @exception java.sql.SQLException
360      */

361     public final void truncate()
362         throws SQLException {
363
364         String JavaDoc query = "truncate table lead";
365    
366         Statement stmt = null;
367         try {
368             stmt = con.createStatement();
369             if (Prefs.DEBUG) LogWrite.write(query);
370             stmt.executeUpdate(query);
371         } catch (SQLException e) {
372             throw e;
373         } finally {
374             try { if (stmt != null) stmt.close();
375             } catch (SQLException x) { }
376         }
377     }
378
379     /**
380      * select all lead rows
381      *
382      * @param lastSyncDate a user's last sync date used to limit
383      * the query or null if no limit
384      * @return the Lead row(s) that were selected
385      * @exception java.sql.SQLException
386      */

387     public final ArrayList JavaDoc selectAllRows(java.util.Date JavaDoc lastSyncDate)
388         throws SQLException {
389
390         Lead lead;
391
392         Statement stmt = null;
393         ResultSet rs = null;
394         ArrayList JavaDoc rows = new ArrayList JavaDoc();
395
396         StringBuffer JavaDoc query = new StringBuffer JavaDoc();
397         query.append(selectAllQuery);
398         if (lastSyncDate != null) {
399             query.append(" WHERE lead.modified_date > ");
400             query.append(DateUtils.formatDateTime(DB_TYPE, lastSyncDate));
401         }
402
403         try {
404             stmt = con.createStatement();
405             if (Prefs.DEBUG) LogWrite.write(query.toString());
406             rs = stmt.executeQuery(query.toString());
407
408             int i;
409
410             while (rs.next()) {
411                 i=1;
412                 lead = new Lead();
413                 lead.setPK(rs.getLong(i)); i++;
414                 lead.setCampaignKey(rs.getLong(i)); i++;
415                 lead.getAddress().setPK(rs.getLong(i)); i++;
416                 AddressDB addr = new AddressDB(DB_TYPE);
417                 addr.setConnection(getConnection());
418                 Address a = (Address)addr.selectRow(new Long JavaDoc(lead.getAddress().getPK()));
419                 lead.setAddress(a);
420                 lead.setCompany(rs.getString(i)); i++;
421                 lead.setInterestLevel(rs.getString(i)); i++;
422                 lead.getContactedBy().setPK(rs.getLong(i)); i++;
423                 lead.setContactDate(rs.getDate(i)); i++;
424                 String JavaDoc x = rs.getString(i); i++;
425                 Boolean JavaDoc val;
426                 if (x.equals("Y"))
427                     val = new Boolean JavaDoc(true);
428                 else
429                     val = new Boolean JavaDoc(false);
430                 lead.setConverted(val);
431                 lead.setModifiedBy(rs.getString(i)); i++;
432                 lead.setModifiedDate(rs.getDate(i));
433                 rows.add(lead);
434             }
435         } catch (SQLException e) {
436             throw e;
437         } finally {
438             try {
439                 if (rs != null) rs.close();
440             } catch (SQLException x) { throw x; }
441             try {
442                 if (stmt != null) stmt.close();
443             } catch (SQLException x) { throw x; }
444         }
445         return rows;
446     }
447 }
448
Popular Tags