private void removeItem()
{
int selectedRowIndex = dataGridView2.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dataGridView2.Rows[selectedRowIndex];
string itemCode = Convert.ToString(selectedRow.Cells["Material ID"].Value);
try
{
string query = "DELETE FROM raw_material WHERE material_id=@itemCode";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@itemCode", itemCode));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("Item Removed Successfully!");
populateGrid();
}
else
{
MessageBox.Show("Error!");
}
}
catch (Exception)
{
MessageBox.Show("Error!");
}
}
private void declineBtn_Click(object sender, EventArgs e)
{
string val = null;
try
{
val = dataGridView3.SelectedRows[0].Cells["Order #"].Value.ToString();
}
catch
{
MessageBox.Show("Error Occured! Please check Selection!");
return;
}
string update = "UPDATE purchaseorder set approval='Declined' where po_id=@ponum";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@poNum", val));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(update, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("Purchase Order Declined!");
populateGrid();
}
else
{
MessageBox.Show("Error Occured! Please check Selection!");
}
populateNonComboGrids();
}
private void removeSupplierBtn_Click(object sender, EventArgs e)
{
try
{
string query1 = "SELECT * FROM SUPPLIER WHERE supplier_id= '" + removeSupplierId.Text.ToString() + "' ";
int count1 = DatabaseHandler.returnRowCountWithoutParams(query1);
Console.WriteLine("Selected supplier " + removeSupplierId.Text.ToString());
Console.WriteLine("Row count" + count1);
if (count1 == 0)
{
MessageBox.Show("Error! Supplier doesn't exist! ");
return;
}
string query = "DELETE FROM SUPPLIER WHERE supplier_id=@supplierCode";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@supplierCode", removeSupplierId.Text));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("Supplier Removed Successfully!");
populateGrid();
}
else
{
MessageBox.Show("Error! The supplier may have unremoved items. Remove items first!");
}
}
catch (Exception)
{
MessageBox.Show("Error! The supplier may have unremoved items. Remove items first!");
}
}
private void addNewMaterial_btn_Click(object sender, EventArgs e)
{
string itemId = itemIdTxt.Text;
string itemName = itemNameTxt.Text;
if (String.IsNullOrEmpty(itemId) || String.IsNullOrEmpty(itemName))
{
MessageBox.Show("One or more feilds are empty!");
}
string query = "INSERT INTO raw_material(material_id,name) VALUES (@itemId,@itemName)";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@itemId", itemId));
paramList.Add(new MySqlParameter("@itemName", itemName));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("New material Added Successfully!");
itemNameTxt.Enabled = false;
addNewMaterial_btn.Enabled = false;
addItemBtn.Enabled = true;
populateGrid();
}
else
{
MessageBox.Show("Error Occured! Please check if the Info entered!");
}
}
private void removeMaterialsbtn_Click(object sender, EventArgs e)
{
int selectedRowIndex = dataGridView2.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dataGridView2.Rows[selectedRowIndex];
string materialCode = Convert.ToString(selectedRow.Cells["Material ID"].Value);
try
{
string query = "DELETE FROM raw_material WHERE material_id=@materialCode";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@materialCode", materialCode));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("Material Removed Successfully!");
populateGrid();
}
else
{
MessageBox.Show("Error! The material may have unremoved supplier items. Remove supplier items first!");
}
}
catch (Exception)
{
MessageBox.Show("Error! The material may have unremoved supplier items. Remove supplier items first!");
}
}
private void addBtn_Click(object sender, EventArgs e)
{
string memberName = nameTxt.Text;
string username = usernameTxt.Text;
string password = passwordTxt.Text;
string retypePassword = retypePasswordTxt.Text;
string email = emailText.Text;
string userRole = null;
if (ownerRadio.Checked)
{
userRole = "Owner";
}
else if (accountantRadio.Checked)
{
userRole = "Accountant";
}
else if (storekeeperRadio.Checked)
{
userRole = "StoreKeeper";
}
if (String.IsNullOrEmpty(userRole) || String.IsNullOrEmpty(memberName) || String.IsNullOrEmpty(username) || String.IsNullOrEmpty(password) || String.IsNullOrEmpty(email) || !(ownerRadio.Checked || accountantRadio.Checked || storekeeperRadio.Checked))
{
MessageBox.Show("One or more fields are Empty!");
}
else if ((string.Compare(password, retypePassword)) != 0)
{
MessageBox.Show("Password doesn't match!");
}
else if (!String.IsNullOrEmpty(userRole) && !String.IsNullOrEmpty(memberName) && !String.IsNullOrEmpty(username) && !String.IsNullOrEmpty(password) && ((string.Compare(password, retypePassword)) == 0) && !String.IsNullOrEmpty(email))
{
try
{
string query = "INSERT INTO USER VALUES (@memberName,@username,@password,@role,@email)";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@memberName", memberName));
paramList.Add(new MySqlParameter("@username", username));
paramList.Add(new MySqlParameter("@password", password));
paramList.Add(new MySqlParameter("@role", userRole));
paramList.Add(new MySqlParameter("@email", email));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("User Added Successfully!");
populateGrid();
}
else
{
MessageBox.Show("Error Occured! Please check if the user already exists!");
}
}
catch (Exception)
{
MessageBox.Show("Error Occured! Please check if the user already exists!");
}
}
}
private void addItemBtn_Click(object sender, EventArgs e)
{
string itemSupplierCode = itemSupplierCodeTxt.Text;
string itemId = itemIdTxt.Text;
string itemName = itemNameTxt.Text;
string unitPrice = unitPriceText.Text;
string lead_time = leadTime_txt.Text;
if (String.IsNullOrEmpty(itemSupplierCode) || String.IsNullOrEmpty(itemId) || String.IsNullOrEmpty(itemName) || String.IsNullOrEmpty(unitPrice))
{
MessageBox.Show("One or more feilds are empty!");
}
else if (!String.IsNullOrEmpty(itemSupplierCode) && !String.IsNullOrEmpty(itemId) && !String.IsNullOrEmpty(itemName) && !String.IsNullOrEmpty(unitPrice))
{
if (DatabaseHandler.returnRowCountWithoutParams("SELECT * FROM raw_material where material_id='" + itemIdTxt.Text + "'") != 0)
{
try
{
// MessageBox.Show("Such material exists, it will be added to the supplier mentioned");
string query = "INSERT INTO supplier_material(material_id,supplier_id,unit_price,lead_time) VALUES (@itemId,@itemSupplierCode,@unitPrice,@leadTime)";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@itemSupplierCode", itemSupplierCode));
paramList.Add(new MySqlParameter("@itemId", itemId));
paramList.Add(new MySqlParameter("@unitPrice", unitPrice));
paramList.Add(new MySqlParameter("@leadTime", lead_time));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("Item Added Successfully!");
itemSupplierCodeTxt.Text = "";
itemIdTxt.Text = "";
itemNameTxt.Text = "";
unitPriceText.Text = "";
leadTime_txt.Text = "";
populateGrid();
}
else
{
MessageBox.Show("Error Occured! Please check the Info entered!");
}
}
catch (Exception)
{
MessageBox.Show("Error Occured! Please check if the Item already exists with the mentioned supplier!");
}
}
else
{
MessageBox.Show("Error Occured! No such material exists!");
}
}
}
private void addSupplierBtn_Click(object sender, EventArgs e)
{
string supplierName = supplierNameTxt.Text;
string supplierCode = supplierCodeTxt.Text;
string contactNumber = contactNumberTxt.Text;
string email = emailTxt.Text;
if (String.IsNullOrEmpty(supplierName) || String.IsNullOrEmpty(supplierCode) || String.IsNullOrEmpty(contactNumber) || String.IsNullOrEmpty(email))
{
MessageBox.Show("One or more feilds are empty!");
}
else if (contactNumber.Length != 10)
{
MessageBox.Show("Incorrect Contact Number!");
}
else if (!String.IsNullOrEmpty(supplierName) && !String.IsNullOrEmpty(supplierCode) && !String.IsNullOrEmpty(contactNumber) && !String.IsNullOrEmpty(email))
{
try
{
string query = "INSERT INTO SUPPLIER VALUES (@supplierCode,@supplierName,@contactNumber,@email)";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@supplierCode", supplierCode));
paramList.Add(new MySqlParameter("@supplierName", supplierName));
paramList.Add(new MySqlParameter("@contactNumber", contactNumber));
paramList.Add(new MySqlParameter("@email", email));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("Supplier Added Successfully!");
populateGrid();
supplierNameTxt.Text = "";
supplierCodeTxt.Text = "";
contactNumberTxt.Text = "";
emailTxt.Text = "";
}
else
{
MessageBox.Show("Error Occured! Please check if the Supplier already exists!");
}
}
catch (Exception)
{
MessageBox.Show("Error Occured! Please check if the Supplier already exists!");
}
}
}
private void addBtnTxt_Click(object sender, EventArgs e)
{
string name = ClientNameTxt.Text;
string id = identifierTxt.Text;
string contactNum = contactNumTxt.Text;
string email = emailTxt.Text;
if (String.IsNullOrEmpty(name) || String.IsNullOrEmpty(id) || String.IsNullOrEmpty(contactNum) || String.IsNullOrEmpty(email))
{
MessageBox.Show("One or more fields empty!");
}
else if (contactNum.Length != 10)
{
MessageBox.Show("Invalied contact number!");
}
else if (!String.IsNullOrEmpty(name) && !String.IsNullOrEmpty(id) && !String.IsNullOrEmpty(contactNum) && !String.IsNullOrEmpty(email))
{
try
{
string query = "INSERT INTO CLIENT VALUES (@id,@name,@contactNum,@email)";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@id", id));
paramList.Add(new MySqlParameter("@name", name));
paramList.Add(new MySqlParameter("@contactNum", contactNum));
paramList.Add(new MySqlParameter("@email", email));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("Supplier Added Successfully!");
ClientNameTxt.Text = "";
identifierTxt.Text = "";
contactNumTxt.Text = "";
emailTxt.Text = "";
populateGrid();
}
else
{
MessageBox.Show("Error Occured! Please check if the Client already exists!");
}
}
catch (Exception)
{
MessageBox.Show("Error Occured! Please check if the Client already exists!");
}
}
}
private void removeBtn_Click(object sender, EventArgs e)
{
if (removeUsername.Text == GlobalLoginData.username)
{
MessageBox.Show("Sorry, you cannot remove the account in use.");
return;
}
else
{
try
{
string query1 = "SELECT * FROM USER WHERE username= '******' ";
int count1 = DatabaseHandler.returnRowCountWithoutParams(query1);
Console.WriteLine("Selected user " + removeUsername.Text.ToString());
Console.WriteLine("Row count" + count1);
if (count1 == 0)
{
MessageBox.Show("Error! User doesn't exist! ");
return;
}
string query = "DELETE FROM USER WHERE username=@username";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@username", removeUsername.Text));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("User Removed Successfully!");
populateGrid();
}
else
{
MessageBox.Show("No such user found!");
return;
}
}
catch (Exception)
{
MessageBox.Show("User has had interations with System! Cannot Remove!");
}
}
}
private void submit_btn_Click(object sender, EventArgs e)
{
String content = "Null";
selected = reason_cmb.SelectedIndex;
if (selected == -1)
{
MessageBox.Show("Please Select a valied reason");
return;
}
else if (selected == 1)
{
content = "The item order is not feasible to be produced under the current condition.";
Email.sendIoDeclineMail(content, clientEmail, itemOrder_lbl.Text);
}
else if (selected == 2)
{
content = "The volume of the item order is not acceptable.";
Email.sendIoDeclineMail(content, clientEmail, itemOrder_lbl.Text);
}
else if (selected == 3)
{
content = otherInfo_txt.Text;
Email.sendIoDeclineMail(content, clientEmail, itemOrder_lbl.Text);
}
string update = "UPDATE itemorder set approval='Declined' where io_id=@ronum";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@ronum", ItemDispatch.declineOrderNO));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(update, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("Item Order Declined!");
}
else
{
MessageBox.Show("Error Occured! Please check Selection!");
}
this.Close();
}
private void DispatchRequestBtn_Click(object sender, EventArgs e)
{
setReqNum();
int index3 = dataGridView4.DisplayedRowCount(true);
string bomId = bomId_txt.Text;
if (index3 == 1)
{
MessageBox.Show("Invalied selection, dispatch request not created!!");
return;
}
try
{
string query = "insert into material_dispatch(bom_id, approval,postedUser) values (@bomId,'Pending',@user)";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@bomId", bomId));
paramList.Add(new MySqlParameter("@user", GlobalLoginData.username));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected == 1)
{
MessageBox.Show("Material dispatch request posted sucessfully!!");
populateDataGrid();
Email.sendMail("RPC SYSTEM: Please approve the Material dispatch order requisition, ID= " + dispatchId_lbl.Text);
dataGridView4.DataSource = null;
dataGridView4.Rows.Clear();
bomId_txt.Clear();
setReqNum();
}
else
{
MessageBox.Show("Error!!");
}
}
catch (Exception err)
{
Console.WriteLine(err);
}
}
private void reApprove_btn_Click(object sender, EventArgs e)
{
string val = dataGridView8.SelectedRows[0].Cells["Order #"].Value.ToString();
string update = "UPDATE itemorder set approval='Approved' where io_id=@ronum";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@ronum", val));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(update, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("Declined Item Order re-Confirmed!");
populateDataGrid();
}
else
{
MessageBox.Show("Error Occured! Please check Selection!");
}
}
private void Remove_btn_Click(object sender, EventArgs e)
{
string val = dataGridView5.SelectedRows[0].Cells["BOM ID"].Value.ToString();
string update = "DELETE FROM bom where bom_id=@ronum";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@ronum", val));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(update, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("BOM removed successfuly");
populateDataGrid();
}
else
{
MessageBox.Show("Error Occured! Please check Selection!");
}
}
private void DeclineBtn_Click(object sender, EventArgs e)
{
string val = dataGridView1.SelectedRows[0].Cells["Dispatch ID"].Value.ToString();
string update = "UPDATE material_dispatch set approval='Declined' where id=@ronum";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@ronum", val));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(update, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("Dispatch Order Declined!");
populateDataGrid();
}
else
{
MessageBox.Show("Error Occured! Please check Selection!");
}
}
private void approveBtn_Click(object sender, EventArgs e)
{
string val = dataGridView3.SelectedRows[0].Cells["Order #"].Value.ToString();
string update = "UPDATE productionorder set approval='Approved' where pro_id=@ponum";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@ponum", val));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(update, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("Production Order Confirmed!");
populateGrid();
}
else
{
MessageBox.Show("Error Occured! Please check Selection!");
}
populateNonComboGrids();
}
private void RemoveBtn_Click(object sender, EventArgs e)
{
try
{
string query1 = "SELECT * FROM client WHERE client_id= '" + removeClient.Text.ToString() + "' ";
int count1 = DatabaseHandler.returnRowCountWithoutParams(query1);
Console.WriteLine("Selected client " + removeClient.Text.ToString());
Console.WriteLine("Row count" + count1);
if (count1 == 0)
{
MessageBox.Show("Error! Client doesn't exist! ");
return;
}
string query = "DELETE FROM CLIENT WHERE client_id=@ClientCode";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@clientCode", removeClient.Text.ToString()));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
paramList.Clear();
if (rowsAffected != 0)
{
MessageBox.Show("Client Removed Successfully!");
populateGrid();
}
else
{
MessageBox.Show("Error! Client has already intracted with the system! Cannot remove!");
}
}
catch (Exception)
{
MessageBox.Show("Error! Client has already intracted with the system! Cannot remove!");
}
}
private void createPurchaseOrderBtn_Click(object sender, EventArgs e)
{
setPoNum();
try
{
string query = "insert into productionorder(approval,postedUser) values ('Pending',@user);";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@user", GlobalLoginData.username));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("Production Order Created Successfully!");
populateGrid();
}
else
{
MessageBox.Show("Error Occured! Please check input details!");
}
}
catch (Exception)
{
MessageBox.Show("Error Occured! Please check input details!");
}
int i = dataGridView4.Rows.Count;
Console.WriteLine("Special i Value: " + i);
string itemid;
string qty;
for (int row = 0; row < i - 1; row++)
{
string lastPo = DatabaseHandler.returnOneValueWithoutParams("SELECT * FROM productionorder", "pro_id");
itemid = dataGridView4.Rows[row].Cells[0].Value.ToString();
qty = dataGridView4.Rows[row].Cells[2].Value.ToString();
Console.WriteLine(itemid + " " + qty);
try
{
string query = "INSERT INTO productionorder_item VALUES (@poNum,@itemCode,@qty)";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@poNum", lastPo));
paramList.Add(new MySqlParameter("@itemCode", itemid));
paramList.Add(new MySqlParameter("@qty", qty));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected == 0)
{
MessageBox.Show("Error Occured! PO-Item Link Broken!");
}
}
catch (Exception)
{
MessageBox.Show("Error Occured! Please check if the Client already exists!");
}
finally
{
populateGrid();
}
}
Email.sendMail("RPC SYSTEM: Please approve the production order requisition, ID= " + PO.Text);
setPoNum();
addItemCodeTxt.Text = "";
addItemQty.Text = "";
dataGridView4.Rows.Clear();
Console.WriteLine("Current Row Count: " + dataGridView4.Rows.Count);
}
private void DispatchBtn_Click(object sender, EventArgs e)
{
string val = null;
try
{
val = dataGridView2.SelectedRows[0].Cells["Dispatch ID"].Value.ToString();
}
catch (Exception)
{
MessageBox.Show("Nothing Selected");
}
if (dataGridView7.DisplayedRowCount(true) != 1)
{
MessageBox.Show("INSUFFICIANT ITEMS IN THE STORE CANNOT DISPATCH ITEMS ");
return;
}
string update = "UPDATE material_dispatch set released='Yes' where id=@ronum";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@ronum", val));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(update, paramList);
if (rowsAffected != 0)
{
string itemCodeTemp;
string putout;
string putoutqty;
string existqty;
for (int i = 0; i < dataGridView6.Rows.Count - 1; i++)
{
try
{
itemCodeTemp = dataGridView6.Rows[i].Cells["Material ID"].Value.ToString();
putoutqty = dataGridView6.Rows[i].Cells["Quantity"].Value.ToString();
existqty = DatabaseHandler.returnOneValueWithoutParams("select qty from raw_material where material_id='" + itemCodeTemp + "'", "qty");
putout = "UPDATE raw_material SET qty=@remainqty WHERE material_id = @itemCode";
Console.WriteLine("GridView Row Count: " + dataGridView6.RowCount);
Console.WriteLine("itemCodeTemp: " + itemCodeTemp);
Console.WriteLine("putoutqty " + float.Parse(putoutqty));
List <MySqlParameter> paramList2 = new List <MySqlParameter>();
paramList2.Add(new MySqlParameter("@remainqty", (float.Parse(existqty) - float.Parse(putoutqty))));
paramList2.Add(new MySqlParameter("@itemCode", itemCodeTemp));
Console.WriteLine("query :" + putout);
DatabaseHandler.insertOrDeleteRow(putout, paramList2);
}
catch (Exception err)
{
Console.WriteLine(err);
}
}
MessageBox.Show("Order Dispatched!");
populateDataGrid();
}
else
{
MessageBox.Show("Error Occured! Please check Selection!");
}
}
private void RemoveProduct_btn_Click(object sender, EventArgs e)
{
int selectedRowIndex = dataGridView1.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dataGridView1.Rows[selectedRowIndex];
string itemCode = Convert.ToString(selectedRow.Cells["Item Code"].Value);
string query2 = "DELETE FROM item WHERE item_id=@itemCode";
List <MySqlParameter> paramList2 = new List <MySqlParameter>();
paramList2.Clear();
paramList2.Add(new MySqlParameter("@itemCode", itemCode));
int rowsAffected2 = DatabaseHandler.insertOrDeleteRow(query2, paramList2);
if (rowsAffected2 == 0)
{
MessageBox.Show("Error! Item has existing client order items ! Remove client items first! ");
return;
}
try
{
string query = "DELETE FROM composition WHERE item_id=@itemCode";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@itemCode", itemCode));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("Item Removed Successfully!");
populateGrid();
}
else
{
MessageBox.Show("Error!");
}
}
catch (Exception)
{
MessageBox.Show("Item doesn't exist!");
}
try
{
string query = "DELETE FROM item WHERE item_id=@itemCode";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@itemCode", itemCode));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
//MessageBox.Show("Item Removed Successfully!");
populateGrid();
}
else
{
MessageBox.Show("Error!");
}
}
catch (Exception)
{
MessageBox.Show("Item doesn't exist!");
}
}
private void manualAddBtn_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> 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 if (returnedRowCount2 == 1)
{
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("Sorry, Invalid item Code");
}
populateGrid();
}
private void dispatchRequestBtn_Click(object sender, EventArgs e)
{
string ldt = latestDeliveryTimeCal.SelectionRange.Start.ToShortDateString();
setReqNum();
int index3 = dataGridView4.DisplayedRowCount(true);
if (index3 == 1)
{
MessageBox.Show("Update Failed! No items added");
return;
}
try
{
string query = "insert into itemorder(client_id, approval,postedUser,latest_delivery_time) values (@clientCode,'Pending',@user,@ldt)";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@clientCode", selectedClient));
paramList.Add(new MySqlParameter("@user", GlobalLoginData.username));
paramList.Add(new MySqlParameter("@ldt", ldt));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected == 1)
{
try
{
Console.WriteLine("displayed rows" + dataGridView4.Rows.Count);
int i = dataGridView4.Rows.Count;
string itemid;
string qty;
for (int row = 0; row < i - 1; row++)
{
string lastRo = DatabaseHandler.returnOneValueWithoutParams("SELECT * FROM itemorder", "io_id");
Console.WriteLine("lastRo: " + lastRo);
itemid = dataGridView4.Rows[row].Cells[0].Value.ToString();
qty = dataGridView4.Rows[row].Cells[2].Value.ToString();
Console.WriteLine("itemid: " + itemid + " qty: " + qty);
string query2 = "INSERT INTO itemorder_item VALUES (@itemCode,@qty,@roNum)";
paramList.Clear();
paramList.Add(new MySqlParameter("@roNum", lastRo));
paramList.Add(new MySqlParameter("@itemCode", itemid));
paramList.Add(new MySqlParameter("@qty", qty));
rowsAffected = DatabaseHandler.insertOrDeleteRow(query2, paramList);
Console.WriteLine("rows affected: " + rowsAffected);
if (rowsAffected == 1)
{
}
else
{
MessageBox.Show("Update Failed! RO Link Broken");
}
}
}
catch (Exception)
{
}
}
else
{
MessageBox.Show("Update Failed!");
return;
}
MessageBox.Show("Item Order : Posted!");
Email.sendMail("RPC SYSTEM: Please approve the Item dispatch order requisition, ID= " + reqestNum.Text);
populateDataGrid();
itemNameTxt.Clear();
qtyTxt.Clear();
dataGridView4.Rows.Clear();
dataGridView7.Rows.Clear();
setReqNum();
}
catch (Exception)
{
}
}
private void dispatchBtn_Click(object sender, EventArgs e)
{
string val = null;
try
{
val = dataGridView2.SelectedRows[0].Cells["Order #"].Value.ToString();
}
catch (Exception)
{
MessageBox.Show("Nothing Selected");
}
if (dataGridView7.DisplayedRowCount(true) != 1)
{
MessageBox.Show("INSUFFICIANT ITEMS IN THE STORE CANNOT DISPATCH ITEMS ");
return;
}
string update = "UPDATE itemorder set released='Yes' where io_id=@ronum";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@ronum", val));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(update, paramList);
if (rowsAffected != 0)
{
string itemCodeTemp;
string putout;
string putoutqty;
for (int i = 0; i < dataGridView6.Rows.Count - 1; i++)
{
try
{
itemCodeTemp = dataGridView6.Rows[i].Cells["Item Code"].Value.ToString();
putoutqty = dataGridView6.Rows[i].Cells["Qty"].Value.ToString();
putout = "UPDATE item SET qty = qty - @putoutQty WHERE item_id = @itemCode";
Console.WriteLine("GridView Row Count: " + dataGridView6.Rows.Count);
Console.WriteLine("itemCodeTemp: " + itemCodeTemp);
Console.WriteLine("putoutqty " + putoutqty);
List <MySqlParameter> paramList2 = new List <MySqlParameter>();
paramList2.Add(new MySqlParameter("@putoutQty", putoutqty));
paramList2.Add(new MySqlParameter("@itemCode", itemCodeTemp));
Console.WriteLine("query :" + putout);
DatabaseHandler.insertOrDeleteRow(putout, paramList2);
}
catch (Exception err)
{
Console.WriteLine(err);
}
}
MessageBox.Show("Order Dispatched!");
populateDataGrid();
}
else
{
MessageBox.Show("Error Occured! Please check Selection!");
}
}
private void Button2_Click(object sender, EventArgs e)
{
String product_id = prodctId_txt.Text;
String productName = productName_txt.Text;
String unitPrice = unitPrice_txt.Text;
int i = composition_dataGridView.Rows.Count;
Console.WriteLine("Special i Value: " + i);
try
{
string query = "insert into item(item_id, name , unit_price) values (@product_id,@productName,@unitPrice)";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@product_id", product_id));
paramList.Add(new MySqlParameter("@productName", productName));
paramList.Add(new MySqlParameter("@unitPrice", unitPrice));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("New product added Successfully!");
populateGrid();
}
else
{
MessageBox.Show("Error Occured! Please check input details!");
return;
}
}
catch (Exception)
{
MessageBox.Show("Error Occured! Please check input details!");
}
string materialId;
string material_qty;
string material_name;
for (int row = 0; row < i - 1; row++)
{
materialId = composition_dataGridView.Rows[row].Cells[0].Value.ToString();
material_name = composition_dataGridView.Rows[row].Cells[1].Value.ToString();
material_qty = composition_dataGridView.Rows[row].Cells[2].Value.ToString();
Console.WriteLine(materialId + " " + material_qty);
try
{
string query = "INSERT INTO composition (item_id,material_id,quantity) VALUES (@ItemId,@materialId,@qty)";
List <MySqlParameter> paramList2 = new List <MySqlParameter>();
paramList2.Add(new MySqlParameter("@ItemId", product_id));
paramList2.Add(new MySqlParameter("@materialId", materialId));
paramList2.Add(new MySqlParameter("@qty", material_qty));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList2);
if (rowsAffected == 0)
{
MessageBox.Show("Error Occured! Item-material Link Broken!");
}
else
{
// MessageBox.Show("Item added sucsessfully");
}
}
catch (Exception)
{
MessageBox.Show("Error Occured! Please check if the Product already exists!");
}
}
populateGrid();
prodctId_txt.Text = "";
productName_txt.Text = "";
unitPrice_txt.Text = "";
addmaterialCodeTxt.Text = "";
addmaterialQty.Text = "";
composition_dataGridView.Rows.Clear();
Console.WriteLine("Current Row Count: " + composition_dataGridView.RowCount);
Console.WriteLine("Current Displayed Row Count: " + composition_dataGridView.DisplayedRowCount(true));
}
private void Create_bom_btn_Click(object sender, EventArgs e)
{
setPoNum();
try
{
string query = "insert into bom( postedUser, pro_id) values (@user, @proId);";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@user", GlobalLoginData.username));
paramList.Add(new MySqlParameter("@proId", poText.Text));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
//MessageBox.Show("BOM Created Successfully!");
}
else
{
MessageBox.Show("Error Occured! Please check input details!");
}
}
catch (Exception)
{
MessageBox.Show("Error Occured! Please check input details!");
}
int i = dataGridView2.Rows.Count;
Console.WriteLine("Special i Value: " + i);
string itemid;
string qty;
string bomId = bom_lbl.Text;
for (int row = 0; row < i - 1; row++)
{
itemid = dataGridView2.Rows[row].Cells[1].Value.ToString();
qty = dataGridView2.Rows[row].Cells[3].Value.ToString();
string querry = "SELECT material_id as 'material_id', quantity as 'quantity' FROM composition WHERE item_id= '" + itemid + "'";
DatabaseHandler.populatebomDataGridView(querry, dataGridView1, qty);
}
int j = dataGridView1.Rows.Count;
Console.WriteLine("Special j Value: " + j);
int temp = 0;
for (int row = 0; row < j - 1; row++)
{
itemid = dataGridView1.Rows[row].Cells[0].Value.ToString();
qty = dataGridView1.Rows[row].Cells[1].Value.ToString();
Console.WriteLine(itemid + " " + qty);
try
{
string query = "INSERT INTO bom_item VALUES (@material_id, @bom_id, @qty)";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@material_id", itemid));
paramList.Add(new MySqlParameter("@bom_id", bomId));
paramList.Add(new MySqlParameter("@qty", qty));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
// MessageBox.Show("BOM Created Successfully!");
temp++;
}
else
{
MessageBox.Show("Error Occured! BOM-material Link Broken!");
return;
}
}
catch (Exception)
{
MessageBox.Show("Error Occured!");
}
}
data = GetDataTableFromDGV(dataGridView1);
if (temp != 0)
{
MessageBox.Show("BOM Created Successfully!");
}
}
private void commitBtn_Click(object sender, EventArgs e)
{
try
{
string updateQuery = "update purchaseorder set recieved = 'Yes' where po_id=@poNum";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@poNum", poNumTxt.Text));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(updateQuery, paramList);
int rows = dataGridView3.Rows.Count;
string itemid = null;
string qty = null;
for (int i = 0; i < rows - 1; i++)
{
itemid = dataGridView3.Rows[i].Cells[0].Value.ToString();
qty = dataGridView3.Rows[i].Cells[2].Value.ToString();
Console.WriteLine(itemid + " " + qty);
try
{
string query = "UPDATE raw_material SET qty= qty + @qty WHERE material_id=@itemid";
List <MySqlParameter> paramList2 = new List <MySqlParameter>();
paramList2.Add(new MySqlParameter("@qty", qty));
paramList2.Add(new MySqlParameter("@itemid", itemid));
int rowsAffected2 = DatabaseHandler.insertOrDeleteRow(query, paramList2);
if (rowsAffected2 != 0)
{
//populateGrids();
}
else
{
MessageBox.Show("Error Occured! PO-Item Link Broken!");
}
}
catch (Exception)
{
MessageBox.Show("Error Occured!");
}
}
if (rowsAffected != 0)
{
MessageBox.Show("Commited!");
populateGrids();
}
else
{
MessageBox.Show("Error!");
}
}
catch (Exception)
{
MessageBox.Show("Error occured!");
}
poNumTxt.Enabled = true;
poNumTxt.Clear();
dataGridView3.DataSource = null;
dataGridView3.Refresh();
supplierNameLbl.Text = "";
commitBtn.Enabled = false;
}
private void Commit_btn_Click(object sender, EventArgs e)
{
try
{
string val = dataGridView2.SelectedRows[0].Cells["Order #"].Value.ToString();
if (val == null)
{
MessageBox.Show("ENTRY NOT SELECTED!");
return;
}
string bomId = null;
bomId = DatabaseHandler.returnOneValueWithoutParams("select bom_id from bom where pro_id='" + val + "'", "bom_id");
if (bomId == "Null Data!" || bomId == null)
{
MessageBox.Show("ERROR !!! BILL OF MATERIAL HAS NOT BEEN CREATED FOR THE SELECTED PRODUCTON ORDER!");
return;
}
String materialreleased = null;
materialreleased = DatabaseHandler.returnOneValueWithoutParams("select released from material_dispatch where bom_id='" + bomId + "'", "released");
if (materialreleased == "Null Data!" || materialreleased == null)
{
MessageBox.Show("ERROR !!! MATERIAL DISPATCH ORDER HAS NOT BEEN CREATED FOR THE SELECTED PRODUCTON ORDER!");
return;
}
if (materialreleased == "No")
{
MessageBox.Show("ERROR!!! MATERIAL HAS NOT BEEN RELEASED FOR THE SELECTED PRODUCTION ORDER!");
return;
}
string updateQuery = "update productionorder set recieved = 'Yes' where pro_id=@poNum";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@poNum", val));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(updateQuery, paramList);
int rows = dataGridView5.Rows.Count;
string itemid = null;
string qty = null;
for (int i = 0; i < rows - 1; i++)
{
itemid = dataGridView5.Rows[i].Cells[1].Value.ToString();
qty = dataGridView5.Rows[i].Cells[3].Value.ToString();
Console.WriteLine(itemid + " " + qty);
try
{
string query = "UPDATE item SET qty= qty + @qty WHERE item_id=@itemid";
List <MySqlParameter> paramList2 = new List <MySqlParameter>();
paramList2.Add(new MySqlParameter("@qty", qty));
paramList2.Add(new MySqlParameter("@itemid", itemid));
int rowsAffected2 = DatabaseHandler.insertOrDeleteRow(query, paramList2);
if (rowsAffected2 != 0)
{
}
else
{
MessageBox.Show("Error Occured! PO-Item Link Broken!");
}
}
catch (Exception)
{
MessageBox.Show("Error Occured!");
}
}
if (rowsAffected != 0)
{
MessageBox.Show("Commited!");
populateGrid();
}
else
{
MessageBox.Show("Error!");
}
}
catch (Exception)
{
MessageBox.Show("Error occured!");
}
addItemCodeTxt.Clear();
dataGridView5.DataSource = null;
dataGridView5.Refresh();
}
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();
}
