insert into tb_sendmessage(id,biztype,isRevertible,title,msgtype,receivers,ffuncnumber,issent,createtime,fsendstate)Values(‘DB000000000001‘,‘k3-9AB20E12-60C4-448C-B60D-6DFC8BA7AC77‘,0,‘还款提醒：XXX先生/女士，您的融资XX应还租金人民币15,300.00元，本期还款日为8月20日。若已还款请忽略。‘,0,‘接收人手机号码‘,‘K3V10.3YDSW‘,0,‘2014-08-29 10:26:09.937‘,0)insert into t_smsmsglog(fsender,freceiver,facctid,frecvphonenum,fsendphonenum,fsendtime,freceivetime,fcontent,fmidserver,frecman,frecmantype,fid)values(0,-1,2,‘接收人手机号码‘,‘短信中心‘,‘2014-08-29 10:26:09.937‘,‘1900-01-01 00:00:00.000‘,‘还款提醒：XXX先生/女士，您的融资XX应还租金人民币15,300.00元，本期还款日为8月20日。若已还款请忽略。‘,‘服务器IP地址‘,‘接收人姓名‘,1,‘DB000000000001‘)

IF EXISTS(SELECT * FROM sysobjects WHERE NAME=‘pro_sms_ContractSchemeOutDateWarn‘ AND TYPE=‘p‘)DROP PROCEDURE pro_sms_ContractSchemeOutDateWarnGOCREATE PROC pro_sms_ContractSchemeOutDateWarn    @OutDay int       --过期提醒的天数ASSET NOCOUNT ONCREATE TABLE #ContractTemp (    FID int identity(1,1),    FContractNO varchar(50),    FCustomerName varchar(50),    FSex varchar(50),    FMobilePhone varchar(50),    FReceiveDate datetime,    FAmountFor decimal(18,2))--版本一：2014-8-31--INSERT INTO #ContractTemp--SELECT b.FContractNO,c.FName as FCustomerName,c.F_102 as FSex,c.FMobilePhone,a.FReceiveDate,a.FAmountFor from AIS20130715142849.dbo.t_RPContractScheme a --left join AIS20130715142849.dbo.t_RPContract b on a.FContractID=b.FContractID--Left Join AIS20130715142849.dbo.T_Organization c On b.FCustomer=c.FItemID --where datediff(day,getdate(),a.freceivedate)=@OutDay and b.FStatus<>2 and b.FStatus<>1 and c.FMobilePhone is not null--SELECT * FROM #ContractTemp--版本二：2014-9-5，纠正“短信发送晚于付款时间2日”的错误；排除已结租合同（包括已审核、已关闭、实收金额合计累计+保证金>=应收金额累计）INSERT INTO #ContractTempselect t.FContractNO,c.FName as FCustomerName,c.F_102 as FSex,c.FMobilePhone,t1.FReceiveDate,t1.FAmountFor from AIS20130715142849.dbo.t_RPContractScheme t1left join (select a.FContractID,a.FContractNO,a.FStatus,a.Fcustomer,a.FDecimal,sum(b.famountfor) as famountfortotal,sum(famount12) as famount12total from AIS20130715142849.dbo.t_rpcontract a left join AIS20130715142849.dbo.t_rpcontractscheme b on a.fcontractid=b.fcontractid where a.fstatus<>1 and a.fstatus<>2group by a.FContractID,a.fcontractno,a.FStatus,a.FCustomer,a.FDecimal) t ON t1.FContractID=t.FContractIDLeft Join AIS20130715142849.dbo.T_Organization c On t.FCustomer=c.FItemID where t.FAmount12Total+t.FDecimal<t.FAmountForTotal and t.FStatus<>1 and t.FStatus<>2and datediff(day,getdate(),t1.freceivedate)=@OutDay and c.FMobilePhone is not nullDECLARE @DBID varchar(50)DECLARE @curIndex intDECLARE @totalRows intDECLARE @FCustomerName varchar(50)DECLARE @FSex varchar(50)DECLARE @FMobilePhone varchar(50)DECLARE @FReceiveDate datetimeDECLARE @FAmountFor varchar(50)DECLARE @FContractNO varchar(50)SELECT @curIndex=1SELECT @totalRows=COUNT(1) FROM #ContractTempWHILE(@curIndex<=@totalRows)BEGINSELECT @DBID=nullSELECT @FCustomerName=nullSELECT @FSex=nullSELECT @FMobilePhone=nullSELECT @FReceiveDate=nullSELECT @FAmountFor=nullSELECT @FContractNO=nullSELECT @FCustomerName=FCustomerName FROM #ContractTemp WHERE FID=@curIndexSELECT @FSex=CASE FSex WHEN 0 THEN ‘女士‘ WHEN 1 THEN ‘先生‘ ELSE ‘先生/女士‘ END FROM #ContractTemp WHERE FID=@curIndexSELECT @FMobilePhone=FMobilePhone FROM #ContractTemp WHERE FID=@curIndexSELECT @FReceiveDate=FReceiveDate FROM #ContractTemp WHERE FID=@curIndexSELECT @FAmountFor=FAmountFor FROM #ContractTemp WHERE FID=@curIndexSELECT @FAmountFor=CONVERT(varchar,CONVERT(money,@FAmountFor),1)SELECT @FContractNO=FContractNO FROM #ContractTemp WHERE FID=@curIndexSELECT @DBID=MAX(FID) FROM t_DBIDSELECT @DBID=‘DB‘+REPLACE(SPACE(12-LEN(@DBID)),‘ ‘,‘0‘)+CONVERT(varchar(50),@DBID+1)INSERT INTO TB_SENDMESSAGE(ID,bizType,isRevertible,title,msgType,receivers,FFuncNumber) VALUES(@DBID,‘k3-9AB20E12-60C4-448C-B60D-6DFC8BA7AC77‘,0,‘还款提醒:‘+@FCustomerName+@FSex+‘,您的XX融资合同<‘+@FContractNO+‘>本期应还￥‘+@FAmountFor+‘元,还款日为‘+CONVERT(varchar,MONTH(@FReceiveDate))+‘月‘+CONVERT(varchar,DAY(@FReceiveDate))+‘日.若已还款请忽略.‘,0,‘13774534210,13850040496,13959240595‘,‘K3V10.3YDSW‘)INSERT INTO T_SMSMSGLOG(FID,FSender,FReceiver,FAcctID,FRecvPhoneNum,FSendPhoneNum,FContent,FSendTime,FReceiveTime,FMidServer,FRecMan,FRecManType) VALUES(@DBID,0,-1,2,‘13774534210,13850040496,13959240595‘,‘短信中心‘,‘还款提醒:‘+@FCustomerName+@FSex+‘,您的XX融资合同<‘+@FContractNO+‘>本期应还￥‘+@FAmountFor+‘元,还款日为‘+CONVERT(varchar,MONTH(@FReceiveDate))+‘月‘+CONVERT(varchar,DAY(@FReceiveDate))+‘日.若已还款请忽略.‘,GetDate(),0,‘127.0.0.1‘,@FCustomerName,1)INSERT INTO T_DBID(FType)VALUES(‘sms‘)SELECT @curIndex=@curIndex+1ENDDROP TABLE #ContractTempGO--exec pro_sms_ContractSchemeOutDateWarn 2--select * from t_dbid--select * from TB_SENDMESSAGE where id like ‘DB%‘--select * from t_smsmsglog where fid like ‘DB%‘--delete from TB_SENDMESSAGE--delete from t_smsmsglog