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>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<NavigationSearchResult> GetNavigationSearchResultAsync(NavigationSearchRequest request)
        {
            var result = new NavigationSearchResult()
            {
                PressItems = await GetNavigationPressAsync(request),
                TeacherItems = await GetNavigationSearchResultAysnc(request),
                YearItems = await GetNavigationYearResult(request)
            };
            var database = new Database(DatabaseType.MySql, Common.Tools.ConfigHelper.GetValue("Database:ConnectionString"));
            var connection = database.GetConnection();
            var maxYearSql = $"select max(AddDate) from tede_media where id in( SELECT MediaId FROM tede_system_lable_media where typevalue=1)";
            result.EndTime = await connection.ExecuteScalarAsync<string>(maxYearSql);
            result.EndTime = Convert.ToDateTime(result.EndTime).ToString("yyyy-MM-dd");
            var minYearSql = $"select min(AddDate) from tede_media where id in( SELECT MediaId FROM tede_system_lable_media where typevalue=1)  and adddate <>'0001-01-01 00:00:00'";
            result.BeginTime = await connection.ExecuteScalarAsync<string>(minYearSql);
            result.BeginTime = Convert.ToDateTime(result.BeginTime).ToString("yyyy-MM-dd");
            return result;
        }

        /// <summary>
        /// 获取搜索作者
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<NavigationSearchTeacherResult>> GetNavigationSearchResultAysnc(NavigationSearchRequest request)
        {
            var database = new Database(DatabaseType.MySql, Common.Tools.ConfigHelper.GetValue("Database:ConnectionString"));
            var connection = database.GetConnection();
            var categorySql = "";
            if (request.CategoryId > 0)
                categorySql += $" and lableId={request.CategoryId}";
            var teacherSql = "";
            if (request.TeacherId != null && request.TeacherId.Count > 0)
            {
                var str = "";
                foreach (var item in request.TeacherId)
                    str += "{item},";
                str = str.Remove(str.Length - 1, 1);
                teacherSql += $" and TeacherId in({str})";
            }
            if (request.Press != null && request.Press.Count > 0)
            {
                var str = "";
                foreach (var item in request.Press)
                    str += "'{item}',";
                str = str.Remove(str.Length - 1, 1);
                teacherSql += $" or Press in ({str})";
            }
            if (!string.IsNullOrEmpty(request.BeginTime) && !string.IsNullOrEmpty(request.EndTime))
            {
                if (DateTime.TryParse(request.BeginTime, out var beginTime) && DateTime.TryParse(request.EndTime, out var endTime))
                    teacherSql += $" and AddDate>='{request.BeginTime}' and AddDate<='{request.EndTime}'";
            }
            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 {categorySql}) {teacherSql} and  Author is not null group by Author,TeacherId";
            var reuslt = await connection.QueryAsync<NavigationSearchTeacherResult>(sql);
            if (request.TeacherId != null && request.TeacherId.Count > 0)
                foreach (var item in reuslt)
                {
                    foreach (var teacherId in request.TeacherId)
                    {
                        item.IsChecked = item.TeacherId.Equals(teacherId);
                    }
                }
            return reuslt;
        }
        /// <summary>
        /// 获取出版社
        /// </summary>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public async Task<IEnumerable<PressResult>> GetNavigationPressAsync(NavigationSearchRequest request)
        {
            var result = new List<PressResult>();
            var categorySql = "";
            if (request.CategoryId > 0)
                categorySql += $" and lableId={request.CategoryId}";
            var teacherSql = "";
            if (request.TeacherId != null && request.TeacherId.Count > 0)
            {
                var str = "";
                foreach (var item in request.TeacherId)
                    str += "{item},";
                str = str.Remove(str.Length - 1, 1);
                teacherSql += $" and TeacherId in({str})";
            }
            if (request.Press != null && request.Press.Count > 0)
            {
                var str = "";
                foreach (var item in request.Press)
                    str += "'{item}',";
                str = str.Remove(str.Length - 1, 1);
                teacherSql += $" or Press in ({str})";
            }
            if (!string.IsNullOrEmpty(request.BeginTime) && !string.IsNullOrEmpty(request.EndTime))
            {
                if (DateTime.TryParse(request.BeginTime, out var beginTime) && DateTime.TryParse(request.EndTime, out var endTime))
                    teacherSql += $" and AddDate>='{request.BeginTime}' and AddDate<='{request.EndTime}'";
            }
            var sql = $"SELECT Press FROM tede_media where id in(select MediaId from tede_system_lable_media where TypeValue=1 {categorySql}) {teacherSql} and  Press is not null group by Press";
            // if (request.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 database = new Database(DatabaseType.MySql, Common.Tools.ConfigHelper.GetValue("Database:ConnectionString"));
            var connection = database.GetConnection();
            var pressList = await connection.QueryAsync<string>(sql);
            if (request.Press != null)
                foreach (var item in pressList)
                {
                    result.Add(new PressResult { PressName = item, IsChecked = request.Press.Equals(item) });
                }
            return result;
        }
        /// <summary>
        /// 获取年份数据
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<IEnumerable<NavigationYearResult>> GetNavigationYearResult(NavigationSearchRequest request)
        {
            var result = new List<NavigationYearResult>();
            var database = new Database(DatabaseType.MySql, Common.Tools.ConfigHelper.GetValue("Database:ConnectionString"));
            var connection = database.GetConnection();
            var categorySql = "";
            if (request.CategoryId > 0)
                categorySql += $" and lableId={request.CategoryId}";
            var teacherSql = "";
            if (request.TeacherId != null && request.TeacherId.Count > 0)
            {
                var str = "";
                foreach (var item in request.TeacherId)
                    str += "{item},";
                str = str.Remove(str.Length - 1, 1);
                teacherSql += $" and TeacherId in({str})";
            }
            if (request.Press != null && request.Press.Count > 0)
            {
                var str = "";
                foreach (var item in request.Press)
                    str += "'{item}',";
                str = str.Remove(str.Length - 1, 1);
                teacherSql += $" or Press in ({str})";
            }
            if (!string.IsNullOrEmpty(request.BeginTime) && !string.IsNullOrEmpty(request.EndTime))
            {
                if (DateTime.TryParse(request.BeginTime, out var beginTime) && DateTime.TryParse(request.EndTime, out var endTime))
                    teacherSql += $" and AddDate>='{request.BeginTime}' and AddDate<='{request.EndTime}'";
            }
            var maxYearSql = $"select max(AddDate) from tede_media where id in( SELECT MediaId FROM tede_system_lable_media where typevalue=1 {categorySql}) {teacherSql} ";
            var maxYear = await connection.ExecuteScalarAsync<string>(maxYearSql);
            var minYearSql = $"select min(AddDate) from tede_media where id in( SELECT MediaId FROM tede_system_lable_media where typevalue=1 {categorySql}) {teacherSql}";
            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 && minYearDate.Year > 1)
                {

                    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 tede_system_lable_media where typevalue=1 {categorySql}) {teacherSql}";
                        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>'{maxYearDate.Year}' and AddDate<'{maxYearDate.Year + 1}' and  id in( SELECT MediaId FROM tede_system_lable_media where typevalue=1 {categorySql}) {teacherSql}";
                    var number = await connection.ExecuteScalarAsync<int>(sql);
                    result.Add(new NavigationYearResult() { Year = (maxYearDate.Year) + "年", Count = number });
                }
            }
            return result;
        }
    }
}