using DataEntity;
using DataRWDAL.Base;
using SqlSugar;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Dynamic;
using System.Linq;
using System.Reflection;
using XImagingXhandler.XDAL;
using static System.Net.WebRequestMethods;

namespace DataRWDAL
{
    /// <summary>
    /// 台面板位库表操作类
    /// </summary>
    public class LatticeDB : BaseDB
    {
        #region 查询返回台面板位数据集 by 设备型号名
        /// <summary>
        /// 查询返回台面板位数据集 by 设备型号名
        /// </summary>
        /// <param name="deviceNumber">设备型号名</param>
        /// <returns>台面板位数据集</returns>
        public static List<Lattice> GetLatticeDataFromdb(string deviceNumber)
        {
            List<Lattice> lattices = new List<Lattice>();
            using (var db = GetInstance())
            {
                // 多表查询
                var query = db.Queryable<Lattice, SoftwareInformation>
                    (
                        (st, sc) =>
                        new JoinQueryInfos(JoinType.Left, st.software_information_id == sc.software_information_id)
                    )
                    .Where((st, sc) => sc.software_device_number == deviceNumber&&st.lattice_state.Equals(1));

                var result =query.ToList(); // 转换为List

                foreach (Lattice lattice in result)
                {
                    var filterQuery = db.Queryable<TabletopRelLattice>().Where(x => x.lattice_id.Equals(System.Convert.ToInt32(lattice.lattice_id)));
                    if (filterQuery != null && filterQuery.Count() > 0)
                    {
                    }
                    else
                    {
                        lattices.Add(lattice);
                    }
                }

                if (lattices != null && lattices.Count > 0)
                {
                    var a = lattices[lattices.Count - 1];
                    if (a.lattice_num.Equals("P0"))
                    {
                        lattices.RemoveAt(lattices.Count - 1);
                        lattices.Insert(0, a);
                    }
                }

                return lattices;//db.Queryable<Lattice, SoftwareInformation>((st, sc) => st.software_information_id == sc.software_information_id && sc.software_device_number == deviceNumber).ToList();
            }
        }
        #endregion

        #region 查询返回台面板位数据集 by 设备型号名、设备臂Id
        /// <summary>
        /// 查询返回台面板位数据集 by 设备型号名
        /// </summary>
        /// <param name="deviceNumber">设备型号名</param>
        /// <param name="device_arm_id">设备臂Id</param>
        /// <param name="isbaselattice">是否是构建台面用的板位:0： 不是；1：是</param>
        /// <returns>台面板位数据集</returns>
        public static List<Lattice> GetLatticeDataFromdb(string deviceNumber, int device_arm_id,int isbaselattice=1)
        {
            List<Lattice> lattices= new List<Lattice>();
            using (var db = GetInstance())
            {
                // 多表查询
                var query = db.Queryable<Lattice, SoftwareInformation>
                    (
                        (st, sc) =>
                        new JoinQueryInfos(JoinType.Left, st.software_information_id == sc.software_information_id)
                    )
                    .Where((st, sc) => sc.software_device_number == deviceNumber && st.device_arm_id == device_arm_id&&st.lattice_state.Equals(1));//.OrderBy(st=>st.lattice_num,OrderByType.Asc);

                var result =query.ToList();

                foreach (Lattice lattice in result)
                {
                    var filterQuery = db.Queryable<TabletopRelLattice>().Where(x => x.lattice_id.Equals(System.Convert.ToInt32(lattice.lattice_id)));
                    if (filterQuery != null && filterQuery.Count() > 0)
                    {
                        if(isbaselattice==0) //非出厂台面
                        {
                            lattices.Add(lattice); 
                        }
                        else if(isbaselattice==1)
                        {
                            lattices.Add(lattice);
                        }
                    }
                    else
                    {
                        lattices.Add(lattice); //出厂默认台面
                    }
                }

                if (lattices != null && lattices.Count > 0)
                {
                    var a = lattices[lattices.Count - 1];
                    if (a.lattice_num.Equals("P0"))//把P0移动到第一个
                    {
                        lattices.RemoveAt(lattices.Count - 1);
                        lattices.Insert(0, a);
                    }
                }

                Lattice lat = new Lattice();
                lat.lattice_id = "0";
                lat.lattice_num = "请选择";
                lattices.Insert(0,lat);
                return lattices;
            }
        }
        #endregion

        #region 只查询出板位的编号名，提供给该设备下的夹爪使用
        /// <summary>
        /// 只查询出板位的编号名，提供给该设备下的夹爪使用
        /// </summary>
        /// <param name="deviceNumber">设备型号</param>
        /// <param name="device_arm_id">夹爪臂Id</param>
        /// <param name="mainChannelArmId">主通道臂Id</param>
        /// <returns></returns>
        public static List<Lattice> GetGripperLatticeNumDataFromdb(string deviceNumber, int mainChannelArmId)
        {
            List<Lattice> lattices = new List<Lattice>();
            using (var db = GetInstance())
            {
                // 多表查询
                var query = db.Queryable<Lattice, SoftwareInformation>
                    (
                        (st, sc) =>
                        new JoinQueryInfos(JoinType.Left, st.software_information_id == sc.software_information_id)
                    )
                    .Where((st, sc) => sc.software_device_number == deviceNumber && st.device_arm_id == mainChannelArmId && st.lattice_state.Equals(1));

                var result = query.ToList();

                foreach (Lattice lattice in result)
                {
                    lattice.lattice_id = "";
                    var filterQuery = db.Queryable<TabletopRelLattice>().Where(x => x.lattice_id.Equals(System.Convert.ToInt32(lattice.lattice_id)));
                    if (filterQuery != null && filterQuery.Count() > 0)
                    {
                        lattices.Add(lattice); //非出厂台面
                    }
                    else
                    {
                        lattices.Add(lattice); //出厂默认台面
                    }
                }

                if (lattices != null && lattices.Count > 0)
                {
                    var a = lattices[lattices.Count - 1];
                    if (a.lattice_num.Equals("P0"))//把P0移动到第一个
                    {
                        lattices.RemoveAt(lattices.Count - 1);
                        lattices.Insert(0, a);
                    }
                }

                Lattice lat = new Lattice();
                lat.lattice_id = "0";
                lat.lattice_num = "请选择";
                lattices.Insert(0, lat);
                return lattices;
            }

            return lattices;
        }
        #endregion

        #region 查询返回台面板位数据集 by 设备型号名、设备臂Id
        /// <summary>
        /// 查询返回台面板位数据集 by 设备型号名
        /// </summary>
        /// <param name="tabletopid">台面模板Id</param>
        /// <returns>台面板位数据集</returns>
        public static List<Lattice> GetLatticeDataByTabletopTemplateFromdb(string tabletopid)
        {
            using (var db = GetInstance())
            {
                // 多表查询
                var query = db.Queryable<Lattice,TabletopRelLattice>
                    (
                        (st, sc) =>
                        new JoinQueryInfos(JoinType.Left, st.lattice_id == sc.lattice_id.ToString())
                    )
                    .Where((st, sc) => sc.tabletopid == tabletopid&&st.lattice_state.Equals(1));

                var result = query.OrderBy("lattice_id").ToList();

                if (result != null && result.Count > 0)
                {
                    var a = result[result.Count - 1];
                    if (a.lattice_num.Equals("P0"))//把P0移动到第一个
                    {
                        result.RemoveAt(result.Count - 1);
                        result.Insert(0, a);
                    }
                }

                Lattice lat = new Lattice();
                lat.lattice_id = "0";
                lat.lattice_num = "请选择";
                result.Insert(0, lat);
                return result;
            }
        }
        #endregion

        #region 查询返回台面板位数据 by 台面板位Id
        /// <summary>
        /// 查询返回台面板位数据 by 台面板位Id
        /// </summary>
        /// <param name="latticeId">台面板位Id</param>
        /// <returns>台面板位数据</returns>
        public static Lattice GetLatticeDataByIdFromdb(string latticeId)
        {
            using (var db = GetInstance())
            {
                return db.Queryable<Lattice>().Single(it => it.lattice_id.Equals(latticeId));
            }
        }
        #endregion

        #region 查询返回台面板位数据 by 台面板位号、设备臂Id、设备型号
        /// <summary>
        /// 查询返回台面板位数据 by 台面板位号、设备臂Id、设备型号
        /// </summary>
        /// <param name="latticeNum">台面板位号</param>
        /// <param name="armId">设备臂Id</param>
        /// <param name="deviceNumber">设备型号</param>
        /// <param name="tabletopid">台面模板Id</param>
        /// <returns>台面板位数据</returns>
        public static Lattice GetLatticeDataByIdFromdb(string latticeNum, int armId, string deviceNumber)
        {
            using (var db = GetInstance())
            {
                // 多表查询
                var query = db.Queryable<Lattice, SoftwareInformation>
                    (
                        (st, sc) =>
                        new JoinQueryInfos(JoinType.Left, st.software_information_id == sc.software_information_id)
                    )
                    .Where((st, sc) => sc.software_device_number == deviceNumber && st.device_arm_id == armId&&st.lattice_num.Equals(latticeNum)&&st.lattice_state.Equals(1)).ToList();
                foreach(Lattice lattice in query)
                {
                    var filterQuery = db.Queryable<TabletopRelLattice>().Where(x => x.lattice_id.Equals(System.Convert.ToInt32(lattice.lattice_id)));
                    if(filterQuery!=null&&filterQuery.Count()>0)
                    {
                    }
                    else
                    {
                        return lattice;
                    }
                }

                return null;
            }
        }
        #endregion

        #region 查询返回台面板位数据集 by 设备臂Id、设备型号
        /// <summary>
        /// 查询返回台面板位数据集 by 设备臂Id、设备型号
        /// </summary>
        /// <param name="armId">设备臂Id</param>
        /// <param name="deviceNumber">设备型号</param>
        /// <returns>台面板位数据</returns>
        public static List<Lattice> GetTrashLatticeDataByIdFromdb(string armId, string deviceNumber)
        {
            int mid = System.Convert.ToInt32(armId);

            using (var db = GetInstance())
            {
                // 多表查询
                var query = db.Queryable<Lattice, SoftwareInformation>
                    (
                        (st, sc) =>
                        new JoinQueryInfos(JoinType.Left, st.software_information_id == sc.software_information_id)
                    )
                    .Where((st, sc) => sc.software_device_number == deviceNumber && st.device_arm_id == mid && st.is_trash == 1);

                return query.ToList();
            }
        }
        #endregion

        #region 修改台面板位数据 by 台面板位对象
        /// <summary>
        /// 修改台面板位数据 by 台面板位对象
        /// </summary>
        /// <param name="lattice">台面板位对象</param>
        /// <returns>1：成功；0：失败</returns>
        public static int UpdateLatticeIntodb(Lattice lattice)
        {
            using (var db = GetInstance())
            {
                return db.Updateable<Lattice>(lattice).ExecuteCommand();
            }
        }
        #endregion

        #region 查询返回台面板位数据 by 台面板位号、设备臂Id、设备型号、台面模板Id
        /// <summary>
        /// 查询返回台面板位数据 by 台面板位号、设备臂Id、设备型号、台面模板Id
        /// </summary>
        /// <param name="latticeNum">台面板位号</param>
        /// <param name="armId">设备臂Id</param>
        /// <param name="deviceNumber">设备型号</param>
        /// <param name="tabletopid">台面模板id</param>
        /// <returns>台面板位数据</returns>
        public static Lattice GetLatticeDataByLatticeNumAndTempIdFromdb(string latticeNum, int armId, string deviceNumber,string tabletopid)
        {
            using (var db = GetInstance())
            {
                // 多表查询
                //var query = db.Queryable<Lattice, SoftwareInformation>
                //    (
                //        (st, sc) =>
                //        new JoinQueryInfos(JoinType.Left, st.software_information_id == sc.software_information_id)
                //    )
                //    .Where((st, sc) => sc.software_device_number == deviceNumber && st.device_arm_id == armId && st.lattice_num.Equals(latticeNum)).Single();

                var query = db.Queryable("lattice", "t").AddJoinInfo("softwareinformation", "l", "t.software_information_id=l.software_information_id and l.software_device_number='"
                    + deviceNumber + "'"
                    + " and t.device_arm_id=" + armId.ToString() + ""
                    + " and t.lattice_num='" + latticeNum + "'"
                    + " and t.lattice_state=1", JoinType.Inner)
                    .AddJoinInfo("tabletoprellattice","tl", "t.lattice_id=tl.lattice_id and tl.tabletopid='" + tabletopid+"'", JoinType.Inner)
                    .Select("t.*");

                if (query != null)
                {
                    var a = query.ToList();
                    Lattice lattice = new Lattice();
                    foreach (ExpandoObject epo in a)
                    {
                        lattice = epo.ToEntity<Lattice>();
                        lattice.lattice_id= epo.ToArray().FirstOrDefault(x => x.Key.Equals("lattice_id")).Value.ToString();
                    }
                    var result = lattice;
                    return result;
                }

                return null;
            }
        }
        #endregion
    }
}