/// <summary>
/// 是否存在
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public bool ExistsUserName(string userName)
{
string cmdText = "select count(1) from [loachs_users] where [userName] = @userName ";
OleDbParameter[] prams =
{
OleDbHelper.MakeInParam("@userName", OleDbType.VarWChar, 50, userName),
};
return(Convert.ToInt32(OleDbHelper.ExecuteScalar(CommandType.Text, cmdText, prams)) > 0);
}
public SettingInfo GetSetting()
{
string cmdText = "select top 1 [setting] from [loachs_sites]";
string str = Convert.ToString(OleDbHelper.ExecuteScalar(cmdText));
object obj = DeSerialize(typeof(SettingInfo), str);
if (obj == null)
{
return(new SettingInfo());
}
return((SettingInfo)obj);
}
/// <summary>
/// 添加
/// </summary>
/// <param name="postinfo">实体</param>
/// <returns>成功返回新记录的ID,失败返回 0</returns>
public int InsertPost(PostInfo postinfo)
{
CheckSlug(postinfo);
string cmdText = @"insert into [loachs_posts]
(
[CategoryId],[Title],[Summary],[Content],[Slug],[UserId],[CommentStatus],[CommentCount],[ViewCount],[Tag],[UrlFormat],[Template],[Recommend],[Status],[TopStatus],[HideStatus],[CreateDate],[UpdateDate]
)
values
(
@CategoryId,@Title,@Summary,@Content,@Slug,@UserId,@CommentStatus,@CommentCount,@ViewCount,@Tag,@UrlFormat,@Template,@Recommend,@Status,@TopStatus,@HideStatus,@CreateDate,@UpdateDate
)";
OleDbParameter[] prams =
{
OleDbHelper.MakeInParam("@CategoryId", OleDbType.Integer, 4, postinfo.CategoryId),
OleDbHelper.MakeInParam("@Title", OleDbType.VarWChar, 255, postinfo.Title),
OleDbHelper.MakeInParam("@Summary", OleDbType.VarWChar, 0, postinfo.Summary),
OleDbHelper.MakeInParam("@Content", OleDbType.VarWChar, 0, postinfo.Content),
OleDbHelper.MakeInParam("@Slug", OleDbType.VarWChar, 255, postinfo.Slug),
OleDbHelper.MakeInParam("@UserId", OleDbType.Integer, 4, postinfo.UserId),
OleDbHelper.MakeInParam("@CommentStatus", OleDbType.Integer, 1, postinfo.CommentStatus),
OleDbHelper.MakeInParam("@CommentCount", OleDbType.Integer, 4, postinfo.CommentCount),
OleDbHelper.MakeInParam("@ViewCount", OleDbType.Integer, 4, postinfo.ViewCount),
OleDbHelper.MakeInParam("@Tag", OleDbType.VarWChar, 255, postinfo.Tag),
OleDbHelper.MakeInParam("@UrlFormat", OleDbType.Integer, 1, postinfo.UrlFormat),
OleDbHelper.MakeInParam("@Template", OleDbType.VarChar, 50, postinfo.Template),
OleDbHelper.MakeInParam("@Recommend", OleDbType.Integer, 1, postinfo.Recommend),
OleDbHelper.MakeInParam("@Status", OleDbType.Integer, 1, postinfo.Status),
OleDbHelper.MakeInParam("@TopStatus", OleDbType.Integer, 1, postinfo.TopStatus),
OleDbHelper.MakeInParam("@HideStatus", OleDbType.Integer, 1, postinfo.HideStatus),
OleDbHelper.MakeInParam("@CreateDate", OleDbType.Date, 8, postinfo.CreateDate),
OleDbHelper.MakeInParam("@UpdateDate", OleDbType.Date, 8, postinfo.UpdateDate)
};
OleDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
int newId = StringHelper.ObjectToInt(OleDbHelper.ExecuteScalar("select top 1 [PostId] from [Loachs_Posts] order by [PostId] desc"));
//if (newId > 0)
//{
// OleDbHelper.ExecuteNonQuery(string.Format("update [loachs_users] set [postcount]=[postcount]+1 where [userid]={0}", postinfo.UserId));
// OleDbHelper.ExecuteNonQuery("update [loachs_sites] set [postcount]=[postcount]+1");
// OleDbHelper.ExecuteNonQuery(string.Format("update [loachs_terms] set [count]=[count]+1 where [termid]={0}", postinfo.CategoryId));
//}
return(newId);
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="postId"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="totalRecord"></param>
/// <returns></returns>
public List <CommentInfo> GetCommentList(int pageSize, int pageIndex, out int totalRecord, int order, int userId, int postId, int parentId, int approved, int emailNotify, string keyword)
{
string condition = " 1=1 ";// "[ParentId]=0 and [PostId]=" + postId;
if (userId != -1)
{
condition += " and userid=" + userId;
}
if (postId != -1)
{
condition += " and postId=" + postId;
}
if (parentId != -1)
{
condition += " and parentId=" + parentId;
}
if (approved != -1)
{
condition += " and approved=" + approved;
}
if (emailNotify != -1)
{
condition += " and emailNotify=" + emailNotify;
}
if (!string.IsNullOrEmpty(keyword))
{
condition += string.Format(" and (content like '%{0}%' or author like '%{0}%' or ipaddress like '%{0}%' or email like '%{0}%' or siteurl like '%{0}%' )", keyword);
}
string cmdTotalRecord = "select count(1) from [loachs_comments] where " + condition;
totalRecord = Convert.ToInt32(OleDbHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord));
// throw new Exception(cmdTotalRecord);
string cmdText = OleDbHelper.GetPageSql("[loachs_comments]", "[CommentId]", "*", pageSize, pageIndex, order, condition);
return(DataReaderToCommentList(OleDbHelper.ExecuteReader(cmdText)));
}
/// <summary>
/// 统计评论
/// </summary>
/// <param name="userId"></param>
/// <param name="postId"></param>
/// <param name="incChild"></param>
/// <returns></returns>
public int GetCommentCount(int userId, int postId, bool incChild)
{
string condition = " 1=1 ";
if (userId != -1)
{
condition += " and [userId] = " + userId;
}
if (postId != -1)
{
condition += " and [postId] = " + postId;
}
if (incChild == false)
{
condition += " and [parentid]=0";
}
string cmdText = "select count(1) from [loachs_comments] where " + condition;
return(Convert.ToInt32(OleDbHelper.ExecuteScalar(CommandType.Text, cmdText)));
}
/// <summary>
/// 检查别名是否重复
/// </summary>
/// <param name="term"></param>
/// <returns></returns>
private bool CheckSlug(CategoryInfo term)
{
while (true)
{
string cmdText = string.Empty;
if (term.CategoryId == 0)
{
cmdText = string.Format("select count(1) from [loachs_terms] where [Slug]='{0}' and [type]={1}", term.Slug, (int)TermType.Category);
}
else
{
cmdText = string.Format("select count(1) from [loachs_terms] where [Slug]='{0}' and [type]={1} and [termid]<>{2}", term.Slug, (int)TermType.Category, term.CategoryId);
}
int r = Convert.ToInt32(OleDbHelper.ExecuteScalar(cmdText));
if (r == 0)
{
return(true);
}
term.Slug += "-2";
}
}
public List <PostInfo> GetPostList(int pageSize, int pageIndex, out int recordCount, int categoryId, int tagId, int userId, int recommend, int status, int topstatus, int hidestatus, string begindate, string enddate, string keyword)
{
string condition = " 1=1 ";
if (categoryId != -1)
{
condition += " and categoryId=" + categoryId;
}
if (tagId != -1)
{
condition += " and tag like '%{" + tagId + "}%'";
}
if (userId != -1)
{
condition += " and userid=" + userId;
}
if (recommend != -1)
{
condition += " and recommend=" + recommend;
}
if (status != -1)
{
condition += " and status=" + status;
}
if (topstatus != -1)
{
condition += " and topstatus=" + topstatus;
}
if (hidestatus != -1)
{
condition += " and hidestatus=" + hidestatus;
}
if (!string.IsNullOrEmpty(begindate))
{
condition += " and createdate>=#" + begindate + "#";
}
if (!string.IsNullOrEmpty(enddate))
{
condition += " and createdate<#" + enddate + "#";
}
if (!string.IsNullOrEmpty(keyword))
{
condition += string.Format(" and (summary like '%{0}%' or title like '%{0}%' )", keyword);
}
string cmdTotalRecord = "select count(1) from [loachs_posts] where " + condition;
// throw new Exception(cmdTotalRecord);
recordCount = StringHelper.ObjectToInt(OleDbHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord));
string cmdText = OleDbHelper.GetPageSql("[Loachs_Posts]", "[PostId]", "*", pageSize, pageIndex, 1, condition);
return(DataReaderToCommentList(OleDbHelper.ExecuteReader(cmdText)));
}