sql-tools/从通道恢复短信历史.sql

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 '%<EFBFBD><EFBFBD>֤<EFBFBD><EFBFBD>%'

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