/// <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);
}
//keyboard
/// <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 updataAddExcleHeader(string pagecode, string hcode, string oldhcode, 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
{
List <OracleDBO.OracleCmdParam> oparam = new List <OracleDBO.OracleCmdParam>();
if (!string.IsNullOrEmpty(hcode))
{
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("pagecode", pagecode));
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);
}
}
else
{
List <OracleDBO.OracleCmdParam> oparam2 = new List <OracleDBO.OracleCmdParam>();
string sql12 = @"update M_P_EXCEL_HEADER h set h.nowrow = :nowrow,h.nowline = :nowline where h.hcode = :hcode";
oparam2.Add(OracleDBO.Param("nowline", nowline));
oparam2.Add(OracleDBO.Param("hcode", oldhcode));
oparam2.Add(OracleDBO.Param("nowrow", nowrow));
int n = mydbo.ExecuteNonQuery(sql12, 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="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>
/// <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 AddEffectiveExcleHeader(string pagecode, string hcode, string hname, int 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
{
List <OracleDBO.OracleCmdParam> oparam = new List <OracleDBO.OracleCmdParam>();
string sql = @"select m.nowline nowline from m_p_excel_header m where m.pagecode = :pagecode and m.hname=:hname";
oparam.Add(OracleDBO.Param("pagecode", pagecode));
oparam.Add(OracleDBO.Param("hname", hname));
DataTable dt = mydbo.GetDataTable(sql, oparam);
string sql1 = @"insert into m_p_excel_effective
(pagecode,hcode, effectivename, linesort, takeline)
values
(:pagecode,:hcode, :hname, :nowline, :takeline)";
oparam.Add(OracleDBO.Param("nowline", dt.Rows[0]["nowline"]));
oparam.Add(OracleDBO.Param("takeline", takeline + ""));
oparam.Add(OracleDBO.Param("hcode", hcode));
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);
//}
}
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);
}
/// <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);
}
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="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="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 bool updataAddExcleHeader(int pagesize, int page, ref int totalnum, ref int pagecount,
ref DataTable dt, string pagecode, string onetypehcode, string onetype,
string onetypevalues, string datetypehcode, string datetypeselecttype, string startdate,
string enddata, string startdata_time, string enddata_time,
string digitaltypehcode, string digitaltypeselecttype, string twotypetype,
string oneval1, string oneval2, string choosetypehcode, string choosetypeselecttype, string choosetype, ref string errmsg)
{
bool result = true;
OracleDBO mydbo = new OracleDBO();
mydbo.IsThrowException = true;
mydbo.AutoClose = false;
mydbo.IsByParamName = true;
try
{
List <OracleDBO.OracleCmdParam> oparam1 = new List <OracleDBO.OracleCmdParam>();
#region 查询记录总数
string sql = "";
switch (onetype)
{
case "文本":
sql = "(t.hcode = :onetypehcode and c.content like '%' || :onetypevalues || '%')";
oparam1.Add(OracleDBO.Param("onetypehcode", onetypehcode));
oparam1.Add(OracleDBO.Param("onetypevalues", onetypevalues));
break;
default:
sql = "";
break;
}
string sql4 = "";
switch (choosetype)
{
case "已选中":
if (string.IsNullOrWhiteSpace(sql))
{
sql4 = " (t.hcode = :choosetypehcode and c.content = '0')";
}
else
{
sql4 = " or (t.hcode = :choosetypehcode and c.content = '0')";
}
oparam1.Add(OracleDBO.Param("choosetypehcode", choosetypehcode));
break;
case "未选中":
if (string.IsNullOrWhiteSpace(sql))
{
sql4 = " (t.hcode = :choosetypehcode and c.content = '1')";
}
else
{
sql4 = " or (t.hcode = :choosetypehcode and c.content = '1')";
}
oparam1.Add(OracleDBO.Param("choosetypehcode", choosetypehcode));
break;
default:
sql4 = "";
break;
}
string sql2 = "";
switch (datetypeselecttype)
{
case "时间":
if (string.IsNullOrWhiteSpace(sql) && string.IsNullOrWhiteSpace(sql4))
{
sql2 = " (t.hcode = :datetypehcode and to_date(c.content, 'yyyy/MM/dd') BETWEEN to_date(:startdate, 'yyyy/MM/dd') AND to_date(:enddata, 'yyyy/MM/dd'))";
}
else
{
sql2 = " or (t.hcode = :datetypehcode and to_date(c.content, 'yyyy/MM/dd') BETWEEN to_date(:startdate, 'yyyy/MM/dd') AND to_date(:enddata, 'yyyy/MM/dd'))";
}
oparam1.Add(OracleDBO.Param("startdate", startdate));
oparam1.Add(OracleDBO.Param("enddata", enddata));
oparam1.Add(OracleDBO.Param("datetypehcode", datetypehcode));
break;
default:
sql2 = "";
break;
}
string sql3 = "";
switch (digitaltypeselecttype)
{
case "数字":
if (string.IsNullOrWhiteSpace(sql) && string.IsNullOrWhiteSpace(sql4) && string.IsNullOrWhiteSpace(sql2))
{
sql3 = " t.pagecode = :pagecode and t.nowrow in (select h.nowrow from (select c.content, c.nowrow from M_P_EXCEL_CONTENT c where c.hcode = :hcode) h where h.content " + twotypetype + " (select round(:oneval2) || '%' from dual))";
}
else
{
sql3 = " or t.pagecode = :pagecode and t.nowrow in (select h.nowrow from (select c.content, c.nowrow from M_P_EXCEL_CONTENT c where c.hcode = :hcode) h where h.content " + twotypetype + " (select round(:oneval2) || '%' from dual))";
}
oparam1.Add(OracleDBO.Param("oneval2", oneval2));
oparam1.Add(OracleDBO.Param("hcode", digitaltypehcode));
oparam1.Add(OracleDBO.Param("pagecode", pagecode));
break;
case "百分比":
if (string.IsNullOrWhiteSpace(sql) && string.IsNullOrWhiteSpace(sql4) && string.IsNullOrWhiteSpace(sql2))
{
sql3 = " t.pagecode = :pagecode and t.nowrow in (select h.nowrow from (select c.content, c.nowrow from M_P_EXCEL_CONTENT c where c.hcode = :hcode) h where h.content " + twotypetype + " (select round(:oneval2) || '%' from dual))";
}
else
{
sql3 = " or t.pagecode = :pagecode and t.nowrow in (select h.nowrow from (select c.content, c.nowrow from M_P_EXCEL_CONTENT c where c.hcode = :hcode) h where h.content " + twotypetype + " (select round(:oneval2) || '%' from dual))";
}
oparam1.Add(OracleDBO.Param("oneval2", oneval2));
oparam1.Add(OracleDBO.Param("hcode", digitaltypehcode));
break;
default:
sql3 = "";
break;
}
#endregion
string sql11 = " and 1=1";
if (!string.IsNullOrWhiteSpace(sql) || !string.IsNullOrWhiteSpace(sql2) || !string.IsNullOrWhiteSpace(sql3))
{
sql11 = " and t.nowrow in (select c.nowrow from M_P_EXCEL_EFFECTIVE t left join m_p_excel_content c on t.hcode = c.hcode where " + sql + sql4 + sql2 + ")" + sql3;
}
else if (!(string.IsNullOrWhiteSpace(sql) && string.IsNullOrWhiteSpace(sql2) && string.IsNullOrWhiteSpace(sql3)))
{
sql11 = sql3;
}
#region 查询记录总数
string sql13 = @"select count(*) from M_P_EXCEL_CONTENT t
left join m_p_inputattribute i
on t.hcode = i.hcode
left join M_P_EXCEL_EFFECTIVE t1
on t.hcode = t1.hcode
and t.pagecode = t1.pagecode
where t.pagecode = :pagecode " + sql11;
oparam1.Add(OracleDBO.Param("pagecode", pagecode));
totalnum = Convert.ToInt32(mydbo.ExecuteScalar(sql13, oparam1));
#endregion
#region 统计页数
if (totalnum == 0)
{
return(true);
}
if (totalnum % pagesize > 0)
{
pagecount = totalnum / pagesize + 1;
}
else
{
pagecount = totalnum / pagesize;
}
if (page > pagecount)
{
page = pagecount;
}
if (page < 1)
{
page = 1;
}
#endregion
string sql10 = @"select t.pagecode,
t.nowrow,
t.nowline,
t.hcode,
t.createname,
t.ausercode,
t.agread,
t.content,
t1.linesort,
t1.takeline,
i.selecttype,
i.optiontype
from M_P_EXCEL_CONTENT t
left join m_p_inputattribute i
on t.hcode = i.hcode
left join M_P_EXCEL_EFFECTIVE t1
on t.hcode = t1.hcode
and t.pagecode = t1.pagecode
where t.pagecode = :pagecode " + sql11;
string sql111 = @"select *
from (select paging1.*, rownum 序号
from (" + sql10 + @") paging1
where ( select count(*) from (" + sql10 + @") paging1 group by paging1.nowrow having count(*) > 1) pp） <= :maxrownum) paging2
where paging2.序号 > :startrownum";
oparam1.Add(OracleDBO.Param("maxrownum", page * pagesize));
oparam1.Add(OracleDBO.Param("startrownum", pagesize * (page - 1)));
//OracleDBO.PagingSQL(ref sql10, ref oparam1, page, pagesize, ref errmsg);
dt = mydbo.GetDataTable(sql111, oparam1);
}
catch (Exception ex)
{
errmsg = ex.Message.ToString();
result = false;
LogWriter.WriteLog(ex);
}
finally
{
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);
}
/// <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 updataAddEffectiveExcleHeader(string pagecode, string hcode, string oldhode, string hname, int 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
{
List <OracleDBO.OracleCmdParam> oparam = new List <OracleDBO.OracleCmdParam>();
string sql = @"select m.nowline from M_P_EXCEL_CONTENT m where m.pagecode =:pagecode order by m.nowline desc";
// string sql = @"select m.nowline from m_p_excel_header m where m.pagecode = :pagecode and m.hname=:hname";
oparam.Add(OracleDBO.Param("pagecode", pagecode));
DataTable dt1 = mydbo.GetDataTable(sql, oparam);
string aaa = dt1.Rows[0]["nowline"].ToString();
int A = Convert.ToInt32(aaa);
int a = A + 1;
string a1 = Convert.ToString(a);
if (!string.IsNullOrEmpty(hcode))
{
string sql1 = @"insert into m_p_excel_effective
(pagecode,hcode, effectivename, linesort, takeline)
values
(:pagecode,:hcode, :hname, :nowline, :takeline)";
oparam.Add(OracleDBO.Param("nowline", a1));
oparam.Add(OracleDBO.Param("takeline", takeline + ""));
oparam.Add(OracleDBO.Param("hcode", hcode));
oparam.Add(OracleDBO.Param("hname", hname));
int n = mydbo.ExecuteNonQuery(sql1, oparam);
if (n > 0)
{
List <OracleDBO.OracleCmdParam> oparam2 = new List <OracleDBO.OracleCmdParam>();
string sql121 = @"select * from m_p_excel_content c where c.pagecode=:pagecode";
oparam2.Add(OracleDBO.Param("pagecode", pagecode));
DataTable dt = mydbo.GetDataTable(sql121, oparam2);
int y = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
List <OracleDBO.OracleCmdParam> oparam21 = new List <OracleDBO.OracleCmdParam>();
if (y != ParseInteger.ParseInt(dt.Rows[i]["nowrow"].ToString()))
{
List <OracleDBO.OracleCmdParam> oparam20 = new List <OracleDBO.OracleCmdParam>();
string sql1210 = @"select * from m_p_excel_content c where c.pagecode=:pagecode and c.hcode=:hcode and c.nowrow=:nowrow";
oparam20.Add(OracleDBO.Param("pagecode", pagecode));
oparam20.Add(OracleDBO.Param("nowrow", dt.Rows[i]["nowrow"].ToString()));
oparam20.Add(OracleDBO.Param("hcode", hcode));
DataTable dt12 = mydbo.GetDataTable(sql1210, oparam20);
if (dt12.Rows.Count < 1)
{
string sql12 = @"insert into m_p_excel_content
(pagecode, hcode, nowrow, nowline)
values
(:pagecode, :hcode, :nowrow, :nowline)";
oparam21.Add(OracleDBO.Param("nowrow", dt.Rows[i]["nowrow"].ToString()));
oparam21.Add(OracleDBO.Param("nowline", a1));
oparam21.Add(OracleDBO.Param("pagecode", pagecode));
oparam21.Add(OracleDBO.Param("hcode", hcode));
int n1 = mydbo.ExecuteNonQuery(sql12, oparam21);
}
y = ParseInteger.ParseInt(a1);
}
}
//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);
}
}
else
{
List <OracleDBO.OracleCmdParam> oparam11 = new List <OracleDBO.OracleCmdParam>();
string sql1 = @"update m_p_excel_effective h set h.linesort = :nowline where h.hcode = :hcode";
oparam11.Add(OracleDBO.Param("nowline", dt1.Rows[0]["nowline"]));
oparam11.Add(OracleDBO.Param("hcode", oldhode));
int n = mydbo.ExecuteNonQuery(sql1, oparam11);
}
}
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="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);
}
