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