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.EnumConst;
using GxPress.Repository.Interface;
using GxPress.Repository.Interface.Media;
using GxPress.Result.DataCenter;
using GxPress.Service.Interface.PlatformData;

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

        private readonly IMediaRepository mediaRepository;

        public PlatformDataService(IUserRepository userRepository, IMediaRepository mediaRepository)
        {

            this.userRepository = userRepository;
            this.mediaRepository = mediaRepository;
        }
        /// <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);
        }
        /// <summary>
        /// 内容类型分布图
        /// </summary>
        /// <returns></returns>
        public async Task<List<ContentTypeDistributingResult>> GetContentTypeDistributingResults()
        {
            var result = new List<ContentTypeDistributingResult>();
            var connectionString = ConfigHelper.GetValue("Database:ConnectionString");
            var database = new Database(DatabaseType.MySql, connectionString);
            var connection = database.GetConnection();
            foreach (ResourceTypeConst item in Enum.GetValues(typeof(ResourceTypeConst)))
            {
                var model = new ContentTypeDistributingResult();
                model.Name = item.GetDescriptionOriginal();

                var sql = $"select count(1) from tede_media where MediaType={item.GetHashCode()}";
                model.Value = await connection.ExecuteScalarAsync<int>(sql);
                result.Add(model);
            }
            foreach (AttachTypeConst item in Enum.GetValues(typeof(AttachTypeConst)))
            {
                var model = new ContentTypeDistributingResult();
                model.Name = item.GetDescriptionOriginal();
                var sql = $"select count(1) from tede_media where AttachId={item.GetHashCode()}";
                model.Value = await connection.ExecuteScalarAsync<int>(sql);
                result.Add(model);
            }
            return result;
        }
        /// <summary>
        /// 收费内容统计图
        /// </summary>
        /// <returns></returns>
        public async Task<PayContentstatisticsResult> GetPayContentstatisticsResult()
        {
            var result = new PayContentstatisticsResult();
            result.PageAdata = new List<int>();
            result.PageBdata = new List<int>();
            result.Title = new List<string>();
            var connectionString = ConfigHelper.GetValue("Database:ConnectionString");
            var database = new Database(DatabaseType.MySql, connectionString);
            var connection = database.GetConnection();
            foreach (ResourceTypeConst item in Enum.GetValues(typeof(ResourceTypeConst)))
            {

                result.Title.Add(item.GetDescriptionOriginal());
                var sql = $"select count(1) from tede_media where MediaType={item.GetHashCode()} and FreeProportion>0";
                result.PageAdata.Add(await connection.ExecuteScalarAsync<int>(sql));
                sql = $"select count(1) from tede_media where MediaType={item.GetHashCode()} and FreeProportion=0";
                result.PageBdata.Add(await connection.ExecuteScalarAsync<int>(sql));
            }
            foreach (AttachTypeConst item in Enum.GetValues(typeof(AttachTypeConst)))
            {
                result.Title.Add(item.GetDescriptionOriginal());
                var sql = $"select count(1) from tede_media where AttachId={item.GetHashCode()} and FreeProportion>0";
                result.PageAdata.Add(await connection.ExecuteScalarAsync<int>(sql));
                sql = $"select count(1) from tede_media where MediaType={item.GetHashCode()} and FreeProportion=0";

                result.PageBdata.Add(await connection.ExecuteScalarAsync<int>(sql));
            }
            return result;
        }
        /// <summary>
        /// 内容增长情况图
        /// </summary>
        /// <returns></returns>
        public async Task<ContentIncreaseResult> GetContentIncreaseResult()
        {
            var result = new ContentIncreaseResult();
            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 mediaRepository.CountAsync(nowTime.AddMonths(-i).ToString("yyyy-MM-01"), nowTime.AddMonths(-i + 1).ToString("yyyy-MM-01")));
            }
            return result;
        }
        /// <summary>
        ///  平台累计访问数据(次)
        /// </summary>
        /// <returns></returns>
        public async Task<PlatformAccumulativeVisitResult> GetPlatformAccumulativeVisitResult()
        {

            var sql = @"
                        SELECT 
                            (SELECT 
                                    SUM(ReadCount)
                                FROM
                                    tede_media) AS VisitCount,
                            (SELECT 
                                    SUM(CommentCount)
                                FROM
                                    tede_media) AS CommentCount,
                            (SELECT 
                                    SUM(RetransmissionCount)
                                FROM
                                    tede_media) AS RetransmissionCount,
                                    (SELECT 
                                    SUM(CollectCount)
                                FROM
                                    tede_media) AS CollctionCount";
            var connectionString = ConfigHelper.GetValue("Database:ConnectionString");
            var database = new Database(DatabaseType.MySql, connectionString);
            var connection = database.GetConnection();
            return await connection.QueryFirstAsync<PlatformAccumulativeVisitResult>(sql);
        }
        /// <summary>
        /// 平台商务数据
        /// </summary>
        /// <returns></returns>
        public async Task<PlatformCommerceResult> GetPlatformCommerceResult()
        {
            var sql = @"SELECT 
                        (SELECT 
                                SUM(Price)
                            FROM
                                tede_order) AS Amount,
                        (SELECT 
                                COUNT(1)
                            FROM
                                tede_order) AS OrderCount,
                        (SELECT 
                                COUNT(1)
                            FROM
                                tede_user) AS VipUserCount,
                        (SELECT 
                                SUM(Price)
                            FROM
                                tede_order
                            WHERE
                                IsVip = 1) AS VipAmount,
                         (SELECT 
                                COUNT(1)
                            FROM
                                tede_order
                            WHERE
                                IsVip = 1) AS VipOrderCount";
            var connectionString = ConfigHelper.GetValue("Database:ConnectionString");
            var database = new Database(DatabaseType.MySql, connectionString);
            var connection = database.GetConnection();
            return await connection.QueryFirstAsync<PlatformCommerceResult>(sql);
        }
        /// <summary>
        /// 内容销售排行榜
        /// </summary>
        /// <returns></returns>
        public async Task<List<ContentSaleRankingResult>> GetContentSaleRankingResults()
        {
            var result = new List<ContentSaleRankingResult>();
            foreach (ResourceTypeConst item in Enum.GetValues(typeof(ResourceTypeConst)))
            {
                result.Add(new ContentSaleRankingResult { CategoryName = item.GetDescriptionOriginal(), MediaType = item.GetHashCode() });
            }
            foreach (AttachTypeConst item in Enum.GetValues(typeof(AttachTypeConst)))
            {
                result.Add(new ContentSaleRankingResult { CategoryName = item.GetDescriptionOriginal(), MediaType = item.GetHashCode() });
            }
            var connectionString = ConfigHelper.GetValue("Database:ConnectionString");
            var database = new Database(DatabaseType.MySql, connectionString);
            var connection = database.GetConnection();
            var sql = @"SELECT 
                            MediaType,
                            SUM(SellAmount) AS Amount,
                            (SUM(SellAmount) / (SELECT 
                                    SUM(SellAmount)
                                FROM
                                    tede_media)) AS Proportion
                        FROM
                            tede_media
                        GROUP BY MediaType";
            var list = await connection.QueryAsync<ContentSaleRankingResult>(sql);
            foreach (var item in list)
            {
                foreach (var dom in result)
                {
                    if (dom.MediaType == item.MediaType)
                    {
                        dom.Amount = dom.Amount;
                        dom.Proportion = dom.Proportion;
                    }
                }
            }
            sql = @"SELECT 
                            AttachType as MediaType,     
                            SUM(SellAmount) AS Amount,
                            (SUM(SellAmount) / (SELECT 
                                    SUM(SellAmount)
                                FROM
                                    tede_media)) AS Proportion     
                        FROM
                            tede_media where AttachId>0
                        GROUP BY AttachType";
            list = await connection.QueryAsync<ContentSaleRankingResult>(sql);
            foreach (var item in list)
            {
                foreach (var dom in result)
                {
                    if (dom.MediaType == item.MediaType)
                    {
                        dom.Amount = dom.Amount;
                        dom.Proportion = dom.Proportion;
                    }
                }
            }
            return result;
        }
        /// <summary>
        /// 内容分类销售占比
        /// </summary>
        /// <returns></returns>
        public async Task<List<ContentSaleProportionResult>> GetContentSaleProportionResults()
        {
            var result = new List<ContentSaleProportionResult>();
            foreach (ResourceTypeConst item in Enum.GetValues(typeof(ResourceTypeConst)))
            {
                result.Add(new ContentSaleProportionResult { Name = item.GetDescriptionOriginal(), MediaType = item.GetHashCode() });
            }
            foreach (AttachTypeConst item in Enum.GetValues(typeof(AttachTypeConst)))
            {
                result.Add(new ContentSaleProportionResult { Name = item.GetDescriptionOriginal(), MediaType = item.GetHashCode() });
            }
            var connectionString = ConfigHelper.GetValue("Database:ConnectionString");
            var database = new Database(DatabaseType.MySql, connectionString);
            var connection = database.GetConnection();
            var sql = @"SELECT 
                            MediaType,
                            (SUM(SellAmount) / (SELECT 
                                    SUM(SellAmount)
                                FROM
                                    tede_media)) AS Value
                        FROM
                            tede_media
                        GROUP BY MediaType";
            var list = await connection.QueryAsync<ContentSaleProportionResult>(sql);
            foreach (var item in list)
            {
                foreach (var dom in result)
                {
                    if (dom.MediaType == item.MediaType)
                    {
                        dom.Value = dom.Value;
                    }
                }
            }
            sql = @"SELECT 
                            AttachType as MediaType,     
                            (SUM(SellAmount) / (SELECT 
                                    SUM(SellAmount)
                                FROM
                                    tede_media)) AS Proportion     
                        FROM
                            tede_media where AttachId>0
                        GROUP BY AttachType";
            list = await connection.QueryAsync<ContentSaleProportionResult>(sql);
            foreach (var item in list)
            {
                foreach (var dom in result)
                {
                    if (dom.MediaType == item.MediaType)
                    {
                        dom.Value = dom.Value;
                    }
                }
            }
            return result;
        }
    }
}