smp_check_purchased_item

USE [Telecaster]
GO
/****** Object:  StoredProcedure [dbo].[smp_check_purchased_item]    Script Date: 8/6/2012 7:03:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[smp_check_purchased_item]

@IN_ACCOUNT_ID INT,
@IN_SID INT,
@OUT_TOTAL_ITEM_COUNT INT OUTPUT,	/* Total Item Count */
@OUT_NEW_ITEM_COUNT INT OUTPUT,		/* New Item Count (AKA New presents received)*/
@OUT_PREMIUM_TICKET INT OUTPUT,		/* Premium Ticket Flag - 0 = No Pass*/
@OUT_PREMIUM_REST_TIME INT OUTPUT,	/* HV Pass Time Remaining */
@OUT_STAMINA_REGEN_TIME INT OUTPUT	/* Stam Regen Time (Stam Bonus) */

AS
SET NOCOUNT ON

SET @OUT_NEW_ITEM_COUNT = 10 
SET @OUT_STAMINA_REGEN_TIME = 20 

SET @OUT_PREMIUM_TICKET = 0
SET @OUT_PREMIUM_REST_TIME = 0

SELECT @OUT_PREMIUM_TICKET = COUNT(*) from PaidItem WITH (NOLOCK) where taken_account_id = @IN_ACCOUNT_ID and item_code = 910000 and valid_time > GETDATE()

IF @OUT_PREMIUM_TICKET > 0
BEGIN

/* Extend the Pass on first login after it is purchased to grant the user to full time on the pass based on the first login */
UPDATE PaidItem set valid_time = DATEADD(s, DATEDIFF(s, bought_time, GETDATE()), valid_time), taken_time = GETDATE() where taken_account_id = @IN_ACCOUNT_ID AND item_code = 910000 AND taken_time IS NULL

/** Combine passes by invalidating older passes and adding the combined extra time onto the most recently purchased pass **/
DECLARE @VALID_PASS_COUNT INT
SET @VALID_PASS_COUNT = 0

DECLARE @COMBINED_TIME INT
SET @COMBINED_TIME = 0

DECLARE @NEWEST_PASS INT
SET @NEWEST_PASS = -1

-- Check for more than one pass in Item Box.  If there are combine them into one pass.
SELECT @VALID_PASS_COUNT = COUNT(*) FROM PaidItem WHERE taken_account_id = @IN_ACCOUNT_ID AND item_code = 910000 AND valid_time > GETDATE()

IF @VALID_PASS_COUNT > 1
	BEGIN
		-- Get the combined time of all active passes
		SELECT @COMBINED_TIME = SUM(DATEDIFF(s, GETDATE(), valid_time)) from PaidItem where taken_account_id = @IN_ACCOUNT_ID AND item_code = 910000 AND valid_time > GETDATE()
		
		-- Select the most recent pass
		SELECT TOP (1) @NEWEST_PASS = sid from PaidItem where taken_account_id = @IN_ACCOUNT_ID AND item_code = 910000 AND valid_time > GETDATE() ORDER BY bought_time DESC
		
		-- Invalidate all but the most recent pass
		SET ROWCOUNT 1
		WHILE 1=1
		BEGIN
			UPDATE PaidItem set valid_time = GETDATE(), rest_item_count=0 WHERE sid IN ( SELECT TOP 1 sid FROM PaidItem WITH (NOLOCK) WHERE taken_account_id = @IN_ACCOUNT_ID AND item_code = 910000 AND valid_time > GETDATE() AND sid <> @NEWEST_PASS )
			IF @@ROWCOUNT = 0 BREAK
		END
		SET ROWCOUNT 0

		-- Extend the most recent pass by the Combined time - the current time
		UPDATE PaidItem set valid_time = DATEADD(s, @COMBINED_TIME, GETDATE()), rest_item_count=0 WHERE taken_account_id = @IN_ACCOUNT_ID AND item_code = 910000 AND valid_time > GETDATE()AND sid = @NEWEST_PASS
	END
-- end combine passes
	
SELECT @OUT_PREMIUM_REST_TIME = MAX(DATEDIFF(s, GETDATE(), valid_time)) from PaidItem WITH (NOLOCK) where taken_account_id = @IN_ACCOUNT_ID and item_code = 910000 and valid_time > GETDATE()

SELECT @OUT_TOTAL_ITEM_COUNT = COUNT(*) from PaidItem WITH (NOLOCK) where taken_account_id = @IN_ACCOUNT_ID and rest_item_count > 0;
SELECT @OUT_NEW_ITEM_COUNT = COUNT(*) from PaidItem WITH (NOLOCK) where taken_account_id = @IN_ACCOUNT_ID and rest_item_count > 0 and confirmed = 0;

END

RETURN @@ERROR



GO

USE [Telecaster]
GO
/****** Object:  StoredProcedure [dbo].[smp_read_purchased_item_list]    Script Date: 8/6/2012 7:04:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[smp_read_purchased_item_list]

@IN_ACCOUNT_ID          INT
 
AS
SET NOCOUNT ON

-- ¾ئہجإغ ب®ہخ ؟©؛خ¸¦ ¼¼ئأ
UPDATE dbo.PaidItem set confirmed = 1, confirmed_time = GETDATE() WHERE taken_account_id = @IN_ACCOUNT_ID and confirmed = 0;

SELECT  sid,                          -- ID
        account_id,            -- ±¸¸إار °èء¤ ID
        avatar_id,             -- ±¸¸إار ؤ³¸¯ ID        ( -1 ہد°و؟ىب¨اا؟،¼*±¸¸إار°حہس)
        avatar_name,           -- ±¸¸إار ؤ³¸¯ ہج¸§        ( -1 ہد°و؟ىب¨اا؟،¼*±¸¸إار°حہس)
        server_name,                      -- ±¸¸إار ¼*¹ِ ہج¸§
        item_code,             -- ¾ئہجإغؤعµه
        item_count,            -- ±¸¸إار ¾ئہجإغ°¹¼ِ
        rest_item_count,                  -- ³²ہ؛ ¾ئہجإغ °¹¼ِ
        bought_time,           -- ±¸¸إ½أ°£
        valid_time             -- ہ¯ب؟½أ°£( ء¦ار¾ّہ»°و؟ى-12-31 )
               
        FROM dbo.PaidItem WITH (NOLOCK) WHERE taken_account_id = @IN_ACCOUNT_ID and rest_item_count > 0 
        -- The below shows items after they have been taken
        --or sid in ( select sid from dbo.PaidItem WITH(NOLOCK) WHERE taken_account_id =@IN_ACCOUNT_ID and rest_item_count = 0)

RETURN @@ERROR



GO