/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package club.data;
import club.beans.EventBean;
import club.beans.MemberBean;
import club.ulti.FormatConverter;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.crypto.Data;
/**
*
* @author Quang
*/
public class DataEvent {
private Connection conn;
private Statement stmt;
private PreparedStatement psmt;
private ResultSet rs;
public List<EventBean> getAllEvents() { // get all record in table Member and return in a List
List<EventBean> list = new ArrayList();
String query = "SELECT * FROM Event";
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
EventBean event = new EventBean();
event.setEventID(rs.getInt("EventID"));
event.setDescription(rs.getString("Description"));
event.setDate(rs.getDate("Date"));
event.setDuration(rs.getFloat("Duration"));
event.setVenue(rs.getString("Venue"));
event.setEventType(rs.getInt("EventType"));
event.setFee(rs.getDouble("Fee"));
event.setCreatedTime(rs.getDate("CreatedTime"));
event.setAdminId(rs.getInt("AdminID"));
event.setStatus(rs.getInt("Status"));
event.setCost(rs.getDouble("Cost"));
event.setEventName(rs.getString("EventName"));
list.add(event);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return list;
}
public List<EventBean> getAllEvents(String text) { // get all record in table Member and return in a List
List<EventBean> list = new ArrayList();
String query = "SELECT * FROM Event WHERE EventName LIKE '%" + text + "%' OR Venue LIKE '%" + text + "%' OR Duration LIKE '%" + text + "%' OR Fee LIKE '%" + text + "%' OR EventID LIKE '%" + text + "%'" ; //
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
EventBean event = new EventBean();
event.setEventID(rs.getInt("EventID"));
event.setDescription(rs.getString("Description"));
event.setDate(rs.getDate("Date"));
event.setDuration(rs.getFloat("Duration"));
event.setVenue(rs.getString("Venue"));
event.setEventType(rs.getInt("EventType"));
event.setFee(rs.getDouble("Fee"));
event.setCreatedTime(rs.getDate("CreatedTime"));
event.setAdminId(rs.getInt("AdminID"));
event.setStatus(rs.getInt("Status"));
event.setCost(rs.getDouble("Cost"));
event.setEventName(rs.getString("EventName"));
list.add(event);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return list;
}
public EventBean getEventByID(int id) { // find a member by ID and return as Bean
EventBean event = new EventBean();
String query = "SELECT * FROM Event WHERE EventID=?";
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setInt(1, id);
rs = psmt.executeQuery();
if (rs.next()) {
event.setEventID(rs.getInt("EventID"));
event.setDescription(rs.getString("Description"));
event.setDate(rs.getDate("Date"));
event.setDuration(rs.getFloat("Duration"));
event.setVenue(rs.getString("Venue"));
event.setEventType(rs.getInt("EventType"));
event.setFee(rs.getDouble("Fee"));
event.setCreatedTime(rs.getDate("CreatedTime"));
event.setAdminId(rs.getInt("AdminID"));
event.setStatus(rs.getInt("Status"));
event.setCost(rs.getDouble("Cost"));
event.setEventName(rs.getString("EventName"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return event;
}
public int getTotalEvent() { // return number of events
int total = 999;
String query = "SELECT * FROM Event";
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()){
total = rs.getRow();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return total;
}
public boolean removeEventByID(int id) { // return true if success , else false
boolean flag = false;
String query = "DELETE FROM Event WHERE EventID=" + id;
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
int i = stmt.executeUpdate(query);
if (i == 1) {
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataEvent.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
public boolean isRemovable(int id) { // return true if success , else false
boolean flag = false;
String query = "SELECT COUNT(*) as total FROM Attendance WHERE EventID =" + id;
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
rs.next();
if (rs.getInt("total") == 0){
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataEvent.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
public boolean insertEvent(String description, java.util.Date date, float duration, String venue, int eventType, Double fee, int adminID, int status, double cost,String eventName) {
boolean flag = false;
String query = "INSERT INTO Event"
+ " VALUES(?,?,?,?,?,?,?,?,?,?,?)";
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setNString(1, description);
psmt.setDate(2, FormatConverter.converUtilDateToSqlDate(date));
psmt.setFloat(3, duration);
psmt.setString(4, venue);
psmt.setInt(5, eventType);
psmt.setDouble(6, fee);
java.util.Date now = new java.util.Date();
psmt.setTimestamp(7,new java.sql.Timestamp(now.getTime()));
psmt.setInt(8, adminID);
psmt.setInt(9, status);
psmt.setDouble(10, cost);
psmt.setNString(11, eventName);
int i = psmt.executeUpdate();
conn.commit();
if (i == 1) {
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
public boolean updateEventByID(int id,String description, java.util.Date date, float duration, String venue, int eventType, Double fee, java.util.Date createdTime, int adminID, int status, double cost,String eventName) {
boolean flag = false;
String query = "UPDATE Event"
+ " SET Description=?,Date=?,Duration=?"
+ ",Venue=?"
+ ",EventType=?"
+ ",Fee=?"
+ ",CreatedTime=?"
+ ",AdminID=?"
+ ",Status=?"
+ ",Cost=?"
+ ",EventName=?"
+ " WHERE EventID=" + id;
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setNString(1, description);
psmt.setDate(2, FormatConverter.converUtilDateToSqlDate(date));
psmt.setFloat(3, duration);
psmt.setString(4, venue);
psmt.setInt(5, eventType);
psmt.setDouble(6, fee);
psmt.setDate(7, FormatConverter.converUtilDateToSqlDate(createdTime));
psmt.setInt(8, adminID);
psmt.setInt(9, status);
psmt.setDouble(10, cost);
psmt.setNString(11, eventName);
int i = psmt.executeUpdate();
if (i == 1) {
flag = true;
}
conn.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
public DataEvent() {
super();
}
}