25830581d2
新增成绩查询、裁判认证及检录员账号管理接口，并统一小程序角色登录与个人中心数据。 检录任务绑定赛事秩序册场次，按项目、组别、场地和场次顺序执行；支持最多三名检录员、分配检录轮次及前序检录校验。 补充检录任务、检录记录、裁判申请实体及四份数据库迁移，包含表字段备注、秩序册绑定和检录序号。
84 lines
3.5 KiB
Transact-SQL
84 lines
3.5 KiB
Transact-SQL
/* 小程序角色、裁判执裁报名、检录任务迁移。适用 SQL Server，可重复执行。 */
|
||
|
||
IF COL_LENGTH('dbo.AppUser', 'MiniProgramRole') IS NULL
|
||
BEGIN
|
||
ALTER TABLE dbo.AppUser ADD MiniProgramRole INT NOT NULL
|
||
CONSTRAINT DF_AppUser_MiniProgramRole DEFAULT (1);
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID('dbo.RefereeApplication', 'U') IS NULL
|
||
BEGIN
|
||
CREATE TABLE dbo.RefereeApplication
|
||
(
|
||
Id BIGINT NOT NULL CONSTRAINT PK_RefereeApplication PRIMARY KEY,
|
||
EventId BIGINT NOT NULL,
|
||
RefereeUserId BIGINT NOT NULL,
|
||
ApplicationStatus INT NOT NULL CONSTRAINT DF_RefereeApplication_ApplicationStatus DEFAULT (1),
|
||
Remark NVARCHAR(512) NULL,
|
||
Status INT NOT NULL CONSTRAINT DF_RefereeApplication_Status DEFAULT (1),
|
||
DeleteMark BIT NOT NULL CONSTRAINT DF_RefereeApplication_DeleteMark DEFAULT (0),
|
||
CreatedUserId BIGINT NOT NULL,
|
||
CreatedTime DATETIME2 NOT NULL,
|
||
UpdatedUserId BIGINT NULL,
|
||
UpdatedTime DATETIME2 NULL
|
||
);
|
||
CREATE INDEX IX_RefereeApplication_User ON dbo.RefereeApplication (RefereeUserId, CreatedTime DESC);
|
||
CREATE UNIQUE INDEX UX_RefereeApplication_Event_User_Active
|
||
ON dbo.RefereeApplication (EventId, RefereeUserId)
|
||
WHERE DeleteMark = 0 AND ApplicationStatus <> 4;
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID('dbo.CheckinTask', 'U') IS NULL
|
||
BEGIN
|
||
CREATE TABLE dbo.CheckinTask
|
||
(
|
||
Id BIGINT NOT NULL CONSTRAINT PK_CheckinTask PRIMARY KEY,
|
||
EventId BIGINT NOT NULL,
|
||
CheckerUserId BIGINT NULL,
|
||
TotalRounds INT NOT NULL CONSTRAINT DF_CheckinTask_TotalRounds DEFAULT (1),
|
||
CurrentRound INT NOT NULL CONSTRAINT DF_CheckinTask_CurrentRound DEFAULT (1),
|
||
RoundName NVARCHAR(64) NOT NULL CONSTRAINT DF_CheckinTask_RoundName DEFAULT (N'赛前检录'),
|
||
CurrentGroupId BIGINT NULL,
|
||
CurrentGroupName NVARCHAR(128) NOT NULL CONSTRAINT DF_CheckinTask_CurrentGroupName DEFAULT (N''),
|
||
GroupOrder INT NOT NULL CONSTRAINT DF_CheckinTask_GroupOrder DEFAULT (0),
|
||
TotalGroups INT NOT NULL CONSTRAINT DF_CheckinTask_TotalGroups DEFAULT (0),
|
||
NextGroupName NVARCHAR(128) NULL,
|
||
Venue NVARCHAR(128) NULL,
|
||
TaskStatus INT NOT NULL CONSTRAINT DF_CheckinTask_TaskStatus DEFAULT (0),
|
||
Status INT NOT NULL CONSTRAINT DF_CheckinTask_Status DEFAULT (1),
|
||
DeleteMark BIT NOT NULL CONSTRAINT DF_CheckinTask_DeleteMark DEFAULT (0),
|
||
CreatedUserId BIGINT NOT NULL,
|
||
CreatedTime DATETIME2 NOT NULL,
|
||
UpdatedUserId BIGINT NULL,
|
||
UpdatedTime DATETIME2 NULL
|
||
);
|
||
CREATE INDEX IX_CheckinTask_Checker_Status ON dbo.CheckinTask (CheckerUserId, TaskStatus, UpdatedTime DESC);
|
||
END;
|
||
GO
|
||
|
||
IF OBJECT_ID('dbo.CheckinRecord', 'U') IS NULL
|
||
BEGIN
|
||
CREATE TABLE dbo.CheckinRecord
|
||
(
|
||
Id BIGINT NOT NULL CONSTRAINT PK_CheckinRecord PRIMARY KEY,
|
||
TaskId BIGINT NOT NULL,
|
||
Round INT NOT NULL,
|
||
GroupId BIGINT NULL,
|
||
RegistrationPersonId BIGINT NOT NULL,
|
||
CheckerUserId BIGINT NOT NULL,
|
||
Checked BIT NOT NULL CONSTRAINT DF_CheckinRecord_Checked DEFAULT (0),
|
||
CheckedTime DATETIME2 NULL,
|
||
DeleteMark BIT NOT NULL CONSTRAINT DF_CheckinRecord_DeleteMark DEFAULT (0),
|
||
CreatedUserId BIGINT NOT NULL,
|
||
CreatedTime DATETIME2 NOT NULL,
|
||
UpdatedUserId BIGINT NULL,
|
||
UpdatedTime DATETIME2 NULL
|
||
);
|
||
CREATE UNIQUE INDEX UX_CheckinRecord_Task_Round_Person_Active
|
||
ON dbo.CheckinRecord (TaskId, Round, RegistrationPersonId)
|
||
WHERE DeleteMark = 0;
|
||
END;
|
||
GO
|