/// <summary> /// 根据 isShow 查询文章分类 按Sort 正排序,时间倒序 /// </summary> /// <param name="isShow"></param> /// <returns></returns> public static List <ArticleTypeInfo> GetArticleTypesByIsShow(bool isShow) { const string sql = @"SELECT [Id],[Name],[PId],[Sort],[PinYin],[IsHomeMenu],[CreateTime],[Ico],[IsShow] FROM [dbo].[ArticleType] WHERE IsShow=@IsShow ORDER BY Sort ASC,CreateTime DESC"; var par = new DynamicParameters(); par.Add("@IsShow", isShow, DbType.Boolean); var result = DapWrapper.InnerQuerySql <ArticleTypeInfo>(DbConfig.ArticleManagerConnString, sql, par); return(result); }
/// <summary> /// 更新用户禁用状态 /// </summary> /// <param name="id">用户id</param> /// <param name="disable">是否禁用</param> /// <param name="disableReason">原因</param> /// <returns></returns> public static bool UpdateDisable(int id, bool disable, string disableReason = null) { const string sql = "UPDATE [User] SET Disable=@Disable,DisableReason=@DisableReason WHERE Id=@Id"; var par = new DynamicParameters(); par.Add("@Id", id, DbType.Int32); par.Add("@Disable", disable, DbType.Boolean); par.Add("@DisableReason", disableReason, DbType.String); return(DapWrapper.InnerExecuteSql(DbConfig.ArticleManagerConnString, sql, par) > 0); }
/// <summary> /// 根据ids删除EmailTemplate多条记录 /// </summary> /// <param name="ids"></param> /// <returns></returns> public static Int32 DeleteEmailTemplates(int[] ids) { if (ids.Length == 0) { return(0); } string sql = "DELETE [EmailTemplate] WHERE Id IN (" + string.Join(",", ids) + ")"; return(DapWrapper.InnerExecuteText(DbConfig.ArticleManagerConnString, sql)); }
/// <summary> /// 获取用户最后发帖时间 /// </summary> /// <param name="userId"></param> /// <returns></returns> public static DateTime GetLastPostTime(int userId) { string sql = @"SELECT MAX(CreateTime) FROM [dbo].[Article] WHERE UserId=@UserId"; var par = new DynamicParameters(); par.Add("@UserId", userId, DbType.Int32); return(DapWrapper.InnerQueryScalarSql <DateTime>(DbConfig.ArticleManagerConnString, sql, par)); }
/// <summary> /// 检查是否已经点赞 /// </summary> /// <param name="userId"></param> /// <param name="mId"></param> /// <param name="cId"></param> /// <returns></returns> public static long GetDianZanLogId(int userId, long mId, long cId) { string sql = "select [Id] FROM [DianZanLog] WHERE UserId=@UserId AND MId=@MId AND CId=@CId"; var par = new DynamicParameters(); par.Add("@UserId", userId, DbType.Int32); par.Add("@MId", mId, DbType.Int64); par.Add("@CId", cId, DbType.Int64); return(DapWrapper.InnerQuerySql <long>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault()); }
/// <summary> /// 根据ids删除Notify多条记录 /// </summary> /// <param name="ids"></param> /// <returns></returns> public static Int32 DeleteNotifys(long[] ids) { if (ids.Length == 0) { return(0); } string sql = "DELETE [Notify] WHERE Id IN (" + string.Join(",", ids) + ")"; return(DapWrapper.InnerExecuteText(DbConfig.ArticleManagerConnString, sql)); }
/// <summary> /// 按照用户id查询记录数 /// </summary> /// <param name="userId">分类</param> /// <param name="state">-1所有 0待审核 1 已通过 2未通过..</param> /// <returns></returns> public static async Task <int> SearchArticlesCountByUserId(int userId, int state) { var par = new DynamicParameters(); par.Add("@UserId", userId, DbType.Int32); par.Add("@State", state, DbType.Int32); var result = await DapWrapper.InnerQueryScalarProcAsync <int>(DbConfig.ArticleManagerConnString, "proc_GetArticleCountByUserId", par); return(result); }
/// <summary> /// 修改 Status 为无效 /// </summary> /// <param name="userId"></param> /// <returns></returns> public static bool UpdateStatusToFalseByUserId(int userId) { string sql = @"UPDATE [UserActivateToken] SET Status=0 WHERE UserId=@UserId AND Status=1"; var par = new DynamicParameters(); par.Add("@UserId", userId, DbType.Int32); return(DapWrapper.InnerExecuteSql(DbConfig.ArticleManagerConnString, sql, par) > 0); }
/// <summary> /// 修改评论点赞数 /// </summary> /// <param name="id"></param> /// <param name="num">+-1</param> /// <returns></returns> public static bool UpdateDianZanNum(long id, int num) { string sql = @"UPDATE [Comment] SET DianZanNum=DianZanNum+@DianZanNum WHERE Id=@Id"; var par = new DynamicParameters(); par.Add("@Id", id, DbType.Int64); par.Add("@DianZanNum", num, DbType.Int32); return(DapWrapper.InnerExecuteSql(DbConfig.ArticleManagerConnString, sql, par) > 0); }
/// <summary> ///根据用户多个id获取多个用户信息 /// </summary> /// <param name="ids"></param> /// <returns></returns> public static List <UserVModel> GetUsersByIds(int[] ids) { if (ids == null || ids.Length == 0) { return(new List <UserVModel>()); } var par = new DynamicParameters(); par.Add("@UserIds", string.Join(",", ids), DbType.String); return(DapWrapper.InnerQueryProc <UserVModel>(DbConfig.ArticleManagerConnString, "proc_GetUsersByIds", par)); }
/// <summary> /// 修改UserOtherInfo /// </summary> /// <param name="userother"></param> /// <returns></returns> public static int UpdateUserOther(UserOtherInfo userother) { string sql = @"UPDATE [UserOther] SET PersonalityIntroduce=@PersonalityIntroduce WHERE UserId=@UserId"; var par = new DynamicParameters(); par.Add("@UserId", userother.UserId, DbType.Int32); par.Add("@PersonalityIntroduce", userother.PersonalityIntroduce, DbType.String); return(DapWrapper.InnerExecuteSql(DbConfig.ArticleManagerConnString, sql, par)); }
/// <summary> /// 近期评论数排行榜 /// </summary> /// <param name="days">天数</param> /// <param name="pageSize">页面大小</param> /// <returns></returns> public static List <ArticleVModel> ArticlesCommentNumHotTop(int days, int pageSize) { string sql = string.Format(@"SELECT TOP {0} [Id] ,[Title] ,[CommentNum] FROM [dbo].[Article] {1} order by CommentNum desc", pageSize, days >= 0 ? "WHERE DATEDIFF(DAY,CreateTime,GETDATE())<=" + days : ""); return(DapWrapper.InnerQuerySql <ArticleVModel>(DbConfig.ArticleManagerConnString, sql)); }
/// <summary> /// 修改 Status /// </summary> /// <param name="id"></param> /// <param name="status"></param> /// <returns></returns> public static bool UpdateStatus(long id, bool status) { string sql = @"UPDATE [UserActivateToken] SET Status=@Status WHERE Id=@Id"; var par = new DynamicParameters(); par.Add("@Id", id, DbType.Int32); par.Add("@Status", status, DbType.Boolean); return(DapWrapper.InnerExecuteSql(DbConfig.ArticleManagerConnString, sql, par) > 0); }
/// <summary> /// 获取UserActivateToken id /// </summary> /// <param name="userId"></param> /// <param name="email"></param> /// <param name="token"></param> /// <param name="expireTime"></param> /// <returns></returns> public static long GetUserActivateTokenId(int userId, string email, string token, int expireTime) { string sql = @"SELECT Id FROM [UserActivateToken] WHERE UserId=@UserId AND Email=@Email AND Token=@Token AND Status=1 AND DATEDIFF(MINUTE,CreateTime,GETDATE())<=@Time"; var par = new DynamicParameters(); par.Add("@UserId", userId, DbType.Int32); par.Add("@Email", email, DbType.String); par.Add("@Token", token, DbType.String); par.Add("@Time", expireTime, DbType.Int32); return(DapWrapper.InnerQueryScalarSql <long>(DbConfig.ArticleManagerConnString, sql, par)); }
/// <summary> /// 更新取消状态 /// </summary> /// <param name="id"></param> /// <param name="isCancel"></param> /// <returns></returns> public static bool UpdateIsCancel(long id, bool isCancel) { string sql = @"UPDATE [DianZanLog] SET IsCancel=@IsCancel, CancelTime=GETDATE() WHERE Id=@Id"; var par = new DynamicParameters(); par.Add("@Id", id, DbType.Int64); par.Add("@IsCancel", isCancel, DbType.Boolean); return(DapWrapper.InnerExecuteSql(DbConfig.ArticleManagerConnString, sql, par) > 0); }
/// <summary> /// 修改ArticleInfo /// </summary> /// <param name="article"></param> /// <returns></returns> public static int UpdateArticle(ArticleInfo article) { string sql = @"UPDATE [Article] SET Title=@Title, Content=@Content, Type=@Type, CommentNum=@CommentNum, Dot=@Dot, CreateTime=@CreateTime, UserId=@UserId, ImgNum=@ImgNum, AttachmentNum=@AttachmentNum, LastEditUserId=@LastEditUserId, LastEditTime=@LastEditTime, Guid=@Guid, IsDelete=@IsDelete, State=@State, RefuseReason=@RefuseReason, BType=@BType, DianZanNum=@DianZanNum, Score=@Score, IsStick=@IsStick, IsJiaJing=@IsJiaJing, IsCloseComment=@IsCloseComment, CloseCommentReason=@CloseCommentReason WHERE Id=@Id"; var par = new DynamicParameters(); par.Add("@Id", article.Id, DbType.Int64); par.Add("@Title", article.Title, DbType.String); par.Add("@Content", article.Content, DbType.AnsiString); par.Add("@Type", article.Type, DbType.Int32); par.Add("@CommentNum", article.CommentNum, DbType.Int32); par.Add("@Dot", article.Dot, DbType.Int32); par.Add("@CreateTime", article.CreateTime, DbType.DateTime); par.Add("@UserId", article.UserId, DbType.Int32); par.Add("@ImgNum", article.ImgNum, DbType.Int32); par.Add("@AttachmentNum", article.AttachmentNum, DbType.Int32); par.Add("@LastEditUserId", article.LastEditUserId, DbType.Int32); par.Add("@LastEditTime", article.LastEditTime, DbType.DateTime); par.Add("@Guid", article.Guid, DbType.Guid); par.Add("@IsDelete", article.IsDelete, DbType.Boolean); par.Add("@State", article.State, DbType.Int16); par.Add("@RefuseReason", article.RefuseReason, DbType.String); par.Add("@BType", article.BType, DbType.Int16); par.Add("@DianZanNum", article.DianZanNum, DbType.Int32); par.Add("@Score", article.Score, DbType.Int32); par.Add("@IsStick", article.IsStick, DbType.Boolean); par.Add("@IsJiaJing", article.IsJiaJing, DbType.Boolean); par.Add("@IsCloseComment", article.IsCloseComment, DbType.Boolean); par.Add("@CloseCommentReason", article.CloseCommentReason, DbType.String); return(DapWrapper.InnerExecuteSql(DbConfig.ArticleManagerConnString, sql, par)); }
/// <summary> /// 根据用户Id获取Article分页列表(自定义存储过程) /// </summary> /// <param name="userId"></param> /// <param name="state">-1所有 0待审核 1 已通过 2未通过..</param> /// <param name="pageIndex">页码</param> /// <param name="pageSize">每页显示条数</param> /// <returns>Article列表</returns> public static async Task <IEnumerable <ArticleVModel> > SearchArticlesByUserId(int userId, int state, int pageIndex, int pageSize) { var par = new DynamicParameters(); par.Add("@PageIndex", pageIndex, DbType.Int32); par.Add("@PageSize", pageSize, DbType.Int32); par.Add("@UserId", userId, DbType.Int32); par.Add("@State", state, DbType.Int32); par.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output); var result = await DapWrapper.InnerQueryProcAsync <ArticleVModel>(DbConfig.ArticleManagerConnString, "proc_GetArticlePageListByUserId", par); return(result); }
/// <summary> /// 修改UserPositionInfo /// </summary> /// <param name="userposition"></param> /// <returns></returns> public static int UpdateUserPosition(UserPositionInfo userposition) { string sql = @"UPDATE [UserPosition] SET Code=@Code, Type=@Type WHERE UserId=@UserId"; var par = new DynamicParameters(); par.Add("@UserId", userposition.UserId, DbType.Int32); par.Add("@Code", userposition.Code, DbType.Int32); par.Add("@Type", userposition.Type, DbType.Int16); return(DapWrapper.InnerExecuteSql(DbConfig.ArticleManagerConnString, sql, par)); }
/// <summary> /// 修改RoleModulInfo /// </summary> /// <param name="rolemodul"></param> /// <returns></returns> public static int UpdateRoleModul(RoleModulInfo rolemodul) { string sql = @"UPDATE [Role_Modul] SET MId=@MId, RId=@RId WHERE Id=@Id"; var par = new DynamicParameters(); par.Add("@Id", rolemodul.Id, DbType.Int32); par.Add("@MId", rolemodul.MId, DbType.Int32); par.Add("@RId", rolemodul.RId, DbType.Int32); return(DapWrapper.InnerExecuteSql(DbConfig.ArticleManagerConnString, sql, par)); }
/// <summary> /// 根据父级生成排序值(排序值只会越来越大,越大的越靠前) /// </summary> /// <param name="pid">父级id</param> /// <param name="id">编辑时可排除自身所占位置</param> /// <returns></returns> public static int GetOrderNumber(int pid, int?id) { string sql = "select ISNULL(MAX(OrderId),0) from [Modul] WHERE PId=@PId"; var par = new DynamicParameters(); par.Add("@PId", pid, DbType.Int32); if (id.HasValue) { sql = sql + " AND Id!=@Id "; par.Add("@Id", id.Value, DbType.Int32); } return(DapWrapper.InnerQueryScalarSql <int>(DbConfig.ArticleManagerConnString, sql, par) + 1); }
/// <summary> /// 更新文章置顶状态 /// </summary> /// <param name="id"></param> /// <param name="userId"></param> /// <param name="isStick"></param> /// <returns></returns> public static bool UpdateIsStick(long id, int userId, bool isStick) { string sql = @"UPDATE [Article] SET IsStick=@IsStick, LastEditUserId=@LastEditUserId, LastEditTime=GETDATE() WHERE Id=@Id"; var par = new DynamicParameters(); par.Add("@Id", id, DbType.Int64); par.Add("@IsStick", isStick, DbType.Boolean); par.Add("@LastEditUserId", userId, DbType.Int32); return(DapWrapper.InnerExecuteSql(DbConfig.ArticleManagerConnString, sql, par) > 0); }
/// <summary> /// 添加RoleModulInfo /// </summary> /// <param name="rolemodul"></param> /// <returns></returns> public static int AddRoleModul(RoleModulInfo rolemodul) { string sql = @"INSERT INTO [Role_Modul] ([MId],[RId]) VALUES (@MId,@RId) SELECT SCOPE_IDENTITY() " ; var par = new DynamicParameters(); par.Add("@MId", rolemodul.MId, DbType.Int32); par.Add("@RId", rolemodul.RId, DbType.Int32); return(DapWrapper.InnerQueryScalarSql <int>(DbConfig.ArticleManagerConnString, sql, par)); }
/// <summary> /// 添加UserOtherInfo /// </summary> /// <param name="userother"></param> /// <returns></returns> public static int AddUserOther(UserOtherInfo userother) { string sql = @"INSERT INTO [UserOther] ([UserId],[PersonalityIntroduce]) VALUES (@UserId,@PersonalityIntroduce) SELECT SCOPE_IDENTITY() " ; var par = new DynamicParameters(); par.Add("@UserId", userother.UserId, DbType.Int32); par.Add("@PersonalityIntroduce", userother.PersonalityIntroduce, DbType.String); return(DapWrapper.InnerQueryScalarSql <int>(DbConfig.ArticleManagerConnString, sql, par)); }
/// <summary> /// 获取文章热字段DOt的值 /// </summary> /// <param name="ids"></param> /// <returns></returns> public async static Task <IEnumerable <ArticleHotFieldVModel> > GetArticleHotFieldDots(long[] ids) { if (ids == null || ids.Length == 0) { return(new List <ArticleHotFieldVModel>()); } string idstr = string.Join(",", ids); string sql = string.Format(@"SELECT [Id] ,[AId] ,[Dot] FROM [ArticleManager].[dbo].[Article_HotField] WHERE AId IN({0})", idstr); return(await DapWrapper.InnerQuerySqlAsync <ArticleHotFieldVModel>(DbConfig.ArticleManagerConnString, sql)); }
/// <summary> /// Name检测 /// </summary> /// <param name="name"></param> /// <param name="id">需排除的自身userid</param> /// <returns>true(含有)/false(不含有)</returns> public static bool CheckName(string name, int?id) { string sql = "select count(Id) from [User] where Name=@Name"; var par = new DynamicParameters(); par.Add("@Name", name, DbType.String); if (id.HasValue) { sql += " AND Id!=@Id"; par.Add("@Id", id.Value, DbType.Int32); } return(DapWrapper.InnerQueryScalarSql <int>(DbConfig.ArticleManagerConnString, sql, par) > 0); }
/// <summary> /// 添加UserActivateTokenInfo /// </summary> /// <param name="useractivatetoken"></param> /// <returns></returns> public static long AddUserActivateToken(UserActivateTokenInfo useractivatetoken) { string sql = @"INSERT INTO [UserActivateToken] ([UserId],[Email],[Token]) VALUES (@UserId,@Email,@Token) SELECT SCOPE_IDENTITY() " ; var par = new DynamicParameters(); par.Add("@UserId", useractivatetoken.UserId, DbType.Int32); par.Add("@Email", useractivatetoken.Email, DbType.AnsiString); par.Add("@Token", useractivatetoken.Token, DbType.AnsiString); return(DapWrapper.InnerQueryScalarSql <long>(DbConfig.ArticleManagerConnString, sql, par)); }
/// <summary> /// 更新文章是否关闭评论状态 /// </summary> /// <param name="id"></param> /// <param name="userId"></param> /// <param name="isCloseComment"></param> /// <param name="reason"></param> /// <returns></returns> public static bool UpdateIsCloseComment(long id, int userId, bool isCloseComment, string reason = null) { string sql = string.Format(@"UPDATE [Article] SET IsCloseComment=@IsCloseComment, LastEditUserId=@LastEditUserId, CloseCommentReason=@CloseCommentReason, LastEditTime=GETDATE() WHERE Id ={0}", id); var par = new DynamicParameters(); par.Add("@IsCloseComment", isCloseComment, DbType.Boolean); par.Add("@CloseCommentReason", reason, DbType.String); par.Add("@LastEditUserId", userId, DbType.Int32); return(DapWrapper.InnerExecuteSql(DbConfig.ArticleManagerConnString, sql, par) > 0); }
/// <summary> /// 获取AdminLog分页列表(自定义存储过程) /// </summary> /// <param name="pageIndex">页码</param> /// <param name="pageSize">每页显示条数</param> /// <param name="beginTime">开始时间</param> /// <param name="endTime">结束时间</param> /// <param name="recordCount">总记录数</param> /// <returns>AdminLog列表</returns> public static List <AdminLogInfo> GetAdminLogPageList(int pageIndex, int pageSize, DateTime?beginTime, DateTime?endTime, out int recordCount) { recordCount = 0; var par = new DynamicParameters(); par.Add("@PageIndex", pageIndex, DbType.Int32); par.Add("@PageSize", pageSize, DbType.Int32); par.Add("@BeginTime", beginTime, DbType.DateTime); par.Add("@EndTime", !endTime.HasValue ? endTime : endTime.Value.AddDays(1).AddMilliseconds(-1), DbType.DateTime); par.Add("@TotalCount", recordCount, DbType.Int32, ParameterDirection.Output); var result = DapWrapper.InnerQueryProc <AdminLogInfo>(DbConfig.ArticleManagerConnString, "proc_GetAdminLogPageList", par); recordCount = par.Get <int>("@TotalCount"); return(result); }
/// <summary> /// 添加UserPositionInfo /// </summary> /// <param name="userposition"></param> /// <returns></returns> public static int AddUserPosition(UserPositionInfo userposition) { string sql = @"INSERT INTO [UserPosition] ([UserId],[Code],[Type]) VALUES (@UserId,@Code,@Type) SELECT SCOPE_IDENTITY() " ; var par = new DynamicParameters(); par.Add("@UserId", userposition.UserId, DbType.Int32); par.Add("@Code", userposition.Code, DbType.Int32); par.Add("@Type", userposition.Type, DbType.Int16); return(DapWrapper.InnerQueryScalarSql <int>(DbConfig.ArticleManagerConnString, sql, par)); }
/// <summary> /// 获取Article分页列表(自定义存储过程) /// </summary> /// <param name="pageIndex">页码</param> /// <param name="pageSize">每页显示条数</param> /// <param name="bType"></param> /// <param name="isJingHua"></param> /// <param name="recordCount">总记录数</param> /// <param name="type"></param> /// <returns>Article列表</returns> public static List <ArticleVModel> GetArticlePageList(int pageIndex, int pageSize, int type, int bType, int isJingHua, out int recordCount) { recordCount = 0; var par = new DynamicParameters(); par.Add("@PageIndex", pageIndex, DbType.Int32); par.Add("@PageSize", pageSize, DbType.Int32); par.Add("@Type", type, DbType.Int32); par.Add("@BType", bType, DbType.Int32); par.Add("@IsJiaJing", isJingHua, DbType.Int32); par.Add("@TotalCount", recordCount, DbType.Int32, ParameterDirection.Output); var result = DapWrapper.InnerQueryProc <ArticleVModel>(DbConfig.ArticleManagerConnString, "proc_GetArticlePageList", par); recordCount = par.Get <int>("@TotalCount"); return(result); }