[SQL] USP_MAIL_SEND (with items + /giveitem)

[chreadie]

If you cant send consumable items (item gruop 6), use these lines:

INSERT INTO dbo.TA_PARTS_GRP (GRP, GRP_NM_EN, GRP_NM_KR, IN_DATE, USE_YN) VALUES (6,'Boosters', 'Boosters', GETDATE(), 'Y')
INSERT INTO dbo.TA_PARTS_GRP_LIMIT (GRP, GRP_LIMIT, IN_DATE, USE_YN) VALUES (6, 10000, GETDATE(), 'Y')

Updated to work with Pang Pocket

USE [Pangya_S4_TH]
GO
/****** Object: StoredProcedure [dbo].[USP_MAIL_SEND] Script Date: 02/06/2011 21:07:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[USP_MAIL_SEND] (
@FROMUID INT = 0,
@TOUID INT = 0,
@STUFF VARCHAR(2000) = '',
@TITLE VARCHAR(2000) = '',
@MSG VARCHAR(1000) = '',
@COST INT = 0,
@UnknownInt1 INT = 0,
@UnknownInt2 INT = 0,
@UnknownInt3 INT = 0
--@FLAG INT OUTPUT
)
AS
/********************************************************
** USP_MAIL_SEND by chreadie (www.yunko.se)
********************************************************/
BEGIN

IF OBJECT_ID('TEMPDB..#TD_SRC') IS NOT NULL
DROP TABLE #TD_SRC

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @MAIL_IDX INT;
DECLARE @SEND_DT datetime
DECLARE @BOX_NUM INT
DECLARE @PAPER_IDX INT
DECLARE @SIG_IDX INT
DECLARE @SQL VARCHAR(8000)
DECLARE @TYPEID VARCHAR(2000)
DECLARE @ITEMID VARCHAR(2000)
DECLARE @EQUIP_NUM VARCHAR(2000)
DECLARE @UCCIDX VARCHAR(2000)
DECLARE @SEQ VARCHAR(2000)
DECLARE @ITEMGRP VARCHAR(2000)
DECLARE @DUP VARCHAR(2000)
DECLARE @QTY VARCHAR(2000)
DECLARE @PANG VARCHAR(2000)
DECLARE @COOKIE VARCHAR(2000)
DECLARE @CHAR_ITEM_ID VARCHAR(2000)
DECLARE @DT_TYPE VARCHAR(2000)
DECLARE @DT_VALUE VARCHAR(2000)
DECLARE @MAILSETIDX VARCHAR(2000)
DECLARE @C0 VARCHAR(2000)
DECLARE @C1 VARCHAR(2000)
DECLARE @C2 VARCHAR(2000)
DECLARE @C3 VARCHAR(2000)
DECLARE @C4 VARCHAR(2000)
DECLARE @STR_SEP CHAR(1)
DECLARE @INTLOOP INT
DECLARE @APPLYITEMID INT
SET @SEND_DT = GETDATE()
SET @BOX_NUM = 1
SET @PAPER_IDX = 0
SET @SIG_IDX = 0;
SET @STR_SEP = '|'
SET @SQL = ''
SET @INTLOOP = 1

INSERT INTO [Pangya_S4_TH].[dbo].[Td_Mail_List] (FromUid,ToUid,Subject,prc,Content,Send_Dt,Paper_idx,Sig_Idx) VALUES (@FROMUID,@TOUID,@TITLE,@COST,@MSG,@SEND_DT,@PAPER_IDX,@SIG_IDX)
SET @MAIL_IDX = SCOPE_IDENTITY()
IF @MAIL_IDX IS NOT NULL BEGIN
CREATE TABLE #TD_SRC (
IDX SMALLINT IDENTITY(1,1)
, DT VARCHAR(1000)
)
INSERT INTO [Pangya_S4_TH].[dbo].[Td_Send_Mail] (Uid,Mail_idx,Send_Dt) VALUES(@FROMUID, @MAIL_IDX, @SEND_DT)
INSERT INTO [Pangya_S4_TH].[dbo].[Td_Recv_Mail] (Uid,Mail_idx,Box_Num) VALUES(@TOUID, @MAIL_IDX, @BOX_NUM)
IF CHARINDEX('|', @STUFF) > 0 BEGIN
SELECT @SQL= 'SELECT LTRIM(C) C FROM (SELECT '''
+ REPLACE(CONT,@STR_SEP,''' C UNION ALL ' + CHAR(13) + 'SELECT ''') + ''') T'
FROM ( SELECT CONT = @STUFF ) X
END ELSE BEGIN
SET @SQL = 'SELECT LTRIM(C) C FROM (SELECT ''' + @STUFF + ''' C) T'
END
INSERT INTO #TD_SRC (DT)
EXEC(@SQL)
SELECT * FROM #TD_SRC
WHILE EXISTS( SELECT 1 FROM #TD_SRC WHERE IDX = @INTLOOP ) BEGIN
SELECT @SQL = REPLACE(DT, '^', ' ^') FROM #TD_SRC WHERE IDX = @INTLOOP
IF LEN(@SQL) > 5 BEGIN
IF @FROMUID < 1 BEGIN
EXEC MASTER..XP_SSCANF @SQL, ' ^%s ^%s ^%s ^%s ^%s ^%s ^%s ^%s ^%s ^%s ^%s ^%s ^%s ^%s',
@TYPEID OUTPUT, @ITEMGRP OUTPUT, @DUP OUTPUT, @QTY OUTPUT, @PANG OUTPUT, @COOKIE OUTPUT, @DT_TYPE OUTPUT, @DT_VALUE OUTPUT, @MAILSETIDX OUTPUT, @C0 OUTPUT, @C1 OUTPUT, @C2 OUTPUT, @C3 OUTPUT, @C4 OUTPUT
END
IF @FROMUID > 0 BEGIN
EXEC MASTER..XP_SSCANF @SQL, '%s ^%s ^%s ^%s ^%s ^%s ^%s ^%s ^%s ^%s ^%s ^%s ^%s ^%s ^%s',
@ITEMID OUTPUT, @TYPEID OUTPUT, @ITEMGRP OUTPUT, @DUP OUTPUT, @QTY OUTPUT, @PANG OUTPUT, @COOKIE OUTPUT, @DT_TYPE OUTPUT, @DT_VALUE OUTPUT, @MAILSETIDX OUTPUT, @C0 OUTPUT, @C1 OUTPUT, @C2 OUTPUT, @C3 OUTPUT, @C4 OUTPUT
END
--IF @TYPEID IS NOT NULL BEGIN
INSERT INTO [Pangya_S4_TH].[dbo].[Td_Mail_SetItem] (Typeid, QTY, in_date) VALUES(@TYPEID, @QTY, @SEND_DT)
SET @MAILSETIDX = @@IDENTITY
IF @DT_VALUE < 0 BEGIN
SET @DT_VALUE = 0
END
IF @TYPEID = 436207632 BEGIN
SET @PANG = @QTY
END
INSERT INTO Td_Mail_Item(ToUid, Mail_idx, Send_Dt, TYPEID, ITEM_GRP, DUP, QTY, Pang, Cookie, Dt_type, Dt_Value, ITEM_ID, Mail_set_idx, C0, C1, C2, C3, C4, APPLY_ITEM_ID)
SELECT @TOUID
, @MAIL_IDX
, @SEND_DT
, @TYPEID
, @ITEMGRP
, @DUP
, @QTY
, @PANG
, @COOKIE
, @DT_TYPE
, @DT_VALUE
, @ITEMID
, @MAILSETIDX
, @C0
, @C1
, @C2
, @C3
, @C4
, @APPLYITEMID
--END
END
SET @INTLOOP = @INTLOOP + 1
END
SELECT @MAIL_IDX AS mail_idx, @SEND_DT AS send_dt
--SELECT 0, 1
--SET @FLAG = 1
--return 0

END
ELSE BEGIN
DELETE FROM [Pangya_S4_TH].[dbo].[Td_Mail_List] WHERE FromUid = @FROMUID AND ToUid = @TOUID AND Send_Dt = @SEND_DT
return '0'
END
END

[MentaL]

[Sonict]

You may need to insert a value into dbo.TA_PARTS_GRP_LIMIT otherwise you might not be able to obtain items from mails.

What values? I was testing it out and now I can send items through the mail, but on a regular players account who was the recipient of the test, it says I can't retrieve them. I would like to know what values to use...

I am a GM on my server. It shouldn't make a difference with a regular user receiving a standard item, should it?

[fasa2008]

GM not send items...

[bubbastic]

Keep up the good work ;)

[chreadie]

dbo.TA_PARTS_GRP_LIMIT wants the item group (i dont have a list here at work but for example Strength Boost is group 6) and how many items a character can have of that group.


Post the columnlist of that table if u want further help, im at work so i dont have the database at hand.


fasa2008, what do you mean? you cant send mail with a gmaccount or what?

Thanks bubbastic!

[fasa2008]

dbo.TA_PARTS_GRP_LIMIT wants the item group (i dont have a list here at work but for example Strength Boost is group 6) and how many items a character can have of that group.


Post the columnlist of that table if u want further help, im at work so i dont have the database at hand.


fasa2008, what do you mean? you cant send mail with a gmaccount or what?

Thanks bubbastic!

hello good day! I'm trying to send item with the command gm /giveitem [Nick] [typeid] [num ], plus the item does not arrive in your account, just the letter arrives in your account, the same error before on your old topic ... You know what this error may be? Thanks buddy is people so we need ...

[Hikaru_K]

dbo.TA_PARTS_GRP_LIMIT wants the item group (i dont have a list here at work but for example Strength Boost is group 6) and how many items a character can have of that group.


Post the columnlist of that table if u want further help, im at work so i dont have the database at hand.


fasa2008, what do you mean? you cant send mail with a gmaccount or what?

Thanks bubbastic!

How do you know Strength Boos is from group 6, I mean, where do I find Item group numbers?

[Tsukasa]

You can get those from the IFF files.

The numbers in detail:

0 - Upper Body
1 - Lower Body
2 - Hat
3 - Gloves
4 - Shoes
5 - Accessory
6 - Boosters
7 - <undetermined>
8 - UCC Original
9 - UCC Copy

[Hikaru_K]

The group numbers did the trick! You have to add them in that table (dbo.TA_PARTS_GRP_LIMIT)

It works perfectly if you send the items via mail!!, but as a Game Master you can't send the items with the command /giveitem. The mail notification arrives but without any Item.

[top1]

I have a working version of The Command /giveitem . it's private

[bubbastic]

I have a working version of The Command /giveitem . it's private

LOL ...your command is private ? XD why do you taunt us ?

If you have it working then share it XD and while you are there... share the USP_item_recycle too LOL

[top1]

LOL ...your command is private ? XD why do you taunt us ?

If you have it working then share it XD and while you are there... share the USP_item_recycle too LOL

[chreadie]

The table that holds all items is incomplete and is probably the reason for the /giveitem command not working

Recycle_item is my next priority after making filexplorer write iff-files. Its not that far away.
Posted via Mobile Device

[baue]

not able to send item as GM for email
the message arrives as if it was already open and the item is received!
grateful if you would help me!!!!

[sky0165]

it works fine , but like the others , i can't send GM items , just the letter '_' , i want know if you think in config the Treasure Points '-'