team performance detail not showing sales correctly

[nrc]

On the AST_team_performance_detail.php the Sales aren't showing up correctly for the agents As you can see in the screenshot, the first set of Agents have a few(4 agents) sales missing but looking at the Agent Status Detail you can see the sales are shown on that page.
Anyone know the fix for this?

Team Performance




Agent Status Detail

[mflorell]

admin.php version and build? Install method? etc...

[nrc]

preload iso 4
revision 1975

VERSION: 2.6-399a
BUILD: 130425-0700

admin.php version? how do i find this

[nrc]

Matt anything on this issue?

[mflorell]

I don't really know what that first screenshot is showing me, there is nothing to do with sales in there.

I don't have a good understanding of the exact problem.

[DomeDan]

mflorell:
At the far right side there is a sale-column,
look at Robert Nemer at the bottom of the first table, it says 0 sale
and on the second picture it says 36 sale

nrc:
did you upgrade using svn after the install? if so, did you run the mysql upgrade script?

[mflorell]

I'm not sure how you posted this image, but I don't see a sale column on the first image.

[nrc]

DomeDan, i didn't do the install 5Ghz did our install..I would think he ran the mysql upgrade script, he's been doing this a while.

But this is the only page thats off like this. and the weird part is that the 1st TEAM has lots of 0 in the sales column (which is incorrect) the 2nd TEAM group all have correct Sales count numbers....would that still be an issue if the mysql upgrade didn't run?

Matt, I will repost pics

[nrc]

This is a Screenshot from the 13th (Thursday)


While group 1 seems to be WAY off
Group 2 (Barbara Phillips) seems to be the only one that doesn't show sales correctly

[DomeDan]

mflorell:
you need to get a bigger monitor if you re-size the browser the right forum border will cut into the picture.

nrc:
also post the full url for both the reports showing the same date
like this: AST_team_performance_detail.php?DB=&type=&query_date=2013-06-18+00%3A00%3A00&...

[mflorell]

I know in the code that those are two very different reports. We have seen issues like this show up when leads have been moved to different lists or deleted entirely because some of the reports are dependent on the lead being present and not deleted.

Also, it's difficult to give a reason without seeing the SQL that is generating these results.

[nrc]

well these reports are run back to back within a matter of seconds, so i know nothing was deleted or changed.

How can i get the system to show you the SQL commands being run?

[nrc]

Agent Details URL:

Code: Select all

http://VICIDIALWEB/vicidial/AST_agent_status_detail.php?DB=&query_date=2013-06-13&end_date=2013-06-13&group%5B%5D=--ALL--&user_group%5B%5D=--ALL--&shift=ALL&report_display_type=TEXT&SUBMIT=SUBMIT

Code: Select all

select count(*) as calls,full_name,vicidial_users.user,status from vicidial_users,vicidial_agent_log where event_time <= '2013-06-13 23:59:59' and event_time >= '2013-06-13 00:00:00' and vicidial_users.user=vicidial_agent_log.user and campaign_id IN('AIEF','CIN','NAA','NRF','ROAR','TEMPLATE','TXTRAIN') and vicidial_agent_log.user_group IN('ADMIN','PH-Agents','TX-Ashley','TX-Jasey','TX-Training','TX-University') group by user,full_name,status order by full_name,user,status desc limit 500000;

Team Performance URL:

Code: Select all

http://VICIDIALWEB/vicidial/AST_team_performance_detail.php?DB=&type=&query_date=2013-06-18+00%3A00%3A00&end_date=2013-06-18+23%3A59%3A59&query_date_D=2013-06-13&query_date_T=00%3A00%3A00&end_date_D=2013-06-13&end_date_T=23%3A59%3A59&group%5B%5D=--ALL--&user_group%5B%5D=--ALL--&call_status%5B%5D=--NONE--&report_display_type=TEXT&SUBMIT=SUBMIT

Code: Select all

select count(distinct lead_id) from vicidial_agent_log where lead_id is not null and event_time>='2013-06-13 00:00:00' and event_time<='2013-06-13 23:59:59' and campaign_id IN('AIEF','CIN','NAA','NRF','ROAR','TEMPLATE','TXTRAIN') and user='teu' and user_group='TX-Ashley'
select count(*) from vicidial_callbacks where status in ('ACTIVE', 'LIVE') and campaign_id IN('AIEF','CIN','NAA','NRF','ROAR','TEMPLATE','TXTRAIN') and user='teu' and user_group='TX-Ashley'
select val.status, val.sub_status, vs.customer_contact, sum(val.talk_sec), sum(val.pause_sec), sum(val.wait_sec), sum(val.dispo_sec), sum(val.dead_sec), count(*) from vicidial_agent_log val, vicidial_statuses vs where val.user='teu' and val.user_group='TX-Ashley' and val.event_time>='2013-06-13 00:00:00' and val.event_time<='2013-06-13 23:59:59' and val.status=vs.status and vs.status in (select status from vicidial_statuses) and val.campaign_id in ('AIEF','CIN','NAA','NRF','ROAR','TEMPLATE','TXTRAIN') group by status, customer_contact UNION select val.status, val.sub_status, vs.customer_contact, sum(val.talk_sec), sum(val.pause_sec), sum(val.wait_sec), sum(val.dispo_sec), sum(val.dead_sec), count(*) from vicidial_agent_log val, vicidial_campaign_statuses vs where val.campaign_id in ('AIEF','CIN','NAA','NRF','ROAR','TEMPLATE','TXTRAIN') and val.user='teu' and val.user_group='TX-Ashley' and val.event_time>='2013-06-13 00:00:00' and val.event_time<='2013-06-13 23:59:59' and val.status=vs.status and val.campaign_id=vs.campaign_id and vs.status in (select distinct status from vicidial_campaign_statuses where campaign_id IN('AIEF','CIN','NAA','NRF','ROAR','TEMPLATE','TXTRAIN')) group by status, customer_contact


[DomeDan]

How can i get the system to show you the SQL commands being run?

you do that by setting DB=1 like this:
AST_team_performance_detail.php?DB=&...
AST_team_performance_detail.php?DB=1&...

I come to think of one thing that can be a problem:
from vicidial_users, vicidial_agent_log where vicidial_users.user_group='USERGROUP_1' and vicidial_users.user=vicidial_agent_log.user and vicidial_agent_log.user_group='USERGROUP_1'
If you change user group of an agent, then I doubt it will change the user_group field in vicidial_agent_log

[nrc]

I have added the SQL output to my previous

[DomeDan]

Do you know if something has been changed with those agents with 0 sales?

I've looked at the sql, but cant figure it out yet,
can you get into the database and look in table vicidial_agent_log
and copy the data from one row of a SALE that you know are not listed in team performance and one that are
like this:

SELECT * FROM vicidial_agent_log WHERE event_time>='2013-06-13 00:00:00' AND event_time<='2013-06-13 23:59:59' AND status='SALE' AND user='rnx'

SELECT * FROM vicidial_agent_log WHERE event_time>='2013-06-13 00:00:00' AND event_time<='2013-06-13 23:59:59' AND status='SALE' AND user='tvy'

[nrc]

FOR RNX: phpmyadmin returns *Showing rows 0 - 29 ( 31 total, Query took 0.0106 sec)*
Here is the first row:

Code: Select all

122491   RNX   192.168.40.26   2013-06-13 07:19:18   32529   CIN   1371125958   0   1371125958   34   1371125992   225   1371126217   1   SALE   TX-Ashley   NULL   NULL   NULL   0   N   1371125967.237152

FOR TVY: phpmyadmin returns *Showing rows 0 - 29 ( 34 total, Query took 0.0100 sec)*
Here is the first row:

Code: Select all

122366   tvy   192.168.40.27   2013-06-13 07:10:58   34114   CIN   1371125458   0   1371125458   8   1371125466   108   1371125574   1   SALE   TX-Ashley   NULL   NULL   1371125565   9   N   1371125446.23526

[DomeDan]

Look at team performance detail report and make sure that rnx has 0 sales in that date/time spawn from the query (2013-06-13)
and that tvy got sales in the report.

The only difference I can see is that the calls were made on two different servers
and that tvy got some dead time on the call.

have you added any campaign specific statuses? (vicidial_campaign_statuses)

[nrc]

I have not...and the report still shows the exact same as the screen shot above for the 13th.

(actually it doesnt matter what day i run it for...the first team always has people with 0 sales)

[nrc]

Matt, any help on this topic would be grateful!

thanks

[williamconley]

What you need to do, if possible, is locate the log records of a single missing entry. Then see how it differs from the other records (either directly or through association with some other record that may be involved in the sql queries involved).

Big problems like this are usually solved by following a single known failure to find its cause. The rest of the flaws are then usually shown to be related to the original and resolved at the same time. For instance, sales from Inbound calls are somehow treated differently, or transfers from inbounds ... something slightly different about the records that causes them to be different. But the troubleshooting starts from identifying a broken entry.

[DomeDan]

Check using web-ui and phpmyadmin that you don't have any duplicated users in vicidial_users

and do and william suggests, and also run AST_team_performance_detail.php with DB=1
check every query that is displayed and run those queries one by one in phpmyadmin and figure out what is suppose to be displayed, then you might find something odd that you can investigate further

[nrc]

What you need to do, if possible, is locate the log records of a single missing entry. Then see how it differs from the other records (either directly or through association with some other record that may be involved in the sql queries involved).

Big problems like this are usually solved by following a single known failure to find its cause. The rest of the flaws are then usually shown to be related to the original and resolved at the same time. For instance, sales from Inbound calls are somehow treated differently, or transfers from inbounds ... something slightly different about the records that causes them to be different. But the troubleshooting starts from identifying a broken entry.

I've posted this info a few lines up...we don't take inbound calls so that is not the issue. But I showed a sale from a guy that didn't show up on the report, but the mysql pulls it just fine....and then i show a person who shows sales and its mysql export.

[nrc]

Dome, can you tell me what you mean by web-ui?

[DomeDan]

I mean the user section in the vicidial admin interface

[nrc]

yes i have no duplicate users.

[DomeDan]

I have no more ideas, you need to start debugging,
get the sql queries and run them one by one and try to figure out what you should get and what you are not getting for those who does not list SALE

[nrc]

OK so here is the SQL that runs

Code: Select all

select val.status, val.sub_status, vs.customer_contact, sum(val.talk_sec), sum(val.pause_sec), sum(val.wait_sec), sum(val.dispo_sec), sum(val.dead_sec), count(*) from vicidial_agent_log val, vicidial_statuses vs where val.user='tdr' and val.user_group='TX-Ashley' and val.event_time>='2013-06-26 00:00:00' and val.event_time<='2013-06-26 23:59:59' and val.status=vs.status and vs.status in (select status from vicidial_statuses) and val.campaign_id in ('AIEF','CIN','NAA','NRF','ROAR','SNRF','TEMPLATE','TXTRAIN') group by status, customer_contact UNION select val.status, val.sub_status, vs.customer_contact, sum(val.talk_sec), sum(val.pause_sec), sum(val.wait_sec), sum(val.dispo_sec), sum(val.dead_sec), count(*) from vicidial_agent_log val, vicidial_campaign_statuses vs where val.campaign_id in ('AIEF','CIN','NAA','NRF','ROAR','SNRF','TEMPLATE','TXTRAIN') and val.user='tdr' and val.user_group='TX-Ashley' and val.event_time>='2013-06-26 00:00:00' and val.event_time<='2013-06-26 23:59:59' and val.status=vs.status and val.campaign_id=vs.campaign_id and vs.status in (select distinct status from vicidial_campaign_statuses where campaign_id IN('AIEF','CIN','NAA','NRF','ROAR','SNRF','TEMPLATE','TXTRAIN')) group by status, customer_contact


and here is the output of that in mysql


and here is the output on the report



So, the SQL command is right...we are pulling data, but not displaying correctly on the report.

[nrc]

Ok I just figured it out.

So when we first started the dialer I had set the usernames to ABC (all caps)...and then later on, i decided I wanted them to be lower case.

SO! the first like 6-8 agents i did in CAPS, i went into the admin side-> agents -> modify...and changed the username to lowercase.

WELL, in doing that, the database didn't update all the nesscary tables to reflect lowercase usernames...so when the report is trying to match up usernames from the SQL string, i guess they dont match up because the SQL is pulling usernames as CAPS and not lowercase.

So in all instanances of my 0 sales, its because the username is showing as CAPS in the SQL when in the agent screen they are lowercase.

So now i need to find out how to change in the database all of these usernames to lowercase.

Matt, maybe you can edit the code to either not be able to change usernames, or if you do change it changes all the correct tables?

[DomeDan]

edit - solved before I posted.

[nrc]

Ok, so while i figured out the difference between each of the users UPPERCASE usernames being changed to lowercase...

i'm now even more confused as to why the report shows under Contacts a total of 23 (for the user above)...because it is taking NI and SALE and adding them together...so its seeing the SALE number, but not displaying?


Matt or any Dev, can you try and recreate this on your end?

Create a USER account with CAPS username....make some calls
Change the username to lowercase...make some more calls
Run the team performance report, do Sales show up for that user?

[DomeDan]

I wrote about this in the previous message, but deleted all when you found the reason to the problem

Its because SALE is counted in an other query, this one: "select max(event_time), vicidial_agent_log.user, vicidial_agent_log.lead_id"
this report does a lot of things in php

Now you need to manually update the log tables that still got the user in capital letters
like this: (always make backups of database before doing big UPDATE like this!!)

Code: Select all

UPDATE vicidial_agent_log
SET user='tdr'
WHERE user='TDR'

I think I wrote a script once that renamed or changed user_group on a specific user for every log-table, can't find it

[nrc]

All this headache for that simple little thing.

Thats all it was...as soon as I change them from CAPS to lowercase...Sales numbers pop right into that column!

Thanks so much for the help DOMEDAN!

Hopefully Matt can add a fix to the SVN that if you change username it will reflect in all tables?

[nrc]

Ok so I ran that update...but when they get a new SALE or even a new record is placed into the Agent Log...its back to UPPERCASE and doesn't count again unless I run the update SQL again for that user...

[Plat]

agent error 99% of the time!

[DomeDan]

I guess you need to eliminate those capital letters, because it might take the data from some other table then vicidial_users

So find every table with the user field and run the update query on all of them

[nrc]

Plat, this one is not agent error

[mflorell]

We have noticed that inconsistent use of mixed capitol and lower-case letters in user logins can cause problems in some reports.

[nrc]

Matt, I was just about to post that...

after further testing, i think whatever the case is set as the username is how the case needs to be when the user logs into the dialer.

SO...is there an easy way to modify the login (and re-login) to force lowercase (at least for me, since all usernames are input as lowercase)

[mflorell]

You would have to edit the vicidial.php script to do that.