static void EmailTest()
{
//(new EmailSendService()).StartSendMail();
EmailQueryFilter filter = new EmailQueryFilter();
filter.UserSysNo = 1001;
filter.ReceiveName = "r";
filter.EmailAddress = "j";
//filter.Keywords = "1";
//filter.Status = EmailStatus.SendSuccess;
filter.BeginInDate = "2014-05-01 23:56:33.237";
filter.EndInDate = "2014-06-02 23:56:33.237";
filter.PageSize = 6;
filter.PageIndex = 1;
QueryResult<EmailEntity> list = EmailProcessor.Instance.QueryMail(filter);
Console.WriteLine("ResultListCount:" + list.ResultList != null ? list.ResultList.Count : 0);
Console.WriteLine("TotalCount:" + list.TotalCount);
Console.WriteLine("PageCount:" + list.PageCount);
Console.WriteLine("PageIndex:" + list.PageIndex);
//EmailEntity entity = new EmailEntity()
//{
// UserSysNo = 1002,
// ReceiveName = "Test",
// ReceiveAddress = "*****@*****.**",
// CCAddress = "",
// EmailTitle = "Test Tilte",
// EmailBody = "Body",
// IsBodyHtml = true,
// EmailPriority = System.Net.Mail.MailPriority.Normal,
// Status = EmailStatus.AuditPassed
//};
//EmailProcessor.Instance.InsertMail(entity);
}
/// <summary>
/// 查询电子邮件
/// </summary>
/// <param name="filter">查询条件</param>
/// <returns></returns>
public QueryResult<EmailEntity> QueryMail(EmailQueryFilter filter)
{
QueryResult<EmailEntity> result = new QueryResult<EmailEntity>();
int totalCounts = 0;
var dataList = GetDAInstance.QueryMail(filter, out totalCounts);
result.TotalCount = totalCounts;
result.PageIndex = filter.PageIndex;
result.PageSize = filter.PageSize;
result.ResultList = dataList;
return result;
}
/// <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;
}
static void QueryMailTest()
{
EmailQueryFilter filter = new EmailQueryFilter()
{
PageIndex = 1,
PageSize = 100,
BeginInDate = "2014-01-01",
EndInDate = "2014-08-01",
UserSysNo = 1001,
ReceiveName = "t",
EmailAddress = "t",
Keywords = "测",
Status = EmailStatus.AuditPassed
};
var data = EmailProcessor.Instance.QueryMail(filter);
if (data.ResultList != null)
Console.WriteLine(data.ResultList.Count);
else
Console.WriteLine("NULL Data");
}
public void TestMethodQueryMail()
{
EmailQueryFilter filter = new EmailQueryFilter()
{
PageIndex = 1,
PageSize = 100
};
var data = EmailAndSMSService.Instance.QueryMail(filter);
}