old-ssc/SCC.Services/EmailServices.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using SCC.Interface;
using SCC.Common;
using SCC.Models;

namespace SCC.Services
{
    /// <summary>
    /// 邮件相关服务
    /// </summary>
    public class EmailServices : BaseServices, IEmail
    {
        /// <summary>
        /// 添加邮件提醒记录
        /// </summary>
        /// <param name="LotteryName">彩种名称</param>
        /// <param name="QiHao">期号</param>
        /// <param name="OpenTime">开奖时间</param>
        /// <returns></returns>
        public bool AddEmail(string LotteryName, string QiHao, DateTime OpenTime)
        {
            var param = new SqlParameter[]{
                new SqlParameter("@LotteryName",LotteryName),
                new SqlParameter("@QiHao",QiHao),
                new SqlParameter("@OpenTime",OpenTime)
            };
            var result = SqlHelper.ExecuteNonQuery(CommandType.Text, AddEmailSql, param);
            return result > 0;
        }

        /// <summary>
        /// 获取所有待发送邮件列表
        /// </summary>
        /// <returns></returns>
        public List<EmailModel> GetAllNeedSendEmail()
        {
            List<EmailModel> result = new List<EmailModel>();
            var ds = SqlHelper.ExecuteDataset(CommandType.Text, GetAllNeedSendEmailSql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                result = LoadDataList<EmailModel>(ds.Tables[0]);
            }
            return result;
        }

        /// <summary>
        /// 更新邮件列表为已发送
        /// </summary>
        /// <param name="models">邮件列表</param>
        public void UpdateEmailToSend(List<EmailModel> models)
        {
            if (models.Count == 0) return;
            StringBuilder sb = new StringBuilder();
            foreach (var model in models)
            {
                sb.Append(string.Format("{0},", model.Id));
            }
            var exeSql = string.Format(UpdateEmailToSendSql, sb.ToString().Trim(','));
            SqlHelper.ExecuteNonQuery(CommandType.Text, exeSql, null);
        }

        #region Sql语句
        /// <summary>
        /// 添加邮件记录的Sql语句
        /// </summary>
        private static string AddEmailSql = @"IF NOT EXISTS(SELECT TOP 1 1 FROM SCCEmail WHERE LotteryName=@LotteryName AND QiHao=@QiHao)
                                                BEGIN
	                                                INSERT INTO SCCEmail
	                                                SELECT @LotteryName,@QiHao,@OpenTime,0,GETDATE()
                                                END";
        /// <summary>
        /// 获取所有待发送邮件列表的Sql语句
        /// </summary>
        private static string GetAllNeedSendEmailSql = @"SELECT Id,LotteryName,QiHao,OpenTime From SCCEmail WHERE IsSend = 0";
        /// <summary>
        /// 更新邮件为已发送的Sql语句
        /// </summary>
        private static string UpdateEmailToSendSql = @"Update SCCEmail SET IsSend = 1 WHERE Id in ({0})";
        #endregion
    }
}