private void populateDataGrid()
{
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 ";
DatabaseHandler.populateGridViewWithBinding(selectStatement, dataGridView5);
string selectStatement1 = "SELECT itemorder.io_id as 'Order #', client.name as 'Client Name', itemorder.creation_time as 'Posted Time', itemorder.postedUser as 'User', itemorder.latest_delivery_time as 'Latest Delivery Time' FROM itemorder inner join client on itemorder.client_id = client.client_id WHERE itemorder.approval = 'Pending' AND itemorder.released='No'";
DatabaseHandler.populateGridViewWithBinding(selectStatement1, dataGridView1);
string selectStatement2 = "SELECT itemorder.io_id as 'Order #', client.name as 'Client Name', itemorder.creation_time as 'Posted Time', itemorder.postedUser as 'User', itemorder.latest_delivery_time as 'Latest Delivery Time' FROM itemorder inner join client on itemorder.client_id = client.client_id WHERE itemorder.approval = 'Approved' AND itemorder.released='No'";
DatabaseHandler.populateGridViewWithBinding(selectStatement2, dataGridView2);
string selectStatement3 = "SELECT itemorder.io_id as 'Order #', client.name as 'Client Name', itemorder.creation_time as 'Posted Time', itemorder.postedUser as 'User', itemorder.latest_delivery_time as 'Latest Delivery Time' FROM itemorder inner join client on itemorder.client_id = client.client_id WHERE itemorder.approval = 'Approved' AND itemorder.released='Yes'";
DatabaseHandler.populateGridViewWithBinding(selectStatement3, dataGridView3);
string selectStatement4 = "SELECT itemorder.io_id as 'Order #', client.name as 'Client Name', itemorder.creation_time as 'Posted Time', itemorder.postedUser as 'User', itemorder.latest_delivery_time as 'Latest Delivery Time' FROM itemorder inner join client on itemorder.client_id = client.client_id WHERE itemorder.approval = 'Declined' AND itemorder.released='No'";
DatabaseHandler.populateGridViewWithBinding(selectStatement4, dataGridView8);
dataGridView6.DataSource = null;
dataGridView6.Rows.Clear();
}
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 manualProductId_TextChanged(object sender, EventArgs e)
{
string query = "SELECT item_id as 'Item Code', name as 'Item Name', qty as 'Available Quantity', unit_price as 'Unit Price'FROM item WHERE item_id like '%" + findProductId.Text + "%'";
DatabaseHandler.populateGridViewWithBinding(query, dataGridView1);
string selectStatement = "SELECT raw_material.material_id as 'Material ID', raw_material.name as 'Material Name', raw_material.qty as 'Available Quantity' FROM raw_material WHERE raw_material.material_id like '%" + findProductId.Text + "%'";
DatabaseHandler.populateGridViewWithBinding(selectStatement, dataGridView2);
}
private void populateNonComboGrids()
{
string approvedSelect = "SELECT purchaseorder.po_id as 'Order #', supplier.name as 'Supplier', purchaseorder.creation_time as 'Order Creation Time', purchaseorder.postedUser as 'Posted By' FROM purchaseorder INNER JOIN supplier ON purchaseorder.supplier_id = supplier.supplier_id WHERE purchaseorder.approval = 'Approved'";
DatabaseHandler.populateGridViewWithBinding(approvedSelect, dataGridView2);
string pendingSelect = "SELECT purchaseorder.po_id as 'Order #', supplier.name as 'Supplier', purchaseorder.creation_time as 'Order Creation Time', purchaseorder.postedUser as 'Posted By' FROM purchaseorder INNER JOIN supplier ON purchaseorder.supplier_id = supplier.supplier_id WHERE purchaseorder.approval = 'Pending'";
DatabaseHandler.populateGridViewWithBinding(pendingSelect, dataGridView3);
}
private void populateGrids()
{
string approvedSelect = "SELECT purchaseorder.po_id as 'Order #', supplier.name as 'Supplier', purchaseorder.creation_time as 'Order Creation Time', purchaseorder.postedUser as 'Posted By' FROM purchaseorder INNER JOIN supplier ON purchaseorder.supplier_id = supplier.supplier_id WHERE purchaseorder.approval = 'Approved' AND purchaseorder.recieved = 'No'";
DatabaseHandler.populateGridViewWithBinding(approvedSelect, dataGridView1);
string finishedSelect = "SELECT purchaseorder.po_id as 'Order #', supplier.name as 'Supplier', purchaseorder.creation_time as 'Order Creation Time', purchaseorder.postedUser as 'Posted By' FROM purchaseorder INNER JOIN supplier ON purchaseorder.supplier_id = supplier.supplier_id WHERE purchaseorder.approval = 'Approved' AND purchaseorder.recieved = 'Yes'";
DatabaseHandler.populateGridViewWithBinding(finishedSelect, dataGridView2);
}
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 populateGrid()
{
string query = "SELECT item_id as 'Item Code', name as 'Item Name', qty as 'Available Quantity', unit_price as 'Unit Price' FROM item";
DatabaseHandler.populateGridViewWithBinding(query, dataGridView1);
string selectStatement4 = "SELECT raw_material.material_id as 'Material ID', raw_material.name as 'Material Name', raw_material.qty as 'Available Quantity' FROM raw_material";
// DatabaseHandler.populateViewwithNoParameters(selectStatement4, dataGridView2);
// string query1 = "SELECT material_id as 'Item Code', name as 'Item Name', qty as 'Available Quantity', unit_price as 'Unit Price', supplier as FROM raw_material";
DatabaseHandler.populateGridViewWithBinding(selectStatement4, dataGridView2);
}
private void dataGridView3_Click(object sender, EventArgs e)
{
try
{
string val = dataGridView3.SelectedRows[0].Cells["Order #"].Value.ToString();
string select = "SELECT purchaseorder_item.po_id as 'Order #', purchaseorder_item.material_id as 'Material Code', raw_material.name as 'Material Name', purchaseorder_item.qty as 'Qty' FROM purchaseorder_item INNER JOIN raw_material ON purchaseorder_item.material_id = raw_material.material_id WHERE purchaseorder_item.po_id = '" + val + "'";
DatabaseHandler.populateGridViewWithBinding(select, dataGridView5);
}catch (Exception err)
{
Console.WriteLine(err);
}
}
private void dataGridView3_Click(object sender, EventArgs e)
{
try
{
string val = dataGridView3.SelectedRows[0].Cells["Order #"].Value.ToString();
string select = "SELECT productionorder_item.pro_id as 'Order #', productionorder_item.item_id as 'Item Code', item.name as 'Item Name', productionorder_item.qty as 'Qty' FROM productionorder_item INNER JOIN item ON productionorder_item.item_id = item.item_id WHERE productionorder_item.pro_id = '" + val + "'";
DatabaseHandler.populateGridViewWithBinding(select, dataGridView5);
}catch (Exception err)
{
Console.WriteLine(err);
}
}
private void poNumTxt_TextChanged(object sender, EventArgs e)
{
if (System.Text.RegularExpressions.Regex.IsMatch(poNumTxt.Text, "[^0-9]"))
{
MessageBox.Show("Please enter only numbers.");
poNumTxt.Text = poNumTxt.Text.Remove(poNumTxt.Text.Length - 1);
}
if (poNumTxt.Text == "")
{
string selectedPo = "SELECT purchaseorder.po_id as 'Order #', supplier.name as 'Supplier', purchaseorder.creation_time as 'Order Creation Time', purchaseorder.postedUser as 'Posted By' FROM purchaseorder INNER JOIN supplier ON purchaseorder.supplier_id = supplier.supplier_id WHERE purchaseorder.approval = 'Approved' AND purchaseorder.recieved='No'";
DatabaseHandler.populateGridViewWithBinding(selectedPo, dataGridView1);
}
}
private void populateNonComboGrids()
{
string approvedSelect = "SELECT productionorder.pro_id as 'Order #', productionorder.creation_time as 'Order Creation Time', productionorder.postedUser as 'Posted By' FROM productionorder WHERE productionorder.approval = 'Approved' AND productionorder.recieved = 'No'";
DatabaseHandler.populateGridViewWithBinding(approvedSelect, dataGridView2);
string pendingSelect = "SELECT productionorder.pro_id as 'Order #', productionorder.creation_time as 'Order Creation Time', productionorder.postedUser as 'Posted By' FROM productionorder WHERE productionorder.approval = 'Pending'";
DatabaseHandler.populateGridViewWithBinding(pendingSelect, dataGridView3);
string FinishedSelect = "SELECT productionorder.pro_id as 'Order #', productionorder.creation_time as 'Order Creation Time', productionorder.postedUser as 'Posted By' FROM productionorder WHERE productionorder.approval = 'Approved' AND productionorder.recieved = 'Yes' ";
DatabaseHandler.populateGridViewWithBinding(FinishedSelect, dataGridView6);
}
private void dataGridView1_Click(object sender, EventArgs e)
{
try
{
string val = dataGridView1.SelectedRows[0].Cells["Supplier ID"].Value.ToString();
string select = "SELECT material_id as 'Material Code', supplier_id as 'Supplier ID', unit_price as 'Unit Price', lead_time as 'Lead Time' FROM supplier_material WHERE supplier_id = '" + val + "'";
DatabaseHandler.populateGridViewWithBinding(select, dataGridView5);
}
catch (Exception err)
{
Console.WriteLine(err);
}
}
private void apply_btn_Click(object sender, EventArgs e)
{
string theDate = dateTimePicker1.Value.ToShortDateString();
Console.WriteLine("date" + theDate);
string selectStatement1 = "SELECT itemorder.io_id as 'Order #', client.name as 'Client Name', itemorder.creation_time as 'Posted Time', itemorder.postedUser as 'User', itemorder.latest_delivery_time as 'Latest Delivery Time' FROM itemorder inner join client on itemorder.client_id = client.client_id WHERE itemorder.approval = 'Pending' AND itemorder.released='No' AND itemorder.latest_delivery_time='" + theDate + "'";
DatabaseHandler.populateGridViewWithBinding(selectStatement1, dataGridView1);
string selectStatement2 = "SELECT itemorder.io_id as 'Order #', client.name as 'Client Name', itemorder.creation_time as 'Posted Time', itemorder.postedUser as 'User', itemorder.latest_delivery_time as 'Latest Delivery Time' FROM itemorder inner join client on itemorder.client_id = client.client_id WHERE itemorder.approval = 'Approved' AND itemorder.released='No' AND itemorder.latest_delivery_time='" + theDate + "'";
DatabaseHandler.populateGridViewWithBinding(selectStatement1, dataGridView1);
DatabaseHandler.populateGridViewWithBinding(selectStatement2, dataGridView2);
}
private void DataGridView1_Click(object sender, EventArgs e)
{
try
{
string val = dataGridView1.SelectedRows[0].Cells["Item Code"].Value.ToString();
string select = "SELECT item.item_id as 'Item Code', composition.material_id as 'Material ID', composition.quantity as 'Quantity' FROM item INNER JOIN composition ON item.item_id = composition.item_id WHERE item.item_id = '" + val + "'";
DatabaseHandler.populateGridViewWithBinding(select, dataGridView5);
}
catch (Exception err)
{
Console.WriteLine(err);
}
}
private void populateDataGrid()
{
string selectStatement = "SELECT id as 'Dispatch ID', bom_id as 'BOM ID', creation_time as 'Dispatch Order Creation time', postedUser as 'Posted User' FROM material_dispatch WHERE approval ='Pending' ";
DatabaseHandler.populateGridViewWithBinding(selectStatement, dataGridView1);
string selectStatement1 = "SELECT id as 'Dispatch ID', bom_id as 'BOM ID', creation_time as 'Dispatch Order Creation time', postedUser as 'Posted User' FROM material_dispatch WHERE approval ='Approved' AND released='No' ";
DatabaseHandler.populateGridViewWithBinding(selectStatement1, dataGridView2);
string selectStatement2 = "SELECT id as 'Dispatch ID', bom_id as 'BOM ID', creation_time as 'Dispatch Order Creation time', postedUser as 'Posted User' FROM material_dispatch WHERE approval ='Approved' AND released='Yes' ";
DatabaseHandler.populateGridViewWithBinding(selectStatement2, dataGridView3);
string selectStatement3 = "SELECT bom_id as 'BOM ID', creation_time as 'BOM creation time', postedUser as 'Posted User', pro_id as 'Production Order ID' FROM bom ";
DatabaseHandler.populateGridViewWithBinding(selectStatement3, dataGridView5);
dataGridView6.DataSource = null;
dataGridView6.Rows.Clear();
}
private void ApplyBtn_Click(object sender, EventArgs e)
{
setPoNum();
string poNum = poText.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 productionorder WHERE pro_id = @poNum AND recieved=@recieved AND approval=@approval", paramList);
List <MySqlParameter> paramList2 = new List <MySqlParameter>();
paramList2.Add(new MySqlParameter("@poNum", poNum));
int returnedRowCount2 = DatabaseHandler.returnRowCount("SELECT * FROM bom WHERE pro_id = @poNum", paramList2);
if (returnedRowCount == 1 && returnedRowCount2 == 0)
{
// poText.Enabled = false;
string select = "SELECT productionorder_item.pro_id as 'Order #', productionorder_item.item_id as 'Item Code', item.name as 'Item Name', productionorder_item.qty as 'Qty' FROM productionorder_item INNER JOIN item ON productionorder_item.item_id = item.item_id WHERE productionorder_item.pro_id = '" + poNum + "'";
DatabaseHandler.populateGridViewWithBinding(select, dataGridView2);
setPoNum();
}
else if (returnedRowCount2 == 1)
{
MessageBox.Show("BOM already created for that production order. Please Try again..");
}
else
{
poText.Enabled = true;
MessageBox.Show("No such production order exists or the Order may not be approved. Please Try again..");
poText.Clear();
dataGridView2.DataSource = null;
dataGridView2.Refresh();
}
}
private void Add_btn_Click(object sender, EventArgs e)
{
string bomId;
bomId = bomId_txt.Text;
if (bomId == null)
{
MessageBox.Show("Invalid Selection!");
return;
}
int rowcount = DatabaseHandler.returnRowCountWithoutParams("select id from material_dispatch where bom_id='" + bomId + "'");
if (rowcount == 1)
{
MessageBox.Show("MATERIAL DISPATCH ORDER EXISTS FOR THE SELECTED BOM ID!!!");
return;
}
else
{
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 = '" + bomId + "' ";
DatabaseHandler.populateGridViewWithBinding(selectStatement, dataGridView4);
}
}
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_id = @poNum AND recieved=@recieved AND approval=@approval", paramList);
if (returnedRowCount == 1)
{
poNumTxt.Enabled = false;
string select = "select supplier.name from purchaseorder inner join supplier on purchaseorder.supplier_id = supplier.supplier_id where purchaseorder.po_id ='" + poNum + "'";
string returnedSupplierName = DatabaseHandler.returnOneValueWithoutParams(select, "name");
supplierNameLbl.Text = returnedSupplierName;
string query = "SELECT purchaseorder_item.material_id as 'Item Code', raw_material.name as 'Item', purchaseorder_item.qty as 'Qty' FROM purchaseorder_item INNER JOIN raw_material ON purchaseorder_item.material_id = raw_material.material_id WHERE purchaseorder_item.po_id = '" + poNum + "'";
DatabaseHandler.populateGridViewWithBinding(query, dataGridView3);
string selectedPo = "SELECT purchaseorder.po_id as 'Order #', supplier.name as 'Supplier', purchaseorder.creation_time as 'Order Creation Time', purchaseorder.postedUser as 'Posted By' FROM purchaseorder INNER JOIN supplier ON purchaseorder.supplier_id = supplier.supplier_id WHERE purchaseorder.approval = 'Approved' AND purchaseorder.po_id='" + 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 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);
}