DELIMITER // CREATE OR REPLACE PROCEDURE compareMeterReads (IN audit_date datetime) BEGIN /*This proc can be executed as a standalone proc, or can be called by compareAllMeterReads. It will get the very next meter read, following the audit_date, for each machine (serial_number) The proc will gather data that will be inserted into the meter_read_comparisons table. The data will include: average usage per day for the fiscal year up to the first meter read, average usage per day between the 2 dates being compared, total black copies between the 2 dates being compared, total color copies between the 2 dates being compared, a flag to signal whether or not the data indicates that further audit (evaluation) is needeed. The criteria that determines whether or not the data indicates a need for further evaluation, can be modified. Criteria condition is on lines 188-192 (approx)*/ /*Declare the variables*/ DECLARE first_date_val DATETIME; DECLARE ser_num_val VARCHAR(25); DECLARE last_date_val DATETIME; DECLARE first_black_meter INT; DECLARE first_color_meter INT; DECLARE last_black_meter INT; DECLARE last_color_meter INT; /*Days between the 2 dates being compared*/ DECLARE total_days_between_dates INT; DECLARE black_diff INT; DECLARE color_diff INT; DECLARE black_avg_to_date INT; DECLARE color_avg_to_date INT; /*When this = 1, the fetch will be done*/ DECLARE exit_results_fetch INT DEFAULT 0; /*Declare the cursor that will get the earliest created_date and serial_number for each serial_number.*/ DECLARE results_cursor CURSOR FOR SELECT MIN(created_date) first_date, serial_number FROM `meters` WHERE created_date > audit_date AND serial_number != '' GROUP BY serial_number; /*Declare the cursor that will get the earliest created_date, that follows the first_date from the results_cursor, for each serial_number.*/ DECLARE last_results_cursor CURSOR FOR SELECT MIN(created_date) last_date FROM `meters` WHERE created_date > first_date_val AND serial_number = ser_num_val GROUP BY serial_number; /*Declare NOT FOUND handler*/ DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_results_fetch = 1; /*Clear the data from the previous execution from the table. This is only good if executed ONLY 1 day at a time. TRUNCATE meter_read_comparisons;*/ /*Open the first cursor*/ OPEN results_cursor; getData: LOOP FETCH results_cursor INTO first_date_val, ser_num_val; /*Clear the last_date_val. If a machine has no NEXT read date to follow the first_date_val, THEN the last_date_val would otherwise carry over from the last machine that DID have a Next read date and it could cause erroneous volume amounts.*/ SET last_date_val = NULL; /*Open the cursor that will return a last_date value for the current serial_number.*/ OPEN last_results_cursor; /*Get the results and assign the value to last_date_val*/ FETCH last_results_cursor INTO last_date_val; /*Close the cursor*/ CLOSE last_results_cursor; BEGIN DECLARE black_meter_val INT; DECLARE color_meter_val INT; DECLARE meter_read_order VARCHAR(10); DECLARE black_avg_between_dates INT; DECLARE color_avg_between_dates INT; DECLARE needs_audit VARCHAR(1); DECLARE dupe_serial_number VARCHAR(25); /*When this = 1, the fetch will be done*/ DECLARE exit_volumes_fetch INT DEFAULT 0; DECLARE volumes_cursor CURSOR FOR SELECT black_meter, color_meter, 'first_read' read_order FROM `meters` WHERE created_date = first_date_val AND serial_number = ser_num_val UNION SELECT black_meter, color_meter, 'last_read' read_order FROM `meters` WHERE created_date = last_date_val AND serial_number = ser_num_val; /*Declare NOT FOUND handler*/ DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_volumes_fetch = 1; /*Open the cursor that will return the black and color meter values for first and last meter reads for the current serial_number*/ OPEN volumes_cursor; getVolumes: LOOP /*Get the results and assign the values to black_meter_val, color_meter_val, and meter_read_order*/ FETCH volumes_cursor INTO black_meter_val, color_meter_val, meter_read_order; IF (meter_read_order = "first_read") THEN SET first_black_meter = black_meter_val; SET first_color_meter = color_meter_val; ELSEIF (meter_read_order = "last_read") THEN SET last_black_meter = black_meter_val; SET last_color_meter = color_meter_val; ELSE SET exit_results_fetch = 1; END IF; IF exit_volumes_fetch = 1 THEN LEAVE getVolumes; END IF; END LOOP getVolumes; /*Close the cursor*/ CLOSE volumes_cursor; /*The amount of copies printed between the 2 dates being compared.*/ SET black_diff = (last_black_meter - first_black_meter); SET color_diff = (last_color_meter - first_color_meter); /*Get total amount of days between the dates being compared. This will be divided into the total copies for the same time period, to get the total average copies printed per day.*/ SELECT DATEDIFF(last_date_val, first_date_val) INTO total_days_between_dates; /*The average copies per day between the 2 dates being compared.*/ SET black_avg_between_dates = (black_diff/total_days_between_dates); SET color_avg_between_dates = (color_diff/total_days_between_dates); /*The average copies per day between the first meter read of the fiscal year for this serial number and the first date of the 2 being compared.*/ SELECT getAvgToDate(first_date_val, 'black', ser_num_val) INTO black_avg_to_date; SELECT getAvgToDate(first_date_val, 'color', ser_num_val) INTO color_avg_to_date; /*IF ((black_avg_between_dates > (black_avg_to_date * 1.5)) || (color_avg_between_dates > (color_avg_to_date * 1.5)) || ((black_avg_between_dates * 1.5) < black_avg_to_date) || ((color_avg_between_dates * 1.5) < color_avg_to_date)) THEN SET needs_audit = "Y"; END IF;*/ /*Criteria to determine whether or not further evaluation is needed.*/ IF ((black_avg_between_dates > (black_avg_to_date + 1000)) || (color_avg_between_dates > (color_avg_to_date + 1000)) || ((black_avg_between_dates + 1000) < black_avg_to_date) || ((color_avg_between_dates + 1000) < color_avg_to_date)) THEN SET needs_audit = "Y"; END IF; /*Check to see if there is already a record in the table for this serial number with this date. If one exists, then do not insert another one. If there are a few days between reads, then the next date will be inserted for each of the missing dates, which is duplicating the data. For instance, if a read is 12-21-2018 and the next read for that machine is 12-26-2018, the date of 12-26-2018 will be inserted for 12-22, 12-23, 12-24, 12-25, snd 12-26. To be able to have this called for a date range, this cannot be helped because it would mean running through the table an extra time in the compareAllMeterReads procedure that calls this one. This could be rewritten to move the dynamic date functionality to the calling proc, but no time to do that now.*/ SELECT serial_number INTO dupe_serial_number FROM meter_read_comparisons WHERE serial_number = ser_num_val AND first_date = first_date_val; /*Insert all values, along with the serial_number and corresponding date_timestamps, into the table*/ SET @aSql = 'INSERT INTO meter_read_comparisons (serial_number, first_date, last_date, tot_days_between_dates, read_audit_needed, blk_daily_avg_to_date, black_average_between_dates, col_daily_avg_to_date, color_average_between_dates, total_black_copies, total_color_copies ) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'; PREPARE stmt FROM @aSql; /*Check to see if there is a read beyond the first date. If there is no last_date_val, then there is no volume data to insert.*/ if(last_date_val IS NOT NULL && dupe_serial_number IS NULL) THEN EXECUTE stmt USING ser_num_val, first_date_val, last_date_val, total_days_between_dates, needs_audit, black_avg_to_date, black_avg_between_dates, color_avg_to_date, color_avg_between_dates, black_diff, color_diff; END IF; DEALLOCATE PREPARE stmt; END; IF exit_results_fetch = 1 THEN LEAVE getData; END IF; END LOOP getData; /*Close the cursor*/ CLOSE results_cursor; END // DELIMITER ;