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 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 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 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 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 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 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 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());
}
}