QuoteLineDB


1   package sellwin.db;
2   
3   import sellwin.domain.*;
4   import sellwin.utils.*;
5   
6   import java.util.ArrayList  ;
7   import java.sql.*;
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   * This class implements the DBInterface for
15   * the QuoteLine class objects which are stored
16   * in the quote_line database table.
17   */
18  public class QuoteLineDB extends DBType implements DBInterface {
19  
20      private Connection con;
21      private final static String   deleteQuery =
22          "DELETE FROM quote_line WHERE pk = ";
23          
24      private final static String   deleteByOppQuery =
25          "DELETE FROM quote_line WHERE quote_pk in "+
26          "(select quote_pk from opportunity,quote "+
27              "where opportunity.pk = quote.opp_pk AND opportunity.pk=";
28  
29      private final static String   deleteByQuoteQuery =
30          "DELETE FROM quote_line WHERE quote_pk=";
31  
32      private final static String   insertQuery =
33          "INSERT INTO quote_line (" +
34          " PK, QUOTE_PK, PRODUCT_PK, PRODUCT_NAME, "+
35          " PRODUCT_MODEL_NO, QUANTITY, COST, UNIT_PRICE, "+
36          " TOTAL_PRICE, PRODUCT_DESC, MODIFIED_BY, MODIFIED_DATE " +
37          ") VALUES (";
38  
39      private final static String   updateQuery = 
40              "UPDATE quote_line " +
41              "SET ";
42  
43      private final static String   selectQuery =
44              "SELECT quote_pk, product_pk,"+
45              "quantity, "+
46              "total_price, "+
47              "modified_by, modified_date " +
48              "FROM quote_line "+
49              "WHERE pk = ";
50  
51      private final static String   selectByQuoteQuery =
52              "SELECT pk, product_pk,"+
53              "quantity, "+
54              "total_price, "+
55              "modified_by, modified_date " +
56              "FROM quote_line "+
57              "WHERE quote_pk = ";
58  
59      /**
60       * a do-nothing constructor but necessary to
61       * do the operations offered by this class
62       */
63      public QuoteLineDB() {
64      }
65      
66      /**
67       * construct, use a particular db type
68       * @param dbType the db type to use
69       */
70      public QuoteLineDB(int dbType) {
71          DB_TYPE = dbType;
72      }
73  
74      /**
75       * a version of the constructor when a connection
76       * is already obtained from somewhere else
77       *
78       * @param con the Connection to use 
79       */
80      public QuoteLineDB(Connection con) {
81          this.con = con;
82      }
83  
84      /**
85       * return the Connection in use
86       *
87       * @return the Connection in use
88       */
89      public Connection getConnection() {
90          return this.con;
91      }
92  
93      /**
94       * set the Connection to use 
95       *
96       * @param con the Connection to use for any future IO's
97       */
98      public final void setConnection(Connection con) 
99          throws SQLException {
100 
101         this.con = con;
102     }
103 
104     /**
105      * select a single quote_line row using the passed
106      * primary key
107      * 
108      * @param name description
109      * @return the QuoteLine row(s) that were selected
110      * @exception java.sql.SQLException 
111      */
112     public final Object   selectRow(Object   pk) 
113         throws SQLException {
114 
115         QuoteLine line = new QuoteLine();
116         line.setPK(((Long  )pk).longValue());
117 
118         Statement stmt = null;
119         ResultSet rs = null;
120         String   query = selectQuery + line.getPK();
121 
122         try {
123             stmt = con.createStatement();
124             if (Prefs.DEBUG) LogWrite.write(query);
125             rs = stmt.executeQuery(query);
126 
127             int i;
128 
129             ProductDB prodDB = new ProductDB(DB_TYPE);
130             prodDB.setConnection(getConnection());
131 
132             while (rs.next()) {
133                 i=1;
134                 line.setQuotePK(rs.getLong(i)); i++;
135                 line.getProduct().setPK(rs.getLong(i)); i++;
136                 line.setProduct((Product)prodDB.selectRow(new Long  (line.getProduct().getPK())));
137                 line.setQuantity(new Integer  (rs.getInt(i))); i++;
138                 line.setTotalPrice(new Double  (rs.getDouble(i))); i++;
139                 line.setModifiedBy(rs.getString(i)); i++;
140                 line.setModifiedDate(rs.getDate(i)); 
141             }
142 
143         } catch (SQLException e) {
144             throw e;
145         } finally {
146             try {
147                 if (rs != null) rs.close();
148             } catch (SQLException x) { throw x; }
149             try {
150                 if (stmt != null) stmt.close();
151             } catch (SQLException x) { throw x; }
152         }
153 
154         return line;
155     }
156 
157 
158     /**
159      * update a single quote_line row using the passed
160      * QuoteLine object's attributes.  All columns
161      * get updated by this routine regardless of whether
162      * an attribute was modified or not.
163      *
164      * @param name description
165      * @exception java.sql.SQLException 
166      */
167     public final void updateRow(Object   obj) 
168         throws SQLException {
169 
170         QuoteLine quote_line = (QuoteLine)obj;
171 
172         StringBuffer   query = new StringBuffer  (updateQuery);
173         Statement stmt = con.createStatement();
174 
175         query.append("quantity=");
176         query.append(quote_line.getQuantity().intValue()).append(","); 
177         query.append("modified_by=");
178         query.append(JDBC.quoteMore(quote_line.getModifiedBy()));
179         query.append("modified_date=");
180         if (DB_TYPE == Prefs.MYSQL)
181             query.append("CURRENT_DATE");
182         else
183             query.append("SYSDATE");
184         query.append(" WHERE pk=");
185         query.append(quote_line.getPK()); 
186     
187         if (Prefs.DEBUG) LogWrite.write(query.toString());
188         int updatedRows = stmt.executeUpdate(query.toString());
189     }
190 
191     /**
192      * insert a new quote_line row using the passed
193      * QuoteLine object as the column values.
194      * 
195      * @param obj QuoteLine we are loading or adding
196      * @param load true if we are loading, false if adding
197      * @return the newly assigned primary key of the new row
198      * @exception java.sql.SQLException 
199      */
200     public final long insertRow(Object   obj, boolean load) 
201         throws SQLException {
202 
203         QuoteLine quote_line = (QuoteLine)obj;
204 
205         if (!load)
206             quote_line.setPK(DBUtils.generatePK());
207 
208         StringBuffer   query = new StringBuffer  (insertQuery);
209         Statement stmt = con.createStatement();
210 
211         query.append(quote_line.getPK()).append(","); 
212         query.append(quote_line.getQuotePK()).append(","); 
213         query.append(quote_line.getProduct().getPK()).append(","); 
214         query.append(JDBC.quoteMore(quote_line.getProduct().getName()));
215         query.append(JDBC.quoteMore(quote_line.getProduct().getModelNo()));
216         query.append(quote_line.getQuantity().intValue()).append(","); 
217         query.append(quote_line.getProduct().getCost().doubleValue()).append(","); 
218         query.append(quote_line.getProduct().getPrice().doubleValue()).append(","); 
219         query.append(quote_line.getTotalPrice().doubleValue()).append(","); 
220         query.append(JDBC.quoteMore(quote_line.getProduct().getDesc()));
221         query.append(JDBC.quoteMore(quote_line.getModifiedBy()));
222         if (DB_TYPE == Prefs.MYSQL)
223             query.append("CURRENT_DATE");
224         else
225             query.append("SYSDATE");
226         query.append(")");
227 
228         if (Prefs.DEBUG) LogWrite.write(query.toString());
229         int rc = stmt.executeUpdate(query.toString());
230 
231         return quote_line.getPK();
232     }
233 
234     /**
235      * delete a single quote_line row using the passed
236      * primary key value
237      *
238      * @param ojb primary key stored in a Long
239      * @exception java.sql.SQLException 
240      */
241     public final void deleteRow(Object   obj) 
242         throws SQLException {
243 
244         long pkValue = ((Long  )obj).longValue();
245 
246         String   query = deleteQuery + pkValue;
247 
248         Statement stmt = null;
249 
250         try {
251             stmt = con.createStatement();
252             if (Prefs.DEBUG) LogWrite.write(query);
253             stmt.executeUpdate(query);
254         } catch (SQLException e) {
255             throw e;
256         } finally {
257             try {
258                 if (stmt != null) stmt.close();
259             } catch (SQLException x) { }
260         }
261     }
262 
263     /**
264      * select a single quote_line row using the passed
265      * primary key
266      * 
267      * @param name description
268      * @return the QuoteLine row(s) that were selected
269      * @exception java.sql.SQLException 
270      */
271     public final void selectByQuoteRow(long quote_pk, ArrayList   lines) 
272         throws SQLException {
273 
274         QuoteLine ql = null;
275 
276         Statement stmt = null;
277         ResultSet rs = null;
278         String   query = selectByQuoteQuery + quote_pk;
279 
280         try {
281             stmt = con.createStatement();
282             if (Prefs.DEBUG) LogWrite.write(query);
283             rs = stmt.executeQuery(query);
284 
285             int i;
286             ProductDB prodDB = new ProductDB(DB_TYPE);
287             prodDB.setConnection(getConnection());
288             long productPK;
289 
290             while (rs.next()) {
291                 i=1;
292                 ql = new QuoteLine();
293                 ql.setPK(rs.getLong(i)); i++;
294                 ql.setQuotePK(quote_pk);
295                 productPK = rs.getLong(i); i++; 
296                 ql.setProduct((Product)prodDB.selectRow(new Long  (productPK)));
297                 ql.getProduct().setPK(productPK);
298                 ql.setQuantity(new Integer  (rs.getInt(i))); i++;
299                 ql.setTotalPrice(new Double  (rs.getDouble(i))); i++;
300                 ql.setModifiedBy(rs.getString(i)); i++;
301                 ql.setModifiedDate(rs.getDate(i)); 
302                 lines.add(ql);
303             }
304 
305         } catch (SQLException e) {
306             throw e;
307         } finally {
308             try {
309                 if (rs != null) rs.close();
310             } catch (SQLException x) { throw x; }
311             try {
312                 if (stmt != null) stmt.close();
313             } catch (SQLException x) { throw x; }
314         }
315     }
316 
317     /**
318      * delete all quote_line rows using the passed
319      * quote_pk primary key value
320      *
321      * @param ojb quote primary key stored in a Long
322      * @exception java.sql.SQLException 
323      */
324     public final void deleteQuoteRows(long quote_pk) 
325         throws SQLException {
326 
327         String   query = deleteByQuoteQuery + quote_pk;
328 
329         Statement stmt = null;
330 
331         try {
332             stmt = con.createStatement();
333             if (Prefs.DEBUG) LogWrite.write(query);
334             stmt.executeUpdate(query);
335         } catch (SQLException e) {
336             LogWrite.write(e);
337             throw e;
338         } finally {
339             try {
340                 if (stmt != null) stmt.close();
341             } catch (SQLException x) { }
342         }
343     }
344 
345     /**
346      * delete all quote_line rows using the passed
347      * opp_pk primary key value
348      *
349      * @param ojb opportunity primary key stored in a Long
350      * @exception java.sql.SQLException 
351      */
352     public final void deleteQuoteRowsByOpp(long opp_pk) 
353         throws SQLException {
354 
355         String   query = deleteByOppQuery + opp_pk + ")";
356 
357         Statement stmt = null;
358 
359         try {
360             stmt = con.createStatement();
361             if (Prefs.DEBUG) LogWrite.write(query);
362             stmt.executeUpdate(query);
363         } catch (SQLException e) {
364             LogWrite.write(e);
365             throw e;
366         } finally {
367             try {
368                 if (stmt != null) stmt.close();
369             } catch (SQLException x) { }
370         }
371     }
372     /**
373      * truncate the whole table
374      *
375      * @exception java.sql.SQLException
376      */
377     public final void truncate()
378         throws SQLException {
379 
380         String   query = "truncate table quote_line";
381    
382         Statement stmt = null;
383         try {
384             stmt = con.createStatement();
385             if (Prefs.DEBUG) LogWrite.write(query);
386             stmt.executeUpdate(query);
387         } catch (SQLException e) {
388             throw e;
389         } finally {
390             try { if (stmt != null) stmt.close();
391             } catch (SQLException x) { }
392         }
393     }
394 
395 }
396
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags