private void loginBtn_Click(object sender, EventArgs e)
{
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@username", usernameTxt.Text));
paramList.Add(new MySqlParameter("@password", passwordTxt.Text));
int returnedRowCount = DatabaseHandler.returnRowCount("SELECT*FROM USER WHERE username=@username and password=@password", paramList);
if (returnedRowCount == 1)
{
GlobalLoginData.username = usernameTxt.Text;
paramList.Clear();
paramList.Add(new MySqlParameter("@username", usernameTxt.Text));
GlobalLoginData.userRole = DatabaseHandler.returnOneValue("SELECT role FROM USER WHERE username = @username", paramList, "role");
GlobalLoginData.Name = DatabaseHandler.returnOneValue("SELECT member_name FROM USER WHERE username = @username", paramList, "member_name");
this.Hide();
Dashboard dash = new Dashboard();
dash.Show();
}
else
{
MessageBox.Show("Invalid Credentials, Please Try Again!");
}
}
private void AddItemBtn_Click(object sender, EventArgs e)
{
Console.WriteLine("In Add Btn: Current Index1: " + composition_dataGridView.Rows.Count);
string itemCode = addmaterialCodeTxt.Text;
string itemQty = addmaterialQty.Text;
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@itemCode", itemCode));
int returnedRowCount = DatabaseHandler.returnRowCount("SELECT*FROM raw_material WHERE material_id=@itemCode", paramList);
if (returnedRowCount != 0)
{
//Get Item Name from DB
paramList.Clear();
paramList.Add(new MySqlParameter("@itemCode", itemCode));
string itemName = DatabaseHandler.returnOneValue("SELECT name as 'Material Name' from raw_material where material_id=@itemCode", paramList, "Material Name");
//Add to composition_dataGridView
// int index = composition_dataGridView.DisplayedRowCount(true);
composition_dataGridView.Rows.Add(itemCode, itemName, itemQty);
// composition_dataGridView.Rows[index - 1].Cells[0].Value = itemCode;
// composition_dataGridView.Rows[index - 1].Cells[1].Value = itemName;
//composition_dataGridView.Rows[index - 1].Cells[2].Value = itemQty;
}
else
{
MessageBox.Show("Invalid Material Code!");
}
Console.WriteLine("In Add Btn: Current Index2: " + composition_dataGridView.Rows.Count);
}
private void populatematerialGrid(DataGridView dataGridView)
{
try
{
string val = dataGridView.SelectedRows[0].Cells["BOM ID"].Value.ToString();
string selectStatement = "SELECT bom_item.material_id as 'Material ID', bom_item.qty as 'Quantity', raw_material.name as 'Material Name' FROM raw_material INNER JOIN bom_item ON bom_item.material_id = raw_material.material_id WHERE bom_item.bom_id = '" + val + "' ";
DatabaseHandler.populateGridViewWithBinding(selectStatement, dataGridView6);
dataGridView7.Rows.Clear();
string itemCode = null;
string itemQty = null;
string itemName = null;
Console.WriteLine("Row count " + dataGridView6.Rows.Count);
for (int i = 0; i < dataGridView6.Rows.Count - 1; i++)
{
try
{
itemCode = dataGridView6.Rows[i].Cells[0].Value.ToString();
itemQty = dataGridView6.Rows[i].Cells[1].Value.ToString();
itemName = dataGridView6.Rows[i].Cells[2].Value.ToString();
List <MySqlParameter> paramlist = new List <MySqlParameter>();
paramlist.Clear();
paramlist.Add(new MySqlParameter("@itemCode", itemCode));
paramlist.Add(new MySqlParameter("@value", itemQty));
Console.WriteLine("required qty " + itemQty);
string queryGetQtyCondition = "SELECT IF(qty >= @value,'Yes','No') AS possibility FROM raw_material WHERE material_id = @itemCode";
string possibility = DatabaseHandler.returnOneValue(queryGetQtyCondition, paramlist, "possibility");
if (string.Compare(possibility, "No") != 1)
{
List <MySqlParameter> paramlist2 = new List <MySqlParameter>();
paramlist2.Clear();
paramlist2.Add(new MySqlParameter("@itemCode", itemCode));
string query = "SELECT qty FROM raw_material WHERE material_id = @itemCode";
string available_qty = DatabaseHandler.returnOneValue(query, paramlist2, "qty");
Console.WriteLine("Available qty " + available_qty);
//Add to dataViewGrid7
dataGridView7.Rows.Add(itemCode, itemName, (float.Parse(itemQty) - float.Parse(available_qty)));
}
}
catch (Exception err)
{
Console.WriteLine(err);
}
}
}
catch (Exception err)
{
Console.WriteLine(err);
}
}
private void populateItemGrid(DataGridView dataGridView)
{
try
{
string val = dataGridView.SelectedRows[0].Cells["Order #"].Value.ToString();
string select = "SELECT itemorder_item.io_id as 'Order #', itemorder_item.item_id as 'Item Code', item.name as 'Item Name', itemorder_item.qty as 'Qty' FROM itemorder_item INNER JOIN item ON itemorder_item.item_id = item.item_id WHERE itemorder_item.io_id = '" + val + "'";
DatabaseHandler.populateGridViewWithBinding(select, dataGridView6);
//populate dataGridView7
dataGridView7.Rows.Clear();
string itemCode = null;
string itemQty = null;
string itemName = null;
for (int i = 0; i < dataGridView6.Rows.Count - 1; i++)
{
try
{
itemCode = dataGridView6.Rows[i].Cells[1].Value.ToString();
itemQty = dataGridView6.Rows[i].Cells[3].Value.ToString();
itemName = dataGridView6.Rows[i].Cells[2].Value.ToString();
List <MySqlParameter> paramlist = new List <MySqlParameter>();
paramlist.Clear();
paramlist.Add(new MySqlParameter("@itemCode", itemCode));
paramlist.Add(new MySqlParameter("@value", itemQty));
string queryGetQtyCondition = "SELECT IF(qty >= @value,'Yes','No') AS possibility FROM item WHERE item_id = @itemCode";
string possibility = DatabaseHandler.returnOneValue(queryGetQtyCondition, paramlist, "possibility");
if (string.Compare(possibility, "Yes") != 0)
{
List <MySqlParameter> paramlist2 = new List <MySqlParameter>();
paramlist2.Clear();
paramlist2.Add(new MySqlParameter("@itemCode", itemCode));
string query = "SELECT qty FROM item WHERE item_id = @itemCode";
string available_qty = DatabaseHandler.returnOneValue(query, paramlist2, "qty");
Console.WriteLine("Available qty " + available_qty);
//Add to dataViewGrid7
int index2 = dataGridView7.DisplayedRowCount(true);
dataGridView7.Rows.Add(itemCode, itemName, (Int32.Parse(itemQty) - Int32.Parse(available_qty)));
}
}
catch (Exception)
{
}
}
}
catch (Exception)
{
}
}
private void addItemBtn_Click(object sender, EventArgs e)
{
string itemCode = addItemCodeTxt.Text;
string itemQty = addItemQty.Text;
int i = dataGridView4.Rows.Count;
for (int row = 0; row < i - 1; row++)
{
if (dataGridView4.Rows[row].Cells[0].Value.ToString() == itemCode)
{
MessageBox.Show("Item already entered !!!");
return;
}
}
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@itemCode", itemCode));
int returnedRowCount = DatabaseHandler.returnRowCount("SELECT * FROM raw_material WHERE material_id = @itemCode", paramList);
int returnedRowCount2 = DatabaseHandler.returnRowCountWithoutParams("SELECT * FROM supplier_material WHERE material_id='" + itemCode + "' AND supplier_id='" + selectedSupplier + "'");
if (returnedRowCount != 0 && returnedRowCount2 != 0)
{
//Get Item Name from DB
paramList.Clear();
paramList.Add(new MySqlParameter("@itemCode", itemCode));
string itemName = DatabaseHandler.returnOneValue("SELECT name as 'Material Name' from raw_material where material_id=@itemCode", paramList, "Material Name");
String unitPrice = DatabaseHandler.returnOneValueWithoutParams("SELECT unit_price as 'Material Price' from supplier_material where material_id='" + itemCode + "' AND supplier_id='" + selectedSupplier + "'", "Material Price");
//Add to dataViewGrid4
int index = dataGridView4.Rows.Count;
dataGridView4.Rows.Add(itemCode, itemName, itemQty, unitPrice);
Console.WriteLine("In Add Btn: Current Index: " + index);
supplierComboBox.Enabled = false;
}
else if (returnedRowCount == 0)
{
MessageBox.Show("Invalid Item Code!");
}
else
{
MessageBox.Show("Selected supplier doesn't offer that item!");
}
Console.WriteLine("In Add Btn: Current Index2: " + dataGridView4.DisplayedRowCount(true));
}
private void addItemBtn_Click(object sender, EventArgs e)
{
string itemCode = addItemCodeTxt.Text;
string itemQty = addItemQty.Text;
int i = dataGridView4.Rows.Count;
for (int row = 0; row < i - 1; row++)
{
if (dataGridView4.Rows[row].Cells[0].Value.ToString() == itemCode)
{
MessageBox.Show("Item already entered !!!");
return;
}
}
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@itemCode", itemCode));
int returnedRowCount = DatabaseHandler.returnRowCount("SELECT*FROM item WHERE item_id=@itemCode", paramList);
if (returnedRowCount != 0)
{
//Get Item Name from DB
paramList.Clear();
paramList.Add(new MySqlParameter("@itemCode", itemCode));
string itemName = DatabaseHandler.returnOneValue("SELECT name as 'Item Name' from item where item_id=@itemCode", paramList, "Item Name");
//Add to dataViewGrid4
int index = dataGridView4.DisplayedRowCount(true);
dataGridView4.Rows.Add(itemCode, itemName, itemQty);
Console.WriteLine("In Add Btn: Current Index: " + index);
}
else
{
MessageBox.Show("Invalid Item Code!");
}
}
private void addBtn_Click(object sender, EventArgs e)
{
string itemCode;
string itemQty;
string itemName;
Console.WriteLine("In Add Btn: Current Index1: " + dataGridView4.Rows.Count);
try
{
itemCode = dataGridView5.SelectedRows[0].Cells["Item Code"].Value.ToString();
}
catch (Exception)
{
itemCode = null;
}
if (itemCode == null)
{
MessageBox.Show("Invalid Selection!");
}
else
{
int i = dataGridView4.Rows.Count;
for (int row = 0; row < i - 1; row++)
{
if (dataGridView4.Rows[row].Cells[0].Value.ToString() == itemCode)
{
MessageBox.Show("Item already entered !!!");
return;
}
}
itemQty = qtyTxt.Text;
itemName = dataGridView5.SelectedRows[0].Cells["Item Name"].Value.ToString();
List <MySqlParameter> paramlist = new List <MySqlParameter>();
paramlist.Clear();
paramlist.Add(new MySqlParameter("@itemCode", itemCode));
paramlist.Add(new MySqlParameter("@value", itemQty));
string queryGetQtyCondition = "SELECT IF(qty >= @value,'Yes','No') AS possibility FROM item WHERE item_id = @itemCode";
string possibility = DatabaseHandler.returnOneValue(queryGetQtyCondition, paramlist, "possibility");
if (string.Compare(possibility, "Yes") != 0)
{
List <MySqlParameter> paramlist2 = new List <MySqlParameter>();
paramlist2.Clear();
paramlist2.Add(new MySqlParameter("@itemCode", itemCode));
string query = "SELECT qty FROM item WHERE item_id = @itemCode";
string available_qty = DatabaseHandler.returnOneValue(query, paramlist2, "qty");
Console.WriteLine("Availabel qty " + available_qty);
//Add to dataViewGrid7
dataGridView7.Rows.Add(itemCode, itemName, (Int32.Parse(itemQty) - Int32.Parse(available_qty)));
}
//Add to dataViewGrid4
dataGridView4.Rows.Add(itemCode, itemName, itemQty);
Console.WriteLine("In Add Btn: Current Index2: " + dataGridView4.Rows.Count);
itemNameTxt.Clear();
qtyTxt.Clear();
}
}
private void manualSubstractBtn_Click(object sender, EventArgs e)
{
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@itemCode", manualProductId.Text));
int returnedRowCount = DatabaseHandler.returnRowCount("SELECT * FROM item WHERE item_id = @itemCode", paramList);
int returnedRowCount2 = DatabaseHandler.returnRowCount("SELECT * FROM raw_material WHERE material_id = @itemCode", paramList);
if (returnedRowCount == 1)
{
try
{
List <MySqlParameter> paramList3 = new List <MySqlParameter>();
paramList3.Clear();
paramList3.Add(new MySqlParameter("@itemCode", manualProductId.Text));
paramList3.Add(new MySqlParameter("@value", manualQty.Text));
string queryGetQtyCondition = "SELECT IF(qty >= @value,'Yes','No') AS possibility FROM item WHERE item_id = @itemCode";
string possibility = DatabaseHandler.returnOneValue(queryGetQtyCondition, paramList3, "possibility");
Console.WriteLine("String Possibility " + possibility);
if (string.Compare(possibility, "Yes") == 0)
{
Console.WriteLine("String Possibility Inside If ");
try
{
List <MySqlParameter> paramList2 = new List <MySqlParameter>();
paramList2.Clear();
paramList2.Add(new MySqlParameter("@itemQty", manualQty.Text));
paramList2.Add(new MySqlParameter("@itemCode", manualProductId.Text));
int responseChange = DatabaseHandler.insertOrDeleteRow("UPDATE item SET qty = qty - @itemQty WHERE item_id = @itemCode", paramList2);
if (responseChange == 1)
{
MessageBox.Show("Update Successful");
}
else
{
MessageBox.Show("Error Occured!");
}
}
catch (Exception)
{
MessageBox.Show("Error Occured!");
}
}
else
{
MessageBox.Show("INSUFFICIANT ITEMS!");
}
}
catch (Exception)
{
MessageBox.Show("Sorry, Invalid Item Code");
}
}
else if (returnedRowCount2 == 1)
{
try
{
List <MySqlParameter> paramList3 = new List <MySqlParameter>();
paramList3.Clear();
paramList3.Add(new MySqlParameter("@itemCode", manualProductId.Text));
paramList3.Add(new MySqlParameter("@value", manualQty.Text));
string queryGetQtyCondition = "SELECT IF(qty >= @value,'Yes','No') AS possibility FROM raw_material WHERE material_id = @itemCode";
string possibility = DatabaseHandler.returnOneValue(queryGetQtyCondition, paramList3, "possibility");
Console.WriteLine("String Possbility " + possibility);
if (string.Compare(possibility, "Yes") == 0)
{
Console.WriteLine("String Possibility Inside If ");
try
{
List <MySqlParameter> paramList2 = new List <MySqlParameter>();
paramList2.Clear();
paramList2.Add(new MySqlParameter("@itemQty", manualQty.Text));
paramList2.Add(new MySqlParameter("@itemCode", manualProductId.Text));
int responseChange = DatabaseHandler.insertOrDeleteRow("UPDATE raw_material SET qty = qty - @itemQty WHERE material_id = @itemCode", paramList2);
if (responseChange == 1)
{
MessageBox.Show("Update Successful");
}
else
{
MessageBox.Show("Error Occured!");
}
}
catch (Exception)
{
MessageBox.Show("Error Occured!");
}
}
else
{
MessageBox.Show("INSUFFICIANT ITEMS!");
}
}
catch (Exception)
{
MessageBox.Show("Sorry, Invalid Item Code");
}
}
else
{
MessageBox.Show("Sorry, Invalid Item Code");
}
populateGrid();
}