// Returns all existing coupons of a certain type
@Override
public Collection<Coupon> getCouponByType(CouponType couponType)
throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
ConnectionCloseException {
// Establish connection
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e) {
throw new WaitingForConnectionInterrupted();
}
// Prepare ArrayList to return
ArrayList<Coupon> allCouponsFound = null;
// Prepare and execute statement
PreparedStatement statement = null;
ResultSet couponsFound = null;
// Prepare sql request
String sqlRequest;
try {
sqlRequest = "SELECT * FROM APP.COUPON WHERE COUPON_TYPE='" + couponType + "'";
statement = connection.prepareStatement(sqlRequest);
// Get all coupons in a ResultSet
couponsFound = statement.executeQuery();
// Prepare Collection
allCouponsFound = new ArrayList<Coupon>();
// Move all coupons from ResultSet to an ArrayList
while (couponsFound.next()) {
// Prepare temp coupon
Coupon tempCoupon = new Coupon();
tempCoupon.setId(couponsFound.getLong("ID"));
tempCoupon.setTitle(couponsFound.getString("TITLE"));
tempCoupon.setStartDate(couponsFound.getDate("START_DATE"));
tempCoupon.setEndDate(couponsFound.getDate("END_DATE"));
tempCoupon.setAmount(couponsFound.getInt("AMOUNT"));
tempCoupon.setType(CouponType.valueOf(couponsFound.getString("COUPON_TYPE")));
tempCoupon.setMessage(couponsFound.getString("MESSAGE"));
tempCoupon.setPrice(couponsFound.getDouble("PRICE"));
// Add coupon to the Collection
allCouponsFound.add(tempCoupon);
}
} catch (SQLException e) {
throw new ClosedConnectionStatementCreationException();
}
// Close connections
try {
couponsFound.close();
statement.close();
} catch (SQLException e) {
throw new ConnectionCloseException();
}
pool.returnConnection(connection);
// returns NULL, when no coupons found
return allCouponsFound;
}
// Returns collection of all existing coupons
@Override
public Collection<Coupon> getAllCoupons()
throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
ConnectionCloseException {
// Establish db connection
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e) {
throw new WaitingForConnectionInterrupted();
}
// Prepare and execute SELECT
Statement statement;
ArrayList<Coupon> coupons;
ResultSet rs;
try {
statement = connection.createStatement();
coupons = new ArrayList<Coupon>();
String sql = "SELECT * FROM APP.COUPON ";
rs = statement.executeQuery(sql);
while (rs.next()) {
Coupon coupon = new Coupon();
coupon.setAmount(rs.getInt("AMOUNT"));
coupon.setType(CouponType.valueOf(rs.getString("COUPON_TYPE")));
coupon.setEndDate(rs.getDate("END_DATE"));
coupon.setId(rs.getLong("ID"));
coupon.setImage(rs.getString("IMAGE"));
coupon.setMessage(rs.getString("MESSAGE"));
coupon.setPrice(rs.getDouble("PRICE"));
coupon.setTitle(rs.getString("TITLE"));
coupon.setStartDate(rs.getDate("START_DATE"));
coupons.add(coupon);
// System.out.println(coupon.toString());
coupons.add(coupon);
}
} catch (SQLException e) {
throw new ClosedConnectionStatementCreationException();
}
// Close connections
try {
rs.close();
statement.close();
} catch (SQLException e) {
throw new ConnectionCloseException();
}
pool.returnConnection(connection);
return coupons;
}
// Returns coupon by Title
@Override
public Coupon getCoupon(String title)
throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
ConnectionCloseException {
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e) {
throw new WaitingForConnectionInterrupted();
}
// Prepare and execute coupon
Statement statement;
ResultSet rs;
String sql;
Coupon coupon = null;
;
try {
statement = connection.createStatement();
// Prepare SQL message to get the Coupon by the id
sql = "SELECT * FROM APP.COUPON WHERE TITLE='" + title + "'";
// getting the values into a result set
rs = statement.executeQuery(sql);
coupon = new Coupon();
if (rs.next()) {
coupon.setAmount(rs.getInt("AMOUNT"));
coupon.setId(rs.getLong("ID"));
coupon.setImage(rs.getString("IMAGE"));
coupon.setMessage(rs.getString("MESSAGE"));
coupon.setPrice(rs.getDouble("PRICE"));
coupon.setTitle(rs.getString("TITLE"));
coupon.setEndDate(rs.getDate("END_DATE"));
coupon.setStartDate(rs.getDate("START_DATE"));
coupon.setType(CouponType.valueOf(rs.getString("COUPON_TYPE")));
}
} catch (SQLException e) {
throw new ClosedConnectionStatementCreationException();
}
// Close connections
try {
rs.close();
statement.close();
} catch (SQLException e) {
throw new ConnectionCloseException();
}
pool.returnConnection(connection);
return coupon;
}
// Update existing coupon
@Override
public void updateCoupon(Coupon coupon)
throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
ConnectionCloseException {
// Establish connection
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e) {
throw new WaitingForConnectionInterrupted();
}
PreparedStatement preparedStatement;
// Prepare and execute the update
try {
// Prepare SQL message to remove the Coupon
String updateSQL =
"UPDATE APP.COUPON SET "
+ "AMOUNT=?,MESSAGE=?,PRICE=?,TITLE=?,END_DATE=?,START_DATE=?,IMAGE=?,COUPON_TYPE=? "
+ "WHERE ID=?";
// Prepare statement
preparedStatement = connection.prepareStatement(updateSQL);
preparedStatement.setInt(1, coupon.getAmount());
preparedStatement.setString(2, coupon.getMessage());
preparedStatement.setDouble(3, coupon.getPrice());
preparedStatement.setString(4, coupon.getTitle());
preparedStatement.setDate(5, (java.sql.Date) coupon.getEndDate());
preparedStatement.setDate(6, (java.sql.Date) coupon.getStartDate());
preparedStatement.setString(7, coupon.getImage());
preparedStatement.setString(8, coupon.getType().name());
preparedStatement.setLong(9, coupon.getId());
// update the Coupon
preparedStatement.execute();
// Log
System.out.println(coupon.toString() + " was updated");
} catch (SQLException e) {
throw new ClosedConnectionStatementCreationException();
}
// Close Connections
try {
preparedStatement.close();
} catch (SQLException e) {
throw new ConnectionCloseException();
}
pool.returnConnection(connection);
}
// Removes relevant rows from CUSTOMER_COUPON, COMPANY_COUPON as well as coupon itself
@Override
public void removeCoupon(Coupon coupon)
throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
ConnectionCloseException {
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e) {
throw new WaitingForConnectionInterrupted();
}
// Get coupon ID from DB
Statement statement;
ResultSet idFound;
try {
statement = connection.createStatement();
String sqlRequest = "SELECT ID FROM APP.COUPON WHERE TITLE='" + coupon.getTitle() + "'";
idFound = statement.executeQuery(sqlRequest);
idFound.next();
// coupon.setId(idFound.getLong("ID"));
// Prepare message to remove from purchase history
String removeSQL = "DELETE FROM APP.CUSTOMER_COUPON WHERE COUPON_ID =" + coupon.getId();
// Remove coupon from purchase history
statement.execute(removeSQL);
// Prepare message to remove from company's coupons
removeSQL = "DELETE FROM APP.COMPANY_COUPON WHERE COUPON_ID=" + coupon.getId();
// Remove coupon from company
statement.execute(removeSQL);
// Prepare SQL message to remove the Coupon
removeSQL = "DELETE FROM APP.COUPON WHERE ID=" + coupon.getId();
// Remove the Coupon himself
statement.execute(removeSQL);
System.out.println(coupon.toString() + " was deleted");
} catch (SQLException e) {
throw new ClosedConnectionStatementCreationException();
}
// Close connections
try {
idFound.close();
statement.close();
} catch (SQLException e) {
throw new ConnectionCloseException();
}
pool.returnConnection(connection);
}
private static synchronized void returnConnect(Connection connection) {
if (connectionPool == null) {
connectionPool = new ConnectionPool(driverClassName, url, username, password);
connectionPool.setTestTable("dual");
if (initConn > 0 && incConn > 0 && maxConn > 0 && maxConn > initConn) {
connectionPool.setInitialConnections(initConn);
connectionPool.setIncrementalConnections(incConn);
;
connectionPool.setMaxConnections(maxConn);
;
}
try {
connectionPool.createPool();
} catch (Exception e) {
logger.fatal("can not create connection pool!system will exit..", e);
System.exit(1);
}
}
connectionPool.returnConnection(connection);
;
}
// Adds coupon to a coupons list
@Override
public void createCoupon(Coupon coupon)
throws FailedToCreateCouponException, WaitingForConnectionInterrupted {
// Get connection
Connection connection;
try {
connection = pool.getConnection();
} catch (GetConnectionWaitInteruptedException e1) {
throw new WaitingForConnectionInterrupted();
}
// Prepare SQL message to insert new company
String insertSQL =
"INSERT INTO APP.COUPON (IMAGE,PRICE,MESSAGE,COUPON_TYPE,AMOUNT,END_DATE,START_DATE,TITLE) VALUES"
+ "(?,?,?,?,?,?,?,?)";
PreparedStatement preparedStatement;
try {
preparedStatement = connection.prepareStatement(insertSQL);
preparedStatement.setInt(5, coupon.getAmount());
preparedStatement.setString(3, coupon.getMessage());
preparedStatement.setDouble(2, coupon.getPrice());
preparedStatement.setString(8, coupon.getTitle());
preparedStatement.setDate(6, (java.sql.Date) coupon.getEndDate());
preparedStatement.setDate(7, (java.sql.Date) coupon.getStartDate());
preparedStatement.setString(1, coupon.getImage());
preparedStatement.setString(4, coupon.getType().name());
// Execute prepared Statement
preparedStatement.executeUpdate();
// Close statement connection
preparedStatement.close();
} catch (SQLException e) {
throw new FailedToCreateCouponException();
} finally {
// close connection
pool.returnConnection(connection);
}
System.out.println(coupon.toString() + " was added to the table");
}
public void doGet(HttpServletRequest request, HttpServletResponse response) {
response.setContentType("text/html");
PrintWriter webPageOutput = null;
try {
webPageOutput = response.getWriter();
} catch (IOException error) {
Routines.writeToLog(servletName, "getWriter error : " + error, false, context);
}
HttpSession session = request.getSession();
session.setAttribute("redirect", request.getRequestURL() + "?" + request.getQueryString());
Connection database = null;
try {
database = pool.getConnection(servletName);
} catch (SQLException error) {
Routines.writeToLog(servletName, "Unable to connect to database : " + error, false, context);
}
if (Routines.loginCheck(true, request, response, database, context)) {
return;
}
String server = context.getInitParameter("server");
boolean liveSever = false;
if (server == null) {
server = "";
}
if (server.equals("live")) {
response.setHeader("Refresh", "60");
}
Routines.WriteHTMLHead(
"View System Log", // title
false, // showMenu
13, // menuHighLight
false, // seasonsMenu
false, // weeksMenu
false, // scores
false, // standings
false, // gameCenter
false, // schedules
false, // previews
false, // teamCenter
false, // draft
database, // database
request, // request
response, // response
webPageOutput, // webPageOutput
context); // context
webPageOutput.println("<CENTER>");
webPageOutput.println(
"<IMG SRC=\"../Images/Admin.gif\"" + " WIDTH='125' HEIGHT='115' ALT='Admin'>");
webPageOutput.println("</CENTER>");
pool.returnConnection(database);
webPageOutput.println(Routines.spaceLines(1));
Routines.tableStart(false, webPageOutput);
Routines.tableHeader("System Log", 0, webPageOutput);
Routines.tableDataStart(true, false, false, true, true, 0, 0, "scoresrow", webPageOutput);
boolean firstLine = true;
int numOfLines = 0;
try {
String file = context.getRealPath("/");
FileReader logFile = new FileReader(file + "/Data/log.txt");
BufferedReader logFileBuffer = new BufferedReader(logFile);
boolean endOfFile = false;
while (!endOfFile) {
String logFileText = logFileBuffer.readLine();
if (logFileText == null) {
endOfFile = true;
} else {
if (firstLine) {
firstLine = false;
} else {
webPageOutput.println(Routines.spaceLines(1));
}
numOfLines++;
webPageOutput.println(logFileText);
}
}
logFileBuffer.close();
} catch (IOException error) {
Routines.writeToLog(servletName, "Problem with log file : " + error, false, context);
}
Routines.tableDataEnd(false, true, true, webPageOutput);
Routines.tableEnd(webPageOutput);
if (numOfLines < 20) {
webPageOutput.println(Routines.spaceLines(20 - numOfLines));
}
Routines.WriteHTMLTail(request, response, webPageOutput);
}
public void doPost(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
Connection con = null;
// res.setContentType("text/html");
// res.setHeader("Cache-Control","no-store");
// PrintWriter out=res.getWriter();
HttpSession session = req.getSession(false);
String ad_user;
ad_user = (String) session.getValue("aduser");
java.util.Date time_comp = new java.util.Date(System.currentTimeMillis() - 20 * 60 * 1000);
java.util.Date accessed = new java.util.Date(session.getLastAccessedTime());
if (session == null || ad_user == null || accessed.before(time_comp)) {
session.invalidate();
// out.println("<H2>Your Session has expired </H2>");
// out.println("<a href='admin.htm'>Click Here</a> To Re-Login");
return;
}
String temp_list_id = req.getParameter("list_id");
int list_id;
try {
list_id = Integer.parseInt(temp_list_id);
} catch (Exception e) {
// out.println("<H2>NO List Found</H2>");
// out.println("<a href='javascript:history.go(-1)'>Click Here</a> to go back to previous
// page & try again");
return;
}
try {
con = pool.getConnection();
Statement stmt = con.createStatement();
File file = new File("temp.csv");
FileWriter fout = new FileWriter(file);
BufferedWriter bw = new BufferedWriter(fout);
// String line="";
bw.write("Name,Email\r\n");
ResultSet rs = stmt.executeQuery("Select * from list_member where list_id=" + list_id);
while (rs.next()) {
String name = rs.getString("member_name");
String email = rs.getString("member_email");
bw.write(name + "," + email + "\r\n");
}
bw.close();
fout.close();
rs.close();
// String fname=file.getName();
// String contentType = getServletContext().getMimeType(fname);
// System.out.println(contentType);
res.setContentType("application/csv");
ServletOutputStream out = res.getOutputStream();
FileInputStream fis = new FileInputStream("temp.csv");
byte[] buf = new byte[4 * 1024]; // 4K buffer
int bytesRead;
while ((bytesRead = fis.read(buf)) != -1) {
out.write(buf, 0, bytesRead);
}
out.close();
} catch (Exception e) {
try {
// out.println("<H2>An Error has occured: "+e.getMessage()+"</H2>");
e.printStackTrace();
// out.println("<br><br><a href='javascript:history.go(-1)'>Click Here</a> to go back to
// previous page & Try Again");
con.rollback();
} catch (Exception ignored) {
}
} finally {
if (con != null) pool.returnConnection(con);
// out.close();
}
}
