/// <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);
}
