using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SystemX.Product.ALIS.Interface;
using SystemX.Product.TRA.UIControl;

using static SystemX.Product.TRA.DataManager.DMOverview;
using static SystemX.Product.CP.TRA.Commons;

namespace SystemX.Product.TRA.DataManager
{
    public class DMTestHistory
    {
        public enum eColList
        {
            No,
            TestDateTime,
            StationName,
            Host,
            Section,
            ProductID,
            ProductNo,
            TestType,
            TestCode,
            ParentNo,
            FileVersion,
            FileCode,
            StepVersion,
            Duration,
            TestResult,
            //OK,
            //NOK,
            TestListFileNo,
            TestListVariantNo,
            //TestID,
            LogNo,
            LogCount,
            AccessStart,
            AccessEnd,
            TestlistFileName,
            TestlistReqID
        }

        private SqlConnection DBConn { get; set; }
        private SqlConnection ShortTermDBConn { get; set; }
        private SqlConnection LongTermDBConn { get; set; }

        private SqlCommand SQLCmd { get; set; }


        public DMTestHistory(eSelectDataView SelectView, IDataBaseController getDBController)
        {
            DBConn = getDBController.GetMainConn();

            if (SelectView == eSelectDataView.DataDocumentViewC1)
            {
                ShortTermDBConn = getDBController.GetShortTermConn1();

                LongTermDBConn = getDBController.GetLongTermConn1();
            }
            else if (SelectView == eSelectDataView.DataDocumentViewC2)
            {
                ShortTermDBConn = getDBController.GetShortTermConn2();

                LongTermDBConn = getDBController.GetLongTermConn2();
            }
        }

        public DataTable SearchTestHistory(SelectedDataCollection data, TestHistorySearchOption option, int nReqIdPos = int.MaxValue)
        {
            DataTable dtResult = GetRawResult(data, option, nReqIdPos);
            
            return dtResult;
        }

        DataTable GetRawResult(SelectedDataCollection data, TestHistorySearchOption option, int nReqIdPos = int.MaxValue)
        {
            DataTable dtResult = new DataTable();
            string strQuery = string.Empty;

            DateTime dtCheckTime = DateTime.Now;

            //데이터 검색 시작 시간 확인
            if (option.Time_WholeRange)
                dtCheckTime = data.SearchRangeStart;
            else if (option.Time_SelectedTest)
                dtCheckTime = data.StartTime;
            else if (option.Time_Day)
                dtCheckTime = data.StartTime;

            DateTime dtNow = DateTime.Now;

            int nDiffMonth =  12 * (dtNow.Year - dtCheckTime.Year) + (dtNow.Month - dtCheckTime.Month);

            //검색 시작시간이 3개월 전이면 LongTerm Summary 검색
            bool bLongTermTableUse = nDiffMonth > 3 ? true : false;

            if (data == null)
                return dtResult;

            strQuery += $"SELECT ";

            strQuery += $"{DMCommon.SummaryLogTable}.No,";
            strQuery += $"CONVERT(char(20), {DMCommon.SummaryLogTable}.TestDT, 20) as {eColList.TestDateTime.ToString()},";
            strQuery += $"{DMCommon.SummaryLogTable}.StationName as {eColList.StationName.ToString()},";
            strQuery += $"{DMCommon.SummaryLogTable}.HostID as {eColList.Host.ToString()},";
            strQuery += $"{DMCommon.SummaryLogTable}.Section as {eColList.Section.ToString()},";
            strQuery += $"{DMCommon.SummaryLogTable}.ProductID as {eColList.ProductID.ToString()},";
            strQuery += $"{DMCommon.SummaryLogTable}.TestType as {eColList.TestType.ToString()},";
            strQuery += $"{DMCommon.SummaryLogTable}.ProdNo_C as {eColList.ProductNo.ToString()},";
            strQuery += $"{DMCommon.SummaryLogTable}.Testcode as {eColList.TestCode.ToString()},";
            strQuery += $"{DMCommon.SummaryLogTable}.ProdNo_P as {eColList.ParentNo.ToString()},";
            strQuery += $"{DMCommon.SummaryLogTable}.ProdCode as {eColList.FileCode.ToString()},";
            strQuery += $"{DMCommon.SummaryLogTable}.Version as {eColList.FileVersion.ToString()},";
            strQuery += $"{DMCommon.SummaryLogTable}.StepVersion as {eColList.StepVersion.ToString()},";
            strQuery += $"{DMCommon.SummaryLogTable}.Duration as {eColList.Duration.ToString()},";
            strQuery += $"{DMCommon.SummaryLogTable}.Result as {eColList.TestResult.ToString()},";
            //strQuery += $"CASE WHEN {DMCommon.SummaryLogTable}.Result = 'OK' THEN 1 ELSE 0 END as {eColList.OK.ToString()},";
            //strQuery += $"CASE WHEN {DMCommon.SummaryLogTable}.Result = '-' THEN 0 WHEN {DMCommon.SummaryLogTable}.Result != 'OK' THEN 1  ELSE 0 END as {eColList.NG.ToString()},";

            strQuery += $"{DMCommon.SummaryLogTable}.[TestListFileNo],";
            strQuery += $"{DMCommon.SummaryLogTable}.[TestListVariantNo],";
            strQuery += $"{DMCommon.SummaryLogTable}.[LogNo],";
            strQuery += $"{DMCommon.SummaryLogTable}.[LogCount],";
            //strQuery += $"{DMCommon.SummaryLogTable}.[TestID],";
            strQuery += $"{DMCommon.SummaryLogTable}.[LogNo],";
            strQuery += $"{DMCommon.SummaryLogTable}.[LogCount],";
            strQuery += $"{DMCommon.SummaryLogTable}.[AccessStart],";
            strQuery += $"{DMCommon.SummaryLogTable}.[AccessEnd],";
            strQuery += $"{DMCommon.SummaryLogTable}.[TestListFileName],";
            strQuery += $"{DMCommon.SummaryLogTable}.[TestListCntID] as {eColList.TestlistReqID.ToString()}";

            strQuery += " ";
            strQuery += $"FROM {DMCommon.SummaryLogTable} ";
            strQuery += $"WITH(NOLOCK) ";

            if (option.Time_WholeRange)
                strQuery += $"where TestDT >= '{data.SearchRangeStart.ToString("yyyy-MM-dd 00:00:00")}' and TestDT <= '{data.SearchRangeEnd.ToString("yyyy-MM-dd 23:59:59")}' ";
            else if(option.Time_SelectedTest)
                strQuery += $"where TestDT >= '{data.StartTime.ToString("yyyy-MM-dd HH:mm:ss")}' and TestDT <= '{data.EndTime.ToString("yyyy-MM-dd HH:mm:ss")}' ";
            else if(option.Time_Day)
                strQuery += $"where TestDT >= '{data.StartTime.ToShortDateString()} 00:00:00' and TestDT <= '{data.EndTime.ToShortDateString()} 23:59:59' ";

            if (option.Station && !string.IsNullOrWhiteSpace(data.StationName))
                strQuery += $" and (StationName = '{data.StationName}'  or StationName = '-') ";
            if (option.Host && !string.IsNullOrWhiteSpace(data.HostID))
                strQuery += $" and (HostID = '{data.HostID}' or HostID = '-') ";
            if (option.Section && !string.IsNullOrWhiteSpace(data.SectionID))
                strQuery += $" and (Section = '{data.SectionID}'  or Section = '-') ";
            if (option.TestType)
                strQuery += $" and (TestType = '{data.TestType}'  or TestType = '-') ";
            if (option.ProductNo)
            {
                strQuery += $" and (ProdNo_C = '{data.ProductNo}' ";

                if(option.Host && !string.IsNullOrWhiteSpace(data.HostID))
                {
                    foreach(string strCmd in Enum.GetNames(typeof(eCommand)))
                        strQuery += $"or ProdNo_C = '{strCmd}'";
                }
                strQuery += $" )";
            }
            if (option.TestCode)
                strQuery += $" and (Testcode = '{data.TestCode}'  or Testcode = '-') ";
            if (option.ParentNo)
                strQuery += $" and (ProdNo_P = '{data.ParentNo}'  or ProdNo_P = '-') ";
            if (option.ProductionCode)
                strQuery += $" and (ProdCode = '{data.ProductionCode}'  or ProdCode = '-') ";
            if (option.FileVersion)
                strQuery += $" and (Version = '{data.FileVersion}'  or Version = '-') ";
            if (option.StepVersion)
                strQuery += $" and (StepVersion = '{data.StepVersion}'  or StepVersion = '-1') ";
            if (option.Time_SelectedTest)
            {
                if(nReqIdPos == int.MaxValue)
                    strQuery += $" and (TestListCntID = '{data.TestRequestID[0]}'  or TestListCntID = '-') ";
                else
                    strQuery += $" and (TestListCntID = '{data.TestRequestID[nReqIdPos]}'  or TestListCntID = '-') ";
            }

            //if (option.ShowRedundantData)
            //     strQuery += $" group by {DMCommon.SummaryLogTable}.ProductID ";

            strQuery += " ";          
            strQuery += $"ORDER BY ";
            strQuery += $"{DMCommon.SummaryLogTable}.TestDT";
            strQuery += ";";

            if (bLongTermTableUse) SQLCmd = new SqlCommand(strQuery, LongTermDBConn);
            else SQLCmd = new SqlCommand(strQuery, ShortTermDBConn);
            SQLCmd.CommandTimeout = DMCommon.nDefaultScanTime;

            DbDataReader dtReader = SQLCmd.ExecuteReader();

            dtResult.Load(dtReader);

            dtReader.Close();

            return dtResult;
        }
    }
}