/* 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;
}
}