/// <summary>
/// 删除指定通知类型和天数内的通知
/// </summary>
/// <param name="noticeType">删除的通知类型</param>
/// <param name="days">指定天数</param>
public void DeleteNotice(Noticetype noticeType, int days)
{
string commandText;
if (noticeType == Noticetype.All)
{
commandText = string.Format("DELETE FROM [{0}notices] WHERE DATEDIFF(d,[postdatetime], GETDATE()) > {1}",
BaseConfigs.GetTablePrefix,
days);
DbHelper.ExecuteNonQuery(CommandType.Text, commandText);
}
else
{
commandText = string.Format("DELETE FROM [{0}notices] WHERE [type] = {1} AND DATEDIFF(d,[postdatetime], GETDATE()) > {2}",
BaseConfigs.GetTablePrefix,
(int)noticeType, days);
DbHelper.ExecuteNonQuery(CommandType.Text, commandText);
}
}
/// <summary>
/// 获取指定用户和通知类型的通知信息
/// </summary>
/// <param name="uid">指定的用户id</param>
/// <param name="noticeType">通知类型</param>
/// <returns></returns>
public IDataReader GetNoticeByUid(int uid, Noticetype noticeType)
{
DbParameter[] parms = {
DbHelper.MakeInParam("@uid", (DbType)SqlDbType.Int, 4, uid),
DbHelper.MakeInParam("@type", (DbType)SqlDbType.Int, 4, noticeType),
};
return DbHelper.ExecuteReader(CommandType.Text, string.Format("{0}getnoticebyuid", BaseConfigs.GetTablePrefix), parms);
}
/// <summary>
/// 获取指定用户id及通知类型的通知数
/// </summary>
/// <param name="uid">指定用户id</param>
/// <param name="noticeType">通知类型</param>
/// <returns></returns>
public int GetNoticeCountByUid(int uid, Noticetype noticeType)
{
DbParameter[] parms = {
DbHelper.MakeInParam("@uid", (DbType)SqlDbType.Int, 4, uid),
DbHelper.MakeInParam("@type", (DbType)SqlDbType.Int, 4, (int)noticeType)
};
return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure,
string.Format("{0}getnoticecountbyuid", BaseConfigs.GetTablePrefix),
parms));
}
/// <summary>
/// 获取指定用户和分页下的通知
/// </summary>
/// <param name="uid">用户id</param>
/// <returns>通知集合</returns>
public static NoticeinfoCollection GetNoticeinfoCollectionByUid(int uid, Noticetype noticetype, int pageid, int pagesize)
{
return (uid > 0 && pageid > 0) ? Discuz.Data.Notices.GetNoticeinfoCollectionByUid(uid, noticetype, pageid, pagesize) : null;
}
/// <summary>
/// 获取指定通知id和类型的通知
/// </summary>
/// <param name="uid">指定通知id</param>
/// <param name="noticeType"><see cref="Noticetype"/>通知类型</param>
/// <param name="pageId">分页id</param>
/// <param name="pageSize">页面尽寸</param>
/// <returns></returns>
public IDataReader GetNoticeByUid(int uid, Noticetype noticeType, int pageId, int pageSize)
{
string commandText = "";
if (pageId == 1)
{
commandText = string.Format("SELECT TOP {0} [nid], [uid], [nn_type], [new], [posterid], [poster], [note], [postdatetime] FROM [{1}notices] WHERE [uid]={2} {3} ORDER BY [nid] DESC",
pageSize,
BaseConfigs.GetTablePrefix,
uid,
noticeType == Noticetype.All ? "" : " AND [nn_type]=" + (int)noticeType);
}
else
{
commandText = string.Format("SELECT TOP {0} [nid], [uid], [nn_type], [new], [posterid], [poster], [note], [postdatetime] FROM [{1}notices] WHERE [nid] < (SELECT MIN([nid]) FROM (SELECT TOP {2} [nid] FROM [{1}notices] WHERE [uid]={3} {4} ORDER BY [nid] DESC) AS tblTmp ) AND [uid]={3} {4} ORDER BY [nid] DESC",
pageSize,
BaseConfigs.GetTablePrefix,
(pageId - 1) * pageSize,
uid,
noticeType == Noticetype.All ? "" : " AND [nn_type]=" + (int)noticeType);
}
return DbHelper.ExecuteReader(CommandType.Text, commandText);
}
//signalR_notice 發信者,收信者,內容,類型noticetype
public static void signalR_notice(string fromseq, string toseq, string seq, string text, Noticetype type)
{
pet.Models.Model1 db = new pet.Models.Model1();
Notice notice = new Notice();
notice.fromseq = fromseq;
notice.toseq = toseq;
notice.seq = seq;
notice.state = Convert.ToBoolean(Noticestate.未讀);
notice.text = text;
notice.postday = DateTime.Now;
notice.type = (int)type;
db.Notice.Add(notice);
db.SaveChanges();
}
/// <summary>
/// 获取指定用户id及通知类型的通知数
/// </summary>
/// <param name="uid">指定用户id</param>
/// <param name="noticetype">通知类型</param>
/// <returns></returns>
public static int GetNoticeCountByUid(int uid, Noticetype noticetype)
{
return uid > 0 ? Discuz.Data.Notices.GetNoticeCountByUid(uid, noticetype) : 0;
}
/// <summary>
/// 获取指定用户和分页下的通知
/// </summary>
/// <param name="uid">用户id</param>
/// <returns>通知集合</returns>
public static NoticeinfoCollection GetNoticeinfoCollectionByUid(int uid, Noticetype noticetype, int pageid, int pagesize)
{
return NoticeinfoCollectionDTO(DatabaseProvider.GetInstance().GetNoticeByUid(uid, noticetype, pageid, pagesize));
}
/// <summary>
/// 获取指定用户id及通知类型的通知数
/// </summary>
/// <param name="uid">指定用户id</param>
/// <param name="noticetype">通知类型</param>
/// <returns></returns>
public static int GetNoticeCountByUid(int uid, Noticetype noticetype)
{
return uid > 0 ? SAS.Data.DataProvider.Notices.GetNoticeCountByUid(uid, noticetype) : 0;
}
/// <summary>
/// 获取指定用户id及通知类型的通知数
/// </summary>
/// <param name="uid">指定用户id</param>
/// <param name="noticetype">通知类型</param>
/// <returns></returns>
public static int GetNoticeCountByUid(int uid, Noticetype noticetype)
{
return DatabaseProvider.GetInstance().GetNoticeCountByUid(uid, noticetype);
}
public void DeleteNotice(Noticetype noticetype)
{
string sql = "DELETE FROM [" + BaseConfigs.GetTablePrefix + "notices] WHERE [type] = " + (int)noticetype;
if (noticetype == Noticetype.All)
{
sql = "TRUNCATE TABLE [" + BaseConfigs.GetTablePrefix + "notices]";
}
DbHelper.ExecuteNonQuery(CommandType.Text, sql);
}
public int GetNoticeCountByUid(int uid, Noticetype noticetype)
{
return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, string.Format("SELECT COUNT(nid) FROM [" + BaseConfigs.GetTablePrefix + "notices] WHERE [uid] = {0} {1}", uid, noticetype == Noticetype.All ? "" : " AND [type]=" + (int)noticetype)), 0);
}
public IDataReader GetNoticeByUid(int uid, Noticetype noticetype, int pageid, int pagesize)
{
string sql = "";
if (pageid == 1)
{
sql = string.Format("SELECT TOP {0} * FROM [{1}notices] WHERE [uid]={2} {3} ORDER BY [postdatetime] DESC",
pagesize,
BaseConfigs.GetTablePrefix,
uid,
noticetype == Noticetype.All ? "" : " AND [type]=" + (int)noticetype);
}
else
{
sql = string.Format("SELECT TOP {0} * FROM [{1}notices] WHERE [nid] < (SELECT MIN([nid]) FROM (SELECT TOP {2} [nid] FROM [{1}notices] WHERE [uid]={3} {4} ORDER BY [postdatetime] DESC) AS tblTmp ) AND [uid]={3} {4} ORDER BY [postdatetime] DESC",
pagesize,
BaseConfigs.GetTablePrefix,
((pageid - 1) * pagesize),
uid,
noticetype == Noticetype.All ? "" : " AND [type]=" + (int)noticetype);
}
return DbHelper.ExecuteReader(CommandType.Text, sql);
}
public IDataReader GetNoticeByUid(int uid, Noticetype noticetype)
{
DbParameter[] parms = {
DbHelper.MakeInParam("@uid", DbType.Int32, 4, uid),
DbHelper.MakeInParam("@type", DbType.Int32, 4, noticetype),
};
return DbHelper.ExecuteReader(CommandType.Text, "SELECT * FROM [" + BaseConfigs.GetTablePrefix + "notices] WHERE [uid] = @uid AND [type] = @type ORDER BY [postdatetime] DESC", parms);
}