Jayden
03-10-2016, 07:44 PM
http://i.epvpimg.com/5rfrc.png
بعد غياب طويل عن المنتدى
حبيت ارجعلكم بموضوع جديد .. بصيت على المواضيع اللى موجوده
لقيت موضوع عن سيستم بيدى Reward لما ال Player يعلى فى ال Job Level
ولكن .. السيستم ده بيعتمد على انه يبعت Message لنفسه عشان ياخد ال Reward
فقررت انى اعدل على السيستم ده واخليه ياخد ال Reward بدون Message
يعنى Automatic تماما
ومش بس كده دلوقتى انت تقدر تحدد ال Reward اللى انت عايزها
سواء لو Item هتحدد ايه الى Item اللى انت عايزها
او لو Silk هتحدد كام عدد ال Silk
وتقدر تخليه الاتنين مع بعض برضو
نخش فى المفيد
أولا:
أعمل Execute ل دى
USE SRO_VT_SHARD
GO
/****** Object: Trigger [dbo].[_JobLvlReward] - Author : Jayden ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[_JobLvlReward]
ON [dbo].[_CharTrijob]
AFTER Update
AS
BEGIN
DECLARE @Item varchar(max),@Lvl2ItemQuantity int,@Lvl2SilkQuantity int,@RewardType varchar(4)
-------------------------------------------
SET @RewardType = 'Both' -- Choose your reward type (Silk - Item - Both)
SET @Item = 'ITEM_ETC_ARENA_COIN' -- Put your Item codename here.
SET @Lvl2SilkQuantity = '5' -- Silk Reward of lvl 2 that will be doubled on each lvl.
SET @Lvl2ItemQuantity = '5' -- Item Reward of lvl 2 that will be doubled on each lvl.
--------------------------------------------
/*Dont Touch Anything here if you don't know what you doing */
/* Declares */
DECLARE @ItemID int = (SELECT ID FROM _RefObjCommon WHERE CodeName128 = @Item)
, @CharID int =(SELECT TOP 1 CharID FROM inserted)
, @Lvl int =(SELECT TOP 1 Level FROM inserted),@Quantity int,@Quantitysilk int
DECLARE @UserID int =(SELECT UserJID FROM _User WHERE CharID = @CharID)
/* Setting Quantity to be rewarded ( Doubles on each level ) */
SET @Quantity = (@lvl - 1) * @Lvl2ItemQuantity
SET @Quantitysilk = (@lvl - 1) * @Lvl2SilkQuantity
/* Rewarding New Comer */
IF @lvl = 1 BEGIN RETURN END
IF NOT EXISTS (SELECT * FROM SRO_VT_SHARDLOG.._JobLvlRewardCheck WHERE CharID = @CharID and Lvl = @Lvl)
BEGIN
IF @RewardType = 'Item'
BEGIN
EXEC _RoyalAddEquipment @CharID,@ItemID,@Quantity,0
END
ELSE IF @RewardType = 'Silk'
BEGIN
EXEC SRO_VT_ACCOUNT.CGI.[CGI_WebPurchaseSilkPServer] @UserID,@Quantitysilk
END
ELSE IF @RewardType ='Both'
BEGIN
EXEC _RoyalAddEquipment @CharID,@ItemID,@Quantity,0
EXEC SRO_VT_ACCOUNT.CGI.[CGI_WebPurchaseSilkPServer] @UserID,@Quantitysilk
END
INSERT SRO_VT_SHARDLOG.._JobLvlRewardCheck VALUES (@CharID,@Lvl)
END
END
و دى
USE [SRO_VT_SHARDLOG]
GO
/****** Object: Table [dbo].[_JobLvlRewardCheck] Script Date: 10/3/2016 6:40:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[_JobLvlRewardCheck](
[CharID] [int] NULL,
[Lvl] [int] NULL
) ON [PRIMARY]
GO
ثانيا:
ده ال Procedure اللى بيدى Silk لومش عندك اعمله Execute
USE [SRO_VT_ACCOUNT]
GO
/****** Object: StoredProcedure [CGI].[CGI_WebPurchaseSilkPServer] Script Date: 10/3/2016 6:37:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [CGI].[CGI_WebPurchaseSilkPServer]
@UserID INT,
@NumSilk INT
as
DECLARE @SilkRemain INT
DECLARE @OrderID INT
set @SilkRemain = 0
--DECLARE @PointRemain INT
-- BEGIN TRANSACTION
IF( not exists( SELECT * from SK_Silk where JID = @UserID))
BEGIN
INSERT SK_Silk(JID,silk_own,silk_gift,silk_point)VALUES(@ UserID,@NumSilk,0,0)
--UPDATE Silk Own
END
ELSE
BEGIN
SET @SilkRemain = CGI.getSilkOwn(@UserID)
UPDATE SK_Silk SET silk_own = silk_own + @NumSilk WHERE JID = @UserID
--INSERT Silk Own
END
SELECT @OrderID = cast(MAX(OrderNumber)+1 as INT) FROM SK_SilkBuyList WHERE UserJID = @UserID
IF(@OrderID is NULL)
BEGIN
set @OrderID = 0
END
INSERT SK_SilkBuyList(UserJID,Silk_Type,Silk_Reason,Silk_ Offset,Silk_Remain,ID,BuyQuantity,SlipPaper,RegDat e,OrderNumber) VALUES( @UserID,0,0,@NumSilk,@SilkRemain + @NumSilk,0,1,"User Purchase Silk from VDC-Net2E Billing System",GETDATE(),@OrderID)
INSERT SK_SilkChange_BY_Web(JID,silk_remain,silk_offset,s ilk_type,reason) VALUES(@UserID,@SilkRemain + @NumSilk,@NumSilk,0,0)
IF (@@error <> 0 or @@rowcount = 0)
BEGIN
SELECT Result = "FAIL"
-- ROLLBACK TRANSACTION
RETURN
END
SELECT Result = "SUCCESS"
-- COMMIT TRANSACTION
RETURN
SET QUOTED_IDENTIFIER OFF
ثالثا:
ده ال Procedureاللى بيدى Item لو مش عندك اعمله Execute
USE [SRO_VT_SHARD]
GO
/****** Object: StoredProcedure [dbo].[_RoyalAddEquipment] Script Date: 10/3/2016 6:38:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[_RoyalAddEquipment]
@CharID INT,
@ItemID INT,
@Amount INT,
@OptLvl INT
AS
SET XACT_ABORT ON
DECLARE @FreeSlot TINYINT,
@Link INT,
@TID1 TINYINT,
@TID2 TINYINT,
@TID3 TINYINT,
@TID4 TINYINT,
@MaxStack INT,
@LatestItemSN BIGINT,
@NewItemID BIGINT = 0;
SELECT @Link = R.Link,
@TID1 = TypeID1,
@TID2 = TypeID2,
@TID3 = TypeID3,
@TID4 = TypeID4,
@Amount = CASE
WHEN TypeID1 = 3 AND TypeID2 = 1
THEN Dur_L
WHEN TypeID1 = 3 AND TypeID2 = 2 AND TypeID3 = 1 AND TypeID4 IN(1,2)
THEN 0
WHEN @Amount > RI.MaxStack OR @Amount < 0
THEN MaxStack
ELSE @Amount
END
FROM _RefObjCommon R
JOIN _RefObjItem RI ON R.Link = RI.ID
WHERE R.ID = @ItemID
IF @Link IS NULL OR @Amount IS NULL
BEGIN
PRINT 'Item doesn''t exist/Amount if invalid'
RETURN
END
IF NOT EXISTS(SELECT * FROM _Char WITH (NOLOCK) WHERE CharID = @CharID)
BEGIN
PRINT 'Char doesn''t exist'
RETURN
END
SELECT TOP 1 @FreeSlot = Slot
FROM _Inventory
WHERE CharID = @CharID
AND Slot > 12
AND ItemID = 0
IF @FreeSlot IS NULL
BEGIN
PRINT 'Inventory is full'
RETURN
END
DECLARE @MagNum int = '0'
,@Mag1 bigint = '0'
,@Mag2 bigint = '0'
,@Mag3 bigint = '0'
,@Mag4 bigint = '0'
,@Mag5 bigint = '0'
,@Mag6 bigint = '0'
,@Mag7 bigint = '0'
,@Var bigint = '0'
IF @ItemID BETWEEN '41861' AND '41910'
BEGIN
SET @MagNum = '7'
SET @Mag1 = '65'
SET @Mag2 = '214748364803'
SET @Mag3 = '858993459290'
SET @Mag4 = '429496729714'
SET @Mag5 = '257698037862'
SET @Mag6 = '30064771150'
SET @Mag7 = '30064771144'
SET @Var = '15517301198'
END
IF @TID1 = 3 AND @TID2 = 1 AND @TID3 = 13 AND @TID4 = 4
BEGIN
SET @MagNum = '2'
SET @Mag1 = '8589934825'
SET @Mag2 = '8589934824'
SET @Mag3 = '0'
SET @Mag4 = '0'
SET @Mag5 = '0'
SET @Mag6 = '0'
SET @Mag7 = '0'
SET @Var = '0'
END
BEGIN TRAN
BEGIN TRY
UPDATE _LatestItemSerial SET LatestItemSerial += 1;
SELECT @LatestItemSN = LatestItemSerial
FROM _LatestItemSerial
WITH (UPDLOCK)
INSERT INTO _Items (RefItemID, OptLevel, Data, Variance,MagParamNum,MagParam1,MagParam2,MagParam3 ,MagParam4,MagParam5,MagParam6,MagParam7, Serial64)
VALUES (@ItemID, @OptLvl, @Amount, @Var,@MagNum,@Mag1,@Mag2,@Mag3,@Mag4,@Mag5,@Mag6,@ Mag7, @LatestItemSN)
SELECT @NewItemID = @@IDENTITY
UPDATE _Inventory SET ItemID = @NewItemID WHERE CharID = @CharID AND Slot = @FreeSlot
INSERT INTO _ItemPool (InUse, ItemID)
VALUES (1,@NewItemID)
IF @NewItemID = 0
BEGIN
ROLLBACK TRAN
PRINT 'Huge Error'
RETURN
END
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT 'Dafuq happened'
RETURN
END CATCH
COMMIT TRAN
ودى Table خاصة بيه
USE [SRO_VT_SHARD]
GO
/****** Object: Table [dbo].[_ItemPool] Script Date: 10/3/2016 6:39:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[_ItemPool](
[ItemID] [bigint] NOT NULL,
[InUse] [tinyint] NOT NULL,
CONSTRAINT [PK__ItemPool] PRIMARY KEY NONCLUSTERED
(
[ItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[_ItemPool] WITH CHECK ADD CONSTRAINT [FK__ItemPool__Items] FOREIGN KEY([ItemID])
REFERENCES [dbo].[_Items] ([ID64])
GO
ALTER TABLE [dbo].[_ItemPool] CHECK CONSTRAINT [FK__ItemPool__Items]
GO
يارب يكون مفيد
لو فى اى استفسار انا موجود ..
بعد غياب طويل عن المنتدى
حبيت ارجعلكم بموضوع جديد .. بصيت على المواضيع اللى موجوده
لقيت موضوع عن سيستم بيدى Reward لما ال Player يعلى فى ال Job Level
ولكن .. السيستم ده بيعتمد على انه يبعت Message لنفسه عشان ياخد ال Reward
فقررت انى اعدل على السيستم ده واخليه ياخد ال Reward بدون Message
يعنى Automatic تماما
ومش بس كده دلوقتى انت تقدر تحدد ال Reward اللى انت عايزها
سواء لو Item هتحدد ايه الى Item اللى انت عايزها
او لو Silk هتحدد كام عدد ال Silk
وتقدر تخليه الاتنين مع بعض برضو
نخش فى المفيد
أولا:
أعمل Execute ل دى
USE SRO_VT_SHARD
GO
/****** Object: Trigger [dbo].[_JobLvlReward] - Author : Jayden ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[_JobLvlReward]
ON [dbo].[_CharTrijob]
AFTER Update
AS
BEGIN
DECLARE @Item varchar(max),@Lvl2ItemQuantity int,@Lvl2SilkQuantity int,@RewardType varchar(4)
-------------------------------------------
SET @RewardType = 'Both' -- Choose your reward type (Silk - Item - Both)
SET @Item = 'ITEM_ETC_ARENA_COIN' -- Put your Item codename here.
SET @Lvl2SilkQuantity = '5' -- Silk Reward of lvl 2 that will be doubled on each lvl.
SET @Lvl2ItemQuantity = '5' -- Item Reward of lvl 2 that will be doubled on each lvl.
--------------------------------------------
/*Dont Touch Anything here if you don't know what you doing */
/* Declares */
DECLARE @ItemID int = (SELECT ID FROM _RefObjCommon WHERE CodeName128 = @Item)
, @CharID int =(SELECT TOP 1 CharID FROM inserted)
, @Lvl int =(SELECT TOP 1 Level FROM inserted),@Quantity int,@Quantitysilk int
DECLARE @UserID int =(SELECT UserJID FROM _User WHERE CharID = @CharID)
/* Setting Quantity to be rewarded ( Doubles on each level ) */
SET @Quantity = (@lvl - 1) * @Lvl2ItemQuantity
SET @Quantitysilk = (@lvl - 1) * @Lvl2SilkQuantity
/* Rewarding New Comer */
IF @lvl = 1 BEGIN RETURN END
IF NOT EXISTS (SELECT * FROM SRO_VT_SHARDLOG.._JobLvlRewardCheck WHERE CharID = @CharID and Lvl = @Lvl)
BEGIN
IF @RewardType = 'Item'
BEGIN
EXEC _RoyalAddEquipment @CharID,@ItemID,@Quantity,0
END
ELSE IF @RewardType = 'Silk'
BEGIN
EXEC SRO_VT_ACCOUNT.CGI.[CGI_WebPurchaseSilkPServer] @UserID,@Quantitysilk
END
ELSE IF @RewardType ='Both'
BEGIN
EXEC _RoyalAddEquipment @CharID,@ItemID,@Quantity,0
EXEC SRO_VT_ACCOUNT.CGI.[CGI_WebPurchaseSilkPServer] @UserID,@Quantitysilk
END
INSERT SRO_VT_SHARDLOG.._JobLvlRewardCheck VALUES (@CharID,@Lvl)
END
END
و دى
USE [SRO_VT_SHARDLOG]
GO
/****** Object: Table [dbo].[_JobLvlRewardCheck] Script Date: 10/3/2016 6:40:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[_JobLvlRewardCheck](
[CharID] [int] NULL,
[Lvl] [int] NULL
) ON [PRIMARY]
GO
ثانيا:
ده ال Procedure اللى بيدى Silk لومش عندك اعمله Execute
USE [SRO_VT_ACCOUNT]
GO
/****** Object: StoredProcedure [CGI].[CGI_WebPurchaseSilkPServer] Script Date: 10/3/2016 6:37:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [CGI].[CGI_WebPurchaseSilkPServer]
@UserID INT,
@NumSilk INT
as
DECLARE @SilkRemain INT
DECLARE @OrderID INT
set @SilkRemain = 0
--DECLARE @PointRemain INT
-- BEGIN TRANSACTION
IF( not exists( SELECT * from SK_Silk where JID = @UserID))
BEGIN
INSERT SK_Silk(JID,silk_own,silk_gift,silk_point)VALUES(@ UserID,@NumSilk,0,0)
--UPDATE Silk Own
END
ELSE
BEGIN
SET @SilkRemain = CGI.getSilkOwn(@UserID)
UPDATE SK_Silk SET silk_own = silk_own + @NumSilk WHERE JID = @UserID
--INSERT Silk Own
END
SELECT @OrderID = cast(MAX(OrderNumber)+1 as INT) FROM SK_SilkBuyList WHERE UserJID = @UserID
IF(@OrderID is NULL)
BEGIN
set @OrderID = 0
END
INSERT SK_SilkBuyList(UserJID,Silk_Type,Silk_Reason,Silk_ Offset,Silk_Remain,ID,BuyQuantity,SlipPaper,RegDat e,OrderNumber) VALUES( @UserID,0,0,@NumSilk,@SilkRemain + @NumSilk,0,1,"User Purchase Silk from VDC-Net2E Billing System",GETDATE(),@OrderID)
INSERT SK_SilkChange_BY_Web(JID,silk_remain,silk_offset,s ilk_type,reason) VALUES(@UserID,@SilkRemain + @NumSilk,@NumSilk,0,0)
IF (@@error <> 0 or @@rowcount = 0)
BEGIN
SELECT Result = "FAIL"
-- ROLLBACK TRANSACTION
RETURN
END
SELECT Result = "SUCCESS"
-- COMMIT TRANSACTION
RETURN
SET QUOTED_IDENTIFIER OFF
ثالثا:
ده ال Procedureاللى بيدى Item لو مش عندك اعمله Execute
USE [SRO_VT_SHARD]
GO
/****** Object: StoredProcedure [dbo].[_RoyalAddEquipment] Script Date: 10/3/2016 6:38:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[_RoyalAddEquipment]
@CharID INT,
@ItemID INT,
@Amount INT,
@OptLvl INT
AS
SET XACT_ABORT ON
DECLARE @FreeSlot TINYINT,
@Link INT,
@TID1 TINYINT,
@TID2 TINYINT,
@TID3 TINYINT,
@TID4 TINYINT,
@MaxStack INT,
@LatestItemSN BIGINT,
@NewItemID BIGINT = 0;
SELECT @Link = R.Link,
@TID1 = TypeID1,
@TID2 = TypeID2,
@TID3 = TypeID3,
@TID4 = TypeID4,
@Amount = CASE
WHEN TypeID1 = 3 AND TypeID2 = 1
THEN Dur_L
WHEN TypeID1 = 3 AND TypeID2 = 2 AND TypeID3 = 1 AND TypeID4 IN(1,2)
THEN 0
WHEN @Amount > RI.MaxStack OR @Amount < 0
THEN MaxStack
ELSE @Amount
END
FROM _RefObjCommon R
JOIN _RefObjItem RI ON R.Link = RI.ID
WHERE R.ID = @ItemID
IF @Link IS NULL OR @Amount IS NULL
BEGIN
PRINT 'Item doesn''t exist/Amount if invalid'
RETURN
END
IF NOT EXISTS(SELECT * FROM _Char WITH (NOLOCK) WHERE CharID = @CharID)
BEGIN
PRINT 'Char doesn''t exist'
RETURN
END
SELECT TOP 1 @FreeSlot = Slot
FROM _Inventory
WHERE CharID = @CharID
AND Slot > 12
AND ItemID = 0
IF @FreeSlot IS NULL
BEGIN
PRINT 'Inventory is full'
RETURN
END
DECLARE @MagNum int = '0'
,@Mag1 bigint = '0'
,@Mag2 bigint = '0'
,@Mag3 bigint = '0'
,@Mag4 bigint = '0'
,@Mag5 bigint = '0'
,@Mag6 bigint = '0'
,@Mag7 bigint = '0'
,@Var bigint = '0'
IF @ItemID BETWEEN '41861' AND '41910'
BEGIN
SET @MagNum = '7'
SET @Mag1 = '65'
SET @Mag2 = '214748364803'
SET @Mag3 = '858993459290'
SET @Mag4 = '429496729714'
SET @Mag5 = '257698037862'
SET @Mag6 = '30064771150'
SET @Mag7 = '30064771144'
SET @Var = '15517301198'
END
IF @TID1 = 3 AND @TID2 = 1 AND @TID3 = 13 AND @TID4 = 4
BEGIN
SET @MagNum = '2'
SET @Mag1 = '8589934825'
SET @Mag2 = '8589934824'
SET @Mag3 = '0'
SET @Mag4 = '0'
SET @Mag5 = '0'
SET @Mag6 = '0'
SET @Mag7 = '0'
SET @Var = '0'
END
BEGIN TRAN
BEGIN TRY
UPDATE _LatestItemSerial SET LatestItemSerial += 1;
SELECT @LatestItemSN = LatestItemSerial
FROM _LatestItemSerial
WITH (UPDLOCK)
INSERT INTO _Items (RefItemID, OptLevel, Data, Variance,MagParamNum,MagParam1,MagParam2,MagParam3 ,MagParam4,MagParam5,MagParam6,MagParam7, Serial64)
VALUES (@ItemID, @OptLvl, @Amount, @Var,@MagNum,@Mag1,@Mag2,@Mag3,@Mag4,@Mag5,@Mag6,@ Mag7, @LatestItemSN)
SELECT @NewItemID = @@IDENTITY
UPDATE _Inventory SET ItemID = @NewItemID WHERE CharID = @CharID AND Slot = @FreeSlot
INSERT INTO _ItemPool (InUse, ItemID)
VALUES (1,@NewItemID)
IF @NewItemID = 0
BEGIN
ROLLBACK TRAN
PRINT 'Huge Error'
RETURN
END
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT 'Dafuq happened'
RETURN
END CATCH
COMMIT TRAN
ودى Table خاصة بيه
USE [SRO_VT_SHARD]
GO
/****** Object: Table [dbo].[_ItemPool] Script Date: 10/3/2016 6:39:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[_ItemPool](
[ItemID] [bigint] NOT NULL,
[InUse] [tinyint] NOT NULL,
CONSTRAINT [PK__ItemPool] PRIMARY KEY NONCLUSTERED
(
[ItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[_ItemPool] WITH CHECK ADD CONSTRAINT [FK__ItemPool__Items] FOREIGN KEY([ItemID])
REFERENCES [dbo].[_Items] ([ID64])
GO
ALTER TABLE [dbo].[_ItemPool] CHECK CONSTRAINT [FK__ItemPool__Items]
GO
يارب يكون مفيد
لو فى اى استفسار انا موجود ..