public tbl_userConfig getUserConfig(string openId)
{
SqlSugarClient db = SqlSugarInstance.newInstance();
tbl_userConfig config = db.Queryable <tbl_userConfig>().Where(o => o.userId == openId).First();
return(config);
}
public tbl_user getUserInfo(string openId)
{
SqlSugarClient db = SqlSugarInstance.newInstance();
tbl_user user = db.Queryable <tbl_user>().Where(o => o.openid == openId).First();
return(user);
}
/// <summary>
/// 单表分页查询
/// </summary>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">页容量</param>
/// <param name="whereExpression">条件表达式</param>
/// <param name="orderbyStr">排序字段</param>
/// <param name="whereString">where字符串</param>
/// <param name="whereObj">命令参数对应匿名对象</param>
/// <returns></returns>
public List <T> QueryByWherePage(int pageIndex, int pageSize, Expression <Func <T, bool> > whereExpression, string orderbyStr, string whereString = "1=1", object whereObj = null)
{
using (SqlSugarClient dbClient = SqlSugarInstance.GetInstance())
{
return(dbClient.Queryable <T>().Where(whereExpression).Where(whereString, whereObj).OrderBy(orderbyStr).ToPageList(pageIndex, pageSize));
}
}
/// <summary>
/// 更新实体所有的列
/// </summary>
/// <param name="model">实体对象，主键必须是第一位</param>
/// <returns></returns>
public bool Update(T model)
{
using (SqlSugarClient dbClient = SqlSugarInstance.GetInstance())
{
return(dbClient.Update(model));
}
}
public DateTime getLastRefreshDate(string openId)
{
SqlSugarClient db = SqlSugarInstance.newInstance();
DateTime lastRefresh = db.Queryable <tbl_user>().Where(o => o.openid == openId).Select(o => o.lastRefreshDate).First().GetValueOrDefault();
return(lastRefresh);
}
/// <summary>
/// 执行sql语句或存储过程
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <param name="sql">sql语句</param>
/// <param name="whereObj">命令参数对应匿名对象</param>
/// <returns></returns>
public List <TResult> QueryBySql <TResult>(string sql, object whereObj = null)
{
using (SqlSugarClient dbClient = SqlSugarInstance.GetInstance())
{
return(dbClient.SqlQuery <TResult>(sql, whereObj));
}
}
public List <tbl_user> getUserScoreCard()
{
try
{
SqlSugarClient db = SqlSugarInstance.newInstance();
//DataTable dt = db.Ado.UseStoredProcedure().GetDataTable("proc_scorecard", new { });
//List<string> scores = new List<string>();
//int i = 0;
//for (i = 0; i < 200; i++)
//{
// if (i < dt.Rows.Count)
// {
// scores.Add(dt.Rows[i][0].ToString());
// }
// else
// {
// break;
// }
//}
List <tbl_user> users = db.Queryable <tbl_user>().Where(o => o.ifRobot != 1).OrderBy(o => getRand()).ToList().GetRange(0, 20);
return(users);
}
catch (Exception e)
{
return(new List <tbl_user>());
}
}
public long getFansCount(string openId)
{
SqlSugarClient db = SqlSugarInstance.newInstance();
long count = db.Queryable <tbl_userFollowed>().Where(o => o.followedUser == openId).Count();
return(count);
}
/// <summary>
/// 分组查询
/// </summary>
/// <typeparam name="TResult">返回的新的实体数据</typeparam>
/// <param name="groupbyFiles">分组字段</param>
/// <param name="selectExpression">筛选字段</param>
/// <param name="whereExpression">条件表达式</param>
/// <param name="orderbyStr">排序字段</param>
/// <param name="whereString">where字符串</param>
/// <param name="whereObj">命令参数对应匿名对象</param>
/// <returns></returns>
public List <TResult> QueryByGroup <TResult>(string groupbyFiles, Expression <Func <T, TResult> > selectExpression, Expression <Func <T, bool> > whereExpression, string orderbyStr, string whereString = "1=1", object whereObj = null)
{
using (SqlSugarClient dbClient = SqlSugarInstance.GetInstance())
{
return(dbClient.Queryable <T>().Where(whereExpression).Where(whereString, whereObj).GroupBy(groupbyFiles).Select <T, TResult>(selectExpression).OrderBy(orderbyStr).ToList());
}
}
/// <summary>
/// 查询是否存在记录
/// </summary>
/// <param name="whereExpression">条件表达式</param>
/// <returns></returns>
public bool Any(Expression <Func <T, bool> > whereExpression)
{
using (SqlSugarClient dbClient = SqlSugarInstance.GetInstance())
{
return(dbClient.Queryable <T>().Any(whereExpression));
}
}
/// <summary>
/// 查询所有
/// </summary>
/// <returns></returns>
public List <T> QueryAll()
{
using (SqlSugarClient dbClient = SqlSugarInstance.GetInstance())
{
return(dbClient.Queryable <T>().ToList());
}
}
/// <summary>
/// 查询一个
/// </summary>
/// <param name="expression">Lambda表达式</param>
/// <returns></returns>
public T QuerySingle(Expression <Func <T, bool> > expression)
{
using (SqlSugarClient dbClient = SqlSugarInstance.GetInstance())
{
return(dbClient.Queryable <T>().Single(expression));
}
}
/// <summary>
/// 查询条数
/// </summary>
/// <param name="whereExpression">条件表达式</param>
/// <returns></returns>
public int QueryCount(Expression <Func <T, bool> > whereExpression)
{
using (SqlSugarClient dbClient = SqlSugarInstance.GetInstance())
{
return(dbClient.Queryable <T>().Where(whereExpression).Count());
}
}
public tbl_user getRobotUser()
{
SqlSugarClient db = SqlSugarInstance.newInstance();
tbl_user user = db.Queryable <tbl_user>().Where(o => o.ifRobot == 1).OrderBy(o => getRand()).First();
return(user);
}
/// <summary>
/// 查询一个
/// </summary>
/// <returns></returns>
public T QuerySingle()
{
using (SqlSugarClient dbClient = SqlSugarInstance.GetInstance())
{
return(dbClient.Queryable <T>().Single());
}
}
public List <tbl_user> getRobotUsers()
{
SqlSugarClient db = SqlSugarInstance.newInstance();
List <tbl_user> robots = db.Queryable <tbl_user>().Where(o => o.ifRobot == 1).ToList();
return(robots);
}
public List <tbl_user> getUsers()
{
SqlSugarClient db = SqlSugarInstance.newInstance();
List <tbl_user> users = db.Queryable <tbl_user>().Take(30).ToList();
return(users);
}
public tbl_user getUser(string userid)
{
SqlSugarClient db = SqlSugarInstance.newInstance();
tbl_user result = db.Queryable <tbl_user>().Where(o => o.openid == userid).First();
return(result);
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="entity">实体对象</param>
/// <param name="isIdentity">是否包含主键</param>
/// <returns></returns>
public object Insert(T entity, bool isIdentity = true)
{
using (SqlSugarClient dbClient = SqlSugarInstance.GetInstance())
{
return(dbClient.Insert <T>(entity, isIdentity));
}
}
/// <summary>
/// 查询指定个数的数据
/// </summary>
/// <param name="takeNum">指定个数</param>
/// <param name="whereExpression">条件表达式</param>
/// <param name="orderbyStr">排序字段</param>
/// <returns></returns>
public List <T> QueryTakeIndex(int takeNum, Expression <Func <T, bool> > whereExpression, string orderbyStr)
{
using (SqlSugarClient dbClient = SqlSugarInstance.GetInstance())
{
return(dbClient.Queryable <T>().Where(whereExpression).OrderBy(orderbyStr).Take(takeNum).ToList());
}
}
public void updateUserStatus(string userId)
{
SqlSugarClient db = SqlSugarInstance.newInstance();
tbl_user curUser = db.Queryable <tbl_user>().Where(o => o.openid == userId).First();
curUser.userStatus = 1;//disable the user
db.Updateable <tbl_user>(curUser);
}
public void updateLastRefreshFans(string openId)
{
SqlSugarClient db = SqlSugarInstance.newInstance();
db.Updateable <tbl_user>().UpdateColumns(o => new tbl_user {
lastRefreshFans = DateTime.Now
}).Where(o => o.openid == openId).ExecuteCommand();
}
public tbl_user getCoverPageUser()
{
SqlSugarClient db = SqlSugarInstance.newInstance();
string userId = db.Queryable <tbl_coverPage>().Where(o => o.startDate <= DateTime.Now).OrderBy(o => o.startDate, OrderByType.Desc).First().userId;
tbl_user user = db.Queryable <tbl_user>().Where(o => o.openid == userId).First();
return(user);
}
public void delFollow(string openId, string userId)
{
SqlSugarClient db = SqlSugarInstance.newInstance();
db.Deleteable <tbl_userFollowed>().Where(new tbl_userFollowed {
userId = openId, followedUser = userId
}).ExecuteCommand();
}
public void updateSelfIntro(string openId, string selfIntro)
{
SqlSugarClient db = SqlSugarInstance.newInstance();
tbl_user user = db.Queryable <tbl_user>().Where(o => o.openid == openId).First();
user.selfIntro = selfIntro;
db.Updateable <tbl_user>(user).Where(o => o.openid == openId).ExecuteCommand();
}
public void updateNickName(string openid, string nickName)
{
SqlSugarClient db = SqlSugarInstance.newInstance();
tbl_user cur = db.Queryable <tbl_user>().Where(o => o.openid == openid).First();
cur.nickName = nickName;
db.Updateable(cur).Where(o => o.openid == openid).UpdateColumns(arg => new { arg.nickName }).ExecuteCommand();
}
public long newFansCount(string openId)
{
SqlSugarClient db = SqlSugarInstance.newInstance();
tbl_user user = db.Queryable <tbl_user>().Where(o => o.openid == openId).First();
long count = db.Queryable <tbl_userFollowed>().
Where(o => o.followedUser == openId && o.followedTime >= user.lastRefreshFans.GetValueOrDefault()).Count();
return(count);
}
public long getLovedCount(string openId)
{
SqlSugarClient db = SqlSugarInstance.newInstance();
string[] tpList = db.Queryable <tbl_posts>().Where(o => o.postsMaker == openId).Select(o => o.postsID).ToList().ToArray();
long postsLoved = db.Queryable <tbl_userloved>().Where(o => SqlFunc.ContainsArray <string>(tpList, o.postsID)).Count();
return(postsLoved);
}
public void userTransPage(string openId, string pageName)
{
SqlSugarClient db = SqlSugarInstance.newInstance();
tbl_transpage transpage = new tbl_transpage();
transpage.openId = openId;
transpage.pageName = pageName;
transpage.transId = Guid.NewGuid().ToString();
transpage.transTime = DateTime.Now;
db.Insertable <tbl_transpage>(transpage).ExecuteCommand();
}
public void followUserByUserId(string openId, string userId)
{
SqlSugarClient db = SqlSugarInstance.newInstance();
db.Insertable <tbl_userFollowed>(new tbl_userFollowed
{
followId = Guid.NewGuid().ToString(),
userId = userId,
followedUser = openId,
followedTime = DateTime.Now
}).ExecuteCommand();
}