tede/gx_api/GxPress/Service/GxPress.Service.Implement/PlatformData/PlatformDataService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using Datory;
using GxPress.Common.Tools;
using GxPress.Repository.Interface;
using GxPress.Result.DataCenter;
using GxPress.Service.Interface.PlatformData;

namespace GxPress.Service.Implement.PlatformData
{
    public class PlatformDataService : IPlatformDataService
    {
        private readonly IUserRepository userRepository;

        public PlatformDataService(IUserRepository userRepository)
        {

            this.userRepository = userRepository;
        }
        /// <summary>
        /// 平台运营数据
        /// </summary>
        /// <returns></returns>
        public async Task<PlatformOperationDataResult> GetPlatformDataAsync()
        {
            var newUserTime = DateTime.Now.ToString("yyyy-MM-dd");
            var onlineUserTime = DateTime.Now.AddMinutes(-10).ToString("yyyy-MM-dd HH:mm:ss");
            var activityUserTime = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd HH:mm:ss");
            var sql = $@"SELECT 
                            (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_user) AS SumUserCount,
                            (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_user
                                WHERE
                                    CreatedDate > '{newUserTime}') AS NewUserCount,
                            (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_user
                                WHERE
                                    LoginTime > '{onlineUserTime}') AS OnlineUserCount,
                            (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_user
                                WHERE
                                    LoginTime > '{activityUserTime}') AS ActivityUserCount";
            var connectionString = ConfigHelper.GetValue("Database:ConnectionString");
            var database = new Database(DatabaseType.MySql, connectionString);
            var connection = database.GetConnection();
            var result = await connection.QueryFirstAsync<PlatformOperationDataResult>(sql);
            return result;
        }
        /// <summary>
        /// 用户总数增长趋势
        /// </summary>
        /// <returns></returns>
        public async Task<UserIncreaseResult> GetUserIncreaseResult()
        {
            var result = new UserIncreaseResult();
            result.MonthData = new List<string>();
            result.ExpectedData = new List<int>();
            var nowTime = DateTime.Now;
            for (int i = 0; i < 12; i++)
            {
                result.MonthData.Add(nowTime.AddMonths(-i).Month + "月");
                result.ExpectedData.Add(await userRepository.GetUserCount(nowTime.AddMonths(-i).ToString("yyyy-MM-01"), nowTime.AddMonths(-i + 1).ToString("yyyy-MM-01")));
            }
            return result;
        }
        /// <summary>
        /// 用户地区分布
        /// </summary>
        /// <value></value>
        public async Task<List<UserAreaDistributingResult>> GetUserAreaDistributingResults()
        {
            var sql = "SELECT province as Name,count(1) as Value FROM ccpph_tede.tede_user group by province";
            var connectionString = ConfigHelper.GetValue("Database:ConnectionString");
            var database = new Database(DatabaseType.MySql, connectionString);
            var connection = database.GetConnection();
            var list = await connection.QueryAsync<UserAreaDistributingResult>(sql);
            foreach (var item in list)
            {
                if (string.IsNullOrEmpty(item.Name))
                    item.Name = "未知";
            }
            return list.ToList();
        }
        /// <summary>
        /// 会员用户占比
        /// </summary>
        /// <returns></returns>
        public async Task<UserVipProportionResult> GetUserVipProportionResult()
        {
            var sql = "SELECT (select count(1) from tede_user where IsVip=1)/(select count(1) from tede_user) as value";
            var connectionString = ConfigHelper.GetValue("Database:ConnectionString");
            var database = new Database(DatabaseType.MySql, connectionString);
            var connection = database.GetConnection();
            var result = await connection.QueryFirstAsync<UserVipProportionResult>(sql);
            return result;
        }
        /// <summary>
        /// 实时在线人数
        /// </summary>
        /// <returns></returns>
        public async Task<OnlineUserResult> GetOnlineUserResult()
        {
            var result = new OnlineUserResult();
            result.ActualData = new List<int>();
            result.ExpectedData = new List<int>();
            //时间
            result.MonthData = new List<string>();
            var connectionString = ConfigHelper.GetValue("Database:ConnectionString");
            var database = new Database(DatabaseType.MySql, connectionString);
            var connection = database.GetConnection();
            var nowTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));
            for (int i = 0; i <= 6; i++)
            {
                var monthData = "";
                if ((i * 4).ToString().Length < 2)
                    monthData = "0";
                monthData += (i * 4).ToString() + ":00";
                result.MonthData.Add(monthData);
                var sql = $"SELECT count(1) FROM tede_user_login where CreatedDate>='{nowTime.AddHours(i - 4).ToString("yyyy-MM-dd hh:mm:ss")}' and CreatedDate<='{nowTime.AddHours(i).ToString("yyyy-MM-dd hh:mm:ss")}'";
                result.ActualData.Add(await connection.ExecuteScalarAsync<int>(sql));
                sql = $"SELECT count(1) FROM tede_user_login where CreatedDate>='{nowTime.AddDays(-1).AddHours(i - 4).ToString("yyyy-MM-dd hh:mm:ss")}' and CreatedDate<='{nowTime.AddDays(-1).AddHours(i).ToString("yyyy-MM-dd hh:mm:ss")}'";
                result.ExpectedData.Add(await connection.ExecuteScalarAsync<int>(sql));
            }
            return result;
        }
        /// <summary>
        /// 平台内容数据
        /// </summary>
        /// <returns></returns>
        public async Task<PlatformContentDataResult> GetPlatformContentDataResult()
        {
            var connectionString = ConfigHelper.GetValue("Database:ConnectionString");
            var database = new Database(DatabaseType.MySql, connectionString);
            var connection = database.GetConnection();
            var nowTime = DateTime.Now.ToString("yyyy-MM-dd");
            var sql = $@"SELECT 
                            (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_media) AS SumContentCount,
                            (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_media
                                WHERE
                                    CreatedDate > '{nowTime}') AS NewContentCount,
                            (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_media
                                WHERE
                                IsChecked=1 and IsDelete=0) AS OnlineContentCount,
                                (SELECT 
                                    COUNT(1)
                                FROM
                                    tede_media
                                WHERE
                                FreeProportion>0) AS PayContentCount";
            return await connection.QueryFirstAsync<PlatformContentDataResult>(sql);
        }
    }
}