You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
94 lines
2.4 KiB
94 lines
2.4 KiB
use main
|
|
|
|
SELECT AccountId FROM main.dbo.TSMSHistEx
|
|
GROUP BY AccountId
|
|
|
|
DECLARE @ClientNO varchar(100)
|
|
SELECT @ClientNO = FCLientNO FROM Main.dbo.TClientInfo
|
|
WHERE FClientId IN(SELECT FClientId FROM Main.dbo.TSMSAccount WHERE FOrigUserName = 'dl0537pufaxing')
|
|
EXEC main.dbo.PGetConnInfo @ClientNO
|
|
|
|
SELECT * FROM Main.
|
|
|
|
use JiNing_PuFaXing
|
|
|
|
DROP TABLE #SMSHist
|
|
|
|
DELETE TSMSHist WHERE FSmsId IN(SELECT msgId FROM main.dbo.TSMSHistEx)
|
|
|
|
SELECT CAST( 0 AS bigint) AS FCompId,CAST('' AS VARCHAR(100)) AS FSource,*INTO #SMSHist FROM main.dbo.TSMSHistEx
|
|
WHERE accountId = 'dl0537pufaxing'
|
|
AND MsgId NOT IN(SELECT FSMSId FROM TSMSHist)
|
|
|
|
SELECT * FROM #SMSHist
|
|
|
|
UPDATE #SMSHist SET FCompId = 1 WHERE MsgContent LIKE '%ÑéÖ¤Âë%'
|
|
|
|
ALTER TABLE #SMSHist ADD FBillType VARCHAR(100),FBillid BIGINT
|
|
Go
|
|
UPDATE A
|
|
SET A.FCompId = B.FCompId,
|
|
A.FBillType = B.FBillType,
|
|
A.FBillId = B.FTransId
|
|
FROM #SMSHist AS A, TMemberTransHist AS B,TMember AS M
|
|
WHERE A.MobilePhoneSet = M.FMobilePhone
|
|
AND B.FMemId = M.FMemId
|
|
AND DATEDIFF(MINUTE, B.FTime ,A.CommitTime) < 2
|
|
AND A.FCompId = 0
|
|
|
|
|
|
UPDATE #SMSHist SET FSource = 'Consume' WHERE FBillType = 'XF'
|
|
|
|
UPDATE A
|
|
SET FSource = 'CardRchg'
|
|
FROM #SMSHist AS A,TMemberSaleBill AS B
|
|
WHERE A.FBillId = B.FBillId
|
|
AND A.FBillType = 'KK'
|
|
AND B.FBizType ='CardRchg'
|
|
|
|
UPDATE A
|
|
SET FSource = 'CardSaleNewMember'
|
|
FROM #SMSHist AS A,TMemberSaleBill AS B
|
|
WHERE A.FBillId = B.FBillId
|
|
AND A.FBillType = 'KK'
|
|
AND B.FBizType ='CardSale'
|
|
AND B.FMemState = 1
|
|
|
|
UPDATE A
|
|
SET FSource = 'CardSaleOldMember'
|
|
FROM #SMSHist AS A,TMemberSaleBill AS B
|
|
WHERE A.FBillId = B.FBillId
|
|
AND A.FBillType = 'KK'
|
|
AND B.FBizType ='CardSale'
|
|
AND B.FMemState = 2
|
|
|
|
|
|
UPDATE A
|
|
SET FSource = 'CourseSaleNewMember'
|
|
FROM #SMSHist AS A,TMemberSaleBill AS B
|
|
WHERE A.FBillId = B.FBillId
|
|
AND A.FBillType = 'KK'
|
|
AND B.FBizType IN('CourseSale' ,'CoursePackSale')
|
|
AND B.FMemState = 1
|
|
|
|
|
|
UPDATE A
|
|
SET FSource = 'CourseSaleOldMember'
|
|
FROM #SMSHist AS A,TMemberSaleBill AS B
|
|
WHERE A.FBillId = B.FBillId
|
|
AND A.FBillType = 'KK'
|
|
AND B.FBizType IN('CourseSale' ,'CoursePackSale')
|
|
AND B.FMemState = 2
|
|
|
|
|
|
|
|
|
|
INSERT INTO TSMSHist(FSpId,FPhone,FSmsId,FCompId,FTime,FContent,FStatus,FDelivrdTime,FError,FRetry,FSource,FUserId,FRecordTime)
|
|
SELECT dbo.FNNewId(),MobilePhoneSet,MsgId,FCompId,CommitTime,MsgContent,1,null,'',0,FSource,0,CommitTime
|
|
FROM #SMSHist
|
|
WHERE 1=1
|
|
|
|
|
|
|
|
|
|
|