//delete document private void deleteProjectDocument() { if (dgv_ProjectDocHistory.Rows.Count > 0 & dgv_ProjectDocHistory.SelectedRows.Count > 0) { foreach (DataGridViewRow r in dgv_ProjectDocHistory.SelectedRows) { mdl_ProjectDoc mdl_ProjectDoc = new mdl_ProjectDoc(); int current_pdID = int.Parse(r.Cells["pdID"].Value.ToString()); mdl_ProjectDoc.DocumentType_Desc = r.Cells["Document Type"].Value.ToString(); mdl_ProjectDoc.VersionNumber = Decimal.Parse(r.Cells["Version"].Value.ToString()); DialogResult acceptProjectDoc = MessageBox.Show($"Delete version {mdl_ProjectDoc.VersionNumber} of the {mdl_ProjectDoc.DocumentType_Desc}?", "", MessageBoxButtons.YesNo); if (acceptProjectDoc == DialogResult.Yes) { Project projects = new Project(); // update valid to of current record if (projects.deleteProjectDocument(current_pdID) == true) { dgv_ProjectDocHistory.Rows.RemoveAt(r.Index); } } } } else { MessageBox.Show("Please select a document record."); } }
public bool insertNewDoc(mdl_ProjectDoc mdl_ProjectDoc) { 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 qry_insertNewDoc = new SqlCommand(); qry_insertNewDoc.Connection = conn; qry_insertNewDoc.CommandText = $"insert into [dbo].[tblProjectDocument] " + $"([ProjectNumber], [DocumentType], [VersionNumber], [Submitted], [Accepted]) " + $"values " + $"(@ProjectNumber, @DocumentType, @VersionNumber, @Submitted, @Accepted) "; qry_insertNewDoc.Parameters.Add("@ProjectNumber", SqlDbType.VarChar).Value = mdl_ProjectDoc.ProjectNumber; qry_insertNewDoc.Parameters.Add("@DocumentType", SqlDbType.Int).Value = mdl_ProjectDoc.DocumentType; qry_insertNewDoc.Parameters.Add("@VersionNumber", SqlDbType.Decimal).Value = mdl_ProjectDoc.VersionNumber; qry_insertNewDoc.Parameters.Add("@Submitted", SqlDbType.DateTime).Value = mdl_ProjectDoc.Submitted; SqlParameter param_Accepted = new SqlParameter("@Accepted", mdl_ProjectDoc.Accepted == null ? (object)DBNull.Value : mdl_ProjectDoc.Accepted); qry_insertNewDoc.Parameters.Add(param_Accepted); param_Accepted.IsNullable = true; conn.Open(); qry_insertNewDoc.ExecuteNonQuery(); } return(true); } catch (Exception ex) { MessageBox.Show("Failed to insert new document." + Environment.NewLine + Environment.NewLine + ex.Message); return(false); } }
/// <summary> /// Takes the project number and document type and queries the database for the next whole version number. /// </summary> /// <param name="ProjectNumber"></param> /// <param name="DocumentType"></param> /// <returns></returns> public int?getNextDocVersion(mdl_ProjectDoc mdl_ProjectDoc) { int?version = null; 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 qry_getNextDocVersion = new SqlCommand(); qry_getNextDocVersion.Connection = conn; qry_getNextDocVersion.CommandText = $"select isnull(max(floor([VersionNumber])) + 1, 1) " + $"from[dbo].[tblProjectDocument] " + $"where[ValidTo] is null " + $"and [ProjectNumber] = @ProjectNumber " + $"and[DocumentType] = @DocumentType "; qry_getNextDocVersion.Parameters.Add("@ProjectNumber", SqlDbType.VarChar).Value = mdl_ProjectDoc.ProjectNumber; qry_getNextDocVersion.Parameters.Add("@DocumentType", SqlDbType.Int).Value = mdl_ProjectDoc.DocumentType; conn.Open(); object result = qry_getNextDocVersion.ExecuteScalar(); result = (result == DBNull.Value) ? null : result; version = Convert.ToInt32(result); } } catch (Exception ex) { MessageBox.Show("Failed to generate next document version number." + Environment.NewLine + ex.Message); } return(version); }
/// <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 requiredDocFields(mdl_ProjectDoc mdl_ProjectDoc) { if (mdl_ProjectDoc.DocumentType < 1 || mdl_ProjectDoc.DocumentType == null) { MessageBox.Show("Please choose a Document Type."); return(false); } if (mdl_ProjectDoc.Submitted == null) { MessageBox.Show("Please enter a Submitted Date."); return(false); } return(true); }
//accept document private void acceptProjectDocument() { if (dgv_ProjectDocHistory.Rows.Count > 0 & dgv_ProjectDocHistory.SelectedRows.Count > 0) { foreach (DataGridViewRow r in dgv_ProjectDocHistory.SelectedRows) { mdl_ProjectDoc mdl_ProjectDoc = new mdl_ProjectDoc(); int current_pdID = int.Parse(r.Cells["pdID"].Value.ToString()); mdl_ProjectDoc.ProjectNumber = projectNumber; mdl_ProjectDoc.DocumentType = int.Parse(r.Cells["DocumentID"].Value.ToString()); mdl_ProjectDoc.DocumentType_Desc = r.Cells["Document Type"].Value.ToString(); mdl_ProjectDoc.VersionNumber = Decimal.Parse(r.Cells["Version"].Value.ToString()); if (r.Cells["Submitted"].Value.ToString().Length > 0) { mdl_ProjectDoc.Submitted = DateTime.Parse(r.Cells["Submitted"].Value.ToString()); } mdl_ProjectDoc.Accepted = DateTime.Now; DialogResult acceptProjectDoc = MessageBox.Show($"Accept version {mdl_ProjectDoc.VersionNumber} of the {mdl_ProjectDoc.DocumentType_Desc}?", "", MessageBoxButtons.YesNo); if (acceptProjectDoc == DialogResult.Yes) { Project projects = new Project(); // insert new record if (projects.insertNewDoc(mdl_ProjectDoc) == true) { // update valid to of current record if (projects.deleteProjectDocument(current_pdID) == true) { r.Cells["Accepted"].Value = DateTime.Now; } } } } } else { MessageBox.Show("Please select a document record."); } }