tede/gx_api/GxPress/Service/GxPress.Service.Implement/AppHomePage/AppHomePageService.Search.cs

using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Dapper;
using Datory;
using GxPress.Request.Navigation;
using GxPress.Result.Navigation;

namespace GxPress.Service.Implement.AppHomePage
{
    public partial class AppHomePageService
    {
        /// <summary>
        /// 获取搜索作者
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<NavigationSearchTeacherResult>> GetNavigationSearchResultAysnc(int categoryId)
        {
            var result = new List<NavigationSearchTeacherResult>();
            var database = new Database(DatabaseType.MySql, Common.Tools.ConfigHelper.GetValue("Database:ConnectionString"));
            var connection = database.GetConnection();
            var sql = $"SELECT TeacherId,Author,count(1) as Count FROM tede_media where id in(select MediaId from tede_system_lable_media where TypeValue=1) and  Author is not null group by Author,TeacherId";
            if (categoryId > 0)
                sql = $"SELECT TeacherId,Author,count(1) as Count FROM tede_media where id in(select MediaId from tede_system_lable_media where TypeValue=1 and lableId={categoryId}) and  Author is not null group by Author,TeacherId";
            return await connection.QueryAsync<NavigationSearchTeacherResult>(sql);
        }
        /// <summary>
        /// 获取出版社
        /// </summary>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public async Task<IEnumerable<string>> GetNavigationPressAsync(int categoryId)
        {
            var sql = "SELECT Press FROM tede_media where id in(select MediaId from tede_system_lable_media where TypeValue=1) and  Press is not null group by Press";
            if (categoryId > 0)
                sql = "SELECT Press FROM tede_media where id in(select MediaId from tede_system_lable_media where TypeValue=1 and lableId={categoryId}) and  Press is not null group by Press";
            var result = new List<NavigationSearchTeacherResult>();
            var database = new Database(DatabaseType.MySql, Common.Tools.ConfigHelper.GetValue("Database:ConnectionString"));
            var connection = database.GetConnection();
            return await connection.QueryAsync<string>(sql);
        }
        /// <summary>
        /// 获取年份数据
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<IEnumerable<NavigationYearResult>> GetNavigationYearResult(NavigationYearRequest request)
        {
            var result = new List<NavigationYearResult>();
            var database = new Database(DatabaseType.MySql, Common.Tools.ConfigHelper.GetValue("Database:ConnectionString"));
            var connection = database.GetConnection();
            var str = "";
            if (request.CategoryId > 0)
                str = $" and LableId={request.CategoryId} ";
            var maxYearSql = $"select max(AddDate) from tede_media where id in( SELECT MediaId FROM ccpph_tede.tede_system_lable_media where typevalue=1 {str})";
            var maxYear = await connection.ExecuteScalarAsync<string>(maxYearSql);
            var minYearSql = $"select min(AddDate) from tede_media where id in( SELECT MediaId FROM ccpph_tede.tede_system_lable_media where typevalue=1 {str})";
            var minYear = await connection.ExecuteScalarAsync<string>(minYearSql);
            //
            if (DateTime.TryParse(maxYear, out var maxYearDate) && DateTime.TryParse(minYear, out var minYearDate))
            {
                var count = (maxYearDate.Year - minYearDate.Year);
                if (count > 0)
                {

                    for (int i = 0; i < count; i++)
                    {
                        var sql = $"select count(1) from tede_media where AddDate>'{minYearDate.Year + i}' and AddDate<'{minYearDate.Year + (i + 1)}' and  id in( SELECT MediaId FROM ccpph_tede.tede_system_lable_media where typevalue=1)";
                        var number = await connection.ExecuteScalarAsync<int>(sql);
                        result.Add(new NavigationYearResult() { Year = (minYearDate.Year + i) + "年", Count = number });
                    }
                }
                else
                {
                    var sql = $"select count(1) from tede_media where AddDate>'{minYearDate.Year}' and AddDate<'{minYearDate.Year + 1}' and  id in( SELECT MediaId FROM ccpph_tede.tede_system_lable_media where typevalue=1)";
                    var number = await connection.ExecuteScalarAsync<int>(sql);
                    result.Add(new NavigationYearResult() { Year = (minYearDate.Year) + "年", Count = number });
                }
            }
            return result;
        }
    }
}