OpportunityDB


1   package sellwin.db;
2   
3   import sellwin.domain.*;
4   import sellwin.utils.*;
5   
6   import java.sql.*;
7   import java.util.ArrayList  ;
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 Opportunity class objects which are stored
17   * in the opportunity database table.
18   */
19  public class OpportunityDB extends DBType implements DBInterface {
20      private Connection con;
21  
22      private final static String   selectOppNamesQuery = 
23              "SELECT " +
24              "opportunity.name " +
25              "FROM opportunity, user_group, user_group_member " +
26              "WHERE " +
27              "opportunity.group_name = user_group.name AND " +
28              "user_group.pk = user_group_member.user_group_pk AND " +
29              "user_group_member.user_pk = ";
30  
31      private final static String   selectOppIndexQuery = 
32              "SELECT " +
33              "opportunity.name, opportunity.stage, opportunity.pk " +
34              "FROM opportunity, user_group, user_group_member " +
35              "WHERE " +
36              "opportunity.group_name = user_group.name AND " +
37              "user_group.pk = user_group_member.user_group_pk AND " +
38              "user_group_member.user_pk = ";
39  
40      private final static String   selectQuery = 
41              "SELECT " +
42              "name, dollar_value, probability, " +
43              "stage, lead_source, lead_type , " +
44              "description, group_name, prime_sp_pk , " +
45              "cust_pk, close_date, lead_pk, " +
46              "modified_by, modified_date " +
47              "FROM opportunity " +
48              "WHERE pk=";
49  
50      private final static String   selectByUserQuery = 
51              "SELECT " +
52              "opportunity.pk, opportunity.name, dollar_value, probability, " +
53              "stage, lead_source, lead_type , " +
54              "description, group_name, prime_sp_pk , " +
55              "cust_pk, close_date, lead_pk, " +
56              "opportunity.modified_by, opportunity.modified_date " +
57              "FROM opportunity, user_group, user_group_member " +
58              "WHERE " +
59              "opportunity.group_name = user_group.name AND " +
60              "user_group.pk = user_group_member.user_group_pk AND " +
61              "user_group_member.user_pk = ";
62  
63      private final static String   updateQuery = 
64              "UPDATE opportunity " +
65              "SET  " ;
66      private final static String   insertQuery = 
67              "INSERT INTO opportunity VALUES ( ";
68      private final static String   deleteQuery = 
69              "DELETE FROM opportunity WHERE pk =";
70  
71      /**
72       * a do-nothing constructor but necessary to
73       * do the operations offered by this class
74       */
75      public OpportunityDB() {
76      }
77  
78      /** 
79       * construct using a db type
80       * @param dbType the db type to assume
81       */
82      public OpportunityDB(int dbType) {
83          DB_TYPE = dbType;
84      }
85  
86      /**
87       * a constructor that accepts an existing Connection
88       * to use for future operations
89       *
90       * @param con the Connection to use
91       */
92      public OpportunityDB(Connection con) {
93          this.con = con;
94      }
95  
96      /**
97       * get the Connection in use
98       *
99       * @return the Connection in use
100      */
101     public Connection getConnection() {
102         return this.con;
103     }
104 
105     /**
106      * set the Connection to use 
107      *
108      * @param con the Connection to use for any future IO's
109      */
110     public void setConnection(Connection con) 
111         throws SQLException {
112 
113         this.con = con;
114     }
115 
116     /**
117      * select a single opportunity row using the passed
118      * primary key
119      * 
120      * @param name description
121      * @return the row(s) that were selected
122      * @exception java.sql.SQLException 
123      */
124     public final Object   selectRow(Object   pk) 
125         throws SQLException {
126 
127         Opportunity opportunity = new Opportunity();
128         long pkValue = ((Long  )pk).longValue();
129         opportunity.setPK(pkValue);
130 
131         Statement stmt = null;
132         ResultSet rs = null;
133         String   query = selectQuery + pkValue;
134 
135         try {
136             stmt = con.createStatement();
137             if (Prefs.DEBUG) LogWrite.write(query);
138             rs = stmt.executeQuery(query);
139 
140             int i;
141             while (rs.next()) {
142                 i=1;
143                 opportunity.setName(rs.getString(i)); i++;
144                 opportunity.setDollarValue(rs.getInt(i)); i++;
145                 opportunity.setProbability(rs.getString(i)); i++;
146                 opportunity.setStage(rs.getString(i)); i++;
147                 opportunity.setLeadSource(rs.getString(i)); i++;
148                 opportunity.setLeadType(rs.getString(i)); i++;
149                 opportunity.setDesc(rs.getString(i)); i++;
150                 opportunity.setGroupName(rs.getString(i)); i++;
151                 opportunity.setPrimeSalesPersonPK(rs.getLong(i)); i++;
152                 opportunity.setCustomerPK(rs.getLong(i)); i++;
153                 opportunity.setCloseDate(rs.getDate(i)); i++;
154                 opportunity.setLeadPK(rs.getLong(i)); i++;
155                 opportunity.setModifiedBy(rs.getString(i)); i++;
156                 opportunity.setModifiedDate(rs.getDate(i)); 
157 
158                 //get the customer for this opp
159                 CustomerDB customerDB = new CustomerDB(DB_TYPE);
160                 customerDB.setConnection(getConnection());
161                 Customer customer=null;
162                 customer = (Customer)customerDB.selectRow(new Long  (opportunity.getCustomerPK()));
163                 opportunity.setCustomer(customer);
164 
165                 //get all the activities for this opp and add them
166                 ActivityDB activityDB = new ActivityDB(DB_TYPE);
167                 activityDB.setConnection(getConnection());
168                 activityDB.selectByOppRows(pkValue, opportunity.getActivities());
169                 //get all the quotes for this opp and add them
170                 QuoteDB quoteDB = new QuoteDB(DB_TYPE);
171                 quoteDB.setConnection(getConnection());
172                 quoteDB.selectByOppRow(pkValue, opportunity.getQuotes());
173 
174                 //get all the forecasts for this opp and add them
175                 ForecastDB forecastDB = new ForecastDB(DB_TYPE);
176                 forecastDB.setConnection(getConnection());
177                 forecastDB.selectByOppRow(pkValue, opportunity.getForecasts());
178 
179                 //get all the orders for this opp and add them 
180                 OrderDB orderDB = new OrderDB(DB_TYPE);
181                 orderDB.setConnection(getConnection());
182                 orderDB.selectByOppRow(pkValue, opportunity.getOrders());
183 
184                 //get all the contacts for this opp and add them
185                 ContactDB contactDB = new ContactDB(DB_TYPE);
186                 contactDB.setConnection(getConnection());
187                 contactDB.selectByOppRow(pkValue, opportunity.getContacts());
188             }
189         } catch (SQLException e) {
190             LogWrite.write(e);
191             throw e;
192         } finally {
193             try {
194                 if (rs != null) rs.close();
195             } catch (SQLException x) { throw x; }
196             try {
197                 if (stmt != null) stmt.close();
198             } catch (SQLException x) { throw x; }
199         }
200 
201         return opportunity;
202     }
203 
204 
205     /**
206      * a shallow update of opportunity
207      *
208      * @param name description
209      * @exception java.sql.SQLException 
210      */
211     public void updateRow(Object   obj) 
212         throws SQLException {
213 
214         Opportunity opportunity = (Opportunity)obj;
215         long pk = opportunity.getPK();
216 
217         Statement stmt = null;
218         StringBuffer   query = new StringBuffer  (updateQuery);
219 
220         try {
221             stmt = con.createStatement();
222 
223             query.append("name=");
224             query.append(JDBC.quoteMore(opportunity.getName()));
225             query.append("dollar_value=");
226             query.append(opportunity.getDollarValue()).append(",");
227             query.append("probability=");
228             query.append(JDBC.quoteMore(opportunity.getProbability()));
229             query.append("stage=");
230             query.append(JDBC.quoteMore(opportunity.getStage()));
231             query.append("lead_source=");
232             query.append(JDBC.quoteMore(opportunity.getLeadSource()));
233             query.append("lead_type=");
234             query.append(JDBC.quoteMore(opportunity.getLeadType()));
235             query.append("description=");
236             query.append(JDBC.quoteMore(opportunity.getDesc()));
237             query.append("group_name=");
238             query.append(JDBC.quoteMore(opportunity.getGroupName()));
239             query.append("prime_sp_pk=");
240             if (opportunity.getPrimeSalesPersonPK() == 0) {
241                 query.append("null,");
242             } else {
243                 query.append(opportunity.getPrimeSalesPersonPK()).append(",");
244             }
245             query.append("cust_pk=");
246             if (opportunity.getCustomerPK() == 0) {
247                 query.append("null,");
248             } else {
249                 query.append(opportunity.getCustomerPK()).append(",");
250             }
251 
252             query.append("close_date=");
253             query.append(JDBC.quoteMore(DateUtils.format(DB_TYPE, opportunity.getCloseDate())));
254 
255             query.append("lead_pk=");
256             if (opportunity.getLeadPK() == 0) {
257                 query.append("null,");
258             } else {
259                 query.append(opportunity.getLeadPK()).append(",");
260             }
261 
262             query.append("modified_by=");
263             query.append(JDBC.quoteMore(opportunity.getModifiedBy()));
264             query.append("modified_date=");
265             if (DB_TYPE == Prefs.MYSQL)
266                 query.append("CURRENT_DATE");
267             else
268                 query.append("SYSDATE");
269             query.append(" WHERE pk=");
270             query.append(opportunity.getPK());  
271 
272             if (Prefs.DEBUG) LogWrite.write(query.toString());
273             int updatedRows = stmt.executeUpdate(query.toString());
274         } catch (SQLException e) {
275             throw e;
276         } finally {
277             try { if (stmt != null) stmt.close(); 
278             } catch (SQLException x) { }
279         }
280     }
281 
282     /**
283      * insert a new opportunity row using the passed
284      * Opportunity object as the column values.
285      * 
286      * @param obj the Opportunity to load or add
287      * @param load true if the Opportunity is to be loaded or false
288      * if its to be added
289      * @return the newly assigned primary key of the new row
290      * @exception java.sql.SQLException 
291      */
292     public long insertRow(Object   obj, boolean load) 
293         throws SQLException {
294 
295         Opportunity opportunity = (Opportunity)obj;
296 
297         if (!load)
298             opportunity.setPK(DBUtils.generatePK());
299 
300         Statement stmt = null;
301         StringBuffer   query = new StringBuffer  (insertQuery);
302 
303         try {
304             stmt = con.createStatement();
305 
306             query.append(opportunity.getPK()).append(",");
307             query.append(JDBC.quoteMore(opportunity.getName()));
308             query.append(opportunity.getDollarValue()).append(",");
309             query.append(JDBC.quoteMore(opportunity.getProbability()));
310             query.append(JDBC.quoteMore(opportunity.getStage()));
311             query.append(JDBC.quoteMore(opportunity.getLeadSource()));
312             query.append(JDBC.quoteMore(opportunity.getLeadType()));
313             query.append(JDBC.quoteMore(opportunity.getDesc()));
314             query.append(JDBC.quoteMore(opportunity.getGroupName()));
315             query.append(opportunity.getPrimeSalesPerson().getPK()).append(",");
316             query.append(opportunity.getCustomer().getPK()).append(",");
317             query.append(JDBC.quoteMore(DateUtils.format(DB_TYPE, opportunity.getCloseDate())));
318             if (opportunity.getLead() == null) {
319                 query.append("null,");
320             } else {
321                 query.append(opportunity.getLead().getPK()).append(",");
322             }
323             query.append(JDBC.quoteMore(opportunity.getModifiedBy()));
324             if (DB_TYPE == Prefs.MYSQL)
325                 query.append("CURRENT_DATE");
326             else
327                 query.append("SYSDATE");
328             query.append(")");
329 
330             if (Prefs.DEBUG) LogWrite.write(query.toString());
331             int rc = stmt.executeUpdate(query.toString());
332         } catch (NullPointerException   x) {
333             LogWrite.write(x);
334         } catch (SQLException e) {
335             throw e;
336         } finally {
337             try { if (stmt != null) stmt.close();
338             } catch (SQLException x) { }
339         }
340 
341         return opportunity.getPK();
342     }
343 
344     /**
345      * delete a single opportunity row using the passed
346      * primary key value
347      *
348      * @param ojb primary key stored in a Long
349      * @exception java.sql.SQLException 
350      */
351     public final void deleteRow(Object   obj) 
352         throws SQLException {
353 
354         long pkValue = ((Long  )obj).longValue();
355         String   query = deleteQuery + pkValue;
356 
357         //delete all the children first 
358 
359         //forecasts
360         ForecastDB f = new ForecastDB(DB_TYPE);
361         f.setConnection(getConnection());
362         f.deleteOpportunityRows((Long  )obj);
363 
364         if (Prefs.DEBUG) LogWrite.write("deleted Forecast rows of Opp");
365 
366         //contacts
367         ContactDB c = new ContactDB(DB_TYPE);
368         c.setConnection(getConnection());
369         c.deleteOpportunityRows((Long  )obj);
370 
371         if (Prefs.DEBUG) LogWrite.write("deleted contact rows of Opp");
372 
373         //quotes
374         QuoteDB q = new QuoteDB(DB_TYPE);
375         q.setConnection(getConnection());
376         q.deleteOpportunityRows((Long  )obj);
377 
378         if (Prefs.DEBUG) LogWrite.write("deleted quote rows of Opp");
379 
380         //orders
381         OrderDB o = new OrderDB(DB_TYPE);
382         o.setConnection(getConnection());
383         o.deleteOpportunityRows((Long  )obj);
384 
385         if (Prefs.DEBUG) LogWrite.write("deleted order rows of Opp");
386 
387         //activities
388         ActivityDB a = new ActivityDB(DB_TYPE);
389         a.setConnection(getConnection());
390         a.deleteOpportunityRows((Long  )obj);
391 
392         if (Prefs.DEBUG) LogWrite.write("deleted activity rows of Opp");
393 
394         Statement stmt = null;
395         try {
396             stmt = con.createStatement();
397             if (Prefs.DEBUG) LogWrite.write(query);
398             stmt.executeUpdate(query);
399         } catch (SQLException e) {
400             LogWrite.write(e);
401             throw e;
402         } finally {
403             try { if (stmt != null) stmt.close();
404             } catch (SQLException x) { }        
405         }
406     }
407 
408     /**
409      * select all opportunity "index" rows using the passed
410      * key
411      * 
412      * @param name description
413      * @return the row(s) that were selected
414      * @exception java.sql.SQLException 
415      */
416     public final ArrayList   selectOppIndexRows(SalesPerson sp) 
417         throws SQLException {
418 
419         ArrayList   rows = new ArrayList  ();
420         Statement stmt = null;
421         ResultSet rs = null;
422         String   query = selectOppIndexQuery + sp.getPK() +
423             " ORDER BY opportunity.name";
424 
425         try {
426             stmt = con.createStatement();
427             if (Prefs.DEBUG) LogWrite.write(query);
428             rs = stmt.executeQuery(query);
429 
430             int i;
431             OppIndex oi = null;
432 
433             while (rs.next()) {
434                 i=1;
435                 oi = new OppIndex();
436                 oi.setName(rs.getString(i)); i++;
437                 oi.setStage(rs.getString(i)); i++;
438                 oi.setPK(rs.getLong(i)); i++;
439                 rows.add(oi);
440             }
441         } catch (SQLException e) {
442             throw e;
443         } finally {
444             try {
445                 if (rs != null) rs.close();
446             } catch (SQLException x) { throw x; }
447             try {
448                 if (stmt != null) stmt.close();
449             } catch (SQLException x) { throw x; }
450         }
451 
452         return rows;
453     }
454 
455     /**
456      * select all opportunity names using the passed
457      * sales person pk
458      * 
459      * @param name description
460      * @return the row(s) that were selected
461      * @exception java.sql.SQLException 
462      */
463     public final ArrayList   selectNames(long user_pk) 
464         throws SQLException {
465 
466         ArrayList   names = new ArrayList  ();
467         Statement stmt = null;
468         ResultSet rs = null;
469         String   query = selectOppNamesQuery + user_pk + 
470         " ORDER BY opportunity.name";
471 
472         if (Prefs.DEBUG) LogWrite.write(query);
473         try {
474             stmt = con.createStatement();
475             if (Prefs.DEBUG) LogWrite.write(query);
476             rs = stmt.executeQuery(query);
477 
478             int i;
479             while (rs.next()) {
480                 names.add(rs.getString(1));
481                 if (Prefs.DEBUG) LogWrite.write("found an opp for this sp");
482             }
483         } catch (SQLException e) {
484             throw e;
485         } finally {
486             try {
487                 if (rs != null) rs.close();
488             } catch (SQLException x) { throw x; }
489             try {
490                 if (stmt != null) stmt.close();
491             } catch (SQLException x) { throw x; }
492         }
493 
494         return names;
495     }
496 
497     /**
498      * select all opportunity rows using the passed
499      * user primary key
500      * 
501      * @param user_pk the user's primary key to limit the query with
502      * @param lastSyncDate the user's last sync date used to limit the
503      * query or null if no limit
504      * @return an ArrayList of row(s) that were selected
505      * @exception java.sql.SQLException 
506      */
507     public final ArrayList   selectByUserRows(long user_pk, java.util.Date   lastSyncDate) 
508         throws SQLException {
509 
510         ArrayList   opps = new ArrayList  ();
511         Opportunity opportunity=null;
512 
513         Statement stmt = null;
514         ResultSet rs = null;
515         StringBuffer   query = new StringBuffer  ();
516         query.append(selectByUserQuery);
517         query.append(user_pk);
518 
519         if (lastSyncDate != null) {
520             query.append(" AND opportunity.modified_date > ");
521             query.append(DateUtils.formatDateTime(DB_TYPE, lastSyncDate));
522         }
523 
524         try {
525             stmt = con.createStatement();
526             if (Prefs.DEBUG) LogWrite.write(query.toString());
527             rs = stmt.executeQuery(query.toString());
528 
529             int i;
530             Customer cust;
531             Lead lead;
532             SalesPerson sp;
533             LeadDB leadDB = new LeadDB(DB_TYPE);
534             leadDB.setConnection(getConnection());
535             CustomerDB custDB = new CustomerDB(DB_TYPE);
536             custDB.setConnection(getConnection());
537             SalesPersonDB spDB = new SalesPersonDB(DB_TYPE);
538             spDB.setConnection(getConnection());
539 
540             while (rs.next()) {
541                 i=1;
542                 opportunity = new Opportunity();
543                 opportunity.setPK(rs.getLong(i)); i++;
544                 opportunity.setName(rs.getString(i)); i++;
545                 opportunity.setDollarValue(rs.getInt(i)); i++;
546                 opportunity.setProbability(rs.getString(i)); i++;
547                 opportunity.setStage(rs.getString(i)); i++;
548                 opportunity.setLeadSource(rs.getString(i)); i++;
549                 opportunity.setLeadType(rs.getString(i)); i++;
550                 opportunity.setDesc(rs.getString(i)); i++;
551                 opportunity.setGroupName(rs.getString(i)); i++;
552                 opportunity.setPrimeSalesPersonPK(rs.getLong(i)); i++;
553                 sp = (SalesPerson)spDB.selectRow(new Long  (opportunity.getPrimeSalesPersonPK()));
554                 opportunity.setPrimeSalesPerson(sp);
555                 opportunity.setCustomerPK(rs.getLong(i)); i++;
556                 cust = (Customer)custDB.selectRow(new Long  (opportunity.getCustomerPK()));
557                 opportunity.setCustomer(cust);
558                 opportunity.setCloseDate(rs.getDate(i)); i++;
559                 opportunity.setLeadPK(rs.getLong(i)); i++;
560                 lead = (Lead)leadDB.selectRow(new Long  (opportunity.getLeadPK()));
561                 opportunity.setLead(lead);
562                 opportunity.setModifiedBy(rs.getString(i)); i++;
563                 opportunity.setModifiedDate(rs.getDate(i)); 
564                 opps.add(opportunity);
565             }
566         } catch (SQLException e) {
567             throw e;
568         } finally {
569             try {
570                 if (rs != null) rs.close();
571             } catch (SQLException x) { throw x; }
572             try {
573                 if (stmt != null) stmt.close();
574             } catch (SQLException x) { throw x; }
575         }
576 
577         return opps;
578     }
579     /**
580      * truncate the whole table
581      *
582      * @exception java.sql.SQLException
583      */
584     public final void truncate()
585         throws SQLException {
586 
587         String   query = "truncate table opportunity";
588    
589         Statement stmt = null;
590         try {
591             stmt = con.createStatement();
592             if (Prefs.DEBUG) LogWrite.write(query);
593             stmt.executeUpdate(query);
594         } catch (SQLException e) {
595             throw e;
596         } finally {
597             try { if (stmt != null) stmt.close();
598             } catch (SQLException x) { }
599         }
600     }
601 
602 }
603
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags