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