Store procedure: add agents to queues (Issabel PBX | Elastix PBX)

add_agents_to_queue.sql

-- =============================================
-- Author:	G. Mizael Mtz Hdz
-- Create date: 2019-09-09
-- Description: Store procedure: add agents to queues (Issabel PBX | Elastix PBX)
-- Company:   witsoftplus.com
-- Url: https://gist.github.com/gmizaelmtzhdz
-- =============================================
USE asterisk;
DELIMITER //
DROP PROCEDURE IF EXISTS add_agents_to_queue;
CREATE PROCEDURE add_agents_to_queue(IN agent VARCHAR(50), IN queue BIGINT)
BEGIN
  DECLARE x INT DEFAULT 0;
  DECLARE n BIGINT DEFAULT 0;
  DECLARE queue_id varchar(45) DEFAULT "0";
  DECLARE flag INT DEFAULT 0;
  IF queue IS NULL THEN
    SELECT COUNT(DISTINCT id) INTO n FROM queues_details;
    SELECT n;
    WHILE x  < n DO
      SELECT flags INTO flag FROM queues_details ORDER BY flags DESC LIMIT x,1;
      SELECT DISTINCT id INTO queue_id FROM queues_details LIMIT x,1;
      INSERT IGNORE INTO queues_details(id, keyword, data, flags) VALUES (queue_id,"member",agent,flag+1);
      SET  x = x + 1; 
    END WHILE;
  ELSE
    SELECT flags INTO flag FROM queues_details WHERE id=queue ORDER BY flags DESC LIMIT 0,1;
    INSERT IGNORE INTO queues_details(id, keyword, data, flags) VALUES (queue,"member",agent,"0");
  END IF;
END//
DELIMITER ;



-- Example how to call: specific queue -- 
call add_agents_to_queue("Agent/1001,0", 1000);
call add_agents_to_queue("Agent/1001,0", 10011);
call add_agents_to_queue("Agent/1001,0", 15000000000);


-- Example how to call: all queues -- 
call add_agents_to_queue("Agent/2000,0", NULL);







-- DROP "store procedure": add_agents_to_queue() --
DROP PROCEDURE IF EXISTS add_agents_to_queue;




-- Manually: INSERT -> queue:1000, agent:2000 --
INSERT INTO queues_details(id, keyword, data, flags) VALUES (1000,"member","Agent/2000,0","0");