DROP PROCEDURE IF EXISTS `sp_FixDuplicateJournal`;

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_FixDuplicateJournal`(
	IN `CompanyID` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE FORM_NO VARCHAR(256);
	DECLARE TABLE_NAME VARCHAR(256);
	DECLARE REPLICATION_NUMBER INT DEFAULT 0;
	BLOCK_JOURNAL: BEGIN
		DECLARE DONE_GET_JOURNAL INT DEFAULT FALSE;
		DECLARE CURSOR_GET_JOURNAL CURSOR FOR
		SELECT
			comp_journal.formNo,
			view_journal_table.tableName,
			COUNT(*) c
		FROM
			comp_journal LEFT JOIN view_journal_table
			ON view_journal_table.formNo = comp_journal.formNo AND
			view_journal_table.companyID = comp_journal.companyID
		WHERE
			comp_journal.formNo IS NOT NULL AND
			comp_journal.companyID = `CompanyID` AND
			comp_journal.is_delete = 0
		GROUP BY
			comp_journal.formNo, comp_journal.companyID
		HAVING c > 1;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_GET_JOURNAL = TRUE;
		
		OPEN CURSOR_GET_JOURNAL;
		FETCH CURSOR_GET_JOURNAL INTO FORM_NO, TABLE_NAME, REPLICATION_NUMBER;
		comp_journal_loop: LOOP
			IF DONE_GET_JOURNAL THEN
				LEAVE comp_journal_loop;
			END IF;
			
			SET START_INDEX = 1;
			WHILE START_INDEX i <= REPLICATION_NUMBER DO
				SET @SQL := CONCAT('SELECT * FROM ', TABLE_NAME, ' WHERE ');
				  PREPARE stmt FROM @SQL;
				  EXECUTE stmt;
				  DEALLOCATE PREPARE stmt; 
				
				SET START_INDEX = START_INDEx + 1;
			END WHILE;
			
			FETCH CURSOR_GET_JOURNAL INTO JOURNAL_TO_FIX;
		END LOOP comp_journal_loop;
		CLOSE CURSOR_GET_JOURNAL;
	END BLOCK_JOURNAL;
	
	SELECT * FROM Reports;
END
//