/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package club.data;
import club.beans.AttendanceBean;
import club.beans.MemberBean;
import java.sql.*;
import java.util.*;
import java.util.logging.*;
/**
*
* @author Quang
*/
public class DataAttendance {
private Connection conn;
private Statement stmt;
private PreparedStatement psmt;
private ResultSet rs;
public List<AttendanceBean> getAllAttendances(String option,int id) {
List<AttendanceBean> list = new ArrayList();
String query;
if (option.equalsIgnoreCase("pay")){
query = "SELECT * FROM Attendance WHERE Type = 0 AND EventID ="+id;
} else if(option.equalsIgnoreCase("free")){
query = "SELECT * FROM Attendance WHERE TYPE != 0 AND EventID ="+id;
} else{
query = "SELECT * FROM Attendance WHERE EventID=" +id;
}
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
AttendanceBean bean = new AttendanceBean();
bean.setMemberID(rs.getInt("MemberID"));
bean.setEventID(rs.getInt("EventID"));
bean.setType(rs.getInt("Type"));
bean.setTransactionID(rs.getInt("TransactionID"));
list.add(bean);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataAttendance.class.getName()).log(Level.SEVERE, null, ex);
}
}
return list;
}
public List<MemberBean> getUnjoinedMember(int id) { // get all record in table Member and return in a List
List<MemberBean> list = new ArrayList();
String query = "SELECT * FROM Member WHERE MemberID NOT IN(SELECT MemberID FROM Attendance WHERE EventID = ?)";
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setInt(1, id);
rs = psmt.executeQuery();
while (rs.next()) {
MemberBean member = new MemberBean();
member.setMemberID(rs.getInt("MemberID"));
member.setMembershipTypeID(rs.getInt("MemberShipTypeID"));
member.setEmail(rs.getString("Email"));
member.setBalance(rs.getDouble("Balance"));
member.setStatus(rs.getInt("Status"));
member.setTitle(rs.getInt("Title"));
member.setFirstName(rs.getNString("FirstName"));
member.setLastName(rs.getNString("LastName"));
member.setNickname(rs.getNString("Nickname"));
member.setAddress(rs.getNString("Address"));
member.setDateOfBirth(rs.getDate("DateOfBirth"));
member.setDescription(rs.getNString("Description"));
member.setPhone(rs.getString("Phone"));
member.setCompany(rs.getString("Company"));
member.setJoinType(4);
list.add(member);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataAttendance.class.getName()).log(Level.SEVERE, null, ex);
}
}
return list;
}
public List<MemberBean> getJoinedMember(int id) { // get all record in table Member and return in a List
List<MemberBean> list = new ArrayList();
String query = "SELECT * FROM Member WHERE MemberID IN(SELECT MemberID FROM Attendance WHERE EventID = ?) and "
+ "Member.Status!=2";
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setInt(1, id);
rs = psmt.executeQuery();
while (rs.next()) {
MemberBean member = new MemberBean();
member.setMemberID(rs.getInt("MemberID"));
member.setMembershipTypeID(rs.getInt("MemberShipTypeID"));
member.setEmail(rs.getString("Email"));
member.setBalance(rs.getDouble("Balance"));
member.setStatus(rs.getInt("Status"));
member.setTitle(rs.getInt("Title"));
member.setFirstName(rs.getNString("FirstName"));
member.setLastName(rs.getNString("LastName"));
member.setNickname(rs.getNString("Nickname"));
member.setAddress(rs.getNString("Address"));
member.setDateOfBirth(rs.getDate("DateOfBirth"));
member.setDescription(rs.getNString("Description"));
member.setPhone(rs.getString("Phone"));
member.setCompany(rs.getString("Company"));
list.add(member);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataAttendance.class.getName()).log(Level.SEVERE, null, ex);
}
}
return list;
}
public boolean removeAttendance(int eventID,int memberID) { // return true if success , else false
boolean flag = false;
String query = "DELETE FROM Attendance WHERE MemberID=? AND EventID = ?";
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setInt(1, memberID);
psmt.setInt(2, eventID);
int i = psmt.executeUpdate();
if (i == 1) {
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataAttendance.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
public boolean makeAttendance(int eventID,int memberID,int type,int transID) { // return true if success , else false
boolean flag = false;
String query = "INSERT INTO Attendance VALUES(?,?,?,?)";
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setInt(1, memberID);
psmt.setInt(2, eventID);
psmt.setInt(3, type);
psmt.setInt(4, transID);
int i = psmt.executeUpdate();
if (i == 1) {
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataAttendance.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
public int getType(int EventID, int MemberID) {
int type = 4;
String query = "SELECT * FROM Attendance WHERE EventID = ? AND MemberID = ?";
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setInt(1, EventID);
psmt.setInt(2, MemberID);
rs = psmt.executeQuery();
rs.next();
type = rs.getInt("Type");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataAttendance.class.getName()).log(Level.SEVERE, null, ex);
}
}
return type;
}
public void discountEventFee(int id,Double fee,int admin)
{
String query="Select Attendance.MemberID,[Transaction.value],discount "
+ "from Attendance "
+ "left join Member on Attendance.MemberID=Member.MemberID "
+ "left join [Transaction] on Attendance.TransactionID=[Transaction].TransactionID "
+ "left join MembershipType on Member.membershiptypeid=MembershipType.MembershipTypeid "
+ "where Attendance.TransactionID IN ("
+ "Select TransacionID from Attendance WHERE EventID="+Integer.toString(id)+")";
try {
Vector<Integer> member=new Vector();
Vector<Double> value= new Vector();
Vector<Integer> discount=new Vector();
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
ResultSetMetaData meta = rs.getMetaData();
int total=0;
while(rs.next()){
member.add(rs.getInt(1));
value.add(rs.getDouble(2));
discount.add(rs.getInt(3));
total=total+1;
}
stmt.close();
rs.close();
for(int i=0;i<total;i++){
query = "UPDATE Member"
+ " SET Balance=BALANCE+?"
+ " WHERE MemberID IN("
+ "SELECT MemberID FROM [Transaction] WHERE TransactionID=?)";
psmt = conn.prepareStatement(query);
Double money=fee-fee/100*discount.get(i);
psmt.setDouble(1, money);
psmt.setInt(2, member.get(i));
psmt.executeUpdate();
int run=psmt.executeUpdate();
if(run==1){
psmt.close();
query = "INSERT INTO [Transaction] (MemberID,AdminID,Description,Value,Date)"
+ " VALUES(?,?,?,?,Getdate())";
psmt = conn.prepareStatement(query);
psmt.setInt(1,member.get(i));
psmt.setInt(2,admin);
psmt.setString(3, "refund some money becase event fee is discounted");
psmt.setDouble(4, money);
psmt.executeUpdate();
psmt.close();}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataAttendance.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}