/// <summary>
/// 查找 group_id 和 group_name对应信息是否已存
/// </summary>
/// <param name="group">需要group_name字段</param>
/// <returns>查找到返回true(并获取到group_id)</returns>
public bool searchGroupInfo(Group group)
{
string sql = string.Format("SELECT * FROM FFTX_Group WHERE group_name='{0}'", group.Group_Name);
SqlDataReader sqldr = SqlDB.ExecuteReader(sql);
if (sqldr != null && sqldr.HasRows)
{
//获取 Group ID
sqldr.Read();
group.Group_Id = sqldr.GetInt32(0);
return(true);
}
else
{
return(false);
}
}
public AdminOperate getAdminOperateByuid(User u)
{
AdminOperate ao = new AdminOperate();
string sql = string.Format("select top 1 * from FFTX_Admin_Operate where user_id = {0} order by operate_id", u.User_Id);
SqlDataReader sqldr = SqlDB.ExecuteReader(sql);
if (sqldr != null && sqldr.HasRows)
{
sqldr.Read();
ao.Admin_Id = sqldr.GetValue(1) + "";
ao.User_Id = sqldr.GetValue(2) + "";
ao.Operate_Time = sqldr.GetDateTime(3);
ao.Operate_Reason = sqldr.GetValue(4) + "";
return(ao);
}
return(null);
}
/// <summary>
/// 是否是好友
/// </summary>
/// <param name="f">需要 user_id 和 follow_id</param>
/// <returns>有关系返回true(获取到相关信息)</returns>
public bool hasBeenFriend(Friend f)
{
//查找相关信息
string sql = string.Format("select * from FFTX_Friend where user_id='{0}' and follow_id='{1}'", f.User_Id, f.Follow_Id);
SqlDataReader sqldr = SqlDB.ExecuteReader(sql);
if (sqldr != null && sqldr.HasRows)
{
sqldr.Read();
f.Group_Id = sqldr.GetInt32(3);
f.Follow_Id_Remark = sqldr.GetValue(4) + "";
return(true);
}
else
{
return(false);
}
}
//获取照片信息
public bool getPhotoInfo(Photo p)
{
string sql = String.Format("select * from FFTX_Photo where photo_id = '{0}'", p.Photo_Id);
SqlDataReader sqldr = SqlDB.ExecuteReader(sql);
if (sqldr != null && sqldr.HasRows)
{
sqldr.Read(); //读第一行数据
p.Photo_Id = sqldr.GetInt32(0);
p.Photo_Src = sqldr.GetValue(1) + "";
p.Photo_Time = sqldr.GetDateTime(2);
p.album_id = sqldr.GetInt32(3);
p.Photo_Describe = sqldr.GetValue(4) + "";
p.Photo_Like = sqldr.GetInt32(5);
p.Photo_Label = sqldr.GetInt32(6);
return(true);
}
return(false);
}
public Album getAlbumInfoById(Album album)
{
string sql = string.Format("select * from FFTX_Album where album_id = '{0}'", album.Album_Id);
SqlDataReader sqldr = SqlDB.ExecuteReader(sql);
if (sqldr != null && sqldr.HasRows)
{
sqldr.Read(); //读数据
Album al = new Album(); //获取对象来存放数据
al.Album_Id = sqldr.GetInt32(0);
al.User_Id = sqldr.GetValue(1) + "";
al.Album_Name = sqldr.GetValue(2) + "";
al.Album_Cover = sqldr.GetValue(3) + "";
al.Album_Describe = sqldr.GetValue(4) + "";
al.Album_Power = sqldr.GetInt32(5);
return(al);
}
return(null);
}
/// <summary>
/// 查找用户分组信息
/// </summary>
/// <param name="group">需要User id 和 Group Name</param>
/// <returns>查找到返回true(得到完整的group信息) 没有返回false</returns>
public bool searchGroupFriendInfo(Group group)
{
//是否创建好 group_id和 group_name 对应
if (addGroupInfo(group))
{ //查找 用户分组信息 ( 此时已经获得 group id )
string sql = string.Format("select * from FFTX_Group_to_Friend where user_id='{0}' and group_id='{1}'", group.User_Id, group.Group_Id);
SqlDataReader sqldr = SqlDB.ExecuteReader(sql);
if (sqldr != null && sqldr.HasRows)
{
sqldr.Read();
//获取 组人数
group.Group_Friend_Num = sqldr.GetInt32(4);
return(true);
}
return(false);
}
return(false);
}
// 获取 用户所有请求
// request state 0代表未处理 1代表已拒绝 删除则表明解封成功
public List <UserRequest> getUserRequst()
{
string sql = string.Format("select * from FFTX_Admin_Request where request_state = 0");
SqlDataReader sqldr = SqlDB.ExecuteReader(sql);
if (sqldr != null && sqldr.HasRows)
{
List <UserRequest> list = new List <UserRequest>();
//获取user request
while (sqldr.Read())
{
UserRequest ur = new UserRequest();
ur.User_Id = sqldr.GetValue(1) + "";
ur.Request_Time = sqldr.GetDateTime(2);
list.Add(ur);
}
return(list);
}
return(null);
}
/// <summary>
/// 根据User_Id获取 用户分组详情
/// </summary>
/// <param name="user">用户类</param>
/// <returns>返回list 包含用户所有分组</returns>
public List <Group> getGroupFriendInfo(User user)
{
string sql = string.Format("select * from FFTX_Group_to_Friend where user_id='{0}'", user.User_Id);
SqlDataReader sqldr = SqlDB.ExecuteReader(sql);
if (sqldr != null && sqldr.HasRows)
{
List <Group> list = new List <Group>();
while (sqldr.Read())
{
Group p = new Group();
p.User_Id = sqldr.GetValue(1) + "";
p.Group_Id = sqldr.GetInt32(2);
p.Group_Name = sqldr.GetValue(3) + "";
p.Group_Friend_Num = sqldr.GetInt32(4);
list.Add(p);
}
return(list);
}
return(null);
}
//删除相册 path: Request.MapPath("~s");
public bool deleteAlbum(Album album, string path)
{
//查询相册中所包含的 photo id
string sql = string.Format("select photo_id from FFTX_Photo where album_id = '{0}'", album.Album_Id);
SqlDataReader sqldr = SqlDB.ExecuteReader(sql);
if (sqldr != null && sqldr.HasRows)
{
while (sqldr.Read())
{
//删除photo
deletePhoto(sqldr.GetInt32(0), path);
}
}
string sql2 = string.Format("delete from FFTX_Album where album_id = {0}", album.Album_Id);
int result = SqlDB.ExecuteNonQuery(sql2);
if (result == 1)
{
return(true);
}
return(false);
}
public int getPageNum(Album album)
{
string sql = string.Format("SELECT COUNT(0) from FFTX_Photo where album_id ={0}", album.Album_Id);
SqlDataReader sqldr = SqlDB.ExecuteReader(sql);
int pageNum = 1;
//一页显示的数据总数
int onePageNum = 10;
if (sqldr != null && sqldr.HasRows)
{
sqldr.Read();
int itemSum = sqldr.GetInt32(0);
if (itemSum % onePageNum == 0)
{
pageNum = (itemSum / onePageNum);
}
else
{
pageNum = (itemSum / onePageNum) + 1;
}
}
return(pageNum);
}
public int getPageNum(User u)
{
string sql = string.Format("SELECT COUNT(0) from FFTX_BoardMessage where message_friend_id ={0}", u.User_Id);
SqlDataReader sqldr = SqlDB.ExecuteReader(sql);
int pageNum = 1;
//一页显示的数据总数
int onePageNum = 10;
if (sqldr != null && sqldr.HasRows)
{
sqldr.Read();
int itemSum = sqldr.GetInt32(0);
if (itemSum % onePageNum == 0)
{
pageNum = (itemSum / onePageNum);
}
else
{
pageNum = (itemSum / onePageNum) + 1;
}
}
return(pageNum);
}
//搜索照片
public List <Photo> searchName(string name)
{
string sql = string.Format("select * from FFTX_Photo p,FFTX_Album a where p.photo_src like '%(%{0}%' and a.album_power = 0 and a.album_id = p.album_id;", name);
SqlDataReader sqldr = SqlDB.ExecuteReader(sql);
if (sqldr != null && sqldr.HasRows)
{
List <Photo> list = new List <Photo>();
while (sqldr.Read())
{
Photo p = new Photo();
p.Photo_Id = sqldr.GetInt32(0);
p.Photo_Src = sqldr.GetValue(1) + "";
p.Photo_Time = sqldr.GetDateTime(2);
p.album_id = sqldr.GetInt32(3);
p.Photo_Describe = sqldr.GetValue(4) + "";
p.Photo_Like = sqldr.GetInt32(5);
p.Photo_Label = sqldr.GetInt32(6);
list.Add(p);
}
return(list);
}
return(null);
}
//推荐图片 5个图片
public List <Photo> getRecommandList(User u)
{
string sql = string.Format("select top 5 p.photo_id,p.photo_src,p.photo_time,p.album_id,photo_describe,p.photo_like,p.photo_label from FFTX_Photo p,FFTX_User u,FFTX_Album a where photo_label = (SELECT top 1 photo_label FROM FFTX_Photo p,FFTX_Album a where a.user_id={0} and a.album_id = p.album_id and p.photo_label<>1 and p.photo_label<>2 and p.photo_label<>3 GROUP BY photo_label ORDER BY count( * ) DESC)and u.user_id<> {0} and u.user_id= a.user_id and a.album_id = p.album_id order by newid()", u.User_Id);
SqlDataReader sqldr = SqlDB.ExecuteReader(sql);
if (sqldr != null && sqldr.HasRows)
{
List <Photo> list = new List <Photo>();
while (sqldr.Read())
{
Photo p = new Photo();
p.Photo_Id = sqldr.GetInt32(0);
p.Photo_Src = sqldr.GetValue(1) + "";
p.Photo_Time = sqldr.GetDateTime(2);
p.album_id = sqldr.GetInt32(3);
p.Photo_Describe = sqldr.GetValue(4) + "";
p.Photo_Like = sqldr.GetInt32(5);
p.Photo_Label = sqldr.GetInt32(6);
list.Add(p);
}
return(list);
}
return(null);
}
//排行榜 5个图片
public List <Photo> getRankList()
{
string sql = string.Format("select top 5 * from FFTX_Photo ORDER BY photo_like desc");
SqlDataReader sqldr = SqlDB.ExecuteReader(sql);
if (sqldr != null && sqldr.HasRows)
{
List <Photo> list = new List <Photo>();
while (sqldr.Read())
{
Photo p = new Photo();
p.Photo_Id = sqldr.GetInt32(0);
p.Photo_Src = sqldr.GetValue(1) + "";
p.Photo_Time = sqldr.GetDateTime(2);
p.album_id = sqldr.GetInt32(3);
p.Photo_Describe = sqldr.GetValue(4) + "";
p.Photo_Like = sqldr.GetInt32(5);
p.Photo_Label = sqldr.GetInt32(6);
list.Add(p);
}
return(list);
}
return(null);
}
//查找好友
public User getFriendInfo(User user)
{
string sql = string.Format("select * from FFTX_User where user_id = '{0}'", user.User_Id);
SqlDataReader sqldr = SqlDB.ExecuteReader(sql);
if (sqldr != null && sqldr.HasRows)
{
sqldr.Read(); //读第一行数据
User u = new User(); //获取对象来存放数据
u.User_Id = sqldr.GetValue(0) + "";
u.User_Name = sqldr.GetValue(1) + "";
u.User_State = sqldr.GetInt32(5);
u.User_Email = sqldr.GetValue(6) + "";
u.User_Phone = sqldr.GetValue(7) + "";
u.User_HeadImg = sqldr.GetValue(8) + "";
u.User_Sex = sqldr.GetValue(9) + "";
u.User_Fans = sqldr.GetInt32(10);
u.User_Follow = sqldr.GetInt32(11);
return(u);
}
return(null);
}