/* public List<Address> getAllAddresses()
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand();

                cmd.CommandText = "getaddresses";
                cmd.CommandType = CommandType.StoredProcedure;
                //cmd.Connection = connection;

                MySqlDataReader data;

                List<Address> listOfAddresses = new List<Address>();

                connection.Open();
                data = cmd.ExecuteReader();

                while (data.Read())
                {
                    int addressID = data.GetInt32("idAddress");
                    string addressln1 = data.GetString("AddressLine1");
                    string addressln2 = data.GetString("AddressLine2");
                    string postCode = data.GetString("Postcode");
                    string region = data.GetString("Region");
                    string country = data.GetString("Country");
                    Address address = new Address(addressID, addressln1, addressln2, postCode, region, country);

                    listOfAddresses.Add(address);

                }
                connection.Close();

                return listOfAddresses;

            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return null;
            }

        } */
        public List<Worker> getAllWorkers()
        {
            try
            {
                connection.Open();
                MySqlCommand command;
                MySqlDataReader data;

                string query = "BEGIN; CREATE OR REPLACE VIEW t AS SELECT * FROM worker; SELECT * FROM t;";
                List<Worker> listOfWorkers = new List<Worker>();

                command = new MySqlCommand(query, connection);
                data = command.ExecuteReader();

                //THIS NEEDS EXPANDING FOR CONTACT DETAILS
                while (data.Read())
                {
                    int workerID = data.GetInt32("WorkerId");
                    int storeID = data.GetInt32("Store_idStore");
                    int jobID = data.GetInt32("Job_idJob");
                    string forename = data.GetString("FirstName");
                    string surname = data.GetString("LastName");

                    Worker worker = new Worker(workerID, storeID, jobID, forename, surname);

                    listOfWorkers.Add(worker);

                }
                connection.Close();

                for (int i = 0; i < listOfWorkers.Count; i++)
                {
                    Salary addToWorker = getJobDetails(listOfWorkers[i].getJobID());
                    String storeName = getStoreName(listOfWorkers[i].getStoreID());
                    listOfWorkers[i].setJobName(addToWorker.getJobName());
                    listOfWorkers[i].setSalary(addToWorker.getSingleSalary());
                    listOfWorkers[i].setStoreName(storeName);
                }
                return listOfWorkers;

            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return null;
            }
        }
        public List<Worker> chartWorkersSalaries()
        {
            try
            {
                connection.Open();
                MySqlCommand command;
                MySqlDataReader data;
                string query = "SELECT WorkerId,Store_idStore,Job_idJob FROM worker;";
                List<Worker> listOfWorkers = new List<Worker>();

                command = new MySqlCommand(query, connection);
                data = command.ExecuteReader();

                while (data.Read())
                {
                    int workerID = data.GetInt32("WorkerId");
                    int storeID = data.GetInt32("Store_idStore");
                    int jobID = data.GetInt32("Job_idJob");

                    Worker worker = new Worker();
                    worker.setWorkerID(workerID);
                    worker.setStoreID(storeID);
                    worker.setJobID(jobID);
                    listOfWorkers.Add(worker);

                }
                connection.Close();
                foreach (Worker wrkr in listOfWorkers)
                {
                    decimal salary = getWorkerSalary(wrkr.getJobID());
                    wrkr.setSalary(salary);
                }

                return listOfWorkers;

            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return null;
            }
        }