public void execute(DataGridView datagridview, DataGridView datagridview2, string amount, string user_name, int shop)
{
MySqlConnection connection = DBConnection.getConn();
for (int i = 0; i < datagridview.Rows.Count; i++)
{
string currentstock = "";
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT stock_shop_" + shop + " FROM pharmacy_management.product WHERE product_code = '" + Convert.ToString(datagridview.Rows[i].Cells[1].Value) + "'";
try
{
connection.Open();
MySqlDataReader myReader = cmd.ExecuteReader();
while (myReader.Read())
{
currentstock = Convert.ToString(Convert.ToInt32(myReader.GetString("stock_shop_" + shop)));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
/*string stock_requested = Convert.ToString(datagridview.Rows[i].Cells[3].Value);
* MessageBox.Show(stock_requested);
* MessageBox.Show(currentstock);
* int value = Convert.ToInt32(currentstock) - Convert.ToInt32(stock_requested);
* MessageBox.Show(Convert.ToString(value));*/
string newstock = Convert.ToString(Convert.ToInt32(currentstock) - Convert.ToInt32(datagridview.Rows[i].Cells[3].Value));
string updatequery = "UPDATE pharmacy_management.product SET stock_shop_" + shop + " = '" + newstock + "' WHERE product_code = '" + Convert.ToString(datagridview.Rows[i].Cells[1].Value) + "' ";
MySqlCommand cmdsampleinventory = new MySqlCommand(updatequery, connection);
try
{
connection.Open();
int myread2er = cmdsampleinventory.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
string details = "";
for (int i = 0; i < datagridview.Rows.Count; i++)
{
string product_code = "";
string product = "";
string quantity = "";
string price = "";
string total = "";
product_code = Convert.ToString(datagridview.Rows[i].Cells[1].Value);
product = Convert.ToString(datagridview.Rows[i].Cells[2].Value);
quantity = Convert.ToString(datagridview.Rows[i].Cells[3].Value);
price = Convert.ToString(datagridview.Rows[i].Cells[4].Value);
total = Convert.ToString(datagridview.Rows[i].Cells[5].Value);
string newname = "";
try
{
string[] names = product.Split();
foreach (string item in names)
{
newname += item + "_";
}
}
catch { }
string outcome = newname.Substring(0, newname.Length - 1);
details += product_code + " " + outcome + " " + quantity + " " + price + " " + total + "/";
}
try
{
DateTime current = DateTime.Now;
string date = "";
string time = "";
date = current.ToShortDateString();
time = current.ToShortTimeString();
connection.Open();
string insertquery = "INSERT INTO pharmacy_management.invoice VALUES (null,@user,@shop,@details,@date,@time)";
MySqlCommand command = new MySqlCommand(insertquery, connection);
command.Parameters.AddWithValue("@user", user_name);
command.Parameters.AddWithValue("@shop", shop);
command.Parameters.AddWithValue("@details", details);
command.Parameters.AddWithValue("@date", date);
command.Parameters.AddWithValue("@time", time);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
Invoice frm = new Invoice(user_name);
for (int i = 0; i < datagridview.RowCount; i++)
{
frm.dataGridView3.Rows.Add();
for (int j = 2; j < 6; j++)
{
frm.dataGridView3.Rows[i].Cells[j - 2].Value = datagridview.Rows[i].Cells[j].Value;
//MessageBox.Show(Convert.ToString(this.datagridview.Rows[i].Cells[j].Value));
}
}
MySqlCommand cmd2 = connection.CreateCommand();
cmd2.CommandText = "SELECT * FROM pharmacy_management.invoice WHERE invoice_number = (SELECT MAX(invoice_number) FROM pharmacy_management.invoice)";
string invoice_num = "";
try
{
connection.Open();
MySqlDataReader myReader2 = cmd2.ExecuteReader();
while (myReader2.Read())
{
invoice_num = Convert.ToString(Convert.ToInt32(myReader2.GetString("Invoice_number")));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
frm.invcnumtxtx.Text = invoice_num;
frm.ttlbilltxt.Text = amount;
string selectQuery = "SELECT product_code, name, description, category, stock_shop_" + shop + ",unit_price, re_order_size FROM pharmacy_management.product ";
MySqlDataAdapter adapter = new MySqlDataAdapter(selectQuery, connection);
DataTable dt = new DataTable();
adapter.Fill(dt);
datagridview2.DataSource = dt;
datagridview.Rows.Clear();
frm.Show();
DBConnection.returnConn(connection);
connection = null;
}