[Development] Silkroad Jobs reward !

[ahmed4ever2u]

Originally Posted by 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

Well my system till now in beta stage its not finished yet and all of your askes already implemented into the sql script
About the C# part i will add it into C# API with a timer to spam the DB with this Query after the first Version be done

i will work on it again

[Neko*]

good job Ahmed

but i still don't get the idea !
this system gives you reward for reaching new level ?
i mean when i level up from 3->4 i will get a reward i determined in the query ?

i still don't get how does it reward you ?
i read all the query you made hope to understand how it works
but i don't get how _chartrijob work so i didn't get anything at all. :herbderb:

[ILowe]

Version 2.4 i add and dont give cion ?

[ILowe]

help

[eminem5]

you should make the version ex: 1.1.1


and nice job

[ILowe]

job When giving money only I would like to give gold coin at the end of each job

[isozy]

I like this idea a lot to improve the job pvp system around the servers, after all jobs are one of the main features of silkroad and they're fun to play. Someone should really release more stuff around the jobs area.

[Orion_Sro]

Awesome

[mimomaro]

Hard to Understand (

[geryxpg]

nice

[Manager.Play]

GOOD JOP AHMED

[Ch3tos]

Awesome work
but i wanna some small help for add new column in _CharTrijob
i think it's with query

[InZiDeR]

good work

[skip999]

Very nice... How can i use it for v1.93?

[GMCreative]

Originally Posted by 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

Hello could you tell me how to add a new column?
Do not do this part. please help me.
DoneORnot int NULL