25830581d2
新增成绩查询、裁判认证及检录员账号管理接口，并统一小程序角色登录与个人中心数据。 检录任务绑定赛事秩序册场次，按项目、组别、场地和场次顺序执行；支持最多三名检录员、分配检录轮次及前序检录校验。 补充检录任务、检录记录、裁判申请实体及四份数据库迁移，包含表字段备注、秩序册绑定和检录序号。
138 lines
6.4 KiB
Transact-SQL
138 lines
6.4 KiB
Transact-SQL
/*
|
||
将检录任务、检录记录绑定到赛事秩序册场次。
|
||
适用：SQL Server，可重复执行。
|
||
依赖：20260804_AddMiniProgramCompetitionRoles.sql
|
||
*/
|
||
|
||
SET NOCOUNT ON;
|
||
|
||
IF OBJECT_ID(N'dbo.CheckinTask', N'U') IS NULL
|
||
THROW 50001, N'缺少 dbo.CheckinTask，请先执行 20260804_AddMiniProgramCompetitionRoles.sql', 1;
|
||
|
||
IF OBJECT_ID(N'dbo.CheckinRecord', N'U') IS NULL
|
||
THROW 50002, N'缺少 dbo.CheckinRecord，请先执行 20260804_AddMiniProgramCompetitionRoles.sql', 1;
|
||
|
||
IF COL_LENGTH(N'dbo.CheckinTask', N'CurrentSessionKey') IS NULL
|
||
ALTER TABLE dbo.CheckinTask ADD CurrentSessionKey NVARCHAR(64) NULL;
|
||
|
||
IF COL_LENGTH(N'dbo.CheckinTask', N'CurrentSessionOrder') IS NULL
|
||
ALTER TABLE dbo.CheckinTask ADD CurrentSessionOrder INT NOT NULL
|
||
CONSTRAINT DF_CheckinTask_CurrentSessionOrder DEFAULT (0);
|
||
|
||
IF COL_LENGTH(N'dbo.CheckinTask', N'CurrentSessionName') IS NULL
|
||
ALTER TABLE dbo.CheckinTask ADD CurrentSessionName NVARCHAR(256) NULL;
|
||
|
||
IF COL_LENGTH(N'dbo.CheckinTask', N'CurrentEventProjectId') IS NULL
|
||
ALTER TABLE dbo.CheckinTask ADD CurrentEventProjectId BIGINT NULL;
|
||
|
||
IF COL_LENGTH(N'dbo.CheckinTask', N'CurrentSessionVenue') IS NULL
|
||
ALTER TABLE dbo.CheckinTask ADD CurrentSessionVenue NVARCHAR(128) NULL;
|
||
|
||
IF COL_LENGTH(N'dbo.CheckinRecord', N'SessionKey') IS NULL
|
||
ALTER TABLE dbo.CheckinRecord ADD SessionKey NVARCHAR(64) NULL;
|
||
|
||
IF COL_LENGTH(N'dbo.CheckinRecord', N'SessionOrder') IS NULL
|
||
ALTER TABLE dbo.CheckinRecord ADD SessionOrder INT NOT NULL
|
||
CONSTRAINT DF_CheckinRecord_SessionOrder DEFAULT (0);
|
||
|
||
GO
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.CheckinTask') AND name = N'IX_CheckinTask_Event_Session')
|
||
CREATE INDEX IX_CheckinTask_Event_Session
|
||
ON dbo.CheckinTask (EventId, CurrentSessionOrder, CurrentEventProjectId);
|
||
|
||
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.CheckinRecord') AND name = N'UX_CheckinRecord_Task_Round_Person_Active')
|
||
DROP INDEX UX_CheckinRecord_Task_Round_Person_Active ON dbo.CheckinRecord;
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.CheckinRecord') AND name = N'UX_CheckinRecord_Task_Session_Person_Active')
|
||
CREATE UNIQUE INDEX UX_CheckinRecord_Task_Session_Person_Active
|
||
ON dbo.CheckinRecord (TaskId, SessionKey, RegistrationPersonId)
|
||
WHERE DeleteMark = 0 AND SessionKey IS NOT NULL;
|
||
|
||
DECLARE @Comments TABLE
|
||
(
|
||
TableName SYSNAME NOT NULL,
|
||
ColumnName SYSNAME NOT NULL,
|
||
[Description] NVARCHAR(4000) NOT NULL
|
||
);
|
||
|
||
INSERT INTO @Comments (TableName, ColumnName, [Description])
|
||
VALUES
|
||
(N'CheckinTask', N'TotalRounds', N'兼容字段：当前赛事秩序册场次总数'),
|
||
(N'CheckinTask', N'CurrentRound', N'兼容字段：当前场次在整本赛事秩序册中的全局顺序'),
|
||
(N'CheckinTask', N'RoundName', N'兼容字段：当前秩序册场次显示名称'),
|
||
(N'CheckinTask', N'CurrentSessionKey', N'当前秩序册场次稳定标识，格式为 EP{EventProjectId}-S{项目内场次序号}'),
|
||
(N'CheckinTask', N'CurrentSessionOrder', N'当前场次在整本赛事秩序册中的全局顺序，从1开始'),
|
||
(N'CheckinTask', N'CurrentSessionName', N'当前秩序册场次显示名称，包含全局场次、项目和项目内场次'),
|
||
(N'CheckinTask', N'CurrentEventProjectId', N'当前场次所属赛事项目ID，关联 EventProject.Id'),
|
||
(N'CheckinTask', N'CurrentSessionVenue', N'当前秩序册场次包含的比赛场地名称或编号'),
|
||
(N'CheckinTask', N'CurrentGroupId', N'当前秩序册场次仅含单一组别时的 EventProjectGroup.Id；跨组别场次为空'),
|
||
(N'CheckinTask', N'CurrentGroupName', N'当前秩序册场次包含的组别名称，多个组别使用顿号连接'),
|
||
(N'CheckinTask', N'GroupOrder', N'兼容字段：当前场次在所属项目内的场次序号'),
|
||
(N'CheckinTask', N'TotalGroups', N'兼容字段：当前赛事项目在秩序册中的场次总数'),
|
||
(N'CheckinTask', N'NextGroupName', N'兼容字段：秩序册中的下一个场次名称'),
|
||
(N'CheckinRecord', N'Round', N'兼容字段：检录发生时的秩序册全局场次顺序'),
|
||
(N'CheckinRecord', N'GroupId', N'该人员所属赛事项目组别ID，关联 EventProjectGroup.Id'),
|
||
(N'CheckinRecord', N'SessionKey', N'检录发生时的秩序册场次稳定标识，与 CheckinTask.CurrentSessionKey 对应'),
|
||
(N'CheckinRecord', N'SessionOrder', N'检录发生时该场次在赛事秩序册中的全局顺序');
|
||
|
||
DECLARE
|
||
@TableName SYSNAME,
|
||
@ColumnName SYSNAME,
|
||
@Description NVARCHAR(4000),
|
||
@ObjectId INT,
|
||
@ColumnId INT;
|
||
|
||
DECLARE CommentCursor CURSOR LOCAL FAST_FORWARD FOR
|
||
SELECT TableName, ColumnName, [Description] FROM @Comments;
|
||
|
||
OPEN CommentCursor;
|
||
FETCH NEXT FROM CommentCursor INTO @TableName, @ColumnName, @Description;
|
||
|
||
WHILE @@FETCH_STATUS = 0
|
||
BEGIN
|
||
SET @ObjectId = OBJECT_ID(QUOTENAME(N'dbo') + N'.' + QUOTENAME(@TableName), N'U');
|
||
SET @ColumnId = COLUMNPROPERTY(@ObjectId, @ColumnName, 'ColumnId');
|
||
|
||
IF @ObjectId > 0 AND @ColumnId > 0
|
||
BEGIN
|
||
IF EXISTS
|
||
(
|
||
SELECT 1 FROM sys.extended_properties
|
||
WHERE class = 1 AND major_id = @ObjectId
|
||
AND minor_id = @ColumnId AND name = N'MS_Description'
|
||
)
|
||
BEGIN
|
||
EXEC sys.sp_updateextendedproperty
|
||
@name = N'MS_Description', @value = @Description,
|
||
@level0type = N'SCHEMA', @level0name = N'dbo',
|
||
@level1type = N'TABLE', @level1name = @TableName,
|
||
@level2type = N'COLUMN', @level2name = @ColumnName;
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
EXEC sys.sp_addextendedproperty
|
||
@name = N'MS_Description', @value = @Description,
|
||
@level0type = N'SCHEMA', @level0name = N'dbo',
|
||
@level1type = N'TABLE', @level1name = @TableName,
|
||
@level2type = N'COLUMN', @level2name = @ColumnName;
|
||
END;
|
||
END;
|
||
|
||
FETCH NEXT FROM CommentCursor INTO @TableName, @ColumnName, @Description;
|
||
END;
|
||
|
||
CLOSE CommentCursor;
|
||
DEALLOCATE CommentCursor;
|
||
|
||
SELECT
|
||
t.name AS TableName,
|
||
c.name AS ColumnName,
|
||
CAST(ep.value AS NVARCHAR(4000)) AS [Description]
|
||
FROM sys.extended_properties ep
|
||
INNER JOIN sys.tables t ON ep.class = 1 AND ep.major_id = t.object_id
|
||
INNER JOIN sys.columns c ON c.object_id = t.object_id AND c.column_id = ep.minor_id
|
||
WHERE ep.name = N'MS_Description'
|
||
AND t.name IN (N'CheckinTask', N'CheckinRecord')
|
||
ORDER BY t.name, c.column_id;
|