DELIMITER //
CREATE PROCEDURE `updateLastMeterRead`(IN org_id INT, IN end_date DATE)
    BEGIN

        DECLARE ser_num VARCHAR(100);
        DECLARE blk_fy_vol INT;
        DECLARE col_fy_vol INT;
        DECLARE end_mtr_dt DATE DEFAULT NOW();


        -- A boolean variable to tell us when the cursor is out of data
        DECLARE done TINYINT DEFAULT FALSE;

        IF end_date != '' THEN end_mtr_dt = end_date; END IF;


DECLARE est_vols CURSOR FOR SELECT ms.serial_number,
	ROUND((cd.`End` - cd.`Begin`) / ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`))
          * ABS(DATEDIFF(cd.last_meter_import, end_mtr_dt))) + (cd.`End` - cd.`Begin`) blk_fy_vol,
	ROUND((cd.`MstrEnd` - cd.`MstrBegin`) / ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`))
		* ABS(DATEDIFF(cd.last_meter_import, end_mtr_dt))) + (cd.`MstrEnd` - cd.`MstrBegin`) col_fy_vol

	FROM machine_status ms
	INNER JOIN current_devices cd ON cd.SerialNumber = ms.serial_number
	WHERE cd.org_id_ma = org_id
	AND ms.service_needed = 1
	GROUP BY serial_number

	UNION

	SELECT SerialNumber,
	ROUND((cd.`End` - cd.`Begin`) / ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`))
          * ABS(DATEDIFF(cd.last_meter_import, end_mtr_dt))) + (cd.`End` - cd.`Begin`) blk_fy_vol,
	ROUND((cd.`MstrEnd` - cd.`MstrBegin`) / ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`))
          * ABS(DATEDIFF(cd.last_meter_import, end_mtr_dt))) + (cd.`MstrEnd` - cd.`MstrBegin`) col_fy_vol

	FROM current_devices cd
	LEFT JOIN machine_status ms ON ms.serial_number = cd.SerialNumber
	WHERE cd.org_id_ma = org_id
	AND ms.serial_number IS NULL

	UNION

	SELECT SerialNumber,
	ROUND((cd.`End` - cd.`Begin`) / ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`))
          * ABS(DATEDIFF(cd.last_meter_import, end_mtr_dt))) + (cd.`End` - cd.`Begin`) blk_fy_vol,
	ROUND((cd.`MstrEnd` - cd.`MstrBegin`) / ABS(DATEDIFF(cd.last_meter_import, cd.`BDate`))
          * ABS(DATEDIFF(cd.last_meter_import, end_mtr_dt))) + (cd.`MstrEnd` - cd.`MstrBegin`) col_fy_vol

	FROM current_devices cd
	WHERE cd.org_id_ma = org_id
	AND DATEDIFF(CURRENT_TIMESTAMP, cd.last_meter_import) > 30;


        
    -- a cursor that runs out of data throws an exception; we need to catch this.
    -- when the NOT FOUND condition fires, the var done, which defaults to FALSE, will be set to true.
    -- and since this is a CONTINUE handler, execution continues with the next statement.   

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- open the cursor
    OPEN est_vols;

    my_loop: -- loops must have a label; it's used to leave the loop
    LOOP
 		-- read the values from the next row that is available in the cursor

  		FETCH est_vols INTO ser_num, blk_fy_vol, col_fy_vol;

          IF done THEN -- this will be true when we are out of rows to read, so we go to the statement after END LOOP.
            LEAVE my_loop; 
          ELSE  
       
			UPDATE meter
            SET `End` = blk_fy_vol,
            	`MstrEnd` = col_fy_vol
			WHERE serial_number = ser_num; 

  		  END IF;
	END LOOP my_loop;

CLOSE est_vols;

END //
DELIMITER ;