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 13 14 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 75 public OpportunityDB() { 76 } 77 78 82 public OpportunityDB(int dbType) { 83 DB_TYPE = dbType; 84 } 85 86 92 public OpportunityDB(Connection con) { 93 this.con = con; 94 } 95 96 101 public Connection getConnection() { 102 return this.con; 103 } 104 105 110 public void setConnection(Connection con) 111 throws SQLException { 112 113 this.con = con; 114 } 115 116 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 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 ActivityDB activityDB = new ActivityDB(DB_TYPE); 167 activityDB.setConnection(getConnection()); 168 activityDB.selectByOppRows(pkValue, opportunity.getActivities()); 169 QuoteDB quoteDB = new QuoteDB(DB_TYPE); 171 quoteDB.setConnection(getConnection()); 172 quoteDB.selectByOppRow(pkValue, opportunity.getQuotes()); 173 174 ForecastDB forecastDB = new ForecastDB(DB_TYPE); 176 forecastDB.setConnection(getConnection()); 177 forecastDB.selectByOppRow(pkValue, opportunity.getForecasts()); 178 179 OrderDB orderDB = new OrderDB(DB_TYPE); 181 orderDB.setConnection(getConnection()); 182 orderDB.selectByOppRow(pkValue, opportunity.getOrders()); 183 184 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 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 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 351 public final void deleteRow(Object obj) 352 throws SQLException { 353 354 long pkValue = ((Long )obj).longValue(); 355 String query = deleteQuery + pkValue; 356 357 359 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 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 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 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 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 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 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 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 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 | Popular Tags |