DROP PROCEDURE IF EXISTS initial_compare;
DELIMITER //
CREATE PROCEDURE initial_compare () SQL SECURITY INVOKER
BEGIN
DECLARE sp_recid INT DEFAULT 0;
DECLARE sp_CustomerRows INT DEFAULT 0;
DECLARE spC_dbid INT DEFAULT 0;
DECLARE spC_tableid INT DEFAULT 0;
DECLARE spC_rowid INT DEFAULT 0;
DECLARE sp_ani CHAR(25) DEFAULT '';
DECLARE sp_dialedno CHAR(25) DEFAULT '';
DECLARE sp_duration INT DEFAULT '0';
DECLARE sp_callstart DATETIME DEFAULT '0000-00-00 00:00:00';
DECLARE spR_dbid INT DEFAULT 0;
DECLARE spR_tableid INT DEFAULT 0;
DECLARE spR_rowid INT DEFAULT 0;

SELECT count(*) INTO sp_CustomerRows FROM customer;

WHILE sp_recid < sp_CustomerRows DO
        # We have to Prepare/Execute/Deallocate to be able to use LIMIT
        SET @_sp_recid = sp_recid;
        PREPARE stmt FROM "SELECT cdr_dbid,cdr_tableid,cdr_rowid,ani,dialedno,duration,callstart
          INTO spC_dbid,spC_tableid,spC_rowid,sp_ani,sp_dialedno,sp_duration,sp_callstart
          FROM customer WHERE matched='N' LIMIT ?,1";
        EXECUTE stmt USING @_sp_recid;
        DEALLOCATE PREPARE stmt;

        SELECT cdr_dbid,cdr_tableid,cdr_rowid INTO spR_dbid,spR_tableid,spR_rowid
                FROM carrier
		WHERE dialedno='sp_dialedno' AND ani='sp_ani' AND matched='N'
                AND ABS(sp_duration - duration) < 10
                AND TIMEDIFF(sp_callstart, callstart) BETWEEN TIME('-00:10:00') AND TIME('4:10:00')
                ORDER BY ABS(sp_duration - duration),ABS(TIME_TO_SEC(TIMEDIFF('sp_callstart',callstart)))
                LIMIT 1;

        IF spR_dbid != 0 THEN
                UPDATE carrier SET matched='Y' WHERE cdr_dbid=spR_dbid AND cdr_tableid=spR_tableid
                        AND cdr_rowid=spR_rowid;
        END IF;

        SET sp_recid = sp_recid + 1;
END WHILE;

END
//
DELIMITER ;