private void ShowData()
{
ClassConnection.connectionDatabase();
string Sql = @"SELECT * FROM tbemployee ORDER BY Id_employee";
ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();
if (DataReader.HasRows)
{
ClassConnection.dataTable = new DataTable();
ClassConnection.dataTable.Load(DataReader);
dataGridView1.DataSource = ClassConnection.dataTable;
FormatDataGridView();
DataReader.Close();
}
else
{
ClassConnection.dataTable = new DataTable();
ClassConnection.dataTable.Load(DataReader);
dataGridView1.DataSource = ClassConnection.dataTable;
FormatDataGridView();
DataReader.Close();
}
}
private void ShowData()
{
ClassConnection.connectionDatabase();
string Sql = @"SELECT tbproduct.Id_product,tbproduct.N_product,tbproduct.N_product_desc,tbcategory.N_category,tbproduct.N_seller, tbproduct.pricebuy,tbproduct.unit
FROM tbproduct,tbcategory WHERE tbproduct.Id_category = tbcategory.Id_category
ORDER BY tbproduct.Id_product";
ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();
if (DataReader.HasRows)
{
ClassConnection.dataTable = new DataTable();
ClassConnection.dataTable.Load(DataReader);
dataGridView1.DataSource = ClassConnection.dataTable;
FormatDataGridView();
DataReader.Close();
}
else
{
ClassConnection.dataTable = new DataTable();
ClassConnection.dataTable.Load(DataReader);
dataGridView1.DataSource = ClassConnection.dataTable;
FormatDataGridView();
DataReader.Close();
}
}
private void FormInventory_Load(object sender, EventArgs e)
{
ClassConnection.connectionDatabase();
ShowData();
EnabledFalse();
buttonDelete.Enabled = false;
buttonSave.Enabled = false;
buttonCancel.Enabled = false;
}
private void ButtonSave_Click(object sender, EventArgs e)
{
if (textBox1.Text == "")
{
return;
}
try
{
ClassConnection.connectionDatabase();
string sqls = "";
sqls = "INSERT INTO tbbuy(buyid,buytotal,buydate)";
sqls += " VALUES(@a,@b,@c)";
OleDbCommand yourCommands = new OleDbCommand(sqls, ClassConnection.connection);
yourCommands.Parameters.AddWithValue("a", txtID.Text);
yourCommands.Parameters.AddWithValue("b", Convert.ToDouble(lblTotal.Text));
yourCommands.Parameters.AddWithValue("c", dateTimepicker1.Value.ToShortDateString());
yourCommands.ExecuteNonQuery();
for (int i = 0; i <= listView1.Items.Count - 1; i++)
{
ClassConnection.connectionDatabase();
string Sql = @"INSERT INTO tbbuyitem(buyid,Id_product,buyitempricebuy,buyitemqty,buyitemtotal) VALUES(@a,@b,@c,@d,@e)";
ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
ClassConnection.command.Parameters.AddWithValue("a", txtID.Text);
ClassConnection.command.Parameters.AddWithValue("b", Convert.ToString(listView1.Items[i].SubItems[0].Text));
ClassConnection.command.Parameters.AddWithValue("c", Convert.ToInt32(listView1.Items[i].SubItems[3].Text));
ClassConnection.command.Parameters.AddWithValue("d", Convert.ToDouble(listView1.Items[i].SubItems[2].Text));
ClassConnection.command.Parameters.AddWithValue("e", Convert.ToDouble(listView1.Items[i].SubItems[4].Text));
ClassConnection.command.ExecuteNonQuery();
string sqlStrings = "";
sqlStrings = "UPDATE tbproduct SET unit = unit + @a WHERE Id_product= @id";
OleDbCommand myCommands = new OleDbCommand(sqlStrings, ClassConnection.connection);
myCommands.Parameters.AddWithValue("a", Convert.ToInt32(listView1.Items[i].SubItems[2].Text));
myCommands.Parameters.AddWithValue("id", listView1.Items[i].SubItems[0].Text);
myCommands.ExecuteNonQuery();
}
MessageBox.Show("บันทึกเรียบร้อยแล้ว", "ตรวจสอบ", MessageBoxButtons.OK);
//ClassSale.SalePrintID = TextBox1.Text.Trim();
ClassConnection.connectionDatabase();
ClearData();
EnabledFalse();
ButtonOpen.Enabled = true;
ButtonAdd.Enabled = false;
ButtonDelete.Enabled = false;
ButtonCancel.Enabled = false;
ButtonSave.Enabled = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
private void FormSale_Load(object sender, EventArgs e)
{
ClassConnection.connectionDatabase();
ClearData();
EnabledFalse();
ButtonOpen.Enabled = true;
ButtonAdd.Enabled = false;
ButtonDelete.Enabled = false;
ButtonCancel.Enabled = false;
ButtonSave.Enabled = false;
}
private void buttonCancel_Click(object sender, EventArgs e)
{
ClassConnection.connectionDatabase();
ClearData();
ShowData();
EnabledFalse();
buttonAdd.Enabled = true;
buttonEdit.Enabled = true;
buttonDelete.Enabled = false;
buttonSave.Enabled = false;
buttonCancel.Enabled = false;
}
private void buttonSearch_Click(object sender, EventArgs e)
{
ClassConnection.connectionDatabase();
String sql = @"SELECT * FROM tbcategory WHERE Id_category Like @name OR N_category Like @name ORDER BY Id_category";
ClassConnection.command = new OleDbCommand(sql, ClassConnection.connection);
ClassConnection.command.Parameters.AddWithValue("name", "%" + textBoxSearch.Text + "%");
OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();
ClassConnection.dataTable = new DataTable();
ClassConnection.dataTable.Load(DataReader);
dataGridView1.DataSource = ClassConnection.dataTable;
FormatDataGridView();
}
private void buttonSearch_Click(object sender, EventArgs e)
{
ClassConnection.connectionDatabase();
string Sql = @"SELECT tbproduct.Id_product, tbproduct.N_product, tbproduct.N_product_desc, tbproduct.N_seller,
tbcategory.N_category, tbproduct.pricebuy, tbproduct.unit
FROM tbproduct,tbcategory WHERE tbproduct.Id_category = tbcategory.Id_category
AND (tbproduct.Id_product Like @name
OR tbproduct.N_product Like @name)
ORDER BY tbproduct.Id_product";
ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
ClassConnection.command.Parameters.AddWithValue("name", "%" + textBoxSearch.Text + "%");//ค้นหา
OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();
ClassConnection.dataTable = new DataTable();
ClassConnection.dataTable.Load(DataReader);
dataGridView1.DataSource = ClassConnection.dataTable;
FormatDataGridView();
}
private void comboBox1_TextChanged(object sender, EventArgs e)
{
try
{
//comboBox1.Items.Clear();
ClassConnection.connectionDatabase();
string Sql = "SELECT Id_category FROM tbcategory WHERE N_category =@cat";
ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
ClassConnection.command.Parameters.AddWithValue("cat", comboBox1.Text);
OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();
DataReader.Read();
_idcat = DataReader["Id_category"].ToString();
DataReader.Close();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message.ToString());
comboBox1.Text = "";
}
}
private void MaxID()
{
int maxId = 0;
ClassConnection.connectionDatabase();
string Sql = @"SELECT MAX(saleid) FROM tbsale";
ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
//ClassConnection.DataReader = ClassConnection.Command.ExecuteReader();
try
{
maxId = Convert.ToInt32(ClassConnection.command.ExecuteScalar());
//MessageBox.Show(maxId.ToString());
}
catch (Exception ex)
{
maxId = 0;
}
txtID.Text = (maxId + 1).ToString("000");
}
private void showcombobox()
{
try
{
comboBox1.Items.Clear();
ClassConnection.connectionDatabase();
string Sql = "SELECT Distinct N_category FROM tbcategory ORDER BY N_category";
ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();
while (DataReader.Read())
{
comboBox1.Items.Add(DataReader["N_category"]);
}
DataReader.Close();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message.ToString());
comboBox1.Text = "";
}
}
private void buttonDelete_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("ต้องการลบข้อมูลจริงหรือไม่", "ยืนยันการลบ", MessageBoxButtons.YesNo);
if (result == DialogResult.Cancel || result == DialogResult.No)
{
return;
}
OleDbTransaction tr = ClassConnection.connection.BeginTransaction();
try
{
ClassConnection.connectionDatabase();
string Sql = @"DELETE FROM tbemployee WHERE Id_employee = @id";
ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
ClassConnection.command.Parameters.AddWithValue("id", textBoxID.Text);
int r = (int)ClassConnection.command.ExecuteNonQuery();
if (r == -1)
{
MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถลบข้อมูลได้", "ตรวจสอบ");
tr.Rollback();
}
else
{
MessageBox.Show("ลบข้อมูลแล้ว", "ตรวจสอบ");
tr.Commit();
ClearData();
ShowData();
}
}
catch
{
tr.Rollback();
}
finally
{
ClassConnection.connectionDatabase();
tr.Dispose();
}
}
private void buttonLogin_Click(object sender, EventArgs e)
{
if (textBoxUsername.Text == "")
{
textBoxUsername.Focus();
return;
}
else if (textBoxPassword.Text == "")
{
textBoxPassword.Focus();
return;
}
ClassConnection.connectionDatabase();
string sql = @"SELECT * FROM tbemployee WHERE username = @username AND password = @password";
ClassConnection.command = new OleDbCommand(sql, ClassConnection.connection);
ClassConnection.command.Parameters.AddWithValue("username", textBoxUsername.Text);
ClassConnection.command.Parameters.AddWithValue("password", textBoxPassword.Text);
OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();//รับค่าcommand
DataReader.Read();
if (DataReader.HasRows) //hasrow มีค่าซ้ำ
{
FormMain form = new FormMain(); //เชื่อมไปอีกฟอม
MessageBox.Show("ยินดีต้อนรับเข้าสู่ระบบ", "ตรวจสอบ", MessageBoxButtons.OK);
this.Hide();
form.ShowDialog();
}
else
{
MessageBox.Show("username หรือ password ไม่ถูกต้อง", "ตรวจสอบ", MessageBoxButtons.OK);
}
ClearData();
}
private void buttonSave_Click(object sender, EventArgs e)
{
if (textBoxID.Text == "")
{
textBoxID.Focus();
return;
}
else if (textBoxName.Text == "")
{
textBoxName.Focus();
return;
}
else if (textBoxCard.Text == "")
{
textBoxCard.Focus();
return;
}
else if (textBoxAddress.Text == "")
{
textBoxAddress.Focus();
return;
}
else if (textBoxTel.Text == "")
{
textBoxTel.Focus();
return;
}
else if (textBoxUsername.Text == "")
{
textBoxUsername.Focus();
return;
}
else if (textBoxPassword.Text == "")
{
textBoxPassword.Focus();
return;
}
if (ClassConnection.count == 1)
{
ClassConnection.connectionDatabase();
string Sql = @"SELECT * FROM tbemployee WHERE N_employee = '" + textBoxName.Text + "'";
ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();
if (DataReader.HasRows)
{
MessageBox.Show("มีข้อมูลซ้ำกรุณาตรวจสอบ", "ตรวจสอบ");
return;
}
else
{
DataReader.Close();
OleDbTransaction tr = ClassConnection.connection.BeginTransaction();
try
{
ClassConnection.connectionDatabase();
string SqlInsert = @"INSERT INTO tbemployee(Id_employee, N_employee, card_employee, address_employee, tel_employee, [username], [password])
VALUES(@a,@b,@c,@d,@e,@f,@g)";
ClassConnection.command = new OleDbCommand(SqlInsert, ClassConnection.connection);
ClassConnection.command.Parameters.AddWithValue("a", textBoxID.Text);
ClassConnection.command.Parameters.AddWithValue("b", textBoxName.Text);
ClassConnection.command.Parameters.AddWithValue("c", textBoxCard.Text);
ClassConnection.command.Parameters.AddWithValue("d", textBoxAddress.Text);
ClassConnection.command.Parameters.AddWithValue("e", textBoxTel.Text);
ClassConnection.command.Parameters.AddWithValue("f", textBoxUsername.Text);
ClassConnection.command.Parameters.AddWithValue("g", textBoxPassword.Text);
int result = ClassConnection.command.ExecuteNonQuery();
if (result == -1)
{
MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถเพิ่มข้อมูลได้", "ตรวจสอบ");
tr.Rollback();
}
else
{
MessageBox.Show("บันทึกข้อมูลแล้ว", "ตรวจสอบ");
tr.Commit();
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message.ToString());
tr.Rollback();
}
finally
{
ClassConnection.connectionDatabase();
tr.Dispose();
}
}
}
else if (ClassConnection.count == 2)
{
OleDbTransaction tr = ClassConnection.connection.BeginTransaction();
try
{
ClassConnection.connectionDatabase();
string SqlUpdate = @"UPDATE tbemployee SET N_employee =@b,card_employee =@c,address_employee =@d,tel_employee =@e,[username] =@f,[password] =@g WHERE Id_employee = @id";
ClassConnection.command = new OleDbCommand(SqlUpdate, ClassConnection.connection);
ClassConnection.command.Parameters.AddWithValue("b", textBoxName.Text);
ClassConnection.command.Parameters.AddWithValue("c", textBoxCard.Text);
ClassConnection.command.Parameters.AddWithValue("d", textBoxAddress.Text);
ClassConnection.command.Parameters.AddWithValue("e", textBoxTel.Text);
ClassConnection.command.Parameters.AddWithValue("f", textBoxUsername.Text);
ClassConnection.command.Parameters.AddWithValue("g", textBoxPassword.Text);
ClassConnection.command.Parameters.AddWithValue("id", textBoxID.Text);
int result = ClassConnection.command.ExecuteNonQuery();
if (result == -1)
{
MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถแก้ไขข้อมูลได้", "ตรวจสอบ");
tr.Rollback();
}
else
{
MessageBox.Show("แก้ไขข้อมูลแล้ว", "ตรวจสอบ");
tr.Commit();
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message.ToString());
tr.Rollback();
}
finally
{
ClassConnection.connectionDatabase();
tr.Dispose();
}
}
buttonSave.Enabled = false;
buttonCancel.Enabled = false;
buttonAdd.Enabled = true;
buttonEdit.Enabled = true;
buttonDelete.Enabled = true;
ClearData();
ShowData();
ClassConnection.count = 0;
EnabledFalse();
}
private void buttonSave_Click(object sender, EventArgs e)
{
if (textBoxID.Text == "")
{
textBoxID.Focus();
return;
}
else if (textBoxName.Text == "")
{
textBoxName.Focus();
return;
}
else if (textboxDesc.Text == "")
{
textBoxName.Focus();
return;
}
else if (comboBox1.Text == "")
{
comboBox1.Select();
return;
}
else if (textboxSeller.Text == "")
{
textBoxPriceBuy.Focus();
return;
}
else if (textBoxPriceBuy.Text == "")
{
textBoxPriceBuy.Focus();
return;
}
else if (textBoxNum.Text == "")
{
textBoxNum.Focus();
return;
}
if (ClassConnection.count == 1)
{
ClassConnection.connectionDatabase();
string Sql = @"SELECT * FROM tbproduct WHERE N_product = '" + textBoxName.Text + "'";
ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();
if (DataReader.HasRows)
{
MessageBox.Show("มีข้อมูลซ้ำกรุณาตรวจสอบ", "ตรวจสอบ");
return;
}
else
{
DataReader.Close();
OleDbTransaction tr = ClassConnection.connection.BeginTransaction();//คนแรกให้เข้าคนที่สองเข้ามาซ้ำไม่ได้
try
{
ClassConnection.connectionDatabase();
string SqlInsert = @"INSERT INTO tbproduct(Id_product, N_product, N_product_desc, Id_category, N_seller, pricebuy ,unit)
VALUES(@a,@b,@c,@d,@e,@f,@g)";
ClassConnection.command = new OleDbCommand(SqlInsert, ClassConnection.connection);
ClassConnection.command.Parameters.AddWithValue("a", textBoxID.Text);
ClassConnection.command.Parameters.AddWithValue("b", textBoxName.Text);
ClassConnection.command.Parameters.AddWithValue("c", textboxDesc.Text);
ClassConnection.command.Parameters.AddWithValue("d", _idcat);
ClassConnection.command.Parameters.AddWithValue("e", textboxSeller.Text);
ClassConnection.command.Parameters.AddWithValue("f", textBoxPriceBuy.Text);
ClassConnection.command.Parameters.AddWithValue("g", textBoxNum.Text);
int result = ClassConnection.command.ExecuteNonQuery();
if (result == -1)
{
MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถเพิ่มข้อมูลได้", "ตรวจสอบ");
tr.Rollback();
}
else
{
MessageBox.Show("บันทึกข้อมูลแล้ว", "ตรวจสอบ");
tr.Commit();
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message.ToString());
tr.Rollback();
}//หาสาเหตุ
finally
{
ClassConnection.connectionDatabase();
tr.Dispose();
}
}
}
else if (ClassConnection.count == 2)
{
OleDbTransaction tr = ClassConnection.connection.BeginTransaction();
try
{
ClassConnection.connectionDatabase();
string SqlUpdate = @"UPDATE tbproduct SET N_product =@b, N_product_desc =@c, Id_category =@d, N_seller =@e, pricebuy =@f,unit =@g WHERE Id_product = @id";
ClassConnection.command = new OleDbCommand(SqlUpdate, ClassConnection.connection);
ClassConnection.command.Parameters.AddWithValue("b", textBoxName.Text);
ClassConnection.command.Parameters.AddWithValue("c", textboxDesc.Text);
ClassConnection.command.Parameters.AddWithValue("d", _idcat);
ClassConnection.command.Parameters.AddWithValue("e", textboxSeller.Text);
ClassConnection.command.Parameters.AddWithValue("f", textBoxPriceBuy.Text);
ClassConnection.command.Parameters.AddWithValue("g", textBoxNum.Text);
ClassConnection.command.Parameters.AddWithValue("id", textBoxID.Text);
int result = ClassConnection.command.ExecuteNonQuery();
if (result == -1)
{
MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถแก้ไขข้อมูลได้", "ตรวจสอบ");
tr.Rollback();
}
else
{
MessageBox.Show("แก้ไขข้อมูลแล้ว", "ตรวจสอบ");
tr.Commit();
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message.ToString());
tr.Rollback();
}
finally
{
ClassConnection.connectionDatabase();
tr.Dispose();
}
}
buttonSave.Enabled = false;
buttonCancel.Enabled = false;
buttonAdd.Enabled = true;
buttonEdit.Enabled = true;
buttonDelete.Enabled = true;
ClearData();
ShowData();
ClassConnection.count = 0;
EnabledFalse();
}