gistfile1.sql

DROP PROCEDURE IF EXISTS `updateGPCD`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `updateGPCD`(_ACCOUNT VARCHAR(50))
BEGIN


DECLARE done INT DEFAULT 0;
DECLARE _has_breakdown INT DEFAULT 0;
DECLARE _sel_acc VARCHAR(20) DEFAULT '';
DECLARE _readdate DATE DEFAULT '1970-01-01';
DECLARE _prev_readdate DATE; 
DECLARE _cur_acc VARCHAR(20);
DECLARE _total_gallons FLOAT DEFAULT 0.0;
DECLARE _gpcd FLOAT;
DECLARE _cur_people FLOAT;
DECLARE _cur_gpcd FLOAT;
DECLARE _days_elapsed INTEGER; 
DECLARE cur1 CURSOR FOR SELECT `account`,`readdate`,`total_gallons`,`gpcd` FROM `usage_bill` WHERE `status`='Active' ORDER BY `account` ASC, `readdate` ASC, `current_read` ASC;
DECLARE cur2 CURSOR FOR SELECT `account`,`readdate`,`total_gallons`,`gpcd` FROM `usage_bill` WHERE `account`=_ACCOUNT AND `status`='Active' ORDER BY `account` ASC, `readdate` ASC, `current_read` ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
START TRANSACTION;
IF _ACCOUNT > 0 THEN
    OPEN cur2;
ELSE
    OPEN cur1;
END IF;
read_loop: LOOP
IF _ACCOUNT > 0 THEN
    FETCH cur2 INTO _cur_acc,_readdate,_total_gallons,_gpcd;
ELSE
    FETCH cur1 INTO _cur_acc,_readdate,_total_gallons,_gpcd;
END IF;

	IF done THEN	
      LEAVE read_loop;
    END IF;
	
	IF _cur_acc<>_sel_acc THEN
		
		
		SET _sel_acc = _cur_acc;
		SET _prev_readdate = _readdate;
		SET _cur_people = getNumberOfPeople(_sel_acc,_readdate);
		SET _cur_gpcd = ROUND(_total_gallons / 60 / _cur_people); 
		
		
		
		SELECT COUNT(*) INTO _has_breakdown
		FROM   residence_occupant ro 
			   JOIN residence r 
				 ON ro.residence_id = r.id 
		WHERE  r.utility_acct_no = _ACCOUNT; 
                
	ELSE
		
		SET _days_elapsed = TO_DAYS(_readdate)-TO_DAYS(_prev_readdate);
		IF _has_breakdown>0 THEN
			SET _cur_people = getNumberOfPeople(_sel_acc,_readdate);
		END IF;
		SET _cur_gpcd = ROUND(_total_gallons / _days_elapsed / _cur_people); 
		
		
		SET _prev_readdate=_readdate;
	END IF;
	IF _gpcd<>_cur_gpcd OR _gpcd IS NULL THEN
		UPDATE `usage_bill` SET `gpcd`=_cur_gpcd WHERE `account`=_cur_acc AND `readdate`=_readdate;
	END IF;
END LOOP;
IF _ACCOUNT > 0 THEN
    CLOSE cur2;
ELSE
    CLOSE cur1;
END IF;
COMMIT;
END$$

DELIMITER ;