public IList<CY.UME.Core.Business.Album> GetAlbumByPagesAndAccountId(CY.UME.Core.Business.Account account, PagingInfo pageInfo, int viewPermission)
{
IList<Core.Business.Album> albumlist = new List<Core.Business.Album>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
StringBuilder sbFilter = new StringBuilder();
sbFilter.Append(" [AccountId] = ");
sbFilter.Append(account.Id);
string permission = String.Empty;
if (viewPermission == 0)//�Լ�����
{
;
}
else if (viewPermission == 1)//���ѷ���
{
permission = "(0,1,2)";
}
else if (viewPermission == 2)//İ���˷���
{
permission = "(0,2)";
}
else
{
return albumlist;
}
if (permission.Length != 0)
{
sbFilter.Append(" And ViewPermission in " + permission);
}
sql.AddParameter("@Tables", SqlDbType.VarChar, "Album");
sql.AddParameter("@PK", SqlDbType.VarChar, "Id");
sql.AddParameter("@Filter", SqlDbType.VarChar, sbFilter.ToString());
sql.AddParameter("@Fields", SqlDbType.VarChar, "[Id],[AccountId],[ViewPermission],[DateCreated],[LastModifiedTime],[IsAvatar],[Name],[CoverPath],[ViewPassword]");
sql.AddParameter("@PageNumber", SqlDbType.Int, pageInfo.CurrentPage);
sql.AddParameter("@PageSize", SqlDbType.Int, pageInfo.PageSize);
sql.AddParameter("@sort", SqlDbType.NVarChar, "Id DESC");
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Album album = new Core.Business.Album();
long id = reader.GetInt64(0);
album = Core.Business.Album.Load(id);
album.MarkOld();
albumlist.Add(album);
}
reader.Close();
}
return albumlist;
}
public IList<CY.UME.Core.Business.Recommend> GetRecommendByType_Page(PagingInfo pinfo,string type)
{
IList<Core.Business.Recommend> recommendlist = new List<Core.Business.Recommend>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
#region 参数
string tables = "Recommend";
string pK = "Id";
string sort = "RecommendNumber Desc";
int pageNum = 0;
int pageSize = int.MaxValue;
if (pinfo != null)
{
pageNum = pinfo.CurrentPage;
pageSize = pinfo.PageSize;
}
string fields = "*";
string filter = string.Empty;
if (type != string.Empty)
{
filter = "Type='" + type+"'";
}
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.Recommend recommend = new Core.Business.Recommend();
long id = reader.GetInt64(0);
recommend = Core.Business.Recommend.Load(id);
recommend.MarkOld();
recommendlist.Add(recommend);
}
reader.Close();
}
return recommendlist;
}
/// <summary>
/// ��ȡ���ظ��б�
/// </summary>
/// <param name="active"></param>
/// <param name="pageInfo"></param>
/// <returns></returns>
public List<TopicReply> GetActiveTopicReplyList(Activities active, PagingInfo pageInfo)
{
List<Core.Business.TopicReply> topicReplylist = new List<Core.Business.TopicReply>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
string sorts = String.Empty;
string filter = String.Empty;
string fields = String.Empty;
if (active == null)
{
return topicReplylist;
}
sorts = "DateCreated desc";
filter = "[InstanceId] in (select [Id] from TopicExtend where InstanceId='" +active.Id + "' and [Type]='active')";
fields = "[Id],[DateCreated],[InstanceId],[AccountId],[AuthorId],[ReferedId],[Content],[IP]";
sql.AddParameter("@PageNumber", SqlDbType.Int, pageInfo.CurrentPage);
sql.AddParameter("@PageSize", SqlDbType.Int, pageInfo.PageSize);
sql.AddParameter("@Tables", SqlDbType.NVarChar, "TopicReply");
sql.AddParameter("@PK", SqlDbType.NVarChar, "Id");
sql.AddParameter("@Sort", SqlDbType.NVarChar, sorts);
sql.AddParameter("@Fields", SqlDbType.NVarChar, fields);
sql.AddParameter("@Filter", SqlDbType.NVarChar, filter);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.TopicReply topicReply = new Core.Business.TopicReply();
if (!reader.IsDBNull(0)) topicReply.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) topicReply.DateCreated = reader.GetDateTime(1);
if (!reader.IsDBNull(2)) topicReply.InstanceId = reader.GetGuid(2);
if (!reader.IsDBNull(3)) topicReply.AccountId = reader.GetInt64(3);
if (!reader.IsDBNull(4)) topicReply.AuthorId = reader.GetInt64(4);
if (!reader.IsDBNull(5)) topicReply.ReferedId = reader.GetInt64(5);
if (!reader.IsDBNull(6)) topicReply.Content = reader.GetString(6);
if (!reader.IsDBNull(7)) topicReply.IP = reader.GetString(7);
topicReply.MarkOld();
topicReplylist.Add(topicReply);
}
reader.Close();
}
return topicReplylist;
}
public IList<Account> GetAccountBindedPhoneByGroup(Group sgroup, PagingInfo pageInfo)
{
IList<Core.Business.Account> accountlist = new List<Core.Business.Account>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
#region ����
string tables = "Account";
string pK = "Id";
string sort = "Id Desc";
int pageNum = pageInfo.CurrentPage;
int pageSize = pageInfo.PageSize;
string fields = "[Id],[Credit],[DateCreated],[IsProtected],[HasAvatar],[IsTeacher],[IsPublic],[ViewNumber],[Name],[NickName],[Code]";
string filter = "[Id] in (Select AccountId From AccountGroup Where GroupId=" + sgroup.Id.ToString() + ")";
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.Account account = new Core.Business.Account();
long id = reader.GetInt64(0);
account = Core.Business.Account.Load(id);
account.MarkOld();
accountlist.Add(account);
}
reader.Close();
}
return accountlist;
}
//根据作者Id，类型，是否已读查询邮件
public IList<Core.Business.Messages> GetMessagesByAccountIdOrAuthorId(CY.UME.Core.Business.Messages messages, PagingInfo pageInfo, String Sorts)
{
IList<Core.Business.Messages> messageslist = new List<Core.Business.Messages>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
string filter = SetStrFilter(messages);
if (Sorts.Length <= 0)
Sorts = "Id DESC";
sql.AddParameter("@PageNumber", SqlDbType.Int, pageInfo.CurrentPage);
sql.AddParameter("@PageSize", SqlDbType.Int, pageInfo.PageSize);
sql.AddParameter("@Tables", SqlDbType.NVarChar, "Messages");
sql.AddParameter("@PK", SqlDbType.NVarChar, "Id");
sql.AddParameter("@Sort", SqlDbType.NVarChar, Sorts);
sql.AddParameter("@Fields", SqlDbType.NVarChar, "[Id],[Content],[Type],[DateCreated],[SendDate],[IsRead],[AuthorId],[AccountId],[Title],[Attachments],[AuthorName],[AccountName]");
sql.AddParameter("@Filter", SqlDbType.NVarChar, filter);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Messages message = new Core.Business.Messages();
if (!reader.IsDBNull(0)) message.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) message.Content = reader.GetString(1);
if (!reader.IsDBNull(2)) message.Type = reader.GetInt32(2);
if (!reader.IsDBNull(3)) message.DateCreated = reader.GetDateTime(3);
if (!reader.IsDBNull(4)) message.SendDate = reader.GetDateTime(4);
if (!reader.IsDBNull(5)) message.IsRead = reader.GetInt32(5);
if (!reader.IsDBNull(6)) message.AuthorId = reader.GetInt64(6);
if (!reader.IsDBNull(7)) message.AccountId = reader.GetInt64(7);
if (!reader.IsDBNull(8)) message.Title = reader.GetString(8);
if (!reader.IsDBNull(9)) message.Attachments = reader.GetString(9);
if (!reader.IsDBNull(10)) message.AuthorName = reader.GetString(10);
if (!reader.IsDBNull(11)) message.AccountName = reader.GetString(11);
message.MarkOld();
messageslist.Add(message);
}
reader.Close();
}
return messageslist;
}
public IList<CY.UME.Core.Business.Visitor> GetAllVisitorByAccountId(long userId, PagingInfo pagingInfo)
{
IList<Core.Business.Visitor> visitorList = new List<Core.Business.Visitor>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
#region ²ÎÊý
string tables = "Visitor";
string pK = "Id";
string sort = "VisiteDate Desc";
int pageNum = pagingInfo.CurrentPage;
int pageSize = pagingInfo.PageSize;
string fields = "[Id], [AccountId],[VisitorId],[VisiteDate],[IP]";
string filter = "AccountId = " + userId;
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.Visitor visitor = new Core.Business.Visitor();
if (!reader.IsDBNull(0)) visitor.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) visitor.AccountId = reader.GetInt64(1);
if (!reader.IsDBNull(2)) visitor.VisitorId = reader.GetInt64(2);
if (!reader.IsDBNull(3)) visitor.VisiteDate = reader.GetDateTime(3);
if (!reader.IsDBNull(5)) visitor.IP = reader.GetString(4);
visitor.MarkOld();
visitorList.Add(visitor);
}
reader.Close();
}
return visitorList;
}
private static readonly ILog log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); //
#endregion Fields
#region Methods
/// <summary>
/// 绑定
/// </summary>
/// <param name="CurrentPage"></param>
/// <param name="PageSize"></param>
public void bind(AccountSearchJson json, string IsShow, string Sort, bool isTrue)
{
PagingInfo pagingInfo = new PagingInfo();
pagingInfo.CurrentPage = AspNetPager1.CurrentPageIndex;
pagingInfo.PageSize = AspNetPager1.PageSize = SetCurrentPageSize.CurrentPageSize;
List<CY.UME.Core.Business.Account> accountList = CY.UME.Core.Business.Account.GetIsShowAccounts(json, CY.Utility.Common.ConvertUtility.ConvertToInt(IsShow, -1), Sort, pagingInfo);
List<Accountplus> accountplus = new List<Accountplus>();
for (int i = 0; i < accountList.Count; i++)
{
Accountplus tempaccountplus = new Accountplus();
tempaccountplus.Id = accountList[i].Id;
tempaccountplus.Code = accountList[i].Code;
tempaccountplus.Name = accountList[i].Name;
tempaccountplus.NickName = accountList[i].NickName;
tempaccountplus.DateCreated = accountList[i].DateCreated;
tempaccountplus.IsProtected = accountList[i].IsProtected;
tempaccountplus.HasAvatar = accountList[i].HasAvatar;
tempaccountplus.ViewNumber = accountList[i].ViewNumber;
tempaccountplus.IsTeacher = accountList[i].IsTeacher;
tempaccountplus.IsPublic = accountList[i].IsPublic;
tempaccountplus.Credit = accountList[i].Credit;
tempaccountplus.Remark = accountList[i].Remark;
tempaccountplus.FriendCount = accountList[i].FriendCount;
if (CY.UME.Core.Business.Recommend.IsRecommend(accountList[i].Id.ToString(), "user"))
{
tempaccountplus.Oper = "decommend";
}
else
{
tempaccountplus.Oper = "recommend";
}
accountplus.Add(tempaccountplus);
}
if (accountplus.Count >= 1)
{
if (isTrue)
{
AspNetPager1.RecordCount = CY.UME.Core.Business.Account.GetIsShowAccountCount(json, CY.Utility.Common.ConvertUtility.ConvertToInt(IsShow, -1));//绑定总条数 GetAccountsCount
}
//绑定用户
AspNetPager1.Visible = true;
Repeater2.Visible = false;
Repeater1.Visible = true;
Repeater1.DataSourceID = "";
Repeater1.DataSource = accountplus;
Repeater1.DataBind();
}
else
{
Repeater1.Visible = false;
Repeater2.Visible = true;
Repeater2.DataSourceID = "";
Repeater2.DataSource = BindNoData("暂无数据");
Repeater2.DataBind();
AspNetPager1.Visible = false;
}
}
public List<CY.UME.Core.Business.Topic> SearchTopices(string InstanceId, string topicExtendType, string authorName, string title, int? minViewNum, int? maxViewNum, int? minReplyNum, int? maxReplyNum, DateTime? minPubDate, DateTime? maxPubDate, int? level, int? type, PagingInfo pageInfo)
{
List<Core.Business.Topic> topiclist = new List<Core.Business.Topic>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
string filter = String.Empty;
string fields = String.Empty;
filter += "1=1";
bool topicExtendCheck = false;
if (InstanceId.Length > 0 || topicExtendType.Length > 0)
{
topicExtendCheck = true;
}
if (topicExtendCheck)
{
filter += " and [Id] in (Select [Id] FROM [TopicExtend] Where 1=1";
}
if (InstanceId.Length > 0)
{
filter += " and InstanceId = '" + InstanceId + "'";
}
if (topicExtendType.Length > 0)
{
filter += " and [Type]='" + topicExtendType + "'";
}
if (topicExtendCheck)
{
filter += ")";
}
bool accountCheck = false;
if (authorName.Length > 0)
{
accountCheck = true;
}
if (accountCheck)
{
filter += " and AccountId in (select [Id] From Account Where 1=1";
}
if (authorName.Length > 0)
{
filter += " and [Name] like '%" + authorName + "%'";
}
if (accountCheck)
{
filter += ")";
}
if (title.Length > 0)
{
filter += " and [Title] like '%" + title + "%'";
}
if (minViewNum != null)
{
filter += " and [ViewNum] >= " + minViewNum.ToString();
}
if (maxViewNum != null)
{
filter += " and [ViewNum] <= " + minViewNum.ToString();
}
if (minReplyNum != null)
{
filter += " and [ReplyNum] >= " + minReplyNum.ToString();
}
if (maxReplyNum != null)
{
filter += " and [ReplyNum] <= " + maxReplyNum.ToString();
}
if (minPubDate != null)
{
filter += " and [DateCreated] >= '" + minPubDate.ToString() + "'";
}
if (maxPubDate != null)
{
filter += " and [DateCreated] <= '" + maxPubDate.ToString() + "'";
}
if (level != null)
{
filter += " and [Level] =" + level.ToString();
}
if (type != null)
{
filter += " and [type]=" + type.ToString();
}
fields = "[Id], [Content],[ViewNum],[ReplyNum],[TypeId],[DateCreated],[LastReplyDate],[AccountId],[LastAuthorId],[Title],[IP],[Level]";
sql.AddParameter("@PageNumber", SqlDbType.Int, pageInfo.CurrentPage);
sql.AddParameter("@PageSize", SqlDbType.Int, pageInfo.PageSize);
sql.AddParameter("@Tables", SqlDbType.NVarChar, "Topic");
sql.AddParameter("@PK", SqlDbType.NVarChar, "Id");
sql.AddParameter("@Sort", SqlDbType.NVarChar, "LastReplyDate desc");
sql.AddParameter("@Fields", SqlDbType.NVarChar, fields);
sql.AddParameter("@Filter", SqlDbType.NVarChar, filter);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Topic topic = new Core.Business.Topic();
if (!reader.IsDBNull(0)) topic.Id = reader.GetGuid(0);
if (!reader.IsDBNull(1)) topic.Content = reader.GetString(1);
if (!reader.IsDBNull(2)) topic.ViewNum = reader.GetInt32(2);
if (!reader.IsDBNull(3)) topic.ReplyNum = reader.GetInt32(3);
if (!reader.IsDBNull(4)) topic.TypeId = reader.GetInt32(4);
if (!reader.IsDBNull(5)) topic.DateCreated = reader.GetDateTime(5);
if (!reader.IsDBNull(6)) topic.LastReplyDate = reader.GetDateTime(6);
if (!reader.IsDBNull(7)) topic.AccountId = reader.GetInt64(7);
if (!reader.IsDBNull(8)) topic.LastAuthorId = reader.GetInt64(8);
if (!reader.IsDBNull(9)) topic.Title = reader.GetString(9);
if (!reader.IsDBNull(10)) topic.IP = reader.GetString(10);
if (!reader.IsDBNull(11)) topic.Level = reader.GetInt32(11);
topic.MarkOld();
topiclist.Add(topic);
}
reader.Close();
}
return topiclist;
}
/// <summary>
/// �õ���ע�ҵ���
/// </summary>
/// <param name="uid"></param>
/// <param name="pinfo"></param>
/// <returns></returns>
public IList<Account> GetFollowerAccountByCurrentAccount(long uid, PagingInfo pinfo, string oderby)
{
IList<Core.Business.Account> accountlist = new List<Core.Business.Account>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
#region ����
string tables = "Account INNER JOIN AccountFollower ON Account.Id= AccountFollower.FollowerId";
string pK = "Account.Id";
string sort = "";
if (oderby == "")
{
sort = "Account.Id Desc";
}
else if (oderby == "newcear")
{
sort = "AccountFollower.DataCreated Desc";
}
else if (oderby == "name")
{
sort = "Account.Name";
}
int pageNum = pinfo.CurrentPage;
int pageSize = pinfo.PageSize;
string fields = "Account.Id,Account.Name";
string filter = " AccountFollower.AccountId=" + uid;
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.Account account = new Core.Business.Account();
long id = reader.GetInt64(0);
account = Core.Business.Account.Load(id);
account.MarkOld();
accountlist.Add(account);
}
reader.Close();
}
return accountlist;
}
public IList<CY.UME.Core.Business.TopicReply> GetReplyByTopicIdAndPagesDESC(Guid topicId, PagingInfo pageInfo)
{
IList<Core.Business.TopicReply> topicReplylist = new List<Core.Business.TopicReply>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
string sorts = String.Empty;
string filter = String.Empty;
string fields = String.Empty;
sorts = "Id";
filter = "Id IN(SELECT TOP (" + pageInfo.PageSize + ") [Id] FROM [TopicReply] WHERE [InstanceId] = '" + topicId.ToString() + "' ORDER BY [DateCreated])";
fields = "[Id],[DateCreated],[InstanceId],[AccountId],[AuthorId],[ReferedId],[Content],[IP]";
sql.AddParameter("@PageNumber", SqlDbType.Int, pageInfo.CurrentPage);
sql.AddParameter("@PageSize", SqlDbType.Int, pageInfo.PageSize);
sql.AddParameter("@Tables", SqlDbType.NVarChar, "TopicReply");
sql.AddParameter("@PK", SqlDbType.NVarChar, "Id");
sql.AddParameter("@Sort", SqlDbType.NVarChar, sorts);
sql.AddParameter("@Fields", SqlDbType.NVarChar, fields);
sql.AddParameter("@Filter", SqlDbType.NVarChar, filter);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.TopicReply topicReply = new Core.Business.TopicReply();
if (!reader.IsDBNull(0)) topicReply.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) topicReply.DateCreated = reader.GetDateTime(1);
if (!reader.IsDBNull(2)) topicReply.InstanceId = reader.GetGuid(2);
if (!reader.IsDBNull(3)) topicReply.AccountId = reader.GetInt64(3);
if (!reader.IsDBNull(4)) topicReply.AuthorId = reader.GetInt64(4);
if (!reader.IsDBNull(5)) topicReply.ReferedId = reader.GetInt64(5);
if (!reader.IsDBNull(6)) topicReply.Content = reader.GetString(6);
if (!reader.IsDBNull(7)) topicReply.IP = reader.GetString(7);
topicReply.MarkOld();
topicReplylist.Add(topicReply);
}
reader.Close();
}
return topicReplylist;
}
public IList<Account> GetAccountSameInterestByCurrentUserId(long Currentid, PagingInfo pageInfo)
{
IList<Core.Business.Account> accountlist = new List<Core.Business.Account>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
#region ����
string tables = "view_account_all";
string pK = "Id";
string sort = "Id Desc";
int pageNum = pageInfo.CurrentPage;
int pageSize = pageInfo.PageSize;
//string fields = "";
string filter = "";
Core.Business.AccountExtend ace = Core.Business.AccountExtend.Load(Currentid);
if (ace != null && !string.IsNullOrEmpty(ace.Interest))
{
string interestAll = ace.Interest;
string sqlWhere = " Id!=" + Currentid + " and ";
if (interestAll.Contains(","))
{
string[] interestTemp = interestAll.Split(',');
if (interestTemp != null && interestTemp.Length > 0)
{
sqlWhere += "( ";
for (int i = 0; i < interestTemp.Length; i++)
{
if (i == interestTemp.Length - 1)
{
sqlWhere += " interest like '%" + interestTemp[i] + "%' ";
}
else
{
sqlWhere += " interest like '%" + interestTemp[i] + "%' or ";
}
}
sqlWhere += " ) ";
filter = sqlWhere;
}
}
else
{
filter = sqlWhere + "interest like '%" + interestAll + "%'";
}
}
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.Account account = new Core.Business.Account();
long id = reader.GetInt64(0);
account = Core.Business.Account.Load(id);
account.MarkOld();
accountlist.Add(account);
}
reader.Close();
}
return accountlist;
}
// ��̨����
public List<Account> GetAccounts(
string name,
int gender,
string bloodType,
int birthYear,
int birthMonth,
int birthDate,
City hometownCity,
Province homwtownProvince,
City resideCity,
Province resideProvince,
ClassInfo classInfo,
Grade grade,
Major major,
College college,
University university,
string code,
bool? hasAvatar,
string nickName,
bool? isProtected,
int minViewCount,
int maxViewCount,
bool? isPublic,
string Sort,
PagingInfo pagingInfo)
{
List<Account> accountlist = new List<Account>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
System.Text.StringBuilder sbFilter = new StringBuilder();
#region Build filter
sbFilter.Append("1=1 ");
if (!string.IsNullOrEmpty(name))
{
sbFilter.Append(" And [Name] like '%");
sbFilter.Append(name.Replace("'", "").Replace("\"", ""));
sbFilter.Append("%'");
}
if (gender == 1 || gender == 0)
{
sbFilter.Append(" And [Gender] = ");
sbFilter.Append(gender);
}
if (!string.IsNullOrEmpty(bloodType))
{
sbFilter.Append(" And BloodType = '");
sbFilter.Append(bloodType.Replace("'", "").Replace("\"", ""));
sbFilter.Append(" '");
}
if (birthYear > 0)
{
sbFilter.Append(" And BirthYear = ");
sbFilter.Append(birthYear);
}
if (birthMonth > 0 && birthMonth < 13)
{
sbFilter.Append(" And BirthMonth = ");
sbFilter.Append(birthMonth);
}
if (birthDate > 0 && birthDate < 32)
{
sbFilter.Append(" And BirthDate = ");
sbFilter.Append(birthDate);
}
if (hometownCity != null)
{
if (hometownCity.Id > 0)
{
sbFilter.Append(" And HometownCityId = ");
sbFilter.Append(hometownCity.Id);
}
}
else
{
if (homwtownProvince != null && homwtownProvince.Id > 0)
{
sbFilter.Append(" And HometownProvinceId = ");
sbFilter.Append(homwtownProvince.Id);
}
}
if (resideCity != null)
{
if (resideCity.Id > 0)
{
sbFilter.Append(" And ResideCityId = ");
sbFilter.Append(resideCity.Id);
}
}
else
{
if (resideProvince != null && resideProvince.Id > 0)
{
sbFilter.Append(" And ResideProvinceId = ");
sbFilter.Append(resideProvince.Id);
}
}
if (classInfo != null)
{
if (classInfo.Id > 0)
{
sbFilter.Append(" And ClassInfoId = ");
sbFilter.Append(classInfo.Id);
}
}
else
{
if (grade != null && grade.Id > 0)
{
sbFilter.Append(" And GradeId = ");
sbFilter.Append(grade.Id);
}
if (major != null)
{
if (major.Id > 0)
{
sbFilter.Append(" And MajorId = ");
sbFilter.Append(major.Id);
}
}
else
{
if (college != null)
{
if (college.Id > 0)
{
sbFilter.Append(" And CollegeId = ");
sbFilter.Append(college.Id);
}
}
else
{
if (university != null && university.Id > 0)
{
sbFilter.Append(" And UniversityID = ");
sbFilter.Append(university.Id);
}
}
}
}
if (!string.IsNullOrEmpty(code))
{
sbFilter.Append(" And Code = '");
sbFilter.Append(code.Replace("'", "").Replace("\"", ""));
sbFilter.Append("'");
}
if (hasAvatar.HasValue)
{
sbFilter.Append(string.Format(" And HasAvatar = '{0}'", hasAvatar.Value ? "1" : "0"));
}
if (!string.IsNullOrEmpty(nickName))
{
sbFilter.Append(" And NickName = '");
sbFilter.Append(nickName.Replace("'", "").Replace("\"", ""));
sbFilter.Append("'");
}
if (isProtected.HasValue)
{
sbFilter.Append(string.Format(" And IsProtected = '{0}'", isProtected.Value ? "1" : "0"));
}
if ((minViewCount > 0) &&
(maxViewCount > 0) &&
(minViewCount < maxViewCount))
{
sbFilter.Append(string.Format(" And ViewNumber Between {0} and {1} ", minViewCount, maxViewCount));
}
if (isPublic.HasValue)
{
sbFilter.Append(" And IsPublic = '");
sbFilter.Append(isPublic.Value ? "1" : "0");
sbFilter.Append("'");
}
//ͨ��������֤�����û�
sbFilter.Append(" And IsEmailChecked='1'");
#endregion
#region BackCode
/*
int pageSize = int.MaxValue;
int pageNumber = 1;
if (pagingInfo != null)
{
pageSize = pagingInfo.PageSize;
pageNumber = pagingInfo.CurrentPage;
}
String Fields1 = "Id,ViewNumber,DateCreated,IsProtected,HasAvatar,IsTeacher,Name,NickName,Code,Credit,IsPublic,BirthYear,BirthMonth," +
"BirthDate,Gender,LoveState,HometownProvinceId,HometownCityId,ResideProvinceId,ResideCityId,Birthday,IsEmailChecked," +
"Telephone,Email,QQ,MSN,BloodType,Theme,LibUserId,Password,Interest,MajorId,MajorName,CollegeId,CollegeName,UniversityID,UniversityName,GradeId,GradeYear," +
"HometownProvinceName,HometownCityName,ResideProvinceName,ResideCityName,IsShow,FriendCount";
String Fields2 = "Id,Credit,DateCreated,IsProtected,HasAvatar,IsTeacher,IsPublic,ViewNumber,Name,NickName,Code,FriendCount";
sql.AddParameter("@Tables", SqlDbType.VarChar, "view_account_all");
sql.AddParameter("@PK", SqlDbType.VarChar, "Id");
sql.AddParameter("@Fields", SqlDbType.VarChar, Fields2);//"Id"
sql.AddParameter("@sort", SqlDbType.NVarChar, Sort + " DESC");
sql.AddParameter("@Filter", SqlDbType.VarChar, sbFilter.ToString());
sql.AddParameter("@PageNumber", SqlDbType.Int, pageNumber);
sql.AddParameter("@PageSize", SqlDbType.Int, pageSize);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
*/
#endregion
int pageSize = int.MaxValue;
int pageNumber = 1;
if (pagingInfo != null)
{
pageSize = pagingInfo.PageSize;
pageNumber = pagingInfo.CurrentPage;
}
sql.AddParameter("@PageNum", SqlDbType.Int, pageSize);
sql.AddParameter("@Num", SqlDbType.Int, pageSize * (pageNumber - 1));
String Column = "Id,Credit,DateCreated,IsProtected,HasAvatar,IsTeacher,IsPublic,ViewNumber,Name,NickName,Code,(SELECT COUNT(Friendship.Id) FROM Friendship WHERE (Friendship.AccountId = view_account_all.Id) AND (Friendship.IsChecked = 1)) AS FriendCount";
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append("SELECT TOP (@PageNum) " + Column + " FROM view_account_all WHERE " + sbFilter.ToString() + " AND [Id] NOT IN ");
sbSQL.Append("(SELECT TOP (@Num) [Id] FROM view_account_all WHERE " + sbFilter.ToString() + " ORDER BY " + Sort + " DESC) ORDER BY " + Sort + " DESC");
SqlDataReader reader = sql.ExecuteSqlReader(sbSQL.ToString());
if (reader != null)
{
while (reader.Read())
{
Core.Business.Account account = new Core.Business.Account();
if (!reader.IsDBNull(0)) account.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) account.Credit = reader.GetInt32(1);
if (!reader.IsDBNull(2)) account.DateCreated = reader.GetDateTime(2);
if (!reader.IsDBNull(3)) account.IsProtected = reader.GetBoolean(3);
if (!reader.IsDBNull(4)) account.HasAvatar = reader.GetBoolean(4);
if (!reader.IsDBNull(5)) account.IsTeacher = reader.GetBoolean(5);
if (!reader.IsDBNull(6)) account.IsPublic = reader.GetBoolean(6);
if (!reader.IsDBNull(7)) account.ViewNumber = reader.GetInt64(7);
if (!reader.IsDBNull(8)) account.Name = reader.GetString(8);
if (!reader.IsDBNull(9)) account.NickName = reader.GetString(9);
if (!reader.IsDBNull(10)) account.Code = reader.GetString(10);
if (!reader.IsDBNull(11)) account.FriendCount = reader.GetInt32(11);
account.MarkOld();
accountlist.Add(account);
}
reader.Close();
}
return accountlist;
}
public List<Account> GetAccounts(
string name,
int gender,
string bloodType,
int birthYear,
int birthMonth,
int birthDate,
City hometownCity,
Province homwtownProvince,
City resideCity,
Province resideProvince,
ClassInfo classInfo,
Grade grade,
Major major,
College college,
University university,
string code,
bool? hasAvatar,
string nickName,
bool? isProtected,
int minViewCount,
int maxViewCount,
bool? isPublic,
int minYear,
int maxYear,
string interest,
PagingInfo pagingInfo)
{
List<Account> accounts = new List<Account>();
System.Text.StringBuilder sbFilter = new StringBuilder();
#region Build filter
sbFilter.Append("1=1 ");
if (!string.IsNullOrEmpty(name))
{
sbFilter.Append(" And [Name] like '%");
sbFilter.Append(name.Replace("'", "").Replace("\"", ""));
sbFilter.Append("%'");
}
if (gender == 1 || gender == 0)
{
sbFilter.Append(" And [Gender] = ");
sbFilter.Append(gender);
}
if (!string.IsNullOrEmpty(bloodType))
{
sbFilter.Append(" And BloodType = '");
sbFilter.Append(bloodType.Replace("'", "").Replace("\"", ""));
sbFilter.Append(" '");
}
if (birthYear > 0)
{
sbFilter.Append(" And BirthYear = ");
sbFilter.Append(birthYear);
}
if (birthMonth > 0 && birthMonth < 13)
{
sbFilter.Append(" And BirthMonth = ");
sbFilter.Append(birthMonth);
}
if (birthDate > 0 && birthDate < 32)
{
sbFilter.Append(" And BirthDate = ");
sbFilter.Append(birthDate);
}
if (hometownCity != null)
{
if (hometownCity.Id > 0)
{
sbFilter.Append(" And HometownCityId = ");
sbFilter.Append(hometownCity.Id);
}
}
else
{
if (homwtownProvince != null && homwtownProvince.Id > 0)
{
sbFilter.Append(" And HometownProvinceId = ");
sbFilter.Append(homwtownProvince.Id);
}
}
if (resideCity != null)
{
if (resideCity.Id > 0)
{
sbFilter.Append(" And ResideCityId = ");
sbFilter.Append(resideCity.Id);
}
}
else
{
if (resideProvince != null && resideProvince.Id > 0)
{
sbFilter.Append(" And ResideProvinceId = ");
sbFilter.Append(resideProvince.Id);
}
}
if (classInfo != null)
{
if (classInfo.Id > 0)
{
sbFilter.Append(" And ClassInfoId = ");
sbFilter.Append(classInfo.Id);
}
}
else
{
if (grade != null && grade.Id > 0)
{
sbFilter.Append(" And GradeId = ");
sbFilter.Append(grade.Id);
}
if (major != null)
{
if (major.Id > 0)
{
sbFilter.Append(" And MajorId = ");
sbFilter.Append(major.Id);
}
}
else
{
if (college != null)
{
if (college.Id > 0)
{
sbFilter.Append(" And CollegeId = ");
sbFilter.Append(college.Id);
}
}
else
{
if (university != null && university.Id > 0)
{
sbFilter.Append(" And UniversityID = ");
sbFilter.Append(university.Id);
}
}
}
}
if (!string.IsNullOrEmpty(code))
{
sbFilter.Append(" And Code = '");
sbFilter.Append(code.Replace("'", "").Replace("\"", ""));
sbFilter.Append("'");
}
if (hasAvatar.HasValue)
{
sbFilter.Append(string.Format(" And HasAvatar = '{0}'", hasAvatar.Value ? "1" : "0"));
}
if (!string.IsNullOrEmpty(nickName))
{
sbFilter.Append(" And NickName = '");
sbFilter.Append(nickName.Replace("'", "").Replace("\"", ""));
sbFilter.Append("'");
}
if (isProtected.HasValue)
{
sbFilter.Append(string.Format(" And IsProtected = '{0}'", isProtected.Value ? "1" : "0"));
}
if ((minViewCount > 0) &&
(maxViewCount > 0) &&
(minViewCount < maxViewCount))
{
sbFilter.Append(string.Format(" And ViewNumber Between {0} and {1} ", minViewCount, maxViewCount));
}
if (isPublic.HasValue)
{
sbFilter.Append(" And IsPublic = '");
sbFilter.Append(isPublic.Value ? "1" : "0");
sbFilter.Append("'");
}
if (minYear != 0 && maxYear != 0)
{
sbFilter.Append(string.Format(" And BirthYear Between {0} and {1} ", minYear, maxYear));
}
if (interest != string.Empty)
{
sbFilter.Append(string.Format(" And Interest like '%{0}%' ", interest));
}
//ͨ��������֤�����û�
sbFilter.Append(" And IsEmailChecked='1'");//������ AND [IsShow] = 0
#endregion
int pageSize = int.MaxValue;
int pageNumber = 1;
if (pagingInfo != null)
{
pageSize = pagingInfo.PageSize;
pageNumber = pagingInfo.CurrentPage;
}
SqlServerUtility sql = new SqlServerUtility(connectionString);
sql.AddParameter("@Tables", SqlDbType.VarChar, "view_account_all");
sql.AddParameter("@PK", SqlDbType.VarChar, "Id");
sql.AddParameter("@Fields", SqlDbType.VarChar, "Id");
sql.AddParameter("@sort", SqlDbType.NVarChar, "Id DESC");
sql.AddParameter("@Filter", SqlDbType.VarChar, sbFilter.ToString());
sql.AddParameter("@PageNumber", SqlDbType.Int, pageNumber);
sql.AddParameter("@PageSize", SqlDbType.Int, pageSize);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
long id = reader.GetInt64(0);
Account account = Account.Load(id);
accounts.Add(account);
}
reader.Close();
}
return accounts;
}
public IList<Account> GetStudentByCurrentUserId(long Currentid, PagingInfo pageInfo)
{
IList<Core.Business.Account> accountlist = new List<Core.Business.Account>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
#region ����
string tables = "view_account_all";
string pK = "Id";
string sort = "Id Desc";
int pageNum = pageInfo.CurrentPage;
int pageSize = pageInfo.PageSize;
string filter = "";
Core.Business.AccountExtend ace = Core.Business.AccountExtend.Load(Currentid);
if (ace != null)
{
string sqlwhere = " Id<>" + Currentid;
if (ace.CollegeId != -1 || ace.UniversityId != -1)
{
sqlwhere += " and ";
if (ace.CollegeId != -1)
{
sqlwhere += " CollegeId=" + ace.CollegeId;
}
else if (ace.UniversityId != -1)
{
sqlwhere += " UniversityId=" + ace.UniversityId;
}
}
filter = sqlwhere;
}
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.Account account = new Core.Business.Account();
long id = reader.GetInt64(0);
account = Core.Business.Account.Load(id);
account.MarkOld();
accountlist.Add(account);
}
reader.Close();
}
return accountlist;
}
public IList<Account> GetAllFriendshipByOder(long uid, PagingInfo pinfotemp, string oderby, int divernum)
{
IList<Core.Business.Account> accountlist = new List<Core.Business.Account>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
string sort = "";
if (oderby == "")
{
sort = "Account.Id Desc";
}
else if (oderby == "newcear")
{
sort = "Friendship.DateCreated Desc";
}
else if (oderby == "name")
{
sort = "Account.Name ASC";
}
int pageNum = pinfotemp.CurrentPage;
int pageSize = 0;
if (pageNum == 1)
{
pageSize = divernum;
}
else
{
pageSize = pinfotemp.PageSize;
}
string SqlGetUC = "SELECT Top " + pageSize + " Account.Id,Account.Name From Account INNER JOIN Friendship ON Account.Id=Friendship.FriendId WHERE Account.Id not in(SELECT Top ";
if (pageNum == 1)
{
SqlGetUC += "0";
}
else
{
SqlGetUC += ((pageNum - 2) * pageSize) + divernum;
}
SqlGetUC += " Account.Id From Account INNER JOIN Friendship ON Account.Id=Friendship.FriendId WHERE Friendship.AccountId=" + uid + " and IsChecked=1 ORDER BY " + sort + ") and Friendship.AccountId=" + uid + " and IsChecked=1 ORDER BY " + sort;
SqlDataReader reader = sql.ExecuteSqlReader(SqlGetUC);
if (reader != null)
{
while (reader.Read())
{
Core.Business.Account account = new Core.Business.Account();
long id = reader.GetInt64(0);
account = Core.Business.Account.Load(id);
account.MarkOld();
accountlist.Add(account);
}
reader.Close();
}
return accountlist;
}
/// <summary>
/// 得到留言
/// </summary>
/// <param name="uid"></param>
/// <param name="pinfo"></param>
/// <returns></returns>
public IList<CY.UME.Core.Business.SpaceComment> GetLevelWordsList(PagingInfo pinfo, CY.UME.Core.Business.InstantlyKilling instantlyKilling)
{
IList<CY.UME.Core.Business.SpaceComment> LeaveList = new List<CY.UME.Core.Business.SpaceComment>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
#region 参数
string tables = "SpaceComment INNER JOIN SpaceCommentExtend ON SpaceComment.Id=SpaceCommentExtend.Id";
string pK = "SpaceComment.Id";
string sort = "SpaceComment.DateCreated Desc";
int pageNum = pinfo.CurrentPage;
int pageSize = pinfo.PageSize;
string fields = "SpaceComment.Id,SpaceComment.AuthorId,SpaceComment.Content,SpaceComment.DateCreated";
string filter = " SpaceCommentExtend.Type='Killing'";
if (instantlyKilling != null)
{
filter += " and SpaceCommentExtend.InstanceId=" + instantlyKilling.Id;
}
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())
{
CY.UME.Core.Business.SpaceComment scomment = new SpaceComment();
long id = reader.GetInt64(0);
scomment = Core.Business.SpaceComment.Load(id);
scomment.MarkOld();
LeaveList.Add(scomment);
}
reader.Close();
}
return LeaveList;
}
public List<CY.UME.Core.Business.Topic> GetGroupsTopicesByGroupTypeAndAccountId(CY.UME.Core.Business.Account account, PagingInfo pageInfo, int type, bool? IsRecommend, bool? IsManage, string field)
{
List<Core.Business.Topic> topiclist = new List<Core.Business.Topic>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
string filter = String.Empty;
string fields = String.Empty;
filter = "[Id] in (Select [Id] FROM [TopicExtend] Where [Type]='group'";
if (account != null)
{
filter += " and [InstanceId] in (select GroupId from accountgroup where accountId = " + account.Id;
if (IsManage != null)
{
if (IsManage.Value)
{
filter += " and [Role] = 1";
}
else
{
filter += " and [Role] <> 1";
}
}
filter += ")";
}
if (type != -1)
{
filter += " and [InstanceId] in (select [Id] from [Group] where 1=1 and [IsChecked]=1";
if (type == -2)
{
filter += " and [Type] <>0 ";
}
else
{
filter += " and [Type] = " + type.ToString();
}
filter += ")";
}
if (IsRecommend != null)
{
filter += " and [InstanceId] in (select [Id] from [Group] where [IsRecommended]=" + (IsRecommend.Value ? "1" : "0") + " and [IsChecked]=1)";
}
filter += ")";
fields = "[Id], [Content],[ViewNum],[ReplyNum],[TypeId],[DateCreated],[LastReplyDate],[AccountId],[LastAuthorId],[Title],[IP],[Level]";
sql.AddParameter("@PageNumber", SqlDbType.Int, pageInfo.CurrentPage);
sql.AddParameter("@PageSize", SqlDbType.Int, pageInfo.PageSize);
sql.AddParameter("@Tables", SqlDbType.NVarChar, "Topic");
sql.AddParameter("@PK", SqlDbType.NVarChar, "Id");
sql.AddParameter("@Sort", SqlDbType.NVarChar, field + " desc");
sql.AddParameter("@Fields", SqlDbType.NVarChar, fields);
sql.AddParameter("@Filter", SqlDbType.NVarChar, filter);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Topic topic = new Core.Business.Topic();
if (!reader.IsDBNull(0)) topic.Id = reader.GetGuid(0);
if (!reader.IsDBNull(1)) topic.Content = reader.GetString(1);
if (!reader.IsDBNull(2)) topic.ViewNum = reader.GetInt32(2);
if (!reader.IsDBNull(3)) topic.ReplyNum = reader.GetInt32(3);
if (!reader.IsDBNull(4)) topic.TypeId = reader.GetInt32(4);
if (!reader.IsDBNull(5)) topic.DateCreated = reader.GetDateTime(5);
if (!reader.IsDBNull(6)) topic.LastReplyDate = reader.GetDateTime(6);
if (!reader.IsDBNull(7)) topic.AccountId = reader.GetInt64(7);
if (!reader.IsDBNull(8)) topic.LastAuthorId = reader.GetInt64(8);
if (!reader.IsDBNull(9)) topic.Title = reader.GetString(9);
if (!reader.IsDBNull(10)) topic.IP = reader.GetString(10);
if (!reader.IsDBNull(11)) topic.Level = reader.GetInt32(11);
topic.MarkOld();
topiclist.Add(topic);
}
reader.Close();
}
return topiclist;
}
public List<TopicReply> SearchTopicReply(Activities active, string con, string title, string authorName, DateTime minDate, DateTime maxDate, PagingInfo pageInfo)
{
List<Core.Business.TopicReply> topicReplylist = new List<Core.Business.TopicReply>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
string sorts = String.Empty;
string filter = String.Empty;
string fields = String.Empty;
filter += "1=1";
if (active != null)
{
filter += " and [InstanceId] in (select [Id] from TopicExtend where InstanceId='" +active.Id+ "' and [Type]='active')";
}
if (con.Length > 0)
{
filter += " and [Content] like '%" + con + "%'";
}
if (title.Length > 0)
{
filter += " and [InstanceId] in (select [Id] from [Topic] where [Title] like '%" + title + "%')";
}
if (authorName.Length > 0)
{
filter += " and [AuthorId] in (select [Id] from [Account] where [Name] like '%" + authorName + "%')";
}
filter += " and [DateCreated] between '" + minDate.ToString() + "' and '" + maxDate.ToString() + "'";
sorts = "DateCreated desc";
fields = "[Id],[DateCreated],[InstanceId],[AccountId],[AuthorId],[ReferedId],[Content],[IP]";
sql.AddParameter("@PageNumber", SqlDbType.Int, pageInfo.CurrentPage);
sql.AddParameter("@PageSize", SqlDbType.Int, pageInfo.PageSize);
sql.AddParameter("@Tables", SqlDbType.NVarChar, "TopicReply");
sql.AddParameter("@PK", SqlDbType.NVarChar, "Id");
sql.AddParameter("@Sort", SqlDbType.NVarChar, sorts);
sql.AddParameter("@Fields", SqlDbType.NVarChar, fields);
sql.AddParameter("@Filter", SqlDbType.NVarChar, filter);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.TopicReply topicReply = new Core.Business.TopicReply();
if (!reader.IsDBNull(0)) topicReply.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) topicReply.DateCreated = reader.GetDateTime(1);
if (!reader.IsDBNull(2)) topicReply.InstanceId = reader.GetGuid(2);
if (!reader.IsDBNull(3)) topicReply.AccountId = reader.GetInt64(3);
if (!reader.IsDBNull(4)) topicReply.AuthorId = reader.GetInt64(4);
if (!reader.IsDBNull(5)) topicReply.ReferedId = reader.GetInt64(5);
if (!reader.IsDBNull(6)) topicReply.Content = reader.GetString(6);
if (!reader.IsDBNull(7)) topicReply.IP = reader.GetString(7);
topicReply.MarkOld();
topicReplylist.Add(topicReply);
}
reader.Close();
}
return topicReplylist;
}
public IList<CY.UME.Core.Business.Topic> GetReplyedTopicesByAccount(CY.UME.Core.Business.Account account, int level, string type, PagingInfo pageInfo)
{
IList<CY.UME.Core.Business.Topic> topiclist = new List<CY.UME.Core.Business.Topic>();
if (account == null)
{
return topiclist;
}
SqlServerUtility sql = new SqlServerUtility(connectionString);
string filter = "Id in (select InstanceId from TopicReply where [AuthorId] = " + account.Id + ")";
filter += "and Id in (select Id from TopicExtend where InstanceId NOT IN(SELECT [Id] FROM [Group] WHERE [IsChecked] = 0) and [Type]='" + type + "')"; //����˹��˽�ɢȺ��ʱ��ʾ����ظ�����--2010/12/25
if (level != -1)
{
filter += " and [level]=" + level;
}
sql.AddParameter("@PageNumber", SqlDbType.Int, pageInfo.CurrentPage);
sql.AddParameter("@PageSize", SqlDbType.Int, pageInfo.PageSize);
sql.AddParameter("@Tables", SqlDbType.NVarChar, "Topic");
sql.AddParameter("@PK", SqlDbType.NVarChar, "Id");
sql.AddParameter("@Sort", SqlDbType.NVarChar, "LastReplyDate desc");
sql.AddParameter("@Fields", SqlDbType.NVarChar, "[Id], [Content],[ViewNum],[ReplyNum],[TypeId],[DateCreated],[LastReplyDate],[AccountId],[LastAuthorId],[Title],[IP],[Level]");
sql.AddParameter("@Filter", SqlDbType.NVarChar, filter);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Topic topic = new Core.Business.Topic();
if (!reader.IsDBNull(0)) topic.Id = reader.GetGuid(0);
if (!reader.IsDBNull(1)) topic.Content = reader.GetString(1);
if (!reader.IsDBNull(2)) topic.ViewNum = reader.GetInt32(2);
if (!reader.IsDBNull(3)) topic.ReplyNum = reader.GetInt32(3);
if (!reader.IsDBNull(4)) topic.TypeId = reader.GetInt32(4);
if (!reader.IsDBNull(5)) topic.DateCreated = reader.GetDateTime(5);
if (!reader.IsDBNull(6)) topic.LastReplyDate = reader.GetDateTime(6);
if (!reader.IsDBNull(7)) topic.AccountId = reader.GetInt64(7);
if (!reader.IsDBNull(8)) topic.LastAuthorId = reader.GetInt64(8);
if (!reader.IsDBNull(9)) topic.Title = reader.GetString(9);
if (!reader.IsDBNull(10)) topic.IP = reader.GetString(10);
if (!reader.IsDBNull(11)) topic.Level = reader.GetInt32(11);
topic.MarkOld();
topiclist.Add(topic);
}
reader.Close();
}
return topiclist;
}
public DataTable GetAll(Core.Business.Album albums, String Sort, PagingInfo pageInfo)
{
SqlServerUtility sql = new SqlServerUtility(connectionString);
DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(Int64));
dt.Columns.Add("AccountId", typeof(Int64));
dt.Columns.Add("ViewPermission", typeof(Int32));
dt.Columns.Add("DateCreated",typeof(DateTime));
dt.Columns.Add("LastModifiedTime", typeof(DateTime));
dt.Columns.Add("IsAvatar",typeof(bool));
dt.Columns.Add("Name", typeof(String));
dt.Columns.Add("CoverPath",typeof(String));
dt.Columns.Add("ViewPassword", typeof(String));
dt.Columns.Add("AccountName", typeof(String));
dt.Columns.Add("PicNumber", typeof(Int32));
String strFilter = SetStrFilter(albums);
String Columns = "[Album].[Id],[Album].[AccountId],[Album].[ViewPermission],[Album].[DateCreated],[Album].[LastModifiedTime],[Album].[IsAvatar],[Album].[Name],[Album].[CoverPath],[Album].[ViewPassword],[Account].[Name],(SELECT COUNT([Picture].[Id]) FROM [Picture] WHERE [Picture].[AlbumId] = [Album].[Id]) AS PicNumber";
String SqlGetUC = "SELECT TOP (" + pageInfo.PageSize + ") " + Columns + " FROM [Album],[Account] WHERE [Album].[Id] NOT IN ";
SqlGetUC += "(SELECT TOP (" + pageInfo.PageSize * (pageInfo.CurrentPage - 1) + ") [Album].[Id] FROM [Album],[Account] WHERE " + strFilter + ") and " + strFilter + " ORDER BY " + Sort + " DESC";// ORDER BY " + Sort + " DESC
SqlDataReader reader = sql.ExecuteSqlReader(SqlGetUC);
if (reader != null)
{
while (reader.Read())
{
DataRow dr = dt.NewRow();
if (!reader.IsDBNull(0)) dr[0] = reader.GetInt64(0);
if (!reader.IsDBNull(1)) dr[1] = reader.GetInt64(1);
if (!reader.IsDBNull(2)) dr[2] = reader.GetInt32(2);
if (!reader.IsDBNull(3)) dr[3] = reader.GetDateTime(3);
if (!reader.IsDBNull(4)) dr[4] = reader.GetDateTime(4);
if (!reader.IsDBNull(5)) dr[5] = reader.GetBoolean(5);
if (!reader.IsDBNull(6)) dr[6] = reader.GetString(6);
if (!reader.IsDBNull(7)) dr[7] = reader.GetString(7);
if (!reader.IsDBNull(8)) dr[8] = reader.GetString(8);
if (!reader.IsDBNull(9)) dr[9] = reader.GetString(9);
if (!reader.IsDBNull(10)) dr[10] = reader.GetInt32(10);
dt.Rows.Add(dr);
}
reader.Close();
}
return dt;
}
public int GetSearchTopicesCount(string InstanceId, string topicExtendType, string authorName, string title, int? minViewNum, int? maxViewNum, int? minReplyNum, int? maxReplyNum, DateTime? minPubDate, DateTime? maxPubDate, int? level, int? type, PagingInfo pageInfo)
{
SqlServerUtility sql = new SqlServerUtility(connectionString);
string filter = "1=1";
bool topicExtendCheck = false;
if (InstanceId.Length > 0 || topicExtendType.Length > 0)
{
topicExtendCheck = true;
}
if (topicExtendCheck)
{
filter += " and [Id] in (Select [Id] FROM [TopicExtend] Where 1=1";
}
if (InstanceId.Length > 0)
{
filter += " and InstanceId = '" + InstanceId + "'";
}
if (topicExtendType.Length > 0)
{
filter += " and [Type]='" + topicExtendType + "'";
}
if (topicExtendCheck)
{
filter += ")";
}
bool accountCheck = false;
if (authorName.Length > 0)
{
accountCheck = true;
}
if (accountCheck)
{
filter += " and AccountId in (select [Id] From Account Where 1=1";
}
if (authorName.Length > 0)
{
filter += " and [Name] like '%" + authorName + "%'";
}
if (accountCheck)
{
filter += ")";
}
if (title.Length > 0)
{
filter += " and [Title] like '%" + title + "%'";
}
if (minViewNum != null)
{
filter += " and [ViewNum] >= " + minViewNum.ToString();
}
if (maxViewNum != null)
{
filter += " and [ViewNum] <= " + minViewNum.ToString();
}
if (minReplyNum != null)
{
filter += " and [ReplyNum] >= " + minReplyNum.ToString();
}
if (maxReplyNum != null)
{
filter += " and [ReplyNum] <= " + maxReplyNum.ToString();
}
if (minPubDate != null)
{
filter += " and [DateCreated] >= '" + minPubDate.ToString() + "'";
}
if (maxPubDate != null)
{
filter += " and [DateCreated] <= '" + minPubDate.ToString() + "'";
}
if (level != null)
{
filter += " and [Level] =" + level.ToString();
}
if (type != null)
{
filter += " and [type]=" + type.ToString();
}
SqlDataReader reader = sql.ExecuteSqlReader("SELECT COUNT([Topic].[Id]) FROM [Topic] WHERE " + filter);
if (reader != null && !reader.IsClosed && reader.Read())
{
int count = 0;
if (!reader.IsDBNull(0)) count = reader.GetInt32(0);
reader.Close();
return count;
}
else
{
if (reader != null && !reader.IsClosed)
reader.Close();
return 0;
}
}
public IList<CY.UME.Core.Business.Topic> GetTopicsByInstanceIdAndType(string topicExtendType, string instanceId, PagingInfo pageInfo, string field, string sort, int level)
{
IList<Core.Business.Topic> topiclist = new List<Core.Business.Topic>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
string sorts = String.Empty;
string filter = String.Empty;
string fields = String.Empty;
sorts = field + " " + sort;
filter = "[Id] in (Select [Id] FROM [TopicExtend] Where 1=1 ";
if (instanceId.Length != 0)
{
filter += " and [InstanceId] = '" + instanceId + "'";
}
if (topicExtendType.Length != 0)
{
filter += " and [Type] = '" + topicExtendType + "'";
}
filter += ")";
if (level != -1)
{
filter += " AND [Level] = " + level.ToString();
}
fields = "[Id],[Content],[ViewNum],[ReplyNum],[TypeId],[DateCreated],[LastReplyDate],[AccountId],[LastAuthorId],[Title],[IP],[Level]";
sql.AddParameter("@PageNumber", SqlDbType.Int, pageInfo.CurrentPage);
sql.AddParameter("@PageSize", SqlDbType.Int, pageInfo.PageSize);
sql.AddParameter("@Tables", SqlDbType.NVarChar, "Topic");
sql.AddParameter("@PK", SqlDbType.NVarChar, "Id");
sql.AddParameter("@Sort", SqlDbType.NVarChar, sorts);
sql.AddParameter("@Fields", SqlDbType.NVarChar, fields);
sql.AddParameter("@Filter", SqlDbType.NVarChar, filter);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Topic topic = new Core.Business.Topic();
if (!reader.IsDBNull(0)) topic.Id = reader.GetGuid(0);
if (!reader.IsDBNull(1)) topic.Content = reader.GetString(1);
if (!reader.IsDBNull(2)) topic.ViewNum = reader.GetInt32(2);
if (!reader.IsDBNull(3)) topic.ReplyNum = reader.GetInt32(3);
if (!reader.IsDBNull(4)) topic.TypeId = reader.GetInt32(4);
if (!reader.IsDBNull(5)) topic.DateCreated = reader.GetDateTime(5);
if (!reader.IsDBNull(6)) topic.LastReplyDate = reader.GetDateTime(6);
if (!reader.IsDBNull(7)) topic.AccountId = reader.GetInt64(7);
if (!reader.IsDBNull(8)) topic.LastAuthorId = reader.GetInt64(8);
if (!reader.IsDBNull(9)) topic.Title = reader.GetString(9);
if (!reader.IsDBNull(10)) topic.IP = reader.GetString(10);
if (!reader.IsDBNull(11)) topic.Level = reader.GetInt32(11);
topic.MarkOld();
topiclist.Add(topic);
}
reader.Close();
}
return topiclist;
}
public IList<Account> GetAllAccount(PagingInfo pageinfo)
{
IList<Core.Business.Account> accountlist = new List<Core.Business.Account>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
#region ����
string tables = "view_account_all";
string pK = "Id";
string sort = "Id Desc";
int pageNum = pageinfo.CurrentPage;
int pageSize = pageinfo.PageSize;
//string fields = "";
//string filter = "";
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.Account account = new Core.Business.Account();
long id = reader.GetInt64(0);
account = Core.Business.Account.Load(id);
account.MarkOld();
accountlist.Add(account);
}
reader.Close();
}
return accountlist;
}