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 btnStockCheck_Click(object sender, EventArgs e)
{
DataTable ordersDT = DataSupport.RunDataSet("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)
{
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");
}
}