public static void StockCheck()
{
DataSupport oms_dh = new DataSupport(String.Format(@"Initial Catalog={0};Data Source= {1};User Id = {2}; Password = {3}", Utils.DBConnection["OMS"]["DBNAME"], Utils.DBConnection["OMS"]["SERVER"], Utils.DBConnection["OMS"]["USERNAME"], Utils.DBConnection["OMS"]["PASSWORD"]));
DataTable ordersDT = DataSupport.RunDataSet(String.Format("SELECT * FROM ReleaseOrders WHERE status = 'FOR STOCK CHECKING'")).Tables[0];
foreach (DataRow row in ordersDT.Rows)
{
var order_id = row["order_id"].ToString();
var result = FAQ.DoesOrderHaveStocks(order_id);
if (result == false)
{
oms_dh.ExecuteNonQuery("UPDATE OutgoingShipmentRequests SET status = 'INSUFFICIENT STOCKS' WHERE out_shipment_id = '" + row["oms_shipment_id"].ToString() + "';", IsolationLevel.ReadCommitted);
MessageBox.Show("Can't Reserve order " + order_id);
continue;
}
String sql = " UPDATE ReleaseOrders SET status = 'FOR SCHEDULING' WHERE order_id = '" + order_id + "'; ";
DataTable detailsDT = FAQ.GetOrderDetails(order_id);
foreach (DataRow detail in detailsDT.Rows)
{
var dt = FAQ.WhereAreProductsInWarehouse(detail["product"].ToString(), detail["uom"].ToString());
int qty_to_be_reserved = int.Parse(detail["qty"].ToString());
foreach (DataRow selected_row in dt.Rows)
{
int qty_in_location = int.Parse(selected_row["available_qty"].ToString());
int qty_reserved = qty_in_location;
if (qty_to_be_reserved < qty_in_location)
{
qty_reserved = qty_to_be_reserved;
}
sql += " UPDATE LocationProductsLedger SET reserved_qty = reserved_qty + " + qty_reserved +
" WHERE product = '" + detail["product"].ToString() + "' AND uom = '" + detail["uom"].ToString() + "' " +
" AND lot_no = '" + selected_row["lot_no"].ToString() + "' AND expiry = '" + selected_row["expiry"].ToString() + "' " +
" AND location='" + selected_row["location"].ToString() + "'; ";
qty_to_be_reserved -= qty_reserved;
if (qty_to_be_reserved <= 0)
{
break;
}
}
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
//MessageBox.Show("It works");
oms_dh.ExecuteNonQuery("UPDATE OutgoingShipmentRequests SET status = 'FOR SCHEDULING' WHERE out_shipment_id = '" + row["oms_shipment_id"].ToString() + "';", IsolationLevel.ReadCommitted);
}
}
private void SaveData()
{
String id = DataSupport.GetNextMenuCodeInt("RC");
// Save Transaction
String sql = DataSupport.GetInsert("Receipts", Utils.ToDict(
"receipt_id", id
, "received_from", parent.txtReceivedFrom.Text
, "received_on", parent.txtReceivedOn.Value.ToShortDateString()
, "received_by", parent.cboReceivedBy.Text
, "encoded_on", DateTime.Now
));
foreach (DataGridViewRow row in parent.headerGrid.Rows)
{
sql += DataSupport.GetInsert("ReceiptDetails", Utils.ToDict(
"receipt", id
, "line", parent.headerGrid.Rows.IndexOf(row) + 1
, "product", row.Cells["product_id"].Value.ToString()
, "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()
, "remarks", row.Cells["remarks"].Value.ToString()
));
}
// Update Location Ledger
sql += DataSupport.GetInsert("LocationLedger", Utils.ToDict(
"location", "STAGING-IN"
, "transaction_datetime", parent.txtReceivedOn.Value.ToShortDateString()
, "transaction_type", "IN"
, "transaction_name", "RECEIPT"
, "transaction_id", id
));
// Update Location Products Ledger
foreach (DataGridViewRow row in parent.headerGrid.Rows)
{
if (FAQ.IsNewLine("STAGING-IN", row.Cells["product_id"].Value.ToString(), row.Cells["uom"].Value.ToString(), row.Cells["lot"].Value.ToString(), row.Cells["expiry"].Value.ToString()))
{
sql += "UPDATE LocationProductsLedger SET qty = qty + " + row.Cells["Quantity"].Value.ToString() + " WHERE location = '" + "STAGING-IN" + "' AND product='" + row.Cells["product_id"].Value.ToString() + "' AND uom ='" + row.Cells["uom"].Value.ToString() + "' AND lot_no = '" + row.Cells["lot"].Value.ToString() + "' AND expiry='" + row.Cells["expiry"].Value.ToString() + "'";
}
else
{
sql += DataSupport.GetInsert("LocationProductsLedger", Utils.ToDict(
"location", "STAGING-IN"
, "product", row.Cells["product_id"].Value.ToString()
, "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()
));
}
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
if (parent.oms_shipment_id != "")
{
DataSupport oms_dh = new DataSupport(String.Format(@"Initial Catalog={0};Data Source= {1};User Id = {2}; Password = {3}", Utils.DBConnection["OMS"]["DBNAME"], Utils.DBConnection["OMS"]["SERVER"], Utils.DBConnection["OMS"]["USERNAME"], Utils.DBConnection["OMS"]["PASSWORD"]));
StringBuilder oms_sql = new StringBuilder("UPDATE IncomingShipmentRequests SET status = 'RECEIVED', received_on = '" + DateTime.Now + "' WHERE shipment_id = '" + parent.oms_shipment_id + "'; ");
foreach (DataGridViewRow row in parent.headerGrid.Rows)
{
oms_sql.Append("UPDATE IncomingShipmentRequestDetails SET received_qty = '" + row.Cells["Quantity"].Value.ToString() + "' WHERE shipment = '" + parent.oms_shipment_id + "' AND product = '" + row.Cells["product_id"].Value.ToString() + "' AND uom = '" + row.Cells["uom"].Value.ToString() + "' AND lot_no='" + row.Cells["lot"].Value.ToString() + "' AND expiry='" + row.Cells["expiry"].Value.ToString() + "';");
}
oms_dh.ExecuteNonQuery(oms_sql.ToString());
}
MessageBox.Show("Success");
webBrowser1.DocumentText = webBrowser1.DocumentText.Replace("(issued on save)", id);
btnPrintPreview.Text = "Print";
btnCancel.Visible = false;
}
private void LoadData()
{
ordersDT = DataSupport.RunDataSet(@"SELECT RD.ORDER_ID[Order], RD.drop_sequence[Drop], RD.Status, RO.status [Order Status], CASE WHEN RO.holding_transaction IS NULL THEN 'YES' ELSE 'NO' END [Forced]
FROM ReleaseDetails RD
LEFT OUTER JOIN ReleaseOrders RO ON RO.order_id = RD.order_id
WHERE RD.release = @release_id
AND RO.status = 'FOR RELEASING'
ORDER BY RD.drop_sequence", "release_id", release_id).Tables[0];
current_order = GetCurrentOrder();
if (current_order == "FINISHED")
{
var ordersDT = FAQ.GetReleasedOrders(release_id);
// Update OMS
{
DataSupport oms_dh = new DataSupport(String.Format(@"Initial Catalog={0};Data Source= {1};User Id = {2}; Password = {3}", Utils.DBConnection["OMS"]["DBNAME"], Utils.DBConnection["OMS"]["SERVER"], Utils.DBConnection["OMS"]["USERNAME"], Utils.DBConnection["OMS"]["PASSWORD"]));
String sql = "";
foreach (DataRow order_row in ordersDT.Rows)
{
String order_id = order_row["order_id"].ToString();
sql += " UPDATE OutgoingShipmentRequests SET status='FOR DELIVERY' WHERE out_shipment_id='" + order_id + "'; ";
}
oms_dh.ExecuteNonQuery(sql, IsolationLevel.ReadCommitted);
}
// Update TMS
{
DataSupport tms_dh = new DataSupport(String.Format(@"Initial Catalog={0};Data Source= {1};User Id = {2}; Password = {3}", Utils.DBConnection["TMS"]["DBNAME"], Utils.DBConnection["TMS"]["SERVER"], Utils.DBConnection["TMS"]["USERNAME"], Utils.DBConnection["TMS"]["PASSWORD"]));
String sql = "";
foreach (DataRow order_row in ordersDT.Rows)
{
String order_id = order_row["order_id"].ToString();
sql += " UPDATE TripOrders SET status='FOR DELIVERY' WHERE order_id='" + order_id + "'; ";
}
tms_dh.ExecuteNonQuery(sql, IsolationLevel.ReadCommitted);
}
// Update WMS
{
String sql = "";
foreach (DataRow order_row in ordersDT.Rows)
{
String order_id = order_row["order_id"].ToString();
sql += " UPDATE ReleaseOrders SET status='RELEASED' WHERE order_id='" + order_id + "'; ";
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
}
PrintReleaseDocument dialog = new PrintReleaseDocument();
dialog.release_id = release_id;
dialog.ShowDialog();
DialogResult = DialogResult.OK;
return;
}
itemsDT = 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", "release", release_id, "order_id", current_order).Tables[0];
orders_grid.DataSource = ordersDT;
items_grid.DataSource = itemsDT;
}