/// <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>
/// 加载配置
/// </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;
}