USE [SRO_VT_SHARD]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-----------------------------------------------------------------------------------------------------

ALTER procedure [dbo].[_RenameCharNameByID]
@CharID        int,
@NewName    varchar(64)
as
    if (not exists (select charid from _char where charid = @charid))
        return -1
    set xact_abort on
begin transaction
    if (exists (select charid from _charnamelist with (updlock) where charname16 = @NewName))
    begin
        rollback transaction
        return -3
    end
    declare @old_name varchar(64)
    select @old_name = charname16 from _char where charid = @charid
    update _char set charname16 = @NewName where charid = @charid
    if (@@error <> 0 or @@rowcount = 0)
    begin
        rollback transaction
        return -4       
    end
    insert _CharNameList values(@NewName, @charid)
    UPDATE _Char set CharName16 = @NewName where CharID = @CharID
    update _Friend set friendcharname = @NewName where friendcharid = @charid
    update _GuildMember set charname = @NewName where charid = @charid
    update _Memo set fromcharname = @NewName where fromcharname = @old_name
    update _TrainingCampMember set charname = @NewName where charid = @charid
    declare @cos_id int
    declare cos_cursor  cursor fast_forward for   
    select id
    from _charcos 
    where ownercharid = @CharID
    open cos_cursor 
    fetch next from cos_cursor  into @cos_id
    while( @@fetch_status = 0 )
    begin
        update _Items set CreaterName = @NewName 
        where ID64 in 
        (select ItemID from _InvCOS where COSID = @cos_id and ItemID > 0) 
        and CreaterName = @old_Name
        if (@@error <> 0 )
        begin
            close cos_cursor
            deallocate cos_cursor
            rollback transaction
            return -5
        end
        fetch next from cos_cursor  into @cos_id
    end
    close cos_cursor
    deallocate cos_cursor
    declare pc_inv_cursor cursor fast_forward for
    select it.Data                           
    from _Inventory as inv join _Items as it on inv.ItemID = it.ID64 
    where 
    (inv.CharID = @CharID and inv.Slot >= 13 and inv.ItemID > 0) and   
    (it.Data <> 0)  and                           
    (exists (select top 1 ID from _RefObjCommon where ID = it.RefItemID and TypeID1 = 3 and TypeID2 = 2))
    open pc_inv_cursor 
    fetch next from pc_inv_cursor  into @cos_id
    while( @@fetch_status = 0 )
    begin
        update _Items set CreaterName = @NewName 
        where ID64 in 
        (select ItemID from _InvCOS where COSID = @cos_id and ItemID > 0) 
        and CreaterName = @old_Name
        if (@@error <> 0 )
        begin
            close pc_inv_cursor
            deallocate pc_inv_cursor
            rollback transaction
            return -6
        end
        fetch next from pc_inv_cursor  into @cos_id
    end
    close pc_inv_cursor
    deallocate pc_inv_cursor
    if (CHARINDEX('@', @old_name, 0) > 0)
    begin
        select * from _Char
        where CharID = ''
    end
    commit transaction
return 1 

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_AddTimedJob]    Script Date: 06/05/2013 15:10:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  procedure [dbo].[_AddTimedJob]
@CharID            int,
@Category        tinyint,
@JobID            int,
@TimeToKeep    int,
@Data1            int,
@Data2            int,
--##begin due to develop composite item
@Data3            int,
@Data4            int,
@Data5            int,
@Data6            int,
@Data7            int,
@Data8            int,
@Serial64        bigint,
@JID            int
--##end due to develop composite item
as
    if(@JobID = '10000') -- RefSkill ID again
        BEGIN
declare @CharName16 varchar(64)
        Select @CharName16=CharName16 from _Char where CharID = @CharID
        Update _Char Set CharName16 = '@'+ @CharName16 Where CharID = @CharID
            return -3
        END 
    if (not exists (select CharID from _Char with (nolock) where CharID = @CharID))
                return -1
            if (@TimeToKeep <= 0)
                return -2
            declare @NewJobID int
            set @NewJobID = 0
            --##JobID 1 = Guild // JobID 2 = Thief/Hunter/Trader
            --##If you want to only have one not both, then just modify the line below!
            --if (@JobID = 1 and @Category = 2 and @Serial64=0) or (@JobID = 2 and @Category = 2 and @Serial64=0)  --For Guild and CharJob           
    if (@JobID = 1 and @Category = 2 and @Serial64=0)  --Only for Guild
            BEGIN
            set @CharID = 0           
            insert into _TimedJob(CharID,Category,JobID,TimeToKeep,Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8,Serial64,JID)
            values(@CharID, @Category, @JobID, @TimeToKeep, @Data1, @Data2, @Data3, @Data4, @Data5, @Data6, @Data7, @Data8, @Serial64, @JID)
           
            END
            ELSE
            BEGIN
            --##begin due to develop composite item
            insert into _TimedJob(CharID,Category,JobID,TimeToKeep,Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8,Serial64,JID)
            values(@CharID, @Category, @JobID, @TimeToKeep, @Data1, @Data2, @Data3, @Data4, @Data5, @Data6, @Data7, @Data8, @Serial64, @JID)
            END
            --##end due to develop composite item
            set @NewJobID = @@identity
            if (@@error <> 0 or @@rowcount = 0)
               
                return -3
    return @NewJobID