Redmine

use bitnami_redmine;

DROP PROCEDURE IF EXISTS copyCategories;

DROP PROCEDURE IF EXISTS copyCategoriesForAll;

DELIMITER $$

create procedure copyCategories(in sourceProject int, in targetProject int)

/**

This script copy all issue-categories from "sourceProject" to "targetProject".

*/

begin

   insert into issue_categories (name, project_id, assigned_to_id, reminder_notification )

   SELECT name, targetProject, assigned_to_id, reminder_notification from issue_categories where project_id = sourceProject;

end$$

DELIMITER ;

DELIMITER $$

create procedure copyCategoriesForAll(in sourceProject int)

/**

This script copy all issue-categories from "sourceProject" to others projects.

*/

begin

	DECLARE finished INTEGER DEFAULT 0;

	DECLARE id_ INT;

	declare cnt int;

	declare zero int default 0;

	DECLARE projects_cursor CURSOR FOR SELECT id FROM projects;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

	open projects_cursor;

	projects_cursor: LOOP

		FETCH projects_cursor INTO id_;

		IF finished = 1 THEN 

			LEAVE projects_cursor;

		END IF;

		select count(id) 

		into cnt

		from issue_categories 

		where project_id = id_ 

			and name in (select name from issue_categories where project_id = sourceProject);

		if (cnt = 0) then	

			insert into issue_categories (name, project_id, assigned_to_id, reminder_notification)

			SELECT	name, id_, assigned_to_id, reminder_notification /* , 		project_id, id_, project_id = id_*/

			from	issue_categories

			where	project_id != id_ and project_id = sourceProject;

		end if;

	END LOOP projects_cursor;

	CLOSE projects_cursor;

end$$

DELIMITER ;

/* 

THERE TEST'S

call copyCategoriesForAll(1);

call copyCategories(1,2); 

*/