public IList<Core.Business.Notice> GetAllNotice()
{
IList<Core.Business.Notice> noticelist = new List<Core.Business.Notice>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
SqlDataReader reader = sql.ExecuteSPReader("USP_Notice_SelectAll");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Notice notice = new Core.Business.Notice();
if (!reader.IsDBNull(0)) notice.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) notice.Content = reader.GetString(1);
if (!reader.IsDBNull(2)) notice.AccountId = reader.GetInt64(2);
if (!reader.IsDBNull(3)) notice.AuthorId = reader.GetInt64(3);
if (!reader.IsDBNull(4)) notice.DateCreated = reader.GetDateTime(4);
if (!reader.IsDBNull(5)) notice.IsReaded = reader.GetBoolean(5);
if (!reader.IsDBNull(6)) notice.Type = reader.GetString(6);
if (!reader.IsDBNull(7)) notice.InstanceId = reader.GetString(7);
notice.MarkOld();
noticelist.Add(notice);
}
reader.Close();
}
return noticelist;
}
public Core.Business.Notice Select(Int64 id)
{
SqlServerUtility sql = new SqlServerUtility(connectionString);
sql.AddParameter("@Id", SqlDbType.BigInt, id);
SqlDataReader reader = sql.ExecuteSPReader("USP_Notice_Select_By_Id");
if (reader != null && !reader.IsClosed && reader.Read())
{
Core.Business.Notice notice = new Core.Business.Notice();
if (!reader.IsDBNull(0)) notice.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) notice.Content = reader.GetString(1);
if (!reader.IsDBNull(2)) notice.AccountId = reader.GetInt64(2);
if (!reader.IsDBNull(3)) notice.AuthorId = reader.GetInt64(3);
if (!reader.IsDBNull(4)) notice.DateCreated = reader.GetDateTime(4);
if (!reader.IsDBNull(5)) notice.IsReaded = reader.GetBoolean(5);
if (!reader.IsDBNull(6)) notice.Type = reader.GetString(6);
if (!reader.IsDBNull(7)) notice.InstanceId = reader.GetString(7);
reader.Close();
return notice;
}
else
{
if (reader != null && !reader.IsClosed)
reader.Close();
return null;
}
}
public List<CY.UME.Core.Business.Notice> GetTopNotice(string type, bool isReaded, CY.UME.Core.PagingInfo pageInfo)
{
List<Core.Business.Notice> noticelist = new List<Core.Business.Notice>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
#region ����
string tables = "Notice";
string pK = "Id";
string sort = "Id Desc";
int pageNum = pageInfo.CurrentPage;
int pageSize = pageInfo.PageSize;
string fields = "[Id],[Content],[AccountId],[AuthorId],[DateCreated],[IsReaded],[Type],[InstanceId]";
string isR = String.Empty;
if (isReaded)
{
isR = "1";
}
else
{
isR = "0";
}
string filter = "1=1 and [Type]='" + type + "' and [IsReaded]=" + isR;
string group = String.Empty;
#endregion
sql.AddParameter("@Tables", SqlDbType.VarChar, tables);
sql.AddParameter("@PK", SqlDbType.VarChar, pK);
sql.AddParameter("@Sort", SqlDbType.VarChar, sort);
sql.AddParameter("@PageNumber", SqlDbType.Int, pageNum);
sql.AddParameter("@PageSize", SqlDbType.Int, pageSize);
sql.AddParameter("@Fields", SqlDbType.VarChar, fields);
sql.AddParameter("@Filter", SqlDbType.VarChar, filter);
sql.AddParameter("@Group", SqlDbType.VarChar, group);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Notice notice = new Core.Business.Notice();
if (!reader.IsDBNull(0)) notice.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) notice.Content = reader.GetString(1);
if (!reader.IsDBNull(2)) notice.AccountId = reader.GetInt64(2);
if (!reader.IsDBNull(3)) notice.AuthorId = reader.GetInt64(3);
if (!reader.IsDBNull(4)) notice.DateCreated = reader.GetDateTime(4);
if (!reader.IsDBNull(5)) notice.IsReaded = reader.GetBoolean(5);
if (!reader.IsDBNull(6)) notice.Type = reader.GetString(6);
if (!reader.IsDBNull(7)) notice.InstanceId = reader.GetString(7);
notice.MarkOld();
noticelist.Add(notice);
}
reader.Close();
}
return noticelist;
}
public IList<CY.UME.Core.Business.Notice> GetNoticeGroupByType(CY.UME.Core.Business.Account currentAccount, string[] type, bool isReaded, Core.PagingInfo pageInfo)
{
IList<CY.UME.Core.Business.Notice> noticelist = new List<CY.UME.Core.Business.Notice>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
#region ����
string tables = "Notice";
string pK = "Type";
int pageNum = pageInfo.CurrentPage;
int pageSize = pageInfo.PageSize;
string fields = "";
if (type[0] == "friend")
{
fields = "[Type],[InstanceId],count(*) as Count";
}
else if (type[0] == "activeCheckNo")
{
fields = "[Type],[InstanceId],[Id],[Content]";
}
else if (type[0] == "activeCheck")
{
fields = "[Type],[InstanceId]";
}
else
{
fields = "[Type],[InstanceId],count(*) as Count";
}
string isR = String.Empty;
string strType = String.Empty;
if (type != null && type[0] != "activeCheckNo" && type[0] != "activeCheck")
{
strType += " and [Type] in (";
foreach (string s in type)
{
strType += "'" + s + "',";
}
strType = strType.Remove(strType.Length - 1, 1);
strType += ")";
}
else if (type[0] == "activeCheckNo")
{
strType = " and [Type] ='activeCheckNo' ";
}
else if (type[0] == "activeCheck")
{
strType = " and [Type]='activeCheck' ";
}
if (isReaded)
{
isR = " and [IsReaded]=1";
}
else
{
isR = " and [IsReaded]=0";
}
string filter = "1=1 " + isR + strType + " and [AccountId]=" + currentAccount.Id.ToString();
string group = "";
if (type[0] == "friend")
{
group = "[Type],[InstanceId]";
}
else if (type[0] == "activeCheckNo" || type[0] == "activeCheck")
{
group = "";
}
else
{
group = "[Type],[InstanceId]";
}
#endregion
sql.AddParameter("@Tables", SqlDbType.VarChar, tables);
sql.AddParameter("@PK", SqlDbType.VarChar, pK);
sql.AddParameter("@PageNumber", SqlDbType.Int, pageNum);
sql.AddParameter("@PageSize", SqlDbType.Int, pageSize);
sql.AddParameter("@Fields", SqlDbType.VarChar, fields);
sql.AddParameter("@Filter", SqlDbType.VarChar, filter);
sql.AddParameter("@Group", SqlDbType.VarChar, group);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Notice notice = new Core.Business.Notice();
if (type[0] == "friend")
{
if (!reader.IsDBNull(0)) notice.Type = reader.GetString(0);
if (!reader.IsDBNull(1)) notice.InstanceId = reader.GetString(1);
if (!reader.IsDBNull(2)) notice.Num = reader.GetInt32(2);
}
else if (type[0] == "activeCheckNo")
{
if (!reader.IsDBNull(0)) notice.Type = reader.GetString(0);
if (!reader.IsDBNull(1)) notice.InstanceId = reader.GetString(1);
if (!reader.IsDBNull(2)) notice.Id = reader.GetInt64(2);
if (!reader.IsDBNull(3)) notice.Content = reader.GetString(3);
}
else if (type[0] == "activeCheck")
{
if (!reader.IsDBNull(0)) notice.Type = reader.GetString(0);
if (!reader.IsDBNull(1)) notice.InstanceId = reader.GetString(1);
}
else
{
if (!reader.IsDBNull(0)) notice.Type = reader.GetString(0);
if (!reader.IsDBNull(1)) notice.InstanceId = reader.GetString(1);
if (!reader.IsDBNull(2)) notice.Num = reader.GetInt32(2);
}
notice.MarkOld();
noticelist.Add(notice);
}
reader.Close();
}
return noticelist;
}
public IList<CY.UME.Core.Business.Notice> GetNoticeByTypeAndDate(CY.UME.Core.Business.Account account, string[] type, bool? isReaded, CY.UME.Core.PagingInfo pageInfo)
{
IList<CY.UME.Core.Business.Notice> noticelist = new List<CY.UME.Core.Business.Notice>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
#region ����
string isR = String.Empty;
string strType = String.Empty;
if (isReaded != null)
{
if (isReaded == true)
{
isR = " and [IsReaded]=1";
}
else
{
isR = " and [IsReaded]=0";
}
}
if (type != null)
{
strType += " and [Type] in (";
foreach (string s in type)
{
strType += "'" + s + "',";
}
//strType = strType.Remove(strType.Length - 1, 1);
strType += "'0')";
}
if (string.IsNullOrEmpty(pageInfo.Temp))
{
strType += " and [Id] NOT IN (" + pageInfo.Temp + "0) ";
}
string filter = " 1=1 and [AccountId]=" + account.Id.ToString() + isR + strType;
#endregion
sql.AddParameter("@Tables", SqlDbType.VarChar, "Notice");
sql.AddParameter("@PK", SqlDbType.VarChar, "Id");
sql.AddParameter("@Sort", SqlDbType.VarChar, "Id Desc");
sql.AddParameter("@PageNumber", SqlDbType.Int, pageInfo.CurrentPage);
sql.AddParameter("@PageSize", SqlDbType.Int, pageInfo.PageSize);
sql.AddParameter("@Fields", SqlDbType.VarChar, "[Id],[Content],[AccountId],[AuthorId],[DateCreated],[IsReaded],[Type],[InstanceId]");
sql.AddParameter("@Filter", SqlDbType.VarChar, filter);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Notice notice = new Core.Business.Notice();
if (!reader.IsDBNull(0)) notice.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) notice.Content = reader.GetString(1);
if (!reader.IsDBNull(2)) notice.AccountId = reader.GetInt64(2);
if (!reader.IsDBNull(3)) notice.AuthorId = reader.GetInt64(3);
if (!reader.IsDBNull(4)) notice.DateCreated = reader.GetDateTime(4);
if (!reader.IsDBNull(5)) notice.IsReaded = reader.GetBoolean(5);
if (!reader.IsDBNull(6)) notice.Type = reader.GetString(6);
if (!reader.IsDBNull(7)) notice.InstanceId = reader.GetString(7);
notice.MarkOld();
noticelist.Add(notice);
}
reader.Close();
}
return noticelist;
}
public List<CY.UME.Core.Business.Notice> GetNoticeByAccountAndFriendAndType(CY.UME.Core.Business.Account account, CY.UME.Core.Business.Account author, string type, bool isReaded)
{
List<Core.Business.Notice> noticelist = new List<Core.Business.Notice>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
sql.AddParameter("@AccountId", SqlDbType.BigInt, account.Id);
sql.AddParameter("@AuthorId", SqlDbType.BigInt, author.Id);
sql.AddParameter("@Type", SqlDbType.NVarChar, type);
sql.AddParameter("@IsReaded", SqlDbType.Bit, isReaded);
SqlDataReader reader = sql.ExecuteSPReader("USP_Notice_Select_By_AccountId_And_AuthorId_And_Type");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Notice notice = new Core.Business.Notice();
long id = 0;
if (!reader.IsDBNull(0)) id = reader.GetInt64(0);
notice = CY.UME.Core.Business.Notice.Load(id);
noticelist.Add(notice);
}
reader.Close();
}
return noticelist;
}
// ֪ͨ���� ��̨����
public IList<Core.Business.Notice> GetAllNotice(String AccountName, String IsReaded, String Content, String Type, DateTime StartTime, DateTime EndTime, Core.PagingInfo pageInfo)
{
IList<CY.UME.Core.Business.Notice> noticelist = new List<CY.UME.Core.Business.Notice>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
String strFilter = SetStrFilter(AccountName, IsReaded, Content, Type, StartTime, EndTime);
sql.AddParameter("@Tables", SqlDbType.VarChar, "[Notice],[Account]");
sql.AddParameter("@PK", SqlDbType.VarChar, "Notice.Id");
sql.AddParameter("@Sort", SqlDbType.VarChar, "Notice.Id Desc");
sql.AddParameter("@PageNumber", SqlDbType.Int, pageInfo.CurrentPage);
sql.AddParameter("@PageSize", SqlDbType.Int, pageInfo.PageSize);
sql.AddParameter("@Fields", SqlDbType.VarChar, "[Notice].[Id],[Notice].[Content],[Notice].[AccountId],[Notice].[AuthorId],[Notice].[DateCreated],[Notice].[IsReaded],[Notice].[Type],[Notice].[InstanceId]");
sql.AddParameter("@Filter", SqlDbType.VarChar, strFilter);
sql.AddParameter("@Group", SqlDbType.VarChar, "");
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Notice notice = new Core.Business.Notice();
if (!reader.IsDBNull(0)) notice.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) notice.Content = reader.GetString(1);
if (!reader.IsDBNull(2)) notice.AccountId = reader.GetInt64(2);
if (!reader.IsDBNull(3)) notice.AuthorId = reader.GetInt64(3);
if (!reader.IsDBNull(4)) notice.DateCreated = reader.GetDateTime(4);
if (!reader.IsDBNull(5)) notice.IsReaded = reader.GetBoolean(5);
if (!reader.IsDBNull(6)) notice.Type = reader.GetString(6);
if (!reader.IsDBNull(7)) notice.InstanceId = reader.GetString(7);
notice.MarkOld();
noticelist.Add(notice);
}
reader.Close();
}
return noticelist;
}
public List<CY.UME.Core.Business.Notice> GetAllNotice(CY.UME.Core.Business.Account account, string type, bool isReaded)
{
List<Core.Business.Notice> noticelist = new List<Core.Business.Notice>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
sql.AddParameter("@AccountId", SqlDbType.BigInt, account.Id);
sql.AddParameter("@Type", SqlDbType.VarChar, type);
sql.AddParameter("@IsReaded", SqlDbType.Bit, isReaded);
SqlDataReader reader = sql.ExecuteSPReader("USP_Notice_SelectAll_By_AccountId_And_Type_And_IsReaded");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Notice notice = new Core.Business.Notice();
if (!reader.IsDBNull(0)) notice.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) notice.Content = reader.GetString(1);
if (!reader.IsDBNull(2)) notice.AccountId = reader.GetInt64(2);
if (!reader.IsDBNull(3)) notice.AuthorId = reader.GetInt64(3);
if (!reader.IsDBNull(4)) notice.DateCreated = reader.GetDateTime(4);
if (!reader.IsDBNull(5)) notice.IsReaded = reader.GetBoolean(5);
if (!reader.IsDBNull(6)) notice.Type = reader.GetString(6);
if (!reader.IsDBNull(7)) notice.InstanceId = reader.GetString(7);
notice.MarkOld();
noticelist.Add(notice);
}
reader.Close();
}
return noticelist;
}
