private List <AccountInfoModel> LoadCmppAccount()
{
List <AccountInfoModel> lst = null;
string sql = "select * from t_spaccountinfo";
DataTable dt = MySqlDBExec.GetDateTable(sql, null);
if (dt != null && dt.Rows.Count > 0)
{
lst = new List <AccountInfoModel>();
for (int i = 0; i < dt.Rows.Count; i++)
{
AccountInfoModel aim = new AccountInfoModel();
aim.eprId = int.Parse(dt.Rows[i]["EprId"].ToString());
aim.loginname = dt.Rows[i]["LoginName"].ToString();
aim.password = dt.Rows[i]["Password"].ToString();
aim.senddelay = int.Parse(dt.Rows[i]["SendDelay"].ToString());
aim.serviceid = dt.Rows[i]["ServiceId"].ToString();
aim.spid = dt.Rows[i]["Pid"].ToString();
aim.protocolType = dt.Rows[i]["ProtocolType"].ToString();
aim.serviceIp = dt.Rows[i]["ServiceIp"].ToString();
aim.servicePort = int.Parse(dt.Rows[i]["ServicePort"].ToString());
aim.spnumber = dt.Rows[i]["Spnumber"].ToString();
lst.Add(aim);
}
}
return(lst);
}
/// <summary>
/// 删除内容
/// </summary>
/// <param name="id">内容表id</param>
/// <returns></returns>
private void DeleteContent(string id)
{
string delsql = string.Format("delete from t_sendsms where id={0} and CMSubmitStatus='1' and CUSubmitStatus='1' and CTSubmitStatus='1' ", id);
int result = MySqlDBExec.ExecuteNonQuery(delsql, null);
if (result == 0)
{
SMSLog.Debug("SmsService==>DeleteContent[FailSql]：" + delsql);
}
}
/// <summary>
/// 修改ct提交状态,待下次读取
/// </summary>
/// <param name="id">内容表id</param>
/// <param name="cTSubmitStatus">状态0 可读取,1已发完</param>
/// <returns></returns>
private void UpdateSMSContentSubmitStatu(string id, int cTSubmitStatus)
{
string sql = string.Format("update t_sendsms set CTSubmitStatus={0} where Id={1} ", cTSubmitStatus, id);
int result = MySqlDBExec.ExecuteNonQuery(sql, null);
if (result == 0)
{
SMSLog.Debug("SmsService==>UpdateSMSContentSubmitStatu[FailSql]：" + sql);
}
}
/// <summary>
/// 根据批次号修改手机号码表状态
/// </summary>
/// <param name="state">状态:0未提交，-1失败,1成功</param>
/// <param name="batchnum">批次号</param>
/// <param name="errorCode">错误码</param>
/// <param name="errMsg">错误说明</param>
/// <returns></returns>
private void UpdateMobileByBatchNum(int state, string batchnum, string errorCode, string errMsg)
{
string sql = string.Format("update t_smsmobile_t set SubmitStatus={0},ErrorCode='{1}',Remark='{2}' where BatchNum='{3}'", state, errorCode, errMsg, batchnum);
int result = MySqlDBExec.ExecuteNonQuery(sql, null);
if (result == 0)
{
SMSLog.Debug("SmsService==>UpdateSMSContentSubmitStatu[FailSql]：" + sql);
}
}
/// <summary>
/// 加载待发短信内容sql
/// </summary>
/// <returns></returns>
protected string GetLoadMessageSql()
{
string rand = GetTimesTamp(DateTime.Now).ToString();
string updatesql = "update t_sendsms set CTSubmitStatus='" + rand + "' where SendTime < NOW() and CheckStatus='1' and CTSubmitStatus='0' and (CTGatewayNum ='" + lp.GateWayNum + "' or CTGatewayNum='1001') order by Priority,SendTime limit " + lp.ReadContentNum + " ";
int result = MySqlDBExec.ExecuteNonQuery(updatesql, null);
if (result > 0)
{
string selecesql = "select * from t_sendsms where CTSubmitStatus='" + rand + "' and (CTGatewayNum ='" + lp.GateWayNum + "' or CTGatewayNum='1001') order by Priority,SendTime ";
return(selecesql);
}
return("");
}
/// <summary>
/// 更新手机号码提交状态、MSGID
/// </summary>
/// <param name="id">id</param>
/// <param name="state">提交结果</param>
/// <param name="msgid">msgid</param>
/// <param name="srcno">发送号码</param>
/// <returns></returns>
private void UpdateMobileSubmitState(string id, string state, string msgid, string remark, string srcno)
{
int submitstatu = 0;
if (state.Equals("0"))
{
submitstatu = 1;
remark = "成功";
}
string sql = string.Format("update t_smsmobile_t set MsgId='{0}',SubmitStatus='{1}',ErrorCode='{2}',Remark='{3}',SrcNo='{4}' where id='{5}' ", msgid, submitstatu, state, remark, srcno, id);
int re = 0;
int i = 0;
do
{
re = MySqlDBExec.ExecuteNonQuery(sql, null);
i++;
} while (re <= 0 && i < 3);
if (re < 1)
{
SMSLog.Debug("SmsService==>UpdateMobileSubmitState[FailSql]：" + sql);
}
//重新加入缓存
SmsModel sm = this.GetFormCache(id);
if (sm != null)
{
this.AddCache(msgid, sm);
}
//失败状态，写入状态流水表
if (submitstatu == 0)
{
if (sm != null)
{
object[] obj = { sm.eprId, sm.userId, sm.mobile, sm.clientMsgId, submitstatu };
string sqlstate = string.Format("insert into t_pushsmsmsg(EprId,UserId,Mobile,ClientMsgId,Status,AddTime) values('{0}','{1}','{2}','{3}','{4}',now())", obj);
Random rand = new Random();
int ii = rand.Next(0, _reportQueueNum + 1);
SqlStatementModel seq = new SqlStatementModel();
seq.MySqlCmdParms = null;
seq.CmdTxt = sqlstate;
seq.CmdType = CommandType.Text;
seq.ExecTimer = 1;
ReportSeqQueue[ii].AddSql(seq);
}
}
}
/// <summary>
/// 读取手机号码
/// </summary>
/// <param name="batchnum">批次号</param>
/// <returns></returns>
public DataTable LoadMobile(string batchnum)
{
string gwno = lp.GateWayNum + "";
string rand = GetTimesTamp(DateTime.Now).ToString();
//string sendtime = DateTime.Now.AddHours(-1).ToString();
string updatecondition = " where GatewayNum ='" + gwno + "' and smsFlag='0' and SubmitStatus='0' and SendTime>date_add(now(),interval -1 hour) and BatchNum='" + batchnum + "'";
string updatesql = "update t_smsmobile_t set smsFlag=" + rand + " where GatewayNum ='" + gwno + "' and smsFlag='0' and SubmitStatus='0' and BatchNum='" + batchnum + "' order by SendTime limit " + lp.ReadMobileNum;
if (MySqlDBExec.ExecuteNonQuery(updatesql, null) > 0)
{
string selectsql = "select * from t_smsmobile_t where SendTime>date_add(now(),interval -1 hour) and smsFlag='" + rand + "' and GatewayNum ='" + gwno + "' and SubmitStatus='0' and BatchNum='" + batchnum + "'";
DataTable dt = MySqlDBExec.GetDateTable(selectsql, null);
return(dt);
}
return(null);
}
private AccountInfoModel GetAccountInfo(int eprId)
{
string sql = "select * from t_accountInfo where EprId=" + eprId;
DataTable dt = MySqlDBExec.GetDateTable(sql, null);
if (dt != null && dt.Rows.Count > 0)
{
AccountInfoModel aim = new AccountInfoModel();
aim.eprId = int.Parse(dt.Rows[0]["EprId"].ToString());
aim.loginname = dt.Rows[0]["LoginName"].ToString();
aim.password = dt.Rows[0]["Password"].ToString();
aim.senddelay = int.Parse(dt.Rows[0]["SendDelay"].ToString());
aim.serviceid = dt.Rows[0]["ServiceId"].ToString();
aim.spid = dt.Rows[0]["Pid"].ToString();
aim.protocolType = dt.Rows[0]["ProtocolType"].ToString();
aim.serviceIp = dt.Rows[0]["ServiceIp"].ToString();
aim.servicePort = int.Parse(dt.Rows[0]["ServicePort"].ToString());
return(aim);
}
return(null);
}
private void CollectThread(object obj)
{
if (GlobalModel.IsStopCollect)
{
return;
}
string sql = GetLoadMessageSql();
if (!string.IsNullOrEmpty(sql))
{
DataTable dt = MySqlDBExec.GetDateTable(sql, null);
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string CTGatewayNum = dt.Rows[i]["CTGatewayNum"].ToString();
string id = dt.Rows[i]["Id"].ToString();
string batchnum = dt.Rows[i]["BatchNum"].ToString();
string eprId = dt.Rows[i]["EprId"].ToString();
string con = dt.Rows[i]["Content"].ToString();//短信内容
int mobileCount = 0;
int.TryParse(dt.Rows[i]["MobileCount"].ToString(), out mobileCount);
if (CTGatewayNum.Equals("1001"))
{
SMSLog.Debug("SMDDatabase==>该批次号[" + batchnum + "]1001不发送，修改tcsubmitstate状态为1 并试图删除");
UpdateSMSContentSubmitStatu(id, 1);
this.DeleteContent(id);
UpdateMobileByBatchNum(-1, batchnum, "1001", "限制网关");
continue;
}
DataTable dt_mobile = this.LoadMobile(batchnum);//根据批次号取发送号码
if (dt_mobile != null && dt_mobile.Rows.Count > 0)
{
for (int j = 0; j < dt_mobile.Rows.Count; j++)
{
SmsModel msg = new SmsModel();
msg.content = con; //短信内容
msg.contentId = id; //内容表ID
if (FillMessage(dt_mobile.Rows[j], msg))
{
AddQueue(msg.eprId, msg); //加入待发队列
AddCache(msg.id + "", msg); //加入缓存
}
}
if (dt_mobile.Rows.Count <= mobileCount)
{
SMSLog.Debug("SMDDatabase==>该批次号[" + batchnum + "]已读取完，修改tcsubmitstate状态为1 并试图删除");
UpdateSMSContentSubmitStatu(id, 1);
this.DeleteContent(id);
}
else
{
UpdateSMSContentSubmitStatu(id, 0);//改变状态，待下次读取
}
}
else
{
try
{
SMSLog.Debug("SMDDatabase==>无该批次号[" + batchnum + "]手机号码,修改tcsubmitstate状态为1 并试图删除");
UpdateSMSContentSubmitStatu(id, 1);
this.DeleteContent(id);
}
catch (Exception ex)
{
SMSLog.Error("SMDDatabase::CollectThread", "删除异常: " + ex.Message);
}
}
}//for
}
}
}