public ActionResult DenyUser(string id)
{
using (DataContext dataContext = new DataContext("dbOpenXDA"))
{
ActionResult adminValidationResult = ValidateAdminRequest();
if (adminValidationResult != null)
{
return(adminValidationResult);
}
Guid userID = Guid.Parse(id);
ConfirmableUserAccount confirmableUserAccount = dataContext.Table <ConfirmableUserAccount>().QueryRecordWhere("ID = {0}", userID);
if (confirmableUserAccount == null)
{
return(HttpNotFound());
}
string accountName = UserInfo.SIDToAccountName(confirmableUserAccount.Name);
CascadeDelete("UserAccount", $"ID='{userID}'");
ViewBag.Message = accountName + " has been denied access to email notifications.";
string emailServiceName = GetEmailServiceName();
string message = $"{emailServiceName} subscriptions have been denied by the administrator.";
SendEmail(confirmableUserAccount.Email, message, message);
return(View("Message"));
}
}
private ActionResult HandleVerifySubmit(VerifyCodeModel formData, ConfirmableUserAccount user)
{
using (DataContext dataContext = new DataContext("dbOpenXDA"))
{
if (s_memoryCache.Contains(formData.type + user.ID.ToString()))
{
string code = s_memoryCache.Get(formData.type + user.ID.ToString()).ToString();
if (code != formData.code.ToString("D6"))
{
TempData["BadCode"] = true;
return(RedirectToAction("Verify", new { id = formData.type }));
}
dataContext.Connection.ExecuteNonQuery($"UPDATE UserAccount Set {(formData.type == "email" ? "EmailConfirmed" : "PhoneConfirmed")} = 1 WHERE ID = '{user.ID}'");
s_memoryCache.Remove(formData.type + user.ID.ToString());
string emailServiceName = GetEmailServiceName();
string recipient = formData.type == "email" ? user.Email : user.Phone;
string subject = $"{emailServiceName} has confirmed your {(formData.type == "email" ? "email address" : "SMS number")}.";
string body = $"Once you are approved by an administrator, you will begin receiving notifications.";
SendEmail(recipient, subject, body);
}
else
{
TempData["ExpiredCode"] = true;
return(RedirectToAction("Verify", new { id = formData.type }));
}
return(RedirectToAction("UpdateSettings"));
}
}
public ActionResult ApproveUser(string id)
{
using (DataContext dataContext = new DataContext("dbOpenXDA"))
{
ActionResult adminValidationResult = ValidateAdminRequest();
if (adminValidationResult != null)
{
return(adminValidationResult);
}
ConfirmableUserAccount confirmableUserAccount = dataContext.Table <ConfirmableUserAccount>().QueryRecordWhere("ID = {0}", Guid.Parse(id));
if (confirmableUserAccount == null)
{
return(HttpNotFound());
}
dataContext.Connection.ExecuteNonQuery("UPDATE UserAccount SET Approved = 1 WHERE ID = {0}", confirmableUserAccount.ID);
string accountName = UserInfo.SIDToAccountName(confirmableUserAccount.Name);
ViewBag.Message = accountName + " has been approved to receive notifications.";
string emailServiceName = GetEmailServiceName();
string message = $"{emailServiceName} subscriptions have been approved.";
SendEmail(confirmableUserAccount.Email, message, message);
return(View("Message"));
}
}
public ActionResult VerifyCode(VerifyCodeModel formData)
{
using (DataContext dataContext = new DataContext("dbOpenXDA"))
{
string username = HttpContext.User.Identity.Name;
string usersid = UserInfo.UserNameToSID(username);
ConfirmableUserAccount user = dataContext.Table <ConfirmableUserAccount>().QueryRecordWhere("ID = {0}", formData.accountid);
if (username != user.Name && usersid != user.Name)
{
return(new HttpStatusCodeResult(HttpStatusCode.Forbidden));
}
if (formData.submit == "Submit")
{
return(HandleVerifySubmit(formData, user));
}
else if (formData.submit == "Resend Code")
{
return(HandleVerifyResendCode(formData, user));
}
ViewBag.Message = "Bad Command";
return(View("Message"));
}
}
private ActionResult HandleVerifyResendCode(VerifyCodeModel formData, ConfirmableUserAccount user)
{
string url = m_dataContext.Connection.ExecuteScalar <string>("SELECT Value FROM DashSettings WHERE Name = 'System.URL'");
// if email changed force reconfirmation
if (formData.type == "email")
{
// generate code for email confirmation
string code = Random.Int32Between(0, 999999).ToString("D6");
s_memoryCache.Set("email" + user.ID.ToString(), code, new CacheItemPolicy {
SlidingExpiration = TimeSpan.FromDays(1)
});
string emailServiceName = GetEmailServiceName();
string subject = $"{emailServiceName} requires you to confirm your email.";
string body = $"From your workstation, input {code} at {url}/email/verify/email";
SendEmail(user.Email, subject, body);
}
// if phone changed force reconfirmation
if (formData.type == "sms")
{
string code = Random.Int32Between(0, 999999).ToString("D6");
s_memoryCache.Set("sms" + user.ID.ToString(), code, new CacheItemPolicy {
SlidingExpiration = TimeSpan.FromDays(1)
});
string emailServiceName = GetEmailServiceName();
string subject = $"{emailServiceName} requires you to confirm your SMS number.";
string body = $"From your workstation, input {code} at {url}/email/verify/sms";
SendEmail(user.Phone, subject, body);
}
return(RedirectToAction("Verify", new { id = formData.type }));
}
private void UpdateUserAccountAssetGroup(ConfirmableUserAccount userAccount, UpdateSettingModel formData)
{
using (DataContext dataContext = new DataContext("dbOpenXDA"))
{
// update link to asset group
TableOperations <UserAccountAssetGroup> userAccountAssetGroupTable = dataContext.Table <UserAccountAssetGroup>();
IEnumerable <UserAccountAssetGroup> userAccountAssetGroups = userAccountAssetGroupTable.QueryRecordsWhere("UserAccountID = {0}", userAccount.ID);
IEnumerable <int> assetGroups = userAccountAssetGroups.Select(x => x.AssetGroupID);
// formData will come back as null instead of empty array ....
if (formData.region == null)
{
formData.region = new List <int>();
}
// First pass. Add Link in database if the link does not exist.
foreach (int id in formData.region)
{
if (!assetGroups.Contains(id))
{
UserAccountAssetGroup userAccountAssetGroup = new UserAccountAssetGroup();
userAccountAssetGroup.UserAccountID = userAccount.ID;
userAccountAssetGroup.AssetGroupID = id;
userAccountAssetGroup.Dashboard = true;
userAccountAssetGroup.Email = true;
userAccountAssetGroupTable.AddNewRecord(userAccountAssetGroup);
}
else
{
UserAccountAssetGroup userAccountAssetGroup = userAccountAssetGroups.Where(x => x.AssetGroupID == id).First();
if (!userAccountAssetGroup.Dashboard || !userAccountAssetGroup.Email)
{
userAccountAssetGroup.Dashboard = true;
userAccountAssetGroup.Email = true;
userAccountAssetGroupTable.UpdateRecord(userAccountAssetGroup);
}
}
}
userAccountAssetGroups = userAccountAssetGroupTable.QueryRecordsWhere("UserAccountID = {0}", userAccount.ID);
// Second pass. Remove Link if the link does not exist in data from form.
foreach (UserAccountAssetGroup link in userAccountAssetGroups)
{
if (!formData.region.Contains(link.AssetGroupID))
{
userAccountAssetGroupTable.DeleteRecord(link);
}
}
}
}
private void HandleUpdate(UpdateSettingModel formData)
{
using (DataContext dataContext = new DataContext("dbOpenXDA"))
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
TableOperations <ConfirmableUserAccount> userAccountTable = dataContext.Table <ConfirmableUserAccount>();
ConfirmableUserAccount userAccount = userAccountTable.QueryRecordWhere("Name = {0}", formData.sid);
string url = connection.ExecuteScalar <string>("SELECT AltText1 FROM ValueList WHERE Text = 'URL' AND GroupID = (SELECT ID FROM ValueListGroup WHERE Name = 'System')");
string emailServiceName = GetEmailServiceName();
string recipient, subject, body;
// if phone changed force reconfirmation
if (userAccount.Phone != formData.phone + "@" + formData.carrier)
{
userAccount.Phone = formData.phone;
userAccount.PhoneConfirmed = false;
if (!string.IsNullOrEmpty(formData.phone))
{
userAccount.Phone += $"@{formData.carrier}";
// generate code for sms confirmation
string code = Random.Int32Between(0, 999999).ToString("D6");
s_memoryCache.Set("sms" + userAccount.ID.ToString(), code, new CacheItemPolicy {
SlidingExpiration = TimeSpan.FromDays(1)
});
recipient = userAccount.Phone;
subject = $"{emailServiceName} requires you to confirm your SMS number.";
body = $"From your workstation, input {code} at {url}/email/verify/sms";
SendEmail(recipient, subject, body);
}
}
userAccountTable.UpdateRecord(userAccount);
UpdateUserAccountAssetGroup(userAccount, formData);
UpdateUserAccountEmailType(userAccount, formData.job, false);
UpdateUserAccountEmailType(userAccount, formData.sms, true);
recipient = userAccount.Email;
subject = $"{emailServiceName} subscriptions updated";
body = $"Your {emailServiceName} subscriptions have been updated. Visit {url}/email/UpdateSettings to review your subscriptions.";
SendEmail(recipient, subject, body);
}
}
private void HandleSignUp(UpdateSettingModel formData)
{
UserInfo userInfo = new UserInfo(System.Web.HttpContext.Current.User.Identity.Name);
userInfo.Initialize();
//// Create new user
m_dataContext.Connection.ExecuteNonQuery("INSERT INTO UserAccount (Name, Email, EmailConfirmed, FirstName, LastName) VALUES ({0}, {1}, {2}, {3}, {4})", formData.sid, userInfo.Email, true, userInfo.FirstName, userInfo.LastName);
HandleUpdate(formData);
// email system admin for approval
ConfirmableUserAccount user = m_dataContext.Table <ConfirmableUserAccount>().QueryRecordWhere("Name = {0}", formData.sid);
IEnumerable <AssetGroup> assetGroup = m_dataContext.Table <AssetGroup>().QueryRecordsWhere($"ID IN ({string.Join(",", formData.region)})", formData.region);
IEnumerable <EmailType> emailType = m_dataContext.Table <EmailType>().QueryRecordsWhere($"ID IN ({string.Join(",", formData.job)})");
IEnumerable <XSLTemplate> xslTemplate = m_dataContext.Table <XSLTemplate>().QueryRecordsWhere($"ID IN ({string.Join(",", emailType.Select(x => x.XSLTemplateID))})");
string url = m_dataContext.Connection.ExecuteScalar <string>("SELECT Value FROM DashSettings WHERE Name = 'System.URL'");
string admin = m_dataContext.Connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Email.AdminAddress'");
string templateName = (xslTemplate.Any() ? string.Join(", ", xslTemplate.Select(x => x.Name)) : "None");
string regionName = (assetGroup.Any() ? string.Join(", ", assetGroup.Select(x => x.Name)) : "None");
string emailServiceName = GetEmailServiceName();
string subject = $"{formData.username} requests access to the {emailServiceName}.";
string body = @"
<html>
<p>" + formData.username + @" requests access to the " + emailServiceName + @".</p>
<table>
<tr><td>Email:</td><td>" + userInfo.Email + @"</td></tr>
<tr><td>Name:</td><td>" + userInfo.FirstName + " " + userInfo.LastName + @"</td></tr>
<tr><td>Phone:</td><td>" + formData.phone + @"</td></tr>
<tr><td>Region:</td><td>" + regionName + @"</td></tr>
<tr><td>Job:</td><td>" + templateName + @"</td></tr>
</table>
<a href='" + url + @"/email/approveuser/" + user.ID + @"'>Approve</a>
<a href='" + url + @"/email/denyuser/" + user.ID + @"'>Deny</a>
</html>
";
if (!string.IsNullOrEmpty(admin))
{
SendEmail(admin, subject, body);
}
}
private void HandleUpdate(UpdateSettingModel formData)
{
TableOperations <ConfirmableUserAccount> userAccountTable = m_dataContext.Table <ConfirmableUserAccount>();
ConfirmableUserAccount userAccount = userAccountTable.QueryRecordWhere("Name = {0}", formData.sid);
string url = m_dataContext.Connection.ExecuteScalar <string>("SELECT Value FROM DashSettings WHERE Name = 'System.URL'");
string emailServiceName = GetEmailServiceName();
string recipient, subject, body;
string phone = formData.phone;
string carrier = formData.carrier;
if (!string.IsNullOrEmpty(phone) && !string.IsNullOrEmpty(carrier) && carrier != "0")
{
phone = new string(formData.phone.Where(char.IsDigit));
userAccount.Phone = $"{phone}@{carrier}";
userAccount.PhoneConfirmed = false;
// generate code for sms confirmation
string code = Random.Int32Between(0, 999999).ToString("D6");
s_memoryCache.Set("sms" + userAccount.ID.ToString(), code, new CacheItemPolicy {
SlidingExpiration = TimeSpan.FromDays(1)
});
recipient = userAccount.Phone;
subject = $"{emailServiceName} requires you to confirm your SMS number.";
body = $"From your workstation, input {code} at {url}/email/verify/sms";
SendEmail(recipient, subject, body);
}
userAccountTable.UpdateRecord(userAccount);
UpdateUserAccountAssetGroup(userAccount, formData);
UpdateUserAccountEmailType(userAccount, formData.job, false);
UpdateUserAccountEmailType(userAccount, formData.sms, true);
recipient = userAccount.Email;
subject = $"{emailServiceName} subscriptions updated";
body = $"Your {emailServiceName} subscriptions have been updated. Visit {url}/email/UpdateSettings to review your subscriptions.";
SendEmail(recipient, subject, body);
}
private void UpdateUserAccountEmailType(ConfirmableUserAccount userAccount, IEnumerable <int> emailTypeIDs, bool sms)
{
using (DataContext dataContext = new DataContext("dbOpenXDA"))
{
// update links between user account and email type
EmailCategory eventEmailCategory = dataContext.Table <EmailCategory>().QueryRecordWhere("Name = 'Event'");
DataTable userAccountEmailTypeDataTable = dataContext.Connection.RetrieveData(UserAccountEmailTypeQuery, userAccount.ID, eventEmailCategory.ID, sms);
IEnumerable <int> userAccountEmailTypeIDs = userAccountEmailTypeDataTable.Select().Select(x => (int)x["EmailTypeID"]);
// formData will come back as null instead of empty array ....
if (emailTypeIDs == null)
{
emailTypeIDs = new List <int>();
}
// First pass. Add Link in database if the link does not exist.
foreach (int id in emailTypeIDs)
{
if (!userAccountEmailTypeIDs.Contains(id))
{
UserAccountEmailType userAccountEmailType = new UserAccountEmailType();
userAccountEmailType.UserAccountID = userAccount.ID;
userAccountEmailType.EmailTypeID = id;
dataContext.Table <UserAccountEmailType>().AddNewRecord(userAccountEmailType);
}
}
userAccountEmailTypeDataTable = dataContext.Connection.RetrieveData(UserAccountEmailTypeQuery, userAccount.ID, eventEmailCategory.ID, sms);
// Second pass. Remove Link if the link does not exist in data from form.
foreach (DataRow link in userAccountEmailTypeDataTable.Rows)
{
if (!emailTypeIDs.Contains((int)link["EmailTypeID"]))
{
dataContext.Table <UserAccountEmailType>().DeleteRecordWhere("ID = {0}", (int)link["UserAccountEmailTypeID"]);
}
}
}
}
private void HandleSignUp(UpdateSettingModel formData)
{
using (DataContext dataContext = new DataContext("dbOpenXDA"))
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
UserInfo userInfo = new UserInfo(System.Web.HttpContext.Current.User.Identity.Name);
userInfo.Initialize();
//// Create new user
dataContext.Connection.ExecuteNonQuery("INSERT INTO UserAccount (Name, Email, EmailConfirmed, FirstName, LastName) VALUES ({0}, {1}, {2}, {3}, {4})", formData.sid, userInfo.Email, true, userInfo.FirstName, userInfo.LastName);
HandleUpdate(formData);
// email system admin for approval
ConfirmableUserAccount user = dataContext.Table <ConfirmableUserAccount>().QueryRecordWhere("Name = {0}", formData.sid);
IEnumerable <int> regionData = formData.region ?? Enumerable.Empty <int>();
IEnumerable <int> jobData = formData.job ?? Enumerable.Empty <int>();
IEnumerable <int> smsData = formData.sms ?? Enumerable.Empty <int>();
string assetGroupIDList = string.Join(",", regionData);
string emailTypeIDList = string.Join(",", jobData);
string smsEmailTypeIDList = string.Join(",", smsData);
IEnumerable <AssetGroup> assetGroup = Enumerable.Empty <AssetGroup>();
IEnumerable <XSLTemplate> emailTemplate = Enumerable.Empty <XSLTemplate>();
IEnumerable <XSLTemplate> smsTemplate = Enumerable.Empty <XSLTemplate>();
if (assetGroupIDList.Length > 0)
{
assetGroup = dataContext.Table <AssetGroup>().QueryRecordsWhere($"ID IN ({assetGroupIDList})");
}
if (emailTypeIDList.Length > 0)
{
emailTemplate = dataContext.Table <XSLTemplate>().QueryRecordsWhere($"ID IN (SELECT XSLTemplateID FROM EmailType WHERE ID IN ({emailTypeIDList}))");
}
if (smsEmailTypeIDList.Length > 0)
{
smsTemplate = dataContext.Table <XSLTemplate>().QueryRecordsWhere($"ID IN (SELECT XSLTemplateID FROM EmailType WHERE ID IN ({smsEmailTypeIDList}))");
}
string url = connection.ExecuteScalar <string>("SELECT AltText1 FROM ValueList WHERE Text = 'URL' AND GroupID = (SELECT ID FROM ValueListGroup WHERE Name = 'System')");
string admin = dataContext.Connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Email.AdminAddress'");
string emailTemplateName = (emailTemplate.Any() ? string.Join(", ", emailTemplate.Select(x => x.Name)) : "None");
string smsTemplateName = (smsTemplate.Any() ? string.Join(", ", smsTemplate.Select(x => x.Name)) : "None");
string regionName = (assetGroup.Any() ? string.Join(", ", assetGroup.Select(x => x.Name)) : "None");
string emailServiceName = GetEmailServiceName();
string subject = $"{formData.username} requests access to the {emailServiceName}.";
string body = @"
<html>
<p>" + formData.username + @" requests access to the " + emailServiceName + @".</p>
<table>
<tr><td>Email:</td><td>" + userInfo.Email + @"</td></tr>
<tr><td>Name:</td><td>" + userInfo.FirstName + " " + userInfo.LastName + @"</td></tr>
<tr><td>Phone:</td><td>" + formData.phone + @"</td></tr>
<tr><td>Region:</td><td>" + regionName + @"</td></tr>
<tr><td>Email Template:</td><td>" + emailTemplateName + @"</td></tr>
<tr><td>SMS Template:</td><td>" + smsTemplateName + @"</td></tr>
</table>
<a href='" + url + @"/email/approveuser/" + user.ID + @"'>Approve</a>
<a href='" + url + @"/email/denyuser/" + user.ID + @"'>Deny</a>
</html>
";
if (!string.IsNullOrEmpty(admin))
{
SendEmail(admin, subject, body);
}
}
}
