Apex Code Development You need to sign in to do that
Don't have an account?
kiran2000 aggregate result returns 0 rows ..please help
Aggregate result returns 0 rows .I don't what is wrong with the query .Can some one help me
global Database.QueryLocator start(Database.BatchableContext BC){
return Database.getQueryLocator('SELECT Id,Accounts__c,Calls__c,Days_Worked__c,Reaches__c,Account_Type__c,Avg_Calls__c,Quarter__c,Reach_Status__c,Territory__c,Territory_Name__c,Territory_Type__c,OwnerId,Territory_Id__c,Parent_Territory_Id__c,Grand_Parent_Territory_Id__c,Business_Days_in_Quarter__c,Date__c,Date_Type_Dashboard__c FROM Account_Statistic__c WHERE Date__c = :MonthStartDate AND Date_Type_Dashboard__c=\'Monthly\' AND Territory_Type__c = \'District\'');
}
global void execute(Database.BatchableContext BC, List<sObject> scope){
List<Account_Statistic__c> accStatisticList = (List<Account_Statistic__c>)scope;
Account_Statistic__c accStaticRec = accStatisticList[0];
System.debug('::::::accStaticRec ::::::::'+accStaticRec );
list<Id> terrIdList = new list<Id>();
Map<Id,Id> groupIdAndTerrIdMap = new Map<Id,Id>();
Map<Id,Set<Id>> terrIdAndSetOfNonTargetPractionerAccIdMap = new Map<Id,Set<Id>>();
Map<Id,Set<Id>> terrIdAndSetOfTargetPractionerAccIdMap = new Map<Id,Set<Id>>();
Map<Id,Set<Id>> terrIdAndSetOfPharmacyAccIdMap = new Map<Id,Set<Id>>();
list<Territory> tlist = new list<Territory>();
tlist = [Select t.ParentTerritoryId, t.Id From Territory t where t.ParentTerritoryId =:accStaticRec.Territory_Id__c];
map<Id,list<Territory>> tmap = new map<Id,list<Territory>>{};
Set<Id> finalaccIdSet = new Set<Id>();
Set<Id> totalaccIdSet = new Set<id>();
list<Id> tIdlist = new list<Id>();
Set<Id> accIdSet = new Set<Id>();
Set<Id> accSet = new Set<Id>();
for(Territory terr:tlist){
tIdlist.add(terr.Id);
}
System.debug('::::tIdlist:::::'+tIdlist);
for(Group groupRec : [SELECT Id,RelatedId FROM Group WHERE RelatedId In:tIdlist] ){
groupIdAndTerrIdMap.put(groupRec.Id,groupRec.RelatedId );
}
System.debug('::::groupIdAndTerrIdMap.keyset():::::'+groupIdAndTerrIdMap.keyset());
if(groupIdAndTerrIdMap != NULL && groupIdAndTerrIdMap.size() > 0){
for(AccountShare accShare : [SELECT Id,AccountId,Account.RecordTypeId,Account.RecordType.Name,Account.Target__c, UserOrGroupId FROM AccountShare WHERE (RowCause = 'Territory' OR RowCause = 'TerritoryManual') AND UserOrGroupId IN :groupIdAndTerrIdMap.keySet() AND (Account.RecordType.Name = 'Practitioner' OR Account.RecordType.Name = 'Pharmacy')] ){
if(accShare.Account.RecordType.Name == 'Practitioner'){
if(accShare.Account.Target__c){
Id RelatedId = groupIdAndTerrIdMap.get(accShare.UserOrGroupId );
if(!terrIdAndSetOfTargetPractionerAccIdMap.containsKey(RelatedId) )
terrIdAndSetOfTargetPractionerAccIdMap.put(RelatedId,new Set<Id>());
terrIdAndSetOfTargetPractionerAccIdMap.get(RelatedId).add(accShare.AccountId);
}else{
Id RelatedId = groupIdAndTerrIdMap.get(accShare.UserOrGroupId );
if(!terrIdAndSetOfNonTargetPractionerAccIdMap.containsKey(RelatedId) )
terrIdAndSetOfNonTargetPractionerAccIdMap.put(RelatedId,new Set<Id>());
terrIdAndSetOfNonTargetPractionerAccIdMap.get(RelatedId).add(accShare.AccountId);
}
}else if(accShare.Account.RecordType.Name == 'Pharmacy'){
Id RelatedId = groupIdAndTerrIdMap.get(accShare.UserOrGroupId );
if(!terrIdAndSetOfPharmacyAccIdMap.containsKey(RelatedId) )
terrIdAndSetOfPharmacyAccIdMap.put(RelatedId,new Set<Id>());
terrIdAndSetOfPharmacyAccIdMap.get(RelatedId).add(accShare.AccountId);
}
}
}
for(Territory terr:tlist){
if(accStaticRec.Account_Type__c == 'Target'){
accIdSet = terrIdAndSetOfTargetPractionerAccIdMap.get(terr.Id);
}else if(accStaticRec.Account_Type__c == 'Non-Target'){
accIdSet = terrIdAndSetOfNonTargetPractionerAccIdMap.get(terr.Id);
}else if(accStaticRec.Account_Type__c == 'Pharmacy'){
accIdSet = terrIdAndSetOfPharmacyAccIdMap.get(terr.Id);
}
System.debug('::::accIdSet:::::'+accIdSet);
if(accIdSet != NULL && accIdSet.size() > 0){
System.debug('::::accIdSet:::::'+accIdSet);
System.debug('::::terr.Id:::::'+terr.Id);
String tId = Id.valueOf(terr.Id);
System.debug('::::tId::::'+tId);
AggregateResult[] groupedResultCall = [SELECT Account_vod__c,count(Id) FROM Call2_vod__c WHERE Call_Date_vod__c >= :MonthStartDate AND Call_Date_vod__c <= :MonthEndDate AND Account_Type__c =:accStaticRec.Account_Type__c
AND Territory_vod__c =:tId AND Status_vod__c = 'Submitted_vod' AND (Call_Type_vod__c != 'Call Only' OR Call_Type_vod__c != 'Event Only' OR Call_Type_vod__c != 'Event Detail')
AND Account_vod__c IN :accIdSet
AND Activity_Type__c <> 'Staff'
GROUP BY Account_vod__c ];
System.debug('::::groupedResultCall:::::'+groupedResultCall);
Map<Id,Integer> AccIdAndCountOfCallMap = new Map<Id,Integer>();
Integer totalCallsCount = 0;
for (AggregateResult ar : groupedResultCall) {
String accId = String.ValueOf(ar.get('Account_vod__c'));
System.debug('::::accId:::::'+accId);
accSet.add(Id.valueOf(accId));
System.debug('::::accSet:::::'+accSet);
finalaccIdSet.addAll(accSet);
System.debug('::::finalaccIdSet:::::'+finalaccIdSet);
}
System.debug('::::totalaccIdSet:::::'+totalaccIdSet);
totalaccIdSet.addAll(accIdSet);
System.debug('::::finalaccIdSet:::::'+finalaccIdSet);
System.debug('::::totalaccIdSet:::::'+totalaccIdSet);
}
System.debug('::::finalaccIdSet:::::'+finalaccIdSet.size());
System.debug('::::totalaccIdSet:::::'+totalaccIdSet.size());
}
System.debug('::::finalaccIdSet:::::'+finalaccIdSet.size());
System.debug('::::totalaccIdSet:::::'+totalaccIdSet.size());
if(accStaticRec.Reach_Status__c == 'Reached'){
accStaticRec.Accounts__c = finalaccIdSet.size();
}else if(accStaticRec.Reach_Status__c == 'Not Reached'){
if(finalaccIdSet.size() == totalaccIdSet.size()){
accStaticRec.Accounts__c = 0;
}else{
accStaticRec.Accounts__c = totalaccIdSet.size() - finalaccIdSet.size();
}
}
if(accStaticRec != NULL) {
update accStaticRec;
System.debug(':::::accStaticRec::::::::::'+accStaticRec);
}
}
}
System.debug(':::::FinalMap:::::::'+FinalMap);
}