Recreating materialised table queue_stats_mv from the normal queue_stats table
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 '',
`info3` varchar(50) NOT NULL default '',
`info4` varchar(50) NOT NULL default '',
`info5` 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;
We need to create a view to simplify queries:
create or replace view queue_stats_full as select q.*,qevent.event as event, qagent.agent as agent, qname.queue as queue from queue_stats q force index (ixdate) left join qagent on qagent=agent_id left join qname on qname=queue_id left join qevent on qevent = event_id;
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.
After that you might want to populate some extra fields like URL and DID:
update queue_stats_mv q1 left join queue_stats q2 on q1.uniqueid=q2.uniqueid and q2.qevent=15 set did=q2.info4, q1.datetime=q1.datetime;
update queue_stats_mv q1 left join queue_stats q2 on q1.uniqueid=q2.uniqueid and q2.qevent=15 set url=q2.info1, q1.datetime=q1.datetime;
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 ;