using DataEntity;
using DataRWDAL;
using DataRWDAL.Base;
using HxEnum;
using MySql.Data.MySqlClient;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Dynamic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XCommon.Log;
using XCommon.MySql;

namespace XImagingXhandler.XDAL
{
    /// <summary>
    /// 台面模板数据库操作类
    /// </summary>
    public class TabletopTemplateDB : BaseDB
    {
        #region 获取所有可用台面模板集
        /// <summary>
        /// 获取所有可用台面模板集
        /// </summary>
        public static ObservableCollection<TabletopTemplate> GetTabletopTemplateCollectionFromdb()
        {
            using (var db = GetInstance())
            {
                return new ObservableCollection<TabletopTemplate>(db.Queryable<TabletopTemplate>().Where(it => it.tabletopstate.Equals(1)).ToList());
            }
        }
        #endregion

        #region 获取当前系统应用的台面模板
        /// <summary>
        /// 获取当前系统应用的台面模板
        /// </summary>
        public static TabletopTemplate GetCurrentAppTabletopTemplateCollectionFromdb()
        {
            using (var db = GetInstance())
            {
                var query= db.Queryable<TabletopTemplate>().Single(it => it.tabletopstate.Equals(1) && it.usestate.Equals(1));

                if(query!=null)
                {
                    return query;
                }
                else
                {
                    return null;
                }
            }
        }
        #endregion

        #region 获取台面模板通过Id
        /// <summary>
        /// 获取台面模板通过Id
        /// </summary>
        public static TabletopTemplate GetTabletopTemplateByIddb(string tabletopid)
        {
            using (var db = GetInstance())
            {
                return db.Queryable<TabletopTemplate>().Single(it => it.tabletopid.Equals(tabletopid));
            }
        }
        #endregion

        #region 添加一条新的台面模板数据 by 台面模板数据
        /// <summary>
        /// 添加一条新的台面模板数据 by 台面模板数据
        /// </summary>
        /// <param name="tabletopTemplate">台面模板数据类对象</param>
        /// <returns>1:成功；0：失败</returns>
        public static int AddTabletopTemplateIntodb(TabletopTemplate tabletopTemplate)
        {
            using (var db = GetInstance())
            {
                return db.Insertable<TabletopTemplate>(tabletopTemplate).ExecuteCommand();
            }
        }
        #endregion

        #region 修改一条新的台面模板数据 by 台面模板数据
        /// <summary>
        /// 修改一条新的台面模板数据 by 台面模板数据
        /// </summary>
        /// <param name="tabletopTemplate">台面模板数据类对象</param>
        /// <returns>1:成功；0：失败</returns>
        public static int UpdateTabletopTemplateIntodb(TabletopTemplate tabletopTemplate)
        {
            using (var db = GetInstance())
            {
                return db.Updateable<TabletopTemplate>(tabletopTemplate).ExecuteCommand();
            }
        }
        #endregion

        #region 添加一条台面板位数据 by 台面模板Id、台面板位对象
        /// <summary>
        /// 添加一条台面板位数据 by 台面模板Id、台面板位对象
        /// </summary>
        /// <param name="tabletopid">台面模板Id</param>
        /// <param name="lattice">板位数据对象</param>
        /// <returns>1:成功；0：失败</returns>
        public static int AddLatticeIntodb(string tabletopid, Lattice lattice)
        {
            using (var db = GetInstance())
            {
                return db.Insertable<Lattice>(lattice).ExecuteCommand();
            }
        }
        #endregion

        #region 获取某个模板下的所有板位数据集
        /// <summary>
        /// 获取某个模板下的所有板位数据集
        /// </summary>
        /// <param name="tabletopTemplateId">台面模板Id</param>
        /// <returns>所有板位数据集</returns>
        public static ObservableCollection<Lattice> GetLatticesByTabletopTemplateId(string tabletopTemplateId)
        {
            using (var db = GetInstance())
            {
                var query = db.Queryable("tabletoprellattice", "t").AddJoinInfo("lattice", "l", "t.lattice_id=l.lattice_id and t.tabletopid='"+ tabletopTemplateId + "' and l.lattice_state=1 and t.isbaselattice=1", JoinType.Inner).Select("l.*,t.lattice_pixel_x,t.lattice_pixel_y,t.labware_id");
                if(query!=null)
                {
                    var a = query.ToList();
                    ObservableCollection<Lattice> liquids = new ObservableCollection<Lattice>();
                    foreach (ExpandoObject epo in a)
                    {
                        Lattice lq = epo.ToEntity<Lattice>();
                        lq.lattice_id = epo.ToArray().FirstOrDefault(x=>x.Key.Equals("lattice_id")).Value.ToString();
                        liquids.Add(lq);
                    }
                    var result = liquids;
                    return result;
                }
            }
            return null;
        }
        #endregion
    }
}