Deletar item do servidor / suporte à reembolso

[WoLfulus]

ATENÇÃO: Antes de executar a query abaixo para instalar as tabelas e procedures, você tem que alterar algumas coisas para "bater" com as configurações do seu servidor. Eu marquei em AZUL o que você precisa configurar de acordo com o seu servidor.

• O script está configurado para a coluna "Cash" na MEMB_INFO como local para depositar os cashs retornados ao usuário
• O script está configurado para utilizar a tabela "ExWarehouse" como local dos baús extras. (alguns servidores a tabela é ExtWarehouse por exemplo)

Suporte para versão 97d somente.

Instalação
Só se executa isso UMA VEZ no banco de dados do servidor. Isso é só a instalação, o modo de utilizar está lá em baixo.

--
-- Tabela de items a serem removidos
--

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WOLF_ITEMS]') AND type in (N'U'))
DROP TABLE [dbo].[WOLF_ITEMS]
GO

CREATE TABLE WOLF_ITEMS 
( 
	ItemSection INT, 
	ItemIndex INT, 
	ItemPrice INT DEFAULT (0)
)
GO

--
-- Função de inicialização
--

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WOLF_INITIALIZE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WOLF_INITIALIZE]
GO

CREATE PROCEDURE WOLF_INITIALIZE AS
BEGIN
	SET NOCOUNT ON
	DELETE FROM WOLF_ITEMS
END
GO

--
-- Registro de items a serem removidos
--

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WOLF_REMOVE_ITEM]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WOLF_REMOVE_ITEM]
GO

CREATE PROCEDURE WOLF_REMOVE_ITEM 
	@sec AS INT, 
	@id AS INT,
	@price AS INT
AS 
BEGIN
	SET NOCOUNT ON
	INSERT INTO WOLF_ITEMS (ItemSection, ItemIndex, ItemPrice) VALUES (@sec, @id, @price)
END
GO

--
-- Remoção de item do bau
--

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WOLF_WAREHOUSE_REMOVE]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[WOLF_WAREHOUSE_REMOVE]
GO

CREATE FUNCTION WOLF_WAREHOUSE_REMOVE
(
	@index INT,
	@items VARBINARY(1200)
)
RETURNS VARBINARY(1200)
AS 
BEGIN
	DECLARE @temp INT
	DECLARE @query NVARCHAR(MAX)
	DECLARE @ret VARBINARY(1200)
	
	IF @index = 0 
	BEGIN
		SELECT @ret = 0xFFFFFFFFFFFFFFFFFFFF + SUBSTRING(@items, 11, 1190)
	END
	ELSE
	BEGIN
		IF @index = 119
		BEGIN
			SELECT @ret = SUBSTRING(@items, 1, 1190) + 0xFFFFFFFFFFFFFFFFFFFF
		END
		ELSE
		BEGIN
			SELECT @ret = SUBSTRING(@items, 1, @index * 10) + 0xFFFFFFFFFFFFFFFFFFFF + SUBSTRING(@items, (@index + 1) * 10 + 1, 1200 - (@index + 1) * 10)
		END
	END
	
	RETURN @ret
END
GO

--
-- Processamento de warehouses
--

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WOLF_PROCESS_WAREHOUSE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WOLF_PROCESS_WAREHOUSE]
GO

CREATE PROCEDURE WOLF_PROCESS_WAREHOUSE
	@itemlist AS VARBINARY(1200),
	@outItems AS VARBINARY(1200) OUTPUT,
	@outPrice AS INT OUTPUT
AS
BEGIN 
	DECLARE @i AS INT
	DECLARE @item AS VARBINARY(10)
	DECLARE @byte1 AS INT
	DECLARE @byte2 AS INT
	DECLARE @index AS INT
	DECLARE @section AS INT
	DECLARE @items AS VARBINARY(1200)
	DECLARE @price INT
	DECLARE @totalPrice INT
	
	SET @totalPrice = 0
	
	SET @items = @itemlist
	SET @i = 0
	
	WHILE @i < 120
	BEGIN
		SELECT @byte1 = CONVERT(INTEGER, SUBSTRING(@items, @i * 10 + 1, 1)),
			   @byte2 = CONVERT(INTEGER, SUBSTRING(@items, @i * 10 + 8, 1)), 		   
			   @item = SUBSTRING(@items, @i * 10 + 1, 10)
			   
		IF @item <> 0xFFFFFFFFFFFFFFFFFFFF 
		BEGIN
			SET @index = (@byte1 & 0x1F)
			SET @section = ((@byte1 & 0xE0) / POWER(2, 5)) | ((@byte2 & 0x80) / POWER(2, 4))
		
			IF EXISTS(SELECT * FROM WOLF_ITEMS WHERE ItemSection = @section AND ItemIndex = @index)
			BEGIN
				SET @items = [dbo].[WOLF_WAREHOUSE_REMOVE] (@i, @items)
				SELECT @price = ItemPrice FROM WOLF_ITEMS WHERE ItemSection = @section AND ItemIndex = @index
				SET @totalPrice = @totalPrice + @price
			END
		END

		SET @i = @i + 1
	END

	SET @outItems = @items 
	SET @outPrice = @totalPrice
END
GO

--
-- Remoção de item do inventário
--

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WOLF_INVENTORY_REMOVE]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[WOLF_INVENTORY_REMOVE]
GO

CREATE FUNCTION WOLF_INVENTORY_REMOVE
(
	@index INT,
	@items VARBINARY(760)
)
RETURNS VARBINARY(760)
AS 
BEGIN
	DECLARE @temp INT
	DECLARE @query NVARCHAR(MAX)
	DECLARE @ret VARBINARY(760)
	
	IF @index = 0 
	BEGIN
		SELECT @ret = 0xFFFFFFFFFFFFFFFFFFFF + SUBSTRING(@items, 11, 750)
	END
	ELSE
	BEGIN
		IF @index = 75
		BEGIN
			SELECT @ret = SUBSTRING(@items, 1, 750) + 0xFFFFFFFFFFFFFFFFFFFF
		END
		ELSE
		BEGIN
			SELECT @ret = SUBSTRING(@items, 1, @index * 10) + 0xFFFFFFFFFFFFFFFFFFFF + SUBSTRING(@items, (@index + 1) * 10 + 1, 760 - (@index + 1) * 10)
		END
	END
	
	RETURN @ret
END
GO

--
-- Processamento de inventário
-- 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WOLF_PROCESS_INVENTORY]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WOLF_PROCESS_INVENTORY]
GO

CREATE PROCEDURE WOLF_PROCESS_INVENTORY 
	@itemlist AS VARBINARY(760),
	@outItems AS VARBINARY(760) OUTPUT,
	@outPrice AS INT OUTPUT
AS
BEGIN 
	
	DECLARE @i AS INT
	DECLARE @item AS VARBINARY(10)
	DECLARE @byte1 AS INT
	DECLARE @byte2 AS INT
	DECLARE @index AS INT
	DECLARE @section AS INT
	DECLARE @items AS VARBINARY(760)
	DECLARE @price INT
	DECLARE @totalPrice INT
	
	SET @totalPrice = 0
	
	SET @items = @itemlist
	SET @i = 0
	
	WHILE @i < 76
	BEGIN
		SELECT @byte1 = CONVERT(INTEGER, SUBSTRING(@items, @i * 10 + 1, 1)),
			   @byte2 = CONVERT(INTEGER, SUBSTRING(@items, @i * 10 + 8, 1)), 		   
			   @item = SUBSTRING(@items, @i * 10 + 1, 10)
			   
		IF @item <> 0xFFFFFFFFFFFFFFFFFFFF 
		BEGIN
			SET @index = (@byte1 & 0x1F)
			SET @section = ((@byte1 & 0xE0) / POWER(2, 5)) | ((@byte2 & 0x80) / POWER(2, 4))
		
			IF EXISTS(SELECT * FROM WOLF_ITEMS WHERE ItemSection = @section AND ItemIndex = @index)
			BEGIN
				SET @items = [dbo].[WOLF_INVENTORY_REMOVE] (@i, @items)
				SELECT @price = ItemPrice FROM WOLF_ITEMS WHERE ItemSection = @section AND ItemIndex = @index
				SET @totalPrice = @totalPrice + @price
			END
		END

		SET @i = @i + 1
	END

	SET @outItems = @items 
	SET @outPrice = @totalPrice
END
GO

--
-- Execução do processo
--

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WOLF_EXECUTE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WOLF_EXECUTE]
GO

CREATE PROCEDURE WOLF_EXECUTE
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @name AS VARCHAR(10)
	DECLARE @account AS VARCHAR(10)
	DECLARE @vault AS INT
	DECLARE @inventory AS VARBINARY(760)
	DECLARE @newinventory AS VARBINARY(760)
	DECLARE @warehouse AS VARBINARY(1200)
	DECLARE @newwarehouse AS VARBINARY(1200)
	DECLARE @finalPrice INT

	-- Inventários
	
	DECLARE inventories CURSOR FOR SELECT AccountID, Name, Inventory FROM Character
	
	OPEN inventories
	FETCH NEXT FROM inventories INTO @account, @name, @inventory

	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC [dbo].[WOLF_PROCESS_INVENTORY] @inventory, @outItems = @newinventory OUTPUT, @outPrice = @finalPrice OUTPUT
		
		IF @newinventory <> @inventory
		BEGIN
			UPDATE Character SET Inventory = @newinventory WHERE Name = @name
			UPDATE MEMB_INFO SET Cash = Cash + @finalPrice WHERE memb___id = @account
		END

		FETCH NEXT FROM inventories INTO @account, @name, @inventory
	END

	CLOSE inventories
	DEALLOCATE inventories 
	
	-- Warehouses
	
	DECLARE warehouses CURSOR FOR SELECT AccountID, Items FROM warehouse
	
	OPEN warehouses
	FETCH NEXT FROM warehouses INTO @account, @warehouse

	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC [dbo].[WOLF_PROCESS_WAREHOUSE] @warehouse, @outItems = @newwarehouse OUTPUT, @outPrice = @finalPrice OUTPUT
		
		IF @newwarehouse <> @warehouse
		BEGIN
			UPDATE warehouse SET Items = @newwarehouse WHERE AccountID = @account
			UPDATE MEMB_INFO SET Cash = Cash + @finalPrice WHERE memb___id = @account
		END

		FETCH NEXT FROM warehouses INTO @account, @warehouse
	END

	CLOSE warehouses
	DEALLOCATE warehouses 
	
	-- ExtWarehouses
	
	DECLARE extwarehouses CURSOR FOR 
		SELECT AccountID, Items, VaultID FROM ExWarehouse
	
	OPEN extwarehouses
	FETCH NEXT FROM extwarehouses INTO @account, @warehouse, @vault

	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC [dbo].[WOLF_PROCESS_WAREHOUSE] @warehouse, @outItems = @newwarehouse OUTPUT, @outPrice = @finalPrice OUTPUT
		
		IF @newwarehouse <> @warehouse
		BEGIN
			UPDATE ExWarehouse SET Items = @newwarehouse WHERE AccountID = @account AND VaultID = @vault
			UPDATE MEMB_INFO SET Cash = Cash + @finalPrice WHERE memb___id = @account
		END

		FETCH NEXT FROM extwarehouses INTO @account, @warehouse, @vault
	END

	CLOSE extwarehouses
	DEALLOCATE extwarehouses 
END
GO

Exemplos de uso:

1. Remove todos os Chaos Nature Bows do servidor, sem dar nenhum cash pro usuário.

4 = Seção e bows
6 = Índice do Chaos Nature Bow
0 = Cashs retornados para cada item encontrado

EXEC WOLF_INITIALIZE
EXEC WOLF_REMOVE_ITEM 4, 6, 0
EXEC WOLF_EXECUTE

2. Remove o set Dragon do servidor e devolve 10 cashs para cada Helm, Boots, Gloves, e 15 para cada Armor e Pants

EXEC WOLF_INITIALIZE
EXEC WOLF_REMOVE_ITEM 7, 1, 10
EXEC WOLF_REMOVE_ITEM 8, 1, 15
EXEC WOLF_REMOVE_ITEM 9, 1, 15
EXEC WOLF_REMOVE_ITEM 10, 1, 10
EXEC WOLF_REMOVE_ITEM 11, 1, 10
EXEC WOLF_EXECUTE

Resumindo:

EXEC WOLF_INITIALIZE 
EXEC WOLF_REMOVE_ITEM <SEÇÃO_DO_ITEM>, <ÍNDICE_DO_ITEM>, <CASHS_DEVOLVIDOS>
EXEC WOLF_EXECUTE

Sempre que tiver + de um item para ser removido, por exemplo sets que são 5 itens, repetir a linha do WOLF_REMOVE_ITEM com as informações do item em específico.

• Não darei suporte pro script, nem farei atualizações no mesmo
• O script tem muito tempo que foi feito, e nem lembro se é a última versão
• NUNCA UTILIZE O SCRIPT COM O SERVIDOR LIGADO E/OU SITE/SHOP LIGADO
• Use por sua conta e risco... eu to cagando e andando se você fizer algo errado

Créditos: WoLfulus