public static bool IsSuperUser(string CurrentUser, string DefaultConnection)
{
bool response = false;
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(DefaultConnection);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
// Get the user
var objUser = (from user in context.AdefHelpDeskUsers
where user.Username == CurrentUser
select user).FirstOrDefault();
if (objUser != null)
{
return(response = (objUser.IsSuperUser));
}
else
{
response = false;
}
}
return(response);
}
public static DTOStatus DeleteRole(int id, string ConnectionString)
{
// Status to return
DTOStatus objDTOStatus = new DTOStatus();
objDTOStatus.Success = true;
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(ConnectionString);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
try
{
var objRole = context.AdefHelpDeskRoles.SingleOrDefaultAsync(x => x.Id == id).Result;
if (objRole == null)
{
objDTOStatus.StatusMessage = $"id #{id} Not Found";
objDTOStatus.Success = false;
}
context.AdefHelpDeskRoles.Remove(objRole);
context.SaveChanges();
}
catch (Exception ex)
{
objDTOStatus.StatusMessage = ex.GetBaseException().Message;
objDTOStatus.Success = false;
}
}
return(objDTOStatus);
}
public static DTOUser UserFromUserId(int paramUser, string DefaultConnection)
{
DTOUser response = new DTOUser();
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(DefaultConnection);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
// Get the user
var objUser = (from user in context.AdefHelpDeskUsers
where user.UserId == paramUser
select user).FirstOrDefault();
if (objUser != null)
{
response.userId = objUser.UserId;
response.userName = objUser.Username;
response.email = objUser.Email;
response.firstName = objUser.FirstName;
response.lastName = objUser.LastName;
response.isSuperUser = objUser.IsSuperUser;
response.riapassword = objUser.Riapassword;
}
}
return(response);
}
public static List <int> UserRoleIdsForUser(string paramUser, string DefaultConnection)
{
List <int> response = new List <int>();
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(DefaultConnection);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
// Get User roles for user
var colUserRoles = (from user in context.AdefHelpDeskUsers
from UserRoles in context.AdefHelpDeskUserRoles
where UserRoles.UserId == user.UserId
where user.Username == paramUser
select UserRoles);
foreach (var item in colUserRoles)
{
response.Add(item.RoleId);
}
}
return(response);
}
private DTOStatus ResetVersionTable()
{
DTOStatus objDTOStatus = new DTOStatus();
objDTOStatus.Success = true;
objDTOStatus.StatusMessage = "";
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(GetConnectionString());
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
try
{
// Get all version records
var versions = (from version in context.AdefHelpDeskVersion
select version).ToList();
// Delete them
foreach (var version in versions)
{
context.AdefHelpDeskVersion.Remove(version);
context.SaveChanges();
}
}
catch (Exception ex)
{
objDTOStatus.Success = false;
objDTOStatus.StatusMessage = ex.GetBaseException().Message;
}
}
return(objDTOStatus);
}
public List <ApiSecurityDTO> Get()
{
// Collection to hold ApiSecuritys
List <ApiSecurityDTO> colApiSecurityDTOs = new List <ApiSecurityDTO>();
// Must be a Super Administrator to call this Method
if (!UtilitySecurity.IsSuperUser(this.User.Identity.Name, GetConnectionString()))
{
return(colApiSecurityDTOs);
}
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(GetConnectionString());
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
// This returns all ApiSecuritys in the database
colApiSecurityDTOs = (from objApiSecurity in context.AdefHelpDeskApiSecurity
select new ApiSecurityDTO
{
id = objApiSecurity.Id,
username = objApiSecurity.Username,
contactName = objApiSecurity.ContactName,
contactCompany = objApiSecurity.ContactCompany,
contactWebsite = objApiSecurity.ContactWebsite,
contactEmail = objApiSecurity.ContactEmail,
contactPhone = objApiSecurity.ContactPhone,
password = objApiSecurity.Password,
isActive = objApiSecurity.IsActive,
}).OrderBy(x => x.username).ToList();
}
return(colApiSecurityDTOs);
}
public static List <DTOUser> UsersForRoleId(int paramRoleID, string DefaultConnection)
{
List <DTOUser> response = new List <DTOUser>();
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(DefaultConnection);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
// Get Users for Role
var colUsers = (from user in context.AdefHelpDeskUsers
from UserRoles in context.AdefHelpDeskUserRoles
where UserRoles.User == user
where UserRoles.RoleId == paramRoleID
select user);
foreach (var item in colUsers)
{
DTOUser objDTOUser = new DTOUser();
objDTOUser.userId = item.UserId;
objDTOUser.userName = item.Username;
objDTOUser.firstName = item.FirstName;
objDTOUser.lastName = item.LastName;
objDTOUser.email = item.Email;
objDTOUser.isSuperUser = item.IsSuperUser;
response.Add(objDTOUser);
}
}
return(response);
}
public static int UserIdFromUserName(string paramUser, string DefaultConnection)
{
int response = -1;
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(DefaultConnection);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
// Get the user
var objUser = (from user in context.AdefHelpDeskUsers
where user.Username == paramUser
select user).FirstOrDefault();
if (objUser != null)
{
return(response = objUser.UserId);
}
else
{
response = -1;
}
}
return(response);
}
public GeneralSettings(string DefaultConnection)
{
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(DefaultConnection);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
var resuts = from Settings in context.AdefHelpDeskSettings
select Settings;
_SMTPServer = Convert.ToString(resuts.FirstOrDefault(x => x.SettingName == "SMTPServer").SettingValue);
_SMTPAuthendication = Convert.ToString(resuts.FirstOrDefault(x => x.SettingName == "SMTPAuthendication").SettingValue);
_SMTPSecure = Convert.ToBoolean(resuts.FirstOrDefault(x => x.SettingName == "SMTPSecure").SettingValue);
_SMTPUserName = Convert.ToString(resuts.FirstOrDefault(x => x.SettingName == "SMTPUserName").SettingValue);
_SMTPPassword = Convert.ToString(resuts.FirstOrDefault(x => x.SettingName == "SMTPPassword").SettingValue);
_SMTPFromEmail = Convert.ToString(resuts.FirstOrDefault(x => x.SettingName == "SMTPFromEmail").SettingValue);
_FileUploadPath = Convert.ToString(resuts.FirstOrDefault(x => x.SettingName == "FileUploadPath").SettingValue);
_StorageFileType = Convert.ToString(resuts.FirstOrDefault(x => x.SettingName == "StorageFileType").SettingValue);
_AzureStorageConnection = Convert.ToString(resuts.FirstOrDefault(x => x.SettingName == "AzureStorageConnection").SettingValue);
_UploadPermission = Convert.ToString(resuts.FirstOrDefault(x => x.SettingName == "UploadPermission").SettingValue);
_AllowRegistration = Convert.ToBoolean(resuts.FirstOrDefault(x => x.SettingName == "AllowRegistration").SettingValue);
_VerifiedRegistration = Convert.ToBoolean(resuts.FirstOrDefault(x => x.SettingName == "VerifiedRegistration").SettingValue);
_ApplicationName = Convert.ToString(resuts.FirstOrDefault(x => x.SettingName == "ApplicationName").SettingValue);
_ApplicationGUID = Convert.ToString(resuts.FirstOrDefault(x => x.SettingName == "ApplicationGUID").SettingValue);
}
}
public void UpdateAzureStorageConnection(string DefaultConnection, string AzureStorageConnection)
{
// Ensure there is a AdefHelpDesk Container
// Retrieve the connection string for use with the application.
string storageConnectionString = AzureStorageConnection;
// Check whether the connection string can be parsed.
CloudStorageAccount storageAccount = null;
CloudBlobContainer cloudBlobContainer = null;
if (CloudStorageAccount.TryParse(storageConnectionString, out storageAccount))
{
CloudBlobClient cloudBlobClient = storageAccount.CreateCloudBlobClient();
cloudBlobContainer = cloudBlobClient.GetContainerReference("adefhelpdesk-files");
cloudBlobContainer.CreateIfNotExistsAsync().Wait();
}
else
{
throw new Exception("Cannot create Azure Storage folder using this connection!");
}
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(DefaultConnection);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
var resuts = from Settings in context.AdefHelpDeskSettings
where Settings.SettingName == "AzureStorageConnection"
select Settings;
resuts.FirstOrDefault().SettingValue = Convert.ToString(AzureStorageConnection);
context.SaveChanges();
}
}
public static CategoryNode CreateCategory(CategoryNode categoryNode, string ConnectionString)
{
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(ConnectionString);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
var newCategoryNode = new AdefHelpDeskCategories();
newCategoryNode.CategoryName = categoryNode.NodeName;
if (categoryNode.ParentId > 0)
{
newCategoryNode.ParentCategoryId = categoryNode.ParentId;
}
else
{
newCategoryNode.ParentCategoryId = null;
}
newCategoryNode.Selectable = categoryNode.Selectable;
newCategoryNode.RequestorVisible = categoryNode.RequestorVisible;
context.AdefHelpDeskCategories.Add(newCategoryNode);
context.SaveChanges();
categoryNode.Id = newCategoryNode.CategoryId;
}
return(categoryNode);
}
public string GetCurrentVersion()
{
// Version object to return
string strVersion = "00.00.00";
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(GetConnectionString());
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
try
{
// There is actually a connection string
// Test it by trying to read the Version table
var result = (from version in context.AdefHelpDeskVersion
orderby version.VersionNumber descending
select version).FirstOrDefault();
// We have to find at least one Version record
if (result != null)
{
// Set Version number
strVersion = result.VersionNumber;
}
}
catch
{
strVersion = "00.00.00";
}
}
return(strVersion);
}
public static List <DTOUser> SuperUsers(string DefaultConnection)
{
List <DTOUser> response = new List <DTOUser>();
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(DefaultConnection);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
// Get the SuperUsers
var colUsers = (from user in context.AdefHelpDeskUsers
where user.IsSuperUser == true
select user);
foreach (var item in colUsers)
{
DTOUser objDTOUser = new DTOUser();
objDTOUser.userId = item.UserId;
objDTOUser.userName = item.Username;
objDTOUser.firstName = item.FirstName;
objDTOUser.lastName = item.LastName;
objDTOUser.email = item.Email;
objDTOUser.isSuperUser = item.IsSuperUser;
response.Add(objDTOUser);
}
}
return(response);
}
public IActionResult Index([FromBody] DTOVerification Verification)
{
// LoginStatus to return
LoginStatus objLoginStatus = new LoginStatus();
if ((Verification.userName != null) && (Verification.password != null) && (Verification.verificationCode != null))
{
// Get values passed
var paramUserName = Verification.userName.Trim();
var paramPassword = Verification.password.Trim();
var paramVerificationCode = Verification.verificationCode;
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(GetConnectionString());
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
// Test the Verification Code
var objAdefHelpDeskUser = (from AdefHelpDeskUsers in context.AdefHelpDeskUsers
where AdefHelpDeskUsers.Username == paramUserName
where AdefHelpDeskUsers.VerificationCode == paramVerificationCode
select AdefHelpDeskUsers).FirstOrDefault();
if (objAdefHelpDeskUser == null)
{
// Bad verification code
objLoginStatus.isLoggedIn = false;
objLoginStatus.status = "Incorrrect Verification Code.";
return(Ok(objLoginStatus));
}
// Sign the User in
var SignInResult = _signInManager.PasswordSignInAsync(
paramUserName, paramPassword, false, lockoutOnFailure: false).Result;
if (!SignInResult.Succeeded)
{
// Return the error
objLoginStatus.status = $"Could not sign user {paramUserName} in.";
objLoginStatus.isLoggedIn = false;
return(Ok(objLoginStatus));
}
else
{
// Clear the verification code
objAdefHelpDeskUser.VerificationCode = null;
context.SaveChanges();
// Return Success
objLoginStatus.status = $"User {paramUserName} signed in.";
objLoginStatus.isLoggedIn = true;
return(Ok(objLoginStatus));
}
}
}
objLoginStatus.isLoggedIn = false;
objLoginStatus.status = "Authentication Failure";
return(Ok(objLoginStatus));
}
private DTOStatus DeleteAllUsers()
{
DTOStatus objDTOStatus = new DTOStatus();
objDTOStatus.Success = true;
objDTOStatus.StatusMessage = "";
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(GetConnectionString());
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
try
{
context.Database.ExecuteSqlCommand("delete from ADefHelpDesk_Users");
context.Database.ExecuteSqlCommand("delete from AspNetUsers");
}
catch (Exception ex)
{
objDTOStatus.Success = false;
objDTOStatus.StatusMessage = ex.GetBaseException().Message;
}
}
return(objDTOStatus);
}
public async Task <IActionResult> Delete([FromRoute] int id)
{
// Must be a Super Administrator to call this Method
if (!UtilitySecurity.IsSuperUser(this.User.Identity.Name, GetConnectionString()))
{
return(BadRequest());
}
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(GetConnectionString());
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
var objApiSecurity = await context.AdefHelpDeskApiSecurity.SingleOrDefaultAsync(x => x.Id == id);
if (objApiSecurity == null)
{
return(NotFound());
}
context.AdefHelpDeskApiSecurity.Remove(objApiSecurity);
await context.SaveChangesAsync();
// Log to the System Log
Log.InsertSystemLog(
GetConnectionString(),
Constants.WebAPIAccountDeleted,
this.User.Identity.Name,
$"({this.User.Identity.Name}) Deleted Username: {objApiSecurity.Username}");
}
return(NoContent());
}
public static string DeleteTask(int TaskId, string DefaultConnection, string strCurrentUser)
{
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(DefaultConnection);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
var objTask = context.AdefHelpDeskTasks.SingleOrDefault(x => x.TaskId == TaskId);
#region Validate
if (objTask == null)
{
return("Task Not found");
}
if (context.AdefHelpDeskTaskDetails.FirstOrDefault(x => x.TaskId == objTask.TaskId) != null)
{
return("Must delete all Comments and Work items first.");
}
#endregion
// Delete associated records
var colTaskAssociations = from TaskAssociations in context.AdefHelpDeskTaskAssociations
where TaskAssociations.TaskId == objTask.TaskId
select TaskAssociations;
context.AdefHelpDeskTaskAssociations.RemoveRange(colTaskAssociations);
var colTaskCategories = from TaskCategories in context.AdefHelpDeskTaskCategories
where TaskCategories.TaskId == objTask.TaskId
select TaskCategories;
context.AdefHelpDeskTaskCategories.RemoveRange(colTaskCategories);
var colAdefHelpDeskLog = from AdefHelpDeskLog in context.AdefHelpDeskLog
where AdefHelpDeskLog.TaskId == objTask.TaskId
select AdefHelpDeskLog;
context.AdefHelpDeskLog.RemoveRange(colAdefHelpDeskLog);
context.SaveChanges();
// Log it
Log.InsertSystemLog(DefaultConnection,
Constants.TaskDetailDeletion,
strCurrentUser,
$"({strCurrentUser}) Deleted Task # {objTask.TaskId} ({objTask.Description})");
// Delete Task
context.AdefHelpDeskTasks.Remove(objTask);
context.SaveChanges();
}
return("");
}
public static string DeleteUser(int id, UserManager <ApplicationUser> _userManager, string ConnectionString, string strCurrentUser)
{
try
{
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(ConnectionString);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
// Get User
var objDTOUser = (from objuser in context.AdefHelpDeskUsers
.Include(role => role.AdefHelpDeskUserRoles)
where objuser.UserId == id
select objuser).FirstOrDefault();
if (objDTOUser == null)
{
return("NotFound");
}
// Cannot delete yourself
if (objDTOUser.Username == strCurrentUser)
{
return("You cannot delete your own account");
}
// Get user in UserManager
var objUser = _userManager.FindByNameAsync(objDTOUser.Username).Result;
// Delete all roles
foreach (var itemRole in objDTOUser.AdefHelpDeskUserRoles)
{
var objUserRole = context.AdefHelpDeskUserRoles.SingleOrDefaultAsync(x => x.UserRoleId == itemRole.UserRoleId).Result;
context.AdefHelpDeskUserRoles.Remove(objUserRole);
}
context.SaveChanges();
// Delete User in AdefHelpDeskUsers
context.AdefHelpDeskUsers.Remove(objDTOUser);
context.SaveChanges();
// Delete the User in UserManager
_userManager.DeleteAsync(objUser);
}
}
catch (Exception ex)
{
throw ex;
}
return("");
}
public LogSearchResult Logs([FromBody] SearchParameters searchData)
{
LogSearchResult objLogSearchResult = new LogSearchResult();
objLogSearchResult.LogList = new List <DTOLog>();
// Must be a Super Administrator to call this Method
if (!UtilitySecurity.IsSuperUser(this.User.Identity.Name, GetConnectionString()))
{
objLogSearchResult.errorMessage = "Must be a Super Administrator to call this Method";
return(objLogSearchResult);
}
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(GetConnectionString());
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
int intTaskId = Convert.ToInt32(searchData.searchString);
var QueryResult = (from Log in context.AdefHelpDeskLog
where Log.TaskId == intTaskId
select Log).OrderByDescending(l => l.LogId)
.Skip(searchData.rowsPerPage * (searchData.pageNumber - 1))
.Take(searchData.rowsPerPage).ToList();
List <DTOLog> colDTOLog = new List <DTOLog>();
foreach (var item in QueryResult)
{
// Get user
DTOUser objDTOUSer = UtilitySecurity.UserFromUserId(item.UserId, GetConnectionString());
// Create Log
DTOLog objDTOLog = new DTOLog();
objDTOLog.LogID = item.LogId;
objDTOLog.TaskID = item.TaskId;
objDTOLog.LogDescription = item.LogDescription;
objDTOLog.UserName = (objDTOUSer != null) ? objDTOUSer.userName : "******";
objDTOLog.DateCreated = item.DateCreated.ToShortDateString() + ' ' + item.DateCreated.ToShortTimeString();
colDTOLog.Add(objDTOLog);
}
objLogSearchResult.LogList = colDTOLog;
objLogSearchResult.totalRows = context.AdefHelpDeskLog.Where(x => x.TaskId == intTaskId).Count();
objLogSearchResult.errorMessage = string.Empty;
}
return(objLogSearchResult);
}
// Methods
#region public static DTOStatus UpdateCategory(int id, CategoryNode categoryNode, string ConnectionString)
public static DTOStatus UpdateCategory(int id, CategoryNode categoryNode, string ConnectionString)
{
// Status to return
DTOStatus objDTOStatus = new DTOStatus();
objDTOStatus.Success = true;
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(ConnectionString);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
var existingCategoryNode = context.AdefHelpDeskCategories.SingleOrDefault(x => x.CategoryId == id);
if (existingCategoryNode == null)
{
objDTOStatus.StatusMessage = $"id #{id} Not Found";
objDTOStatus.Success = false;
return(objDTOStatus);
}
// Update the Node
existingCategoryNode.CategoryName = categoryNode.NodeName;
if (categoryNode.ParentId > 0)
{
existingCategoryNode.ParentCategoryId = categoryNode.ParentId;
}
else
{
existingCategoryNode.ParentCategoryId = null;
}
existingCategoryNode.Selectable = categoryNode.Selectable;
existingCategoryNode.RequestorVisible = categoryNode.RequestorVisible;
context.Entry(existingCategoryNode).State = EntityState.Modified;
try
{
context.SaveChanges();
}
catch (DbUpdateConcurrencyException ex)
{
objDTOStatus.StatusMessage = ex.GetBaseException().Message;
objDTOStatus.Success = false;
return(objDTOStatus);
}
}
return(objDTOStatus);
}
// Methods
#region public static DTOUser GetUserMethod(int id, string ConnectionString)
public static DTOUser GetUserMethod(int id, string ConnectionString)
{
DTOUser objDTOUser = new DTOUser();
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(ConnectionString);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
// Get all possible roles to reduce database calls later
var AllRoles = (from role in context.AdefHelpDeskRoles
select role).ToList();
// Perform Search
var Result = (from user in context.AdefHelpDeskUsers
.Include(roles => roles.AdefHelpDeskUserRoles)
where user.UserId == id
select user).FirstOrDefault();
objDTOUser.userId = Result.UserId;
objDTOUser.userName = Result.Username;
objDTOUser.firstName = Result.FirstName;
objDTOUser.lastName = Result.LastName;
objDTOUser.isSuperUser = Result.IsSuperUser;
objDTOUser.email = Result.Email;
objDTOUser.password = ""; // Never send to UI - also no longer used
objDTOUser.riapassword = ""; // Never send to UI
objDTOUser.verificationCode = Result.VerificationCode;
objDTOUser.userRoles = new List <RoleDTO>();
foreach (var itemRole in Result.AdefHelpDeskUserRoles)
{
var objUserRole = AllRoles.Where(x => x.Id == itemRole.RoleId).FirstOrDefault();
RoleDTO objRoleDTO = new RoleDTO();
objRoleDTO.iD = objUserRole.Id;
objRoleDTO.portalID = objUserRole.PortalId;
objRoleDTO.roleName = objUserRole.RoleName;
objDTOUser.userRoles.Add(objRoleDTO);
}
}
return(objDTOUser);
}
public void UpdateApplicationGUID(string DefaultConnection, string ApplicationGUID)
{
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(DefaultConnection);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
var resuts = from Settings in context.AdefHelpDeskSettings
where Settings.SettingName == "ApplicationGUID"
select Settings;
resuts.FirstOrDefault().SettingValue = Convert.ToString(ApplicationGUID);
context.SaveChanges();
}
}
public static void InsertSystemLog(string DefaultConnection, string LogType, string UserName, string LogMessage)
{
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(DefaultConnection);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
AdefHelpDeskSystemLog objAdefHelpDeskSystemLog = new AdefHelpDeskSystemLog();
objAdefHelpDeskSystemLog.CreatedDate = DateTime.Now;
objAdefHelpDeskSystemLog.LogMessage = Extensions.Left(LogMessage, 4000);
objAdefHelpDeskSystemLog.LogType = LogType;
objAdefHelpDeskSystemLog.UserName = UserName;
context.AdefHelpDeskSystemLog.Add(objAdefHelpDeskSystemLog);
context.SaveChanges();
}
}
public static void InsertLog(string DefaultConnection, int TaskID, int UserID, string LogDescription)
{
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(DefaultConnection);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
AdefHelpDeskLog objAdefHelpDeskLog = new AdefHelpDeskLog();
objAdefHelpDeskLog.DateCreated = DateTime.Now;
objAdefHelpDeskLog.LogDescription = Extensions.Left(LogDescription, 499);
objAdefHelpDeskLog.TaskId = TaskID;
objAdefHelpDeskLog.UserId = UserID;
context.AdefHelpDeskLog.Add(objAdefHelpDeskLog);
context.SaveChanges();
}
}
public static string DeleteTaskDetail(int TaskDetailId, string DefaultConnection, string strCurrentUser)
{
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(DefaultConnection);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
var objTaskDetail = context.AdefHelpDeskTaskDetails.SingleOrDefault(x => x.DetailId == TaskDetailId);
if (objTaskDetail == null)
{
return("Task Detail Not Found");
}
// Get the Attachments of the current item
var colAttachments = from Attachment in context.AdefHelpDeskAttachments
where Attachment.DetailId == objTaskDetail.DetailId
select Attachment;
// Loop thru each Attachment
foreach (var objAttachment in colAttachments)
{
// Delete the file
DeleteExistingFile(objAttachment, DefaultConnection, strCurrentUser);
}
context.AdefHelpDeskAttachments.RemoveRange(colAttachments);
context.SaveChanges();
// Log it
Log.InsertSystemLog(DefaultConnection,
Constants.TaskDetailDeletion,
strCurrentUser,
$"({strCurrentUser}) Deleted TaskDetail # {objTaskDetail.DetailId} ({objTaskDetail.Description}) of Task # {objTaskDetail.TaskId}.");
// Delete TaskDetail
context.AdefHelpDeskTaskDetails.Remove(objTaskDetail);
context.SaveChanges();
}
return("");
}
private DTOStatus MakeUserASuperUser(string UserName)
{
DTOStatus objDTOStatus = new DTOStatus();
objDTOStatus.Success = true;
objDTOStatus.StatusMessage = "";
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(GetConnectionString());
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
try
{
// Get the user
var objUser = (from user in context.AdefHelpDeskUsers
where user.Username == UserName
select user).FirstOrDefault();
if (objUser != null)
{
// Update them
objUser.IsSuperUser = true;
context.SaveChanges();
}
else
{
objDTOStatus.Success = false;
objDTOStatus.StatusMessage = $"Cound not find {UserName} in database";
}
}
catch (Exception ex)
{
objDTOStatus.Success = false;
objDTOStatus.StatusMessage = ex.GetBaseException().Message;
}
}
return(objDTOStatus);
}
public static RoleDTO CreateRole(RoleDTO RoleDTO, string ConnectionString)
{
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(ConnectionString);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
var newRoleDTO = new AdefHelpDeskRoles();
newRoleDTO.RoleName = RoleDTO.roleName;
newRoleDTO.PortalId = -1;
context.AdefHelpDeskRoles.Add(newRoleDTO);
context.SaveChanges();
RoleDTO.iD = newRoleDTO.Id;
}
return(RoleDTO);
}
public static DTOStatus RunUpdateScripts(string _NewDatabaseVersion, IWebHostEnvironment _hostEnvironment, string ConnectionString)
{
DTOStatus objDTOStatus = new DTOStatus();
objDTOStatus.Success = true;
// Get the update scripts
Dictionary <int, string> ColScripts = UpdateScripts();
foreach (var sqlScript in ColScripts)
{
try
{
// Run the script
DTOVersion objVersion = GetDatabaseVersion(_NewDatabaseVersion, ConnectionString);
int intCurrentDatabaseVersion = ConvertVersionToInteger(objVersion.VersionNumber);
// Only run the script if it is higher than the
// current database version
if (sqlScript.Key > intCurrentDatabaseVersion)
{
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(ConnectionString);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
context.Database.ExecuteSqlCommand(GetSQLScript(sqlScript.Value, _hostEnvironment));
}
}
}
catch (Exception ex)
{
objDTOStatus.StatusMessage = ex.Message;
objDTOStatus.Success = false;
return(objDTOStatus);
}
}
return(objDTOStatus);
}
// Methods
#region public static SystemLogSearchResult SystemLogsMethod(SearchParameters searchData, string CurrentUser, string ConnectionString)
public static SystemLogSearchResult SystemLogsMethod(SearchParameters searchData, string CurrentUser, string ConnectionString)
{
SystemLogSearchResult objSystemLogSearchResult = new SystemLogSearchResult();
objSystemLogSearchResult.SystemLogList = new List <DTOSystemLog>();
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(ConnectionString);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
var QueryResult = (from systemLog in context.AdefHelpDeskSystemLog
select systemLog).OrderByDescending(l => l.LogId)
.Skip(searchData.rowsPerPage * (searchData.pageNumber - 1))
.Take(searchData.rowsPerPage).ToList();
List <DTOSystemLog> colDTOSystemLog = new List <DTOSystemLog>();
foreach (var item in QueryResult)
{
DTOSystemLog objDTOSystemLog = new DTOSystemLog();
objDTOSystemLog.LogID = item.LogId;
objDTOSystemLog.LogType = item.LogType;
objDTOSystemLog.LogMessage = item.LogMessage;
objDTOSystemLog.UserName = item.UserName ?? "";
objDTOSystemLog.CreatedDate = item.CreatedDate.ToShortDateString() + ' ' + item.CreatedDate.ToShortTimeString();
colDTOSystemLog.Add(objDTOSystemLog);
}
objSystemLogSearchResult.SystemLogList = colDTOSystemLog;
objSystemLogSearchResult.totalRows = context.AdefHelpDeskSystemLog.Count();
objSystemLogSearchResult.errorMessage = string.Empty;
}
return(objSystemLogSearchResult);
}
// Helpers
#region public static DTOVersion GetDatabaseVersion(string NewDatabaseVersion, string ConnectionString)
public static DTOVersion GetDatabaseVersion(string NewDatabaseVersion, string ConnectionString)
{
// Version object to return
DTOVersion objVersion = new DTOVersion();
// If Version returned is NewDatabaseVersion
// we will assume the Version table does not exist
objVersion.VersionNumber = NewDatabaseVersion;
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(ConnectionString);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
try
{
// There is actually a connection string
// Test it by trying to read the Version table
var result = (from version in context.AdefHelpDeskVersion
orderby version.VersionNumber descending
select version).FirstOrDefault();
// We have to find at least one Version record
if (result != null)
{
// Set Version number
objVersion.VersionNumber = result.VersionNumber;
}
}
catch
{
// Do nothing if we cannot connect
// the method will return NewDatabaseVersion
}
}
return(objVersion);
}