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

[MGProjects]

Table

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

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

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

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

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

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

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

[MentaL]

[loveme]

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

[DrugDealers]

Good Work Bro

[Showtek]

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

[lemoniscool]

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

[loveme]

SRO_VT_SHARD or SRO_VT_LOG ?

[Showtek]

SRO_VT_SHARD or SRO_VT_LOG ?

I think Log :)

[loveme]

I think Log :)

or create a new db?

[lemoniscool]

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

[sladlejrhfpq]

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 :)

[Jangan]

MG,

Change your procedures from ->

USE [ytytty]

to

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:

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:

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.

[loveme]

_LogPvP , _GlobalsLog , _On

these negative does not save data

[lemoniscool]

_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 ^^

[keshk2007]

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 ?

[lemoniscool]

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