XdCxRhDW/XdCxRhDW.Repostory/XlRepository.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Entity;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XdCxRhDW.Entity;

namespace XdCxRhDW.Repostory
{
    public static class XlRepository
    {
        /// <summary>
        /// 获取数据库所有双行根数
        /// </summary>
        /// <returns></returns>
        public static async Task<List<XlInfo>> GetAllAsync()
        {
            try
            {
                using (RHDWContext db = new RHDWContext())
                {
                    var res = await db.XlInfos.OrderByDescending(p => p.TimeUTC).OrderByDescending(p => p.Lon).Take(5000).ToListAsync();
                    return res;
                }
            }
            catch (Exception ex)
            {
                Serilog.Log.Error(ex, "加载星历信息异常");
                return null;
            }
        }
        public static async Task<int> GetCount(int? satCode)
        {
            using (RHDWContext db = new RHDWContext())
            {
                if (satCode == null)
                {
                    var count = await db.XlInfos.CountAsync();
                    return count;
                }
                else
                {
                    var count = await db.XlInfos.Where(w => w.SatCode == satCode).CountAsync();
                    return count;
                }
            }
        }
        public static async Task<List<SatInfo>> GetAllSat()
        {
            using (RHDWContext db = new RHDWContext())
            {
                string sql = "select * from XlInfo where id in (select max(id) maxid from xlinfo GROUP BY SatCode )";
                var data = await db.Database.SqlQuery<XlInfo>(sql).ToListAsync();
                var sats = data.Select(p => new SatInfo()
                {
                    SatCode = p.SatCode,
                    SatName = p.SatName,
                    SatLon = p.Lon,
                }).ToList();
                return sats;
            }
        }
        /// <summary>
        /// 获取数据库所有双行根数
        /// </summary>
        /// <returns></returns>
        public static async Task<List<XlInfo>> GetPageAsync(int currentPage, int pageSize, int? satCode)
        {
            try
            {
                using (RHDWContext db = new RHDWContext())
                {
                    var topNumber = (currentPage - 1) * pageSize;
                    StringBuilder sb = new StringBuilder();
                    sb.Append($"select * from xlinfo ");
                    if (satCode != null)
                    {
                        sb.Append($"where SatCode={satCode} ");
                    }
                    sb.Append($"order by TimeUTC desc limit {pageSize} offset {topNumber}");
                    string sql = sb.ToString();
                    var res = await db.Database.SqlQuery<XlInfo>(sql).ToListAsync();
                    return res;
                }
            }
            catch (Exception ex)
            {
                Serilog.Log.Error(ex, "加载星历信息异常");
                return null;
            }
        }

        /// <summary>
        /// 获取某个星某个时刻最近的双行根(可能返回null)
        /// </summary>
        /// <param name="satCode">卫星编号</param>
        /// <param name="sigTime">时刻</param>
        /// <returns></returns>
        public static async Task<XlInfo> GetLatestAsync(int satCode, DateTime sigTime)
        {
            try
            {
                satCode = 40892;
                sigTime = new DateTime(2024, 10, 09, 23, 59, 53);
                //GetLatestAsync方法在索引列上使用了加减运算会导致索引失效,运算变慢,这个函数是对GetLatestAsyncOld的优化

                //老的SQL很慢
                //SELECT min(abs(JULIANDAY(TimeUTC)-JULIANDAY('2024-10-09 23:59:53'))) as NearDaySpan,* FROM XlInfo where satcode=40892
                using (RHDWContext db = new RHDWContext())
                {
                    string sql = $"select * from XlInfo where SatCode={satCode} and TimeUTC>='{sigTime:yyyy-MM-dd HH:mm:ss}' order by TimeUTC limit 1";
                    var max = await db.Database.SqlQuery<XlInfo>(sql).FirstOrDefaultAsync();

                    sql = $"select * from XlInfo where SatCode={satCode} and TimeUTC<='{sigTime:yyyy-MM-dd HH:mm:ss}' order by TimeUTC desc limit 1";
                    var min = await db.Database.SqlQuery<XlInfo>(sql).FirstOrDefaultAsync();

                    if (max == null && min == null)
                        return null;
                    else if (max != null && min == null)
                        return max;
                    else if (max == null && min != null)
                        return min;
                    else
                    {
                        //谁离得更接近就用谁
                        var val1 = Math.Abs((max.TimeUTC - sigTime).TotalSeconds);
                        var val2 = Math.Abs((min.TimeUTC - sigTime).TotalSeconds);
                        if (val1 < val2)
                            return max;
                        else
                            return min;
                    }
                }
            }
            catch (Exception ex)
            {
                Serilog.Log.Error(ex, $"获取卫星[{satCode}]--{sigTime:yyyyMMddHHmmss}时刻附近的双行根数出错!");
                return null;
            }
        }
    }
}