CheckboxCreator.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using GUI.Datatypes;

namespace GUI.IO_Modules
{
    class CheckboxCreator
    {
        public String Dir;
        private String[] locationNames;
        private LocationCB[] locations;
        private List<MachineCB>[] machinesAtLocation;
        private List<ProductCB>[] productsAtLocation;
        private Dictionary<String, int> locationDictionary = new Dictionary<String, int>();

        public CheckboxCreator(String dir)
        {
            Dir = dir;
        }

        public void Create()
        {
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = Dir;
            csbuilder.Add("Extended Properties", "Excel 12.0 Xml");

            DataTable sheet1 = new DataTable();
            using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
            {
                connection.Open();
                // select everything from worksheet "Allgemein"
                string sqlQuery = @"SELECT * FROM [Allgemein$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(sqlQuery, connection))
                {
                    adapter.Fill(sheet1);
                }
                createLocations(sheet1);
                connection.Close();
            }

            foreach (String SheetName in locationNames)
            {
                sheet1 = new DataTable();
                using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
                {
                    connection.Open();
                    // select everything from the worksheet that is named after a location
                    string sqlQuery = @"SELECT * FROM [Standort_" + SheetName + "$]";
                    using (OleDbDataAdapter adapter = new OleDbDataAdapter(sqlQuery, connection))
                    {
                        adapter.Fill(sheet1);
                    }
                    createMachines(sheet1, SheetName);

                    connection.Close();
                }
            }
        }

        public String[] GetLocationNames()
        {
            return locationNames;
        }

        public Dictionary<String, int> GetLocationDict()
        {
            return locationDictionary;
        }

        public LocationCB[] GetAllLocations()
        {
            return locations;
        }

        public List<MachineCB>[] GetAllMachines()
        {
            return machinesAtLocation;
        }

        public List<ProductCB>[] GetAllProducts()
        {
            return productsAtLocation;
        }

        private void createLocations(DataTable sheet)
        {
            locationNames = new String[Convert.ToInt32(sheet.Columns[0].Caption)];
            for (int i = 0; i < locationNames.Length; i++)
            {
                // locationnames is stored at sheet.Rows[i].ItemArray[0]
                locationNames[i] = sheet.Rows[i].ItemArray[0].ToString();
            }

            int numberOfLocations = locationNames.Length;
            locations = new LocationCB[numberOfLocations];
            machinesAtLocation = new List<MachineCB>[numberOfLocations];
            productsAtLocation = new List<ProductCB>[numberOfLocations];

            int counter = 0;
            foreach (String s in locationNames)
            {
                locationDictionary.Add(s, counter);
                locations[counter] = new LocationCB(s, Convert.ToDouble(sheet.Rows[counter].ItemArray[4]));
                machinesAtLocation[counter] = new List<MachineCB>();
                productsAtLocation[counter] = new List<ProductCB>();
                counter++;
            }
        }

        private void createMachines(DataTable sheet, String LocationName)
        {
            int numOfMachines = (sheet.Columns.Count - 1) / 3;
            LocationCB loc = locations[locationDictionary[LocationName]];
            for (int i = 0; i < numOfMachines; i++)
            {
                String Machinename = sheet.Columns[1 + 3 * i].ToString();
                MachineCB m = new MachineCB(Machinename, loc);
                machinesAtLocation[locationDictionary[LocationName]].Add(m);
                createProducts(sheet, m, 1 + 3 * i);
            }
        }

        private void createProducts(DataTable sheet, MachineCB m, int indexOfMachine)
        {
            for (int i = 1; i < sheet.Rows.Count;i++)
            {
                DataRow d= sheet.Rows[i];
                if (!d[indexOfMachine].ToString().Equals(""))
                {
                    ProductCB p = new ProductCB(d[0].ToString(), m, Convert.ToDouble(d[indexOfMachine]), Convert.ToDouble(d[indexOfMachine+1]), Convert.ToDouble(d[indexOfMachine+2]));
                    productsAtLocation[0].Add(p);
                }
            }
        }
    }
}