[RESOLVED]Need help please- Update for FIFO inventory - next record problem

  
$db = new clsDBMassimo;  
	$product_id = $Stock->product_id->GetValue();  
	$AmOrdered = $Stock->stock_out_unit->GetValue();  
//check if the stock is enough  
	$sql = "SELECT SUM(stock_in_unit) as Total FROM stock_level WHERE product_id = $product_id";  
	$db->query($sql);  
	$result = $db->next_record();  
	if($result) ($Total = $db->f("Total"));  
//if stock not enough, then cancel insert and give error message, but i still have an error  
	if($Total < $AmOrdered){  
		$Stock->Errors->addError("Stock Not Enough");=>in this part also have problem.event   
		$db->close();    though I already add an error, but stock out still inserted on table.any suggestion?  
	}else{  //if the stock is enough then select from table stock_level  
		$sql = "SELECT * FROM stock_level WHERE product_id = $product_id ORDER BY in_date";  
		$db->query($sql);  
		$result= $db->next_record();  
		$IDnya = $db->f("stock_level_id");  
		$AmOnHand = $db->f("stock_in_unit");  
		  
		while($db->next_record()){  
//loop as long as amount ordered > 0  
			while ($AmOrdered > 0){  
				if($AmOrdered < $AmOnHand){  
					$Res = $AmOnHand - $AmOrdered;  
					$update = "UPDATE stock_level SET stock_in_unit = $Res where stock_level_id = $IDnya";  
					$db->query($update);  
					$AmOrdered = 0;  
				}else{  
					$AmOrdered = $AmOrdered - $AmOnHand;  
					$update = "UPDATE stock_level SET stock_in_unit = 0 where stock_level_id = $IDnya";  
					$db->query($update);  
//here should be move to the next record to get the next stock, but can not move to next record					  
				}  
			}  
		}  
		$db->close();  
	}    

  
  $product_id = $Stock->product_id->GetValue();  
  $AmOrdered  = $Stock->stock_out_unit->GetValue();  
  //check if the stock is enough  
  $InStock = CCDLookup('SUM(stock_in_unit)','stock_level','product_id='. $DBMassimo->ToSQL($product_id, ccsInteger),$DBMassimo);  
  //if stock not enough, then cancel insert and give error message, but i still have an error  
  if($InStock < $AmOrdered){  
    $Stock->InsertAllowed = False;   
    $Stock->Errors->addError("Stock Not Enough");  
    return;  
  }  

  
 $product_id = $Stock->product_id->GetValue();    
  $AmOrdered  = $Stock->stock_out_unit->GetValue();    
  //check if the stock is enough    
  $InStock = CCDLookup('SUM(stock_in_unit)','stock_level','product_id='. $DBMassimo->ToSQL($product_id, ccsInteger),$DBMassimo);    
  //if stock not enough, then cancel insert and give error message, but i still have an error    
  if($InStock < $AmOrdered){    
    $Stock->InsertAllowed = False;     
    $Stock->Errors->addError("Stock Not Enough");    
    return;    
  }    
// the RETURN above makes the BeforeInsert end here if error is posted.  
  
  $db = new clsDBMassimo;  // new connection  
  $dbU = new clsDBMassimo;  // new connection  
  $sql = "SELECT * FROM stock_level WHERE product_id =". $db->ToSQL($product_id, ccsInteger) ." ORDER BY in_date";  
  $db->query($sql);  
  while($db->next_record() ){  
    if($AmOrdered > 0){    // booked amounts are deducted when 0 we are finished.  
      $IDnya = $db->f("stock_level_id");  
      $AmOnHand = $db->f("stock_in_unit");  
      if($AmOrdered < $AmOnHand){  
        $Res = $AmOnHand - $AmOrdered;  
        $update = "UPDATE stock_level SET stock_in_unit =";  
        $update .= $dbU->ToSQL($Res, ccsInteger)           ;  
        $update .= " where stock_level_id ="              ;  
        $update .= $dbU->ToSQL($IDnya, ccsIteger)          ;  
        $dbU->query($update);  
        $AmOrdered = 0;  
      }else{ //$AmOrdered >= $AmOnHand  
        $update = "UPDATE stock_level SET stock_in_unit = 0";  
        $update .=" where stock_level_id ="                 ;  
        $update .=$dbU->ToSQL($IDnya, ccsInteger)            ;  
        $dbU->query($update);  
        $AmOrdered = $AmOrdered - $AmOnHand;  
      }  
    }  
  }  
  $db->close();  
  $dbU->close();