using DataRWDAL.Base;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Reflection;
using XImagingXhandler.XDAL;

namespace DataRWDAL
{
    /// <summary>
    /// 液体体积校准库表操作类
    /// </summary>
    public class LiquidAccuracyDB : BaseDB
    {
        #region 查询返回液体体积校准数据集 by 液体范围Id、设备臂Id、液体类型Id
        public static ObservableCollection<LiquidAccuracy> GetLiquidAccuracyFromdb(int liquidRangeId, int deviceArmId, string liquidTypeId)
        {
            using (var db = GetInstance())
            {
                return new ObservableCollection<LiquidAccuracy>(db.Queryable<LiquidAccuracy>().Where(it => it.liquid_range_id.Equals((double)liquidRangeId)
                                                     && it.device_arm_id.Equals((double)deviceArmId)
                                                     && it.liquidtype_Id.Equals(liquidTypeId)).OrderBy(it => it.liquidaccuracy_Id, SqlSugar.OrderByType.Asc).ToList());
            }
        }
        #endregion

        #region 查询返回液体体积校准数系数的数据集 by 液体范围Id、设备臂Id、目标体积、液体类型Id
        /// <summary>
        /// 查询返回液体体积校准数系数的数据集 by 液体范围Id、设备臂Id、目标体积、液体类型Id
        /// </summary>
        /// <param name="liquidRangeId">液体范围Id</param>
        /// <param name="deviceArmId">设备臂Id</param>
        /// <param name="targetVolume">目标体积</param>
        /// <param name="liquidTypeId">液体类型Id</param>
        /// <returns>液体体积校准数系数的数据集(仅加载范围内最接近目标体积的两条校准数据)</returns>
        public static List<LiquidAccuracy> GetLiquidAccuracyFromdb(int liquidRangeId, int deviceArmId, double targetVolume, string liquidTypeId)
        {
            List<LiquidAccuracy> liquidAccuracies = new List<LiquidAccuracy>();
            using (var db = GetInstance())
            {
                var query1 = db.Queryable<LiquidAccuracy>().Where(it => it.liquid_range_id.Equals((double)liquidRangeId)
                                                     && it.device_arm_id.Equals((double)deviceArmId)
                                                     && it.liquidtype_Id.Equals(liquidTypeId)
                                                     && it.liquid_target_volume <= targetVolume).OrderBy(it => it.liquid_target_volume, SqlSugar.OrderByType.Desc).Max(it => it.liquid_target_volume);


                var liquidAccuraciesMin = db.Queryable<LiquidAccuracy>().Where(it => it.liquid_range_id.Equals((double)liquidRangeId)
                                                 && it.device_arm_id.Equals((double)deviceArmId)
                                                 && it.liquidtype_Id.Equals(liquidTypeId)
                                                 && it.liquid_target_volume == query1).OrderBy(it => it.liquid_target_volume, SqlSugar.OrderByType.Desc).ToList();

                double minVolume = 0.0d;
                double minAveVolume = 0.0d;
                foreach (LiquidAccuracy la in liquidAccuraciesMin)
                {
                    minVolume += la.liquid_Real_volume;
                }
                if (liquidAccuraciesMin.Count > 0)
                {
                    minAveVolume = minVolume / liquidAccuraciesMin.Count;

                    LiquidAccuracy la = new LiquidAccuracy();
                    la.liquid_target_volume = liquidAccuraciesMin[0].liquid_target_volume;
                    la.liquid_Real_volume = minAveVolume;
                    liquidAccuracies.Add(la);
                }

                var query2 = db.Queryable<LiquidAccuracy>().Where(it => it.liquid_range_id.Equals((double)liquidRangeId)
                                                     && it.device_arm_id.Equals((double)deviceArmId)
                                                     && it.liquidtype_Id.Equals(liquidTypeId)
                                                     && it.liquid_target_volume >= targetVolume).OrderBy(it => it.liquid_target_volume, SqlSugar.OrderByType.Desc).Min(it => it.liquid_target_volume);


                var liquidAccuraciesMax = db.Queryable<LiquidAccuracy>().Where(it => it.liquid_range_id.Equals((double)liquidRangeId)
                                                 && it.device_arm_id.Equals((double)deviceArmId)
                                                 && it.liquidtype_Id.Equals(liquidTypeId)
                                                 && it.liquid_target_volume == query2).OrderBy(it => it.liquid_target_volume, SqlSugar.OrderByType.Desc).ToList();

                minVolume = 0.0d;
                minAveVolume = 0.0d;
                foreach (LiquidAccuracy la in liquidAccuraciesMax)
                {
                    minVolume += la.liquid_Real_volume;
                }
                if (liquidAccuraciesMax.Count > 0)
                {
                    minAveVolume = minVolume / liquidAccuraciesMax.Count;

                    LiquidAccuracy la = new LiquidAccuracy();
                    la.liquid_target_volume = liquidAccuraciesMax[0].liquid_target_volume;
                    la.liquid_Real_volume = minAveVolume;
                    liquidAccuracies.Add(la);
                }
            }
            return liquidAccuracies;
        }
        #endregion

        #region 添加一条新的液体体积校准数据 by 体积校准数据
        /// <summary>
        /// 添加一条新的液体体积校准数据 by 体积校准数据对象
        /// </summary>
        /// <param name="liquidAccuracy">体积校准数据对象</param>
        /// <param name="id">输出添加成功后的Id</param>
        /// <returns>1：成功；0：失败</returns>
        public static int AddLiquidAccuracyIntodb(LiquidAccuracy liquidAccuracy, out string id)
        {
            id = "0";
            using (var db = GetInstance())
            {
                var query1 = db.Queryable<LiquidAccuracy>().Max(it => it.liquidaccuracy_Id);
                if(query1==null||query1=="")
                {
                    liquidAccuracy.liquidaccuracy_Id = "1";
                }
                else
                {
                    liquidAccuracy.liquidaccuracy_Id = (System.Convert.ToInt32(query1)+1).ToString();
                    id = liquidAccuracy.liquidaccuracy_Id;
                }
                return db.Insertable<LiquidAccuracy>(liquidAccuracy).ExecuteCommand();

            }
        }
        #endregion

        #region 修改一条新的液体体积校准数据 by 体积校准数据
        /// <summary>
        /// 修改一条新的液体体积校准数据 by 体积校准数据
        /// </summary>
        /// <param name="liquidAccuracy">体积校准数据</param>
        /// <returns>1：成功；0：失败</returns>
        public static int UpdateLiquidAccuracyIntodb(LiquidAccuracy liquidAccuracy)
        {
            using (var db = GetInstance())
            {
                return db.Updateable<LiquidAccuracy>(liquidAccuracy).ExecuteCommand();
            }
        }
        #endregion

        #region 删除一条新的液体体积校准数据 by 体积校准数据
        /// <summary>
        /// 删除一条新的液体体积校准数据 by 体积校准数据
        /// </summary>
        /// <param name="liquidAccuracy">体积校准数据</param>
        /// <returns>1：成功；0：失败</returns>
        public static int DeleteLiquidAccuracyIntodb(LiquidAccuracy liquidAccuracy)
        {
            using (var db = GetInstance())
            {
                return db.Deleteable<LiquidAccuracy>(liquidAccuracy).ExecuteCommand();
            }
        }
        #endregion
    }
}