public IList <UserDepartment> GetListByDepartmentId(int departmentId)
{
using (var model = new gb_ts_stagingEntities())
{
var q = (from userDepartment in model.tic_UserDepartments
join user in model.users on userDepartment.UserId equals user.Id
join departmentRole in model.tic_DepartmentRoles on userDepartment.DepartmentRoleId equals departmentRole.Id
where userDepartment.IsActive
where userDepartment.DepartmentId == departmentId
select new UserDepartment
{
Id = userDepartment.Id,
User = new CustomUser
{
Id = user.Id,
FirstName = user.FirstName,
LastName = user.LastName,
Email = user.Email
},
DepartmentRole = new DepartmentRole
{
Id = departmentRole.Id,
Name = departmentRole.RoleName
}
}).ToList();
return(q);
}
}
public IList <DropdownItem> GetDropdownValuesFromTable(string tableName, string idFieldName, string nameFieldName, string filterFieldName,
string filterFieldValue)
{
checkSqlInjection(tableName);
checkSqlInjection(idFieldName);
checkSqlInjection(nameFieldName);
checkSqlInjection(filterFieldName);
checkSqlInjection(filterFieldValue);
using (var model = new gb_ts_stagingEntities())
{
var isActiveCheck = "";
//If IsActive field exists then show only IsActive records
if (model.Database.SqlQuery <TableFieldMetaData>("DESCRIBE " + tableName).FirstOrDefault(rec => rec.Field == "IsActive") != null)
{
isActiveCheck = " AND IsActive=1 ";
}
var query = string.Format("SELECT {0} AS Id, {1} AS Name FROM {2} WHERE {3}='{4}' {5} ORDER BY {1}", idFieldName, nameFieldName, tableName, filterFieldName, filterFieldValue, isActiveCheck);
var fields = model.Database.SqlQuery <DropdownItem>(query);
return(fields.OrderBy(rec => rec.Name).ToList());
}
}
public IList <string> GetTableNames()
{
using (var model = new gb_ts_stagingEntities())
{
return(model.Database.SqlQuery <string>("SHOW TABLES").OrderBy(rec => rec).ToList());
}
}
public FormTemplate GetFirstMatchingTemplate(TicketModel record)
{
using (var model = new gb_ts_stagingEntities())
{
var q = (from formTemplate in model.tic_FormTemplates
join issueType in model.tic_IssueTypes on formTemplate.IssueTypeId equals issueType.Id into inIssueType
from outIssueType in inIssueType.DefaultIfEmpty()
join product in model.tic_Products on formTemplate.ProductId equals product.Id into inProduct
from outProduct in inProduct.DefaultIfEmpty()
join productCategory in model.tic_ProductCategories on formTemplate.ProductCategoryId equals productCategory.Id into inProductCategory
from outProductCategory in inProductCategory.DefaultIfEmpty()
join ticketType in model.tic_TicketTypes on formTemplate.TicketTypeId equals ticketType.Id into inTicketType
from outTicketType in inTicketType.DefaultIfEmpty()
where formTemplate.IsActive
where outIssueType == null || outIssueType.Id == record.IssueType.Id
where outProduct == null || outProduct.Id == record.Product.Id
where outProductCategory == null || outProductCategory.Id == record.ProductCategory.Id
where outTicketType == null || outTicketType.Id == record.TicketType.Id
orderby formTemplate.SortOrder
select new FormTemplate
{
Id = formTemplate.Id,
}).FirstOrDefault();
return(q);
}
}
public TicketLog Insert(int ticketId, int entryTypeId, string briefEntryValue, string fullEntryValue, string actorUserId)
{
using (var model = new gb_ts_stagingEntities())
{
var newRecord = new TicketsLog()
{
TicketId = ticketId,
EntryTypeId = entryTypeId,
EntryValue = briefEntryValue,
CreationDate = DateTime.Now,
EntryExtendedValue = fullEntryValue,
ActorUserId = actorUserId
};
model.TicketsLogs.Add(newRecord);
model.SaveChanges();
return(new TicketLog
{
Id = newRecord.Id,
TicketId = ticketId,
EntryTypeId = entryTypeId,
EntryValue = briefEntryValue,
LogDate = newRecord.CreationDate,
EntryExtendedValue = fullEntryValue
});
}
}
public IList <UserDepartment> GetListByUserId(string userId)
{
using (var model = new gb_ts_stagingEntities())
{
var q = (from userDepartment in model.tic_UserDepartments
join department in model.tic_Departments on userDepartment.DepartmentId equals department.Id
join departmentRole in model.tic_DepartmentRoles on userDepartment.DepartmentRoleId equals departmentRole.Id
where userDepartment.IsActive && department.IsActive
where userDepartment.UserId == userId
select new UserDepartment
{
Id = userDepartment.Id,
Department = new Department
{
Id = department.Id,
Name = department.Name,
CanSeeCustomerDetails = department.CanSeeCustomerDetails
},
DepartmentRole = new DepartmentRole
{
Id = departmentRole.Id,
Name = departmentRole.RoleName
}
}).ToList();
return(q);
}
}
public IList <TicketLog> GetList(int?ticketId, bool shouldHideInternalData, bool shouldHideCustomerData)
{
using (var model = new gb_ts_stagingEntities())
{
var q = (from ticketsLog in model.TicketsLogs
where !ticketId.HasValue || ticketId.Value == ticketsLog.TicketId
orderby ticketsLog.CreationDate
select new TicketLog
{
Id = ticketsLog.Id,
EntryValue =
((new List <int> {
(int)LogEntryTypeId.TicketCreated, (int)LogEntryTypeId.ReplyAddedByCustomer, (int)LogEntryTypeId.TicketEdited
}.Contains(ticketsLog.EntryTypeId) &&
shouldHideCustomerData) ||
shouldHideInternalData)
? ticketsLog.EntryValue : ticketsLog.EntryExtendedValue,
EntryTypeId = ticketsLog.EntryTypeId,
ActorId = ticketsLog.ActorId,
TicketId = ticketsLog.TicketId,
LogDate = ticketsLog.CreationDate
});
if (shouldHideInternalData)
{
//Remove comments and assignee data
q = q.Where(rec => !(new[] { (int?)LogEntryTypeId.AssigneeChanged, (int?)LogEntryTypeId.CommentAdded }).Contains(rec.EntryTypeId));
}
return(q.ToList());
}
}
public TicketModel Insert(TicketModel ticket)
{
using (var model = new gb_ts_stagingEntities())
{
var newRecord = new Ticket
{
IssueTypeId = ticket.IssueType.Id,
TypeId = ticket.TicketType.Id,
ProductId = ticket.Product.Id,
ProductCategoryId = ticket.ProductCategory.Id,
CustomerId = ticket.CustomerId,
CustomerUserId = ticket.CustomerUserId,
PriorityId = ticket.PriorityId,
StatusId = ticket.StatusId,
DepartmentId = ticket.DepartmentId,
PackageId = string.Empty,
CreationDate = DateTime.Now,
};
model.Tickets.Add(newRecord);
model.SaveChanges();
ticket.Id = newRecord.Id;
return(ticket);
}
}
public TicketFieldModel UpdateOrInsert(TicketFieldModel ticketField)
{
using (var model = new gb_ts_stagingEntities())
{
var recordToEdit = model.TicketFields.FirstOrDefault(rec => rec.TicketId == ticketField.TicketId && rec.CustomFieldId == ticketField.CustomFieldId);
if (recordToEdit != null)
{
recordToEdit.TicketId = ticketField.TicketId;
recordToEdit.CustomFieldId = ticketField.CustomFieldId;
recordToEdit.Value = ticketField.Value;
recordToEdit.TextValue = ticketField.TextValue;
}
else
{
recordToEdit = new TicketField
{
Value = ticketField.Value,
CustomFieldId = ticketField.CustomFieldId,
TextValue = ticketField.TextValue,
TicketId = ticketField.TicketId,
};
model.TicketFields.Add(recordToEdit);
}
model.SaveChanges();
ticketField.Id = recordToEdit.Id;
return(ticketField);
}
}
public TicketWorkflow Insert(TicketWorkflow workflow)
{
using (var model = new gb_ts_stagingEntities())
{
var newRecord = new tic_TicketWorkflow
{
IssueTypeId = workflow.IssueType.Id == 0 ? null : (int?)workflow.IssueType.Id,
TicketTypeId = workflow.TicketType.Id == 0 ? null : (int?)workflow.TicketType.Id,
ProductId = workflow.Product.Id == 0 ? null : (int?)workflow.Product.Id,
ProductCategoryId = workflow.ProductCategory.Id == 0 ? null : (int?)workflow.ProductCategory.Id,
CustomerPriorityId = workflow.CustomerPriorityId == 0 ? null : workflow.CustomerPriorityId,
DepartmentId = workflow.Department.Id,
CreationDate = DateTime.Now,
CreatedBy = "Admin",
SortOrder = workflow.SortOrder,
IsActive = true
};
model.tic_TicketWorkflow.Add(newRecord);
model.SaveChanges();
workflow.Id = newRecord.Id;
return(workflow);
}
}
public CustomField Insert(CustomField customField)
{
using (var model = new gb_ts_stagingEntities())
{
var newRecord = new tic_CustomFields
{
Name = customField.Name,
DbFilterFieldName = customField.DbFilterFieldName,
DbTableIdFieldName = customField.DbTableIdFieldName,
DbTableName = customField.DbTableName,
DbTableTextFieldName = customField.DbTableTextFieldName,
PlaceholderText = customField.PlaceholderText,
StepNumber = customField.StepNumber,
Title = customField.Title,
Identifier = customField.Identifier,
CustomeFieldTypeId = customField.CustomFieldTypeId,
CreatedBy = "Admin",
CreationDate = DateTime.Now,
IsActive = true,
RootCustomFieldId = customField.RootCustomFieldId,
DropdownCustomFieldId = customField.DropdownCustomFieldId
};
model.tic_CustomFields.Add(newRecord);
model.SaveChanges();
customField.Id = newRecord.Id;
return(customField);
}
}
public TicketWorkflow GetFirstMathingRule(TicketModel record)
{
using (var model = new gb_ts_stagingEntities())
{
var q = (from ticketWorkflow in model.tic_TicketWorkflow
join department in model.tic_Departments on ticketWorkflow.DepartmentId equals department.Id
where ticketWorkflow.IsActive
where ticketWorkflow.IssueTypeId == record.IssueType.Id || ticketWorkflow.IssueTypeId == null
where ticketWorkflow.ProductId == record.Product.Id || ticketWorkflow.ProductId == null
where ticketWorkflow.ProductCategoryId == record.ProductCategory.Id || ticketWorkflow.ProductCategoryId == null
where ticketWorkflow.TicketTypeId == record.TicketType.Id || ticketWorkflow.TicketTypeId == null
where ticketWorkflow.CustomerPriorityId == record.PriorityId || ticketWorkflow.CustomerPriorityId == null
orderby ticketWorkflow.SortOrder
select new TicketWorkflow
{
Id = ticketWorkflow.Id,
Department = new Department
{
Id = department.Id,
Name = department.Name
},
}).FirstOrDefault();
return(q);
}
}
public IList <TicketModel> GetTicketsForCustomer(string userId)
{
using (var model = new gb_ts_stagingEntities())
{
var q = GetFullList(model).Where(m => m.CustomerContact.UserId == userId).ToList();
return(q);
}
}
public TicketModel GetTicketById(int ticketId)
{
using (var model = new gb_ts_stagingEntities())
{
var q = GetFullList(model).FirstOrDefault(m => m.Id == ticketId);
return(q);
}
}
public bool IsEmailTemplateNameUnique(int id, string name)
{
using (var model = new gb_ts_stagingEntities())
{
var record = model.tic_EmailTemplate.FirstOrDefault(rec => rec.Name == name && rec.IsActive && rec.Id != id);
return(record == null);
}
}
public IList <TicketModel> GetList()
{
using (var model = new gb_ts_stagingEntities())
{
var q = GetFullList(model).ToList();
return(q);
}
}
public IList <TicketWorkflow> GetList()
{
using (var model = new gb_ts_stagingEntities())
{
var q = (from ticketWorkflow in model.tic_TicketWorkflow
join issueType in model.tic_IssueTypes on ticketWorkflow.IssueTypeId equals issueType.Id into inIssueType
from outIssueType in inIssueType.DefaultIfEmpty()
join product in model.tic_Products on ticketWorkflow.ProductId equals product.Id into inProduct
from outProduct in inProduct.DefaultIfEmpty()
join productCategory in model.tic_ProductCategories on ticketWorkflow.ProductCategoryId equals productCategory.Id into inProductCategory
from outProductCategory in inProductCategory.DefaultIfEmpty()
join ticketType in model.tic_TicketTypes on ticketWorkflow.TicketTypeId equals ticketType.Id into inTicketType
from outTicketType in inTicketType.DefaultIfEmpty()
join department in model.tic_Departments on ticketWorkflow.DepartmentId equals department.Id
where ticketWorkflow.IsActive
orderby ticketWorkflow.SortOrder
select new TicketWorkflow
{
Id = ticketWorkflow.Id,
IssueType = outIssueType == null ? null : new IssueType
{
Id = outIssueType.Id,
Name = outIssueType.Name
},
Product = outProduct == null ? null : new Product
{
Id = outProduct.Id,
Name = outProduct.Name
},
ProductCategory = outProductCategory == null ? null : new ProductCategory
{
Id = outProductCategory.Id,
Name = outProductCategory.Name
},
TicketType = outTicketType == null ? null : new TicketType
{
Id = outTicketType.Id,
Name = outTicketType.Name
},
Department = new Department
{
Id = department.Id,
Name = department.Name
},
CustomerPriorityId = ticketWorkflow.CustomerPriorityId,
SortOrder = ticketWorkflow.SortOrder
}).ToList();
return(q);
}
}
public bool IsTicketTypeNameUnique(string name, int id)
{
using (var model = new gb_ts_stagingEntities())
{
var record = model.tic_TicketTypes.FirstOrDefault(rec => rec.Name == name && rec.IsActive && rec.Id != id);
return(record == null);
}
}
public bool IsCustomFieldIdentifierUnique(string identifier, int id)
{
using (var model = new gb_ts_stagingEntities())
{
var record = model.tic_CustomFields.First(rec => rec.Identifier == identifier && rec.IsActive && rec.Id != id);
return(record == null);
}
}
public bool IsCustomFieldNameUnique(string name, int id)
{
using (var model = new gb_ts_stagingEntities())
{
var record = model.tic_CustomFields.First(rec => rec.Name == name && rec.IsActive && rec.Id != id);
return(record == null);
}
}
public IList <Error> Validate(TicketWorkflow record)
{
using (var model = new gb_ts_stagingEntities())
{
record.IssueTypeId = record.IssueType?.Id;
if (record.IssueTypeId == 0)
{
record.IssueTypeId = null;
}
record.ProductCategoryId = record.ProductCategory?.Id;
if (record.ProductCategoryId == 0)
{
record.ProductCategoryId = null;
}
if (record.CustomerPriorityId == 0)
{
record.CustomerPriorityId = null;
}
record.TicketTypeId = record.TicketType?.Id;
if (record.TicketTypeId == 0)
{
record.TicketTypeId = null;
}
record.ProductId = record.Product?.Id;
if (record.ProductId == 0)
{
record.ProductId = null;
}
var q =
model.tic_TicketWorkflow.FirstOrDefault(
rec =>
rec.IsActive &&
rec.IssueTypeId == record.IssueTypeId &&
rec.ProductCategoryId == record.ProductCategoryId &&
rec.CustomerPriorityId == record.CustomerPriorityId &&
rec.ProductId == record.ProductId &&
rec.TicketTypeId == record.TicketTypeId &&
rec.Id != record.Id);
if (q == null)
{
return(null);
}
return(new List <Error> {
new Error {
Code = 1, ErrorText = "Workflow rule should be unique."
}
});
}
}
public void Delete(int id)
{
using (var model = new gb_ts_stagingEntities())
{
var recordToDelete = model.TicketsLogs.First(rec => rec.Id == id);
model.TicketsLogs.Remove(recordToDelete);
model.SaveChanges();
}
}
public string GetTicketStatusName(int ticketId)
{
using (var model = new gb_ts_stagingEntities())
{
var ticket = model.Tickets.First(rec => rec.Id == ticketId);
var ticketStatus = model.tic_TicketStatuses.First(t => t.Id == ticket.StatusId);
return(ticketStatus.Name);
}
}
public IList <User> GetUsersByRole(string role)
{
using (var model = new gb_ts_stagingEntities())
{
var q = model.users.Where(u => u.roles.Any(r => r.Name == role)).Select(u => new User {
Id = u.Id, Email = u.Email
}).ToList();
return(q);
}
}
public IList <int> GetUpdatedTickets(DateTime fromTime)
{
using (var model = new gb_ts_stagingEntities())
{
var q = (from ticket in model.Tickets
where ticket.CreationDate >= fromTime || ticket.LastUpdate >= fromTime
where ticket.StatusId > 0
select ticket.Id).ToList();
return(q);
}
}
public IList <TicketModel> GetTicketsByDepartmentIdAndStatuses(List <int> departmentIds, IList <TicketStatusId> ticketStatusIds)
{
using (var model = new gb_ts_stagingEntities())
{
var q =
GetFullList(model)
.Where(m => ticketStatusIds.Select(st => (int)st).Contains(m.TicketStatus.Id) && departmentIds.Contains(m.DepartmentId.Value))
.ToList();
return(q);
}
}
public IList <TicketModel> GetTicketsByDepartmentIdAndAssignee(List <int> departmentIds, string userId)
{
using (var model = new gb_ts_stagingEntities())
{
var q =
GetFullList(model)
.Where(m => m.AssignedTo == userId && departmentIds.Contains(m.DepartmentId.Value))
.ToList();
return(q);
}
}
public IList <TicketModel> GetTicketsByDepartmentId(List <int> departmentIds)
{
using (var model = new gb_ts_stagingEntities())
{
var q =
GetFullList(model)
.Where(m => departmentIds.Contains(m.DepartmentId.Value))
.ToList();
return(q);
}
}
public IList <string> GetTableFieldNames(string tableName)
{
checkSqlInjection(tableName);
using (var model = new gb_ts_stagingEntities())
{
var query = "DESCRIBE " + tableName;
var fields = model.Database.SqlQuery <TableFieldMetaData>(query).ToList();
return(fields.Select(rec => rec.Field).OrderBy(rec => rec).ToList());
}
}
public void Delete(int id)
{
using (var model = new gb_ts_stagingEntities())
{
var recordToDelete = model.tic_EmailTemplate.First(rec => rec.Id == id);
recordToDelete.UpdatedBy = "Admin";
recordToDelete.LastUpdate = DateTime.Now;
recordToDelete.IsActive = false;
model.SaveChanges();
}
}
