//////////////////////////////////////////////////
// IGNORE BEYOND THIS POINT BUT DO NOT DELETE //
//////////////////////////////////////////////////
/* //DO NOT DELETE
* //Method which returns all project records which have a status code of 0, ie need to be confirmed by a researcher.
* public DataTable getResearcherUnconfirmedProjects()
* {
* //Connects to database
* ConnectionClass.OpenConnection();
*
* //Declare new mysql command using connection to returns all projects which need
*
* //MySqlCommand cmd = ConnectionClass.con.CreateCommand();
* //cmd.CommandType = CommandType.Text;
* //cmd.CommandText = "SELECT * FROM projects WHERE StatusCode = '0'";
* //cmd.ExecuteNonQuery();
*
* String query = "SELECT * FROM projects WHERE StatusCode = '0'";
*
* //Create datatable for results to be read into
* DataTable dt = new DataTable();
* MySqlDataAdapter da = new MySqlDataAdapter(query, ConnectionClass.con);
* //Fill the datatable with the results from the MYSQL command using data adapter
* da.Fill(dt);
* ConnectionClass.CloseConnection();
* //If the datatable is empty, ie there are no Projects which require a researcher to confirm
* if (dt == null)
* {
* return null;
* }
* //else if there are projects to be confirmed
* else
* {
* return dt;
* }
* }
*/
/*
* 1. Using ProjectID researcher wants to sign, check the database to see if it has been confirmed or not already.
* 2. If it has not been signed yet, sign it
* */
//DO NOT DELETE
//Function which takes in a ProjectID for the project to be confirmed, changing its status code to 1 and its Researcher signed value to userID
public void researcherConfirmation1(int projectID, int userID)
{
//Connects to database
ConnectionClass.OpenConnection();
DataTable dt = viewProjectInfo(projectID);
int researcherSigned = 0;
int statusCode = 0;
foreach (DataRow dr in dt.Rows)
{
researcherSigned = Convert.ToInt32(dr["ResearcherSigned"]);
statusCode = Convert.ToInt32(dr["StatusCode"]);
Console.WriteLine(researcherSigned + " " + statusCode);
}
if (researcherSigned == 0 && statusCode == 0)
{
//Declare new mysql command using connection which sets user specified project's ResearcherSigned and StatusCode values to userID
ConnectionClass.OpenConnection();
MySqlCommand cmd = ConnectionClass.con.CreateCommand();
cmd.CommandText = "UPDATE projects SET ResearcherSigned = '" + userID + "', StatusCode = '1' WHERE ProjectID = '" + projectID + "'";
cmd.ExecuteNonQuery();
ConnectionClass.CloseConnection();
}
ConnectionClass.CloseConnection();
}
public string ValidateLoginDetails(string StaffID, string pwd)
{
//assign stored procedure
string storedProc = "checkLogin;";
DataTable dt = new DataTable(); //this is creating a virtual table
ConnectionClass.OpenConnection();
//open connection
MySqlConnection connection = new MySqlConnection(ConnectionClass.ConnectionString);
MySqlDataAdapter sda = new MySqlDataAdapter(storedProc, connection);
//assign parameters
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.AddWithValue("?sID", StaffID);
sda.SelectCommand.Parameters.AddWithValue("?pwd", pwd);
// in above line the program is selecting the whole data from table and the matching it with the user name and password provided by user.
sda.Fill(dt);
try //when data table has something in it
{
Console.WriteLine("found!");
ConnectionClass.CloseConnection();
string uid = dt.Rows[0][0].ToString(); //store the user id as a string
return(uid); //return string
}
catch (Exception) //when data table is empty
{
return(null);
}
}
/// <summary>
/// Deletes any existing files in the project with the same name then reads the bytes from the pasted stream and inputs them into the database.
/// </summary>
/// <param name="id">Project ID that the file belongs to</param>
/// <param name="stream">Stream of the file you are uploading</param>
/// <param name="fileName">File name to display to the user</param>
/// <returns>Number of rows effected</returns>
public int UploadFile(int id, Stream stream, string fileName)
{
//Convert file to bytes
byte[] file;
using (var reader = new BinaryReader(stream))
{
file = reader.ReadBytes((int)stream.Length);
}
ConnectionClass.OpenConnection();
MySqlCommand comm = new MySqlCommand("deleteFileWhereProjectIDAndFileName", ConnectionClass.con);
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.Parameters.Add(new MySqlParameter("?id", id));
comm.Parameters.Add(new MySqlParameter("?fileName", fileName));
comm.ExecuteNonQuery();
//Insert bytes into the storedfiles table
comm = new MySqlCommand("createNewFile", ConnectionClass.con);
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.Parameters.AddWithValue("?id", id);
comm.Parameters.AddWithValue("?fileName", fileName);
comm.Parameters.Add("?fileData", MySqlDbType.LongBlob, file.Length).Value = file;
int i = comm.ExecuteNonQuery();
ConnectionClass.CloseConnection();
return(i);
}
//Method which returns a datatable containing all the related files returned based on the projectID passed to it.
public DataTable viewProjectFiles(int input)
{
//Connects to database
ConnectionClass.OpenConnection();
//Declare new mysql command using stored procedure.
MySqlCommand command = new MySqlCommand("viewProjectFiles", ConnectionClass.con);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new MySqlParameter("@id", input));
//Create datatable for results to be read into
DataTable dt = new DataTable();
//Adaptor to read results into the datatable
MySqlDataAdapter sda = new MySqlDataAdapter(command);
//Fill the datatable with the results from the MYSQL command using data adapter
sda.Fill(dt);
//Close Connection
ConnectionClass.CloseConnection();
return(dt);
}
/*
* Below is potential sign/reject methods refactored. Reject doesn't require switch statement as it
* doesn't matter who rejects it, all signed fields are reset to 0/default.
* All reject stored procedures should simply use 'deanRejectProject'.
*
*
* /// <summary>
* /// Sign project switch statement to run correct signing stored procedure.
* /// </summary>
* /// <param name="projectID"></param>
* /// <param name="staffID"></param>
* /// <param name="position"></param>
* public void SignChoice(int projectID, string staffID, int position)
* {
* switch (position)
* {
* case 0:
* Sign(projectID, staffID, "researcherSignProject;");
* break;
* case 1:
* Sign(projectID, staffID, "RISSign;");
* break;
* case 2:
* Sign(projectID, staffID, "AssocDeanSign;");
* break;
* case 3:
* Sign(projectID, staffID, "DeanSign;");
* break;
* }
* }
*
* /// <summary>
* /// Sign selected project with staffID based on user's job position.
* /// </summary>
* /// <param name="projectID"></param>
* /// <param name="staffID"></param>
* /// <param name="proc"></param>
* /// <returns>Number of records affected</returns>
* private int Sign(int projectID, string staffID, string proc)
* {
* //assign stored procedure
* string storedProc = proc;
* //open connection
* MySqlConnection connection = new MySqlConnection(ConnectionClass.ConnectionString);
* connection.Open();
* //define stored procedure
* MySqlCommand cmd = new MySqlCommand(storedProc, connection);
* cmd.CommandType = System.Data.CommandType.StoredProcedure;
* //assign parameters
* cmd.Parameters.Add(new MySqlParameter("?pID", projectID));
* cmd.Parameters.Add(new MySqlParameter("?sID", staffID));
* //execute procedure
* int i = cmd.ExecuteNonQuery();
* //close connection and return number of rows affected (should be 1)
* connection.Close();
* return i;
* }
*
* /// <summary>
* /// Rejects selected project.
* /// </summary>
* /// <param name="projectID"></param>
* /// <param name="proc"></param>
* /// <returns>Number of records affected</returns>
* private int Reject(int projectID)
* {
* //assign stored procedure
* string storedProc = "rejectProject;";
* //open connection
* MySqlConnection connection = new MySqlConnection(ConnectionClass.ConnectionString);
* connection.Open();
* //define stored procedure
* MySqlCommand cmd = new MySqlCommand(storedProc, connection);
* cmd.CommandType = System.Data.CommandType.StoredProcedure;
* //assign parameters
* cmd.Parameters.Add(new MySqlParameter("?pID", projectID));
* //execute procedure
* int i = cmd.ExecuteNonQuery();
* //close connection and return number of rows affected (should be 1)
* connection.Close();
* return i;
* }*/
public bool DeleteProject(int projectID)
{
try
{
//assign stored procedure
string storedProc = "DeletePairingAndProject;";
//open connection
MySqlConnection connection = new MySqlConnection(ConnectionClass.ConnectionString);
connection.Open();
//define stored procedure
MySqlCommand cmd = new MySqlCommand(storedProc, connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//assign parameters
cmd.Parameters.Add(new MySqlParameter("?pID", projectID));
//execute procedure
cmd.ExecuteNonQuery();
ConnectionClass.CloseConnection();
return(true);//file deleted
}
catch (Exception)
{
ConnectionClass.CloseConnection();
return(false);
throw;
}
}
//DB INFO:
//Hostname: silva.computing.dundee.ac.uk
//Port: 3306
//Username: 17agileteam5
//Password: 7485.at5.5847
//DONE
//Method which returns a datatable containing all the information returned for a project based on the projectID passed to it.
public DataTable viewProjectInfo(int input)
{
//Connects to database
ConnectionClass.OpenConnection();
//Declare new mysql command using stored procedure.
MySqlCommand command = new MySqlCommand("viewProjectInfo", ConnectionClass.con);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new MySqlParameter("@id", input));
//Create datatable for results to be read into
DataTable dt = new DataTable();
//Adaptor to read results into the datatable
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
//Fill the datatable with the results from the MYSQL command using data adapter
adapter.Fill(dt);
//Close Connection
ConnectionClass.CloseConnection();
//If the datatable is empty, ie the project row does not exist in the database, then return null.
if (dt == null)
{
return(null);
}
//else if the project record does exist, return this datatable.
else
{
return(dt);
}
}
/// <summary>
/// Takes a file primary key (fileID) and deletes all rows from storedfiles with that key.
/// </summary>
/// <param name="fileID">Database primary key that you want to delete</param>
/// <returns>Number of rows effected</returns>
public int DeleteFile(int fileID)
{
//Deletes all rows with primary key = fileID
ConnectionClass.OpenConnection();
MySqlCommand comm = new MySqlCommand("deleteFile", ConnectionClass.con);
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.Parameters.Add(new MySqlParameter("?id", fileID));
int i = comm.ExecuteNonQuery();
ConnectionClass.CloseConnection();
return(i);
}
public void updateBot(int id, string p)
{
ConnectionClass.OpenConnection();
MySqlCommand command = new MySqlCommand("updateProfileBot", ConnectionClass.con);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new MySqlParameter("@u", id));
command.Parameters.Add(new MySqlParameter("@p", p));
command.ExecuteNonQuery();
ConnectionClass.CloseConnection();
}
/// <summary>
///
/// </summary>
/// <param name="title"></param>
/// <param name="userID"></param>
/// <returns></returns>
public String CreateProject(string title, int userID)
{
MySqlCommand cmd;
ConnectionClass.OpenConnection();
cmd = ConnectionClass.con.CreateCommand(); //New Connection object
try
{
//FIRST INSERT THE NEW PROJECT INTO THE PROJECTS TABLE
//SQL Query
cmd.CommandText = "INSERT INTO projects(Title)VALUES(@title);SELECT LAST_INSERT_ID();";
// Populate SQl query values
cmd.Parameters.AddWithValue("@title", title);
// Execute Query
MySqlDataReader reader = cmd.ExecuteReader();
String pID = "";
while (reader.Read())
{
pID = reader.GetString("LAST_INSERT_ID()");
}
reader.Close();
//FOLLOW BY INSERTING THE PROJECT AND ID INTO LINK
//SQL Query
cmd.CommandText = "INSERT INTO userprojectpairing(LoginDetails_UserID,Projects_ProjectID)VALUES(@userID,@projID)";
// Populate SQl query values
cmd.Parameters.AddWithValue("@userID", userID);
cmd.Parameters.AddWithValue("@projID", pID);
// Execute Query
cmd.ExecuteNonQuery();
// Close Connection
ConnectionClass.CloseConnection();
return(pID);
}
catch (Exception)
{
ConnectionClass.CloseConnection();
return(null);
throw;
}
}
public DataTable ViewSignedProjects(string id)
{
ConnectionClass.OpenConnection();
MySqlConnection connection = new MySqlConnection(ConnectionClass.ConnectionString);
MySqlDataAdapter sda = new MySqlDataAdapter("viewSignedProjects", connection);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.AddWithValue("?sID", id);
DataTable dt = new DataTable();
sda.Fill(dt);
ConnectionClass.CloseConnection();
return(dt);
}
public void updateTop(int id, string f, string l, string e)
{
ConnectionClass.OpenConnection();
MySqlCommand command = new MySqlCommand("updateProfileTop", ConnectionClass.con);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new MySqlParameter("@u", id));
command.Parameters.Add(new MySqlParameter("@f", f));
command.Parameters.Add(new MySqlParameter("@l", l));
command.Parameters.Add(new MySqlParameter("@e", e));
command.ExecuteNonQuery();
ConnectionClass.CloseConnection();
}
public DataTable getUserInfo(int inputID)
{
//Connects to database
ConnectionClass.OpenConnection();
//Declare new mysql command using stored procedure.
MySqlCommand command = new MySqlCommand("returnProfile", ConnectionClass.con);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new MySqlParameter("@id", inputID));
//Create datatable for results to be read into
DataTable dt = new DataTable();
//Adaptor to read results into the datatable
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
//Fill the datatable with the results from the MYSQL command using data adapter
adapter.Fill(dt);
//Close Connection
ConnectionClass.CloseConnection();
/*
* foreach (DataRow dataRow in dt.Rows)
* {
* foreach (var item in dataRow.ItemArray)
* {
* Console.WriteLine(item);
* }
*
* }
*/
//If the datatable is empty, ie the project row does not exist in the database, then return null.
if (dt == null)
{
return(null);
}
//else if the project record does exist, return this datatable.
else
{
return(dt);
}
}
/// <summary>
/// Downloads all files with the passed project ID into the path location passed to it
/// </summary>
/// <param name="id">Project ID to fetch files from</param>
/// <param name="path">Path to download folder</param>
/// <returns>List of paths to the files downloaded</returns>
public List <String> DownloadAllFiles(int id, string path)
{
List <String> fileList = new List <string>();
ConnectionClass.OpenConnection();
MySqlCommand comm = new MySqlCommand("selectAllFilesWithProjectID", ConnectionClass.con);
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@id", id);
using (MySqlDataReader sqlQueryResult = comm.ExecuteReader())
{
if (sqlQueryResult.HasRows)
{
//Loop for all files
while (sqlQueryResult != null && sqlQueryResult.Read())
{
byte[] blob = new Byte[(sqlQueryResult.GetBytes(sqlQueryResult.GetOrdinal("FileData"), 0, null, 0, int.MaxValue))];
sqlQueryResult.GetBytes(sqlQueryResult.GetOrdinal("FileData"), 0, blob, 0, blob.Length);
//Manage file name duplication filename(count).filetype
String fileName = sqlQueryResult["FileName"].ToString();
String fullPath = System.IO.Path.Combine(path, fileName);
int count = 1;
while (File.Exists(fullPath))
{
string[] split = fileName.Split('.');
fullPath = System.IO.Path.Combine(path, split[0] + "(" + count + ")." + split[1]);
count++;
}
using (FileStream fs = new FileStream(fullPath, FileMode.Create, FileAccess.Write))
{
fs.Write(blob, 0, blob.Length);
fileList.Add(fullPath);
}
}
}
}
ConnectionClass.CloseConnection();
return(fileList);
}
/// <summary>
/// Gets the email of a user with the passed id
/// </summary>
/// <param name="id">The database user primary key</param>
/// <returns>The email that matches with the userID</returns>
public String getUserEmail(int id)
{
String email = "";
ConnectionClass.OpenConnection();
MySqlCommand comm = new MySqlCommand("getEmailOfUser", ConnectionClass.con);
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@id", id);
using (MySqlDataReader sqlQueryResult = comm.ExecuteReader())
{
if (sqlQueryResult.HasRows)
{
while (sqlQueryResult != null && sqlQueryResult.Read())
{
email = sqlQueryResult["Email"].ToString();
}
}
}
ConnectionClass.CloseConnection();
return(email);
}
public int GetRISSignID(int projectID)
{
int RISID = 0;
ConnectionClass.OpenConnection();
MySqlCommand comm = new MySqlCommand("GetRISSignID", ConnectionClass.con);
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@pID", projectID);
using (MySqlDataReader sqlQueryResult = comm.ExecuteReader())
{
if (sqlQueryResult.HasRows)
{
while (sqlQueryResult != null && sqlQueryResult.Read())
{
RISID = (int)sqlQueryResult["Title"];
}
}
}
ConnectionClass.CloseConnection();
return(RISID);
}
public string GetProjectName(int projectID)
{
string projectName = "";
ConnectionClass.OpenConnection();
MySqlCommand comm = new MySqlCommand("GetProjectName", ConnectionClass.con);
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@pID", projectID);
using (MySqlDataReader sqlQueryResult = comm.ExecuteReader())
{
if (sqlQueryResult.HasRows)
{
while (sqlQueryResult != null && sqlQueryResult.Read())
{
projectName = (string)sqlQueryResult["Title"];
}
}
}
ConnectionClass.CloseConnection();
return(projectName);
}
public int GetProjectOwner(int pID)
{
int owner = 0;
ConnectionClass.OpenConnection();
MySqlCommand comm = new MySqlCommand("GetProjectOwner", ConnectionClass.con);
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@id", pID);
using (MySqlDataReader sqlQueryResult = comm.ExecuteReader())
{
if (sqlQueryResult.HasRows)
{
while (sqlQueryResult != null && sqlQueryResult.Read())
{
owner = (int)sqlQueryResult["LoginDetails_UserID"];
}
}
}
ConnectionClass.CloseConnection();
return(owner);
}
public byte[] GetFile(int id)
{
ConnectionClass.OpenConnection();
MySqlCommand comm = new MySqlCommand("selectFileWithFileID", ConnectionClass.con);
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@id", id);
using (MySqlDataReader sqlQueryResult = comm.ExecuteReader())
{
if (sqlQueryResult.HasRows)
{
//Loop for all files
while (sqlQueryResult != null && sqlQueryResult.Read())
{
byte[] blob = new Byte[(sqlQueryResult.GetBytes(sqlQueryResult.GetOrdinal("FileData"), 0, null, 0, int.MaxValue))];
sqlQueryResult.GetBytes(sqlQueryResult.GetOrdinal("FileData"), 0, blob, 0, blob.Length);
ConnectionClass.CloseConnection();
return(blob);
}
}
}
return(null);
}
