BBSReplyFactory
BBSReplyFactory
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using Com.Csddt.BBS.Model;
using Com.Csddt.BBS.IDAL;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Com.Csddt.Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using System.Data.OracleClient;
namespace Com.Csddt.BBS.OracleDAL
{
public class BBSReplyFactory : IBBSReply
{
/// <summary>
/// 数据库连接字符串
/// </summary>
private Database db = DatabaseFactory.CreateDatabase("Com.Csddt.bbs.ConnectionString");
#region 成员方法
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(BBSReplyEntity model)
{
StringBuilder sql = new StringBuilder();
sql.Append("insert into bbs_article_reply(bbs_reply_id, user_id, bbs_article_id, bbs_reply_content, bbs_reply_date, bbs_reply_ip, bbs_ischecked, bbs_check_date,BBS_CHECKED_USER)");
sql.Append("values");
sql.Append("(seq_bbs_article_reply.nextval, :userID, :articleID, :replyContent, :replyDate,:replyIP,:replyChecked,:checkDate,:checkUser)");
OracleDatabase db1 = (OracleDatabase)db;
OracleCommand cmd = (OracleCommand)db1.GetSqlStringCommand(sql.ToString());
//DbCommand cmd = db.GetSqlStringCommand(sql.ToString());
db.AddInParameter(cmd, "userID", DbType.Int32, model.USER_ID);
db.AddInParameter(cmd, "articleID", DbType.Int32, model.BBS_ARTICLE_ID);
db1.AddParameter(cmd, "replyContent", OracleType.Clob, model.BBS_REPLY_CONTENT.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, model.BBS_REPLY_CONTENT); /**//*公告*/
//db.AddInParameter(cmd, "replyContent", DbType.String, model.BBS_REPLY_CONTENT);
db.AddInParameter(cmd, "replyDate", DbType.DateTime, model.BBS_REPLY_DATE);
db.AddInParameter(cmd, "replyIP", DbType.String, model.BBS_REPLY_IP);
db.AddInParameter(cmd, "replyChecked", DbType.Int32, model.BBS_ISCHECKED);
db.AddInParameter(cmd, "checkDate", DbType.DateTime, model.BBS_CHECK_DATE);
db.AddInParameter(cmd, "checkUser",DbType.Int32,model.BbsCheckedUser);
if (db.ExecuteNonQuery(cmd) > 0)
{ //更新回复量和回复时间 //更新评论的数据
//修改了--添加了,
DbCommand cmdSun = db.GetSqlStringCommand("update bbs_article set bbs_reply_sum=bbs_reply_sum+1,bbs_update_date=to_Date('" + model.BBS_REPLY_DATE + "','yyyy-mm-dd hh24:mi:ss') where bbs_article_id=" + model.BBS_ARTICLE_ID);
db.ExecuteNonQuery(cmdSun);
return true;
}
else
{
return false;
}
}
/// <summary>
/// 批量删除方法
/// </summary>
/// <param name="replyIDs">编号列表，以逗号分开</param>
/// <returns></returns>
public bool Deletes(string replyIDs)
{
string sql = "delete bbs_article_reply where bbs_reply_id in (" + replyIDs + ")";
DbCommand cmd = db.GetSqlStringCommand(sql);
if (db.ExecuteNonQuery(cmd) > 0)
{
return true;
}
else
{
return false;
}
}
public bool Delete(int replyID)
{
string sql = "delete bbs_article_reply where bbs_reply_id =" + replyID;
DbCommand cmd = db.GetSqlStringCommand(sql);
if (db.ExecuteNonQuery(cmd) > 0)
{
//更新回复量
DbCommand cmdSun = db.GetSqlStringCommand("update bbs_article set bbs_reply_sum=bbs_reply_sum-1 where bbs_article_id=(select b.bbs_article_id from bbs_article_reply b where b.bbs_reply_id=" + replyID + ")");
db.ExecuteNonQuery(cmdSun);
return true;
}
else
{
return false;
}
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="articleID">贴子编号</param>
/// <param name="refTotal"></param>
/// <returns></returns>
public DataTable GetPageList(int pageSize, int pageIndex, int bbsArticleID, ref int refTotal)
{
string sql = "select count(*) from bbs_article_reply where bbs_article_id=" + bbsArticleID + " and bbs_ischecked=1";
DbCommand cmdCount = db.GetSqlStringCommand(sql);
if (db.ExecuteScalar(cmdCount) != null)
{
refTotal = int.Parse(db.ExecuteScalar(cmdCount).ToString());
}
else
{
return null;
}
string sql2 = "select * from (select row_number() over (order by t.bbs_reply_date) as rn, t.*,a.bbs_article_title,u.user_name from bbs_article_reply t inner join bbs_article a on t.bbs_article_id=a.bbs_article_id left join csddt_new.adm_user u on a.user_id=u.user_id where a.bbs_article_id=" + bbsArticleID + " and bbs_ischecked=1) where rn between " + pageSize * (pageIndex - 1) + " and " + pageSize * pageIndex;
DbCommand cmd = db.GetSqlStringCommand(sql2);
DataSet ds = db.ExecuteDataSet(cmd);
return ds.Tables[0];
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="start">开始时间</param>
/// <param name="end">结束时间</param>
/// <param name="author">作者</param>
/// <param name="isChecked">是否已审核</param>
/// <param name="articleID">专题编号</param>
/// <param name="refTotal"></param>
/// <returns></returns>
public DataTable GetPageList(int pageSize, int pageIndex, DateTime? start, DateTime? end, string author, int isChecked, int typeid, ref int refTotal)
{
string sql = "select count(*) from bbs_article_reply t inner join bbs_article a on t.bbs_article_id=a.bbs_article_id where ";
string condition = " a.subject_type_id=" + typeid;
if (!string.IsNullOrEmpty(author))
{
condition = condition + " and u.user_name like '%" + author + "%'";
}
if (isChecked != -1)
{
condition = condition + " and bbs_ischecked=" + isChecked;
}
if (start != null && end != null)
{
condition = condition + " and bbs_article_date between to_Date('" + start + "','yyyy-mm-dd hh24:mi:ss') and to_date('" + end + "','yyyy-mm-dd hh24:mi:ss')";
}
DbCommand cmdCount = db.GetSqlStringCommand(string.Concat(sql, condition));
if (db.ExecuteScalar(cmdCount) != null)
{
refTotal = int.Parse(db.ExecuteScalar(cmdCount).ToString());
}
else
{
return null;
}
//string sql2 = "select * from (select row_number() over (order by t.bbs_reply_date desc) as rn, t.*,a.bbs_article_title,u.user_name from bbs_article_reply t inner join bbs_article a on t.bbs_article_id=a.bbs_article_id inner join csddt_new.adm_user u on a.user_id=u.user_id where a.bbs_article_id=" + bbsArticleID + ") where rn between " + pageSize * (pageIndex - 1) + " and " + pageSize * pageIndex;
string sql2 = string.Format("select * from (select row_number() over (order by t.bbs_reply_date) as rn, t.*,a.bbs_article_title,u.user_name from bbs_article_reply t inner join bbs_article a on t.bbs_article_id=a.bbs_article_id left join csddt_new.adm_user u on a.user_id=u.user_id where {0} ) where rn between " + pageSize * (pageIndex - 1) + " and " + pageSize * pageIndex, condition);
DbCommand cmd = db.GetSqlStringCommand(sql2);
DataSet ds = db.ExecuteDataSet(cmd);
return ds.Tables[0];
}
/// <summary>
/// 批量审核方法
/// </summary>
/// <param name="replyIDs">回复编号</param>
/// <returns></returns>
public bool Checked(string replyIDs)
{
string sql = "update bbs_article_reply set bbs_ischecked=1,bbs_check_date= to_Date('" + DateTime.Now + "','yyyy-mm-dd hh24:mi:ss') where bbs_reply_id in (" + replyIDs + ")";
DbCommand cmd = db.GetSqlStringCommand(sql);
if (db.ExecuteNonQuery(cmd) > 0)
{
return true;
}
else
{
return false;
}
}
#endregion 成员方法
public IList<BBSReplyEntity> GetList(int userId, int start, int count, out int totCount)
{
if (start < 0 || count <= 0)
{
totCount = 0;
return null;
}
string sWhere = "";
if (userId > 0)
{
sWhere += " and bar.user_id =: USER_ID";
}
sWhere += " order by bar.bbs_reply_date desc ";
DbCommand cmdCount = db.GetSqlStringCommand(@"select count(*)
from bbs_article_reply bar
inner join adm_user au on bar.user_id = au.user_id
inner join bbs_article ba on ba.bbs_article_id = bar.bbs_article_id
inner join bbs_module bm on ba.category_id = bm.mid
inner join life_circle lc on get_bbs_Module_ParentID(ba.category_id) =
lc.bbsid where 1=1 " + sWhere + "");
if (userId > 0)
{
db.AddInParameter(cmdCount, "USER_ID", DbType.Int32, userId);
}
totCount = Convert.ToInt32(db.ExecuteScalar(cmdCount));
IList<BBSReplyEntity> list = null;
string sql = @"select *
from (select r.*,rownum as rowno
from ( select bar.*
from bbs_article_reply bar
inner join adm_user au on bar.user_id = au.user_id
inner join bbs_article ba on ba.bbs_article_id = bar.bbs_article_id
inner join bbs_module bm on ba.category_id = bm.mid
inner join life_circle lc on get_bbs_Module_ParentID(ba.category_id) =
lc.bbsid " + sWhere + ") r) where rowno >:startIndex and rowno<=:endIndex ";
DbCommand cmd = db.GetSqlStringCommand(sql);
if (userId > 0)
{
db.AddInParameter(cmd, "USER_ID", DbType.Int32, userId);
}
db.AddInParameter(cmd, "startIndex", DbType.Int32, start);
db.AddInParameter(cmd, "endIndex", DbType.Int32, start + count);
using (IDataReader reader = db.ExecuteReader(cmd))
{
list = ReplyBind(reader);
}
return list;
}
public IList<BBSReplyEntity> ReplyBind(IDataReader reader)
{
IList<BBSReplyEntity> ReplyList = new List<BBSReplyEntity>();
while (reader != null && reader.Read())
{
BBSReplyEntity entity = new BBSReplyEntity();
entity.BBS_REPLY_ID =Common.Common.ToInt32(reader["BBS_REPLY_IP"].ToString(), 0);
entity.USER_ID = Common.Common.ToInt32(reader["USER_ID"].ToString(), 0);
entity.BBS_ARTICLE_ID = Common.Common.ToInt32(reader["BBS_ARTICLE_ID"].ToString(), 0);
entity.BBS_REPLY_CONTENT = reader["BBS_REPLY_CONTENT"].ToString();
entity.BBS_REPLY_DATE = Common.Common.ToDateTime(reader["BBS_REPLY_DATE"].ToString(), DateTime.Now);
entity.BBS_REPLY_IP = reader["BBS_REPLY_IP"].ToString();
entity.BBS_ISCHECKED = Common.Common.ToInt32(reader["BBS_ISCHECKED"].ToString(), 0);
entity.BBS_CHECK_DATE =Common.Common.ToDateTime(reader["BBS_CHECK_DATE"].ToString(), DateTime.Now);
entity.BBS_EDITTIME = Common.Common.ToDateTime(reader["BBS_EDITTIME"].ToString(), DateTime.Now);
entity.BBS_EDITUSER = Common.Common.ToInt32(reader["BBS_EDITUSER"].ToString(), 0);
entity.BBS_REPLY_ID = Convert.ToInt32(reader["BBS_REPLY_ID"]);
if (DBNull.Value != reader["BBS_CHECKED_USER"])
entity.BbsCheckedUser = Common.Common.ToInt32(reader["BBS_CHECKED_USER"].ToString(),0);
//扩展字段
try
{
if (DBNull.Value != reader["login_name"])
entity.UserLoginName = reader["login_name"].ToString();
}
catch
{
}
try
{
if (DBNull.Value != reader["USER_NICKNAME"])
entity.UserNickName = reader["USER_NICKNAME"].ToString();
}
catch
{
}
try
{
if (DBNull.Value != reader["user_img"])
entity.UserImg = reader["user_img"].ToString();
}
catch
{
}
ReplyList.Add(entity);
}
return ReplyList;
}
/// <summary>
/// 用户回帖数量
/// </summary>
/// <returns></returns>
public DataTable Usersreplynumber()
{
string sql = @"select * from (select u.user_id, u.login_name,count(*) as num from bbs_article_reply a join adm_user u on a.user_id=u.user_id
where trunc(bbs_reply_date)>=trunc(sysdate,'day')+1
AND trunc(bbs_reply_date)<= Next_day(SYSDATE,2)-1 group by u.user_id, u.login_name order by count(*) desc)where rownum<=10";
DbCommand cmd = db.GetSqlStringCommand(sql);
DataSet ds = db.ExecuteDataSet(cmd);
return ds.Tables[0];
}
/// <summary>
/// 获得评论分页列表
/// </summary>
/// <param name="articleID">帖子id</param>
/// <param name="pageIndex">当前页索引</param>
/// <param name="pageSize">每页显示多少</param>
/// <returns></returns>
public IList<BBSReplyEntity> GetbbsArticleList(int articleID, int pageIndex, int pageSize, ref int refTotal)
{
//查询总页数
String strSql = "select count(*) from bbs_article_reply r where r.bbs_article_id= " + articleID;
refTotal = GetCount(strSql);
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
StringBuilder str = new StringBuilder();
str.Append(" select *");
str.Append(" from (select rownum as rowindex, t.*");
str.Append(" from (select r.*, u.login_name, u.user_nickname, u.user_img");
str.Append(" from bbs_article_reply r");
str.Append(" left join adm_user u on r.user_id = u.user_id");
str.AppendFormat(" where r.bbs_article_id = {0} and bbs_ischecked=1 order by r.bbs_reply_date asc) t ) ", articleID);
str.AppendFormat(" where rowindex >= {0}",startIndex);
str.AppendFormat(" and rowindex <= {0}",endIndex);
DbCommand cmd = db.GetSqlStringCommand(str.ToString());
IList<BBSReplyEntity> list = new List<BBSReplyEntity>();
using (IDataReader reader = db.ExecuteReader(cmd))
{
list = ReplyBind(reader);
}
return list;
}
public int GetCount(string strSql)
{
DbCommand cmd = db.GetSqlStringCommand(strSql);
try
{
Object objValue = db.ExecuteScalar(cmd);
if (DBNull.Value != objValue)
{
return Com.Csddt.Common.Common.ToInt32(objValue.ToString(), 0);
}
else
{
return 0;
}
}
catch
{
return 0;
}
}
/// <summary>
/// 获得某帖子总回复数
/// </summary>
/// <param name="articleId"></param>
/// <returns></returns>
public int GetReplayCount(int articleId)
{
String strSql = "select count(*) from bbs_article_reply r where r.bbs_article_id= " + articleId;
return GetCount(strSql);
}
}
}
