Example #1
0
        /*************************************************************************
        *   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();
            }
        }
Example #2
0
        /*************************************************************************
        *   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();
            }
        }