using DataEntity;
using DataEntity.Device;
using DataEntity.Sockets.TakePhoto;
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 XCommon.Log;
using XCommon.MySql;
using XImagingXhandler.XDAL;

namespace DataRWDAL
{
    public class ExperimentDB : BaseDB
    {
        #region 登录实验信息
        /// <summary>
        /// 登录实验信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static int Add(ExperimentModel model)
        {
            using (var db = GetInstance())
            {
                return db.Insertable<ExperimentModel>(model).ExecuteCommand();
            }
        }
        #endregion

        #region 更新实验信息
        /// <summary>
        /// 更新实验信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static int Update(ExperimentModel model)
        {
            using (var db = GetInstance())
            {
                return db.Updateable<ExperimentModel>(model).ExecuteCommand();
            }
        }
        #endregion

        #region 根据实验运行时间、来源板码、目标板码，查询实验列表数据集
        /// <summary>
        /// 根据实验运行时间、来源板码、目标板码，查询实验列表数据集
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="srcBarcode"></param>
        /// <param name="tagBarcode"></param>
        /// <returns></returns>
        public static ObservableCollection<ExperimentModel> GetExperimentCollectionFromdb(string startTime,string endTime,string srcBarcode,string tagBarcode)
        {
            ObservableCollection<ExperimentModel> experimentModelsResult = new ObservableCollection<ExperimentModel>();
            int countNum = 1;
            using (var db = GetInstance())
            {
                if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                {
                    var queryList= new ObservableCollection<ExperimentModel>(db.Queryable<ExperimentModel>().Where(it => (!it.Status.Equals(-1))&&(SqlFunc.Between(it.LunchTime,Convert.ToDateTime(startTime),Convert.ToDateTime(endTime))||
                                                                             SqlFunc.Between(it.EndTime, Convert.ToDateTime(startTime), Convert.ToDateTime(endTime)))).OrderBy(it => it.EndTime, OrderByType.Desc).ToList());

                    //进一步搜索该实验报表中的来源barcode和目标barcode
                    if(queryList!=null)
                    {
                        foreach(var item in queryList)
                        {
                            item.indexNum = (countNum++).ToString();
                            if (!string.IsNullOrEmpty(srcBarcode) && !string.IsNullOrEmpty(tagBarcode))
                            {
                                var query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId) && t.SourceBarcode.Contains(srcBarcode) && t.TargetBarcode.Contains(tagBarcode)).ToList();
                                string sourceBarcodes = string.Empty;
                                string targetBarcodes = string.Empty;
                                if (query != null)
                                {
                                    query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId)).ToList();
                                    if (query != null)
                                    {
                                        List<string> sourceList = new List<string>();
                                        List<string> targetList = new List<string>();
                                        foreach (var m in query)
                                        {
                                            if (!sourceList.Contains(m.SourceBarcode))
                                            {
                                                sourceList.Add(m.SourceBarcode);
                                            }
                                            if (!targetList.Contains(m.TargetBarcode))
                                            {
                                                targetList.Add(m.TargetBarcode);
                                            }
                                        }

                                        sourceBarcodes = string.Format(",", sourceList);
                                        targetBarcodes = string.Format(",", targetList);
                                    }
                                    item.ExperimentName = (item.ExperimentName != null && (item.ExperimentName.Length > 0)) ? item.ExperimentName.Substring(0, item.ExperimentName.LastIndexOf('.')) : "";
                                    item.SourceBarcodes = sourceBarcodes;
                                    item.TargetBarcodes = targetBarcodes;
                                    experimentModelsResult.Add(item);
                                }
                            }
                            else if (!string.IsNullOrEmpty(srcBarcode) && string.IsNullOrEmpty(tagBarcode))
                            {
                                var query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId) && t.SourceBarcode.Contains(srcBarcode)).ToList();
                                string sourceBarcodes = string.Empty;
                                string targetBarcodes = string.Empty;
                                if (query != null)
                                {
                                    query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId)).ToList();
                                    if (query != null)
                                    {
                                        List<string> sourceList = new List<string>();
                                        List<string> targetList = new List<string>();
                                        foreach (var m in query)
                                        {
                                            if (!sourceList.Contains(m.SourceBarcode))
                                            {
                                                sourceList.Add(m.SourceBarcode);
                                            }
                                            if (!targetList.Contains(m.TargetBarcode))
                                            {
                                                targetList.Add(m.TargetBarcode);
                                            }
                                        }

                                        sourceBarcodes = string.Format(",", sourceList);
                                        targetBarcodes = string.Format(",", targetList);
                                    }
                                    item.ExperimentName = (item.ExperimentName != null && (item.ExperimentName.Length > 0)) ? item.ExperimentName.Substring(0, item.ExperimentName.LastIndexOf('.')) : "";
                                    item.SourceBarcodes = sourceBarcodes;
                                    item.TargetBarcodes = targetBarcodes;
                                    experimentModelsResult.Add(item);
                                }
                            }
                            else if (string.IsNullOrEmpty(srcBarcode) && !string.IsNullOrEmpty(tagBarcode))
                            {
                                var query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId) && t.TargetBarcode.Contains(tagBarcode)).ToList();
                                string sourceBarcodes = string.Empty;
                                string targetBarcodes = string.Empty;
                                if (query != null)
                                {
                                    query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId)).ToList();
                                    if (query != null)
                                    {
                                        List<string> sourceList = new List<string>();
                                        List<string> targetList = new List<string>();
                                        foreach (var m in query)
                                        {
                                            if (!sourceList.Contains(m.SourceBarcode))
                                            {
                                                sourceList.Add(m.SourceBarcode);
                                            }
                                            if (!targetList.Contains(m.TargetBarcode))
                                            {
                                                targetList.Add(m.TargetBarcode);
                                            }
                                        }

                                        sourceBarcodes = string.Format(",", sourceList);
                                        targetBarcodes = string.Format(",", targetList);
                                    }
                                    item.ExperimentName = (item.ExperimentName != null && (item.ExperimentName.Length > 0)) ? item.ExperimentName.Substring(0, item.ExperimentName.LastIndexOf('.')) : "";
                                    item.SourceBarcodes = sourceBarcodes;
                                    item.TargetBarcodes = targetBarcodes;
                                    experimentModelsResult.Add(item);
                                }
                            }
                            else if (string.IsNullOrEmpty(srcBarcode) && string.IsNullOrEmpty(tagBarcode))
                            {
                                string sourceBarcodes = string.Empty;
                                string targetBarcodes = string.Empty;

                                var query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId)).ToList();
                                if (query != null)
                                {
                                    List<string> sourceList = new List<string>();
                                    List<string> targetList = new List<string>();
                                    foreach (var m in query)
                                    {
                                        if (!sourceList.Contains(m.SourceBarcode))
                                        {
                                            sourceList.Add(m.SourceBarcode);
                                        }
                                        if (!targetList.Contains(m.TargetBarcode))
                                        {
                                            targetList.Add(m.TargetBarcode);
                                        }
                                    }

                                    sourceBarcodes = string.Format(",", sourceList);
                                    targetBarcodes = string.Format(",", targetList);
                                }
                                item.ExperimentName = (item.ExperimentName != null && (item.ExperimentName.Length > 0)) ? item.ExperimentName.Substring(0, item.ExperimentName.LastIndexOf('.')) : "";
                                item.SourceBarcodes = sourceBarcodes;
                                item.TargetBarcodes = targetBarcodes;
                                experimentModelsResult.Add(item);
                            }
                        }
                    }
                }
                else if(!string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    endTime = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    var queryList = new ObservableCollection<ExperimentModel>(db.Queryable<ExperimentModel>().Where(it => (!it.Status.Equals(-1)) && (SqlFunc.Between(it.LunchTime, Convert.ToDateTime(startTime), Convert.ToDateTime(endTime)) ||
                                                                             SqlFunc.Between(it.EndTime, Convert.ToDateTime(startTime), Convert.ToDateTime(endTime)))).OrderBy(it => it.EndTime, OrderByType.Desc).ToList());

                    //进一步搜索该实验报表中的来源barcode和目标barcode
                    if (queryList != null)
                    {
                        foreach (var item in queryList)
                        {
                            item.indexNum = (countNum++).ToString();
                            if (!string.IsNullOrEmpty(srcBarcode) && !string.IsNullOrEmpty(tagBarcode))
                            {
                                var query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId) && t.SourceBarcode.Contains(srcBarcode) && t.TargetBarcode.Contains(tagBarcode)).ToList();
                                string sourceBarcodes = string.Empty;
                                string targetBarcodes = string.Empty;
                                if (query != null)
                                {
                                    query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId)).ToList();
                                    if (query != null)
                                    {
                                        List<string> sourceList = new List<string>();
                                        List<string> targetList = new List<string>();
                                        foreach (var m in query)
                                        {
                                            if (!sourceList.Contains(m.SourceBarcode))
                                            {
                                                sourceList.Add(m.SourceBarcode);
                                            }
                                            if (!targetList.Contains(m.TargetBarcode))
                                            {
                                                targetList.Add(m.TargetBarcode);
                                            }
                                        }

                                        sourceBarcodes = string.Format(",", sourceList);
                                        targetBarcodes = string.Format(",", targetList);
                                    }
                                    item.ExperimentName = (item.ExperimentName != null && (item.ExperimentName.Length > 0)) ? item.ExperimentName.Substring(0, item.ExperimentName.LastIndexOf('.')) : "";
                                    item.SourceBarcodes = sourceBarcodes;
                                    item.TargetBarcodes = targetBarcodes;
                                    experimentModelsResult.Add(item);
                                }
                            }
                            else if (!string.IsNullOrEmpty(srcBarcode) && string.IsNullOrEmpty(tagBarcode))
                            {
                                var query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId) && t.SourceBarcode.Contains(srcBarcode)).ToList();
                                string sourceBarcodes = string.Empty;
                                string targetBarcodes = string.Empty;
                                if (query != null)
                                {
                                    query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId)).ToList();
                                    if (query != null)
                                    {
                                        List<string> sourceList = new List<string>();
                                        List<string> targetList = new List<string>();
                                        foreach (var m in query)
                                        {
                                            if (!sourceList.Contains(m.SourceBarcode))
                                            {
                                                sourceList.Add(m.SourceBarcode);
                                            }
                                            if (!targetList.Contains(m.TargetBarcode))
                                            {
                                                targetList.Add(m.TargetBarcode);
                                            }
                                        }

                                        sourceBarcodes = string.Format(",", sourceList);
                                        targetBarcodes = string.Format(",", targetList);
                                    }
                                    item.ExperimentName = (item.ExperimentName != null && (item.ExperimentName.Length > 0)) ? item.ExperimentName.Substring(0, item.ExperimentName.LastIndexOf('.')) : "";
                                    item.SourceBarcodes = sourceBarcodes;
                                    item.TargetBarcodes = targetBarcodes;
                                    experimentModelsResult.Add(item);
                                }
                            }
                            else if (string.IsNullOrEmpty(srcBarcode) && !string.IsNullOrEmpty(tagBarcode))
                            {
                                var query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId) && t.TargetBarcode.Contains(tagBarcode)).ToList();
                                string sourceBarcodes = string.Empty;
                                string targetBarcodes = string.Empty;
                                if (query != null)
                                {
                                    query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId)).ToList();
                                    if (query != null)
                                    {
                                        List<string> sourceList = new List<string>();
                                        List<string> targetList = new List<string>();
                                        foreach (var m in query)
                                        {
                                            if (!sourceList.Contains(m.SourceBarcode))
                                            {
                                                sourceList.Add(m.SourceBarcode);
                                            }
                                            if (!targetList.Contains(m.TargetBarcode))
                                            {
                                                targetList.Add(m.TargetBarcode);
                                            }
                                        }

                                        sourceBarcodes = string.Format(",", sourceList);
                                        targetBarcodes = string.Format(",", targetList);
                                    }
                                    item.ExperimentName = (item.ExperimentName != null && (item.ExperimentName.Length > 0)) ? item.ExperimentName.Substring(0, item.ExperimentName.LastIndexOf('.')) : "";
                                    item.SourceBarcodes = sourceBarcodes;
                                    item.TargetBarcodes = targetBarcodes;
                                    experimentModelsResult.Add(item);
                                }
                            }
                            else if (string.IsNullOrEmpty(srcBarcode) && string.IsNullOrEmpty(tagBarcode))
                            {
                                string sourceBarcodes = string.Empty;
                                string targetBarcodes = string.Empty;

                                var query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId)).ToList();
                                if (query != null)
                                {
                                    List<string> sourceList = new List<string>();
                                    List<string> targetList = new List<string>();
                                    foreach (var m in query)
                                    {
                                        if (!sourceList.Contains(m.SourceBarcode))
                                        {
                                            sourceList.Add(m.SourceBarcode);
                                        }
                                        if (!targetList.Contains(m.TargetBarcode))
                                        {
                                            targetList.Add(m.TargetBarcode);
                                        }
                                    }
                                    sourceBarcodes = string.Format(",", sourceList);
                                    targetBarcodes = string.Format(",", targetList);
                                }
                                item.ExperimentName = (item.ExperimentName != null && (item.ExperimentName.Length > 0)) ? item.ExperimentName.Substring(0, item.ExperimentName.LastIndexOf('.')) : "";
                                item.SourceBarcodes = sourceBarcodes;
                                item.TargetBarcodes = targetBarcodes;
                                experimentModelsResult.Add(item);
                            }
                        }
                    }
                }
                else if (string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))//不存在
                {

                }
                else if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
                {
                    endTime = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    var queryList = new ObservableCollection<ExperimentModel>(db.Queryable<ExperimentModel>().Where(it => (!it.Status.Equals(-1))).OrderBy(it => it.EndTime, OrderByType.Desc).ToList());

                    //进一步搜索该实验报表中的来源barcode和目标barcode
                    if (queryList != null)
                    {
                        foreach (var item in queryList)
                        {
                            item.indexNum = (countNum++).ToString();
                            if (!string.IsNullOrEmpty(srcBarcode) && !string.IsNullOrEmpty(tagBarcode))
                            {
                                var query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId) && t.SourceBarcode.Contains(srcBarcode) && t.TargetBarcode.Contains(tagBarcode)).ToList();
                                string sourceBarcodes = string.Empty;
                                string targetBarcodes = string.Empty;
                                if (query != null)
                                {
                                    query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId)).ToList();
                                    if (query != null)
                                    {
                                        List<string> sourceList = new List<string>();
                                        List<string> targetList = new List<string>();
                                        foreach (var m in query)
                                        {
                                            if (!sourceList.Contains(m.SourceBarcode))
                                            {
                                                sourceList.Add(m.SourceBarcode);
                                            }
                                            if (!targetList.Contains(m.TargetBarcode))
                                            {
                                                targetList.Add(m.TargetBarcode);
                                            }
                                        }

                                        sourceBarcodes = string.Format(",", sourceList);
                                        targetBarcodes = string.Format(",", targetList);
                                    }
                                    item.ExperimentName = (item.ExperimentName != null && (item.ExperimentName.Length > 0)) ? item.ExperimentName.Substring(0, item.ExperimentName.LastIndexOf('.')) : "";
                                    item.SourceBarcodes = sourceBarcodes;
                                    item.TargetBarcodes = targetBarcodes;
                                    experimentModelsResult.Add(item);
                                }
                            }
                            else if (!string.IsNullOrEmpty(srcBarcode) && string.IsNullOrEmpty(tagBarcode))
                            {
                                var query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId) && t.SourceBarcode.Contains(srcBarcode)).ToList();
                                string sourceBarcodes = string.Empty;
                                string targetBarcodes = string.Empty;
                                if (query != null)
                                {
                                    query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId)).ToList();
                                    if (query != null)
                                    {
                                        List<string> sourceList = new List<string>();
                                        List<string> targetList = new List<string>();
                                        foreach (var m in query)
                                        {
                                            if (!sourceList.Contains(m.SourceBarcode))
                                            {
                                                sourceList.Add(m.SourceBarcode);
                                            }
                                            if (!targetList.Contains(m.TargetBarcode))
                                            {
                                                targetList.Add(m.TargetBarcode);
                                            }
                                        }

                                        sourceBarcodes = string.Format(",", sourceList);
                                        targetBarcodes = string.Format(",", targetList);
                                    }
                                    item.ExperimentName = (item.ExperimentName != null && (item.ExperimentName.Length > 0)) ? item.ExperimentName.Substring(0, item.ExperimentName.LastIndexOf('.')) : "";
                                    item.SourceBarcodes = sourceBarcodes;
                                    item.TargetBarcodes = targetBarcodes;
                                    experimentModelsResult.Add(item);
                                }
                            }
                            else if (string.IsNullOrEmpty(srcBarcode) && !string.IsNullOrEmpty(tagBarcode))
                            {
                                var query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId) && t.TargetBarcode.Contains(tagBarcode)).ToList();
                                string sourceBarcodes = string.Empty;
                                string targetBarcodes = string.Empty;
                                if (query != null)
                                {
                                    query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId)).ToList();
                                    if (query != null)
                                    {
                                        List<string> sourceList = new List<string>();
                                        List<string> targetList = new List<string>();
                                        foreach (var m in query)
                                        {
                                            if (!sourceList.Contains(m.SourceBarcode))
                                            {
                                                sourceList.Add(m.SourceBarcode);
                                            }
                                            if (!targetList.Contains(m.TargetBarcode))
                                            {
                                                targetList.Add(m.TargetBarcode);
                                            }
                                        }

                                        sourceBarcodes = string.Format(",", sourceList);
                                        targetBarcodes = string.Format(",", targetList);
                                    }
                                    item.ExperimentName = (item.ExperimentName != null && (item.ExperimentName.Length > 0)) ? item.ExperimentName.Substring(0, item.ExperimentName.LastIndexOf('.')) : "";
                                    item.SourceBarcodes = sourceBarcodes;
                                    item.TargetBarcodes = targetBarcodes;
                                    experimentModelsResult.Add(item);
                                }
                            }
                            else if (string.IsNullOrEmpty(srcBarcode) && string.IsNullOrEmpty(tagBarcode))
                            {
                                string sourceBarcodes = string.Empty;
                                string targetBarcodes = string.Empty;

                                var query = db.Queryable<ExperimentRunDetailModel>().Where(t => t.ExperimentId.Equals(item.ExperimentId)).ToList();
                                if (query != null)
                                {
                                    List<string> sourceList = new List<string>();
                                    List<string> targetList = new List<string>();
                                    foreach (var m in query)
                                    {
                                        if (!sourceList.Contains(m.SourceBarcode) && m.SourceBarcode != null && m.SourceBarcode != string.Empty)
                                        {
                                            sourceList.Add(m.SourceBarcode);
                                        }
                                        if (!targetList.Contains(m.TargetBarcode)&&m.TargetBarcode!=null&&m.TargetBarcode!=string.Empty)
                                        {
                                            targetList.Add(m.TargetBarcode);
                                        }
                                    }
                                    sourceBarcodes = string.Join(",", sourceList);
                                    targetBarcodes = string.Join(",", targetList);
                                }
                                item.ExperimentName = (item.ExperimentName != null && (item.ExperimentName.Length > 0)) ? item.ExperimentName.Substring(0, item.ExperimentName.LastIndexOf('.')) : "";
                                item.SourceBarcodes = sourceBarcodes;
                                item.TargetBarcodes = targetBarcodes;
                                experimentModelsResult.Add(item);
                            }
                        }
                    }
                }
            }
            return experimentModelsResult;
        }
        #endregion

        #region 根据实验Id 获取实验数据
        /// <summary>
        /// 根据实验Id 获取实验数据
        /// </summary>
        /// <param name="experimentid">实验Id</param>
        /// <returns>实验数据</returns>
        public static ExperimentModel GetExperimentModelFromdb(string experimentid)
        {
            using (var db = GetInstance())
            {
                return db.Queryable<ExperimentModel>().Single(it => it.ExperimentId.Equals(experimentid));
            }
        }
        #endregion
    }
}