/// <summary> /// 统计音乐人的各类作品数量 列表 /// </summary> /// <returns></returns> public List <SingerListStatisticsEntity> GetSingerStatisticsList(StorePlaySongPageParam param) { string order = string.Empty; //switch (param.OrderField) //{ // case "PlaySongCount": // order = "COUNT(distinct b.SongId)"; // break; // case "PlayStoreCount": // order = "COUNT( b.PlayUserId) "; // break; //} string sql = $@"select top {param.PageSize} * from (select row_number() over(order by ee.{param.OrderField} desc) as rownumber, u.UserId as SingerId,u.SingerName,r.UploadCount,r.PublishCount,ee.PlaySongCount,ee.PlayStoreCount from (select distinct UserId from LoginLog where LoginTime>'{param.BeginDate}' and LoginTime<'{param.EndDate}' and UserType=1) s left join [SingerDetailInfo] u on u.UserId = s.UserId left join (select a.SingerId, SUM(CASE WHEN a.AuditStatus in ('0','1','2','3') THEN 1 ELSE 0 END) AS UploadCount, SUM(CASE a.AuditStatus WHEN '2' THEN 1 ELSE 0 END) AS PublishCount from SongBook a group by a.SingerId having a.SingerId is not null) r on r.SingerId=s.UserId left join (select song.SingerId,Count(distinct song.Id) as PlaySongCount,COUNT(distinct StoreCode) as PlayStoreCount from SongPlayRecord record left join SongBook song on record.SongId=song.Id group by song.SingerId) ee on u.UserId=ee.SingerId ) temp_row where temp_row.rownumber>(({param.PageIndex}-1)*{param.PageSize}) ;"; return(helper.Query <SingerListStatisticsEntity>(sql).ToList()); }
/// <summary> /// 获取指定商家的播放记录 /// </summary> /// <param name="param"></param> /// <returns></returns> public Tuple <int, List <StorePlaySongEntity> > GetStorePlaySongList(StorePlaySongPageParam param) { string order = string.Empty; switch (param.OrderField) { case "PlayTime": order = "Sum(BroadcastTime)"; break; case "PlayCount": order = "count(1)"; break; } string sql = $@" select top {param.PageSize} * from (select row_number() over(order by {order} desc) as rownumber, a.SongId, b.SongName,Sum(BroadcastTime) as PlayTime,count(1) as PlayCount from [dbo].[SongPlayRecord] a left join SongBook b on a.SongId = b.Id where a.BeginPlayTime >= '{param.BeginDate}' and a.BeginPlayTime <='{param.EndDate}' and a.StoreCode='{param.StoreCode}' group by a.SongId,b.SongName ) temp_row where temp_row.rownumber>(({param.PageIndex}-1)*{param.PageSize}) ;"; var count = helper.QueryScalar($@"select Count(1) from SongBook b where Id in (select SongId from [SongPlayRecord] a where a.BeginPlayTime >= '{param.BeginDate}' and a.BeginPlayTime <='{param.EndDate}' and a.StoreCode='{param.StoreCode}')"); return(Tuple.Create(Convert.ToInt32(count), helper.Query <StorePlaySongEntity>(sql).ToList())); }
public ActionResult GetStoreStatisticsList() { var page = GetIntValFromReq("page"); //页码 var limit = GetIntValFromReq("limit"); //每页数据 DateTime begin = GetDateTimeValFromReq("beginDate").Value; DateTime end = GetDateTimeValFromReq("endDate").Value.AddDays(1).AddSeconds(-1); string field = GetStrValFromReq("field"); StorePlaySongPageParam param = new StorePlaySongPageParam { PageIndex = page, PageSize = limit, OrderField = field, BeginDate = begin, EndDate = end }; var result = storeStatisticsRepository.GetStorePlaySongInfo(param); return(Json(new { code = 0, msg = string.Empty, count = result.Item1, data = result.Item2 }, JsonRequestBehavior.AllowGet)); }
/// <summary> /// 获取商家播放歌曲列表信息 /// </summary> /// <returns></returns> public Tuple <int, List <StoreSongStatisticsEntity> > GetStorePlaySongInfo(StorePlaySongPageParam param) { string order = string.Empty; //switch (param.OrderField) //{ // case "PlayTime": // order = "r.Sum(BroadcastTime)"; // break; // case "PlaySongCount": // order = "r.count( distinct a.SongId) "; // break; // case "PlayCount": // order = "r.count(1)"; // break; //} string sql = $@"select top {param.PageSize} * from (select row_number() over(order by g.{param.OrderField} desc) as rownumber, s.*, d.Id,f.StoreName,g.PlayCount,g.PlaySongCount,g.PlayTime from (select distinct StoreCode from LoginLog where UserType=2 and LoginTime >= '{param.BeginDate}' and LoginTime <= '{param.EndDate}') s left join (select count( distinct a.SongId) as PlaySongCount, a.StoreCode,Sum(BroadcastTime) as PlayTime,count(1) as PlayCount from [dbo].[SongPlayRecord] a left join [User] c on c.StoreCode=a.StoreCode left join StoreDetailInfo b on c.Id = b.UserId where c.UserType=2 and c.IsMain=1 and a.BeginPlayTime >= '{param.BeginDate}' and a.BeginPlayTime <= '{param.EndDate}' group by a.StoreCode,b.StoreName) g on s.StoreCode = g.StoreCode left join [User] d on d.StoreCode=s.StoreCode and d.UserType=2 and d.IsMain=1 left join StoreDetailInfo f on f.UserId=d.Id ) temp_row where temp_row.rownumber>(({param.PageIndex}-1)*{param.PageSize}) ;"; var count = helper.QueryScalar($@" select Count(distinct StoreCode) from LoginLog where UserType=2 and LoginTime >= '{param.BeginDate}' and LoginTime <= '{param.EndDate}'"); return(Tuple.Create(Convert.ToInt32(count), helper.Query <StoreSongStatisticsEntity>(sql).ToList())); }