private void WorkOrder_Load(object sender, EventArgs e)
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query;
try
{
query = "select * from products";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
using (var command = new MySqlCommand(query, returnConn))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
comboBox1.Items.Add(reader.GetString("productId"));
}
}
}
cmd.ExecuteNonQuery();
//comboBox1.Items.Add()
connection.CloseConnection();
}
catch (Exception ex)
{
MetroMessageBox.Show(this.MdiParent, ex.Message);
}
}
private void delete_Click(object sender, EventArgs e)
{
if (comboBox1.Text != "" && quantity.Text != "" && datereq.Text != "" && pronotes.Text != "")
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query;
query = ("delete from inv_productreq where productReqId = @id");
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.Parameters.AddWithValue("@id", reqid.Text);
cmd.ExecuteNonQuery();
connection.CloseConnection();
MetroMessageBox.Show(this.MdiParent, "Record Deleted SucessFully!");
DisplayData();
ClearData();
}
else
{
MetroMessageBox.Show(this.MdiParent, "Select Any Row!");
return;
}
}
private void DisplayData()
{
try
{
connection.OpenConnection();
MySqlConnection returnconn = new MySqlConnection();
returnconn = connection.GetConnection();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM totalin", returnconn);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt.DefaultView;
connection.CloseConnection();
}
catch (Exception ex)
{
MetroMessageBox.Show(this.MdiParent, ex.Message);
}
try
{
connection.OpenConnection();
MySqlConnection returnconn = new MySqlConnection();
returnconn = connection.GetConnection();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM totalout", returnconn);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
metroGrid1.DataSource = dt.DefaultView;
connection.CloseConnection();
}
catch (Exception ex)
{
MetroMessageBox.Show(this.MdiParent, ex.Message);
}
}
public void getDetails(string pid)
{
DBConnect conn = new DBConnect();
conn.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = conn.GetConnection();
MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM workorderreport where ProductID = '" + pid + "'", returnConn);
adapter.Fill(this.inv_itpDataSet.workorderreport);
}
private void button1_Click(object sender, EventArgs e)
{
if (comboBox1.Text != "" && quantity.Text != "" && datereq.Text != "" && pronotes.Text != "")
{
// string Request_ID = reqid.Text;
string ProductID = comboBox1.Text;
string Quantity = quantity.Text;
string RequstedDate = datereq.Value.ToString("yyyy-MM-dd");
string Product_Notes = pronotes.Text;
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query;
query = "INSERT INTO inv_productreq ( productId , quantity , requestDate, orderDate ,notes ) VALUES ( @2, @3, @4, @5,@6)";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
// cmd.Parameters.AddWithValue("@1", Request_ID);
cmd.Parameters.AddWithValue("@2", ProductID);
cmd.Parameters.AddWithValue("@3", Quantity);
cmd.Parameters.AddWithValue("@4", RequstedDate);
cmd.Parameters.AddWithValue("@5", DateTime.Now.ToString("yyyy/MM/dd"));
cmd.Parameters.AddWithValue("@6", Product_Notes);
cmd.ExecuteNonQuery();
connection.CloseConnection();
MetroMessageBox.Show(this.MdiParent, "Sucessfully Added!!");
DisplayData();
ClearData();
}
catch (Exception ex)
{
MetroMessageBox.Show(this.MdiParent, ex.Message);
}
}
else
{
MetroMessageBox.Show(this.MdiParent, "Insert the Values");
return;
}
}
private void button2_Click(object sender, EventArgs e)
{
if (comboBox1.Text != "" && quantity.Text != "" && datereq.Text != "" && pronotes.Text != "")
{
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query;
query = ("update inv_productreq set productId=@2 , quantity=@3 , requestDate=@4, notes=@5 where productReqId =@1");
MySqlCommand cmd = new MySqlCommand(query, returnConn);
string Request_ID = reqid.Text;
string ProductID = comboBox1.Text;
string Quantity = quantity.Text;
string RequstedDate = datereq.Value.ToString("yyyy-MM-dd");
string Product_Notes = pronotes.Text;
cmd.Parameters.AddWithValue("@1", Request_ID);
cmd.Parameters.AddWithValue("@2", ProductID);
cmd.Parameters.AddWithValue("@3", Quantity);
cmd.Parameters.AddWithValue("@4", RequstedDate);
cmd.Parameters.AddWithValue("@5", Product_Notes);
cmd.ExecuteNonQuery();
MetroMessageBox.Show(this.MdiParent, "Record Updated Sucessfully!");
connection.CloseConnection();
DisplayData();
ClearData();
}
catch (Exception ex)
{
MetroMessageBox.Show(this.MdiParent, ex.Message);
}
}
else
{
MetroMessageBox.Show(this.MdiParent, "Please Select Record to Update");
return;
}
}
public bool connectDB()
{
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
return(true);
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex);
return(false);
}
}
private void invoice()
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query;
query = "select Total from available";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
using (var command = new MySqlCommand(query, returnConn))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
//totalquantity.Items.Add(reader.GetString("productId"));
totalquantity.Text = reader.GetInt32("Total").ToString();
}
}
}
int available = Int32.Parse(totalquantity.Text) - Int32.Parse(pro_quantity.Text);
if (available < 1000)
{
int neededquantity = 1000 - available;
string query1;
query1 = "Insert into inv_productreq (productId, quantity, requestDate, orderDate,status,notes) Values (@1,@2,@3,@4,@5,@6)";
MySqlCommand cmd2 = new MySqlCommand(query1, returnConn);
cmd2.Parameters.AddWithValue("@1", pro_id.Text);
cmd2.Parameters.AddWithValue("@2", neededquantity);
cmd2.Parameters.AddWithValue("@3", DateTime.Now);
cmd2.Parameters.AddWithValue("@4", DateTime.Now.AddDays(-15));
cmd2.Parameters.AddWithValue("@5", "Pending");
cmd2.Parameters.AddWithValue("@6", "Urgent");
cmd2.ExecuteNonQuery();
MetroMessageBox.Show(this.MdiParent, "Sucessfully WorkOrder sent");
}
connection.CloseConnection();
}
private void button5_Click(object sender, EventArgs e)
{
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query;
query = (" update inv_newitems set ReceivedDate=@receiveddate,BatchNo=@batchno,ManufacturedDate=@manufactureddate,ExpiredDate=@expireddate,ProductID=@productid,ProductName=@productname,Quantity=@quantity,MaximumRetailPrice=@ppunit,UnitPrice=@unitpp,Packed=@packed where ID=@id");
MySqlCommand cmd = new MySqlCommand(query, returnConn);
//newly
cmd.Parameters.AddWithValue("@id", lbl2.Text);
cmd.Parameters.AddWithValue("@receiveddate", txt_receiveddate.Value.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@batchno", batchno.Text);
cmd.Parameters.AddWithValue("@manufactureddate", manufactureddate.Value.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@expireddate", expireddate.Value.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@productid", productid.Text);
cmd.Parameters.AddWithValue("@productname", textBox1.Text);
cmd.Parameters.AddWithValue("@quantity", quantity.Text);
cmd.Parameters.AddWithValue("@ppunit", priceperunit.Text);
cmd.Parameters.AddWithValue("@unitpp", unitprice.Text);
if (box.Checked == true)
{
cmd.Parameters.AddWithValue("@packed", box.Text);
}
else
{
cmd.Parameters.AddWithValue("@packed", packet.Text);
}
cmd.ExecuteNonQuery();
MetroMessageBox.Show(this.MdiParent, "Record Updated Sucessfully");
connection.CloseConnection();
DisplayData();
ClearData();
}
catch (Exception ex)
{
MetroMessageBox.Show(this.MdiParent, ex.Message);
}
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
MySqlCommand cmd = new MySqlCommand("select *from inv_newitems where BatchNo like '" + txtid.Text + "%' ", returnConn);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
connection.CloseConnection();
}
catch (Exception ex)
{
MetroMessageBox.Show(this.MdiParent, ex.Message);
}
}
private void txtorder_TextChanged(object sender, EventArgs e)
{
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
// String query = "select * from INV_ITP.inv_order where orderid like '%" + txtorder.Text + "%'";
MySqlCommand cmd = new MySqlCommand("select * from orders where invoiceNo like '" + txtorder.Text + "%' ", returnConn);
// MessageBox.Show(query);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
connection.CloseConnection();
}
catch (Exception ex) {
MetroMessageBox.Show(this.MdiParent, ex.Message);
}
}
private void DisplayData()
{
connection.OpenConnection();
MySqlConnection returnconn = new MySqlConnection();
returnconn = connection.GetConnection();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM inv_productreq", returnconn);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
connection.CloseConnection();
dataGridView1.Columns[0].HeaderText = "Product Request ID";
dataGridView1.Columns[1].HeaderText = "Product ID";
dataGridView1.Columns[2].HeaderText = "Quantity";
dataGridView1.Columns[3].HeaderText = "Request Date";
dataGridView1.Columns[4].HeaderText = "Order Date";
dataGridView1.Columns[5].HeaderText = "Status";
dataGridView1.Columns[6].HeaderText = "Notes";
}
private void button4_Click(object sender, EventArgs e)
{
if (txt_receiveddate.Text != "" && batchno.Text != "" && productid.Text != "" && manufactureddate.Text != "" && expireddate.Text != "" && textBox1.Text != "" && priceperunit.Text != "" && quantity.Text != "")
{
// string id = txt_id.Text;
string receivedate = txt_receiveddate.Value.ToString("yyyy-MM-dd");
string Batchno = batchno.Text;
string Productid = productid.Text;
string Manufactureddate = manufactureddate.Value.ToString("yyyy-MM-dd");
string Expireddate = expireddate.Value.ToString("yyyy-MM-dd");
string Productname = textBox1.Text;
string Priceperunit = priceperunit.Text;
string Quantity = quantity.Text;
string Box = box.Text;
string Packet = packet.Text;
string UnitPrice = unitprice.Text;
try
{
DBConnect conn = new DBConnect();
conn.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = conn.GetConnection();
string query = ("insert into inv_newitems (ReceivedDate,BatchNo,ManufacturedDate,ExpiredDate,ProductID,ProductName,Quantity,MaximumRetailPrice,UnitPrice,Packed) values(@receiveddate,@batchno,@manufactureddate,@expireddate,@productid,@productname,@quantity,@ppunit,@unitpp,@packed)");
MySqlCommand cmd = new MySqlCommand(query, returnConn);
//cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@receiveddate", receivedate);
cmd.Parameters.AddWithValue("@batchno", Batchno);
cmd.Parameters.AddWithValue("@productid", Productid);
cmd.Parameters.AddWithValue("@manufactureddate", Manufactureddate);
if (manufactureddate.Value != expireddate.Value)
{
cmd.Parameters.AddWithValue("@expireddate", Expireddate);
}
else
{
MetroMessageBox.Show(this.MdiParent, "Manufactured Date and Expired Date can't Be Same");
return;
}
if (manufactureddate.Value > expireddate.Value)
{
MetroMessageBox.Show(this.MdiParent, "Expired Date Should Be Future Date of Manufacture Date");
return;
}
cmd.Parameters.AddWithValue("@productname", Productname);
cmd.Parameters.AddWithValue("@quantity", Quantity);
cmd.Parameters.AddWithValue("@ppunit", Priceperunit);
cmd.Parameters.AddWithValue("@unitpp", UnitPrice);
if (box.Checked == true)
{
cmd.Parameters.AddWithValue("@packed", Box);
}
else if (packet.Checked == true)
{
cmd.Parameters.AddWithValue("@packed", Packet);
}
//cmd.Parameters.AddWithValue("@packed", packet.Text);
cmd.ExecuteNonQuery();
conn.CloseConnection();
MetroMessageBox.Show(this.MdiParent, "Added Successfully");
DisplayData();
ClearData();
}
catch (Exception ex)
{
MetroMessageBox.Show(this.MdiParent, ex.Message);
}
}
else
{
MetroMessageBox.Show(this.MdiParent, "Insert Values");
}
}
private void pro_id_TextChanged(object sender, EventArgs e)
{
try{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "SELECT * FROM products WHERE productId = '" + pro_id.Text + "'";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
using (MySqlDataReader read = cmd.ExecuteReader())
{
while (read.Read())
{
pro_name.Text = read.GetString("name").ToString();
//txt_amound.Text = read.GetString().ToString();
}
}
}
catch (Exception ex)
{
MetroMessageBox.Show(this.MdiParent, ex.Message);
//MessageBox.Show(ex.Message);
}
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query1 = "SELECT * FROM inv_newitems WHERE productId = '" + pro_id.Text + "'";
MySqlCommand cmd1 = new MySqlCommand(query1, returnConn);
using (MySqlDataReader read = cmd1.ExecuteReader())
{
while (read.Read())
{
pro_amound.Text = read.GetString("MaximumRetailPrice").ToString();
}
}
}
catch (Exception ex)
{
MetroMessageBox.Show(this.MdiParent, ex.Message);
//MessageBox.Show(ex.Message);
}
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query1 = "SELECT * FROM available WHERE productID = '" + pro_id.Text + "'";
MySqlCommand cmd1 = new MySqlCommand(query1, returnConn);
using (MySqlDataReader read = cmd1.ExecuteReader())
{
while (read.Read())
{
quantityy.Text = read.GetString("Total").ToString();
}
}
}
catch (Exception ex)
{
MetroMessageBox.Show(this.MdiParent, "ex");
//MessageBox.Show(ex.Message);
}
}
private void button1_Click(object sender, EventArgs e)
{
if (pro_id.Text != "" && pro_name.Text != "" && pro_quantity.Text != "" && pro_amound.Text != "")
{
try
{
if (status.Text == "Approved")
{
//MessageBox.Show(pro_quantity.Text);
// MessageBox.Show(quantityy.Text);
if ((pro_id.Text != "" && pro_name.Text != "" && pro_quantity.Text != "" && pro_amound.Text != ""))
{
// MessageBox.Show("if success");
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query;
query = "INSERT INTO inv_inventoryout (orderid, orderdate, deliverydate, productid, productname, quantity, unitprice, status) VALUES (@1, @2, @3, @4, @5, @6, @7, @8)";
//query = "insert into inv_inventoryout(orderid,orderdate,deliverydate,productid,productname,quantity,unitprice,status) values (@1,@2,@3,@4,@5,@6,@7,@8)";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.Parameters.AddWithValue("@1", txitid.Text);
cmd.Parameters.AddWithValue("@2", date_order.Value.ToString("yyyy/MM/dd"));
cmd.Parameters.AddWithValue("@3", date_delivery.Value.ToString("yyyy/MM/dd"));
cmd.Parameters.AddWithValue("@4", pro_id.Text);
cmd.Parameters.AddWithValue("@5", pro_name.Text);
cmd.Parameters.AddWithValue("@6", Convert.ToInt32(pro_quantity.Text));
cmd.Parameters.AddWithValue("@7", Convert.ToInt32(pro_amound.Text));
cmd.Parameters.AddWithValue("@8", status.Text);
cmd.ExecuteNonQuery();
MetroMessageBox.Show(this.MdiParent, "Record Updated Sucessfully!");
int quantity1 = Int32.Parse(pro_quantity.Text);
int quantity2 = Int32.Parse(quantityy.Text);
int quantity3 = quantity2 - quantity1;
//MessageBox.Show("Converted");
if (quantity3 < 0)
{
MetroMessageBox.Show(this.MdiParent, "Not Having Enough Quantity! Sent a Work Order Request");
//invoice();
}
connection.CloseConnection();
invoice();
DisplayData();
ClearData();
}
catch (Exception ex)
{
MetroMessageBox.Show(this, ex.Message);
}
}
else
{
MetroMessageBox.Show(this.MdiParent, "Select the Field to Update");
}
}
}
catch (Exception ex)
{
MetroMessageBox.Show(this.MdiParent, ex.Message);
}
}
}