短信模板(在SQL中利用replace函数完成替换)
[OwnerName][OwnerTitle],您好!我是[ProjectName]保险公司业务员[UserName],您的[QuoteContent],不清楚可联系服务电话:(公司[ProjectContactPhone])或(个人[UserMobilePhone]),谢谢您的查收!祝您身体健康!
任务:将[……]的内容用实际内容进行替换
代码:
string sql = "DECLARE @OwnerMobile VARCHAR(250) \r\n";
sql += "DECLARE @OwnerName VARCHAR(250) \r\n";
sql += "DECLARE @OwnerTitle VARCHAR(250) \r\n";
sql += "DECLARE @ProjectName VARCHAR(100) \r\n";
sql += "DECLARE @UserName VARCHAR(100) \r\n";
sql += "DECLARE @ProjectContactPhone VARCHAR(100) \r\n";
sql += "DECLARE @UserMobilePhone VARCHAR(100) \r\n";
sql += "DECLARE @QuoteContent VARCHAR(4000) \r\n";
sql += "DECLARE @MessageTemplate VARCHAR(1000) \r\n";
sql += "SELECT @OwnerMobile=a.Mobile,@OwnerName=a.OwnerName,@OwnerTitle=case a.sex WHEN '1' THEN '先生' WHEN '2' THEN '小姐' ELSE '' END,@QuoteContent=b.QuoteContent,@ProjectName=c.ProjectName,@ProjectContactPhone=c.ContactPhone,@UserName=d.UserName,@UserMobilePhone=isnull(d.MobilePhone,''),@MessageTemplate=c.MessageTemplate FROM [Sales_CarOwners] a \r\n"
+ "LEFT JOIN [Sales_QuoteBill] b ON a.[CarID] = b.[CarID] \r\n"
+ "LEFT JOIN [Sales_System_Project] c ON b.ProjectID=c.ID \r\n"
+ "LEFT JOIN [System_Users] d ON b.QuoteUserID = d.UserID \r\n"
+ "WHERE b.[ID] =" + QuoteID + " \r\n";
//替换
sql += "SET @MessageTemplate=REPLACE(@MessageTemplate,'[OwnerName]',@OwnerName) \r\n";
sql += "SET @MessageTemplate=REPLACE(@MessageTemplate,'[OwnerTitle]',@OwnerTitle) \r\n";
sql += "SET @MessageTemplate=REPLACE(@MessageTemplate,'[ProjectName]',@ProjectName) \r\n";
sql += "SET @MessageTemplate=REPLACE(@MessageTemplate,'[UserName]',@UserName) \r\n";
sql += "SET @MessageTemplate=REPLACE(@MessageTemplate,'[QuoteContent]',@QuoteContent) \r\n";
sql += "SET @MessageTemplate=REPLACE(@MessageTemplate,'[ProjectContactPhone]',@ProjectContactPhone) \r\n";
sql += "SET @MessageTemplate=REPLACE(@MessageTemplate,'[UserMobilePhone]',@UserMobilePhone) \r\n";