fx

DROP FUNCTION IF EXISTS GetMovingToAddress;
DELIMITER //
CREATE FUNCTION GetMovingToAddress(request_id INT) RETURNS INT DETERMINISTIC
BEGIN
  DECLARE address_id INT;
  DECLARE moveRequestId INT;    
  DECLARE carMoveRequestId INT;
  DECLARE storageRequestId INT;
  SELECT move_request_id INTO moveRequestId FROM request WHERE id = request_id;
  SELECT car_move_request_id INTO carMoveRequestId FROM request WHERE id = request_id;
  SELECT storage_request_id INTO storageRequestId FROM request WHERE id = request_id;  
  CASE  
  WHEN moveRequestId IS NOT NULL 
  THEN
    SELECT move_request.moving_to_address INTO address_id FROM move_request WHERE id = moveRequestId;
  WHEN carMoveRequestId IS NOT NULL
  THEN
    SELECT car_move_request.move_to_address INTO address_id FROM car_move_request WHERE id = carMoveRequestId;
  WHEN storageRequestId IS NOT NULL
  THEN
    SELECT storage_request.storage_location_id INTO address_id FROM storage_request WHERE id = storageRequestId;
  END CASE;   
  RETURN address_id; 
END //
DELIMITER ;


DROP FUNCTION IF EXISTS GetMovingFromAddress;
DELIMITER //
CREATE FUNCTION GetMovingFromAddress(request_id INT) RETURNS INT DETERMINISTIC
BEGIN
  DECLARE address_id INT;
  DECLARE moveRequestId INT;    
  DECLARE carMoveRequestId INT;
  DECLARE storageRequestId INT;
  SELECT move_request_id INTO moveRequestId FROM request WHERE id = request_id;
  SELECT car_move_request_id INTO carMoveRequestId FROM request WHERE id = request_id;
  SELECT storage_request_id INTO storageRequestId FROM request WHERE id = request_id;  
  CASE  
  WHEN moveRequestId IS NOT NULL 
  THEN
    SELECT move_request.moving_from_address INTO address_id FROM move_request WHERE id = moveRequestId;
  WHEN carMoveRequestId IS NOT NULL
  THEN
    SELECT car_move_request.move_from_address INTO address_id FROM car_move_request WHERE id = carMoveRequestId;
  WHEN storageRequestId IS NOT NULL
  THEN
    SET address_id = NULL;
  END CASE;   
  RETURN address_id; 
END //
DELIMITER ;