/// <summary> /// 获取多条评论信息(数量) /// </summary> /// <param name="AuthorId"></param> /// <returns></returns> public int GetReadableCount(int AuthorId) { string sql = "select count(*) from (select `comment`.id,`comment`.content,`comment`.reply_at,readable.whether,author.loginname,dataresource.avatar_url,active.title from `comment` join readable on `comment`.id = readable.commentid join active ON `comment`.ActiveID = active.id join author ON author.id = `comment`.AuthorID join dataresource on dataresource.id = author.DataID where readable.whether=0 and active.PublisherID = " + AuthorId + " ORDER BY readable.whether,`comment`.reply_at ASC ) a"; int counts = MySqlDapper.Query <int>(sql).FirstOrDefault(); return(counts); }
/// <summary> /// 历史 /// </summary> /// <param name="roleid"></param> /// <returns></returns> public List <Active> GetHistoryList(int roleid) { string sql = string.Format("SELECT a.*,b.loginname from active a INNER JOIN author b ON a.PublisherID = '{0}'", roleid); List <Active> activelist = MySqlDapper.Query <Active>(sql); return(activelist); }
/// <summary> /// 获取多条评论信息 /// </summary> /// <param name="AuthorId"></param> /// <returns></returns> public List <Readable> GetReadableList(int AuthorId) { string sql = "select `comment`.id,`comment`.content,`comment`.reply_at,readable.whether,author.loginname,dataresource.avatar_url,active.title from `comment` join readable on `comment`.id = readable.commentid join active ON `comment`.ActiveID = active.id join author ON author.id = `comment`.AuthorID join dataresource on dataresource.id = author.DataID where active.PublisherID = " + AuthorId + " ORDER BY readable.whether,`comment`.reply_at ASC"; List <Readable> readables = MySqlDapper.Query <Readable>(sql); return(readables); }
public List <Active> GetActiveList() { string sql = "select active.* from active join author on author.id=PublisherID join dataresource on dataresource.id=author.DataID"; List <Active> activelist = MySqlDapper.Query <Active>(sql); return(activelist); }
/// <summary> /// 获取单条评论信息 /// </summary> /// <param name="CommonId"></param> /// <returns></returns> public Readable GetReadable(int CommonId) { string sql = "select `comment`.id,`comment`.content,`comment`.reply_at,readable.whether,author.loginname,dataresource.avatar_url,active.title from `comment` join readable on `comment`.id = readable.commentid join active ON `comment`.ActiveID = active.id join author ON author.id = `comment`.AuthorID join dataresource on dataresource.id = author.DataID where `comment`.id = " + CommonId + " ORDER BY readable.whether,`comment`.reply_at ASC"; Readable readable = MySqlDapper.Query <Readable>(sql).FirstOrDefault(); return(readable); }
/// <summary> /// 根据用户id查询用户收藏信息 /// </summary> /// <param name="Authorid">用户id</param> /// <returns></returns> public List <Active> GetCollectAuthorid(int Authorid) { string sql = "select active.*,author.loginname,dataresource.avatar_url,type.tab,(select count(*) from `comment` where `comment`.ActiveID = active.id) as reply_count,(select reply_at from `comment` where `comment`.ActiveID = active.id ORDER BY(reply_at) desc limit 0, 1) as last_reply_at,(SELECT is_collect from collect where Authorid = " + Authorid + " and Activeid = active.id) as is_collect from active join author on author.id = PublisherID join dataresource on dataresource.id = author.DataID join type on type.id = active.TypeID join collect on active.id = collect.Activeid where collect.Authorid = " + Authorid; List <Active> actives = MySqlDapper.Query <Active>(sql).ToList(); if (actives.Count > 0) { foreach (var active in actives) { active.author = new Author(); active.author.avatar_url = active.avatar_url; active.author.loginname = active.loginname; string sql2 = "select `comment`.*,author.loginname,dataresource.avatar_url,(select is_zan from clickgood where Authorid = " + Authorid + " and Commentid = comment.id ) as is_zan,(select count(*) from clickgood where Commentid = comment.id) as zanNum from `comment` join author on author.id=`comment`.AuthorID join dataresource on dataresource.id=author.DataID where ActiveID=" + active.id; List <Comment> commentlist = MySqlDapper.Query <Comment>(sql2); foreach (var comment in commentlist) { comment.author = new Author(); comment.author.loginname = comment.loginname; comment.author.avatar_url = comment.avatar_url; } active.replies = commentlist; } } return(actives); }
/// <summary> /// 查看点过赞的评论 /// </summary> /// <param name="roleid"></param> /// <returns></returns> public List <Comment> GetPraiseList(int roleid) { string sql = string.Format(@"SELECT dataresource.avatar_url,author.loginname,`comment`.*,clickgood.* from dataresource JOIN author on dataresource.id = author.DataID JOIN `comment` ON `comment`.AuthorID = author.id JOIN clickgood ON clickgood.Commentid =`comment`.id WHERE clickgood.Authorid ='{0}'", roleid); List <Comment> commentlist = MySqlDapper.Query <Comment>(sql); return(commentlist); }
/// <summary> /// 登录判断 /// </summary> /// <param name="token"></param> /// <returns></returns> public Author GetAuthor(string accesstoken) { string sql = "select author.*,dataresource.avatar_url from author join dataresource on author.DataID = dataresource.id where OpenId = '" + accesstoken + "'"; Author author = MySqlDapper.Query <Author>(sql).FirstOrDefault(); if (author != null) { author.success = true; } return(author); }
/// <summary> /// 发布添加 /// </summary> /// <param name="title">标题</param> /// <param name="content">内容</param> /// <param name="type">类型</param> /// <param name="PublisheriD">用户id</param> /// <returns></returns> public bool PostActive(string title, string content, int type, int PublisheriD) { string sql = "insert into active values(id,'" + title + "','" + content + "'," + type + ",0,0,NOW()," + PublisheriD + ",0,0)"; int count = MySqlDapper.Execute(sql); if (count > 0) { return(true); } else { return(false); } }
public int Addjob(string Jobtitle, string Jobname, string Jobaddress, string JobMes, string Jobask, string Jobemail, int Authorid) { DynamicParameters param = new DynamicParameters(); param.Add("jobtitle", Jobtitle, DbType.String); param.Add("jobname", Jobname, DbType.String); param.Add("jobaddress", Jobaddress, DbType.String); param.Add("jobMes", JobMes, DbType.String); param.Add("jobask", Jobask, DbType.String); param.Add("jobemail", Jobemail, DbType.String); param.Add("authorid", Authorid, DbType.Int32); param.Add("result", 0, DbType.Int32, ParameterDirection.Output); MySqlDapper.Execute("p_Addjob", CommandType.StoredProcedure, param); int res = param.Get <int>("result"); return(res); }
/// <summary> /// 获取动态和相关评论的集合 /// </summary> /// <returns>返回一个集合</returns> public List <Active> GetActiveList(string tab, int id) { string sql = "select active.*,author.loginname,dataresource.avatar_url,type.tab,(select count(*) from `comment` where `comment`.ActiveID=active.id)as reply_count,(select reply_at from `comment` where `comment`.ActiveID=active.id ORDER BY(reply_at) desc limit 0,1) as last_reply_at,(SELECT is_collect from collect where Authorid = " + id + " and Activeid = active.id) as is_collect from active join author on author.id=PublisherID join dataresource on dataresource.id=author.DataID join type on type.id=active.TypeID"; if (tab != "all") { sql += " where tab='" + tab + "'"; } List <Active> activelist = new List <Active>(); try { activelist = MySqlDapper.Query <Active>(sql); } catch (Exception) { return(activelist); } if (activelist.Count > 0) { foreach (var active in activelist) { active.author = new Author(); active.author.avatar_url = active.avatar_url; active.author.loginname = active.loginname; string sql2 = "select `comment`.*,author.loginname,dataresource.avatar_url,(select is_zan from clickgood where Authorid = " + id + " and Commentid = comment.id ) as is_zan,(select count(*) from clickgood where Commentid = comment.id) as zanNum from `comment` join author on author.id=`comment`.AuthorID join dataresource on dataresource.id=author.DataID where ActiveID=" + active.id; List <Comment> commentlist = MySqlDapper.Query <Comment>(sql2); foreach (var comment in commentlist) { comment.author = new Author(); comment.author.loginname = comment.loginname; comment.author.avatar_url = comment.avatar_url; } active.replies = commentlist; } } return(activelist); }
/// <summary> /// 收藏 /// </summary> /// <param name="author_id"></param> /// <param name="active_id"></param> /// <returns></returns> public bool Collect(int author_id, int active_id) { string sql = "SELECT count(*) from collect where Authorid = " + author_id + " and Activeid = " + active_id; //List<int> i = MySqlDapper.Query<int>(sql); //int count = i.FirstOrDefault(); int count = Convert.ToInt32(MySqlDapper.Scalar(sql)); if (count > 0) { string delsql = "delete from collect where Authorid = " + author_id + " and Activeid = " + active_id; MySqlDapper.Execute(delsql); return(false); } else { string addsql = string.Format("insert into collect(Authorid,Activeid,is_collect) values({0},{1},1)", author_id, active_id); MySqlDapper.Execute(addsql); return(true); } }
/// <summary> /// 点赞 /// </summary> /// <param name="authorid"></param> /// <param name="commentid"></param> /// <returns></returns> public bool zan(int authorid, int commentid) { string sql = "select count(*) from clickgood where Authorid = " + authorid + " and Commentid = " + commentid; //List<int> i = MySqlDapper.Query<int>(sql); //int count = i.FirstOrDefault(); int count = Convert.ToInt32(MySqlDapper.Scalar(sql)); if (count > 0) { string delsql = "delete from clickgood where Authorid = " + authorid + " and Commentid = " + commentid; MySqlDapper.Execute(delsql); return(false); } else { string addsql = string.Format("insert into clickgood(Authorid,Commentid,is_zan) values({0},{1},1)", authorid, commentid); MySqlDapper.Execute(addsql); return(true); } }
/// <summary> /// 修改访问量 /// </summary> /// <param name="active"></param> /// <returns></returns> public int UpdateVisit_count(int id) { string sql = string.Format("update Active set visit_count=visit_count+1 where id='{0}'", id); return(MySqlDapper.Execute(sql)); }
public Job GetJobList(int id) { string sql = "SELECT Job.*,author.loginname,DataResource.avatar_url from Job INNER JOIN author on Job.Authorid = author.id join DataResource on DataResource.id = author.DataID where Activeid =" + id; return(MySqlDapper.Query <Job>(sql).FirstOrDefault()); }