public int saveTask(DTOTask task)
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "prc_GuardarTarea";
cmd.Parameters.AddWithValue("@cod_tarea", task.Task_Code);
cmd.Parameters.AddWithValue("@desc_tarea", task.Task_Description);
cmd.Parameters.AddWithValue("@dur_aproximada", task.Task_Duration);
cmd.Parameters.AddWithValue("@fecha", task.Task_Date);
cmd.Parameters.AddWithValue("@nom_archivo", task.Task_FileName);
try
{
if (verifyTask(task.Task_Code) == true)
{
return(3);
}
else
{
con.Open();
cmd.ExecuteNonQuery();
return(1);
}
}
catch (Exception ex)
{
Console.WriteLine("Error" + ex.Message);
con.Close();
return(0);
}
}
public void deleteWholeTask(DTOTask task)
{
using (DbContext)
{
// Get task, highlight, complete and timer
var wholeTask = taskRepository.getTaskPanelByTaskId(DbContext, task);
// Remove task, highlight, complete and timer
if (wholeTask.Highlight.Id > 0)
{
taskRepository.deleteHighlightColor(DbContext, wholeTask.Highlight);
}
if (wholeTask.Complete.Id > 0)
{
taskRepository.deleteComplete(DbContext, wholeTask.Complete);
}
if (wholeTask.Timer.Id > 0)
{
taskRepository.deleteTimer(DbContext, wholeTask.Timer);
}
if (wholeTask.Task.Id > 0)
{
taskRepository.deleteTask(DbContext, wholeTask.Task);
}
// Save changes
DbContext.SaveChanges();
}
}
/// <summary>
/// Insert a task in db.
/// </summary>
/// <param name="context">Context of the database.</param>
/// <param name="task">Task to be inserted.</param>
/// <returns>The id of the inserted record.</returns>
public int insertTaskAndSaveChanges(IDbContext context, DTOTask task)
{
// Get entity from dto
var entity = Mapper.mapEntityTask(task);
return(insertAndSaveChanges <Task>(context, entity));
}
public DTOTask RetrieveTask([FromBody] DTOTask paramDTOTask)
{
int intUserID = UtilitySecurity.UserIdFromUserName(this.User.Identity.Name, GetConnectionString());
bool IsAdministrator = UtilitySecurity.IsAdministrator(this.User.Identity.Name, GetConnectionString());
return(GetTask(paramDTOTask, intUserID, IsAdministrator, GetConnectionString(), this.User.Identity.Name, this.User.Identity.IsAuthenticated));
}
/// <summary>
/// Get all the task by requirement id as iqueryable.
/// </summary>
/// <param name="context">context of the database.</param>
/// <param name="task">Task that contains the requirement id fk.</param>
/// <returns>An iqueryable collection.</returns>
private IQueryable <Task> getAllByReqId(IDbContext context, DTOTask task)
{
throwExceptionIfForeignKeyInvalid(task.RequirementId);
var query = getAll <Task>(context).Where(x => x.RequirementId == task.RequirementId).AsQueryable();
return(query);
}
// Task ================================================
/// <summary>
/// Get the number of completed tas for a requirement.
/// </summary>
/// <param name="context">context of the database.</param>
/// <param name="task">task that contains the requirement id to filter by.</param>
/// <returns>An integer containing the number of completed tasks.</returns>
public int getCompletedTasksNumber(IDbContext context, DTOTask task)
{
var query = (from taskRecord in getAllByReqId(context, task)
join completeRecord in getAll <Complete>(context) on taskRecord.Id equals completeRecord.TaskId
where completeRecord.IsComplete
select task.Id).Count();
return(query);
}
/// <summary>
/// Check if tasks exist by requirement id.
/// </summary>
/// <param name="context">Context of the database.</param>
/// <param name="task">Task tha contains the project id to check the existance of the task.</param>
/// <returns>True if tasks exists for that project id. Otherwise false.</returns>
public bool tasksExistByRequirementId(IDbContext context, DTOTask task)
{
// Throw exception if requirement id is not valid
throwExceptionIfForeignKeyInvalid(task.RequirementId);
var query = getAll <Task>(context).Where(x => x.RequirementId == task.RequirementId).Any();
return(query);
}
// Highlight
public DTOHighlightColor getRandomHighlight(DTOTask task)
{
var randonColor = r.Next(0, 4);
DTOHighlightColor highlight = new DTOHighlightColor
{
Color = randonColor,
TaskId = task.Id
};
return(highlight);
}
// Timer
public DTOTimer getRandomTimer(DTOTask task)
{
var randonSeconds = getRLong();
DTOTimer timer = new DTOTimer
{
TimeInSeconds = randonSeconds,
TaskId = task.Id
};
return(timer);
}
// Complete
public DTOComplete getRandomCompleteWithIsCompleteTrue(DTOTask task)
{
var randonFinalizationDate = getRandomDate();
DTOComplete complete = new DTOComplete
{
IsComplete = true,
FinalizationDate = randonFinalizationDate,
TaskId = task.Id
};
return(complete);
}
// Task
public DTOTask getRandomTask(DTORequirements requirement)
{
var randonDescription = "descrp-" + getRString();
var randonCreationDate = getRandomDate();
var taskToInsert = new DTOTask
{
RequirementId = requirement.Id,
Description = randonDescription,
CreationDate = randonCreationDate
};
return(taskToInsert);
}
public DataTable listTasks(DTOTask task)
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "prc_ListarTareas";
DataTable data = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
con.Open();
sda.Fill(data);
con.Close();
return(data);
}
/// <summary>
/// Update a task in db.
/// </summary>
/// <param name="context">Context of the database.</param>
/// <param name="task">Task to be updated.</param>
public void updateTask(IDbContext context, DTOTask task)
{
// Get old entity
var oldEntity = getById <Task>(context, Mapper.mapEntityTask(task)).FirstOrDefault();
// Get new entity and set the dates to the ones in old entity
var newEntity = Mapper.mapEntityTask(task);
newEntity.RequirementId = oldEntity.RequirementId;
newEntity.CreationDate = oldEntity.CreationDate;
// Update
update <Task>(context, newEntity, oldEntity);
}
/// <summary>
/// Update a task.
/// </summary>
/// <param name="task">Task to be updated.</param>
public void updateTask(DTOTask task)
{
using (DbContext)
{
// Validate task to update
if (!taskToUpdateIsOk(task))
{
return;
}
// Update task and save changes
taskRepository.updateTask(DbContext, task);
DbContext.SaveChanges();
}
}
public static DTOTask MapAPITaskToTask(DTOAPITask objTask, DTOAPITaskDetail objTaskDetail)
{
DTOTask paramTask = new DTOTask();
paramTask.assignedRoleId = objTask.assignedRoleId ?? -1;
paramTask.assignedRoleName = objTask.assignedRoleName ?? "";
paramTask.createdDate = objTask.createdDate ?? "";
paramTask.description = objTask.description ?? "";
paramTask.dueDate = objTask.dueDate ?? "";
paramTask.estimatedCompletion = objTask.estimatedCompletion ?? "";
paramTask.estimatedHours = objTask.estimatedHours;
paramTask.estimatedStart = objTask.estimatedStart ?? "";
paramTask.portalId = objTask.portalId ?? -1;
paramTask.priority = objTask.priority ?? "";
paramTask.requesterEmail = objTask.requesterEmail ?? "";
paramTask.requesterName = objTask.requesterName ?? "";
paramTask.requesterPhone = objTask.requesterPhone ?? "";
paramTask.requesterUserId = objTask.requesterUserId ?? -1;
paramTask.selectedTreeNodes = objTask.selectedTreeNodes;
paramTask.sendEmails = objTask.sendEmails ?? true;
paramTask.status = objTask.status ?? "";
paramTask.taskId = objTask.taskId ?? -1;
paramTask.ticketPassword = objTask.ticketPassword ?? "";
if (objTaskDetail != null)
{
DTOTaskDetail paramDTOTaskDetail = new DTOTaskDetail();
paramDTOTaskDetail.colDTOAttachment = new List <DTOAttachment>();
paramDTOTaskDetail.contentType = objTaskDetail.contentType ?? "";
paramDTOTaskDetail.description = objTaskDetail.taskDetailDescription ?? "";
paramDTOTaskDetail.detailId = objTaskDetail.detailId ?? -1;
paramDTOTaskDetail.detailType = objTaskDetail.detailType ?? "";
paramDTOTaskDetail.emailDescription = objTaskDetail.emailDescription ?? "";
paramDTOTaskDetail.insertDate = objTaskDetail.insertDate ?? "";
paramDTOTaskDetail.sendEmails = objTaskDetail.sendTaskDetailEmails ?? false;
paramDTOTaskDetail.startTime = objTaskDetail.startTime ?? "";
paramDTOTaskDetail.stopTime = objTaskDetail.stopTime ?? "";
paramDTOTaskDetail.userId = objTaskDetail.userId ?? -1;
paramDTOTaskDetail.userName = objTaskDetail.userName ?? "";
paramTask.colDTOTaskDetail = new List <DTOTaskDetail>();
paramTask.colDTOTaskDetail.Add(paramDTOTaskDetail);
}
return(paramTask);
}
/// <summary>
/// Insert a task in db.
/// </summary>
/// <param name="task">Task to be inserted.</param>
/// <returns>The id of the inserted record.</returns>
public int insertTask(DTOTask task)
{
using (DbContext)
{
// Validate insert task
if (!taskToInsertIsOk(task))
{
return(-1);
}
// Set the creation date
task.CreationDate = DateTime.Now;
// Insert task and return id
return(taskRepository.insertTaskAndSaveChanges(DbContext, task));
}
}
public DTOTaskDetailResponse CreateUpdateTaskDetail(DTOAPITask objTask, DTOAPITaskDetail objTaskDetail, IFormFile objFile)
{
DTOTaskDetailResponse objDTOStatus = new DTOTaskDetailResponse();
objDTOStatus.isSuccess = true;
objDTOStatus.message = "";
objDTOStatus.taskDetail = new DTOTaskDetail();
// Get Settings
string CurrentHostLocation = $"{this.Request.Scheme}://{this.Request.Host}{this.Request.PathBase}";
string ContentRootPath = _hostEnvironment.ContentRootPath;
string strCurrentUser = this.User.Claims.FirstOrDefault().Value;
string strConnectionString = GetConnectionString();
int intUserId = -1;
bool IsSuperUser = true;
bool IsAdministrator = true;
bool IsAuthenticated = true;
try
{
DTOTask paramTask = ExternalAPIUtility.MapAPITaskToTask(objTask, objTaskDetail);
objDTOStatus = UploadTaskController.InsertUpdateTaskDetailMethod(
strConnectionString,
CurrentHostLocation,
ContentRootPath,
paramTask,
objFile,
strCurrentUser,
intUserId,
IsSuperUser,
IsAdministrator,
strCurrentUser,
IsAuthenticated);
}
catch (Exception ex)
{
objDTOStatus.isSuccess = false;
objDTOStatus.message = ex.GetBaseException().Message;
}
return(objDTOStatus);
}
public int deleteTask(DTOTask task)
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "prc_EliminarTarea";
cmd.Parameters.AddWithValue("@cod_tarea", task.Task_Code);
try
{
con.Open();
cmd.ExecuteNonQuery();
return(1);
}
catch (Exception ex)
{
Console.WriteLine("Error" + ex.Message);
con.Close();
return(0);
}
}
// Task ==================================================
/// <summary>
/// Get all tasks with project and requirement names and ids.
/// </summary>
/// <param name="requirement">Requirement to filter by.</param>
/// <returns>A DTO Task view with the collection of tasks, ids and names for both requirement and project.</returns>
public DTOTaskView getTaskView(DTOTask task)
{
using (DbContext)
{
// Get the parts of the dto task view
var idsAndNames = taskRepository.getIdsAndNamesForProjectAndRequirement(DbContext, task);
var listDTOTaskPanel = taskRepository.getListTaskPanelByRequirementId(DbContext, task);
var numberOfCompletedTasks = taskRepository.getCompletedTasksNumber(DbContext, task);
var numberOfTotalTasks = taskRepository.getAllTasksNumber(DbContext, task);
// Fill the dto task view
return(new DTOTaskView
{
IdsNames = idsAndNames,
ListTaskPanels = listDTOTaskPanel,
CompletedTasks = numberOfCompletedTasks,
TotalTasks = numberOfTotalTasks
});
}
}
public DTOTaskStatus GetTask(int TaskId)
{
DTOTaskStatus objDTOStatus = new DTOTaskStatus();
objDTOStatus.Success = true;
objDTOStatus.StatusMessage = "";
// Get Settings
string CurrentHostLocation = $"{this.Request.Scheme}://{this.Request.Host}{this.Request.PathBase}";
string ContentRootPath = _hostEnvironment.ContentRootPath;
string strCurrentUser = this.User.Claims.FirstOrDefault().Value;
string strConnectionString = GetConnectionString();
int intUserId = -1;
bool IsAdministrator = true;
bool IsAuthenticated = true;
try
{
DTOTask obJDTOTask = new DTOTask();
obJDTOTask.taskId = TaskId;
obJDTOTask.ticketPassword = "";
objDTOStatus.Task = TaskController.GetTask(
obJDTOTask,
intUserId,
IsAdministrator,
strConnectionString,
strCurrentUser,
IsAuthenticated);
}
catch (Exception ex)
{
objDTOStatus.Success = false;
objDTOStatus.StatusMessage = ex.GetBaseException().Message;
}
return(objDTOStatus);
}
public DTOStatus UpdateTask(DTOAPITask objTask)
{
DTOStatus objDTOStatus = new DTOStatus();
objDTOStatus.Success = true;
objDTOStatus.StatusMessage = "";
// Get Settings
string CurrentHostLocation = $"{this.Request.Scheme}://{this.Request.Host}{this.Request.PathBase}";
string ContentRootPath = _hostEnvironment.ContentRootPath;
string strCurrentUser = this.User.Claims.FirstOrDefault().Value;
string strConnectionString = GetConnectionString();
int intUserId = -1;
bool IsAuthenticated = true;
try
{
DTOTask paramTask = ExternalAPIUtility.MapAPITaskToTask(objTask, null);
objDTOStatus = UploadTaskController.UpdateTaskMethod(
strConnectionString,
CurrentHostLocation,
ContentRootPath,
paramTask,
strCurrentUser,
intUserId,
IsAuthenticated);
}
catch (Exception ex)
{
objDTOStatus.Success = false;
objDTOStatus.StatusMessage = ex.GetBaseException().Message;
}
return(objDTOStatus);
}
/// <summary>
/// Get all tasks by a requirement id. It also gets the id and name of the project and requirement.
/// </summary>
/// <param name="context">Context of the database.</param>
/// <param name="requirement">Requeriment to filter by.</param>
/// <returns>A DTO Task view.</returns>
public DTOIdsNames getIdsAndNamesForProjectAndRequirement(IDbContext context, DTOTask task)
{
// Throw exception if keys are invalid
throwExceptionIfForeignKeyInvalid(task.RequirementId);
// Get ids and names of project and requirement
var dtoIdsNames = (from projectRecord in getAll <Project>(context)
join requirementRecord in getAll <Requirement>(context)
on projectRecord.Id equals requirementRecord.ProjectId
where requirementRecord.Id == task.RequirementId
select new DTOIdsNames
{
ProjectId = projectRecord.Id,
ProjectName = projectRecord.Name,
RequirementId = requirementRecord.Id,
RequirementName = requirementRecord.Name
}).FirstOrDefault();
return(dtoIdsNames);
}
/// <summary>
/// Get all the tasks with the complete status, highlight, and timer.
/// </summary>
/// <param name="context">Context of the database.</param>
/// <param name="requirement">Requirement to filter by.</param>
/// <returns>A Collection of DTOTaskPanel.</returns>
public List <DTOTaskPanel> getListTaskPanelByRequirementId(IDbContext context, DTOTask task)
{
// Throw exception if keys are invalid
throwExceptionIfForeignKeyInvalid(task.RequirementId);
// Get defaults
var defaults = new DTOTaskPanel();
var query = (from taskRecord in getAll <Task>(context)
// left join highlight
join highlightRecord in getAll <Highlight>(context)
on taskRecord.Id equals highlightRecord.TaskId into joinHighlight
from highLight in joinHighlight.DefaultIfEmpty()
// left join complete
join completeRecord in getAll <Complete>(context)
on taskRecord.Id equals completeRecord.TaskId into joinComplete
from complete in joinComplete.DefaultIfEmpty()
// left join timer
join timerRecord in getAll <Timer>(context)
on taskRecord.Id equals timerRecord.TaskId into joinTimer
from timer in joinTimer.DefaultIfEmpty()
where taskRecord.RequirementId == task.RequirementId
select new
{
Task = new { TaskId = taskRecord.Id, Description = taskRecord.Description, CreationDate = taskRecord.CreationDate },
Highlight = new
{
Color = (highLight == null) ? defaults.Highlight.Color : highLight.Color,
Id = (highLight == null) ? defaults.Highlight.Id : highLight.Id
},
Complete = new
{
IsComplete = (complete == null) ? defaults.Complete.IsComplete : complete.IsComplete,
Id = (complete == null) ? defaults.Complete.Id : complete.Id
},
Timer = new
{
TimeInSeconds = (timer == null) ? defaults.Timer.TimeInSeconds : timer.TimeInSeconds,
Id = (timer == null) ? defaults.Timer.Id : timer.Id,
}
// Map to the DTOTaskPanel
}).AsEnumerable().Select(x => new DTOTaskPanel {
Timer = new DTOTimer {
TimeInSeconds = x.Timer.TimeInSeconds, Id = x.Timer.Id
},
Complete = new DTOComplete {
IsComplete = x.Complete.IsComplete, Id = x.Complete.Id
},
Highlight = new DTOHighlightColor {
Color = x.Highlight.Color, Id = x.Highlight.Id
},
Task = new DTOTask {
Description = x.Task.Description, Id = x.Task.TaskId, CreationDate = x.Task.CreationDate
}
}).OrderByDescending(x => x.Task.CreationDate).ToList();
return(query);
}
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).Replace(@"\", @"/");
// 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);
}
// Validations ===========================================
/// <summary>
/// Validate if the task to insert is ok.
/// </summary>
/// <param name="tasks">Task to be inserted.</param>
/// <returns>True if task is ok. Otherwise false.</returns>
private bool taskToInsertIsOk(DTOTask tasks)
{
return(true);
}
/// <summary>
/// Get the number of total task for a requirement.
/// </summary>
/// <param name="context">context of the database.</param>
/// <param name="task">task that contains the requirement id to filter by.</param>
/// <returns>An integer containing the number of tasks for a requirement.</returns>
public int getAllTasksNumber(IDbContext context, DTOTask task)
{
var query = getAllByReqId(context, task).Count();
return(query);
}
/// <summary>
/// Remove a task from db.
/// </summary>
/// <param name="context">Context of the database.</param>
/// <param name="task">Task to remove.</param>
public void deleteTask(IDbContext context, DTOTask task)
{
delete <Task>(context, Mapper.mapEntityTask(task));
}
/// <summary>
/// Get a DTO task by id.
/// </summary>
/// <param name="context">Context of the database.</param>
/// <param name="task">Task to filter by.</param>
/// <returns>A DTO Task matching the criteria.</returns>
public DTOTask getTaskById(IDbContext context, DTOTask task)
{
var query = getById <Task>(context, Mapper.mapEntityTask(task)).FirstOrDefault();
return(Mapper.mapDTOTask(query));
}
/// <summary>
/// Validate if update is ok.
/// </summary>
/// <param name="task">Task to validate</param>
/// <returns>True if validation successful. Otherwiser false.</returns>
private bool taskToUpdateIsOk(DTOTask task)
{
return(true);
}
// 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);
}
