public ActionResult Edit(TicketViewModel ticketViewModel)
{
CS_Ticket ticketObj = new CS_Ticket();
CS_Log logObj = new CS_Log();
CS_EmailLink linkObj = new CS_EmailLink();
int statusId = _dbContext.CS_Ticket.Where(s => s.TicketId == ticketViewModel.TicketId).Select(a => a.StatusId).Single();
int slaId = _dbContext.CS_Ticket.Where(s => s.TicketId == ticketViewModel.TicketId).Select(a => a.SLAId).Single();
int employId = _dbContext.CS_Ticket.Where(s => s.TicketId == ticketViewModel.TicketId).Select(a => a.EmployeeId).Single();
string ticketNumber = _dbContext.CS_Ticket.Where(t => t.TicketId == ticketViewModel.TicketId).Select(a => a.TicketNumber).Single();
string priority = _dbContext.CS_SLA.Where(p => p.SLAId == ticketViewModel.IDSLA).Select(a => a.PriorityName).Single();
string status = _dbContext.CS_Status.Where(s => s.StatusId == ticketViewModel.StatusId).Select(a => a.Status).Single();
if (ticketViewModel.ForwardTo != null)
{
if (ticketViewModel.ProjectId != 1)
{
linkObj.TicketId = ticketViewModel.TicketId;
linkObj.Link = Utility.GenerateLink(linkObj.TicketId);
if ((_dbContext.CS_EmailLink.Where(p => p.TicketId == linkObj.TicketId).Count()) == 1)
{
_objLink.Update(linkObj);
string employeeBody = EmailContent.EmployeeBody(linkObj.Link);
string employeeEmail = _dbContext.Employee.Where(e => e.Id == ticketViewModel.ForwardTo).Select(a => a.Email_Official).SingleOrDefault();
Thread NotifyEmployee = new Thread(() => SendMail.SendAcknowledgement(employeeEmail, EmailContent.EmployeeSubject, employeeBody, ticketNumber, priority, status));
NotifyEmployee.Start();
}
}
}
if (statusId != ticketViewModel.StatusId ||
slaId != ticketViewModel.IDSLA)
{
//if (ticketViewModel.StatusId == 6)
//{
// linkObj.TicketId = _dbContext.CS_EmailLink.Where(t => t.TicketId == ticketViewModel.TicketId).Select(a => a.TicketId).Single();
// _objLink.DeleteById(linkObj.TicketId);
//}
string emailFrom = _dbContext.Customer.Where(e => e.Id == ticketViewModel.CustomerId).Select(a => a.Email).Single();
Thread AcknowledgeCustomer = new Thread(() => SendMail.SendAcknowledgement(emailFrom, EmailContent.CustomerSubject, EmailContent.CustomerBody, ticketNumber, priority, status));
AcknowledgeCustomer.Start();
}
// Find priority level from SLAId
Int16 prioritylevel = 0;
foreach (var item in _objSLA.GetAll())
{
if (item.SLAId == ticketViewModel.IDSLA)
{
prioritylevel = item.PriorityLevel;
break;
}
}
// Create ticket object with edited value for update
ticketObj.TicketId = ticketViewModel.TicketId;
ticketObj.Title = ticketViewModel.Title;
ticketObj.CreatedOn = ticketViewModel.CreatedOn;
ticketObj.PriorityLevel = Convert.ToByte(prioritylevel);
ticketObj.Description = _dbContext.CS_Ticket.Where(x => x.TicketId == ticketViewModel.TicketId).Select(d => d.Description).SingleOrDefault();
ticketObj.CustomerId = ticketViewModel.CustomerId;
ticketObj.EmployeeId = GetEmployeeId();
ticketObj.ForwardToEmployee = ticketViewModel.ForwardTo;
ticketObj.SLAId = ticketViewModel.IDSLA;
ticketObj.StatusId = ticketViewModel.StatusId;
ticketObj.ProjectId = ticketViewModel.ProjectId;
ticketObj.TicketNumber = ticketViewModel.TicketNumber;
ticketObj.LastComment = ticketViewModel.Comment;
// New object for log table to insert entry
logObj.Title = ticketViewModel.Title;
logObj.Comment = ticketViewModel.Comment;
logObj.CreatedOn = DateTime.Now;
logObj.ForwardToEmployee = ticketViewModel.ForwardTo;
logObj.EmployeeId = GetEmployeeId();
logObj.StatusId = ticketViewModel.StatusId;
logObj.TicketId = ticketViewModel.TicketId;
logObj.CustumerId = ticketViewModel.CustomerId;
logObj.PriorityName = FindPriorityBySLAId(ticketViewModel.IDSLA);
// Methods call update ticket table
_objTicket.Update(ticketObj);
// Method call to insert in log table
_objLog.Insert(logObj);
return(RedirectToAction(ActionName.TicketIndex));
}
/// <summary>
/// Performs escalation of ticket
/// </summary>
public void PerformEscalation()
{
while (true)
{
try
{
//Iterates through all entries of list
foreach (var listItem in CommonProperty.EscalationList)
{
//Finds current status of ticket
int StatusId = (from s in CommonProperty.dbContext.CS_Ticket
where s.TicketId == listItem.EscTicketDetail.TicketId
select s.StatusId).Single();
//Escalates to next level
if (listItem.EscalationExpiryTime < DateTime.Now &&
StatusId == 1)
{
listItem.EscalationExpiryTime = listItem.EscalationExpiryTime.AddMinutes(listItem.EscalationTime); //Changes expiry time to next
//Ticket not reached to highest level
string priority = CommonProperty.dbContext.CS_SLA.Where(p => p.SLAId == listItem.EscTicketDetail.SLAId).Select(a => a.PriorityName).Single();
string status = CommonProperty.dbContext.CS_Status.Where(s => s.StatusId == listItem.EscTicketDetail.StatusId).Select(a => a.Status).Single();
if (listItem.EscalationLevel > 1)
{
listItem.EscalationLevel -= 1; //Increments next level
//Finds employee of project with next level
listItem.EscTicketDetail.EmployeeId = (from e in CommonProperty.dbContext.ProjectTeam
where e.ProjectId == listItem.EscTicketDetail.ProjectId &&
e.CS_Level == listItem.EscalationLevel
select e.EmployeeId).FirstOrDefault();
//Updates ticket entry
CommonProperty.Ticket.Update(listItem.EscTicketDetail);
//Store log details
CommonProperty.LogDetail.CreatedOn = DateTime.Now;
CommonProperty.LogDetail.EmployeeId = listItem.EscTicketDetail.EmployeeId;
CommonProperty.LogDetail.Title = listItem.EscTicketDetail.Title;
CommonProperty.LogDetail.Comment = "Ticket Escalted";
CommonProperty.LogDetail.StatusId = StatusId;
CommonProperty.LogDetail.TicketId = listItem.EscTicketDetail.TicketId;
CommonProperty.LogDetail.CustumerId = listItem.EscTicketDetail.CustomerId;
CommonProperty.LogDetail.PriorityName = (from p in CommonProperty.dbContext.CS_SLA
where p.SLAId == listItem.EscTicketDetail.SLAId
select p.PriorityName).SingleOrDefault();
//Insert log details
CommonProperty.Log.Insert(CommonProperty.LogDetail);
int[] arr = CommonProperty.dbContext.ProjectTeam.Where(e => e.ProjectId == listItem.EscTicketDetail.ProjectId && e.CS_Level == listItem.EscalationLevel).Select(a => a.EmployeeId).ToArray();
var employeeEmail = CommonProperty.dbContext.Employee.Where(p => arr.Contains(p.Id)).Select(a => a.Email_Official).ToList();
CommonProperty.LinkDetail.TicketId = CommonProperty.dbContext.CS_EmailLink.Where(t => t.TicketId == listItem.EscTicketDetail.TicketId).Select(a => a.TicketId).Single();
CommonProperty.LinkDetail.Link = Utility.GenerateLink(CommonProperty.LinkDetail.TicketId);
CommonProperty.Link.Update(CommonProperty.LinkDetail);
string employeeBody = EmailContent.EmployeeBody(CommonProperty.LinkDetail.Link);
SendMail.SendAcknowledgement(employeeEmail, EmailContent.EmployeeSubject, employeeBody, listItem.EscTicketDetail.TicketNumber, CommonProperty.LogDetail.PriorityName, "OPEN");
//string employeeEmail = CommonProperty.dbContext.Employee.Where(e => e.Id == listItem.EscTicketDetail.EmployeeId).Select(a => a.Email_Official).Single();
//SendMail.MailToEmployee(employeeEmail,listItem.EscTicketDetail.TicketNumber);
}
else
{
CommonProperty.TempList.Add(listItem); //All escalation level is finished
//Sends mail to authority person
// SendMail.SLABreach(listItem.EscTicketDetail.TicketNumber);
SendMail.SendAcknowledgement(CommonProperty.SLABreachEmailAddress, EmailContent.SLAbreechSubject, EmailContent.SLAbreechBody, listItem.EscTicketDetail.TicketNumber, CommonProperty.LogDetail.PriorityName, "OPEN");
}
}
else
{
//Status changed from open
if (StatusId != 1)
{
CommonProperty.TempList.Add(listItem);
}
}
}
//Removes unnecessary entries
foreach (var item in CommonProperty.TempList)
{
CommonProperty.EscalationList.Remove(item);
}
}
catch (Exception ex)
{
StreamWriter sw = new StreamWriter(CommonProperty.EscalationErrorFilePath, true);
sw.WriteLine(ex.ToString());
sw.Flush();
sw.Close();
}
Thread.Sleep(5000);
}
}
public ActionResult Generate(TicketViewModel ticketViewModel)
{
CS_Ticket ticketObj = new CS_Ticket();
CS_Log logObj = new CS_Log();
CS_EmailLink linkObj = new CS_EmailLink();
ticketObj.CreatedOn = DateTime.Now;
ticketObj.EmployeeId = GetEmployeeId();
ticketObj.Description = ticketViewModel.Description;
ticketObj.CustomerId = ticketViewModel.CustomerId;
ticketObj.SLAId = ticketViewModel.IDSLA;
ticketObj.Title = ticketViewModel.Title;
ticketObj.ProjectId = ticketViewModel.ProjectId;
ticketObj.StatusId = 1;
ticketObj.LastComment = "";
_objTicket.Insert(ticketObj);
// To add entry in Log table
int ticketid = _dbContext.CS_Ticket.Max(item => item.TicketId);
logObj.TicketId = ticketid;
logObj.Title = ticketViewModel.Title;
logObj.Comment = "Ticket Created";
logObj.CreatedOn = ticketObj.CreatedOn;
logObj.EmployeeId = ticketObj.EmployeeId;
logObj.CustumerId = ticketObj.CustomerId;
logObj.StatusId = 1;
logObj.PriorityName = FindPriorityBySLAId(ticketObj.SLAId);
_objLog.Insert(logObj);
string ReadUrl = ReadURL();
bool result = (ReadUrl.All(Char.IsDigit) && ReadUrl.Length > 0);
// Update Ticket generated In calllog table
if (result == true)
{
int callLogid = Convert.ToInt32(ReadUrl);
//// CS_CallLog callObj = new CS_CallLog();
//var row = (from u in _dbContext.CS_CallLog
// where u.CallLogId == callLogid
// select u);
//foreach (var entity in row)
//{
// callObj = entity;
// callObj.IsTicketGenerated = true;
//}
CS_CallLog callObj = _objCallLog.FindById(callLogid);
callObj.IsTicketGenerated = true;
callObj.TicketId = ticketObj.TicketId;
_objCallLog.Update(callObj);
}
//Fetch customer email id
string emailFrom = _dbContext.Customer.Where(e => e.Id == ticketObj.CustomerId).Select(a => a.Email).SingleOrDefault();
//Fetch status name
string status = _dbContext.CS_Status.Where(s => s.StatusId == ticketObj.StatusId).Select(a => a.Status).SingleOrDefault();
//Fetch priority name
string priority = _dbContext.CS_SLA.Where(p => p.SLAId == ticketObj.SLAId).Select(a => a.PriorityName).SingleOrDefault();
//Fetch ticket number
string ticketNumber = _dbContext.CS_Ticket.Where(t => t.TicketId == ticketObj.TicketId).Select(a => a.TicketNumber).SingleOrDefault();
//Fectch employee email
string employeeEmail = _dbContext.Employee.Where(e => e.Id == ticketObj.EmployeeId).Select(a => a.Email_Official).SingleOrDefault();
linkObj.TicketId = ticketObj.TicketId;
linkObj.Link = Utility.GenerateLink(linkObj.TicketId);
_objLink.Insert(linkObj);
string employeeBody = EmailContent.EmployeeBody(linkObj.Link);
Thread AcknowledgeCustomer = new Thread(() => SendMail.SendAcknowledgement(emailFrom, EmailContent.CustomerSubject, EmailContent.CustomerBody, ticketNumber, priority, status));
Thread NotifyEmployee = new Thread(() => SendMail.SendAcknowledgement(employeeEmail, EmailContent.EmployeeSubject, employeeBody, ticketNumber, priority, status));
AcknowledgeCustomer.Start();
NotifyEmployee.Start();
return(RedirectToAction(ActionName.TicketIndex, ControllerName.Ticket));
}
/// <summary>
/// Generates ticket
/// </summary>
/// <param name="EmailFrom">Sender of email</param>
private void GenerateTicket(string EmailFrom)
{
MailAddress address = new MailAddress(EmailFrom); //Represents address of mail sender or recipient
string DomainName = "@" + address.Host; //Domain name of sender
//Count customer with given domain name
var customer = (from c in CommonProperty.dbContext.Customer
where c.CS_DomainName == DomainName
select c.Id);
var custFromEmail = (from e in CommonProperty.dbContext.CS_CustomerEmployeeInfo
where e.EmailId == EmailFrom
select e.CustomerId);
//Customer identification. CustomerCount = 0 => Not identified
if (customer.Count() == 0 && custFromEmail.Count() == 0)
{
GenerateDefaultTicket(1); //Insert values for default customer
}
else
{
if (customer != null)
{
//Finds customer ID
CommonProperty.TicketDetail.CustomerId = customer.SingleOrDefault();
}
else
{
CommonProperty.TicketDetail.CustomerId = custFromEmail.Single();
}
//Count projects of customer
int ProjectCount = (from p in CommonProperty.dbContext.Projects
where p.CustomerId == CommonProperty.TicketDetail.CustomerId
select p.Id).Count();
//For more than 1 customer project- insert to default customer
if (ProjectCount > 1)
{
GenerateDefaultTicket(CommonProperty.TicketDetail.CustomerId); //Insert values for default customer
}
else
{
//Finds project ID
CommonProperty.TicketDetail.ProjectId = (from p in CommonProperty.dbContext.Projects
where p.CustomerId == CommonProperty.TicketDetail.CustomerId
select p.Id).SingleOrDefault();
//Finds SLA ID
var SlaIDCount = from s in CommonProperty.dbContext.CS_SLA
where s.ProjectId == CommonProperty.TicketDetail.ProjectId &&
s.IsDefault == true
select s.SLAId;
if (SlaIDCount != null)
{
CommonProperty.TicketDetail.SLAId = SlaIDCount.SingleOrDefault();
var priorityLevel = (from p in CommonProperty.dbContext.CS_SLA
where p.SLAId == CommonProperty.TicketDetail.SLAId
select p.PriorityLevel).SingleOrDefault();
CommonProperty.TicketDetail.PriorityLevel = Convert.ToByte(priorityLevel);
}
else
{
var SlaID = (from s in CommonProperty.dbContext.CS_SLA
where s.ProjectId == CommonProperty.TicketDetail.ProjectId
select s.SLAId).FirstOrDefault();
//Assign default priority
var Priority = (from p in CommonProperty.dbContext.CS_Priority
select p.PriorityLevel).FirstOrDefault();
CommonProperty.TicketDetail.PriorityLevel = Convert.ToByte(Priority);
}
int level = (from e in CommonProperty.dbContext.ProjectTeam
where e.ProjectId == CommonProperty.TicketDetail.ProjectId
select e.CS_Level).Distinct().Count();
//Finds employee Id which is in first level of project team
CommonProperty.TicketDetail.EmployeeId = (from e in CommonProperty.dbContext.ProjectTeam
where e.ProjectId == CommonProperty.TicketDetail.ProjectId &&
e.CS_Level == level
select e.EmployeeId).FirstOrDefault();
//Status becomes open
CommonProperty.TicketDetail.StatusId = 1;
//Insert ticket in database
CommonProperty.Ticket.Insert(CommonProperty.TicketDetail);
}
}
CommonProperty.LogDetail.CreatedOn = DateTime.Now;
CommonProperty.LogDetail.EmployeeId = CommonProperty.TicketDetail.EmployeeId;
CommonProperty.LogDetail.Title = CommonProperty.TicketDetail.Title;
CommonProperty.LogDetail.Comment = "Ticket created";
CommonProperty.LogDetail.StatusId = CommonProperty.TicketDetail.StatusId;
CommonProperty.LogDetail.TicketId = CommonProperty.TicketDetail.TicketId;
CommonProperty.LogDetail.CustumerId = CommonProperty.TicketDetail.CustomerId;
CommonProperty.LogDetail.PriorityName = (from p in CommonProperty.dbContext.CS_SLA
where p.SLAId == CommonProperty.TicketDetail.SLAId
select p.PriorityName).SingleOrDefault();
//Insert log details
CommonProperty.Log.Insert(CommonProperty.LogDetail);
CommonProperty.TicketDetail.TicketNumber = (from e in CommonProperty.dbContext.CS_Ticket
where e.TicketId == CommonProperty.TicketDetail.TicketId
select e.TicketNumber).SingleOrDefault();
string priority = CommonProperty.dbContext.CS_SLA.Where(p => p.SLAId == CommonProperty.TicketDetail.SLAId).Select(a => a.PriorityName).SingleOrDefault();
string status = CommonProperty.dbContext.CS_Status.Where(s => s.StatusId == CommonProperty.TicketDetail.StatusId).Select(a => a.Status).SingleOrDefault();
int employeeCount = (from e in CommonProperty.dbContext.ProjectTeam
where e.ProjectId == CommonProperty.TicketDetail.ProjectId
select e.CS_Level).Distinct().Count();
int incrementTime = (from t in CommonProperty.dbContext.CS_SLA
where t.SLAId == CommonProperty.TicketDetail.SLAId
select t.EscalationTime).SingleOrDefault();
int[] arr = CommonProperty.dbContext.ProjectTeam.Where(e => e.ProjectId == CommonProperty.TicketDetail.ProjectId && e.CS_Level == employeeCount).Select(a => a.EmployeeId).ToArray();
var employeeEmail = CommonProperty.dbContext.Employee.Where(p => arr.Contains(p.Id)).Select(a => a.Email_Official).ToList();
CommonProperty.LinkDetail.TicketId = CommonProperty.TicketDetail.TicketId;
CommonProperty.LinkDetail.Link = Utility.GenerateLink(CommonProperty.LinkDetail.TicketId);
CommonProperty.Link.Insert(CommonProperty.LinkDetail);
string employeeBody = EmailContent.EmployeeBody(CommonProperty.LinkDetail.Link);
CommonProperty.EscalationList.
Add(new Escalation()
{
EscTicketDetail = CommonProperty.TicketDetail,
EscalationExpiryTime = CommonProperty.TicketDetail.CreatedOn.AddMinutes(incrementTime),
EscalationLevel = employeeCount,
EscalationTime = incrementTime,
//CurrentLevel = 1
});
SendMail.SendAcknowledgement(EmailFrom, EmailContent.CustomerSubject, EmailContent.CustomerBody, CommonProperty.TicketDetail.TicketNumber, priority, status);
SendMail.SendAcknowledgement(employeeEmail, EmailContent.EmployeeSubject, employeeBody, CommonProperty.TicketDetail.TicketNumber, priority, status);
}
