smp_check_purchased_item

[OyunPozitifRappelz]

Please help!

[Arthas2012]

Originally Posted by OyunPozitifRappelz

Please help!

Enjoy

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

[mongreldogg]

^^
Msg 208, Level 16, State 6, Procedure smp_check_purchased_item, Line 3
Invalid object name 'dbo.smp_check_purchased_item'.

tell plz what is it=)

[Arthas2012]

Originally Posted by mongreldogg

^^
Msg 208, Level 16, State 6, Procedure smp_check_purchased_item, Line 3
Invalid object name 'dbo.smp_check_purchased_item'.

tell plz what is it=)

umh.. it should work, try to delete the already one you have and be sure it's Telecaster the name of the database (if not change it in the script).

[gruce]

edit it -_-

[mongreldogg]

Originally Posted by Arthas2012

umh.. it should work, try to delete the already one you have and be sure it's Telecaster the name of the database (if not change it in the script).

db name is Telecaster=)
i'm not so noob=)
and I haven't this object in my DB. And so I have created it thanks=)
Mystical xD

[gruce]

Lol @@

[mongreldogg]

But have another problem now.

Could not find stored procedure 'dbo.smp_raed_purchase_item_list'
(DB COM ERROR in GS)

[Arthas2012]

Originally Posted by mongreldogg

But have another problem now.

Could not find stored procedure 'dbo.smp_raed_purchase_item_list'
(DB COM ERROR in GS)

I edited my post, now it works.

[mongreldogg]

Originally Posted by Arthas2012

I edited my post, now it works.

Thanks but it doesn't helped me with new error =(

[Arthas2012]

Originally Posted by mongreldogg

But have another problem now.

Could not find stored procedure 'dbo.smp_raed_purchase_item_list'
(DB COM ERROR in GS)

Originally Posted by mongreldogg

Thanks but it doesn't helped me with new error =(

Which error? o_O

[mongreldogg]

Originally Posted by Arthas2012

Which error? o_O

The first you quoted is an error that appears in GS when I open cash shop bag.
Not this: smp_check_purchased_item
But this: smp_read_purchase_item_list

[Arthas2012]

Originally Posted by mongreldogg

The first you quoted is an error that appears in GS when I open cash shop bag.
Not this: smp_check_purchased_item
But this: smp_read_purchase_item_list

come on, today i'm generous

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

Delete it before use it (if you already have)


Arthas

[mongreldogg]

Originally Posted by Arthas2012

come on, today i'm generous

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

Delete it before use it (if you already have)


Arthas

works. thanks man)

Originally Posted by Arthas2012

come on, today i'm generous

[/html]

Delete it before use it (if you already have)


Arthas

Oh no! Not again!
smp_takeout_commercial_item

=(((
Can you give me fix?)
Yeah i know i know I'm noob but.. Please=)

[Arthas2012]

Originally Posted by mongreldogg

works. thanks man)



Oh no! Not again!
smp_takeout_commercial_item

=(((
Can you give me fix?)
Yeah i know i know I'm noob but.. Please=)

BTW, in download center there are smp fixes dude...

and these smp it's really easy to fix..

 Spoiler

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

@OUT_ITEM_CODE          INT OUTPUT,
@IN_SID                 INT,
@IN_COUNT               INT,
@IN_ACCOUNT_ID          INT,
@IN_AVATAR_ID           INT,
@IN_AVATAR_NAME         VARCHAR(61),
@IN_SERVER_NAME         VARCHAR(30),
@IN_TRANSACTION_CODE	INT,
@IN_IDX					INT
 
AS
SET NOCOUNT ON

SET @OUT_ITEM_CODE = 0

DECLARE @RET INT
SET @RET = -1

DECLARE @REST_ITEM_COUNT INT
SET @REST_ITEM_COUNT = -1

-- °¹¼ِ ہج»َ
IF @IN_COUNT < 1
BEGIN
    GOTO ON_END
END

BEGIN TRANSACTION

	SELECT @REST_ITEM_COUNT = rest_item_count FROM dbo.PaidItem WHERE sid = @IN_SID;

	IF @REST_ITEM_COUNT < @IN_COUNT
	BEGIN
		ROLLBACK TRANSACTION
        GOTO ON_END
	END

	-- Remove item(s) from Item Box
	UPDATE dbo.PaidItem SET

        rest_item_count = ( @REST_ITEM_COUNT - @IN_COUNT ),
        taken_avatar_id = @IN_AVATAR_ID,
        taken_avatar_name = @IN_AVATAR_NAME,
        taken_server_name = @IN_SERVER_NAME,
        taken_time = GETDATE()
        
    WHERE sid = @IN_SID and taken_account_id = @IN_ACCOUNT_ID;
    SET @RET = 0

	DECLARE @ACCOUNT_ID INT
	SET @ACCOUNT_ID = -1

	SELECT @OUT_ITEM_CODE = item_code, @ACCOUNT_ID = taken_account_id from dbo.PaidItem where sid = @IN_SID;

	IF @ACCOUNT_ID <> @IN_ACCOUNT_ID
		BEGIN
			SET @OUT_ITEM_CODE = 0
			ROLLBACK TRANSACTION
			GOTO ON_END
		END

	-- Add the item(s) to the character
	INSERT INTO dbo.Item (

		sid,							-- 0
		owner_id,						-- 1
		account_id,						-- 2
		summon_id,						-- 3
		auction_id,						-- 4
		keeping_id,						-- 5
		idx,							-- 6
		code,							-- 7
		flag,							-- 8
		cnt,							-- 9
		[level],						-- 10
		enhance,
		ethereal_durability,						-- 11
		endurance,						-- 12
		gcode,							-- 13
		create_time,						-- 14
		wear_info,						-- 15
		socket_0,						-- 16
		socket_1,						-- 17
		socket_2,						-- 18
		socket_3,						-- 19
		remain_time,						-- 20
		elemental_effect_type,					-- 21
		elemental_effect_expire_time,				-- 22
		elemental_effect_attack_point,				-- 23
		elemental_effect_magic_point,				-- 24
		update_time						-- 25

	)
	VALUES (
	
		@IN_TRANSACTION_CODE,					-- 0
		@IN_AVATAR_ID,						-- 1
		@IN_ACCOUNT_ID,						-- 2
		0,							-- 3
		0,							-- 4
		0,							-- 5
		@IN_IDX,						-- 6
		@OUT_ITEM_CODE,						-- 7
		0,							-- 8
		@IN_COUNT,						-- 9
		0,							-- 10
		0,
		0,							-- 11
		0,							-- 12
		0,							-- 13
		GETDATE(),						-- 14	
		-1,							-- 15
		0,							-- 16
		0,							-- 17
		0,							-- 18
		0,							-- 19
		0,							-- 20
		0,							-- 21
		0,							-- 22
		0,							-- 23
		0,							-- 24
		GETDATE()						-- 25
	);

	SET @RET = @@ERROR

	IF @RET <> 0
	BEGIN
		ROLLBACK TRANSACTION
        GOTO ON_END
	END

COMMIT TRANSACTION

ON_END:
RETURN @RET



GO