The Procedures Thread

[X_Sarah_X]

I will keep this thread updated with released procedures that fix things, and procedures that are usefull to people running a Pangya server.
All credits go to the people who created and/or modified these procedures to make them work, I only gathered them into one thread.


Procedure Fixes

Tutorial Fix by chreadie
Mail / Giveitem Fix (USP_MAIL_SEND) by chreadie
Card Equip Fix by lizardo (as posted by Acardia)
TD_CHAR_EQUIP_SAVE_S4 Fix by chreadie




Usefull Procedures

Add Item To User By Tsukasa

USE [Pangya_S4_TH]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[TSU_AddItemToUser] (
	@user_id	varchar(32),
	@item_id int
)
AS

BEGIN
	DECLARE @user_idx int
	
	SELECT @user_idx = [UID] FROM [Pangya_Member_Info]
	WHERE [userid] = @user_id
	
	IF @@ROWCOUNT = 1
	BEGIN
		SELECT [TYPEID] AS [Item ID], [NAME] AS [Item Name] FROM [PANGYA_ITEM_TYPELIST]
		WHERE TYPEID = @item_id
		
		IF @@ROWCOUNT = 1
		BEGIN
			INSERT INTO Pangya_Item_WareHouse ( [UID], [typeid], [valid], [regdate] )
			VALUES ( @user_idx, @item_id, 1, GETDATE())
			
			RETURN @@ERROR
		END
		ELSE
		BEGIN
			PRINT N'Item does not exist!'
			RETURN 1
		END
	END
	ELSE
	BEGIN
		PRINT N'User does not exist!'
		RETURN 1
	END
END
GO

Register Proc that auto-adds Cookies by X_Sarah_X

USE [ini3_py_account]
GO
/****** Object:  StoredProcedure [dbo].[SP_PANGYA_REGIST]    Script Date: 02/05/2011 20:02:14 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[SP_PANGYA_REGIST]

@idcard	CHAR(13),
@sex 		BIT,
@birthday 	VARCHAR(50),
@zipcode	CHAR(5),
@id		VARCHAR(32),
@pswd		VARCHAR(32)

AS

IF NOT EXISTS(	SELECT UserID FROM Ini3_UserLogin WHERE UserID = @id AND VisaIDCard = @idcard)
       BEGIN
	INSERT INTO Ini3_UserLogin(UserID, Password, Birthday, Sex, VisaIDCard, Confirm18, UserName, ZipCode)
	VALUES (@id, @pswd, CONVERT(DATETIME,@birthday), @sex, @idcard, '0', @id, @zipcode)
	
	-- Registration Done, adding cookies procedure below
	
	
	DECLARE @UserName varchar(32)
	DECLARE @UserIdx int
	
		-- Static sitecode, is always 'ini3'
	DECLARE @siteCode varchar(5)
	SET @siteCode = N'ini3'

	-- Get the nickname and the idx for the specified user from the game-server database...
	SELECT @UserName = [UserID], @UserIdx = [idx] FROM [ini3_py_account].[dbo].[Ini3_UserLogin]
	WHERE userid = @id;
	
	-- Check whether we have previous records for this user...
	SELECT UserNo, UserID FROM [INI3BILL_DB].[dbo].TAccountMst
	WHERE UserNo = @UserIdx
	AND UserID = @id
	
	IF @@ROWCOUNT = 0
		-- User never got cookies so we need to create a new row for him...
		INSERT INTO [INI3BILL_DB].[dbo].TAccountMst ([SiteCode], [UserNo], [UserID], [UserName], [CookieAmt])
		VALUES ( @siteCode, @UserIdx, @id, @UserName, '250000')
	ELSE
		-- User has cookies so we can simply update the old value
		UPDATE [INI3BILL_DB].[dbo].TAccountMst SET [CookieAmt] = [CookieAmt] + '250000', [UpdDate] = CURRENT_TIMESTAMP
		WHERE [UserNo] = @UserIdx AND [UserID] = @id
       END

[MentaL]

[thaicom05]

You Is Create Procedures Lv Up Get Gif & Pang & Cookie To UID

[AlviNz214]

@Sarah, how can i add a script for pang, i mean i will merge it to cookies?,

[X_Sarah_X]

@Sarah, how can i add a script for pang, i mean i will merge it to cookies?,

Even though you'd think adding pang/cookies would be similar, nothing's further from the truth.
To begin with, they are stored in different databases.

Pang is stored in the PangYa_Member_Info table in the Pangya_S4_TH database iirc.

You will eventually use a simple UPDATE query and build a procedure based on that.

The query you will need is:

USE Pangya_S4_TH
UPDATE Pangya_Member_Info
SET Pang = Pang + xxxx
WHERE userid = xxxx

or you can use:

USE Pangya_S4_TH
UPDATE Pangya_Member_Info
SET Pang = Pang + xxxx
WHERE NickName = xxxx

The difference is only the column which you are searching for.
I personally prefer searching userids since they never change.

You can, however, use the Cookies procedure as a reference for writing the new Pang procedure. That way you learn a bit of SQL while doing it.

[AlviNz214]

@sarah thanks,

[Cyclone X Jis]

i want register auto give level 70 i want code .......... please help me

[chreadie]

i want register auto give level 70 i want code .......... please help me

The user needs to login and create the first character (i think) before you can assign a level to that character. One way would be to alter the procedure that makes the character in the database.

[X_Sarah_X]

The user needs to login and create the first character (i think) before you can assign a level to that character. One way would be to alter the procedure that makes the character in the database.

Or you can change the default value of the player level column to the rank you want them to start off on.
I did that 2 years ago when I started Pangya Chaos and wanted my players to start as Senior A ranks :)