/// <summary>
/// 获取微信粉丝绑定的用户列表
/// </summary>
/// <param name="wxopenid">微信粉丝ID</param>
/// <param name="dt">返回列表</param>
/// <param name="errmsg">出错时返回的错误提示信息</param>
/// <returns>是否成功执行</returns>
//public bool GetWXFansBindUserInfo(string wxopenid, ref DataTable dt, ref string errmsg)
//{
// bool result = true;
// OracleDBO mydbo = new OracleDBO();
// mydbo.IsByParamName = true;
// mydbo.IsThrowException = true;
// try
// {
// string sql2 = @"select t.usercode,t.username,t.account,t.wxopenid,t.usertype,t.state from WOW_A_USERINFO t where t.wxopenid='oeZqt5jQPCvZubmkNqXwhaE4q-cE'";
// OracleDBO.OracleCmdParam[] oparam2 = new OracleDBO.OracleCmdParam[1];
// oparam2[0].Name = ":wxopenid";
// oparam2[0].Type = OracleDbType.Varchar2;
// oparam2[0].Value = wxopenid;
// dt = mydbo.GetDataTable(sql2, oparam2);
// }
// catch (Exception ex)
// {
// errmsg = ex.Message.ToString();
// result = false;
// LogWriter.WriteLog(ex);
// }
// finally
// {
// mydbo.Close();
// }
// return result;
//}
public bool GetWXFansBindUserInfo(string wxopenid, ref DataTable dt, ref DataTable dt1, ref string errmsg)
{
bool result = true;
OracleDBO mydbo = new OracleDBO();
mydbo.IsThrowException = true;
mydbo.AutoClose = false;
try
{
string sql2 = @"select t.usercode,t.username,t.account,t.wxopenid,t.usertype,t.state from wow_a_userinfo t where t.wxopenid=:wxopenid";
OracleDBO.OracleCmdParam[] oparam2 = new OracleDBO.OracleCmdParam[1];
oparam2[0].Name = ":wxopenid";
oparam2[0].Type = OracleDbType.Varchar2;
oparam2[0].Value = wxopenid;
dt = mydbo.GetDataTable(sql2, oparam2);
}
catch (Exception ex)
{
errmsg = ex.Message.ToString();
result = false;
LogWriter.WriteLog(ex);
}
finally
{
mydbo.Close();
}
return(result);
}
/// <summary>
/// 取消关注
/// </summary>
/// <param name="wxopenid">微信粉丝ID</param>
/// <param name="errmsg">出错时返回的错误提示信息</param>
/// <returns>是否成功执行</returns>
public bool UnSubscribe(string wxopenid, ref string errmsg)
{
bool result = true;
OracleDBO mydbo = new OracleDBO();
mydbo.IsByParamName = true;
mydbo.IsThrowException = true;
try
{
string sql1 = @"update sys_s_wxfansinfo t
set t.subscribed = 0, t.subscribetime = :subscribetime
where t.wxopenid = :wxopenid";
OracleDBO.OracleCmdParam[] oparam1 = new OracleDBO.OracleCmdParam[2];
oparam1[0].Name = ":subscribetime";
oparam1[0].Type = OracleDbType.Date;
oparam1[0].Value = DateTime.Now;
oparam1[1].Name = ":wxopenid";
oparam1[1].Type = OracleDbType.Varchar2;
oparam1[1].Value = wxopenid;
int n = mydbo.ExecuteNonQuery(sql1, oparam1);
}
catch (Exception ex)
{
errmsg = ex.Message.ToString();
result = false;
LogWriter.WriteLog(ex);
}
finally
{
mydbo.Close();
}
return(result);
}
/// <summary>
/// 存
/// </summary>
/// <param name="wxopenid">微信粉丝ID</param>
/// <param name="dt">返回列表</param>
/// <param name="errmsg">出错时返回的错误提示信息</param>
/// <returns>是否成功执行</returns>
public bool Getopenid(string wxopenid, string account, ref string errmsg)
{
bool result = true;
OracleDBO mydbo = new OracleDBO();
mydbo.IsThrowException = true;
mydbo.IsByParamName = true;
mydbo.AutoClose = false;
try
{
string sql12 = @"select * from W_LOGIN t where t.opcode=:opcode";
OracleDBO.OracleCmdParam[] oparam12 = new OracleDBO.OracleCmdParam[1];
oparam12[0].Name = ":opcode";
oparam12[0].Type = OracleDbType.Varchar2;
oparam12[0].Value = account;
DataTable dt = mydbo.GetDataTable(sql12, oparam12);
if (dt.Rows.Count > 0)
{
string sql1 = @"update W_LOGIN t set t.wxopenid=:wxopenid where t.opcode=:opcode";
OracleDBO.OracleCmdParam[] oparam1 = new OracleDBO.OracleCmdParam[2];
oparam1[0].Name = ":wxopenid";
oparam1[0].Type = OracleDbType.Varchar2;
oparam1[0].Value = wxopenid;
oparam1[1].Name = ":opcode";
oparam1[1].Type = OracleDbType.Varchar2;
oparam1[1].Value = account;
int n = mydbo.ExecuteNonQuery(sql1, oparam1);
}
else
{
string sql1 = @"insert into W_LOGIN(wxopenid,opcode) values(:wxopenid,:opcode)";
OracleDBO.OracleCmdParam[] oparam1 = new OracleDBO.OracleCmdParam[2];
oparam1[0].Name = ":wxopenid";
oparam1[0].Type = OracleDbType.Varchar2;
oparam1[0].Value = wxopenid;
oparam1[1].Name = ":opcode";
oparam1[1].Type = OracleDbType.Varchar2;
oparam1[1].Value = account;
int n = mydbo.ExecuteNonQuery(sql1, oparam1);
}
}
catch (Exception ex)
{
errmsg = ex.Message.ToString();
result = false;
LogWriter.WriteLog(ex);
}
finally
{
mydbo.Close();
}
return(result);
}
/// <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="subscribed">是否关注，0取消关注，1关注</param>
/// <param name="errmsg">出错时返回的错误提示信息</param>
/// <returns>是否成功执行</returns>
public bool SubscribeLog(string wxopenid, int subscribed, ref string errmsg)
{
bool result = true;
OracleDBO mydbo = new OracleDBO();
mydbo.IsByParamName = true;
mydbo.IsThrowException = true;
try
{
string sql1 = @"insert into sys_p_wxsubscribelog
(wxopenid, createdate, subscribed)
values
(:wxopenid, :createdate, :subscribed)";
OracleDBO.OracleCmdParam[] oparam1 = new OracleDBO.OracleCmdParam[3];
oparam1[0].Name = ":wxopenid";
oparam1[0].Type = OracleDbType.Varchar2;
oparam1[0].Value = wxopenid;
oparam1[1].Name = ":createdate";
oparam1[1].Type = OracleDbType.Date;
oparam1[1].Value = DateTime.Now;
oparam1[2].Name = ":subscribed";
oparam1[2].Type = OracleDbType.Int32;
oparam1[2].Value = subscribed;
int n = mydbo.ExecuteNonQuery(sql1, oparam1);
}
catch (Exception ex)
{
errmsg = ex.Message.ToString();
result = false;
LogWriter.WriteLog(ex);
}
finally
{
mydbo.Close();
}
return(result);
}
public bool GetUsercode(string id, string account, ref string errmsg)
{
bool result = true;
OracleDBO mydbo = new OracleDBO();
mydbo.IsThrowException = true;
mydbo.IsByParamName = true;
mydbo.AutoClose = false;
try
{
string wxopenid = "";
int n = 0;
account = account.Trim();
string sql12 = @"select t.wxopenid from W_LOGIN t where t.opcode=:account";
OracleDBO.OracleCmdParam[] oparam12 = new OracleDBO.OracleCmdParam[1];
oparam12[0].Name = ":account";
oparam12[0].Type = OracleDbType.Varchar2;
oparam12[0].Value = account;
DataTable dt1 = mydbo.GetDataTable(sql12, oparam12);
if (dt1.Rows.Count > 0)
{
wxopenid = dt1.Rows[0]["wxopenid"].ToString();
}
if (id == "0")
{
string sql1 = @"update WOW_A_USERINFO t2 set t2.wxopenid=:wxopenid where t2.username=:username";
OracleDBO.OracleCmdParam[] oparam1 = new OracleDBO.OracleCmdParam[2];
oparam1[0].Name = ":wxopenid";
oparam1[0].Type = OracleDbType.Varchar2;
oparam1[0].Value = wxopenid;
oparam1[1].Name = ":username";
oparam1[1].Type = OracleDbType.Varchar2;
oparam1[1].Value = account;
n = mydbo.ExecuteNonQuery(sql1, oparam1);
}
if (id == "1")
{
DALWXMessageHandleBasic mysend = new DALWXMessageHandleBasic();
bool success = mysend.Sendfalse(WXApiInfo.wxappid, wxopenid, "绑定被拒绝", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), ref errmsg);
//推送绑定不被允许
string sql4 = @"delete from W_LOGIN t t.opcode=:opcode";
OracleDBO.OracleCmdParam[] oparam4 = new OracleDBO.OracleCmdParam[1];
oparam4[0].Name = ":opcode";
oparam4[0].Type = OracleDbType.Varchar2;
oparam4[0].Value = account;
int n4 = mydbo.ExecuteNonQuery(sql4, oparam4);
result = false;
}
if (n > 0)
{
//推送绑定允许
DALWXMessageHandleBasic mysend = new DALWXMessageHandleBasic();
bool success = mysend.SendSecss(WXApiInfo.wxappid, wxopenid, "绑定已同意", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), ref errmsg);
string sql4 = @"delete from W_LOGIN t t.opcode=:opcode";
OracleDBO.OracleCmdParam[] oparam4 = new OracleDBO.OracleCmdParam[1];
oparam4[0].Name = ":opcode";
oparam4[0].Type = OracleDbType.Varchar2;
oparam4[0].Value = account;
int n4 = mydbo.ExecuteNonQuery(sql4, oparam4);
}
}
catch (Exception ex)
{
errmsg = ex.Message.ToString();
result = false;
LogWriter.WriteLog(ex);
}
finally
{
mydbo.Close();
}
return(result);
}
/// <summary>
/// 更新微信
/// </summary>
/// <param name="wxfansinfo">微信粉丝信息</param>
/// <param name="errmsg">出错时返回的错误提示信息</param>
/// <returns>是否成功执行</returns>
public bool UpdateWXFansInfo(WXFansInfo wxfansinfo, ref string errmsg)
{
bool result = true;
OracleDBO mydbo = new OracleDBO();
mydbo.IsByParamName = true;
mydbo.IsThrowException = true;
try
{
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 = wxfansinfo.openid;
int num = Convert.ToInt32(mydbo.ExecuteScalar(sql1, oparam1));
if (num == 0)
{
#region 添加粉丝
string sql2 = @"insert into sys_s_wxfansinfo
(wxopenid,
nickname,
sex,
province,
city,
country,
headimgurl,
subscribed)
values
(:wxopenid,
:nickname,
:sex,
:province,
:city,
:country,
:headimgurl,
:subscribed)";
OracleDBO.OracleCmdParam[] oparam2 = new OracleDBO.OracleCmdParam[8];
oparam2[0].Name = ":wxopenid";
oparam2[0].Type = OracleDbType.Varchar2;
oparam2[0].Value = wxfansinfo.openid;
oparam2[1].Name = ":nickname";
oparam2[1].Type = OracleDbType.Varchar2;
oparam2[1].Value = wxfansinfo.nickname;
oparam2[2].Name = ":sex";
oparam2[2].Type = OracleDbType.Varchar2;
oparam2[2].Value = wxfansinfo.sex == "1" ? "男" : "女";
oparam2[3].Name = ":province";
oparam2[3].Type = OracleDbType.Varchar2;
oparam2[3].Value = wxfansinfo.province;
oparam2[4].Name = ":city";
oparam2[4].Type = OracleDbType.Varchar2;
oparam2[4].Value = wxfansinfo.city;
oparam2[5].Name = ":country";
oparam2[5].Type = OracleDbType.Varchar2;
oparam2[5].Value = wxfansinfo.country;
oparam2[6].Name = ":headimgurl";
oparam2[6].Type = OracleDbType.Varchar2;
oparam2[6].Value = wxfansinfo.headimgurl;
oparam2[7].Name = ":subscribed";
oparam2[7].Type = OracleDbType.Int32;
oparam2[7].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
where t.wxopenid = :wxopenid";
OracleDBO.OracleCmdParam[] oparam2 = new OracleDBO.OracleCmdParam[7];
oparam2[0].Name = ":wxopenid";
oparam2[0].Type = OracleDbType.Varchar2;
oparam2[0].Value = wxfansinfo.openid;
oparam2[1].Name = ":nickname";
oparam2[1].Type = OracleDbType.Varchar2;
oparam2[1].Value = wxfansinfo.nickname;
oparam2[2].Name = ":sex";
oparam2[2].Type = OracleDbType.Varchar2;
oparam2[2].Value = wxfansinfo.sex == "1" ? "男" : "女";
oparam2[3].Name = ":province";
oparam2[3].Type = OracleDbType.Varchar2;
oparam2[3].Value = wxfansinfo.province;
oparam2[4].Name = ":city";
oparam2[4].Type = OracleDbType.Varchar2;
oparam2[4].Value = wxfansinfo.city;
oparam2[5].Name = ":country";
oparam2[5].Type = OracleDbType.Varchar2;
oparam2[5].Value = wxfansinfo.country;
oparam2[6].Name = ":headimgurl";
oparam2[6].Type = OracleDbType.Varchar2;
oparam2[6].Value = wxfansinfo.headimgurl;
int n = mydbo.ExecuteNonQuery(sql2, oparam2);
#endregion
}
}
catch (Exception ex)
{
errmsg = ex.Message.ToString();
result = false;
LogWriter.WriteLog(ex);
}
finally
{
mydbo.Close();
}
return(result);
}
/// <summary>
/// 返回序列
/// </summary>
/// <param name="SeqType">类型</param>
/// <param name="SeqLength">长度</param>
/// <param name="Prefix">前缀</param>
/// <param name="Sequence">序列</param>
/// <param name="errmsg">出错时返回的错误提示信息</param>
/// <returns>是否成功执行</returns>
public bool GenerateSequence(string SeqType, int SeqLength, string Prefix, ref int Sequence, ref string errmsg)
{
bool result = true;
OracleDBO mydbo = new OracleDBO();
mydbo.IsThrowException = true;
mydbo.IsByParamName = true;
mydbo.AutoClose = false;
try
{
#region 查询该类型在数据库中是否有记录
int n;
string sql1 = @"select t.seq from sys_p_sequence t where t.seqtype=:seqtype";
OracleDBO.OracleCmdParam[] oparam1 = new OracleDBO.OracleCmdParam[1];
oparam1[0].Name = ":seqtype";
oparam1[0].Type = OracleDbType.Varchar2;
oparam1[0].Value = SeqType;
DataTable dt = mydbo.GetDataTable(sql1, oparam1);
#endregion
if (dt.Rows.Count > 0)
{
#region 更新该类型序列的值
string sql2 = @"update sys_p_sequence t set t.seq = :seq where t.seqtype = :seqtype";
OracleDBO.OracleCmdParam[] oparam2 = new OracleDBO.OracleCmdParam[2];
oparam2[0].Name = ":seq";
oparam2[0].Type = OracleDbType.Int32;
oparam2[0].Value = Convert.ToInt32(dt.Rows[0]["seq"]) + 1;
oparam2[1].Name = ":seqtype";
oparam2[1].Type = OracleDbType.Varchar2;
oparam2[1].Value = SeqType;
n = mydbo.ExecuteNonQuery(sql2, oparam2);
Sequence = Convert.ToInt32(dt.Rows[0]["seq"]);
#endregion
}
else
{
#region 添加序列类型
string sql2 = @"insert into sys_p_sequence(seqtype,seqlength,prefix,seq) values(:seqtype,:seqlength,:prefix,:seq)";
OracleDBO.OracleCmdParam[] oparam2 = new OracleDBO.OracleCmdParam[4];
oparam2[0].Name = ":seqtype";
oparam2[0].Type = OracleDbType.Varchar2;
oparam2[0].Value = SeqType;
oparam2[1].Name = ":seqlength";
oparam2[1].Type = OracleDbType.Int32;
oparam2[1].Value = SeqLength;
oparam2[2].Name = ":prefix";
oparam2[2].Type = OracleDbType.Varchar2;
oparam2[2].Value = Prefix;
oparam2[3].Name = ":seq";
oparam2[3].Type = OracleDbType.Int32;
oparam2[3].Value = 2;
n = mydbo.ExecuteNonQuery(sql2, oparam2);
Sequence = 1;
#endregion
}
}
catch (Exception ex)
{
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);
}
/// <summary>
/// 创建临时二维码
/// </summary>
/// <param name="ticket">获取的二维码ticket，凭借此ticket可以在有效时间内换取二维码。</param>
/// <param name="expire_seconds">该二维码有效时间，以秒为单位。 最大不超过2592000（即30天）。</param>
/// <param name="url">二维码图片解析后的地址，开发者可根据该地址自行生成需要的二维码图片</param>
/// <param name="qrtype">二维码类型，0为临时二维码，1为永久二维码</param>
/// <param name="scene_id">二维码的场景ID</param>
/// <param name="intentions">意图，【关注公众号】【绑定用户】</param>
/// <param name="failuredate">失效时间</param>
/// <param name="errmsg">出错时返回的错误提示信息</param>
/// <returns>是否成功执行</returns>
public bool CreateTmpQRCode(string ticket, int expire_seconds, string url, int qrtype, int scene_id, string intentions, DateTime failuredate, ref string errmsg)
{
bool result = true;
OracleDBO mydbo = new OracleDBO();
mydbo.IsByParamName = true;
mydbo.IsThrowException = true;
mydbo.AutoClose = false;
try
{
#region 获取微信二维码是否存在记录
string sql1 = @"select coun(*)
from sys_p_wxqrmanage t
where t.valid = 1
and t.scene_id = :scene_id";
OracleDBO.OracleCmdParam[] oparam1 = new OracleDBO.OracleCmdParam[1];
oparam1[0].Name = ":scene_id";
oparam1[0].Type = OracleDbType.Int32;
oparam1[0].Value = scene_id;
int num = Convert.ToInt32(mydbo.ExecuteScalar(sql1, oparam1));
#endregion
if (num == 0)
{
#region 添加二维码信息
string sql2 = @"insert into sys_p_wxqrmanage
(ticket, expire_seconds, url, qrtype, scene_id, intentions, failuredate)
values
(:ticket,
:expire_seconds,
:url,
:qrtype,
:scene_id,
:intentions,
:failuredate)";
OracleDBO.OracleCmdParam[] oparam2 = new OracleDBO.OracleCmdParam[7];
oparam2[0].Name = ":ticket";
oparam2[0].Type = OracleDbType.Varchar2;
oparam2[0].Value = ticket;
oparam2[1].Name = ":expire_seconds";
oparam2[1].Type = OracleDbType.Int32;
oparam2[1].Value = expire_seconds;
oparam2[2].Name = ":url";
oparam2[2].Type = OracleDbType.Varchar2;
oparam2[2].Value = url;
oparam2[3].Name = ":qrtype";
oparam2[3].Type = OracleDbType.Int32;
oparam2[3].Value = qrtype;
oparam2[4].Name = ":scene_id";
oparam2[4].Type = OracleDbType.Int32;
oparam2[4].Value = scene_id;
oparam2[5].Name = ":intentions";
oparam2[5].Type = OracleDbType.Varchar2;
oparam2[5].Value = intentions;
oparam2[6].Name = ":failuredate";
oparam2[6].Type = OracleDbType.Date;
oparam2[6].Value = failuredate;
int n = mydbo.ExecuteNonQuery(sql2, oparam2);
#endregion
}
else
{
errmsg = "该场景ID已存在";
result = false;
}
}
catch (Exception ex)
{
errmsg = ex.Message.ToString();
result = false;
LogWriter.WriteLog(ex);
}
finally
{
mydbo.Close();
}
return(result);
}
/// <summary>
/// 获取微信粉丝绑定的用户列表
/// </summary>
/// <param name="wxopenid">微信粉丝ID</param>
/// <param name="dt">返回列表</param>
/// <param name="errmsg">出错时返回的错误提示信息</param>
/// <returns>是否成功执行</returns>
public bool GetWXFansBindUserInfo(string wxopenid, ref DataTable dt, ref DataTable dt_page, ref string errmsg)
{
bool result = true;
OracleDBO mydbo = new OracleDBO();
mydbo.IsThrowException = true;
mydbo.IsByParamName = true;
mydbo.AutoClose = false;
try
{
mydbo.BeginTransaction();
string sql1 = @"select t1.usercode,
t1.password,
t1.usertype,
t1.createdate,
t1.wxopenid,
t1.account,
t2.username,
t2.position,
t2.sex,
t2.birthday,
t2.address,
t2.phonenumber,
t2.hiredate,
t3.nickname,
t3.headimgurl,
t4.departmentcode,
t4.departmentname department,
t1.idnumber,
t1.idkey
from sys_s_user t1
join sys_s_userinfo t2
on t2.usercode = t1.usercode
left join sys_s_wxfansinfo t3
on t3.wxopenid = t1.wxopenid
left join sys_s_departmentinfo t4
on t4.departmentcode = t2.department
where t1.status = 1
and t1.usertype in (0, 2)
and t1.wxopenid = :wxopenid";
OracleDBO.OracleCmdParam[] oparam1 = new OracleDBO.OracleCmdParam[1];
oparam1[0].Name = ":wxopenid";
oparam1[0].Type = OracleDbType.Varchar2;
oparam1[0].Value = wxopenid;
dt = mydbo.GetDataTable(sql1, oparam1);
if (dt != null && dt.Rows.Count > 0)
{
string usercode = dt.Rows[0]["usercode"].ToString();
//这里去查页面列表，dt_page
string rolecode = "";
string sql = @"select t.rolecode from SYS_S_USER t where t.usercode=:usercode";
OracleDBO.OracleCmdParam[] oparam13 = new OracleDBO.OracleCmdParam[1];
oparam13[0].Name = ":usercode";
oparam13[0].Type = OracleDbType.Varchar2;
oparam13[0].Value = usercode;
DataTable abc = mydbo.GetDataTable(sql, oparam13);
for (int i = 0; i < abc.Rows.Count; i++)
{
rolecode = abc.Rows[i]["rolecode"].ToString();
}
string sql2 = @"select t1.pagecode, t1.pagename, t1.ico, t1.url, t1.superior
from M_WX_ROLEPAGE t, M_WX_PCPAGEMANAGE t1
where t.pagecode = t1.pagecode
and t.rolecode = :rolecode ";
OracleDBO.OracleCmdParam[] oparam12 = new OracleDBO.OracleCmdParam[1];
oparam12[0].Name = ":rolecode";
oparam12[0].Type = OracleDbType.Varchar2;
oparam12[0].Value = rolecode;
dt_page = mydbo.GetDataTable(sql2, oparam12);
}
}
catch (Exception ex)
{
mydbo.Rollback();
errmsg = ex.Message.ToString();
result = false;
LogWriter.WriteLog(ex);
}
finally
{
mydbo.Close();
}
return(result);
}
