public List<Customer> getUniqueMailsFromTable(String tableName) throws Exception {
List<Customer> customers = new ArrayList<Customer>();
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
createMessageTable(tableName);
conn = getConnection();
stat = conn.createStatement();
rs =
stat.executeQuery(
"select name,emailId from customer where phoneNumber not in "
+ "(select emailId from "
+ tableName
+ "Mails where "
+ "isSend=0 ) and emailId!='' group by emailId ORDER BY id LIMIT 0, 1000");
while (rs.next()) {
Customer customer = new Customer();
customer.setName(rs.getString(Fields.name) == null ? "" : rs.getString(Fields.name));
customer.setEmailId(
rs.getString(Fields.emailId) == null ? "" : rs.getString(Fields.emailId));
customers.add(customer);
}
return customers;
} catch (Exception e) {
LotteryLogger.getInstance()
.setError("Error in getting unique mails count ," + e.getMessage());
throw new Exception("Error in getting unique mails count ," + e.getMessage());
} finally {
if (rs != null) rs.close();
if (stat != null) stat.close();
if (conn != null) conn.close();
}
}
public void insertMessageData(String tableName, Customer customer, boolean isSend, String status)
throws Exception {
Connection conn = null;
PreparedStatement prep = null;
try {
conn = getConnection();
conn.setAutoCommit(false);
prep =
conn.prepareStatement(
"insert or ignore into " + tableName + "Messages values(null,?,?,?,?,?,?,?);");
prep.setString(1, customer.getName());
prep.setString(2, customer.getPhoneNumber());
prep.setBoolean(3, isSend);
prep.setString(4, status);
prep.setString(5, Util.formatDate(new Date()));
prep.setString(6, Util.formatDate(new Date()));
prep.addBatch();
prep.executeBatch();
conn.commit();
} catch (Exception e) {
LotteryLogger.getInstance()
.setError(
"error in saving to database process customer, where table name is"
+ ((Util.isStringNullOrEmpty(tableName) ? "" : tableName))
+ e.getMessage());
if (conn != null) conn.rollback();
throw new Exception(
"error in saving to database process customer, where table name is"
+ ((Util.isStringNullOrEmpty(tableName) ? "" : tableName)));
} finally {
if (prep != null) prep.close();
if (conn != null) conn.close();
}
}
public SearchResult getCustomers(SearchFilter searchFilter) throws Exception {
SearchResult searchResult = new SearchResult();
List<Customer> customers = new ArrayList<Customer>();
String query = "select * from customer where id>=1 ";
if (!Util.isStringNullOrEmpty(searchFilter.getName()))
query += "and name= '" + searchFilter.getName() + "' ";
if (searchFilter.getFromdate() != null)
query +=
"and date >= '"
+ Util.formatDate(Util.getStartingdate(searchFilter.getFromdate()))
+ "' and date < '"
+ Util.getNextDate(searchFilter.getToDate())
+ "'";
if (!Util.isStringNullOrEmpty(searchFilter.getBumperName())
&& !Util.isStringNullOrEmpty(searchFilter.getBumperName())) {
if (searchFilter.isBothSelected())
query +=
" and (lotteryType='monthly' or bumperName='" + searchFilter.getBumperName() + "' )";
else {
query += " and bumperName='" + searchFilter.getBumperName() + "' ";
}
} else if (!Util.isStringNullOrEmpty(searchFilter.getBumperName())) {
query += " and bumperName='" + searchFilter.getBumperName() + "' ";
} else {
query += " and lotteryType='monthly'";
}
if (!Util.isStringNullOrEmpty(searchFilter.getPhoneNumber())) {
query += " and phoneNumber='" + searchFilter.getPhoneNumber() + "' ";
}
if (!Util.isStringNullOrEmpty(searchFilter.getTicketNumber())) {
query += " and ticketNumber='" + searchFilter.getTicketNumber() + "' ";
}
if (!Util.isStringNullOrEmpty(searchFilter.getEmailId())) {
query += " and emailId='" + searchFilter.getEmailId() + "' ";
}
if (!Util.isStringNullOrEmpty(searchFilter.getAddress())) {
query += " and address='" + searchFilter.getAddress() + "' ";
}
if (searchFilter.isMessageSend()) {
query += "and (isMessageSend=1 )";
}
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
conn = getConnection();
stat = conn.createStatement();
rs =
stat.executeQuery(
query
+ " ORDER BY id LIMIT "
+ searchFilter.getPageCount() * (searchFilter.getPageNumber() - 1)
+ ", "
+ (searchFilter.getPageCount()));
while (rs.next()) {
Customer customer = new Customer();
customer.setName(rs.getString(Fields.name) == null ? "" : rs.getString(Fields.name));
customer.setEmailId(
rs.getString(Fields.emailId) == null ? "" : rs.getString(Fields.emailId));
customer.setAddress(
rs.getString(Fields.address) == null ? "" : rs.getString(Fields.address));
customer.setBumperName(
rs.getString(Fields.bumperName) == null ? "" : rs.getString(Fields.bumperName));
customer.setDate(
rs.getString(Fields.date) == null
? Util.getDate("")
: Util.getDate(rs.getString(Fields.date)));
customer.setLotteryType(
rs.getString(Fields.lotteryType) == null ? "" : rs.getString(Fields.lotteryType));
customer.setPhoneNumber(
rs.getString(Fields.phoneNumber) == null ? "" : rs.getString(Fields.phoneNumber));
customer.setSerialNumber(rs.getInt(Fields.serialNumber));
customer.setTicketNumber(
rs.getString(Fields.ticketNumber) == null ? "" : rs.getString(Fields.ticketNumber));
customers.add(customer);
}
searchResult.totalResults = searchResultCount(searchFilter);
searchResult.customers = customers;
searchResult.searchFilter = searchFilter;
return searchResult;
} catch (Exception e) {
LotteryLogger.getInstance()
.setError("Error in getting filtered customers ," + e.getMessage());
throw new Exception("Error in getting filtered customers,");
} finally {
if (rs != null) rs.close();
if (stat != null) stat.close();
if (conn != null) conn.close();
}
}
public void insertCustomers(List<Customer> customers) throws Exception {
Connection conn = null;
PreparedStatement prep = null;
try {
init();
conn = getConnection();
conn.setAutoCommit(false);
for (Customer customer : customers) {
prep =
conn.prepareStatement(
"insert into customer values ( null,"
+ "?,"
+ "?,"
+ "?,"
+ " ?,"
+ "?,"
+ " ?,"
+ "?,"
+ "? ,"
+ "? ,"
+ "?,"
+ "?,"
+ "?,"
+ "? , ?, ?);");
prep.setInt(1, customer.getSerialNumber());
prep.setString(2, customer.getSeries());
prep.setString(3, customer.getTicketNumber());
prep.setString(4, customer.getName());
prep.setString(5, customer.getLotteryType());
prep.setString(6, customer.getBumperName());
prep.setString(7, customer.getPhoneNumber());
prep.setString(8, customer.getEmailId());
prep.setString(9, customer.getAddress());
prep.setBoolean(10, false);
prep.setBoolean(11, false);
prep.setString(12, Constants.backupMessage);
prep.setString(13, Util.formatDate(customer.getDate()));
prep.setString(14, Util.formatDate(new Date()));
prep.setString(15, Util.formatDate(new Date()));
prep.addBatch();
prep.executeBatch();
prep.close();
}
conn.commit();
} catch (Exception e) {
LotteryLogger.getInstance().setError("Error in inserting customers," + e.getMessage());
if (conn != null) conn.rollback();
throw new Exception("Error in inserting customer," + e.getMessage());
} finally {
if (prep != null) prep.close();
if (conn != null) conn.close();
}
}
public void savePendings(
List<Customer> customers, boolean isMessageSend, boolean isMailSend, String status)
throws Exception {
Connection conn = null;
PreparedStatement prest = null;
try {
init();
conn = getConnection();
conn.setAutoCommit(false);
for (Customer customer : customers) {
String sql = "delete from customer where serialNumber=" + customer.getSerialNumber();
Statement stat = conn.createStatement();
stat.execute(sql);
PreparedStatement prep =
conn.prepareStatement(
"insert into customer values ( null,"
+ "?,"
+ "?,"
+ "?,"
+ " ?,"
+ "?,"
+ " ?,"
+ "?,"
+ "? ,"
+ "? ,"
+ "?,"
+ "?,"
+ "?,"
+ "? , ?, ?,?) ;");
prep.setInt(1, customer.getSerialNumber());
prep.setString(2, customer.getSeries());
prep.setString(3, customer.getTicketNumber());
prep.setString(4, customer.getName());
prep.setString(5, customer.getLotteryType());
prep.setString(6, customer.getBumperName());
prep.setString(7, customer.getPhoneNumber());
prep.setString(8, customer.getEmailId());
prep.setString(9, customer.getAddress());
prep.setBoolean(10, isMessageSend);
prep.setBoolean(11, isMailSend);
prep.setString(12, status);
prep.setString(13, Util.formatDate(customer.getDate()));
prep.setString(14, Util.formatDate(new Date()));
prep.setString(15, Util.formatDate(new Date()));
prep.addBatch();
prep.executeBatch();
prep.close();
}
conn.commit();
} catch (Exception e) {
LotteryLogger.getInstance().setError("Error in inserting pending customers" + e.getMessage());
if (conn != null) conn.rollback();
throw new Exception("Error in inserting pending customers");
} finally {
if (conn != null) conn.close();
}
}