/// <summary>
/// Takes the UserModel and constructs a string that is presented via a MessageBox for
/// review of entered details. Returns which button was clicked.
/// </summary>
/// <param name="mdl_User"></param>
/// <returns></returns>
private DialogResult confirmationBox(mdl_Project mdl_Project)
{
// tabs and newlines align and break up displayed information for ease of review
string reviewProjectDetails = $"Create new project with these details?" + Environment.NewLine + Environment.NewLine;
reviewProjectDetails += $"DATRAG:\t\t\t{mdl_Project.DATRAG_Desc}" + Environment.NewLine;
reviewProjectDetails += $"Project Name:\t\t{mdl_Project.ProjectName}" + Environment.NewLine + Environment.NewLine;
reviewProjectDetails += $"DSPT:\t\t\t{mdl_Project.DSPT}" + Environment.NewLine;
reviewProjectDetails += $"ISO27001:\t\t\t{mdl_Project.ISO27001}" + Environment.NewLine + Environment.NewLine;
reviewProjectDetails += $"Projected Start Date:\t\t{mdl_Project.ProjectedStartDate}" + Environment.NewLine;
reviewProjectDetails += $"Projected End Date:\t\t{mdl_Project.ProjectedEndDate}" + Environment.NewLine;
reviewProjectDetails += $"Start Date:\t\t\t{mdl_Project.StartDate}" + Environment.NewLine;
reviewProjectDetails += $"End Date:\t\t\t{mdl_Project.EndDate}" + Environment.NewLine + Environment.NewLine;
reviewProjectDetails += $"LASER:\t\t\t{mdl_Project.LASER}" + Environment.NewLine;
reviewProjectDetails += $"IRC:\t\t\t{mdl_Project.IRC}" + Environment.NewLine;
reviewProjectDetails += $"SEED:\t\t\t{mdl_Project.SEED}" + Environment.NewLine + Environment.NewLine;
reviewProjectDetails += $"Portfolio Number:\t\t{mdl_Project.PortfolioNumber}" + Environment.NewLine;
reviewProjectDetails += $"Stage:\t\t\t{mdl_Project.Stage_Desc}" + Environment.NewLine;
reviewProjectDetails += $"Classification:\t\t{mdl_Project.Classification_Desc}" + Environment.NewLine + Environment.NewLine;
reviewProjectDetails += $"PI:\t\t\t{mdl_Project.PI_Desc}" + Environment.NewLine;
reviewProjectDetails += $"Lead Applicant:\t\t{mdl_Project.LeadApplicant_Desc}" + Environment.NewLine;
reviewProjectDetails += $"Faculty:\t\t\t{mdl_Project.Faculty_Desc}" + Environment.NewLine + Environment.NewLine;
DialogResult confirm = MessageBox.Show(
text: reviewProjectDetails
, caption: "Confirmation"
, buttons: MessageBoxButtons.OKCancel);
return(confirm);
}
/// <summary>
/// Uses methods in User.cs to add Lead Applicant and PI to project research team if they're not already present.
/// </summary>
/// <param name="mdl_Project"></param>
private void insertResearchTeam(mdl_Project mdl_Project)
{
int? PI_UserNumber = mdl_Project.PI;
int? LA_UserNumber = mdl_Project.LeadApplicant;
string ProjectNumber = mdl_Project.ProjectNumber;
try
{
User Users = new User();
if (PI_UserNumber != null)
{
if (Users.checkUserProject((int)PI_UserNumber, ProjectNumber) == true)
{
Users.insertUserProject((int)PI_UserNumber, ProjectNumber);
}
}
if (LA_UserNumber != null)
{
if (Users.checkUserProject((int)LA_UserNumber, ProjectNumber) == true)
{
Users.insertUserProject((int)LA_UserNumber, ProjectNumber);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to insert Principal Investigator or Lead Applicant to project research team." + Environment.NewLine + ex.Message);
//throw;
}
}
/// <summary>
/// Method to assign current project values to project data model (mdl_CurrentProject).
/// Creates new Project class object and uses parameter pNumber with class DataSet (ds_Project) to
/// populate using method from Project class: getProject(pNumber, ds_Project).
/// </summary>
/// <param name="pNumber"></param>
private void fillCurrentProjectVariables(string pNumber)
{
try
{
//instantiate new Project type object that contains project methods
Project Projects = new Project();
//populate list of project details
mdl_CurrentProject = Projects.getProject(pNumber, ds_Project);
}
catch (Exception ex)
{
MessageBox.Show("Method fillCurrentProjectVariables of class frm_Projects has failed" + Environment.NewLine + Environment.NewLine + ex);
//throw;
}
}
/// <summary>
/// Checks if mandatory fields have an entry.
/// </summary>
/// <param name="mdl_Project"></param>
/// <returns>
/// 'true' if all fields are populated, 'false' and mesaagebox feedback if any are missing.
/// </returns>
public bool requiredFields(mdl_Project mdl_Project)
{
bool requiredFields = true;
if (string.IsNullOrWhiteSpace(mdl_Project.ProjectName))
{
MessageBox.Show("Please enter a Project Title.");
requiredFields = false;
}
if (mdl_Project.LeadApplicant == null || mdl_Project.LeadApplicant < 0)
{
MessageBox.Show("Please select a Lead Applicant.");
requiredFields = false;
}
return(requiredFields);
}
/// <summary>
/// Method to create a new project record using values entered in form.
/// Assigns control values to ProjectModel class variables,
/// checks dates are dates and passes them as parameters to
/// the insertProject(...) method of the Projects class.
/// </summary>
/// <returns>true on successful insert, false on fail</returns>
private bool insertNewProject()
{
//generate new pNumber and put it into class variable, can be used within
//this method/class but also to feed back to parent form.
pNumber = getNewProjectNumber();
mdl_Project mdl_Project = new mdl_Project();
//populate ProjectModel class variables with values held in form controls
mdl_Project.ProjectNumber = pNumber;
mdl_Project.ProjectName = tb_pNameValue.Text;
mdl_Project.PortfolioNumber = tb_PortfolioNo.Text;
mdl_Project.DSPT = chkb_DSPT.Checked;
mdl_Project.ISO27001 = chkb_ISO27001.Checked;
mdl_Project.LASER = chkb_LASER.Checked;
mdl_Project.IRC = chkb_IRC.Checked;
mdl_Project.SEED = chkb_SEED.Checked;
if (cb_pStage.SelectedIndex > -1)
{
mdl_Project.Stage = int.Parse(cb_pStage.SelectedValue.ToString());
mdl_Project.Stage_Desc = cb_pStage.Text;
}
if (cb_pClassification.SelectedIndex > -1)
{
mdl_Project.Classification = int.Parse(cb_pClassification.SelectedValue.ToString());
mdl_Project.Classification_Desc = cb_pClassification.Text;
}
if (cb_DATRAG.SelectedIndex > -1)
{
mdl_Project.DATRAG = int.Parse(cb_DATRAG.SelectedValue.ToString());
mdl_Project.DATRAG_Desc = cb_DATRAG.Text;
}
if (cb_LeadApplicant.SelectedIndex > -1)
{
mdl_Project.LeadApplicant = int.Parse(cb_LeadApplicant.SelectedValue.ToString());
mdl_Project.LeadApplicant_Desc = cb_LeadApplicant.Text;
}
if (cb_PI.SelectedIndex > -1)
{
mdl_Project.PI = int.Parse(cb_PI.SelectedValue.ToString());
mdl_Project.PI_Desc = cb_PI.Text;
}
if (cb_Faculty.SelectedIndex > -1)
{
mdl_Project.Faculty = int.Parse(cb_Faculty.SelectedValue.ToString());
mdl_Project.Faculty_Desc = cb_Faculty.Text;
}
//dates are fuckey
bool dateCheck = true;
if (dateCheck == true & mtb_ProjectedStartDateValue.Text != "" & mtb_ProjectedStartDateValue.Text != " / /")
{
try
{
mdl_Project.ProjectedStartDate = Convert.ToDateTime(mtb_ProjectedStartDateValue.Text);
}
catch (Exception)
{
MessageBox.Show("Please enter valid Projected Start Date");
dateCheck = false;
}
}
if (dateCheck == true & mtb_ProjectedEndDateValue.Text != "" & mtb_ProjectedEndDateValue.Text != " / /")
{
try
{
mdl_Project.ProjectedEndDate = Convert.ToDateTime(mtb_ProjectedEndDateValue.Text);
}
catch (Exception)
{
MessageBox.Show("Please enter valid Projected End Date");
dateCheck = false;
}
}
if (dateCheck == true & mtb_pStartDateValue.Text != "" & mtb_pStartDateValue.Text != " / /")
{
try
{
mdl_Project.StartDate = Convert.ToDateTime(mtb_pStartDateValue.Text);
}
catch (Exception)
{
MessageBox.Show("Please enter valid Start Date");
dateCheck = false;
}
}
if (dateCheck == true & mtb_pEndDateValue.Text != "" & mtb_pEndDateValue.Text != " / /")
{
try
{
mdl_Project.EndDate = Convert.ToDateTime(mtb_pEndDateValue.Text);
}
catch (Exception)
{
MessageBox.Show("Please enter valid End Date");
dateCheck = false;
}
}
//instantiate new Project type object that contains methods to update db
Project Projects = new Project();
//Check required fields have an entry
if (Projects.requiredFields(mdl_Project) == false)
{
return(false);
}
if (dateCheck == true)
{
if (confirmationBox(mdl_Project) == DialogResult.OK)
{
//insert new record
if (Projects.insertProject(mdl_Project) == true)
{
return(true);
}
}
}
return(false);
}
/// <summary>
/// Method to insert a new project record into dbo.tblProject.
/// Takes ProjectModel as parameter, adds class variables it contains to a SQL query string as
/// parameters then executes an insert.
/// </summary>
/// <param name="mdl_Project"></param>
/// <returns>
/// true on success, defaults to false
/// </returns>
public bool insertProject(mdl_Project mdl_Project)
{
bool success = false;
try
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
//generate the parameterised SQL query to insert new record
SqlCommand qryInsertProject = new SqlCommand();
qryInsertProject.Connection = conn;
qryInsertProject.CommandText = "insert into [dbo].[tblProject] "
+ "(ProjectNumber, ProjectName, PortfolioNumber, Stage, Classification, DATRAG, ProjectedStartDate" +
", ProjectedEndDate, StartDate, EndDate, [PI], LeadApplicant, Faculty, DSPT " +
", ISO27001, LIDA, LASER, IRC, SEED) "
+ "values "
+ "(@ProjectNumber, @ProjectName, @PortfolioNumber, @Stage, @Classification, @DATRAG, @ProjectedStartDate "
+ ", @ProjectedEndDate, @StartDate, @EndDate, @PI, @LeadApplicant, @Faculty, @DSPT "
+ ", @ISO27001, @LIDA, @LASER, @IRC, @SEED) ";
//assign the parameter values
qryInsertProject.Parameters.Add("@ProjectNumber", SqlDbType.VarChar, 5).Value = mdl_Project.ProjectNumber;
qryInsertProject.Parameters.Add("@ProjectName", SqlDbType.VarChar, 100).Value = mdl_Project.ProjectName;
SqlParameter param_PortfolioNumber = new SqlParameter("@PortfolioNumber", mdl_Project.PortfolioNumber == null ? (object)DBNull.Value : mdl_Project.PortfolioNumber);
param_PortfolioNumber.IsNullable = true;
qryInsertProject.Parameters.Add(param_PortfolioNumber);
SqlParameter param_Stage = new SqlParameter("@Stage", mdl_Project.Stage == null ? (object)DBNull.Value : mdl_Project.Stage);
param_Stage.IsNullable = true;
qryInsertProject.Parameters.Add(param_Stage);
SqlParameter param_Classification = new SqlParameter("@Classification", mdl_Project.Classification == null ? (object)DBNull.Value : mdl_Project.Classification);
param_Classification.IsNullable = true;
qryInsertProject.Parameters.Add(param_Classification);
SqlParameter param_DATRAG = new SqlParameter("@DATRAG", mdl_Project.DATRAG == null ? (object)DBNull.Value : mdl_Project.DATRAG);
param_DATRAG.IsNullable = true;
qryInsertProject.Parameters.Add(param_DATRAG);
SqlParameter param_ProjectedStartDate = new SqlParameter("@ProjectedStartDate", mdl_Project.ProjectedStartDate == null ? (object)DBNull.Value : mdl_Project.ProjectedStartDate);
param_ProjectedStartDate.IsNullable = true;
qryInsertProject.Parameters.Add(param_ProjectedStartDate);
SqlParameter param_ProjectedEndDate = new SqlParameter("@ProjectedEndDate", mdl_Project.ProjectedEndDate == null ? (object)DBNull.Value : mdl_Project.ProjectedEndDate);
param_ProjectedEndDate.IsNullable = true;
qryInsertProject.Parameters.Add(param_ProjectedEndDate);
SqlParameter param_StartDate = new SqlParameter("@StartDate", mdl_Project.StartDate == null ? (object)DBNull.Value : mdl_Project.StartDate);
param_StartDate.IsNullable = true;
qryInsertProject.Parameters.Add(param_StartDate);
SqlParameter param_EndDate = new SqlParameter("@EndDate", mdl_Project.EndDate == null ? (object)DBNull.Value : mdl_Project.EndDate);
param_EndDate.IsNullable = true;
qryInsertProject.Parameters.Add(param_EndDate);
SqlParameter param_LeadApplicant = new SqlParameter("@LeadApplicant", mdl_Project.LeadApplicant == null ? (object)DBNull.Value : mdl_Project.LeadApplicant);
param_LeadApplicant.IsNullable = true;
qryInsertProject.Parameters.Add(param_LeadApplicant);
SqlParameter param_PI = new SqlParameter("@PI", mdl_Project.PI == null ? (object)DBNull.Value : mdl_Project.PI);
param_PI.IsNullable = true;
qryInsertProject.Parameters.Add(param_PI);
SqlParameter param_Faculty = new SqlParameter("@Faculty", mdl_Project.Faculty == null ? (object)DBNull.Value : mdl_Project.Faculty);
param_Faculty.IsNullable = true;
qryInsertProject.Parameters.Add(param_Faculty);
qryInsertProject.Parameters.Add("@DSPT", SqlDbType.Bit).Value = mdl_Project.DSPT;
qryInsertProject.Parameters.Add("@ISO27001", SqlDbType.Bit).Value = mdl_Project.ISO27001;
qryInsertProject.Parameters.Add("@LIDA", SqlDbType.Bit).Value = mdl_Project.LIDA;
qryInsertProject.Parameters.Add("@LASER", SqlDbType.Bit).Value = mdl_Project.LASER;
qryInsertProject.Parameters.Add("@IRC", SqlDbType.Bit).Value = mdl_Project.IRC;
qryInsertProject.Parameters.Add("@SEED", SqlDbType.Bit).Value = mdl_Project.SEED;
//open connection to database, run query and close connection
conn.Open();
qryInsertProject.ExecuteNonQuery();
//Add new PI and Lead Applicant to project research team
insertResearchTeam(mdl_Project);
MessageBox.Show($"Project details updated for {mdl_Project.ProjectNumber}");
success = true;
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to insert new project record" + Environment.NewLine + ex.Message);
//throw;
}
return(success);
}
/// <summary>
/// Method to populate ProjectModel with latest single record.
/// Uses parameter pNumber to query Project DataSet (passed as parameter ds_Project), assigns
/// values to the returned ProjectModel class variables.
/// </summary>
/// <param name="pNumber"></param>
/// <param name="ds_Project"></param>
/// <returns>
/// Populated ProjectModel
/// </returns>
public mdl_Project getProject(string pNumber, DataSet ds_Project)
{
mdl_Project mdl_Project = new mdl_Project();
//if no records found, try will fail at "DataRow pRow = pRows[i];" and go to catch
try
{
DataRow[] pRows = ds_Project.Tables["tblProjects"].Select($"ProjectNumber = '{pNumber}'");
//there's always a small a chance a project might have multiple records where ValidTo is null
//DataSet (ds_prj) is populated ordered by pNumber and then pID so largest (last added) pID for each project is last
//feed back to user if more than one 'current' project record
if (pRows.Count() > 1)
{
MessageBox.Show("More than one current record found for this project, showing last only. Please contact a system administrator.");
}
//active row (pRow) filled with last row from pRows
int i = pRows.Count() - 1;
DataRow pRow = pRows[i];
//populate DataRow to output with values from pRow
mdl_Project.pID = (int)pRow["pID"];
mdl_Project.ProjectNumber = pRow["ProjectNumber"].ToString();
mdl_Project.ProjectName = pRow["ProjectName"].ToString();
if (pRow["PortfolioNumber"].ToString().Length > 0)
{
mdl_Project.PortfolioNumber = pRow["PortfolioNumber"].ToString();
}
if (pRow["Stage"].ToString().Length > 0)
{
mdl_Project.Stage = (int?)pRow["Stage"];
}
if (pRow["Classification"].ToString().Length > 0)
{
mdl_Project.Classification = (int?)pRow["Classification"];
}
if (pRow["DATRAG"].ToString().Length > 0)
{
mdl_Project.DATRAG = (int?)pRow["DATRAG"];
}
if (pRow["ProjectedStartDate"].ToString().Length > 0)
{
mdl_Project.ProjectedStartDate = (DateTime)pRow["ProjectedStartDate"];
}
if (pRow["ProjectedEndDate"].ToString().Length > 0)
{
mdl_Project.ProjectedEndDate = (DateTime)pRow["ProjectedEndDate"];
}
if (pRow["StartDate"].ToString().Length > 0)
{
mdl_Project.StartDate = (DateTime)pRow["StartDate"];
}
if (pRow["EndDate"].ToString().Length > 0)
{
mdl_Project.EndDate = (DateTime)pRow["EndDate"];
}
if (pRow["PI"].ToString().Length > 0)
{
mdl_Project.PI = (int?)pRow["PI"];
}
if (pRow["LeadApplicant"].ToString().Length > 0)
{
mdl_Project.LeadApplicant = (int?)pRow["LeadApplicant"];
}
if (pRow["Faculty"].ToString().Length > 0)
{
mdl_Project.Faculty = (int?)pRow["Faculty"];
}
mdl_Project.LIDA = (bool)pRow["LIDA"];
mdl_Project.DSPT = (bool)pRow["DSPT"];
mdl_Project.ISO27001 = (bool)pRow["ISO27001"];
mdl_Project.LASER = (bool)pRow["LASER"];
mdl_Project.IRC = (bool)pRow["IRC"];
mdl_Project.SEED = (bool)pRow["SEED"];
}
catch (Exception ex)
{
MessageBox.Show("Failed to load project details" + Environment.NewLine + Environment.NewLine + ex.Message);
}
return(mdl_Project);
}
/// <summary>
/// Method to update project details in SQL Server database.
/// Form control values assigned to ProjectModel while checking dates are valid, checks for
/// mandatory fields, compares with current record values (no change no update) and primary
/// key (not latest record no update).
/// Updates record via insert & logical delete before refreshing DataSet and form controls.
/// </summary>
/// <param name="pNumber"></param>
private bool updateProject(string pNumber)
{
bool success = false;
mdl_Project mdl_NewProject = new mdl_Project();
mdl_NewProject.ProjectNumber = pNumber;
mdl_NewProject.ProjectName = tb_pNameValue.Text;
if (tb_PortfolioNo.Text.Length > 0)
{
mdl_NewProject.PortfolioNumber = tb_PortfolioNo.Text;
}
mdl_NewProject.DSPT = chkb_DSPT.Checked;
mdl_NewProject.ISO27001 = chkb_ISO27001.Checked;
mdl_NewProject.LIDA = chkb_LIDA.Checked;
mdl_NewProject.LASER = chkb_LASER.Checked;
mdl_NewProject.IRC = chkb_IRC.Checked;
mdl_NewProject.SEED = chkb_SEED.Checked;
if (cb_pStage.SelectedIndex > -1)
{
mdl_NewProject.Stage = int.Parse(cb_pStage.SelectedValue.ToString());
}
if (cb_pClassification.SelectedIndex > -1)
{
mdl_NewProject.Classification = int.Parse(cb_pClassification.SelectedValue.ToString());
}
if (cb_DATRAG.SelectedIndex > -1)
{
mdl_NewProject.DATRAG = int.Parse(cb_DATRAG.SelectedValue.ToString());
}
if (cb_Faculty.SelectedIndex > -1)
{
mdl_NewProject.Faculty = int.Parse(cb_Faculty.SelectedValue.ToString());
}
if (cb_LeadApplicant.SelectedIndex > -1)
{
mdl_NewProject.LeadApplicant = int.Parse(cb_LeadApplicant.SelectedValue.ToString());
}
if (cb_PI.SelectedIndex > -1)
{
mdl_NewProject.PI = int.Parse(cb_PI.SelectedValue.ToString());
}
//dates are fuckey
bool dateCheck = true;
if (dateCheck == true & mtb_ProjectedStartDateValue.Text != "" & mtb_ProjectedStartDateValue.Text != " / /")
{
try
{
mdl_NewProject.ProjectedStartDate = Convert.ToDateTime(mtb_ProjectedStartDateValue.Text);
}
catch (Exception)
{
MessageBox.Show("Please enter valid Projected Start Date");
dateCheck = false;
}
}
if (dateCheck == true & mtb_ProjectedEndDateValue.Text != "" & mtb_ProjectedEndDateValue.Text != " / /")
{
try
{
mdl_NewProject.ProjectedEndDate = Convert.ToDateTime(mtb_ProjectedEndDateValue.Text);
}
catch (Exception)
{
MessageBox.Show("Please enter valid Projected End Date");
dateCheck = false;
}
}
if (dateCheck == true & mtb_pStartDateValue.Text != "" & mtb_pStartDateValue.Text != " / /")
{
try
{
mdl_NewProject.StartDate = Convert.ToDateTime(mtb_pStartDateValue.Text);
dateCheck = true;
}
catch (Exception)
{
MessageBox.Show("Please enter valid Start Date");
dateCheck = false;
}
}
if (dateCheck == true & mtb_pEndDateValue.Text != "" & mtb_pEndDateValue.Text != " / /")
{
try
{
mdl_NewProject.EndDate = Convert.ToDateTime(mtb_pEndDateValue.Text);
}
catch (Exception)
{
MessageBox.Show("Please enter valid End Date");
dateCheck = false;
}
}
//instantiate new Project type object that contains methods to update db
Project Projects = new Project();
//Check required fields have an entry
if (Projects.requiredFields(mdl_NewProject) == false)
{
return(success = false);
}
//check to see if any changes have been made, no need to update if none.
if (mdl_NewProject == mdl_CurrentProject)
{
return(success = true);
}
if (dateCheck == true)
{
//check that record currently displayed is current record in database before updating anything
if (Projects.checkCurrentRecord(pNumber, mdl_CurrentProject.pID) == true)
{
//update existing project - first perform insert new record, if success returned = true then logical delete
if (Projects.insertProject(mdl_NewProject) == true)
{
Projects.deleteProject(mdl_CurrentProject.pID);
//refresh dataset (ds_Projects) and form variable and control values
fillProjectsDataSet();
refreshProjectForm(pNumber);
success = true;
}
}
}
return(success);
}