コード例 #1
0
        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 <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());
        }
コード例 #3
0
        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());
        }
コード例 #4
0
        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)},
コード例 #5
0
        public async Task AddAttendees(BulkAttendeeRequest request)
        {
            ParameterDictionary parameters = ParameterDictionary.Single("invites", TvpHelper.MapInvites(request.InviteRequests));

            parameters.AddParameter("userId", request.UserId);

            await ExecuteStoredProc("[dbo].[spCreateInvites]", parameters)
            .ConfigureAwait(continueOnCapturedContext: false);
        }
コード例 #6
0
        public static IBindingParameterContract GetParameterContract(RelationIdentity relation)
        {
            if (relation == null)
            {
                throw new ArgumentNullException(nameof(relation));
            }

            return(Resolvers.GetOrAdd(relation, _ =>
            {
                IBindingMetadata[] bindings = relation.Heading.Select(m => m.GetMetadata <IBindingMetadata>()).ToArray();

                return TvpHelper.GetParameterContract(bindings);
            }));
        }
コード例 #7
0
        public async Task <IDictionary <string, PostcodeInfo> > Execute(SqlTransaction transaction, GetPostcodeInfos query)
        {
            var sql = @"
SELECT pc.Postcode, pc.Position.Lat Latitude, pc.Position.Long Longitude, pc.InEngland
FROM Pttcd.Postcodes pc
JOIN @Postcodes x ON pc.Postcode = x.Value";

            var paramz = new
            {
                Postcodes = TvpHelper.CreateStringTable(query.Postcodes)
            };

            return((await transaction.Connection.QueryAsync <PostcodeInfo>(sql, paramz, transaction: transaction))
                   .ToDictionary(p => p.Postcode, p => p));
        }
コード例 #8
0
        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 <string, LearningDelivery> > Execute(SqlTransaction transaction, GetLearningDeliveries query)
        {
            var sql = @"
SELECT ld.LearnAimRef, ld.LearnAimRefTitle, ld.EffectiveTo, ld.NotionalNVQLevelv2, ld.AwardOrgCode, 
lart.LearnAimRefTypeDesc, ld.OperationalEndDate
FROM LARS.LearningDelivery ld
JOIN @LearningAimRefs x ON ld.LearnAimRef = x.Value
JOIN LARS.LearnAimRefType lart ON ld.LearnAimRefType = lart.LearnAimRefType";

            var paramz = new
            {
                LearningAimRefs = TvpHelper.CreateUnicodeStringTable(query.LearnAimRefs)
            };

            return((await transaction.Connection.QueryAsync <LearningDelivery>(sql, paramz, transaction)).ToDictionary(r => r.LearnAimRef, r => r));
        }
コード例 #10
0
        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));
        }
コード例 #11
0
ファイル: TvpExtensions.cs プロジェクト: mephistoc/jerrycurl
        /// <summary>
        /// Appends a table-valued parameter from the current values, e.g. <c>@TP0</c>, to the projection buffer.
        /// </summary>
        /// <param name="projection">The current projection.</param>
        /// <returns>A new attribute containing the appended buffer.</returns>
        public static IProjectionAttribute TvpName(this IProjection projection)
        {
            if (projection.Source == null)
            {
                throw ProjectionException.ValueNotFound(projection);
            }
            else if (!projection.Attributes.Any())
            {
                throw ProjectionException.FromProjection(projection, "No attributes found.");
            }

            IProjectionMetadata metadata = TvpHelper.GetPreferredTvpMetadata(projection);
            IField source = new Relation(projection.Source, metadata.Identity.Name).Scalar();

            RelationIdentity identity = new RelationIdentity(metadata.Identity.Schema, projection.Attributes.Select(a => a.Metadata.Identity));

            IBindingParameterContract contract = TvpCache.GetParameterContract(identity);

            string paramName   = projection.Context.Lookup.Custom("TP", projection.Identity, field: source);
            string dialectName = projection.Context.Domain.Dialect.Parameter(paramName);

            return(projection.Attr().Append(dialectName).Append(new Parameter(paramName, source, contract)));
        }
        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)})";
コード例 #13
0
        public async Task <OneOf <NotFound, Success> > Execute(SqlTransaction transaction, CreateCourseRun query)
        {
            var sql = $@"
IF NOT EXISTS (SELECT 1 FROM Pttcd.Courses WHERE CourseId = @CourseId)
BEGIN
    SELECT 1 AS Result
    RETURN
END

INSERT INTO Pttcd.CourseRuns (
    CourseRunId,
    CourseId,
    CourseRunStatus,
    CreatedOn,
    CreatedBy,
    UpdatedOn,
    UpdatedBy,
    CourseName,
    VenueId,
    ProviderCourseId,
    DeliveryMode,
    FlexibleStartDate,
    StartDate,
    CourseWebsite,
    Cost,
    CostDescription,
    DurationUnit,
    DurationValue,
    StudyMode,
    AttendancePattern,
    [National]
)
VALUES (
    @CourseRunId,
    @CourseId,
    {(int)CourseStatus.Live},
    @CreatedOn,
    @CreatedByUserId,
    @CreatedOn,
    @CreatedByUserId,
    @CourseName,
    @VenueId,
    @ProviderCourseId,
    @DeliveryMode,
    @FlexibleStartDate,
    @StartDate,
    @CourseWebsite,
    @Cost,
    @CostDescription,
    @DurationUnit,
    @DurationValue,
    @StudyMode,
    @AttendancePattern,
    @National
)

INSERT INTO Pttcd.CourseRunSubRegions (CourseRunId, RegionId)
SELECT @CourseRunId, Value FROM @SubRegionIds

SELECT 0 AS Result

DECLARE @CourseRunIds Pttcd.GuidIdTable

INSERT INTO @CourseRunIds VALUES (@CourseRunId)

EXEC Pttcd.RefreshFindACourseIndex @CourseRunIds = @CourseRunIds, @Now = @CreatedOn";

            var paramz = new
            {
                query.CourseId,
                query.CourseRunId,
                query.CourseName,
                query.DeliveryMode,
                query.FlexibleStartDate,
                query.StartDate,
                CourseWebsite = query.CourseUrl,
                query.Cost,
                query.CostDescription,
                query.DurationUnit,
                query.DurationValue,
                query.ProviderCourseId,
                query.National,
                SubRegionIds = TvpHelper.CreateStringTable(query.SubRegionIds ?? Enumerable.Empty <string>()),
                query.VenueId,
                query.AttendancePattern,
                query.StudyMode,
                CreatedByUserId = query.CreatedBy.UserId,
                query.CreatedOn,
            };

            var result = await transaction.Connection.QuerySingleAsync <Result>(sql, paramz, transaction);

            if (result == Result.CourseNotFound)
            {
                return(new NotFound());
            }
            else
            {
                return(new Success());
            }
        }
コード例 #14
0
        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());
        }
コード例 #15
0
        public async Task <OneOf <NotFound, Success> > Execute(SqlTransaction transaction, UpdateCourseRun query)
        {
            var sql = $@"
UPDATE Pttcd.CourseRuns SET
    CourseName = @CourseName,
    DeliveryMode = @DeliveryMode,
    FlexibleStartDate = @FlexibleStartDate,
    StartDate = @StartDate,
    CourseWebsite = @CourseUrl,
    Cost = @Cost,
    CostDescription = @CostDescription,
    DurationUnit = @DurationUnit,
    DurationValue = @DurationValue,
    ProviderCourseId = @ProviderCourseId,
    [National] = @National,
    VenueId = @VenueId,
    AttendancePattern = @AttendancePattern,
    StudyMode = @StudyMode,
    UpdatedBy = @UpdatedByUserId,
    UpdatedOn = @UpdatedOn,
    DataIsHtmlEncoded = 0
WHERE CourseRunId = @CourseRunId
AND CourseRunStatus = {(int)CourseStatus.Live}

IF @@ROWCOUNT = 0
BEGIN
    SELECT 1 AS Result
    RETURN
END

MERGE Pttcd.CourseRunSubRegions AS target
USING (SELECT Value FROM @SubRegionIds) AS source
ON target.CourseRunId = @CourseRunId AND target.RegionId = source.Value
WHEN NOT MATCHED THEN INSERT (CourseRunId, RegionId) VALUES (@CourseRunId, source.Value)
WHEN NOT MATCHED BY SOURCE AND target.CourseRunId = @CourseRunId THEN DELETE;

DECLARE @CourseRunIds Pttcd.GuidIdTable

INSERT INTO @CourseRunIds VALUES (@CourseRunId)

EXEC Pttcd.RefreshFindACourseIndex @CourseRunIds = @CourseRunIds, @Now = @UpdatedOn

SELECT 0 AS Result";

            var paramz = new
            {
                query.CourseRunId,
                query.CourseName,
                query.DeliveryMode,
                query.FlexibleStartDate,
                query.StartDate,
                query.CourseUrl,
                query.Cost,
                query.CostDescription,
                query.DurationUnit,
                query.DurationValue,
                query.ProviderCourseId,
                query.National,
                SubRegionIds = TvpHelper.CreateStringTable(query.SubRegionIds ?? Array.Empty <string>()),
                query.VenueId,
                query.AttendancePattern,
                query.StudyMode,
                UpdatedByUserId = query.UpdatedBy.UserId,
                query.UpdatedOn
            };

            var result = await transaction.Connection.QuerySingleAsync <Result>(sql, paramz, transaction);

            if (result == Result.Success)
            {
                return(new Success());
            }
            else
            {
                return(new NotFound());
            }
        }
コード例 #16
0
        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);
            }
        }