aivfo-tl-3.0/ivf_tl_operate_2.0/control/ivf_tl_Services/DBService.cs

using IvfTl.Control.Entity.DBEntitys;
using IvfTl.Control.Entity.DTO.ControllerResults;
using IvfTl.Control.Entity.GlobalEntitys;
using IvfTl.Control.Entity.GlobalEnums;
using IvfTl.Control.Entity.InitEntitys;
using ivf_tl_UtilHelper;
using Microsoft.Data.Sqlite;
using Newtonsoft.Json;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace IvfTl.Control.Services
{
    public class DBService
    {
        public event Action<string, LogEnum> ErrorLogEvent;

        public event Action<Exception, string, string, LogEnum> ExceptionLogEvent;

        public string DbPath = "";

        private SqlSugarScope Db;

        public DBService()
        {

        }

        public bool StartDbService(string dbPath)
        {
            try
            {
                DbPath = dbPath;
                string connStr = new SqliteConnectionStringBuilder()
                {
                    DataSource = dbPath,
                    Mode = SqliteOpenMode.ReadWrite,
                }.ToString();

                ConnectionConfig connectionConfig = new ConnectionConfig()
                {
                    ConnectionString = connStr,
                    DbType = DbType.Sqlite,//数据库类型
                    IsAutoCloseConnection = true //不设成true要手动close
                };

                Db = new SqlSugarScope(connectionConfig);

                // V-051：本地 SQLite CodeFirst 自动建 house_autofocus_calibration 镜像表
                // （M2-04 的标定镜像写入此表；本地库原无该表 → 写入失败无数据）。
                // InitTables 发 CREATE TABLE IF NOT EXISTS，已建则不动；单独 try 包裹，
                // 建表失败不阻断 DB 服务启动（与既有防御式风格一致）。
                // D1-09 修复:本地 aivfoTL.db schema 落后于实体(多表缺列,如 tl_setting.localAutofocusEnabled、
                // house_well_setting 缺列等)→ control 各 Update/Query 报 "no such column"。
                // CodeFirst.InitTables 对已存在表只补缺失列(additive,ADD COLUMN,不删列不丢数据),逐实体自愈本地 schema;
                // 每实体独立 try/catch,单表失败(如 SQLite ADD NOT NULL 列限制)不影响其它表与 DB 服务启动。
                var localEntities = new[]
                {
                    typeof(HouseAutofocusCalibrationDB), typeof(TLSettingDB), typeof(HouseDB),
                    typeof(HouseWellSettingDB), typeof(DishDB), typeof(BalanceDB),
                    typeof(EmbryoDB), typeof(HouseWellPhotoDB), typeof(PictureDB)
                };
                foreach (var et in localEntities)
                {
                    try { Db.CodeFirst.InitTables(et); }
                    catch (Exception exTable)
                    {
                        ExceptionLogEvent?.Invoke(exTable, "DBServiceImpl.StartDbService.InitTables:" + et.Name, null, LogEnum.DbException);
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.StartDbService", null, LogEnum.DbException);
                return false;
            }
        }

        /// <summary>
        /// 生成实体
        /// </summary>
        public void BuildEntity(string path, string nameSpace)
        {
            Db.DbFirst.IsCreateAttribute().CreateClassFile(path, nameSpace);
        }

        /// <summary>
        /// 数据库更新
        /// </summary>
        /// <param name="tLInitControllerResult"></param>
        public void DBUpdateTLInfo(TLInitControllerResult tLInitControllerResult)
        {
            try
            {
                TLSettingDB tLSettingDB = ConvertHelper.ConvertToTLSettingDB(tLInitControllerResult.TLSetting);
                if (tLSettingDB == null)
                {
                    return;
                }
                var oldSetting = Db.Queryable<TLSettingDB>().First(x => x.tlSn == tLSettingDB.tlSn);
                if (oldSetting != null)
                {
                    tLSettingDB.cid = oldSetting.cid;
                    Db.Updateable(tLSettingDB).ExecuteCommand();
                }
                else
                {
                    Db.Insertable(tLSettingDB).ExecuteCommand();
                }
                UpdateHoustInfo(tLInitControllerResult.HouseList, tLSettingDB.tlSn);
                UpdateHouseWellSetting(tLInitControllerResult.HouseWellList, tLSettingDB.tlSn);
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.DBUpdateTLInfo", null, LogEnum.DbException);
            }
        }

        /// <summary>
        /// 数据库更新并获取数据
        /// </summary>
        /// <param name="tLInitData"></param>
        /// <returns></returns>
        public (TLSettingDB, List<HouseDB>, List<HouseWellSettingDB>) DBGetTLInfo(TLInitData tLInitData)
        {
            try
            {

                Db.Updateable<TLSettingDB>()
               .SetColumns(it => it.softwareVersion == tLInitData.softwareVersion)
               .SetColumns(x => x.verticalMotorPulseMax == tLInitData.verticalMotorPulseMax)
               .Where(it => it.tlSn == tLInitData.tlSn).ExecuteCommand();

                var houseSnList = tLInitData.houseLinkDataList.Select(x => x.houseSn).ToList();

                UpdateHouseInfo(tLInitData.houseLinkDataList, tLInitData.tlSn);

                UpdateHouseWellSetting(tLInitData.houseEEPROInitDTOList, tLInitData.tlSn);

                TLSettingDB tLSettingDB = Db.Queryable<TLSettingDB>().First(it => it.tlSn == tLInitData.tlSn);
                List<HouseDB> houseDBList = Db.Queryable<HouseDB>().Where(x => x.tlSn == tLInitData.tlSn && houseSnList.Contains(x.houseSn)).ToList();
                List<HouseWellSettingDB> HouseWellSettingDBList = Db.Queryable<HouseWellSettingDB>().Where(x => x.tlSn == tLInitData.tlSn && houseSnList.Contains(x.houseSn)).ToList();
                return (tLSettingDB, houseDBList, HouseWellSettingDBList);
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.UpdateHouseInfo", null, LogEnum.DbException);
                return (new TLSettingDB(), new List<HouseDB>(), new List<HouseWellSettingDB>());
            }
        }

        #region DishDB
        public List<Dish> GetDBDishs(string tlsn)
        {
            try
            {
                var dishList = Db.Queryable<DishDB>().Where(it => it.tlSn == tlsn && it.endTime == null).ToList();

                if (!dishList.Any()) return new List<Dish>();

                List<Dish> result = new List<Dish>();
                foreach (var item in dishList)
                {
                    Dish dish = ConvertHelper.ConvertToDish(item);
                    result.Add(dish);
                    var embryoList = Db.Queryable<EmbryoDB>().Where(it => it.dishId == item.id).ToList();
                    if (!embryoList.Any()) continue;
                    dish.Embryo = new List<Embryo>();
                    foreach (var itemEmbryo in embryoList)
                    {
                        dish.Embryo.Add(ConvertHelper.ConvertToEmbryo(itemEmbryo));
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.GetDBDishs", null, LogEnum.DbException);
                return new List<Dish>();
            }
        }

        public void AddDishs(List<DishDB> dishDbList, string tlsn)
        {
            try
            {
                Db.Deleteable<DishDB>().Where(x => x.tlSn == tlsn).ExecuteCommand();
                Db.Insertable(dishDbList).ExecuteCommand();
                return;
                var alldishList = Db.Queryable<DishDB>().Where(it => it.tlSn == tlsn).ToList();

                var oldDishList = alldishList.Where(it => !it.endTime.HasValue).ToList();

                List<DishDB> endDishs = new List<DishDB>();
                foreach (var item in oldDishList)
                {
                    var aa = dishDbList.FirstOrDefault(x => x.id == item.id);
                    if (aa == null)
                    {
                        item.endTime = DateTime.Now;
                        endDishs.Add(item);
                    }
                }
                if (endDishs.Any())
                {
                    Db.Updateable(endDishs).UpdateColumns(it => new { it.endTime }).ExecuteCommand();
                }
                List<DishDB> insertDishList = new List<DishDB>();
                List<DishDB> updateDishList = new List<DishDB>();
                foreach (var item in dishDbList)
                {
                    var aa = alldishList.FirstOrDefault(x => x.id == item.id);
                    if (aa == null)
                    {
                        insertDishList.Add(item);
                    }
                    else
                    {
                        if (aa.endTime.HasValue)
                        {
                            aa.endTime = null;
                            updateDishList.Add(aa);
                        }
                    }
                }
                if (insertDishList.Any())
                {
                    Db.Insertable(insertDishList).ExecuteCommand();
                }
                if (updateDishList.Any())
                {
                    Db.Updateable(updateDishList).UpdateColumns(it => new { it.endTime }).ExecuteCommand();
                }
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.AddDishs", null, LogEnum.DbException);
            }
        }

        public void AddDish(Dish dish, string tlsn)
        {
            try
            {
                Db.Deleteable<DishDB>().Where(x => x.id == dish.id && x.tlSn == tlsn).ExecuteCommand();
                Db.Deleteable<EmbryoDB>().Where(x => x.dishId == dish.id && x.tlSn == tlsn).ExecuteCommand();

                DishDB dishDB = ConvertHelper.ConvertToDishDB(dish);
                Db.Insertable(dishDB).ExecuteCommand();

                List<EmbryoDB> embryoDbList = new List<EmbryoDB>();
                foreach (var item in dish.Embryo)
                {
                    embryoDbList.Add(ConvertHelper.ConvertToEmbryoDB(item));
                }

                Db.Insertable(embryoDbList).ExecuteCommand();
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.AddDish", null, LogEnum.DbException);
            }
        }

        public void EndDish(long id, DateTime endTime, string tlsn)
        {
            try
            {
                Db.Updateable<DishDB>().SetColumns(it => it.endTime == endTime).Where(it => it.id == id && it.tlSn == tlsn).ExecuteCommand();

                Db.Updateable<EmbryoDB>().SetColumns(it => new EmbryoDB()
                {
                    state = (int)EmbryoState.End,
                    stateTime = endTime,
                }).Where(it => it.dishId == id && it.tlSn == tlsn && it.state == (int)EmbryoState.None).ExecuteCommand();
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.EndDish", null, LogEnum.DbException);
            }
        }
        #endregion

        #region EmbryoDB
        public void AddEmbryos(List<EmbryoDB> embryoDbList, string tlsn)
        {
            try
            {

                Db.Deleteable<EmbryoDB>().Where(x => x.tlSn == tlsn).ExecuteCommand();
                Db.Insertable(embryoDbList).ExecuteCommand();
                return;


                var allEmbryoList = Db.Queryable<EmbryoDB>().Where(it => it.tlSn == tlsn).ToList();

                var ingEmbryoList = allEmbryoList.Where(x => x.state == (int)EmbryoState.None).ToList();

                var updateEmbryoList = new List<EmbryoDB>();
                foreach (var item in ingEmbryoList)
                {
                    var a = embryoDbList.FirstOrDefault(x => x.id == item.id);
                    if (a == null)
                    {
                        item.state = (int)EmbryoState.End;
                        item.stateTime = DateTime.Now;
                        updateEmbryoList.Add(item);
                    }
                    if (a.state != item.state)
                    {
                        item.state = a.state;
                        item.stateTime = a.stateTime;
                        updateEmbryoList.Add(item);
                    }
                }
                if (updateEmbryoList.Any())
                {
                    Db.Updateable(updateEmbryoList).UpdateColumns(it => new { it.state, it.stateTime }).ExecuteCommand();
                }
                updateEmbryoList.Clear();
                var insertEmbryoList = new List<EmbryoDB>();
                foreach (var item in embryoDbList)
                {
                    var aa = allEmbryoList.FirstOrDefault(x => x.id == item.id);
                    if (aa == null)
                    {
                        insertEmbryoList.Add(item);
                    }
                    else
                    {
                        if (aa.state != item.state)
                        {
                            aa.state = item.state;
                            aa.stateTime = item.stateTime;
                            updateEmbryoList.Add(aa);
                        }
                    }
                }

                if (insertEmbryoList.Any())
                {
                    Db.Insertable(insertEmbryoList).ExecuteCommand();
                }
                if (updateEmbryoList.Any())
                {
                    Db.Updateable(updateEmbryoList).UpdateColumns(it => new { it.state, it.stateTime }).ExecuteCommand();
                }
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.AddEmbryos", null, LogEnum.DbException);
            }
        }

        public void ChangeEmbryoState(Embryo embryo)
        {
            try
            {
                Db.Updateable<EmbryoDB>().SetColumns(it => new EmbryoDB()
                {
                    state = embryo.state,
                    stateTime = embryo.stateTime,
                }).Where(it => it.id == embryo.id && it.tlSn == embryo.tlSn).ExecuteCommand();
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.ChangeEmbryoState", null, LogEnum.DbException);
            }
        }
        #endregion

        #region BalanceDB
        public List<Balance> DbGetBalances(string tlsn)
        {
            try
            {
                var a = Db.Queryable<BalanceDB>().Where(it => it.tlSn == tlsn && it.endTime == null).ToList();
                if (!a.Any()) return new List<Balance>();
                List<Balance> result = new List<Balance>();
                foreach (var item in a)
                {
                    result.Add(ConvertHelper.ConvertToBalance(item));
                }
                return result;
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.DbGetBalances", null, LogEnum.DbException);
                return new List<Balance>();
            }
        }

        public void AddBalances(List<BalanceDB> balanceDbList, string tlsn)
        {
            try
            {
                Db.Deleteable<BalanceDB>().Where(x => x.tlSn == tlsn).ExecuteCommand();
                Db.Insertable(balanceDbList).ExecuteCommand();
                return;

                var allBalanceList = Db.Queryable<BalanceDB>().Where(it => it.tlSn == tlsn).ToList();

                var ingBalacneList = allBalanceList.Where(x => !x.endTime.HasValue).ToList();

                var updateList = new List<BalanceDB>();
                foreach (var item in ingBalacneList)
                {
                    var a = balanceDbList.FirstOrDefault(x => x.id == item.id);
                    if (a == null)
                    {
                        item.endTime = DateTime.Now;
                        updateList.Add(item);
                    }
                }
                if (updateList.Any())
                {
                    Db.Updateable(updateList).UpdateColumns(it => new { it.endTime }).ExecuteCommand();
                }
                updateList.Clear();
                var insertList = new List<BalanceDB>();
                foreach (var item in balanceDbList)
                {
                    var old = allBalanceList.FirstOrDefault(x => x.id == item.id);
                    if (old == null)
                    {
                        insertList.Add(item);
                    }
                    else
                    {
                        if (old.endTime != item.endTime)
                        {
                            old.endTime = item.endTime;
                            updateList.Add(old);
                        }
                    }
                }
                if (updateList.Any())
                {
                    Db.Updateable(updateList).UpdateColumns(it => new { it.endTime }).ExecuteCommand();
                }
                if (insertList.Any())
                {
                    Db.Insertable(insertList).ExecuteCommand();
                }
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.AddBalances", null, LogEnum.DbException);
            }
        }

        public void AddBalance(Balance balance)
        {
            try
            {
                var oldBalance = Db.Queryable<BalanceDB>().First(x => x.id == balance.id);
                if (oldBalance == null)
                {
                    BalanceDB balanceDB = ConvertHelper.ConvertToBalanceDB(balance);
                    Db.Insertable(balanceDB).ExecuteCommand();
                }
                else
                {
                    if (oldBalance.endTime.HasValue)
                    {
                        oldBalance.endTime = null;
                        Db.Updateable(oldBalance).UpdateColumns(it => new { it.endTime }).ExecuteCommand();
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.AddBalance", null, LogEnum.DbException);
            }
        }

        public void EndBalance(long id, DateTime? endTime)
        {
            try
            {
                Db.Updateable<BalanceDB>().SetColumns(it => it.endTime == endTime).Where(it => it.id == id).ExecuteCommand();
                return;
                var oldBalance = Db.Queryable<BalanceDB>().First(x => x.id == id);
                if (oldBalance == null) return;
                oldBalance.endTime = endTime;
                Db.Updateable(oldBalance).UpdateColumns(it => new { it.endTime }).ExecuteCommand();
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.EndBalance", null, LogEnum.DbException);
            }
        }
        #endregion

        #region houseDB

        /// <summary>
        /// 更新仓室信息
        /// </summary>
        /// <param name="houseList"></param>
        /// <param name="tlsn"></param>
        private void UpdateHoustInfo(List<House> houseList, string tlsn)
        {
            try
            {
                List<HouseDB> houseDBList = new List<HouseDB>();
                foreach (var item in houseList)
                {
                    houseDBList.Add(ConvertHelper.ConvertToHouseDB(item));
                }
                if (!houseDBList.Any()) return;
                Db.Deleteable<HouseDB>().Where(x => x.tlSn == tlsn).ExecuteCommand();
                Db.Insertable(houseDBList).ExecuteCommand();
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.UpdateHoustInfo", null, LogEnum.DbException);
            }
        }

        /// <summary>
        /// 初始化时更新仓室信息
        /// </summary>
        /// <param name="houseLinkDataList"></param>
        /// <param name="tlsn"></param>
        private void UpdateHouseInfo(List<HouseInitData> houseLinkDataList, string tlsn)
        {
            try
            {
                if (!houseLinkDataList.Any()) return;

                var houseSnList = houseLinkDataList.Select(x => x.houseSn).ToList();

                var oldHouseList = Db.Queryable<HouseDB>().Where(x => x.tlSn == tlsn && houseSnList.Contains(x.houseSn)).ToList();


                if (!oldHouseList.Any()) return;

                foreach (var item in oldHouseList)
                {
                    var newItem = houseLinkDataList.FirstOrDefault(x => x.houseSn == item.houseSn);
                    if (newItem == null) continue;
                    item.housePort = newItem.housePort;
                    item.ccdId = newItem.ccdId;
                    item.ccdSn = newItem.ccdSn;
                    item.inletValveOpeningTime = newItem.inletValveOpeningTime;
                    item.temperatureLowerHeatingPlate = newItem.temperatureLowerHeatingPlate;
                    item.verticalMotorSpacePulse = newItem.verticalMotorSpacePulse;
                }
                var updateHouseRsult = Db.Updateable(oldHouseList)
                    .UpdateColumns(it => new
                    {
                        it.housePort,
                        it.ccdId,
                        it.ccdSn,
                        it.inletValveOpeningTime,
                        it.temperatureLowerHeatingPlate,
                        it.verticalMotorSpacePulse
                    }).ExecuteCommand();
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.UpdateHouseInfo", null, LogEnum.DbException);
            }
        }
        #endregion

        #region HouseWellSettingDB
        private void UpdateHouseWellSetting(List<HouseWellSetting> houseWellSettingList, string tlSn)
        {
            try
            {
                List<HouseWellSettingDB> houseWellSettingDBList = new List<HouseWellSettingDB>();
                foreach (var item in houseWellSettingList)
                {
                    houseWellSettingDBList.Add(ConvertHelper.ConvertToHouseWellSettingDB(item));
                }
                Db.Deleteable<HouseWellSettingDB>().Where(x => x.tlSn == tlSn).ExecuteCommand();
                Db.Insertable(houseWellSettingDBList).ExecuteCommand();
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.UpdateHouseWellSetting", null, LogEnum.DbException);
            }
        }

        private void UpdateHouseWellSetting(List<HouseEEPROInfo> houseEEPROInfos, string tlsn)
        {
            try
            {
                if (!houseEEPROInfos.Any()) return;
                var houseSnList = houseEEPROInfos.Select(x => x.houseSn).ToList();
                var oldHouseList = Db.Queryable<HouseWellSettingDB>().Where(x => x.tlSn == tlsn && houseSnList.Contains(x.houseSn)).ToList();

                if (!oldHouseList.Any()) return;
                foreach (var item in oldHouseList)
                {
                    var newItem = houseEEPROInfos.FirstOrDefault(x => x.houseSn == item.houseSn);
                    if (newItem == null) continue;
                    item.eepromClearPosition = newItem.eepromClearPosition;
                    switch (item.wellSn)
                    {
                        case 1:
                            item.horizontalMotorPosition = newItem.hwell1;
                            break;
                        case 2:
                            item.horizontalMotorPosition = newItem.hwell2;
                            break;
                        case 3:
                            item.horizontalMotorPosition = newItem.hwell3;
                            break;
                        case 4:
                            item.horizontalMotorPosition = newItem.hwell4;
                            break;
                        case 5:
                            item.horizontalMotorPosition = newItem.hwell5;
                            break;
                        case 6:
                            item.horizontalMotorPosition = newItem.hwell6;
                            break;
                        case 7:
                            item.horizontalMotorPosition = newItem.hwell7;
                            break;
                        case 8:
                            item.horizontalMotorPosition = newItem.hwell8;
                            break;
                        case 9:
                            item.horizontalMotorPosition = newItem.hwell9;
                            break;
                        case 10:
                            item.horizontalMotorPosition = newItem.hwell10;
                            break;
                        case 11:
                            item.horizontalMotorPosition = newItem.hwell11;
                            break;
                        case 12:
                            item.horizontalMotorPosition = newItem.hwell12;
                            break;
                        case 13:
                            item.horizontalMotorPosition = newItem.hwell13;
                            break;
                        case 14:
                            item.horizontalMotorPosition = newItem.hwell14;
                            break;
                        case 15:
                            item.horizontalMotorPosition = newItem.hwell15;
                            break;
                        case 16:
                            item.horizontalMotorPosition = newItem.hwell16;
                            break;
                    }
                }

                var updateHouseRsult = Db.Updateable(oldHouseList)
                    .UpdateColumns(it => new
                    {
                        it.horizontalMotorPosition,
                        it.eepromClearPosition,
                    }).ExecuteCommand();
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.UpdateHouseWellSetting", null, LogEnum.DbException);
            }
        }
        #endregion

        #region pictureDB
        public void AddPictrue(PictureDB pictureDB)
        {
            try
            {
                var oldPic = Db.Queryable<PictureDB>().First(x => x.TlSn == pictureDB.TlSn && x.SourceImageName == pictureDB.SourceImageName);
                if (oldPic == null)
                {
                    Db.Insertable(pictureDB).ExecuteCommand();
                }
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.AddPictrue", null, LogEnum.DbException);
            }
        }

        public void DeletePictrue(string fileName, string tlsn)
        {
            try
            {
                Db.Deleteable<PictureDB>().Where(x => x.TlSn == tlsn && x.SourceImageName == fileName).ExecuteCommand();
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.DeletePictrue", null, LogEnum.DbException);
            }
        }

        public PictureDB SearchPicture(string fileName, string tlsn)
        {
            try
            {
                var pic = Db.Queryable<PictureDB>().First(x => x.TlSn == tlsn && x.SourceImageName == fileName);
                return pic;
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.SearchPicture", null, LogEnum.DbException);
                return null;
            }
        }
        #endregion

        #region HouseWellPhotoDB
        /// <summary>
        /// 更新拍照位置
        /// </summary>
        /// <param name="houses"></param>
        /// <param name="housesn"></param>
        /// <param name="tlsn"></param>
        /// <param name="positionType">0表示自动对焦</param>
        public void UpdatePosition(List<HouseWellPhotoDB> houses, int housesn, string tlsn, int positionType)
        {
            try
            {
                var wellList = houses.Select(x => x.wellSn).Distinct().ToList();
                //Db.Deleteable<HouseWellPhotoDB>().Where(x => x.tlSn == tlsn && x.houseSn == housesn && wellList.Contains(x.wellSn) && x.positionType == positionType).ExecuteCommand();
                if (positionType == 0)
                {
                    Db.Deleteable<HouseWellPhotoDB>().Where(x => x.tlSn == tlsn && x.houseSn == housesn && wellList.Contains(x.wellSn) && x.positionType == 0).ExecuteCommand();
                }
                else
                {
                    Db.Deleteable<HouseWellPhotoDB>().Where(x => x.tlSn == tlsn && x.houseSn == housesn && wellList.Contains(x.wellSn) && x.positionType != 0).ExecuteCommand();
                }
                Db.Insertable(houses).ExecuteCommand();
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.UpdatePosition", null, LogEnum.DbException);
            }
        }

        /// <summary>
        /// 获取电机位置
        /// </summary>
        /// <param name="wellSn"></param>
        /// <param name="housesn"></param>
        /// <param name="tlsn"></param>
        /// <param name="positionType">0表示自动对焦</param>
        /// <returns></returns>
        public List<HouseWellPhotoDB> GetPosition(List<int> wellSn, int housesn, string tlsn, int positionType)
        {
            try
            {
                //return Db.Queryable<HouseWellPhotoDB>().Where(x => x.tlSn == tlsn && x.houseSn == housesn && wellSn.Contains(x.wellSn) && x.positionType == positionType).ToList();
                if (positionType == 0)
                {
                    return Db.Queryable<HouseWellPhotoDB>().Where(x => x.tlSn == tlsn && x.houseSn == housesn && wellSn.Contains(x.wellSn) && x.positionType == 0).ToList();
                }
                else
                {
                    return Db.Queryable<HouseWellPhotoDB>().Where(x => x.tlSn == tlsn && x.houseSn == housesn && wellSn.Contains(x.wellSn) && x.positionType != 0).ToList();
                }
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.GetPosition", null, LogEnum.DbException);
                return new List<HouseWellPhotoDB>();
            }
        }
        #endregion

        #region HouseAutofocusCalibrationDB（M2-04 标定结果库内镜像）
        /// <summary>
        /// 标定结果镜像入 house_autofocus_calibration（真相源仍为本地 calibration.json，见 12 §2.7）。
        /// scene=0 出厂基准：按 (tlSn,houseSn,wellSn,scene=0,deleted==null) upsert(每 well 唯一)；
        /// scene=1 日常对焦：直接 Insertable append(留历史，清理任务只删 scene=1)。
        /// 入参取标定产出的原始值(WellCalib 解包后传入)，本方法不依赖 IvfTl.AutoFocus 程序集。
        /// 写库异常被本方法吞掉(记日志)，绝不向上抛，保证不崩对焦/采集线程。
        /// </summary>
        /// <param name="tlSn">tl 设备 sn</param>
        /// <param name="houseSn">仓室编号</param>
        /// <param name="wellSn">well 编号</param>
        /// <param name="scene">场景：0=出厂基准 1=日常对焦</param>
        /// <param name="focusZ">最清晰层 Z 脉冲(锚点)</param>
        /// <param name="exposure">曝光(×100µs)</param>
        /// <param name="horizontalPulse">水平电机位置脉冲</param>
        /// <param name="peakRatio">清晰度峰比</param>
        /// <param name="circleFound">是否检到 well 圆</param>
        /// <param name="centerOffsetPct">居中偏移百分比</param>
        /// <param name="note">备注</param>
        /// <param name="source">结果来源，默认 LOCAL_JSON</param>
        public void SaveAutofocusCalibration(string tlSn, int houseSn, int wellSn, int scene,
            int focusZ, int exposure, int horizontalPulse, decimal peakRatio, bool circleFound,
            decimal centerOffsetPct, string note = "", string source = "LOCAL_JSON")
        {
            try
            {
                DateTime now = DateTime.Now;
                if (scene == 0)
                {
                    // 出厂基准：每 well 唯一，存在则更新(upsert)，不存在则插入；清理任务不删 scene=0。
                    var old = Db.Queryable<HouseAutofocusCalibrationDB>()
                        .First(x => x.tlSn == tlSn && x.houseSn == houseSn && x.wellSn == wellSn
                                    && x.scene == 0 && x.deleted == null);
                    if (old != null)
                    {
                        old.focusZ = focusZ;
                        old.exposure = exposure;
                        old.horizontalPulse = horizontalPulse;
                        old.peakRatio = peakRatio;
                        old.circleFound = circleFound ? 1 : 0;
                        old.centerOffsetPct = centerOffsetPct;
                        old.calibTime = now;
                        old.source = source;
                        old.note = note;
                        old.updateTime = now;
                        Db.Updateable(old).ExecuteCommand();
                        return;
                    }
                }
                // scene=1 日常对焦 append，或 scene=0 首次插入。
                var entity = new HouseAutofocusCalibrationDB
                {
                    tlSn = tlSn,
                    houseSn = houseSn,
                    wellSn = wellSn,
                    scene = scene,
                    focusZ = focusZ,
                    exposure = exposure,
                    horizontalPulse = horizontalPulse,
                    peakRatio = peakRatio,
                    circleFound = circleFound ? 1 : 0,
                    centerOffsetPct = centerOffsetPct,
                    calibTime = now,
                    source = source,
                    note = note,
                    createTime = now,
                };
                Db.Insertable(entity).ExecuteCommand();
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.SaveAutofocusCalibration", null, LogEnum.DbException);
            }
        }

        /// <summary>
        /// M2-06 安全门降级用：读 scene=0 出厂基准的最清晰层 Z 脉冲(FocusZ)。
        /// 查 house_autofocus_calibration 中 (tlSn,houseSn,wellSn,scene=0,deleted==null) 的基准行，
        /// 取最新一条(按 calibTime 倒序)的 focusZ；无基准/异常返回 null。
        /// 用于安全门关闭(local_autofocus_enabled=0)时按基准位置拍照(降级)，不做实际对焦。
        /// 读库异常被本方法吞掉(记日志)，绝不向上抛，保证不崩对焦/采集线程。
        /// </summary>
        public int? GetBaselineFocusZ(string tlSn, int houseSn, int wellSn)
        {
            try
            {
                var baseline = Db.Queryable<HouseAutofocusCalibrationDB>()
                    .Where(x => x.tlSn == tlSn && x.houseSn == houseSn && x.wellSn == wellSn
                                && x.scene == 0 && x.deleted == null)
                    .OrderBy(x => x.calibTime, OrderByType.Desc)
                    .First();
                return baseline?.focusZ;
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.GetBaselineFocusZ", null, LogEnum.DbException);
                return null;
            }
        }

        /// <summary>
        /// G4-1 / 需求文档12 §2.7：对焦标定数据清理任务。
        /// 物理删除 house_autofocus_calibration 中 scene=1(日常对焦) 且 calibTime 早于 (now - keepDays) 的记录；
        /// scene=0(出厂基准) 永不删除——否则日常对焦失去参照基准(12 §2.7 约束①)。
        /// keepDays&lt;=0 视为不清理(安全阀:避免 cutoff 异常误删全部)。
        /// 全 try 兜底,异常吞掉记日志,绝不向上抛(不崩采集/对焦线程)。返回删除条数。
        /// </summary>
        /// <param name="keepDays">保留天数(来自 tl_setting.clean_autofocus_data 下发值,默认30)</param>
        public int CleanAutofocusData(int keepDays)
        {
            try
            {
                if (keepDays <= 0) return 0;
                DateTime cutoff = DateTime.Now.AddDays(-keepDays);
                // Deleteable 物理删除(本表无逻辑删除全局过滤)：retention 需真正缩表，故物理删而非置 deleted。
                int n = Db.Deleteable<HouseAutofocusCalibrationDB>()
                    .Where(x => x.scene == 1 && x.calibTime < cutoff)
                    .ExecuteCommand();
                return n;
            }
            catch (Exception ex)
            {
                ExceptionLogEvent?.Invoke(ex, "DBServiceImpl.CleanAutofocusData", null, LogEnum.DbException);
                return 0;
            }
        }
        #endregion
    }
}