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