private void setPoNum()
{
string lastPo = DatabaseHandler.returnOneValueWithoutParams("SELECT*FROM purchaseorder", "po");
int lastPoNum;
if (lastPo == "Null Data!")
{
lastPoNum = 0;
}
else
{
lastPoNum = Int32.Parse(lastPo);
}
poNumLbl.Text = (lastPoNum + 1).ToString();
globalLastPo = lastPoNum;
}
private void setReqNum()
{
string lastRo = DatabaseHandler.returnOneValueWithoutParams("SELECT*FROM requestorder", "ro");
int lastRoNum;
if (lastRo == "Null Data!")
{
lastRoNum = 0;
}
else
{
lastRoNum = Int32.Parse(lastRo);
}
reqestNum.Text = (lastRoNum + 1).ToString();
globalLastRo = lastRoNum;
}
private void applyBtn_Click(object sender, EventArgs e)
{
string poNum = poNumTxt.Text;
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@poNum", poNum));
paramList.Add(new MySqlParameter("@recieved", "No"));
paramList.Add(new MySqlParameter("@approval", "Approved"));
int returnedRowCount = DatabaseHandler.returnRowCount("SELECT*FROM purchaseorder WHERE po = @poNum AND recieved=@recieved AND approval=@approval", paramList);
if (returnedRowCount == 1)
{
poNumTxt.Enabled = false;
string select = "select supplier.supplier_name from purchaseorder inner join supplier on purchaseorder.supplier_code = supplier.supplier_code where purchaseorder.po ='" + poNum + "'";
string returnedSupplierName = DatabaseHandler.returnOneValueWithoutParams(select, "supplier_name");
supplierNameLbl.Text = returnedSupplierName;
string query = "SELECT po_item.item_code as 'Item Code', STORE.item_name as 'Item', po_item.qty as 'Qty' FROM po_item INNER JOIN STORE ON po_item.item_code = STORE.item_code WHERE po_item.po = '" + poNum + "'";
DatabaseHandler.populateGridViewWithBinding(query, dataGridView3);
string selectedPo = "SELECT purchaseorder.po as 'Order #', supplier.supplier_name as 'Supplier', purchaseorder.creation_time as 'Order Creation Time', purchaseorder.postedUser as 'Posted By' FROM purchaseorder INNER JOIN supplier ON purchaseorder.supplier_code = supplier.supplier_code WHERE purchaseorder.approval = 'Approved' AND purchaseorder.po='" + poNum + "'";
DatabaseHandler.populateGridViewWithBinding(selectedPo, dataGridView1);
commitBtn.Enabled = true;
}
else
{
poNumTxt.Enabled = true;
MessageBox.Show("No such uncommited Purchase Order or the Order may not be approved. Please Try again..");
poNumTxt.Clear();
dataGridView3.DataSource = null;
dataGridView3.Refresh();
supplierNameLbl.Text = "";
}
}
private void createPurchaseOrderBtn_Click(object sender, EventArgs e)
{
setPoNum();
try
{
string query = "insert into purchaseorder(supplier_code, 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.DisplayedRowCount(true);
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");
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 po_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)
{
//populateGrid();
}
else
{
MessageBox.Show("Error Occured! PO-Item Link Broken!");
}
}
catch (Exception)
{
MessageBox.Show("Error Occured! Please check if the Client already exists!");
}
finally
{
populateGrid();
}
}
setPoNum();
addItemCodeTxt.Text = "";
addItemQty.Text = "";
dataGridView4.Rows.Clear();
Console.WriteLine("Current Row Count: " + dataGridView4.RowCount);
Console.WriteLine("Current Displayed Row Count: " + dataGridView4.DisplayedRowCount(true));
}
private void dispatchRequestBtn_Click(object sender, EventArgs e)
{
setReqNum();
try
{
string query = "insert into requestorder(client_code, approval,postedUser) values (@clientCode,'Pending',@user)";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@clientCode", selectedClient));
paramList.Add(new MySqlParameter("@user", GlobalLoginData.username));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
if (rowsAffected == 1)
{
try
{
int i = dataGridView4.DisplayedRowCount(true);
string itemid;
string qty;
for (int row = 0; row < i - 1; row++)
{
string lastRo = DatabaseHandler.returnOneValueWithoutParams("SELECT*FROM requestorder", "ro");
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 ro_item VALUES (@roNum,@itemCode,@qty)";
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)
{
string updateStore = "UPDATE STORE SET qty = qty - @qty WHERE item_code = @itemCode";
paramList.Clear();
paramList.Add(new MySqlParameter("@qty", qty));
paramList.Add(new MySqlParameter("@itemCode", itemid));
DatabaseHandler.insertOrDeleteRow(updateStore, paramList);
itemNameTxt.Clear();
qtyTxt.Clear();
dataGridView4.Rows.Clear();
}
else
{
MessageBox.Show("Update Failed! RO Link Broken");
}
}
}
catch (Exception)
{
}
}
else
{
MessageBox.Show("Update Failed!");
}
MessageBox.Show("Purchase Order : Posted!");
populateDataGrid();
}
catch (Exception)
{
}
}
private void postRequest_Click(object sender, EventArgs e)
{
try
{
string getSupplierCodeQuery = "SELECT supplier_code from STORE WHERE item_code='" + reqProdId.Text + "'";
int rowCountSuppliers = DatabaseHandler.returnRowCountWithoutParams(getSupplierCodeQuery);
Console.WriteLine("getSupplierCodeQuery: " + getSupplierCodeQuery + " rowCountSuppliers: " + rowCountSuppliers);
if (rowCountSuppliers == 1)
{
string supplierCode = DatabaseHandler.returnOneValueWithoutParams(getSupplierCodeQuery, "supplier_code");
Console.WriteLine("supplierCode: " + supplierCode);
try
{
string query = "insert into purchaseorder(supplier_code, approval,postedUser) values (@supplierCode,'Pending',@user);";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Clear();
paramList.Add(new MySqlParameter("@supplierCode", supplierCode));
paramList.Add(new MySqlParameter("@user", GlobalLoginData.username));
int rowsAffected = DatabaseHandler.insertOrDeleteRow(query, paramList);
Console.WriteLine("rowsAffected: " + rowsAffected);
if (rowsAffected != 0)
{
string lastPo = DatabaseHandler.returnOneValueWithoutParams("SELECT*FROM purchaseorder", "po");
int lastPoNum;
if (lastPo == "Null Data!")
{
lastPoNum = 0;
}
else
{
lastPoNum = Int32.Parse(lastPo);
}
Console.WriteLine("lastPoNum: " + lastPoNum);
string insertQuery = "INSERT INTO po_item VALUES (@poNum,@itemCode,@qty)";
List <MySqlParameter> paramList2 = new List <MySqlParameter>();
paramList2.Add(new MySqlParameter("@poNum", lastPoNum));
paramList2.Add(new MySqlParameter("@itemCode", reqProdId.Text));
paramList2.Add(new MySqlParameter("@qty", reqQty.Text));
int rowsAffected2 = DatabaseHandler.insertOrDeleteRow(insertQuery, paramList2);
Console.WriteLine("rowsAffected2: " + rowsAffected2);
if (rowsAffected2 != 0)
{
MessageBox.Show("Purchase Request Created Successfully!");
populateGrid();
}
else
{
MessageBox.Show("Error Occured! PO-Item Link Broken!");
}
populateGrid();
}
else
{
MessageBox.Show("Error Occured! Please check input details!");
}
}
catch (Exception)
{
MessageBox.Show("Error Occured! Please check input details!");
}
}
}
catch (Exception)
{
}
}