//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.");
}
}
