Aggregate Asterisk CDR by disposition and dst peers

asterisk_get_missed_calls.sql

DELIMITER $$
DROP PROCEDURE IF EXISTS getMissedCalls $$
CREATE PROCEDURE getMissedCalls(IN day VARCHAR(10))
LANGUAGE SQL
SQL SECURITY INVOKER
COMMENT 'Aggregate Asterisk CDR by disposition and dst peers' 
BEGIN
        DECLARE cols TEXT;
        DECLARE done BOOLEAN DEFAULT FALSE;
        -- make columns with peer names:
        -- MAX(CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(dstchannel, '-', 1), "/", -1) = '206' THEN disposition END) AS '206'
        -- MAX(CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(dstchannel, '-', 1), "/", -1) = '207' THEN disposition END) AS '207'
        -- etc.
        DECLARE peers CURSOR FOR SELECT GROUP_CONCAT(DISTINCT
                        CONCAT(
                                'MAX(CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(dstchannel, ''-'', 1), "/", -1) = ''',
                                SUBSTRING_INDEX(SUBSTRING_INDEX(dstchannel, '-', 1), '/', -1),
                                ''' THEN disposition END) AS ''',
                                SUBSTRING_INDEX(SUBSTRING_INDEX(dstchannel, '-', 1), '/', -1),
                                ''''
                        )
                ) 
                FROM cdr
                WHERE dstchannel LIKE 'SIP%' AND DATE(calldate) = DATE(day);
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;

        -- very long string result in previous query :)
        SET group_concat_max_len = 5000;

        OPEN peers;
        FETCH peers INTO cols;

        DROP TEMPORARY TABLE IF EXISTS current_missed_calls;

        -- retrieve calls
        -- ------------------------------------------------
        -- | calldate | src | dst | 201 | 202 | 203 | etc.|
        -- ------------------------------------------------
        IF cols IS NOT NULL THEN
                SET @query = CONCAT(
                        'CREATE TEMPORARY TABLE current_missed_calls AS ',
                        'SELECT calldate, src, dst, ', cols, ' ',
                        'FROM cdr 
                         WHERE dstchannel LIKE ''SIP%'' AND DATE(calldate)=''', day, '''
                         GROUP BY uniqueid ORDER BY calldate DESC, clid'
                );
                PREPARE stmt FROM @query;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
        ELSE
                -- no calls, make empty table
                CREATE TEMPORARY TABLE IF NOT EXISTS current_missed_calls 
                (calldate DATETIME DEFAULT NULL, src VARCHAR(15) DEFAULT NULL, dst VARCHAR(15) DEFAULT NULL);
        END IF;
        -- cleanup
        CLOSE peers;
END
$$

DELIMITER ;