-- Run create database here if you need to regenerate the database

USE SodaDB
GO

DROP PROCEDURE decrement_inventory
GO

CREATE PROCEDURE decrement_inventory
AS
DECLARE @c UNIQUEIDENTIFIER;
DECLARE @h UNIQUEIDENTIFIER;
DECLARE @m XML;
DECLARE @t NVARCHAR(128);

WHILE 1 = 1
BEGIN

BEGIN TRANSACTION
WAITFOR(
   GET CONVERSATION GROUP @c FROM InvRequestQueue_L8A),
   TIMEOUT 100 ;

IF @c IS NULL 
  BEGIN
   ROLLBACK TRANSACTION;
   BREAK;
  END

WHILE 1 = 1
BEGIN
	RECEIVE TOP(1) 
        @h=conversation_handle, 
        @t=message_type_name,
		@m=CAST(message_body as XML) 
     FROM InvRequestQueue_L8A 
     WHERE conversation_group_id = @c;

    IF @@rowcount <> 1 
		BREAK;
    IF @t = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    OR @t = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' 
    BEGIN 
		END CONVERSATION @h WITH CLEANUP;
        CONTINUE
    END
    ELSE
    IF @t = N'InvRequestMsg_L8A'
    BEGIN
		-- If the message gets here, it's valid
		-- We'll break it up in the next lab with XQuery
		-- And also insert it into the inventory request table
		UPDATE	inventory 
		SET		units_in_stock = units_in_stock - 3
		WHERE	product_id = 1040; 

		-- Send response
		SEND ON CONVERSATION @h
		 MESSAGE TYPE InvResponseMsg_L8A ('
		<InventoryReq xmlns="http://www.company.com/inventoryRequest">
		  <order>1</order>
		  <product>1040</product>
		  <units>3</units>
		  <status>OK</status>
		</InventoryReq>');

        -- Processed message, now end conversation
        END CONVERSATION @h;
    END
END
COMMIT TRANSACTION;
END
GO

-- 
-- Turn on activation
--

ALTER QUEUE InvRequestQueue_L8A WITH ACTIVATION 
  (STATUS=ON,
   PROCEDURE_NAME=decrement_inventory,
   MAX_QUEUE_READERS=5,
   EXECUTE AS SELF
  )
go



