/// <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));
}
