145 lines
6.7 KiB
Transact-SQL
145 lines
6.7 KiB
Transact-SQL
-- ============================================================
|
||
-- 创建 assessment_record_conclusions 表
|
||
-- 存储每条测评记录的独立结论数据副本
|
||
-- 由报告生成时从 report_conclusions 模板表复制而来
|
||
-- Database: MiAssessment_Business (SQL Server 2022)
|
||
-- ============================================================
|
||
|
||
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'assessment_record_conclusions')
|
||
BEGIN
|
||
CREATE TABLE assessment_record_conclusions (
|
||
Id bigint IDENTITY(1,1) NOT NULL,
|
||
RecordId bigint NOT NULL,
|
||
CategoryId bigint NOT NULL,
|
||
ConclusionType int NOT NULL,
|
||
StarLevel int NOT NULL,
|
||
Title nvarchar(100) NULL,
|
||
Content nvarchar(max) NOT NULL,
|
||
CreateTime datetime NOT NULL CONSTRAINT DF_arc_CreateTime DEFAULT (GETDATE()),
|
||
UpdateTime datetime NOT NULL CONSTRAINT DF_arc_UpdateTime DEFAULT (GETDATE()),
|
||
IsDeleted bit NOT NULL CONSTRAINT DF_arc_IsDeleted DEFAULT (0),
|
||
|
||
CONSTRAINT PK_assessment_record_conclusions PRIMARY KEY CLUSTERED (Id),
|
||
CONSTRAINT FK_arc_assessment_records FOREIGN KEY (RecordId) REFERENCES assessment_records (Id),
|
||
CONSTRAINT FK_arc_report_categories FOREIGN KEY (CategoryId) REFERENCES report_categories (Id)
|
||
);
|
||
|
||
-- 按测评记录查询
|
||
CREATE NONCLUSTERED INDEX ix_arc_record_id
|
||
ON assessment_record_conclusions (RecordId);
|
||
|
||
-- 按记录+分类查询
|
||
CREATE NONCLUSTERED INDEX ix_arc_record_category
|
||
ON assessment_record_conclusions (RecordId, CategoryId);
|
||
|
||
PRINT N'表 assessment_record_conclusions 创建成功';
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
PRINT N'表 assessment_record_conclusions 已存在，跳过创建';
|
||
END
|
||
GO
|
||
|
||
-- ============================================================
|
||
-- 创建 report_page_configs 表
|
||
-- 定义 PDF 报告中每一页的类型、顺序和关联资源
|
||
-- Database: MiAssessment_Business (SQL Server 2022)
|
||
-- ============================================================
|
||
|
||
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'report_page_configs')
|
||
BEGIN
|
||
CREATE TABLE report_page_configs (
|
||
Id bigint IDENTITY(1,1) NOT NULL,
|
||
PageType int NOT NULL,
|
||
PageName nvarchar(50) NOT NULL,
|
||
Title nvarchar(100) NOT NULL,
|
||
SortOrder int NOT NULL,
|
||
ImageUrl nvarchar(500) NULL,
|
||
RouteUrl nvarchar(200) NULL,
|
||
Status int NOT NULL CONSTRAINT DF_rpc_Status DEFAULT (1),
|
||
CreateTime datetime NOT NULL CONSTRAINT DF_rpc_CreateTime DEFAULT (GETDATE()),
|
||
UpdateTime datetime NOT NULL CONSTRAINT DF_rpc_UpdateTime DEFAULT (GETDATE()),
|
||
|
||
CONSTRAINT PK_report_page_configs PRIMARY KEY CLUSTERED (Id)
|
||
);
|
||
|
||
-- 按排序查询
|
||
CREATE NONCLUSTERED INDEX ix_rpc_sort_order
|
||
ON report_page_configs (SortOrder);
|
||
|
||
-- 按状态筛选
|
||
CREATE NONCLUSTERED INDEX ix_rpc_status
|
||
ON report_page_configs (Status);
|
||
|
||
PRINT N'表 report_page_configs 创建成功';
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
PRINT N'表 report_page_configs 已存在，跳过创建';
|
||
END
|
||
GO
|
||
|
||
-- ============================================================
|
||
-- 插入 report_page_configs 初始数据
|
||
-- 包含静态图片页（PageType=1）和网页截图页（PageType=2）
|
||
-- 按 SortOrder 排序
|
||
-- ============================================================
|
||
|
||
-- 仅在表为空时插入初始数据
|
||
IF NOT EXISTS (SELECT 1 FROM report_page_configs)
|
||
BEGIN
|
||
INSERT INTO report_page_configs (PageType, PageName, Title, SortOrder, ImageUrl, RouteUrl, Status)
|
||
VALUES
|
||
-- 静态图片页：报告封面图
|
||
(1, N'cover-image', N'报告封面图', 1, N'/images/report/cover.png', NULL, 1),
|
||
|
||
-- 网页截图页：封面页（测评人信息）
|
||
(2, N'cover', N'封面页', 2, NULL, N'/report/cover'),
|
||
|
||
-- 静态图片页：目录/导读页
|
||
(1, N'intro-page', N'目录导读页', 3, N'/images/report/intro.png', NULL, 1),
|
||
|
||
-- 网页截图页：八大智能分析
|
||
(2, N'intelligence-overview', N'八大智能分析', 4, NULL, N'/report/intelligence-overview'),
|
||
|
||
-- 网页截图页：最强智能详情
|
||
(2, N'strongest-intelligence', N'最强智能详情', 5, NULL, N'/report/strongest-intelligence'),
|
||
|
||
-- 网页截图页：较弱智能详情
|
||
(2, N'weakest-intelligence', N'较弱智能详情', 6, NULL, N'/report/weakest-intelligence'),
|
||
|
||
-- 网页截图页：个人特质分析
|
||
(2, N'personality-traits', N'个人特质分析', 7, NULL, N'/report/personality-traits'),
|
||
|
||
-- 网页截图页：40项细分能力分析
|
||
(2, N'sub-abilities', N'40项细分能力分析', 8, NULL, N'/report/sub-abilities'),
|
||
|
||
-- 静态图片页：板块分隔页
|
||
(1, N'separator-learning', N'学习分析分隔页', 9, N'/images/report/separator-learning.png', NULL, 1),
|
||
|
||
-- 网页截图页：先天学习类型分析
|
||
(2, N'learning-types', N'先天学习类型分析', 10, NULL, N'/report/learning-types'),
|
||
|
||
-- 网页截图页：学习关键能力分析
|
||
(2, N'learning-abilities', N'学习关键能力分析', 11, NULL, N'/report/learning-abilities'),
|
||
|
||
-- 网页截图页：科学大脑类型分析
|
||
(2, N'brain-types', N'科学大脑类型分析', 12, NULL, N'/report/brain-types'),
|
||
|
||
-- 网页截图页：性格类型分析
|
||
(2, N'character-types', N'性格类型分析', 13, NULL, N'/report/character-types'),
|
||
|
||
-- 网页截图页：未来关键发展能力分析
|
||
(2, N'future-abilities', N'未来关键发展能力分析', 14, NULL, N'/report/future-abilities'),
|
||
|
||
-- 静态图片页：报告尾页
|
||
(1, N'ending-page', N'报告尾页', 15, N'/images/report/ending.png', NULL, 1);
|
||
|
||
PRINT N'report_page_configs 初始数据插入成功（共 15 条）';
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
PRINT N'report_page_configs 已有数据，跳过初始数据插入';
|
||
END
|
||
GO
|