How can we help you?

Recreating materialised table queue_stats_mv from the normal queue_stats table
Posted by: Nicolás Gudiño Posted on: Jun-22nd, 2016 18:34

If you installed Asternic from version 2.1.0 , then queue_stats_mv will be populated already by means of an insertion trigger, so this process is not needed.

However, if you installed an older Asternic version and want to upgrade to a newer one and try the materialized_view setting, you can recreate the data using this method.

First of all, you will have to create the queue_stats_mv table. Log into the mysql command line client:

mysql -u root -p qstats

And then paste this query to create the queue_stats_mv table:

CREATE TABLE IF NOT EXISTS `queue_stats_mv` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `datetime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `datetimeconnect` timestamp NOT NULL default '0000-00-00 00:00:00',
  `datetimeend` timestamp NOT NULL default '0000-00-00 00:00:00',
  `queue` varchar(100) NOT NULL default '',
  `agent` varchar(100) NOT NULL default '',
  `event` varchar(40) NOT NULL default '',
  `uniqueid` varchar(50) NOT NULL default '',
  `clid` varchar(50) NOT NULL default '',
  `url` varchar(100) NOT NULL default '',
  `did` varchar(100) NOT NULL default '',
  `position` int(6) unsigned NOT NULL default '1',
  `info1` varchar(50) NOT NULL default '',
  `info2` varchar(50) NOT NULL default '',
  `overflow` int(6) unsigned NOT NULL default '1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uni` (`uniqueid`),
  KEY `fecha` (`datetime`)
);

If that table is already created and you want to empty it and start from new data, use this query:

TRUNCATE TABLE queue_stats_mv;

Once you have that table created, you should create a temporary table to help us in the process. This table will contain the last valid record for a call:

CREATE TEMPORARY TABLE `lastentry` ( 
    `uniqueid` varchar(40) character set utf8 default NULL, 
    `datetime` datetime default NULL, 
     `queue_stats_id` int(12), KEY `uni` (`uniqueid`)) 
   SELECT IF(uniqueid='MANAGER',CONCAT_WS('.','x',UNIX_TIMESTAMP(datetime),queue_stats_id),uniqueid) AS uniqueid,
   MAX(datetime) AS datetime, MAX(queue_stats_id) AS queue_stats_id 
   FROM queue_stats_full WHERE event IN ('COMPLETECALLER','COMPLETEAGENT','TRANSFER','ABANDON','EXITWITHKEY','EXITWITHTIMEOUT','EXITEMPTY','PAUSE','UNPAUSE','ADDMEMBER','REMOVEMEMBER') 
   GROUP BY IF(uniqueid='MANAGER',CONCAT_WS('.','x',UNIX_TIMESTAMP(datetime),queue_stats_id),uniqueid); 

Then we set some helper variables:

SET @num := 0, @uniqueid := '', @fechaconnect :='', @fechaenter:='', @colaenter='', @clid='';

And finally we populate the queue_stats_mv table from data in queue_stats with this query:

INSERT IGNORE INTO queue_stats_mv ( 
    datetime, datetimeconnect, datetimeend, queue, agent, event, 
    uniqueid, clid, url, position, info1, info2, info3, info4, info5, overflow ) 
SELECT IF(x.fecha_enter='',x.datetime,x.fecha_enter) AS datetime, 
IF(x.fecha_connect='',x.datetime,x.fecha_connect) AS datetimeconnect, 
x.datetime AS datetimeend, queue, agent, event, x.uniqueid AS uniqueid, 
IFNULL(x.clid,'') AS clid, IFNULL(x.url,'') AS url, 
IF(event='COMPLETECALLER' OR event='COMPLETEAGENT',IFNULL(info3,''),IFNULL(x.position,1)) AS position, 
IFNULL(info1,'') AS info1, IFNULL(info2,'') AS info2, 
IFNULL(info3,'') AS info3, IFNULL(info4,'') AS info4, IFNULL(info5,'') AS info5,   
IF(x.queueorig <> x.queue,1,0) AS overflow 
FROM (
   SELECT queue_stats_id, IF(uniqueid='MANAGER',CONCAT_WS('.','x',UNIX_TIMESTAMP(datetime),queue_stats_id),uniqueid) AS uniqueid, 
   datetime, qname, qagent, qevent, IFNULL(info1,'') AS info1, IFNULL(info2,'') AS info2, IFNULL(info3,'') AS info3, 
   IFNULL(info4,'') AS info4, IFNULL(info5,'') AS info5, event, agent, queue, 
   @row_number, @fecha_connect, @fecha_enter, @queueorig, @clid, IFNULL(@did,''), IFNULL(@url,''), @position, @dummy, 
      @num := if(@uniqueid = uniqueid, @num + 1, 1) as row_number, 
      @fechaconnect := if(@uniqueid = uniqueid && event='CONNECT', datetime,if(event='ADDMEMBER' or event='REMOVEMEMBER' OR event='PAUSE' or event='UNPAUSE',datetime,if(@uniqueid <> uniqueid,datetime,@fechaconnect))) AS fecha_connect,
      @fechaenter := if (@uniqueid <> uniqueid, if (event='ENTERQUEUE', datetime,'') , if(event='ENTERQUEUE', if(@fechaenter='',datetime,@fechaenter),@fechaenter) ) AS fecha_enter,
           @colaenter := if (@uniqueid <> uniqueid, if (event='ENTERQUEUE', queue,'') , if(event='ENTERQUEUE', if(@colaenter='',queue,@colaenter),@colaenter) ) AS queueorig,
           @clid := if (@uniqueid <> uniqueid, if (event='ENTERQUEUE', info2,'') , if(event='ENTERQUEUE', if(@clid='',info2,@clid),@clid) ) AS clid,
           @did := if (@uniqueid <> uniqueid, if (event='ENTERQUEUE', info5,'') , if(event='ENTERQUEUE', if(@did='',info5,@did),@did) ) AS did,
           @url := if (@uniqueid <> uniqueid, if (event='ENTERQUEUE', info1,'') , if(event='ENTERQUEUE', if(@url='',info1,@url),@url) ) AS url,
           @position := if (@uniqueid <> uniqueid, if (event='ENTERQUEUE', info3,1) , if(event='ENTERQUEUE', if(@position=1,info3,@position),@position) ) AS position,
      @uniqueid := uniqueid AS dummy
  FROM queue_stats 
  LEFT JOIN qevent ON event_id = qevent 
  LEFT JOIN qagent ON agent_id = qagent 
  LEFT JOIN qname  ON queue_id = qname  
  ORDER BY uniqueid,datetime,queue_stats_id  
) AS x LEFT JOIN lastentry AS latest ON latest.uniqueid = x.uniqueid 
WHERE event IN ('COMPLETECALLER','COMPLETEAGENT','ABANDON','EXITWITHKEY','EXITWITHTIMEOUT','EXITEMPTY','TRANSFER','PAUSE','UNPAUSE','ADDMEMBER','REMOVEMEMBER') 
AND latest.queue_stats_id = x.queue_stats_id;

That will take a while to complete.

Once you finish, be sure to create the trigger so it gets populated:

DROP TRIGGER IF EXISTS queue_stats_ins;
DELIMITER $$
CREATE TRIGGER queue_stats_ins
AFTER INSERT ON queue_stats
FOR EACH ROW
BEGIN

SET @event_name = ''; SET @queue_name = ''; SET @agent_name = ''; SET @enterdate  = '';
SET @connectdate= ''; SET @clid       = ''; SET @position   = ''; SET @url        = '';
set @overflow   = ''; SET @firstenter = ''; SET @lastenter  = ''; SET @did        = '';

SELECT event FROM qevent WHERE event_id = NEW.qevent INTO @event_name;
SELECT agent FROM qagent WHERE agent_id = NEW.qagent INTO @agent_name;
SELECT queue FROM qname  WHERE queue_id = NEW.qname  INTO @queue_name;

IF @event_name LIKE 'COMPLETE%' THEN

/*answered calls*/

/* Elige el primer evento ENTERQUEUE, y si elijo el ultimo? (DESC) */
SELECT datetime,info1,info2,info3,info5 FROM queue_stats JOIN qevent ON qevent=event_id WHERE event='ENTERQUEUE' 
AND uniqueid=NEW.uniqueid ORDER BY datetime LIMIT 1 INTO @enterdate,@url,@clid,@position,@did;

/* Elige el ultimo evento CONNECT */
SELECT datetime FROM queue_stats JOIN qevent ON qevent=event_id 
WHERE event='CONNECT' AND uniqueid=NEW.uniqueid ORDER BY datetime DESC LIMIT 1 INTO @connectdate;

SELECT count(*) FROM queue_stats JOIN qevent ON qevent=event_id WHERE event='ENTERQUEUE'
AND uniqueid=NEW.uniqueid INTO @overflow;

REPLACE INTO queue_stats_mv (uniqueid,event,agent,queue,datetime,datetimeconnect,datetimeend,clid,position,url,did,overflow) 
VALUES (NEW.uniqueid,@event_name,@agent_name,@queue_name,@enterdate,@connectdate,NEW.datetime,@clid,IFNULL(NEW.info3,1),IFNULL(@url,''),IFNULL(@did,''),@overflow);

ELSEIF @event_name='TRANSFER' THEN

/*transferred calls*/

SELECT datetime,info1,info2,info3,info5 FROM queue_stats JOIN qevent ON qevent=event_id WHERE event='ENTERQUEUE' 
AND uniqueid=NEW.uniqueid ORDER BY datetime LIMIT 1 INTO @enterdate,@url,@clid,@position,@did;

SELECT datetime FROM queue_stats JOIN qevent ON qevent=event_id 
WHERE event='CONNECT' AND uniqueid=NEW.uniqueid ORDER BY datetime DESC LIMIT 1 INTO @connectdate;

SELECT count(*) FROM queue_stats JOIN qevent ON qevent=event_id WHERE event='ENTERQUEUE'

AND uniqueid=NEW.uniqueid INTO @overflow;

REPLACE INTO queue_stats_mv (uniqueid,event,agent,queue,datetime,datetimeconnect,datetimeend,clid,position,url,did,info1,info2,overflow) 
VALUES (NEW.uniqueid,@event_name,@agent_name,@queue_name,@enterdate,@connectdate,NEW.datetime,@clid,IFNULL(@position,1),IFNULL(@url,''),IFNULL(@did,''),IFNULL(NEW.info1,0),IFNULL(NEW.info2,0),@overflow);

ELSEIF @event_name LIKE '%ABANDON%' OR @event_name LIKE 'EXIT%' THEN

/*unanswered calls*/

SELECT datetime,info1,info2,info3,info5 FROM queue_stats JOIN qevent ON qevent=event_id WHERE event='ENTERQUEUE' 
AND uniqueid=NEW.uniqueid ORDER BY datetime LIMIT 1 INTO @enterdate,@url,@clid,@position,@did;

SELECT count(*) FROM queue_stats JOIN qevent ON qevent=event_id WHERE event='ENTERQUEUE'
AND uniqueid=NEW.uniqueid INTO @overflow;

SET @connectdate = @enterdate;

REPLACE INTO queue_stats_mv (uniqueid,event,agent,queue,datetime,datetimeconnect,datetimeend,clid,position,url,did,info1,info2,overflow) 
VALUES (NEW.uniqueid,@event_name,@agent_name,@queue_name,@enterdate,@connectdate,NEW.datetime,IFNULL(@clid,''),IFNULL(@position,1),IFNULL(@url,''),IFNULL(@did,''),IFNULL(NEW.info1,0),IFNULL(NEW.info2,0),@overflow);

ELSEIF @event_name LIKE 'AGENT%' OR @event_name LIKE '%PAUSE%' OR @event_name LIKE '%MEMBER%' THEN 

/* login, pausa, etc */

INSERT INTO queue_stats_mv (uniqueid,event,agent,queue,datetime,datetimeconnect,datetimeend,info1,info2) 
VALUES (CONCAT_WS('.','x',UNIX_TIMESTAMP(NOW()),NEW.queue_stats_id),@event_name,@agent_name,@queue_name,NEW.datetime,NEW.datetime,NEW.datetime,IFNULL(NEW.info1,0),IFNULL(NEW.info2,0));

END IF;
END;
$$
DELIMITER ;


Leave a comment