private void btnDeclareComplete_Click(object sender, EventArgs e)
{
String sql = "";
String putaway_id = txtPutawayID.Text;
DateTime now = DateTime.Now;
// Update Putaway
sql += "UPDATE PutawayDetails SET actual_qty = expected_qty WHERE putaway= '" + putaway_id + "'; ";
// Update Transactions Ledger
DataTable detailsDT = FAQ.GetPutawayDetails(putaway_id);
// Update Transaction Ledger
{
// Out with the container
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
outsDT.Rows.Add(txtContainer.Text, now, "OUT", "PUTAWAY_DECLARE_COMPLETE", putaway_id);
sql += LedgerSupport.UpdateLocationLedger(outsDT);
// In with the location
DataTable insDT = LedgerSupport.GetLocationLedgerDT();
foreach (DataRow row in detailsDT.Rows)
{
insDT.Rows.Add(row["location"], now, "IN", "PUTAWAY_DECLARE_COMPLETE", putaway_id);
}
sql += LedgerSupport.UpdateLocationLedger(insDT);
}
// Update Location Products Ledger
{
// Out with the container
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataRow row in detailsDT.Rows)
{
outsDT.Rows.Add(txtContainer.Text, row["product"].ToString(), int.Parse(row["expected_qty"].ToString()) * -1, row["uom"].ToString(), row["lot_no"].ToString(), row["expiry"].ToString());
}
sql += LedgerSupport.UpdateLocationProductsLedger(outsDT);
// In with the location
DataTable insDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataRow row in detailsDT.Rows)
{
insDT.Rows.Add(row["location"].ToString(), row["product"].ToString(), row["expected_qty"].ToString(), row["uom"].ToString(), row["lot_no"].ToString(), row["expiry"].ToString());
}
sql += LedgerSupport.UpdateLocationProductsLedger(insDT);
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
this.Close();
}
private void SaveData()
{
String id = DataSupport.GetNextMenuCodeInt("PL");
DateTime now = DateTime.Now;
// Save Transaction
String sql = "";
foreach (DataGridViewRow row in parent.header_grid.SelectedRows)
{
sql += DataSupport.GetInsert("ForDisposals", Utils.ToDict(
"trans_id", id
, "product", row.Cells["Product"].Value.ToString()
, "qty", row.Cells["Qty"].Value.ToString()
, "uom", row.Cells["Uom"].Value.ToString()
, "lot_no", row.Cells["Lot No"].Value.ToString()
, "expiry", row.Cells["Expiry"].Value.ToString()
, "reason", row.Cells["Days To Expiry"].Value.ToString()
));
// Update Transaction Ledger
{
DataTable insDT = LedgerSupport.GetLocationLedgerDT();
insDT.Rows.Add("STAGING-OUT", now, "IN", "CANCEL_ORDER", id);
sql += LedgerSupport.UpdateLocationLedger(insDT);
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
outsDT.Rows.Add(row.Cells["Location"].Value.ToString(), now, "OUT", "CANCEL_ORDER", id);
sql += LedgerSupport.UpdateLocationLedger(outsDT);
}
// Update Location Products Ledger
{
DataTable insDT = LedgerSupport.GetLocationProductsLedgerDT();
insDT.Rows.Add("STAGING-OUT", row.Cells["Product"].Value, row.Cells["Qty"].Value, row.Cells["Uom"].Value, row.Cells["Lot No"].Value, row.Cells["Expiry"].Value);
sql += LedgerSupport.UpdateLocationProductsLedger(insDT);
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
outsDT.Rows.Add(row.Cells["Location"].Value.ToString(), row.Cells["Product"].Value, int.Parse(row.Cells["Qty"].Value.ToString()) * -1, row.Cells["Uom"].Value, row.Cells["Lot No"].Value, row.Cells["Expiry"].Value, int.Parse(row.Cells["qty"].Value.ToString()) * -1, int.Parse(row.Cells["Qty"].Value.ToString()) * -1);
sql += LedgerSupport.UpdateLocationProductsLedger(outsDT);
}
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
webBrowser1.DocumentText = webBrowser1.DocumentText.Replace("(issued on save)", id);
btnPrintPreview.Text = "Print";
btnCancel.Visible = false;
}
private void btnStockCheck_Click(object sender, EventArgs e)
{
try
{
var row = headerGrid.SelectedRows[0];
StringBuilder sql = new StringBuilder();
Dictionary <String, Object> header = new Dictionary <string, object>();
DataTable dt = FAQ.GetOMSOutgoingDetails(row.Cells["out_shipment_id"].Value.ToString());
if (FAQ.IsAlreadyDownloaded(row.Cells["out_shipment_id"].Value.ToString()))
{
LedgerSupport.StockCheck();
return;
}
String order_id = row.Cells["out_shipment_id"].Value.ToString();
header.Add("order_id", order_id);
header.Add("client", row.Cells["client"].Value);
header.Add("reference", row.Cells["document_reference"].Value);
header.Add("reference_date", row.Cells["document_reference_date"].Value);
header.Add("order_date", row.Cells["datetime"].Value);
header.Add("recipient", row.Cells["authorized_tms"].Value);
header.Add("customer", row.Cells["customer_id"].Value);
header.Add("oms_shipment_id", row.Cells["out_shipment_id"].Value);
sql.Append(DataSupport.GetInsert("ReleaseOrders", header));
foreach (DataRow detail_row in dt.Rows)
{
Dictionary <String, Object> detail = new Dictionary <string, object>();
detail.Add("release_order", order_id);
detail.Add("product", detail_row["product"]);
detail.Add("uom", detail_row["uom"]);
detail.Add("qty", detail_row["expected_qty"]);
sql.Append(DataSupport.GetInsert("ReleaseOrderDetails", detail));
}
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
LedgerSupport.StockCheck();
MessageBox.Show("Stock Check Complete");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void SaveData()
{
DateTime now = DateTime.Now;
String putaway_id = DataSupport.GetNextMenuCodeInt("PA");
// Save Transaction
String sql = DataSupport.GetInsert("Putaways", Utils.ToDict(
"putaway_id", putaway_id
, "container", "CANCELLED_PALLET"
, "encoded_on", now
, "completed", now
));
foreach (DataGridViewRow row in parent.items_grid.Rows)
{
sql += DataSupport.GetInsert("PutawayDetails", Utils.ToDict(
"putaway", putaway_id
, "product", row.Cells["Product"].Value.ToString()
, "expected_qty", row.Cells["Qty"].Value.ToString()
, "uom", row.Cells["Uom"].Value.ToString()
, "lot_no", row.Cells["Lot No"].Value.ToString()
, "expiry", row.Cells["Expiry"].Value.ToString()
, "location", row.Cells["Putaway To"].Value.ToString()
, "actual_qty", row.Cells["Qty"].Value.ToString()
));
}
// Update Transaction Ledger
{
// Out with the cancelled pallet
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
outsDT.Rows.Add("CANCELLED_PALLET", now, "OUT", "CANCELLED_PUTAWAY", putaway_id);
sql += LedgerSupport.UpdateLocationLedger(outsDT);
// In with the location
DataTable insDT = LedgerSupport.GetLocationLedgerDT();
foreach (DataGridViewRow row in parent.items_grid.Rows)
{
insDT.Rows.Add(row.Cells["Putaway To"].Value.ToString(), now, "IN", "CANCELLED_PUTAWAY", putaway_id);
}
sql += LedgerSupport.UpdateLocationLedger(insDT);
}
// Update Location Products Ledger
{
// Out with the cancelled pallet
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataGridViewRow row in parent.items_grid.Rows)
{
outsDT.Rows.Add("CANCELLED_PALLET", row.Cells["Product"].Value, int.Parse(row.Cells["Qty"].Value.ToString()) * -1, row.Cells["Uom"].Value, row.Cells["Lot No"].Value, row.Cells["Expiry"].Value);
}
sql += LedgerSupport.UpdateLocationProductsLedger(outsDT);
// In with the location
DataTable insDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataGridViewRow row in parent.items_grid.Rows)
{
insDT.Rows.Add(row.Cells["Putaway To"].Value.ToString(), row.Cells["Product"].Value, row.Cells["Qty"].Value, row.Cells["Uom"].Value, row.Cells["Lot No"].Value, row.Cells["Expiry"].Value);
}
sql += LedgerSupport.UpdateLocationProductsLedger(insDT);
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
webBrowser1.DocumentText = webBrowser1.DocumentText.Replace("(issued on save)", putaway_id);
btnPrintPreview.Text = "Print";
btnCancel.Visible = false;
}
private void SaveData()
{
String now = DateTime.Now.ToString();
String sql = "";
String bs_id = DataSupport.GetNextMenuCodeInt("BS");
// Save Transaction
sql += DataSupport.GetInsert("BadStockDeclarations", Utils.ToDict(
"declaration_id", bs_id
, "declared_by", parent.txtDeclaredBy.Text
, "declared_on", now
, "status", "OK"
));
foreach (DataGridViewRow row in parent.header_grid.Rows)
{
sql += DataSupport.GetInsert("BadStockDeclarationDetails", Utils.ToDict(
"declaration", bs_id
, "line", parent.header_grid.Rows.IndexOf(row) + 1
, "product", row.Cells["product"].Value.ToString()
, "uom", row.Cells["uom"].Value.ToString()
, "lot_no", row.Cells["lot_no"].Value.ToString()
, "expiry", row.Cells["expiry"].Value.ToString()
, "location", row.Cells["location"].Value.ToString()
, "qty", row.Cells["qty"].Value.ToString()
, "reason", row.Cells["reason"].Value.ToString()
, "bad_stock_storage", row.Cells["bad_stock_storage"].Value.ToString()
));
}
foreach (DataGridViewRow row in parent.header_grid.Rows)
{
// Update Transaction Ledger
{
// Out with the good storage location
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
outsDT.Rows.Add(row.Cells["location"].Value.ToString(), now, "OUT", "BAD STOCK DEC", bs_id);
sql += LedgerSupport.UpdateLocationLedger(outsDT);
// In with the bad storage location
DataTable insDT = LedgerSupport.GetLocationLedgerDT();
insDT.Rows.Add(row.Cells["bad_stock_storage"].Value.ToString(), now, "IN", "BAD STOCK DEC", bs_id);
sql += LedgerSupport.UpdateLocationLedger(insDT);
}
// Update Location Products Ledger
{
// Out with the good storage location
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
outsDT.Rows.Add(row.Cells["location"].Value.ToString(), row.Cells["product"].Value, int.Parse(row.Cells["qty"].Value.ToString()) * -1, row.Cells["uom"].Value, row.Cells["lot_no"].Value, row.Cells["expiry"].Value);
sql += LedgerSupport.UpdateLocationProductsLedger(outsDT);
// In with the bad storage location
DataTable insDT = LedgerSupport.GetLocationProductsLedgerDT();
insDT.Rows.Add(row.Cells["bad_stock_storage"].Value.ToString(), row.Cells["product"].Value, row.Cells["qty"].Value, row.Cells["uom"].Value, row.Cells["lot_no"].Value, row.Cells["expiry"].Value);
sql += LedgerSupport.UpdateLocationProductsLedger(insDT);
}
// Update For Resolution
sql += DataSupport.GetInsert("ForResolutions", Utils.ToDict(
"trans_source", "BAD_STOCK_DEC"
, "trans_id", bs_id
, "detected_on", now
, "product", row.Cells["product"].Value
, "uom", row.Cells["uom"].Value
, "lot_no", row.Cells["lot_no"].Value
, "expiry", row.Cells["expiry"].Value
, "location", row.Cells["bad_stock_storage"].Value.ToString()
, "variance_type", "BAD STOCK"
, "variance_qty", row.Cells["qty"].Value
, "status", "FOR RESOLUTION"
, "line", parent.header_grid.Rows.IndexOf(row) + 1
));
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
webBrowser1.DocumentText = webBrowser1.DocumentText.Replace("(issued on save)", bs_id);
btnPrintPreview.Text = "Print";
btnCancel.Visible = false;
}
private void DeclareComplete()
{
//MessageBox.Show("Under Construction...");
//return;
StringBuilder sql = new StringBuilder();
String picklist_id = txtPicklist.Text;
DateTime now = DateTime.Now;
//// Update Picklist
sql.Append("UPDATE Picklists SET status = 'DECLARED COMPLETE' WHERE picklist_id= '" + picklist_id + "'; ");
// Update Transaction Ledger
{
DataTable insDT = LedgerSupport.GetLocationLedgerDT();
insDT.Rows.Add("STAGING-OUT", now, "IN", "CASEBREAK_PICKLIST_DECLARE_COMPLETE", picklist_id);
foreach (DataGridViewRow row in scanned_grid_details.Rows)
{
insDT.Rows.Add("STAGING-IN", now, "IN", "CASEBREAK_PICKLIST_DECLARE_COMPLETE", picklist_id);
}
sql.Append(LedgerSupport.UpdateLocationLedger(insDT));
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
foreach (DataGridViewRow row in scanned_grid.Rows)
{
outsDT.Rows.Add(row.Cells["original_location"].Value, now, "OUT", "CASEBREAK_PICKLIST_DECLARE_COMPLETE", picklist_id);
}
sql.Append(LedgerSupport.UpdateLocationLedger(outsDT));
}
//// Update Location Products Ledger
{
DataTable insDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataGridViewRow row in scanned_grid_details.Rows)
{
insDT.Rows.Add("STAGING-IN", row.Cells["product"].Value, row.Cells["qty"].Value, row.Cells["uom"].Value, row.Cells["lot_no"].Value, row.Cells["expiry"].Value);
}
foreach (DataGridViewRow row in scanned_grid.Rows)
{
insDT.Rows.Add("STAGING-OUT", row.Cells["product"].Value, row.Cells["qty"].Value, row.Cells["uom"].Value, row.Cells["lot_no"].Value, row.Cells["expiry"].Value);
}
sql.Append(LedgerSupport.UpdateLocationProductsLedger(insDT));
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataGridViewRow row in scanned_grid.Rows)
{
outsDT.Rows.Add(row.Cells["original_location"].Value, row.Cells["product"].Value, int.Parse(row.Cells["qty"].Value.ToString()) * -1, row.Cells["uom"].Value, row.Cells["lot_no"].Value, row.Cells["expiry"].Value, int.Parse(row.Cells["qty"].Value.ToString()) * -1, int.Parse(row.Cells["qty"].Value.ToString()) * -1);
}
sql.Append(LedgerSupport.UpdateLocationProductsLedger(outsDT));
}
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
//this.Close();
}
private void btnPrintPreview_Click(object sender, EventArgs e)
{
String now = DateTime.Now.ToString();
String phcount = parent.phcount_id;
RollbackDataSupport ds = new RollbackDataSupport();
// Save to Transaction Tables
String sql = "";
sql += " UPDATE PhysicalCounts SET finished_on = '" + now + "' WHERE phcount_id='" + phcount + "'; ";
DataTable dt = ds.RunDataSet("SELECT * FROM PhysicalCountDetailItems WHERE phcount='" + phcount + "'").Tables[0];
foreach (DataGridViewRow item_row in parent.header_grid.Rows)
{
if (item_row.Cells["expected"].Value.ToString() == "NO")
{
sql += DataSupport.GetUpsert("PhysicalCountDetails", Utils.ToDict(
"phcount", phcount
, "location", item_row.Cells["location"].Value.ToString()
, "status", "ACTIVE"
), "phcount", "location");
sql += DataSupport.GetInsert("PhysicalCountDetailItems", Utils.ToDict(
"phcount", phcount
, "location", item_row.Cells["location"].Value.ToString()
, "product", item_row.Cells["product"].Value.ToString()
, "uom", item_row.Cells["uom"].Value.ToString()
, "lot_no", item_row.Cells["lot_no"].Value.ToString()
, "expiry", item_row.Cells["Expiry"].Value.ToString()
, "expected_qty", 0
, "actual_qty", item_row.Cells["actual_qty"].Value.ToString()
, "line", dt.Rows.Count + 1
));
}
else
{
foreach (DataRow row in dt.Rows)
{
// Skip if expected is empty
if (row["product"].ToString() == "EMPTY")
{
continue;
}
if (row["location"].ToString() == item_row.Cells["location"].Value.ToString() &&
row["product"].ToString() == item_row.Cells["product"].Value.ToString() &&
row["uom"].ToString() == item_row.Cells["uom"].Value.ToString() &&
row["lot_no"].ToString() == item_row.Cells["lot_no"].Value.ToString() &&
DateTime.Parse(row["expiry"].ToString()).ToShortDateString() == item_row.Cells["Expiry"].Value.ToString()
)
{
sql += @"UPDATE PhysicalCountDetailItems SET actual_qty = '" + item_row.Cells["actual_qty"].Value.ToString() + @"'
WHERE phcount = '" + phcount + @"'
AND location = '" + row["location"].ToString() + @"'
AND line = '" + row["line"].ToString() + @"'
";
break;
}
}
}
}
ds.RunNonQuery(sql);
// Update Ledgers
String update_sql = "";
{
DataTable details_dt = ds.RunDataSet("SELECT * FROM PhysicalCountDetailItems WHERE phcount='" + phcount + "'").Tables[0];
foreach (DataRow item_row in details_dt.Rows)
{
if (item_row["shortage"].ToString() != "0")
{
// Update Transaction Ledger
{
// Out with the location
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
outsDT.Rows.Add(item_row["location"].ToString(), now, "OUT", "PHYSICAL_COUNT", phcount);
update_sql += LedgerSupport.UpdateLocationLedger(outsDT);
}
// Update Location Products Ledger
{
// Out with the location
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
outsDT.Rows.Add(item_row["location"].ToString(), item_row["product"].ToString(), int.Parse(item_row["shortage"].ToString()) * -1, item_row["uom"].ToString(), item_row["lot_no"].ToString(), item_row["expiry"].ToString());
update_sql += LedgerSupport.UpdateLocationProductsLedger(outsDT);
}
// Update For Resolution
update_sql += DataSupport.GetInsert("ForResolutions", Utils.ToDict(
"trans_source", "PHYSICAL_COUNT"
, "trans_id", phcount
, "detected_on", now
, "product", item_row["product"].ToString()
, "uom", item_row["uom"].ToString()
, "lot_no", item_row["lot_no"].ToString()
, "expiry", item_row["expiry"].ToString()
, "location", item_row["location"].ToString()
, "variance_type", "SHORTAGE"
, "variance_qty", item_row["shortage"].ToString()
, "status", "FOR RESOLUTION"
, "line", details_dt.Rows.IndexOf(item_row) + 1
));
}
else if (item_row["overage"].ToString() != "0")
{
// Update Transaction Ledger
{
// In with the "found" location
DataTable insDT = LedgerSupport.GetLocationLedgerDT();
insDT.Rows.Add(item_row["location"].ToString(), now, "IN", "PHYSICAL_COUNT", phcount);
update_sql += LedgerSupport.UpdateLocationLedger(insDT);
}
// Update Location Products Ledger
{
// In with the "found" location
DataTable insDT = LedgerSupport.GetLocationProductsLedgerDT();
insDT.Rows.Add(item_row["location"].ToString(), item_row["product"].ToString(), item_row["overage"].ToString(), item_row["uom"].ToString(), item_row["lot_no"].ToString(), item_row["expiry"].ToString());
update_sql += LedgerSupport.UpdateLocationProductsLedger(insDT);
}
// Update For Resolution
update_sql += DataSupport.GetInsert("ForResolutions", Utils.ToDict(
"trans_source", "PHYSICAL_COUNT"
, "trans_id", phcount
, "detected_on", now
, "product", item_row["product"].ToString()
, "uom", item_row["uom"].ToString()
, "lot_no", item_row["lot_no"].ToString()
, "expiry", item_row["expiry"].ToString()
, "location", item_row["location"].ToString()
, "variance_type", "OVERAGE"
, "variance_qty", item_row["overage"].ToString()
, "status", "FOR RESOLUTION"
, "line", details_dt.Rows.IndexOf(item_row) + 1
));
}
}
}
if (update_sql != "")
{
ds.RunNonQuery(update_sql);
}
ds.CommitData();
MessageBox.Show("Success");
// webBrowser1.DocumentText = webBrowser1.DocumentText.Replace("(issued on save)", id);
btnPrintPreview.Text = "Print";
btnCancel.Visible = false;
}
private void SaveData()
{
// Inform OMS
var selected_row = parent.header_grid.SelectedRows[0];
String order_id = selected_row.Cells["Order ID"].Value.ToString();
DateTime now = DateTime.Now;
String id = DataSupport.GetNextMenuCodeInt("ORC");
// Get Reason
String reason = parent.dialog.txtReason.Text.EscapeString();
String sql = "";
sql += " UPDATE ReleaseOrders SET status='CANCELLED',cancel_id='" + id + "', cancel_reason ='" + reason + "', cancel_datetime='" + now + "', cancel_by='" + parent.dialog.txtCancelled.Text.EscapeString() + "' WHERE order_id= '" + order_id + "'; ";
// Get Picked Items
DataTable pickedDT = FAQ.GetPickedItems(FAQ.GetPicklistID(order_id), order_id);
// Good Stocks
// Move from Staging out to Cancelled Pallet
// Update Transaction Ledger
{
DataTable insDT = LedgerSupport.GetLocationLedgerDT();
insDT.Rows.Add("CANCELLED_PALLET", now, "IN", "CANCEL_ORDER", order_id);
sql += LedgerSupport.UpdateLocationLedger(insDT);
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
outsDT.Rows.Add("STAGING-OUT", now, "OUT", "CANCEL_ORDER", order_id);
sql += LedgerSupport.UpdateLocationLedger(outsDT);
}
// Update Location Products Ledger
{
DataTable insDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataRow row in pickedDT.Rows)
{
insDT.Rows.Add("CANCELLED_PALLET", row["product"], row["qty"], row["uom"], row["lot_no"], row["expiry"]);
}
sql += LedgerSupport.UpdateLocationProductsLedger(insDT);
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataRow row in pickedDT.Rows)
{
outsDT.Rows.Add("STAGING-OUT", row["product"], int.Parse(row["qty"].ToString()) * -1, row["uom"], row["lot_no"], row["expiry"], int.Parse(row["qty"].ToString()) * -1, int.Parse(row["qty"].ToString()) * -1);
}
sql += LedgerSupport.UpdateLocationProductsLedger(outsDT);
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
webBrowser1.DocumentText = webBrowser1.DocumentText.Replace("(issued on save)", id);
btnPrintPreview.Text = "Print";
btnCancel.Visible = false;
}
private void DeclareIncomplete()
{
String sql = "";
String picklist_id = txtPicklist.Text;
String now = DateTime.Now.ToString();
// Flags if user chooses missing / bad stocks for order resolution updating
Boolean has_missing = false;
Boolean has_bad_stocks = false;
DataTable BadStockDT = new DataTable();
BadStockDT.Columns.Add("Location");
BadStockDT.Columns.Add("Product");
BadStockDT.Columns.Add("Uom");
BadStockDT.Columns.Add("Lot No");
BadStockDT.Columns.Add("Expiry");
BadStockDT.Columns.Add("Qty");
BadStockDT.Columns.Add("Reason");
BadStockDT.Columns.Add("Bad Stock Storage");
// Update Picklist
sql += "UPDATE Picklists SET status = 'DECLARED INCOMPLETE' WHERE picklist_id= '" + picklist_id + "'; ";
DataTable order_ids = FAQ.GetOrdersFromPicklist(picklist_id);
// Update Order Status
foreach (DataRow row in order_ids.Rows)
{
sql += " UPDATE ReleaseOrders SET status='FOR RELEASING' WHERE order_id= '" + row["order_id"] + "' ";
}
// Good Stocks
// Move from Storage to Staging out
// Update Transaction Ledger
{
DataTable insDT = LedgerSupport.GetLocationLedgerDT();
insDT.Rows.Add("STAGING-OUT", now, "IN", "PICKLIST_DECLARE_INCOMPLETE", picklist_id);
sql += LedgerSupport.UpdateLocationLedger(insDT);
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
foreach (DataGridViewRow row in scanned_grid.Rows)
{
outsDT.Rows.Add(row.Cells["original_location"].Value, now, "OUT", "PICKLIST_DECLARE_INCOMPLETE", picklist_id);
}
sql += LedgerSupport.UpdateLocationLedger(outsDT);
}
// Update Location Products Ledger
{
DataTable insDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataGridViewRow row in scanned_grid.Rows)
{
insDT.Rows.Add("STAGING-OUT", row.Cells["product"].Value, row.Cells["qty"].Value, row.Cells["uom"].Value, row.Cells["lot_no"].Value, row.Cells["expiry"].Value);
}
sql += LedgerSupport.UpdateLocationProductsLedger(insDT);
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataGridViewRow row in scanned_grid.Rows)
{
outsDT.Rows.Add(row.Cells["original_location"].Value, row.Cells["product"].Value, int.Parse(row.Cells["qty"].Value.ToString()) * -1, row.Cells["uom"].Value, row.Cells["lot_no"].Value, row.Cells["expiry"].Value, int.Parse(row.Cells["qty"].Value.ToString()) * -1, int.Parse(row.Cells["qty"].Value.ToString()) * -1);
}
sql += LedgerSupport.UpdateLocationProductsLedger(outsDT);
}
// Missing / Bad Stocks
// Specify if it's missing or bad
MissingOrBadGridWindow dialog = new MissingOrBadGridWindow();
foreach (DataGridViewRow row in picklist_details_grid.Rows)
{
int qty = int.Parse(row.Cells["Quantity"].Value.ToString());
for (int i = 0; i < qty; i++)
{
int index = dialog.header_grid.Rows.Add(
row.Cells["Product"].Value.ToString()
, row.Cells["Uom"].Value.ToString()
, row.Cells["Lot No"].Value.ToString()
, row.Cells["Expiry"].Value.ToString()
, row.Cells["Location"].Value.ToString()
, "1"
);
var new_row = dialog.header_grid.Rows[index];
}
}
if (dialog.ShowDialog() != DialogResult.OK)
{
return;
}
// For missing, Subtract from location and add a for resolutions - SHORTAGE
foreach (DataGridViewRow row in dialog.header_grid.Rows)
{
if (row.Cells["what_happened"].Value.ToString() == "MISSING")
{
has_missing = true;
// Update Transaction Ledger
{
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
outsDT.Rows.Add(row.Cells["location"].Value, now, "OUT", "PICKLIST_DECLARE_INCOMPLETE", picklist_id);
sql += LedgerSupport.UpdateLocationLedger(outsDT);
}
// Update Location Products Ledger
{
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
outsDT.Rows.Add(row.Cells["location"].Value, row.Cells["product"].Value, int.Parse(row.Cells["qty"].Value.ToString()) * -1, row.Cells["uom"].Value, row.Cells["lot_no"].Value, row.Cells["expiry"].Value, int.Parse(row.Cells["qty"].Value.ToString()) * -1, int.Parse(row.Cells["qty"].Value.ToString()) * -1);
sql += LedgerSupport.UpdateLocationProductsLedger(outsDT);
}
// Update For Resolution
sql += DataSupport.GetInsert("ForResolutions", Utils.ToDict(
"trans_source", "PICKLIST_DECLARE_INCOMPLETE"
, "trans_id", picklist_id
, "detected_on", now
, "product", row.Cells["product"].Value
, "uom", row.Cells["uom"].Value
, "lot_no", row.Cells["lot_no"].Value
, "expiry", row.Cells["expiry"].Value
, "location", row.Cells["location"].Value.ToString()
, "variance_type", "SHORTAGE"
, "variance_qty", row.Cells["qty"].Value
, "status", "FOR RESOLUTION"
, "line", dialog.header_grid.Rows.IndexOf(row) + 1
));
}
}
// For bad stocks, Move it to bad stock storage and add a for resolutions - BAD STOCK
// Add a for resolutions - ORDERS
{
ChooseBadStockLocationWindow grid_dialog = new ChooseBadStockLocationWindow();
foreach (DataGridViewRow row in dialog.header_grid.Rows)
{
if (row.Cells["what_happened"].Value.ToString() == "BAD STOCKS")
{
has_bad_stocks = true;
int index = grid_dialog.header_grid.Rows.Add(
row.Cells["Product"].Value.ToString()
, row.Cells["Uom"].Value.ToString()
, row.Cells["lot_no"].Value.ToString()
, row.Cells["Expiry"].Value.ToString()
, row.Cells["Location"].Value.ToString()
, "1"
);
}
}
if (grid_dialog.header_grid.Rows.Count > 0)
{
if (grid_dialog.ShowDialog() != DialogResult.OK)
{
return;
}
foreach (DataGridViewRow row in grid_dialog.header_grid.Rows)
{
// Update the Printout
BadStockDT.Rows.Add(
row.Cells["location"].Value.ToString()
, row.Cells["product"].Value.ToString()
, row.Cells["uom"].Value.ToString()
, row.Cells["lot_no"].Value.ToString()
, row.Cells["expiry"].Value.ToString()
, row.Cells["qty"].Value.ToString()
, row.Cells["reason"].Value.ToString()
, row.Cells["bad_stock_location"].Value.ToString()
);
// Update Transaction Ledger
{
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
outsDT.Rows.Add(row.Cells["location"].Value, now, "OUT", "PICKLIST_DECLARE_INCOMPLETE", picklist_id);
sql += LedgerSupport.UpdateLocationLedger(outsDT);
DataTable insDT = LedgerSupport.GetLocationLedgerDT();
insDT.Rows.Add(row.Cells["bad_stock_location"].Value, now, "IN", "PICKLIST_DECLARE_INCOMPLETE", picklist_id);
sql += LedgerSupport.UpdateLocationLedger(insDT);
}
// Update Location Products Ledger
{
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
outsDT.Rows.Add(row.Cells["location"].Value, row.Cells["product"].Value, int.Parse(row.Cells["qty"].Value.ToString()) * -1, row.Cells["uom"].Value, row.Cells["lot_no"].Value, row.Cells["expiry"].Value, int.Parse(row.Cells["qty"].Value.ToString()) * -1, int.Parse(row.Cells["qty"].Value.ToString()) * -1);
sql += LedgerSupport.UpdateLocationProductsLedger(outsDT);
DataTable insDT = LedgerSupport.GetLocationProductsLedgerDT();
insDT.Rows.Add(row.Cells["bad_stock_location"].Value, row.Cells["product"].Value, int.Parse(row.Cells["qty"].Value.ToString()) * 1, row.Cells["uom"].Value, row.Cells["lot_no"].Value, row.Cells["expiry"].Value);
sql += LedgerSupport.UpdateLocationProductsLedger(insDT);
}
// Update For Resolution
sql += DataSupport.GetInsert("ForResolutions", Utils.ToDict(
"trans_source", "PICKLIST_DECLARE_INCOMPLETE"
, "trans_id", picklist_id
, "detected_on", now
, "product", row.Cells["product"].Value
, "uom", row.Cells["uom"].Value
, "lot_no", row.Cells["lot_no"].Value
, "expiry", row.Cells["expiry"].Value
, "location", row.Cells["location"].Value.ToString()
, "variance_type", "BAD STOCKS"
, "variance_qty", row.Cells["qty"].Value
, "status", "FOR RESOLUTION"
, "line", dialog.header_grid.Rows.IndexOf(row) + 1
));
}
}
}
{
OrderHoldingWindow order_dialog = new OrderHoldingWindow();
order_dialog.BadStockDT = BadStockDT;
order_dialog.parent = this;
foreach (DataGridViewRow row in picklist_details_grid.Rows)
{
order_dialog.products_grid.Rows.Add(
row.Cells["product"].Value.ToString()
, row.Cells["Uom"].Value.ToString()
, row.Cells["expiry"].Value.ToString()
, row.Cells["Lot No"].Value.ToString()
, row.Cells["Quantity"].Value.ToString()
, "0"
);
}
foreach (DataGridViewRow scanned_row in scanned_grid.Rows)
{
foreach (DataGridViewRow row in order_dialog.products_grid.Rows)
{
if (row.Cells["product"].Value.ToString() == scanned_row.Cells["product"].Value.ToString() &&
row.Cells["uom"].Value.ToString() == scanned_row.Cells["uom"].Value.ToString() &&
row.Cells["expiry"].Value.ToString() == scanned_row.Cells["expiry"].Value.ToString() &&
row.Cells["lot_no"].Value.ToString() == scanned_row.Cells["lot_no"].Value.ToString()
)
{
row.Cells["qty_ordered"].Value = int.Parse(row.Cells["qty_ordered"].Value.ToString()) + int.Parse(scanned_row.Cells["qty"].Value.ToString());
row.Cells["qty_picked"].Value = scanned_row.Cells["qty"].Value.ToString();
}
}
}
if (order_dialog.ShowDialog() != DialogResult.OK)
{
return;
}
// Update Order Status for Orders put on hold
DataTable order_compromisedDT = GetMergedOrders(order_dialog);
foreach (DataRow row in order_compromisedDT.Rows)
{
if (row["Status"].ToString() == "HOLD")
{
String reason = "";
if (has_bad_stocks && has_missing)
{
reason = "MISSING, BAD STOCK";
}
else if (has_missing)
{
reason = "MISSING";
}
else if (has_bad_stocks)
{
reason = "BAD STOCK";
}
sql += " UPDATE ReleaseOrders SET status='FOR RESOLUTION', holding_transaction='" + picklist_id + "', holding_datetime='" + now + "', holding_reason='" + reason + "' WHERE order_id= '" + row["Order ID"] + "' ";
}
}
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
this.Close();
}
private void DeclareComplete()
{
String sql = "";
String picklist_id = txtPicklist.Text;
DateTime now = DateTime.Now;
// Update Picklist
sql += "UPDATE Picklists SET status = 'DECLARED COMPLETE' WHERE picklist_id= '" + picklist_id + "'; ";
DataTable order_ids = FAQ.GetOrdersFromPicklist(picklist_id);
// Update Order Status
foreach (DataRow row in order_ids.Rows)
{
sql += " UPDATE ReleaseOrders SET status='FOR RELEASING' WHERE order_id= '" + row["order_id"] + "' ";
}
// Update Transaction Ledger
{
DataTable insDT = LedgerSupport.GetLocationLedgerDT();
insDT.Rows.Add("STAGING-OUT", now, "IN", "PICKLIST_DECLARE_COMPLETE", picklist_id);
sql += LedgerSupport.UpdateLocationLedger(insDT);
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
foreach (DataGridViewRow row in scanned_grid.Rows)
{
outsDT.Rows.Add(row.Cells["original_location"].Value, now, "OUT", "PICKLIST_DECLARE_COMPLETE", picklist_id);
}
sql += LedgerSupport.UpdateLocationLedger(outsDT);
}
// Update Location Products Ledger
{
DataTable insDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataGridViewRow row in scanned_grid.Rows)
{
insDT.Rows.Add("STAGING-OUT", row.Cells["product"].Value, row.Cells["qty"].Value, row.Cells["uom"].Value, row.Cells["lot_no"].Value, row.Cells["expiry"].Value);
}
sql += LedgerSupport.UpdateLocationProductsLedger(insDT);
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataGridViewRow row in scanned_grid.Rows)
{
outsDT.Rows.Add(row.Cells["original_location"].Value, row.Cells["product"].Value, int.Parse(row.Cells["qty"].Value.ToString()) * -1, row.Cells["uom"].Value, row.Cells["lot_no"].Value, row.Cells["expiry"].Value, int.Parse(row.Cells["qty"].Value.ToString()) * -1, int.Parse(row.Cells["qty"].Value.ToString()) * -1);
}
sql += LedgerSupport.UpdateLocationProductsLedger(outsDT);
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
this.Close();
}
private void SaveData()
{
String putaway_id = DataSupport.GetNextMenuCodeInt("PA");
DateTime now = DateTime.Now;
// Save Transaction
String sql = DataSupport.GetInsert("Putaways", Utils.ToDict(
"putaway_id", putaway_id
, "container", parent.cboContainer.SelectedValue.ToStringNull()
, "encoded_on", now
));
foreach (DataGridViewRow row in parent.headerGrid.Rows)
{
sql += DataSupport.GetInsert("PutawayDetails", Utils.ToDict(
"putaway", putaway_id
, "product", row.Cells["product"].Value.ToString()
, "expected_qty", row.Cells["Quantity"].Value.ToString()
, "uom", row.Cells["uom"].Value.ToString()
, "lot_no", row.Cells["lot"].Value.ToString()
, "expiry", row.Cells["expiry"].Value.ToString()
, "location", row.Cells["location"].Value.ToString()
));
}
// Update Transaction Ledger
{
// Out with the staging in
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
outsDT.Rows.Add("STAGING-IN", now, "OUT", "PUTAWAY", putaway_id);
sql += LedgerSupport.UpdateLocationLedger(outsDT);
// In with the container
DataTable insDT = LedgerSupport.GetLocationLedgerDT();
foreach (DataGridViewRow row in parent.headerGrid.Rows)
{
insDT.Rows.Add(parent.cboContainer.SelectedValue.ToStringNull(), now, "IN", "PUTAWAY", putaway_id);
}
sql += LedgerSupport.UpdateLocationLedger(insDT);
}
// Update Location Products Ledger
{
// Out with the staging in
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataGridViewRow row in parent.headerGrid.Rows)
{
outsDT.Rows.Add("STAGING-IN", row.Cells["product"].Value, int.Parse(row.Cells["Quantity"].Value.ToString()) * -1, row.Cells["uom"].Value, row.Cells["lot"].Value, row.Cells["expiry"].Value);
}
sql += LedgerSupport.UpdateLocationProductsLedger(outsDT);
// In with the container
DataTable insDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataGridViewRow row in parent.headerGrid.Rows)
{
insDT.Rows.Add(parent.cboContainer.SelectedValue.ToStringNull(), row.Cells["product"].Value, row.Cells["Quantity"].Value, row.Cells["uom"].Value, row.Cells["lot"].Value, row.Cells["expiry"].Value);
}
sql += LedgerSupport.UpdateLocationProductsLedger(insDT);
}
try
{
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
webBrowser1.DocumentText = webBrowser1.DocumentText.Replace("(issued on save)", putaway_id);
btnPrintPreview.Text = "Print";
btnCancel.Visible = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void txtScan_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
var product_row = BarcodeSupport.GetProductFromBarcode(txtScan.Text);
if (product_row == null)
{
MessageBox.Show("Barcode Not Recognized");
return;
}
String product = product_row["PRODUCT"].ToString();
String uom = product_row["MATCHED_UOM"].ToString();
DataTable dt = DataSupport.RunDataSet("SELECT order_id[Order], Product, Expiry, lot_no[Lot No], order_qty[Order Qty], Uom, Scanned_qty[Scanned Qty], scanned_on [Scanned On] FROM ReleaseDetailItems WHERE release = @release AND order_id = @order_id AND product=@product AND @uom = @uom", "release", release_id, "order_id", current_order, "product", product, "uom", uom).Tables[0];
SelectGridWindow dialog = new SelectGridWindow();
dialog.dataGridView1.DataSource = dt;
if (dialog.ShowDialog() != DialogResult.OK)
{
return;
}
var selected_row = dialog.dataGridView1.SelectedRows[0];
String expiry = selected_row.Cells["Expiry"].Value.ToString();
String lot_no = selected_row.Cells["Lot No"].Value.ToString();
String sql = "";
sql += " UPDATE ReleaseDetailItems SET Scanned_qty = Scanned_qty +1 WHERE release = '" + release_id + "' AND order_id = '" + current_order + "' AND product='" + product + "' AND uom = '" + uom + "' AND expiry = '" + expiry + "' AND lot_no='" + lot_no + "' ";
// Update Transaction Ledger
{
// Out with the location
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
outsDT.Rows.Add("STAGING-OUT", now, "OUT", "RELEASE", release_id);
sql += LedgerSupport.UpdateLocationLedger(outsDT);
// In with both Staging out and for resolution
DataTable insDT = LedgerSupport.GetLocationLedgerDT();
insDT.Rows.Add("RELEASED", now, "IN", "PICKLIST_DECLARE_COMPLETE", release_id);
sql += LedgerSupport.UpdateLocationLedger(insDT);
}
// Update Location Products Ledger
{
// Out with the staging out
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
outsDT.Rows.Add("STAGING-OUT", product, -1, uom, lot_no, expiry);
sql += LedgerSupport.UpdateLocationProductsLedger(outsDT);
DataTable insDT = LedgerSupport.GetLocationProductsLedgerDT();
insDT.Rows.Add("RELEASED", product, 1, uom, lot_no, expiry);
sql += LedgerSupport.UpdateLocationProductsLedger(insDT);
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
SynchroSupport.UpdateOrderStatus(release_id);
LoadData();
}
}
