/// <summary>
/// 事件消息防复重
/// </summary>
/// <returns></returns>
public bool EventMsgPreventDuplicates(string fromusername, double createtime)
{
bool result = true;
OracleDBO mydbo = new OracleDBO();
mydbo.IsThrowException = true;
mydbo.AutoClose = false;
mydbo.BeginTransaction();
try
{
string sql = "select 1 from sys_p_wxeventmsglog t where t.fromusername=:fromusername and t.createtime=:createtime and rownum<2";
OracleDBO.OracleCmdParam[] oparam = new OracleDBO.OracleCmdParam[2];
oparam[0].Name = ":fromusername";
oparam[0].Type = OracleDbType.Varchar2;
oparam[0].Value = fromusername;
oparam[1].Name = ":createtime";
oparam[1].Type = OracleDbType.Double;
oparam[1].Value = createtime;
DataTable dt = mydbo.GetDataTable(sql, oparam);
if (dt != null && dt.Rows.Count > 0)
{
result = false;
mydbo.Rollback();
return(result);
}
sql = "insert into sys_p_wxeventmsglog(fromusername,createtime,logtime) values(:fromusername,:createtime,sysdate)";
mydbo.ExecuteNonQuery(sql, oparam);
mydbo.Commit();
}
catch (Exception ex)
{
mydbo.Rollback();
result = false;
LogWriter.WriteLog(ex);
}
finally
{
mydbo.Close();
}
return(result);
}
/// <summary>
/// 二维码扫描关注
/// </summary>
/// <param name="wxopenid">微信粉丝ID</param>
/// <param name="scene_id">场景ID</param>
/// <param name="wxuser">粉丝信息</param>
/// <param name="errmsg">出错时返回的错误提示信息</param>
/// <returns>是否成功执行</returns>
public bool QRCodeApplication(string wxopenid, string scene_id, WXFansInfo wxuser, ref string errmsg)
{
bool result = true;
OracleDBO mydbo = new OracleDBO();
mydbo.IsByParamName = true;
mydbo.IsThrowException = true;
mydbo.AutoClose = false;
try
{
#region 查询是否存在该粉丝
string sql1 = @"select count(*) from sys_s_wxfansinfo t where t.wxopenid = :wxopenid";
OracleDBO.OracleCmdParam[] oparam1 = new OracleDBO.OracleCmdParam[1];
oparam1[0].Name = ":wxopenid";
oparam1[0].Type = OracleDbType.Varchar2;
oparam1[0].Value = wxopenid;
int num = Convert.ToInt32(mydbo.ExecuteScalar(sql1, oparam1));
#endregion
mydbo.BeginTransaction();
if (num == 0)
{
#region 添加微信粉丝信息
string sql2 = @"insert into sys_s_wxfansinfo
(wxopenid,
nickname,
sex,
province,
city,
country,
headimgurl,
subscribetime,
subscribed)
values
(:wxopenid,
:nickname,
:sex,
:province,
:city,
:country,
:headimgurl,
:subscribetime,
:subscribed)";
OracleDBO.OracleCmdParam[] oparam2 = new OracleDBO.OracleCmdParam[9];
oparam2[0].Name = ":wxopenid";
oparam2[0].Type = OracleDbType.Varchar2;
oparam2[0].Value = wxopenid;
oparam2[1].Name = ":nickname";
oparam2[1].Type = OracleDbType.Varchar2;
oparam2[1].Value = wxuser.nickname;
oparam2[2].Name = ":sex";
oparam2[2].Type = OracleDbType.Varchar2;
oparam2[2].Value = wxuser.sex;
oparam2[3].Name = ":province";
oparam2[3].Type = OracleDbType.Varchar2;
oparam2[3].Value = wxuser.province;
oparam2[4].Name = ":city";
oparam2[4].Type = OracleDbType.Varchar2;
oparam2[4].Value = wxuser.city;
oparam2[5].Name = ":country";
oparam2[5].Type = OracleDbType.Varchar2;
oparam2[5].Value = wxuser.country;
oparam2[6].Name = ":headimgurl";
oparam2[6].Type = OracleDbType.Varchar2;
oparam2[6].Value = wxuser.headimgurl;
oparam2[7].Name = ":subscribetime";
oparam2[7].Type = OracleDbType.Date;
oparam2[7].Value = DateTime.Now;
oparam2[8].Name = ":subscribed";
oparam2[8].Type = OracleDbType.Varchar2;
oparam2[8].Value = 1;
int n = mydbo.ExecuteNonQuery(sql2, oparam2);
#endregion
}
else
{
#region 更新微信粉丝信息
string sql2 = @"update sys_s_wxfansinfo t
set t.nickname = :nickname,
t.sex = :sex,
t.province = :province,
t.city = :city,
t.country = :country,
t.headimgurl = :headimgurl,
t.subscribetime = :subscribetime,
t.subscribed = :subscribed
where t.wxopenid = :wxopenid";
OracleDBO.OracleCmdParam[] oparam2 = new OracleDBO.OracleCmdParam[9];
oparam2[0].Name = ":wxopenid";
oparam2[0].Type = OracleDbType.Varchar2;
oparam2[0].Value = wxopenid;
oparam2[1].Name = ":nickname";
oparam2[1].Type = OracleDbType.Varchar2;
oparam2[1].Value = wxuser.nickname;
oparam2[2].Name = ":sex";
oparam2[2].Type = OracleDbType.Varchar2;
oparam2[2].Value = wxuser.sex;
oparam2[3].Name = ":province";
oparam2[3].Type = OracleDbType.Varchar2;
oparam2[3].Value = wxuser.province;
oparam2[4].Name = ":city";
oparam2[4].Type = OracleDbType.Varchar2;
oparam2[4].Value = wxuser.city;
oparam2[5].Name = ":country";
oparam2[5].Type = OracleDbType.Varchar2;
oparam2[5].Value = wxuser.country;
oparam2[6].Name = ":headimgurl";
oparam2[6].Type = OracleDbType.Varchar2;
oparam2[6].Value = wxuser.headimgurl;
oparam2[7].Name = ":subscribetime";
oparam2[7].Type = OracleDbType.Date;
oparam2[7].Value = DateTime.Now;
oparam2[8].Name = ":subscribed";
oparam2[8].Type = OracleDbType.Varchar2;
oparam2[8].Value = 1;
int n = mydbo.ExecuteNonQuery(sql2, oparam2);
#endregion
}
string sql3 = @"select t.val1
from sys_p_wxqrmanage t
where t.scene_id = :scene_id
and t.intentions = '绑定用户'
and t.valid = 1";
OracleDBO.OracleCmdParam[] oparam3 = new OracleDBO.OracleCmdParam[1];
oparam3[0].Name = ":scene_id";
oparam3[0].Type = OracleDbType.Int32;
oparam3[0].Value = Convert.ToInt32(scene_id);
DataTable dt = mydbo.GetDataTable(sql3, oparam3);
if (dt != null && dt.Rows.Count > 0)
{
string usercode = dt.Rows[0]["val1"].ToString();
#region 更新二维码为已使用
string sql4 = @"update sys_p_wxqrmanage t
set t.valid = 0
where t.scene_id = :scene_id
and t.intentions = '绑定用户'
and t.valid = 1";
OracleDBO.OracleCmdParam[] oparam4 = new OracleDBO.OracleCmdParam[1];
oparam4[0].Name = ":scene_id";
oparam4[0].Type = OracleDbType.Int32;
oparam4[0].Value = scene_id;
int n = mydbo.ExecuteNonQuery(sql4, oparam4);
#endregion
#region 更新用户绑定微信信息
string sql5 = @"update sys_s_user t set t.wxopenid = :wxopenid where t.usercode = :usercode";
OracleDBO.OracleCmdParam[] oparam5 = new OracleDBO.OracleCmdParam[2];
oparam5[0].Name = ":wxopenid";
oparam5[0].Type = OracleDbType.Varchar2;
oparam5[0].Value = wxopenid;
oparam5[1].Name = ":usercode";
oparam5[1].Type = OracleDbType.Varchar2;
oparam5[1].Value = usercode;
n = mydbo.ExecuteNonQuery(sql5, oparam5);
#endregion
string sql6 = @"select t.username from sys_s_userinfo t where t.usercode=:usercode";
OracleDBO.OracleCmdParam[] oparam6 = new OracleDBO.OracleCmdParam[1];
oparam6[0].Name = ":usercode";
oparam6[0].Type = OracleDbType.Varchar2;
oparam6[0].Value = usercode;
DataTable dt2 = mydbo.GetDataTable(sql6, oparam6);
if (dt2 != null && dt2.Rows.Count > 0)
{
errmsg = "成功绑定用户：" + dt2.Rows[0]["username"].ToString();
}
mydbo.Commit();
}
else
{
result = false;
errmsg = "该二维码已失效";
mydbo.Commit();
}
}
catch (Exception ex)
{
mydbo.Rollback();
errmsg = ex.Message.ToString();
result = false;
LogWriter.WriteLog(ex);
}
finally
{
mydbo.Close();
}
return(result);
}
public string GetAccessToken(string wxappid, bool refresh = false)
{
string result = "";
int maxtrytimes = 3;
int currtry = 0;
string appsecret = WXApiInfo.appsecret;
DateTime stateupdatetime;
int maxfreshingtime = 10;//最大刷新时间（秒），超过则不等待
OracleDBO mydbo = new OracleDBO();
mydbo.AutoClose = false;
try
{
string token;
DateTime gettime;
int expires_in;
string status;
DataRow dr;
while (currtry < maxtrytimes)
{
dr = mydbo.GetDataRow("select * from sys_p_wxappaccesstoken t where t.wxappid='" + wxappid + "'");
if (dr != null)
{
token = dr["access_token"].ToString();
gettime = Convert.ToDateTime(dr["gettime"]);
expires_in = Convert.ToInt32(dr["expires_in"]);
status = dr["state"].ToString();
stateupdatetime = Convert.ToDateTime(dr["stateupdatetime"]);
if (status == "Normal")
{
//是否已超时
if ((DateTime.Now - gettime).TotalSeconds < (expires_in - 5) && !refresh)
{
//未超时并且不要求更新
result = token;
break;
}
else
{
#region 更新access_token流程
mydbo.BeginTransaction();
int effnum = mydbo.ExecuteNonQuery("update sys_p_wxappaccesstoken set state='Refreshing',stateupdatetime=sysdate where WXAppID='" + wxappid + "' and state='Normal' and gettime=to_date('" + gettime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')");
mydbo.Commit();
//如果影响数大于零，说明更新状态成功，则即是取得了更新权。
if (effnum > 0)
{
UserAccessToken tokeninfo = GetNewAccessToken(wxappid, appsecret);
if (tokeninfo.errcode == 0)
{
result = tokeninfo.access_token;
mydbo.BeginTransaction();
mydbo.ExecuteNonQuery("update sys_p_wxappaccesstoken set access_token='" + tokeninfo.access_token + "',expires_in=" + tokeninfo.expires_in.ToString() + ",gettime=to_date('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss'),state='Normal',stateupdatetime=sysdate where WXAppID='" + wxappid + "'");
mydbo.Commit();
break;
}
else
{
//如果未成功，先将状态改回来
mydbo.BeginTransaction();
mydbo.ExecuteNonQuery("update sys_p_wxappaccesstoken t set t.state='Normal',t.stateupdatetime=sysdate where t.access_token='" + token + "' and t.gettime=to_date('" + gettime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') and t.wxappid='" + wxappid + "'");
mydbo.Commit();
}
}
else
{
//如果不是Normal状态，可能是其他请求正在刷新中，等半秒再取
if ((DateTime.Now - stateupdatetime).TotalSeconds > maxfreshingtime)
{
mydbo.BeginTransaction();
mydbo.ExecuteNonQuery("update sys_p_wxappaccesstoken t set t.state='Normal',t.stateupdatetime=sysdate where t.access_token='" + token + "' and t.gettime=to_date('" + gettime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') and t.wxappid='" + wxappid + "'");
mydbo.Commit();
}
System.Threading.Thread.Sleep(500);
}
#endregion
}
}
else
{
//如果不是Normal状态，可能是其他请求正在刷新中，等半秒再取
if ((DateTime.Now - stateupdatetime).TotalSeconds > maxfreshingtime)
{
mydbo.BeginTransaction();
mydbo.ExecuteNonQuery("update sys_p_wxappaccesstoken t set t.state='Normal',t.stateupdatetime=sysdate where t.access_token='" + token + "' and t.gettime=to_date('" + gettime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') and t.wxappid='" + wxappid + "'");
mydbo.Commit();
}
System.Threading.Thread.Sleep(500);
}
}
else
{
UserAccessToken tokeninfo = GetNewAccessToken(wxappid, appsecret);
if (tokeninfo.errcode == 0)
{
result = tokeninfo.access_token;
mydbo.BeginTransaction();
mydbo.ExecuteNonQuery("insert into sys_p_wxappaccesstoken(WXAppID,access_token,expires_in,state,gettime,stateupdatetime) values('" + wxappid + "','" + tokeninfo.access_token + "'," + tokeninfo.expires_in.ToString() + ",'Normal',to_date('" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "','yyyy-mm-dd hh24:mi:ss'),to_date('" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "','yyyy-mm-dd hh24:mi:ss'))");
mydbo.Commit();
break;
}
}
currtry++;
}
}
catch (Exception ex)
{
LogWriter.WriteLog(ex);
}
mydbo.Close();
return(result);
}
/// <summary>
/// 添加表头
/// </summary>
/// <param name="pagecode"></param>
/// <param name="hcode"></param>
/// <param name="hname"></param>
/// <param name="breadth"></param>
/// <param name="nowrow"></param>
/// <param name="nowline"></param>
/// <param name="takerow"></param>
/// <param name="takeline"></param>
/// <param name="errmsg"></param>
/// <returns></returns>
public bool AddExcleHeader(string pagecode, string hcode, string hname, string breadth, string nowrow, string nowline, string takerow, string takeline, string selecttype, string optiontype, ref string errmsg)
{
bool result = true;
OracleDBO mydbo = new OracleDBO();
mydbo.IsThrowException = true;
mydbo.AutoClose = false;
mydbo.IsByParamName = true;
try
{
mydbo.BeginTransaction();
List <OracleDBO.OracleCmdParam> oparam = new List <OracleDBO.OracleCmdParam>();
string sql = @"select max(to_number(m.nowrow)) nowrow,max(to_number(m.nowline)) nowline from m_p_excel_header m where m.pagecode = :pagecode";
oparam.Add(OracleDBO.Param("pagecode", pagecode));
DataTable dt = mydbo.GetDataTable(sql, oparam);
//if (dt.Rows[0]["nowrow"].ToString().Equals("") && dt.Rows[0]["nowline"].ToString().Equals(""))
//{
// nowline = "1";
// nowrow = "1";
//}
//else
//{
// nowline = dt.Rows[0]["nowline"].ToString();
// nowrow = dt.Rows[0]["nowrow"].ToString();
//}
string sql1 = @"insert into m_p_excel_header
(pagecode, hcode, hname, nowrow, nowline, takerow, takeline)
values
(:pagecode, :hcode, :hname, :nowrow, :nowline, :takerow, :takeline)";
oparam.Add(OracleDBO.Param("hcode", hcode));
oparam.Add(OracleDBO.Param("hname", hname));
oparam.Add(OracleDBO.Param("nowrow", nowrow));
oparam.Add(OracleDBO.Param("nowline", nowline));
oparam.Add(OracleDBO.Param("takerow", takerow));
oparam.Add(OracleDBO.Param("takeline", takeline));
int n = mydbo.ExecuteNonQuery(sql1, oparam);
if (n > 0)
{
List <OracleDBO.OracleCmdParam> oparam1 = new List <OracleDBO.OracleCmdParam>();
string sql2 = @"insert into m_p_inputattribute(hcode,selecttype,optiontype) values(:hcode,:selecttype,:optiontype)";
oparam1.Add(OracleDBO.Param("hcode", hcode));
oparam1.Add(OracleDBO.Param("selecttype", selecttype));
oparam1.Add(OracleDBO.Param("optiontype", optiontype));
mydbo.ExecuteNonQuery(sql2, oparam1);
}
mydbo.Commit();
}
catch (Exception ex)
{
mydbo.Rollback();
errmsg = ex.Message.ToString();
result = false;
LogWriter.WriteLog(ex);
}
finally
{
mydbo.Close();
}
return(result);
}