private void btnLogin_Click(object sender, EventArgs e) { if (cbbClass.SelectedIndex < 0 || cbbName.SelectedIndex < 0) { RmsgBox.Show("請選擇姓名", "錯誤"); return; } loginName = studentItems[cbbName.SelectedIndex].name; studentNumber = studentItems[cbbName.SelectedIndex].number; class_name = classItems[cbbClass.SelectedIndex].name; int id = studentItems[cbbName.SelectedIndex].id; int class_id = classItems[cbbClass.SelectedIndex].id; Boolean ifOnDuty = false; if (ckb_onDuty.Checked) { ifOnDuty = true; } //MessageBox.Show(string.Format("ID: {3}, 姓名: {0}, 座號: {1}, 班級: {2}", loginName, studentNumber, class_name, id), "登入成功", MessageBoxButtons.OK, MessageBoxIcon.None); Form_main form = new Form_main(this, id, loginName, studentNumber, class_name, class_id, ifOnDuty); //hide form1 and show form_main form.Show(); this.Hide(); }
//delete supplier private void btnDeleteSup_Click(object sender, EventArgs e) { try { string supplierName = cbbSuppliers.SelectedItem.ToString(); DialogResult dr = RmsgBox.Show("是否要刪除廠商: " + supplierName + "?", "確認刪除", MessageBoxButtons.OKCancel); if (dr == DialogResult.OK) { SqlConnection con = new SqlConnection(sqlCon); con.Open(); SqlCommand cmd = new SqlCommand("delete from supplier_items where sup_id = @sup_id", con); cmd.Parameters.AddWithValue("@sup_id", SupplierID); cmd.ExecuteNonQuery(); cmd = new SqlCommand("delete from supplier where name = @name", con); cmd.Parameters.AddWithValue("@name", supplierName); cmd.ExecuteNonQuery(); con.Close(); DsSupplier.Tables["supplier"].Clear(); DaSupplier.Fill(DsSupplier, "supplier"); cbbSuppliers.Items.Clear(); foreach (DataRow row in DsSupplier.Tables["supplier"].Rows) { cbbSuppliers.Items.Add(row["name"]); } cbbSuppliers.SelectedIndex = 0; RmsgBox.Show("已刪除廠商: " + supplierName, "成功"); } }catch (Exception ex) { Console.WriteLine(ex); } }
//Add Snew supplier private void btnAddSup_Click(object sender, EventArgs e) { string promptValue = Prompt.ShowDialog("請輸入廠商的名稱:", "建立新廠商", false); if (promptValue != "") { try { SqlConnection con = new SqlConnection(sqlCon); con.Open(); SqlCommand cmd = new SqlCommand("insert into supplier(name) values(@name)", con); cmd.Parameters.AddWithValue("@name", promptValue); SqlDataReader reader = cmd.ExecuteReader(); reader.Close(); con.Close(); DsSupplier.Tables["supplier"].Clear(); DaSupplier.Fill(DsSupplier, "supplier"); cbbSuppliers.Items.Clear(); foreach (DataRow row in DsSupplier.Tables["supplier"].Rows) { cbbSuppliers.Items.Add(row["name"]); } cbbSuppliers.SelectedIndex = cbbSuppliers.Items.Count - 1; } catch (Exception ex) { RmsgBox.Show("廠商名稱不可重複", "錯誤"); } } }
//Delete class private void btnDeleteClass_Click(object sender, EventArgs e) { string className = cbbClass.SelectedItem.ToString(); DialogResult dr = RmsgBox.Show("是否要刪除班級: " + className + "?", "確認刪除", MessageBoxButtons.OKCancel); if (dr == DialogResult.OK) { SqlConnection con = new SqlConnection(sqlCon); con.Open(); SqlCommand cmd = new SqlCommand("delete from student where class_id = @class_id", con); cmd.Parameters.AddWithValue("@class_id", classID); cmd.ExecuteNonQuery(); cmd = new SqlCommand("delete from class where name = @name", con); cmd.Parameters.AddWithValue("@name", className); cmd.ExecuteNonQuery(); con.Close(); DsLunch.Tables["class"].Clear(); DaClass.Fill(DsLunch, "class"); cbbClass.Items.Clear(); foreach (DataRow row in DsLunch.Tables["class"].Rows) { cbbClass.Items.Add(row["name"]); } cbbClass.SelectedIndex = 0; RmsgBox.Show("已刪除班級: " + className, "成功"); } }
//Reception Login private void button1_Click(object sender, EventArgs e) { string pwd = Prompt.ShowDialog("請輸入密碼", "登入", true); if (pwd == password) { Form_order_detail form = new Form_order_detail(sqlCon, 0); form.ShowDialog(this); } else { RmsgBox.Show("密碼錯誤!", "錯誤"); } }
//Destroy order private void BtnDestroy_Click(object sender, EventArgs e) { Button btn = (Button)sender; DialogResult dr = RmsgBox.Show("訂單是否已下訂完成?", "警告", MessageBoxButtons.OKCancel); if (dr == DialogResult.OK) { SqlConnection con = new SqlConnection(sqlCon); con.Open(); SqlDataReader reader = null; try { //create new history string strSQL = "insert into history values(@class_name, CONVERT(VARCHAR(19),GETDATE(), 110))"; SqlCommand cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@class_name", tabControl1.SelectedTab.Text); cmd.ExecuteNonQuery(); //get history id int history_id; strSQL = "select Max(history_id) from history where class_name = @class_name"; cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@class_name", tabControl1.SelectedTab.Text); history_id = (int)cmd.ExecuteScalar(); //create history details strSQL = "insert into history_detail select @history, s.name, i.item_name, i.price, od.qty from student s join class c on s.class_id = c.class_id " + "join orders o on o.stu_id = s.stu_id join order_detail od on o.order_id = od.order_id " + "join supplier_items i on i.item_id = od.item_id where c.class_id = @class_id"; cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@class_id", btn.Name); cmd.Parameters.AddWithValue("@history", history_id); cmd.ExecuteNonQuery(); //delete order details strSQL = "delete from order_detail where order_id in (select order_id from orders where stu_id in (select stu_id from student where class_id = @class_id))"; cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@class_id", btn.Name); cmd.ExecuteNonQuery(); //delete orders strSQL = "delete from orders where stu_id in (select stu_id from student where class_id = @class_id)"; cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@class_id", btn.Name); cmd.ExecuteNonQuery(); //Nullify todaySupplier & submit strSQL = "update class set todaySupplier = null, submit = null where class_id = @class_id"; cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@class_id", btn.Name); cmd.ExecuteNonQuery(); tabControl1.TabPages.Remove(tabControl1.SelectedTab); loadClsHis(); } catch (Exception ex) { Console.WriteLine(ex); } finally { if (reader != null) { reader.Close(); } con.Close(); } } }
//save items private void btnSave_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(sqlCon); con.Open(); int i = 0; SqlCommand cmd = new SqlCommand("update supplier set tel = @tel where sup_id = @sup_id", con); cmd.Parameters.AddWithValue("@tel", tbTel.Text); cmd.Parameters.AddWithValue("@sup_id", SupplierID); SqlDataReader reader = cmd.ExecuteReader(); reader.Close(); foreach (supplierItem item in supplierItems) { try { bool ifModified = false; //Console.WriteLine(tempItemName[i]); /* * foreach (string o in tempItemName) * { * if (item.name.Text == o.ToString()) * { * Console.WriteLine(item.name.Text + " should be modified"); * ifModified = true; * break; * } * } */ if (i < tempItemName.Count()) { Console.WriteLine(tempItemName[i] + " should be modified"); ifModified = true; } if (ifModified) //if this item should be modified { if (item.name.Text != "") //update { Console.WriteLine("updating " + item.name.Text); cmd = new SqlCommand("update supplier_items set item_name = @name, price = @price where sup_id = @sup_id AND item_name = @OldName", con); cmd.Parameters.AddWithValue("@name", item.name.Text); cmd.Parameters.AddWithValue("@OldName", tempItemName[i]); cmd.Parameters.AddWithValue("@sup_id", SupplierID); cmd.Parameters.AddWithValue("@price", item.price.Text); reader = cmd.ExecuteReader(); reader.Close(); } else //delete { Console.WriteLine("deleting" + item.name.Text); cmd = new SqlCommand("delete from supplier_items where sup_id = @sup_id AND item_name = @name", con); cmd.Parameters.AddWithValue("@sup_id", SupplierID); cmd.Parameters.AddWithValue("@name", tempItemName[i]); reader = cmd.ExecuteReader(); reader.Close(); } } else // should be inserted { if (item.name.Text != "") { Console.WriteLine("inserting " + item.name.Text); cmd = new SqlCommand("insert into supplier_items values(@sup_id, @name, @price)", con); cmd.Parameters.AddWithValue("@name", item.name.Text); cmd.Parameters.AddWithValue("@sup_id", SupplierID); cmd.Parameters.AddWithValue("@price", item.price.Text); reader = cmd.ExecuteReader(); reader.Close(); } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } i++; } con.Close(); DsSupplier = new DataSet(); DaSupplier.Fill(DsSupplier, "supplier"); DasupItem.Fill(DsSupplier, "supplier_items"); RmsgBox.Show("已儲存變更", "儲存"); loadSupItemPage(); }
//Save/Update student names private void btnSave_Click(object sender, EventArgs e) { //DsLunch.Tables["student"].Rows.Clear(); //DaStudent.Fill(DsLunch, "student"); SqlConnection con = new SqlConnection(sqlCon); con.Open(); int i = 0; foreach (studentItem student in studentItems) { try { bool ifModified = false; foreach (int o in tempStuId) { if (student.id.Text == o.ToString()) { Console.WriteLine(student.id.Text + " should be modified"); ifModified = true; break; } } if (ifModified) //if this item should be modified { if (student.name.Text != "") //update { Console.WriteLine("updating " + student.name.Text); SqlCommand cmd = new SqlCommand("update student set name = @name where number = @number and class_id = @class_id", con); cmd.Parameters.AddWithValue("@name", student.name.Text); cmd.Parameters.AddWithValue("@number", Convert.ToInt32(student.id.Text)); cmd.Parameters.AddWithValue("@class_id", classID); SqlDataReader reader = cmd.ExecuteReader(); reader.Close(); } else //delete { Console.WriteLine("deleting" + student.id.Text); SqlCommand cmd = new SqlCommand("delete from student where number = @number and class_id = @class_id", con); cmd.Parameters.AddWithValue("@number", Convert.ToInt32(student.id.Text)); cmd.Parameters.AddWithValue("@class_id", classID); SqlDataReader reader = cmd.ExecuteReader(); reader.Close(); } } else // should be inserted { if (student.name.Text != "") { Console.WriteLine("inserting " + student.name.Text); SqlCommand cmd = new SqlCommand("insert into student values(@name, @number, @class_id)", con); cmd.Parameters.AddWithValue("@name", student.name.Text); cmd.Parameters.AddWithValue("@number", Convert.ToInt32(student.id.Text)); cmd.Parameters.AddWithValue("@class_id", classID); SqlDataReader reader = cmd.ExecuteReader(); reader.Close(); } } } catch (Exception ex) { Console.WriteLine(ex); } i++; } con.Close(); DsLunch = new DataSet(); DaStudent.Fill(DsLunch, "student"); DaClass.Fill(DsLunch, "class"); RmsgBox.Show("已儲存變更", "儲存"); loadClassPage(); }
//button confirm supplier private void btnConfirm_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(sqlCon); try { if (todaySup == null)//Choose Sup { try { con.Open(); string strSQL = "update class set todaySupplier = @sup_name where class_id = @class_id"; SqlCommand cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@sup_name", cbbChooseSup.SelectedItem.ToString()); cmd.Parameters.AddWithValue("@class_id", class_id); cmd.ExecuteNonQuery(); todaySup = cbbChooseSup.SelectedItem.ToString(); Console.WriteLine(cbbChooseSup.SelectedItem); btnConfirm.Text = "更改廠商"; cbbChooseSup.Enabled = false; btnSubmit.Enabled = true; } catch (Exception ex) { Console.WriteLine(ex); RmsgBox.Show("請選擇供應商", "錯誤"); } } else//Change Sup { //DialogResult dr = MessageBox.Show("是否更改供應商? 已下定訂單將被刪除", "警告", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning); DialogResult dr = RmsgBox.Show("是否更改供應商? 已下定訂單將被刪除", "警告", MessageBoxButtons.OKCancel); if (dr == DialogResult.OK) { ifOrdered = false; todaySup = null; btnConfirm.Text = "選擇"; cbbChooseSup.Enabled = true; cbbChooseSup.SelectedIndex = 0; //get name and price of each items of this supplier, and make button(name) and Label(price) on panel1 panel1.Controls.Clear(); //clear existing controls btnDishes.Clear(); supplierItems.Clear(); btnSubmit.Text = "確認訂購"; btnSubmit.Enabled = false; lblTotal.Visible = false; con = new SqlConnection(sqlCon); con.Open(); string strSQL = "select item_name, price, item_id from supplier_items where sup_id = @sup_id"; SqlCommand cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@sup_id", cbbChooseSup.SelectedIndex + 1); SqlDataReader reader = cmd.ExecuteReader(); while (reader.HasRows) { int i = 0; buildSupplierItems(reader, i); reader.NextResult(); } reader.Close(); con.Close(); con.Open(); //delete order details strSQL = "delete from order_detail where order_id in (select order_id from orders where stu_id in (select stu_id from student where class_id = @class_id))"; cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@class_id", class_id); cmd.ExecuteNonQuery(); //delete orders strSQL = "delete from orders where stu_id in (select stu_id from student where class_id = @class_id)"; cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@class_id", class_id); cmd.ExecuteNonQuery(); } } } finally { con.Close(); } }
//Confirm Button private void btnSubmit_Click(object sender, EventArgs e) { if (!ifOrdered) //not yet ordered, use to submit order { //Check if ordered anything foreach (supplier_item i in supplierItems) { if (i.tb.Value != 0) { break; } if (i == supplierItems[supplierItems.Count - 1]) { RmsgBox.Show("並未選取任何物品", "錯誤"); return; } } int orderID; //Check if TodaySup changed SqlConnection con = new SqlConnection(sqlCon); con.Open(); string strSQL = "select todaySupplier from class where class_id = @class_id"; SqlCommand cmd = new SqlCommand(strSQL, con); cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@class_id", class_id); String todaySupNew = (string)cmd.ExecuteScalar(); if (todaySupNew != todaySup) //today sup is different from the data in database { RmsgBox.Show("值日生已更換今日供應商,請重新下訂", "錯誤"); todaySup = todaySupNew; lblTodaySup.Text = "今天的供應商: " + todaySupNew; panel1.Controls.Clear(); supplierItems.Clear(); lblTotal.Visible = false; ifOrdered = false; showItems(); return; } string output = "您的訂單為:\n\n"; foreach (supplier_item supItem in supplierItems) { if (Convert.ToInt32(supItem.tb.Text) != 0) { output += supItem.btn.Text + " x " + supItem.tb.Text + "\n\n"; } } RmsgBox.Show(output, "下訂成功"); //create order master strSQL = "insert into orders values(@stu_id)"; cmd = new SqlCommand(strSQL, con); cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@stu_id", student_id); cmd.ExecuteNonQuery(); //get order id strSQL = "select order_id from orders where stu_id = @stu_id"; cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@stu_id", student_id); orderID = (int)cmd.ExecuteScalar(); //insert order items foreach (supplier_item supItem in supplierItems) { if (Convert.ToInt32(supItem.tb.Text) != 0) { strSQL = "insert into order_detail values(@order_id, @qty, @item_id)"; cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@order_id", orderID); cmd.Parameters.AddWithValue("@qty", supItem.tb.Value); cmd.Parameters.AddWithValue("@item_id", supItem.item_id); cmd.ExecuteNonQuery(); } } con.Close(); //show detail page panel1.Controls.Clear(); showOrderDetails(); ifOrdered = true; } else //Already ordered, use to cancel order { panel1.Controls.Clear(); supplierItems.Clear(); lblTotal.Visible = false; ifOrdered = false; showItems(); //Delete orders SqlConnection con = new SqlConnection(sqlCon); con.Open(); //delete order details string strSQL = "delete from order_detail where order_id in (select order_id from orders where stu_id = @stu_id)"; SqlCommand cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@stu_id", student_id); cmd.ExecuteNonQuery(); //delete orders strSQL = "delete from orders where stu_id = @stu_id"; cmd = new SqlCommand(strSQL, con); cmd.Parameters.AddWithValue("@stu_id", student_id); cmd.ExecuteNonQuery(); } }