public IEnumerable <Entity.blog_tb_tag> GetTags(string blogID)
{
string sql = "select blog_tb_tag.*,categoryDisplay from blog_tb_tag LEFT JOIN blog_tb_category on blog_tb_category.categoryID=blog_tb_tag.categoryID where blog_tb_tag.blogID=@blogID";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@blogID", blogID));
return(ObjectHelper.DataTableToModel <blog_tb_tag>(dt));
}
public List <Entity.blog_tb_slider> Query(string blogID)
{
string sql = "select * from blog_tb_slider where BlogID=@BlogID order by OrderWeight DESC limit 0,5";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@BlogID", blogID));
return(ObjectHelper.DataTableToModel <blog_tb_slider>(dt));
}
public IEnumerable <Entity.blog_tb_topic> GetTopic(string blogID)
{
string sql = "select blog_tb_topic.*,ifnull(ArticleCount,0) ArticleCount from blog_tb_topic LEFT JOIN (select count(*) ArticleCount ,topicID from blog_tb_article GROUP BY topicID) table1 on table1.topicID=blog_tb_topic.topicID where blogID=@blogID";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@blogID", blogID));
return(ObjectHelper.DataTableToModel <blog_tb_topic>(dt));
}
public blog_tb_article_content GetArticleContent(string articleID)
{
string sql = "select * from blog_tb_article_content where articleID=@articleID";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@articleID", articleID));
return(ObjectHelper.DataTableToSingleModel <blog_tb_article_content>(dt));
}
public IList <blog_tb_Photo> Query(string albumID)
{
string sql = "select * from blog_tb_Photo where AlbumID=@AlbumID and IsDelete=0";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@AlbumID", albumID));
return(FYJ.ObjectHelper.DataTableToModel <blog_tb_Photo>(dt));
}
public DataTable GetMenuTable(string blogID)
{
string sql = "select *,(select count(1) from blog_tb_menu where parentID=table1.menuID ) as cc from blog_tb_menu as table1 where blogID=@blogID order by menuOrder desc";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@blogID", blogID));
return(dt);
}
public IEnumerable <blog_tb_tag> GetArticleTags(string articleID)
{
string sql = "select * from blog_tb_tag where tagID in ( select tagID from blog_tb_tagArticle where articleID=@articleID)";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@articleID", articleID));
return(ObjectHelper.DataTableToModel <blog_tb_tag>(dt));
}
public int SaveTemp(string userID, string content)
{
if (!String.IsNullOrWhiteSpace(content))
{
string sql = "select ID,TalkContent from blog_tb_Talk where UserID=@UserID and IsTemp=1 limit 0,1";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@UserID", userID));
if (dt.Rows.Count > 0)
{
blog_tb_Talk entity = GetEntity(dt.Rows[0]["ID"].ToString());
entity.TalkContent = content;
entity.TalkDatetime = DateTime.Now;
entity.ADD_DATE = DateTime.Now;
entity.UPDATE_DATE = DateTime.Now;
Update(entity);
}
else
{
blog_tb_Talk entity = new blog_tb_Talk();
entity.UserID = userID;
entity.ID = Guid.NewGuid().ToString("N");
entity.TalkContent = content;
entity.TalkDatetime = DateTime.Now;
entity.ADD_DATE = DateTime.Now;
entity.UPDATE_DATE = DateTime.Now;
entity.IsTemp = true;
Insert(entity);
}
}
return(1);
}
public int UpdateComment(string articleID, int add)
{
string addstr = (add >= 0 ? "+" + add : add.ToString());
string sql = "update blog_tb_article_extend set articleCommentTimes=articleCommentTimes" + addstr + " where articleID=@articleID";
return(DbInstance.ExecuteSql(sql, DbInstance.CreateParameter("@articleID", articleID)));
}
public bool IsReplyed(string articleID, string userID)
{
string sql = "select 1 from blog_tb_comment where articleID=@articleID and userID=@userID";
return(DbInstance.Exists(sql, DbInstance.CreateParameter("@articleID", articleID)
, DbInstance.CreateParameter("@userID", userID)));
}
public new int Delete(string commentID)
{
try
{
DbInstance.BeginTran();
string sql = "select articleID,articleCommentTimes from blog_view_article where articleID=(select articleID from blog_tb_comment where commentID=@commentID)";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@commentID", commentID));
if (dt.Rows.Count > 0)
{
int articleCommentTimes = Convert.ToInt32(dt.Rows[0]["articleCommentTimes"]);
articleCommentTimes = Math.Max(articleCommentTimes - 1, 0);
sql = "update blog_tb_article_extend set articleCommentTimes=" + articleCommentTimes + " where articleID=@articleID";
DbInstance.ExecuteSql(sql, DbInstance.CreateParameter("@articleID", dt.Rows[0]["articleID"]));
}
base.Delete(commentID);
DbInstance.Commit();
return(1);
}
catch
{
DbInstance.Rollback();
throw;
}
}
public IList <SelectModel> QueryAlbumSelect(string userID)
{
string sql = @"SELECT ID Value,Display Text FROM blog_tb_Album where UserID=@UserID";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@UserID", userID));
return(FYJ.ObjectHelper.DataTableToModel <SelectModel>(dt));
}
public List <blog_tb_Talk> Query(string userID)
{
string sql = "select * from blog_tb_Talk where UserID=@UserID and IsTemp=0 and IsDisabled=0 order by TalkDatetime DESC limit 0,100";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@UserID", userID));
return(ObjectHelper.DataTableToModel <blog_tb_Talk>(dt));
}
public DataTable Query(string albumID)
{
string sql = "select * from blog_tb_Photo where AlbumID=@AlbumID and IsDelete=0";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@AlbumID", albumID));
return(dt);
}
public System.Collections.Generic.IEnumerable <Entity.blog_tb_topic> GetTopic(string blogID)
{
string sql = "select * from blog_tb_topic where blogID=@blogID";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@blogID", blogID));
return(FYJ.Common.ObjectHelper.DataTableToModel <blog_tb_topic>(dt));
}
//public override object NewID()
//{
// int max = DbInstance.GetInt("select max(articleID) from blog_tb_article");
// return base.NewID();
//}
public int UpdateExtend(string articleID, string lastOpenIP, string lastOpenUserID)
{
string sql = "select * from blog_tb_article_extend where articleID=@articleID";
if (DbInstance.Exists(sql, DbInstance.CreateParameter("@articleID", articleID)))
{
sql = " update blog_tb_article_extend set lastOpenDatetime=GETDATE(),lastOpenIP=@ip,lastOpenUserID=@userID,UPDATE_DATE=GETDATE(),articleClickTimes=articleClickTimes+1 where articleID=@articleID";
DbInstance.ExecuteSql(sql
, DbInstance.CreateParameter("@articleID", articleID)
, DbInstance.CreateParameter("@ip", lastOpenIP)
, DbInstance.CreateParameter("@userID", lastOpenUserID));
}
else
{
blog_tb_article_extend entity = new blog_tb_article_extend();
entity.articleClickTimes = 1;
entity.articleCommentTimes = 0;
entity.articleID = Convert.ToInt32(articleID);
entity.extendID = Guid.NewGuid().ToString("N");
entity.lastOpenDatetime = DateTime.Now;
entity.UPDATE_DATE = DateTime.Now;
entity.lastOpenIP = lastOpenIP;
entity.lastOpenUserID = lastOpenUserID;
FYJ.Data.Entity.EntityHelper <blog_tb_article_extend> .Insert(entity, "blog_tb_article_extend", "extendID", true, DbInstance);
}
return(1);
}
public IEnumerable <Entity.blog_tb_tag> GetTags(string blogID)
{
string sql = "select * from blog_tb_tag where blogID=@blogID";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@blogID", blogID));
return(FYJ.Common.ObjectHelper.DataTableToModel <blog_tb_tag>(dt));
}
public System.Data.DataTable GetPhotoBySha1(string sha1, string userID)
{
DataTable dt = DbInstance.GetDataTable("select * from blog_tb_Photo where Sha1=@Sha1 and UserID=@UserID "
, DbInstance.CreateParameter("@Sha1", sha1)
, DbInstance.CreateParameter("@UserID", userID));
return(dt);
}
public IList <blog_tb_Photo> GetPhotoBySha1(string sha1, string userID)
{
DataTable dt = DbInstance.GetDataTable("select * from blog_tb_Photo where Sha1=@Sha1 and UserID=@UserID "
, DbInstance.CreateParameter("@Sha1", sha1)
, DbInstance.CreateParameter("@UserID", userID));
return(FYJ.ObjectHelper.DataTableToModel <blog_tb_Photo>(dt));
}
public blog_tb_article GetSingle(int articleID, string select = "*")
{
DataTable dt = DbInstance.GetDataTable("select " + select + " from blog_tb_article where articleID=@articleID"
, DbInstance.CreateParameter("@articleID", articleID));
blog_tb_article model = FYJ.Data.Util.DataTableHelper.DataTableToModel <blog_tb_article>(dt).FirstOrDefault();
return(model);
}
/// <summary>
/// 获取相关主题文章
/// </summary>
/// <param name="articleID"></param>
/// <returns></returns>
public DataTable GetArticleTopicList(string articleID)
{
string sql = @"select row_number() over(order by articleDatetime desc) as rownum,articleID,articleTitle,articleTitle2,articleUrl,topicDisplay from blog_view_article
where topicID =(select topicID from blog_view_article where articleID=@articleID)
and articleID <>@articleID and isDisabled=0";
DataTable dt = this.DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@articleID", articleID));
return(dt);
}
public long GetArticleUpdateLong(string articleID)
{
//不用EF 因为有缓存
//long date = GetEntities().Where(c => c.articleID == articleID).Select(c => c.UPDATE_DATE).FirstOrDefault().ToFileTimeUtc();
object obj = DbInstance.GetObject("select UPDATE_DATE from blog_tb_article where articleID=@articleID"
, DbInstance.CreateParameter("@articleID", articleID));
long date = Convert.ToInt64(Convert.ToDateTime(obj).ToString("yyyyMMddHHmmss"));
return(date);
}
public int GetNoReadCommentCount(string blogID)
{
string sql = @"select COUNT(*) from blog_tb_comment
inner join blog_view_article on blog_view_article.articleID=blog_tb_comment.articleID
left join blog_tb_article_extend on blog_tb_article_extend.articleID=blog_view_article.articleID
where blogID=@blogID
and blog_tb_comment.ADD_DATE>=blog_tb_article_extend.lastOpenDatetime";
int count = DbInstance.GetInt(sql, DbInstance.CreateParameter("@blogID", blogID));
return(count);
}
public int ChangeArticle(string ids, string categoryID)
{
string tmp = "";
foreach (string s in ids.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
{
string t = s.Trim('\'');
tmp += "'" + t + "',";
}
tmp = tmp.TrimEnd(',');
string sql = "update blog_tb_article set categoryID=@categoryID where articleID in (" + tmp + ")";
return DbInstance.ExecuteSql(sql, DbInstance.CreateParameter("@categoryID", categoryID));
}
public IList <blog_tb_Album> QueryAlbum(ref GridPager pager)
{
int count = 0;
List <IDataParameter> plist = null;
if (pager.Parameters != null && pager.Parameters.Count > 0)
{
plist = new List <IDataParameter>();
foreach (string key in pager.Parameters.Keys)
{
plist.Add(DbInstance.CreateParameter("@" + key.TrimStart('@'), pager.Parameters[key]));
}
}
string table = @"
(SELECT
a.ID,
a.UserID,
a. NAME,
a.Display,
a.ADD_DATE,
a.Permission,
a.UPDATE_DATE,
IFNULL(c.cc, 0) AS PhotoCount,
CASE WHEN IFNULL(CoverUrl, '') = '' THEN p.ThumbUrl ELSE CoverUrl END AS CoverUrl
FROM
blog_tb_Album AS a
LEFT JOIN (
SELECT COUNT(*) AS cc,AlbumID
FROM
blog_tb_Photo
GROUP BY
AlbumID
) AS c ON c.AlbumID = a.ID
LEFT OUTER JOIN (
SELECT
AlbumID, MIN(ThumbUrl) AS ThumbUrl
FROM
blog_tb_Photo AS blog_tb_Photo_1
GROUP BY
AlbumID
) AS p ON p.AlbumID = a.ID
) t
";
DataTable dt = DbInstance.GetDataTable(out count, table, pager.OrderColumn + " " + pager.Order, plist, "*", pager.Where, pager.CurrentPage, pager.PageSize);
pager.TotalRows = count;
return(FYJ.ObjectHelper.DataTableToModel <blog_tb_Album>(dt));
}
public int CreateCatelog(string ids)
{
ids = ids.Trim(',');
string sql = "select articleTitle,articleID from blog_tb_article where articleID in (" + ids + ") order by ADD_DATE";
DataTable dt = DbInstance.GetDataTable(sql);
string str = "";
foreach (DataRow dr in dt.Rows)
{
str += "<p><a href=\"/artic-" + dr["articleID"] + ".html\">" + dr["articleTitle"] + "</a></p>";
}
DbInstance.BeginTran();
int result = DbInstance.ExecuteSql("update blog_tb_article_content set articleContent=@articleContent+articleContent,UPDATE_DATE=@UPDATE_DATE where articleID in (" + ids + ")"
, DbInstance.CreateParameter("@articleContent", str)
, DbInstance.CreateParameter("@UPDATE_DATE", DateTime.Now));
DbInstance.Commit();
return result;
}
public List <blog_tb_menu> GetList(string blogID, string parentID)
{
string sql = @"select *,(select count(1) from blog_tb_menu where parentID=table1.menuID ) as rowcount from blog_tb_menu as table1 where blogID=@blogID ";
List <IDbDataParameter> paras = new List <IDbDataParameter>();
if (!String.IsNullOrEmpty(parentID))
{
sql += " and parentID=@parentID";
paras.Add(DbInstance.CreateParameter("@parentID", parentID));
}
sql += " order by menuOrder desc";
paras.Add(DbInstance.CreateParameter("@blogID", blogID));
DataTable dt = DbInstance.GetDataTable(sql, paras);
List <blog_tb_menu> list = ObjectHelper.DataTableToModel <blog_tb_menu>(dt);
return(list);
}
public System.Data.DataTable QueryAlbum(ref GridPager pager)
{
int count = 0;
List <IDataParameter> plist = null;
if (pager.Parameters != null && pager.Parameters.Count > 0)
{
plist = new List <IDataParameter>();
foreach (string key in pager.Parameters.Keys)
{
plist.Add(DbInstance.CreateParameter("@" + key.TrimStart('@'), pager.Parameters[key]));
}
}
DataTable dt = DbInstance.GetDataTable(out count, "blog_view_Album", pager.OrderColumn + " " + pager.Order, plist, "*", pager.Where, pager.CurrentPage, pager.PageSize);
pager.TotalRows = count;
return(dt);
}
public int SaveTempContent(string articleID, string content)
{
int result = 0;
if (this.DbInstance.Exists("select 1 from blog_tb_article_temp where articleID=@articleID", DbInstance.CreateParameter("@articleID", articleID)))
{
result = DbInstance.ExecuteSql("update blog_tb_article_temp set articleContent=@articleContent,UPDATE_DATE=GETDATE() where articleID=@articleID"
, DbInstance.CreateParameter("@articleID", articleID)
, DbInstance.CreateParameter("@articleContent", content));
}
else
{
result = DbInstance.ExecuteSql("insert into blog_tb_article_temp values(@ID,@articleID,@articleContent,GETDATE(),GETDATE())",
DbInstance.CreateParameter("@ID", Guid.NewGuid().ToString("N")),
DbInstance.CreateParameter("@articleID", articleID),
DbInstance.CreateParameter("@articleContent", content));
}
return(result);
}
public int UpdateLastOpenDatetime(string articleID)
{
int count = DbInstance.GetInt("select count(*) from blog_tb_article_extend where articleID=@articleID", DbInstance.CreateParameter("@articleID", articleID));
if (count == 0)
{
return(DbInstance.ExecuteSql("insert into blog_tb_article_extend(extendID,articleID,lastOpenDatetime,UPDATE_DATE) values (@extendID,@articleID,@lastOpenDatetime,@UPDATE_DATE)"
, DbInstance.CreateParameter("@extendID", Guid.NewGuid().ToString("N"))
, DbInstance.CreateParameter("@articleID", articleID)
, DbInstance.CreateParameter("@lastOpenDatetime", DateTime.Now)
, DbInstance.CreateParameter("@UPDATE_DATE", DateTime.Now)));
}
else
{
return(DbInstance.ExecuteSql("update blog_tb_article_extend set lastOpenDatetime=@lastOpenDatetime,UPDATE_DATE=@UPDATE_DATE where articleID=@articleID"
, DbInstance.CreateParameter("@articleID", articleID)
, DbInstance.CreateParameter("@lastOpenDatetime", DateTime.Now)
, DbInstance.CreateParameter("@UPDATE_DATE", DateTime.Now)));
}
}
