![[Release] Correct DB SP for Punishment system.](https://www.elitepvpers.com/forum/iconimages/sro-private-server/release-correct-db-sp-punishment-system_ltr.gif){kind=small}
[Release] Correct DB SP for Punishment system.
Here's my fixed Stored Procedures for a working Punishment system (Both AutoPunishment and SMC SR_UserPunishment)
All you need is to execute the following fixed SP:-
_RegisterPunishment
PHP Code:
USE [SRO_VT_ACCOUNT]
GO
/****** Object: StoredProcedure [dbo].[_RegisterPunishment] UPDATED BY KOC2000 at: 10/30/2011 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
ALTER procedure [dbo].[_RegisterPunishment]
@UserJID int,
@Type tinyint,
@Executor varchar(128),
@Shard smallint,
@CharName varchar(16),
@CharInfo varchar(256),
@PosInfo varchar(64),
@Guide varchar(512),
@Description varchar(1024),
@RaiseTime datetime,
@BlockStartTime datetime,
@BlockEndTime datetime,
@PunishTime datetime
as
-- ?? ??? Blocking??? ?????
if( exists( select UserJID from _BlockedUser with(NOLOCK) where UserJID = @UserJID and Type = @Type))
begin
select -1 -- ?? ??? ?? ??? ???
RETURN
end
declare @NewSerialNo int
set @NewSerialNo = 0
insert _Punishment values( @UserJID, @Type, @Executor, @Shard, @CharName, @CharInfo,
@PosInfo, @Guide, @Description, @RaiseTime, @BlockStartTime, @BlockEndTime, @PunishTime, 0)
set @NewSerialNo = @@identity
if( @@rowcount = 0 or @@error <> 0 or @NewSerialNo = 0 or @NewSerialNo is null)
begin
select -2 -- insert??? ?? ??
return
end
-- Getting username (KiMO)
declare @UserID varchar(25)
SELECT @UserID = StrUserID FROM TB_User WITH (NOLOCK) WHERE JID = @UserJID
if( @@rowcount = 0 or @@error <> 0 or @UserID is null)
BEGIN
SELECT -1
RETURN
END
/*
declare @UserID varchar(128)
select @UserID = sr_id from sr_member1 where sr_code = @UserJID
if( @@rowcount = 0 or @@error <> 0 or @UserID is null)
begin
select - 2
return
end
*/
-- ??? blocking? ????
insert _BlockedUser values( @UserJID, @UserID, @Type, @NewSerialNo, @BlockStartTime, @BlockEndTime)
select 0
_UpdatePunishment
PHP Code:
USE [SRO_VT_ACCOUNT]
GO
/****** Object: StoredProcedure [dbo].[_UpdatePunishment] UPDATED BY KOC2000 at: 10/30/2011 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
ALTER procedure [dbo].[_UpdatePunishment]
@SerialNo int,
@UserJID int,
@Type int,
@Executor varchar(32),
@Shard smallint,
@CharName varchar(32),
@CharInfo varchar(256),
@PosInfo varchar(64),
@Guide varchar(512),
@Description varchar(1024),
@RaiseTime datetime,
@BlockStartTime datetime,
@BlockEndTime datetime,
@PunishTime datetime
as
if( not exists( select SerialNo from _Punishment with(NOLOCK) where SerialNo = @SerialNo))
begin
select -1 -- valid?? ??
RETURN
end
declare @NewSerialNo int
set @NewSerialNo = 0
-- Getting username (KiMO)
declare @UserID varchar(25)
SELECT @UserID = StrUserID FROM TB_User WITH (NOLOCK) WHERE JID = @UserJID
if( @@rowcount = 0 or @@error <> 0 or @UserID is null)
BEGIN
SELECT -1
RETURN
END
/*
declare @UserID varchar(128)
select @UserID = sr_id from sr_member1 where sr_code = @UserJID
if( @@rowcount = 0 or @@error <> 0 or @UserID is null)
begin
select -1
return
end
*/
update _Punishment set Status = 1 where SerialNo = @SerialNo
-- ?? blocking? ????
delete _BlockedUser where UserJID = @UserJID
insert _Punishment values( @UserJID, @Type, @Executor, @Shard, @CharName, @CharInfo,
@PosInfo, @Guide, @Description, @RaiseTime, @BlockStartTime, @BlockEndTime, @PunishTime, 2)
set @NewSerialNo = @@identity
if( @@rowcount = 0 or @@error <> 0 or @NewSerialNo = 0 or @NewSerialNo is null)
begin
select -2 -- insert??? ?? ??
return
end
-- ??? blocking? ????
insert _BlockedUser values( @UserJID, @UserID, @Type, @NewSerialNo, @BlockStartTime, @BlockEndTime)
select 0
_RegisterAutomatedPunishment
PHP Code:
USE [SRO_VT_ACCOUNT]
GO
/****** Object: StoredProcedure [dbo].[_RegisterAutomatedPunishment] UPDATED BY KOC2000 at: 10/30/2011 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[_RegisterAutomatedPunishment]
@Account VARCHAR(128), --This is "strUserID"
@Type TINYINT,
@Executor VARCHAR(128),
@Guide VARCHAR(512),
@Description VARCHAR(1024),
@BlockTimeElapse INT
AS
------------------------------------------------------------------------------------
-- novice with deepdark
-- depend only on db time.. we dont have to sync the time between DB and GlobalManager for accuracy
DECLARE @BlockStartTime VARCHAR(128)
DECLARE @BlockEndTime VARCHAR(128)
SET @BlockStartTime = getdate()
SET @BlockEndTime = dateadd( mi, @BlockTimeElapse, @BlockStartTime)
------------------------------------------------------------------------------------
DECLARE @UserJID INT
SET @UserJID = 0
-- Get UserJID from provided @Account.
SELECT @UserJID = JID FROM TB_User WITH (NOLOCK) WHERE strUserID = @Account
IF @@ERROR <> 0 OR @UserJID = 0
BEGIN
SELECT -1
RETURN
END
DECLARE @return_value int
-- Check if user is blocked before and execute proper SP.
IF( EXISTS ( SELECT UserJID FROM _BlockedUser WITH (NOLOCK) WHERE UserJID = @UserJID AND Type = @Type))
BEGIN
DECLARE @SerialNo INT
SET @SerialNo = 0
SELECT @SerialNo = SerialNo FROM _Punishment WITH (NOLOCK) WHERE UserJID = @UserJID
EXEC @return_value = [dbo].[_UpdatePunishment] @SerialNo, @UserJID, @Type, @Executor,0, '', '', '', @Guide, @Description, @BlockStartTime, @BlockStartTime, @BlockEndTime, @BlockStartTime
END
ELSE
BEGIN
EXEC @return_value = [dbo].[_RegisterPunishment] @UserJID, @Type, @Executor,0, '', '', '', @Guide, @Description, @BlockStartTime, @BlockStartTime, @BlockEndTime, @BlockStartTime
END
SELECT @UserJID
