/// <summary>
/// 查询用户基本信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
internal static UserInfo SelUserInfoByID(int id)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var user = db.UserInfo.FirstOrDefault(m => m.U_Id == id);
if (user != null)
{
UserInfo u = new UserInfo()
{
U_Img = user.U_Img,
U_Info = user.U_Info,
U_Like = user.U_Like,
U_Name = user.U_Name,
U_Email = user.U_Email,
U_Birthday = user.U_Birthday,
U_Tell = user.U_Tell,
U_Fans = user.U_Fans,
U_Follow = user.U_Follow,
U_Gender = user.U_Gender,
U_Hobby = user.U_Hobby,
U_RegistrationTime = user.U_RegistrationTime,
U_CollectSongMenu = user.U_CollectSongMenu,
U_CreatSongMenu = user.U_CreatSongMenu
};
return(u);
}
return(null);
}
}
/// <summary>
/// 根据用户存储的歌单id 查询歌单列表
/// </summary>
/// <param name="SongMenuID"></param>
/// <returns></returns>
internal static object SelSongMenuByUser(string[] SongMenuID)
{
if (SongMenuID == null)
{
return(null);
}
using (YoungMusicEntities db = new YoungMusicEntities())
{
//将歌曲id转成list 使用Contains去查询包含该id的歌曲信息
List <int> list = new List <int>();
foreach (string item in SongMenuID)
{
list.Add(int.Parse(item));
}
var song = (from s in db.SongMenu
join u in db.UserInfo
on s.M_UserId equals u.U_Id
select new
{
M_Id = s.M_Id,
M_Name = s.M_Name,
M_Img = s.M_Img,
M_Info = s.M_Info,
M_CollectCount = s.M_CollectCount,
M_PlayCount = s.M_PlayCount,
M_Type = s.M_Type,
M_UserId = s.M_UserId,
U_Name = u.U_Name,
M_CreatTime = s.M_CreatTime,
M_SongId = s.M_SongId
}).Where(m => list.Contains(m.M_Id)).ToList();
return(song);
}
}
/// <summary>
/// 添加一个用户信息
/// </summary>
/// <param name="u">UserInfo</param>
internal static int AddUserInfo(UserInfo u)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
UserInfo user = db.UserInfo.FirstOrDefault(m => m.U_Email == u.U_Email);
try
{
if (user == null)
{
db.UserInfo.Add(u);
db.SaveChanges();
user = db.UserInfo.FirstOrDefault(m => m.U_Email == u.U_Email);
return(user.U_Id);
}
else
{
return(0);
}
}
catch (Exception)
{
return(0);
}
}
}
/// <summary>
/// 获取所有歌单信息
/// </summary>
/// <param name="top"></param>
/// <param name="page"></param>
/// <returns></returns>
internal static object GetSongMenus(out int count, int top, int page)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var songMenu = (from s in db.SongMenu
join u in db.UserInfo
on s.M_UserId equals u.U_Id
select new
{
M_Id = s.M_Id,
M_Name = s.M_Name,
M_Img = s.M_Img,
M_Info = s.M_Info,
M_CollectCount = s.M_CollectCount,
M_PlayCount = s.M_PlayCount,
M_Type = s.M_Type,
M_UserId = s.M_UserId,
U_Name = u.U_Name,
M_CreatTime = s.M_CreatTime,
M_SongId = s.M_SongId
}).Where(x => x.M_Name != "我喜欢的音乐").ToList();
count = songMenu.Count;
var sm = songMenu.OrderByDescending(m => m.M_PlayCount)
.OrderByDescending(m => m.M_CollectCount).Skip(top * page).Take(top);
return(sm);
}
}
/// <summary>
/// 根据传进来的歌单id数组 查询对应的歌曲信息
/// </summary>
/// <param name="songMenuNum"></param>
/// <returns></returns>
internal static object SelSongListByMenu(string[] songMenuNum)
{
if (songMenuNum == null)
{
return(null);
}
using (YoungMusicEntities db = new YoungMusicEntities())
{
//将歌曲id转成list 使用Contains去查询包含该id的歌曲信息
List <int> list = new List <int>();
foreach (string item in songMenuNum)
{
list.Add(int.Parse(item));
}
var song = (from s in db.SongList
join g in db.Singer
on s.S_Singer equals g.S_Id
select new
{
sID = s.S_Id, //歌曲ID
sName = s.S_Name, //歌曲名称
sUrl = s.S_Url, //歌曲播放地址
sCover = s.S_Cover, //歌曲封面
sPlayCount = s.S_PlayCount, //播放次数
sCollectCount = s.S_CollectCount, //收藏次数
sUpTime = s.S_UpTime, //上传时间
sLyric = s.S_Lyric, //歌词
sAlbumID = s.S_Album, //专辑id
wyid = s.WYID, //网易ID
gID = s.S_Singer,
gName = g.S_Name,
}).Where(m => list.Contains(m.sID)).ToList();
return(song);
}
}
/// <summary>
/// 根据歌单id，查询歌单基本信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
internal static object SelSongMenuByID(int id)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var s = (from m in db.SongMenu
join u in db.UserInfo
on m.M_UserId equals u.U_Id
join t in db.Type
on m.M_Type equals t.T_Id
where m.M_Id == id
select new
{
M_Id = m.M_Id,
M_Name = m.M_Name,
M_Img = m.M_Img,
M_Info = m.M_Info,
M_CollectCount = m.M_CollectCount,
M_PlayCount = m.M_PlayCount,
M_Type = m.M_Type,
T_TypeName = t.T_Name,
M_UserId = m.M_UserId,
U_Name = u.U_Name,
U_Img = u.U_Img,
M_CreatTime = m.M_CreatTime,
M_SongId = m.M_SongId
}).ToList().FirstOrDefault();
return(s);
}
}
/// <summary>
/// 热门歌曲推荐
/// </summary>
/// <returns></returns>
internal static object SelHotTop(int top = 10)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var list = (from s in db.SongList
orderby s.S_CollectCount descending
orderby s.S_PlayCount descending
select new
{
S_Id = s.S_Id,
S_Name = s.S_Name,
S_Album = s.S_Album,
S_CollectCount = s.S_CollectCount,
S_Url = s.S_Url,
S_Audit_State = s.S_Audit_State,
S_Cover = s.S_Cover,
S_Lyric = s.S_Lyric,
S_PlayCount = s.S_PlayCount,
S_Singer = s.S_Singer,
S_Type = s.S_Type,
S_UpTime = s.S_UpTime
}).Take(top).ToList();
return(list);
}
}
/// <summary>
/// 获取专辑信息 有分页
/// </summary>
/// <param name="top"></param>
/// <param name="page"></param>
/// <returns></returns>
public static object GetAlbums(int top, int page)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var list = (from a in db.Album
join s in db.Singer
on a.A_SingerId equals s.S_Id
select new
{
A_Id = a.A_Id,
A_Img = a.A_Img,
A_Info = a.A_Info,
A_Name = a.A_Name,
A_PlayCount = a.A_PlayCount,
A_SingerId = a.A_SingerId,
SingerInfo = new
{
S_Id = s.S_Id,
S_Img = s.S_Img,
S_Name = s.S_Name,
S_Info = s.S_Info
},
A_Song = a.A_Song,
A_Time = a.A_Time
}).OrderByDescending(x => x.A_PlayCount).Skip(top * page).Take(top).ToList();
return(list);
}
}
/// <summary>
/// 根据歌曲ID 查歌曲详情信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static object SelSongbyID(int id)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var song = (from s in db.SongList
join g in db.Singer
on s.S_Singer equals g.S_Id
join t in db.Type
on s.S_Type equals t.T_Id
join a in db.Album
on s.S_Album equals a.A_Id into info
from i in info.DefaultIfEmpty()
where s.S_Id == id
select new
{
sID = s.S_Id, //歌曲ID
sName = s.S_Name, //歌曲名称
typeID = t.T_Id, //类型ID
typeName = t.T_Name, //类型名称
singerID = g.S_Id, //歌手ID
singerName = g.S_Name, //歌手名称
singerImg = g.S_Img, //歌手图片
sUrl = s.S_Url, //歌曲播放地址
sCover = s.S_Cover, //歌曲封面
sPlayCount = s.S_PlayCount, //播放次数
sCollectCount = s.S_CollectCount, //收藏次数
sUpTime = s.S_UpTime, //上传时间
sLyric = s.S_Lyric, //歌词
sAlbumID = s.S_Album, //专辑id
sAlbumName = i.A_Name, //专辑id
wyid = s.WYID, //网易ID
}).ToList();
return(song);
}
}
/// <summary>
/// 根据专辑id查询专辑基本信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
internal static object GetAlbumByID(int id)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var a = (from m in db.Album
join s in db.Singer
on m.A_SingerId equals s.S_Id
where m.A_Id == id
select new
{
A_Id = m.A_Id,
A_Name = m.A_Name,
A_Img = m.A_Img,
A_Info = m.A_Info,
A_PlayCount = m.A_PlayCount,
A_SingerId = m.A_SingerId,
SingerInfo = new
{
S_Id = s.S_Id,
S_Name = s.S_Name,
S_Img = s.S_Img,
S_Info = s.S_Info
},
A_Time = m.A_Time,
A_Song = m.A_Song
}).ToList().FirstOrDefault();
return(a);
}
}
/// <summary>
/// 模糊查询 查询歌名是传入值或者歌手是传入值
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
internal static object SelSongListByName(string name, int top = 30, int page = 0)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var song = (from s in db.SongList
join g in db.Singer
on s.S_Singer equals g.S_Id
join t in db.Type
on s.S_Type equals t.T_Id
join a in db.Album
on s.S_Album equals a.A_Id into info
from i in info.DefaultIfEmpty()
select new
{
sID = s.S_Id, //歌曲ID
sName = s.S_Name, //歌曲名称
typeID = t.T_Id, //类型ID
typeName = t.T_Name, //类型名称
singerID = g.S_Id, //歌手ID
singerName = g.S_Name, //歌手名称
singerImg = g.S_Img, //歌手图片
sUrl = s.S_Url, //歌曲播放地址
sCover = s.S_Cover, //歌曲封面
sPlayCount = s.S_PlayCount, //播放次数
sCollectCount = s.S_CollectCount, //收藏次数
sUpTime = s.S_UpTime, //上传时间
sLyric = s.S_Lyric, //歌词
sAlbumID = s.S_Album, //专辑id
sAlbumName = i.A_Name, //专辑id
wyid = s.WYID, //网易ID
}).Where(m => m.singerName.Contains(name) || m.sName.Contains(name))
.OrderByDescending(m => m.sPlayCount).OrderByDescending(m => m.sCollectCount).Skip(top * page).Take(top).ToList();
return(song);
}
}
internal static object SelTypeListAll()
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
return(db.Type.Select(x => new { Tid = x.T_Id, Tname = x.T_Name }).ToList());
}
}
/// <summary>
/// 查询当前用户是否存在 并且邮箱和密码正确
/// </summary>
/// <param name="u">如果邮箱密码正确 并存在这个用户 返回true 否则返回false</param>
/// <returns></returns>
internal static UserInfo SelUserInfo(UserInfo u)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
UserInfo user = db.UserInfo.FirstOrDefault(m => m.U_Email == u.U_Email && m.U_Pwd == u.U_Pwd);
return(user);
}
}
/// <summary>
/// 发布动态
/// </summary>
/// <param name="dynamic"></param>
/// <returns></returns>
internal static bool AddDynamic(Dynamic dynamic)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
db.Dynamic.Add(dynamic);
return(db.SaveChanges() > 0);
}
}
/// <summary>
/// 歌单播放次数+1
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
internal static bool AddPlayCount(int id)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var s = db.SongMenu.FirstOrDefault(m => m.M_Id == id);
s.M_PlayCount += 1;
return(db.SaveChanges() > 0);
}
}
/// <summary>
/// 上传歌单图片
/// </summary>
/// <param name="mid"></param>
/// <param name="resultUrl"></param>
internal static void UpSongMenuImg(int mid, string resultUrl)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var m = db.SongMenu.FirstOrDefault(x => x.M_Id == mid);
if (m != null)
{
m.M_Img = resultUrl;
}
db.SaveChanges();
}
}
/// <summary>
/// 跟新用户头像
/// </summary>
/// <param name="uid"></param>
/// <param name="resultUrl"></param>
internal static void UpUserPhoto(int uid, string resultUrl)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var user = db.UserInfo.FirstOrDefault(m => m.U_Id == uid);
if (user != null)
{
user.U_Img = resultUrl;
}
db.SaveChanges();
}
}
/// <summary>
/// 添加收藏歌曲/取消收藏歌曲
/// </summary>
/// <param name="uID">用户id</param>
/// <param name="songID">歌曲id</param>
/// <param name="muneID">歌单id</param>
/// <param name="flag">是否要删除 已收藏并且要删除才会删除 true要删除 false 不删除</param>
/// <returns>操作成功 返回 true 歌单不存在会抛出异常</returns>
internal static string AddSongToMenu(int uID, int songID, int muneID, bool flag)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
SongMenu m = db.SongMenu.FirstOrDefault(menu => menu.M_Id == muneID && menu.M_UserId == uID);
if (m != null)
{
string number = m.M_SongId;
string[] songNumber = number.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
string nowNumber = "";
bool f = false;
foreach (string item in songNumber)
{
//如果是相等的 说明已收藏
if (item == songID.ToString())
{
f = true;
}
else
{
nowNumber = nowNumber + item + ",";
}
}
if (f)
{
//如果是已收藏 然后要删除
if (flag)
{
m.M_SongId = nowNumber;
db.SaveChanges();
return("删除成功");
}
else
{
//不要删除
m.M_SongId = number;
db.SaveChanges();
return("已收藏");
}
}
else
{
//如果是没有收藏
number = number + songID + ",";
m.M_SongId = number;
db.SaveChanges();
return("添加收藏成功");
}
}
return("歌单不存在");
}
}
/// <summary>
/// 添加到用户最近播放
/// </summary>
/// <param name="id"></param>
/// <param name="sid"></param>
/// <returns></returns>
internal static bool AddRecentSong(int id, int sid)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var u = db.UserInfo.FirstOrDefault(m => m.U_Id == id);
if (u != null)
{
u.U_Like += sid.ToString() + ",";
SongListService.AddCount(sid, 1);
return(db.SaveChanges() > 0);
}
return(false);
}
}
/// <summary>
/// 发布评论
/// </summary>
/// <param name="c"></param>
/// <returns></returns>
internal static string AddDynamicComment(DynamicComment c)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
db.DynamicComment.Add(c);
if (db.SaveChanges() > 0)
{
return("发布成功");
}
else
{
return("发布失败");
}
}
}
/// <summary>
/// 动态点赞
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
internal static string AddGoodCount(int id)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var d = db.Dynamic.FirstOrDefault(x => x.D_Id == id);
d.D_GoodCount += 1;
if (db.SaveChanges() > 0)
{
return("点赞成功");
}
else
{
return("失败");
}
}
}
/// <summary>
/// 修改歌单信息
/// </summary>
/// <param name="s"></param>
/// <returns></returns>
internal static string UpSongMenuInfo(SongMenu s)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
SongMenu m = db.SongMenu.FirstOrDefault(sm => sm.M_Id == s.M_Id);
if (m != null)
{
m.M_Info = s.M_Info;
m.M_Name = s.M_Name;
m.M_Type = s.M_Type;
//m.M_UserId = s.M_UserId;
db.SaveChanges();
return("修改成功");
}
return("要修改的歌单不存在");
}
}
/// <summary>
/// 添加一个歌手信息 => 名字
/// </summary>
/// <param name="name">歌手名</param>
/// <returns>歌手id</returns>
public static int AddSinger(string name)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
Singer s = db.Singer.FirstOrDefault(m => m.S_Name == name);
if (s == null)
{
db.Singer.Add(new Singer()
{
S_Name = name
});
db.SaveChanges();
}
s = db.Singer.FirstOrDefault(m => m.S_Name == name);
return(s.S_Id);
}
}
/// <summary>
/// 获取动态个数
/// </summary>
/// <param name="uid"></param>
/// <returns></returns>
internal static int GetDynamicCount(int uid)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var user = db.UserInfo.FirstOrDefault(x => x.U_Id == uid);
if (user == null)
{
return(0);
}
var dyn = (from d in db.Dynamic
join u in db.UserInfo
on d.D_UserId equals u.U_Id
select new
{
D_Id = d.D_Id,
D_Title = d.D_Title,
D_UserId = d.D_UserId,
D_Content = d.D_Content,
D_GoodCount = d.D_GoodCount,
D_imgs = d.D_imgs,
D_SongID = d.D_SongID,
D_UpTime = d.D_UpTime,
U_info = new
{
U_Id = u.U_Id,
U_Img = u.U_Img,
U_Info = u.U_Info,
U_Like = u.U_Like,
U_Name = u.U_Name,
//U_Email = u.U_Email,
//U_Birthday = u.U_Birthday,
//U_Tell = u.U_Tell,
U_Fans = u.U_Fans,
U_Follow = u.U_Follow,
//U_Gender = u.U_Gender,
//U_Hobby = u.U_Hobby,
//U_RegistrationTime = u.U_RegistrationTime,
//U_CollectSongMenu = u.U_CollectSongMenu,
//U_CreatSongMenu = u.U_CreatSongMenu
}
}).Where(m => m.D_UserId == uid).ToList();
return(dyn.Count);
}
}
/// <summary>
/// 更新用户信息
/// </summary>
/// <param name="u"></param>
/// <returns></returns>
internal static UserInfo UpUserInfo(UserInfo u)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var user = db.UserInfo.FirstOrDefault(m => m.U_Id == u.U_Id);
if (user != null)
{
user.U_Id = u.U_Id;
user.U_Info = u.U_Info;
user.U_Name = u.U_Name;
user.U_Birthday = u.U_Birthday;
user.U_Tell = u.U_Tell;
user.U_Gender = u.U_Gender;
user.U_Hobby = u.U_Hobby;
}
db.SaveChanges();
return(SelUserInfoByID(u.U_Id));
}
}
/// <summary>
/// 将用户信息添加到歌手
/// </summary>
/// <param name="u">用户对象</param>
/// <returns></returns>
public static int AddSinger(UserInfo u)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
Singer s = db.Singer.FirstOrDefault(m => m.S_Name == u.U_Name);
if (s == null)
{
db.Singer.Add(new Singer()
{
S_Name = u.U_Name,
S_Img = u.U_Img,
S_Info = u.U_Info
});
db.SaveChanges();
}
s = db.Singer.FirstOrDefault(m => m.S_Name == u.U_Name);
return(s.S_Id);
}
}
/// <summary>
/// 获取子评论
/// </summary>
/// <param name="sid">动态id</param>
/// <param name="pid">评论id</param>
/// <returns></returns>
internal static object GetChildrenComm(int did, int pid)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var comm = (from c in db.DynamicComment
join u1 in db.UserInfo
on c.DC_From_User equals u1.U_Id
join u2 in db.UserInfo
on c.DC_To_User equals u2.U_Id
where c.DC_Dynamic == did
where c.DC_Pid == pid
select new
{
SM_Id = c.DC_Id,
SM_Pid = c.DC_Pid,
SM_Content = c.DC_Content,
SM_From_User = c.DC_From_User,
From_UserInfo = new
{
U_Id = u1.U_Id,
U_Img = u1.U_Img,
U_Info = u1.U_Info,
//U_Like = u1.U_Like,
U_Name = u1.U_Name,
U_Email = u1.U_Email
},
SM_To_User = c.DC_To_User,
To_UserInfo = new
{
U_Id = u2.U_Id,
U_Img = u2.U_Img,
U_Info = u2.U_Info,
//U_Like = u2.U_Like,
U_Name = u2.U_Name,
U_Email = u2.U_Email
},
SM_Menu = c.DC_Dynamic,
SM_UpTime = c.DC_UpTime
}).ToList();
return(comm);
}
}
/// <summary>
/// 添加一个歌曲信息 如果当前歌曲不存在 就添加 如果存在 就不添加
/// </summary>
/// <param name="s"></param>
/// <returns></returns>
public static bool AddSongList(SongList s)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
try
{
SongList song = db.SongList.FirstOrDefault(m => m.S_Name == s.S_Name && m.S_Singer == s.S_Singer && m.S_Url == s.S_Url && m.S_Cover == s.S_Cover);
if (song == null)
{
db.SongList.Add(s);
db.SaveChanges();
}
return(true);
}
catch (Exception)
{
return(false);
}
}
}
/// <summary>
/// 判断歌单用户是否已经收藏
/// </summary>
/// <param name="uid"></param>
/// <param name="mid"></param>
/// <returns></returns>
internal static bool IsCollect(int uid, int mid)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var u = db.UserInfo.FirstOrDefault(x => x.U_Id == uid);
if (u == null)
{
throw new Exception("用户不存在");
}
string[] songNumber = u.U_CollectSongMenu.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string item in songNumber)
{
if (item == mid.ToString())
{
return(true);
}
}
return(false);
}
}
/// <summary>
/// 用户的粉丝数据
/// </summary>
/// <param name="id"></param>
internal static object SelUserFans(int id)
{
using (YoungMusicEntities db = new YoungMusicEntities())
{
var userInfo = db.UserInfo.FirstOrDefault(m => m.U_Id == id);
if (userInfo != null)
{
if (userInfo.U_Fans == null)
{
userInfo.U_Fans = "";
}
string[] number = userInfo.U_Fans?.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
List <int> list = new List <int>();
foreach (string item in number)
{
list.Add(int.Parse(item));
}
var u = (from user in db.UserInfo
select new
{
U_Id = user.U_Id,
U_Img = user.U_Img,
U_Info = user.U_Info,
U_Like = user.U_Like,
U_Name = user.U_Name,
U_Email = user.U_Email,
U_Birthday = user.U_Birthday,
U_Tell = user.U_Tell,
U_Fans = user.U_Fans,
U_Follow = user.U_Follow,
U_Gender = user.U_Gender,
U_Hobby = user.U_Hobby,
U_RegistrationTime = user.U_RegistrationTime,
U_CollectSongMenu = user.U_CollectSongMenu,
U_CreatSongMenu = user.U_CreatSongMenu
}).Where(m => list.Contains(m.U_Id)).ToList();
return(u);
}
}
return(null);
}