using System;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Xml;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Win32;
using System.IO;
using System.Text;
using System.Runtime.InteropServices;
using System.Collections.Generic;
using System.Windows.Documents;
using System.Reflection;
using static System.Net.Mime.MediaTypeNames;
using System.Data.Common;
using Microsoft.Office.Interop.Excel;
using System.Threading;
using System.ComponentModel;

namespace LDorado_Reader
{
    public partial class MainWindow : System.Windows.Window
    {
        List<List<string>> datenWire = new List<List<string>>();
        List<List<string>> datenBom = new List<List<string>>();      

        public MainWindow()
        {
            InitializeComponent();
        }

        private void BtnOpen_Click(object sender, RoutedEventArgs e)
        {
            var fileContent = string.Empty;
            var filePath = string.Empty;

            OpenFileDialog openFileDialog = new OpenFileDialog();

            openFileDialog.InitialDirectory = "c:\\";
            openFileDialog.Filter = "XML files (*.xml)|*.xml";
            openFileDialog.FilterIndex = 2;
            openFileDialog.RestoreDirectory = true;

            Nullable<bool> result = openFileDialog.ShowDialog();

            if (result == true)
            {
                filePath = openFileDialog.FileName;
                txtPath.Text = filePath;
            }
           
            string Path = txtPath.Text;
            if (File.Exists(Path))
            {
                XmlDocument xdoc = new XmlDocument();
                xdoc.PreserveWhitespace = true;

                // XML Dokument laden
                xdoc.Load(Path);
                // ComplexTable Tag suchen
                XmlNodeList cTables = xdoc.GetElementsByTagName("ComplexTable");

                try
                {
                    // alle cTables durchlaufen
                    foreach (XmlNode table in cTables)
                    {
                        // tableType in Variable speichern
                        string tableType = table.Attributes["TableType"].Value;
                        // Liste von allen SubTables in der cTable in Variable schreiben
                        XmlNodeList subTables = table.SelectNodes("SubTable");

                        if (tableType == "Wirelist")
                        {
                            // Methode öffnen
                            DataRead(subTables, datenWire);
                        }
                        else if (tableType == "BOM")
                        {
                            // Methode öffnen
                            DataRead(subTables, datenBom);
                        }
                    }

                    // bei erfolgreichen Einlesen, Button aktivieren
                    btnWire.IsEnabled = true;
                    btnBom.IsEnabled = true;
                    btnWire_Bom.IsEnabled = true;
                    btnAEM.IsEnabled = true;
                }
                catch (System.IO.FileNotFoundException)
                {
                    Console.WriteLine("Datei konnte nicht gefunden werden!");
                }
            }
        }

        private void BtnWire_Click(object sender, RoutedEventArgs e)
        {
            int rw = 1; //Row
            int cl = 2; // Column

            if (txtPath.Text != "" && txtPath.Text != "XML Dateipfad...")
            {
                string filePath = txtPath.Text;

                string DateiName = Path.GetFileNameWithoutExtension(filePath);
                string ExcelName = DateiName + "_Wirelist";

                // Excel Dokument erstellen
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;

                object misValue = System.Reflection.Missing.Value;
                xlApp.Visible = false;
                // no update
                xlApp.ScreenUpdating = false;

                xlWorkBook = xlApp.Workbooks.Add(misValue);
                // in Sheet 1 von Excel schreiben
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                xlWorkSheet.Name = "Wirelist";

                foreach (var list in datenWire)
                {                    
                    list.ForEach(x => { xlWorkSheet.Cells[rw, cl] = x; cl += 1; });

                    string text = xlWorkSheet.Cells[rw, 2].Value == null ? "-" : xlWorkSheet.Cells[rw, 2].Value.ToString();
                    if (text.Length <= 3)
                    {
                        if (text.Substring(0, 1) == "V")
                        {
                            xlWorkSheet.Cells[rw, 1] = "2";
                        }
                        else
                        {
                            xlWorkSheet.Cells[rw, 1] = "3";
                        }
                    }
                    else
                    {
                        if (text.Substring(0, 4) == "LTGS" || text == "Varianten")
                        {
                            xlWorkSheet.Cells[rw, 1] = "1";
                        }
                        else if (text == "Ltg-Nr.")
                        {
                            xlWorkSheet.Cells[rw, 1] = "0";
                        }
                        else
                        {
                            xlWorkSheet.Cells[rw, 1] = "3";
                        }
                    }

                    rw += 1;
                    cl = 2;
                }

                // Excel Dokument speichern
                xlApp.DisplayAlerts = false;
                xlWorkBook.Worksheets["Wirelist"].Columns["A:XFD"].Autofit();

                string fileName = SaveFile(ExcelName, filePath);

                if (fileName == "cancel")
                {
                    xlWorkBook.Close(false, misValue, misValue);
                    xlApp.Quit();
                }
                else
                {
                    xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,
                                      misValue, misValue, misValue, misValue,
                                      Excel.XlSaveAsAccessMode.xlExclusive, misValue,
                                      misValue, misValue, misValue, misValue);

                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();

                    MessageBox.Show("Excel Dokument wurde erstellt!");
                }
                Marshal.ReleaseComObject(xlWorkSheet);
                Marshal.ReleaseComObject(xlWorkBook);
                Marshal.ReleaseComObject(xlApp);                             
            }
            else
            {
                MessageBox.Show("Dateipfad ist nicht korrekt, bitte erneut eingeben.");
            }
        }

        private void BtnBom_Click(object sender, RoutedEventArgs e)
        {
            int rw = 1; //Row
            int cl = 2; // Column

            if (txtPath.Text != "" && txtPath.Text != "XML Dateipfad...")
            {
                string filePath = txtPath.Text;

                string DateiName = Path.GetFileNameWithoutExtension(filePath);
                string ExcelName = DateiName + "_Bom";

                // Excel Dokument erstellen
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;

                object misValue = System.Reflection.Missing.Value;
                xlApp.Visible = false;
                // no update
                xlApp.ScreenUpdating = false;

                xlWorkBook = xlApp.Workbooks.Add(misValue);
                // in Sheet 1 von Excel schreiben
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                xlWorkSheet.Name = "Bom";

                foreach (var list in datenBom)
                {
                    list.ForEach(x => { xlWorkSheet.Cells[rw, cl] = x; cl += 1; });

                    string text = xlWorkSheet.Cells[rw, 2].Value == null ? "-" : xlWorkSheet.Cells[rw, 2].Value.ToString();
                    if (text.Length <= 3)
                    {
                        if (text.Substring(0, 1) == "V")
                        {
                            xlWorkSheet.Cells[rw, 1] = "2";
                        }
                        else
                        {
                            xlWorkSheet.Cells[rw, 1] = "3";
                        }
                    }
                    else
                    {
                        if (text.Substring(0, 4) == "LTGS" || text == "Varianten")
                        {
                            xlWorkSheet.Cells[rw, 1] = "1";
                        }
                        else if (text == "Ltg-Nr.")
                        {
                            xlWorkSheet.Cells[rw, 1] = "0";
                        }
                        else
                        {
                            xlWorkSheet.Cells[rw, 1] = "3";
                        }
                    }

                    rw += 1;
                    cl = 2;
                }
                // Excel Dokument speichern
                xlApp.DisplayAlerts = false;
                xlWorkBook.Worksheets["Bom"].Columns["A:XFD"].Autofit();

                string fileName = SaveFile(ExcelName, filePath);

                if (fileName == "cancel")
                {
                    xlWorkBook.Close(false, misValue, misValue);
                    xlApp.Quit();
                }
                else
                {
                    xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,
                                      misValue, misValue, misValue, misValue,
                                      Excel.XlSaveAsAccessMode.xlExclusive, misValue,
                                      misValue, misValue, misValue, misValue);

                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();

                    MessageBox.Show("Excel Dokument wurde erstellt!");
                }
                Marshal.ReleaseComObject(xlWorkSheet);
                Marshal.ReleaseComObject(xlWorkBook);
                Marshal.ReleaseComObject(xlApp);
            }
            else
            {
                MessageBox.Show("Dateipfad ist nicht korrekt, bitte erneut eingeben.");
            }
        }

        private void BtnWire_Bom_Click(object sender, RoutedEventArgs e)
        {
            int rw = 1; //Row
            int cl = 2; // Column

            if (txtPath.Text != "" && txtPath.Text != "XML Dateipfad...")
            {
                string filePath = txtPath.Text;

                string DateiName = Path.GetFileNameWithoutExtension(filePath);
                string ExcelName = DateiName + "_Wire_BOM";

                // Excel Dokument erstellen
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;

                object misValue = System.Reflection.Missing.Value;
                xlApp.Visible = false;
                // no update
                xlApp.ScreenUpdating = false;

                xlWorkBook = xlApp.Workbooks.Add(misValue);
                // in Sheet 1 von Excel schreiben
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                xlWorkSheet.Name = "Wirelist";                

                foreach (var list in datenWire)
                {
                    list.ForEach(x => { xlWorkSheet.Cells[rw, cl] = x; cl += 1; });

                    string text = xlWorkSheet.Cells[rw, 2].Value == null ? "-" : xlWorkSheet.Cells[rw, 2].Value.ToString();
                    if (text.Length <= 3)
                    {
                        if (text.Substring(0, 1) == "V")
                        {
                            xlWorkSheet.Cells[rw, 1] = "2";
                        }
                        else
                        {
                            xlWorkSheet.Cells[rw, 1] = "3";
                        }
                    }
                    else
                    {
                        if (text.Substring(0, 4) == "LTGS" || text == "Varianten")
                        {
                            xlWorkSheet.Cells[rw, 1] = "1";
                        }
                        else if (text == "Ltg-Nr.")
                        {
                            xlWorkSheet.Cells[rw, 1] = "0";
                        }
                        else
                        {
                            xlWorkSheet.Cells[rw, 1] = "3";
                        }
                    }

                    rw += 1;
                    cl = 2;
                }
                // -------------------------------------------------------------------------------------------
                rw = 1;
                cl = 2;

                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
                xlWorkSheet.Name = "Bom";

                foreach (var list in datenBom)
                {
                    list.ForEach(x => { xlWorkSheet.Cells[rw, cl] = x; cl += 1; });

                    string text = xlWorkSheet.Cells[rw, 2].Value == null ? "-" : xlWorkSheet.Cells[rw, 2].Value.ToString();
                    if (text.Length <= 3)
                    {
                        if (text.Substring(0, 1) == "V")
                        {
                            xlWorkSheet.Cells[rw, 1] = "2";
                        }
                        else
                        {
                            xlWorkSheet.Cells[rw, 1] = "3";
                        }
                    }
                    else
                    {
                        if (text.Substring(0, 4) == "LTGS" || text == "Varianten")
                        {
                            xlWorkSheet.Cells[rw, 1] = "1";
                        }
                        else if (text == "Ltg-Nr.")
                        {
                            xlWorkSheet.Cells[rw, 1] = "0";
                        }
                        else
                        {
                            xlWorkSheet.Cells[rw, 1] = "3";
                        }
                    }

                    rw += 1;
                    cl = 2;
                }
                // Excel Dokument speichern
                xlApp.DisplayAlerts = false;
                xlWorkBook.Worksheets["Wirelist"].Columns["A:XFD"].Autofit();
                xlWorkBook.Worksheets["Bom"].Columns["A:XFD"].Autofit();

                string fileName = SaveFile(ExcelName, filePath);

                if (fileName == "cancel")
                {
                    xlWorkBook.Close(false, misValue, misValue);
                    xlApp.Quit();
                }
                else
                {
                    xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,
                                      misValue, misValue, misValue, misValue,
                                      Excel.XlSaveAsAccessMode.xlExclusive, misValue,
                                      misValue, misValue, misValue, misValue);

                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();

                    MessageBox.Show("Excel Dokument wurde erstellt!");
                }
                Marshal.ReleaseComObject(xlWorkSheet);
                Marshal.ReleaseComObject(xlWorkBook);
                Marshal.ReleaseComObject(xlApp);
            }
            else
            {
                MessageBox.Show("Dateipfad ist nicht korrekt, bitte erneut eingeben.");
            }            
        }

        private void BtnAEM_Click(object sender, RoutedEventArgs e)
            {
            int rw = 1; //Row
            int cl = 2; // Column            

            if (txtPath.Text != "" && txtPath.Text != "XML Dateipfad...")
            {
                string filePath = txtPath.Text;

                string DateiName = Path.GetFileNameWithoutExtension(filePath);
                string ExcelName = DateiName + "_AEM_Vorbereitung";

                // Excel Dokument erstellen
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;

                xlApp.Visible = false;
                // no update
                xlApp.ScreenUpdating = false;

                object misValue = System.Reflection.Missing.Value;

                xlWorkBook = xlApp.Workbooks.Open(Environment.CurrentDirectory + @"\Data\AEM.xlsx",
                                                                  misValue, false, misValue, misValue, misValue,
                                                                  misValue, misValue, misValue, misValue, misValue,
                                                                  misValue, misValue, misValue, misValue);

                // in Sheet 2 von Excel schreiben
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
                xlWorkSheet.Name = "Wirelist";

                foreach (var list in datenWire)
                {
                    list.ForEach(x => { xlWorkSheet.Cells[rw, cl] = x; cl += 1; });

                    string text = xlWorkSheet.Cells[rw, 2].Value == null ? "-" : xlWorkSheet.Cells[rw, 2].Value.ToString();
                    if (text.Length <= 3)
                    {
                        if (text.Substring(0, 1) == "V")
                        {
                            xlWorkSheet.Cells[rw, 1] = "2";
                        }
                        else
                        {
                            xlWorkSheet.Cells[rw, 1] = "3";
                        }
                    }
                    else
                    {
                        if (text.Substring(0, 4) == "LTGS" || text == "Varianten")
                        {
                            xlWorkSheet.Cells[rw, 1] = "1";
                        }
                        else if (text == "Ltg-Nr.")
                        {
                            xlWorkSheet.Cells[rw, 1] = "0";
                        }
                        else
                        {
                            xlWorkSheet.Cells[rw, 1] = "3";
                        }
                    }

                    rw += 1;
                    cl = 2;
                }
                // -------------------------------------------------------------------------------------------
                rw = 1;
                cl = 2;

                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(3);
                xlWorkSheet.Name = "Bom";

                foreach (var list in datenBom)
                {
                    list.ForEach(x => { xlWorkSheet.Cells[rw, cl] = x; cl += 1; });

                    string text = xlWorkSheet.Cells[rw, 2].Value == null ? "-" : xlWorkSheet.Cells[rw, 2].Value.ToString();
                    if (text.Length <= 3)
                    {
                        if (text.Substring(0, 1) == "V")
                        {
                            xlWorkSheet.Cells[rw, 1] = "2";
                        }
                        else
                        {
                            xlWorkSheet.Cells[rw, 1] = "3";
                        }
                    }
                    else
                    {
                        if (text.Substring(0, 4) == "LTGS" || text == "Varianten")
                        {
                            xlWorkSheet.Cells[rw, 1] = "1";
                        }
                        else if (text == "Ltg-Nr.")
                        {
                            xlWorkSheet.Cells[rw, 1] = "0";
                        }
                        else
                        {
                            xlWorkSheet.Cells[rw, 1] = "3";
                        }
                    }

                    rw += 1;
                    cl = 2;
                }
                // Excel Dokument speichern
                xlApp.DisplayAlerts = false;
                xlWorkBook.Worksheets["Wirelist"].Columns["A:XFD"].Autofit();
                xlWorkBook.Worksheets["Bom"].Columns["A:XFD"].Autofit();

                string fileName = SaveFile(ExcelName, filePath);

                if (fileName == "cancel")
                {
                    xlWorkBook.Close(false, misValue, misValue);
                    xlApp.Quit();
                }
                else
                {
                    xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,
                                      misValue, misValue, misValue, misValue,
                                      Excel.XlSaveAsAccessMode.xlExclusive, misValue,
                                      misValue, misValue, misValue, misValue);

                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();

                    MessageBox.Show("Excel Dokument wurde erstellt!");
                }
                Marshal.ReleaseComObject(xlWorkSheet);
                Marshal.ReleaseComObject(xlWorkBook);
                Marshal.ReleaseComObject(xlApp);
            }
            else
            {
                MessageBox.Show("Dateipfad ist nicht korrekt, bitte erneut eingeben.");
            }          
        }

        private static void DataRead(XmlNodeList subTables, List<List<string>> daten)
            {
                // Subtalbe durchlaufen in ComplexTable mit richtigen TableType
                foreach (XmlNode subTable in subTables)
                {
                    // Spalten durchlaufen
                    XmlNodeList rows = subTable.SelectNodes("Row");

                    foreach (XmlNode row in rows)
                    {
                        List<string> Column = new List<string>();

                        // einzelne Cells durchlaufen
                        XmlNodeList cells = row.SelectNodes("Cell");
                        foreach (XmlNode cell in cells)
                        {
                            // einzeltene Celleninhalten ausgeben
                            string text = cell.Attributes["Text"].Value;                        
                            Column.Add(text);
                        }
                        daten.Add(Column);
                    }
                }
            }       

        private void Grid_Drop(object sender, DragEventArgs e)
        {
            if (e.Data.GetDataPresent(DataFormats.FileDrop))
            {
                string[] files = (string[])e.Data.GetData(DataFormats.FileDrop);
                // prüft ob Dateityp korrekt ist
                var fileInfo = new FileInfo(files[0]);
                if (fileInfo.Extension == ".xml")
                {
                    txtPath.Text = files[0];

                    string Path = txtPath.Text;
                    if (File.Exists(Path))
                    {
                        XmlDocument xdoc = new XmlDocument();
                        xdoc.PreserveWhitespace = true;

                        // XML Dokument laden
                        xdoc.Load(Path);
                        // ComplexTable Tag suchen
                        XmlNodeList cTables = xdoc.GetElementsByTagName("ComplexTable");

                        try
                        {
                            // alle cTables durchlaufen
                            foreach (XmlNode table in cTables)
                            {
                                // tableType in Variable speichern
                                string tableType = table.Attributes["TableType"].Value;
                                // Liste von allen SubTables in der cTable in Variable schreiben
                                XmlNodeList subTables = table.SelectNodes("SubTable");

                                if (tableType == "Wirelist")
                                {
                                    // Methode öffnen
                                    DataRead(subTables, datenWire);
                                }
                                else if (tableType == "BOM")
                                {
                                    // Methode öffnen
                                    DataRead(subTables, datenBom);
                                }
                            }

                            // bei erfolgreichen Einlesen, Butten aktivieren
                            btnWire.IsEnabled = true;
                            btnBom.IsEnabled = true;
                            btnWire_Bom.IsEnabled = true;
                            btnAEM.IsEnabled = true;
                        }
                        catch (System.IO.FileNotFoundException)
                        {
                            Console.WriteLine("Datei konnte nicht gefunden werden!");
                        }
                    }
                }
                else
                {
                    MessageBox.Show("Bitte eine .XML Datei einfügen");
                }
            }
        }

        static string SaveFile(string ExcelName, string FilePath)
        {
            string filename;
            SaveFileDialog dlg = new SaveFileDialog();
            // gibt als Ausgangspfad den XML Pfad aus   
            dlg.InitialDirectory = FilePath;
            // Wiederherstellung letzten Dateipfad ausschalten
            dlg.RestoreDirectory = false;
            // Dateinamen festlegen
            dlg.FileName = ExcelName;
            dlg.DefaultExt = ".xlsx";
            dlg.Filter = "Excel documents (.xlsx)|*.xlsx";

            Nullable<bool> result = dlg.ShowDialog();

            if (result == true)
            {
                filename = dlg.FileName;
                return filename;
            }
            else
            {
                return filename = "cancel";
            }
        }

    }
}