private void ItemOrderDeclineInfo_Load(object sender, EventArgs e)
{
itemOrder_lbl.Text = ItemDispatch.declineOrderNO;
client_name_lbl.Text = ItemDispatch.declineOrderClient;
otherInfo_txt.Enabled = false;
clientEmail = DatabaseHandler.returnOneValueWithoutParams("SELECT email FROM client WHERE name='" + ItemDispatch.declineOrderClient + "'", "email");
}
private void setPoNum()
{
string lastPo = DatabaseHandler.returnOneValueWithoutParams("SELECT * FROM productionorder", "pro_id");
int lastPoNum;
if (lastPo == "Null Data!")
{
lastPoNum = 0;
}
else
{
lastPoNum = Int32.Parse(lastPo);
}
PO.Text = (lastPoNum + 1).ToString();
globalLastPo = lastPoNum;
}
private void setReqNum()
{
string lastRo = DatabaseHandler.returnOneValueWithoutParams("SELECT * FROM material_dispatch", "id");
int lastRoNum;
if (lastRo == "Null Data!")
{
lastRoNum = 0;
}
else
{
lastRoNum = Int32.Parse(lastRo);
}
dispatchId_lbl.Text = (lastRoNum + 1).ToString();
globalLastRo = lastRoNum;
}
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 setReqNum()
{
string lastRo = DatabaseHandler.returnOneValueWithoutParams("SELECT * FROM itemorder", "io_id");
int lastRoNum;
if (lastRo == "Null Data!")
{
lastRoNum = 0;
}
else
{
lastRoNum = Int32.Parse(lastRo);
}
reqestNum.Text = (lastRoNum + 1).ToString();
globalLastRo = lastRoNum;
}
private void itemIdTxt_Leave(object sender, EventArgs e)
{
if (DatabaseHandler.returnRowCountWithoutParams("SELECT * FROM raw_material where material_id='" + itemIdTxt.Text + "'") != 0)
{
String matrialName;
matrialName = DatabaseHandler.returnOneValueWithoutParams("SELECT name FROM raw_material WHERE material_id='" + itemIdTxt.Text + "'", "name").ToString();
itemNameTxt.Text = matrialName;
itemNameTxt.Enabled = false;
addNewMaterial_btn.Enabled = false;
addItemBtn.Enabled = true;
}
else
{
itemNameTxt.Enabled = true;
addNewMaterial_btn.Enabled = true;
addItemBtn.Enabled = false;
}
}
private void setPoNum()
{
string lastbom = DatabaseHandler.returnOneValueWithoutParams("SELECT * FROM bom", "bom_id");
int lastbomNum;
if (lastbom == "Null Data!")
{
lastbomNum = 0;
}
else
{
lastbomNum = Int32.Parse(lastbom);
}
bom_lbl.Text = (lastbomNum + 1).ToString();
globalLastbom = lastbomNum;
bomNo = bom_lbl.Text;
}
private void setId()
{
string lastId = DatabaseHandler.returnOneValueWithoutParams("SELECT * FROM client", "client_id");
string nextId;
if (lastId == "Null Data!")
{
nextId = "#1001";
}
else
{
var prefix = System.Text.RegularExpressions.Regex.Match(lastId, "^\\#+").Value;
var number = System.Text.RegularExpressions.Regex.Replace(lastId, "^\\#+", "");
var i = int.Parse(number) + 1;
nextId = (prefix + i.ToString(new string('0', number.Length))).ToString();
}
identifierTxt.Text = nextId;
}
private void setId()
{
string lastId = DatabaseHandler.returnOneValueWithoutParams("SELECT * FROM supplier", "supplier_id");
string nextId;
string lastId2 = DatabaseHandler.returnOneValueWithoutParams("SELECT * FROM raw_material", "material_id");
string nextId2;
if (lastId == "Null Data!")
{
nextId = "&0001";
}
else
{
var prefix = System.Text.RegularExpressions.Regex.Match(lastId, "^\\&+").Value;
var number = System.Text.RegularExpressions.Regex.Replace(lastId, "^\\&+", "");
var i = int.Parse(number) + 1;
nextId = (prefix + i.ToString(new string('0', number.Length))).ToString();
}
supplierCodeTxt.Text = nextId;
if (lastId2 == "Null Data!")
{
nextId2 = "$0001";
}
else
{
var prefix = System.Text.RegularExpressions.Regex.Match(lastId2, "^\\$+").Value;
var number = System.Text.RegularExpressions.Regex.Replace(lastId2, "^\\$+", "");
var i = int.Parse(number) + 1;
nextId2 = (prefix + i.ToString(new string('0', number.Length))).ToString();
}
Console.WriteLine("next material Id" + nextId2);
Console.WriteLine("next item Id" + nextId);
itemIdTxt.Text = "";
itemIdTxt.Text = nextId2;
}
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 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 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 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)
{
}
}