KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > sellwin > db > OrderDB


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  * This class implements the DBInterface for
15  * the Order class objects which are stored
16  * in the order database table.
17  */
18 public class OrderDB extends DBType implements DBInterface {
19     private Connection con;
20         
21     private final static String JavaDoc deleteOppQuery =
22         "DELETE FROM sellwin_order WHERE opp_pk = ";
23     private final static String JavaDoc deleteQuery =
24         "DELETE FROM sellwin_order WHERE pk = ";
25     private final static String JavaDoc insertQuery =
26         "INSERT INTO sellwin_order " +
27         "VALUES (";
28     private final static String JavaDoc updateQuery =
29         "UPDATE sellwin_order SET " ;
30
31     private final static String JavaDoc selectQuery =
32             "SELECT opp_pk, "+
33             "order_number, quote_pk, ship_address_pk, "+
34             "bill_address_pk, status, payment_form, "+
35             "modified_by, modified_date " +
36             "FROM sellwin_order "+
37             "WHERE pk = ";
38     private final static String JavaDoc selectByOppQuery =
39             "SELECT pk, "+
40             "order_number, quote_pk, ship_address_pk, "+
41             "bill_address_pk, status, payment_form, "+
42             "modified_by, modified_date " +
43             "FROM sellwin_order "+
44             "WHERE opp_pk = ";
45
46     /**
47      * a do-nothing constructor but necessary to
48      * do the operations offered by this class
49      *
50      */
51     public OrderDB() {
52     }
53
54     /**
55      * construct, specifying a db type
56      * @param dbType the db type to assume
57      */
58     public OrderDB(int dbType) {
59         DB_TYPE = dbType;
60     }
61
62     /**
63      * a version of the constructor when a connection
64      * is already obtained from somewhere else
65      *
66      * @param con the Connection to use
67      */
68     public OrderDB(Connection con) {
69         this.con = con;
70     }
71
72     /**
73      * return the Connection in use
74      *
75      * @return the Connection in use
76      */
77     public Connection getConnection() {
78         return this.con;
79     }
80
81     /**
82      * set the Connection to use
83      *
84      * @param con the Connection to use for any future IO's
85      */
86     public final void setConnection(Connection con)
87         throws SQLException {
88
89         this.con = con;
90     }
91
92     /**
93      * select a single order row using the passed
94      * primary key
95      *
96      * @param name description
97      * @return the Order row(s) that were selected
98      * @exception java.sql.SQLException
99      */
100     public final Object JavaDoc selectRow(Object JavaDoc pk)
101         throws SQLException {
102
103         Order order = new Order();
104         order.setPK(((Long JavaDoc)pk).longValue());
105
106         Statement stmt = null;
107         ResultSet rs = null;
108         String JavaDoc query = selectQuery + order.getPK();
109
110         try {
111             stmt = con.createStatement();
112             if (Prefs.DEBUG) LogWrite.write(query);
113             rs = stmt.executeQuery(query);
114
115             int i;
116
117             while (rs.next()) {
118                 i=1;
119                 order.setOppKey(rs.getLong(i)); i++;
120                 order.setOrderNumber(new Long JavaDoc(rs.getLong(i))); i++;
121                 order.setQuoteKey(rs.getLong(i)); i++;
122                 AddressDB adb = new AddressDB(DB_TYPE);
123                 adb.setConnection(getConnection());
124                 order.setBillAddressKey(rs.getLong(i)); i++;
125                 order.setBillAddress((Address)adb.selectRow(new Long JavaDoc(order.getBillAddressKey())));
126                 order.setShipAddressKey(rs.getLong(i)); i++;
127                 order.setShipAddress((Address)adb.selectRow(new Long JavaDoc(order.getShipAddressKey())));
128                 order.setStatus(rs.getString(i)); i++;
129                 order.setPaymentForm(rs.getString(i)); i++;
130                 order.setModifiedBy(rs.getString(i)); i++;
131                 order.setModifiedDate(rs.getDate(i));
132
133                 QuoteDB quoteDB = new QuoteDB(DB_TYPE);
134                 quoteDB.setConnection(getConnection());
135                 Quote quote = (Quote)quoteDB.selectRow(new Long JavaDoc(order.getQuoteKey()));
136                 order.setQuote(quote);
137             }
138
139         } catch (SQLException e) {
140             throw e;
141         } finally {
142             try {
143                 if (rs != null) rs.close();
144             } catch (SQLException x) { throw x; }
145             try {
146                 if (stmt != null) stmt.close();
147             } catch (SQLException x) { throw x; }
148         }
149
150         return order;
151     }
152
153
154     /**
155      * not yet coded
156      * @param name description
157      * @exception java.sql.SQLException
158      */
159     public final void updateRow(Object JavaDoc obj)
160         throws SQLException {
161
162         Order order = (Order)obj;
163         Statement stmt=null;
164         StringBuffer JavaDoc query = new StringBuffer JavaDoc(updateQuery);
165
166         try {
167             
168             stmt = con.createStatement();
169
170             query.append("status=");
171             query.append(JDBC.quoteMore(order.getStatus()));
172             query.append("payment_form=");
173             query.append(JDBC.quoteMore(order.getPaymentForm()));
174             query.append("sub_total=");
175             query.append(order.getSubTotal()).append(",");
176             query.append("tax=");
177             query.append(order.getTax()).append(",");
178             query.append("shipping=");
179             query.append(order.getShipping()).append(",");
180             query.append("total=");
181             query.append(order.getTotal()).append(",");
182             query.append("modified_by=");
183             query.append(JDBC.quoteMore(order.getModifiedBy()));
184             query.append("modified_date=");
185             if (DB_TYPE == Prefs.MYSQL)
186                 query.append("CURRENT_DATE");
187             else
188                 query.append("SYSDATE");
189             query.append(" WHERE pk=");
190             query.append(order.getPK());
191
192             if (Prefs.DEBUG) LogWrite.write(query.toString());
193             int updatedRows = stmt.executeUpdate(query.toString());
194         } catch (SQLException e) {
195             throw e;
196         } finally {
197             try { if (stmt != null) stmt.close();
198             } catch (SQLException x) {}
199         }
200     }
201
202     /**
203      * insert a new order row using the passed
204      * Order object as the column values.
205      *
206      * @param obj the Order to add or load
207      * @param load true if we are to load or false if we are to add
208      * @return the newly assigned primary key of the new row
209      * @exception java.sql.SQLException
210      */
211     public final long insertRow(Object JavaDoc obj, boolean load)
212         throws SQLException {
213
214         Order order = (Order)obj;
215
216         if (!load)
217             order.setPK(DBUtils.generatePK());
218
219         StringBuffer JavaDoc query = new StringBuffer JavaDoc(insertQuery);
220         Statement stmt = con.createStatement();
221
222         try {
223             query.append(order.getPK()).append(",");
224             query.append(order.getOppKey()).append(",");
225             query.append(order.getOrderNumber().longValue()).append(",");
226             query.append(order.getQuoteKey()).append(",");
227             query.append(order.getShipAddress().getPK()).append(",");
228             query.append(order.getBillAddress().getPK()).append(",");
229             query.append(JDBC.quoteMore(order.getStatus()));
230             query.append(JDBC.quoteMore(order.getPaymentForm()));
231             query.append(order.getSubTotal()).append(",");
232             query.append(order.getTax()).append(",");
233             query.append(order.getShipping()).append(",");
234             query.append(order.getTotal()).append(",");
235
236             query.append(JDBC.quoteMore(order.getModifiedBy()));
237             if (DB_TYPE == Prefs.MYSQL)
238                 query.append("CURRENT_DATE");
239             else
240                 query.append("SYSDATE");
241             query.append(")");
242         } catch (NullPointerException JavaDoc e) {
243             LogWrite.write(e);
244         }
245
246         if (Prefs.DEBUG) LogWrite.write(query.toString());
247         int rc = stmt.executeUpdate(query.toString());
248
249         return order.getPK();
250     }
251
252     /**
253      * delete a single order row using the passed
254      * primary key value
255      *
256      * @param ojb primary key stored in a Long
257      * @exception java.sql.SQLException
258      */
259     public final void deleteRow(Object JavaDoc obj)
260         throws SQLException {
261
262         long pkValue = ((Long JavaDoc)obj).longValue();
263
264         String JavaDoc query = deleteQuery + pkValue;
265
266         Statement stmt = null;
267
268         try {
269             stmt = con.createStatement();
270             if (Prefs.DEBUG) LogWrite.write(query);
271             stmt.executeUpdate(query);
272         } catch (SQLException e) {
273             throw e;
274         } finally {
275             try {
276                 if (stmt != null) stmt.close();
277             } catch (SQLException x) { }
278         }
279     }
280
281     /**
282      * delete all order rows using the passed
283      * opportunity primary key value
284      *
285      * @param ojb primary key stored in a Long
286      * @exception java.sql.SQLException
287      */
288     public final void deleteOpportunityRows(Object JavaDoc obj)
289         throws SQLException {
290
291         long opp_pk = ((Long JavaDoc)obj).longValue();
292
293         String JavaDoc query = deleteQuery + opp_pk;
294
295         Statement stmt = null;
296
297         try {
298             stmt = con.createStatement();
299             if (Prefs.DEBUG) LogWrite.write(query);
300             stmt.executeUpdate(query);
301         } catch (SQLException e) {
302             throw e;
303         } finally {
304             try {
305                 if (stmt != null) stmt.close();
306             } catch (SQLException x) { }
307         }
308     }
309
310     /**
311      * select all order rows using the passed
312      * opportunity primary key
313      *
314      * @param name description
315      * @exception java.sql.SQLException
316      */
317     public final void selectByOppRow(long opp_pk, ArrayList JavaDoc orders)
318         throws SQLException {
319
320         Order order = null;
321
322         Statement stmt = null;
323         ResultSet rs = null;
324         String JavaDoc query = selectByOppQuery + opp_pk;
325
326         try {
327             stmt = con.createStatement();
328             if (Prefs.DEBUG) LogWrite.write(query);
329             rs = stmt.executeQuery(query);
330
331             int i;
332             QuoteDB quoteDB = new QuoteDB(DB_TYPE);
333             quoteDB.setConnection(getConnection());
334             Quote quote;
335
336             while (rs.next()) {
337                 i=1;
338                 order = new Order();
339                 order.setPK(rs.getLong(i)); i++;
340                 order.setOrderNumber(new Long JavaDoc(rs.getLong(i))); i++;
341                 order.setQuoteKey(rs.getLong(i)); i++;
342                 AddressDB adb = new AddressDB(DB_TYPE);
343                 adb.setConnection(getConnection());
344                 order.setBillAddressKey(rs.getLong(i)); i++;
345                 order.setBillAddress((Address)adb.selectRow(new Long JavaDoc(order.getBillAddressKey())));
346                 order.setShipAddressKey(rs.getLong(i)); i++;
347                 order.setShipAddress((Address)adb.selectRow(new Long JavaDoc(order.getShipAddressKey())));
348                 order.setStatus(rs.getString(i)); i++;
349                 order.setPaymentForm(rs.getString(i)); i++;
350                 order.setModifiedBy(rs.getString(i)); i++;
351                 order.setModifiedDate(rs.getDate(i));
352
353                 //go ahead and get the order's quote information
354 quote = (Quote)quoteDB.selectRow(new Long JavaDoc(order.getQuoteKey()));
355                 order.setQuote(quote);
356
357                 orders.add(order);
358             }
359
360         } catch (SQLException e) {
361             throw e;
362         } finally {
363             try {
364                 if (rs != null) rs.close();
365             } catch (SQLException x) { throw x; }
366             try {
367                 if (stmt != null) stmt.close();
368             } catch (SQLException x) { throw x; }
369         }
370     }
371     /**
372      * truncate the whole table
373      *
374      * @exception java.sql.SQLException
375      */
376     public final void truncate()
377         throws SQLException {
378
379         String JavaDoc query = "truncate table sellwin_order";
380    
381         Statement stmt = null;
382         try {
383             stmt = con.createStatement();
384             if (Prefs.DEBUG) LogWrite.write(query);
385             stmt.executeUpdate(query);
386         } catch (SQLException e) {
387             throw e;
388         } finally {
389             try { if (stmt != null) stmt.close();
390             } catch (SQLException x) { }
391         }
392     }
393
394 }
395
Popular Tags