Display wrong values on dashboard

[simon2112b]

HELP
I'm having problems with the dashboard because he is shows wrong values( if i have 1 opp of 3.5k the system show on dashboard 48 opps of 54.6k) and i'm sure it has something with the Team Security
I have these line in /modules/opportunities/charts.php

if(file_exists('modules/TeamSecurity/TeamSecurity.php')) {
require_once('modules/TeamSecurity/TeamSecurity.php');
$query = get_team_query($query, $opp, $opp, "dashboard");
}

If i comment that it works fine.
But i need those lines becuase of the TeamSecurity

What i realize is that those line change me SQL query from this :

SELECT opportunities.sales_stage, users.user_name, opportunities.assigned_user_id, count( * ) AS opp_count, sum( amount_usdollar /1000 ) AS total
FROM users, opportunities
WHERE opportunities.assigned_user_id
IN (
'de0ba0b6-52b6-4b22-c814-432e42dc3034'
)
AND opportunities.sales_stage
IN (
'Prospecting', 'Levantamento', 'Perception Analysis', 'Proposal/Price Quote', 'Closed Won', 'Closed Lost'
)
AND opportunities.date_closed >= '2005-07-01'
AND opportunities.date_closed <= '2006-01-04'
AND opportunities.assigned_user_id = users.id
AND opportunities.deleted =0
GROUP BY opportunities.sales_stage, users.user_name, opportunities.assigned_user_id
LIMIT 0 , 30

to this :

SELECT DISTINCT opportunities.sales_stage, opportunities.assigned_user_id, users.user_name, count( * ) AS opp_count, sum( amount_usdollar /1000 ) AS total
FROM users, opportunities
LEFT JOIN roles_relation ON opportunities.id = roles_relation.relation_id
LEFT JOIN roles_users ON roles_relation.role_id = roles_users.role_id
WHERE opportunities.assigned_user_id
IN (
'de0ba0b6-52b6-4b22-c814-432e42dc3034'
)
AND opportunities.sales_stage
IN (
'Prospecting', 'Levantamento', 'Perception Analysis', 'Proposal/Price Quote', 'Closed Won', 'Closed Lost'
)
AND opportunities.date_closed >= '2005-07-01'
AND opportunities.date_closed <= '2006-01-04'
AND opportunities.assigned_user_id = users.id
AND opportunities.deleted =0
AND (
(
roles_users.user_id = 'de0ba0b6-52b6-4b22-c814-432e42dc3034'
AND (
(
roles_relation.deleted IS NULL
OR roles_relation.deleted =0
)
AND (
roles_users.deleted IS NULL
OR roles_users.deleted =0
)
)
)
OR opportunities.assigned_user_id = 'de0ba0b6-52b6-4b22-c814-432e42dc3034'
)
GROUP BY opportunities.sales_stage, users.user_name, opportunities.assigned_user_id


and that breacks the sql query giving me wrong values and soo in the dashboard,
what should i try ?
Thanks

[dwiegand]

Simon, did you ever get this fixed? I have been pulling my hair out trying to figure out how to modify the query to fit the need without recoding the whole thing.


The issue comes in when hitting the roles-relations table.

I am going to work on this today. If I find something that works I'll post it up.