I have to create Sales Person wise Leads, Opportunity, Customer Count Reports.
I am Using Below Query to generate Report:
SELECT (SELECT Count()
FROM tablead
WHERE sales_person =s2
.name
AND Date(creation) = Curdate()) AS LeadCount,
(SELECT Count()
FROM tablead
WHERE tablead.modified_by =tabemployee
.user_id
AND Date(modified) = Curdate()) AS ModifiedCount,
(SELECT Count(DISTINCT reference_name)
FROM tabcommunication
WHERE communication_type = ‘Comment’
AND reference_doctype = ‘Lead’
AND tabcommunication.owner =tabemployee
.user_id
AND Date(creation) = Curdate()) AS commentedLeads,
(SELECT Count()
FROM tablead
WHERE sales_person =s2
.name
AND asm_lead_status = ‘Hot’
AND Date(creation) = Curdate()) AS AssignedHotLead,
(SELECT Count()
FROM tabopportunity
WHERE sales_person = s2.name
AND Date(creation) = Curdate()) AS OpportunityCount,
(SELECT Count(*)
FROMtabsales order
WHERE status = 1
AND owner = tabemployee.user_id
AND Date(creation) = Curdate()) AS SalesCount,
s2.name
FROMtabsales person
AS s2
JOINtabemployee
ONtabemployee
.name =s2
.employee
WHERE 1;
To get Sales Person wise Daily sales Data.
I have check apply userpermission check while creating report.
User Permission are also set.
But Everyone is able to see all counts instead only for Sales Person they have access.
Please suggest best way to implement.