Jordan Savant // Software Engineer

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;