/*
* Created on May 8, 2004
*
* To change the template for this generated file go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
package net.reliableresponse.notification.broker.impl.sql;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Vector;
import net.reliableresponse.notification.NotSupportedException;
import net.reliableresponse.notification.broker.BrokerFactory;
import net.reliableresponse.notification.broker.UserMgmtBroker;
import net.reliableresponse.notification.device.Device;
import net.reliableresponse.notification.device.DeviceSetting;
import net.reliableresponse.notification.license.LicenseFile;
import net.reliableresponse.notification.usermgmt.Account;
import net.reliableresponse.notification.usermgmt.Group;
import net.reliableresponse.notification.usermgmt.Member;
import net.reliableresponse.notification.usermgmt.OnCallGroup;
import net.reliableresponse.notification.usermgmt.OnCallSchedule;
import net.reliableresponse.notification.usermgmt.User;
/**
* @author drig
*
* To change the template for this generated type comment go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
public abstract class GenericSQLUserMgmtBroker implements UserMgmtBroker {
public abstract Connection getConnection();
/*
* (non-Javadoc)
*
* @see net.reliableresponse.notification.broker.UserMgmtBroker#addUser(net.reliableresponse.notification.usermgmt.User)
*/
public String addUser(User user) throws NotSupportedException {
// Check to see if this is allowed
int maxUsers = LicenseFile.getInstance().getMaxUsers();
BrokerFactory.getLoggingBroker().logDebug("maxUsers="+maxUsers);
if (maxUsers>0) {
int currentUsers = getNumUsers();
if (currentUsers>=maxUsers) {
BrokerFactory.getLoggingBroker().logWarn("Ignoring max users setting");
if (1==0)
throw new NotSupportedException("Maximum number of licensed users reached");
}
}
String sql = "INSERT INTO member(uuid, type, firstname, lastname, email, deleted, cached, vacation) values (?, '1', ?, ?, ?, 'N', ?, ?)";
String uuid = user.getUuid();
PreparedStatement stmt = null;
Connection connection = getConnection();
// Add the user's base information
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, uuid);
String firstName = user.getFirstName();
if (firstName == null) firstName = "";
String lastName = user.getLastName();
if (lastName == null) lastName = "";
String email = user.getEmailAddress();
if (email == null) email = "";
stmt.setString(2, firstName);
stmt.setString(3, lastName);
stmt.setString(4, email);
stmt.setString(5, user.isInPermanentCache()?"Y":"N");
stmt.setString(6, user.isOnVacation()?"Y":"N");
stmt.executeUpdate();
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
Hashtable information = user.getAllInformation();
Enumeration names = information.keys();
// Add the user's information
try {
while (names.hasMoreElements()) {
sql = "INSERT INTO userinformation(member, name, value) values (?, ?, ?)";
String name = (String) names.nextElement();
String value = (String) information.get(name);
if (value == null) value = "";
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, uuid);
stmt.setString(2, name);
stmt.setString(3, value);
stmt.executeUpdate();
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
// Add the devices
Device[] devices = user.getDevices();
try {
for (int i = 0; i < devices.length; i++) {
addDeviceToUser(user, connection, devices[i]);
}
} finally {
try {
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return uuid;
}
private void deleteDevice (User user, Connection connection, String device) {
String sql;
PreparedStatement stmt = null;
BrokerFactory.getLoggingBroker().logDebug(
"Deleting device " + device + " from user " + user);
try {
sql = "DELETE FROM device WHERE uuid=? AND member=?";
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, device);
stmt.setString(2, user.getUuid());
stmt.executeUpdate();
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
}
/**
* @param user
* @param connection
* @param device
*/
private void addDeviceToUser(User user, Connection connection, Device device) {
String sql;
PreparedStatement stmt = null;
BrokerFactory.getLoggingBroker().logDebug(
"Adding " + device + " to user " + user);
try {
sql = "INSERT INTO device(uuid, type, member) SELECT ?, uuid, ? FROM devicetype WHERE classname=?";
BrokerFactory.getLoggingBroker().logDebug(
"INSERT INTO device(uuid, type, member) SELECT '"
+ device.getUuid() + "', uuid, '"
+ user.getUuid()
+ "' FROM devicetype WHERE classname='"
+ device.getClass().getName() + "'");
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, device.getUuid());
stmt.setString(2, user.getUuid());
stmt.setString(3, device.getClass().getName());
stmt.execute();
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
DeviceSetting[] settings = device.getAvailableSettings();
Hashtable info = device.getSettings();
for (int settingNum = 0; settingNum < settings.length; settingNum++) {
try {
sql = "INSERT INTO devicesetting(device, name, value) VALUES(?, ?, ?)";
BrokerFactory.getLoggingBroker().logDebug("Adding device setting "+settings[settingNum].getName()+":"+
info.get(settings[settingNum].getName())+
" to "+user+"'s device "+device);
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, device.getUuid());
stmt.setString(2, settings[settingNum].getName());
stmt.setString(3, (String) info
.get(settings[settingNum].getName()).toString());
stmt.executeUpdate();
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
}
}
private void deleteDeviceFromUser(User user, Connection connection, String deviceUuid) {
String sql;
PreparedStatement stmt = null;
try {
BrokerFactory.getLoggingBroker().logDebug("Deleting device with uuid "+deviceUuid+" from "+user);
sql = "DELETE FROM device WHERE uuid=?";
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, deviceUuid);
stmt.executeUpdate();
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
}
public void updateUsersDevice(User user, Connection connection, Device device) {
String sql;
PreparedStatement stmt = null;
BrokerFactory.getLoggingBroker().logDebug(
"Updating " + device + " for user " + user);
DeviceSetting[] settings = device.getAvailableSettings();
Hashtable info = device.getSettings();
for (int settingNum = 0; settingNum < settings.length; settingNum++) {
try {
BrokerFactory.getLoggingBroker().logDebug("Setting "+device+"'s "+settings[settingNum].getName()+" to "+
info.get(settings[settingNum].getName()).toString());
sql = "UPDATE devicesetting SET value=? WHERE device=? AND name=?";
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, info.get(settings[settingNum].getName()).toString());
stmt.setString(2, device.getUuid());
stmt.setString(3, settings[settingNum].getName());
stmt.executeUpdate();
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
}
}
/*
* (non-Javadoc)
*
* @see net.reliableresponse.notification.broker.UserMgmtBroker#getUsers(int,
* int, net.reliableresponse.notification.usermgmt.User[])
*/
public int getUsers(int pageSize, int pageNum, User[] users) {
int count = 0;
if (users.length < pageSize) {
pageSize = users.length;
}
BrokerFactory.getLoggingBroker().logDebug("Getting users, pagesize="+pageSize+", pageNum="+pageNum);
String sql = "SELECT uuid, firstname, lastname, email, cached, vacation FROM member WHERE deleted='N' AND type='1' ORDER BY lastName LIMIT ? OFFSET ?";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setInt(1, pageSize);
stmt.setInt(2, pageNum * pageSize);
rs = stmt.executeQuery();
while (rs.next()) {
User user = new User();
user.setUuid(rs.getString(1));
user.setFirstName(rs.getString(2));
user.setLastName(rs.getString(3));
user.setEmailAddress(rs.getString(4));
user.setInPermanentCache(rs.getString(5).equalsIgnoreCase("Y"));
user.setOnVacation(rs.getString(6).equalsIgnoreCase("Y"));
users[count] = user;
count++;
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
try {
for (int usernum = 0; usernum < count; usernum++) {
User user = (User) users[usernum];
//getUserInformation(user, connection);
//getUserDevices(user, connection);
user.setAutocommit(true);
}
} finally {
try {
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return pageSize;
}
/*
* (non-Javadoc)
*
* @see net.reliableresponse.notification.broker.UserMgmtBroker#getUsersLike(int,
* int, java.lang.String,
* net.reliableresponse.notification.usermgmt.User[])
*/
public int getUsersLike(int pageSize, int pageNum, String substring,
User[] users) {
int count = 0;
if (users.length < pageSize) {
pageSize = users.length;
}
String sql = "SELECT uuid, firstname, lastname, email, cached, vacation FROM member WHERE deleted='N' AND type='1' AND "+
"(lower(firstname) like ? OR lower(lastname) like ? or lower(email) like ?) ORDER BY lastName LIMIT ? OFFSET ?";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
String search = "%" + substring.toLowerCase() + "%";
BrokerFactory.getLoggingBroker().logDebug("Searching for "+search);
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, search);
stmt.setString(2, search);
stmt.setString(3, search);
stmt.setInt(4, pageSize);
stmt.setInt(5, pageNum * pageSize);
rs = stmt.executeQuery();
while (rs.next()) {
User user = new User();
user.setUuid(rs.getString(1));
user.setFirstName(rs.getString(2));
user.setLastName(rs.getString(3));
user.setEmailAddress(rs.getString(4));
user.setInPermanentCache(rs.getString(5).equalsIgnoreCase("Y"));
user.setOnVacation(rs.getString(6).equalsIgnoreCase("Y"));
users[count] = user;
BrokerFactory.getLoggingBroker().logDebug("Loaded user like "+user);
count++;
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
try {
for (int usernum = 0; usernum < count; usernum++) {
User user = (User) users[usernum];
//getUserInformation(user, connection);
//getUserDevices(user, connection);
user.setAutocommit(true);
}
} finally {
try {
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return pageSize;
}
public User[] getUsersWithDeviceType(String deviceClass) {
String sql = "SELECT m.uuid, m.firstname, m.lastname, m.email, m.cached, m.vacation FROM member m, device d, devicetype t "+
"WHERE deleted='N' AND m.uuid=d.member AND d.type=t.uuid AND t.classname=?";
Vector members = new Vector();
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString (1, deviceClass);
rs = stmt.executeQuery();
while (rs.next()) {
User user = new User();
user.setUuid(rs.getString(1));
user.setFirstName(rs.getString(2));
user.setLastName(rs.getString(3));
user.setEmailAddress(rs.getString(4));
user.setInPermanentCache(rs.getString(5).equalsIgnoreCase("Y"));
user.setOnVacation(rs.getString(6).equalsIgnoreCase("Y"));
members.addElement(user);
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
try {
for (int usernum = 0; usernum < members.size(); usernum++) {
User user = (User)members.elementAt(usernum);
//getUserInformation(user, connection);
//getUserDevices(user, connection);
user.setAutocommit(true);
}
} finally {
try {
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return (User[])members.toArray(new User[0]);
}
public int getUuids(int pageSize, int pageNum, String[] uuids) {
int count = 0;
if (uuids.length < pageSize) {
pageSize = uuids.length;
}
String sql = "SELECT uuid FROM member WHERE deleted='N' AND type='1' ORDER BY lastName LIMIT ? OFFSET ?";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setInt(1, pageSize);
stmt.setInt(2, pageNum * pageSize);
rs = stmt.executeQuery();
while (rs.next()) {
User user = new User();
uuids[count] = rs.getString(1);
count++;
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return count;
}
public int getUuidsLike(int pageSize, int pageNum, String substring,
String[] uuids) {
int count = 0;
if (uuids.length < pageSize) {
pageSize = uuids.length;
}
String like = "%" + substring.toLowerCase() + "%";
BrokerFactory.getLoggingBroker().logDebug("Looking for a uuid like "+like);
String sql = "SELECT uuid FROM member WHERE deleted='N' AND type='1' AND (lower(firstname) like ? OR lower(lastname) like ? or lower(email) like ?) ORDER BY lastName LIMIT ? OFFSET ?";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, like);
stmt.setString(2, like);
stmt.setString(3, like);
stmt.setInt(4, pageSize);
stmt.setInt(5, pageNum * pageSize);
rs = stmt.executeQuery();
while (rs.next()) {
uuids[count] = rs.getString(1);
count++;
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return count;
}
/*
* (non-Javadoc)
*
* @see net.reliableresponse.notification.broker.UserMgmtBroker#getNumUsers()
*/
public int getNumUsers() {
String sql = "SELECT COUNT(*) FROM member WHERE deleted='N' AND type='1'";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
rs = stmt.executeQuery();
if (rs.next()) {
return (rs.getInt(1));
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return 0;
}
/*
* (non-Javadoc)
*
* @see net.reliableresponse.notification.broker.UserMgmtBroker#getNumUsersLike(java.lang.String)
*/
public int getNumUsersLike(String substring) {
String sql = "SELECT COUNT(*) FROM member WHERE deleted='N' AND type='1' AND (lower(firstname) like ? OR lower(lastname) like ? or lower(email) like ?)";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
String like = "%" + substring.toLowerCase() + "%";
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, like);
stmt.setString(2, like);
stmt.setString(3, like);
rs = stmt.executeQuery();
if (rs.next()) {
return (rs.getInt(1));
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return 0;
}
public User[] getUsersWithEmailAddress(String address) {
String sql = "SELECT m.uuid FROM member m, devicesetting ds, device d, devicetype dt "+
" WHERE m.deleted='N' AND dt.classname='net.reliableresponse.notification.device.EmailDevice' "+
" AND d.type=dt.uuid AND d.member=m.uuid AND ds.device=d.uuid AND ds.name='Address'" +
" AND ds.value=? AND m.type='1'";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
Vector users = new Vector();
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, address);
rs = stmt.executeQuery();
if (rs.next()) {
String uuid = rs.getString(1);
User user = getUserByUuid(uuid);
users.addElement(user);
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return (User[])(users.toArray(new User[0]));
}
/*
* (non-Javadoc)
*
* @see net.reliableresponse.notification.broker.UserMgmtBroker#getUserByEmailAddress(java.lang.String)
*/
public User getUserByEmailAddress(String emailAddress) {
String sql = "SELECT uuid, firstname, lastname, email, cached, vacation FROM member WHERE deleted='N' AND LOWER(email)=? and type='1'";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
User user = new User();
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, emailAddress.toLowerCase());
rs = stmt.executeQuery();
if (rs.next()) {
user.setUuid(rs.getString(1));
String firstName =rs.getString(2);
if (firstName == null) firstName = "";
user.setFirstName(firstName);
String lastName =rs.getString(3);
if (lastName == null) lastName = "";
user.setLastName(lastName);
user.setEmailAddress(rs.getString(4));
user.setInPermanentCache(rs.getString(5).equalsIgnoreCase("Y"));
user.setOnVacation(rs.getString(6).equalsIgnoreCase("Y"));
} else {
return null;
}
//getUserInformation(user, connection);
//getUserDevices(user, connection);
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
user.setAutocommit(true);
return user;
}
public User getUserByInformation(String key, String value) {
String sql = "SELECT m.uuid, m.firstname, m.lastname, m.email, m.cached, m.vacation FROM member m, userinformation i WHERE deleted='N' AND m.type='1' AND i.name=? AND i.value=? AND m.uuid=i.member";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
User user = new User();
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, key);
stmt.setString(2, value);
rs = stmt.executeQuery();
if (rs.next()) {
user.setUuid(rs.getString(1));
String firstName =rs.getString(2);
if (firstName == null) firstName = "";
user.setFirstName(firstName);
String lastName =rs.getString(3);
if (lastName == null) lastName = "";
user.setLastName(lastName);
user.setEmailAddress(rs.getString(4));
user.setInPermanentCache(rs.getString(5).equalsIgnoreCase("Y"));
user.setOnVacation(rs.getString(6).equalsIgnoreCase("Y"));
} else {
return null;
}
//getUserInformation(user, connection);
//getUserDevices(user, connection);
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
user.setAutocommit(true);
return user;
}
public User[] getUsersWithInformationLike(String key, String value) {
String sql = "SELECT m.uuid, m.firstname, m.lastname, m.email, m.cached, m.vacation FROM member m, userinformation i WHERE m.deleted='N' AND m.type='1' AND i.name=? AND lower(i.value) like ? AND m.uuid=i.member";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
Vector users = new Vector();
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, key);
stmt.setString(2, "%"+value.toLowerCase()+"%");
rs = stmt.executeQuery();
while (rs.next()) {
User user = new User();
user.setUuid(rs.getString(1));
String firstName =rs.getString(2);
if (firstName == null) firstName = "";
user.setFirstName(firstName);
String lastName =rs.getString(3);
if (lastName == null) lastName = "";
user.setLastName(lastName);
user.setEmailAddress(rs.getString(4));
user.setInPermanentCache(rs.getString(5).equalsIgnoreCase("Y"));
user.setOnVacation(rs.getString(6).equalsIgnoreCase("Y"));
user.setAutocommit(true);
users.addElement(user);
}
//getUserInformation(user, connection);
//getUserDevices(user, connection);
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return (User[])users.toArray(new User[0]);
}
/*
* (non-Javadoc)
*
compi
* @see net.reliableresponse.notification.broker.UserMgmtBroker#updateUser(net.reliableresponse.notification.usermgmt.User)
*/
public void updateUser(User user) throws NotSupportedException {
//BrokerFactory.getLoggingBroker().logDebug("Updating " + user);
// Update the base user object
String sql = "UPDATE member set firstname=?, lastname=?, email=?, cached=?, vacation=? WHERE uuid=?";
//BrokerFactory.getLoggingBroker().logDebug("Updating user with uuid "+user.getUuid());
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, user.getFirstName());
stmt.setString(2, user.getLastName());
stmt.setString(3, user.getEmailAddress());
stmt.setString(4, user.isInPermanentCache()?"Y":"N");
stmt.setString(5, user.isOnVacation()?"Y":"N");
stmt.setString(6, user.getUuid());
stmt.executeUpdate();
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
// Update all the user information
Hashtable information = user.getAllInformation();
Enumeration names = information.keys();
try {
while (names.hasMoreElements()) {
sql = "SELECT value FROM userinformation WHERE member=? and name=?";
String name = (String) names.nextElement();
String value = (String) information.get(name);
BrokerFactory.getLoggingBroker().logDebug(
"Updating " + user + "'s " + name + " to " + value);
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, user.getUuid());
stmt.setString(2, name);
rs = stmt.executeQuery();
if (rs.next()) {
String testValue = rs.getString(1);
if (testValue == null) testValue = "";
BrokerFactory.getLoggingBroker().logDebug("Only updating if "+testValue+
" doesn't equal "+value);
if (!testValue.equals(value)) {
sql = "UPDATE userinformation set value=? WHERE member=? AND name=?";
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, value);
stmt.setString(2, user.getUuid());
stmt.setString(3, name);
stmt.executeUpdate();
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
}
} else {
sql = "INSERT INTO userinformation (member, name, value) VALUES (?, ?, ?)";
BrokerFactory.getLoggingBroker().logDebug("SQL: INSERT INTO userinformation (member, name, value) VALUES (?, ?, ?)");
BrokerFactory.getLoggingBroker().logDebug(user.getUuid());
BrokerFactory.getLoggingBroker().logDebug(name);
BrokerFactory.getLoggingBroker().logDebug(value);
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, user.getUuid());
stmt.setString(2, name);
stmt.setString(3, value);
stmt.executeUpdate();
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
System.exit(0);
} finally {
try {
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
}
}
// Add, update or delete devices
Vector usersDevices = new Vector();
Device[] temp = user.getDevices();
for (int i = 0; i < temp.length; i++) {
usersDevices.addElement(temp[i].getUuid());
}
Vector existingUuids = new Vector();
// Gather all the uuids in the database
sql = "SELECT uuid FROM device WHERE member=?";
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, user.getUuid());
rs = stmt.executeQuery();
while (rs.next()) {
String existingUuid = rs.getString(1);
BrokerFactory.getLoggingBroker().logDebug("Existing uuid="+existingUuid);
existingUuids.addElement(existingUuid);
}
// Find any devices that need to be deleted
for (int i = 0; i < existingUuids.size(); i++) {
if (!usersDevices.contains((String)existingUuids.elementAt(i))) {
deleteDevice (user, connection, (String)existingUuids.elementAt(i));
}
}
// Find any devices that need to be added or updated
for (int i = 0; i < usersDevices.size(); i++) {
BrokerFactory.getLoggingBroker().logDebug("Adding or updating device "+usersDevices.elementAt(i));
if (!(existingUuids.contains((String)usersDevices.elementAt(i)))) {
// Add the device
BrokerFactory.getLoggingBroker().logDebug("Adding device "+usersDevices.elementAt(i));
addDeviceToUser(user, connection, user.getDeviceWithUuid((String)usersDevices.elementAt(i)));
} else {
// update the device
BrokerFactory.getLoggingBroker().logDebug("Updating device "+usersDevices.elementAt(i));
updateUsersDevice(user, connection, user.getDeviceWithUuid((String)usersDevices.elementAt(i)));
}
}
// Find any devices that need to be deleted
for (int i = 0; i < existingUuids.size(); i++) {
if (!(usersDevices.contains((String)existingUuids.elementAt(i)))) {
deleteDeviceFromUser (user, connection, (String)existingUuids.elementAt(i));
}
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
}
public void undeleteUser (User user) {
BrokerFactory.getLoggingBroker().logDebug("Un deleteing " + user);
String sql = "UPDATE member SET deleted='N' WHERE uuid=?";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, user.getUuid());
stmt.executeUpdate();
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
}
public User getDeletedUser (String firstname, String lastname) {
String sql = "SELECT uuid, firstname, lastname, email, cached, vacation FROM member WHERE deleted='Y' AND firstname=? AND lastname=? and type='1'";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
User user = new User();
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, firstname);
stmt.setString(2, lastname);
rs = stmt.executeQuery();
if (rs.next()) {
user.setUuid(rs.getString(1));
String firstName =rs.getString(2);
if (firstName == null) firstName = "";
user.setFirstName(firstName);
String lastName =rs.getString(3);
if (lastName == null) lastName = "";
user.setLastName(lastName);
user.setEmailAddress(rs.getString(4));
user.setInPermanentCache(rs.getString(5).equalsIgnoreCase("Y"));
user.setOnVacation(rs.getString(6).equalsIgnoreCase("Y"));
user.setDeleted(true);
} else {
return null;
}
//getUserInformation(user, connection);
//getUserDevices(user, connection);
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
user.setAutocommit(true);
return user;
}
public void deleteUser(User user) throws NotSupportedException {
BrokerFactory.getLoggingBroker().logDebug("Deleteing " + user);
String sql = "UPDATE member SET deleted='Y', deletedon=? WHERE uuid=?";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
stmt.setString(2, user.getUuid());
stmt.executeUpdate();
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
}
public int purgeUsersBefore(Date before) {
BrokerFactory.getLoggingBroker().logDebug("Purging users before "+before);
String sql = "DELETE FROM member WHERE deleted='Y' AND type='1' AND deletedon<?";
PreparedStatement stmt = null;
Connection connection = getConnection();
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setTimestamp(1, new Timestamp(before.getTime()));
int numDeleted = stmt.executeUpdate();
return numDeleted;
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return -1;
}
public String[] getDeletedUuidsBefore (Date before) {
String sql = "SELECT uuid FROM member WHERE deleted='Y' AND type='1' AND deletedOn<?";
Vector uuids = new Vector();
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setTimestamp(1, new Timestamp(before.getTime()));
rs = stmt.executeQuery();
while (rs.next()) {
String uuid = rs.getString(1);
uuids.addElement(uuid);
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return (String[])uuids.toArray(new String[0]);
}
/*
* (non-Javadoc)
*
* @see net.reliableresponse.notification.broker.UserMgmtBroker#getUserByUuid(java.lang.String)
*/
public User getUserByUuid(String uuid) {
String sql = "SELECT firstname, lastname, email, deleted, cached, vacation FROM member WHERE uuid=? and type='1'";
User user = new User();
user.setUuid(uuid);
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, uuid);
rs = stmt.executeQuery();
if (rs.next()) {
String first = rs.getString(1);
if (first == null) first = "";
user.setFirstName(first);
String last = rs.getString(2);
if (last == null) last = "";
user.setLastName(last);
user.setEmailAddress(rs.getString(3));
user.setDeleted (rs.getString(4).equalsIgnoreCase("Y"));
user.setInPermanentCache(rs.getString(5).equalsIgnoreCase("Y"));
user.setOnVacation(rs.getString(6).equalsIgnoreCase("Y"));
} else {
return null;
}
//getUserInformation(user, connection);
//getUserDevices(user, connection);
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
return null;
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
user.setAutocommit(true);
return user;
}
public String[] getUuidsInPermanentCache() {
String sql = "SELECT uuid FROM member WHERE deleted='N' AND type='1' AND cached='Y'";
Vector uuids = new Vector();
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
rs = stmt.executeQuery();
while (rs.next()) {
String uuid = rs.getString(1);
uuids.addElement(uuid);
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return (String[])uuids.toArray(new String[0]);
}
/**
* @param user
* @param connection
*/
public void getUserInformation(User user) {
Connection connection = getConnection();
getUserInformation(user, connection);
try {
connection.close();
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
}
}
public void getUserInformation(User user, Connection connection) {
BrokerFactory.getLoggingBroker().logDebug("Getting information for "+user);
boolean origauto = user.getAutocommit();
user.setAutocommit(false);
String sql;
PreparedStatement stmt = null;
ResultSet rs = null;
sql = "SELECT name, value FROM userinformation WHERE member=?";
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, user.getUuid());
rs = stmt.executeQuery();
while (rs.next()) {
user.setInformation(rs.getString(1), rs.getString(2));
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
user.setAutocommit(origauto);
}
}
/**
* @param uuid
* @param user
* @param connection
*/
public void getUserDevices(User user) {
Connection connection = getConnection();
getUserDevices(user, connection);
try {
connection.close();
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} }
protected void getUserDevices(User user, Connection connection) {
BrokerFactory.getLoggingBroker().logDebug("Getting devices for "+user);
boolean origauto = user.getAutocommit();
user.setAutocommit(false);
String sql;
PreparedStatement stmt = null;
ResultSet rs = null;
sql = "SELECT d.uuid, t.classname FROM device d, devicetype t WHERE d.member=? AND t.uuid=d.type";
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, user.getUuid());
rs = stmt.executeQuery();
while (rs.next()) {
try {
Hashtable info = new Hashtable();
String deviceUuid = rs.getString(1);
Device device = (Device) Class.forName(rs.getString(2))
.newInstance();
device.setUuid(deviceUuid);
DeviceSetting[] settings = device.getAvailableSettings();
for (int settingNum = 0; settingNum < settings.length; settingNum++) {
PreparedStatement stmt2 = null;
ResultSet rs2 = null;
sql = "SELECT value FROM devicesetting WHERE device=? AND name=?";
try {
stmt2 = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt2.setString(1, device.getUuid());
stmt2.setString(2, settings[settingNum].getName());
rs2 = stmt2.executeQuery();
if (rs2.next()) {
String value = rs2.getString(1);
if (value != null) {
info.put(settings[settingNum].getName(),
value);
}
}
} catch (SQLException e2) {
BrokerFactory.getLoggingBroker().logError(e2);
} finally {
try {
if (rs2 != null)
rs2.close();
if (stmt2 != null)
stmt2.close();
} catch (SQLException e3) {
BrokerFactory.getLoggingBroker().logError(e3);
}
}
}
device.initialize(info);
user.addDevice(device);
} catch (InstantiationException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
} catch (IllegalAccessException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
} catch (ClassNotFoundException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
user.setAutocommit(origauto);
}
}
/*
* (non-Javadoc)
*
* @see net.reliableresponse.notification.broker.UserMgmtBroker#getUsersByName(java.lang.String,
* java.lang.String)
*/
public User[] getUsersByName(String firstName, String lastName) {
Vector users = new Vector();
String sql = "SELECT uuid, firstname, lastname, email, cached, vacation FROM member WHERE deleted='N' AND lower(firstname)=? and lower(lastname)=? and type='1' ORDER BY lastname";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, firstName.toLowerCase());
stmt.setString(2, lastName.toLowerCase());
rs = stmt.executeQuery();
BrokerFactory.getLoggingBroker().logDebug(
"Getting user by name " + firstName + ", " + lastName);
while (rs.next()) {
User user = new User();
user.setUuid(rs.getString(1));
user.setFirstName(rs.getString(2));
user.setLastName(rs.getString(3));
user.setEmailAddress(rs.getString(4));
user.setInPermanentCache(rs.getString(5).equalsIgnoreCase("Y"));
user.setOnVacation(rs.getString(6).equalsIgnoreCase("Y"));
BrokerFactory.getLoggingBroker().logDebug(
"Found user by name " + user);
users.addElement(user);
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
try {
for (int usernum = 0; usernum < users.size(); usernum++) {
User user = (User) users.elementAt(usernum);
//getUserInformation(user, connection);
//getUserDevices(user, connection);
}
} finally {
try {
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return (User[]) users.toArray(new User[0]);
}
/*
* (non-Javadoc)
*
* @see net.reliableresponse.notification.broker.UserMgmtBroker#getUsersByPagerNumber(java.lang.String)
*/
public User[] getUsersByPagerNumber(String pagerNumber) {
// TODO
return null;
}
public String[] getUuidsByName(String firstName, String lastName) {
Vector uuids = new Vector();
String sql = "SELECT uuid FROM member WHERE deleted='N' AND type='1' AND (lower(firstname)=? AND lower(lastname)=?)";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, firstName);
stmt.setString(2, lastName);
rs = stmt.executeQuery();
while (rs.next()) {
uuids.addElement(rs.getString(1));
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return (String[])uuids.toArray(new String[0]);
}
public String[] getUuidsByPagerNumber(String pagerNumber) {
// TODO Auto-generated method stub
return null;
}
public int getPriorityOfGroup(User user, Group group) {
String sql = "SELECT priority FROM membership WHERE child=? AND parent=?";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setString(1, user.getUuid());
stmt.setString(2, group.getUuid());
rs = stmt.executeQuery();
if (rs.next()) {
return rs.getInt(1);
}
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
return 0;
}
public void setPriorityOfGroup(User user, Group group, int priority) {
String sql = "UPDATE membership SET priority=? WHERE child=? AND parent=?";
PreparedStatement stmt = null;
Connection connection = getConnection();
ResultSet rs = null;
try {
stmt = connection.prepareStatement(sql);BrokerFactory.getLoggingBroker().logDebug("sql="+(sql));
stmt.setInt(1, priority);
stmt.setString(2, user.getUuid());
stmt.setString(3, group.getUuid());
stmt.executeUpdate();
} catch (SQLException e) {
BrokerFactory.getLoggingBroker().logError(e);
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (SQLException e1) {
BrokerFactory.getLoggingBroker().logError(e1);
}
}
}
}