DB Log with Plus from Weapons, GLobals saved in tables and more

Table

PHP Code:

---

USE [ytytty]
GO

/****** Object:  Table [dbo].[_LogItemPlus]    Script Date: 05/29/2013 06:15:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[_LogItemPlus](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CharID] [varchar](10) NOT NULL,
    [Serial64] [varchar](64) NOT NULL,
    [Plus] [text] NOT NULL,
    [Data] [datetime] NOT NULL,
    [Lido] [int] NOT NULL,
 CONSTRAINT [PK__LogItemPlus1_1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO 


---

Procedure

PHP Code:

---

USE [ytytty]
GO

/****** Object:  StoredProcedure [dbo].[_AddLogItem]    Script Date: 05/29/2013 06:17:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-------------------------------------------------------------------------------------------------------------------
-- Step 2
-- ±âÁ¸ ¾ÆÀÌÅÛ ·Î±× ÀԷ Stored procedure ¼öÁ¤
-- DataBase            : Shard Log  
CREATE  procedure [dbo].[_AddLogItem]  
  @CharID  int,  
  @ItemRefID int,  
  @ItemSerial bigint,  
  @dwData int,  
  @TargetStorage tinyint,  
  @Operation tinyint,  
  @Slot_From tinyint,  
  @Slot_To tinyint,  
  @EventPos varchar(64),  
  @strDesc varchar(128),
  @Gold        bigint        -- #ifdef EXTEND_GOLD_TYPE »ðÀÔ   
  as  
   declare @len_pos int  
   declare @len_desc int 
   declare @strDesc_separado varchar(128) 
   declare  @len_desc1 int
   set @len_pos = len(@EventPos)  
   set @len_desc = len(@strDesc)  
   if (@len_pos > 0 and @len_desc > 0)  
   begin  
            insert _LogEventItem (EventTime, CharID, ItemRefID, dwData, TargetStorage, Operation, Slot_From, Slot_To, EventPos, strDesc, Serial64, Gold)    -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼
            values(GetDate(), @CharID, @ItemRefID, @dwData, @TargetStorage, @Operation, @Slot_From, @Slot_To, @EventPos, @strDesc, @ItemSerial, @Gold)        -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼
   end  
   else if (@len_pos > 0 and @len_desc = 0)  
   begin  
            insert _LogEventItem (EventTime, CharID, ItemRefID, dwData, TargetStorage, Operation, Slot_From, Slot_To, EventPos, Serial64, Gold)            -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼
            values(GetDate(), @CharID, @ItemRefID, @dwData, @TargetStorage, @Operation, @Slot_From, @Slot_To, @EventPos, @ItemSerial, @Gold)                -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼ 
   end  
   else if (@len_pos = 0 and @len_desc > 0)  
   begin  
            insert _LogEventItem (EventTime, CharID, ItemRefID, dwData, TargetStorage, Operation, Slot_From, Slot_To, strDesc, Serial64, Gold)            -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼
            values(GetDate(), @CharID, @ItemRefID, @dwData, @TargetStorage, @Operation, @Slot_From, @Slot_To, @strDesc, @ItemSerial, @Gold)                    -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼ 
   end  
   else if (@len_pos = 0 and @len_desc = 0)  
   begin  
            insert _LogEventItem (EventTime, CharID, ItemRefID, dwData, TargetStorage, Operation, Slot_From, Slot_To, Serial64, Gold)                        -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼
            values(GetDate(), @CharID, @ItemRefID, @dwData, @TargetStorage, @Operation, @Slot_From, @Slot_To, @ItemSerial, @Gold)                            -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼
   end  
   -- À¯·á ¾ÆÀÌÅÛ ±¸ÀÔÀ̸é!  
   -- #define LOG_ITEMEVENT_BUY_CASHITEM  (BYTE)35  
   if (@Operation = 35)  
   begin  
    insert _LogCashItem (RefItemID, CharID, Cnt, EventTime, Serial64)  
    values(@ItemRefID, @CharID, @dwData, GetDate(), @ItemSerial)  
   end
   if (@Operation = 90 OR @Operation = 160)  
   begin  
   set @len_desc1 = CHARINDEX (']',@strDesc) 
  if(@len_desc1 = 9)  
     begin
     set @strDesc = SUBSTRING(@strDesc,0,9)
     set @strDesc_separado = SUBSTRING(@strDesc, 8, 1)
    end
   else if(@len_desc1 = 10)
        begin
        set @strDesc = SUBSTRING(@strDesc,0,10)
        set @strDesc_separado = SUBSTRING(@strDesc, 8, 2)
        end
        if(@strDesc_separado >= 8)
        begin
        --set @strDesc_separado = @len_desc1
      insert _LogItemPlus(CharID, Serial64, Plus, Data, Lido)  
    values(@CharID, @ItemSerial, @strDesc_separado, GetDate(), '0')  
    end 
   end

GO 


---

Table

PHP Code:

---

USE [ytytty]
GO

/****** Object:  Table [dbo].[_LogGuildWar]    Script Date: 05/29/2013 06:15:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[_LogGuildWar](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [GUILDWINNER] [text] NOT NULL,
    [GUILDLOST] [text] NOT NULL,
 CONSTRAINT [PK__LogGuildWar] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO 


---

Procedure

Table

PHP Code:

---

USE [ytytty]
GO

/****** Object:  Table [dbo].[_LogPvP]    Script Date: 05/29/2013 06:16:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[_LogPvP](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CharWinner] [varchar](16) NOT NULL,
    [CharLost] [varchar](16) NOT NULL,
    [Data] [datetime] NOT NULL,
 CONSTRAINT [PK__log1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO 


---

Procedure

Table

PHP Code:

---

USE [ytytty]
GO

/****** Object:  Table [dbo].[_On]    Script Date: 05/29/2013 06:16:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[_On](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [CharName] [varchar](50) NOT NULL,
    [Statu] [varchar](50) NOT NULL,
 CONSTRAINT [PK__On] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO 


---

Procedure

Table

PHP Code:

---

USE [ytytty]
GO

/****** Object:  Table [dbo].[DBItem]    Script Date: 05/29/2013 06:16:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DBItem](
    [ID] [int] NULL,
    [Name] [varchar](128) NULL,
    [CName] [varchar](128) NULL,
    [Pict] [varchar](128) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO 


---

Procedure

Table

PHP Code:

---

USE [ytytty]
GO

/****** Object:  Table [dbo].[_GlobalsLog]    Script Date: 05/29/2013 06:16:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[_GlobalsLog](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [CharName] [varchar](16) NULL,
    [Globals] [text] NULL,
 CONSTRAINT [PK__GlobalsLog] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO 


---

Procedure

hello

good works

i want

Jobs reward

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

Good Work Bro

Well, only ItemPluss works fine (btw whats the min + it logs?)

youre missing the procedure parts for everything except ItemPlus ....

SRO_VT_SHARD or SRO_VT_LOG ?

Quote:

---

Originally Posted by loveme

SRO_VT_SHARD or SRO_VT_LOG ?

---

I think Log :)

Quote:

---

Originally Posted by Showtek

I think Log :)

---

or create a new db?

no matter what db it is, youre still missing the stored procedure edits for everything except item plus log ..

Quote:

---

Originally Posted by lemoniscool

no matter what db it is, youre still missing the stored procedure edits for everything except item plus log ..

---

yeah, just took a quick look on it, but couldnt find anything related to globals, the table is created, but nothing more :)

MG,

Change your procedures from ->

Code:

---

USE [ytytty]

---

to

Code:

---

USE [SRO_VT_LOG or SRO_VT_SHARDLOG]

---

yeah he is missing the insert datas for the rest of the queries... anyways...



to MG,

Instead of building more stress on SRO_VT_LOG Database and adding a lot more code than needed, since you are adding the same data to both _LogEventItem and _LogItemPlus

instead of doing that, you can basically just alter the original _LogEventItem query, they are seperated into 4 main if statments:

Code:

---

if (@len_pos > 0 and @len_desc > 0) 
  else if (@len_pos > 0 and @len_desc = 0) 
  else if (@len_pos = 0 and @len_desc > 0) 
  else if (@len_pos = 0 and @len_desc = 0)

---

what you can do here is find out the len_pos and the len_desc of example the plusing of items, it will follow one of these categories. And you just need to add your own if statment inside of that if statment.

example:

Code:

---

else if (@len_pos = 0 and @len_desc = 0)
if (@Operation = 35) 
  begin 
    insert _LogCashItem (RefItemID, CharID, Cnt, EventTime, Serial64) 
    values(@ItemRefID, @CharID, @dwData, GetDate(), @ItemSerial) 
  end
else 
  begin 
            insert _LogEventItem (EventTime, CharID, ItemRefID, dwData, TargetStorage, Operation, Slot_From, Slot_To, Serial64, Gold)                        -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼
            values(GetDate(), @CharID, @ItemRefID, @dwData, @TargetStorage, @Operation, @Slot_From, @Slot_To, @ItemSerial, @Gold)                            -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼
  end


---

so this way it will just reduce the amount of data being inserted a lot... you can do more performance steps, but generally this will make your life easier.


Good luck and nice work.

_LogPvP , _GlobalsLog , _On

these negative does not save data

Quote:

---

Originally Posted by loveme

_LogPvP , _GlobalsLog , _On

these negative does not save data

---

do you even read the answeres in this post Oo thats exactly what i said 3, and 7 posts above and what Jangan said 1 post above ..

@MGProject: why dont you just tell us the EventIDs for pvp, global and guild war? we could write our own statements for the procedures then ^^

i made it but when i make plus nothing happen in game but when i enter SRO_VT_SHARDLOG ..._LogItemPlus
i see Char ID Made Plus Number etc

why i dont see global say .... made ... +... in game ? same ISRO or what this querys ?

these querys are supposed to save the character logs in different tables not to view a message on screen if someone plussed an item ^^

@MG

Actually, forget about the length stuff.. Operation 90 means alchemy elixier success... you can let it find out the new plus using the dwdata... which is much easier then the method used above by you.

For finding out dwdata shit.... I'll make ur life easy: +6=393221 ;+7=458758;+8=524295;+9=589832
Basically, all the dwdata numbers are constant thus,

... Check for operation 90, case when dwata = xxx then ....
Basically 4 lines for all the item plus stuff.

Also if I'm not wrong, globals can only be logged via the gameserver directly and its not really anything to do witht he db directly (except ofc for storing purposes)

About the pvp procedure basically check for ... EDIT: DELETED THE STUFF.. said too much

EventID 19 is for PK only, free pvp mode is not recorded with this .. isnt there any way to log the free pvp mode?

I worked on that just 1-2 days ago... made a logger for FWkills, pvpkills and so on... was actually really easy. If you know some basic sql and know how to use SMC u can find out how to do it ^^

i need global log Procedure

global log for Procedure need

Any one help me in pluss notice plssss

pls who can reupdate GlobalLog Procedure

Quote:

---

Originally Posted by magamio

pls who can reupdate GlobalLog Procedure

---

me too!!!

thanks bro