118 lines
5.0 KiB
Transact-SQL
118 lines
5.0 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Characters', N'CharacterImportance') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.Characters ADD CharacterImportance int NULL;
|
|
END;
|
|
GO
|
|
|
|
UPDATE dbo.Characters
|
|
SET CharacterImportance =
|
|
CASE
|
|
WHEN DefaultDescription LIKE N'%Imported character classification: Major%' THEN 10
|
|
WHEN DefaultDescription LIKE N'%Imported character classification: Supporting%' THEN 6
|
|
WHEN DefaultDescription LIKE N'%Imported character classification: Minor%' THEN 2
|
|
ELSE CharacterImportance
|
|
END
|
|
WHERE CharacterImportance IS NULL
|
|
AND DefaultDescription LIKE N'%Imported character classification:%';
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Character_ListByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
|
|
Height, EyeColour, CharacterImportance, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
|
|
FROM dbo.Characters
|
|
WHERE ProjectID = @ProjectID AND IsArchived = 0
|
|
ORDER BY CharacterName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Character_Get
|
|
@CharacterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
|
|
Height, EyeColour, CharacterImportance, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
|
|
FROM dbo.Characters
|
|
WHERE CharacterID = @CharacterID AND IsArchived = 0;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Character_Save
|
|
@CharacterID int = NULL,
|
|
@ProjectID int,
|
|
@CharacterName nvarchar(200),
|
|
@ShortName nvarchar(100) = NULL,
|
|
@Sex nvarchar(50) = NULL,
|
|
@BirthDate date = NULL,
|
|
@AgeAtSeriesStart int = NULL,
|
|
@AgeReferenceSceneID int = NULL,
|
|
@Height nvarchar(50) = NULL,
|
|
@EyeColour nvarchar(50) = NULL,
|
|
@CharacterImportance int = NULL,
|
|
@DefaultDescription nvarchar(max) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF @CharacterID IS NULL OR @CharacterID = 0
|
|
BEGIN
|
|
INSERT dbo.Characters (ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID, Height, EyeColour, CharacterImportance, DefaultDescription)
|
|
VALUES (@ProjectID, @CharacterName, @ShortName, @Sex, @BirthDate, @AgeAtSeriesStart, @AgeReferenceSceneID, @Height, @EyeColour, @CharacterImportance, @DefaultDescription);
|
|
SET @CharacterID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.Characters
|
|
SET CharacterName = @CharacterName, ShortName = @ShortName, Sex = @Sex, BirthDate = @BirthDate,
|
|
AgeAtSeriesStart = @AgeAtSeriesStart, AgeReferenceSceneID = @AgeReferenceSceneID,
|
|
Height = @Height, EyeColour = @EyeColour, CharacterImportance = @CharacterImportance, DefaultDescription = @DefaultDescription,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE CharacterID = @CharacterID;
|
|
END
|
|
SELECT @CharacterID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.CharacterTimeline_GetByProject
|
|
@ProjectID int,
|
|
@BookID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
|
|
Height, EyeColour, CharacterImportance, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
|
|
FROM dbo.Characters
|
|
WHERE ProjectID = @ProjectID AND IsArchived = 0
|
|
ORDER BY ISNULL(CharacterImportance, 0) DESC, CharacterName;
|
|
|
|
SELECT sc.SceneCharacterID, sc.SceneID, sc.CharacterID, c.ProjectID, c.CharacterName, c.ShortName, c.BirthDate, c.AgeAtSeriesStart,
|
|
sc.RoleInSceneTypeID, role.TypeName AS RoleInSceneTypeName, sc.PresenceTypeID, presence.TypeName AS PresenceTypeName,
|
|
sc.LocationID, location.LocationName, location.LocationPath, sc.EntryLocationID, entry.LocationName AS EntryLocationName,
|
|
sc.ExitLocationID, exitLocation.LocationName AS ExitLocationName, sc.AppearanceNotes, sc.OutfitDescription,
|
|
sc.PhysicalCondition, sc.EmotionalState, sc.KnowledgeNotes, s.SceneNumber, s.SceneTitle, ch.ChapterNumber, b.BookTitle,
|
|
sc.CreatedDate, sc.UpdatedDate
|
|
FROM dbo.SceneCharacters sc
|
|
INNER JOIN dbo.Characters c ON c.CharacterID = sc.CharacterID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = sc.SceneID
|
|
INNER JOIN dbo.Chapters ch ON ch.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = ch.BookID
|
|
LEFT JOIN dbo.CharacterRoleInSceneTypes role ON role.CharacterRoleInSceneTypeID = sc.RoleInSceneTypeID
|
|
LEFT JOIN dbo.PresenceTypes presence ON presence.PresenceTypeID = sc.PresenceTypeID
|
|
LEFT JOIN dbo.LocationPaths location ON location.LocationID = sc.LocationID
|
|
LEFT JOIN dbo.LocationPaths entry ON entry.LocationID = sc.EntryLocationID
|
|
LEFT JOIN dbo.LocationPaths exitLocation ON exitLocation.LocationID = sc.ExitLocationID
|
|
WHERE c.ProjectID = @ProjectID AND c.IsArchived = 0 AND s.IsArchived = 0 AND ch.IsArchived = 0 AND b.IsArchived = 0
|
|
AND (@BookID IS NULL OR b.BookID = @BookID)
|
|
ORDER BY b.SortOrder, ch.SortOrder, s.SortOrder, c.CharacterName;
|
|
END;
|
|
GO
|