private void createPurchaseOrderBtn_Click(object sender, EventArgs e)
{
setPoNum();
try
{
string query = "insert into purchaseorder(supplier_id ,approval ,postedUser) values (@supplierCode,'Pending',@user);";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@supplierCode", selectedSupplier));
paramList.Add(new MySqlParameter("@user", GlobalLoginData.username));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected != 0)
{
MessageBox.Show("Purchase Order Created Successfully!");
supplierComboBox.Enabled = true;
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 purchaseorder", "po_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 purchaseorder_item VALUES (@itemCode, @poNum, @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)
{
//populateGrid();
}
else
{
MessageBox.Show("Error Occured! PO-material Link Broken!");
}
}
catch (Exception)
{
MessageBox.Show("Error Occured!");
}
finally
{
populateGrid();
}
}
Email.sendMail("RPC SYSTEM: Please approve the purchase order requisition, ID= " + poNumLbl.Text);
setPoNum();
addItemCodeTxt.Text = "";
addItemQty.Text = "";
dataGridView4.Rows.Clear();
Console.WriteLine("Current Row Count: " + dataGridView4.RowCount);
Console.WriteLine("Current Displayed Row Count: " + dataGridView4.DisplayedRowCount(true));
}
public void populateCombo()
{
string selectStatement = "SELECT supplier_id as 'Supplier Code', name as 'Supplier' FROM SUPPLIER";
DatabaseHandler.populateCombobox(selectStatement, supplierComboBox);
}
private void fill_materialDispatch_order()
{
int pending = DatabaseHandler.returnRowCountWithoutParams("SELECT * from material_dispatch WHERE approval='Pending'");
materialDispatch_lbl.Text = pending.ToString();
}
private void populateGrid()
{
string selectStatement = "SELECT name as 'Client Name', client_id as 'Client ID' , contact_no as 'Contact Number', email as 'Email' FROM CLIENT";
DatabaseHandler.populateViewwithNoParameters(selectStatement, dataGridView1);
}
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 fill_production_order()
{
int pending = DatabaseHandler.returnRowCountWithoutParams("SELECT * from productionorder WHERE approval='Pending'");
productionOrder_lbl.Text = pending.ToString();
}
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 populateDispatchCombo()
{
string selectStatement = "SELECT client_id as 'Client Id', name as 'Client Name' FROM client";
DatabaseHandler.populateDispatchCombobox(selectStatement, clientComboBox);
}
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 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 itemName_TextChanged(object sender, EventArgs e)
{
string selectStatement = "SELECT item.item_id as 'Item Code', item.name as 'Item Name', item.unit_price as 'Item Price', item.qty as 'Available Quantity' FROM item WHERE item.name like '%" + itemNameTxt.Text + "%'";
DatabaseHandler.populateGridViewWithBinding(selectStatement, dataGridView5);
}
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 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 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 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();
}