getNumberOfPeople

DELIMITER $$

DROP FUNCTION IF EXISTS `getNumberOfPeople`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `getNumberOfPeople`(_cur_acc VARCHAR(20),_date DATE) RETURNS INT(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN

DECLARE _runstats INTEGER DEFAULT 1; 

DECLARE _occupant_no FLOAT DEFAULT 0.0;
DECLARE _num_people FLOAT DEFAULT 0.0;
DECLARE _bedroom_no FLOAT DEFAULT 0.0;
DECLARE _no_people FLOAT DEFAULT 2;
DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;


SELECT ro.occupant_no
INTO   _occupant_no
FROM   residence r
       JOIN residence_occupant ro
         ON ro.residence_id = r.id
WHERE  r.utility_acct_no = _cur_acc
       AND _date BETWEEN period_start AND period_end
LIMIT  1;

IF _occupant_no > 0 THEN
    IF _runstats >0 THEN
		REPLACE INTO residence_occupant_stat (account_no,occupant_number,method) VALUES (_cur_acc,_occupant_no,1);
	END IF;
	RETURN _occupant_no;
END IF;

SELECT occupant_no,
       num_people,
       bedroom_no
INTO   _occupant_no, _num_people, _bedroom_no
FROM   residence
WHERE  utility_acct_no = _cur_acc
LIMIT  1;

IF _occupant_no > 0 THEN
	IF _runstats >0 THEN
		REPLACE INTO residence_occupant_stat (account_no,occupant_number,method) VALUES (_cur_acc,_occupant_no,2);
	END IF;
	RETURN _occupant_no;
END IF;

IF _num_people > 0 THEN
	IF _runstats >0 THEN
		REPLACE INTO residence_occupant_stat (account_no,occupant_number,method) VALUES (_cur_acc,_num_people,3);
	END IF;
	RETURN _num_people;
END IF;

IF _bedroom_no > 0 THEN
	IF _runstats >0 THEN
		REPLACE INTO residence_occupant_stat (account_no,occupant_number,method) VALUES (_cur_acc,_bedroom_no,4);
	END IF;
	RETURN _bedroom_no;
END IF;

IF _runstats >0 THEN
		REPLACE INTO residence_occupant_stat (account_no,occupant_number,method) VALUES (_cur_acc,_no_people,5);
END IF;
RETURN _no_people;
END$$

DELIMITER ;