using System;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.IO;
using System.Text;
using System.Windows.Forms;
using System.Drawing;
using System.Diagnostics;
using System.Collections.Generic;

namespace M423project
{
    public class DetectionData : IDisposable
    {
        private SqlConnection connection;
        private SqlConnection connForUpload;
        private string _standardCheckMode;

        int enableUploadMinutes = 10;
        private static readonly object synObj = new object();
        public DetectionData()
        {
            string connStr = ConfigurationManager.AppSettings["connectionString"];
            _standardCheckMode = ConfigurationManager.AppSettings["StandardCheckMode"];

            connection = new SqlConnection(connStr);
            try
            {
                connection.Open();
            }
            catch (Exception)
            {
                CommonUtil.WriteLog(LogType.Err, "连接数据库失败！");
            }

            connForUpload = new SqlConnection(connStr);
            try
            {
                connForUpload.Open();
            }
            catch (Exception)
            {
                CommonUtil.WriteLog(LogType.Err, "DataUpload连接数据库失败！");
            }

            string enableUploadMinutesStr = ConfigurationManager.AppSettings["EnableUploadMinutes"];

            int temp = 0;
            if (int.TryParse(enableUploadMinutesStr, out temp))
            {
                enableUploadMinutes = temp;
            }
            else
            {
                enableUploadMinutes = 10;
            }
        }

        public void Dispose()
        {
            connection.Close();
            connForUpload.Close();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public int SaveProductDetection(string _productNo, int productType, OPC opc, Label label)
        {
            lock (synObj)
            {
                int result = 0;
                string sql = string.Format("EXEC DBO.Proc_SaveProduct '{0}','{1}' ", _productNo, productType);

                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.Read())
                    {
                        result = (int)dr.GetValue(0);
                    }
                    dr.Close();
                }

                if (productType == (int)DetectionOption.doStandardBlock)
                {
                    label.BackColor = Color.Green;
                    label.Text = "";
                    opc.Write(OPCOutputTag.StandardCheck, true);
                }

                return result;
            }
        }

        public void SaveHeightData(int _detectID, double _height, double _cellHeight, MeasureState _productNoResult,
              MeasureState _heightResult, MeasureState _cellHeightResult, string productNo)
        {
            lock (synObj)
            {
                string sql = string.Format(
                  " UPDATE ProductDetectionDetail "
                + " SET   ProductHeight = {0}, "
                + "       ProductCellHeight = {1}, "
                + "       ProductHeightStatus = '{2}', "
                + "       ProductCellHeightStatus = '{3}', "
                + "       ProductNoStatus = '{4}', "
                + "       ProductNo='{5}',"
                + "       DetectTime = GetDATE() "
                + " WHERE AutoID = {6} ", _height, _cellHeight, _heightResult.ToString(),
                _cellHeightResult.ToString(), _productNoResult.ToString(), productNo, _detectID);

                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void SaveSizeData(int detectID, double _length, double _width, double _cellWidth,
              string productState, int plantID, string imageFileName, string lengthState, string widthState, string productNo, string productNoStatus)
        {
            lock (synObj)
            {
                string sql = string.Format("UPDATE ProductDetectionDetail "
                + " SET   ProductLength = {0},     "
                + "       ProductWidth = {1}, "
                + "       ProductCellWidth = {2}, "
                + "       PlateID = {3},  "
                + "       ImageFileName = '{4}', "
                + "       DetectTime = GetDATE(), "
                + "       ProductLengthStatus = '{5}', "
                + "       ProductWidthStatus  = '{6}', "
                + "       ProductStatus = '{7}',"
                + "       ProductNo='{8}',"
                + "       ProductNoStatus='{9}'"
                //+ "       CarrierBarcode='{8}'"
                + " WHERE AutoID = {10} ", _length, _width, _cellWidth, plantID, imageFileName, lengthState.ToString()
                , widthState.ToString(), productState.ToString(), productNo, productNoStatus, detectID);

                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        #region add by Patrick 2018-07-13
        public void SaveTrayBarcode(int detectID)
        {
            string trayCode = "";
            trayCode = CarrierBarcodeCtrl.GlobalCarrierBarcode;

            if (string.IsNullOrEmpty(trayCode))
            {
                return;
            }
            lock (synObj)
            {
                string sql = string.Format("UPDATE ProductDetectionDetail "
                            + " SET   CarrierBarcode='{0}'"
                            + " WHERE AutoID = {1} ", trayCode, detectID);

                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void SaveTrayBarcode(List<int> detectIDList, string carrierCode = null)
        {
            //string trayCode = CarrierBarcodeCtrl.GlobalCarrierBarcode;

            if (carrierCode == null)
            {
                carrierCode = CarrierBarcodeCtrl.GlobalCarrierBarcode;
            }

            if (detectIDList.Count == 0)
            {
                return;
            }
            lock (synObj)
            {
                string idList = string.Join(",", detectIDList);
                try
                {
                    string sql = $"Update ProductDetectionDetail set CarrierBarcode='{carrierCode}' where AutoID in ({idList})";

                    using (SqlCommand cmd = new SqlCommand(sql, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    Trace.TraceInformation($"{idList}产品绑定载具码：{carrierCode}");
                }
                catch (Exception ex)
                {
                    Trace.TraceError($"{idList}产品绑定载具码：{carrierCode}异常：{ex.Message}");
                    //throw;
                }
            }
        }
        #endregion

        #region add by Patrick 2019-04-16
        public void SaveSpotCheck(int detectID)
        {
            lock (synObj)
            {
                string sql = string.Format("UPDATE ProductDetectionDetail "
                            + " SET   IsSample={0}"
                            + " WHERE AutoID = {1} ", 1, detectID);

                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }
        #endregion

        public DataTable Query(string sql)
        {
            DataTable dt = new DataTable();
            using (SqlCommand cmd = new SqlCommand(sql, connection))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
            }
            return dt;
        }

        public DataTable QueryUnloadData()
        {
            DataTable dt = new DataTable();
            //string sql = string.Format(@"SELECT TOP 100 AutoID,PlateID, ProductNo,CarrierBarcode,DetectTime,ProductStatus,ProductLengthStatus
            //                            ,ProductWidthStatus,ProductHeightStatus,ProductNoStatus,LocalUploadFlag = cast(0 as int),ProductLength,ProductWidth,ProductHeight,IsSample 
            //                            FROM ProductDetectionDetail
            //                            WHERE ISNULL(UploadFlag, 0) = 0 AND ISNULL(ProductType,0)=0
            //                            AND ProductLengthStatus <> 'UNKNOWN'
            //                            AND ProductWidthStatus  <> 'UNKNOWN'
            //                            AND ((ProductNoStatus  = 'OK' AND ISNULL(CarrierBarcode,'')<>'') OR (ProductNoStatus  = 'OK' AND ProductStatus  = 'NG' AND ISNULL(CarrierBarcode,'')=''))
            //                            AND DATEDIFF(DAY,'2019-11-29',DetectTime)>=0
            //                            ORDER BY DetectTime DESC");

            string sql = string.Format(@"SELECT TOP 100 AutoID,PlateID, ProductNo,CarrierBarcode,DetectTime,ProductStatus,ProductLengthStatus
                                        ,ProductWidthStatus,ProductHeightStatus,ProductNoStatus,LocalUploadFlag = cast(0 as int),ProductLength,ProductWidth,ProductHeight,IsSample 
                                        FROM ProductDetectionDetail
                                        WHERE ISNULL(UploadFlag, 0) = 0 AND ISNULL(ProductType,0)=0
                                        AND ProductLengthStatus <> 'UNKNOWN'
                                        AND ProductWidthStatus  <> 'UNKNOWN'
                                        AND ProductNoStatus  = 'OK' 
                                        AND ProductStatus<>'NA'
                                        AND (ISNULL(CarrierBarcode,'')<>'' OR ProductStatus  = 'NG')
                                        AND DATEDIFF(MINUTE,DetectTime,'{0}')<={1}
                                        ORDER BY DetectTime DESC", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), enableUploadMinutes.ToString());

            SqlDataReader dr = null;
            using (SqlCommand cmd = new SqlCommand(sql, connForUpload))
            {
                try
                {
                    dr = cmd.ExecuteReader();
                    dt.Load(dr);
                }
                catch (Exception e)
                {
                    CommonUtil.WriteLog(LogType.Inf, string.Format("获取待上传数据时出现异常：{0}", e.Message));
                }
                finally
                {
                    if (dr != null)
                        dr.Close();
                }
            }
            return dt;
        }

        public int SetUploadDataStatus(string _autoIDList)
        {
            int result = 0;
            string sql = string.Format("EXEC Proc_UpdateUploadStatus '{0}' ", _autoIDList);

            using (SqlCommand cmd = new SqlCommand(sql, connForUpload))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {

                    result = (int)dr.GetValue(0);
                }
                dr.Close();
            }
            return result;
        }

        public void CleanData()
        {
            lock (synObj)
            {
                try
                {
                    string sql = string.Format("DELETE FROM  ProductDetectionDetail WHERE   DetectTime < GetDATE() - 40 ");

                    using (SqlCommand cmd = new SqlCommand(sql, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception)
                {

                }
            }
        }

        static object csvLock = new object();
        public void SaveToCsv(ProductMeasureResult pmr)
        {
            string fileName = @"E:\DailyReport\detectdata" + DateTime.Now.ToString("yyyyMMdd") + ".csv";

            CSVDataRecord(pmr, fileName);

        }

        public void CSVDataRecord(ProductMeasureResult pmr, string fileName)
        {
            bool IsNewFile = !File.Exists(fileName);
            lock (csvLock)
            {
                using (FileStream myStream = new FileStream(fileName, FileMode.OpenOrCreate))
                {
                    myStream.Seek(0, SeekOrigin.End);
                    using (StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.UTF8))
                    {
                        try
                        {
                            if (IsNewFile)
                            {
                                sw.WriteLine("檢測編號,檢測時間,工位,序列碼,判定,FAI9,判定,FAI2,判定,FAI3,判定,FAI12,判定,結果,类型");
                            }

                            string data = $"{pmr.DetectID}," +
                                $"{pmr.DetectTime.ToString("HH:mm:ss")}," +
                                $"{pmr.PlateID},{pmr.ProductNo}," +
                                $"{GetMeasureResult(pmr.ProductNoResult)}," +
                                $"{GetMeasureValueStr(pmr.Height)}," +
                                $"{GetMeasureResult(pmr.HeightResult)}," +
                                $"{GetMeasureValueStr(pmr.CellHeight)}," +
                                $"{GetMeasureResult(pmr.CellHeightResult)}," +
                                $"{GetMeasureValueStr(pmr.Length)}," +
                                $"{GetMeasureResult(pmr.LengthResult)}," +
                                $"{GetMeasureValueStr(pmr.Width)}," +
                                $"{GetMeasureResult(pmr.WidthResult)}," +
                                $"{GetMeasureResult(pmr.ProductResult)}," +
                                $"{(CommonUtil.DetectionOption == DetectionOption.doProduct ? "产品" : "标准块")}";

                            sw.WriteLine(data);
                            sw.Flush();
                            //sw.WriteLine(
                            //    string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14}",
                            //    pmr.DetectID, pmr.DetectTime.ToString("HH:mm:ss")
                            //    , pmr.PlateID, pmr.ProductNo, pmr.ProductNoResult.ToString()
                            //    , pmr.Height.ToString("0.00000")
                            //    , pmr.HeightResult.ToString()
                            //    , pmr.CellHeight.ToString("0.00000")
                            //    , pmr.CellHeightResult
                            //    , pmr.Length.ToString("0.00000")
                            //    , pmr.LengthResult.ToString()
                            //    , pmr.Width.ToString("0.00000")
                            //    , pmr.WidthResult.ToString(),
                            //      pmr.ProductResult.ToString(),
                            //      CommonUtil.DetectionOption == DetectionOption.doProduct ? "产品" : "标准块"
                            //    ));
                        }
                        catch (Exception e)
                        {
                            CommonUtil.WriteLog(LogType.Inf, string.Format("记录检测结果出现异常：{0}", e.Message));
                        }
                    }
                }
            }
        }

        private string GetMeasureValueStr(double measureValue)
        {
            return (measureValue == 999.999 || measureValue == -999.999 || measureValue == CommonUtil.InvalidValue) ? "NA" : measureValue.ToString("f5");
        }

        private string GetMeasureResult(string result)
        {
            return result == "OK" ? "OK" : "NG";
        }

        public bool IsStandardCheck()
        {
            try
            {
                int count = 0;
                if (_standardCheckMode == "0")
                {
                    return true;
                }
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat(@"select COUNT(AutoID) from ProductDetectionDetail
                            WHERE ProductType=1 AND DATEDIFF({0},GETDATE(),DetectTime)=0", _standardCheckMode == "1" ? "WEEK" : "MONTH");
                using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
                {
                    count = Convert.ToInt32(cmd.ExecuteScalar());
                }
                return count > 0;
            }
            catch (Exception ex)
            {
                return false;
            }

        }
    }
}