[Share] Query to edit chaputonitems

[Jolin88]

Hello, this is the first release from the EliteRanDev, the team made up in the purpose to boost Ran Dev in peace

THIS IS FOR EP4 ONLY, NO GUARANTEE THAT IT WILL WORK ON EP3 IT MIGHT LEAD TO ITEM LOSS

This one was made by :
FredCunanan
and me, Jolin88
Version : 1.00


This query can edit items that players wear, and if you think its useless because there is a Ran Manager, think again :
You can build this into your CP to edit items through the CP, like refining weapon for EP


You have to run this query first to create the required functions :

Use RanGame1

------ hex_bin function should be added under RanGame1
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Modified by FredCunanan
-- Create date: 9/24/2009
-- Description: Function in SQL that converts string to binary
-- this must be added under rangame1
-- =============================================
Create FUNCTION [dbo].[hex_bin](@hexstr VARCHAR(8000))
RETURNS varbinary(8000)
AS
BEGIN
DECLARE @hex CHAR(2), @i INT, @count INT, @b varbinary(8000), @odd BIT, @start bit
SET @count = LEN(@hexstr)
SET @start = 1
SET @b = CAST('' AS varbinary(1))
IF SUBSTRING(@hexstr, 1, 2) = '0x'
SET @i = 3
ELSE
SET @i = 1
SET @odd = CAST(LEN(SUBSTRING(@hexstr, @i, LEN(@hexstr))) % 2 AS BIT)
WHILE (@i <= @count)
BEGIN
IF @start = 1 AND @odd = 1
BEGIN
SET @hex = '0' + SUBSTRING(@hexstr, @i, 1)
END
ELSE
BEGIN
SET @hex = SUBSTRING(@hexstr, @i, 2)
END
SET @b = @b +
CAST(CASE WHEN SUBSTRING(@hex, 1, 1) LIKE '[0-9]'
THEN CAST(SUBSTRING(@hex, 1, 1) AS INT)
ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 1, 1)))-55 AS INT)
END * 16 +
CASE WHEN SUBSTRING(@hex, 2, 1) LIKE '[0-9]'
THEN CAST(SUBSTRING(@hex, 2, 1) AS INT)
ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 2, 1)))-55 AS INT)
END AS binary(1))
SET @i = @i + (2 - (CAST(@start AS INT) * CAST(@odd AS INT)))
IF @start = 1
BEGIN
SET @start = 0
END
END
RETURN @b
END

------ BinToChar function should be added under RanGame1

GO
-- =============================================
-- Author: Modified by FredCunanan
-- Create date: 09/22/09
-- Description: Function that converts binary to String
-- added additional parameters
-- =============================================
Create FUNCTION [dbo].[BinToChar](@binvalue varbinary(8000),@Len int,@Tag int,@Rev int)
RETURNS VarChar(8000)
WITH EXECUTE AS CALLER
AS
BEGIN
declare @charvalue varchar(8000)
declare @i int
declare @length int
declare @hexstring char(16)

SET @charvalue = '0x'
SET @i = 1
SET @length = datalength(@binvalue)
SET @hexstring = '0123456789ABCDEF'

while (@i <= @length)
begin

declare @tempint int
declare @firstint int
declare @secondint int

select @tempint = convert(int, substring(@binvalue,@i,1))
select @firstint = floor(@tempint/16)
select @secondint = @tempint - (@firstint*16)

select @charvalue = @charvalue +
substring(@hexstring, @firstint+1, 1) +
substring(@hexstring, @secondint+1, 1)

select @i = @i + 1

end
IF @tag = 1
Begin
set @charvalue = (Substring(@charvalue,3,@Len))
End
ELSE
Begin
set @charvalue = (Substring(@charvalue,0,@Len))
End

IF @Rev = 1
Begin
declare @TempStr as varchar(8000)
Set @i = len(@charvalue) + 1
Set @TempStr = ''
While @i > 1
Begin
Set @TempStr = @TempStr + Substring(@charvalue,@i-2,2)
Set @i = @i - 2
End
Set @charvalue = @TempStr
End
Return(@charvalue)
END


















------------------
This is the query to edit the items equiped by players :


declare @chaname varchar(30)
set @Chaname = 'put here the name of the char you want to edit'

declare @i int
-- @i controls what item you will edit : 1 = Coat 2 = Pants 3 = Gloves 4 = Shoes 5 = Weapon 6 = Rosary 7 = Earrings 8 = Top Ring 9 = Below Ring 10 = Arrows
set @i = 1 -- input here the number of what you want to edit

declare @c varchar(8000)
declare @x int
declare @MainID int -- New Main ID
set @MainID = 0
declare @SubID int -- New Sub ID
set @SubID = 0
declare @Drop int -- 6 = Undropable 5 = depends on item setting
set @Drop = 5
declare @RefiningAtk int -- Damage refining level
set @RefiningAtk = 0
declare @RefiningDef int -- Def refining level
set @RefiningDef = 0
declare @Fire int -- Fire refining level
set @Fire = 0
declare @Ele int -- Ele refining level
set @Ele = 0
declare @Ice int -- Ice refining level
set @Ice = 0
declare @Poi int -- Poi refining level
set @Poi = 0
declare @Air int -- Air refining level
set @Air = 0
declare @RV1 int -- RV type 1
set @RV1 = 0
declare @RV2 int -- RV type 2
set @RV2 = 0
declare @RV3 int -- RV type 3
set @RV3 = 0
declare @RV4 int -- RV type 4
set @RV4 = 0
declare @RVVal1 int -- RV value 1
set @RVVal1 = 0
declare @RVVal2 int -- RV value 2
set @RVVal2 = 0
declare @RVVal3 int -- RV value 3
set @RVVal3 = 0
declare @RVVal4 int -- RV value 4
set @RVVal4 = 0


update chainfo set @c = dbo.BinToChar(chaputonitems,8000,0,0) where ChaName = @Chaname
set @x = 64 * 2

-- Here are the variables you can edit from the items
-- If there is one you don't want to edit, just put -- before the statement

set @c = stuff(@c,((@x*@i)+ 27),4,dbo.BinToChar(convert(binary(2),@MainID),4,1,1)) -- item main ID
set @c = stuff(@c,((@x*@i)+ 31),4,dbo.BinToChar(convert(binary(2),@SubID),4,1,1)) -- item sub ID
set @c = stuff(@c,((@x*@i)+ 79+16),6,dbo.BinToChar(convert(binary(1),@Drop),2,1,0)+'00FF') -- 6 for Non-Drop Default Value 5
set @c = stuff(@c,((@x*@i)+ 85+16),2,dbo.BinToChar(convert(binary(1),@RefiningAtk),2,1,0)) -- Refining ATK
set @c = stuff(@c,((@x*@i)+ 87+16),2,dbo.BinToChar(convert(binary(1),@RefiningDef),2,1,0)) -- Refining Def
set @c = stuff(@c,((@x*@i)+ 89+16),2,dbo.BinToChar(convert(binary(1),@Fire),2,1,0)) -- Refining Resistance
set @c = stuff(@c,((@x*@i)+ 91+16),2,dbo.BinToChar(convert(binary(1),@Ele),2,1,0)) -- Refining Resistance
set @c = stuff(@c,((@x*@i)+ 93+16),2,dbo.BinToChar(convert(binary(1),@Ice),2,1,0)) -- Refining Resistance
set @c = stuff(@c,((@x*@i)+ 95+16),2,dbo.BinToChar(convert(binary(1),@Poi),2,1,0)) -- Refining Resistance
set @c = stuff(@c,((@x*@i)+ 97+16),2,dbo.BinToChar(convert(binary(1),@Air),2,1,0)) -- Refining Resistance
set @c = stuff(@c,((@x*@i)+ 99+16),2,dbo.BinToChar(convert(binary(1),@RV1),2,1,0)) -- +RV Type 1
set @c = stuff(@c,((@x*@i)+ 101+16),2,dbo.BinToChar(convert(binary(1),@RV2),2,1,0)) -- +RV Type 2
set @c = stuff(@c,((@x*@i)+ 103+16),2,dbo.BinToChar(convert(binary(1),@RV3),2,1,0)) -- +RV Type 3
set @c = stuff(@c,((@x*@i)+ 105+16),2,dbo.BinToChar(convert(binary(1),@RV4),2,1,0)) -- +RV Type 4
set @c = stuff(@c,((@x*@i)+ 107+16),4,dbo.BinToChar(convert(binary(2),@RVVal1),4,1,1)) -- +RV Value 1
set @c = stuff(@c,((@x*@i)+ 111+16),4,dbo.BinToChar(convert(binary(2),@RVVal2),4,1,1)) -- +RV Value 2
set @c = stuff(@c,((@x*@i)+ 115+16),4,dbo.BinToChar(convert(binary(2),@RVVal3),4,1,1)) -- +RV Value 3
set @c = stuff(@c,((@x*@i)+ 119+16),4,dbo.BinToChar(convert(binary(2),@RVVal4),4,1,1)) -- +RV Value 4
update chainfo set chaputonitems = dbo.hex_bin(@c) where ChaName = @Chaname











Thats all, any questions?

[MentaL]

[[ADMIN]dragon]

thank you jolin for that tell me your yahoo mesanger and i give a price for 10k pesos b coz you help me thnk you very much bcoz my server is now in top 1 dubai games..... hehhehehe

[Jolin88]

I don't want money for that, if I was doing it for money you'd surely not see it here
Good luck with your server

[ramispo]

sir jolin can u make one to put on our cp??
just asking
i trid it but i cant make it work on cp always error

[jeyci]

it would be best if you use tags. cant sort it out (im noob)

[volter]

anyone who success with this wonderful share by ser jolin?
cuz i can't make it work lock of idea.

[russhimi]

how to use it?
u said can edit threw CP,how?i need create new page?

[Thamodz]

@Jolin

Thanks!

[russhimi]

erk,how?

[Jolin88]

To use normally, you need to use it in the query editor in your SQL server, to use for the CP you have to script an SQL function out of it

[john_d]

Thanks for this. :P

twas very good.