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(); } }