private void deleteProduct(string pid)
{
DialogResult dialogResult = MessageBox.Show("ต้องการลบสินค้าหรือไม่", "", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
string sql = "";
sql = "DELETE FROM purchase_order WHERE pid = @pid";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@pid", pid);
cmd.CommandType = System.Data.CommandType.Text;
iFt.query(cmd, con);
sql = "DELETE FROM product WHERE pid = @pid";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@pid", pid);
cmd.CommandType = System.Data.CommandType.Text;
iFt.query(cmd, con);
sql = "DELETE FROM " + ItemTable[Page] + " WHERE pid = @pid";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@pid", pid);
cmd.CommandType = System.Data.CommandType.Text;
iFt.query(cmd, con);
}
else if (dialogResult == DialogResult.No)
{
//do something else
}
}
private void insertItem()
{
string sql = "";
// product
sql = "INSERT INTO product VALUES(@pid, @qty, @price)";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@pid", prPid.Text);
cmd.Parameters.AddWithValue("@qty", 0);
cmd.Parameters.AddWithValue("@price", prPrice.Text);
cmd.CommandType = System.Data.CommandType.Text;
iFt.query(cmd, con);
// child of product
switch (page)
{
case 0:
sql = "INSERT INTO len VALUES(@pid, @brand, @type, @sight, @sph, @cyl)";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@pid", prPid.Text);
cmd.Parameters.AddWithValue("@brand", prBrand.Text);
cmd.Parameters.AddWithValue("@type", prTypeLen.Text);
cmd.Parameters.AddWithValue("@sight", prSightLen.Text);
cmd.Parameters.AddWithValue("@sph", prSPHLen.Text);
cmd.Parameters.AddWithValue("@cyl", prCYLLen.Text);
break;
case 1:
sql = "INSERT INTO frame VALUES(@pid, @brand, @class, @color)";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@pid", prPid.Text);
cmd.Parameters.AddWithValue("@brand", prBrand.Text);
cmd.Parameters.AddWithValue("@class", prClass.Text);
cmd.Parameters.AddWithValue("@color", prColor.Text);
break;
case 2:
sql = "INSERT INTO contact_len VALUES(@pid, @brand, @duration, @sight, @sph)";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@pid", prPid.Text);
cmd.Parameters.AddWithValue("@brand", prBrand.Text);
cmd.Parameters.AddWithValue("@duration", prDuration.Text);
cmd.Parameters.AddWithValue("@sight", prSightContact.Text);
cmd.Parameters.AddWithValue("@sph", prSPHContact.Text);
break;
}
iFt.query(cmd, con);
}
private void updateQty()
{
string sql = "";
int qty = 0;
qty = Int32.Parse(txtQty.Text) + Int32.Parse(prQty.Text);
sql = "UPDATE product ";
sql += "SET qty = @qty ";
sql += "WHERE pid = @pid";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@pid", txtPid.Text);
cmd.Parameters.AddWithValue("@qty", qty);
cmd.CommandType = System.Data.CommandType.Text;
iFt.query(cmd, con);
}
private void BTNSubmit_Click(object sender, EventArgs e)
{
string sql = "";
string date = "";
string rid = "";
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
rid = getReceiptId();
date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
sql = "INSERT INTO receipt VALUES (@rid, @date, @money)";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@rid", rid);
cmd.Parameters.AddWithValue("@date", date);
cmd.Parameters.AddWithValue("@money", txtGet.Text);
cmd.CommandType = System.Data.CommandType.Text;
iFt.query(cmd, con);
foreach (DataGridViewRow row in dgvProduct.Rows)
{
string curPid = row.Cells["ID"].Value.ToString();
string curQty = row.Cells["qty"].Value.ToString();
string curPrice = row.Cells["price"].Value.ToString();
sql = "INSERT INTO receipt_detail VALUES (@rid, @pid, @qty, @price)";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@rid", rid);
cmd.Parameters.AddWithValue("@pid", curPid);
cmd.Parameters.AddWithValue("@qty", curQty);
cmd.Parameters.AddWithValue("@price", Int32.Parse(curPrice) / Int32.Parse(curQty));
cmd.CommandType = System.Data.CommandType.Text;
iFt.query(cmd, con);
sql = "UPDATE product SET qty = qty - @qty WHERE pid = @pid";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@pid", curPid);
cmd.Parameters.AddWithValue("@qty", curQty);
cmd.CommandType = System.Data.CommandType.Text;
iFt.query(cmd, con);
}
resetAll();
}
private void BTNEdit_Click(object sender, EventArgs e)
{
string sql = "";
sql = "UPDATE product ";
sql += "SET price = @price ";
sql += "WHERE pid = @pid";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@pid", prPid.Text);
cmd.Parameters.AddWithValue("@price", prPrice.Text);
cmd.CommandType = System.Data.CommandType.Text;
iFt.query(cmd, con);
switch (Page)
{
case 0:
sql = "UPDATE len ";
sql += "SET brand = @brand, type = @type, sight = @sight, sph = @sph, cyl = @cyl ";
sql += "WHERE pid = @pid";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@pid", prPid.Text);
cmd.Parameters.AddWithValue("@brand", prBrand.Text);
cmd.Parameters.AddWithValue("@type", prTypeLen.Text);
cmd.Parameters.AddWithValue("@sight", prSightLen.Text);
cmd.Parameters.AddWithValue("@sph", prSPHLen.Text);
cmd.Parameters.AddWithValue("@cyl", prCYLLen.Text);
cmd.CommandType = System.Data.CommandType.Text;
iFt.query(cmd, con);
break;
case 1:
sql = "UPDATE frame ";
sql += "SET brand = @brand, class = @class, color = @color ";
sql += "WHERE pid = @pid";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@pid", prPid.Text);
cmd.Parameters.AddWithValue("@brand", prBrand.Text);
cmd.Parameters.AddWithValue("@class", prClass.Text);
cmd.Parameters.AddWithValue("@color", prColor.Text);
cmd.CommandType = System.Data.CommandType.Text;
iFt.query(cmd, con);
break;
case 2:
sql = "UPDATE contact_len ";
sql += "SET brand = @brand, duration = @duration, sight = @sight, sph = @sph ";
sql += "WHERE pid = @pid";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@pid", prPid.Text);
cmd.Parameters.AddWithValue("@brand", prBrand.Text);
cmd.Parameters.AddWithValue("@duration", prDuration.Text);
cmd.Parameters.AddWithValue("@sight", prSightContact.Text);
cmd.Parameters.AddWithValue("@sph", prSPHContact.Text);
cmd.CommandType = System.Data.CommandType.Text;
iFt.query(cmd, con);
break;
}
this.Close();
}