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