/// <summary>
/// 新增微信用户
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool AddWXuser(MWXUserInfo model)
{
//// sql语句
string sql = "INSERT INTO wxuser(wxuserid,subscribe,openid,nickname,sex,sexdes,city,country,province,language,headimgurl,subscribe_time,remark,tagid_list,subscribe_scene,subscribe_scene_des,isDelete,isEffective,great_time,modify_time) " +
"VALUES (?wxuserid,?subscribe,?openid,?nickname,?sex,?sexdes,?city,?country,?province,?language,?headimgurl,?subscribe_time,?remark,?tagid_list,?subscribe_scene,?subscribe_scene_des,?isDelete,?isEffective,?great_time,?modify_time)";
List <MySqlParameter> parameterList = GetMySqlParameterListByModel(model);
//// 执行操作
return(PKMySqlHelper.ExecuteNonQuery(sql, parameterList.ToArray()) > 0);
}
/// <summary>
/// 新增微信用户
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool AddWXuser(MWXUserInfo model)
{
if (model == null)
{
return(false);
}
model.isDelete = 0;
model.isEffective = 1;
return(opertService.AddWXuser(model));
}
/// <summary>
/// 根据OpenID 获取用户基本信息(需关注公众号）
/// </summary>
/// <param name="openId"></param>
public MWXUserInfo GetUserInfo(string openId)
{
//// 微信API地址
string wxAPIURL = WebConfigeOpert.GetWXAPIURL();
String strUrl = string.Format("{0}cgi-bin/user/info?access_token={1}&openid={2}&lang=zh_CN", wxAPIURL, WXTokenOpert.GetAccessToken(), openId);
MWXUserInfo model = PublicTools.HttpGetRequest <MWXUserInfo>(strUrl);
//// 对获取大的用户信息的关注日期和头像做处理
if (model != null)
{
model.subscribe_time = PublicTools.GetDateTimeWithSecond(Convert.ToInt64(model.subscribe_time)).ToString("yyyy-MM-dd HH:mm:ss");
model.headimgurl = model.headimgurl.Substring(0, model.headimgurl.LastIndexOf('/') + 1);
}
return(model);
}
/// <summary>
/// 根据code获取用户信息
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public MWXUserInfo GetWXUserInfoByCode(string code)
{
//// 实现逻辑步骤
//// 1、首先判断在session中是否已经存在对应的cdoe，根据实际情况获取openid
//// 2、如果存在说明不是第一次跳转过来的，那么直接用session中对应的openid做操作
//// 3、如果不存在，那么需要根据code，到微信获取对应的openid
//// 4、根据openid 到数据库中查询用户数据信息
//// 5、如果数据库未查询到数据信息，那么根据openid到微信查询对应的用户信息
//// 6、对步骤5获取的用户数据信息入库
//// 7、返回最终查询到的用户信息对象
//// 用户信息模型
MWXUserInfo model = null;
string openid = string.Empty;
//// 1、首先判断在session中是否已经存在对应的cdoe，根据实际情况获取openid
HttpContext httpContext = HttpContext.Current;
if (string.IsNullOrEmpty(httpContext.Session["code"] + string.Empty))
{
//// 3、如果不存在，那么需要根据code，到支付获取对应的openid
openid = new WXApiOpert().GetOpenIdByCode(code);
//// LogOpert.AddWeiXinMessage("1根据code到微信获取openid：" + openid);
}
else
{
//// 取session中的openid
openid = httpContext.Session["openid"] + string.Empty;
//// LogOpert.AddWeiXinMessage("在内存中获取openid：" + openid);
if (string.IsNullOrEmpty(openid))
{
openid = new WXApiOpert().GetOpenIdByCode(code);
//// LogOpert.AddWeiXinMessage("2根据code到微信获取openid：" + openid);
}
}
//// LogOpert.AddWeiXinMessage("最终的openid值为：" + openid);
//// 方法封装了
model = this.GetWXUserInfoByOpenid(openid);
httpContext.Session["loginuserId"] = model.wxuserid;
httpContext.Session["loginuserName"] = model.nickname;
//// 7、返回最终查询到的用户信息对象
return(model);
}
/// <summary>
/// 根据code获取用户信息
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public MWXUserInfo GetWXUserInfoByOpenid(string openid)
{
//// 实现逻辑步骤
//// 1、根据openid 到数据库中查询用户数据信息
//// 2、如果数据库未查询到数据信息，那么根据openid到微信查询对应的用户信息
//// 3、对步骤5获取的用户数据信息入库
//// 4、返回最终查询到的用户信息对象
//// 用户信息模型
MWXUserInfo model = null;
if (!string.IsNullOrEmpty(openid))
{
//// 1、根据openid 到数据库中查询用户数据信息
model = new WXuserService().GetWXUserInfoByOpenid(openid);
if (model == null)
{
//// 2、如果数据库未查询到数据信息，那么根据openid到微信查询对应的用户信息
model = new WXApiOpert().GetUserInfo(openid);
if (model != null)
{
model.great_time = System.DateTime.Now;
model.modify_time = System.DateTime.Now;
}
//// LogOpert.AddWeiXinMessage("微信中根据openid获取到的用户信息为：" + JsonHelper.GetJson<MWXUserInfo>(model));
if (model != null)
{
//// 3、对步骤5获取的用户数据信息入库 异步入库
AddWXUser handler = new AddWXUser(new WXuserService().AddWXuser);
model.wxuserid = PublicTools.GetRandomNumberByTime();
handler.BeginInvoke(model, null, null);
//// LogOpert.AddWeiXinMessage("用户信息异步数据入库：" + JsonHelper.GetJson<MWXUserInfo>(model));
}
}
else
{
//// LogOpert.AddWeiXinMessage("数据库中根据openid获取到的用户信息为：" + JsonHelper.GetJson<MWXUserInfo>(model));
}
}
//// 4、返回最终查询到的用户信息对象
return(model);
}
/// <summary>
/// 根据Openid获取用户信息
/// </summary>
/// <param name="openid"></param>
/// <returns></returns>
public MWXUserInfo GetWXUserInfoByOpenid(string openid)
{
MWXUserInfo model = null;
//// 语句
string sql = "SELECT wxuserid,subscribe,openid,nickname,sex,sexdes,city,country,province,language,headimgurl,subscribe_time,remark,tagid_list,subscribe_scene,subscribe_scene_des,great_time,modify_time FROM wxuser where openid=?openid";
MySqlParameter[] parameterList = new MySqlParameter[1];
MySqlParameter parameter = new MySqlParameter("?openid", MySqlDbType.VarChar, 50);
parameter.Value = openid;
parameterList[0] = parameter;
using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList))
{
if (sqlDataReader.Read())
{
model = new MWXUserInfo();
model.wxuserid = sqlDataReader["wxuserid"] != DBNull.Value ? sqlDataReader["wxuserid"].ToString() : string.Empty;
model.subscribe = (sqlDataReader["subscribe"] != DBNull.Value && string.IsNullOrEmpty(sqlDataReader["subscribe"].ToString())) ? Convert.ToInt32(sqlDataReader["subscribe"].ToString()) : 0;
model.openid = sqlDataReader["openid"] != DBNull.Value ? sqlDataReader["openid"].ToString() : string.Empty;
model.nickname = sqlDataReader["nickname"] != DBNull.Value ? sqlDataReader["nickname"].ToString() : string.Empty;
model.nickname = Base64.DecodeBase64(model.nickname);
model.sex = sqlDataReader["sex"] != DBNull.Value ? sqlDataReader["sex"].ToString() : string.Empty;
model.sexdes = sqlDataReader["sexdes"] != DBNull.Value ? sqlDataReader["sexdes"].ToString() : string.Empty;
model.city = sqlDataReader["city"] != DBNull.Value ? sqlDataReader["city"].ToString() : string.Empty;
model.country = sqlDataReader["country"] != DBNull.Value ? sqlDataReader["country"].ToString() : string.Empty;
model.province = sqlDataReader["province"] != DBNull.Value ? sqlDataReader["province"].ToString() : string.Empty;
model.language = sqlDataReader["language"] != DBNull.Value ? sqlDataReader["language"].ToString() : string.Empty;
model.headimgurl = sqlDataReader["headimgurl"] != DBNull.Value ? sqlDataReader["headimgurl"].ToString() : string.Empty;
model.subscribe_time = sqlDataReader["subscribe_time"] != DBNull.Value ? sqlDataReader["subscribe_time"].ToString() : string.Empty;
model.remark = sqlDataReader["remark"] != DBNull.Value ? sqlDataReader["remark"].ToString() : string.Empty;
model.tagid_list = sqlDataReader["tagid_list"] != DBNull.Value ? sqlDataReader["tagid_list"].ToString() : string.Empty;
model.subscribe_scene = sqlDataReader["subscribe_scene"] != DBNull.Value ? sqlDataReader["subscribe_scene"].ToString() : string.Empty;
model.subscribe_scene_des = sqlDataReader["subscribe_scene_des"] != DBNull.Value ? sqlDataReader["subscribe_scene_des"].ToString() : string.Empty;
model.great_time = sqlDataReader["great_time"] != DBNull.Value ? Convert.ToDateTime(sqlDataReader["great_time"].ToString()) : DateTime.MinValue;
model.modify_time = sqlDataReader["modify_time"] != DBNull.Value ? Convert.ToDateTime(sqlDataReader["modify_time"].ToString()) : DateTime.MinValue;
}
}
return(model);
}
/// <summary>
/// 根据id获取openId更新用户信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool UpdateWXUserInfo(MWXUserInfo model)
{
string sql = "update wxuser set subscribe=?subscribe,nickname=?nickname,sex=?sex,sexdes=?sexdes,city=?city,country=?country,province=?province,language=?language,headimgurl=?headimgurl,tagid_list=?tagid_list,subscribe_scene=?subscribe_scene,subscribe_scene_des=?subscribe_scene_des,qr_scene_str=?qr_scene_str,qr_scene=?qr_scene where 1=1 ";
if (!string.IsNullOrEmpty(model.wxuserid))
{
sql = sql + " and wxuserid=?wxuserid";
}
if (!string.IsNullOrEmpty(model.openid))
{
sql = sql + " and openid=?openid";
}
List <MySqlParameter> parameterList = GetMySqlParameterListByModel(model);
return(PKMySqlHelper.ExecuteNonQuery(sql, parameterList.ToArray()) > 0);
}
/// <summary>
/// 根据code获取用户信息jeson对象
/// </summary>
/// <param name="code"></param>
public string GetWXUserInfoJesonByCode(string code)
{
LogOpert.AddWeiXinMessage("微信用户信息：" + code);
try
{
if (!string.IsNullOrEmpty(code))
{
MWXUserInfo userMdel = this.GetWXUserInfoByCode(code);
if (userMdel != null)
{
return(JsonHelper.GetJson <MWXUserInfo>(userMdel));
}
}
return(string.Empty);
}
catch (Exception ex)
{
LogOpert.AddWeiXinMessage("用户信息获取处理异常：" + ex.Message);
return(string.Empty);
}
}
/// <summary>
/// 根据id获取openId更新用户信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool UpdateWXUserInfo(MWXUserInfo model)
{
return(opertService.UpdateWXUserInfo(model));
}
/// <summary>
/// 获取参数化查询对象
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
private List <MySqlParameter> GetMySqlParameterListByModel(MWXUserInfo model)
{
List <MySqlParameter> parameterList = new List <MySqlParameter>();
MySqlParameter parameter = new MySqlParameter("?wxuserid", MySqlDbType.VarChar, 25);
parameter.Value = model.wxuserid;
parameterList.Add(parameter);
parameter = new MySqlParameter("?subscribe", MySqlDbType.Int16, 1);
parameter.Value = model.subscribe;
parameterList.Add(parameter);
parameter = new MySqlParameter("?openid", MySqlDbType.VarChar, 50);
parameter.Value = model.openid;
parameterList.Add(parameter);
parameter = new MySqlParameter("?nickname", MySqlDbType.VarChar, 1000);
parameter.Value = Base64.EncodeBase64(model.nickname);
parameterList.Add(parameter);
parameter = new MySqlParameter("?sex", MySqlDbType.VarChar, 1);
parameter.Value = model.sex;
parameterList.Add(parameter);
parameter = new MySqlParameter("?sexdes", MySqlDbType.VarChar, 10);
parameter.Value = model.sex == "1" ? "男" : (model.sex == "2" ? "女" : "未知");
parameterList.Add(parameter);
parameter = new MySqlParameter("?city", MySqlDbType.VarChar, 30);
parameter.Value = model.city;
parameterList.Add(parameter);
parameter = new MySqlParameter("?country", MySqlDbType.VarChar, 30);
parameter.Value = model.country;
parameterList.Add(parameter);
parameter = new MySqlParameter("?province", MySqlDbType.VarChar, 30);
parameter.Value = model.province;
parameterList.Add(parameter);
parameter = new MySqlParameter("?language", MySqlDbType.VarChar, 20);
parameter.Value = model.language;
parameterList.Add(parameter);
parameter = new MySqlParameter("?headimgurl", MySqlDbType.VarChar, 1000);
parameter.Value = model.headimgurl;
parameterList.Add(parameter);
parameter = new MySqlParameter("?subscribe_time", MySqlDbType.DateTime);
parameter.Value = model.subscribe_time;
parameterList.Add(parameter);
parameter = new MySqlParameter("?remark", MySqlDbType.VarChar, 1000);
parameter.Value = model.remark;
parameterList.Add(parameter);
parameter = new MySqlParameter("?tagid_list", MySqlDbType.VarChar, 200);
parameter.Value = model.tagid_list;
parameterList.Add(parameter);
parameter = new MySqlParameter("?subscribe_scene", MySqlDbType.VarChar, 50);
parameter.Value = model.subscribe_scene;
parameterList.Add(parameter);
parameter = new MySqlParameter("?subscribe_scene_des", MySqlDbType.VarChar, 50);
parameter.Value = this.GetSubscribeScenDes(model.subscribe_scene);
parameterList.Add(parameter);
parameter = new MySqlParameter("?isDelete", MySqlDbType.Int16, 1);
parameter.Value = model.isDelete;
parameterList.Add(parameter);
parameter = new MySqlParameter("?isEffective", MySqlDbType.Int16, 1);
parameter.Value = model.isEffective;
parameterList.Add(parameter);
DateTime dateTime = System.DateTime.Now;
parameter = new MySqlParameter("?great_time", MySqlDbType.DateTime);
parameter.Value = dateTime;
parameterList.Add(parameter);
parameter = new MySqlParameter("?modify_time", MySqlDbType.DateTime);
parameter.Value = dateTime;
parameterList.Add(parameter);
return(parameterList);
}
/// <summary>
/// 分页获取微信用户信息
/// </summary>
/// <param name="pagIndex">页码（第一页从1 开始）</param>
/// <param name="pagCount">每页数据条数</param>
/// <returns></returns>
public List <MWXUserInfo> GetWXUserInfoPagList(int pagIndex, int pagCount, string nickname, string subscribe)
{
// 查询条件
StringBuilder sqlWhere = new StringBuilder(" 1=1 ");
if (!string.IsNullOrEmpty(nickname))
{
sqlWhere.Append(" and nickname like CONCAT('%',?nickname,'%') ");
}
if (!string.IsNullOrEmpty(subscribe))
{
sqlWhere.Append(" and subscribe= ?subscribe ");
}
string sql = " SELECT wxuserid,subscribe,openid,nickname,sex,sexdes,city,country,province,language,headimgurl,subscribe_time,remark,tagid_list,subscribe_scene,subscribe_scene_des,qr_scene,qr_scene_str,great_time,modify_time " +
$" FROM wxuser WHERE {sqlWhere.ToString()} ORDER BY wxuserid desc limit {((pagIndex - 1) * pagCount)}, {pagCount}; ";
//string sql = " SELECT TOP " + pagCount * pagIndex + " [wxuserid],[subscribe],[openid],[nickname],[sex],[sexdes],[city],[country],[province],[language],[headimgurl],[subscribe_time],[remark],[tagid_list],[subscribe_scene],[subscribe_scene_des],[qr_scene],[qr_scene_str],[great_time],[modify_time] " +
// " FROM( SELECT ROW_NUMBER() OVER(ORDER BY great_time DESC) AS ROWID,* FROM wxuser]) AS TEMP1 WHERE ROWID> " + pagCount * (pagIndex - 1);
List <MySqlParameter> parameterList = new List <MySqlParameter>();
MySqlParameter parameter = new MySqlParameter("?nickname", MySqlDbType.Int16, 1);
parameter.Value = nickname;
parameterList.Add(parameter);
parameter = new MySqlParameter("?subscribe", MySqlDbType.Int16, 50);
parameter.Value = subscribe;
parameterList.Add(parameter);
List <MWXUserInfo> listModel = null;
using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray()))
{
if (sqlDataReader != null)
{
listModel = new List <MWXUserInfo>();
while (sqlDataReader.Read())
{
MWXUserInfo model = new MWXUserInfo();
model.wxuserid = sqlDataReader["wxuserid"] != DBNull.Value ? sqlDataReader["wxuserid"].ToString() : string.Empty;
model.subscribe = (sqlDataReader["subscribe"] != DBNull.Value && string.IsNullOrEmpty(sqlDataReader["subscribe"].ToString())) ? Convert.ToInt32(sqlDataReader["subscribe"].ToString()) : 0;
model.openid = sqlDataReader["openid"] != DBNull.Value ? sqlDataReader["openid"].ToString() : string.Empty;
model.nickname = sqlDataReader["nickname"] != DBNull.Value ? sqlDataReader["nickname"].ToString() : string.Empty;
model.nickname = Base64.DecodeBase64(model.nickname);
model.sex = sqlDataReader["sex"] != DBNull.Value ? sqlDataReader["sex"].ToString() : string.Empty;
model.sexdes = sqlDataReader["sexdes"] != DBNull.Value ? sqlDataReader["sexdes"].ToString() : string.Empty;
model.city = sqlDataReader["city"] != DBNull.Value ? sqlDataReader["city"].ToString() : string.Empty;
model.country = sqlDataReader["country"] != DBNull.Value ? sqlDataReader["country"].ToString() : string.Empty;
model.province = sqlDataReader["province"] != DBNull.Value ? sqlDataReader["province"].ToString() : string.Empty;
model.language = sqlDataReader["language"] != DBNull.Value ? sqlDataReader["language"].ToString() : string.Empty;
model.headimgurl = sqlDataReader["headimgurl"] != DBNull.Value ? sqlDataReader["headimgurl"].ToString() : string.Empty;
model.subscribe_time = sqlDataReader["subscribe_time"] != DBNull.Value ? sqlDataReader["subscribe_time"].ToString() : string.Empty;
model.remark = sqlDataReader["remark"] != DBNull.Value ? sqlDataReader["remark"].ToString() : string.Empty;
model.tagid_list = sqlDataReader["tagid_list"] != DBNull.Value ? sqlDataReader["tagid_list"].ToString() : string.Empty;
model.subscribe_scene = sqlDataReader["subscribe_scene"] != DBNull.Value ? sqlDataReader["subscribe_scene"].ToString() : string.Empty;
model.subscribe_scene_des = sqlDataReader["subscribe_scene_des"] != DBNull.Value ? sqlDataReader["subscribe_scene_des"].ToString() : string.Empty;
model.great_time = sqlDataReader["great_time"] != DBNull.Value ? Convert.ToDateTime(sqlDataReader["great_time"].ToString()) : DateTime.MinValue;
model.modify_time = sqlDataReader["modify_time"] != DBNull.Value ? Convert.ToDateTime(sqlDataReader["modify_time"].ToString()) : DateTime.MinValue;
listModel.Add(model);
}
}
}
return(listModel);
}