DROP PROCEDURE IF EXISTS `procedure_foo`;
DELIMITER $$
CREATE PROCEDURE `procedure_foo`()
BEGIN
DECLARE done INTEGER DEFAULT FALSE;
DECLARE token_id, group_id, group_domain_id, type_id INTEGER;
DECLARE group_name VARCHAR(64);
DECLARE group_code VARCHAR(255);
DECLARE cur CURSOR FOR SELECT `id`, `name`, `domain_id` FROM `civicrm_group`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SELECT id INTO type_id FROM `acme_token_type` WHERE `name` = 'in_group' LIMIT 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO group_id, group_name, group_domain_id;
IF done THEN
LEAVE read_loop;
END IF;
-- Double Insert Protection
SELECT COUNT(id) INTO token_id FROM `acme_token_entry` WHERE `token` = CONCAT('in_group_', group_id) LIMIT 1;
IF token_id > 0 THEN
ITERATE read_loop;
END IF;
-- Get Group Code
SELECT bb.code INTO group_code FROM `bb_object_type` bb JOIN `civicrm_group` gr ON (bb.id = gr.group_type) WHERE gr.id = group_id;
-- Insert based on group type:
IF group_code = 'FRIENDS' THEN
INSERT INTO `acme_token_entry` (`type_id`, `domain_id`, `is_system`, `token`, `label`, `access_level`) VALUES
(type_id, group_domain_id, 1, CONCAT('in_group_', group_id), CONCAT('In Group ', group_name), 99);
ELSEIF group_code = 'OPT_OUT' THEN
INSERT INTO `acme_token_entry` (`type_id`, `domain_id`, `is_system`, `token`, `label`, `access_level`) VALUES
(type_id, group_domain_id, 1, CONCAT('in_group_', group_id), CONCAT('In Group ', group_name), 98);
ELSEIF group_name = 'All Constituents' THEN
INSERT INTO `acme_token_entry` (`type_id`, `domain_id`, `is_system`, `token`, `label`, `access_level`) VALUES
(type_id, group_domain_id, 1, CONCAT('in_group_', group_id), CONCAT('In Group ', group_name), 99);
ELSE
INSERT INTO `acme_token_entry` (`type_id`, `domain_id`, `is_system`, `token`, `label`, `access_level`) VALUES
(type_id, group_domain_id, 0, CONCAT('in_group_', group_id), CONCAT('In Group ', group_name), 0);
END IF;
END LOOP;
CLOSE cur;
END
$$
delimiter ;
CALL procedure_foo();
DROP PROCEDURE procedure_foo;