public SkillFilterFacetDto(TaxonomyTerm taxonomyTerm, IReadOnlyList <SkillTaxonomyTerm> skillIds) { Id = taxonomyTerm.Id; Name = taxonomyTerm.Name; MatchedSkillIds = skillIds.Select(s => s.SkillId).ToList(); SkillFilterId = taxonomyTerm.TaxonomyId; }
public SkillGroupDto(TaxonomyTerm taxonomyTerm, IReadOnlyList <TaxonomyTerm> taxonomyTerms, IReadOnlyList <SkillTaxonomyTerm> skillTaxonomyTerms, IReadOnlyList <Skill> skills) { Id = taxonomyTerm.Id; Name = taxonomyTerm.Name; ChildGroups = taxonomyTerms.Where(t => t.ParentTaxonomyTermId == taxonomyTerm.Id).Select(t => new SkillGroupDto(t, taxonomyTerms, skillTaxonomyTerms, skills)).ToList(); Skills = skills.Join( skillTaxonomyTerms.Where(s => s.TaxonomyTermId == taxonomyTerm.Id), s => s.Id, t => t.SkillId, (s, t) => new SkillDto(s, skills) ).ToList(); }
// [RequireActivity("Content.AdminWrite")] public IActionResult Create(CreateTermModel model) { // Rule: (Name, TypeId) must be a unique tuple. if (data.TaxonomyTerms.Any(x => x.TaxonomyId == model.TaxonomyId && x.Name == model.Name)) { return Conflict("Name and TypeId must be unique"); } // Rule: Type must exist. var taxonomy = data.Taxonomies.Find(model.TaxonomyId); if (taxonomy == null) { return Conflict("Type must exist"); } // Rule: If ParentId is specified, it must exist. if (model.ParentId.HasValue) { var parent = data.TaxonomyTerms.Find(model.ParentId); if (parent == null) { return Conflict("Invalid ParentId"); } // Rule: Parent must belong to the same type. if (parent.TaxonomyId != model.TaxonomyId) { return Conflict("Parent term is not a member of the same type"); } } var term = new TaxonomyTerm { Name = model.Name, ParentId = model.ParentId, TaxonomyId = model.TaxonomyId }; data.Add(term); data.SaveChanges(); return Ok(term); }
public async Task <IReadOnlyList <string> > ImportSkillsAsync(int courseId, string description, Stream fileStream) { var failureMessages = new List <string>(); // Get the course and its skill sets and self assessment scales. var categoryRegExMatcher = new Regex(@"(.*?)(?<!\/)(?:\/(?!\/)|$)"); IList <SelfAssessmentScale> selfAssessmentScales = new List <SelfAssessmentScale>(); IList <Taxonomy> taxonomies = new List <Taxonomy>(); IList <TaxonomyTerm> taxonomyTerms = new List <TaxonomyTerm>(); using (var connection = _dbService.GetConnection()){ await connection.OpenAsync(); var course = await connection.QueryFirstAsync <Course>(@" SELECT * FROM [dbo].[Course] WHERE Id = @Id", new { Id = courseId }); // Validate the course exists. if (course == null) { failureMessages.Add("The course does not exist."); return(failureMessages); } // Do this in a transaction. using (var transaction = connection.BeginTransaction()){ var csvReader = new CsvReader(new StreamReader(fileStream)); csvReader.Configuration.HasHeaderRecord = false; // Flags to set what is currently being read in the file. var processingSelfAssessmentScales = false; var processingSkills = false; var newSkills = new List <ImportedSkill>(); // Get the latest SkillsSet for the course. var latestSkillSetVersion = (await connection.QueryAsync <int?>(@" SELECT MAX(Version) FROM [dbo].[SkillSet] INNER JOIN [dbo].[CourseSkillSet] ON [SkillSet].[Id] = [CourseSkillSet].[SkillSetId] WHERE [CourseId] = @CourseId", new { CourseId = courseId }, transaction)) .Single(); var skillSetVersion = latestSkillSetVersion.HasValue ? latestSkillSetVersion.Value + 1 : 1; // Create the SkillsSet. var skillSetId = (await connection.QueryAsync <int>(@" INSERT INTO [dbo].[SkillSet] ([Version] ,[Name] ,[Description] ,[CreatedAt] ,[CreatedBy] ,[LastUpdatedAt] ,[LastUpdatedBy] ,[SelfAssignable]) VALUES (@Version ,@Name ,@Description ,@When ,-1 --System User ,@When ,-1 --System User ,1); SELECT CAST(SCOPE_IDENTITY() AS INT)", new { Version = skillSetVersion, Name = $"{course.Title} Skills (v{skillSetVersion})", Description = description, When = DateTime.UtcNow }, transaction)) .Single(); // Create the Course/SkillsSet relationship. await connection.ExecuteAsync(@" INSERT INTO [dbo].[CourseSkillSet] ([CourseId] ,[SkillSetId]) VALUES (@CourseId ,@SkillSetId)", new { CourseId = courseId, SkillSetId = skillSetId }, transaction); // Read each line of the file. while (csvReader.Read()) { // Set Processing self assessment scales? processingSkills = processingSkills || csvReader.IsSkillsListMarkerRow(); processingSelfAssessmentScales = !processingSkills && (processingSelfAssessmentScales || csvReader.IsSelfAssessmentScalesMarkerRow()); // Process Self Assessment scales. if (processingSelfAssessmentScales) { if (csvReader.IsSelfAssessmentScalesMarkerRow() || csvReader.IsSelfAssessmentScalesHeaderRow()) { continue; // Move to the next row if we are on the marker or header. } // Check we don't already have a scale with the same name. var scaleName = csvReader.GetField <string>(0); if (string.IsNullOrWhiteSpace(scaleName)) { continue; // Empty row. } if (selfAssessmentScales.Any(s => s.Name.Equals(scaleName, StringComparison.OrdinalIgnoreCase))) { failureMessages.Add($"There is an existing Self Assessment scale with the name '{scaleName}', names must be unique."); } else { // Create the scale. var selfAssessmentScaleId = (await connection.QueryAsync <int>(@" INSERT INTO [dbo].[SelfAssessmentScale] ([Name]) VALUES (@Name); SELECT CAST(SCOPE_IDENTITY() AS INT)", new { Name = scaleName }, transaction)) .Single(); selfAssessmentScales.Add(new SelfAssessmentScale { Id = selfAssessmentScaleId, Name = scaleName }); // Calculate the equal score for each level. var levels = csvReader.CurrentRecord.Skip(1).ToList(); // Trim off any empty levels at the end. if (string.IsNullOrEmpty(levels.Last())) { var lastLevelIndex = levels.FindLastIndex(l => !string.IsNullOrEmpty(l)); levels.RemoveRange(lastLevelIndex + 1, levels.Count - lastLevelIndex - 1); } var score = 100 / (levels.Count - 1); // We discount a level, as the first is set to 0. // Do we need to spread any remainder? var scoreRemainder = 100 - (score * (levels.Count - 1)); // Add the levels. await connection.ExecuteAsync(@" INSERT INTO [dbo].[SelfAssessmentLevel] ([Name], [Score], [SelfAssessmentScaleId]) VALUES (@Name, @Score, @SelfAssessmentScaleId);", levels.Select((level, index) => new { Name = level, Score = (score * (index)) + (scoreRemainder > 0 ? (index < scoreRemainder ? index : scoreRemainder) : 0), // Add to the score if needed to balance the total. SelfAssessmentScaleId = selfAssessmentScaleId }), transaction ); } } // Process Skills. if (processingSkills) { if (csvReader.IsSkillsListMarkerRow() || csvReader.IsSkillsHeaderRow()) { continue; // Move to the next row if we are on the marker or header. } var skill = new ImportedSkill { Name = csvReader.GetField <string>(0), ParentSkill = csvReader.GetField <string>(1), CanSelfAssess = csvReader.GetField <string>(2), SelfAssessmentScale = csvReader.GetField <string>(3), CanSelfCount = csvReader.GetField <string>(4), Categories = csvReader.CurrentRecord.Skip(5).Where(r => !string.IsNullOrWhiteSpace(r)).ToList() }; // Need to find the parent? if (!string.IsNullOrWhiteSpace(skill.ParentSkill)) { var parentSkill = newSkills.FirstOrDefault(s => ((string)s.Name).Equals(skill.ParentSkill, StringComparison.OrdinalIgnoreCase)); if (parentSkill == null) { // Parent skill does not exist. failureMessages.Add($"The parent skill '{skill.ParentSkill}' does not exist in the file, make sure it appears before its children and is spelt the same."); continue; } skill.ParentSkillId = parentSkill.Id; } // Need to set the self assessment scale? if (!string.IsNullOrWhiteSpace(skill.SelfAssessmentScale)) { var selfAssessmentScale = selfAssessmentScales.FirstOrDefault(s => ((string)s.Name).Equals(skill.SelfAssessmentScale, StringComparison.OrdinalIgnoreCase)); if (selfAssessmentScale == null) { // Self assessment scale skill does not exist. failureMessages.Add($"The Self Assessment Scale '{skill.SelfAssessmentScale}' does not exist."); continue; } skill.SelfAssessmentScaleId = selfAssessmentScale.Id; } // Create the skill. skill.Id = (await connection.QueryAsync <int>(@" INSERT INTO [dbo].[Skill] ([Name] ,[CanSelfAssess] ,[ParentSkillId] ,[SkillSetId] ,[CanSelfCount] ,[SelfAssessmentScaleId] ,[Removed] ,[CreatedAt] ,[CreatedBy] ,[LastUpdatedAt] ,[LastUpdatedBy]) VALUES (@Name ,@CanSelfAssess ,@ParentSkillId ,@SkillSetId ,@CanSelfCount ,@SelfAssessmentScaleId ,0 ,@When ,-1 --System User ,@When ,-1 --System User ); SELECT CAST(SCOPE_IDENTITY() AS INT)", new { Name = skill.Name, CanSelfAssess = skill.CanSelfAssess.Equals("yes", StringComparison.OrdinalIgnoreCase) ? 1 : 0, ParentSkillId = skill.ParentSkillId, SkillSetId = skillSetId, CanSelfCount = skill.CanSelfCount.Equals("yes", StringComparison.OrdinalIgnoreCase) ? 1 : 0, SelfAssessmentScaleId = skill.SelfAssessmentScaleId, When = DateTime.UtcNow }, transaction)) .Single(); // Process the taxonomy terms. foreach (var category in skill.Categories) { // Each skill entry can have multiple categories attached to it. // Each category is in the form [TaxonomyName]/[TaxonomyTerm]/[TaxonomyTerm] // So terms can be nested, i.e. a hierarchy var skillCategories = categoryRegExMatcher.Split(category).Where(c => !string.IsNullOrWhiteSpace(c)).ToList(); // Check the taxonomy exists, otherwise create it. var taxonomy = taxonomies.FirstOrDefault(t => t.Name.Equals(skillCategories[0], StringComparison.OrdinalIgnoreCase)); if (taxonomy == null) { // Create the taxonomy. taxonomy = new Taxonomy { Name = skillCategories[0] }; taxonomy.Id = (await connection.QueryAsync <int>(@" INSERT INTO [dbo].[Taxonomy] ([Name] ,[Type] ,[SkillSetId] ,[Removed] ,[CreatedAt] ,[CreatedBy] ,[LastUpdatedAt] ,[LastUpdatedBy]) VALUES (@Name ,0 ,@SkillSetId ,0 ,@When ,-1 --System User ,@When ,-1 --System User ); SELECT CAST(SCOPE_IDENTITY() AS INT)", new { Name = taxonomy.Name, SkillSetId = skillSetId, When = DateTime.UtcNow }, transaction)) .Single(); // Add the new taxonomy into the list. taxonomies.Add(taxonomy); } // Check each of the terms exist and then associate them with the skill. TaxonomyTerm parentTaxonomyTerm = null; int index = 1; foreach (var skillTaxonomyTerm in skillCategories.Skip(1)) { var skillName = skillTaxonomyTerm.Replace("//", "/"); // Replace any escaped values. var taxonomyTerm = taxonomyTerms.FirstOrDefault(t => t.Name.Equals(skillName, StringComparison.OrdinalIgnoreCase) && t.TaxonomyId == taxonomy.Id && (parentTaxonomyTerm == null || t.ParentTaxonomyTermId == parentTaxonomyTerm.Id)); if (taxonomyTerm == null) { // Create the taxonomy term. taxonomyTerm = new TaxonomyTerm { Name = skillName, TaxonomyId = taxonomy.Id, ParentTaxonomyTermId = parentTaxonomyTerm == null ? null : (int?)parentTaxonomyTerm.Id }; taxonomyTerm.Id = (await connection.QueryAsync <int>(@" INSERT INTO [dbo].[TaxonomyTerm] ([Name] ,[TaxonomyId] ,[ParentTaxonomyTermId] ,[Removed] ,[CreatedAt] ,[CreatedBy] ,[LastUpdatedAt] ,[LastUpdatedBy]) VALUES (@Name ,@TaxonomyId ,@ParentTaxonomyTermId ,0 ,@When ,-1 --System User ,@When ,-1 --System User ); SELECT CAST(SCOPE_IDENTITY() AS INT)", new { Name = taxonomyTerm.Name, TaxonomyId = taxonomyTerm.TaxonomyId, ParentTaxonomyTermId = taxonomyTerm.ParentTaxonomyTermId, When = DateTime.UtcNow }, transaction)) .Single(); // Add the new taxonomy term into the list. taxonomyTerms.Add(taxonomyTerm); } parentTaxonomyTerm = taxonomyTerm; index++; // If we at the end then associate this term with the skill. if (index == skillCategories.Count) { await connection.ExecuteAsync(@" IF (SELECT COUNT(*) FROM [dbo].[SkillTaxonomyTerm] WHERE SkillId = @SkillId AND TaxonomyTermId = @TaxonomyTermId) = 0 INSERT INTO [dbo].[SkillTaxonomyTerm] ([SkillId] ,[TaxonomyTermId] ,[CreatedAt] ,[CreatedBy]) VALUES (@SkillId ,@TaxonomyTermId ,@When ,-1 --System User );", new { SkillId = skill.Id, TaxonomyTermId = taxonomyTerm.Id, When = DateTime.UtcNow }, transaction ); } } } newSkills.Add(skill); } } // Any failure messages? if (failureMessages.Any()) { transaction.Rollback(); } else { transaction.Commit(); } } } return(failureMessages); }