ForecastDB


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 Forecast class objects which are stored
17   * in the forecast database table.
18   */
19  public class ForecastDB extends DBType implements DBInterface {
20      private Connection con;
21  
22      private final static String   deleteQuery =
23          "DELETE FROM forecast WHERE pk = ";
24  
25      private final static String   deleteOppRowsQuery =
26          "DELETE FROM forecast WHERE opp_pk = ";
27  
28      private final static String   insertQuery =
29          "INSERT INTO forecast " +
30          "VALUES (";
31  
32      private final static String   updateQuery =
33          "UPDATE forecast SET " ;
34  
35      private final static String   selectQuery =
36              "SELECT "+
37              "opp_pk," +
38              "name," +
39              "close_date," +
40              "submitted," + "submitted_date," + "submitted_by," +
41              "revenue," +
42              "profit," +
43              "margin," +
44              "scenario," +
45              "amount_jan," + "amount_feb," + "amount_mar," + "q1," + 
46              "amount_apr," + "amount_may," + "amount_jun," + "q2," + 
47              "amount_jul," + "amount_aug," + "amount_sep," + "q3," + 
48              "amount_oct," + "amount_nov," + "amount_dec," + "q4," + 
49              "modified_by, modified_date " +
50              "FROM forecast "+
51              "WHERE pk = ";
52  
53      private final static String   selectForOppQuery =
54              "SELECT "+
55              "pk," +
56              "name," +
57              "close_date," +
58              "submitted," + "submitted_date," + "submitted_by," +
59              "revenue," +
60              "profit," +
61              "margin," +
62              "scenario," +
63              "amount_jan," + "amount_feb," + "amount_mar," + "q1," + 
64              "amount_apr," + "amount_may," + "amount_jun," + "q2," + 
65              "amount_jul," + "amount_aug," + "amount_sep," + "q3," + 
66              "amount_oct," + "amount_nov," + "amount_dec," + "q4," + 
67              "modified_by, modified_date " +
68              "FROM forecast "+
69              "WHERE opp_pk = ";
70  
71      /**
72       * a do-nothing constructor but necessary to
73       * do the operations offered by this class
74       */
75      public ForecastDB() {
76      }
77  
78      /**
79       * construct using a particular db type
80       * @param dbType the db type
81       */
82      public ForecastDB(int dbType) {
83          DB_TYPE = dbType;
84      }
85  
86      /**
87       * a version of the constructor when a connection
88       * is already obtained from somewhere else
89       *
90       * @param con the Connection to use 
91       */
92      public ForecastDB(Connection con) {
93          this.con = con;
94      }
95  
96      /**
97       * return 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 final void setConnection(Connection con) 
111         throws SQLException {
112 
113         this.con = con;
114     }
115 
116     /**
117      * select a single forecast row using the passed
118      * primary key
119      * 
120      * @param pk the primary key we want to search with
121      * @return the Forecast row(s) that were selected
122      * @exception java.sql.SQLException 
123      */
124     public final Object   selectRow(Object   pk) 
125         throws SQLException {
126 
127         Forecast sp = new Forecast();
128         sp.setPK(((Long  )pk).longValue());
129 
130         Statement stmt = null;
131         ResultSet rs = null;
132         String   query = selectQuery + sp.getPK();
133 
134         try {
135             stmt = con.createStatement();
136             if (Prefs.DEBUG) LogWrite.write(query);
137             rs = stmt.executeQuery(query);
138 
139             int i;
140 
141             while (rs.next()) {
142                 i=1;
143                 sp.setOppKey(rs.getLong(i)); i++;
144                 sp.setName(rs.getString(i)); i++;
145                 sp.setCloseDate(rs.getDate(i)); i++;
146                 String   flag=rs.getString(i); i++;
147                 Boolean   val;
148                 if (flag.equals("Y")) val=new Boolean  (true); else val=new Boolean  (false);
149                 sp.setSubmitted(val);
150                 sp.setSubmittedDate(rs.getDate(i)); i++;
151                 sp.setSubmittedBy(rs.getString(i)); i++;
152                 sp.setRevenue(new Integer  (rs.getInt(i))); i++;
153                 sp.setProfit(new Integer  (rs.getInt(i))); i++;
154                 sp.setMargin(new Double  (rs.getDouble(i))); i++;
155                 sp.setScenario(rs.getString(i)); i++;
156                 sp.setAmountJan(new Integer  (rs.getInt(i))); i++;
157                 sp.setAmountFeb(new Integer  (rs.getInt(i))); i++;
158                 sp.setAmountMar(new Integer  (rs.getInt(i))); i++;
159                 sp.setQ1(new Integer  (rs.getInt(i))); i++;
160                 sp.setAmountApr(new Integer  (rs.getInt(i))); i++;
161                 sp.setAmountMay(new Integer  (rs.getInt(i))); i++;
162                 sp.setAmountJun(new Integer  (rs.getInt(i))); i++;
163                 sp.setQ2(new Integer  (rs.getInt(i))); i++;
164                 sp.setAmountJul(new Integer  (rs.getInt(i))); i++;
165                 sp.setAmountAug(new Integer  (rs.getInt(i))); i++;
166                 sp.setAmountSep(new Integer  (rs.getInt(i))); i++;
167                 sp.setQ3(new Integer  (rs.getInt(i))); i++;
168                 sp.setAmountOct(new Integer  (rs.getInt(i))); i++;
169                 sp.setAmountNov(new Integer  (rs.getInt(i))); i++;
170                 sp.setAmountDec(new Integer  (rs.getInt(i))); i++;
171                 sp.setQ4(new Integer  (rs.getInt(i))); i++;
172                 sp.setModifiedBy(rs.getString(i)); i++;
173                 sp.setModifiedDate(rs.getDate(i)); 
174             }
175 
176         } catch (SQLException e) {
177             throw e;
178         } finally {
179             try {
180                 if (rs != null) rs.close();
181             } catch (SQLException x) { throw x; }
182             try {
183                 if (stmt != null) stmt.close();
184             } catch (SQLException x) { throw x; }
185         }
186 
187         return sp;
188     }
189 
190 
191     /**
192      * update a Forecast stored in the database using the
193      * passed parm 
194      * @param obj the Forecast we are updating
195      * @exception java.sql.SQLException 
196      */
197     public final void updateRow(Object   obj) 
198         throws SQLException {
199 
200         Forecast fore = (Forecast)obj;
201 
202         StringBuffer   query = new StringBuffer  (updateQuery);
203         Statement stmt = con.createStatement();
204 
205         query.append("name=");
206         query.append(JDBC.quoteMore(fore.getName()));
207         query.append("close_date=");
208         query.append(JDBC.quoteMore(DateUtils.format(DB_TYPE, fore.getCloseDate())));
209         query.append("submitted=");
210         String   x; if (fore.getSubmitted().booleanValue()) x="Y"; else x="N";
211         query.append(JDBC.quoteMore(x));
212         query.append("submitted_date=");
213         query.append(JDBC.quoteMore(DateUtils.format(DB_TYPE, fore.getSubmittedDate())));
214         query.append("submitted_by=");
215         query.append(JDBC.quoteMore(fore.getSubmittedBy()));
216         query.append("revenue=");
217         query.append(fore.getRevenue().intValue()).append(",");
218         query.append("profit=");
219         query.append(fore.getProfit().intValue()).append(",");
220         query.append("margin=");
221         query.append(fore.getMargin().doubleValue()).append(",");
222         query.append("scenario=");
223         query.append(JDBC.quoteMore(fore.getScenario()));
224         query.append("amount_jan=");
225         query.append(fore.getAmountJan().intValue()).append(",");
226         query.append("amount_feb=");
227         query.append(fore.getAmountFeb().intValue()).append(",");
228         query.append("amount_mar=");
229         query.append(fore.getAmountMar().intValue()).append(",");
230         query.append("q1=");
231         query.append(fore.getQ1().intValue()).append(","); 
232         query.append("amount_apr=");
233         query.append(fore.getAmountApr().intValue()).append(",");
234         query.append("amount_may=");
235         query.append(fore.getAmountMay().intValue()).append(",");
236         query.append("amount_jun=");
237         query.append(fore.getAmountJun().intValue()).append(",");
238         query.append("q2=");
239         query.append(fore.getQ2().intValue()).append(",");
240         query.append("amount_jul=");
241         query.append(fore.getAmountJul().intValue()).append(",");
242         query.append("amount_aug=");
243         query.append(fore.getAmountAug().intValue()).append(",");
244         query.append("amount_sep=");
245         query.append(fore.getAmountSep().intValue()).append(",");
246         query.append("q3=");
247         query.append(fore.getQ3().intValue()).append(",");
248         query.append("amount_oct=");
249         query.append(fore.getAmountOct().intValue()).append(",");
250         query.append("amount_nov=");
251         query.append(fore.getAmountNov().intValue()).append(",");
252         query.append("amount_dec=");
253         query.append(fore.getAmountDec().intValue()).append(",");
254         query.append("q4=");
255         query.append(fore.getQ4().intValue()).append(",");
256         query.append("modified_by=");
257         query.append(JDBC.quoteMore(fore.getModifiedBy()));
258         query.append("modified_date=");
259         query.append(JDBC.quote(DateUtils.format(DB_TYPE, fore.getModifiedDate())));
260         query.append(" WHERE pk=");
261         query.append(fore.getPK()); 
262 
263         if (Prefs.DEBUG) LogWrite.write(query.toString());
264         int updatedRows = stmt.executeUpdate(query.toString());
265     }
266 
267     /**
268      * insert a new forecast row using the passed
269      * Forecast object as the column values.
270      * 
271      * @param obj the object we are trying to insert
272      * @param load true if the row is to be loaded, false if the
273      * row is to be added
274      * @return the newly assigned primary key of the new row
275      * @exception java.sql.SQLException 
276      */
277     public final long insertRow(Object   obj, boolean load) 
278         throws SQLException {
279 
280         Forecast forecast = (Forecast)obj;
281 
282         if (!load)
283             forecast.setPK(DBUtils.generatePK());
284 
285         StringBuffer   query = new StringBuffer  (insertQuery);
286         Statement stmt = con.createStatement();
287 
288         query.append(forecast.getPK()).append(",");
289         query.append(forecast.getOppKey()).append(",");
290         query.append(JDBC.quoteMore(forecast.getName()));
291         query.append(JDBC.quoteMore(DateUtils.format(DB_TYPE, forecast.getCloseDate())));
292         String   x; if (forecast.getSubmitted().booleanValue()) x = "Y"; else x="N";
293         query.append(JDBC.quoteMore(x));
294         query.append(JDBC.quoteMore(DateUtils.format(DB_TYPE, forecast.getSubmittedDate())));
295         query.append(JDBC.quoteMore(forecast.getSubmittedBy()));
296         query.append(forecast.getRevenue().intValue()).append(",");
297         query.append(forecast.getProfit().intValue()).append(",");
298         query.append(forecast.getMargin().doubleValue()).append(",");
299         query.append(JDBC.quoteMore(forecast.getScenario()));
300         query.append(forecast.getAmountJan().intValue()).append(",");
301         query.append(forecast.getAmountFeb().intValue()).append(",");
302         query.append(forecast.getAmountMar().intValue()).append(",");
303         query.append(forecast.getQ1().intValue()).append(",");
304         query.append(forecast.getAmountApr().intValue()).append(",");
305         query.append(forecast.getAmountMay().intValue()).append(",");
306         query.append(forecast.getAmountJun().intValue()).append(",");
307         query.append(forecast.getQ2().intValue()).append(",");
308         query.append(forecast.getAmountJul().intValue()).append(",");
309         query.append(forecast.getAmountAug().intValue()).append(",");
310         query.append(forecast.getAmountSep().intValue()).append(",");
311         query.append(forecast.getQ3().intValue()).append(",");
312         query.append(forecast.getAmountOct().intValue()).append(",");
313         query.append(forecast.getAmountNov().intValue()).append(",");
314         query.append(forecast.getAmountDec().intValue()).append(",");
315         query.append(forecast.getQ4().intValue()).append(",");
316         query.append(JDBC.quoteMore(forecast.getModifiedBy()));
317         query.append(JDBC.quote(DateUtils.format(DB_TYPE, forecast.getModifiedDate())));
318         query.append(")");
319 
320         if (Prefs.DEBUG) LogWrite.write(query.toString());
321         int rc = stmt.executeUpdate(query.toString());
322 
323         return forecast.getPK();
324     }
325 
326     /**
327      * delete a single forecast row using the passed
328      * primary key value
329      *
330      * @param ojb primary key stored in a Long
331      * @exception java.sql.SQLException 
332      */
333     public final void deleteRow(Object   obj) 
334         throws SQLException {
335 
336         long pkValue = ((Long  )obj).longValue();
337 
338         String   query = deleteQuery + pkValue;
339 
340         Statement stmt = null;
341 
342         try {
343             stmt = con.createStatement();
344             if (Prefs.DEBUG) LogWrite.write(query);
345             stmt.executeUpdate(query);
346         } catch (SQLException e) {
347             LogWrite.write(e);
348             throw e;
349         } finally {
350             try {
351                 if (stmt != null) stmt.close();
352             } catch (SQLException x) { }
353         }
354     }
355 
356 
357     /**
358      * delete all forecast rows using the passed
359      * opportunity primary key value
360      *
361      * @param ojb primary key stored in a Long
362      * @exception java.sql.SQLException 
363      */
364     public final void deleteOpportunityRows(Object   obj) 
365         throws SQLException {
366 
367         long oppPK = ((Long  )obj).longValue();
368 
369         String   query = deleteOppRowsQuery + oppPK;
370 
371         Statement stmt = null;
372 
373         try {
374             stmt = con.createStatement();
375             if (Prefs.DEBUG) LogWrite.write(query);
376             stmt.executeUpdate(query);
377         } catch (SQLException e) {
378             LogWrite.write(e);
379             throw e;
380         } finally {
381             try {
382                 if (stmt != null) stmt.close();
383             } catch (SQLException x) { }
384         }
385     }
386 
387     /**
388      * select all forecast rows using the passed
389      * opportunity primary key
390      * 
391      * @param opp_pk the Opportunity primary key to search with
392      * @param forecasts the list of Forecasts the search will return
393      * its result set into
394      * @exception java.sql.SQLException 
395      */
396     public final void selectByOppRow(long opp_pk, ArrayList   forecasts) 
397         throws SQLException {
398 
399         Forecast f = null;
400 
401         Statement stmt = null;
402         ResultSet rs = null;
403         String   query = selectForOppQuery + opp_pk;
404 
405         try {
406             stmt = con.createStatement();
407             if (Prefs.DEBUG) LogWrite.write(query);
408             rs = stmt.executeQuery(query);
409 
410             int i;
411 
412             while (rs.next()) {
413                 i=1;
414                 f = new Forecast();
415                 f.setPK(rs.getLong(i)); i++;
416                 f.setName(rs.getString(i)); i++;
417                 f.setCloseDate(rs.getDate(i)); i++;
418                 String   flag=rs.getString(i); i++;
419                 Boolean   val;
420                 if (flag.equals("Y")) val=new Boolean  (true); else val=new Boolean  (false);
421                 f.setSubmitted(val);
422                 f.setSubmittedDate(rs.getDate(i)); i++;
423                 f.setSubmittedBy(rs.getString(i)); i++;
424                 f.setRevenue(new Integer  (rs.getInt(i))); i++;
425                 f.setProfit(new Integer  (rs.getInt(i))); i++;
426                 f.setMargin(new Double  (rs.getDouble(i))); i++;
427                 f.setScenario(rs.getString(i)); i++;
428                 f.setAmountJan(new Integer  (rs.getInt(i))); i++;
429                 f.setAmountFeb(new Integer  (rs.getInt(i))); i++;
430                 f.setAmountMar(new Integer  (rs.getInt(i))); i++;
431                 f.setQ1(new Integer  (rs.getInt(i))); i++;
432                 f.setAmountApr(new Integer  (rs.getInt(i))); i++;
433                 f.setAmountMay(new Integer  (rs.getInt(i))); i++;
434                 f.setAmountJun(new Integer  (rs.getInt(i))); i++;
435                 f.setQ2(new Integer  (rs.getInt(i))); i++;
436                 f.setAmountJul(new Integer  (rs.getInt(i))); i++;
437                 f.setAmountAug(new Integer  (rs.getInt(i))); i++;
438                 f.setAmountSep(new Integer  (rs.getInt(i))); i++;
439                 f.setQ3(new Integer  (rs.getInt(i))); i++;
440                 f.setAmountOct(new Integer  (rs.getInt(i))); i++;
441                 f.setAmountNov(new Integer  (rs.getInt(i))); i++;
442                 f.setAmountDec(new Integer  (rs.getInt(i))); i++;
443                 f.setQ4(new Integer  (rs.getInt(i))); i++;
444                 f.setModifiedBy(rs.getString(i)); i++;
445                 f.setModifiedDate(rs.getDate(i)); 
446                 forecasts.add(f);
447             }
448         } catch (SQLException e) {
449             throw e;
450         } finally {
451             try {
452                 if (stmt != null) stmt.close();
453             } catch (SQLException x) { }
454         }
455     }
456     /**
457      * truncate the whole table
458      *
459      * @exception java.sql.SQLException
460      */
461     public final void truncate()
462         throws SQLException {
463 
464         String   query = "truncate table forecast";
465    
466         Statement stmt = null;
467         try {
468             stmt = con.createStatement();
469             if (Prefs.DEBUG) LogWrite.write(query);
470             stmt.executeUpdate(query);
471         } catch (SQLException e) {
472             throw e;
473         } finally {
474             try { if (stmt != null) stmt.close();
475             } catch (SQLException x) { }
476         }
477     }
478 
479 }
480
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags