学校项目:站内信息之手机短信群发功能开发
一个学校项目中需要使用手机短信群发功能,一些重要消息需要向学生、教师、学生家长的短信群发,可按照学校、年级、班级进行发送,可以导入文件群发,根据需求,实现效果:
前端功能界面
实现按年级发送,年级列表是该校所有年级的列表,选择具体的年级,可将短信发送给该年级的全部学生。
选择某个年级以后,列出该年级的全部班级,选择要发送的班级,实现按班级的发送。
选择具体的班级,列表出该班级的全部学生,选择学生,实现向指定的学生发送手机短信。
左侧菜单下面的教师短信、年级短信、班级短信也是类似处理,只是做了权限的判断,年级短信由年级管理员发送,班级短信由班主任发送。
后台功能实现
1、短信发送的实现,使用接收类型判断,将按学校、年级、班级、学生发送的用一个方法处理,为避免短信误发,增加了10分钟内不允许再次发送,同时增加了短信预览的功能;
同时短信中的变量也会替换成与接收者有关的对应的值。
/// <summary> /// 发送短信 /// </summary> /// <param name="receiverType">接收对象 年级grades,班级classes,学生students</param> /// <param name="ids">接收对象Id</param> /// <param name="message">短信内容</param> /// <param name="otherphones">同时发到其它号码</param> /// <param name="isPreview">是否是预览短信</param> /// <returns></returns> public ActionResult SendSMS(string receiverType, string ids, string message, string otherphones, bool isPreview) { BaseResult baseResult = new BaseResult(); // 判断是否能够发送短信 if (!SMSHelper.CanSendSMS(UserHelper.UserInfo) && !isPreview) { baseResult.status = false; baseResult.message = "发送短信时间间隔不能小于10分钟。"; return Json(baseResult, JsonRequestBehavior.AllowGet); } string previewResult = string.Empty; if (string.IsNullOrWhiteSpace(receiverType) || string.IsNullOrWhiteSpace(ids) || string.IsNullOrWhiteSpace(message)) { baseResult.status = false; baseResult.message = "参数不能为空"; return Json(baseResult, JsonRequestBehavior.AllowGet); } // 替换发送者姓名 string realName = UserHelper.UserInfo.RealName; if (isPreview) { realName = "<span style='color:blue'>" + realName + "</span>"; } message = message.Replace("{发送者姓名}", realName); bool needContinue = true; string[] receverIds = ids.Split(','); List<string> listResult = new List<string>(); try { baseResult.status = true; baseResult.message = "短信已提交成功"; // 判断接收对象 if (receiverType.Equals("grades", StringComparison.CurrentCultureIgnoreCase)) { //List<BaseStudentEntity> baseClassEntities = SMSHelper.GetAllStudentByBatchIds(ids); // 向指定的年级发送 // 先添加到发送表中,然后用后台程序读取表中数据逐条发送 //List<BaseStudentEntity> baseClassEntities = SMSHelper.GetAllStudentByBatchIds(ids); // 循环年级 foreach (var id in receverIds) { if (!needContinue) { break; } // 获取班级 List<BaseClassEntity> baseClassEntities = SMSHelper.GetClassByBatchId(id); if (baseClassEntities != null && baseClassEntities.Any()) { // 循环全部班级 for (int i = 0; i < baseClassEntities.Count; i++) { SMSHelper.SendSMSToClass(message, baseClassEntities[i], out previewResult, isPreview); if (isPreview && string.IsNullOrWhiteSpace(previewResult)) { continue; } listResult.Add(previewResult); if (isPreview && listResult.Count > 2) { previewResult = string.Join("<br/>", listResult); needContinue = false; break; } } } } } else if (receiverType.Equals("classes", StringComparison.CurrentCultureIgnoreCase)) { // 循环班级 foreach (var id in receverIds) { BaseClassEntity baseClassEntity = new BaseClassManager().GetObject(id); SMSHelper.SendSMSToClass(message, baseClassEntity, out previewResult, isPreview); if (isPreview && string.IsNullOrWhiteSpace(previewResult)) { continue; } listResult.Add(previewResult); if (isPreview && listResult.Count > 2) { previewResult = string.Join("<br/>", listResult); break; } } } else if (receiverType.Equals("students", StringComparison.CurrentCultureIgnoreCase)) { // 循环学生发送 foreach (var id in receverIds) { BaseStudentEntity studentEntity = new BaseStudentManager().GetObject(id); if (studentEntity != null) { // 取到该学生所在班级 获取班主任信息 BaseClassEntity baseClassEntity = new BaseClassManager().GetObject(studentEntity.ClassId); // 获取班主任姓名 班主任手机 BaseTeacherEntity headTeacherEntity = new BaseTeacherManager().GetObject(baseClassEntity.HeadTeacherId); // 替换有关班主任的信息 message = SMSHelper.ChangeMessageByHeadTeacher(message, headTeacherEntity); SMSHelper.AddSMSSentLogToStudent(message, studentEntity, out previewResult, isPreview); if (isPreview && string.IsNullOrWhiteSpace(previewResult)) { continue; } listResult.Add(previewResult); if (isPreview && listResult.Count > 2) { previewResult = string.Join("<br/>", listResult); break; } } } } else if (receiverType.Equals("phones", StringComparison.CurrentCultureIgnoreCase)) { // 向手机号码发送 ids 是手机号码 SMSHelper.AddSMSSentLog(message, ids, out previewResult, isPreview); } else { baseResult.status = false; baseResult.message = "receiverType参数不正确"; } } catch (Exception ex) { baseResult.status = false; baseResult.message = "系统异常:" + ex.Message; } // 向另外指定的手机号码同时发送 if (!string.IsNullOrWhiteSpace(otherphones)) { SMSHelper.AddSMSSentLog(message, otherphones, out previewResult, isPreview); } // 如果是预览 if (isPreview) { baseResult.message = previewResult; } return Json(baseResult, JsonRequestBehavior.AllowGet); }
2、具体发送功能,实现将待发短信插入到表中及异步调用短信发送接口。
/// <summary> /// 短信发给指定班级 /// </summary> /// <param name="message"></param> /// <param name="baseClassEntity"></param> /// <param name="previewResult"></param> /// <param name="isPreview"></param> public static void SendSMSToClass(string message, BaseClassEntity baseClassEntity, out string previewResult, bool isPreview = false) { previewResult = string.Empty; // 获取班主任姓名 班主任手机 BaseTeacherEntity headTeacherEntity = new BaseTeacherManager().GetObject(baseClassEntity.HeadTeacherId); message = SMSHelper.ChangeMessageByHeadTeacher(message, headTeacherEntity, isPreview); SMSSentLogManager smsSentLogManager = new SMSSentLogManager(); // 获取班级的全部学生 List<BaseStudentEntity> studentEntities = SMSHelper.GetClassAllStudent(baseClassEntity.Id); if (studentEntities != null && studentEntities.Any()) { for (int j = 0; j < studentEntities.Count; j++) { SMSHelper.AddSMSSentLogToStudent(message, studentEntities[j], out previewResult, isPreview); if (isPreview) { break; } } } } /// <summary> /// 将要向学生发送的信息先添加到发送表中 /// </summary> /// <param name="message"></param> /// <param name="studentEntity"></param> /// <param name="previewResult"></param> /// <param name="isPreview"></param> public static void AddSMSSentLogToStudent(string message, BaseStudentEntity studentEntity, out string previewResult, bool isPreview = false) { previewResult = string.Empty; SMSSentLogManager smsSentLogManager = new SMSSentLogManager(); if (message.Contains("{接收者姓名}")) { string studentName = studentEntity.StudentName; if (isPreview) { studentName = "<span style='color:blue'>" + studentName + "</span>"; } message = message.Replace("{接收者姓名}", studentName); } // 获取预览短信的内容 if (isPreview) { previewResult = message; return; } // 将短信内容添加到发送表中 SMSSentLogEntity smsSentLogEntity = new SMSSentLogEntity(); smsSentLogEntity.SystemCode = BaseSystemInfo.SystemCode; // 短信内容 smsSentLogEntity.MessageID = Guid.NewGuid().ToString(); smsSentLogEntity.MessageContent = Regex.Replace(message, @"\r\n", ""); smsSentLogEntity.MessageCount = (int)Math.Ceiling(message.Length / 65.0); ; // 发送者 smsSentLogEntity.SendCompanyId = UserHelper.UserInfo.CompanyId; smsSentLogEntity.SendCompanyName = UserHelper.UserInfo.CompanyName; smsSentLogEntity.SendCompanyCode = UserHelper.UserInfo.CompanyCode; smsSentLogEntity.SendUserId = UserHelper.UserInfo.Id; smsSentLogEntity.SendUserName = UserHelper.UserInfo.UserName; smsSentLogEntity.SendUserCode = UserHelper.UserInfo.Code; // 接受者 smsSentLogEntity.ReceiverUserId = studentEntity.Id; smsSentLogEntity.ReceiverUserName = studentEntity.StudentName; smsSentLogEntity.ReceiverUserMobile = studentEntity.Phone; smsSentLogEntity.ReceiverType = "student"; // 短信创建时间 smsSentLogEntity.CreateTime = DateTime.Now; //发送时间不要设置 smsSentLogEntity.SendTime = null; smsSentLogManager.Add(smsSentLogEntity); // 异步调用短信发送接口 SendDaHanTask(smsSentLogEntity.MessageID, smsSentLogEntity.ReceiverUserMobile, smsSentLogEntity.MessageContent); }
3、异步短信发送,调用第三方短信接口,实现短信的异步发送及短信状态的获取。
/// <summary> /// 异步发送短信 /// </summary> /// <param name="msgid"></param> /// <param name="phone"></param> /// <param name="content"></param> /// <returns></returns> public static async Task SendDaHanTask(string msgid, string phone, string content) { SendDaHan(msgid, phone, content); } /// <summary> /// 发送短信 /// </summary> /// <param name="msgid">记录guid</param> /// <param name="phone">手机号</param> /// <param name="content">短信内容</param> /// <returns></returns> public static string SendDaHan(string msgid, string phone, string content) { string result = string.Empty; // 发送短信 string account = "XXX"; string password = "YYYY"; string url = MobileHost + "/http/sms/Submit"; string subcode = null; // string msgid = null; string sendtime = null; string sign = null; string message = DocXml(account, password, msgid, phone, content, subcode, sendtime, sign); string xmlResult = Post(url, message); // Clipboard.SetText(xmlResult); XmlDocument xmlDocument = new XmlDocument(); xmlDocument.LoadXml(xmlResult); string selectPath = "//response/msgid"; XmlNodeList xmlNodeList = xmlDocument.SelectNodes(selectPath); foreach (XmlNode xmlNode in xmlNodeList) { if (xmlNode.Name.Equals("msgid")) { // System.Console.WriteLine(xmlNode.FirstChild.Value); result = xmlNode.FirstChild.Value; } } /* selectPath = "//response/result"; xmlNodeList = xmlDocument.SelectNodes(selectPath); foreach (XmlNode xmlNode in xmlNodeList) { if (xmlNode.Name.Equals("result")) { System.Console.WriteLine(xmlNode.FirstChild.Value); } } */ return result; } /// <summary> /// 向服务器发送POST数据 /// </summary> /// <param name="url"></param> /// <param name="message"></param> /// <returns></returns> public static string Post(string url, string message) { WebClient webClient = new WebClient(); NameValueCollection postValues = new NameValueCollection(); postValues.Add("message", message); //向服务器发送POST数据 byte[] responseArray = webClient.UploadValues(url, postValues); return Encoding.UTF8.GetString(responseArray); } /// <summary> /// MD5运算(32位小写) /// </summary> /// <param name="str"></param> /// <returns></returns> public static string md5(string str) { byte[] result = Encoding.Default.GetBytes(str); MD5 md5 = new MD5CryptoServiceProvider(); byte[] output = md5.ComputeHash(result); String md = BitConverter.ToString(output).Replace("-", ""); return md.ToLower(); } /// <summary> /// 封装xml /// </summary> /// <param name="account"></param> /// <param name="password"></param> /// <param name="msgid"></param> /// <param name="phones"></param> /// <param name="content"></param> /// <param name="subcode"></param> /// <param name="sendtime"></param> /// <param name="sign"></param> /// <returns></returns> public static string DocXml(string account, string password, string msgid, string phones, string content, string subcode, string sendtime, string sign) { XmlDocument doc = new XmlDocument(); XmlNode xmlnode = doc.CreateXmlDeclaration("1.0", "utf-8", null); doc.AppendChild(xmlnode); XmlElement xe1 = doc.CreateElement("response"); XmlElement sub1 = doc.CreateElement("account"); sub1.InnerText = account; xe1.AppendChild(sub1); XmlElement sub2 = doc.CreateElement("password"); sub2.InnerText = md5(password); xe1.AppendChild(sub2); XmlElement sub3 = doc.CreateElement("msgid"); sub3.InnerText = msgid; xe1.AppendChild(sub3); XmlElement sub4 = doc.CreateElement("phones"); sub4.InnerText = phones; xe1.AppendChild(sub4); XmlElement sub5 = doc.CreateElement("content"); sub5.InnerText = content; xe1.AppendChild(sub5); XmlElement sub6 = doc.CreateElement("subcode"); sub6.InnerText = subcode; xe1.AppendChild(sub6); XmlElement sub7 = doc.CreateElement("sendtime"); sub7.InnerText = sendtime; xe1.AppendChild(sub7); XmlElement sub8 = doc.CreateElement("sign"); sub8.InnerText = sign; xe1.AppendChild(sub8); doc.AppendChild(xe1); return xe1.OuterXml; } /// <summary> /// 获取发送状态 /// </summary> /// <returns>影响行数</returns> public static int GatawayStatusDaHan() { int result = 0; string account = "XXX"; string password = md5("YYY"); string smsId = string.Empty; string url = MobileHost + "/http/sms/Report"; string phone = string.Empty; //String data = "account=" + account + "&password=" + password + "&phone=" + phone + "&smsId=" + smsId; string message = "<?xml version='1.0' encoding='UTF-8'?><message>" + "<account>" + account + "</account>" + "<password>" + password + "</password>" + "<msgid>" + smsId + "</msgid><phone>" + phone + "</phone></message>"; string xmlResult = Post(url, message); // txtResult.Text = xmlResult; // Clipboard.SetText(xmlResult); XmlDocument xmlDocument = new XmlDocument(); xmlDocument.LoadXml(xmlResult); string selectPath = "//response/report"; XmlNodeList xmlNodeList = xmlDocument.SelectNodes(selectPath); result = 0; using (IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.UserCenterDbType, BaseSystemInfo.UserCenterDbConnection)) { dbHelper.Open(); foreach (XmlNode xmlNode in xmlNodeList) { // System.Console.WriteLine(xmlNode["wgcode"].InnerText); // System.Console.WriteLine(xmlNode["msgid"].InnerText); string msgid = xmlNode["msgid"].InnerText; string status = xmlNode["status"].InnerText; string wgcode = xmlNode["wgcode"].InnerText; if (status.Equals("0")) { wgcode = "DELIVRD"; } else { if (string.IsNullOrEmpty(wgcode)) { wgcode = status; } } string sqlQuery = @"UPDATE SMSSentLog SET GatawayStatus = " + dbHelper.GetParameter("GatawayStatus") + " WHERE Channel = " + dbHelper.GetParameter("Channel") + " AND MessageID = " + dbHelper.GetParameter("MessageID") + ""; dbHelper.ExecuteNonQuery(sqlQuery, new[] { dbHelper.MakeParameter("GatawayStatus", wgcode), dbHelper.MakeParameter("Channel", 3), dbHelper.MakeParameter("MessageID", msgid) }); result++; } dbHelper.Close(); } // 若有状态报告,继续读取状态 if (result > 40) { GatawayStatusDaHan(); } //Console.WriteLine(DateTime.Now.ToString(BaseSystemInfo.DateTimeFormat) + " GatawayStatusDaHan ..." + result); return result; }
表结构
发送的短信存储在表中,目前的表创建语句:
CREATE TABLE [dbo].[SMSSentLog]( [Id] [int] IDENTITY(1,1) NOT NULL, [Channel] [tinyint] NULL, [SystemCode] [nvarchar](50) NULL, [SendCompanyId] [varchar](50) NULL, [SendCompanyCode] [nvarchar](20) NULL, [SendCompanyName] [nvarchar](20) NULL, [SendUserId] [varchar](50) NULL, [SendUserCode] [nvarchar](20) NULL, [SendUserName] [nvarchar](20) NULL, [MessageID] [varchar](50) NOT NULL, [MessageCode] [nvarchar](50) NULL, [MessageContent] [nvarchar](1000) NOT NULL, [MessageCount] [tinyint] NOT NULL, [ReceiverUserId] [varchar](50) NULL, [ReceiverUserName] [varchar](50) NULL, [ReceiverUserMobile] [varchar](40) NOT NULL, [ReceiverType] [varchar](20) NULL, [IsWapPush] [int] NULL, [WapUrl] [varchar](200) NULL, [CreateTime] [datetime] NOT NULL, [SendType] [int] NOT NULL, [SendTime] [datetime] NULL, [GatawayStatus] [varchar](20) NULL, CONSTRAINT [PK_SmsSentLog] PRIMARY KEY CLUSTERED ( [Id] DESC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信通道' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'Channel' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发送短信的系统编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'SystemCode' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学校ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'SendCompanyId' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学校编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'SendCompanyCode' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学校名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'SendCompanyName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发送者的Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'SendUserId' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发送者的Code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'SendUserCode' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发送者的登录账号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'SendUserName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息Id,回复时将对这个' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'MessageID' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'MessageCode' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息内容' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'MessageContent' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息条数,用于统计' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'MessageCount' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'接收者用户Id,注意可能不是学生' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'ReceiverUserId' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'接收者项目' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'ReceiverUserName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'接收者手机号码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'ReceiverUserMobile' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'接收对象的类型:学生、教师' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'ReceiverType' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是WAPPUSH短信?' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'IsWapPush' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'WAPPUSH链接' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'WapUrl' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信添加时间,添加的同时并不是发送的时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'CreateTime' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发送类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'SendType' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信发送时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'SendTime' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'网关状态,短信发送后的状态 发送状态码(成功,失败,异常等)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SMSSentLog', @level2type=N'COLUMN',@level2name=N'GatawayStatus' GO ALTER TABLE [dbo].[SMSSentLog] ADD CONSTRAINT [DF_SMSSentLog_Channel] DEFAULT ((1)) FOR [Channel] GO ALTER TABLE [dbo].[SMSSentLog] ADD CONSTRAINT [DF_SMSSentLog_Channel1] DEFAULT ((1)) FOR [MessageCount] GO ALTER TABLE [dbo].[SMSSentLog] ADD CONSTRAINT [DF_SmsSentLog_CreateTime] DEFAULT (getdate()) FOR [CreateTime] GO ALTER TABLE [dbo].[SMSSentLog] ADD CONSTRAINT [DF_SMSSentLog_SendTime] DEFAULT (getdate()) FOR [SendTime] GO