|
1 package sellwin.db;
2
3 import sellwin.domain.*;
4 import sellwin.utils.*;
5
6 import java.sql.*;
7 import java.util.*;
8
9
13
18 public class SalesPersonDB extends DBType implements DBInterface {
19
20 private Connection con;
21 private final static String  deleteAllQuery =
22 "DELETE FROM sales_person ";
23
24 private final static String deleteQuery =
25 "DELETE FROM sales_person WHERE pk = ";
26
27 private final static String insertQuery =
28 "INSERT INTO sales_person " +
29 "VALUES (";
30 private final static String updateQuery =
31 "UPDATE sales_person " +
32 "SET ";
33
34 private final static String selectAllNamesQuery =
35 "SELECT address.first_name, " +
36 "address.last_name, address.middle_initial "+
37 "FROM sales_person , address "+
38 "WHERE sales_person.address_pk = address.pk " +
39 "ORDER BY address.last_name, address.first_name";
40
41 private final static String selectQuery =
42 "SELECT id, password, address.pk, address.first_name, " +
43 "address.last_name, address.middle_initial, "+
44 "address.address_line1, address.address_line2, "+
45 "address.title, address.phone, "+
46 "address.fax, address.pager, "+
47 "address.cell, address.email, "+
48 "address.city, address.state, "+
49 "address.country, address.zip, "+
50 "address.zip4, address.modified_by, "+
51 "address.modified_date, sales_person.modified_by, "+
52 "sales_person.modified_date," +
53 "sales_person.sync_date " +
54 "FROM sales_person , address "+
55 "WHERE sales_person.pk = ";
56
57 private final static String selectAllIDQuery =
58 "SELECT id " +
59 "FROM sales_person " +
60 "ORDER BY id";
61
62 private final static String selectAllQuery =
63 "SELECT sales_person.pk, id, password, address.pk, address.first_name, " +
64 "address.last_name, address.middle_initial, "+
65 "address.address_line1, address.address_line2, "+
66 "address.title, address.phone, "+
67 "address.fax, address.pager, "+
68 "address.cell, address.email, "+
69 "address.city, address.state, "+
70 "address.country, address.zip, "+
71 "address.zip4, address.modified_by, "+
72 "address.modified_date, sales_person.modified_by, "+
73 "sales_person.modified_date, " +
74 "sales_person.sync_date " +
75 "FROM sales_person , address "+
76 "WHERE sales_person.address_pk = address.pk ";
77
78
79 private final static String selectByLogonQuery =
80 "SELECT sales_person.pk, address.pk, address.first_name, " +
81 "address.last_name, address.middle_initial, "+
82 "address.address_line1, address.address_line2, "+
83 "address.title, address.phone, "+
84 "address.fax, address.pager, "+
85 "address.cell, address.email, "+
86 "address.city, address.state, "+
87 "address.country, address.zip, "+
88 "address.zip4, address.modified_by, "+
89 "address.modified_date, sales_person.modified_by, "+
90 "sales_person.modified_date, " +
91 "sales_person.sync_date " +
92 "FROM sales_person , address "+
93 "WHERE sales_person.id = ";
94
95
96
100 public SalesPersonDB() {
101 }
102
103
107 public SalesPersonDB(int dbType) {
108 DB_TYPE = dbType;
109 }
110
111
117 public SalesPersonDB(Connection con) {
118 this.con = con;
119 }
120
121
126 public Connection getConnection() {
127 return this.con;
128 }
129
130
135 public final void setConnection(Connection con)
136 throws SQLException {
137
138 this.con = con;
139 }
140
141
149 public final Object selectRow(Object pk)
150 throws SQLException {
151
152 SalesPerson sp = new SalesPerson();
153 long pkValue = ((Long )pk).longValue();
154 Statement stmt = null;
155 ResultSet rs = null;
156 String query = selectQuery + pkValue +
157 " AND sales_person.address_pk = address.pk";
158
159 try {
160
161 UserGroupMemberDB userGroupMemberDB = new UserGroupMemberDB(DB_TYPE);
162 userGroupMemberDB.setConnection(getConnection());
163
164 stmt = con.createStatement();
165 if (Prefs.DEBUG) LogWrite.write(query);
166 rs = stmt.executeQuery(query);
167
168 int i;
169
170 while (rs.next()) {
171 i=1;
172 sp.setID(rs.getString(i)); i++;
173 sp.setPassword(rs.getString(i)); i++;
174 sp.getAddress().setPK(rs.getLong(i)); i++;
175 sp.getAddress().setFirstName(rs.getString(i)); i++;
176 sp.getAddress().setLastName(rs.getString(i)); i++;
177 sp.getAddress().setMiddleInitial(rs.getString(i)); i++;
178 sp.getAddress().setAddressLine1(rs.getString(i)); i++;
179 sp.getAddress().setAddressLine2(rs.getString(i)); i++;
180 sp.getAddress().setTitle(rs.getString(i)); i++;
181 sp.getAddress().setPhone(rs.getString(i)); i++;
182 sp.getAddress().setFax(rs.getString(i)); i++;
183 sp.getAddress().setPager(rs.getString(i)); i++;
184 sp.getAddress().setCell(rs.getString(i)); i++;
185 sp.getAddress().setEmail(rs.getString(i)); i++;
186 sp.getAddress().setCity(rs.getString(i)); i++;
187 sp.getAddress().setState(rs.getString(i)); i++;
188 sp.getAddress().setCountry(rs.getString(i)); i++;
189 sp.getAddress().setZip(rs.getString(i)); i++;
190 sp.getAddress().setZip4(rs.getString(i)); i++;
191 sp.getAddress().setModifiedBy(rs.getString(i)); i++;
192 sp.getAddress().setModifiedDate(rs.getDate(i)); i++;
193 sp.setModifiedBy(rs.getString(i)); i++;
194 sp.setModifiedDate(rs.getDate(i)); i++;
195 sp.setSyncDate(rs.getDate(i));
196
197 ArrayList userGroups = userGroupMemberDB.selectGroupsForUser(sp.getPK());
200 UserGroup ug;
201 for (int x=0;x<userGroups.size();x++) {
202 ug = (UserGroup)userGroups.get(x);
203 sp.addUserGroup(ug);
204 }
205
206 SalesPersonRoleDB salesPersonRoleDB = new SalesPersonRoleDB(
208 getConnection());
209 salesPersonRoleDB.selectRolesForUser(sp.getPK(), sp.getUserRoles());
210
211 }
212
213 } catch (SQLException e) {
214 LogWrite.write(e);
215 throw e;
216 } finally {
217 try {
218 if (rs != null) rs.close();
219 } catch (SQLException x) { throw x; }
220 try {
221 if (stmt != null) stmt.close();
222 } catch (SQLException x) { throw x; }
223 }
224
225 return sp;
226 }
227
228
229
238 public final void updateRow(Object obj)
239 throws SQLException {
240
241 SalesPerson sales_person = (SalesPerson)obj;
242 long pk = sales_person.getPK();
243
244 AddressDB addr = new AddressDB(DB_TYPE);
246 addr.setConnection(getConnection());
247 addr.updateRow(sales_person.getAddress());
248
249 StringBuffer query = new StringBuffer (updateQuery);
250 Statement stmt = con.createStatement();
251
252 query.append("id=");
253 query.append(JDBC.quoteMore(sales_person.getID()));
254 query.append("sync_date=");
255 if (sales_person.getSyncDate() == null)
256 query.append("null,");
257 else {
258 if (DB_TYPE == Prefs.MYSQL) {
259 query.append(DateUtils.formatDateTime(DB_TYPE,
260 sales_person.getSyncDate()));
261 query.append(",");
262 } else {
263 query.append("to_date(");
264 query.append(DateUtils.formatDateTime(DB_TYPE,
265 sales_person.getSyncDate()));
266 query.append(",'yyyy-mm-dd hh24:mi'),");
267 }
268 }
269 query.append("password=");
270 query.append(JDBC.quote(sales_person.getPassword()));
271 query.append(" WHERE pk=").append(sales_person.getPK());
272
273 if (Prefs.DEBUG) LogWrite.write(query.toString());
274 int updatedRows = stmt.executeUpdate(query.toString());
275
276 SalesPersonRoleDB spRoleDB = new SalesPersonRoleDB(DB_TYPE);
280 spRoleDB.setConnection(getConnection());
281 spRoleDB.deleteSalesPersonRows(new Long (sales_person.getPK()));
282 ArrayList roles = sales_person.getUserRoles();
283 UserRole role;
284 SalesPersonRole spRole;
285 for (int j=0;j<roles.size();j++) {
286 role = (UserRole)roles.get(j);
287 spRole = new SalesPersonRole();
288 spRole.setSalesPersonPK(sales_person.getPK());
289 spRole.setUserRolePK(role.getPK());
290 spRole.setModifiedBy(sales_person.getModifiedBy());
291 spRoleDB.insertRow(spRole, false);
292 }
293 }
294
295
304 public final long insertRow(Object obj, boolean load)
305 throws SQLException {
306
307 SalesPerson sales_person = (SalesPerson)obj;
308
309 AddressDB a= new AddressDB(DB_TYPE);
311 a.setConnection(getConnection());
312 sales_person.getAddress().setModifiedBy(sales_person.getModifiedBy());
313 sales_person.getAddress().setPK(a.insertRow(sales_person.getAddress(), load));
314
315 if (!load)
316 sales_person.setPK(DBUtils.generatePK());
317
318 StringBuffer query = new StringBuffer (insertQuery);
319 Statement stmt = con.createStatement();
320
321 query.append(sales_person.getPK());
322 query.append(",");
323 query.append(JDBC.quoteMore(sales_person.getID()));
324 query.append(JDBC.quoteMore(sales_person.getPassword()));
325 query.append(sales_person.getAddress().getPK()).append(",");
326 query.append(JDBC.quoteMore(sales_person.getModifiedBy()));
327 if (DB_TYPE == Prefs.MYSQL)
328 query.append("CURRENT_DATE,");
329 else
330 query.append("SYSDATE,");
331 query.append("null"); query.append(")");
333
334 if (Prefs.DEBUG) LogWrite.write(query.toString());
335 int rc = stmt.executeUpdate(query.toString());
336
337 return sales_person.getPK();
338 }
339
340
347 public final void deleteRow(Object obj)
348 throws SQLException {
349
350 SalesPerson sp = (SalesPerson)obj;
351
352
353 String query = deleteQuery + sp.getPK();
354
355 Statement stmt = null;
356
357 try {
358
359 stmt = con.createStatement();
360 if (Prefs.DEBUG) LogWrite.write(query);
361 stmt.executeUpdate(query);
362
363 AddressDB addr = new AddressDB(DB_TYPE);
365 addr.setConnection(getConnection());
366 addr.deleteRow(new Long (sp.getAddress().getPK()));
367 } catch (SQLException e) {
368 throw e;
369 } finally {
370 try {
371 if (stmt != null) stmt.close();
372 } catch (SQLException x) { }
373 }
374 }
375
376
385 public final SalesPerson selectRow(String id, String password)
386 throws SQLException, SellwinNotFoundException {
387
388 SalesPerson sp = null;
389 Statement stmt = null;
390 ResultSet rs = null;
391 String query = selectByLogonQuery + "'" + id + "'" +
392 " AND password = " + "'" + password + "'" +
393 " AND sales_person.address_pk = address.pk";
394
395 try {
396 stmt = con.createStatement();
397
398 if (Prefs.DEBUG) LogWrite.write(query);
399 rs = stmt.executeQuery(query);
400
401 int i;
402
403 while (rs.next()) {
404 i=1;
405 sp = new SalesPerson();
406 sp.setID(id);
407 sp.setPassword(password);
408 sp.setPK(rs.getLong(i)); i++;
409 sp.getAddress().setPK(rs.getLong(i)); i++;
410 sp.getAddress().setFirstName(rs.getString(i)); i++;
411 sp.getAddress().setLastName(rs.getString(i)); i++;
412 sp.getAddress().setMiddleInitial(rs.getString(i)); i++;
413 sp.getAddress().setAddressLine1(rs.getString(i)); i++;
414 sp.getAddress().setAddressLine2(rs.getString(i)); i++;
415 sp.getAddress().setTitle(rs.getString(i)); i++;
416 sp.getAddress().setPhone(rs.getString(i)); i++;
417 sp.getAddress().setFax(rs.getString(i)); i++;
418 sp.getAddress().setPager(rs.getString(i)); i++;
419 sp.getAddress().setCell(rs.getString(i)); i++;
420 sp.getAddress().setEmail(rs.getString(i)); i++;
421 sp.getAddress().setCity(rs.getString(i)); i++;
422 sp.getAddress().setState(rs.getString(i)); i++;
423 sp.getAddress().setCountry(rs.getString(i)); i++;
424 sp.getAddress().setZip(rs.getString(i)); i++;
425 sp.getAddress().setZip4(rs.getString(i)); i++;
426 sp.getAddress().setModifiedBy(rs.getString(i)); i++;
427 sp.getAddress().setModifiedDate(rs.getDate(i)); i++;
428 sp.setModifiedBy(rs.getString(i)); i++;
429 sp.setModifiedDate(rs.getDate(i)); i++;
430 sp.setSyncDate(rs.getDate(i));
431
432 SalesPersonRoleDB salesPersonRoleDB = new SalesPersonRoleDB(DB_TYPE);
434 salesPersonRoleDB.setConnection(getConnection());
435 salesPersonRoleDB.selectRolesForUser(sp.getPK(), sp.getUserRoles());
436 }
437
438 if (sp == null)
439 throw new SellwinNotFoundException("Logon failed.");
440
441 } catch (SellwinNotFoundException x) {
442 throw x;
443 } catch (SQLException e) {
444 LogWrite.write(e);
445 throw e;
446 } catch (Exception f) {
447 LogWrite.write(f);
448 } finally {
449 try {
450 if (rs != null) rs.close();
451 } catch (SQLException x) { throw x; }
452 try {
453 if (stmt != null) stmt.close();
454 } catch (SQLException x) { throw x; }
455 }
456
457 if (Prefs.DEBUG) LogWrite.write("returning an sp");
458 return sp;
459 }
460
461
467 public final ArrayList selectAllNames()
468 throws SQLException {
469
470 ArrayList names = new ArrayList();
471 Statement stmt = null;
472 ResultSet rs = null;
473 String query = selectAllNamesQuery;
474
475 try {
476 stmt = con.createStatement();
477 if (Prefs.DEBUG) LogWrite.write(query);
478 rs = stmt.executeQuery(query);
479
480 int i;
481 SalesPerson sp;
482
483 while (rs.next()) {
484 i=1;
485 sp = new SalesPerson();
486 sp.getAddress().setFirstName(rs.getString(i)); i++;
487 sp.getAddress().setLastName(rs.getString(i)); i++;
488 sp.getAddress().setMiddleInitial(rs.getString(i)); i++;
489 names.add(sp.getAddress().getFormattedName());
490 }
491
492 } catch (SQLException e) {
493 throw e;
494 } finally {
495 try {
496 if (rs != null) rs.close();
497 } catch (SQLException x) { throw x; }
498 try {
499 if (stmt != null) stmt.close();
500 } catch (SQLException x) { throw x; }
501 }
502
503 return names;
504 }
505
506
514 public final TreeMap selectAllRows(java.util.Date lastSyncDate)
515 throws SQLException {
516
517 TreeMap users = new TreeMap();
518 SalesPerson sp;
519 Statement stmt = null;
520 ResultSet rs = null;
521 StringBuffer query = new StringBuffer ();
522 query.append(selectAllQuery);
523 if (lastSyncDate != null) {
524 query.append(" AND sales_person.modified_date > ");
525 query.append(DateUtils.formatDateTime(DB_TYPE, lastSyncDate));
526 }
527 query.append(" ORDER BY id");
528
529 try {
530 stmt = con.createStatement();
531 if (Prefs.DEBUG) LogWrite.write(query.toString());
532 rs = stmt.executeQuery(query.toString());
533
534 int i;
535 UserGroupMemberDB userGroupMemberDB = new UserGroupMemberDB(DB_TYPE);
536 userGroupMemberDB.setConnection(getConnection());
537 ArrayList userGroups;
538 UserGroup ug;
539
540 while (rs.next()) {
541 i=1;
542 sp = new SalesPerson();
543 sp.setPK(rs.getLong(i)); i++;
544 sp.setID(rs.getString(i)); i++;
545 sp.setPassword(rs.getString(i)); i++;
546 sp.getAddress().setPK(rs.getLong(i)); i++;
547 sp.getAddress().setFirstName(rs.getString(i)); i++;
548 sp.getAddress().setLastName(rs.getString(i)); i++;
549 sp.getAddress().setMiddleInitial(rs.getString(i)); i++;
550 sp.getAddress().setAddressLine1(rs.getString(i)); i++;
551 sp.getAddress().setAddressLine2(rs.getString(i)); i++;
552 sp.getAddress().setTitle(rs.getString(i)); i++;
553 sp.getAddress().setPhone(rs.getString(i)); i++;
554 sp.getAddress().setFax(rs.getString(i)); i++;
555 sp.getAddress().setPager(rs.getString(i)); i++;
556 sp.getAddress().setCell(rs.getString(i)); i++;
557 sp.getAddress().setEmail(rs.getString(i)); i++;
558 sp.getAddress().setCity(rs.getString(i)); i++;
559 sp.getAddress().setState(rs.getString(i)); i++;
560 sp.getAddress().setCountry(rs.getString(i)); i++;
561 sp.getAddress().setZip(rs.getString(i)); i++;
562 sp.getAddress().setZip4(rs.getString(i)); i++;
563 sp.getAddress().setModifiedBy(rs.getString(i)); i++;
564 sp.getAddress().setModifiedDate(rs.getDate(i)); i++;
565 sp.setModifiedBy(rs.getString(i)); i++;
566 sp.setModifiedDate(rs.getDate(i)); i++;
567 sp.setSyncDate(rs.getDate(i));
568
569 userGroups = userGroupMemberDB.selectGroupsForUser(sp.getPK());
572 for (int x=0;x<userGroups.size();x++) {
573 ug = (UserGroup)userGroups.get(x);
574 sp.addUserGroup(ug);
575 }
576 users.put(sp.getID(), sp);
577
578 SalesPersonRoleDB salesPersonRoleDB = new SalesPersonRoleDB(
580 getConnection());
581 salesPersonRoleDB.selectRolesForUser(sp.getPK(), sp.getUserRoles());
582 }
583
584 } catch (SQLException e) {
585 throw e;
586 } finally {
587 try {
588 if (rs != null) rs.close();
589 } catch (SQLException x) { throw x; }
590 try {
591 if (stmt != null) stmt.close();
592 } catch (SQLException x) { throw x; }
593 }
594
595 return users;
596 }
597
598
604 public final ArrayList selectAllIDs()
605 throws SQLException {
606
607 ArrayList idList = new ArrayList();
608 Statement stmt = null;
609 ResultSet rs = null;
610 String query = selectAllIDQuery ;
611
612 try {
613 stmt = con.createStatement();
614 if (Prefs.DEBUG) LogWrite.write(query);
615 rs = stmt.executeQuery(query);
616
617 while (rs.next()) {
618 idList.add(rs.getString(1));
619 }
620 } catch (SQLException e) {
621 throw e;
622 } finally {
623 try {
624 if (rs != null) rs.close();
625 } catch (SQLException x) { throw x; }
626 try {
627 if (stmt != null) stmt.close();
628 } catch (SQLException x) { throw x; }
629 }
630
631 return idList;
632 }
633
634
639 public final void deleteAllRows()
640 throws SQLException {
641
642 String query = deleteAllQuery;
643
644 Statement stmt = null;
645
646 try {
647 stmt = con.createStatement();
648 if (Prefs.DEBUG) LogWrite.write(query);
649 stmt.executeUpdate(query);
650 } catch (SQLException e) {
651 throw e;
652 } finally {
653 try {
654 if (stmt != null) stmt.close();
655 } catch (SQLException x) { }
656 }
657 }
658
663 public final void truncate()
664 throws SQLException {
665
666 String query = "truncate table sales_person";
667
668 Statement stmt = null;
669 try {
670 stmt = con.createStatement();
671 if (Prefs.DEBUG) LogWrite.write(query);
672 stmt.executeUpdate(query);
673 } catch (SQLException e) {
674 throw e;
675 } finally {
676 try { if (stmt != null) stmt.close();
677 } catch (SQLException x) { }
678 }
679 }
680
681
687 public final void updateSyncDate(long pk)
688 throws SQLException {
689
690 StringBuffer query = new StringBuffer (updateQuery);
691 Statement stmt = con.createStatement();
692
693 query.append("sync_date=");
694 query.append(DateUtils.formatDateTime(DB_TYPE, new java.util.Date ()));
695 query.append(" WHERE pk=").append(pk);
696
697 if (Prefs.DEBUG) LogWrite.write(query.toString());
698 int updatedRows = stmt.executeUpdate(query.toString());
699 }
700 }
701 |
Popular Tags |
Java API By Example, From Geeks To Geeks.