private void map(SMSInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.CellNumber = Util.TrimNull(tempdr["CellNumber"]);
oParam.SMSContent = Util.TrimNull(tempdr["SMSContent"]);
oParam.Priority = Util.TrimIntNull(tempdr["Priority"]);
oParam.RetryCount = Util.TrimIntNull(tempdr["RetryCount"]);
oParam.CreateUserSysNo = Util.TrimIntNull(tempdr["CreateUserSysNo"]);
oParam.CreateTime = Util.TrimDateNull(tempdr["CreateTime"]);
oParam.ExpectSendTime = Util.TrimDateNull(tempdr["ExpectSendTime"]);
oParam.HandleTime = Util.TrimDateNull(tempdr["HandleTime"]);
oParam.Status = Util.TrimIntNull(tempdr["Status"]);
}
public void Insert(SMSInfo oInfo)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
//��ֹ�ظ�ɨ�裬��ͬ�Ķ���ֻ����һ��
if(!IsExistsSMS(oInfo))
new SMSDac().Insert(oInfo);
scope.Complete();
}
}
public void UpdateSMSStatus(SMSInfo oInfo)
{
new SMSDac().UpdateSMSStatus(oInfo);
}
private bool IsExistsSMS(SMSInfo oInfo)
{
string sql = "select * from sms where cellnumber=" + oInfo.CellNumber + " and smscontent=" + Util.ToSqlString(Util.SafeFormat(oInfo.SMSContent.Trim()));
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
return true;
else
return false;
}
public void UpdateSMSRetryCount(SMSInfo oInfo)
{
string sql = "";
if (oInfo.RetryCount == 0) //1Сʱ���ٲ���һ��
{
sql = "update sms set retrycount=1,expectsendtime=DATEADD(hour, 1, getdate()) where sysno=" + oInfo.SysNo;
}
else if (oInfo.RetryCount == 1) //3Сʱ���ٲ���һ��
{
sql = "update sms set retrycount=2,expectsendtime=DATEADD(hour, 2, getdate()) where sysno=" + oInfo.SysNo;
}
else if(oInfo.RetryCount == 2) //24Сʱ���ٲ���һ��
{
sql = "update sms set retrycount=3,expectsendtime=DATEADD(hour, 21, getdate()) where sysno=" + oInfo.SysNo;
}
else if (oInfo.RetryCount == 3) //���ϵ�
{
sql = "update sms set status=" + (int)AppEnum.TriStatus.Abandon + " where sysno=" + oInfo.SysNo;
}
SqlHelper.ExecuteNonQuery(sql);
}
/// <summary>
/// Search for the emails to be send
/// </summary>
/// <returns></returns>
public Hashtable SearchAsyncSMS()
{
string sql = @"select *
from sms
where expectsendtime < getdate() and status = 0";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
Hashtable smsHash = new Hashtable(5);
if (Util.HasMoreRow(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
SMSInfo oInfo = new SMSInfo();
this.map(oInfo, dr);
smsHash.Add(oInfo, null);
}
}
return smsHash;
}
public int InsertSMS(SMSInfo oInfo)
{
return new SMSDac().Insert(oInfo);
}
public int Insert(SMSInfo oParam)
{
string sql = @"INSERT INTO SMS
(
CellNumber, SMSContent, Priority,
RetryCount, CreateUserSysNo, CreateTime, ExpectSendTime,HandleTime, Status
)
VALUES (
@CellNumber, @SMSContent, @Priority,
@RetryCount, @CreateUserSysNo, @CreateTime, @ExpectSendTime, @HandleTime, @Status
);set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramCellNumber = new SqlParameter("@CellNumber", SqlDbType.NVarChar,11);
SqlParameter paramSMSContent = new SqlParameter("@SMSContent", SqlDbType.NVarChar,70);
SqlParameter paramPriority = new SqlParameter("@Priority", SqlDbType.Int,4);
SqlParameter paramRetryCount = new SqlParameter("@RetryCount", SqlDbType.Int,4);
SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int, 4);
SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
SqlParameter paramExpectSendTime = new SqlParameter("@ExpectSendTime", SqlDbType.DateTime);
SqlParameter paramHandleTime = new SqlParameter("@HandleTime", SqlDbType.DateTime);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);
paramSysNo.Direction = ParameterDirection.Output;
if ( oParam.CellNumber != AppConst.StringNull)
paramCellNumber.Value = oParam.CellNumber;
else
paramCellNumber.Value = System.DBNull.Value;
if ( oParam.SMSContent != AppConst.StringNull)
paramSMSContent.Value = oParam.SMSContent;
else
paramSMSContent.Value = System.DBNull.Value;
if ( oParam.Priority != AppConst.IntNull)
paramPriority.Value = oParam.Priority;
else
paramPriority.Value = System.DBNull.Value;
if ( oParam.RetryCount != AppConst.IntNull)
paramRetryCount.Value = oParam.RetryCount;
else
paramRetryCount.Value = System.DBNull.Value;
if (oParam.CreateUserSysNo != AppConst.IntNull)
paramCreateUserSysNo.Value = oParam.CreateUserSysNo;
else
paramCreateUserSysNo.Value = System.DBNull.Value;
if ( oParam.CreateTime != AppConst.DateTimeNull)
paramCreateTime.Value = oParam.CreateTime;
else
paramCreateTime.Value = System.DBNull.Value;
if (oParam.ExpectSendTime != AppConst.DateTimeNull)
paramExpectSendTime.Value = oParam.ExpectSendTime;
else
paramExpectSendTime.Value = System.DBNull.Value;
if ( oParam.HandleTime != AppConst.DateTimeNull)
paramHandleTime.Value = oParam.HandleTime;
else
paramHandleTime.Value = System.DBNull.Value;
if ( oParam.Status != AppConst.IntNull)
paramStatus.Value = oParam.Status;
else
paramStatus.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramCellNumber);
cmd.Parameters.Add(paramSMSContent);
cmd.Parameters.Add(paramPriority);
cmd.Parameters.Add(paramRetryCount);
cmd.Parameters.Add(paramCreateUserSysNo);
cmd.Parameters.Add(paramCreateTime);
cmd.Parameters.Add(paramExpectSendTime);
cmd.Parameters.Add(paramHandleTime);
cmd.Parameters.Add(paramStatus);
return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
}
public int UpdateSMSStatus(SMSInfo oParam)
{
string sql = @"UPDATE sms SET
Status=@Status,HandleTime=@HandleTime
WHERE SysNo=@SysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);
SqlParameter paramHandleTime = new SqlParameter("@HandleTime", SqlDbType.DateTime);
if (oParam.SysNo != AppConst.IntNull)
paramSysNo.Value = oParam.SysNo;
else
paramSysNo.Value = System.DBNull.Value;
if (oParam.Status != AppConst.IntNull)
paramStatus.Value = oParam.Status;
else
paramStatus.Value = System.DBNull.Value;
if (oParam.HandleTime != AppConst.DateTimeNull)
paramHandleTime.Value = oParam.HandleTime;
else
paramHandleTime.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramStatus);
cmd.Parameters.Add(paramHandleTime);
return SqlHelper.ExecuteNonQuery(cmd);
}