Vsro Servere Paket Box Sistemi Ekleme

USE [SRO_VT_LOG]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PICK_Common](
    [ID] INT IDENTITY(1,1) NOT NULL,
    [GroupID] INT NOT NULL,
    [GroupCodeName] VARCHAR(MAX) NOT NULL,
    [ItemCodeName] VARCHAR(MAX) NOT NULL,
    [ItemCount] INT NOT NULL,
    [ItemPlus] INT NOT NULL,
    [Ratio] DECIMAL(4,3) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[PICK_Common] ADD  CONSTRAINT [DF_PICK_Common_ItemCount]  DEFAULT (1) FOR [ItemCount]
GO

ALTER TABLE [dbo].[PICK_Common] ADD  CONSTRAINT [DF_PICK_Common_ItemPlus]  DEFAULT (0) FOR [ItemPlus]
GO

ALTER TABLE [dbo].[PICK_Common] ADD  CONSTRAINT [DF_PICK_Common_Ratio]  DEFAULT (0) FOR [Ratio]
GO

USE [SRO_VT_LOG]
GO

/****** Object:  StoredProcedure [dbo].[_AddLogItem]    Script Date: 7.02.2019 11:33:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER  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
AS

----==========================================================================================================----
    ------------------------------------------- PICK Selection -----------------------------------------------
    IF(@Operation=41 AND @slot_To=255 AND (@ItemRefID IN (ID_BOX_1,ID_BOX_2,ID_BOX_3,ID_BOX_4,ID_BOX_5,ID_BOX_6,ID_BOX_7)))
    BEGIN
        DECLARE @CharName_PICK VARCHAR(64)=(SELECT CharName16 FROM [SRO_VT_SHARD].[dbo].[_Char] WITH (NOLOCK) WHERE CharID=@CharID);
        DECLARE @GoupID INT
        DECLARE @ItemCodeName_PICK VARCHAR(MAX), @ItemCount_PICK INT, @ItemPlus_PICK INT

        SELECT @GoupID=
            CASE
            WHEN @ItemRefID=ID_BOX_1 THEN 1 --Pick Of Group1
            WHEN @ItemRefID=ID_BOX_2 THEN 2 --Pick of Group2
            WHEN @ItemRefID=ID_BOX_3 THEN 3 --Pick of Group3
            WHEN @ItemRefID=ID_BOX_4 THEN 4 --Pick of Group4
            WHEN @ItemRefID=ID_BOX_5 THEN 5 --Pick of Group5
            WHEN @ItemRefID=ID_BOX_6 THEN 6 --Pick of Group6
            WHEN @ItemRefID=ID_BOX_7 THEN 7 --Pick Of Group7
            ELSE 0
            END

        DECLARE @Random FLOAT = RAND()

        ;WITH OddIntervals AS
        (
        SELECT
        O.*,
        OddStart = CONVERT(DECIMAL(4,3), SUM(O.[Ratio]) OVER (ORDER BY O.[ID]) - O.[Ratio]),
        OddsEnd = CONVERT(DECIMAL(4,3), SUM(O.[Ratio]) OVER (ORDER BY O.[ID]))
        FROM PICK_Common AS O WHERE
        GroupID=@GoupID
        )
        SELECT @ItemCodeName_PICK=O.ItemCodeName , @ItemCount_PICK=O.ItemCount , @ItemPlus_PICK=O.ItemPlus FROM OddIntervals AS O WHERE
        GroupID=@GoupID            AND
        @Random > O.OddStart    AND
        @Random <= O.OddsEnd

        EXEC SRO_VT_SHARD.dbo._ADD_ITEM_EXTERN @CharName_PICK,@ItemCodeName_PICK,@ItemCount_PICK,@ItemPlus_PICK

    END
----==========================================================================================================----

    DECLARE @len_pos INT, @len_desc INT
    SET @len_pos = LEN(@EventPos)
    SET @len_desc = LEN(@strDesc)
    IF (@len_pos > 0 AND @len_desc > 0)
    BEGIN
        RETURN -1
    END
    ELSE IF (@len_pos > 0 AND @len_desc = 0)
    BEGIN
        RETURN -1
    END
    ELSE IF (@len_pos = 0 AND @len_desc > 0)
    BEGIN
        RETURN -1
    END
    ELSE IF (@len_pos = 0 AND @len_desc = 0)
    BEGIN
        RETURN -1
    END
    IF (@Operation = 35)
    BEGIN
        RETURN -1
    END