[Development] Silkroad Jobs reward !

[ahmed4ever2u]

Everybody in sro scene realize the actuality of killing job system by Joymax after doing their so called "improvements" nevertheless they fucked it up hence I decided to try to rebirth it

This program was actually made for someone but he doesn't need it anymore so I decided to release it maybe I can help in this community so it could be better
After the story everybody will be like : so what is this program going to do ?
Short answer : Rebirthing job system it'll give rewards for the active jobbers more jobbing = more profit ! so the players would have the inducement to do more jobs !

Let's take fast look on the script recent and new versions
The program current version is : Version 2.6 !

- its capable to add new items as a reward ! (it may have some bugs)

Firstly to be able to use my system you need to create this table

 Spoiler

USE [SRO_VT_SHARD]
GO
print 'Created by Ahmed4ever2u'
/****** Object:  Table [dbo].[Reward_Job_Info]    Script Date: 10/19/2012 22:48:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Reward_Job_Info](
    [CharID] [int] NOT NULL,
    [CharName] [varchar](16) NOT NULL,
    [Message_Stat] [varchar](50) NOT NULL,
    [Reward_Stat] [varchar](16) NOT NULL,
    [Number] [int] IDENTITY(1,1) NOT NULL,
    [Job_Type] [varchar](16) NOT NULL,
    [Reward_Item] [varchar](50) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Reward_Job_Info] ADD  CONSTRAINT [DF_Reward_Message_Message_Stat]  DEFAULT ((0)) FOR [Message_Stat]
GO

add new column
_CharTrijob

 Spoiler

DoneORnot    int    [SIZE=5][COLOR=Red][B]NULL[/B][/COLOR][/SIZE]

you need to edit your _AddNewChar stored procedures

 Spoiler

search for

INSERT INTO _CharTrijob VALUES (@NewCharID, 0, 1, 0, 0, 0)

and change it for

INSERT INTO _CharTrijob VALUES (@NewCharID, 0, 1, 0, 0, 0,0)

you need to add new stored procedures

 Spoiler

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[ADD_ITEM_TO_STORAGE]   Script Date: 10/19/2012 20:20:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE   procedure [dbo].[ADD_ITEM_TO_STORAGE]

@JID            int,
@ItemToAdd        int,
@data            int,
@opt_level        int

as

    if (not exists(select * from sysobjects where name = '_TEMP_ADDITEMEXTERN_CHEST_LOG'))
    begin
        create table _TEMP_ADDITEMEXTERN_CHEST_LOG
        (
            ID int identity(0, 1) not null,
            LogString varchar(256),
            LogDate datetime default getdate() not null
        )
    end

    declare @LogString varchar(256)
    declare @link_to_item int

    if (not exists (select UserJID from _User with (nolock) where UserJID = @JID))
    begin
        set @LogString = 'not existing account ID: ' + cast(@JID as char(15))
        insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
        raiserror('%s', 1, 16, @LogString)
        return -2
    end
    declare @IsAdd_Able int

    set @IsAdd_able = 0

    select @IsAdd_Able = ( case when count(CHEST.slot) >= CHESTINFO.ChestSize then 0 else 1 end )
    from _chest as CHEST with (nolock) INNER JOIN _chestinfo as CHESTINFO with (nolock)  ON CHESTINFO.JID = CHEST.UserJID 
    where CHEST.UserJID = @JID and (CHEST.itemid != 0 and CHEST.itemid is  not null)
    group by CHESTINFO.ChestSize

     if ( @IsAdd_Able = 0 )
     begin
         set @LogString = 'chest Full! JID is ' + cast(@JID as char(15)) + ', item: ' + cast(@ItemToAdd as char(15))
         insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
         raiserror('%s', 1, 16, @LogString)
         return -3
     end
--//////////////////////////////////////////////////////////////////////////////////////////////////////////////

    declare @empty_slot int
    select top 1 @empty_slot = slot from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null) order by slot
    
    set @link_to_item = 0
    
    select @link_to_item = link from _RefObjCommon where ID = @ItemToAdd

    if (@link_to_item = 0 or @link_to_item is null)
    begin
        set @LogString = 'RefItem Link == NULL JID is ' + cast(@JID as char(15))
        insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
        raiserror('%s', 1, 16, @LogString)
        return -5
    end

    declare @tid1 int
    declare @tid2 int
    declare @tid3 int
    declare @tid4 int

    select @tid1 = TypeID1, @tid2 = TypeID2, @tid3 = TypeID3, @tid4 = TypeID4 from _RefObjCommon where ID = @ItemToAdd

    declare @IS_EQUIP int
    declare @IS_PET int

    set @IS_EQUIP = 0
    set @IS_PET = 0

    if (@tid1 <> 3)
    begin
        set @LogString = 'not item! JID is '  + cast(@JID as char(15)) + 'item is: ' + cast(@ItemToAdd as char(15))
        insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
        raiserror('%s', 1, 16, @LogString)
        return -6        -- ¿ÀÀ×! ¾ÆÀÌÅÛÀÌ ¾Æ´Ï¾ß?
    end

    if (@tid1 = 3 and @tid2 = 1)
        set @IS_EQUIP = 1
    else if (@tid1 = 3 and @tid2 = 2 and @tid3 = 1 and (@tid4 = 1 or @tid4 = 2))
        set @IS_PET = 1

    if (@IS_EQUIP = 1)
    begin
        select @data = Dur_L from _RefObjItem where ID = @link_to_item
        
        if (@opt_level < 0)
            set @opt_level = 0
        else if (@opt_level > 12)
            set @opt_level = 12
    end
    else
    begin

        if( @IS_PET = 1 )
            set @data = 0
        else
        begin
            declare @max_count int
            select @max_count = MaxStack from _RefObjItem where ID = @link_to_item
    
            if (@data <= 0 or @data > @max_count)  
                set @data = @max_count  
        end

        set @opt_level = 0
    end
    print 'By Ahmed4ever2u'
    -------------------------------------------------------------------------------- let's generate item !!!
    set xact_abort on

begin transaction
    
    declare @dummy_serial_number bigint
    set @dummy_serial_number = 0

    declare @NewItemID bigint
    set @NewItemID = 0

    EXEC @NewItemID = _STRG_ALLOC_ITEM_NoTX @dummy_serial_number OUTPUT
    
    IF (@NewItemID = 0)
    BEGIN
        rollback transaction
        set @LogString = 'failed to allocate new item id! JID is '  + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
        
        insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
        raiserror('%s', 1, 16, @LogString)
        return -7
    END

  if( @IS_PET = 1 )
      UPDATE _Items SET RefItemID = @ItemToAdd, OptLevel = @Opt_Level, Data = @Data, Variance = 0 WHERE ID64 = @NewItemID
  else
      UPDATE _Items SET RefItemID = @ItemToAdd, OptLevel = @Opt_Level, Data = @Data WHERE ID64 = @NewItemID
  
    IF (@@ERROR <> 0)
    BEGIN
        rollback transaction
        set @LogString = 'failed to create new item! JID is '  + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))

        insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
        raiserror('%s', 1, 16, @LogString)
        return -8
    END

    UPDATE _chest SET ItemID = @NewItemID WHERE UserJID = @JID AND Slot = @empty_slot
    IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
    BEGIN
        rollback transaction
        set @LogString = 'failed to insert item to chest! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))

        insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
        raiserror('%s', 1, 16, @LogString)
        return -9
    END

    --------------------------------------------------------------------------------
    if (@NewItemID <= 0)
    begin
        rollback transaction
        set @LogString = 'item generation failed for unknown reason! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))

        insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
        raiserror('%s', 1, 16, @LogString)
        return -10
    end

commit transaction

    set @LogString = 'success ' + 'jid: ' + cast(@JID as char(15)) + ', item: ' + cast(@ItemToAdd as char(15)) + cast(@data as varchar(10)) + ', slot: ' + cast(@empty_slot as varchar(10))
    insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
    print 'By Ahmed4ever2u'
    return 1

the Query (will be into C# after development end)

 Spoiler

use SRO_VT_SHARD
DECLARE @JobType Varchar(10),@CharID INT,@CharName VARCHAR(16),@AccountID INT,@GoldReward INT,@ID64 INT,
@RefObjID INT,@ServerName Varchar(16),@RewardWithItem varchar (10),@Reward_Item varchar (25)
/*______________________________________________________________________________________________________*/

SET @JobType = 'Thief'    -- thief & trader & hunter
SET @ServerName = 'Eagle'    --- for exable Eagle Team 
SET @GoldReward = 1000
SET @RewardWithItem = 'Active' -- Active & in-active
SET @Reward_Item = 'ITEM_ETC_AHMED'

/*______________________________________________________________________________________________________*/
SET @JobType = /*Thanks for Caipi for @This Part*/
(
    CASE
        When @JobType = 'trader'     then 1
        When @JobType = 'thief'         then 2
        When @JobType = 'hunter'     then 3
        ELSE NULL
    END
);
IF (@JobType is null)
    begin
        PRINT('Wrong JobType, please use on of the following "Hunter" & "Trader" & "Thief"')
        RETURN
    end
    if not exists (select * from _CharTrijob where Level = 2 and JobType = @JobType)
        Begin
            Print 'Hey There is no one in level 2 Yet :)'
            Return
        end
if @RewardWithItem = 'Active'
    Begin
        if not exists (select CodeName128 from _RefObjCommon where CodeName128 like @Reward_Item)
            begin
                print 'Some thing is wrong with your reward item ' +@Reward_Item+ ' please Check it'
                return
            end
    End
            SET @CharID = (select MIN(CharID) from _CharTrijob where Level = 2 and JobType = @JobType and DoneORnot = 0)
            SET @CharName = (select CharName16 from _Char where CharID = @CharID)
            SET @AccountID = (select UserJID from _User where CharID = @CharID)
            SET @RefObjID = (select RefObjID from _Char where CharID = @CharID)
    if exists (select MIN(CharID) from _CharTrijob where Level = 2 and JobType = @JobType and DoneORnot = 0)
        begin
        IF not exists (select CharID from Reward_Job_Info where CharID = @CharID)
            begin
                INSERT INTO Reward_Job_Info (CharID,CharName,Message_Stat,Reward_Stat,Job_Type) VALUES (@CharID,@CharName,0,0,@JobType)
                update _CharTrijob Set DoneORnot = 1 where CharID = @CharID
                update Reward_Job_Info SET Reward_Stat = 1 where CharID = @CharID 
                update _AccountJID set Gold = Gold + @GoldReward where JID = @AccountID
                update Reward_Job_Info Set Message_Stat = 1 where CharID = @CharID 
                INSERT INTO _Memo (CharID,FromCharName,Message,Date,Status,RefObjID) Values (@CharID,@ServerName+' Team','Hello you have Been Rewarded with '/*Thanks for Caipi for this part :)*/+CONVERT(varchar(255), @GoldReward)+' gold For getting new Level by doing ' +@JobType+ ' Job',GETDATE(),1,@RefObjID)
            end
        End
    if exists (select DoneOrNot from _CharTrijob where DoneORnot = 1 and Level = 1)
        begin
            SET @CharID = (select MIN(CharID) from _CharTrijob where DoneORnot = 1 and Level = 1)
            update _CharTrijob set DoneORnot = 0 where CharID = @CharID
            update Reward_Job_Info SET Reward_Stat = 0, Message_Stat = 0 where CharID = @CharID
            update _AccountJID set Gold = Gold - @GoldReward where JID = @AccountID
            INSERT INTO _Memo (CharID,FromCharName,Message,Date,Status,RefObjID) Values (@CharID,@ServerName+' Team','Hello your Reward has been taken because you have downgrade the level about  '/*Thanks for Caipi for this part :)*/+CONVERT(varchar(255), @GoldReward)+' gold discounted from your storage',GETDATE(),1,@RefObjID)
        end
if @RewardWithItem = 'Active'
    begin
        DECLARE @ItemID INT,@ItemDur INT
            set @ItemID =(select ID from _RefObjCommon where CodeName128 like @Reward_Item)
            set @ItemDur = (select Dur_L from _RefObjItem where ID = (select Link from _RefObjCommon where CodeName128 like @Reward_Item))
                exec ADD_ITEM_TO_STORAGE @JID = @AccountID ,@ItemToAdd = @ItemID ,@data = @ItemDur,@opt_level = 0
    end

Version 2.4

 Spoiler

- bug fix version (more stable now)
- added the base for reward with item
- starting on reward with silks

Firstly to be able to use my system you need to create this table

 Spoiler

USE [SRO_VT_SHARD]
GO

/****** Object:  Table [dbo].[Reward_Job_Info]    Script Date: 10/16/2012 15:52:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Reward_Job_Info](
    [CharID] [int] NOT NULL,
    [CharName] [varchar](16) NOT NULL,
    [Message_Stat] [varchar](50) NOT NULL,
    [Reward_Stat] [varchar](16) NOT NULL,
    [Number] [int] IDENTITY(1,1) NOT NULL,
    [Job_Type] [varchar](16) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Reward_Job_Info] ADD  CONSTRAINT [DF_Reward_Message_Message_Stat]  DEFAULT ((0)) FOR [Message_Stat]
GO

add new column
_CharTrijob

 Spoiler

DoneORnot    int    [SIZE=5][COLOR=Red][B]NULL[/B][/COLOR][/SIZE]

you need to edit your _AddNewChar stored procedures

 Spoiler

search for

INSERT INTO _CharTrijob VALUES (@NewCharID, 0, 1, 0, 0, 0)

and change it for

INSERT INTO _CharTrijob VALUES (@NewCharID, 0, 1, 0, 0, 0,0)

the Query (will be into C# after development end)

 Spoiler

use SRO_VT_SHARD
DECLARE @JobType Varchar(10),@CharID INT,@CharName VARCHAR(16),@AccountID INT,@GoldReward INT,@ID64 INT,
@RefObjID INT,@ServerName Varchar(16),@RewardWithItem varchar (10)
/*______________________________________________________________________________________________________*/

SET @GoldReward = 1000
SET @RewardWithItem = 'Active' -- Active & in-active
SET @JobType = 'Thief'    -- thief & trader & hunter
SET @ServerName = 'Eagle'    --- for exable Eagle Team 

/*______________________________________________________________________________________________________*/
SET @JobType = /*Thanks for Caipi for @This Part*/
(
    CASE
        When @JobType = 'trader'     then 1
        When @JobType = 'thief'         then 2
        When @JobType = 'hunter'     then 3
        ELSE NULL
    END
);
IF (@JobType is null)
    begin
        PRINT('Wrong JobType, please use on of the following "Hunter" & "Trader" & "Thief"')
        RETURN
    end
    if not exists (select * from _CharTrijob where Level = 2 and JobType = @JobType)
        Begin
            Print 'Hey There is no one in level 2 Yet :)'
            Return
        end
            SET @CharID = (select MIN(CharID) from _CharTrijob where Level = 2 and JobType = @JobType and DoneORnot = 0)
            SET @CharName = (select CharName16 from _Char where CharID = @CharID)
            SET @AccountID = (select UserJID from _User where CharID = @CharID)
            SET @RefObjID = (select RefObjID from _Char where CharID = @CharID)
    if exists (select MIN(CharID) from _CharTrijob where Level = 2 and JobType = @JobType and DoneORnot = 0)
        begin
        IF not exists (select CharID from Reward_Job_Info where CharID = @CharID)
            begin
                INSERT INTO Reward_Job_Info (CharID,CharName,Message_Stat,Reward_Stat,Job_Type) VALUES (@CharID,@CharName,0,0,@JobType)
                update _CharTrijob Set DoneORnot = 1 where CharID = @CharID
                update Reward_Job_Info SET Reward_Stat = 1 where CharID = @CharID 
                update _AccountJID set Gold = Gold + @GoldReward where JID = @AccountID
                update Reward_Job_Info Set Message_Stat = 1 where CharID = @CharID 
                INSERT INTO _Memo (CharID,FromCharName,Message,Date,Status,RefObjID) Values (@CharID,@ServerName+' Team','Hello you have Been Rewarded with '/*Thanks for Caipi for this part :)*/+CONVERT(varchar(255), @GoldReward)+' gold For getting new Level by doing ' +@JobType+ ' Job',GETDATE(),1,@RefObjID)
            end
        End
    if exists (select DoneOrNot from _CharTrijob where DoneORnot = 1 and Level = 1)
        begin
            SET @CharID = (select MIN(CharID) from _CharTrijob where DoneORnot = 1 and Level = 1)
            update _CharTrijob set DoneORnot = 0 where CharID = @CharID
            update Reward_Job_Info SET Reward_Stat = 0, Message_Stat = 0 where CharID = @CharID
            update _AccountJID set Gold = Gold - @GoldReward where JID = @AccountID
            INSERT INTO _Memo (CharID,FromCharName,Message,Date,Status,RefObjID) Values (@CharID,@ServerName+' Team','Hello your Reward has been taken because you have downgrade the level about  '/*Thanks for Caipi for this part :)*/+CONVERT(varchar(255), @GoldReward)+' gold discounted from your storage',GETDATE(),1,@RefObjID)
        end
if @RewardWithItem = 'Active'
    begin
        if exists (select MIN(CharID) from Reward_Job_Info where Reward_Stat = 1 and Message_Stat = 1)
            begin
                SET @CharID = (select MIN(CharID) from Reward_Job_Info where Reward_Stat = 1 and Message_Stat = 1)
                    if exists (select EXP from _CharTrijob where CharID = @CharID and Exp > 443686732)
                        Begin
                            print 'not finished yet please wait the next version'
                        End
            end
    end

Version 2.3

 Spoiler

- if the player level up to the 2nd level while jobbing it will send a message tell him how much gold he have got .
- if the player downgrade to the 1st level while jobbing it will send a message tell him how much gold he have lost.
-its working fine now with level 1,2
-Query optimizations(Thanks for Caipi)

Firstly to be able to use my system you need to create this table

 Spoiler

USE [SRO_VT_SHARD]
GO

/****** Object:  Table [dbo].[Reward_Job_Info]    Script Date: 10/16/2012 15:52:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Reward_Job_Info](
    [CharID] [int] NOT NULL,
    [CharName] [varchar](16) NOT NULL,
    [Message_Stat] [varchar](50) NOT NULL,
    [Reward_Stat] [varchar](16) NOT NULL,
    [Number] [int] IDENTITY(1,1) NOT NULL,
    [Job_Type] [varchar](16) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Reward_Job_Info] ADD  CONSTRAINT [DF_Reward_Message_Message_Stat]  DEFAULT ((0)) FOR [Message_Stat]
GO

_CharTrijob

 Spoiler

DoneORnot    varchar(16)    (default value = 0) NOT NULL

the Query (will be into C# after development end)

 Spoiler

use SRO_VT_SHARD
DECLARE @JobType Varchar(10),@CharID INT,@CharName VARCHAR(16),@AccountID INT,@Job_Type VARCHAR(16),@GoldReward INT,@ID64 INT,@RefObjID INT
,@ServerName Varchar(16)
--################################################################################################################
SET @GoldReward = 1000
SET @JobType = 'Thief'    -- thief & trader & hunter
SET @ServerName = 'Eagle'    --- for exable Eagle Team 
--################################################################################################################
SET @JobType = /*Thanks for Caipi for @This Part*/
(
    CASE
        When @JobType = 'trader'     then 1
        When @JobType = 'thief'         then 2
        When @JobType = 'hunter'     then 3
        ELSE NULL
    END
);
IF (@JobType is null)
    begin
        PRINT('Wrong JobType, please use on of the following "Hunter" & "Trader" & "Thief"')
        RETURN
    end
    if exists (select * from _CharTrijob where Level = 2)
        Begin
            Print 'Hey There is no one in level 2 Yet :)'
            Return
        end
            SET @CharID = (select MIN(CharID) from _CharTrijob where Level = 2 and JobType = @JobType and DoneORnot = 0)
            SET @CharName = (select CharName16 from _Char where CharID = @CharID)
            SET @Job_Type = (select JobType from _CharTrijob where CharID = @CharID)
            SET @AccountID = (select UserJID from _User where CharID = @CharID)
            SET @RefObjID = (select RefObjID from _Char where CharID = @CharID)
    if exists (select MIN(CharID) from _CharTrijob where Level = 2 and JobType = @JobType and DoneORnot = 0)
        begin
        IF not exists (select CharID from Reward_Job_Info where CharID = @CharID)
            begin
                INSERT INTO Reward_Job_Info (CharID,CharName,Message_Stat,Reward_Stat,Job_Type) VALUES (@CharID,@CharName,0,0,@JobType)
                update _CharTrijob Set DoneORnot = 1 where CharID = @CharID
                update Reward_Job_Info SET Reward_Stat = 1 where CharID = @CharID 
                update _AccountJID set Gold = Gold + @GoldReward where JID = @AccountID
                update Reward_Job_Info Set Message_Stat = 1 where CharID = @CharID 
                INSERT INTO _Memo (CharID,FromCharName,Message,Date,Status,RefObjID) Values (@CharID,@ServerName+' Team','Hello you have Been Rewarded with '/*Thanks for Caipi for this part :)*/+CONVERT(varchar(255), @GoldReward)+' gold For getting new Level by doing ' +@JobType+ ' Job',GETDATE(),1,@RefObjID)
            end
        IF exists (select CharID from Reward_Job_Info where CharID = @CharID)
            Begin
                if exists (select DoneOrNot from _CharTrijob where DoneORnot = 1 and Level = 1)
                    begin
                        SET @CharID = (select CharID from _CharTrijob where DoneORnot = 1 and Level = 1)
                        update _CharTrijob set DoneORnot = 0 where CharID = @CharID
                        update Reward_Job_Info SET Reward_Stat = 0, Message_Stat = 0 where CharID = @CharID
                        update _AccountJID set Gold = Gold - @GoldReward where JID = @AccountID
                        INSERT INTO _Memo (CharID,FromCharName,Message,Date,Status,RefObjID) Values (@CharID,@ServerName+' Team','Hello your Reward has been taken because you have downgrade the level about  '/*Thanks for Caipi for this part :)*/+CONVERT(varchar(255), @GoldReward)+' gold discounted from your storage',GETDATE(),1,@RefObjID)
                    end
            End
        End
--################################################################################################################
    if not exists (select * from _CharTrijob where Level = 2)
        Begin
            PRINT 'No one In the 2nd Level Yet !'
            RETURN
        End

version 2.1

 Spoiler

-Added more checks to avoid bugs that could occur during usage

you need to create new table

 Spoiler

USE [SRO_VT_SHARD]
GO

/****** Object:  Table [dbo].[Reward_Job_Info]    Script Date: 10/16/2012 15:52:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Reward_Job_Info](
    [CharID] [int] NOT NULL,
    [CharName] [varchar](16) NOT NULL,
    [Message_Stat] [varchar](50) NOT NULL,
    [Reward_Stat] [varchar](16) NOT NULL,
    [Number] [int] IDENTITY(1,1) NOT NULL,
    [Job_Type] [varchar](16) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Reward_Job_Info] ADD  CONSTRAINT [DF_Reward_Message_Message_Stat]  DEFAULT ((0)) FOR [Message_Stat]
GO

_CharTrijob

 Spoiler

DoneORnot    varchar(16)    (default value = 0) NOT NULL

the Query (will be into C# after development end)

 Spoiler

use SRO_VT_SHARD
DECLARE @JobType Varchar(10),@CharID INT,@CharName VARCHAR(16),@AccountID INT,@Job_Type VARCHAR(16),@GoldReward INT,@ID64 INT,@RefObjID INT
,@ServerName Varchar(16)
--################################################################################################################
SET @GoldReward = 1000
SET @JobType = 'Thief'    -- thief & trader & hunter
SET @ServerName = 'Eagle'    --- for exable Eagle Team 
--################################################################################################################
If @JobType = 'Trader' set @Job_Type = 1 If @JobType = 'Thief' set @Job_Type = 2 If @JobType = 'Hunter' set @Job_Type = 3
if exists (select MIN(CharID) from _CharTrijob where Level = 2 and JobType = @Job_Type and DoneORnot = 0)
    begin
        SET @CharID = (select MIN(CharID) from _CharTrijob where Level = 2 and JobType = @Job_Type and DoneORnot = 0)
        SET @CharName = (select CharName16 from _Char where CharID = @CharID)
        SET @Job_Type = (select JobType from _CharTrijob where CharID = @CharID)
        SET @AccountID = (select UserJID from _User where CharID = @CharID)
        SET @ID64 = (select MAX(ID64)from _Memo)+1
        SET @RefObjID = (select RefObjID from _Char where CharID = @CharID)
--        _________________________________________________________________________________________________________    
        INSERT INTO Reward_Job_Info (CharID,CharName,Message_Stat,Reward_Stat,Job_Type) VALUES (@CharID,@CharName,0,0,@JobType)
        update _CharTrijob Set DoneORnot = 1 where CharID = @CharID
        update Reward_Job_Info SET Reward_Stat = 1 where CharID = @CharID 
        update _AccountJID set Gold = Gold + @GoldReward where JID = @AccountID
        update Reward_Job_Info Set Message_Stat = 1 where CharID = @CharID 
        SET IDENTITY_INSERT _Memo ON
            INSERT INTO _Memo (ID64,CharID,FromCharName,Message,Date,Status,RefObjID)
                Values (@ID64,@CharID,@ServerName+' Team','Hello you have Been Rewarded with '+@GoldReward+' gold For getting new Level by doing ' +@JobType+ ' Job',GETDATE(),1,@RefObjID)
        SET IDENTITY_INSERT _Memo OFF
    End
--################################################################################################################
if not exists (select MIN(CharID) from _CharTrijob where Level = 2 and JobType = @Job_Type and DoneORnot = 0)
    begin
        print 'There is no more '+@JobType+' to get reward'
    end

version 2.0

 Spoiler

-Fully rewritten
-The program currently adding gold to storage to avoid bugs
-The program now sends message to players regarding to their reward
-The program only works with level 2
-If player died and downgrade the level it won't consume money again (works on version 1.1)
-You can't reset the stuff for now

you need to create new table

 Spoiler

USE [SRO_VT_SHARD]
GO

/****** Object:  Table [dbo].[Reward_Job_Info]    Script Date: 10/16/2012 15:52:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Reward_Job_Info](
    [CharID] [int] NOT NULL,
    [CharName] [varchar](16) NOT NULL,
    [Message_Stat] [varchar](50) NOT NULL,
    [Reward_Stat] [varchar](16) NOT NULL,
    [Number] [int] IDENTITY(1,1) NOT NULL,
    [Job_Type] [varchar](16) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Reward_Job_Info] ADD  CONSTRAINT [DF_Reward_Message_Message_Stat]  DEFAULT ((0)) FOR [Message_Stat]
GO

_CharTrijob

 Spoiler

DoneORnot    varchar(16)    (default value = 0) NOT NULL

the Query (will be into C# after development end)

 Spoiler

use SRO_VT_SHARD
DECLARE @JobType Varchar(10),@CharID INT,@CharName VARCHAR(16),@AccountID INT,@Job_Type VARCHAR(16),@GoldReward INT,@ID64 INT,@RefObjID INT
,@ServerName Varchar(16)
--################################################################################################################
SET @GoldReward = 1000
SET @JobType = 'Thief'    -- thief & trader & hunter
SET @ServerName = 'Eagle'    --- for example Eagle Team 
--################################################################################################################
If @JobType = 'Trader' set @Job_Type = 1 If @JobType = 'Thief' set @Job_Type = 2 If @JobType = 'Hunter' set @Job_Type = 3
    SET @CharID = (select MIN(CharID) from _CharTrijob where Level = 2 and JobType = @Job_Type and DoneORnot = 0)
    SET @CharName = (select CharName16 from _Char where CharID = @CharID)
    SET @Job_Type = (select JobType from _CharTrijob where CharID = @CharID)
    SET @AccountID = (select UserJID from _User where CharID = @CharID)
    SET @ID64 = (select MAX(ID64)from _Memo)+1
    SET @RefObjID = (select RefObjID from _Char where CharID = @CharID)
--################################################################################################################    
        INSERT INTO Reward_Job_Info (CharID,CharName,Message_Stat,Reward_Stat,Job_Type) VALUES (@CharID,@CharName,0,0,@JobType)
        update _CharTrijob Set DoneORnot = 1 where CharID = @CharID
        update Reward_Job_Info SET Reward_Stat = 1 where CharID = @CharID 
        update _AccountJID set Gold = Gold + @GoldReward where JID = @AccountID
        update Reward_Job_Info Set Message_Stat = 1 where CharID = @CharID 
        SET IDENTITY_INSERT _Memo ON
            INSERT INTO _Memo (ID64,CharID,FromCharName,Message,Date,Status,RefObjID)
                Values (@ID64,@CharID,@ServerName+' Team','Hello you have Been Rewarded with '+@GoldReward+' gold For getting new Level by doing ' +@JobType+ ' Job',GETDATE(),1,@RefObjID)
        SET IDENTITY_INSERT _Memo OFF
--################################################################################################################

Version 1.1

 Spoiler

- it support 1 job atm
- it gives only gold as a reward atm
- there is no messages to inform the players that they have a special reward.
- it works only with level 2
- if a player downgrade the level it will take the money again !
- rest option
----------
you need to add new columns into _Char & _CharTrijob

_Char

 Spoiler

Job_Reward        varchar(16)    (default value = 0) NOT NULL
Message_Stat    varchar(16)    (default value = 0) NOT NULL

_CharTrijob

 Spoiler

DoneORnot    varchar(16)    (default value = 0) NOT NULL

the Query (will be into C# after development end)

 Spoiler

use SRO_VT_SHARD
declare @Reward INT,@Type VARCHAR (10), @JobType Varchar(10)

--################################
SET @Type = 'START' --- REST OR START
set @Reward = 100 --- Gold atm
SET @JobType = 'Thief' -- Thief -- Trader -- Hunter
--################################
IF @JobType = 'Thief' SET @JobType = 2 IF @JobType = 'Trader' SET @JobType = 1 IF @JobType = 'Hunter' SET @JobType = 3
if @Type = 'Start'
    Begin
            update _Char set RemainGold = RemainGold + @Reward, Job_Reward = 1  where CharID = (select MIN(CharID) from _CharTrijob where Level = 2 and JobType = @JobType and DoneORnot = 0) and Message_Stat = 0 and Job_Reward = 0
            update _CharTrijob set DoneORnot = 1 where CharID IN (select CharID from _Char where Job_Reward = 1)
        if Exists (select level from _CharTrijob where DoneORnot = 1 and Level = 1 and JobType = @JobType)
            Begin
                update _Char set RemainGold = RemainGold - @Reward ,Job_Reward = 20 where CharID IN (select CharID from _CharTrijob where DoneORnot = 1 and Level = 1 and JobType = @JobType)
                update _CharTrijob set DoneORnot = 0 where DoneORnot = 1 and Level = 1 and JobType = @JobType
            end
        if exists (select Job_Reward from _Char where Job_Reward = 20)
            begin
                if exists (select level from _CharTrijob where Level = 2 and DoneORnot = 0 and JobType = @JobType and CharID IN (select CharID from _Char where Job_Reward = 20))
                    begin
                        update _Char set Job_Reward = 0 where CharID = (select MIN(CharID)from _CharTrijob where Level = 2 and DoneORnot = 0 and JobType = @JobType and CharID IN (select CharID from _Char where Job_Reward = 20))
                        update _Char set RemainGold = RemainGold + @Reward, Job_Reward = 1  where CharID = (select MIN(CharID) from _CharTrijob where Level = 2 and JobType = @JobType and DoneORnot = 0) and Message_Stat = 0 and Job_Reward = 0
                        update _CharTrijob set DoneORnot = 1 where CharID IN (select CharID from _Char where Job_Reward = 1)
                    end
            end
    End
if @Type = 'REST'
    BEGIN
        PRINT 'WARRING PLEASE NOTE THAT YOU CANT GET THE REWARD GOLD ANY MORE'
        update _Char set Job_Reward = 0 ,Message_Stat = 0
        update _CharTrijob SET DoneORnot = 0
        PRINT 'REST DONE'
    END

Here is the list with the people who have helped in this query development

 Spoiler

1st : Caipi
2nd : LastThief

At last how you can help me ?
Post any edited stuff and I'll make it compatible with my query and I'll re-release it again !

Kind regards ,
Ahmed

[Zodiao]

Now im proud that im egyptian again,
nice job

[PortalDark]

#cleaned
there will be no tolerance on racist post
stay on topic, if you wanna post something positive, do so
if not, feel free to go to the flamewar section and argue there

[ahmed4ever2u]

Version 2 is out.

[killercom]

Originally Posted by ahmed4ever2u

Isro Killed the job system after several improves they thought its good improvements but they fucked it up how ever i worked on it about 3 hours through the last 2 days
yea slow
i made this one for some one who gave up and dont need it anymore.
i decided to share and make this section come alive again
---------
what is the aim of it ?
giving special rewards for every Player do jobs and reach more levels !
----------

lets go to the script


version 2.0 !
- fully re-written.
- it add the gold to the storage now (less bugs Thanks to caipi for the advice)
- it send messages now (informing the players that they have got reward)
- works only with level 2
- if a player downgrade the level it will NOT take the money again ! (disabled "its working on version 1.1")
- you cant do rest yet.
----------
you need to create new table

 Spoiler

USE [SRO_VT_SHARD]
GO

/****** Object:  Table [dbo].[Reward_Job_Info]    Script Date: 10/16/2012 15:52:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Reward_Job_Info](
	[CharID] [int] NOT NULL,
	[CharName] [varchar](16) NOT NULL,
	[Message_Stat] [varchar](50) NOT NULL,
	[Reward_Stat] [varchar](16) NOT NULL,
	[Number] [int] IDENTITY(1,1) NOT NULL,
	[Job_Type] [varchar](16) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Reward_Job_Info] ADD  CONSTRAINT [DF_Reward_Message_Message_Stat]  DEFAULT ((0)) FOR [Message_Stat]
GO

_CharTrijob

 Spoiler

DoneORnot	varchar(16)	(default value = 0) NOT NULL

the Query (will be into C# after development end)

 Spoiler

use SRO_VT_SHARD
DECLARE @JobType Varchar(10),@CharID INT,@CharName VARCHAR(16),@AccountID INT,@Job_Type VARCHAR(16),@GoldReward INT,@ID64 INT,@RefObjID INT
,@ServerName Varchar(16)
--################################################################################################################
SET @GoldReward = 1000
SET @JobType = 'Thief'	-- thief & trader & hunter
SET @ServerName = 'Eagle'	--- for example Eagle Team 
--################################################################################################################
If @JobType = 'Trader' set @Job_Type = 1 If @JobType = 'Thief' set @Job_Type = 2 If @JobType = 'Hunter' set @Job_Type = 2
	SET @CharID = (select MIN(CharID) from _CharTrijob where Level = 2 and JobType = @Job_Type and DoneORnot = 0)
	SET @CharName = (select CharName16 from _Char where CharID = @CharID)
	SET @Job_Type = (select JobType from _CharTrijob where CharID = @CharID)
	SET @AccountID = (select UserJID from _User where CharID = @CharID)
	SET @ID64 = (select MAX(ID64)from _Memo)+1
	SET @RefObjID = (select RefObjID from _Char where CharID = @CharID)
--################################################################################################################	
		INSERT INTO Reward_Job_Info (CharID,CharName,Message_Stat,Reward_Stat,Job_Type) VALUES (@CharID,@CharName,0,0,@JobType)
		update _CharTrijob Set DoneORnot = 1 where CharID = @CharID
		update Reward_Job_Info SET Reward_Stat = 1 where CharID = @CharID 
		update _AccountJID set Gold = Gold + @GoldReward where JID = @AccountID
		update Reward_Job_Info Set Message_Stat = 1 where CharID = @CharID 
		SET IDENTITY_INSERT _Memo ON
			INSERT INTO _Memo (ID64,CharID,FromCharName,Message,Date,Status,RefObjID)
				Values (@ID64,@CharID,@ServerName+' Team','Hello you have Been Rewarded with '+@GoldReward+' gold For getting new Level by doing ' +@JobType+ ' Job',GETDATE(),1,@RefObjID)
		SET IDENTITY_INSERT _Memo OFF
--################################################################################################################

Version 1.1

 Spoiler

- it support 1 job atm
- it gives only gold as a reward atm
- there is no messages to inform the players that they have a special reward.
- it works only with level 2
- if a player downgrade the level it will take the money again !
- rest option
----------
you need to add new columns into _Char & _CharTrijob

_Char

 Spoiler

Job_Reward		varchar(16)	(default value = 0) NOT NULL
Message_Stat	varchar(16)	(default value = 0) NOT NULL

_CharTrijob

 Spoiler

DoneORnot	varchar(16)	(default value = 0) NOT NULL

the Query (will be into C# after development end)

 Spoiler

use SRO_VT_SHARD
declare @Reward INT,@Type VARCHAR (10), @JobType Varchar(10)

--################################
SET @Type = 'START' --- REST OR START
set @Reward = 100 --- Gold atm
SET @JobType = 'Thief' -- Thief -- Trader -- Hunter
--################################
IF @JobType = 'Thief' SET @JobType = 2 IF @JobType = 'Trader' SET @JobType = 1 IF @JobType = 'Hunter' SET @JobType = 3
if @Type = 'Start'
	Begin
			update _Char set RemainGold = RemainGold + @Reward, Job_Reward = 1  where CharID = (select MIN(CharID) from _CharTrijob where Level = 2 and JobType = @JobType and DoneORnot = 0) and Message_Stat = 0 and Job_Reward = 0
			update _CharTrijob set DoneORnot = 1 where CharID IN (select CharID from _Char where Job_Reward = 1)
		if Exists (select level from _CharTrijob where DoneORnot = 1 and Level = 1 and JobType = @JobType)
			Begin
				update _Char set RemainGold = RemainGold - @Reward ,Job_Reward = 20 where CharID IN (select CharID from _CharTrijob where DoneORnot = 1 and Level = 1 and JobType = @JobType)
				update _CharTrijob set DoneORnot = 0 where DoneORnot = 1 and Level = 1 and JobType = @JobType
			end
		if exists (select Job_Reward from _Char where Job_Reward = 20)
			begin
				if exists (select level from _CharTrijob where Level = 2 and DoneORnot = 0 and JobType = @JobType and CharID IN (select CharID from _Char where Job_Reward = 20))
					begin
						update _Char set Job_Reward = 0 where CharID = (select MIN(CharID)from _CharTrijob where Level = 2 and DoneORnot = 0 and JobType = @JobType and CharID IN (select CharID from _Char where Job_Reward = 20))
						update _Char set RemainGold = RemainGold + @Reward, Job_Reward = 1  where CharID = (select MIN(CharID) from _CharTrijob where Level = 2 and JobType = @JobType and DoneORnot = 0) and Message_Stat = 0 and Job_Reward = 0
						update _CharTrijob set DoneORnot = 1 where CharID IN (select CharID from _Char where Job_Reward = 1)
					end
			end
	End
if @Type = 'REST'
	BEGIN
		PRINT 'WARRING PLEASE NOTE THAT YOU CANT GET THE REWARD GOLD ANY MORE'
		update _Char set Job_Reward = 0 ,Message_Stat = 0
		update _CharTrijob SET DoneORnot = 0
		PRINT 'REST DONE'
	END

how can you help me?
post your edited parts only in a reply and i'll make it work with my Query .

you always surprise us by your awesome work

[ahmed4ever2u]

Version 2.1 is OUT !

[_slimshady_]

I think you should say this is only for 1.93 files with new job system. Can you make something like this for the old job system? Levels 1-8?

[ahmed4ever2u]

Version 2.3 is OUT !

Originally Posted by _slimshady_

I think you should say this is only for 1.93 files with new job system. Can you make something like this for the old job system? Levels 1-8?

its for 1.88
and you can customize it its like an open source

[PortalDark]

#moved to correct section

[ahmed4ever2u]

Version 2.4 is out !

[nemanja1992]

Great Job Bro keep it good :P

[ahmed4ever2u]

Version 2.6 is OUT !

[SupremeSRO]

i read like 3 times ur thread but i still dont understand what this do ..

"Short answer : Rebirthing job system it'll give rewards for the active jobbers more jobbing = more profit ! so the players would have the inducement to do more jobs"

could you explain me more ? what this can do exactly?

Thx in advance

[ahmed4ever2u]

Originally Posted by SupremeSRO

i read like 3 times ur thread but i still dont understand what this do ..

"Short answer : Rebirthing job system it'll give rewards for the active jobbers more jobbing = more profit ! so the players would have the inducement to do more jobs"

could you explain me more ? what this can do exactly?

Thx in advance

simply doing jobs = more income for the players

it will never be based on getting your reward from stealing the trader goods (thats the thief)
or getting your reward from helping the trader(thats the hunter) or getting your reward from selling your goods(thats the trader)

my system will make it enjoyable because it will give them another rewards !
my system will change the aim for doing jobs which it will make them do WAR HUNTERS VS THIEFS and this will make the players active because the reward will be silks & special items & even Gold !

[unforgiven78]

I followed every step you mentioned carefully, except the last step about C# query.. I don't know where to use that !!
Should i use it in SQL server 2012 just like the previous queries?
or
Should i use it in Visual Studio C# which i've never been there before to get something done with my server?

Another question.. Let's say that i'm finished with setting up your system, How do i change the reward for traders for example?

I want to give the traders additional rewards beside gold such as GOLD COINS,
I want to give the thieves additional rewards beside gold such as SILVER COINS,

How do i do that?


Thank you & Good job