public static DTOTask GetTask(DTOTask paramDTOTask, int intUserID, bool IsAdministrator, string DefaultConnection, string strCurrentUser, bool IsAuthenticated)
{
DTOTask objTask = new DTOTask();
objTask.taskId = -1; // Task Not found
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(DefaultConnection);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
AdefHelpDeskTasks Result;
// Perform Search
if (paramDTOTask.ticketPassword != "")
{
// Using ticketPassword
Result = (from task in context.AdefHelpDeskTasks
.Include(details => details.AdefHelpDeskTaskDetails)
.Include(categories => categories.AdefHelpDeskTaskCategories)
where task.TicketPassword == paramDTOTask.ticketPassword
where task.TaskId == paramDTOTask.taskId
select task).FirstOrDefault();
var myresult = Result.TaskId;
}
else
{
// Using TaskId
Result = (from task in context.AdefHelpDeskTasks
.Include(details => details.AdefHelpDeskTaskDetails)
.Include(categories => categories.AdefHelpDeskTaskCategories)
where task.TaskId == paramDTOTask.taskId
select task).FirstOrDefault();
// Must be a Administrator or Requester to only use TaskId
if (!IsAdministrator)
{
if (!(Result.RequesterUserId == intUserID))
{
if (!UtilitySecurity.IsAdministrator(strCurrentUser, DefaultConnection))
{
return(objTask);
}
}
}
}
if (Result == null)
{
return(objTask);
}
objTask.taskId = Result.TaskId;
objTask.status = Result.Status;
objTask.assignedRoleId = Result.AssignedRoleId;
objTask.createdDate = Result.CreatedDate.ToShortDateString();
objTask.description = Result.Description;
objTask.dueDate = (Result.DueDate != null) ? Result.DueDate.Value.ToShortDateString() : "";
objTask.estimatedCompletion = (Result.EstimatedCompletion != null) ? Result.EstimatedCompletion.Value.ToShortDateString() : "";
objTask.estimatedHours = Result.EstimatedHours;
objTask.estimatedStart = (Result.EstimatedStart != null) ? Result.EstimatedStart.Value.ToShortDateString() : "";
objTask.portalId = Result.PortalId;
objTask.priority = Result.Priority;
objTask.requesterEmail = Result.RequesterEmail;
objTask.requesterName = Result.RequesterName;
objTask.requesterPhone = Result.RequesterPhone;
objTask.requesterUserId = Result.RequesterUserId;
objTask.ticketPassword = Result.TicketPassword;
// Set Requester Name
if (Result.RequesterUserId > 0)
{
var User = UtilitySecurity.UserFromUserId(Result.RequesterUserId, DefaultConnection);
objTask.requesterName = $"{User.firstName} {User.lastName}";
}
else
{
objTask.requesterName = Result.RequesterName;
}
// Add Task Categories
objTask.selectedTreeNodes = new List <int>();
foreach (var itemTaskCategory in Result.AdefHelpDeskTaskCategories)
{
objTask.selectedTreeNodes.Add(itemTaskCategory.CategoryId);
}
// Add Task Details
objTask.colDTOTaskDetail = new List <DTOTaskDetail>();
// Get all TaskDetails
var TaskDetails = Result.AdefHelpDeskTaskDetails.OrderByDescending(x => x.DetailId);
// Non-Admins can only see "Comment - Visible"
if (!IsAdministrator)
{
TaskDetails = TaskDetails.Where(x => x.DetailType == "Comment - Visible").OrderByDescending(x => x.DetailId);
}
else
{
TaskDetails = TaskDetails.OrderByDescending(x => x.DetailId);
}
foreach (var itemTaskDetail in TaskDetails)
{
DTOTaskDetail objDTOTaskDetail = new DTOTaskDetail();
objDTOTaskDetail.contentType = (itemTaskDetail.ContentType != null) ? itemTaskDetail.ContentType : Constants.TXT;
objDTOTaskDetail.description = itemTaskDetail.Description;
objDTOTaskDetail.detailId = itemTaskDetail.DetailId;
objDTOTaskDetail.detailType = itemTaskDetail.DetailType;
objDTOTaskDetail.insertDate = itemTaskDetail.InsertDate.ToLongDateString() + " " + itemTaskDetail.InsertDate.ToLongTimeString();
objDTOTaskDetail.startTime = (itemTaskDetail.StartTime != null) ? itemTaskDetail.StartTime.Value.ToShortDateString() + " " + itemTaskDetail.StartTime.Value.ToShortTimeString() : "";
objDTOTaskDetail.stopTime = (itemTaskDetail.StopTime != null) ? itemTaskDetail.StopTime.Value.ToShortDateString() + " " + itemTaskDetail.StopTime.Value.ToShortTimeString() : "";
objDTOTaskDetail.userId = itemTaskDetail.UserId;
objDTOTaskDetail.userName = UtilitySecurity.UserFromUserId(itemTaskDetail.UserId, DefaultConnection).userName;
// Add Attachments
objDTOTaskDetail.colDTOAttachment = new List <DTOAttachment>();
var AttachmentResults = (from attachment in context.AdefHelpDeskAttachments
where attachment.DetailId == objDTOTaskDetail.detailId
select attachment);
foreach (var itemAttachmement in AttachmentResults)
{
DTOAttachment objDTOAttachment = new DTOAttachment();
objDTOAttachment.attachmentID = itemAttachmement.AttachmentId;
//objDTOAttachment.attachmentPath = itemAttachmement.AttachmentPath; -- Do not send for security reasons
//objDTOAttachment.fileName = itemAttachmement.FileName; -- Do not send for security reasons
objDTOAttachment.originalFileName = itemAttachmement.OriginalFileName;
objDTOAttachment.userId = itemAttachmement.UserId.ToString();
objDTOTaskDetail.colDTOAttachment.Add(objDTOAttachment);
// If file type is .EML it is a Email
if (Path.GetExtension(itemAttachmement.OriginalFileName).ToUpper() == Constants.EML)
{
// Construct path
string FullFilePath = Path.Combine(itemAttachmement.AttachmentPath, itemAttachmement.FileName);
// Set Email Description and ContentType
SetEmailContents(itemAttachmement.FileName, itemAttachmement.AttachmentId, FullFilePath, DefaultConnection, ref objDTOTaskDetail);
objDTOTaskDetail.contentType = Constants.EML.Replace(".", "");
}
}
objTask.colDTOTaskDetail.Add(objDTOTaskDetail);
}
}
#region **** Save to the Log
if ((objTask.taskId != null) && (objTask.taskId != -1))
{
string strLogUserName = (IsAuthenticated) ? strCurrentUser : "******";
Log.InsertLog(DefaultConnection, Convert.ToInt32(objTask.taskId), intUserID, $"{strLogUserName} viewed ticket.");
}
#endregion
return(objTask);
}
public IActionResult Index()
{
// SearchTaskParameters to return
// Set important defaults
SearchTaskParameters objSearchTaskParameters = new SearchTaskParameters();
objSearchTaskParameters.priority = "";
objSearchTaskParameters.searchText = "";
objSearchTaskParameters.status = "";
objSearchTaskParameters.pageNumber = 1;
objSearchTaskParameters.rowsPerPage = 10;
// See if the user is logged in
if (this.User.Identity.IsAuthenticated)
{
// Get UserId
int UserId = UtilitySecurity.UserIdFromUserName(this.User.Identity.Name, GetConnectionString());
// Get the LastSearch
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(GetConnectionString());
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
var objLastSearch = context.AdefHelpDeskLastSearch
.Where(x => x.UserId == UserId)
.FirstOrDefault();
if (objLastSearch != null)
{
objSearchTaskParameters.id = objLastSearch.Id.ToString();
objSearchTaskParameters.userId = objLastSearch.UserId.ToString();
objSearchTaskParameters.assignedRoleId = objLastSearch.AssignedRoleId != null?objLastSearch.AssignedRoleId.ToString() : "";
objSearchTaskParameters.searchText = objLastSearch.SearchText != null?objLastSearch.SearchText.ToString() : "";
objSearchTaskParameters.status = objLastSearch.Status != null?objLastSearch.Status.ToString() : "";
objSearchTaskParameters.createdDate = objLastSearch.CreatedDate != null?objLastSearch.CreatedDate.Value.ToShortDateString() : "";
objSearchTaskParameters.dueDate = objLastSearch.DueDate != null?objLastSearch.DueDate.Value.ToShortDateString() : "";
objSearchTaskParameters.priority = objLastSearch.Priority != null?objLastSearch.Priority.ToString() : "";
objSearchTaskParameters.pageNumber = objLastSearch.CurrentPage ?? -1;
objSearchTaskParameters.rowsPerPage = objLastSearch.PageSize ?? -1;
// Categories (selectedTreeNodes)
objSearchTaskParameters.selectedTreeNodes = new List <int>();
if (objLastSearch.Categories != null)
{
string[] Categories = objLastSearch.Categories.Split(",");
foreach (var Category in Categories)
{
if (Category.Trim().Length > 0)
{
objSearchTaskParameters.selectedTreeNodes.Add(Convert.ToInt32(Category));
}
}
}
}
}
}
// Return the result
return(Ok(objSearchTaskParameters));
}
// Private Methods
#region public static TaskSearchResult SearchTasks(SearchTaskParameters searchData, int intUserId, int iSAdministrator, string DefaultConnection)
public static TaskSearchResult SearchTasks(SearchTaskParameters searchData, int intUserId, int iSAdministrator, string DefaultConnection)
{
TaskSearchResult objTaskSearchResult = new TaskSearchResult();
objTaskSearchResult.taskList = new List <DTOTask>();
List <AdefHelpDeskRoles> AllRoles = new List <AdefHelpDeskRoles>();
var resultTable = new DataTable();
//If searchData.rowsPerPage = 0 set it to 1
if (searchData.rowsPerPage == 0)
{
searchData.rowsPerPage = 1;
}
//If searchData.pageNumber = 0 set it to 1
if (searchData.pageNumber == 0)
{
searchData.pageNumber = 1;
}
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(DefaultConnection);
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
// Get all possible roles to reduce database calls later
AllRoles = (from role in context.AdefHelpDeskRoles
select role).ToList();
}
using (var conn = new SqlConnection(DefaultConnection))
{
using (var cmd = new SqlCommand())
{
cmd.CommandText = "[spSearchTasks]";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@paramIsAdmin", iSAdministrator);
cmd.Parameters.AddWithValue("@paramUserId", intUserId);
cmd.Parameters.AddWithValue("@paramSearchText", searchData.searchText ?? "");
cmd.Parameters.AddWithValue("@paramStatus", searchData.status ?? "");
cmd.Parameters.AddWithValue("@paramPriority", searchData.priority ?? "");
cmd.Parameters.AddWithValue("@paramCreatedDate", searchData.createdDate ?? "");
cmd.Parameters.AddWithValue("@paramDueDate", searchData.dueDate ?? "");
cmd.Parameters.AddWithValue("@paramAssignedRoleId", searchData.assignedRoleId ?? "");
cmd.Parameters.AddWithValue("@paramSelectedTreeNodes", String.Join(",", searchData.selectedTreeNodes));
cmd.Parameters.AddWithValue("@paramSortOrder", searchData.sortOrder ?? "");
cmd.Parameters.AddWithValue("@paramSortField", searchData.sortField ?? "");
cmd.Parameters.AddWithValue("@paramRowsPerPage", searchData.rowsPerPage);
cmd.Parameters.AddWithValue("@paramPageNumber", searchData.pageNumber);
SqlParameter parTotalCount = new SqlParameter("@paramTotalCount", SqlDbType.Int);
parTotalCount.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parTotalCount);
using (var adapter = new SqlDataAdapter(cmd))
{
adapter.Fill(resultTable);
}
objTaskSearchResult.totalRows = Convert.ToInt32(parTotalCount.Value);
}
}
List <DTOTask> colDTOTasks = new List <DTOTask>();
foreach (System.Data.DataRow item in resultTable.Rows)
{
DTOTask objDTOTask = new DTOTask();
objDTOTask.taskId = Convert.ToInt32(item.ItemArray[0]);
objDTOTask.portalId = Convert.ToInt32(item.ItemArray[1]);
objDTOTask.description = Convert.ToString(item.ItemArray[2]);
objDTOTask.status = Convert.ToString(item.ItemArray[3]);
objDTOTask.priority = Convert.ToString(item.ItemArray[4]);
objDTOTask.createdDate = Convert.ToDateTime(item.ItemArray[5]).ToShortDateString();
objDTOTask.estimatedStart = (item.ItemArray[6].ToString() != "") ? Convert.ToDateTime(item.ItemArray[6]).ToShortDateString() : "";
objDTOTask.estimatedCompletion = (item.ItemArray[7].ToString() != "") ? Convert.ToDateTime(item.ItemArray[7]).ToShortDateString() : "";
objDTOTask.dueDate = (item.ItemArray[8].ToString() != "") ? Convert.ToDateTime(item.ItemArray[8]).ToShortDateString() : "";
objDTOTask.assignedRoleId = Convert.ToInt32(item.ItemArray[9]);
objDTOTask.ticketPassword = Convert.ToString(item.ItemArray[10]);
objDTOTask.requesterUserId = Convert.ToInt32(item.ItemArray[11]);
objDTOTask.requesterName = Convert.ToString(item.ItemArray[12]);
objDTOTask.requesterEmail = Convert.ToString(item.ItemArray[13]);
objDTOTask.requesterPhone = Convert.ToString(item.ItemArray[14]);
if (item.ItemArray[15].ToString() != "")
{
objDTOTask.estimatedHours = Convert.ToInt32(item.ItemArray[15]);
}
// Set Requester Name
if (objDTOTask.requesterUserId > 0)
{
var User = UtilitySecurity.UserFromUserId(objDTOTask.requesterUserId.Value, DefaultConnection);
objDTOTask.requesterName = $"{User.firstName} {User.lastName}";
}
else
{
objDTOTask.requesterName = objDTOTask.requesterName;
}
// Set AssignedRoleName
var objUserRole = AllRoles.Where(x => x.Id == objDTOTask.assignedRoleId).FirstOrDefault();
if (objUserRole != null)
{
objDTOTask.assignedRoleName = objUserRole.RoleName;
}
else
{
objDTOTask.assignedRoleName = "[Unassigned]";
}
colDTOTasks.Add(objDTOTask);
}
objTaskSearchResult.taskList = colDTOTasks;
objTaskSearchResult.errorMessage = string.Empty;
return(objTaskSearchResult);
}
public IActionResult SaveSearchParameters([FromBody] SearchTaskParameters paramSearchTaskParameters)
{
// See if the user is logged in
try
{
if (this.User.Identity.IsAuthenticated)
{
// Get UserId
int UserId = UtilitySecurity.UserIdFromUserName(this.User.Identity.Name, GetConnectionString());
var optionsBuilder = new DbContextOptionsBuilder <ADefHelpDeskContext>();
optionsBuilder.UseSqlServer(GetConnectionString());
using (var context = new ADefHelpDeskContext(optionsBuilder.Options))
{
// First remove any existing records for the user
context.AdefHelpDeskLastSearch.RemoveRange(context.AdefHelpDeskLastSearch.Where(x => x.UserId == UserId));
context.SaveChanges();
AdefHelpDeskLastSearch objNewLastSearch = new AdefHelpDeskLastSearch();
objNewLastSearch.UserId = UserId;
objNewLastSearch.SearchText = (paramSearchTaskParameters.searchText != null) ? paramSearchTaskParameters.searchText.Trim() : "";
objNewLastSearch.Status = (paramSearchTaskParameters.status != null) ? paramSearchTaskParameters.status.Trim() : "";
objNewLastSearch.Priority = (paramSearchTaskParameters.priority != null) ? paramSearchTaskParameters.priority.Trim() : "";
objNewLastSearch.CurrentPage = paramSearchTaskParameters.pageNumber;
objNewLastSearch.PageSize = paramSearchTaskParameters.rowsPerPage;
if (paramSearchTaskParameters.userId != null)
{
objNewLastSearch.UserId = Convert.ToInt32(paramSearchTaskParameters.userId);
}
if (paramSearchTaskParameters.assignedRoleId != null)
{
objNewLastSearch.AssignedRoleId = Convert.ToInt32(paramSearchTaskParameters.assignedRoleId);
}
if (paramSearchTaskParameters.createdDate != null)
{
objNewLastSearch.CreatedDate = Utility.CastToDate(paramSearchTaskParameters.createdDate);
}
if (paramSearchTaskParameters.dueDate != null)
{
objNewLastSearch.DueDate = Utility.CastToDate(paramSearchTaskParameters.dueDate);
}
objNewLastSearch.Categories = string.Join(",", paramSearchTaskParameters.selectedTreeNodes);
// Save changes
context.AdefHelpDeskLastSearch.Add(objNewLastSearch);
context.SaveChanges();
}
}
}
catch
{
// Do nothing if search paramaters cannot be saved
}
return(NoContent());
}