public string addGroup(String name, String lang, int parentid, String accessHash)
{
user u = new user();
if (u.validateAccessHash(accessHash))
{
String newId = "Error";
sqltransaction s = new sqltransaction();
String qs = "SELECT MAX(id) as maxid FROM groups";
String tn = "maxid";
DataSet ds = s.doQueryWithResults(qs, tn);
// get the highest id, then add 100
int maxid = int.Parse(ds.Tables[tn].Rows[0][tn].ToString());
int newid = maxid + 100;
qs = "INSERT INTO groups (id, name, language, parent) VALUES (" + newid + ", '" + name + "','" + lang + "', " + parentid + ")";
bool c = s.doQueryNoResults(qs);
if (c)
{
newId = newid.ToString();
}
return(newId);
}
else
{
return("");
}
}
public void userOptedIn(String userId)
{
// updates a user account after renewal
sqltransaction s = new sqltransaction();
String svSQLQString = "UPDATE users SET optout=0 WHERE (id='" + userId + "')";
Boolean renewed = s.doQueryNoResults(svSQLQString);
}
public void renewUser(String userId)
{
// updates a user account after renewal
sqltransaction s = new sqltransaction();
String svSQLQString = "UPDATE users SET d_created=GETDATE() WHERE (id='" + userId + "')";
Boolean renewed = s.doQueryNoResults(svSQLQString);
}
public void modifyUser(String id, String firstname, String lastname, String nickname, String username, String password, String company, String company_website, String email, String phone, String address1, String address2, String city, String provstate, String country, String postalzip, String roleid, String statusid, String groupid)
{
// updates the user with all information changes except id, d_created and d_last_accessed
if (id != "")
{
uid = id;
//sqltransaction s = new sqltransaction();
//String svSQLQString = "UPDATE users SET ";
//svSQLQString += "first_name='" + firstname + "', last_name='" + lastname + "', altname='" + nickname + "', username='******', password='******', company='" + company + "', company_url='" + company_website + "', email='" + email + "', street_address1='" + address1 + "', street_address2='" + address2 + "', city='" + city + "', prov_state='" + provstate + "', country='" + country + "', postal_zip='" + postalzip + "', role='" + roleid + "', status='" + statusid + "', [group]='" + groupid + "', d_updated=GETDATE()";
//svSQLQString += " WHERE ";
//svSQLQString += " (id='" + uid + "')";
//Boolean userCreated = s.doQueryNoResults(svSQLQString);
if (password != "")
{
sqltransaction s = new sqltransaction();
String svSQLQString = "UPDATE users SET ";
svSQLQString += "first_name='" + firstname + "', last_name='" + lastname + "', altname='" + nickname + "', username='******', password='******', company='" + company + "', company_url='" + company_website + "', email='" + email + "', phone_number='" + phone + "', street_address1='" + address1 + "', street_address2='" + address2 + "', city='" + city + "', prov_state='" + provstate + "', country='" + country + "', postal_zip='" + postalzip + "', role='" + roleid + "', status='" + statusid + "', [group]='" + groupid + "', d_updated=GETDATE()";
svSQLQString += " WHERE ";
svSQLQString += " (id='" + uid + "')";
Boolean userCreated = s.doQueryNoResults(svSQLQString);
}
else
{
sqltransaction s = new sqltransaction();
String svSQLQString = "UPDATE users SET ";
svSQLQString += "first_name='" + firstname + "', last_name='" + lastname + "', altname='" + nickname + "', username='******', company='" + company + "', company_url='" + company_website + "', email='" + email + "', phone_number='" + phone + "', street_address1='" + address1 + "', street_address2='" + address2 + "', city='" + city + "', prov_state='" + provstate + "', country='" + country + "', postal_zip='" + postalzip + "', role='" + roleid + "', status='" + statusid + "', [group]='" + groupid + "', d_updated=GETDATE()";
svSQLQString += " WHERE ";
svSQLQString += " (id='" + uid + "')";
Boolean userCreated = s.doQueryNoResults(svSQLQString);
}
}
}
public void createUser(String firstname, String lastname, String nickname, String username, String password, String company, String company_website, String email, String phone, String address1, String address2, String city, String provstate, String country, String postalzip, String roleid, String statusid, String groupid)
{
// adds this user to the database.
if (uid != "")
{
sqltransaction s = new sqltransaction();
String svSQLQString = "INSERT INTO users ";
svSQLQString += "(id, first_name, last_name, altname, username, password, company, company_url, email, phone_number, street_address1, street_address2, city, prov_state, country, postal_zip, role, status, [group], d_created)";
svSQLQString += " VALUES ";
svSQLQString += "('" + uid + "','" + firstname + "','" + lastname + "','" + nickname + "','" + username + "','" + saltedhash.ComputeHash(password, "MD5", Encoding.UTF8.GetBytes(uid)) + "','" + company + "','" + company_website + "','" + email + "','" + phone + "','" + address1 + "','" + address2 + "','" + city + "','" + provstate + "','" + country + "','" + postalzip + "','" + roleid + "','" + statusid + "','" + groupid + "',GETDATE())";
Boolean userCreated = s.doQueryNoResults(svSQLQString);
}
}
public void reActivateUser(String svUID)
{
// set the user's status to disabled.
if (svUID != "")
{
sqltransaction s = new sqltransaction();
String svSQLQString = "UPDATE users SET ";
svSQLQString += "status='100', d_updated=GETDATE()";
svSQLQString += " WHERE ";
svSQLQString += " (id='" + svUID + "')";
Boolean userCreated = s.doQueryNoResults(svSQLQString);
}
}
public void hardDeleteUser(String svUID)
{
// delete the user's record.
if (svUID != "")
{
// protect the built-in admin
if (svUID != "965efdec-6254-4664-85e7-6b13e8f795d8")
{
sqltransaction s = new sqltransaction();
String svSQLQString = "DELETE FROM users ";
svSQLQString += " WHERE ";
svSQLQString += " (id='" + svUID + "')";
Boolean userCreated = s.doQueryNoResults(svSQLQString);
// add any other special conditions here.
}
}
}
public bool setGroup(int id, String name, String lang, int parentid, String accessHash)
{
user u = new user();
if (u.validateAccessHash(accessHash))
{
sqltransaction s = new sqltransaction();
String qs = "UPDATE groups SET name='" + name + "', language='" + lang + "', parent=" + parentid + " WHERE id=" + id + "";
bool c = s.doQueryNoResults(qs);
return(c);
}
else
{
return(false);
}
}
public void loadUserDataUPbyID(String svID)
{
// loads up the variables for the user at svUID.
sqltransaction s = new sqltransaction();
String svQString = "SELECT u.*, s.name as statusText FROM users u INNER JOIN status s ON s.id=u.status WHERE u.id='" + svID + "'";
DataSet ds = s.doQueryWithResults(svQString, "userdata");
if (ds.Tables["userdata"].Rows.Count == 1)
{
foreach (DataRow r in ds.Tables["userdata"].Rows)
{
uid = r["id"].ToString();
first_name = r["first_name"].ToString();
last_name = r["last_name"].ToString();
altname = r["altname"].ToString();
username = r["username"].ToString();
password = r["password"].ToString();
company = r["company"].ToString();
company_url = r["company_url"].ToString();
email = r["email"].ToString();
phone = r["phone_number"].ToString();
street_address1 = r["street_address1"].ToString();
street_address2 = r["street_address2"].ToString();
city = r["city"].ToString();
prov_state = r["prov_state"].ToString();
country = r["country"].ToString();
postal_zip = r["postal_zip"].ToString();
role = r["role"].ToString();
status = r["status"].ToString();
statusText = r["statusText"].ToString();
group = r["group"].ToString();
d_created = r["d_created"].ToString();
d_last_accessed = r["d_last_accessed"].ToString();
d_updated = r["d_updated"].ToString();
if (status == "300")
{
err = "This user has been disabled.";
}
}
}
else
{
err += ds.Tables["userdata"].Rows.Count + " rows were retrieved for user [" + svID + "]";
}
}
public DataSet getUsersByCompany(String companyid, String accessHash)
{
user u = new user();
if (u.validateAccessHash(accessHash))
{
String tn = "users";
String qs = "SELECT u.*, r.name as roleName FROM users u INNER JOIN roles r ON u.role=r.id WHERE u.company='" + companyid + "' AND r.language='en' AND u.status='100' ORDER BY u.last_name";
sqltransaction s = new sqltransaction();
DataSet ds = s.doQueryWithResults(qs, tn);
return(ds);
}
else
{
return(new DataSet());
}
}
public DataSet getNonActiveUserList(String accessHash)
{
user u = new user();
if (u.validateAccessHash(accessHash))
{
String tn = "users";
String qs = "SELECT * FROM users WHERE (status>100) ORDER BY d_created";
sqltransaction s = new sqltransaction();
DataSet ds = s.doQueryWithResults(qs, tn);
return(ds);
}
else
{
return(new DataSet());
}
}
public DataSet searchActiveUserList(String searchTerm, String accessHash)
{
user u = new user();
if (u.validateAccessHash(accessHash))
{
String tn = "users";
String qs = "SELECT * FROM users WHERE status=100 AND (first_name LIKE '%" + searchTerm + "%' OR last_name LIKE '%" + searchTerm + "%') ORDER BY username";
sqltransaction s = new sqltransaction();
DataSet ds = s.doQueryWithResults(qs, tn);
return(ds);
}
else
{
return(new DataSet());
}
}
public DataSet getAllUserList(String accessHash)
{
user u = new user();
if (u.validateAccessHash(accessHash))
{
String tn = "users";
String qs = "SELECT * FROM users ORDER BY username";
sqltransaction s = new sqltransaction();
DataSet ds = s.doQueryWithResults(qs, tn);
return(ds);
}
else
{
return(new DataSet());
}
}
public Boolean initialUpdatePass(String id, String pass, String accessHash)
{
user u = new user();
if (u.validateAccessHash(accessHash))
{
sqltransaction s = new sqltransaction();
String svSQLQString = "UPDATE users SET password='******'";
svSQLQString += " WHERE ";
svSQLQString += " (id='" + id + "')";
Boolean userUpdate = s.doQueryNoResults(svSQLQString);
return(userUpdate);
}
else
{
return(false);
}
}
public bool removeGroup(int id, String accessHash)
{
user u = new user();
if (u.validateAccessHash(accessHash))
{
sqltransaction s = new sqltransaction();
String qs = "DELETE FROM groups WHERE id=" + id + "";
bool c = s.doQueryNoResults(qs);
//will need to put in code for if removing group with children then to update all children to root parent
return(c);
}
else
{
return(false);
}
}
public Boolean updateLastAccessUser(String id, String accessHash)
{
user u = new user();
if (u.validateAccessHash(accessHash))
{
// updates the user with all information changes except id, d_created and d_last_accessed
sqltransaction s = new sqltransaction();
String svSQLQString = "UPDATE users SET d_last_accessed=GETDATE()";
svSQLQString += " WHERE ";
svSQLQString += " (id='" + id + "')";
Boolean userCreated = s.doQueryNoResults(svSQLQString);
return(userCreated);
}
else
{
return(false);
}
}
public void loadUserData(String svUID)
{
// loads up the variables for the user at svUID. // updated to load only active users.
sqltransaction s = new sqltransaction();
String svQString = "SELECT * FROM users WHERE id='" + svUID + "' AND status='100'";
DataSet ds = s.doQueryWithResults(svQString, "userdata");
if (ds.Tables["userdata"].Rows.Count == 1)
{
foreach (DataRow r in ds.Tables["userdata"].Rows)
{
uid = r["id"].ToString();
first_name = r["first_name"].ToString();
last_name = r["last_name"].ToString();
altname = r["altname"].ToString();
username = r["username"].ToString();
password = saltedhash.ComputeHash(r["password"].ToString(), "MD5", Encoding.UTF8.GetBytes(r["id"].ToString()));
company = r["company"].ToString();
company_url = r["company_url"].ToString();
email = r["email"].ToString();
phone = r["phone_number"].ToString();
street_address1 = r["street_address1"].ToString();
street_address2 = r["street_address2"].ToString();
city = r["city"].ToString();
prov_state = r["prov_state"].ToString();
country = r["country"].ToString();
postal_zip = r["postal_zip"].ToString();
role = r["role"].ToString();
status = r["status"].ToString();
group = r["group"].ToString();
d_created = r["d_created"].ToString();
d_last_accessed = r["d_last_accessed"].ToString();
d_updated = r["d_updated"].ToString();
}
}
else
{
err += ds.Tables["userdata"].Rows.Count + " Rows were retrieved for uid [" + svUID + "]";
}
}
public String getRolesName(String lang, String roleid, String accessHash)
{
user u = new user();
String myRoleName = "";
if (u.validateAccessHash(accessHash))
{
if (lang == "")
{
lang = "en";
}
String tn = "roles";
String qs = "SELECT name FROM roles WHERE (language='" + lang + "') AND (id='" + roleid + "')";
sqltransaction s = new sqltransaction();
DataSet ds = s.doQueryWithResults(qs, tn);
if (ds.Tables[tn].Rows.Count > 0)
{
myRoleName = ds.Tables[tn].Rows[0]["name"].ToString();
}
}
return(myRoleName);
}
public DataSet getStatus(String lang, String accessHash)
{
user u = new user();
if (u.validateAccessHash(accessHash))
{
if (lang == "")
{
lang = "en";
}
String tn = "status";
String qs = "SELECT id, name FROM status WHERE language='" + lang + "' ORDER BY name";
sqltransaction s = new sqltransaction();
DataSet ds = s.doQueryWithResults(qs, tn);
return(ds);
}
else
{
return(new DataSet());
}
}
public String getActiveUserIdByEmail(String emailAddress, String accessHash)
{
user u = new user();
if (u.validateAccessHash(accessHash))
{
String tn = "users";
String qs = "SELECT TOP (1) * FROM users WHERE status=100 AND email='" + emailAddress + "'";
sqltransaction s = new sqltransaction();
DataSet ds = s.doQueryWithResults(qs, tn);
if (ds.Tables[0].Rows.Count == 1)
{
return(ds.Tables[0].Rows[0]["id"].ToString());
}
else
{
return("");
}
}
else
{
return("");
}
}
public void loadUserDataUP(String svUsername, String svPassword)
{
// loads up the variables for the user at svUID.
sqltransaction s = new sqltransaction();
String svQString = "SELECT * FROM users WHERE (username='******')";
DataSet ds = s.doQueryWithResults(svQString, "userdata");
err = "User Not Found"; // protect against users not found
if (ds.Tables["userdata"].Rows.Count >= 1)
{
foreach (DataRow r in ds.Tables["userdata"].Rows)
{
// looped because we need the user id to get the password
if (saltedhash.ComputeHash(svPassword, "MD5", Encoding.UTF8.GetBytes(r["id"].ToString())) == r["password"].ToString())
{
uid = r["id"].ToString();
first_name = r["first_name"].ToString();
last_name = r["last_name"].ToString();
altname = r["altname"].ToString();
username = r["username"].ToString();
password = r["password"].ToString();
company = r["company"].ToString();
company_url = r["company_url"].ToString();
email = r["email"].ToString();
phone = r["phone_number"].ToString();
street_address1 = r["street_address1"].ToString();
street_address2 = r["street_address2"].ToString();
city = r["city"].ToString();
prov_state = r["prov_state"].ToString();
country = r["country"].ToString();
postal_zip = r["postal_zip"].ToString();
role = r["role"].ToString();
status = r["status"].ToString();
statusText = "perform a soft get user by id for this info";
group = r["group"].ToString();
d_created = r["d_created"].ToString();
d_last_accessed = r["d_last_accessed"].ToString();
d_updated = r["d_updated"].ToString();
if (status == "100")
{
err = null; // all good, active user
}
else
{
// msgs for status conversion
switch (status)
{
case "200":
err = "This user is pending activation.";
break;
case "300":
err = "This user has been disabled.";
break;
case "400":
err = "This user has been deleted.";
break;
case "500":
err = "This user has been suspended.";
break;
default:
err = "This user is not active.";
break;
}
}
}
}
}
else
{
err = ds.Tables["userdata"].Rows.Count + " rows were retrieved for user [" + svUsername + "]";
}
}
public DataSet searchActiveUserListByFacet(String firstName, String lastName, String email, String username, String city,
String prov, String country, String userType, String companyId, String accessHash)
{
user u = new user();
if (u.validateAccessHash(accessHash))
{
String tn = "users";
String qs = "SELECT * FROM users WHERE (status=100) ";
String fa = "AND ";
if (firstName == "" && lastName == "" && email == "" && username == "" && city == "" && prov == "" && country == "" && userType == "" && companyId == "")
{
return(new DataSet());
}
else
{
if (firstName != "")
{
qs += fa + "(first_name LIKE '%" + firstName + "%') ";
fa = "AND ";
}
if (lastName != "")
{
qs += fa + "(last_name LIKE '%" + lastName + "%') ";
fa = "AND ";
}
if (email != "")
{
qs += fa + "(email='" + email + "') ";
fa = "AND ";
}
if (username != "")
{
qs += fa + "(username LIKE '%" + username + "%') ";
fa = "AND ";
}
if (city != "")
{
qs += fa + "(city='" + city + "') ";
fa = "AND ";
}
if (prov != "")
{
qs += fa + "(prov_state='" + prov + "') ";
fa = "AND ";
}
if (country != "")
{
qs += fa + "(country='" + country + "') ";
fa = "AND ";
}
if (userType != "")
{
qs += fa + "(role='" + userType + "') ";
fa = "AND ";
}
if (companyId != "")
{
int x;
if (int.TryParse(companyId, out x))
{
qs += fa + "(company='" + companyId + "') ";
}
else
{
qs += fa + "(company LIKE '%" + companyId + "%') ";
}
fa = "AND ";
}
qs += "ORDER BY last_name";
sqltransaction s = new sqltransaction();
DataSet ds = s.doQueryWithResults(qs, tn);
return(ds);
}
}
else
{
return(new DataSet());
}
}
