Exemple #1
0
        /// <summary>
        /// 获取待发送的短信列表
        /// </summary>
        /// <param name="topCnts">获取记录数</param>
        /// <returns></returns>
        public List<SMSEntity> GetWaitSendSMSList(int topCnts)
        {
            List<SMSEntity> result = new List<SMSEntity>();

            string sql = @"DECLARE @TblSysNoList TABLE (GetSysNo INT);
            INSERT INTO @TblSysNoList
            SELECT TOP " + topCnts.ToString() + @" [SysNo]
            FROM [SohoEmailAndSMS].[dbo].[SMS](NOLOCK)
            WHERE [Status] = 200 OR ([Status] = 300 AND [SendTime] < GETDATE())

            UPDATE [SohoEmailAndSMS].[dbo].[SMS] SET Status = 301
            WHERE [SysNo] IN (SELECT GetSysNo FROM @TblSysNoList)

            SELECT [SysNo]
              ,[UserSysNo]
              ,[ReceiveName]
              ,[ReceivePhoneNumber]
              ,[SMSBody]
              ,[Status]
              ,[SendTime]
              ,[InDate]
              ,[LastUpdateTime]
              ,[Note]
              FROM [SohoEmailAndSMS].[dbo].[SMS](NOLOCK)
              WHERE [SysNo] IN (SELECT GetSysNo FROM @TblSysNoList)";
            SqlServerDBHelper db = new SqlServerDBHelper();
            try
            {
                DataTable dt = db.ExeSqlDataAdapter(CommandType.Text, sql);
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        result.Add(new SMSEntity()
                        {
                            SysNo = int.Parse(row["SysNo"].ToString()),
                            UserSysNo = string.IsNullOrWhiteSpace(row["UserSysNo"].ToString()) ? 0 : int.Parse(row["UserSysNo"].ToString()),
                            ReceiveName = row["ReceiveName"].ToString(),
                            ReceivePhoneNumber = row["ReceivePhoneNumber"].ToString(),
                            SMSBody = row["SMSBody"].ToString(),
                            Status = (SMSStatus)int.Parse(row["Status"].ToString()),
                            SendTime = row["SendTime"].ToString(),
                            InDate = row["InDate"].ToString(),
                            LastUpdateTime = row["LastUpdateTime"].ToString(),
                            Note = row["Note"].ToString(),
                        });
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.WriteException(ex.ToString());
            }
            finally
            {
                db.Dispose();
            }

            return result;
        }
        /// <summary>
        /// 写入Email数据
        /// </summary>
        /// <param name="entity">Email对象</param>
        public bool InsertEmail(EmailEntity entity)
        {
            int retVal = 0;
            string sql = @"INSERT INTO [SohoEmailAndSMS].[dbo].[Emails]
                            ([UserSysNo]
                            ,[ReceiveName]
                            ,[ReceiveAddress]
                            ,[CCAddress]
                            ,[EmailTitle]
                            ,[EmailBody]
                            ,[IsBodyHtml]
                            ,[EmailPriority]
                            ,[Status]
                            ,[SendTime]
                            ,[InDate]
                            ,[LastUpdateTime]
                            ,[Note])
                        VALUES
                            (@UserSysNo
                            ,@ReceiveName
                            ,@ReceiveAddress
                            ,@CCAddress
                            ,@EmailTitle
                            ,@EmailBody
                            ,@IsBodyHtml
                            ,@EmailPriority
                            ,@Status
                            ,@SendTime
                            ,GETDATE()
                            ,NULL
                            ,@Note)";

            SqlServerDBHelper db = new SqlServerDBHelper();
            try
            {
                SqlParameter[] para = new SqlParameter[]
                {
                    new SqlParameter("@UserSysNo", entity.UserSysNo.HasValue ? entity.UserSysNo.Value : 0),
                    new SqlParameter("@ReceiveName", string.IsNullOrWhiteSpace(entity.ReceiveName) ? "" : entity.ReceiveName),
                    new SqlParameter("@ReceiveAddress", entity.ReceiveAddress),
                    new SqlParameter("@CCAddress", string.IsNullOrWhiteSpace(entity.CCAddress) ? "" : entity.CCAddress),
                    new SqlParameter("@EmailTitle", entity.EmailTitle),
                    new SqlParameter("@EmailBody", entity.EmailBody),
                    new SqlParameter("@IsBodyHtml", entity.IsBodyHtml),
                    new SqlParameter("@EmailPriority", entity.EmailPriority),
                    new SqlParameter("@Status", entity.Status),
                    new SqlParameter("@SendTime", string.IsNullOrWhiteSpace(entity.SendTime) ? "" : entity.SendTime),
                    new SqlParameter("@Note", string.IsNullOrWhiteSpace(entity.Note) ? "" : entity.Note)
                };
                retVal = db.ExecuteNonQuery(CommandType.Text, sql, para);
            }
            catch (Exception ex)
            {
                Logger.WriteException(ex.ToString());
            }
            finally
            {
                db.Dispose();
            }

            return retVal > 0 ? true : false;
        }
 /// <summary>
 /// 更新电子邮件状态
 /// </summary>
 /// <param name="sysNo">电子邮件编号</param>
 /// <param name="status">状态</param>
 /// <param name="note">备注</param>
 public void UpdateEmailStatus(long sysNo, EmailStatus status, string note)
 {
     string sql = @"UPDATE [SohoEmailAndSMS].[dbo].[Emails]
                       SET [Status] = @Status,
                           [LastUpdateTime] = GETDATE(),
                           [Note] = ISNULL([Note], N'') + @Note
                       WHERE [SysNo] = @SysNo";
     SqlServerDBHelper db = new SqlServerDBHelper();
     try
     {
         SqlParameter[] para = new SqlParameter[]
         {
             new SqlParameter("@SysNo", sysNo),
             new SqlParameter("@Status", status),
             new SqlParameter("@Note", note)
         };
         db.ExecuteNonQuery(CommandType.Text, sql, para);
     }
     catch (Exception ex)
     {
         Logger.WriteException(ex.ToString());
     }
     finally
     {
         db.Dispose();
     }
 }
        /// <summary>
        /// 查询Email数据
        /// </summary>
        /// <param name="filter">查询条件</param>
        /// <param name="totalCounts">总记录数</param>
        /// <returns></returns>
        public List<EmailEntity> QueryMail(EmailQueryFilter filter, out int totalCounts)
        {
            totalCounts = 0;
            List<EmailEntity> result = new List<EmailEntity>();

            string sql = @"SELECT COUNT(1)
                          FROM [SohoEmailAndSMS].[dbo].[Emails](NOLOCK)
                              WHERE #StrWhere#

                            SELECT TOP " + filter.PageSize.ToString() + @" T.* FROM
                               (SELECT
                               row_number() over(order by InDate DESC) AS RowNumber
                              ,[SysNo]
                              ,[UserSysNo]
                              ,[ReceiveName]
                              ,[ReceiveAddress]
                              ,[CCAddress]
                              ,[EmailTitle]
                              ,[EmailBody]
                              ,[IsBodyHtml]
                              ,[EmailPriority]
                              ,[Status]
                              ,[SendTime]
                              ,[InDate]
                              ,[LastUpdateTime]
                              ,[Note]
                          FROM [SohoEmailAndSMS].[dbo].[Emails](NOLOCK)
                              WHERE #StrWhere#) T
                          WHERE T.[RowNumber] > @StartNumber AND T.[RowNumber] < @EndNumber";
            SqlServerDBHelper db = new SqlServerDBHelper();
            try
            {
                List<SqlParameter> param = new List<SqlParameter>();
                string strWhere = "1=1";
                if (filter.UserSysNo.HasValue)
                {
                    strWhere += " AND [UserSysNo] = @UserSysNo";
                    param.Add(new SqlParameter("@UserSysNo", filter.UserSysNo));
                }
                if (!string.IsNullOrWhiteSpace(filter.ReceiveName))
                {
                    strWhere += " AND [ReceiveName] LIKE '%' + @ReceiveName + '%'";
                    param.Add(new SqlParameter("@ReceiveName", filter.ReceiveName));
                }
                if (!string.IsNullOrWhiteSpace(filter.EmailAddress))
                {
                    strWhere += " AND ([ReceiveAddress] LIKE '%' + @EmailAddress + '%'";
                    strWhere += " OR [CCAddress] LIKE '%' + @EmailAddress + '%')";
                    param.Add(new SqlParameter("@EmailAddress", filter.EmailAddress));
                }
                if (!string.IsNullOrWhiteSpace(filter.Keywords))
                {
                    strWhere += " AND ([EmailTitle] LIKE '%' + @Keywords + '%'";
                    strWhere += " OR [EmailBody] LIKE '%' + @Keywords + '%')";
                    param.Add(new SqlParameter("@Keywords", filter.Keywords));
                }
                if (filter.Status.HasValue)
                {
                    strWhere += " AND [Status] = @Status";
                    param.Add(new SqlParameter("@Status", filter.Status));
                }
                if (!string.IsNullOrWhiteSpace(filter.BeginInDate))
                {
                    strWhere += " AND [InDate] >= @BeginInDate";
                    param.Add(new SqlParameter("@BeginInDate", filter.BeginInDate));
                }
                if (!string.IsNullOrWhiteSpace(filter.EndInDate))
                {
                    strWhere += " AND [InDate] <= @EndInDate";
                    param.Add(new SqlParameter("@EndInDate", filter.EndInDate));
                }
                long startNumber = (filter.PageIndex - 1) * filter.PageSize;
                long endNumber = filter.PageIndex * filter.PageSize + 1;
                param.Add(new SqlParameter("@StartNumber", startNumber));
                param.Add(new SqlParameter("@EndNumber", endNumber));

                sql = sql.Replace("#StrWhere#", strWhere);
                SqlParameter[] paramArray = param.ToArray();
                DataSet ds = db.ExeSqlDataSet(CommandType.Text, sql, paramArray);
                if (ds != null && ds.Tables.Count > 0)
                {
                    totalCounts = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                }
                if (ds != null && ds.Tables.Count > 1)
                {
                    DataTable dt = ds.Tables[1];
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            result.Add(new EmailEntity()
                            {
                                SysNo = int.Parse(row["SysNo"].ToString()),
                                UserSysNo = string.IsNullOrWhiteSpace(row["UserSysNo"].ToString()) ? 0 : int.Parse(row["UserSysNo"].ToString()),
                                ReceiveName = row["ReceiveName"].ToString(),
                                ReceiveAddress = row["ReceiveAddress"].ToString(),
                                CCAddress = row["CCAddress"].ToString(),
                                EmailTitle = row["EmailTitle"].ToString(),
                                EmailBody = row["EmailBody"].ToString(),
                                IsBodyHtml = row["IsBodyHtml"].ToString() == "1" ? true : false,
                                EmailPriority = (MailPriority)int.Parse(row["EmailPriority"].ToString()),
                                Status = (EmailStatus)int.Parse(row["Status"].ToString()),
                                SendTime = row["SendTime"].ToString(),
                                InDate = row["InDate"].ToString(),
                                LastUpdateTime = row["LastUpdateTime"].ToString(),
                                Note = row["Note"].ToString(),
                            });
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.WriteException(ex.ToString());
            }
            finally
            {
                db.Dispose();
            }

            return result;
        }
 /// <summary>
 /// 加载配置
 /// </summary>
 /// <returns></returns>
 public DataTable LoadConfig()
 {
     DataTable dt = null;
     string sql = @"SELECT SysNo, Category, ConfigKey, ConfigValue
                         FROM [SohoEmailAndSMS].[dbo].[Configs](NOLOCK)
                         WHERE [Category] = N'Email'";
     SqlServerDBHelper db = new SqlServerDBHelper();
     try
     {
         dt = db.ExeSqlDataAdapter(CommandType.Text, sql);
     }
     catch(Exception ex)
     {
         Logger.WriteException(ex.ToString());
     }
     finally
     {
         db.Dispose();
     }
     return dt;
 }