[Help Thread] Please post your questions here.

USE [Telecaster]
GO

IF (OBJECT_ID('smp_check_purchased_item') IS NOT NULL)
  DROP PROCEDURE smp_check_purchased_item
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[smp_check_purchased_item]
@IN_ACCOUNT_ID			INT,
@IN_CHARACTER_ID		INT,
@OUT_TOTAL_ITEM_COUNT	INT		OUTPUT,
@OUT_NEW_ITEM_COUNT		INT		OUTPUT,
@OUT_PREMIUM_TICKET		INT		OUTPUT,
@OUT_PREMIUM_REST_TIME	INT		OUTPUT,
@OUT_STAMINA_REGEN_TIME	INT		OUTPUT

 
AS
SET NOCOUNT ON

	SET @OUT_PREMIUM_TICKET  = 0
	SET @OUT_PREMIUM_REST_TIME = 0
	SET @OUT_STAMINA_REGEN_TIME = 0

	SELECT @OUT_PREMIUM_TICKET = COUNT(*), @OUT_PREMIUM_REST_TIME = DATEDIFF( second, GETDATE(), MAX( valid_time ) ) FROM dbo.PaidItem WHERE taken_account_id = @IN_ACCOUNT_ID AND item_code = 910000 AND valid_time > GETDATE() AND isCancel = 0

	IF @OUT_PREMIUM_TICKET < 1
	BEGIN
		SET @OUT_PREMIUM_REST_TIME = 0

		DECLARE @LOGOUT_TIME		DATETIME
		SET @LOGOUT_TIME = GETDATE()

		SELECT @LOGOUT_TIME = logout_time FROM Character WITH (NOLOCK) WHERE account_id = @IN_ACCOUNT_ID AND sid = @IN_CHARACTER_ID
		SELECT @OUT_STAMINA_REGEN_TIME = DATEDIFF( minute, @LOGOUT_TIME, MAX( valid_time ) ) FROM dbo.PaidItem WHERE taken_account_id = @IN_ACCOUNT_ID AND item_code = 910000 AND valid_time > @LOGOUT_TIME AND isCancel = 0

		IF @OUT_STAMINA_REGEN_TIME < 1 OR @OUT_STAMINA_REGEN_TIME IS NULL
		BEGIN
			SET @OUT_STAMINA_REGEN_TIME = 0
		END
END
	SELECT @OUT_TOTAL_ITEM_COUNT = COUNT(*) from dbo.PaidItem WHERE taken_account_id = @IN_ACCOUNT_ID AND item_code <> 910000 AND rest_item_count > 0  AND valid_time > GETDATE() AND isCancel = 0
	SELECT @OUT_NEW_ITEM_COUNT   = COUNT(*) from dbo.PaidItem WHERE taken_account_id = @IN_ACCOUNT_ID AND item_code <> 910000 AND rest_item_count > 0  AND confirmed = 0 AND valid_time > GETDATE() AND isCancel = 0

RETURN @@ERROR

GO

USE [Telecaster]
GO

IF (OBJECT_ID('smp_read_purchased_item_list') IS NOT NULL)
  DROP PROCEDURE smp_read_purchased_item_list
GO
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 AND isCancel = 0

SELECT  sid,                 
        account_id,           
        avatar_id,            
        avatar_name,           
        server_name,			
        item_code,            
        item_count,            
        rest_item_count,		  
        bought_time,           
        valid_time             
        FROM dbo.PaidItem WHERE taken_account_id =@IN_ACCOUNT_ID AND rest_item_count > 0 AND item_code <> 910000 AND isCancel = 0 ORDER BY sid

RETURN @@ERROR
 


GO

USE [Telecaster]
GO

IF (OBJECT_ID('smp_update_takeout_info') IS NOT NULL)
  DROP PROCEDURE smp_update_takeout_info
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[smp_update_takeout_info]

@IN_CID				INT,
@IN_ACCOUNT_ID		INT,
@IN_COUNT			INT,
@IN_AVATAR_ID		INT,
@IN_AVATAR_NAME		VARCHAR(61),
@IN_SERVER_NAME		VARCHAR(30)

AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  

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_CID AND taken_account_id = @IN_ACCOUNT_ID

GO

USE [Telecaster]
GO

IF (OBJECT_ID('smp_takeout_commercial_item') IS NOT NULL)
  DROP PROCEDURE smp_takeout_commercial_item
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[smp_takeout_commercial_item]
@OUT_ITEM_CODE		INT OUTPUT,
@IN_CID				INT,
@IN_COUNT			BIGINT,
@IN_ACCOUNT_ID		INT,
@IN_AVATAR_ID		INT,
@IN_AVATAR_NAME		VARCHAR(61),
@IN_SERVER_NAME		VARCHAR(30),
@IN_SID				INT,
@IN_REMAIN_TIME		INT

AS
SET NOCOUNT ON

SET @OUT_ITEM_CODE = 0

DECLARE @RET INT
SET @RET = 0

IF @IN_COUNT < 1
BEGIN
	SET @RET = -1
    GOTO ON_END
END

DECLARE @REST_ITEM_COUNT INT
SET @REST_ITEM_COUNT = -1

SELECT @REST_ITEM_COUNT = rest_item_count FROM dbo.PaidItem WITH (NOLOCK) WHERE sid = @IN_CID AND isCancel = 0;
IF @@ERROR <> 0
BEGIN
	SET @RET = -11
    GOTO ON_END
END

IF @REST_ITEM_COUNT < @IN_COUNT
BEGIN
	SET @RET = -2
    GOTO ON_END
END

DECLARE @ACCOUNT_ID INT
SET @ACCOUNT_ID = -1

SELECT @OUT_ITEM_CODE = item_code, @ACCOUNT_ID = taken_account_id FROM dbo.PaidItem WITH (NOLOCK) WHERE sid = @IN_CID;
IF @@ERROR <> 0
BEGIN
	SET @RET = -12
    GOTO ON_END
END

IF @OUT_ITEM_CODE = 910000
BEGIN
	SET @RET = -3
    GOTO ON_END
END

IF @ACCOUNT_ID <> @IN_ACCOUNT_ID
BEGIN
    SET @OUT_ITEM_CODE = 0
	SET @RET = -4
    GOTO ON_END
END

EXEC dbo.smp_update_takeout_info @IN_CID, @IN_ACCOUNT_ID, @IN_COUNT, @IN_AVATAR_ID, @IN_AVATAR_NAME, @IN_SERVER_NAME

IF @@ERROR <> 0
BEGIN
	SET @RET = 1
    GOTO ON_END
END

INSERT INTO Item
	(
	sid,					-- 0
			owner_id,				-- 1
			account_id,				-- 2
			summon_id,				-- 3
			auction_id,				-- 4
			keeping_id,				-- 5
			previous_sid,			-- 6
			code,					-- 7
			flag,					-- 8
			cnt,					-- 9
			level,					-- 10
			enhance,				-- 11
			ethereal_durability,	-- 12
			endurance,				-- 13
			gcode,					-- 14
			create_time,			-- 15
			wear_info,				-- 16
			socket_0,				-- 17
			socket_1,				-- 18
			socket_2,				-- 19
			socket_3,				-- 20
			awaken_sid,				-- 21
			random_option_sid,		-- 22
			remain_time,			-- 23
			elemental_effect_type,	-- 24
			elemental_effect_expire_time,	-- 25
			elemental_effect_attack_point,	-- 26
			elemental_effect_magic_point,	-- 27
			appearance_code,		-- 28
			update_time,			-- 29
			summon_code				-- 30
	)
	VALUES
	(
			@IN_SID,				-- 0
			@IN_AVATAR_ID,			-- 1
			@IN_ACCOUNT_ID,			-- 2
			0,			-- 3
			0,			-- 4
			0,			-- 5
			0,		-- 6
			@OUT_ITEM_CODE,				-- 7
			0,				-- 8
			@IN_COUNT,				-- 9
			1,				-- 10
			0,			-- 11
			0,	-- 12
			0,			-- 13
			0,				-- 14
			GETDATE(),				-- 15	
			-1,						-- 16
			0,			-- 17
			0,			-- 18
			0,			-- 19
			0,			-- 20
			0,						-- 21
			0,						-- 22
			@IN_REMAIN_TIME,		-- 23
			0,	-- 24
			0,	-- 25
			0,	-- 26
			0,	-- 27
			0,	-- 28
			GETDATE(),				-- 29
			0);

IF @@ERROR <> 0
BEGIN
	SET @RET = 2
    GOTO ON_END
END

SET @RET = 0

ON_END:
RETURN @RET


GO

USE [Telecaster]
GO

ALTER TABLE [dbo].[PaidItem] DROP CONSTRAINT [DF__PaidItem__isCanc__607D3EDD]
GO

ALTER TABLE [dbo].[PaidItem] DROP CONSTRAINT [DF__PaidItem__taken___5F891AA4]
GO

ALTER TABLE [dbo].[PaidItem] DROP CONSTRAINT [DF__PaidItem__valid___5E94F66B]
GO

ALTER TABLE [dbo].[PaidItem] DROP CONSTRAINT [DF__PaidItem__confir__5DA0D232]
GO

ALTER TABLE [dbo].[PaidItem] DROP CONSTRAINT [DF__PaidItem__confir__5CACADF9]
GO

ALTER TABLE [dbo].[PaidItem] DROP CONSTRAINT [DF__PaidItem__rest_i__5BB889C0]
GO

ALTER TABLE [dbo].[PaidItem] DROP CONSTRAINT [DF__PaidItem__type__5AC46587]
GO

ALTER TABLE [dbo].[PaidItem] DROP CONSTRAINT [DF__PaidItem__taken___59D0414E]
GO

ALTER TABLE [dbo].[PaidItem] DROP CONSTRAINT [DF__PaidItem__taken___58DC1D15]
GO

ALTER TABLE [dbo].[PaidItem] DROP CONSTRAINT [DF__PaidItem__taken___57E7F8DC]
GO

ALTER TABLE [dbo].[PaidItem] DROP CONSTRAINT [DF__PaidItem__taken___56F3D4A3]
GO

ALTER TABLE [dbo].[PaidItem] DROP CONSTRAINT [DF__PaidItem__server__55FFB06A]
GO

ALTER TABLE [dbo].[PaidItem] DROP CONSTRAINT [DF__PaidItem__avatar__550B8C31]
GO

ALTER TABLE [dbo].[PaidItem] DROP CONSTRAINT [DF__PaidItem__avatar__541767F8]
GO

/****** Object:  Table [dbo].[PaidItem]    Script Date: 22.12.2015 22:49:55 ******/
DROP TABLE [dbo].[PaidItem]
GO

/****** Object:  Table [dbo].[PaidItem]    Script Date: 22.12.2015 22:49:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PaidItem](
	[sid] [int] IDENTITY(1,1) NOT NULL,
	[buy_id] [int] NOT NULL,
	[account_id] [int] NOT NULL,
	[avatar_id] [int] NOT NULL,
	[avatar_name] [varchar](61) NOT NULL,
	[server_name] [varchar](30) NOT NULL,
	[taken_account_id] [int] NOT NULL,
	[taken_avatar_id] [int] NOT NULL,
	[taken_avatar_name] [varchar](61) NOT NULL,
	[taken_server_name] [varchar](30) NOT NULL,
	[item_code] [int] NOT NULL,
	[item_count] [int] NOT NULL,
	[type] [tinyint] NOT NULL,
	[rest_item_count] [int] NOT NULL,
	[confirmed] [bit] NOT NULL,
	[confirmed_time] [datetime] NOT NULL,
	[bought_time] [datetime] NOT NULL,
	[valid_time] [datetime] NOT NULL,
	[taken_time] [datetime] NOT NULL,
	[isCancel] [bit] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
	[sid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PaidItem] ADD  DEFAULT ((0)) FOR [avatar_id]
GO

ALTER TABLE [dbo].[PaidItem] ADD  DEFAULT ('') FOR [avatar_name]
GO

ALTER TABLE [dbo].[PaidItem] ADD  DEFAULT ('') FOR [server_name]
GO

ALTER TABLE [dbo].[PaidItem] ADD  DEFAULT ((0)) FOR [taken_account_id]
GO

ALTER TABLE [dbo].[PaidItem] ADD  DEFAULT ((0)) FOR [taken_avatar_id]
GO

ALTER TABLE [dbo].[PaidItem] ADD  DEFAULT ('') FOR [taken_avatar_name]
GO

ALTER TABLE [dbo].[PaidItem] ADD  DEFAULT ('') FOR [taken_server_name]
GO

ALTER TABLE [dbo].[PaidItem] ADD  DEFAULT ((0)) FOR [type]
GO

ALTER TABLE [dbo].[PaidItem] ADD  DEFAULT ((0)) FOR [rest_item_count]
GO

ALTER TABLE [dbo].[PaidItem] ADD  DEFAULT ((0)) FOR [confirmed]
GO

ALTER TABLE [dbo].[PaidItem] ADD  DEFAULT ('9999-12-31') FOR [confirmed_time]
GO

ALTER TABLE [dbo].[PaidItem] ADD  DEFAULT ('9999-12-31') FOR [valid_time]
GO

ALTER TABLE [dbo].[PaidItem] ADD  DEFAULT ('9999-12-31') FOR [taken_time]
GO

ALTER TABLE [dbo].[PaidItem] ADD  DEFAULT ((0)) FOR [isCancel]
GO

USE [Telecaster]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[insert_shopbox_to_all_from_globalvariable]
@item_id	INT,
@anzahl	INT
AS

DECLARE @index int,@max int,@account_id int,@buy_id int

set @max = (Select count(*) from Auth.dbo.Accounts)
set @index = 0
set @account_id = 0

WHILE @index < @max
BEGIN
	IF EXISTS(SELECT account_id from Auth.dbo.Accounts where account_id = @index)
		BEGIN
			set @buy_id = (select ISNULL(max(buy_id)+1, 0) from dbo.PaidItem)
			IF EXISTS(SELECT * FROM dbo.PaidItem WHERE account_id = @index AND item_code = @item_id)
				BEGIN
					UPDATE dbo.Paiditem set item_count = item_count+@anzahl,rest_item_count = rest_item_count+@anzahl where account_id = @index and item_code = @item_id
				END
			ELSE
				BEGIN
					INSERT INTO [dbo].[PaidItem]([buy_id],[account_id],[avatar_id],[avatar_name],[server_name],[taken_account_id],[taken_avatar_id],[taken_avatar_name],[taken_server_name],[item_code],[item_count],[type],[rest_item_count],[confirmed],[confirmed_time],[bought_time],[valid_time],[taken_time],[isCancel])
					VALUES(@buy_id,@index,0,'','',@index,0,'','',@item_id,@anzahl,0,@anzahl,1,GETDATE(),GETDATE(),GETDATE()+300,GETDATE(),0)
				END
			set @index = @index + 1
		END
	ELSE
		BEGIN
			set @index = @index + 1
		END
END

RETURN @@ERROR


GO