62,046
社区成员
发帖
与我相关
我的任务
分享
//余额查询
if (opdt.Rows[n].ItemArray[1].ToString().Trim()=="KT 004")
{
string querybalance = "select Balance from Card where CustomerID='"+Convert.ToInt32(opdt.Rows[n].ItemArray[2].ToString())+"'";
DataTable balancedt = null;
CommonDB.OpenSql(querybalance, ref balancedt);
if(balancedt.Rows.Count!=0)
{
DataTable balancequerydt = null;
string sqlbalancequery = "select Content from SmsMessageBoard where Code='BALANCE_QUERY'";
CommonDB.OpenSql(sqlbalancequery, ref balancequerydt);
string[] arrys = balancequerydt.Rows[0].ItemArray[0].ToString().Split('&');
string balanceamount = arrys[0] + balancedt.Rows[0].ItemArray[0].ToString() + arrys[2];
string[] mobs=new string[1];
mobs[0] = opdt.Rows[n].ItemArray[3].ToString().Trim();
if(sms.Send(mobs,balanceamount))
{
//更新
string updatequerybalance = "update SmsMessage set SendMessage='"+balanceamount+"',SendTime='" + DateTime.Now + "',RetryTimes = RetryTimes + 1 where RecID='" + Convert.ToInt32(opdt.Rows[n].ItemArray[0].ToString()) + "'";
CommonDB.ExecSQL(updatequerybalance);
sucesscount++;
}
}
}//end if (opdt.Rows[n].ItemArray[1].ToString().Trim()=="KT 004")
// 消费查询
if (opdt.Rows[n].ItemArray[1].ToString().Trim() == "KT 001")
{
DataTable consumedt = null;
string date_today = System.DateTime.Today.Date.ToString("yyyy-MM-dd");
string date = System.DateTime.Today.AddDays(-7).Date.ToString("yyyy-MM-dd");
string date1 = date.Substring(0, 10) + " 00:00:00";
string date2 = date_today.Substring(0, 10) + " 23:00:00";
string Account = opdt.Rows[n].ItemArray[4].ToString();
// string queryconsume = "select ArisesTime ,DealAmount ,B.TerminalCode from CardDealRecordConsume A,Terminal B where A.TerminalID=B.TerminalID and A.PhysicalCode='" + Account + "'and A.ArisesTime between CONVERT(CHAR,'" + date1 + "',20) and CONVERT(CHAR,'" + date2 + "',20) and A.BookID ='201' order by A.ArisesTime desc";
string queryconsume = "select top 3 ArisesTime ,DealAmount from CardDealRecordConsume A where A.PhysicalCode='" + Account + "' and A.BookID='201' Order by ArisesTime desc";
if (CommonDB.OpenSql(queryconsume, ref consumedt) != MyConst.RET_OK)
{
continue;
}
if (consumedt.Rows.Count < 1)
{
DataTable nodealdt = null;
string sqlnodeal = "select Content from SmsMessageBoard where Code='NO_DEAL_RECORD'";
CommonDB.OpenSql(sqlnodeal, ref nodealdt);
string[] mobil11 = new string[1];
mobil11[0] = opdt.Rows[n].ItemArray[3].ToString().Trim();
if (sms.Send(mobil11, nodealdt.Rows[0].ItemArray[0].ToString()))
{
string updatequeryconsume1 = "update SmsMessage set SendMessage='" + nodealdt.Rows[0].ItemArray[0].ToString() + "',SendTime='" + DateTime.Now + "',RetryTimes = RetryTimes + 1 where RecID='" + Convert.ToInt32(opdt.Rows[n].ItemArray[0].ToString()) + "'";
CommonDB.ExecSQL(updatequeryconsume1);
sucesscount++;
}
continue;
}
if (consumedt.Rows.Count > 0)
{
string strmessageresult = null;
for (int i = 0; i < consumedt.Rows.Count;i++ )
{
strmessageresult = strmessageresult + (i + 1).ToString() + "." + "时间:" + consumedt.Rows[i].ItemArray[0].ToString() + "," + "金额:" + consumedt.Rows[i].ItemArray[1].ToString() +"元" + ";";
}
strmessageresult = "消费记录:" + consumedt.Rows.Count.ToString() + "条," + strmessageresult;
string[] mobss=new string[1];
mobss[0] = opdt.Rows[n].ItemArray[3].ToString().Trim();
if (sms.Send(mobss, strmessageresult))
{
//更新
string updatequeryconsume = "update SmsMessage set SendMessage='消费记录查询操作成功',SendTime='" + DateTime.Now + "',RetryTimes = RetryTimes + 1 where RecID='" + Convert.ToInt32(opdt.Rows[n].ItemArray[0].ToString()) + "'";
CommonDB.ExecSQL(updatequeryconsume);
sucesscount++;
}
}//end if (consumedt.Rows.Count > 0)
}// end if (opdt.Rows[n].ItemArray[1].ToString().Trim() == "KT 001")
//改密码
if (opdt.Rows[n].ItemArray[1].ToString().Trim().Substring(0, 6) == "KT 003")
{
string[] passwordmessage = opdt.Rows[n].ItemArray[1].ToString().Trim().Split('#');
string oldpw = passwordmessage[1];
string newpw = passwordmessage[2];
DataTable pwdt = null;
string selectpw = "select MainPassword from Customer where CustomerID='" + Convert.ToInt32(opdt.Rows[n].ItemArray[2].ToString()) + "'";
if (CommonDB.OpenSql(selectpw, ref pwdt) != MyConst.RET_OK)
{
continue;
}
if (pwdt.Rows.Count < 1)
{
DataTable nobinddt = null;
string sqlnobind = "select Content from SmsMessageBoard where Code='NO_BINDING'";
CommonDB.OpenSql(sqlnobind, ref nobinddt);
string[] mobil=new string[1];
mobil[0] = opdt.Rows[n].ItemArray[3].ToString().Trim();
sms.Send(mobil,nobinddt.Rows[0].ItemArray[0].ToString());
string updatepwmodify2 = "update SmsMessage set RetryTimes =4,SendMessage='" + nobinddt.Rows[0].ItemArray[0].ToString() + "',SendTime='" + DateTime.Now + "' where RecID='" + Convert.ToInt32(opdt.Rows[n].ItemArray[0].ToString()) + "'";
CommonDB.ExecSQL(updatepwmodify2);
sucesscount++;
continue;
}
if (pwdt.Rows.Count>0)
{
if (pwdt.Rows[0].ItemArray[0].ToString().Trim()==oldpw.Trim())
{
string updatepw = "update Customer set MainPassword='" + newpw + "' where CustomerID='" + Convert.ToInt32(opdt.Rows[n].ItemArray[2].ToString()) + "'";
if (CommonDB.ExecSQL(updatepw) != MyConst.RET_FAIL)
{
DataTable pwdokdt = null;
string sqlpwdok = "select Content from SmsMessageBoard where Code='PWD_OK'";
CommonDB.OpenSql(sqlpwdok, ref pwdokdt);
string[] mobilsucess = new string[1];
mobilsucess[0] = opdt.Rows[n].ItemArray[3].ToString().Trim();
if (sms.Send(mobilsucess, pwdokdt.Rows[0].ItemArray[0].ToString()))
{
//更新
string updatepwmodify = "update SmsMessage set SendMessage='" + pwdokdt.Rows[0].ItemArray[0].ToString() + "',SendTime='" + DateTime.Now + "',RetryTimes = RetryTimes + 1 where RecID='" + Convert.ToInt32(opdt.Rows[n].ItemArray[0].ToString()) + "'";
CommonDB.ExecSQL(updatepwmodify);
sucesscount++;
}
else
{
continue;
}
}
else
continue;
}
else
{
DataTable pwdinvaliddt = null;
string sqlpwdinvalid = "select Content from SmsMessageBoard where Code='PWD_INVALID'";
CommonDB.OpenSql(sqlpwdinvalid, ref pwdinvaliddt);
string[] mobil=new string[1];
mobil[0] = opdt.Rows[n].ItemArray[3].ToString().Trim();
sms.Send(mobil, pwdinvaliddt.Rows[0].ItemArray[0].ToString());
string updatepwmodify1 = "update SmsMessage set RetryTimes =4,SendMessage='" + pwdinvaliddt.Rows[0].ItemArray[0].ToString() + "',SendTime='" + DateTime.Now + "' where RecID='" + Convert.ToInt32(opdt.Rows[n].ItemArray[0].ToString()) + "'";
CommonDB.ExecSQL(updatepwmodify1);
sucesscount++;
continue;
}
}
}// end if (opdt.Rows[n].ItemArray[1].ToString().Trim().Substring(0, 6) == "KT 003")
}//end if (opdt.Rows[n].ItemArray[1].ToString().Trim().Length > 6)
//指令处理
private void SmsOperation()
{
ShowInfo("正在处理短信.....");
int sucesscount = 0;
//int failcount = 0;
//取出指令
string selectopera = @"select RecID,RecvMessage,CustomerID,MobilePhone,PhysicalCode from SmsMessage where SendMessage IS NULL and RetryTimes <= 3";
DataTable opdt = null;
if (CommonDB.OpenSql(selectopera, ref opdt) != MyConst.RET_OK)
{
ShowInfo("获取数据出现异常");
return;
}
if (opdt.Rows.Count<1)
{
ShowInfo("没有新短信");
return;
}
//循环执行相应操作
for (int n=0;n<opdt.Rows.Count;n++)
{
if (opdt.Rows[n].ItemArray[1].ToString().Trim().Length > 6)
{
//挂失
if (opdt.Rows[n].ItemArray[1].ToString().Trim().Substring(0, 6) == "KT 002")
{
string[] passwordmessage1 = opdt.Rows[n].ItemArray[1].ToString().Trim().Split('#');
string pw = passwordmessage1[1];
DataTable pdt = null;
string selectpw1 = "select MainPassword,State from Customer where CustomerID='" + Convert.ToInt32(opdt.Rows[n].ItemArray[2].ToString()) + "'";
if (CommonDB.OpenSql(selectpw1, ref pdt) != MyConst.RET_OK)
{
continue;
}
if (pdt.Rows.Count < 1)
{
DataTable nobinddt = null;
string sqlnobind = "select Content from SmsMessageBoard where Code='NO_BINDING'";
CommonDB.OpenSql(sqlnobind, ref nobinddt);
string[] mobil = new string[1];
mobil[0] = opdt.Rows[n].ItemArray[3].ToString().Trim();
sms.Send(mobil, nobinddt.Rows[0].ItemArray[0].ToString());
string updatepwmodify4 = "update SmsMessage set RetryTimes =4 ,SendMessage='" + nobinddt.Rows[0].ItemArray[0].ToString() + "',SendTime='" + DateTime.Now + "' where RecID='" + Convert.ToInt32(opdt.Rows[n].ItemArray[0].ToString()) + "'";
CommonDB.ExecSQL(updatepwmodify4);
sucesscount++;
continue;
}
if (pdt.Rows.Count > 0)
{
if (pdt.Rows[0].ItemArray[0].ToString().Trim() == pw)//密码合理
{
if(pdt.Rows[0].ItemArray[1].ToString()=="2")
{
DataTable lossdonedt = null;
string sqllossdone = "select Content from SmsMessageBoard where Code='LOSS_DONE'";
CommonDB.OpenSql(sqllossdone, ref lossdonedt);
string[] mobill = new string[1];
mobill[0] = opdt.Rows[n].ItemArray[3].ToString().Trim();
sms.Send(mobill, lossdonedt.Rows[0].ItemArray[0].ToString());
string updatepwmodify5 = "update SmsMessage set RetryTimes =4 ,SendMessage='" + lossdonedt.Rows[0].ItemArray[0].ToString() + "',SendTime='" + DateTime.Now + "' where RecID='" + Convert.ToInt32(opdt.Rows[n].ItemArray[0].ToString()) + "'";
CommonDB.ExecSQL(updatepwmodify5);
sucesscount++;
continue;
}
else
{
SpParam spar = new SpParam("@CustomerID", SqlDbType.Int, 32, ParameterDirection.Input, Convert.ToInt32(opdt.Rows[n].ItemArray[2].ToString()));
List<SpParam> splist = new List<SpParam>();
splist.Add(spar);
int resultcnt = CommonDB.ExecProcedure("CardLossSelf", splist);
if (resultcnt != MyConst.RET_FAIL)
{
DataTable lossokdt = null;
string sqllossok = "select Content from SmsMessageBoard where Code='LOSS_OK'";
CommonDB.OpenSql(sqllossok, ref lossokdt);
string[] mob = new string[1];
mob[0] = opdt.Rows[n].ItemArray[3].ToString().Trim();
if (sms.Send(mob, lossokdt.Rows[0].ItemArray[0].ToString()))
{
string updateloss = "update SmsMessage set SendMessage='" + lossokdt.Rows[0].ItemArray[0].ToString() + "',SendTime='" + DateTime.Now + "',RetryTimes = RetryTimes + 1 where RecID='" + Convert.ToInt32(opdt.Rows[n].ItemArray[0].ToString()) + "'";
int updatecnt = CommonDB.ExecSQL(updateloss);
sucesscount++;
}
}
else
{
continue;
//string[] mob1 = new string[1];
//mob1[0] = opdt.Rows[n].ItemArray[3].ToString().Trim();
//sms.Send(mob1, "挂失操作失败,请稍后再发挂失操作指令");
}
}
}//end if (pdt.Rows[0].ItemArray[0].ToString().Trim() == pw)
else//密码非法
{
DataTable losspwddt = null;
string sqllosspwd = "select Content from SmsMessageBoard where Code='LOSS_PWD'";
CommonDB.OpenSql(sqllosspwd, ref losspwddt);
string[] mob11 = new string[1];
mob11[0] = opdt.Rows[n].ItemArray[3].ToString().Trim();
sms.Send(mob11, losspwddt.Rows[0].ItemArray[0].ToString());
string updatepwmodify3 = "update SmsMessage set RetryTimes =4,SendMessage='" + losspwddt.Rows[0].ItemArray[0].ToString() + "',SendTime='" + DateTime.Now + "' where RecID='" + Convert.ToInt32(opdt.Rows[n].ItemArray[0].ToString()) + "'";
CommonDB.ExecSQL(updatepwmodify3);
sucesscount++;
}
}
}//end if (opdt.Rows[n].ItemArray[1].ToString().Trim().Substring(0, 6) == "KT 002")
private void RecSmsMessage()
{
ShowInfo("正在监听用户短信......");
string Smessage = string.Empty;
sms.Receive(out Smessage);
if (Smessage !=null)
{
char[] splitchar = { '+', ',' };
string[] ArryMessage;
ArryMessage = Smessage.Split(splitchar);
ShowInfo("接收到" +(ArryMessage.Length/2) + "条短信,正在保存......");
for (int m = 0; m < ArryMessage.Length; m++)
{
if (m % 2 == 0)//ArryMessage[偶数],ArryMessage[偶数+1]分别存储了手机号,手机短信
{
string StrCount = @"select * from Customer A,Card B where A.MobilePhone='" + ArryMessage[m] + "'and A.State in ('1','2') and B.CustomerID=A.CustomerID";
//int exist =CommonDB.ExecSQLGetCount(StrCount);//判断是否有记录
DataTable countdt = null;
CommonDB.OpenSql(StrCount, ref countdt);
int exist = countdt.Rows.Count;
if (exist != 0)
{
string Selectinfo = @"select A.PhysicalCode,A.CardID,B.CustomerID from Card A,Customer B where B.MobilePhone='"+ArryMessage[m]+"' and A.State=B.State and A.CustomerID=B.CustomerID";//1取出表中相关信息记录;
DataTable dt = null;
CommonDB.OpenSql(Selectinfo,ref dt);
string Insertinfo = @"insert into SmsMessage(ArisesTime,PhysicalCode,CardID,CustomerID,MobilePhone,RetryTimes,RecvMessage) values('" + DateTime.Now + "','" + dt.Rows[0].ItemArray[0].ToString() + "','" + dt.Rows[0].ItemArray[1].ToString() + "','" + dt.Rows[0].ItemArray[2].ToString() + "','" + ArryMessage[m] + "','0','" + ArryMessage[m + 1] + "')";
if (CommonDB.ExecSQL(Insertinfo) == MyConst.RET_OK)//2插入表SmsMessage;
{
if (m/2==0)
ShowInfo("第"+(m+1)+"条短信已经成功保存");
else
ShowInfo("第" + (m/2+1) + "条短信已经成功保存");
}
else
{
continue;
}
}
else
{
DataTable nobinddt = null;
string sqlnobind = "select Content from SmsMessageBoard where Code='NO_BINDING'";
CommonDB.OpenSql(sqlnobind, ref nobinddt);
string[] ph={ArryMessage[m].ToString()};
sms.Send(ph, nobinddt.Rows[0].ItemArray[0].ToString());//发短信通知用户无手机记录,先到一卡通中心登记手机号
}
}
else//跳过
{
continue;
}
}
}
else
{
ShowInfo("未有新短信发送过来");
}
}
//标记记录已经发送成功
private bool MarkOK()
{
string commandText = @"UPDATE SmsMessage SET SendTime = '{0}' WHERE RecID = {1}";
commandText = string.Format(commandText, DateTime.Now, recID);
bool ret = (CommonDB.ExecSQL(commandText) == MyConst.RET_OK);
if (!ret)
{
ShowInfo("更新记录行失败:" + CommonDB.ErrorInfo);
Common.WriteLog("更新记录行失败(MarkOK):" + CommonDB.ErrorInfo);
}
return ret;
}
//标记记录发送失败
private bool MarkFail()
{
string commandText = @"UPDATE SmsMessage SET RetryTimes = RetryTimes + 1 WHERE RecID = {0}";
commandText = string.Format(commandText, recID);
bool ret = (CommonDB.ExecSQL(commandText) == MyConst.RET_OK);
if (!ret)
{
ShowInfo("更新记录行失败:" + CommonDB.ErrorInfo);
Common.WriteLog("更新记录行失败(MarkFail):" + CommonDB.ErrorInfo);
}
return ret;
}
public partial class FormMain : CommonLib.FormBase
{
private SmsBase sms;
public FormMain()
{
InitializeComponent();
}
private void FormMain_Load(object sender, EventArgs e)
{
this.mobilePhones = new string[1]; //手机号码数组(目前只能装一个元素)
mobilePhones[0] = string.Empty;
tmr.Interval = Int32.Parse(FileConfig.GetConfigValue("TimerInterval")) * 1000;
int smsVersion = Int32.Parse(FileConfig.GetConfigValue("SmsVersion"));
switch (smsVersion)
{
case 1:
sms = new SmsV1();
break;
case 2:
sms = new SmsV2();
break;
default:
Common.MsgStop2(string.Format("未定义的短信接口版本[{0}]。", smsVersion));
sms = new SmsBase();
break;
}
}
private void ShowInfo(string info)
{
if (edInfo.Lines.Length > 2000)
edInfo.Text = string.Empty;
if (info != "")
edInfo.AppendText(DateTime.Now.ToString() + " " + info + "\r\n");
else
edInfo.AppendText("\r\n");
}
private void tmr_Tick(object sender, EventArgs e)
{
tmr.Enabled = false;
if (CheckSmsService())
{
if (Int32.Parse(FileConfig.GetConfigValue("SmsVersion")) == 2)// add by tansy 2010 7 29
{
RecSmsMessage();
SmsOperation();
SendSmsMessage();
}
if (Int32.Parse(FileConfig.GetConfigValue("SmsVersion")) == 1)
{
SendSmsMessage();
}
if (Int32.Parse(FileConfig.GetConfigValue("SmsVersion")) == 0)
{
return;
}
}
tmr.Enabled = true;
}
//检查短信接口工作是否正常
private bool CheckSmsService()
{
ShowInfo("检测短信接口......");
if(sms.CheckInterface())
{
ShowInfo("短信接口工作正常");
return true;
}
else
{
ShowInfo(sms.ErrInfo);
Common.WriteLog(sms.ErrInfo);
return false;
}
}
//发送短信
DataTable table = null;
int recID; //待发信息表记录ID
string[] mobilePhones; //手机号码数组(目前只能装一个元素)
string smsMessage; //待发的短信内容
private void SendSmsMessage()
{
//检索待发短信(发送失败次数超过3次的短信,将不再发送)
ShowInfo("检索待发短信......");
string commandText = @"SELECT * FROM SmsMessage WHERE SendTime IS NULL AND RetryTimes <= 3 and SendMessage <> ''"; //and SendMessage='"+string.Empty+"' ";
if (CommonDB.OpenSql(commandText, ref table) != MyConst.RET_OK)
{
ShowInfo("检索待发短信失败:" + CommonDB.ErrorInfo);
Common.WriteLog("检索待发短信失败:" + CommonDB.ErrorInfo);
return;
}
if (table.Rows.Count < 1)
{
ShowInfo("没有需要发送的短信");
return;
}
//循环发送待发短信
string msgInfo;
foreach (DataRow row in table.Rows)
{
recID = row.Field<int>("RecID");
this.mobilePhones[0] = row.Field<string>("MobilePhone").Trim();
this.smsMessage = row.Field<string>("SendMessage").Trim();
msgInfo = string.Format(@"[手机号] {0} [待发短信] {1}......", this.mobilePhones[0], this.smsMessage.Substring(0, 20));
//手机号或待发短信有一个为空,则跳过该待发记录
if (this.mobilePhones[0] == string.Empty || this.smsMessage == string.Empty)
{
ShowInfo(msgInfo + "跳过");
this.MarkOK();
continue;
}
//正式发送短信
if(sms.Send(this.mobilePhones, this.smsMessage))
{
ShowInfo(msgInfo + "成功");
this.MarkOK();
}
else
{
ShowInfo(msgInfo + "失败");
Common.WriteLog(sms.ErrInfo);
this.MarkFail();
}
}
}