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 LeadDB extends DBType implements DBInterface { 20 private Connection con; 21 22 private final static String deleteQuery = 23 "DELETE FROM lead WHERE pk = "; 24 private final static String insertQuery = 25 "INSERT INTO lead " + 26 "VALUES ("; 27 private final static String updateQuery = 28 "UPDATE lead " + 29 "SET "; 30 private final static String selectQuery = 31 "SELECT campaign_pk, address_pk, company, " + 32 "interest_level, contacted_by_pk, "+ 33 "contact_date, converted_opp, "+ 34 "modified_by, modified_date " + 35 "FROM lead "+ 36 "WHERE pk = "; 37 38 private final static String selectAllQuery = 39 "SELECT pk, campaign_pk, address_pk, company, " + 40 "interest_level, contacted_by_pk, "+ 41 "contact_date, converted_opp, "+ 42 "modified_by, modified_date " + 43 "FROM lead "; 44 45 private final static String selectByCampaignQuery = 46 "SELECT pk, address_pk, company, " + 47 "interest_level, contacted_by_pk, "+ 48 "contact_date, converted_opp, "+ 49 "modified_by, modified_date " + 50 "FROM lead "+ 51 "WHERE campaign_pk = "; 52 53 57 public LeadDB() { 58 } 59 60 64 public LeadDB(int dbType) { 65 DB_TYPE = dbType; 66 } 67 68 74 public LeadDB(Connection con) { 75 this.con = con; 76 } 77 78 83 public Connection getConnection() { 84 return this.con; 85 } 86 87 92 public final void setConnection(Connection con) 93 throws SQLException { 94 95 this.con = con; 96 } 97 98 106 public final Object selectRow(Object pk) 107 throws SQLException { 108 109 Lead lead = new Lead(); 110 lead.setPK(((Long )pk).longValue()); 111 112 Statement stmt = null; 113 ResultSet rs = null; 114 String query = selectQuery + lead.getPK(); 115 116 try { 117 stmt = con.createStatement(); 118 if (Prefs.DEBUG) LogWrite.write(query); 119 rs = stmt.executeQuery(query); 120 121 int i; 122 123 while (rs.next()) { 124 i=1; 125 lead.setCampaignKey(rs.getLong(i)); i++; 126 127 lead.getAddress().setPK(rs.getLong(i)); i++; 128 AddressDB addr = new AddressDB(DB_TYPE); 129 addr.setConnection(getConnection()); 130 Address a = (Address)addr.selectRow(new Long (lead.getAddress().getPK())); 131 lead.setAddress(a); 132 133 lead.setCompany(rs.getString(i)); i++; 134 lead.setInterestLevel(rs.getString(i)); i++; 135 136 lead.getContactedBy().setPK(rs.getLong(i)); i++; 137 SalesPersonDB spd = new SalesPersonDB(DB_TYPE); 138 spd.setConnection(getConnection()); 139 SalesPerson sp = (SalesPerson)spd.selectRow(new Long (lead.getContactedBy().getPK())); 140 lead.setContactedBy(sp); 141 142 lead.setContactDate(rs.getDate(i)); i++; 143 String x = rs.getString(i); i++; 144 Boolean val; 145 if (x.equals("Y")) val = new Boolean (true); else val = new Boolean (false); 146 lead.setConverted(val); 147 lead.setModifiedBy(rs.getString(i)); i++; 148 lead.setModifiedDate(rs.getDate(i)); 149 } 150 151 } catch (SQLException e) { 152 throw e; 153 } finally { 154 try { 155 if (rs != null) rs.close(); 156 } catch (SQLException x) { throw x; } 157 try { 158 if (stmt != null) stmt.close(); 159 } catch (SQLException x) { throw x; } 160 } 161 162 return lead; 163 } 164 165 166 175 public final void updateRow(Object obj) 176 throws SQLException { 177 178 Lead lead = (Lead)obj; 179 180 StringBuffer query = new StringBuffer (updateQuery); 181 Statement stmt = con.createStatement(); 182 183 String flag; 184 Boolean x = lead.getConverted(); 185 if (x.booleanValue()) flag = "Y"; else flag = "N"; 186 query.append("converted_opp="); 187 query.append("'").append(flag).append("',"); 188 query.append("modified_by="); 189 query.append("'").append(lead.getModifiedBy()).append("',"); 190 query.append("modified_date="); 191 if (DB_TYPE == Prefs.MYSQL) 192 query.append("CURRENT_DATE"); 193 else 194 query.append("SYSDATE"); 195 query.append(" WHERE pk="); 196 query.append(lead.getPK()); 197 198 if (Prefs.DEBUG) LogWrite.write(query.toString()); 199 int updatedRows = stmt.executeUpdate(query.toString()); 200 } 201 202 212 public final long insertRow(Object obj, boolean load) 213 throws SQLException { 214 215 Lead lead = (Lead)obj; 216 217 Address address = lead.getAddress(); 219 AddressDB a= new AddressDB(DB_TYPE); 220 a.setConnection(getConnection()); 221 lead.getAddress().setPK(a.insertRow(address, load)); 222 223 224 228 if (!load) 229 lead.setPK(DBUtils.generatePK()); 230 StringBuffer query = new StringBuffer (insertQuery); 231 232 Statement stmt = con.createStatement(); 233 234 query.append(lead.getPK()).append(","); 235 query.append(lead.getCampaignKey()).append(","); 236 query.append(lead.getAddress().getPK()).append(","); 237 query.append(JDBC.quoteMore(lead.getCompany())); 238 query.append(JDBC.quoteMore(lead.getInterestLevel())); 239 query.append(lead.getContactedBy().getPK()).append(","); 240 query.append(JDBC.quoteMore(DateUtils.format(DB_TYPE, lead.getContactDate()))); 241 242 String x; 243 if (lead.getConverted().booleanValue()) x = "Y"; else x="N"; 244 query.append(JDBC.quoteMore(x)); 245 query.append(JDBC.quoteMore(lead.getModifiedBy())); 246 if (DB_TYPE == Prefs.MYSQL) 247 query.append("CURRENT_DATE"); 248 else 249 query.append("SYSDATE"); 250 query.append(")"); 251 252 if (Prefs.DEBUG) LogWrite.write(query.toString()); 253 int rc = stmt.executeUpdate(query.toString()); 254 255 return lead.getPK(); 256 } 257 258 265 public final void deleteRow(Object obj) 266 throws SQLException { 267 268 long pkValue = ((Long )obj).longValue(); 269 270 String query = deleteQuery + pkValue; 271 272 Statement stmt = null; 273 274 try { 275 stmt = con.createStatement(); 276 if (Prefs.DEBUG) LogWrite.write(query); 277 stmt.executeUpdate(query); 278 } catch (SQLException e) { 279 throw e; 280 } finally { 281 try { 282 if (stmt != null) stmt.close(); 283 } catch (SQLException x) { } 284 } 285 } 286 287 295 public final ArrayList selectByCampaignRows(long camp_pk) 296 throws SQLException { 297 298 ArrayList leads = new ArrayList (); 299 300 Statement stmt = null; 301 ResultSet rs = null; 302 String query = selectByCampaignQuery + camp_pk; 303 304 try { 305 stmt = con.createStatement(); 306 if (Prefs.DEBUG) LogWrite.write(query); 307 rs = stmt.executeQuery(query); 308 309 int i; 310 Lead lead=null; 311 312 while (rs.next()) { 313 i=1; 314 lead = new Lead(); 315 lead.setPK(rs.getLong(i)); i++; 316 lead.setCampaignKey(camp_pk); 317 318 lead.getAddress().setPK(rs.getLong(i)); i++; 319 AddressDB addr = new AddressDB(DB_TYPE); 320 addr.setConnection(getConnection()); 321 Address a = (Address)addr.selectRow(new Long (lead.getAddress().getPK())); 322 lead.setAddress(a); 323 324 lead.setCompany(rs.getString(i)); i++; 325 lead.setInterestLevel(rs.getString(i)); i++; 326 327 lead.getContactedBy().setPK(rs.getLong(i)); i++; 328 SalesPersonDB spd = new SalesPersonDB(DB_TYPE); 329 spd.setConnection(getConnection()); 330 SalesPerson sp = (SalesPerson)spd.selectRow(new Long (lead.getContactedBy().getPK())); 331 lead.setContactedBy(sp); 332 333 lead.setContactDate(rs.getDate(i)); i++; 334 String x = rs.getString(i); i++; 335 Boolean val; 336 if (x.equals("Y")) val = new Boolean (true); else val = new Boolean (false); 337 lead.setConverted(val); 338 lead.setModifiedBy(rs.getString(i)); i++; 339 lead.setModifiedDate(rs.getDate(i)); 340 341 leads.add(lead); 342 } 343 } catch (SQLException e) { 344 throw e; 345 } finally { 346 try { 347 if (rs != null) rs.close(); 348 } catch (SQLException x) { throw x; } 349 try { 350 if (stmt != null) stmt.close(); 351 } catch (SQLException x) { throw x; } 352 } 353 354 return leads; 355 } 356 361 public final void truncate() 362 throws SQLException { 363 364 String query = "truncate table lead"; 365 366 Statement stmt = null; 367 try { 368 stmt = con.createStatement(); 369 if (Prefs.DEBUG) LogWrite.write(query); 370 stmt.executeUpdate(query); 371 } catch (SQLException e) { 372 throw e; 373 } finally { 374 try { if (stmt != null) stmt.close(); 375 } catch (SQLException x) { } 376 } 377 } 378 379 387 public final ArrayList selectAllRows(java.util.Date lastSyncDate) 388 throws SQLException { 389 390 Lead lead; 391 392 Statement stmt = null; 393 ResultSet rs = null; 394 ArrayList rows = new ArrayList (); 395 396 StringBuffer query = new StringBuffer (); 397 query.append(selectAllQuery); 398 if (lastSyncDate != null) { 399 query.append(" WHERE lead.modified_date > "); 400 query.append(DateUtils.formatDateTime(DB_TYPE, lastSyncDate)); 401 } 402 403 try { 404 stmt = con.createStatement(); 405 if (Prefs.DEBUG) LogWrite.write(query.toString()); 406 rs = stmt.executeQuery(query.toString()); 407 408 int i; 409 410 while (rs.next()) { 411 i=1; 412 lead = new Lead(); 413 lead.setPK(rs.getLong(i)); i++; 414 lead.setCampaignKey(rs.getLong(i)); i++; 415 lead.getAddress().setPK(rs.getLong(i)); i++; 416 AddressDB addr = new AddressDB(DB_TYPE); 417 addr.setConnection(getConnection()); 418 Address a = (Address)addr.selectRow(new Long (lead.getAddress().getPK())); 419 lead.setAddress(a); 420 lead.setCompany(rs.getString(i)); i++; 421 lead.setInterestLevel(rs.getString(i)); i++; 422 lead.getContactedBy().setPK(rs.getLong(i)); i++; 423 lead.setContactDate(rs.getDate(i)); i++; 424 String x = rs.getString(i); i++; 425 Boolean val; 426 if (x.equals("Y")) 427 val = new Boolean (true); 428 else 429 val = new Boolean (false); 430 lead.setConverted(val); 431 lead.setModifiedBy(rs.getString(i)); i++; 432 lead.setModifiedDate(rs.getDate(i)); 433 rows.add(lead); 434 } 435 } catch (SQLException e) { 436 throw e; 437 } finally { 438 try { 439 if (rs != null) rs.close(); 440 } catch (SQLException x) { throw x; } 441 try { 442 if (stmt != null) stmt.close(); 443 } catch (SQLException x) { throw x; } 444 } 445 return rows; 446 } 447 } 448 | Popular Tags |