using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;

using System.Globalization;
using WpfLocalization;
using System.Threading;
using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Collections;
using System.IO.Ports;
using System.Collections.ObjectModel;
using System.Reflection;
using System.Configuration;
using System.Collections.Specialized;
using System.Data.SqlServerCe;
using System.ComponentModel;
using System.Windows.Threading;

using System.Data.OleDb;
namespace WpfApplication19
{
    /// <summary>
    /// Interaction logic for RLWindow.xaml
    /// </summary>
    public partial class RLWindow : Window
    {
        private bool isFullScreen = false;
        private Debug dw;
        public SqlConnection configConn, masterConn, transConn, proConn;
        private SqlCeConnection _opConn;
        //private SqlDataReader reader;
        string[] obTables = new string[] {"BEL-MAS-1", "BEL-MAS-3", "E-MAS-1", "E-MAS-3", 
                                             "MG-MAS-1", "MG-MAS-3", "V-MAS-1", "V-MAS-3"};

        public SerialPort comport = new SerialPort();
        ObservableCollection<MeasurePoint> mps;
        int nextCol = 3;

        bool isMiddle = false, _haveMiddle = false;
        bool _isIn = false;
        public string inStarGS = "";
        public string midGS = "";
        string sMi16, sMi20, kind;

        enum RecordTypes { TopUnder, InOut }
        string[] masViews = new string[] { "bel_master_view", "e_master_view", "mg_master_view", "v_master_view" };

        List<string> list;
        string header;
        Stack<string> _lastAction = new Stack<string>();
        bool _isTkx = false;
        DateTime _lastTimeDataPort;
        bool _isCheck = false;
        DailyCheck _dc;
        RLWindow _rlWindow;
        Encoding encAscii = Encoding.ASCII;

        Dictionary<string, string> rolesDescription = new Dictionary<string, string>
        {
               {"qa_staff", "QA Staff"},{"user","User"}, {"admin", "Administrator"}, {"qa_man", "QA Ass't Manager"}
        };

        Dictionary<string, string> _sideMap = new Dictionary<string, string>
        {
               {"out", "in"},{"in","under"}, {"rim", "base"}, {"top", "under"}, {"outer", "inner"}
        };
        private double out0, out1;
        private double in0, in1;

        MeasurePoint currentMP;
        string currentColumn;
        Debug _debugWindow;
        Thread _aThread, _bThread;
        bool _clearThread = false, _canSent = true;
        int _pattern = 1;
        string _updated_at;
        private int _obLimit, _obCount, _ocLimit, _ocCount, _rhLimit, _rhCount;
        bool _isOver = false;
        bool _isFirstMeasure = true;
        DateTime _startUsing = new DateTime();
        int _usingTime;
        bool _isExisting = false;
        private List<string> _agreementList = new List<string>();
        string _currentValue, _prevValue = null;
        double _lower1, _upper1, _lower2, _upper2;
        string strAccessConnEMASTER = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\\\192.168.0.253\\mgt\\master\\E-Master.mdb;Mode=Share Deny None";
        string strAccessConnBELMASTER = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\\\192.168.0.253\\mgt\\master\\BEL-Master.mdb;Mode=Share Deny None";
        string strAccessConnMGMASTER = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\\\192.168.0.253\\mgt\\master\\MG-Master.mdb;Mode=Share Deny None";
        string strAccessConnVMASTER = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\\\192.168.0.253\\mgt\\master\\V-Master.mdb;Mode=Share Deny None";

        OleDbConnection accessConnEMASTER = null;
        OleDbConnection accessConnBELMASTER = null;
        OleDbConnection accessConnMGMASTER = null;
        OleDbConnection accessConnVMASTER = null;
        OleDbConnection tempConn = null;

        RLAlert rlAlert;
        public RLWindow()
        {
            InitializeComponent();
            lotNo.Focus();
            initDatabaseConnection();
        }

        public void closeFromAlert()
        {
            this.rlAlert.Close();
            this.Close();
        }
        private void initDatabaseConnection()
        {


            try
            {
                _opConn = Utils.createSqlCeConnection("operatingDbCS");
                // Sample usage
                /*
                SqlCeDataReader reader;
                reader = Utils.Query(_opConn, "select * from UsingTime");
                while(reader.Read()){
                    Console.WriteLine("hello");
                }
                reader.Close();
                 */
            }
            catch (SqlCeException se)
            {
                MessageBox.Show("operatingDbCS : ", se.Message);
            }
            /*
            try
            {
            configConn = Utils.createSqlConnection("configDbCS");
            }
            catch (SqlException se)
            {
            MessageBox.Show("configDbCS : ", se.Message);
            }

            try
            {
            masterConn = Utils.createSqlConnection("masterDbCS");
            }
            catch (SqlException se)
            {
            MessageBox.Show("masterDbCS : ", se.Message);
            }

            try
            {
            transConn = Utils.createSqlConnection("tranDbCS");
            }
            catch (SqlException se)
            {
            MessageBox.Show("tranDbCS : ", se.Message);
            }
            */
            try
            {
                proConn = Utils.createSqlConnection("productionDbCS");
                transConn = Utils.createSqlConnection("productionDbCS");
                masterConn = Utils.createSqlConnection("productionDbCS");
                configConn = Utils.createSqlConnection("productionDbCS");
            }
            catch (SqlException se)
            {
                MessageBox.Show("productionDbCS : ", se.Message);
            }

            //mod: change config by move every table alias
            //transConn  = masterConn = configConn = proConn;

            /*
            try
            {
                accessConnEMASTER = new OleDbConnection(strAccessConnEMASTER);
                accessConnEMASTER.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: Failed to create e-master connection. \n{0}", ex.Message);

            }

            try
            {
                accessConnBELMASTER = new OleDbConnection(strAccessConnBELMASTER);
                accessConnBELMASTER.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: Failed to create e-master connection. \n{0}", ex.Message);

            }
            try
            {
                accessConnMGMASTER = new OleDbConnection(strAccessConnMGMASTER);
                accessConnMGMASTER.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: Failed to create e-master connection. \n{0}", ex.Message);

            }
            try
            {
                accessConnVMASTER = new OleDbConnection(strAccessConnVMASTER);
                accessConnVMASTER.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: Failed to create e-master connection. \n{0}", ex.Message);

            }*/

        }
        private void addRow_Click(object sender, RoutedEventArgs e)
        {
            if (measureGrid.ItemsSource == null)
                return;
            _lastAction.Push("row");
            MeasurePoint m = mps.Last();

            int count = 1;
            foreach (var l in list)
            {
                mps.Add(new MeasurePoint() { Count = m.Count + 1, Type = l, SubOrder = count });
                count++;
            }

        }

        private void addMeasureColumn_Click(object sender, RoutedEventArgs e)
        {
            _lastAction.Push("column");
            if (nextCol <= 10)
            {
                measureGrid.Columns.ElementAt(++nextCol).Visibility = Visibility.Visible;

                foreach (var item in measureGrid.Items)
                {
                    //Console.WriteLine(item);
                    MeasurePoint c = item as MeasurePoint;

                    c.Avg = (c.P1 + c.P2 + c.P3 + c.P4 + c.P6 + c.P7 + c.P8 + c.P9 + c.P10) / nextCol;
                    c.Avg = Math.Round(c.Avg, 2);

                    if (c.SubOrder == 1)
                    {
                        c.Grade = computeGrade(sMi16, c.Avg, kind, "-", c.Type);
                    }
                    else if (c.SubOrder != 1 && c.Type != "MID" && c.Type != "IN*")
                    {
                        if (sMi20 != "0")
                            c.Grade = computeGrade(sMi20, c.Avg, kind, "-", c.Type);
                        else
                            c.Grade = computeGrade(sMi16, c.Avg, kind, "-", c.Type);
                    }
                }

                measureGrid.Items.Refresh();
            }

        }

        private void removeMeasureColumn_Click(object sender, RoutedEventArgs e)
        {
            _lastAction.Push("remove_column");

            measureGrid.Columns.ElementAt(nextCol).Visibility = Visibility.Hidden;
            if (nextCol <= 10)
            {
                measureGrid.Columns.ElementAt(--nextCol).Visibility = Visibility.Visible;

                foreach (var item in measureGrid.Items)
                {
                    //Console.WriteLine(item);
                    MeasurePoint c = item as MeasurePoint;
                    c.setValue((nextCol + 1).ToString(), 0);
                    computeValues(c, true);
                }

                measureGrid.Items.Refresh();
            }
        }

        private void addMiddleBtn_Click(object sender, RoutedEventArgs e)
        {
            if (measureGrid.ItemsSource == null)
                return;
            _lastAction.Push("middle");

            this.isMiddle = !this.isMiddle;

            if (this.isMiddle == false)
            {
                middleText.Text = "Add Middle";

                RemoveMid();
            }
            else
            {
                middleText.Text = "Remove Middle";
                EditMIDWindow w = new EditMIDWindow();
                w.ShowDialog();
                AddMid();

            }
            //this.measureGrid.CommitEdit();
            measureGrid.Items.Refresh();
        }
        private void RemoveMid()
        {
            list.Remove("MID");
            ObservableCollection<MeasurePoint> temp = new ObservableCollection<MeasurePoint>();
            foreach (var m in mps)
            {

                if (m.Type != "MID")
                {
                    temp.Add(m);
                }
            }
            mps = temp;
            measureGrid.ItemsSource = null;
            measureGrid.ItemsSource = mps;
        }

        private void AddMid()
        {


            list.Insert(1, "MID");
            ObservableCollection<MeasurePoint> temp = new ObservableCollection<MeasurePoint>();
            foreach (var m in mps)
            {
                temp.Add(m);
                if (m.SubOrder == 1)
                {
                    temp.Add(new MeasurePoint() { Type = "MID", SubOrder = m.SubOrder + 1, Count = m.Count });
                }
            }
            mps = temp;
            measureGrid.ItemsSource = null;
            measureGrid.ItemsSource = mps;
        }
        private void AddIn()
        {
            list.Insert(1, "IN*");
            ObservableCollection<MeasurePoint> temp = new ObservableCollection<MeasurePoint>();
            foreach (var m in mps)
            {
                temp.Add(m);
                if (m.SubOrder == 1)
                {
                    temp.Add(new MeasurePoint() { Type = "IN*", SubOrder = m.SubOrder + 1, Count = m.Count });
                }
            }
            mps = temp;
            measureGrid.ItemsSource = null;
            measureGrid.ItemsSource = mps;
        }
        public void setFilterLogData(string q)
        {
            //MessageBox.Show("RL Called");
            _isExisting = true;
            this._updated_at = q;

        }
        private void addInBtn_Click(object sender, RoutedEventArgs e)
        {
            //measureGrid.ItemsSource  = null;
            if (measureGrid.ItemsSource == null)
                return;
            _isIn = !_isIn;

            if (_isIn == false)
            {
                inText.Text = "Add IN*";
                RemoveIn();
            }
            else
            {
                inText.Text = "Remove IN*";
                EditINWindow w = new EditINWindow();
                w.ShowDialog();
                AddIn();
            }
            //ObservableCollection<MeasurePoint> temp = new ObservableCollection<MeasurePoint>();
            _lastAction.Push("in");
            measureGrid.Items.Refresh();
            /*
            for(int i = 0; i < mps.Count; i++){	
                var mp = mps[i];
                if(mp.SubOrder == 1){
                    mps.Insert(mps.IndexOf(mp) + 1, new MeasurePoint() { Count = mp.Count, Type = "IN*", SubOrder = 3});
                }
            }
            measureGrid.ItemsSource = mps;
            */

            //measureGrid.ItemsSource = null;
            //measureGrid.Items.Add(0, new MeasurePoint() { Count = 1, Type = "In"});
        }
        private void RemoveIn()
        {
            list.Remove("IN*");
            ObservableCollection<MeasurePoint> temp = new ObservableCollection<MeasurePoint>();
            foreach (var m in mps)
            {

                if (m.Type != "IN*")
                {
                    temp.Add(m);
                }
            }
            mps = temp;
            measureGrid.ItemsSource = null;
            measureGrid.ItemsSource = mps;

        }
        private void deleteData_Click(object sender, RoutedEventArgs e)
        {
            this.currentMP.setValue(currentColumn, 0);
            this.computeValues(currentMP, true);
            measureGrid.Items.Refresh();
        }
        private void computeValues(MeasurePoint c, bool isCompGrade = false)
        {
            c.Avg = (c.P1 + c.P2 + c.P3 + c.P4 + c.P5 + c.P6 + c.P7 + c.P8 + c.P9 + c.P10) / nextCol;
            c.Avg = Math.Round(c.Avg, 2);

            if (isCompGrade == true)
            {
                if (c.SubOrder == 1)
                {
                    c.Grade = computeGrade(sMi16, c.Avg, kind, "-", c.Type);
                }
                else if (c.SubOrder != 1 && c.Type != "MID" && c.Type != "IN*")
                {
                    if (sMi20 != "0")
                        c.Grade = computeGrade(sMi20, c.Avg, kind, "-", c.Type);
                    else
                        c.Grade = computeGrade(sMi16, c.Avg, kind, "-", c.Type);
                }
                else if (c.Type == "MID" || c.Type == "IN*")
                {
                    c.Grade = computeGrade("0", c.Avg, kind, "-", c.Type);
                }
            }
            if (c.SubOrder == 1 || c.Type == "IN*" || c.Type == "MID" || c.Type == "FA1" || c.Type == "FA2")
            {
                if (this.header == "OB" || this.header == "OC")
                {
                    if (c.Avg >= this.out0 && c.Avg <= this.out1)
                    {
                        c.ResultGrade = "OK";
                    }
                    else if (c.Avg < this.out0)
                    {
                        c.ResultGrade = "Hard";
                    }
                    else if (c.Avg > this.out1)
                    {
                        c.ResultGrade = "Soft";
                    }
                }
                else
                {
                    if (c.Avg >= this.out0 && c.Avg <= this.out1)
                    {
                        c.ResultGrade = "OK";
                    }
                    else if (c.Avg < this.out0)
                    {
                        c.ResultGrade = "Soft";
                    }
                    else if (c.Avg > this.out1)
                    {
                        c.ResultGrade = "Hard";
                    }
                }

            }
            else if (c.SubOrder != 1)
            {
                if (this.header == "OB" || this.header == "OC")
                {
                    if (c.Avg >= this.in0 && c.Avg <= this.in1)
                    {
                        c.ResultGrade = "OK";
                    }
                    else if (c.Avg < this.in0)
                    {
                        c.ResultGrade = "Hard";
                    }
                    else if (c.Avg > this.in1)
                    {
                        c.ResultGrade = "Soft";
                    }
                }
                else
                {
                    if (c.Avg >= this.in0 && c.Avg <= this.in1)
                    {
                        c.ResultGrade = "OK";
                    }
                    else if (c.Avg < this.in0)
                    {
                        c.ResultGrade = "Soft";
                    }
                    else if (c.Avg > this.in1)
                    {
                        c.ResultGrade = "Hard";
                    }
                }
            }
        }
        private string computeGrade(string size, double lower, string kind, string defValue, string type)
        {
            double rlMin = double.Parse(RLMin.Text);
            double rlMax = double.Parse(RLMax.Text);

            if ((rlMin <= lower) && (rlMax >= lower))
            {
                return "OK";
            }
            else
            {
                return "Fail";
            }


            //return value;
        }
        //int row;
        private void saveCellBtn_Click(object sender, RoutedEventArgs e)
        {
            measureGrid.Focus();
            if (currentColumn == null)
                return;

            if (_isOver == true)
                return;
            /*
            if(_isFirstMeasure == true){
                _startUsing = DateTime.Now;
                _isFirstMeasure = false;
            }*/
            if (currentColumn != "Average" && currentColumn != "Grade" && currentColumn != "Result Grade")
            {
                this.currentMP.setValue(currentColumn, Convert.ToDouble(readingBox.Text));
                //var c = measureGrid.SelectedValue;

                this.computeValues(currentMP, true);

            }
            try
            {
                var info = measureGrid.CurrentColumn;
                var item = measureGrid.CurrentItem as MeasurePoint;
                int col = info.DisplayIndex;
                var mp = (MeasurePoint)measureGrid.CurrentItem;
                int row = mps.IndexOf(mp);

                //measureGrid.RaiseEvent(new RoutedEventArgs(DataGrid.rowediten));
                //MessageBox.Show("row " + item.Count.ToString());

                //MessageBox.Show("row " +  mps.IndexOf(mp).ToString());
                //MessageBox.Show("col " + info.DisplayIndex.ToString());


                measureGrid.Items.Refresh();
                measureGrid.Focus();
                if (col < nextCol)
                {
                    measureGrid.CurrentCell = new DataGridCellInfo(
                        measureGrid.Items[row], measureGrid.Columns[col + 1]);
                    measureGrid.BeginEdit();
                }
                else
                {
                    try
                    {
                        //this.computeValues(currentMP, true);
                        measureGrid.CurrentCell = new DataGridCellInfo(
                    measureGrid.Items[row + 1], measureGrid.Columns[1]);
                        measureGrid.SelectedItem = measureGrid.Items[row + 1];
                        measureGrid.BeginEdit();


                    }
                    catch
                    {
                        measureGrid.CurrentCell = new DataGridCellInfo(
                        measureGrid.Items[0], measureGrid.Columns[1]);
                        measureGrid.SelectedItem = measureGrid.Items[0];
                        measureGrid.BeginEdit();
                    }


                }
            }
            catch
            {
                ;
            }
        }
        private void PrintLabel(string lotNo, DateTime dt)
        {
            string sql = string.Format("select * from data_rl where lot_no = '{0}' and created_at = '{1}' order by id", lotNo, dt);
            SqlDataReader reader = Utils.Query(proConn, sql);
            string s = "";
            int n = 0;

            List<double> outs = new List<double>();
            List<double> ins = new List<double>();
            bool outValid = false;
            bool inValid = false;
            string inAvg = "";
            string outAvg = "";

            int row = 0;
            int nrow = 0;
            while (reader.Read())
            {
                nrow++;
                n = Convert.ToInt32(reader["ndata"]);

                string r_type = reader["r_type"].ToString();

                try
                {
                    r_type = r_type.Substring(0, 3);
                }
                catch
                {
                    ;
                }
                s += string.Format("{0,3}", r_type.ToUpper()) + " ";

                for (int i = 1; i <= n; i++)
                {
                    if (n > 3)
                    {
                        s += string.Format("{0,-3:0.00}", Convert.ToDouble(reader["p" + i])) + " ";
                    }
                    else
                    {
                        s += string.Format("{0,-3:0.00}", Convert.ToDouble(reader["p" + i])) + " ";
                    }


                    row++;
                }
                //s = s.Trim();
                var grade = reader["grade"].ToString().Trim();
                var rgrade = reader["rgrade"].ToString().Trim();

                try
                {
                    if (n >= 5)
                        rgrade = rgrade.Substring(0, 2);
                }
                catch
                {
                    ;
                }
                //if (reader["rgrade"].ToString().ToLower() != "ok")
                //{
                //	s += string.Format(" {0,-6:*0.00*}", Convert.ToDouble(reader["avg"])) + " " + string.Format("{0,-2}",grade) + " " + rgrade;

                //}
                //else
                //{
                if (n >= 5)
                {
                    s += string.Format("{0,5:0.00}", Convert.ToDouble(reader["avg"])) + " " + string.Format("{0,-2}", grade);

                }
                else
                {
                    s += string.Format("{0,7:0.00}", Convert.ToDouble(reader["avg"])) + " " + string.Format("{0,-2}", grade);

                }


                //}

                //s = s.Trim();
                s += "\n";

                /*
                UPP,UPPE = Out
                LOW,LOWE = In
                Rim = Out
                Base =In
                Soko = In
                IN* = In
                 */
                if (r_type.ToUpper() == "OUT" || r_type.ToUpper() == "TOP" || r_type.ToUpper() == "FA1" || r_type.ToUpper() == "UPP" || r_type.ToUpper() == "UPPE" || r_type.ToUpper() == "RIM")
                {

                    outs.Add(Convert.ToDouble(reader["avg"]));

                }
                if (r_type.ToUpper() == "IN" || r_type.ToUpper() == "UNDER" || r_type.ToUpper() == "UND" || r_type.ToUpper() == "FA2" || r_type.ToUpper() == "LOW" || r_type.ToUpper() == "LOWE" || r_type.ToUpper() == "BASE" || r_type.ToUpper() == "SOKO" || r_type.ToUpper() == "IN*")
                {

                    ins.Add(Convert.ToDouble(reader["avg"]));

                }

            }

            //string avg = string.Format("{0.00}", Convert.ToDouble(reader["avg"]));


            double[] inArray = new double[] { 
                                              0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                                              0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                                              0, 0, 0, 0, 0, 0, 0, 0, 0, 0};
            double[] outArray = new double[] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                                               0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                                               0, 0, 0, 0, 0, 0, 0, 0, 0, 0};

            double sumIns = 0;
            double sumOuts = 0;

            for (int i = 0; i < ins.Count; i++)
            {
                inArray[i] = ins[i];
                sumIns += ins[i];
            }

            for (int i = 0; i < outs.Count; i++)
            {
                outArray[i] = outs[i];
                sumOuts += outs[i];
            }
            double avgIns = 0;
            if (ins.Count == 0)
                avgIns = 0;
            else
                avgIns = sumIns / ins.Count;

            double avgOut = 0;
            if (outs.Count == 0)
                avgOut = 0;
            else
                avgOut = sumOuts / outs.Count;

            reader.Close();

            sql = string.Format("select m.name , ls.avg, ls.grade, ls.start_time, ls.end_time, ls.code  from lot_summary_rl ls, machines m where ls.lot_no = '{0}' and ls.created_at = '{1}' and ls.machine_id = m.id", lotNo, dt);
            reader = Utils.Query(proConn, sql);

            var temp = list;


            var types = string.Join("/", temp.ToArray());

            string code = "";
            string et = "";

            var w = (MainWindow)Application.Current.MainWindow;
            while (reader.Read())
            {
                code = reader["code"].ToString();
                s += string.Format("{0}(RL) {1} Times {2} AVG {3:0.00} \n", reader["name"].ToString(), w.UsingTimeTB.Text, types, Convert.ToDouble(reader["avg"]));
                var startTime = Convert.ToDateTime(reader["start_time"]);
                var endTime = Convert.ToDateTime(reader["end_time"]);
                et = string.Format("{0:dd/MM/yyyy HH:mm:ss}", endTime);
                s += string.Format("{0:dd/MM/yyyy} {1:HH:mm:ss}-{2:HH:mm:ss}\n", startTime, startTime, endTime);
            }
            code = code.Replace("-", "");
            string tableName = "";

            
            reader.Close();
            //Utils.Query(proConn, "select P1, P2, P3, P4, P5, P6, P7, P8, P9, P10 from lot_summary where lot_no = 
            Printer.Print(s, n);
            //Printer.Print(s);
            //var p = new Printer();

        }
        private void printBtn_Click(object sender, RoutedEventArgs e)
        {
            if (_isExisting == true)
            {
                PrintLabel(lotNo.Text, Convert.ToDateTime(_updated_at));
            }
            else
            {
                MessageBox.Show("You can print when you have commited data");
            }
        }

        private void commitBtn_Click(object sender, RoutedEventArgs e)
        {
            if (measureGrid.ItemsSource == null)
                return;
            var now = DateTime.Now;
            var w = (MainWindow)Application.Current.MainWindow;

            var id = Utils.getSetting("currentMachine");
            double sum = 0;
            int count = 0;
            List<List<float>> s = new List<List<float>>();
            _isExisting = true;

            Dictionary<string, List<double>> sumType = new Dictionary<string, List<double>>();

            foreach (var item in measureGrid.Items)
            {
                //Console.WriteLine(item);

                MeasurePoint mp = item as MeasurePoint;
                if (mp.Avg == null)
                {
                    mp.Avg = 0;
                }
                if (mp.Avg == 0)
                    continue;
                if (mp.Grade == null)
                {
                    mp.Grade = "-";
                }
                if (mp.ResultGrade == null)
                {
                    mp.ResultGrade = "-";
                }
                /*
                if (mp.Type == "MID" && this.isMiddle == false)
                    continue;
                if (mp.Type == "IN*" && _isIn == false)
                    continue;
                */
                if (sumType.ContainsKey(mp.Type.Trim()) == false)
                {
                    sumType[mp.Type.Trim()] = new List<double>();
                }
                (sumType[mp.Type.Trim()] as List<double>).Add(mp.Avg);

             
                string sql = string.Format(" insert into data_rl(lot_no, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, row_no, avg, grade, rgrade, r_type, created_at, updated_at, ndata, sub_order, machine_id, code, header) " +
                " values ('{0}', {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, '{13}', '{14}', '{15}', '{16}', '{17}', {18}, {19}, {20}, '{21}', '{22}' )"
                , lotNo.Text.Trim(), mp.P1, mp.P2, mp.P3, mp.P4, mp.P5, mp.P6, mp.P7, mp.P8, mp.P9, mp.P10, mp.Count, mp.Avg, mp.Grade.Trim(), mp.ResultGrade.Trim(), mp.Type.Trim(), now, now, nextCol, mp.SubOrder, id, w.code.Text.Trim(), this.header);
                sum += mp.Avg;
                //var s0 = lotNo.Text.Trim();
                /*
                var tempList = new List<float>();
                for(int i = 1; i < nextCol; i++){
                    tempList.Add(mp.GetValue("P"+i));
					
                }
                s.Add(tempList);
                */
                count++;
                using (SqlCommand command = new SqlCommand(sql, proConn))
                {
                    command.ExecuteNonQuery();
                }

            }
            //Math.Round(
            int i = 0;
            string[] keys = new string[sumType.Keys.Count];

            sumType.Keys.CopyTo(keys, 0);

            for (int k = 0; k < keys.Length; k++)
            {
                keys[k] = "'" + keys[k] + "'";
            }
            //List<string> avgValues = new List<string>();
            string[] values = new string[sumType.Keys.Count];
            int j = 0;
            string[] columns = new string[sumType.Keys.Count];
            string[] columnValues = new string[sumType.Keys.Count];
            foreach (var name in sumType)
            {
                var temp = name.Value as List<double>;
                double st = 0.0;
                foreach (double n in temp)
                {
                    st += n;
                }
                values[j] = Math.Round((double)(st / temp.Count), 2).ToString();
                columns[j] = "avg" + j + "_name";
                columnValues[j] = "avg" + j;
                j++;
            }
            double avg = Math.Round(sum / count, 2);

            string grade = "-";
            if (Convert.ToInt32(sMi16) <= Convert.ToInt32(sMi20))
            {
                grade = computeGrade(sMi16, avg, kind, "-", "OUT");
            }
            else
            {
                if (Convert.ToInt32(sMi20) == 0)
                    grade = computeGrade(sMi16, avg, kind, "-", "OUT");
                else
                    grade = computeGrade(sMi20, avg, kind, "-", "OUT");
            }

            try
            {
                string sql2 = string.Format("insert into lot_summary_rl(lot_no, created_at, avg, code,  start_time, end_time, grade, machine_id, " + String.Join(",", columns) + "," + String.Join(",", columnValues) + ") values('{0}', '{1}', {2}, '{3}', '{4}', '{5}', '{6}', {7}, {8}, {9})",
                            lotNo.Text.Trim(), now, avg, w.code.Text.Trim(), _startUsing, now, grade.Trim(), id, String.Join(",", keys), String.Join(",", values
                            ));
                using (SqlCommand command = new SqlCommand(sql2, proConn))
                {
                    command.ExecuteNonQuery();
                }

                _updated_at = now.ToString();
                PrintLabel(lotNo.Text, now);
                MessageBox.Show("Commit Complete");
                lotNo.Focus();
                lotNo.SelectAll();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Data Input Error " + ex.Message);
            }
        }

        private void undoBtn_Click(object sender, RoutedEventArgs e)
        {
            if (_lastAction.Count == 0)
                return;
            string action = _lastAction.Pop();
            if (action == "middle")
            {
                this.isMiddle = !this.isMiddle;

                if (this.isMiddle == false)
                {
                    middleText.Text = "Add Middle";
                    RemoveMid();
                }
                else
                {
                    middleText.Text = "Remove Middle";
                    AddMid();
                }

            }
            if (action == "in")
            {
                _isIn = !_isIn;

                if (_isIn == false)
                {
                    inText.Text = "Add IN*";
                    RemoveIn();
                }
                else
                {
                    inText.Text = "Remove IN*";
                    AddIn();
                }

            }
            else if (action == "row")
            {
                for (int i = 0; i < list.Count; i++)
                {
                    mps.RemoveAt(mps.Count - 1);
                }
            }
            else if (action == "column")
            {

                measureGrid.Columns.ElementAt(nextCol--).Visibility = Visibility.Hidden;

                foreach (var item in measureGrid.Items)
                {
                    //Console.WriteLine(item);
                    MeasurePoint c = item as MeasurePoint;

                    c.setValue((nextCol + 1).ToString(), (float)0.0);
                    c.Avg = (c.P1 + c.P2 + c.P3 + c.P4 + c.P6 + c.P7 + c.P8 + c.P9 + c.P10) / (nextCol);
                    c.Avg = Math.Round(c.Avg, 2);
                    if (c.SubOrder == 1)
                    {
                        c.Grade = computeGrade(sMi16, c.Avg, kind, "-", c.Type);
                    }
                    else if (c.SubOrder != 1 && c.Type != "MID" && c.Type != "IN*")
                    {
                        if (sMi20 != "0")
                            c.Grade = computeGrade(sMi20, c.Avg, kind, "-", c.Type);
                        else
                            c.Grade = computeGrade(sMi16, c.Avg, kind, "-", c.Type);
                    }
                }


            }
            else if (action == "remove_column")
            {

                measureGrid.Columns.ElementAt(++nextCol).Visibility = Visibility.Visible;

                foreach (var item in measureGrid.Items)
                {
                    //Console.WriteLine(item);
                    MeasurePoint c = item as MeasurePoint;

                    c.setValue((nextCol).ToString(), (float)0.0);
                    computeValues(c, true);
                }


            }

            measureGrid.Items.Refresh();
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            //measureGrid.ItemsSource = null;
            RLMin.Text = Utils.getSetting("RLMin");
            RLMax.Text = Utils.getSetting("RLMax");
            this.rlAlert = new RLAlert();

            this.rlAlert.setRLWindow(this);
            this.rlAlert.ShowDialog();
        }
        private bool isAlreadyCheck(string ln)
        {
            SqlCommand catCMD = proConn.CreateCommand();

            string sql = @"SELECT  * FROM lot_summary_rl where lot_no = '" + ln + "'";
            catCMD.CommandText = sql;

            SqlDataReader reader = catCMD.ExecuteReader();

            bool r = reader.HasRows;

            reader.Close();
            return r;
        }
        private List<string> GetRecordType(string pro2)
        {
            List<string> tempList = new List<string>();
            tempList.Add("FA1");
            tempList.Add("FA2");
                //rList.Add("MID");
                //rList.Add("IN*");
            return tempList;
            
            foreach (var view in masViews)
            {

                SqlCommand cmd = proConn.CreateCommand();
                string sql = @"
              select * from [" + view + "] v where v.PRO2='" + pro2 + "'";
                cmd.CommandText = sql;
                //Console.WriteLine(sql);
                SqlDataReader reader = cmd.ExecuteReader();

                if (!reader.HasRows)
                {
                    reader.Close();
                    continue;
                }
                bool hasIn, hasOut;


                while (reader.Read())
                {

                    var mi17 = reader["MI17"].ToString();
                    if (string.IsNullOrEmpty(mi17) || mi17 == "NULL")
                    {
                        hasOut = false;
                    }
                    else
                    {
                        hasOut = true;
                    }

                    string mi21 = reader["MI21"].ToString();
                    if (string.IsNullOrEmpty(mi21) || mi21 == "NULL")
                    {
                        hasIn = false;
                    }
                    else
                    {
                        hasIn = true;
                    }
                    List<string> rList = new List<string>();
                    if (_isTkx == true)
                    {
                        rList.Add("FA1");
                        rList.Add("FA2");
                        //rList.Add("MID");
                        //rList.Add("IN*");
                        reader.Close();
                        return rList;
                    }
                    if (view == "bel_master_view" || view == "e_master_view" || view == "mg_master_view")
                    {


                        if (reader["MP45"].ToString() != "NULL" && !string.IsNullOrEmpty(reader["MP45"].ToString().Trim()))
                        {
                            rList.Add(reader["MP45"].ToString().ToUpper());

                        }
                        //rList.Add("MID");
                        //rList.Add("IN*");
                        if (reader["MP49"].ToString() != "NULL" && !string.IsNullOrEmpty(reader["MP49"].ToString().Trim()))
                        {
                            rList.Add(reader["MP49"].ToString().ToUpper());
                        }
                        else
                        {
                            if (hasIn == true)
                            {
                                var side = reader["MP45"].ToString().ToLower();
                                rList.Add(_sideMap[side].ToUpper());
                            }
                        }

                        reader.Close();

                        return rList;
                    }
                    else
                    {
                        if (reader["MP34"].ToString() != "NULL" && !string.IsNullOrEmpty(reader["MP34"].ToString().Trim()))
                        {
                            rList.Add(reader["MP34"].ToString().ToUpper());

                        }
                        //rList.Add("MID");
                        //rList.Add("IN*");
                        if (reader["MP39"].ToString() != "NULL" && !string.IsNullOrEmpty(reader["MP39"].ToString().Trim()))
                        {
                            rList.Add(reader["MP39"].ToString().ToUpper());
                        }
                        else
                        {
                            if (hasIn == true)
                            {
                                var side = reader["MP34"].ToString().ToLower();
                                rList.Add(_sideMap[side].ToUpper());
                            }
                        }

                        reader.Close();
                        return rList;
                    }
                }
            }

            return null;
        }
        private bool fillProData(string ln, string viewName)
        {

            /*
              join [V-MAS-3] v
              on p.PRO1 = v.TC
              left outer join [TABLE_TI-T4] t4 
              on v.MI5 = t4.TIT4
     
             */
            string sql = @"
            SELECT  * FROM " + viewName + " p " +
              "where p.PRO2 = '" + ln + "'";

            SqlDataReader reader = Utils.Query(proConn, sql);

            string pro1 = "";
            if (!reader.HasRows)
            {
                reader.Close();
                return false;
            }
            else
            {


                reader.Close();
                return true;
                //return pro1;
            }
        }
        private void find_Click(object sender, RoutedEventArgs e)
        {
            string ln = lotNo.Text;
            _startUsing = DateTime.Now;
            bool isFound = false;
            isMiddle = _isIn = false;
            _isExisting = false;
            _isCheck = false;
            _isTkx = false;
            midGS = "";
            inStarGS = "";
            measureGrid.ItemsSource = null;
            measureGrid.Items.Clear();
            nextCol = 3;


            
            foreach (var view in masViews)
            {
                isFound = fillProData(ln, view);
                if (isFound == true)
                    break;

            }
            if (isFound == false)
            {
                MessageBox.Show("Lot No.:" + ln + " Not Founded");
                return;
            }


            //MessageBox.Show("after fill prodata");
            mps = new ObservableCollection<MeasurePoint>();
            list = GetRecordType(ln);
            if (list.Count == 0)
            {
                list.Add("FA1");
                list.Add("FA2");
               
            }
            if (isAlreadyCheck(ln))
            {
                //ChooseRecord cr = new ChooseRecord(ln);
                //cr.ShowDialog();
                _isCheck = true;
                   ChooseRecord3 cr = new ChooseRecord3(ln);
                cr.ShowDialog();

                SqlDataReader reader = Utils.Query(proConn, "select * from data_rl where lot_no = '" + ln + "' and updated_at = '" + this._updated_at + "' order by id");
                isMiddle = false;
                _haveMiddle = false;
                while (reader.Read())
                {
                    var mp = new MeasurePoint();
                    nextCol = Convert.ToInt32(reader["ndata"]);
                    mp.Count = Convert.ToInt16(reader["row_no"]);
                    mp.Type = reader["r_type"].ToString();
                    if (mp.Type == "MID")
                    {
                        isMiddle = true;
                        _haveMiddle = true;
                       
                    }

                    mp.SubOrder = Convert.ToInt16(reader["sub_order"]);
                    mp.P1 = Math.Round(Convert.ToDouble(reader["p1"]), 2);
                    mp.P2 = Math.Round(Convert.ToDouble(reader["p2"]), 2);
                    mp.P3 = Math.Round(Convert.ToDouble(reader["p3"]), 2);
                    mp.P4 = Math.Round(Convert.ToDouble(reader["p4"]), 2);
                    mp.P5 = Math.Round(Convert.ToDouble(reader["p5"]), 2);
                    mp.P6 = Math.Round(Convert.ToDouble(reader["p6"]), 2);
                    mp.P7 = Math.Round(Convert.ToDouble(reader["p7"]), 2);
                    mp.P8 = Math.Round(Convert.ToDouble(reader["p8"]), 2);
                    mp.P9 = Math.Round(Convert.ToDouble(reader["p9"]), 2);
                    mp.P10 = Math.Round(Convert.ToDouble(reader["p10"]), 2);
                    mp.Avg = Math.Round(Convert.ToDouble(reader["avg"]), 2);
                    mp.Grade = reader["grade"].ToString();
                    mp.ResultGrade = reader["rgrade"].ToString();
                    mp.Type = reader["r_type"].ToString();

                    mps.Add(mp);

                    // if row['sub_order'] == 1 and not have middle 
                    //add dummy mid row
                    //if (Convert.ToInt32(reader["sub_order"]) == 1 && _haveMiddle == false)
                    //	mps.Add(new MeasurePoint() { Count = mp.Count, Type = "MID", SubOrder = 2 });

                }
                reader.Close();
            }
            else
            {

                for (int i = 0; i < 3; i++)
                {


                    int count = 1;
                    foreach (var l in list)
                    {
                        mps.Add(new MeasurePoint() { Count = i + 1, Type = l, SubOrder = count });
                        count++;
                    }

                }
                if (_isTkx == true)
                {
                    measureGrid.Columns.ElementAt(nextCol--).Visibility = Visibility.Hidden;
                }
            }

            measureGrid.ItemsSource = mps;

            for (int i = 1; i < 10; i++)
            {
                measureGrid.Columns.ElementAt(i).Visibility = Visibility.Hidden;
            }
            for (int i = 1; i <= nextCol; i++)
            {
                measureGrid.Columns.ElementAt(i).Visibility = Visibility.Visible;
            }

            measureGrid.Focus();
            try
            {
                measureGrid.SelectedItem = measureGrid.Items[0];
                measureGrid.CurrentCell = new DataGridCellInfo(
                            measureGrid.Items[0], measureGrid.Columns[1]);
                measureGrid.BeginEdit();
            }
            catch { }
        }
        private void measureGrid_BeginningEdit(object sender, DataGridBeginningEditEventArgs e)
        {

            this.currentColumn = e.Column.Header.ToString();
            this.currentMP = (MeasurePoint)e.Row.Item;

            cellIndicattor.Text = "(" + currentMP.Type + "-" + currentMP.Count + ", " + currentColumn + ")";

        }

        private void measureGrid_SelectedCellsChanged(object sender, SelectedCellsChangedEventArgs e)
        {

            //this.currentMP = (MeasurePoint)measureGrid.CurrentItem;
            //this.currentColumn = measureGrid.CurrentColumn.Header.ToString();
            //cellIndicattor.Text = "("+currentMP.Type + "-" +currentMP.Count+", "+currentColumn+")";

        }

        private void measureGrid_CurrentCellChanged(object sender, EventArgs e)
        {
            try
            {
                this.currentColumn = measureGrid.CurrentColumn.Header.ToString();
                this.currentMP = (MeasurePoint)measureGrid.CurrentItem;
                cellIndicattor.Text = "(" + currentMP.Type + "-" + currentMP.Count + ", " + currentColumn + ")";

                //this.computeValues(currentMP);

            }
            catch (NullReferenceException ne) { }
        }

        private void measureGrid_KeyDown(object sender, KeyEventArgs e)
        {
        }

        private void measureGrid_Loaded(object sender, RoutedEventArgs e)
        {
            //MessageBox.Show("Refresh Finish");
        }

        private void measureGrid_SourceUpdated(object sender, DataTransferEventArgs e)
        {
            MessageBox.Show("refresh finish");
        }

        private void measureGrid_ColumnDisplayIndexChanged(object sender, DataGridColumnEventArgs e)
        {

        }

        private void measureGrid_PreviewKeyDown(object sender, KeyEventArgs e)
        {

        }

        private void measureGrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
        {
            //MessageBox.Show("refresh");
            int row = e.Row.GetIndex();
            (sender as DataGrid).RowEditEnding -= measureGrid_RowEditEnding;
            (sender as DataGrid).CommitEdit();

            var c = mps[row];
            computeValues(c, true);
            measureGrid.Items.Refresh();
            (sender as DataGrid).RowEditEnding += measureGrid_RowEditEnding;
        }
        private void measureGrid_KeyUp(object sender, KeyEventArgs e)
        {
            Console.WriteLine("hello");
            //MessageBox.Show(e.Key.ToString());
            var info = measureGrid.CurrentColumn;
            var item = measureGrid.CurrentItem as MeasurePoint;
            int col = info.DisplayIndex;
            var mp = (MeasurePoint)measureGrid.CurrentItem;
            int row = mps.IndexOf(mp);
            if (e.Key == Key.Tab)
            {

                //measureGrid.RaiseEvent(new RoutedEventArgs(DataGrid.rowediten));
                //MessageBox.Show("row " + item.Count.ToString());

                //MessageBox.Show("row " +  mps.IndexOf(mp).ToString());
                //MessageBox.Show("col " + info.DisplayIndex.ToString());

                if (col < nextCol)
                {
                    measureGrid.CurrentCell = new DataGridCellInfo(
                        measureGrid.Items[row], measureGrid.Columns[col + 1]);
                    measureGrid.BeginEdit();
                }
                else
                {
                    try
                    {
                        measureGrid.CurrentCell = new DataGridCellInfo(
                    measureGrid.Items[row + 1], measureGrid.Columns[1]);
                        measureGrid.BeginEdit();


                    }
                    catch
                    {
                        measureGrid.CurrentCell = new DataGridCellInfo(
                        measureGrid.Items[0], measureGrid.Columns[1]);
                        measureGrid.BeginEdit();
                    }

                }
            }
            if (e.Key == Key.Return)
            {
                if (isMiddle == false)
                    row += 2;
                if (row > measureGrid.Items.Count)
                {
                    measureGrid.CurrentCell = new DataGridCellInfo(
                        measureGrid.Items[0], measureGrid.Columns[1]);
                    measureGrid.BeginEdit();
                }
            }
        }


        private void measureGrid_CellEditEnding(object sender, DataGridCellEditEndingEventArgs e)
        {
            /*
            row = e.Row.GetIndex();
            (sender as DataGrid).CellEditEnding -= measureGrid_CellEditEnding;
            (sender as DataGrid).CommitEdit();
            var c = mps[row];
            c.Avg = (c.P1 + c.P2 + c.P3 + c.P4 + c.P6 + c.P7 + c.P8 + c.P9 + c.P10) / (nextCol - 1);
            c.Grade = computeGrade(sMi16, c.Avg, kind, "-"); 
            (sender as DataGrid).CellEditEnding += measureGrid_CellEditEnding;
            */
        }

        private void measureGrid_LostFocus(object sender, RoutedEventArgs e)
        {
            /*
            measureGrid.LostFocus -= measureGrid_LostFocus;
            measureGrid.Items.Refresh();
            measureGrid.LostFocus += measureGrid_LostFocus;
            */
        }

        private void measureGrid_LostKeyboardFocus(object sender, KeyboardFocusChangedEventArgs e)
        {
            //measureGrid.Items.Refresh();
        }
        private void measureGrid_LoadingRow(object sender, DataGridRowEventArgs e)
        {
            /*
            DataGridRow c = e.Row;
            MeasurePoint mp = e.Row.Item as MeasurePoint;
            if (mp.Type == "MID" && this.isMiddle == false)
            {
                c.Visibility = Visibility.Collapsed;
            }
            if (mp.Type == "IN*" && _isIn == false)
            {
                c.Visibility = Visibility.Collapsed;
            }
            */
        }

        private void RLMin_TextChanged(object sender, TextChangedEventArgs e)
        {
            Utils.UpdateSetting("rlMin", RLMin.Text.ToString());
        }

        private void RLMax_TextChanged(object sender, TextChangedEventArgs e)
        {
            Utils.UpdateSetting("rlMax", RLMax.Text.ToString());
        }
    }
}