/// <summary> /// 添加歌单 /// </summary> /// <returns></returns> public bool AddSongList(PlayListEntity playList) { string sql = $@"insert into PlayList ([ListName],[ListContent],StoreId,[StoreCode],[IsPublish],[UpdateTime],[Status]) values ('{playList.ListName}','{playList.ListContent}',{playList.StoreId},'{playList.StoreCode}','{playList.IsPublish}','{DateTime.Now}','{1}')"; return(helper.Execute(sql) > 0 ? true : false); }
/// <summary> /// 更新歌单 /// </summary> /// <param name="songSheet"></param> /// <returns></returns> public bool UpdateSongSheetById(PlayListEntity songSheet) { string sql = $@"update PlayList set ListName='{songSheet.ListName}',StoreId={songSheet.StoreId}, ListContent='{songSheet.ListContent}',UpdateTime='{DateTime.Now}' where Id={songSheet.Id}"; return(helper.Execute(sql) > 0 ? true : false); }
/// <summary> /// 更新歌单 /// </summary> /// <returns></returns> public bool UpdateSongList(PlayListEntity playList) { // string sql = $@"update PlayList set [ListName]='{playList.ListName}',[ListContent]='{playList.ListContent}', //[StoreCode]='{playList.StoreCode}',[IsPublish]='{playList.IsPublish}',[UpdateTime]='{DateTime.Now}') ;"; string sql = $@"update PlayList set [ListContent]='{playList.ListContent}', [UpdateTime]='{DateTime.Now}' where Id = {playList.Id} ;"; return(helper.Execute(sql) > 0 ? true : false); }
public ResponseResultDto <int> AddSongSheet(PlayListEntity songSheet) { var result = songSheetApiRepository.AddSongSheet(songSheet); return(new ResponseResultDto <int> { IsSuccess = result > 0 ? true : false, ErrorMessage = string.Empty, Result = result //返回新添加的歌单的Id,便于直接查看歌单详情 }); }
/// <summary> /// 添加歌单 /// </summary> /// <param name="songSheet"></param> /// <returns>新增加的歌单的Id</returns> public int AddSongSheet(PlayListEntity songSheet) { var p = new DynamicParameters(); p.Add("@Id", dbType: DbType.Int32, direction: ParameterDirection.Output); var result = helper.Execute($@"insert into PlayList (ListName,ListContent,StoreId,StoreCode,IsPublish,UpdateTime,Status) values ('{songSheet.ListName}','{songSheet.ListContent}',{songSheet.StoreId},'{songSheet.StoreCode}', {1},'{DateTime.Now}',{1}); SELECT @Id=SCOPE_IDENTITY()", p); return(p.Get <int>("@Id")); }
public ActionResult UpdateSongList(int id, string listContent) { PlayListEntity playListEntity = new PlayListEntity { Id = id, ListContent = listContent }; var result = playListRepository.UpdateSongList(playListEntity); return(Json(new { status = result }, JsonRequestBehavior.AllowGet)); }
public ActionResult AppendSongList(int id, string listContent, string storeCode, int storeId) { PlayListEntity playListEntity = new PlayListEntity { Id = id, ListContent = listContent, StoreCode = storeCode, StoreId = storeId }; var result = playListRepository.AppendSongList(playListEntity); return(Json(new { status = result.Item1, data = result.Item2 }, JsonRequestBehavior.AllowGet)); }
public ResponseResultDto <bool> UpdateSongSheetById(PlayListEntity songSheet) { if (songSheet.Id <= 0) { return(new ResponseResultDto <bool> { IsSuccess = false, ErrorMessage = "参数异常", Result = false }); } var result = songSheetApiRepository.UpdateSongSheetById(songSheet); return(new ResponseResultDto <bool> { IsSuccess = result, ErrorMessage = string.Empty, Result = result }); }
public Tuple <bool, int> AppendSongList(PlayListEntity playList) { if (playList.Id > 0) //更新歌单 { var t = helper.QueryScalar($@"select ListContent from PlayList where Id={playList.Id}; "); string sql = $@"update PlayList set [ListContent]='{playList.ListContent + t.ToString()}', [UpdateTime]='{DateTime.Now}' where Id = {playList.Id} ;"; return(Tuple.Create(helper.Execute(sql) > 0 ? true : false, playList.Id)); } else { // 添加新歌单 // string sql = $@"insert into PlayList(ListName,ListContent,StoreId,StoreCode,IsPublish,UpdateTime,Status) //values ('{string.Empty}','{playList.ListContent}',{playList.StoreId},'{playList.StoreCode}',{0},'{DateTime.Now}',{1})"; var p = new DynamicParameters(); p.Add("@Id", dbType: DbType.Int32, direction: ParameterDirection.Output); var result = helper.Execute($@"insert into PlayList(ListName,ListContent,StoreId,StoreCode,IsPublish,UpdateTime,Status) values ('{string.Empty}','{playList.ListContent}',{playList.StoreId},'{playList.StoreCode}',{0},'{DateTime.Now}',{1}); SELECT @Id=SCOPE_IDENTITY()", p); var id = p.Get <int>("@Id"); return(Tuple.Create(result > 0 ? true : false, id)); } }
/// <summary> /// 根据商家或分店Id,分页获取歌单中的歌曲列表 /// </summary> /// <param name="storeId"></param> /// <param name="param"></param> /// <returns></returns> public PagedResult <SongInfoParam> GetSongListByStoreId(int storeId, PageParam param) { PlayListEntity playListEntity = helper.Query <PlayListEntity>($@"select * from PlayList where StoreId={storeId}").FirstOrDefault(); if (playListEntity == null || string.IsNullOrWhiteSpace(playListEntity.ListContent)) { return(new PagedResult <SongInfoParam> { Page = param.PageIndex, PageSize = param.PageSize, Total = 0, Results = null }); } string[] arr = playListEntity.ListContent.Split(','); StringBuilder sb = new StringBuilder("b.Id in ("); List <string> tempList = new List <string>(); foreach (var item in arr) { if (!string.IsNullOrWhiteSpace(item)) { tempList.Add(item); sb.Append(item).Append(","); } } string whereIn = sb.ToString(); int length = whereIn.Length; whereIn = whereIn.Substring(0, length - 1); whereIn += ")"; if (tempList.Count == 0) { whereIn = string.Empty; return(new PagedResult <SongInfoParam> { Page = param.PageIndex, PageSize = param.PageSize, Total = 0, Results = null }); } string orderField = string.Empty; switch (param.OrderField) { case "PlayTime": orderField = "Sum(BroadcastTime)"; break; case "PlaySongCount": orderField = "count( distinct a.SongId) "; break; case "PlayCount": orderField = "count(a.SongId)"; break; } string order = string.IsNullOrWhiteSpace(orderField) ? string.Empty : ("c." + orderField + " " + param.OrderType + ","); string sql = $@" select top {param.PageSize} * from (select row_number() over(order by {order} b.Id desc) as rownumber, b.SongLength, b.Id, b.SongName,Sum(BroadcastTime) as PlayTime,count(a.SongId) as PlayCount from SongBook b left join SongPlayRecord a on a.SongId = b.Id where {whereIn} group by b.Id,b.SongName,b.SongLength ) temp_row where temp_row.rownumber>(({param.PageIndex}-1)*{param.PageSize}) ;"; var count = helper.QueryScalar($@"select Count(1) from SongBook b left join SongPlayRecord a on a.SongId = b.Id where {whereIn} group by b.Id,b.SongName,b.SongLength"); var result = helper.Query <SongInfoParam>(sql).ToList(); return(new PagedResult <SongInfoParam> { Page = param.PageIndex, PageSize = param.PageSize, Total = Convert.ToInt32(count), Results = result }); }
/// <summary> /// 获取商家后台歌单,当不存在未发布歌单时,则为最新发布的歌单 /// </summary> /// <param name="storeId"></param> /// <returns></returns> public Tuple <bool, string, PlayListEntity, List <SongBookEntity> > GetStoreSongListForAdmin(int storeId) { bool status = false; string message = string.Empty; string updateTimeStr = string.Empty; PlayListEntity listItem = new PlayListEntity(); List <SongBookEntity> list = new List <SongBookEntity>(); //string storeCode = helper.QueryScalar($@"select StoreCode from [User] where Id={storeId}").ToString(); string sql = $@"select * from PlayList where StoreId='{storeId}' and Status=1"; var result = helper.Query <PlayListEntity>(sql).ToList(); if (result.Count > 0) { string songListStr = string.Empty; var unPublish = result.Where(a => { return(a.IsPublish == false); }).OrderByDescending(a => a.UpdateTime).FirstOrDefault(); if (unPublish != null) { songListStr = unPublish.ListContent; updateTimeStr = unPublish.UpdateTime.ToString("yyyy-MM-dd HH:mm:ss"); listItem = unPublish; //else //{ // var temp = result.OrderByDescending(a => a.UpdateTime).FirstOrDefault(); // songListStr = temp.ListContent; // updateTimeStr = temp.UpdateTime.ToString("yyyy-MM-dd HH:mm:ss"); // listItem = temp; //} string[] arr = songListStr.Split(','); StringBuilder sb = new StringBuilder("select * from SongBook where Id in ("); foreach (var item in arr) { if (!string.IsNullOrWhiteSpace(item)) { sb.Append(item).Append(","); } } string resultSql = sb.ToString(); int length = resultSql.Length; resultSql = resultSql.Substring(0, length - 1); resultSql += ");"; try { list = helper.Query <SongBookEntity>(resultSql).ToList(); } catch (Exception) { } } else { //该商家没有后台歌单 status = false; message = "该商家没有歌单"; //listItem = new PlayListEntity(); } } else { status = false; message = "该商家没有歌单"; } return(Tuple.Create(status, message, listItem, list)); }