[SCRIPT] Блок статистики семи печатей

Тема в разделе "Веб скрипты (WEB)", создана пользователем root, 1 окт 2014.

  1. root

    root Administrator Команда форума Administrator Moderator Developers Team

    Регистрация:
    23 авг 2014
    Сообщения:
    253
    Симпатии:
    56
    Баллы:
    11
    Получаем текущий раунд, статус евента и статус печатей:
    Код:
    SELECT	@status = status, @round = round_number, @ssq1 = seal1, @ssq2 = seal2, @ssq3 = seal3
    FROM
    	[lin2world].[dbo].[ssq_data]
    WHERE
    	round_number = (
    		SELECT
    			max(round_number)
    		FROM
    			[lin2world_loa].[dbo].[ssq_data]
    	)
    Получаем кол-во поинтов за сдачу SealStone:
    Код:
    SELECT	@dusk_cnt = a.collected_point, @dawn_cnt = b.collected_point
    FROM
    	[lin2world].[dbo].[ssq_join_data] AS a
    	JOIN [lin2world].[dbo].[ssq_join_data] AS b ON a.round_number = b.round_number
    WHERE
    	a.round_number= @round
    	AND a.type = 2
    	AND b.type = 1
    Перевод от кол-ва собранного в поинты для вывода:
    Код:
    SET @point = ( @dusk_cnt + @dawn_cnt ) / 500SET @dusk_point = @dusk_cnt / @point
    SET @dawn_point = @dawn_cnt / @point
    Если учитывать еще и фестиваль тьмы, то чуть веселее.
    Код:
    CREATE TABLE #room_points (room_no INT, points INT);INSERT INTO #room_points (room_no, points) VALUES (1,60), (2,70), (3,100), (4,120), (5,150);
    
    
    SELECT
    	@dusk_fest = Sum(point)
    FROM
    	#room_points
    WHERE
    	room_no IN (
    		SELECT
    			room_no
    		FROM
    			[lin2world].[dbo].[time_attack_record]
    		WHERE
    			ssq_round = @round
    			AND record_type = 2
    			AND ssq_part = 2
    		GROUP BY
    			room_no
    	)
    
    
    SELECT
    	@dawn_fest = Sum(point)
    FROM
    	#room_points
    WHERE
    	room_no IN (
    		SELECT
    			room_no
    		FROM
    			[lin2world].[dbo].[time_attack_record]
    		WHERE
    			ssq_round = @round
    			AND record_type = 2
    			AND ssq_part = 1
    		GROUP BY
    			room_no
    	)
    
    
    DELETE TABLE #room_points;
    
    
    IF @dusk_fest IS NULL BEGIN SET @dusk_fest = 0; END
    IF @dawn_fest IS NULL BEGIN SET @dawn_fest = 0; END
    
    
    SET @dusk_point =  @dusk_point + @dusk_fest
    SET @dawn_point = @dawn_point + @dawn_fest