Custom Rankings

[deadly009]

We've discussed that before so, we're creating a custom ranking like everyone can add their own rankings to a specific table that uses another table as reference to define the ranking name.

so, here are the tables and procedure and how to execute.

People have to insert their ranking definitions on dbo._CustomRankingsDefinitions
Then execute like that.
EXEC SRO_WEB_LARAVEL.dbo._UpdateCustomRankings @CharID = 123,@RankingName = 'Test Ranking',@Point = 15

USE [SRO_WEB_LARAVEL]
GO

/****** Object:  Table [dbo].[_CustomRankingsDefinitions]    Script Date: 07/11/2020 05:02:53 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[_CustomRankingsDefinitions](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Service] [tinyint] NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK__CustomRankingsDefinitions] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX__CustomRankingsDefinitions] UNIQUE NONCLUSTERED 
(
	[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[_CustomRankingsDefinitions] ADD  CONSTRAINT [DF__CustomRankingsDefinitions_Service]  DEFAULT ((1)) FOR [Service]
GO


USE [SRO_WEB_LARAVEL]
GO

/****** Object:  Table [dbo].[_CustomRankings]    Script Date: 07/11/2020 05:02:51 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[_CustomRankings](
	[CharID] [int] NOT NULL,
	[Point] [int] NOT NULL,
	[RankingLink] [int] NOT NULL,
	[LastUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[_CustomRankings] ADD  CONSTRAINT [DF__CustomRankings_LastUpdated]  DEFAULT (getdate()) FOR [LastUpdated]
GO

ALTER TABLE [dbo].[_CustomRankings]  WITH CHECK ADD  CONSTRAINT [FK__CustomRankings__CustomRankingsDefinitions] FOREIGN KEY([RankingLink])
REFERENCES [dbo].[_CustomRankingsDefinitions] ([ID])
GO

ALTER TABLE [dbo].[_CustomRankings] CHECK CONSTRAINT [FK__CustomRankings__CustomRankingsDefinitions]
GO



USE [SRO_WEB_LARAVEL]
GO

/****** Object:  StoredProcedure [dbo].[_UpdateCustomRankings]    Script Date: 07/11/2020 05:02:58 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[_UpdateCustomRankings]
@CharID INT,
@RankingName VARCHAR(50),
@Point INT
AS
BEGIN

DECLARE @RankingID INT = (SELECT ID FROM dbo._CustomRankingsDefinitions WHERE Name = @RankingName AND Service = 1)
IF (@RankingID IS NOT NULL)
BEGIN
IF NOT EXISTS(SELECT * FROM dbo._CustomRankings WHERE CharID = @CharID AND RankingLink = @RankingID)
BEGIN
INSERT INTO dbo._CustomRankings (CharID,Point,RankingLink,LastUpdated) VALUES (@CharID,@Point,@RankingID,GETDATE())
END
ELSE
BEGIN
UPDATE dbo._CustomRankings SET Point += @Point, LastUpdated = GETDATE() WHERE CharID = @CharID
END
END
ELSE
BEGIN
PRINT 'This ranking either does not exist or disabled. Please set service to 1 on _CustomRankingsDefinitions table if you want to enable it.'
END
END
GO