public Tuple <int, List <SongBookEntity> > GetUploadSongListBySingerId(SingerSongPageParam param)
{
string sql = string.Format(@"
select top {0} * from (select row_number() over(order by UploadTime desc) as rownumber,
* from SongBook where SingerId={2} and Status=1 and (AuditStatus=0 or AuditStatus=1 or AuditStatus=3)
) temp_row
where temp_row.rownumber>(({1}-1)*{0});", param.PageSize, param.PageIndex, param.SingerId);
int count = Convert.ToInt32(helper.QueryScalar($@"select Count(1) from SongBook where SingerId={param.SingerId} and Status=1 and (AuditStatus=0 or AuditStatus=1 or AuditStatus=3)"));
return(Tuple.Create(count, helper.Query <SongBookEntity>(sql).ToList()));
}
public Tuple <int, List <SongBookEntity> > GetSongListBySingerId(SingerSongPageParam pageParam)
{
string sql = string.Format(@"
select top {0} * from (select row_number() over(order by {3} d.UploadTime desc) as rownumber,
* from SongBook d left join (select COUNT(a.Id) as PlayTimes , Sum(b.BroadcastTime) as TotalPlayTime ,a.Id as tempId
from SongPlayRecord b left join SongBook a on a.Id = b.SongId where a.Status=1 and a.AuditStatus=2
group by a.Id) c on c.tempId = d.Id where d.Status=1 and d.SingerId={2}) temp_row
where temp_row.rownumber>(({1}-1)*{0});", pageParam.PageSize, pageParam.PageIndex, pageParam.SingerId,
string.IsNullOrWhiteSpace(pageParam.OrderField) ? string.Empty : ("c." + pageParam.OrderField + " " + pageParam.OrderType + ","));
int count = Convert.ToInt32(helper.QueryScalar($@"select Count(1) from SongBook where Status=1 and SingerId= {pageParam.SingerId}"));
return(Tuple.Create(count, helper.Query <SongBookEntity>(sql).ToList()));
}
public ActionResult GetSongRecordBySingerId()
{
var page = GetIntValFromReq("page");
var limit = GetIntValFromReq("limit");
int singerId = GetIntValFromReq("singerId");
SingerSongPageParam param = new SingerSongPageParam
{
PageIndex = page,
PageSize = limit,
SingerId = singerId,
};
var result = singerStatisticsRepository.GetSongRecordBySingerId(param);
return(Json(new { code = 0, msg = string.Empty, count = result.Item1, data = result.Item2 }, JsonRequestBehavior.AllowGet));
}
public Tuple <int, List <SongPlayRecordEntity> > GetSongRecordBySingerId(SingerSongPageParam param)
{
string sql = string.Format(@"
select top {0} * from (select row_number() over(order by a.BeginPlayTime desc) as rownumber,
a.StoreCode,BeginPlayTime,b.SongName,b.SongLength,d.StoreName,e.StoreTypeName from SongPlayRecord a left join SongBook b
on a.SongId = b.Id
left join [User] c on c.StoreCode=a.StoreCode and c.IsMain=1 left join StoreDetailInfo d
on d.UserId=c.Id left join StoreType e on e.Id= d.StoreTypeId
where b.SingerId = {2}
) temp_row
where temp_row.rownumber>(({1}-1)*{0});", param.PageSize, param.PageIndex, param.SingerId);
int count = Convert.ToInt32(helper.QueryScalar($@"select Count(1) from SongPlayRecord a left join
SongBook b on a.SongId=b.Id where b.Status=1 and b.AuditStatus=2 and b.SingerId={param.SingerId};"));
return(Tuple.Create(count, helper.Query <SongPlayRecordEntity>(sql).ToList()));
}
public ActionResult GetPublishSongListBySingerId()
{
var page = GetIntValFromReq("page");
var limit = GetIntValFromReq("limit");
int singerId = GetIntValFromReq("singerId");
var order = GetStrValFromReq("order");
string field = GetStrValFromReq("field");
SingerSongPageParam param = new SingerSongPageParam
{
PageIndex = page,
PageSize = limit,
SingerId = singerId,
OrderType = order,
OrderField = field
};
var result = singerStatisticsRepository.GetPublishSongListBySingerId(param);
return(Json(new { code = 0, msg = string.Empty, count = result.Item1, data = result.Item2 }, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 根据歌手Id，分页获取音乐
/// </summary>
/// <returns></returns>
public ActionResult GetSongListBySingerId()
{
var page = GetIntValFromReq("page"); //页码
var limit = GetIntValFromReq("limit"); //每页数据
var singerId = GetIntValFromReq("singerId");
var field = GetStrValFromReq("field");
var order = GetStrValFromReq("order");
var param = new SingerSongPageParam
{
PageIndex = page,
PageSize = limit,
SingerId = singerId,
OrderField = field,
OrderType = order
};
var result = songBookRepository.GetSongListBySingerId(param);
return(Json(new { code = 0, msg = "", count = result.Item1, data = result.Item2 }, JsonRequestBehavior.AllowGet));
}
