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
