using AUTS.Domain;
using AUTS.Domain.Entities;
using AUTS.Domain.ViewModels;
using AUTS.Services.Cache;
using AUTS.Services.DBUtility.Custom;
using AUTS.Services.Enums;
using AUTS.Services.Extensions;
using AUTS.Services.Tool;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace AUTS.Services.Manager
{
    public partial class Capacity
    {
        private static string TestLogTbName = "TBL_{0}_{1}_TestLog";//测试记录表名

        /// <summary>
        /// 获取当前机型最大ID
        /// </summary>
        /// <param name="ProjectID"></param>
        /// <param name="context"></param>
        /// <returns></returns>
        public static int GerOnProjectMaxID(int ProjectID, List<TBL_StationList> cacheUserStation, System.Web.HttpContext context)
        {
            var stationID = cacheUserStation.Where(x => x.ProjectID == ProjectID).Min(m => m.ID);
            var dbTableName = string.Format(TestLogTbName, ProjectID, stationID);

            string strsql = "select max(ID) from " + dbTableName;
            string connectionString = Users.GerOnUserCofin(context);
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(strsql, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return 0;
                        }
                        else
                        {
                            return int.Parse(obj.ToString());
                        }
                    }
                    catch (MySqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
            //return DBUtility.Custom.DbHelperMySqlCustom.GetMaxID("ID", dbTableName);
        }

        public static ReturnResult<object> GetTestLogList(RequestTestLogSearch reqModel, List<TBL_Project> cacheUserProject, List<TBL_StationList> cacheUserStation)
        {
            ReturnResult<object> result = new ReturnResult<object>();
            try
            {
                var project = cacheUserProject.SingleOrDefault(x => x.ID == reqModel.ProjectID);
                if (project == null)
                {
                    result.Message = "无效或不存在的机型";
                    return result;
                }

                if (reqModel.KeyWhere != "number" && reqModel.KeyWhere != "testLogID")
                {
                    result.Message = "无效查询类型";
                    return result;
                }

                if (String.IsNullOrEmpty(reqModel.KeyWord))
                {
                    result.Message = "请输入查询关键词";
                    return result;
                }

                #region 初始化
                var strWhereList = new List<string>();//条件语句列表

                string dbTableName;//查询表名字段
                StringBuilder strSqlSB = new StringBuilder();


                var rdata = new ResponseDataquerySearch();
                var thisDataList = new List<DataQuerySearch>();
                var dataList = new List<DataQuerySearch>();
                #endregion

                #region 工作站部分
                //工作站
                var stationList = cacheUserStation.Where(x => x.ProjectID == reqModel.ProjectID).OrderBy(o => o.ArtworkOrder).Select(s => new TBL_Station { StationID = s.ID, StationName = s.StationName, StationType = s.StationType, ArtworkOrder = s.ArtworkOrder }).ToList();//当前选择的工艺站
                #endregion

                var maxAssemblySn = "";//最大编号组合
                var assemPrList = stationList.Where(x => x.StationType == StationType.Assem.GetEnumDesc()).OrderByDescending(x => x.ArtworkOrder).ToList();//所有组装站

                string connectionString = Users.GerOnUserCofin();
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    DataSet dataSet = new DataSet();

                    try
                    {
                        var strSql = "";

                        connection.Open();

                        MySqlDataAdapter command = new MySqlDataAdapter();
                        if (reqModel.KeyWhere == "testLogID")
                        {
                            var forIDdbTableName = string.Format(TestLogTbName, reqModel.ProjectID, stationList.Min(m => m.StationID));
                            strSql = "SELECT DUT_SN FROM `" + forIDdbTableName + "` WHERE ID=" + reqModel.KeyWord.ToInt();
                            command = new MySqlDataAdapter(strSql.ToString(), connection);
                            command.Fill(dataSet, "ds");
                            if (dataSet.Tables[0].Rows.Count > 0)
                            {
                                maxAssemblySn = dataSet.Tables[0].Rows[0]["DUT_SN"].ToString();
                            }
                            else
                            {
                                result.Message = "该序号暂无数据";
                                return result;
                            }
                        }
                        else if (reqModel.KeyWhere == "number")
                        {
                            maxAssemblySn = reqModel.KeyWord;
                        }
                        //从组合站中取出最大的序号集
                        foreach (var item in assemPrList)
                        {
                            dataSet.Clear();
                            dbTableName = string.Format(TestLogTbName, reqModel.ProjectID, item.StationID);

                            strSql = "select AssemblySn from " + dbTableName + " Where locate('," + maxAssemblySn + ",',CONCAT(',',AssemblySn,','))";

                            command = new MySqlDataAdapter(strSql.ToString(), connection);
                            command.Fill(dataSet, "ds");

                            if (dataSet.Tables[0].Rows.Count > 0)
                            {
                                maxAssemblySn = dataSet.Tables[0].Rows[0]["AssemblySn"].ToString();
                                break;
                            }
                        }

                        var dalHelperCustom = new DALHelperCustom<DataQuerySearch>();
                        foreach (var item in stationList)
                        {
                            dataSet.Clear();
                            thisDataList = null;
                            strSqlSB.Remove(0, strSqlSB.Length);//移除stringBuilder中的内容

                            dbTableName = string.Format(TestLogTbName, reqModel.ProjectID, item.StationID);

                            strSqlSB.Append("SELECT ID,StartTime,TestResult,DUT_SN FROM " + dbTableName);
                            strSqlSB.Append(" WHERE locate(CONCAT(',',DUT_SN,','),'," + maxAssemblySn + ",')");

                            command = new MySqlDataAdapter(strSqlSB.ToString(), connection);
                            command.Fill(dataSet, "ds");

                            //if (dataSet.Tables[0].Rows.Count == 0)
                            //{
                            //    break;
                            //}
                            thisDataList = dalHelperCustom.DataRowToModels(dataSet.Tables[0]);

                            foreach (var data in thisDataList)
                            {
                                dataList.Add(new DataQuerySearch
                                {
                                    ID = data.ID,
                                    StartTime = data.StartTime == null ? "" : data.StartTime,
                                    TestResult = data.TestResult,
                                    StationName = item.StationName,
                                    StationID = item.StationID,
                                    ProjectID = reqModel.ProjectID,
                                    DUT_SN = data.DUT_SN,
                                });
                            }
                        }

                    }
                    catch (MySqlException ex)
                    {
                        result.Message = ex.Message;
                    }
                }

                rdata.ProductNumbers = maxAssemblySn;
                rdata.DataQuerySearchList = dataList;
                result.Data = rdata;
                result.Status = 200;
            }
            catch (CustomException ex)
            {
                result.Message = ex.Message;
            }
            catch (Exception ex)
            {
                result.Message = "网络系统繁忙，请稍候再试!";
                Logs.WriteErrorLog(ex);
            }


            return result;
        }


        /// <summary>
        /// 测试记录详情
        /// </summary>
        /// <param name="projectID"></param>
        /// <param name="stationID"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static ReturnResult<object> GetTestLogDetails(int projectID, int stationID, int id)
        {
            ReturnResult<object> result = new ReturnResult<object>();

            string connectionString = Users.GerOnUserCofin();
            using (MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
            {
                System.Data.DataSet dataSet = new System.Data.DataSet();
                try
                {
                    connection.Open();

                    MySql.Data.MySqlClient.MySqlDataAdapter command = new MySql.Data.MySqlClient.MySqlDataAdapter();

                    var forIDdbTableName = string.Format(TestLogTbName, projectID, stationID);
                    var strSql = "SELECT * FROM `" + forIDdbTableName + "` WHERE ID=" + id;

                    command = new MySql.Data.MySqlClient.MySqlDataAdapter(strSql.ToString(), connection);
                    command.Fill(dataSet, "ds");

                    List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();

                    foreach (DataRow Row in dataSet.Tables[0].Rows)//循环行
                    {
                        Dictionary<string, object> row = new Dictionary<string, object>();
                        for (int i = 0; i < Row.ItemArray.Length; i++)
                        {
                            row.Add(dataSet.Tables[0].Columns[i].ColumnName, Row[i].ToString());
                        }
                        rows.Add(row);
                    }
                    result.Data = rows;
                    result.Status = 200;

                }
                catch (MySql.Data.MySqlClient.MySqlException ex)
                {
                    //throw new Exception(ex.Message);
                    result.Message = ex.Message;
                    Logs.WriteErrorLog(ex);
                }
            }
            return result;
        }

        public static ReturnResult<object> GetTestLogDateList(RequestTestLogDateSearch reqModel, List<TBL_Project> cacheUserProject, List<TBL_StationList> cacheUserStation)
        {
            ReturnResult<object> result = new ReturnResult<object>();
            try
            {
                if (reqModel.StartDate == null && reqModel.EndDate == null)
                {
                    result.Message = "请选择日期";
                    return result;
                }
                var project = cacheUserProject.SingleOrDefault(x => x.ID == reqModel.ProjectID);
                if (project == null)
                {
                    result.Message = "无效或不存在的机型";
                    return result;
                }

                //第一站工作站
                var station = cacheUserStation.Where(x => x.ProjectID == reqModel.ProjectID).OrderBy(o => o.ArtworkOrder).FirstOrDefault();//当前选择的工艺站

                if (station == null)
                {
                    result.Message = "当前机型无测试站";
                    return result;
                }

                var vModel = new ResponseEveryDate();
                var dataList = new List<DataQuerySearch>();
                var listCount = 0;
                var page = reqModel.page;
                var pagesize = reqModel.pagesize;
                var dbTableName = string.Format(TestLogTbName, reqModel.ProjectID, station.ID);//查询表名
                var startDataTime = (reqModel.StartDate ?? DateTime.Now).ToString("yyyy-MM-dd 00:00:00");//开始时间
                var endDataTime = (reqModel.EndDate ?? DateTime.Now).AddDays(1).ToString("yyyy-MM-dd 00:00:00");//结束时间

                var strWhereStr = "";

                if (reqModel.StartDate != null)
                {
                    if (reqModel.EndDate != null)
                    {
                        strWhereStr += string.Format(" (DATE_FORMAT(StartTime, '%Y-%m-%d %H:%i:%s') >='{0}')", startDataTime);
                        strWhereStr += string.Format(" AND (DATE_FORMAT(StartTime, '%Y-%m-%d %H:%i:%s') <'{0}')", endDataTime);
                    }
                    else
                    {
                        strWhereStr += (string.Format("TO_DAYS('{0}')=TO_DAYS(StartTime)", startDataTime));
                    }
                }
                else
                {
                    strWhereStr += (string.Format("TO_DAYS('{0}')=TO_DAYS(StartTime)", endDataTime));
                }

                string connectionString = Users.GerOnUserCofin();
                var dalHelperCustom = new DALHelperCustom<DataQuerySearch>();
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    DataSet dataSet = new DataSet();

                    connection.Open();


                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        cmd.Connection = connection;

                        cmd.CommandText = "select count(*) from `" + dbTableName + "` WHERE " + strWhereStr + ";";

                        var obj = cmd.ExecuteScalar();
                        if (!(Object.Equals(obj, null)) && !(Object.Equals(obj, System.DBNull.Value)))
                        {
                            listCount = int.Parse(obj.ToString());
                        }
                    }


                    MySqlDataAdapter command = new MySqlDataAdapter();
                    var sqlStr = "SELECT ID,StartTime,TestResult,DUT_SN FROM " + dbTableName + " WHERE " + strWhereStr + " order by ID desc LIMIT " + (page - 1) * pagesize + "," + pagesize + ";";
                    command = new MySqlDataAdapter(sqlStr, connection);
                    command.Fill(dataSet, "ds");

                    var thisDataList = dalHelperCustom.DataRowToModels(dataSet.Tables[0]);
                    foreach (var data in thisDataList)
                    {
                        dataList.Add(new DataQuerySearch
                        {
                            ID = data.ID,
                            StartTime = data.StartTime == null ? "" : data.StartTime,
                            TestResult = data.TestResult,
                            StationName = station.StationName,
                            StationID = station.ID,
                            ProjectID = reqModel.ProjectID,
                            DUT_SN = data.DUT_SN,
                        });
                    }
                }
                vModel.DataList = dataList;
                vModel.TotalCount = listCount;
                result.Data = vModel;
                result.Status = 200;
            }
            catch (CustomException ex)
            {
                result.Message = ex.Message;
            }
            catch (Exception ex)
            {
                result.Message = "网络系统繁忙，请稍候再试!";
                Logs.WriteErrorLog(ex);
            }
            return result;
        }

        /// <summary>
        /// 抽检
        /// </summary>
        /// <param name="reqModel"></param>
        /// <param name="cacheUserProject"></param>
        /// <param name="cacheUserStation"></param>
        /// <returns></returns>
        public static ReturnResult<object> QualityAssuranceAjax(RequestQAAjaxSearch reqModel, List<TBL_Project> cacheUserProject, List<TBL_StationList> cacheUserStation)
        {
            ReturnResult<object> result = new ReturnResult<object>();
            try
            {
                #region 校验
                var project = cacheUserProject.SingleOrDefault(x => x.ID == reqModel.ProjectID);
                if (project == null)
                {
                    result.Message = "无效或不存在的机型";
                    return result;
                }

                var qaStation = cacheUserStation.FirstOrDefault(x => x.ProjectID == reqModel.ProjectID && x.StationType == StationType.QA.GetEnumDesc());
                if (qaStation == null)
                {
                    result.Message = "该机型暂无QA站";
                    return result;
                }

                if (string.IsNullOrEmpty(reqModel.Number))
                {
                    result.Message = "请输入条码号";
                    return result;
                }
                #endregion

                string dbTableName;
                var checkResult = true;
                var strCheckProcList = new List<string>();//条件语句列表
                var rdata = new ResponseQAAjaxSearch();
                var qaResultVM = new QAResultVM();
                var dataList = new List<QADataSearch>();

                #region 工艺站处理
                //QA之外的站
                var stationList = cacheUserStation.Where(x => x.ProjectID == reqModel.ProjectID && x.StationType != StationType.QA.GetEnumDesc()).OrderBy(o => o.ArtworkOrder).Select(s => new TBL_Station { StationID = s.ID, StationName = s.StationName, StationType = s.StationType }).ToList();

                var maxAssemblySn = reqModel.Number.Trim();//最大组合编号
                var assemPrList = stationList.Where(x => x.StationType == StationType.Assem.GetEnumDesc()).OrderByDescending(x => x.ArtworkOrder).ToList();//组装站列表

                #endregion

                #region 查询过程
                string connectionString = Users.GerOnUserCofin();
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    DataSet dataSet = new DataSet();
                    try
                    {
                        connection.Open();

                        MySqlDataAdapter command = new MySqlDataAdapter();
                        var strSql = "";
                        foreach (var assemPr in assemPrList)
                        {
                            dataSet.Reset();
                            //dataSet.Clear();
                            dbTableName = string.Format(TestLogTbName, reqModel.ProjectID, assemPr.StationID);

                            strSql = "select AssemblySn from " + dbTableName + " Where locate('," + reqModel.Number.Trim() + ",',CONCAT(',',AssemblySn,','));";

                            command = new MySqlDataAdapter(strSql.ToString(), connection);
                            command.Fill(dataSet, "ds");

                            if (dataSet.Tables[0].Rows.Count > 0)
                            {
                                maxAssemblySn = dataSet.Tables[0].Rows[0]["AssemblySn"].ToString();
                                break;
                            }
                        }

                        StringBuilder strSqlSB = new StringBuilder();

                        var dalHelperCustom = new DALHelperCustom<QADataSearch>();
                        var thisDataList = new List<QADataSearch>();


                        foreach (var item in stationList)
                        {
                            dataSet.Reset();
                            //dataSet.Clear();
                            thisDataList = null;
                            strSqlSB.Remove(0, strSqlSB.Length);//移除

                            dbTableName = string.Format(TestLogTbName, reqModel.ProjectID, item.StationID);

                            if (item.StationType == StationType.Test2.GetEnumDesc())
                            {
                                strSqlSB.Append("SELECT DateTime_2nd as StartTime,TestResult,DUT_SN FROM " + dbTableName);
                                strSqlSB.Append(" WHERE locate(CONCAT(',',DUT_SN,','),'," + maxAssemblySn + ",')");
                                strSqlSB.Append("  ORDER BY StartTime DESC LIMIT 1;");
                            }
                            else
                            {
                                strSqlSB.Append("SELECT StartTime,TestResult,DUT_SN FROM " + dbTableName);
                                strSqlSB.Append(" WHERE locate(CONCAT(',',DUT_SN,','),'," + maxAssemblySn + ",')");
                                strSqlSB.Append("  ORDER BY StartTime DESC LIMIT 1;");
                            }


                            command = new MySqlDataAdapter(strSqlSB.ToString(), connection);
                            command.Fill(dataSet, "ds");

                            thisDataList = dalHelperCustom.DataRowToModels(dataSet.Tables[0]);
                            if (thisDataList.Where(x => !x.TestResult).Count() > 0 || thisDataList.Where(x => x.TestResult).Count() == 0)
                            {
                                checkResult = false;
                            }

                            foreach (var data in thisDataList)
                            {
                                dataList.Add(new QADataSearch
                                {
                                    StationID = item.StationID,
                                    StartTime = data.StartTime == null ? "" : data.StartTime,
                                    TestResult = data.TestResult,
                                    StationName = item.StationName,
                                });

                                strCheckProcList.Add(item.StationName + ":" + data.TestResult + " - " + data.StartTime);
                            }

                        }
                    }
                    catch (MySqlException ex)
                    {
                        result.Message = ex.Message;
                        Logs.WriteErrorLog(ex);
                    }
                }
                #endregion

                qaResultVM.ProductNumbers = maxAssemblySn;
                qaResultVM.TestResult = checkResult;
                qaResultVM.CheckProc = string.Join("<br>", strCheckProcList.ToArray());
                qaResultVM.ProjectID = reqModel.ProjectID;
                qaResultVM.QAStationID = qaStation.ID;

                rdata.qaResultVM = qaResultVM;
                rdata.QADataSearchList = dataList;
                result.Data = rdata;
                result.Status = 200;

            }
            catch (CustomException ex)
            {
                result.Message = ex.Message;
            }
            catch (Exception ex)
            {
                result.Message = "网络系统繁忙，请稍候再试!";
                Logs.WriteErrorLog(ex);
            }
            return result;
        }

        public static ReturnResult<object> QualityAssuranceSubmit(RequestQASubmit reqModel, int uesrID)
        {
            ReturnResult<object> result = new ReturnResult<object>();
            try
            {
                var total = 0;
                var totalToday = 0;
                int rows = 0;
                var data = new ResponseQASubmit();
                var entity = new TBL_QA_TestLog
                {
                    UserID = uesrID,
                    StartTime = DateTime.Now,
                    DUT_SN = reqModel.NumberSubmit,
                    TestResult = reqModel.QA_TestResultSubmit,
                    QA_FlowLog = reqModel.QA_CheckProcSubmit
                };

                if (reqModel.QA_TestResultSubmit)
                {
                    entity.QA_CheckResult = "OK";
                }
                else
                {
                    entity.QA_CheckResult = "NG";
                }

                if (!string.IsNullOrEmpty(reqModel.QARemarks))
                {
                    entity.QA_CheckResult += ":" + reqModel.QARemarks;
                }

                var dbTableName = string.Format(TestLogTbName, reqModel.QA_ProjectID, reqModel.QA_QAStationID);

                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into " + dbTableName + "(");

                PropertyInfo[] propertys = entity.GetType().GetProperties();// 获得此模型的公共属性

                List<MySqlParameter> parameters = new List<MySqlParameter>();
                foreach (PropertyInfo pi in propertys)
                {
                    if (!pi.CanWrite) continue;
                    strSql.Append(pi.Name + ",");
                }
                strSql.Remove(strSql.Length - 1, 1);//移除最后一个逗号
                strSql.Append(" ) values (");
                foreach (PropertyInfo pi in propertys)
                {
                    if (!pi.CanWrite) continue;
                    strSql.Append("@" + pi.Name + ",");
                    parameters.Add(new MySqlParameter("@" + pi.Name, pi.GetValue(entity)));
                }
                strSql.Remove(strSql.Length - 1, 1);//移除最后一个逗号
                strSql.Append(");select @@IDENTITY");

                string connectionString = Services.Manager.Users.GerOnUserCofin();
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        try
                        {
                            if (connection.State != ConnectionState.Open)
                                connection.Open();
                            cmd.Connection = connection;
                            cmd.CommandText = strSql.ToString();

                            cmd.CommandType = CommandType.Text;//cmdType;
                            if (parameters.ToArray() != null)
                            {
                                foreach (MySqlParameter parameter in parameters.ToArray())
                                {
                                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                                        (parameter.Value == null))
                                    {
                                        parameter.Value = DBNull.Value;
                                    }
                                    cmd.Parameters.Add(parameter);
                                }
                            }

                            rows = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();

                            cmd.CommandText = "select COUNT(*) FROM " + dbTableName;

                            object obj = cmd.ExecuteScalar();
                            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                            {

                            }
                            else
                            {
                                total = int.Parse(obj.ToString());
                            }

                            cmd.CommandText = "select COUNT(*) FROM " + dbTableName + " WHERE TO_DAYS('" + DateTime.Now.ToString("yyyy-MM-dd 00:00:00") + "')=TO_DAYS(StartTime)";
                            obj = cmd.ExecuteScalar();

                            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                            {

                            }
                            else
                            {
                                totalToday = int.Parse(obj.ToString());
                            }

                        }
                        catch (MySqlException e)
                        {
                            result.Message = e.Message;
                        }
                    }
                }
                data.Total = total;
                data.TotalToday = totalToday;

                result.Data = data;
                if (rows > 0)
                {
                    result.Status = 200;
                }

                //var isSuccess = false;
                //isSuccess = new DALHelperCustom<TBL_QA_TestLog>(dbTableName).Add(entity);

                //if (isSuccess)
                //{
                //    result.Status = 200;
                //}
            }
            catch (CustomException ex)
            {
                result.Message = ex.Message;
            }
            catch (Exception ex)
            {
                result.Message = "网络系统繁忙，请稍候再试!";
                Logs.WriteErrorLog(ex);
            }
            return result;
        }

        /// <summary>
        /// 抽检记录查询
        /// </summary>
        /// <param name="reqModel"></param>
        /// <param name="cacheUserProject"></param>
        /// <param name="cacheUserStation"></param>
        /// <returns></returns>
        public static ReturnResult<object> QALogSearch(RequestQALogSearch reqModel, List<TBL_Project> cacheUserProject, List<TBL_StationList> cacheUserStation)
        {
            ReturnResult<object> result = new ReturnResult<object>();

            try
            {
                var project = cacheUserProject.SingleOrDefault(x => x.ID == reqModel.ProjectID);
                if (project == null)
                {
                    result.Message = "无效或不存在的机型";
                    return result;
                }

                //取QA站ID
                var qaStation = cacheUserStation.FirstOrDefault(x => x.ProjectID == reqModel.ProjectID && x.StationType == StationType.QA.GetEnumDesc());
                if (qaStation == null)
                {
                    result.Message = "该机型暂无QA站";
                    return result;
                }

                var dataList = new List<ResponseQALogSearch>();
                var strWhereList = new List<string>();//条件语句列表

                var startDataTime = (reqModel.StartDate ?? DateTime.Now).ToString("yyyy-MM-dd 00:00:00");//开始时间
                var endDataTime = (reqModel.EndDate ?? DateTime.Now).AddDays(1).ToString("yyyy-MM-dd 00:00:00");//结束时间

                //时间
                if (reqModel.StartDate != null)
                {
                    if (reqModel.EndDate != null)
                    {
                        if (reqModel.StartDate > reqModel.EndDate)
                        {
                            throw new CustomException("结束时间必须早于开始时间");
                        }

                        strWhereList.Add(string.Format("(DATE_FORMAT(StartTime, '%Y-%m-%d %H:%i:%s') >='{0}')", startDataTime));
                        strWhereList.Add(string.Format("(DATE_FORMAT(StartTime, '%Y-%m-%d %H:%i:%s') <='{0}')", endDataTime));

                    }
                    else
                    {
                        strWhereList.Add(string.Format("TO_DAYS('{0}')=TO_DAYS(StartTime)", startDataTime));

                    }
                }
                else
                {
                    if (reqModel.EndDate != null)
                    {
                        strWhereList.Add(string.Format("TO_DAYS('{0}')=TO_DAYS(StartTime)", endDataTime));
                    }
                }

                string strWhere = "";
                if (strWhereList.Count > 0)
                {
                    strWhere = " where " + string.Join(" AND ", strWhereList.ToArray()) + ";";
                }

                string connectionString = Users.GerOnUserCofin();
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    DataSet dataSet = new DataSet();
                    try
                    {
                        StringBuilder strSql = new StringBuilder();

                        connection.Open();

                        var dbTableName = string.Format(TestLogTbName, reqModel.ProjectID, qaStation.ID);

                        strSql.Append("select * from " + dbTableName);
                        if (!string.IsNullOrEmpty(strWhere))
                        {
                            strSql.Append(strWhere);
                        }

                        MySqlDataAdapter command = new MySqlDataAdapter(strSql.ToString(), connection);
                        command.Fill(dataSet, "ds");

                        var onQALogDataList = new DALHelperCustom<ResponseQALogSearch>().DataRowToModels(dataSet.Tables[0]);

                        foreach (var data in onQALogDataList)
                        {
                            dataList.Add(new ResponseQALogSearch
                            {
                                StartTime = data.StartTime == null ? "" : data.StartTime,
                                QA_CheckResult = data.QA_CheckResult,
                                DUT_SN = data.DUT_SN,
                            });
                        }
                    }
                    catch (MySql.Data.MySqlClient.MySqlException ex)
                    {
                        //throw new Exception(ex.Message);
                        result.Message = "网络系统繁忙，请稍候再试!";
                        Logs.WriteErrorLog(ex);
                    }
                    result.Data = dataList;
                    result.Status = 200;
                }
            }
            catch (CustomException ex)
            {
                result.Message = ex.Message;
            }
            catch (Exception ex)
            {
                result.Message = "网络系统繁忙，请稍候再试!";
                Logs.WriteErrorLog(ex);
            }
            return result;
        }


        public static ReturnResult<object> EveryDaySearch(int pid, int oid, List<TBL_Project> cacheUserProject, List<TBL_StationList> cacheUserStation, List<TBL_Orders> cacheUserOrder, string connectionString)
        {
            ReturnResult<object> result = new ReturnResult<object>();

            try
            {

                var project = cacheUserProject.SingleOrDefault(x => x.ID == pid);
                if (project == null)
                {
                    result.Message = "无效或不存在的机型";
                    return result;
                }
                var order = cacheUserOrder.SingleOrDefault(x => x.ProductID == pid && x.ID == oid);
                if (order == null)
                {
                    result.Message = "无效或不存在的订单";
                    return result;
                }

                var resModel = new ResponseEveryDaySearch
                {
                    ProjectName = project.ProjectName,
                    OrderNo = order.OrderNo,
                    OrderCount = order.OrderCount,
                    CreateTime = order.CreateTime.ToString("yyyy/MM/dd"),
                    DeliveryTime = order.DeliveryTime.ToString("yyyy/MM/dd"),
                };


                var stationList = cacheUserStation.Where(x => x.StationType != StationType.QA.GetEnumDesc() && x.ProjectID == order.ProductID).OrderBy(o => o.ArtworkOrder);//不为该机型QA的测试站

                DataTable tblDatas = new DataTable("Datas");
                var strSql = "";
                var dbTableName = "";
                var dalHelperCustom = new DALHelperCustom<VM_TBL_CapacityEveryDay>();

                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {

                    var minTime = Convert.ToDateTime(order.CreateTime.ToString("yyyy/MM/dd"));//订单最早日期
                    var maxTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy/MM/dd"));//订单最晚日期

                    DataSet dataSet = new DataSet();
                    MySqlDataAdapter command = new MySqlDataAdapter();

                    connection.Open();

                    strSql = "SELECT * FROM `TBL_ProductionPlan` WHERE OrderID=" + order.ID + " ORDER BY ProductionTime LIMIT 0,1;";
                    command = new MySqlDataAdapter(strSql, connection);
                    command.Fill(dataSet, "ds");

                    var ppList = new DALHelperCustom<TBL_ProductionPlan>().DataRowToModels(dataSet.Tables[0]);
                    if (ppList != null && ppList.Count() > 0)
                    {
                        minTime = ppList.First().ProductionTime;
                    }


                    TimeSpan ts = maxTime.Subtract(minTime);
                    int differenceInDays = ts.Days + 1;



                    tblDatas.Columns.Add("测试站", Type.GetType("System.String"));//新列
                    tblDatas.Columns.Add("数据", Type.GetType("System.String"));//新列
                    for (var i = 0; i < differenceInDays; i++)
                    {
                        tblDatas.Columns.Add(minTime.AddDays(i).ToString("yyyy/MM/dd"), Type.GetType("System.String"));//新列
                    }

                    foreach (var itemStation in stationList)
                    {
                        strSql = "";
                        dataSet.Clear();

                        dbTableName = string.Format(TestLogTbName, itemStation.ProjectID, itemStation.ID);//测试站表名


                        DataRow totalRow = tblDatas.NewRow();//总数据行
                        totalRow["测试站"] = itemStation.StationName;
                        totalRow["数据"] = "总数";


                        DataRow effRow = tblDatas.NewRow();//有效数行
                        effRow["测试站"] = itemStation.StationName;
                        effRow["数据"] = "有效";


                        DataRow passRow = tblDatas.NewRow();//PASS行
                        passRow["测试站"] = itemStation.StationName;
                        passRow["数据"] = "PASS";


                        DataRow fallRow = tblDatas.NewRow();//FALL行
                        fallRow["测试站"] = itemStation.StationName;
                        fallRow["数据"] = "FALL";

                        strSql = "select DUT_SN,TestResult,StartTime from `" + dbTableName + "` Where OrderID=" + oid;//查询语句

                        strSql += (string.Format(" AND (DATE_FORMAT(StartTime, '%Y-%m-%d %H:%i:%s') >='{0}')", minTime.ToString("yyyy-MM-dd hh:mm:ss")));
                        strSql += (string.Format(" AND (DATE_FORMAT(StartTime, '%Y-%m-%d %H:%i:%s') <='{0}')", maxTime.ToString("yyyy-MM-dd hh:mm:ss")));
                        strSql += ";";

                        command = new MySqlDataAdapter(strSql.ToString(), connection);
                        command.Fill(dataSet, "ds");

                        if (dataSet.Tables[0].Rows.Count > 0)
                        {
                            var thisDataList = dalHelperCustom.DataRowToModels(dataSet.Tables[0]).Select(s => new { StartTime = s.StartTime.ToString("yyyy/MM/dd"), s.TestResult, s.DUT_SN, DataTime = s.StartTime });
                            var dataListGroup = thisDataList.GroupBy(x => x.StartTime).OrderBy(o => o.Key);

                            foreach (var itemG in dataListGroup)
                            {
                                totalRow[itemG.Key] = thisDataList.Where(x => x.StartTime == itemG.Key).Count();
                                effRow[itemG.Key] = thisDataList.Where(x => x.StartTime == itemG.Key).GroupBy(g => g.DUT_SN).Count();

                                //passRow[itemG.Key] = thisDataList.Where(x => x.StartTime == itemG.Key && x.TestResult).Count();
                                //fallRow[itemG.Key] = thisDataList.Where(x => x.StartTime == itemG.Key && !x.TestResult).Count();

                                passRow[itemG.Key] = thisDataList.Where(x => x.StartTime == itemG.Key).GroupBy(s => s.DUT_SN)
             .Select(s => s.OrderByDescending(o => o.DataTime).FirstOrDefault()).Where(x => x.TestResult).Count();
                                fallRow[itemG.Key] = thisDataList.Where(x => x.StartTime == itemG.Key).GroupBy(s => s.DUT_SN)
             .Select(s => s.OrderByDescending(o => o.DataTime).FirstOrDefault()).Where(x => !x.TestResult).Count();
                            }
                        }
                        tblDatas.Rows.Add(totalRow);
                        tblDatas.Rows.Add(effRow);
                        tblDatas.Rows.Add(passRow);
                        tblDatas.Rows.Add(fallRow);
                    }
                }
                var rows = ModelHelper.TableToRow(tblDatas);

                resModel.Rows = rows;
                result.Data = resModel;
                result.Status = 200;
            }
            catch (MySqlException ex)
            {
                result.Message = "网络系统繁忙，请稍候再试!";
                Logs.WriteErrorLog(ex);
            }
            catch (CustomException ex)
            {
                result.Message = ex.Message;
            }
            catch (Exception ex)
            {
                result.Message = "网络系统繁忙，请稍候再试!";
                Logs.WriteErrorLog(ex);
            }
            return result;
        }


    }
}