using System;
using System.Collections.Generic;
using System.ComponentModel.Design;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Runtime.Remoting.Metadata.W3cXsd2001;
using MathNet.Numerics;
using MathNet.Numerics.LinearAlgebra;

namespace WindowsFormsApplication1
{


// THIS CODE REQUIERS MathNet.Numeric AND ClosedXML_Excel PACKAGES
    internal partial class interpolator
    {
         // DONT FORGET TO COPY THESE VARIABLES
        private static int numberOfRows = 483;
        private static int numberOfcolumns = 3;
        private static int numberofColumnSubsections = 3;
        private static int numberOfMins = 4;

        public static int DesiredX;
        public static int DesiredY;
        public static int RealX;
        public static int RealY;
        public static Pair[] nearPoints = new Pair[numberOfMins];
        
       
        /*
        public static void Main(string[] args)
        {
            try
            {
                DesiredX = 280;
                DesiredY = 68;
                Pair temp = calculateMapping(DesiredX, DesiredY);
                Pair realValues = new Pair(RealX , RealY);
                Console.WriteLine( "valuse produced by the algorithm :  " + (int) temp.x + " " + (int)temp.y);
                //Console.WriteLine( "real values of above values: "  + realValues.x + " " + realValues.y);
            }
            catch (Exception e)
            {
                using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"Log", true))
                {
                    file.WriteLine(e);
                }
            }
        }
        */

       
        
        // INSERT THIS METHOD WHERE YOU WANT THE MAPPING TO HAPPEN
        public static Pair calculateMapping(int x, int y)
        {
            // READ DATA FROM THE EXCELL FILE
            int[][] desired = new int[numberOfRows][];
            int[][] real = new int[numberOfRows][];
            for (int i = 0; i < numberOfRows; i++)
            {
                real[i] = new int[numberOfcolumns];
                desired[i] = new int[numberOfcolumns];
            }
            //ENTER EXCEL FILE PATH HERE
            var path = @"NEW ERROR TABLE.xlsx";
            int[] data = createDataTable(path);
            
            
            var offSetPath = @"offset extra.xlsx";
            int[] offSetNumbers = createDataTable(offSetPath);
            
            int XOffset = offSetNumbers[0];
            int YOffset = offSetNumbers[1];
            int XUpperBound = Int32.MinValue;
            int XLowerBound = Int32.MaxValue;
            int YUpperBound = Int32.MinValue;
            int YLowerBound = Int32.MaxValue;
            bool inBoundryX = false;
            bool inBoundryY = false;


            // TURN DATA TABLE INTO desired AND real ARRAYS
            int k = 0;
            for (int i = 0; i < numberOfRows*numberOfcolumns*numberofColumnSubsections; i+=numberOfcolumns*numberofColumnSubsections)
            {
                for (int j = 0 , p = i; j < numberofColumnSubsections; j++ , p++)
                {
                    desired[k][j] = data[p];
                    real[k][j] = data[p + numberofColumnSubsections];
                    if (j == 0)
                    {
                        if (real[k][j] > XUpperBound)
                        {
                            XUpperBound = real[k][j];
                        }

                        if (real[k][j] < XLowerBound)
                        {
                            XLowerBound = real[k][j];
                        }
                    }

                    if (j == 1)
                    {
                        if (real[k][j] > YUpperBound)
                        {
                            YUpperBound = real[k][j];
                        }

                        if (real[k][j] < YLowerBound)
                        {
                            YLowerBound = real[k][j];
                        }
                    }
                }
                k++;
            }


            int desiredX = x;
            int desiredY = y;
            // point IS GONNA BE THE FINAL RESULT
            int[] point = {desiredX, desiredY};
            
            if (point[0] < XUpperBound && point[0] > XLowerBound)
            {
                inBoundryX = true;
            }

            if (point[1] < YUpperBound && point[1] > YLowerBound)
            {
                inBoundryY = true;
            }
            // FIND THE NEAREST POINTS TO GIVEN POINT
            Pair[][] nearestPoints = findNearestPoints(desiredX, desiredY, real, desired);
            
            // CALCULATE THE MAPPED VALUES FOR THE GIVEN POINT
            for (int j = 0; j < nearestPoints.Length; j++)
            {
                double[] points = new double[nearestPoints[0].Length];
                double[] values = new double[nearestPoints[0].Length];
                for (int i = 0; i < nearestPoints[0].Length; i++)
                {
                    points[i] = nearestPoints[j][i].x;
                    values[i] = nearestPoints[j][i].y;
                }

                int number = numberOfMins;
                for (int p = 0; p < number-1; p++)
                {
                    for (int n = p+1; n < number; n++)
                    {
                        if (notEqual(points[p] , values[p] , points[n] , values[n]))
                        {
                            points[n] = points[number-1];
                            values[n] = values[number-1];
                            number--;
                        }
                    }
                }
                
                double[] endResultPoints = new double[number];
                double[] endResultValues = new double[number];

                for (int i = 0; i < number; i++)
                {
                    endResultPoints[i] = points[i];
                    endResultValues[i] = values[i];
                }

                
                if ((j == 0 && inBoundryX) || (j == 1 && inBoundryY))
                {
                    var interpolator =
                        MathNet.Numerics.Interpolation.NevillePolynomialInterpolation.Interpolate(endResultPoints,
                            endResultValues);
                    point[j] = (int) interpolator.Interpolate(point[j]);
                }
            }

            point[0] = (!inBoundryX) ? (point[0] + XOffset) : point[0];
            point[1] = (!inBoundryY) ? (point[1] + YOffset) : point[1];
            Pair endResult = new Pair(point[0] , point[1] );
            
            for (int i = 0; i < numberOfRows; i++)
            {
                if (endResult.x == desired[i][0] && endResult.y == desired[i][1])
                {
                    RealX = real[i][0];
                    RealY = real[i][1];
                }
            }
            return endResult;
        }
        
        public static Pair[][] findNearestPoints(int desiredX, int desiredY, int[][] real, int[][] desired)
        {
            // POPULATE THE DISTANCES ARRAY
            Pair[][] result = new Pair[2][];
            for (int i = 0; i < 2; i++)
            {
                result[i] = new Pair[numberOfMins];
            }
            double[] distances = new double[numberOfRows];
            for (int i = 0; i < numberOfRows; i++)
            {
                distances[i] = distance(real[i][0], real[i][1], desiredX, desiredY);
            }
            // FIND THE CLOSEST POINTS REAL AND DESIRED VALUES AND STORE THEM IN result[0] AND result[1]
            findMins(distances , result , desired , real);
            //findMins(distances , result[1] , desired , real , 1);
            // RETURN A TWO DIMENSIONAL ARRAY CONTAING REAL AND DESIRED VALUES FOR THE CLOSEST FOUND POINTS
            return result;
        }
        
        // CREATES A DATA TABLE FROM THE GIVEN EXCEL FILE PATH
        public static int[] createDataTable(string filePath)
        {
            int k = 0;
            List<DataTable> worksheets = ImportExcel(filePath);
            int[] data = new int[numberOfcolumns*numberOfRows*numberofColumnSubsections];
            foreach(var item in worksheets){
                foreach (DataRow row in  item.Rows)
                {
                    Object[] temp = row.ItemArray;
                    for (int i = 0; i < temp.Length; i++)
                    {
                        if (!temp[i].ToString().Trim().Equals("")) 
                        {
                            data[k] = Convert.ToInt32(temp[i].ToString());
                            k++;
                            if (k >= numberOfcolumns * numberOfRows * numberofColumnSubsections - 1)
                            {
                                return data;
                            }
                        }
                    }
                }
               
            }
            return data;
        }

        // USED INSIDE createDataTable METHOD
        public static List<DataTable> ImportExcel(string FileName)
        {
            List<DataTable> _dataTables = new List<DataTable>();
            string _ConnectionString = string.Empty;
            string _Extension = Path.GetExtension(FileName);
            //Checking for the extentions, if XLS connect using Jet OleDB
            if (_Extension.Equals(".xls", StringComparison.CurrentCultureIgnoreCase))
            {
                _ConnectionString =
                    "Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties=Excel 8.0";
            }
            //Use ACE OleDb
            else if (_Extension.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase))
            {
                _ConnectionString =
                    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0";
            }

            DataTable dataTable = null;

            using (OleDbConnection oleDbConnection =
                new OleDbConnection(string.Format(_ConnectionString, FileName)))
            {
                oleDbConnection.Open();
                //Getting the meta data information.
                //This DataTable will return the details of Sheets in the Excel File.
                DataTable dbSchema = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables_Info, null);
                foreach (DataRow item in dbSchema.Rows)
                {
                    //reading data from excel to Data Table
                    using (OleDbCommand oleDbCommand = new OleDbCommand())
                    {
                        oleDbCommand.Connection = oleDbConnection;
                        oleDbCommand.CommandText = string.Format("SELECT * FROM [{0}]",
                            item["TABLE_NAME"].ToString());
                        using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter())
                        {
                            oleDbDataAdapter.SelectCommand = oleDbCommand;
                            dataTable = new DataTable(item["TABLE_NAME"].ToString());
                            oleDbDataAdapter.Fill(dataTable);
                            _dataTables.Add(dataTable);
                        }
                    }
                }
            }

            return _dataTables;
        }

        // CALCULATES THE DISTANCE BETWEEN TWO GIVEN POINTS
        public static double distance(int x1, int y1, int x2, int y2)
        {
            return Math.Sqrt((x1 - x2) * (x1 - x2) + (y1 - y2) * (y1 - y2));
        }

        // FINDS numberOfMins MINIMUMS IN THE GIVEN ARRAY
        public static void findMins(double[] distances , Pair[][] result , int[][] desired ,int[][] real)
        {
            int temp = 1;
            int index = 0;
            double currentMin = distances.Min();
            for (int i = 0; i < distances.Length; i++)
            {
                if (distances[i] == currentMin)
                {
                    index = i;
                }
            }
            Pair temp2X = new Pair(real[index][0] , desired[index][0]);
            Pair temp2Y = new Pair(real[index][1] , desired[index][1]);
            nearPoints[0] = new Pair( index , (int) distances[index]);
           
            result[0][0] = temp2X;
            result[1][0] = temp2Y;
            while (temp != numberOfMins)
            {
                double iterator = Math.Pow(10 , 5);
                index = 0;
                for (int i = 0; i < distances.Length; i++)
                {
                    if (distances[i] < iterator && distances[i] > currentMin && !notEqual(temp2X.y , temp2Y.y , desired[i][0] , desired[i][1]))
                    {
                        iterator = distances[i];
                        index = i;
                    }
                }
               
                currentMin = iterator;
                Pair tempX = new Pair(real[index][0] , desired[index][0]);
                Pair tempY = new Pair(real[index][1] , desired[index][1]);
                result[0][temp] = tempX;
                result[1][temp] = tempY;
                nearPoints[temp] = new Pair( index , (int )iterator);
                temp++;
            }
            /*
            using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"Log", true))
            {
                file.WriteLine("nearest points are : ");
            }
            
            for (int i = 0; i < nearPoints.Length; i++)
            {
                using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"Log", true))
                {
                    file.WriteLine("index : " + nearPoints[i].x + "   " + "distance : " + nearPoints[i].y + "| its real values : " + result[0][i].x + " " + result[1][i].x);
                }
                //Console.WriteLine("index : " + nearPoints[i].x + "   " + "distance : " + nearPoints[i].y + "| its real values : " + result[0][i].x + " " + result[1][i].x);
            }
            */
        }

        public static bool notEqual(double x1, double y1, double x2, double y2)
        {
            if (x1 != x2)
            {
                return false;
            }

            if (y1 != y2)
            {
                return false;
            }

            return true;
        }
        
    }
        // CLASS USED FOR STORING A POINT VALUE
    class Pair
    {
        public double x;
        public double y;

        public Pair(int x, int y)
        {
            this.x = x;
            this.y = y;
        }
    }

}