CREATE PROCEDURE   `um_star_update_all`(in starid int,in sys char(3),in cli char(3))
BEGIN
-- ---------------------------------------
-- recalculate all roles and make fill history tables 
-- starid id from um_starfile
-- sys system to update
-- cli client to update
-- ---------------------------------------

-- create a temp table to hold the values from um_role_auth
	CREATE TEMPORARY TABLE link( rid INT , role int, step int, rname varchar(45));

-- all values are convertet to range and compared
	    
-- find all object where high is not set
	INSERT INTO link (rid,role,step) (
		SELECT um_role_auth.id,um_role_auth.role_id,um_starfile_data.step 
	    FROM um_role_auth 
	    INNER JOIN um_starfile_data ON 
	    	um_role_auth.object = um_starfile_data.object AND 
	    	um_role_auth.field=um_starfile_data.field AND 
	    	(REPLACE(um_role_auth.low, '*', '')<=REPLACE(um_starfile_data.value, '*', 'z'))  AND 
	    	(REPLACE(um_role_auth.low, '*', 'z')>=REPLACE(um_starfile_data.value, '*', '')) AND 
	    	um_role_auth.high ='' AND 
	    	um_role_auth.um_delete = 0
		WHERE um_starfile_data.um_star_id =starid
	);
	        
-- find all object where high is also set
	INSERT INTO link (rid,role,step) (
		SELECT um_role_auth.id,um_role_auth.role_id,um_starfile_data.step 
	    FROM um_role_auth 
	    INNER JOIN um_starfile_data ON 
	    	um_role_auth.object = um_starfile_data.object AND 
	    	um_role_auth.field=um_starfile_data.field AND 
	    	(REPLACE(um_role_auth.low, '*', '')<=REPLACE(um_starfile_data.value, '*', 'z'))  AND 
	    	(REPLACE(um_role_auth.high, '*', 'z')>=REPLACE(um_starfile_data.value, '*', '')) AND 
	    	um_role_auth.high >''  AND 
	    	um_role_auth.um_delete = 0
		WHERE um_starfile_data.um_star_id = starid
	);
	    
-- clear the old roles
	UPDATE um_role set um_role.star_category_step = 0 
	WHERE `system` = sys AND client = cli;
	       
-- insert the new
	UPDATE um_role 
		INNER JOIN (SELECT role, min(step) as minimum FROM link GROUP BY role) temp ON id=temp.role 
	SET star_category_step=temp.minimum 
	WHERE `system` = sys AND client = cli;

	DROP TEMPORARY TABLE link;
 
-- update history
    INSERT INTO um_dash_star_role_history 
        SELECT * FROM (
			SELECT `system`,client, current_date, star_category_step,count(star_category_step) AS step2 
			FROM um_role 
        	WHERE `system` = sys AND `client` = cli 
        	group by `system`,client, current_date,star_category_step 
        ) AS t2
	ON DUPLICATE KEY UPDATE count =step2;

-- update the composite roles maybe it should be after role history
	UPDATE um_role AS a, (
		SELECT um_role.id,min(um_role_1.star_category_step) AS step
		FROM um_role_comp 
		INNER JOIN um_role ON 
			um_role_comp.composite = um_role.id
		INNER JOIN um_role um_role_1 ON 
			um_role_comp.single = um_role_1.id
		WHERE um_role_1.star_category_step >0
		GROUP BY um_role.id
	) AS b
	SET a.star_category_step = b.step
	WHERE a.id = b.id;

-- update user star cat
	INSERT INTO um_dash_star_user
	SELECT * FROM (
		SELECT a.`system`,a.client,user_id,min(star_category_step) AS step2, null
		FROM um_user_role a
    	INNER JOIN um_role b on a.role_id = b.id
    	WHERE a.`system` = sys AND  a.`client` = cli 
    	GROUP BY user_id
    ) AS t2
    ON DUPLICATE KEY UPDATE step = step2;
        
	INSERT INTO um_dash_star_user
		SELECT * FROM (
			SELECT a.`system`, a.client, user_id, min(star_category_step) AS step2, null
			FROM um_user_role a
	    		INNER JOIN um_role b ON a.role_id = b.id  
	    	WHERE a.`system` = sys AND  a.`client` = cli AND star_category_step > 0 
	    	GROUP BY user_id
	    ) as t2
	ON DUPLICATE KEY UPDATE step = step2;
	    
-- add the roles containing the licence step
UPDATE um_dash_star_user a, (
	SELECT
		a.user_id,
		SUBSTRING(GROUP_CONCAT(c.role SEPARATOR ', '), 1, 32765) AS roles
	FROM um_dash_star_user a
		JOIN um_user_role b ON a.user_id = b.user_id
		JOIN um_role c ON b.role_id = c.id AND a.step = c.star_category_step
	GROUP BY a.user_id
) b
SET a.roles = b.roles
WHERE a.user_id = b.user_id;

-- update user star history
	INSERT INTO um_dash_star_user_history 
		SELECT * FROM (
			SELECT `system`,client, current_date,step,count(step) AS count2 
			FROM um_dash_star_user
	        WHERE `system` = sys AND `client` = cli 
	        GROUP BY `system`,client,current_date,step 
		) AS t2
	ON DUPLICATE KEY UPDATE count = count2;

END