public List <AtividadeDTO> ObterListaAtividades(int codigoFuncionario, int numeroPagina, int registrosPorPagina)
{
int salto = 0;
try
{
using (HelpDeskEntities contexto = new HelpDeskEntities(Util.ConnectionString))
{
if (numeroPagina > 1)
{
salto = (numeroPagina - 1) * registrosPorPagina;
}
var dados = (from a in contexto.Atividade
where a.CodigoFuncionario == codigoFuncionario
orderby a.DataInicio descending
select new AtividadeDTO
{
Codigo = a.Codigo,
Descricao = a.Descricao,
DataInicio = a.DataInicio,
DataFim = a.DataFim,
Status = a.Status.Descricao,
Solicitante = a.Solicitante.Nome
}).Skip(salto).Take(registrosPorPagina).ToList();
return(dados);
}
}
catch
{
return(null);
}
}
public List <AtividadeDTO> ObterListaAtividades(int codigoFuncionario)
{
try
{
using (HelpDeskEntities contexto = new HelpDeskEntities(Util.ConnectionString))
{
var dados = (from a in contexto.Atividade
where a.CodigoFuncionario == codigoFuncionario
orderby a.DataInicio descending
select new AtividadeDTO
{
Codigo = a.Codigo,
Descricao = a.Descricao,
DataInicio = a.DataInicio,
DataFim = a.DataFim,
Status = a.Status.Descricao,
Solicitante = a.Solicitante.Nome
}).ToList();
return(dados);
}
}
catch
{
return(null);
}
}
private List <TicketReportViewModel> GetReportTicket()
{
try
{
List <TicketReportViewModel> reportViewModels = new List <TicketReportViewModel>();
using (var db = new HelpDeskEntities())
{
var tickets = db.Tickets.ToList();
foreach (var item in tickets)
{
reportViewModels.Add(new TicketReportViewModel
{
TicketNo = item.TicketNo,
Subject = item.Subject,
Description = item.Description,
Status = GetTicketStatus(item.Status),
CreatedBy = item.CreatedBy,
CreationDate = item.CreationDate
});
}
}
return(reportViewModels);
}
catch (Exception ex)
{
throw ex;
}
}
private async Task <List <TicketViewModel> > GetTickets(string status, string userName)
{
if (string.IsNullOrWhiteSpace(SessionVar.GetString("displayName")))
{
Redirect("~/Account/Login");
}
var tickets = new List <TicketViewModel>();
using (var dataContext = new HelpDeskEntities())
{
var data = new List <Ticket>();
if (!string.IsNullOrWhiteSpace(status) && !string.IsNullOrWhiteSpace(userName))
{
data = await dataContext.Tickets.Include("Category").Where(t => t.IsDeleted.Equals(false) && t.Status.Equals(status) && t.CreatedBy.Equals(userName)).OrderByDescending(t => t.ModifiedDate).ToListAsync();
}
else if (!string.IsNullOrWhiteSpace(status) && string.IsNullOrWhiteSpace(userName))
{
data = await dataContext.Tickets.Include("Category").Where(t => t.IsDeleted.Equals(false) && t.Status.Equals(status)).OrderByDescending(t => t.ModifiedDate).ToListAsync();
}
else if (string.IsNullOrWhiteSpace(status) && !string.IsNullOrWhiteSpace(userName))
{
data = await dataContext.Tickets.Include("Category").Where(t => t.IsDeleted.Equals(false) && t.CreatedBy.Equals(userName)).OrderByDescending(t => t.ModifiedDate).ToListAsync();
}
else
{
data = await dataContext.Tickets.Include("Category").Where(t => t.IsDeleted.Equals(false)).OrderByDescending(t => t.ModifiedDate).ToListAsync();
}
if (data.Count > 0)
{
int i = 1;
foreach (Ticket ticket in data)
{
tickets.Add(new TicketViewModel()
{
SN = i,
TicketID = ticket.TicketID,
TicketNo = ticket.TicketNo,
Subject = ticket.Subject,
Description = ticket.Description,
Status = ticket.Status,
CreatedBy = ticket.CreatedBy,
CreationDate = ticket.CreationDate,
CategoryID = ticket.CategoryID,
ModifiedBy = ticket.ModifiedBy,
ModifiedDate = ticket.ModifiedDate,
Category = ticket.Category
});
i++;
}
}
}
return(tickets);
}
public async Task <ActionResult> RaiseTicket()
{
if (string.IsNullOrWhiteSpace(SessionVar.GetString("displayName")))
{
Redirect("~/Account/Login");
}
using (HelpDeskEntities dataContext = new HelpDeskEntities())
{
ViewBag.Categories = await dataContext.Categories.ToListAsync();
}
return(View());
}
public int Alterar(Status status)
{
try
{
using (HelpDeskEntities contexto = new HelpDeskEntities(Util.ConnectionString))
{
contexto.Entry(status).State = System.Data.EntityState.Modified;
return(contexto.SaveChanges());
}
}
catch (Exception)
{
return(-1);
}
}
public Classificacao Obter(int codigo)
{
try
{
using (HelpDeskEntities contexto = new HelpDeskEntities(Util.ConnectionString))
{
return((from c in contexto.Classificacao
where c.Codigo == codigo
select c).First());
}
}
catch (Exception)
{
return(null);
}
}
public List <Solicitante> ObterSolicitantes()
{
try
{
using (HelpDeskEntities contexto = new HelpDeskEntities(Util.ConnectionString))
{
return((from s in contexto.Solicitante
orderby s.Nome
select s).ToList());
}
}
catch (Exception)
{
return(null);
}
}
public int ObterQuantidadeAtividades(int codigoFuncionario)
{
try
{
using (HelpDeskEntities contexto = new HelpDeskEntities(Util.ConnectionString))
{
return((from a in contexto.Atividade
where a.CodigoFuncionario == codigoFuncionario
select a.Codigo).Count());
}
}
catch
{
return(-2);
}
}
public int Gravar(Solicitante sol)
{
try
{
using (HelpDeskEntities contexto =
new HelpDeskEntities(Util.ConnectionString))
{
contexto.Solicitante.Add(sol);
return(contexto.SaveChanges());
}
}
catch (Exception)
{
return(-1);
}
}
public List <Classificacao> ObterClassificacoes()
{
try
{
using (HelpDeskEntities contexto = new HelpDeskEntities(Util.ConnectionString))
{
return((from c in contexto.Classificacao
where c.Ativa == "S"
orderby c.Nome
select c).ToList());
}
}
catch (Exception)
{
return(null);
}
}
public async Task <ActionResult> AuditTicket(TicketViewModel model)
{
if (string.IsNullOrWhiteSpace(SessionVar.GetString("displayName")))
{
Redirect("~/Account/Login");
}
if (!HomeController.IsMember(User.Identity.Name, ConfigurationManager.AppSettings["ADIAG"].ToString()))
{
Redirect("~/Account/AccessDenied");
}
if (string.IsNullOrWhiteSpace(model.Description))
{
return(Json(new { type = "danger", header = "Error", message = "Please enter a note for your action." }));
}
StringBuilder errors = new StringBuilder();
Guid id = model.TicketID;
var isTicketUpdated = await IsTicketUpdated(model);
if (isTicketUpdated.Item1)
{
GeneralSetting generalSetting = null;
using (HelpDeskEntities dataContext = new HelpDeskEntities())
{
generalSetting = await dataContext.GeneralSettings.FirstOrDefaultAsync();
if (generalSetting != null)
{
Ticket ticket = await dataContext.Tickets.FirstOrDefaultAsync(param => param.TicketID == model.TicketID);
if (ticket != null)
{
// mail sender and ict
string recipients = ticket.OwnerEmail + "," + generalSetting.RecipientEmails;
SendEmail("ICT HelpDesk Re: [" + model.TicketNo + "] " + model.Subject, model.Description, recipients, id.ToString(), model.FileAttachments);
}
}
}
return(Json(new { type = "success", header = "Success", message = "" }));
}
return(Json(new { type = "danger", header = "Error", message = isTicketUpdated.Item2 }));
}
public List <Status> ObterStatus()
{
try
{
using (HelpDeskEntities contexto =
new HelpDeskEntities(Util.ConnectionString))
{
var dados = (from s in contexto.Status
where s.Ativo == "S"
select s).ToList();
return(dados);
}
}
catch
{
return(null);
}
}
public Funcionario ValidarAcesso(int codigo, string senha)
{
Funcionario func = null;
try
{
using (HelpDeskEntities contexto = new HelpDeskEntities(Util.ConnectionString))
{
return((from f in contexto.Funcionario
where f.Codigo == codigo && f.Senha == senha &&
f.Ativo == "S" && f.DataDemissao == null
select f).FirstOrDefault());
}
}
catch
{
return(func);
}
}
public async Task <ActionResult> UpdateTicket(TicketViewModel model)
{
if (string.IsNullOrWhiteSpace(SessionVar.GetString("displayName")))
{
Redirect("~/Account/Login");
}
StringBuilder errors = new StringBuilder();
Guid id = model.TicketID;
var isTicketUpdated = await IsTicketUpdated(model);
if (isTicketUpdated.Item1)
{
GeneralSetting generalSetting = null;
using (HelpDeskEntities dataContext = new HelpDeskEntities())
{
generalSetting = await dataContext.GeneralSettings.FirstOrDefaultAsync();
if (generalSetting != null)
{
if (generalSetting.EnableEmailAlert && !string.IsNullOrWhiteSpace(generalSetting.RecipientEmails))
{
Ticket ticket = await dataContext.Tickets.FirstOrDefaultAsync(param => param.TicketID == id);
string recipients = ticket.OwnerEmail + "," + generalSetting.RecipientEmails;
SendEmail("ICT HelpDesk Re: [" + model.TicketNo + "] " + model.Subject, model.Description, recipients, id.ToString(), model.FileAttachments);
//if (HomeController.IsMember(User.Identity.Name, ConfigurationManager.AppSettings["ADAG"].ToString()))
// SendEmail("ICT HelpDesk Re: [" + model.TicketNo + "] " + model.Subject, model.Description, model.OwnerEmail, id.ToString(), model.FileAttachments);
//else
// SendEmail("ICT HelpDesk Re: [" + model.TicketNo + "] " + model.Subject, model.Description, generalSetting.RecipientEmails, id.ToString(), model.FileAttachments);
}
}
}
return(RedirectToAction("TicketDetails", new RouteValueDictionary(new { controller = "Home", action = "TicketDetails", id = id })));
}
return(RedirectToAction("TicketDetails", new RouteValueDictionary(new { controller = "Home", action = "TicketDetails", id = id, errors = isTicketUpdated.Item2 })));
}
public async Task <ActionResult> TicketDetails(Guid id, string errors = "", string subject = "", string mailBody = "", string recipients = "")
{
if (string.IsNullOrWhiteSpace(SessionVar.GetString("displayName")))
{
Redirect("~/Account/Login");
}
Ticket ticket = new Ticket();
TicketViewModel ticketViewModel = new TicketViewModel();
try
{
using (HelpDeskEntities dataContext = new HelpDeskEntities())
{
ticket = await dataContext.Tickets.Include("Category").FirstOrDefaultAsync(p => p.TicketID.Equals(id));
if (ticket != null)
{
ticket.TicketFiles = await dataContext.TicketFiles.Where(p => p.TicketID.Equals(id)).ToListAsync();
ticket.TicketNotes = await dataContext.TicketNotes.Where(p => p.TicketID.Equals(id)).ToListAsync();
ticketViewModel.TicketID = id;
ticketViewModel.TicketNo = ticket.TicketNo;
ticketViewModel.CategoryID = ticket.CategoryID;
ticketViewModel.Category = ticket.Category;
ticketViewModel.Description = ticket.Description;
ticketViewModel.Status = ticket.Status;
ticketViewModel.CreatedBy = ticket.CreatedBy;
ticketViewModel.CreationDate = ticket.CreationDate;
ticketViewModel.ModifiedBy = ticket.ModifiedBy;
ticketViewModel.ModifiedDate = ticket.ModifiedDate;
ticketViewModel.IsDeleted = ticket.IsDeleted;
foreach (TicketFile item in ticket.TicketFiles)
{
ticketViewModel.TicketFileViewModels.Add(new TicketFileViewModel()
{
TicketID = ticket.TicketID,
FileName = item.FileName,
FileForTOrN = item.FileForTOrN,
RefID = item.RefID,
CreatedBy = item.CreatedBy,
CreationDate = item.CreationDate,
TicketViewModel = ticketViewModel
});
}
foreach (TicketNote item in ticket.TicketNotes)
{
ticketViewModel.TicketNoteViewModels.Add(new TicketNoteViewModel()
{
TicketID = ticket.TicketID,
Note = item.Note,
CreatedBy = item.CreatedBy,
CreationDate = item.CreationDate,
TicketViewModel = ticketViewModel
});
}
}
}
ViewBag.Errors = errors;
}
catch (Exception ex)
{
HomeController.LogError(ex, HttpContext.Server.MapPath("~/Error_Log.txt"));
}
return(View(ticketViewModel));
}
public async Task <ActionResult> RaiseTicket(TicketViewModel model)
{
if (string.IsNullOrWhiteSpace(SessionVar.GetString("displayName")))
{
Redirect("~/Account/Login");
}
Guid id = Guid.NewGuid();
bool isSuccessful = false;
bool sendEmailAlert = false;
GeneralSetting generalSetting = null;
try
{
ViewBag.Categories = await new HelpDeskEntities().Categories.ToListAsync();
#region validate inputs
StringBuilder errors = new StringBuilder();
if (string.IsNullOrWhiteSpace(model.Subject))
{
errors.AppendLine("Subject is required.");
}
if (string.IsNullOrWhiteSpace(model.Description))
{
errors.AppendLine("Description is required.");
}
if (model.CategoryID == null)
{
errors.AppendLine("Category is required.");
}
if (model.FileAttachments != null)
{
string[] unsupportedFileTypes = new[] { "exe" };
}
#endregion
if (errors.Length > 0)
{
for (int i = 0; i < errors.Length; i++)
{
ModelState.AddModelError("Error" + i.ToString(), errors[i].ToString());
}
}
else
{
// sanitize data
model.Subject = SecurityHelper.Sanitize(model.Subject);
model.Description = SecurityHelper.Sanitize(model.Description);
#region save data
using (TransactionScope transactionScope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (HelpDeskEntities dataContext = new HelpDeskEntities())
{
string ticketNo = StringHelper.GenerateRandomNumber(5);
model.TicketNo = ticketNo;
Ticket ticket = new Ticket()
{
TicketID = id,
TicketNo = ticketNo,
CategoryID = model.CategoryID,
Subject = model.Subject,
Description = model.Description,
Status = "N",
CreatedBy = User.Identity.Name,
CreationDate = DateTime.Now,
ModifiedBy = User.Identity.Name,
ModifiedDate = DateTime.Now,
OwnerEmail = Session["userEmail"].ToString(),
IsDeleted = false
};
dataContext.Tickets.Add(ticket);
if (await dataContext.SaveChangesAsync() > 0)
{
#region ticket files
if (model.FileAttachments != null)
{
if (model.FileAttachments.Length > 0)
{
string targetPath = HttpContext.Server.MapPath(this.fileAttachmentPath);
if (model.FileAttachments[0] != null)
{
List <TicketFile> ticketFiles = new List <TicketFile>();
foreach (var file in model.FileAttachments)
{
string fileName = Path.Combine(targetPath, file.FileName);
file.SaveAs(fileName);
ticketFiles.Add(new TicketFile()
{
TicketFileID = Guid.NewGuid(),
TicketID = ticket.TicketID,
FileName = file.FileName,
FileForTOrN = "T",
RefID = id,
CreatedBy = User.Identity.Name,
CreationDate = DateTime.Now,
ModifiedBy = User.Identity.Name,
ModifiedDate = DateTime.Now,
IsDeleted = false
});
}
dataContext.TicketFiles.AddRange(ticketFiles);
await dataContext.SaveChangesAsync();
}
}
}
#endregion
// trigger e-mail [optional]
generalSetting = await dataContext.GeneralSettings.FirstOrDefaultAsync();
if (generalSetting != null)
{
if (generalSetting.EnableEmailAlert && !string.IsNullOrWhiteSpace(generalSetting.RecipientEmails))
{
sendEmailAlert = true;
}
}
transactionScope.Complete();
isSuccessful = true;
}
else
{
errors.AppendLine("Unknow error occured.");
}
}
}
#endregion
}
}
catch (DbEntityValidationException ex)
{
HomeController.LogError(ex, HttpContext.Server.MapPath("~/Error_Log.txt"));
}
catch (Exception ex)
{
HomeController.LogError(ex, HttpContext.Server.MapPath("~/Error_Log.txt"));
}
if (isSuccessful)
{
if (sendEmailAlert)
{
SendEmail("ICT HelpDesk [" + model.TicketNo + "] " + model.Subject, model.Description, generalSetting.RecipientEmails, id.ToString(), model.FileAttachments);
}
return(RedirectToAction("TicketDetails", new RouteValueDictionary(new { controller = "Home", action = "TicketDetails", id = id })));
}
return(View(model));
}
private async Task <Tuple <bool, string> > IsTicketUpdated(TicketViewModel model)
{
if (string.IsNullOrWhiteSpace(SessionVar.GetString("displayName")))
{
Redirect("~/Account/Login");
}
StringBuilder errors = new StringBuilder();
Guid id = model.TicketID;
bool isSuccessful = false;
try
{
#region validate inputs
if (model.TicketID == null)
{
errors.AppendLine("Ticket ID is required.");
}
if (string.IsNullOrWhiteSpace(model.Description))
{
errors.AppendLine("Description is required.");
}
if (model.FileAttachments != null)
{
string[] unsupportedFileTypes = new[] { "exe" };
}
#endregion
if (errors.Length > 0)
{
isSuccessful = false;
}
else
{
// sanitize data
model.Description = SecurityHelper.Sanitize(model.Description);
#region save data
using (TransactionScope transactionScope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (HelpDeskEntities dataContext = new HelpDeskEntities())
{
Ticket ticket = await dataContext.Tickets.FirstOrDefaultAsync(p => p.TicketID.Equals(model.TicketID));
if (ticket == null)
{
errors.AppendLine("Unknown ticket details");
}
if (errors.Length == 0)
{
model.Subject = ticket.Subject;
model.OwnerEmail = ticket.OwnerEmail;
ticket.Status = model.Status;
ticket.ModifiedBy = User.Identity.Name;
dataContext.Entry(ticket).State = EntityState.Modified;
if (await dataContext.SaveChangesAsync() > 0)
{
Guid ticketNoteID = Guid.NewGuid();
#region ticket note
TicketNote ticketNote = new TicketNote()
{
TicketNoteID = ticketNoteID,
TicketID = model.TicketID,
Note = model.Description,
CreatedBy = User.Identity.Name,
CreationDate = DateTime.Now,
ModifiedBy = User.Identity.Name,
ModifiedDate = DateTime.Now,
IsDeleted = false
};
dataContext.TicketNotes.Add(ticketNote);
if (await dataContext.SaveChangesAsync() > 0)
{
#region ticket files
if (model.FileAttachments != null)
{
if (model.FileAttachments.Length > 0)
{
string targetPath = HttpContext.Server.MapPath(this.fileAttachmentPath);
if (model.FileAttachments[0] != null)
{
List <TicketFile> ticketFiles = new List <TicketFile>();
foreach (var file in model.FileAttachments)
{
string fileName = Path.Combine(targetPath, file.FileName);
file.SaveAs(fileName);
ticketFiles.Add(new TicketFile()
{
TicketFileID = Guid.NewGuid(),
TicketID = ticket.TicketID,
FileName = file.FileName,
FileForTOrN = "N",
RefID = ticketNoteID,
CreatedBy = User.Identity.Name,
CreationDate = DateTime.Now,
ModifiedBy = User.Identity.Name,
ModifiedDate = DateTime.Now,
IsDeleted = false
});
}
dataContext.TicketFiles.AddRange(ticketFiles);
await dataContext.SaveChangesAsync();
}
}
}
#endregion
}
#endregion
transactionScope.Complete();
isSuccessful = true;
}
else
{
errors.AppendLine("Unknow error occured.");
}
}
}
}
#endregion
}
}
catch (DbEntityValidationException ex)
{
isSuccessful = false;
HomeController.LogError(ex, HttpContext.Server.MapPath("~/Error_Log.txt"));
}
catch (Exception ex)
{
isSuccessful = false;
errors.AppendLine(ex.Message);
HomeController.LogError(ex, HttpContext.Server.MapPath("~/Error_Log.txt"));
}
return(Tuple.Create(isSuccessful, errors.ToString()));
}
