public async Task <OneOf <CreateTLevelFailedReason, Success> > Execute( SqlTransaction transaction, CreateTLevel query) { var sql = $@" INSERT INTO Pttcd.TLevels ( TLevelId, TLevelStatus, CreatedOn, CreatedByUserId, UpdatedOn, UpdatedByUserId, ProviderId, TLevelDefinitionId, WhoFor, EntryRequirements, WhatYoullLearn, HowYoullLearn, HowYoullBeAssessed, WhatYouCanDoNext, YourReference, StartDate, Website ) VALUES ( @TLevelId, @TLevelStatus, @CreatedOn, @CreatedByUserId, @UpdatedOn, @UpdatedByUserId, @ProviderId, @TLevelDefinitionId, @WhoFor, @EntryRequirements, @WhatYoullLearn, @HowYoullLearn, @HowYoullBeAssessed, @WhatYouCanDoNext, @YourReference, @StartDate, @Website ) INSERT INTO Pttcd.TLevelLocations (TLevelLocationId, TLevelId, VenueId, TLevelLocationStatus) SELECT NEWID(), @TLevelId, Id, @TLevelLocationStatus FROM @LocationVenueIds DECLARE @TLevelIds Pttcd.GuidIdTable INSERT INTO @TLevelIds VALUES (@TLevelId) EXEC Pttcd.RefreshFindACourseIndexForTLevels @TLevelIds, @CreatedOn"; try { await transaction.Connection.ExecuteAsync( sql, param : new { query.TLevelId, TLevelStatus = TLevelStatus.Live, CreatedOn = query.CreatedOn, CreatedByUserId = query.CreatedBy.UserId, UpdatedOn = query.CreatedOn, UpdatedByUserId = query.CreatedBy.UserId, query.ProviderId, query.TLevelDefinitionId, query.WhoFor, query.EntryRequirements, query.WhatYoullLearn, query.HowYoullLearn, query.HowYoullBeAssessed, query.WhatYouCanDoNext, query.YourReference, query.StartDate, query.Website, LocationVenueIds = TvpHelper.CreateGuidIdTable(query.LocationVenueIds), TLevelLocationStatus = TLevelLocationStatus.Live }, transaction : transaction); } catch (SqlException ex) when(ex.Number == 2601 && ex.Message.Contains("'IX_TLevels_DefinitionProviderStartDate'")) { return(CreateTLevelFailedReason.TLevelAlreadyExistsForDate); } return(new Success()); }
public async Task <None> Execute( SqlTransaction transaction, DeleteTLevelsForProviderWithTLevelDefinitions query) { var sql = @" DECLARE @TLevelIds Pttcd.GuidIdTable UPDATE Pttcd.TLevels SET TLevelStatus = @DeletedTLevelStatus, DeletedOn = @DeletedOn, DeletedByUserId = @DeletedByUserId OUTPUT INSERTED.TLevelId INTO @TLevelIds FROM Pttcd.TLevels t JOIN @TLevelDefinitionIds x ON t.TLevelDefinitionId = x.Id WHERE t.ProviderId = @ProviderId AND t.TLevelStatus = @LiveTLevelStatus EXEC Pttcd.RefreshFindACourseIndexForTLevels @TLevelIds, @DeletedOn"; await transaction.Connection.ExecuteAsync( sql, new { query.ProviderId, query.DeletedOn, DeletedByUserId = query.DeletedBy.UserId, TLevelDefinitionIds = TvpHelper.CreateGuidIdTable(query.TLevelDefinitionIds), LiveTLevelStatus = TLevelStatus.Live, DeletedTLevelStatus = TLevelLocationStatus.Deleted }, transaction : transaction); return(new None()); }
public async Task <Success> Execute(SqlTransaction transaction, UpdateFindACourseIndexForVenues query) { var sql = $@" UPDATE Pttcd.FindACourseIndex SET LastSynced = @Now, VenueName = v.VenueName, VenueAddress = STUFF( CONCAT( NULLIF(', ' + v.AddressLine1, ', '), NULLIF(', ' + v.AddressLine2, ', '), NULLIF(', ' + v.Town, ', '), NULLIF(', ' + v.County, ', '), NULLIF(', ' + v.Postcode, ', ')), 1, 2, NULL), VenueTown = v.Town FROM Pttcd.FindACourseIndex i JOIN Pttcd.Venues v ON i.VenueId = v.VenueId JOIN @VenueIds x ON v.VenueId = x.Id WHERE Live = 1"; var paramz = new { VenueIds = TvpHelper.CreateGuidIdTable(query.VenueIds), query.Now }; await transaction.Connection.ExecuteAsync(sql, paramz, transaction); return(new Success()); }
public async Task <IReadOnlyDictionary <Guid, Provider> > Execute(SqlTransaction transaction, GetProvidersByIds query) { var sqlParameters = new { ProviderIds = TvpHelper.CreateGuidIdTable(query.ProviderIds) }; var sql = @$ " SELECT {nameof(Provider.ProviderId)},
public async Task <Success> Execute(SqlTransaction transaction, UpdateFindACourseIndexForCourseRuns query) { var paramz = new { CourseRunIds = TvpHelper.CreateGuidIdTable(query.CourseRunIds), Now = query.Now }; await transaction.Connection.ExecuteAsync( "Pttcd.RefreshFindACourseIndex", paramz, transaction, commandType : System.Data.CommandType.StoredProcedure); return(new Success()); }
public async Task <IReadOnlyDictionary <Guid, Venue> > Execute( SqlTransaction transaction, GetVenuesByIds query) { var sql = $@" SELECT v.VenueId, v.ProviderId, v.ProviderUkprn, v.VenueName, v.ProviderVenueRef, v.AddressLine1, v.AddressLine2, v.Town, v.County, v.Postcode, v.Telephone, v.Email, v.Website, v.Position.Lat Latitude, v.Position.Long Longitude FROM Pttcd.Venues v JOIN @VenueIds x ON v.VenueId = x.Id WHERE v.VenueStatus = {(int)VenueStatus.Live}"; var param = new { VenueIds = TvpHelper.CreateGuidIdTable(query.VenueIds) }; return((await transaction.Connection.QueryAsync <Venue>(sql, param, transaction)) .ToDictionary(v => v.VenueId, v => v)); }
public async Task <IReadOnlyDictionary <Guid, Provider> > Execute(SqlTransaction transaction, GetProvidersByIds query) { var sqlParameters = new { ProviderIds = TvpHelper.CreateGuidIdTable(query.ProviderIds) }; var sql = @$ " SELECT ProviderId, Ukprn, ProviderName, ProviderType, Alias, DisplayNameSource, ApprenticeshipQAStatus, BulkUploadInProgress, BulkUploadPublishInProgress, BulkUploadStartedDateTime, BulkUploadTotalRowCount, LearnerSatisfaction, EmployerSatisfaction FROM Pttcd.Providers WHERE {nameof(Provider.ProviderId)} IN (SELECT * FROM @{nameof(sqlParameters.ProviderIds)})";
public async Task <None> Execute(SqlTransaction transaction, UpsertCoursesFromCosmos query) { await UpsertCourses(); await UpsertCourseRuns(); await UpsertCourseRunRegions(); await UpsertCourseRunSubRegions(); await UpdateFindACourseIndex(); async Task UpsertCourses() { var createTableSql = @" CREATE TABLE #Courses ( CourseId UNIQUEIDENTIFIER, CourseStatus INT, CreatedOn DATETIME, CreatedBy NVARCHAR(MAX), UpdatedOn DATETIME, UpdatedBy NVARCHAR(MAX), TribalCourseId INT, LearnAimRef VARCHAR(50), ProviderUkprn INT, CourseDescription NVARCHAR(MAX), EntryRequirements NVARCHAR(MAX), WhatYoullLearn NVARCHAR(MAX), HowYoullLearn NVARCHAR(MAX), WhatYoullNeed NVARCHAR(MAX), HowYoullBeAssessed NVARCHAR(MAX), WhereNext NVARCHAR(MAX), BulkUploadErrorCount INT )"; await transaction.Connection.ExecuteAsync(createTableSql, transaction : transaction); await BulkCopyHelper.WriteRecords( query.Records.Select(r => new { r.CourseId, r.CourseStatus, r.CreatedOn, r.CreatedBy, r.UpdatedOn, r.UpdatedBy, r.TribalCourseId, r.LearnAimRef, r.ProviderUkprn, r.CourseDescription, r.EntryRequirements, r.WhatYoullLearn, r.HowYoullLearn, r.WhatYoullNeed, r.HowYoullBeAssessed, r.WhereNext, r.BulkUploadErrorCount }), tableName : "#Courses", transaction); var mergeSql = @" MERGE Pttcd.Courses AS target USING ( SELECT CourseId, CourseStatus, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, TribalCourseId, LearnAimRef, ProviderUkprn, CourseDescription, EntryRequirements, WhatYoullLearn, HowYoullLearn, WhatYoullNeed, HowYoullBeAssessed, WhereNext, BulkUploadErrorCount FROM #Courses ) AS source ON target.CourseId = source.CourseId WHEN NOT MATCHED THEN INSERT ( CourseId, LastSyncedFromCosmos, CourseStatus, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, TribalCourseId, LearnAimRef, ProviderUkprn, CourseDescription, EntryRequirements, WhatYoullLearn, HowYoullLearn, WhatYoullNeed, HowYoullBeAssessed, WhereNext, BulkUploadErrorCount ) VALUES ( source.CourseId, @LastSyncedFromCosmos, source.CourseStatus, source.CreatedOn, source.CreatedBy, source.UpdatedOn, source.UpdatedBy, source.TribalCourseId, source.LearnAimRef, source.ProviderUkprn, source.CourseDescription, source.EntryRequirements, source.WhatYoullLearn, source.HowYoullLearn, source.WhatYoullNeed, source.HowYoullBeAssessed, source.WhereNext, source.BulkUploadErrorCount ) WHEN MATCHED THEN UPDATE SET CourseStatus = source.CourseStatus, LastSyncedFromCosmos = @LastSyncedFromCosmos, CreatedOn = source.CreatedOn, CreatedBy = source.CreatedBy, UpdatedOn = source.UpdatedOn, UpdatedBy = source.UpdatedBy, TribalCourseId = source.TribalCourseId, LearnAimRef = source.LearnAimRef, ProviderUkprn = source.ProviderUkprn, CourseDescription = source.CourseDescription, EntryRequirements = source.EntryRequirements, WhatYoullLearn = source.WhatYoullLearn, HowYoullLearn = source.HowYoullLearn, WhatYoullNeed = source.WhatYoullNeed, HowYoullBeAssessed = source.HowYoullBeAssessed, WhereNext = source.WhereNext, BulkUploadErrorCount = source.BulkUploadErrorCount;"; await transaction.Connection.ExecuteAsync( mergeSql, param : new { query.LastSyncedFromCosmos }, transaction : transaction); } async Task UpsertCourseRuns() { var createTableSql = @" CREATE TABLE #CourseRuns ( CourseRunId UNIQUEIDENTIFIER, CourseId UNIQUEIDENTIFIER, CourseRunStatus INT, CreatedOn DATETIME, CreatedBy NVARCHAR(MAX), UpdatedOn DATETIME, UpdatedBy NVARCHAR(MAX), CourseName NVARCHAR(MAX), VenueId UNIQUEIDENTIFIER, ProviderCourseId NVARCHAR(MAX), DeliveryMode TINYINT, FlexibleStartDate BIT, StartDate DATE, CourseWebsite NVARCHAR(MAX), Cost INT, CostDescription NVARCHAR(MAX), DurationUnit TINYINT, DurationValue INT, StudyMode TINYINT, AttendancePattern TINYINT, [National] BIT, BulkUploadErrorCount INT )"; await transaction.Connection.ExecuteAsync(createTableSql, transaction : transaction); await BulkCopyHelper.WriteRecords( query.Records.SelectMany(c => c.CourseRuns.Select(cr => new { cr.CourseRunId, c.CourseId, cr.CourseRunStatus, cr.CreatedOn, cr.CreatedBy, cr.UpdatedOn, cr.UpdatedBy, cr.CourseName, cr.VenueId, cr.ProviderCourseId, DeliveryMode = (byte)cr.DeliveryMode, cr.FlexibleStartDate, cr.StartDate, cr.CourseWebsite, Cost = cr.Cost.HasValue ? (int?)(cr.Cost * 100) : null, // SqlBulkCopy truncates decimals - convert to int cr.CostDescription, DurationUnit = (byte)cr.DurationUnit, cr.DurationValue, cr.StudyMode, AttendancePattern = (byte)cr.AttendancePattern, cr.National, cr.BulkUploadErrorCount })), tableName : "#CourseRuns", transaction); var mergeSql = @" MERGE Pttcd.CourseRuns AS target USING ( SELECT CourseRunId, CourseId, CourseRunStatus, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, CourseName, VenueId, ProviderCourseId, DeliveryMode, FlexibleStartDate, StartDate, CourseWebsite, Cost, CostDescription, DurationUnit, DurationValue, StudyMode, AttendancePattern, [National], BulkUploadErrorCount FROM #CourseRuns ) AS source ON target.CourseRunId = source.CourseRunId WHEN NOT MATCHED THEN INSERT ( CourseRunId, CourseId, CourseRunStatus, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, CourseName, VenueId, ProviderCourseId, DeliveryMode, FlexibleStartDate, StartDate, CourseWebsite, Cost, CostDescription, DurationUnit, DurationValue, StudyMode, AttendancePattern, [National], BulkUploadErrorCount ) VALUES ( source.CourseRunId, source.CourseId, source.CourseRunStatus, source.CreatedOn, source.CreatedBy, source.UpdatedOn, source.UpdatedBy, source.CourseName, source.VenueId, source.ProviderCourseId, source.DeliveryMode, source.FlexibleStartDate, source.StartDate, source.CourseWebsite, CONVERT(DECIMAL, source.Cost) / 100, source.CostDescription, source.DurationUnit, source.DurationValue, source.StudyMode, source.AttendancePattern, source.[National], source.BulkUploadErrorCount ) WHEN MATCHED THEN UPDATE SET CourseId = source.CourseId, CourseRunStatus = source.CourseRunStatus, CreatedOn = source.CreatedOn, CreatedBy = source.CreatedBy, UpdatedOn = source.UpdatedOn, UpdatedBy = source.UpdatedBy, CourseName = source.CourseName, VenueId = source.VenueId, ProviderCourseId = source.ProviderCourseId, DeliveryMode = source.DeliveryMode, FlexibleStartDate = source.FlexibleStartDate, StartDate = source.StartDate, CourseWebsite = source.CourseWebsite, Cost = CONVERT(DECIMAL, source.Cost) / 100, CostDescription = source.CostDescription, DurationUnit = source.DurationUnit, DurationValue = source.DurationValue, StudyMode = source.StudyMode, AttendancePattern = source.AttendancePattern, [National] = source.[National], BulkUploadErrorCount = source.BulkUploadErrorCount WHEN NOT MATCHED BY SOURCE AND target.CourseId IN (SELECT CourseId FROM #Courses) THEN DELETE;"; await transaction.Connection.ExecuteAsync(mergeSql, transaction : transaction); } async Task UpsertCourseRunRegions() { var createTableSql = @" CREATE TABLE #CourseRunRegions ( CourseRunId UNIQUEIDENTIFIER, RegionId VARCHAR(9) COLLATE SQL_Latin1_General_CP1_CI_AS )"; await transaction.Connection.ExecuteAsync(createTableSql, transaction : transaction); await BulkCopyHelper.WriteRecords( query.Records.SelectMany(c => c.CourseRuns.SelectMany(cr => cr.RegionIds.Select(r => new { cr.CourseRunId, RegionId = r }))), tableName : "#CourseRunRegions", transaction); var mergeSql = @" MERGE Pttcd.CourseRunRegions AS target USING ( SELECT CourseRunId, RegionId FROM #CourseRunRegions ) AS source ON target.CourseRunId = source.CourseRunId AND target.RegionId = source.RegionId WHEN NOT MATCHED THEN INSERT (CourseRunId, RegionId) VALUES (source.CourseRunId, source.RegionId) WHEN NOT MATCHED BY SOURCE AND target.CourseRunId IN (SELECT CourseRunId FROM #CourseRuns) THEN DELETE;"; await transaction.Connection.ExecuteAsync(mergeSql, transaction : transaction); } async Task UpsertCourseRunSubRegions() { var createTableSql = @" CREATE TABLE #CourseRunSubRegions ( CourseRunId UNIQUEIDENTIFIER, RegionId VARCHAR(9) COLLATE SQL_Latin1_General_CP1_CI_AS )"; await transaction.Connection.ExecuteAsync(createTableSql, transaction : transaction); await BulkCopyHelper.WriteRecords( query.Records.SelectMany(c => c.CourseRuns.SelectMany(cr => cr.SubRegionIds.Select(r => new { cr.CourseRunId, RegionId = r }))), tableName : "#CourseRunSubRegions", transaction); var mergeSql = @" MERGE Pttcd.CourseRunSubRegions AS target USING ( SELECT CourseRunId, RegionId FROM #CourseRunSubRegions ) AS source ON target.CourseRunId = source.CourseRunId AND target.RegionId = source.RegionId WHEN NOT MATCHED THEN INSERT (CourseRunId, RegionId) VALUES (source.CourseRunId, source.RegionId) WHEN NOT MATCHED BY SOURCE AND target.CourseRunId IN (SELECT CourseRunId FROM #CourseRuns) THEN DELETE;"; await transaction.Connection.ExecuteAsync(mergeSql, transaction : transaction); } Task UpdateFindACourseIndex() { var courseRunIds = query.Records.SelectMany(c => c.CourseRuns).Select(cr => cr.CourseRunId); return(transaction.Connection.ExecuteAsync( "Pttcd.RefreshFindACourseIndex", param: new { CourseRunIds = TvpHelper.CreateGuidIdTable(courseRunIds), Now = query.LastSyncedFromCosmos }, transaction: transaction, commandType: System.Data.CommandType.StoredProcedure)); } return(new None()); }
public async Task <OneOf <NotFound, UpdateTLevelFailedReason, Success> > Execute( SqlTransaction transaction, UpdateTLevel query) { var sql = $@" DECLARE @Result INT UPDATE Pttcd.TLevels SET UpdatedOn = @UpdatedOn, UpdatedByUserId = @UpdatedByUserId, WhoFor = @WhoFor, EntryRequirements = @EntryRequirements, WhatYoullLearn = @WhatYoullLearn, HowYoullLearn = @HowYoullLearn, HowYoullBeAssessed = @HowYoullBeAssessed, WhatYouCanDoNext = @WhatYouCanDoNext, YourReference = @YourReference, StartDate = @StartDate, Website = @Website WHERE TLevelId = @TLevelId AND TLevelStatus = @LiveTLevelStatus SELECT @Result = @@ROWCOUNT MERGE Pttcd.TLevelLocations AS target USING (SELECT Id VenueId FROM @LocationVenueIds) AS source ON target.VenueId = source.VenueId AND target.TLevelId = @TLevelId AND target.TLevelLocationStatus = @LiveTLevelLocationStatus WHEN NOT MATCHED THEN INSERT (TLevelLocationId, TLevelLocationStatus, TLevelId, VenueId) VALUES (NEWID(), @LiveTLevelLocationStatus, @TLevelId, source.VenueId) WHEN NOT MATCHED BY SOURCE AND target.TLevelId = @TLevelId THEN UPDATE SET TLevelLocationStatus = @DeletedTLevelLocationStatus; DECLARE @TLevelIds Pttcd.GuidIdTable INSERT INTO @TLevelIds VALUES (@TLevelId) EXEC Pttcd.RefreshFindACourseIndexForTLevels @TLevelIds, @UpdatedOn SELECT @Result"; try { var result = await transaction.Connection.QuerySingleAsync <Result>( sql, param : new { query.TLevelId, query.UpdatedOn, UpdatedByUserId = query.UpdatedBy.UserId, query.WhoFor, query.EntryRequirements, query.WhatYoullLearn, query.HowYoullLearn, query.HowYoullBeAssessed, query.WhatYouCanDoNext, query.YourReference, query.StartDate, query.Website, LocationVenueIds = TvpHelper.CreateGuidIdTable(query.LocationVenueIds), LiveTLevelStatus = TLevelStatus.Live, LiveTLevelLocationStatus = TLevelLocationStatus.Live, DeletedTLevelLocationStatus = TLevelLocationStatus.Deleted }, transaction : transaction); if (result == Result.Ok) { return(new Success()); } else { return(new NotFound()); } } catch (SqlException ex) when(ex.Number == 2601 && ex.Message.Contains("'IX_TLevels_DefinitionProviderStartDate'")) { return(UpdateTLevelFailedReason.TLevelAlreadyExistsForDate); } }