[SQL] vSRO - Premium Free Access System [Official Feature]

[SnapPop]

Hello Dear RaGEZOERS
today i'm gonna share it after it worked with me
well i promised some peoples to release the full databases but i see no need just for add only 2 feature that it can be add in different databases
and i'm not calling it a release since already many servers have it also it already existing on Black Rogue DBs
also big thanks for Chern0byl without him i couldn't finish what i started he gave me the idea that BR's GW can work with vSRO
okay let's get started
first of all we need add the required tables here we go

1-you need to drop and re-create dbo.TB_USER

USE [SRO_VT_ACCOUNT]
GO

/****** Object:  Table [dbo].[TB_User]    Script Date: 05/19/2012 21:52:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TB_User](
    [JID] [int] IDENTITY(1,1) NOT NULL,
    [StrUserID] [varchar](25) NOT NULL,
    [password] [varchar](50) NOT NULL,
    [truepassword] [varchar](50) NULL,
    [Status] [tinyint] NULL,
    [GMrank] [tinyint] NULL,
    [Name] [varchar](25) NULL,
    [Email] [varchar](100) NULL,
    [sex] [char](2) NULL,
    [certificate_num] [varchar](30) NULL,
    [address] [varchar](100) NULL,
    [postcode] [varchar](10) NULL,
    [phone] [varchar](20) NULL,
    [mobile] [varchar](20) NULL,
    [regtime] [datetime] NULL,
    [reg_ip] [varchar](25) NULL,
    [Time_log] [datetime] NULL,
    [freetime] [int] NULL,
    [sec_primary] [tinyint] NOT NULL,
    [sec_content] [tinyint] NOT NULL,
    [AccPlayTime] [int] NULL,
    [LatestUpdateTime_ToPlayTime] [int] NULL,
    [at] [smallint] NULL,
    [block] [smallint] NULL,
    [blockreason] [varchar](500) NULL,
    [expired] [datetime] NULL,
    [emailkey] [varchar](50) NULL,
    [hashigh] [smallint] NULL,
    [lastloginip] [int] NULL,
    [question] [varchar](50) NULL,
    [answer] [varchar](50) NULL,
 CONSTRAINT [PK_TB_User] PRIMARY KEY CLUSTERED 
(
    [JID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY],
 CONSTRAINT [IX_TB_User] UNIQUE NONCLUSTERED 
(
    [StrUserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[TB_User] ADD  CONSTRAINT [DF_TB_User_Status]  DEFAULT ((1)) FOR [Status]
GO

ALTER TABLE [dbo].[TB_User] ADD  CONSTRAINT [DF_TB_User_GMrank]  DEFAULT ((0)) FOR [GMrank]
GO

ALTER TABLE [dbo].[TB_User] ADD  CONSTRAINT [DF_TB_User_regtime]  DEFAULT (getdate()) FOR [regtime]
GO

ALTER TABLE [dbo].[TB_User] ADD  CONSTRAINT [DF_TB_User_freetime]  DEFAULT ((0)) FOR [freetime]
GO

ALTER TABLE [dbo].[TB_User] ADD  CONSTRAINT [DF_TB_User_sec_primary]  DEFAULT ((3)) FOR [sec_primary]
GO

ALTER TABLE [dbo].[TB_User] ADD  CONSTRAINT [DF_TB_User_sec_content]  DEFAULT ((3)) FOR [sec_content]
GO

ALTER TABLE [dbo].[TB_User] ADD  CONSTRAINT [DF_TB_User_at]  DEFAULT ((0)) FOR [at]
GO

ALTER TABLE [dbo].[TB_User] ADD  CONSTRAINT [DF_TB_User_block]  DEFAULT ((0)) FOR [block]
GO

ALTER TABLE [dbo].[TB_User] ADD  CONSTRAINT [DF_TB_User_expired]  DEFAULT (getdate()) FOR [expired]
GO

ALTER TABLE [dbo].[TB_User] ADD  CONSTRAINT [DF_TB_User_hashigh]  DEFAULT ((0)) FOR [hashigh]
GO

ALTER TABLE [dbo].[TB_User] ADD  CONSTRAINT [DF_TB_User_lastloginip]  DEFAULT ((0)) FOR [lastloginip]
GO 

2-create dbo._FreeAccess

USE [SRO_VT_ACCOUNT]
GO

/****** Object:  Table [dbo].[_FreeAccess]    Script Date: 05/19/2012 21:55:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[_FreeAccess](
    [JID] [int] NOT NULL,
    [EndTime] [datetime] NOT NULL,
    [ShardID] [int] NOT NULL
) ON [PRIMARY]

GO 

3-create dbo.BrutalBlockedIP

USE [SRO_VT_ACCOUNT]
GO

/****** Object:  Table [dbo].[_BrutalBlockedIP]    Script Date: 05/19/2012 21:55:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[_BrutalBlockedIP](
    [nIdx] [int] IDENTITY(1,1) NOT NULL,
    [szIPBegin] [varchar](16) NOT NULL,
    [szIPEnd] [varchar](16) NOT NULL,
    [szGM] [varchar](64) NOT NULL,
    [dIssueDate] [datetime] NOT NULL,
    [szISP] [varchar](256) NULL,
    [szDesc] [varchar](512) NULL,
 CONSTRAINT [PK__BrutalBlockedIP] PRIMARY KEY CLUSTERED 
(
    [nIdx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO 

4-drop and create dbo._PrivilegedIP

USE [SRO_VT_ACCOUNT]
GO

/****** Object:  Table [dbo].[_PrivilegedIP]    Script Date: 05/19/2012 21:57:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[_PrivilegedIP](
    [nIdx] [int] IDENTITY(1,1) NOT NULL,
    [szIPBegin] [varchar](16) NOT NULL,
    [szIPEnd] [varchar](16) NOT NULL,
    [szGM] [varchar](64) NOT NULL,
    [dIssueDate] [datetime] NOT NULL,
    [szISP] [varchar](256) NULL,
    [szDesc] [varchar](512) NULL,
 CONSTRAINT [PK__PrivilegedIP] PRIMARY KEY CLUSTERED 
(
    [nIdx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO 

Now the required stored procedures for AccountDB

5-CertifyTB_User

USE [SRO_VT_ACCOUNT]
GO

/****** Object:  StoredProcedure [dbo].[_CertifyTB_User]    Script Date: 05/19/2012 21:59:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- 4. ALTER PROCEDURE _CertifyTB_User
CREATE PROCEDURE [dbo].[_CertifyTB_User]
    @szUserID    varchar(25),
    @szPassword    varchar(50)
AS



    if exists ( 
        select 
            '' 
        from _Punishment with ( nolock )
        where SerialNo = ( 
            select 
                max( SerialNo ) from _BlockedUser with ( nolock ) 
            where UserJID = ( SELECT JID FROM TB_User WHERE StrUserID = @szUserID ) and 
                Type = 99 and getdate() between timeBegin and timeEnd ) )
    begin
        select convert( tinyint, 3), ( select JID from TB_User where StrUserID = @szUserID ), convert( tinyint, 0), convert( tinyint, 0)

        return
    end
    if exists (select 1 from TB_User where StrUserID=@szUserID and block=1 and expired>=GETDATE())
    begin
        select convert( tinyint, 3), ( select JID from TB_User where StrUserID = @szUserID ), convert( tinyint, 0), convert( tinyint, 0)
        return
    end
    declare @nUserJID int
    declare @sec_primary tinyint
    declare @sec_content tinyint

    set    @nUserJID        = 0
    set    @sec_primary        = 0
    set    @sec_content        = 0

-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- ?????? ?????????? ???????? ??????
    declare @ApplyType            tinyint
    declare @AccPlayTime             int        
    declare @LatestUpdateTime_ToPlayTime     int            

    set @ApplyType                 = 0        -- ?????? ?????????? ???????? ?????????? ???????? ???? ?????????? ???? ??????
    set @AccPlayTime             = 0        
    set @LatestUpdateTime_ToPlayTime     = 0  
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

-- ?????? ?????? ???? ????
--    select @nUserJID = JID, @sec_primary = sec_primary, @sec_content = sec_content from TB_User

    SELECT @nUserJID = JID, @sec_primary = sec_primary, @sec_content = sec_content
    , @AccPlayTime = AccPlayTime, @LatestUpdateTime_ToPlayTime = LatestUpdateTime_ToPlayTime FROM TB_User
    WHERE StrUserID = @szUserID and password = @szPassword 
    --and AT=1

-- Edit For fatigue system
-- Please, fill value to @ApplyType ( 1 : TEEN USER , 2 : ADULT USER ) from your web database
-- ex ) @ApplyType = exec web.proc.certify

    if( @nUserJID = 0 or @nUserJID is null or @@error <> 0 or @@rowcount = 0)
    begin
-- ?????? ?????? ???? ????
        select convert( tinyint, 1), convert( int, 0), convert( tinyint, 0), convert( tinyint, 0)
        --select convert( tinyint, 1), convert( int, 0), convert( tinyint, 0), convert( tinyint, 0), convert(tinyint, 0), convert(int, 0), convert(int, 0)
        return
    end
    if( exists( select Type from _BlockedUser where UserJID = @nUserJID and Type = 1 and getdate() between timeBegin and timeEnd))
    begin
-- ?????? ?????? ???? ????
        select convert( tinyint, 3), @nUserJID, convert( tinyint, 0), convert( tinyint, 0)
        --select convert( tinyint, 3), @nUserJID, convert( tinyint, 0), convert( tinyint, 0), convert(tinyint, 0), convert(int, 0), convert(int, 0)
        return
    end
-- ?????? ?????? ???? ????
select convert(tinyint, 0), @nUserJID, @sec_primary, @sec_content
    --select convert(tinyint, 0), @nUserJID, @sec_primary, @sec_content, @ApplyType, @AccPlayTime, @LatestUpdateTime_ToPlayTime
    return

GO 

6-create _CheckfreeAccess

USE [SRO_VT_ACCOUNT]
GO

/****** Object:  StoredProcedure [dbo].[_CheckFreeAccess]    Script Date: 05/19/2012 22:04:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




----------------------------------------------------------------------
-- ภฺภฏมขฑูภฬ ฐกดษวัม๖ธฆ ศฎภฮวฯฑโ ภงวั วมทฮฝรมฎ 
----------------------------------------------------------------------
CREATE PROCEDURE [dbo].[_CheckFreeAccess]
    @JID        INT,
    @ShardID    INT
AS
    -- วุด็ JID ภว วุด็ ปตๅภว BanLvl ภฬ ฑโบปภฬ พฦดั พึต้ฟก EndTime ภป ฐกมฎฟยดู. 
    DECLARE @EndTIme DATETIME 
    SET @EndTIme = 0

    -- ว๖ภ็ ผณฐ่ ป๓ BANLevel ภฬ 4 ( BanLevel รึฐํฤก ) ภฮ ฐๆฟ์ฟกธธ FA ฐก ฐกดษวฯดู. 
    SELECT TOP 1 @EndTIme = Endtime 
        FROM _FreeAccess 
        WHERE JID = @JID 
            AND ShardID = @ShardID 
        ORDER BY EndTime DESC
    if exists (select 1 from TB_User where JID=@JID and block=1)
    begin
        Select 1 
        return
    end
    IF  ( @EndTIme <> 0 )  AND (  DATEDIFF( SECOND, GETDATE(), @EndTIme  ) > 0 )
     BEGIN 
        -- ฝรฐฃย๗ฐก พ็ผ๖, ฝรฐฃ ฟฉภฏฐก ภึดยณ๐ 
        SELECT 1
    END 
    ELSE 
    BEGIN
        -- ฝรฐฃย๗ฐก ภฝผ๖, ฝรฐฃ ฟฉภฏฐก พ๘ดยณ๐ 
        SELECT 0
    END



GO 

7-create _ResetFreeAccess

USE [SRO_VT_ACCOUNT]
GO

/****** Object:  StoredProcedure [dbo].[_ResetFreeAccess]    Script Date: 05/19/2012 22:06:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




----------------------------------------------------------------------
-- ณฏยฅฐก ม๖ณญ พึต้ภบ ป่มฆวุ น๖ธฐดู.
----------------------------------------------------------------------
CREATE PROCEDURE [dbo].[_ResetFreeAccess]
AS
    DELETE FROM _FreeAccess WHERE GETDATE() > EndTime



GO 

now we finished AccountDB time for ShardDB

8-create table _FreeAccessBanlvl

USE [SRO_VT_SHARD]
GO

/****** Object:  Table [dbo].[_FreeAccessBanLvl]    Script Date: 05/19/2012 22:07:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[_FreeAccessBanLvl](
    [JID] [int] NOT NULL,
    [BanLvl] [int] NOT NULL,
    [EndTime] [datetime] NOT NULL
) ON [PRIMARY]

GO 

9-create procedure _GetAllFreeAccessBanlvl

USE [SRO_VT_SHARD]
GO

/****** Object:  StoredProcedure [dbo].[_GetAllFreeAccessBanLvl]    Script Date: 05/19/2012 22:10:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO




CREATE PROCEDURE [dbo].[_GetAllFreeAccessBanLvl]
AS
    -- ธีภ๚ ฑโฐฃภฬ ม๖ณญ ณัภบ ม๖ฟ๖ น๖ธฐดู!
    DELETE FROM _FreeAccessBanLvl 
    WHERE GETDATE() > EndTime

    IF EXISTS (SELECT JID FROM _FreeAccessBanLvl WITH (NOLOCK))
    BEGIN
        -- SELECT ALL!!
        SELECT JID, BanLvl, EndTime 
        FROM _FreeAccessBanLvl WITH (NOLOCK)     
    END
    ELSE
    BEGIN
        -- ทนฤฺตๅฐก พ๘ดย ฐๆฟ์ ด๕นฬภป บธณปมึภฺ.
        SELECT 0, 0, GETDATE()
    END



GO 

10-create this function because the next stored procedure will ask for it

USE [SRO_VT_SHARD]
GO

/****** Object:  UserDefinedFunction [dbo].[_DiffTime_CRTTIME_SQLTIME]    Script Date: 05/19/2012 22:13:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE FUNCTION [dbo].[_DiffTime_CRTTIME_SQLTIME]
    (@CRTTime INT, @SQLTime DATETIME)
RETURNS INT
AS
BEGIN
    DECLARE @nTimeTo     INT
    DECLARE @nTimeFrom    INT

    SET @nTimeTo     = 0
    SET @nTimeFrom    = @CRTTime

    SET @nTimeTo     = DATEDIFF( ss, '1970-01-01 09:00:00', @SQLTime )
/*
    DECLARE @nRemainHH    INT
    DECLARE @nRemainMM    INT
    DECLARE @nRemainSS    INT

    SET @nRemainHH     = (@nTimeTo - @nTimeFrom) / 60 / 60
    SET @nRemainMM     = (@nTimeTo - @nTimeFrom) / 60 - (@nRemainHH * 60)
    SET @nRemainSS    = (@nTimeTo - @nTimeFrom) - (@nRemainMM * 60 ) - (@nRemainHH * 60 * 60)
*/
/*
    RETURN cast( abs(@nRemainHH) as varchar(128)) + 'ฝรฐฃ'
        + cast( abs(@nRemainMM) as varchar(128)) + 'บะ'
        + cast( abs(@nRemainSS) as varchar(128)) + 'รส'
*/
    RETURN (@nTimeFrom - @nTimeTo)
END




GO 

11- create procedure _InitializeFreeAccessBanLvl

USE [SRO_VT_SHARD]
GO

/****** Object:  StoredProcedure [dbo].[_InitializeFreeAccessBanLvl]    Script Date: 05/20/2012 13:15:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE proc [dbo].[_InitializeFreeAccessBanLvl]
as
    delete _FreeAccessBanLvl where BanLvl in(3, 4)

    declare @CharID        int
    declare @Category    int
    declare @JobID        int
    declare @TimeToKeep    int
    declare @Data1        int
    declare @Data3        int
    declare @JID        int

    declare @Gold1D_ItemID      int
    declare @Gold4W_ItemID      int
    declare @Silver1D_ItemID int
    declare @Silver4W_ItemID int

    declare @EndDateTime    datetime

    select @Gold1D_ItemID = id  from _RefObjCommon with(nolock) where codename128 = 'ITEM_MALL_GOLD_TIME_SERVICE_TICKET_1D'
    if( @@error <> 0 or @@rowcount = 0 )
    begin
        raiserror( '1ภฯ ฐ๑ตๅลธภำ พฦภฬลภฬ พ๘ฝภดฯดู.', 0, 1 )
        return -1
    end

    select @Gold4W_ItemID = id  from _RefObjCommon with(nolock) where codename128 =  'ITEM_MALL_GOLD_TIME_SERVICE_TICKET_4W'
    if( @@error <> 0 or @@rowcount = 0 )
    begin
        raiserror( '4มึ ฐ๑ตๅลธภำ พฦภฬลภฬ พ๘ฝภดฯดู.', 0, 1 )
        return -1
    end

    select @Silver1D_ItemID = id  from _RefObjCommon with(nolock) where codename128 =  'ITEM_MALL_SILVER_TIME_SERVICE_TICKET_1D'
    if( @@error <> 0 or @@rowcount = 0 )
    begin
        raiserror( '1ภฯ ฝวน๖ลธภำ พฦภฬลภฬ พ๘ฝภดฯดู.', 0, 1 )
        return -1
    end

    select @Silver4W_ItemID = id  from _RefObjCommon with(nolock) where codename128 =  'ITEM_MALL_SILVER_TIME_SERVICE_TICKET_4W'
    if( @@error <> 0 or @@rowcount = 0 )
    begin
        raiserror( '4มึ ฝวน๖ลธภำ พฦภฬลภฬ พ๘ฝภดฯดู.', 0, 1 )
        return -1
    end

    set @JID = 0

    declare @Date datetime
    declare @DiffTime int
    set @Date = cast(getdate() as datetime)

    declare timedjob_cursor  cursor fast_forward for    
    
    -- ฐ๑ตๅ, ฝวน๖, วมธฎนฬพ๖ พฦภฬล
    select max(Category), max(JobID), max(TimeToKeep), max(Data1), max( isnull(Data3, 0) ), CharID
    from _TimedJob with (nolock)
    where Category = 5 or ( Category = 3 and JobID in( @Gold1D_ItemID,@Gold4W_ItemID,@Silver1D_ItemID,@Silver4W_ItemID ) )
    group by CharID

    open timedjob_cursor

    fetch next from timedjob_cursor into @Category, @JobID, @TimeToKeep, @Data1, @Data3, @CharID

    while @@fetch_status = 0
    begin    
        select @JID = UserJID from _User where CharID = @CharID
        if @@rowcount = 0 or @@error <> 0
        begin
            close timedjob_cursor
            deallocate timedjob_cursor    
            Raiserror('ฤณธฏลอฟก วุด็วฯดย ฐ่มคภฬ พ๘ณื! [%d]', 1, 16, @CharID)
            return -1
        end

        if( @Category = 3  )
        begin
            declare @ItemTotalSecond int
            
            select @ItemTotalSecond = param1 from _RefObjItem where id in ( select link from _RefObjCommon where id = @JobID )

            set @TimeToKeep = @Data1 + ( @ItemTotalSecond + ( @Data3 * 86400 ) + 86400 )
        end

        set @DiffTime = dbo._DiffTime_CRTTIME_SQLTIME( @TimeToKeep, @Date )
        set @EndDateTime = DATEADD(second, @DiffTime, getdate())
        insert into _FreeAccessBanLvl values(@JID,  4, @EndDateTime)

        fetch next from timedjob_cursor into @Category, @JobID, @TimeToKeep, @Data1, @Data3, @CharID
    end

    close timedjob_cursor
    deallocate timedjob_cursor    






GO 

12-create procedure _InsertFreeAccessBanLvl_CashItemUsed and watch out from it because it link ShardDB with AccountDB

USE [SRO_VT_SHARD]
GO

/****** Object:  StoredProcedure [dbo].[_InsertFreeAccessBanLvl_CashItemUsed]    Script Date: 05/19/2012 22:16:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE PROCEDURE [dbo].[_InsertFreeAccessBanLvl_CashItemUsed]
    @JID        INT,
    @BanLevel    INT,
    @ValidityTerm    INT,
    @ShardID    INT    
AS
    DECLARE @EndDate DATETIME
    SET @EndDate = DATEADD(second, @ValidityTerm, GETDATE())

    -- ภฯน ฑโฐฃมฆ พฦภฬลภว ป็ฟ๋ภบ วุด็ ฤณธฏลอทฮ ฑนวัตศดู.
    INSERT INTO _FreeAccessBanLvl VALUES (@JID, @BanLevel, @EndDate)

-----------------------------------------------------------------------------------------------------------------------
    --[ Start !!! ] 2009.05.12. FREE_ACCESS ฐณผฑ by ta4rom
-----------------------------------------------------------------------------------------------------------------------
    --AccountDB มขผำ มคบธฟอ ตฅภฬลอบฃภฬฝบภฬธงภบ ม๗มข วฯตๅ ฤฺต๙ตวพ๎มฎ ภึดู. กฺ บฮบะ ศฎภฮ 

    IF @BanLevel = 4 
    BEGIN    -- นทนบง 4ภฯ ฐๆฟ์ฟกธธ, AcountDB ฟก วุด็ มคบธธฆ ณฒฑไดู. 
        INSERT INTO SRO_VT_ACCOUNT.dbo._FreeAccess VALUES (@JID, @EndDate, @ShardID )
        -- กฺ _Account_For_SMC ภว Account DB ตฅภฬลอบฃภฬฝบธํภธทฮ พหธยฐิ ผ๖มค 
    END 

-----------------------------------------------------------------------------------------------------------------------
    --[ End !!! ] 2009.05.12. FREE_ACCESS ฐณผฑ by ta4rom
-----------------------------------------------------------------------------------------------------------------------
        
    -- ปตๅธลดฯภ๚ฟกฐิ พหทมมึฑโ ภงวุ ภฬ มภป วัดู. -_-
    SELECT @JID, @BanLevel, @EndDate

GO 

look at that

INSERT INTO SRO_VT_ACCOUNT.dbo._FreeAccess VALUES (@JID, @EndDate, 

so basically if you're useing the same names of DBs then just execute without edit and if it's not then change SRO_VT_ACCOUNT to w/e

13-create procedure _InsertFreeAccessBanLvl_SilkPurchased

USE [SRO_VT_SHARD]
GO

/****** Object:  StoredProcedure [dbo].[_InsertFreeAccessBanLvl_SilkPurchased]    Script Date: 05/19/2012 22:19:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO



CREATE PROCEDURE [dbo].[_InsertFreeAccessBanLvl_SilkPurchased]
    @JID        INT
AS
    DECLARE @EndDate DATETIME
    SET @EndDate = DATEADD(dd, 30, GETDATE())

    INSERT INTO _FreeAccessBanLvl VALUES (@JID, 1, @EndDate)

    -- ปตๅธลดฯภ๚ฟกฐิ พหทมมึฑโ ภงวุ ภฬ มภป วัดู. -_-
    SELECT @JID, 1, @EndDate



GO 

okay that's SQL side now server side and the easy part you will just need to add 2 parameters to server.cfg under Sr_ShardManager

        EnablePremiumFreeAccess                1
        BanUselessUserWhenFreeAccess            1 

it's not recommended to set BanUselessUserWhenFreeAccess to 0 cause it may make flood when your server get crowded
okay you must download the GW too from Chern0byl's thread and if you got problem with download server you can replace with BR's one it works too

last thing and most important for me:
don't post this thread on the shit pvpers cause they will leech and will never mention the right credit
enjoy my dears RaGEZONERS

[MentaL]

[LoCker]

lol finally you released it !!
why you took so long time to release ? as i see from the Quarie's date they are done like more than 1 week ago. is study or you was just testing ?
thank you bro i was deadly waiting xP

[SnapPop]

lol finally you released it !!
why you took so long time to release ? as i see from the Quarie's date they are done like more than 1 week ago. is study or you was just testing ?
thank you bro i was deadly waiting xP

i'm sorry for late yeah i was testing and was a bit busy because my exams will start after 3 days i hope i not fail again i want finish that bloody collage :(

[ducker997]

Thanks
But i still dont know this for what?
What you mean premium free access system?

[safty0202]

same more explain please ?

[3d2000]

nice Release ya Prince :D Testing it :D


just plzz leave every thing and finish your exams :) . GOOD LUCK

[Jangan]

oh this is beautiful! i will test this on perfection + atomix servers.

[BryanSA]

very nice, thx :)

[Kira]

Nice work bro
and good luck in ur Exams :)

[elitebi]

Thanks but what if the TB_User already have users in it? the fields are different, can't even copy

[SnapPop]

first sorry for answer late i was sleeping second thanks for all the kind posts thanks for 3d2000,Jangan,BryanSA and MR5 :)
okay @ ducker and safty this system allow the users who using premium for login from the first time even when capacity is full and this feature exist on isro ksro br basically all the official files but not in vsro
@ elitebi okay try to execute the procedure (CertifyTB_User) from Chern0byl's Thread :) it will work too i guess

[egsro]

i don't want to recreate SK_USERs :(

[SnapPop]

i don't want to recreate SK_USERs :(

okay no problem just execute (CertifyTB_User) from Chern0byl thread (vsro login no captcha) but you must know it may miss up something and its up to you

[LoCker]

Good Luck with exams bro :)

[nukertube]

Nice Release : )
Good Luck With exams bro :D

[SnapPop]

Good Luck with exams bro :)

Nice Release : )
Good Luck With exams bro :D

really thanks all of you
and i'm really happy that you guys liked it :)

[arabianfox]

Thanks really for that and good luck with your exams..

[hellspawn80]

What this exactly do ? (noob Inside)

[LoCker]

really thanks all of you
and i'm really happy that you guys liked it :)

are you kedding ! sure bro its awesome
and i'm still waiting for the database that you said it suppose to make vsro files same like any official files ;)
just take your time with exams and all the best my friend :)

[elitebi]

When I try to execute CertifyTB_User , I get this error

Msg 201, Level 16, State 4, Procedure _CertifyTB_User, Line 0
Procedure or function '_CertifyTB_User' expects parameter '@szUserID', which was not supplied.

[SnapPop]

When I try to execute CertifyTB_User , I get this error

Msg 201, Level 16, State 4, Procedure _CertifyTB_User, Line 0
Procedure or function '_CertifyTB_User' expects parameter '@szUserID', which was not supplied.

try this but as i said before it may miss up something and may system will be incompleted

USE [SRO_VT_ACCOUNT]
GO
/****** Object:  StoredProcedure [dbo].[_CertifyTB_User]    Script Date: 04/26/2012 14:41:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 4. ALTER PROCEDURE _CertifyTB_User
ALTER PROCEDURE [dbo].[_CertifyTB_User]
    @szUserID    varchar(25),
    @szPassword    varchar(50)
AS



    if exists ( 
        select 
            '' 
        from _Punishment with ( nolock )
        where SerialNo = ( 
            select 
                max( SerialNo ) from _BlockedUser with ( nolock ) 
            where UserJID = ( SELECT JID FROM TB_User WHERE StrUserID = @szUserID ) and 
                Type = 99 and getdate() between timeBegin and timeEnd ) )
    begin
        select convert( tinyint, 3), ( select JID from TB_User where StrUserID = @szUserID ), convert( tinyint, 0), convert( tinyint, 0)

        return
    end

    declare @nUserJID int
    declare @sec_primary tinyint
    declare @sec_content tinyint

    set    @nUserJID        = 0
    set    @sec_primary        = 0
    set    @sec_content        = 0

-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- ?????? ?????????? ???????? ??????
    declare @ApplyType            tinyint
    declare @AccPlayTime             int        
    declare @LatestUpdateTime_ToPlayTime     int            

    set @ApplyType                 = 0        -- ?????? ?????????? ???????? ?????????? ???????? ???? ?????????? ???? ??????
    set @AccPlayTime             = 0        
    set @LatestUpdateTime_ToPlayTime     = 0  
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

-- ?????? ?????? ???? ????
--    select @nUserJID = JID, @sec_primary = sec_primary, @sec_content = sec_content from TB_User

    SELECT @nUserJID = JID, @sec_primary = sec_primary, @sec_content = sec_content
    , @AccPlayTime = AccPlayTime, @LatestUpdateTime_ToPlayTime = LatestUpdateTime_ToPlayTime FROM TB_User
    WHERE StrUserID = @szUserID and password = @szPassword 
    --and AT=1

-- Edit For fatigue system
-- Please, fill value to @ApplyType ( 1 : TEEN USER , 2 : ADULT USER ) from your web database
-- ex ) @ApplyType = exec web.proc.certify

    if( @nUserJID = 0 or @nUserJID is null or @@error <> 0 or @@rowcount = 0)
    begin
-- ?????? ?????? ???? ????
        select convert( tinyint, 1), convert( int, 0), convert( tinyint, 0), convert( tinyint, 0)
        --select convert( tinyint, 1), convert( int, 0), convert( tinyint, 0), convert( tinyint, 0), convert(tinyint, 0), convert(int, 0), convert(int, 0)
        return
    end
    if( exists( select Type from _BlockedUser where UserJID = @nUserJID and Type = 1 and getdate() between timeBegin and timeEnd))
    begin
-- ?????? ?????? ???? ????
        select convert( tinyint, 3), @nUserJID, convert( tinyint, 0), convert( tinyint, 0)
        --select convert( tinyint, 3), @nUserJID, convert( tinyint, 0), convert( tinyint, 0), convert(tinyint, 0), convert(int, 0), convert(int, 0)
        return
    end
-- ?????? ?????? ???? ????
select convert(tinyint, 0), @nUserJID, @sec_primary, @sec_content
    --select convert(tinyint, 0), @nUserJID, @sec_primary, @sec_content, @ApplyType, @AccPlayTime, @LatestUpdateTime_ToPlayTime
    return 

[SnapPop]

guys what do you think about that ?
http://www.******.com/forum/private-...ss-system.html

[LoCker]

guys what do you think about that ?
http://www.******.com/forum/private-...ss-system.html

opps
but i guess its normal thing that other forum steal your work since silkroad section isn't private for RaGEZONERS only
but anyway we know that it's your own work so just ignore these noobs lechers

[SnapPop]

okay never mind Locker and next time i will be more carefully to prevent these shit from leak my work
but these leechers say that this system can't work with vsro shard is that right ?
@Jangan you tested or not yet ?
come on someone test and post here by making capacity (1) and login with normal account and then login with premium account
i can't test that on my machine basically i can't run server files + 2 clients on dead machine with 1GB ram but i can run the first client and it logins normally someone test with 2 client with capacity (1)

[kibritcheto]

EnablePremiumFreeAccess
BanUselessUserWhenFreeAccess

not exist on vsro sr_shardmanager.exe