CREATE DEFINER=`spcsta5`@`cpe-67-255-194-115.maine.res.rr.com` PROCEDURE `getLastMeterRead`(IN v_orgid int)
BEGIN

DECLARE recCount int;
DECLARE numRow int DEFAULT 0;
DECLARE v_blkMeter INT;
DECLARE v_clrMeter INT;
DECLARE v_dte datetime;
DECLARE v_serialNum varchar(50);

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

DECLARE cursor1 -- cursor1 is an arbitrary label, an identifier for the cursor
 CURSOR FOR
		SELECT
		MAX(m.black_meter) AS `black_end_meter`,
		MAX(m.color_meter) AS `color_end_meter`,
		MAX(m.date_timestamp) AS `last_date_time`,
		cd.SerialNumber
		FROM current_devices cd INNER JOIN meter m ON cd.SerialNumber = m.serial_number
		WHERE cd.org_id_ma = v_orgid
        AND m.date_timestamp<=cd.EDate
		GROUP BY cd.SerialNumber;
        
-- a cursor that runs out of data throws an exception; we need to catch this.
-- when the NOT FOUND condition fires, "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 cursor1;

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

  FETCH NEXT FROM cursor1 INTO v_blkMeter, v_clrMeter, v_dte, v_serialNum;

  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 -- val1 and val2 will be the next values from c1 and c2 in table t1, 
       -- so now we call the procedure with them for this "row"
       UPDATE current_devices cd
				SET cd.`End` = v_blkMeter,
						cd.MstrEnd = v_clrMeter,
            cd.last_meter_import = v_dte
            
		WHERE cd.org_id_ma = v_orgid AND cd.SerialNumber = v_serialNum; 

  END IF;
END LOOP;

-- execution continues here when LEAVE my_loop is encountered;
-- you might have more things you want to do here
-- Add the user and timestamp to the Import End Meter page.


CLOSE cursor1;

END