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();
}
}