|
USE [SRO_VT_SHARDLOG]
GO
/****** Object: StoredProcedure [dbo].[_LockUnLockSystemByHLP] Script Date: 16/04/2016 09:25:24 ص ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[_LockUnLockSystemByHLP]
@SenderCharName varchar(64),
@Message varchar(300)
as
DECLARE
@Silk_Own int
,@JID Int
,@CharID Int
,@SecretWord varchar(300)
,@ID int
,@Type varchar(50)
,@Lock_type varchar(50)
,@Gender varchar(50)
,@DG int
,@NEW_lockID int
,@New_UnlockID int
,@RefID int
,@ItemID int
select @CharID = A.CharID ,@JID =U.UserJID from SRO_VT_SHARD.._Char a inner join SRO_VT_SHARD.._User u
On A.CharID = U.CharID where CharName16 = @SenderCharName
select @Silk_Own = silk_own from SRO_VT_ACCOUNT..SK_Silk where JID = @JID
Select @SecretWord = certificate_num from SRO_VT_ACCOUNT..TB_User where JID = @JID
-------------------------------------------------------------------
Select @ItemID = F.ItemID ,@RefID = I.RefItemID
from SRO_VT_SHARD.._Inventory F
inner join SRO_VT_SHARD.._Items I ON F.ItemID = I.ID64
Where CharID = @CharID and Slot = '13'
-------------------------------------------------------
Select @ID = ID , @DG = DG , @Gender = Gender ,@Type = Type ,@Lock_type = Lock_type
from SRO_VT_RAMY.dbo._LockUnLockSystem
where ID = @RefID
------------------------------------------------------------
SET @NEW_lockID =(SELECT ID
FROM SRO_VT_SHARDLOG.dbo._LockUnLockSystem
WHERE TYPE = @Type and Lock_Type = 'lock'
and Gender =@Gender AND DG = @DG)
SET @New_UnlockID =(SELECT ID
FROM SRO_VT_SHARDLOG.dbo._LockUnLockSystem
WHERE TYPE = @Type and Lock_Type = 'unlock'
and Gender =@Gender AND DG = @DG)
----------------------------------------------------------------------------------------------------
IF @Message = 'lock ('+(@SecretWord)+')' and @RefID not like @NEW_lockID
begin
IF not exists (select @Silk_Own where @Silk_Own >= 15)
begin
Insert into SRO_VT_SHARDLOG..iLegend_Tool (Service,Type,Target,Message,Date) values
(1,'Private',@SenderCharName,
'Lock process Cannot Completed Due to inSufficient Silk ,You need to have [15 silk] for lock. ',Getdate())
End
Else
IF (exists (select @Silk_Own where @Silk_Own >= 15))
begin
Update SRO_VT_SHARD.._Items Set RefItemID=@NEW_lockID,CreaterName='HLP' Where ID64 = @ItemID
update SRO_VT_ACCOUNT..SK_Silk set silk_own = @Silk_Own - 15 where JID = @JID
Insert into SRO_VT_SHARDLOG..iLegend_Tool (Service,Type,Target,Message,Date) values
(1,'Private',@SenderCharName,'Your item has been Locked Successfuly. ',Getdate())
Insert into SRO_VT_SHARDLOG..iLegend_Tool (Service,Type,Target,Date) values
(1,'Totown',@SenderCharName,Getdate())
End End
Else
If not Exists (Select @RefID from SRO_VT_SHARDLOG.dbo._LockUnLockSystem where ID =@RefID)
begin
Insert into SRO_VT_SHARDLOG..iLegend_Tool (Service,Type,Target,Message,Date) values
(1,'Private',@SenderCharName,'The Target Item Cannot be Lock. ',Getdate())
end
IF @Message = 'unlock ('+(@SecretWord)+')' and @RefID not like @New_UnlockID
begin
IF not exists (select @Silk_Own where @Silk_Own >= 15)
Begin
Insert into SRO_VT_SHARDLOG..iLegend_Tool (Service,Type,Target,Message,Date) values
(1,'Private',@SenderCharName,
'Unlock process Cannot Completed Due to inSufficient Silk ,You need to have [15 silk] for unlock. ',Getdate())
End
Else
IF (exists (select @Silk_Own where @Silk_Own >= 15))
begin
Update SRO_VT_SHARD.._Items Set RefItemID=@New_UnlockID,CreaterName='HLP' Where ID64 = @ItemID
update SRO_VT_ACCOUNT..SK_Silk set silk_own = @Silk_Own - 15 where JID = @JID
Insert into SRO_VT_SHARDLOG..iLegend_Tool (Service,Type,Target,Message,Date) values
(1,'Private',@SenderCharName,'Your item has been UnLocked Successfuly. ',Getdate())
Insert into SRO_VT_SHARDLOG..iLegend_Tool (Service,Type,Target,Date) values
(1,'Totown',@SenderCharName,Getdate())
End End
|