/// <summary> /// Gets a list of users for a specific company that do not have a mailbox /// </summary> /// <param name="companyCode"></param> /// <returns></returns> public static List<BaseUser> GetNonMailboxUsers(string companyCode) { SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString); SqlCommand cmd = new SqlCommand("SELECT * FROM Users WHERE CompanyCode=@CompanyCode AND (MailboxPlan=0 OR MailboxPlan IS NULL) ORDER BY DisplayName", sql); // Create our collection List<BaseUser> users = new List<BaseUser>(); try { // Add our parameter cmd.Parameters.AddWithValue("@CompanyCode", companyCode); // Open connection sql.Open(); // Read data SqlDataReader r = cmd.ExecuteReader(); while (r.Read()) { BaseUser tmp = new BaseUser(); tmp.ID = int.Parse(r["ID"].ToString()); tmp.UserGuid = Guid.Parse(r["UserGuid"].ToString()); tmp.UserPrincipalName = r["UserPrincipalName"].ToString(); tmp.DistinguishedName = r["DistinguishedName"].ToString(); tmp.DisplayName = r["DisplayName"].ToString(); tmp.CompanyCode = companyCode; if (r["sAMAccountName"] != DBNull.Value) tmp.sAMAccountName = r["sAMAccountName"].ToString(); else tmp.sAMAccountName = ""; if (r["Firstname"] != DBNull.Value) tmp.Firstname = r["Firstname"].ToString(); else tmp.Firstname = ""; if (r["Middlename"] != DBNull.Value) tmp.Middlename = r["MiddleName"].ToString(); else tmp.Middlename = ""; if (r["Lastname"] != DBNull.Value) tmp.Lastname = r["Lastname"].ToString(); else tmp.Lastname = ""; if (r["Email"] != DBNull.Value) tmp.Email = r["Email"].ToString(); else tmp.Email = ""; if (r["Department"] != DBNull.Value) tmp.Department = r["Department"].ToString(); else tmp.Department = ""; if (r["IsResellerAdmin"] != DBNull.Value) tmp.IsResellerAdmin = bool.Parse(r["IsResellerAdmin"].ToString()); else tmp.IsResellerAdmin = false; if (r["IsCompanyAdmin"] != DBNull.Value) tmp.IsCompanyAdmin = bool.Parse(r["IsCompanyAdmin"].ToString()); else tmp.IsCompanyAdmin = false; if (r["MailboxPlan"] != DBNull.Value) tmp.MailboxPlan = int.Parse(r["MailboxPlan"].ToString()); else tmp.MailboxPlan = 0; if (r["TSPlan"] != DBNull.Value) tmp.TSPlan = int.Parse(r["TSPlan"].ToString()); else tmp.TSPlan = 0; if (r["LyncPlan"] != DBNull.Value) tmp.LyncPlan = int.Parse(r["LyncPlan"].ToString()); else tmp.LyncPlan = 0; if (r["Created"] != DBNull.Value) tmp.Created = DateTime.Parse(r["Created"].ToString()); // Add to our collection users.Add(tmp); } // Dispose r.Close(); r.Dispose(); } catch (Exception) { throw; } finally { cmd.Dispose(); sql.Dispose(); } // Return our collection return users; }
/// <summary> /// Gets a list of users and then gets if they have access to the application or not /// </summary> /// <param name="companyCode"></param> /// <param name="citrixPlanID"></param> /// <returns></returns> public static List<BaseUser> GetAssignedCitrixUsers(string companyCode, string citrixPlanID) { SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString); SqlCommand cmd = new SqlCommand(@"SELECT * FROM Users WHERE CompanyCode=@CompanyCode ORDER BY DisplayName; SELECT * FROM UserPlansCitrix WHERE CitrixPlanID=@CitrixPlanID AND (UserID IN (SELECT UserID FROM Users WHERE CompanyCode=@CompanyCode))", sql); // Create our collection List<BaseUser> users = new List<BaseUser>(); try { // DEBUG // logger.Debug("Getting Citrix users for " + companyCode + " and Citrix plan id " + citrixPlanID); // Add our parameter cmd.Parameters.AddWithValue("CompanyCode", companyCode); cmd.Parameters.AddWithValue("CitrixPlanID", citrixPlanID); // Open connection sql.Open(); // Read data and get all users SqlDataReader r = cmd.ExecuteReader(); if (r.HasRows) { while (r.Read()) { BaseUser tmp = new BaseUser(); tmp.ID = int.Parse(r["ID"].ToString()); tmp.UserGuid = Guid.Parse(r["UserGuid"].ToString()); tmp.UserPrincipalName = r["UserPrincipalName"].ToString(); tmp.DistinguishedName = r["DistinguishedName"].ToString(); tmp.DisplayName = r["DisplayName"].ToString(); tmp.CompanyCode = companyCode; if (r["sAMAccountName"] != DBNull.Value) tmp.sAMAccountName = r["sAMAccountName"].ToString(); else tmp.sAMAccountName = ""; if (r["Firstname"] != DBNull.Value) tmp.Firstname = r["Firstname"].ToString(); else tmp.Firstname = ""; if (r["Middlename"] != DBNull.Value) tmp.Middlename = r["MiddleName"].ToString(); else tmp.Middlename = ""; if (r["Lastname"] != DBNull.Value) tmp.Lastname = r["Lastname"].ToString(); else tmp.Lastname = ""; if (r["Email"] != DBNull.Value) tmp.Email = r["Email"].ToString(); else tmp.Email = ""; if (r["Department"] != DBNull.Value) tmp.Department = r["Department"].ToString(); else tmp.Department = ""; if (r["IsResellerAdmin"] != DBNull.Value) tmp.IsResellerAdmin = bool.Parse(r["IsResellerAdmin"].ToString()); else tmp.IsResellerAdmin = false; if (r["IsCompanyAdmin"] != DBNull.Value) tmp.IsCompanyAdmin = bool.Parse(r["IsCompanyAdmin"].ToString()); else tmp.IsCompanyAdmin = false; if (r["MailboxPlan"] != DBNull.Value) tmp.MailboxPlan = int.Parse(r["MailboxPlan"].ToString()); else tmp.MailboxPlan = 0; if (r["TSPlan"] != DBNull.Value) tmp.TSPlan = int.Parse(r["TSPlan"].ToString()); else tmp.TSPlan = 0; if (r["LyncPlan"] != DBNull.Value) tmp.LyncPlan = int.Parse(r["LyncPlan"].ToString()); else tmp.LyncPlan = 0; if (r["Created"] != DBNull.Value) tmp.Created = DateTime.Parse(r["Created"].ToString()); // Add to our collection users.Add(tmp); } } // DEBUG // logger.Debug("Successfully retrieved all Citrix users for " + companyCode); // Go to our next result r.NextResult(); // Now see who has what application if (r.HasRows) { while (r.Read()) { int userID = int.Parse(r["UserID"].ToString()); int citrixID = int.Parse(r["CitrixPlanID"].ToString()); // Find the value in our list BaseUser found = users.Find(u => u.ID == userID); if (found != null) found.IsChecked = true; } } // Close r.Close(); sql.Close(); // Dispose r.Dispose(); // Return return users; } catch (Exception ex) { // FATAL // logger.Fatal("Error getting Citrix users for " + companyCode + " and Citrix plan id " + citrixPlanID, ex); throw; } finally { cmd.Dispose(); sql.Dispose(); } }
/// <summary> /// Gets detailed information about a specific user /// </summary> /// <param name="userPrincipalName"></param> /// <returns></returns> public static BaseUser GetUser(string userPrincipalName) { SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString); SqlCommand cmd = new SqlCommand(@"SELECT *, (SELECT CompanyName FROM Companies WHERE CompanyCode=u.CompanyCode) AS CompanyName, (SELECT CompanyName FROM Companies WHERE IsReseller=1 AND CompanyCode=(SELECT ResellerCode FROM Companies WHERE CompanyCode=u.CompanyCode)) AS ResellerName, (SELECT ResellerCode FROM Companies WHERE CompanyCode=u.CompanyCode) AS ResellerCode FROM Users u WHERE UserPrincipalName=@UserPrincipalName", sql); try { // Our object to return BaseUser tmp = new BaseUser(); // Add our parameter cmd.Parameters.AddWithValue("UserPrincipalName", userPrincipalName); // Open connection sql.Open(); // Read data SqlDataReader r = cmd.ExecuteReader(); while (r.Read()) { tmp.ID = int.Parse(r["ID"].ToString()); tmp.UserGuid = Guid.Parse(r["UserGuid"].ToString()); tmp.UserPrincipalName = r["UserPrincipalName"].ToString(); tmp.DistinguishedName = r["DistinguishedName"].ToString(); tmp.DisplayName = r["DisplayName"].ToString(); tmp.CompanyCode = r["CompanyCode"].ToString(); tmp.CompanyName = r["CompanyName"].ToString(); tmp.ResellerCode = r["ResellerCode"].ToString(); tmp.ResellerName = r["ResellerName"].ToString(); if (r["sAMAccountName"] != DBNull.Value) tmp.sAMAccountName = r["sAMAccountName"].ToString(); else tmp.sAMAccountName = ""; if (r["Firstname"] != DBNull.Value) tmp.Firstname = r["Firstname"].ToString(); else tmp.Firstname = ""; if (r["Middlename"] != DBNull.Value) tmp.Middlename = r["MiddleName"].ToString(); else tmp.Middlename = ""; if (r["Lastname"] != DBNull.Value) tmp.Lastname = r["Lastname"].ToString(); else tmp.Lastname = ""; if (r["Email"] != DBNull.Value) tmp.Lastname = r["Email"].ToString(); else tmp.Lastname = ""; if (r["Department"] != DBNull.Value) tmp.Department = r["Department"].ToString(); else tmp.Department = ""; if (r["IsResellerAdmin"] != DBNull.Value) tmp.IsResellerAdmin = bool.Parse(r["IsResellerAdmin"].ToString()); else tmp.IsResellerAdmin = false; if (r["IsCompanyAdmin"] != DBNull.Value) tmp.IsCompanyAdmin = bool.Parse(r["IsCompanyAdmin"].ToString()); else tmp.IsCompanyAdmin = false; if (r["MailboxPlan"] != DBNull.Value) tmp.MailboxPlan = int.Parse(r["MailboxPlan"].ToString()); else tmp.MailboxPlan = 0; if (r["TSPlan"] != DBNull.Value) tmp.TSPlan = int.Parse(r["TSPlan"].ToString()); else tmp.TSPlan = 0; if (r["LyncPlan"] != DBNull.Value) tmp.LyncPlan = int.Parse(r["LyncPlan"].ToString()); else tmp.LyncPlan = 0; if (r["Created"] != DBNull.Value) tmp.Created = DateTime.Parse(r["Created"].ToString()); if (r["AdditionalMB"] != DBNull.Value) tmp.AdditionalMB = int.Parse(r["AdditionalMB"].ToString()); else tmp.AdditionalMB = 0; } // Dispose r.Close(); r.Dispose(); // return return tmp; } catch (Exception ex) { throw ex; } finally { cmd.Dispose(); sql.Dispose(); } }
/// <summary> /// Updates a user in SQL /// </summary> /// <param name="user"></param> public static void UpdateUser(BaseUser user) { SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString); SqlCommand cmd = new SqlCommand(@"UPDATE Users SET DisplayName=@DisplayName, Firstname=@Firstname, Middlename=@Middlename, Lastname=@Lastname, Department=@Department, IsCompanyAdmin=@IsCompanyAdmin, IsResellerAdmin=@IsResellerAdmin WHERE UserPrincipalName=@UserPrincipalName", sql); try { // Add our parameters cmd.Parameters.AddWithValue("@UserPrincipalName", user.UserPrincipalName); cmd.Parameters.AddWithValue("@DisplayName", user.DisplayName); cmd.Parameters.AddWithValue("@Firstname", user.Firstname); cmd.Parameters.AddWithValue("@Middlename", user.Middlename); cmd.Parameters.AddWithValue("@Lastname", user.Lastname); cmd.Parameters.AddWithValue("@Department", user.Department); cmd.Parameters.AddWithValue("@IsCompanyAdmin", user.IsCompanyAdmin); cmd.Parameters.AddWithValue("@IsResellerAdmin", user.IsResellerAdmin); // Open connection sql.Open(); // Insert data cmd.ExecuteNonQuery(); // Close sql.Close(); } catch (Exception) { throw; } finally { cmd.Dispose(); sql.Dispose(); } }
/// <summary> /// Gets a list of members in a distribution group /// </summary> /// <param name="groupEmail"></param> /// <returns></returns> public List<BaseUser> Get_DistributionGroupMembers(string groupEmail) { // Our return object List<BaseUser> members = new List<BaseUser>(); try { // Start clock Stopwatch stopwatch = Stopwatch.StartNew(); // DEBUG // logger.Debug("Getting members of distribution group [" + groupEmail + "]"); // Run commands powershell = PowerShell.Create(); powershell.Runspace = runspace; // Enable the mailbox PSCommand cmd = new PSCommand(); cmd.AddCommand("Get-DistributionGroupMember"); cmd.AddParameter("Identity", groupEmail); cmd.AddParameter("ResultSize", "Unlimited"); cmd.AddParameter("DomainController", this.domainController); powershell.Commands = cmd; // Invoke and get returned information Collection<PSObject> results = powershell.Invoke(); foreach (PSObject ps in results) { // BEGIN DEBUG foreach (PSMemberInfo psmi in ps.Members) { // DEBUG // if (psmi.Value == null) this.logger.Debug("Value for [" + psmi.Name + "] was null."); else this.logger.Debug("Value for [" + psmi.Name + "] is " + psmi.Value.ToString()); } // Check and make sure it is a user string recipientType = ps.Members["RecipientType"].Value.ToString(); if (recipientType == "UserMailbox" || recipientType.Contains("DistributionGroup")) { BaseUser tmp = new BaseUser(); tmp.CompanyCode = ps.Members["CustomAttribute1"].Value.ToString(); tmp.DisplayName = ps.Members["DisplayName"].Value.ToString(); tmp.Email = ps.Members["PrimarySmtpAddress"].Value.ToString(); tmp.DistinguishedName = ps.Members["DistinguishedName"].Value.ToString().ToUpper(); // Upper case for comparisons with other lists tmp.UserGuid = Guid.Parse(ps.Members["Guid"].Value.ToString()); tmp.sAMAccountName = ps.Members["sAMAccountName"].Value.ToString(); // Set the object type if (recipientType == "UserMailbox") tmp.ObjectType = "User"; else tmp.ObjectType = "Group"; if (ps.Members["FirstName"].Value != null) tmp.Firstname = ps.Members["Firstname"].Value.ToString(); if (ps.Members["LastName"].Value != null) tmp.Lastname = ps.Members["Lastname"].Value.ToString(); // Add our object members.Add(tmp); } } // Stop the clock stopwatch.Stop(); // Check for errors CheckErrors(ref powershell); // DEBUG // logger.Debug("Successfully retrieved members for group [" + groupEmail + "] in " + stopwatch.Elapsed.ToString() + " second(s)"); // Return list return members; } catch (Exception ex) { // DEBUG // this.logger.Error("Error getting distribution group members for group " + groupEmail + ": " + ex.ToString()); throw; } finally { if (powershell != null) powershell.Dispose(); } }