DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_FixJournalYearPeriod`(
	IN `CompanyId` INT,
	IN `YearToFix` INT,
	IN `PeriodToFix` INT,
	IN `FixedYear` INT,
	IN `FixedPeriod` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Input parameters CompanyId, YearToFix, PeriodToFix, FixedYear, FixedPeriod'
BEGIN
	DECLARE Done BOOL DEFAULT FALSE;
	DECLARE JournalID VARCHAR(128);
	DECLARE TransactionID INT;
	DECLARE TableName VARCHAR(128);
	
	DECLARE JournalIDToFix CURSOR FOR SELECT comp_journal.journalID FROM comp_journal WHERE comp_journal.companyID = CompanyId AND comp_journal.year = YearToFix AND comp_journal.period = PeriodToFix;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = TRUE;
	
	DROP TEMPORARY TABLE IF EXISTS message;
	CREATE TEMPORARY TABLE message (
		JournalToFix VARCHAR(128),
		FixedJournal VARCHAR(128),
		TransactionTable VARCHAR(128)
	);
	
	OPEN JournalIDToFix;
	
	read_loop: LOOP
		FETCH JournalIDToFix INTO JournalID;
		IF Done THEN
			LEAVE read_loop;
		END IF;
		
		IF SUBSTRING(JournalID,1,2) <> 'JL' THEN
			SELECT
				view_journal_table.ID, view_journal_table.tableName
			INTO
				TransactionID, TableName
			FROM
				view_journal_table
			WHERE
				view_journal_table.journalID = JournalID AND
				view_journal_table.companyID = CompanyId;
			
			SET @UpdateQuery = CONCAT('UPDATE ', TableName, ' SET journalNo = "', CONCAT(SUBSTRING(JournalID,1,2), FixedYear, LPAD(FixedPeriod,2,'0'), SUBSTRING(JournalID,9,8)), '" WHERE ID = ', TransactionID);
			PREPARE statement FROM @UpdateQuery;
			EXECUTE statement;
			DEALLOCATE PREPARE statement;
			
			-- UPDATE
			-- 	TableName
			-- SET
			-- 	journalNo = CONCAT(SUBSTRING(JournalID,1,2), FixedYear, FixedPeriod, SUBSTRING(JournalID,9,8))
			-- WHERE
			-- 	ID = TransactionID;
		ELSE
			SET TableName = '-';
		END IF;
		
		INSERT INTO message VALUES(JournalID, CONCAT(SUBSTRING(JournalID,1,2), FixedYear, LPAD(FixedPeriod,2,'0'), SUBSTRING(JournalID,9,8)), TableName);
		
		UPDATE
			comp_journal_detail
		SET
			comp_journal_detail.year = FixedYear,
			comp_journal_detail.period = FixedPeriod,
			comp_journal_detail.journalID = CONCAT(SUBSTRING(JournalID,1,2), FixedYear, LPAD(FixedPeriod,2,'0'), SUBSTRING(JournalID,9,8))
		WHERE
			comp_journal_detail.journalID = JournalID AND
			comp_journal_detail.companyID = CompanyId;
		
		UPDATE
			comp_journal
		SET
			comp_journal.year = FixedYear,
			comp_journal.period = FixedPeriod,
			comp_journal.journalID = CONCAT(SUBSTRING(JournalID,1,2), FixedYear, LPAD(FixedPeriod,2,'0'), SUBSTRING(JournalID,9,8))
		WHERE
			comp_journal.journalID = JournalID AND
			comp_journal.companyID = CompanyId;
	END LOOP;
	
	UPDATE
		comp_financial_year
	SET
		comp_financial_year.financialYear = FixedYear,
		comp_financial_year.period = FixedPeriod
	WHERE
		comp_financial_year.financialYear = YearToFix AND
		comp_financial_year.period = PeriodToFix AND
		comp_financial_year.companyID = CompanyId;
	
	UPDATE
		comp_acc_det
	SET
		comp_acc_det.year = FixedYear,
		comp_acc_det.period = FixedPeriod
	WHERE
		comp_acc_det.year = YearToFix AND
		comp_acc_det.period = PeriodToFix AND
		comp_acc_det.companyID = CompanyId;
		
	UPDATE
		comp_jl_seq
	SET
		comp_jl_seq.year = FixedYear,
		comp_jl_seq.period = FixedPeriod
	WHERE
		comp_jl_seq.year = YearToFix AND
		comp_jl_seq.period = PeriodToFix AND
		comp_jl_seq.compID = CompanyId;
	
	CLOSE JournalIDToFix;
	SELECT * FROM message;
END//