251 lines
10 KiB
Transact-SQL
251 lines
10 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.CharacterSexValues', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.CharacterSexValues
|
|
(
|
|
CharacterSexValueID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_CharacterSexValues PRIMARY KEY,
|
|
OwnerUserID int NULL,
|
|
SexName nvarchar(100) NOT NULL,
|
|
IsSystem bit NOT NULL CONSTRAINT DF_CharacterSexValues_IsSystem DEFAULT (0),
|
|
SortOrder int NOT NULL CONSTRAINT DF_CharacterSexValues_SortOrder DEFAULT (0),
|
|
IsActive bit NOT NULL CONSTRAINT DF_CharacterSexValues_IsActive DEFAULT (1),
|
|
CreatedDateUTC datetime2 NOT NULL CONSTRAINT DF_CharacterSexValues_CreatedDateUTC DEFAULT (SYSUTCDATETIME()),
|
|
CONSTRAINT FK_CharacterSexValues_AppUser FOREIGN KEY (OwnerUserID) REFERENCES dbo.AppUser(UserID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Characters', N'SexValueID') IS NULL
|
|
ALTER TABLE dbo.Characters ADD SexValueID int NULL;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_Characters_CharacterSexValues')
|
|
ALTER TABLE dbo.Characters ADD CONSTRAINT FK_Characters_CharacterSexValues FOREIGN KEY (SexValueID) REFERENCES dbo.CharacterSexValues(CharacterSexValueID);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_CharacterSexValues_SystemName' AND object_id = OBJECT_ID(N'dbo.CharacterSexValues'))
|
|
CREATE UNIQUE INDEX UX_CharacterSexValues_SystemName ON dbo.CharacterSexValues(SexName) WHERE IsSystem = 1 AND IsActive = 1;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_CharacterSexValues_OwnerName' AND object_id = OBJECT_ID(N'dbo.CharacterSexValues'))
|
|
CREATE UNIQUE INDEX UX_CharacterSexValues_OwnerName ON dbo.CharacterSexValues(OwnerUserID, SexName) WHERE OwnerUserID IS NOT NULL AND IsActive = 1;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM dbo.CharacterSexValues WHERE IsSystem = 1 AND SexName = N'Male')
|
|
INSERT dbo.CharacterSexValues (OwnerUserID, SexName, IsSystem, SortOrder) VALUES (NULL, N'Male', 1, 10);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM dbo.CharacterSexValues WHERE IsSystem = 1 AND SexName = N'Female')
|
|
INSERT dbo.CharacterSexValues (OwnerUserID, SexName, IsSystem, SortOrder) VALUES (NULL, N'Female', 1, 20);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM dbo.CharacterSexValues WHERE IsSystem = 1 AND SexName = N'Other')
|
|
INSERT dbo.CharacterSexValues (OwnerUserID, SexName, IsSystem, SortOrder) VALUES (NULL, N'Other', 1, 30);
|
|
GO
|
|
|
|
;WITH CharacterOwners AS
|
|
(
|
|
SELECT c.CharacterID, ownerAccess.UserID AS OwnerUserID, LTRIM(RTRIM(c.Sex)) AS SexName
|
|
FROM dbo.Characters c
|
|
INNER JOIN dbo.ProjectUserAccess ownerAccess ON ownerAccess.ProjectID = c.ProjectID
|
|
AND ownerAccess.AccessRole = N'Owner'
|
|
AND ownerAccess.IsActive = 1
|
|
WHERE NULLIF(LTRIM(RTRIM(c.Sex)), N'') IS NOT NULL
|
|
),
|
|
CustomSexValues AS
|
|
(
|
|
SELECT DISTINCT co.OwnerUserID, co.SexName
|
|
FROM CharacterOwners co
|
|
WHERE NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.CharacterSexValues csv
|
|
WHERE csv.IsSystem = 1
|
|
AND csv.IsActive = 1
|
|
AND LOWER(csv.SexName) = LOWER(co.SexName)
|
|
)
|
|
AND NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.CharacterSexValues csv
|
|
WHERE csv.OwnerUserID = co.OwnerUserID
|
|
AND csv.IsActive = 1
|
|
AND LOWER(csv.SexName) = LOWER(co.SexName)
|
|
)
|
|
)
|
|
INSERT dbo.CharacterSexValues (OwnerUserID, SexName, IsSystem, SortOrder)
|
|
SELECT OwnerUserID, SexName, 0, 100
|
|
FROM CustomSexValues;
|
|
GO
|
|
|
|
;WITH CharacterOwners AS
|
|
(
|
|
SELECT c.CharacterID, ownerAccess.UserID AS OwnerUserID, LTRIM(RTRIM(c.Sex)) AS SexName
|
|
FROM dbo.Characters c
|
|
INNER JOIN dbo.ProjectUserAccess ownerAccess ON ownerAccess.ProjectID = c.ProjectID
|
|
AND ownerAccess.AccessRole = N'Owner'
|
|
AND ownerAccess.IsActive = 1
|
|
WHERE NULLIF(LTRIM(RTRIM(c.Sex)), N'') IS NOT NULL
|
|
)
|
|
UPDATE c
|
|
SET SexValueID = csv.CharacterSexValueID
|
|
FROM dbo.Characters c
|
|
INNER JOIN CharacterOwners co ON co.CharacterID = c.CharacterID
|
|
INNER JOIN dbo.CharacterSexValues csv ON csv.IsActive = 1
|
|
AND LOWER(csv.SexName) = LOWER(co.SexName)
|
|
AND (csv.IsSystem = 1 OR csv.OwnerUserID = co.OwnerUserID)
|
|
WHERE c.SexValueID IS NULL;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.CharacterSexValue_GetOrCreate
|
|
@OwnerUserID int,
|
|
@SexName nvarchar(100)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SET @SexName = NULLIF(LTRIM(RTRIM(@SexName)), N'');
|
|
IF @SexName IS NULL
|
|
THROW 53001, 'Sex name is required.', 1;
|
|
|
|
DECLARE @CharacterSexValueID int;
|
|
|
|
SELECT TOP (1) @CharacterSexValueID = CharacterSexValueID
|
|
FROM dbo.CharacterSexValues
|
|
WHERE IsActive = 1
|
|
AND LOWER(SexName) = LOWER(@SexName)
|
|
AND (IsSystem = 1 OR OwnerUserID = @OwnerUserID)
|
|
ORDER BY IsSystem DESC, SortOrder, SexName;
|
|
|
|
IF @CharacterSexValueID IS NULL
|
|
BEGIN
|
|
INSERT dbo.CharacterSexValues (OwnerUserID, SexName, IsSystem, SortOrder)
|
|
VALUES (@OwnerUserID, @SexName, 0, 100);
|
|
|
|
SET @CharacterSexValueID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
|
|
SELECT @CharacterSexValueID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Character_ListByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT CharacterID, ProjectID, CharacterName, ShortName, SexValueID, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
|
|
Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, ImagePath, ThumbnailPath,
|
|
AvatarSourceCharacterImageID, AvatarImagePath, AvatarThumbnailPath,
|
|
CreatedDate, UpdatedDate, IsArchived, ArchivedDate, ArchivedReason
|
|
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, SexValueID, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
|
|
Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, ImagePath, ThumbnailPath,
|
|
AvatarSourceCharacterImageID, AvatarImagePath, AvatarThumbnailPath,
|
|
CreatedDate, UpdatedDate, IsArchived, ArchivedDate, ArchivedReason
|
|
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,
|
|
@SexValueID int = NULL,
|
|
@Sex nvarchar(100) = NULL,
|
|
@BirthDate date = NULL,
|
|
@AgeAtSeriesStart int = NULL,
|
|
@AgeReferenceSceneID int = NULL,
|
|
@Height nvarchar(50) = NULL,
|
|
@EyeColour nvarchar(50) = NULL,
|
|
@CharacterImportance int = NULL,
|
|
@ShowInQuickAddBar bit = 0,
|
|
@DefaultDescription nvarchar(max) = NULL,
|
|
@ImagePath nvarchar(500) = NULL,
|
|
@ThumbnailPath nvarchar(500) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF @CharacterID IS NULL OR @CharacterID = 0
|
|
BEGIN
|
|
INSERT dbo.Characters (ProjectID, CharacterName, ShortName, SexValueID, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID, Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, ImagePath, ThumbnailPath)
|
|
VALUES (@ProjectID, @CharacterName, @ShortName, @SexValueID, @Sex, @BirthDate, @AgeAtSeriesStart, @AgeReferenceSceneID, @Height, @EyeColour, @CharacterImportance, @ShowInQuickAddBar, @DefaultDescription, @ImagePath, @ThumbnailPath);
|
|
SET @CharacterID = CAST(SCOPE_IDENTITY() AS int);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.Characters
|
|
SET CharacterName = @CharacterName, ShortName = @ShortName, SexValueID = @SexValueID, Sex = @Sex, BirthDate = @BirthDate,
|
|
AgeAtSeriesStart = @AgeAtSeriesStart, AgeReferenceSceneID = @AgeReferenceSceneID,
|
|
Height = @Height, EyeColour = @EyeColour, CharacterImportance = @CharacterImportance,
|
|
ShowInQuickAddBar = @ShowInQuickAddBar, DefaultDescription = @DefaultDescription,
|
|
ImagePath = @ImagePath, ThumbnailPath = @ThumbnailPath,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE CharacterID = @CharacterID;
|
|
END
|
|
SELECT @CharacterID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneFloorPlanOccupancy_ListByScene
|
|
@SceneID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT o.SceneFloorPlanOccupancyID, o.SceneID, o.FloorPlanID, o.FloorPlanFloorID,
|
|
o.CharacterID, c.CharacterName, c.ShortName AS CharacterShortName,
|
|
COALESCE(c.AvatarImagePath, c.ImagePath) AS CharacterImagePath,
|
|
COALESCE(c.AvatarThumbnailPath, c.ThumbnailPath) AS CharacterThumbnailPath,
|
|
o.StoryAssetID, sa.AssetName, sa.ImagePath AS AssetImagePath, sa.ThumbnailPath AS AssetThumbnailPath,
|
|
o.LocationID, COALESCE(lp.LocationName, l.LocationName) AS LocationName, COALESCE(lp.LocationPath, l.LocationName) AS LocationPath,
|
|
fpf.Name AS FloorName, o.CreatedDate, o.ModifiedDate
|
|
FROM dbo.SceneFloorPlanOccupancy o
|
|
INNER JOIN dbo.Locations l ON l.LocationID = o.LocationID
|
|
LEFT JOIN dbo.LocationPaths lp ON lp.LocationID = o.LocationID
|
|
LEFT JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = o.FloorPlanFloorID
|
|
LEFT JOIN dbo.Characters c ON c.CharacterID = o.CharacterID
|
|
LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = o.StoryAssetID
|
|
WHERE o.SceneID = @SceneID
|
|
ORDER BY COALESCE(c.CharacterName, sa.AssetName);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SceneFloorPlanOccupancy_ListByFloorPlan
|
|
@FloorPlanID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SELECT o.SceneFloorPlanOccupancyID, o.SceneID, o.FloorPlanID, o.FloorPlanFloorID,
|
|
o.CharacterID, c.CharacterName, c.ShortName AS CharacterShortName,
|
|
COALESCE(c.AvatarImagePath, c.ImagePath) AS CharacterImagePath,
|
|
COALESCE(c.AvatarThumbnailPath, c.ThumbnailPath) AS CharacterThumbnailPath,
|
|
o.StoryAssetID, sa.AssetName, sa.ImagePath AS AssetImagePath, sa.ThumbnailPath AS AssetThumbnailPath,
|
|
o.LocationID, COALESCE(lp.LocationName, l.LocationName) AS LocationName, COALESCE(lp.LocationPath, l.LocationName) AS LocationPath,
|
|
fpf.Name AS FloorName, o.CreatedDate, o.ModifiedDate
|
|
FROM dbo.SceneFloorPlanOccupancy o
|
|
INNER JOIN dbo.Locations l ON l.LocationID = o.LocationID
|
|
LEFT JOIN dbo.LocationPaths lp ON lp.LocationID = o.LocationID
|
|
LEFT JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = o.FloorPlanFloorID
|
|
LEFT JOIN dbo.Characters c ON c.CharacterID = o.CharacterID
|
|
LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = o.StoryAssetID
|
|
WHERE o.FloorPlanID = @FloorPlanID
|
|
ORDER BY o.SceneID, COALESCE(c.CharacterName, sa.AssetName);
|
|
END;
|
|
GO
|