/// <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;
}