Add VIP - Gold - Silver - Bronze - 30 days

[fratika]

Add VIP - Gold - Silver - Bronze 30 days:

You require the following to view this post content:

• To see this content, please click the "-SAY THANKS-" button located on the bottom-right of this post.

[dmtuan23]

This code update all account, how to code for new account?

[fratika]

This code update all account, how to code for new account?

this code update only one account not all accounts,by end of the code you have username there you add the username,  and for new account only from website you have to do it. I don't have that code for website.

[Phantasm]

Personal made:

Setting Up the VIP Update Stored Procedure with Enhanced Functionality. This version will include automated expiration handling and detailed logging:

Code Select Expand

USE MuOnline;
GO

-- Create a VIP history table to track all VIP changes
IF OBJECT_ID('dbo.VIP_History', 'U') IS NULL
BEGIN
    CREATE TABLE VIP_History (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Username NVARCHAR(50),
        VIPLevel INT,
        DateSet DATETIME DEFAULT GETDATE(),
        ExpirationDate DATETIME
    );
END
GO

-- Create or alter the stored procedure to update VIP status
CREATE OR ALTER PROCEDURE SetVIPStatus
    @Username NVARCHAR(50), -- Username of the account
    @VIPLevel INT,          -- VIP Level (1 = Bronze, 2 = Silver, 3 = Gold)
    @Days INT              -- Duration in days
AS
BEGIN
    -- Validate VIP Level
    IF @VIPLevel NOT IN (1, 2, 3)
    BEGIN
        PRINT 'Invalid VIP Level. Please use 1 for Bronze, 2 for Silver, 3 for Gold.';
        RETURN;
    END
   
    -- Check if the user exists
    IF NOT EXISTS (SELECT * FROM MEMB_INFO WHERE memb___id = @Username)
    BEGIN
        PRINT 'Username does not exist.';
        RETURN;
    END

    -- Update Account Level and Expiration Date
    DECLARE @ExpirationDate DATETIME = DATEADD(DAY, @Days, GETDATE());
   
    UPDATE MEMB_INFO
    SET AccountLevel = @VIPLevel,
        AccountExpireDate = @ExpirationDate
    WHERE memb___id = @Username;

    -- Log the VIP status update in the VIP_History table
    INSERT INTO VIP_History (Username, VIPLevel, ExpirationDate)
    VALUES (@Username, @VIPLevel, @ExpirationDate);

    -- Feedback for successful operation
    PRINT 'VIP status updated successfully for user: ' + @Username;
END;
GO

Automatic Deactivation of Expired VIP Accounts. To handle expired VIP accounts, you can create a SQL Agent Job that runs daily (or at any preferred interval) to reset expired accounts:

Code Select Expand

USE MuOnline;
GO

CREATE PROCEDURE DeactivateExpiredVIPAccounts
AS
BEGIN
    -- Reset accounts where VIP has expired
    UPDATE MEMB_INFO
    SET AccountLevel = 0  -- Assuming 0 is the standard non-VIP level
    WHERE AccountExpireDate < GETDATE() AND AccountLevel > 0;

    PRINT 'Expired VIP accounts have been reset.';
END;
GO
Then, set this DeactivateExpiredVIPAccounts stored procedure to run as a scheduled job, ensuring no VIP account remains active past its expiration date.

Executing the Enhanced Procedure and Viewing Logs:

Code Select Expand

-- Example: Set user 'USERNAME' to Gold VIP (Level 3) for 30 days
EXEC SetVIPStatus @Username = 'USERNAME', @VIPLevel = 3, @Days = 30;

-- Example: Check VIP history for auditing purposes
SELECT * FROM VIP_History WHERE Username = 'USERNAME';

Email Notifications for VIP Changes and Expirations

Code Select Expand

-- Modify the SetVIPStatus procedure to include email notifications
ALTER PROCEDURE SetVIPStatus
    @Username NVARCHAR(50),
    @VIPLevel INT,       
    @Days INT             
AS
BEGIN
    -- Existing code here (validation, updating account, logging, etc.)
   
    -- Send notification email
    DECLARE @Email NVARCHAR(100);
    SET @Email = (SELECT email FROM MEMB_INFO WHERE memb___id = @Username);

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'YourEmailProfile',  -- Set up a database mail profile first
        @recipients = @Email,
        @subject = 'Your VIP Status Has Been Updated!',
        @body = 'Dear ' + @Username + ', your VIP status has been updated to Level '
                + CAST(@VIPLevel AS NVARCHAR) + ' and is valid for ' + CAST(@Days AS NVARCHAR) + ' days.';

    PRINT 'Notification email sent successfully.';
END;

VIP Status Query Tool for Admins

Code Select Expand

CREATE PROCEDURE GetVIPStatus
    @Username NVARCHAR(50)
AS
BEGIN
    SELECT
        MEMB_INFO.memb___id AS Username,
        MEMB_INFO.AccountLevel,
        MEMB_INFO.AccountExpireDate,
        VIP_History.VIPLevel,
        VIP_History.DateSet,
        VIP_History.ExpirationDate
    FROM MEMB_INFO
    LEFT JOIN VIP_History ON MEMB_INFO.memb___id = VIP_History.Username
    WHERE MEMB_INFO.memb___id = @Username;

    PRINT 'VIP status information retrieved.';
END;

Flexible VIP Levels and Benefits Table

Code Select Expand

-- Create a VIP_Tiers table to store flexible levels
CREATE TABLE VIP_Tiers (
    VIPLevel INT PRIMARY KEY,
    DurationDays INT,
    Cost DECIMAL(10, 2),
    Benefits NVARCHAR(255) -- Description of benefits
);

-- Example data insertion for VIP levels
INSERT INTO VIP_Tiers (VIPLevel, DurationDays, Cost, Benefits)
VALUES (1, 30, 10.00, 'Access to bronze features'),
      (2, 30, 20.00, 'Access to silver features and faster support'),
      (3, 30, 30.00, 'Access to all features, priority support');

Renewal Discounts or Loyalty Rewards

Code Select Expand

-- Example: Adding a discount flag to the VIP_History table
ALTER TABLE VIP_History ADD RenewalDiscount BIT DEFAULT 0;

-- Procedure logic for applying discounts on renewal
IF EXISTS (SELECT 1 FROM VIP_History WHERE Username = @Username AND ExpirationDate > GETDATE() - 30)
BEGIN
    SET @Discount = 1;  -- 1 means eligible for discount
    PRINT 'User eligible for renewal discount!';
END;

Grace Period for Expired VIPs

Code Select Expand

ALTER PROCEDURE DeactivateExpiredVIPAccounts
AS
BEGIN
    -- Set VIP level to reduced status during grace period
    UPDATE MEMB_INFO
    SET AccountLevel = -1,  -- Example of a grace period level
        GraceExpireDate = DATEADD(DAY, 7, AccountExpireDate)  -- 7-day grace period
    WHERE AccountExpireDate < GETDATE() AND AccountLevel > 0;

    -- Fully deactivate accounts after grace period
    UPDATE MEMB_INFO
    SET AccountLevel = 0
    WHERE GraceExpireDate < GETDATE() AND AccountLevel = -1;

    PRINT 'Expired VIP accounts updated with grace period or deactivated fully.';
END;
Summary of Enhanced Features

Flexible VIP Settings: Easily set any level and duration dynamically.
Expiration Automation: Expired accounts are automatically reset to a non-VIP level.
Detailed VIP History: Logs each VIP change, capturing the username, VIP level, and expiration date.
Error Handling and Notifications: Improved feedback and validations for ease of use.
Automated Notifications: Keeps users informed about their VIP status and encourages renewals.
Grace Periods: Provides a smooth transition after expiration, maintaining user satisfaction.
Renewal Rewards: Encourages loyalty with discounts for timely renewals.
Flexible VIP Management: A VIP tiers table enables rapid adjustments to levels and benefits.
Admin Query Tool: Streamlines VIP status management with quick access to detailed user information.