LIVE STATS - Addon

[nrc]

So my company uses a TV at the front of the room for the agents to see everyone's stats throughout the day. See how many sales, or how many upsales, or how long they have been on break, etc...

Well VICIDIAL doesn't currently have something like this, so we spent a few hundred man hours and created something, its not fancy, but it certianly gets the job done!

To make this work, we had to create custom fields in the lists. and then when we dial a campaign, our query goes and finds out what the listid is and then gathers the data from those list(s) (multiple lists if we call more than 1 campaign in a day).

Here is the outcome of what it would look like on a supervisors station or even a dedicated TV in the room:

full pic - http://i39.tinypic.com/34sm9zb.jpg

The code has built-in cell flashing when the user/agent goes over a certain threshold (for us pause time over 45mins...wrap % over 2%)
D amount, Up$, Monthly$ are all pulled from the custom fields in the listid table. These numbers ONLY show up if the dispo was marked as a SALE, otherwise it doesn't calculate a number (we did this on purpose, because agents will mismark a call and not realize it until they compare their written stats with the stats on the TV, makes it easier for us to pinpoint).
All other numbers are calculated by dividing other numbers already displayed, or by doing a COUNT in the SQL.

We have a column called Ver. $ and Ver. #....This column is based on weather the agent got a Credit Card sale or a Bank draft sale. If they did then it shows under the Verified column.

And you can sort by any column heading as well.


We have also implemented a slim version into the agent screen as a rollover button:

full pic - http://i40.tinypic.com/2eqaz55.jpg

And that same button, once rolled over if you click will display stats for all the agents (so the agent doesn't have to leave there desk!):

full pic - http://i44.tinypic.com/29xthg.jpg


Just putting this out there to see if anyone was interested in something like this? We won't do any modifying for you, but the hard part should be done, you'll just have to edit the code to reflect your database or your custom field names.

[omarrodriguezt]

I like it. Can you please share the code in the issue tracker?

[dspaan]

Looks awesome! Please share it in the issue tracker + short instruction. We are looking for something like this too and have been working on it although we don't need the figures expressed in money. Do the stats get reset on a daily basis at a fixed time?

[GaD]

This is awesome! I like the look and the 'preliminary feel' of it! Great job! I've been looking into programing something like this myself. Of course you went way over what I was planning on doing (err..., have my programmer do ). I'd love to take your live report for a test run. Is this in the issue tracker somewhere...?

[nrc]

Sorry guys, we had a few issues with the stats. We were pulling from wrong tables, getting wrong numbers, etc... we are trying to fine tune it now.

But we've also had to change it up a bit...We actually have a cron job run once a min. gather the data and put it into a table. and then the end user script just pulls the data from that table on each refresh. (we are still trying to figure out how to output all the data to a variable and loop correctly, and this so far has been our work around for it.)

As soon as we can get the last part of code in working order, I will try and add it to the tracker for you guys to play with.

[dspaan]

Thanks in advance for sharing

[nrc]

Sorry guys for the wait....I've upload the majority of the script to the tracker:
http://www.vicidial.org/VICIDIALmantis/view.php?id=689

This is only the script that shows on the TV, I didn't add the agent scripts...that can easily be done by modifying the allstats file

I WILL HELP WHEN I CAN

[dspaan]

Thank you so much for sharing

I got two questions to start with:

Can you give a brief instruction on where to alter the code to remove some columns?
Can you specify the field types of the custom fields?

Also which SVN version are you using this on right now?

[nrc]

dspaan,

Version: 2.6b0.5
SVN Version: 1975

A majority of the code will be found within the allstats.php file. This is the file that does a majority of the gathering and placing into the temp DB.
starting at line 414 is what gets displayed in each cell column

config.php is the file that for the most part calls the tempDB and displays the data. config.php is also the file that holds the column names.
starting at line 137 is what sets the column header names


All of my fields are just plain TXT fields, but you can make the system work with any type of field.

[nrc]

Just and FYI:

If you used these live stats, PLEASE PLEASE make sure to build a replication box to pull the stats from.

We have found that these stats create a LOCK on the tables and can slow the screen pops for the agents and cause dispo errors

also attached is the most recent version of the stats - 11-13-2013
http://l.bitcasa.com/kHMrgXSK

[nrc]

Another update to this...

THANKS TO DOMEDAN and his LEADERBOARD addon he did a while back.

I was able to edit his file to make mine more mysql friendly! (this shouldn't cause any major table locks now) and most of the work is done by PHP now instead of inside mysql tables and query's!

New Screenshots:


You can now pick by campagin, and/or usergroup




Here is the code...its now 1 file (make sure you place within your vicidial folder, as it needs dbconnect and functions.php)

http://pastebin.com/GCBTT4ms

[nrc]

Still working on the DOWNLOAD button...so you can download the stats to CSV vs. printing to the page.

[DomeDan]

Good work nrc!

for people interested in this kinds of reports:
* nrc Live Stats Addon is using custom tables
* my domedan Leaderboard is using security_phrase as order value
that's the major difference, so if you use custom tables then you probably want to look at nrc addon and maybe build your own report from that one

[nrc]

What DomeDan said


Here is an updated version that now prints to txt file which can be opened in excel.

http://pastebin.com/mArds4Mv

[dspaan]

Hey guys, i tried Domedan's version but i'm getting error's. Maybe it's better that each version uses it's on thread because i can't follow anymore what the setup instruction for each branch is now.

Do i still need to make all those custom fields with the Domedan version? I just made a SALE as agent and filled out the security phrase field but no luck:

Warning: mysql_query() expects parameter 2 to be resource, null given in /srv/www/htdocs/vicidial/domedan_leaderboard.php on line 506 Warning: mysql_fetch_array() expects parameter 1 to be resource, null given in /srv/www/htdocs/vicidial/domedan_leaderboard.php on line 507 Warning: Invalid argument supplied for foreach() in /srv/www/htdocs/vicidial/domedan_leaderboard.php on line 601 Warning: asort() expects parameter 1 to be array, null given in /srv/www/htdocs/vicidial/domedan_leaderboard.php on line 647 Warning: array_reverse() expects parameter 1 to be array, null given in /srv/www/htdocs/vicidial/domedan_leaderboard.php on line 650 Warning: Invalid argument supplied for foreach() in /srv/www/htdocs/vicidial/domedan_leaderboard.php on line 669 Warning: array_sum

[DomeDan]

Its not really a copy/paste run script, one query joins a table that are not part of vicidial, you should edit it a little.

[williamconley]

Its not really a copy/paste run script, one query joins a table that are not part of vicidial, you should edit it a little.

of course you could post the structure of the custom table and instructions for what data belongs in it ...

[DomeDan]

forgot this topic, here is the table: (the script looks for this table in database: ledger)

Code: Select all

CREATE TABLE `invoice_types` (
  `invoice_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'connection to invoices',
  `ledger` varchar(30) COLLATE utf8_unicode_ci NOT NULL COMMENT 'what ledger',
  `ledger_state` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1 = active, 0 =  inactive',
  `manage_invoices` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'should the system create invoices etc for this ledger, 0 = inactive, 1 = active, 2 = value for special management',
  `bank_client_id` varchar(4) COLLATE utf8_unicode_ci NOT NULL COMMENT 'client id this ledger got at the bank',
  `bank_account` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'bank account to ledger',
  `ledger_name` varchar(60) COLLATE utf8_unicode_ci NOT NULL COMMENT 'name printed on the invoice-address',
  `ledger_address1` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'address  printed on the invoice-address',
  `ledger_postal_code` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'postal code printed on the invoice-address',
  `ledger_city` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'city printed on the invoice-address',
  `invoice_deadline_days` int(2) DEFAULT NULL COMMENT 'days waiting after invoice is sent (invoice_print_date + invoice_deadline_days = invoice_deadline_date)',
  `invoice_deadline_extra_days` int(2) DEFAULT NULL COMMENT 'days waiting when invoice_deadline is reached (days waiting before sending the first reminder)',
  `reminder1_fee` int(2) DEFAULT NULL COMMENT 'fee added to invoice amount (invoice_amount + fee = reminder1_amount (sending reminder1 at this step)',
  `reminder1_deadline_days` int(2) DEFAULT NULL COMMENT 'days waiting after reminder1 is sent (reminder1_print_date + reminder1_deadline_days = reminder1_deadline_date',
  `reminder1_deadline_extra_days` int(2) DEFAULT NULL COMMENT 'days waiting when reminder1_deadline is reached (days waiting before sending the second reminder)',
  `reminder2_fee` int(2) DEFAULT NULL COMMENT 'fee added to reminder1 amount (reminder1_amount + fee = reminder2_amount (sending reminder2 at this step)',
  `reminder2_deadline_days` int(2) DEFAULT NULL COMMENT 'days waiting after reminder2 is sent (reminder2_print_date + reminder2_deadline_days = reminder2_deadline_date',
  `reminder2_deadline_extra_days` int(2) DEFAULT NULL COMMENT 'days waiting when reminder2_deadline is reached (days waiting before sending the third reminder)',
  `reminder3_fee` int(2) DEFAULT NULL COMMENT 'fee added to reminder1 amount (reminder1_amount + fee = reminder2_amount (sending reminder3 at this step)',
  `reminder3_deadline_days` int(2) DEFAULT NULL COMMENT 'days waiting after reminder3 is sent (reminder3_print_date + reminder3_deadline_days = reminder3_deadline_date',
  `reminder3_deadline_extra_days` int(2) DEFAULT NULL COMMENT 'days waiting when reminder3_deadline is reached (days waiting before sending the forth reminder witch were to call the customer)',
  `postage_fee` int(2) NOT NULL DEFAULT '0' COMMENT 'price in nok for how much the postage fee is, it is used in some reports',
  `description` varchar(35) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`invoice_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci