public static void populateProjects(string Username)
{
/*
* Populate the project list for a given username
*/
//Clear any projects in the system and load more from the database
purgeProjects();
DatabaseHandler.loadProjects(Username);
//Threadsafe estimation calculations for each project
foreach (Project project in projectList)
{
TimeHandler.estimatedFinishingDate(project);
foreach (Task task in project.taskList)
{
Trace.WriteLine("Calculating EFT for task: " + task.TaskName);
TimeHandler.estimatedTaskFinishingDate(task);
}
}
//Run email handler now projects are loaded
if (LoginHandler.shouldSendEMail)
{
EMail email = new EMail();
email.sendEMail(emailBody());
LoginHandler.shouldSendEMail = false;
}
}
public static void updateProjectLength(int projectID)
{ /*
* This method takes a project's ID and updates that project's length in the database
*/
double projectLength = 0;
Project currProject = null;
//Return the project object based on its unique ID
foreach (Project project in ModelView.projectList)
{
if (project.ID == projectID)
{
currProject = project;
projectLength = TimeHandler.getProjectHoursFromTasks(project);
}
}
//If the projects new length is greater than zero (it exists) update the database with the 'new' length
if (projectLength > 0)
{
string SQL = "UPDATE tblProject SET ProjectLength = @LENGTH WHERE ProjectID = @PID";
//Populate command object
command = new MySqlCommand(SQL, Conn);
try
{
//Add SQL param values
command.Parameters.AddWithValue("@LENGTH", projectLength);
command.Parameters.AddWithValue("@PID", currProject.ID);
}
catch (Exception)
{
MessageBox.Show("Failed to bind information to SQL variables");
}
/*Open the connection and execute the insert-into command. This method will return the number of rows that have been affected (in this case, added)
* therefore, if affectedRows>0, insertion has been completed successfully*/
Conn.Open();
if (command.ExecuteNonQuery() > 0)
{
Trace.WriteLine("Updated project hours and length successfully");
}
else
{
//If the code gets this far, it means that there has not been an in-code syntax error, instead a data entry error (v.likely to be user-related)
MessageBox.Show("Project Length Updating failied - check that all criteria have a correct entry");
}
//Close the connection to reduce resource usage and prevent other changes to database from other users (and this software)
Conn.Close();
}
else
{
//Is this just a new empty project
if (currProject.taskList.Count != 0)
{
MessageBox.Show("Project Length Updating failed - likely project does not exist");
Trace.WriteLine("ERROR UPDATING PROJECT HOURS");
}
}
}
public static void login(string username, string password)
{
/*
* This method checks to see if a user has a profile in the database, and saves this data within code in order to generate proper task/project lists
* The email is also saved locally
*/
if (LoginHandler.loggedIn)
{
MessageBox.Show("Already logged in - please logout first");
}
else
{
//Return number of records with the username and password fields (1 for profile exist/correct combination, 0 for erroneous input or no profile saved)
string sql = "SELECT COUNT(*) FROM tblUser WHERE Username='******' and Password='******'";
command = new MySqlCommand(sql, Conn);
Conn.Open();
int result = Convert.ToInt32(command.ExecuteScalar());
if (result > 0)
{
//Save the profile information locally
MessageBox.Show("Login Successful");
LoginHandler.username = username;
LoginHandler.password = password;
LoginHandler.loggedIn = true;
LoginHandler.shouldSendEMail = TimeHandler.shouldSendEmail();
//Close database connection
Conn.Close();
//Select their email (based on username) and save it
sql = "SELECT EMail FROM tblUser WHERE Username='******'";
command = new MySqlCommand(sql, Conn);
Conn.Open();
MySqlDataReader newReader = command.ExecuteReader();
//If there is information stored in the connection buffer, save the email
while (newReader.Read())
{
LoginHandler.email = newReader["EMail"].ToString();
}
Conn.Close();
}
else
{
//Output incorrect entry
MessageBox.Show("Incorrect Credentials - Try Again");
LoginHandler.loggedIn = false;
Conn.Close();
}
}
}
public static void loadProjects(string username)
{
//This SQL/connection retruns all relevant project information(s) for the currently logged-in user
string SQL = "SELECT ProjectID, ProjectName, ProjectDescription, DateStarted, DateDue, ProjectLength FROM tblProject WHERE Username='******'";
MySqlCommand command = new MySqlCommand(SQL, Conn);
//If a connection is already opened (error check) close it first
if (Conn.State == System.Data.ConnectionState.Open)
{
Conn.Close();
}
Conn.Open();
//Create reader object that 'scans' the SQL return
MySqlDataReader newReader = command.ExecuteReader();
//While there is information stored in the connection buffer, add the projects (as data points/bars) to the chart
while (newReader.Read())
{
string xPoint = newReader["ProjectName"].ToString();
double yPoint = Convert.ToDouble(newReader["ProjectLength"].ToString());
string projectDescription = newReader["ProjectDescription"].ToString();
DateTime dateStarted = Convert.ToDateTime(newReader["DateStarted"].ToString());
DateTime dateDue = Convert.ToDateTime(newReader["DateDue"].ToString());
double projectLength = yPoint;
int projectID = Convert.ToInt32(newReader["ProjectID"]);
//Create a new project from the raw data output from the database, add it to the project list in the model-view
ModelView.projectList.Add(new Project(xPoint, yPoint, projectDescription, dateStarted, dateDue, projectLength, projectID));
}
Conn.Close();
//Once all projects have been loaded update their lengths and generate their tasks
foreach (Project project in ModelView.projectList)
{
int projectID = project.ID;
loadNewTasks(project);
Trace.WriteLine("Updating project length: " + projectID.ToString());
updateProjectLength(projectID);
//Check if any of the tasks in the current iteration project have been completed
foreach (Task task in project.taskList)
{
Trace.WriteLine("Checking task '" + task.TaskName + "' for completion...");
TimeHandler.checkTaskComplete(task);
}
}
}
public static void addNewTask(string TaskName, string TaskDescription, double TaskLength, int ProjectID, int Priority, DateTime StartDate)
{
/*
* This method acts similarly to the one above, however is used if a task with priotiy 1 needs to be added (with a start date)
*/
string SQL = "INSERT INTO tblTask(TaskName, TaskDescription, TaskLength, ProjectID, Priority, StartDate) VALUES(@TN,@TD,@TL,@PID,@PR,@SD)";
//Populate command object
command = new MySqlCommand(SQL, Conn);
try
{
//Add SQL param values
command.Parameters.AddWithValue("@TN", TaskName);
command.Parameters.AddWithValue("@TD", TaskDescription);
command.Parameters.AddWithValue("@TL", TaskLength);
command.Parameters.AddWithValue("@PID", ProjectID);
command.Parameters.AddWithValue("@PR", Priority);
command.Parameters.AddWithValue("@SD", TimeHandler.ConvertToAccessDateTime(StartDate));
}
catch (Exception)
{
MessageBox.Show("Failed to bind project information to SQL variables");
}
/*Open the connection and execute the insert-into command. This method will return the number of rows that have been affected (in this case, added)
* therefore, if affectedRows>0, insertion has been completed successfully*/
Conn.Open();
if (command.ExecuteNonQuery() > 0)
{
MessageBox.Show("Task added successfully");
}
else
{
//If the code gets this far, it means that there has not been an in-code syntax error, instead a data entry error (v.likely to be user-related)
MessageBox.Show("Task addition failed - check that all task criteria have a correct entry");
}
//Close the connection to reduce resource usage and prevent other changes to database from other users (and this software)
Conn.Close();
//Update project length
updateProjectLength(ProjectID);
}
public static void editProject(string ProjectName, string ProjectDescription, DateTime StartDate, Project ProjectToEdit)
{
/*
* Takes new project information and syncs these changes to the database
*/
string SQL = "UPDATE tblProject SET ProjectName = @PN, ProjectDescription = @PDN, DateStarted = @SD WHERE ProjectID = @PID";
//Create command
command = new MySqlCommand(SQL, Conn);
try
{
//Add param values
command.Parameters.AddWithValue("@PN", ProjectName);
command.Parameters.AddWithValue("@PDN", ProjectDescription);
command.Parameters.AddWithValue("@SD", TimeHandler.ConvertToAccessDateTime(StartDate));
command.Parameters.AddWithValue("@PID", ProjectToEdit.ID);
}
catch (Exception)
{
MessageBox.Show("Failed to bind information to SQL variables");
}
/*Open the connection and execute the insert-into command. This method will return the number of rows that have been affected (in this case, added)
* therefore, if affectedRows>0, insertion has been completed successfully*/
Conn.Open();
if (command.ExecuteNonQuery() > 0)
{
MessageBox.Show("Project information updated");
}
else
{
//If the code gets this far, it means that there has not been an in-code syntax error, instead a data entry error (v.likely to be user-related)
MessageBox.Show("Project edit failed - check that all criteria have a correct entry");
}
//Close the connection to reduce resource usage and prevent other changes to database from other users (and this software)
Conn.Close();
}
public static void addNewUser(string Username, string Password, string FirstName, string Surname, DateTime DateOfBirth, string EMail)
{
//Generate SQL (with params) and populate the commmand object
string SQL = "INSERT INTO tbluser(Username, Password, FirstName, Surname, DateOfBirth, EMail) VALUES(@UN, @PW, @FN, @SN, @DOB, @EM)";
command = new MySqlCommand(SQL, Conn);
try
{
//Add the paramater values to the command objects
command.Parameters.AddWithValue("@UN", Username);
command.Parameters.AddWithValue("@PW", Password);
command.Parameters.AddWithValue("@FN", FirstName);
command.Parameters.AddWithValue("@SN", Surname);
command.Parameters.AddWithValue("@DOB", TimeHandler.ConvertToAccessDateTime(DateOfBirth));
command.Parameters.AddWithValue("@EM", EMail);
}
catch (Exception)
{
MessageBox.Show("Failed to bind user information to SQL variables");
}
/*Open the connection and execute the insert-into command. This method will return the number of rows that have been affected (in this case, added)
* therefore, if affectedRows>0, insertion has been completed successfully*/
Conn.Open();
if (command.ExecuteNonQuery() > 0)
{
MessageBox.Show("User Registered Successfuly");
}
else
{
//If the code gets this far, it means that there has not been an in-code syntax error, instead a data entry error (v.likely to be user-related)
MessageBox.Show("User registration failed - check that all project criteria have a correct entry");
}
//Close the connection to reduce resource usage and prevent other changes to database from other users (and this software)
Conn.Close();
}
public static void addNewProject(string Username, string ProjectName, string ProjectDescription, DateTime DateStarted, DateTime prjDueDate)
{
//Generate SQL (with params) and populate the commmand object
string SQL = "INSERT INTO tblproject(Username,ProjectName,ProjectDescription,ProjectLength,DateStarted,DateDue) VALUES(@UN,@PN,@PD,@PL,@DS,@DD)";
command = new MySqlCommand(SQL, Conn);
try
{
//Add the paramater values to the command objects
command.Parameters.AddWithValue("@UN", Username);
command.Parameters.AddWithValue("@PN", ProjectName);
command.Parameters.AddWithValue("@PD", ProjectDescription);
command.Parameters.AddWithValue("@PL", 1);
command.Parameters.AddWithValue("@DS", TimeHandler.ConvertToAccessDateTime(DateStarted));
command.Parameters.AddWithValue("@DD", TimeHandler.ConvertToAccessDateTime(prjDueDate));
}
catch (Exception)
{
MessageBox.Show("Failed to bind project information to SQL variables");
}
/*Open the connection and execute the insert-into command. This method will return the number of rows that have been affected (in this case, added)
* therefore, if affectedRows>0, insertion has been completed successfully*/
Conn.Open();
if (command.ExecuteNonQuery() > 0)
{
MessageBox.Show("Project Added Successfuly");
}
else
{
//If the code gets this far, it means that there has not been an in-code syntax error, instead a data entry error (v.likely to be user-related)
MessageBox.Show("New project failed - check that all project criteria have a correct entry");
}
//Close the connection to reduce resource usage and prevent other changes to database from other users (and this software)
Conn.Close();
}
public static void generateTaskCharts(string xAxisTitle, string yAxisTitle)
{
/*
* This method generates a chart for each task within a project
* NOTE: We have to be a bit 'hacky' here and convert the arraylist to an array so we can manipulate objects at run time
*/
Array projList = projectList.ToArray();
//Update starting dates and charts
foreach (Project p in projList)
{
taskChartGenerator("Task Name", "Hours Left", p);
foreach (Task task in p.taskList)
{
DatabaseHandler.updateTaskStartTime(task, TimeHandler.estimatedTaskStartDateUpdated(task));
}
}
//Convert the array and reinstantiate the project list
projectList = new ArrayList(projList);
}
private void btnSaveTask_Click(object sender, RoutedEventArgs e)
{
/*
* This method identifies the project being used as well as other task data, and calls the database handler to add them to the database
*/
int projectID = 0;
int priority = 0;
//Identify what project is being used and update task priorities
foreach (Project project in ModelView.projectList)
{
if (project.x == cmbProjectSelector.SelectedValue.ToString())
{
projectID = project.ID;
priority = ModelView.nextTaskPriority(project);
}
}
string taskName = txtTaskName.Text.ToString();
string taskDescription = txtTaskDescription.Text.ToString();
double taskLength = Convert.ToDouble(txtTaskLength.Text.ToString());
//Is this the first task for this project? If so add the start date
if (priority == 1)
{
DatabaseHandler.addNewTask(taskName, taskDescription, taskLength, projectID, priority, DateTime.Now);
}
else
{
DatabaseHandler.addNewTask(taskName, taskDescription, taskLength, projectID, priority, TimeHandler.estimatedTaskStartDate(projectID));
}
}
