Untitled

use master
set nocount on
truncate table _Stat
IF CURSOR_STATUS('global', 'cursorProduct') >= 0
BEGIN
    CLOSE cursorProduct;
    DEALLOCATE cursorProduct;
END
DECLARE @serial bigint
DECLARE @var bigint

DECLARE cursorProduct CURSOR FOR
SELECT distinct Serial64, SUBSTRING(strDesc, CHARINDEX('[Var: ', strDesc) + LEN('[Var: '), CHARINDEX(']', strDesc, CHARINDEX('[Var: ', strDesc) + LEN('[Var: ')) - CHARINDEX('[Var: ', strDesc) - LEN('[Var: ')) AS ExtractedValue
FROM SRO_VT_LOG.._LogEventItem WHERE Operation = 114 and strDesc like '0,MOB%Var%' AND ItemRefID IN (SELECT ID FROM SRO_VT_SHARD.dbo._RefObjCommon WHERE CodeName128 LIKE 'ITEM_%' and TypeID1 =3 and TypeID2 = 1 and TypeID3 < 13 and Service = 1)

OPEN cursorProduct
FETCH NEXT FROM cursorProduct
      INTO @serial, @var
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Variance BIGINT = @var,@serial2 BIGINT = @serial,@Counter INT = 0;
WHILE @Variance > 0
BEGIN
    INSERT Tmp(Value)
    VALUES ((@Variance & 0x1F) * 100 / 31);
    SET @Counter = 5;
    WHILE @Counter <> 0
    BEGIN
        SET @Variance = (@Variance - (@Variance & 1)) / 2;
        SET @Counter -= 1;
    END;
END;
    IF (SELECT COUNT(*)FROM Tmp) = 7--Vu khi
    BEGIN
	    INSERT _Stat (Serial,Description, Value)
    VALUES (@serial2,N'Công vật lý', (SELECT Value FROM  Tmp WHERE ID = 5))
         , (@serial2,N'Công phép', (SELECT Value FROM  Tmp WHERE ID = 6))
         , (@serial2,N'Độ bền', (SELECT Value FROM  Tmp WHERE ID = 1))
         , (@serial2,N'Tỉ lệ đánh', (SELECT Value FROM  Tmp WHERE ID = 4))
         , (@serial2,N'Chí mạng', (SELECT Value FROM  Tmp WHERE ID = 7))
         , (@serial2,N'Gia tăng vật lý', (SELECT Value FROM  Tmp WHERE ID = 2))
         , (@serial2,N'Gia tăng phép', (SELECT Value FROM  Tmp WHERE ID = 3));
    END;
	
    IF (SELECT COUNT(*)FROM Tmp) = 6--Giap/khien
    BEGIN
    INSERT _Stat (Serial,Description, Value)
    VALUES (@serial2,N'Thủ vật lý', (SELECT Value FROM Tmp WHERE ID = 4))
         , (@serial2,N'Thủ phép', (SELECT Value FROM Tmp WHERE ID = 5))
         , (@serial2,N'Độ bền', (SELECT Value FROM Tmp WHERE ID = 1))
         , (@serial2,N'Tỉ lệ đỡ', (SELECT Value FROM Tmp WHERE ID = 6))
         , (@serial2,N'Gia tăng vật lý', (SELECT Value FROM Tmp WHERE ID = 2))
         , (@serial2,N'Gia tăng phép', (SELECT Value FROM Tmp WHERE ID = 3));
    END;
	    IF (SELECT COUNT(*)FROM Tmp) = 2--TS
    BEGIN
    INSERT _Stat (Serial,Description, Value)
    VALUES (@serial2,N'Hấp thụ vật lý', (SELECT Value FROM Tmp WHERE ID = 1))
         , (@serial2,N'Hấp thụ phép', (SELECT Value FROM Tmp WHERE ID = 2))
    END;
	truncate table Tmp
    FETCH NEXT FROM cursorProduct
          INTO @serial, @var
END

CLOSE cursorProduct
DEALLOCATE cursorProduct