/// <summary>
/// 根据id获取ArticleVModel
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static ArticleVModel GetArticleVModel(long id)
{
string sql = @"select [Id],
[Title],
[Content],
[Type],
[CommentNum],
[Dot],
[CreateTime],
[UserId],
[ImgNum],
[AttachmentNum],
[LastEditUserId],
[LastEditTime],
[Guid],
[IsStick],
[IsJiaJing],
dbo.GetTypeNameByTypeId(Type) TypeName,
dbo.GetNickNameByUserId(UserId) UserName,
dbo.GetNickNameByUserId([LastEditUserId]) AS LastEditUserName,
[State],
[RefuseReason] ,
[IsCloseComment],
[CloseCommentReason],
[BType],
[DianZanNum]
FROM [Article] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int32);
return(DapWrapper.InnerQuerySql <ArticleVModel>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据id获取UserInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static UserInfo GetUserInfo(int id)
{
string sql = "select [Id],[Name],[NickName],[Password],[RoleId],[OnLineTime],[ActionTime],[CreateTime],[Avatar],[Sex],[Email],[EmailStatus],[Score],[ArticleNum],[CommentNum],[Disable],[DisableReason],[QQ] FROM [User] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int32);
return(DapWrapper.InnerQuerySql <UserInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据id获取TagInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static TagInfo GetTagInfo(long id)
{
string sql = "select [Id],[Tag],[AId],[State],[Direction],[CreateTime],[UserId] FROM [Tag] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int64);
return(DapWrapper.InnerQuerySql <TagInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据id获取EmailTemplateInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static EmailTemplateInfo GetEmailTemplateInfo(int id)
{
string sql = "select [Id],[Title],[Template],[ValueIdentifier],[Explanation],[IsSystem],[IsHtml],[CreateTime],[UserId],[LastEditUserId],[LastEditTime] FROM [EmailTemplate] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int32);
return(DapWrapper.InnerQuerySql <EmailTemplateInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据id获取RoleModulInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static RoleModulInfo GetRoleModulInfo(int id)
{
string sql = "select [Id],[MId],[RId] FROM [Role_Modul] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int32);
return(DapWrapper.InnerQuerySql <RoleModulInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据角色id获取已有模块id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static int[] GetMIds(int id)
{
string sql = "select [MId] FROM [Role_Modul] WHERE RId=@RId";
var par = new DynamicParameters();
par.Add("@RId", id, DbType.Int32);
return(DapWrapper.InnerQuerySql <int>(DbConfig.ArticleManagerConnString, sql, par).ToArray());
}
/// <summary>
/// 根据id获取DianZanLogInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static DianZanLogInfo GetDianZanLogInfo(long id)
{
string sql = "select [Id],[MId],[CId],[UserId],[Ip],[IsCancel],[CancelTime],[Type],[CreateTime] FROM [DianZanLog] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int64);
return(DapWrapper.InnerQuerySql <DianZanLogInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据id获取SendMailLogInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static SendMailLogInfo GetSendMailLogInfo(long id)
{
string sql = "select [Id],[UserId],[SendUserId],[TemplateId],[ToEmail],[FromEmail],[Status],[Title],[Body],[Ip],[IsSystem],[CreateTime] FROM [SendMailLog] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int64);
return(DapWrapper.InnerQuerySql <SendMailLogInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据id获取AdminLogInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static AdminLogInfo GetAdminLogInfo(int id)
{
string sql = "select [Id],[UserId],[Controllers],[Action],[Parameter],[ActionId],[Ip],[Url],[InTime],[Method],[IsAjax],[UserAgent],[ControllersDsc],[ActionDsc] FROM [AdminLog] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int32);
return(DapWrapper.InnerQuerySql <AdminLogInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据Id获取用户信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static UserInfo GetUserInfo(int id)
{
string sql = "select " + Fileds + " FROM [dbo].[User] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int32);
return(DapWrapper.InnerQuerySql <UserInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据email获取用户信息
/// </summary>
/// <param name="email"></param>
/// <returns></returns>
public static UserInfo GetUserInfo(string email)
{
string sql = "select " + Fileds + " FROM [dbo].[User] WHERE Email=@Email";
var par = new DynamicParameters();
par.Add("@Email", email, DbType.String);
return(DapWrapper.InnerQuerySql <UserInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据id获取UserScoreLogInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static UserScoreLogInfo GetUserScoreLogInfo(long id)
{
string sql = "select [Id],[UserId],[Score],[CreateTime],[Describe],[OldScore],[NewScore] FROM [UserScoreLog] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int64);
return(DapWrapper.InnerQuerySql <UserScoreLogInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据id获取NotifyInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static NotifyInfo GetNotifyInfo(long id)
{
string sql = "select [Id],[FromUserId],[ToUserId],[CreateTime],[IsDelete],[IsRead],[IsSystem],[Content],[Title] FROM [Notify] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int64);
return(DapWrapper.InnerQuerySql <NotifyInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据id获取RoleInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static RoleInfo GetRoleInfo(int id)
{
string sql = "select [Id],[RName],[CreateTime],[IsDel],[Description],[IsSuper],[ArticleNeedVerified],[CommentNeedVerified] FROM [Role] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int32);
return(DapWrapper.InnerQuerySql <RoleInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据userid获取UserPositionInfo
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public static List <UserPositionInfo> GetUserPositionInfos(int userid)
{
string sql = "select [UserId],[Code],[Type] FROM [UserPosition] WHERE UserId=@UserId";
var par = new DynamicParameters();
par.Add("@UserId", userid, DbType.Int32);
return(DapWrapper.InnerQuerySql <UserPositionInfo>(DbConfig.ArticleManagerConnString, sql, par));
}
/// <summary>
/// 根据id获取AttachmentInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static AttachmentInfo GetAttachmentInfo(long id)
{
string sql = "select [Id],[FileName],[FileTitle],[Description],[Type],[Width],[Height],[FileSize],[IsShow],[AId],[Score],[State],[UserId],[InTime],[BType],[LocalPath],[VirtualPath],[Guid] FROM [Attachment] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int64);
return(DapWrapper.InnerQuerySql <AttachmentInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据id获取ModulInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static ModulInfo GetModulInfo(int id)
{
string sql = "select [Id],[ModulName],[Controller],[Action],[Description],[CreateTime],[PId],[OrderId],[IsShow],[Priority],[IsDisplay],[Ico] FROM [Modul] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int32);
return(DapWrapper.InnerQuerySql <ModulInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据id获取CommentInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static CommentInfo GetCommentInfo(long id)
{
string sql = "select [Id],[AId],[UserId],[CreateTime],[Content],[IP],[UserAgent],[State],[RefuseReason],[IsDelete],[LastEditUserId],[LastEditTime],[IsStick],[DianZanNum] FROM [Comment] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int64);
return(DapWrapper.InnerQuerySql <CommentInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据id获取ArticleInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static ArticleInfo GetArticleInfo(long id)
{
string sql = "select [Id],[Title],[Content],[Type],[CommentNum],[Dot],[CreateTime],[UserId],[ImgNum],[AttachmentNum],[LastEditUserId],[LastEditTime],[Guid],[IsDelete],[State],[RefuseReason],[BType],[DianZanNum],[Score],[IsStick],[IsJiaJing],[IsCloseComment],[CloseCommentReason] FROM [Article] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int32);
return(DapWrapper.InnerQuerySql <ArticleInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据userid获取UserOtherInfo
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public static UserOtherInfo GetUserOtherInfo(int userid)
{
string sql = "select [UserId],[PersonalityIntroduce] FROM [UserOther] WHERE UserId=@UserId";
var par = new DynamicParameters();
par.Add("@UserId", userid, DbType.Int32);
return(DapWrapper.InnerQuerySql <UserOtherInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据guid获取AttachmentInfo 集合
/// </summary>
/// <param name="guid"></param>
/// <returns></returns>
public static List <AttachmentInfo> GetAttachmentByGuid(Guid guid)
{
const string sql = "select [Id],[FileName],[FileTitle],[Description],[Type],[Width],[Height],[FileSize],[IsShow],[AId],[Score],[State],[UserId],[Guid],[InTime],[BType],[LocalPath],[VirtualPath] FROM [Attachment] WHERE Guid=@Guid";
var par = new DynamicParameters();
par.Add("@Guid", guid, DbType.Guid);
return(DapWrapper.InnerQuerySql <AttachmentInfo>(DbConfig.ArticleManagerConnString, sql, par));
}
/// <summary>
/// 根据id获取UserAccessLogInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static UserAccessLogInfo GetUserAccessLogInfo(long id)
{
string sql = "select [Id],[Url],[Referer],[UserAgent],[UserId],[Ip],[InsertTime],[Other],[Type] FROM [UserAccessLog] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int64);
return(DapWrapper.InnerQuerySql <UserAccessLogInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据id获取UserActivateTokenInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static UserActivateTokenInfo GetUserActivateTokenInfo(long id)
{
string sql = "select [Id],[UserId],[Email],[Token],[Status],[CreateTime] FROM [UserActivateToken] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int64);
return(DapWrapper.InnerQuerySql <UserActivateTokenInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 根据id获取ArticleTypeInfo
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static ArticleTypeInfo GetArticleTypeInfo(int id)
{
string sql = "select [Id],[Name],[PId],[Sort],[PinYin],[IsHomeMenu],[CreateTime],[Ico],[IsShow] FROM [ArticleType] WHERE Id=@Id";
var par = new DynamicParameters();
par.Add("@Id", id, DbType.Int32);
return(DapWrapper.InnerQuerySql <ArticleTypeInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 获取所有
/// </summary>
/// <returns></returns>
public static List <ArticleVModel> GetAllData()
{
string sql = @"select [Id],[Title],[Content],[Type],[CommentNum],[Dot],[CreateTime],[UserId],[ImgNum],[AttachmentNum],[LastEditUserId],[LastEditTime],[Guid],[IsDelete],[State],[RefuseReason],[BType],[DianZanNum],[Score],[IsStick],[IsJiaJing],[IsCloseComment],[CloseCommentReason],
dbo.GetTypeNameByTypeId([Article].Type) AS TypeName,
dbo.GetNickNameByUserId([UserId]) AS UserName,
dbo.GetNickNameByUserId([LastEditUserId]) AS LastEditUserName FROM [Article]";
return(DapWrapper.InnerQuerySql <ArticleVModel>(DbConfig.ArticleManagerConnString, sql));
}
/// <summary>
/// 根据Name和Password获取用户信息
/// </summary>
/// <param name="name"></param>
/// <param name="password"></param>
/// <returns></returns>
public static UserInfo GetUserInfo(string name, string password)
{
string sql = "select " + Fileds + " FROM [dbo].[User] WHERE Name=@Name AND Password=@Password ";
var par = new DynamicParameters();
par.Add("@Name", name, DbType.String);
par.Add("@Password", password, DbType.String);
return(DapWrapper.InnerQuerySql <UserInfo>(DbConfig.ArticleManagerConnString, sql, par).FirstOrDefault());
}
/// <summary>
/// 获取用户所有点过赞的评论id
/// </summary>
/// <param name="userId">用户id</param>
/// <param name="aId">文章id</param>
/// <returns></returns>
public static List <long> GetUserAllDianZanCommentId(int userId, long aId)
{
string sql = "select [CId] FROM [DianZanLog] WHERE UserId=@UserId AND MId=@MId AND IsCancel=0";
var par = new DynamicParameters();
par.Add("@UserId", userId, DbType.Int32);
par.Add("@MId", aId, DbType.Int64);
return(DapWrapper.InnerQuerySql <long>(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>
/// 根据 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="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));
}
