/// <summary>
/// 查询短信数据
/// </summary>
/// <param name="filter">查询条件</param>
/// <param name="totalCounts">总记录数</param>
/// <returns></returns>
public List<SMSEntity> QuerySMS(SMSQueryFilter filter, out int totalCounts)
{
totalCounts = 0;
List<SMSEntity> result = new List<SMSEntity>();
string sql = @"SELECT COUNT(1)
FROM [SohoEmailAndSMS].[dbo].[SMS](NOLOCK)
WHERE #StrWhere#
SELECT TOP " + filter.PageSize.ToString() + @" T.* FROM
(SELECT
row_number() over(order by InDate DESC) AS RowNumber
,[SysNo]
,[UserSysNo]
,[ReceiveName]
,[ReceivePhoneNumber]
,[SMSBody]
,[Status]
,[SendTime]
,[InDate]
,[LastUpdateTime]
,[Note]
FROM [SohoEmailAndSMS].[dbo].[SMS](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.ReceivePhoneNumber))
{
strWhere += " AND [ReceivePhoneNumber] LIKE '%' + @ReceivePhoneNumber + '%'";
param.Add(new SqlParameter("@ReceivePhoneNumber", filter.ReceivePhoneNumber));
}
if (!string.IsNullOrWhiteSpace(filter.Keywords))
{
strWhere += " AND [SMSBody] 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 (filter.BeginInDate.HasValue)
{
strWhere += " AND [InDate] >= @BeginInDate";
param.Add(new SqlParameter("@BeginInDate", filter.BeginInDate));
}
if (filter.EndInDate.HasValue)
{
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 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>
/// 查询短信
/// </summary>
/// <param name="filter">查询条件</param>
/// <returns></returns>
public QueryResult<SMSEntity> QuerySMS(SMSQueryFilter filter)
{
QueryResult<SMSEntity> result = new QueryResult<SMSEntity>();
int totalCounts = 0;
var dataList = GetDAInstance.QuerySMS(filter, out totalCounts);
result.TotalCount = totalCounts;
result.PageIndex = filter.PageIndex;
result.PageSize = filter.PageSize;
result.ResultList = dataList;
return result;
}
