/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package club.data;
import club.beans.MemberBean;
import club.ulti.FormatConverter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.table.DefaultTableModel;
/**
*
* @author sior
*/
public class DataMember {
private static Connection conn;
private static Statement stmt;
private static PreparedStatement psmt;
private static ResultSet rs;
public List<MemberBean> getAllMembers() { // get all record in table Member and return in a List
List<MemberBean> list = new ArrayList();
String query = "SELECT * FROM Member";
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
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(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return list;
}
public List<MemberBean> getAllMembers(String text) { // get all record in table Member and return in a List
List<MemberBean> list = new ArrayList();
String query = "SELECT * FROM Event WHERE FirstName LIKE '%" + text + "%' OR LastName LIKE '%" + text + "%' OR Nickname LIKE '%" + text + "%' OR Email LIKE '%" + text + "%' OR MemberID LIKE '%" + text + "%'" ; //
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
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(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return list;
}
public static MemberBean getMemberByID(int id) { // find a member by ID and return as Bean
MemberBean member = null;
String query = "SELECT * FROM Member WHERE MemberID=" + id;
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
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"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return member;
}
public int getTotalMember() { // return number of members
int total = 0;
String query = "SELECT * FROM Member";
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
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 static boolean removeMemberByID(int id) { // return true if success , else false
boolean flag = false;
String query = "DELETE FROM Member WHERE MemberID=" + 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(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
public boolean insertMember(int memberShipTypeID, String email, double balance, int status, int title, String firstName, String lastName, String nickname, String address, Date date, String desc, String phone, String company) {
boolean flag = false;
String query = "INSERT INTO Member"
+ " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setInt(1, memberShipTypeID);
psmt.setString(2, email);
psmt.setDouble(3, balance);
psmt.setInt(4, status);
psmt.setInt(5, title);
psmt.setNString(6, firstName);
psmt.setNString(7, lastName);
psmt.setNString(8, nickname);
psmt.setNString(9, address);
psmt.setDate(10, FormatConverter.converUtilDateToSqlDate(date));
psmt.setNString(11, desc);
psmt.setString(12, phone);
psmt.setString(13, company);
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 static boolean updateMemberByID(int id, int memberShipTypeID, String email, double balance, int status, int title, String firstName, String lastName, String nickname, String address, Date date, String desc, String phone, String company) {
boolean flag = false;
String query = "UPDATE Member"
+ " SET MemberShipTypeID=?,Email=?,Balance=?"
+ ",Status=?"
+ ",Title=?"
+ ",FirstName=?"
+ ",LastName=?"
+ ",Nickname=?"
+ ",Address=?"
+ ",DateOfBirth=?"
+ ",Description=?"
+ ",Phone=?"
+ ",Company=?"
+ " WHERE MemberID=" + id;
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setInt(1, memberShipTypeID);
psmt.setString(2, email);
psmt.setDouble(3, balance);
psmt.setInt(4, status);
psmt.setInt(5, title);
psmt.setNString(6, firstName);
psmt.setNString(7, lastName);
psmt.setNString(8, nickname);
psmt.setNString(9, address);
psmt.setDate(10, FormatConverter.converUtilDateToSqlDate(date));
psmt.setNString(11, desc);
psmt.setString(12, phone);
psmt.setString(13, company);
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 static DefaultTableModel getAllMembersAsModel() { // get all records in table Member and return as model
DefaultTableModel model = null;
String query = "SELECT MemberID,Title,Email,Nickname,FirstName,LastName,TypeName,Balance,[Status] "
+ "FROM Member JOIN MembershipType \n"
+ "ON Member.MembershipTypeID = MembershipType.MembershipTypeID";
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
ResultSetMetaData meta = rs.getMetaData();
Vector header = new Vector();
Vector data = new Vector();
int count = meta.getColumnCount();
for (int i = 1; i <= count; i++) {
header.add(meta.getColumnName(i));
}
while (rs.next()) {
Vector row = new Vector();
for (int i = 1; i <= count; i++) {
row.add(rs.getObject(i));
}
data.add(row);
}
model = new DefaultTableModel(data, header){
@Override
public boolean isCellEditable(int row, int column) {
//all cells false
return false;
}
};
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return model;
}
public static DefaultTableModel getAllMembersAsModel(String text) { // get all records in table Member and return as model
DefaultTableModel model = null;
String query = "SELECT MemberID,Title,Email,Nickname,FirstName,LastName,TypeName,Balance,[Status] "
+ "FROM Member JOIN MembershipType \n"
+ "ON Member.MembershipTypeID = MembershipType.MembershipTypeID "
+ "WHERE FirstName LIKE '%" + text + "%' OR LastName LIKE '%" + text + "%' OR Nickname LIKE '%" + text + "%' OR Email LIKE '%" + text + "%' OR MemberID LIKE '%" + text + "%'";
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
ResultSetMetaData meta = rs.getMetaData();
Vector header = new Vector();
Vector data = new Vector();
int count = meta.getColumnCount();
for (int i = 1; i <= count; i++) {
header.add(meta.getColumnName(i));
}
while (rs.next()) {
Vector row = new Vector();
for (int i = 1; i <= count; i++) {
row.add(rs.getObject(i));
}
data.add(row);
}
model = new DefaultTableModel(data, header){
@Override
public boolean isCellEditable(int row, int column) {
//all cells false
return false;
}
};
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return model;
}
public static boolean checkMemberInType(int id){
boolean flag = false;
String query = "SELECT * FROM Member WHERE MembershipTypeID="+id;
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if(rs.next())
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 int getMaxID(){
int rt=0;
String query="SELECT TOP 1 MemberID from Member ORDER BY MemberID DESC";
try{
conn=ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
rs.next();
rt=rs.getInt("MemberID");
}catch(Exception e){
e.printStackTrace();
}
return rt;
}
public static boolean updateBalance(int id, double balance){
boolean flag = false;
String query = "UPDATE Member"
+ " SET Balance=BALANCE+?"
+ " WHERE MemberID=?";
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setDouble(1, balance);
psmt.setInt(2, id);
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 void checkfee(int admin)
{
Date da=new Date();
Date st=new Date();
Date end=new Date();
st.setDate(1);
int month=da.getMonth();
switch(month){
case 1:
end.setDate(31);
break;
case 2:{
if(da.getYear()%100==0)
{
int y=da.getYear();
y=y/100;
if(y%4==0){
end.setDate(29);
break;
}else{end.setDate(28);break;}
}else{
if(da.getYear()%4==0){end.setDate(29);break;}
else{end.setDate(28);break;}
}
}
case 3:end.setDate(31);
break;
case 4:end.setDate(30);
break;
case 5:end.setDate(31);
break;
case 6:end.setDate(30);
break;
case 7:end.setDate(31);
break;
case 8:end.setDate(31);
break;
case 9:end.setDate(30);
break;
case 10:end.setDate(31);
break;
case 11:end.setDate(30);
break;
case 12:end.setDate(31);
break;
}
try {
Vector<Integer> member=new Vector();
Vector<Double> fee= new Vector();
Vector<Integer> membertype=new Vector();
String query="Select MembershipTypeID,fee from MembershipType Where fee>0";
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
ResultSetMetaData meta = rs.getMetaData();
int total=0;
while(rs.next()){
membertype.add(rs.getInt(1));
fee.add(rs.getDouble(2));
total=total+1;
}
stmt.close();
rs.close();
for(int i=0;i<total;i++){
query="Select MemberID from Member where memberID not in (select memberID from [transaction] where isFeePerMonth=1)"
+ " And Status=1"
+ " And MembershipTypeID="+Integer.toString(membertype.get(i));
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
meta = rs.getMetaData();
int count=0;
member=new Vector();
while(rs.next()){
member.add(rs.getInt(1));
count=count+1;
}
for(int x=0;x<count;x++){
query = "UPDATE Member"
+ " SET Balance=BALANCE+?"
+ " WHERE MemberID=?";
psmt = conn.prepareStatement(query);
Double fe=0.0-fee.get(i);
psmt.setDouble(1, fe);
psmt.setInt(2, member.get(x));
int run=psmt.executeUpdate();
if(run!=-1){
psmt.close();
query = "INSERT INTO [Transaction] (MemberID,AdminID,Description,Value,Date,isfeepermonth)"
+ " VALUES(?,?,?,?,Getdate(),1)";
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, fe);
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);
}
}
}
}