public ActionResult Create([Bind(Include = "ID,IdCustomer,IdSLA")] CustomerSLA customerSLA)
{
try
{
if (ModelState.IsValid)
{
var SLAExists = db.CustomerSLAS.Where(x => x.IdCustomer.Equals(customerSLA.IdCustomer) && x.IdSLA.Equals(customerSLA.IdSLA)).Count();
if (SLAExists == 0)
{
db.CustomerSLAS.Add(customerSLA);
db.SaveChanges();
this.AddToastMessage("Assign SLA", "SLA successfully assigned to company.", ToastType.Success);
return(RedirectToAction("Create", "CustomerSLAs", new { IdCustomer = customerSLA.IdCustomer }));
}
else
{
this.AddToastMessage("Assign SLA", "This SLA already exists for this company, please verify", ToastType.Error);
return(RedirectToAction("Create", "CustomerSLAs", new { IdCustomer = customerSLA.IdCustomer }));
}
}
}
catch (Exception ex)
{
logger.Error(ex.ToString());
}
ViewBag.IdCustomer = new SelectList(db.Customers, "Id", "CompanyName", customerSLA.IdCustomer);
ViewBag.IdSLA = new SelectList(db.SLAS, "ID", "Name", customerSLA.IdSLA);
return(View(customerSLA));
}
public ActionResult Create([Bind(Include = "ID,idConsultant,idKPI")] ConsultantKPI consultantKPI)
{
try
{
if (ModelState.IsValid)
{
var KPIExists = db.ConsultantsKPIs.Where(x => x.idConsultant.Equals(consultantKPI.idConsultant) && x.idKPI.Equals(consultantKPI.idKPI)).Count();
if (KPIExists == 0)
{
db.ConsultantsKPIs.Add(consultantKPI);
db.SaveChanges();
this.AddToastMessage("Assign KPI", "KPI successfully assigned to consultant.", ToastType.Success);
return(RedirectToAction("Create", "ConsultantKPIs", new { idConsultant = consultantKPI.idConsultant }));
}
else
{
this.AddToastMessage("Assign KPI", "This KPI already exists for this consultant, please verify", ToastType.Error);
return(RedirectToAction("Create", "ConsultantKPIs", new { idConsultant = consultantKPI.idConsultant }));
}
}
}
catch (Exception ex)
{
logger.Error(ex.ToString());
}
ViewBag.idConsultant = new SelectList(db.Consultants, "Id", "FullName", consultantKPI.idConsultant);
ViewBag.idKPI = new SelectList(db.SLAS, "ID", "Name", consultantKPI.idKPI);
return(View(consultantKPI));
}
public ActionResult Create([Bind(Include = "ID,CreationDate,Category,Solution,Details,idConsultant")] KnowledgeBase knowledgeBase)
{
if (User.IsInRole("Administrator"))
{
idConsultantLogged = 1;
}
else
{
idConsultantLogged = int.Parse(User.Identity.GetConsultantId());
}
try
{
var solutionToInsert = new KnowledgeBase
{
ID = knowledgeBase.ID,
CreationDate = knowledgeBase.CreationDate,
Category = knowledgeBase.Category,
Solution = knowledgeBase.Solution,
Details = knowledgeBase.Details,
idConsultant = idConsultantLogged
};
using (var context = new PlusBContext())
{
db.KnowledgeBases.Add(solutionToInsert);
db.SaveChanges();
this.AddToastMessage("Knowledge Base", "Solution created successfully!", ToastType.Success);
}
}
catch (DbUpdateException sqlExc)
{
var sqlException = sqlExc.GetBaseException() as SqlException;
if (sqlException != null)
{
logger.Error(sqlExc.ToString());
this.AddToastMessage("Knowledge Base", "This solution already exists, please verify.", ToastType.Error);
}
else
{
throw;
}
}
return(RedirectToAction("Index"));
}
public void Execute(IJobExecutionContext context)
{
var yesterday = DateTime.Today.AddDays(-1);
DateTime today = DateTime.Now;
TimeSpan currentHour = TimeSpan.Parse(today.ToString("HH:mm:ss"));
var shortDate = today.Date;
bool sendNotification = false;
try
{
// Get tickets in Unassigned state
IList <Ticket> getUnassignedTickets = db.Tickets.Where(x => x.Id_Consultant == 1).ToList();
// Get Yesterday's consultant with less Performance Average
var getLessAvgPerformanceScore = db.PerformanceEvalutions
.Where(x => x.Date == yesterday)
.Select(x => x.PerformanceAverage)
.Min();
var getLessConsultantInfo = db.PerformanceEvalutions
.Where(x => x.PerformanceAverage == getLessAvgPerformanceScore &&
x.Date == yesterday);
// Extract consultant email in emailToSend static var
emailToSend = getLessConsultantInfo.Select(x => x.Consultant.Email).First();
foreach (var t in getUnassignedTickets)
{
Ticket ticket = db.Tickets.Find(t.Id);
ticket.Id_Consultant = getLessConsultantInfo.Select(x => x.Consultant.ID).First();
ticket.AssignmentDate = shortDate;
ticket.AssignmentTime = currentHour;
db.Set <Ticket>().AddOrUpdate(ticket);
db.SaveChanges();
sendNotification = true;
}
if (sendNotification == true)
{
SendEmail();
}
}
catch (Exception ex)
{
logger.Error(ex.ToString());
}
}
public ActionResult Edit([Bind(Include = "Id,Date,OpenTime,Id_Customer,ShortDescription,LongDescription,Environment,Id_Technology,Id_Severity,Id_Impact,Id_TaskType,Status,Id_Consultant")] Ticket ticket)
{
if (ModelState.IsValid)
{
db.Entry(ticket).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
ViewBag.Id_Consultant = new SelectList(db.Consultants, "ID", "FirstName", ticket.Id_Consultant);
ViewBag.Id_Customer = new SelectList(db.Customers, "Id", "CompanyName", ticket.Id_Customer);
ViewBag.Id_Impact = new SelectList(db.Impacts, "Id", "ImpactName", ticket.Id_Impact);
ViewBag.Id_Severity = new SelectList(db.Severities, "Id", "SeverityName", ticket.Id_Severity);
ViewBag.Id_TaskType = new SelectList(db.TaskTypes, "Id", "TaskName", ticket.Id_TaskType);
ViewBag.Id_Technology = new SelectList(db.Technologies, "ID", "Name", ticket.Id_Technology);
return(Json(new { success = true }));
}
public async Task <ActionResult> Create(ConsultantUserModel model)
{
//insert into table Consultant
var ConsultantDetails = new Consultant {
ID = model.ID,
FirstName = model.FirstName,
LastName = model.LastName,
DateOfBirth = model.DateOfBirth,
IdNumber = model.IdNumber,
Gender = model.Gender,
Email = model.UserName,
Pais = model.Pais,
Address = model.Address,
PhoneNumber = model.PhoneNumber,
JobTitle = model.JobTitle,
Education = model.Education,
HireDate = model.HireDate
};
using (var context = new PlusBContext())
{
context.Consultants.Add(ConsultantDetails);
context.SaveChanges();
}
//ends of insert consultant post
//insert into AspNetUser table to login the user next time and assign consultant claim
var Password = model.Password;
var lastConsultantId = (from i in consultantUserRepo.GetConsultants()
orderby i.ID descending
select i.ID).First();
var consultantUser = new ApplicationUser
{
UserName = model.UserName,
Email = model.UserName,
ConsultantID = lastConsultantId.ToString()
};
var CreateConsultantUser = UserManager.Create(consultantUser, Password);
var roleConsultant = UserManager.AddToRole(consultantUser.Id, "Consultant");
return(RedirectToAction("Index"));
}
public ActionResult fillSurvey(Survey survey)
{
//Get ticket details
int urlID = Convert.ToInt32(Url.RequestContext.RouteData.Values["id"]);
Ticket ticket = db.Tickets.Find(urlID);
try
{
DateTime?dateTicketClosed = ticket.ClosedDate;
var surveyDetails = new Survey
{
DateSent = dateTicketClosed ?? DateTime.Now,
Comments = survey.Comments,
DateAnswered = DateTime.Now,
idTicket = ticket.Id,
Answer1 = survey.Answer1,
Answer2 = survey.Answer2,
Answer3 = survey.Answer3,
Answer4 = survey.Answer4,
IsAnswered = 1
};
using (var context = new PlusBContext())
{
context.Surveys.Add(surveyDetails);
context.SaveChanges();
this.AddToastMessage("Survey", "Survey created successfully.", ToastType.Success);
}
}
catch (Exception ex)
{
logger.Error(ex.ToString());
this.AddToastMessage("Survey", "There was a problem while creating survey, please verify.", ToastType.Error);
}
return(RedirectToAction("surveyAlertCreation"));
}
public async Task <ActionResult> Create(TicketAttachmentModel model, IEnumerable <HttpPostedFileBase> files)
{
var user = UserManager.FindById(User.Identity.GetUserId());
//insert into table Ticket
var ticketDetails = new Ticket
{
Id = model.Id,
Date = model.Date,
OpenTime = model.OpenTime,
Id_Customer = model.Id_Customer,
ShortDescription = model.ShortDescription,
LongDescription = model.LongDescription,
Environment = model.Environment,
Id_Technology = model.Id_Technology,
Id_Severity = model.Id_Severity,
Id_Impact = model.Id_Impact,
Id_TaskType = model.Id_TaskType,
Status = model.Status,
Id_Consultant = model.Id_Consultant,
Creator = user.Email,
ClosedDate = null,
AssignmentDate = null,
AverageResolution = null,
AssignmentTime = null,
ClosedTime = null,
TotalResolutionHours = null,
AutoAssigned = 0,
EscalationReason = null,
EscalationDate = null
};
using (var context = new PlusBContext())
{
context.Tickets.Add(ticketDetails);
context.SaveChanges();
this.AddToastMessage("Incident", "Incident created successfully.", ToastType.Success);
var consultantEmails = db.Consultants
.Where(consultant => consultant.ID != 1)
.Select(consultant => consultant.Email)
.ToList();
foreach (var email in consultantEmails)
{
string body = "<b>A new Incident has been opened </b>";
string subject = "Consultant - New Incident created";
EmailManager.SendEmail(email, body, subject);
}
}
try
{
foreach (HttpPostedFileBase file in files)
{
//Methods to convert attachment to byte[]
Stream str = file.InputStream;
BinaryReader Br = new BinaryReader(str);
Byte[] FileDetail = Br.ReadBytes((Int32)str.Length);
//query to extract last Ticket Id
var lastTicketId = (from i in ticketRepo.GetTickets()
orderby i.Id descending
select i.Id).First();
var attachmentDetails = new Attachment
{
FileName = file.FileName,
FileContent = FileDetail,
IdTicket = lastTicketId
};
using (var context = new PlusBContext())
{
context.Attachments.Add(attachmentDetails);
context.SaveChanges();
}
}
}
catch (Exception ex)
{
logger.Error(ex.ToString());
// return RedirectToAction("Index", "Tickets");
}
if (User.IsInRole("Customer"))
{
return(RedirectToAction("Index", "Tickets"));
}
else
{
return(RedirectToAction("UnassignedList", "Tickets"));
}
}
public void Save()
{
context.SaveChanges();
}
public void Execute(IJobExecutionContext context)
{
try
{
DateTime today = DateTime.Now;
var shortDate = today.Date;
var getConsultants = db.Consultants.Where(x => !x.ID.Equals(1)); // Get consultants
foreach (var i in getConsultants) // Run a loop to extract id for every consultant
{
var totalResolvedTickets = db.Tickets.Where(x => x.Id_Consultant == i.ID && x.ClosedDate == shortDate && x.Status == "Closed").Count();
var totalAssignedTickets = db.Tickets.Where(x => x.AssignmentDate == shortDate && x.Id_Consultant == i.ID).Count();
var techWeightAvg = (from x in db.Tickets
join y in db.Technologies
on x.Id_Technology equals y.ID
where x.AssignmentDate == shortDate && x.Id_Consultant == i.ID
select(int?) y.Weight).Sum() ?? 0;
if (techWeightAvg == 0)
{
techWeightAvg = 0;
}
else
{
techWeightAvg = techWeightAvg / totalAssignedTickets;
}
double?totalHoursTaken = db.Tickets.Where(x => x.ClosedDate == shortDate && x.Id_Consultant == i.ID).Select(x => x.TotalResolutionHours).Sum();
double?perfAvg = totalResolvedTickets + totalAssignedTickets + techWeightAvg + totalHoursTaken;
if (totalHoursTaken == null)
{
totalHoursTaken = 0;
}
if (perfAvg == null)
{
perfAvg = 0;
}
try
{
var performanceEvalDetails = new PerformanceEvaluation
{
Date = shortDate,
idConsultant = i.ID,
TotalResolvedIncidents = totalResolvedTickets,
TotalAssignedIncidents = totalAssignedTickets,
TechWeightAverage = techWeightAvg,
TotalHoursTaken = Math.Round(totalHoursTaken.Value, 2),
PerformanceAverage = Math.Round(perfAvg.Value, 2)
};
using (var x = new PlusBContext())
{
x.PerformanceEvalutions.Add(performanceEvalDetails);
x.SaveChanges();
}
}
catch (Exception ex)
{
logger.Error(ex.ToString());
}
}
}
catch (Exception ex)
{
logger.Error(ex.ToString());
}
}
public void Execute(IJobExecutionContext context)
{
try
{
DateTime today = DateTime.Now;
var shortDate = today.Date;
var getKPIData = from a in db.Tickets
join b in db.CustomerSLAS
on a.Id_Customer equals b.IdCustomer
join c in db.SLAS
on b.IdSLA equals c.ID
join d in db.ConsultantsKPIs
on a.Id_Consultant equals d.idConsultant
join e in db.KPIS
on d.idKPI equals e.ID
where a.Status == "Closed" && a.ClosedDate == shortDate
select new { a.Id, a.Id_Consultant, e.ID, Score = ((e.FormulaValue + a.TotalResolutionHours) / c.ResolutionTimeAverage) };
foreach (var data in getKPIData) // Run a loop to extract id for every consultant
{
string range;
if (data.Score > 0 && data.Score <= 10)
{
range = "Under average";
}
else if (data.Score > 10 && data.Score <= 25)
{
range = "Good";
}
else
{
range = "Exceed Expectation";
}
try
{
var KPIEvalDetails = new KPIEvaluation
{
Date = shortDate,
idConsultant = data.Id_Consultant,
idKPI = data.ID,
Score = Convert.ToDouble(data.Score),
Range = range,
idTicket = data.Id
};
using (var x = new PlusBContext())
{
x.KPIEvaluations.Add(KPIEvalDetails);
x.SaveChanges();
}
}
catch (Exception ex)
{
logger.Error(ex.ToString());
}
} // end getConsultants foreach
}
catch (Exception ex)
{
logger.Error(ex.ToString());
}
}
public ActionResult Create(DateTime date, TimeSpan time, string activity)
{
try
{
var user = UserManager.FindById(User.Identity.GetUserId());
var ticketActivity = new TicketActivity
{
Date = date,
Time = time,
Activity = activity,
idTicket = IDTicket,
User = user.Email
};
using (var context = new PlusBContext())
{
context.TicketActivities.Add(ticketActivity);
context.SaveChanges();
this.AddToastMessage("Incident", "Activity added to the incident number " + ticketActivity.idTicket, ToastType.Success);
}
if (User.IsInRole("Consultant"))
{
var customerEmail = db.Tickets
.Where(x => x.Id == IDTicket)
.Select(x => x.Creator)
.FirstOrDefault();
emailToSend = customerEmail;
activityToSend = activity;
//Code to call Store Procedure...
SqlConnection connection = new SqlConnection("Data Source=KEIDY-LPT\\SQLEXPRESS;Initial Catalog=PlusBContext;Integrated Security=True;");
var command = new SqlCommand("getNotificationFlag", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@emailUser", emailToSend);
connection.Open();
int queryResult = (int)command.ExecuteScalar();
connection.Close();
if (queryResult == 1)
{
SendEmail(); // send notification
}
}
else
{
var consultantEmail = db.Tickets
.Where(x => x.Id == IDTicket)
.Select(x => x.Id_Consultant)
.FirstOrDefault();
var consultant_Email = db.Consultants
.Where(y => y.ID == consultantEmail)
.Select(y => y.Email)
.FirstOrDefault();
emailToSend = consultant_Email;
activityToSend = activity;
//Code to call Store Procedure...
SqlConnection connection = new SqlConnection("Data Source=KEIDY-LPT\\SQLEXPRESS;Initial Catalog=PlusBContext;Integrated Security=True;");
var command = new SqlCommand("getNotificationFlag", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@emailUser", emailToSend);
connection.Open();
int queryResult = (int)command.ExecuteScalar();
connection.Close();
if (queryResult == 1)
{
SendEmail(); // send notification
}
}
}
catch (Exception ex)
{
logger.Error(ex.ToString());
}
if (User.IsInRole("Consultant"))
{
return(RedirectToAction("Assigned", "Tickets", new { id = IDTicket }));
}
else
{
return(RedirectToAction("incidentCreated", "Tickets", new { id = IDTicket }));
}
}