public static int AddIncident(Incident incident)
{
if (incident == null)
{
throw new ArgumentException("incident parameter must not be null");
}
SqlConnection connection = DBConnection.GetConnection();
string insertStatement =
"INSERT Incidents " +
"(CustomerID, ProductCode, DateOpened, Title, Description) " +
"VALUES (@CustomerID, @ProductCode, @DateOpened, @Title, @Description)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@CustomerID", incident.CustomerID);
insertCommand.Parameters.AddWithValue("@ProductCode", incident.ProductCode);
insertCommand.Parameters.AddWithValue("@DateOpened", incident.DateOpened);
insertCommand.Parameters.AddWithValue("@Title", incident.Title);
insertCommand.Parameters.AddWithValue("@Description", incident.Description);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement =
"SELECT IDENT_CURRENT('Incidents') FROM Incidents";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
int incidentID = Convert.ToInt32(selectCommand.ExecuteScalar());
return incidentID;
}
finally
{
if (connection != null)
connection.Close();
}
}
public static int AddIncident(Customer customer, Product product, string title, string description)
{
if (customer == null || product == null)
{
throw new ArgumentException("customer and product parameters must not be null");
}
Incident incident = new Incident();
incident.CustomerID = customer.CustomerID;
incident.ProductCode = product.ProductCode;
incident.Title = title;
incident.Description = description;
incident.DateOpened = DateTime.Now;
return IncidentData.AddIncident(incident);
}
public static List<Incident> GetOpenIncidents()
{
List<Incident> incidentList = new List<Incident>();
SqlConnection connection = DBConnection.GetConnection();
string selectStatement =
"SELECT i.ProductCode, i.DateOpened, c.Name as Customer, c.CustomerID as customerID, " +
" t.Name as Technician, i.Title " +
"FROM Incidents i " +
"JOIN Customers c ON c.CustomerID = i.CustomerID " +
"LEFT OUTER JOIN Technicians t ON t.TechID = i.TechID " +
"WHERE i.DateClosed IS NULL" ;
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Incident incident = new Incident();
incident.ProductCode = reader["ProductCode"].ToString();
incident.DateOpened = (DateTime)reader["DateOpened"];
incident.Customer = reader["Customer"].ToString();
incident.CustomerID = (int) reader["CustomerID"];
incident.TechName = reader["Technician"].ToString();
incident.Title = reader["Title"].ToString();
incidentList.Add(incident);
}
}
finally
{
if (connection != null)
connection.Close();
if (reader != null)
reader.Close();
}
return incidentList;
}
public static Boolean UpdateIncident(Incident oldIncident, Incident newIncident)
{
return IncidentData.UpdateIncident(oldIncident, newIncident);
}
public static Incident IncidentByID(int incidentID)
{
SqlConnection connection = DBConnection.GetConnection();
string selectStatement =
"SELECT i.ProductCode, i.DateOpened, i.DateClosed, c.Name as Customer, c.CustomerID as customerID, " +
" t.Name as Technician, t.TechID, i.Title, p.Name as ProductName, i.IncidentID, i.Description " +
"FROM Incidents i " +
"JOIN Customers c ON c.CustomerID = i.CustomerID " +
"JOIN Products p ON p.ProductCode = i.ProductCode " +
"LEFT OUTER JOIN Technicians t ON t.TechID = i.TechID " +
"WHERE IncidentID = @IncidentID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@IncidentID", incidentID);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
if (reader.Read())
{
Incident incident = new Incident((int) reader["IncidentID"]);
incident.CustomerID = (int) reader["CustomerID"];
incident.Customer = reader["Customer"].ToString();
incident.DateOpened = (DateTime) reader["DateOpened"];
incident.Title = reader["Title"].ToString();
incident.Description = reader["Description"].ToString();
if (!DBNull.Value.Equals(reader["TechID"]))
{
incident.TechID = (int)reader["TechID"];
incident.TechName = reader["Technician"].ToString();
}
else
{
incident.TechID = null;
incident.TechName = null;
}
if (!DBNull.Value.Equals(reader["DateClosed"]))
{
incident.DateClosed = (DateTime) reader["DateClosed"];
}
else
{
incident.DateClosed = null;
}
incident.ProductCode = reader["ProductCode"].ToString();
incident.ProductName = reader["ProductName"].ToString();
return incident;
}
else
{
return null;
}
}
finally
{
if (connection != null)
connection.Close();
if (reader != null)
reader.Close();
}
}
// Update the oldIncident values to the newIncident values. If the oldIncident
// values have changed in the DB then don't update and return false
public static Boolean UpdateIncident(Incident oldIncident, Incident newIncident)
{
SqlConnection connection = DBConnection.GetConnection();
String updateStatement =
"UPDATE Incidents " +
"SET CustomerID = @NewCustomerID, " +
" ProductCode = @NewProductCode, " +
" TechID = @NewTechID, " +
" DateClosed = @NewDateClosed, " +
" Title = @NewTitle, " +
" Description = @NewDescription " +
"WHERE IncidentID = @IncidentID " +
" AND CustomerID = @OldCustomerID " +
" AND ProductCode = @OldProductCode " +
" AND (TechID = @OldTechID " +
" OR TechID IS NULL AND @OldTechID IS NULL) " +
" AND (DateClosed = @OldDateClosed " +
" OR DateClosed IS NULL AND @OldDateClosed IS NULL) " +
" AND Description = @OldDescription";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@IncidentID", oldIncident.IncidentID);
updateCommand.Parameters.AddWithValue("@OldCustomerID", oldIncident.CustomerID);
updateCommand.Parameters.AddWithValue("@OldProductCode", oldIncident.ProductCode);
if (oldIncident.TechID.HasValue)
{
updateCommand.Parameters.AddWithValue("@OldTechID", oldIncident.TechID);
}
else
{
updateCommand.Parameters.AddWithValue("@OldTechID", DBNull.Value);
}
if (oldIncident.DateClosed.HasValue)
{
updateCommand.Parameters.AddWithValue("@OldDateClosed", oldIncident.DateClosed);
}
else
{
updateCommand.Parameters.AddWithValue("@OldDateClosed", DBNull.Value);
}
updateCommand.Parameters.AddWithValue("@OldTitle", oldIncident.Title);
updateCommand.Parameters.AddWithValue("@OldDescription", oldIncident.Description);
updateCommand.Parameters.AddWithValue("@NewCustomerID", newIncident.CustomerID);
updateCommand.Parameters.AddWithValue("@NewProductCode", newIncident.ProductCode);
if (newIncident.TechID.HasValue)
{
updateCommand.Parameters.AddWithValue("@NewTechID", newIncident.TechID);
}
else
{
updateCommand.Parameters.AddWithValue("@NewTechID", DBNull.Value);
}
if (newIncident.DateClosed.HasValue)
{
updateCommand.Parameters.AddWithValue("@NewDateClosed", newIncident.DateClosed);
}
else
{
updateCommand.Parameters.AddWithValue("@NewDateClosed", DBNull.Value);
}
updateCommand.Parameters.AddWithValue("@NewTitle", newIncident.Title);
updateCommand.Parameters.AddWithValue("@NewDescription", newIncident.Description);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
return true;
else
return false;
}
finally
{
connection.Close();
}
}
//Load the specified incident ID into the form
private void LoadIncident()
{
int incidentID;
try
{
incidentID = Convert.ToInt32(IncidentIDBox.Text);
}
catch (System.FormatException ex)
{
MessageBox.Show("Incident ID must be an integer");
IncidentIDBox.Text = "";
return;
}
Incident incident;
try
{
incident = IncidentsController.GetIncidentByID(incidentID);
}
catch (SqlException ex)
{
MessageBox.Show("Database Query error fetching incident.\n" + ex.Message);
return;
}
if (incident == null)
{
MessageBox.Show("No incidents found with Incident ID " + incidentID);
return;
}
else if (incident.DateClosed != null)
{
MessageBox.Show("Incident " + incidentID + " has already been closed");
}
else
{
this.fetchedIncident = incident;
this.currentIncident = this.fetchedIncident.ShallowCopy();
ResetForm();
}
}
private void UpdateIncident()
{
Boolean newTechAssigned = this.currentIncident.TechID != this.fetchedIncident.TechID;
String addText = TextToAddBox.Text;
if (DescriptionBoxFull && addText != "")
{
MessageBox.Show("Cannot add text when the description is already full");
return;
}
if (!DescriptionBoxFull && !newTechAssigned && addText == "")
{
MessageBox.Show("Incident cannot be updated unless text is added or a tech is changed");
return;
}
if (DescriptionBoxFull)
{
addText = "";
}
else if (addText == "" && newTechAssigned)
{
addText = GetNewLineDatePrefix() + "updated/assigned the technician";
TextToAddBox.Text = "updated/assigned the technician";
}
else
{
addText = GetNewLineDatePrefix() + addText;
}
string newDescription = DescriptionBox.Text + addText;
if (newDescription.Length > MAX_DESCRIPTION_LENGTH)
{
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
DialogResult result;
int remainingCharacters = MAX_DESCRIPTION_LENGTH - GetNewLineDatePrefix().Length - currentIncident.Description.Length;
result = MessageBox.Show("There is only room to add " + remainingCharacters + " characters. OK to truncate?", "Description too long", buttons);
if (result == DialogResult.Yes)
{
newDescription = newDescription.Substring(0, MAX_DESCRIPTION_LENGTH);
}
else
{
return;
}
}
try
{
this.currentIncident.Description = newDescription;
Boolean updateSuccessful = IncidentsController.UpdateIncident(this.fetchedIncident, this.currentIncident);
if (!updateSuccessful)
{
MessageBox.Show("Cannot udpate. Incident has been changed by another process.\nClick 'Get Incident' to reload");
}
this.fetchedIncident = this.currentIncident.ShallowCopy();
}
catch (SqlException ex)
{
MessageBox.Show("Database error updating incident.\n" + ex.Message);
}
MessageBox.Show("Incident updated");
ResetForm();
}
