续:--3
CREATE OR REPLACE PROCEDURE SMS_SPMobileUserOperation
(
usernumber char,
spnumber char,
smscontent char,
userlinkid char
)
AS smsflag int;
hasuser int;
hasyewu int; myywdm char(10);
myywlb int;
mydzmlz char(10);
mytdmlz char(10);
myxxf int;
myywjs1 char(100);
myywjs2 char(100);
myywjs3 char(100); mydbdzywdm char(10);
mymaxdzmsg int;
myid number;
mymsg char(160);begin
--取消定制
if(( smscontent='0000' ) or ( smscontent='00000' )) then
--更新用户信息
update SMS_DingzhiYonghu set dzflag=0, ffhm='CANCEL',tdsj=to_char(sysdate(),'yyyy-mm-dd'), tdfsflag=1
where YHHM=usernumber;
--更新发送短信记录表的状态
update SMS_SendRecord set msg_state=-100
where msg_mobile=usernumber and msg_state=0;
select SMS_SEND_MSG_ID.NEXTVAL into myid from dual;
--插入一条发送短信记录
insert into SMS_SendRecord values( myid, usernumber, '02188', '0000', '0000', sysdate(), 0, '您定制的业务已取消,咨询请拨打85111118', 0, 1, 0 );
return;
end if;--统计已有的业务
select count(1) into hasyewu
from SMS_Yewu
where JRHM=spnumber and (DZMLZ=smscontent or TDMLZ=smscontent);
--只有一条
if( hasyewu = 1) then
select YWDM, YWLB, DZMLZ, TDMLZ, XXF, YWJS1, YWJS2, YWJS3
into myywdm, myywlb, mydzmlz, mytdmlz, myxxf, myywjs1, myywjs2, myywjs3
from SMS_Yewu
where JRHM=spnumber and (DZMLZ=smscontent or TDMLZ=smscontent);
else
--多于一条的,则增加一条坏短信记录,并在发送记录中增加一条取消业务的提示信息
insert into SMS_BadMessage values( sysdate(), usernumber, spnumber, smscontent, userlinkid );
select SMS_SEND_MSG_ID.NEXTVAL into myid from dual;
insert into SMS_SendRecord values( myid, usernumber, spnumber, '0000', '0000', sysdate(), 0, '取消定制业务请回复4个0即:(0000),其它咨询请拨打85111118', 0, 1, 0 );
return;
end if;--统计已有用户
select count(1) into hasuser
from SMS_DingzhiYonghu
where YHHM=usernumber and SPNUMBER=spnumber and DZYWDM=myywdm;
--判断业务类别
if( myywlb = 2 ) then-- dingzhi
if( hasuser = 1 ) then
if( rtrim(mytdmlz) = rtrim(smscontent) ) then-- 退定命令字
smsflag := 3;--OLD_USER_CANCEL;
else
smsflag := 1;--NEW_USER_JOIN;
--smsflag := 2;--NEW_USER_VERITY;
end if;
else
smsflag := 1;--NEW_USER_JOIN;
end if;
else
smsflag := 4;--DIAN_BO_USER;
end if;--判断短信标志
if( smsflag = 1 ) then--user join
select SMS_SEND_MSG_ID.NEXTVAL into myid from dual;
delete SMS_DingzhiYonghu where YHHM=usernumber and DZYWDM=myywdm and SPNUMBER=spnumber;
insert into SMS_DingzhiYonghu values( usernumber, myywdm, 1, to_char(sysdate(),'yyyy-mm-dd'), 1, '9999-01-01', 0, 'jion', userlinkid, spnumber, smscontent );
insert into SMS_SendRecord values( myid, usernumber, spnumber, myywdm, userlinkid, sysdate(), 0, myywjs2, 0, 1, 0 );
elsif( smsflag = 3 ) then--user cancel
update SMS_DingzhiYonghu set dzflag=0, ffhm='cancel',
tdsj=to_char(sysdate(),'yyyy-mm-dd'), tdfsflag=1
where YHHM=usernumber and DZYWDM=myywdm and SPNUMBER=spnumber;
elsif( smsflag = 4 ) then--an tiao dian bo
select SMS_SEND_MSG_ID.NEXTVAL into myid from dual;
select distinct DZYWDM_DB into mydbdzywdm from SMS_YEWU where YWDM=myywdm;
if( mydbdzywdm = ' ' ) then
mydbdzywdm := myywdm;
end if;
select max(msg_id) into mymaxdzmsg from SMS_CONTENT where MSG_YWDM=mydbdzywdm;
select msg_content into mymsg from SMS_CONTENT where MSG_YWDM=mydbdzywdm and msg_id=mymaxdzmsg;
mymsg := replace( mymsg, chr(10), ';' );
mymsg := replace( mymsg, chr(13), ';' );
insert into SMS_SendRecord values( myid, usernumber, spnumber, myywdm, userlinkid, sysdate(), 0, mymsg, myxxf, 2, 2 );
end if;end SMS_SPMobileUserOperation;
CREATE OR REPLACE PROCEDURE SMS_SPMobileUserOperation
(
usernumber char,
spnumber char,
smscontent char,
userlinkid char
)
AS smsflag int;
hasuser int;
hasyewu int; myywdm char(10);
myywlb int;
mydzmlz char(10);
mytdmlz char(10);
myxxf int;
myywjs1 char(100);
myywjs2 char(100);
myywjs3 char(100); mydbdzywdm char(10);
mymaxdzmsg int;
myid number;
mymsg char(160);begin
--取消定制
if(( smscontent='0000' ) or ( smscontent='00000' )) then
--更新用户信息
update SMS_DingzhiYonghu set dzflag=0, ffhm='CANCEL',tdsj=to_char(sysdate(),'yyyy-mm-dd'), tdfsflag=1
where YHHM=usernumber;
--更新发送短信记录表的状态
update SMS_SendRecord set msg_state=-100
where msg_mobile=usernumber and msg_state=0;
select SMS_SEND_MSG_ID.NEXTVAL into myid from dual;
--插入一条发送短信记录
insert into SMS_SendRecord values( myid, usernumber, '02188', '0000', '0000', sysdate(), 0, '您定制的业务已取消,咨询请拨打85111118', 0, 1, 0 );
return;
end if;--统计已有的业务
select count(1) into hasyewu
from SMS_Yewu
where JRHM=spnumber and (DZMLZ=smscontent or TDMLZ=smscontent);
--只有一条
if( hasyewu = 1) then
select YWDM, YWLB, DZMLZ, TDMLZ, XXF, YWJS1, YWJS2, YWJS3
into myywdm, myywlb, mydzmlz, mytdmlz, myxxf, myywjs1, myywjs2, myywjs3
from SMS_Yewu
where JRHM=spnumber and (DZMLZ=smscontent or TDMLZ=smscontent);
else
--多于一条的,则增加一条坏短信记录,并在发送记录中增加一条取消业务的提示信息
insert into SMS_BadMessage values( sysdate(), usernumber, spnumber, smscontent, userlinkid );
select SMS_SEND_MSG_ID.NEXTVAL into myid from dual;
insert into SMS_SendRecord values( myid, usernumber, spnumber, '0000', '0000', sysdate(), 0, '取消定制业务请回复4个0即:(0000),其它咨询请拨打85111118', 0, 1, 0 );
return;
end if;--统计已有用户
select count(1) into hasuser
from SMS_DingzhiYonghu
where YHHM=usernumber and SPNUMBER=spnumber and DZYWDM=myywdm;
--判断业务类别
if( myywlb = 2 ) then-- dingzhi
if( hasuser = 1 ) then
if( rtrim(mytdmlz) = rtrim(smscontent) ) then-- 退定命令字
smsflag := 3;--OLD_USER_CANCEL;
else
smsflag := 1;--NEW_USER_JOIN;
--smsflag := 2;--NEW_USER_VERITY;
end if;
else
smsflag := 1;--NEW_USER_JOIN;
end if;
else
smsflag := 4;--DIAN_BO_USER;
end if;--判断短信标志
if( smsflag = 1 ) then--user join
select SMS_SEND_MSG_ID.NEXTVAL into myid from dual;
delete SMS_DingzhiYonghu where YHHM=usernumber and DZYWDM=myywdm and SPNUMBER=spnumber;
insert into SMS_DingzhiYonghu values( usernumber, myywdm, 1, to_char(sysdate(),'yyyy-mm-dd'), 1, '9999-01-01', 0, 'jion', userlinkid, spnumber, smscontent );
insert into SMS_SendRecord values( myid, usernumber, spnumber, myywdm, userlinkid, sysdate(), 0, myywjs2, 0, 1, 0 );
elsif( smsflag = 3 ) then--user cancel
update SMS_DingzhiYonghu set dzflag=0, ffhm='cancel',
tdsj=to_char(sysdate(),'yyyy-mm-dd'), tdfsflag=1
where YHHM=usernumber and DZYWDM=myywdm and SPNUMBER=spnumber;
elsif( smsflag = 4 ) then--an tiao dian bo
select SMS_SEND_MSG_ID.NEXTVAL into myid from dual;
select distinct DZYWDM_DB into mydbdzywdm from SMS_YEWU where YWDM=myywdm;
if( mydbdzywdm = ' ' ) then
mydbdzywdm := myywdm;
end if;
select max(msg_id) into mymaxdzmsg from SMS_CONTENT where MSG_YWDM=mydbdzywdm;
select msg_content into mymsg from SMS_CONTENT where MSG_YWDM=mydbdzywdm and msg_id=mymaxdzmsg;
mymsg := replace( mymsg, chr(10), ';' );
mymsg := replace( mymsg, chr(13), ';' );
insert into SMS_SendRecord values( myid, usernumber, spnumber, myywdm, userlinkid, sysdate(), 0, mymsg, myxxf, 2, 2 );
end if;end SMS_SPMobileUserOperation;
create or replace procedure xml_file_field_process( myinstr varchar2 )
as TranID varchar2(2000);
MsgType varchar2(200); SendAddr varchar2(1000);
SendDevID varchar2(200);
SendDevType varchar2(200); DestAddr varchar2(1000);
DestDevID varchar2(200);
DestDevType varchar2(200); FeeUser varchar2(1000);
FUIDType varchar2(200);
FUIDNumber varchar2(200); DestUser varchar2(1000);
DUIDType varchar2(200);
DUIDNumber varchar2(200); LinkID varchar2(200);
ActionID varchar2(200);
ActionRID varchar2(200);
SPID varchar2(200);
SPywdmID varchar2(200);
AccessMode varchar2(200);
FeatureStr varchar2(200); mydzmlz char(10);
mytdmlz char(10);
begin TranID := get_xml_Field( myinstr, 'TransactionID' );
MsgType := get_xml_Field( myinstr, 'MsgType' );
SendAddr := get_xml_Field( myinstr, 'Send_Address' );
SendDevID := get_xml_Field( SendAddr, 'DeviceID' );
SendDevType := get_xml_Field( SendAddr, 'DeviceType' ); DestAddr := get_xml_Field( myinstr, 'Dest_Address' );
DestDevID := get_xml_Field( DestAddr, 'DeviceID' );
DestDevType := get_xml_Field( DestAddr, 'DeviceType' ); FeeUser := get_xml_Field( myinstr, 'FeeUser_ID' );
FUIDType := get_xml_Field( FeeUser, 'UserIDType' );
FUIDNumber := get_xml_Field( FeeUser, 'MSISDN' ); DestUser := get_xml_Field( myinstr, 'DestUser_ID' );
DUIDType := get_xml_Field( DestUser, 'UserIDType' );
DUIDNumber := get_xml_Field( DestUser, 'MSISDN' ); LinkID := get_xml_Field( myinstr, 'LinkID' );
ActionID := get_xml_Field( myinstr, 'ActionID' );
ActionRID := get_xml_Field( myinstr, 'ActionReasonID' );
SPID := get_xml_Field( myinstr, 'SPID' );
SPywdmID := get_xml_Field( myinstr, 'SPServiceID' );
AccessMode := get_xml_Field( myinstr, 'AccessMode' );
FeatureStr := get_xml_Field( myinstr, 'FeatureStr' ); SPywdmID := substr( SPywdmID, 2 );
select DZMLZ,TDMLZ into mydzmlz,mytdmlz
from SMS_YEWU where rtrim(JRHM)='02188' and rtrim(YWDM)=SPywdmID;
if( MsgType = 'SyncOrderRelationReq' ) then
if( ActionID = '1' ) then
SMS_SPMobileUserOperation( DUIDNumber, '02188', mydzmlz, LinkID );
delete sms_cmpp3_xml;
insert into sms_cmpp3_xml values( TranID, 'SyncOrderRelationResp', 0 );
elsif( ActionID = '2' ) then
SMS_SPMobileUserOperation( DUIDNumber, '02188', mytdmlz, LinkID );
delete sms_cmpp3_xml;
insert into sms_cmpp3_xml values( TranID, 'SyncOrderRelationResp', 0 );
else
SMS_SPMobileUserOperation( DUIDNumber, '02188', '0880', LinkID );
delete sms_cmpp3_xml;
insert into sms_cmpp3_xml values( TranID, 'SyncOrderRelationResp', 0 );
end if;
end if;
end xml_file_field_process;
(
@myinstr VARCHAR(4000) ,
@fieldname VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN
-- local variables here
DECLARE @Result VARCHAR(200)
DECLARE @head VARCHAR(200)
DECLARE @tail VARCHAR(200)
DECLARE @ptr1 INTEGER
DECLARE @ptr2 INTEGER
SELECT @head = '<' + @fieldname
SELECT @tail = '</' + @fieldname + '>'
SELECT @ptr1 = CHARINDEX(@head, @myinstr, 1)
SELECT @ptr2 = CHARINDEX(@tail, @myinstr, 1)
IF ( ( @ptr1 > 0 ) and ( @ptr2 > 2 ) )
BEGIN
SELECT @Result = SUBSTRING(@myinstr, @ptr1 + LEN(@head) + 1, @ptr2 - @ptr1 - (LEN(@head) + 1))
END
ELSE
BEGIN
SELECT @Result = 'no this field'
END
return (@Result) END
GO
(
@myinstr VARCHAR(4000) ,
@fieldname VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN
-- local variables here
DECLARE @Result VARCHAR(200)
DECLARE @head VARCHAR(200)
DECLARE @tail VARCHAR(200)
DECLARE @ptr1 INTEGER
DECLARE @ptr2 INTEGER
SELECT @head = '<' + @fieldname
SELECT @tail = '</' + @fieldname + '>'
SELECT @ptr1 = CHARINDEX(@head, @myinstr, 1)
SELECT @ptr2 = CHARINDEX(@tail, @myinstr, 1)
IF ( ( @ptr1 > 0 ) and ( @ptr2 > 2 ) )
BEGIN
SELECT @Result = SUBSTRING(@myinstr, @ptr1, @ptr2 - @ptr1 + LEN(@tail))
END
ELSE
BEGIN
SELECT @Result = 'no this field'
END
return (@Result) END
GO
CREATE PROCEDURE SMS_Send_Message_Out
@inid INT ,
@outnum INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @myywdm char(10)
DECLARE @mymsg char(160)
DECLARE @myid NUMERIC(38) DECLARE mymobile cursor FOR
SELECT
YHHM,
LINKID,
SPNUMBER
FROM SMS_DingZhiYongHu
WHERE DZYWDM = @myywdm
AND DZFLAG = 1
DECLARE myyewusp cursor FOR
SELECT
XXFJFLX,
XXF,
JRHM
FROM SMS_YEWU
WHERE YWDM = @myywdm
SELECT
@myywdm = MSG_YWDM,
@mymsg = MSG_CONTENT
FROM SMS_CONTENT
WHERE MSG_ID = @inid
AND MSG_STATE = 0
OPEN myyewusp
FETCH NEXT FROM myyewusp INTO
WHILE (@@FETCH_STATUS <> -1) BEGIN
OPEN mymobile
FETCH NEXT FROM mymobile INTO
WHILE (@@FETCH_STATUS <> -1) BEGIN
IF ( RTRIM(mysp.JRHM)= RTRIM(myyh.SPNUMBER))
BEGIN
SELECT @myid = SMS_SEND_MSG_ID.NEXTVAL
INSERT INTO SMS_SENDRECORD
VALUES ( @myid ,
myyh.YHHM ,
mysp.JRHM ,
@myywdm ,
' ' ,
GETDATE() ,
0 ,
@mymsg ,
mysp.XXF ,
mysp.XXFJFLX ,
2 )
END
FETCH NEXT FROM mymobile INTO
END
CLOSE mymobile
DEALLOCATE mymobile
FETCH NEXT FROM myyewusp INTO
END
CLOSE myyewusp
DEALLOCATE myyewusp
UPDATE SMS_CONTENT
SET MSG_STATE = 1
WHERE MSG_ID = @inid
SET NOCOUNT OFF END
GO
修正:
FETCH NEXT FROM mymobile INTO
和
FETCH NEXT FROM myyewusp INTO
去掉!
CREATE PROCEDURE SMS_Send_Short_Message
@inid INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @myywdm char(10)
DECLARE @mymsg char(160)
DECLARE @myid NUMERIC(38) DECLARE mymobile cursor FOR
SELECT
YHHM,
LINKID,
SPNUMBER
FROM SMS_DingZhiYongHu
WHERE DZYWDM = @myywdm
AND DZFLAG = 1
DECLARE myyewusp cursor FOR
SELECT
XXFJFLX,
XXF,
JRHM
FROM SMS_YEWU
WHERE YWDM = @myywdm
SELECT
@myywdm = MSG_YWDM,
@mymsg = RTRIM(MSG_CONTENT)
FROM SMS_CONTENT
WHERE MSG_ID = @inid
AND MSG_STATE = 0
SELECT @mymsg = REPLACE(@mymsg, CHAR(10), ';')
SELECT @mymsg = REPLACE(@mymsg, CHAR(13), ';')
OPEN myyewusp
WHILE (@@FETCH_STATUS <> -1) BEGIN
OPEN mymobile
WHILE (@@FETCH_STATUS <> -1) BEGIN
IF ( RTRIM(mysp.JRHM)= RTRIM(myyh.SPNUMBER))
BEGIN
SELECT @myid = SMS_SEND_MSG_ID.NEXTVAL
INSERT INTO SMS_SENDRECORD
VALUES ( @myid ,
myyh.YHHM ,
mysp.JRHM ,
@myywdm ,
' ' ,
GETDATE() ,
0 ,
@mymsg ,
mysp.XXF ,
mysp.XXFJFLX ,
2 )
END
END
CLOSE mymobile
DEALLOCATE mymobile
END
CLOSE myyewusp
DEALLOCATE myyewusp
UPDATE SMS_CONTENT
SET MSG_STATE = 1
WHERE MSG_ID = @inid
SET NOCOUNT OFF END
GOCREATE PROCEDURE SMS_SPMobileUserOperation
@usernumber char ,
@spnumber char ,
@smscontent char ,
@userlinkid char
AS
DECLARE @LoopProcessingFlag BIT
BEGIN
SET NOCOUNT ON
DECLARE @smsflag int
DECLARE @hasuser int
DECLARE @hasyewu int
DECLARE @myywdm char(10)
DECLARE @myywlb int
DECLARE @mydzmlz char(10)
DECLARE @mytdmlz char(10)
DECLARE @myxxf int
DECLARE @myywjs1 char(100)
DECLARE @myywjs2 char(100)
DECLARE @myywjs3 char(100)
DECLARE @mydbdzywdm char(10)
DECLARE @mymaxdzmsg int
DECLARE @myid INT
DECLARE @mymsg char(160)
--取消定制
IF ( ( @smscontent = '0000' ) or ( @smscontent = '00000' ) )
BEGIN
UPDATE SMS_DingzhiYonghu
SET dzflag = 0,
ffhm = 'CANCEL',
tdsj = CONVERT(DATETIME, CONVERT(VARCHAR(23), DBO.sysdate(), 112)),
tdfsflag = 1
WHERE YHHM = @usernumber
UPDATE SMS_SendRecord
SET msg_state = - 100
WHERE msg_mobile = @usernumber
AND msg_state = 0
SELECT @myid = SMS_SEND_MSG_ID.NEXTVAL
INSERT INTO SMS_SendRecord
VALUES ( @myid ,
@usernumber ,
'02188' ,
'0000' ,
'0000' ,
DBO.sysdate() ,
0 ,
'您定制的业务已取消,咨询请拨打85111118' ,
0 ,
1 ,
0 )
return
END
SELECT @hasyewu = COUNT(1)
FROM SMS_Yewu
WHERE JRHM = @spnumber
AND (DZMLZ = @smscontent
OR TDMLZ = @smscontent)
--只有一条
IF ( @hasyewu = 1 )
BEGIN
SELECT
@myywdm = YWDM,
@myywlb = YWLB,
@mydzmlz = DZMLZ,
@mytdmlz = TDMLZ,
@myxxf = XXF,
@myywjs1 = YWJS1,
@myywjs2 = YWJS2,
@myywjs3 = YWJS3
FROM SMS_Yewu
WHERE JRHM = @spnumber
AND (DZMLZ = @smscontent
OR TDMLZ = @smscontent)
END
ELSE
BEGIN
INSERT INTO SMS_BadMessage
VALUES ( DBO.sysdate() ,
@usernumber ,
@spnumber ,
@smscontent ,
@userlinkid )
SELECT @myid = SMS_SEND_MSG_ID.NEXTVAL
INSERT INTO SMS_SendRecord
VALUES ( @myid ,
@usernumber ,
@spnumber ,
'0000' ,
'0000' ,
DBO.sysdate() ,
0 ,
'取消定制业务请回复4个0即:(0000),其它咨询请拨打85111118' ,
0 ,
1 ,
0 )
return
END
SELECT @hasuser = COUNT(1)
FROM SMS_DingzhiYonghu
WHERE YHHM = @usernumber
AND @SPNUMBER = @spnumber
AND DZYWDM = @myywdm
--判断业务类别
IF ( @myywlb = 2 )
BEGIN
-- dingzhi
IF ( @hasuser = 1 )
BEGIN
IF ( RTRIM(@mytdmlz)= RTRIM(@smscontent))
BEGIN
-- 退定命令字
SELECT @smsflag = 3
END
ELSE
BEGIN
SELECT @smsflag = 1
END
END
ELSE
BEGIN
SELECT @smsflag = 1
END
END
ELSE
BEGIN
SELECT @smsflag = 4
END
--判断短信标志
SET @LoopProcessingFlag = 0
IF ( @smsflag = 1 )
BEGIN
SELECT @myid = SMS_SEND_MSG_ID.NEXTVAL
DELETE SMS_DingzhiYonghu
WHERE YHHM = @usernumber
AND DZYWDM = @myywdm
AND @SPNUMBER = @spnumber
INSERT INTO SMS_DingzhiYonghu
VALUES ( @usernumber ,
@myywdm ,
1 ,
CONVERT(DATETIME, CONVERT(VARCHAR(23), DBO.sysdate(), 112)) ,
1 ,
'9999-01-01' ,
0 ,
'jion' ,
@userlinkid ,
@spnumber ,
@smscontent )
INSERT INTO SMS_SendRecord
VALUES ( @myid ,
@usernumber ,
@spnumber ,
@myywdm ,
@userlinkid ,
DBO.sysdate() ,
0 ,
@myywjs2 ,
0 ,
1 ,
0 )
SET @LoopProcessingFlag = 1
END
IF ( @smsflag = 3 ) AND (@LoopProcessingFlag = 0)
BEGIN
UPDATE SMS_DingzhiYonghu
SET dzflag = 0,
ffhm = 'cancel',
tdsj = CONVERT(DATETIME, CONVERT(VARCHAR(23), DBO.sysdate(), 112)),
tdfsflag = 1
WHERE YHHM = @usernumber
AND DZYWDM = @myywdm
AND @SPNUMBER = @spnumber
SET @LoopProcessingFlag = 1
END
IF ( @smsflag = 4 ) AND (@LoopProcessingFlag = 0)
BEGIN
SELECT @myid = SMS_SEND_MSG_ID.NEXTVAL
SELECT DISTINCT @mydbdzywdm = DZYWDM_DB
FROM SMS_YEWU
WHERE YWDM = @myywdm
IF ( @mydbdzywdm = ' ' )
BEGIN
SELECT @mydbdzywdm = @myywdm
END
SELECT @mymaxdzmsg = MAX(msg_id)
FROM SMS_CONTENT
WHERE MSG_YWDM = @mydbdzywdm
SELECT @mymsg = msg_content
FROM SMS_CONTENT
WHERE MSG_YWDM = @mydbdzywdm
AND msg_id = @mymaxdzmsg
SELECT @mymsg = REPLACE(@mymsg, CHAR(10), ';')
SELECT @mymsg = REPLACE(@mymsg, CHAR(13), ';')
INSERT INTO SMS_SendRecord
VALUES ( @myid ,
@usernumber ,
@spnumber ,
@myywdm ,
@userlinkid ,
DBO.sysdate() ,
0 ,
@mymsg ,
@myxxf ,
2 ,
2 )
END
SET NOCOUNT OFF END
GO
@myinstr VARCHAR(4000)
AS
DECLARE @LoopProcessingFlag BIT
BEGIN
SET NOCOUNT ON
DECLARE @TranID VARCHAR(2000)
DECLARE @MsgType VARCHAR(200)
DECLARE @SendAddr VARCHAR(1000)
DECLARE @SendDevID VARCHAR(200)
DECLARE @SendDevType VARCHAR(200)
DECLARE @DestAddr VARCHAR(1000)
DECLARE @DestDevID VARCHAR(200)
DECLARE @DestDevType VARCHAR(200)
DECLARE @FeeUser VARCHAR(1000)
DECLARE @FUIDType VARCHAR(200)
DECLARE @FUIDNumber VARCHAR(200)
DECLARE @DestUser VARCHAR(1000)
DECLARE @DUIDType VARCHAR(200)
DECLARE @DUIDNumber VARCHAR(200)
DECLARE @LinkID VARCHAR(200)
DECLARE @ActionID VARCHAR(200)
DECLARE @ActionRID VARCHAR(200)
DECLARE @SPID VARCHAR(200)
DECLARE @SPywdmID VARCHAR(200)
DECLARE @AccessMode VARCHAR(200)
DECLARE @FeatureStr VARCHAR(200)
DECLARE @mydzmlz char(10)
DECLARE @mytdmlz char(10)
SELECT @TranID = DBO.get_xml_Field(@myinstr, 'TransactionID')
SELECT @MsgType = DBO.get_xml_Field(@myinstr, 'MsgType')
SELECT @SendAddr = DBO.get_xml_Field(@myinstr, 'Send_Address')
SELECT @SendDevID = DBO.get_xml_Field(@SendAddr, 'DeviceID')
SELECT @SendDevType = DBO.get_xml_Field(@SendAddr, 'DeviceType')
SELECT @DestAddr = DBO.get_xml_Field(@myinstr, 'Dest_Address')
SELECT @DestDevID = DBO.get_xml_Field(@DestAddr, 'DeviceID')
SELECT @DestDevType = DBO.get_xml_Field(@DestAddr, 'DeviceType')
SELECT @FeeUser = DBO.get_xml_Field(@myinstr, 'FeeUser_ID')
SELECT @FUIDType = DBO.get_xml_Field(@FeeUser, 'UserIDType')
SELECT @FUIDNumber = DBO.get_xml_Field(@FeeUser, 'MSISDN')
SELECT @DestUser = DBO.get_xml_Field(@myinstr, 'DestUser_ID')
SELECT @DUIDType = DBO.get_xml_Field(@DestUser, 'UserIDType')
SELECT @DUIDNumber = DBO.get_xml_Field(@DestUser, 'MSISDN')
SELECT @LinkID = DBO.get_xml_Field(@myinstr, 'LinkID')
SELECT @ActionID = DBO.get_xml_Field(@myinstr, 'ActionID')
SELECT @ActionRID = DBO.get_xml_Field(@myinstr, 'ActionReasonID')
SELECT @SPID = DBO.get_xml_Field(@myinstr, 'SPID')
SELECT @SPywdmID = DBO.get_xml_Field(@myinstr, 'SPServiceID')
SELECT @AccessMode = DBO.get_xml_Field(@myinstr, 'AccessMode')
SELECT @FeatureStr = DBO.get_xml_Field(@myinstr, 'FeatureStr')
SELECT @SPywdmID = SUBSTRING(@SPywdmID, 2, LEN(@SPywdmID))
SELECT
@mydzmlz = DZMLZ,
@mytdmlz = TDMLZ
FROM SMS_YEWU
WHERE RTRIM(JRHM) = '02188'
AND RTRIM(YWDM) = @SPywdmID
IF ( @MsgType = 'SyncOrderRelationReq' )
BEGIN
SET @LoopProcessingFlag = 0
IF ( @ActionID = '1' )
BEGIN
EXEC SMS_SPMOBILEUSEROPERATION @DUIDNumber , '02188' , @mydzmlz , @LinkID
DELETE sms_cmpp3_xml
INSERT INTO sms_cmpp3_xml
VALUES ( @TranID ,
'SyncOrderRelationResp' ,
0 )
SET @LoopProcessingFlag = 1
END
IF ( @ActionID = '2' ) AND (@LoopProcessingFlag = 0)
BEGIN
EXEC SMS_SPMOBILEUSEROPERATION @DUIDNumber , '02188' , @mytdmlz , @LinkID
DELETE sms_cmpp3_xml
INSERT INTO sms_cmpp3_xml
VALUES ( @TranID ,
'SyncOrderRelationResp' ,
0 )
SET @LoopProcessingFlag = 1
END
IF (@LoopProcessingFlag = 0)
BEGIN
EXEC SMS_SPMOBILEUSEROPERATION @DUIDNumber , '02188' , '0880' , @LinkID
DELETE sms_cmpp3_xml
INSERT INTO sms_cmpp3_xml
VALUES ( @TranID ,
'SyncOrderRelationResp' ,
0 )
END
END
SET NOCOUNT OFF END
GO
得分非你莫属了