/*************************************************************************
* Create Inspection
* Iterates through the dynamically populated form, grabs values from
* the repeater items and elements.
* Performs an insert into the inspections and inspection failure tables of the db.
* Uses pop-up/modal notifications for successful or non-successful attempts.
**************************************************************************/
protected void btn_createInspection_Click(object sender, EventArgs e)
{
int inspectionId = 0;
Boolean passed = false;
string creationDate;
if (txt_inspectionDate.Text != "")
creationDate = txt_inspectionDate.Text;
else creationDate = DateTime.Now.ToString("M/d/yyyy");
List<InspectionItem> failedItems = new List<InspectionItem>();
int contractorId = 0;
foreach (RepeaterItem i in repeater_busInfo.Items)
{
Label contractor = (Label)i.FindControl("lbl_contractor_id");
contractorId = int.Parse(contractor.Text);
}
//iterate through and add failures to list
foreach (RepeaterItem group in Repeater_groups.Items)
{
Repeater rep = (Repeater)group.FindControl("Repeater_items");
foreach (RepeaterItem item in rep.Items)
{
CheckBox cb = (CheckBox)item.FindControl("cb_fail");
if (cb.Checked)
{
InspectionItem failedItem = new InspectionItem();
DropDownList severity = (DropDownList)item.FindControl("ddl_severity");
failedItem.severity_id = severity.SelectedIndex;
Label id = (Label)item.FindControl("lbl_elementsId");
failedItem.element_id = int.Parse(id.Text);
TextBox tb = (TextBox)item.FindControl("txt_comments");
string comments = tb.Text;
Repeater rep2 = (Repeater)item.FindControl("Repeater_elements");
foreach(RepeaterItem note in rep2.Items)
{
CheckBox cb_note = (CheckBox)note.FindControl("cb_elements");
if (cb_note.Checked)
failedItem.notes += cb_note.Text + " / ";
}
failedItem.notes += comments;
failedItems.Add(failedItem);
}
}
}
if (failedItems.Count == 0)
passed = true;
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["BSIAConnectionString"].ConnectionString);
conn.Open();
int bus_id = 0;
SqlCommand cmd1 = new SqlCommand("SELECT bus_id from BusContractorNumber WHERE bus_number = @bus_num", conn);
cmd1.Parameters.AddWithValue("@bus_num", ddl_bus.SelectedValue);
bus_id = (int)cmd1.ExecuteScalar();
SqlCommand cmd = new SqlCommand("INSERT INTO BSIA.dbo.Inspections(inspection_date, season_id, bus_id, contractor_id, user_id, odometer, inspector_esignature, notes, contractor_ename, contractor_esignature, pass_date, tag_number, created_by, updated_by, date_created, date_updated) " +
"output INSERTED.inspection_id" + " VALUES (@insp_date, @season_id, @bus_id, @contractor_id, @user_id," +
" @odometer, @inspector_esignature, @notes, @contractor_ename, @contractor_esignature, @pass_date, @tag_number," +
" @created_by, @updated_by, GETDATE(), GETDATE())", conn);
try
{
cmd.Parameters.AddWithValue("@insp_date", creationDate);
cmd.Parameters.AddWithValue("@season_id", ddl_season.SelectedIndex);
cmd.Parameters.AddWithValue("@bus_id", bus_id);
cmd.Parameters.AddWithValue("@contractor_id", contractorId);
//TODO: get user id
//bogus user id for now
cmd.Parameters.AddWithValue("@user_id", 1);
cmd.Parameters.AddWithValue("@odometer", int.Parse(txt_odometer.Text));
//TODO: update '1' to proper variable for esignatures
cmd.Parameters.AddWithValue("@inspector_esignature", "1");
cmd.Parameters.AddWithValue("@contractor_esignature", "1");
string contractor_ename = txt_sig_contractor_first.Text + " " + txt_sig_contractor_last.Text;
cmd.Parameters.AddWithValue("@contractor_ename", contractor_ename);
cmd.Parameters.AddWithValue("@notes", ta_notes.Value);
if (passed)
cmd.Parameters.AddWithValue("@pass_date", creationDate);
else
cmd.Parameters.AddWithValue("@pass_date", DBNull.Value);
cmd.Parameters.AddWithValue("@tag_number", txt_tag.Text);
//TODO: get user id
//bogus user id for now
cmd.Parameters.AddWithValue("@created_by", 1);
cmd.Parameters.AddWithValue("@updated_by", 1);
inspectionId = (int)cmd.ExecuteScalar();
if (failedItems.Count != 0)
{
foreach (InspectionItem item in failedItems)
{
item.inspection_id = inspectionId;
SqlCommand cmd_fails = new SqlCommand("INSERT INTO BSIA.dbo.InspectionFailures(inspection_id, element_id, notes, severity_id, created_by, updated_by, date_created, date_updated) " +
" VALUES (@inspection_id, @element_id, @notes, @severity_id," +
" @created_by, @updated_by, GETDATE(), GETDATE())", conn);
cmd_fails.Parameters.AddWithValue("@inspection_id", item.inspection_id);
cmd_fails.Parameters.AddWithValue("@element_id", item.element_id);
cmd_fails.Parameters.AddWithValue("@notes", (item.notes == null)? "":item.notes);
cmd_fails.Parameters.AddWithValue("@severity_id", item.severity_id);
//TODO: get user id
//bogus user id for now
cmd_fails.Parameters.AddWithValue("@created_by", 1);
cmd_fails.Parameters.AddWithValue("@updated_by", 1);
cmd_fails.ExecuteNonQuery();
}
}
//notify user Creation was successful
ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "openModal_success_create();", true);
pnl_success.Visible = true;
btn_createInspection.Enabled = false;
}
catch (Exception err)
{
//notify user Creation resulted in an error
ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "openModal_error_create();", true);
pnl_error.Visible = true;
System.Console.Write(err);
}
finally
{
conn.Close();
}
}
/*************************************************************************
* Save changes to Inspection
* Takes input and inserts into the Inspection table of the BSIA database.
**************************************************************************/
protected void btn_saveInspection_Click(object sender, EventArgs e)
{
//retrieve the saved inspection_id from command arguemnets
Button btn= (Button)sender;
int inspectionId = int.Parse(btn.CommandArgument);
//delete all previously saved failures
/*
DELETE FROM dbo.InspectionFailures
WHERE inspection_id=@inspection_id;
*/
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["BSIAConnectionString"].ConnectionString);
conn.Open();
try
{
SqlCommand cmd = new SqlCommand("DELETE FROM dbo.InspectionFailures " +
"WHERE inspection_id=@inspection_id AND repaired_date IS NULL", conn);
cmd.Parameters.Add(new SqlParameter("@inspection_id", inspectionId));
cmd.ExecuteNonQuery();
}
catch (Exception err)
{
}
//updated failure information
Boolean passed = false;
List<InspectionItem> failedItems = new List<InspectionItem>();
//iterate through and add failures to list
foreach (RepeaterItem group in Repeater_groups.Items)
{
Repeater rep = (Repeater)group.FindControl("Repeater_items");
foreach (RepeaterItem item in rep.Items)
{
CheckBox cb = (CheckBox)item.FindControl("cb_fail");
if (cb.Checked)
{
InspectionItem failedItem = new InspectionItem();
DropDownList severity = (DropDownList)item.FindControl("ddl_severity");
failedItem.severity_id = severity.SelectedIndex;
Label id = (Label)item.FindControl("lbl_elementsId");
failedItem.element_id = int.Parse(id.Text);
TextBox tb = (TextBox)item.FindControl("txt_comments");
string comments = tb.Text;
Repeater rep2 = (Repeater)item.FindControl("Repeater_elements");
foreach (RepeaterItem note in rep2.Items)
{
CheckBox cb_note = (CheckBox)note.FindControl("cb_elements");
if (cb_note.Checked)
failedItem.notes += cb_note.Text + " / ";
}
//TextBox tb = (TextBox)item.FindControl("txt_comments");
failedItem.notes += comments;
failedItems.Add(failedItem);
}
}
}
if (failedItems.Count == 0)
{
passed = true;
}
else
{
foreach (InspectionItem item in failedItems)
{
item.inspection_id = inspectionId;
try
{
SqlCommand cmd_fails = new SqlCommand("INSERT INTO BSIA.dbo.InspectionFailures(inspection_id, element_id, notes, severity_id, created_by, updated_by, date_created, date_updated) " +
" VALUES (@inspection_id, @element_id, @notes, @severity_id," +
" @created_by, @updated_by, GETDATE(), GETDATE())", conn);
cmd_fails.Parameters.AddWithValue("@inspection_id", item.inspection_id);
cmd_fails.Parameters.AddWithValue("@element_id", item.element_id);
cmd_fails.Parameters.AddWithValue("@notes", (item.notes == null) ? "" : item.notes);
cmd_fails.Parameters.AddWithValue("@severity_id", item.severity_id);
//TODO: get user id
//bogus user id for now
cmd_fails.Parameters.AddWithValue("@created_by", 1);
cmd_fails.Parameters.AddWithValue("@updated_by", 1);
cmd_fails.ExecuteNonQuery();
}
catch (Exception err)
{ }
}
}
//update inspection
string today = DateTime.Now.ToString("M/d/yyyy");
SqlCommand cmd_update = new SqlCommand("UPDATE BSIA.dbo.Inspections " +
"SET odometer=@odometer, " +
"notes=@notes, " +
"date_updated=GETDATE(), " +
"pass_date=@pass_date " +
"WHERE inspection_id=@inspection_id", conn);
try
{
cmd_update.Parameters.AddWithValue("@inspection_id", inspectionId);
cmd_update.Parameters.AddWithValue("@odometer", txt_odometer.Text);
cmd_update.Parameters.AddWithValue("@notes", ta_notes.Value);
if (passed)
cmd_update.Parameters.AddWithValue("@pass_date", today);
else
cmd_update.Parameters.AddWithValue("@pass_date", DBNull.Value);
cmd_update.ExecuteNonQuery();
//notify user Creation was successful
ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "openModal_success_edit();", true);
pnl_success_edit.Visible = true;
}
catch (Exception err)
{
//notify user Edit resulted in an error
ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "openModal_error_edit();", true);
pnl_error_edit.Visible = true;
System.Console.Write(err);
}
finally
{
conn.Close();
}
}