/// <summary>
/// Add a RepairItem to the list of staged RepairItems for this Repair.
/// </summary>
public void AddRepairItem(RepairItem ri)
{
ListViewItem riLV = new ListViewItem();
decimal discountAmount = 0;
string productName = "";
// Get the product name
using (SqlCommand command = new SqlCommand())
{
command.Connection = cnn;
command.CommandType = CommandType.Text;
command.CommandText = "SELECT ProductName FROM Products WHERE ProductID = @ProductID";
command.Parameters.AddWithValue("@ProductID", ri.ProductID);
// TODO: Exception handling
using (SqlDataReader reader = command.ExecuteReader())
{
// Should always be true
if (reader.Read())
{
productName = reader[0].ToString();
}
else
{
// Somehow there was an error applying the promo code to the order (perhaps it just expired)
}
}
}
riLV.Tag = ri;
riLV.SubItems.AddRange(new String[] {
productName,
ri.RepairType,
ri.LaborPrice.ToString("F")
});
lv_repairItems.Items.Add(riLV);
lv_repairItems.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize);
ValidateForm();
}
public RepairItemSelection(RepairForm parent)
{
InitializeComponent();
this.parent = parent;
repairItem = new RepairItem();
cnn = new SqlConnection(Constants.ConnectionString);
try
{
cnn.Open();
}
catch (SqlException ex)
{
MessageBox.Show("Error: the connection could not be opened.");
Close();
}
loadItems();
}
/// <summary>
/// If editing a previously existing repair, preload all fields.
/// </summary>
private void PreloadData()
{
;
repair = new Repair();
repair.RepairID = editItemID;
// Load in Repair details
using (SqlCommand command = new SqlCommand())
{
command.Connection = cnn;
command.CommandType = CommandType.Text;
command.CommandText = "SELECT CustomerID, CompletionDate, Description FROM Repairs WHERE RepairID = @RepairID";
command.Parameters.AddWithValue("@RepairID", editItemID);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
repair.CustomerID = Convert.ToInt32(reader[0]);
repair.CompletionDate = Convert.ToDateTime(reader[1]);
repair.Description = reader[2].ToString();
}
}
}
// Load in RepairItem details
using (SqlCommand command = new SqlCommand())
{
command.Connection = cnn;
command.CommandType = CommandType.Text;
command.CommandText = "SELECT RepairItemID, ProductID, RepairType, LaborPrice FROM RepairItems WHERE RepairID = @RepairID";
command.Parameters.AddWithValue("@RepairID", editItemID);
List <RepairItem> riList = new List <RepairItem>();
// Create new SqlDataReader object and read data from the command.
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
RepairItem ri = new RepairItem();
ri.RepairItemID = Convert.ToInt32(reader[0]);
ri.ProductID = Convert.ToInt32(reader[1]);
ri.RepairType = reader[2].ToString();
ri.LaborPrice = Convert.ToDecimal(reader[3]);
riList.Add(ri);
}
}
foreach (RepairItem ri in riList)
{
AddRepairItem(ri);
}
}
Constants.SetComboBoxToItemWithID(cmb_customer, repair.CustomerID);
datetimePickCompleted.Value = repair.CompletionDate;
txt_description.Text = repair.Description;
ValidateForm();
}
private void ProcessRepair()
{
int autoRepairID = 0;
if (creating)
{
// Insert Repair
using (SqlCommand command = new SqlCommand())
{
command.Connection = cnn;
command.CommandType = CommandType.Text;
command.CommandText = "INSERT INTO Repairs (CustomerID, CompletionDate, Description) VALUES (@CustomerID, @CompletionDate, @Description); SELECT SCOPE_IDENTITY()";
command.Parameters.AddWithValue("@CustomerID", repair.CustomerID);
command.Parameters.AddWithValue("@CompletionDate", repair.CompletionDate.ToString("yyyy-MM-dd"));
command.Parameters.AddWithValue("@Description", repair.Description);
autoRepairID = Convert.ToInt32(command.ExecuteScalar());
MessageBox.Show("Repair succefully placed!");
}
}
else
{
// Edit Repair
using (SqlCommand command = new SqlCommand())
{
command.Connection = cnn;
command.CommandType = CommandType.Text;
command.CommandText = "UPDATE Repairs SET CustomerID = @CustomerID, CompletionDate = @CompletionDate, Description = @Description WHERE RepairID = @RepairID";
command.Parameters.AddWithValue("@RepairID", editItemID);
command.Parameters.AddWithValue("@CustomerID", repair.CustomerID);
command.Parameters.AddWithValue("@CompletionDate", repair.CompletionDate.ToString("yyyy-MM-dd"));
command.Parameters.AddWithValue("@Description", repair.Description);
autoRepairID = Convert.ToInt32(command.ExecuteScalar());
MessageBox.Show("Repair succefully updated!");
}
}
if (creating)
{
// Insert OrderItems
foreach (ListViewItem item in lv_repairItems.Items)
{
RepairItem ri = item.Tag as RepairItem;
using (SqlCommand command = new SqlCommand())
{
command.Connection = cnn;
command.CommandText = "INSERT INTO RepairItems(RepairID, ProductID, RepairType, LaborPrice) VALUES (@RepairID, @ProductID, @RepairType, @LaborPrice)";
command.Parameters.AddWithValue("@RepairID", autoRepairID);
command.Parameters.AddWithValue("@ProductID", ri.ProductID);
command.Parameters.AddWithValue("@RepairType", ri.RepairType);
command.Parameters.AddWithValue("@LaborPrice", ri.LaborPrice);
command.ExecuteNonQuery();
}
}
}
else
{
// Delete RepairItems
using (SqlCommand command = new SqlCommand())
{
command.Connection = cnn;
command.CommandText = "DELETE FROM RepairItems WHERE RepairID = @RepairID ";
command.Parameters.AddWithValue("@RepairID ", repair.RepairID);
command.ExecuteNonQuery();
}
// Re-insert RepairItems
foreach (ListViewItem item in lv_repairItems.Items)
{
RepairItem ri = item.Tag as RepairItem;
using (SqlCommand command = new SqlCommand())
{
command.Connection = cnn;
command.CommandText = "INSERT INTO RepairItems(RepairID, ProductID, RepairType, LaborPrice) VALUES (@RepairID, @ProductID, @RepairType, @LaborPrice)";
command.Parameters.AddWithValue("@RepairID", editItemID);
command.Parameters.AddWithValue("@ProductID", ri.ProductID);
command.Parameters.AddWithValue("@RepairType", ri.RepairType);
command.Parameters.AddWithValue("@LaborPrice", ri.LaborPrice);
command.ExecuteNonQuery();
}
}
}
cnn.Close();
Close();
}
