using DataRWDAL.Base;
using MySql.Data.MySqlClient;
using Newtonsoft.Json.Linq;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Reflection;
using XImagingXhandler.XDAL;

namespace DataRWDAL
{
    /// <summary>
    /// 耗材库表操作类
    /// </summary>
    public class LabwareDB : BaseDB
    {
        #region 根据耗材名称，获取耗材信息
        /// <summary>
        /// 根据耗材名称，获取耗材信息
        /// </summary>
        /// <param name="labwareName">耗材名称</param>
        /// <returns>耗材对象</returns>
        public static Labware GetInfodByName(string labwareName)
        {
            using (var db = GetInstance())
            {
                return db.Queryable<Labware>().Single(it => it.labware_name.Equals(labwareName));
            }
        }
        #endregion

        #region 添加一条新的耗材数据 by 耗材数据
        /// <summary>
        /// 添加一条新的耗材数据 by 耗材数据
        /// </summary>
        /// <param name="labware">耗材对象</param>
        /// <returns>1:成功；0:失败</returns>
        public static int AddLabwareIntodb(Labware labware)
        {
            using (var db = GetInstance())
            {
                var result=0;
                db.BeginTran(); // 开启事务
                try
                {
                    result = db.Insertable<Labware>(labware).ExecuteCommand();
                    db.CommitTran(); // 提交事务
                }
                catch (Exception ex)
                {
                    db.RollbackTran(); // 事务回滚
                                       // 处理异常
                    result = 0;
                }
                return result;
            }
        }
        #endregion

        #region 查询返回耗材数据集，条件有默认类型、Id、耗材类型、包含的孔数
        /// <summary>
        /// 查询返回耗材数据集，条件有默认类型、Id、耗材类型、包含的孔数
        /// </summary>
        /// <param name="isDefaultType">默认类型；0表示非默认；1：表示默认；2表示全部</param>
        /// <param name="labwareId">耗材Id</param>
        /// <param name="labwaretype">耗材类型</param>
        /// <param name="wellcount">孔数（已不支持）</param>
        /// <returns>符合给定条件的耗材数据集</returns>
        public static ObservableCollection<Labware> GetLabware(int isDefaultType, string labwareId = "", string labwaretype = "", int wellcount = 0)
        {
            using (var db = GetInstance())
            {
                string sqlstr = "";
                if (labwareId == "")
                {
                    if (labwaretype == "")
                    {
                        if (isDefaultType == 2)
                        {
                            return new ObservableCollection<Labware>(db.Queryable<Labware>().Where(it => it.labware_status.Equals(1)).ToList());
                        }
                        else
                        {
                            return new ObservableCollection<Labware>(db.Queryable<Labware>().Where(it => it.labware_status.Equals(1) && it.is_default_type.Equals(Convert.ToInt32(isDefaultType))).ToList());
                        }
                    }
                    else
                    {
                        if (wellcount == 0)
                        {
                            return new ObservableCollection<Labware>(db.Queryable<Labware>().Where(it => it.labware_status.Equals(1) && it.labware_type.Equals(labwaretype)&& it.is_default_type.Equals(Convert.ToInt32(isDefaultType))).ToList());
                        }
                        else
                        {
                            //此查询暂不支持
                            return null;
                        }
                    }
                }
                else
                {
                    return new ObservableCollection<Labware>(db.Queryable<Labware>().Where(it => it.labware_status.Equals(1) && it.labware_id.Equals(labwareId) && it.is_default_type.Equals(Convert.ToInt32(isDefaultType))).ToList());
                }
            }
        }
        #endregion

        #region 查询返回孔板类（含圆形皿）耗材数据集
        /// <summary>
        /// 查询返回孔板类（含圆形皿）耗材数据集
        /// </summary>
        /// <returns></returns>
        public static ObservableCollection<Labware> GetSpecialLabware()
        {
            using (var db = GetInstance())
            {
                return new ObservableCollection<Labware>(db.Queryable<Labware>().Where(it => it.labware_status.Equals(1)&&(it.labware_type_id.Equals("1")||it.labware_type_id.Equals("5"))).OrderBy(it=>it.labware_type_id).ToList());
            }
        }
        #endregion

        #region 修改耗材数据 by 耗材对象
        /// <summary>
        /// 修改耗材数据 by 耗材对象
        /// </summary>
        /// <param name="labware">耗材对象</param>
        /// <returns>1：成功；0：失败</returns>
        public static int UpdateLabwareIntodb(Labware labware)
        {
            int iResult = 0;//接收返回值
            if (labware.labware_type_id == "3")//试剂槽类型
            {
                labware.well_height = (float)labware.labware_height - (float)labware.well_bottom_height;
            }

            using (var db = GetInstance())
            {
                iResult = db.Updateable<Labware>(labware).ExecuteCommand();

                //如果有关联的枪头盒，则更新枪头盒下的Tip的数据
                if (labware.labware_type_id.Equals("6"))//吸头盒
                {
                    Labware tipLabware = GetLabware(labware.tip_loaded_type);
                    if (!string.IsNullOrEmpty(labware.tip_loaded_type))
                    {
                        labware.tip_name = tipLabware.tip_name;
                        labware.tip_volume_max = tipLabware.tip_volume_max;
                        labware.tip_use_volume_max = tipLabware.tip_use_volume_max;
                        labware.tip_air_volume = tipLabware.tip_air_volume;
                        labware.tip_length = tipLabware.tip_length;
                        labware.tip_header_height = tipLabware.tip_header_height;
                        labware.tip_cone_height = tipLabware.tip_cone_height;
                        labware.tip_cone_radius_max = tipLabware.tip_cone_radius_max;
                        labware.tip_cone_radius_min = tipLabware.tip_cone_radius_min;
                        labware.press_tip_power = tipLabware.press_tip_power;
                        labware.press_tip_depth = tipLabware.press_tip_depth;
                        labware.press_tipinto_depth = tipLabware.press_tipinto_depth;
                        labware.quit_tip = tipLabware.quit_tip;
                        labware.is_filter = tipLabware.is_filter;
                        labware.is_lls = tipLabware.is_lls;
                        iResult = db.Updateable<Labware>(labware).ExecuteCommand();
                    }
                }
                else if (labware.labware_type_id.Equals("2"))//吸头
                {
                    List<Labware> labwareList = GetLabwareBytiploadedtype(labware.labware_id);
                    foreach (Labware l in labwareList)
                    {
                        l.tip_name = labware.tip_name;
                        l.tip_volume_max = labware.tip_volume_max;
                        l.tip_use_volume_max = labware.tip_use_volume_max;
                        l.tip_air_volume = labware.tip_air_volume;
                        l.tip_length = labware.tip_length;
                        l.tip_header_height = labware.tip_header_height;
                        l.tip_cone_height = labware.tip_cone_height;
                        l.tip_cone_radius_max = labware.tip_cone_radius_max;
                        l.tip_cone_radius_min = labware.tip_cone_radius_min;
                        l.press_tip_power = labware.press_tip_power;
                        l.press_tip_depth = labware.press_tip_depth;
                        l.press_tipinto_depth = labware.press_tipinto_depth;
                        l.quit_tip = labware.quit_tip;
                        l.is_filter = labware.is_filter;
                        l.is_lls = labware.is_lls;
                        iResult = db.Updateable<Labware>(l).ExecuteCommand();
                    }
                }
            }

            return iResult;
        }
        #endregion

        #region 删除耗材数据 by 耗材对象
        /// <summary>
        /// 删除耗材数据 by 耗材对象
        /// </summary>
        /// <param name="labware">耗材对象</param>
        /// <returns>1：成功；0：失败</returns>
        public static int DeleteLabwareIntodb(Labware labware)
        {
            using (var db = GetInstance())
            {
                return db.Updateable<Labware>(labware).ExecuteCommand();
            }
        }
        #endregion

        #region 查询返回枪头盒耗材数据集
        /// <summary>
        /// 查询返回枪头盒耗材数据集
        /// </summary>
        /// <returns>枪头盒耗材数据集</returns>
        public static ObservableCollection<Labware> GetTipLabwareFromdb()
        {
            using (var db = GetInstance())
            {
                return new ObservableCollection<Labware>(db.Queryable<Labware>().Where(it => it.labware_status.Equals(1) && it.labware_type.Equals("吸头盒")).ToList());
            }
        }
        #endregion

        #region 查询返回离心管耗材数据集
        /// <summary>
        /// 查询返回离心管耗材数据集
        /// </summary> 
        /// <returns>离心管耗材数据集</returns>
        public static ObservableCollection<Labware> GetCentrifugalLabwareFromdb()
        {
            using (var db = GetInstance())
            {
                return new ObservableCollection<Labware>(db.Queryable<Labware>().Where(it => it.labware_status.Equals(1) && it.labware_type.Equals("离心管")).ToList());
            }
        }
        #endregion

        #region 查询返回耗材数据 by 耗材Id
        /// <summary>
        /// 查询返回耗材数据 by 耗材Id
        /// </summary>
        /// <param name="labwareId">耗材Id</param>
        /// <returns>枪头盒耗材数据集</returns>
        public static Labware GetLabware(string labwareId = "")
        {
            using (var db = GetInstance())
            {
                return db.Queryable<Labware>().Single(it => it.labware_status.Equals(1) && it.labware_id.Equals(labwareId));
            }
        }
        #endregion

        #region 查询返回耗材数据集 by 耗材已安装的枪头耗材Id
        /// <summary>
        /// 查询返回耗材数据集 by 耗材已安装的枪头耗材Id
        /// </summary>
        /// <param name="tiploadedtype">耗材已安装的枪头耗材Id</param>
        /// <returns>耗材数据集</returns>
        public static List<Labware> GetLabwareBytiploadedtype(string tiploadedtype = "")
        {
            using (var db = GetInstance())
            {
                return db.Queryable<Labware>().Where(it => it.labware_status.Equals(1) && it.tip_loaded_type.Equals(tiploadedtype)).ToList();
            }
        }
        #endregion

        #region 查询返回关联的被叠放耗材数据集 by 耗材对象
        /// <summary>
        /// 查询返回关联的被叠放耗材数据集 by 耗材对象
        /// </summary>
        /// <param name="labware">耗材对象</param>
        /// <returns>被叠放耗材数据集</returns>
        public static ObservableCollection<Labware> GetPiledlabwareOfALabware(Labware labware)
        {
            string strlabwares = labware.piled_script;
            ObservableCollection<Labware> labwares = new ObservableCollection<Labware>();
            if (!string.IsNullOrEmpty(strlabwares))
            {
                JObject job = JObject.Parse(strlabwares);
                JArray jArray = (JArray)job["list"];
                if (jArray != null)
                {
                    for (int k = 0; k < jArray.Count; k++)
                    {
                        Labware alabware = new Labware();
                        alabware = GetLabware(jArray[k]["labwereid"].ToString());
                        alabware.x = jArray[k]["x"].ToString();
                        alabware.y = jArray[k]["y"].ToString();
                        alabware.z = jArray[k]["z"].ToString();
                        labwares.Add(alabware);
                    }
                }
            }
            return labwares;
        }
        #endregion

        #region 查询返回板位类耗材数据集
        /// <summary>
        /// 查询返回板位类耗材数据集
        /// </summary>
        /// <returns></returns>
        public static ObservableCollection<Labware> GetSpecialShelfLabware()
        {
            using (var db = GetInstance())
            {
                return new ObservableCollection<Labware>(db.Queryable<Labware>().Where(it => it.labware_status.Equals(1) && it.labware_type_id.Equals("9")).OrderBy(it => it.labware_type_id).ToList());
            }
        }
        #endregion

        #region 查询孔位数据 by 耗材Id 仅管架类型
        /// <summary>
        /// 查询孔位数据 by 耗材Id 仅管架类型
        /// </summary>
        /// <param name="labwareid">耗材Id</param>
        /// <returns></returns>
        public static ObservableCollection<LabwareWellInfo> GetSpecialLabwareWellInfo(string labwareid)
        {
            using (var db = GetInstance())
            {
                return new ObservableCollection<LabwareWellInfo>(db.Queryable<LabwareWellInfo>().Where(it => it.labware_id.Equals(labwareid)).OrderBy(it=>it.labware_well_name,SqlSugar.OrderByType.Asc).ToList());
            }
        }
        #endregion

        #region 查询孔位数据是否存在 by 孔位数据Id 仅管架类型
        /// <summary>
        /// 查询孔位数据是否存在 by 孔位数据Id 仅管架类型
        /// </summary>
        /// <param name="labwareWellInfoid">孔位数据Id</param>
        /// <returns></returns>
        public static bool GetSpecialLabwareWellInfoExistById(string labwareWellInfoid)
        {
            bool result = false;
            using (var db = GetInstance())
            {
                var query= db.Queryable<LabwareWellInfo>().Single(it => it.well_id.Equals(labwareWellInfoid));
                if(query!=null)
                {
                    result = true;
                }
            }
            return result;
        }
        #endregion

        #region 添加孔位数据 by 孔位数据对象
        /// <summary>
        /// 添加孔位数据 by 孔位数据对象
        /// </summary>
        /// <param name="labwareWellInfo">孔位数据对象</param>
        /// <returns></returns>
        public static int AddLabwareWellInfoIntodb(LabwareWellInfo labwareWellInfo)
        {
            using (var db = GetInstance())
            {
                var result = 0;
                db.BeginTran(); // 开启事务
                try
                {
                    result = db.Insertable<LabwareWellInfo>(labwareWellInfo).ExecuteCommand();
                    db.CommitTran(); // 提交事务
                }
                catch (Exception ex)
                {
                    db.RollbackTran(); // 事务回滚
                                       // 处理异常
                    result = 0;
                }
                return result;
            }
        }
        #endregion

        #region 删除所有孔位数据 by 耗材Id
        /// <summary>
        /// 删除所有孔位数据 by 耗材Id
        /// </summary>
        /// <param name="labwareid">耗材Id</param>
        /// <returns></returns>
        public static int DeleteLabwareWellInfoByLabwareIddb(string labwareid)
        {
            using (var db = GetInstance())
            {
                var result = 0;
                db.BeginTran(); // 开启事务
                try
                {
                    result = db.Deleteable<LabwareWellInfo>().Where(it=>it.labware_id.Equals(labwareid)).ExecuteCommand();
                    db.CommitTran(); // 提交事务
                }
                catch (Exception ex)
                {
                    db.RollbackTran(); // 事务回滚
                                       // 处理异常
                    result = 0;
                }
                return result;
            }
        }
        #endregion

        #region 更新孔位数据 by 孔位数据对象
        /// <summary>
        /// 更新孔位数据 by 孔位数据对象
        /// </summary>
        /// <param name="labwareWellInfo">孔位数据对象</param>
        /// <returns></returns>
        public static int UpdateLabwareWellInfoIntodb(LabwareWellInfo labwareWellInfo)
        {
            using (var db = GetInstance())
            {
                var result = 0;
                db.BeginTran(); // 开启事务
                try
                {
                    result = db.Updateable<LabwareWellInfo>().Where(it => it.well_id.Equals(labwareWellInfo.well_id)).ExecuteCommand();
                    db.CommitTran(); // 提交事务
                }
                catch (Exception ex)
                {
                    db.RollbackTran(); // 事务回滚
                                       // 处理异常
                    result = 0;
                }
                return result;
            }
        }
        #endregion
    }
}