using System;
using System.Collections;
using System.Linq;
using System.Drawing;
using System.Globalization;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using DevExpress.Spreadsheet;
using DevExpress.Web.Office;
using DevExpress.XtraSpreadsheet;


namespace DevExpress.Web.Demos
{

    public class CellValueValidationHelpers
    {

        static Worksheet MPD_Page, Section_Page, ATA_Page, Revision_Page, Carte_Page, MRBR_Page, ACCESS_Page ,ZONE_Page ;
        public static void OnCellValueChanged(object sender, SpreadsheetCellEventArgs e)
        {
            SpreadsheetDocumentInfo documentInfo = sender as SpreadsheetDocumentInfo;
            revisionValidationCell(e.Cell);
            sectionValidationCell(e.Cell);

        }
        //   public static void PrepareWorksheet(Worksheet MPD_Page_in, Worksheet Revision_Page_in, Worksheet Section_Page_in, Worksheet ATA_Page_in, Worksheet Carte_Page_in)
        public static async Task PrepareWorksheet(DevExpress.Web.Mvc.MVCxSpreadsheet wb)

        {

            MPD_Page = wb.Document.Worksheets[0];
            Section_Page = wb.Document.Worksheets[2];
            Revision_Page = wb.Document.Worksheets[1];
            ATA_Page = wb.Document.Worksheets[3];
            Carte_Page = wb.Document.Worksheets[4];
            MRBR_Page = wb.Document.Worksheets[5];
            ACCESS_Page  = wb.Document.Worksheets[6];
            ZONE_Page = wb.Document.Worksheets[7];

            var tr = revisionValidation();
            var sr = sectionValidation();
            var tv = taskValidation();
            var mr = mrbrValidation();
            accessValidation();
             preparationValidation();
            zoneValidation();
          //  await tr;
          //  await sr;
          //  await tv;
          //  await mr;

        }

        private static void zoneValidation()
        {
            var cells = MPD_Page.Columns[6].ExistingCells.ToArray();
            string indice = "";
            var zonesListes = ZONE_Page.Columns[0].ExistingCells.ToArray();
            string[] zonesValue = new string[zonesListes.Length];
            string[] zones;
            for (int i = 0; i < zonesListes.Length; i++)
            {
                zonesValue[i] = zonesListes[i].Value.ToString();

            }
            for (int i = 1; i < cells.Length; i++)
            {

                indice = "G" + (i + 1).ToString();
                if (cells[i].Value.ToString() == "")
                {
                    continue;
                }
                zones = cells[i].Value.ToString().Split('\n');
                foreach (var zone in zones)
                {
                    if (Array.IndexOf(zonesValue, zone) == -1)
                    {
                        MPD_Page.Cells[indice].FillColor = Color.Red;
                        break;
                    }

                }


            }
        }

        private static void preparationValidation()
        {
            var cells = MPD_Page.Columns[5].ExistingCells.ToArray();
            string indice = "";
            for (int i = 1; i < cells.Length; i++)
            {

                indice = "F" + (i + 1).ToString();
                if (cells[i].Value.ToString() == "")
                {
                    continue;
                }

                if (cells[i].Value.ToString().IndexOf("\n")>0)
                {
                    MPD_Page.Cells[indice].FillColor = Color.Red;
                }

            }



        }

        static void accessValidation()
        {
            var accessListe = ACCESS_Page.Columns[0].ExistingCells.ToArray();
            var cells = MPD_Page.Columns[4].ExistingCells.ToArray();
            var zonesListes = MPD_Page.Columns[6].ExistingCells.ToArray();
            string[] accessValue = new string[accessListe.Length];

            for (int i = 0; i < accessListe.Length; i++)
            {
                accessValue[i] = accessListe[i].Value.ToString();

            }


            string indice = "";

            for (int i = 1; i < cells.Length; i++)
            {
                indice = "E" + (i + 1).ToString();
                bool notFound = false;
                string[] ligneAccess , zones ;
                string[] access;
                string[] cellAccessValues , cellAccessValuesReversed;

                if (cells[i].Value.ToString() == "")
                {
                    continue;
                }

                zones = zonesListes[i].Value.ToString().Split('\n');
                ligneAccess = cells[i].Value.ToString().Split('\n');

                if (zones.Length < ligneAccess.Length)
                {
                    MPD_Page.Cells[indice].FillColor = Color.Red;
                    continue;

                }
                cellAccessValues = cells[i].Value.ToString().Replace('\n', ';').Split(';');
                cellAccessValuesReversed = cells[i].Value.ToString().Replace('\n', ';').Split(';');
                Array.Reverse(cellAccessValuesReversed);


                foreach (var itemligne in ligneAccess)
                {

                    if (itemligne != "") {
                    access = itemligne.Split(';');
                    foreach (var itemaccess in access)
                    {

                       if (Array.IndexOf(accessValue, itemaccess)== -1 || (Array.IndexOf(cellAccessValues, itemaccess) != (cellAccessValues.Length - Array.IndexOf(cellAccessValuesReversed, itemaccess)) - 1))
                        {
                            notFound = true;
                            MPD_Page.Cells[indice].FillColor = Color.Red;
                            break;
                        }
                    }
                    if (notFound)
                    {
                        break;
                    }
                }



                }



            }
        }

        static async Task revisionValidation()
        {
            Column cc = MPD_Page.Columns[0];
            var cells = cc.ExistingCells.ToArray();
            string indice = "";

            for (int i = 2; i < cells.Length; i++)
            {
                indice = "A" + (i + 1).ToString();

                if (cells[i].Value.ToString() == "" || cells[i].Value.ToString() == "R" || cells[i].Value.ToString() == "N" || cells[i].Value.ToString() == "D")
                {

                }
                else
                {
                    MPD_Page.Cells[indice].FillColor = Color.Red;

                }
            }
        }
        static async Task sectionValidation()
        {
            ArrayList arrSample = new ArrayList();


            Column cc = Section_Page.Columns[0];
            Column cc2 = ATA_Page.Columns[0];


            var cells = cc.ExistingCells.ToArray();
            var cells2 = cc2.ExistingCells.ToArray();

            Regex regex = new Regex(@"^[0-9]?[0-9]-[0-9][0-9]$");
            string[] token;
            string indice = "";
            // var ar = MPD_Page.Columns[0].ExistingCells.ToArray() ;
            int colN = MPD_Page.Columns[0].ExistingCells.ToArray().Length;
            for (int i = 2; i < colN; i++)
            {
                indice = "B" + i.ToString();



                if (regex.IsMatch(MPD_Page.Cells[indice].Value.ToString()))
                {
                    token = MPD_Page.Cells[indice].Value.ToString().Split('-');
                    if (!cells.Any(x => x.DisplayText == token[0]) || !cells2.Any(x => x.DisplayText == token[1]))
                    {

                        MPD_Page.Cells[indice].FillColor = Color.Red;
                    }

                }
                else
                {
                    MPD_Page.Cells[indice].FillColor = Color.Red;
                }

            }
        }
        static async Task taskValidation()
        {
            Column cc = MPD_Page.Columns[2];
            var regexStrings = Carte_Page.Columns[0].ExistingCells.ToArray();

            var cells = cc.ExistingCells.ToArray();


            string[] cellsValue = new string[cells.Length];
            string[] cellsValueReversed = new string[cells.Length];
            for (int i = 0; i < cells.Length; i++)
            {
                cellsValue[i] = cells[i].Value.ToString();
                cellsValueReversed[i] = cells[i].Value.ToString();
            }

            Array.Reverse(cellsValueReversed);


            string indice = "";

            Regex[] regexS = new Regex[regexStrings.Length];
            int regexS_length = 0;
            for (int i = 1; i < regexStrings.Length; i++)
            {

                if (regexStrings[i].Value.ToString() != "")
                    regexS[regexS_length++] = new Regex(regexStrings[i].Value.ToString());
            }


            //  Regex regex = new Regex(@"^[0-9]{6}-[0-9]{2}-[0-9]{1}$");
            for (int i = 1; i < cells.Length; i++)
            {
                indice = "C" + (i + 1).ToString();

                if (Array.IndexOf(cellsValue, cellsValue[i]) != (cells.Length - Array.IndexOf(cellsValueReversed, cellsValue[i])) - 1)
                {
                    MPD_Page.Cells[indice].FillColor = Color.Red;
                    continue;
                }


                MPD_Page.Cells[indice].FillColor = Color.Red;
                for (int j = 0; j < regexS_length; j++)
                {
                    if (regexS[j].IsMatch(cells[i].Value.ToString()))
                    {
                        MPD_Page.Cells[indice].FillColor = Color.White;
                        break;
                    }
                }





            }


        }
        static async Task mrbrValidation()
        {
            Column cc = MPD_Page.Columns[3];
            var regexStrings = MRBR_Page.Columns[5].ExistingCells.ToArray();

            var cells = MPD_Page.Columns[3].ExistingCells.ToArray();

            Regex[] regexS = new Regex[regexStrings.Length - 1];
            int regexS_length = 0;
            for (int i = 1; i < regexStrings.Length; i++)
            {

                if (regexStrings[i].Value.ToString() != "")
                    regexS[regexS_length++] = new Regex(regexStrings[i].Value.ToString());
            }

            string indice = "";


            for (int i = 1; i < cells.Length; i++)
            {
                indice = "D" + (i + 1).ToString();

                /*
                if (Array.IndexOf(cellsValue, cellsValue[i]) != (cells.Length - Array.IndexOf(cellsValueReversed, cellsValue[i])) - 1)
                {
                    MPD_Page.Cells[indice].FillColor = Color.Red;
                    continue;
                } */

                string[] elemnts;
                MPD_Page.Cells[indice].FillColor = Color.Red;

                for (int j = 0; j < regexS_length; j++)
                {
                    elemnts = cells[i].Value.ToString().Split('\n');
                    foreach (var item in elemnts)
                    {
                        if (regexS[j].IsMatch(item))
                        {
                            MPD_Page.Cells[indice].FillColor = Color.White;
                            break;
                        }
                    }




                }





            }
        }

        static async Task revisionValidationCell(Cell cell)
        {
            if (cell.ColumnIndex != 0)
                return;

            if (cell.Value.ToString() == "" || cell.Value.ToString() == "R" || cell.Value.ToString() == "N" || cell.Value.ToString() == "D")
                cell.FillColor = Color.White;
            else cell.FillColor = Color.Red;
        }
        static async Task sectionValidationCell(Cell cell)
        {
            if (cell.ColumnIndex != 1)
                return;
            Column cc = Section_Page.Columns[0];
            Column cc2 = ATA_Page.Columns[0];


            var cells = cc.ExistingCells.ToArray();
            var cells2 = cc2.ExistingCells.ToArray();
            Regex regex = new Regex(@"^[0-9]?[0-9]-[0-9][0-9]$");

            string[] token;
            if (regex.IsMatch(cell.Value.ToString()))
            {
                token = cell.Value.ToString().Split('-');
                if (!cells.Any(x => x.DisplayText == token[0]) || !cells2.Any(x => x.DisplayText == token[1]))
                    cell.FillColor = Color.Red;
                else cell.FillColor = Color.White;
            }
            else
                cell.FillColor = Color.Red;
        }
    }
}