PlotDirector/PlotLine/Sql/079_VisualIdentityCharacterAssetImages.sql

IF COL_LENGTH(N'dbo.Characters', N'ImagePath') IS NULL
    ALTER TABLE dbo.Characters ADD ImagePath nvarchar(500) NULL;
GO

IF COL_LENGTH(N'dbo.Characters', N'ThumbnailPath') IS NULL
    ALTER TABLE dbo.Characters ADD ThumbnailPath nvarchar(500) NULL;
GO

IF COL_LENGTH(N'dbo.StoryAssets', N'ImagePath') IS NULL
    ALTER TABLE dbo.StoryAssets ADD ImagePath nvarchar(500) NULL;
GO

IF COL_LENGTH(N'dbo.StoryAssets', N'ThumbnailPath') IS NULL
    ALTER TABLE dbo.StoryAssets ADD ThumbnailPath nvarchar(500) NULL;
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, ShowInQuickAddBar, DefaultDescription, ImagePath, ThumbnailPath,
        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, ShowInQuickAddBar, DefaultDescription, ImagePath, ThumbnailPath,
        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,
    @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, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID, Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, ImagePath, ThumbnailPath)
        VALUES (@ProjectID, @CharacterName, @ShortName, @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, 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.Character_UpdateImage
    @CharacterID int,
    @ImagePath nvarchar(500) = NULL,
    @ThumbnailPath nvarchar(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.Characters
    SET ImagePath = @ImagePath,
        ThumbnailPath = @ThumbnailPath,
        UpdatedDate = SYSUTCDATETIME()
    WHERE CharacterID = @CharacterID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StoryAsset_ListByProject
    @ProjectID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT sa.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName, sa.Description, sa.Importance,
        sa.CurrentStateID, ast.StateName AS CurrentStateName, sa.CurrentLocationID,
        location.LocationName AS CurrentLocationName, location.LocationPath AS CurrentLocationPath,
        sa.IsResolved, sa.ShowInQuickAddBar, sa.ImagePath, sa.ThumbnailPath, sa.CreatedDate, sa.UpdatedDate, sa.IsArchived
    FROM dbo.StoryAssets sa
    INNER JOIN dbo.AssetKinds ak ON ak.AssetKindID = sa.AssetKindID
    LEFT JOIN dbo.AssetStates ast ON ast.AssetStateID = sa.CurrentStateID
    LEFT JOIN dbo.LocationPaths location ON location.LocationID = sa.CurrentLocationID
    WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0
    ORDER BY sa.Importance DESC, sa.AssetName;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StoryAsset_Get
    @StoryAssetID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT sa.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName, sa.Description, sa.Importance,
        sa.CurrentStateID, ast.StateName AS CurrentStateName, sa.CurrentLocationID,
        location.LocationName AS CurrentLocationName, location.LocationPath AS CurrentLocationPath,
        sa.IsResolved, sa.ShowInQuickAddBar, sa.ImagePath, sa.ThumbnailPath, sa.CreatedDate, sa.UpdatedDate, sa.IsArchived
    FROM dbo.StoryAssets sa
    INNER JOIN dbo.AssetKinds ak ON ak.AssetKindID = sa.AssetKindID
    LEFT JOIN dbo.AssetStates ast ON ast.AssetStateID = sa.CurrentStateID
    LEFT JOIN dbo.LocationPaths location ON location.LocationID = sa.CurrentLocationID
    WHERE sa.StoryAssetID = @StoryAssetID AND sa.IsArchived = 0;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StoryAsset_Save
    @StoryAssetID int = NULL,
    @ProjectID int,
    @AssetName nvarchar(200),
    @AssetKindID int,
    @Description nvarchar(max) = NULL,
    @Importance int,
    @CurrentStateID int = NULL,
    @CurrentLocationID int = NULL,
    @IsResolved bit,
    @ShowInQuickAddBar bit = 0,
    @ImagePath nvarchar(500) = NULL,
    @ThumbnailPath nvarchar(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    IF @StoryAssetID IS NULL OR @StoryAssetID = 0
    BEGIN
        INSERT dbo.StoryAssets (ProjectID, AssetName, AssetKindID, Description, Importance, CurrentStateID, CurrentLocationID, IsResolved, ShowInQuickAddBar, ImagePath, ThumbnailPath)
        VALUES (@ProjectID, @AssetName, @AssetKindID, @Description, @Importance, @CurrentStateID, @CurrentLocationID, @IsResolved, @ShowInQuickAddBar, @ImagePath, @ThumbnailPath);
        SET @StoryAssetID = CAST(SCOPE_IDENTITY() AS int);
    END
    ELSE
    BEGIN
        UPDATE dbo.StoryAssets
        SET AssetName = @AssetName, AssetKindID = @AssetKindID, Description = @Description, Importance = @Importance,
            CurrentStateID = @CurrentStateID, CurrentLocationID = @CurrentLocationID, IsResolved = @IsResolved,
            ShowInQuickAddBar = @ShowInQuickAddBar, ImagePath = @ImagePath, ThumbnailPath = @ThumbnailPath,
            UpdatedDate = SYSUTCDATETIME()
        WHERE StoryAssetID = @StoryAssetID;
    END
    SELECT @StoryAssetID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StoryAsset_UpdateImage
    @StoryAssetID int,
    @ImagePath nvarchar(500) = NULL,
    @ThumbnailPath nvarchar(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.StoryAssets
    SET ImagePath = @ImagePath,
        ThumbnailPath = @ThumbnailPath,
        UpdatedDate = SYSUTCDATETIME()
    WHERE StoryAssetID = @StoryAssetID;
END;
GO